Nemrég megkeresett egy barátom azzal a kéréssel, hogy segítsek az összes lehetséges kifejezés generálásában, amely adott szavak halmazából áll. Ilyen jellegű problémák adódhatnak az online hirdetésekhez és a SEO promóciókhoz szükséges kulcsszavak és kifejezések listáinak összeállításakor, amikor a keresési lekérdezésben a szavak összes lehetséges permutációján keresztül kell mennie:
A matematikában ezt a műveletet ún Descartes termék. A hivatalos definíció a következő: az A és B halmazok derékszögű szorzata az összes olyan pár halmaza, amelynek első komponense az A halmazhoz, a második komponense pedig a B halmazhoz tartozik. számok és szöveg.
Ez emberi nyelvre lefordítva azt jelenti, hogy ha az A halmazban például a „fehér” és a „piros”, a B halmazban pedig a „BMW” és „Mercedes” szavak szerepelnek, akkor e két halmaz derékszögű szorzata után A get on output a kifejezések összes lehetséges változatának halmaza, amely mindkét lista szavaiból áll:
- fehér bmw
- piros bmw
- fehér Mercedes
- vörös mercedes
… vagyis éppen amire szükségünk van. Nézzünk meg néhány módot ennek a feladatnak az Excelben való megoldására.
1. módszer. Képletek
Kezdjük a képletekkel. Tegyük fel, hogy kiindulási adatként három eredeti szavak listája van az A, B és C oszlopokban, és az egyes listák elemeinek száma változhat:
Először készítsünk három oszlopot indexekkel, azaz sorszámú szavakkal minden listából minden lehetséges kombinációban. Az egységek első sora (E2:G2) manuálisan kerül bevitelre, a többihez pedig a következő képletet használjuk:
A logika itt egyszerű: ha az előző felső cellában lévő index már elérte a lista végét, azaz egyenlő a függvény által kiszámított lista elemeinek számával COUNT (COUNTA), majd újraindítjuk a számozást. Ellenkező esetben az indexet 1-gyel növeljük. Különös figyelmet kell fordítani a tartományok dollárjelekkel ($) való ügyes rögzítésére, hogy a képletet le és jobbra másolhassa.
Most, hogy minden listából megvannak a szükséges szavak sorszámai, a függvény segítségével kivonhatjuk magukat a szavakat. INDEX (INDEX) három külön oszlopba:
Ha még nem találkozott ezzel a funkcióval a munkája során, akkor erősen javaslom, hogy legalább átlósan tanulmányozza át – sok helyzetben segít, és nem kevésbé (sőt még inkább!) VPR (VLOOKUP).
Nos, ezután már csak a kapott töredékeket kell soronként felragasztani az összefűzési szimbólum (&) segítségével:
… vagy (ha az Excel legújabb verziója van) a praktikus funkcióval KOMBÁJN (TEXTJOIN), amely adott elválasztó karakteren (szóközön) keresztül a megadott cellák teljes tartalmát össze tudja ragasztani:
2. módszer: Power Queryn keresztül
A Power Query a Microsoft Excel hatékony bővítménye, amely két fő feladatot lát el: 1. szinte bármilyen külső forrásból adatokat tölt be az Excelbe, és 2. a betöltött táblák mindenféle átalakítását. A Power Query már be van építve az Excel 2016-2019-be, az Excel 2010-2013-hoz pedig külön bővítményként van telepítve (ingyenesen letöltheti a Microsoft hivatalos webhelyéről). Ha még nem kezdted el a Power Query használatát a munkádban, akkor itt az ideje, hogy elgondolkodj rajta, mert a fent leírt átalakítások könnyen és természetesen, pár mozdulattal megtörténnek.
Először töltsük be a forráslistákat külön lekérdezésekként a Power Queryben. Ehhez minden táblánál hajtsa végre a következő lépéseket:
- A táblázatokat egy gombbal „okossá” alakítsuk Formázás táblázatként lap Kezdőlap (Főoldal — Táblázat formázása) vagy billentyűparancsot Ctrl+T. Minden táblázat automatikusan nevet kap 1,2,3., XNUMX., XNUMX. táblázat…, amely azonban kívánság szerint módosítható a lapon építész (Tervezés).
- Miután beállította az aktív cellát a táblázatban, nyomja meg a gombot Az asztalról (A táblázatból) lap dátum (Dátum) vagy a lapon Teljesítmény lekérdezés (ha külön bővítményként telepítette az Excel 2010–2013-hoz).
- A megnyíló lekérdezésszerkesztő ablakban válassza ki a parancsot Kezdőlap — Bezárás és betöltés — Bezárás és betöltés… (Kezdőlap — Bezárás&Betöltés — Bezárás&Betöltés ide...) majd a lehetőséget Csak hozzon létre egy kapcsolatot (Csak kapcsolat létrehozása). Ez a betöltött táblát a memóriában hagyja, és lehetővé teszi a jövőbeni elérését.
Ha mindent helyesen csinál, akkor a jobb oldali panel kimenetének három kérésnek kell lennie módban Csak csatlakozás táblázatunk neveivel:
Most kattintson a jobb gombbal az első lekérdezésre, és válassza ki a parancsot Link (Referencia)frissíthető másolatot készíteni róla, majd a paranccsal hozzáadni egy további oszlopot az adatokhoz Oszlop hozzáadása ž – Egyedi oszlop (Oszlop hozzáadása -ž Egyéni oszlop). A képlet beviteli ablakában adja meg az új oszlop nevét (például Töredék2) és egy rendkívül egyszerű kifejezést képletként:
=2. táblázat
… azaz más szóval a második lekérdezés neve:
Miután rákattintott OK egy új oszlopot fogunk látni, aminek minden cellájában lesz egy beágyazott táblázat a második táblázatból származó kifejezésekkel (e táblázatok tartalmát megtekintheti, ha a szó melletti cella hátterébe kattint Táblázat):
A beágyazott táblázatok teljes tartalmát ki kell bontani az eredményül kapott oszlop fejlécében található dupla nyíl gombbal, és törölni kell a bejelölést. Használja az eredeti oszlop nevét előtagként (Használja az eredeti oszlopnevet előtagként):
… és az első két halmaz összes lehetséges kombinációját megkapjuk:
Ráadásul minden hasonló. Adjon hozzá egy másik számított oszlopot a következő képlettel:
=3. táblázat
…, majd bontsa ki újra a beágyazott táblákat – és most már minden lehetséges lehetőségünk van a három halmazból származó szavak permutálására:
Marad a három oszlop kijelölése balról jobbra tartva Ctrl, és a paranccsal összefűzi a tartalmukat szóközzel elválasztva Oszlopok egyesítése (Oszlopok egyesítése) a lapról Átalakítás (Átalakítás):
A kapott eredményeket a már ismert paranccsal vissza lehet rakni a lapra Kezdőlap — Bezárás és betöltés — Bezárás és betöltés… (Kezdőlap — Bezárás&Betöltés — Bezárás&Betöltés ide...):
Ha a jövőben valami megváltozik a töredékes forrástábláinkban, akkor elég lesz frissíteni a generált lekérdezést úgy, hogy jobb gombbal kattintunk a kapott táblára, és kiválasztjuk a parancsot Frissítés és mentés (Frissítés) vagy a billentyűparancs megnyomásával Ctrl+más+F5.
- Mi az a Power Query, Power Pivot, Power Map és Power BI, és miért van szükségük Excel-felhasználóra
- Gantt-diagram létrehozása a Power Queryben
- 5 mód az INDEX funkció használatára