Dynamische Matrix im sverweis erstellen

Forumthread: Dynamische Matrix im sverweis erstellen

02.03.2017 12:07:41

Sven

Hallo Zusammen,
gibt es eine Möglichkeit die Matrix innerhalb eines SVERWEIS dynamisch zu ersellen?
SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
Ich rechne aus einer Liste ,bei der die Matrix sich monatlich um 50 Zeilen verschiebt.
Vorab vielen Dank

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Dynamische Matrix im sverweis erstellen

02.03.2017 13:03:57

Sven
AW: Dynamische Matrix im sverweis erstellen

02.03.2017 13:40:09

ChrisL
Index & Vergleich

02.03.2017 15:39:49

Michael
AW: Index & Vergleich

02.03.2017 18:27:34

ChrisL
@chrisL

03.03.2017 11:00:13

Michael

AW: Dynamische Matrix im sverweis erstellen

02.03.2017 13:03:57

Sven

Hier ist ein Beispiel
=SVERWEIS($15;'Tabelle1'!$A$5:$AL$50;10;FALSCH)
Der Matrixbereich (hier im Beispiel 5 und 50 ) verändert sich monatlich, deswegen möchte ich den Matrixbereich dynamisch halten
Nächsten Monat muss die Funktion so aussehen:
=SVERWEIS($15;'Tabelle1'!$A$51:$AL$100;10;FALSCH)
Usw.
Am liebsten hätte ich, wenn ich über eine separate Tabelle diesen Bereich festlegen kann. Daraufhin soll sich die Funktion anhand meine Eingaben anpassen.
z.B
Monat -- Matrixbereich von -- Matrixbereich bis
Januar -- 5 -- 50
Februar -- 51 -- 100

AW: Dynamische Matrix im sverweis erstellen

02.03.2017 13:40:09

ChrisL

Hi Sven
z.B. mit Indirekt()
=SVERWEIS($15; INDIREKT("'Tabelle1'!$A$5:$AL$" & Tabelle2!A1) ;10;FALSCH)
Nachteil: Indirekt ist eine volatile Formel
http://www.online-excel.de/excel/singsel.php?f=171
Vermutlich könnte man es noch besser lösen, wenn man eine konkrete Beispieldatei hätte. Ist der Monat gekennzeichnet? Sind es je Monat immer gleich viele Zeilen die hinzukommen?
cu
Chris

Index & Vergleich

02.03.2017 15:39:49

Michael

Hi,
das Indirekt läßt sich durch Index/Vergleich ersetzen.
Anbei Testdatei mit zwei Formelvarianten: https://www.herber.de/bbs/user/111900.xlsx
Die "eigentliche" Formel in Formel!C10 lautet:
=INDEX(Tabelle1!J:J;J10-1+VERGLEICH($B$13;INDEX(Tabelle1!A:A;J10):INDEX(Tabelle1!A:A;K10);0))
wobei J10 und K10 die Zellen aus Deinem skizzierten "Matrix-Bereich" sind.
Letzteren habe ich mit einem extra Index ausgestattet, weil sich die Formeln dann übersichtlicher schreiben lassen.
SVerweis durchsucht ja nur eine Spalte (A:A) und gibt in Deiner Formel das Ergebnis aus Spalte 10, nämlich J:J zurück: mit Index braucht man auch nur diese beiden Spalten in der Formel anzugeben.
Schöne Grüße,
Michael

AW: Index & Vergleich

02.03.2017 18:27:34

ChrisL

Hi Michael
Danke. An Index habe ich zwar gedacht, allerdings beschränkte sich mein Horizont auf eine Zelle. Dass man damit einen Bereich bilden kann, war mir nicht bekannt.
INDEX(Tabelle1!A:A;J10):INDEX(Tabelle1!A:A;K10)
In dem Fall ginge auch...
=SVERWEIS(C6;INDEX(Tabelle1!A:A;J10):INDEX(Tabelle1!J:J;K10);10;0)
cu
Chris

@chrisL

03.03.2017 11:00:13

Michael

Hi Chris,
das hatte ich irgendwo bei excelformeln aufgeschnappt...
"In dem Fall" schreibt sich der SVerweis sicher griffiger - danke auch.
Na denn, gute Zeit & Gruß,
Michael

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um eine dynamische Matrix im SVERWEIS zu erstellen, folgen diese Schritte:

Tabellenblatt vorbereiten: Erstelle ein Tabellenblatt, in dem du deine Matrixbereiche definierst. Zum Beispiel:

Monat   -- Matrixbereich von   -- Matrixbereich bis
Januar  -- 5                  -- 50
Februar -- 51                 -- 100

Formel verwenden: Verwende die SVERWEIS-Funktion in folgender Form:
```
=SVERWEIS(Suchkriterium; INDIREKT("'Tabelle1'!$A$" & Tabelle2!A1 & ":$AL$" & Tabelle2!B1); Spaltenindex; FALSCH)
```
Hierbei ersetzt du Tabelle2!A1 und Tabelle2!B1 durch die Zellen, die deinen Matrixbereich definieren.
Dynamischen Spaltenindex festlegen: Wenn du den Spaltenindex dynamisch festlegen möchtest, kannst du eine separate Zelle für den Spaltenindex nutzen, und dann in der SVERWEIS-Formel darauf verweisen.

Häufige Fehler und Lösungen

Fehler: #NV-Fehler tritt auf.
- Lösung: Stelle sicher, dass das Suchkriterium in der ersten Spalte deiner Matrix vorhanden ist.
Fehler: Die Formel gibt nicht die erwarteten Werte zurück.
- Lösung: Überprüfe die Matrixbereiche und stelle sicher, dass sie korrekt in der Formel angegeben sind.
Fehler: Indirekt() führt zu einer langsamen Berechnung.
- Lösung: Indirekt() ist eine volatile Funktion. Überlege, alternative Methoden zu verwenden, wie INDEX und VERGLEICH.

Alternative Methoden

Falls du die dynamische Matrix nicht mit SVERWEIS umsetzen kannst, gibt es Alternativen:

INDEX und VERGLEICH:
```
=INDEX(Tabelle1!J:J; VERGLEICH($B$13; INDEX(Tabelle1!A:A; J10):INDEX(Tabelle1!A:A; K10); 0))
```
Diese Methode ermöglicht es, einen Bereich dynamisch zu definieren, ohne die Nachteile von Indirekt().
Datentabellen: Wenn du regelmäßig Daten hinzufügst, kannst du auch Excel-Tabellen (Strukturierte Tabellen) verwenden, um die Matrix automatisch zu erweitern.

Praktische Beispiele

Hier sind einige praktische Beispiele für die Anwendung:

Einfacher SVERWEIS:

=SVERWEIS(A1; 'Tabelle1'!$A$5:$AL$50; 10; FALSCH)

Dynamischer SVERWEIS mit Indirekt:

=SVERWEIS(A1; INDIREKT("'Tabelle1'!$A$5:$AL$" & Tabelle2!A1); 10; FALSCH)

Verwendung von INDEX und VERGLEICH:

=INDEX(Tabelle1!J:J; VERGLEICH(A1; INDEX(Tabelle1!A:A; J10):INDEX(Tabelle1!A:A; K10); 0))

Diese Beispiele zeigen, wie man eine dynamische Matrix in Excel implementieren kann.

Tipps für Profis

Matrix benennen: Du kannst die Matrix als benannten Bereich definieren, um sie in Formeln einfacher zu verwenden.
Bereich dynamisch festlegen: Nutze die Funktion OFFSET, um dynamisch Bereiche zu erstellen.
Verknüpfungen zu anderen Tabellenblättern: Du kannst den SVERWEIS auch auf andere Tabellenblätter anwenden, indem du den Blattsnamen in die Formel einfügst.

FAQ: Häufige Fragen

1. Wie kann ich die Matrix in einer anderen Arbeitsmappe referenzieren?
Du kannst SVERWEIS mit dem vollständigen Pfad der anderen Arbeitsmappe verwenden, z.B.:

=SVERWEIS(A1; '[AndereDatei.xlsx]Tabelle1'!$A$5:$AL$50; 10; FALSCH)

2. Kann ich mehrere Bedingungen im SVERWEIS berücksichtigen?
Nein, SVERWEIS unterstützt nur eine Bedingung. Du kannst jedoch INDEX und VERGLEICH für komplexere Bedingungen verwenden.

Beliebteste Forumthreads (12 Monate)

Dynamische Matrix im sverweis erstellen

Forumthread: Dynamische Matrix im sverweis erstellen

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Dynamische Matrix im SVERWEIS erstellen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread