Большинство пользователей Excel годами используют одни и те же функции: СУММ, ВПР, МАКС, МИН… И даже не подозревают, что в Excel есть инструменты, которые могут в разы упростить жизнь — быстрее считать, обходить ошибки, работать гибче и чище.
В этой статье я разберу 5 полезных, но часто «недооценённых» функций, которые способны заменить десятки стандартных формул.
• Автор: Ольга Пивикова, основатель онлайн-клуба ExcelClub •
1. АГРЕГАТ — одна функция вместо девятнадцати
Функция АГРЕГАТ способна заменить СУММ, СРЗНАЧ, МАКС, МИН и ещё 15 функций.
Но главное — она умеет игнорировать ошибки, скрытые строки и промежуточные итоги.
Когда это полезно?
— У вас есть таблица с ошибками (#Н/Д, #ДЕЛ/0!)
— Данные подтягиваются через ВПР и что-то пропало
— Таблица содержит промежуточные итоги
— Нужно сделать расчёт, но обычные функции ломаются
🔹 Пример 1. Сумма, несмотря на ошибки
В столбце есть числа, но часть ячеек — с ошибками.
СУММ выдаст ошибку, а АГРЕГАТ — нет.
Для этого нужно написать формулу:
=АГРЕГАТ(9; 6; A2:A20)
Где
9 — сумма
6 — игнорировать ошибки
A2:A20 — массив для работы
✅Готово
🔹 Пример 2. Максимальное значение без учёта промежуточных итогов
Если в таблице уже есть промежуточные итоги
Обычный МАКС учтёт их и результат будет неверным. АГРЕГАТ — нет.
Для этого нужно выбрать
=АГРЕГАТ(4; 0; A2:A50)
Где
4 — МАКС
0 — пропускать вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
А2 :А50 — массив для работы вместе с промежуточными итогами
✅ Готово
Если же в ваших исходных данных содержатся и ошибки, и промежуточные итоги, то выбирайте в функции АГРЕГАТ код 3 — в нём самый полный перечень настроек.
2. ВЫБОР — мини-справочник внутри формулы
Эта функция возвращает значение из списка по номеру. Ей можно заменить ВПР, чтобы не заводить отдельную таблицу.
Смотрите, как это работает
🔹 Пример 1. Присвоить оценку по набранным баллам
Есть баллы от 1 до 5, и нужно вывести оценку: ужасно, плохо, нормально, хорошо или отлично.
Вариант 1 - Можно воспользоваться формулой ВПР. Но в этом случае нам понадобится дополнительный справочник, чтобы указать соответствие оценки для разного количества баллов.
=ВПР(B2; $F$2:$G$6; 2; 0)
Вариант 2 - используем всеми забытую функцию ВЫБОР.
=ВЫБОР(A2; "Ужасно"; "Плохо"; "Нормально"; "Хорошо"; "Отлично")
Нажимаем Enter и протягиваем её на весь массив
✅ Готово
Если A2 = 4 → результат «Хорошо»
Если А3 = 2 → результат «Плохо» и т.д.
В итоге, при использовании функции ВЫБОР нам не понадобился дополнительный справочник. Мы прописали его внутри нашей формулы.
🔹 Пример 2. Определить квартал по дате
Здесь кроме функции ВЫБОР мы будем использовать ещё и функцию МЕСЯЦ (поможет нам из любой даты извлечь порядковый номер месяца)
=МЕСЯЦ(A2)
А дальше, в качестве номера индекса, мы будем ссылаться на функцию МЕСЯЦ, и пропишем все возможные варианты. Их у нас 12 (12 месяцев — по 3 в каждом квартале):
Январь, февраль, март — 1
Апрель, май, июнь — 2 и т.д.
Получаем формулу👇
=ВЫБОР(МЕСЯЦ(A2); 1;1;1;2;2;2;3;3;3;4;4;4)
Нажимаем Enter и протягиваем на весь массив.
✅ Готово. Excel вернул номер квартала
А если вы захотите добавить слово КВАРТАЛ к номеру, то просто допишите к формуле
& “ КВАРТАЛ” 👇
=ВЫБОР(МЕСЯЦ(A2); 1;1;1;2;2;2;3;3;3;4;4;4)& “КВАРТАЛ”
✅ Готово.
3. РАЗНДАТ — вычисляем возраст, стаж и сроки проектов
Вы не найдете эту функцию в Мастере функций Excel, если начнете её вводить. Вы не увидите подсказок и расшифровок аргументов. Как будто этой функции не существует в Excel. Но она есть и прекрасно работает!
У этой функции 3 аргумента: начало, окончание и дальше самое важное - это правильно подобрать формат. И чтобы это сделать, нужно знать расшифровку, которую я для вас прикрепляю ниже👇
Если нужен результат в количестве месяцев, то ставим формат "m". Если месяц еще полностью не закончен, то он не будет посчитан, результат будет 0.
Если в количестве лет, то “y”. Если год ещё полностью не закончен, то он тоже не будет посчитан.
🔹 Пример 1. Посчитать стаж сотрудника в годах
Ячейка B2 = Дата приёма: 16.01.2001
Ячейка C2 = Сегодня: 24.06.2023
=РАЗНДАТ(B2; C2; "Y")
Результат: 2 года (2003 год неполный)
🔹 Пример 2. Посчитать стаж сотрудника в месяцах
Ячейка B2 = Дата приёма: 16.01.2001
Ячейка C2 = Сегодня: 24.06.2023
=РАЗНДАТ(B2; C2; "M")
Результат: 29 месяцев (последнией месяц “июнь” не считается, т.к. неполный)
4. РИМСКОЕ — красиво оформляем нумерацию
Приходилось ли вам когда-нибудь использовать римские цифры в Excel? Например, для названия месяцев, кварталов или нумерации пунктов и разделов.
Если число небольшое, например от 1 до 12 месяцев или 4 квартала, то можно и вручную прописать римский вариант цифр. Но что если таких цифр у вас больше или, возможно, вы даже не знаете, как они правильно пишутся в римском варианте?
Оказывается, нам и не нужно это знать, ведь Excel все знает за нас и для этого есть специальная функция. Она так и называется РИМСКОЕ.
🔹 Пример 1. Преобразовать список задач
=РИМСКОЕ(A2)
🔹 Пример 2. Год в римском стиле
=РИМСКОЕ(1992)
Результат: MCMXCII
Функция РИМСКОЕ сможет вам конвертировать практически любое число до 3999 включительно.
5. АРАБСКОЕ — обратный перевод римских чисел
Когда стоит задача преобразовать римские числа в арабские.
🔹 Пример 1. Разбор главы
=АРАБСКОЕ("VIII")
Результат: 8
🔹 Пример 2. Перевод списка
=АРАБСКОЕ(A2)
🔥6. БОНУС: функция ВПР
С малоизвестными функциями разобрались, а как насчёт самой популярной функции в Excel — ВПР?
Так в одной статье с ней и не познакомишь, уж настолько она мощная и настолько в ней много нюансов!
‼️Поэтому я подготовила целый БЕСПЛАТНЫЙ экспресс-курс из 4 видеоуроков с пошаговым разбором и примерами.
После этого прохождения мини-курса вы будете смотреть на задачи и думать:
«Так, я знаю как это сделать за 2 минуты!».
✅ Вы перестанете бояться формул. Поймете, что ВПР — это не ракетостроение, а простая и чёткая логика.
✅ Вы будете знать врага в лицо. Любая ошибка #Н/Д перестанет быть загадкой. Вы будете точно знать её причину и метод исправления за 30 секунд.
✅ Вы автоматизируете самую надоевшую рутину: подстановку данных из одной таблицы в другую. Это освободит вам часы времени.
✅ Вы сможете наконец-то доверять своим отчётам и перестанете перепроверять каждую цифру по десять раз.
Чтобы получить доступ бесплатно — переходите по ссылке прямо сейчас
⭐️ ПОЛУЧИТЬ КУРС БЕСПЛАТНО 👉 https://first.excel-club.ru/vpr
•••
Если статья была полезной — поставьте лайк и сохраните себе, чтобы не потерять.
А в комментариях напишите, какая функция была для вас открытием 👇
•••
Подписывайтесь на мой телеграм-канал 👉 ПОДПИСАТЬСЯ