エクセルのセル式で恐縮ですが、再計算が止まらない

早速ですが
H10に=MATCH(6^6,'15min'!G:G)　で最終ROWを求めます
J10に　= INDIRECT("’15min’!AF"&H10)　他のシートのAFの最下段の値をとります
同じく　
K10に　= INDIRECT("’15min’!AL"&H10)
L10に　= INDIRECT("’15min’!AP"&H10)
この式を使うと　再計算が止まりません。どれか一つ　でもです。
スタック領域が無くなるまで計算します

=LOOKUP(4^4,AP:AP)　などより、負荷が軽いかなと思って　こちらを使ったのですが、

一番負荷の軽い方法は無いでしょうか

No.5 ベストアンサー 回答者： WindFaller 回答日時： 2017/02/27 00:22

わざわざ、試していただきありがとうございました。

今、掲示板のコードを改めて貼り付けてみましたが、問題がありません。

ページファイル＝0
マイナス値はいいとして、やっぱり何かがあるということでしょうね。

=MemoryInfo(1)

今、ずっと仮想メモリのことを追いかけて調べてみました。
http://pc-kaizen.com/virtual-memory

仮想メモリ(ページファイリング)の値はどのぐらいに設定すれば最適なのか？

「プログラムやアプリケーションソフトによっては仮想メモリを要求する場合があるので、仮想メモリ(ページファイリング)をゼロまたは無効に設定するのはあまりおすすめしません。

物理メモリが何らかの理由で使い切ってしまった場合は、処理ができず、開いてあるプログラムやアプリケーションソフトがエラーを起こし、固まってしまったり、パソコン(Windows)が不安定になる可能性があるからです。

ただ、仮想メモリ(ページファイリング)をゼロまたは無効に設定したからといって、パソコンが壊れるわけではないので、一度、ゼロまたは無効に設定して、色々プログラムやアプリケーションソフトを立ち上げて試してみるのもいいと思います。」

ということは、そこが、「0」もありえるということです。

ここらあたりの話は、Excelのメモリの問題では、多少は知ってはいたものの、0もあるのは、今回は、初めて知りました。

------------------
エクセル2010,07　WIN10,WIN7で
ページファイル＝0
----------------
Excel 2007 で試してみました。
使用可能なページファイル：871,620kb
仮想メモリサイズ：2,097,024kb
使用可能な仮想メモリ：1,653,852kb

--------------

確認の仕方はご存知かもしれませんが、
スタートメニューで右クリック--コントロールパネル--システムとセキュリティ
--システム

左脇のシステムの詳細設定--詳細設定--パフォーマンス
設定--パフォーマンスオプション--詳細設定--

仮想メモリ
　・ページファイルとはハードディスク上の領域で……

すべてのドライブの総ページングファイルサイズ--変更
画像は、その最後の変更の画面です。多いのか少ないのかはわかりませんが、昔から、デフォルトの倍ぐらいの設定をしてきました。

この回答へのお礼

お手数をおかけします。

ご指摘の部分は、以前別問題で触った所ではあります。
現状、メモリーが　16G　で　仮想メモリーファイル　5625mb　とセットしてあります。

ページファイル＝0
別の角度から考えてみます。ありがとうございました。

お礼日時：2017/02/27 19:16

No.4 回答者： WindFaller 回答日時： 2017/02/26 13:35

#3の訂正します。

Function MemoryInfo(arg As Variant)
　Dim msg As String, MemData As MEMORYSTATUS
　　↓
正しくは、
Function MemoryInfo(arg As Variant)
　Dim buf As String, MemData As MEMORYSTATUS

この回答へのお礼

恐縮です。早速ですが
エクセル2013で　WIN10,7　両方とも
ページファイル＝0
仮想メモリサイズ＝-957.396
使用可能な＝-871.088
とマイナス表記
エクセル2010,07　WIN10,WIN7で
ページファイル＝0
仮想メモリサイズ＝2.017024
使用可能な＝1.602116

気になるのが、どちらも、ページファイル＝0
コマンドの参照の問題か当方のWIN、エクセル等の設定か　
分かりかねるのですが、宜しければ、ご考察願えれば幸いです
とりいそぎ

お礼日時：2017/02/26 19:28

No.3 回答者： WindFaller 回答日時： 2017/02/26 13:25

#2の回答者です。

最初に、不揮発性関数と揮発性関数という言葉はもうご存知だと思います。私が、INDIRECT 関数からINDEX関数に換えると申しましたのは、その揮発性から不揮発性に換えることを意味していたわけです。

不揮発性というのは、再計算の際に、値の変わるものだけ変わって、後は、値が残っているということで、再計算をしなくて済むという利点があります。

それと、「スタック領域が無くなるまで計算します」というのは、一種の無限ループのようなもので、通常では現れないはずです。

前回のMicrosoft の引用先では、
規則その 3: 高機能再計算を適切に利用する
Excel の高機能再計算をより適切に利用すれば、それだけ少ない処理で毎回の再計算を実行できます。そこで、次のことを実践します。

•他の関数よりも大幅に効率的でない限り、INDIRECT や OFFSET などの揮発性関数は使用しないでください (適切に設計されていれば OFFSET は一般的に高速です)。

•配列数式や関数で使用する範囲のサイズを最小にします。

•配列数式やメガ関数は、別の補助的な列や行に分割します。

とはいえ、もしかしたら、それを「計数化」いわゆる「みえる化」させるということで、こちらにお聞きになったのでしょうか。

私は、こういう勉強はしたことがなかったので、正直なところ、とても難しいです。

>どちらが負荷が軽いか、
これについては、安全装置として、Excel2003 までは、INFO関数 MemoryUsed という引数があったのですが、今は見当たりません。

ここで、田中享氏が、Win32 APIを使って、その状態を測ろうとしています。
http://officetanaka.net/excel/vba/tips/tips71.htm

お使いになるかはわかりませんが、そこから、必要なものだけを抜き出してユーザー定義関数にしてみました。

Private Declare Sub GlobalMemoryStatus Lib "kernel32" (lpBuffer As MEMORYSTATUS)

Private Type MEMORYSTATUS
　　dwLength As Long
　　dwMemoryLoad As Long
　　dwTotalPhys As Long
　　dwAvailPhys As Long
　　dwTotalPageFile As Long
　　dwAvailPageFile As Long
　　dwTotalVirtual As Long
　　dwAvailVirtual As Long
End Type

Function MemoryInfo(arg As Variant)
　Dim msg As String, MemData As MEMORYSTATUS
　GlobalMemoryStatus MemData
　Application.Volatile
　With MemData
　　Select Case arg
　　　Case 1
　　　　buf = "使用可能なページファイル：" & Format(.dwAvailPageFile / 1024, "#,##0")
　　　Case 2
　　　　buf = "仮想メモリサイズ：" & Format(.dwTotalVirtual / 1024, "#,##0")
　　　Case 3
　　　　buf = "使用可能な仮想メモリ：" & Format(.dwAvailVirtual / 1024, "#,##0")
　　End Select
　End With
　
　MemoryInfo = buf & "kb"
End Function

使い方
=MemoryInfo(ROW(A1))
下に2行、ドラッグコピーをします。

そうすると、以下のように出てきます。
使用可能なページファイル：1,618,444kb
仮想メモリサイズ：2,097,024kb
使用可能な仮想メモリ：1,278,752kb　(ここで決まります）

これを値コピーして、定数化させます。

この関数を使って条件を変えて測ってみる(ファンクションキーF9を押す）と、そのたびごとに数値が変わります。特に、その違いは、仮想メモリのところで顕著に出てきます。それで、様子をみたらいかがと思います。

No.2 回答者： WindFaller 回答日時： 2017/02/26 02:56

ずっと気になっていて、やっと思い出しました。

ただし、Excelのバージョンによって、この現象は変わりますから、必ずしも正解ではありません。一般的な解決策です。

J10に　= INDIRECT("’15min’!AF"&H10)　
これらのINDIRECT関数の数式が問題ですね。

全部、INDEX関数に書き換えてください。
=INDEX('15min'!AF:AF,H10,1)

>この式を使うと　再計算が止まりません。どれか一つ　でもです。
しかし、これは、INDIRECT 関数とは別のものが原因だと思います。INDIRECT関数だけでは、そのような現象は置きません。再読込している何か別のものがありませんか？


参考：Excel 2010 のパフォーマンス: 計算パフォーマンスの強化
https://msdn.microsoft.com/ja-jp/library/office/ …

この回答へのお礼

有難うございます。アドレス参考になります

現状
=IF(ISERROR(AB9), LOOKUP(7^7,G:G),AB9)
や　=LOOKUP(4^4,AF:AF)
なら　再計算は起きないのです・・・。

まあ、どちらが負荷が軽いか、程度の悩みではあるのですが。

お礼日時：2017/02/26 07:59

No.1 回答者： 銀鱗 回答日時： 2017/02/25 15:32

(´・ω・`)良く分かりませんが…

演算方式をオプションで手動にして、再計算が必要な時にF9キーを押すとか…。