Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 00:42:03
Listbox mit gefilterten Daten füllen
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
24.04.2024 18:49:56
merge & select
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
956to960
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
956to960
956to960
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

keine Duplikate zählen

keine Duplikate zählen
28.02.2008 09:32:00
Andre
Hallo zusammen,
kann mir einer die Formel so abändern, dass sie für meine Wünsche funktioniert.
Ich möchte gerne die Anzahl von Kunden zählen, die abgeschlossen sind.
Ich habe eine Array Formel gefunden, die ich nicht ganz verstehe, da sie nur ab Zeile 1 funktioniert,
aber damit die Kopfzeile mitzählt.
Arrayformel:
{=SUMME((VERGLEICH(A1:A10;A1:A10;0)=ZEILE(1:10)*(B1:B100))*1)}
Blaues Feld: Formel ab Zeile 1 mit -1 als Korrektur am Ende, damit wäre das Ergebniss richtig
rotes Feld: diese Eingrenzung ist richtig, aber Ergebnis ist 0!!!!
gelbes Feld: Formel ohne Korrektur -1, bringt aber falsches Ergebnis, da 3 richtig wäre

Die Datei https://www.herber.de/bbs/user/50254.xls wurde aus Datenschutzgründen gelöscht


Hat jemand eine Ahnung, wie man es besser machen könnte?
Liebe Grüsse
Andre

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: keine Duplikate zählen
28.02.2008 09:52:50
Lessie
Hi,
=SUMMENPRODUKT((VERGLEICH($A$2:$A$11;$A$2:$A$11;0)=ZEILE($A$2:$A$11)-1)*(C2:C110))
Gruss

AW: keine Duplikate zählen
28.02.2008 09:56:38
Specke
Hallo Andre,
=SUMME((VERGLEICH(A2:A11;A2:A11;0)=ZEILE(1:10)*(C2:C110))*1)
Gruß Specke

AW: keine Duplikate zählen
28.02.2008 09:59:00
Andre
Hallo zusammen,
Super für die schnelle Hilfe, hat wir sehr geholfen, vielen vielen Dank.
Gruss
Andre

{=SUMME(1/ZÄHLENWENN(A2:A11;A2:A11)*(C2:C11<>""))}
28.02.2008 11:07:01
WF
.

AW: {=SUMME(1/ZÄHLENWENN(A2:A11;A2:A11)*(C2:C11<>""))}
28.02.2008 11:22:00
Andre
Hallo WF,
bei deiner Formel kommt eine ungerade Zahl heraus z.B. 6,5?
Gruss
Andre

stimmt - und die andere Formel ist auch falsch
28.02.2008 12:04:00
WF
Hi Andre,
da muss nämlich beim ERSTEN A in C was stehen - sonst wird A ignoriert.
Ich tu was!
WF

Anzeige
AW: stimmt - und die andere Formel ist auch falsch
28.02.2008 13:07:00
Andre
Die Formel
=SUMMENPRODUKT((VERGLEICH($C$5:$C$17052;$C$5:$C$17052;0)=ZEILE($C$5:$C$17052)-4)*(I5:I170520))
funktioniert ganz gut, aber bei 17000 Zeilen braucht er schon verdammt lange. Ausserdem muss ich die Formel noch bis FU rüberziehen (120 Spalten), da die Kunden nach bestimmten Kategorien aufgeteilt werden.
Ich versuchen noch simultan über eine Pivot eine Auswertung zu machen, aber falls einer eine Formel kennt, die nicht soviel Resourcen frisst, wäre Super.
Gruss
Andre

AW: stimmt - und die andere Formel ist auch falsch
28.02.2008 13:33:38
Mag
Hi,
Ansatz mit VBA:

Option Explicit
Sub ttt()
Dim rng As Range, Bereich1 As Range, Bereich2 As Range, i As Long, z As Long
Set Bereich1 = Range(Cells(2, 1), Cells(65536, 1).End(xlUp))
Set Bereich2 = Range(Cells(2, 3), Cells(65536, 3).End(xlUp))
For i = 1 To Bereich1.Count
Set rng = Bereich1(i)
If Application.WorksheetFunction.Match(rng.Text, Bereich1, 0) = i Then
If Application.WorksheetFunction.SumIf(Bereich1, rng, Bereich2) > 0 Then z = z + 1
End If
Next i
MsgBox z
End Sub


Gruss

Anzeige
AW: stimmt - und die andere Formel ist auch falsch
28.02.2008 13:43:08
Mag
... als Function im Modul:

Option Explicit
Public Function MyAnzahl(Kunden As Range, Umsatz As Range) As Long
Dim rng As Range, i As Long
For i = 1 To Kunden.Count
If Application.WorksheetFunction.Match(Kunden(i).Text, Kunden, 0) = i Then
If Application.WorksheetFunction.SumIf(Kunden, Kunden(i), Umsatz) > 0 Then MyAnzahl =  _
MyAnzahl + 1
End If
Next i
End Function


Gruss

AW: stimmt - und die andere Formel ist auch falsch
28.02.2008 13:44:24
Mag
... Aufruf in der Arbeitsmappe:

=MyAnzahl(A2:A11;C2:C11)


AW: stimmt - und die andere Formel ist auch falsch
28.02.2008 14:19:06
Andre
Hi Mag,
ein VBA Ansatz ist auch Super, da es wahrscheinlich mit einer Formel nicht funktioniert. Da die Datenmenge zu gross ist.
Meine Frage an dich zu deinem Makro.
Die Formel =MyAnzahl(A2:A11;C2:C11) kann ich in jede Zelle schreiben, da das Makro im Modul steht, richtig? Bei meinem Beispiel hatte ich nur eine Spalte als Umsatz, da die Umsätze aber nach Kategorien
aufgeteilt ist, müsste das Makro einen grösseren Bereich berechnen.
in Spalte C sind die Kunden fest, aber die Umsatzkategorien reichen von Spalte I bis FV.
Ausserdem hat die Formel bei mir einen Fehler angezeigt #Name? als ich dein Makro ins Modul kopiert habe und in einer Zelle die Formel =MyAnzahl(A2:A11;C2:C11) eingegeben habe.
Kannst du mir noch mal helfen, vielleicht mit einem Excel file als Beispiel.
Gruss
Andre

Anzeige
AW: stimmt - und die andere Formel ist auch falsch
28.02.2008 15:25:31
Andre
Hi Mag,
vielen Dank für deine Hilfe, leider rattert sich mein Rechner tot, bei 17000 Zeilen. Kann man das Makro noch ändern, dass es effizienter läuft?
Gruss
Andre

AW: stimmt - und die andere Formel ist auch falsch
28.02.2008 16:07:38
Mag
... hmm, hatte es ausprobiert mit 17000 Zeilen und hatte nur Bruchteil einer Sekunde gebraucht; wobei, wenn mehrere Formeln in der Tabelle sind, kann es einige Sekunden dauern. Eine effektivere Variante? Stelle mal auf offen ...
Gruss

AW: stimmt - und die andere Formel ist auch falsch
28.02.2008 16:27:00
Andre
Hab ein Notebook S7020, vielleicht 2-3- Jahre alt. aber bei mir rattert er sich einen ab. Ausserdem muss ich dieselbe Prozedur im Blatt bei 120 Spalten vornehmen. Und wenn ich die Formel rüberziehe, läuft mein Rechnerauf Hochtouren und nach 10 Minuten brech ich ab.

Anzeige
AW: stimmt - und die andere Formel ist auch falsch
28.02.2008 13:49:00
Mag
... korrigiert:

Option Explicit
Public Function MyAnzahl(Kunden As Range, Umsatz As Range) As Long
Dim i As Long
For i = 1 To Kunden.Count
If Application.WorksheetFunction.Match(Kunden(i), Kunden, 0) = i Then
If Application.WorksheetFunction.SumIf(Kunden, Kunden(i), Umsatz) > 0 Then
MyAnzahl = MyAnzahl + 1
End If
End If
Next i
End Function


Gruss

Diese Formel funktioniert
28.02.2008 14:25:00
WF
Deine zweite Formel funktioniert genauso wenig wie die erste.
wenn in C5 bis C10 A steht und in i6 bis i10 ne 1 in i5 also NICHTS, wird A NICHT gezählt !!!
Und außerden gibt es #NV bei Leerzellen in Spalte C
Die funktioniert:
=SUMMENPRODUKT((VERGLEICH(C5:C17&I5:I17;C5:C17&I5:I17;0)=(ZEILE(C5:C17)-4)*(I5:I17""))*1)
Salut WF

Anzeige
AW: Diese Formel funktioniert
28.02.2008 14:32:00
Andre
Hi WF,
leider kann ich nicht mit einer Summenprodukt Formel arbeiten, da die Datenmenge zu hoch ist. Mein Rechner hängt sich auf, da 120 Spalten jeweils 17000 Zeilen berechnen muss.
Mag hat einen VBA Ansatz, der wahrscheinlich die Datenmenge beherscht. Warte noch auf eine Antwort.
Aber ich danke dir erstmal für deine Hilfe.
Gruss
Andre

AW: keine Duplikate zählen
28.02.2008 12:01:00
Jumbo
... das Problem ist bei der Formel, dass je Kunde nur geschaut wird, ob er beim ersten Vorkommen in der Liste Umsatz hatte; wenn nein, dann wird er auch nicht gezählt ...
Gruss

AW: keine Duplikate zählen
28.02.2008 10:02:00
Gert
Hallo Andre,
was repräsentiert das"Blaue_Feld"; das "Rote Feld" und das "Gelbe Feld" ?
In Deiner Tabelle kann ich nur erkennen, das 5 Zellen der Spalte "C" eine 1 beinhalten.
Dies kann mit :
=ZÄHLENWENN(C2:C11;1)
abgefragt werden, das Ergebnis =5.
Bitte mehr Details.
mfg
Gert

Anzeige
AW: keine Duplikate zählen
28.02.2008 10:40:20
Andre
Hallo,
ich habe ungefähr 17000 Zeilen, leider bringt mir die Formel von Lessie nur ein Ergebnis von 12!!!
Stösst die Formel bei zu grossen Mengen an ihre Grenzen?
An Gert,
Er soll nur zählen wenn, in Spalte C ein Wert steht. Normalerweise steht dort ein Umsatzbetrag, also mehr als eins. Ich würde gerne die Kunden zählen, bei denen ein Umsatz gelaufen ist. Der Kunde könnte mehrmals als einmal vorkommen. Aber er darf nicht doppelt zählen.
gelt
In den "Blaue_Feld"; das "Rote Feld" und das "Gelbe Feld" habe ich Formeln hinter hinterlegt, die ich gefunden bzw. abgeändert habe.
Die Formel von Gert würde funktionieren, aber bei 17000 Zeilen bringt er mir nur ein Ergebnis von 12!!! anstatt wahrscheinlich 4000. Hat jemand eine Idee ob die Formel ab ihre Grenzen stösst und eine andere Formel als Ersatz, die die Datenmenge bewältigt?
Gruss
Andre
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige