Būdai, kaip suskaičiuoti reikšmes darbalapyje

Vaizdo įrašas: Langelių skaičiavimas naudojant „Excel“ būsenos juostą

Peržiūrėkite šį vaizdo įrašą ir sužinokite, kaip peržiūrėti skaičių būsenos juostoje.

Automatinės sudėties naudojimas

Naudokite automatinę sudėtį pasirinkdami langelių diapazoną, kuriame yra bent viena skaitinė reikšmė. Tada skirtuke Formulės spustelėkite Automatinė sudėtis > Skaičiuoti skaičius.

„Excel“ pateikia skaitinių reikšmių diapazone skaičių langelyje, kuris yra šalia pasirinkto diapazono. Paprastai šis rezultatas rodomas langelyje, esančiame į dešinę nuo horizontalaus diapazono arba langelyje po vertikaliu diapazonu.

Puslapio viršus

Tarpinės sumos eilutės įtraukimas

Tarpinės sumos eilutę galite įtraukti į "Excel" duomenis. Spustelėkite bet kurioje duomenų vietoje, tada spustelėkite Duomenys > tarpinė suma.

Taip pat žr. šiuos straipsnius:

• Darbalapio duomenų struktūra (grupavimas)

• Tarpinių sumų įterpimas darbalapio duomenų sąraše

Puslapio viršus

Langelius skaičiavimas sąraše arba „Excel“ lentelės stulpelyje naudojant funkciją SUBTOTAL

Naudokite funkciją SUBTOTAL norėdami suskaičiuoti "Excel" lentelės arba langelių diapazono reikšmes. Jei lentelėje ar diapazone yra paslėptų langelių, galite naudoti SUBTOTAL, kad įtrauktumėte arba išskirtumėte tuos paslėptus langelius, ir tai didžiausias skirtumas tarp funkcijų SUM ir SUBTOTAL.

SUBTOTAL sintaksė yra tokia:

SUBTOTAL(funkcijos_nr,nuoroda1,[nuoroda2],...])

Norėdami į diapazoną įtraukti paslėptas reikšmes, turite nustatyti argumento function_numreikšmę 2.

Norėdami į diapazoną neįtraukti paslėptų reikšmių, nustatykite argumento function_numreikšmę 102.

Puslapio viršus

Vaizdo įrašas: Funkcijų COUNT, COUNTIF ir COUNTA naudojimas

Peržiūrėkite šį vaizdo įrašą ir sužinokite, kaip naudoti funkcijas COUNT, COUNTIF ir COUNTA, kai norite skaičiuoti tik tuos langelius, kurie atitinka jūsų nurodytas sąlygas.

Puslapio viršus

Langelių skaičiavimas diapazone naudojant funkciją COUNT

Formulėje naudokite funkciją COUNT, jei norite suskaičiuoti, kiek skaitinių reikšmių yra diapazone.

Anksčiau pateiktame pavyzdyje A2, A3 ir A6 yra vieninteliai langeliai, kuriuose yra skaitinių reikšmių diapazone, todėl išvestis yra 3.

Puslapio viršus

Langelių skaičiavimas diapazone pagal vieną sąlygą ir naudojant funkciją COUNTIF

Naudokite funkciją COUNTIF norėdami suskaičiuoti, kiek kartų konkreti reikšmė rodoma langelių diapazone.

Puslapio viršus

Langelių skaičiavimas stulpelyje pagal vieną ar kelias sąlygas ir naudojant funkciją DCOUNT

Funkcija DCOUNT skaičiuoja langelius, kuriuose yra skaičių sąrašo įrašų lauke (stulpelyje) arba duomenų bazėje, kurie atitinka jūsų nurodytas sąlygas.

Toliau pateiktame pavyzdyje norite sužinoti mėnesių skaičių, įskaitant arba vėlesnius nei 2016 m. kovo mėn., per kuriuos buvo parduota daugiau nei 400 vienetų. Pirmoje darbalapio lentelėje nuo A1 iki B7 yra pardavimo duomenys.

DCOUNT naudoja sąlygas, kad nustatytų, iš kur turi būti pateiktos reikšmės. Sąlygos paprastai įvedamos langeliuose pačiame darbalapyje, o jūs nurodote šiuos langelius argumente kriterijai . Šiame pavyzdyje langeliuose A10 ir B10 yra dvi sąlygos: viena, nurodanti, kad grąžinama reikšmė turi būti didesnė už 400, o kita nurodo, kad pabaigos mėnuo turi būti lygus arba didesnis nei 2016 m. kovo 31 d.

Turėtumėte naudoti šią sintaksę:

=DCOUNT(A1:B7,"Mėnesio pabaiga",A9:B10)

DCOUNT tikrina duomenis diapazone nuo A1 iki B7, taiko sąlygas, nurodytas A10 ir B10, ir pateikia 2– bendrą eilučių, kurios atitinka abi sąlygas, skaičių (5 ir 7 eilutės).

Puslapio viršus

Langelių skaičiavimas diapazone pagal kelias sąlygas, naudojant funkciją COUNTIFS

Funkcija COUNTIFS panaši į funkciją COUNTIF su viena svarbia išimtimi: COUNTIFS leidžia taikyti kriterijus langeliams keliuose diapazonuose ir skaičiuoja, kiek kartų atitinka visus kriterijus. Su COUNTIFS galite naudoti iki 127 diapazonų / kriterijų porų.

COUNTIFS sintaksė yra:

COUNTIFS(kriterijų_diapazonas1, kriterijai1, [kriterijų_diapazonas2, kriterijai2],…)

Žr. toliau pateiktą pavyzdį:

Puslapio viršus

Skaičiavimas, pagrįstas kriterijais, kartu naudojant funkcijas COUNT ir IF

Tarkime, kad jums reikia nustatyti, kiek pardavėjų pardavė tam tikrą prekę tam tikrame regione, arba norite sužinoti, kiek tam tikros vertės pardavimų atliko konkretus pardavėjas. Kartu galite naudoti funkcijas IF ir COUNT; t. y. pirmiausia naudojate funkciją IF sąlygai patikrinti, o tada, tik jei funkcijos IF rezultatas yra True, langeliams skaičiuoti naudojate funkciją COUNT.

Puslapio viršus

Skaičiavimas, kaip dažnai pasitaiko kelios tekstinės arba skaitinės reikšmės, kartu naudojant funkcijas SUM ir IF

Toliau pateiktuose pavyzdžiuose kartu naudojamos funkcijos IF ir SUM. Pirmiausia funkcija IF išbando reikšmes keliuose langeliuose ir jei bandymo rezultatas yra „True“, funkcija SUM sudeda bandymą išlaikiusias reikšmes.

1 pavyzdys

Aukščiau pateikta funkcija nurodo, ar C2:C7 yra reikšmės Petraitis ir Petraitis, tada funkcija SUM turėtų rodyti įrašų, kuriuose yra sąlyga, sumą. Formulė randa tris Petraičio įrašus ir vieną Dodsworth nurodytame diapazone ir rodo 4.

2 pavyzdys

Aukščiau pateiktoje funkcijoje nurodoma, ar D2:D7 yra reikšmių, mažesnių nei 9 000 EUR arba didesnės nei 19 000 EUR, tada sum turi būti rodoma visų įrašų, kuriuose yra sąlyga, suma. Formulė randa du įrašus D3 ir D5, kurių reikšmės mažesnės nei 9 000 EUR, tada D4 ir D6 su reikšmėmis, didesnėmis nei 19 000 EUR, ir rodo 4.

3 pavyzdys

Aukščiau pateikta funkcija nurodo, ar D2:D7 yra sąskaitų faktūrų, skirtų Petraičiui už mažiau nei 9 000 EUR, tada funkcija SUM turėtų rodyti įrašų, kurių sąlyga patenkinama, sumą. Formulė randa, kad C6 atitinka sąlygą, ir rodo 1.

Papildomų patarimų žr. žinių bazės skyriuose:

• XL: funkcijos SUM(IF()) naudojimas kaip masyvo funkcijos, o ne COUNTIF() su AND

• XL: kaip suskaičiuoti skaičiaus arba teksto pasikartojimus diapazone

Puslapio viršus

Langelių skaičiavimas "PivotTable" stulpelyje arba eilutėje

"PivotTable" apibendrina jūsų duomenis ir padeda analizuoti bei detalizuoti duomenis, kad galėtumėte pasirinkti kategorijas, pagal kurias norite peržiūrėti savo duomenis.

Galite greitai sukurti „PivotTable“ pasirinkdami langelį duomenų diapazone arba „Excel“ lentelėje, tada skirtuko Įterpimas grupėje Lentelės spustelėdami PivotTable.

Susipažinkime su pardavimo skaičiuoklės pavyzdžiu, kuriame galite suskaičiuoti, kiek yra golfo ir teniso pardavimo reikšmių konkrečiuose ketvirčiuose.

1. "Excel" skaičiuoklėje įveskite šiuos duomenis.

   

2. Pasirinkti A2:C8

3. Spustelėkite Įterpimas > PivotTable.

4. Dialogo lange "PivotTable" kūrimas spustelėkite Pasirinkti lentelę arba diapazoną, tada spustelėkite Naujas darbalapis, tada spustelėkite Gerai.

   Naujame lape sukuriama tuščia "PivotTable".

5. Srityje "PivotTable" laukai atlikite šiuos veiksmus:

   1. Vilkite Sport į sritį Eilutės .

   2. Vilkite Ketvirtis į sritį Stulpeliai .

   3. Vilkite Pardavimas į sritį Reikšmės .

   4. Pakartokite c veiksmą.

      Lauko pavadinimas rodomas kaip SumofSales2 tiek "PivotTable", tiek reikšmių srityje.

      Šiuo metu "PivotTable" laukų sritis atrodo taip:

      

   5. Srityje Reikšmės spustelėkite išplečiamąjį sąrašą šalia SumofSales2 ir pasirinkite Reikšmės lauko parametrai.

   6. Dialogo lange Reikšmių lauko parametrai atlikite šiuos veiksmus:

      1. Dalyje Sumuoti reikšmės lauką pagal pasirinkite Skaičiavimas.

      2. Lauke Pasirinktinis pavadinimas modifikuokite pavadinimą į Skaičius.

         

      3. Spustelėkite Gerai.

   "PivotTable" rodo golfo ir teniso įrašų skaičių 3 ir 4 ketvirčiuose kartu su pardavimo skaičiais.

   

Puslapio viršus

Langelių skaičiavimas diapazone naudojant funkciją COUNTA

Naudokite funkciją COUNTA norėdami suskaičiuoti tik diapazono langelius, kuriuose yra reikšmių.

Skaičiuodami langelius kartais norite nepaisyti tuščių langelių, nes jums svarbūs tik reikšmę turintys langeliai. Pavyzdžiui, norite suskaičiuoti bendrą pardavimą atlikusių pardavėjų skaičių (D stulpelis).

FUNKCIJA COUNTA nepaiso tuščių D3, D4, D8 ir D11 reikšmių ir skaičiuoja tik langelius, kuriuose yra D stulpelio reikšmės. Funkcija randa šešis langelius stulpelyje D, kuriame yra reikšmės, ir rodo 6 kaip išvestį.

Puslapio viršus

Netuščių langelių skaičiavimas sąraše su konkrečiomis sąlygomis naudojant funkciją DCOUNTA

Naudokite funkciją DCOUNTA norėdami suskaičiuoti netuščius jūsų nurodytas sąlygas atitinkančius langelius sąrašo įrašų stulpelyje arba duomenų bazėje.

Šiame pavyzdyje naudojama funkcija DCOUNTA , norint suskaičiuoti duomenų bazės įrašų, esančių diapazone A1:B7 ir atitinkančių sąlygas, nurodytas kriterijų diapazone A9:B10, skaičių. Tos sąlygos yra tokios, kad produkto ID reikšmė turi būti didesnė arba lygi 2 000, o vertinimų reikšmė turi būti didesnė arba lygi 50.

DCOUNTA randa dvi eilutes, kurios atitinka sąlygas – 2 ir 4 eilutes, ir rodo reikšmę 2 kaip išvestį.

Puslapio viršus

Tuščių langelių skaičiavimas vientisame diapazone naudojant funkciją COUNTBLANK

Naudokite funkciją COUNTBLANK , kad būtų pateiktas tuščių langelių, esančių vientisame diapazone, skaičius (langeliai yra vientisi, jei jie visi sujungti nepertraukiama seka). Jei langelyje yra formulė, kuri pateikia tuščią tekstą (""), langelis yra skaičiuojamas.

Skaičiuodami langelius galbūt norite įtraukti tuščius langelius, nes jie jums reikšmingi. Toliau pateiktame maisto prekių pardavimo skaičiuoklės pavyzdyje. Tarkime, norite sužinoti, kiek langelių neturi nurodytų pardavimų skaičių.

Puslapio viršus

Tuščių langelių skaičiavimas nevientisame diapazone naudojant funkcijų SUM ir IF derinį

Naudokite funkcijų SUM ir IF derinį. Paprastai tai galite padaryti naudodami funkciją IF masyvo formulėje, kad nustatytumėte, ar kiekviename nuorodos langelyje yra reikšmė, ir tada sumuodami reikšmių FALSE, kurias pateikė formulė, skaičių.

Žr. kelis funkcijų SUM ir IF derinių pavyzdžius ankstesniame skyriuje Skaičiavimas, kaip dažnai pasitaiko kelios teksto arba skaitinės reikšmės, šioje temoje kartu naudojant funkcijas SUM ir IF .

Puslapio viršus

Unikalių reikšmių skaičiavimas sąrašo stulpelyje naudojant išplėstinį filtrą

Dialogo lange Išplėstinis filtras raskite duomenų stulpelio unikalias reikšmes. Reikšmes galite filtruoti vietoje arba galite ištraukti jas į įklijuoti naujoje vietoje. Tuomet naudodami funkciją ROWS galite apskaičiuoti naujo diapazono elementus.

Norėdami naudoti išplėstinį filtrą, spustelėkite skirtuką Duomenys ir grupėje Rikiuoti & filtrą spustelėkite Išsamiau.

Tolesniame paveikslėlyje rodoma, kaip naudoti išplėstinį filtrą norint kopijuoti tik unikalius įrašus į naują darbalapio vietą.

Toliau pateiktame paveikslėlyje E stulpelyje yra reikšmės, kurios buvo nukopijuotos iš diapazono stulpelyje D.

Daugiau informacijos žr . Filtravimas naudojant išplėstinius kriterijus

Puslapio viršus

Unikalių reikšmių skaičiavimas diapazone, kuris atitinka vieną ar kelias sąlygas, naudojant funkcijas IF, SUM, FREQUENCY, MATCH ir LEN

Naudokite įvairius funkcijų IF, SUM, FREQUENCY, MATCH ir LEN derinius:

Daugiau informacijos ir pavyzdžių žr. straipsnio Unikalių reikšmių skaičiavimas tarp pasikartojančių skyrių "Unikalių reikšmių skaičiavimas naudojant funkcijas".

Puslapio viršus

Bendro langelių skaičiaus skaičiavimas diapazone naudojant funkcijas ROWS ir COLUMNS

Tarkime, norite nustatyti didelio darbalapio dydį, kad galėtumėte nuspręsti, ar darbaknygėje naudoti rankinį, ar automatinį skaičiavimą . Norėdami suskaičiuoti visus diapazono langelius, naudokite formulę, kuri sudaugina grąžinamas reikšmes naudodama funkcijas ROWS ir COLUMNS . Žr. šį vaizdą kaip pavyzdį:

Puslapio viršus

Žodžių skaičiavimas diapazone naudojant funkcijų SUM, IF, LEN, TRIM ir SUBSTITUTE derinį

Masyvo formulėje galite naudoti funkcijų SUM, IF, LEN, TRIM ir SUBSTITUTE derinį. Toliau pateiktame pavyzdyje parodytas rezultatas, gautas naudojant įdėtąją formulę žodžių skaičiui rasti 7 langelių diapazone (3 iš jų yra tušti). Kai kurių langelių pradžioje arba pabaigos yra tarpų – funkcijos TRIM ir SUBSTITUTE pašalina šiuos papildomus tarpus prieš skaičiavimą. Žr. toliau pateiktą pavyzdį:

Dabar, kad aukščiau pateikta formulė veiktų tinkamai, turite padaryti ją masyvo formule, priešingu atveju formulė grąžins #VALUE! klaidą. Norėdami tai padaryti, spustelėkite langelį, kuriame yra formulė, tada juostoje Formulė paspauskite Ctrl + Shift + Enter. "Excel" įtraukia riestinį skliaustą formulės pradžioje ir pabaigoje, todėl jis yra masyvo formulė.

Daugiau informacijos apie masyvo formules žr. "Excel" formulių apžvalga ir Masyvo formulės kūrimas.

Puslapio viršus