Inhalt
- Führen Sie Ihre Berechnungen in einem Schritt durch und verlieren Sie die zusätzlichen Spalten
- Erster Schritt: Überprüfen Sie die Liste der nativen Funktionen von Excel
- Schritt 2: Wählen Sie den Datenbereich aus, für den Sie Ihre Berechnungen durchführen möchten
- Schritt drei - Geben Sie Ihre Array-Formel ein
- Schritt vier: Testen Sie Ihre Array-Formel
- Wie berechne ich die Gesamtzahl der Zeichen in einer Sammlung von Zellen?
- Wie bestimme ich die Gesamtzahl der Wörter in einer Sammlung von Zellen mit Zeichenfolgen?
- Ich kann nichts in die Formelleiste eingeben, da das Excel-Dialogfeld "Funktion einfügen" immer wieder im Weg ist!
- Denken Sie, dass Excel-Array-Formeln bei Ihrer Arbeit hilfreich sein könnten?
Ich gebe gerne Tipps und Ratschläge zur Verwendung verschiedener Computerprogramme.
Führen Sie Ihre Berechnungen in einem Schritt durch und verlieren Sie die zusätzlichen Spalten
Wenn Sie in Excel viele Zahlen eingeben, die Arrays beinhalten, und den Gedanken hassen, zusätzliche Spalten mit Zahlen erstellen zu müssen, um Zwischenergebnisse anzuzeigen, sind Sie hier genau richtig. Wie sich herausstellt, gibt es eine einfachere und elegantere Möglichkeit, mehrstufige Berechnungen mit Arrays durchzuführen. Excel ermöglicht seit langem die Verwendung von benutzerdefinierten Formeln, die Arrays als Argumente akzeptieren können, obwohl diese Funktionen bis auf fortgeschrittene und fortgeschrittene Benutzer noch relativ unbekannt sind. Sie werden als Array-Formeln bezeichnet und manchmal auch als CSE-Formeln bezeichnet. Zum Aufrufen muss der Benutzer nach Eingabe der Formel die STRG-UMSCHALTTASTE (anstelle der üblichen EINGABETASTE) drücken. Abhängig von den Arten von Berechnungen, die Sie ausführen müssen, kann sich eine Array-Formel entweder in einer Zelle oder in mehreren befinden, wenn die Ergebnisse aus mehr als einem Wert bestehen.
Wie unten gezeigt wird, können Sie mit Array-Formeln in weniger Schritten viele verschiedene Arten von Berechnungen durchführen, die nicht in der Liste der nativen Funktionen von Excel enthalten sind. Wenn Sie zum ersten Mal Array-Formeln verwenden und sich mit deren Verwendung vertraut machen möchten, führt Sie dieses Einführungs-Tutorial durch die grundlegenden Schritte zum Durchführen einiger einfacher Ein-Schritt-Berechnungen mit Einzelzellen-Array-Formeln. In diesem Artikel werden anhand einiger einfacher Beispiele Möglichkeiten zum Erstellen eigener Array-Formeln erläutert.
Erster Schritt: Überprüfen Sie die Liste der nativen Funktionen von Excel
Mit Array-Formeln können Sie den Prozess umgehen, zusätzliche Spalten mit Zwischenergebnissen in Ihrer Arbeitsmappe erstellen zu müssen, damit Sie direkter zum Endergebnis Ihrer Berechnungen gelangen. Sie erleichtern nicht nur die Organisation Ihrer Arbeitsmappe, sondern reduzieren auch deren Größe.Es gibt jedoch einen Kompromiss, da die Informationen in (was früher war) diesen zusätzlichen Spalten vorübergehend im Speicher von Excel gespeichert werden. Bei weniger leistungsstarken Computern können Array-Formeln manchmal zu erheblichen Leistungseinbußen führen. Dies ist problematischer, wenn Sie mit sehr großen Datenmengen arbeiten oder viele Arrayformeln verwenden und Ihre Arbeitsmappe für automatische Aktualisierungen eingerichtet haben.
Daher besteht der erste Schritt darin, die Liste der nativen Funktionen von Excel zu überprüfen, um festzustellen, ob bereits eine vorhanden ist, die die von Ihnen beabsichtigten Berechnungen ausführen kann. Eine Möglichkeit, dies zu tun, besteht darin, die Funktionslisten im Dialogfenster "Funktion einfügen" zu überprüfen, wie oben dargestellt. Eine native Funktion erspart Ihnen nicht nur die zusätzliche Arbeit beim Eingeben einer benutzerdefinierten Array-Formel, sondern wird höchstwahrscheinlich auch schneller ausgeführt. Angenommen, Sie möchten die Summe der Quadrate einer Zahlenspalte berechnen. Früher (ca. 1990) mussten Sie eine zusätzliche Zahlenspalte erstellen, die aus den Quadraten jeder Zahl in Ihrem Originaldatensatz besteht, und diese neuen Werte dann als Array-Argument in der SUMME-Funktion von Excel verwenden. In späteren Versionen von Excel wurde die SUMSQ-Funktion endlich verfügbar, sodass Sie diesen zusätzlichen Schritt nicht mehr ausführen müssen.
Spätere Versionen von Excel enthalten zwar zunehmend native Funktionen mit größerer Komplexität, die Arrays als Argumente akzeptieren, können jedoch möglicherweise nicht alle potenziellen Benutzeranforderungen vorhersehen. Wenn Sie beispielsweise die Summe der Würfel oder die Summe eines Polynoms höherer Ordnung mit mehr als einem Term ausführen möchten, verfügen die nativen Funktionen von Excel noch nicht über diese Funktionen. In solchen Situationen sind Array-Formeln so ziemlich die einzige Alternative.
In den Schritten 2 bis 4 werde ich als Beispiel die Summe der Cubes in einem Array verwenden, da für Excel 2007, die Version, die ich in diesem Lernprogramm verwenden werde, eine native Funktion noch nicht verfügbar ist.
Schritt 2: Wählen Sie den Datenbereich aus, für den Sie Ihre Berechnungen durchführen möchten
In einer Array-Formel können Sie entweder Ihr Datenarray als Bereich eingeben (z. B. A2: A7), oder Sie können ihm einen Namen geben und diesen als Argument verwenden.
Wenn ich Array-Formeln verwende, ist es meine persönliche Präferenz, meinem Datenarray einen Namen zu geben. Dies ist die Methode, die ich in diesem Lernprogramm verwenden werde. Wenn Sie dies auf diese Weise tun und die Anzahl der Zeilen oder Spalten jemals ändern müssen, müssen Sie diese Änderungen nur einmal im "Namensmanager" vornehmen, anstatt das Array erneut eingeben und erneut eingeben zu müssen Formel. (Dies minimiert auch die Möglichkeit, Fehler während der Bearbeitung einzuführen, insbesondere bei komplizierteren Array-Formeln, bei denen der Datenbereich mehr als einmal als Argument auftreten kann.)
Um Ihr Datenarray zu benennen,
- Wählen Sie das Datenarray aus, für das Sie Ihre Berechnungen durchführen möchten.
- Klicken Sie oben auf die Registerkarte Formeln.
- Klicken Sie im Bereich "Definierte Namen" auf "Name definieren".
- Geben Sie im Dialogfenster Ihren Array-Namen ein (z. B. MyArray).
Hinweis: Die obigen Anweisungen gelten für Excel 2007, die für dieses Lernprogramm verwendete Version, und können von einer Excel-Version zur nächsten geringfügig abweichen.
Schritt drei - Geben Sie Ihre Array-Formel ein
Hier berechnen wir die Summe der Würfel der Zahlen im Datenarray A2: A7, das ich MyArray genannt habe (siehe Schritt 2 oben). Die hier gezeigten Array-Formelberechnungen verwenden die nativen Excel-Funktionen SUM und POWER.
- Wählen Sie die Zelle aus, in der sich Ihre Array-Formel befinden soll. (Hier wird das Endergebnis angezeigt.)
- Geben Sie das Gleichheitszeichen (=) ein.
- Geben Sie direkt nach dem = -Zeichen Ihre Formel in die Formelleiste ein. Da wir die Summe der Würfel für jedes Element in MyArray berechnen, geben Sie Folgendes ein: SUMME (POWER (MyArray, 3))
- Drücken Sie gleichzeitig die Tasten STRG + UMSCHALT + EINGABETASTE. Sie sollten die Ergebnisse in der Zelle sehen, in der Sie Ihre Array-Formel eingegeben haben (oben rot umrandet). Sie sollten auch geschweifte Klammern um die Formel sehen, die Sie gerade in die Formelleiste eingegeben haben, wie im obigen Screenshot gezeigt.
Schritt vier: Testen Sie Ihre Array-Formel
- Klicken Sie auf die Zelle, in die Sie Ihre Array-Formel eingegeben haben, und überprüfen Sie, ob sie in der Formelleiste von geschweiften Klammern umgeben ist.
- Nehmen Sie einige Änderungen in einer oder mehreren Zellen in Ihrem Bereich vor und stellen Sie sicher, dass die richtige endgültige Antwort in der Zelle angezeigt wird, in der Sie Ihre Array-Formel eingegeben haben.
Wie im obigen Screenshot gezeigt, wird durch Erhöhen des Werts von A7 von 6 auf 60 die Summe der Würfel, wie in Zelle B2 gezeigt, korrekt von 441 (der oben in Schritt 3 gezeigte ursprüngliche Wert) auf 216225 aktualisiert.
Eine Warnung
Wenn Sie vergessen, STRG + UMSCHALT + EINGABETASTE zu drücken und stattdessen versehentlich EINGABETASTE drücken, wird je nach Excel-Version entweder eine Fehlermeldung oder, schlimmer noch, fehlerhafte Ergebnisse angezeigt. Da es leicht zu vergessen ist, überprüfen Sie immer die Formelleiste auf diese geschweiften Klammern!
Wie berechne ich die Gesamtzahl der Zeichen in einer Sammlung von Zellen?
In diesem Beispiel befinden sich die Daten in den Zellen A1-A6 (siehe Screenshot), also MyArray = A1: A6. Die Array-Formel zur Berechnung der Gesamtzahl der Zeichen in diesen Zellen (und des Endergebnisses) befindet sich in Zelle B1 (rot umrandet).
Die Anzahl der Zeichen für eine beliebige Zeichenfolge, die sich in Zelle A1 befindet, kann leicht aus der LEN-Funktion ermittelt werden, d. H.
LEN (A1)
Um die Gesamtzahl der Zeichen für jedes Mitglied eines Arrays zu ermitteln, ersetzen Sie "A1" in der obigen Formel durch den Namen oder den Bereich des Arrays und verwenden Sie ihn als Argument für die SUMME-Funktion, d. H.
SUMME (LEN (MyArray))
Wenn Sie das Obige als Array-Formel eingeben, sollte Folgendes in der Formelleiste angezeigt werden:
{= SUMME (LEN (MyArray))}
Wie bestimme ich die Gesamtzahl der Wörter in einer Sammlung von Zellen mit Zeichenfolgen?
In diesem Beispiel befinden sich die Daten in den Zellen C1-C3, also MyArray = C1: C3. Die Array-Formel zur Berechnung der Gesamtzahl der Wörter in diesen drei Zellen (und des Endergebnisses) befindet sich in Zelle D1 (rot umrandet).
Für eine einzelne Zelle C1 kann die Anzahl der Wörter gefunden werden, indem gezählt wird, wie oft das Leerzeichen auftritt, und 1 addiert wird, d. H.
LEN (C1) -LEN (ERSATZ (C1, "", "")) + 1
Um die Gesamtzahl der Wörter für jede Zelle in einem Array zu ermitteln, ersetzen Sie "C1" in der obigen Formel durch den Namen oder den Bereich des Arrays und verwenden Sie diesen als Argument für die SUMME-Funktion, d. H.
SUMME (LEN (MyArray) -LEN (ERSATZ (MyArray, "", "")) + 1)
Wenn Sie das Obige als Array-Formel mit STRG + UMSCHALT + EINGABETASTE eingeben, sollte in der Formelleiste Folgendes angezeigt werden (siehe Abbildung oben):
{= SUMME (LEN (MyArray) -LEN (ERSATZ (MyArray, "", "")) + 1)}
Ich kann nichts in die Formelleiste eingeben, da das Excel-Dialogfeld "Funktion einfügen" immer wieder im Weg ist!
Klicken Sie nicht auf das Symbol "Funktion einfügen" links neben der Formelleiste (hier rot durchgestrichen dargestellt). Wählen Sie stattdessen die Zelle aus, in der sich Ihre Formel befinden soll, und geben Sie ein Gleichheitszeichen (=) ein.
Das Gleichheitszeichen wird dann in der Formelleiste angezeigt, sodass Sie die gewünschte Formel eingeben können.
Dieser Artikel ist genau und nach bestem Wissen des Autors. Der Inhalt dient nur zu Informations- oder Unterhaltungszwecken und ersetzt nicht die persönliche Beratung oder professionelle Beratung in geschäftlichen, finanziellen, rechtlichen oder technischen Angelegenheiten.
Denken Sie, dass Excel-Array-Formeln bei Ihrer Arbeit hilfreich sein könnten?
othellos am 06. Juli 2013:
Sehr gutes Objektiv für Excel-Formeln. Ihre vorgeschlagene Methode ist für mich sehr hilfreich, da ich in meiner Arbeit häufig Formeln verwende. Ich hätte nie gedacht, sie so zu machen. Schöne Tipps. Vielen Dank: =)
Susan R. Davis von Vancouver am 31. Oktober 2012:
Ja, und ich schätze das großartige Tutorial.
JaneEyre9999 am 10. Mai 2012:
Ja! Vielen Dank, egal wie lange wir Tabellenkalkulationssoftware verwendet haben, wir alle stecken in unseren alten "Tricks" fest.
nhotdeals am 13. März 2012:
Die schwierigsten sind versetzt und indirekt. Bill Jelen hatte die besten Excel-Bücher
anonym am 09. März 2012:
Alle Excel-Benutzer müssen dieses Objektiv und Sie lieben. Liebe Mary Stuart :) Eine weitere großartige und hilfreiche Arbeit von Ihnen. 5 Sterne. Habe wundervolle Zeiten .. immer .. liebe Mary: D.
Beverly Rodriguez aus Albany New York am 06. März 2012:
Sehr gut gemachter und gründlicher Artikel!
SophiaStar LM am 06. März 2012:
Ich muss das griffbereit halten, Excel ist nicht meine Stärke! Vielen Dank für diese tollen Tipps!
SoniaCarew am 06. März 2012:
Vielen Dank! Dies ist leicht mein Lieblingsobjektiv, da ich Excel nicht mag, es aber täglich verwenden muss. Dieses Objektiv wird mir sehr helfen ... Danke, danke!
anonym am 06. März 2012:
Das ist gut, ich hatte gestern einen Freund, der mit mir über Excel sprach
Itaya Lightbourne von Topeka, KS am 05. März 2012:
Toller Artikel und wird auf jeden Fall nützlich sein! :) :)
Susan Deppner aus Arkansas USA am 05. März 2012:
Sieht nach sehr vollständigen Informationen aus!
Scriber1 LM am 05. März 2012:
Ja, ich arbeite oft mit Excel und dieses Objektiv ist sehr informativ und hilfreich. Danke und tolle Arbeit!
