alex82
Goto Top

Umcodieren durch spaltenweises vergleichenen von mehreren Teilen sowie Leistung. Kompliziert!?

Hallo!

Ich habe eine sehr komplizierte Problemstellung welche ich nicht mehr mit "bedingte Formatierung“, “Filtern" oder "wenn Funktionen" realisieren kann.

Grundlegende Problemstellung:
Mehreren Anlagen soll anhand von den verwendeten Teilen, Herstellern, und verwendeter Norm jeweils ein Vordruck zugeordnet werden. Also Aufgrund der gegebenen Angaben die Umcodierung in den jeweils zu verwendenden Vordruck erfolgen. Da nach soll noch geprüft werden ob die Leistung der jeweiligen Anlage für diesen Verwendungszweck ok ist.


Ausgangsmaterial ist einer Tabelle (Tabellenblatt "Zuordnung") mit folgenden Daten:

Spalte A "Familie"(Spalte A ist am Anfang leer und soll von Hand per Dropdownmenü gefüllt werden - Grundlage für die Auswahl im Dropdownmenü ist "Spalte B "Marke"") / Spalte B "Marke" / Spalte C "Hersteller" / Spalte D "Teil1" / Spalte E "Teil2" / Spalte F "Teil3" / Spalte G "Norm" / Spalten H und I brauchen nicht betrachtet zu werden (aus Spalte H und I berechnet sich Spalte J (J4=(H4/I4)) ) / Spalte J "Leistung"

z.B.

A4 Dropdown - Familie1 / B4 Marke1 / C4 Hersteller1 / D4 21131 / E4 5f89r3 / F4 004r5 / G4 Norm3 / (H4 + I4 Leisungsberechnung) / J4 1242 (kW)
A5 Dropdown - Familie3 / B5 Marke65 / C5 Hersteller2 / D5 72109 / E5 1434r2 / F5 1263 / G5 Norm3 / (H5 + I5 Leisungsberechnung) / J5 2345 (kW)
A6 Dropdown - Familie3 / B6 Marke58 / C6 Hersteller1 / D6 72109 / E5 1434r2 / F6 9328 / G6 Norm4 / (H6 + I6 Leisungsberechnung) / J6 803 (kW)
usw....


Jeder "Hersteller" hat verschiedene "Teile", teilweise werden auch die gleichen "Teile" bei 2 oder 3 Herstellern verwendet. Die Teile werden durch alphanumerische Zeichen, oftmals auch mit führender bzw. führenden Nullen bezeichnet.
Ich habe bereits für jeden Hersteller ein eigenes Tabellenblatt mit den möglichen Kombinationen erstellt welches wie folgt aussieht:
z.B.
Tabellenblatt "Hersteller1": Spalte A "Teil1" / Spalte B "Teil2" / Spalte C "Teil3" / Spalte D "Norm" / Spalte E "Formular"
A3 21131 / B3 5f89r3 / C3 004r5 / Norm3 / E Hersteller1.1a
A4 72109 / B4 1434r2 / C4 9328 / Norm3 / E Hersteller1.2a
A5 72109 / B5 1434r2 / C5 1263 / Norm3 / E Hersteller1.3a
A6 21131 / B6 5f89r3 / C6 004r5 / Norm4 / E Hersteller1.1
A7 72109 / B7 1434r2 / C7 9328 / Norm4 / E Hersteller1.2
A8 72109 / B8 1434r2 / C8 1263 / Norm4 / E Hersteller1.3
...

Tabellenblatt "Hersteller2": Spalte A "Teil1" / Spalte B "Teil2" / Spalte C "Teil3" / Spalte D "Norm" / Spalte E "Formular"
A3 21131 / B3 5f89r3 / C3 004r5 / Norm3 / E Hersteller2.1a
A4 72109 / B4 1434r2 / C4 9328 / Norm3 / E Hersteller2.2a
A5 72109 / B5 1434r2 / C5 1263 / Norm3 / E Hersteller2.3a
A6 21131 / B6 5f89r3 / C6 004r5 / Norm4 / E Hersteller2.1
A7 72109 / B7 1434r2 / C7 9328 / Norm4 / E Hersteller2.2
A8 72109 / B8 1434r2 / C8 1263 / Norm4 / E Hersteller2.3
...

Tabellenblatt "Hersteller3": Spalte A "Teil1" / Spalte B "Teil2" / Spalte C "Teil3" / Spalte D "Norm" / Spalte E "Formular"
A3 62101 / B3 z6271h / C3 7362 / Norm3 / E Hersteller3.1a
A4 98432 / B4 h5953m / C4 3829 / Norm3 / E Hersteller3.2a
A5 98432 / B5 h5953m / C5 5321 / Norm3 / E Hersteller3.3a
A6 98432 / B6 z6271h / C6 5321 / Norm3 / E Hersteller3.4a
A7 62101 / B7 z6271h / C7 7362 / Norm4 / E Hersteller3.1
A8 98432 / B8 h5953m / C8 3829 / Norm4 / E Hersteller3.2
A9 98432 / B9 h5953m / C9 5321 / Norm4 / E Hersteller3.3
A10 98432 / B10 z2671h / C10 5321 / Norm4 / E Hersteller3.4
...
Es soll nun zuerst anhand von Tabellenblatt "Zuordnung" Spalte C "Hersteller" auf in dem entsprechende Tabellenblatt des Herstellers z.b. erste Anlage aus diesem Beispiel. Tabellenblatt "Zuordnung" Spalte 4

Hersteller1 daher soll in Tabellenblatt "Hersteller1" zeilenweise verglichen werden.
Es sollen die in Tabellenblatt "Zuordnung" in Zeile 4 angegebenen Teile und die Normnummer aus dieser Zeile spaltenweise verglichen werden bis alle 3 Teilenummern sowie die Normnummer in der Zeile des Tabellenblatts "Hersteller1" mit den 3 Teilenummern und der Normnummer in Zeile 4 von Tabellenblatt Zuordnung Übereinstimmen. In der Zeile des Tabellenblatts "Hersteller1" für die diese Deckungsgleichheit zutrifft steht nun in Spalte E die Umkodierung der Anlage welche in Tabellenblatt "Zuordnung" Spalte L hinter der jeweils abgearbeiteten Anlage ausgegeben werden soll.


Des weiteren habe ich in je einem Tabellenblatt für jede "Norm-Herstellerkombination" Leistungsbereiche für jede "Familie" (Minimale bis Maximale Leistung) berechnet.
Nun soll verglichen werden ob die Leistung aus Tabellenblatt "Zuordnung" ( Spalte J) für die jeweilige "Familie" (aus Tabellenblatt "Zuordnung" Spalte A) innerhalb des erlaubten Bereichs für diese Familie Liegt.
Beispiel für das "Norm -Herstellerkombinations"Tabellenblatt
Tabellenblatt "Norm3 Hersteller1":
Familie1 Minimaler Wert: B13
Familie1 Maximaler Wert: B16
Familie2 Minimaler Wert: B23
Familie2 Maximaler Wert: B26
Familie3 Minimaler Wert: B33
Familie3 Maximaler Wert: B36
Familie4 Minimaler Wert: B43
Familie4 Maximaler Wert: B46
...
Falls die Leistung für die "Familie" in dem im jeweiligen "Norm -Herstellerkombinations" Tabellenblatt liegt soll im Tabellenblatt "Zuordnung" in Spalte M hinter der Abgearbeiteten Kombination "OK" ausgegeben werden. Falls der Wert außerhalb des berechneten Bereichs ist soll die Abweichung in Tabellenblatt "Zuordnung" in Spalte M angezeigt werden.

Ich hoffe mal dass mir jemand bei dieser komplexen Problemstellung weiterhelfen kann.
Insbesondere das zeilenweise Vergleichen und umcodieren/Ausgabe der in der "gefundenen" Spalte stehenden Umschlüsselung würden mich schon ein großes Stück weiter bringen.


Gruß Alex

Content-Key: 101902

Url: https://administrator.de/contentid/101902

Ausgedruckt am: 28.03.2024 um 18:03 Uhr

Mitglied: bastla
bastla 15.11.2008 um 17:34:21 Uhr
Goto Top
Hallo Alex82 und willkommen im Forum!

Vorläufig nur zu Teil 1:
Wenn Du in einer Hilfsspalte (im Beispiel habe ich die Spalte H der Herstellertabelle verwendet) alle 4 gesuchten Begriffe zusammenfasst und (da der in weiterer Folge verwendete SVERWEIS() nur in der ersten Spalte suchen kann) die Daten aus der Spalte E in die Spalte rechts von der Hilfsspalte (also hier: I) übernimmst, kann sehr leicht mit der SVERWEIS()-Funktion der dazu passende Eintrag gefunden werden - die Formeln in der Herstellertabelle lauten:
in H3: =A3&B3&C3&D3
in I3: =E3
Diese Formeln sind für alle Zeilen nach unten zu kopieren.

In der Zuordnungstabelle muss zunächst noch der jeweilige Bezug (wegen der variablen Zieltabelle) in einer Hilfsspalte (im Beispiel Q) erstellt werden:
in Q4: ="'" & C4 & "'!$H$3:$I$200"
wobei die letzte Zeilennummer (200) zumindest der höchsten Zeilennummer mit Daten aus allen Herstellertabellen entsprechen muss (lt Deinem obigen Beispiel wäre das die Zeile 10). Auch diese Formel muss für jede Zeile (der Zuordnungstabelle) kopiert werden.

Das Heraussuchen (in Spalte L) ginge dann so:
in L4: =SVERWEIS(D4&E4&F4&G4;INDIREKT(Q4);2;0)
SVERWEIS() muss übrigens verwendet werden, da es bei VERWEIS() keinen Parameter "Bereich_Verweis" gibt und so nicht dafür gesorgt werden kann, dass nur bei exakter Übereinstimmung ein Wert zurückgegeben wird.

Grüße
bastla
Mitglied: Alex82
Alex82 15.11.2008 um 18:47:36 Uhr
Goto Top
Hallo bastla

Vielen Dank für deine Antwort!
Das zusammenfassen ist eine wirklich gute Idee. Leider muss ich gleich weg, habe also heute Abend keine Zeit mehr das zu versuchen. Ich werde es aber direkt morgen testen und spätestens morgen Nachmittag berichten wie es gelaufen ist.

Gruß Alex
Mitglied: Alex82
Alex82 16.11.2008 um 16:04:37 Uhr
Goto Top
Nachdem ich nun die Tabelle angepasst habe funtioniert Teile vergleichen und anschliessendes Umcodieren Tadellos.
Danke bastla!!

Nun Bleibt nur noch Das Problem ob die jeweils in einer Anlage enthaltenen Teile eines Herstellers für die jeweilige Marke (Familie) im Leistungsbereich liegen.
Es müsste also geprüft werden
"Familie1" in Kombination mit "Hersteller2" für vorhandene Leistung ok?
z.B. Minimal 1345 kw bis Maximal 678234 kW
vorhanden 2361 kW ( wert aus Tabellenblatt "zuordnung" in J4 ) das ist ja ok, dann soll in "OK" oder "1" in M4 ausgegeben werden.
für den vergleich habe ich mir etw sowas gedacht
=WENN(J4>$R$4-1;(WENN(J4<$S$4-1;1))) jedoch funtioniert das leider noch nicht.
Vorläufig habe ich nun erstmal ein Tabellenblatt "Leistungsbereiche" erstellt welches die Gundlage der Überprüfung sein könnte.
es ist wie folgt aufgebaut:
A Familie / B Hersteller / C Norm / D Min Leistung / E Max Leistung

