Kuphatikiza mindandanda iwiri popanda kubwereza

Mkhalidwe wapamwamba: muli ndi mindandanda iwiri yomwe iyenera kuphatikizidwa kukhala imodzi. Kuphatikiza apo, pamndandanda woyamba pakhoza kukhala zinthu zonse zapadera komanso zofananira (zonse pakati pa mindandanda ndi mkati), koma pazotulutsa muyenera kupeza mndandanda wopanda zobwereza (zobwereza):

Tiyeni tiyang'ane mwamwambo njira zingapo zothetsera vuto lodziwika bwino - kuyambira lakale "pamphumi" kupita ku zovuta, koma zokongola.

Njira 1: Chotsani Zobwerezedwa

Mutha kuthana ndi vutoli m'njira yosavuta - kukopera pamanja zinthu za mindandanda yonseyi kukhala imodzi ndikuyika chidacho pazotsatira. Chotsani Zobwerezedwa kuchokera ku tabu Deta (Deta - Chotsani Zobwerezedwa):

Zoonadi, njirayi sichingagwire ntchito ngati deta yomwe ili m'mabuku oyambira nthawi zambiri imasintha - muyenera kubwereza ndondomeko yonse pambuyo pa kusintha kulikonse. 

Njira 1a. tebulo la pivot

Njira imeneyi, kwenikweni, ndi kupitiriza momveka kwa yapitayo. Ngati mindandandayo si yayikulu kwambiri ndipo kuchuluka kwazinthu zomwe zilimo zimadziwika pasadakhale (mwachitsanzo, osapitilira 10), mutha kuphatikiza matebulo awiri kukhala amodzi mwa maulalo achindunji, onjezani ndime yomwe ili kumanja ndi kumanzere. pangani tebulo lachidule kutengera zomwe zatsatira:

Monga mukudziwira, tebulo la pivot limanyalanyaza kubwerezabwereza, kotero pazotulukapo tidzapeza mndandanda wosakanizidwa popanda kubwereza. Danga lothandizira lomwe lili ndi 1 likufunika kokha chifukwa Excel imatha kupanga matebulo achidule okhala ndi magawo awiri.

Mindandanda yapachiyambi ikasinthidwa, deta yatsopano idzapita patebulo lophatikizidwa pogwiritsa ntchito maulalo achindunji, koma tebulo la pivot liyenera kusinthidwa pamanja (dinani kumanja - Sinthani & Sungani). Ngati simukusowa recalculation pa ntchentche, ndiye ndi bwino ntchito njira zina.

Njira 2: Njira Yopangira

Mutha kuthetsa vutoli ndi ma formula. Pankhaniyi, recalculation ndi kasinthidwe zotsatira zidzachitika basi ndipo nthawi yomweyo, mwamsanga pambuyo kusintha mindandanda yapachiyambi. Kuti zitheke komanso kufupikitsa, tiyeni tipereke mayina athu pamndandanda. Lembani 1 и Lembani 2ntchito Woyang'anira Dzina tsamba chilinganizo (Mafomula - Woyang'anira Dzina - Pangani):

Pambuyo potchula dzina, fomula yomwe tikufuna idzawoneka motere:

Poyang'ana koyamba, zikuwoneka zowopsa, koma, kwenikweni, zonse sizowopsa. Ndiroleni ndikulitse chilinganizochi pamizere ingapo pogwiritsa ntchito makiyi a Alt + Enter ndikulowera ndi mipata, monga tinachitira, mwachitsanzo apa:

Logic yake ndi iyi:

• Njira INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) imasankha zinthu zonse zapadera pamndandanda woyamba.Zikangotha, zimayamba kupereka #N/A cholakwika:

  

• Njira INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) imatulutsa zinthu zapadera pamndandanda wachiwiri chimodzimodzi.
• Zomwe zili m'zigawo ziwiri za IFERROR zimagwiritsa ntchito zotulutsa poyamba mwazosiyana kuchokera pamndandanda-1, ndiyeno kuchokera pamndandanda-2 wina ndi mnzake.

Zindikirani kuti iyi ndi njira yophatikizika, mwachitsanzo, mutalemba, iyenera kulowetsedwa mu cell yomwe siili wamba. Lowani, koma ndi njira yachidule ya kiyibodi Ctrl+kosangalatsa+Lowani ndiyeno koperani (koka) mpaka ku maselo amwana okhala ndi malire.

