Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 17:19:09
Formel mit Index-Funktion
24.04.2024 16:19:42
SummeWenn / SummeWenns mit Bedingung wenn kleiner als dann
24.04.2024 14:48:50
"Eingabefeld" zwischen zwei Zeilen geht nicht weg.
24.04.2024 14:40:10
Zeilenumbrüche in Textbox zählen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
984to988
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
984to988
984to988
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel gesucht

Formel gesucht
20.06.2008 10:34:50
Timo
Hallo zusammen,
meine Güte dieses Problem findet für mich kein Ende.
Werner, du hattest mir hier neulich schon mal geholfen. Nochmals vielen Dank an dieser Stelle. Nun will mein Vorgesetzter es aber anders als ursprünglich haben. Leider schaffe ich das jetzt, trotz der Inspiration deines letzten Vorschlages, nicht.

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


In Tabelle 1 sind "Bauteile" in den Zeilen aufgeführt, in den Spalten "Produktaggregate".
- 1 Bauteil kann mehrere Teilenummern haben. (Weil es von unterschiedlichen Lieferanten kommen kann)
- Fließt ein Bauteil in ein Produktaggregat ein, so ist der Lieferanteil des Lieferanten angegeben. D.h. alle Teile bei denen die Prozentangabe größer 0 ist fließen in das Produktaggregat ein.
- Manche Felder sind frei, auch diese Teilenummern gehen nicht in das Aggregat ein.
- Ein Bauteil kommt von maximal 2 Lieferanten.
In Tabelle 2 sollen die Orangenen Felder variabel sein.
Wenn ein Bauteil von nur einem Lieferanten kommt sollen die Felder vom 2. Lieferanten frei bleiben. Wenn ein Bauteil gar nicht in das Aggregat eingeht sollen alle Felder frei bleiben.
In Tabelle 2 habe ich die SOLL-Antworten eingetragen die kommen sollten, wenn in den orangenen Feldern Teil A und die Produktaggregate 1 und 3 stehen.
Und? Habt ihr eine Ahnung wie man das mit einer Formel lösen kann?
Vielen Dank für eure Hilfe
Gruß
Timo

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel gesucht
20.06.2008 17:11:00
fcs
Hallo Timo,
eine Formellösung wird schwierig, da du mehrfach voneinander abhängige Verweise verarbeiten muss.
Strukturiere per Makro deine Quelldaten um und mach die Auswertung per Pivot-Tabellenbericht. Das ist einfach, flexibel und bietet per Drop-Downauswahl Konfigurations-Möglichkeiten.
Siehe Beispieldatei:
https://www.herber.de/bbs/user/53226.xls
Gruß
Franz

mit INDEX() und KKLEINSTE() ...
20.06.2008 17:21:00
neopa
Hallo Timo,
... hab Dir etwas gebastelt. Den Rest könntest Du auf dieser Basis ja dann selbst einfach anpassen, ist nur noch minimal. Hätte die Berechnung für Lieferant und Preis auch noch alles in die eine Formel pressen können, aber dazu fehlten mir jetzt noch ein paar Minuten, muss jetzt leider Schluss machen.
Formel aus C5 einfach nach rechts kopieren.
Tabelle1

 ABCDEFG
2Quelle      
3LieferantTeilenummerTeilebezeichnungPreisProduktaggregat1Produktaggregat2Produktaggregat3
4Lieferant1123Teil A1 €100%0%50%
5Lieferant2456Teil B2 €50%20%100%
6Lieferant3789Teil A3 €0%100%50%
7Lieferant4213Teil C4 €20%50% 
8Lieferant5546Teil C5 €80%50% 
9Lieferant6879Teil D6 €70% 50%
10Lieferant7321Teil E7 € 100%0%
11Lieferant8654Teil B8 €50%80%0%
12Lieferant9987Teil D9 €30% 50%
13Lieferant10231Teil F10 €100%0%100%


Tabelle2

 ABCDEF
3  Produktaggregat1Produktaggregat3
4  1. Lieferant2. Lieferant1. Lieferant2. Lieferant
5Teil ATeilenummer123 123789

Formeln der Tabelle
ZelleFormel
C5{=WENN(INDEX(Tabelle1!$E:$G;KKLEINSTE(WENN(Tabelle1!$C$4:$C$19=$A$5;ZEILE(A$4:A$6)); REST(SPALTE(Tabelle3!B1); 2)+1); VERGLEICH(WENN(C$3="";B$3;C$3); Produkt;))>0;INDEX(Tabelle1!$B:$B;KKLEINSTE(WENN(Tabelle1!$C$4:$C$19=$A$5;ZEILE(A$4:A$6)); REST(SPALTE(Tabelle3!B1); 2)+1)); "")}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
C5Produkt=Tabelle1!$E$3:$G$3
Namen verstehen

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C3Liste =Produkt 
E3Liste =Produkt 
A5Liste =Teil 
Namen in Formeln
ZelleNameBezieht sich auf
C3Produkt=Tabelle1!$E$3:$G$3
E3Produkt=Tabelle1!$E$3:$G$3
A5Teil=Tabelle1!$I$4:$I$9
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
ein Nachtrag ...
20.06.2008 21:20:00
neopa
Hallo Timo,
.. wollte gerade ein paar Freiminuten nützen, um Dir die angefangene Formel noch zu komplettieren.
Dabei musste ich jedoch feststellen, dass sich erstens in meiner Formel ein kleiner Fehler eingeschlichen hatte, der jedoch schnell behoben war. Anstelle ... ZEILE(A$4:A$6) ... sollte es ... ZEILE(A$4:A$19)... lauten. Die Formel für Lieferant und Preis war auch schnell ergänzt. Doch beim Prüfen stellte ich noch Probleme fest, die sich darin äußern, dass es in bestimmten Konstellationen Fehlererwerte ergibt und wenn es z.B. nur einen Ergebnis-Lieferant gibt, dieser in der Spalte für den 2. Lieferant gelistet wird.
Mit einer Formeloptimierung sollte das behebbar sein. Doch dazu werde ich momentan und die nächsten mind. 3 Tage nicht kommen. Auch wenn Du es nicht mehr brauchen solltest, mich interessiert die Lösung es jetzt selbst, so dass wenn ich wieder etwas Zeit habe nochmal einen Versuch starte.
Hier nun noch die fehlerbehaftete Zwischenlösung, falls es trotzdem jemand interessieren sollte.
 ABCDEF
3  Produktaggregat1Produktaggregat3
4  1. Lieferant2. Lieferant1. Lieferant2. Lieferant
5Teil ATeilenummer123 123789
6 LieferantLieferant1 Lieferant1Lieferant3
7 Preis1,00 € 1,00 €3,00 €

Formeln der Tabelle
ZelleFormel
C5{=WENN(INDEX(Tabelle1!$E:$G;KKLEINSTE(WENN(Tabelle1!$C$4:$C$19=$A$5;ZEILE(A$4:A$19)); REST(SPALTE(Tabelle3!B1); 2)+1); VERGLEICH(WENN(C$3="";B$3;C$3); Produkt;))>0;INDEX(Tabelle1!$A:$D;KKLEINSTE(WENN(Tabelle1!$C$4:$C$19=$A$5;ZEILE(A$4:A$19)); REST(SPALTE(Tabelle3!B1); 2)+1); VERGLEICH($B5;Tabelle1!$A$3:$D$3;)); "")}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
C5Produkt=Tabelle1!$E$3:$G$3
Namen verstehen

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C3Liste =Produkt 
E3Liste =Produkt 
A5Liste =Teil 
Namen in Formeln
ZelleNameBezieht sich auf
C3Produkt=Tabelle1!$E$3:$G$3
E3Produkt=Tabelle1!$E$3:$G$3
A5Teil=Tabelle1!$I$4:$I$9
Namen verstehen

Gruß Werner
.. ,- ...

Anzeige
AW: Formel gesucht - Formellösung
21.06.2008 11:39:39
fcs
Hallo Timo,
hier meine Formel-Lösung.
Es ist aber meiner Meinung nach nur mit Hilfsberechnungen möglich das Ergebnis zu berechnen.
Die Berechnung aller Zwischenwerte in eine Formel zu integrieren für meines Wissens zu Problemen in der Exelberechnung (Kombination mehrerer Matrixoperationen in einer Formel) und solch eine Formel ist extrem unübersichtlich.
Da die Formeln recht komplex sind hier auch deine Datei mit den Formeln. Im Blatt 2 hab ich die verbundenen Zellen in Zeile 3 aufglöst. Die Auswahl für Teilebezeichnung und Aggregat erfolgt mit Datengültigkeit-Dropdown-Listen.
Die hierfür festgelegten Namen (TeileBez und Produkt) werden auch in den Formeln verwendet.
https://www.herber.de/bbs/user/53234.xls
Gruß
Franz
Tabellenblattname: Tabelle2 A B C D E F 1 2 Abfrage 3 Produktaggregat1 Produktaggregat3 4 1. Lieferant 2. Lieferant 1. Lieferant 2. Lieferant 5 Teil A Teilenummer 123 123 789 6 Lieferant Lieferant1 Lieferant1 Lieferant3 7 Preis 1 1 3 8 Anteil 100% 50% 50% 9 A B C D E 10 Hilfswerte und Berechnungen 11 Name Quelltabelle Tabelle1 12 1. Datenzeile 4 13 Letzte Datenzeile 13 14 Spalte Prozentwerte 5 7 15 Prozentwertebereich 'Tabelle1'!Z4S5:Z13S5 'Tabelle1'!Z4S7:Z13S7 16 Zeile Lieferant 1 4 4 17 Zeile Lieferant 2 4 6 Benutzte Formeln: C5: =WENN(C$17>0;INDEX(Daten;C$16;2);"") C6: =WENN(C$17>0;INDEX(Daten;C$16;1);"") C7: =WENN(C$17>0;INDEX(Daten;C$16;4);"") C8: =WENN(C$17>0;INDEX(Daten;C$16;C14);"") C14: =4+VERGLEICH(C$3;Produkt;0) C15: ="'" & $B$11 & "'!Z" & $B$12 & "S" & C14 & ":Z" & $B$13 & "S" & C14 C16: =MIN(WENN((TeileBez=$A$5)*(INDIREKT(C15;FALSCH)>0);ZEILE(TeileBez);999999)) C17: =MAX(WENN((TeileBez=$A$5)*(INDIREKT(C15;FALSCH)>0);ZEILE(TeileBez);0)) D5: =WENN(C$17C$16;INDEX(Daten;C$17;2);"") D6: =WENN(C$17C$16;INDEX(Daten;C$17;1);"") D7: =WENN(C$17C$16;INDEX(Daten;C$17;4);"") D8: =WENN(C$17C$16;INDEX(Daten;C$17;C14);"") E5: =WENN(E$17>0;INDEX(Daten;E$16;2);"") E6: =WENN(E$17>0;INDEX(Daten;E$16;1);"") E7: =WENN(E$17>0;INDEX(Daten;E$16;4);"") E8: =WENN(E$17>0;INDEX(Daten;E$16;E14);"") E14: =4+VERGLEICH(E$3;Produkt;0) E15: ="'" & $B$11 & "'!Z" & $B$12 & "S" & E14 & ":Z" & $B$13 & "S" & E14 E16: =MIN(WENN((TeileBez=$A$5)*(INDIREKT(E15;FALSCH)>0);ZEILE(TeileBez);999999)) E17: =MAX(WENN((TeileBez=$A$5)*(INDIREKT(E15;FALSCH)>0);ZEILE(TeileBez);0)) F5: =WENN(E$17E$16;INDEX(Daten;E$17;2);"") F6: =WENN(E$17E$16;INDEX(Daten;E$17;1);"") F7: =WENN(E$17E$16;INDEX(Daten;E$17;4);"") F8: =WENN(E$17E$16;INDEX(Daten;E$17;E14);"") Die Formeln in E16 und E17 sind Matrixformeln!! Namen in der Tabelle: Daten : =Tabelle1!$A$1:$IU$65535 Produkt : =Tabelle1!$E$3:$G$3 TeileBez : =Tabelle1!$C$4:$C$13


Anzeige
AW: Formel gesucht
23.06.2008 15:12:11
Timo
Hallo zusammen,
ersteinmal vielen Dank für eure Antworten und Entschuldigung für meine späte Rückmeldung. Ich war allerdings über das Wochenende auf Reise.
Franz, sicher ist es einfacher mit einem Makro, aber leider habe ich gar keine Ahnung wie man das anwendet. Auf lange Frist will ich mich in Makros und VBA einarbeiten, weil ich mit immer komplexer werdenden Problemen konfrontiert werde, die sicher mit Makros einfacher zu lösen sind. Hast du hierfür vielleicht Literturempfehlungen? Diese sollten allerdings für absolute Anfänger ausgelegt sein.
Zu euren Lösungen: ich muss mich nun ersteinmal in beide Formeln reindenken.
Werner, bei dir gefällt mir, dass es eine Lösung zu sein scheint, die ich mit kopieren nach unten und rechts verschieben kann. Allerdings sagst du, dass bei einigen Konstellationen noch Fehler auftreten. Ich hoffe diese finden zu können.
Franz, wie oben gesagt, gefällt mir bei Werners Lösung, dass es nur eine Formel ohne Hilfsspalten ist. Sollte ich dies nicht auf meine Problem umbauen können, werde ich sicher auf deine Lösung zurückgreifen.
Also euch beiden
Vielen Dank
Ich gebe dann nocheinmal eine Rückmeldung, wenn ich weiter gekommen bin.
Gruß
Timo

