Štejte edinstvene številske vrednosti na podlagi meril v Excelu
V Excelovem delovnem listu lahko pride do težave, ki šteje število edinstvenih številskih vrednosti glede na določeno stanje. Na primer, kako lahko iz poročila preštejem edinstvene vrednosti količine »majice« izdelka, kot je prikazano na spodnjem posnetku zaslona? V tem članku bom prikazal nekaj formul za dosego te naloge v Excelu.
- Štejte edinstvene številske vrednosti na podlagi meril v Excelu 2019, 2016 in starejših
- Štejte edinstvene številske vrednosti na podlagi meril v Excelu 365
Štejte edinstvene številske vrednosti na podlagi meril v Excelu 2019, 2016 in starejših
V Excelu 2019 in starejših različicah lahko kombinirate funkcije SUM, FREQUENCY in IF, da ustvarite formulo za štetje edinstvenih vrednosti na podlagi meril, splošna sintaksa pa je:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: Obseg celic, ki ustreza kriterijem, ki ste jih določili;
- criteria: Pogoj, na katerem želite šteti edinstvene vrednosti;
- range: Obseg celic z edinstvenimi vrednostmi za štetje.
Spodnjo formulo uporabite v prazno celico in pritisnite Ctrl + Shift + Enter tipke, da dobite pravilen rezultat, si oglejte posnetek zaslona:
Pojasnilo formule:
=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))
- IF(A2:A12=E2,C2:C12): Ta funkcija IF vrne vrednost v stolpcu C, če je izdelek v stolpcu A "majica", rezultat je matrika, kot je ta: {FALSE; 300; 500; FALSE; 400; FALSE; 300; FALSE; FALSE; NEPRAVILNO; 350}.
- FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): Funkcija FREQUENCE se uporablja za štetje vsake številske vrednosti na seznamu nizov in vrne rezultat tako: {0; 2; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0} .
- --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Preverite, ali je vsaka vrednost v matriki večja od 0, in dobite rezultat takole: {FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}. Nato dvojni negativni znak pretvori TRUE in FALSE v 1s in 0s ter vrne matriko, kot je ta: {0; 1; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0}.
- SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): Na koncu s pomočjo funkcije SUM seštejte te vrednosti in dobite skupno število: 4.
Nasvet:
Če želite prešteti edinstvene vrednosti na podlagi več kot enega pogoja, morate formuli z znakom * dodati samo druga merila:
Štejte edinstvene številske vrednosti na podlagi meril v Excelu 365
V Excelu 365 lahko kombinacija funkcij ROWS, UNIQUE in FILTER pomaga pri štetju edinstvenih številskih vrednosti na podlagi meril, splošna sintaksa pa je:
- range: Obseg celic z edinstvenimi vrednostmi za štetje.
- criteria_range: Obseg celic, ki ustreza kriterijem, ki ste jih določili;
- criteria: Pogoj, na katerem želite šteti edinstvene vrednosti;
Kopirajte ali vnesite naslednjo formulo v celico in pritisnite Vnesite tipko za vrnitev rezultata, glej posnetek zaslona:
Pojasnilo formule:
=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))
- A2: A12 = E2: Ta izraz preveri, ali vrednost v celici E2 obstaja v območju A2: A12, in dobite ta rezultat: {FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}.
- FILTER(C2:C12,A2:A12=E2): Funkcija FREQUENCE se uporablja za štetje vsake številske vrednosti na seznamu nizov in vrne rezultat tako: {0; 2; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0} .
- UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): Tu se funkcija UNIQUE uporablja za ekstrahiranje edinstvenih vrednosti iz matrike seznamov, da dobimo ta rezultat: {300; 500; 400; 350}.
- ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): Funkcija ROWS vrne število vrstic na podlagi obsega celic ali matrike, zato je rezultat: 4.
Nasvet:
1. Če ujemajoča se vrednost ne obstaja v območju podatkov, boste dobili vrednost napake, če želite vrednost napake zamenjati z 0, uporabite naslednjo formulo:
2. Če želite šteti edinstvene vrednosti na podlagi več kot enega pogoja, morate formuli dodati še druga merila s * znakom, kot je ta:
Uporabljena relativna funkcija:
- VSEM:
- Funkcija Excel SUM vrne vsoto predloženih vrednosti.
- POGOSTOST:
- Funkcija FREQUENCY izračuna, kako pogosto se vrednosti pojavljajo v razponu vrednosti, nato pa vrne navpični niz številk.
- VRSTICE:
- Funkcija ROWS vrne število vrstic v dani referenci ali nizu.
- JEDINSTVENO:
- Funkcija UNIQUE vrne seznam edinstvenih vrednosti na seznamu ali obsegu.
- FILTER:
- Funkcija FILTER pomaga filtrirati vrsto podatkov na podlagi meril, ki jih določite.
Več člankov:
- Štejte edinstvene številske vrednosti ali datume v stolpcu
- Recimo, da imate seznam številk, ki vsebujejo nekaj dvojnikov, želite zdaj šteti število edinstvenih vrednosti ali pa se vrednosti pojavijo le enkrat na seznamu, kot je prikazano na spodnjem posnetku zaslona. V tem članku bomo govorili o nekaterih uporabnih formulah za hitro in enostavno reševanje te naloge v Excelu.
- Šteje vse tekme / podvojene podatke med dvema stolpcema
- Primerjava dveh stolpcev podatkov in štetje vseh ujemanj ali dvojnikov v teh stolpcih je lahko za večino od nas običajno opravilo. Na primer, imate dva stolpca imen, nekatera imena se pojavljajo tako v prvem kot v drugem stolpcu, zdaj želite šteti vsa ujemajoča se imena (ujemanja, ki se nahajajo kjer koli v dveh stolpcih) med dvema stolpcema, kot je prikazano spodaj, ta vadnica bo predstavil nekaj formul za dosego tega cilja v Excelu.
- Štetje celic je eno od mnogih vrednosti
- Recimo, da imam seznam izdelkov v stolpcu A, zdaj pa želim pridobiti skupno število posebnih izdelkov Apple, Grape in Lemon, ki so navedeni v območju C4: C6 iz stolpca A, kot je prikazano spodaj. Običajno v Excelu preproste funkcije COUNTIF in COUNTIFS v tem scenariju ne bodo delovale. V tem članku bom govoril o tem, kako hitro in enostavno rešiti to nalogo s kombinacijo funkcij SUMPRODUCT in COUNTIF.
Najboljša orodja za pisarniško produktivnost
Kutools za Excel - vam pomaga izstopati iz množice
Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...
Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)
- Eno sekundo za preklop med desetinami odprtih dokumentov!
- Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
- Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
- Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Edge in Firefox.
