次の場合の関数はどうなりますか（添付画像あり）

質問

エクセル２００７で、毎日数字を入力していき、
基準値以上なら達成とみなして「連続××日目」
とカウントを表示する関数を教えてください（詳細は添付画像の通りです）

基準値以上でカウントし、基準値未満でカウントをリセットします。
たとえば基準値が４なら、達成時間が４時間であれば連続達成日数としてカウントし、３時間５９分なら０にリセットです。
ただし、基準値は次の様に変動致します。
平日の場合、F４の値（この画像では５時間半）
土日の場合、次の２つに分岐
Ｂ列にαとあれば、F5の値（ここでは３時間）
Ｂ列にβとあれば、F6の値（ここでは４時間半）

※土日のうち、どちらがαでどちらがβかは、その週によって変わるということです。
※Ｆ４～Ｆ６の基準値は、月ごと、場合によっては週ごとに変わることがあります。
そのたびに計算式の数値を調整するのも面倒ですので、計算式内で基準値を使用するときは
必ずＦ４～Ｆ６セルを絶対参照するようにしたいです。
※Ｄ８には、現在の記録が常に更新されるようにします。
※Excel2007です。
※添付データが小さくて確認しづらい可能性があります。その場合は下記の外部サーバーで大きなサイズの添付画像を閲覧できます。
https://www.dropbox.com/s/6uf26e2njvo3c05/%E7%84%A1%E9%A1%8C.png?dl=0

大変複雑になってしまいお手数をお掛けしますが、
何とぞ、よろしくお願い致します。

回答No.3

mt2015 · ベストアンサー

ANo.1です。
> Ｄ８の、現在の達成日数の関数も、よろしければ教えて頂きたいです。

達成時間が入力されている一番下の行の連続達成日数を表示できればよいでしょうか？
それでしたらこんな感じでどうでしょう。
=VLOOKUP(SUMPRODUCT(MAX(C11:C100*(D11:D100<>""))),C11:E100,3,FALSE)

データが何行目まであるのか解りませんでしたので、とりあえず100行目まであるとしています。
もっと多い場合は式中の100を書き換えてください。

お礼

すごい・・・・
お示しの計算式で、意図する動作を確認致しました。

これほど素晴らしい式を頂いて、本当によろしかったのでしょうか、
お時間を割いて手間暇をかけていただき、たいへん恐縮です。

エクセルが、見事に化けました。
これが本当の表計算なのですね、もはやエクセルというより別の便利アプリですね、
完全に１つのソフトウェアを開発してしまった感じですね。
私ばかりが得をして、回答者様に何もお返しできないのが心苦しいですが、
御蔭さまで本当に助かりました。
これで日常がよくなります、本当に、頭が上がりません。
この度は、素晴らしいソフトウェアの開発を、本当にどうもありがとうございます。

回答No.4

bunjii

>No.1の回答者様の添付画像にあるような方法を採ることにいたします。
E列の数式は解決したものとさせて頂きます。

>依然として、Ｄ８セルにおける「現在の連続達成日数」を求める方法が分かりません。
「現在の連続達成日数」と言う定義が分からないので回答できませんでした。
貼付画像では23行目まで表示されており日付の値が4/1(日)なので今年のようですが将来の達成時間を既に予測できているのでしたら今日の日付は3/20(火)ですからE11の1をD8セルに返すことでしょうか？
しかし、E11の値は質問の文言からは0になるはずなのでどのような論理なのかが説明されていません。
23行目が最終の入力日としてE23の値をD8セルへ返す場合は「現在の連続達成日数」と言う文言が不可解なことになります。

Excelの数式は論理に叶ったものにしなければなりませんので曖昧な条件では数式を組み立てられません。
D8セルへの数式も現時点では曖昧さが残っていますので再度見直して補足を完全にしてください。
現在の日付はTODAY関数で取得できますのでC列の日付から今日の日付を探すことはMATCH関数で行番号を抽出できます。

お礼

この度は、私どもの不完全な質問形式により混乱を招いてしまい、
申し訳ありません。

もっと論理的に質問するよう気を配るべきでした。

回答No.2

bunjii

>基準値以上でカウントし、基準値未満でカウントをリセットします。
>たとえば基準値が４なら、達成時間が４時間であれば連続達成日数としてカウントし、３時間５９分なら０にリセットです。
添付画像と異なるようですが勘違いでしょうか？

>※Ｆ４～Ｆ６の基準値は、月ごと、場合によっては週ごとに変わることがあります。
Excelでは数式が入力されているセルについて自動再計算が標準になっています。また、再計算を手動に変更しても再計算コマンドによってすべての数式について再計算されますのでF4～F6の値が変更されると以前に計算された結果が再計算で置き換えられます。
このような条件で良ければワークシート関数で処理可能です。
達成時間のD列に入力して時点で単発の計算を行うにはVBAでプログラムしないと目的に合いません。

>大変複雑になってしまいお手数をお掛けしますが、
複雑と言うより条件の提示が不明確のため的確な回答ができません。
基準値の変更がない場合は下記の数式で良いと思います。
E11セルの数式（但し、土日の確認セルにはαまたはβが必ず入力されていること）
=IF(WEEKDAY(C11,2)<6,IF(D11>=$F$4,SUM(E10,1),0),IF(B11="α",IF(D11>=F$5,SUM(E10,1),0),IF(D11>=F$6,SUM(E10,1),0)))

「基準値以上でカウントし」とは「上のセルに1を加えた値」と解釈していますのでE11セルの数式にE10+1では#VALUE!になるためSUM関数でエラーを回避して同等の結果を得ています。（SUM関数では文字列のとき0と解釈されます）

お礼

プロの方とお見受けします。
特定のセルの内容を変えるとそのセルが参照されている全セルも再計算されてしまうのですね。
なるほど、そうでしたか。
右も左も分からないもので、大きな勘違いをしていたようです。

お時間を割いて計算式をありがとうございます。
ただ、私どもの用途においては基準値は一定ではないので、
No.1の回答者様の添付画像にあるような方法を採ることにいたします。
No.1の回答者様の改善案と計算式を使って、意図する動作が得られました。

依然として、Ｄ８セルにおける「現在の連続達成日数」を求める方法が分かりません。
※過去最高記録ではなく現時点での連続記録であり、つねにＥ列の値を参照します。
とある方が、　=VLOOKUP(999,E:E,1,TRUE) と仰っていたのですが、それはどういう原理なのでしょうか？
既にご回答を頂いている折厚かましいですが、よろしければ、ご教授いただければ嬉しいです。

回答No.1

mt2015

> ※Ｆ４～Ｆ６の基準値は、月ごと、場合によっては週ごとに変わることがあります。
> そのたびに計算式の数値を調整するのも面倒ですので、計算式内で基準値を使用するときは
> 必ずＦ４～Ｆ６セルを絶対参照するようにしたいです。

例えば、3/31まではF4の基準値は4:00だったけど、4/1からは5:30に変更する……って事ですか？
そりゃ無理だ。
F4セルの基準値を書き換えたらこのシートでF4セルを参照して演算した結果が全て書き換わっちゃいます。
G:I列にその日の基準値を持つべきです。

添付の図ではG：I列にその日の基準値をもっています。
平日の基準値を4/1から4:00→5:30、αの基準値を3/27から3:00→4:00と変えています。

E11セルに↓
=1*(D11>=IFERROR(HLOOKUP(B11,H$10:I11,ROW(A2),FALSE),G11))

E12セルには↓を入れて、E12を下にコピーしています。
=IF(D12>=IFERROR(HLOOKUP(B12,H$10:I12,ROW(A3),FALSE),G12),E11+1,0)

お礼

プロの方とお見受けします。
お示しの計算式で、意図する動作を確認致しました。
ご丁寧に画像までお貼り頂き助かります。

素晴らしい、美しい式です。
これほど複雑なことを、この２つの計算式で達成できるものなのですか。
それほどスマートで優れた式だということでしょう。
HLOOKUP関数はエラーが起こったりして難しいということをネットで知りました。
このように使いこなされているなんてすごい。

厚かましいですが、
Ｄ８の、現在の達成日数の関数も、よろしければ教えて頂きたいです。
私にとって、分かりそうで分からないです。
※過去最高記録ではなく、現時点で連続達成の何日目かを表示するものです。