Kubatanidza mazita maviri pasina kudzokorora

Mamiriro echinyakare: une zvinyorwa zviviri zvinoda kubatanidzwa kuita imwe. Uyezve, mumazita ekutanga panogona kunge paine ese akasarudzika zvinhu uye anoenderana (zvese pakati pezvirongwa nemukati), asi pakubuda iwe unofanirwa kuwana runyorwa pasina kudzokorora (dzokororo):

Ngatitarisei nzira dzinoverengeka dzekugadzirisa dambudziko rakajairika - kubva rekare "pahuma" kusvika kune yakaoma, asi yakanaka.

Method 1: Bvisa Duplicates

Iwe unogona kugadzirisa dambudziko nenzira yakapusa - tevedzera manyorerwo ezvinyorwa zvese zviri zviviri mune imwe uye wozoisa chishandiso kune inoguma seti. Bvisa Maduplicates kubva kune tab Data (Data - Bvisa zvakapetwa):

Ehe, nzira iyi haishande kana iyo data iri muzvinyorwa zvinyorwa ichiwanzochinja - iwe uchafanirwa kudzokorora maitiro ese mushure mekuchinja kwega kwega zvakare. 

Nzira 1a. tafura yepivot

Iyi nzira ndeye, kutaura zvazviri, kuenderera kune musoro kweiyo yapfuura. Kana mazita acho asina kunyanyokura uye huwandu hwezvinhu zviri mazviri huchizivikanwa pachine nguva (semuenzaniso, kwete kupfuura gumi), saka unogona kusanganisa matafura maviri mune rimwe chete neakananga link, wedzera koramu ine iri kurudyi uye gadzira tafura yepfupiso zvichibva pane inobuda tafura:

Sezvaunoziva, iyo pivot tafura inofuratira kudzokorora, saka pakubuda tinowana rondedzero yakasanganiswa isina kudzokorora. Koramu yekubatsira ine 1 inodiwa chete nekuti Excel inogona kuvaka pfupiso matafura ane angangoita maviri makoramu.

Kana zvinyorwa zvepakutanga zvashandurwa, iyo data nyowani ichaenda kutafura yakasanganiswa kuburikidza neakananga zvinongedzo, asi tafura yepivot ichafanirwa kuvandudzwa pamunhu (kurudyi-tinya - Gadziridza & Chengeta) Kana iwe usingade recalculation pane nhunzi, saka zviri nani kushandisa dzimwe sarudzo.

Nzira 2: Array Formula

Iwe unogona kugadzirisa dambudziko nemafomula. Muchiitiko ichi, kuverengera uye kuvandudzwa kwemhedzisiro kuchaitika otomatiki uye pakarepo, pakarepo mushure mekuchinja mune yekutanga zvinyorwa. Kuti zvive nyore uye nekupfupika, ngatipe mazita edu mazita. Ratidza 1 и Ratidza 2kushandisa Zita reManeja Tab Formula (Mafomati - Maneja Wezita - Gadzira):

Mushure mekupa zita, fomula yatinoda ichaita seizvi:

Pakutanga kuona, zvinoratidzika zvinotyisa, asi, chaizvoizvo, zvinhu zvose hazvisi zvinotyisa. Rega ndiwedzere iyi fomula pamitsetse yakati wandei ndichishandisa iyo Alt + Enter kiyi musanganiswa uye indent nenzvimbo, sezvatakaita, semuenzaniso pano:

Iyo logic pano ndeiyi inotevera:

• Formula INDEX(Rondedzero1;MATCH(0;COUNTIF($E$1:E1;List1); 0) inosarudza zvinhu zvese zvakasiyana kubva parunyorwa rwekutanga.Panongopera,inotanga kupa #N/A kukanganisa:

  

• Iyo formula INDEX(Rondedzero2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) inoburitsa zvinhu zvakasarudzika kubva parunyorwa rwechipiri nenzira imwechete.
• Nested mune imwe neimwe maviri IFERROR mabasa shandisa kuburitsa kwekutanga kweakasarudzika kubva pane rondedzero-1, uyezve kubva pane iyo runyorwa-2 imwe mushure meimwe.

Ziva kuti iyi rondedzero, kureva mushure mekunyora, inofanira kuiswa musero isiri yakajairika. pinda, asi nechidimbu che keyboard Ctrl+kusuduruka+pinda uye wozokopa (dhonza) pasi kumaseru emwana ane margin.

Mushanduro yeChirungu yeExcel, iyi fomula inoita senge:

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

Iyo yakaderera yeiyi nzira ndeyekuti array mafomula anooneka anonokesa basa nefaira kana sosi matafura aine hombe (mazana akati wandei kana kupfuura) nhamba yezvinhu. 

Nzira 3. Power Query

Kana yako sosi zvinyorwa zvine huwandu hukuru hwezvinhu, semuenzaniso, mazana akati wandei kana zviuru, saka panzvimbo yeinononoka array formula, zviri nani kushandisa yakasarudzika nzira, inova iyo Power Query yekuwedzera-mukati maturusi. Iyi yekuwedzera-mukati inovakwa muExcel 2016 nekukasira. Kana iwe uine Excel 2010 kana 2013, unogona kudhawunirodha nekuiisa zvakasiyana (yemahara).

Iyo algorithm yezviito zvinotevera:

1. Vhura imwe tabhu yeadd-on yakaiswa Simba Query (kana iwe uine Excel 2010-2013) kana ingoenda kune iyo tab Data (kana iwe uine Excel 2016).
2. Sarudza yekutanga rondedzero uye tinya bhatani Kubva Tafura/Range (Kubva kuRange/Table). Kana tikabvunzwa nezve kugadzira "smart tafura" kubva pane yedu runyorwa, tinobvuma:

   

3. Iwindo remupepeti wemubvunzo rinovhura, kwaunogona kuona data rakaremerwa uye zita remubvunzo Tafura 1 (unogona kuishandura kuti ive yako kana uchida).
4. Tinya kaviri pamusoro wetafura (izwi Ratidza 1) uye uipe zita kune chero imwe (semuenzaniso vanhu) Izvo chaizvo zvekutumidza hazvina kukosha, asi zita rakagadzirwa rinofanira kuyeukwa, nekuti. ichafanirwa kushandiswa zvakare gare gare pakuunza tafura yechipiri. Kubatanidza matafura maviri mune ramangwana kunozoshanda chete kana misoro yekoramu yakabatana.
5. Wedzera rondedzero yekudonha mukona yepamusoro kuruboshwe kuvhara uye kudhawunirodha uye sarudza Vhara woisa mukati... (Vhara & Rodha ku…):

   

6. Mubhokisi rebhokisi rinotevera (rinogona kutaridzika zvakasiyana - usavhunduke), sarudza Ingogadzira chinongedzo (Gadzira kubatana chete):

   

7. Isu tinodzokorora maitiro ese (mapoinzi 2-6) kune yechipiri runyorwa. Pakutumidza zita remusoro wechikamu, zvakakosha kushandisa zita rimwechete (Vanhu) semubvunzo wapfuura.
8. MuExcel hwindo pane tab Data kana kuti pane tab Simba Query Sarudza Tora Dhata - Sanganisa Zvikumbiro - Wedzera (Tora Dhata - Unganidza Mibvunzo - Wedzera):

   

9. Mubhokisi renhaurirano rinoonekwa, sarudza zvikumbiro zvedu kubva pane zvinodonhedza pasi:

   

10. Somugumisiro, tichawana mubvunzo mutsva, apo mazita maviri achabatanidzwa pasi peumwe neumwe. Inoramba ichibvisa zvakapetwa nebhatani Delete Row – Bvisa Maduplicates (Delete Rows - Delete Duplicates):

    

11. Mubvunzo wakapedzwa unogona kupihwa zita kudivi rerudyi resarudzo, uchichipa zita rakajeka (iri richave zita retafura yemhedzisiro muchokwadi) uye zvese zvinogona kuiswa kune pepa nemurairo. kuvhara uye kudhawunirodha (Vhara&Ropa):

    

Mune ramangwana, nechero shanduko kana kuwedzera kune yekutanga zvinyorwa, zvichave zvakakwana kungobaya-kurudyi kuti uvandudze tafura yemhedzisiro.

• Maitiro ekuunganidza akawanda matafura kubva akasiyana mafaera uchishandisa Power Query
• Kutora Zvinhu Zvakasiyana kubva pane Rondedzero
• Maitiro ekufananidza rondedzero mbiri kune imwe neimwe yemachisi uye misiyano