Live-Forum - Die aktuellen Beiträge
Datum
Titel
05.05.2024 12:26:30
Stunden addieren
05.05.2024 11:49:03
Bedingte Formatierung, Auslesen der Adresse aus einer Zelle
05.05.2024 10:34:48
Spalte ausblenden mit VBA
05.05.2024 10:08:33
In einem Range Bereich jede 3 Zelle befüllen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1892to1896
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
1892to1896
1892to1896
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA - Gefilterte Werte transponieren

VBA - Gefilterte Werte transponieren
21.08.2022 17:35:56
Nadine
Hallo zusammen,
oft war ich stiller Leser der Beiträge, jetzt habe ich selbst ein großes Anliegen.
Ich habe folgendes Problem (Ich probiere es verständlich zu schreiben. Sorry, falls es mir nicht gelingt.)
Ich habe ein Worksheet (Buchhaltung), in dem ich sämtliche Informationen über Bestellungen meiner Kunden gesammelt habe. Dort sind neben Namen und Adressdaten auch die Gruppenzuordnung (Spalte G) und die bestellten Artikelnummern (Spalten H-R) vermerkt. Nun möchte ich in einem weiteren Worksheet alle Artikelnummern, die von einer (bzw. mehreren) Gruppe(n) bestellt wurden, untereinander auflisten. Im Zielworksheet (Bestellen) habe ich in den Spalten A2-4 Platz gelassen, um die Gruppen, für die die Werte kopiert werden sollen, zu definieren.
Das heißt, ich würde gerne in "Buchhaltung" die Spalte G nach den Zellen B1, C1 und D1 aus "Bestellen" filtern und von den resultierenden Zeilen die Werte aus den Spalten H-R transponiert (und ohne Leerzellen) in "Bestellen" ab Zelle A4 in Spalte A einfügen.
Optimal wäre es, wenn zusätzlich auch noch Artikel, die innerhalb der ausgewählten Gruppe mehrfach bestellt wurden, zusammengezählt werden und die Anzahl in Spalte B dann neben der Artikelnummer ausgegeben würde.
Schon einmal vielen Dank an Alle, die sich meinem Problem annehmen.
Liebe Grüße Nadine

25
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA - Gefilterte Werte transponieren
21.08.2022 17:45:23
Oberschlumpf
Moin Nadine,
du nutzt die Spalten A-R, das sind 18 Spalten! :-)
zumindest für mich sind das - ohne Datei - zu viele Informationen.
Kannst du uns bitte per Upload eine Bsp-Datei mit ausreichend vielen Bsp-Datenzeilen zeigen?
Am besten mit 2 Blättern:
- in Blatt 1 steht alles so, wie es jetzt ist
- in Blatt 2 steht alles, wie du es haben möchtest
Ciao
Thorsten
AW: VBA - Gefilterte Werte transponieren
21.08.2022 21:05:24
Eifeljoi5
Hallo
Eine Beispieldatei mit Wunschergebnis hilft den Helfern, ohne ist es wie so oft nur ein reines Rätzelspiel.
Darf Power Query genutzt werden?
AW: VBA - Gefilterte Werte transponieren
21.08.2022 21:58:48
Nadine
Hey,
danke für die Rückmeldung. Ich habe mich mal daran versucht, das möglichst runter zu brechen und eine Beispieldatei gemacht.
https://www.herber.de/bbs/user/154755.xlsx
Ich hoffe, ich habe an alles gedacht :-)
Power Query kenne ich nicht. :D
Liebe Grüße
Anzeige
Außer Konkurrenz - Formellösung...
21.08.2022 23:10:18
{Boris}
Hi,
...für xl365 Beta.
Die gewünschten Gruppen kannst Du in B1 bis J1 eintragen.
Die Formel listet alle Artikel auf - in der Nachbarspalte werden aber nur die Artikel addiert, deren Gruppe in B1:J1 eingetragen ist - andernfalls gibt es ne Null.
Die Formel z.B. in D4 eintragen. Achtung: Habe das Tabellenblatt mal nach Tab1 umbenannt, damit die Formel übersichtlicher wird.
=LET(
x;EINDEUTIG(ZUSPALTE('Tab1'!H2:R10;1));
y;NACHZEILE(x;LAMBDA(a;SUMME((ZÄHLENWENN(B1:J1;'Tab1'!G2:G10)>0)*('Tab1'!H2:R10=a))));
WAHL({1.2};x;y))
VG, Boris
AW: Außer Konkurrenz - Formellösung...
22.08.2022 00:07:59
Nadine
Hey,
danke für deine schnelle Antwort. Ich habe Tab1 durch Buchhaltung ersetzt, aber es kommt die Fehlermeldung #ÜBERLAUF!
Hast du einen Tipp, woran das liegen kann?
Liebe Grüße
Anzeige
#ÜBERLAUF!
22.08.2022 00:11:48
{Boris}
Hi,
...bedeutet, dass die Zellen, in die die Formel "hineinspillt", nicht LEER sind (oder es eventuell auch ne verbundene Zelle dazwischen gibt).
Check das mal.
VG, Boris
AW: #ÜBERLAUF!
22.08.2022 00:19:37
Nadine
Ahhh super. Ich habs gefunden. 1000 Dank dir
AW: #ÜBERLAUF!
22.08.2022 06:34:48
Luschi
Hallo Nadine,
bevor ich mir mit der Außer-Konkurrenz-Lösung von {Boris} den halben Tag vermiese. hier eine offizielle Lösung für Jedermänn*in ab Excel 2010.
- einfach in PQ Spalte entpivotiert
- diese Spalte gruppiert mit gleichzeitigem Zählen der Elemente in der Gruppe
- Gruppe wieder entfernt - und fertig
- das ist eine Art der Datenfindung unter 3 Minuten
- mal sehen, ob uns {Boris } seinen Zeiteinsatz mitteilt.
https://www.herber.de/bbs/user/154756.xlsx
Gruß von Luschi
aus klein-Paris
Anzeige
Zeit
22.08.2022 10:49:26
{Boris}
Hi Luschi,
mal sehen, ob uns {Boris } seinen Zeiteinsatz mitteilt.
Tatsächlich hat es am Längsten gedauert, die Wunschlösung nachzuvollziehen. Dachte zuerst, die Anzahl soll je Gruppe in den einzelnen Spalten B, C und D ermittelt werden (was ja sogar übersichtlicher wäre - am Ende müsste man nur noch die 3 Spalten zeilenweise addieren) - dann hab ich aber erst geschnallt, dass es nur 1 Ergebnisspalte sein soll unter Berücksichtigung der Gruppenvorgaben in B1:D1.
Wenn man das LAMBDA-Prinzip ein mal verinnerlicht hat, ist es dann gar nicht mehr so schwer. Und die neue total simple Funktion ZUSPALTE ist halt perfekt, um 2-dimensionale Daten in 1 Dimension zu bekommen, was die Weiterverarbeitung dann enorm vereinfacht.
Innerhalb LAMBDA passiert ja "nur" ein einfaches Zählen mit mehreren Bedingungen - früher mit SUMMENPRODUKT, heute halt nur mit SUMME. Dank NACHZEILE werden die Suchbegriffe halt über die Variable a einzeln an das letzte Array *('Tab1'!H2:R10=a) übergeben - und LAMBDA macht daraus dann das gewünschte Ergebnisarray.
Reines Formelschreiben daher kleiner 1 Minute. :-)
VG, Boris
Anzeige
AW: meine Feststellungen hierzu ...
22.08.2022 12:52:39
neopa
Hallo Nadine,
... da ich nur Excel 2016 im Einsatz habe kann keinen Vergleich mit dem Ergebnis der Formellösung von Boris vornehmen. nicht nachstellen.
Die PQ-Lösung von Luschi erzeugt zwar im Wesentlichen Deine Vorgabe, listet aber auch 3 Art mehr als diese. Eine Trennung nach Gruppen kann ich weder bei Dir noch im Lösungsvorschlag von Luschi erkennen.
Deinen Angaben hatte ich jedoch entnommen, dass Du wohl auch eine Filterung nach Gruppe(n) vornehmen möchtest. Oder?
Meine Lösungsvorschlag (siehe https://www.herber.de/bbs/user/154761.xlsx) ermöglicht alles über eine abschließende Pivotauswertung nach zuvor erfolgter Datenaufbereitung in PQ. Dazu muss ich in meiner Excelversion das PQ-Zwischenergebnis einem (internen) Datenmodel übergeben und danach die Pivotauswertung vornehmen. In Deiner Éxcelversion könntest Du die Pivotauswertung gleich innerhalb von PQ aktivieren.
Gruß Werner
.. , - ...
Anzeige
Formel als Screenshot
22.08.2022 15:26:24
{Boris}
Hi Werner,
hier mal der Screenshot zur Formel (Spalten A und B = händische Musterlösung von Nadine):
Userbild
VG, Boris
AW: das Ergebnislisting Deiner Formel ...
22.08.2022 19:36:24
neopa
Hallo Boris,
... scheint identisch zu sein mit der PQ-Lösung die Luschi eingestellt hat (die man auch schnell erstellen kann). Wie bereits bereits beschrieben und aufgezeigt, hab ich in meinem Vorschlag noch eine Trennung nach Gruppen vorgenommen. Aber obwohl Nadine ursprünglich von einer entsprechenden "Filterung" geschrieben hat, scheint sie derartiges nicht (mehr) zu wollen. Aber ich gehe davon aus, dass Du es in XL365 auch in Deine Formel noch integrieren könntest. Wie sähe dann die Formel aus?
Gruß Werner
.. , - ...
Anzeige
Nur die gewünschten Gruppen auflisten...
22.08.2022 20:14:02
{Boris}
Hi Werner,
...dazu würde ich die beiden Ergebnisarrays am Ende noch nach y ungleich Null filtern:
Userbild
VG, Boris
AW: danke; dazu nun noch folgendes ...
23.08.2022 15:00:19
neopa
Hallo Boris,
... wenn alle Gruppen einzeln neben- (oder evtl.. alternativ auch unter-) einander gelistet werden sollen, wäre das auch mit nur einer Formel realisierbar?
Gruß Werner
.. , - ...
Alle Gruppen einzeln...
23.08.2022 16:35:12
{Boris}
Hi Werner,
...ist schon eine kleine Frickelei. Hab erstmal alle 3 Gruppen in der Formel einzeln ausgewertet - ich habe zwar das Gefühl, dass das auch anders gehen muss, aber für den Moment ist das hier erstmal meine Lösung.
Hab noch VSTAPELN mit reingepackt, um die Überschriften direkt innerhalb der Formel mit auszugeben.
Userbild
VG, Boris
Anzeige
Hier ist das Bild "defekt" - siehe andere Antwort
23.08.2022 16:43:00
{Boris}
VG, Boris
Alle Gruppen einzeln...
23.08.2022 16:42:20
{Boris}
Hi Werner,
...ist schon eine kleine Frickelei. Hab erstmal alle 3 Gruppen in der Formel einzeln ausgewertet - ich habe zwar das Gefühl, dass das auch anders gehen muss, aber für den Moment ist das hier erstmal meine Lösung.
Hab noch VSTAPELN mit reingepackt, um die Überschriften direkt innerhalb der Formel mit auszugeben - und die Gesamtsumme wird ebenfalls ausgegeben.
Userbild
Hier die Formel:
=LET(x;EINDEUTIG(ZUSPALTE('Tab1'!H2:R10;1));gr_1;NACHZEILE(x;LAMBDA(a;SUMME(('Tab1'!H2:R10=a) *('Tab1'!G2:G10=B1))));gr_2;NACHZEILE(x;LAMBDA(a;SUMME(('Tab1'!H2:R10=a)*('Tab1'!G2:G10=C1))));gr_3; NACHZEILE(x;LAMBDA(a;SUMME(('Tab1'!H2:R10=a)*('Tab1'!G2:G10=D1)))); VSTAPELN({"Art.-Nr"."Gruppe 1"."Gruppe 2"."Gruppe 3"."Gesamt"};WAHL({1.2.3.4.5};x;gr_1;gr_2;gr_3; gr_1+gr_2+gr_3)))
bzw. besser lesbar in der Umbruchdarstellung:
=LET(
x;EINDEUTIG(ZUSPALTE('Tab1'!H2:R10;1));
gr_1;NACHZEILE(x;LAMBDA(a;SUMME(('Tab1'!H2:R10=a)*('Tab1'!G2:G10=B1))));
gr_2;NACHZEILE(x;LAMBDA(a;SUMME(('Tab1'!H2:R10=a)*('Tab1'!G2:G10=C1))));
gr_3;NACHZEILE(x;LAMBDA(a;SUMME(('Tab1'!H2:R10=a)*('Tab1'!G2:G10=D1))));
VSTAPELN({"Art.-Nr"."Gruppe 1"."Gruppe 2"."Gruppe 3"."Gesamt"};WAHL({1.2.3.4.5};x;gr_1;gr_2;gr_3;gr_1+gr_2+gr_3)))
VG, Boris
Anzeige
Bereiche noch Variablen zuweisen...
23.08.2022 16:52:36
{Boris}
Hi Werner,
...macht das Ganze flexibler und verkürzt die Formel auch nochmal:
=LET(
v;'Tab1'!H2:R10;
w;'Tab1'!G2:G10;
x;EINDEUTIG(ZUSPALTE(v;1));
gr_1;NACHZEILE(x;LAMBDA(a;SUMME((v=a)*(w=B1))));
gr_2;NACHZEILE(x;LAMBDA(a;SUMME((v=a)*(w=C1))));
gr_3;NACHZEILE(x;LAMBDA(a;SUMME((v=a)*(w=D1))));
VSTAPELN({"Art.-Nr"."Gruppe 1"."Gruppe 2"."Gruppe 3"."Gesamt"};WAHL({1.2.3.4.5};x;gr_1;gr_2;gr_3;gr_1+gr_2+gr_3)))
VG, Boris
AW: schon interessant und gut zu wissen ...
23.08.2022 16:55:06
neopa
Hallo Boris,
... ich geh mal davon aus, dass die Formel sich nicht nur nach unten sondern auch nach rechts spillt. Auch nehme ich an, dass man SORTIEREN() noch integrieren kann, um die die Datenausgabe noch nach der Art.-Nr zu sortieren.
Trotzdem würde ich mich dann später mal (auch bei Vorhandensein von XL365) doch für eine PQ-Lösung (mit Pivotauswertung) entscheiden würde, weil ich diese schneller erstellt habe und diese zudem im Ergebnis noch gefiltert und sortierte werden kann. Aber versuchen würde ich es da schon auch, eine solche Formel aufzustellen.
Ich danke Dir.
Gruß Werner
.. , - ...
Anzeige
Den Spill-Bereich...
23.08.2022 17:00:20
{Boris}
Hi Werner,
...siehst Du im Bild anhand der blauen Zellumrandung. Das ist nur EINE Formel in D3.
Sortieren ist das kleinste Problem:
Die Variable x wird dann so befüllt:
x;SORTIEREN(EINDEUTIG(ZUSPALTE(v;1)))
Ob so eine Formel "vernünftig" ist - darüber lässt sich trefflich streiten. Auf der Habenseite für die Formel steht halt, dass NIX mehr gemacht werden muss - kein Klick oder sonst was.
Muss aber jeder selbst entscheiden.
VG, Boris
Und wenn...
23.08.2022 17:01:59
{Boris}
Hi Werner,
...die Ausgangstabelle auch noch als intelligente / strukturierte Tabelle formatiert ist, dann passt sich die Formel natürlich auch automatisch an die Tabellengröße an.
VG, Boris
AW: alles kar, danke owT
23.08.2022 17:14:38
neopa
Gruß Werner
.. , - ...
AW: VBA - Gefilterte Werte transponieren
22.08.2022 16:39:17
Daniel
Hi
Beispielsweise so (sicher optimierbar)
1. die Liste der Artikel ohne Dopplungen bekommst du, wenn sie Sortiert sein dürfen und es sich um Zahlen handelt mit dieser Formel in A4, diese Forme spillt automatisch nach unten

=EINDEUTIG(KKLEINSTE('Buchhaltung - Ausgangspunkt'!H2:R10; SEQUENZ(ANZAHL('Buchhaltung - Ausgangspunkt'!H2:R10))))
2. die Gruppen bekommst du mit dieser Formel in B1, auch diese spillt nach rechts:

=MTRANS(EINDEUTIG('Buchhaltung - Ausgangspunkt'!G2:G10))
3. die Berechnung machst du dann mit dieser Formel in B4

=SUMMENPRODUKT((B$1='Buchhaltung - Ausgangspunkt'!$G$2:$G$10) *('Buchhaltung - Ausgangspunkt'!$H$2:$R$10=$A4))
Ob es jetzt eine Möglichkeit gibt, die "Ausbreitung" an den Autospill von A4 und B1 anzupassen, weiß ich nicht, ich meine das 365 das kann, kenne aber die Syntax nicht.
Ansonsten die Formel soweit nach rechts und nach unten ziehen, wie Daten da sind
Gruß Daniel
Liest Du eigentlich auch....
22.08.2022 20:26:15
{Boris}
Hi Daniel,
Ob es jetzt eine Möglichkeit gibt, die "Ausbreitung" an den Autospill von A4 und B1 anzupassen, weiß ich nicht,
....andere Antworten?
Oder Du reagierst mal auf Antworten auf Deine Fragen? https://www.herber.de/forum/messages/1894894.html
Du hast ja echt was drauf - ich lese Deine Beiträge stets sehr gerne - aber ggü. anderen (Antwortern) hast Du oft die Scheuklappen auf...
VG, Boris
AW: Liest Du eigentlich auch....
23.08.2022 14:06:51
Daniel
Manchmal habe ich auch besseres zu tun als das Forum hier nach neuen Beiträgen in alten Threads zu über wachen.
Die Frage war auch eher rethorisch als Gedankenanstoss gemeint, daher hatte ich den Threads nicht mehr auf dem Radar, ich war ja auch nicht der Threaderöffner.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige