「新製品のデータを追加したら、集計関数の範囲を修正しなきゃ…」
「ドロップダウンリストに、新しい項目が反映されない…」と思われた方は今回の記事は必見です。
Excelでデータ更新作業をするたびに、こんな風に感じていませんか？実は、Excel 2021以降で使えるようになった「#（ハッシュ記号）」を使えば、この悩みは一瞬で解決します。この記号は、同じく新機能の「スピル」と組み合わせることで、データと数式が自動で連動します。今回は、Excelの「#」の使い方を基本から実践的な活用例まで、分かりやすく丁寧に解説します。
ぜひ最後まで見てあなたのスキルをUPしてください
「スピル」とは？
「#」を理解するために、まずは「スピル（Spill）」という機能を知る必要があります。
スピルとは、1つのセルに入力した数式の結果が、必要に応じて複数のセルに自動的に展開される機能のことです。これは「動的配列」という仕組みによって実現されています。
例えば、商品の単価リストから、一括で10%の消費税込み価格を計算したいとします。
従来のやり方
1つのセルに「=B2*1.1」と入力し、オートフィルで下のセルに数式をコピーしていました。
スピルを使ったやり方
1つのセルに「=B2:B5*1.1」のように、計算したい範囲全体を選択して数式を入力します。
たったこれだけで、Enterキーを押すと、隣の列に計算結果が自動的に「あふれ出し（スピルし）」ます。結果が表示された範囲は青い枠線で囲まれ、数式は先頭のセルにしか入っていないのが特徴です。
数式が入っていないセルは、選択すると少し色が薄く表示され、直接編集はできません。
この「スピル」によって動的に生成されたデータ範囲（動的配列）を扱う上で、「#」が絶大な威力を発揮します。
注意点
数式の結果が展開される先に他のデータや文字が入力されていると、「#スピル!」というエラーが表示されます。スピル機能を使う際は、結果が表示される範囲が空のセルになっていることを確認しましょう。
「#」の基本的な使い方
「#」は、スピルによって作成された範囲全体を簡単に参照するための記号です。
UNIQUE関数を例に見てみましょう。UNIQUE関数は、指定した範囲から重複しない値だけを抽出してくれる、スピル対応の便利な関数です。
まず、=UNIQUE(C3:C17)のように入力し、製品カテゴリーの重複しないリストを作成します。
結果がスピルして表示されます。
次に、このカテゴリーが何種類あるか数えるためにCOUNTA関数を使います。ここで「#」の出番です。
=COUNTA(E2#)と入力します。UNIQUE関数の結果が表示されている先頭セル（この例ではE2）の後ろに「#」を付けるだけです。
つまり、「#」は『このセルから始まっているスピルの範囲全部』という意味を持つ、非常に便利な記号なのです。
これだけで、UNIQUE関数でスピルした範囲全体（E2からE11）が選択され、自動的に「9」という結果が返ってきます。
「#」を使わない場合と比較
もし=COUNTA(E2:E6)のように範囲を固定してしまうと、元のデータに新しいカテゴリー（例：イヤホン）が追加されても、UNIQUE関数の結果は「9」のままです。毎回、数式の「E6」の部分を「E7」に手で直さなければなりません。
しかし、「#」を使っていれば、UNIQUE関数のスピル範囲が自動で拡張されるのに連動して、COUNTA関数も新しい範囲を自動で認識し、正しく「10」とカウントしてくれます。もうあなたが手動で数式を修正する必要はありません。
自動で更新されるドロップダウンリスト
データの入力規則を使ったドロップダウンリストは便利ですが、リストの元データが増減するたびに参照範囲を修正するのが面倒でした。「#」を使えば、その手間から解放されます。
元データをテーブル化する:
まず、リストの元になるデータ範囲を選択し、「Ctrl + T」でテーブルに変換します。なぜ最初にテーブル化するのでしょうか？それは、テーブルの末尾に新しいデータを追加すると、Excelがそれをテーブルの一部として自動認識し、UNIQUE関数などの参照範囲も自動で拡張してくれるからです。このひと手間が、完全自動化の鍵となります。マウスパットを例）
UNIQUE関数でリストを作成
作業用のセルにUNIQUE関数を使い、ドロップダウンリストに表示したい項目（例：商品売上表）を抽出します。
データの入力規則を設定:
ドロップダウンリストを作成したいセルを選択します。
①「データ」タブ → 「データの入力規則」をクリックします。
②「入力値の種類」で「リスト」を選択します。
③「元の値」のボックスに、=UNIQUE関数が入っているセル（例：=$E$3:$E$11）と入力し、OKボタンを押します。
すると、下から引き出されるように、プルダウンリストが作成されます。
しかし、この状態だと、データの中身が変わり、マウスが新しく追加されたり、タブレットやプリンターがなくなった時、プルダウンリストが連動してくれないので、元の値を変更します。
【データの入力規則】に戻り、【＄E＄3】の後ろに【＃】ハッシュを付けて、OKボタンを押します。
これで、全て選べるようになります。勿論、データを変更、拡張しても、データが連動して、プルダウンリストが表示されます。
関数と連携した動的ダッシュボード
では、もう少し深くみていきましょう。上記のダッシュボードを作成して、商品を選択すると、それに基づき、全ての商品・単価と、【平均金額】・【最大金額】・【最小金額】・【個数】が表示されるようにします。
FILTER関数と「#」を組み合わせれば、ドロップダウンリストで項目を選ぶだけで、関連データが自動で抽出・集計される簡易ダッシュボードが作成できます。
=FILTER(テーブル全体, 商品列 = H3)
選択された製品カテゴリーのデータだけを抽出します。
抽出データを「#」で集計
FILTER関数の結果もスピルするため、「#」で参照して販売日数や商品の平均などを計算できます。
すると、表の単価の金額の平均を抽出してくれます。これは、範囲全体を選択しているのですが、商品名はテキスト、単価は、数値です。
この「#」があるおかげで、FILTER関数の結果が3行になっても5行になっても、Excelが自動で正しい範囲を認識して計算してくれる、というわけです。【最大値】【最小値】も同様です。
=COUNTA(G10#)のように単純に「#」で参照すると、スピルした範囲全体（この例では2列×4行=8セル）をカウントしてしまい、正しい結果（3日）になりません。
INDEX関数で特定の列だけを対象にする
こういう場合はINDEX関数を組み合わせます。INDEX関数を使うと、スピルした範囲の中から特定の列や行だけを取り出すことができます。
INDEX関数とは？
この様なデータがあります。
ノートパソコン Core i7を表示するには、INDEX(表全体, 行番号, 列番号)で表示されます。
この様に、ノートパソコン Core i7が取り出されました。この様に、1行目と、2行目がクロスした範囲を返す事が出来ます。
3行目と2列目がクロスした、デスクトップを表示させたいなら、引数を3,2にすると、14800と言う値が表示されます。
この際、行番号の2を0にした場合、
【データ3】が表示されます。行番号を0にした事で、行番号が何行目か？分からない状態ですので、データ3の金額が全部かえってきます。
なので、個数を調べる時は、INDEX(スピル範囲の先頭セル#, 0, 列番号)
INDEX関数の引数は通常 (配列, 行番号, 列番号) です。ここで2つ目の引数である行番号に 0 を指定するのがポイントです。0 を指定すると、配列内のすべての行を対象とし、3つ目の引数で指定した列のデータだけを抜き出す事が出来ます。
それを、CountAで個数を表示したいので、関数を挿入すると、【ノートパソコン】の個数は4つあると確認出来ます。
これで、ドロップダウンリストの項目を「スマートフォン」から「ノートPC」に変更するだけで、抽出データと全ての集計結果が一瞬で自動的に更新されるようになります。
- 「#」は？
-
「スピル」機能で動的に生成された範囲（動的配列）を参照する記号
- Excelでスピルとは何ですか？
-
Excelの#SPILL!エラー（スピルエラー）の主な原因は、数式が展開されるべき範囲に既にデータが存在する場合、スピル範囲がワークシートの端を超えてしまう場合、または結合されたセルがある場合です。これらの状況では、Excelが結果を返すための十分なスペースを確保できないため、エラーが発生します。
まとめ
今回は、Excelの作業効率を飛躍的に向上させる「#」の使い方について解説しました。
■__________________________________■
メルマガ登録でお渡しする
【10大】
特典をプレゼント中↓↓↓
✅Powerquery教科書（PDF版）
✅MOS教科書（PDF版）
✅Ctrlキーのショートカット集
✅現金出納帳フォーマット
✅家計簿フォーマット
✅備品管理表フォーマット
✅提出物が揃ったら自動で完成（提出物確認）
✅備品注文書（チェックボックスで管理）
✅ガントチャートフォーマット
✅目次自動作成ツール
＼ 無料メルマガに登録してプレゼントの受け取りはここから／
コメント