Exceli massiivi valemid on ülivõimsad. Kui olete õppinud triki Ctrl + Shift + Enter, saate tuhandeid valemeid ühe valemiga asendada. Täna teeb ühe massiivi valem 86 000 arvutust.
Triskaidekafoobia on 13. reede hirm. See teema ei ravi midagi, kuid see näitab teile täiesti hämmastavat valemit, mis asendab 110 268 valemit. Päriselus ei pea ma kunagi lugema, mitu reedet on minu elu jooksul toimunud 13. päeva, kuid selle valemi jõud ja ilu illustreerivad Exceli jõudu.
Oletame, et teil on sõber, kes on reedel, 13. päeval ebausklik. Soovite illustreerida, mitu reedet, 13. päeva on teie sõber läbi elanud.
Seadistage allpool lihtne tööleht, mille sünnikuupäev on B1 ja =TODAY()B2. Seejärel hindab B6 metsik valem iga päev, et teie sõber on elus olnud, et välja selgitada, mitu neist päevadest oli reede ja langes kuu 13. kuupäeval. Minu jaoks on see number 86. Pole midagi karta.
Muide, 17.02.1965 on tõesti minu sünnipäev. Aga ma ei taha, et sa saadaksid mulle sünnipäevakaardi. Selle asemel tahan minu sünnipäevaks, et lasete mul üks väike samm korraga selgitada, kuidas see hämmastav valem töötab.
Kas olete kunagi kasutanud funktsiooni INDIRECT? Kui te seda palute =INDIRECT("C3"), läheb Excel C3-sse ja tagastab kõik, mis selles lahtris on. Kuid INDIRECT on võimsam, kui arvutate lahtriviite lennult. Võiksite seada auhinnaratta, kus keegi valib tähe A ja C vahele ja seejärel numbri vahemikus 1 kuni 3. Kui ühendate need kaks vastust, on teil lahtriaadress ja mis iganes sellel lahtriaadressil on, on auhind . Tundub, et võitsin kuurordi ööbimise asemel fotoraamatu.
Kas teate, kuidas Excel kuupäevi salvestab? Kui Excel näitab teile 17.02.1965, salvestab see lahtrisse 23790, sest 17.02.1965 oli 20. sajandi 23790. päev. Valemi keskmes on liitmine, mis ühendab alguskuupäeva ning kooloni ja lõppkuupäeva. Excel ei kasuta vormindatud kuupäeva. Selle asemel kasutab see telgitaguseid seerianumbrit. Nii B3&":"&B4saab 23790: 42167. Uskuge või mitte, see on kehtiv lahtriviide. Kui soovite lisada kõik ridadesse 3–5, võite neid kasutada =SUM(3:5). Niisiis, kui edastate funktsioonile INDIRECT 23790: 42167, osutab see kõigile ridadele.
Järgmine asi, mida tapja valem teeb, on ROW(23790:42167). Tavaliselt möödute ühest lahtrist: =ROW(D17)on 17. Kuid sel juhul möödute tuhandetest lahtritest. Kui küsite ROW(23790:42167)ja lõpetate valemi klahvikombinatsiooniga Ctrl + Tõst + Enter, tagastab Excel tegelikult iga numbri 23790, 23791, 23792 ja nii edasi kuni 42167.
See samm on hämmastav samm. Selles etapis läheme kahest numbrist ja "hüppame välja" 18378 numbri massiivi. Nüüd peame selle vastuste kogumiga midagi ette võtma. Eelmise joonise lahter B9 lihtsalt loeb, kui palju vastuseid saame, mis on igav, kuid see tõestab, et see annab ROW(23790:42167)tagasi 18378 vastust.
Lihtsustame algset küsimust dramaatiliselt, et näeksite, mis toimub. Sel juhul leiame 2015. aasta juuli reede arvu. Allpool punktis B7 näidatud valem annab õige vastuse punktis B6.
Valemi keskmes on ROW(INDIRECT(B3&":"&B4)). See tagastab 31 kuupäeva juulis 2015. Kuid valem edastab need 31 kuupäeva WEEKDAY(,2)funktsioonile. See funktsioon tagastab esmaspäevaks 1, reedeks 5 ja nii edasi. Nii et suur küsimus on see, kui paljud neist 31 kuupäevast tagastavad WEEKDAY(,2)funktsioonile edastamise 5.
Valemi arvutamist saate vaadata aegluubis, kasutades lindi vahekaardi Vormel käsku Hinda valemit.
See toimub pärast seda, kui INDIRECT teisendab kuupäevad reaviiteks.
Järgmises etapis edastab Excel WEEKDAY funktsioonile 31 numbrit. Nüüd edastaks see tapjavalemis 31 asemel 18 378 numbrit.
Siin on 31 WEEKDAY funktsiooni tulemused. Pidage meeles, et tahame kokku lugeda, kui palju on 5.
Kontrollides, kas eelmine massiiv on 5, tagastatakse terve hulk tõese / vale väärtusi. Tõelisi väärtusi on 5, üks igaks reedeks.
Ma ei saa teile näidata, mis edasi saab, kuid oskan seda selgitada. Excel ei saa KOKKU hunnikut tõeseid ja valesid väärtusi. See on reeglitega vastuolus. Kuid kui korrutate need tõesed ja väärad väärtused ühega või kui kasutate funktsiooni topeltnegatiivne või N (), teisendate tõesed väärtused väärtuseks 1 ja valeväärtused väärtuseks 0. Saada need väärtusele SUM või SUMPRODUCT ja saate saada tegelike väärtuste arv.
Siin on sarnane näide, et lugeda, mitu kuud on 13. päev. See on triviaalne mõelda: igal kuul on 13. kuupäev, nii et vastus terve aasta paremaks oleks 12. Excel teeb matemaatikat, genereerib 365 kuupäeva, saadab need kõik funktsiooni PÄEV () ja mõtleb välja, kui palju lõpeb üles kuu 13. kuupäeval. Vastus on ootuspäraselt 12.
Järgmine joonis on tööleht, mis täidab kogu selle teema alguses näidatud ühe tapja valemi loogika. Olen loonud rea igaks päevaks, mil olen elus olnud. Veerus B saan selle kuupäeva PÄEV (). Veerus C saan kuupäeva WEEKDAY (). Kas veerus D on B võrdne 13-ga? Kas veerus E on C = 5? Korrutan seejärel D * E, et teisendada õige / väär väärtuseks 1/0.
Olen palju ridu peitnud, kuid näitan teile keskel kolme juhuslikku päeva, mis on juhtumisi nii reede kui ka 13. kuupäev.
F18381 kogusumma on sama 86, mille mu algne valem tagastas. Suurepärane märk. Kuid sellel töölehel on 110 268 valemit. Minu algne tapjavalem teeb kogu 110 1108 valemi loogika ühes valemis.
Oota. Tahan täpsustada. Algses valemis pole midagi maagilist, mis saaks nutikaks ja lühendaks loogikat. See algne valem teeb tõesti 110 268 sammu, tõenäoliselt isegi rohkem, sest algne valem peab ROW () massiivi arvutama kaks korda.
Leidke võimalus seda ROW(INDIRECT(Date:Date))päriselus kasutada ja saatke see mulle e-kirjaga (pub at dot com). Esimesele sajale inimesele saadan vastuse auhinna. Ilmselt mitte kuurordis viibimine. Tõenäoliselt Big Mac. Aga nii läheb auhindadega. Palju Big Mace ja vähe kuurordi ööbimisi.
Esimest korda nägin seda valemit teadetetahvlile postitatuna 2003. aastal Ekimi poolt. Au anti Harlan Grove'ile. Valem ilmus ka Bob Umlase raamatus "See pole Excel, see on maagia". Mike Delaney, Meni Porat ja Tim Sheets pakkusid kõik välja miinus / miinus-triki. SUMPRODUCTi pakkusid välja Audrey Lynn ja Steven White. Tänan teid kõiki.
Vaata videot
- On olemas salajane valemiklass, mida nimetatakse massiivvalemiteks.
- Massiivvalemiga saab teha tuhandeid vahearvutusi.
- Sageli peavad nad vajutama klahvikombinatsiooni Ctrl + Tõst + Enter, kuid mitte alati.
- Parim massiivivalemite raamat on Mike Girvini Ctrl + Tõst + Enter.
- INDIRECT võimaldab teil rakendada võrgu abil midagi, mis näeb välja nagu lahtriviide.
- Kuupäevad on kenasti vormindatud, kuid salvestatakse päevade arvuna alates 1. jaanuarist 1900.
- Kahe kuupäeva liitmine osutab Exceli ridade vahemikule.
- Testamendi küsimine
ROW(INDIRECT(Date1:Date2))"hüppab välja" paljude järjestikuste arvude massiivi - Funktsiooni WEEKDAY abil saate teada, kas kuupäev on reede.
- Mitu reedet toimub juulis?
- Aegluubis arvutatava valemi vaatamiseks kasutage tööriista Valemi hindamine
- Mitu 13. kohta toimub sel aastal?
- Mitu reedet, 13. kuupäeva juhtus kahe kuupäeva vahel?
- Kontrollige igast kuupäevast, kas WEEKDAY on reede
- Kontrollige igast kuupäevast, kas PÄEV on 13
- Korrutage need tulemused, kasutades SUMPRODUCT
- Kasutage - õige / vale teisendamiseks väärtuseks 1/0
Video ärakiri
Õppige Exceli podcastist, osa 2026 - Minu lemmikvalem kogu Excelis!
Terve selle raamatu taskuhäälingu edastamiseks klõpsake esitusloendisse pääsemiseks paremas ülanurgas oleval nupul „i”!
Hea küll, see oli raamatus 30. teema, me olime nagu valemiosa lõpus või valemisektsioonide keskel ja ütlesin, et pean lisama oma kõigi aegade lemmikvalemi. See on lihtsalt hämmastav valem, olenemata sellest, kas peate loendama reede 13. kuupäeva või mitte, see avab maailma kogu Exceli salajase ala nimega Massiivivalemid! Sisestage alguskuupäev, sisestage lõppkuupäev ja see valem arvutab nende kahe kuupäeva vahel toimunud reede 13. kuupäeva arvu. Tegelikult teeb see kahe kuupäeva vahel igal päeval viis arvutust, 91895 arvutust + SUM, 91896 arvutust, mis toimuvad selle ühe väikese valemi sees. Nüüd, selle osa lõpuks, hakkavad teid massiivalemid nii huvitama. Ma tahan juhtida tähelepanuminu sõbral Mike Girvinil on parim massiivivalemite raamat nimega “Ctrl + Shift” Enter ”, see on hiljutine sinise kaanega trükk, varem oli see kollane ja roheline kaas. Nüüd, ükskõik mille saate, on suurepärane raamat, sama sisu nii kollases kui ka rohelises.
Olgu, alustame selle sisemusest valemiga, mida te pole võib-olla kuulnud, nimega KOHE. INDIRECT võimaldab meil liita või mingil viisil luua natuke teksti, mis näeb välja nagu lahtriviide. Olgu, oletame, et meil on siin auhinnaratas ja ma lihtsalt palusin teil valida A, B ja C. Hästi, nii et valite selle ja valite C, seejärel valige see ja valige 3, olgu, ja teie auhind on kuurordi majutus, sest just see on salvestatud C3-sse. Ja siinne valem on kokku liidetud olenemata C5-st ja mis tahes C6-st, kasutades & ja seejärel edastades selle kaudsele. Nii et = INDIRECT (C5 ja C6) on antud juhul C3, mis peab olema tasakaalustatud viide. INDIRECT ütleb: "Hei, me läheme C3-sse ja anname sellele vastuse, eks?" Tagasi Lotus 1-2-3-s nimetati seda funktsiooniks @@,Excelis nimetasid nad selle ümber KAUDSEKS. Hästi, nii et teil on kaudne KOHE, nüüd on siin hämmastav asi, mis seal sees toimub.
Meil on kaks kuupäeva, kuidas Excel kuupäevi salvestab, 17.02.1965, see on tegelikult lihtsalt vormindamine. Kui me läksime ja vaatasime selle taga olevat tegelikku arvu, on see 23790, mis tähendab, et 01.01.1900 on 23790 päeva ja 1.01.18080 on 42167 päeva. Macis on see alates 1. jaanuarist 1904, seega on kuupäevad umbes 3000 soodsamad. Hea küll, nii see Excel salvestab, näitab seda meile siiski tänu sellele numbrivormingule kui kuupäevale, kuid kui me ühendaksime B3 ja a: ning B4, annaks see meile tegelikult kaadri taha salvestatud numbrid. Nii = B3 & ”:” & B4, ja kui me annaksime selle edasi KOHE, suunab see tegelikult kõik read 23790-st 42167-ni.
Nii et seal on B6 KAUDSE, ma küsisin selle RIDA. See annab mulle terve hulga vastuseid ja selgitab välja, kui palju vastuseid ma kasutasin. Ja selleks, et see toimiks, kui ma lihtsalt vajutan Enter, siis see ei toimi, pean hoidma all Ctrl ja Shift ning vajutama Enter ja nägema, mis lisab siin valemi ümber oleva (). See käsib Excelis minna ülivalemirežiimi, massiivvalemirežiimi ja teha kogu matemaatika kõigeks, mis sellest massiivist 18378 välja tuli. Nii et see on hämmastav trikk, mis on kaudselt date1: date2, andke see funktsioon ROW ja siin on väike näide.
Nii et me tahame lihtsalt välja mõelda, mitu reedet sel juulil toimus, siin on alguskuupäev, siin on lõppkuupäev ja kõigi nende ridade jaoks, mida ma WEEKDAY palun. WEEKDAY ütleb meile, mis nädalapäev see on ja siin on 2 argumendi reeded väärtuseks 5. Nii et ma otsin vastust ja me valime selle valemi, minge valemitesse, ja valemi hindamine ja valemi hindamine on suurepärane viis vaadata, kuidas valem arvutatakse aegluubis. Nii et seal on B3, 1. juuli, ja näete, et see muutub numbriks, ja siis liitume kooloniga, eks, seal on B4, mis muutub numbriks, ja nüüd saame teksti 42186: 42216. Siinkohal edastame selle ROW-le ja see lihtne väike väljend muutub siin 31 väärtuseks.
Nüüd, näites, kus mul oli kõik aastatel 1965–15, hüppas see välja 86000 väärtust, eks, ja te ei soovi seda teha ja valemit hinnata, sest see oleks kuidagi hull, olgu? Kuid näete, mis siin 31-ga toimub, ja nüüd kannan need 31 päeva WEEKDAY funktsiooni ja saame 3-4-5. Nii et 3 tähendab, et see oli kolmapäev ja siis 4 tähendab, et see oli neljapäev, ja siis 5 tähendab, et see oli reede. Võtke kõik need 31 väärtust ja vaadake, kas need on = 5, mis on reede, ja me saame hulga FALSE ja TRUE, nii et kolmapäev, neljapäev, reede ja siis 7 lahtrit hiljem oleks järgmine TÕENE, vinge!
Hästi, nii et sel juhul on meil 5 TÕENE ja 26 VÄÄRAT. Nende liitmiseks pean teisendama FALSE väärtuseks 0 ja TÕENE väärtuseks 1 ning väga levinud viis selleks on - . Hästi, kahjuks ei näidanud see vastust seal, kus nägime tervet hunnikut 1-sid ja 0-sid, kuid tegelikult see juhtub, ja siis SUMPRODUCT liidab selle ja viib meid 5. siia, kui tahame arvake välja, kui palju oli sel aastal kuu 13. kuupäeva, alates sellest alguskuupäevast kuni selle lõpukuupäevani, väga sarnane protsess. Kuigi meil on 365, edastage see funktsioon PÄEVale ja kontrollige, kui palju on 13, olgu. 92000 rea näite puhul teate, et me saame päeva, saame nädalapäeva, kontrollime, kas, PÄEV = 13, kontrollime, kas WEEKDAY = FALSE, korrutades selle * selle,ja ainult siis, kui on reede 13. kuupäev, jõuab see lõpuks TÕELISEKS. Seejärel on SUMPRODUCTis kirjas „Lisage kõik need üles” ja nii saame selle ühe valemi sees 86, sõna otseses mõttes 91895 + SUM, 91896, see on hullult võimas! Minge, ostke Mike'i raamatut, see on hämmastav raamat, see avab teile terve Exceli valemite maailma ja tegelikult peaksite lihtsalt mõlemad raamatud ostma. Osta minu raamat, osta Mike'i raamat ja sul on vinge kollektsioon, mis viib su kogu ülejäänud aasta läbi.see avab teile terve Exceli valemite maailma ja tegelikult peaksite lihtsalt mõlemad raamatud ostma. Osta minu raamat, osta Mike'i raamat ja sul on vinge kollektsioon, mis viib su kogu ülejäänud aasta läbi.see avab teile terve Exceli valemite maailma ja tegelikult peaksite lihtsalt mõlemad raamatud ostma. Osta minu raamat, osta Mike'i raamat ja sul on vinge kollektsioon, mis viib su kogu ülejäänud aasta läbi.
Olgu, nii et võtke uuesti kokku: seal on salajane valemiklass, mida nimetatakse massiivivalemiteks, ja massiivivalemiga saab teha tuhandeid vahearvutusi. Tavaliselt nõuavad nad, et vajutaksite klahvikombinatsiooni Ctrl + Tõst + Enter, kuid mitte alati. Massiivivalemite parim raamat on Mike Girvini raamat "Ctrl + Tõst + Sisesta". Hea küll, nii et INDIRECT laseb sul rakendada midagi, mis näeb välja nagu lahtriviide, ja siis läheb INDIRECT selle lahtriviite juurde. Kahe kuupäeva liitmine kooloniga osutab Exceli ridade vahemikule ja küsib seejärel kuupäeva INDIRECT rea Rida: date2 hüppab välja paljude järjestikuste arvude massiivi, võib-olla 31, võib-olla 365 või võib-olla 85000. Kontrollige igal kuupäeval, et näha, kas WEEKDAY = reede, kontrollige iga päev, kas PÄEV = 13, korrutage need kaks TÕENE ja VÄÄR massiivi, kasutades SUMPRODUCT. Paljudel juhtudelKasutan - TRUE / FALSE teisendamiseks 1-ks ja 0-ks, et võimaldada SUMPRODUCTi tööd. See on vinge valem, ma ei loonud seda, vaid leidsin selle teadetetahvlilt, kui ma seda läbi töötasin, olen nagu "Wow, see on väga lahe!"
Hästi, ma tahan tänada teid peatumast, näeme järgmine kord järgmise netisaate jaoks!
Laadige fail alla
Laadige näidisfail alla siit: Podcast2026.xlsx
