Uzisusa njani iiseli eziphindiweyo kwiphepha le-Excel

Sifumana ingqokelela yedatha, kwaye kwinqanaba elithile siyaqonda ukuba ezinye ziphindwe kabini.

Kufuneka sihlalutye idatha, sisazi ukuba ukuphindaphinda ziimpazamo.

Kweli nqaku, siza kubona iindlela ezintathu zokuphelisa iiseli eziphindwe kabini.

Susa iiseli eziphindwe kabini kwi-Excel

Kwindlela nganye kwezi zichazwe ngezantsi, sisebenzisa i-spreadsheet elula engezantsi, enoluhlu lwamagama kwikholamu A.

Siqala ngokubonisa indlela yokusebenzisa umyalelo we-Excel's Susa iiDuplicate ukususa iimpinda, kwaye emva koko sibonisa indlela yokusebenzisa i-Excel's Advanced Filter ukuphumeza lo msebenzi. Okokugqibela, sibonisa indlela yokususa iimpinda usebenzisa umsebenzi Countif yeExcel .

Susa iimpinda usebenzisa i-Excel's Susa iiDuplicates umyalelo

Umyalelo Susa iimpinda ifumaneka kwiqela elithi "Izixhobo zeDatha", ngaphakathi kwethebhu Dati yeribhoni ye-Excel.

Ukususa iiseli eziphindiweyo usebenzisa lo myalelo:

• Khetha nayiphi na iseli ngaphakathi kweseti yedatha ofuna ukuyisusa kabini kuyo kwaye ucofe iqhosha Susa iimpinda.
• Uya kunikwa incoko ethi "Susa iimpinda" eboniswe ngezantsi:

• Le ncoko yababini ikuvumela ukuba ukhethe ukuba yeyiphi imihlathi kwiseti yedatha ofuna ukuyijonga yongeniso oluphindiweyo. Kumzekelo wespredishithi ongentla, sinekholamu enye kuphela yedatha (indawo ethi "Igama"). Ke ngoko sishiya indawo ethi "Igama" ekhethiweyo kwibhokisi yencoko yababini.
• Emva kokuqinisekisa ukuba iindawo ezifunekayo zikhethiwe kwibhokisi yencoko yababini, cofa OK. I-Excel iya kuthi emva koko icime imiqolo ephindwe kabini, njengoko kufuneka, kwaye ikunike umyalezo, ikwazisa ngenani leerekhodi ezisusiweyo kunye nenani leerekhodi ezizodwa eziseleyo (jonga ngezantsi).

• Ngaphezulu komyalezo kukho itafile ephuma kucimo. Njengoko kuceliwe, ikopi yeseli A11 (equlathe isenzeko sesibini segama elithi “Dan BROWN”) isusiwe.

Qaphela ukuba umyalelo we-Excel's Susa iiDuplicate usenokusetyenziswa kwiiseti zedatha ezinemihlathi emininzi. Umzekelo woku unikiwe kwiphepha lokukhupha imiqolo ephindwe kabini.

Susa iimpinda usebenzisa i-Excel's filter

Isihluzi esiPhezulu se-Excel sinenketho ekuvumela ukuba ucofe iirekhodi ezizodwa kwi-spreadsheet kwaye ukope uluhlu lweziphumo ezihluziweyo kwindawo entsha.

Oku kubonelela ngoluhlu oluqulathe ukwenzeka kokuqala kwengxelo ephindwe kabini, kodwa alunakwenzeka okunye.

Ukususa iimpinda usebenzisa isihluzo esisephambili:

• Khetha umhlathi okanye iikholamu zokucoca (uluhlu A kumzekelo wespredishithi ongentla);(Kungenjalo, ukuba ukhetha nayiphi na iseli ngaphakathi kweseti yedatha yangoku, i-Excel iya kukhetha ngokuzenzekelayo lonke uluhlu lwedatha xa usenza isihluzo esikwinqanaba eliphezulu.)
• Khetha i-Excel Advanced Filter ukhetho kwi-Data tab phezulu kwincwadi yakho yokusebenza ye-Excel(okanye kwi-Excel 2003, oku ukhetho lufumaneka kwimenyu Idatha → Isihluzo ).

• Uzakuboniswa ngebhokisi yencoko yababini ebonisa iinketho zesihluzo esiphezulu se-Excel (bona ngezantsi) Ngaphakathi kwale bhokisi yencoko yababini:

Ispredishithi esinesiphumo, esinoluhlu olutsha lwedatha kuluhlu C, luboniswe ngasentla.

Unokuqaphela ukuba ixabiso eliphindwe kabini "Dan BROWN" lisusiwe kuluhlu.

Ngoku unokucima imiqolo esekhohlo yoluhlu lwakho olutsha lwedatha (iintsika ze-AB kumzekelo we-spreadsheet) ukuze ubuyele kwifomati yoqobo yesipredishithi.

Susa iimpinda usebenzisa i-Excel's Countif function

Le ndlela iya kusebenza kuphela ukuba imixholo yeseli ingaphantsi koonobumba abangama-256 ubude, njengoko imisebenzi ye-Excel ayinakho ukuphatha imitya yokubhaliweyo emide.

Inyathelo 1: Gxininisa iimpinda

Enye indlela yokususa iimpinda kuluhlu lweeseli ze-Excel kukusebenzisa i umsebenzi Countif yeExcel .

Ukubonisa oku, siya kuphinda sisebenzise umzekelo olula we-spreadsheet, onoluhlu lwamagama kuluhlu A.

Ukufumana naziphi na iimpinda kuluhlu lwamagama, sifaka umsebenzi Countif kwikholamu B yesipredishithi (jonga ngezantsi). Lo msebenzi ubonisa inani lezenzeko zegama ngalinye ukuya kumgca wangoku.

Njengoko kubonisiwe kwibha yefomula yespreadsheet ngasentla, ifomathi yomsebenzi Ukubala kwiseli B2 yi :=COUNTIF( $A$2:$A$11, A2 )

Nceda uqaphele ukuba olu phawu lusebenzisa indibaniselwano ye iireferensi zeeseli ezipheleleyo nezizalanayo. Ngenxa yokudityaniswa kwezitayile zereferensi, xa ifomula ikotshelwa kwikholamu B, iba,

=COUNTIF( $A$2:$A$11, A2 ) =COUNTIF( $A$2:$A$11, A3 ) =COUNTIF( $A$2:$A$11, A4 ) njl

Ngoko ke, ifomula kwiseli B4 ibuyisela ixabiso 1 kwisenzeko sokuqala soluhlu lwamagama "Laura BROWN," kodwa ifomula kwiseli B7 ibuyisela ixabiso 1 kwisenzeko sesibini salo mtya wokubhaliweyo.

Inyathelo lesi-2: Cima iphindwa kwimiqolo

Ngoku sisebenzise umsebenzi we-Excel Countif Ukuqaqambisa okuphindiweyo kumhlathi A womzekelo we-spreadsheet, kufuneka sicime iirowu apho ubalo likhulu kuno-1.

Kumzekelo olula we-spreadsheet, kulula ukubona kunye nokucima umqolo omnye ophindwe kabini. Nangona kunjalo, ukuba unokuphinda-phinda ezininzi, unokuyifumana ngokukhawuleza ukusebenzisa isihluzo esizenzekelayo se-Excel ukucima yonke imiqolo ephindwe kabini ngaxeshanye. Sebenzisa isihluzo esizenzekelayo se-Excel ukuphelisa ukuphinda-phinda imiqolo

La manyathelo alandelayo abonisa indlela yokususa iimpinda ezininzi ngaxeshanye (emva kokuba ziphawulwe kusetyenziswa ifayile ye Countif):

• Khetha umhlathi oqulathe umsebenzi Countif (ikholamu B kumzekelo we-spreadsheet);
• Cofa iqhosha lokucoca ulwelo kwithebhu Dati kwi-spreadsheet ukusebenzisa i-Excel yokucoca ngokuzenzekelayo kwidatha yakho;

• Sebenzisa icebo lokucoca phezulu kumqolo B ukukhetha imiqolo engalingani no 1. Oko kukuthi, nqakraza kwicebo lokucoca kwaye, kuluhlu lwamaxabiso, ungakhethi ixabiso 1;
• Uzakushiyeka ngespreadsheet apho ukwenzeka kokuqala kwexabiso ngalinye kufihlwa. Oko kukuthi, amaxabiso aphindwe kabini kuphela aboniswayo. Ungayicima le migca ngokuyiqaqambisa, emva koko ucofe ekunene kwaye ukhethe Susa imivumbo .

• Susa isihluzo kwaye uya kuphelela kwi-spreadsheet, apho okuphindwe kabini kususiwe. Ngoku unokucima ikholamu equlethe umsebenzi Countif ukubuyela kwimo yentsusa yespredishithi.

Ercole Palmeri