Microsoft Excel2021 特定の文字列に含まれる数値の抽出について

Question

お世話になります。

Microsoft Excel2021を使用しております。

左の図のように無作為に並べられた文字列と数値の羅列の中から特定の文字が含まれるセルを抽出し、それに含まれる数値のみ右の表に一覧として反映させたいと考えています。

数値だけの抽出が難しいようでしたらセルの文字列毎反映でも構いません。

自分で色々調べてみた結果、列ごとのフィルター抽出などは理解できましたが、別の列に反映させる方法がわからず苦戦しております。

どうかご教授くださいませ。

Answer 1

こんにちは。

遅ればせながら、ワークシート関数を組み合わせた数式を使う方法も考えてみました。

ただし、皆さんが言われているように 1種類の数式で結果を求めるのは Excel2021 では無理があるかと思いますので、作業列を使い中間結果を求め、その結果から最終結果を得る方法になります。

それと、質問文で提示されている表は実際のデータとかなり違っているようですが、実際のデータの詳細が分かりませんので、以下は、提示されている表を対象にした具体的な方法になります。ご了解ください。

もし、もう興味が無ければ、以下全て読み飛ばしてください。

［１］作業列の作成

1. ここでは P列（空いている列ならどこでも可）を作業列として使い、P3セルに下記の数式１を入れます。
   ・数式１
   =OFFSET($A$3,MOD(ROW(A1)-1,9),INT((ROW(A1)-1)/9))

＜数式１の動作概要＞
この数式を行方向（下方向）にコピー・貼り付けしていくことで、ROW(A1) の部分が A列を使った行数のカウンターとして機能し、MOD(ROW(A1)-1,9) の部分で 9行（ 3行目から 11行目の行数 ＝ 9 なので）ごとに変化する、
0,1,2,3,4,5,6,7,8,0,1,2,3,4,5,6,7,8,0,1,2,3,4,5,6,7,8,・・・
といった数値が得られます。
同じく、INT((ROW(A1)-1)/9) の部分でも 9行ごとに変化する、
0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,・・・
といった数値が得られます。
これらの数値が、それぞれ OFFSET 関数に行数と列数の加算値として渡され、結果的に A3セルを起点とした縦一列のデータの並びが得られます。 2. P3セルに入れた数式を P65セルまでコピー・貼り付けします。
これにより、元のデータの個数分のセル範囲に A列から G列のデータが縦一列に並べて表示されます。

※元のデータが入っているセル範囲が A3:G11 で、このセル範囲は変わらないとのことですので、9行×7列で 63個のデータが有ることになりますから P3:P65 のセル範囲で全てのデータが埋まることになります。

結果は、図１のようになります。
・図１
P列全体が見えていませんが、実際には 65行目までデータが入っています。
尚、作業列の見た目が気になるようでしたら、列ごと非表示にしておいても数式の動作には影響ありません。

［２］最終結果を求める

※ここでは、2行目の見出し（埼玉、神奈川・・など）は事前に入力済みとします。

1. J3セルに下記の数式２を入れます。
   ・数式２
   =IFERROR(TRIM(SUBSTITUTE(FILTER($P3:$P65,IFERROR(FIND(J$2,$P3:$P65),0)),J$2,""))*1,"")

※スピルの働きで、この数式を入れるだけでご希望の数値の一覧が行方向（縦方向）に列挙されます。
※数式中に 2箇所ある $P3:$P65 は作業列のセル範囲指定になりますので、もし、作業列を別の列に変更する場合は該当のセル範囲に書き換えてください。

＜数式の動作概要＞
まず、IFERROR(FIND(J$2,$P3:$P65),0) の部分で、FIND 関数により $P3:$P65 のセル範囲に J$2セルの内容（ここでは「埼玉」の文字列）が含まれていればセル位置ではなくその文字位置の数値を返し、含まれていなければ IFERROR 関数の働きで数値の 0 を返した配列を作り出し、これを FILTER 関数に渡します。
尚、ここでの文字位置の数値は文字の有無を判定する数値として扱います。

