Jinsi ya Kuhesabu Records Zenye Kurudiwa (Duplicate Values) Katika Excel Kwa Kutumia SUMPRODUCT na COUNTIF – Mwongozo wa Kitaalamu wa Data Analysis

Moja ya changamoto kubwa katika usimamizi wa data ni kugundua taarifa zilizojirudia (Duplicate Records).

Tatizo hili hujitokeza sana wakati wa:

Kuchakata matokeo ya wanafunzi
Kusafisha taarifa za wafanyakazi
Kuandaa taarifa za wateja
Kusimamia namba za simu
Kusimamia namba za usajili
Kusimamia namba za mitihani
Kuandaa taarifa za kifedha

Kwa mfano unaweza kuwa na wanafunzi 2,041 kwenye spreadsheet lakini baadhi yao wameandikishwa mara mbili kwa makosa.

Swali linakuwa:

Ni records ngapi zimejirudia?

Excel ina njia nyingi za kutatua tatizo hili, lakini moja ya formula zenye nguvu zaidi ni:

=SUMPRODUCT(($C$7:$C$2041<>"")*(COUNTIF($C$7:$C$2041,$C$7:$C$2041)>1)/COUNTIF($C$7:$C$2041,$C$7:$C$2041&""))

Kwa mtazamo wa kwanza formula hii inaweza kuonekana ngumu.

Lakini ukielewa logic yake, utaweza kuitumia kwenye maelfu ya records kwa urahisi.

Formula Hii Inafanya Nini?

Kazi kuu ya formula hii ni:

Kuhesabu idadi ya values za kipekee ambazo zimejirudia ndani ya range fulani.

Mfano:

Jina
John
Peter
John
Mary
Peter
Anna

Katika mfano huu:

John amerudiwa.

Peter amerudiwa.

Mary hajarudiwa.

Anna hajarudiwa.

Formula itatoa:

2

Kwa sababu kuna values mbili zilizojirudia.

Kuvunja Formula Sehemu Kwa Sehemu
Sehemu ya Kwanza
($C$7:$C$2041<>"")

Hii inauliza:

"Cell hii ina data?"

Mfano:

Data
John
Peter
(Blank)
Mary

Matokeo:

TRUE

TRUE

FALSE

TRUE

Blank cells zinaondolewa kwenye hesabu.

Sehemu ya Pili
COUNTIF($C$7:$C$2041,$C$7:$C$2041)>1

Hii ndiyo moyo wa formula.

Excel inaangalia kila value mara ngapi imejitokeza.

Mfano:

Data
John
Peter
John
Mary
Peter

COUNTIF itarudisha:

Data Count
John 2
Peter 2
John 2
Mary 1
Peter 2

Kisha:

>1

inauliza:

"Je value hii imetokea zaidi ya mara moja?"

Matokeo:

TRUE

TRUE

TRUE

FALSE

TRUE

Kwa Nini Tunagawa Kwa COUNTIF Tena?

Sehemu hii:

/COUNTIF($C$7:$C$2041,$C$7:$C$2041&"")

ndiyo sehemu inayozuia duplicate kuhesabiwa mara nyingi.

Mfano:

John ametokea mara 2.

Kama hatutagawa:

John angehesabiwa mara 2.

Lakini tunataka ahesabiwe mara 1 tu.

Kwa hiyo:

John = 1/2

John = 1/2

Jumla:

1/2 + 1/2 = 1

Peter = 1/2

Peter = 1/2

Jumla:

1

Hivyo kila duplicate group inakuwa value 1.

Mfano Halisi

Tuchukulie data hii:

Student ID
S001
S002
S001
S003
S004
S002

Duplicate Groups:

S001

S002

Formula itarudisha:

2

Kwa sababu kuna duplicate groups mbili.

Formula Inavyofanya Hesabu

Kwa S001:

COUNTIF = 2

Contribution:

1 ÷ 2 = 0.5

Cell ya pili ya S001:

0.5

Jumla:

1

Kwa S002:

0.5 + 0.5 = 1

Total:

2

Ndiyo jibu la mwisho.

Faida za SUMPRODUCT

SUMPRODUCT ni moja ya functions zenye nguvu sana katika Excel.

Inaweza:

✓ Kuhesabu duplicates

✓ Kuhesabu records kwa masharti mengi

✓ Kufanya weighted calculations

✓ Kufanya advanced reporting

✓ Kuchakata maelfu ya records

Formula Mbadala ya Kuhesabu Duplicate Cells

Kama unataka kujua ni cells ngapi zimerudiwa bila kujali group:

=SUMPRODUCT((COUNTIF(C7:C2041,C7:C2041)>1)*1)

Mfano:

John

John

Peter

Peter

Peter

Jibu:

5

Kwa sababu cells zote zinazohusika na duplication zimehesabiwa.

Formula Ya Kuhesabu Unique Values Pekee
=SUMPRODUCT((C7:C2041<>"")/COUNTIF(C7:C2041,C7:C2041&""))

Mfano:

John

John

Peter

Mary

Mary

Jibu:

3

Kwa sababu kuna:

John

Peter

Mary

Formula Ya Kugundua Duplicate Moja Moja

Kwenye row mpya:

=IF(COUNTIF($C$7:C7,C7)>1,"Duplicate","Unique")

Matokeo:

John → Unique

John → Duplicate

Peter → Unique

Peter → Duplicate

Matumizi Katika Mfumo wa Matokeo

Formula hii ni muhimu sana kwa:

Student IDs
Examination Numbers
Candidate Numbers
Registration Numbers
Phone Numbers
Employee IDs
Payment References

Inaweza kusaidia kugundua makosa kabla ya ku-upload data kwenye database.

Hitimisho

Formula:

=SUMPRODUCT(($C$7:$C$2041<>"")*(COUNTIF($C$7:$C$2041,$C$7:$C$2041)>1)/COUNTIF($C$7:$C$2041,$C$7:$C$2041&""))

si formula ya kawaida.

Ni formula ya kitaalamu inayotumika kuchambua data kubwa na kugundua duplicate values kwa usahihi mkubwa.

Ukielewa logic yake:

Inaondoa blank cells.
Inatafuta values zilizojirudia.
Inazuia duplicate group kuhesabiwa zaidi ya mara moja.
Inatoa idadi sahihi ya duplicate groups.

Kwa hiyo ni formula muhimu sana kwa walimu, wahasibu, data analysts na watengenezaji wa mifumo ya taarifa.

Pata Mifumo Bora ya Shule na Uchakataji wa Matokeo

Unahitaji mfumo wa:

✓ School Management System

✓ Examination Processing System

✓ Mkoa na Wilaya Results System

✓ Marks Entry System

✓ Student Management System

✓ Accounting System

Tembelea:

https://faulink.com

Faulink Technologies – Suluhisho za Kisasa za Elimu, Data Analysis na Usimamizi wa Taarifa.