PR
私が使っているPCガジェット類
作業環境で実際に使っている・気になっている周辺機器などをまとめました
※ 一部のリンクは広告(アフィリエイト)を含みます
【Excel】URLの値の一部を取得したい
Microsoft 365のExcelを使っているためご利用のバージョンでは使えない機能や関数がある可能性がありますのでご了承ください
目次
やりたいこと
突然ですがURLにはいろんな情報がありますよね（ねっ！？）
例えばnetkeibaさんの競走馬情報ページのURLがコチラ
https://db.netkeiba.com/horse/2021105743
一番最後に競走馬IDが記載されていてここを関数だけで取得する方法です
（今回SUBSTITUTEで直前まで置換する方法は趣旨からそれるため受け付けません）
関数の説明
まず結論から、TEXTSPLITとCHOOSECOLSを組み合わせることで取得することができます
↓の画像はA列は数式を文字列で、B列はその結果を表示しています（2行目はスピルなのでF列まで値が入っているように見えています
TEXTSPLITについて
まずは関数のヘルプリンクの先にあるMS公式のページから
列と行の区切り記号を使用してテキスト文字列を分割します。
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
https://support.microsoft.com/ja-jp/office/textsplit-%E9%96%A2%E6%95%B0-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7?ns=excel&version=90&syslcid=1041&uilcid=1041&appver=zxl900&helpid=xlmain11.chm60673&ui=ja-jp&rs=ja-jp&ad=jp
- text 分割するテキスト。 必ず指定します。
- col_delimiter 列間でテキストをスピルするポイントを示すテキスト。
- row_delimiter テキストを下の行に書き込むポイントを示すテキスト。 省略可能です。
- ignore_empty 連続する区切り記号を無視するには TRUE を指定します。 既定値は FALSE で、空のセルが作成されます。 省略可能です。
- match_mode 大文字と小文字を区別しない一致を実行するには、1 を指定します。 既定値は 0 で、大文字と小文字を区別する一致が行われます。 省略可能です。
- pad_with 結果を埋め込む値。 既定値は #N/A です。
何となくMS公式ヘルプってわかりにくいイメージがあったんですが関数のヘルプはちゃんとわかりやすい説明になってました
強いて付け加えるなら返値が配列（スピル）ってことぐらい
引用元見てもらえればわかるのですがdelimiterは配列表記（”{}”で囲む）することで複数条件が指定できるのもポイント
この指定をすることでもう少し複雑なURLの情報も分割することができます
CHOOSECOLS
一昔前のエクセルにはなかった配列の考え方もスピルの登場によってメジャーになった気がします
こちらもヘルプリンクの先にあるMS公式のページから
配列から指定された行を返します。
=CHOOSECOLS(array,col_num1,[col_num2],…)
https://support.microsoft.com/ja-jp/office/choosecols-%E9%96%A2%E6%95%B0-bf117976-2722-4466-9b9a-1c01ed9aebff?ns=excel&version=90&syslcid=1041&uilcid=1041&appver=zxl900&helpid=xlmain11.chm60686&ui=ja-jp&rs=ja-jp&ad=jp
- 配列 新しい配列で返される列を含む配列。 必ず指定します。
- col_num1 返される最初の列。 必ず指定します。
- col_num2 返される追加の列。 省略可能です。
ここでは”配列＝スピル”の認識でOK
左から（類似のCHOOSEROWなら上から）何番目かを指定すればその値を抽出することができる
ポイントはマイナスの値を指定すると逆から参照できるので一番最後の値を取得したいなら”-1″を指定すると配列が可変でも対応できる
あとがき
昔似たようなことをしようとして、区切り位置機能で分割してHLOOKUPとCOUNTAの組み合わせで無理矢理引用していることもありました
プログラミングやってる人なら馴染み深い配列がExcelの標準機能に組み込まれたのでやれることの幅が広がりましたよね
関数で実現できるってことはテンプレ作ってしまえば手作業なしでできるツールも作れるので参考になれば幸いです
最後に・・・
PythonとExcelを中心に仕事に役立つ業務ツールや自動化、スクレイピングツールの作成を受注していて、クラウドワークスでは気が付けば100件以上のお仕事を受注してきました！
会社員をやりながらの副業なので時間の捻出は相応ですが、クライアントの方々と近い立場でこちらからも提案しながら活動していますのでお悩みあれば是非ご相談ください
コメント