FILTER($P3:$P65,IFERROR(FIND(J$2,$P3:$P65),0)) の部分では、渡された配列を基にして、$P3:$P65 のセル範囲から指定された文字列を含むセルだけを選び出した配列を作り出し、これを SUBSTITUTE 関数に渡します。
SUBSTITUTE(FILTER($P3:$P65,IFERROR(FIND(J$2,$P3:$P65),0)),J$2,"") の部分では、渡された配列の内容から J$2セルの内容（ここでは「埼玉」の文字列）のみ ""（空白の文字列）に変更し、更にこれを TRIM 関数に渡し余分なスペースを削除した配列とします。

最後に、出来上がった配列を *1 とすることで文字列から数値に変換しています。
尚、指定した文字列を含むセルが存在しなかった場合などには、該当の列にエラーが表示されますので、IFERROR 関数の働きでエラー表示だけを ""（空白の文字列）に変えています。 2. J3セルに入れた数式を列方向（右方向）に必要なだけコピー・貼り付けします。（ここでは N3セルまで貼り付けています。）
結果は、図２のようになります。
・図２
https://learn-attachment.microsoft.com/api/attachments/f3f70647-cf76-49b4-8b4d-b75583209a49?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/26515b1a-5613-432f-a1c4-5447afc82931?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">![画像](https://learn-attachment.microsoft.com/api/attachments/26515b1a-5613-432f-a1c4-5447afc82931?platform=QnA

Answer 2

ご参考までに。

VBAの公式サポート場所は海外となっています。

 VBA(マクロ)に関しては、海外コミュニティで聞いて下さい。

・Stack Overflow(VBA)

　　[VBA プログラミングに関する質問]

　　Stack Overflow には、説明的なタイトル、完全で簡潔な問題ステートメント、問題を再現する
　　ための十分な詳細の要求などのガイドラインがあることに注意してください。
　　機能要求または過度に広範な質問は、トピック外と見なされます。
　　新しいユーザーの場合は、Stack Overflow ヘルプ センター にアクセスして詳細を確認して下さい。

※ 閲覧者・回答者が多い公式サポートサイトをお勧めします。

　 英語で質問を送信してください。

Answer 3

元データの内容に規則性があり、セル範囲も固定であればマクロは作りやすいと思います。

頑張ってください。

※個人的には（コマンドなどのサンプルははともかく）マクロのサンプルなど動くコードの提供までこのフォーラムで行う気は無いので、サンプルが必要であれば別の場所で質問するか、他の方が提供してくれるのを待ってください。

Answer 4

申し訳ありません。なにぶん不慣れなもので質問の仕方が不親切でした。

そもそものデータの入力から見直したほうがよさそうですね…。

含まれる都道府県名は今スクリーンショットに出ているだけですか？

こちら質問するために作成したサンプルのため、実際は別の単語が入ります。種類も数十種類あります。

後ろの数字は必ず半角2桁ですか？

実際は小数点一桁の数字が入ります。0.1～2.0のいずれかになります。

都道府県名と数字の間は半角英数字1つでしょうか？

半角英数字１つになります。

元データの範囲は可変ですか、固定ですか？

データは固定になります。

以上適切なお答えができているか定かではありませんが、ご確認ください。

Answer 5

このデータの持ち方だと、ワークシートの機能（ワークシート関数）だけでは難しいでしょう（多分無理）。

マクロ（VBA）で処理するしかなさそうですが、元データの形式や要件が分からないとサンプルも作れませんね。

含まれる都道府県名は今スクリーンショットに出ているだけですか？

後ろの数字は必ず半角2桁ですか？

都道府県名と数字の間は半角英数字1つでしょうか？

元データの範囲は可変ですか、固定ですか？