Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.10.2025 19:34:56
Eine Zelle in 3 Spalten teilen
20.10.2025 19:24:08
Excel Form befüllen
20.10.2025 19:03:51
wenn istleer funktioniert nicht wegen formel
20.10.2025 13:56:55
Wenn Funktion mit Xverweis oder ähnlich
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1540to1544
1540to1544
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Excel sverweis mit transponieren

Forumthread: Excel sverweis mit transponieren

Excel sverweis mit transponieren
08.02.2017 12:25:04
gök
Hallo zusammen,
ich habe ein Problem wo ihr mir hoffentlich helfen könnt.
Habe eine Tabelle*
NR. PLZ
A 28201
A 28203
A 28201
B 28205
B 28199
F 28201
F 28259
.....
Sprich in der Spalte NR. tauchen die selben Bezeichnungen öfters auf, denen verschiedene Werte in der Spalte PLZ zugeordnet sind.
In einem weiteren Arbeitsblatt habe ich folgenden Aufbau:
Nr. PLZ1 PLZ2 PLZ3.............
A
B
C
D
E
F
G
H
....
In der Spalte Nr. tauchen die Werte nur noch EINMALL auf!
Jetzt sollen in die Spalten PLZ1, PlZ2 usw die Werte von der ersten Tabelle* und Spalte PLZ zugeordnet werden.
Quasi die Suche in der Spalte wäre ja mit sVerweis möglich, aber da einem Wert mehrere PLZ zugeordnet sind, bringt dieses nichts. Zusätzlich müsste in der Tabelle2 quasi die Werte vom PLZ transponiert werden.
Wenn jemand eine Idee hat, wie ich diese Zuordnung hinbekommen könnte, wäre ich dankbar.
VG
Gökhan
Anzeige

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Excel sverweis mit transponieren
08.02.2017 12:30:19
SF
Hola,
als Matrixformel (mit Strg-Shift-Enter abschließen!):
=WENNFEHLER(INDEX(Tabelle1!$B$2:$B$8;KKLEINSTE(WENN(Tabelle1!$A$2:$A$8=$A2;ZEILE($A$2:$A$8)-1); SPALTE(A1)));"")
Gruß,
steve1da
AW: Excel sverweis mit transponieren
08.02.2017 12:46:52
gök
Leider hat es nicht funktioniert, kriege eine Fehlermeldung dass die Formel falsch ist.

Die Datei https://www.herber.de/bbs/user/111307.xlsx wurde aus Datenschutzgründen gelöscht


Habe mal eine Beispiel Datei hochgeladen, vielleicht hilft dieser um meinen Fehler zu entdecken.
Anzeige
AW: Excel sverweis mit transponieren
08.02.2017 13:15:29
SF
Hola,
du musst die Formel auch schon richtig übernehmen!
Von "=A:A" habe ich nichts geschrieben!
Gruß,
steve1da
AW: Excel sverweis mit transponieren
08.02.2017 13:31:59
gök
Hey,
sorry auch auf die Gefahr dass ich "nerve" bin im Thema Formeln wie du merkst nicht gut drin.
Auch wenn ich die Formel 1:1 lasse, kriege ich eine Fehlermeldung.
VG
Gökhan
Anzeige
AW: Excel sverweis mit transponieren
08.02.2017 13:34:00
SF
Hola,
kann es sein dass du ein Leerzeichen am Ende der Formel mitkopierst?
Ansonsten kann ich nur sagen dass die Formel bei mir klappt.
Gruß,
steve1da
AW: Excel sverweis mit transponieren
08.02.2017 13:50:09
gök
Hey Steve,
ja du hattest Recht mit dem Leerzeichen, aber die Formel funktioniert für 3 Zeilen.
Dachte es liegt an der Eingrenzung weil in der Formel bis Zeile 8 steht, nur wenn ich diesen Wert ändere, dann funktioniert es wieder nicht.
Wenn du vielleicht dir Datei nochmal gucken könntest, verstehst du bestimmt was ich meine.
VG
Gökhan
Anzeige
AW: Excel sverweis mit transponieren
08.02.2017 13:54:56
SF
Hola,
=WENNFEHLER(INDEX(Tabelle1!$B$2:$B$497;KKLEINSTE(WENN(Tabelle1!$A$2:$A$497=$A2;ZEILE($A$2:$A$497)-1); SPALTE(A1)));"")
das funktioniert doch wunderbar.
Gruß,
steve1da
AW: Excel sverweis mit transponieren
08.02.2017 14:45:50
gök
Hey,
ich hatte fast gesagt YES, aber es hat nicht komplett funktioniert leider...
aber sieh bitte
https://www.herber.de/bbs/user/111318.xlsx
VG
Gökhan
Anzeige
AW: Excel sverweis mit transponieren
08.02.2017 14:47:47
SF
Hola,
du musst schon sagen was nicht funktioniert!
So kann ich nur raten: die Bereiche in der Formel gehen bis 497, die Daten in der Tabelle bis 1239.
Gruß,
steve1da
AW: Excel sverweis mit transponieren
08.02.2017 14:51:00
gök
Hey
also Tabelle 2 bis zur Zeile 172 funktioniert die Formel,
danach erschneit kein Ergebnis mehr.
Gökhan
Anzeige
AW: Excel sverweis mit transponieren
08.02.2017 14:52:39
SF
Hola,
nochmal: mach aus den Bereichen bis 497 eine 1239.
Gruß,
steve1da
AW: Excel sverweis mit transponieren
08.02.2017 14:59:30
gök
Hey
habe ich gemacht und die 1. Spalte hat auch funktioniert, nur wenn ich den Bereich auf 1239 verändere, funktionieren die restlichen Spalten nicht.
Aber vielen lieben Dank, muss gucken dass ich den Rest hin bekomme.
VG
Gökhan
Anzeige
AW: Excel sverweis mit transponieren
08.02.2017 17:46:23
SF
Hola,
hast du alle Bereiche geändert und die Formel dann als Matrixformel abgeschlossen?
Was ist mit den anderen Vorschlägen?
Gruß,
steve1da
AW: in Deiner XL-Vers. gibt es kein WENNFEHLER(...
08.02.2017 13:40:42
...
Hallo Gökhan
... dann z.B. als Matrixformel so:
{=WENN(ISTZAHL(KKLEINSTE(WENN(Tabelle1!$A$1:$A$1999=$A2;ZEILE(A$1:A$1999));SPALTE(A1))); INDEX(Tabelle1!$B:$B;KKLEINSTE(WENN(Tabelle1!$A$1:$A$1999=$A2;ZEILE(A$1:A$1999));SPALTE(A1)));"") }
Gruß Werner
.. , - ...
Anzeige
Aber er hat eine xlsx File hochgeladen owT
08.02.2017 13:43:10
SF
AW: dann geht es aber auch einfacher ...
08.02.2017 13:55:34
...
@ Gökhan,
... wenn Du nicht wie von Dir angegeben Version 10 - Office XP sondern möglicherweise doch Excel 2010 hast geht es ganz ohne Matrixformel. Welche Version hast Du also?
Gruß Werner
.. , - ...
AW: dann geht es aber auch einfacher ...
08.02.2017 14:31:44
gök
HEy Werner,
sorry ihr habt Recht, Excel 2010 habe ich.
VG
Gökhan
Anzeige
AW: dann einfach AGGREGAT() ohne INDEX() ...
08.02.2017 14:40:30
...
Hallo Gökhan,
... einfach in C3:
=WENNFEHLER(AGGREGAT(15;6;Tabelle1!$B$3:$B$1500/(Tabelle1!$A$3:$A$1500=$A2);SPALTE(A1));"")
und diese Formel nach rechts und unten kopieren.
Gruß Werner
.. , - ...
AW: dann einfach AGGREGAT() ohne INDEX() ...
08.02.2017 14:41:51
SF
Hola,
=WENNFEHLER(INDEX(Tabelle1!$B:$B;AGGREGAT(15;6;ZEILE($A$1:$A$1999)/(Tabelle1!$A$1:$A$1999=$A2); _
SPALTE(A1)));"")

Gruß,
steve1da
Anzeige
AW: nein, INDEX() ist hier nicht notwendig owT
08.02.2017 14:52:46
...
Gruß Werner
.. , - ...
;
Anzeige
Anzeige

Infobox / Tutorial

Excel Sverweis mit Transponieren

Schritt-für-Schritt-Anleitung

  1. Daten vorbereiten: Stelle sicher, dass Du Deine Daten in zwei Tabellen hast. Tabelle1 sollte die Spalten NR. und PLZ enthalten, während Tabelle2 die Spalten NR., PLZ1, PLZ2, usw. haben sollte.

  2. Formel eingeben: Gehe zu der ersten Zelle in Tabelle2, wo Du die PLZ von Tabelle1 einfügen möchtest. Verwende die folgende Matrixformel:

    =WENNFEHLER(INDEX(Tabelle1!$B$2:$B$497;KKLEINSTE(WENN(Tabelle1!$A$2:$A$497=$A2;ZEILE($A$2:$A$497)-1);SPALTE(A1)));"")

    Achte darauf, die Formel mit Strg + Shift + Enter abzuschließen, um sie als Matrixformel zu speichern.

  3. Formel nach rechts und unten kopieren: Ziehe die Formel nach rechts, um die PLZ1, PLZ2, usw. zu füllen, und dann nach unten, um alle NR. zu berücksichtigen.

  4. Bereiche anpassen: Stelle sicher, dass die Bereiche in der Formel (z.B. $B$497) auch die vollständige Anzahl der Zeilen in Tabelle1 abdecken.

Häufige Fehler und Lösungen

  • Fehlermeldung bei der Formel: Überprüfe, ob Du Leerzeichen oder falsche Zellreferenzen kopiert hast. Das ist einer der häufigsten Fehler.

  • Ergebnisse fehlen nach Zeile 172: Stelle sicher, dass die Bereiche in der Formel die gesamte Datenmenge abdecken. Ändere die Zeilenangaben entsprechend.

  • Formel funktioniert nicht vollständig: Wenn die Formel nur für die ersten Zeilen funktioniert, überprüfe die Anzahl der Zeilen in Deiner Tabelle. Möglicherweise musst Du die Zeilenanzahl in der Formel anpassen.

Alternative Methoden

  • AGGREGAT-Funktion: Eine einfache Möglichkeit, um mehrere PLZ ohne Verwendung von INDEX() zu holen, ist die AGGREGAT-Funktion. Verwende in Zelle C3:

    =WENNFEHLER(AGGREGAT(15;6;Tabelle1!$B$3:$B$1500/(Tabelle1!$A$3:$A$1500=$A2);SPALTE(A1));"")

    Diese Methode ist besonders nützlich, wenn Du Excel 2010 oder eine neuere Version verwendest.

  • Matrix transponieren in Excel: Eine weitere Möglichkeit ist, die Daten in Tabelle1 zuerst zu transponieren, bevor Du die Sverweis-Formel anwendest. Du kannst die Daten kopieren, mit der rechten Maustaste klicken und "Inhalte einfügen" -> "Transponieren" auswählen.

Praktische Beispiele

  • Beispiel für Sverweis: Angenommen, Du hast folgende Daten in Tabelle1:

    NR.    PLZ
A      28201
A      28203
B      28205

    Wenn Du diese Daten in Tabelle2 transponieren möchtest, verwende die oben beschriebenen Schritte.

  • Beispiel für AGGREGAT: Für die gleiche Tabelle kannst Du die AGGREGAT-Funktion verwenden, um alle PLZ für NR. A zu finden:

    =WENNFEHLER(AGGREGAT(15;6;Tabelle1!$B$2:$B$8/(Tabelle1!$A$2:$A$8="A");SPALTE(A1));"")

Tipps für Profis

  • Datenvalidierung nutzen: Verwende Datenvalidierung, um sicherzustellen, dass nur gültige NR. eingegeben werden können, was die Fehleranfälligkeit reduziert.

  • Schnellere Berechnungen: Überlege, ob Du die Daten in einer Pivot-Tabelle zusammenfassen kannst, um die Performance zu verbessern, besonders bei großen Datensätzen.

  • Formelüberprüfung: Nutze die Funktion "Formelüberwachung" in Excel, um zu sehen, wo eine Formel möglicherweise Fehler verursacht.

FAQ: Häufige Fragen

1. Warum funktioniert die Sverweis-Formel nicht richtig?
Die Sverweis-Formel kann nicht mehrere Werte für den gleichen Schlüssel zurückgeben. In solchen Fällen ist eine Matrixformel die bessere Wahl.

2. Wie kann ich die Matrix transponieren?
Um eine Matrix zu transponieren, kopiere die Daten, klicke mit der rechten Maustaste in die Zielzelle und wähle "Inhalte einfügen" und dann "Transponieren".

3. Funktioniert das in Excel 2010?
Ja, die oben genannten Methoden funktionieren in Excel 2010 und neueren Versionen. Stelle sicher, dass Du die richtigen Formeln und Bereiche verwendest.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige