Готовите отчёты, ведёте список продаж или работаете с бухгалтерскими таблицами? Эта статья — для тех, кто знает Excel на базовом уровне, но хочет работать быстрее и аккуратнее. Описание 6 инструментов ниже основано на официальных страницах поддержки Microsoft: для каждого инструмента мы рассказываем, что он делает, приводим пример Microsoft и даём практический совет.
Функции поиска: XLOOKUP и VLOOKUP
1. Как работает XLOOKUP?
Согласно объяснению Microsoft, функция XLOOKUP предназначена для поиска данных в таблице или диапазоне по строке. Например, вы хотите найти в длинном списке код товара и узнать название товара из той же строки.
Важная особенность этой функции: если дополнительный параметр не задан, XLOOKUP ищет точное совпадение (exact match), то есть находит именно то значение, которое вы ищете.
2. VLOOKUP: не забывайте главное правило
Чтобы функция VLOOKUP работала правильно, искомое значение должно находиться в первом столбце диапазона. На той же странице Microsoft рекомендует попробовать функцию XLOOKUP как улучшенный вариант, который работает в любом направлении. Если в вашей версии Excel есть XLOOKUP, её стоит опробовать.
Условные вычисления: SUMIF и IF
3. SUMIF: суммируйте только то, что нужно
Функция SUMIF суммирует в диапазоне только те значения, которые соответствуют заданному вами условию. Пример Microsoft:
=SUMIF(B2:B25,">5")
Эта формула суммирует в диапазоне B2:B25 только числа больше 5. Обратите внимание, что в примере условие (">5") записано в кавычках. По той же логике можно, например, суммировать только платежи больше определённой суммы.
4. IF: если условие истинно — один результат, если ложно — другой
Функция IF выполняет логическое сравнение значения с ожидаемым результатом. Поэтому у неё может быть два результата: один — если условие истинно, другой — если ложно. Пример Microsoft:
=IF(C2="Yes",1,2)
Эта формула возвращает 1, если значение в ячейке C2 равно "Yes", и 2 в противном случае. Тем же способом можно, например, на основе записи в столбце оплаты показывать в отчёте одну из двух разных отметок.
Анализ и упорядочивание данных: PivotTable и Flash Fill
5. PivotTable: обобщение большой таблицы
PivotTable (сводная таблица) — мощный инструмент для вычисления, обобщения и анализа данных. Например, с его помощью можно обобщить длинный список продаж по товарам.
Чтобы начать:
- Убедитесь, что данные расположены в столбцах с одной строкой заголовков.
- В англоязычном интерфейсе выберите Insert > PivotTable.
Если заранее разложить данные по аккуратным столбцам, таблица к тому же станет понятнее для ваших коллег.
6. Flash Fill: мгновенное заполнение с помощью Ctrl+E
Flash Fill (мгновенное заполнение) автоматически заполняет данные, когда Excel улавливает образец, то есть закономерность. Пример Microsoft: разделить имя и фамилию, записанные вместе в одном столбце. Если автоматически не срабатывает, Flash Fill можно запустить из меню Data или сочетанием клавиш Ctrl+E. Хорошая привычка — просмотреть автоматически заполненный результат в нескольких строках.
Какой инструмент для чего?
| Инструмент | Для чего нужен |
|---|---|
| XLOOKUP | Поиск данных в таблице по строке (по умолчанию — точное совпадение) |
| VLOOKUP | Поиск данных, когда искомое значение находится в первом столбце диапазона |
| SUMIF | Суммирование только тех значений, которые соответствуют условию |
| IF | Возврат одного из двух результатов в зависимости от условия |
| PivotTable | Вычисление, обобщение и анализ данных |
| Flash Fill | Автоматическое заполнение данных при обнаружении закономерности |
Если вы только начинаете изучать Excel, начните с IF и SUMIF, затем переходите к функциям поиска, а после этого — к PivotTable. Если пробовать каждый инструмент на своей реальной таблице, его легче запомнить.
Кратко
- XLOOKUP ищет в таблице по строке и, если дополнительный параметр не задан, находит точное совпадение; в Excel 2016 и Excel 2019 этой функции нет.
- Для VLOOKUP искомое значение должно находиться в первом столбце диапазона.
- SUMIF суммирует только значения, соответствующие условию, а IF возвращает один из двух результатов в зависимости от условия.
- Для PivotTable данные должны быть расположены в столбцах с одной строкой заголовков.
- Если Flash Fill не срабатывает автоматически, запустите его сочетанием Ctrl+E.
В Estavor Академии курсы офисных программ проводятся только онлайн — в индивидуальном или групповом формате. Если у вас есть вопрос по Excel, напишите в WhatsApp: +994 77 754 10 12.
📚 Источники
🌐 Оригинал статьи — на азербайджанском языке; официальные источники доступны на азербайджанском языке.
Другие статьи
Уровни владения языком: что значат A1–C2? (английский и русский)
Что значат A1, B2, C1? Уровни владения языком по CEFR и соответствие им уровней ТРКИ (TORFL) по русскому языку — для изучающих английский и русский.
Основы бухучёта: активы, обязательства, капитал, доходы и расходы
Что такое активы, обязательства, капитал, доходы и расходы? Простые определения по Концептуальным основам МСФО (IFRS), примеры и краткая заметка о 1С.