Momwe mungafananizire mizati iwiri mu Excel ndikuchotsa zobwereza (unikani, sinthani mitundu, sunthani)

Nkhaniyi itenga mphindi 10 kuti muwerenge. M'mphindi 5 zotsatira, mutha kufananiza mizati iwiri mu Excel mosavuta ndikuwona ngati pali zobwereza, kuzichotsa kapena kuziwunikira mumitundu. Choncho, nthawi yakwana!

Excel ndi pulogalamu yamphamvu kwambiri komanso yabwino kwambiri popanga ndikukonza ma data ambiri. Ngati muli ndi mabuku angapo ogwira ntchito omwe ali ndi deta (kapena tebulo lalikulu limodzi), ndiye kuti mwina mukufuna kufananitsa mizati iwiri, pezani mfundo zobwereza, kenako chitani nawo, mwachitsanzo, kufufuta, kuwunikira kapena kuchotsa zomwe zili mkati. Zigawo zitha kukhala patebulo limodzi, kukhala moyandikana kapena kusakhala moyandikana, zitha kukhala pamasamba awiri kapenanso m'mabuku osiyanasiyana.

Tangoganizani tili ndi mizati iwiri yokhala ndi mayina a anthu - mayina asanu pagawo lililonse A ndi mayina 3 pagawo B. Muyenera kufananiza mayina omwe ali m'zaza ziwirizi ndikupeza obwereza. Monga mukumvetsetsa, iyi ndi data yopeka, yotengedwa mwachitsanzo. Mu matebulo enieni, tikuchita ndi masauzande kapena makumi masauzande a zolemba.

Yankho A: mizati yonse ili pa pepala limodzi. Mwachitsanzo, ndime A ndi column B.

Yankho B: Mizati ili pamasamba osiyanasiyana. Mwachitsanzo, ndime A pa pepala Sheet2 ndi column A pa pepala Sheet3.

Excel 2013, 2010 ndi 2007 ali ndi chida chomangidwira Chotsani Zobwerezedwa (Chotsani Zobwerezedwa) koma ilibe mphamvu panthawiyi chifukwa sichingafanane ndi deta mu 2 mizati. Komanso, imatha kuchotsa zobwerezedwa. Palibe njira zina monga kuwunikira kapena kusintha mitundu. Ndipo mfundo!

Kenako, ndikuwonetsani njira zofananira ndizambiri mu Excel, zomwe zimakupatsani mwayi wopeza ndikuchotsa zolemba zobwereza.

• Fananizani mizati iwiri ndikuyang'ana zobwereza pogwiritsa ntchito mafomu
  
  • Njira A: mizati yonse ili pa pepala limodzi
  • B: Mizati ili pamasamba osiyanasiyana kapena m'mabuku osiyanasiyana
• Kukonza zobwereza zomwe zapezeka
  
  • Onetsani mizere yobwereza yokha mugawo A
  • Sinthani mtundu kapena chowunikira chomwe chapezeka chobwereza
  • Chotsani zobwerezedwa mgawo loyamba

Fananizani mizati iwiri mu Excel ndikupeza zolemba zobwereza pogwiritsa ntchito mafomu

Njira A: mizati yonse ili pa pepala limodzi

1. Mu selo loyamba lopanda kanthu (mwachitsanzo, iyi ndi selo C1), timalemba zotsatirazi:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Mu formula yathu A1 iyi ndi selo yoyamba ya gawo loyamba lomwe tifanizire. $B1 и $B10000 awa ndi maadiresi a maselo oyambirira ndi otsiriza a gawo lachiwiri, omwe tidzafanizira nawo. Zindikirani zolozera mtheradi - zilembo zam'mizere ndi manambala amizere zimatsogozedwa ndi chizindikiro cha dollar ($). Ndimagwiritsa ntchito maumboni athunthu kuti ma adilesi amafoni azikhala chimodzimodzi pokopera ma fomula.

   Ngati mukufuna kupeza zobwereza muzambiri B, sinthani maumboni kuti fomula iwoneke motere:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   M'malo mwake "Only” ndi “Zobwereza» Mutha kulemba zolemba zanu, mwachitsanzo, «Simunapezeke” ndi “Zapezeka", kapena kusiya basi"Zobwereza' ndipo lowetsani chikhalidwe cha danga m'malo mwa mtengo wachiwiri. Pamapeto pake, ma cell omwe palibe zobwereza zomwe zapezeka adzakhalabe opanda kanthu, ndipo, ndikukhulupirira, kuyimira izi ndikosavuta kuwunikanso.

2. Tsopano tiyeni tikopere fomula yathu kumaselo onse omwe ali mugawoli C, mpaka pansi mpaka pamzere wapansi, womwe uli ndi deta yomwe ili m'ndandanda A. Kuti muchite izi, sunthani cholozera cha mbewa kumunsi kumanja kwa selo C1, cholozeracho chidzatenga mawonekedwe amtundu wakuda, monga momwe tawonetsera pachithunzichi:Dinani ndikugwira batani lakumanzere ndikukokera malire a chimango pansi, ndikuwunikira ma cell onse omwe mukufuna kuyika fomula. Maselo onse ofunikira akasankhidwa, masulani batani la mouse:

   

Tip: M'matebulo akuluakulu, kukopera fomula kumakhala kofulumira ngati mugwiritsa ntchito njira zazifupi za kiyibodi. Onetsani selo C1 Ndi kukanikiza Ctrl + C (kuti mukopere fomula pa clipboard), kenako dinani Ctrl + Shift + End (kuti musankhe ma cell onse opanda kanthu pagawo C) kenako dinani Ctrl + V (kuyika fomula m'maselo onse osankhidwa).

3. Zabwino, tsopano zikhalidwe zonse zobwereza zalembedwa kuti "Zobwereza":

Njira B: mizati iwiri ili pamasamba osiyanasiyana (m'mabuku osiyanasiyana)

1. Mu selo loyamba la gawo loyamba lopanda kanthu patsamba lantchito Sheet2 (kwa ife ndi gawo B) lowetsani fomula ili:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   apa Sheet3 ndi dzina la pepala lomwe ndime ya 2 ilipo, ndi $A$1:$A$10000 ndi ma adilesi a cell kuyambira woyamba mpaka womaliza mu gawo lachiwirili.

2. Lembani fomulayo kumaselo onse pamndandanda B (chimodzimodzi ndi njira A).
3. Timapeza zotsatirazi:

Kukonza zobwereza zomwe zapezeka

Zabwino, tapeza zolembedwa mugawo loyamba zomwe ziliponso mugawo lachiwiri. Tsopano tiyenera kuchita nawo kanthu. Kudutsa muzobwereza zonse patebulo pamanja sikuthandiza ndipo kumatenga nthawi yochulukirapo. Pali njira zabwinoko.

Onetsani mizere yobwereza yokha mugawo A

Ngati mizati yanu ilibe mitu, muyenera kuwonjezera. Kuti muchite izi, ikani cholozera pa nambala yomwe ikuyimira mzere woyamba, ndipo idzasanduka muvi wakuda, monga momwe chithunzi chili pansipa:

Dinani kumanja ndikusankha kuchokera ku menyu yankhani Kuika (Ikani):

Perekani mayina kumizati, mwachitsanzo, “dzina” ndi “Zobwereza?» Kenako tsegulani tabu Deta (Deta) ndikusindikiza fyuluta (Zosefera):

Pambuyo pake dinani pa kavi kakang'ono kotuwa pafupi ndi "Zobwereza?« kuti mutsegule zosefera; Chotsani kuchongani zinthu zonse pamndandandawu kupatula Zobwereza, ndi kukanikiza OK.

Ndizo zonse, tsopano mukuwona zinthu zokhazo zomwe zili pamndandandawo А, zomwe zikubwerezedwa muzambiri В. Pali ma cell awiri otere patebulo lathu lophunzitsira, koma, monga mukumvetsetsa, muzochita padzakhala ena ambiri.

Kuti muwonetsenso mizere yonse yazambiri А, dinani chizindikiro cha fyuluta mu ndime В, yomwe tsopano ikuwoneka ngati funnel yokhala ndi muvi wawung'ono, ndikusankha Sankhani zonse (Sankhani zonse). Kapena mutha kuchita chimodzimodzi kudzera pa Riboni podina Deta (Deta) > Sankhani & Sefa (Sankhani & Sefa) > Chotsani (Chotsani) monga zikuwonekera pa chithunzi pansipa:

Sinthani mtundu kapena chowunikira chomwe chapezeka chobwereza

Ngati zolemba "Zobwereza” sizokwanira pazolinga zanu ndipo mukufuna kuyika ma cell obwereza ndi mtundu wina wamitundu, mtundu wodzaza kapena njira ina…

Pachifukwa ichi, sefa zobwereza zomwe zasonyezedwa pamwambapa, sankhani maselo onse osasankhidwa ndikudina Ctrl + 1kuti mutsegule kukambirana Sakani Maselo (mtundu wa cell). Mwachitsanzo, tiyeni tisinthe mtundu wodzaza wa maselo m'mizere yokhala ndi zobwereza kukhala zachikasu chowala. Inde, mukhoza kusintha mtundu wodzaza ndi chida Lembani (Zodzaza Mtundu) tabu Kunyumba (Kunyumba) koma mwayi wa bokosi la zokambirana Sakani Maselo (Mawonekedwe a Cell) mwakuti mutha kukonza zosankha zonse nthawi imodzi.

Tsopano simudzaphonya ma cell aliwonse okhala ndi zobwereza:

Kuchotsa zobwereza kuchokera pamndandanda woyamba

Sakanizani tebulo kuti ma cell okhawo omwe ali ndi ziwerengero zobwereza awonetsedwe, ndikusankha maselowo.

Ngati mizati iwiri yomwe mukufanizira ili pamasamba osiyanasiyana, ndiko kuti, m'matebulo osiyanasiyana, dinani kumanja komwe mwasankha ndikusankha Chotsani Mzere (Chotsani mzere):

Press OKExcel ikakufunsani kuti mutsimikizire kuti mukufunadi kuchotsa mzere wonse wamasamba ndikuchotsa zosefera. Monga mukuwonera, mizere yokhayo yokhala ndi zinthu zapadera imatsalira:

Ngati mizati 2 ili patsamba lomwelo, pafupi wina ndi mzake (oyandikana) kapena osayandikirana (osati moyandikana), ndiye kuti njira yochotsera zobwereza idzakhala yovuta kwambiri. Sitingathe kuchotsa mzere wonse wokhala ndi zikhalidwe zofanana, chifukwa izi zichotsanso ma cell pamndandanda wachiwiri. Chifukwa chake kusiya zolemba zapadera zokha muzambiri А, chitani izi:

1. Sakanizani tebulo kuti muwonetse zobwereza zokha ndikusankha ma cell amenewo. Dinani kumanja pa iwo ndikusankha kuchokera pazosankha Chotsani zomwe zili mkati (zomveka bwino).
2. Sambani fyuluta.
3. Sankhani maselo onse muzambiri А, kuyambira pa cell A1 mpaka pansi pali deta.
4. Dinani Deta (Deta) ndikusindikiza Sanjani A mpaka Z (Sankhani kuchokera ku A mpaka Z). Mu bokosi la zokambirana lomwe limatsegula, sankhani Pitirizani ndi zomwe mwasankha (Chotsani zomwe mwasankha) ndikudina batani Black (Kusanja):
5. Chotsani ndimeyi ndi fomula, simudzayifunanso, kuyambira pano muli ndi zofunikira zokhazokha.
6. Ndi zimenezo, tsopano ndime А ili ndi deta yapadera yokha yomwe ilibe mgawo В:

Monga mukuwonera, kuchotsa zobwereza kuchokera pamizati iwiri mu Excel pogwiritsa ntchito mafomu sikovuta.