Pogojno štetje in seštevanje

Pogojno štetje in seštevanje v modulu Calc

To je pregled različnih načinov za štetje in seštevanje vsebine celic glede na pogoje, odvisne od rezultata določenega preizkusa.

COUNT

Funkcija COUNT prešteje celice, ki vsebujejo številke in prezre vse druge. Tako so npr. celice z besedilom prezrte.

COUNTA

Funkcija COUNTA prešteje celice, ki vsebujejo karkoli (besedilo, številke, napake, logične vrednosti, formule). Prezre prazne celice.

COUNTBLANK

Funkcija COUNTBLANK prešteje prazne celice.

SUM

Funkcija SUM sešteje vse številke v navedenih celicah. Oglejte si spodaj, kako jih lahko uporabo kot pogojne funkcije.

SUBTOTAL

Funkcija SUBTOTAL vrne rezultate COUNT, COUNTA ali SUM filtriranih podatkov, t.j. podatke v celicah, izbranih s filtrom.

COUNTIF

Funkcija COUNTIF prešteje elemente, ki ustrezajo podanemu pogoju. Primer: COUNTIF(A1:A4; “>4”) prešteje celice v obsegu A1:A4, ki so večje kot 4.

SUMIF

Funkcija SUMIF sešteje tiste elemente, ki ustrezajo podanemu pogoju. Primer: SUMIF(A1:A4; ”=rdeči”; B1:B4) sešteje vrednosti v obsegu B1:B4, ki ustrezajo vnosom “rdeči” v obsegu in A1:A4.

DCOUNT, DCOUNTA, DSUM

Funkcija DCOUNT, funkcija DCOUNTA in funkcija DSUM se vedejo podobno kot COUNT, COUNTA in SUM, le da so celice, ki naj bodo seštete ali preštete, izbrane glede na tabelo pogojev. Primer: DCOUNT(A1:C5; 0; E6:F7) prešteje vrstice v obsegu A1:C5, za katere veljajo vsi pogoji, navedeni v obsegu E6:F7.

Pogoji v obsegih celic

Enostavna metoda za štetje ali seštevanje z več pogoji je vnos teh pogojev v novo vrstico ali stolpec. Primer: če A1:A6 vsebuje seznam barv in B1:B6 seznam velikosti, lahko v celico D1 vnesemo formulo =(A1=”rdeče”), kar vrne TRUE ali FALSE, odvisno od tega, ali je celica A1 rdeče ali ne. Druga pot je, da v celico D1 vnesemo formulo =AND(A1=”rdeče”; B1=”veliko”), ki vrne TRUE, če je celica A1 rdeče IN celica B1 veliko, sicer vrne FALSE. To formulo kopirajte in prilepite v D2:D6, tako da dobimo obseg celic, ki so TRUE, če ustrezajo pogojem, sicer so FALSE.

Pri številskih izračunih se TRUE obravnava kot 1 in FALSE kot 0. Z vnosom =SUM(D1:D6) enostavno seštejete te 1-ice in 0-le, tako dobite število vnosov, ki so rdeče IN veliko.

Ker se pravzaprav TRUE in FALSE ovrednotita kot 1 in 0, ne potrebujemo funkcije AND - v D1 lahko enostavno zapišemo =(A1=”rdeče”)*(B1=”veliko”) in jo kopiramo/prilepimo navzdol v D2:D6.

Zdaj pa recimo, da C1:C6 vsebuje seznam tež teh vnosov, zanima pa nas skupna teža vsega, kar je veliko rdeče. V D1 zapišemo =(A1=”rdeče”)*(B1=”veliko”)*C1 in kopiramo/prilepimo navzdol v D2:D6. D1 bo vsebovalo težo v C1, če ustreza pogojem (sicer bo teža nič) in tako naprej v D2:D6. Tako bo formula =SUM(D1:D6) vrnila skupno težo.

Drug način je, da obseg D1:D6 zapolnimo z matrično formulo. V D1 vpišemo =(A1:A6=”rdeče”)*(B1:B6=”veliko”)*C1:C6 in jo vnesemo z Ctrl+Shift+Enter. Vse celice v D1:D6 zdaj kažejo želene teže, kot v prejšnjem primeru.

SUMPRODUCT

Funkcijo SUMPRODUCT lahko uporabite za izvajanje štetja in seštevanja v prejšnjem odseku brez rabe dodatnih stolpcev. Da bi to razumeli, morate razumeti matrične formule.

Če uporabimo primer iz gornjega razdelka, lahko A1:A6=”rdeče”, B1:B6=”veliko” in C1:C6 obravnavamo kot 3 ločene matrike, ki niso prikazane, temveč le notranje izračunane.

=SUMPRODUCT(A1:A6=”rdeče”; B1:B6=”veliko”; C1:C6) pomnoži ustrezne elemente matrik in vrne njihovo vsoto, t.j.:

(A1=”rdeče”)*(B1=”veliko”)*C1 + (A2=”rdeče”)*(B2=”veliko”)*C2 + ...

To znova vrne skupno težo, pri čemer dodaten stolpec ni potreben.

Opazite lahko, da forumle SUMPRODUCT enostavno vnesete, če pritisnete tipko Enter - ne zahtevajo Ctrl+Shift+Enter, čeprav gre za matrike.

Prav tako upoštevajte, da izračuni z ogromnimi matrikami vzamejo veliko časa za obdelavo, zato lahko zelo upočasnijo delo s preglednico.

SUM z matričnimi formulami

Alternativa k SUMPRODUCT je uporaba funkcije SUM. Prejšnji primer bi tako zapisali kot:

=SUM( (A1:A6=”rdeče”)*(B1:B6=”veliko”)*C1:C6) )

in vnesli kot matrično formulo s pritiskom Ctrl+Shift+Enter. Tako kot pri SUMPRODUCT formula deluje kot množenje ustreznih elementov matrik, vrne pa njihov seštevek.

Več podrobnosti in primerov matričnih formul najdete v kakOOojčku Uporaba polj.

DataPilot

Drug pristop k pogojnemu štetju in seštevanju je uporaba DataPilota, s katerim ustvarite interaktivno tabelo, kjer lahko podatke razporedite in seštevate glede na različne vidike.

Triki in nasveti: preverjanje nastavitev

Pri ujemanju besedila z nekaterimi funkcijami (kot je SUMIF) so lahko rezultati odvisni od nastavitev na strani meni Orodja -> Možnosti -> OpenOffice.org Calc -> Izračuni. Če uporabnikove nastavitve niso pravilne, so lahko rezultati napačni.

Možna rešitev je, da na vidno mesto v preglednico vključimo preverjanje, da so nastavitve pravilne. Primer:

=IF(ISERR(SEARCH(".";"a"));"NAPAKA: prosimo, da omogočite regularne izraze";"")

pokaže sporočilo o napaki, če podpora za regularne izraze ni vključena.

Še en primer - v celico A3 vnesite besedilo:

Preverjeno: 

V celico A4 vnesite:

="Regularni izrazi so "&IF(COUNTIF(A3;".*"); "omogočeni"; "onemogočeni")

V celico A5 vnesite:

="Ujemanje celih celic je "&IF(COUNTIF(A3;"<>e"); "omogočeno"; "onemogočeno")

ali še bolje: uporabite ustrezna sporočila o napaki.

Triki in nasveti: elementi med dvema datumoma

Datumi so interno shranjeni kot številke, zato jih je zelo enostavno primerjati. Če želite npr. prešteti celice v A1:A6 med dvema datumoma, lahko uporabite:

=SUMPRODUCT(A1:A6>DATEVALUE("5 Nov 06"); A1:A6<DATEVALUE("5 Dec 06"))

Če izrazite datume s poševnicami (npr. “1/2/2005”), lahko opustite funkcijo DATEVALUE, ker bo Calc datum pretvoril sam. Vendar bodite pozorni, saj bo takšno besedilo glede na krajevne nastavitve ponekod pretvorjeno v 1Feb05 (npr. Slovenija), drugod pa v 2Jan05 (npr. ZDA).

Triki in nasveti: seštevanje največjih/najmanjših vrednosti

Če želite sešteti največja tri števila v obsegu A1:A5, to najenostavneje storite tako, da vnesete:

=LARGE(A1:A5; 1) v celico B1

=LARGE(A1:A5; 2) v celico B2

=LARGE(A1:A5; 3) v celico B3

tako da bodo največja 3 števila v obsegu B1:B3, nato pa uporabite formulo:

=SUM(B1:B3), ki poda rezultat.

Ta metoda je zelo jasna in jo zato v splošnem priporočamo.

Če želite isti rezultat dobiti v eni sami celici, lahko uporabite:

=SUMPRODUCT(LARGE(A1:A5; ROW(A1:A3)))

Tukaj ROW(A1:A3) predstavlja matriko (polje) z 1 stolpcem in 3 vrsticami, ki vsebuje številke 1, 2, 3.

LARGE(A1:A5; ROW(A1:A3)) je tako 1-stolpčna, 3-vrstična matrika, ki vsebuje največje 3 številke, funkcija SUMPRODUCT pa jih zgolj sešteje. Namesto SUMPRODUCT bi lahko uporabili tudi SUM, vendar je potrebno v tem primeru vnesti formulo kot matrično formulo s pritiskom kombinacije tipk Ctrl+Shift+Enter.

Če želite sešteti največje 4 številke (za primer), uporabite ...ROW(A1:A4).. namesto ...ROW(A1:A3)..

Če želite sešteti najmanjše številke, uporabite SMALL(...) namesto LARGE(...).

Triki in nasveti: seštevanje celic, ki ustrezajo praznim v drugem stolpcu itd.

=SUMPRODUCT(ISBLANK(A1:A5); B1:B5)

sešteje tiste celice v B1:B5, ki ustrezajo praznim celicam v A1:A5.

Druge podobne funkcije (npr. ISTEXT, ISNUMBER) lahko uporabite na enak način.

Triki in nasveti: seštevanje v več kot enem stolpcu

=SUMPRODUCT(D1:D6="red";E1:E6+F1:F6)

sešteje vrednosti v celicah E1:E6 in v F1:F6, kar ustreza celicam D1:D6, ki vsebujejo rdeče. Če npr. D2 in D4 vsebujeta rdeče, je rezultat E2+F2+E4+F4.

Triki in nasveti: seštevanje vsake n-te vrstice

=SUMPRODUCT(MOD(ROW(A1:A8); 2)=0;  A1:A8)

bo seštelo vsake sode vrstice v obsegu A1:A8. Spremenite =0 v =1 da boste sešteli vse lihe vrstice. Spremenite 2 v 3, da boste sešteli vse tretje vrstice itn.

Triki in nasveti: seštevanje elementov z določenim oblikovanjem

Funkcija CELL vrne informacije o celici, npr. obliko številk ali datumov, ki so v njej prikazani, in širino stolpca. Zato jo lahko uporabimo z eno gornjih metod. Funkcija, ki bi vrnila barvo ali pisavo celice, ne obstaja. Če ne obstaja nobena neodvisna formula glede barve ali pisave, je potrebno uporabiti makro.

Triki in nasveti: seštevanje ujemajočih elementov v ločen seznam

Recimo, da A1:A9 vsebuje seznam datumov, B1:B9 vsebuje telefonske številke in C1:C9 stroške telefonskih klicev. F1:F5 je seznam določenih telefonskih števil, zanima pa vas strošek klicev na te številke.

V celico D1 vnesite:

=ISNUMBER(MATCH(B1; F$1:F$5; 0))*C1

in kopirajte/prilepite tudi v D2:D9

=SUM(D1:D9) zdaj pove skupen strošek klicev.

Če želite ta izračun izvesti brez dodatnega stolpca, lahko uporabite:

=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)

Zgoraj smo razpravljali, kako pomembno je, da preverimo nastavitve programa Calc; to je dober primer - če so regularni izrazi vključeni (privzeta nastavitev), je telefonska številka, zapisana kot (720) 528-1700, interpretirana kot regularni izraz in se lahko ujema z drugačnimi vnosi, kot je dejanski (720) 528-1700.

Content on this page is licensed under the Public Documentation License (PDL).