15 промтов для Excel и Google Sheets: формулы, макросы и дашборды, которые сэкономят часы работы

Напиши формулу Excel для суммирования значений из столбца C (сумма), если в столбце A стоит "Активен" и дата в столбце B попадает в диапазон с 1 апреля 2026 по 30 июня 2026. Используй SUMIFS. Покажи пример с данными.

=SUMIFS(C2:C100, A2:A100, "Активен", B2:B100, ">="&DATE(2026,4,1), B2:B100, "<="&DATE(2026,6,30))

=SUMIFS(C2:C100, A2:A100, "Активен", B2:B100, ">="&DATE(2026,4,1), B2:B100, "<="&DATE(2026,6,30))

У меня есть таблица с колонками: Email (A), Дата регистрации (B). Нужно удалить дубликаты по Email, оставив только строку с максимальной датой. Напиши формулу для Sheets или Excel, которая пометит строки для удаления.

=COUNTIFS(A$2:A2, A2, B$2:B2, ">"&B2)=0

Как в Excel сгруппировать даты в сводной таблице по неделям? Напиши пошаговую инструкцию и формулу для создания вспомогательного столбца "Неделя" через WEEKNUM.

Напиши макрос VBA для Excel, который объединяет все листы из всех открытых книг в один лист "Master" текущей книги. Копировать только данные, без форматирования. Добавь столбец с именем исходного файла.

Sub MergeAllWorkbooks()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim destWs As Worksheet
    Dim lastRow As Long
    Dim copyRange As Range

    Set destWs = ThisWorkbook.Sheets("Master")
    destWs.Cells.Clear

    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            For Each ws In wb.Sheets
                lastRow = destWs.Cells(destWs.Rows.Count, 1).End(xlUp).Row + 1
                Set copyRange = ws.UsedRange
                copyRange.Copy destWs.Cells(lastRow, 1)
                ' Добавляем имя файла в последний столбец
                destWs.Cells(lastRow, copyRange.Columns.Count + 1).Resize(copyRange.Rows.Count).Value = wb.Name
            Next ws
        End If
    Next wb
End Sub

Напиши скрипт Google Apps Script, который каждое утро в 9:00 берёт данные из листа "Sales" (колонки: Дата, Сумма, Менеджер), фильтрует по вчерашней дате, создаёт тело письма в HTML и отправляет на email из ячейки A1 листа "Settings".

function sendDailyReport() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales');
  var data = sheet.getDataRange().getValues();
  var yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  var yesterdayStr = Utilities.formatDate(yesterday, Session.getScriptTimeZone(), 'yyyy-MM-dd');

  var filtered = data.filter(function(row) {
    return Utilities.formatDate(row[0], Session.getScriptTimeZone(), 'yyyy-MM-dd') === yesterdayStr;
  });

  var html = '<h3>Отчёт за ' + yesterdayStr + '</h3><table border="1"><tr><th>Менеджер</th><th>Сумма</th></tr>';
  filtered.forEach(function(row) {
    html += '<tr><td>' + row[2] + '</td><td>' + row[1] + '</td></tr>';
  });
  html += '</table>';

  var settings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Settings');
  var email = settings.getRange('A1').getValue();
  MailApp.sendEmail(email, 'Ежедневный отчёт по продажам', '', {htmlBody: html});
}

Напиши формулу для условного форматирования в Excel: выделить ячейки в диапазоне B2:B100, если значение меньше (СРЗНАЧ - 2*СТАНДОТКЛОН) или больше (СРЗНАЧ + 2*СТАНДОТКЛОН).

=ИЛИ(B2<(СРЗНАЧ(B$2:B$100)-2*СТАНДОТКЛОН.В(B$2:B$100)); B2>(СРЗНАЧ(B$2:B$100)+2*СТАНДОТКЛОН.В(B$2:B$100)))

Предложи структуру дашборда в Excel для отдела продаж. Нужно отобразить: общая выручка за месяц, конверсия из лида в сделку, топ-5 менеджеров, динамика по неделям, воронка продаж. Какие листы-источники создать? Какие сводные и чарты использовать? Напиши примерные формулы для расчёта конверсии.

Напиши макрос, который при открытии файла Excel обновляет все сводные таблицы и сохраняет копию файла в папку "Архив" с текущей датой в имени.

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim savePath As String

    ' Обновление сводных
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

    ' Сохранение копии
    savePath = ThisWorkbook.Path & "\Архив\" & Format(Date, "yyyy-mm-dd") & "_Dashboard.xlsx"
    ThisWorkbook.SaveCopyAs savePath
End Sub

У меня есть даты в столбце A и продажи в столбце B за последние 2 года. Напиши формулу Excel (FORECAST.ETS) для прогноза на июль-сентябрь 2026. Учти сезонность.

=FORECAST.ETS(DATE(2026,7,1), B2:B25, A2:A25, 12, 1)

Напиши скрипт Google Apps Script, который получает курс доллара с API ЦБ РФ (cbr.ru) и записывает его в ячейку A1 листа "Курсы". Обновлять раз в час.

function getUSDrate() {
  var url = 'https://www.cbr-xml-daily.ru/daily_json.js';
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());
  var rate = json.Valute.USD.Value;
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Курсы').getRange('A1').setValue(rate);
}