Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Anzahl der Kombinationen zählen

Anzahl der Kombinationen zählen
16.11.2017 12:12:50
Hauke-Christian
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

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Anzahl der Kombinationen zählen
16.11.2017 12:20:12
Daniel
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
AW: Anzahl der Kombinationen zählen
16.11.2017 14:51:00
Hauke-Christian
Hi Daniel,
arbeite mit der neusten Excel Version.
LG,
Chris
AW: Anzahl der Kombinationen zählen
16.11.2017 14:54:36
Hauke-Christian
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.
Anzeige
AW: Anzahl der Kombinationen zählen
16.11.2017 15:02:50
SF
Hola,
schreib die Formel mal in eine andere Zelle als F3.
Gruß,
steve1da
AW: Anzahl der Kombinationen zählen
16.11.2017 15:09:18
Hauke-Christian
Auch das habe ich bereits versucht, mit dem gleichen Ergebnis.
AW: Anzahl der Kombinationen zählen
16.11.2017 15:10:43
SF
Hola,
klappt bei mir super. Zeig doch mal eine Beispieldatei mit der Formel.
Gruß,
steve1da
Ohne VBA gehts so , mit VBA genauso
16.11.2017 18:31:30
Daniel
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
Anzeige
Mit Anhängetrick
16.11.2017 12:27:21
lupo1
F3: =SUMMENPRODUKT((A$1:A$999&B$1:B$999&C$1:C$999&gt"")*(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
Da war ich wohl ein wenig doof
16.11.2017 12:51:00
lupo1
F3: =SUMMENPRODUKT(N(A$1:A$999&B$1:B$999&C$1:C$999=E3&F3&G3))
AW: alternativ eine reine Formellösung ...
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
.. , - ...
Anzeige

304 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige