Pieņemsim, ka jums ir šāds pārskats par tirdzniecības pārstāvju pārdošanu:
No tā jums jānoskaidro, cik daudz zīmuļi pārdod tirdzniecības pārstāvis Ivanovs V janvārī.
PROBLĒMA: Kā apkopot datus pēc vairākiem kritērijiem?
RISINĀJUMS: 1. metode:
BDSUMM(A1:G16;F1;I1:K2)
Angļu valodā:
DSUM(A1:G16,F1,I1:K2)
KĀ TAS STRĀDĀ:
No mūsu norādītās datu bāzes A1:G16 funkciju BDSUMM izgūst un summē kolonnas datus Daudzums(arguments " Lauks" = F1) atbilstoši šūnās dotajam I1:K2 (Pārdevējs = Ivanovs; Produkti = Zīmuļi;Mēnesis = janvāris) kritērijiem.
Mīnusi: kritēriju sarakstam jābūt uz lapas.
PIEZĪMES: Summēšanas kritēriju skaitu ierobežo RAM.
LIETOŠANAS JOMA: jebkura Excel versija
2. metode:
SUMPRODUKTS((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
Angļu valodā:
SUMPRODUKTS((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
KĀ TAS STRĀDĀ:
Funkcija SUMPRODUCT ģenerē TRUE un FALSE vērtību masīvus Excel atmiņā atbilstoši atlasītajiem kritērijiem.
Ja aprēķini tiktu veikti lapas šūnās (skaidrības labad es demonstrēšu visu formulas darbību tā, it kā aprēķini notiktu uz lapas, nevis atmiņā), tad masīvi izskatītos šādi:
Ir skaidrs, ka, ja piem. D2 = Zīmuļi, tad vērtība būs TRUE, un ja D3=Mapes, tad FALSE (jo mūsu piemērā produkta izvēles kritērijs ir vērtība Zīmuļi).
Zinot, ka TRUE vienmēr ir vienāds ar 1 un FALSE vienmēr ir vienāds ar 0, mēs turpinām strādāt ar masīviem tāpat kā ar skaitļiem 0 un 1.
Secīgi reizinot iegūtās masīvu vērtības savā starpā, mēs iegūstam VIENU nulles un vieninieku masīvu. Ja bija izpildīti visi trīs atlases kritēriji, ( IVANOVS, ZĪMUĻI, JANVĀRIS) t.i. visi nosacījumi bija PATIESI, mēs iegūstam 1 (1*1*1 = 1), bet, ja nav izpildīts vismaz viens nosacījums, iegūstam 0 (1*1*0 = 0; 1*0*1 = 0; 0*1*1 = 0).
Tagad atliek tikai reizināt iegūto masīvu ar masīvu, kurā ir dati, kas mums ir jāapkopo kā rezultātā (diapazons F2:F16) un faktiski summējiet to, kas nav reizināts ar 0.
Tagad salīdziniet masīvus, kas iegūti ar formulas palīdzību un ar soli pa solim aprēķinu uz lapas (izcelts sarkanā krāsā).
Man liekas, ka viss skaidrs :)
MINUSI: SUMPRODUKTS - "smagā" masīva formula. Aprēķinot lielos datu diapazonos, pārrēķina laiks ievērojami palielinās.
PIEZĪMES
LIETOŠANAS JOMA: jebkura Excel versija
3. metode: masīva formula
SUMMA(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
Angļu valodā:
SUMMA(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
KĀ TAS STRĀDĀ: Tāpat kā 2. metode. Ir tikai divas atšķirības – šī formula tiek ievadīta, nospiežot Ctrl+Shift+Enter nevis vienkārši nospiežot Ievadiet un 0. un 1. masīvs netiek reizināts ar summēšanas diapazons, un tiek atlasīts, izmantojot funkciju IF.
MINUSI: Masīvu formulas, aprēķinot lielos datu diapazonos, ievērojami palielina pārrēķina laiku.
PIEZĪMES: apstrādāto masīvu skaits ir ierobežots līdz 255.
LIETOŠANAS JOMA: jebkura Excel versija
4. metode:
SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)
Iepriekš es aprakstīju, kā izmantot pielāgotu funkciju, lai atrastu . Diemžēl šī funkcija nedarbojas, ja šūnas ir krāsotas ar nosacījumformatēšana. Es apsolīju "pabeigt" funkciju. Bet divu gadu laikā, kas pagājuši kopš šīs piezīmes publicēšanas, es neesmu varējis uzrakstīt sagremojamu kodu ne pats, ne ar interneta informācijas palīdzību ... ( Atjaunināts 2017. gada 29. martā Vēl pēc pieciem gadiem man tomēr izdevās uzrakstīt kodu; skatīt piezīmes beigu daļu). Un pavisam nesen uzgāju ideju, kas ietverta D. Holy grāmatā R. Holy "Excel 2007. Tricks", kas ļauj iztikt vispār bez koda.
Lai ir skaitļu saraksts no 1 līdz 100, kas novietoti diapazonā A1: A100 (1. att.; sk. arī Excel faila lapu "SUMIS"). Diapazonam ir nosacīts formatējums, kas atzīmē šūnas, kurās ir skaitļi, kas ir lielāki par 10 un mazāki vai vienādi ar 20.
Rīsi. 1. Skaitļu diapazons; nosacījumformatēšana izceļ šūnas, kurās ir vērtības no 10 līdz 20
Lejupielādējiet piezīmi formātā, piemērus formātā
Tagad šūnās jāpievieno vērtības, kas atbilst tikko noteiktajiem kritērijiem. Nav svarīgi, kāds formatējums tiek lietots šīm šūnām, taču ir jāzina kritēriji, pēc kuriem šūnas tiek izceltas.
Lai pievienotu šūnu diapazonu, kas atbilst vienatnē kritēriju, varat izmantot funkciju SUMIF (2. att.).
Rīsi. 2. Šūnu summēšana, kas atbilst vienam un tam pašam nosacījumam
Ja Jums ir daži nosacījumus, varat izmantot SUMIFS funkcija(3. att.).
Rīsi. 3. Vairākiem nosacījumiem atbilstošo šūnu summēšana
Varat izmantot funkciju COUNTIF, lai saskaitītu to šūnu skaitu, kas atbilst tiem pašiem kritērijiem.
Varat izmantot funkciju COUNTIFS, lai saskaitītu to šūnu skaitu, kas atbilst vairākiem kritērijiem.
Excel nodrošina vēl vienu funkciju, kas ļauj norādīt vairākus nosacījumus. Šī funkcija ir daļa no pamata funkciju kopas. Excel dati un to sauc par BDSUMM. Lai to pārbaudītu, izmantojiet to pašu skaitļu kopu diapazonā A2:A100 (4. att.; skatiet arī Excel faila lapu "BDSUMM").
Rīsi. 4. Izmantojot datu bāzes funkcijas
Atlasiet šūnas C1:D2 un piešķiriet šim diapazonam nosaukumu Kritēriji, ierakstot to nosaukuma lodziņā pa kreisi no formulas joslas. Tagad atlasiet šūnu C1 un ievadiet =$A$1, kas ir saite uz pirmo šūnu darblapā, kurā ir datu bāzes nosaukums. Ievadiet =$A$1 šūnā D1, un jūs iegūsit divas kolonnas A virsraksta kopijas. Šīs kopijas tiks izmantotas kā virsraksti BDSUMM (C1:D2) nosacījumiem, kurus nosaucāt par kritērijiem. Šūnā C2 ievadiet >10. Šūnā D2 ierakstiet<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($A$1:$A$101,1, kritēriji)
Varat izmantot funkciju COUNT, lai saskaitītu to šūnu skaitu, kas atbilst vairākiem kritērijiem.
Lasot Džona Volkenbaha grāmatu, uzzināju, ka, sākot ar Excel 2010, VBA ieviesa jaunu DisplayFormat rekvizītu (skatiet, piemēram, Range.DisplayFormat Property). Tas nozīmē, ka VBA var nolasīt ekrānā redzamo formātu. Nav svarīgi, kā tas tika iegūts, izmantojot tiešos lietotāja iestatījumus vai nosacīto formatējumu. Diemžēl MS izstrādātāji ir panākuši, ka rekvizīts DisplayFormat darbojas tikai procedūrās, kas izsauktas no VBA, un lietotāja definētas funkcijas, kuru pamatā ir šis rekvizīts, ģenerē #VALUE! Tomēr jūs varat iegūt vērtību summu diapazonā pa noteiktas krāsas šūnām, izmantojot procedūru (makro, nevis funkciju). Atvērts (satur VBA kodu). Iet cauri izvēlnei Skatīt -> Makro -> Makro; logā Makro, iezīmējiet līniju SumColorCond. un nospiediet Skrien. Palaidiet makro, atlasiet summēšanas diapazonu un kritēriju. Atbilde parādīsies logā.
Procedūras kodekss
Sub SumColorCond() Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor = 0 " Diapazona vaicājums Set UserRange = Application.InputBox(_ Prompt:="Select range",ell:Active=S,el. _ Tips:=8) " Kritērija vaicājums Set CriterionRange = Application.InputBox(_ Prompt:="Atlasīt summēšanas kritērijs", _ Title:="Kritēriju atlase", _ Default:=ActiveCell.Address, _ Type:=8) " Summējiet "pareizās" šūnas katram i lietotāja diapazonā If i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat.Interior.Color =B Apakšsumma BeigasColors +
Sub SumColorCond() pieteikumu. Nepastāvīgs True Dim SumColor kā dubultā Dim i As Range Dim UserRange kā diapazons Dim CriterionRange As Range SumColor = 0 "Diapazona pieprasījums Set UserRange = Application.InputBox(_ Prompt:="Izvēlieties summēšanas diapazonu", _ Title:="Diapazona atlase", _ Noklusējums:=ActiveCell.Address, _ Tips:=8) "Pieprasījuma kritēriji Iestatīt CriterionRange = Lietojumprogramma . InputBox(_ Uzvedne := "Atlasīt summēšanas kritēriju", _ Title : = "Kritēriju atlase" , _ Noklusējums := ActiveCell . Adrese , _ |
Bieži gadās, ka izklājlapā ir jāsaskaita katra otrā, trešā, ceturtā utt. Tagad, pateicoties sekojošam trikam, to var izdarīt.
Excel nenodrošina standarta funkciju, kas varētu summēt katru n-tā šūna vai virkne. Tomēr jūs varat paveikt šo uzdevumu ar vairākiem Dažādi ceļi. Visas šīs pieejas ir balstītas uz ROW (ROW) un MOD (MOD) funkcijām.
ROW funkcija atgriež rindas numuru dotajai šūnas atsaucei: ROW(atsauce), Excel krievu valodā ROW(atsauce).
MOD funkcija (MOD) atgriež atlikumu pēc skaitļa dalīšanas ar dalītāju: MOD(skaitlis;dalītājs), Excel krievu versijā MOD(skaitlis;dalītājs).
Ievietojiet funkciju ROW funkcijā MOD (lai nodotu skaitlisku argumentu), daliet ar 2 (lai summētu katru otro šūnu) un pārbaudiet, vai rezultāts nav nulle. Ja tā, šūna tiek summēta. Šīs funkcijas var izmantot ļoti dažādos veidos – dažas no tām nodrošinās labākus rezultātus nekā citas. Piemēram, masīva formula katras otrās šūnas summēšanai diapazonā $A$1:$A$100 varētu izskatīties šādi: =SUM(IF(MOD(ROW($A$1:$A$500);2)=0;$A$1:$A$500;0)) 0)) .
Tā kā šī ir masīva formula, jums tā jāievada, nospiežot taustiņu kombināciju Ctrl+Shift+Enter, programma Excel pievienos krokainas figūriekavas, lai tā izskatītos šādi: (=SUM(IF(MOD(ROW($A$1:$A$500),2)=0;$A$1:$A$500;0))) 1:$A$500;0))) Excel ir jāpievieno iekava; ja pievienosit tos pats, formula nedarbosies.
Lai gan mērķis ir sasniegts, šī metode negatīvi ietekmē dizainu. izklājlapa. Tas ir nevajadzīgs masīva formulas pielietojums. Lai padarītu situāciju vēl ļaunāku, šai garajai formulai ir ligzdota pārrēķināma ROW funkcija, kas padara arī lielāko formulu pārrēķināmu. Tas nozīmē, ka tas tiks pastāvīgi pārrēķināts neatkarīgi no tā, ko jūs darāt darbgrāmatā. Tas ir ļoti slikts veids!
Šeit ir vēl viena formula, kas ir nedaudz labākā izvēle: =SUMPRODUKTS((MOD(RINDA($A$1:$A$500);2)=0)*($1:$A$500))
Tomēr ņemiet vērā, ka šī formula atgriezīs #VALUE! (#VALUE!), ja kādā diapazona šūnā ir teksts, nevis skaitļi. Šī formula, lai gan patiesībā nav masīva formula, arī palēninās Excel darbs ja tas tiek izmantots pārāk daudz reižu vai ja tas katru reizi attiecas uz lielu diapazonu.
Par laimi ir Labākais veids, kas ir ne tikai efektīvāka, bet arī daudz elastīgāka. Tam ir jāizmanto DSUM funkcija. Šajā piemērā mēs esam izmantojuši diapazonu A1:A500 kā diapazonu, kurā summēt katru n-to šūnu.
Šūnā E1 ievadiet vārdu Kritēriji. Šūnā E2 ievadiet šādu formulu: =MOD(ROW(A2)-$C$2-1,$C$2)=0 Atlasiet šūnu C2 un atlasiet Dati → Validācija.
Laukā Datu tips (Atļaut) atlasiet Saraksts (Saraksts) un laukā Avots (Avots) ievadiet 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Pārliecinieties, vai ir atlasīts lodziņš List. atļautās vērtības(Šūnā) un noklikšķiniet uz pogas Labi. Šūnā C1 ievadiet tekstu SUM every…. Jebkurā šūnā, izņemot 1. rindu, ievadiet šādu formulu: =DSUM($A:$A;1;$E$1:$E$2)
Šūnā, kas atrodas tieši virs tās, kurā ievadījāt funkciju DSUM, ievadiet tekstu ="Summing Every" & $C$2 & CHOOSE($C$2;"st";"nd";"rd";"th";"th";"th";"th";"th";"th";"th") & "Cell" . Tagad atliek tikai atlasīt vajadzīgo numuru šūnā C2, bet pārējo paveiks DSUM funkcija.
Izmantojot funkciju DSUM, varat summēt šūnas jūsu norādītajos intervālos. Funkcija DSUM ir daudz efektīvāka nekā masīva formula vai funkcija SUMPRODUCT. Lai gan iestatīšana prasa nedaudz ilgāku laiku, šis ir gadījums, kad grūti iemācīties, viegli cīnīties.
