Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 21:51:33
Idee für Grafik
26.04.2024 21:29:07
PQ zufällige Reihenfolge
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1352to1356
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
1352to1356
1352to1356
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Datentabelle neu zusammenstellen, zuordnen

Datentabelle neu zusammenstellen, zuordnen
02.04.2014 09:46:40
KLE
Hallo zusammen,
war lange nicht mehr hier, da das basteln in Excel nicht mehr meine Kernaufgabe ist. Und die alten Dateien noch ziemlich gut laufen ;o) - Dank Euch!!!
Nun habe ich aber eine kleine Herausforderung, die ich heute noch lösen müsste und da für brauche ich EURE Hilfe!
Ich habe eine Abverkaufsliste, pro Zeile ein Artikel. Ziel ist es, pro Zeile einen Kunden zu haben und dahinter in den Spalten die jeweiligen Produkte, die er gekauft hat.
D.h. die Spaltenüberschriften müsste Excel selbst (immer wenn er ein noch nicht gefundenen Artikel findet - neue Spalte mit diesem Artikel als Überschrift) erkennen und dazuschreiben. Doch irgendwie komme ich nicht weiter, einfach zu lange nicht mehr mit VBA gearbeitet...
Die Datei selbst hat ca. 4.000 Einträge, wo Kunden bis zu 30-40 Artikel einzeln gekauft haben.
Da diese Liste zukünftig häufiger (mit aktuellen Umsätzen) kommen wird, würde ich gern diese dann als "Master" ansehen und darauf aufbauend die nächsten bearbeiten.
https://www.herber.de/bbs/user/89953.xlsx
Anbei auch eine Musterdatei (Die Daten sollten dann in das BLatt "Übersicht") - wäre super klasse, wenn ihr mir helfen könntet!
Sonnige Grüße
Kay

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

Betreff
Datum
Anwender
Anzeige
AW: Datentabelle neu zusammenstellen, zuordnen
02.04.2014 10:37:24
Oberschlumpf
Hi Kay
Und was ist, wenn Müller oder irgdn anderer Kunde Produkt A zweimal oder mehrmals gekauft hat?
Ciao
Thorsten

reine Formellösung: sortiert transformiert & ...
02.04.2014 11:28:37
der
Hallo Kay,
... auch gleich ausgewertet (Thorstens berechtigten Hinweis gleich mit verarbeitet).
Formeln A3 und B3 nach unten, Formel D1 nach rechts und Formel C2 (mit benutzerdefinierten Zahlenformat: 0;;;) sowohl nach unten und rechts kopieren sowie Bereichsnamensdefinition nicht vergessen.
Übersicht

 ABCDEF
1IDNameProdukt AProdukt BProdukt CProdukt D
22Meyer  2 
33Lehmann 11 
44Schmidt 1  
55Koschwitz1   
66Meier  1 
711Müller1  1
8      

Formeln der Tabelle
ZelleFormel
C1{=INDEX(Produkte;VERGLEICH(KGRÖSSTE(ZÄHLENWENN(Produkte;">"&Produkte); SPALTE(A1)); ZÄHLENWENN(Produkte;">"&Produkte); ))}
D1{=WENN((ZÄHLENWENN(Produkte;">"&C1&"*")=0)+(C1=""); "";INDEX(Produkte;VERGLEICH(MAX(WENN(Produkte>C1;ZÄHLENWENN(Produkte;">="&Produkte&"*"))); WENN(Produkte>C1;ZÄHLENWENN(Produkte;">="&Produkte&"*")); 0))&"")}
A2=MIN(Daten!A:A)
B2=WENN(A2="";"";SVERWEIS(A2;Daten!A:B;2;))
C2=ZÄHLENWENNS(Daten!$A$1:$A$4000;$A2;Daten!$C$1:$C$4000;C$1)
A3{=WENN(MAX(A$1:A2)=MAX(Daten!A:A); "";MIN(WENN(Daten!A$2:A$4000>A2;Daten!A$2:A$4000)))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Namen in Formeln
ZelleNameBezieht sich auf
C1Produkte=Daten!$C$2:INDEX(Daten!$C:$C;ANZAHL2(Daten!$C:$C))
D1Produkte=Daten!$C$2:INDEX(Daten!$C:$C;ANZAHL2(Daten!$C:$C))
Namen verstehen


Daten

 ABC
1IDNameProdukt
211MüllerProdukt D
32MeyerProdukt C
43LehmannProdukt C
511MüllerProdukt A
64SchmidtProdukt B
73LehmannProdukt B
85KoschwitzProdukt A
96MeierProdukt C
102MeyerProdukt C
11   


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

Anzeige
Erhalte schon in der Musterdatei Fehlermeldung,..
02.04.2014 12:02:46
KLE
Hi, und danke für Eure schnelle Hilfe!
Bräuchte wohl doch eher eine VBA-Lösung, da ich diese meist besser "modelieren" und anpassen kann wie Formeln. Habe jetzt allein schon bei der Formel in C1 (genau wie Du beschrieben) eine Fehlermeldung erhalten "#Wert".
Ja, die Anzahl sollte unten dann unter den Artikel-Spalten aufgeführt werden - nicht das Produkt als Bezeichnung. Dieses steht ja schon ob im Spaltenkopf dann...
Wäre super, wenn ihr mir hier noch weiterhelfen könntet. Werde versuchen die Formellösung zu realisieren in der Zeit. Da es aber häufiger vorkommen wird...wäre mir eine VBA-Lösung vielleicht sogar lieber.
Sonnige Grüße und DAnke!
Kay

Anzeige
Formeln sind teilweise Matrixformeln ...
02.04.2014 13:56:48
der
Hallo Kay,
... und zwar die in C1, D1 und A3 und sind deshalb wie bereits angegeben: "Umrandende { } nicht mit eingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!" auch entsprechend einzugeben.
Gruß Werner
.. , - ...

Das Problem liegt in der Namens-Vergabe mit Formel
02.04.2014 14:01:56
KLE
Hi dass hatte ich auch. Es gab nur ein Problem bei mir, dass Excel nicht die Namen annahm "Produkte". D.h. die Formeln wurden nicht verarbeitet. Hatte wohl auch noch was mit dem Format zu tun.
Jetzt klappt es mit der Musterdatei, werde es auf die Große übertragen und anpassen.
Frage: WIE kann ich wirklich die INDEX-Formel als Variable per Namen anlegen? Excel will immer nur einen Bezug zu einer fixen Zelle haben...?
Gruß und danke!
Kay

Anzeige
eingeben wie vorgegeben ...
02.04.2014 14:09:21
der
Hallo Kay,
... Deine Aussage: "Excel will immer nur einen Bezug zu einer fixen Zelle haben" ist für Standardexcel (bis XL 2010) nicht korrekt. Ich kann mir kaum vorstellen, dass Deine Version: 2013 - Office 365 das nicht mehr unterstützen sollte. Allerdings überprüfen kann ich es nicht.
Was genau passiert wenn Du einen Namen =Daten!$C$2:INDEX(Daten!$C:$C;ANZAHL2(Daten!$C:$C)) zuweist?
Gruß Werner
.. , - ...
Gruß Werner
.. , - ...

Doch eine VBA-Lösung machbar?
02.04.2014 16:25:51
KLE
Hallo,
@Werner, ich bekomme als Ergebnis "#NV" - keine Fehlermeldung oder der gleichen!
Kann mir jemand das Ziel per VBA formulieren? Das heißt, wie übertrage ich die Namen und ID's ohne Wiederholungen und laufe dann die Schleife dahingehend durch, um die Spalten zu definieren (Spaltenüberschriften) und dann die Anzahl derer, die der Kunde kaufte...
In VBA komme ich mit dem "modelieren" schneller rein und kann es auch für andere Auswertungen in der Zukunft besser gestalten. Mit den Formeln und Bezügen...scheine ich im Moment (war mal anders) irgendwie auf Kriegsfuss...;o(
Vielen Dank und sonnige Grüße
Kay

Anzeige
AW: Doch eine VBA-Lösung machbar?
02.04.2014 17:52:07
Oberschlumpf
Hi Kay
hier, versuch mal
https://www.herber.de/bbs/user/89964.xls
Zuerst wird Inhalt in Übersicht komplett gelöscht, da die Übersicht immer wieder neu aufgebaut wird.
Dann werden in Tabelle Daten alle Produkte ausgelesen und in Tabelle Übersicht jeweils 1x als Spaltenüberschrift eingesetzt.
Nun werden wieder in Daten die Kunden-IDs ausgelesen und auch wieder jeweils nur 1x, aber inklusive Name, nach Übersicht zeilenweise übertragen.
Im letzten Schritt werden in Daten die Kunden-IDs mit denen in Übersicht verglichen.
Wenn Übereinstimmung gefunden, wird in Übersicht in den Überschriften das gekaufte Produkt aus Daten gesucht.
Wenn auch das gefunden, wird in Übersicht in der richtigen Zelle der jeweils vorhandene oder auch noch nicht vorhandene Wert mit 1 addiert.
Passt es so?
Ciao
Thorsten
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige