John hat ein Arbeitsblatt, das Datensätze enthält, die in einem Kostenverfolgungssystem verwendet werden. Datensatznummern werden in Spalte A, Standorte in Spalte B und Kosten in Spalte C eingegeben. Nicht alle Datensätze haben einen Kostenwert in Spalte C. John möchte die Anzahl der Datensätze „mit Standort X und Kosten <> 0“ ermitteln.

Ihr erster Impuls könnte darin bestehen, eine der zum Zählen vorgesehenen Arbeitsblattfunktionen zu verwenden, z. B. CountIf. Das einzige Problem ist, dass CountIf nicht zulässt, dass zwei Bedingungen bei der Berechnung einer Lösung überprüft werden. Es gibt jedoch einige Lösungen, die Sie verwenden können, ohne dass zusätzliche Spalten oder Zwischenberechnungen hinzugefügt werden müssen.

Die erste (und vielleicht einfachste) Lösung besteht darin, die Arbeitsblattfunktion SUMPRODUCT zu verwenden. Mit dieser Funktion können Sie Daten aus einer Spalte, Zeile oder einem Array mit beliebig vielen Kriterien zählen oder summieren. Die grundlegende Syntax lautet wie folgt:

=SUMPRODUCT( (CONDITION1)  (CONDITION2)  (CONDITION3) * (DATACELLS) )

In diesem speziellen Fall können Sie die Formel folgendermaßen zusammenstellen:

=SUMPRODUCT((B2:B101="X")*(C2:C101>0))

Dies stellt zwei verschiedene Bedingungen bereit, die überprüft werden.

Zuerst werden die Zellen in Spalte B überprüft, um festzustellen, ob sie gleich „X“ sind, und dann werden die entsprechenden Zellen in Spalte C überprüft, um festzustellen, ob sie gleich 0 sind. Beide Bedingungen geben entweder True (1) oder False (0) zurück. Diese Ergebnisse werden dann miteinander multipliziert, was entweder 1 oder 0 ergibt. Die Funktion SUMPRODUCT addiert sie dann, was zu einer kumulativen Anzahl führt.

Eine andere Lösung besteht darin, eine Array-Formel zu erstellen, die die Berechnung für Sie übernimmt. Array-Formeln unterscheiden sich von regulären Formeln darin, dass sie auf einer Reihe von Zellen arbeiten und diese durchlaufen, um ein Ergebnis zu erzielen. Betrachten Sie die folgende Formel:

=(B2="X")*(C2>0)

Dies gibt einen einzelnen Wert zurück, entweder 1 oder 0. Die Formel verwendet dieselbe Grundlogik, die in der früheren Erläuterung der SUMPRODUCT-Lösung beschrieben wurde. Die beiden logischen Vergleiche geben 1 oder 0 zurück, die miteinander multipliziert werden, was zu 1 oder 0 als Antwort führt. Betrachten Sie nun die folgende Formel:

=SUM((B2:B101="X")*(C2:C101>0))

Dies sieht jetzt der früheren SUMPRODUCT-Formel sehr ähnlich, funktioniert jedoch nicht richtig als gerade Formel. Dies liegt daran, dass SUM nicht für die iterative Arbeit mit einer Reihe von Zellen ausgelegt ist. Wenn Sie diese Formel als Array-Formel eingeben (drücken Sie Umschalt + Strg + Eingabetaste, um sie einzugeben), versteht Excel, dass Sie jeden der Bereiche nacheinander durcharbeiten möchten, um die endgültige Summe zu ermitteln, bei der es sich um eine Anzahl von Datensätzen handelt die angegebenen Kriterien erfüllen.

Die verschiedenen Möglichkeiten, Array-Formeln zu verwenden, sind ein ziemlich breites Thema.

Weitere Informationen zur Funktionsweise von Array-Formeln finden Sie in anderen Ausgaben von _WordTips _ oder auf der folgenden Website:

http://www.cpearson.com/excel/ArrayFormulas.aspx

Eine dritte Option besteht darin, die Datenbankarbeitsblattfunktionen zu verwenden, um eine Zählung zurückzugeben. Mit diesen erstellen Sie eine „Kriterientabelle“ in Ihrem Arbeitsblatt, und die Funktion verwendet dann die Kriterien, um die Datensätze zu analysieren. Bei den folgenden Schritten wird davon ausgegangen, dass die Spaltenbezeichnungen für die drei Spalten RecNum, Location und Cost sind:

  1. Suchen Sie einige leere Zellen, entweder im selben Arbeitsblatt wie Ihre Datensätze oder in einem anderen Arbeitsblatt. (Für dieses Beispiel gehe ich davon aus, dass Sie die Spalten J und K verwenden.)

  2. Geben Sie in Zelle J1 das Wort Standort ein.

  3. Geben Sie in Zelle K1 das Wort Kosten ein.

  4. Geben Sie in Zelle J2 X ein.

  5. Geben Sie in Zelle K2> 0 ein. Sie haben jetzt Ihre Kriterientabelle in die Zellen J1: K2 eingegeben.

  6. Wählen Sie die Zellen J1: K2 aus.

  7. Wählen Sie im Menü Einfügen die Option Name und dann Definieren. Excel zeigt das Dialogfeld Name definieren an. (Siehe Abbildung 1.)

  8. Geben Sie den Namen Kriterien ein und klicken Sie auf OK.

  9. Geben Sie in die Zelle, in der die Anzahl der Datensätze Ihren Kriterien entsprechen soll, Folgendes ein:

=DCOUNT(B1:C101,2,Criteria)

Beachten Sie, dass das erste mit DCOUNT verwendete Argument die zweite und dritte Spalte Ihrer Datensatzliste ist. Dieses Argument enthält auch die Spaltenbezeichnungen, die erforderlich sind, damit DCOUNT die richtigen Kriterienübereinstimmungen aus der Kriterientabelle finden kann (drittes Argument).

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (2815) gilt für Microsoft Excel 97, 2000, 2002 und 2003.