Slimming valitud rakud Excelis. Excel. Rakkude arvestamine ja kokkupanek, mis vastavad tingimusliku vormingu kriteeriumile
Sageli juhtub sageli, et peate kokku võtma iga teise, kolmanda, neljanda jne kokkuvõtmise tabelisse. Nüüd tänu järgmisele trikkile saab seda teha.
Excel ei anna standardset funktsiooni, mis võib igaüks kokku võtta n-per raku või string. Siiski saate seda ülesannet teha mitmes erinevalt. Kõik need lähenemisviisid põhinevad stringifunktsioonidel (rida) ja jäävad (mod).
Rida funktsioon (rida) Tagastab määratud lingi liini numbri rakule: rida (viide) Exceli reas (link) vene versioonis.
Funktsioon vasakule (mod) Tagastab arvu jaotumise tasakaalu: Mod (number; Divisor), Excli vene versioonis jääb (number; jagaja).
Asetage liinifunktsioon (rida) funktsiooni (mod) (mod) (numbrilise argumendi ülekandmiseks), jagage 2-ga (kokkuvõtte iga teise raku kokku) ja kontrollige, kas tulemus on null. Kui jah, siis rakk on kokku võetud. Neid funktsioone saab kasutada mitmel viisil - mõned tagavad parima tulemuse kui teised. Näiteks massiivi valem summeerimiseks iga teise raku vahemikus $ 1 1: $ ja $ 100 võiks välja näeb: \u003d summa (kui (mod (rida ($ 1: $ $ 500); 2) \u003d \u003d) 0; $ 1: $ $ 500; 0)), Excel'i vene versioonis \u003d summad (if (jääb (string ($ 1: $ $ 500); 2) \u003d 0; $ A $ 1: $ ja $ 500; 0)).
Kuna see on massiivi valem, peate sisestama, vajutades Ctrl + Shift + Enter klahvi kombinatsiooni, Excel lisab traksid välja nägema: (\u003d summa (kui (MOD (Rida ($ 1: $ $ 500), 2) \u003d 0; $ 1: $ $ 500; 0))), Excel'i vene versioonis: (\u003d kogused (jääb (jääb (string ($ 1 1: $ $ 500); 2) \u003d \u003d) \u003d \u003d 0; $ 1: $ 500 $; 0))) On vaja, et Excel ise lisab joonise sulgud; Kui lisate need ise, ei tööta valem.
Kuigi eesmärk on saavutatud, mõjutab see meetod kahjulikult disaini. arvutustabeli. See on massiivi valemi tarbetu kasutamine. Juhtumi halvenemise korral investeeritakse selle pika valemiga ümberarvutatud funktsioon, mis teisendab suure valemi ka ümber arvutatud. See tähendab, et seda tõlgitakse pidevalt, mida te töövihikus teete. See on väga halb tee!
Siin on veel üks valem, mis on natuke parim valik: \u003d Sumprodukt ((mod (rida $ 1: $ $ 500); 2) \u003d 0) * ($ 1: $ $ 500 $ 500)), Exceli vene versioonis \u003d sumpracy (((( String ($ 1: $ $ 500); 2) \u003d 0) * ($ 1: $ $ 500)).
Siiski on vaja meeles pidada, et see valem tagastab vea # tähenda! (#Value!) Kui mis tahes vahemikud ei sisalda numbreid, vaid teksti. See valem, kuigi see ei ole tegelikult massiivi valem aeglustab excel tööKui kasutate seda liiga palju kordi või kui iga kord, kui see viitab suurele vahemikule.
Õnneks on olemas parim viis, mis ei ole mitte ainult tõhusam, vaid ka palju paindlikum lahendus. See nõuab BDSMM-funktsiooni (DSUM) kasutamist. Selles näites kasutasime vahemikus A1: A500 vahemikku, milles on vaja kokku võtta iga n-m rakku.
E1-rakus sisestage sõna kriteeriumid. E2 rakus, sisestage järgmine valem: \u003d Mod (rida (A2) - $ C $ 2-1; $ C $ 2) \u003d 0, Vene versioon Excel \u003d jääb (string (A2) - $ C $ $ C $ 2-1; $ q $ 2) \u003d 0. Valige C2-rakk ja valige Andmed → Kontrollige (Andmed → valideerimine).
Andmeliikis (Luba) valdkonnas valige loend (loend) ja lähtekvaliteedil (allikas), sisestage 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Veenduge, et loend on märkeruut kontrollitakse. lubatud väärtused (In-Cell) ja klõpsake nuppu OK. C1-rakus sisestage summa tekst iga .... Iga rakus, välja arvatud rida 1, sisestage järgmine valem: \u003d DSUM ($ a: $ a; 1; $ E $ 1: $ E $ 2), Vene versioon Excel \u003d BDSMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
Rakus otse üle selle, kus sisestasite BDSMM-funktsiooni (DSUM), sisestage tekst \u003d "Summer iga" & $ $ 2 ja vali ($ C $ 2; St ";" RD ";" RD ";" RD "; "Th"; "th"; "th"; "th"; "th"; "th") ja "rakk". Nüüd jääb see ainult valida soovitud number C2-rakus ja ülejäänud teeb BDSMM funktsiooni (DSUM).
Kasutades BDSMM-funktsiooni (DSUM), saate rakkude kokku võtta kindlaks määratud intervalliga. BDSMM (DSUM) funktsioon on palju tõhusam kui massiivi valem või sumproduct funktsioon. Kuigi seade võtab veidi kauem aega, siis see on nii, kui on raske õppida, lihtne lahing.
Varem kirjeldasin kasutajafunktsiooni. Kahjuks see funktsioon ei tööta, kui rakud on värvitud tingimuslik vormindamine. Ma lubasin funktsiooni "lõpetada". Kuid kahe aasta jooksul, mis edastati märkuse avaldamisest, ei saanud ma iseseisvalt ega internetiühenduse abiga vastata vastaja koodi kirjutamiseks ... ( Update 29. märtsil 2017 Viis aastat hiljem õnnestus mul endiselt koodi kirjutada; Vaadake märkmete lõplikku loetelu). Ja hiljuti tulin I-i 2007. aasta raamatus sisalduva idee D. Choli, R. Holi "Bookis. Trikid", mis võimaldab teil teha ilma koodita.
Oletame, et on olemas nimekiri numbritest 1 kuni 100 asendisse vahemikus A1: A100 (joonis 1; vt ka leht "Smere" Exceli fail). Kasutatud kohaldatav tingimuslik vormindamine, märgistamisrakud, mis sisaldavad numbreid rohkem kui 10 ja vähem või võrdne 20-ga.
Joonis fig. 1. Numbrite vahemik; Tingimuslik vormindamine esiletõstetud rakud sisaldavad väärtusi 10 kuni 20
Allalaadimise märkus vormingus, näited formaadis
Nüüd on vaja korduda väärtused rakkudes, mis vastavad just installitud kriteeriumile. See ei ole oluline, millist vormingut rakendatakse nendele rakkudele, kuid see on vaja teada kriteeriumi vastavalt rakkude eraldamisele.
Klappida rakkude vahemik, mis vastavad Üks Kriteerium, saate kasutada vaikse funktsiooni (joonis 2).
Joonis fig. 2. Ühe tingimusega vastavate rakkude kokkupanek
Kui teil on mõned Tingimused, saate kasutada Summellimoni funktsiooni (joonis 3).
Joonis fig. 3. Mitmed tingimused vastavad rakkude kokkuvõtmine
Et lugeda ühe kriteeriumiga vastavate rakkude arvu, saate kasutada meetri funktsiooni.
Arvutada mitmeid kriteeriume vastavaid rakkude arvu, saate kasutada MeckeStimn funktsiooni.
Excel pakub teist funktsiooni, mis võimaldab määrata mitmeid tingimusi. See funktsioon on lisatud andmebaasi funktsiooni komplektis. eXCEL-andmed Ja nimetatakse bddrummiks. Selle kontrollimiseks kasutage sama numbrit A2 vahemikus: A100 (joonis 4; vt ka BDSMM-i nimekirja Excel-faili).
Joonis fig. 4. Andmebaasi funktsioonide kasutamine
Valige C1: D2 rakud ja määrake selle vahemiku kriteeriumi nimi, sisestades selle vasakpoolse nime all valemi stringist. Nüüd valige C1-rakk ja sisestage \u003d $ 1 $ 1, st viide esimese raku lehel, mis sisaldab andmebaasi nime. Sisestage \u003d $ A $ 1 D1-raku ja saate kaks veeru päise koopiat. Neid koopiaid kasutatakse BDSMMi tingimuste pealkirjadeks (C1: D2), mida sa kriteeriumi nimetasid. C2-rakus sisestage\u003e 10. D2-raku sisenemisel<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSMM ($ 1: $ ja $ 101,1, kriteerium)
Mitme kriteeriumiga vastavate rakkude arvu arvutamiseks saate BSc funktsiooni kasutada.
Raamatu lugemine John Waolenbach ma sain teada, et alates Excel 2010 versioonist ilmus VBA-s uus Displayformati vara (vt näiteks Range.Displayformati vara). Need., VBA saab lugeda ekraanil kuvatavat vormi. See ei ole oluline, kuidas see saadi, otsesed kasutaja seaded või tingimusliku vormindamise teel. Kahjuks on MS arendajad teinud nii, et DisplayFormati vara töötab ainult VBA-lt protseduurides ja sellel varal põhinevad kasutajate funktsioonid annavad vea # tähendus! Siiski on võimalik saada väärtuste kogust vahemikus konkreetse värvi rakkudes, kasutades protseduuri (makro, kuid mitte töötamine). Avatud (sisaldab VBA-koodi). Mine menüü läbi Vaade -> Makrod -> Makrod; aknas MakroTõstke joon Sumyzvetusja vajutage Teostama. Käivitage makro, valige summeerimise vahemik ja kriteeriumid. Vastus ilmub aknasse.
Menetluse kood
Sub Sumyzvetusl () Application.Volatiilne TRUE DIM SUMCOLOR AS Double Dim CrictionRange AS RANKE SUMCOLOR \u003d 0 "Taotluse komplekt USERRANCE \u003d Application.Inputbox (_ Küsi: \u003d" Vali summeerimise vahemik " : \u003d ActivEcell.Address, _ Type: \u003d 8) "Kriteeriumide taotlus Kriteerium kriteerium \u003d rakendus.InPutbox (_ Küsi: \u003d" kokkuvõte" KriteeriumRenge.Displayformat. Interjoy.Color Siis Sumcolor \u003d Sumcolor + I Lõpeta, kui järgmine msgbox sumcolor End Sub
Subyzvetus () Rakendus. Volatiilne tõsi. Dim Sumcolori topelt Hämara i vahemikus Dim Userresrange vahemikus Dim CrictionRange vahemikus Sumcolor \u003d 0. "Taotluse vahemik Määra Userrerange \u003d rakenduste.Inputbox (_ Küsi: \u003d "Valige summeerimise vahemik", _ Pealkiri: \u003d "Range valik", _ Vaikimisi: \u003d ActiveCell.Address, _ Tüüp: \u003d 8) "Taotlus Määra kriteerium \u003d rakendus. Sisendkast (_ Küsi: \u003d. "Valige summeerimise kriteerium", _ Pealkiri: \u003d "Kriteeriumide valik", _ Vaikimisi: \u003d ActiveCell. Aadress, _ |
Oletame, et teil on see aruanne müügiesindajate müügi kohta:
Sellest peate teada saama, kui palju pliiatsid Ütles müügiesindaja Ivanov sisse jaanuar.
Probleem: Kuidas kokku võtta andmeid mitme kriteeriumi kohta?
Otsus: 1. meetod:
BDSMM (A1: G16; F1; I1: K2)
Inglise keeles:
DSUM (A1: G16, F1, I1: K2)
KUIDAS SEE TÖÖTAB:
Meilt määratud andmebaasist A1: G16. ülesanne Bddrumm Eemaldab ja summas veeru andmed summa (argument " Ala" = F1.) rakkude rakkude kohaselt I1: K2. (Müüja \u003d Ivanov; Tooted \u003d pliiatsid; Kuu \u003d jaanuar) Kriteeriumid.
Miinused: kriteeriumide loetelu peaks olema lehel.
Märkused: Kommentamiskriteeriumide arv piirdub RAM-iga.
Rakenduspiirkond: Excli iga versioon
2. meetod:
Summer ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
Inglise keeles:
Sumproduct ((B2: B16 \u003d i2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
KUIDAS SEE TÖÖTAB:
SUMP funktsioon genereerib massiivid tõe ja vale väärtustest vastavalt valitud kriteeriumidele, Excel mälus.
Kui arvutused viidi läbi lehtrakkudes (selguse huvides, näitan ma kogu valemi tööd, justkui arvutused toimuvad lehel ja mitte mällu), siis maineid näevad välja selline:
Ilmselgelt, kui näiteks D2 \u003d pliiatsidSiis väärtus on võrdne tõe ja kui D3 \u003d kaustad, seejärel vale (kui kaupade valiku kriteerium meie näites on väärtus Pliiatsid).
Teades, et tõe väärtus on alati võrdne 1-ga ja vale on alati võrdne 0-ga, jätkame tööd massiividega nagu numbrite 0 ja 1 puhul.
Vahelduvad saadud väärtused massiivid omavahel järjekindlalt saame ühe massiivi nonols ja üksused. Kus kõik kolm valikukriteeriumi teostati ( Ivanov, pliiatsid, jaanuar) s.t. Kõik tingimused võtsid tõe väärtused, et saada 1 (1 * 1 * 1 \u003d 1), kuid kui ainult üks tingimus ei olnud rahul - saame 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).
Nüüd jääb see ainult selleks, et korrutada saadud massiivi massiivile, mis sisaldab andmeid, mida peame kokku võtma (vahemik F2: F16) Ja tegelikult, et kokku 0 ei korruta.
Nüüd võrrelda saadud valemist ja samm-sammult arvutamisel massiivide lehel (punaselt esile tõstetud).
Ma arvan, et kõik on selge :)
Miinused: Kokkuvõte. - Massiivi "raske" valem. Suureandmete vahemike arvutamisel suureneb ümberarvumise aeg oluliselt.
Märkused
Rakenduspiirkond: Excli iga versioon
3. meetod: massiivne valem
Summa (kui (((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))
Inglise keeles:
Summa (kui ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16)
KUIDAS SEE TÖÖTAB: Just nagu meetodi number 2. On ainult kaks erinevust - see valem sisestatakse vajutades Ctrl + Shift + Entermitte ainult vajutades SISENEMA Ja massiivi 0h ja 1-C ei korrutatud summeerimise vahemikus ja on valitud funktsiooni abil, kui.
Miinused: Massiivi valemid suuremate andmete arvutamisel suurendavad oluliselt ümberarvumise aega.
Märkused: Massiivi massiivide arv on piiratud 255-ga.
Rakenduspiirkond: Excli iga versioon
Meetod 4:
Smerevüülmn (F2: F16; B2: B16; I2; D2: D16; J2; A2: A16; K2)