Ndodh shpesh që ju duhet të përmblidhni çdo qelizë të dytë, të tretë, të katërt etj. në një tabelë. Tani, falë trukut të mëposhtëm, mund të bëhet.
Excel nuk ofron një funksion standard që mund të përmbledhë secilin qeliza e n-të ose një varg. Megjithatë, ju mund ta kryeni këtë detyrë me disa menyra te ndryshme. Të gjitha këto qasje bazohen në funksionet ROW (ROW) dhe MOD (MOD).
Funksioni ROW kthen numrin e rreshtit për referencën e dhënë të qelizës: ROW(referencë), në versionin rus të Excel ROW(referenca).
Funksioni MOD (MOD) kthen pjesën e mbetur pas pjesëtimit të një numri me një pjesëtues: MOD(numër;pjesëtues), në versionin rus të Excel MOD (numër;pjesëtues).
Vendosni funksionin ROW në funksionin MOD (për të kaluar një argument numerik), pjesëtojeni me 2 (për të mbledhur çdo qelizë të dytë) dhe kontrolloni nëse rezultati nuk është zero. Nëse po, qeliza përmblidhet. Këto funksione mund të përdoren në mënyra të ndryshme - disa do të ofrojnë rezultati më i mirë, se të tjerët. Për shembull, një formulë grupi për të përmbledhur çdo qelizë tjetër në rangun $A$1:$A$100 mund të duket kështu: =SUM(IF(MOD(ROW($A$1:$A$500);2)=0;$ 1 $ A: 500 $ A $; 0))
Meqenëse kjo është një formulë grupi, duhet ta futni atë duke shtypur Ctrl+Shift+Enter, Excel do të shtojë kllapa kaçurrelë në mënyrë që të duket kështu: (=SUM(IF(MOD(ROW($A$1:$A$500), 2)= 0;$A$1:$A$500;0))) 500$;0))) Na duhet Excel për të shtuar kllapa kaçurrelë; nëse i shtoni vetë, formula nuk do të funksionojë.
Edhe pse qëllimi është arritur, kjo metodë ndikon negativisht në dizajn. fletëllogaritëse. Ky është një aplikim i panevojshëm i një formule grupi. Për t'i bërë gjërat edhe më keq, kjo formulë e gjatë ka një funksion ROW të rillogaritshëm të vendosur brenda saj, duke e bërë formulën më të madhe gjithashtu të rillogaritshme. Kjo do të thotë se do të rillogaritet vazhdimisht, pavarësisht se çfarë bëni në librin e punës. Kjo është një mënyrë shumë e keqe!
Këtu është një formulë tjetër, e cila është pak zgjedhja më e mirë: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) :$A$500);2)=0)*($A$1 : 500$ A$)) .
Megjithatë, kini parasysh se kjo formulë do të kthejë #VLERËN! (#VALUE!) nëse ndonjë qelizë në interval përmban tekst në vend të numrave. Kjo formulë, edhe pse në fakt nuk është një formulë grupi, gjithashtu ngadalësohet Punë në Excel nëse përdoret shumë herë, ose nëse i referohet një gamë të madhe çdo herë.
Për fat të mirë ka Menyra me e mire, i cili është jo vetëm më efikas, por edhe shumë më fleksibël. Kërkon përdorimin e funksionit DSUM. Në këtë shembull, ne kemi përdorur diapazonin A1:A500 si diapazonin në të cilin përmbledhim çdo qelizë të nëntë.
Në qelizën E1, futni fjalën Kritere. Në qelizën E2, futni formulën e mëposhtme: =MOD(ROW(A2)-$C$2-1;$C$2)=0 Zgjidhni qelizën C2 dhe zgjidhni Data → Validation.
Në fushën Lloji i të dhënave (Lejo), zgjidhni Lista (Lista) dhe në fushën Burimi (Burimi), shkruani 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Sigurohuni që Lista e Vlerat e lejuara kontrollohen (In-Cell) dhe klikoni butonin OK. Në qelizën C1, shkruani tekstin SUM çdo…. Në çdo qelizë përveç rreshtit 1, futni formulën e mëposhtme: =DSUM($A:$A;1;$E$1:$E$2) :$E$2) .
Në qelizën menjëherë mbi atë ku keni futur funksionin DSUM, shkruani tekstin "Përmbledhja e çdo" & $C$2 & CHOOSE($C$2;"st";"nd";"rd";"th";" th";"th";"th";"th";"th";"th") & "Qeliza" . Tani mbetet vetëm të zgjidhni numrin e dëshiruar në qelizën C2, dhe pjesa tjetër do të bëhet nga funksioni DSUM.
Duke përdorur funksionin DSUM, ju mund të përmbledhni qelizat në intervalet që specifikoni. Funksioni DSUM është shumë më efikas se një formulë grupi ose funksioni SUMPRODUCT. Megjithëse kërkon pak më shumë kohë për t'u vendosur, ky është një rast ku i vështirë për t'u mësuar, i lehtë për t'u luftuar.
Më parë, kam përshkruar se si të përdor një funksion të personalizuar për të gjetur . Fatkeqësisht, ky funksion nuk funksionon nëse qelizat janë të ngjyrosura me formatimi i kushtëzuar. Unë premtova të "përfundoja" funksionin. Por në dy vitet që kanë kaluar nga publikimi i atij shënimi, nuk kam mundur të shkruaj kodin e tretshëm as vetë dhe as me ndihmën e informacionit nga interneti ... ( Përditësimi më 29 mars 2017 Pas pesë vitesh të tjera, unë ende arrita të shkruaj kodin; shih pjesën e fundit të shënimit). Dhe vetëm kohët e fundit kam hasur në një ide që përmban libri i D. Holy, R. Holy "Excel 2007. Tricks", i cili ju lejon të bëni fare pa kod.
Le të ketë një listë numrash nga 1 deri në 100, të vendosur në rangun A1: A100 (Fig. 1; shih gjithashtu fletën "SUMIS" të skedarit Excel). Gama ka një formatim të kushtëzuar që shënjon qelizat që përmbajnë numra më të mëdhenj se 10 dhe më të vegjël ose të barabartë me 20.
Oriz. 1. Gama e numrave; Formatimi i kushtëzuar thekson qelizat që përmbajnë vlera nga 10 në 20
Shkarkoni shënimin në format, shembuj në format
Tani duhet të shtoni vlerat në qelizat që plotësojnë kriteret që sapo keni vendosur. Nuk ka rëndësi se çfarë formatimi aplikohet në këto qeliza, por duhet të dini kriteret me të cilat theksohen qelizat.
Për të shtuar një varg qelizash që korrespondojnë me vetëm kriter, mund të përdorni funksionin SUMIF (Fig. 2).
Oriz. 2. Përmbledhja e qelizave që plotësojnë të njëjtin kusht
Nëse keni disa kushteve, mund të përdorni funksionin SUMIFS (Fig. 3).
Oriz. 3. Përmbledhja e qelizave që plotësojnë kushte të shumta
Mund të përdorni funksionin COUNTIF për të numëruar numrin e qelizave që plotësojnë të njëjtat kritere.
Mund të përdorni funksionin COUNTIFS për të numëruar numrin e qelizave që plotësojnë kritere të shumta.
Excel ofron një funksion tjetër që ju lejon të specifikoni kushte të shumta. Kjo veçori është pjesë e grupit të veçorive bazë. të dhëna excel dhe quhet BDSUMM. Për ta kontrolluar atë, përdorni të njëjtin grup numrash në diapazonin A2:A100 (Fig. 4; shih gjithashtu fletën "BDSUMM" të skedarit Excel).
Oriz. 4. Përdorimi i veçorive të bazës së të dhënave
Zgjidhni qelizat C1:D2 dhe emërtoni këtë varg Kriteret duke e shtypur atë në kutinë e emrit në të majtë të shiritit të formulave. Tani zgjidhni qelizën C1 dhe shkruani =$A$1, që është lidhja me qelizën e parë në fletën e punës që përmban emrin e bazës së të dhënave. Futni =$A$1 në qelizën D1 dhe do të merrni dy kopje të titullit të kolonës A. Këto kopje do të përdoren si tituj për kushtet e BDSUMM (C1:D2), të cilat i keni emërtuar Kriteret. Në qelizën C2, futni >10. Në qelizën D2, shkruani<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM($A$1:$A$101.1, kriteret)
Mund të përdorni funksionin COUNT për të numëruar numrin e qelizave që plotësojnë disa kritere.
Ndërsa lexoja librin e John Walkenbach, mësova se duke filluar me Excel 2010, VBA prezantoi një veçori të re DisplayFormat (shih, për shembull, Vetia Range.DisplayFormat). Kjo do të thotë, VBA mund të lexojë formatin e shfaqur në ekran. Nuk ka rëndësi se si është marrë, nga cilësimet e drejtpërdrejta të përdoruesit ose duke përdorur formatimin e kushtëzuar. Fatkeqësisht, zhvilluesit e MS e bënë atë që vetia DisplayFormat të funksionojë vetëm në procedurat e thirrura nga VBA dhe funksionet e përcaktuara nga përdoruesi bazuar në këtë veti gjenerojnë #VALUE! Sidoqoftë, mund të merrni shumën e vlerave në një gamë mbi qelizat e një ngjyre të caktuar duke përdorur një procedurë (një makro, jo një funksion). Hapur (përmban kodin VBA). Kaloni nëpër menu Pamje -> Makrot -> Makrot; në dritare Makro, theksoni vijën SumColorCond., dhe shtypni Vraponi. Ekzekutoni makro, zgjidhni diapazonin e përmbledhjes dhe kriterin. Përgjigja do të shfaqet në dritare.
Kodi i procedurës
Aplikacioni Sub SumColorCond().Volatile True Dim SumColor si zbehje e dyfishtë i si diapazon i zbehtë si diapazon i zbehtë. ="Zgjedhja e diapazonit", _ Default:=ActiveCell.Address, _ Lloji:=8) " Criterion Query Set CriterionRange = Application.InputBox(_ Prompt:="Zgjidh kriteri i përmbledhjes", _ Title:="Zgjedhja e kritereve", _ Default:=ActiveCell.Address, _ Type:=8) " Mblidhni qelizat "korrekte" Për çdo i në User Range If i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat Brendshme.Ngjyra Pastaj SumColor = SumColor + i Fundi If Next MsgBox SumColor End Sub
Nën SumColorCond() aplikacion. E vërtetë e paqëndrueshme Dim SumColor As Double Dim i As Range Dim User Range si Range Dim CriterionRange as Range Shuma Ngjyra = 0 "Kërkesë për diapazon Cakto Gama e Përdoruesit = Application.InputBox(_ Prompt:="Zgjidh gamën e përmbledhjes", _ Titulli: "Zgjedhja e diapazonit", _ Parazgjedhja:=ActiveCell.Adresa, _ Lloji:=8) “Kriteret e Kërkesës Set CriterionRange = Aplikim . Kutia e hyrjes (_ Prompt := "Zgjidhni kriterin e përmbledhjes", _ Titulli : = "Zgjedhja e kritereve" , _ Default:= ActiveCell. Adresë , _ |
Le të supozojmë se keni raportin e mëposhtëm për shitjet e përfaqësuesve të shitjeve:
Prej tij duhet të zbuloni se sa lapsa shitur nga përfaqësuesi i shitjeve Ivanov V janar.
PROBLEM: Si të përmbledhim të dhënat sipas kritereve të shumta??
ZGJIDHJE: Metoda 1:
BDSUMM(A1:G16;F1;I1:K2)
Në versionin anglisht:
DSUM(A1:G16,F1,I1:K2)
SI PUNON:
Nga baza e të dhënave që specifikuam A1: G16 funksionin BDSUMM merr dhe mbledh të dhënat e kolonës sasi(argumenti" Fusha" = F1) sipas të dhëna në qeliza I1:K2 (Shitësi = Ivanov; Produkte = Lapsa;Muaj = Janar) kriteret.
KUNDËT: Lista e kritereve duhet të jetë në fletë.
SHËNIME: Numri i kritereve të përmbledhjes është i kufizuar nga RAM.
FUSHA E APLIKIMIT: Çdo version i Excel
Metoda 2:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
Në versionin anglisht:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
SI PUNON:
Funksioni SUMPRODUCT gjeneron vargje me vlera TRUE dhe FALSE, sipas kritereve të zgjedhura, në memorien Excel.
Nëse llogaritjet do të kryheshin në qelizat e fletës (për qartësi, unë do të demonstroj të gjithë funksionimin e formulës sikur llogaritjet të bëheshin në fletë, dhe jo në kujtesë), atëherë vargjet do të dukeshin kështu:
Është e qartë se nëse, për shembull, D2=Lapsa, atëherë vlera do të jetë TRUE, dhe nëse D3=Dosje, pastaj FALSE (pasi kriteri i zgjedhjes së produktit në shembullin tonë është vlera Lapsa).
Duke ditur që TRUE është gjithmonë 1 dhe FALSE është gjithmonë 0, ne vazhdojmë të punojmë me vargje si me numrat 0 dhe 1.
Duke shumëzuar vlerat e fituara të vargjeve me njëri-tjetrin në mënyrë sekuenciale, marrim NJË grup zero dhe njësh. Aty ku plotësoheshin të tre kriteret e përzgjedhjes, ( IVANOV, LAPSAT, JANAR) d.m.th. të gjitha kushtet ishin TË VËRTETA, marrim 1 (1*1*1 = 1), por nëse të paktën një kusht nuk plotësohej, marrim 0 (1*1*0 = 0; 1*0*1 = 0; 0* 1* 1 = 0).
Tani mbetet vetëm të shumëzojmë grupin që rezulton me grupin që përmban të dhënat që duhet të përmbledhim si rezultat (varg F2: F16) dhe, në fakt, përmblidhni atë që nuk shumëzohet me 0.
Tani krahasoni grupet e marra me ndihmën e formulës dhe me llogaritjen hap pas hapi në fletë (të theksuara me të kuqe).
Unë mendoj se gjithçka është e qartë :)
MINUSET: SUMPRODUCT - formula e grupit "të rëndë". Kur llogaritni në intervale të mëdha të dhënash, koha e rillogaritjes rritet ndjeshëm.
SHËNIME
FUSHA E APLIKIMIT: Çdo version i Excel
Metoda 3: Formula e vargjeve
SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
Në versionin anglisht:
SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
SI PUNON: Ashtu si metoda numër 2. Ka vetëm dy dallime - formula e dhënë futet duke shtypur Ctrl+Shift+Enter në vend që thjesht të shtypni Hyni dhe grupi i 0 dhe 1 nuk shumëzohet me diapazonin e mbledhjes, por zgjidhet duke përdorur funksionin IF.
MINUSET: Formulat e grupeve kur llogariten në intervale të mëdha të dhënash rrisin ndjeshëm kohën e rillogaritjes.
SHËNIME: Numri i vargjeve të përpunuara është i kufizuar në 255.
FUSHA E APLIKIMIT: Çdo version i Excel
Metoda 4:
SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)