Как сделать таблицу в excel 2007

Таблицы в формате EXCEL 2007

history 10 апреля 2013 г.
    Группы статей

  • Другие Стандартные Средства
  • Имена
  • Проверка данных
  • Таблицы в формате EXCEL 2007
  • Условное форматирование

Для упрощения управления логически связанными данными в EXCEL 2007 введен новый формат таблиц. Использование таблиц в формате EXCEL 2007 снижает вероятность ввода некорректных данных, упрощает вставку и удаление строк и столбцов, упрощает форматирование таблиц.

Исходная таблица

Пусть имеется обычная таблица (диапазон ячеек), состоящая из 6 столбцов.

В столбце (номер позиции), начиная со второй строки таблицы, имеется формула =A2+1 , позволяющая производить автоматическую нумерацию строк . Для ускорения ввода значений в столбце Ед.изм. (единица измерения) с помощью Проверки данных создан Выпадающий (раскрывающийся) список .

В столбце Стоимость введена формула для подсчета стоимости товара (цена*количество) =E3*D3 . Числовые значения в столбце отформатированы с отображением разделителей разрядов.

Действия с обычной таблицей

Чтобы показать преимущества таблиц в формате EXCEL 2007, сначала произведем основные действия с обычной таблицей.

Для начала добавим новую строку в таблицу, т.е. заполним данными строку 4 листа:

  • заполним значениями столбцы без формул ( Наименование, Цена, Количество );
  • в столбцах Стоимость и с помощью Маркера заполнения скопируем формулы в ячейки ниже;
  • чтобы в новой строке заработал Выпадающий список , в столбце Ед.изм . скопируем формат в ячейку ниже. Для этого выделим ячейку С3 , скопируем ее в Буфер обмена и, выделив ячейку ниже, через меню Главная/ Буфер обмена/ Вставить/ Специальная вставка/ Условия на значения вставим Выпадающий список (либо, как на предыдущем шаге скопируем Маркером заполнения значение из ячейки С3 в С4 , тем самым скопировав правило Проверки данных . Затем необходимо ввести значение Единицы измерения в новой строке).

Конечно, можно заранее скопировать формулы и форматы ячеек вниз на несколько строк – это ускорит заполнение таблицы.

Теперь рассмотрим те же действия, но в таблице в формате EXCEL 2007.

Создание таблиц в формате EXCEL 2007

Выделим любую ячейку рассмотренной выше таблицы и выберем пункт меню Вставка/ Таблицы/ Таблица .

EXCEL автоматически определит, что в нашей таблице имеются заголовки столбцов. Если убрать галочку Таблица с заголовками , то для каждого столбца будут созданы заголовки Столбец1 , Столбец2 , …

СОВЕТ : Избегайте заголовков в числовых форматах (например, «2009») и ссылок на них. При создании таблицы они будут преобразованы в текстовый формат. Формулы, использующие в качестве аргументов числовые заголовки, могут перестать работать.

После нажатия кнопки ОК:

  • к таблице будет автоматически применен стиль с чрезстрочным выделением;
  • в заголовке будет включен фильтр (чтобы его отключить выделите любую ячейку таблицы и нажмите CTRL+SHIFT+L , повторное нажатие включит фильтр);
  • станет доступным специальная вкладка в меню для работы с таблицами ( Работа с таблицами/ Конструктор ), вкладка активна только когда выделена любая ячейка таблицы;
  • таблице будет присвоено Имя , которое можно посмотреть через конструктор таблиц или через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) .

СОВЕТ : Перед преобразованием таблицы в формат EXCEL 2007 убедитесь, что исходная таблица правильно структурирована. В статье Советы по построению таблиц изложены основные требования к «правильной» структуре таблицы.

Удаление таблиц в формате EXCEL 2007

Чтобы удалить таблицу вместе с данными, нужно выделить любой заголовок в таблице, нажать CTRL + A , затем клавишу DELETE (любо выделите любую ячейку с данными, дважды нажмите CTRL + A , затем клавишу DELETE ). Другой способ удалить таблицу — удалить с листа все строки или столбцы, содержащие ячейки таблицы (удалить строки можно, например, выделив нужные строки за заголовки, вызвав правой клавишей мыши контекстное меню и выбрав пункт Удалить ).

Чтобы сохранить данные таблицы можно преобразовать ее в обычный диапазон. Для этого выделите любую ячейку таблицы (Будет отображена вкладка Работа с таблицами, содержащая вкладку Конструктор) и через меню Работа с таблицами/ Конструктор/ Сервис/ Преобразовать в диапазон преобразуйте ее в обычный диапазон. Форматирование таблицы останется. Если форматирование также требуется удалить, то перед преобразованием в диапазон очистите стиль таблицы ( Работа с таблицами/ Конструктор/ Стили таблиц/ Очистить ).

Добавление новых строк

Теперь проделаем те же действия с таблицей в формате EXCEL 2007, которые мы осуществляли ранее с обычным диапазоном.

Начнем с заполнения со столбца Наименование (первый столбец без формул). После ввода значения, в таблице автоматически добавится новая строка.

Как видно из рисунка сверху, форматирование таблицы автоматически распространится на новую строку. Также в строку скопируются формулы в столбцах Стоимость и №. В столбце Ед.изм . станет доступен Выпадающий список с перечнем единиц измерений.

Для добавления новых строк в середине таблицы выделите любую ячейку в таблице, над которой нужно вставить новую строку. Правой клавишей мыши вызовите контекстное меню, выберите пункт меню Вставить (со стрелочкой), затем пункт Строки таблицы выше.

Удаление строк

Выделите одну или несколько ячеек в строках таблицы, которые требуется удалить. Щелкните правой кнопкой мыши, выберите в контекстном меню команду Удалить, а затем команду Строки таблицы. Будет удалена только строка таблицы, а не вся строка листа. Аналогично можно удалить столбцы.

Данные итогов в таблице

Щелкните в любом месте таблицы. На вкладке Конструктор в группе Параметры стилей таблиц установите флажок Строка итогов.

В последней строке таблицы появится строка итогов, а в самой левой ячейке будет отображаться слово Итог .

В строке итогов щелкните ячейку в столбце, для которого нужно рассчитать значение итога, а затем щелкните появившуюся стрелку раскрывающегося списка. В раскрывающемся списке выберите функцию, которая будет использоваться для расчета итогового значения. Формулы, которые можно использовать в строке итоговых данных, не ограничиваются формулами из списка. Можно ввести любую нужную формулу в любой ячейке строки итогов. После создания строки итогов добавление новых строк в таблицу затрудняется, т.к. строки перестают добавляться автоматически при добавлении новых значений (см. раздел Добавление строк ). Но в этом нет ничего страшного: итоги можно отключить/ включить через меню.

Присваивание имен таблицам

При создании таблиц в формате EXCEL 2007, EXCEL присваивает имена таблиц автоматически: Таблица1 , Таблица2 и т.д., но эти имена можно изменить (через конструктор таблиц: Работа с таблицами/ Конструктор/ Свойства/ Имя таблицы ), чтобы сделать их более выразительными.

Имя таблицы невозможно удалить (например, через Диспетчер имен ). Пока существует таблица – будет определено и ее имя.

Структурированные ссылки (ссылки на поля и значения таблицы в формулах)

