Difference between revisions of "Documentation/SL/How Tos/Pogojno štetje in seštevanje"
(→COUNT) |
(→Triki in nasveti: seštevanje največjih/najmanjših vrednosti) |
||
(31 intermediate revisions by the same user not shown) | |||
Line 3: | Line 3: | ||
//--> | //--> | ||
[[Category:Documentation/SL]] | [[Category:Documentation/SL]] | ||
− | [[Category:Calc]] | + | [[Category:Calc po slovensko]] |
[[Category:KakOOojčki]] | [[Category:KakOOojčki]] | ||
{{DISPLAYTITLE:Pogojno štetje in seštevanje}} | {{DISPLAYTITLE:Pogojno štetje in seštevanje}} | ||
Line 13: | Line 13: | ||
==COUNT== | ==COUNT== | ||
− | [[Documentation/How_Tos/Calc: COUNT | + | [[Documentation/SL/How_Tos/Calc: funkcija COUNT|Funkcija COUNT]] prešteje celice, ki vsebujejo številke in prezre vse druge. Tako so npr. celice z besedilom prezrte. |
==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== | ||
− | [[Documentation/How_Tos/Calc: COUNTBLANK | + | [[Documentation/SL/How_Tos/Calc: funkcija COUNTBLANK|Funkcija COUNTBLANK]] prešteje prazne celice. |
==SUM== | ==SUM== | ||
− | [[Documentation/How_Tos/Calc: SUM | + | [[Documentation/SL/How_Tos/Calc: funkcija SUM|Funkcija SUM]] sešteje vse številke v navedenih celicah. Oglejte si spodaj, kako jih lahko uporabo kot pogojne funkcije. |
==SUBTOTAL== | ==SUBTOTAL== | ||
− | + | [[Documentation/SL/How_Tos/Calc: funkcija SUBTOTAL|Funkcija SUBTOTAL]] vrne rezultate <tt>'''COUNT'''</tt>, <tt>'''COUNTA'''</tt> ali <tt>'''SUM'''</tt> filtriranih podatkov, t.j. podatke v celicah, izbranih s '''filtrom'''. | |
==COUNTIF== | ==COUNTIF== | ||
− | + | [[Documentation/SL/How_Tos/Calc: funkcija COUNTIF|Funkcija COUNTIF]] prešteje elemente, ki ustrezajo podanemu pogoju. Primer: <tt>'''COUNTIF(A1:A4; “>4”)'''</tt> prešteje celice v obsegu <tt>'''A1:A4'''</tt>, ki so večje kot <tt>'''4'''</tt>. | |
==SUMIF== | ==SUMIF== | ||
− | + | [[Documentation/SL/How_Tos/Calc: funkcija SUMIF|Funkcija SUMIF]] sešteje tiste elemente, ki ustrezajo podanemu pogoju. Primer: <tt>'''SUMIF(A1:A4; ”=rdeči”; B1:B4)'''</tt> sešteje vrednosti v obsegu <tt>'''B1:B4'''</tt>, ki ustrezajo vnosom “<tt>'''rdeči'''</tt>” v obsegu in <tt>'''A1:A4'''</tt>. | |
==DCOUNT, DCOUNTA, DSUM== | ==DCOUNT, DCOUNTA, DSUM== | ||
− | + | [[Documentation/SL/How_Tos/Calc: funkcija DCOUNT|Funkcija DCOUNT]], | |
− | [[Documentation/How_Tos/Calc: DCOUNTA | + | [[Documentation/SL/How_Tos/Calc: funkcija DCOUNTA|funkcija DCOUNTA]] in |
− | [[Documentation/How_Tos/Calc: DSUM | + | [[Documentation/SL/How_Tos/Calc: funkcija DSUM|funkcija DSUM]] se vedejo podobno kot <tt>'''COUNT'''</tt>, <tt>'''COUNTA'''</tt> in <tt>'''SUM'''</tt>, le da so celice, ki naj bodo seštete ali preštete, izbrane glede na tabelo pogojev. Primer: <tt>'''DCOUNT(A1:C5; 0; E6:F7)'''</tt> prešteje vrstice v obsegu <tt>'''A1:C5'''</tt>, za katere veljajo vsi pogoji, navedeni v obsegu <tt>'''E6:F7'''</tt>. |
− | == | + | ==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 <tt>'''A1:A6'''</tt> vsebuje seznam barv in <tt>'''B1:B6'''</tt> seznam velikosti, lahko v celico <tt>'''D1'''</tt> vnesemo formulo <tt>'''<nowiki>=(A1=”rdeče”)</nowiki>'''</tt>, kar vrne <tt>'''TRUE'''</tt> ali <tt>'''FALSE'''</tt>, odvisno od tega, ali je celica <tt>'''A1'''</tt> <tt>'''rdeče'''</tt> ali ne. Druga pot je, da v celico <tt>'''D1'''</tt> vnesemo formulo <tt>'''<nowiki>=AND(A1=”rdeče”; B1=”veliko”)</nowiki>'''</tt>, ki vrne <tt>'''TRUE'''</tt>, če je celica <tt>'''A1'''</tt> <tt>'''rdeče'''</tt> IN celica <tt>'''B1'''</tt> <tt>'''veliko'''</tt>, sicer vrne <tt>'''FALSE'''</tt>. To formulo kopirajte in prilepite v <tt>'''D2:D6'''</tt>, tako da dobimo obseg celic, ki so <tt>'''TRUE'''</tt>, če ustrezajo pogojem, sicer so <tt>'''FALSE'''</tt>. | |
− | [[Image:Condsummation_1.png| | + | [[Image:Condsummation_1.png|primer]] |
− | + | Pri številskih izračunih se <tt>'''TRUE'''</tt> obravnava kot <tt>'''1'''</tt> in <tt>'''FALSE'''</tt> kot <tt>'''0'''</tt>. Z vnosom <tt>'''<nowiki>=SUM(D1:D6)</nowiki>'''</tt> enostavno seštejete te <tt>'''1'''</tt>-ice in <tt>'''0'''</tt>-le, tako dobite število vnosov, ki so <tt>'''rdeče'''</tt> IN <tt>'''veliko'''</tt>. | |
− | + | Ker se pravzaprav <tt>'''TRUE'''</tt> in <tt>'''FALSE'''</tt> ovrednotita kot <tt>'''1'''</tt> in <tt>'''0'''</tt>, ne potrebujemo funkcije <tt>'''AND'''</tt> - v <tt>'''D1'''</tt> lahko enostavno zapišemo <tt>'''<nowiki>=(A1=”rdeče”)*(B1=”veliko”)</nowiki>'''</tt> in jo kopiramo/prilepimo navzdol v <tt>'''D2:D6'''</tt>. | |
− | + | Zdaj pa recimo, da <tt>'''C1:C6'''</tt> vsebuje seznam tež teh vnosov, zanima pa nas skupna teža vsega, kar je <tt>'''veliko'''</tt> <tt>'''rdeče'''</tt>. V <tt>'''D1'''</tt> zapišemo <tt>'''<nowiki>=(A1=”rdeče”)*(B1=”veliko”)*C1</nowiki>'''</tt> in kopiramo/prilepimo navzdol v <tt>'''D2:D6'''</tt>. <tt>'''D1'''</tt> bo vsebovalo težo v <tt>'''C1'''</tt>, če ustreza pogojem (sicer bo teža nič) in tako naprej v <tt>'''D2:D6'''</tt>. Tako bo formula <tt>'''<nowiki>=SUM(D1:D6)</nowiki>'''</tt> vrnila skupno težo. | |
− | [[Image:Condsummation_2.png| | + | [[Image:Condsummation_2.png|primer]] |
− | + | Drug način je, da obseg <tt>'''D1:D6'''</tt> zapolnimo z matrično formulo. V <tt>'''D1'''</tt> vpišemo <tt>'''<nowiki>=(A1:A6=”rdeče”)*(B1:B6=”veliko”)*C1:C6</nowiki>'''</tt> in jo vnesemo z Ctrl+Shift+Enter. Vse celice v <tt>'''D1:D6'''</tt> zdaj kažejo želene teže, kot v prejšnjem primeru. | |
==SUMPRODUCT== | ==SUMPRODUCT== | ||
− | + | [[Documentation/SL/How_Tos/Calc: funkcija 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 <tt>'''A1:A6=”rdeče”'''</tt>, <tt>'''B1:B6=”veliko”'''</tt> in <tt>'''C1:C6'''</tt> obravnavamo kot 3 ločene matrike, ki niso prikazane, temveč le notranje izračunane. | |
− | <tt>'''<nowiki>=SUMPRODUCT(A1:A6= | + | <tt>'''<nowiki>=SUMPRODUCT(A1:A6=”rdeče”; B1:B6=”veliko”; C1:C6)</nowiki>'''</tt> pomnoži ustrezne elemente matrik in vrne njihovo vsoto, t.j.: |
− | '''(A1= | + | '''(A1=”rdeče”)*(B1=”veliko”)*C1 + (A2=”rdeče”)*(B2=”veliko”)*C2 + ...''' |
− | [[Image:Condsummation_3.png| | + | [[Image:Condsummation_3.png|primer]] |
− | + | To znova vrne skupno težo, pri čemer dodaten stolpec ni potreben. | |
− | + | Opazite lahko, da forumle <tt>'''SUMPRODUCT'''</tt> 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 [[Documentation/SL/How_Tos/Calc: funkcija SUM|funkcije SUM]]. Prejšnji primer bi tako zapisali kot: | |
− | + | '''<nowiki>=SUM( (A1:A6=”rdeče”)*(B1:B6=”veliko”)*C1:C6) )</nowiki>''' | |
− | + | 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 [[Documentation/SL/How_Tos/Uporaba polj|'''Uporaba polj''']]. | |
− | + | ||
− | + | ||
==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== |
− | + | Pri ujemanju besedila z nekaterimi funkcijami (kot je <tt>'''SUMIF'''</tt>) 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. | |
− | [[Image:Condsummation_4.png| | + | [[Image:Condsummation_4.png|nastavitve]] |
− | + | Možna rešitev je, da na vidno mesto v preglednico vključimo preverjanje, da so nastavitve pravilne. Primer: | |
− | '''<nowiki>=IF(ISERR(SEARCH(".";"a"));" | + | '''<nowiki>=IF(ISERR(SEARCH(".";"a"));"NAPAKA: prosimo, da omogočite regularne izraze";"")</nowiki>''' |
− | + | pokaže sporočilo o napaki, če podpora za regularne izraze ni vključena. | |
− | + | Še en primer - v celico <tt>'''A3'''</tt> vnesite besedilo: | |
− | ''' | + | '''Preverjeno: ''' |
− | + | V celico <tt>'''A4'''</tt> vnesite: | |
− | '''<nowiki>=" | + | '''<nowiki>="Regularni izrazi so "&IF(COUNTIF(A3;".*"); "omogočeni"; "onemogočeni")</nowiki>''' |
− | + | V celico <tt>'''A5'''</tt> vnesite: | |
− | '''<nowiki>=" | + | '''<nowiki>="Ujemanje celih celic je "&IF(COUNTIF(A3;"<>e"); "omogočeno"; "onemogočeno")</nowiki>''' |
− | [[Image:Condsummation_7.png| | + | [[Image:Condsummation_7.png|preverjanje nastavitev]] |
− | + | 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: | |
'''<nowiki>=SUMPRODUCT(A1:A6>DATEVALUE("5 Nov 06"); A1:A6<DATEVALUE("5 Dec 06"))</nowiki>''' | '''<nowiki>=SUMPRODUCT(A1:A6>DATEVALUE("5 Nov 06"); A1:A6<DATEVALUE("5 Dec 06"))</nowiki>''' | ||
− | + | Če izrazite datume s poševnicami (npr. “<tt>'''1/2/2005'''</tt>”), lahko opustite funkcijo <tt>'''DATEVALUE'''</tt>, 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 <tt>'''A1:A5'''</tt>, to najenostavneje storite tako, da vnesete: | |
− | <tt>'''<nowiki>=LARGE(A1:A5; 1)</nowiki>'''</tt> | + | <tt>'''<nowiki>=LARGE(A1:A5; 1)</nowiki>'''</tt> v celico <tt>'''B1'''</tt> |
− | <tt>'''<nowiki>=LARGE(A1:A5; 2)</nowiki>'''</tt> | + | <tt>'''<nowiki>=LARGE(A1:A5; 2)</nowiki>'''</tt> v celico <tt>'''B2'''</tt> |
− | <tt>'''<nowiki>=LARGE(A1:A5; 3)</nowiki>'''</tt> | + | <tt>'''<nowiki>=LARGE(A1:A5; 3)</nowiki>'''</tt> v celico <tt>'''B3'''</tt> |
− | + | tako da bodo največja 3 števila v obsegu <tt>'''B1:B3'''</tt>, nato pa uporabite formulo: | |
− | <tt>'''<nowiki>=SUM(B1:B3)</nowiki>'''</tt> | + | <tt>'''<nowiki>=SUM(B1:B3)</nowiki>'''</tt>, ki poda rezultat. |
− | [[Image:Condsummation_5.png| | + | [[Image:Condsummation_5.png|primer]] |
− | + | Ta metoda je zelo jasna in jo zato v splošnem priporočamo. | |
− | + | Če želite isti rezultat dobiti v eni sami celici, lahko uporabite: | |
'''<nowiki>=SUMPRODUCT(LARGE(A1:A5; ROW(A1:A3)))</nowiki>''' | '''<nowiki>=SUMPRODUCT(LARGE(A1:A5; ROW(A1:A3)))</nowiki>''' | ||
− | [[Image:Condsummation_6.png| | + | [[Image:Condsummation_6.png|primer]] |
− | + | Tukaj <tt>'''ROW(A1:A3)'''</tt> predstavlja matriko (polje) z 1 stolpcem in 3 vrsticami, ki vsebuje številke <tt>'''1'''</tt>, <tt>'''2'''</tt>, <tt>'''3'''</tt>. | |
− | <tt>'''LARGE(A1:A5; ROW(A1:A3))'''</tt> | + | <tt>'''LARGE(A1:A5; ROW(A1:A3))'''</tt> je tako 1-stolpčna, 3-vrstična matrika, ki vsebuje največje 3 številke, funkcija <tt>'''SUMPRODUCT'''</tt> pa jih zgolj sešteje. Namesto <tt>'''SUMPRODUCT'''</tt> bi lahko uporabili tudi <tt>'''SUM'''</tt>, 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 <tt>'''...ROW(A1:A4)..'''</tt> namesto <tt>'''...ROW(A1:A3)..'''</tt> | |
− | + | Če želite sešteti najmanjše številke, uporabite <tt>'''SMALL(...)'''</tt> namesto <tt>'''LARGE(...)'''</tt>. | |
− | == | + | ==Triki in nasveti: seštevanje celic, ki ustrezajo praznim v drugem stolpcu itd.== |
'''<nowiki>=SUMPRODUCT(ISBLANK(A1:A5); B1:B5)</nowiki>''' | '''<nowiki>=SUMPRODUCT(ISBLANK(A1:A5); B1:B5)</nowiki>''' | ||
− | + | sešteje tiste celice v <tt>'''B1:B5'''</tt>, ki ustrezajo praznim celicam v <tt>'''A1:A5'''</tt>. | |
− | + | Druge podobne funkcije (npr. <tt>'''ISTEXT'''</tt>, <tt>'''ISNUMBER'''</tt>) lahko uporabite na enak način. | |
− | == | + | ==Triki in nasveti: seštevanje v več kot enem stolpcu== |
'''<nowiki>=SUMPRODUCT(D1:D6="red";E1:E6+F1:F6)</nowiki>''' | '''<nowiki>=SUMPRODUCT(D1:D6="red";E1:E6+F1:F6)</nowiki>''' | ||
− | + | sešteje vrednosti v celicah <tt>'''E1:E6'''</tt> in v <tt>'''F1:F6'''</tt>, kar ustreza celicam <tt>'''D1:D6'''</tt>, ki vsebujejo <tt>'''rdeče'''</tt>. Če npr. <tt>'''D2'''</tt> in <tt>'''D4'''</tt> vsebujeta rdeče, je rezultat <tt>'''E2+F2+E4+F4'''</tt>. | |
− | == | + | ==Triki in nasveti: seštevanje vsake n-te vrstice== |
'''<nowiki>=SUMPRODUCT(MOD(ROW(A1:A8); 2)=0; </nowiki> A1:A8)''' | '''<nowiki>=SUMPRODUCT(MOD(ROW(A1:A8); 2)=0; </nowiki> A1:A8)''' | ||
− | + | bo seštelo vsake sode vrstice v obsegu <tt>'''A1:A8'''</tt>. Spremenite <tt>'''=0'''</tt> v <tt>'''=1'''</tt> da boste sešteli vse lihe vrstice. Spremenite <tt>'''2'''</tt> v <tt>'''3'''</tt>, da boste sešteli vse tretje vrstice itn. | |
− | [[Image:Condsummation_8.png| | + | [[Image:Condsummation_8.png|primer]] |
==Triki in nasveti: seštevanje elementov z določenim oblikovanjem== | ==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== | ==Triki in nasveti: seštevanje ujemajočih elementov v ločen seznam== | ||
Line 213: | Line 212: | ||
[[Image:Condsummation_9.png|primer]] | [[Image:Condsummation_9.png|primer]] | ||
− | + | Če želite ta izračun izvesti brez dodatnega stolpca, lahko uporabite: | |
<nowiki>=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)</nowiki> | <nowiki>=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)</nowiki> | ||
− | + | 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. | |
[[fr:Documentation/FR/Calc/Sommes et calculs conditionnels]] | [[fr:Documentation/FR/Calc/Sommes et calculs conditionnels]] | ||
{{PDL1}} | {{PDL1}} |
Latest revision as of 10:35, 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
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). |