Excelで土日・祝日の合計をFILTER関数を使ってスピルする方法
日々の業務で、特定の日のデータを抽出して合計する必要があることはよくあります。特に土日や祝日は、通常の営業日に比べて異なる傾向を持つことが多いため、その分析が重要です。しかし手作業で行うと時間がかかり、ミスも増えます。この記事では、ExcelのFILTER関数を使ってスピル表示を活用し、土日や祝日のデータを効率的に抽出・合計する方法を詳しく解説します。これにより、業務効率が大幅に向上し、正確な分析が可能になります。
📊 やり方の比較
| 手法 | メリット | デメリット | 推奨 |
|---|---|---|---|
| 数式(FILTER関数) | スピル機能で自動更新される, 条件指定が柔軟 | 古いExcelでは使えない, 関数の理解が必要 | ◎ |
| マウス操作(フィルタリング) | 直感的で簡単, 元データを変更しない | 手動での更新が面倒, 条件指定が複雑な場合に不向き | 〇 |
fx 使用する数式
Excel / SpreadSheet
=SUM(FILTER(売上表!C:C, (TEXT(売上表!A:A, "ddd")="土曜日" + TEXT(売上表!A:A, "ddd")="日曜日") * ISNUMBER(MATCH(売上表!A:A, 祝日リスト!A:A, 0))))💡 仕組みの解説
【数式の仕組み】この数式は、FILTER関数を使って土日や祝日のデータを抽出し、SUM関数で合計します。具体的には以下の通りです。
- TEXT(売上表!A:A, “ddd”)：各日付セルを「土曜日」「日曜日」の文字列に変換
- (TEXT(売上表!A:A, “ddd”)=“土曜日” + TEXT(売上表!A:A, “ddd”)=“日曜日”)：土曜日または日曜日の場合はTRUE（1）、それ以外はFALSE（0）
- ISNUMBER(MATCH(売上表!A:A, 祝日リスト!A:A, 0))：祝日リストと一致する場合TRUE、それ以外はFALSE
- (2) * (3)：土曜日または日曜日の上にさらに祝日である場合はTRUE（1）、それ以外はFALSE（0）
- FILTER(売上表!C:C, (2) * (3))：条件を満たすセルの値のみ抽出
- SUM(…): 抽出された値の合計 この数式は、元データを削除するのではなく、別の場所に抽出したデータとその合計をスピル表示します。
具体的な手順
1
まず、土日と祝日のリストを作成し、それぞれの列に配置します。
2
FILTER関数を入力するセルを選択し、数式バーで以下のように入力します：=SUM(FILTER(売上表!C:C, (TEXT(売上表!A:A, “ddd”)=“土曜日” + TEXT(売上表!A:A, “ddd”)=“日曜日”) * ISNUMBER(MATCH(売上表!A:A, 祝日リスト!A:A, 0))))
3
数式を入力したらEnterキーを押すと、スピル機能が自動的にデータを抽出し、合計値を表示します。
よくある質問
Q Q1. VBAとの違いは？
A.
A1. VBAはマクロ言語で、より複雑な処理や大量のデータを扱う場合に適していますが、FILTER関数はシンプルな構文で即座に結果を得ることができます。
Q Q2. スピルエラーとは？
A.
A2. スピルエラーは、数式が範囲外のセルを参照しようとした場合に出るエラーです。データ範囲を適切に設定することで解消できます。
Q Q3. スマホ版でも使える？
A.
A3. Excel for the webやGoogleスプレッドシートではスピル機能が利用可能ですので、スマートフォンからも同様の操作が可能となります。