Şərt üzrə Excel yekunu. İki meyarla seçmə yekunlaşdırma

Günortanız Xeyir

Excel-də müxtəlif funksiyaları və nəzərdən keçirdiyimiz növbəti funksiyanı təsvir etmək istəyimi davam etdirəcəyəm. Bu, toplama funksiyalarının başqa bir nümayəndəsidir, lakin özünəməxsus şərtləri ilə. Excel-də BSUM funksiyası müəyyən etdiyiniz meyarlara uyğun olaraq cədvəlinizdə nömrələri axtarır, bu onun əsas xüsusiyyətidir.

Ürək üzərində deyə bilərəm ki, bir çox hesablamalar və hesablamalar onsuz da müxtəlif , və ya SUMPRODUCT istifadə etməklə edilə bilər, lakin "wildcards" istifadə edərək kompleks seçim etmək lazımdırsa, onda mütləq məqaləmizin qəhrəmanından istifadə etməlisiniz. .

Əvvəlcə, istifadə edən sintaksisə baxaq:

=BDSUMM(verilənlər bazanız diapazonu; axtarış sahəsi; axtarış termini), Harada

ilə işləyərkən BDSUMM funksiyasıİşləyərkən diqqət etməli olduğunuz bir neçə şərti qeyd etmək lazımdır:

• Məlumatlarla doldurulmuş bütöv bir sütun üzərində işləyərkən, göstərilən meyarlar diapazonunda sütun başlıqlarının altına mütləq boş sətir daxil etməlisiniz;
• Kriteriyalar diapazonu siyahı ilə üst-üstə düşməməlidir;
• Vərəqin hər hansı bir yerində bir sıra şərtlər yaratmaq üçün məlumatları yerləşdirə bilsəniz də, buna baxmayaraq, onu siyahıdan sonra yerləşdirməməlisiniz, çünki siyahıya əlavə etdiyimiz məlumatlar siyahıdan sonra birinci sətirə daxil ediləcək və əgər sətirdə məlumatlar var, sonra yenilərini əlavə etmək işləməyəcək;
• Siz ən azı bir başlıq və başlığın altında yerləşən və şərti ehtiva edən xanalardan ən azı birini ehtiva edən şərtlər üçün istənilən diapazondan istifadə edə bilərsiniz.

Beləliklə, nəzəri hissəni tamamlanmış hesab edirəm, indi praktik tətbiqə keçək BDSUMM funksiyaları işimizdə bunun üçün həyata keçirmək üçün bir neçə nümunə nəzərdən keçirəcəyik, mən prinsipə uyğun nümunələr etdim, lakin meyar üzrə dəyərlər toplamaq əvəzinə, toplama olacaq:

BDSUMM funksiyası iləbir ədədi meyar

Beləliklə, başlamaq üçün bir ədədi meyarla sadə bir nümunəni nəzərdən keçirək, bunun üçün "Vəhsul" sütununu seçirik və "\u003e\u003e 10" məhsuldarlığı olan ağaclara ehtiyacımız olduğunu göstəririk. Nəticə əldə etmək üçün bizə bu cür düstur lazımdır (sizə mütləq istinadlardan istifadə etməyi məsləhət görürəm):

=BDSUMM($B$6:$G$12;E6;E2:E3),

burada, $B$6:$G$12 cəm edəcəyimiz diapazon, E6 cəm edəcəyimiz sütun və E2:E3 toplama meyarlarını daxil etdiyimiz diapazondur. Nəticədə düstur cəmi 34 mövqe üçün 3 mövqe tapdı.

Bənzər bir nəticə əldə etmək üçün aşağıdakı düsturlardan da istifadə edə bilərsiniz:

=SUMIF(E7:E12,">=10")

=SUMIF(E7:E12,E3)

Bir mətn kriteriyası ilə BDSUMM funksiyası

