Excel как изменить таблицу данных

Таблицы Excel позволяет упростить процесс форматирования и анализа больших объемов данных. Впервые таблицы Excel были представлены в 2007 версии, как расширение инструмента Списки более ранних версий. Начиная с этой версии, у пользователей появилась возможность использовать формулы извлечения данных с таблиц.

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

Чтобы конвертировать обычный диапазон данных в таблицу Excel 2007+, вам необходимо выделить любую ячейку данного диапазона, перейти во вкладку Вставка, в группе Таблица нажать кнопку Таблица.

В появившемся диалоговом окне Создание таблицы, Excel укажет диапазон данных, который необходимо превратить в таблицу. Жмем ОК. Диапазон данных будет конвертирован в таблицу Excel.

Зачем нужны таблицы Excel?

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

Формат: Полностью изменяет внешний вид таблицы с помощью нескольких щелчков мыши.

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

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

Задавать имя таблицы: Вы можете задать таблице осмысленное название, которое будет в дальнейшем использоваться в формулах. Чтобы дать имя таблице, перейдите по вкладке Работа с таблицами -> Конструктор в группу Свойства. В поле Имя таблицы укажите желаемое название таблицы.

Формулы таблиц Excel

Табличные формулы позволяют получить данные таблицы простым и интуитивно понятным способом. При создании таблицы, Excel по умолчанию установит название Таблица 1, что не совсем отвечает концепции интуитивно понятный. Поэтомувы можетеизменить этот параметр, как мы описывали выше, на что-нибудь более элегантное, например, Продажи.

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

Использование конкретной колонки таблицы в формулах

Возьмем для примера таблицу с данными о продажах, указанную в начале статьи, где вам необходимо определить среднее значение колонки Цель. Для этого необходимо в ячейку ввести формулу =СРЗНАЧ(Продажи[Цель]). Строка Продажи[Цель] указывает на то, что необходимо вернуть столбец Цель из таблицы Продажи. Аналогично, строка Продажи[Факт] вернет нам столбец Факт таблицы Продажи. Точно таким же образом мы можем использовать функции и для других операций, например, =МАКС(Продажи[Цель]), =СЧЁТЗ(Продажи[Цель]) или =МИН(Продажи[Цель]).

Использование конкретной строки таблицы в формулах

Работа со строками таблицы аналогична работе с колонками, но все есть небольшие различия. Чтобы обратиться к строке таблицы, необходимо использовать символ @. Таким образом, если вы хотите обратиться к 10 строке таблицы, вы должны поместить формулу =Продажи[@] в любую ячейку 10 строки рабочего листа. Символ @ означает, что вы обращаетесь к той же строке таблицы, на какой строке находится формула. Т.е. формула =СЧЁТЕСЛИ(Продажи[@];”<>”) вернет количество не пустых ячеек соответствующей строки таблицы. Если вы скопируете данную формулу вниз, данные будут актуальны уже для следующей строки, хотя формула не изменится.

Использование всей таблицы в формулах

Подход аналогичный:

1. Чтобы обратится ко всей таблице, используйте =Продажи[#Все]
2. Если вас интересуют только значения таблицы, =Продажи[#Данные]
3. Если только заголовки, =Продажи[#Заголовки]

Использование формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ в таблице

Другая интересная особенность таблиц является использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая состоит из двух частей — первая, указывает на операцию, которую необходимо использовать для подведения итогов, вторая, содержит диапазон данных для обработки. Таким образом, формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;E1:E10), вернет сумму диапазонаE1:E10, а формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1; E1:E10) — среднее значение.

Возвращаемся к таблицам Excel. Вы можете воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, чтобы подвести итоги по всей таблице или конкретным строкам или столбцам. Чтобы вернуть среднее значение колонки Факт, введите следующую формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;Продажи[Факт]) .

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

Использование итогов для конкретного столбца в формулах

Чтобы вернуть итоговое значение столбца таблицы Excel, например, Цель, необходимо воспользоваться формулой =Продажи[[#Итоги];[Факт]]. Обратите внимание, что возвращаемое значение не обязательно будет функцией суммирования, а будет определяться той функцией, которая определена для строки итогов. Давайте посмотрим это на примере. Если итоговое значение столбца Факт таблицы Продажи содержит номер функции 4, то в строке итогов вы увидите максимальное значение столбца Факт. А формула =Продажи[[#Итоги];[Факт]] вернет не сумму значений столбца Факт, а его максимум.

Вам также могут быть интересны следующие статьи