指定範囲内に何か文字列が入力された場合その文字を抽出 - エクセル(Excel)関数

指定範囲内に何か文字列が入力された場合その文字を抽出 - エクセル(Excel)関数

セルを範囲で指定して、その範囲のセル内に何が入力された時、そのセルの文字列を抽出する方法

エクセル表サンプル
B列に何か入力されたらE列にその文字列を表示
　 A B C D E F
1 　 　 　 　 　 　
2 　 　 　 行番号 文字列 　
3 　   　  5  有給 　
4 　   　  8  代休 　
5 　  有給   12  入院中 　
6 　   　  　 　 　
7 　   　  　 　 　
8 　  代休   　 　 　
9 　   　  　 　 　
10 　   　  　 　 　
11 　   　  　 　 　
12 　  入院中 　  　 　 　
13 　   　  　 　 　
エクセル表サンプルの2列目に何かが入力された場合
MATCH関数を使います
・関数MATCH（読み方 - マッチ）
・書式=SUMIF(検査値,検査範囲,照合の型)
・内容検索条件に一致するセルの位置を数値で表します。照合の型は省略すると既定値は1です。
通常MATCH関数は一致する文字列を探しますが
文字列が特定していない場合
ワイルドカードで「*」をつけると
「*美」：美で終わる文字列（美の前の複数の文字は無視されます）
「美*」：美で始まる文字列（美の後の複数の文字は無視されます）
「*美*」：美を含むセルをカウントします

ワイルドカードは「*」複数「?」は1文字です
「?美」：美の前に1文字
「??美」：美の前に2文字
「???美」：美の前に3文字

「*」とすることで何かが入力されたセルになります
列を指定した場合
=MATCH("*",B:B,0)
B列のセルに何かが入力された位置を返します
結果：5

範囲を指定した場合
=MATCH("*",B3:B13,0)
B3～B13のセルに何かが入力された位置を返します
結果：3

※ワイルドカードは文字列にしか使えません。0など数値のみのセルは見つけられず無視されます（B列をセルの書式設定で「文字列」に指定すれば見つけられます） また、文字列なので照合の型「0」しか使えません。MATCH関数の照合の型「-1」と「1」は数値のみ有効です

上記で文字列が入力されている位置が数字で取得できたので
=MATCH("*",B:B,0)
に列のBを付けて
="B"&MATCH("*",B:B,0)
とすればよさそうに見えますがこれは単なる文字列として扱われセル番号としては認識されません。

セル番号に変えるには INDIRECT関数を使います
上記の式を丸ごとINDIRECT()の中に入れるとセルの番号として認識されます

列指定の場合
セルD3 =MATCH("*",B:B,0)
セルE3 =INDIRECT("B"&MATCH("*",B:B,0))
結果：セルD3=5　セルE3=有給

範囲指定の場合
セルD3 =MATCH("*",B3:B13,0)+2
セルE3 =INDIRECT("B"&MATCH("*",B3:B13,0)+2)
結果：セルD3=5　セルE3=有給
2個目を取得

列を指定する方が簡単ですがMATCH関数には開始位置のオプションは無いので2個目、3個目を取得するには範囲を指定しないと取得できません

検査範囲の開始位置を最初に見つけたセルの下からスタートさせます

=MATCH("*",B3:B13,0)
これで最初の文字列を見つけます結果「3」

もともと3行目なので上の2行の「2」と取得したセルの1個下の「1」を足します
MATCH("*",B3:B13,0)+3
これが次の開始の行の番号になります

セル番号にするにはINDIRECT関数で「B」と行の番号を連結させます
=INDIRECT("B"&MATCH("*",B3:B13,0)+3)
これが「B6」になります

1つ目の式の開始の「B3」を上記の式に変更します
=INDIRECT("B"&MATCH("*",B3:B13,0)+2)
開始の「B3」を上記の式に変更します
=INDIRECT("B"&MATCH("*",INDIRECT("B"&MATCH("*",B3:B13,0)+3):B13,0)+2)

これで中身は
=INDIRECT("B"&MATCH("*",B6:B13,0)+2)

このままだとB6からの位置を出しますからB6より上のセル分
MATCH("*",B3:B13,0)
を足します
=INDIRECT("B"&MATCH("*",INDIRECT("B"&MATCH("*",B3:B13,0)+3):B13,0)+MATCH("*",B3:B13,0)+2)

セルD4）行の合計
=MATCH("*",INDIRECT("B"&MATCH("*",B3:B13,0)+3):B13,0)+MATCH("*",B3:B13,0)+2
結果：8

セルE4）2個目を取得
=INDIRECT("B"&MATCH("*",INDIRECT("B"&MATCH("*",B3:B13,0)+3):B13,0)+MATCH("*",B3:B13,0)+2)
結果：代休
3個目を取得

2個目と同じように検査範囲の開始位置を2個目に見つけたセルの下からスタートさせます

2個目を取得しているD4のセルの下8+1がスタート位置になります
=MATCH("*",INDIRECT("B"&MATCH("*",B3:B13,0)+3):B13,0)+MATCH("*",B3:B13,0)+2+1
結果：9

Bを付けてセル番号にします
=INDIRECT("B"&MATCH("*",INDIRECT("B"&MATCH("*",B3:B13,0)+3):B13,0)+MATCH("*",B3:B13,0)+3)
セル番号の結果：B9（セル番号のB9なので空の場合0になります）

元の1個目の取得の数式
=INDIRECT("B"&MATCH("*",B3:B13,0)+2)
この「B3」が「B9」になり
=INDIRECT("B"&MATCH("*",B9:B13,0)+2)
B9より上のセル分（D4）を足します
=INDIRECT("B"&MATCH("*",B9:B13,0)+8)
この式で
B9
=INDIRECT("B"&MATCH("*",INDIRECT("B"&MATCH("*",B3:B13,0)+3):B13,0)+MATCH("*",B3:B13,0)+3)
+8
=MATCH("*",INDIRECT("B"&MATCH("*",B3:B13,0)+3):B13,0)+MATCH("*",B3:B13,0)+2

上記を組み合わせます
セルE5


4個目、5個目も同じ要領で次のセル番号とその上の行数を取得すれば呼び出せますがかなり長い式になります
まとめ
セルを範囲で指定して、その範囲のセル内に何が入力された時、そのセルの文字列を3つまで抽出する方法

1個目
列で取得
=INDIRECT("B"&MATCH("*",B:B,0))

範囲を指定して取得
=INDIRECT("B"&MATCH("*",B3:B13,0)+2)

2個目


3個目
もっとたくさんある場合

何か文字が入力されているセルを
CONCATENATEとSORTを使って並び替えて取得

CONCATENATEとSORTを使って並び替えて取得

セル）E21
=SORT(CONCATENATE(B21:B32),1,-1)

SORTだけだと空白が0になってしまうのでCONCATENATEを使用しています

※SORTとCONCATENATEは新しいエクセルのSPILL機能を使用していますので古いエクセルでは使用できません
参考：CONCATENATEとCONCATと&の違い
・1,2,3,4の4つの数字で12桁のランダム数を作成
・複数列の条件が一致している数を出す方法
・セルの列をアルファベットで取得
・関数内のセル参照を別シートから数式で出す方法
・50個のセルが次のセルと同じになる確率
・指定の文字列を含むセルを抽出とセルに警告
・指定範囲内に何か文字列が入力された場合その文字を抽出
・消費税8%の価格を10%にする方法とCEILING関数
指定範囲内に何か文字列が入力された場合その文字を抽出＞HOME

エクセル(Excel)関数

(C) 2024 Digital World