İndi gəlin BDSUMM funksiyasının mətn kriteriyaları ilə necə davrandığına baxaq, ümumiyyətlə, hər şey əvvəlki nümunədəki kimi qalır, yalnız mətn kriteriyasının göstərildiyi və yalnız bu formada göstərildiyi halda: = "= s. Serovo" və sonra nəticə əldə edəcəksiniz, əks halda düstur meyarlarınızı tanıya bilməyəcək. İndi bu kriteriyanı düsturda əvəz edirik və alırıq:

=BDSUMM($B$6:$G$12,E6,C2:C3), gördüyümüz kimi, yalnız kriteriyanın diapazonu dəyişmişdir.

Bənzər bir nəticə əldə etmək üçün sizə lazım olacaq:

=SUMIF(C7:C12,"Serovo",E7:E12)

Müxtəlif sütunlarda iki kriteriya ilə cəmləyin

Nümunə iki meyarın istifadəsi ilə mürəkkəbdir, lakin biz prinsipial olaraq yeni bir şey istifadə etməyəcəyik, "Serovo kəndi" mətn meyarını və ">=10" ədədi meyarını göstərəcəyik, "Bitki" toplama sahəsini tərk edəcəyik, biz nəticədə yalnız sonuncu arqumentlə düsturda dəyişiklik əldə edəcək. İndi formulamız belə görünəcək:

=BDSUMM($B$6:$G$12;E6;C2:E3), yenidən yalnız meyar aralığının ünvanında dəyişikliklər görürsünüz.

Bunun köməyi ilə alternativ bir seçim əldə edilə bilər:

=SUMIFS(E7:E12,C7:C12,C3,E7:E12,E3)

=SUMIFS(E7:E12,C7:C12,"Serovo",E7:E12,">=10")

Bir sütunda iki şərtdən biri ilə yekunlaşdırma

Tətbiq edildiyi kimi başqa bir variantı nəzərdən keçirin, lakin indi biz bir meyardan deyil, ikiqat meyardan istifadə edəcəyik, ancaq bir sahə üçün. İki meyarın fərqli sətirlərdə olmasına əmin olun. Düsturun mahiyyəti ondan ibarətdir ki, o, kriteriyaların hər birini ayrıca hesablayaraq eyni diapazondan iki dəfə keçir. Bu nümunə üçün düstur belə görünəcək:

=BDSUMM($B$6:$G$12,E6,C2:C4), burada yenə meyarın diapazonunu dəyişirik, lakin enində deyil, hündürlüyündə.

Əvəzedici olaraq SUMIF funksiyasının cəmindən də istifadə edə bilərsiniz:

=SUMIF(C7:C12,C3,E7:E12)+SUMIF(C7:C12,C4,E7:E12).

İki fərqli sütunda iki şərtdən biri ilə yekunlaşdırma

Tətbiqin beşinci nümunəsi iki şərtdən birinin iki fərqli sütunda cəmlənməsi olacaq və arqumentlər hamısı yeni sətirlərdə yerləşdirilməlidir, bu onların növbə ilə cəmlənməsinə imkan verəcəkdir. Bu məqsədlər üçün bizə bir düstur lazımdır:

=BDSUMM($B$6:$G$12,D6,C2:D4), üç sətirdən ibarət meyar diapazonu istisna olmaqla, formulun formalaşması prinsipi qorunur: başlıq və iki meyar.

İki sütunla iki mətn meyarı üzrə cəmləyin

IN bu misalİşdə iki sütunda iki meyar olduqda, lakin ədədi və mətn meyarları olduqda əvvəllər nəzərdən keçirilən nümunənin demək olar ki, tam analoqunu nəzərdən keçirəcəyik və burada iki mətn meyarına və iki sütuna görə yekunlaşdırmanı nəzərdən keçirəcəyik. . Biz meyarlar diapazonunda göstərəcəyimiz "="=s.Ivanovo"" və "="=Albalı"" meyarlarından istifadə edirik. Beləliklə, formulamız belə görünəcək:

=BDSUMM($B$6:$G$12,D6,B2:C3).

Seçim və ümumiləşdirmə meyarlarını əldə etmək üçün düsturun nəticəsini istifadə etmək

BDSUMM funksiyasının bu nümunəsində mən seçim və toplama meyarlarını müəyyən etmək üçün bir düsturdan istifadə edirəm, bu halda hansı ağacların bizə gəlir gətirdiyini tapacağıq və şərti arqumenti müəyyən etmək üçün meyvə satışı üçün orta dəyəri götürəcəyik. ağaclar və bizi maraqlandıran hər şey orta səviyyədən yuxarıdır. DOĞRU meyarımıza çevriləcək orta dəyəri müəyyən etmək üçün düstur yaradırıq:

=G7>ORTA($G$7:$G$12), diapazonu düzəltməyi unutmayın ki, düsturla dəyərlər üzərində təkrarlanan zaman onlar aşağı sürüşməsin, lakin G7 dəyəri onun “YANLIŞ” olub olmadığını müəyyən etmək üçün bütün diapazonda sürüşməlidir. və ya “DOĞRU”.

Başlıq adlarının təkrarlanmaması da çox vacibdir, onların fərqi var, ona görə də meyarlar sahəsinə “Orta” adını verəcəyəm. Və sonra düstur işləməyə başlayacaq, o, orta dəyərin olması üçün bütün $G$7:$G$12 diapazonundan keçəcək və "DOĞRU" müsbət nəticə əldə edildikdə, o, yekunlaşdıracaq. Aşağıdakı formula işi yerinə yetirəcək:

=BDSUMM($B$6:$G$12;G6;$G$2:$G$3)

Əgər sualın alternativ həlli ilə çox maraqlanırsınızsa, bu formada SUMIF funksiyası ilə seçimi sınayın:

=SUMIF($G$7:$G$12;">"&ORTA($G$7:$G$12))

BDSUMM üç meyarla işləyir

Bu misalda biz iki kənddə yetişdirilən meyvələrin orta satışını hesablayırıq: “İvanovo kəndi” və “Uyutnoe kəndi”. Mən artıq seçimin əsas ideyasını meyarlara görə təsvir etmişəm, ona görə də təkrarlamayacağam, sadəcə deyəcəyəm ki, bu, əvvəllər nəzərdən keçirilən meyarların birləşməsi olacaq. Nəticəni əldə etmək üçün bu formada bizə lazımdır:

=BDSUMM($B$6:$G$12;G6;$C$2:$G$4)

Mətn kriteriyalarına görə cəmi

Daha əvvəl qeyd etdiyim kimi, o, yalnız joker işarələrlə deyil, həm də hərflərin işi ilə bağlı axtarış edə bilər və indi bu, məhz belə bir seçimdir. Başlamaq üçün, meyarın seçilmə şərtini müəyyənləşdiririk, əgər "kənd İVANOVO" adı böyük hərflərlə rast gəlinirsə, onda toplama aparırıq, bu meyarı müəyyən etmək üçün bizə bir düstur lazımdır:

=DƏMİ("İvanovo kəndi";C7)

İndi biz diapazonu göstərilən meyarın olub-olmadığını yoxlayacaq və "TRUE" dəyərini aldıqdan sonra yekunlaşdıracaq bir funksiya yaza bilərik. Nümunədə şərtə uyğun olaraq bir dəfə xüsusi qeyd etdim və gördüyünüz kimi düstur uğurla bütün yaşayış məntəqələrini seçdi və düzgün olanı tapdı və "9" nəticəsini aldı. Bunun üçün formula istifadə edilmişdir:

=BDSUMM($B$6:$G$12;D6;$C$2:$C$3)

Düşünürəm ki, təfərrüatı və bir çox təfərrüatları təsvir etdim, buna görə suallar az olacaq, amma faydaları çox olacaq. Hər hansı bir sualınız varsa şərhlərdə yazın, bəyənmələrinizi və rəylərinizi gözləyirəm. Digər funksiyaları veb saytımda tapa bilərsiniz.

Biznesinizdə uğurlar!

“Pul üçün, əlbəttə, almaq olar cazibədar it, amma heç bir pul onu xoşbəxtliklə quyruğunu bulaya bilməz.
"
D. Billinqs

Funksiya SUMIF göstərilən şərtə cavab verən diapazon dəyərlərini cəmləmək lazım olduqda istifadə olunur. Tutaq ki, məsələn, rəqəmlər sütununda yalnız 5-dən böyük dəyərləri toplamaq istəyirsiniz. Bunu etmək üçün aşağıdakı düsturdan istifadə edə bilərsiniz: =SUMIF(B2:B25,">5")

Bu video Excel 2013-də Nömrələr əlavə etmək təlimatının bir hissəsidir.

məsləhət:

Sintaksis

SUMIF(aralıq, şərt, [cəm_aralığı])

Funksiya Arqumentləri SUMIF aşağıda təsvir edilmişdir.

Aralığı. Tələb olunan arqument. Şərtlərə görə qiymətləndirilir hüceyrələrin diapazonu. Hər diapazondakı xanalarda nömrələr, adlar, massivlər və ya rəqəmlərə istinadlar olmalıdır. Boş xanalar və mətn dəyərləri olan xanalar atlanır. Seçilmiş diapazonda standart tarixlər ola bilər excel formatı(aşağıdakı nümunələrə baxın).

Vəziyyət. Tələb olunan arqument. Hansı xanaların cəmlənməsini təyin edən rəqəm, ifadə, xana istinadı, mətn və ya funksiya şəklində şərt. Məsələn, şərt aşağıdakı kimi təqdim edilə bilər: 32, ">32", B5, "32", "almalar" və ya TODAY().

Əhəmiyyətli: Bütün mətn şərtləri və şərtləri məntiqi və riyazi əlamətlər qoşa dırnaq içərisində olmalıdır ( " ). Şərt rəqəmdirsə, dırnaq işarələrindən istifadə etmək lazım deyil.

cəm_aralığı. Könüllü arqument. Göstərilən xanalardan fərqli olduqda dəyərləri cəmlənən hüceyrələr diapazon . Əgər mübahisə cəm_aralığı buraxılmış, Excel arqumentdə göstərilən xanaları cəmləyir diapazon (şərtin tətbiq olunduğu eyni hüceyrələr).

Mübahisədə vəziyyət joker işarələrdən istifadə edə bilərsiniz: sual işarəsi ( ? ) və ulduz ( * ). Sual işarəsi hər hansı bir simvola, ulduz isə hər hansı simvol ardıcıllığına uyğun gəlir. Birbaşa sual işarəsini (və ya ulduz işarəsi) axtarmaq istəyirsinizsə, ondan əvvəl tilde işarəsi ( ~ ).

Qeydlər

SUMIF funksiyası 255 simvoldan uzun sətirləri uyğunlaşdırmaq üçün istifadə edilərsə və ya #VALUE! sətrinə tətbiq edilərsə, yanlış nəticələr qaytarır.

Arqument cəm_aralığı arqumentlə eyni ölçüdə olmaya bilər diapazon . Cəmlənəcək faktiki xanaları təyin edərkən, arqumentin yuxarı sol xanası başlanğıc xana kimi istifadə olunur. cəm_aralığı , sonra isə arqumentin ölçüsünə uyğun olan diapazon hissəsinin xanaları cəmlənir diapazon . Misal:

Ancaq arqumentlər varsa diapazon Və cəm_aralığı SUMIF funksiyaları fərqli sayda xana ehtiva edir, iş vərəqinin yenidən hesablanması gözlənildiyindən daha uzun çəkə bilər.

Nümunələr

Misal 1

Nümunə məlumatlarını aşağıdakı cədvəldən kopyalayın və yeni Excel vərəqinin A1 xanasına yapışdırın. Formula nəticələrini göstərmək üçün onları seçin və F2 və ardınca ENTER düyməsini basın. Lazım gələrsə, bütün məlumatları görmək üçün sütunların enini dəyişdirin.

Misal 2

Aşağıdakı cədvəldən nümunə məlumatları kopyalayın və yeni Excel vərəqinin A1 xanasına yapışdırın. Formula nəticələrini göstərmək üçün onları seçin və F2 və ardınca ENTER düyməsini basın. Bundan əlavə, sütunların enini onlardakı məlumatlara uyğun olaraq tənzimləyə bilərsiniz.

Problemin formalaşdırılması

Məsələn, aşağıdakı formada satış cədvəlimiz var:

Tapşırıq : menecer Qriqoryevin Kopeyka mağazası üçün yerinə yetirdiyi bütün sifarişləri yekunlaşdırın.

Metod 1: SUMIF funksiyası

Tapşırığımızda yalnız bir şərt olsaydı (bütün Qriqoryevin əmrləri və ya bütün sifarişlər "Kopeyka"), onda tapşırıq daxili Excel funksiyasından istifadə etməklə kifayət qədər asanlıqla həll ediləcəkdi. SUMIF Riyaziyyat kateqoriyasından. Onu necə istifadə edəcəyinizi oxuya bilərsiniz. Ancaq bizim vəziyyətimizdə bir deyil, iki şərt var, ona görə də bu üsul uyğun deyil ...

Metod 2. Göstərici sütunu

Cədvəlimizə bir növ göstərici kimi xidmət edəcək daha bir sütun əlavə edək: əgər sifariş “Kopeyka”da və Qriqoryevdən idisə, bu sütunun xanasında 1, əks halda 0 qiyməti olacaq. Düstur Bu sütuna daxil edilməli olan çox sadədir:

=(A2="Penny")*(B2="Qriqoriyev")

Mötərizədə olan məntiqi bərabərliklər DOĞRU və ya YANLIŞ-dır ki, bu da Excel-də 1 və 0-a bərabərdir.Beləliklə, biz bu ifadələri çoxaltdığımız üçün yalnız hər iki şərt yerinə yetirildikdə vahid əldə edəcəyik. İndi satışın dəyərini əldə edilən sütunun dəyərlərinə vurmaq və nəticədə alınan məbləğləri toplamaq qalır:

Metod 3: Sehrli massiv düsturu

Əgər siz əvvəllər Excel-in massiv düsturları kimi gözəl xüsusiyyətinə rast gəlməmisinizsə, onda sizə əvvəlcədən onlar haqqında çoxlu yaxşı şeylər oxumağı məsləhət görürəm.. Yaxşı, bizim vəziyyətimizdə problem bir düsturla həll olunur:

SUM((A2:A26="Qipik")*(B2:B26="Qriqoriyev")*D2:D26)

Bu düsturu daxil etdikdən sonra hər zamanki kimi Enter deyil, lakin basmalısınız Ctrl+Shift+Enter - onda Excel bunu kimi qəbul edəcəkmassiv düsturu və buruq mötərizələr əlavə edəcək. Klaviaturadan mötərizələri daxil etməyə ehtiyac yoxdur. Bu metodun (əvvəlki kimi) asanlıqla üç, dörd və s. heç bir məhdudiyyət olmadan şərtlər.

Metod 4: BDSUMM verilənlər bazası funksiyası

Kateqoriyada verilənlər bazası funksiyasını tapmaq olar BDSUMM (DSUM) , bu da problemimizi həll etməyə kömək edə bilər:

Nüans ondan ibarətdir ki, bu funksiyanın işləməsi üçün seçim şərtlərini ehtiva edən vərəqdə bir sıra hüceyrələr yaratmaq və sonra funksiyanın bu diapazonunu arqument kimi göstərmək lazımdır:

BDSUMM(A1:D26;D1;F1:G2)

Metod 5. Qismən Toplam Sihirbazı

Bu, çox kriteriyaların cəmlənməsi üçün mürəkkəb düsturlar yaratmağa kömək edən Excel əlavəsinin adıdır. Bu pulsuz əlavəni menyu vasitəsilə birləşdirə bilərsinizAlətlər - Əlavələr - Xülasə Sihirbazı(Alətlər - Əlavələr - Şərti Toplam Sihirbazı). Bundan sonra menyu Xidmət əmri görünməlidir qismən məbləğ Xülasə Sihirbazını işə salan:

İlk addımda Sum Mastersdata ilə aralığı təyin etməlisiniz, yəni. bütün cədvəlimizi seçin. İkinci mərhələdə, hər bir şərti düymə ilə siyahıya əlavə edərək, toplama üçün bir sütun və dəyərləri seçmək üçün şərtləri formalaşdırmaq lazımdır.Şərt əlavə edin:

Və nəhayət, 3-cü və 4-cü addımlarda nəticəni göstərmək istədiyimiz xananı göstəririk. Və aşağıdakılarla bitiririk:

3-cü Metodda bu massiv düsturuna oxşar bir şey istifadə etdiyimizi asanlıqla görmək olar. Yalnız burada klaviaturaya heç toxunmaq olmaz - yaşasın tənbəllik - tərəqqinin mühərriki!

bilirdinmi düşüncə təcrübəsi, gedanken təcrübəsi nədir?
Bu, mövcud olmayan bir təcrübədir, başqa bir dünya təcrübəsidir, əslində orada olmayanın təxəyyülüdür. Düşüncə təcrübələri yuxu kimidir. Canavarlar dünyaya gətirirlər. Fərziyyələrin eksperimental sınağı olan fiziki eksperimentdən fərqli olaraq, “düşüncə təcrübəsi” sehrli şəkildə eksperimental testi arzuolunan, yoxlanılmamış nəticələrlə əvəz edir, sübut olunmamış əsaslardan sübut kimi istifadə etməklə, əslində məntiqin özünü pozan məntiqi konstruksiyaları manipulyasiya edir, yəni. əvəzetmə. Beləliklə, "fikir eksperimentləri"nə müraciət edənlərin əsas vəzifəsi həqiqi fiziki eksperimenti onun "kuklası" ilə əvəz edərək dinləyicini və ya oxucunu aldatmaqdır - fiziki yoxlama olmadan şərti azadlığa buraxılan qondarma.
Fizikanı xəyali, “düşüncə təcrübələri” ilə doldurmaq dünyanın absurd, sürreal, çaşdırıcı mənzərəsinə gətirib çıxardı. Əsl tədqiqatçı bu cür “sarğıları” real dəyərlərdən ayırmalıdır.

Relyativistlər və pozitivistlər iddia edirlər ki, "düşüncə təcrübəsi" nəzəriyyələri (beynimizdə də yaranır) ardıcıllıq üçün yoxlamaq üçün çox faydalı bir vasitədir. Bununla da insanları aldadırlar, çünki istənilən yoxlamanı yalnız yoxlama obyektindən asılı olmayan mənbə həyata keçirə bilər. Fərziyyə ərizəçisinin özü öz ifadəsinin sınağı ola bilməz, çünki bu ifadənin özünün səbəbi ifadədə ərizəçiyə görünən ziddiyyətlərin olmamasıdır.

Biz bunu elmi və ictimai rəyi idarə edən bir növ dinə çevrilmiş SRT və GR-nin timsalında görürük. Onlara zidd olan heç bir fakt Eynşteynin düsturuna qalib gələ bilməz: “Əgər fakt nəzəriyyəyə uyğun gəlmirsə, faktı dəyişdirin” (Başqa bir versiyada “Fakt nəzəriyyəyə uyğun gəlmirmi? ").

"Düşüncə eksperimentinin" iddia edə biləcəyi maksimum, yalnız ərizəçinin öz məntiqi çərçivəsində hipotezin daxili ardıcıllığıdır, çox vaxt heç bir şəkildə doğru deyil. Təcrübəyə uyğunluq bunu yoxlamır. Həqiqi sınaq yalnız real fiziki təcrübədə baş verə bilər.

Təcrübə bir təcrübədir, çünki o, düşüncənin zərifliyi deyil, düşüncənin sınağıdır. Öz daxilində ardıcıl olan düşüncə özünü sınaya bilməz. Bu Kurt Gödel tərəfindən sübut edilmişdir.

FORUM XƏBƏRLƏRİ Eter nəzəriyyəsi cəngavərləri

30.10.2017 - 06:17: -> - Kərim_Xaydarov. 19.10.2017 - 04:24: -> - Kərim_Xaydarov. 11/10/2017 - 05:10: -> - Kərim_Xaydarov. 05.10.2017 - 11:03: