Skaičiavimas yra neatskiriama duomenų analizės dalis, nesvarbu, ar skaičiuojate pagrindinius duomenis organizacijos departamente ar vienetus, parduotus per ketvirtį. „Excel“ pateikia kelis būdus, kuriuos galite naudoti norėdami suskaičiuoti duomenų langelius, eilutes ar stulpelius. Šiame straipsnyje pateikiama išsami metodų santrauka, atsisiunčiama darbaknygė su interaktyviais pavyzdžiais ir saitais į susijusias temas, kad būtų galima geriau suprasti.
Pastaba: Skaičiavimo nereikėtų painioti su sumavimo. Daugiau informacijos apie reikšmių langeliuose, stulpeliuose ar eilutėse sumavimo žr. "Excel" duomenų įtraukimo ir skaičiavimo būdų sumavimas.
Atsisiųskite mūsų pavyzdžius
Galite atsisiųsti darbaknygės pavyzdį, kuriame pateikiama pavyzdžių, kaip papildyti šiame straipsnyje nurodytą informaciją. Dauguma šio straipsnio skyrių nurodys atitinkamą darbaknygės pavyzdžio darbalapį, kuriame pateikiami pavyzdžiai ir daugiau informacijos.
Atsisiųsti pavyzdžių, kaip suskaičiuoti reikšmes skaičiuoklėje
Šiame straipsnyje:
-
Skaičiavimas pagal vieną ar kelias sąlygas
-
Langelių skaičiavimas diapazone pagal vieną sąlygą ir naudojant funkciją COUNTIF
-
Langelių skaičiavimas stulpelyje pagal vieną ar kelias sąlygas ir naudojant funkciją DCOUNT
-
Langelių skaičiavimas diapazone pagal kelias sąlygas, naudojant funkciją COUNTIFS
-
Skaičiavimas, pagrįstas kriterijais, kartu naudojant funkcijas COUNT ir IF
-
Specialūs atvejais (visų langelių skaičiavimas, žodžių skaičiavimas)
Paprastas skaičiavimas
Reikšmes diapazone arba lentelėje galite skaičiuoti naudodami paprastą formulę, spustelėdami mygtuką arba naudodami darbalapio funkciją.
"Excel" taip pat gali rodyti "Excel" būsenos juosta pažymėtų langelių skaičių. Peržiūrėkite vaizdo įrašo demonstraciją, kurioje pateikta trumpa būsenos juostos naudojimo apžvalga. Daugiau informacijos žr. skyriuje Skaičiavimų ir skaičiavimų rodymas būsenos juostoje . Galite nurodyti reikšmes, rodomas būsenos juostoje, kai norite greitai peržvelkite duomenis ir neturite laiko įvesti formulių.
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.
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.
Pastaba: Parinktis Tarpinė suma veiks tik su įprastais "Excel" duomenimis, o ne su "Excel" lentelėmis, "PivotTable" lentelėmis arba "PivotChart".
Taip pat žr. šiuos straipsnius:
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.
Skaičiavimas pagal vieną ar kelias sąlygas
Galite suskaičiuoti, kiek langelių diapazone atitinka sąlygas (dar vadinamą kriterijus), kurias nurodėte naudodami kelias darbalapio funkcijas.
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.
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.
Pastaba: A7 yra laiko reikšmė, tačiau joje yra tekstas (a.m.), todėl funkcija COUNT jos nelaiko skaitinėmis reikšmėmis. Jei buvote pašalinti a.m. langelyje COUNT A7 laikys skaitinėmis reikšmėmis ir pakeis išvestį į 4.
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.
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).
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į:
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.
Pastabos:
-
Šiame pavyzdyje pateiktos formulės turi būti įvedamos kaip masyvo formulės. Jei atidarėte šią darbaknygę "Excel", skirtoje "Windows", arba "Excel", skirtoje "Mac", ir norite pakeisti formulę arba sukurti panašią formulę, paspauskite F2, tada paspauskite Ctrl + Shift + Enter, kad formulė pateiktų rezultatus, kurių tikitės. Ankstesnėse "Excel", skirtos "Mac", versijose naudokite +Shift+Enter.
-
Kad formulių pavyzdžiai veiktų, antrasis funkcijos IF argumentas turi būti skaičius.
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.
Svarbu: Šiame pavyzdyje pateiktos formulės turi būti įvedamos kaip masyvo formulės. Tai reiškia, kad paspauskite F2 , tada paspauskite Ctrl + Shift + Enter. Ankstesnėse "Excel", skirtos "Mac", versijose naudokite +Shift+Enter.
Papildomų patarimų žr. žinių bazės skyriuose:
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.
Pastaba: Norėdami interaktyviai naudotis, šiuos veiksmus galite atlikti atsisiunčiamos darbaknygės "PivotTable" lape pateiktuose duomenų pavyzdžiuose.
-
"Excel" skaičiuoklėje įveskite šiuos duomenis.
-
Pasirinkti A2:C8
-
Spustelėkite Įterpimas > PivotTable.
-
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".
-
Srityje "PivotTable" laukai atlikite šiuos veiksmus:
-
Vilkite Sport į sritį Eilutės .
-
Vilkite Ketvirtis į sritį Stulpeliai .
-
Vilkite Pardavimas į sritį Reikšmės .
-
Pakartokite c veiksmą.
Lauko pavadinimas rodomas kaip SumofSales2 tiek "PivotTable", tiek reikšmių srityje.
Šiuo metu "PivotTable" laukų sritis atrodo taip:
-
Srityje Reikšmės spustelėkite išplečiamąjį sąrašą šalia SumofSales2 ir pasirinkite Reikšmės lauko parametrai.
-
Dialogo lange Reikšmių lauko parametrai atlikite šiuos veiksmus:
-
Dalyje Sumuoti reikšmės lauką pagal pasirinkite Skaičiavimas.
-
Lauke Pasirinktinis pavadinimas modifikuokite pavadinimą į Skaičius.
-
Spustelėkite Gerai.
-
"PivotTable" rodo golfo ir teniso įrašų skaičių 3 ir 4 ketvirčiuose kartu su pardavimo skaičiais.
-
Skaičiavimas, kai jūsų duomenyse yra tuščių reikšmių
Naudodami darbalapio funkcijas, galite suskaičiuoti langelius, kuriuose yra duomenų, arba tuščius langelius.
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į.
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į.
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ų.
Pastaba: Darbalapio funkcija COUNTBLANK suteikia patogiausią būdą nustatyti tuščių langelių diapazone skaičių, bet ji neveikia labai gerai, kai dominantys langeliai yra uždaroje darbaknygėje arba kai jie nėra gretimi diapazonai. Žinių bazės straipsnis XL: Kada naudoti SUM(IF()) vietoj CountBlank() parodo, kaip tokiais atvejais naudoti SUM(IF()) masyvo formulę.
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 .
Reikšmių unikalių pasikartojimų skaičiavimas
Galite suskaičiuoti unikalias reikšmes diapazone, kartu naudodami "PivotTable", funkciją COUNTIF, SUM ir IF arba dialogo langą Išplėstinis filtras .
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.
Pastabos:
-
Jei filtruojate duomenis vietoje, reikšmės nepanaikinamos iš jūsų darbalapio – viena arba kelios eilutės gali būti paslėptos. Skirtuko Duomenys grupėje Rūšiuoti ir filtruoti spustelėkite Valyti, jei norite dar kartą peržiūrėti tas reikšmes.
-
Jei norite tik sužinoti unikalių reikšmių skaičių, panaudoję išplėstinį filtrą pažymėkite duomenis (filtruotus ar nukopijuotus duomenis) ir peržiūrėkite būsenos juostą. Būsenos juostoje esantis reikšmė Skaičius turi būti lygi unikalių reikšmių skaičiui.
Daugiau informacijos žr . Filtravimas naudojant išplėstinius kriterijus
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".
Specialūs atvejais (visų langelių skaičiavimas, žodžių skaičiavimas)
Naudodami įvairius darbalapio funkcijų derinius galite suskaičiuoti, kiek langelių ar žodžių yra diapazone.
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į:
Ž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.
Skaičiavimų ir rezultatų rodymas būsenos juostoje
Pažymėjus vieną ar daugiau langelių, informacija apie tų langelių duomenis rodoma "Excel" būsenos juostoje. Pavyzdžiui, jei pažymėti keturi darbalapio langeliai ir juose yra reikšmės 2, 3, teksto eilutė (pvz., "debesis") ir 4, būsenos juostoje vienu metu galima rodyti visas šias reikšmes: Average, Count, Numerical Count, Min, Max ir Sum. Dešiniuoju pelės mygtuku spustelėkite būsenos juostą, kad būtų rodomos arba slepiamos visos šios reikšmės. Šios reikšmės rodomos tolesnėje iliustracijoje.
Reikia daugiau pagalbos?
Visada galite paklausti „Excel“ technologijų bendruomenės specialisto arba gauti palaikymo bendruomenėse.