式の解読のお願いm(_._)m

あるサイトで見掛けた
桁が不揃いの“数字で始まる文字列”を数値のみへの
変換式が記載されていました。

実に簡単、かつ、お見事と思うのですが、私には難解過ぎるのです。
添付図に実例を示しましたが、どなたか、解読のほどをお願いします。

A列の「数字で始まる文字」と、
B列に「変換された数値」を、それぞれ示しました。
A列は表示通りの入力でも、式 =数字&"文字列" でも構わないようです。

よろしくお願いします。

No.3 ベストアンサー 回答者： goomania 回答日時： 2024/02/23 17:41

長文で失礼します。

ご質問のような疑問が生じたときは、EXCELがその数式をどのように解釈して計算を進めているのか、ステップを踏んで確認するのが近道です。

メインメニューの数式タブ→「数式の検証」を選択すると添付画像のようなダイアログが開くので、「検証」ボタンなを押すと、ステップを踏んで計算がどのように進められているのかを確認できます。

画像①をご覧ください。見にくいので以下のURLにも掲出しておきます。
https://gyazo.com/98b7c46d1edb5baaf6b41c036ee40dd3

ご質問者のご指摘のとおり、
<「1行目から4行目までの長さの部分文字列を取得」するのは数字だけでなく、文字をも含めてしまう･･･>
という状態になっているのが判ります。
画像②で、
<「*1」を適用するとエラーになる筈です･･･>
というご指摘のとおり#VALUE!エラーになっています。

しかし、LOOKUP関数は、検索値が数値であるか、文字列であるかを区別し、かつ、検索対象中のエラーを無視します。
従って、検索対象の中にエラーがあっても問題ないことになります。

また、LOOKUP関数で検索対象が順序良くに並んでいて(数値のみで桁数が順次増えていくので必ず規則的並びになります)、検索値が存在しない場合(この数式は、検索値を極めて大きな数値にして、敢えてそのような状態を作り出している訳ですが)、最も後ろにある検索対象値が返ります。

ご質問の数式の場合、A列は「左側に数値、右側に文字」という並びとなっている(数値と数値以外が混在した並びの文字列ではこの数式は正しい結果になりません)ので、その文字列の左側から1桁から最大4桁までの数値を抽出することになり、最も後にある数値(最も桁数の多い数値)が抽出されるということになります。

その結果「桁が不揃いの“数字で始まる文字列”を数値のみへの変換」が実現するという訳です。

但し、
=LOOKUP(10^308,MID(A3,1,ROW($1:$4))*1)
という数式には辻褄のあわない部分が存在します。
ご質問者のご指摘のとおり、「1行目から4行目までの長さの部分文字列を取得」するので、仮にこの部分が全て数値であったとしても9999が最大値です。
ですからROW($1:$4)としている以上、LOOKUP関数で検索対象に存在しない大きな検索値を想定する場合、9999を超える数を考えればよいことになります。

おそらく64ビットの倍精度浮動小数点数での最大値を意識したと思われる10^308というとてつもなく大きな数値にする必要はなく、10^4で足りるということです。
つまり、ROW($1:$4)なら10^4を検索値にすれば足りるし、ROW($1:$10)なら10^10を検索値にすれば足りるということになります。

また、結果をB列のセルに表示する以上、EXCELの一つのセルの数値の有効桁数の制限を受けます。EXCELの一つのセルの数値の有効桁数は15桁です。
従って、検索値の10^308に合わせてROW($1:$308)として長い文字列から桁数の多い数値を抽出しようとしても、B列の数値は15桁以降は0が並ぶだけになってしまいます。

例えば、クレジットカード番号の桁数でも16桁ありますので、「1234567890123456VISA」というような文字列から「=LOOKUP(10^308,MID(A3,1,ROW($1:$308))*1)」で16桁の数値を抽出しようとしても、結果は「1234567890123450」となって末尾が0になってしまいます。

つまり抽出結果が元の数字と異なり、結果が保証されないことになってしまいますので、この数式はLOOKUP関数の検索値を10^15より大きく＜ROW($1:$15)より拡大＞することには意味がないことになります。

No.4 回答者： goomania 回答日時： 2024/02/23 21:30

No.3です。

前回回答の説明の中に、

>検索値が存在しない場合･･･、最も後ろにある検索対象値が返ります。

との表現がありますが、誤解を生む表現でしたので修正いたします。
その後の説明で詳細を解説しいているのですが、上記文章だけ読むと誤解のもとです。

【検索値が検索範囲の最大値より大きくて、検索対象範囲に】検索値が存在しない場合、･･･最も後ろにある検索対象値が返ります。

という表現が正しいことになります。
お詫びして、修正いたします。

この回答へのお礼

goomaniaさん、
[数式]⇒[ワークシート分析]⇒[数式の検証]
こんな便利なツールが Excel に装備されていることを、今まで知りませんでした。
初歩的な質問をして、こんなにも有益な情報をいただいて、熱く俺を申し上げるしだいです。
ありがとうございました。m(_._)m

お礼日時：2024/02/23 23:59

No.2 回答者： fujillin 回答日時： 2024/02/23 15:42

こんにちは

すでに回答があるようですけれど・・・

＞私には難解過ぎるのです
配列計算さえ分かれば、さほどではないと思います。
あとは、少しだけエクセル関数の癖（？）のような部分を利用しているところもありますが。
式は短いものですが、分解して考えてみましょう。

