Formuły tablicowe Excela są bardzo wydajne. Możesz zastąpić tysiące formuł jedną formułą, gdy nauczysz się sztuczki Ctrl + Shift + Enter. Obecnie pojedyncza formuła tablicowa wykonuje 86 000 obliczeń.
Triskaidekafobia to strach przed piątek 13. Ten temat niczego nie wyleczy, ale pokaże absolutnie niesamowitą formułę, która zastępuje 110 268 formuł. W prawdziwym życiu nigdy nie muszę liczyć, ile piątków trzynastych wydarzyło się w moim życiu, ale siła i piękno tej formuły ilustrują potęgę Excela.
Powiedz, że masz przyjaciela, który jest przesądny co do piątku trzynastego. Chcesz zilustrować, ile piątku trzynastego przeżył twój przyjaciel.
Skonfiguruj prosty arkusz roboczy poniżej, z datą urodzenia w B1 i =TODAY()B2. Następnie dzika formuła w B6 ocenia każdego dnia, że twój przyjaciel żyje, aby dowiedzieć się, ile z tych dni przypadło w piątek i przypadło 13 dnia miesiąca. Dla mnie liczba to 86. Nie ma się czego bać.
Nawiasem mówiąc, 17.02.1965 to naprawdę moje urodziny. Ale nie chcę, żebyś wysyłał mi kartkę urodzinową. Zamiast tego, na moje urodziny, pozwól mi wyjaśnić, jak działa ta niesamowita formuła, krok po kroku.
Czy kiedykolwiek używałeś funkcji POŚREDNI? Kiedy poprosisz =INDIRECT("C3"), Excel przejdzie do C3 i zwróci wszystko, co jest w tej komórce. Ale POŚREDNI jest bardziej wydajny, gdy obliczasz odwołanie do komórki w locie. Możesz ustawić koło nagród, w którym ktoś wybiera literę między A i C, a następnie wybiera liczbę od 1 do 3. Kiedy połączysz dwie odpowiedzi, będziesz mieć adres komórki, a cokolwiek jest pod tym adresem komórki, jest nagrodą . Wygląda na to, że zamiast pobytu w ośrodku wygrałem fotoksiążkę.
Czy wiesz, jak Excel przechowuje daty? Gdy program Excel pokazuje 17.02.1965, zapisuje w komórce 23790, ponieważ 17.02.1965 to 23790 dzień XX wieku. W sercu formuły znajduje się konkatenacja, która łączy datę początkową i dwukropek oraz datę końcową. Excel nie używa sformatowanej daty. Zamiast tego używa numeru seryjnego za kulisami. Więc B3&":"&B4staje się 23790: 42167. Wierz lub nie, ale to prawidłowe odwołanie do komórki. Jeśli chcesz dodać wszystko w wierszach od 3 do 5, możesz użyć =SUM(3:5). Tak więc, gdy przekazujesz 23790: 42167 do funkcji INDIRECT, wskazuje ona na wszystkie wiersze.
Następną rzeczą, którą robi formuła zabójcy, jest poproszenie o ROW(23790:42167). Zwykle mijasz pojedynczą komórkę: =ROW(D17)wynosi 17. Ale w tym przypadku mijasz tysiące komórek. Kiedy pytasz o ROW(23790:42167)formułę i kończysz ją za pomocą Ctrl + Shift + Enter, Excel w rzeczywistości zwraca każdą liczbę od 23790, 23791, 23792 itd., Aż do 42167.
Ten krok jest niesamowitym krokiem. W tym kroku przechodzimy od dwóch liczb i „wyskakujemy” tablicę 18378 liczb. Teraz musimy coś zrobić z tą tablicą odpowiedzi. Komórka B9 z poprzedniego rysunku po prostu zlicza, ile otrzymaliśmy odpowiedzi, co jest nudne, ale udowadnia, że ROW(23790:42167)zwraca 18378 odpowiedzi.
Uprośćmy radykalnie pierwotne pytanie, abyś mógł zobaczyć, co się dzieje. W tym przypadku znajdziemy liczbę piątków w lipcu 2015 r. Wzór przedstawiony poniżej w B7 zapewnia poprawną odpowiedź w B6.
Sercem formuły jest ROW(INDIRECT(B3&":"&B4)). To zwróci 31 dat w lipcu 2015 r. Ale formuła następnie przekazuje te 31 dat do WEEKDAY(,2)funkcji. Ta funkcja zwróci 1 dla poniedziałku, 5 dla piątku i tak dalej. Więc najważniejsze pytanie brzmi: ile z tych 31 dat zwraca 5 po przekazaniu do WEEKDAY(,2)funkcji.
Możesz obserwować obliczanie formuły w zwolnionym tempie, używając polecenia Oceń formułę na karcie Formuła na wstążce.
Dzieje się tak po tym, jak INDIRECT konwertuje daty na odwołanie do wiersza.
W następnym kroku Excel przekaże 31 liczb do funkcji WEEKDAY. Teraz, w formule zabójcy, przeszedłby 18 378 liczb zamiast 31.
Oto wyniki funkcji 31 WEEKDAY. Pamiętaj, że chcemy policzyć, ile jest 5.
Sprawdzenie, czy poprzednia tablica to 5, zwraca całą gamę wartości Prawda / Fałsz. Istnieje 5 wartości True, po jednej na każdy piątek.
Nie mogę pokazać, co dzieje się później, ale mogę to wyjaśnić. Program Excel nie może SUMAĆ wielu wartości Prawda i Fałsz. To niezgodne z zasadami. Ale jeśli pomnożymy te wartości True i False przez 1 lub jeśli użyjemy podwójnie ujemnej lub funkcji N (), przekonwertujemy wartości True na 1, a wartości False na 0. Wyślij je do SUMA lub SUMPRODUCT, a będziesz uzyskać liczbę wartości True.
Oto podobny przykład, aby policzyć, ile miesięcy ma dzień 13. Jest to trywialne do przemyślenia: każdy miesiąc ma 13, więc odpowiedź na cały rok powinna wynosić 12. Excel wykonuje obliczenia matematyczne, generuje 365 dat, wysyła je wszystkie do funkcji DAY () i oblicza, ile się kończy do 13 dnia miesiąca. Odpowiedź, zgodnie z oczekiwaniami, to 12.
Następna ilustracja to arkusz roboczy, który wykonuje całą logikę formuły jednego zabójcy pokazanej na początku tego tematu. Stworzyłem wiersz na każdy dzień, w którym żyłem. W kolumnie B otrzymuję DZIEŃ () z tego dnia. W kolumnie C otrzymuję DZIEŃ.TYG () daty. Czy w kolumnie D B jest równe 13? Czy w kolumnie E C = 5? Następnie mnożę D * E, aby przekonwertować wartość Prawda / Fałsz na 1/0.
Ukryłem wiele wierszy, ale pokazuję trzy losowe dni w środku, które przypadają na piątek i 13.
Suma w F18381 jest taka sama, jak 86, którą zwróciła moja oryginalna formuła. Świetny znak. Ale ten arkusz zawiera 110 268 formuł. Moja oryginalna formuła zabójcy zawiera całą logikę tych 110 268 formuł w jednej formule.
Czekać. Chcę wyjaśnić. W oryginalnej formule nie ma nic magicznego, co by było sprytne i skracało logikę. Ta oryginalna formuła naprawdę robi 110 268 kroków, prawdopodobnie nawet więcej, ponieważ oryginalna formuła musi dwukrotnie obliczyć tablicę ROW ().
Znajdź sposób na wykorzystanie tego ROW(INDIRECT(Date:Date))w prawdziwym życiu i wyślij mi go w e-mailu (pub at dot com). Wyślę nagrodę do pierwszych 100 osób, które odpowiedzą. Prawdopodobnie nie jest to pobyt w ośrodku wypoczynkowym. Raczej Big Mac. Ale tak to wygląda z nagrodami. Wiele Big Maców i niewiele pobytów w ośrodkach.
Po raz pierwszy zobaczyłem tę formułę opublikowaną na Message Board w 2003 roku przez Ekim. Kredyt został przyznany Harlan Grove. Formuła pojawiła się również w książce Boba Umlasa To nie jest Excel, to magia. Mike Delaney, Meni Porat i Tim Sheets zasugerowali sztuczkę minus / minus. SUMPRODUCT zasugerowali Audrey Lynn i Steven White. Dziękuję wam wszystkim.
Obejrzyj wideo
- Istnieje tajna klasa formuł zwana Formułami tablicowymi.
- Formuła tablicowa może wykonywać tysiące obliczeń pośrednich.
- Często wymagają naciśnięcia Ctrl + Shift + Enter, ale nie zawsze.
- Najlepszą książką o formułach tablicowych jest Ctrl + Shift + Enter Mike'a Girvina.
- POŚREDNI umożliwia użycie konkatenacji do zbudowania czegoś, co wygląda jak odwołanie do komórki.
- Daty są ładnie sformatowane, ale są przechowywane jako liczba dni od 1 stycznia 1900 r.
- Łączenie dwóch dat spowoduje wskazanie zakresu wierszy w programie Excel.
- Pytanie o
ROW(INDIRECT(Date1:Date2))testament "wyskoczy" tablicę wielu kolejnych liczb - Korzystanie z funkcji WEEKDAY, aby sprawdzić, czy data to piątek.
- Ile piątków przypada w lipcu?
- Aby zobaczyć obliczanie formuły w zwolnionym tempie, użyj narzędzia Oceń formułę
- Ile trzynastek występuje w tym roku?
- Ile piątku trzynastego wydarzyło się między dwoma datami?
- Sprawdź każdą datę, aby zobaczyć, czy WEEKDAY to piątek
- Sprawdź każdą datę, aby zobaczyć, czy DZIEŃ jest 13
- Pomnóż te wyniki za pomocą SUMPRODUCT
- Użyj -, aby przekonwertować True / False na 1/0
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2026 - Moja ulubiona formuła w całym programie Excel!
Podcasting całej tej książki, kliknij „i” w prawym górnym rogu, aby przejść do listy odtwarzania!
W porządku, to był 30. temat w książce, byliśmy na końcu sekcji z formułami lub w środku sekcji z formułami i powiedziałem, że muszę uwzględnić moją ulubioną formułę wszechczasów. To po prostu niesamowita formuła, niezależnie od tego, czy musisz policzyć liczbę w piątek 13, czy nie, otwiera ona świat na cały sekretny obszar programu Excel o nazwie Formuły tablicowe! Wpisz datę początkową, datę końcową, a ta formuła obliczy liczbę piątków trzynastych, które wystąpiły między tymi dwiema datami. W rzeczywistości wykonuje pięć obliczeń każdego dnia między tymi dwiema datami, 91895 obliczeń + SUMA, 91896 obliczeń dzieje się wewnątrz tej jednej małej formuły, w porządku. Teraz, pod koniec tego odcinka, będziesz tak zaintrygowany formułami tablicowymi. Chcę podkreślić,mój przyjaciel Mike Girvin ma najlepszą książkę o formułach tablicowych o nazwie „Ctrl + Shift” Enter ”. Jest to niedawny wydruk z niebieską okładką, który był kiedyś żółto-zieloną okładką. Cokolwiek dostaniesz, jest to świetna książka, o tej samej zawartości zarówno w żółtej, jak i zielonej.
W porządku, więc zacznijmy od wewnętrznej strony, od formuły, której być może nie słyszałeś, o nazwie POŚREDNI. POŚREDNIE pozwala nam łączyć lub w jakiś sposób tworzyć fragmenty tekstu, które wyglądają jak odwołanie do komórki. W porządku, powiedzmy, że mamy tutaj koło nagród i właśnie poprosiłem Cię o wybór między A, B i C. W porządku, więc wybierasz to i wybierasz C, a następnie wybierasz to i wybierasz 3, w porządku, i swoją nagrodę to pobyt w ośrodku wypoczynkowym, ponieważ to właśnie jest przechowywane w C3. Formuła tutaj jest łączona razem, niezależnie od tego, co jest od C5 i co jest w C6, używając &, a następnie przekazując to do POŚREDNIEGO. Więc = POŚREDNI (C5 i C6), w tym przypadku, to C3, które musi być zrównoważonym odniesieniem. POŚREDNI mówi „Hej, idziemy do C3 i odpowiemy na ten temat, dobrze?” W Lotus 1-2-3 nazywało się to funkcją @@,w programie Excel zmienili jego nazwę na POŚREDNIA. W porządku, więc masz POŚREDNI, teraz oto niesamowita rzecz, która dzieje się w środku.
Mamy dwie daty, jak Excel przechowuje daty, 2/17/1965, to tak naprawdę tylko formatowanie. Gdybyśmy spojrzeli na rzeczywistą liczbę za tym, jest to 23790, co oznacza, że jest to 23790 dni od 01.01.1900 i 42167 dni od 01.01.1980. Na Macu będzie to od 01.01.1904, więc daty będą około 3000 wyłączone. W porządku, tak to przechowuje Excel, ale pokazuje nam to dzięki temu formatowi liczb jako daty, ale gdybyśmy połączyli razem B3 i a: i B4, to faktycznie dałoby nam liczby przechowywane za kulisami. Więc = B3 & ”:” & B4, a jeśli przekażemy to do INDIRECT, to w rzeczywistości wskaże wszystkie wiersze od 23790 do 42167.
Więc jest POŚREDNI z B6, poprosiłem o WIERSZ tego, co da mi całą masę odpowiedzi i obliczy, ile odpowiedzi policzyłem, w porządku. Aby to zadziałało, jeśli po prostu naciśnę Enter, to nie działa, muszę przytrzymać Ctrl i Shift i nacisnąć Enter, i widzę, że dodaje to () wokół formuły tutaj. Mówi programowi Excel, aby przejść do trybu superformuły, trybu formuły tablicowej i wykonać wszystkie obliczenia matematyczne dla wszystkiego, co wyskoczyło z tej tablicy, 18378, w porządku. To jest niesamowita sztuczka, pośrednia w stosunku do date1: date2, przekaż to do funkcji WIERSZ, a oto mały przykład.
Chcemy tylko dowiedzieć się, ile piątków miało miejsce w lipcu, tutaj jest data rozpoczęcia, tutaj jest data końcowa, a dla każdego z tych wierszy zapytam o dzień tygodnia. WEEKDAY mówi nam, jaki jest dzień tygodnia, a tutaj w argumencie 2 piątki będą miały wartość 5. Więc szukam odpowiedzi i zamierzamy wybrać tę formułę, przejdź do Formuły, i Oceń formułę oraz Oceń formułę to świetny sposób, aby zobaczyć, jak formuła jest obliczana w zwolnionym tempie. Więc jest B3, 1 lipca, i widzisz, że zmienia się w liczbę, a następnie łączymy się z dwukropkiem, tak, jest B4, który zmieni się w liczbę, a teraz otrzymujemy tekst 42186: 42216. W tym momencie przekazujemy to do WIERSZA i to proste, małe wyrażenie zamieni się tutaj w 31 wartości.
Teraz, w przykładzie, w którym miałem wszystko od 1965 do 2015, wyskoczyłoby 86000 wartości, prawda, a ty nie chcesz tego robić i oceniać formuły, ponieważ byłoby to trochę szalone, w porządku? Ale możesz zobaczyć, co się dzieje tutaj z 31, a teraz przechodzę przez te 31 dni do funkcji WEEKDAY i otrzymujemy 3-4-5. Czyli 3 oznacza, że była to środa, a następnie 4 oznacza czwartek, a 5 oznacza, że był to piątek. Weź wszystkie te 31 wartości i zobacz, czy są = 5, czyli piątek, a otrzymamy kilka wartości FAŁSZ i PRAWDA, więc środa, czwartek, piątek, a następnie 7 komórek później będzie następną PRAWDA, niesamowite!
W porządku, więc w tym przypadku mamy 5 PRAWDA i 26 FAŁSZYWEK. Aby je dodać, muszę przekonwertować FAŁSZ na 0, a PRAWDA na 1, a bardzo powszechnym sposobem jest użycie - . W porządku, niestety nie pokazało tam odpowiedzi, gdzie widzieliśmy całą masę jedynek i zer, ale tak właśnie się dzieje, a następnie SUMPRODUCT dodaje to i przenosi nas do 5. Tutaj, na dole, jeśli chcemy sprawdź, ile 13 dnia miesiąca upłynęło w tym roku, od tej daty początkowej do tej końcowej, bardzo podobny proces. Chociaż będziemy mieć 365, przekaż to do funkcji DZIEŃ i sprawdź, ile jest 13, w porządku. W przykładzie wiersza 92000, wiesz, otrzymujemy dzień, otrzymujemy dzień tygodnia, sprawdzamy, czy DAY = 13, sprawdzamy, czy WEEKDAY = FALSE, mnożąc to * this,i tylko w przypadkach, gdy jest piątek 13, kończy się to jako PRAWDA. Następnie SUMPRODUCT mówi „Dodaj wszystkie te elementy” i tak otrzymujemy 86, dosłownie 91895 obliczeń + SUMA, 91896 zachodzące wewnątrz tej jednej formuły, jest niesamowicie potężny! Idź, kup książkę Mike'a, to niesamowita książka, otworzy przed tobą cały świat formuł Excela, a właściwie powinieneś po prostu kupić obie książki. Kup moją książkę, kup książkę Mike'a, a będziesz mieć niesamowitą kolekcję, która pozwoli Ci przetrwać resztę roku.otworzy przed tobą cały świat formuł programu Excel, a właściwie powinieneś po prostu kupić obie książki. Kup moją książkę, kup książkę Mike'a, a będziesz mieć niesamowitą kolekcję, która pozwoli Ci przetrwać resztę roku.otworzy przed tobą cały świat formuł programu Excel, a właściwie powinieneś po prostu kupić obie książki. Kup moją książkę, kup książkę Mike'a, a będziesz mieć niesamowitą kolekcję, która pozwoli Ci przetrwać resztę roku.
W porządku, więc podsumuj: istnieje tajna klasa formuł zwana formułami tablicowymi, a formuła tablicowa może wykonywać tysiące obliczeń pośrednich. Zwykle wymagają naciśnięcia Ctrl + Shift + Enter, ale nie zawsze, a najlepszą książką o formułach tablicowych jest książka „Ctrl + Shift + Enter” Mike'a Girvina. W porządku, więc POŚREDNIE umożliwia użycie konkatenacji do zbudowania czegoś, co wygląda jak odwołanie do komórki, a następnie POŚREDNIE przechodzi do tego odwołania do komórki. Łączenie dwóch dat dwukropkiem wskaże zakres wierszy w programie Excel, a następnie zapytanie o WIERSZ POŚREDNIEGO z date1: date2 spowoduje wyświetlenie tablicy wielu kolejnych liczb, być może 31, może 365 lub może 85000. Sprawdź każdej daty, aby zobaczyć, czy DZIEŃ.TYG = piątek, sprawdzaj każdego dnia, czy DZIEŃ = 13, pomnóż te dwie tablice PRAWDA i FAŁSZ za pomocą SUMPRODUCT. W wielu przypadkachUżyjemy - aby przekonwertować TRUE / FALSE na 1 i 0, aby umożliwić działanie SUMPRODUCT. To niesamowita formuła, nie stworzyłem jej, znalazłem ją na tablicy ogłoszeń, kiedy nad nią pracowałem, myślę: „Wow, to jest naprawdę fajne!”
W porządku, chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem od!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2026.xlsx
