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 hər birini cəmləyə bilən standart funksiya təmin etmir n-ci hüceyrə və ya simli. Ancaq bu tapşırığı bir neçə fərqli yolla yerinə yetirə bilərsiniz. Bütün bu yanaşmalar ROW (ROW) və MOD (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(istinad).
MOD funksiyası (MOD)ədədi bölücüyə böldükdən sonra qalanı qaytarır: MOD(nömrə;bölən), Excelin rus versiyasında MOD(nömrə;bölən).
ROW funksiyasını MOD funksiyasına qoyun (rəqəmli arqumenti ötürmək üçün), 2-yə bölün (hər ikinci xananı toplamaq üçün) və nəticənin sıfır olub olmadığını yoxlayın. Əgər belədirsə, hüceyrə yekunlaşdırılır. 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: $500$A$;0))
Bu massiv düsturu olduğundan, onu Ctrl+Shift+Enter düymələri ilə daxil etməlisiniz, Excel bu kimi görünməsi üçün buruq mötərizələr əlavə edəcək: (=SUM(ƏGƏR(MOD(ROW($A$1:$A$500),) 2)= 0;$A$1:$A$500;0))) $500;0))) Buruq mötərizələr əlavə etmək üçün Excel lazımdır; onları özünüz əlavə etsəniz, formula işləməyəcək.
Məqsəd əldə edilsə də, bu üsul dizayna mənfi təsir göstərir. elektron cədvəl. Bu massiv formulunun lazımsız tətbiqidir. Vəziyyəti daha da pisləşdirmək üçün, bu uzun düsturun içərisinə daxil edilmiş yenidən hesablana bilən ROW funksiyası var ki, bu da daha böyük düsturu yenidən hesablana bilir. Bu o deməkdir ki, nə etdiyinizdən asılı olmayaraq daima yenidən hesablanacaq iş dəftəri. Bu çox pis yoldur!
Budur, bir az olan başqa bir düstur ən yaxşı seçimdir: =MƏHSUL((MOD(SIRA($A$1:$A$500);2)=0)*($A$1:$A$500)) :$A$500);2)=0)*($A$1 :$500$)) .
Bununla belə, bu düsturun #VALUE dəyərini qaytaracağını unutmayın! (#VALUE!) diapazondakı hər hansı xanada rəqəmlər əvəzinə mətn varsa. Bu düstur əslində massiv düsturu olmasa da, yavaşlayır Excel işiçox dəfə istifadə olunursa və ya hər dəfə böyük diapazona istinad edirsə.
Xoşbəxtlikdən var Ən yaxşı yol, bu nəinki daha səmərəli, həm də daha çevikdir. 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 =0 . C2 xanasını seçin və Məlumat → Doğrulama seçin.
Məlumat növü (İcazə verin) sahəsində Siyahı (Siyahı) seçin və Mənbə (Mənbə) sahəsinə 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 daxil edin. icazə verilən dəyərlər yoxlanılır (Hüceyrədə) 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 xanaya aşağıdakı düsturu daxil edin: =DSUM($A:$A;1;$E$1:$E$2) :$E$2) .
DSUM funksiyasını daxil etdiyiniz xananın dərhal yuxarısındakı xanaya "Hər biri yekunlaşdırmaq" & $C$2 & CHOOSE($C$2;"st";"nd";"rd";"th";" mətnini daxil edin. th";"th";"th";"th";"th";"th") & "Hüceyrə" . İndi yalnız 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 intervallarla xanaları cəmləyə bilərsiniz. DSUM funksiyası massiv formulundan və ya SUMPRODUCT funksiyasından qat-qat səmərəlidir. Quraşdırmaq bir az daha uzun sürsə də, bu, öyrənmək çətin, mübarizə aparmaq asan bir haldır.
Əvvəllər tapmaq üçün xüsusi funksiyadan necə istifadə edəcəyimi təsvir etdim. Təəssüf ki, hüceyrələr rəngləndikdə bu funksiya işləmir şərti formatlaşdırma. Mən söz verdim ki, funksiyanı “bitirəcəyəm”. 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ədim... ( Yeniləmə 29 mart 2017-ci il Daha beş ildən sonra kodu yazmağı bacardım; qeydin son hissəsinə baxın). Və bu yaxınlarda mən D. Holy, R. Holy-nin "Excel 2007. Tricks" kitabında yer alan bir fikrə rast gəldim ki, bu da ümumiyyətlə kod olmadan etmə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ının "SUMIS" vərəqinə də baxın). Bu diapazonda 10-dan çox və 20-dən kiçik və ya bərabər olan xanaları işarələyən şərti formatlama 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ı vurğulayır
Qeydi formatda, nümunələri formatda yükləyin
İndi yeni qurduğunuz 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 (Şəkil 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. Çoxlu Şərtlərə Uyğun Hüceyrələrin Cəmi
Eyni meyarlara 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 bir çox şərtləri təyin etməyə imkan verən başqa bir funksiya təqdim edir. Bu xüsusiyyət əsas funksiyalar dəstinin bir hissəsidir. 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ının "BDSUMM" vərəqinə də baxın).
düyü. 4. Verilənlər bazası funksiyalarından istifadə
C1:D2 xanalarını seçin və bu diapazonu düstur çubuğunun sol tərəfindəki ad xanasına yazaraq Kriteriya adlandırın. İndi C1 xanasını seçin və verilənlər bazasının adını ehtiva edən iş vərəqindəki ilk xanaya keçid olan =$A$1 daxil edin. 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 yazın<=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 yeni DisplayFormat xüsusiyyətini təqdim edir (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 (funksiya deyil, makro) istifadə edərək müəyyən bir rəngin xanaları üzərindəki 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 vurğulayın SumColorCond., və basın Qaç. Makronu işə salın, toplama diapazonunu və kriteriyasını seçin. Cavab pəncərədə görünəcək.
Prosedur kodu
Sub SumColorCond() Application.Volatile True Dim SumColor Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor = 0 " Range Sorğu Set UserRange = Application.InputBox(_ Prompt:="Cəmi Aralığı seçin", _ Başlıq: ="Range Selection", _ Default:=ActiveCell.Address, _ Type:=8) " Kriteriya Sorğu Seti CriterionRange = Application.InputBox(_ Prompt:="Seçin toplama meyarı", _ Title:="Kriteriya seçimi", _ Defolt:=ActiveCell.Address, _ Type:=8) " UserRange-də Hər i Üçün "düzgün" xanaları cəmləyin Əgər i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat İnterior.Rəng Sonra SumColor = SumColor + i Son Əgər Növbəti MsgBox SumColor End Sub
Sub SumColorCond() tətbiq. Uçucu Doğru Dim SumColor Cüt kimi Dim i As Range UserRange-i diapazon kimi azaldın Dim CriterionRange As Range SumColor = 0 "Mənzil sorğusu UserRange = Application.InputBox(_) təyin edin Prompt:="Cəmləmə aralığını seçin", _ Başlıq:="Aralıq seçimi", _ Defolt:=ActiveCell.Ünvan, _ Növ:=8) "Soruş meyarları CriterionRange = Tətbiq təyin edin. Giriş qutusu(_ Tələb:= "Cəmləşdirmə meyarını seçin", _ Başlıq : = "Meyar seçimi" , _ Defolt:= ActiveCell. Ünvan, _ |
Tutaq ki, satış nümayəndələrinin satışı ilə bağlı aşağıdakı hesabatınız var:
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 in yanvar.
PROBLEM: Məlumatları bir neçə meyarla necə ümumiləşdirmək olar?
QƏRAR: Metod 1:
BDSUMM(A1:G16;F1;I1:K2)
İngilis versiyası:
DSUM(A1:G16,F1,I1:K2)
BU NECƏ İŞLƏYİR:
Göstərdiyimiz verilənlər bazasından A1: G16 funksiyası BDSUMM sütun məlumatlarını alır və cəmləyir Kəmiyyət(arqument" Sahə" = F1) xanalarda verilənə 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)
İngilis versiyası:
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ərinin massivlərini yaradır.
Hesablamalar vərəqin xanalarında aparılsaydı (aydınlıq üçün mən düsturun bütün əməliyyatını hesablamalar yaddaşda deyil, vərəqdə olduğu 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əki məhsul seçim meyarı dəyərdir Qələmlər).
DOĞRU-nun həmişə 1-ə, YANLIŞ-ın isə 0-a bərabər olduğunu bilərək, 0 və 1 ədədləri kimi massivlərlə işləməyə davam edirik.
Massivlərin əldə edilmiş dəyərlərini ardıcıl olaraq bir-birinə vuraraq, 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, QARANDAŞLAR, YANVAR) yəni. bütün şərtlər DOĞRU idi, biz 1 alırıq (1*1*1 = 1), lakin ən azı bir şərt yerinə yetirilməsə, biz 0 alırıq (1*1*0 = 0 ; 1*0*1 = 0 ; 0* 1*1 = 0).
İndi yalnız nəticədə cəmləməmiz lazım olan məlumatları ehtiva edən massivlə çoxaltmaq qalır (aralıq F2: F16) və əslində 0-a vurulmayanı yekunlaşdırın.
İndi düsturun köməyi ilə əldə edilən massivləri vərəqdə 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))
İngilis versiyası:
SUM(ƏGƏR((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
BU NECƏ İŞLƏYİR: Eynilə 2 nömrəli üsul kimi. Yalnız iki fərq var - verilmiş formula basaraq daxil edilir Ctrl+Shift+Enter sadəcə basmaq əvəzinə 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)