Условие

Дана статистика по показателям ЦКС Краснодар за апрель 2017 на «зелёных» листах. Нужно собрать таблицу итогов за любой выбранный день: при изменении даты в ячейке A1 все значения в жёлтых ячейках автоматически подтягиваются.

Показатели (по линиям обслуживания ОИО, ОЭО, КК и линии продаж):

AWT — average wait time, цель 60 / 90 / 30 сек.
LCR — lost call rate, цель 10%.
СВО — среднее время ответа, по МР цели от 265 до 480 сек.
NPS — целевая 30%.
FCR24 — first contact resolution за 24 часа, цели 77–84% по МР.

Невыполнение KPI — выделять цветом. Для каждой линии — диаграммы по AWT и LCR с явной отметкой цели.

Решение

Подход

Это задача на именованные диапазоны + INDEX/MATCH (или VLOOKUP), либо INDIRECT для динамической ссылки на лист по выбранной дате.

Структура данных

На каждом «зелёном» листе:

Колонки = даты (27/3 ... 7/5).
Строки = показатели в разрезе МР × линия × KPI.

Нужно по дате A1 вытащить колонку и подставить значения в свод.

Реализация формулы

Если все МР собраны на одном листе с колонками-датами, простое решение:

=INDEX(РФ!$A:$AZ,
       MATCH(<наименование_показателя>, РФ!$C:$C, 0),
       MATCH($A$1, РФ!$1:$1, 0))

Или через XLOOKUP (Excel 365):

=XLOOKUP($A$1, РФ!$1:$1,
         XLOOKUP(<показатель>, РФ!$C:$C, РФ!$A:$AZ))

Если каждый МР на отдельном листе, и листы названы одинаково, выбираем лист по имени МР через INDIRECT:

=INDEX(INDIRECT("'"&B$2&"'!$A:$AZ"),
       MATCH($A4, INDIRECT("'"&B$2&"'!$C:$C"), 0),
       MATCH($A$1, INDIRECT("'"&B$2&"'!$1:$1"), 0))

где B$2 — имя МР (заголовок столбца), $A4 — название показателя.

Условное форматирование (невыполненные)

Для AWT (цель 60 сек): плохо — больше цели.

Conditional Formatting → Use formula → =B4 > $B$<row_target> → красная заливка.

Для LCR (цель 10%): аналогично, плохо больше цели.

Для NPS, FCR (цель — больше = лучше): плохо — меньше цели, формула <.

Диаграммы AWT и LCR

Для каждой линии (ОИО, ОЭО, КК):

Тип: гистограмма по МР.
Один ряд — показатель за выбранный день, второй — горизонтальная константа цели.
Ряды, превышающие цель, подсвечивать красным через Conditional Series Color (нет «из коробки», обычно делают через два ряда: «целевой» и «превышающий», разделённые IF).

// формула вспомогательной колонки для красных столбцов
=IF(<значение> > <цель>, <значение>, NA())

NA() скрывает не-красные точки в красном ряду; столбец остаётся в зелёном.

Реализация (Python для проверки/автоматизации)

Если задача допускает Python, проще автоматизировать в pandas + openpyxl:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
 
target_date = "2017-04-15"
wb = load_workbook("kpi.xlsx")
out = []
mr_list = ["МР Северо-Запад", "МР Центр", ...]
metrics = {"AWT": 60, "LCR": 0.10, "NPS": 0.30, "FCR24": 0.77}
 
for mr in mr_list:
    sheet = wb[mr]
    df = pd.DataFrame(sheet.values)  # упрощённо
    # отыскать строку показателя и колонку даты
    ...
    out.append({"mr": mr, "AWT": ..., "LCR": ..., ...})
 
df_out = pd.DataFrame(out)
 
red_fill = PatternFill("solid", fgColor="FFC7CE")
# раскраска по правилам
...

Подводные камни

INDIRECT пересчитывается всегда. На больших книгах тормозит. Для прода лучше Power Query или явные ссылки.
Имена листов с пробелами. 'МР Северо-Запад'!A1 — апострофы обязательны.
Формат даты. Если на листах даты как текст, MATCH не найдёт. Преобразовать в одинаковый формат.
Цели по МР различаются (СВО: 265 до 480 сек). Цели надо хранить в отдельной таблице, а не зашивать в формулу.
#N/A в показателе. При выходных или отсутствии данных — выводите «—», а не #N/A. Через IFERROR.
Условное форматирование на «зелёный/красный» — не цветовые коды в названии цветов, а абсолютные правила. Сразу проверьте, что слепота на красно-зелёный решена через иконки/текст.
Диаграммы с целевой линией. Cтандартный приём — добавить ряд target как линию (не столбец) на той же диаграмме.

Эталонный ответ

Стек: имя выбранной даты в A1 → INDEX/MATCH (или INDIRECT для разделённых листов) подтягивает значения → условное форматирование маркирует невыполненные KPI → диаграмма с двумя рядами (значение + target-линия) и доп.рядом «превышение» для красной подсветки. Для прода — переписать на Power Query или Python+openpyxl.

МТС: динамическая таблица KPI контактного центра по дате