Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1448to1452
1448to1452
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Auswertung mehrere Merkmale

Forumthread: Auswertung mehrere Merkmale

Auswertung mehrere Merkmale
20.09.2015 09:52:41
Gerd
Hallo!
Ich bekomme als Ergebnis eines Internetformulars folgende Excelliste:
Userbild
Nun soll letztendlich folgendes dabei heraus kommen:
Userbild
Ich komm mit viel Handarbeit (und meinen überschaubaren EXCEL-Kenntnissen) da schon hin (Text in Spalten, Firmennummer zuordnen mit VERKETTEN, alles per Hand untereinanderkopieren, mit PIVOT letztendlich auswerten). Da es sich aber um eine Liste mit manchmal bis zu 1000 Zeilen handelt, ist das mitunter recht aufwändig.
Gibt es da eine elegantere Lösung (gerne auch mit VBA)?

Anzeige

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Auswertung mehrere Merkmale
20.09.2015 10:06:32
ransi
HAllo Gerd,
MAn kann das mit etwas VBA lösen.
Mein Excel kann aber deinen Screenshot nicht verarbeiten.
Ich tippe das nicht Alles ab.
HAst du das Ganze auch als Exceltabelle ?
ransi

AW: Auswertung mehrere Merkmale
20.09.2015 10:10:18
Sepp
Hallo Gerd,
eine Beispieldatei ist immer besser als auch noch so schöne Bildchen!
Tabelle1

 ABC
1FirmennrFirmennameProdukte
2341MüllerWeissbrot, Roggenbrot, Mehrkornbrot, Semmeln, Bamberger
3193MaierMehrkornbrot, Bamberger
4253SchmidtWeissbrot, Semmeln, Bamberger
5119HuberRoggenbrot, Mehrkornbrot, Semmeln
6   
7   
8   
9Bamberger  
10Müller  
11Maier  
12Schmidt  
13   
14Mehrkornbrot  
15Müller  
16Maier  
17Huber  
18   
19Roggenbrot  
20Müller  
21Huber  
22   
23   
24Semmeln  
25Müller  
26Schmidt  
27Huber  
28   
29Weissbrot  
30Müller  
31Schmidt  
32   
33   

Formeln der Tabelle
ZelleFormel
A10=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$9&"*";$C$2:$C$5)); ZEILE(A1))); "")
A11=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$9&"*";$C$2:$C$5)); ZEILE(A2))); "")
A12=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$9&"*";$C$2:$C$5)); ZEILE(A3))); "")
A13=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$9&"*";$C$2:$C$5)); ZEILE(A4))); "")
A15=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$14&"*";$C$2:$C$5)); ZEILE(A1))); "")
A16=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$14&"*";$C$2:$C$5)); ZEILE(A2))); "")
A17=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$14&"*";$C$2:$C$5)); ZEILE(A3))); "")
A18=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$14&"*";$C$2:$C$5)); ZEILE(A4))); "")
A20=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$19&"*";$C$2:$C$5)); ZEILE(A1))); "")
A21=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$19&"*";$C$2:$C$5)); ZEILE(A2))); "")
A22=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$19&"*";$C$2:$C$5)); ZEILE(A3))); "")
A23=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$19&"*";$C$2:$C$5)); ZEILE(A4))); "")
A25=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$24&"*";$C$2:$C$5)); ZEILE(A1))); "")
A26=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$24&"*";$C$2:$C$5)); ZEILE(A2))); "")
A27=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$24&"*";$C$2:$C$5)); ZEILE(A3))); "")
A28=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$24&"*";$C$2:$C$5)); ZEILE(A4))); "")
A30=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$29&"*";$C$2:$C$5)); ZEILE(A1))); "")
A31=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$29&"*";$C$2:$C$5)); ZEILE(A2))); "")
A32=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$29&"*";$C$2:$C$5)); ZEILE(A3))); "")
A33=WENNFEHLER(INDEX($B$2:$B$5;AGGREGAT(15;6;ZEILE($A$1:$A$4)/(SUCHEN("*"&$A$29&"*";$C$2:$C$5)); ZEILE(A4))); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Sepp

Anzeige
Ist meine Lösung nicht OK? o.T.
20.09.2015 10:52:40
Sepp
Gruß Sepp

Anzeige
AW: Ist meine Lösung nicht OK? o.T.
20.09.2015 11:00:35
Gerd
Hi Sepp!
Erst einmal besten Dank
Bei deiner Formel musste ich erst kurz nachdenken.:-)
Sie kommt dem Ergebnis schon relativ nah. Das Problem ist, dass es real ungefähr 300 verschiedene Produkte und aktuell zwischen 1 bis 50 Produkte pro Firma gibt.
Wenn ich deine Lösung richtig verstanden habe, müsste ich die Produkte selbst erst einmal auflisten und vom Abstand her schon wissen, wieviel Platz ich unter jedem Produkt benötige.

Anzeige
AW: Ist meine Lösung nicht OK? o.T.
20.09.2015 11:31:50
Daniel
Hi
ja, bei einer reinen Formellösung ist das so (oder die Formel wird seeeeeehr aufwendig)
Gruss Daniel

Anzeige
AW: Auswertung mehrere Merkmale
20.09.2015 11:17:33
ransi
HAllo Gerd,
Originaldatei

 ABCDEFG
1FirmennrFimennameProdukte 341Bäcker MüllerWeissbrot
2341Bäcker MüllerWeissbrot, Roggenbrot, Mehrkornbrot, Semmeln, Bamberger 341Bäcker MüllerRoggenbrot
3193Bäcker MaierMehrkornbrot, Bamberger 341Bäcker MüllerMehrkornbrot
4253Bäcker SchmidtWeissbrot, Semmeln,Bamberger  341Bäcker MüllerSemmeln
5119Bäcker HuberRoggenbrot, Mehrkornbrot, Semmeln 341Bäcker MüllerBamberger
6    193Bäcker MaierMehrkornbrot
7    193Bäcker MaierBamberger
8    253Bäcker SchmidtWeissbrot
9    253Bäcker SchmidtSemmeln,Bamberger
10    119Bäcker HuberRoggenbrot
11    119Bäcker HuberMehrkornbrot
12    119Bäcker HuberSemmeln


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Dieser Code bereitet deine Daten auf.(aus blau mach gelb)
Option Explicit

Sub machs()
    Dim Daten As Variant, arr As Variant, out(1 To 10000, 1 To 3)
    Dim i As Integer, L As Long, lngCounter As Long
    With Sheets("Originaldatei")
        Daten = Intersect(.Range("A1").CurrentRegion, .Range("A1").CurrentRegion.Offset(1, 0))
        For L = LBound(Daten) To UBound(Daten)
            arr = Split(Daten(L, 3), ", ")
            For i = LBound(arr) To UBound(arr)
                lngCounter = lngCounter + 1
                out(lngCounter, 1) = Daten(L, 1)
                out(lngCounter, 2) = Daten(L, 2)
                out(lngCounter, 3) = arr(i)
            Next
        Next
        .Range("E1").Resize(lngCounter, 3) = out
    End With
End Sub



Die Pivotauswertung bekommst du dann alleine hin ?
ransi

Anzeige
AW: Auswertung mehrere Merkmale
20.09.2015 11:30:24
Daniel
Hi
per Makro beispielsweise so, die Daten werden wie hier angezeigt nach Spalte E geschrieben.
Sub test() Dim arr Dim dic Dim Z As Long Dim tt() As String Dim t Dim K Set dic = CreateObject("Scripting.dictionary") arr = Cells(1, 1).CurrentRegion.Value For Z = 2 To UBound(arr, 1) tt = Split(arr(Z, 3), ",") For Each t In tt t = Trim(t) dic(t) = dic(t) & "," & arr(Z, 2) Next Next Z = 0 Application.ScreenUpdating = True Columns(5).Clear For Each K In dic.keys Z = Z + 1 Cells(Z, 5) = K Cells(Z, 5).Font.Bold = True tt = Split(Mid(dic(K), 2), ",") For Each t In tt Z = Z + 1 Cells(Z, 5) = t Cells(Z, 5).InsertIndent 1 Next Next End Sub Gruss Daniel

Anzeige
So aufwendig wdn von allgemeinen UDFs ...
21.09.2015 00:27:54
allgemeinen
…getragene Formeln nun auch nicht unbedingt, Gerd,
wenn man nur Schritt3 erzeugt und den Rest der Pivot-Automatik überlässt (→ wie es ja auch Ransi gemacht hat). Eine komplette Subprozedur-Lösung lohnt sich idR dann, wenn diese Arbeit (bei stets gleichen Bedingungen!) regelmäßig getan wdn muss und nicht etwa nur einmalig ist. Anderenfalls kommt man auch mit ein paar oft nutzbaren Ergänzungen des Xl-Fktsbestands hin:
 ABCDE
1FirmennrFimennameProdukte Zeilenbeschriftungen
2341Bäcker MüllerWeissbrot, Roggenbrot, Mehrkornbrot, Semmeln, BambergerBamberger
3193Bäcker MaierMehrkornbrot, BambergerBäcker Maier
4253Bäcker SchmidtWeissbrot, Semmeln,Bamberger ,PaniniBäcker Müller
5119Bäcker HuberRoggenbrot, Mehrkornbrot, SemmelnBäcker Schmidt
6Tab1: OriginaldatenMehrkornbrot
7FirmennrFimennameProdukteTab2: Schritt3 direkt aus Originaldaten (Tab1)Bäcker Huber
8341Bäcker MüllerWeissbrot Bäcker Maier
9341Bäcker MüllerRoggenbrot Bäcker Müller
10341Bäcker MüllerMehrkornbrotMit dieser, von im Archiv vor-Panini
11341Bäcker MüllerSemmelnhandenen allgemeinen UDFs Bäcker Schmidt
12341Bäcker MüllerBambergergetragenen Lösung entfallen Roggenbrot
13193Bäcker MaierMehrkornbrotdie Arbeitsschritte 1 und 2.Bäcker Huber
14193Bäcker MaierBamberger Bäcker Müller
15253Bäcker SchmidtWeissbrotEine Sortierung von Tab2 ist Semmeln
16253Bäcker SchmidtSemmelnnur für die Tab3-Erstellung Bäcker Müller
17253Bäcker SchmidtBambergernicht erforderlich, könnte Bäcker Schmidt
18253Bäcker SchmidtPaniniaber mit zusätzl Formelauf-Weissbrot
19119Bäcker HuberRoggenbrotwand ggf auch hier erfolgen.Bäcker Müller
20119Bäcker HuberMehrkornbrot Bäcker Schmidt
21119Bäcker HuberSemmeln Tab3: Pivot-Tabelle (Schritt4)
22A8[:A20]: {=INDEX(VSplit(VJoin(WENN(ISTZAHL(FINDEN(C8;C$2:C$5));A$2:A$5;"");;-1);;1);1+ZÄHLENWENN(C$7:C7;"*"&C8&"*"))}
23B8[:B20]: {=INDEX(VSplit(VJoin(WENN(ISTZAHL(FINDEN(C8;C$2:C$5));B$2:B$5;"");"|";-1);"|");1+ZÄHLENWENN(C$7:C7;"*"&C8&"*"))}
24C8:C20: {=MTRANS(GLÄTTEN(VSplit(VJoin(C2:C5;",");",")))}

Spalte C der Tab2 enthält eine mehrzellige MatrixFml, die Spalten A u.B 1zellige MatrixFmln, deren Basis nachfolgend dargestellt wird:
 GHIJ
7Auswahlmatrix aufgespannter VektorenAuswahlindex
8Bäcker MüllerBäcker Schmidt 1
9Bäcker MüllerBäcker Huber 1
10Bäcker MüllerBäcker MaierBäcker Huber1
11Bäcker MüllerBäcker SchmidtBäcker Huber1
12Bäcker MüllerBäcker MaierBäcker Schmidt1
13Bäcker MüllerBäcker MaierBäcker Huber2
14Bäcker MüllerBäcker MaierBäcker Schmidt2
15Bäcker MüllerBäcker Schmidt 2
16Bäcker MüllerBäcker SchmidtBäcker Huber2
17Bäcker MüllerBäcker MaierBäcker Schmidt3
18Bäcker Schmidt  1
19Bäcker MüllerBäcker Huber 2
20Bäcker MüllerBäcker MaierBäcker Huber3
21Bäcker MüllerBäcker SchmidtBäcker Huber3
22G8:I8[;G9[:I21]]: {=VSplit(VJoin(WENN(ISTZAHL(FINDEN(C8;C$2:C$5));B$2:B$5;"");"|";-1);"|")
23J8[:J21]:=1+ZÄHLENWENN(C$7:C7;"*"&C8&"*")

Ein direktes Generieren der mittels Pivot-Mechanismus erzeugten Form sollte auch möglich sein, dürfte sich aber etwas aufwendiger gestalten. Ich hatte bereits daran gearbeitet, als mich deine aktuelle Lösung auf die hiermit vorliegende (einfachere) Idee brachte.
Die UDFs sind in der aktuellen Version in dieser BspDatei enthalten. Die Enumeration am ModulAnfang nicht vergessen!
Gruß, Luc :-?
Besser informiert mit …

Anzeige
Ganz ohne Pivot m.ein paar UDFs mehr, ...
21.09.2015 04:55:09
Luc:-?
…aber auch in 2 Schritten, könnte es so aussehen:
Userbild
Die zusätzlichen UDFs sind allerdings unveröffentlicht, weshalb das nur eine Demo sein kann.
Luc :-?

Auswertung mehrere Merkmale
22.09.2015 09:11:02
Gerd
Hi Leute!
Vielen Dank, dass ihr euch meinem Problem angenommen habt. Die einklick-Versionen von Daniel und Sepp sind für mich natürlich am einfachsten.
@Luc:
Wahnsinn, was mit Formeln möglich ist, allerdings ist diese Lösungsmöglichkeit gegenüber den Makros für die Anwender wohl zu Fehleranfällig. Trotzdem auch dir besten Dank.

Anzeige
AW: Auswertung mehrere Merkmale
22.09.2015 13:07:35
Peter
Hallo Gerd,
hier noch eine VBA-Lösung, in der sowohl die Brotsorten, als auch die Bäcker-Namen sortiert ausgegeben werden.
Gruß Peter
https://www.herber.de/bbs/user/100316.xlsm

Auswertung mehrere Merkmale
23.09.2015 10:51:57
Gerd
Hallo Peter!
Auch dir herzlichen Dank.
Mit den Originaldaten ausprobiert (Ergebnistabelle hat dann knapp 2000 Zeilen) scheint dieses Makro sogar am schnellsten zu laufen. :-)

Anzeige
Allerdings mit UDFs...! ;-) Gruß owT
24.09.2015 00:02:30
Luc:-?
:-?

Beliebteste Forumthreads (12 Monate)

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