「○日ぶりの気温」がわかるエクセル関数は？

質問

よく新聞などに「７ヶ月ぶりの暑さ」とか「１５年ぶりの寒さ」などと載っていますが、それをエクセルの関数で調べることができるでしょうか？

私がとっているデータはだいたい以下のようになります。データは横方向にとっています。世界の各国（国によっては数地点～３００以上地点）のデータがあるので、全体では５０００くらいのデータ量があります。

　北海道｜23　16　19　18　17　　
　東京　｜27　25　　　19　26　　　
　沖縄　｜19　25　26　23　20　　　　

たとえば、北海道だと２日ぶりの低温になり、東京だと３日ぶりの高温になります。これがわかるようにしたいのです。
試してみたのは、
=MATCH(LOOKUP(IT2,D2:IS2),D2:IS2,0)
のように、MATCH関数とLOOKUP関数を組み合わせてみました。（高温を調べる時は、気温をすべてマイナスにして対応）
LOOKUP関数は、列の右から検索してくれますが、MATCH関数は列の左から検索するようで、同じ気温が２つあるとおかしな答えになってしまいます。本当は２日ぶりの高温なのに１２６日ぶりになっちゃう、とかです。
なお、上記の方法で行き詰まっているのは
　○気温の数字が降順ではない。なおかつ、空欄もある
　○検査値以下の最大の値はＬＯＯＫＵＰ関数で求められるが、検査値以上の最小の値をスマートに求める方法がない
　○対象となる同じ気温が複数あった場合は最も右の気温を対象としたいが、MATCH関数は最も左を採用してしまう。

などです。この問題をクリアする関数があったら、教えてください。よろしくお願いします。

回答No.3

mt2008 · ベストアンサー

私も、No1の方と同様に「○日ぶり」の数え方に違和感を覚えましたが、今回は質問者さんの数え方でいきます。

基準となるセル(一番右のセル＝当日)があり、
1.基準セルの値より小さい値が入っている一番右側のセルを探して、基準セルの列数-探したセルの列数-1　が、「○日ぶりの低温」
2.基準セルの値より大きい値が入っている一番右側のセルを探して、基準セルの列数-探したセルの列数-1　が、「○日ぶりの高温」
と、言う事でどうでしょう？
質問の表がシートのA1：F3に入っているとしたばあい、
北海道の低温は
{=COLUMN(F1)-MAX(((B1:E1)<=F1)*(B1:E1<>"")*COLUMN(B1:E1))-1}

東京の高温は
{=COLUMN(F2)-MAX(((B2:E2)>=F2)*(B2:E2<>"")*COLUMN(B2:E2))-1}

で出ます（配列式ですので、｛｝を抜いた式を入力してCtrl+Shift+Enterで確定してください）。

お礼

うわっ！すごい！一発でできました。これを求めていました。ありがとうございます！

回答No.2

Nouble

マッチやルックアップを使いこなされているご様子からして
エクセル関数マスターの称号を目指して
日々精進されているものと思います

直接の解を示しては
貴方しか発想できない解の可能性を
もいでしまいますので
ヒントです

ルックアップはソートしてないと使えない筈です

エクセルでは真理値フェールは０トゥルーは１なのです

当たり前ですが
０に何を掛けでも０ですし
一定範囲内の１の数はただ単に総和
詰まり集計すれば解ります

各々の日の気温から基準日の気温を引いて
ｓｉｇｎ関数にかけると
基準日より温暖な日は１に
寒冷な日は－１に
同じ温度の日は０になるでしょう
北海道の例では
　１　－１　　１　　１　　０
ですね

何日ぶりとは
基準日と直前の日との関係が
遡って何日も続いている状態を言います

なので何日続いたか
検索機能のある関数で探すとか
集計関数で対象期間特定後に集計するとか

または
何日目に同様な状態で無いように切り替わるか
その場所を
切り替わり点の有無の真理値に
昇順の連番を掛けておいて
連番最大値を探すことにより知る…　とか

他にも手はありますが
そんなことをすれば良いのですよね

あとの問題として
どの程度まで近しいと見るかがありますが

それはご自身で決めて頂けないと…
ね～

配列数式を使えば雑作もないですよね

お礼

一つ誤解があるようなので訂正させてください。私はエクセル関数マスターの称号を目指しているレベルではないのです。ＭＡＴＣＨやＬＯＯＫＵＰは、１ヶ月以上検索しまくってやっとたどり着いたくらいで「配列関数って何？」くらいのレベルなのです（汗）
多大なヒントありがとうございました。特に１と０とー１にする発想は、難しいことばっかり考えていた私一人では絶対にわからないところでした。頂上は一つでも、いろんな手段があるんだな、と非常に勉強になりました。

回答No.1

noname#204879

直接の回答でなくて恐縮ですが、貴方の「○日ぶり」の使い方が正しいのかどうか気になったので、確認させてください。（国語の問題？）

例えば「２日ぶりに会った」とは、前回会ったのは一昨日つまり２日前だ、と私は理解しています。
貴方の「北海道だと２日ぶりの低温」の前回が16℃を指しているのなら、「北海道だと３日ぶりの低温」と言うべきだと考えますが、如何でしょうか？

ちなみに、http://blog.goo.ne.jp/iwadas/m/200811 において、某気象予報士が「2008年11月29日」に「６日ぶりに15度を超えました」と書いています。
一方で…
http://www.data.jma.go.jp/obd/stats/etrn/view/daily_s1.php?prec_no=44&prec_ch=%93%8C%8B%9E%93s&block_no=47662&block_ch=%93%8C%8B%9E&year=2008&month=11&day=&elm=daily&view=
に気象庁発表の「東京2008年11月」度の[日ごとの値]が記載されています。
22日～29日の「最高気温」は、14.5、16.9、11.9、13.4、14.0、11.0、13.5、16.0 と報告されています。
つまり先の「６日ぶり」は６日前の気温16.9℃を基準にしていることは明らかですね。

お礼

確かに私の用語の使い方はおかしいですね。要は、基準が一定であればいいや、と考えていたのであまり重視しなかったところでした。ありがとうございました。