• ベストアンサー

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

よく新聞などに「7ヶ月ぶりの暑さ」とか「15年ぶりの寒さ」などと載っていますが、それをエクセルの関数で調べることができるでしょうか? 私がとっているデータはだいたい以下のようになります。データは横方向にとっています。世界の各国(国によっては数地点~300以上地点)のデータがあるので、全体では5000くらいのデータ量があります。  北海道|23 16 19 18 17    東京 |27 25   19 26     沖縄 |19 25 26 23 20     たとえば、北海道だと2日ぶりの低温になり、東京だと3日ぶりの高温になります。これがわかるようにしたいのです。 試してみたのは、 =MATCH(LOOKUP(IT2,D2:IS2),D2:IS2,0) のように、MATCH関数とLOOKUP関数を組み合わせてみました。(高温を調べる時は、気温をすべてマイナスにして対応) LOOKUP関数は、列の右から検索してくれますが、MATCH関数は列の左から検索するようで、同じ気温が2つあるとおかしな答えになってしまいます。本当は2日ぶりの高温なのに126日ぶりになっちゃう、とかです。 なお、上記の方法で行き詰まっているのは  ○気温の数字が降順ではない。なおかつ、空欄もある  ○検査値以下の最大の値はLOOKUP関数で求められるが、検査値以上の最小の値をスマートに求める方法がない  ○対象となる同じ気温が複数あった場合は最も右の気温を対象としたいが、MATCH関数は最も左を採用してしまう。 などです。この問題をクリアする関数があったら、教えてください。よろしくお願いします。

質問者が選んだベストアンサー

  • ベストアンサー
  • mt2008
  • ベストアンサー率52% (885/1701)
回答No.3

私も、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で確定してください)。

powergif
質問者

お礼

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

その他の回答 (2)

  • Nouble
  • ベストアンサー率18% (330/1783)
回答No.2

マッチやルックアップを使いこなされているご様子からして エクセル関数マスターの称号を目指して 日々精進されているものと思います 直接の解を示しては 貴方しか発想できない解の可能性を もいでしまいますので ヒントです ルックアップはソートしてないと使えない筈です エクセルでは真理値フェールは0トゥルーは1なのです 当たり前ですが 0に何を掛けでも0ですし 一定範囲内の1の数はただ単に総和 詰まり集計すれば解ります 各々の日の気温から基準日の気温を引いて sign関数にかけると 基準日より温暖な日は1に 寒冷な日は-1に 同じ温度の日は0になるでしょう 北海道の例では  1 -1  1  1  0 ですね 何日ぶりとは 基準日と直前の日との関係が 遡って何日も続いている状態を言います なので何日続いたか 検索機能のある関数で探すとか 集計関数で対象期間特定後に集計するとか または 何日目に同様な状態で無いように切り替わるか その場所を 切り替わり点の有無の真理値に 昇順の連番を掛けておいて 連番最大値を探すことにより知る… とか 他にも手はありますが そんなことをすれば良いのですよね あとの問題として どの程度まで近しいと見るかがありますが それはご自身で決めて頂けないと… ね~ 配列数式を使えば雑作もないですよね

powergif
質問者

お礼

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

noname#204879
noname#204879
回答No.1

直接の回答でなくて恐縮ですが、貴方の「○日ぶり」の使い方が正しいのかどうか気になったので、確認させてください。(国語の問題?) 例えば「2日ぶりに会った」とは、前回会ったのは一昨日つまり2日前だ、と私は理解しています。 貴方の「北海道だと2日ぶりの低温」の前回が16℃を指しているのなら、「北海道だと3日ぶりの低温」と言うべきだと考えますが、如何でしょうか? ちなみに、http://blog.goo.ne.jp/iwadas/m/200811 において、某気象予報士が「2008年11月29日」に「6日ぶりに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 と報告されています。 つまり先の「6日ぶり」は6日前の気温16.9℃を基準にしていることは明らかですね。

powergif
質問者

お礼

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

関連するQ&A

専門家に質問してみよう