„Excel“ masyvo formulės yra labai galingos. Išmokę „Ctrl“ + „Shift“ + „Enter“ triukus, tūkstančius formulių galėsite pakeisti viena. Šiandien viena masyvo formulė apskaičiuoja 86 000.
Triskaidekafobija yra 13-ojo penktadienio baimė. Ši tema nieko neišgydys, tačiau ji parodys jums visiškai nuostabią formulę, kuri pakeis 110 268 formules. Realiame gyvenime man niekada nereikia skaičiuoti, kiek penktadienio 13-osios įvyko mano gyvenime, tačiau šios formulės galia ir grožis iliustruoja „Excel“ galią.
Tarkime, kad turite draugą, prietaringą dėl 13-osios penktadienio. Norite parodyti, kiek penktadienio 13-osios dienos jūsų draugas išgyveno.
Žemiau nustatykite paprastą darbalapį su gimimo data B1 ir =TODAY()B2. Tada laukinė formulė B6 kiekvieną dieną įvertina, ar jūsų draugas buvo gyvas, kad išsiaiškintų, kiek tų dienų buvo penktadienis ir krito mėnesio 13 dieną. Man šis skaičius yra 86. Nieko nereikia bijoti.
Beje, 1965 02 17 iš tikrųjų yra mano gimtadienis. Bet nenoriu, kad atsiųstum man gimtadienio atviruką. Vietoj to, per mano gimtadienį, noriu, kad leistumėte man paaiškinti, kaip ta nuostabi formulė veikia po vieną mažą žingsnį.
Ar kada nors naudojatės netiesiogine funkcija? Kai paprašysite =INDIRECT("C3"), „Excel“ pereis į C3 ir grąžins viską, kas yra toje ląstelėje. Bet NETIESIOGINIS yra galingesnis, kai apskaičiuojate langelio atskaitą skrendant. Galėtumėte įsteigti prizų ratą, kur kas nors pasiima raidę tarp A ir C, o paskui - skaičių nuo 1 iki 3. Kai sujungsite du atsakymus, turėsite langelio adresą, o kas yra to langelio adresas - tai prizas . Panašu, kad vietoj kurorto viešnagės laimėjau nuotraukų knygą.
Ar žinote, kaip „Excel“ saugo datas? Kai „Excel“ parodo jums 1965-02-17, ji ląstelėje saugo 23790, nes 1965-02-17 buvo 207-ojo amžiaus 23790-oji diena. Formulės esmė yra sujungimas, sujungiantis pradžios datą, dvitaškį ir pabaigos datą. „Excel“ nenaudoja suformatuotos datos. Vietoj to, jis naudoja serijos numerį užkulisiuose. Taigi B3&":"&B4tampa 23790: 42167. Patikėkite ar ne, tai yra galiojanti langelio nuoroda. Jei norėtumėte viską pridėti 3–5 eilutėse, galite naudoti =SUM(3:5). Taigi, kai perduodate 23790: 42167 funkciją NETIESIOGINIS, ji nukreipia į visas eilutes.
Kitas dalykas, kurį daro žudikas, yra paprašyti ROW(23790:42167). Paprastai praeinate vieną langelį: =ROW(D17)yra 17. Bet šiuo atveju jūs praleidžiate tūkstančius ląstelių. Kai paprašysite ROW(23790:42167)ir baigsite formulę naudodami „Ctrl“ + „Shift“ + „Enter“, „Excel“ iš tikrųjų grąžins kiekvieną skaičių nuo 23790, 23791, 23792 ir pan. Iki 42167.
Šis žingsnis yra nuostabus žingsnis. Šiame žingsnyje mes einame iš dviejų skaičių ir „iššokame“ 18378 skaičių masyvą. Dabar mes turime ką nors padaryti su tuo atsakymų rinkiniu. Ankstesnio paveikslo B9 langelis tik suskaičiuoja, kiek atsakymų gauname, o tai nuobodu, tačiau tai įrodo, kad ROW(23790:42167)pateikiami 18378 atsakymai.
Smarkiai supaprastinkime pradinį klausimą, kad galėtumėte pamatyti, kas vyksta. Tokiu atveju rasime 2015 m. Liepos mėn. Penktadienių skaičių. Žemiau B7 rodoma formulė pateikia teisingą atsakymą B6.
Formulės esmė yra ROW(INDIRECT(B3&":"&B4)). Tai grąžins 31 datą 2015 m. Liepos mėn. Tačiau formulė perduoda šias 31 datas WEEKDAY(,2)funkcijai. Ši funkcija grąžins 1 pirmadieniui, 5 penktadieniui ir pan. Taigi didelis klausimas, kiek iš tų 31 datos grąžina 5, kai perduodama WEEKDAY(,2)funkcijai.
Galite žiūrėti, kaip formulė apskaičiuojama sulėtintai, naudodami juostos skirtuko Formulė komandą Įvertinti formulę.
Tai įvyksta po to, kai „INDIRECT“ paverčia datas eilutės nuoroda.
Kitame etape „Excel“ netrukus perduos 31 numerį „WEEKDAY“ funkcijai. Pagal žudiko formulę jis perduotų 18 378 numerius, o ne 31.
Štai 31 SAVAITĖS DIENOS funkcijų rezultatai. Atminkite, kad mes norime suskaičiuoti, kiek yra 5.
Patikrinus, ar ankstesnis masyvas yra 5, pateikiama visa krūva „True / False“ reikšmių. Yra 5 tikrosios vertės, po vieną kiekvienam penktadieniui.
Negaliu jums parodyti, kas bus toliau, bet galiu tai paaiškinti. „Excel“ negali SUMMINTI daugybės teisingų ir klaidingų reikšmių. Tai prieštarauja taisyklėms. Bet jei padauginsite šias teisingas ir klaidingas reikšmes iš 1 arba jei naudosite dvigubą neigiamą arba N () funkciją, tikrąsias reikšmes paversite 1, o klaidingas - į 0. Nusiųskite jas į SUM arba SUMPRODUCT ir gausite gauti tikrųjų verčių skaičių.
Pateikiame panašų pavyzdį, norint suskaičiuoti, kiek mėnesių turi 13 dienų. Tai yra nereikšminga mintis: kiekvieną mėnesį yra 13-a, taigi atsakymas visiems metams yra geriau 12. „Excel“ atlieka matematiką, generuoja 365 datas, jas visas siunčia į DAY () funkciją ir išsiaiškina, kiek pabaigos mėnesio 13 dieną. Atsakymas, kaip ir tikėtasi, yra 12.
Kitas paveikslėlis yra darbalapis, kuriame pateikiama visa logika, tokia pati žudiko formulė, rodoma šios temos pradžioje. Sukūriau eilę kiekvienai dienai, kai buvau gyvas. B stulpelyje gaunu tos dienos DAY (). C stulpelyje gaunu WEEKDAY () datą. Ar D stulpelyje B lygus 13? Ar E stulpelyje C = 5? Tada padauginu D * E, kad teisingą / klaidingą paverčiau 1/0.
Aš paslėpiau daugybę eilučių, bet viduryje rodau tris atsitiktines dienas, kurios būna ir penktadienis, ir 13-oji.
Iš viso F18381 yra tas pats 86, kurį grąžino mano pradinė formulė. Puikus ženklas. Bet šiame darbalapyje yra 110 268 formulių. Mano originali žudiko formulė atlieka visą šių 110 268 formulių logiką vienoje formulėje.
Laukti. Noriu patikslinti. Originalioje formulėje nėra nieko stebuklingo, kuris protingas ir sutrumpintų logiką. Ta originali formulė tikrai atlieka 110 268 veiksmus, tikriausiai dar daugiau, nes pradinė formulė turi apskaičiuoti ROW () masyvą du kartus.
Raskite būdą tai panaudoti ROW(INDIRECT(Date:Date))realiame gyvenime ir atsiųskite man el. Paštu („pub at dot com“). Aš atsiųsiu prizą pirmiesiems 100 žmonių, kurie atsakys. Tikriausiai ne kurortinė viešnagė. Greičiau „Big Mac“. Bet taip yra su prizais. Daug „Big Mac“ ir nedaug kurortinių viešnagių.
Pirmą kartą šią formulę pamačiau 2003 m. Pranešimų lentoje „Ekim“. Kreditas buvo suteiktas Harlanui Grove'ui. Formulė pasirodė ir Bobo Umlaso knygoje „Tai nėra„ Excel “,„ Tai magija “. Mike'as Delaney, Meni Poratas ir Timas Sheetsas visi pasiūlė minuso / minuso triuką. SUMPRODUCT pasiūlė Audrey Lynn ir Stevenas White'as. Ačiū jums visiems.
Žiūrėti video
- Yra slapta formulių klasė, vadinama Masyvo formulėmis.
- Masyvo formulė gali atlikti tūkstančius tarpinių skaičiavimų.
- Jie dažnai reikalauja paspausti „Ctrl“ + „Shift“ + „Enter“, bet ne visada.
- Geriausia masyvo formulių knyga yra Mike'o Girvino „Ctrl + Shift + Enter“.
- Netiesioginis leidžia naudoti sujungimą kuriant tai, kas atrodo kaip langelio nuoroda.
- Datos yra gerai suformatuotos, tačiau saugomos kaip dienų skaičius nuo 1900 m. Sausio 1 d.
- Susiejant dvi datas bus nurodytas „Excel“ eilučių diapazonas.
- Prašant
ROW(INDIRECT(Date1:Date2))valios, „iššoks“ daugybės iš eilės einančių skaičių masyvas - Naudodamiesi WEEKDAY funkcija, norėdami išsiaiškinti, ar data yra penktadienis.
- Kiek penktadienių būna šį liepą?
- Norėdami žiūrėti, kaip formulė skaičiuojama sulėtintai, naudokite įrankį Įvertinti formulę
- Kiek šiemet įvyksta 13-osios?
- Kiek penktadienio 13-osios įvyko tarp dviejų datų?
- Kiekvieną datą patikrinkite, ar SAVAITĖS diena yra penktadienis
- Kiekvieną datą patikrinkite, ar DAY yra 13
- Padauginkite tuos rezultatus naudodami SUMPRODUCT
- Naudokite - konvertuoti tiesą / klaidingą į 1/0
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš podcast'o, 2026 epizodas - mano mėgstamiausia formulė visoje „Excel“!
Transliuodami šią visą knygą, viršutiniame dešiniajame kampe spustelėkite „i“, kad patektumėte į grojaraštį!
Gerai, tai buvo 30-oji knygos tema, mes buvome tarsi formulės skyriaus pabaigoje arba viduryje formulės, ir aš sakiau, kad turiu įtraukti savo mėgstamą visų laikų formulę. Tai tiesiog nuostabi formulė, nesvarbu, ar turite suskaičiuoti skaičių penktadienis, 13 ar ne, ji atveria pasaulį į visą slaptą „Excel“ sritį, pavadintą „Masyvo formulės“! Įveskite pradžios datą, įveskite pabaigos datą ir ši formulė apskaičiuoja penktadienio, 13-osios, įvykusio tarp tų dviejų datų, skaičių. Iš tikrųjų kiekvieną dieną tarp tų dviejų datų atliekami penki skaičiavimai, 91895 skaičiavimai + SUM, 91896 skaičiavimai, atliekami šios vienos mažos formulės viduje, gerai. Dabar, pasibaigus šiam epizodui, jus taip suintriguos masyvo formulės. Noriu atkreipti dėmesį,mano draugas Mike'as Girvinas turi geriausią knygą apie masyvo formules, vadinamas „Ctrl + Shift“ Enter “, tai neseniai spausdinta mėlyna danga, anksčiau buvusi geltona ir žalia viršelis. Dabar, nesvarbu, kurią gausite, yra puiki knyga, kurios turinys yra geltonos ir žalios spalvos.
Gerai, pradėkime nuo šios vidinės pusės, naudodami formulę, kurios galbūt negirdėjote, vadinamą NETIESIOGINIU. Netiesioginis leidžia mums susieti arba kažkokiu būdu sukurti šiek tiek teksto, kuris atrodo kaip langelio nuoroda. Gerai, tarkime, kad čia turime prizų ratą, ir aš tiesiog paprašiau jūsų pasirinkti tarp A, B ir C. Gerai, todėl jūs pasirenkate šį variantą ir pasirenkate C, tada pasirinkite šį ir pasirinkite 3, gerai, ir savo prizą yra kurorto viešnagė, nes būtent tai saugoma C3. Čia formulė sujungiama, nesvarbu, kas yra nuo C5, ir kas yra C6, naudojant & ir tada perduodant tai netiesioginiam. Taigi = NETIESIOGINIS (C5 ir C6), šiuo atveju yra C3, tai turi būti subalansuota atskaitos vertė. Netiesioginis sako: „Ei, mes eisime į C3 ir pateiksime atsakymą iš to, gerai?“ Atgal į „Lotus 1-2-3“ tai buvo vadinama funkcija @,„Excel“ programoje jie pervadino jį į NETIESIOGINĮ. Gerai, taigi jūs turite netiesioginį, dabar čia yra nuostabus dalykas, kuris vyksta ten.
Mes turime dvi datas, kaip „Excel“ saugo datas, 1965 2 17, tai iš tikrųjų yra tik formatavimas. Jei nuėjome ir pažiūrėjome faktinį skaičių už to, tai yra 23790, o tai reiškia, kad nuo 1900 m. Sausio 1 d. Yra 23790 dienų, o nuo 1980 m. „Mac“ sistemoje tai bus nuo 1904 m. Sausio 1 d., Taigi datos bus apie 3000 nuolaidų. Gerai, šitaip „Excel“ jį saugo, tačiau mums tai rodo ir dėl šio skaičiaus formato kaip datos, bet jei mes sujungtume B3 ir a: ir B4, tai iš tikrųjų suteiktų mums užkulisiuose saugomus skaičius. Taigi = B3 & ”:” & B4, ir jei mes tai perduotume NETIESIOGINIAM, tai iš tikrųjų nurodys visas eilutes nuo 23790 iki 42167.
Taigi yra B6 NETIESIOGINIS, aš paprašiau to eilės. Tai duos visą krūvą atsakymų ir išsiaiškinsiu, kiek atsakymų aš naudoju. Jei norite, kad šis darbas veiktų, jei tiesiog paspaudžiu „Enter“, jis neveikia, turiu palaikyti „Ctrl“ ir „Shift“ ir paspausti Enter, kad pamatytumėte, kad čia pridėta () aplink formulę. Tai nurodo „Excel“ pereiti į super formulės režimą, masyvo formulės režimą ir atlikti visą matematiką viskam, kas pasirodė iš to masyvo, 18378, gerai. Taigi, tai yra nuostabus triukas, netiesioginis „date1: date2“, perduokite tai funkcijai ROW ir pateikiame nedidelį pavyzdį.
Taigi mes tiesiog norime išsiaiškinti, kiek penktadienių įvyko šį liepą. Štai pradžios data, čia pabaigos data ir kiekvienai iš tų eilučių prašysiu SAVAITĖS DIENOS. WEEKDAY nurodo, kokia yra savaitės diena, o štai, argumente „Penktadieniai“ 2 reikšmė bus 5. Taigi, aš ieškau atsakymo ir pasirinksime šią formulę, eikite į „Formulės“, ir „Formulės įvertinimas“ ir „Formulės vertinimas“ yra puikus būdas stebėti, kaip formulė apskaičiuojama sulėtintai. Taigi yra B3, liepos 1 d., Ir jūs matote, kad pasikeičia skaičius, tada mes prisijungiame prie dvitaškio, tiesa, yra B4, kuris pasikeis į skaičių, ir dabar mes gausime tekstą 42186: 42216. Šiuo metu mes perduosime tai eilutei ir ta paprasta maža išraiška čia pavirs 31 verte.
Dabar pavyzdyje, kur aš turėjau viską nuo 1965 m. Iki 2015 m., Jis iššoko 86000 vertybių, tiesa, ir jūs nenorite to daryti ir vertinti formulės, nes būtų kažkoks beprotiškas, gerai? Bet jūs galite pamatyti, kas čia vyksta su 31, ir dabar aš praleidžiu tas 31 dienas WEEKDAY funkcijai, ir mes gauname 3-4-5. Taigi 3 reiškia, kad tai buvo trečiadienis, o tada 4 reiškia, kad tai buvo ketvirtadienis, o tada 5 reiškia, kad tai buvo penktadienis. Paimkite visas šias 31 reikšmes ir pažiūrėkite, ar jie yra 5, o tai yra penktadienis, ir mes gausime daugybę NETIESOS ir TIESOS, taigi trečiadienis, ketvirtadienis, penktadienis ir vėliau 7 langeliai vėliau bus kita TIESA, nuostabu!
Gerai, taigi šiuo atveju turime 5 TIKRUMUS ir 26 NETIESAIS. Norint juos susumuoti, turiu konvertuoti NETIKRĄ į 0 ir TIESAS į 1, o labai įprastas būdas tai padaryti yra naudoti - . Gerai, deja, ten nerodė atsakymo, kur mes matėme visą 1 ir 0 krūvą, bet iš tikrųjų taip atsitinka, o tada SUMPRODUCT prideda jį ir patenka į 5-ąjį. Čia, jei norime, išsiaiškinkite, kiek buvo mėnesio 13-osios šiais metais, nuo šios pradžios datos iki šios pabaigos datos, labai panašus procesas. Nors turėsime 365, perduokite tai funkcijai DAY ir patikrinkite, ar jų yra 13, gerai. 92000 eilutės pavyzdyje, žinote, mes gauname dieną, mes gauname savaitės dieną, tikriname, ar DAY = 13, patikriname, ar WEEKDAY = FALSE, padauginę šį * tai,ir tik tais atvejais, kai yra penktadienis, 13-oji, tai baigiasi TIKRA. Tada „SUMPRODUCT“ sako „Suskaičiuok visus“ ir taip gauname 86, pažodžiui 91895 skaičiavimus + SUM, 91896, vykstančius šioje vienoje formulėje. Tai be galo galinga! Eikite nusipirkti Mike'o knygos, tai nuostabi knyga, ji atvers jums visą „Excel“ formulių pasaulį ir iš tikrųjų turėtumėte nusipirkti abi knygas. Įsigykite mano knygą, nusipirkite Mike'o knygą, ir jūs turėsite nuostabią kolekciją, kurią naudosite per likusius metus.tai atvers jums visą „Excel“ formulių pasaulį ir iš tikrųjų turėtumėte nusipirkti abi knygas. Įsigykite mano knygą, nusipirkite Mike'o knygą, ir jūs turėsite nuostabią kolekciją, kurią naudosite per likusius metus.tai atvers jums visą „Excel“ formulių pasaulį ir iš tikrųjų turėtumėte nusipirkti abi knygas. Įsigykite mano knygą, nusipirkite Mike'o knygą, ir jūs turėsite nuostabią kolekciją, kurią naudosite per likusius metus.
Gerai, todėl pakartokime: yra slapta formulių klasė, vadinama masyvo formulėmis, o masyvo formulė gali atlikti tūkstančius tarpinių skaičiavimų. Paprastai jie reikalauja paspausti „Ctrl“ + „Shift“ + „Enter“, bet ne visada, o geriausia masyvo formulių knyga yra Mike'o Girvino knyga „Ctrl + Shift + Enter“. Gerai, todėl „INDIRECT“ leidžia naudoti sujungimą kuriant tai, kas atrodo kaip langelio nuoroda, o tada „INDIRECT“ eina į tos langelio nuorodą. Susiejant dvi datas su dvitaškiu, bus rodomas „Excel“ eilučių diapazonas, o tada bus prašoma nurodyti Datos netiesioginio eilutę: data2 pasirodys daugybės iš eilės einančių skaičių masyvas, galbūt 31, gal 365, o gal 85000. Patikrinkite kiekvieną datą, kad sužinotumėte, ar SAVAITĖ = penktadienis, patikrinkite kiekvieną dieną, ar Diena = 13, naudodami SUMPRODUCT padauginkite tuos du TIKRŲ ir NETIESIŲ masyvus. Daugeliu atvejų mesNaudosiu - konvertuoti TRUE / FALSE į 1 ir 0, kad SUMPRODUCT veiktų. Tai nuostabi formulė, aš jos nesukūriau, radau skelbimų lentoje, kai ją atidirbau, aš tarsi „Oho, tai tikrai šaunu!“
Gerai, noriu padėkoti už tai, kad užsukote, pamatysime kitą kartą, kai pasirodys dar viena transliacija iš!
Atsisiųsti failą
Failo pavyzdį atsisiųskite čia: Podcast2026.xlsx