M'Chingerezi cha Excel, njira iyi ikuwoneka motere:

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

Choyipa cha njirayi ndikuti mafomula amachedwetsa ntchito ndi fayilo ngati matebulo oyambira ali ndi zinthu zambiri (mazana angapo kapena kupitilira apo). 

Njira 3. Funso la Mphamvu

Ngati magwero anu ali ndi zinthu zambiri, mwachitsanzo, mazana angapo kapena masauzande, ndiye kuti m'malo mongopanga pang'onopang'ono, ndi bwino kugwiritsa ntchito njira yosiyana kwambiri, yomwe ndi zida zowonjezera za Power Query. Zowonjezera izi zimamangidwa mu Excel 2016 mwachisawawa. Ngati muli ndi Excel 2010 kapena 2013, mutha kutsitsa ndikuyiyika padera (kwaulere).

Algorithm ya zochita ndi izi:

1. Tsegulani tabu yosiyana ya zowonjezera zomwe zaikidwa Kufunsa Mphamvu (ngati muli ndi Excel 2010-2013) kapena ingopitani ku tabu Deta (ngati muli ndi Excel 2016).
2. Sankhani mndandanda woyamba ndikudina batani Kuchokera pa Table/Range (Kuchokera pagulu/Table). Tikafunsidwa za kupanga "smart table" pamndandanda wathu, timavomereza:

   

3. Zenera la mkonzi wamafunso limatsegulidwa, pomwe mutha kuwona zomwe zidakwezedwa ndi dzina lafunso Gulu 1 (mukhoza kuzisintha kukhala zanu ngati mukufuna).
4. Dinani kawiri pamutu wa tebulo (mawu Lembani 1) ndikuchitcha dzina lina lililonse (mwachitsanzo anthu). Zomwe kwenikweni kutchula sizofunikira, koma dzina lopangidwa liyenera kukumbukiridwa, chifukwa. iyenera kugwiritsidwanso ntchito pambuyo pake potumiza tebulo lachiwiri. Kuphatikiza matebulo awiri mtsogolomu kudzagwira ntchito ngati mitu yawo ikugwirizana.
5. Wonjezerani mndandanda wotsitsa pamwamba kumanzere kutseka ndikutsitsa ndi kusankha Tsekani ndikulowetsani… (Tsekani&Kwezani ku…):

   

6. Mu bokosi lotsatira la zokambirana (litha kuwoneka mosiyana pang'ono - musachite mantha), sankhani Ingopangani kulumikizana (Pangani kulumikizana kokha):

   

7. Timabwereza ndondomeko yonse (mfundo 2-6) pamndandanda wachiwiri. Posintha dzina la mutu wagawo, ndikofunikira kugwiritsa ntchito dzina lomwelo (People) monga momwe adafunsa kale.
8. Pazenera la Excel pa tabu Deta kapena pa tabu Kufunsa Mphamvu Sankhani Pezani Zambiri - Phatikizani Zopempha - Onjezani (Pezani Zambiri - Gwirizanitsani Mafunso - Ikani):

   

9. M'bokosi la zokambirana lomwe likuwoneka, sankhani zopempha zathu kuchokera pamndandanda wotsikira pansi:

   

10. Zotsatira zake, tidzapeza funso latsopano, pomwe mindandanda iwiri idzalumikizidwa pansi pa wina ndi mnzake. Zimatsalira kuchotsa zobwereza ndi batani Chotsani Mizere - Chotsani Zobwerezedwa (Chotsani Mizere - Chotsani Zobwerezedwa):

    

11. Funso lomalizidwa litha kusinthidwanso kumanja kwa gulu lazosankha, ndikulipatsa dzina lanzeru (ili likhala dzina lazotsatira) ndipo chilichonse chikhoza kukwezedwa papepala ndi lamulo. kutseka ndikutsitsa (Tsekani&Lekani):

    

M'tsogolomu, ndi zosintha zilizonse kapena zowonjezera pamndandanda woyambirira, zidzakhala zokwanira kungodina kumanja kuti musinthe tebulo lazotsatira.

• Momwe mungasonkhanitsire matebulo angapo kuchokera kumafayilo osiyanasiyana pogwiritsa ntchito Power Query
• Kutulutsa Zinthu Zapadera Pandandanda
• Momwe mungafananizire mindandanda iwiri ndi machesi ndi zosiyana