Kuidas Excelis üksikud lahtrid kokku võtta. Excel. Loendage ja summeerige lahtrid, mis vastavad tingimusliku vormindamise kriteeriumidele
Sageli juhtub, et peate arvutustabeli iga teise, kolmanda, neljanda jne lahtri kokku võtma. Nüüd saab tänu järgmisele trikile hakkama.
Excel ei paku standardset funktsiooni, mis saaks igaühe summeerida n lahter või string. Kuid saate seda ülesannet täita mitmega erinevatel viisidel... Kõik need lähenemised põhinevad funktsioonidel ROW ja MOD.
Funktsioon ROW tagastab antud lahtriviite rea numbri: ROW (viide) Exceli ROW venekeelses versioonis (viide).
Funktsioon OSTAT (MOD) tagastab ülejäänud arvu jagamise jagajaga: MOD (number; jagaja) Exceli venekeelses versioonis OSTAT (number; jagaja).
Pange funktsioon ROW funktsiooni MOD (arvulise argumendi edastamiseks), jagage 2-ga (kõigi teiste lahtrite summeerimiseks) ja kontrollige, kas tulemus pole null. Kui jah, siis lahter liidetakse. Neid funktsioone saab kasutada väga erinevatel viisidel - mõned annavad paremaid tulemusi kui teised. Näiteks massiivi valem iga teise lahtri summeerimiseks vahemikus $ A $ 1: $ A $ 100 võib välja näha järgmine: \u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)), Exceli venekeelses versioonis \u003d SUM (IF (OSTAT (LINE ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ 500; 0)).
Kuna see on massiivivalem, peate selle sisestama, vajutades klahvikombinatsiooni Ctrl + Tõst + Enter, lisab Excel lokkis traksid nii, et see näeks välja järgmine: (\u003d SUM ), 2) \u003d 0; $ A $ 1: $ A $ 500; 0))), Exceli venekeelses versioonis: (\u003d SUM (IF (alles (ROW ($ A $ 1: $ A $ 500)); 2) \u003d 0; $ A $ 1: $ A $ 500; 0))) Teil on vaja Exceli, et lisada iseenesest lokkis traksid; kui lisate need ise, ei tööta valem.
Kuigi eesmärk on saavutatud, mõjutab see meetod disaini negatiivselt. arvutustabel... See on massiivi valemi tarbetu rakendamine. Asja teeb veelgi hullemaks see, et see pikk valem sisaldab ümberarvutatavat funktsiooni ROW, mis muudab suure valemi ka ümberarvutatavaks funktsiooniks. See tähendab, et seda arvutatakse pidevalt ümber, olenemata sellest, mida te töövihikus teete. See on väga halb viis!
Siin on veel üks valem, mis on veidi parim valik: \u003d SUMPRODUCT ((MOD (RIDA ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)), Exceli venekeelses versioonis \u003d SUMPRODUCT ((REST (RIDA ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)).
Pidage siiski meeles, et see valem tagastab väärtuse #VALUE! (#VALUE!) Kui vahemiku mõni lahter sisaldab numbrite asemel teksti. See valem, kuigi see pole tegelikult massiivvalem, aeglustub ka exceli töökui kasutate seda liiga palju kordi või kui see viitab iga kord suurele vahemikule.
Õnneks on parim viis, mis pole mitte ainult tõhusam, vaid ka palju paindlikum. See nõuab DSUM-funktsiooni kasutamist. Selles näites kasutasime vahemikku A1: A500 vahemikuna, millele tuleb lisada iga n-nda lahter.
Sisestage lahtrisse E1 sõna Kriteeriumid. Sisestage lahtrisse E2 järgmine valem: \u003d MOD (ROW (A2) - $ C $ 2-1; $ C $ 2) \u003d 0, Exceli venekeelses versioonis \u003d OSTAT (ROW (A2) - $ C $ 2- 1; $ C $ 2) \u003d 0. Valige lahter C2 ja valige käsk Andmed → Valideerimine.
Valige väljal Luba loend ja sisestage väljale Allikas 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Veenduge, et ruut oleks märgitud. (Lahtrisisene) ja klõpsake nuppu OK. Lahtrisse C1 sisestage tekst SUM iga…. Sisestage mis tahes lahtrisse peale 1. rea järgmine valem: \u003d DSUM ($ A: $ A; 1; $ E $ 1: $ E $ 2), Exceli venekeelses versioonis \u003d BDSUMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
Sisestage lahtrisse otse lahtrisse, kuhu sisestasite funktsiooni DSUM, tekst \u003d "Summing Every" & $ C $ 2 & CHOOSE ($ C $ 2; "st"; "nd"; "rd"; "th" ; "th"; "th"; "th"; "th"; "th"; "th") & "Cell". Nüüd jääb üle vaid soovitud number lahtrisse C2 valida ja ülejäänud funktsioon täidab funktsioon DSUM.
Funktsiooni BDSUM (DSUM) abil saate lahtrid kokku summutada teie määratud intervalliga. Funktsioon DSUM on palju tõhusam kui massiivvalem või funktsioon SUMPRODUCT. Ehkki seadistamine võtab veidi kauem aega, juhtub see siis, kui seda on raske õppida, on kerge võidelda.
Varem kirjeldasin, kuidas leida a. Kahjuks ei tööta see funktsioon, kui lahtrid on värvitud tingimuslik vormindamine... Lubasin funktsiooni "täpsustada". Kuid selle märkuse avaldamisest möödunud kahe aasta jooksul ei ole mul õnnestunud seedivat koodi kirjutada ei üksi ega Internetist pärineva teabe abil ... ( Värskendus 29. märtsil 2017 Veel viie aasta pärast jõudsin ikkagi koodi kirjutada; vt noodi viimast osa). Ja just hiljuti sattusin ideele, mis sisaldub D. Hawley, R. Hawley raamatus "Excel 2007. Tricks", mis võimaldab teil ilma koodita hakkama saada.
Olgu loetelu numbritest vahemikus 1 kuni 100, mis asuvad vahemikus A1: A100 (joonis 1; vt ka Exceli faili lehte "SUMIF"). Vahemik on tinglikult vormindatud, et tähistada lahtrid, mis sisaldavad arve, mis on suurem kui 10 ja väiksem kui 20.
Joonis: 1. Numbrivahemik; tingimuslikult vormistatud esiletõstetud lahtrid, mis sisaldavad väärtusi 10 kuni 20
Laadige märkus alla vormingus, näited vormingus
Nüüd peate lahtrites lisama väärtused, mis vastavad äsja seatud kriteeriumidele. Pole tähtis, millist vormingut neile rakkudele rakendatakse, kuid peate teadma kriteeriume, mille järgi lahtrid on esile tõstetud.
Lahtrite vahemiku liitmiseks, mis sobivad üks kriteeriumina saate kasutada funktsiooni SUMIF (joonis 2).
Joonis: 2. Ühele tingimusele vastavate lahtrite liitmine
Kui teil on mõned tingimustes saate kasutada funktsiooni SUMIFS (joonis 3).
Joonis: 3. Mitmele tingimusele vastavate lahtrite liitmine
Funktsiooni COUNTIF abil saate loendada ühele kriteeriumile vastavate lahtrite arvu.
Funktsiooni COUNTIF abil saate loendada mitmele kriteeriumile vastavate lahtrite arvu.
Excel pakub veel ühe funktsiooni, mis võimaldab teil määrata mitu tingimust. See funktsioon kuulub baaside funktsioonide hulka exceli andmed ja seda nimetatakse BDSUMM-iks. Selle kontrollimiseks kasutage sama numbrikomplekti vahemikus A2: A100 (joonis 4; vaadake ka Exceli faili lehte "BDSUMM").
Joonis: 4. Andmebaasi funktsioonide kasutamine
Valige lahtrid C1: D2 ja nimetage see vahemik Kriteeriumiks, tippides selle valemiribast vasakule asuvasse nimevälja. Nüüd valige lahter C1 ja sisestage \u003d $ A $ 1, mis on viide andmebaasi nime sisaldava lehe esimesele lahtrile. Lahtrisse D1 sisestage \u003d $ A $ 1 ja saate kaks eksemplari veeru pealkirjast A. Neid koopiaid kasutatakse tingimuste BDSUMM (C1: D2), mille nimetasite kriteeriumiks, päistena. Lahtrisse C2 sisestage\u003e 10. Lahtrisse D2 sisestage<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($ A $ 1: $ A $ 101,1, kriteerium)
Funktsiooni COUNT abil saate arvutada mitmele kriteeriumile vastavate lahtrite arvu.
John Walkenbachi raamatut lugedes sain teada, et alates Excel 2010-st on VBA-l uus atribuut DisplayFormat (vt näiteks Range.DisplayFormat Property). See tähendab, et VBA suudab ekraanil kuvatavat vormingut lugeda. Pole tähtis, kuidas see kasutaja otsesätetes või tingimusvormingu abil vastu võeti. Kahjuks on MS teinud nii, et atribuut DisplayFormat töötab ainult protseduurides, mida kutsutakse VBA-st, ja sellel atribuudil põhinevad UDF-id viskavad väärtuse #VALUE! Siiski saate protseduuri (makro, kuid mitte funktsiooni) abil saada vahemikus olevate väärtuste summa teatud värvi lahtrite kaupa. Ava (sisaldab VBA koodi). Minge läbi menüü Vaade -> Makrod -> Makrod; aknas Makro, tõstke joon esile SumColorUslja vajutage Täitke... Käivitage makro, valige liitmisvahemik ja kriteerium. Vastus ilmub aknasse.
Menetluse kood
Sub SumColorConv () Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor \u003d 0 "Vahemiku päring Set UserRange \u003d Application.InputBox (_ Prompt: \u003d" Valige summeerimisvahemik ", _ Pealkiri: \u003d "Vahemiku valik", _ Vaikimisi: \u003d ActiveCell.Aadress, _ Tüüp: \u003d 8) "Kriteeriumitaotluse komplekt CriterionRange \u003d Application.InputBox (_ Prompt: \u003d" Vali liitmise kriteerium", _ Pealkiri: \u003d" Valige kriteerium ", _ Vaikimisi: \u003d ActiveCell.Address, _ Tüüp: \u003d 8)" "Õige" lahtrite kokkuvõte igale i-le UserRange'is i .DisplayFormat. Interior.Color Siis SumColor \u003d SumColor + i lõpp, kui järgmine MsgBox SumColor End Sub
AlamvärvUl () Rakendus. Lenduv Tõsi Dim SumColor kahekordne Dim i As Range Hämar UserRange As Range Hämar kriteeriumRange as Range SumColor \u003d 0 "Vahemiku päring Määra UserRange \u003d Application.InputBox (_ Viip: \u003d "Valige summeerimisvahemik", _ Pealkiri: \u003d "Vahemiku valik", _ Vaikimisi: \u003d ActiveCell. Aadress, _ Tüüp: \u003d 8) "Prokurör Set CriterionRange \u003d Rakendus. InputBox (_ Viip: \u003d "Valige liitmise kriteerium", _ Pealkiri: \u003d "Kriteeriumi valik", _ Vaikimisi: \u003d ActiveCell. Aadress, _ |
Oletame, et teil on selline müügiaruanne:
Sellest peate välja selgitama, kui palju pliiatsid müüb müügiesindaja Ivanov aastal jaanuar.
PROBLEEM: Kuidas andmeid mitme kriteeriumi järgi kokku võtta ??
OTSUS: 1. meetod:
BDSUMM (A1: G16; F1; I1: K2)
Eestikeelses versioonis:
DSUM (A1: G16, F1, I1: K2)
KUIDAS SEE TÖÖTAB:
Meie täpsustatud andmebaasist A1: G16 funktsioon BDSUMM otsib ja võtab kokku veergude andmed summa (argument " Väli" = F1) vastavalt lahtrites antud andmetele I1: K2 (Müüja \u003d Ivanov; Tooted \u003d pliiatsid; Kuu \u003d jaanuar) kriteeriumid.
Miinused: kriteeriumide loetelu peaks olema lehel.
MÄRKUSED: Summeerimiskriteeriumite arv on piiratud RAM-iga.
TAOTLUSPIIRKOND: Mis tahes Exceli versioon
2. meetod:
ALUMPRODUKT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
Eestikeelses versioonis:
ALUMPRODUKT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
KUIDAS SEE TÖÖTAB:
Funktsioon SUMPRODUCT moodustab Exceli mällu vastavalt valitud kriteeriumidele TRUE ja FALSE väärtuste massiivid.
Kui arvutused tehti lehe lahtrites (selguse huvides näitan kogu valemi tööd nii, nagu toimuksid arvutused lehel, mitte mälus), siis näeksid massiivid välja sellised:
On ilmne, et kui näiteks D2 \u003d pliiatsid, siis on väärtus TÕENE ja kui D3 \u003d kaustad, siis FALSE (kuna meie näites toote valimise kriteeriumiks on väärtus Pliiatsid).
Teades, et TRUE on alati võrdne 1-ga ja FALSE on alati võrdne 0-ga, jätkame tööd massiividega nagu numbritega 0 ja 1.
Korrutades saadud massiivide väärtused järjestikku, saame ÜHE nullide ja üksuste massiivi. Kui kõik kolm valikukriteeriumi olid täidetud, ( IVANOV, Pliiatsid, JAANUAR) st. kõik tingimused omandasid väärtused TÕENE, saame 1 (1 * 1 * 1 \u003d 1), kuid kui vähemalt üks tingimus ei ole täidetud, saame 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).
Nüüd jääb ainult saadud massiivi korrutamine massiiviga, mis sisaldab andmeid, mille peame selle tulemusena kokku võtma (vahemik F2: F16) ja tegelikult kokku see, mida ei korruta 0-ga.
Nüüd võrrelge valemi abil saadud massiive ja lehel samm-sammult arvutades (punase värviga esile tõstetud).
Ma arvan, et kõik on selge :)
Miinused: SUMPRODUCT - "raske" massiivi valem. Suurte andmevahemike arvutamisel pikeneb ümberarvutamise aeg märgatavalt.
MÄRKUSED
TAOTLUSPIIRKOND: Mis tahes Exceli versioon
3. meetod: massiivi valem
SUMMA (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))
Eestikeelses versioonis:
SUMMA (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16))
KUIDAS SEE TÖÖTAB: Samamoodi nagu meetodil nr 2. Erinevusi on ainult kaks - see valem sisestatakse vajutades Ctrl + Tõst + Entermitte lihtsalt vajutades Sisenema ja massiive 0-nda ja 1-q ei korrutata summeerimisvahemikuga, vaid see valitakse funktsiooni IF abil.
Miinused: Massiivivalemid suurte andmevahemike arvutamisel pikendavad märkimisväärselt ümberarvutamise aega.
MÄRKUSED: Töödeldud massiivide arv on piiratud 255-ga.
TAOTLUSPIIRKOND: Mis tahes Exceli versioon
4. meetod:
SUMIF (F2: F16; B2: B16; I2; D2: D16; J2; A2: A16; K2)