Liebes Forum,
ich bräuchte Hilfe für folgende Aufgabe:
Ich habe eine größere Anzahl von Zweigstellen (> 700).
Ich weiss von jeder Zweigstelle, ob sie "geschlossen" oder "geöffnet" (Spalte B) ist.
Alle Zweigstellen sind einem Bereich/Bezirk zugeordnet und zwar durch einen entsprechenden Texteintrag in Spalte F (z.B. "HH", "B" etc.).
Ich will jetzt nur die Bezirke zählen, in denen sämtliche Zweigstellen geschlossen sind.
Die Funktion Zählenwenn(B4:B744;"geschlossen") müsste nach meiner Vorstellung also mit der weiteren Bedingung verknüpft werden, dass auch in allen anderen Zeilen, die in der Spalte F den jeweils identischen Bezirkskürzel (also Text "HH" oder "B" etc.) aufweisen, in Spalte B ebenfalls den Wert "geschlossen" aufweisen.
Ich hoffe, ich konnte mich verständlich machen und es kann mir in diesem Forum jemand helfen!
Herzlichen Dank vorab!
beste Grüße
M. Shawmut
Hi,
für deinem Fall gibt's die Fkt. ZÄHLENWENNS().
Hallo Günter,
die Funktion Zählenwenns kenne ich, im Prinzip.
Aber wie muss ich in meinem Fall die weiteren Kriterien/Bedingungen angeben, damit Excel ein "geschlossen" in Spalte B nur dann zählt, wenn auch alle anderen Zeilen/Zweigstellen mit dem identischen Bezirkskürzel (dafür gibt es fast 300 Varianten) in Spalte F ein "geschlossen" in Spalte B aufweisen???
=Zählenwenns(B4:B744;"geschlossen"; ????)
Excel müsste ja im Prinzip zunächst in Spalte B schauen, wo "geschlossen" steht, und sich dann in der entsprechenden Ziele aus Spalte F das "Bezirkskürzel" holen, um dann in einem zweiten Schritt zu prüfen, ob in allen anderen Zeilen mit genau diesem Bezirkkürzel in Spalte B ebenfalls "geschlossen" steht.
Mein Problem ist dabei auch, dass es so viele Bezirke sind (>300). Ich kann die Bezirkskürzel also nicht als konkreten Text ("HH" etc.) in die Formel übernehmen.
Herzlichen Dank vorab
Marc
Hallo Marc,
Eine abgespeckte und ggf. anonymisierte Beispieltabelle hochzuladen würde weiterhelfen.
Die Datei sollte vom Aufbau dem Original entsprechen.
Hallo Cadmus,
hier eine abgespeckte Beispiel Datei.
Im Beispiel sind 6 Zweigstellen geschlossen.
Gezählt werden sollen aber nur "vollständig geschlossenen Bereiche/Bezirke".
Das sind in dem Bsp. Plön, Salzgitter, Wolfsburg (also 3), in den anderen Bereichen ist zumindest eine Zweigstelle offen.
Bei über 700 Zweigstellen wäre es schön, die Prüfung ließe sich automatisieren
Herzlichen Dank vorab
Marc
Hallo Marc,
anbei deine Testdatei mit einer Formel um die komplett geschlossenen Bezirke zu ermitteln (ist eine Matrixformel in Zelle H2)
und einer Formel zur Auflistung der komplett geschlossenen Bezirke. (in Zelle J2:J20 ; kann bei Bedarf beliebig nach unten kopiert werden)
Die Formel überprüfen die Einträge in dem Bereich A2:D999
Probier es mal aus :19:
Viele Grüße
Georg
[
attachment=31259]
Hallo Georg,
vielen, vielen herzlichen Dank für die Hilfestellung!
Die Formel macht in der Testdatei genau das, was ich wollte (genial!). Toll ist auch die Funktion, dass die komplett geschlossenen Bereiche namentlich ausgewiesen werden.
Beim Übertrag auf meine - etwas umfangreichere - Original-Tabelle hatte ich allerdings Probleme:
In meiner Originaldatei liegen die relevanten Informationen in Spalte A und Spalte F. Ich habe die Matrix-Formel entsprechend angepasst (auch was die Zeilenbezüge betrifft).
Als Ergebnis wird mir in der Originaldatei dann aber "0" ausgegeben, was nicht stimmt. Komischerweise ändert sich an dem Ergebnis auch nichts, wenn ich in Spalte A den Status einer Zweigstelle ändere.
Mir ist nicht klar, woran das liegen könnte. In meiner Orignaltabelle habe ich den Öffnungsstatus zu zwei unterschiedlichen Stichtagen einmal in Spalte A und einmal in Spalte C aufgeführt. Erklärt das vielleicht die Fehlfunktion???
Du hattest geschrieben, die Formel durchsuche "die Einträge in dem Bereich A2:D999". Ich hatte das als Tippfehler verstanden, denn tatsächlich sucht die Formel doch nur in den Spalten A und D, oder? Oder wird auch in den Spalten dazwischen gesucht??
Besten Dank und viele Grüße
Marc
Hallo Marc,
ja, es werden nur die Spalten A und D überprüft. (Zellbereiche A2:A999 und D2:D999)
In der Beispieldatei nur den Status aus Spalte A und das Bezirkskürzel aus Spalte D.
zur Formel:
{=SUMME(($A$2:$A$999<>"")*(ZÄHLENWENN($D$2:$D$999;$D$2:$D$999)=ZÄHLENWENNS($D$2:$D$999;$D$2:$D$999;$A$2:$A$999;"geschlossen"))*WENNFEHLER((VERGLEICH($D$2:$D$999;$D$2:$D$999;0)=(ZEILE($D$2:$D$999)-1));0))}
Die Formel bildet eine Summe unter folgenden Bedingungen:
($A$2:$A$999<>"")
-> A2:A999 ist nicht leer
(ZÄHLENWENN($D$2:$D$999;$D$2:$D$999)*ZÄHLENWENNS($D$2:$D$999;$D$2:$D$999;$A$2:$A$999;"geschlossen"))
-> "Anzahl pro Bezirkskürzel" entspricht "Anzahl geschlossen pro Betriebskürzel"
WENNFEHLER((VERGLEICH($D$2:$D$999;$D$2:$D$999;0)=(ZEILE($D$2:$D$999)-1));0)
-> jedes Betriebskürzel nur einmal zählen (verhindert Duplikate)
Die geschweiften Klammern nicht manuell eingeben. Matrixformel mit Schift + Strg + Enter eingeben.
Viele Grüße
Georg