計算を速くするために単純化したいのですが、これ以上は難しいでしょうか？

質問

A1に1から100までの整数を入力し、
A2には
　A1が100以下の場合A
　A1が80以下の場合B
　A1が60以下の場合C
　A1が40以下の場合D
　A1が20以下の場合E
を表示するために

A2=IF(A1<=20,"E",IF(A1<=40,"D",IF(A1<=60,"C",IF(A1<=80,"B",IF(A1<=100,"A","?")))))

としたのですが、これよりも簡単にする方法はありますでしょうか。

実際のデータはもう少し刻みが多く、計算するデータの数も非常に多いので、計算時間を少しでも速くしたいと考えています。
実際に入力する値も整数で、戻り値も文字列です。

よろしくお願いします。

回答No.2

ham_kamo · ベストアンサー

こういう場合はVLOOKUPを使うといいです。
Sheet2のA1:B6に以下の表を作っておきます。
0　　　E
21　　　D
41　　　C
61　　　B
81　　　A
101　　　?

あとは、
A2=VLOOKUP(A1,Sheet2!A1:B6,2)
とすればいいでしょう。

補足

回答ありがとうございます。
入力する関数は、かなり少なくなりました。

さきほど、↓別の質問を見ていたところ、
http://okwave.jp/qa2539700.html
回答2で「VLOOKUP系の参照関数や配列数式はかなりのマシンパワーを浪費します～」とありました。

今回のIF関数でたくさん入力するのと、VLOOKUPでシンプルにまとめるのでは、一般的にはどちらが計算が速いのでしょうか？
もしご存じでしたらよろしくお願いします。

回答No.10

KenKen_SP

> 「ブックに記入された「数式の数」が問題になって来ます。」
> とおっしゃっており、数式の数としては１つですが、MIDとMATCHで
> ２つ関数があるので、若干不利となってしまうのでしょうか？

ひとつの数式内で使用されている関数の種類や数が重要なのではなく、

　　「ワークシート内の数式の総量」

がパフォーマンスを考える上では重要であり、これを「極力少なくする」よう
にシートを設計するというのが、

　　「ブックに記入された「数式の数」が問題になって来ます。」

の意図だと思います。例えば...

　　・お題：　D1～D10000 にある金額を各行で千円未満切捨てし、
　　　　　　　その合計を求める。(表示は特に必要なし）
　　・解Ａ：　作業列 E1:E10000 を設け、= ROUNDDOWN(D1/1000,0）とし、
　　　　　　　各行の千未満切捨ての金額を計算し、それを SUMで合計
　　・解Ｂ：　=SUMPRODUCT(ROUNDDOWN(D1:D10000/1000,0)) & "千円"

解Ａでは 10001 個の数式が必要なのに対し、解Ｂでは１個の数式で済みます。
このような工夫で省ける数式の数を極力まで減らします。

Excel では数式の参照するセルの値を書き換えると 再計算が発生します。
値を書き換える度、10001 個のセルの数式を計算するか、1 個のセルの数式
を計算するのか。

この計算の総量が一番パフォーマンスに影響するのです。

したがって、MIDとMATCHで２つの関数があるから不利...という点はさほど
重要ではなく、パフォーマンスを考える上では上記のような工夫が必要です。

使い慣れた方、あるいは、分かり易い方を使えば良いのだと思います。

補足

わかりやすい説明ありがとうございました。
セル内の関数の数にとらわれるのではなく、ブック全体として計算量を減らすことを心がけようと思います。
ありがとうございました。

回答No.9

Nouble

度々済みません
訂正です

質問者さんのネストの数は仰る通り５ですね
お察しの通り
「(」でカッコが式中同時に最大何個あけられたかで見ればいいと思いますよ

失礼致しました　<(＿ ＿)>

補足

ネストの数は、てっきりIFの数かと思ってました。

=IF(A1>40,IF(A1>80,"A",IF(A1>60,"B","C")),IF(A1>20,"D","E"))
の場合
IF(A1>40,～,～)　→　ネスト1つめ
IF(A1>80,"A",～)　→　ネスト2つめ
IF(A1>60,"B","C")　→　ネスト3つめ
IF(A1>20,"D","E")　→　ネスト2つめ
ということですね。

ありがとうございます。

回答No.8

Nouble

P.S.

おっと！
ネストの深さですか

質問者の示されたIF文はネストが６
私のは３だと思います

なので質問者さんの式だと
後２段ネストを深くすると
エクセルに計算を拒否されてしまいますね

これ困りものなのですよね
見た目式に異常はないのに結果がエラーになるのですから

かく言う私もここでの質問に対し解答しようと作った式がこれに陥り
泣きを見た経験があります　(ーー;)ヾ

回答No.7

Nouble

p.s.
同じif文のネストで書くにしても
２分木法を使うとよりネストが減り
処理も効率化しますよ

=IF(A1>40,IF(A1>80,"A",IF(A1>60,"B","C")),IF(A1>20,"D","E"))

回答No.6

Nouble

ちょっと長くなってしまいましたが
=CHOOSE(MATCH(1,FREQUENCY(A1,{100,80,60,40,20}),0),"A","B","C","D","E","")
とか
=CHAR(SUMPRODUCT(FREQUENCY(A1,{100,80,60,40,20})*{1;2;3;4;5;0})+64)
とかでも出ますが
本来の趣旨からは外れてますかね　(～ ～;)ヾ

MIDでの構文が巧妙過ぎて、
その後では何を書いても霞みますぅ～

ちなみに上段の式は入力値が自然数ならば
小数点以下が付いていようが
マイナスであろうが
101以上であろうが
エラーとせず
ちゃんと処理してくれます

解説はこちらでどうぞ
http://pc.nikkeibp.co.jp/pc21/special/hr/hr6.shtml

補足

回答ありがとうございます。
将来、小数点、マイナス等の取り扱いが必要となるかもしれなかったので助かります。
IF関数以外に、こんなにも方法があるとは、驚きました。

回答No.5

ham_kamo

No.2です。補足拝見しました。
確かにIFのネストもVLOOKUPも多用したり、データが多すぎると非常にマシンに負荷をかけます。が、どっちが計算が速いかまではちょっと私にはわかりません。

ただ、ご指摘のURLにある質問を見たところ、ファイルサイズが30MBとファイルサイズが巨大なので、パフォーマンスの問題が出ているようです。実際にファイルの大きさがそこまで極端に大きくないのであれば、とりあえず気にする必要はないと思います。（とは言え、データの数も非常に多いので、と書いていらっしゃいますね…。どれくらいの大きさなのでしょう）

もし、データ量が多く、ひとつ値を入力するたびに計算に自動時間が走ってExcelが止まってしまうようであれば、
「ツール」＞「オプション」＞「計算方法」
を手動にし、データ入力後に「再計算実行」（F9を押しても可能）するという手もあります。

補足

IF,VLOOKUPそれぞれ試してみようとおもいます。
作成途中で、最終的にどのくらいになるのかまだわかりませんが、現段階で6MB位です。
「計算方法→手動」利用してみます。

回答No.4

KenKen_SP

Match という手もあるかな。エラー処理してないけど。
=MID("ABCDE",MATCH(A1,{100,80,60,40,20},-1),1)

補足

回答ありがとうございます。

回答1の方の意見では、計算速度の判断では、
「ブックに記入された「数式の数」が問題になって来ます。」
とおっしゃっており、数式の数としては１つですが、MIDとMATCHで２つ関数があるので、若干不利となってしまうのでしょうか？

MIDという関数初めて知りました。
１つの答えを求めるのにも、いろいろな関数・組み合わせのアプローチがあるというがわかりました。
ありがとうございます。

回答No.3

mshr1962

A2=LOOKUP(A1,{0,21,41,61,81,101},{"E","D","C","B","A","?"})
または
A2=VLOOKUP(A1,{0,"E";21,"D";41,"C";61,"B";81,"A";101,"?"},2,TRUE)

上記の{}内はセル参照でも可、その場合は下記のように表を作成
0　　"E"
21　 "D"
41　 "C"
61　 "B"
81　 "A"
101　"?"
上記の範囲がD1:E6なら
A2=LOOKUP(A1,D1:D6,E1,E6)
または
A2=VLOOKUP(A1,D1:E6,2,TRUE)

補足

回答ありがとうございます。
LOOKUPでセル参照以外の方法もあるんですね。
知りませんでした。

計算速度を考えた場合、LOOKUPとVLOOKUPでは速度はほとんど変わらないのでしょうか。
それともどちらかが若干速くなったりするのでしょうか。

また、回答2の方にも伺っているのですが、さきほど、↓別の質問を見ていたところ、
http://okwave.jp/qa2539700.html
回答2で「VLOOKUP系の参照関数や配列数式はかなりのマシンパワーを浪費します～」とありました。

IF関数でたくさん入力するのと、VLOOKUPでシンプルにまとめるのでは、一般的にはどちらが計算が速いのでしょうか？
VLOOKUPでセル参照を使わなければ、そこまで負担がかからないような気がするのですが、いかがでしょうか？

もしご存じでしたら回答よろしくお願いします。

回答No.1

hana-hana3

＞実際のデータはもう少し刻みが多く、

ＩＦ文では７つのネスト（重複）しか許可されていないので、それ以上の判断が必要な場合は別な方法で行う必要があります。

シート計算でどれだけの時間がかかるかの判断は、ブックに記入された「数式の数」が問題になって来ます。
複雑な数式を数千個以上登録されていると、再計算に時間が掛るようになってきます。

補足

回答ありがとうございます。

ネスト（重複）というのは、IF関数の入れ子のことを指しているのでしょうか。

今回の
A2=IF(A1<=20,"E",IF(A1<=40,"D",IF(A1<=60,"C",IF(A1<=80,"B",IF(A1<=100,"A","?")))))
の場合、５つと数えればよいのでしょうか？

お手数ですがよろしくお願いします。