12 промтов, которые превращают аналитика в гуру Excel: автоматизация отчётов, Power Query и живые дашборды

Excel и Google Sheets на стероидах: 12 промтов для сводных таблиц, формул и дашбордов без единой ошибки

Вы когда-нибудь застревали на часы, пытаясь вспомнить синтаксис INDEX+MATCH или правильно настроить сводную таблицу? А может, тратили дни на создание дашборда, который в итоге оказывался неинформативным? Знакомая боль для многих аналитиков и офисных сотрудников. Но что, если я скажу, что с помощью правильно составленных промтов для ChatGPT или других LLM можно решать эти задачи в разы быстрее и без ошибок?

В этой статье я собрал 12 проверенных промтов, которые превратят ваш Excel и Google Sheets в мощные инструменты. Вы научитесь генерировать сложные формулы, автоматизировать сводные таблицы, создавать динамические дашборды и даже писать скрипты на Apps Script или VBA. Каждый промт сопровождается примером использования и пояснением, для каких задач он подходит. Все промты протестированы на реальных кейсах из практики аналитиков и финансистов.

1. Генерация сложных формул с пояснением

Задача: Написать формулу, которая объединяет данные из нескольких листов, обрабатывает ошибки и возвращает результат.

Промт:

Ты — эксперт по Excel и Google Sheets. Напиши формулу для следующей задачи: [опишите задачу]. Учти возможные ошибки (например, #N/A, #VALUE!) и предложи обработку через IFERROR. Объясни каждый шаг формулы. Используй только стандартные функции, доступные в обеих программах.

Пример:
Пользователь хочет просуммировать продажи из листа "Январь" и "Февраль" для товаров из списка в столбце A, но только если менеджер — "Иванов".

Ответ AI:

=IFERROR(SUMIFS(Январь!C:C, Январь!B:B, A2, Январь!D:D, "Иванов") + SUMIFS(Февраль!C:C, Февраль!B:B, A2, Февраль!D:D, "Иванов"), 0)

Пояснение: Функция SUMIFS суммирует значения из столбца C на каждом листе, проверяя соответствие товара (столбец B) и менеджера (столбец D). IFERROR возвращает 0, если данных нет.

2. Создание сводной таблицы с нуля

Задача: Быстро построить сводную таблицу по большому массиву данных.

Промт:

У меня есть таблица с колонками: [перечислите колонки]. Мне нужна сводная таблица, которая покажет [что должно быть в строках], [что в столбцах], [какие значения суммировать]. Опиши пошагово, как её создать в Excel и в Google Sheets. Учти, что данные могут обновляться.

Пример:
Данные: Дата, Регион, Продукт, Продажи, Количество.
Нужно: по строкам — Регион, по столбцам — Продукт, значения — сумма Продаж.

Ответ AI:
В Excel: выделите данные → Вставка → Сводная таблица → перетащите поля в области. В Google Sheets: выделите данные → Вставка → Сводная таблица → настройте строки, столбцы, значения. Для автоматического обновления используйте именованный диапазон или функцию QUERY.

3. Автоматизация отчётов через Power Query

Задача: Настроить автоматическую загрузку и преобразование данных из CSV.

Промт:

Напиши пошаговую инструкцию по созданию запроса в Power Query для загрузки данных из папки с CSV-файлами. Нужно объединить все файлы, удалить пустые строки, преобразовать типы данных и загрузить в лист Excel. Учти, что файлы могут добавляться.

Ответ AI:
1. Данные → Получить данные → Из файла → Из папки.
2. Укажите путь к папке.
3. В редакторе Power Query выберите "Объединить файлы".
4. Удалите пустые строки (Главная → Удалить строки → Удалить пустые).
5. Измените типы данных.
6. Закройте и загрузите. При добавлении новых файлов достаточно обновить запрос.

4. Создание динамического дашборда

Задача: Сделать интерактивный дашборд с графиками, которые обновляются при изменении данных.

Промт:

Предложи структуру дашборда в Excel на основе данных о продажах. Включи: ключевые метрики (KPI), графики динамики, срез по регионам. Опиши, какие формулы и элементы управления использовать (например, срезы, раскрывающиеся списки). Данные должны обновляться автоматически.

Ответ AI:
Создайте таблицу с KPI: общая выручка, средний чек, количество заказов. Используйте сводные таблицы и срезы для фильтрации. Для динамики — график на основе сводной. Добавьте раскрывающийся список для выбора периода. Все формулы должны ссылаться на именованные диапазоны или таблицы.

5. Написание макроса VBA для автоматизации рутины

Задача: Автоматизировать ежемесячное форматирование отчёта.

Промт:

Напиши макрос VBA, который: 1) находит последнюю строку в столбце A, 2) применяет жирный шрифт к заголовкам, 3) выделяет цветом ячейки с отрицательными значениями в столбце C, 4) сохраняет файл с датой в имени.

Ответ AI:

Sub FormatReport()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Rows(1).Font.Bold = True
    Dim cell As Range
    For Each cell In Range("C2:C" & lastRow)
        If cell.Value < 0 Then cell.Interior.Color = RGB(255, 200, 200)
    Next cell
    ActiveWorkbook.SaveAs "Report_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
End Sub

6. Перевод сложных формул с одного языка на другой

Задача: Перенести формулу из Excel в Google Sheets или наоборот.

Промт:

Переведи следующую формулу Excel в Google Sheets: [вставьте формулу]. Учти различия в функциях (например, ARRAYFORMULA, QUERY). Если есть несовместимости, предложи альтернативу.

Пример:
Excel: =SUMIFS(C:C, A:A, "<>", B:B, "Иванов")
Google Sheets: =SUMIFS(C:C, A:A, "<>", B:B, "Иванов") — синтаксис аналогичен, но в Google Sheets можно использовать ARRAYFORMULA для массивов.

7. Поиск и исправление ошибок в формулах

Задача: Разобраться, почему формула выдаёт #N/A или #REF!.

Промт:

Моя формула выдаёт ошибку #N/A. Вот она: [формула]. Данные находятся в диапазоне [описание]. Объясни возможные причины и предложи исправленную формулу.

Ответ AI:
Причина: функция VLOOKUP не находит значение. Проверьте, что искомое значение есть в первом столбце диапазона. Исправьте: =IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "Не найдено").

8. Создание кастомной функции в Google Apps Script

Задача: Написать функцию, которой нет в стандартном наборе.

Промт:

Напиши функцию на Google Apps Script, которая принимает диапазон и возвращает количество уникальных значений. Приведи пример использования в таблице.

Ответ AI:

function COUNTUNIQUE(range) {
  var values = range.flat();
  var unique = new Set(values);
  return unique.size;
}

Использование: =COUNTUNIQUE(A2:A100)

9. Генерация тестовых данных для тренировки

Задача: Создать реалистичный набор данных для проверки формул.

Промт:

Сгенерируй 100 строк тестовых данных для таблицы продаж: дата, регион, продукт, количество, цена, менеджер. Данные должны быть правдоподобными. Выведи в формате CSV.

Ответ AI:

Дата,Регион,Продукт,Количество,Цена,Менеджер
2025-01-05,Москва,Ноутбук,3,55000,Иванов
2025-01-06,СПб,Смартфон,5,30000,Петров
...

10. Оптимизация медленных формул

Задача: Ускорить работу листа с тысячами формул.

Промт:

Мой лист Excel тормозит из-за большого количества формул ВПР. Предложи способы оптимизации: замена на INDEX+MATCH, использование Power Query, преобразование в таблицу. Опиши пошагово.

Ответ AI:
1. Замените VLOOKUP на INDEX+MATCH — работает быстрее.
2. Перенесите расчёты в Power Query.
3. Используйте таблицы Excel для автоматического расширения формул.
4. Отключите автоматический пересчёт (Формулы → Параметры вычислений → Вручную).

11. Создание интерактивного чек-листа с условным форматированием

Задача: Визуально отслеживать выполнение задач.

Промт:

Как в Google Sheets сделать чек-лист, где при отметке галочкой строка зачёркивается и меняет цвет? Опиши шаги.

Ответ AI:
1. Вставьте флажки (Вставка → Флажок).
2. Выделите строку → Формат → Условное форматирование.
3. Формула: =$B2=TRUE (если флажок в B).
4. Настройте зачёркивание и серый цвет.

12. Автоматическая отправка отчётов по почте

Задача: Настроить ежедневную рассылку отчёта из Google Sheets.

Промт:

Напиши скрипт Google Apps Script, который отправляет лист "Отчёт" по электронной почте каждый день в 9:00. Тема письма: "Ежедневный отчёт". В теле — ссылка на файл.

Ответ AI:

function sendReport() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Отчёт");
  var url = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  MailApp.sendEmail("manager@example.com", "Ежедневный отчёт", "Ссылка: " + url);
}
// Триггер: ежедневно с 9:00

Заключение

Эти 12 промтов покрывают ключевые задачи аналитика: от генерации формул до автоматизации отчётов. Главное — адаптировать их под свои данные и не бояться экспериментировать. Помните, что AI — это инструмент, который усиливает ваши навыки, но не заменяет понимание логики работы таблиц. Используйте промты как отправную точку, а затем настраивайте под конкретные нужды.

Если вы хотите глубже освоить Excel и Google Sheets, попробуйте наши текстовые курсы на asibiont.com — они построены на реальных кейсах и помогут систематизировать знания. А если у вас есть свои любимые промты, делитесь в комментариях!