[最終更新 : 2023/9/11 | 記事作成日 : 2012/10/27]
更新内容 (2023/9/11) : 文章の順序入れ替え
MATCH関数といえば、表計算ソフトの検索に使う基本的な関数です。
しかし、同じ値が複数ある場合は、最初のものしか見つけられません。
なんとかして、すべて見つけられないものか?
という試行錯誤の話。
わたしが使っている方法は、MATCH関数とOFFSET関数の組み合わせです。
概要
| . | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | すいか | . | 検索語 | バナナ | . |
| 2 | なし | . | リスト行数 | 10 | . |
| 3 | バナナ | . | 該当数 | 3 | . |
| 4 | みかん | . | 検索結果 | . | . |
| 5 | りんご | . | 1 | 3 | 行目 |
| 6 | みかん | . | 2 | 7 | 行目 |
| 7 | バナナ | . | 3 | 9 | 行目 |
| 8 | なし | . | . | . | . |
| 9 | バナナ | . | . | . | . |
| 10 | りんご | . | . | . | . |
こんな感じで、検索語を入力すると、
それが何行目にあるか表示します。
(通常、MATCH関数では、最初の 3行目しか分からないハズ)
これを実現する方法の話。
興味のある方は続きをどうぞ
解決策の進化の軌跡 ?
[追記 : 2023/8/19]
[Lv.1] : VLOOKUP + 作業列
- 欠点 : 検索列は左端のみ
- 欠点 : VLOOKUP の 検索範囲が固定
- - - ↓ - - -
[Lv.2] : INDEX,MATCH + 作業列 または XLOOKUP + 作業列
- 欠点解消 : 検索列は任意に
- 欠点 : INDEX,MATCH / XLOOKUP の 検索範囲が固定
- - - ↓ - - -
[Lv.3] : OFFSET,MATCH + 作業列
- 欠点解消 : OFFSET は検索範囲の指定が必要ない。
(始点の指定のみで終点の指定はない) - 欠点 : MATCH の 検索範囲が固定
- 注意点 : 行・列の指定方法が変わる
(左上の指定例)
- INDEX (範囲,1,1)
- OFFSET (始点,0,0)
(始点には範囲の左上を指定する)
- - - ↓ - - -
[Lv.4] : OFFSET + MATCH,OFFSET ☆ : この記事の解説内容
MATCH の検索範囲を OFFSET で指定する。
- 欠点解消 : 作業列を元データに追加しなくてもよい。
- 欠点解消 : MATCH の検索範囲を任意に変更できる。
- 欠点 : 検索条件は 1つ
- - - ↓ - - -
[Lv.5] : FILTER
- 欠点解消 : 1つの関数で全てを抽出できる。
- 欠点解消 : 複数条件の検索も可能
- 欠点 : FILTER の 検索範囲が固定
補足
- [Lv.1-3] データを並べ替えてから検索すると作業列は必要ない。
- [Lv.1-3] の「作業列」や「並べ替え」は元データを加工している。
- [Lv.4,5] は元データをそのまま利用できる。
- [Lv.4 以外] 検索範囲を自動で拡張したい場合は OFFSET で範囲を指定する。
注 : 配列式を使う方法もあるようですが、詳しくないので割愛。
目次
- [Lv.4] MATCH、OFFSETの併用
以下は方法の紹介のみです。
具体的な使い方はリンク先にある別サイトの解説をご覧ください。
[Lv.4] MATCH、OFFSETの併用 (わたしが使っている方法)
[ 目次へ戻る ]
以下、解説
| . | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | すいか | . | 検索語 | バナナ | . | . |
| 2 | なし | . | リスト行数 | 10 | . | . |
| 3 | バナナ | . | 該当数 | 3 | . | . |
| 4 | みかん | . | 検索結果 | . | . | . |
| 5 | りんご | . | 1 | 3 | 行目 | [0] |
| 6 | みかん | . | 2 | 7 | 行目 | [3] |
| 7 | バナナ | . | 3 | 9 | 行目 | [7] |
| 8 | なし | . | . | . | . | . |
| 9 | バナナ | . | . | . | . | . |
| 10 | りんご | . | . | . | . | . |
[数値] : 作業セル
この方法の特徴
- 元リストに手を加えていない
- 作業セル数は、該当数のみ
作業セルが元リストではなく、
結果表示の方にあるのが特徴のひとつです。
これだけ見ても何も分からないと思います。
簡単にいうと、ひとつ見つけるごとに
検索範囲をずらしています。
サンプルデータ
[ 目次へ戻る ]
[追記 : 2014/3/8]
動作確認用のサンプルデータ (テキストファイル、3126バイト)
使い方は、テキストを読んでください。
保存方法
-
Windows :
マウス右クリック、「対象をファイルに保存」 -
Mac :
control+クリック、「対象をファイルに保存」
または、対象をデスクトップなどにドラッグ
作業セルの中身
ぱっと見で分かると思いますが、 ひとつ前の結果をそのまま表示しています。
- 3行目 [0]
- 7行目 [3] (1つ目の結果)
- 9行目 [7] (2つ目の結果)
ひとつ前の結果を表示しているだけなので、作業セルがなくても計算はできます。
(上セルの結果を参照すればよい)
何を計算しているか分かりやすくするために、作業セルを使って説明しています。
検索範囲をずらす方法
MATCH関数の範囲指定の部分にOFFSET関数を使います。
事前にD2セルでデータの行数を調べています。
OFFSET(基準セル,行位置修正,列位置修正,範囲行数,範囲列数)
- OFFSET($A$1,[0],0,D$2-[0],1)
- OFFSET($A$1,[3],0,D$2-[3],1)
- OFFSET($A$1,[7],0,D$2-[7],1)
[数値] : 作業セルの値
作業セルの部分は、実際は、セルの番地を入れます。
(下記、設定例参照)
検索範囲
- A1から10行分の範囲
- A4から 7行分の範囲
- A8から 3行分の範囲
[追記 : 2023/8/19]
1つ目の検索範囲
| . | A |
|---|---|
| 1 | すいか |
| 2 | なし |
| 3 | バナナ |
| 4 | みかん |
| 5 | りんご |
| 6 | みかん |
| 7 | バナナ |
| 8 | なし |
| 9 | バナナ |
| 10 | りんご |
(match関数の結果 : 3)
2つ目の検索範囲 (3行ずらす)
| . | A |
|---|---|
| 4 | みかん |
| 5 | りんご |
| 6 | みかん |
| 7 | バナナ |
| 8 | なし |
| 9 | バナナ |
| 10 | りんご |
(match関数の結果 : 4)
(1つ目の結果 : 3)
(2つ目の位置 : 4+3=7)
3つ目の検索範囲 (7行ずらす)
| . | A |
|---|---|
| 8 | なし |
| 9 | バナナ |
| 10 | りんご |
(match関数の結果 : 2)
(2つ目の結果 : 7)
(3つ目の位置 : 2+7=9)
.
設定例
| . | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | すいか | . | 検索語 | バナナ | . | . |
| 2 | なし | . | リスト行数 | 10 | . | . |
| 3 | バナナ | . | 該当数 | 3 | . | . |
| 4 | みかん | . | 検索結果 | . | . | . |
| 5 | りんご | . | 1 | 3 | 行目 | [0] |
| 6 | みかん | . | 2 | 7 | 行目 | [3] |
| 7 | バナナ | . | 3 | 9 | 行目 | [7] |
| 8 | なし | . | . | . | . | . |
| 9 | バナナ | . | . | . | . | . |
| 10 | りんご | . | . | . | . | . |
ここでの仮定
- リスト範囲 : A1:A10
- 検索語 : D1
- リスト行数 : D2
- 数式入力セル : D5:D7
- 作業セル : F5:F7
MATCH(検索値,範囲,条件)
例 : D5セル
- 変更前 : MATCH(D$1,$A$1:$A$10,0)
- 変更後 : MATCH(D$1,OFFSET($A$1,F5,0,D$2-F5,1),0)+F5
これを該当行数分 (D5:D7) コピペすれば良い。
分かりづらいので、項目ごとに分けた表
MATCH関数の設定 : D5セル
| 検索値 | 検索範囲 | 照合の型 | 修正 |
|---|---|---|---|
| D$1 | $A$1:$A$10 | 0 | . |
| D$1 | OFFSET($A$1,F5,0,D$2-F5,1) | 0 | +F5 |
[修正]は、MATCH関数とは別の計算です。
3ヶ所の「F5」の意味
- 1つ目の F5 : 検索開始行をずらす。
→条件に当てはまる値が見つかったセルの次の行にします。
- 2つ目の -F5 : 検索行数を減らす。
→そのままだと、検索範囲の終点が下にずれてしまうので修正します。
- 3つ目の +F5 : ずらした分を元に戻す。
→求めたい値は、先頭からの行数なので、 最後にずらした分を足します。
これで先頭からの行数が分かります。
作業列(F列)は、省略可能です。
その場合は、「F5」を「D4」に変更してください。
使用例
実際は、行数を表示するだけでは、あまり意味がありません。
この結果をさらに作業セルとして、
その行の取り出したい列のデータを抽出しています。
| . | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | すいか | 300円 | A店 | . | 検索語 | バナナ | . | . | . |
| 2 | なし | 150円 | C店 | . | リスト行数 | 10 | . | . | . |
| 3 | バナナ | 120円 | A店 | . | 該当数 | 3 | . | . | . |
| 4 | みかん | 100円 | A店 | . | 検索結果 | . | . | . | . |
| 5 | りんご | 100円 | B店 | . | 1 | 120円 | A店 | [3] | [0] |
| 6 | みかん | 150円 | B店 | . | 2 | 140円 | C店 | [7] | [3] |
| 7 | バナナ | 140円 | C店 | . | 3 | 110円 | B店 | [9] | [7] |
| 8 | なし | 170円 | B店 | . | . | . | . | . | . |
| 9 | バナナ | 110円 | B店 | . | . | . | . | . | . |
| 10 | りんご | 130円 | C店 | . | . | . | . | . | . |
[数値] : 作業セル
設定例 : 価格・店名を取り出す
ここでもOFFSET関数を使っています。(理由は後述)
ここでの仮定
- 品名 : A列
- 価格 : B列
- 店名 : C列
- 数式入力セル : F5:G7
- 作業セル : H5
OFFSET(基準セル,行位置,列位置)
例 : F5セル
- OFFSET(B$1,$H5-1,0)
これを該当範囲 (この例では、F5:G7) にコピペする。
OFFSET関数の利点
OFFSET関数は、リストの量が変わっていく場合に便利です。
INDEX関数は、
範囲 (始点と終点) を指定しないといけません。
リストの量が変われば、範囲を指定しなおす必要があります。
一方、OFFSET関数は、
基準セル (始点) を指定するだけです。
そのため、リスト数が変わった時に、
範囲を指定しなおす必要がありません。
リスト行数は、事前に別セルで調べておくと、あとで楽になります。
OFFSET関数使用時の注意点
OFFSET関数の位置指定は、行数や列数ではなく、基準セルからの相対位置になります。
INDEX関数やLOOKUP関数のように、行数や列数を指定するわけではありません。
通常は、元の表の左上を基準セルにして、 参照したい座標から -1 する方法が 簡単です。
INDEX関数、OFFSET関数で、同じセルを指定する時の違い
- INDEX(範囲,行,列)
- OFFSET(基準セル,行-1,列-1)
追記 : [Lv.5] FILTER関数
[ 目次へ戻る ]
[修正 : 2023/6/22 | 追記 : 2018/11/11]
Excel 2021 以降や
Googleスプレッドシートの場合、
専用の関数があるため、そちらを使ったほうが簡単です。
検索語に該当する行を全て抽出できるだけでなく、
検索語を複数にしたり、
複数の列の検索にも対応しているので、
使い勝手はいいと思います。
Excel 2019 以前 や LibreOffice Calc など、
他のソフトと互換性を持たせたい場合は、
別の方法を使ってください。
LibreOffice は拡張機能 ( Lox365 ) で追加可能
[追記 : 2023/8/19]
-
追加された関数
FILTER、SORT、TEXTSPLIT、TOCOL、UNIQUE、XLOOKUP
(注 : 一部、動作が不完全なものもあるようです)紹介記事 : (窓の杜 '22/9/27の記事)
Excelの新関数「XLOOKUP」を「LibreOffice」でも! 有志が拡張機能で実現 - 窓の杜
[Lv.1-3] 作業セルを使う方法
[ 目次へ戻る ]
| . | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | すいか | [0] | . | 検索語 | バナナ | . |
| 2 | なし | [0] | . | リスト行数 | 10 | . |
| 3 | バナナ | [1] | . | 該当数 | 3 | . |
| 4 | みかん | [0] | . | 検索結果 | . | . |
| 5 | りんご | [0] | . | 1 | 3 | 行目 |
| 6 | みかん | [0] | . | 2 | 7 | 行目 |
| 7 | バナナ | [2] | . | 3 | 9 | 行目 |
| 8 | なし | [0] | . | . | . | . |
| 9 | バナナ | [3] | . | . | . | . |
| 10 | りんご | [0] | . | . | . | . |
[数値] : 作業セル
検索して見かけた方法のひとつに、「作業セル」を使う方法があります。
作業セルの簡単な説明
条件に当てはまった行のとなりの列に連番を表示することで、 複数の項目を取り出す方法。
気になる点
- 元リストに手を加えている
- 作業セルにムダが多い
(検索語と関係ない行でも計算をしているため)
この方法については、こちらの記事を参考にしています。
おわりに
おおざっぱな説明でしたが、 わかってもらえたでしょうか?
一見、ブログの内容と関係ないようなネタですが、 記事を作る時に必要になってきます。
例えば、こんな時に
- 同じ制作会社の作品を抽出とか
- 同じジャンルの作品を抽出とか
このブログを良く見ている方には、
「ああ、あの記事か」と、思い当たる所があるでしょう。
おなじみのあの記事を作るために、
この「MATCH関数で 2つ目以降を見つける方法」
を使っています。
おわび
説明を簡単にするため、主要な部分以外は大幅に省略しています。
実際は、行数チェックとか、エラー表示回避とか...
わたしが実際に使っているソフトは、
ExcelではなくOpenOffice.orgのCalcです。
一応、Excelの記述に合わせて書いたつもりですが、
間違っていたらごめんなさい。
この記事では見つからないもの
「複数条件、複数列」など、
検索語や検索範囲を増やして絞り込む方法とは異なります。
これについては、ほかのサイトをご覧ください。
参考サイト
- (リンク切れ)
Excel一般機能：複数条件での検索方法 - ChiquilinSite
この記事へのコメント