Трюк №41. Как создать пользовательскую функцию в Excel при помощи имен

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

Предположим, у вас есть налоговая ставка 10%, которую вы используете в рабочей книге для различных вычислений. Вместо того чтобы вводить значение 10% (0,1) во всех формулах, для которых необходима эта налоговая ставка, можно просто ввести слово TaxRate, и Excel автоматически подставит значение 0,1, которому присвоено это имя. Вот как можно это сделать.

Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define) и в поле Имя (Names in workbook) введите TaxRate. В поле Формула (Refers To) введите =0,1 и щелкните на кнопке Добавить (Add). Теперь в любую ячейку можно ввести любую формулу, а вместо значения 10% можно использовать слово TaxRate. Вероятно, одно из самых больших преимуществ этого метода заключается в том, что, когда налоговая ставка увеличивается или уменьшается и необходимо отразить в формулах новое значение, можно просто выбрать команду Вставка → Имя → Присвоить (Insert → Name → Define), а затем выбрать имя TaxRate и изменить соответствующее значение.

Чтобы сделать очередной шаг в этой концепции, в качестве диапазона в поле Формула (Refers To) можно указывать формулы, а не адреса ячеек или постоянные значения. Предположим, вы хотите создать имя, которое, если ввести его в ячейку, будет автоматически возвращать сумму значений в десяти ячейках непосредственно над этой ячейкой.

Выделите ячейку АИ на любом рабочем листе и выберите команду Вставка → Имя → Присвоить (Insert → Name → Define). В поле Имя (Names in workbook) введите имя Total. В поле Формула (Refers to) введите следующую формулу: =SUM(A1:A10), в русской версии Excel =СУММ(А1:А10). Щелкните на кнопке Добавить (Add ), затем щелкните на кнопке ОК. В любой столбец, начиная со строки 1, введите 10 чисел. Теперь перейдите к строке И в том же столбце и введите формулу =Total. Имя Total автоматически вернет сумму значений, которые вы только что ввели в 10 ячеек А1:А10.

Чтобы создать похожую именованную формулу, которая не ограничивается 10 ячейками, а использует все ячейки непосредственно над той строкой, где вы ввели =Total, сделайте следующее:
Выделите ячейку В11 и выберите команду Вставка → Имя → Присвоить (Insert → Name → Define). Щелкните имя Total. Посмотрите в поле Формула (Refers to): вы должны увидеть формулу =СУММ(В1:В10) (=SUM(B1:B10)). Так вы создаете именованные формулы. Другими словами, поскольку вы не указали абсолютные ссылки на столбцы для исходного имени Total, эта формула всегда ссылается на тот столбец, в котором находится. Теперь щелкните поле Формула (Refers to) и измените формулу следующим образом: =SUM(B$1:B10), в русской версии Excel =СУММ(В$1:В10). Щелкните на кнопке Добавить (Add), затем щелкните на кнопке ОК.

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

Комбинируя этот трюк с одной из стандартных, хотя и малоизвестных возможностей Excel — методом пересечения, — можно создавать сложные функции поиска. Если вы не знаете, как работает метод пересечения, вот небольшой пример.

В ячейку А1 введите заголовок Name, в ячейку В1 — Pay, в ячейку Cl — Title. В ячейку А2 введите Bill, в ячейку A3 — Fred. В ячейку В2 введите 10, в ячейку ВЗ — 20, в ячейку С2 — Mr, в ячейку СЗ — Dr. Теперь выделите диапазон А1:СЗ и выберите команду Вставка → Имя → Создать (Insert → Name → Create). Удостоверьтесь, что установлены флажки для строки выше и столбца слева, и щелкните на кнопке ОК.

Выделите любую ячейку за пределами таблицы и введите =Fred Title. Вы должны увидеть правильное обращение для имени Fred.
[stextbox id=»warning»]Важно, чтобы между словами Fred и Title был пробел, так как именно его Excel понимает как оператор пересечения.[/stextbox]
Основываясь на таком принципе, можно сочетать эту возможность с возможностями именованных формул Excel, чтобы делать электронные таблицы не только простыми в работе, но и легко читаемыми и удобными для восприятия.

Предположим, в вашей электронной таблице есть таблица, на основе которой вы создаете имена в Excel. Создав имена для этой таблицы, вы увидите, что Excel автоматически вставляет символ подчеркивания вместо пробелов между двумя или более словами. Это происходит потому, что имена именованных диапазонов не могут содержать пробелы. Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define) и в поле Имя (Names in workbook) введите FredsPayRate. В поле Формула (Refers to) введите =Fred_Jones Pay_Rate и щелкните на кнопке Добавить (Add).

Теперь в любой ячейке за пределами таблицы введите формулу =FredsPayRate. Будет автоматически возвращена ставка заработной платы для сотрудника Fred. Поэкспериментируйте с пересечениями, чтобы узнать, как они наилучшим образом будут работать в ваших проектах.

Top