エクセル 複雑なIF関数を避けたいのですが…

質問

入力した数値を一定のルールで丸めて出た数値を別のセルに返したいのですが、

●1以上10,000未満の場合　【1の位】切り捨て【10の位】1～5の場合5、6～9の場合0で100の位に1プラス【100の位以上】そのまま
●10,000以上100,000未満【1の位】切り捨て【10の位】切り上げ【100の位以上】そのまま
●100,000以上1,000,000未満【1の位】切り捨て【10の位】切り上げ【100の位】切り上げ【1000以上の位】そのまま
●1,000,000以上10,000,000未満【1の位】切り捨て【10の位】切り捨て【100の位】切り上げ【1000以上の位】そのまま
●10,000,000以上【1の位】切り捨て【10の位】切り捨て【100の位】切り捨て【1000の位】切り上げ【1万以上の位】そのまま

といったルールなのです。
CEILINGやIF関数を使ってと思ったのですが、一つのセルに7個以上は使えないし…

一度やってみたら

=IF(T1="","",IF(V1="","",IF(V1>T1,"",IF(LEN(V1)<=3,ROUNDUP(A1,-LEN(V1)+2),IF(LEN(V1)=4,IF(LEN(ROUNDUP(A1)-50)>4,ROUNDUP(A1),(ROUNDUP(A1,-2)-50)),ROUNDUP(A1,-(LEN(V1)-3)))))))

1こんなに長くなってしまいました。

もっとシンプルでできないものでしょうか？

回答No.3

KURUMITO · ベストアンサー

Ａ１セルに数値が有るとして次のような式になりますね。

=IF(A1<10,"",IF(LEN(A1)<5,CEILING(A1,50),ROUNDUP(A1,IF(LEN(A1)<6,-2,IF(LEN(A1)<8,-3,-4)))))

お礼

遅くなりましたが、どうもありがとうございました。　一発で解決しました。
今まで悩んでいたのが嘘のようです。

回答No.5

keithin

ご相談に書かれたことを丁寧に読むと
　10000未満で「１の位を切り捨てて」５０に切り上げ
　10000-1,000,000で「上位４桁」に切り上げ
　1,000,0000-で「上位５桁」に切り上げ
のように見えますが

=CEILING(ROUNDDOWN(A1,-1),IF(A1<10000,50,10^INT(LOG(A1)-IF(A1>=1000000,4,3))))
ぐらいでしょうか。

お礼

どうもありがとうございました。　解決いたしました。

見方を変えればここまでシンプルなんでびっくりです。

回答No.4

tom04

こんにちは！
質問にある
●100,000以上1,000,000未満【1の位】切り捨て【10の位】切り上げ【100の位】切り上げ【1000以上の位】そのまま

の意味がイマイチよく判らないのですが・・・
123445　→　123500　→　124000　という結果？

これではあまりに誤差が多すぎる感じがしますので、勝手に

10000未満は1の位の5ずつの切り上げ、
それ以外は
1万以上は1の位を切り上げ、十までの表示
10万以上は10の位を切り上げ、百までの表示
100万以上は100の位を切り上げ、千までの表示
1000万以上は100の位を切り上げ、万までの表示
という判断でやってみました。

A1セルに数値を入力するとして、
表示したいセルに
=IF(A1<10000,ROUNDUP(A1*2,-1)/2,ROUNDUP(A1,4-LEN(A1)))

※　お示しの数式では数値の桁数はV1セルのように見受けられますので
そうであれば、数式内の最後部分
ROUNDUP(A1,4-LEN(A1)
を
＞ROUNDUP(A1,4-LEN(V1)

に変更して試してみてください。

一発で解決とはいかないと思いますが、
他の回答者さんのたたき台になりますかね？m(_ _)m

お礼

どうもありがとうございました。　無事解決しました。

回答No.2

moon00

私が見たところ、提示された条件は以下のように条件をまとめられる気がします。

・10,000以上の場合については、頭から4桁目を切り上げ、それ以下は0
　（100,000以上1,000,000未満の場合に、【10の位】切り上げとなってますが、
　　ここは切り捨てではないでしょうか？）

・1以上10,000未満の場合を2つに分け、
　→10未満なら0
　→10以上なら、1の位を切り捨てた上で、50を基準として、その倍数になるように大きい方にまとめる

このように理解しました。

上記の条件だと、IF関数を2回使えばできます。

=IF(LEN(V1)>4,ROUNDUP(TRUNC(V1/10^(LEN(V1)-4)),-1)*10^(LEN(V1)-4),IF(LEN(V1)>1,CEILING(ROUNDDOWN(V1,-1),50),0))
LEN関数で桁数を算出。10,000以上ということは、長さが4より大きいので「>4」にてIF条件とする。
頭から4桁取り出すために、（桁数-4）×10の数字で割り算し、整数部分だけを取り出すTRUNC関数を使う。
出てきた数字の1の位を切り上げし、再度（桁数-4）×10を掛ける。
桁数が4以下、すなわち10,000未満の数字の内、10以上のものを取り出すため、再度IF関数で桁数>1とする。
10以上のものは、1の位を切り捨て（ROUNDDOWN）、CEILING関数で50の倍数にする。
上記の条件に当てはまらない1桁の数字の場合は、0とする。

お礼

説明不足でもうしわけありませんでした。　解決しました。
引っかかったのど元の骨が取れた気分です。　どうもありがとうございました。

回答No.1

aokii

各処理結果を別の各列セルで行っておいて、入力した数値の条件で処理結果の列データを選択してはいかがでしょう。

お礼

どうもありがとうございました。　無事解決しました。