Excelで商品名や型番から特定の数値を取り出そうとCopilotに数式を依頼した際、見慣れない記号が並んだ式が提案されることがあります。結果が正しくても、数式の中で何が行われているのか分からないまま運用するのは不安が残るものです。提示された数式に含まれる正規表現の仕組みと、各記号の役割を整理します。
この記事の結論
ExcelでCopilotが提示する数式に含まれる「[0-9]」や「¥.」などは、文字列のパターンを指定する正規表現です。文字の位置が不揃いなデータから数値や単位を抽出する際にREGEXEXTRACT関数やREGEXREPLACE関数とともに使われます。記号の役割を把握しておくことで、AIが生成した数式がどの条件で抽出を行っているか確認しやすくなります。
なぜCopilotは数式に記号を多用するのか？
セル内の文字列から特定の値を取り出す処理では、データの並び順が問題になります。「天然水 550ml 24本」「洗濯洗剤 詰替 1.8L 6個」「小麦粉 1kg 10袋」といったデータでは、商品名、容量、入数の文字数や出現位置がセルごとに異なっています。
文字の位置が一定でない場合、「左から何文字目」という指定では目的の数値を取り出せません。そこで用いられるのが「正規表現」です。正規表現は、「連続する数字」や「指定した文字以外」といった文字列の特徴や並び方の規則を指定してデータを検索・抽出する手法です。
Copilotは文字位置のばらつきに対応するため、文字列のパターンを判定できるREGEXEXTRACT関数やREGEXREPLACE関数と正規表現を組み合わせた数式を作成します。
容量を取り出す数式は2段階で文字を削り出している
容量を取り出すために提案される数式には、以下のような構成が見られます。
=--REGEXREPLACE(REGEXEXTRACT(LOWER(A2),"[0-9]+(¥.[0-9]+)? *(ml|l|kg|g)"),"[^0-9.]","")
この数式は、内側の処理で単位を含むまとまりを抜き出し、外側の処理で余分な文字を取り除く2段階の構成になっています。
内側のREGEXEXTRACT(LOWER(A2),"[0-9]+(¥.[0-9]+)? *(ml|l|kg|g)")では、まずLOWER関数で大文字を小文字に揃え、「500mL」のような大文字混じりの表記を「500ml」として扱えるようにします。
正規表現の[0-9]は「0から9のいずれか1文字」を表し、直後の+は「直前の条件を1回以上繰り返す」ことを意味します。この組み合わせによって「1桁以上の数字」が検出されます。
続く(¥.[0-9]+)?は小数部分への対応です。「¥.」はピリオドそのものを示し、「[0-9]+」で小数点の後に続く数字を指定しています。末尾の?は「直前のまとまりが存在してもしなくても一致する」という意味を持つため、「550」のような整数と「1.8」のような小数の両方に対応できます。
さらに *(ml|l|kg|g)の*は「直前の文字が0回以上繰り返される」ことを示すため、数値と単位の間にスペースがある場合もない場合も検出します。縦棒記号の|は「または」を表し、指定された複数の単位のいずれかに一致する部分を探します。この内側の処理により、「550ml」や「1.8l」といった文字列が抽出されます。
次に外側のREGEXREPLACE関数が動作します。第2引数に指定された"[^0-9.]"の角括弧内にある先頭の^は、「指定した文字以外」を意味します。つまり「数字とピリオド以外の文字」を検索し、第3引数の空文字列""に置き換えることで、末尾に残っていた「ml」などの単位文字を消去して数値部分だけを残します。
数式の先頭にある「–」は、取り出された文字列をExcel上で数値データとして認識させるための記述です。
| 記号 | 意味 | 数式内での役割 |
|---|---|---|
| [0-9] | 0から9のいずれか1文字 | 数字を検出する |
| + | 直前の文字・条件を1回以上繰り返す | 「[0-9]+」で1桁以上の連続した数字を表す |
| ¥. | ピリオドそのもの | 小数点文字をそのまま指定する |
| ? | 直前のまとまりがあってもなくてもよい | 「(¥.[0-9]+)?」で小数部分の有無に対応する |
| * | 直前の文字・条件を0回以上繰り返す | 「 *」で空白が0個以上ある場合に対応する |
| | | または（いずれか） | 「ml|l|kg|g」のように複数の単位候補を並べる |
| [^0-9.] | 数字と小数点以外の文字 | 置換で削除対象とする文字を指定する |
入数の取り出しや単位だけの抽出はどう動く？
入数を取り出す数式も、容量と同じ仕組みで構成されています。
=--REGEXREPLACE(REGEXEXTRACT(A2,"[0-9]+ *(本|個|袋)"),"[^0-9]","")
内側の処理では「1桁以上の数字＋任意のスペース＋本・個・袋のいずれか」というパターンで「24本」や「6個」を抽出し、外側のREGEXREPLACE関数で[^0-9]（数字以外の文字）を削除して数値だけを残しています。
数値ではなく「ml」や「本」といった単位だけを取り出したい場合は、文字の削り落としを行わずに抽出関数だけで処理が完結します。
=REGEXEXTRACT(LOWER(A2),"ml|l|kg|g")
=REGEXEXTRACT(A2,"本|個|袋")
取り出したい文字列の候補を|で並べてREGEXEXTRACT関数に渡すことで、一致した単位の文字がそのまま取り出されます。この場合は不要な文字を削除する処理が不要なため、REGEXREPLACE関数を組み合わせる必要はありません。
基本記号の把握が数式の動作確認につながる
正規表現のすべてのルールを暗記する必要はありません。しかし、代表的な記号が何を条件に指定しているかを把握しておくと、AIが提示した数式が想定通りの挙動をするか確認しやすくなります。
今回の数式と正規表現の基礎知識は、窓の杜の解説記事で報告された事例に基づいています。
提示された式をそのまま業務シートへ反映する前に、抽出対象となる単位の漏れがないか、整数と小数の両方が含まれる可能性に対応できているかなど、正規表現の指定条件と実データを照合する手順が確実な運用につながります。
あわせて読みたい関連記事
おすすめ 無料ExcelアドインnExToolsの機能と導入手順・注意点
現在の記事と同じくExcelの実務での活用や作業効率化を扱っており、数式や正規表現による処理の理解に加えて、アドインを通じた機能拡張の仕組みや注意点を把握する上で参考になるため。
コメント