MATCH関数、INDEX関数、IF関数 MIN関数で0以外の数字を拾うようにしたい Ver2

Question

タイトル　MATCH関数、INDEX関数、IF関数 MIN関数で0以外の数字を拾うようにしたい　の続きとなります。前回は皆さま　お役に立つご回答を頂きましてありがとうございました。
私の質問内容が足らずに自分の目的を達成することができませんでした。
質問内容不足しており大変申し訳ございません。
もう一度お力添えを頂きたく存じます。

具体的に上げさせていただきます。
目的　ピボットでグラフを作成してます。　データ量は随時ローデータにより変動があります。
下記はピポットテーブルの値となります。

B6列　　C6列　 D６列
年　　　金額　　前年比
2015
2016
2017
列の項目名は固定ですがローデータにより行数と値は都度変わります。
前年比はB7列目がピボットで計算してるので100%固定で表示されます。

B7列～D7列以降の行数はローデータにより増えたり減ったり変動します。

これを
=INDEX($B:$B,MATCH(MIN($D:$D),$D:$D,0))で引っ張ると　
D6列の前年比のMINを引っ張ってくるのですが
ピボットで計算すると前年比は2015年が100%と計算されてしまいます。
前年比０や空白も存在します。
その為
=INDEX($B:$B,MATCH(MIN($D:$D),$D:$D,0))-1として前年比の表示方法を変えてます。

問題点：
この式だと2015年前年比100%の値もMIN関数の対象に入ってしまいます。

やりたいことは
①前年比100%の値を対象から外したい
②ピボットの前年比の数値の出し方は計算式-1（それ以外に御座いましたらご教授ください）
を使って表示方法を変えたい
③ピボットの空白の値や0の値は対象外として返さないようにしたい。
④同じことをMAX関数でもやりたい
⑤データの列数は固定ですが行数の値はローデータにより変動するので
行数が増えても対応できる数式をご教授いただきたいです。

また前回ご回答いただいた皆様もしよろしければ再度ご教授いただけないでしょうか？

よろしくお願いいたします。

nouble1 · Accepted Answer

先ず、
先生は　止めては、
頂けませんか？


私語とながら、

先生と　馬鹿に、
されるのは、

性に　合わないもので。


次に、

エラーが　出るとの、
事ですが。


残ながら、

具体的な　事が、
伝わって　きませんでした。


其処で、

お願いします、
具体的事例を　示しては、
頂けませんか？


次に、

セル内に　文字列が、
含まれるか　判定し、

含まれる　場合は、
対象から　外す、
方式ですが。


最も、

簡単な　方法は、
ISNUMBER構文や、TEXT構文を、
用いて　判定して。


其の上で、

文字列が　含まれると、
判定される　セルに対しては、

MIN構文が　選り出してくる、
最小の、

其の　対照の、
最大級値を、
与えて　しまえば、
いい　訳ですが。


しかし、

ビジネスシーンでの　実務を、
想定すれば、

数値を　全角で、
入力される　事も、

「'」付きで、
入力される　事も、

「"」で　囲われて、
入力される　事も、

十分に　ありますので、
ISNUMBER構文も、ISTEXT構文も、

実務での　使用に、
耐えるとは　いえません。


次に、

旧態依然の　やり方ですか、

=SIGN(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(@$A$1:$A$3,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"０",""),"１",""),"２",""),"３",""),"４",""),"５",""),"６",""),"７",""),"８",""),"９",""),"-",""),"ー","")))*10^15
と、

「全ての　アラビア数字を、
取り払った　後、
何文字　残るかを、
調べ、

1文字も　残らなかったら、
全て　アラビア数字だったと、
解る。」
との　やり方ですが。


此は、

正規表記が　使えれば、
短文化が　叶い、
スマートに　なって、
楽なのですが。


しかし、

エクセルでは　ワイルドカードしか、
使えませんから、

何とも　力業で、
スマートでは　ありませんし、
長いですね。


ですので、

方向性として、
「一文字ずつに　分離して、
数値に　変換できるか、
確認して、

変換不能だった　文字数が、
なければ、
全て　アラビア数字だった、」
との、

判定方法を　取る、
事を　選択しました。


此処で、

話を　そらしますが、
配列数式で　扱える、
データ形式は、
擬えて　長方形のみ、

なのですよね。


しかし、

セル文字数には　一貫性が、
ありませんから、
一筋縄では　いかないのですよね。


其処で、

今回は　他者開発の、
「範囲セル内最大文字数に、
全セル文字数を　揃える、」
との　方式を、
持ち込みました。


さて、

代表式です、

=SIGN(MMULT(ISERROR(VALUE(MID(SUBSTITUTE($A$1:$A$4,"-","")&REPT("1",MAX(LEN($A$1:$A$4))-LEN($A$1:$A$4)),COLUMN(OFFSET($A$1,0,0,1,MAX(LEN($A$1:$A$4)))),1)))+0,SIGN(ROW(OFFSET($A$1,0,0,MAX(LEN($A$1:$A$4)),1))))-ISERROR(FIND("-",A1:A4))+1)*10^15

此方を　先日の、
式で、
条件部分に　追加、
頂ければ、

アラビア数字以外を　一文字でも、
含む　セルを、
対象外と　出来ます。


では、

代表式を　細かく、
みて　いきます。


先ず、

=MID(文字列群,{1,2,〜最大文字数}。1)
との　構文を、
持ちいて、
範囲内文字列を　一文字ずつに、
分解して　います。
(私が　開発した、
ものです。)


此の際に、

文字列長が　一定でなければ、
配列数式では　扱えなくなるため、
REPT構文を　持ちいて、
不足文字数を　補っています。


又、

{1,2,〜最大文字数}
此の　部位の、
自動生成に付いて、
触れて　おきます。


此は、

OFFSET構文で、
=OFFSET($A$1,0,0,0,MAX(LEN(対象範囲内セル)))
と　生成させ、
COLUMN構文で　配列数式型連番数値を、
生成させて　います。


此処で、

重要な　注意点ですが、
=ROW(OFFSET($A$1,0.0.MAX(LEN(対象範囲内セル),1))　…甲
此の　様に、
する　事を、
思い描きがちか、

と　思いますが。


此は、

文字を　一文字ずつに、
する　時には、
駄目なのです。


何故か、

其の　理由は、
端的に　いってしまえば、

抑も　文字列とは、
行並びデータの　ような、
形なのです。


ですので、

行並びデータを　縦に、
スライドされると、

1箇所に　複数値が、
配置されるような　状態と、
なって　しまうので、
扱えなく　なるのです。


ですので、

甲は　今回は、
使えません。


此までは、

此の　様に、
MID($A$1:$A$3&REPT("1",MAX(LEN(@$A$1:$A$3))-LEN($A$1:$A$3)),COLUMN(OFFSET($A$1,0,0,1,MAX(LEN(@$A$1:$A$3)))),1)　…乙
組み上がっている　事と、
なります。


次に、

乙を　基礎式として、
数値化できるが　判定する、
訳ですが、

其れは、
=ISERROR(VALUE(乙)) 　…丙
と　しました。


此は、

MID構文で　文字列と、
みなされ　一文字化された、
各々が　数値化可能かを、
検証して、

エラー箇所を　判定させています。


此により、

アラビア数字だったのか、違ったのかの、
基礎情報と　しています。


但、

元来　分解された、
配列数式データは、
再結合不可能なので、
此までは　困っていたのですが。


ご存知の、

MMULTI構文による　行毎集計が、
私が　開発した、
事により、

出来るように　なりましたので、
フワレークスルーを　迎えました。


しかし、

制約として　MMULTI構文では、
ブール値も　駄目で、
アラビア数値記載可能な　数値しか、
扱えないようなので、

丙+0
として　数値化しています。


又、

MMULTI構文は　行列積ですので、
行毎集計として　機能させる、
ためには、

{1:1:…:1}と、
いった　ものが、
必要と　なりますが、

此の　自動精製用に、
=SIGN(ROW(OFFSET($A$1,0,0,MAX(LEN(@$A$1:$A$4)),1)))　…丁
と　しました。


此で、

=MMULTI(丙+0,丁)　… 戊
が　始めて、
成立し、
セル毎の　非アラビア文字数合計が、
判明しました、


勿論、

0を　返せば、
其の　位置の、
セルは　数値だったと、
結論付けられます。


但し、

此のままでは、
負の　数には、
非対応なので、

-ISERROR(FIND("-",A1:A4))+1　… 己
と　追加しました。


総じれば、

先式の、
=SIGN(戊-己)*10^15
と　なります。


尚、

先日　開示させて、
頂きました　ファイルに、
シートを　追加して、

式を　置いて、
おきました。

nouble1 · Answer

少し　訂正させてくださいね、
其の式は、
基本的な　機能構造は、
私が　作った、
ものですが。


式自体は、

私の　ものでは、
ありませんよ？


次に、

文字の　対象外しですが、
出来は　するのですが、

スマートな　方法では、
ないので。


少し、

新方式を　考えさせて、
くださいね。

nouble1 · Answer

ああ　失礼、
ピボットテーブル内でしたよね、
ご免なさいね。


此を、

http://officetanaka.net/excel/function/function/getpivotdata.htm
試してみて　頂けますか？

MATCH関数、INDEX関数、IF関数 MIN関数で0以外の数字を拾うようにしたい Ver2

先ず、

少し 訂正させてくださいね、

ああ 失礼、

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

少し　訂正させてくださいね、

ああ　失礼、