エクセルIF文をROUNDUPで短く書きたい

質問

質問があります

シート１のI列O列の値（データは500件程度）に応じて、シート2のE3 に判定結果を表示するセル式を作成しようとしています。まずI列の値でE3を空白表示するか決めます。その後O列の値が
-40より小さかったら24、
-35より小さかったら21、
-30より小さかったら18
の調子で5増えるごとに3の倍数で値が減っていき0以上なら0とする、セル式を作成したいです。

以下の式を作成しましたが、文章で記述したとおりに動いてくれません
（特に5＜O＜０→０のあたり）、この記述に何か問題がありますでしょうか？

=IF('シート１'!I:I=0,“　“, ROUNDUP(シート１!O:O/-5,0)*3)

----------------------------------------------------------------------
I=0　　　　　→“　“
I=0でなければOの値で判定
０＝＞O→0

-5＜O＜０→０

-10＜O＜－5→３

-15＜O＜－10→６

-20＜O＜－15→９

-25＜O＜－20→１２

-30＜O＜－25→１５

-35＜O＜－30→１８

-40＜O＜－35→２１

O<-40　　　　　→２４
-------------------------------------------------------------------

回答No.6

don9don9 · ベストアンサー

http://okwave.jp/qa/q7201524.html

こちらの質問でNo.2の回答を書いた者です。
新しく質問を投稿しなおすのであれば、前の質問は締め切ってください。

>I列とO列は500件程度のデータが入っているんですよね。
>シート2のE3に判定結果とおっしゃいますが、その判定とは500件のデータ1件1件に対して個別に判定を行うのか、500件すべての合計や平均などから判定を行うのかがまずわかりません。

と書いたのですが、今回の質問を見てもその点は明確ではありませんね。

>=IF('シート１'!I:I=0,“　“, ROUNDUP(シート１!O:O/-5,0)*3)

このような式をE3に入力した場合、一見I列やO列全体を参照する式のようにみえますが、実際には式が入っているセルと同じ行の値、つまりI3とO3しか参照しません。
思うような結果が得られていない原因は、そのあたりにある可能性もあります。

それと他の方も指摘されていますが、
・Oの値がちょうど-5のときは0なのか3なのか？
・Oの値がちょうど-10のときは3なのか6なのか？
など、Oが範囲の境目ちょうどの値の場合にどちらになるかが不明瞭です。
こういう場合は、片方を＜ではなく≦で表していただいた方が明確になります。

お礼

OKWAVEで質問したものが、こちらにも表示されたようですね。申し訳ありません

500件のデータ（-40から50までの値がランダムに入っている）1件1件に対して個別に判定を行います
本来同一シート状でOI列の値に応じて、判定結果をR列に表示させたかったのです。

ところが、OI列の値で判定する前に、まずC列（1から１４の値がランダムに）の値で判定するために、シートを2つ使うという複雑なことになっていました。

2つの条件に対し、500件のデータ一つ一つを突合させるやり方、また不等号の境界線を明確にさせる
やりたいことが整理されていません

ご指摘感謝します。

回答No.7

don9don9

セルの値の範囲ごとに一定の値を返したい場合は、検索関数が使いやすいと思います。
検索関数については参考URLをご覧ください。

以下、LOOKUP関数を使った方法になります。

シートを一つ追加します。（名前を「シート３」とします）
このシート３のA1:B8に、以下のデータを入れます。
A1に-40、B1に21
A2に-35、B2に18
A3に-30、B3に15
A4に-25、B4に12
A5に-20、B5に9
A6に-15、B5に6
A7に-10、B7に3
A8に-5、B8に0

E3の式は
=IF(シート１!I:I=0,"", IF(シート１!O:O<-40,24,LOOKUP(シート１!O:O,シート３!A:A,シート３!B:B)))
となります。

LOOKUP関数はリストから検索値を昇順に見ていくため、リストの最初の値（この場合はA1セルの値）より小さい値が来ると#N/Aエラーを返します。
ですから、O列の値が-40より小さい場合は24を返し、それ以外の場合はLOOKUP関数でシート３のリストを昇順に見て該当する値を返す、という式にしています。
この場合、O列の値が-5より大きい場合はすべて0を返します。

参考URL：

http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/vlookup.htm

お礼

複数のシートを使うこと、Lookup関数を使うとよいことはわかってきました。整理してもう少しシンプルにしていきます。ご教授感謝します。

回答No.5

mt2008

Ｉ列、Ｏ列の値の何を対象とするんでしょうか。
500件程度有る列の話しでありながら結果をE3セルと指定しているので、何をしたいのか良く判りません。

・同じ行のＩ列、Ｏ列が対象ですか？（E3セルならＩ3、Ｏ3）

・Ｏ列の合計値または最小値、最大値を見れば良いのですか？

前者でしたら↓こんな感じです。
=IF(シート１!I3="","",-3*ROUNDDOWN(MAX(-40,シート１!O3)/5,0)*(シート１!O3<0))

-5、-10丁度などの時にどうするのか不明でしたので-35<=Ｏ<-40　で21となるようにしました。
あと、カテゴリの見直しもお勧めします。

お礼

同じ行のＩ列、Ｏ列が対象です。

本来この話はC列とOI列2つの条件に対して500件のデータ1件1件、個別に突合させ、判定結果をR列に表示させるというものだったのでした。

それを部分的に切り取ったため、また一つのシートで処理しきれず、2つのシートを使おうとしたために、このように他者にとって意味不明な質問となってしまいました。

まずやりたいことを明確にしたいと思います。
ご指摘感謝します。

回答No.4

KURUMITO

難しく考えてしまったようですね。

=IF(Sheet1!I:I=0,"",IF(SUM(Sheet1!I$1:I$1000)<=0,IF(SUM(Sheet1!I$1:I$1000)<-40,24,-1*ROUNDDOWN((INT(SUM(Sheet1!I$1:I$1000))+1)/5,0)*3),""))

お礼

一つのセル式に関数が4つも入ってるのは見たことがありません。
でもひるんではいけないですね。ご指摘感謝します

回答No.3

KURUMITO

シート2のE3セルには次の式を入力してはどうでしょう。

=IF(SUMPRODUCT((Sheet1!I$1:I$1000<>"")*(Sheet1!O$1:O$1000))<=0,IF(SUMPRODUCT((Sheet1!I$1:I$1000<>"")*(Sheet1!O$1:O$1000))<-40,24,-1*ROUNDDOWN((INT(SUMPRODUCT((Sheet1!I$1:I$1000<>"")*(Sheet1!O$1:O$1000)))+1)/5,0)*3),"")

なお、－40より小さかったら24の意味は-41になって24で、－40では21という意味になりますがそれでよいのですね。

お礼

細かいところまでまだ注意がいってないということがよくわかりました。感謝します

回答No.2

tomtzr

=IF('シート１'!I:I=0,"",IF('シート１'!O:O<-40,24,(ROUNDDOWN('シート１'!O:O/5,0)*-3))))
で如何でしょうか

お礼

この形が正解に近いということは理解できて来ました。感謝します

回答No.1

pentium100

-40より小さかったら24
-40より大きかったら21
は分かりますが、
「-40ちょうど」の時はどうなるのか書いていませんね。

-40のときは24でいいのかな？（つまり-40以下の時、24）
とりあえず仮定して、こう書いてみました。

=IF('シート１'!I:I=0,"",IF('シート１'!O:O>=0,0,ROUNDDOWN('シート１'!O:O/5,0)*-3))

お礼

じっくり考えて作ってないということがよくわかりました。
整理します