Kamakailan lamang, nilapitan ako ng isang kaibigan ko na may kahilingan na tumulong sa pagbuo ng lahat ng posibleng mga parirala na binubuo ng isang hanay ng mga ibinigay na salita. Maaaring lumitaw ang mga ganitong problema kapag nag-compile ng mga listahan ng mga keyword at parirala para sa online na advertising at promosyon ng SEO, kapag kailangan mong dumaan sa lahat ng posibleng permutasyon ng mga salita sa isang query sa paghahanap:
Sa matematika, ang operasyong ito ay tinatawag Kartesyan produkto. Ang opisyal na kahulugan ay ang mga sumusunod: ang produkto ng Cartesian ng mga hanay A at B ay ang hanay ng lahat ng mga pares, ang unang bahagi nito ay kabilang sa hanay A, at ang pangalawang bahagi ay kabilang sa hanay ng B. Bukod dito, ang mga elemento ng mga hanay ay maaaring pareho. mga numero at teksto.
Isinalin sa wika ng tao, nangangahulugan ito na kung sa set A mayroon tayo, halimbawa, ang mga salitang "puti" at "pula", at sa set B "BMW" at "Mercedes", pagkatapos ay pagkatapos ng produkto ng Cartesian ng dalawang hanay na ito ay get on the output ay ang hanay ng lahat ng posibleng variant ng mga parirala, na binubuo ng mga salita ng parehong listahan:
- puting bmw
- pulang bmw
- puting Mercedes
- pulang mercedes
… ie kung ano lang ang kailangan natin. Tingnan natin ang ilang paraan upang malutas ang gawaing ito sa Excel.
Paraan 1. Mga Formula
Magsimula tayo sa mga formula. Ipagpalagay natin na bilang paunang data mayroon tayong tatlong listahan ng mga orihinal na salita sa mga column A, B at C, ayon sa pagkakabanggit, at maaaring mag-iba ang bilang ng mga elemento sa bawat listahan:
Una, gumawa tayo ng tatlong column na may mga indeks, ibig sabihin, ordinal na bilang ng mga salita mula sa bawat listahan sa lahat ng posibleng kumbinasyon. Ang unang hilera ng mga yunit (E2:G2) ay manu-manong ipapasok, at para sa iba ay gagamitin namin ang sumusunod na formula:
Ang lohika dito ay simple: kung ang index sa superior nakaraang cell ay umabot na sa dulo ng listahan, ibig sabihin ay katumbas ng bilang ng mga elemento sa listahan na kinakalkula ng function. COUNT (COUNTA), pagkatapos ay i-restart namin ang pagnunumero. Kung hindi, pinapataas namin ang index ng 1. Bigyang-pansin ang matalinong pag-aayos ng mga hanay na may mga dollar sign ($) upang makopya mo ang formula pababa at sa kanan.
Ngayon na mayroon na tayong mga ordinal na numero ng mga salita na kailangan natin mula sa bawat listahan, maaari nating kunin ang mga salita mismo gamit ang function INDEX (INDEX) sa tatlong magkakahiwalay na hanay:
Kung hindi mo pa nakikita ang function na ito sa iyong trabaho dati, masidhi kong ipinapayo sa iyo na pag-aralan ito nang hindi bababa sa pahilis - nakakatulong ito sa maraming sitwasyon at kapaki-pakinabang nang hindi kukulangin (at higit pa!) VPR (VLOOKUP).
Buweno, pagkatapos nito, nananatili lamang upang idikit ang mga nagresultang mga fragment na linya sa pamamagitan ng linya gamit ang simbolo ng concatenation (&):
… o (kung mayroon kang pinakabagong bersyon ng Excel) na may madaling gamiting function Pagsamahin (TEXTJOIN), na maaaring idikit ang buong nilalaman ng tinukoy na mga cell sa pamamagitan ng ibinigay na karakter ng separator (espasyo):
Paraan 2. Sa pamamagitan ng Power Query
Ang Power Query ay isang malakas na add-in para sa Microsoft Excel na gumaganap ng dalawang pangunahing gawain: 1. paglo-load ng data sa Excel mula sa halos anumang panlabas na pinagmulan, at 2. lahat ng uri ng pagbabago ng mga na-load na talahanayan. Ang Power Query ay binuo na sa Excel 2016-2019, at para sa Excel 2010-2013 ito ay naka-install bilang isang hiwalay na add-in (maaari mo itong i-download mula sa opisyal na website ng Microsoft nang libre). Kung hindi mo pa nasisimulang gamitin ang Power Query sa iyong trabaho, oras na para pag-isipan ito, dahil ang mga pagbabagong tulad ng mga inilarawan sa itaas ay ginagawa doon nang madali at natural, sa loob lang ng ilang paggalaw.
Una, i-load natin ang mga listahan ng pinagmulan bilang hiwalay na mga query sa Power Query. Upang gawin ito, para sa bawat talahanayan, gawin ang mga sumusunod na hakbang:
- Gawin nating "matalino" ang mga talahanayan gamit ang isang pindutan I-format bilang isang talahanayan tab Tahanan (Home — Format bilang Talahanayan) o keyboard shortcut Ctrl+T. Ang bawat talahanayan ay awtomatikong bibigyan ng isang pangalan Talahanayan1,2,3…, na, gayunpaman, ay maaaring baguhin kung ninanais sa tab Tagagawa (Disenyo).
- Ang pagkakaroon ng itakda ang aktibong cell sa talahanayan, pindutin ang pindutan Mula sa mesa (Mula sa Talahanayan) tab data (Petsa) o sa tab kapangyarihan query (kung na-install mo ito bilang isang hiwalay na add-in para sa Excel 2010-2013).
- Sa window ng editor ng query na bubukas, piliin ang command Tahanan — Isara at I-load — Isara at I-load sa… (Home — Isara&I-load — Isara&I-load sa..) at pagkatapos ay ang pagpipilian Gumawa lang ng koneksyon (Gumawa lamang ng koneksyon). Iiwan nito ang na-load na talahanayan sa memorya at papayagan itong ma-access sa hinaharap.
Kung gagawin mo nang tama ang lahat, kung gayon ang output sa kanang panel ay dapat na tatlong kahilingan sa mode Koneksyon lang kasama ang aming mga pangalan ng talahanayan:
Ngayon mag-right-click sa unang query at piliin ang command link (Sanggunian)upang gumawa ng naa-update na kopya nito, at pagkatapos ay magdagdag ng karagdagang column sa data sa pamamagitan ng command Pagdaragdag ng column ž – Custom na column (Magdagdag ng Column -ž Custom Column). Sa window ng formula input, ilagay ang pangalan ng bagong column (halimbawa, Fragment2) at isang napakasimpleng expression bilang isang formula:
=Talahanayan2
… ibig sabihin, sa madaling salita, ang pangalan ng pangalawang query:
Pagkatapos ng pag-click sa OK makakakita tayo ng bagong column, sa bawat cell kung saan magkakaroon ng nested table na may mga parirala mula sa pangalawang table (makikita mo ang mga nilalaman ng mga table na ito kung mag-click ka sa background ng cell sa tabi ng salita mesa):
Nananatili itong palawakin ang lahat ng nilalaman ng mga nested table na ito gamit ang button na may double arrow sa header ng resultang column at alisan ng check Gamitin ang orihinal na pangalan ng column bilang prefix (Gamitin ang orihinal na pangalan ng column bilang prefix):
… at nakukuha namin ang lahat ng posibleng kumbinasyon ng mga elemento mula sa unang dalawang set:
Dagdag pa, ang lahat ay magkatulad. Magdagdag ng isa pang kinakalkula na column na may formula:
=Talahanayan3
…, at pagkatapos ay palawakin muli ang mga nested na talahanayan – at ngayon ay mayroon na tayong lahat ng posibleng opsyon para sa pag-permute ng mga salita mula sa tatlong set, ayon sa pagkakabanggit:
Ito ay nananatiling piliin ang lahat ng tatlong mga hanay mula kaliwa hanggang kanan, hawak Ctrl, at pagdugtungin ang kanilang mga nilalaman na pinaghihiwalay ng mga puwang gamit ang command Pagsamahin ang mga column (Pagsamahin ang Mga Hanay) mula sa tab Pagbabagong-anyo (Pagbabago):
Ang mga resultang resulta ay maaaring i-unload pabalik sa sheet gamit ang pamilyar na utos Tahanan — Isara at I-load — Isara at I-load sa… (Home — Isara&I-load — Isara&I-load sa..):
Kung sa hinaharap ay may magbabago sa aming mga source table na may mga fragment, kung gayon ito ay sapat lamang na i-update ang nabuong query sa pamamagitan ng pag-right-click sa resultang talahanayan at pagpili ng command I-update at I-save (Refresh) o sa pamamagitan ng pagpindot sa keyboard shortcut Ctrl+Alt+F5.
- Ano ang Power Query, Power Pivot, Power Map at Power BI at bakit kailangan nila ng Excel user
- Paggawa ng Gantt Chart sa Power Query
- 5 paraan para gamitin ang function na INDEX