Nach meinem Verständnis schlage ich eine Lösung vor, die etwas VBA UDF und eine Helper-Kolonne verwendet.
Ein leicht vereinfachtes Blattbeispiel ist unten angegeben. Die relevanten Daten befinden sich in den Spalten C, E, G & I. Rechts neben jeder dieser Spalten befindet sich eine Hilfsspalte, die Sie gegebenenfalls ausblenden möchten.
Drücken Sie in Ihrem Arbeitsblatt zuerst ALT+ F11, um den VBA-Editor aufzurufen. Fügen Sie ein Modul aus dem Menü Einfügen ein und fügen Sie den folgenden UDF-Code (User Defined Function) ein.
Function prmarr(ParamArray arg()) As Variant Dim arr1 cnt = 0 For i = LBound(arg) To UBound(arg) cnt = cnt + arg(i).Rows.Count ' get total rows from all ranges Next i ReDim arr1(cnt) ' re dim the array for those many total rows cnt = 0 ' reuse the counter now 'create a one dimentional list of array from all of the above ranges For i = LBound(arg) To UBound(arg) For Each cell In arg(i) arr1(cnt) = cell.Value cnt = cnt + 1 Next cell Next i prmarr = arr1 ' pass this array as return parameter End Function
Beachten Sie, dass dies ein sehr einfacher VBA-Code ist und dass im Code keine Validierungen oder Fehlerüberprüfungen enthalten sind. Wenn Sie ein horizontales Array oder überlappende Arrays oder mehrdimensionale Arrays übergeben, kann dies fehlschlagen. Es wird davon ausgegangen, dass Sie nur ein Spaltenarray übergeben, um ordnungsgemäß zu funktionieren.
Diese Funktion nimmt eine variable Anzahl von Spaltenfeldbereichen auf und gibt ein eindimensionales Feld zurück, das alle Zellwerte enthält, aus denen die Gesamtzahl der Vorkommen des aktuellen Werts seit der Startzelle ab der ersten Datenspalte gezählt wird.
Da in Ihrem Excel ein VBA-Code enthalten ist, müssen Sie die Datei als .XLSM Macro Enabled Excel-Arbeitsblatt speichern.
Setzen Sie in D1 die folgende Formel und ziehen Sie sie bis zu den beabsichtigten Zeilen nach unten.
=COUNTIF($C$1:C1,C1)
Nun, während Sie durch nachfolgende Helfer-Spalten fortschreiten. Jede Helper-Spalte erfordert eine geringfügige Änderung der Formel. Obwohl die Struktur gleich bleibt, nimmt die Anzahl der Argumente zu.
Geben Sie in F2 die folgende Formel ein und drücken Sie CTRL+ SHIFT+ ENTERinnerhalb der Formelleiste, um eine Array-Formel zu erstellen. Excel wird die Formel jetzt in geschweifte Klammern einschließen, um anzuzeigen, dass es sich um eine Array-Formel handelt. In diesem Schritt ist das Erstellen einer Array-Formel erforderlich, andernfalls führt dies zu einem falschen Ergebnis.
=SUM(IF(prmarr(C$1:C$9,E$1:E1)=E1,1,0))
Verstehe diese Formel. Sie übergeben C1: C9 und E $ 1: E1 als Parameter an die UDF, dh vorherige Spalte (n) + erster Spaltenwert bis zum Testbedingungswert und prüfen, ob eine Übereinstimmung mit der aktuellen Zelle besteht. Wenn ja, wird SUMME die Gesamtzahl dieses Werts seit Beginn der ersten Spalte erzeugen. Ziehen Sie es bis zu den vorgesehenen Zeilen nach unten.
Auf ähnliche Weise wird jetzt die Formel der Formelformel in H1
=SUM(IF(prmarr(C$1:C$9,E$1:E$9,G$1:G1)=G1,1,0))
Und so weiter.
Füllen Sie dies für alle Spalten aus.
Jetzt kommt der Bedingte Formatierungsteil.
Wählen Sie in diesem Fall die allererste Zelle, dh C1. Gehen Sie zu Bedingte Formatierung -> Neue Regel -> Verwenden Sie eine Formel, um die zu formatierenden Zellen festzulegen.
Füge jetzt in der Regel die folgende Formel ein
=MOD(D1,4)=0
Wählen Sie die Hintergrundfarbe Ihrer Wahl aus und klicken Sie auf OK, um die Formatierung auf Zelle C1 anzuwenden.
Wenn Sie nun C1 auswählen, doppelklicken Sie auf Format Painter, und malen Sie diese Formatierung in alle anwendbaren Datenspalten.
Beachten Sie, dass.
- Excel hat möglicherweise eine Begrenzung, wie viele Parameter an eine UDF übergeben werden können. Ich bin mir nicht sicher, ob und wie es angewendet werden kann, wenn es als deklariert wird
ParamArray as Variant - Ich schlage vor, dass Sie es zunächst in einem Test-Arbeitsblatt mit Beispieldaten testen, die verschiedene Bedingungen simulieren, um eine Bestätigung zu erhalten, dass dies wie erwartet funktioniert, bevor Sie es auf Ihr Produktionsblatt anwenden.
- Wenn Sie immer noch Probleme haben oder wenn es Fehler gibt, aktualisieren Sie hier, und ich werde versuchen, das Problem zu beheben, wenn die Zeit es zulässt.
