Array-Formeln (Matrix) - Excel Office Version 10
Der folgende Beitrag stammt von einem meiner US-MVP-Kollegen:
Bob Umlas
Er ist seit 1995 MVP und hat viele Artikel über Excel geschrieben.
Er hat mir erlaubt, einen seiner veröffentlichten Beiträge zu übersetzen
und hier zu publizieren.
Ein herzliches Dankeschön!
Ich fand es lohnenswert, diesen Beitrag zu übersetzen, da
er mit einfachen Beispielen den Gebrauch von Array-Formeln darstellt.
Den englische Originaltext findest Du hier:
http://www.emailoffice.com/excel/arrays-bobumlas.html
Übersetzt von: Monika Weber - http://www.jumper.ch
Inhaltsverzeichnis
1.) Was sind Array-Formeln?
2.) Die Funktion ODER verwenden
3.) Die letzte benutzte Zeile in einem Bereich ermitteln
4.) Innerhalb eines Bereiches nach Fehlern in einer
Zeichenkette suchen
5.) Das Auftreten von bestimmten Zeichen in einem
Bereich zählen
6.) Den letzten Teil einer Zeichenkette finden
7.) Mehr als eine Spalte mit einer Zeichenkette
vergleichen
8.) Sind doppelte Werte in zwei Bereichen
enthalten?
9.) Entscheiden, ob der Inhalt eines Bereiches
eindeutig ist
10.) Berechnen von Quersummen
11.) Nur den numerischen Teil einer Zelle ermitteln
12.) Den nahesten Wert zum Mittelwert finden
13.) Bedingungen verwenden, um das letzte Kriterium
zu ermitteln
14.) Einen Kalender mit einer einfachen
Array-Formel erzeugen
Kannst Du Dich erinnern schon das Bedürfnis verspürt zu haben, mittels Formeln zu prüfen, ob Deine Daten richtig sind oder nicht? Zu ermitteln, ob aufeinander folgende Zellen eines Bereiches, oder auch zwei, doppelte Werte enthalten? Kannst Du in einer Formel bestimmen, ob Dein Text ein bestimmtes Muster in einem Bereich aufweist (wie zum Beispiel Vorname, Leerzeichen, Nachname?) und wenn nicht, dann diese Zelle als fehlerhaft zu markieren?
Wie oft hast Du Dir schon gewünscht, dass Du auf einfache Art, aus einer Liste das Total von Beträgen aus dem Jahre 1995 aus der Abteilung »Finanz« zu ermitteln, wenn die Liste auch Daten außer 1995 und von anderen Abteilungen enthält als »Finanz«? Array-Formeln bieten die Lösung. Möchtest Du die Summe von Ziffern in einer Zelle bilden? Versuch es. Mit einer Array-Formel klappt es (gib 81736 in eine Zelle ein und mit einer Formel kannst Du folgendes berechnen: 8+1+7+3+6). Auch dieses Ergebnis kannst Du mit einer Array-Formel berechnen.
Array-Formeln sind ein mächtiges Instrument zum summieren von Daten in einer Tabelle oder Daten, welche sich nicht auf dem Tabellenblatt befinden (zum Beispiel um die Anzahl Beträge im März zu ermitteln, wenn der Bereich nicht nur Daten aus dem Monat März enthält. Wie soll das mit MONAT(A1:A50) funktionieren?)
Wenn Du eine Array-Formel erzeugen möchtest, dann musst Du immer die Tastenkombination CTRL + SHIFT drücken, bevor Du die Formel mit ENTER abschließt. Dabei werden geschweifte Klammern um die Formel herum ( { } ) erzeugt. Diese sind nur in der Bearbeitungsleiste sichtbar. Gibt diese Klammern nie von Hand ein, sondern erzeuge sie nur mit der erwähnten Tastenkombination. Am besten ist das an Beispielen zu erklären.
2.) Die Funktion ODER verwenden
Die Technik ist keine Array-Funktion und sie muss auch nicht als solche eingegeben werden. Aber sie muss hier in unsere Beispiele mit einbezogen werden, denn es kann mit Array-Konstanten gearbeitet werden, die Du vielleicht noch nicht kennst. Nehmen wir mal an, Du hast eine Zelle, B1, in welcher Du überprüfen möchtest, ob folgendes enthalten ist: eine 4, 6 oder 9. Jetzt versuchst Du vielleicht folgendes einzugeben: =ODER(B1=4;B1=6;B1=9), was auch wirklich funktioniert, aber es gibt einen einfacheren Weg. Die Formel =ODER(B1={4;6;9}) liefert das selbe Ergebnis.
Hier müssen jedoch die geschweiften Klammern von Hand eingegeben werden. Wenn Du Array-Konstanten eingibst, können keine Variablen verwendet werden auch nicht, wenn deren Werte bekannt sein könnten. Wenn Du ein x definierst, welches =4 sein könnte, dann kannst Du das nicht so in der Formel verwenden =ODER(B1={x;6;9}) - Du würdest eine Fehlermeldung erhalten.
3.) Die letzte benutzte Zeile in einem Bereich ermitteln
Diese Technik funktioniert für alles gleich, für einzelne, mehrere zusammenhängende oder mehrere unabhängige Bereiche. Es wird jeweils die letzte benutzte Zeile in dem gewünschten Bereich ermittelt. Zum Beispiel könnte der Bereich A1:A20 und dann der Bereich A30:A35 gefüllt sein. Die letzte Zelle wäre in diesem Fall die Zelle A35. Schauen wir uns das anhand eines Beispiels an:
Um die letzte benutzte Zeile in einem Bereich zu finden kannst Du folgende Array-Formel verwenden: {=MAX(ZEILE(1:10)*(A1:E10<>""))}. Der Teil A1:E10<>"" ergibt ein 10x5 Array, bestehend aus WAHR/FALSCH Werten. Diesen Vergleich kannst Du auch mit der Funktion ISTLEER erreichen: =NICHT(ISTLEER(A1:E10)). FALSCH bedeutet, der Bereich IST leer. WAHR bedeutet, der Bereich beinhaltet Werte. Wenn Du dieses Array nun mit Werten multiplizierst {1;2;3;...;9;10}, was mit ZEILE(1:10) erreicht wird, dann erhältst Du {0,0,0,0,0;0,0,0,0,0;0,0,0,3,0;4,0,0,0,0;0,0,0,0,0;0,0,6,0,0;0,0,0,0,0;0,0,0,0,8;0,9,0,0,0;0,0,0,0,0}. Das entspricht 10 Zeilen à je 5 Zellen, wobei immer, wenn eine Zelle einen Wert enthält, die Zeilennummer ausgegeben wird.
Beachte, dass immer nach 5 Werten ein Semikolon enthalten ist, was jeweils »die nächste Zeile« bedeutet. Die Nullen (0) sind das Resultat der Multiplikation des Werts FALSCH in jeder Zelle. Wenn Du den gesamten Array betrachtest, dann wirst Du als erste Zahl eine 3 entdecken. Das bedeutet, dass in der dritten Reihe ein Wert enthalten ist {0,0,0,3,0}. Das entspricht der Zelle D3 im Bereich A1:E10. Wir sind also in der dritten Zeile. WAHR multipliziert mit 3 ergibt 3. Genauso entspricht eine nicht-Null einem nicht-Leer in einem Bereich. Dieses Array wurde angeführt durch die Funktion MAX. In diesem Falle wird als Resultat eine 9 ausgegeben, welche dem höchsten Wert in dem Array entspricht. Das bedeutet, dass die letzte benutzte Zeile in dem Bereich die 9. Zeile ist.
Beitrag vom: 18. März 2003
4.) Innerhalb eines Bereiches nach Fehlern in einer Zeichenkette suchen
Gehen wir einmal davon aus, dass im Bereich von A1:A10 Namen enthalten sind. In der Form: Vorname, Leerzeichen, Nachname. Gehen wir außerdem davon aus, dass in den Zellen A7 und A9 Fehler enthalten sind - es sind keine Leerzeichen zwischen dem Vor- und dem Nachname. (Wie BillClinton).
Mit Hilfe dieser Technik kannst Du herausfinden, ob und welche Namen Fehler enthalten. Geben wir mal in der Zelle B2 die Formel =WENN(NICHT(ISTFEHLER(B1));INDEX(A1:A10;B1)&" is incorrect.";"") ein, welche besagt, dass wenn in der Zelle B1 kein Fehler ist, dann gib den Inhalt der Zelle aus plus den Text " is incorrect ". Die Zelle, welche gegen die Regel verstößt, wird durch INDEX(A1:A10;B1) angezeigt. Dazu muss in der Zelle B1 der Ort der Zelle ausgegeben werden, welche einen Fehler enthält, also innerhalb des Bereichs A1:A10. Die Zelle B1 beinhaltet die Array-Formel {=VERGLEICH(WAHR;ISTFEHLER(SUCHEN(" ";A1:A10));0)}. Das ist eine interessante Formel welche gewöhnlich in Excel so nicht verwendet wird VERGLEICH(WAHR...). Du wirst feststellen, dass damit (VERGLEICH(WAHR...)) ein sehr großer Effekt erzeugt werden kann. Schauen wir uns zuerst den inneren Teil der Formel an: SUCHEN(" ";A1:A10). Für gewöhnlich wird mit dieser Funktion nur eine Zelle und nicht ein ganzer Bereich angesprochen. Es ist deshalb erforderlich, dass die Formel als Array-Formel eingegeben wird.
Die Funktion gibt etwas in der Art zurück: {4;3;5;4;8;8;#WERT!;6;#WERT!;6}. Der erste Wert, 4, bedeutet, dass das Leerzeichen an der 4. Position der Zelle A1 zu finden ist. Das Selbe gilt für 3 und 5. Das heißt, in der Zelle A2 wird an 3. Stelle das Leerzeichen gefunden und in der Zelle A3 wird das Leezeichen an 5. Stelle gefunden. Der Fehler #WERT! befindet sich an der 7. Position in dem Array, was in unserem Beispiel der Zelle A7 entspricht, welche BillClinton enthält. Das sagt uns, dass in dieser Zelle kein Leerzeichen zu finden ist. Die Funktion VERGLEICH ist nun dazu da, den Wert WAHR im Array zu finden. Der Wert in Zelle B1, in unserem Fall die 7 ist das Ergebnis (Das »,0« am Ende der Funktion VERGLEICH bewirkt, dass ein exakter Vergleich stattfindet). Wenn alle Zellen ein Leerzeichen enthalten würden, dann wird im Array jeweils ein FALSCH eingetragen und die Funktion VERGLEICH würde eine Fehlermeldung ausgeben. Folglich würde die Zelle B2 leer bleiben, denn die Formel darin würde den Fehler in Zelle B1 abfangen.
Beitrag vom: 19. März 2003
5.) Das Auftreten von bestimmten Zeichen in einem Bereich zählen
Nehmen wir einmal an, Du möchtest zählen, wie oft der Buchstabe »a« in den Zellen eines bestimmten Bereiches vorkommt. Oder, wie viele Zellen ein »a« enthalten. Für jede der beiden Möglichkeiten gibt es eine eigene Lösung.
Beispiel 1:
Um die Anzahl an Zellen zu ermitteln, die mindestens ein
»a« beinhalten, verwendest Du die Formel: {=SUMME(WENN(ISTFEHLER(SUCHEN("a";A1:B3));;1))}. Diese Formel
enthält die Funktion SUCHEN, welche dazu da ist, einen Bereich an Zellen zu durchsuchen. Diese
Funktion wird für gewöhnlich nicht verwendet, um einen Bereich (Range) zu
durchsuchen, sondern eine
Zeichenkette (String). Wir benötigen deshalb eine Array-Formel. Diese
muss entsprechend mit der Tastenkombination (SHIFT + CTRL + ENTER) abgeschlossen
werden (Verwenden Sie an Stelle von SUCHEN die Funktion FINDEN, um eine
case-sensitive Suche durchzuführen, d.h. wenn zwischen Groß- und Kleinbuchstaben
unterschieden werden soll). SUCHEN gibt eine Reihe an Zahlen oder
Fehlermeldungen zurück. Die Zahlen entsprechen der Position des Buchstabens »a«
in der Zeichenkette. Eine Fehlermeldung würde bedeuten, dass kein »a« in der
gesamten Zeichenkette vorkommt. Das Array stimmt mit jeder Zelle im Bereich überein. Da
es nicht einfach ist, mit Fehlermeldungen zu arbeiten, werden diese durch ISTFEHLER zu WAHR/FALSCH konvertiert. Immer wenn ein FALSCH gefunden wird, dann
gibt WENN eine 1 zurück. Nebenbei erwähnt: Dieses Ergebnis kann auch durch
folgende Formel erreicht werden: {=SUMME(1*NICHT(ISTFEHLER(SUCHEN("a";A1:B3))))}.
Hier wird das FALSCH in ein WAHR umgewandelt, indem die Funktion NICHT verwendet
wird. Dann muss noch mit 1 multipliziert werden, damit die Funktion SUMME das
Ganze aufaddieren kann.
Beispiel 2:
Wenn Du wissen möchtest, wie oft der Buchstabe »a« im Wort
»Banane« vorkommt, welches 2 »a«'s hat, dann verwende die Formel:
{=SUMME(LÄNGE(D2:D4))-SUMME(LÄNGE(WECHSELN(D2:D4;"a";"")))}.
Wie funktioniert das? Die Funktion LÄNGE verlangt, wie das schon beim ersten
Beispiel der Fall war, dass ein String und nicht ein Bereich eingegeben wird. Es
ist erforderlich, die Formel als Array abzuschließen, damit Excel weiß, dass die
Funktion LÄNGE jeden Wert im Bereich als String zu betrachten hat. Die
Funktion LÄNGE(A1:A2) würde {3;5} zurückgeben, wenn der Inhalt von A1 »Bob« und
der Inhalt von A2 »Sally« wäre. Mittels SUMME(LÄNGE(MeinBereich)) kann
also die Anzahl der Zeichen im gesamten Bereich ermittelt werden. Wenn wir vom
Ergebnis MeinBereich alle Zeichen ohne den Buchstaben »a« von der
gesamten Anzahl der Zeichen subtrahieren, dann erfahren wir, wie oft der
Buchstabe »a« vorkommt. Das genau tut der zweite Teil der Formel. Die Funktion
WECHSELN verändert den Buchstaben »a« in einen Null-String (""). In einem
Bereich, welcher Zellen beinhaltet, gibt jede Zelle eine Reihe an Werten zurück,
welche an die Funktion LÄNGE übergeben werden und addiert diese (SUMME).
Beitrag vom: 20. März 2003
6.) Den letzten Teil einer Zeichenkette finden
Es kommt häufig vor, dass aus einem gesamten Pfad nur der Dateinamen ermittelt werden soll (siehe Abbildung):
Die Zelle A1 enthält als Text den Pfad zu einer Datei. Der Backslash (\) ist das Trennzeichen der einzelnen »Felder«. Die Zelle A1 könnte irgendeinen Text enthalten, wie z.B. »Now is the time for all good men to come to the aid of their party«, wobei hier das Trennzeichen ein Leerzeichen ist. Die Technik, welche wir uns nun anschauen wollen, würde in diesem Satz das Wort »party« von den restlichen »Feldern« trennen. Die Formel in der Zelle A3 ist kompliziert. Sehen wir uns also den Lösungsweg an.
Die Aufgabe wäre einfach, wenn wir nur den ersten Backslash finden müssten. Wir könnten die Funktion SUCHEN verwenden. Wenn wir den Text trennen würden, dann könnten wir den ersten Backslash finden. Unsere Aufgabe ist es also, den Text zu trennen. Es sieht aus, als könnte das mit einer Formel nicht erreicht werden (oder vielmehr, wir haben diesen Weg noch nicht herausgefunden!). Es ist jedoch möglich den Textteil als separaten Teil zu ermitteln. Um ein einfacheres Beispiel zu nehmen: Nehmen wir mal an in einem anderen Tabellenblatt wäre in der Zelle D7 der Text »abc« enthalten. Welche Möglichkeit hätten wir, diese Zeichenkette wie folgt umzustellen: {"c","b","a"}? Nun, das ist wirklich einfach. Die Technik, welche ein {"a","b","c"} daraus macht ist die Array-Formel {=TEIL(D7;ZEILE(1:3);1)}. Sehen wir uns nun an, was geschieht, wenn wir die Formel wie folgt ändern: {=TEIL(D7;4-ZEILE(1:3);1)}. Durch das Einfügen von »4-« subtrahieren wir den Array {1;2;3} vom Wert 4, welcher das Array {3;2;1} erzeugt! Der String wird also umgedreht. Das bedeutet =TEIL(D7;{3;2;1};1) erzeugt {"c","b","a"}.
Um uns der Lösung zu nähern, gehen wir einen Schritt
weiter. Anstelle der Konstanten 4, verwenden wir LÄNGE(D7)+1. Anstatt nur die
Zeichenkette zu trennen, verwenden wir zusätzlich die Funktion VERGLEICH. Damit
können wir den ersten Backslash finden. Die Formel lautet:
{=VERGLEICH("\";TEIL(A1;LÄNGE(A1)+1-ZEILE(1:25);1);0)}. Um die Formel zu
veranschaulichen, wollen wir entschlüsseln, was die Funktion VERGLEICH auslöst:
VERGLEICH("\";{"f";"f";"u";"t";"S";"y";"M";"\";"S";"E";"L";"P";"M";"A";"X";"E";"\";"5";"L";"E";"C";"X";"E";"\";":";";";"C"};0).
Da die Zeichenkette länger als 25 Zeichen sein könnte, kannst Du den Teil
ZEILE(1:25) ersetzen durch ZEILE(1:255).
Nach all diesen Erklärungen möchte ich verraten, dass es auch einen Weg ohne Array-Formel gibt! Unter Verwendung der Funktion WECHSELN kannst Du das letzte Trennzeichen in anderes Zeichen abändern. Suchen dann nach diesem Zeichen in der Zeichenkette. Alles was nach diesem Zeichen kommt ist der letzte Teil. Die Funktion WECHSELN hat folgende Syntax: =WECHSELN(Text;Alter Text;NeuerText;Ntes_Auftreten). Das Ntes_Auftreten wird durch die Funktion WECHSELN berechnet: =LÄNGE(A1)-LÄNGE(WECHSELN(A1;"\";"")). Diese Formel gibt aus, wie oft das Zeichen »\« im Text vorkommt. Die Funktion WECHSELN ersetzt die »\« durch Null Strings (""). Dies ist erforderlich, damit sich die Länge des Textes ändert. Wenn die Zeichenkette wie folgt aussehen würde: "a\a\a", dann würde es wie folgt aussehen: 5-LÄNGE("aaa") oder 5-3, oder 2, nämlich die Anzahl des Backslashs.
Nun ändern wir den letzten Backslash in ein anderes
Zeichen. Zum Beispiel ZEICHEN(13):
=WECHSELN(A1;"\";ZEICHEN(13);LÄNGE(A1)-LÄNGE(WECHSELN(A1;"\";""))).
Nun müssen wir noch die Position dieses Zeichens ermitteln. Dazu ergänzen
wir die Formel um die Funktion FINDEN:
=FINDEN(ZEICHEN(13);WECHSELN(A1;"\";ZEICHEN(13);LÄNGE(A1)-LÄNGE(WECHSELN(A1;"\";"")))).
Das Ganze werden wir nun in eine TEIL-Funktion packen, damit wir endlich den
gewünschten Rest der Zeichenkette ausgeben können:
=TEIL(A1;FINDEN(ZEICHEN(13);
WECHSELN(A1;"\";ZEICHEN(13);LÄNGE(A1)-LÄNGE(WECHSELN(A1;"\";""))))+1;255)
Sicherlich, diese Formel ist länger, als die Array-Formel, aber sie rechnet viel
schneller.
Beitrag vom: 22. März 2003
7.) Mehr als eine Spalte mit einer Zeichenkette vergleichen
Auf der folgenden Abbildung wird gezeigt, dass doppelte Namen in der Spalte A enthalten sind. Um den Betrag in Spalte C zu finden, müssen mehrere Faktoren berücksichtigt werden, wie zum Beispiel der Code in Spalte B. Wir können der Abbildung entnehmen, dass beispielsweise der Eintrag mit dem Namen »John« und dem Code »3« auf der Zeile 9 zu finden ist. Der Betrag ist somit »158«.
Aber wie können wir dieses Ergebnis mit einer Excel-Formel
ermitteln? Wenn die Spalten A und B kombiniert würden, zum Beispiel, indem wir
die Formel =A2&B2 in die Zelle D2 eingeben und nach unten ausfüllen, dann hätten
wir eine einmalige Kombination an Werten. In Spalte B wäre denn folgendes zu
sehen: Bob1;Bob2;Bob3, etc. John3 würde folglich in der Zelle C9 erscheinen. Wir
könnten die Formel =VERGLEICH(E2&F2;D2:D11;0) verwenden, um die Zeile zu
finden, in welcher sich sich das Ergebnis befindet. Nun, mit einer Array-Formel
können wir die Kombination von Spalte A und B simulieren. Die Zelle E4 enthält
die Array-Formel:
{=INDEX(C:C;VERGLEICH(E2&F2;$A$1:$A11&$B$1:$B$11;0))}.
Wir verbinden nun also A1:11 mit B1:B11 (&). Das ergibt folgendes Array:
{"NameCode";"Bob1";"Bob2";"Bob3";"Bob4";"Bob5";"John1";"John2";"John3";"John4";"John5"}.
Vergleichen (VERGLEICH) mit »John3« ergibt das das gewünschte Resultat: 9. Durch
die Verwendung der Index-Funktion erhalten wir das gewünschte Ergebnis: 158.
Beitrag vom: 28. März 2003
8.) Sind doppelte Werte in zwei Bereichen enthalten?
Manchmal hast Du zwei Bereiche und Du musst wissen, ob in diesen beiden Bereichen doppelte Werte vorhanden sind. Das heißt, ob im zweiten Bereich Daten enthalten sind, die schon im ersten Bereich auftauchen. Für gewöhnlich sind die beiden Bereiche gleich aufgebaut. In unserem Beispiel wollen wir jedoch Formeln erstellen, bei denen es keine Rolle spielt, wie die Bereiche aufgebaut sind, bzw. wir bauen zwei Formeln (siehe Abbildung):
In der oberen Hälfte der Abbildung sehen wir zwei Bereiche B2:B4 und D2:D4 und in beiden Bereichen hat es einen Wert 6. Wie Du sehen kannst, der Wert WAHR in Zelle B7, zeigt an, dass ein doppelter Wert vorhanden ist. Rechts neben der Zelle B7 wird die Array-Formel angezeigt: {=ODER(MTRANS(B2:B4)=D2:D4)}.
Die Funktion MTRANS (Transponieren) Wechselt die Ausrichtung des Bereiches B2:B4 in horizontal, also in die andere Richtung als der zweite Bereich D2:D4. Wenn man das tut, Excel vergleicht jeden Wert im ersten Array mit jedem Wert im zweiten Bereich. Es ist also kein einfacher eins-zu-eins-Vergleich. Dei 4 in der Zelle D2 wird verglichen mit {1;6;3}. Die 5 in Zelle D3 wird ebenfalls verglichen mit {1;6;3} und die 6 in Zelle D4 wird ebenfalls mit den selben Werten verglichen {1;6;3}. Das ergibt insgesamt 9 Vergleiche. In der Regel setzt sich die Anzahl der Vergleiche wie folgt zusammen: Die Anzahl der Elemente in jedem Bereich werden miteinander multipliziert. In unserem Falle 3*3. Wenn Du innerhalb der Funktion ODER, also innerhalb der runden Klammern, die Formel markierst und dann die Funktionstaste 9 (F9) drückst, dann kannst Du das Ergebnis sehen: {FALSCH.FALSCH.FALSCH;FALSCH.FALSCH.FALSCH;FALSCH.WAHR.FALSCH}, wobei WAHR an der achten Stelle steht, weil das der Vergleich von D4 zu B3 ist.
Die Werte WAHR und FALSCH werden an die ODER-Funktion übergeben. Jedes Vorkommen von WAHR würde bedeuten, dass dort eine Übereinstimmung stattfindet. Es ist also ein Doppelter Eintrag vorhanden. Wenn keiner der Werte übereinstimmt, dann wird ein FALSCH ausgegeben.
In der zweiten Hälfte der Abbildung findet der Vergleich
bereits auf verschieden ausgerichtete Bereiche statt. Auf die Funktion MTRANS
kann deshalb verzichtet werden. Es muss nur noch der Bereich B10:B12 mit dem
Bereich D10:I10 vergleichen werden. Das bedeutet einen Vergleich von 3*6 bzw.
18. Das Innere der Formel sieht somit wie folgt aus:
{FALSCH.FALSCH.FALSCH.FALSCH.FALSCH;FALSCH.FALSCH.FALSCH.FALSCH.FALSCH.FALSCH;FALSCH.FALSCH.FALSCH.FALSCH.FALSCH.FALSCH.FALSCH}
Beachte, dass immer nach sechs FALSCH ein Semikolon folgt. Das deutet auf die Richtung hin.
Ein Problem, oder eine Einschränkung dieser Technik
Mit dieser Technik gibt es ein Problem. Da ein Bereich
Spalten-orientiert sein muss, ist die Anzahl der Werte, die verglichen werden
kann auf 256 beschränkt. Wie können wir denn nun Duplikate finden, wenn mehr als
256 Werte verglichen werden müssen? Beispielsweise im Falle von A1:A400 gegen
B1:B400. Da würde auch die Funktion MTRANS nicht mehr funktionieren. Das Array
würde auf 256 Werte gekürzt. Es gibt da noch einen anderen Weg, eine
Array-Formel zu verwenden. Wenn Du zwei Bereiche mit der Formel
=VERGLEICH(Bereich1;Bereich2;0) vergleichst und diese als Array-Formel
abschließt, dann sieht das Ergebnis ungefähr so aus:
{#N/A;#N/A;2;#N/A;#N/A;4;#N/A}, wobei die #N/A's darauf hindeuten, dass keine
Übereinstimmung zwischen dem ersten und dem zweiten Bereich festgestellt werden
konnte. Die Werte hingegen weisen darauf hin, dass eine Übereinstimmung gefunden
wurde und in welcher Position.
Die Funktion ANZAHL ignoriert interessanterweise das #N/A und gibt nur die Anzahl der gefundenen nicht #N/A's zurück. Deshalb kann die folgende Formel verwendet werden: {=ANZAHL(VERGLEICH(Bereich1;Bereich2;0))}. Wenn der Rückgabewert 0 (Null) ist, dann wurde keine Übereinstimmung in dem Array gefunden.
Beitrag vom: 04. April 2003
9.) Entscheiden, ob der Inhalt eines Bereiches eindeutig ist
Stell Dir vor, Du hättest ein Tabellenblatt von dem Du wissen möchtest, ob der Inhalt eindeutig ist, also ohne doppelte Einträge (siehe Abbildung).
Die Zellen B2:B8 sind als Bereich definiert. Die ZÄHLENWENN-Funktion wird verwendet, um einen Bereich (Array) an Werten zurückzugeben. Für gewöhnlich, verwendet die Funktion ZÄHLENWENN zwei Argumente: Einen Bereich und ein Suchkriterium. Das Suchkriterium ist ein einfacher Test. In diesem Fall verwenden wir den Bereich selbst als Kriterium. Zu diesem Zweck muss die Funktion als Array beendet werden.
Was tut ZÄHLENWENN(Bereich, Bereich) für uns? Jeder Wert im Bereich wird als Suchkriterium verwendet. Wenn der Bereich B2:B8, welcher das Array {1;3;2;4;3;3;4} zurückgibt, zweimal in ZÄHLENWENN vorkommt, dann ist der erste Test: =ZÄHLENWENN({1;3,2;4;3;3;4},1). Er zählt wie oft die 1 vorkommt. Das ist 1x. Der zweite Test lautet =ZÄHLENWENN({1;3;2;4;3;3;4},3). Er zählt, wie oft die 3 vorkommt. Die Zahl 3 ist der zweite Wert in dem Bereich. Es sind drei 3en vorhanden. Das gleiche gilt für die 5 weiteren Tests: Es gibt eine 2, zwei 4en, drei 3en und zwei 4en. Deshalb =ZÄHLENWENN({1;3;2;4;3;3;4},{1;3;2;4;3;3;4}) ergibt einen Array von {1;3;1;2;3;3;2}.
Wenn dies der Funktion MAX übergeben wird =MAX(ZÄHLENWENN(Bereich1;Bereich1)), wird der höchste Wert in dem Array ermittelt. Das ergibt eine 3. Da dies nicht mit 1 übereinstimmt ist das Ergebnis FALSCH. Das bedeutet, dass in dem Bereich doppelte Werte vorhanden sind. Der Bereich F2:F8, welcher auf der Abbildung zu sehen ist enthält die a-g. Das ZÄHLENWENN(Bereich2;Bereich2) gibt einen Array {1;1;1;1;1;1;1} zurück, denn jeder Eintrag in dem Suchkriterium gibt eine 1 zurück: =ZÄHLENWENN(Bereich2;"a") zählt, wie oft in dem Bereich2 ein Eintrag einem "a" entspricht. Das ist 1x. Das selbe gibt für "b", "c" usw. MAX({1;1;1;1;1;1;1}) ist 1, welches, verglichen mit einer 1 ein WAHR zurückgibt. Das bedeutet, dass keine doppelten Einträge vorhanden sind.
Es gibt da noch eine weitere sehr mächtige Formel, welche ermittelt, wie viele eindeutige Werte (unique) in dem Bereich vorhanden sind. Die Array-Formel lautet {=SUMME(1/ZÄHLENWENN(Bereich;Bereich))}. Nehmen wir mal an, dass in dem Bereich 7 Werte vorhanden sind {11,12,13,11,14,11,12}. Die Funktion würde eine 4 zurückgeben, denn es hat insgesamt 4 eindeutige Werte. Der Teil ZÄHLENWENN der Formel gibt folgenden Array zurück: {3,2,1,3,1,3,2}. Jede dieser Zahlen gibt an, wie oft ein Wert in dem Bereich vorkommt. Der Wert 1 kommt 3x vor. Der Werte 12 zweimal, der Wert 13 einmal. Wenn dieses Array durch den Wert 1 dividiert wird, dann ist das Ergebnis {.33333,.5,1,.33333,1,.33333,.5}. Jeder der .33333 (oder 1/3) entspricht den 3en und es hat drei davon. Die .5 entspricht den 2en und es hat zwei davon usw. Wenn man diese aufaddiert, dann werden sie "zusammengebracht" die 3 Drittel und die 2 Hälften usw. Auf diese Weise wird gezählt, wie viele es davon hat! Das bedeutet, wir haben 3x .33333, 2x .5 und 2x eine 1. Das ergibt total 4 - die Anzahl der eindeutigen Werte. Sie können diese Formel auch in der zuvor behandelten Funktion MAX verwenden.
Beitrag vom: 15. April 2003
Stell Dir vor in der Zelle A1 wäre der Wert 15234 enthalten. Jede der einzelnen Zahlen zusammengezählt ergibt die Quersumme 15 (1+5+2+3+4). Wenn Du jede einzelne Zahl in eine einzelne Zelle stellen könntest, dann wäre es möglich, die Funktion SUMME zu verwenden, um das Resultat zu erhalten. Dies kann mittels einer Array-Formel simuliert werden. Die Array-Formel lautet wie folgt: {=SUMME(1*TEIL(A1;ZEILE(INDIREKT("1:"&LÄNGE(A1)));1))}. Versuchen wir die Formel zu verstehen. Gehen wir davon aus, wir wüssten, dass in der Zelle A1 insgesamt 5 Zahlen enthalten sind. Dann könnte die Formel wie folgt aussehen: {=SUMME(1*TEIL(A1;ZEILE(1:5);1))}. Weiter reduziert würde das so aussehen: =SUMME(1*TEIL(A1;{1;2;3;4;5};1)). Noch weiter reduziert so: =SUMME(1*{"1";"5";"2";"3";"4"}). Die könnte wiederum reduziert werden zu: =SUMME({1;5;2;3;4}). Es würde immer das Ergebnis 15 entstehen.
Excel wird uns allerdings nicht erlauben, die folgende Formel zu verwenden: ZEILE(1:LÄNGE(A1)). Als Lösung kann die Funktion INDIREKT verwendet werden INDIREKT("1:"&LÄNGE(A1)). Dies wird zu INDIREKT("1:5"), was Excel "versteht". Wenn diese Formel an die Funktion ZEILE übergeben wird, erhalten wir das erforderliche Array {1;2;3;4;5}.
Ein anderer Versuch könnte unternommen werden, indem ein Array verwendet wird, das groß genug ist, um alle eingegebenen Zahl abzudecken. Das könnte wie folgt aussehen: {=SUMME(1*TEIL(A1;ZEILE(1:30);1))}. Leider endet dieser Versuch mit der Fehlermeldung #WERT!. Dies begründet sich darin, dass Excel mit TEIL(A1;6;1) einmal einen Nullwert ("") erhält. Wenn dieser mit 1 multipliziert wird, entsteht der Fehler. Summierte Array-Formeln, die Fehler enthalten, enden logischerweise auch als Fehler.
Beitrag vom: 22. August 2004
11.) Nur den numerischen Teil einer Zelle ermitteln
Wenn Du eine Spalte hast, die alphanumerische Daten enthält, wobei ein Teil davon Zahlen sind, möchtest Du vielleicht in der Lage sein, nach den Zahlen zu sortieren. Aber den numerischen Teil zu ermitteln ist nicht ganz einfach, speziell dann nicht, wenn Länge der alphanumerischen Daten variiert. In einer Zelle steht beispielsweise ABC123 und in einer anderen PART03772. Die Teile 123 und 3772 zu finden ist kein einfacher Akt. Wollen wir eine Methode suchen, die das kann. Die erste Aktion besteht darin, herauszufinden, wo sich die erste Zahl befindet. Nehmen wir an, die Zelle A1 enthält ABC123. Du kannst ein Zeichenkette in ein Array umwandeln, indem Du die Array-Formel {=TEIL(A1;ZEILE(1:10);1)} verwendest
Die Funktion ZEILE(1:10) wird verwendet, um das Array portionieren. Die 10 sollte ausreichen, um jeden Buchstaben aufzunehmen (es wird davon ausgegangen, dass die alphanumerische Sektion nicht mehr als 10 Zeichen umfasst). Diese Formel würde folgendes Array ergeben: {“A”;“B”;“C”;“1”;“2”;“3”;“”;“”;“”;“”}. Wir sind allerdings immer noch nicht in der Lage, zwischen Buchstaben und Zahlen zu unterscheiden. Wenn wir das Array allerdings mit 1 multiplizieren würden =1*TEIL(A1;ZEILE(1:10);1), erhielten wir folgenden Inhalt: {#WERT!; #WERT!; #WERT!;1;2;3; #WERT!; #WERT!; #WERT!;#WERT!}.
Beachte, dass die Werte 1, 2 und 3 im Resultat eingebettet sind. Hier kann der Unterschied erkannt werden! Was würde geschehen, wenn wird dieses Array der Funktion ISTFEHLER übergeben? =ISTFEHLER(1*TEIL(A1;ZEILE(1:10);1)) ergibt das Array {WAHR;WAHR;WAHR;FALSCH;FALSCH;FALSCH;WAHR;...}. Hier können wir die erste FALSCH-Bedingung finden (die Position, welche mit der ersten Zahl übereinstimmt), indem wir die Funktion VERGLEICH verwenden {=VERGLEICH(FALSCH;ISTFEHLER(1*TEIL(A1;ZEILE(1:10);1));0)}. Diese Formel gibt den Wert 4 zurück. Nun müssen wir diesen Teil des Teils herausholen. Dazu verwenden wir die Funktion TEIL {=TEIL(A1;VERGLEICH(FALSCH;ISTFEHLER(1*TEIL(A1;ZEILE(1:10);1));0);255)}. Dies jedoch gibt den numerischen Teil als Text. Damit können wir immer noch nicht wunschgemäß sortieren (die 2 würde nach 123 sortiert, weil 2 als Text nach 1 kommt). Um den Text als numerischen Wert zu konvertieren, müssen wir eine Multiplikation mit 1 vornehmen {=1*TEIL(A1;VERGLEICH(FALSCH;ISTFEHLER(1*TEIL(A1;ZEILE(1:10);1));0);255)}.
Wenn Du die Funktion mittels AutoAusfüllen nach unten ziehst, musst Du darauf achten, dass der Bezug ZEILE(1:10) absolut ist: {=1*TEIL(A1;VERGLEICH(FALSCH;ISTFEHLER(1*TEIL(A1;ZEILE($1:$10);1));0);255)}.
Beitrag vom: 22. August 2004
12.) Den nahesten Wert zum Mittelwert finden
Wenn Du die Funktion MITTELWERT verwendest, wird nicht zwangsläufig eine der Zahlen im Bereich das Resultat sein. Es mag jedoch Situationen geben, in denen Du den Wert ermitteln möchtest, der dem Mittelwert am ehesten entspricht. Nehmen wir als Beispiel die nachfolgende Abbildung. Dort ist zu erkennen, dass die Zahl 23 in Zelle B8 der Wert ist, der dem Durchschnitt am nahesten kommt. Der Durchschnitt steht in Zelle B7.
Wenn Du den nahesten Wert zum Mittelwert finden musst, dann bist Du nicht daran interessiert, ob der Wert höher oder tiefer ist als der Durchschnitt. Bei einem Durchschnitt von 21,8 ist die Zahl 22 näher dran als die Zahl 21. Eine Subtraktion dieser Werte vom Durchschnitt ergäbe 0,2 oder den negativen Wert 0,8. Wenn Du nicht an Positiv und Negativ interessiert bist, kannst Du nicht einfach den kleinsten Wert nehmen, sondern musst das Minimum des Wertes der absoluten Differenz ermitteln.
Um die absolute Differenz zu erhalten, nimmst Du beide Werte und subtrahierst diese vom Mittelwert. Diese Kalkulation wird der Funktion ABS übergeben. Bis dahin hast Du also {=ABS(Anzahl-MITTELWERT(Anzahl))} (bei "Anzahl" handelt es sich um den so benannten Bereich B2:B6). Dieser Teil erzeugt das Array {11,8;6,8;1,2;23,2;5,8}. Die Grundlage ist (10-21,8 ergibt -11,8. 15-21,8 ergibt -6,8 usw. da Minuszeichen erscheint, wegen der Verwendung der ABS-Funktion).
Nun haben wir einen Bereich an Differenzen. Wir sind jedoch am kleinsten davon interessiert. Deshalb wird die Formel in die Funktion MIN integriert, was eine Rückgabe von 1,2 zur Folge hat. Nun müssen wir herausfinden wo im ganzen Bereich sich dieser Wert befindet. Deshalb VERGLEICHen wir das MIN(ABS(Anzahl-MITTELWERT(Anzahl))) mit dem Bereich ABS(Anzahl-MITTELWERT(Anzahl)). Der dritte Parameter der Funktion VERGLEICH, Null, erfordert eine genaue Übereinstimmung. Deshalb finden wir die 1,2 an der 3ten Stelle des Arrays. Diese 3 wird an die Funktion INDEX übergeben. Damit gibt Excel den nahesten Wert zurück.
Die endgültige Formel lautet somit {=INDEX(Anzahl;VERGLEICH(MIN(ABS(Anzahl-MITTELWERT(Anzahl)));ABS(Anzahl-MITTELWERT(Anzahl));0))}.
Beitrag vom: 22. August 2004
13.) Bedingungen verwenden, um das letzte Kriterium zu ermitteln
Gehen wir davon aus, Du hast eine Tabelle, die Namen, Daten und Werte enthält. Du möchtest nun eine Summe bilden über alle Beträge, wobei der Name Bob und das Datum <01.05.1998 berücksichtigt werden soll.
Wenn Du verschiedene Variationen mit der Funktion ODER anwendest, wirst Du einen Fehler erhalten, weil diese immer den Wert WAHR zurückgeben. Deshalb wird auch die gesamte ODER-Formel in einem WAHR enden. Das könnte dazu führen, dass alle Werte summiert würden. Wenn Du zum Beispiel die Formel {=SUMME(WENN(ODER(Name="Bob";MONAT(Datum)<5);Betrag))} verwenden würdest (die Bereiche sind entsprechend benannt), würdest Du womöglich einen falschen Wert erhalten. Der Grund liegt darin, dass Name="Bob" das Array {WAHR;FALSCH;FALSCH;WAHR;FALSCH;WAHR;FALSCH;FALSCH}zurückgibt und MONAT(Datum)<5 das Array {FALSCH;WAHR;FALSCH;WAHR;WAHR;WAHR;FALSCH;WAHR} wenn dies zusammen in einer ODER-Funktion verwendet wird, ist das Ergebnis schlicht und einfach WAHR und nicht wie erwartet WAHR und FALSCH. Die Formel würde im Grunde genommen reduziert zu =SUMME(WENN(WAHR;Betrag)) was natürlich nicht zum gewünschten Ergebnis führen würde, denn alle Beträge würden addiert.
Die korrekte Formel wäre {=SUMME(((Name="Bob")+(MONAT(Datum)<5)>0)*Betrag)}.
Sehen wir uns an, wie die Formel funktioniert. Nehmen wir an, die Beträge wären {1;2;3;4;5;6;7;8}. Wenn die beiden WAHR/FALSCH-Bereiche aus dem obigen Abschnitt addiert würden, dann würde das Array wie folgt lauten: {1;1;0;2;1;2;0;1}. Beachte, dass WAHR und WAHR den Wert 2 ergibt. WAHR und FALSCH ergibt 1. FALSCH und FALSCH ergibt 0. Was bedeutet das? Nun, jede 1 entspricht WAHR und FALSCH oder FALSCH und WAHR. Das bedeutet, dass eine der Bedingungen zutrifft (der Name ist "Bob" oder der MONAT(Datum) ist >5). 2 bedeutet, dass beide Bedingungen zutreffen und 0 bedeutet, dass keine der Bedingungen eintritt. Du bist jedoch an den Nicht-Nullen interessiert. Dazu wird das Array verglichen mit Null {1;1;0;2;1;2;0;1}>0. Dies ergibt {WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR;FALSCH;WAHR}. Wenn dies mit dem Betrag multipliziert wird, ergibt das {1;2;0;4;5;6;0;8}. Die Summe davon ist 26. Wenn Du beide Bedingungen ermitteln möchtest, dann verwende: {=SUMME((Name="Bob")*(MONAT(Datum)<5)*Betrag)}.
Beitrag vom: 22. August 2004
14.) Einen Kalender mit einer einfachen Array-Formel erzeugen
Es scheint, als würden die Leute immer wieder in der Lage sein wollen, einen Kalender mittels Excel zu erzeugen. Deshalb: Hier ist ein Weg, dies mit nur einer Formel zu tun. Das ist nicht ganz einfach, aber mit den bisher genutzten Techniken wird es möglich sein. Am besten werden zuerst zwei Namen "week" und "weekday" definiert (Menübefehl "Einfügen/Namen/Definieren"). "week" wird als Array-Konstante definiert: ={0;1;2;3;4;5} und "weekday" als ={1.2.3.4.5.6.7}. Beachte, dass die Zahlen in "week" durch Semikolon getrennt sind. Das bedeutet spaltenorientiert. Die Zahlen in "weekday" sind durch Punkte getrennt, was zeilenorientiertes Array ausmacht.
Es werden drei Zellen als Basis benötigt, um den Kalender zu erzeugen. In die Zelle A1 wird =HEUTE() eingegeben. In Zelle B1 wird =WOCHENTAG(B2) eingetragen. Die Zelle B2 erhält die Formel =DATUM(JAHR(A1);MONAT(A1);1). Die Zelle B2 ist damit der erste Tag des Monats, basierend auf dem Datum. das in Zelle A1 steht. Wenn A1 die Formel =HEUTE() enthält, basiert der Kalender auf dem aktuellen Monat. Wenn Du einen Kalender zu einem anderen Monat erstellen möchtest, gib in Zelle A1 ein entsprechendes Datum ein. Zelle B1 ist der Wochentag von Zelle B2, also die Zahl 1 = Sonntag.
Ein weiterer Name "Calendar" wird definiert. Hier wird die Formel =week*7+weekday+$B$2-$B$1 eingetragen. Selektiere nun ab Zelle A5 6 Zeilen und 7 Spalten (A5:G10) und gib die Formel =Calendar ein (Shift + Strg + Enter = Arrayformel). Der Kalender ist nun vorhanden. Wie funktioniert das alles? Nehmen wir an, wir arbeiten mit dem Februar 1998. Der Teil der Formel =week*7+weekday ist immer derselbe, nämlich die Zahlen 1-42. Wieso? Week*7 ist das spaltenorientierte Array {0;7;14;21;28;35}. Wenn das mit dem zeilenorientierten Array {1.2.3.4.5.6.7} addiert wird, erweitert Excel das zu {1.2.3.4.5.6.7;8.9.10.11.12.13.14;15.16.17.18.19.20.21;22.23.24.25.26.27.28;29.30.31.32.33.34.35;36.37.38.39.40.41.42}. Beachte die Semikolons nach 7; 14 usw. Dies gibt an, dass die nächste Zeile beginnt.
Zu diesem Array wird die Zelle B2 addiert, welche den ersten Tag des Monats enthält. Für Februar 1998 ist das die Serie # 35827. Nun haben wir ein Array. das aus {35828.35829. …. etc.} besteht. Jetzt wird der Wochentag subtrahiert 01.02.1998. Weil der 1.2.1998 ein Sonntag ist, ist der Wochentag 1 und das Array wird zu {35827.35828. …. etc.).
Weil es sich dabei um gültige Datumszahlen sind, brauchen diese nur noch entsprechend in ein Datumsformat ungewandelt zu werden (z.B. mit T - Tage). Da der 1.2.1998 ein Sonntag ist, startet der Kalender direkt mit 1 in der oberen linken Zelle des 6x7 Bereiches. Mit der entsprechenden Ausrichtung und Formatierung sieht das Ganze aus wie ein richtiger Kalender!
Wie auch immer - noch ein paar Bemerkungen. Wenn wir den November 1997 nehmen würden, dann würde die obere linke Zelle mit dem 26. beginnen. Dies, weil das gesamte Array 6x7 mit Serien-Nummern gefüllt ist und der Sonntag in der oberen linken Zelle den 26. Oktober 1997 ergibt. Die gesamte erste Zeile besteht somit aus {26.27.28.29.30.31.1}. Wir möchten jedoch nur den 1. sehen. Ähnlich dazu enthält die letzte Zeile {30.1.2.3.4.5.6}, weil bis zum Dezember 1997 erweitert wird. Hier möchten wir genauso nur bis zum 30. sehen.
Das umzusetzen macht die Formel etwas komplizierter, aber wir erreichen damit das gewünschte Resultat.
{=WENN(TAG(Calendar)>15;WENN(ZEILE()=5;"";Calendar);WENN(TAG(Calendar)<15;WENN(ZEILE()>8;"";Calendar);Calendar))}
Die Formel besagt, dass wenn der Tag des Monats >15 ist, die erste Zeile leer bleiben soll. Sehen wir uns das etwas näher an, bevor wir uns dem Rest der Formel zuwenden. In der ersten Zeile (Zeile 5) haben wir einige Tage des Vormonats gesehen (als wir den Nov. 1997 angeschaut haben). Wenn der Tag über 15 ist (15 ist etwas willkürlich - tatsächlich war es die 26, deshalb könnte 25 auch funktionieren) und die Zeile ist die erste Zeile dann werden die unerwünschten Zahlen nicht angezeigt mit: =WENN(TAG(Calendar)>15;WENN(ZEILE()=5;"";Calendar ...
Ähnlich funktioniert es mit der letzten Zeile(n). Wir möchten den Anfang des nächsten Monats nicht sehen. Dies wird abgefragt durch WENN(TAG(Calendar)<15;WENN(ZEILE()>8;"";Calendar);Calendar)). Wir wählen lieber ZEILE()>8 als ZEILE()>10, weil einige Monate kurz sind, wie z.B. der Feb. 98. Dieser Monat füllt insgesamt nur 4 Zeilen. In diesem Falle möchten wir die Zeilen 5 und 6 ausblenden.
Beitrag vom: 26. August 2004
Thank you
Bob! You have really
a crazy good brain (and heart!) :-)
It was not an easy task to translate all this stuff!
Love,
Monika
Zurück zum Anfang