Anzeige
AW: Formel gesucht
24.06.2008 14:08:00
fcs
Hallo Timo,
ich hab jetzt mal die Hilfsberechnungen etwas anders angeordnet.
Variante 1:
Ein Teil der Hilfsberechnungen ist direkt in die Hauptformeln eingebaut und die restlichen Hilfsberechnungen oberhalb der Liste angeordnet.
Jetzt kann man immer die 2 Spalten eines Produktaggregats nach rechts kopieren oder die 4 Zeilen eines Teils nach unten kopieren.
https://www.herber.de/bbs/user/53317.xls
Variante 2:
Ein Teil der Hilfsberechnungen ist oberhalb der Liste angeordnet. Die jeweils zutreffenden Zeilen für die Lieferanten des Teils werden in einer separaten Zeile berechnet.
Jetzt kann man immer die 2 Spalten eines Produktaggregats nach rechts kopieren oder die 5 Zeilen eines Teils nach unten kopieren.
Die Zeile mit den Zeilennummern kann man z.B. per Autofilter ausblenden.
https://www.herber.de/bbs/user/53318.xls
Gruß
Franz

Anzeige
mit gekürzter MATIXformel besser realisierbar ...
24.06.2008 15:02:50
neopa
Hallo Timo,
... hatte wie bereits geschrieben am Freitag wegen Zeitmangel nicht die optimale Formellösung gefunden. Bin erst seit vorhin wieder dazugekommen, mich der Problematik anzunhmen. Mein jeztz vorliegendes Ergebnis alle von mir am Freitag benannten Probleme bereinigt und das bei einer kürzeren Formellösung die zusätzlich auch mehr Flexibilität bietet.
Meine neue Formel könnte natürlich etwas weniger schwülstig sein, wenn keine Zellverbindungen berücksichtigt werden müssten. Doch das war ja eine (hier durchaus nachvollziehbare) Bedingung. Die Formel ist diesmal auch so flexiebel, dass der komplette Ergebnisbereich A3:F7 nachträglich (fast) beliebig verschoben werden kann.
Ansonsten gebe ich natürlich Franz Recht, diese Formel ist für Formelungeübte Nutzer sicher nicht gleich verständlich, doch VBAunerfahren geht das sicherlich so auch mit einem Makrocode ;-) .
Die Formel zunächst natürlich im vorhandenen Zielbereich in C3 einsetzen und dann wieder einfach durch Ziehen nach Rechts und unten kopieren. Fertig!
Tabelle1

 ABCDEFGHI
3LieferantTeilenummerTeilebezeichnungPreisProduktaggregat1Produktaggregat2Produktaggregat3  
4Lieferant1123Teil A1 €100%0%50% Teil A
5Lieferant2456Teil B2 €50%20%100% Teil B
6Lieferant3789Teil A3 €0%100%50% Teil C
7Lieferant4213Teil C4 €20%50%  Teil D
8Lieferant5546Teil C5 €80%50%  Teil E
9Lieferant6879Teil D6 €70% 50% Teil F
10Lieferant7321Teil E7 € 100%0%  
11Lieferant8654Teil B8 €50%80%0%  
12Lieferant9987Teil D9 €30% 50%  
13Lieferant10231Teil F10 €100%0%100%  
14         


Tabelle3

 ABCDEF
3  Produktaggregat1Produktaggregat3
4  1. Lieferant2. Lieferant1. Lieferant2. Lieferant
5Teil ATeilenummer123 123789
6 LieferantLieferant1 Lieferant1Lieferant3
7 Preis1 13

Formeln der Tabelle
ZelleFormel
C5{=WENN(SUMME((Tabelle1!$C$4:$C$99=$A$5)*(Produkt=INDEX($C$3:$F$3;WENN(ZÄHLENWENN($A$4:C$4;"*"&"ant")>2;3;1)))*(Tabelle1!$E$4:$G$99>0))>(LINKS(C$4;1)-1); INDEX(Tabelle1!$A:$D;KKLEINSTE(WENN(Tabelle1!$C$4:$C$99=$A$5;ZEILE(A$4:A$99)); --LINKS(C$4;1)); VERGLEICH($B5;Tabelle1!$A$3:$D$3;)); "")}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
C5Produkt=Tabelle1!$E$3:$G$3
Namen verstehen

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C3Liste =Produkt 
E3Liste =Produkt 
A5Liste =Teil 
Namen in Formeln
ZelleNameBezieht sich auf
C3Produkt=Tabelle1!$E$3:$G$3
E3Produkt=Tabelle1!$E$3:$G$3
A5Teil=Tabelle1!$I$4:$I$9
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: mit gekürzter MATIXformel besser realisierbar ...
24.06.2008 16:13:00
Timo
Hallo miteinander,
ich habe dank eurer Anregungen eine Lösung gefunden, indem ich ein wenig mit den Formeln herumgespielt habe. Diese benötigt nun keine Hilfspalten. Diese ist allerdings sehr lang und ich versuche es nocheinmal mit der nun verkürzten Formel.
Euch vielen Dank für die Hilfe. Auch wenn ich jetzt doch eine eigene Formel gefunden habe, hätte ich diese ohne eure Ideen nicht formulieren können.
Danke schön
Gruß
Timo

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige