Наскоро един мой приятел се обърна към мен с молба да помогна с генерирането на всички възможни фрази, състоящи се от набор от дадени думи. Проблеми от този вид могат да възникнат при съставянето на списъци с ключови думи и фрази за онлайн реклама и SEO промоция, когато трябва да преминете през всички възможни пермутации на думи в заявка за търсене:
В математиката тази операция се нарича Декартов продукт. Официалната дефиниция е следната: декартовото произведение на множества A и B е множеството от всички двойки, чийто първи компонент принадлежи на множество A, а вторият компонент принадлежи на множество B. Освен това елементите на множествата могат да бъдат както числа и текст.
Преведено на човешки език това означава, че ако в множество А имаме например думите „бяло” и „червено”, а в множество Б „BMW” и „Мерцедес”, то след декартовото произведение на тези две множества имаме get на изхода е набор от всички възможни варианти на фрази, съставени от думите от двата списъка:
- бяло бмв
- червено бмв
- бял мерцедес
- червен мерцедес
… т.е. точно това, от което се нуждаем. Нека да разгледаме няколко начина за решаване на тази задача в Excel.
Метод 1. Формули
Да започнем с формулите. Да приемем, че като първоначални данни имаме три списъка с оригинални думи в колони A, B и C, съответно, и броят на елементите във всеки списък може да варира:
Първо, нека направим три колони с индекси, т.е. поредни номера на думите от всеки списък във всички възможни комбинации. Първият ред от единици (E2:G2) ще бъде въведен ръчно, а за останалите ще използваме следната формула:
Логиката тук е проста: ако индексът в горната предишна клетка вече е достигнал края на списъка, т.е. е равен на броя елементи в списъка, изчислен от функцията COUNT (COUNTA), след което започваме отново номерирането. В противен случай увеличаваме индекса с 1. Обърнете специално внимание на умното фиксиране на диапазоните със знаци за долар ($), така че да можете да копирате формулата надолу и надясно.
Сега, след като имаме поредните номера на думите, от които се нуждаем от всеки списък, можем да извлечем самите думи с помощта на функцията INDEX (ИНДЕКС) в три отделни колони:
Ако не сте срещали тази функция в работата си преди, тогава силно ви съветвам да я изучите поне диагонално - тя помага в много ситуации и е полезна не по-малко (и дори повече!) ВПР (VLOOKUP).
Е, след това остава само да залепите получените фрагменти ред по ред, като използвате символа за конкатенация (&):
… или (ако имате най-новата версия на Excel) с удобната функция COMBINE (СЪЕДИНЯВАНЕ НА ТЕКСТА), който може да залепи цялото съдържание на посочените клетки чрез даден разделителен знак (интервал):
Метод 2. Чрез Power Query
Power Query е мощна добавка за Microsoft Excel, която изпълнява две основни задачи: 1. зареждане на данни в Excel от почти всеки външен източник и 2. всички видове трансформации на заредени таблици. Power Query вече е вграден в Excel 2016-2019, а за Excel 2010-2013 е инсталиран като отделна добавка (можете да я изтеглите безплатно от официалния сайт на Microsoft). Ако все още не сте започнали да използвате Power Query в работата си, тогава е време да помислите за това, защото трансформациите като описаните по-горе се извършват лесно и естествено, само с няколко движения.
Първо, нека заредим списъците с източници като отделни заявки в Power Query. За да направите това, за всяка таблица изпълнете следните стъпки:
- Нека превърнем масите в „умни“ с бутон Форматирайте като таблица етикет Начало (Начало — Форматиране като таблица) или клавишна комбинация Ctrl+T. Всяка таблица автоматично ще получи име Таблица 1,2,3…, което обаче може да се промени при желание в раздела конструктор (Дизайн).
- След като зададете активната клетка в таблицата, натиснете бутона От масата (От таблицата) етикет Дата (Дата) или на раздела Запитване за захранване (ако сте го инсталирали като отделна добавка за Excel 2010-2013).
- В прозореца на редактора на заявки, който се отваря, изберете командата Начало — Затвори и зареди — Затвори и зареди в… (Начало — Close&Load — Close&Load to..) и след това опцията Просто създайте връзка (Създаване само на връзка). Това ще остави заредената таблица в паметта и ще позволи достъп до нея в бъдеще.
Ако направите всичко правилно, тогава изходът в десния панел трябва да бъде три заявки в режим Само връзка с нашите имена на таблици:
Сега щракнете с десния бутон върху първата заявка и изберете командата връзка (Референтен)за да направите негово копие с възможност за актуализиране и след това добавете допълнителна колона към данните чрез командата Добавяне на колона ž – Персонализирана колона (Добавяне на колона -ž персонализирана колона). В прозореца за въвеждане на формула въведете името на новата колона (например Fragment2) и изключително прост израз като формула:
=Таблица2
... т.е., с други думи, името на второто запитване:
След като кликнете върху OK ще видим нова колона, във всяка клетка от която ще има вложена таблица с фрази от втората таблица (можете да видите съдържанието на тези таблици, ако щракнете на фона на клетката до думата Маса):
Остава да разширите цялото съдържание на тези вложени таблици, като използвате бутона с двойни стрелки в заглавката на получената колона и премахнете отметката Използвайте оригиналното име на колона като префикс (Използвайте оригиналното име на колона като префикс):
... и получаваме всички възможни комбинации от елементи от първите два комплекта:
Освен това всичко е подобно. Добавете друга изчислена колона с формулата:
=Таблица3
… и след това отново разширете вложените таблици – и вече имаме всички възможни опции за пермутиране на думи съответно от трите набора:
Остава да изберете и трите колони отляво надясно, като държите Ctrlи свържете тяхното съдържание, разделено с интервали, като използвате командата Обединяване на колони (Обединяване на колони) от раздела Трансформация (Трансформиране):
Получените резултати могат да бъдат разтоварени обратно в листа с вече познатата команда Начало — Затвори и зареди — Затвори и зареди в… (Начало — Close&Load — Close&Load to..):
Ако в бъдеще нещо се промени в нашите изходни таблици с фрагменти, тогава ще бъде достатъчно просто да актуализирате генерираната заявка, като щракнете с десния бутон върху получената таблица и изберете командата Актуализиране и запазване (Опресняване) или чрез натискане на клавишната комбинация Ctrl+Друг+F5.
- Какво е Power Query, Power Pivot, Power Map и Power BI и защо се нуждаят от потребител на Excel
- Създаване на диаграма на Гант в Power Query
- 5 начина за използване на функцията ИНДЕКС