Difference between revisions of "Documentation/SL/How Tos/Pogojno štetje in seštevanje"
(→SUM z matričnimi formulami) |
(→DataPilot) |
||
Line 95: | Line 95: | ||
==DataPilot== | ==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== | ==Triki in nasveti: preverjanje nastavitev== |
Revision as of 10:19, 23 July 2008
Contents
- 1 Pogojno štetje in seštevanje v modulu Calc
- 1.1 COUNT
- 1.2 COUNTA
- 1.3 COUNTBLANK
- 1.4 SUM
- 1.5 SUBTOTAL
- 1.6 COUNTIF
- 1.7 SUMIF
- 1.8 DCOUNT, DCOUNTA, DSUM
- 1.9 Pogoji v obsegih celic
- 1.10 SUMPRODUCT
- 1.11 SUM z matričnimi formulami
- 1.12 DataPilot
- 1.13 Triki in nasveti: preverjanje nastavitev
- 1.14 Triki in nasveti: elementi med dvema datumoma
- 1.15 Triki in nasveti: seštevanje največjih/najmanjših vrednosti
- 1.16 Triki in nasveti: seštevanje celic, ki ustrezajo praznim v drugem stolpcu itd.
- 1.17 Triki in nasveti: seštevanje v več kot enem stolpcu
- 1.18 Triki in nasveti: seštevanje vsake n-te vrstice
- 1.19 Triki in nasveti: seštevanje elementov z določenim oblikovanjem
- 1.20 Triki in nasveti: seštevanje ujemajočih elementov v ločen seznam
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
Dates are stored internally as numbers and can thus be compared easily. For example, to count the number of cells in A1:A6 between two dates, you could use:
=SUMPRODUCT(A1:A6>DATEVALUE("5 Nov 06"); A1:A6<DATEVALUE("5 Dec 06"))
If you express the dates with slashes (e.g. “1/2/2005”) you can dispense with the DATEVALUE function, as Calc will convert the date. However, be aware that in one country this text may be converted to 1Feb05 and in another to 2Jan05.
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.
However if you wish to derive the same result in a single cell, you could use
=SUMPRODUCT(LARGE(A1:A5; ROW(A1:A3)))
Here ROW(A1:A3) is a 1 column 3 row array containing the numbers 1, 2, 3.
LARGE(A1:A5; ROW(A1:A3)) is then a 1 column 3 row array containing the largest 3 numbers and SUMPRODUCT simply adds them up. We could use SUM instead of SUMPRODUCT but in that case the formula must be entered as an array formula by pressing Ctrl_Shift_Enter.
To add up the largest 4 numbers (say), use ...ROW(A1:A4).. instead of ...ROW(A1:A3)..
To add the smallest numbers, use SMALL(...) instead of 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
The CELL function returns information about cells, for instance the format that numbers or dates are displayed in and the column width. It can thus be used with one of the methods above. There is no function that returns the colour or font of a cell. If no independent formula relating to colour or font exists, then it may be necessary to use a macro.
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.
To perform this calculation without an extra column, you could use:
=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)
We discussed above how it can be important to check Calc settings; this is a good example - if the user has regular expressions turned on (the default) a telephone number written as (720) 528-1700 is interpreted as a regular expression and might match entries other than (720) 528-1700.
Content on this page is licensed under the Public Documentation License (PDL). |