Töölehel väärtuste loendamine viisid

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 avamiseks leiate sellest artiklist põhjaliku kokkuvõtte meetoditest, interaktiivsete näidetega allalaaditavast töövihikust ja linkidest seotud teemadele, et neid paremini mõista.

Märkus.: Loendamist ei tohiks segi ajada summeerimisega. Lisateavet lahtrites, veergudes või ridades väärtuste summeerimise kohta leiate teemast Andmete lisamise ja loendamise Excel kokkuvõtted.

Näidiste allalaadimine

Saate alla laadida näidistöövihiku, mis annab näiteid selle artikli teabe täiendamiseks. Enamik selle artikli jaotisi viitab näidistöövihiku vastavale töölehele, mis sisaldab näiteid ja lisateavet.

Arvutustabelis väärtuste loendamiseks näidete

Selle artikli teemad

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

Vaadake järgmist videot ja saate teada, kuidas olekuribal kuvada loendamist.

Teie brauser ei toeta videot.

Automaatsumma kasutamine

Kasutage automaatsummat, valides lahtrivahemiku, mis sisaldab vähemalt ühte arvväärtust. Seejärel klõpsake menüü Valemid nuppu Automaatsumma ja > Loendusnumbrid.

Count Numbers (Arvude loendamine)

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.

Lehe algusse

Vahesummarea lisamine

Saate oma andmetesse lisada vahekokkuvõtte Excel. Klõpsake andmetes suvalist kohta ja seejärel klõpsake nuppu >Vahekokkuvõte.

Märkus.: Suvand Vahekokkuvõte töötab ainult tavaliste andmete Excel, mitte Excel, PivotTable-liigendtabeleid ega PivotChart-liigenddiagramme.

Alamkokkuvõtterea lisamiseks klõpsake menüü Andmed nuppu Vahekokkuvõte, Excel Andmed

Lugege ka järgmisi artikleid.

Lehe algusse

Loendi või Exceli tabeli veeru lahtrite loendamine funktsiooniga SUBTOTAL

Funktsiooni SUBTOTAL abil saate loendada väärtuste arvu Excel või lahtrivahemikus. Kui tabel või vahemik sisaldab peidetud lahtreid, saate nende peidetud lahtrite kaasamiseks või välistamiseks kasutada funktsiooni SUBTOTAL ning see on suurim erinevus funktsioonide SUM ja SUBTOTAL vahel.

Subtotal süntaks näeb välja selline:

SUBTOTAL(funktsiooni_nr,viide1,[viide2];...)

Näide SUBTOTAL

Peidetud väärtuste kaasamiseks vahemikku peaksite argumendi function_numväärtuseks 2.

Vahemiku peidetud väärtuste välistamiseks määrake argumendi function_numväärtuseks 102.

Lehe algusse

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.

Teie brauser ei toeta videot.

Lehe algusse

Vahemiku lahtrite loendamine funktsiooniga COUNT

Vahemiku arvväärtuste loendamiseks kasutage valemis funktsiooni COUNT.

Funktsiooni COUNT näide

Ülaltoodud näites on A2, A3 ja A6 ainsad lahtrid, mis sisaldavad vahemikus arvväärtusi, seega on väljund 3.

Märkus.: A7 on ajaväärtus, kuid see sisaldab teksti(a.m.), seega ei pea COUNT seda arvväärtuseks. Kui soovite eemaldada a.m. loendab COUNT arvulise väärtusena A7 ja muudab väljundi väärtuseks 4.

Lehe algusse

Vahemiku lahtrite loendamine ühe tingimuse põhjal funktsiooniga COUNTIF

Funktsiooni COUNTIF abil saate loendada, mitu korda lahtrivahemikus konkreetset väärtust kuvatakse.

COUNTIF-i näited

Lehe algusse

Veeru lahtrite loendamine ühe või mitme tingimuse põhjal funktsiooniga DCOUNT

Funktsioon DCOUNT loendab lahtrid, mis sisaldavad loendi või andmebaasi kirjete väljal (veerus) numbreid, mis vastavad teie määratud tingimustele.

Järgmises näites soovite leida kuude arvu, sh või hiljem kui märts 2016, kus oli müüdud üle 400 ühiku. Töölehe esimene tabel A1-st B7-ni sisaldab müügiandmeid.

DCOUNT-i näidisandmed

DCOUNT kasutab tingimusi, et määrata, kust väärtused tagastatakse. Tingimused sisestatakse tavaliselt töölehe enda lahtritesse ja seejärel viitate nendele lahtritele argumendis kriteeriumid. Selles näites sisaldavad lahtrid A10 ja B10 kahte tingimust: üks, mis määrab, et tagastusväärtus peab olema suurem kui 400, ja teine, mis määrab, et lõppkuu peab olema võrdne või suurem kui 31. märts 2016.

Peaksite kasutama järgmist süntaksit.

=DCOUNT(A1:B7;"Kuu lõpp";A9:B10)

DCOUNT kontrollib andmeid vahemikus A1 kuni B7, rakendab lahtrites A10 ja B10 määratud tingimused ning tagastab arvu 2, mõlemale tingimustele (read 5 ja 7) vastava ridade koguarvu.

Lehe algusse

Vahemiku lahtrite loendamine mitme tingimuste alusel funktsiooni COUNTIFS abil

Funktsioon COUNTIFS sarnaneb funktsiooniga COUNTIF ühe olulise erandiga: COUNTIFS võimaldab teil rakendada kriteeriume mitme vahemiku lahtritele ja loendab kõigi kriteeriumide täitmise kordade arvu. Funktsiooniga COUNTIFS saate kasutada kuni 127 vahemiku/kriteeriumi paari.

FUNKTSIOONI COUNTIFS süntaks on järgmine.

COUNTIFS(kriteeriumide_vahemik1; kriteeriumid1; [kriteeriumide_vahemik2; kriteeriumid2]; …)

Vaadake järgmist näidet.

COUNTIFS-i näide

Lehe algusse

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 vajutage klahvikombinatsiooni Ctrl + Shift + Enter, et valem tagastaks soovitud tulemid. Mac-arvuti Excel versioonides kasutage klahvikombinatsiooni KÄSK +Shift+Enter.

  • Kui näidisvalemid töötavad, peab funktsiooni IF teine argument olema arv.

Pesastatud FUNKTSIOONIDE COUNT ja IF näited

Lehe algusesse

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

Näide 1: SUM ja IF on valemis pesastatud

Ülaltoodud funktsioon ütleb, et kui C2:C7 sisaldab väärtusi Buchanan ja Dodsworth,peaks funktsioon SUM kuvama kirjete summa, kus tingimus on täidetud. Valem leiab buchanani ja ühe Dodsworthi jaoks antud vahemikus kolm kirjet ja kuvab arvu 4.

Näide 2

Näide 2: SUM ja IF on valemis pesastatud

Ü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 eurot, ja seejärel D4 ja D6, mille väärtused on suuremad kui 19 000 eurot, ja kuvab 4.

Näide 3

Näide 3: SUM ja IF on valemis pesastatud

Ülaltoodud funktsioon ütleb, et kui funktsioonil D2:D7 on buchanani arveid vähem kui 9000 $, peaks funktsioon SUM kuvama kirjete summa, kus tingimus on täidetud. Valem leiab, et C6 vastab tingimusele ja kuvab 1.

NB!: Selle näite valemid tuleb sisestada massiivivalemitena. See tähendab, et vajutate klahvi F2 ja seejärel klahvikombinatsiooni Ctrl+Shift+Enter. Mac-arvuti Excel versioonides kasutage klahvikombinatsiooni KÄSK +Shift+Enter.

Täiendavate näpunäidete saamiseks lugege järgmiseid teabebaasi artikleid.

Lehe algusse

PivotTable-liigendtabeli veeru või rea lahtrite loendamine

PivotTable-liigendtabel võtab teie andmed kokku ning aitab teil andmeid analüüsida ja andmetesse süvitsi minna, lastes 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.

Näide PivotTable-liigendtabelist ja väljade seosest väljade loendiga.

Vaatame müügitabeli näidisstsenaariumi, kus saate loendada, kui palju müügiväärtusi on konkreetsete kvartalite jaoks golfi- ja tenniseväljade puhul olemas.

Märkus.: Interaktiivseks kasutuskogemuseks saate need juhised käivitada allalaaditava töövihiku PivotTable-liigendtabeli lehel esitatud näidisandmete põhjal.

  1. Sisestage arvutustabelisse järgmised Excel andmed.

    PivotTable-liigendtabeli näidisandmed
  2. Valige A2:C8

  3. Klõpsake menüüs Lisa nuppu PivotTable-liigendtabel.

  4. Klõpsake dialoogiboksis PivotTable-liigendtabeli loomine nuppu Vali tabel või vahemik,seejärel käsku Uus töölehtja seejärel nuppu OK.

    Uuel lehel luuakse tühi PivotTable-liigendtabel.

  5. Tehke paanil PivotTable-liigendtabeli väljad järgmist.

    1. Lohistage spordialaalale Read.

    2. Lohistage piirkond Kvartal alale Veerud.

    3. Lohistage müük alale Väärtused.

    4. Korrake juhist c.

      Välja nimi kuvatakse nii PivotTable-liigendtabelis kui ka alas Väärtused väärtusena SumofSales2.

      Praegu näeb paan PivotTable-liigendtabeli väljad välja selline:

      PivotTable-liigendtabeli väljad
    5. Klõpsake jaotises Väärtused välja SumofSales2 kõrval asuvat ripploendit ja valige Väärtusväli Sätted.

    6. Tehke dialoogiboksis Väärtuseväli Sätted järgmist.

      1. Valige jaotises Väärtuste summeerimine jaotisesLoendus.

      2. Muutke väljal Kohandatud nimi nimeks Count (Loenda).

        Value Field Sätted dialog box
      3. Klõpsake nuppu OK.

    PivotTable-liigendtabelis kuvatakse golfi- ja tennisekirjete arv 3. ja 4. kvartalis koos müüginäitajatega.

    PivotTable

Lehe algusse

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 sisaldavaid lahtreid vahemikus.

Lahtreid loendades on mõnikord vaja tühjad lahtrid loendamisest välja arvata, sest vajalikud on ainult andmetega lahtrid. Näiteks soovite loendada müügiesindajate koguarvu (veerg D).

Näide COUNTA-st

COUNTA ignoreerib tühiväärtusi lahtrites D3, D4, D8 ja D11 ning loendab ainult veeru D väärtusi sisaldavad lahtrid. Funktsioon leiab veerus D kuus lahtrit, mis sisaldavad väärtusi ja kuvab väljundiks 6.

Lehe algusse

Kindlaid tingimusi sisaldavate mittetülevate lahtrite loendamine loendis funktsiooni DCOUNTA abil

Loendi või andmebaasi kirjeveerus määratud tingimustele vastavate mittetühjade lahtrite loendamiseks kasutage funktsiooni DCOUNTA.

Järgmises näites loendatakse funktsiooni DCOUNTA abil andmebaasi kirjete arv vahemikus A1:B7, mis vastavad kriteeriumivahemikus A9:B10 määratud tingimustele. Need tingimused on sellised, et toote ID väärtus peab olema suurem kui 2000 või sellega võrdne ja hinnangväärtus peab olema suurem kui 50 või sellega võrdne.

Näide funktsioonist DCOUNTA

DCOUNTA leiab kaks rida, mis vastavad tingimustele – read 2 ja 4 ning kuvab väljundiks väärtuse 2.

Lehe algusse

Järjestikuse vahemiku tühjade lahtrite loendamine funktsiooniga COUNTBLANK

Kasutage funktsiooni COUNTBLANK, et tagastada tühjade lahtrite arv külgnevas vahemikus (lahtrid on külgnevad, kui need kõik on ühendatud katkematus järjestuses). 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 näites on toidukaupade müügi arvutustabel. oletagem, et soovite teada saada, mitu lahtrit pole müüginäitajad mainitud.

Näide: COUNTBLANK

Märkus.: Töölehefunktsioon COUNTBLANK on kõige mugavam viis vahemikus tühjade lahtrite arvu määramiseks, kuid see ei toimi eriti hästi, kui huvilised on suletud töövihikus või kui need ei moodusta külgnevaid vahemike. Teabebaasi artikkel XL: Millal kasutada funktsiooni SUM(IF()) funktsiooni CountBlank() asemel, näitab, kuidas kasutada sel juhul massiivivalemi SUM(IF()).

Lehe algusse

Mittejärjestikuse vahemiku tühjade lahtrite loendamine funktsioonide SUM ja IF kombinatsiooniga

Kasutage funktsioonide SUM ja IF kombinatsiooni. Üldiselt saate seda teha massiivivalemis funktsiooni IF abil, et teha kindlaks, kas iga viidatud lahter sisaldab väärtust, ja seejärel summeerida valemi tagastatud FALSE-väärtuste arvu.

Vaadake paari näidet funktsiooni SUM ja IF kombinatsioonidest varasemas jaotises Loendage, kui sageli esineb mitu teksti- või arvuväärtust, kasutades selles teemas funktsioone SUM ja IF.

Lehe algusse

Väärtuste üheste esinemiste loendamine

Vahemiku kordumatute väärtuste loendamiseks saate kasutada funktsioone PivotTable-liigendtabel,FUNKTSIOON COUNTIF, SUM ja IFkoos 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üüd Andmed ja klõpsake jaotises & 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.

Täpsem filter

Järgmisel joonisel sisaldab veerg E väärtusi, mis kopeeriti veerus D vahemikust.

Teisest asukohast kopeeritud veerg

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

Lehe algusse

Üheste väärtuste arvu loendamine vahemikus, mis vastab ühele või mitmele tingimustele funktsioonide IF, SUM, FREQUENCY, MATCH ja LEN abil

Kasutage funktsioonide IF, SUM, FREQUENCY, MATCH ja LEN erinevaid kombinatsioone.

Lisateavet ja näiteid leiate artikli Duplikaatide hulgas kordumatute väärtuste loendamine jaotisest "Kordumatute väärtuste loendamine funktsioonide abil".

Lehe algusse

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. Kõigi vahemiku lahtrite loendamiseks kasutage valemit, mis korrutab tagastusväärtused funktsioonide ROWS ja COLUMNS abil. Vaadake näidet järgmisest pildist.

Näide funktsioonist ROWS ja COLUMNS vahemikus lahtrite arvu loendamiseks

Lehe algusse

Sõnade loendamine vahemikus funktsioonide SUM, IF, LEN, TRIM ja SUBSTITUTE abil

Massiivivalemis saate kasutada funktsioone SUM, IF, LEN, TRIMja SUBSTITUTE. Järgmises näites on kujutatud pesastatud valemi kasutamise tulem, et leida sõnade arv vahemikus 7 lahtrit (3, 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äide pesastatud valemist sõnade loendamiseks

Kui soovite, et ülaltoodud valem töötaks õigesti, peate selle massiivivalemiks tegema, muidu tagastab valem #VALUE! #VALUE!. Selleks klõpsake valemiga lahtrit ja seejärel vajutage valemiribal klahvikombinatsiooni Ctrl + Shift + Enter. Excel lisab valemi algusesse ja lõppu lokisulu, muutes selle massiivivalemina.

Massiivivalemite kohta leiate lisateavet artiklitest Valemite ülevaade Excel Massiivivalemi loomine.

Lehe algusse

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.

Olekuriba

Lehe algusse

Kas teil on Exceli kohta mõni spetsiifiline küsimus?

Postitage oma küsimus Exceli kogukonnafoorumisse

Aidake meil Excelit täiustada

Kas teil on soovitusi selle kohta, kuidas saakime täiustada järgmist Exceli versiooni? Sellisel juhul tutvuge nende teemadega Exceli User Voice’i veebilehel.

Kas vajate veel abi?

Täiendage Office'i kasutamise oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×