Yadda ake cire kwafin sel a cikin takardar Excel

Muna karɓar tarin bayanai, kuma a wani lokaci za mu gane cewa wasu daga cikinsu an kwafi su.

Dole ne mu bincika bayanan, sanin cewa kwafi kurakurai ne.

A cikin wannan labarin, za mu ga hanyoyi uku don kawar da kwafi.

Cire kwafin sel a cikin Excel

Ga kowane hanyoyin da aka bayyana a ƙasa, muna amfani da maƙunsar maƙunsar bayanai a ƙasa, wanda ke da jerin sunaye a shafi na A.

Da farko mun nuna yadda ake amfani da umarnin Cire Duplicates na Excel don cire kwafin, sannan mu nuna yadda ake amfani da Advanced Filter na Excel don cim ma wannan aikin. A ƙarshe, muna nuna yadda ake cire kwafi amfani da aikin Countif na Excel .

Cire kwafi ta amfani da umarnin Cire Duplicates na Excel

Umurnin Cire kwafi Ana samunsa a rukunin "Kayan Bayanai", a cikin shafin Dati na Excel ribbon.

Don cire kwafin sel ta amfani da wannan umarni:

• Zaɓi kowane tantanin halitta a cikin bayanan da kake son cire kwafi daga kuma danna maɓallin Cire kwafi.
• Za a gabatar muku da maganganun “Cire Kwafi” da aka nuna a ƙasa:

• Wannan maganganun yana ba ku damar zaɓar waɗanne ginshiƙai a cikin saitin bayanan ku da kuke son bincika shigarwar kwafi. A cikin maƙunsar misalan da ke sama, ginshiƙin bayanai ɗaya kawai muke da shi (filin “Sunan”). Don haka mun bar filin "Sunan" da aka zaɓa a cikin akwatin maganganu.
• Bayan tabbatar da cewa an zaɓi filayen da ake buƙata a cikin akwatin maganganu, danna OK. Daga nan sai Excel zai goge layuka masu kwafin, kamar yadda ake buƙata, sannan ya gabatar muku da saƙo, yana sanar da ku adadin bayanan da aka cire da adadin abubuwan da suka rage (duba ƙasa).

• Sama da saƙon akwai kuma tebur sakamakon gogewa. Kamar yadda aka nema, an cire kwafin tantanin halitta A11 (wanda ya ƙunshi abu na biyu na sunan "Dan BROWN").

Lura cewa ana iya amfani da umarnin Cire Duplicates na Excel akan ma'ajin bayanai tare da ginshiƙai da yawa. Ana bayar da misalin wannan akan shafin Cire Duplicate Layukan.

Cire kwafi ta amfani da ingantaccen tacewa na Excel

Babban Filter na Excel yana da zaɓi wanda zai baka damar tace bayanai na musamman a cikin maƙunsar rubutu da kwafi sakamakon da aka tace zuwa sabon wuri.

Wannan yana ba da jeri wanda ya ƙunshi abin da ya faru na farko na rikodin kwafi, amma ba ya ƙunshi ƙarin abubuwan da suka faru.

Don cire kwafi ta amfani da ingantaccen tacewa:

• Zaɓi ginshiƙi ko ginshiƙan don tacewa (shafi A cikin maƙunsar misalan da ke sama);(A madadin, idan kun zaɓi kowane tantanin halitta a cikin bayanan na yanzu, Excel za ta zaɓi gabaɗayan kewayon bayanai ta atomatik lokacin da kuka kunna matattarar ci gaba.)
• Zaɓi zaɓin Advanced Filter na Excel daga shafin Data a saman littafin aikin ku na Excel(ko a cikin Excel 2003, wannan Ana samun zaɓi a cikin menu Data → Tace ).

• Za a gabatar muku da akwatin maganganu da ke nuna zaɓuɓɓuka don ingantaccen tacewa na Excel (duba ƙasa) A cikin wannan akwatin maganganu:

Sakamakon maƙunsar bayanai, tare da sabon jerin bayanai a shafi na C, ana nunawa a sama.

Kuna iya lura cewa an cire kwafin ƙimar "Dan BROWN" daga jerin.

Yanzu zaku iya share ginshiƙan hagu na sabon jerin bayananku ( ginshiƙan AB a cikin maƙunsar misali) don komawa zuwa ainihin tsarin maƙunsar bayanai.

Cire kwafi ta amfani da aikin Countif na Excel

Wannan hanyar za ta yi aiki ne kawai idan abin da ke cikin tantanin halitta bai kai tsayin haruffa 256 ba, saboda ayyukan Excel ba za su iya ɗaukar dogon igiyoyin rubutu ba.

Mataki 1: Hana kwafi

Wata hanya don cire kwafi a cikin kewayon sel Excel shine amfani da aiki Countif na Excel .

Don kwatanta wannan, za mu sake yin amfani da maƙunsar misali mai sauƙi, wanda ke da jerin sunaye a shafi na A.

Don nemo kowane kwafi a cikin jerin sunayen, mun saka aikin Countif a shafi na B na maƙunsar bayanai (duba ƙasa). Wannan aikin yana nuna adadin abubuwan da suka faru na kowane suna har zuwa layi na yanzu.

Kamar yadda aka nuna a maƙunsar ƙira a sama, tsarin aikin Countif a cikin cell B2 shi ne :=COUNTIF( $A$2:$A$11, A2 )

Lura cewa wannan fasalin yana amfani da haɗin gwiwa cikakken kuma dangi nassoshi cell. Saboda wannan haɗe-haɗe da salon magana, idan aka kwafi dabarar zuwa shafi na B, ya zama.

=COUNTIF( $A$2:$A$11, A2 ) =COUNTIF( $A$2:$A$11, A3 ) =COUNTIF( $A$2:$A$11, A4 ) da dai sauransu.

Saboda haka, dabarar da ke cikin tantanin halitta B4 tana mayar da darajar 1 don farkon abin da ya faru na rubutun kirtani "Laura BROWN," amma dabarar a cikin tantanin halitta B7 ta dawo da darajar 1 don faruwa na biyu na wannan rubutun.

Mataki 2: Share kwafin layuka

Yanzu da muka yi amfani da aikin Excel Countif Don haskaka kwafi a shafi na A na maƙunsar misali, muna buƙatar share layuka waɗanda ƙidayar ta fi 1 girma.

A cikin sauƙi misali maƙunsar rubutu, yana da sauƙin gani da share jeri ɗaya na kwafin. Koyaya, idan kuna da kwafi da yawa, zaku iya samun saurin amfani da tacewa ta atomatik na Excel don share duk layuka masu kwafin lokaci guda. Yi amfani da tacewa ta atomatik na Excel don kawar da kwafin layuka

Matakan da ke gaba suna nuna yadda ake cire kwafi da yawa a lokaci ɗaya (bayan an haskaka su ta amfani da aikin Countif):

• Zaɓi shafi mai ɗauke da aikin Countif (shafi B a cikin maƙunsar misali);
• Danna maɓallin tace a cikin shafin Dati na maƙunsar bayanai don amfani da tacewa ta atomatik na Excel zuwa bayanan ku;

• Yi amfani da tacewa a saman shafi na B don zaɓar layuka waɗanda ba su kai 1. Wato, danna kan tacewa kuma, daga jerin ƙimar, cire ƙimar 1;
• Za a bar ku da maƙunsar rubutu inda aka ɓoye farkon abin da ya faru na kowace ƙima. Wato, ƙima mai kwafi kawai ake nunawa. Kuna iya share waɗannan layin ta hanyar haskaka su, sannan danna-dama kuma zaɓi share ratsi .

• Cire tacewa kuma zaku ƙare tare da maƙunsar rubutu, inda aka cire kwafin. Yanzu zaku iya share ginshiƙin da ke ɗauke da aikin Countif don komawa zuwa ainihin tsarin maƙunsar rubutu.

Ercole Palmeri