Един интересен казус – как да се направи автоматично дублиране на стойности в Ексел. Както обикновено – има повече от едно решения, които ще представя набързо по-долу.
Казусът:
В първата колона на таблицата по-долу имаме списък с имена, които се повтарят по един път. Във втората колона срещу всяко име е показано колко пъти трябва да се повтори то. Целта е да се направи списък, в който всяко име е повторено съответния брой пъти, както е във втората таблица.
За тази малка табличка лесно ще го направим ръчно. Но ако са няколкостотин имена и всяко трябва да се повтори 20-30-100 пъти? Тогава на помощ идва богатият набор от възможности, които предлага Ексел (и то без да прибягваме до VBA 😉)
Решение1: Power Query
„Това е много лесно с Power Query“ е първото, което ми хрумна, когато видях, че трябва да се прави дублиране на стойности в Ексел. Първата стъпка е да заредим данните. На картинката е показано как става в Ексел 2019 и нагоре. Ако сте на по-ранни версии е възможно да има разлики, но в общи линии търсите From Other sources > From Table/Range.
Вече сме в Power Query и отиваме на Add Column > Custom Column
В това меню записваме следната проста формула:
С нея ще създадем списък от числа. Първото е 1, а последното – това, което стои в колоната „Повторения“. Когато дадем ОК, виждаме това:
Добавена е една колона, в която на всеки ред пише List. Обърнете внимание, че цветът на шрифта не е черен, а зелен. Това винаги означава, че в клетката няма стойност, а по-сложна структура. В Power Query във всяка клетка можем освен текст, числа да имаме и списъци (List), записи (Record) и дори цели таблици (Table). В случая, ако кликнете вдясно от думата List, ще видите долу под таблицата съдържанието на списъка. На първия ред срещу името „Пешо“ списъкът е от три стойности – 1, 2 и 3. Точно както и се очаква. Повече за списъците в Power Query, как се създават и използват може да прочетете тук.
Като натиснем бутона, ограден в синьо получаваме тази таблица:
Обърнете внимание, че в колоната „Име“ сме получили точно каквото ни трябва – всяко име повторено толкова пъти, колкото е стойността в колона „Повторения“.
Кликаме с десния бутон на мишката върху заглавието на колоната „Име“ и избираме Remove Other Columns.
А след това зареждаме данните в Ексел по този начин:
Ето го и крайният резултат:
Решение2: Динамични формули
Динамичните формули са една от новостите в Ексел през последните години. С тяхна помощ с успех се постигат трансформации на данните, за които преди използвахме доста сложни формули. Освен това областта с резултатите се увеличава и намалява в зависимост от изходните данни без да правим копи/пейст (нарича се SPILL).
За да изградим формулата, която ще свърши работата, започваме с класическата функция REPT, която повтаря един текст определен брой пъти. Обърнете внимание на подчертаната част от формулата:
След като е въведена формулата, удряме един Enter и бум – формулата се „копира“ сама до края на областта. Няма нужда да правим копи/пейст надолу.
Както виждате – имената са повторени на всеки ред колкото пъти трябва, но това е далеч от желания краен резултат. Те все още не са едно под друго в колона. За да го постигнем първо ще ги съберем в един голям текст, после ще ги разделим и накрая ще ги подредим едно под друго. За събирането използваме функцията TEXTJOIN.
Резултатът от нея е това:
Сега използваме TEXTSPLIT, за да разделим текста и всяко име да застане в една клетка на реда. Ето затова ни трябваше разделителят вертикална черта (|). Ако не ви харесва – може да използвате всякакъв друг символ.
Резултатът – вече сме постигнали дублиране на стойности.
Но те са на един ред. За да ги обърнем в колона, използваме TOCOL
И резултатът е каквото желаем:
Ето я формулата, ако приемем, че имената започват в клетка В3:
=TOCOL(TEXTSPLIT(TEXTJOIN(„|“;;REPT(B3:B8&“|“;C3:C8));“|“;;TRUE))
Решение3: Обикновени формули
Дублиране на стойности можем да постигнем и с по-старите формули в Ексел. Ето едно предложение за това:
Най-напред в някоя клетка записваме съвсем проста формула:
След това в клетката под нея въвеждаме една доста по-сложна:
Тази формула брои колко пъти се среща името в горната клетка до момента и проверява дали този брой е по-малък от съответното число в колона „Повторения“. Ако не е – връща същото име, ако обаче е по-голям или равен – връща следващото име.
Копираме няколко реда надолу и – желаният резултат е налице
Тук го има неудобството, че когато копираме повече клетки от необходимото – получавеме #N/A
Текст на формулата:
=IF(
COUNTIF($E$3:E3;E3)<INDEX($C$3:$C$7;MATCH(E3;$B$3:$B$7;0));
E3;
INDEX($B$3:$B$7;MATCH(E3;$B$3:$B$7;0)+1)
)
Равносметка
Това е един малко необичаен казус, дотолкова доколкото обикновено премахваме повтарящи се стойности. А тук точно обратно – правим дублиране на стойности. Но както виждате – Excel може всичко и имаме поне три начина да го направим. И трите решения работят, но е добре да се познават техните предимства и недостатъци, за да се подбере правилното в конкретната ситуация
Решението с Power Query има следните предимства:
- Формулата е най-лесната от всички
- При промяна на изходните данни, обновяването на крайния списък е много лесно – само с един Refresh
- То е единственият вариант, когато данните не са в Excel, а примерно в база данни, уебсайт, pdf файл. Или когато общият брой на редовете е над 1 млн. реда
Недостатъци на Power Query:
- Наличен е във версия 2010 и нагоре. Но нека си говорим честно – в 2022 година да работите с по-стара версия от 2010 не е добре
- Все още малко хора познават и работят с Power Query. Което е ужасно лошо. Този инструмент е фантастичен за автоматизиране на практически всички задачи, свързани с обработка на данни
- Крайният резултат не се обновява автоматично при промяна в изходните данни
Решение с динамични формули – предимства:
- Най-голямото предимство е, че крайният резултат като брой редове се променя автоматично при промяна на изходните данни. Трябва само новите данни да са в обхвата на формулата. Затова, ако забелязвате на снимките има един ред оцветен с жълто. Той е включен във формулата, но не се попълва, а ако трябва да се добавят данни – вмъква се ред над него и така формулата автоматично го хваща
- Освен това, поне за мен, структурата на формулата е много логична. Повтаряме имената с REPT, колкото пъти е необходимо, след това ги обединяваме с TEXTJOIN, разделяме получения текст в отделни клетки с TEXTSPLIT и накрая с TOCOL го превръщаме в списък с имената.
Недостатъци на динамичните формули:
- налични са само в Ексел 365. Дори и да имате такъв, формулите няма да работят, ако изпратите файла на човек с по-стара версия
- крайният резултат може да бъде само до 1млн. реда
Решение с „обикновени“ формули – предимства
- Очевидно те ще работят на всяка версия на Excel
- За голяма част от потребителите това ще е решение в тяхната зона на комфорт, т.е. няма да изисква придобивне на много ново знание, ще са сигурни с използването на тези формули
Недостатъци на „обикновените формули“:
- Използват се всъщност 2 формули, т.к. за първата клетка формулата е различна
- Изискват ръчно копиране и пействане при промяна на изходните данни
- COUNTIF може значително да забави работата на компютъра при големи обеми, напр. повече от 50-100-150 хиляди реда
- Аз лично от толкова много INDEX, MATCH, смесено и абсолютно адресиране се обърквам. Хем съм измислил формулата
- Тук също има ограничение на крайния резултат от 1 млн. реда