Hallo Forumteilnehmer,
ich habe eine Problem mit einer Vergleichformel, die mir als Ergebnis #WERT! liefert.
Basis bildet eine Matrix, in der ich anhand mehrerer Suchkriterien in Zeilen
und Spalten den richtigen Eintrag suche. Die richtige Zeilennummer wird ermittelt; der Fehler entsteht bei der Ermittlung der Spaltennummer.
Verweis 1 | S | T | U | V | W | X | Y | Z |
| 1 | | | | | Suchkriterien2 |
| 2 | | | | | C | B | C | D |
| 3 | | | | | 3 | 2 | 3 | 4 |
| 4 | Suchkriterien1 | | | | | |
| 5 | a | y | z | | 12 | 100 | 1.597 | 12.457 |
| 6 | c | c | b | | 35 | 124 | 3.654 | 15.478 |
| 7 | x | y | z | | 50 | 885 | 9.547 | 96.585 |
| 8 | a | d | p | | 10 | 965 | 8.521 | 35.428 |
| 9 | x | y | z | | 45 | 986 | 9.658 | 12.322 |
| 10 | a | b | o | | 66 | 157 | 4.214 | 35.875 |
| 11 | | | | | | | | |
| 12 | Sverweis mit mehreren Suchkriterien ohne doppelte Einträge | | | | | | | |
| 13 | | | | | | | | |
| 14 | Suchkriterien1 | | Ergebnis | | | |
| 15 | x | y | z | | #WERT! | | | |
| 16 | Suchkriterien2 | | | | |
| 17 | C | 3 | | | | | |
| Formeln der Tabelle |
| Zelle | Formel | | W15 | =INDEX(W5:Z10;VERGLEICH(1;MMULT((S5:S10=S15)*(T5:T10=T15)*(U5:U10=U15);1);0);VERGLEICH(1;MMULT((W2:Z2=S17)*(W3:Z3=T17);1);0)) |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Joe
Hallo
Du hast ja mehr als einen Treffer.
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| W | X | Y | Z |
| 14 | Ergebnis | | | |
| 15 | | | | |
| 16 | | Spalte | Zeile | |
| 17 | 23,007 | 1 | 3 | 50 |
| 18 | 23,009 | 1 | 5 | 45 |
| 19 | 25,007 | 3 | 3 | 9547 |
| 20 | 25,009 | 3 | 5 | 9658 |
| 21 | #ZAHL! | #ZAHL! | #ZAHL! | #ZAHL! |
| 22 | #ZAHL! | #ZAHL! | #ZAHL! | #ZAHL! |
| Zelle | Formel |
| W17 | =AGGREGAT(15;6;SPALTE($W$5:$Z$10)/(W$2:$Z$2=$S$17)/($W$3:$Z$3=$T$17)+ZEILE($W$5:$Z$10)/($S$5:$S$10=$S$15)/($T$5:$T$10=$T$15)/($U$5:$U$10=$U$15)/10^3;ZEILEN(W$17:W17)) |
| X17 | =QUOTIENT(W17;1)-22 |
| Y17 | =REST(W17;1)*10^3-4 |
| Z17 | =INDEX($W$5:$Z$10;Y17;X17) |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hallo,
mir ist auch unklar was Du hier mit:
"...ohne doppelte Einträge" meinst
.
Wenn immer nur der oberste und am weitest links gelegene Treffer ausgewiesen werden soll,
dann folgende Formel:
Code:
=WENNFEHLER(INDEX(A:Z;AGGREGAT(15;6;ZEILE(W5:W13)/(S5:S13=S15)/(T5:T13=T15)/(U5:U13=U15);1);
AGGREGAT(15;6;SPALTE(W1:Z1)/(W2:Z2=S17)/(W3:Z3=T17);1));"")
Anderenfalls, um alle relevanten Treffer zu listen, wäre mein Vorschlag, nachfolgende Formel nach unten und rechts kopieren.
| S | T | U | V | W | X | Y | Z |
| 1 | | | | | Suchkriterien2 |
| 2 | | | | | C | B | C | D |
| 3 | | | | | 3 | 2 | 3 | 4 |
| 4 | Suchkriterien1 | | | | | |
| 5 | a | y | z | | 12 | 100 | 1.597 | 12.457 |
| 6 | c | c | b | | 35 | 124 | 3.654 | 15.478 |
| 7 | x | y | z | | 50 | 885 | 9.547 | 96.585 |
| 8 | a | d | p | | 10 | 965 | 8.521 | 35.428 |
| 9 | x | y | z | | 45 | 986 | 9.658 | 12.322 |
| 10 | a | b | o | | 66 | 157 | 4.214 | 35.875 |
| 11 | | | | | | | | |
| 12 | | | | | | |
| 13 | | | | | | | | |
| 14 | Suchkriterien1 | | Ergebnis |
| 15 | x | y | z | | 50 | | 9547 | |
| 16 | Suchkriterien2 | | 45 | | 9658 | |
| 17 | C | 3 | | | | | | |
| 18 | | | | | | | | |
| Formeln der Tabelle |
| Zelle | Formel | | W15 | =WENNFEHLER(INDEX(W:W;AGGREGAT(15;6;ZEILE(W$5:W$10)/($S$5:$S$10=$S$15)/($T$5:$T$10=$T$15)/($U$5:$U$10=$U$15)/(W$2=$S$17)/(W$3=$T$17);ZEILE(A1)));"") |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Hallo,
das liegt an der Zeile. Du musst diese innerhalb von MMULT mittels MTRANS in eine Spalte "umwandeln", dann funktioniert auch die von Dir gepostete Formel ohne Fehler:
Code:
=INDEX(W5:Z10;VERGLEICH(1;MMULT((S5:S10=S15)*(T5:T10=T15)*(U5:U10=U15);1);0);VERGLEICH(1;MMULT(MTRANS((W2:Z2=S17)*(W3:Z3=T17));1);0))
Hallo Michael,
der Einsatz von MTRANS() erfordert zwingend den Abschluss der Formel als Matrixformel, was aber nicht notwendig ist, weil auch bei Anwendung von MMULT() hier kein MTRANS() notwendig ist.
Man muss dazu lediglich die Argumente im entsprechenden MMULT()-Formelteil vertauschen.
Also so:
Code:
=INDEX(W5:Z10;VERGLEICH(1;MMULT((S5:S10=S15)*(T5:T10=T15)*(U5:U10=U15);1);0);VERGLEICH(1;MMULT(1;(W2:Z2=S17)*(W3:Z3=T17));0))
@ Joe,
(sorry, hab erst heute mitbekomme, dass Du der Fragesteller bist)
Wie Du weißt, hab ich ein Faible für AGGREGAT(), welche ich oft einsetze, wenn man mehrere Ergebnisse ohne klassische Matrixformel ausgegeben muss. Deshalb hatte ich aus dieser mein Formelvorschlag auch für nur ein Ergebnis aus der "mehre Ergebniswerte" abgeleitet.
Der Einsatz von MMULT() ist natürlich hier günstiger.
Hallo,
die Unterstützung bei den allen Formeln passt. Herzlichen Dank.
Joe
P.S. Hallo Werner, schön von Dir zu hören.