Excel-Array-Formeln sind sehr leistungsfähig. Sie können Tausende von Formeln durch eine einzige Formel ersetzen, sobald Sie den Trick Strg + Umschalt + Eingabetaste gelernt haben. Heute führt eine einzelne Array-Formel 86.000 Berechnungen durch.
Triskaidekaphobia ist die Angst vor Freitag, dem 13 .. Dieses Thema wird nichts heilen, aber es zeigt Ihnen eine absolut erstaunliche Formel, die 110.268 Formeln ersetzt. Im wirklichen Leben muss ich nie zählen, wie viele Freitag, der 13. in meinem Leben aufgetreten sind, aber die Kraft und die Schönheit dieser Formel veranschaulichen die Kraft von Excel.
Sagen Sie, Sie haben einen Freund, der am Freitag, den 13. abergläubisch ist. Sie möchten veranschaulichen, wie viele Freitag, den 13., Ihr Freund durchlebt hat.
Richten Sie das folgende einfache Arbeitsblatt mit dem Geburtsdatum in B1 und =TODAY()in B2 ein. Dann wertet eine wilde Formel in B6 jeden Tag aus, an dem Ihr Freund am Leben war, um herauszufinden, wie viele dieser Tage Freitag waren und am 13. des Monats fielen. Für mich ist die Zahl 86. Nichts, wovor ich Angst haben müsste.
Der 17.02.1965 ist übrigens wirklich mein Geburtstag. Aber ich möchte nicht, dass du mir eine Geburtstagskarte schickst. Stattdessen möchte ich, dass Sie mir zu meinem Geburtstag erklären, wie diese erstaunliche Formel Schritt für Schritt funktioniert.
Haben Sie jemals die INDIRECT-Funktion verwendet? Wenn Sie danach fragen =INDIRECT("C3"), wechselt Excel zu C3 und gibt alles zurück, was sich in dieser Zelle befindet. INDIRECT ist jedoch leistungsfähiger, wenn Sie die Zellreferenz im laufenden Betrieb berechnen. Sie können ein Preisrad einrichten, in dem jemand einen Buchstaben zwischen A und C und dann eine Zahl zwischen 1 und 3 auswählt. Wenn Sie die beiden Antworten verketten, erhalten Sie eine Zellenadresse, und was auch immer sich an dieser Zellenadresse befindet, ist der Preis . Sieht so aus, als hätte ich anstelle des Resortaufenthaltes ein Fotobuch gewonnen.
Wissen Sie, wie Excel Daten speichert? Wenn Excel Ihnen den 17.02.1965 zeigt, werden 23790 in der Zelle gespeichert, da der 17.02.1965 der 23790. Tag des 20. Jahrhunderts war. Das Herzstück der Formel ist eine Verkettung, die das Startdatum und einen Doppelpunkt sowie das Enddatum verbindet. Excel verwendet das formatierte Datum nicht. Stattdessen wird die Seriennummer hinter den Kulissen verwendet. So B3&":"&B4wird 23790: 42167. Ob Sie es glauben oder nicht, das ist eine gültige Zellreferenz. Wenn Sie alles in den Zeilen 3 bis 5 addieren möchten, können Sie verwenden =SUM(3:5). Wenn Sie also 23790: 42167 an die INDIRECT-Funktion übergeben, zeigt sie auf alle Zeilen.
Das nächste, was die Killerformel tut, ist nach dem zu fragen ROW(23790:42167). Normalerweise übergeben Sie eine einzelne Zelle: =ROW(D17)ist 17. In diesem Fall übergeben Sie jedoch Tausende von Zellen. Wenn Sie ROW(23790:42167)die Formel mit Strg + Umschalt + Eingabetaste anfordern und beenden, gibt Excel tatsächlich jede Zahl von 23790, 23791, 23792 usw. bis 42167 zurück.
Dieser Schritt ist der erstaunliche Schritt. In diesem Schritt gehen wir von zwei Zahlen aus und "knallen" ein Array von 18378 Zahlen heraus. Jetzt müssen wir etwas mit dieser Reihe von Antworten anfangen. Zelle B9 der vorherigen Abbildung zählt nur, wie viele Antworten wir erhalten, was langweilig ist, aber es beweist, dass ROW(23790:42167)18378 Antworten zurückgegeben werden.
Lassen Sie uns die ursprüngliche Frage dramatisch vereinfachen, damit Sie sehen können, was passiert. In diesem Fall finden wir die Anzahl der Freitage im Juli 2015. Die unten in B7 gezeigte Formel liefert die richtige Antwort in B6.
Im Zentrum der Formel steht ROW(INDIRECT(B3&":"&B4)). Dies wird die 31 Daten im Juli 2015 zurückgeben. Die Formel übergibt diese 31 Daten dann jedoch an die WEEKDAY(,2)Funktion. Diese Funktion gibt eine 1 für Montag, eine 5 für Freitag usw. zurück. Die große Frage ist also, wie viele dieser 31 Daten eine 5 zurückgeben, wenn sie an die WEEKDAY(,2)Funktion übergeben werden.
Sie können die Berechnung der Formel in Zeitlupe mit dem Befehl Formel auswerten auf der Registerkarte Formel des Menübands anzeigen.
Dies geschieht, nachdem INDIRECT die Daten in eine Zeilenreferenz konvertiert hat.
Im nächsten Schritt übergibt Excel 31 Zahlen an die WEEKDAY-Funktion. Jetzt würde es in der Killerformel 18.378 statt 31 Zahlen geben.
Hier sind die Ergebnisse der 31 WEEKDAY-Funktionen. Denken Sie daran, wir möchten zählen, wie viele 5 sind.
Wenn Sie überprüfen, ob das vorherige Array 5 ist, werden eine ganze Reihe von True / False-Werten zurückgegeben. Es gibt 5 wahre Werte, einen für jeden Freitag.
Ich kann dir nicht zeigen, was als nächstes passiert, aber ich kann es erklären. Excel kann eine Reihe von True- und False-Werten nicht summieren. Es ist gegen die Regeln. Wenn Sie jedoch diese True- und False-Werte mit 1 multiplizieren oder die Double-Negative- oder N () -Funktion verwenden, konvertieren Sie die True-Werte in 1 und die False-Werte in 0. Senden Sie diese an SUM oder SUMPRODUCT, und Sie werden es tun Holen Sie sich die Anzahl der True-Werte.
Hier ist ein ähnliches Beispiel, um zu zählen, wie viele Monate einen Tag 13 enthalten. Das ist trivial: Jeder Monat hat einen 13., daher lautet die Antwort für ein ganzes Jahr besser 12. Excel berechnet, generiert 365 Daten, sendet sie alle an die Funktion DAY () und ermittelt, wie viele enden bis zum 13. des Monats. Die Antwort lautet erwartungsgemäß 12.
Die nächste Abbildung ist ein Arbeitsblatt, das die gesamte Logik der zu Beginn dieses Themas gezeigten Killerformel ausführt. Ich habe für jeden Tag, an dem ich am Leben war, eine Reihe erstellt. In Spalte B erhalte ich den DAY () dieses Datums. In Spalte C erhalte ich den WOCHENTAG () des Datums. Ist B in Spalte D gleich 13? Ist in Spalte E C = 5? Ich multipliziere dann D * E, um True / False in 1/0 umzuwandeln.
Ich habe viele Zeilen versteckt, aber ich zeige Ihnen drei zufällige Tage in der Mitte, die zufällig sowohl ein Freitag als auch der 13. sind.
Die Summe in F18381 ist die gleiche 86, die meine ursprüngliche Formel zurückgegeben hat. Ein tolles Zeichen. Dieses Arbeitsblatt enthält jedoch 110.268 Formeln. Meine ursprüngliche Killerformel führt die gesamte Logik dieser 110.268 Formeln in einer einzigen Formel aus.
Warten. Ich möchte klarstellen. Es gibt nichts Magisches in der ursprünglichen Formel, das klug wird und die Logik verkürzt. Diese ursprüngliche Formel führt tatsächlich 110.268 Schritte aus, wahrscheinlich sogar noch mehr, da die ursprüngliche Formel das ROW () - Array zweimal berechnen muss.
Finden Sie einen Weg, dies ROW(INDIRECT(Date:Date))im wirklichen Leben zu nutzen, und senden Sie es mir per E-Mail (Pub at dot com). Ich werde einen Preis an die ersten 100 Personen senden, die antworten. Wahrscheinlich kein Resortaufenthalt. Eher ein Big Mac. Aber so geht es mit Preisen. Viele Big Macs und nicht viele Resortaufenthalte.
Ich habe diese Formel 2003 zum ersten Mal von Ekim im Message Board gesehen. Harlan Grove wurde die Ehre erwiesen. Die Formel erschien auch in Bob Umlas 'Buch This Isn't Excel, It's Magic. Mike Delaney, Meni Porat und Tim Sheets schlugen alle den Minus / Minus-Trick vor. SUMPRODUCT wurde von Audrey Lynn und Steven White vorgeschlagen. Danke euch allen.
Schau Video
- Es gibt eine geheime Klasse von Formeln, die als Array-Formeln bezeichnet werden.
- Eine Array-Formel kann Tausende von Zwischenberechnungen durchführen.
- Sie müssen häufig Strg + Umschalt + Eingabetaste drücken, aber nicht immer.
- Das beste Buch über Array-Formeln ist Mike Girvins Strg + Umschalt + Eingabetaste.
- Mit INDIRECT können Sie mithilfe der Verkettung etwas erstellen, das wie eine Zellreferenz aussieht.
- Die Daten sind gut formatiert, werden jedoch seit dem 1. Januar 1900 mehrere Tage gespeichert.
- Das Verketten von zwei Daten verweist auf eine Reihe von Zeilen in Excel.
- Wenn Sie nach dem
ROW(INDIRECT(Date1:Date2))Willen fragen, wird ein Array mit vielen aufeinanderfolgenden Zahlen "herausspringen" - Verwenden Sie die WEEKDAY-Funktion, um herauszufinden, ob ein Datum Freitag ist.
- Wie viele Freitage gibt es in diesem Juli?
- Verwenden Sie das Werkzeug Formel auswerten, um eine Formel in Zeitlupe zu berechnen
- Wie viele 13. treten in diesem Jahr auf?
- Wie viele Freitag, der 13. zwischen zwei Daten?
- Überprüfen Sie jedes Datum, um festzustellen, ob der WOCHENTAG Freitag ist
- Überprüfen Sie jedes Datum, um festzustellen, ob der TAG 13 ist
- Multiplizieren Sie diese Ergebnisse mit SUMPRODUCT
- Verwenden Sie -, um True / False in 1/0 zu konvertieren
Video-Transkript
Lerne Excel aus dem Podcast, Folge 2026 - Meine Lieblingsformel in ganz Excel!
Klicken Sie beim Podcasting des gesamten Buches auf das „i“ in der oberen rechten Ecke, um zur Wiedergabeliste zu gelangen!
Okay, es war das 30. Thema in dem Buch, wir waren sozusagen am Ende des Formelabschnitts oder mitten im Formelabschnitt, und ich sagte, ich muss meine Lieblingsformel aller Zeiten aufnehmen. Dies ist nur eine erstaunliche Formel, egal ob Sie die Zahl Freitag, den 13. zählen müssen oder nicht, sie öffnet eine Welt in einem ganzen geheimen Bereich von Excel, den Array-Formeln! Geben Sie ein Startdatum und ein Enddatum ein, und diese Formel berechnet die Anzahl der Freitag, den 13., die zwischen diesen beiden Daten aufgetreten sind. Es werden tatsächlich fünf Berechnungen an jedem einzelnen Tag zwischen diesen beiden Daten durchgeführt, 91895 Berechnungen + SUMME, 91896 Berechnungen, die innerhalb dieser einen kleinen Formel stattfinden, in Ordnung. Am Ende dieser Episode werden Sie von Array-Formeln so fasziniert sein. Ich möchte darauf hinweisen,Mein Freund Mike Girvin hat das beste Buch über Array-Formeln mit dem Namen "Strg + Umschalt" Enter. Dies ist ein neuer Druck mit dem blauen Umschlag, der früher ein gelber und grüner Umschlag war. Was auch immer Sie bekommen, es ist ein großartiges Buch, das sowohl im gelben als auch im grünen Buch den gleichen Inhalt hat.
Also gut, beginnen wir mit einer Formel namens INDIRECT, die Sie vielleicht noch nicht gehört haben. Mit INDIRECT können wir Text verketten oder auf irgendeine Weise erstellen, der wie eine Zellreferenz aussieht. Okay, nehmen wir an, wir haben hier ein Preisrad, und ich habe Sie nur gebeten, zwischen A, B und C zu wählen. Also gut, Sie wählen dies und wählen C und dann wählen Sie dies und wählen Sie 3, in Ordnung, und Ihren Preis ist ein Resortaufenthalt, denn das ist es, was in C3 gespeichert ist. Und die Formel hier wird zusammengefügt, was auch immer seit C5 und was auch immer in C6 ist, indem das & verwendet wird und dieses dann an INDIRECT übergeben wird. Also = INDIREKT (C5 & C6) ist in diesem Fall C3, das muss eine ausgeglichene Referenz sein. INDIRECT sagt: "Hey, wir gehen zu C3 und geben die Antwort darauf zurück, okay?" Zurück in Lotus 1-2-3 wurde dies als @ @ -Funktion bezeichnet.In Excel wurde es in INDIREKT umbenannt. Okay, also haben Sie im INDIREKTEN, hier ist das Erstaunliche, was dort drinnen passiert.
Wir haben zwei Daten, wie speichert Excel Daten, 17.02.1965, das ist wirklich nur Formatierung. Wenn wir uns die tatsächliche Zahl dahinter angesehen haben, ist es 23790, was bedeutet, dass es 23790 Tage seit dem 1.1.1900 und 42167 Tage seit dem 1.1.1980 sind. Auf einem Mac ist es seit dem 1.1.1904, also sind die Daten ungefähr 3000 Rabatt. Okay, so speichert Excel es, es zeigt es uns zwar dank dieses Zahlenformats als Datum, aber wenn wir B3 und a: und B4 miteinander verketten würden, würden wir tatsächlich die hinter den Kulissen gespeicherten Zahlen erhalten. Also = B3 & ”:” & B4, und wenn wir das an INDIRECT übergeben würden, würde es tatsächlich auf alle Zeilen von 23790 bis 42167 zeigen.
Es gibt also das INDIREKTE von B6, ich habe nach der REIHE gefragt, das wird mir eine ganze Reihe von Antworten geben und herausfinden, wie viele Antworten ich verwendet habe, in Ordnung. Und damit dies funktioniert, wenn ich nur die Eingabetaste drücke, funktioniert es nicht. Ich muss Strg und Umschalt gedrückt halten und die Eingabetaste drücken, um zu sehen, dass hier oben das () um die Formel hinzugefügt wird. Es weist Excel an, in den Super-Formel-Modus und den Array-Formel-Modus zu wechseln und die gesamte Berechnung für alles durchzuführen, was aus diesem Array, 18378, herausgekommen ist. Das ist also ein erstaunlicher Trick, indirekt von Datum1: Datum2, übergeben Sie ihn an die ROW-Funktion, und hier ist ein kleines Beispiel.
Wir wollen nur herausfinden, wie viele Freitage in diesem Juli aufgetreten sind. Hier ist ein Startdatum, hier ist ein Enddatum. Für jede dieser Zeilen werde ich nach dem WOCHENTAG fragen. WOCHENTAG sagt uns, welcher Wochentag es ist, und hier in der 2 Argumentation Freitags wird ein Wert von 5 haben. Also suche ich nach der Antwort, und wir werden diese Formel wählen, gehen Sie zu Formeln, und Formel auswerten, und Formel auswerten ist eine großartige Möglichkeit, um zu sehen, wie eine Formel in Zeitlupe berechnet wird. Es gibt also die B3, den 1. Juli, und Sie sehen, dass sich eine Zahl ändert, und dann schließen wir uns dem Doppelpunkt an, richtig, es gibt die B4, die sich in eine Zahl ändert, und jetzt erhalten wir den Text 42186: 42216. An diesem Punkt übergeben wir das an die REIHE, und dieser einfache kleine Ausdruck wird genau hier in 31 Werte umgewandelt.
In dem Beispiel, in dem ich von 1965 bis 2015 alles hatte, wurden 86000 Werte angezeigt, richtig, und Sie möchten das nicht tun und die Formel bewerten, weil das irgendwie verrückt wäre, in Ordnung? Aber Sie können sehen, was hier mit der 31 passiert, und jetzt übergebe ich diese 31 Tage an die WEEKDAY-Funktion, und wir erhalten 3-4-5. 3 bedeutet also, dass es ein Mittwoch war, und 4 bedeutet, dass es ein Donnerstag war, und 5 bedeutet, dass es ein Freitag war. Nehmen Sie alle diese 31 Werte und sehen Sie, ob sie = 5 sind, was ein Freitag ist, und wir werden eine Reihe von FALSEs und TRUEs erhalten. Mittwoch, Donnerstag, Freitag und dann 7 Zellen später wären die nächsten TRUE. genial!
Okay, in diesem Fall haben wir 5 TRUEs und 26 FALSEs. Um diese zu addieren, muss ich die FALSE in 0 und die TRUEs in 1 konvertieren. Eine sehr gebräuchliche Methode ist die Verwendung von - . Okay, leider hat es dort nicht die Antwort gezeigt, wo wir eine ganze Reihe von Einsen und Nullen gesehen haben, aber genau das passiert, und dann addiert SUMPRODUCT es und bringt uns auf die 5. Hier unten, wenn wir wollen Finden Sie heraus, wie viele vom 13. des Monats es in diesem Jahr von diesem Startdatum bis zu diesem Enddatum einen sehr ähnlichen Prozess gab. Obwohl wir 365 haben werden, übergeben Sie das an die DAY-Funktion und überprüfen Sie, wie viele 13 sind, in Ordnung. Für das 92000-Zeilen-Beispiel erhalten Sie den Tag, den Wochentag und prüfen, ob DAY = 13 ist. Überprüfen Sie, ob WEEKDAY = FALSE ist. Multiplizieren Sie dies * this,und nur in den Fällen, in denen ein Freitag der 13. ist, endet das als WAHR. Das SUMPRODUCT sagt dann "Addiere all diese" und so erhalten wir die 86, buchstäblich 91895 Berechnungen + SUMME, 91896, die innerhalb dieser einen Formel passieren, es ist verrückt mächtig! Kaufen Sie Mikes Buch, es ist ein erstaunliches Buch, es wird Ihnen eine ganze Welt von Excel-Formeln öffnen, und eigentlich sollten Sie einfach beide Bücher kaufen. Kaufen Sie mein Buch, kaufen Sie Mikes Buch, und Sie werden eine großartige Sammlung haben, die Sie durch den Rest des Jahres führen wird.Es öffnet Ihnen eine ganze Welt von Excel-Formeln, und eigentlich sollten Sie einfach beide Bücher kaufen. Kaufen Sie mein Buch, kaufen Sie Mikes Buch, und Sie werden eine großartige Sammlung haben, die Sie durch den Rest des Jahres führen wird.Es öffnet Ihnen eine ganze Welt von Excel-Formeln, und eigentlich sollten Sie einfach beide Bücher kaufen. Kaufen Sie mein Buch, kaufen Sie Mikes Buch, und Sie werden eine großartige Sammlung haben, die Sie durch den Rest des Jahres führen wird.
Okay, also rekapitulieren Sie: Es gibt eine geheime Klasse von Formeln, die Array-Formeln genannt werden, und Array-Formeln können Tausende von Zwischenberechnungen durchführen. Normalerweise müssen Sie Strg + Umschalt + Eingabetaste drücken, aber nicht immer. Das beste Buch für Array-Formeln ist Mike Girvins Buch „Strg + Umschalt + Eingabetaste“. Okay, mit INDIRECT können Sie mithilfe der Verkettung etwas erstellen, das wie eine Zellreferenz aussieht, und dann geht INDIRECT zu dieser Zellreferenz. Wenn Sie zwei Datumsangaben mit einem Doppelpunkt verketten, wird in Excel auf eine Reihe von Zeilen verwiesen. Wenn Sie dann nach der REIHE des INDIREKTEN Datums1 fragen: Datum2 wird ein Array mit vielen aufeinander folgenden Zahlen angezeigt, z. B. 31, möglicherweise 365 oder möglicherweise 85000. Überprüfen Sie Überprüfen Sie an jedem Datum, ob der WOCHENTAG = Freitag ist, jeden Tag, um festzustellen, ob der TAG = 13 ist, und multiplizieren Sie diese beiden Arrays von TRUEs und FALSEs mit dem SUMPRODUCT. In vielen Fällen haben wir 'll benutze -, um TRUE / FALSE in 1 und 0 umzuwandeln, damit das SUMPRODUCT funktioniert. Es ist eine großartige Formel, ich habe sie nicht erstellt, ich habe sie im Message Board gefunden, als ich sie durchgearbeitet habe. Ich bin wie "Wow, das ist wirklich cool!"
Okay, ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2026.xlsx
