Kohët e fundit, një miku im m'u afrua me një kërkesë për të ndihmuar në gjenerimin e të gjitha frazave të mundshme që përbëhen nga një grup fjalësh të dhëna. Probleme të këtij lloji mund të lindin gjatë përpilimit të listave të fjalëve kyçe dhe frazave për reklamimin në internet dhe promovimin e SEO, kur duhet të kaloni të gjitha ndërrimet e mundshme të fjalëve në një pyetje kërkimi:
Në matematikë, ky operacion quhet Produkt kartezian. Përkufizimi zyrtar është si më poshtë: prodhimi kartezian i grupeve A dhe B është bashkësia e të gjitha çifteve, përbërësi i parë i të cilit i përket grupit A dhe komponenti i dytë i përket grupit B. Për më tepër, elementët e grupeve mund të jenë të dyja numrat dhe teksti.
E përkthyer në gjuhën njerëzore, kjo do të thotë se nëse në grupin A kemi, për shembull, fjalët "e bardhë" dhe "e kuqe", dhe në grupin B "BMW" dhe "Mercedes", atëherë pas produktit kartezian të këtyre dy grupeve ne marrë në dalje është grupi i të gjitha varianteve të mundshme të frazave, i përbërë nga fjalët e të dy listave:
- bmw e bardhe
- bmw e kuqe
- Mercedes i bardhë
- mercedes i kuq
… dmth vetëm ajo që na nevojitet. Le të shohim disa mënyra për të zgjidhur këtë detyrë në Excel.
Metoda 1. Formulat
Le të fillojmë me formulat. Le të supozojmë se si të dhëna fillestare kemi tre lista të fjalëve origjinale në kolonat A, B dhe C, përkatësisht, dhe numri i elementeve në secilën listë mund të ndryshojë:
Së pari, le të bëjmë tre kolona me indekse, dmth. numra rendorë fjalësh nga secila listë në të gjitha kombinimet e mundshme. Rreshti i parë i njësive (E2:G2) do të futet manualisht, dhe për pjesën tjetër do të përdorim formulën e mëposhtme:
Logjika këtu është e thjeshtë: nëse indeksi në qelizën e mëparshme superiore ka arritur tashmë në fund të listës, dmth është i barabartë me numrin e elementeve në listë të llogaritur nga funksioni COUNT (COUNTA), pastaj rifillojmë numërimin. Përndryshe, ne e rrisim indeksin me 1. Kushtojini vëmendje të veçantë fiksimit të zgjuar të diapazoneve me shenja dollari ($) në mënyrë që të mund të kopjoni formulën poshtë dhe djathtas.
Tani që kemi numrat rendorë të fjalëve që na duhen nga secila listë, mund t'i nxjerrim vetë fjalët duke përdorur funksionin INDEKSI (INDEX) në tre kolona të veçanta:
Nëse nuk e keni hasur më parë këtë funksion në punën tuaj, atëherë ju këshilloj fuqimisht ta studioni atë të paktën diagonalisht - ndihmon në shumë situata dhe është i dobishëm jo më pak (dhe madje edhe më shumë!) VPR (VLOOKUP).
Epo, pas kësaj, mbetet vetëm të ngjitni fragmentet që rezultojnë rresht pas rreshti duke përdorur simbolin e lidhjes (&):
… ose (nëse keni versionin më të fundit të Excel) me funksionin e dobishëm COMBINE (TEKSTI BON), i cili mund të ngjitë të gjithë përmbajtjen e qelizave të specifikuara përmes një karakteri të caktuar ndarës (hapësirë):
Metoda 2. Përmes Power Query
Power Query është një shtesë e fuqishme për Microsoft Excel që kryen dy detyra kryesore: 1. ngarkimin e të dhënave në Excel nga pothuajse çdo burim i jashtëm dhe 2. të gjitha llojet e transformimeve të tabelave të ngarkuara. Power Query është ndërtuar tashmë në Excel 2016-2019, dhe për Excel 2010-2013 është instaluar si një shtesë e veçantë (mund ta shkarkoni falas nga faqja zyrtare e Microsoft). Nëse ende nuk keni filluar të përdorni Power Query në punën tuaj, atëherë është koha të mendoni për këtë, sepse transformimet si ato të përshkruara më sipër bëhen atje lehtësisht dhe natyrshëm, në vetëm disa lëvizje.
Së pari, le të ngarkojmë listat burimore si pyetje të veçanta në Power Query. Për ta bërë këtë, për secilën tabelë, kryeni hapat e mëposhtëm:
- Le t'i kthejmë tabelat në "të zgjuara" me një buton Formatoni si tabelë tab Fillimi (Shtëpia - Formatoni si tabelë) ose shkurtore të tastierës Ctrl+T. Secilës tabelë automatikisht do t'i jepet një emër Tabela 1,2,3…, e cila, megjithatë, mund të ndryshohet nëse dëshironi në skedën konstruktor (Dizajn).
- Pasi të keni vendosur qelizën aktive në tabelë, shtypni butonin Nga tavolina (Nga tabela) tab Data (Data) ose në skedën Kërkesa e energjisë (nëse e keni të instaluar si një shtesë të veçantë për Excel 2010-2013).
- Në dritaren e redaktuesit të pyetjeve që hapet, zgjidhni komandën Faqja kryesore — Mbyll dhe ngarko — Mbylle dhe ngarko në… (Shtëpia — Mbyll&Ngarko — Mbylle&Ngarko te..) dhe më pas opsioni Thjesht krijoni një lidhje (Krijo vetëm lidhje). Kjo do ta lërë tabelën e ngarkuar në memorie dhe do të lejojë aksesin e saj në të ardhmen.
Nëse bëni gjithçka në mënyrë korrekte, atëherë dalja në panelin e djathtë duhet të jetë tre kërkesa në modalitet Vetëm lidhje me emrat e tabelave tona:
Tani klikoni me të djathtën në pyetjen e parë dhe zgjidhni komandën lidhje (Referenca)për të bërë një kopje të azhurnueshme të saj dhe më pas shtoni një kolonë shtesë në të dhënat përmes komandës Shtimi i një kolone ž – Kolona e personalizuar (Shto kolonë -ž kolonë të personalizuar). Në dritaren e futjes së formulës, shkruani emrin e kolonës së re (për shembull, Fragment2) dhe një shprehje jashtëzakonisht të thjeshtë si formulë:
=Tabela 2
… dmth, me fjalë të tjera, emri i pyetjes së dytë:
Pas klikimit OK do të shohim një kolonë të re, në secilën qelizë të së cilës do të ketë një tabelë të mbivendosur me fraza nga tabela e dytë (mund të shihni përmbajtjen e këtyre tabelave nëse klikoni në sfondin e qelizës pranë fjalës Tryezë):
Mbetet për të zgjeruar të gjitha përmbajtjet e këtyre tabelave të mbivendosura duke përdorur butonin me shigjeta të dyfishta në kokën e kolonës që rezulton dhe duke hequr zgjedhjen Përdorni emrin origjinal të kolonës si parashtesë (Përdor emrin origjinal të kolonës si parashtesë):
… dhe marrim të gjitha kombinimet e mundshme të elementeve nga dy grupet e para:
Për më tepër, gjithçka është e ngjashme. Shtoni një kolonë tjetër të llogaritur me formulën:
=Tabela 3
…, dhe pastaj zgjeroni përsëri tabelat e mbivendosura – dhe tani ne kemi tashmë të gjitha opsionet e mundshme për ndryshimin e fjalëve nga tre grupet, përkatësisht:
Mbetet për të zgjedhur të tre kolonat nga e majta në të djathtë, duke mbajtur Ctrl, dhe lidhni përmbajtjen e tyre të ndara me hapësira duke përdorur komandën Bashkoni kolonat (Shkrija e kolonave) nga skeda Transformim (Transformo):
Rezultatet që rezultojnë mund të shkarkohen përsëri në fletë me komandën tashmë të njohur Faqja kryesore — Mbyll dhe ngarko — Mbylle dhe ngarko në… (Shtëpia — Mbyll&Ngarko — Mbylle&Ngarko te..):
Nëse në të ardhmen diçka ndryshon në tabelat tona burimore me fragmente, atëherë do të mjaftojë vetëm të përditësoni pyetjen e krijuar duke klikuar me të djathtën në tabelën që rezulton dhe duke zgjedhur komandën Përditëso & Ruaj (Rifresko) ose duke shtypur shkurtoren e tastierës Ctrl+Ndalet+F5.
- Çfarë është Power Query, Power Pivot, Power Map dhe Power BI dhe pse u nevojitet një përdorues Excel
- Krijimi i një grafiku Gantt në Power Query
- 5 mënyra për të përdorur funksionin INDEX