Anzahl der Kombinationen zählen


Betrifft: Anzahl der Kombinationen zählen
von: Hauke-Christian Pennekamp
Geschrieben am: 16.11.2017 12:12:50

Hallo zusammen,
ich bin mir nicht wirklich sicher, ob man soetwas in VBA bewerkstelligen kann. Fände es aber schöner so, als mit Formeln rumzubasteln.
Habe in 3 Spalten folgende Werte:
A B C D
Apfel Birne Apfel
Apfel Kirsche Birne
Apfel Birne Apfel
Birne Apfel Kirsche
Apfel Birne Apfel
Birne Apfel Kirsche
Daraus hätte ich gerne:
A B C D
Apfel Birne Apfel 3
Apfel Kirsche Birne 1
Birne Apfel Kirsche 2
Hab es schon mit Formeln versucht, aber schön ist es nicht und Kombinationen werden nicht richtig gezeigt.
Hat vllt jemand eine Idee?
LG,
Chris

Betrifft: AW: Anzahl der Kombinationen zählen
von: Daniel
Geschrieben am: 16.11.2017 12:20:12
Hi
kann man schon mit VBA machen.
trotzdem wäre es gut, die älteste Excelversion zu kennen, auf der der Code mindestens laufen muss, damit man weiß, welche Excelfunktionen bei der Programmierung verwendet werden dürfen und welche nicht.
als dritte Möglichkeit kannst du die Tabelle auch noch mit Hilfe der Pivottabelle erstellen.
da geht das mit wenigen Mausklicks.
Gruß Daniel

Betrifft: AW: Anzahl der Kombinationen zählen
von: Hauke-Christian Pennekamp
Geschrieben am: 16.11.2017 14:51:00
Hi Daniel,
arbeite mit der neusten Excel Version.
LG,
Chris

Betrifft: AW: Anzahl der Kombinationen zählen
von: Hauke-Christian Pennekamp
Geschrieben am: 16.11.2017 14:54:36
Danke Lupo1, aber mit der Formel schreibt Excel immer nur eine 0 in die Zelle F3 und gibt eine Fehlermeldung aus. Über Summenprodukt hatte ich es schon versucht.

Betrifft: AW: Anzahl der Kombinationen zählen
von: SF
Geschrieben am: 16.11.2017 15:02:50
Hola,
schreib die Formel mal in eine andere Zelle als F3.
Gruß,
steve1da

Betrifft: AW: Anzahl der Kombinationen zählen
von: Hauke-Christian Pennekamp
Geschrieben am: 16.11.2017 15:09:18
Auch das habe ich bereits versucht, mit dem gleichen Ergebnis.

Betrifft: AW: Anzahl der Kombinationen zählen
von: SF
Geschrieben am: 16.11.2017 15:10:43
Hola,
klappt bei mir super. Zeig doch mal eine Beispieldatei mit der Formel.
Gruß,
steve1da

Betrifft: Ohne VBA gehts so , mit VBA genauso
von: Daniel
Geschrieben am: 16.11.2017 18:31:30
Hi
die gewünschte Umwandlung kannst du von Hand so machen:
1. Sortiere nach Spalte A, B, und C, so dass gleiche Kombinationen direkt untereinander stehen.
2. in die Zelle D1 kommt diese Formel, welche du bis zum Datenende runterziehst:
=WENN(A2&B2&C2=A1&B1&C1;D2+1;1)
diese Formel zählt die aufgelaufenen gleichen Zeilen für die Spalten A-C. Das Ergebnis steht in der jeweils ersten Zeile einer Gruppe
3. kopiere die Spalte D und füge sie an gleicher Stelle als Wert ein
4. führe mit der Tabelle die Funktion DATEN - DATENTOOLS - DUPLIAKETE ENTFERNEN aus, mit den drei Spalten als Kriterium
so kann man die Tabelle von Hand sehr schnell in die gewünschte Form bringen.
Durch die Sortierung kann man die Anzahl mit einfachen und schnellen Formeln machen, so dass das auch mit sehr großen Datenmengen funktioniert.
Auch das Duplikate-Entfernen kann sehr große Datenmengen verarbeiten.
Wenn man ein Marko haben will, lässt man es einfach diese Schritte ausführen:

Sub test()
Dim sp As Long
With ActiveSheet.UsedRange
    .Sort key1:=.Cells(1, 1), order1:=xlAscending, _
          key2:=.Cells(1, 2), order2:=xlAscending, _
          key3:=.Cells(1, 3), order3:=xlAscending, Header:=xlGuess
    With .Columns(.Columns.Count + 1)
        .FormulaR1C1 = "=IF(R[1]C1&R[1]C2&R[1]C3=RC1&RC2&RC3,R[1]C+1,1)"
        .Formula = .Value
    End With
    .EntireRow.RemoveDuplicates Array(1, 2, 3), xlGuess
End With
    
End Sub

Gruß Daniel

Betrifft: Mit Anhängetrick
von: lupo1
Geschrieben am: 16.11.2017 12:27:21
F3: =SUMMENPRODUKT((A$1:A$999&B$1:B$999&C$1:C$999>"")*(LÄNGE(WECHSELN(WECHSELN(WECHSELN(A$1:A$999&1&B$1:B$999&2&C$1:C$999&3;E3&1;);F3&2;);G3&3;))=0))
C3:E3 enthalten die gesuchten Einträge

Betrifft: Da war ich wohl ein wenig doof
von: lupo1
Geschrieben am: 16.11.2017 12:51:00
F3: =SUMMENPRODUKT(N(A$1:A$999&B$1:B$999&C$1:C$999=E3&F3&G3))

Betrifft: AW: alternativ eine reine Formellösung ...
von: ... neopa C
Geschrieben am: 16.11.2017 21:54:10
Hallo Chris,
... mit zwei Matrixfunktion(alität)sformel die keines spez. Formelabschluss wie eine klassische Matrixformel benötigen. Formel H2 (nach unten kopieren) ist in Prinzip die gleiche Formel wie sie Lupo zum zählen genutzt hat.
Die Formel E2 nach rechts bis G2 und diese 3 Zellen nach unten kopiert ermittelt die zu zählenden Kombinationen aus den Daten in A:C.
Allerdings ist eine solche Formellösung nicht für Massendatenauswertung (viele tausende Datensätze zu empfehlen)

 ABCDEFGH
1ApfelBirneApfel duplikatfre Kombinat.Anzahl
2ApfelKirscheBirne ApfelBirneApfel3
3ApfelBirneApfel ApfelKirscheBirne1
4BirneApfelKirsche BirneApfelKirsche2
5ApfelBirneApfel     
6BirneApfelKirsche     
7        

Formeln der Tabelle
ZelleFormel
E2=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$1:A$999)/ISTFEHLER(VERGLEICH($A$1:$A$999&$B$1:$B$999&$C$1:$C$999;$E$1:$E1&$F$1:$F1&$G$1:$G1;0)); 1))&"";"")
H2=WENN(E2="";"";SUMME(INDEX(N(A$1:A$999&B$1:B$999&C$1:C$999=E2&F2&G2); )))


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

Beiträge aus den Excel-Beispielen zum Thema "Anzahl der Kombinationen zählen"