Maitiro ekufananidza makoramu maviri muExcel uye kubvisa zvakapetwa (simudza, colorize, fambisa)

Ichi chinyorwa chichakutora iwe maminitsi gumi kuti uverenge. Mumaminetsi mashanu anotevera, unogona kufananidza zviri nyore makoramu maviri muExcel uye woona kana paine zvakapetwa mazviri, zvibvise kana kuasimbisa muruvara. Saka, nguva yasvika!

Excel ine simba kwazvo uye inotonhorera application yekugadzira uye kugadzirisa yakawanda data. Kana iwe uine akati wandei mabhuku ebasa ane data (kana tafura imwe hombe), saka ungangoda kuenzanisa makoramu maviri, tsvaga zvakapetwa tsika, uye woita chimwe chinhu nawo, semuenzaniso, bvisa, simbisa kana kujekesa zviri mukati. Makoramu anogona kunge ari patafura imwe chete, ari padyo kana kuti asiri padhuze, anogona kunge ari pamapepa maviri akasiyana kana kunyange mumabhuku akasiyana.

Fungidzira tine makoramu maviri ane mazita evanhu - mazita mashanu pachikamu chimwe nechimwe A uye mazita matatu muchikamu B. Iwe unofanirwa kuenzanisa mazita ari mumakoramu maviri aya uye wotsvaga zvakapetwa. Sezvaunonzwisisa, iyi inhema data, inotorwa chete semuenzaniso. Mumatafura chaiwo, tiri kubata nezviuru kana kunyange makumi ezviuru zvezvinyorwa.

Mhinduro A: makoramu ese ari pashizha rimwe chete. Somuenzaniso, chikamu A uye column B.

Bhuku B: Makoramu ari pamapepa akasiyana. Somuenzaniso, chikamu A pabepa Sheet2 uye column A pabepa Sheet3.

Excel 2013, 2010 uye 2007 vane mudziyo wakavakirwa-mukati Bvisa Maduplicates (Bvisa Duplicates) asi haina simba mune ino mamiriro sezvo isingakwanise kuenzanisa data mumakoramu maviri. Uyezve, inogona chete kubvisa zvakapetwa. Iko hakuna dzimwe sarudzo dzakadai sekuvhenekera kana kushandura mavara. Uye pfungwa!

Tevere, ini ndichakuratidza nzira dzinogoneka dzekuenzanisa makoramu maviri muExcel, izvo zvinokutendera iwe kuwana uye kubvisa zvakapetwa marekodhi.

• Enzanisa makoramu maviri uye tsvaga zvakapetwa uchishandisa mafomula
  
  • Sarudzo A: makoramu ese ari pashizha rimwe chete
  • Sarudzo B: Makoramu ari pamapepa akasiyana kana mumabhuku ekushanda akasiyana
• Kugadziriswa kwezvakadzokororwa zvakawanikwa
  
  • Ratidza mitsara yakafanana chete mukoromo A
  • Chinja ruvara kana kukwirisa kwawanikwa zvakapetwa
  • Bvisa zvakapetwa kubva pachikamu chekutanga

Enzanisa makoramu maviri muExcel uye tsvaga zvinyorwa zvakapetwa uchishandisa mafomula

Sarudzo A: makoramu ese ari pashizha rimwe chete

1. Musero rekutanga risina chinhu (mumuenzaniso wedu, iyi ndiyo sero C1), tinonyora fomura inotevera:

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

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

   

   Mune fomula yedu A1 iri ndiro sero rekutanga rekoramu yekutanga yatichaenzanisa. $B1 и $B10000 aya ndiwo maadero ekutanga uye ekupedzisira maseru echikamu chechipiri, chatichaita kuenzanisa. Ziva mareferensi akakwana - mavara mavara nenhamba dzemitsara inotungamirwa nechiratidzo chedhora ($). Ndinoshandisa mareferensi chaiwo kuitira kuti kero dzeserura dzirambe dzakafanana pakukopa mafomula.

   Kana iwe uchida kuwana duplicates mu column B, shandura mareferensi kuitira kuti fomula iite seizvi:

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

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

   Pane kudaro “chete"Uye"mufananidzo» Unogona kunyora mavara ako, semuenzaniso, «Hazviwanikwi"Uye"Found", kana kusiya chete"mufananidzo' uye isa chimiro chemuchadenga pane chechipiri kukosha. Muchiitiko chekupedzisira, maseru asina kudzokorora anowanikwa acharamba asina chinhu, uye, ndinotenda, iyi inomiririra data ndiyo inonyanya kuenderana nekuongorora zvakare.

2. Zvino ngatikopai fomula yedu kumaseru ese ari mukoramu C, nzira yose kusvika kumutsara wepasi, iyo ine data mumutsara A. Kuti uite izvi, fambisa chinongedzo chembeva kukona yekurudyi yesero C1, iyo pointer inotora chimiro cheblack crosshair, sezvakaratidzwa mumufananidzo uri pazasi:Dzvanya uye bata pasi bhatani rekuruboshwe uye dhonza muganho wefuremu pasi, uchiratidza maseru ese kwaunoda kuisa fomula. Kana maseru ese anodiwa asarudzwa, sunungura bhatani rembeva:

   

Tip: Mumatafura mahombe, kukopa fomula kuchakurumidza kana ukashandisa mapfupi ekhibhodi. Simbisa sero C1 uye tigoshingairira Ctrl + C (kukopa fomula kune clipboard), wobva wadzvanya Ctrl + Shift + End (kusarudza ese asina-isina masero mumutsara C) uye pakupedzisira tinya Ctrl + V (kuisa fomula mumaseru ese akasarudzwa).

3. Hukuru, ikozvino ese akateedzera kukosha akanyorwa se "mufananidzo":

Sarudzo B: makoramu maviri ari pamapepa akasiyana (mumabhuku ebasa akasiyana)

1. Musero rekutanga rekutanga risina chinhu papepa rebasa Sheet2 (munyaya yedu iri column B) isa iyi inotevera fomula:

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

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

   pano Sheet3 ndiro zita repepa pane iyo 2nd column iripo, uye $A$1:$A$10000 ndiwo kero dzesero kubva pa1 kusvika pekupedzisira muchikamu chechipiri ichi.

2. Kopa fomula kumaseru ese ari muchikamu B (zvakafanana nesarudzo A).
3. Tinowana mhedzisiro iyi:

Kugadziriswa kwezvakadzokororwa zvakawanikwa

Zvikuru, tawana zvinyorwa mukoramu yekutanga zviripo zvakare muchikamu chechipiri. Zvino tinofanira kuita chimwe chinhu navo. Kuenda nemarekodhi ese akadhindwa ari patafura pamaoko hazvina kunyatsoshanda uye zvinotora nguva yakawandisa. Pane nzira dziri nani.

Ratidza mitsara yakafanana chete mukoromo A

Kana makoramu ako asina misoro, saka iwe unofanirwa kuwedzera iwo. Kuti uite izvi, isa chitubu panhamba inomiririra mutsara wekutanga, uye inoshanduka kuita museve mutema, sezvakaratidzwa mumufananidzo uri pazasi:

Tinya-kurudyi uye sarudza kubva pane zviri mukati menyu Kuisa (Isa):

Ipa mazita kumakoramu, semuenzaniso, "zita"Uye"Duplicate?» Wobva wavhura iyo tab Data (Data) uye tinya firita (Sefa):

Mushure mezvo tinya pamuseve wegrey padivi pe "Duplicate?« kuvhura iyo sefa menyu; bvisa zvese zviri murunyoro urwu kunze mufananidzo, uye tinya OK.

Ndizvo chete, ikozvino iwe unoona chete izvo zvinhu zvekoramu А, izvo zvakadhindwa muchikamu В. Kune maviri chete masero akadaro mutafura yedu yekudzidzira, asi, sezvaunonzwisisa, mukuita pachava nemamwe akawanda.

Kuratidza mitsara yese yekoramu zvakare А, tinya chiratidzo chesefa muchikamu В, iyo zvino inoita sefunnel ine museve muduku, uye sarudza Sarudza zvose (Sarudza zvose). Kana kuti unogona kuita zvimwe chetezvo kuburikidza neRibhoni nekudzvanya Data (Data) > Sarudza & Sefa (Ronga & Sefa) > Clear (Zvakajeka) sezvinoratidzwa mumufananidzo uri pazasi:

Chinja ruvara kana kukwirisa kwawanikwa zvakapetwa

Kana zvinyorwa "mufananidzo”hazvina kukwana nekuda kwezvinangwa zvako uye unoda kumaka duplicate masero ane rumwe ruvara rwefonti, ruvara rwekuzadza kana imwe nzira…

Mune ino kesi, sefa zvakapetwa sezvaratidzwa pamusoro, sarudza ese akasefa maseru uye tinya Ctrl + 1kuvhura dialog Fomati Masero (cell format). Semuenzaniso, ngatichinje ruvara rwekuzadza kwemaseru mumitsara ine duplicate kune yero inopenya. Zvechokwadi, iwe unogona kuchinja ruvara rwekuzadza nechishandiso Zadza (Zadza Ruvara) tab Home (Kumba) asi dialog box mukana Fomati Masero (Sero Format) pakuti unogona kugadzirisa ese mafomati sarudzo panguva imwe chete.

Iye zvino haungazopotsa chero maseru ane duplicate:

Kubvisa zvakapetwa kaviri kubva pachikamu chekutanga

Sefa tafura kuitira kuti masero chete ane duplicate values ​​aonekwe, uye sarudza iwo maseru.

Kana makoramu maviri auri kuenzanisa ari pamapepa akasiyana, ndiko kuti, mumatafura akasiyana, tinya-kurudyi rakasarudzwa uye sarudza Delete Row (Bvisa mutsara):

dzvanya OKkana Excel ichikukumbira kuti usimbise kuti iwe unoda chaizvo kudzima mutsara wese wepepa uye wobva wabvisa sefa. Sezvauri kuona, mitsetse chete ine yakasarudzika tsika inosara:

Kana makoramu maviri ari pashizha rimwe chete, pedyo kune mumwe nemumwe (padhuze) kana kuti kwete pedyo kune mumwe nemumwe (kwete pedyo), ipapo maitiro ekubvisa zvakapetwa achave akanyanya kuoma. Hatikwanise kubvisa mutsara wese neduplicate values, sezvo izvi zvichibvisa maseru kubva muchikamu chechipiri zvakare. Saka kusiya chete manyorerwo akasiyana mukoromo А, Ita izvi:

1. Sefa tafura kuratidza chete duplicate values ​​uye sarudza iwo maseru. Tinya-kurudyi pavari uye sarudza kubva pane zviri mukati menyu Bvisa zviri mukati (zvakajeka zviri mukati).
2. Chenesa firita.
3. Sarudza maseru ese muchikamu А, kutangira muchitokisi A1 nzira yese kusvika kuzasi ine data.
4. Baya Data (Data) uye tinya Ronga A kusvika Z (Ronga kubva A kusvika Z). Mubhokisi rehurukuro rinovhura, sarudza Ramba nesarudzo yazvino (Ronga mukati mesarudzo yataurwa) uye tinya bhatani Nhema (Kuronga):
5. Bvisa iyo column neformula, iwe hauchazoida, kubva zvino zvichienda mberi iwe une chete yakasarudzika maitiro.
6. Ndizvozvo, ikozvino koramu А ine chete yakasarudzika data isiri muchikamu В:

Sezvauri kuona, kubvisa zvakapetwa kubva kumakoramu maviri muExcel uchishandisa mafomula hakuna kuoma.