Всім хороші зведені таблиці - і вважають швидко, і налаштовуються гнучко, і дизайн можна накрутити в них ошатний, якщо потрібно. Але є і кілька ложок дьогтю, зокрема, неможливість створити зведену, де в області значень повинні бути не числа, а текст.
Давайте спробуємо обійти це обмеження і придумати "пару милиць" в подібній ситуації.
Припустимо, наша компанія возить в кілька міст Росії і Казахстану свою продукцію в контейнерах. Контейнери відправляються не частіше, ніж раз на місяць. Кожен контейнер має буквено-цифровий номер. В якості вихідних даних є стандартна таблиця з перерахуванням поставок, з якої потрібно зробити якусь подобу зведеної, щоб наочно бачити номери контейнерів, відправлених в кожне місто і кожному місяці:
Для зручності, давайте заздалегідь зробимо таблицю з вихідними даними "розумної" за допомогою команди Головна - Форматувати як таблицю (Home - Format as Table) і дамо їй ім'я Поставки на вкладці Конструктор (Design). Надалі, це спростить життя, тому що можна буде використовувати ім'я таблиці і її стовпців прямо в формулах.
Спосіб 1. Найпростіший - використовуємо Power Query
Якщо немає можливості використовувати Power Query, то можна піти в інший спосіб - через зведену таблицю або формули.
Спосіб 2. Допоміжна зведена
Додамо до нашої вихідної таблиці ще один стовпець, де за допомогою простої формули обчислимо номер кожного рядка в таблиці:
Очевидно, що -1 потрібен, оскільки у нас в таблиці є однорядкова шапка. Якщо ваша таблиця лежить не на початку листа, то можна використовувати трохи більше складну, але універсальну формулу, яка обчислює різницю в номерах поточного рядка і шапки таблиці:
Тепер стандартним чином побудуємо на основі наших даних зведену таблицю бажаного виду, але в поле значень закинемо поле Номер рядка замість потрібного нам Контейнери.
Оскільки у нас не буває декількох контейнерів в одному і тому ж місті в один і той же місяць, то наша зведена видасть, фактично, не суму, а номери рядків потрібних нам контейнерів.
Додатково можна відключити загальні і проміжні підсумки на вкладці Конструктор - Загальні підсумки і Проміжні підсумки (Design - Grand Totals, Subtotals) і там же переключити зведену в більш зручний табличний макет кнопкою Макет звіту (Report Layout).
Таким чином ми вже на півдорозі до результату: у нас є таблиця, де на перетині міста і місяці варто номер рядка в початковій таблиці, де лежить потрібний нам код контейнера.
Тепер скопіюємо зведену (на цей же лист або на інший) і вставимо як значення, а потім введемо в область значень свою формулу, яка буде отримувати код контейнера по номеру рядка, знайденому в зведеної:
Функція ЯКЩО (IF). в даному випадку, перевіряє, щоб чергова осередок у зведеній була не порожній. Якщо порожня, то виводимо порожню текстову рядок "", тобто залишаємо осередок незаповненою. Якщо не порожня, то витягаємо з шпальти Контейнер вихідної таблиці Поставки вміст комірки за номером рядка за допомогою функції ІНДЕКС (INDEX).
Мабуть, єдиним не надто очевидним моментом тут є задвоєння слово Контейнер у формулі. Така дивна форма запису:
В майбутньому, при зміні даних у вихідній таблиці Поставки. необхідно не забути оновити нашу допоміжну зведену з номерами рядків, клацнувши по ній правою кнопкою миші і вибравши команду Оновити (Refresh).
Спосіб 3. Формули
Цей спосіб не вимагає створення проміжної зведеної таблиці і ручного оновлення, а використовує "важку зброю Excel" - функцію СУММЕСЛІМН (SUMIFS). Замість пошуку номерів рядків у зведеній їх можна обчислити за допомогою ось такої формули:
При деякій зовнішньої громіздкість, насправді, це стандартний варіант використання функції вибіркового підсумовування СУММЕСЛІМН. яка підсумовує номера рядків для заданого міста і місяці. Знову ж таки, оскільки у нас не буває декількох контейнерів в одному і тому ж місті в один і той же місяць, то наша функція видасть, фактично, не суму, а сам номер рядка. А потім вже знайомої по попередньому способу функцією ІНДЕКС можна витягти і коди контейнерів:
Само-собою, в цьому випадку вже не потрібно думати про оновлення зведеної, але на великих таблицях функція СУММЕСЛИ може відчутно гальмувати. Тоді доведеться відключати автоматичне оновлення формул або ж скористатися першим способом - зведеною таблицею.
Якщо зовнішній вигляд зведеної вам не дуже підходить для звіту, то можна витягувати з неї номери рядків в фінальну таблицю не безпосередньо, як ми робили, а за допомогою функції ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ (GET.PIVOT.DATA). Як це зробити можна подивитися тут.