1. Meklēt pēc tagiem

Iedomājieties tabulu, kurā nodaļu (vai kontu vai citu) nosaukumi ir uzskaitīti rindās pēc kārtas.

Apkopojiet šūnas pēc kritērija
Ir nepieciešams aprēķināt kopējo summu katram departamentam. Daudzi to dara ar filtru un raksta ar pildspalvām šūnās.
Lai gan to var izdarīt vienkārši un vienkārši ar vienu funkciju - SUMMESLI .
SUMMESLES (SUMIFES) (SUMIFES) - apkopo šūnas, kas atbilst noteiktam stāvoklim (var norādīt tikai vienu nosacījumu). Šo funkciju var izmantot arī tad, ja tabula ir sadalīta kolonnās pa periodiem (katru mēnesi, katru mēnesi, trīs slejas - ienākumi | izdevumi | starpība) un jums ir nepieciešams aprēķināt kopējo summu par visiem periodiem tikai ar ienākumiem, izdevumiem un starpību.

Kopumā SUMMESLI: Range , Criterion , Range_Summing ir trīs argumenti.
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Diapazons (A1: A20000) - norāda diapazonu ar kritērijiem. Ti Kolonna, kurā jāmeklē vērtība, kas norādīta kritērija kritērijā .
• Kritērijs (A1) ir vērtība (teksts vai skaitlis, kā arī datums), kas jāatrod diapazonā . Var saturēt aizstājējzīmes ar "*" un "?". Ti norādot kritēriju "* masa *", lai apkopotu vērtības, kurās notiek vārds "masa". Tajā pašā laikā vārds “masa” var rasties jebkurā vietā tekstā, vai šūnā var būt tikai viens vārds. Un norādot "masu *", visas vērtības, kas sākas ar "masu", tiks apkopotas. "?" - aizstāj tikai vienu rakstzīmi, t.i. norādot "mas? a", varat apkopot līnijas ar vērtību "masa" un vērtību "maska" utt.
  Ja kritērijs ir ierakstīts šūnā un jums joprojām ir jāizmanto aizstājējzīmes, varat pievienot saiti uz šo šūnu, pievienojot vajadzīgo. Pieņemsim, ka jums ir jāapkopo vērtības, kas satur vārdu "kopā". Vārds "kopējais" ir ierakstīts A1 šūnā, savukārt A kolonnā var būt dažādas pareizrakstības vērtības, kas satur vārdu "kopā": "kopsummas jūnijam", "kopsummas par jūliju", "kopsummas par martu". Formula pēc tam izskatās šādi:
  = SUMMERS (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - & zīme (ampersand) apvieno vairākas vērtības vienā. Ti rezultāts būs "* rezultāts *".
  Lai labāk izprastu principu, kā formulas darbojas, labāk ir izmantot Aprēķināt formulas rīku: Kā apskatīt soļus, lai aprēķinātu formulas
  Visi teksta kritēriji un kritēriji ar loģiskām un matemātiskām zīmēm jāiekļauj dubultās pēdiņās (= SUMMESLI (A1: A20000; "kopā"; B1: B20000)). Ja kritērijs ir skaitlis, citāti nav nepieciešami. Ja vēlaties tieši atrast jautājumu zīmi vai zvaigznīti, priekšā ir jāievieto tilde (~).
  Par tildi un tās funkcijām var atrast šajā rakstā: Kā nomainīt / noņemt / atrast zvaigznīti?
• Sum_Range (B1: B20000) (izvēles arguments) - norāda summējamo summu vai skaitlisko vērtību diapazonu.

Kā tas darbojas: funkcija meklē diapazonu vērtībai, kas norādīta kritērija argumentā, un, kad tiek atrasts atbilstība, tiek summēti dati, kas norādīti argumentā Range_Amount. Ti ja A ailē ir nodaļas nosaukums un B ailē norādītā summa, tad, norādot Attīstības departamentu kā kritēriju, tiks iegūta visu B slejas vērtību summa, kas pretējā pusē atrodas Attīstības departamentā A slejā. Faktiski SumArrangement var nebūt tāds pats kā Range argumentam, un tas neradīs pašas funkcijas kļūdu. Tomēr, definējot šūnas summēšanai, Range_Amount argumenta augšējā kreisā šūna tiks izmantota kā sākuma šūna summēšanai, un tad šūnas, kas atbilst izmēram un formai diapazona argumentam, tiks summētas.

Dažas funkcijas
Funkcijas pēdējais arguments (Sum_And_Band: B1: B20000) nav obligāts. Tas nozīmē, ka to nevar noteikt. Ja jūs to nenorādīsiet, funkcija papildinās vērtības, kas norādītas diapazona argumentā. Kas tas ir. Piemēram, jums ir jāsaņem summa tikai tiem skaitļiem, kas ir lielāki par nulli. Summas A slejā. Tad funkcija izskatīsies šādi:
= SUMMERS (A1: A20000; "> 0")

Kas jāņem vērā: diapazons_summing un diapazonam jābūt vienādam ar līniju skaitu. Pretējā gadījumā jūs varat iegūt nepareizu rezultātu. Optimāli, ja tas izskatīsies manis sniegtajās formulās: summāciju diapazons un diapazons sākas no vienas līnijas un ir vienāds līniju skaits: A1: A20000; B1: B20000

Summēšana ar diviem vai vairākiem kritērijiem
Bet ko darīt, ja kritēriji 2 un vairāk sumēšanai? Pieņemsim, ka jums ir jāapkopo tikai tās summas, kas pieder vienai nodaļai un tikai noteiktam datumam. Laimīgie biroja versiju 2007 un jaunāki īpašnieki var izmantot funkciju SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Pirmais arguments norāda šūnu diapazonu, kurā ir summas, kas tiks savāktas vienā.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Norāda šūnu diapazonu, kurā vēlaties meklēt atbilstību pēc kritērija.
$ I $ 3, $ H8 - kritērijs. Šeit, tāpat kā SUMMESLI, ir atļauts izmantot aizstājējzīmes . un viņi strādā vienādi.

Argumentu precizēšanas specifika: vispirms ir norādīts kritēriju diapazons (tie ir numurēti), tad vērtība (kritērijs) ir norādīta tieši semikolonā, kas šajā diapazonā ir atrodams - $ A $ 2: $ A $ 50; $ I $ 3. Un nekas cits. Jums nevajadzētu mēģināt vispirms norādīt visus diapazonus un pēc tam kritērijus tiem - funkcija vai nu sniegs kļūdu, vai arī nesummēs, kas ir nepieciešams.

Visi nosacījumi tiek salīdzināti saskaņā ar I principu. Tas nozīmē, ka, ja ir izpildīti visi uzskaitītie nosacījumi. Ja vismaz viens nosacījums nav izpildīts, funkcija izlaiž līniju un nepievieno neko.
Runājot par SUMMERS, summēšanas un kritēriju diapazoniem jābūt vienādiem ar rindu skaitu.

Tāpēc, ka SUMMESLIMN parādījās tikai Excel versijās, sākot no 2007. gada, tad kā iepriekšminēto versiju neapmierināti lietotāji šādos gadījumos var būt? Ļoti vienkāršs: izmantojiet citu funkciju - SUMPRODUCT. Es nespēšu argumentus, jo Ir daudzi no tiem, un tie ir visu vērtību kopumi. Šī funkcija reizina ar argumentiem norādītos blokus. Es centīšos aprakstīt vispārējo principu par šīs funkcijas izmantošanu, lai apkopotu datus par vairākiem nosacījumiem.
Lai atrisinātu summēšanas problēmu ar vairākiem kritērijiem, funkcija izskatīsies šādi:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ 50 - datumu diapazons. $ I $ 3 ir kritērija datums, kuram nepieciešams apkopot datus.
$ B $ 2: $ B $ 50 - departamentu nosaukumi. H5 - departamenta nosaukums, dati, kas jāapkopo.
$ C $ 2: $ C $ 50 - diapazons ar summām.

Mēs analizējam loģiku, jo daudziem, tas būs pilnīgi neskaidrs, tikai aplūkojot šo funkciju. Ja tikai tāpēc, ka šī lietojumprogramma nav aprakstīta. Lai uzlabotu lasāmību, samaziniet diapazonu izmērus:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Tātad izteiksme ($ A $ 2: $ A $ 5 = $ I $ 3) un ($ B $ 2: $ B $ 5 = H5) ir loģiskas un atgriezeniskas loģiskās FALSE un TRUE masīvi. TRUE, ja šūnas ar diapazonu $ A $ 2: $ A $ 5 ir vienādas ar šūnas $ I $ 3 vērtību un šūnu diapazonā no $ B $ 2: $ B $ 5 ir vienāda ar šūnas H5 vērtību. Ti mums ir:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Kā redzat, pirmajā masīvā ir divi atbilstības nosacījumi, bet otrajā. Turklāt šie divi bloki tiek reizināti (par to atbild reizināšanas zīme (*)). Kad notiek reizināšana, notiek FALSE un TRUE masīvu netiešā konversija uz ciparu konstantēm 0 un 1, attiecīgi ({0; 1; 1; 0} * {0; 0; 1; 0}). Kā jūs zināt, reizinot ar nulli, mēs iegūstam nulli. Un rezultāts ir viens masīvs:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ 50)
Tad masīvs {0; 0; 1; 0} tiek reizināts ar skaitļu virkni diapazonā no $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Tā rezultātā mēs saņemam 30. Ko mums vajadzēja - mēs saņemam tikai to summu, kas atbilst kritērijam. Ja ir vairāk nekā viena summa, kas atbilst kritērijam, tad tie tiks apkopoti.

SUMMYROIZV priekšrocības
Ja argumentiem ir reizinājuma zīmes vietā pluss zīme:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
tad nosacījumi tiks salīdzināti saskaņā ar OR principu: t.i. kopējās summas tiks summētas, ja ir izpildīts vismaz viens nosacījums: vai nu $ A $ 2: $ A $ 5 ir vienāda ar šūnu vērtību $ I $ 3 vai diapazona $ B $ 2 šūnu: $ B $ 5 ir vienāda ar šūnu vērtību H5.
Tā ir SUMMPRODUCT priekšrocība, izmantojot SUMMESLIMN. SUMMESLIMN nevar summēt vērtības saskaņā ar OR principu, tikai saskaņā ar UN principu (ir jāizpilda visi nosacījumi).

Trūkumi
SUMPRODUCT nevar izmantot aizstājējzīmes * un ?. Precīzāk var izmantot, bet tie netiks uztverti kā speciālas rakstzīmes, bet kā zvaigznīte un jautājuma zīme. Es domāju, ka tas ir ievērojams trūkums. Un, lai gan to var apiet, es izmantoju citas funkcijas SUMPRODUCT ietvaros - tas joprojām būtu lieliski, ja funkcija varētu kaut kā izmantot aizstājējzīmes.

Šajā piemērā jūs atradīsiet pāris piemēru piemērus, lai labāk izprastu iepriekš aprakstīto.

