Сегодня у нас юбилейный 50-й урок. И в честь этого, думаю, самое время вспомнить, что наши уроки посвящены изучению формул в Excel. Однако, ряд последних статей был посвящен, скорее, больше теоретическим вопросам и изучению функционала Excel. Пришло время вернуться к истокам.
Сегодня мы изучим, как можно ссылаться в формуле на таблицу или её элементы.
Давайте начнём с формулы, которая будет ссылаться на одну ячейку в таблице. В одном из прошлых уроков мы с вами создали таблицу [tblКлиентскиеПродажи].
Нам необходимо в столбце C посчитать итоговую сумму с учётом скидки, если сумма заказа превышает порог заказа. Если не превышает, то оставить ту же сумму.
Для этого в ячейку C2 введём следующую формулу:
=ЕСЛИ([@[Сумма заказа]]>=rngПорог;[@[Сумма заказа]]*(1-rngСкидка);[@[Сумма заказа]])
Страшно? Ничего страшного) Давайте разберём её.
Если число в колонке "Сумма заказа" больше или равно числа в ячейке "Порог заказа", то изменяем его на процент указанный в ячейке "Скидка", иначе оставляем его без изменений. Вспоминаем прошлые уроки. Тут у нас и функция ЕСЛИ, и ссылки на именованные диапазоны, и вычисления процентов, и новое - ссылки на элементы таблицы.
При вводе формулы, когда мы выделяем мышью ячейку B2, чтобы сослаться на неё, то она появляется в формуле в виде [@[Сумма заказа]], так как является частью таблицы.
Сумма заказа - это название столбца. Символ @ означает, что значение относится к той же строке в таблице, в которой вводится формула. И всё заключено в квадратные скобки. Обратите внимание, что внутри квадратных скобок название столбца "Сумма заказа" тоже заключено в квадратные скобки. Это потому, что оно состоит из двух слов. Если бы оно состояло из одного слова, то выглядело бы вот так: [@Сумма].
Ну а имена диапазонов rngПорог и rngСкидка ссылаются на ячейки F2 и F4.
Как только мы ввели формулу и нажали Enter, то столбец автоматически заполнился до конца. Нам не нужно было копировать или протаскивать формулу вниз вручную.
Почему так произошло? Обратите внимание, какую бы ячейку в столбце С мы бы не выбрали, формула в каждой из них будет выглядеть абсолютно одинаково.
Если бы мы в качестве ссылки в формуле использовали диапазон, то формулы в ячейках отличались бы. Они ссылались бы на B3, B4, B5 и так далее. Но этого не происходит, благодаря символу @, который позволяет ссылаться на ту же строку, где располагается формула. Благодаря этому также формула автоматически заполняется вниз.
Если вы будете ссылаться в таблице на ячейку, которая находится в отличной от формулы строке, то в формуле будет использована обычная ссылка на ячейку. Так, если при вводе формулы в ячейке С2 вы сошлетесь на ячейку В3, то будет использована ссылка на В3, так как между двумя разными строками (2 и 3) нет связи, которую Excel мог бы распознать.
Давайте посмотрим ещё один пример использования @.
У нас есть ещё одна таблица [tblТовары]. В первой колонке список товаров, которые мы продаем, во второй - сумма выручки за каждый товар.
Нам необходимо в столбце [Процент] написать формулу, которая посчитает долю от общей суммы в выручке каждого товара.
Для этого в ячейке С2 пишем формулу:
=[@Сумма]/СУММ([Сумма])
В этой формуле используется две ссылки. Одна, вида [@Сумма], ссылается на сумму выручки по одному товару. Вторая, [Сумма] (без знака @), на столбец.
Благодаря знаку @, формулы становятся очень читаемы. Можно для себя заменить его на слово "этот", и тогда формулу можно будет прочитать как "эту выручку разделить на сумму выручек".
Вот, что у нас получилось.
Для более удобного отображения данных отформатируем столбец С в процентный формат.
На сегодня всё. В следующий раз мы с вами изучим ссылочные элементы таблиц, такие как заголовки, итоговая строка, колонки и т.д.
Не переключайтесь;)