1. Ieškoti pagal žymes

Įsivaizduokite lentelę, kurioje sekcijų eilutėse yra išvardyti departamentų (ar sąskaitų ar kitų) pavadinimai.

Sumažinkite ląsteles pagal kriterijų
Būtina apskaičiuoti bendrą kiekvienos departamento sumą. Daugelis tai daro su filtru ir rašydami rašiklius ląstelėse.
Nors tai galima lengvai ir paprastai atlikti tik su viena funkcija - SUMMESLI .
SUMMESLES (SUMIF) - susideda iš ląstelių, kurios atitinka tam tikrą sąlygą (galima nurodyti tik vieną sąlygą). Ši funkcija taip pat gali būti naudojama, jei lentelė yra suskirstyta į stulpelius pagal laikotarpius (kas mėnesį, kiekvieną mėnesį, tris stulpelius - pajamų | išlaidų | skirtumą) ir reikia apskaičiuoti bendrą sumą visais laikotarpiais tik pagal pajamas, išlaidas ir skirtumus.

Iš viso yra trys argumentai SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Diapazonas (A1: A20000) - nurodo intervalą su kriterijais. Ty Stulpelis, kuriame ieškoma vertės, nurodytos kriterijų argumente.
• Kriterijus (A1) yra vertė (tekstas arba skaitmeninis, taip pat data), kuri turi būti nustatyta diapazone . Gali būti pakaitos simbolių „*“ ir „?“. Ty nurodant kriterijų "* masė *", kad apibendrintumėte vertes, kuriose atsiranda žodis "masė". Tuo pačiu metu žodis „masė“ gali atsirasti bet kurioje teksto vietoje, arba ląstelėje gali būti tik vienas žodis. Nurodant „masę *“, visos vertės, pradedant nuo „masė“, bus apibendrintos. "?" - pakeičia tik vieną simbolį, t. nurodydami „mas? a“, galite apibendrinti eilutes, kurių vertė yra „masė“, ir vertė „kaukė“ ir tt
  Jei kriterijus yra parašytas ląstelėje ir vis tiek turite naudoti pakaitos simbolius, galite pridėti nuorodą į šį langelį pridėdami reikiamą. Tarkime, jums reikia apibendrinti vertes, kuriose yra žodis „iš viso“. Žodis „visas“ yra parašytas ląstelėje A1, o A stulpelyje gali būti įvairių rašybos reikšmių, kuriose yra žodis „iš viso“: „birželio mėnesio sumos“, „liepos mėnesio sumos“, „kovo mėn. Tada formulė turėtų atrodyti taip:
  = SUMMERS (A1: A20000; "*" ir A1 & "*"; B1: B20000)
  "*" Ir A1 & "*" - & ženklas (ampersand) sujungia kelias vertybes į vieną. Ty rezultatas yra „* rezultatas *“.
  Siekiant geriau suprasti, kaip veikia formulės, geriau naudoti „ Apskaičiuoti formulę“ įrankį: Kaip peržiūrėti formulių skaičiavimo veiksmus
  Visi tekstiniai kriterijai ir kriterijai su loginiais ir matematiniais ženklais turi būti pateikiami dvigubomis kabutėmis (= SUMMESLI (A1: A20000; „total“; B1: B20000)). Jei kriterijus yra skaičius, kabučių nereikia. Jei norite tiesiogiai rasti klaustuką arba žvaigždutę, priešais jį reikia įdėti tildę (~).
  Apie „tilde“ ir jos funkcijas galima rasti šiame straipsnyje: Kaip pakeisti / pašalinti / rasti žvaigždutę?
• Sum_Range (B1: B20000) (pasirinktinis argumentas) - nurodo sumų arba skaičių reikšmių, kuriuos reikia susumuoti, diapazoną.

Kaip tai veikia: funkcija ieško intervalo pagal vertę, nurodytą kriterijų argumente, ir kai randamas atitikimas, sumos duomenis, nurodytus argumento „Range_Amount“. Ty jei A skiltyje yra departamento pavadinimas ir B skiltyje nurodyta suma, tada nurodant plėtros departamentą kaip kriterijų, bus gauta visų B stulpelio verčių suma, priešingai nei A skiltyje. Iš tiesų, SumArrangement gali būti ne tokio paties dydžio, kaip ir „Range“ argumentas, ir tai nesukels pačios funkcijos klaidos. Tačiau, apibrėžiant ląsteles apibendrinti, viršutinė kairioji „Range_Amount“ elemento ląstelė bus naudojama kaip pradinis elementas apibendrinimui, o tada ląstelės, atitinkančios dydį ir formą pagal intervalo argumentą, bus suvestinės.

Kai kurios funkcijos
Paskutinis funkcijos argumentas (Sum_And_Band: B1: B20000) yra neprivalomas. Tai reiškia, kad ji negali būti nurodyta. Jei to nenurodysite, funkcija prideda reikšmes, nurodytas intervalo argumente. Kas tai yra. Pavyzdžiui, reikia gauti tik tų skaičių, kurie yra didesni už nulį, sumą. A stulpelyje sumos. Tada ši funkcija atrodys taip:
= SUMMERS (A1: A20000; "> 0")

Ką reikėtų apsvarstyti: interval_summing ir diapazonas turi būti lygus linijų skaičiui. Priešingu atveju galite gauti neteisingą rezultatą. Optimaliai, jei jis atrodys pagal pateiktas formules: suvestinių diapazonas ir diapazonas prasideda nuo vienos linijos ir turi tokį patį eilių skaičių: A1: A20000; B1: B20000

Sumažinimas per du ar daugiau kriterijų
Bet ką daryti, kai 2 ir daugiau sumavimo kriterijai? Tarkime, jums reikia apibendrinti tik tas sumas, kurios priklauso vienai tarnybai ir tik tam tikrą datą. Laimingi biurų versijų 2007 ir naujesnės versijos savininkai gali naudotis SUMMESLIMN funkcija:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Pirmasis argumentas nurodo ląstelių diapazoną, kuriame yra sumų, kurios bus surenkamos į vieną.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Nurodo ląstelių diapazoną, kuriame norite ieškoti atitikties pagal kriterijų.
$ I $ 3, $ H8 - kriterijus. Čia, kaip ir SUMMESLI, leidžiama naudoti pakaitos simbolius * ir ? jie dirba taip pat.

Konkrečių argumentų nustatymas: pirma, nurodomas kriterijų diapazonas (jie yra sunumeruoti), tada vertė (kriterijus) nurodoma tiesiogiai kabliataškiu, kuris šiame intervale turi būti rastas - $ A $ 2: $ A $ 50; $ I $ 3. Ir nieko daugiau. Jūs neturėtumėte pabandyti pirmiausia nurodyti visų diapazonų, o tada jų kriterijų. Funkcija arba klaidą, nei apibendrins, kas yra būtina.

Visos sąlygos yra lyginamos pagal I principą. Tai reiškia, kad jei visos išvardytos sąlygos yra įvykdytos. Jei bent viena sąlyga nėra įvykdyta, funkcija praleidžia liniją ir nieko neprideda.
Kalbant apie SUMMERS, sumavimo ir kriterijų intervalai turėtų būti lygūs eilių skaičiui.

Nes „SUMMESLIMN“ pasirodė tik „Excel“ versijose, nuo 2007 m., Kaip tokiais atvejais gali būti nepatenkinti ankstesnių versijų vartotojai? Labai paprasta: naudokite kitą funkciją - SUMPRODUCT. Aš negalvosiu argumentų, nes Yra daug jų, ir jie visi yra vertybių masyvai. Ši funkcija daugina masyvus, nurodytus argumentais. Bandysiu apibūdinti bendrą principą, kaip šią funkciją naudoti apibendrinant duomenis keliomis sąlygomis.
Norėdami išspręsti sumavimo problemą keliais kriterijais, ši funkcija atrodys taip:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A - $ diapazonas. $ I $ 3 yra tos kriterijaus data, kuriai reikia sumuoti duomenis.
$ B $ 2: $ B $ 50 - departamentų pavadinimai. H5 - departamento pavadinimas, kurio duomenys turi būti apibendrinti.
$ C $ 2: $ C $ 50 - diapazonas su sumomis.

