зміст
Зведені таблиці зручні для всіх – вони швидко обчислюють і гнучко налаштовуються, а дизайн можна елегантно вставити в них, якщо потрібно. Але є також кілька ложок дьогтю, зокрема, неможливість створити резюме, де область значень повинна містити не цифри, а текст.
Давайте спробуємо обійти це обмеження і придумати «пару милиць» в подібній ситуації.
Припустимо, наша компанія транспортує свою продукцію в контейнерах в кілька міст нашої країни і Казахстану. Контейнери відправляються не частіше одного разу на місяць. Кожен контейнер має буквено-цифровий номер. В якості вихідних даних є стандартна таблиця з перерахуванням поставок, з якої потрібно зробити якусь зведення, щоб наочно бачити кількість контейнерів, відправлених в кожне місто і кожен місяць:
Для зручності заздалегідь зробимо таблицю з вихідними даними «розумною» за допомогою команди Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю) і дати їй ім'я Поставки таб Конструктор (Дизайн). У майбутньому це спростить життя, т.к. можна буде використовувати назву таблиці та її стовпців безпосередньо у формулах.
Спосіб 1. Найпростіший – використовувати Power Query
Power Query — це надпотужний інструмент для завантаження та перетворення даних у Excel. Ця надбудова вбудована в Excel за замовчуванням з 2016 року. Якщо у вас Excel 2010 або 2013, ви можете завантажити та встановити її окремо (цілком безкоштовно).
Весь процес, для наочності, я поетапно розібрав у наступному відео:
Якщо немає можливості використовувати Power Query, то можна піти іншими шляхами – через зведену таблицю або формули.
Спосіб 2. Допоміжний конспект
Давайте додамо ще один стовпець до нашої вихідної таблиці, де за простою формулою обчислимо кількість кожного рядка в таблиці:
Очевидно, що -1 потрібен, тому що у нас є однорядковий заголовок у нашій таблиці. Якщо ваша таблиця знаходиться не на початку аркуша, то ви можете скористатися трохи більш складною, але універсальною формулою, яка обчислює різницю чисел поточного рядка і заголовка таблиці:
Тепер стандартним способом ми побудуємо зведену таблицю потрібного типу на основі наших даних, але в полі значення опустимо поле Номер рядка замість того, що ми хочемо контейнер:
Оскільки ми не маємо кількох контейнерів в одному місті в одному місяці, то в нашому підсумку буде, по суті, не сума, а номери рядків потрібних нам контейнерів.
Крім того, ви можете вимкнути загальні та проміжні підсумки на вкладці Конструктор – Загальні підсумки и Підсумки (Дизайн — загальні підсумки, проміжні підсумки) і там же кнопкою перемкнути зведення в більш зручну таблицю Макет звіту (Макет звіту).
Таким чином, ми вже на півдорозі до результату: у нас є таблиця, де на перетині міста і місяця в вихідній таблиці стоїть номер рядка, де лежить потрібний нам код контейнера.
Тепер давайте скопіюємо резюме (на той самий аркуш або інший) і вставимо його як значення, а потім введемо нашу формулу в область значень, яка витягне код контейнера за номером рядка, знайденим у підсумку:
функція IF (ЯКЩО), у цьому випадку перевіряє, чи не порожня наступна клітинка у підсумку. Якщо пуста, виведіть порожній текстовий рядок «», тобто залиште клітинку порожньою. Якщо не пусто, то витягти зі стовпця Контейнер вихідна таблиця Поставки вміст комірки за номером рядка за допомогою функції ІНДЕКС (ІНДЕКС).
Мабуть, єдиним не дуже очевидним моментом тут є подвійне слово Контейнер у формулі. Така дивна форма написання:
Постачання[[Контейнер]:[Контейнер]]
… потрібен лише для посилання на стовпець Контейнер був абсолютним (як посилання зі знаками $ для звичайних «нерозумних» таблиць) і не скочувався до сусідніх стовпців при копіюванні нашої формули праворуч.
Надалі при зміні даних у вихідній таблиці Поставки, ми повинні не забувати оновити наш допоміжний підсумок номерами рядків, клацнувши на ньому правою кнопкою миші та вибравши команду Update & Save (Оновити).
Спосіб 3. Формули
Цей метод не вимагає створення проміжної зведеної таблиці та ручного оновлення, але використовує «важку зброю» Excel – функцію SUMMESLIMN (SUMIFS). Замість того, щоб шукати номери рядків у підсумку, ви можете обчислити їх за цією формулою:
З певною зовнішньою громіздкістю, фактично, це стандартний випадок використання функції вибіркового підсумовування SUMMESLIMNA, що підсумовує номери рядків для даного міста та місяця. Знову ж таки, оскільки ми не маємо кількох контейнерів в одному місті в одному місяці, наша функція фактично видасть не суму, а сам номер рядка. А далі вже знайома з попереднього способу функція ІНДЕКС Ви також можете отримати коди контейнерів:
Звичайно, в цьому випадку вам більше не потрібно думати про оновлення зведення, але на великих таблицях функція СУММЕСЛІ може бути помітно повільним. Тоді вам доведеться вимкнути автоматичне оновлення формул, або скористатися першим способом – зведеною таблицею.
Якщо зовнішній вигляд зведення не дуже підходить для вашого звіту, то ви можете витягти з нього номери рядків у підсумкову таблицю не напряму, як це зробили ми, а за допомогою функції GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Як це зробити, можна дізнатися тут.
- Як створити звіт за допомогою зведеної таблиці
- Як налаштувати обчислення у зведених таблицях
- Вибірковий підрахунок за допомогою SUMIFS, COUNTIFS тощо.