Excelでデータ抽出＆集計をマスター！動的リストと関数の活用法

以下がプロンプト例として考えるべき数式：

1. 金額合計（SUMIFSを使う）
   金額合計を求めるには、SUMIFS関数を使うよ。
2. 受注件数（COUNTIFSを使う）
   条件に一致する受注件数を求めるには、COUNTIFS関数を使うよ。

🔨 合計金額にはSUMIFS関数

1. 金額合計（G4）

セルG4に以下の数式を入力してね：

=SUMIFS(D3:D12, B3:B12, G2, C3:C12, G3)

数式の意味：

• D3:D12：金額が入っている範囲（合計する対象）
• B3:B12：工務店名が入っている範囲（条件1）
• G2：入力された工務店名（条件1の基準）
• C3:C12：商品名が入っている範囲（条件2）
• G3：入力された商品名（条件2の基準）

これで、G2に工務店名、G3に商品名を入力すれば、該当する金額の合計がG4に表示されるよ。

2. 受注件数も表示したい場合（オプション）

もし受注件数も表示したい場合は、セルG5に以下の数式を入力：

=COUNTIFS(B3:B12, G2, C3:C12, G3)

🔨 驚異のFILTER関数

方法1: FILTER関数を使う（Excel 365または2021以降）

手順

1. セルI4に以下の数式を入力：
   =FILTER(C3:F12, B3:B12=H4, "該当するデータがありません")
2. 上記数式を入力すれば、I4:Lに自動で抽出された結果が並ぶよ！
3. 金額の合計を求めたい場合は、セルL13に以下を入力：
   =SUM(L4:L12)

---

方法2: FILTERが使えない場合（配列数式）

Excel 2019以前では配列数式を使う。以下の方法で対応可能：

1. 氏名（I4） セルI4に以下を入力：
   =IFERROR(INDEX(C$3:C$12, SMALL(IF($B$3:$B$12=$H$4, ROW($B$3:$B$12)-ROW($B$3)+1), ROW(1:1))), "")
   ※この数式は Ctrl + Shift + Enter を押して確定。
   
2. 建設場所（J4）、区分（K4）、金額（L4）
   各列に対して以下をコピーして対応範囲を変更：
   • 建設場所: 範囲をD$3:D$12に変更
   • 区分: 範囲をE$3:E$12に変更
   • 金額: 範囲をF$3:F$12に変更
3. 数式をI4からコピーして下にドラッグすればOK。
4. 合計金額（L13）は同じようにSUM(L4:L12)で計算できる！

---

方法3: VBAで抽出

---

🔨 FILTER関数が使えない場合の対処法

手順（配列数式）

1. 氏名（I4） セルI4に次の数式を入力：
   =IFERROR(INDEX(C$3:C$12, SMALL(IF($B$3:$B$12=$H$4, ROW($B$3:$B$12)-ROW($B$3)+1), ROW(1:1))), "")
   • 入力後 Ctrl + Shift + Enter を押して確定する！（配列数式になる）
   • I4に抽出された最初の氏名が表示される。
2. 他の列（建設場所、区分、金額） I4の数式をコピーしてJ4、K4、L4に貼り付け、範囲を変更：
   • 建設場所（J4）: C$3:C$12 → D$3:D$12
   • 区分（K4）: C$3:C$12 → E$3:E$12
   • 金額（L4）: C$3:C$12 → F$3:F$12
3. 下方向にドラッグ I4:L4をドラッグして必要な行数までコピー。空白が表示されるまで続けて大丈夫！
4. 合計金額（L13） セルL13に以下の数式を入力：
   =SUM(L4:L12) これで合計金額も計算完了！

---

🔨 プルダウンメニューで入力を簡単に

手順：工務店名をプルダウンで選択できるようにする

1. 工務店名の一覧を準備する：
   • B列の工務店名（B3:B12）は重複があるから、重複を除いたリストを作る必要がある。例えば、別の場所（M列など）に以下の数式を入力：scssコピーする編集する=UNIQUE(B3:B12)
     • Excel 365/2021以降の**UNIQUE関数**で、重複を除いた工務店名のリストを作成する。
     ※Excel 2019以前なら、ピボットテーブルなどを使って重複を手動で除く方法もある。
2. プルダウンの設定：
   • セルH4を選択（工務店名を入力するセル）。
   • 上部メニューからデータタブ → データの入力規則を選択。
   • 「条件」をリストに設定して、「元の値」に重複を除いた工務店名のリスト範囲を指定（例：$M$3:$M$8）。
   • OKを押して設定完了！
3. 動作確認：
   • H4セルにプルダウンメニューが追加され、工務店名を選択可能に。

---

補足：自動更新するプルダウンメニュー

もし工務店名が増えたり変更されたときに、プルダウンリストを自動で更新したい場合は、名前付き範囲やテーブルを活用する方法もある。必要なら追加の説明をするよ！

---

🔨 プルダウンメニューを自動更新

方法1: 名前付き範囲を使う

手順

1. 工務店名のリストを動的に取得する
   例えば、セル範囲B3:B12から重複を除いたリストを動的に作成する場合：
   • 空いている列（例：M列）に以下の数式を入力：=UNIQUE(B3:B100)
     ※B3:B100に余裕を持たせているので、新しいデータが追加されても自動で反映される。
2. 名前付き範囲を作成
   • 数式タブ → 名前の管理を選択。
   • 新規作成をクリックして、「名前」を設定（例：工務店リスト）。
   • 「参照範囲」に先ほどのUNIQUE関数の出力範囲を指定（例：=$M$3:$M$100）。
   • OKを押して設定完了。
3. プルダウンリストに名前付き範囲を適用
   • H4セルを選択して、データタブ → データの入力規則を開く。
   • 「元の値」に次のように入力：コピーする編集する=工務店リスト

これで、工務店名を追加しても自動でプルダウンに反映されるよ！

---

方法2: テーブルを活用する

手順

1. 工務店名のデータ範囲をテーブル化
   • B列の工務店名（例：B3:B12）を選択。
   • 上部の「挿入」タブから「テーブル」をクリック。
   • 「先頭行をテーブルの見出しとして使用する」を確認して、OKを押す。
2. テーブルに名前をつける
   • テーブル内を選択し、「テーブルデザイン」タブで名前を設定（例：工務店テーブル）。
3. プルダウンリストにテーブル列を指定
   • H4セルを選択して、データタブ → データの入力規則を開く。
   • 「元の値」に以下を入力：scssコピーする編集する=INDIRECT("工務店テーブル[工務店名]") ※[工務店名]はテーブル内の列名に一致させる。

これでテーブルに新しい工務店名を追加するだけで、プルダウンメニューに反映されるようになるよ！

---

どちらがオススメか？

• Excel 365/2021なら、UNIQUE関数を使った方法がシンプルで便利。
• Excel 2019以前なら、テーブルを使う方法が実用的。

---

🔨 UNIQUE関数が使えない場合の対処法

方法：UNIQUEが使えないバージョン

ステップ1：重複を除いた工務店リストを作成

1. B列から重複を取り除く
   • Excelの上部メニューからデータタブを選択。
   • データツールグループの中にある重複の削除をクリック。
   • 範囲としてB3:B12を選択（工務店名が含まれる範囲）。
   • 「OK」をクリックすると、重複を取り除いたリストが作成される。
2. 別の列にコピー
   重複を削除した工務店リストを、プルダウンメニューで使うために例えばM列にコピーしておく。

---

ステップ2：プルダウンメニューを設定

1. H4セルを選択
   • 工務店名を入力するセル（H4）を選択する。
2. データの入力規則を設定
   • 上部メニューのデータタブを選択。
   • データの入力規則をクリック。
   • 「条件の設定」で「リスト」を選択。
   • 「元の値」に以下を入力：swiftコピーする編集する=$M$3:$M$12 ※M列に作成した工務店リストの範囲を指定。
3. OKを押して完了！

---

補足：工務店リストが変更される場合

もし工務店リストが頻繁に変わる場合は、手動で重複削除を実行する必要があるけれど、それ以外はこの方法でも十分に対応可能だよ。

---

---

🔨 Excelのバージョンについて

FILTER関数・UNIQUE関数が使えるバージョン

• Excel 365
• Excel 2021

---

🔨 ジッピーから締めのご挨拶

---

---

🔨 終わりに