Excel masīvu formulas ir ļoti jaudīgas. Kad esat iemācījies triku Ctrl + Shift + Enter, jūs varat aizstāt tūkstošiem formulu ar vienu formulu. Šodien viena masīva formula veic 86 000 aprēķinu.
Triskaidekaphobia ir bailes no piektdienas, 13. datuma. Šī tēma neko neizārstēs, bet parādīs pilnīgi pārsteidzošu formulu, kas aizstāj 110 268 formulas. Reālajā dzīvē man nekad nav jāskaita, cik piektdienas, 13. dienas, ir bijis manā mūžā, bet šīs formulas spēks un skaistums ilustrē Excel spēku.
Sakiet, ka jums ir draugs, kurš ir māņticīgs par piektdienu, 13. datumu. Jūs vēlaties ilustrēt, cik piektdienas, 13. datuma, jūsu draugs ir pārdzīvojis.
Zemāk iestatiet vienkāršo darblapu ar dzimšanas datumu B1 un =TODAY()B2. Tad savvaļas formula B6 katru dienu novērtē, ka tavs draugs ir bijis dzīvs, lai noskaidrotu, cik no šīm dienām bija piektdiena un nokrita mēneša 13. datumā. Man šis skaitlis ir 86. Nav ko baidīties.
Starp citu, 1965. gada 17. februāris tiešām ir mana dzimšanas diena. Bet es nevēlos, lai jūs man atsūtītu dzimšanas dienas kartīti. Tā vietā manai dzimšanas dienai es vēlos, lai jūs ļautu man paskaidrot, kā šī apbrīnojamā formula darbojas, pa vienam mazam solim.
Vai esat kādreiz izmantojis funkciju NETIEŠAIS? Kad jūs to pieprasīsit =INDIRECT("C3"), Excel pāriet uz C3 un atgriezīs visu, kas atrodas šūnā. Bet NETIEŠAIS ir jaudīgāks, ja aprēķināt šūnu atsauci lidojuma laikā. Jūs varētu izveidot balvu riteni, kur kāds izvēlas burtu starp A un C un pēc tam izvēlas skaitli no 1 līdz 3. Kad jūs savienojat abas atbildes, jums būs šūnu adrese, un tas, kas atrodas šajā šūnas adresē, ir balva . Izskatās, ka es ieguvu fotogrāfiju grāmatu kūrorta uzturēšanās vietā.
Vai jūs zināt, kā programma Excel glabā datumus? Kad Excel rāda jums 17.02.1965, tā šūnā glabā 23790, jo 1965.02.17. Bija 20. gadsimta 23790. diena. Formulas pamatā ir savienojums, kas savieno sākuma datumu, kolu un beigu datumu. Excel neizmanto formatēto datumu. Tā vietā tā izmanto sērijas numuru aiz ainas. Tātad B3&":"&B4kļūst 23790: 42167. Ticiet vai nē, bet tā ir derīga šūnu atsauce. Ja vēlaties visu saskaitīt 3. – 5. Rindā, varat izmantot =SUM(3:5). Tātad, kad jūs nododat funkciju 23790: 42167 funkcijai NETIEŠA, tā norāda uz visām rindām.
Nākamā lieta, ko slepkavas formula dara, ir lūgt ROW(23790:42167). Parasti jūs iet garām vienai šūnai: =ROW(D17)ir 17. Bet šajā gadījumā jūs iet garām tūkstošiem šūnu. Kad jūs pieprasāt ROW(23790:42167)un pabeidzat formulu ar Ctrl + Shift + Enter, Excel faktiski atgriež katru skaitli no 23790, 23791, 23792 un tā tālāk līdz 42167.
Šis solis ir pārsteidzošs solis. Šajā solī mēs ejam no diviem skaitļiem un “izlecam” 18378 skaitļu masīvu. Tagad mums ir kaut kas jādara ar šo atbilžu masīvu. Iepriekšējā attēla B9 šūna tikai saskaita, cik daudz atbilžu mēs saņemam, kas ir garlaicīgi, bet tas pierāda, ka ROW(23790:42167)tiek atgrieztas 18378 atbildes.
Dramatizēsim sākotnējo jautājumu, lai jūs varētu redzēt, kas notiek. Šajā gadījumā mēs atradīsim 2015. gada jūlija piektdienu skaitu. Zemāk B7 attēlotā formula sniedz pareizo atbildi B6.
Formulas pamatā ir ROW(INDIRECT(B3&":"&B4)). Tas atgriezīs 31 datumu 2015. gada jūlijā. Bet pēc tam formula nodod šos 31 datumu WEEKDAY(,2)funkcijai. Šī funkcija atgriezīs 1 pirmdienai, 5 piektdienai utt. Tātad liels jautājums ir, cik no šiem 31 datuma atgriež 5, kad tiek nodots WEEKDAY(,2)funkcijai.
Formulas aprēķināšanu var veikt lēnā kustībā, izmantojot lentes cilnes Formula komandu Novērtēt formulu.
Tas notiek pēc tam, kad INDIRECT datumus pārvērš par atsaucēm uz rindu.
Nākamajā solī Excel gatavojas nodot 31 numuru funkcijai WEEKDAY. Pēc slepkavas formulas tas 31 skaitļa vietā nodotu 18 378 numurus.
Šeit ir 31 WEEKDAY funkciju rezultāti. Atcerieties, ka mēs vēlamies saskaitīt, cik ir 5.
Pārbaudot, vai iepriekšējais masīvs ir 5, tiek atgriezta vesela virkne True / False vērtību. Ir 5 patiesās vērtības, viena katrai piektdienai.
Es nevaru jums parādīt, kas notiks tālāk, bet es varu to izskaidrot. Excel nevar SUMMĒT virkni patiesu un nepatiesu vērtību. Tas ir pretrunā ar noteikumiem. Bet, ja reizināt šīs patiesās un nepatiesās vērtības ar 1 vai izmantojat funkciju dubultnegatīvs vai N (), patiesās vērtības pārvēršat par 1 un nepatiesās vērtības uz 0. Nosūtiet tās uz SUM vai SUMPRODUCT, un jūs iegūt patieso vērtību skaitu.
Šeit ir līdzīgs piemērs, lai saskaitītu, cik mēnešos ir 13 diena. Tas ir mazsvarīgi domāt: Katram mēnesim ir 13. datums, tāpēc atbilde uz visu gadu labāk ir 12. Excel veic matemātiku, ģenerē 365 datumus, tos visus nosūta funkcijai DAY () un izdomā, cik daudz beigsies. augšā mēneša 13. datumā. Atbilde, kā gaidīts, ir 12.
Nākamais attēls ir darblapa, kurā ir visa loģika, kas ir viena slepkavas formula, kas parādīta šīs tēmas sākumā. Esmu izveidojis rindu katrai dienai, kad esmu dzīvs. B slejā es saņemu šī datuma DAY (). C slejā es saņemu datuma WEEKDAY (). Vai D slejā B ir vienāds ar 13? Vai E slejā C = 5? Pēc tam es reizinu D * E, lai patieso / nepatieso pārvērstu par 1/0.
Esmu paslēpis daudzas rindas, bet es jums parādīju trīs nejaušas dienas vidū, kas gadās būt gan piektdiena, gan 13. diena.
F18381 kopējā summa ir tā pati 86, kuru atgrieza mana sākotnējā formula. Lieliska zīme. Bet šai darblapai ir 110 268 formulas. Mana sākotnējā slepkavas formula visu šo 110 268 formulu loģiku veic vienā formulā.
Pagaidi. Es gribu precizēt. Sākotnējā formulā nav nekā maģiska, kas kļūst gudrs un saīsina loģiku. Šī sākotnējā formula patiešām veic 110 268 darbības, iespējams, pat vairāk, jo sākotnējai formulai ROW () masīvs jāaprēķina divreiz.
Atrodiet veidu, kā to izmantot ROW(INDIRECT(Date:Date))reālajā dzīvē, un nosūtiet to man uz e-pastu (pub at dot com). Es nosūtīšu balvu pirmajiem 100 cilvēkiem, kas atbildēs. Iespējams, ka tā nav kūrorta uzturēšanās. Visticamāk, Big Mac. Bet tā tas notiek ar balvām. Daudz Big Mac un nav daudz kūrortviesnīcu.
Es pirmo reizi redzēju šo formulu 2003. gada ziņojumu dēlī, ko ievietoja Ekim. Kredīts tika piešķirts Harlanam Grovem. Formula parādījās arī Boba Umlas grāmatā Šī nav Excel, tā ir maģija. Mike Delaney, Meni Porat un Tim Sheets visi ieteica mīnus / mīnus triku. SUMPRODUCT ieteica Odrija Linna un Stīvens Vaits. Paldies jums visiem.
Skatīties video
- Ir slepena formulu klase, ko sauc par masīvu formulām.
- Masīva formula var veikt tūkstošiem starpaprēķinu.
- Viņi bieži pieprasa nospiest Ctrl + Shift + Enter, bet ne vienmēr.
- Labākā grāmata par masīvu formulām ir Maika Girvina Ctrl + Shift + Enter.
- INDIRECT ļauj izmantot savienojumu, lai izveidotu kaut ko tādu, kas izskatās kā šūnas atsauce.
- Datumi ir labi formatēti, bet tiek glabāti kā dienu skaits kopš 1900. gada 1. janvāra.
- Apvienojot divus datumus, Excel tiks norādīts uz virknes diapazonu.
- Pieprasot
ROW(INDIRECT(Date1:Date2))testamentu, "izlec" daudzu secīgu skaitļu masīvs - Izmantojot WEEKDAY funkciju, lai noskaidrotu, vai datums ir piektdiena.
- Cik piektdienas notiek šajā jūlijā?
- Lai skatītos, kā formula tiek aprēķināta lēnā kustībā, izmantojiet rīku Novērtēt formulu
- Cik šogad notiek 13. daļa?
- Cik piektdiena, 13. diena notika starp diviem datumiem?
- Pārbaudiet katru datumu, lai redzētu, vai WEEKDAY ir piektdiena
- Katrā datumā pārbaudiet, vai DAY ir 13
- Reiziniet šos rezultātus, izmantojot SUMPRODUCT
- Izmantojiet - lai pārveidotu True / False uz 1/0
Video atšifrējums
Uzziniet Excel no Podcast epizodes, 2026. gada epizode - Mana mīļākā formula visā programmā Excel!
Apraidot visu šo grāmatu, augšējā labajā stūrī noklikšķiniet uz “i”, lai nokļūtu atskaņošanas sarakstā!
Labi, tā bija 30. tēma grāmatā, mēs kaut kā atradāmies formulas sadaļas beigās vai formulas sadaļas vidū, un es teicu, ka man jāiekļauj mana visu laiku iecienītākā formula. Šī ir tikai pārsteidzoša formula, neatkarīgi no tā, vai jums ir jāsaskaita skaitlis piektdiena, 13. vai nē, tā paver pasauli visā Excel slepenajā apgabalā ar nosaukumu Array Formulas! Ievietojiet sākuma datumu, ievietojiet beigu datumu, un šī formula aprēķina piektdienas, 13. datuma, skaitu, kas notika starp šiem diviem datumiem. Tas faktiski veic piecus aprēķinus katru dienu starp šiem diviem datumiem, 91895 aprēķini + SUM, 91896 aprēķini, kas notiek šīs vienas mazās formulas iekšienē, labi. Tagad, līdz šīs epizodes beigām, jūs tik ļoti ieinteresēs masīvu formulas. Es gribu norādīt,manam draugam Maikam Girvinam ir labākā masīvu formulu grāmata ar nosaukumu “Ctrl + Shift” Enter ”, šī ir nesen izdrukāta zilā vāka, kas agrāk bija dzeltens un zaļš vāks. Neatkarīgi no tā, kuru iegūsit, tā ir lieliska grāmata, kuras saturs ir gan dzeltenā, gan zaļajā.
Labi, sāksim to iekšpusē, izmantojot formulu, kuru, iespējams, vēl neesat dzirdējis, saukta par NETIEŠU. Netiešs ļauj mums savienot vai kaut kādā veidā izveidot mazliet tekstu, kas izskatās kā šūnas atsauce. Labi, pieņemsim, ka mums šeit ir balvu ritenis, un es tikko lūdzu jūs izvēlēties starp A, B un C. Labi, tāpēc jūs izvēlaties šo un izvēlaties C, un pēc tam izvēlieties šo un izvēlieties 3, labi, un savu balvu ir kūrorta uzturēšanās, jo tieši tas tiek glabāts C3. Formula šeit tiek apvienota neatkarīgi no tā, kas ir C5, un neatkarīgi no tā, kas atrodas C6, izmantojot & un pēc tam nododot to NETIEŠAM. Tātad = NETIEŠA (C5 un C6) šajā gadījumā ir C3, kurai jābūt līdzsvarotai atsaucei. NETIEŠI saka: “Hei, mēs ejam uz C3 un atgriezīsim atbildi no tā, labi?” Atpakaļ Lotus 1-2-3 to sauca par @@ funkciju,programmā Excel viņi to pārdēvēja par NETIEŠU. Labi, tāpēc jums ir netiešais, tagad šeit ir pārsteidzoša lieta, kas notiek tur iekšā.
Mums ir divi datumi. Kā Excel glabā datumus, 17.05.1965, tas patiesībā ir tikai formatējums. Ja mēs gājām un apskatījām faktisko skaitli aiz tā, tas ir 23790, kas nozīmē, ka kopš 1901. gada 1. janvāra ir 23790 dienas un kopš 1980. gada 1. janvāra ir 42167 dienas. Mac datorā tas būs kopš 1904. gada 1. janvāra, tāpēc datumi būs aptuveni 3000 atlaidi. Labi, šādā veidā Excel to glabā, tomēr mums to parāda, pateicoties šim ciparu formātam kā datumam, bet, ja mēs kopā saliktu B3 un a: un B4, tas faktiski mums dotu aizkulisēs saglabātos numurus. Tātad = B3 & ”:” & B4, un, ja mēs to nodotu NETIEŠAM, tas faktiski norādīs uz visām rindām no 23790 līdz 42167.
Tātad ir B6 NETIEŠIE, es lūdzu rindu, kas man sniegs veselu virkni atbilžu un lai saprastu, cik daudz atbilžu es izmantoju, labi. Un, lai tas darbotos, ja es vienkārši nospiedu Enter, tas nedarbojas, man ir jāuztur Ctrl un Shift un jānospiež Enter, un jāredz, ka šeit tiek pievienota () ap formulu. Tas liek programmai Excel pāriet super formulas režīmā, masīvu formulu režīmā un veikt visu matemātiku visam, kas izlecis no šī masīva 18378. gada. Tātad, tas ir pārsteidzošs triks, netiešs no date1: date2, nododiet to funkcijai ROW, un šeit ir neliels piemērs.
Tāpēc mēs vienkārši vēlamies noskaidrot, cik piektdienas notika šajā jūlijā. Šeit ir sākuma datums, šeit ir beigu datums, un katrai no šīm rindām es prasīšu WEEKDAY. WEEKDAY pastāsta, kāda ir nedēļas diena, un šeit, argumentā 2, piektdienās vērtība būs 5. Tātad, es meklēju atbildi, un mēs izvēlēsimies šo formulu, dodieties uz Formulas, un Novērtēt formulu, un Vērtēt formulu ir lielisks veids, kā skatīties, kā formula tiek aprēķināta palēninājumā. Tātad, tur ir B3, 1. jūlijs, un jūs redzat, ka mainās skaitlis, un tad mēs pievienojamies kolai, labi, tur ir B4, kas mainīsies uz numuru, un tagad mēs saņemam tekstu 42186: 42216. Šajā brīdī mēs to nododam ROW, un šī vienkāršā mazā izteiksme šeit pārvērtīsies par 31 vērtībām.
Tagad piemērā, kur man bija viss no 1965. gada līdz 2015. gadam, tas izlaistu 86000 vērtības, vai ne, un jūs nevēlaties to darīt un novērtēt formulu, jo tas būtu kaut kas nenormāls, labi? Bet jūs varat redzēt, kas šeit notiek ar 31, un tagad es nododu šīs 31 dienas WEEKDAY funkcijai, un mēs saņemam 3-4-5. Tātad 3 nozīmē, ka tā bija trešdiena, un pēc tam 4 nozīmē, ka tā bija ceturtdiena, un pēc tam 5 nozīmē, ka tā bija piektdiena. Paņemiet visas šīs 31 vērtības un pārbaudiet, vai tās ir = 5, kas ir piektdiena, un mēs iegūsim virkni FALSE un TRUE, tāpēc trešdiena, ceturtdiena, piektdiena un pēc tam 7 šūnas vēlāk būs nākamā TRUE, satriecošs!
Labi, tāpēc šajā gadījumā mums ir 5 TRUE un 26 FALSE. Lai tos saskaitītu, man jāpārvērš FALSE kā 0 un TRUE uz 1, un ļoti izplatīts veids, kā to izdarīt, ir - . Labi, diemžēl tas neparādīja atbildi tur, kur mēs redzējām veselu virkni 1 un 0, bet patiesībā tā arī notiek, un tad SUMPRODUCT to saskaita un noved mūs pie 5. Šeit, ja mēs vēlamies noskaidrojiet, cik šogad bija mēneša 13. datuma, sākot no šī sākuma datuma līdz šim beigu datumam, ļoti līdzīgs process. Lai gan mums būs 365, nododiet to funkcijai DAY un pārbaudiet, cik labi ir 13, labi. Attiecībā uz 92000. rindas piemēru, jūs zināt, mēs saņemam dienu, mēs saņemam darba dienu, pārbaudot, vai DAY = 13, pārbaudot, vai WEEKDAY = FALSE, reizinot šo * šo,un tikai gadījumos, kad ir piektdiena, 13. diena, tā nonāk kā PATIESA. Pēc tam SUMPRODUCT saka “Pievienojiet visus tos kopā”, un tādējādi mēs iegūstam 86, burtiski 91895 aprēķinus + SUM, 91896, kas notiek šīs vienas formulas iekšpusē, tas ir ļoti spēcīgi! Ejiet nopirkt Maika grāmatu, tā ir pārsteidzoša grāmata, tā pavērs jums visu Excel formulu pasauli, un patiesībā jums vienkārši jāpērk abas grāmatas. Pērciet manu grāmatu, iegādājieties Maika grāmatu, un jums būs lieliska kolekcija, kas jūs nogādās pārējā gada laikā.tas jums atvērs visu Excel formulu pasauli, un patiesībā jums vienkārši jāpērk abas grāmatas. Pērciet manu grāmatu, iegādājieties Maika grāmatu, un jums būs lieliska kolekcija, kas jūs nogādās pārējā gada laikā.tas jums atvērs visu Excel formulu pasauli, un patiesībā jums vienkārši jāpērk abas grāmatas. Pērciet manu grāmatu, iegādājieties Maika grāmatu, un jums būs lieliska kolekcija, kas jūs nogādās pārējā gada laikā.
Labi, tāpēc atkārtojiet: ir slepena formulu klase, ko sauc par masīvu formulām, un masīva formula var veikt tūkstošiem starpaprēķinu. Parasti tie prasa nospiest taustiņu kombināciju Ctrl + Shift + Enter, bet ne vienmēr, un labākā masīvu formulu grāmata ir Maika Girvina grāmata “Ctrl + Shift + Enter”. Labi, tāpēc INDIRECT ļauj izmantot savienojumu, lai izveidotu kaut ko, kas izskatās kā šūnas atsauce, un pēc tam INDIRECT pāriet uz šīs šūnas atsauci. Savienojot divus datumus ar kolu, Excel tiks norādīts uz virknes diapazonu un pēc tam tiks prasīts datuma INDIRECT 1. rinda: date2 parādīs daudzu secīgu skaitļu masīvu, varbūt 31, varbūt 365 vai varbūt 85000. Pārbaudiet katru dienu, lai redzētu, vai WEEKDAY = piektdiena, pārbaudiet katru dienu, lai redzētu, vai DAY = 13, reiziniet šos divus TRUE un FALSE masīvus, izmantojot SUMPRODUCT. Daudzos gadījumos mēsIzmantos - lai pārveidotu TRUE / FALSE uz 1 un 0, lai SUMPRODUCT darbotos. Tā ir lieliska formula, es to neizveidoju, atradu ziņojumu dēlī, kad to pārstrādāju, es esmu kā “Oho, tas ir ļoti forši!”
Labi, es gribu pateikties, ka apstājāties, mēs tiksimies nākamreiz uz citu netcast no!
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2026.xlsx
