Почти в каждом офисе есть файл, который кто-то каждую неделю обновляет вручную. Цены ищут по одной в прайс-листе, адреса по одному копируют в письмо, повторяющихся клиентов вычёркивают на глаз. Ничего сложного, но на это уходит полдня, и каждый ручной шаг даёт ещё один шанс скопировать не ту строку.
Для большинства таких задач в Excel уже есть формула. Ниже пять таких формул: к каждой небольшой пример, что она возвращает и какую ошибку с ней делают чаще всего. Данные в примерах выдуманные.
Сначала о версии, названиях и разделителе
- XLOOKUP, UNIQUE и FILTER есть в Excel 2021, Excel 2024, Microsoft 365 и Excel в браузере. В Excel 2019 и более старых их нет, и в ячейке появится
#ИМЯ?. - TEXTJOIN есть начиная с Excel 2019, IFERROR с Excel 2007.
- Все пять работают и в Google Таблицах.
- В русском Excel функции называются по-русски: XLOOKUP это ПРОСМОТРX, TEXTJOIN это ОБЪЕДИНИТЬ, UNIQUE это УНИК, FILTER это ФИЛЬТР, IFERROR это ЕСЛИОШИБКА. Надёжнее всего набрать первые буквы и выбрать функцию из подсказки Excel.
- В примерах аргументы разделены точкой с запятой: так Excel работает при региональных настройках, которые стоят на большинстве компьютеров в Молдове. Если ваш Excel ждёт запятую, он сообщит, что с формулой что-то не так; тогда замените точки с запятой на запятые.
XLOOKUP (ПРОСМОТРX): цена по коду товара с другого листа
В заказе указаны только коды товаров, а цены лежат на другом листе. Кто-то находит каждый код и переносит цену вручную.
На листе «Produse» код в столбце A, название в B, цена в леях в C:
- P-101, бумага A4 (500 листов), 85
- P-102, тонер для лазерного принтера, 640
- P-103, папка-регистратор, 32
На листе заказа коды начинаются с A2. В B2:
=XLOOKUP(A2; Produse!A:A; Produse!C:C; "lipsă")
В русском Excel та же формула выглядит так: =ПРОСМОТРX(A2; Produse!A:A; Produse!C:C; "lipsă").
Для P-102 в ячейке будет 640. Для кода, которого нет в прайсе, например P-150, вместо #Н/Д появится «lipsă» (по-румынски «отсутствует»; можно написать любой текст). Протяните формулу вниз по столбцу, и цена появится в каждой строке.
В отличие от ВПР (VLOOKUP), не нужно считать столбцы, а столбец с ценой может стоять где угодно, даже левее кода. По умолчанию ПРОСМОТРX ищет точное совпадение.
Частая ошибка. Код явно есть в прайсе, а формула всё равно пишет «lipsă». Обычно в одном из двух мест в конце стоит пробел или код записан числом на одном листе и текстом на другом. Проверьте формулой =ДЛСТР(A2): если символов больше, чем видно глазом, есть лишние пробелы, и их уберёт СЖПРОБЕЛЫ.
TEXTJOIN (ОБЪЕДИНИТЬ): весь столбец в одной ячейке
Письмо нужно отправить всем из списка, а адреса записаны в столбце. Копировать их по одному в поле «Кому» долго, и какой-нибудь адрес обязательно потеряется.
Столбец B, начиная со строки 2:
- ana@exemplu.md
- office@firma-a.md
- (пустая ячейка)
- contabil@firma-b.md
=TEXTJOIN(", "; TRUE; B2:B50)
В русском Excel: =ОБЪЕДИНИТЬ(", "; ИСТИНА; B2:B50).
Получится одна ячейка с текстом ana@exemplu.md, office@firma-a.md, contabil@firma-b.md, который можно сразу вставить в поле получателей. Первый аргумент задаёт, что ставить между значениями (здесь запятая с пробелом), а ИСТИНА велит пропускать пустые ячейки. Если письмо уходит клиентам, вставляйте список в поле «Скрытая копия», чтобы они не видели адреса друг друга.
Частая ошибка. С ЛОЖЬ вместо ИСТИНА каждая пустая ячейка оставляет двойную запятую, и некоторые почтовые программы не принимают такой список. Кроме того, в одной ячейке помещается не больше 32 767 символов; если результат длиннее, функция выдаст #ЗНАЧ!.
UNIQUE (УНИК): список клиентов без повторов
В журнале продаж одна строка на каждый счёт, поэтому один и тот же клиент встречается десять раз. Чтобы получить список клиентов, кто-то копирует столбец, удаляет повторы, а через месяц делает всё заново.
Столбец C, строки со 2 по 500:
- Alfa SRL
- Beta Grup
- Alfa SRL
- Gamma Trade
- Beta Grup
=UNIQUE(C2:C500), в русском Excel =УНИК(C2:C500).
Получится список: Alfa SRL, Beta Grup, Gamma Trade, каждый по одному разу, в том порядке, в каком они впервые встречаются. Формулу пишут в одну ячейку, а результаты сами заполняют ячейки под ней (Microsoft называет это динамическим массивом). Если в диапазоне появится новый клиент, список обновится сам. Чтобы отсортировать его по алфавиту, оберните формулу в СОРТ: =СОРТ(УНИК(C2:C500)).
Частая ошибка. #ПЕРЕНОС! (в английском Excel #SPILL!). Формуле нужны свободные ячейки под ней, и одно-единственное значение на пути блокирует весь результат. Очистите эти ячейки или перенесите формулу. И ещё две мелочи: УНИК не исправляет написание, так что «Alfa SRL» и «SRL Alfa» останутся двумя клиентами, а пустые строки диапазона дадут 0 в конце списка.
FILTER (ФИЛЬТР): только счета больше 1 000 леев
Директор хочет видеть только крупные счета. Кто-то ставит фильтр, копирует оставшиеся строки на новый лист и повторяет это каждый раз, когда приходят новые счета.
Счета в столбцах с A по D (номер, дата, клиент, сумма в леях):
- F-0145, 03.11, Alfa SRL, 850
- F-0146, 04.11, Beta Grup, 2400
- F-0147, 05.11, Gamma Trade, 1150
- F-0148, 06.11, Alfa SRL, 600
=FILTER(A2:D500; D2:D500>1000), в русском Excel =ФИЛЬТР(A2:D500; D2:D500>1000).
Вы получите строки F-0146 и F-0147 со всеми четырьмя столбцами, начиная с ячейки с формулой. Новый счёт больше 1 000 леев сам появится в результате. Условием может быть любое сравнение: D2:D500>=5000 или C2:C500="Alfa SRL".
Частая ошибка. Если ни один счёт не подходит, ФИЛЬТР возвращает ошибку #CALC!. Третий аргумент задаёт, что показать в этом случае: =ФИЛЬТР(A2:D500; D2:D500>1000; "нет"). И следите, чтобы диапазон условия был той же высоты, что и таблица: A2:D500 вместе с D2:D400 даст ошибку.
IFERROR (ЕСЛИОШИБКА): отчёт без #Н/Д и #ДЕЛ/0!
В отчёте для директора есть столбец средней цены, и там, где количество равно нулю или код не нашёлся, в ячейке стоит #ДЕЛ/0! или #Н/Д. Перед печатью кто-то стирает их вручную.
Продажи в леях в столбце B, проданные штуки в столбце C:
- 12000, 40
- 3500, 0
- 7200, 30
=IFERROR(B2/C2; 0), в русском Excel =ЕСЛИОШИБКА(B2/C2; 0).
В первой строке получится 300, во второй 0 вместо #ДЕЛ/0!, в третьей 240. Второй аргумент задаёт, что показать вместо ошибки: 0, пустую ячейку ("") или короткое слово.
Частая ошибка. ЕСЛИОШИБКА прячет все ошибки подряд, в том числе те, которые вам нужно увидеть. Если формула ссылается на удалённый лист (#ССЫЛКА!) или в названии функции опечатка (#ИМЯ?), ячейка спокойно покажет 0, и отчёт будет выглядеть правильно. Сначала убедитесь, что формула работает, и только потом оборачивайте её. Подумайте и о том, что ноль сделает с итогами: среднее по столбцу, где ошибки стали нулями, получится меньше. Часто честнее поставить "": СРЗНАЧ такие ячейки пропускает.
Формулы можно сочетать
Одну формулу можно вложить в другую. Все номера счетов больше 1 000 леев в одной ячейке:
=TEXTJOIN(", "; TRUE; FILTER(A2:A500; D2:D500>1000))
В русском Excel: =ОБЪЕДИНИТЬ(", "; ИСТИНА; ФИЛЬТР(A2:A500; D2:D500>1000)). Отсортированный список клиентов выше устроен так же.
Коротко
- Значение из другой таблицы по коду: ПРОСМОТРX (XLOOKUP).
- Столбец в одной ячейке: ОБЪЕДИНИТЬ (TEXTJOIN).
- Список без повторов: УНИК (UNIQUE).
- Только строки, которые подходят под условие: ФИЛЬТР (FILTER).
- Отчёт без кодов ошибок: ЕСЛИОШИБКА (IFERROR), но осторожно.
Когда таблица становится базой данных компании
Эти формулы хорошо работают, пока одним файлом занимается один человек. Всё меняется, когда таблица становится базой данных компании: три человека правят копии одного и того же файла, кто-то пересылает по почте «clienti_final_2.xlsx», и уже никто не знает, в какой копии сегодняшняя оплата. Формулы здесь не помогут, потому что вопрос в другом: кто, что и где изменил.
Тогда и окупается CRM или небольшая автоматизация: клиенты, счета и история хранятся в одном месте, каждый видит свою часть, а списки из этой статьи превращаются в сохранённый фильтр или отчёт. Мы уже писали о том, как перенести клиентскую базу из Excel в CRM и ничего не потерять. Если хотите обсудить ваши таблицы, загляните на страницу о CRM-системах или напишите нам.