Excel-taulukon kaavat ovat erittäin tehokkaita. Voit korvata tuhannet kaavat yhdellä kaavalla, kun olet oppinut Ctrl + Vaihto + Enter-temppu. Nykyään yksi matriisikaava tekee 86 000 laskutoimitusta.
Triskaidekaphobia on perjantaina 13. päivän pelko. Tämä aihe ei paranna mitään, mutta se näyttää sinulle aivan hämmästyttävän kaavan, joka korvaa 110 268 kaavaa. Tosielämässä minun ei tarvitse koskaan laskea, kuinka monta perjantaia 13. on tapahtunut elämässäni, mutta tämän kaavan voima ja kauneus kuvaa Excelin voimaa.
Sano, että sinulla on taikauskoinen ystävä perjantaina 13. päivänä. Haluat kuvata, kuinka monta perjantaina 13. päivä ystäväsi on elänyt.
Määritä alla oleva yksinkertainen laskentataulukko, jossa syntymäpäivä on B1 ja =TODAY()B2. Sitten B6: n villi kaava arvioi joka päivä, että ystäväsi on ollut elossa selvittääkseen, kuinka moni noista päivistä oli perjantai ja putosi kuukauden 13. päivänä. Minulle luku on 86. Mitään pelättävää.
Muuten, 17.2.1965 on todella syntymäpäiväni. Mutta en halua sinun lähettävän minulle syntymäpäiväkorttia. Sen sijaan, syntymäpäivänäni, haluan sinun antavan minun selittää, kuinka hämmästyttävä kaava toimii, pieni askel kerrallaan.
Oletko koskaan käyttänyt epäsuoraa toimintoa? Kun pyydät =INDIRECT("C3"), Excel siirtyy C3: een ja palauttaa kaiken, mikä on kyseisessä solussa. Epäsuora on kuitenkin tehokkaampi, kun lasket soluviitteen lennossa. Voisit perustaa palkintopyörän, jossa joku poimii kirjaimen A ja C väliltä ja sitten numeron välillä 1 ja 3. Kun liität nämä kaksi vastausta, sinulla on solun osoite, ja mikä tahansa solun osoitteessa on palkinto . Näyttää siltä, että olen voittanut valokuvakirjan lomakeskuksen sijasta.
Tiedätkö kuinka Excel tallentaa päivämääriä? Kun Excel näyttää sinulle 2.17.1965, se tallentaa soluun 23790, koska 17.2.1965 oli 1900-luvun 23790. päivä. Kaavan ytimessä on ketjutus, joka yhdistää alkamispäivän sekä kaksoispisteen ja lopetuspäivän. Excel ei käytä alustettua päivämäärää. Sen sijaan se käyttää sarjanumeroa kulissien takana. Joten siitä B3&":"&B4tulee 23790: 42167. Uskokaa tai älkää, se on kelvollinen soluviite. Jos halusit lisätä kaikki riveillä 3 - 5, voit käyttää =SUM(3:5). Joten kun välität 23790: 42167 EPÄSUORA-funktiolle, se osoittaa kaikkiin riveihin.
Seuraava asia, jonka tappaja kaava tekee, on pyytää ROW(23790:42167). Normaalisti ohitat yhden solun: =ROW(D17)on 17. Mutta tässä tapauksessa ohitat tuhansia soluja. Kun kysyt ROW(23790:42167)ja lopetat kaavan painamalla Ctrl + Vaihto + Enter, Excel palauttaa jokaisen luvun 23790, 23791, 23792 ja niin edelleen aina 42167 asti.
Tämä vaihe on hämmästyttävä askel. Tässä vaiheessa siirrytään kahdesta numerosta ja "pudotetaan" joukko 18378 numeroa. Nyt meidän on tehtävä jotain tällä joukolla vastauksia. Edellisen kuvan solu B9 laskee vain, kuinka monta vastausta saamme, mikä on tylsää, mutta se osoittaa, että ROW(23790:42167)se palauttaa 18378 vastausta.
Yksinkertaistetaan alkuperäistä kysymystä dramaattisesti, jotta voit nähdä, mitä tapahtuu. Tässä tapauksessa löydämme perjantaisten määrän heinäkuussa 2015. Alla kohdassa B7 esitetty kaava antaa oikean vastauksen kohtaan B6.
Kaavan ytimessä on ROW(INDIRECT(B3&":"&B4)). Tämä palauttaa 31 päivämäärää heinäkuussa 2015. Mutta kaava siirtää sitten 31 päivämäärää WEEKDAY(,2)funktiolle. Tämä toiminto palauttaa arvon 1 maanantaina, 5 perjantaina ja niin edelleen. Joten iso kysymys on, kuinka moni näistä 31 päivämäärästä palauttaa 5, kun se välitetään WEEKDAY(,2)funktiolle.
Voit katsella kaavan laskemista hidastettuna käyttämällä Arvioi kaava -komentoa valintanauhan Kaava-välilehdessä.
Tämä tapahtuu sen jälkeen, kun INDIRECT muuntaa päivämäärät riviviittauksiksi.
Seuraavassa vaiheessa Excel välittää 31 numeroa WEEKDAY-funktiolle. Tappajakaavassa se välittäisi 18 378 numeroa 31 sijasta.
Tässä ovat 31 WEEKDAY -toiminnon tulokset. Muista, että haluamme laskea, kuinka monta on 5.
Tarkistamalla, onko edellinen taulukko 5, palautetaan koko joukko True / False-arvoja. Todellisia arvoja on 5, yksi kutakin perjantaia kohti.
En voi näyttää, mitä seuraavaksi tapahtuu, mutta voin selittää sen. Excel ei voi LUKU joukko tosi ja väärä arvoja. Se on sääntöjen vastaista. Mutta jos kerrot tosi ja epätosi arvot yhdellä tai jos käytät kaksois-negatiivista tai N () -funktiota, muunnat tosi arvot arvoksi 1 ja vääriä arvoja arvoksi 0. Lähetä ne SUM- tai SUMPRODUCT-tilaan ja saat saat todellisten arvojen määrän.
Tässä on samanlainen esimerkki laskeaksesi, kuinka monessa kuukaudessa on päivä 13. Tämä on triviaalia ajatella: Jokaisella kuukaudella on 13. päivä, joten vastaus on parempi koko vuodelle 12. Excel tekee matematiikkaa, generoi 365 päivämäärää, lähettää ne kaikki DAY () -funktiolle ja selvittää, kuinka monta loppua ylöspäin kuukauden 13. päivänä. Vastaus on odotetusti 12.
Seuraava kuva on laskentataulukko, joka tekee kaiken logiikan yhden tappajakaavan, joka näkyy tämän aiheen alussa. Olen luonut rivin jokaiselle päivälle, jonka olen ollut elossa. Sarakkeessa B saan päivän päivämäärän (). Sarakkeessa C saan päivämäärän WEEKDAY (). Sarakkeessa D on B yhtä kuin 13? Onko sarakkeessa E C = 5? Kerrotaan sitten D * E muuntamaan True / False arvoksi 1/0.
Olen piilottanut paljon rivejä, mutta näytän sinulle keskellä kolme satunnaista päivää, jotka sattuu olemaan sekä perjantai että 13. päivä.
F18381: n summa on sama 86, jonka alkuperäinen kaava palautti. Hyvä merkki. Mutta tässä laskentataulukossa on 110268 kaavaa. Alkuperäinen tappajakaava tekee kaiken näiden 110 268 kaavan logiikan yhdessä kaavassa.
Odota. Haluan selventää. Alkuperäisessä kaavassa ei ole mitään maagista, joka saa älykkään ja lyhentää logiikkaa. Tämä alkuperäinen kaava tekee todella 110268 vaihetta, todennäköisesti vielä enemmän, koska alkuperäisen kaavan on laskettava ROW () -taulukko kahdesti.
Etsi tapa käyttää tätä ROW(INDIRECT(Date:Date))tosielämässä ja lähetä se minulle sähköpostitse (pub at dot com). Lähetän palkinnon sadalle ensimmäiselle vastaavalle henkilölle. Todennäköisesti ei lomakeskus. Todennäköisesti Big Mac. Mutta niin se menee palkintojen kanssa. Paljon Big Maceja ja ei paljon lomakohteita.
Näin tämän kaavan ensimmäisen kerran viestitaululle vuonna 2003, jonka Ekim. Luotto annettiin Harlan Grovelle. Kaava ilmestyi myös Bob Umlasin kirjassa This Isn't Excel, It's Magic. Mike Delaney, Meni Porat ja Tim Sheets ehdottivat kaikki miinus / miinus-temppua. SUMPRODUCTia ehdottivat Audrey Lynn ja Steven White. Kiitos kaikille.
Katso video
- On olemassa salainen kaavaluokka, jota kutsutaan Array-kaavoiksi.
- Matriisikaava voi tehdä tuhansia välilaskelmia.
- He vaativat usein, että painat Ctrl + Vaihto + Enter, mutta ei aina.
- Paras taulukko taulukoiden kaavoista on Mike Girvinin Ctrl + Vaihto + Enter.
- INDIRECT antaa sinun yhdistämisen avulla rakentaa jotain, joka näyttää soluviittaukselta.
- Päivämäärät on muotoiltu hienosti, mutta ne tallennetaan päivinä 1. tammikuuta 1900 lähtien.
- Kahden päivämäärän yhdistäminen osoittaa useita rivejä Excelissä.
- Tahdon pyytäminen
ROW(INDIRECT(Date1:Date2))"ponnahtaa" ulos joukosta useita peräkkäisiä numeroita - WEEKDAY-toiminnon avulla voit selvittää, onko päivämäärä perjantai.
- Kuinka monta perjantaia tapahtuu heinäkuussa?
- Voit tarkastella kaavan laskemista hidastettuna käyttämällä Arvioi kaava -työkalua
- Kuinka monta 13. osaa tapahtuu tänä vuonna?
- Kuinka monta perjantai 13. päivä tapahtui kahden päivämäärän välillä?
- Tarkista jokaisesta päivämäärästä, onko WEEKDAY perjantai
- Tarkista jokaisesta päivämäärästä, onko PÄIVÄ 13
- Kerro nämä tulokset käyttämällä SUMPRODUCT
- Käytä - muuntaa True / False arvoksi 1/0
Videon transkriptio
Opi Excel podcastista, jakso 2026 - Suosikkikaavani kaikessa Excelissä!
Podcasting koko tätä kirjaa, napsauta "i" oikeassa yläkulmassa päästäksesi soittolistaan!
Selvä, se oli kirjan 30. aihe, olimme tavallaan kaavaosan lopussa tai kaavaosan keskellä, ja sanoin, että minun on sisällytettävä kaikkien aikojen suosikkikaavani. Tämä on vain hämmästyttävä kaava, riippumatta siitä, onko sinun laskettava numero perjantai 13. vai ei, se avaa maailman koko Excelin salaiselle alueelle nimeltä Array Formulas! Laita aloituspäivä, aseta lopetuspäivä, ja tämä kaava laskee näiden kahden päivämäärän välillä tapahtuneen perjantain 13. päivän määrän. Se tekee tosiasiallisesti viisi laskutusta joka ikinen päivä näiden kahden päivämäärän välillä, 91895 laskelmat + SUM, 91896 laskelmat tapahtuvat tämän yhden pienen kaavan sisällä. Nyt tämän jakson loppuun mennessä massiivikaavat kiehtovat sinua. Haluan huomauttaa,Ystäväni Mike Girvinillä on paras taulukko taulukoista nimeltä "Ctrl + Shift" Enter ", tämä on äskettäin tullut sininen kansi, joka oli ennen keltainen ja vihreä kansi. Nyt, kumpi saat, on loistava kirja, saman sisällön sekä keltaisessa että vihreässä.
Selvä, aloitetaan tämän sisältä, kaavalla, jota et ehkä ole kuullut, nimeltään epäsuora. EPÄSUORA antaa meille mahdollisuuden yhdistää tai jollain tavalla rakentaa vähän tekstiä, joka näyttää soluviittaukselta. Selvä, niin sanotaan, että meillä on palkintopyörä täällä, ja pyysin juuri sinua valitsemaan A, B ja C. Hyvä on, joten valitset tämän ja valitset C ja valitset tämän ja valitset 3, kunnossa, ja palkintosi on lomakohde, koska se tallennetaan C3: een. Ja tässä oleva kaava on yhdistetty riippumatta siitä, mikä on C5 ja mikä on C6: ssa, käyttämällä & ja sitten välittämällä sen epäsuoraan. Joten = EPÄSUORA (C5 & C6) on tässä tapauksessa C3, sen on oltava tasapainotettu viite. EPÄSUORA sanoo: "Hei, menemme C3: een ja palautamme vastauksen siitä, okei?" Takaisin Lotus 1-2-3: ssa tätä kutsuttiin funktioksi @@,Excelissä he nimeivät sen uudelleen epäsuoraksi. Selvä, joten sinulla on epäsuora, nyt tässä on hämmästyttävä asia, mitä tapahtuu sen sisällä.
Meillä on kaksi päivämäärää, miten Excel tallentaa päivämäärät, 17.2.1965, joka on oikeastaan vain muotoilua. Jos menimme katsomaan todellista lukua sen takana, se on 23790, mikä tarkoittaa, että se on 23790 päivää 1.1.1900 ja 42167 päivää 1.1.18080. Macissa se on tullut 1.1.1994 lähtien, joten päivämäärät ovat noin 3000 alennusta. Selvä, näin Excel tallentaa sen, se näyttää sen meille kuitenkin tämän numeromuodon ansiosta päivämääränä, mutta jos yhdistämme B3: n ja a:: n ja B4: n, se tosiasiallisesti antaa meille kulissien takana tallennetut numerot. Joten = B3 & ”:” & B4, ja jos välitämme sen epäsuoralle, se osoittaa oikeastaan kaikki rivit 23790: stä 42167: een.
Joten on B6: n EPÄSUORA, pyysin sen riviä, se antaa minulle kokonaisen joukon vastauksia ja selvittää, kuinka monta vastausta käytin. Ja jotta tämä toimisi, jos vain painan Enter-näppäintä, se ei toimi, minun on pidettävä Ctrl ja Shift -näppäimiä painettuna ja painettava Enter-näppäintä. Se käskee Excelin siirtymään superkaavatilaan, matriisikaavamoodiin ja tekemään kaiken matematiikan kaikesta, mikä nousi siitä taulukosta, 18378, kunnossa. Joten, tämä on hämmästyttävä temppu, epäsuora date1: date2, välitä se ROW-funktiolle, ja tässä on pieni esimerkki.
Joten haluamme vain selvittää, kuinka monta perjantaia tapahtui heinäkuussa, tässä on alkamispäivä, tässä lopetuspäivä ja jokaiselle riville aion pyytää WEEKDAY. WEEKDAY kertoo meille, mikä viikonpäivä on, ja täällä, 2 argumentissa, perjantaisin arvo on 5. Etsin siis vastausta, ja aiomme valita tämän kaavan, siirry kaavoihin, ja Arvioi kaava, ja Arvioi kaava on loistava tapa seurata kaavan laskemista hidastettuna. Joten siellä on B3, 1. heinäkuuta, ja huomaat, että se muuttuu numeroksi, ja sitten liitymme kaksoispisteeseen, oikeassa, siellä on B4, joka muuttuu numeroksi, ja nyt saamme tekstin 42186: 42216. Tässä vaiheessa välitämme sen ROW: lle, ja se yksinkertainen pieni lauseke muuttuu 31 arvoksi täällä.
Nyt esimerkissä, jossa minulla oli kaikki vuosina 1965--2015, se ponnahtaa esiin 86000 arvoa, eikö, etkä halua tehdä sitä ja arvioida kaavaa, koska se olisi tavallaan hullua, okei? Mutta voit nähdä, mitä täällä tapahtuu 31: n kanssa, ja nyt siirrän nuo 31 päivää WEEKDAY-toimintoon, ja saamme 3-4-5. Joten 3 tarkoittaa, että se oli keskiviikko, ja sitten 4 tarkoittaa, että se oli torstai, ja sitten 5 tarkoittaa, että se oli perjantai. Ota kaikki nämä 31 arvoa ja katso, ovatko ne = 5, joka on perjantai, ja saamme joukon EPÄTOSIA ja TOSIA, joten keskiviikko, torstai, perjantai ja sitten 7 solua myöhemmin olisi seuraava TOSI, mahtava!
Selvä, joten tässä tapauksessa meillä on 5 TOSIA ja 26 EPÄTOSI. Jotta voin lisätä ne yhteen, minun on muunnettava EPÄTOSI arvoksi 0 ja TOSI arvoksi 1, ja hyvin yleinen tapa tehdä se on käyttää - . Selvä, valitettavasti se ei näyttänyt vastausta siellä, missä näimme kokonaisen joukon 1: tä ja 0: ta, mutta näin tapahtuu, ja sitten SUMPRODUCT lisää sen ja vie meidät viidenneksi tänne, jos haluamme selvittää kuinka monta kuukauden 13. päivää oli tänä vuonna, tästä aloituspäivästä tähän päättymispäivään asti, hyvin samanlainen prosessi. Vaikka meillä on 365, välitä se PÄIVÄ-funktiolle ja tarkista, kuinka moni on 13, kunnossa. 92000 rivin esimerkissä tiedät, saamme päivän, saamme viikonpäivän, tarkistamme onko DAY = 13, tarkistaaksesi, onko WEEKDAY = FALSE, kertomalla tämä * tämä,ja vain tapauksissa, joissa on perjantai 13. päivä, päädytään TOSIIN. SUMPRODUCT sanoo sitten "Lisää kaikki ne ylös", ja näin saamme 86, kirjaimellisesti 91895 laskelmat + SUM, 91896 tämän yhden kaavan sisällä, se on hullu voimakas! Mene ostamaan Miken kirja, se on upea kirja, se avaa sinulle koko maailman Excel-kaavoja, ja sinun pitäisi vain ostaa molemmat kirjat. Osta kirjani, osta Miken kirja, niin sinulla on mahtava kokoelma, joka vie sinut läpi loppuvuoden.se avaa sinulle koko Excel-kaavojen maailman, ja oikeastaan sinun pitäisi vain ostaa molemmat kirjat. Osta kirjani, osta Miken kirja, niin sinulla on mahtava kokoelma, joka vie sinut läpi loppuvuoden.se avaa sinulle koko Excel-kaavojen maailman, ja oikeastaan sinun pitäisi vain ostaa molemmat kirjat. Osta kirjani, osta Miken kirja, niin sinulla on mahtava kokoelma, joka vie sinut läpi loppuvuoden.
Alright, so recap: there's a secret class of formulas called array formulas, and array formula can do thousands of intermediate calculations. They usually require you to press Ctrl+Shift+Enter, but not always, and the best book on array formulas is Mike Girvin’s “Ctrl+Shift+Enter” book. Alright, so INDIRECT lets you use concatenation to build something that looks like a cell reference, and then INDIRECT goes to that cell reference. Concatenating two dates with a colon will point to a range of rows in Excel, and then asking for the ROW of the INDIRECT of date1:date2 will pop out an array of many consecutive numbers, maybe 31, maybe 365, or maybe 85000. Check each date to see if the WEEKDAY=Friday, check each day to see if the DAY=13, multiply those two arrays of TRUEs and FALSEs using the SUMPRODUCT. In many cases we'll use -- to convert the TRUE/FALSE to 1’s and 0’s to allow the SUMPRODUCT to work. It's an awesome formula, I didn't create it, I found it on the message board, as I worked through it, I'm like “Wow, this is really cool!”
Selvä, haluan kiittää sinua vierailustasi, nähdään ensi kerralla uudesta netcastista!
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2026.xlsx
