Неодамна, еден мој пријател ми пријде со барање да помогнам во создавањето на сите можни фрази што се состојат од збир на дадени зборови. Проблеми од овој вид може да се појават при составување списоци на клучни зборови и фрази за онлајн рекламирање и промоција на SEO, кога треба да ги поминете сите можни пермутации на зборови во барањето за пребарување:
Во математиката, оваа операција се нарекува Декартов производ. Официјалната дефиниција е следна: Декартов производ на множествата А и Б е множеството од сите парови, чија прва компонента припаѓа на множеството А, а втората компонента припаѓа на множеството Б. Покрај тоа, елементите на множествата можат да бидат и двете броеви и текст.
Преведено на човечки јазик, тоа значи дека ако во множеството А ги имаме, на пример, зборовите „бело“ и „црвено“, а во множеството Б „БМВ“ и „Мерцедес“, тогаш по Декартовиот производ од овие две групи get on the output е збир на сите можни варијанти на фрази, составен од зборовите на двете листи:
- бела БМВ
- црвено БМВ
- бел мерцедес
- црвен мерцедес
… т.е. само она што ни треба. Ајде да погледнеме неколку начини за решавање на оваа задача во Excel.
Метод 1. Формули
Да почнеме со формули. Да претпоставиме дека како почетни податоци имаме три списоци со оригинални зборови во колоните A, B и C, соодветно, а бројот на елементи во секоја листа може да варира:
Прво, да направиме три колони со индекси, односно редни броеви на зборови од секоја листа во сите можни комбинации. Првиот ред на единици (E2:G2) ќе се внесе рачно, а за останатите ќе ја користиме следнава формула:
Логиката овде е едноставна: ако индексот во супериорната претходна ќелија веќе го достигнал крајот на листата, т.е. е еднаков на бројот на елементи во списокот пресметан со функцијата COUNT (COUNTA), потоа го рестартираме нумерирањето. Во спротивно, го зголемуваме индексот за 1. Обрнете посебно внимание на паметното фиксирање на опсезите со знаци за долари ($) за да можете да ја копирате формулата надолу и надесно.
Сега кога ги имаме редните броеви на зборовите што ни се потребни од секоја листа, можеме да ги извлечеме самите зборови користејќи ја функцијата Индекс (ИНДЕКС) во три посебни колони:
Ако досега не сте ја сретнале оваа функција во вашата работа, тогаш силно ве советувам да ја проучите барем дијагонално - помага во многу ситуации и е корисна не помалку (и уште повеќе!) VPR (VLOOKUP).
Па, после тоа, останува само да се залепат добиените фрагменти линија по линија користејќи го симболот за конкатенација (&):
… или (ако ја имате најновата верзија на Excel) со практична функција КОМБИНА (ПРИКЛУЧЕТЕ ТЕКСТ), кој може да ја залепи целата содржина на наведените ќелии преку даден сепараторски знак (простор):
Метод 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).
- Во прозорецот за уредувач на прашања што се отвора, изберете ја командата Дома — Затвори и вчитај — Затвори и вчитај во… (Дома - Затвори&Вчитај - Затвори&Вчитај до..) а потоа опцијата Само создадете врска (Создадете само врска). Ова ќе ја остави вчитаната табела во меморија и ќе овозможи пристап до неа во иднина.
Ако правите сè правилно, тогаш излезот во десниот панел треба да биде три барања во режимот Само врска со нашите имиња на табели:
Сега кликнете со десното копче на првото барање и изберете ја командата линк (Референца)да направите нејзина копија што може да се ажурира, а потоа да додадете дополнителна колона на податоците преку командата Додавање колона ž – Прилагодена колона (Додај колона -ž Прилагодена колона). Во прозорецот за внесување формула, внесете го името на новата колона (на пример, Fragment2) и исклучително едноставен израз како формула:
=Табела2
… т.е., со други зборови, името на второто барање:
По кликнување на OK ќе видиме нова колона, во секоја ќелија од која ќе има вгнездена табела со фрази од втората табела (содржината на овие табели можете да ја видите ако кликнете во позадината на ќелијата до зборот Табела):
Останува да се прошири целата содржина на овие вгнездени табели користејќи го копчето со двојни стрелки во заглавието на добиената колона и отштиклирање Користете го оригиналното име на колоната како префикс (Користете го оригиналното име на колоната како префикс):
… и ги добиваме сите можни комбинации на елементи од првите две групи:
Понатаму, сè е слично. Додадете уште една пресметана колона со формулата:
=Табела3
…, а потоа повторно проширете ги вгнездените табели – и сега веќе ги имаме сите можни опции за менување зборови од трите групи, соодветно:
Останува да ги изберете сите три колони од лево кон десно, држејќи Ctrl, и спојте ја нивната содржина одделена со празни места користејќи ја командата Спојување на колони (Спојување на колони) од јазичето Трансформација (Трансформирајте):
Добиените резултати може да се истоварат назад на листот со веќе познатата команда Дома — Затвори и вчитај — Затвори и вчитај во… (Дома - Затвори&Вчитај - Затвори&Вчитај до..):
Ако во иднина нешто се промени во нашите изворни табели со фрагменти, тогаш ќе биде доволно само да се ажурира генерираното барање со десен клик на добиената табела и избирање на командата Ажурирајте и зачувајте (Освежи) или со притискање на кратенката на тастатурата Ctrl+Alt+F5.
- Што е Power Query, Power Pivot, Power Map и Power BI и зошто им е потребен корисник на Excel
- Креирање на Gantt графикон во Power Query
- 5 начини за користење на функцијата INDEX