Таблицы и формулы Excel

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

Имя таблицы

Когда Вы определяете таблицу, Excel автоматический присваивает ей уникальное имя. Изменить это имя можно на вкладке «Конструктор» ленты инструментов. Указав необходимое имя в поле «Имя таблицы» группы «Свойства».

Установка имени таблицы Excel

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

Итоги

Как же легко добавлять итоги в таблицу. Встаньте в любую ячейку таблицы, откройте вкладку на ленте инструментов «Конструктор» и в группе элементов «Параметры стилей таблиц» выберете галочку «Строка итогов». Всё итог появится внизу таблицы.

Таблицы и формулы Excel

Как видно итоговая сумма появилась только под полем общей стоимости продаж. Нам нужны еще итоги по полю «TotalCost», среднее значение по «DiscountAmount» и посчитать количество по полю «DiscountQuantity» учитывая только значения больше 0.

Для того что бы добавить итог сумму по полю «TotalCost» встаем курсором в строку «Итог» столбца и в выпадающем списке выбираем «Сумма». Так же встаем в столбце «DiscountAmount» на последнюю строку итоги и выбираем в выпадающем списке «Среднее». С количеством по полю «DiscountQuantity» интереснее. Так же в нужном столбце встаем в последнюю строку и в выпадающем списке выбираем «Другие функции». Откроется привычное окно «Мастер функций» в котором в категории «Статистические» выбираем функцию «СЧЕТЕСЛИ».

Выбор функции СЧЕТЕСЛИ

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

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

Нажмем ОК. и наша строка итогов, после всех действий должна принять такой вид:

Результат вставки функции в таблицу Excel

Интересна особенность вставки формулы итогов через саму строку итогов. Вместо вставки функций СРЗНАЧ, СЧЁТ, СЧЕТЗ, МАКС, МИН, ПРОИЗВЕД, СТАНДОТКЛЛОН, СТАНДОТКЛОНП, СУММ, ДИСП, ДИСПР Excel вставит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ с необходимыми параметрами, а если выбирать «Другие функции» то вставлена будет именно та функция, которую выбрали. По этому в строке итогов в трех полях будет функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а в одном поле функция СЧЁТЕСЛИ.

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

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

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

Столбец будем добавлять в конец таблицы. Как Вы уже знаете, если начать ввод в ячейке рядом с таблицей после подтверждения ввода Excel воспримет эту ячейку новым столбцом таблицы и автоматический увеличит таблицу на один столбец присвоим ему при этом стандартное имя.

Добавление столбца в таблицу, вставкой новой формулы

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

Результат вставки формулы в таблицу Excel

Обратите внимание на некоторые особенности ввода формул, мы вводили формулу используя имя столбца таблицы, а не имя столбца листа. На самом деле вводя формулу в ячейке L2, мы могли написать формулу как =F2*G2. Такой вариант тоже допустим, и такая формула так же будет распространена на весь столбец таблицы, но тогда будет теряться удобство работы с таблицей. Формула в которой отражается реальное имя столбца более наглядна чем формула со ссылками. В некоторых формулах можно встретить обозначение столбца как [@UnitPrice] в данном случае символ @ обозначает – «эта строка» и фактический может опускаться при вводе формул.

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

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

Ссылки на таблицы и данные таблицы

Ссылаться на таблицы и на данные таблицы из любого другого места рабочей книги так же просто как добавлять формулу в саму таблицу. Для этого будет использоваться полное имя таблицы или столбца данных. Таблица – универсальный и хороший источник данных для других таблиц и отчетов. Например, если бы у нас таблица содержала только числовые значения, как например результаты каких то наблюдений за показателями, разделенных на несколько столбцов, то что бы просчитать среднее значение всех показаний, достаточно вне таблицы написать формулу =СРЗНАЧ(Таблица1). Где «Таблица1» имя таблицы, в которой содержатся значения, в данном случае мы ссылались бы на всю таблицу целиком. Хотя на самом деле для ссылки в формулах на числовые значения не обязательно что бы таблица содержала только числа. Просто любые не числовые значения будут пропускаться.

Ссылка на столбец таблицы выглядит как Имя_таблицы[имя_столбца]. Так что бы вне таблицы получить сумму всех значений поля SalesAmount можно написать формулу =СУММ(Таблица1[SalesAmount]). То есть что бы сослаться на весь столбец таблицы достаточно указать имя таблицы и имя столбца в квадратных скобках. Такой подход не отменяет стандартных возможностей ссылки на значения таблицы, можно использовать как имя ячейки, например А10 так и диапазон, например А10:С15.

Удобство работы с именами вместо абсолютных ссылок заключается в том, что Вам нет необходимости заботиться о положении данных в таблице, о количестве строк таблицы или том где таблица находится. Ссылка в формулах по именам всегда будет давать верный результат. Укажете имя таблицы, Excel сам найдет ее в рабочей книге, укажете имя столбца Excel сам посчитает, сколько строк в таблице и включит все строки в формулу. Ведь на сколько лучше выглядит формула =СУММА(Таблица1[Сумма_продаж]) чем формула =СУММА(А10:А40). Если Вы не видите, данные Вы не можете точно сказать на что указывает диапазон А10:А40, это тем более может быть не известно другому специалисту, который может работать с книгой вместо Вас, но даже не это главное, если количество строк в таблице изменится то первая формула будет давать правильный результат, а вторую необходимо будет исправить, указав верный диапазон.

Добавить комментарий