Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
936to940
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
936to940
936to940
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Formel INDEX und VERGLEICH

Formel INDEX und VERGLEICH
29.12.2007 19:29:10
zellner
Hallo,
ich versuche mich seit ein paar Tagen an folgendem Problem(Beispiel siehe Link ganz unten), vielleicht könnt ihr mir dabei helfen:
In einer Tabelle möchte ich abhängig von verschieden Steuerschlüsseln einen bestimmten Wert auslesen und verwende die Funktionen INDEX und VERGLEICH.
Um die richtige Zeile zu finden habe ich den entsprechenden Steuerschlüssel "Key_2b". Soweit ok.
Um aber die richtige Spalte "Satz_2b" auszuwählen kann ich nicht einfach die oberste Zeile nehmen, da die Bereiche voneinander getrennt sind und jeder Bereich seine eigene "Überschrift" hat.
Wie kann ich also eine gesamte Tabelle nutzen und den Spaltenverweis bei INDEX variabel steuern? Kann man dafür den Key_0x nehmen, um erst mal einen Bezug zu der Zeile zu machen (SVERWEIS?)?
Ich versuche es mit "try & error", was natürlich viel Zeit kostet. Wenn mir hier jemand eine Lösung möglichst ohne VBA zeigen kann wäre ich echt dankbar.
Danke vorab und guten Rutsch
R. Zellner
https://www.herber.de/bbs/user/48700.xls

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel INDEX und VERGLEICH
29.12.2007 20:09:00
Beverly
Hi,
was ist denn das Problem mit deiner Formel? Sie zeigt doch richtig die Zahl 7 an. Oder meinst du, wenn die betreffende Kombination nicht gefunden wird, dass dann z.B. kein Ergebnis ausgegeben wird?
Tabelle1
 ABCDEFG
4 Key_0Satz_1Satz_2Satz_3Satz_4Satz_5
5 Key_10,10,20,30,40,5
6 Key_20,60,70,80,91
7 Key_31,11,21,31,41,5
8 Key_0aSatz_1aSatz_2aSatz_3aSatz_4aSatz_5a
9 Key_1a1020304050
10 Key_2a60708090100
11 Key_3a110120130140150
12 Key_0bSatz_1bSatz_2bSatz_3bSatz_4bSatz_5b
13 Key_1b12345
14 Key_2b678910
15 Key_3b1112131415
16       
17SuchenKey_2b     
18SuchenSatz_3     
19Ergebniss      
Formeln der Tabelle
B19 : =WENN(ISTNV(INDEX(C5:G15;VERGLEICH(B17;B5:B15;FALSCH); VERGLEICH(B18;C12:G12;FALSCH))); "";INDEX(C5:G15;VERGLEICH(B17;B5:B15;FALSCH); VERGLEICH(B18;C12:G12;FALSCH)))
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
________________________________________

Anzeige
AW: Formel INDEX und VERGLEICH
29.12.2007 21:52:00
zellner
Hallo Beverly,
danke für die Antwort, aber es ist ein bissle komplizierter, wie ich am Anfang dargestellt habe.
Ich habe Hr. Ehrensberger geantwortet, da seine Formel in der Testtabelle funktioniert hat wenn aus unterschiedlichen Bereichen abgefragt wurde
Noch mal Danke
Raphael Zellner

AW: Formel INDEX und VERGLEICH
29.12.2007 20:28:35
Josef
Hallo R. Zellner,
"Ineressanter" Tabellenaufbau.
Tabelle1

 ABCDEFGH
3        
4 Key_0Satz_1Satz_2Satz_3Satz_4Satz_5 
5 Key_10,10,20,30,40,5 
6 Key_20,60,70,80,91 
7 Key_31,11,21,31,41,5 
8 Key_0aSatz_1aSatz_2aSatz_3aSatz_4aSatz_5a 
9 Key_1a1020304050 
10 Key_2a60708090100 
11 Key_3a110120130140150 
12 Key_0bSatz_1bSatz_2bSatz_3bSatz_4bSatz_5b 
13 Key_1b12345 
14 Key_2b678910 
15 Key_3b1112131415 
16        
17SuchenKey_2b      
18SuchenSatz_4b      
19Ergebniss9      
20        

Formeln der Tabelle
ZelleFormel
B19=INDEX(BEREICH.VERSCHIEBEN(B3;VERGLEICH(B17;B4:B15;0); 1;1;5); VERGLEICH(B18;BEREICH.VERSCHIEBEN(B3;VERGLEICH("Key_0"&WENN(LÄNGE(B17)>5;RECHTS(B17;1); ""); B4:B15;0); 1;1;5); 0))
Excel Tabellen im Web darstellen  Excel Jeanie HTML
Gruß Sepp

Anzeige
AW: Formel INDEX und VERGLEICH
29.12.2007 21:47:08
zellner
Hallo Herr Ehrensberger,
ich hätte heute so schnell keine Antwort erwartet, vielen Dank.
Leider ist es doch nicht ganz so einfach wie in meiner Testtabelle dargestellt, die echt sieht etwas komplizierter aus.
Können Sie mir dazu noch mal helfen?
Das erste wär nämlich, das die Daten auf einem separaten Tabellenblatt innerhalb der Arbeitsmappe liegen.
Das zweite, der Key besteht aus Städtenamen, die natürlich völlig unterschiedlich ausfallen können und eigentlich immer nur die Zahlen gleich sind.
Und drittens: die Auswahl der Sätze erfolgt aus einer Zelle, die mit einem DropDown Menü ausgefüllt werden kann. Die Daten hierfür hole ich mir ebenfalls aus dieser Tabelle und würde z. B. die Daten "Satz_1b bis Satz_5b" beinhalten, aber das klappt bereits:)
Welche Funktion hat der Teil: BEREICH.VERSCHIEBEN ?
Ich habe noch mal ein Beispiel angehängt
https://www.herber.de/bbs/user/48701.xls
Danke noch mal
Raphael Zellner

Anzeige
AW: Formel INDEX und VERGLEICH
30.12.2007 10:48:00
Josef
Hallo Raphael,
zeig doch mal einen Ausschnitt deiner Tabelle mit Originaldaten, vor allem was die Stadt- und Satzbezeichnungen betrifft. So wird das doch ein endloses Ratespiel.
Gruß Sepp

AW: Formel INDEX und VERGLEICH
30.12.2007 17:17:38
zellner
Hallo Herr Ehrensberger,
auchIhnen Danke für die Unterstützung bei meinem Problem. Erich hat in der Zwischenzeit die Lösung gefunden.
Einen guten Rutsch nach 2008 und ein paar ruhige Tage
Raphael zellner

AW: Formel INDEX und VERGLEICH
29.12.2007 21:18:31
Erich
Hallo R.,
vielleicht reicht auch schon die Formel in Zeile 20.
Sie prüft nur die ersten 6 Stellen des Satzes gegen C4:G4.
In B20 steht dann 70 statt #NV.
Sind die Zeilen 8 und 12 wirklich erforderlich?
 ABCDEFG
4 Key_0Satz_1Satz_2Satz_3Satz_4Satz_5
5 Key_10,10,20,30,40,5
6 Key_20,60,70,80,91
7 Key_31,11,21,31,41,5
8 Key_0aSatz_1aSatz_2aSatz_3aSatz_4aSatz_5a
9 Key_1a1020304050
10 Key_2a60708090100
11 Key_3a110120130140150
12 Key_0bSatz_1bSatz_2bSatz_3bSatz_4bSatz_5b
13 Key_1b12345
14 Key_2b678910
15 Key_3b1112131415
16       
17SuchenKey_2a Key_2a   
18SuchenSatz_2 Satz_2a   
19Formel 1#NV 70   
20Formel 270 70   

Formeln der Tabelle
ZelleFormel
B19=INDEX(BEREICH.VERSCHIEBEN(B3;VERGLEICH(B17;B4:B15;0); 1;1;5); VERGLEICH(B18;BEREICH.VERSCHIEBEN(B3;VERGLEICH("Key_0"&WENN(LÄNGE(B17)>5;RECHTS(B17;1); ""); B4:B15;0); 1;1;5); 0))
D19=INDEX(BEREICH.VERSCHIEBEN(B3;VERGLEICH(D17;B4:B15;0); 1;1;5); VERGLEICH(D18;BEREICH.VERSCHIEBEN(B3;VERGLEICH("Key_0"&WENN(LÄNGE(D17)>5;RECHTS(D17;1); ""); B4:B15;0); 1;1;5); 0))
B20=INDEX(C5:G15;VERGLEICH(B17;B5:B15;0); VERGLEICH(LINKS(B18;6); C4:G4;0))
D20=INDEX(C5:G15;VERGLEICH(D17;B5:B15;0); VERGLEICH(LINKS(D18;6); C4:G4;0))

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Formel INDEX und VERGLEICH
29.12.2007 22:00:00
zellner
Hallo Erich,
leider sind die Zeilen 8 und 12 notwendig. Er beinhaltet einen Städtenamen und "_0" bis "_4" im Anhang.
Für jeden Stadtbereich gibt es dann einen unterschiedlichen Satz, der alle möglichen Namen haben kann.
Durch den Städtenamen und den Anhang habe ich eine eindeutige Zeile, der Satz ist jedoch mein Problem. Die Formel von Hr. Ehrensberger war soweit ok, allerdings mein Beispiel zu stark vereinfacht. Die Daten stehen auf einem weiteren Tabellenblatt in der Arbeitsmappe.
Vielen Dank aber für Ihren Beitrag.
Raphael Zellner

AW: Formel INDEX und VERGLEICH
30.12.2007 02:05:00
Erich
Hallo Raphael,
hier mal zwei neue Formeln (in Spalte C mit Fehlerausgabe für Stadt.._0):
 ABC
17SuchenStadtA_3 
18SuchenSatz_5a 
19   
20Formel 1150150
21Formel 2150150

Formeln der Tabelle
ZelleFormel
B20=INDEX(Tabelle2!C5:G15;VERGLEICH(B17;Tabelle2!B5:B15;0); VERGLEICH(B18;BEREICH.VERSCHIEBEN(Tabelle2!C4:G4; 4*(GANZZAHL((VERGLEICH(B17;Tabelle2!B4:B15;0)-1)/4)); 0); 0))
C20=WENN(RECHTS(B17;1)="0";NV(); INDEX(Tabelle2!C5:G15;VERGLEICH(B17;Tabelle2!B5:B15;0); VERGLEICH(B18;BEREICH.VERSCHIEBEN(Tabelle2!C4:G4; 4*(GANZZAHL((VERGLEICH(B17;Tabelle2!B4:B15;0)-1)/4)); 0); 0)))
B21=SVERWEIS(B17;Tabelle2!B5:G15; 1+VERGLEICH(B18;BEREICH.VERSCHIEBEN(Tabelle2!C4:G4; 4*(GANZZAHL((VERGLEICH(B17;Tabelle2!B4:B15;0)-1)/4)); 0); 0); 0)
C21=WENN(RECHTS(B17;1)="0";NV(); SVERWEIS(B17;Tabelle2!B5:G15; 1+VERGLEICH(B18;BEREICH.VERSCHIEBEN(Tabelle2!C4:G4; 4*(GANZZAHL((VERGLEICH(B17;Tabelle2!B4:B15;0)-1)/4)); 0); 0); 0))

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
P.S.: Im Forum duzen wir uns üblicherweise.

Anzeige
AW: Formel INDEX und VERGLEICH
30.12.2007 09:36:00
Erich
Hallo Raphael,
mein voriger Beitrag ging davon aus, dass zu jeder Stadt genau 4 Zeilen mit _0 bis _3 existieren,
wobei die _0 für die Namen der Sätze reserviert ist.
In folgendem Beispiel muss nur noch für jede Stadt die _0 mit den Satznamen existieren,
bei den Stadtteilen sind _1 bis _9 möglich.
Die Satznamen pro Stadt sind beliebig.
Trifft das Beispiel deine Datenstruktur besser?
Die Formel in C19 gibt "nein" aus, wenn Stadt_0 gesucht wird, B19 gibt den Satznamen aus.
Tabelle2

 BCDEFG
3Key12345
4Köln_0aaabbbcccdddeee
5Köln_10,10,20,30,40,5
6Köln_20,60,70,80,91
7Köln_31,11,21,31,41,5
8Ulm_0fffggghhhiiijjj
9Ulm_11020304050
10Ulm_760708090100
11Ulm_3110120130140150
12Ulm_4160170180190200
13Bonn_0kkklllmmmnnnooo
14Bonn_11112131415


Tabelle1

 ABC
16SuchenUlm_7 
17Suchenjjj 
18   
19Formel100100

Formeln der Tabelle
ZelleFormel
B19=SVERWEIS(B16;Tabelle2!B5:G14; 1+VERGLEICH(B17;BEREICH.VERSCHIEBEN(Tabelle2!C4:G4; VERGLEICH(LINKS(B16;LÄNGE(B16)-1)&"0";Tabelle2!B4:B14;0)-1;0); 0); 0)
C19=WENN(RECHTS(B16;1)="0";"nein"; SVERWEIS(B16;Tabelle2!B5:G14; 1+VERGLEICH(B17;BEREICH.VERSCHIEBEN(Tabelle2!C4:G4; VERGLEICH(LINKS(B16;LÄNGE(B16)-1)&"0";Tabelle2!B4:B14;0)-1;0); 0); 0))

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
DANKE: Formel INDEX und VERGLEICH
30.12.2007 17:15:24
zellner
Hallo Erich,
Die Formel aus B19 habe ich eben in mein Original eingetragen und angepasst: Funktioniert spitzenmäßig!!! Ich bin super dankbar für die Unterstützung. Ein paar der Formel kannte ich noch nicht.
Vielen Dank noch mal, guten Rutsch nach 2008 und ein paar ruhige Tage ohne excel-Probleme anderer Leute.
Bis zum nächsten mal
Raphael Zellner

Danke für Rückmeldung und guten Rutsch! (owT)
30.12.2007 18:41:00
Erich

310 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige