Excel-də bir sıra xanaların cəmi. Excel. Şərti formatlaşdırma meyarlarına cavab verən xanaları sayın və cəmləyin

Əvvəllər tapmaq üçün xüsusi funksiyadan necə istifadə edəcəyimi təsvir etmişdim. Təəssüf ki, hüceyrələr istifadə edərək rənglənirsə, bu funksiya işləmir şərti formatlaşdırma. Mən söz verdim ki, funksiyanı “sonlandıracağam”. Amma o qeydin dərcindən keçən iki il ərzində nə təkbaşına, nə də internetdən alınan məlumatların köməyi ilə həzm oluna bilən kod yaza bilməmişəm... ( 29 mart 2017-ci il tarixli yeniləmə Daha beş ildən sonra kodu yazmağı bacardım; qeydin son hissəsinə baxın). Və bu yaxınlarda D. Hawley, R. Hawley tərəfindən yazılmış "Excel 2007. Tricks" kitabında olan bir fikrə rast gəldim ki, bu da sizə ümumiyyətlə kodsuz işləməyə imkan verir.

A1:A100 diapazonunda yerləşdirilən 1-dən 100-ə qədər rəqəmlərin siyahısı olsun (şək. 1; Excel faylındakı “SUMIF” vərəqinə də baxın). Aralığın 10-dan çox və 20-dən kiçik və ya bərabər olan xanaları qeyd edən şərti formatlaşdırma var.

düyü. 1. Rəqəmlərin diapazonu; şərti formatlaşdırma 10-dan 20-yə qədər dəyərləri olan xanaları seçir

Qeydi formatda, nümunələri formatda yükləyin

İndi siz təyin etdiyiniz meyarlara cavab verən xanalara dəyərlər əlavə etməlisiniz. Bu xanalara hansı formatın tətbiq olunmasının əhəmiyyəti yoxdur, lakin siz hüceyrələrin hansı meyarlarla vurğulandığını bilməlisiniz.

Uyğun xanalar sırasını əlavə etmək üçün tək meyar üçün SUMIF funksiyasından istifadə edə bilərsiniz (şək. 2).

düyü. 2. Eyni şərtə cavab verən hüceyrələrin cəmi

Varsa bəzilərişərtlərdə SUMIFS funksiyasından istifadə edə bilərsiniz (Şəkil 3).

düyü. 3. Bir neçə şərtə cavab verən hüceyrələrin cəmi

Bir meyara cavab verən xanaların sayını hesablamaq üçün COUNTIF funksiyasından istifadə edə bilərsiniz.

Çoxsaylı meyarlara cavab verən xanaların sayını hesablamaq üçün COUNTIFS funksiyasından istifadə edə bilərsiniz.

Excel-də bir çox şərtləri təyin etməyə imkan verən başqa bir funksiya var. Bu funksiya verilənlər bazası funksiya dəstinə daxildir Excel məlumatları və BDSUMM adlanır. Bunu yoxlamaq üçün A2:A100 diapazonunda eyni ədədlər dəstindən istifadə edin (Şəkil 4; Excel faylında “BDSUMM” vərəqinə də baxın).

düyü. 4. Verilənlər bazası funksiyalarından istifadə

C1:D2 xanalarını seçin və diapazonu düstur çubuğunun sol tərəfindəki ad xanasına daxil edərək Kriteriya adlandırın. İndi C1 xanasını seçin və =$A$1 daxil edin, yəni verilənlər bazası adını ehtiva edən vərəqdəki ilk xanaya keçid. D1 xanasına =$A$1 daxil edin və siz A sütununun başlığının iki nüsxəsini əldə edəcəksiniz. Bu nüsxələr Meyar adlandırdığınız BDSUMM(C1:D2) şərtləri üçün başlıqlar kimi istifadə olunacaq. C2 xanasına >10 daxil edin. D2 xanasına daxil edin<=20. В ячейке, где должен быть результат, введите следующую формулу:

BDSUMM($A$1:$A$101,1,Meyarlar)

Çox meyarlara cavab verən xanaların sayını hesablamaq üçün COUNT funksiyasından istifadə edə bilərsiniz.

Con Uolkenbaxın kitabını oxuyarkən öyrəndim ki, Excel 2010-dan başlayaraq VBA-da yeni DisplayFormat xüsusiyyəti yaranıb (məsələn, Range.DisplayFormat Property-ə baxın). Yəni VBA ekranda göstərilən formatı oxuya bilir. Onun necə əldə edildiyi, birbaşa istifadəçi parametrləri və ya şərti formatlaşdırmadan istifadə edilməsinin əhəmiyyəti yoxdur. Təəssüf ki, MS tərtibatçıları bunu elə etdilər ki, DisplayFormat xüsusiyyəti yalnız VBA-dan çağırılan prosedurlarda işləyir və bu xassə əsasında istifadəçi tərəfindən müəyyən edilmiş funksiyalar #VALUE! Bununla birlikdə, bir prosedurdan (makros, funksiya deyil) istifadə edərək müəyyən rəngli hüceyrələr üçün diapazondakı dəyərlərin cəmini əldə edə bilərsiniz. Açıq (VBA kodu ehtiva edir). Menyudan keçin Baxın -> Makrolar -> Makrolar; pəncərədə Makro, xətti seçin SumColorUsl, və basın İcra etmək. Makronu işə salın, toplama diapazonunu və kriteriyasını seçin. Cavab pəncərədə görünəcək.

Prosedur kodu

Sub SumColorConv() Application.Volatile True Dim SumColor Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor = 0 " Range Sorunu Set UserRange = Application.InputBox(_ Prompt:="Cəmləşdirmə diapazonunu seçin", _ Başlıq : ="Range seçimi", _ Default:=ActiveCell.Address, _ Type:=8) " Kriteriya sorğusu Set CriterionRange = Application.InputBox(_ Prompt:="Seçin toplama meyarı", _ Başlıq:="Meyar seçimi", _ Default:=ActiveCell.Address, _ Type:=8) " UserRange-də Hər i Üçün "düzgün" xanaların cəmlənməsi Əgər i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat Interior.Color Sonra SumColor = SumColor + i End If Next MsgBox SumColor End Sub.

Ondan nə qədər olduğunu öyrənmək lazımdır qələmlər satış nümayəndəsi tərəfindən satılır İvanov V yanvar.

PROBLEM: Məlumatları bir neçə kriteriyaya görə necə ümumiləşdirmək olar?

HƏLL: Metod 1:

BDSUMM(A1:G16;F1;I1:K2)

DSUM(A1:G16,F1,I1:K2)

Göstərdiyimiz verilənlər bazasından A1: G16 funksiyası BDSUMM sütun məlumatlarını alır və ümumiləşdirir Kəmiyyət(arqument" Sahə" = F1) xanalarda göstərilənlərə uyğun olaraq I1:K2 (Satıcı = İvanov; Məhsullar = Qələmlər;Ay = Yanvar) meyarlar.

Eksiler: Meyarların siyahısı vərəqdə olmalıdır.

QEYDLƏR: Toplama meyarlarının sayı RAM ilə məhdudlaşır.

TƏTBİQ SAHƏSİ: Excel-in istənilən versiyası

Metod 2:

XÜSUSİYYƏT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

XÜSUSİYYƏT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

BU NECƏ İŞLƏYİR:

SUMPRODUCT funksiyası Excel yaddaşında seçilmiş meyarlara uyğun olaraq TRUE və FALSE dəyərlərindən ibarət massivlər əmələ gətirir.

Hesablamalar vərəq xanalarında aparılsaydı (aydınlıq üçün mən düsturun bütün işini hesablamalar yaddaşda deyil, vərəqdə baş verən kimi nümayiş etdirəcəyəm), massivlər belə görünəcək:

Aydındır ki, əgər məsələn, D2=Qələmlər, onda dəyər TRUE olacaq və əgər D3=Qovluqlar, sonra YANLIŞ (çünki nümunəmizdə məhsul seçmək meyarı dəyərdir Qələmlər).

TRUE dəyərinin həmişə 1-ə, FALSE isə həmişə 0-a bərabər olduğunu bilərək, 0 və 1 rəqəmlərində olduğu kimi massivlərlə işləməyə davam edirik.
Yaranan massiv dəyərlərini ardıcıl olaraq öz aralarına vurmaqla sıfır və birlərdən ibarət BİR massiv alırıq. Hər üç seçim meyarına cavab verildiyi halda, ( İVANOV, QƏRANDAŞ, YANVAR) yəni. bütün şərtlər TRUE qiymətini aldı, biz 1 (1*1*1 = 1) alırıq, lakin ən azı bir şərt yerinə yetirilmədikdə, biz 0 alırıq (1*1*0 = 0; 1*0*1 = 0; 0*1* 1 = 0).

İndi yalnız nəticədə əldə edilən massivi sonda toplamalı olduğumuz məlumatları ehtiva edən massivlə çoxaltmaq qalır (aralıq F2: F16) və əslində 0-a vurulmayanı yekunlaşdırın.

İndi düsturdan istifadə edərək əldə edilən massivləri vərəqdəki addım-addım hesablama ilə müqayisə edin (qırmızı rənglə vurğulanır).

Məncə hər şey aydındır :)

Minuslar: MƏHSUL - "ağır" massiv düsturu. Böyük məlumat diapazonlarında hesablamalar apararkən, yenidən hesablama vaxtı nəzərəçarpacaq dərəcədə artır.

QEYDLƏR

TƏTBİQ SAHƏSİ: Excel-in istənilən versiyası

Metod 3: Massiv düsturu

SUM(ƏGƏR((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

SUM(ƏGƏR((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

BU NECƏ İŞLƏYİR: 2 nömrəli üsulla tamamilə eynidir. Yalnız iki fərq var - bu formula basaraq daxil edilir Ctrl+Shift+Enter və yalnız basmaqla deyil Daxil edin və 0 və 1 massivi toplama diapazonuna vurulmur, lakin IF funksiyasından istifadə etməklə seçilir.

Minuslar: Böyük məlumat diapazonlarında hesablama zamanı massiv düsturları yenidən hesablama vaxtını nəzərəçarpacaq dərəcədə artırır.

QEYDLƏR: İşlənmiş massivlərin sayı 255 ilə məhdudlaşır.

TƏTBİQ SAHƏSİ: Excel-in istənilən versiyası

Metod 4:

SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)

Tez-tez olur ki, cədvəldə hər ikinci, üçüncü, dördüncü və s. xananı toplamaq lazımdır. İndi aşağıdakı hiylə sayəsində bunu etmək olar.

Excel-də hər birini cəmləyə bilən standart funksiya yoxdur n-ci hüceyrə və ya sim. Ancaq bu tapşırığı bir neçə ilə yerinə yetirə bilərsiniz fərqli yollar. Bütün bu yanaşmalar ROW və MOD funksiyalarına əsaslanır.

ROW funksiyası verilmiş xana arayışı üçün sıra nömrəsini qaytarır: ROW(istinad), Excel ROW-un rus versiyasında(link).
REST funksiyası (MOD)ədədin bölücüyə bölünməsinin qalığını qaytarır: MOD(nömrə;bölən), Excelin rus versiyasında QALIN(nömrə;bölən).

ROW funksiyasını MOD funksiyasının içərisinə yerləşdirin (rəqəm arqumentini ötürmək üçün), 2-yə bölün (hər digər xananı toplamaq üçün) və nəticənin sıfırdan fərqli olub olmadığını yoxlayın. Əgər belədirsə, xana cəmlənir. Bu funksiyalar müxtəlif yollarla istifadə edilə bilər - bəziləri digərlərindən daha yaxşı nəticələr verəcəkdir. Məsələn, $A$1:$A$100 diapazonundaki hər bir digər xananı toplamaq üçün massiv düsturu belə görünə bilər: =SUM(ƏGƏR(MOD(ROW($A$1:$A$500);2)=0;$ A$1: $A$500;0)), Excelin rus versiyasında =SUM(ƏGƏR(RESID(ROW($A$1:$A$500),2)=0;$A$1:$A$500,0) ) .

Bu massiv düsturu olduğundan, onu Ctrl+Shift+Enter düymələri ilə daxil etməlisiniz. )= 0;$A$1:$A$500;0))), Excelin rus versiyasında: (=SUM(IF(REMINAL(ROW($A$1:$A$500),2)=0;$A $1:$A $500;0))) Buruq mötərizələrin özünü əlavə etmək üçün Excel lazımdır; onları özünüz əlavə etsəniz, formula işləməyəcək.

Burada bir az olan başqa bir formula var ən yaxşı seçimdir: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)), Excelin rus versiyasında =SUMPRODUCT((REMINAL(ROW($) A$1 :$A$500);2)=0)*($A$1:$A$500)) .

Bununla belə, bu düsturun #VALUE xətası qaytaracağını unutmayın. (#VALUE!) diapazondakı hər hansı xana rəqəmlərdən çox mətndən ibarətdirsə. Bu düstur əslində massiv düsturu olmasa da, yavaşlayır Excel işi, onu çox dəfə istifadə edirsinizsə və ya hər dəfə geniş diapazona istinad edirsinizsə.

Xoşbəxtlikdən, var Ən yaxşı yol, bu, təkcə daha səmərəli deyil, həm də daha çevik bir həlldir. Bu, DSUM funksiyasının istifadəsini tələb edir. Bu nümunədə biz A1:A500 diapazonundan hər n-ci xananın cəmlənməsi üçün diapazon kimi istifadə etdik.

E1 xanasına Kriteriya sözünü daxil edin. E2 xanasına aşağıdakı düsturu daxil edin: =MOD(ROW(A2)-$C$2-1,$C$2)=0, Excelin rus versiyasında =RESIDENT(ROW(A2)-$C$2-1, $C$2) =0 . C2 xanasını seçin və Data → Validation əmrini seçin.

İcazə verin sahəsində Siyahı seçin və Mənbə sahəsinə 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 daxil edin. İcazə verilən dəyərlərin siyahısı qutusunun seçildiyinə əmin olun ( In-Cell) və OK düyməsini basın. C1 xanasına hər... SUM mətnini daxil edin. 1-ci sətirdən başqa istənilən xanada aşağıdakı düsturu daxil edin: =DSUM($A:$A;1,$E$1:$E$2) :$E$2) .

DSUM funksiyasını daxil etdiyiniz xananın birbaşa yuxarısındakı xanaya "Hər biri yekunlaşdırmaq" & $С$2 & CHOOSE($C$2;"st";"nd";"rd";"th";" mətnini daxil edin. th";"th";"th";"th";"th";"th") & "Hüceyrə" . İndi C2 xanasında istədiyiniz nömrəni seçmək qalır, qalanını isə DSUM funksiyası yerinə yetirəcək.

DSUM funksiyasından istifadə edərək siz müəyyən etdiyiniz intervalda xanaları cəmləyə bilərsiniz. DSUM funksiyası massiv düsturu və ya ilə müqayisədə daha səmərəlidir SUMPRODUCT funksiyası(MƏHSUL). Quraşdırma bir az daha uzun sürsə də, bu, öyrənmək çətin, mübarizə aparmaq asandır.