Microsoft Excel

Herbers Excel/VBA-Archiv

Sverweis mit variabler Matrix und Zähler

Betrifft: Sverweis mit variabler Matrix und Zähler von: Florian
Geschrieben am: 06.07.2015 14:06:06

Hallo zusammen,

ich versuche jetzt seit ein paar Tagen eine Formel auf zu stellen in der eine Angebotshistorie angezeigt wird.
Der Sachverhalt ist folgender.
In eine Exceltabelle werden Angebotsdaten gezogen um diese vergleichen zu können.
die Arbeitsmappe wir dann unter dem Namenssystematik "XXXXX-XXX-1A_AP" gespeichert.
1A steht hier für den ersten Angebotsvergleich zu einem Teil. Falls weitere Angebote eingeholt werden wird der nächste Angebotsvergleich mit 2B nach der gleichen Systematik gespeichert. Alle anderen Variablen im Namen bleiben für diesen Fall fix.
(1A kann auch zu 10 und somit 1B zu 11 geändert werden falls das Problem damit leichter zu lösen wäre.)

Jetzt zu meinem eigentlichen Problem.
Die Formel soll bei jedem neuen Angebotsvergleich die vorherigen Erbaitsmappen mit durchsuchen und die Kosten dann mit in den neuen vergleich ziehen. Eine Angebotshistorie eben zu jedem Lieferanten.

Ich bin bis jetzt soweit gekommen aber eben nicht weiter.

=WENN('Angebotsvergleich Prototypen'!$D$8=0;" ";SVERWEIS('Angebotsvergleich Prototypen'!$D$8; INDIREKT("'["&'Angebotsvergleich Prototypen'!$A$8&"-"&'Angebotsvergleich Prototypen'!$B$8&"-"&'Angebotsvergleich Prototypen'!$C$8&"_AP"&".xlsx]Angebotsvergleich Prototypen'!$D:$H") ;4;0))

in D8 steht der Lieferantenname
in A8 die ersten 5 Nummer also XXXXX
in B8 die weitern 3 Nummern XXX
in C8 besagte Systematik A1 oder B1 (wahlweise 10 oder 11)

Falls fragen zum Sachverhalt offen sind, ein nachfragen ich versuche diese dann so schnell wie möglich zu beantworten.

Schon mal vielen Dank für Eure Hilfe

Grüße

Flo

  

Betrifft: AW: Sverweis mit variabler Matrix und Zähler von: Klexy
Geschrieben am: 06.07.2015 14:18:37

Ist 'Angebotsvergleich Prototypen' die Vergleichsdatei?
Dann ist die Angabe in der Formel überflüssig.

Und wie ist der Pfad, in dem die ganzen Angebotsdateien liegen?
Soll die Vergleichsdatei nur für das Projekt XXXXX-XXX gelten oder für alle vorhandenen Projekte?
usw.
Eine Musterdatei eines Angebots wäre hilfreich und eine Musterdatei der Vergleichsdatei. Wie soll die aussehen? Was soll wo angezeigt werden? Farbliche Markierung ist am einfachsten.


  

Betrifft: AW: Sverweis mit variabler Matrix und Zähler von: Florian
Geschrieben am: 06.07.2015 14:41:43

Hey,

also 'Angebotsvergleich Prototypen' ist die Tabelle in der das Suchkriterium und die Gesatmkosten stehen in der jeweiligen Verlgeichsdatei von der es ja dann mehrere geben wird.
ich versuch mal noch ein bisschen genauer zu erklären wie ich mir das vorstelle und was der Sachverhalt ist.

Es werden für in Bauteil Angebote eingeholt, diese haben eine Projektnummer (XXXXX) eine Progressnummer (XXX) und eine Teilenummer (z.B. 1A oder 10). Projektnummer und Progressnummer bleiben für das jeweilige Projekt immer fix, was sich ändert ist die Teilenummer.
eine Angebotsdatei ist dann folgendermaßen gespeichert "XXXXX-XXX-1A_Lieferant1"

Davon wird es dann logischerweise mehrere geben die als Unterschied eben den Lieferantennamen haben.

Alle eingegangen Angebote mit 1A werden dann in einer Angebotsverlgeichsmappe gezogen.
Diese heißt dann "XXXXX-XXX-1A_AP".

Fall jetzt an besagtem Teil 1A während des Projekts Änderungen von Nöten sind, werden erneut Angebote eingeholt, allerdings dann unter dem Teilenamen 1B und die ganze Sache beginnt von vorne.

Jetzt zu Problematik.

in den Angebotsvergeleicehn soll unter der Tabelle Angebotshistorie eine Auflistung der Lieferanten sein samt derer bisherigen Angebotspreise zu Teil 1, die Preise stehen ja in den Angebotsvergleichen und sollen von da gezogen werden.

Als Beispiel mal Lieferant 1. Momentan läuft die 4 Angebotsrunde zu Teil 1, also heißt dieße 1D.
Jetzt soll im Angebotsverlgiehc unter dem Namen (Suchkriterium "Lieferant1") aufgelistet werden ob dieser in den Runden A,B,C schon einmal ein Angebot abgegeben hat und dann die Kosten dieses Angebots ziehen.

Ich weiß es ist schwer zu erklären und verstehen, falls noch weiter Fragen sind einfach melden.

Meine Versuche findet Ihr in der Angebotsvergleichmappe in der Tabelle Angebotshistorie in der G Spalte. A-E sind eventuelle Versuche aus dem Netz einen Zähler zu integrieren aber ich denke für diesen Fall nicht zu gebrauchen.

Hier mal die 2 Arbeitsmappen

https://www.herber.de/bbs/user/98661.xlsx Angebotsvergleich
https://www.herber.de/bbs/user/98662.xlsm Testangebot


Vielen Dank schonmal und Grüße

Flo


  

Betrifft: AW: Sverweis mit variabler Matrix und Zähler von: Florian
Geschrieben am: 06.07.2015 15:56:19

Hier nochmal die 2 Dateien, da die vorherigen fehlerhaft waren

https://www.herber.de/bbs/user/98665.xlsx
https://www.herber.de/bbs/user/98666.xlsm


  

Betrifft: AW: Sverweis mit variabler Matrix und Zähler von: Klexy
Geschrieben am: 06.07.2015 18:15:11

Der erste Satz ergibt keinen Sinn. Da fehlt mindestens ein Wort:
also 'Angebotsvergleich Prototypen' ist die Tabelle in der das Suchkriterium und die Gesatmkosten stehen in der jeweiligen Vergleichsdatei von der es ja dann mehrere geben wird.

Wo ist die Angebotsvergleichsmappe ? Ist das die https://www.herber.de/bbs/user/98665.xlsx ? Und wenn ja, da sind keine Daten von Angeboten drin. Erst recht nicht von mehreren Angeboten. Ein einziges Angebot kann man durch Umbenennung der anderen Datei herholen. Das hast du aber nicht beschrieben, das musste ich erraten.

... soll unter der Tabelle Angebotshistorie eine Auflistung der Lieferanten sein ... Wo sind die? Von wo genau sollen sie "gezogen" werden?

usw.
Ich hab da aufgrund unklarer Syntax den Überblick verloren. Ich fürchte, da kann dir keiner helfen. Es sei denn, du machst eine oder zwei Beispieldateien in denen Daten enthalten sind (nicht nur Fehlermeldungen aufgrund fehlender Bezüge) mit einer klaren Beschreibung, was wann wo woher erscheinen soll.

Ein Tip noch: die Spalte A in "Angebotsvergleich Prototypen" hat eine meterlange Formel, in der du umständlich manuell festlegst, dass da eine 1,2,3... reingeschrieben werden soll, anstatt einfach 1,2,3... runterzuziehen und jeweils darauf Bezug zu nehmen in den Sverweisen der Spalten daneben, wo du auch in jeder einzelnen Formel manuell "2", "3", "4"... eingibst.


  

Betrifft: AW: Sverweis mit variabler Matrix und Zähler von: Florian
Geschrieben am: 07.07.2015 08:51:34

Hallo Klexy,

danke für deine Mühe mir zu helfen und entschuldig meine unklare Formulierung, da passt wohl wieder die Geschichte von Frosch und Fisch.

Ok, dann versuch ich´s nochmal.

Zuerst mal ja, https://www.herber.de/bbs/user/98665.xlsx ist die Vergleichsmappe. Damit #Bezug einen Bezug wird, musst du das Testangebot (also die andere Datei) in "40099-000-1A_Lieferant1" umbenennen und öffnen. Hast du ja schon gemacht, habs nochmal geschreiben falls wer anders drüberstolpert. Herber ändert ja den Namen beim upload. Hab ich nicht bedacht und vergessen zu erwähnen.

sobald das passiert ist dürften die Bezüge in 'Angebotsvergleich Prototypen' in der linken Tabelle ein paar Werte anzeigen um die Formel nach zu vollziehen.
Auch die Formel in 'Angebotshistorie' sollte jetzt die Gesamtkosten anzeigen.
Falls du möchtest, das alle Bezüge im Angebotsvergleich verschwinden, dann gib zu Testzwekcen einfach in A9:F13 die selben Werte ein wie in A8:F8. Oder bennen die Angebotsdatei einfach in "40099-000-1A_Liefrant2" und schreib das in A9:F9 für einen besseren Überblick.
Ich hab nur ein Angebot hochgeladen weil der Bezug von Angebot in die Angebotsvergleichsmappe ja funktioniert.

Zu deiner ersten Anmerkung, ja der Satz ist mies formuliert hab ich heute beim drüberlesen gemerkt.
Ich wollte damit ausdrücken, das in der Tabelle 'Angebotsvergleich Prototypen in Spalte D8:D13, also der Lieferantenname, das Suchkriterium für VERWEISE in der Tabelle 'Angebotshistorie' steht.
Diese Mappe, also der Angebotsvergleich, heißt ursprünglich "40099-000-1A_AP".
Falls jetzt ein zweites Angebot zu diesem Teil eingeholt wird, dann werden die neuen Angebote
"40099-000-1B_Lieferant1", "40099-000-1B_Lieferant2", usw. und der Angebotsvergleich
"40099-000-1B_AP" heißen.
Diesen Sachverhalt, das es mehrere Angebotsvergleichsmappen geben wird wollte ich mit dem 2ten Teil des Satzes ausdrücken.

Jetzt nochmal zu dem eigentlichen Problem.
Die Formeln in 'Angebotshistorie' funktionieren aber eben nur für genau diesen Angebotsvergleich mit diesem Namen. von den 4 Formeln die da stehen, denke ich das ich mit der untersten G15 am weitesten bin, diese wäre also relevant.
Da ja, wie gestern beschrieben,sich bei den Angebotsvergleichsmappen nur der alphanumerische Teil unterscheidet also 1A, 1B, 1C, usw. soll diese Formel danach suchen können.
Bei einem Treffer in der jeweiligen Mappe, in der Tabelle 'Angebotsvergleich Prototypen' in D8:D13 das Suchkriterium (Lieferantenname) finden und dessen Gesamtkosten ausgeben.
Da das ganze dann als Matrixformel gedacht ist soll die nächste Zeile dann wissen, das "40099-000-1A_AP" durchsucht und gefunden wurde und den Wert im Angebotsvergleich 1B ausgeben falls vorhanden.

So, wieder ne Wall of Text aber ich hoffe, dass ich es diesmal hinbekommen hab mein Problem zu erläutern.

Nochmal vielen Danke für deine Geduld und Grüße

Flo


  

Betrifft: AW: Sverweis mit variabler Matrix und Zähler von: Klexy
Geschrieben am: 09.07.2015 15:19:33

Ich fasse mal zusammen:
1. Die Sache funktioniert nur, wenn die beiden Dateien 40099-000-1A_AP.xlsx und 40099-000-1A_Lieferant1.xlsm heißen.
2. Beide Dateien müssen gleichzeitig geöffnet sein.
3. In 40099-000-1A_Lieferant1.xlsm kommt es nur aufs Blatt Stückkosten 1 an
4. Eine weitere zu vergleichende Datei müsste 40099-000-1A_Lieferant2.xlsm heißen und sinnvollerweise im Blatt Stückkosten 1 andere

Stückzahlen enthalten

Fragen bzw. Kritik:
1. Warum sind im Blatt Stückkosten 1 jeweils 2 Zeilen verbunden? Das macht es unnötig kompliziert. Und bei den Einmalkosten 4 Zeilen!
2. Ich stelle grade fest, dass auch Stückkosten2,3 und Einmalkosten in den Vergleich mit einfließen. Das war bisher geheim.
3. Deine Formeln in den Vergleichsblättern sind unnötig kompliziert, alle einzeln von Hand getippt und können nicht einfach runtergezogen werden, wie man das normalerweise mit Formeln in einer Spalte machen können sollte. Siehe mein voriger Beitrag.
4. Wenn du 6 Lieferanten vergleichst, warum verteilst du die dann auf 3 Blättern und schreibst sie nicht alle in 1 Blatt nebeneinander? Das macht es unnötig kompliziert.
5. Wenn die Einmalkosten auch in die Liste sollen, warum hat diese Tabelle dann eine andere Spaltenanordnung? Das macht es unnötig kompliziert
6. Warum sind in den Stückkosten die Spalten "Position", "Beschreibung" und "Kosten" jeweils über 2 Spalten verbunden? Das macht es unnötig kompliziert
7. Die Formel in der Angebotshistorie kann sich gar nicht auf 1A, 1B, 1C beziehen, denn sie bezieht sich nur auf die Datei 40099-000-1A_AP. Die Angebote aus dem Zyklus 1B und 1C stehen in eigenen AP-Dateien. Um die AP-Dateien miteinander zu vergleichen, brauchst du eine weitere Datei.
8. In dieser weiteren AP-Vergleichsdatei musst du auch die Liste der möglichen Lieferantennamen hinterlegen, damit die in dem Verweis gefunden werden.
9. Die Positionsnummern in den Stückkosten sind keine Zahlen, sondern Text. Das geht gaaaar nicht! Das macht die Sache extrem kompliziert.

Überarbeite mal deine Datein im Hinblick auf diese ganzen unnötigen Verkomplizierungen. Dadurch vereinfachst du die Datengrundlage, was wiederum zu einer tragbaren Lösung für dein Problem führen könnte.

Hier ein Beispiel dafür, wie z.B. die AP-Datei aussehen sollte:
https://www.herber.de/bbs/user/98738.xlsx
hellrosa sind neu formulierte Formeln
hellblau sind händisch eingetragene Werte (was beim enmaligen Eintrag kein Problem ist)
Oben bei den Gesamtkosten und Lieferterminen sind auch neue Formeln, die ich aber nicht rosa gemacht habe.

Nochwas grundsätzliches:
1. Keine Deko-Leerzeilen oberhalb der Tabellen! Wenn du Rand brauchst für den Drucker, dann mach das mit dem Seitenlayout/Seitenränder
2. Keine verbundenen Zellen in Tabellen! Das gibt nur Probleme und du kannst nicht sortieren oder filtern.


 

Beiträge aus den Excel-Beispielen zum Thema "Sverweis mit variabler Matrix und Zähler"