SUMIF ფუნქცია Excel-ში და ჯამი მრავალი პირობით

Excel წარმოუდგენლად ფუნქციონალური პროგრამაა. ჩაშენებული ფუნქციების ნაკრებიც კი საკმარისია თითქმის ნებისმიერი ამოცანის შესასრულებლად. და ბევრისთვის ნაცნობი სტანდარტულების გარდა, არის ისეთებიც, რომელთა შესახებაც ცოტას სმენია. მაგრამ ამავე დროს, ისინი არ წყვეტენ სასარგებლოს. მათ უფრო ვიწრო სპეციალობა აქვთ და მათი საჭიროება ყოველთვის არ არის. მაგრამ თუ იცით მათ შესახებ, მაშინ კრიტიკულ მომენტში ისინი შეიძლება ძალიან სასარგებლო იყოს.

დღეს ჩვენ ვისაუბრებთ ერთ-ერთ ასეთ ფუნქციაზე − SUMESLIMN.

თუ მომხმარებლის წინაშე დგას დავალება შეაჯამოს რამდენიმე მნიშვნელობა, ფოკუსირება მოახდინოს გარკვეულ კრიტერიუმებზე, მაშინ აუცილებელია ფუნქციის გამოყენება SUMESLIMN. ფორმულა, რომელიც იყენებს ამ ფუნქციას, იღებს ამ პირობებს არგუმენტებად, შემდეგ აჯამებს მნიშვნელობებს, რომლებიც მათ აკმაყოფილებენ და შემდეგ ნაპოვნი მნიშვნელობა შეიყვანება უჯრედში, რომელშიც ის დაწერილია. 

SUMIFS ფუნქციის დეტალური აღწერა

ფუნქციის განხილვამდე SUMESLIMN, ჯერ უნდა გესმოდეთ, რა არის მისი უფრო მარტივი ვერსია – SUMMESLI, ვინაიდან სწორედ მასზეა დაფუძნებული ფუნქცია, რომელსაც განვიხილავთ. თითოეულმა ჩვენგანმა ალბათ უკვე იცნობს ორ ფუნქციას, რომლებიც ხშირად გამოიყენება - თანხა (ახორციელებს სიდიდეების შეჯამებას) და თუ (ამოწმებს მნიშვნელობას მითითებულ პირობაზე).

თუ მათ დააკავშირებთ, მიიღებთ სხვა ფუნქციას - SUMMESLI, რომელიც ამოწმებს მონაცემებს მომხმარებლის მიერ განსაზღვრული კრიტერიუმების მიხედვით და აჯამებს მხოლოდ იმ რიცხვებს, რომლებიც აკმაყოფილებს ამ კრიტერიუმებს. თუ ვსაუბრობთ Excel-ის ინგლისურ ვერსიაზე, მაშინ ამ ფუნქციას ეწოდება SUMIF. მარტივი სიტყვებით, -language სახელი არის ინგლისურენოვანის პირდაპირი თარგმანი. ეს ფუნქცია შეიძლება გამოყენებულ იქნას სხვადასხვა მიზნებისთვის. კერძოდ, ის შეიძლება გამოყენებულ იქნას როგორც ალტერნატივა VPR, ანუ ჩაწერეთ

ძირითადი განსხვავება ფუნქციას შორის SUMESLIMN  ჩვეულებრივი ფუნქციიდან SUMMESLI არის ის, რომ რამდენიმე კრიტერიუმია გამოყენებული. მისი სინტაქსი ერთი შეხედვით საკმაოდ რთულია, მაგრამ უფრო დაწვრილებითი გამოკვლევის შემდეგ აღმოჩნდება, რომ ამ ფუნქციის ლოგიკა ძალიან მარტივია. ჯერ უნდა აირჩიოთ დიაპაზონი, სადაც მოხდება მონაცემების შემოწმება, შემდეგ კი დააყენოთ შესაბამისობის პირობები, რომლებთანაც შესრულდება ანალიზი. და ასეთი ოპერაცია შეიძლება შესრულდეს საკმაოდ დიდი რაოდენობის პირობებში.

თავად სინტაქსი ასეთია:

SUMIFS(ჯამური_დიაპაზონი, პირობის_დიაპაზონი1, პირობა1, [პირობის_დიაპაზონი2, პირობა2], ...)

შესაფერის ადგილებში აუცილებელია ცალკეული შემთხვევისთვის შესაფერისი უჯრედების მასივების განთავსება. 

მოდით განვიხილოთ არგუმენტები უფრო დეტალურად:

1. ჯამის_დიაპაზონი. ეს არგუმენტი, ისევე როგორც პირობის 1 და 1 პირობის დიაპაზონი, საჭიროა. ეს არის უჯრედების ერთობლიობა, რომლებიც უნდა შეჯამდეს.
2. მდგომარეობა_დიაპაზონი 1. ეს არის ის დიაპაზონი, სადაც მდგომარეობა შემოწმდება. ის დაწყვილებულია შემდეგ არგუმენტთან - Condition1. კრიტერიუმის შესაბამისი მნიშვნელობების შეჯამება ხორციელდება წინა არგუმენტში მითითებულ უჯრედებში.
3. მდგომარეობა 1. ეს არგუმენტი განსაზღვრავს კრიტერიუმებს, რომლებიც უნდა შემოწმდეს. ის შეიძლება დაყენდეს, მაგალითად, ამ გზით: "> 32".
4. მდგომარეობის დიაპაზონი 2, მდგომარეობა 2… აქ, შემდეგი პირობები დაყენებულია იმავე გზით. თუ საჭიროა რამდენიმე პირობის მითითება, მაშინ ემატება არგუმენტები მდგომარეობის დიაპაზონი 3 და პირობა 3. სინტაქსი იგივეა შემდეგი არგუმენტებისთვის.

ფუნქცია საშუალებას იძლევა მაქსიმუმ 127 წყვილი პირობებისა და დიაპაზონის დამუშავება. 

თქვენ შეგიძლიათ გამოიყენოთ იგი ერთდროულად რამდენიმე სფეროში (ჩვენ მოგცემთ მხოლოდ რამდენიმეს, სია რეალურად კიდევ უფრო გრძელია):

1. Აღრიცხვა. მაგალითად, კარგია ფუნქციის გამოყენება SUMESLIMN შემაჯამებელი ანგარიშების შექმნა, მაგალითად, კვარტალში გარკვეულ თანხაზე მეტის ხარჯვისთვის. ან შექმენით ანგარიში ერთ პროდუქტზე გარკვეული ფასების კატეგორიიდან.
2. გაყიდვების მენეჯმენტი. აქ ასევე ფუნქცია შეიძლება იყოს ძალიან სასარგებლო. მაგალითად, ჩვენ წინაშე დგას ამოცანა, შევაჯამოთ მხოლოდ იმ საქონლის ღირებულება, რომელიც გარკვეულ დროს მიყიდა გარკვეულ მომხმარებელს. და ასეთ სიტუაციაში ფუნქცია SUMESLIMN შეიძლება ძალიან სასარგებლო იყოს.
3. Განათლება. დღეს ამ სფეროდან უფრო პრაქტიკულ მაგალითებს მოვიყვანთ. კერძოდ, მისი გამოყენება შეგიძლიათ სტუდენტების შეფასებების შეჯამების მისაღებად. თქვენ შეგიძლიათ აირჩიოთ ერთი საგნისთვის ან ინდივიდუალური კლასებისთვის. ადამიანს შეუძლია დაუყონებლივ დააწესოს რამდენიმე კრიტერიუმი, რომლითაც შეირჩევა შეფასება, რაც ძალიან მოსახერხებელია და შეუძლია დაზოგოს ბევრი დრო.

როგორც ხედავთ, ამ ფუნქციის აპლიკაციების სპექტრი ძალიან ფართოა. მაგრამ ეს არ არის მისი ერთადერთი დამსახურება. მოდით გადავხედოთ ამ მახასიათებლის კიდევ რამდენიმე უპირატესობას:

1. მრავალი კრიტერიუმის დაყენების უნარი. რატომ არის ეს უპირატესობა? შეგიძლიათ გამოიყენოთ ნორმალური ფუნქცია SUMMESLI! და ყველაფერი იმიტომ, რომ ეს მოსახერხებელია. არ არის საჭირო თითოეული კრიტერიუმისთვის ცალკეული გამოთვლების ჩატარება. ყველა მოქმედების დაპროგრამება შესაძლებელია წინასწარ, მანამდეც კი. როგორ ჩამოყალიბდება მონაცემთა ცხრილი. ეს არის დროის შესანიშნავი დაზოგვა.
2. ავტომატიზაცია. თანამედროვე ეპოქა ავტომატიზაციის ხანაა. ბევრი რამის გამომუშავება შეუძლია მხოლოდ იმ ადამიანს, რომელმაც იცის როგორ სწორად მოახდინოს თავისი მუშაობის ავტომატიზაცია. ამიტომ არის Excel-ის და ფუნქციის დაუფლების უნარი SUMESLIMN განსაკუთრებით მნიშვნელოვანია ნებისმიერი ადამიანისთვის, ვისაც კარიერის აშენება სურს. ერთი ფუნქციის ცოდნა საშუალებას გაძლევთ შეასრულოთ რამდენიმე მოქმედება ერთდროულად, როგორც ერთი. და აქ ჩვენ გადავდივართ ამ ფუნქციის შემდეგ სარგებელზე.
3. დროის დაზოგვა. მხოლოდ იმის გამო, რომ ერთი ფუნქცია ერთდროულად ასრულებს რამდენიმე დავალებას.
4. Სიმარტივე. იმისდა მიუხედავად, რომ სინტაქსი ერთი შეხედვით საკმაოდ მძიმეა მისი მოცულობის გამო, სინამდვილეში, ამ ფუნქციის ლოგიკა ძალიან მარტივია. ჯერ შეირჩევა მონაცემთა დიაპაზონი, შემდეგ მნიშვნელობების დიაპაზონი, რომელიც შემოწმდება გარკვეულ პირობასთან შესაბამისობაში. და რა თქმა უნდა, თავად პირობაც უნდა იყოს მითითებული. და ასე რამდენჯერმე. სინამდვილეში, ეს ფუნქცია დაფუძნებულია მხოლოდ ერთ ლოგიკურ კონსტრუქტზე, რაც მას უფრო მარტივს ხდის, ვიდრე ცნობილი VPR მიუხედავად იმისა, რომ მისი გამოყენება შესაძლებელია იმავე მიზნებისთვის, ასევე კრიტერიუმების უფრო დიდი რაოდენობის გათვალისწინებით. 

SUMIFS ფუნქციის გამოყენების მახასიათებლები

ამ ფუნქციის გამოყენების რამდენიმე მახასიათებელია, რომელსაც ყურადღება უნდა მიაქციოთ. უპირველეს ყოვლისა, ეს ფუნქცია უგულებელყოფს დიაპაზონებს ტექსტური სტრიქონებით ან ნულოვანით, რადგან მონაცემთა ამ ტიპების დამატება შეუძლებელია არითმეტიკული ნიმუშით, მხოლოდ სტრიქონების მსგავსია. ამ ფუნქციას არ შეუძლია ამის გაკეთება. ასევე ყურადღება უნდა მიაქციოთ შემდეგ პირობებს:

1. თქვენ შეგიძლიათ გამოიყენოთ ამ ტიპის მნიშვნელობები, როგორც უჯრედების არჩევის პირობები, რათა შემდგომ დაამატოთ მათში შემავალი მნიშვნელობები: რიცხვითი მნიშვნელობები, ლოგიკური გამონათქვამები, უჯრედების მითითებები და ა.შ. 
2. თუ მოწმდება ტექსტი, ლოგიკური გამონათქვამები ან მათემატიკური ნიშნები, მაშინ ასეთი კრიტერიუმები მითითებულია ციტატების მეშვეობით.
3. არ შეიძლება 255 სიმბოლოზე მეტი ტერმინების გამოყენება.
4. შესაძლებელია მიახლოებითი კრიტერიუმების გამოყენება მნიშვნელობების შერჩევისას ველური ბარათების გამოყენებით. კითხვის ნიშანი გამოიყენება ერთი სიმბოლოს ჩასანაცვლებლად, ხოლო გამრავლების ნიშანი (ვარსკვლავი) საჭიროა მრავალი სიმბოლოს ჩასანაცვლებლად. 
5. ლოგიკური მნიშვნელობები, რომლებიც შეჯამების დიაპაზონშია, ავტომატურად გარდაიქმნება ციფრულ მნიშვნელობებად მათი ტიპის მიხედვით. ამრიგად, მნიშვნელობა "TRUE" იქცევა ერთად, ხოლო "FALSE" - ნულში. 
6. თუ #VALUE! შეცდომა გამოჩნდება უჯრედში, ეს ნიშნავს, რომ უჯრედების რაოდენობა მდგომარეობასა და შეჯამების დიაპაზონში განსხვავებულია. თქვენ უნდა დარწმუნდეთ, რომ ამ არგუმენტების ზომები იგივეა. 

SUMIFS ფუნქციის გამოყენების მაგალითები

ფუნქცია SUMESLIMN არც ისე რთული, როგორც ერთი შეხედვით ჩანს, გამოდის. მაგრამ მეტი სიცხადისთვის, მოდით შევხედოთ რამდენიმე პრაქტიკულ მაგალითს, თუ როგორ შეგიძლიათ გამოიყენოთ ფუნქცია SUMESLIMN. ეს ბევრად გაადვილებს თემის შესწავლას.

მდგომარეობის შეჯამება დინამიური დიაპაზონი

ასე რომ, დავიწყოთ პირველი მაგალითით. ვთქვათ, გვაქვს ცხრილი, რომელიც შეიცავს ინფორმაციას იმის შესახებ, თუ როგორ უმკლავდებიან მოსწავლეები კონკრეტულ საგანში სასწავლო გეგმას. არის ქულების ნაკრები, შესრულება ფასდება 10-ბალიანი სკალით. ამოცანაა გამოვნახოთ იმ სტუდენტების საგამოცდო შეფასება, რომელთა გვარი იწყება A ასოთი, ხოლო მინიმალური ქულა არის 5.

ცხრილი ასე გამოიყურება.

იმისათვის, რომ გამოვთვალოთ ჯამური ქულა ზემოთ აღწერილი კრიტერიუმების საფუძველზე, უნდა გამოვიყენოთ შემდეგი ფორმულა.

მოდით უფრო დეტალურად აღვწეროთ არგუმენტები:

1. C3:C14 არის ჩვენი შეჯამების დიაპაზონი. ჩვენს შემთხვევაში, ეს ემთხვევა მდგომარეობის დიაპაზონს. მისგან შეირჩევა ქულები, რომლებიც გამოიყენება თანხის გამოსათვლელად, მაგრამ მხოლოდ ის, რაც ჩვენს კრიტერიუმებს ექვემდებარება.
2. ">5" ჩვენი პირველი პირობაა.
3. B3:B14 არის მეორე შემაჯამებელი დიაპაზონი, რომელიც მუშავდება მეორე კრიტერიუმის შესატყვისად. ჩვენ ვხედავთ, რომ არ არის დამთხვევა შეჯამების დიაპაზონთან. აქედან დავასკვნით, რომ შეჯამების დიაპაზონი და პირობის დიაპაზონი შეიძლება იყოს ან არ იყოს იდენტური. 
4. "A*" არის მეორე დიაპაზონი, რომელიც განსაზღვრავს ნიშნების შერჩევას მხოლოდ იმ სტუდენტებისთვის, რომელთა გვარი იწყება A-ით. ჩვენს შემთხვევაში, ვარსკვლავი ნიშნავს სიმბოლოების ნებისმიერ რაოდენობას. 

გამოთვლების შემდეგ ვიღებთ შემდეგ ცხრილს.

როგორც ხედავთ, ფორმულამ შეაჯამა მნიშვნელობები დინამიური დიაპაზონის საფუძველზე და მომხმარებლის მიერ მითითებულ პირობებზე.

შერჩევითი შეჯამება პირობით Excel-ში

ახლა დავუშვათ, გვინდა მივიღოთ ინფორმაცია იმის შესახებ, თუ რა საქონელი რომელ ქვეყნებში გაიგზავნა ბოლო კვარტალში. ამის შემდეგ, იპოვეთ მთლიანი შემოსავალი ივლისისა და აგვისტოს გადაზიდვებიდან.

თავად ცხრილი ასე გამოიყურება. 

საბოლოო შედეგის დასადგენად, ჩვენ გვჭირდება ასეთი ფორმულა.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

ამ ფორმულით ჩატარებული გამოთვლების შედეგად ვიღებთ შემდეგ შედეგს.

ყურადღება ეს ფორმულა საკმაოდ დიდია, მიუხედავად იმისა, რომ ჩვენ მხოლოდ ორი კრიტერიუმი გამოვიყენეთ. თუ მონაცემთა დიაპაზონი იგივეა, მაშინ შეგიძლიათ მნიშვნელოვნად შეამციროთ ფორმულის სიგრძე, როგორც ეს ნაჩვენებია ქვემოთ.

SUMIFS ფუნქცია აჯამებს მნიშვნელობებს მრავალ პირობებში

ახლა კიდევ ერთი მაგალითი მოვიყვანოთ საილუსტრაციოდ. ამ შემთხვევაში, ცხრილი იგივე რჩება, რაც წინა შემთხვევაში. 

ჩვენ ვიყენებთ შემდეგ ფორმულას (მაგრამ მას ვწერთ მასივის ფორმულად, ანუ შევდივართ კლავიშთა კომბინაციის საშუალებით CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

ფუნქციის შემდეგ SUMESLIMN შეაჯამებს მნიშვნელობათა მასივს ფორმულაში მითითებულ კრიტერიუმებზე დაყრდნობით (ანუ ჩინეთისა და საქართველოს ქვეყნები), მიღებული მასივი ჯდება ჩვეულებრივი ფუნქციით. ჯამი, რომელიც იწერება მასივის ფორმულის სახით.

თუ პირობები გადაცემულია მასივის მუდმივად ერთზე მეტი წყვილისთვის, მაშინ ფორმულა არასწორ შედეგს მისცემს.

ახლა მოდით შევხედოთ ცხრილს, რომელიც შეიცავს ჯამებს.

როგორც ხედავთ, ჩვენ წარმატებას მივაღწიეთ. თქვენც აუცილებლად მიაღწევთ წარმატებას. დიდი წარმატება ამ სფეროში. ეს არის ძალიან მარტივი ფუნქცია, რომელსაც შეუძლია გაიგოს ადამიანი, რომელმაც ახლახან დაადგა ფეხი Excel-ის სწავლის გზას. და ჩვენ უკვე ვიცით, რომ ფუნქცია SUMESLIMN საშუალებას გაძლევთ იყოთ ეფექტური საქმიანობის ნებისმიერ სფეროში, ბუღალტრული აღრიცხვიდან დაწყებული განათლებამდეც კი. მაშინაც კი, თუ თქვენ აშენებთ კარიერას ნებისმიერ სხვა სფეროში, რომელიც ზემოთ არ არის აღწერილი, ეს ფუნქცია მაინც დაგეხმარებათ ფულის გამომუშავებაში. ამიტომ არის ის ღირებული.

რაც მთავარია, ის საშუალებას გაძლევთ დაზოგოთ დრო, რაც, სამწუხაროდ, შეზღუდული რესურსია. როგორც ჩანს, ორი ფუნქციის გამოსაყენებლად არის რამდენიმე წამი, მაგრამ როდესაც თქვენ უნდა შეასრულოთ განმეორებადი ოპერაციების დიდი რაოდენობა, მაშინ ეს წამები ემატება საათებს, რომლებიც შეიძლება სხვა რამეზე დახარჯოთ. ამიტომ გირჩევთ ივარჯიშოთ ამ ფუნქციის გამოყენებით. უფრო მეტიც, ის წარმოუდგენლად მარტივია.