Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
940to944
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
940to944
940to944
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Sortierung bez. SVERWEIS und Rang mit Macro

Sortierung bez. SVERWEIS und Rang mit Macro
18.01.2008 21:24:52
Martin
Hallo Leute!
Ich komm heut mit einem etwas komplexeren Problem zu euch. Ich denke aber, dass das mit VBA nicht soo schwer zu lösen ist. Nur mit Formeln ist es meines erachtens viel zu kompliziert.
Es geht um folgendes:
Ich habe ein Excel File in dem in einem Blatt Werte aus einem Fakturierungsprogramm stehen. Dies sind fixe Werte die im nachhinein nicht mehr verändert werden. Diese Tabelle beinhaltet:
Leistungsgruppen, Positionen, Beschreibung, Menge, Lohnkosten, Materialkosten und noch ein paar andere Spalten, die aber nicht so wichtig sind. Sortiert ist das Tabellenblatt schon nach Leistungsgruppen.
Es gibt nun ein zweites Tabellenblatt indem zusätzliche Positionen aufgeführt werden. Es ist prinzipiell gleich aufgebaut wie das vorher beschribene.
Diese Beiden Tabellenblätter sollen nun aber so ausgewertet werden, dass ich in einer Spalte die Positionen (bzw. der Text) der jeweiligen Leistungsgruppe aufgelistet werden.
Ich hab mal ein File upgeloadet um das Problem Vereinfacht darzustellen.
https://www.herber.de/bbs/user/49178.xls
Es besteht aus 3 Tabellenblättern. Faktura (Hier werden die Daten mittel copy/paste direkt vom Programm eingefügt), Zusatz (Hier werden zusätzliche Positionen manuell eingegeben), Auswertung (Hier sollen dann die ersten beiden Blätter ausgewertet werden)
Ihr werdet sehen, dass im Tabellenblatt Faktura in der Spalte Kosten manche Werte mit 0 ausgeschrieben sind. Wenn es möglich ist sollten diese nicht in die Auswertung mit aufgenommen werden.
Ich habe es geschafft das Problem mit Rang und Sverweis Funktionen zu lösen, aber eine Eingabe in der Mappe dauert ewig da es bis zu 1000 Positionen aus Faktura und bis zu 500 Positionen aus Zusatz geben kann und Sverweis bei jeder eingabe neu berechnen muss. Das ausschalten der Berechnung ist leider auch nicht möglich, da ich immer wieder Berechnungen benötige, die nicht immer mit F9 duchgeführt werden können.
Ich hoffe ihr könnt mir helfen, oder mir zumindest einen denkanstoss geben. Bin allerdings in VBA eine ziemliche Niete. Wenn möglich wäre eine kurze Erklärung super!
Ich weiss das ist etwas komplex, aber eventuell hat ja jemand eine nicht zu komplizierte Lösung hierführ.
Danke im Vorraus!
lg
Martin

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sortierung bez. SVERWEIS und Rang mit Macro
18.01.2008 21:54:00
Daniel
Hi
irgendwie blick ich dein Problem nicht.
Warum kann ich die von dir beschriebene SVERWEIS-Formel nicht finden, um deine bisherige Lösung, die ja anscheinend funktioniert, aber nur etwas langsam ist.
Prinzipiell kann man sagen, daß die SVERWEIS-Funktion eine sehr langsame Funktion ist, wenn mit unsortierten Daten gearbeitet wird und der 4. Parameter = 0 ist.
Gradezu Raketenmässig geht die Post ab, wenn du den SVERWEIS mit 4. Parameter = 1 verwendest.
das geht aber nur, wenn 2 bedingungen erfüllt sind:
1. die SVERWEIS-Matrix muss nach der 1. Spalte sortiert sein
2. dir muss klar sein, daß jetzt keine Fehlermeldung mehr kommt, wenn der Suchbegriff nicht gefunden wird, sondern als Ergebnis das des Nächstkleineren wertes.
das kann man aber über folgende Formel abfangen (prinzip-darstellung)

= wenn (Sverweis(Suchbegriff; Suchmatrix;1;1)=Suchbegriff;Sverweis(Suchbegriff;Suchmatrix; Ergebnisspalte;1);"Wert nicht gefunden")  


die 2 Bedingungen mögen etwas einschränken und u.U. einige Umstrukturierungen in den Daten erfordern, aber der Performancegewinn gegenüber einer SVERWEIS-Lösung mit unsortierten Daten ist ennorm und bei grossen Datenmengen den Aufwand auf jeden Fall wert.
Gruß, Daniel

Anzeige
AW: Sortierung bez. SVERWEIS und Rang mit Macro
18.01.2008 22:41:00
Martin
Hallo Daniel!
Danke für deine Antwort!
Ich weiss das Beispiel ist ein bisschen schwierig zu verstehen und auch schwierig zu erklären.
Die sverweis kannst du deshalb nicht finden weil ich die datei für das forum gemacht hab. die auswertung würde noch komplizierter zu verstehen sein. aber ich versuchs trotzdem mal. ich habe mit einer mischung aus RANG und SVERWEIS gearbeitet und jeder position einen key von 1 - 1500 zugewiesen.
ich habe es nicht geschafft mit der SVERWEIS sofort den richtigen text in der richtigen reihe zu bekommen sondern nur über einen weiteren zwischenschritt.
die erste funktion sieht so aus:

WENN(ODER(SVERWEIS(KKLEINSTE($D$7:$D$706;$N7);$D$7:$F$706;3;FALSCH)O$6;O$6="");"";KKLEINSTE($D$7:$D$706;$N7))


hier überprüfe ich mitt der ersten wenn funktion ob es sich bei der gesuchten position um die richtige leistungsgruppe handelt. wenn nicht, dann "", sonst soll er mir als verweis den kkleinsten des KEY Wertes angeben. das mache ich dann mit allen 1500 werten.


=WENN(ISTFEHL(SVERWEIS(KKLEINSTE(P$7:P$506;$BY7);$D$7:$H$706;5;FALSCH));""; SVERWEIS(KKLEINSTE(P$7:P$506;$BY7);$D$7:$H$706;5;FALSCH)) 


die zweite funktion ist nicht mehr so schlimm. ich verwende nun den key der vorher erstellten funktion und mache dementsprechend den sverweis um mir den text anzeigen zu lassen.
die kkleinste funktion als suchbegriff macht deshalb sinn da ich für meinen bereich die werte aus 2 tabellenblätter vereinen muss. ich habs nicht anders geschafft alle texte für alle leistungsgruppen wirklich untereinander stehen zu haben.
ich stelle gern mein ganzes file online, aber bis jemand versteht was ich da mache, dauert es eventuell ein bisschen und ich weis nicht ob sich das jemand antun will.
es ist mir klar dass die funktionen in dieser art dir nicht wirklich weiterhelfen werden, aber vieleicht kannst du mir ja doch die möglichkeit sagen ob und wie das mit einem macro funktioniert...
danke dir nochmals
lg
martin

Anzeige
AW: Sortierung bez. SVERWEIS und Rang mit Macro
18.01.2008 22:44:00
Martin
sorry!
die frage ist noch offen!
lg
martin

AW: Sortierung bez. SVERWEIS und Rang mit Macro
19.01.2008 00:15:00
Daniel
Hi
nur wer versteht, was du machen willst, kann dir auch ein Makro schreiben.
das einzige was ich dir raten kann, ist:
versuche deine Daten so umzustrukurieren, daß du mit dem SVerweis mit 4. Parameter = 1 (WAHR) arbeiten kannst.
der SVERWEIS mit 4. Parameter = 0 (falsch) ist ne echte Spassbremse:
Gruß, Daniel

AW: Sortierung bez. SVERWEIS und Rang mit Macro
19.01.2008 19:48:00
Martin
hallo daniel!
danke für deine antwort!
es ist möglich meine tabelle mit WAHR zu bearbeiten. allerdings bringt das performance mässig leider gar nichts.
ich wollte das file heute uploaden, allerdings hat es mit seinen drei blättern jetzt schon über 7MB und das understützt der server verständlicherweise nicht.
hab beschlossen das ganze in access zu machen. ist zwar ein bisschen komplizierter aber die berichte kann ich dann auch noch gut verwenden.
danke trotzdem
martin
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige