PDA

Vollständige Version anzeigen : Countifs Schleife über komplette Spalte, Ausgabe in dazugehörige Spalte


Ondolfa
14.07.2012, 08:25
Hallo liebe Excelgemeinde,
Ich habe ein großes Problem und hoffe mir kann jemand helfen.
Ich habe eine Exceldatei über mehrere Tabellenblätter. 16 davon enthalten Kundendaten über die Jahre 2005 bis 2020(bzw werden monatlich mit den Daten gefüllt). Die Namen sind jeweils „Cust Data+JAHR“, also zum Bsp. „Cust Data2012 oder wie im unteren Beispiel „Tabelle14“ für 2012, wenn man ein Makro aufnimmt.
Ich habe ein Makro, dass jeweils die erste Spalte in allen ausliest(Kundennnummern), die Duplikate entfernt und diese in die erste Spalte von „Tabelle 2“ schreibt.
Nun möchte ich hier für jedes Jahr pro Tool und Monat auslesen wie oft welche Kundennummer vorkommt. Aufgezeichnet sieht das wie folgt aus, würde man einfach nur die Formeln kopieren:

ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Tabelle14[SP - Sold to Party],Tabelle2!RC[-1],Tabelle14[Purchase Order Type],""WEB"",Tabelle14[MONTH],""1"")"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B256"), Type:=xlFillDefault

Nun ist mein Problem, dass mir bei dieser Art und Weise komplett Excel hängen bleibt, weil es einfach zuviel Rechenleistung braucht. Was vor allem auch daran liegt, dass ich nicht konkret weiß wieviele Zeilen gebraucht werden, da immer Kundennummern dazukommen können. Ich habe es auch mit Pivot versucht, aber über alle Blätter benötigt es zuviel Leistung und wenn ich Pivots für alle einzeln erstellen würde hätte ich wieder nicht alle Kundennummern, sondern immer nur die, die im jeweiligen Jahr vorkommen. Ich benötige diese aber um später kumulierte Anzahlen über die letzten 2 Jahre herauszugeben, d.h. es kann auch mal von Mai2010 bis Mai 2012 sein.
Nun hatte ich an eine Schleife gedacht, dass ich für jedes Jahr ein Makro hab, welches die Anzahlen für den jeweiligen Monat des konkreten Jahres für das Tool ausgibt. Die Monate stehen als 1-12 im Spaltenkopf. Die Kundennummern wie gesagt in der ersten Spalte „Tabelle2“.
Da ich allerdings lediglich in der Lage bin Codes anzupassen, jedoch meine Programmierfähigkeiten nicht so weit reichen so etwas selbst zu schreiben wollte ich fragen, ob mir vielleicht irgendwer Hilfestellung geben kann? Tausend Dank schonmal im Voraus!

P.S. Eine weitere Frage von mir wäre, ob das überhaupt möglich ist oder ob das immer den Rechner hängen lässt?

josef e
14.07.2012, 10:37
<div style="width:98%; margin-left:5px; margin-right:15px; text-align:justify;">
Hallo ?,

Beispieldatei mit Beschreibung?


</div>

<div style="width:100px; text-align:center; color:white; font-style:italic; font-size:0.8em; font-family:Tahoma; background-color:royalblue;margin-left:5px; margin-top:15px; padding:4px; border:3px double darkblue;">&laquo; Gru&szlig; Sepp &raquo;</div>

Ondolfa
16.07.2012, 01:38
Hallo Sepp,
entschuldige bitte, ich dachte die Beschreibung wäre ok.

Ich stelle jetzt erstmal mein Ursprungsproblem dar und dann wie ich dachte es zu lösen.

In meiner Datei habe ich erstmal das Sheet Customer Data 2010 (im Original 2005-2020). Hier importiere ich immer aktuell jeden Monat neue Kundendaten. Die Tabellen sind alle gleich aufgebaut. In Spalte A befinden sich die Kundennummern, in Spalte I welches Tool die jeweiligen Kunden genutzt haben und in R und S befinden sich Monat und Jahr.
Dabei kann ein Kunde pro Monat dasselbe Tool mehrfach genutzt haben und taucht deshalb mehrfach auf.

Meine Aufgabe ist es dynamische Charts zu erstellen, in denen automatisch die kumulierten Anzahlen der Kundennummern für die letzten 24 Monate aufgelistet werden. Also z.B. von Mai 2010 bis Mai 2012. Dies soll sich automatisch mit dem monatlichen Import der Kundendaten aktualisieren. Dabei soll jedoch dieselbe Kundennummer pro Monat nur einmal gezählt werden.

Mein Ansatz war es nun, zuerst einmal herauszufinden welche unterschiedlichen Kundennummern es gibt. Da eine Kundennummer in einem Jahr auch gar nicht vorkommt brauchte ich eine Liste aller Kundennummern, um die kumulierten Werte zu erstellen. Dabei mussten natürlich Duplikate bereinigt werden.
Dies habe ich mit der folgenden Formel gemacht:

Option Explicit

Sub CopyAndRemoveDups()

Application.ScreenUpdating = False

Dim oD As Object, rngC As Range
Set oD = CreateObject("Scripting.Dictionary")

With Sheets("Cust Data2010") '2.Tab lesen
For Each rngC In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not oD.exists(rngC.Value) Then
oD.Add rngC.Value, rngC.Value
End If
Next
End With
With Sheets("Cust Data2011") 'erste Tab lesen
For Each rngC In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not oD.exists(rngC.Value) Then
oD.Add rngC.Value, rngC.Value
End If
Next
End With
With Sheets("Cust Data2012") '2.Tab lesen
For Each rngC In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not oD.exists(rngC.Value) Then
oD.Add rngC.Value, rngC.Value
End If
Next
End With


'Daten eintragen
Sheets("Tabelle2").Cells(3, 1).Resize(oD.Count) = WorksheetFunction.Transpose(oD.keys)

Application.ScreenUpdating = True
End Sub


Die Kundennummern werden dann in Spalte A der Tabelle 2, von Duplikaten bereinigt, geschrieben. Es handelt sich wohlgemerkt um tausende von Nummern.

Dann war mein Plan, für jeden Monat jeden Jahres auszulesen, ob die jeweilige Kundennummer für das jeweilige Tool existiert. In Tabelle2 habe ich das beispielhaft für Tool1 realisiert. Hier habe ich COUNTIFS verwendet, da die Kriterien DIESE Kundennummer, dieses Tool und dieser Monat ausgelesen werden müssen. Theoretisch könnte man auch das Jahr auslesen, allerdings war das nicht nötig da jedes Jahr sein eigenes Sourcesheet hat.

Dann hätte ich eine Abfrage gemacht über die letzten 24 Monate wie viele Kundennummern vorkommen, d.h. ich hätte alle nichtleeren und NichtNull Felder betrachtet. D.h. z.B. für den Stichmonat Juli: In den Feldern zu den Monaten Juli 2012-Juli 2010 hätte ich geschaut welche Kundennummern vorkommen, das wäre meine kumulierte Zahl für Juli 2012 in der Grafik. Für Juni hätte ich dasselbe für den Zeitraum Juni 2012-2010 gemacht. Das hätte ich dann wohl über einen Wahrheitswert machen müssen. Aber soweit kam ich bisher gar nicht.

Jetzt wo ich es aufschreibe kommt mir das Ganze noch verwirrender vor. Ich hoffe ihr könnt ungefähr mein Ziel erfassen?

Weiterhin bin ich dann trotzdem noch nicht bei den kumulierten Werten.
Hast du da vielleicht eine Idee, die weniger umständlich ist als meine?

Vielen Dank und viele Grüsse Ondolfa

Hans Hofmann
16.07.2012, 07:51
Es ist grundübel zusammengehörige Daten über mehrere Tabellen zu verteilen.
Alle Daten in EINE Tabelle und dann als Datenbank mit Filter bzw. Spezialfilter und Pivottabellen auswerten...

Ondolfa
16.07.2012, 10:03
Ah, das hab ich verpeilt. Ich war der Meinung die Zeilenbegrenzung für Excel wäre auch für Access gültig und dazu habe ich zuviele Daten.
Dann versuchs ichs damit.
Übrigens sehr nettes Klima hier im Forum, da möchte man sich gern in eine Heizdecke hüllen.