Kann mir bitte jemand auf die Sprünge helfen?
Ich möchte folgendes in Excel realisieren:
Es existiert eine Matrix mit 5 Spalten und 20 Zeilen
In einem separaten Feld wird ein Wert eingegeben. Jetzt brauche ich eine Formel, die diesen Wert in der Matrix sucht und mir die Überschrift (1. Zeile) derjenigen Spalte zurückgibt, in welcher der Wert gefunden wird.
Ich habe schon mit INDEX und VERGLEICH herumprobiert, aber komme nicht auf die Lösung.
Ich bin für jeden Tip dankbar!
lg
Schandor
Es existiert eine Matrix mit 5 Spalten und 20 Zeilen
In einem separaten Feld wird ein Wert eingegeben. Jetzt
brauche ich eine Formel, die diesen Wert in der Matrix sucht
und mir die Überschrift (1. Zeile) derjenigen Spalte
zurückgibt, in welcher der Wert gefunden wird.
Hallo,
versuche es mal so:
=INDEX(A1:E1;SUMMENPRODUKT((A1:E20=I1)*(SPALTE(A:E))))
Suchbegriff in I1 sonnst bitte anpassen
Gruß Holger
Großartig!
Vielen Dank, Holger!
Schandor
Hallo Holger,
die Formel funktioniert, aber leider nur bedingt:
Die Matrix enthält nämlich leere Zellen (ich weiß schon: SUMMENPRODUKT geht dann von 0 aus), und zwar so:
Die erste Spalte enthält nur 5 Werte
Die zweite Spalte enthält 10 Werte
Die dritte Spalte enthält 15 Werte
Die vierte Spalte enthält 12 Werte
Die fünfte Spalte enthält 20 Werte
SUMMENPRODUKT gibt allerdings nicht bei allen Werten die korrekte Spaltennummer zurück !? Meistens funktioniert es, aber manchmal werden Spaltennummern zurückgegeben, die weit aus dem möglichen Bereich zu liegen kommen! Was mach ich bloß falsch? Denn ich hab die Formel genauso eingegeben, wie Du es vorgeschlagen hast! (Hab die Index-Angabe mal entfernt, um dem Fehler auf die Spur zu kommen )
Liebe Grüße,
Schandor
Ich muss das noch einmal detailliert präzisieren:
Ich habe eine Matrix von N15:R49.
In N15:R15 stehen meine Überschriften.
Die Spalten N bis R enthalten unterschiedlich viele Werte (will heißen: manche Zeilen sind leer).
Gebe ich allein die Formel =SUMMENPRODUKT(N15:R49=E34)*(SPALTE(N:R)) ein, bekomme ich als Ergebnis immer 0.
In E34 steht ein veränderlicher Wert (wird manuell eingegeben).
Wenn schon *diese* Formel nichts ergibt, dann auch nicht die mit der INDEX-Funktion erweiterte.
Was könnte ich falsch machen?
Danke
Ich habe eine Matrix von N15:R49.
In N15:R15 stehen meine Überschriften.
Die Spalten N bis R enthalten unterschiedlich viele Werte
(will heißen: manche Zeilen sind leer).
Gebe ich allein die Formel
=SUMMENPRODUKT(N15:R49=E34)*(SPALTE(N:R)) ein, bekomme ich als
Ergebnis immer 0.
In E34 steht ein veränderlicher Wert (wird manuell
eingegeben).
Hallo Schandor,
aus excelformeln.de angepasst, gibt falsche Ergebnisse wenn Suchkriterium mehrfach vorkommt:
Tabellenblatt: [Mappe1]!Tabelle1
│ N │ O │ P │ Q │ R │ S │ T │
───┼────────┼────────┼────────┼────────┼────────┼───┼────────┤
15 │ Titel1 │ Titel2 │ Titel3 │ Titel4 │ Titel5 │ 1 │ Titel1 │
───┼────────┼────────┼────────┼────────┼────────┼───┼────────┤
16 │ 1 │ │ │ │ │ 2 │ Titel2 │
───┼────────┼────────┼────────┼────────┼────────┼───┼────────┤
17 │ │ 2 │ │ │ │ 3 │ Titel3 │
───┼────────┼────────┼────────┼────────┼────────┼───┼────────┤
18 │ │ │ │ 4 │ 7 │ 4 │ Titel4 │
───┼────────┼────────┼────────┼────────┼────────┼───┼────────┤
19 │ │ │ 3 │ │ │ 5 │ Titel1 │
───┼────────┼────────┼────────┼────────┼────────┼───┼────────┤
20 │ 5 │ │ │ │ 6 │ 6 │ Titel5 │
───┼────────┼────────┼────────┼────────┼────────┼───┼────────┤
21 │ │ │ │ │ │ 7 │ Titel5 │
───┴────────┴────────┴────────┴────────┴────────┴───┴────────┘
Benutzte Formeln:
T15: =INDEX($N$15:blush:R$15;SUMMENPRODUKT(($N$15:blush:R$49=S15)\*(SPALTE(A:E))))
T16: =INDEX($N$15:blush:R$15;SUMMENPRODUKT(($N$15:blush:R$49=S16)\*(SPALTE(A:E))))
T17: =INDEX($N$15:blush:R$15;SUMMENPRODUKT(($N$15:blush:R$49=S17)\*(SPALTE(A:E))))
T18: =INDEX($N$15:blush:R$15;SUMMENPRODUKT(($N$15:blush:R$49=S18)\*(SPALTE(A:E))))
T19: =INDEX($N$15:blush:R$15;SUMMENPRODUKT(($N$15:blush:R$49=S19)\*(SPALTE(A:E))))
T20: =INDEX($N$15:blush:R$15;SUMMENPRODUKT(($N$15:blush:R$49=S20)\*(SPALTE(A:E))))
T21: =INDEX($N$15:blush:R$15;SUMMENPRODUKT(($N$15:blush:R$49=S21)\*(SPALTE(A:E))))
N15:T21
haben das Zahlenformat: Standard
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Gruß
Reinhard
Reinhard, herzlichen Dank – das war tatsächlich das Problem!
lg
Schandor
eine kleine Frage noch am Rande:
Mit welcher Formelkombination löst man eigentlich das Problem, dass SVERWEIS nur exakte Werte (falsch) oder kleinere Werte (wahr) liefert? Ich bräuchte nämlich aus einer Tabelle den GRÖSSEREN Wert (steht eine Zeile unterhalb des Ergebnisses, wie SVERWEIS es zurückliefert)?
lg
Schandor
Mit welcher Formelkombination löst man eigentlich das Problem,
dass SVERWEIS nur exakte Werte (falsch) oder kleinere Werte
(wahr) liefert? Ich bräuchte nämlich aus einer Tabelle den
GRÖSSEREN Wert (steht eine Zeile unterhalb des Ergebnisses,
wie SVERWEIS es zurückliefert)?lg
Schandor
Hallo,
deine Frage ist nicht so eindeutig, das man wirklich drauf antworten kann.
Für [wahr] muss die Spalte mit dem Suchkriterium sortiert sein sonnst gibt es unweigerlich falsche Ergebnisse.
Wenn du als Suchkriterium den größten Wert haben möchtest setzte dort zusätzlich Max() oder kgrösste() ein.
Hier mal eine Seite die ich empfehlen kann.
Dort findest du auch die Formel zur Zeilenüberschrift wieder.
!! beachte oben den Hinweis zur {Matrixformeln} !!
http://www.excelformeln.de/formeln.html
Gruß Holger
Hallo Holger,
Danke zunächst nochmal herzlich für Deine Antwort und Dein Mitdenken.
Ich präzisiere es:
Ich habe eine Matrix (6 Spalten, 40 Zeilen)
Bei den Werten kann es Duplikate geben.
Mit einer SVERWEIS-Formel kann ich zwar die richtige Spalte ermitteln, in der der gesuchte Wert steht, nicht aber die richtige Zeile, da mir SVERWEIS nur gestattet, einen EXAKTEN Wert ODER den niedrigeren Wert (mit =WAHR) zu ermitteln, NICHT aber den nächsthöheren, der gesucht ist!!!
Daher muss ich den Bezug ermitteln. Das tue ich mit
=ADRESSE(MIN(WENN(Matrix=Wert;ZEILE(Matrix)));MIN(WENN(Matrix=Wert;SPALTE(Matrix)))).
Diese Formel liefert mir immer dann das richtige Ergebnis, wenn keine Duplikate vorkommen.
Ich schaffe es einfach nicht, mit SVERWEIS den gewünschten Wert zu ermitteln, und ich google nun schon seit Tagen!!
Die Matrix enthält sagen wir in der 5. Spalte einen Wert für (bis 180). Der Wert darunter ist (bis 200). Suche ich nun nach 190, so gibt mir SVERWEIS entweder #NV zurück (wenn ich mit =falsch spezifiziere) oder 190. Ich bräuchte aber in diesem Fall den Wert 200!
Ich kann einfach nicht glauben, dass so etwas mit Excel nicht möglich sein sollte. Ich finde im Netz zwar jede Menge Antworten, wie man innerhalb einer einzigen (!) Spalte den nächsthöheren Wert ermittelt, aber das hilft mir nicht weiter, da ich zum Zeitpunkt der Prüfung die Spalte ja nicht kenne.
Ich bräuchte eine Formel, die mir den NÄCHSTHÖHEREN Wert in einer *mehrspaltigen* Matrix findet, falls keine genaue Entsprechung existiert – DAS ist die Definition!
Ich wär Dir wirklich für jeden Hinweis dankbar; mittlerweile verzweifle ich daran schon, denn das Fehlen dieser Formel verursacht mir einen Mehraufwand mit dem Faktor 10…
Lieben Gruß
Schandor
Ich bräuchte eine Formel, die mir den NÄCHSTHÖHEREN Wert in
einer *mehrspaltigen* Matrix findet, falls keine genaue
Entsprechung existiert – DAS ist die Definition!
hallo,
hier ist die Definition etwas anders beschreiben, aber ich denke es ist das was du möchtest:
http://www.excelformeln.de/formeln.html?welcher=38
Beachte, das die Tabelle nicht sortiert sein muss, so das anders wie bei SVerweis weiter gesucht wird und tatsächlich immer der Wert der dem Suchkriterium am nächsten kommt gesucht wird.
={beachte wie bereits erwähnt, den Hinweis zur Matrix-Formel!!!}
Gruß Holger
Nachtrag
Wenn es das nicht ist,
erstelle mal eine kleine Beispiel-Tabelle,
http://www.file-upload.net/
hier hoch landen und verlinkten,
wäre nett und einfacher.
LG Holger
Wenn es das nicht ist,
erstelle mal eine kleine Beispiel-Tabelle,
http://www.file-upload.net/
hier hoch landen und verlinkten,
wäre nett und einfacher.
Hallo Holger,
@Schador, ich kann Holger da nur zustimmen.
Erstelle die Beispielmappe mit deinen 5 Spalten und 20-40 zeilen.
Dann in Spalte 6 schreibst du untereinander mehrere Suchwerte.
In Spalte 7 dann manuell was bei der Suche raus kommen soll als Ergebnis bezogen auf den Suchbegriff inSpalte 6.
Natürlich auch nach etwas suchen was es gar nicht im Suchbereich gibt.
Eine wichtige Info dazu ist auch ob die Spalten in sich immer aufsteigend sortiert sind.
Ggfs gib noch Hinweise was geschehen soll wenn ein Suchbegriff mehrmals auftaucht oder die Info daß dies nie geschen kann.
Gruß
Reinhard