Excel =WENN mit SVERWEIS

6 Antworten

Hallo bettdecken, ich habe Dein Problem so verstanden: Produkte mit einem "x" sollen in Tabelle2 angezeigt werden, die ohne "x" sollen nicht auftauchen, auch keine Leerzeilen. Dafür sehe ich 2 Lösungswege. Möglichkeit 1 | per Pivot: Du erstellst in Tabelle2 eine Pivottabelle über den Inhalt aus Tabelle1, Spalte B kommt in den Berichtsfilter - gefiltert wird auf "x"; Produkt kommt in die Zeilenbeschriftungen. Ergebnis: Alle exakt gleichnamigen Produkte werden zusammengefasst, Produkte ohne "x" verschwinden, evtl. 1 Datensatz mit "(Leer)" aber: die Pivotquelle muss immer neu angepasst werden, wenn die Liste in Tabelle1 länger wird, es sei denn, Du verwendest einen dynamischen Bereichsnamen als Pivotquelle (hier kannst Du mich bei Interesse nochmal ansprechen). Möglichkeit 2 | per Formel: wenn Du dieses Ergebnis per Formel erstellen möchtest, brauchst Du eine Hilfsspalte VOR Spalte A in Tabelle1, d.h. A wird B usw. In die neue Zelle A2 kommt folgende Formel: =WENN(C2="";0;ZÄHLENWENN($C$2:$C2;"x")) In Zelle A2 im 2. Tabellenblatt wird dann die Formel: =WENNFEHLER(SVERWEIS(ZEILE()-1;Tabelle1!$A$2:$B$5;2;0);"") eingetragen (der Teil $B$5 beschreibt das Ende der Liste, dieses muss bei einer längeren Liste angepasst werden oder ebenfalls über einen dynamischen Bereich). Ergebnis: gleichnamige Produkte werden nicht zusammengefasst, Produkte ohne "x" sind nicht erhalten, keine Leerzeilen, aber Formeln müssen in beiden Tabellenblättern runtergezogen werden (man kann auch z.B. 500 Zeilen vorbereiten).

Mit WENN und SVERWEIS wirst Du da nicht weiterkommen! Diese Formel in Tabelle2!A2 und runterkopieren:

{=WENNFEHLER(INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$B$1:$B$10000="x";ZEILE($B$1:$B$10000);9^9);ZEILE(A1)));"")}

ACHTUNG!
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER (alle drei gleichzeitig).
DAS erzeugt die { } und macht die Formel zu einer Matrixformel.

Klappt es?

P.S.:
Das ;9^9 kann man sich sparen.
So reicht es:

{=WENNFEHLER(INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$B$1:$B$10000="x";ZEILE($B$1:$B$10000));ZEILE(A1)));"")}

Oder so wie in augsburgchris`Link (DH!).

0

Excel 2010 SVerweis - Bestellliste?

Hallo zusammen,

Folgendes Szenario:

Ich habe eine Bestellliste.

Eingabe ist eine Bestellnummer hinter der ein spezifischer Preis hinterlegt ist.

Zum Beispiel: 1=2€, 3=4€, 5=6€. Jetzt möchte ich in einer Spalte diese Bestellnummern eingeben und automatisch den zugehörigen Betrag in einer anderen Spalte ausgegeben haben.

Ich habe 2 Tabellen-blätter:

Tabellenblatt1 hat einen Wert in C1 der auf Tabellenblatt2 verweist.

Tabellenblatt2 hat in Spalte A die Bestellnummer und in Spalte B der Preis.

=WENN(ISTFEHLER(SVERWEIS(Bestellung!C2;Speisekarte!A:B;"2"));"";SVERWEIS(Bestellung!C2;Speisekarte!A:B;"2"))

Diese Formel sollte mir egtl. die Werte passend ausgeben, aber das klappt nur teilweise, da Excel hingeht und anstelle der Bestellnummer "A9" zum Beispiel die "A2" nimmt. Oder die Bestellnummer "C9" wird zur Bestellnummer "Text"

Kann mir hier einer weiterhelfen?

...zur Frage

Excel Sverweis richtig?

Ich habe eine Tabelle mit einer Kundennummer und der passenden Bestellnummer.
In einer weiteren Tabelle/Spalte habe ich nur die Bestellnummer, aber in einer anderen Reihenfolge. Diese ist aber richtig und wichtig. Ich möchte nun also die Kundennummern der richtig sortierten Bestellnummer zuweisen.

Löse ich das mit dem Sverweis? Leider ist es möglich, dass in der Tabelle Bestellnummer fehlen oder zu viel sind.

...zur Frage

Fortlaufendes Suchkriterium bei SVERWEIS in Excel?

Hallo,

ich habe folgendes Anliegen:

Ich habe eine Excel Tabelle, die sich die Werte aus einer zweiten Arbeitsmappe mithilfe von SVERWEIS bezieht. Das Suchkriterium in der Tabelle sind fortlaufende Zahlen (1001,1002,1003,...) und sind eindeutig in der Wertetabelle vergeben. Wenn ich nun den SVERWEIS in der Tabelle in die gewünschte Zelle schreibe, sieht dieser bspw. so aus: =SVERWEIS("1001";'Preisliste'!$A:$T;Spalte(A1);FALSCH). Der Befehl an sich macht was er soll und funktioniert einwandfrei.

Nun meine Frage: Ist es möglich, dass sich das Suchkriterium beim nach unten ziehen der Zellen immer um +1 verändert und nicht kopiert wird? Ich weiß, ich könnte zwar die Zellenbezeichnung als Suchkriterium vergeben (A1,A2) aber dann wäre keine Sortierung der Tabelle mehr möglich.

...zur Frage

Wie bekomme ich einen SVERWEIS tabellenübergreifend hin?

Moin,

ich möchte in einem Tabellenblatt einen Sverweis verwenden und die Daten dafür befinden sich in einem anderen Tabellenblatt namens Liste.

Verwenden wollte ich =SVERWEIS(C19;A:C;2;FALSCH)

Da sich Spalte A bis C im anderen Tabellenblatt befinden, schrieb ich noch Liste! vor die Buchstaben, was aber nicht funktioniert hat. Bei Wenn Funktionen ging das allerdings über Name! wenn es tabellenübergreifend sein soll.

Wie bekomme ich das geregelt?

Danke.

...zur Frage

Wie kann ich in Excel überprüfen, ob zwei bestimmte Werte in zwei verketteten Spalten im aktuellen Tabellenblatt vorkommen?

Moin,

wenn in Spalte A und B = Beispiel1 + Beispiel1 oder Beispiel2 + Beispiel2 oder Beispiel3 + Beispiel3 steht, dann soll irgendwas angezeigt werden. Bestenfalls direkt ein bestimmtes Wort, alternativ eine 0 für falsch und 1 für wahr.

Spalte A = Nachname

Spalte B = Vorname

Ich möchte nämlich, dass bei bestimmten Personen etwas steht.

Wäre ja mit einer Wenn-Funktion gut machbar, aber da kann ich nur eine Spalte überprüfen.

Danke.

...zur Frage

Excel nächste freie Zelle in Spalte ermitteln?

Hallo,

wie kann ich in Excel aus Tabellenblatt 1 bestimmte Daten aus der neu beschriebenen Zeile (Zeile n, Spalte B, C, E F) in Tabellenblatt 2 transferieren und dort in die nächsten freien Spalten einfügen (Spalte n, A, B, C, D)?

...zur Frage

Was möchtest Du wissen?