No.3ベストアンサー
- 回答日時:
長文で失礼します。
ご質問のような疑問が生じたときは、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
- 回答日時:
No.3です。
前回回答の説明の中に、
>検索値が存在しない場合・・・、最も後ろにある検索対象値が返ります。
との表現がありますが、誤解を生む表現でしたので修正いたします。
その後の説明で詳細を解説しいているのですが、上記文章だけ読むと誤解のもとです。
【検索値が検索範囲の最大値より大きくて、検索対象範囲に】検索値が存在しない場合、・・・最も後ろにある検索対象値が返ります。
という表現が正しいことになります。
お詫びして、修正いたします。
goomaniaさん、
[数式]⇒[ワークシート分析]⇒[数式の検証]
こんな便利なツールが Excel に装備されていることを、今まで知りませんでした。
初歩的な質問をして、こんなにも有益な情報をいただいて、熱く俺を申し上げるしだいです。
ありがとうございました。m(_._)m
No.2
- 回答日時:
こんにちは
すでに回答があるようですけれど・・・
>私には難解過ぎるのです
配列計算さえ分かれば、さほどではないと思います。
あとは、少しだけエクセル関数の癖(?)のような部分を利用しているところもありますが。
式は短いものですが、分解して考えてみましょう。
ご提示の式は、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) に置き換えても同じであることも。
No.1
- 回答日時:
GTPで検索すると以下のように表示されました。
以下、引用です。この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)に示した 「6684g」、「7149km」、「1071km」の場合は
「1行目から4行目までの(数字の)長さの部分文字列を取得」するのは(後で *1 を実行して数値化)分かりますが、
A5,A9 の「8ton」、「9rpm」の場合は「1行目から4行目までの長さの部分文字列を取得」するのは数字だけでなく、文字をも含めてしまうからです。
文字を含めた「8ton」、「9rpm」に「*1」を適用するとエラーになる筈ですが、如何でしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBAで、特定の文字より後を削除して残った数値を文字列に変換と特定の文字より前も削除したい 3 2022/04/15 19:21
- Excel(エクセル) <スプレッドシート>IF関数の複数条件について 5 2022/10/27 14:38
- その他(コンピューター・テクノロジー) 【Tableau Desktop】文字列から8桁の数字を日付型(yyyyMMdd)として取得 1 2023/07/31 10:17
- Visual Basic(VBA) A列にある値をB列・C列にVBAで切り出し 3 2022/04/09 19:20
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/09 08:20
- Excel(エクセル) エクセルのセルの書式設定・ユーザー定義の条件設定について 1 2022/08/17 21:56
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/15 08:30
- Visual Basic(VBA) VBA B列にある前から10文字のみ表示 3 2023/08/07 11:24
- Excel(エクセル) Excel 値を返す数式についてです 3 2022/11/21 20:08
このQ&Aを見た人はこんなQ&Aも見ています
-
「どうして捨てられないの?」前妻の物を捨てられない男性の心理って?
前妻の物を捨てられない理由に加え、捨てるための手段はあるのかを専門家に聞いてみた!
-
“丸(〇/○/◯)”に似た文字…
Excel(エクセル)
-
Excel 大なり小なりを表すとき、 例えば「10以上」なら、>=10 と表せますが、 この10の部
Excel(エクセル)
-
excelの不要な行の削除ができない!
Excel(エクセル)
-
-
4
エクセルで2桁から5桁の数字があります。その数字のダブリを解消する方法を教えてください。
Excel(エクセル)
-
5
Excelでこういう年を切り替えられる簡易なカレンダーを作ったのですが、 年や月を変えると、色が付い
Excel(エクセル)
-
6
Excelの関数で起きた現象の原因がわかりません。
Excel(エクセル)
-
7
エクセルのセル統合について
Excel(エクセル)
-
8
空白のないドロップダウンリストの作り方
Excel(エクセル)
-
9
Excelの関数に関する質問です
Excel(エクセル)
-
10
エクセルで列をアルファベットではなく、数字の表示にするメリットって、なんでしょうか?
Excel(エクセル)
-
11
データの入力規則 Excelです。 例えば、 C1セルに、 A列のデータ(B列にあいうえお"と入っ"
Excel(エクセル)
-
12
エクセルの関数でわからないことあるのでコード付きで教えてください
Excel(エクセル)
-
13
Excel関数かなにかでスムーズに処理するにはあなたならどうしますか?
Excel(エクセル)
-
14
IF関数
Excel(エクセル)
-
15
Excelのテーブルでmatch関数の使い方について
Excel(エクセル)
-
16
SUMIFでの判定条件が異なるために正確に計算されない
Excel(エクセル)
-
17
VBA(えくせる)ってなんでメンテできない人が多いんですか?
Excel(エクセル)
-
18
Excelの関数に関して質問です。 1行目の関数を下まで反映させる場合、ベーシックなやり方としては1
Excel(エクセル)
-
19
表示されている人数だけを数えたい!!
Excel(エクセル)
-
20
Excel関数の先頭に「@」が入っているのは何故?
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
エクセルの表から正の数、負の...
-
VBAで文字列を数値に変換したい
-
エクセルで最初のスペースまで...
-
EXCELで 一桁の数値を二桁に
-
Excelで半角の文字を含むセルを...
-
2つのエクセルのデータを同じよ...
-
エクセル(勝手に太字になる)
-
エクセル 同じ値を探して隣の...
-
エクセルで文字が混じった数字...
-
文字列に数字を含むセルを調べたい
-
エクセル 文字数 多い順 並...
-
条件付き書式の適用先が変更さ...
-
エクセル 時間帯の重複の有無
-
「B列が日曜の場合」C列に/...
-
エクセルのセル内の文字の一部...
-
エクセルで、列の空欄に隣の列...
-
【エクセル】指定した文字以外...
-
エクセルの並び変えで、空白セ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
エクセルで最初のスペースまで...
-
2つのエクセルのデータを同じよ...
-
エクセル 文字数 多い順 並...
-
VBAで文字列を数値に変換したい
-
エクセル(勝手に太字になる)
-
エクセルで文字が混じった数字...
-
「B列が日曜の場合」C列に/...
-
エクセルの表から正の数、負の...
-
Excelで半角の文字を含むセルを...
-
エクセル 同じ値を探して隣の...
-
Excel、市から登録している住所...
-
エクセルの項目軸を左寄せにしたい
-
EXCELで 一桁の数値を二桁に
-
VBA 連続行データを5行ずつ隣の...
-
エクセル 時間帯の重複の有無
-
エクセルで、列の空欄に隣の列...
-
オートフィルターをかけ、#N/A...
-
A列がない・・・A列が非表示に...
おすすめ情報