いつでも医師に相談、gooドクター

タイトル MATCH関数、INDEX関数、IF関数 MIN関数で0以外の数字を拾うようにしたい の続きとなります。前回は皆さま お役に立つご回答を頂きましてありがとうございました。
私の質問内容が足らずに自分の目的を達成することができませんでした。
質問内容不足しており大変申し訳ございません。
もう一度お力添えを頂きたく存じます。

具体的に上げさせていただきます。
目的 ピボットでグラフを作成してます。 データ量は随時ローデータにより変動があります。
下記はピポットテーブルの値となります。

B6列  C6列  D6列
年   金額  前年比
2015
2016
2017
列の項目名は固定ですがローデータにより行数と値は都度変わります。
前年比はB7列目がピボットで計算してるので100%固定で表示されます。

B7列~D7列以降の行数はローデータにより増えたり減ったり変動します。

これを
=INDEX($B:$B,MATCH(MIN($D:$D),$D:$D,0))で引っ張ると 
D6列の前年比のMINを引っ張ってくるのですが
ピボットで計算すると前年比は2015年が100%と計算されてしまいます。
前年比0や空白も存在します。
その為
=INDEX($B:$B,MATCH(MIN($D:$D),$D:$D,0))-1として前年比の表示方法を変えてます。

問題点:
この式だと2015年前年比100%の値もMIN関数の対象に入ってしまいます。

やりたいことは
①前年比100%の値を対象から外したい
②ピボットの前年比の数値の出し方は計算式-1(それ以外に御座いましたらご教授ください)
を使って表示方法を変えたい
③ピボットの空白の値や0の値は対象外として返さないようにしたい。
④同じことをMAX関数でもやりたい
⑤データの列数は固定ですが行数の値はローデータにより変動するので
行数が増えても対応できる数式をご教授いただきたいです。

また前回ご回答いただいた皆様もしよろしければ再度ご教授いただけないでしょうか?

よろしくお願いいたします。

質問者からの補足コメント

  • 文字列が出ない=文字列がないケースは訂正させてください

    No.1の回答に寄せられた補足コメントです。 補足日時:2021/02/28 18:50
gooドクター

A 回答 (3件)

先ず、


先生は 止めては、
頂けませんか?


私語とながら、

先生と 馬鹿に、
されるのは、

性に 合わないもので。


次に、

エラーが 出るとの、
事ですが。


残ながら、

具体的な 事が、
伝わって きませんでした。


其処で、

お願いします、
具体的事例を 示しては、
頂けませんか?


次に、

セル内に 文字列が、
含まれるか 判定し、

含まれる 場合は、
対象から 外す、
方式ですが。


最も、

簡単な 方法は、
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",""),"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
と なります。


尚、

先日 開示させて、
頂きました ファイルに、
シートを 追加して、

式を 置いて、
おきました。
    • good
    • 0
この回答へのお礼

失礼がございまして大変申し訳ございませんでした。
ご説明もありがとうございます。今後ともご指導ご鞭撻宜しくお願いいたします。

お礼日時:2021/03/06 18:25

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


其の式は、
基本的な 機能構造は、
私が 作った、
ものですが。


式自体は、

私の ものでは、
ありませんよ?


次に、

文字の 対象外しですが、
出来は するのですが、

スマートな 方法では、
ないので。


少し、

新方式を 考えさせて、
くださいね。
    • good
    • 0
この回答へのお礼

@nouble1様 
はい、承知いたしました。一部訂正させていただきます。
大変申し訳ございません、新方式ありがとうございます。
お手数をおかけいたします。
何度も試行錯誤をしまして前回ご提案いただいたオフセット関数の式が
やはり一番使いやすかったためそちらを使ってMAXとMIN関数対応させていただいてます。対応方法としては一旦値の先頭行→100万行指定とさせていただいてます。ピボットはこちらで対応できました。
ただ今度はアウトプットしたデータでiferror(2019年売上/2020年)-1,"")など計算式を入れてオフセット関数でMAXとMINを出そうとすると#VALUE!となってしまいます。
空白の部分もあり(基本固定MAX1~150行までとしてます)
選択した項目によって20行などになることもございます。
対策としてオプションから計算方式を変更するで数値は問題なく使えるようになったのですが今度は&関数で作成していた内容が#VALUE!となってしまいます。こちらも対応方法ございましたら先生にご教授いただきたいです。

お礼日時:2021/03/01 08:38

ああ 失礼、


ピボットテーブル内でしたよね、
ご免なさいね。


此を、

http://officetanaka.net/excel/function/function/ …
試してみて 頂けますか?
この回答への補足あり
    • good
    • 0
この回答へのお礼

ありがとうございます。 nouble1さんがおつくりになられたかと思われる関数を引用させていただきますと
=INDEX($B:$B,MATCH(MAX($D:$D-($D:$D=0)*10000),$D:$D,0))
=INDEX($B:$B,MATCH(MIN($D:$D+(-$D:$D=0)*10000),$D:$D,0))
のようにすると文字列が出ない場合はうまくMAXとMIINを空白と0を除き拾えました。ここに文字列も対象外としたい場合はどのような関数を入れるのが適切でしょうか?ご教授ください。オフィス田中さんの動画はよく見させていただいてます。非常に勉強になりますね。

お礼日時:2021/02/28 18:48

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

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

gooドクター

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

人気Q&Aランキング