Ukuhlanganisa izinhlu ezimbili ngaphandle kwezimpinda

Isimo sakudala: unezinhlu ezimbili ezidinga ukuhlanganiswa zibe lunye. Ngaphezu kwalokho, ezinhlwini zokuqala kungaba khona kokubili okuyingqayizivele nezinto ezifanayo (kokubili phakathi kohlu nangaphakathi), kodwa ekuphumeni udinga ukuthola uhlu ngaphandle kokuphindaphinda (izimpinda):

Ngokwesiko, ake sibheke izindlela ezimbalwa zokuxazulula inkinga evamile - kusukela "ebunzini" lakudala kuya kokuyinkimbinkimbi, kodwa enhle kakhulu.

Indlela 1: Susa Izimpinda

Ungakwazi ukuxazulula inkinga ngendlela elula - kopisha ngokuzenzela izakhi zazo zombili izinhlu zibe olulodwa bese usebenzisa ithuluzi kusethi ewumphumela. Susa izimpinda kusuka kuthebhu Idatha (Idatha - Susa Izimpinda):

Yiqiniso, le ndlela ngeke isebenze uma idatha ohlwini lwemithombo ivame ukushintsha - kuzodingeka uphinde yonke inqubo ngemva koshintsho ngalunye futhi. 

Indlela 1a. ithebula le-pivot

Le ndlela, eqinisweni, iwukuqhubeka okunengqondo kwedlule. Uma izinhlu zingezinkulu kakhulu futhi inani eliphakeme lezinto ezikuzo laziwa kusengaphambili (isibonelo, akukho ngaphezu kwe-10), ungahlanganisa amatafula amabili kwelinye ngezixhumanisi eziqondile, engeza ikholomu enangakwesokudla futhi yakha ithebula lesifinyezo ngokusekelwe kuthebula eliwumphumela:

Njengoba wazi, ithebula le-pivot liziba izimpinda, ngakho-ke ekukhipheni sizothola uhlu oluhlanganisiwe olungenazimpinda. Ikholomu eyinsiza eno-1 iyadingeka kuphela ngenxa yokuthi i-Excel ingakha amathebula esifinyezo aqukethe okungenani amakholomu amabili.

Lapho izinhlu zangempela zishintshwa, idatha entsha izoya etafuleni elihlanganisiwe ngezixhumanisi eziqondile, kodwa ithebula le-pivot kuzodingeka libuyekezwe ngesandla (chofoza kwesokudla - Buyekeza & Londoloza). Uma ungadingi ukubala kabusha ku-fly, kungcono ukusebenzisa ezinye izinketho.

Indlela 2: I-Array Formula

Ungakwazi ukuxazulula inkinga ngamafomula. Kulokhu, ukubalwa kabusha nokuvuselelwa kwemiphumela kuzokwenzeka ngokuzenzakalelayo futhi ngaso leso sikhathi, ngokushesha ngemva kwezinguquko ezinhlwini zangempela. Ukuze kube lula nokuba mfushane, asinikeze uhlu lwethu amagama. Uhlu 1 и Uhlu 2usebenzisa Umphathi wegama ithebhu formula (Amafomula - Isiphathi Segama - Dala):

Ngemva kokuqamba igama, ifomula esiyidingayo izobukeka kanje:

Uma uthi nhlá, kubukeka kushaqisa, kodwa, eqinisweni, yonke into ayesabi kangako. Ake nginwebe le fomula emigqeni eminingana ngisebenzisa inhlanganisela yokhiye ethi Alt+Enter futhi ngihlehlise ngezikhala, njengoba senzile, isibonelo lapha:

I-logic lapha yilokhu okulandelayo:

• I-INDEX yefomula(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) ikhetha zonke izici ezihlukile ohlwini lokuqala. Ngokushesha nje lapho ziphela, iqala ukunikeza iphutha elithi #N/A:

  

• I-INDEX yefomula(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) ikhipha ama-elementi ayingqayizivele ohlwini lwesibili ngendlela efanayo.
• Ifakwe komunye nomunye imisebenzi emibili ye-IFERROR sebenzisa okukhiphayo kuqala kwezihlukile ezivela ohlwini-1, bese kusuka ohlwini-2 ngokulandelana.

Qaphela ukuthi lena ifomula yamalungu afanayo, okungukuthi ngemva kokuthayipha, kufanele ifakwe kuseli elingajwayelekile. Faka, kodwa ngesinqamuleli sekhibhodi Ctrl+Shift+Faka bese ukopisha (hudula) phansi kumaseli engane ngomugqa.

Enguqulweni yesiNgisi ye-Excel, le fomula ibukeka kanje:

=IFERROR(IFERROR(INDEX(Uhlu1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) ), “”) 

Uhlangothi olubi lwale ndlela ukuthi amafomula afanayo anciphisa ngokuphawulekayo umsebenzi ngefayela uma amathebula omthombo anenani elikhulu (amakhulu amaningana noma ngaphezulu) lezinto. 

Indlela 3. Umbuzo Wamandla

Uma uhlu lwakho lwemithombo lunenani elikhulu lezakhi, ngokwesibonelo, amakhulu ambalwa noma izinkulungwane, esikhundleni sefomula yohlelo olunensayo, kungcono ukusebenzisa indlela ehluke kakhulu, okungukuthi amathuluzi okungeza we-Power Query. Lesi sengezo sakhelwe ku-Excel 2016 ngokuzenzakalelayo. Uma une-Excel 2010 noma i-2013, ungayilanda futhi uyifake ngokuhlukana (mahhala).

I-algorithm yezenzo imi kanje:

1. Vula ithebhu ehlukile yesengezo esifakiwe Umbuzo Wamandla (uma une-Excel 2010-2013) noma vele uye kuthebhu Idatha (uma une-Excel 2016).
2. Khetha uhlu lokuqala bese ucindezela inkinobho Kusukela kuThebula/Ububanzi (Kusuka kuBanga/Ithebula). Uma sibuzwa mayelana nokudala “ithebula elihlakaniphile” ohlwini lwethu, siyavuma:

   

3. Iwindi lomhleli wombuzo liyavuleka, lapho ungabona khona idatha elayishiwe kanye negama lombuzo Ithebula 1 (ungayishintsha ibe eyakho uma uthanda).
4. Chofoza kabili kunhlokweni yethebula (igama Uhlu 1) bese uyiqambe kabusha kunoma iyiphi enye (isibonelo Abantu). Ukuthi ukusho ukuthini ngempela akubalulekile, kodwa igama elisunguliwe kufanele likhunjulwe, ngoba. kuyodingeka isetshenziswe futhi ngokuhamba kwesikhathi lapho ungenisa ithebula lesibili. Ukuhlanganisa amathebula amabili esikhathini esizayo kuzosebenza kuphela uma izihloko zekholomu zifana.
5. Nweba uhlu lokudonsela phansi ekhoneni eliphezulu kwesokunxele vala futhi ulande bese ukhetha Vala futhi ulayishe... (Vala&Layisha ku...):

   

6. Ebhokisini lengxoxo elilandelayo (lingase libukeke lihluke kancane – ungathuki), khetha Vele udale uxhumano (Dala uxhumano kuphela):

   

7. Siphinda yonke inqubo (amaphuzu 2-6) ohlwini lwesibili. Lapho uqamba kabusha isihloko sekholomu, kubalulekile ukusebenzisa igama elifanayo (Abantu) njengasembuzweni odlule.
8. Efasiteleni le-Excel kuthebhu Idatha noma kuthebhu Umbuzo Wamandla Khetha Thola Idatha - Hlanganisa Izicelo - Engeza (Thola Idatha - Hlanganisa Imibuzo - Faka):

   

9. Ebhokisini lengxoxo elivelayo, khetha izicelo zethu ohlwini lokudonsela phansi:

   

10. Ngenxa yalokho, sizothola umbuzo omusha, lapho izinhlu ezimbili zizoxhunywa ngaphansi komunye nomunye. Kusele ukususa izimpinda ngenkinobho Susa Imigqa - Khipha Izimpinda (Susa Imigqa — Susa Izimpinda):

    

11. Umbuzo oqediwe ungaqanjwa kabusha ngakwesokudla kwephaneli yezinketho, uyinikeze igama elihlakaniphile (leli kuzoba igama letafula lomphumela empeleni) futhi konke kungalayishwa eshidini ngomyalo. vala futhi ulande (Vala&Layisha):

    

Ngokuzayo, nanoma yiziphi izinguquko noma izengezo ezinhlwini zangempela, kuzokwanela ukuthi uchofoze kwesokudla ukuze ubuyekeze ithebula lemiphumela.

• Ungawaqoqa kanjani amatafula amaningi kumafayela ahlukene usebenzisa Umbuzo Wamandla
• Ukukhipha Izinto Eziyingqayizivele ohlwini
• Ungaqhathanisa kanjani izinhlu ezimbili komunye nomunye ukuze uthole okufanayo nokungafani