Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
セル更新時のエクセルの内部処理
投稿日時: 18/01/17 16:10:28
投稿者: ohlq

計算時間短縮のため(できるかどうかわかりませんが)、
セルを更新した際のエクセル内部でどういう手続きが行われているか把握したいです。
下記1案2案を妄想しましたが実際はどのようになっているのでしょうか?
 
例えばA1-A10のセルの範囲を更新した時に
1案:
 (1)A1のセルを評価
 (2)A1のセルを参照しているセルが登録されていて(?)そのアドレス先のセルを再評価(ループ)
 (3)A1のセルに起因する更新が終わったのち、A2のセルを評価
 (4)以下同様
 (最後)更新したセルを順番に再評価したのち収束すれば正常終了
 
2案:
 (1)A1のセルを評価
 (2)A2のセルを評価・・・A10まで
 (3)A1-A10のセルを参照しているセルを再評価
 (最後)更新したセルを順番に再評価したのち収束すれば正常終了
 
ご教示よろしくお願い致します。

回答
投稿日時: 18/01/17 16:45:27
投稿者: WinArrow
投稿者のウェブサイトに移動

実際の計算順序は、Excel設計者でないと分かりませんが、
処理時間を左右する設定方法・運用方法
(1)計算式は、短い方がよい
(2)配列を使った数式は少ない方がよい
(3)セルの書式を設定する際は、個々のセル毎に設定するより、範囲(例、列、行)を指定した方がよい
(4)名前を使った方がよい
(5)参照深度が深い数式がある場合は、データ入力時は、手動計算にしておき、
  最後(目視確認が必要な場合を除き)に「F9」で再計算する方が効果的
(6)他ブックを参照する数式は、他ブックも開いておく方がよい。
なお
見識者からの追加コメントを期待して・・・・

投稿日時: 18/01/17 17:42:32
投稿者: ohlq

回答ありがとうございます。
勉強になります!
 
確認ですがこちらのアドバイスはメンテ・運用方針ではなく、あくまで処理速度に関することということでよろしいでしょうか。
 
すなわち
>(3)セルの書式を設定する際は、個々のセル毎に設定するより、範囲(例、列、行)を指定した方がよい
処理上、列単位でセルの書式設定をした方が数式処理が速い
 
>(4)名前を使った方がよい
名前定義のセル番地指定のことであっていますか?
セル番地を変数とするのではなく名前定義を使用した方が、メンテナンスしやすいという意味ではなく実際に処理が速い

回答
投稿日時: 18/01/17 18:02:16
投稿者: WinArrow
投稿者のウェブサイトに移動

コンピュータの内部処理としては、
メモリ内の計算対象(含む、参照)セルの場所を求めるのに、
たとえば、ブック→シート→セルというように、各々のアドレスを参照して求めていると思われます。
この場合3階層になります。
 
と考えると
名前定義は、ブックレベルで設定していると
ブック→名前(セルの絶対アドレス)となるので、2階層になります。
 
この推測が当たっていれば、3階層より2階層の方が早いということになります。
 
※私が運用と書いたのは、セルの書式設定や、数式の使い方を職場で統一できれば・・・ということです。
中には、トリッキーな数式を使っているケースがあるが、企業としては、後継者が引き継げる(メンテナンスできる)ことが重要であると思っています。
 

投稿日時: 18/01/17 18:34:40
投稿者: ohlq

回答ありがとうございました。
職業がら、個人運用しかしてないため統一すべき運用方針の話は耳が痛いばかりです。
 
さておき勉強になりました。大変ありがとうございました。

回答
投稿日時: 18/01/17 19:17:33
投稿者: WinArrow
投稿者のウェブサイトに移動

一つ忘れていました。
 
揮発性関数は極力使わない・・・・といってもムリ。
揮発性関数以外の数式は、
その数式が参照しているセルの値が変われば、再計算が動きます。(当たり前です)
ですから、その数式が参照しているセル以外のセルの値が変わっても、そのセルは再計算対象外です。
 
しかし、揮発性関数は、その数式が参照しているセル以外のセルの値が変わっても、再計算されます。
 
ですから、揮発性関数が多用されていると、
思っている以上に処理時間が掛かることがあります。
 
個人で使っているとことですが、
コンピュータ内部の計算ロジックにまで、気を使うような使い方はしないような気がします。
 
 
 
 
 

投稿日時: 18/01/17 23:09:20
投稿者: ohlq

揮発性関数とはseedを指定しないrand()が該当しますか?それ以外にもあるのでしょうか?
(というかそれ以外の関数に心当たりがないのですが。)
 
ついで、の質問で恐縮ですが次の例は非常に処理が重たいのです・・・
対処方法はあるんでしょうか。
・数万行程度あるデータのフィルターのチェックオン時
 (フィルター対象列が少ないと軽くなったりする?)
・ピボットテーブルが存在するブック(特にMacだと重い)
 (ピボットテーブルのセルは参照しない方が良い?)

回答
投稿日時: 18/01/18 08:13:17
投稿者: WinArrow
投稿者のウェブサイトに移動

RAND関数は、揮発性関数の一つですが、たくさんありますよ!
ポピュラーなのは、TODAY関数、OFFSET関数など、
くわしくは、ネット検索してみて
 
 
ピボットテーブルは、配列を使っていますから、所持時間に影響するで主ね?
 
配列を扱う例として、VLOOKUP関数があります。
VLOOKUP関数は、「検索」が第一義であることは、周知の通りですが、
Excel側からみると、「無い」ことを証明することでもあります。
数万件の探して「無い」を証明するには全件見ないといけません。
ところが、配列がわの表が昇順に並んでしるという前提ならば、
2分割検索で、全件探さなくても「無い」は証明できます。
物事は、使う側から見るのではなく、「使われる側から見る」
ということも大事ではないでしょうか?
 

投稿日時: 18/01/18 13:54:28
投稿者: ohlq

揮発性関数で検索するとパフォーマンスのことがたくさん引っかかりますね、
調べてみます。
Today関数とかはすぐに納得できましたがOFFSETもそうなのか・・・
Vlookupの代わりにOFFSETを使用して処理速度を上げるようにした過去のケースもありましたがきちんと把握しておかないといけませんね。

投稿日時: 18/01/18 14:01:03
投稿者: ohlq

*vlookupの代わりに使用して他のはoffsetではなくindex(match)でした。

投稿日時: 18/01/18 18:44:44
投稿者: ohlq

いろいろ勉強になりました。
最初の質問の内容が悪かったですが
こちらの意図を汲んでご教示くださったWinArrow様、ありがとうございました。
 
過去に遅かった原因の1つはoffsetが犯人らしいです。
精進します。