Ранее в блоге я упоминал о том, как применил некоторые хитрые функции связки офисных программ для повседневной рабочей задачи по рассылке писем. В этой статье поделюсь примерами еще нескольких полезных функций с примерами. Почему для старых версий? К сожалению сейчас во многих офисах применяются "ломанные" версии именно этих годов. Для информации везде приведу аналогичные функции для более поздних версий, там они усовершенствованы.
1. Функция СМЕЩ (OFFSET) — альтернатива SEQUENCE
Функция СМЕЩ (OFFSET) позволяет динамически изменять диапазоны и создавать массивы данных, аналогичные функции SEQUENCE в новых версиях. Это особенно полезно для создания динамических списков чисел или работы с данными в таблицах.
Синтаксис:
=СМЕЩ(Начальная_ячейка; Смещение_по_строкам; Смещение_по_столбцам; Высота; Ширина)
Пример 1.1: создаём список из 10 чисел, начиная с ячейки A1
=СМЕЩ(A1;0;0;10;1)
При добавлении новых значений диапазон автоматически обновится.
Пример 1.2: имитация SEQUENCE (автономер в столбце)
Если в A1 написать 1, а в A2 формулу:
=A1+1
… и протянуть вниз, получим аналог SEQUENCE.
📌Совет по использованию: используйте СМЕЩ для автоматического обновления диапазонов при добавлении данных.
2. Функция ВПР с МОЖНОСЦЕП (VLOOKUP + CONCATENATE) — поиск по нескольким условиям
Если в вашей работе необходимо искать данные по нескольким критериям, используйте комбинацию функций ВПР и МОЖНОСЦЕП (CONCATENATE). Эта техника позволяет искать значения по нескольким столбцам одновременно
Пример 2.1: найти цену товара, учитывая его код и категорию.
Добавьте в таблице новый столбец с объединением условий:
=A2&B2
Используйте ВПР для поиска:
=ВПР(D2&E2; A:B; 2; ЛОЖЬ)
D2 – код товара, E2 – категория, A:B – диапазон поиска.
📌Совет по использованию: объедините данные в одном столбце, чтобы эффективно искать значения по нескольким условиям.
3. Функция ИНДЕКС + ПОИСКПОЗ — альтернатива XLOOKUP
Функция ИНДЕКС в комбинации с ПОИСКПОЗ является мощным инструментом для поиска данных по конкретным условиям, что делает её отличной альтернативой XLOOKUP.
Пример 3.1: найти цену товара по его имени:
=ИНДЕКС(B2:B10; ПОИСКПОЗ("Яблоки"; A2:A10; 0))
Пример 3.2: если искать по двум критериям, можно сделать так:
=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; (A2:A10="Яблоки") * (B2:B10="Красные"); 0))
📌Совет по использованию: используйте эту комбинацию для более гибкого поиска данных в больших таблицах.
4. Функция СЧЁТЕСЛИМН — подсчёт по нескольким условиям
В старых версиях Excel нет функции COUNTIFS, но её аналог — это СЧЁТЕСЛИМН. С помощью этой функции вы можете подсчитывать значения в таблице, которые соответствуют нескольким критериям.
Пример 4.1: подсчитать количество заказов из Москвы с суммой больше 1000
=СЧЁТЕСЛИМН(A2:A100; "Москва"; B2:B100; ">1000")
📌Совет по использованию: эта функция поможет вам быстро подсчитывать данные, удовлетворяющие сразу нескольким условиям.
5. Функция МАКС(ЕСЛИ) — поиск максимума по условию
Функция МАКС(ЕСЛИ) помогает находить максимальные значения в диапазоне по заданному условию, что является отличной альтернативой MAXIFS в новых версиях Excel.
Пример 5.1: найти максимальную сумму заказа среди клиентов из Москвы
=МАКС(ЕСЛИ(A2:A100="Москва"; B2:B100))
Важно! Формула вводится как Массивная (Ctrl+Shift+Enter).
Заключение
Независимо от того, используете ли вы Excel 2010, 2013 или 2016, вы всё равно можете эффективно работать с данными и анализировать информацию, используя эти секретные функции Excel. Применяйте их в своей повседневной работе и вы заметите, как ваши отчёты и таблицы станут быстрее и удобнее. Для более сложных задач и автоматизации процессов рассмотрите возможность перехода на Excel 365, где доступны ещё более продвинутые функции!
А вы пользуетесь подобными функциями или есть еще интереснее? Напиши в комментариях!