Haysashada tirinta tirada nuqullada la soo galiyay ee tiirka Excel aad bay faa'iido u leedahay sababo badan dartood. Midka koowaad waa in la hubiyo nuqul ka mid ah, ka dib gelitaanka halka nuqul ka mid ah loogu talagalay, laakiin waxaad u baahan tahay in la ogaado tirada dhacdooyinka. Haddii aad u baahan tahay inaad tiriso qiyamka nuqul ka mid ah tiirka Excel , akhri maqaalkan.

Ku xisaabi^(Count) qiyamka nuqul ka mid ah tiirka Excel

Baahida waxaa lagu gaari karaa iyadoo la adeegsanayo shaqada COUNTIF . Waxaad xisaabin kartaa inta jeer ee nuqul ka mid ah Excel ama sida ay u kala horreeyaan nuqullada Excel .

Tiri^(Count) inta jeer ee qiyamka nuqul ka mid ah tiirka Excel

Aynu ka soo qaadno xaalad aan u baahanahay tirada dhacdooyinka halkaas oo qiimihiisu ku soo noqnoqdo liiska la doortay ee tiirka. Haddii waxa kaliya ee aad u baahan tahay inaad ogaato ay tahay inta jeer ee ku celceliska qiima kasta, qaacidadu waxay noqonaysaa:

=COUNTIF($<column of first cell in the list of values>$<row of first cell in the list of values>:$<column of first cell in the list of values>$<row of last cell in the list of values>,<cell number of the first cell in the list of values>)

Halkee,

• <column of the first cell in the list of values> waa tiirka uu liisku ku jiro.
• < safka unugga koowaad ee liiska qiyamka> waa safka kaas oo unugga ugu horreeya ee qiyamku ka kooban yahay lagu dejiyey.
• < safka unugyada ugu dambeeya ee liiska qiyamka> waa safka kaas oo la dajiyay unugga u dambeeya ee ka kooban qiyamka.
• <lambarka unugga unugga koowaad ee liiska qiyamka> waa lambarka unugga unugga koowaad kaas oo qiimihiisu lagu dejiyay dhammaan tiirka.

Tusaale ahaan. Haddii aan haysano liis qoraallo ah oo ku jira tiirka B laga bilaabo B3 ilaa B11 , waxaanan u baahanahay tirada nuqul ka mid ah qiimaha kasta ee tiirka C, laga bilaabo unugga C3 ilaa unugga C11 . Habkani waxa uu noqonayaa:

=COUNTIF($B$3:$B$11,B3)

Habkani waxa uu u baahan yahay in la geliyo unugga C3. Hadda dhagsi meel kasta oo ka baxsan unugga C3 ka dibna dib ugu soo celi. Tani waxay kicinaysaa shaqada Buuxinta^(Fill) . U fiirso^(Notice) dhibic yar oo ku taal geeska midig ee hoose ee unugga la doortay. Guji^(Click) oo u jiid xulashada (iyo isku mar qaaciddada) hoos u dhig unugga C11 .

Xisaabi siday u kala horreeyaan dhacdooyinka nuqul ka mid ah tiirka Excel

Sida tusaalaha kore, haddii aad u baahan tahay tirinta gelitaanka nuqul ka mid ah si aad u kordhiso iyo marka gelitaanka soo noqnoqda, isku day qaacidada soo socota:

=COUNTIF($<column of first cell in the list of values>$<row of first cell in the list of values>:$<cell number of the first cell in the list of values>,<cell number of the first cell in the list of values>)

Halkee,

• <column of the first cell in the list of values> waa tiirka uu liisku ku jiro.
• < safka unugga koowaad ee liiska qiyamka> waa safka kaas oo unugga ugu horreeya ee qiyamku ka kooban yahay lagu dejiyey.
• <lambarka unugga unugga koowaad ee liiska qiyamka> waa lambarka unugga unugga koowaad kaas oo qiimihiisu lagu dejiyay dhammaan tiirka.

Tusaale ahaan. Qaadashada tusaalihii hore, qaybta gelintadu waxay ka socotaa unugga B3 ilaa unugga B11 waxaanan u baahanahay inaan tirino dhacdooyinka tiirka C, qaacidadu waxay noqon doontaa:

=COUNTIF($B$3:$B3,B3)

Geli qaaciidadan unugga C3 oo u soo jiid qaacidada hoos unugga C11 adigoo isticmaalaya shaqada Buuxinta^(Fill) .

Si ka duwan kiiskii hore, waxaad ogaan doontaa in dhacdada ugu horreysa ee gelitaanka lagu calaamadiyay 1, labaad 2, iyo wixii la mid ah.

Akhri soo socda^{(Read next)} : Sida loo isku daro oo loo kala saaro unugyada Excel^{(How to Merge and Unmerge cells in Excel)} .

Rajo inay ku caawiso!

How to count duplicate values in a column in Excel

Keeping a count of the number of dυplicate entrіes across a column in Excеl іs very usеful for mаny reasonѕ. The first is to check for duplication, then for entries where duplication is intended, but you need to know the number of occurrences. If уou need to count duplісate values in a column in Excel, read through this article.

Count duplicate values in a column in Excel

The needful can be achieved using the COUNTIF function. You could either count the frequency of the duplicates in Excel or the order of occurrence of the duplicates in Excel.

Count the frequency of duplicate values across a column in Excel

Let us suppose a situation in which we need the number of instances where a certain value repeats in a selected list across a column. If all you need to know is the frequency of repetition of each value, the formula would be:

=COUNTIF($<column of first cell in the list of values>$<row of first cell in the list of values>:$<column of first cell in the list of values>$<row of last cell in the list of values>,<cell number of the first cell in the list of values>)

Where,

• <column of first cell in the list of values> is the column in which the list is set.
• <row of first cell in the list of values> is the row in which the first cell containing the values is set.
• <row of last cell in the list of values> is the row in which the last cell containing the values is set.
• <cell number of the first cell in the list of values> is the cell number of the first cell in which the value is set across the column.

Eg. If we have a list of entries across column B from B3 to B11, and we need the number of duplicates for each value in column C, from cell C3 to cell C11. The formula would become:

=COUNTIF($B$3:$B$11,B3)

This formula needs to be inserted in cell C3. Now click anywhere outside cell C3 and then back on it. This would activate the Fill function. Notice a small dot at the right-bottom corner of the selected cell. Click on it and pull the selection (and simultaneously the formula) down to cell C11.

Calculate the order of occurrence of duplicates across a column in Excel

Just as in the above example, if you need the count of the duplicate entry to increase as and when the entry reoccurs, try the following formula:

=COUNTIF($<column of first cell in the list of values>$<row of first cell in the list of values>:$<cell number of the first cell in the list of values>,<cell number of the first cell in the list of values>)

Where,

• <column of first cell in the list of values> is the column in which the list is set.
• <row of first cell in the list of values> is the row in which the first cell containing the values is set.
• <cell number of the first cell in the list of values> is the cell number of the first cell in which the value is set across the column.

Eg. Picking up the previous example, the set of entries is from cell B3 to cell B11 and we need to count of occurrences in column C, the formula would become:

=COUNTIF($B$3:$B3,B3)

Enter this formula in cell C3 and pull the formula down to cell C11 using the Fill function.

Unlike in the previous case, you will notice that the first occurrence of an entry is marked 1, the second 2, and so on.

Read next: How to Merge and Unmerge cells in Excel.

Hope it helps!

Related posts

• Sida loo isticmaalo Percentile.Exc function gudaha Excel

• Sida loo isticmaalo shaqada NETWORKDAYS gudaha Excel

• Sida loo tiriyo lambarka Haa ama Maya ee Excel

• Excel, Word ama PowerPoint ma bilaabi karaan markii ugu dambeysay

• Sida loo isticmaalo sifada Nooca Xogta tooska ah ee Excel

• Sidee loo xisaabiyaa korodhka ama hoos u dhaca boqolkiiba ee Excel?

• Sida loo beddelo qaabka faylka caadiga ah ee lagu kaydiyo Word, Excel, PowerPoint

• Sida loo isticmaalo Find and FindB functional in Excel

• Casharka Microsoft Excel, ee Bilowga ah

• Sida looga soo saaro magacyada domain URL-yada Microsoft Excel

• Sida loo daabaco unugyada la doortay ee Excel ama Google Sheets hal bog

• Sida loo isticmaalo Muddada Dhaqaale Function gudaha Excel

• Sida loo isticmaalo shaqada DGET gudaha Excel

• Sida loogu isticmaalo shaqada MID iyo MIDB gudaha Microsoft Excel

• Sida loogu isticmaalo DEC2Bin gudaha Excel si loogu beddelo nambarka jajab tobanle ee binary

• Sida loo geliyo oo loo xisaabiyo foomamka iyo shaqooyinka Excel

• Sida loo qaybiyo hal tiir oo loo kala qaybiyo tiirar badan oo Excel ah

• Sida loogu daro Trendline xaashida shaqada ee Microsoft Excel

• Microsoft Excel waxay isku dayaysaa inay soo ceshato macluumaadkaaga

• Sida loo abuuro Bracket tartanka gudaha Windows 11/10