დავუშვათ, რომ თქვენ გაქვთ შემდეგი ანგარიში გაყიდვების წარმომადგენლებზე:
მისგან თქვენ უნდა გაარკვიოთ რამდენი ფანქრებიგაყიდვების წარმომადგენელმა გაყიდა ივანოვივ იანვარი.
პრობლემა: როგორ შევაჯამოთ მონაცემები რამდენიმე კრიტერიუმის მიხედვით??
გადაწყვეტა: მეთოდი 1:
BDSUMM(A1:G16;F1;I1:K2)
ინგლისურ ვერსიაში:
DSUM(A1:G16,F1,I1:K2)
ᲠᲝᲒᲝᲠ ᲛᲣᲨᲐᲝᲑᲡ:
ჩვენ მიერ მითითებული მონაცემთა ბაზიდან A1: G16ფუნქცია BDSUMMამოიღებს და აჯამებს სვეტის მონაცემებს რაოდენობა(არგუმენტი" ველი" = F1) უჯრედებში მითითებულის მიხედვით I1:K2 (გამყიდველი = ივანოვი; პროდუქტები = ფანქრები;თვე = იანვარი) კრიტერიუმები.
უარყოფითი მხარეები: კრიტერიუმების სია უნდა იყოს ფურცელზე.
შენიშვნები: შეჯამების კრიტერიუმების რაოდენობა შეზღუდულია ოპერატიული მეხსიერებით.
აპლიკაციის ზონა: Excel-ის ნებისმიერი ვერსია
მეთოდი 2:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
ინგლისურ ვერსიაში:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
ᲠᲝᲒᲝᲠ ᲛᲣᲨᲐᲝᲑᲡ:
SUMPRODUCT ფუნქცია აყალიბებს TRUE და FALSE მნიშვნელობების მასივებს, შერჩეული კრიტერიუმების მიხედვით, Excel მეხსიერებაში.
თუ გამოთვლები შესრულდა ფურცლის უჯრედებში (სიცხადისთვის, მე ვაჩვენებ ფორმულის მთელ მოქმედებას, თითქოს გამოთვლები ხდებოდა ფურცელზე და არა მეხსიერებაში), მაშინ მასივები ასე გამოიყურება:
აშკარაა, რომ თუ, მაგალითად, D2=ფანქრები, მაშინ მნიშვნელობა იქნება TRUE და თუ D3=საქაღალდეები, შემდეგ FALSE (რადგან ჩვენს მაგალითში პროდუქტის არჩევის კრიტერიუმი არის მნიშვნელობა ფანქრები).
იმის ცოდნა, რომ მნიშვნელობა TRUE ყოველთვის უდრის 1-ს და FALSE ყოველთვის 0-ის ტოლია, ჩვენ ვაგრძელებთ მუშაობას მასივებთან, როგორც 0 და 1 რიცხვებთან.
მიღებული მასივის მნიშვნელობების თანმიმდევრულად გამრავლებით, მივიღებთ ნულების და ერთების ერთ მასივს. სადაც სამივე შერჩევის კრიტერიუმი დაკმაყოფილდა, ( ივანოვი, ფანქარი, იანვარი) ე.ი. ყველა პირობამ მიიღო მნიშვნელობა TRUE, მივიღებთ 1 (1*1*1 = 1), მაგრამ თუ ერთი პირობა მაინც არ დაკმაყოფილდა, მივიღებთ 0 (1*1*0 = 0; 1*0*1 = 0; 0*1* 1 = 0).
ახლა რჩება მხოლოდ მიღებული მასივის გამრავლება მასივზე, რომელიც შეიცავს მონაცემებს, რომლებიც საბოლოოდ უნდა შევაჯამოთ (დიაპაზონი F2: F16) და, ფაქტობრივად, შეაჯამეთ ის, რაც არ იყო გამრავლებული 0-ზე.
ახლა შეადარეთ ფორმულის გამოყენებით მიღებული მასივები და ფურცელზე ეტაპობრივი გამოთვლა (მონიშნული წითლად).
მგონი ყველაფერი გასაგებია :)
მინუსები: SUMPRODUCT - "მძიმე" მასივის ფორმულა. მონაცემთა დიდ დიაპაზონზე გაანგარიშებისას, ხელახალი გაანგარიშების დრო შესამჩნევად იზრდება.
შენიშვნები
აპლიკაციის ზონა: Excel-ის ნებისმიერი ვერსია
მეთოდი 3: მასივის ფორმულა
SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
ინგლისურ ვერსიაში:
SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
ᲠᲝᲒᲝᲠ ᲛᲣᲨᲐᲝᲑᲡ:ზუსტად იგივე, რაც მეთოდი No2. მხოლოდ ორი განსხვავებაა - ეს ფორმულა შეყვანილია დაჭერით Ctrl+Shift+Enterდა არა მხოლოდ დაჭერით შედიდა 0-ების და 1-ების მასივი არ მრავლდება შეჯამების დიაპაზონიდა არჩეულია IF ფუნქციის გამოყენებით.
მინუსები: მასივის ფორმულები მონაცემთა დიდ დიაპაზონზე გაანგარიშებისას შესამჩნევად ზრდის ხელახალი გამოთვლის დროს.
შენიშვნები: დამუშავებული მასივების რაოდენობა შეზღუდულია 255-მდე.
აპლიკაციის ზონა: Excel-ის ნებისმიერი ვერსია
მეთოდი 4:
SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)
ადრე მე აღვწერე, თუ როგორ გამოვიყენოთ მორგებული ფუნქცია საპოვნელად. სამწუხაროდ, ეს ფუნქცია არ მუშაობს, თუ უჯრედები შეღებილია გამოყენებით პირობითი ფორმატირება. მე დავპირდი ფუნქციის "დასრულებას". მაგრამ ამ ჩანაწერის გამოქვეყნებიდან გასული ორი წლის განმავლობაში, მე ვერც დამოუკიდებლად და ვერც ინტერნეტიდან მოპოვებული ინფორმაციის დახმარებით ვერ დავწერდი მოსანელებელ კოდს... ( განახლება 2017 წლის 29 მარტსკიდევ ხუთი წლის შემდეგ მაინც მოვახერხე კოდის დაწერა; იხილეთ შენიშვნის ბოლო ნაწილი). და ახლახან დამხვდა იდეა, რომელიც შეიცავს დ.ჰაულის, რ.ჰოლის წიგნში „Excel 2007. Tricks“, რომელიც საშუალებას გაძლევთ გააკეთოთ საერთოდ კოდის გარეშე.
მოდით იყოს 1-დან 100-მდე რიცხვების სია, განთავსებული A1:A100 დიაპაზონში (ნახ. 1; აგრეთვე იხილეთ „SUMIF“ ფურცელი Excel ფაილში). დიაპაზონს აქვს პირობითი ფორმატირება, რომელიც აღნიშნავს უჯრედებს, რომლებიც შეიცავს 10-ზე მეტ რიცხვს და 20-ზე ნაკლები ან ტოლი.
ბრინჯი. 1. რიცხვთა დიაპაზონი; პირობითი ფორმატირება ირჩევს უჯრედებს, რომლებიც შეიცავს მნიშვნელობებს 10-დან 20-მდე
ჩამოტვირთეთ შენიშვნა ფორმატში, მაგალითები ფორმატში
ახლა თქვენ უნდა დაამატოთ მნიშვნელობები უჯრედებში, რომლებიც აკმაყოფილებენ თქვენ მიერ ახლახან დაყენებულ კრიტერიუმებს. არ აქვს მნიშვნელობა რა ფორმატირებაა გამოყენებული ამ უჯრედებზე, მაგრამ თქვენ უნდა იცოდეთ რა კრიტერიუმებით არის ხაზგასმული უჯრედები.
შესაბამისი უჯრედების დიაპაზონის დასამატებლად მარტოკრიტერიუმით, შეგიძლიათ გამოიყენოთ SUMIF ფუნქცია (ნახ. 2).
ბრინჯი. 2. უჯრედების ჯამი, რომლებიც აკმაყოფილებენ ერთსა და იმავე პირობას
Თუ თქვენ გაქვთ ზოგიერთიპირობები, შეგიძლიათ გამოიყენოთ SUMIFS ფუნქცია(ნახ. 3).
ბრინჯი. 3. უჯრედების ჯამი, რომლებიც აკმაყოფილებენ რამდენიმე პირობას
თქვენ შეგიძლიათ გამოიყენოთ COUNTIF ფუნქცია იმ უჯრედების რაოდენობის დასათვლელად, რომლებიც აკმაყოფილებენ ერთ კრიტერიუმს.
თქვენ შეგიძლიათ გამოიყენოთ COUNTIFS ფუნქცია იმ უჯრედების რაოდენობის დასათვლელად, რომლებიც აკმაყოფილებენ მრავალ კრიტერიუმს.
Excel-ს აქვს კიდევ ერთი ფუნქცია, რომელიც საშუალებას გაძლევთ მიუთითოთ მრავალი პირობა. ეს ფუნქცია შედის მონაცემთა ბაზის ფუნქციების კომპლექტში Excel მონაცემებიდა ეწოდება BDSUMM. მის შესამოწმებლად გამოიყენეთ რიცხვების იგივე ნაკრები A2:A100 დიაპაზონში (ნახ. 4; აგრეთვე იხილეთ „BDSUMM“ ფურცელი Excel ფაილში).
ბრინჯი. 4. მონაცემთა ბაზის ფუნქციების გამოყენება
აირჩიეთ უჯრედები C1:D2 და დაასახელეთ დიაპაზონის კრიტერიუმები მისი შეყვანით სახელის ველში ფორმულის ზოლის მარცხნივ. ახლა აირჩიეთ უჯრედი C1 და შეიყვანეთ =$A$1, ეს არის ბმული ფურცელზე პირველი უჯრედისა, რომელიც შეიცავს მონაცემთა ბაზის სახელს. შეიყვანეთ =$A$1 უჯრედში D1 და თქვენ მიიღებთ A სვეტის სათაურის ორ ასლს. ეს ასლები გამოყენებული იქნება BDSUMM(C1:D2) პირობების სათაურებად, რომელსაც თქვენ დაასახელეთ კრიტერიუმები. C2 უჯრედში შეიყვანეთ >10. უჯრედში D2 შეიყვანეთ<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($A$1:$A$101,1, კრიტერიუმები)
თქვენ შეგიძლიათ გამოიყენოთ COUNT ფუნქცია იმ უჯრედების რაოდენობის დასათვლელად, რომლებიც აკმაყოფილებენ მრავალ კრიტერიუმს.
ჯონ უოკენბახის წიგნის კითხვისას გავიგე, რომ Excel 2010-დან დაწყებული, VBA-ში გამოჩნდა ახალი DisplayFormat თვისება (იხილეთ, მაგალითად, Range.DisplayFormat Property). ანუ VBA-ს შეუძლია ეკრანზე გამოსახული ფორმატის წაკითხვა. არ აქვს მნიშვნელობა როგორ იქნა მიღებული, მომხმარებლის პირდაპირი პარამეტრებით ან პირობითი ფორმატირების გამოყენებით. სამწუხაროდ, MS-ის დეველოპერებმა გააკეთეს ისე, რომ DisplayFormat თვისება მუშაობს მხოლოდ VBA-დან გამოძახებულ პროცედურებში და მომხმარებლის მიერ განსაზღვრული ფუნქციები ამ თვისებებზე დაფუძნებული აყენებს #VALUE-ს! ამასთან, შეგიძლიათ მიიღოთ მნიშვნელობების ჯამი დიაპაზონში გარკვეული ფერის უჯრედებისთვის პროცედურის გამოყენებით (მაკრო, მაგრამ არა ფუნქცია). ღია (შეიცავს VBA კოდს). გაიარეთ მენიუ ხედი -> მაკროები -> მაკროები; ფანჯარაში მაკროაირჩიეთ ხაზი SumColorUslდა დააჭირეთ შეასრულეთ. გაუშვით მაკრო, აირჩიეთ შეჯამების დიაპაზონი და კრიტერიუმი. პასუხი გამოჩნდება ფანჯარაში.
პროცედურის კოდი
Sub SumColorConv() Application.Volatile True Dim SumColor როგორც Double Dim i As Range Dim User Range როგორც Range Dim CriterionRange როგორც დიაპაზონი SumColor = 0 " დიაპაზონის მოთხოვნის ნაკრები UserRange = Application.InputBox(_ Prompt:="T _აირჩიეთ შეჯამება :="Range selection", _ Default:=ActiveCell.Address, _ Type:=8) " კრიტერიუმის მოთხოვნა Set CriterionRange = Application.InputBox(_ Prompt:="Select შეჯამების კრიტერიუმი", _ Title:="Criterion Selection", _ Default:=ActiveCell.Address, _ Type:=8) " "სწორი" უჯრედების შეჯამება თითოეული i-სთვის User Range If i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat ინტერიერი.ფერი, შემდეგ SumColor = SumColor + i End If შემდეგი MsgBox SumColor End Sub
Sub SumColorCond() განაცხადი. არასტაბილური მართალია Dim SumColor როგორც ორმაგი Dim i As Range Dim User Range როგორც Range Dim CriterionRange როგორც Range SumColor = 0 "დიაპაზონის მოთხოვნა დააყენეთ User Range = Application.InputBox(_ მოთხოვნა: "აირჩიეთ შემაჯამებელი დიაპაზონი", _ სათაური: "აირჩიე დიაპაზონი", _ ნაგულისხმევი:=ActiveCell.Address, _ ტიპი:=8) „მოთხოვნის კრიტერიუმები დააყენეთ CriterionRange = აპლიკაცია. შეყვანის ყუთი (_ მოთხოვნა := "აირჩიე ჯამის კრიტერიუმი", _ სათაური: = "კრიტერიუმების შერჩევა", _ ნაგულისხმევი: = ActiveCell. მისამართი, _ |
ხშირად ხდება, რომ თქვენ გჭირდებათ ყოველი მეორე, მესამე, მეოთხე და ა.შ. უჯრედების შეჯამება ცხრილებში. ახლა, შემდეგი ხრიკის წყალობით, ეს შეიძლება გაკეთდეს.
Excel-ს არ აქვს სტანდარტული ფუნქცია, რომელსაც შეუძლია თითოეულის შეჯამება მე-n უჯრედიან სიმებიანი. თუმცა, თქვენ შეგიძლიათ შეასრულოთ ეს ამოცანა რამდენიმე საშუალებით სხვადასხვა გზები. ყველა ეს მიდგომა დაფუძნებულია ROW და MOD ფუნქციებზე.
ROW ფუნქციააბრუნებს რიგის ნომერს მოცემული უჯრედის მითითებისთვის: ROW(მინიშნება), Excel-ის რუსულ ვერსიაში ROW(ბმული).
REST ფუნქცია (MOD)აბრუნებს რიცხვის გაყოფის ნარჩენს გამყოფზე: MOD(number;divisor), Excel-ის რუსულ ვერსიაში REMAIN(number;divisor).
მოათავსეთ ROW ფუნქცია MOD ფუნქციის შიგნით (რიცხვითი არგუმენტის გადასაცემად), გაყავით 2-ზე (ყველა მეორე უჯრედის შესაჯამებლად) და შეამოწმეთ შედეგი არ არის თუ არა ნული. თუ ასეა, უჯრედი ჯამდება. ეს ფუნქციები შეიძლება გამოყენებულ იქნას სხვადასხვა გზით - ზოგიერთი სხვაზე უკეთეს შედეგს იძლევა. მაგალითად, მასივის ფორმულა $A$1:$A$100 დიაპაზონში ყველა სხვა უჯრედის შესაჯამებლად შეიძლება ასე გამოიყურებოდეს: =SUM(IF(MOD(ROW($A$1:$A$500);2)=0;$ A$1: $A$500;0)), Excel-ის რუსულ ვერსიაში =SUM(IF(RESID(ROW($A$1:$A$500),2)=0;$A$1:$A$500,0) ) .
ვინაიდან ეს არის მასივის ფორმულა, თქვენ უნდა შეიყვანოთ ის Ctrl+Shift+Enter დაჭერით, Excel დაამატებს ხვეულ ბრეკეტებს, ასე გამოიყურება: (=SUM(IF(MOD(ROW($A$1:$A$500),2. )= 0;$A$1:$A$500;0))), Excel-ის რუსულ ვერსიაში: (=SUM(IF(REMINAL(ROW($A$1:$A$500),2)=0;$A $1:$A $500;0))) თქვენ გჭირდებათ Excel, რომ დაამატოთ ხვეული ბრეკეტები; თუ მათ თავად დაამატებთ, ფორმულა არ იმუშავებს.
მიუხედავად იმისა, რომ მიზანი მიღწეულია, ეს მეთოდი უარყოფითად მოქმედებს დიზაინზე ცხრილი. ეს არის მასივის ფორმულის არასაჭირო გამოყენება. კიდევ უფრო უარესი, ამ გრძელ ფორმულას აქვს ჩადებული გადაანგარიშებული ROW ფუნქცია, რაც უფრო დიდ ფორმულას ასევე აქცევს ხელახლა გამოთვლაში. ეს ნიშნავს, რომ ის მუდმივად გადაითვლება, რაც არ უნდა გააკეთოთ სამუშაო წიგნში. ეს ძალიან ცუდი გზაა!
აქ არის კიდევ ერთი ფორმულა, რომელიც ოდნავ არის საუკეთესო არჩევანი: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)), Excel-ის რუსულ ვერსიაში =SUMPRODUCT((REMINAL(ROW($) A$1 :$A$500);2)=0)*($A$1:$A$500)) .
თუმცა, გაითვალისწინეთ, რომ ეს ფორმულა დააბრუნებს #VALUE შეცდომას. (#VALUE!) თუ დიაპაზონში რომელიმე უჯრედი შეიცავს ტექსტს და არა ციფრებს. ეს ფორმულა, თუმცა სინამდვილეში არ არის მასივის ფორმულა, ასევე ანელებს Excel მუშაობა, თუ მას ძალიან ბევრჯერ იყენებთ ან ყოველ ჯერზე დიდ დიაპაზონს მიუთითებს.
საბედნიეროდ, არსებობს Საუკეთესო გზა, რომელიც არა მხოლოდ უფრო ეფექტური, არამედ ბევრად უფრო მოქნილი გადაწყვეტაა. ის მოითხოვს DSUM ფუნქციის გამოყენებას. ამ მაგალითში, ჩვენ გამოვიყენეთ დიაპაზონი A1:A500, როგორც დიაპაზონი, რომელშიც შევაჯამეთ ყოველი მე-n უჯრედი.
E1 უჯრედში შეიყვანეთ სიტყვა Criteria. E2 უჯრედში შეიყვანეთ შემდეგი ფორმულა: =MOD(ROW(A2)-$C$2-1,$C$2)=0, Excel-ის რუსულ ვერსიაში =RESIDENT(ROW(A2)-$C$2-1, $C$2) =0. აირჩიეთ უჯრედი C2 და აირჩიეთ ბრძანება Data → Validation.
ნებადართული ველში აირჩიეთ სია და წყაროს ველში შეიყვანეთ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. დარწმუნდით, რომ სიის ჩამრთველი არის მონიშნული. მისაღები ღირებულებები(უჯრედში) და დააჭირეთ OK. C1 უჯრედში შეიყვანეთ ტექსტი SUM ყოველ…. ნებისმიერ უჯრედში, გარდა 1 მწკრივისა, შეიყვანეთ შემდეგი ფორმულა: =DSUM($A:$A;1,$E$1:$E$2) :$E$2) .
უჯრედში პირდაპირ ზემოთ, სადაც შეიყვანეთ DSUM ფუნქცია, შეიყვანეთ ტექსტი ="Summing Every" & $С$2 & CHOOSE($C$2;"st";"nd";"rd";"th";" th";"th";"th";"th";"th";"th") & "უჯრედი" . ახლა რჩება მხოლოდ C2 უჯრედში სასურველი ნომრის არჩევა და დანარჩენს გააკეთებს DSUM ფუნქცია.
DSUM ფუნქციის გამოყენებით, შეგიძლიათ უჯრედების ჯამი თქვენს მიერ მითითებულ ინტერვალში. DSUM ფუნქცია ბევრად უფრო ეფექტურია ვიდრე მასივის ფორმულა ან SUMPRODUCT ფუნქცია. მიუხედავად იმისა, რომ დაყენებას ცოტა მეტი დრო სჭირდება, ეს არის ძნელი სასწავლი, მარტივი ბრძოლა.
