Ako zhrnúť jednotlivé bunky v programe Excel. Excel. Spočítajte a spočítajte bunky, ktoré spĺňajú kritériá podmieneného formátovania
Často sa stáva, že v bunke musíte sčítať každú druhú, tretiu, štvrtú atď. Bunku. Teraz je to vďaka ďalšiemu triku možné.
Excel neposkytuje štandardnú funkciu, ktorá by dokázala sčítať každý n-ta bunka alebo povrázok. Túto úlohu však môžete splniť niekoľkými rôzne cesty... Všetky tieto prístupy sú založené na funkciách ROW a MOD.
Funkcia ROW vráti číslo riadku pre danú referenciu na bunku: ROW (referencia), v ruskej verzii Excel ROW (referencia).
Funkcia OSTAT (MOD) vráti zvyšok rozdelenia čísla deliteľom: MOD (number; divisor), v ruskej verzii programu Excel OSTAT (number; divisor).
Vložte funkciu ROW do funkcie MOD (na zadanie číselného argumentu), vydelte 2 (aby sme spočítali všetky ďalšie bunky) a skontrolujte, či výsledok nie je nula. Ak je to tak, bunka sa sčíta. Tieto funkcie je možné použiť rôznymi spôsobmi - niektoré poskytnú lepšie výsledky ako iné. Napríklad maticový vzorec na sčítanie každej druhej bunky v rozsahu $ A $ 1: $ A $ 100 môže vyzerať takto: \u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)), v ruskej verzii Excelu \u003d SUM (AK (OSTAT (LINE ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)).
Pretože sa jedná o maticový vzorec, musíte ho zadať stlačením Ctrl + Shift + Enter, Excel pridá zložené zátvorky tak, aby vyzeralo takto: (\u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500) ), 2) \u003d 0; $ A $ 1: $ A $ 500; 0))), v ruskej verzii programu Excel: (\u003d SUM (IF (zostávajúce (ROW ($ A $ 1: $ A $ 500)); 2) \u003d 0; $ A $ 1: $ A $ 500; 0))) Potrebujete Excel, aby sám pridával zložené zátvorky; ak ich pridáte sami, vzorec nebude fungovať.
Aj keď je cieľ dosiahnutý, táto metóda negatívne ovplyvňuje dizajn. tabuľkový kalkulátor... Toto je zbytočné použitie maticového vzorca. Aby toho nebolo málo, tento dlhý vzorec obsahuje prepočítateľnú funkciu ROW, ktorá premení veľký vzorec aj na prepočítateľnú funkciu. To znamená, že sa bude neustále prepočítavať, nech urobíte v zošite čokoľvek. Toto je veľmi zlý spôsob!
Tu je ďalší vzorec, ktorý je mierne najlepšia voľba: \u003d SUMPRODUCT ([MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)), v ruskej verzii Excelu \u003d SUMPRODUCT ((REST (RIADOK ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)).
Pamätajte však, že tento vzorec vráti #HODNOTU! (#HODNOTA!) Ak niektoré bunky v rozsahu obsahujú namiesto čísel text. Tento vzorec, aj keď v skutočnosti nejde o vzorec poľa, sa tiež spomaľuje excel prácaak ho používate príliš mnohokrát alebo ak sa vždy vzťahuje na veľký rozsah.
Našťastie existuje najlepšia cesta, ktorý je nielen efektívnejší, ale aj oveľa flexibilnejší. Vyžaduje použitie funkcie DSUM. V tomto príklade sme použili rozsah A1: A500 ako rozsah, do ktorého je potrebné pridať každú n-tú bunku.
Do bunky E1 zadajte slovo Kritériá. Do bunky E2 zadajte nasledujúci vzorec: \u003d MOD (ROW (A2) - $ C $ 2-1; $ C $ 2) \u003d 0, v ruskej verzii Excelu \u003d OSTAT (ROW (A2) - $ C $ 2- 1; $ C $ 2) \u003d 0. Vyberte bunku C2 a vyberte príkaz Údaje → Overenie.
V poli Povoliť vyberte Zoznam a do poľa Zdroj zadajte 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Skontrolujte, či je začiarknuté políčko Zoznam. prijateľné hodnoty (V bunke) a kliknite na tlačidlo OK. Do bunky C1 zadajte text SUM každý…. Do ktorejkoľvek bunky okrem riadku 1 zadajte nasledujúci vzorec: \u003d DSUM ($ A: $ A; 1; $ E $ 1: $ E $ 2), v ruskej verzii Excelu \u003d BDSUMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
Do bunky priamo nad bunkou, kde ste zadali funkciu DSUM, zadajte text \u003d "Summing Every" & $ C $ 2 & CHOOSE ($ C $ 2; "st"; "nd"; "rd"; "th" ; "th"; "th"; "th"; "th"; "th"; "th") & "Bunka". Teraz už len zostáva zvoliť požadované číslo v bunke C2 a o zvyšok sa postará funkcia DSUM.
Pomocou funkcie DSUM môžete sčítať bunky v intervale, ktorý zadáte. Funkcia DSUM je oveľa efektívnejšia ako maticový vzorec alebo funkcia SUMPRODUCT. Aj keď nastavenie trvá trochu dlhšie, je to tak, keď je ťažké sa naučiť a ľahko bojovať.
Predtým som popísal, ako nájsť a. Táto funkcia bohužiaľ nefunguje, ak sú bunky zafarbené podmienené formátovanie... Sľúbil som, že funkciu „spresním“. Ale za dva roky, ktoré uplynuli od uverejnenia tejto poznámky, som nebol schopný napísať stráviteľný kód sám alebo pomocou informácií z internetu ... ( Aktualizácia 29. marca 2017 Po ďalších piatich rokoch sa mi ešte podarilo napísať kód; pozri záverečnú časť poznámky). A len nedávno som narazil na myšlienku obsiahnutú v knihe D. Hawleyho, R. Hawleyho „Excel 2007. Triky“, ktorá vám umožní zaobísť sa bez kódu.
Nechajte existovať zoznam čísel od 1 do 100, ktorý sa nachádza v rozsahu A1: A100 (obr. 1; pozri tiež hárok „SUMIF“ súboru Excel). Rozsah bol podmienene naformátovaný tak, aby označoval bunky, ktoré obsahujú čísla väčšie ako 10 a menšie alebo rovné 20.
Obrázok: 1. rozsah čísel; podmienené formátovanie zvýraznených buniek obsahujúcich hodnoty od 10 do 20
Stiahnite si poznámku vo formáte, príklady vo formáte
Teraz musíte do buniek pridať hodnoty, ktoré zodpovedajú práve nastaveným kritériám. Nezáleží na tom, aký druh formátovania sa použije na tieto bunky, musíte však poznať kritériá, podľa ktorých sú bunky zvýraznené.
Ak chcete pridať celý rad buniek, ktoré sa zhodujú jeden Kritérium môžete použiť funkciu SUMIF (Obr. 2).
Obrázok: 2. Sčítanie buniek, ktoré spĺňajú jednu podmienku
Ak máte niektoré podmienok môžete použiť funkciu SUMIFS (obr. 3).
Obrázok: 3. Sčítanie buniek, ktoré spĺňajú niekoľko podmienok
Pomocou funkcie COUNTIF môžete spočítať počet buniek, ktoré spĺňajú jedno kritérium.
Pomocou funkcie COUNTIF môžete spočítať počet buniek, ktoré vyhovujú viacerým kritériám.
Excel poskytuje ďalšiu funkciu, ktorá umožňuje určiť viac podmienok. Táto funkcia je obsiahnutá v súbore funkcií báz excel dáta a nazýva sa BDSUMM. Ak to chcete skontrolovať, použite rovnakú množinu čísel v rozsahu A2: A100 (obr. 4; pozri tiež hárok „BDSUMM“ súboru Excel).
Obrázok: 4. Používanie databázových funkcií
Vyberte bunky C1: D2 a pomenujte tento rozsah Kritérium tak, že ho napíšete do poľa pre názov vľavo od panela vzorcov. Teraz vyberte bunku C1 a zadajte \u003d $ A $ 1, čo je odkaz na prvú bunku v hárku obsahujúcom názov databázy. Zadajte \u003d $ A $ 1 v bunke D1 a dostanete dve kópie hlavičky stĺpca A. Tieto kópie sa použijú ako hlavičky pre podmienky BDSUMM (C1: D2), ktoré ste pomenovali Kritérium. Do bunky C2 zadajte\u003e 10. Do bunky D2 zadajte<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($ A $ 1: $ A $ 101,1, kritérium)
Pomocou funkcie COUNT môžete spočítať počet buniek, ktoré vyhovujú viacerým kritériám.
Pri čítaní knihy od Johna Walkenbacha som sa dozvedel, že od verzie Excel 2010 má VBA novú vlastnosť DisplayFormat (pozri napríklad vlastnosť Range.DisplayFormat). To znamená, že VBA dokáže prečítať formát zobrazený na obrazovke. Nezáleží na tom, ako bol prijatý priamym nastavením používateľa alebo použitím podmieneného formátovania. Bohužiaľ, MS to urobilo tak, že vlastnosť DisplayFormat funguje iba v procedúrach volaných z VBA a UDF založené na tejto vlastnosti hodí #VALUE! Súčet hodnôt v rozsahu však môžete získať bunkami určitej farby pomocou postupu (makra, ale nie funkcie). Otvorené (obsahuje kód VBA). Prejdite si ponuku vyhliadka -> Makrá -> Makrá; v okne Makro, zvýraznite riadok SumColorUsla stlačte Vykonať... Spustite makro, vyberte rozsah sčítania a kritérium. Odpoveď sa zobrazí v okne.
Kód postupu
Sub SumColorConv () Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor \u003d 0 "Range query Set UserRange \u003d Application.InputBox (_ Prompt: \u003d" Select summation range ", _ Title: \u003d "Výber rozsahu", _ Predvolené: \u003d ActiveCell.Address, _ Typ: \u003d 8) "Sada požiadaviek na kritériá CriterionRange \u003d Application.InputBox (_ Výzva: \u003d" Vybrať súhrnné kritérium", _ Názov: \u003d" Výber kritérií ", _ Predvolené: \u003d ActiveCell.Address, _ Typ: \u003d 8)" Súčet "správnych" buniek pre každé i v UserRange Ak i.DisplayFormat.Interior.Color \u003d _ CriterionRange.DisplayFormat . Interior.Color Potom SumColor \u003d SumColor + i Koniec Ak Ďalej MsgBox SumColor Koniec Sub
Sub SumColorUl () Aplikácia. Volatile True Dim SumColor ako dvojitá Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor \u003d 0 "Dopyt rozsahu Nastaviť UserRange \u003d Application.InputBox (_ Výzva: \u003d "Vyberte rozsah súčtu", _ Názov: \u003d "Výber rozsahu", _ Predvolené: \u003d ActiveCell.Address, _ Typ: \u003d 8) „Proscriptor Nastaviť CriterionRange \u003d Aplikácia. InputBox (_ Výzva: \u003d „Vyberte kritérium súčtu“, _ Názov: \u003d "Výber kritéria", _ Predvolené: \u003d ActiveCell. Adresa, _ |
Predpokladajme, že máte takýto prehľad predaja:
Z toho treba zistiť koľko ceruzky predáva obchodný zástupca Ivanov v január.
PROBLÉM: Ako zhrnúť údaje podľa niekoľkých kritérií ??
ROZHODNUTIE: Metóda 1:
BDSUMM (A1: G16; F1; I1: K2)
V anglickej verzii:
DSUM (A1: G16, F1, I1: K2)
AKO TO FUNGUJE:
Z databázy, ktorú sme určili A1: G16 funkcia BDSUMM získava a sumarizuje údaje zo stĺpca čiastka (argument) Lúka" = F1) podľa uvedeného v bunkách I1: K2 (Predávajúci \u003d Ivanov; Výrobky \u003d Ceruzky; Mesiac \u003d január) kritériá.
MÍNUSY: Zoznam kritérií by mal byť na hárku.
POZNÁMKY: Počet súčtových kritérií je obmedzený RAM.
OBLASŤ APLIKÁCIE: Akákoľvek verzia programu Excel
Metóda 2:
SUMPRODUCT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
V anglickej verzii:
SUMPRODUCT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
AKO TO FUNGUJE:
Funkcia SUMPRODUCT vytvára v pamäti programu Excel polia hodnôt TRUE a FALSE podľa vybraných kritérií.
Ak by sa výpočty uskutočňovali v bunkách hárku (kvôli prehľadnosti ukážem celú prácu vzorca, akoby sa výpočty uskutočňovali na hárku, a nie v pamäti), potom by polia vyzerali takto:
Je zrejmé, že ak napríklad D2 \u003d ceruzky, potom bude hodnota TRUE, a ak D3 \u003d Priečinky, potom FALSE (pretože kritériom pre výber produktu v našom príklade je hodnota Céruzky).
S vedomím, že TRUE sa vždy rovná 1 a FALSE vždy 0, pokračujeme v práci s poľami ako s číslami 0 a 1.
Násobením získaných hodnôt polí navzájom postupne získame JEDNÉ pole núl a jednotiek. Ak boli splnené všetky tri výberové kritériá, ( IVANOV, CERUZKY, JANUÁR) t.j. všetky podmienky nadobudli hodnoty TRUE, dostaneme 1 (1 * 1 * 1 \u003d 1), ak nie je splnená aspoň jedna podmienka, dostaneme 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).
Teraz zostáva iba vynásobiť výsledné pole poľom obsahujúcim údaje, ktoré musíme vo výsledku zhrnúť (rozsah F2: F16) a v skutočnosti spočítajte, čo sa nevynásobí 0.
Teraz porovnajte polia získané pomocou vzorca a počas výpočtu krok za krokom na hárku (zvýraznené červenou farbou).
Myslím, že je všetko jasné :)
MÍNUSY: SUMPRODUCT - „ťažký“ vzorec poľa. Pri výpočte na veľkých rozsahoch údajov sa čas prepočtu citeľne zvyšuje.
POZNÁMKY
OBLASŤ APLIKÁCIE: Akákoľvek verzia programu Excel
Metóda 3: Pole vzorca
SUMA (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))
V anglickej verzii:
SUM (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16))
AKO TO FUNGUJE: Rovnakým spôsobom ako metóda č. 2. Existujú iba dva rozdiely - tento vzorec sa zadáva stlačením Ctrl + Shift + Enterskôr ako len stlačiť Zadajte a 0-té a 1-q pole sa nevynásobí sumačným rozsahom, ale sa vyberie pomocou funkcie IF.
MÍNUSY: Formáty poľa pri výpočte veľkých rozsahov údajov zreteľne predĺžia čas prepočtu.
POZNÁMKY: Počet spracovaných polí je obmedzený na 255.
OBLASŤ APLIKÁCIE: Akákoľvek verzia programu Excel
Metóda 4:
SUMIF (F2: F16; B2: B16; I2; D2: D16; J2; A2: A16; K2)