Таблицы Excel позволяет упростить процесс форматирования и анализа больших объемов данных. Впервые таблицы Excel были представлены в 2007 версии, как расширение инструмента Списки более ранних версий. Начиная с этой версии, у пользователей появилась возможность использовать формулы извлечения данных с таблиц.
Как создать таблицу Excel
Чтобы конвертировать обычный диапазон данных в таблицу Excel 2007+, вам необходимо выделить любую ячейку данного диапазона, перейти во вкладку Вставка, в группе Таблица нажать кнопку Таблица.
В появившемся диалоговом окне Создание таблицы, Excel укажет диапазон данных, который необходимо превратить в таблицу. Жмем ОК. Диапазон данных будет конвертирован в таблицу Excel.
Зачем нужны таблицы Excel?
Данные таблицы гораздо функциональнее и гибче, нежели стандартные диапазоны данных. К тому же, появляется ряд дополнительных возможностей, которые можно найти во вкладке Работа с таблицами –> Конструктор, которая появляется, если вы выделите любую ячейку таблицы.
Формат: Полностью изменяет внешний вид таблицы с помощью нескольких щелчков мыши.
Подведение итогов: Чтобы добавить дополнительную строку с итоговыми значениями, в группе Параметры стилей таблиц поставьте маркер напротив поля Строка итогов. Обратите внимание, что среди итоговых значений доступны все не только операции суммирования, но и среднее, максимум, минимум и т.д.
Доступ другим пользователям: Вы можете экспортировать выбранную таблицу с помощью сервиса SharePoint.
Задавать имя таблицы: Вы можете задать таблице осмысленное название, которое будет в дальнейшем использоваться в формулах. Чтобы дать имя таблице, перейдите по вкладке Работа с таблицами -> Конструктор в группу Свойства. В поле Имя таблицы укажите желаемое название таблицы.
Формулы таблиц Excel
Табличные формулы позволяют получить данные таблицы простым и интуитивно понятным способом. При создании таблицы, Excel по умолчанию установит название Таблица 1, что не совсем отвечает концепции интуитивно понятный. Поэтому вы можете изменить этот параметр, как мы описывали выше, на что-нибудь более элегантное, например, Продажи.
Теперь вы можете обратиться ко всей таблице целиком, либо к отдельным колонкам или строкам, диапазонам, заголовкам в ваших формулах.
Использование конкретной колонки таблицы в формулах
Возьмем для примера таблицу с данными о продажах, указанную в начале статьи, где вам необходимо определить среднее значение колонки Цель. Для этого необходимо в ячейку ввести формулу =СРЗНАЧ(Продажи[Цель]). Строка Продажи[Цель] указывает на то, что необходимо вернуть столбец Цель из таблицы Продажи. Аналогично, строка Продажи[Факт] вернет нам столбец Факт таблицы Продажи. Точно таким же образом мы можем использовать функции и для других операций, например, =МАКС(Продажи[Цель]), =СЧЁТЗ(Продажи[Цель]) или =МИН(Продажи[Цель]).
Использование конкретной строки таблицы в формулах
Работа со строками таблицы аналогична работе с колонками, но все есть небольшие различия. Чтобы обратиться к строке таблицы, необходимо использовать символ @. Таким образом, если вы хотите обратиться к 10 строке таблицы, вы должны поместить формулу =Продажи[@] в любую ячейку 10 строки рабочего листа. Символ @ означает, что вы обращаетесь к той же строке таблицы, на какой строке находится формула. Т.е. формула =СЧЁТЕСЛИ(Продажи[@];»<>») вернет количество не пустых ячеек соответствующей строки таблицы. Если вы скопируете данную формулу вниз, данные будут актуальны уже для следующей строки, хотя формула не изменится.
Использование всей таблицы в формулах
Подход аналогичный:
- Чтобы обратится ко всей таблице, используйте =Продажи[#Все]
- Если вас интересуют только значения таблицы, =Продажи[#Данные]
- Если только заголовки, =Продажи[#Заголовки]
Использование формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ в таблице
Другая интересная особенность таблиц является использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая состоит из двух частей – первая, указывает на операцию, которую необходимо использовать для подведения итогов, вторая, содержит диапазон данных для обработки. Таким образом, формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;E1:E10), вернет сумму диапазона E1:E10, а формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1; E1:E10) – среднее значение.
Возвращаемся к таблицам Excel. Вы можете воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, чтобы подвести итоги по всей таблице или конкретным строкам или столбцам. Чтобы вернуть среднее значение колонки Факт, введите следующую формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;Продажи[Факт]) .
Вы, наверное, обратили внимание, что когда мы включили опцию Строка итогов, в таблицу была добавлена новая строка с итогами. Теперь мы можем модифицировать итоговые значения, используя таблицу с функциями, изображенную выше. Как вы смогли заметить, функции не ограничиваются только суммированием, можно найти также ряд других полезных операций.
Использование итогов для конкретного столбца в формулах
Чтобы вернуть итоговое значение столбца таблицы Excel, например, Цель, необходимо воспользоваться формулой =Продажи[[#Итоги];[Факт]]. Обратите внимание, что возвращаемое значение не обязательно будет функцией суммирования, а будет определяться той функцией, которая определена для строки итогов. Давайте посмотрим это на примере. Если итоговое значение столбца Факт таблицы Продажи содержит номер функции 4, то в строке итогов вы увидите максимальное значение столбца Факт. А формула =Продажи[[#Итоги];[Факт]] вернет не сумму значений столбца Факт, а его максимум.