z.B.
A3 Familie1 # B3 Hersteller1 # C3 Norm1 # D3 1231 # E3 6354
A4 Familie2 # B4 Hersteller1 # C4 Norm1 # D4 623 # E4 8271
A5 Familie3 # B5 Hersteller1 # C5 Norm1 # D5 948 # E5 8242
A6 Familie4 # B6 Hersteller1 # C6 Norm1 # D6 # E6
A7 Familie5 # B7 Hersteller1 # C7 Norm1 # D7 345 # E7 7326
A8 Familie6 # B8 Hersteller1 # C8 Norm1 # D8 452 # E8 7865
A9 Familie7 # B9 Hersteller1 # C9 Norm1 # D9 252 # E9 7654
A10 Familie8 # B10 Hersteller1 # C10 Norm1 # D10 # E10
A11 Familie9 # B11 Hersteller1 # C11 Norm1 # D11 # E11
A12 Familie10 # B12 Hersteller1 # C12 Norm1 # D12 525 # E12 9864
A13 Familie11 # B13 Hersteller1 # C13 Norm1 # D13 234 # E13 6783
A14 Familie12 # B14 Hersteller1 # C14 Norm1 # D14 321 # E14 8460
A15 Familie13 # B15 Hersteller1 # C15 Norm1 # D15 342 # E15 7326
A16 Familie14 # B16 Hersteller1 # C16 Norm1 # D16 987 # E16 6121
A17 Familie15 # B17 Hersteller1 # C17 Norm1 # D17 518 # E17 2102
A18 Familie1 # B18 Hersteller2 # C18 Norm1 # D18 342 # E18 2519
A19 Familie2 # B19 Hersteller2 # C19 Norm1 # D19 252 # E19 7234
A20 Familie3 # B20 Hersteller2 # C20 Norm1 # D20 525 # E20 8271
A21 Familie4 # B21 Hersteller2 # C21 Norm1 # D21 # E21
A22 Familie5 # B22 Hersteller2 # C22 Norm1 # D22 525 # E22 2102
A23 Familie6 # B23 Hersteller2 # C23 Norm1 # D23 # E23
A24 Familie7 # B24 Hersteller2 # C24 Norm1 # D24 342 # E24 7326
A25 Familie8 # B25 Hersteller2 # C25 Norm1 # D25 # E25
A26 Familie9 # B26 Hersteller2 # C26 Norm1 # D26 342 # E26 7326
A27 Familie10 # B27 Hersteller2 # C27 Norm1 # D27 252 # E27 8460
A28 Familie11 # B28 Hersteller2 # C28 Norm1 # D28 # E28
A29 Familie12 # B29 Hersteller2 # C29 Norm1 # D29 525 # E29 7865
A30 Familie13 # B30 Hersteller2 # C30 Norm1 # D30 342 # E30 8271
A31 Familie14 # B31 Hersteller2 # C31 Norm1 # D31 # E31
A32 Familie15 # B32 Hersteller2 # C32 Norm1 # D32 252 # E32 8242
A33 Familie1 # B33 Hersteller1 # C33 Norm2 # D33 # E33
A34 Familie2 # B34 Hersteller1 # C34 Norm2 # D34 342 # E34 2102
A35 Familie3 # B35 Hersteller1 # C35 Norm2 # D35 # E35
A36 Familie4 # B36 Hersteller1 # C36 Norm2 # D36 521 # E36 3217
A37 Familie5 # B37 Hersteller1 # C37 Norm2 # D37 525 # E37 7326
A38 Familie6 # B38 Hersteller1 # C38 Norm2 # D38 # E38
A39 Familie7 # B39 Hersteller1 # C39 Norm2 # D39 843 # E39 9321
A40 Familie8 # B40 Hersteller1 # C40 Norm2 # D40 # E40
A41 Familie9 # B41 Hersteller1 # C41 Norm2 # D41 252 # E41 8460
A42 Familie10 # B42 Hersteller1 # C42 Norm2 # D42 # E42
A43 Familie11 # B43 Hersteller1 # C43 Norm2 # D43 721 # E43 9221
A44 Familie12 # B44 Hersteller1 # C44 Norm2 # D44 525 # E44 2102
A45 Familie13 # B45 Hersteller1 # C45 Norm2 # D45 # E45
A46 Familie14 # B46 Hersteller1 # C46 Norm2 # D46 252 # E46 7326
A47 Familie15 # B47 Hersteller1 # C47 Norm2 # D47 # E47

Vieleicht hat ja jemand eine Idee wie das man das lösen könnte!?
Mitglied: Alex82
Alex82 16.11.2008 um 18:07:57 Uhr
Goto Top
Habe das nun auch das Leistungsbereichproblem mit Zusammenfasen und SVERWEISEN gelöst.
Nochmals DANKE an bastla für die Hilfe!