Большинство пользователей Excel считают, что для достижения скорости и эффективности необходимо запомнить сотни сложных формул и функций. Я тоже так думал, пока не обнаружил, что всего три базовые функции могут заменить множество привычных мне формул. Эти три функции радикально изменили мой подход к работе в Excel, сэкономив мне массу времени и сил. Если вы ищете способы упростить свою работу и повысить производительность в Excel, вот несколько ценных советов.
Быстрые ссылки
3. XLOOKUP: современная функция поиска в Excel
Помните, как функция ВПР заставляла вас подсчитывать столбцы для определения индекса? Или когда вы хотели найти данные слева от ключевого столбца и в итоге перестраивали весь лист? Эти проблемы теперь позади.
Функция XLOOKUP — это современное решение Excel для всех Ошибки ВПРОн проще и гибче и доступен в Excel 365, Excel 2021 и более поздних версиях. Вместо того, чтобы работать с номерами столбцов или ограничениями тренда, вам нужно просто указать три параметра: что искать, где искать и что вы хотите вернуть.
Вот основная формула функции XLOOKUP:
2. СУММЕСЛИМН/СЧЁТЕСЛИМН: ваши мощные инструменты условного анализа в Excel
Если вы всё ещё полагаетесь на фильтрацию данных каждый раз, когда вам нужно быстро подсчитать сумму или число, вы тратите больше усилий, чем следовало бы. Функции СУММЕСЛИМН и СЧЁТЕСЛИМН — важные инструменты, которые часто упускаются из виду, особенно при работе с отчётами о продажах, бюджетами и любыми другими наборами данных, где требуется выполнять условные вычисления.
Представьте, что ваш менеджер спрашивает вас: «Каков наш общий объём продаж в Азии товаров стоимостью свыше 150 долларов, заказанных онлайн?» Вместо того, чтобы создавать три фильтра, которые могут привести к ошибке, вы можете ответить одной строкой, используя функцию СУММЕСЛИМН:
Например:
Эта формула складывает все значения в столбце L, где столбец A равен «Азия», столбец J больше 150, а столбец D равен «Онлайн».
Обязательно заключите критерии в кавычки, если вы тестируете текстовые значения.
Функция СЧЁТЕСЛИМН работает таким же образом, за исключением того, что она возвращает количество строк, соответствующих вашим условиям:
Например:
Вместо получения суммы, как в функции СУММЕСЛИМН, вы получите количество заказов из Азии, которые были оформлены онлайн и имели цену за единицу больше 150 долларов США.
Функции СУММЕСЛИМН и СЧЁТЕСЛИМН также поддерживают символы вместо значений. Например, можно посчитать все страны, начинающиеся на «Т», и все товары, кроме фруктов:
Символы вместо значений полезны, но они основаны на чистых данных. Если ваш набор данных содержит несоответствующие заглавные или строчные буквы или скрытые символы, результаты могут выглядеть ошибочными. Быстро наведите порядок в неорганизованном листе Excel Это избавит вас от многих проблем в дальнейшем.
Преимущество функций СУММЕСЛИМН и СЧЁТЕСЛИМН в том, что они не разрушают исходные данные. Вы никогда не разрежете и не повредите исходный набор данных. А если вам нужна более сложная модель, Excel поддерживает до 127 пар критериев.
Итак, независимо от того, проверяете ли вы «Сколько клиентов потратили более 500 долларов США за последние 30 дней?» или «Каков общий доход от онлайн-заказов аксессуаров стоимостью менее 50 долларов США в Северной Америке?», эти две функции мгновенно дадут вам ответ.
1. Функция ФИЛЬТР в Excel: простая и профессиональная фильтрация данных
Функция ФИЛЬТР — мощное и уникальное дополнение к Excel, кардинально меняющее подход к фильтрации данных. Вместо сложных действий, требующих нажатия на меню и настройки условий, функция ФИЛЬТР делает всё это с помощью одной простой формулы:
Предположим, вы хотите отобразить доход от продаж, где цена за единицу превышает 600 долларов:
Или в реальном наборе данных:
Числа, соответствующие вашим критериям, появятся немедленно. Если значение не соответствует критериям, вы можете добавить альтернативное сообщение вместо ошибки:
Как и функция СУММЕСЛИМН, функция ФИЛЬТР не ограничена одним условием. Вы можете использовать логику ИЛИ (хотя бы одно условие должно быть истинным) или логику И (все условия должны быть истинными). Вот несколько простых примеров:
