Как писать проверяемые формулы Excel с помощью ИИ
На таблице из шести заказов запросите у ИИ формулу SUMIFS, проверьте ожидаемый итог и протестируйте изменения до применения к своим данным Excel.
Вам нужна стоимость оплаченных онлайн-заказов. ИИ-помощник может предложить формулу Excel, но просьба «сложи продажи» не отвечает на два вопроса: какие заказы считать и что означает столбец суммы.
Начните с маленькой таблицы, итог которой можно вычислить самостоятельно. Попросите формулу, объяснение и список включённых строк. Затем измените входное значение и проверьте ожидаемую реакцию. Так вы проверите формулу до переноса в большую книгу.
Явно задайте правило включения
В этом вымышленном наборе каждая строка — один заказ. Amount — полная стоимость заказа в USD, а не цена единицы. Строка учитывается, только если Channel равен Online и Status равен Paid. Ноль — записанная сумма; отсутствие суммы потребовало бы расследования.
Вставьте блок с табуляцией в ячейку A1 пустого листа. Убедитесь, что заголовки заняли A1:D1, а шесть заказов — строки 2–7. Если всё попало в столбец A, разделите вставленный текст по табуляции.
Order Channel Status Amount
O101 Online Paid 120
O102 Store Paid 80
O103 Online Pending 60
O104 Online Paid 90
O105 Online Refunded 40
O106 Online Paid 0
В упражнении оставьте метки образца неизменными, даже если интерфейс Excel на другом языке. Текстовые критерии формулы относятся к содержимому ячеек, а не к языку интерфейса.
Без ИИ отметьте подходящие строки: O101, O104 и O106. Их сумма — 120 + 90 + 0 = 210. Оплаченный заказ в магазине не учитывается, как и ожидающий оплаты и возвращённый онлайн-заказы.
Запросите формулу, а не только итог
Для этого шага достаточно текстового помощника; доступ к реальной рабочей книге не нужен. Передайте маленькую вымышленную таблицу и адреса ячеек:
Я использую Excel. Заголовки находятся в A1:D1, данные — в A2:D7.
A — Order, B — Channel, C — Status, D — Amount в USD.
Каждая строка — один заказ, D содержит полную сумму заказа.
Напиши формулу для F2, которая суммирует Amount, только когда Channel
равен Online и Status равен Paid. Включи оба условия. Используй обычные
функции Excel и английские имена функций. Объясни каждый диапазон и
перечисли ID заказов, которые должны войти в сумму. Не меняй исходные данные.
[Вставьте таблицу-образец.]
Эталонная формула для задачи:
=SUMIFS(D2:D7,B2:B7,"Online",C2:C7,"Paid")
Это написанный автором ответ, а не утверждение, что каждый помощник выдаст то же самое. Документация Microsoft по SUMIFS определяет сложение значений по нескольким критериям.
Читайте по порядку: сложить D2:D7, но только в строках, где B2:B7 совпадает с Online, а C2:C7 — с Paid. Все три диапазона охватывают одни шесть строк. Формула только с условием канала включила бы неоплаченные заказы.
В F1 поместите ясную подпись, например Paid online orders (USD), а в F2 — формулу. Подпись должна сохранять правило, чтобы читатель понимал значение 210.
Проверьте результат и выбранные строки
Если F2 показывает 210, всё равно сравните включённые ID. В другом наборе правильная сумма может получиться случайно. Здесь вклад дают строки 2, 5 и 7.
Ошибочный результат — полезная улика. Например, 310 совпадает со всеми онлайн-суммами образца: 120 + 60 + 90 + 40 + 0. Это повод проверить, не пропущено ли условие статуса; подсказка не доказывает причину в любой книге.
Если Excel отклоняет формулу, проверьте ожидаемые вашей установкой имена функций и разделители аргументов. В примере английские имена и запятые; некоторым установкам нужны ;. Не заменяйте обычные двойные кавычки вокруг Online и Paid типографскими. Эти поправки синтаксиса не меняют состав заказов.
Руководство Microsoft по ошибкам формул предлагает проверки ошибок и неожиданных результатов. Немедленное добавление IFERROR(...,0) скроет симптом до выяснения причины.
Проверьте предсказуемые изменения
Выполняйте по одному, каждый раз восстанавливая исходные данные:
| Изменение | Ожидаемое F2 | Что проверяет |
|---|---|---|
Изменить C4 с Pending на Paid | 270 | O103 теперь подходит и добавляет 60 |
| Изменить D3 с 80 на 800 | 210 | Заказ магазина остаётся исключённым |
| Изменить D7 с 0 на 5 | 215 | Последняя строка диапазона включена |
| Восстановить исходную таблицу | 210 | Тестовые изменения удалены |
Эти случаи проверяют больше, чем способность исходной формулы показать число: вход заказа в выбранную группу, большое изменение вне неё и последнюю строку диапазона.
Формула и изменения были проверены для статьи в независимом вычислительном движке таблиц. Это не тест функции редактирования книги в ИИ-приложении. Перед адаптацией проверьте всё в собственной установке Excel.
Расширяйте данные только после работы малого примера
Эталон намеренно заканчивается строкой 7. Новый заказ в строке 8 существующая формула не увидит. Расширьте все три диапазона вместе или используйте таблицу Excel со ссылками, следующими за строками. Проверьте новый подходящий заказ, а не предполагайте расширение.
В своих данных замените метки образца фактическими значениями ячеек. До использования результата в отчёте решите, как обрабатывать неполные суммы и непоследовательные статусы. Денежное форматирование само по себе не доказывает, что каждое исходное значение числовое.
Запрашивая исправление у ИИ, опишите проваленную проверку: «Изменение C4 на Paid должно добавить 60, но F2 не изменилось. Проверь диапазоны и оба критерия». Сохраняйте исходную таблицу. Принимайте формулу, когда совпадают правило, выбранные строки и предсказанные изменения, а не просто правдоподобно звучит объяснение.
Источники
- Microsoft: функция SUMIFS: порядок аргументов, несколько критериев и требования к диапазонам.
- Microsoft: обнаружение ошибок в формулах: проверки ошибок и непредвиденных результатов.
