配列数式の動作異常？MATCH及びMIN

質問

お世話になります
Windows7、エクセル2016です。

概ね100000件には
到底達しない範囲の
件数不定で
B列の3行目から下方に
店名●〇　、価格　157円、日付　2014/3/14。
店名×〇　、価格　305円、日付　2014/5/1。
店名●〇　、価格　156円、日付　2014/5/3。
等と、いったデータが
文字列で入力されています。

故あって
最安価格を読み出す必要がでた
と、します

其処で
=MIN(IFERROR(VALUE(MID($B$1:$B$100000,FIND ("単",$B$1:$B$100000)+2,FIND ("数",$B$1:$B$100000)-FIND ("円",$B$1:$B$100000)-2)),999999999999999))
と、してみました。

が、
上手く行きませんでした。

此の時
結果が何故か
999999999999999
と、なりました。

PF9で確認しても
同じでした

其処で
色々な場所に
INDEX文を挟んで見ました
ですが、だめでした

=VALUE(MID($B$3:$B$10,FIND ("単",$B$3:$B$10)+2,FIND ("数",$B$3:$B$10)-FIND ("円",$B$3:$B$10)-2))
此れをPF9で見ると
ちゃんと値が出ていました

また、
上記式を
Ctrl+alt.+Enterで確定すると
ちゃんと最小値が出ました

にも、関わらず
です。

何故でしょうか？

また、
C3に
日付形式で記載された
特定日以降の
直近最初の売買単価
其れを読み出そう
と、

=VALUE(MID(OFFSET($B$0,MATCH(C3,INDEX(IFERROR(DATEVALUE(MID($B$3:$B$100000,FIND ("日",$B$3:$B$100000)+2,FIND ("。",$B$3:$B$100000)-FIND ("円",$B$3:$B$100000)-2)),0),,),-1)-1,0,1,1),FIND ("単",$B$3:$B$100000)+2,FIND ("数",$B$3:$B$100000)-FIND ("円",$B$3:$B$100000)-2))
と、してみました。

此の時、
MATCH文の最終項を1
と、すると
直近直前のデータが読み出されました

が、同様に
MATCH文の最終項を-1
と、すると
データ候補が
目視確認できる
にも関わらず
確かエラーが出ました

何故、こんな事に
なるのでしょうか？

以上2点、お教えください

回答No.4

bunjii · ベストアンサー

>ですかね？
>此で如何でしょうか？
空白や改行が数式内に含まれると正しい数式と認められませんので、Excelの数式バーからコピーした文字列を直接貼り付けてください。
OFFSET関数の参照セルを$B$0としていますがそれは認められませんので$B$1にしてください。従って、行位置の隔たりを調整する必要があるでしょう。（ご自身でチェックしてください）
また、前回の回答でMATCH関数の照合の種類と並び順を説明しましたが、ご理解いただけていないようです。
照合の種類が-1の時は検索範囲の値が降順（大きい順）にソートされていなければなりませんが、提示の模擬データでは昇順（小さい順）になっています。

>あと、MINの話ですが前はINDEX文を介在させるとEnterでの通常確定で結果が出た
>今回は何故此が出来無いのでしょうか？
複数の関数を組み合わせた数式で引数が関数の演算結果であり、然も、配列値となる場合は基本的にCtrl+Alt+Enterキーで確定することになるようです。
=MIN(INDEX(IFERROR(VALUE([文字列]),[数値]),0))
上記の数式モデルでVALUE([文字列])がエラーを含む配列値であるため、IFERROR関数を経由してINDEX関数へ配列値を引き渡すことになり、暗黙の了解事項が成立しないようです。
INDEX関数は配列を引数として扱うことは暗黙の了解事項であり、四則演算や論理演算の結果を配列として扱うことは暗黙の了解事項とされていることのようです。
関数の演算結果も暗黙の了解事項で処理されるケースもありますが、暗黙の了解事項が認められないことも多くあります。（例示は調査が必要なので省略します）
トライ&エラーで対処してください。

お礼

有り難うございます

補足

敢えて口にするほどの事でもない事ですが
〉空白や改行が…
式中に空白や改行が入っても
問題ないですよ

〉Enterキーで確定することにな…
そうでもないですよ

回答No.3

imogasi

#２のご回答のようなのがご希望だろうが、私はむつかしい方法を選択しておいて、質問とその回答を複雑にしているように思う。多分「聞いていることに答えろ」と質問者がいうのは、予想されるが、あえて私は下記を薦めたい。
（１）まずテキストの形式の、エクセルデータに適さないデータの状況を、エクセルのデータらしくする。
まずデーター区切り位置で「、」でf各フィールドに分ける。
そして
（２）価格は関数で=SUBSTITUTE(SUBSTITUTE(B4,"価格",""),"円","")*Iなどで
価格　157円　を157にする。
日付は=SUBSTITUTE(SUBSTITUTE(C3,"日付",""),"。","")などで
日付　2014/3/14。ーー＞2014/3/１４にする。日付として使うならさらに加工が必要かも
しれない。
店名は略（「店名」がついていても良いだろうから）。
（３）挿入ーピボットテーブルで
例データとしてたとえば
店名 価格
A 23
B 34
A 12
B 11
C 32
A 41
A 42
で、
行　　店名
Σ値は　最小値/価格
を指定して、結果は
行ラベル 最小値 / 価格
A 12
B 11
C 32
総計 11
のように区分ごとの最小値が簡単に出る。
１０万行ぐらいなら、数量的な心配はないだろう。
ーー
＃１に対する、質問者のお礼を読んで、質問者がそこまで言うか、と思った。
上記に気に食わなくても「有難うございました」で、スルーして済ましてくださいね。条件該当分の抜き出しを、複雑な関数や配列数式に頼る質問者が多いが、常々疑問に思っている。
FilterやVBAでやれば簡単とかの質問も多いように思う。

お礼

有り難うございます
ご意見を受け取りました。

〉あえて私…
解りますよ

でもそんな仰る様な事は
抑も不要です

何故なら
手作業を挟む
此れを良し
と、するならば

エクセルにある基本機能である
データ区切り機能
でしたか

で
数値部だけを区切り
予め整え用意しておいたシートから
参照する事で
お示しの事は
勘弁に得れます
よね？

現行は
此の手間を必要
と、していました

今回のものは
此の手間を省く
其のためのもの
で、あり

逆に手間を掛けよ
は、お示しの通り
本末転倒なのです

なお、
ある種の塊
に引っかかってらっしゃる様
〉常々疑問…
なので
お礼の意味を込めて
少しお役に立てれば
と、意見を述べます。

できる、
と、していい、
此れ等は別
ですよね？

ある程度、誰でも
人を殺せます
でも、殺してはダメ
当たり前ですよね

同様に
VBA使用をためらったり
許されない方
其れ等は居ます

また、手間をかければ簡単な事も
逆に見れば
省力化に逆行する時
此れもあります。

省力化へ向かう改善者は
場合により
其れなりな式を
扱う必要がある

此の様に
質問者には
其処に向かわせる背景がある
ですよね？

こう思えば
抱かれる蟠りも
少しは消え

健やかな日々が
貴方のものになる
と、思います。

と、いう事で
今回、私にも
背景があります

抑も今回、
私が求めるものは
VBAを操り
ユーザー関数を作り
使用すれば
簡単勘弁な事
ですよね？

解っていて
敢えて回避している
なのです。

申し訳有りませんが
ご理解頂き
入りかけた袋小路を
回避頂きたく思います。

回答No.2

bunjii

模擬データが次のものとすると提示の数式に誤りがあります。
店名●〇　、価格　157円、日付　2014/3/14。
店名×〇　、価格　305円、日付　2014/5/1。
店名●〇　、価格　156円、日付　2014/5/3。
上記3行のデータについて確認します。
、は区切り記号と考えればA列に店名、B列に文字列で価格、C列に文字列の日付になります。
B3セルに「店名●〇　、価格　157円、日付　2014/3/14。」と言う文字列とし、B4以下も同様の文字列と考えても提示の数式では目的の値を抽出できません。

提示の数式でFIND関数の検索文字が"単”と"数"になっていますので、B列の模擬データには"単"も"数"もないのでエラーとなり、999999999999999が返ることになります。

B3="店名●〇　、価格　157円、日付　2014/3/14。"

=MIN(IFERROR(VALUE(MID($B$1:$B$100000,FIND ("単",$B$1:$B$100000)+2,FIND ("数",$B$1:$B$100000)-FIND ("円",$B$1:$B$100000)-2)),999999999999999))
　　　　　　↓
=MIN(IFERROR(VALUE(MID($B$1:$B$100000,FIND("価",$B$1:$B$100000)+2,FIND("円",$B$1:$B$100000)-FIND("価",$B$1:$B$100000)-2)),999999999999999))
この数式はVALUE関数の返り値が配列値になりますので、数式の確定時にはCtrl+Alt+Enterキーの打鍵が必要です。

MATCH関数の照合の種類には次のような規則があります。
照合の種類の1は検索範囲の値が昇順に並んでいるときに有効で、-1は検索範囲の値が降順に並んでいるとき有効に作用します。
0は照合の値と一致する値が無いときはエラーになります。
関数のヘルプで使い方を習得されると良いでしょう。

お礼

有り難うございます。

失政しました
投稿に際し
初期に考えたデータ形式を
投稿間際で変えたのですが
其の変更を
式に適応し忘れました
申し訳ない。

訂正も頂いている様ですが

自ら訂正します
偽
=MIN(IFERROR(VALUE(MID($B$3:$B$100000,FIND ("単",$B$3:$B$100000)+2,FIND ("数",$B$3:$B$100000)-FIND ("円",$B$3:$B$100000)-2)),999999999999999))

=VALUE(MID(OFFSET($B$0,MATCH(C3,INDEX(IFERROR(DATEVALUE(MID($B$3:$B$100000,FIND ("日",$B$3:$B$100000)+2,FIND ("。",$B$3:$B$100000)-FIND ("円",$B$3:$B$100000)-2)),0),,),-1)-1,0,1,1),FIND ("単",$B$3:$B$100000)+2,FIND ("数",$B$3:$B$100000)-FIND ("円",$B$3:$B$100000)-2))

正
=MIN(IFERROR(VALUE(
MID($B$3:$B$100000,
FIND ("価",$B$3:$B$100000)+2,
FIND ("円",$B$3:$B$100000)
-FIND ("価",$B$3:$B$100000)-2)
),999999999999999))

=VALUE(MID(OFFSET($B$0,MATCH(-C3,-INDEX(
IFERROR(DATEVALUE(
MID($B$3:$B$100000,
FIND ("日",$B$3:$B$100000)+2,
FIND ("。",$B$3:$B$100000)
-FIND("日",$B$3:$B$100000)-2
)),0),,),-1)-1,0,1,1),
FIND ("価",$B$3:$B$100000)+2,
FIND ("。",$B$3:$B$100000)
-FIND ("価",$B$3:$B$100000)-2)
)
ですかね？
此で如何でしょうか？

あと、MINの話ですが
前はINDEX文を介在させると
Enterでの通常確定で
結果が出た

今でも物によっては
此の方法で
通常確定できる
と、思います。

今回は何故
此が出来無いのでしょうか？

お教えください。

回答No.1

msMike

質問文が長大過ぎて読み終わっていないけど、
》 …等と、いったデータが文字列で入力されています
》 故あって最安価格を読み出す必要がでた
「データが」すべて「文字列で入力されて」は、Excel を操作する人の仕業とは思えません。
「価格」の「円」は取っ払って、「日付」も日付形式(←分かる？)で入力し直すっても「故あって」不可能ですか？

お礼

ご来訪には感謝致します
有り難うございます

〉操作する人の…
仰る通りです
思えなくて当然です

人がそういうデータを入力した
とは、申してません
よね？

変更不可ですし
回答の本質は式の評価
と、エクセルの動作不良評価
ですよね？

〉価格」の「…
式を読めばお分かりになる
と、思います
無用の進言
ですよね？

更に、
質問文中に
日付形式で入力された
との記載も、しましたし
DATEVALUEも使っていますよね？

〉(←分かる？…
読まずに
此方の技量を
著しく低く見積る
のは
失礼に思いますよ

あと其れ、余談では？

式以外
長い
と、いうほどの ものでなく
読まない
此れは失礼では？

で、なくとも
少々早計な気がします

此のスレッドは
全環境で動作不良なのか
此の確認
でも、あります

故にある程度エクセルを知り尽くしたものの
トラブルレポートでもあります。

ので
いささか不愉快ですよ