Excel Tips by pPoy

トップへ←Top    VBAへVBA  VBA基礎へVBA 基礎  一般へ一般  

MATCH関数 範囲内の最右に入力された文字列を取得する方法
《MATCH関数》(2000,XP,2003)
Excel のワークシートに、左から右へ順番に文字が入力されていて、その一番右の文字のみが必要な場合があります。

以下のような表があります。この表の 「直近値」 欄に、値を入れたいと思います。
勿論1つずつセルを選択しながら、全ての参照式を入れても良いのですが。
(例えば、I3セルに「=G3」、 I5セルに「=F5」とか・・・)

これでは、効率が悪い上に最右列に値が入った場合、また最初から作り直す必要があります。
以下は、Excel の標準の関数を組み合わせて、最右列の値を取り出す方法です。

Book1

1月〜6月欄は、全て入力されているとは限りません。

ところどころ抜けている場合もあります。

直近欄には、1月〜6月の内、一番右に入力された値を表示したいと思います。
「I3」 セルに以下の式を入力します。

=INDEX(C3:H3,MATCH("",C3:H3,-1))

ただしこれだけでは、C〜H 列に何も値が入力されていない場合は、「#N/A」 エラーとなります。
エラーを出さないためには、以下のように IF 関数を追加後、I 列の残りのセルにコピーます。

=IF(COUNTIF(C3:H3,"*"),INDEX(C3:H3,MATCH("",C3:H3,-1)),"")

式入力後
この頁のTOPへTop
≪補足1≫

指定された範囲内の最後の文字列を取り出す準備として、MATCH 関数を使用します。

MATCH 関数は、「検査値」 を 「検査範囲」 の中から検索しますが、見つかったときに返すものは範囲内での位置情報です。
つまり検査範囲の中で何番目に位置しているかという、数字を返します。

引数の 「照合の型」 は省略可能です。 省略された場合は、「1」 が指定されたことになります。
「照合の型」 は、「-1」・「0」・「1」 のいずれかを指定できます。

【書式】
MATCH(検査値,検査範囲,照合の型)

【今回の式】
MATCH("",C3:H3,-1)

上記の式では、「検査値」 として、「""」 (長さ零の文字列) を指定しています。
検査値を探す範囲として、「1月〜6月」 のセル範囲を指定しています。
「照合の型」 は 「-1」 を指定しています。
「-1」 の場合は、検査値以上の最小の値が検索されます。 検査範囲内の値は降順に並び替えられている前提で使用します。
降順の一番下から、つまり範囲の最後から検索して行って、最初に見つかった文字列の位置を返すことになります。

※ MATCH 関数の詳しい使い方は、こちらをご覧ください。
Microsoft Office Online のページに飛びます。
≪補足2≫

MATCH 関数で見つかった位置情報を使用して、ワークシート内のセル参照を取り出すために、INDEX 関数を使用しています。

INDEX 関数には、配列形式とセル範囲形式の 2種類があります。
今回の例では、セル範囲形式を使用しています。

セル範囲形式の INDEX 関数は、指定された行と列が交差する位置にあるセルの参照を返します。
範囲で指定した各領域に 1 行 (または 1 列) しか含まれていない場合は、それぞれ行番号 (または列番号) を省略できます。
領域番号は省略できます。省略した場合は最初の範囲が指定されます。

【書式】
INDEX(範囲,行番号,列番号,領域番号)

【今回の式】
INDEX(C3:H3,MATCH("",C3:H3,-1))

上記の式では、「範囲」 として、「1月〜6月」 の1行のセル範囲を指定しています。
取り出す位置の番号としては、「MATCH関数」 の戻り値を使用しています。

※ INDEX 関数の詳しい使い方は、こちらをご覧ください。
Microsoft Office Online のページに飛びます。
≪補足3≫

指定された範囲内が空白の場合、エラーが出ないようにするために、COUNTIF 関数を 判定用に使用しています。

COUNTIF 関数は、指定した範囲に含まれる空白以外のセルのうち、検索条件に一致するセルの個数を返します。
つまり、一件でも条件に一致するセルがあった場合は、判定が有効になります。

【書式】
COUNTIF(範囲,検索条件)

【今回の式】
COUNTIF(C3:H3,"*")

上記の式では、「範囲」 として、「1月〜6月」 のセル範囲を指定しています。
「検索条件」 として、何らかの文字があること 「"*"」 (ワイルドカード) を指定しています。

※ COUNTIF 関数の詳しい使い方は、こちらをご覧ください。
Microsoft Office Online のページに飛びます。
≪補足4≫

上記の式は、対象が文字列の場合に限ります。対象に数値が入力されている場合は空白となってしまいます。
対象が数値の場合は、こちら をご覧ください。


この頁のTOPへTop
【念の為の注意書きです。】
このページ(下位ページ含む)の全ての物の無断転載を禁止いたします。
又、当ページ(下位ページ含む)に記載されていることは、全て自己の責任において実行してください。

Copyright(C) pPoy 2010