Excel-Formel: Wert über einem Suchbereich ausgeben?

...komplette Frage anzeigen

4 Antworten

Hallo SunnyBurn!

Möglich wäre es mit einem Summenprodukt, gefolgt von den Formeln Adresse und Indirekt. Beispielsweise so:

Nehmen wir einmal an, du hast 7 Spalten (A bis G) und 10 Zeilen. In der 1. Zeile stehen deine Buchstaben als Spaltenüberschriften. Und nehmen wir zudem an, die Vergleichszelle (also in die du die Zahl einträgst, die du abfragen willst), ist H1.

In Zelle H3 (beispielsweise) schreibst du dann:

  • =SUMMENPRODUKT((A2:G10=H1)*SPALTE(A:G))

Das gibt dir die Nummer der Spalte aus, in welcher dein gesuchter Wert innerhalb der Matrix A2:G10 gefunden wurde.

In Zelle H4 (beispielsweise) schreibst du:

  • =INDIREKT(ADRESSE(1;H3))

"Adresse" gibt dir den Zellennamen der 1. Zeile der gesuchten Spalte zurück (Zeile 1 geben wir vor, weil dort ja deine Überschriften stehen; H3 ist die obig herausgefundene Spalte.) "Indirekt" holt sich den Wert, der in der durch "Adresse" gebildeten Zelle zu finden ist; in deinem Fall also der gesuchte Buchstabe.

Viele Grüße und eine fröhliche Nacht! :-)

Tryphen 17.02.2013, 04:14

Die Funktionen gestalten die Funktion wirklich schön übersichtlich. Gut kombiniert, Galilei!

Nebenbei möchte ich lediglich noch anmerken, dass das natürlich auch in einer einzelnen Zelle realisierbar wäre - aber den Gedanken werdet ihr bestimmt schon gehabt haben ;-)

=INDIREKT(ADRESSE(1;SUMMENPRODUKT((A2:G10=H1)*SPALTE(A:G))))

LG

1
Oubyi 17.02.2013, 11:57
@Tryphen

Die Summenprodukt-Formel von Galilei ist wirklich TOP (DH!)
Auch Tryphens INDIREKT-Idee ist sehr gut (DH!).
Allerdings kann man das imho mit INDEX noch kürzer lösen:

=INDEX(A1:G1;;SUMMENPRODUKT((A2:G10=H1)*SPALTE(A:G)))

1
SunnyBurn 17.02.2013, 21:07
@Oubyi

Vielen Dank. Das hat funktioniert. Ihr wart sehr hilfreich. :)

0

Also für den SVERWEIS brauchst du eine Ergebnistabelle wo steht welcher wert was bedeutet, z.B.

6 A

23 B

98 C

23 D

89 E

12 F

so z.B. (also in 2 Spalten, kann man hier schlecht darstellen)

und dann kann man damit auch nur 1 Feld sozusagen auswerten. du gibst an in welchem Feld gesucht werden soll, z.B. H1, dann die Ergebnistabelle (z.B. $A$6:$B$11) und dann aus welcher Spalte der Ergebnistabelle das Ergebnis genommen werden soll (in meinem Beispiel sind es jetzt halt nur 2 Spalten, da wäre es dann 2)

also wäre die Formel: SVERWEIS(H1;$A$6:$B$11;2)

Da hast du dann aber kein "eingabefeld" sondern es wird sofort ein Ergebnis ausgewertet. Wenn jetzt in Feld H1 z.B. eine 98 stünde würde E als Ergebnis ausgegeben.

Das wird z.B. genutzt ernn du eine Tabellle mit Geschäftszahlen hast kannst du in einer zweiten festlegen das z.b. 100.000 gut 50.000 mittelmäßig und 10.000 schlecht ist und so in der letzen spalte einer Tabelle automatisch anzeigen lassen was gut und schlecht lief.

ich grübel mal ob mir noch eine lösung einfällt ^^ ist scho spät

Um eine lange Formel kommst du bei der Menge an Spalten und Daten leider nicht herum. Allerdings gibt es eine bislang noch nicht erwähnte, sehr hilfreiche Funktion: Zählenwenn

=WENN(ZÄHLENWENN(A2:A5;E2)>0;"A";"")&WENN(ZÄHLENWENN(B2:B5;E2)>0;"B";"")&WENN(ZÄHLENWENN(C2:C5;E2)>0;"C";"")

Wie du die Formel deinen Ansprüchen gemäß erweiterst, schätze ich, dürfte dir klar sein.

Im Anhang findest du übrigens noch ein kleines Vorschaubild von der Funktion im Einsatz innerhalb einer kleinen Demonstrationstabelle.

LG

Excel Funktion Zählenwenn Demo - (Excel, Formel, Tabelle)

Ich hab's!!! Und es funktioniert mit SVERWEIS

Zunächst hast du deine 7 Spalten mit deinen Zahlen (Bei mir war das die Matrix C2 bis I24. Unmittelbar rechts dieser 7 Spalten hast du weitere 7 Spalten mit deinen Buchstaben, jeweils eine Spalte von oben nach unten mit A's (Spalte H), die nächste rechts mit B's, etc. bis zu den G's in Spalte P. Bei mir geht die Matrix also bis Zeile 24.

In Zeile C26 kommt =WENNFEHLER(SVERWEIS($A$26;C2:$P$24;8;FALSCH);"")

In Zeile D26 kommt =WENNFEHLER(SVERWEIS($A$26;D2:$P$24;8;FALSCH);"")

In Zeile E26 kommt =WENNFEHLER(SVERWEIS($A$26;E2:$P$24;8;FALSCH);"")

Und so weiter bis Spalte I. Du musst darauf achten, dass der Sverweis-Befehl in der jeweils verkürzten Matrix abfrägt, da er ja immer in der 1. Spalte nach unten absucht und falls er was findet, den Wert 8 Spalten rechts davon ausspuckt, also den Buchstaben.

Unterhalb deiner ersten Spalte mit den Zahlen (bei mir war das die Spalte C), beginnst du mit deiner Abfrage im Feld A26, wie in der SVERWEIS- Anweisung s.o. angegeben (da gibst du deine gesuchte/gewünschte Zahl ein, variabel).

Sollte dies bei dir jetzt nicht so tun oder meine Erläuterung nicht verständlich sein, dann funk mich an, ich schick dir das File morgen Mittag.

Gute Nacht

Was möchtest Du wissen?