📊【保存版】VBA不要！INDEX+AGGREGATE関数で可視セルのみにデータ貼り付け完全ガイド✨

目次

• はじめに
• INDEX+AGGREGATE関数の基本概念
• AGGREGATE関数完全解説
• 実際の使用例
• よくあるエラーパターンと対策
• まとめ

はじめに

先日、「可視セルのみに値を貼り付ける処理を関数で実現できないか」という相談を受けました。通常のペースト操作では非表示行も含めて順番に貼り付けられてしまいますが、A2、A4、A6のように一行おきに表示されている場合に、参照元の連続データ（1番目、2番目、3番目）を順次これらの可視セルにのみ配置したいという要望です。「現在の行が何番目の可視セルなのか」を判定できれば解決できると考えました。この動的な位置判定をINDEXとAGGREGATE関数の組み合わせで実現する方法について、備忘録としてまとめます。

Excel VBAで可視セルのみにペーストするテクニックもぜひご覧ください。

INDEX+AGGREGATE関数の基本概念

INDEX関数の役割

INDEX関数は、指定した配列から特定の位置にあるデータを取得する関数です。

【基本構文】

INDEX(配列, 行番号, [列番号])

【公式ドキュメント】

📘 基本例1：縦の範囲から指定位置の値を取得

=INDEX(A1:A5, 3)

結果：「バナナ」
→ 「A1:A5」の中で3番目にある値が取り出されます。

📗 基本例2：表から特定の行・列の交点を取得

=INDEX(B2:D4, 2, 3)

結果：「155」
→ 範囲B2:D4の中で、2行目・3列目の交差する値（鈴木さんの3月分155）が返されます。

AGGREGATE関数の役割

AGGREGATE関数は、エラー値や非表示行を無視しながら集計処理を行う関数です。今回は「空でないセルの個数を数える」機能を活用します。（詳しくは、後述します）

【基本構文】

AGGREGATE(関数番号, オプション, 配列)

【公式ドキュメント】

なぜこの組み合わせが効果的なのか

INDEX関数とAGGREGATE関数の組み合わせは、動的な位置指定によるデータ取得を可能にします。「現在の行番号に応じた柔軟なデータ参照」を、シンプルな数式で実現できるのが最大の特徴です。

AGGREGATE関数完全解説

AGGREGATE関数の集計方法一覧

AGGREGATE関数では19種類の集計方法が利用できます。関数番号で指定するため、番号と処理内容の対応表として活用してください。

関数番号	対応する関数	処理内容	追加引数	使用例
1	AVERAGE	平均値を計算	なし	=AGGREGATE(1,5,A1:A10)
2	COUNT	数値の個数をカウント	なし	=AGGREGATE(2,5,A1:A10)
3	COUNTA	空でないセルの個数をカウント	なし	=AGGREGATE(3,5,A1:A10)
4	MAX	最大値を取得	なし	=AGGREGATE(4,5,A1:A10)
5	MIN	最小値を取得	なし	=AGGREGATE(5,5,A1:A10)
6	PRODUCT	積（掛け算の結果）を計算	なし	=AGGREGATE(6,5,A1:A10)
7	STDEV	標準偏差（母集団推定）	なし	=AGGREGATE(7,5,A1:A10)
8	STDEVP	標準偏差（母集団全体）	なし	=AGGREGATE(8,5,A1:A10)
9	SUM	合計値を計算	なし	=AGGREGATE(9,5,A1:A10)
10	VAR	分散（母集団推定）	なし	=AGGREGATE(10,5,A1:A10)
11	VARP	分散（母集団全体）	なし	=AGGREGATE(11,5,A1:A10)
12	MEDIAN	中央値を取得	なし	=AGGREGATE(12,5,A1:A10)
13	MODE.SNGL	最頻値を取得	なし	=AGGREGATE(13,5,A1:A10)
14	LARGE	n番目に大きい値を取得	n	=AGGREGATE(14,5,A1:A10,2)
15	SMALL	n番目に小さい値を取得	n	=AGGREGATE(15,5,A1:A10,2)
16	PERCENTILE.INC	パーセンタイル値を取得	k	=AGGREGATE(16,5,A1:A10,0.8)
17	QUARTILE.INC	四分位数を取得	quart	=AGGREGATE(17,5,A1:A10,1)
18	PERCENTILE.EXC	パーセンタイル値を取得	k	=AGGREGATE(18,5,A1:A10,0.5)
19	QUARTILE.EXC	四分位数を取得	quart	=AGGREGATE(19,5,A1:A10,2)

