Пивотните таблици са добри за всички – изчисляват бързо и се конфигурират гъвкаво, а дизайнът може да се навие елегантно в тях, ако е необходимо. Но има и някои недостатъци, по-специално невъзможността за създаване на резюме, където областта на стойността не трябва да съдържа числа, а текст.
Нека се опитаме да заобиколим това ограничение и да измислим „няколко патерици“ в подобна ситуация.
Да предположим, че нашата компания транспортира своите продукти в контейнери до няколко града в нашата страна и Казахстан. Контейнерите се изпращат не повече от веднъж месечно. Всеки контейнер има буквено-цифров номер. Като първоначални данни има стандартна таблица с изброяване на доставките, от която трябва да направите някакъв вид обобщение, за да видите ясно броя на контейнерите, изпратени до всеки град и всеки месец:
За удобство нека направим таблицата с първоначалните данни „интелигентна“ предварително с помощта на командата Начало – Форматиране като таблица (Начало — Форматиране като таблица) и й дай име Доставките етикет конструктор (Дизайн). В бъдеще това ще опрости живота, защото. ще бъде възможно да се използва името на таблицата и нейните колони директно във формулите.
Метод 1. Най-лесният – използвайте Power Query
Power Query е супер мощен инструмент за зареждане и трансформиране на данни в Excel. Тази добавка е вградена в Excel по подразбиране от 2016 г. Ако имате Excel 2010 или 2013, можете да я изтеглите и инсталирате отделно (напълно безплатно).
Целият процес, за по-голяма яснота, анализирах стъпка по стъпка в следния видеоклип:
Ако не е възможно да използвате Power Query, тогава можете да отидете по други начини – чрез обобщена таблица или формули.
Метод 2. Помощно резюме
Нека добавим още една колона към нашата оригинална таблица, където с помощта на проста формула изчисляваме броя на всеки ред в таблицата:
Очевидно -1 е необходим, защото имаме едноредов хедър в нашата таблица. Ако таблицата ви не е в началото на листа, тогава можете да използвате малко по-сложна, но универсална формула, която изчислява разликата в числата на текущия ред и заглавката на таблицата:
Сега по стандартен начин ще изградим обобщена таблица от желания тип въз основа на нашите данни, но в полето за стойност ще изпуснем полето Номер на ред вместо това, което искаме контейнер:
Тъй като нямаме няколко контейнера в един и същи град в един и същи месец, нашето резюме всъщност ще даде не количеството, а номерата на редовете на контейнерите, от които се нуждаем.
Освен това можете да изключите общите и междинните суми в раздела Конструктор – Общи суми и Междинните суми (Дизайн — общи суми, междинни суми) и на същото място превключете резюмето към по-удобно таблично оформление с бутона Докладвайте макет (Оформление на отчета).
Така вече сме на половината път до резултата: имаме таблица, където на пресечната точка на града и месеца има номер на ред в изходната таблица, където се намира кодът на контейнера, от който се нуждаем.
Сега нека копираме обобщението (на същия лист или друг) и да го поставим като стойности и след това да въведем нашата формула в областта на стойността, която ще извлече кода на контейнера по номера на реда, намерен в резюмето:
функция IF (АКО), в този случай проверява дали следващата клетка в резюмето не е празна. Ако е празна, изведете празен текстов низ “”, т.е. оставете клетката празна. Ако не е празно, извлечете от колоната Контейнер изходна таблица Доставките съдържание на клетка по номер на ред с помощта на функция INDEX (ИНДЕКС).
Може би единственият не много очевиден момент тук е двойната дума Контейнер във формулата. Такава странна форма на писане:
Консумативи[[Контейнер]:[Контейнер]]
… е необходимо само за препратка към колоната Контейнер беше абсолютен (като препратка със знаци $ за обикновени „не-интелигентни“ таблици) и не се приплъзна към съседни колони при копиране на нашата формула вдясно.
В бъдеще, когато променяте данните в изходната таблица Доставките, трябва да запомним да актуализираме нашето допълнително резюме с номера на редове, като щракнете с десния бутон върху него и изберете командата Актуализиране и запазване (Опресняване).
Метод 3. Формули
Този метод не изисква създаване на междинна обобщена таблица и ръчно актуализиране, а използва „тежкото оръжие“ на Excel – функцията SUMMESLIMN (SUMIFS). Вместо да търсите номерата на редовете в резюме, можете да ги изчислите с помощта на тази формула:
С известна външна обемност всъщност това е стандартен случай на използване на функцията за селективно сумиране SUMMESLIMNA, който сумира номерата на редовете за дадения град и месец. Отново, тъй като нямаме няколко контейнера в един и същи град в един и същи месец, нашата функция всъщност ще издаде не сумата, а самия номер на линията. И след това функцията, която вече е позната от предишния метод INDEX Можете също да извлечете кодове на контейнери:
Разбира се, в този случай вече не е необходимо да мислите за актуализиране на резюмето, но при големи таблици функцията СУМЕСЛИ може да бъде забележимо бавен. След това ще трябва да изключите автоматичното актуализиране на формулите или да използвате първия метод - обобщена таблица.
Ако външният вид на обобщението не е много подходящ за вашия отчет, тогава можете да извлечете номерата на редовете от него във финалната таблица не директно, както направихме ние, а с помощта на функцията GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Как да направите това можете да намерите тук.
- Как да създадете отчет с помощта на обобщена таблица
- Как да настроите изчисления в осеви таблици
- Селективно броене със SUMIFS, COUNTIFS и др.