Создание пользовательских функций при помощи имен

Как правило, имена используются для обращения к диапазону ячеек, реже для определения констант. В настоящей заметке будет рассмотрено создание пользовательских функций при помощи имен. [1] Но сначала пару слов об именовании констант.

Предположим, вы часто используете в расчетах налоговую ставку НДС – 18%. Вместо того чтобы вводить значение 18% (0,18) в формулах, можно просто ввести слово НДС, и Excel автоматически подставит значение 0,18, которому присвоено это имя. Вот как можно это сделать. Пройдите по меню Формулы –> Присвоить имя. В открывшемся окне Создание имени введите имя НДС, в поле диапазон введите =18%, кликните Ok (рис. 1).

Рис. 1. Определение имени для ставки НДС

Скачать заметку в формате Word или pdf, примеры в формате Excel

Теперь в формулах вместо значения 18% можно использовать слово НДС (рис. 2). Вероятно, одно из самых больших преимуществ этого метода заключается в том, что при изменении налоговой ставки можно просто переопределить значение НДС, пройдя по меню Формулы –> Диспетчер имен, или набрав Ctrl+F3, и изменить соответствующее значение (рис. 3).

Рис. 2. Использование имени НДС в расчетах

Рис. 3. Изменение ставки НДС

В поле Диапазон при определении имени можно также ввести формулу. Предположим, вы хотите создать имя, которое, если ввести его в ячейку, будет автоматически возвращать сумму значений в десяти ячейках непосредственно над этой ячейкой. Выделите ячейку А11 и пройдите по меню Формулы –> Присвоить имя. В поле Имя введите имя Сум10. В поле Диапазон введите формулу =СУММ(А1:А10), кликните Ok (рис. 4). Проверьте, как работает формула (рис. 5). Если начать вводить =сум, то появится подсказка, предлагающая функцию Сум10 (рис. 5а). Если принять подсказку, щелкнув на Сум10, то будет показана область суммирования (рис. 5б). Проверьте, что функция Сум10 суммирует значения только в 10 ячейках, а не во всем столбце (рис. 5в).

Рис. 4. Определение пользовательской функции с помощью имени

Рис. 5. Функция Сум10 в действии

Использование метода пересечения

Комбинируя этот трюк с одной из стандартных, хотя и малоизвестных возможностей Excel — методом пересечения, — можно создавать сложные функции поиска. Если вы не знаете, как работает метод пересечения, вот небольшой пример для ознакомления. Создайте таблицу (рис. 6), выделите ее и пройдите по меню Формулы –> Определение имени –> Создание из выделенного фрагмента. Удостоверьтесь, что установлены флажки в строке выше и в столбце слева, и щелкните Оk (рис. 6а). Выделите любую ячейку за пределами таблицы и введите =Fred Title (рис. 6б). Вы должны увидеть правильное обращение для имени Fred  – Dr. Важно, чтобы между словами Fred и Title был пробел, так как именно его Excel понимает, как оператор пересечения.

Рис. 6. Пример использования пересечения

Предположим у вас есть таблица (рис. 7). Выделите ее и пройдите по меню Формулы –> Определение имени –> Создание из выделенного фрагмента. Удостоверьтесь, что установлены флажки в строке выше и в столбце слева, и щелкните Оk.

Рис. 7. Таблица и диалоговое окно Создание имени из выделенного фрагмента

Если после этого вы откроете Диспетчер имен, то будете удивлены, сколько имен создал Excel (рис. 8).

Рис. 8. Результат применения команды Определение имени –> Создание из выделенного фрагмента

Во-первых, вы получили возможность использовать предопределенные имена – Фамилия, Зарплата, Должность – для нахождения пересечений (рис. 9). Во-вторых, пересечениям можно присвоить новые имена, и использовать их в дальнейшем.

Рис. 9. Определение зарплаты методом пересечений

Пройдите по меню Формулы –> Присвоить имя и введите параметры, как указано на рис. 10. Вы получите возможность использовать имя ЗарплатаПопова в любом месте книги Excel. Поэкспериментируйте с пересечениями, чтобы узнать, как они наилучшим образом будут работать в ваших проектах.

Рис. 10. Создание имени на основе формулы пересечения

[1] По материалам книги Д.Холи, Р.Холи. Excel 2007. Трюки, стр. 126–128