„Братле, помагай! Спешно трябва да направим файл със 130 шийта по списък, като всеки е с точно определено име от списъка. Във всеки шийт има таблица, която не е нещо сложно, но колоните в различните шийтове са различен брой и с различно наименование. Трябва да стане до утре, а сме претрупани с работа. Вече цял месец работим по 12-14 часа и просто не виждам как мога да накарам някого от екипа да отдели цял ден, за да цъка копи/пейст в този файл.“
„Чакай, чакай. Дай едно по едно. Как изглежда таблицата във всеки шийт?“
„Ами ето така. Както казах, нищо сложно. Най-отгоре е името на града, таблицата има фиксиран брой редове, които имат едни и същи наименования. Но колоните са различни. Във всеки град има различен брой търговци и разбира се те имат различни имена“
„Добре, списък с градовете и търговците имаш ли?“
„Ето го. Само че не е във вид, който е удобен за използване. Имената дори не са в една клетка.“
„Нямаш грижи. 20 минути и си готов.“
„Да бе! Майтапиш се нещо, знам те аз. Но ако го направиш, имаш от мен една тава с кадаиф и боза на корем.“
„Абсолютно съм сериозен. Може и по-малко да отнеме. А ще черпиш като се видим.“
Горе-долу така протече преди време разговор с един приятел. Вече разбрахте каква беше целта, а ето как свършихме работа за цял ден за (около) 20 минути.
Планът
Най-напред ще нормализираме горния списък чрез Power Query и ще добавим колона с целите. След това ще направим пивот таблица върху списъка с търговците и целите и ще използваме една хитра опция на пивот таблиците, за да размножим всички шийтове. Ще селектираме всички шийтове и ще направим исканата таблица в тях. Накрая с помощта на условно форматиране ще решим проблема с различния брой колони.
Заб.: в примера, съм използвал 29-те области на България, а имената на търговците са измислени. Всякакви прилики с действителни лица и събития е напълно случайна.
Нормализиране на списъка
Избираме произволна клетка в списъка с търговците и отиваме на Data > From Table/Range.
С това зареждаме таблицата със списъка в Power Query, а там маркираме колоната „Търговци“, десен бутон и избираме Split Column > By Delimiter.
А в този прозорец избираме тези опции
С това указваме на Power Query да раздели колоната „Търговци“ навсякъде, където има запетая и да подреди търговците от всеки град един под друг. Повече за разделянето на колони в Power Query може да прочетете тук.
Ето я таблицата след разделянето и някои козметични обработки за изчистване на излишните интервали.
Сега нека се върнем в Ексел и по същия начин да заредим списъка с целите:
Ето тук таблицата с целите е заредена в Power Query.
Обърнете внимание в ляво има две заявки (queries). Горната се казва List и това е нашият списък с градовете и търговците. Долната е Targets, т.е. това е таблицата с целите. Със следващото действие, ще обединим двете таблици и ще създадем т.нар. cartesian product. Най-просто казано cartesian product се получава когато комбинираме всеки ред от една таблица с всички редове от другата. За повече подробности – тук.
За да създадем този картезиански продукт, трябва да отидем в заявката List и там на Add Column > Custom Column
В това меню в полето Custom column formula пишем формулата =Targets
Резултатът е, че към списъка с градове и търговци добавихме една колона. В нея всяка клетка съдържа списъка с целите. Остава ни само да „разтворим“ тази колона като натиснем оградения в синьо бутон
Тук даваме ОК. Аз лично предпочитам да няма отметка на ограденото с червено. Но и да има – не е фатално.
Нашият картезиански продукт е готов и в Power Query нямаме друга работа освен да заредим таблицата в Ексел с бутона Close&Load.
Резултатът е това:
Създаване на шийтове по списък с имена на градовете
Най-напред създаваме пивот таблица върху нормализирания списък с градовете (Insert > Pivot Table). А в пивот табицата задаваме „Търговци“ да са в Columns, „Цели“ – в Rows, а „град“ – във Filters. Ключовото тук е колоната, която съдържа имената на шийтовете, т.е. „град“ да е във Filters. Виждате отдясно и как изглежда самата пивот таблица за град Благоевград.
Изглежда почти като това, което ни трябва, нали? Само тези Grand Total-и за редовете и колоните не ни трябват. Затова можем да ги махнем по този начин:
И резултатът е това:
Дотук сме на половината от крайната цел, за което ни бяха необходими около 10 минути. Остава ни само да създадем 29 шийта за всеки град, всеки с името на града и после да ги форматираме по желания начин.
Както сме в пивот таблицата отиваме на Analyze, а там кликаме не върху бутона Options, а въру малката стрелка, която е отдясно на него и избираме Show Report Filter Pages. Това ще създаде отделни шийтове с пивот таблици за всеки един от градовете в списъка.
В това меню Ексел ни пита кои филтри да използва. В случая е само един и затова просто даваме ОК.
След няколко секунди мислене, Ексел създава шийтовете с пивот таблици за всеки един град. Като за име на всеки шийт използва стойностите от колона „град“ в изходната таблица.
Ето как създадохме 29 шийта за по-малко от 2 минути. Всеки един от тях съдържа пивот таблица, която съдържа съответния брой колони с имената на търговците. Например така изглеждат Благоевград, Бургас и Кюстендил.
Освен тези шийтове има и три „служебни“: “template”,в който е шаблонът на таблицата, “list” –съдържа списъка с търговците и “pivot”, в който е основната пивот таблица, която създадохме.
Създаване на таблиците
Селектираме всички шийтове с градове. Най-лесно това става като кликнем върху някой град с десния бутон на мишката и изберем Select All Sheets. След това задържаме клавиша Control (CTRL) и с левия бутон кликаме върху трите „служебни“ шийта – template, pivot и lists. След като селектираме само шийтовете с градове, каквото правим в един от тях, ще се случва във всички.
А целта ни е да превърнем пивот таблицата в обикновена и да я форматираме, както е необходимо. Затова в някой от шийтовете с градове правим:
- CTRL+A, за да изберем всички клетки
- CTRL+C за копиране
- Paste > Special > Values
Получава се това:
И докато все още са маркирани всички шийтове с градове, можем да премахнем излишния ред (този, в който пише Column labels) и да поставим града в клетка А1, както е в шаблона.
Сега вече таблицата за всеки град съдържа необходимите данни, остава само да ги форматираме. Но ако направим просто Copy > Paste Special > Formats от шаблона,това няма да е достатъчно. Например за Благоевград има една излишна колона, а за София – две не са форматирани.
За да поправим това, можем да използваме Conditional formatting
Финално форматиране с Conditional formatting
За заглавния ред на таблицата можем да използваме следното условно форматиране:
Тоест, ако клетката не е празна, да бъде оцветена в жълто, което означава, че ако е празна – няма да бъде форматирана.
По-интересно е как да форматираме самата таблица. Тук е удачно да използваме опцията Use a formula to determine which cells to format и да създадем формула, която проверява дали клетката в заглавния ред е празна. Ако не е – тогава редовете надолу да бъдат форматирани с border.
Прилагаме това условно форматиране за всички клетки в шаблона. Добре е да обхванем поне 7-8 колони. И сега вече, когато копираме формата от шаблона и Благоевград, и София са както трябва.
Е, не всичко е идеално. Например колоните са с различна широчина, като понякога имаме значителна диспропорция, както в Добрич
Просто фамилията на търговеца Хаджикарапопкостадинов е значително по-дълга от тази на колегите му.
Но този дребен дефект не можа да помрачи радостта на моя приятел, че за 20 минути с помощта на Power Query, пивот таблица и условно форматиране успяхме да направим това, което в началото изглеждаше като работа за цял ден. А ако питате какво стана с кадаифа – все още не съм го опитал, но в интерес на истината, вината е в мен.