ご提示の式は、LOOKUP(n, 配列) の形式になっています。
（「配列」に関しては後述）
https://support.microsoft.com/ja-jp/office/looku …
配列から値を検索する関数ですが、検索値に大きな値（=∞）を設定すれば、最大の値が返されることになります。
（実際には∞に代えて、10^308を使用しています）
正の値で考えるなら、最大値を得るのにMAX()やLARGE()を利用すれば、わかりにくい「10^308」などを使用しなくてもよくなるのですが、残念ながら、これらは配列の中にエラー値があるとエラーを返します。
その点、LOOKUPはエラー値を無視してくれるので、とても都合が良いのです。

また、LOOKUP関数は、配列がソートされている前提で検索していますので、ソートされていないと「最後の数値」を返すことになります。
例えば、「-123AB」では、「-1」、「-12」、「-123」では-1の方が大きいのですが、逆順になっているので、最後に出現する「-123」が返されます。
この処理は、「文字数の一番多い数値」を返すことになり、結果的に「数値として解釈できる一番長い文字列」を返してくれることになります。


さて、わかりにくいのは「配列」の方かも知れませんが、「1列のセル範囲内の値」と同様に考えれば理解しやすいと思います。
＞MID(A3,1,ROW($1:$4))*1
は、「A3セルの文字列の最初から1文字、2文字・・をとったものを数値化した配列」と考えることができます。
ですので、わざわざMID関数にしなくても
　LEFT(A3,ROW($1:$4))*1
としても、同様の結果が得られます。
例えば、A3セルの値が「939kg」の場合に、仮想のセル範囲（E1:E4セルを想定）
　E1：9
　E2：93
　E3：939
　E4：939k
となっていると考えれば良いです。
「*1」の演算で数値化していますので、上記のE4セルは計算後は「#VALUE!」値になります。
（これを利用して、数値として解釈できない範囲を省いています）
最初のLOOKUP関数の第二引数に、上記のE1:E4を指定していると思えばよいのですが、実際はセル範囲ではなく、メモリー上の配列を対象として計算を行っています。

一方で、ご提示の式では、
＞ROW($1:$4)
と1～4文字限定になっていますので、例えば「123456」が対象の文字列であっても、4文字目までの「1234」しか解釈しないため、結果は「1234」となります。
仮に、何文字でも可能にするには、範囲を大きくしておけば良いですが、それだとほとんどの場合は無駄な計算を繰り返すことになって、計算負荷が増大します。
適切に対応するには、LEN()で元の文字数を取って、その文字数迄にすれば良いですが、数式は少々複雑になります。


＜おまけ＞
ご提示の式は「文字列の最初から」を前提にしていますので、（当然ですが）「A12BC」のような文字列から「12」を抜き出すことはできません。（#N/Aになります）
また、（多分）作者さんが想定していない文字列が対象だと、一寸変わった結果になります。
例えば、
　3/3/3/3　→　45354
　2e4f6g　→　20000
　5-2-1番地　→　45414
とか。

この回答へのお礼

fujillinさん、こんにちは。
種々の有益な情報を多謝です。m(_._)m
大変勉強になりました。

余談めきますが、…
》 わかりにくい「10^308」などを使用しなくても…
この 10^308 ですが、私はコレをLOOKUP関数の最大検索値として頻用しています。10^309 は #NUM! を呈するから。
文字列の最大検索値としては「黑」を使用します。この字が私が知る文字コードの最大値だから。=CODE(黑)=38700

実は今まで ROW($1:$4) が配列を意味していることも知りませんでした。
これを {1;2;3;4} に置き換えて初めて配列であることを知った次第です。
(*^_^*)
今回の場合、ROW($1:$4) を COLUMN(A:D) に置き換えても同じであることも。

お礼日時：2024/02/23 23:41

No.1 回答者： banzaiA 回答日時： 2024/02/22 20:49

ＧＴＰで検索すると以下のように表示されました。

以下、引用です。
このExcelの式は、セルA2の文字列を数値に変換し、その数値が最初の4行のどれに対応するかを調べるためのものです。具体的には以下のような動作をします：

MID(A2, 1, ROW($1:$4))：セルA2の文字列の最初から、1行目から4行目までの長さの部分文字列を取得します。つまり、1文字目から1文字、1文字目から2文字、1文字目から3文字、1文字目から4文字の4つの部分文字列を取得します。
*1：取得した部分文字列を数値に変換します。これはExcelでは、文字列を数値に変換する一般的な方法です。
LOOKUP(10^308, ...)：変換した数値の中から、10308以下の最大の数値を探します。10308はExcelで表現できる最大の数値なので、基本的には変換した数値の中の最大値を探すことになります。
したがって、この式全体の意味は、「セルA2の文字列を最大4桁の数値に変換し、その中の最大値を探す」ということになります。ただし、セルA2の文字列が数値以外の文字を含む場合や、5桁以上の数値を含む場合は正しく動作しないことに注意してください。また、この式は配列式なので、入力する際にはCtrl+Shift+Enterを押す必要があります。

この回答へのお礼

banzaiAさん、コメント多謝です。m(_._)m

MID(A2,1,ROW($1:$4)) の式で理解できないことがあります。
添付図(の A4、A8、A10)に示した 「6684ｇ」、「7149km」、「1071km」の場合は
「1行目から4行目までの(数字の)長さの部分文字列を取得」するのは(後で *1 を実行して数値化)分かりますが、
A5，A9 の「8ton」、「9rpm」の場合は「1行目から4行目までの長さの部分文字列を取得」するのは数字だけでなく、文字をも含めてしまうからです。
文字を含めた「8ton」、「9rpm」に「*1」を適用するとエラーになる筈ですが、如何でしょうか？

お礼日時：2024/02/23 07:23