パーセンタイル値

全体の中で何%の位置にあるかを示す値

番号1-11と12-19の違い

番号1-11：SUBTOTAL関数互換

• SUBTOTALで使える集計方法と同じ処理内容

番号12-19：AGGREGATE関数独自

• 統計関数や順位関数など、より高度な分析が可能

追加引数が必要な関数の詳細

関数番号14・15（LARGE・SMALL）

=AGGREGATE(14, 5, A1:A10, 2) 
=AGGREGATE(15, 5, A1:A10, 3)

2番目に大きい値
3番目に小さい値

関数番号16・18（PERCENTILE）

=AGGREGATE(16, 5, A1:A10, 0.8) 
=AGGREGATE(18, 5, A1:A10, 0.5)

80パーセンタイル値
50パーセンタイル値（中央値）

関数番号17・19（QUARTILE）

=AGGREGATE(17, 5, A1:A10, 1) 
=AGGREGATE(19, 5, A1:A10, 3)

第1四分位数（25パーセンタイル）
第3四分位数（75パーセンタイル）

AGGREGATE関数のオプション詳細解説

AGGREGATE関数の第2引数（オプション）は、計算時に何を無視するかを指定する重要なパラメータです。

オプション番号一覧

オプション	無視する対象	使用場面
0	ネストされたSUBTOTAL/AGGREGATE関数	複雑な集計の重複回避
1	０ と非表示行のセルを無視	フィルター後のデータ集計
2	０ とエラー値を無視	エラーが混在するデータの処理
3	０ と非表示行のセルとエラー値を無視	フィルター＋エラー対応
4	なし（何も無視しない）	全データを対象とした処理
5	非表示行のセルを無視	今回の用途（可視セル判定）
6	エラー値を無視	エラー耐性のある集計
7	非表示行のセルとエラー値を無視	最も安全な設定

実際の使い分けパターン

パターン1：表示されているデータだけを対象にしたい場合

• オートフィルターで絞り込んだデータのみ対象にしたい場合
• 手動で行を非表示にしたデータを除外したい場合

=AGGREGATE(3, 5, A2:A6)

結果：「4」
→ 非表示行（A5）を無視して、空でないセルの数をカウント

パターン2：エラーが混在するデータの処理

• 計算式でエラーが発生している行があっても集計を続行したい場合
• 外部データ取り込み時にエラーが含まれている場合

=AGGREGATE(3, 6, A2:A6)

結果：「4」
→ エラーセル（A4）を無視して、空でないセルの数をカウント

パターン3：最も安全な設定（推奨）

• どのような状況でもエラーになりにくい設定
• 初心者の方にはオプション「7」を推奨

=AGGREGATE(3, 7, A2:A6)

結果：「3」
→ 非表示行（A4）とエラーセル（A5）の両方を無視してカウント

SUBTOTAL関数との比較

AGGREGATE 関数を理解するために、よく比較されるSUBTOTAL 関数との違いを整理します。どちらも「フィルター後の範囲で計算する」という共通点がありますが、使い分けが重要です。

SUBTOTAL関数の基本

基本構文

SUBTOTAL(集計方法, 範囲1, [範囲2], ...)

【公式ドキュメント】

主要な集計方法

101 : AVERAGE  // 平均（非表示行を無視）
103 : COUNTA   // 空でないセルの個数（非表示行を無視）
109 : SUM      // 合計（非表示行を無視）
104 : MAX      // 最大値（非表示行を無視）
105 : MIN      // 最小値（非表示行を無視）

実際の比較例（非表示行を除外して、A2:A6の合計を集計）

SUBTOTAL関数を使った場合

=SUBTOTAL(109,A2:A6)

AGGREGATE関数を使った場合

=AGGREGATE(9, 7, A2:A6)

どちらを選ぶべきか

項目	SUBTOTAL関数	AGGREGATE関数
対応バージョン	Excel 2003以前から対応	Excel 2010以降
集計方法の種類	11種類	19種類
エラー処理	エラーがあると結果もエラー	エラーを無視可能
認知度	高い（よく知られている）	低い（知らない人が多い）

実務での選択指針

SUBTOTAL関数を選ぶべき場合

• チーム内でExcel 2007以前を使用している人がいる
• シンプルな集計のみで十分
• 関数の認知度を重視する場合

AGGREGATE関数を選ぶべき場合

• エラー処理が必要な場合
• LARGE、SMALLなどの順位関数が必要
• より柔軟な集計処理を行いたい場合

AGGREGATE関数の要点

SUBTOTAL 関数の上位互換

• SUBTOTAL 関数の機能を拡張した関数で、同じようにフィルター後の範囲で計算が可能
• より幅広い計算に対応し、エラー発生時も安全に処理できる

利用可能なExcelバージョン

• Excel 2010以降で利用可能（Excel 2007以前では非対応）
• 比較的新しい関数のため、古いバージョンでは使えない点に注意

集計方法の数が多い

• SUBTOTAL 関数：11種類の集計方法
• AGGREGATE 関数：19種類の集計方法に対応
• 追加された代表例：LARGE（n番目に大きい値）、SMALL（n番目に小さい値）など

エラー処理が可能

• 第2引数のオプションで「エラーを無視」する設定が可能
• SUBTOTAL ではエラーを無視できず、範囲にエラーがあると結果もエラーになってしまう

フィルターとの連動

• フィルターで非表示になったデータを除外して計算できる
• LARGE や SMALL と組み合わせれば「フィルター後の範囲でn番目の値」を取得可能

デメリット

• Excel 2007以前では使えない
• 認知度が低いため、職場で共有すると「理解されにくい」場合がある

AGGREGATE 関数は「SUBTOTAL 関数にエラー処理や順位取得機能を加えた強化版」ですが、普及度の低さには注意が必要です。

実際の使用例

社内システムに読み込むExcelファイルで特定の設定コードが変更になり、対象データだけを更新する必要がある状況です。システムの仕様上、列の追加ができないため、行の追加で複数の設定コードの記入が必要で、製品コードが重複してしまいます。

そのため、下記のダミーデータのように製品コードがユニークではない（同じ製品に複数の設定コードが紐づく）ため、製品コードでの絞り込みでは対応できません。特定の設定コードだけをピンポイントで更新する必要があります。

具体的な作業の流れ

【前提条件】

• システム連携用のExcelには多数の製品データが登録されている
• 1つの製品に複数の設定コードが紐づいている（製品コードはユニークではない）
• 特定の設定コードのみを更新したい（例：CFG-A12 → CFG-A01）

【作業手順】

1. 対象データの絞り込み

   • B列の「設定コード」でオートフィルターを適用
   • 変更対象の設定コード
   • 表示されたのは対象となる可視セルのみ
     

2. 更新データの準備（Sheet2）

   • 本部から送られてきた更新リストをシートに転記
     

3. 数式による自動更新

   • 可視セル（フィルター後の表示行）にのみ、順番に新しい設定コードを貼り付け
   • 非表示行（他の設定コード）には影響を与えない

数式の詳細解析

Sheet1のB列（フィルター適用後の可視セル）に以下の数式を入力します。

=INDEX(Sheet2!$B$2:$B$4,AGGREGATE(3,5,$A$2:A2))

数式の各部分の役割

INDEX部分：Sheet2!$B$2:$B$4

• Sheet2のB列（新設定コード）からデータを取得する範囲
• $B$2:$B$4は絶対参照なので、数式をコピーしても参照先は固定される
• 更新対象が複数ある場合は範囲を適切に設定

AGGREGATE部分：AGGREGATE(3,5,$A$2:A2)

• 関数番号「3」：空でないセルの個数をカウント（COUNTA）
• オプション「5」：非表示行を自動的に除外してカウント
• $A$2:A2：A列（製品コード列）のA2セルから現在行までの範囲（下にコピーすると範囲が拡大）

なぜA列を参照するのか

• B列（設定コード列）に数式を入力する場合、同じB列をAGGREGATEで参照すると循環参照エラーが発生する
  

• 循環参照とは、数式が自分自身のセルを参照してしまい、Excelが計算できなくなる状態

• A列（製品コード列）は更新されず、常に値が入っているため、循環参照を避けた安定したカウント基準として使用できる

• フィルター後の可視セルだけをカウントするため、「1番目、2番目、3番目...」という順番が正確に把握できる

動作メカニズムの詳細解説

上述の通り、AGGREGATE関数の関数番号「3」は「空でないセルの個数を数える」という機能で、現在の行が何番目のデータ行なのかを動的に判定するために活用しています。

AGGREGATE関数のオプション「5」により、フィルターが適用されていたり、途中に空行があったりしても、正確な「順番」を把握できるようになります。

1. 1行目（B2セル・CFG-A12の行）の場合

   • AGGREGATE(3,5,$A$2:A2) → A列のA2のみを評価 → 結果：1
   • INDEX(Sheet2!$B$2:$B$4,1) → Sheet2のB列1番目を取得 → 「CFG-A01」

2. 2行目（B5セル・2つ目のCFG-A12）の場合

   • AGGREGATE(3,5,$A$2:A5) → A列のA2～A5を評価（A3、A4は非表示で除外） → 結果：2
   • INDEX(Sheet2!$B$2:$B$4,2) → Sheet2のB列2番目を取得 → 「CFG-B02」

3. 3行目（B8セル・3つ目のCFG-A12）の場合

   • AGGREGATE(3,5,$A$2:A8) → A列のA2～A8を評価（A3、A4、A6、A7は非表示で除外） → 結果：3
   • INDEX(Sheet2!$B$2:$B$4,3) → Sheet2のB列3番目を取得 → 「CFG-C05」

よくあるエラーパターンと対策

パターン1：AGGREGATE 関数の引数ミス

症状

• AGGREGATE 関数で期待した結果が得られない
• 関数番号やオプションの指定ミス

失敗パターンの詳細

❌ 失敗例  ：AGGREGATE(103,5,$A$2:A2)  // SUBTOTALの番号を使用してしまっている
✅ 正しい例：AGGREGATE(3,5,$A$2:A2)    // AGGREGATEの正しい番号「3」を使用

ポイント

• AGGREGATE 関数の番号は1-19の範囲で指定
• SUBTOTAL 関数の101、103などの番号は使用不可

パターン2：範囲指定の参照ミス

症状

• 数式をコピーしても期待した動作をしない
• AGGREGATEの範囲が正しく拡張されない

失敗パターンの詳細

❌ 失敗例　：AGGREGATE(3,5,$A$2:$A$2) // 範囲が固定（拡張されない）
✅ 正しい例：AGGREGATE(3,5,$A$2:A2)   // 下方向にコピーすると範囲が拡張される

【重要なポイント】

• $A$2:A2 の形式が基本パターン
• 開始位置（$A$2）は絶対参照で固定
• 終了位置（A2）は相対参照で可変

デバッグのステップ

エラーが発生した場合の 段階的デバッグ手順 を確立しておくことが重要です。

ステップ1：AGGREGATE 部分の単体確認

=AGGREGATE(3,5,$A$2:A2)

この値が期待した順番（1、2、3...）になっているかチェック。

ステップ2：INDEX 部分の単体確認

=INDEX(Sheet2!$A$2:$A$6,1)
=INDEX(Sheet2!$A$2:$A$6,2)

1番目のデータが取得できるか、2番目のデータが取得できるかチェック。

ステップ3：組み合わせた数式の確認

両方が正常に動作することを確認してから、完全な数式を適用します。

まとめ

INDEX関数とAGGREGATE関数の組み合わせは、可視セルのみへのデータ貼り付けを数式で実現する手法です。AGGREGATE関数が「現在の行が何番目の可視セルか」を動的に判定し、INDEX関数がその順番に応じた参照元データを取得することで、フィルター適用後の表示行にのみ連続データを順次配置できます。

$A$2:A2形式の参照による動的な範囲拡張と、AGGREGATE関数の非表示行を除外したカウント機能により、VBAを使わずに関数のみで実現できるため、マクロが制限される環境でも適用できる実用的なテクニックとなります。

Excel VBAで可視セルのみにペーストするテクニックもぜひご覧ください。