Mes analizuojame logiką, nes daugeliui, tai bus visiškai neaišku tik žiūrint į šią funkciją. Jei tik dėl to, kad ši programa nėra aprašyta. Norėdami geriau skaityti, sumažinkite diapazonų dydį:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Taigi, išraiška ($ A $ 2: $ A $ 5 = $ I $ 3) ir ($ B $ 2: $ B $ 5 = H5) yra loginės FALSE ir TRUE loginės ir grąžinimo matricos. TRUE, jei $ A $ 2 $ $ ląstelė yra lygi ląstelės $ I $ 3 ir $ B $ 2: $ 5 ląstelės vertei lygi ląstelės H5 vertei. Ty mes turime:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Kaip matote, pirmojoje matricoje yra dvi atitikties sąlygos ir antrajame. Be to, šios dvi matricos dauginamos (už tai atsakingas dauginimo ženklas (*)). Kai daugėja, įvyksta netiesioginė masyvų FALSE ir TRUE konversija į skaitmenines konstantas 0 ir 1, atitinkamai ({0; 1; 1; 0} * {0; 0; 1; 0}). Kaip žinote, padauginus iš nulio, mes gauname nulį. Ir rezultatas yra vienas masyvas:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ 50)
Tada masyvas {0; 0; 1; 0} padauginamas iš skaičiaus, esančio diapazone $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Todėl mes gauname 30. Ką mums reikia - mes gauname tik tą sumą, kuri atitinka kriterijų. Jei yra daugiau nei viena suma, atitinkanti kriterijų, tada jie bus apibendrinti.

SUMMYROIZV privalumas
Jei argumentai turi pliuso ženklą vietoj dauginimo ženklo:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
tada sąlygos bus lyginamos pagal OR principą: t. bendros sumos bus susumuojamos, jei bus įvykdyta bent viena sąlyga: $ A $ 2: $ A $ 5 yra lygi $ I $ 3 arba $ B $ 2 ląstelė: $ B $ 5 yra lygus ląstelės vertei H5.
Tai yra privalumas „SUMMPRODUCT“ per SUMMESLIMN. SUMMESLIMN negali apibendrinti vertybių pagal AR principą, tik pagal IR principą (turi būti įvykdytos visos sąlygos).

Trūkumai
SUMPRODUCT negali naudoti pakaitos * ir? Galima naudoti tiksliau, bet jie nebus suvokiami kaip specialūs simboliai, bet kaip žvaigždutė ir klaustukas. Manau, kad tai yra didelis trūkumas. Ir nors tai galima apeiti, naudoju kitas funkcijas SUMPRODUCT viduje - vis tiek būtų puiku, jei funkcija galėtų kažkaip naudoti pakaitos simbolius.

Pavyzdyje rasite keletą funkcijų pavyzdžių, kad geriau suprastumėte tai, kas parašyta aukščiau.

Atsisiųskite pavyzdį

Suma pagal kelis kriterijus (41,5 KiB, 10 477 atsisiuntimai)

Taip pat žiūrėkite:
Sudedant langelius užpildymo spalva
Ląstelių sumavimas pagal šrifto spalvą
Ląstelių apibendrinimas pagal ląstelių formatą
Apskaičiuokite ląstelių kiekį užpildymo spalva
Apskaičiuokite ląstelių kiekį pagal šrifto spalvą
Kaip apibendrinti duomenis iš kelių lapų, įskaitant būklę

{"Bottom bar": {"textstyle": "static", "textpositionstatic": "apačioje", "textautohide": tiesa, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" skaidrė "," texteffecteasing ":" easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" kairė "," teksto efektaiprieinamumas " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "teisė", "texteffectslidedistance1": 120, "texteffecteasing1": "easyOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "dešinė", "texteffectslidedistance2": 120, "texteffecteasing2": "easyOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" ekranas: blokas; pamušalas: 12px; teksto lygiavimas: kairėje; "," textbgcss ":" ekranas: blokas; padėtis: absoliutus; viršutinis: 0px; kairėje: 0px; plotis: 100%; aukštis: 100% fono spalva: # 333333, neskaidrumas: 0,6, filtras: a lpha (skaidrumas = 60); "," titlecss ":" ekranas: blokas; pozicija: santykinis; šriftas: paryškintas 14px "Lucida Sans Unicode", "Lucida Grande", sans-serifas, Arialas; spalva: #fff; "," descriptioncss ":" ekranas: blokas; pozicija: santykinis; šriftas: 12px „Lucida Sans Unicode“, „Lucida Grande“, sans-serifas, Arialas; spalva: #fff; margin-top: 8px; "," buttoncss ":" ekranas: blokas; pozicija: santykinis; margin-top: 8px; "," texteffectresponsive ": tiesa," texteffectresponsivesize ": 640," titlecssresponsive ":" šrifto dydis: 12px; "," descriptioncssresponsive ":" ekranas: nėra: svarbu; "," buttoncssresponsive ": "", "addgooglefonts": klaidingas, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Ieškoti pagal žymes