Difference between revisions of "Documentation/SL/How Tos/Pogojno štetje in seštevanje"
(→COUNT) |
(→COUNTA) |
||
Line 17: | Line 17: | ||
==COUNTA== | ==COUNTA== | ||
− | [[Documentation/How_Tos/Calc: COUNTA | + | [[Documentation/SL/How_Tos/Calc: funkcija COUNTA|Funkcija COUNTA]] prešteje celice, ki vsebujejo karkoli (besedilo, številke, napake, logične vrednosti, formule). Prezre prazne celice. |
==COUNTBLANK== | ==COUNTBLANK== |
Revision as of 14:05, 20 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
One simple method to count or sum using multiple conditions is to enter those conditions in a new row or column. For example, if A1:A6 contains a list of colours and B1:B6 a list of sizes, then we can enter in cell D1 the formula =(A1=”red”), which returns TRUE or FALSE depending if cell A1 is red or not. Alternatively, we can enter in cell D1 the formula =AND(A1=”red”; B1=”big”), which returns TRUE if cell A1 is red AND cell B1 is big and FALSE otherwise. Copy and paste this formula to D2:D6 and we have a range of cells which are TRUE if the conditions are met and FALSE otherwise.
In numerical calculations, TRUE is treated as 1, and FALSE is treated as 0. So entering =SUM(D1:D6) will simply sum those 1s and 0s, and give us the count of items that are both red AND big.
In fact, because TRUE and FALSE evaluate as 1 and 0, we do not need the AND function - in D1 we can simply write =(A1=”red”)*(B1=”big”), and copy/paste down to D2:D6.
Now let us say that C1:C6 contains a list of weights of these items, and we wish to know the total weight for all big red items. In D1 we write =(A1=”red”)*(B1=”big”)*C1, and copy/paste down to D2:D6. D1 will contain the weight in C1 if the conditions are met (and zero otherwise) and so on for D2:D6, Therefore =SUM(D1:D6) will now give us the total weight.
Alternatively, it is possible to fill D1:D6 with an array formula. In D1, write =(A1:A6=”red”)*(B1:B6=”big”)*C1:C6, and enter by pressing Ctrl_Shift_Enter. All the cells in D1:D6 now show the desired weights as before.
SUMPRODUCT
The SUMPRODUCT function can be used to perform the counting and summation calculations in the previous section, without using extra columns. It is necessary to understand array formulas to understand this.
Using the summation example from the previous section, A1:A6=”red”, B1:B6=”big” and C1:C6 may be treated as 3 separate arrays, not displayed but internally calculated.
=SUMPRODUCT(A1:A6=”red”; B1:B6=”big”; C1:C6) will multiply corresponding elements of the arrays together and return their sum, i.e.:
(A1=”red”)*(B1=”big”)*C1 + (A2=”red”)*(B2=”big”)*C2 + ...
This again gives us the total weight, without requiring an extra column.
Notice that SUMPRODUCT formulas are simply entered by pressing the Enter key - they do not require Ctrl_Shift_Enter even though arrays are involved.
Also, be aware that calculations using very large arrays take a lot of computer processing time, and may slow the spreadsheet down.
SUM z matričnimi formulami
An alternative to SUMPRODUCT is to use the SUM function. The previous example would be written:
=SUM( (A1:A6=”red”)*(B1:B6=”big”)*C1:C6) )
and entered as an array formula by pressing Ctrl_Shift_Enter. As with SUMPRODUCT, this works by multiplying corresponding elements of the arrays together and returning their sum.
See Using Arrays for details of array formulas and more examples.
DataPilot
Another way to approach conditional counting and summation is to use the DataPilot to generate an interactive table, whereby data can be arranged and summarised according to different points of view.
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). |