Revision as of 13:53, 6 July 2008

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

The COUNTIF function counts those items that meet a single condition. For example COUNTIF(A1:A4; “>4”) counts the cells in A1:A4 that are greater than 4.

SUMIF

The SUMIF function sums those items that meet a single condition. For example SUMIF(A1:A4; ”=red”; B1:B4) sums the values in B1:B4 that correspond to “red” entries in A1:A4.

DCOUNT, DCOUNTA, DSUM

The DCOUNT function, DCOUNTA function, and DSUM function perform similarly to COUNT, COUNTA and SUM, except that the cells to be counted or summed are chosen according to a table of conditions. For example, DCOUNT(A1:C5; 0; E6:F7) counts the number of rows of A1:C5 for which the multiple conditions specified in E6:F7 are all true.

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.