Lejupielādēt piemēru

Summa pēc vairākiem kritērijiem (41,5 KiB, 10 477 lejupielādes)

Skatiet arī:
Šūnu summēšana pēc aizpildījuma krāsas
Šūnu summēšana pēc fonta krāsas
Šūnu summēšana pa šūnu formātu
Aprēķiniet šūnu daudzumu, aizpildot krāsu
Aprēķiniet šūnu daudzumu pēc fontu krāsas
Kā apkopot datus no vairākām loksnēm, ieskaitot nosacījumus

{"Bottom bar": {"textstyle": "static", "textpositionstatic": "bottom", "textautohide": taisnība, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" pa kreisi "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "labi", "texteffectslidedistance1": 120, "texteffecteasing1": "easyOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easyOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" displejs: bloks; polsterējums: 12px; teksta izlīdzināšana: pa kreisi; "," textbgcss ":" displejs: bloks; pozīcija: absolūts; augšā: 0px; pa kreisi: 0px; platums: 100%; augstums: 100% fona krāsa: # 333333, dūmainība: 0,6, filtrs: a lpha (dūmainība = 60); "," titlecss ":" displejs: bloks; pozīcija: relatīvais; fonts: bold 14px "Lucida Sans Unicode", "Lucida Grande", sans-serifs, Arial; krāsa: #fff; "," descriptioncss ":" displejs: bloks; pozīcija: relatīvais; fonts: 12px "Lucida Sans Unicode", "Lucida Grande", sans-serifs, Arial; krāsa: #fff; margin-top: 8px; "," buttoncss ":" displejs: bloks; pozīcija: relatīvais; margin-top: 8px; "," texteffectresponsive ": taisnība," texteffectresponsivesize ": 640," titlecssresponsive ":" fontu izmērs: 12px; "," descriptioncssresponsive ":" displejs: nav: svarīgs; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Meklēt pēc tagiem