Wir wissen, dass die Funktion ZÄHLENWENN in Excel auf mehrere Kriterien zählen kann. Es nimmt Argumente als ein paar Kriterienbereich und Kriterien an. Wir können Kriterien dynamisch ändern, indem wir die Referenz der Zelle angeben, aber wir können die Kriterienspalte nicht dynamisch ändern. Nun, nicht direkt, aber wir können. Das lernen wir im Voraus mit Excel-Formeln. Dinge in Excel erledigen, die normalerweise nicht möglich sind. Mal sehen wie.
Lernen wir am Beispiel.
Kontext
Hier habe ich die Daten der Verkäufe in verschiedenen Monaten des Jahres von unseren Verkaufsberatern vorbereitet. Sie verkaufen verschiedene Modelle unseres Produkts, die allgemein als model1, model2 usw. bezeichnet werden. Was wir tun müssen, ist die Verkäufe verschiedener Modelle in verschiedenen Monaten dynamisch zu zählen.
In Zelle I2 wählen wir den Monat. In Zelle I2 wählen wir das Modell aus. Diese Werte können sich ändern. Und die Zählung sollte sich auch ändern. Die ZÄHLENWENN-Funktion sollte nach der Monatsspalte suchen, die als Kriterienbereich verwendet wird. Dann wird in dieser Monatsspalte nach dem Modell gesucht.
Hier sind also die Kriterien und der Kriterienbereich beide variabel. Wie machen wir also eine Spaltenvariable in COUNTIFS? Hier ist, wie?
Benannten Bereich für Variablenspalte oder Kriterienbereich verwenden
Generische Formel
=ZÄHLENWENN(INDIREKT(named_range),Kriterien)
Benennen Sie zunächst jede Spalte nach ihren Überschriften. Wählen Sie dazu die Tabelle aus, drücken Sie STRG+UMSCHALT+F3 und benennen Sie die Spalten gemäß der obersten Zeile. Lesen Sie hier darüber.
Die Bereiche B3:B11, C3:C11, D3:D11 und E3:E11 heißen also jeweils Jan, Feb, März und Apr.
Schreiben Sie diese Formel in I4.
=ZÄHLENWENN(INDIREKT(I2),I3)
Wenn Sie nun den Monat in I4 ändern, wird die jeweilige Monatszählung von Model4 in I4 angezeigt.
Wie es funktioniert?
Die Formel ist einfach. Beginnen wir von innen.
INDIRECT(I2): Wie wir wissen, konvertiert die INDIRECT-Funktion die Textreferenz in die tatsächliche Referenz. Wir haben es I2 zur Verfügung gestellt. I2 enthält Apr. Da wir den Bereich E3:E11 mit dem Namen Apr haben, wird INDIRECT(I2) in E3:E11 übersetzt.
Die Formel vereinfacht zu =COUNTIFS(E3:E11,I3). ZÄHLENWENN zählt alles, was sich in I3 im Bereich E3:E11 befindet.
Wenn Sie den Monat ändern, ändert sich die Spalte dynamisch. Dies wird als ZÄHLENWENN mit Variablenspalten bezeichnet. Im GIF habe ich eine bedingte Formatierung basierend auf einer anderen Zelle verwendet.
Die Formel kann auch mit der countif-Funktion arbeiten. Wenn Sie jedoch mehrere Bedingungen haben möchten, verwenden Sie die Funktion ZÄHLENWENN.
Creative-Spaltendiagramm mit Summen
Erstellen Sie ein Overlay-Diagramm in Excel 2016
Pareto-Diagramm und Analyse in Excel durchführen
Erstellen Sie ein Wasserfalldiagramm in Excel
Excel Sparklines: Die winzigen Diagramme in Cell
Tachometer (Messgerät) Diagramm in Excel 2016