Привет, дорогой читатель! 👋
Мы с вами уже знакомы с Диспетчером имён — обычно он используется для того, чтобы дать понятное название диапазону ячеек или константе. Но знаете ли вы, что этот мощный инструмент способен на гораздо большее? Сегодня мы научимся создавать собственные компактные функции Excel без единой строчки кода, макросов и без LAMBDA (которая доступна только в новых версиях). Наши рецепты будут работать в любом Excel от 2007 до 2021.
Представьте: вместо громоздкой, трудночитаемой конструкции =ЛЕВСИМВ(A1; ПОИСК(","; A1)-1) вы пишете простое и понятное =Город(A1). Это не фантастика, это реальность, доступная каждому. Поехали! 🚀
🧠 Что такое именованные формулы и зачем они нужны?
Обычно в Диспетчере имён мы храним ссылки на диапазоны: =Лист1!$A$1:$B$10. Но никто не мешает нам сохранить туда любую формулу, которая будет вычисляться каждый раз при обращении. Это и есть именованная формула — ваш персональный строительный блок для создания мощных и лаконичных решений.
Их главное преимущество — многократное использование без необходимости копировать и вставлять одну и ту же сложную логику по всему листу. Достаточно один раз определить формулу в Диспетчере имён, дать ей понятное имя, и затем использовать её так же легко, как стандартные функции вроде СУММ или ВПР.
🛠️ Классический способ: создаём свою первую функцию
Этот метод работает в любой версии Excel (2007 и новее). Давайте разберём его на простом примере.
Допустим, у нас есть список городов в формате "Город, Регион" (например, "Москва, Центральный"). Нам нужно извлекать из него только название города. Вместо того чтобы каждый раз писать формулу, мы создадим для этого специальную именованную формулу Город.
- Откройте Диспетчер имён. Перейдите на вкладку «Формулы» → «Диспетчер имён». Или нажмите сочетание клавиш Ctrl + F3.
- Нажмите «Создать». В поле «Имя» введите, например, Город.
- Самое важное — поле «Диапазон» (или «Формула»). Сюда мы и вставим нашу логику: =ЛЕВСИМВ(Лист1!A1; ПОИСК(","; Лист1!A1)-1).
💡 Секрет относительности. Обратите внимание: в формуле нет знака $. Это превращает нашу функцию в относительную. Если вы введёте на листе =Город в ячейке B2, формула возьмёт данные из A2. Если в B5 — из A5 и так далее. Это ключевой момент для создания по-настоящему полезных функций.
4. Нажмите ОК.
Теперь в любой ячейке листа вы можете написать =Город, и она автоматически «посмотрит» на ячейку слева, выделит из неё всё до запятой и вернёт название города. Красиво и просто!
📝 Расширяем возможности: функция с двумя аргументами
А что, если нам нужно, чтобы функция брала данные не из соседней ячейки, а из любой указанной? К сожалению, классические именованные формулы не умеют принимать аргументы. Но есть хитрость: использовать функцию СМЕЩ (OFFSET) или ИНДЕКС, чтобы «подтянуть» значение из нужного места.
Пример: расчёт НДС (налога)
Допустим, вы часто считаете НДС (22%) от суммы. Создадим именованную формулу НДС, которая будет брать сумму из ячейки справа.
- Диспетчер имён → Создать → Имя: НДС
- Формула: =Лист1!A1*0,22 (предполагаем, что сумма находится в ячейке слева от той, где вы пишете =НДС)
Теперь, если в ячейке B1 вы напишете =НДС, она умножит содержимое A1 на 0,22. Если скопируете формулу в B2 — умножит A2.
Но это неудобно: приходится помнить, откуда функция берёт данные (Что колонка с НДС следует за колонкой цены без НДС). Лучше использовать относительную ссылку на столбец, но не на строку. Например, если ваши суммы всегда в столбце B, можно определить НДС как =B1*0,22 (без знака $ перед 1). Тогда в строке 5 формула =НДС возьмёт B5.
Пример: извлечение домена из email
У вас есть список email-адресов в столбце A. Хотите получать только домен (часть после @). Создаём именованную формулу Домен:
=ПРАВСИМВ(Лист1!A2; ДЛСТР(Лист1!A2)-ПОИСК("@"; Лист1!A2))
Теперь =Домен в ячейке B1 вернёт домен из A1. И никаких копипастов формулы!
⚠️ Подводные камни классического метода
Этот способ — отличный трюк, но он не лишён недостатков:
- Только один «неявный» аргумент. Функция может ссылаться только на одну ячейку (относительно той, где вызвана). Нельзя передать два разных значения.
- Нестабильность. Такие формулы считаются «летучими» (volatile), что в больших объёмах может замедлить работу книги.
- Сложность отладки. Если в длинной формуле допущена ошибка, найти и исправить её в маленьком окошке Диспетчера имён довольно проблематично.
- Отсутствие автоподсказок. При вводе =Город Excel не покажет всплывающую подсказку с описанием.
Тем не менее, для многих повседневных задач это идеальное решение: просто, быстро и без VBA.
🧪 Ещё примеры для вдохновения
1. Форматирование телефонного номера
Исходные данные: 89123456789. Нужно получить +7 (912) 345-67-89. Создаём именованную формулу Телефон:
="+7 ("&ПСТР(Лист1!A1;2;3)&") "&ПСТР(Лист1!A1;5;3)&"-"&ПСТР(Лист1!A1;8;2)&"-"&ПСТР(Лист1!A1;10;2)
Теперь =Телефон в соседней ячейке превратит голую строку в красивый номер.
2. Проверка, является ли значение числом
Создадим =ЭтоЧисло, которая возвращает "Да"/"Нет":
=ЕСЛИ(ЕЧИСЛО(Лист1!A1); "Да"; "Нет")
3. Расчёт возраста по дате рождения
Пусть в столбце A — дата рождения. Хотим получить возраст на текущую дату. Формула Возраст:
=РАЗНДАТ(Лист1!A1; СЕГОДНЯ(); "Y")
Теперь =Возраст в ячейке B1 покажет полное количество лет.
💡 Где это пригодится в жизни?
- Сложные бизнес-формулы. Например, расчёт премии сотрудника, зависящей от KPI.
- Текстовые преобразования. Извлечение кода из артикула, очистка имён, форматирование адресов.
- Инженерные и научные расчёты. Часто используемые физические или математические формулы.
- Конвертация единиц измерения. Создайте функцию =СМ_В_ДЮЙМЫ(), и вы больше никогда не будете путаться в переводе.
💎 Заключение
Диспетчер имён — это не просто хранилище диапазонов. Это мощный инструмент для создания собственных функций, который работает во всех версиях Excel без всяких LAMBDA. Он превращает громоздкие, трудночитаемые конструкции в элегантные и понятные формулы.
Попробуйте прямо сейчас. Вспомните какую-нибудь длинную формулу, которую вы часто используете, и «упакуйте» её в именованную формулу. Уверен, вы сразу почувствуете разницу. 😊
🔥 Понравилась статья? Ставьте лайк 👍 и подписывайтесь на канал!
А в комментариях напишите, какую собственную функцию вы мечтаете создать? Самые интересные идеи разберём в следующих выпусках! 👇
⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇
Материалы по Эксель. Содержание данного канала:
https://dzen.ru/a/ZhpQXTxmQDShWlXf
⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