質問: countifsが重複する値を1回だけカウントするようにする 私はテスト実行でこのすべてのデータを持っています:controller start end start ts end ts 1 1:13 1:15 1:00 1:30 1 2:08 2:25 2:00 2:30 4 4:02 4:16 4:00 4:30 4 4:17 5:35 4:00 6:00 2 4:03 5:39 4:00 6:00 各テストは特定の時間に開始および終了しますが、テストのスケジューラーは30分全体のタイムスロットを予約するため、floorとceilingで時間を埋めます。各テストは特定のコントローラーを使用し、実行中、他のテストはそのコントローラーを使用できません。このデータから、特定の期間に使用されている同時コントローラーの最大数を計算したいと思います。これを行うために、使用中のコントローラーの数が増えるのはそれだけなので、特定のテストの開始時に使用中のコントローラーの数を計算できると考えました。したがって、そのための私の式は次のとおりです。(2010の本当に素晴らしいテーブルデータ構文を使用)=countifs([start timeslot],"<="&[@[start timeslot]],[end timeslot],">"&[@[start timeslot]]) ただし、タイムスロットの丸めにより、実際にはオーバーラップしないテスト間にオーバーラップが発生するため、countifは、同じコントローラーでのテストを発生回数だけカウントします。countifsが重複する値を、一致する範囲で発生したときに1回だけカウントするようにする方法が必要です。これは、いくつかのサンプルデータと私の数式を含むexcelファイルです。 http://dl.dropbox.com/u/123900/sumproducttest.xlsx 詳細:実際に、必要な数値を計算する配列数式を作成しました。{=sum(--([start timeslot]<=[@[start timeslot]])*--([end timeslot]>[@[start timeslot]])*iferror(1/countifs([controller],[controller],[start timeslot],"<="&[@[start timeslot]],[end timeslot],">"&[@[start timeslot]]),0))} その配列式内のcountifは、現在のテストの開始時に実行されている一連のテスト内で特定のコントローラーが発生する回数の加重配列を生成します。したがって、sum関数は、コントローラーをそれぞれ1/2として使用する2つのテストをカウントし、そのコントローラーを合計で1回だけカウントします。ただし、実際のデータセットには3000行を超える行があり、配列数式によってexcelが貧弱になります。 (私の古いデュアルコアラップトップでは、countifs関数の誤報でさえ約30秒かかるため、配列の数式は1分ごとに1%計算されます; _;)
回答: このデータから、特定の期間に使用されている同時コントローラーの最大数を計算したいと思います。 excelで実行したい場合は、ピボットテーブルを使用してみませんか。これらは、次のようなデータ要約用に設計されました。(右外側の列のcountifに注意してください)ps。必要なデータグループを選択できます(開始、終了、開始ts、終了ts、.....)