Kwanan nan, wani abokina ya matso kusa da ni tare da neman taimako tare da tsarar duk wata magana mai yuwuwa da ke kunshe da saitin kalmomin da aka bayar. Matsalolin irin wannan na iya tasowa yayin tattara jerin kalmomi da jimloli don tallan kan layi da haɓaka SEO, lokacin da kuke buƙatar shiga cikin duk yuwuwar canza kalmomin a cikin tambayar nema:
A cikin ilimin lissafi, ana kiran wannan aiki Samfurin Cartesian. Ma'anar hukuma ita ce kamar haka: samfurin Cartesian na saiti A da B shine saitin kowane nau'i-nau'i, ɓangaren farko wanda shine saitin A, ɓangaren na biyu kuma shine saitin B. Bugu da ƙari, abubuwan saiti na iya zama duka biyun. lambobi da rubutu.
Fassara zuwa harshen ɗan adam, wannan yana nufin cewa idan a cikin saitin A muna da, misali, kalmomin "farar fata" da "ja", kuma a cikin saitin B "BMW" da "Mercedes", sannan bayan samfurin Cartesian na waɗannan saiti guda biyu. samu kan fitarwa shine saitin duk bambance-bambancen jimloli masu yuwuwa, waɗanda aka yi su da kalmomin jeridu biyu:
- bmw fari
- ruwa bmw
- farin Mercedes
- ja Mercedes
... watau kawai abin da muke bukata. Bari mu kalli hanyoyi guda biyu don magance wannan aikin a cikin Excel.
Hanyar 1. Formules
Bari mu fara da dabara. Bari mu ɗauka cewa a matsayin bayanan farko muna da lissafin asali guda uku a cikin ginshiƙan A, B da C, bi da bi, kuma adadin abubuwan da ke cikin kowane jeri na iya bambanta:
Da farko, bari mu yi ginshiƙai guda uku tare da fihirisa, watau lambobi na yau da kullun na kalmomi daga kowane jeri a cikin dukkan haɗin kai. Za a shigar da layin farko na raka'a (E2:G2) da hannu, sauran kuma za mu yi amfani da dabara mai zuwa:
Hankali a nan yana da sauƙi: idan fihirisar da ke cikin babbar tantanin halitta ta baya ta riga ta kai ƙarshen jeri, watau daidai yake da adadin abubuwan da ke cikin lissafin da aikin ya ƙidaya. COUNT (COUNTA), sannan mu sake kunna lamba. In ba haka ba, muna ƙara ƙididdiga ta 1. Kula da hankali na musamman ga ƙwararren gyare-gyare na jeri tare da alamun dollar ($) don ku iya kwafin dabarar ƙasa da dama.
Yanzu da muke da ainihin lambobin kalmomin da muke buƙata daga kowane jeri, za mu iya fitar da kalmomin da kansu ta amfani da aikin INDEX (INDEX) zuwa ginshiƙai daban-daban guda uku:
Idan ba ku ga wannan aikin ba a cikin aikinku a baya, to, ina ba ku shawara sosai da ku yi nazarin shi aƙalla diagonally - yana taimakawa a cikin yanayi da yawa kuma yana da amfani ba ƙasa ba (har ma da ƙari!) VPR (VLOOKUP).
To, bayan haka, ya rage kawai don manne sakamakon gutsuwar layin ta layi ta amfani da alamar haɗuwa (&):
... ko (idan kuna da sabuwar sigar Excel) tare da aikin mai amfani HADE (TEXTJOIN), wanda zai iya manna dukkan abubuwan da ke cikin ƙayyadaddun sel ta hanyar da aka ba da hali (sarari):
Hanyar 2. Ta hanyar Tambayar Wuta
Power Query shine ƙarawa mai ƙarfi don Microsoft Excel wanda ke aiwatar da manyan ayyuka guda biyu: 1. loda bayanai zuwa Excel daga kusan kowane tushe na waje, da 2. kowane nau'in canji na tebur da aka ɗora. An riga an gina Query Query a cikin Excel 2016-2019, kuma don Excel 2010-2013 an shigar dashi azaman ƙari daban (zaku iya saukar da shi daga gidan yanar gizon Microsoft na hukuma kyauta). Idan har yanzu ba ku fara amfani da Query Query a cikin aikinku ba, to lokaci yayi da za ku yi tunani game da shi, saboda sauye-sauye kamar waɗanda aka bayyana a sama ana yin su cikin sauƙi kuma a zahiri, a cikin ƙungiyoyi biyu kawai.
Da farko, bari mu loda lissafin tushen azaman tambayoyi daban-daban a cikin Query Query. Don yin wannan, ga kowane tebur, yi matakai masu zuwa:
- Bari mu juya tebur zuwa "masu wayo" tare da maɓalli Tsara azaman tebur tab Gida (Gida - Tsarin azaman Tebur) ko gajeriyar hanyar keyboard Ctrl+T. Kowane tebur za a ba shi suna ta atomatik Table 1,2,3…, wanda, duk da haka, ana iya canzawa idan ana so akan shafin Constructor (Zane).
- Bayan saita tantanin halitta mai aiki a cikin tebur, danna maɓallin Daga tebur (Daga Tebur) tab data (Kwanan wata) ko a kan tab Tambayar .arfi (idan kun shigar dashi azaman ƙari daban don Excel 2010-2013).
- A cikin taga editan tambaya da ke buɗewa, zaɓi umarni Gida - Rufe kuma Loda - Rufe kuma Load a… (Gida - Rufe&Load - Rufe & Load zuwa ..) sannan zabin Kawai ƙirƙirar haɗi (ƙirƙiri haɗi kawai). Wannan zai bar teburin da aka ɗora a cikin ƙwaƙwalwar ajiya kuma ya ba da damar samun dama ga shi nan gaba.
Idan kun yi duk abin da ke daidai, to, fitarwa a cikin sashin dama ya kamata ya zama buƙatun uku a cikin yanayin Haɗi kawai tare da sunayen teburin mu:
Yanzu danna dama akan tambayar farko kuma zaɓi umarnin link (Magana)don yin kwafinsa mai sabuntawa, sannan ƙara ƙarin shafi zuwa bayanan ta hanyar umarni Ƙara shafi ž – ginshiƙi na al'ada (Ƙara Rukunin -ž Shagon Al'ada). A cikin taga shigar da dabara, shigar da sunan sabon ginshiƙi (misali, Fragment2) da magana mai sauƙin gaske azaman dabara:
=Table2
… watau, a wasu kalmomi, sunan tambaya ta biyu:
Bayan danna kan OK za mu ga sabon ginshiƙi, a cikin kowane tantanin halitta wanda za a sami tebur na gida mai ɗauke da jimloli daga tebur na biyu (zaka iya ganin abubuwan da ke cikin waɗannan tebur idan ka danna bayan tantanin halitta kusa da kalmar. Table):
Ya rage don faɗaɗa duk abubuwan da ke cikin waɗannan tebur ɗin gida ta amfani da maballin tare da kibiyoyi biyu a cikin taken ginshiƙi da kuma cirewa. Yi amfani da sunan ginshiƙi na asali azaman prefix (Yi amfani da sunan shafi na asali azaman prefix):
... kuma muna samun dukkan yuwuwar haɗakar abubuwa daga saiti biyu na farko:
Bugu da ari, duk abin da yake kama. Ƙara wani shafi mai ƙididdigewa tare da dabara:
=Table3
…, sa'an nan kuma sake faɗaɗa allunan da aka gina - kuma yanzu mun riga mun sami duk yuwuwar zaɓuka don daidaita kalmomi daga saiti uku, bi da bi:
Ya rage don zaɓar duk ginshiƙai uku daga hagu zuwa dama, riƙe Ctrl, da kuma haɗa abubuwan da ke cikin su ta hanyar sarari ta amfani da umarnin Haɗa ginshiƙai (Haɗa ginshiƙai) daga tab Sake Kama (canza):
Za a iya sauke sakamakon da aka samu a baya akan takardar tare da saban umarni Gida - Rufe kuma Loda - Rufe kuma Load a… (Gida - Rufe&Load - Rufe & Load zuwa ..):
Idan a nan gaba wani abu ya canza a cikin tebur na tushenmu tare da guntu, to zai isa kawai don sabunta tambayar da aka samar ta danna dama akan teburin da aka samu kuma zaɓi umarnin. Sabunta & Ajiye (Sake sabuntawa) ko ta hanyar latsa gajeriyar hanyar madannai Ctrl+alt+F5.
- Menene Tambayar Wuta, Wutar Wuta, Taswirar Wuta da Power BI kuma me yasa suke buƙatar mai amfani da Excel
- Ƙirƙirar Taswirar Gantt a cikin Query Query
- Hanyoyi 5 don amfani da aikin INDEX