Excel-Abfragedatumsbereich für Wert

Question

Ich versuche, ein Excel-Dokument abzufragen, um die Häufigkeit des Auftretens eines Werts zu ermitteln, damit ich eine bedingte Formatierung und eine Heatmap einfügen kann, um zu ermitteln, wie oft die Ressource an einem Datum belegt ist.

Ich habe Jan1-Dec31 in einzelnen Zellen. Ich muss die Daten wie folgt auf drei Spalten aufteilen

       A       B       C
      Jan1    Jan12   Bob
      Jan2    Jan10   Roger
      Jan11   Jan14   Bob

Die Formel muss das Startdatum in Spalte A und das Enddatum in Spalte B verwenden und das Vorkommen des Ressourcennamens in Spalte C zählen.

Meine Ausgabe würde also wie folgt in meine Heatmap gelangen.

        Bob  Roger
 Jan1    1    0

 Jan2    1    1

 Jan3    1    1

 Jan4    1    1

 Jan5    1    1

 Jan6    1    1

 Jan7    1    1

 Jan8    1    1

 Jan9    1    1

 Jan10   1    1

 Jan11   2    0

 Jan12   2    0

 Jan13   1    0

 Jan14   1    0

Wenn es erweitert werden könnte, ohne 365 Formeln für jede Ressource erstellen zu müssen, wäre das großartig.

Answer 1

ICHdenkenIch verstehe Ihre Frage richtig. Ich interpretiere sie so, dass Sie nach einer Möglichkeit suchen, Ihre „Heatmap“-Eingabe zu erweitern, aber vielleicht lese ich sie falsch. Sagen Sie mir Bescheid, wenn ich mich irre.

Dies ist ein klassischer Fall, in dem SUMPRODUCT großartig ist.

=SUMPRODUCT(--($E2>=$A$2:$A$4)*($E2<=$B$2:$B$4)*(F$1=$C$2:$C$4))

Diese können Sie kopieren und nach unten verschieben (siehe Bild). Natürlich müssen Sie Ihre Bereiche entsprechend bearbeiten, damit sie zu Ihren tatsächlichen Daten passen.

Es funktioniert so. Betrachten Sie Bobs 1.1.2016. Es nimmt das Datum und vergleicht es mit dem Startdatum und erstellt ein Array von Wahr/Falsch, basierend darauf, ob das Datum größer oder gleich den Startdaten im Startdatum-Array ist. Dann macht es dasselbe, kleiner oder gleich den Enddaten im Enddatum-Array. Dann überprüft es das Ressourcen-Array auf Bobs. Am Ende haben Sie drei Arrays:

{TRUE, FALSE, FALSE} * {TRUE, TRUE, TRUE} * {TRUE, FALSE, TRUE} -> {TRUE, FALSE, FALSE}. Das * ist ein UND-Operator, also ist jede Stelle, an der TRUE, TRUE, TRUE vorkommt, eine 1 und wenn FALSE vorkommt, ist es eine Null. Dann wird Ihr TRUE, FALSE, FALSE-Array auf 1, 0, 0 konvertiert und das Ergebnis summiert!

Bearbeiten: Hier ist eine Möglichkeit, das anzugehen, worüber wir in den Kommentaren sprechen.