【Excel】開始点と終了点を引いたラインを割った場合は除外する条件式

Question

上の表の[じゃがいも_値段]を図にしたのが下のグラフになります。

問題は、開始点1/1から終了点5/1の間で、値段下落により1/1から5/1を結んだグレーラインを割ってしまった場合のみ除外したく存じます。

=IF(AND(他の条件>=他の条件,MIN(PI2:PM2)>PM2),"ラインより上","")

上記のようにMIN関数を使って最安値設定はできるのですが、グレーラインを下回った場合の除外する条件分岐が思い浮かびません。

何卒、よろしくお願い申し上げます。

＊＊＊追記1＊＊＊
PN1に判定結果を出力させるイメージです

＊＊＊追記2＊＊＊
1月1〜31日まで細かくデータがあります。

Comments

[meg_]

手計算では出来ますか？

[hatena19]

タグにVBAがありますが、VBAでしたいということでしょうか。
質問ではセルの式になってますが、セルに式を埋め込みたいのでしょうか。

また、結果をどこにどのように出力するのでしょうか。あるいは、どのように利用するのでしょうか。

[退会済みユーザー]

hatena19様
紛らわしくてすみません。こちらVBAは使用せず行いたいと思っております。

>セルの式になってますが、セルに式を埋め込みたいのでしょうか。
はい。セルに式を埋め込み、グレーラインを一度も割らなかったデータは、例えばPN1の列にその判定結果「ラインより上」を表示させたいと思っております。
後ほど、見返す時にフィルターをかけるために利用したく思っております。

[退会済みユーザー]

meg_様
5/1時点の値段と最安値<PM2とで比較し、角度を求めたりして現状対応しようとしてる段階でございます。

[hatena19]

> 例えばPN1の列にその判定結果「ラインより上」を表示させたいと思っております。
画像を、その結果の表示例も含む図にしてもらえませんか。

[退会済みユーザー]

hatena19様
出力結果イメージの画像を追加いたしました。

[meg_]

> 5/1時点の値段と最安値<PM2とで比較し、角度を求めたりして現状対応しようとしてる段階でございます。
？やりたいことは「1/1の価格」と「5/1の価格」を結んだ直線にたいして各価格が上か下か？を判定したいのではないのですか？？ 2/1、3/1、4/1の基準価格を算出すれば良いでしょう。
1stepずつセルを使って計算してみると整理出来るかと思います。（一度に計算式を入力しようとすると大変かと思います）

[coco_bauer]

表には、５点(1/1,2/1、3/1、4/1,5/1の各日)しかないのですが、グラフの曲線は、どのようにして生成したのでしょうか？　曲線の形によってグレーラインとの交点が影響されると思うのですが。
また、除外するというのは、どの値（どの範囲）を何の条件で除外するのでしょう？

[hatena19]

げっ、回答書いたら、条件追加されてた。
> 1月1〜31日まで細かくデータがあります。
えっと判定範囲は1ヶ月毎ですか。
その範囲で一日でも判定ライン（グレーライン）を下回る値があったら除外、すべて判定ライン以上なら"ラインより上"と表示するということですか。

[退会済みユーザー]

coco_bauer様
グラフ曲線につきましては1/1〜１/３１までデータがあるものとして反映しております。（最上段のグラフはサイズ的に月単位で省略してしまいました）
除外範囲は開始点1/1から終了点5/1の間でMIN関数を使い一度でも最安値更新した野菜は「追記1」の画像にありますPN列での判定結果では空白表示にします。

[退会済みユーザー]

hatena19 様
すみません。
判定範囲は1/1〜現在としたく思います。

>その範囲で一日でも判定ライン（グレーライン）を下回る値があったら除外、すべて判定ライン以上なら"ラインより上"と表示するということですか。
はい。そのご認識でお間違いございません。

[hatena19]

> 判定範囲は1/1〜現在としたく思います。
ということは1日ごとに列が増えていくということでしょうか。
ユーザーは毎日、判定列の前に列を挿入してデータを入力していくということですか。

[coco_bauer]

この件から降ろさせてもらいます。質問が突然大幅に変わるのについていけません。

Answer 1

冒頭で最初にご提示いただいた例で、じゃがいもの判定結果をセルPN2に表示するのであれば、セルPN2に、

=IF(AND($PI2:PM2>=$PI$1:PM$1*($PI2-PM2)/($PI$1-PM$1)+(PM2*$PI$1-PM$1*$PI2)/($PI$1-PM$1)),"ラインより上","")

と入力し、Shift + Ctrl + Enterを押下いたしますと、

{=IF(AND($PI2:PM2>=$PI$1:PM$1*($PI2-PM2)/($PI$1-PM$1)+(PM2*$PI$1-PM$1*$PI2)/($PI$1-PM$1)),"ラインより上","")}

と、前後に中括弧がついた式になります。
１日増やす場合は、列PNを列Poにコピペして、列PNに新たに日付と値段を入れていく運用になりますが、いかがでしょうか。

Comments

[退会済みユーザー]

kitasue様
ありがとうございます。
配列数式を利用して対応すること大変勉強になりました。
ポップアップウィンドウが開いてしまって、時間がある時に確認したいと思います。

Answer 2

とりあえず列数は5列固定として、PN2セルに下記の式を入力してから、フィルハンドルを下にドラッグでどうですか。

=IF(MIN(IF($PI2-($PI2-$PM2)/COLUMNS($PJ2:$PM2)*(COLUMNS($PJ2:PJ2))<PJ2,-1,1))=1,"ラインより上","")

---

判定範囲は1/1〜現在
最終列が判定列
判定列の前に列挿入して当日のデータ入力

=IF(MIN(IF($PI3-($PI3-OFFSET($A3,0,COLUMN()-2))/(COLUMN()-COLUMN($PJ3))*(COLUMNS($PJ3:PJ3))<PJ3,-1,1))=1,"ラインより上","")

Comments

[退会済みユーザー]

hatena19様
ありがとうございます！
無事動作いたしました。
再度、時間あるとき分解して調べて行きたいと思います。