Теперь создадим формулу, в которой в качестве аргументов указан один из столбцов таблицы в формате EXCEL 2007 (формулу создадим вне строки итоги ).

  • Введем в ячейку H1 часть формулы: =СУММ(
  • Мышкой выделим диапазон F2:F4 (весь столбец Стоимость без заголовка)

Но, вместо формулы =СУММ(F2:F4 мы увидим =СУММ(Таблица1[Стоимость]

Это и есть структурированная ссылка. В данном случае это ссылка на целый столбец. Если в таблицу будут добавляться новые строки, то формула =СУММ(Таблица1[Стоимость]) будет возвращать правильный результат с учетом значения новой строки. Таблица1 – это имя таблицы ( Работа с таблицами/ Конструктор/ Свойства/ Имя таблицы ).

Структурированные ссылки позволяют более простым и интуитивно понятным способом работать с данными таблиц при использовании формул, ссылающихся на столбцы и строки таблицы или на отдельные значения таблицы.

Рассмотрим другой пример суммирования столбца таблицы через ее Имя . В ячейке H2 введем =СУММ(Т (буква Т – первая буква имени таблицы). EXCEL предложит выбрать, начинающуюся на «Т», функцию или имя, определенное в этой книге (в том числе и имена таблиц).

Дважды щелкнув на имени таблицы, формула примет вид =СУММ(Таблица1 . Теперь введем символ [ (открывающую квадратную скобку). EXCEL после ввода =СУММ(Таблица1[ предложит выбрать конкретное поле таблицы. Выберем поле Стоимость , дважды кликнув на него.

В формулу =СУММ(Таблица1[Стоимость введем символ ] (закрывающую квадратную скобку) и нажмем клавишу ENTER . В итоге получим сумму по столбцу Стоимость .

Ниже приведены другие виды структурированных ссылок: Ссылка на заголовок столбца: =Таблица1[[#Заголовки];[Стоимость]] Ссылка на значение в той же строке =Таблица1[[#Эта строка];[Стоимость]]

Копирование формул со структурированными ссылками

Пусть имеется таблица со столбцами Стоимость и Стоимость с НДС . Предположим, что справа от таблицы требуется рассчитать общую стоимость и общую стоимость с НДС.

Сначала рассчитаем общую стоимость с помощью формулы =СУММ(Таблица1[Стоимость]) . Формулу составим как показано в предыдущем разделе.

Теперь с помощью Маркера заполнения скопируем формулу вправо, она будет автоматически преобразована в формулу =СУММ(Таблица1[Стоимость с НДС])

Это удобно, но что будет если скопировать формулу дальше вправо? Формула будет автоматически преобразована в =СУММ(Таблица1[№]) Т.е. формула по кругу подставляет в формулу ссылки на столбцы таблицы. Т.е. структурированная ссылка похожа на относительную ссылку .

Теперь выделим ячейку J2 и нажмем комбинацию клавищ CTRL+R (скопировать формулу из ячейки слева). В отличие от Маркера заполнения мы получим формулу =СУММ(Таблица1[Стоимость]) , а не =СУММ(Таблица1[Стоимость с НДС]) . В этом случае структурированная ссылка похожа на абсолютную ссылку.

Теперь рассмотрим похожую таблицу и сделаем на основе ее данных небольшой отчет для расчета общей стоимости для каждого наименования фрукта.

В первой строке отчета (диапазон ячеек I1:K2 ) содержатся наименования фруктов (без повторов), а во второй строке, в ячейке I2 формула =СУММЕСЛИ(Таблица1[Наименование];I1;Таблица1[Стоимость]) для нахождения общей стоимости фрукта Яблоки . При копировании формулы с помощью Маркера заполнения в ячейку J2 (для нахождения общей стоимости фрукта Апельсины ) формула станет неправильной =СУММЕСЛИ(Таблица1[Ед.изм.];J1;Таблица1[Стоимость с НДС]) (об этом см. выше), копирование с помощью комбинации клавищ CTRL+R решает эту проблему. Но, если наименований больше, скажем 20, то как быстро скопировать формулу в другие ячейки? Для этого выделите нужные ячейки (включая ячейку с формулой) и поставьте курсор в Строку формул (см. рисунок ниже), затем нажмите комбинацию клавищ CTRL+ENTER . Формула будет скопирована правильно.

Стили таблиц

Для таблиц, созданных в формате EXCEL 2007 ( Вставка/ Таблицы/ Таблица ) существует возможность использовать различные стили для придания таблицам определенного вида, в том числе и с чрезсрочным выделением . Выделите любую ячейку таблицы, далее нажмите Конструктор/ Стили таблиц и выберите подходящий стиль.

Как сделать таблицу в Excel. Пошаговая инструкция

Таблицы в Excel представляют собой ряд строк и столбцов со связанными данными, которыми вы управляете независимо друг от друга.

Работая в Excel с таблицами, вы сможете создавать отчеты, делать расчеты, строить графики и диаграммы, сортировать и фильтровать информацию.

Если ваша работа связана с обработкой данных, то навыки работы с таблицами в Эксель помогут вам сильно сэкономить время и повысить эффективность.

  1. Как работать в Excel с таблицами. Пошаговая инструкция
  2. Прежде чем работать с таблицами в Эксель, последуйте рекомендациям по организации данных:
  3. 1. Выделите область ячеек для создания таблицы
  4. 2. Нажмите кнопку “Таблица” на панели быстрого доступа
  5. 3. Выберите диапазон ячеек
  6. 4. Таблица готова. Заполняйте данными!
  7. Видео урок: как создать простую таблицу в Excel
  8. Форматирование таблицы в Excel
  9. Видео урок: как задать формат таблицы
  10. Как добавить строку или столбец в таблице Excel
  11. Как отсортировать таблицу в Excel
  12. Видео урок как отсортировать таблицу
  13. Как отфильтровать данные в таблице Excel
  14. Как посчитать сумму в таблице Excel
  15. Видео урок: как посчитать сумму в таблице Excel
  16. Как в Excel закрепить шапку таблицы
  17. Видео урок: как закрепить шапку таблицы:
  18. Как перевернуть таблицу в Excel
  19. Видео урок как перевернуть таблицу:

Как работать в Excel с таблицами. Пошаговая инструкция

Прежде чем работать с таблицами в Эксель, последуйте рекомендациям по организации данных:

  • Данные должны быть организованы в строках и столбцах, причем каждая строка должна содержать информацию об одной записи, например о заказе;
  • Первая строка таблицы должна содержать короткие, уникальные заголовки;
  • Каждый столбец должен содержать один тип данных, таких как числа, валюта или текст;
  • Каждая строка должна содержать данные для одной записи, например, заказа. Если применимо, укажите уникальный идентификатор для каждой строки, например номер заказа;
  • В таблице не должно быть пустых строк и абсолютно пустых столбцов.

1. Выделите область ячеек для создания таблицы

Как сделать таблицу в Excel

Выделите область ячеек, на месте которых вы хотите создать таблицу. Ячейки могут быть как пустыми, так и с информацией.

2. Нажмите кнопку “Таблица” на панели быстрого доступа

Как создать таблицу в Excel

На вкладке “Вставка” нажмите кнопку “Таблица”.

3. Выберите диапазон ячеек

Как сделать таблицу в Excel

Во всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.

4. Таблица готова. Заполняйте данными!

Как сделать таблицу в Excel

Поздравляю, ваша таблица готова к заполнению! Об основных возможностях в работе с умными таблицами вы узнаете ниже.

Видео урок: как создать простую таблицу в Excel

Форматирование таблицы в Excel

Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

Если 7-ми стилей вам мало для выбора, тогда, нажав на кнопку, в правом нижнем углу стилей таблиц, раскроются все доступные стили. В дополнении к предустановленным системой стилям, вы можете настроить свой формат.

Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

Форматирование таблицы в Excel

  • Отображение строки заголовков – включает и отключает заголовки в таблице;
  • Строку итогов – включает и отключает строку с суммой значений в колонках;
  • Чередующиеся строки – подсвечивает цветом чередующиеся строки;
  • Первый столбец – выделяет “жирным” текст в первом столбце с данными;
  • Последний столбец – выделяет “жирным” текст в последнем столбце;
  • Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
  • Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.

Видео урок: как задать формат таблицы

Как добавить строку или столбец в таблице Excel

Даже внутри уже созданной таблицы вы можете добавлять строки или столбцы. Для этого кликните на любой ячейке правой клавишей мыши для вызова всплывающего окна:

Как добавить строку/колонку в таблице Excel

Чтобы добавить строку или колонку в таблице Excel:

  • Нажмите правой кнопкой мыши на любой ячейке таблицы, где вы хотите вставить строку или колонку => появится всплывающее окно:
  • Выберите пункт “Вставить” и кликните левой клавишей мыши по “Столбцы таблицы слева” если хотите добавить столбец, или “Строки таблицы выше”, если хотите вставить строку.

Как вставить строку в Excel

  • Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.

Как удалить строку в Excel

Как отсортировать таблицу в Excel

Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

Как отстортировать таблицу в excel

В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.

Видео урок как отсортировать таблицу

Как отфильтровать данные в таблице Excel

Для фильтрации информации в таблице нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

Как отфильтровать данные в таблице Excel

  • “Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
  • “Фильтр по цвету” так же как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
  • “Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
  • Во всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.

Если вы хотите отменить все созданные настройки фильтрации, снова откройте всплывающее окно над нужной колонкой и нажмите “Удалить фильтр из столбца”. После этого таблица вернется в исходный вид.

Фильтрация данных в таблице Excel

Как посчитать сумму в таблице Excel

Для того чтобы посчитать сумму колонки в конце таблицы, нажмите правой клавишей мыши на любой ячейке и вызовите всплывающее окно:

Промежуточные итоги в таблице Excel

В списке окна выберите пункт “Таблица” => “Строка итогов”:

Промежуточные итоги в таблице Excel

Внизу таблица появится промежуточный итог. Нажмите левой клавишей мыши на ячейке с суммой.

Строка с промежуточными итогами в Excel

В выпадающем меню выберите принцип промежуточного итога: это может быть сумма значений колонки, “среднее”, “количество”, “количество чисел”, “максимум”, “минимум” и т.д.

Видео урок: как посчитать сумму в таблице Excel

Как в Excel закрепить шапку таблицы

Таблицы, с которыми приходится работать, зачастую крупные и содержат в себе десятки строк. Прокручивая таблицу “вниз” сложно ориентироваться в данных, если не видно заголовков столбцов. В Эксель есть возможность закрепить шапку в таблице таким образом, что при прокрутке данных вам будут видны заголовки колонок.

Для того чтобы закрепить заголовки сделайте следующее:

  • Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:

  • Выберите пункт “Закрепить верхнюю строку”:

Закрепить шапку в таблице Excel

  • Теперь, прокручивая таблицу, вы не потеряете заголовки и сможете легко сориентироваться где какие данные находятся:

Видео урок: как закрепить шапку таблицы:

Как перевернуть таблицу в Excel

Представим, что у нас есть готовая таблица с данными продаж по менеджерам:

Как перевернуть таблицу в Excel

На таблице сверху в строках указаны фамилии продавцов, в колонках месяцы. Для того чтобы перевернуть таблицу и разместить месяцы в строках, а фамилии продавцов нужно:

  • Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):

Как перевернуть таблицу в Excel

  • Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:

Как перевернуть таблицу в Excel

  • В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:

Как перевернуть таблицу в Excel

  • Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.

Как перевернуть таблицу в Excel

Видео урок как перевернуть таблицу:

В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Ссылка на основную публикацию