Loendamine on andmeanalüüsi lahutamatu osa, olgu siis tegemist teie ettevõtte osakonna inimeste arvuga või igas kvartalis müüdud kaupade arvuga. Excelis saab andmete lahtrite, ridade või veergude loendamiseks kasutada mitut viisi. Parima valiku tegemiseks leiate sellest artiklist põhjaliku kokkuvõtte meetoditest, allalaaditavast töövihikust, mis sisaldab interaktiivseid näiteid ja linke seotud teemadele, et neid paremini mõista.
Märkus.: Loendamist ei tohiks segi ajada summeerimisega. Lisateavet lahtrite, veergude või ridade väärtuste summeerimise kohta leiate teemast Exceli andmete liitmise ja loendamise viisid.
Näidiste allalaadimine
Saate alla laadida näidistöövihiku, mis sisaldab näiteid selles artiklis sisalduva teabe täiendamiseks. Enamik selle artikli jaotisi viitab näidistöövihiku vastavale töölehele, mis sisaldab näiteid ja lisateavet.
Selle artikli teemad
-
Tühjade väärtustega andmete loendamine
-
Vahemiku mittetühjade lahtrite loendamine funktsiooniga COUNTA
-
Kindlate tingimustega loendi mittetühjade lahtrite loendamine funktsiooni DCOUNTA abil
-
Järjestikuse vahemiku tühjade lahtrite loendamine funktsiooniga COUNTBLANK
-
Mittejärjestikuse vahemiku tühjade lahtrite loendamine funktsioonide SUM ja IF kombinatsiooniga
-
Lihtne loendamine
Vahemiku väärtusi saate loendada lihtsa valemiga, nupu klõpsamisega või töölehe funktsiooniga.
Excelis saab valitud lahtrite arvu kuvada ka Exceli olekuribal. Olekuriba kasutamise kiirjuhiseid vaadake järgmisest tutvustavast videost. Lisateabe saamiseks lugege ka jaotist Arvutuste ja hulkade kuvamine olekuribal. Olekuribal kuvatavad väärtused on kasulikud siis, kui soovite kiiret ülevaadet oma andmetest ja teil pole valemite sisestamiseks aega.
Video: Lahtrite loendamine Exceli olekuribaga
Olekuribal loendamise kohta teabe saamiseks vaadake järgmist videot.
Automaatsumma kasutamine
Automaatsumma funktsiooni abil saate valida lahtrivahemiku, mis sisaldab vähemalt ühte arvväärtust. Seejärel klõpsake menüüs Valemid nuppu Automaatsumma > Loenda arve.
Excel esitab vahemiku arvväärtuste loendamise tulemuse teie valitud vahemiku kõrval olevas lahtris. Üldjuhul kuvatakse see tulemus horisontaalse vahemiku puhul paremal asuvas lahtris ja vertikaalse vahemiku puhul all olevas lahtris.
Vahekokkuvõtterea lisamine
Exceli andmetele saate lisada vahekokkuvõtterea. Klõpsake oma andmetes suvalist kohta ja seejärel nuppu Andmed > vahekokkuvõte.
Märkus.: Vahekokkuvõtte suvand töötab ainult tavaliste Exceli andmetega, mitte Exceli tabelite, PivotTable-liigendtabelite ega PivotChart-liigenddiagrammidega.
Samuti leiate teavet järgmistest artiklitest.
Loendi või Exceli tabeli veeru lahtrite loendamine funktsiooniga SUBTOTAL
Exceli tabeli või lahtrivahemiku väärtuste loendamiseks saate kasutada funktsiooni SUBTOTAL . Kui tabel või vahemik sisaldab peidetud lahtreid, saate nende peidetud lahtrite kaasamiseks või välistamiseks kasutada funktsiooni SUBTOTAL ning see on funktsioonide SUM ja SUBTOTAL suurim erinevus.
Süntaks SUBTOTAL on järgmine:
SUBTOTAL(funktsiooni_nr,viide1,[viide2];...)
Vahemikku peidetud väärtuste kaasamiseks peaksite argumendi function_num väärtuseks määrama 2.
Vahemikus peidetud väärtuste välistamiseks määrake argumendi function_num väärtuseks 102.
Loendamine ühe või mitme tingimuse põhjal
Mitmeid töölehe funktsioone saate kasutada teie määratud tingimustele (ehk kriteeriumidele) vastavate lahtrite loendamiseks vahemikus.
Video: Funktsioonide COUNT, COUNTIF ja COUNTA kasutamine
Juhiste saamiseks funktsiooni COUNT kasutamiseks ja funktsioonide COUNTIF ning COUNTA kasutamiseks üksnes teie määratud tingimustele vastavate lahtrite loendamiseks vaadake järgmist videot.
Vahemiku lahtrite loendamine funktsiooniga COUNT
Vahemiku arvväärtuste loendamiseks kasutage valemis funktsiooni COUNT.
Ülaltoodud näites on A2, A3 ja A6 ainsad lahtrid, mis sisaldavad vahemiku arvväärtusi, seega on väljund 3.
Märkus.: A7 on kellaajaväärtus, kuid sisaldab teksti (a.m.), seega count ei pea seda arvväärtuseks. Kui te oleksite a.m. eemaldanud. loeb FUNKTSIOON COUNT arvuliseks väärtuseks A7 ja muudab väljundi väärtuseks 4.
Vahemiku lahtrite loendamine ühe tingimuse põhjal funktsiooniga COUNTIF
Funktsiooni COUNTIF abil saate loendada, mitu korda konkreetne väärtus lahtrivahemikus kuvatakse.
Veeru lahtrite loendamine ühe või mitme tingimuse põhjal funktsiooniga DCOUNT
Funktsioon DCOUNT loendab loendi või andmebaasi kirjeväljal (veerus) arve sisaldavad lahtrid, mis vastavad teie määratud tingimustele.
Järgmises näites soovite leida üle 400 müüdud ühikuga kuude arvu (sh märtsis 2016 või hiljem). Töölehe esimene tabel A1 kuni B7 sisaldab müügiandmeid.
Funktsioon DCOUNT kasutab tingimusi selleks, et teha kindlaks, kust väärtused tagastatakse. Tingimused sisestatakse tavaliselt töölehe enda lahtritesse ja argumendis kriteeriumid viitate neile lahtritele. Selles näites sisaldavad lahtrid A10 ja B10 kahte tingimust: üks, mis määrab, et tagastusväärtus peab olema suurem kui 400, ja teine tingimus, mis määrab, et lõpukuu peab olema võrdne või suurem kui 31. märtsi 2016.
Peaksite kasutama järgmist süntaksit.
=DCOUNT(A1:B7;"Kuu lõpp";A9:B10)
DCOUNT kontrollib vahemikus A1–B7 olevaid andmeid, rakendab lahtrites A10 ja B10 määratud tingimused ning tagastab väärtuse 2, mis on mõlemale tingimusele vastavate ridade koguarv (read 5 ja 7).
Vahemiku lahtrite loendamine mitme tingimuse põhjal funktsiooniga COUNTIFS
Funktsioon COUNTIFS sarnaneb funktsiooniga COUNTIF ühe olulise erandiga: COUNTIFS võimaldab teil kriteeriumid rakendada mitmes vahemikus olevatele lahtritele ja loendab kõigi kriteeriumide täitmise korrad. Funktsiooniga COUNTIFS saate kasutada kuni 127 vahemiku-/kriteeriumipaari.
Funktsiooni COUNTIFS süntaks on:
COUNTIFS(kriteeriumide_vahemik1; kriteeriumid1; [kriteeriumide_vahemik2; kriteeriumid2]; …)
Vaadake järgmist näidet.
Loendamine kriteeriumide põhjal, kasutades funktsioone COUNT ja IF koos
Oletagem, et teil on vaja teada, mitu müügiesindajat müüs teatud piirkonnas mingit kindlat toodet, või soovite teada mõne kindla müügiesindaja teatud väärtust ületavate müükide arvu. Funktsioone IF ja COUNT saate kasutada koos: esmalt tuleb funktsiooni IF abil teatud tingimust testida ja seejärel, kui funktsiooni IF tulem on Tõene, saate funktsiooni COUNT abil lahtrid loendada.
Märkused:
-
Selle näite valemid tuleb sisestada massiivivalemitena. Kui olete selle töövihiku avanud rakenduses Excel for Windows või Excel 2016 for Mac ja soovite valemit muuta või luua sarnase valemi, vajutage klahvi F2 ja seejärel klahvikombinatsiooni Ctrl+Shift+Enter, et valem tagastaks soovitud tulemid. Excel for Maci varasemates versioonides kasutage klahvikombinatsiooni
-
Näidisvalemite toimimiseks peab funktsiooni IF teine argument olema arv.
Mitme tekst- või arvväärtuse esinemissageduse loendamine funktsioonide SUM ja IF koos kasutamise abil
Järgmistes näidetes kasutatakse koos funktsioone IF ja SUM. Esmalt testib funktsioon IF mõnes lahtris asuvaid väärtusi ja seejärel, kui testi tulem on Tõene, liidab funktsioon SUM testi läbinud väärtused kokku.
Näide 1
Ülaltoodud funktsioon ütleb, et kui C2:C7 sisaldab väärtusi Buchanan ja Dodsworth, peaks funktsioon SUM kuvama nende kirjete summa, kus tingimus on täidetud. Valem leiab posti jaoks kolm kirjet ja üks väärtuse Dodsworth jaoks antud vahemikus ja kuvab tulemi 4.
Näide 2
Ülaltoodud funktsioon ütleb, et kui D2:D7 sisaldab väärtusi, mis on väiksemad kui 9000 $ või suuremad kui 19 000 $, peaks funktsioon SUM kuvama kõigi nende kirjete summa, kus tingimus on täidetud. Valem leiab kaks kirjet D3 ja D5, mille väärtused on väiksemad kui 9000 $, ja seejärel D4 ja D6 väärtustega, mis on suuremad kui 19 000 $ ja kuvab tulemi 4.
Näide 3
Ülaltoodud funktsioon ütleb, et kui D2:D7 sisaldab Posti arveid vähem kui 9000 euro eest, peaks funktsioon SUM kuvama nende kirjete summa, kus tingimus on täidetud. Valem leiab, et C6 vastab tingimusele ja kuvab väärtuse 1.
NB!: Selle näite valemid tuleb sisestada massiivivalemitena. See tähendab, et vajutate klahvi F2 ja seejärel klahvikombinatsiooni Ctrl+Shift+Enter. Excel for Maci varasemates versioonides kasutage klahvikombinatsiooni
Täiendavate näpunäidete saamiseks lugege järgmiseid teabebaasi artikleid.
Veeru või rea lahtrite loendamine PivotTable-liigendtabelis
PivotTable-liigendtabel koondab teie andmed ning aitab andmeid analüüsida ja süvitsi uurida, võimaldades teil valida kategooriad, mille andmeid soovite vaadata.
PivotTable-liigendtabeli saate kiirelt luua, kui valite andmete vahemikus või Exceli tabelis lahtri ja klõpsate seejärel vahekaardil Lisa, jaotises Tabelid nuppu PivotTable-liigendtabel.
Vaatame müügi arvutustabeli näidisstsenaariumi, kus saate loendada, mitu müügiväärtust kindlate kvartalite golfi ja tennise jaoks on.
Märkus.: Interaktiivse kasutuskogemuse saamiseks saate neid juhiseid käitada allalaaditava töövihiku PivotTable-liigendtabeli lehel esitatud näidisandmetega.
-
Sisestage Exceli arvutustabelisse järgmised andmed.
-
Valige A2:C8
-
Klõpsake menüüs Lisa nuppu PivotTable-liigendtabel.
-
Klõpsake dialoogiboksis PivotTable-liigendtabeli loomine nuppu Vali tabel või vahemik, seejärel klõpsake nuppu Uus tööleht ja seejärel nuppu OK.
Uuele lehele luuakse tühi PivotTable-liigendtabel.
-
Tehke paanil PivotTable-liigendtabeli väljad järgmist.
-
Lohistage sport alale Read .
-
Lohistage Kvartal alale Veerud .
-
Lohistage müük alale Väärtused .
-
Korrake c. juhist.
Välja nimi kuvatakse nii PivotTable-liigendtabelis kui ka alas Väärtused kujul SumofSales2 .
Nüüd näeb PivotTable-liigendtabeli väljade paan välja selline:
-
Klõpsake alas Väärtused nupu SumofSales2 kõrval olevat ripploendit ja valige Väärtusevälja sätted.
-
Tehke dialoogiboksis Väärtusevälja sätted järgmist.
-
Valige jaotises Väärtuse summeerimisalusväärtus Loenda.
-
Muutke väljal Kohandatud nimi nimeks Loendus.
-
Klõpsake nuppu OK.
-
PivotTable-liigendtabelis kuvatakse golfi ja tennise kirjete arv 3. kvartalis ja 4. kvartalis koos müüginäitajatega.
-
Tühjade väärtustega andmete loendamine
Andmeid sisaldavaid või tühje lahtreid saate loendada töölehe funktsioonidega.
Vahemiku mittetühjade lahtrite loendamine funktsiooniga COUNTA
Funktsiooni COUNTA abil saate loendada ainult väärtusi sisaldava vahemiku lahtreid.
Lahtreid loendades on mõnikord vaja tühjad lahtrid loendamisest välja arvata, sest vajalikud on ainult andmetega lahtrid. Näiteks soovite loendada müügi sooritanud müügiesindajate koguarvu (veerg D).
FUNKTSIOON COUNTA ignoreerib lahtrite D3, D4, D8 ja D11 tühje väärtusi ning loendab ainult veerus D olevaid väärtusi sisaldavad lahtrid. Funktsioon leiab veerust D kuus väärtust sisaldavat lahtrit ja kuvab väljundina väärtuse 6 .
Kindlate tingimustega loendi mittetühjade lahtrite loendamine funktsiooni DCOUNTA abil
Loendi või andmebaasi kirjeveerus määratud tingimustele vastavate mittetühjade lahtrite loendamiseks kasutage funktsiooni DCOUNTA.
Järgmises näites loendab funktsioon DCOUNTA andmebaasis vahemikus A1:B7 olevate kirjete arvu, mis vastavad kriteeriumivahemikus A9:B10 määratud tingimustele. Need tingimused on, et toote ID väärtus peab olema suurem kui 2000 või sellega võrdne ja hinnangute väärtus peab olema suurem kui 50 või sellega võrdne.
DCOUNTA leiab kaks rida, mis vastavad tingimustele – read 2 ja 4 ning kuvab väljundina väärtuse 2 .
Järjestikuse vahemiku tühjade lahtrite loendamine funktsiooniga COUNTBLANK
Funktsiooni COUNTBLANK abil saate tagastada tühjade lahtrite arvu külgnevas vahemikus (lahtrid on külgnevad, kui kõik lahtrid on ühendatud katkematu jadana). Kui lahter sisaldab valemit, mis tagastab tühja teksti (""), on see lahter loendatud.
Lahtreid loendades võite mõnikord oluliste andmete saamiseks soovida loendada ka tühje lahtreid. Järgmises ostude müügi arvutustabeli näites. oletagem, et soovite teada saada, kui palju lahtreid pole müüginäitajaid mainitud.
Märkus.: Töölehefunktsioon COUNTBLANK pakub kõige mugavamat meetodit vahemiku tühjade lahtrite arvu määramiseks, kuid see ei tööta eriti hästi, kui huvipakkuvad lahtrid asuvad suletud töövihikus või kui need ei moodusta külgnevat vahemikku. Teabebaasi artikkel XL: Millal kasutada funktsiooni CountBlank() asemel FUNKTSIOONI SUM(IF()) näitab, kuidas kasutada nendel juhtudel massiivivalemit SUM(IF()).
Mittejärjestikuse vahemiku tühjade lahtrite loendamine funktsioonide SUM ja IF kombinatsiooniga
Kasutage funktsioonide SUM ja IF kombinatsiooni. Üldiselt kasutatakse selleks massiivivalemis funktsiooni IF , et teha kindlaks, kas iga viidatud lahter sisaldab väärtust, ja seejärel summeerite valemi tagastatud väärtuste FALSE arvu.
Funktsiooni sum ja IF kombinatsioonide näited leiate varasemast jaotisest Mitme teksti- või arvväärtuse esinemissageduse loendamine, kasutades selles teemas koos funktsioone SUM ja IF .
Väärtuste üheste esinemiste loendamine
Vahemiku üheste väärtuste loendamiseks saate kasutada funktsioone PivotTable, COUNTIF, SUM ja IF koos või dialoogiboksi Täpsem filter .
Loendiveerus üheste väärtuste loendamine täpsema filtriga
Üheste väärtuste leidmiseks andmeveerust kasutage dialoogiboksi Täpsem filter. Väärtused saate kas samas kohas filtreerida või need ekstraktida ja uude asukohta kleepida. Seejärel saate funktsiooni ROWS abil loendada uues vahemikus sisalduvaid üksusi.
Täpsema filtri kasutamiseks klõpsake menüü Andmed jaotises Sortimine & filtreerimine nuppu Täpsemalt.
Järgmises näites on näha, kuidas kasutada täpsemat filtrit üksnes üheste väärtuste kopeerimiseks uude asukohta töölehel.
Järgmisel joonisel sisaldab veerg E väärtusi, mis kopeeriti veeru D vahemikust.
Märkused:
-
Andmed saate samas kohas filtreerida, väärtuseid ei kustutata teie töölehelt – üks või mitu rida võib olla peidetud. Peidetud väärtuste uuesti kuvamiseks klõpsake vahekaardi Andmed jaotises Sortimine ja filtreerimine nuppu Tühjenda.
-
Kui soovite kiirelt näha üksnes üheste väärtuste arvu, valige (filtreeritud või kopeeritud) andmed pärast täpsema filtri kasutamist ja vaadake olekuriba. Olekuribal olev hulga näitaja peaks olema võrdne üheste väärtuste arvuga.
Lisateavet leiate teemast Filtreerimine täpsemate kriteeriumide abil.
Ühele või mitmele tingimusele vastavate kordumatute väärtuste loendamine funktsioonide IF, SUM, FREQUENCY, MATCH ja LEN abil
Kasutage funktsioonide IF, SUM, FREQUENCY, MATCH ja LEN erinevaid kombinatsioone.
Lisateavet ja näiteid leiate artikli Üheste väärtuste loendamine duplikaatide hulgas jaotisest "Üheste väärtuste loendamine funktsioonide abil".
Erijuhud (kõikide lahtrite loendamine, sõnade loendamine)
Vahemikus olevaid lahtreid või sõnu saate loendada töölehe funktsioonide erinevate kombinatsioonidega.
Vahemiku lahtrite koguarvu loendamine funktsioonidega ROWS ja COLUMNS
Oletame, et soovite kindlaks teha suure töölehe suurust, et otsustada, kas kasutada töövihikus käsitsi või automaatselt arvutamist. Vahemiku kõigi lahtrite loendamiseks kasutage valemit, mis korrutab tagastatavad väärtused funktsioonideGA ROWS ja COLUMNS . Järgmises näites on kujutatud järgmist pilti.
Vahemikus olevate sõnade loendamine funktsioonide SUM, IF, LEN, TRIM ja SUBSTITUTE kombinatsiooni abil
Saate massiivivalemis kasutada funktsioonide SUM, IF, LEN, TRIM ja SUBSTITUTE kombinatsiooni. Järgmises näites on näidatud pesastatud valemi abil sõnade arvu leidmine vahemikus 7 (millest 3 on tühjad). Mõned neist lahtritest sisaldavad eel- või järeltühikuid – funktsioonid TRIM ja SUBSTITUTE eemaldavad enne arvutamist kõik liigsed tühikud. Vaadake järgmist näidet.
Nüüd, et ülaltoodud valem töötaks õigesti, peate selle muutma massiivivalemiks, muidu tagastab valem #VALUE! #VALUE!. Selleks klõpsake valemit sisaldavat lahtrit ja seejärel vajutage ribal Valem klahvikombinatsiooni Ctrl + Shift + Enter. Excel lisab valemi algusse ja lõppu looksulu, muutes selle massiivivalemiks.
Massiivivalemite kohta leiate lisateavet artiklitest Exceli valemite ülevaade ja Massiivivalemi loomine.
Arvutuste ja hulkade kuvamine olekuribal
Kui valitud on üks või mitu lahtrit, kuvatakse teave nende lahtrite kohta Exceli olekuribal. Kui teie töölehel on valitud näiteks neli lahtrit ning need sisaldavad väärtusi 2, 3, tekstistring (näiteks „pilv”) ja 4, saab olekuribal korraga kuvada kõiki järgmiseid väärtusi: keskmine, hulk, arv, min, max ja summa. Nendest väärtustest mõne või nende kõikide kuvamiseks või peitmiseks paremklõpsake olekuriba. Need väärtused on kuvatud järgmises näites.
Kas vajate rohkem abi?
Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.
