Microsoft Excel

Herbers Excel/VBA-Archiv

Vergleich-Funktion für mehrere Treffer? | Herbers Excel-Forum


Betrifft: Vergleich-Funktion für mehrere Treffer? von: Axel Meyer
Geschrieben am: 06.02.2012 17:04:34

Hallo Excelfreunde!

Vielleicht habe ich ja hier einmal eine kniffelige Aufgabe...

Mit VERGLEICH suche ich in einer Liste Stücke heraus, mit Index gebe ich mir die Eigenschaften
dieser Stücke aus einer Nachbarliste zurück


=INDEX(Schedule;VERGELIJKEN(H3&"x"&I3;ConcentrRed;0))

Das funktioniert prima.

Meine Probleme:
ich würde in meiner Formelzelle gerne eine Datenvalidierungsliste aus den Treffern (plural!) erzeugen. D.h., "VERGLEICH" findet in meiner Stückliste eigentlich nicht nur EINEN, den ersten Treffer, den mir zurückgibt. Ich möchte aber alle ca 2...4 weiteren Stücke ebenso finden (die haben in der Nachbarzelle andere Eigenschaften (=Wandstärken/Dicken)). Abhängig von der Auswahl des Stückes durch den User (in den Zellen H3, I3), möchte ich in meiner Zelle (J3) alle zulässigen Eigenschaften (Materialdicken) per Dropdown-Datenvalidierungsliste darstellen.

Puh, verständlich ausgedrückt?

Danke für alle Hilfen!

Gruß aus dem Pommes-Land,
Axel

  

Betrifft: AW: Vergleich-Funktion für mehrere Treffer? von: Josef Ehrensberger
Geschrieben am: 06.02.2012 17:25:58


Hallo Alex,

mit einer kleinen Hilfs-Liste geht das.

Tabelle1

 ABCDEFGHIJKL
1           a
212a        b
313a    12b c
412b         
512c         
613b         

Formeln der Tabelle
ZelleFormel
L1{=WENN(ZEILE(A1)>SUMME(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3)); "";INDEX($C$2:$C$6;KKLEINSTE(WENN(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3); ZEILE($1:$5)); ZEILE(A1))))}
L2{=WENN(ZEILE(A2)>SUMME(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3)); "";INDEX($C$2:$C$6;KKLEINSTE(WENN(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3); ZEILE($1:$5)); ZEILE(A2))))}
L3{=WENN(ZEILE(A3)>SUMME(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3)); "";INDEX($C$2:$C$6;KKLEINSTE(WENN(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3); ZEILE($1:$5)); ZEILE(A3))))}
L4{=WENN(ZEILE(A4)>SUMME(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3)); "";INDEX($C$2:$C$6;KKLEINSTE(WENN(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3); ZEILE($1:$5)); ZEILE(A4))))}
L5{=WENN(ZEILE(A5)>SUMME(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3)); "";INDEX($C$2:$C$6;KKLEINSTE(WENN(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3); ZEILE($1:$5)); ZEILE(A5))))}
L6{=WENN(ZEILE(A6)>SUMME(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3)); "";INDEX($C$2:$C$6;KKLEINSTE(WENN(($A$2:$A$6=$H$3)*($B$2:$B$6=$I$3); ZEILE($1:$5)); ZEILE(A6))))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
J3Liste =_list 
Namen in Formeln
ZelleNameBezieht sich auf
J3_list=Tabelle1!$L$1:INDEX(Tabelle1!$L:$L;SUMME((Tabelle1!$A$2:$A$6=Tabelle1!$H$3)*(Tabelle1!$B$2:$B$6=Tabelle1!$I$3)))
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4




« Gruß Sepp »



  

Betrifft: AW: Vergleich-Funktion für mehrere Treffer? von: Axel Meyer
Geschrieben am: 08.02.2012 08:31:17

Guten morgen Josef,

vielen Dank für Deine Hilfe. Ich fürchte aber, daß Deine Formel ist zu hoch für mich, um sie für meine Zwecke anpassen zu können. So handelt es sich z.B. bei meinen Stücken um Bauteile mit Zollmaßen, die sich also schwierig multiplizieren lassen. Deine Eingabezellen H3 und I3 sowie die Ausgabezelle J3 stimmen mit meiner Tabelle überein. Aber meine Tabelle(nposition) sieht so aus:

	AG	AH	AI
12	1/2"	3/8"	STD
13	3/4"	3/8"	STD
14	3/4"	1/2"	STD
15	3/4"	1/2"	XS
16	1"	1/2"	STD
17	1"	1/2"	XS
18	1"	1/2"	SCH 160
19	1"	3/4"	STD
20	1"	3/4"	XS
21	1"	3/4"	SCH 160
22	1 1/4"	1/2"	STD
23	1 1/4"	1/2"	XS
...	…	…	…

Aufgrund der "-Zeichen kann ich also nicht multiplizieren. Ich könnte zwar das Zahlenformat auf 2x (') anpassen, aber dann müßte ich (denke ich) mit 2,5" anstelle von 2½" arbeiten, was ich vermeiden möchte.

Ist Deine Formel auch dafür geeignet? Wie müßte sie für meine Tabelle ausgeführt werden? Dei Aus Deinem Beispiel vermute ich, dass die C-Spalten-Einträge meiner AI-Spalte entsprechen, Deine B-Spalte meiner AH-Spalte und Deine A-Spalte meiner AG-Spalte. Ich weiß aber nicht, warum Du auf Zeile(A1) referenzierst und warum auf Zeile($1:$5)? Auch: was macht KKleinste? Ich arbeite hier in einem Niederländischen Office und kann das schwierig übersetzen, mir also auch schwierig die Hilfe durchlesen.

Gruß,
Axel


  

Betrifft: AW: Vergleich-Funktion für mehrere Treffer? von: Josef Ehrensberger
Geschrieben am: 08.02.2012 08:39:01


Hallo Axel,

was in den Zellen steht ist doch egal, es werden nicht die Zellwerte Multipliziert sondern Wahrheitswerte.

A1 ist ein Zähler und Zeile(1:5) ist eine Matrix. KKLEINSTE() sollte dir bei XL-gut schon bekannt sein.

Lade doch eine Beispieldatei hoch, dann passe ich die Formel entsprechend an.




« Gruß Sepp »



  

Betrifft: AW: Vergleich-Funktion für mehrere Treffer? von: Axel Meyer
Geschrieben am: 08.02.2012 09:15:53


Josef,
so schnell antworten bringt mich aus der Ruhe!
Danke. Hier der Link zu meinem Excelfile,
(DropBox genutzt, weil ich mit Up-Load unter Herber Schwierigkeiten habe):
http://dl.dropbox.com/u/58038451/Herber/ConcentricReducer.xlsx
Ich habe im Sheet die wichtigen Zellen / Spalten in rot gehalten.
Gruß,
Axel
PS:
Ich hoffe die Zellen BG21 / BC39 heben mich über das Excel-Niveau "bescheiden"... Außerdem: hätte ich "Excel bescheiden" geschrieben, hätte ich vielleicht das Los anderer User geteilt und Du hättest mir Deinen Formelporsche nicht angeboten, weil ich noch Führerscheinneuling bin...


  

Betrifft: AW: Vergleich-Funktion für mehrere Treffer? von: Josef Ehrensberger
Geschrieben am: 08.02.2012 09:38:38


Hallo Alex,

hier die angepasste Datei.

https://www.herber.de/bbs/user/78789.xlsx




« Gruß Sepp »



  

Betrifft: Josef: Zauberer, ich danke Dir! owT von: Axel Meyer
Geschrieben am: 08.02.2012 09:45:21

-


  

Betrifft: AW: Vergleich-Funktion für mehrere Treffer? von: Josef Ehrensberger
Geschrieben am: 08.02.2012 08:39:04


Hallo Axel,

was in den Zellen steht ist doch egal, es werden nicht die Zellwerte Multipliziert sondern Wahrheitswerte.

A1 ist ein Zähler und Zeile(1:5) ist eine Matrix. KKLEINSTE() sollte dir bei XL-gut schon bekannt sein.

Lade doch eine Beispieldatei hoch, dann passe ich die Formel entsprechend an.




« Gruß Sepp »



Beiträge aus den Excel-Beispielen zum Thema "Vergleich-Funktion für mehrere Treffer?"