MATCH関数で 2つ目以降を見つける方法

最終更新 : 2015/4/25 | 記事作成日 2012/10/27

更新内容 (2015/4/25) : 文章修正


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行目しか分からないハズ)
これを実現する方法の話。


興味のある方は続きをどうぞ


サンプルデータ

動作確認用のサンプルデータ (テキストファイル、3126バイト)
使い方は、テキストを読んでください。


保存方法

  • Windows :
    マウス右クリック、「対象をファイルに保存」
  • Mac :
    control+クリック、「対象をファイルに保存」
    または、対象をデスクトップなどにドラッグ

わたしが使っている方法 (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 りんご . . . . .

[数値] : 作業セル


この方法の特徴

  • 元リストに手を加えていない
  • 作業セル数は、該当数のみ

作業セルが元リストではなく、 結果表示の方にあるのが特徴のひとつです。
これだけ見ても何も分からないと思います。
簡単にいうと、ひとつ見つけるごとに 検索範囲をずらしています。


作業セルの中身

ぱっと見で分かると思いますが、 ひとつ前の結果をそのまま表示しています。

  • 3行目 [0]
  • 7行目 [3] (1つ目の結果)
  • 9行目 [7] (2つ目の結果)

ひとつ前の結果を表示しているだけなので、作業セルがなくても計算はできます。
(上セルの結果を参照すればよい)
何を計算しているか分かりやすくするために、作業セルを使って説明しています。


検索範囲をずらす方法

MATCH関数の範囲指定の部分にOFFSET関数を使います。
事前にD2セルでデータの行数を調べています。


OFFSET(基準セル,行位置修正,列位置修正,範囲行数,範囲列数)

  1. OFFSET($A$1,[0],0,D$2-[0],1)
  2. OFFSET($A$1,[3],0,D$2-[3],1)
  3. OFFSET($A$1,[7],0,D$2-[7],1)

検索範囲

  1. A1から10行分の範囲
  2. A4から 7行分の範囲
  3. A8から 3行分の範囲

[数値] : 作業セルの値


作業セルの部分は、実際は、セルの番地を入れます。
(下記、設定例参照)


設定例

. 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)

おわりに

おおざっぱな説明でしたが、 わかってもらえたでしょうか?


一見、ブログの内容と関係ないようなネタですが、 記事を作る時に必要になってきます。

例えば、こんな時に

  • 同じ制作会社の作品を抽出とか
  • 同じジャンルの作品を抽出とか

このブログを良く見ている方には、 「ああ、あの記事か」と、思い当たる所があるでしょう。
おなじみのあの記事を作るために、 この「MATCH関数で 2つ目以降を見つける方法」 を使っています。


おわび

説明を簡単にするため、主要な部分以外は大幅に省略しています。
実際は、行数チェックとか、エラー表示回避とか...


わたしが実際に使っているソフトは、 ExcelではなくOpenOffice.orgのCalcです。
一応、Excelの記述に合わせて書いたつもりですが、 間違っていたらごめんなさい。


他の方法

. 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つ目以降を見つける方法
  • アドレス : http://anime-research.seesaa.net/article/299266624.html

はてなブックマーク - MATCH関数で 2つ目以降を見つける方法 B! はてなブックマーク | | ページ先頭△


posted by quasiquail at 00:05コメント (0)トラックバック (0)日記

この記事へのコメント

  • コメントはありません。
この記事へのトラックバックURL
http://blog.seesaa.jp/tb/299266624
※ブログオーナーが承認したトラックバックのみ表示されます。

この記事へのトラックバック

トラックバックはありません。