Excel クエリの値の日付範囲

Question

Excel ドキュメントをクエリして、値の出現回数を調べ、条件付き書式とヒート マップを適用して、日付中にリソースがビジー状態になった回数を判断できるようにしたいと考えています。

個々のセルにJan1-Dec31があり、次のように3つの列に分割されたデータをクエリする必要があります。

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

数式では、列 A の開始日と列 B の終了日を使用し、列 C のリソース名の出現回数をカウントする必要があります。

したがって、出力は次のようにヒート マップに入力されます。

        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

各リソースに対して 365 個の数式を作成しなくても拡張できれば、それは素晴らしいことです。

Answer 1

私考えるあなたの質問は正しく理解しています。私が解釈したところによると、あなたは「ヒートマップ」入力を拡張する方法を探しているようですが、私の解釈が間違っているかもしれません。間違っていたら教えてください。

これは、SUMPRODUCT が優れている典型的な例です。

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

これをコピーして上下に動かすことができます (画像を参照)。実際のデータに合わせて範囲を編集する必要があることは明らかです。

仕組みは次のようになります。Bob の 1/1/2016 を考えてみましょう。日付を取得して開始日と比較し、日付が開始日配列の開始日より大きいか等しいかに基づいて true/false の配列を作成します。次に、終了日配列の終了日より小さいか等しいかに基づいて同じことを行います。次に、Bobs のリソース配列をチェックします。最後に、3 つの配列ができます。

{TRUE、FALSE、FALSE} * {TRUE、TRUE、TRUE} * {TRUE、FALSE、TRUE} -> {TRUE、FALSE、FALSE}。 * は AND 演算子なので、TRUE、TRUE、TRUE が出現する場所はすべて 1 になり、FALSE が出現する場所は 0 になります。次に、TRUE、FALSE、FALSE 配列を 1、0、0 に強制変換し、結果を合計します。

編集: コメントで話し合っている内容に対処する方法は次のとおりです。