Muva nje, umngane wami weza kimi nesicelo sokusiza ekukhiqizeni yonke imishwana engaba khona ehlanganisa iqoqo lamagama anikeziwe. Izinkinga zalolu hlobo zingavela lapho kuhlanganiswa uhlu lwamagama angukhiye nemishwana yokukhangisa ku-inthanethi nokukhuthazwa kwe-SEO, lapho udinga ukudlula kukho konke ukuvunyelwa kwamagama embuzweni wosesho:
Kumathematika, lo msebenzi ubizwa ngokuthi Umkhiqizo weCartesian. Incazelo esemthethweni imi kanje: umkhiqizo weCartesian wamasethi A no-B isethi yawo wonke amapheya, ingxenye yawo yokuqala okungeyesethi A, kanti ingxenye yesibili ingeyesethi B. Ngaphezu kwalokho, izakhi zamasethi zingaba zombili. izinombolo nombhalo.
Kuhunyushelwa olimini lwabantu, lokhu kusho ukuthi uma kusethi A sinamagama, isibonelo, "mhlophe" nelithi "bomvu", futhi kusethi B "BMW" kanye "Mercedes", bese emva komkhiqizo weCartesian kulawa masethi amabili thina get on the okukhiphayo isethi yazo zonke izinhlobo ezingaba khona zemisho, ezenziwe ngamagama azo zombili izinhlu:
- bmw emhlophe
- bmw ebomvu
- iMercedes emhlophe
- ama-mercedes abomvu
… okungukuthi nje esikudingayo. Ake sibheke izindlela ezimbalwa zokuxazulula lo msebenzi ku-Excel.
Indlela 1. Amafomula
Ake siqale ngamafomula. Ake sicabange ukuthi njengedatha yokuqala sinezinhlu ezintathu zamagama asekuqaleni kumakholomu A, B kanye no-C, ngokulandelana, kanye nenani lezakhi ohlwini ngalunye lingahluka:
Okokuqala, masenze amakholomu amathathu anezinkomba, okungukuthi izinombolo ze-ordinal zamagama ohlwini ngalunye kuzo zonke izinhlanganisela ezingaba khona. Umugqa wokuqala wamayunithi (E2:G2) uzofakwa ngesandla, futhi kokunye sizosebenzisa ifomula elandelayo:
Umqondo lapha ulula: uma inkomba kuseli yangaphambili ephakeme isivele ifinyelele ekupheleni kohlu, okungukuthi ilingana nenani lezakhi ohlwini olubalwa ngomsebenzi. I-COUNT (COUNTA), bese siqala kabusha izinombolo. Uma kungenjalo, sikhulisa inkomba ngo-1. Naka ukunaka okukhethekile ekulungiseni okuhlakaniphile kwezigaba ngezimpawu zamadola ($) ukuze ukwazi ukukopisha ifomula phansi nakwesokudla.
Manje njengoba sinezinombolo ze-ordinal zamagama esiwadingayo ohlwini ngalunye, singakhipha amagama ngokwawo sisebenzisa umsebenzi INDEX (INDEX) abe amakholomu amathathu ahlukene:
Uma ungakaze uhlangabezane nalo msebenzi emsebenzini wakho ngaphambilini, ngakho-ke ngikweluleka ngokuqinile ukuthi uwufunde okungenani ngokulinganayo - uyasiza ezimweni eziningi futhi awusebenzi kancane (nangaphezulu!) VPR (VLOOKUP).
Nokho, ngemva kwalokho, kusala kuphela ukunamathisela umugqa wezingcezu eziwumphumela ngomugqa usebenzisa uphawu lokuhlanganisa (&):
... noma (uma unenguqulo yakamuva ye-Excel) enomsebenzi owusizo hlanganisa (TEXTJOIN), enganamathisela konke okuqukethwe kwamaseli ashiwo ngohlamvu olunikeziwe lwesihlukanisi (isikhala):
Indlela 2. Ngombuzo Wamandla
I-Power Query iyisengezo esinamandla se-Microsoft Excel esenza imisebenzi emibili eyinhloko: 1. ukulayisha idatha ku-Excel kusuka cishe kunoma yimuphi umthombo wangaphandle, kanye 2. nazo zonke izinhlobo zokuguqulwa kwamathebula alayishiwe. I-Power Query isivele yakhelwe ku-Excel 2016-2019, futhi ku-Excel 2010-2013 ifakwe njengesengezo esihlukile (ungayilanda kuwebhusayithi esemthethweni ye-Microsoft mahhala). Uma ungakaqali ukusebenzisa i-Power Query emsebenzini wakho, sekuyisikhathi sokucabanga ngakho, ngoba ukuguqulwa okufana nalokhu okuchazwe ngenhla kwenziwa lapho kalula futhi ngokwemvelo, ngokunyakaza okumbalwa nje.
Okokuqala, masilayishe uhlu lwemithombo njengemibuzo ehlukene kokuthi Umbuzo Wamandla. Ukuze wenze lokhu, etafuleni ngalinye, yenza lezi zinyathelo ezilandelayo:
- Masiguqule amatafula abe “ahlakaniphile” ngenkinobho Fometha njengetafula ithebhu Ikhaya (Ekhaya — Fometha Njengethebula) noma isinqamuleli sekhibhodi Ctrl+T. Ithebula ngalinye lizonikezwa igama ngokuzenzakalelayo Ithebula1,2,3..., okuthi, nokho, ingashintshwa uma ifunwa kuthebhu umakhi (Umklamo).
- Ngemva kokusetha iseli esebenzayo etafuleni, cindezela inkinobho Kusukela etafuleni (Kusuka etafuleni) ithebhu Idatha (Usuku) noma kuthebhu Umbuzo Wamandla (uma uyifakile njengesengezo esihlukile se-Excel 2010-2013).
- Ewindini lomhleli wombuzo elivulekayo, khetha umyalo Ikhaya - Vala futhi Ulayishe - Vala futhi Ulayishe... (Ekhaya — Vala&Layisha — Vala&Layisha ku..) bese inketho Vele udale uxhumano (Dala uxhumano kuphela). Lokhu kuzoshiya ithebula elilayishiwe kumemori futhi kulivumele ukuthi lifinyelelwe esikhathini esizayo.
Uma wenza konke ngendlela efanele, khona-ke okukhiphayo kuphaneli elungile kufanele kube izicelo ezintathu kumodi Ukuxhumana kuphela ngamagama ethu ethebula:
Manje chofoza kwesokudla embuzweni wokuqala bese ukhetha umyalo isixhumanisi (Inkomba)ukwenza ikhophi yayo ebuyekezwayo, bese wengeza ikholomu eyengeziwe kudatha ngomyalo Ukwengeza ikholomu ž – Ikholomu yangokwezifiso (Engeza Ikholomu -ž Ikholomu Ngokwezifiso). Ewindini lokufakwayo kwefomula, faka igama lekholomu entsha (isibonelo, i-Fragment2) kanye nesisho esilula kakhulu njengefomula:
=Ithebula2
... okungukuthi, ngamanye amazwi, igama lombuzo wesibili:
Ngemuva kokuchofoza OK sizobona ikholomu entsha, esitokisini ngasinye lapho kuzoba netafula elibekwe isidleke elinemisho evela etafuleni lesibili (ungabona okuqukethwe kulawa mathebula uma uchofoza ngemuva kweseli eduze kwegama. Ithebula):
Kusele ukunweba konke okuqukethwe kwalawa mathebula abekwe isidleke kusetshenziswa inkinobho enemicibisholo ekabili enhlokweni yekholomu ewumphumela nokungakhethi. Sebenzisa igama lekholomu yoqobo njengesiqalo (Sebenzisa igama lekholomu yoqobo njengesiqalo):
… futhi sithola zonke inhlanganisela yezinto ezingaba khona kumasethi amabili okuqala:
Ngaphezu kwalokho, konke kuyafana. Engeza enye ikholomu ebaliwe nefomula:
=Ithebula3
…, bese unweba amathebula abekwe esidlekeni futhi - futhi manje sesivele sinazo zonke izinketho zokuvumela amagama avela kumasethi amathathu, ngokulandelana:
Kusele ukukhetha wonke amakholomu amathathu ukusuka kwesobunxele kuye kwesokudla, ukubamba Ctrl, futhi ihlanganise okuqukethwe kwakho kuhlukaniswe izikhala kusetshenziswa umyalo Hlanganisa amakholomu (Hlanganisa Amakholomu) kusuka kuthebhu Uguquko (Guqula):
Imiphumela ewumphumela ingalayishwa ibuyiselwe eshidini ngomyalo osuvele ujwayelekile Ikhaya - Vala futhi Ulayishe - Vala futhi Ulayishe... (Ekhaya — Vala&Layisha — Vala&Layisha ku..):
Uma ngokuzayo okuthile kushintsha kumathebula ethu omthombo ngezingcezwana, kuzokwanela nje ukubuyekeza umbuzo okhiqiziwe ngokuchofoza kwesokudla etafuleni eliwumphumela bese ukhetha umyalo. Buyekeza & Londoloza (Vuselela) noma ngokucindezela isinqamuleli sekhibhodi Ctrl+Alt+F5.
- Iyini i-Power Query, i-Power Pivot, i-Power Map kanye ne-Power BI futhi kungani idinga umsebenzisi we-Excel
- Ukudala Ishadi Le-Gantt Embuzweni Wamandla
- Izindlela ezi-5 zokusebenzisa umsebenzi we-INDEX