じゃばのひまつぶし:Excelで作る競馬ソフト自作教室とJRA-VAN登録ソフト(おずたんシリーズ)のサポートサイトです。
JRA-VAN Data Lab対応 競馬ソフト自作教室
<Excelで競馬データベースを学ぼう>
Excelの応答なし問題と改善策
MyKeiba_JVDB

(更新:2020-03-12)


Excelの応答なしについて(現象と改善策)
(注)ここで紹介する方法はExcelを再起動の都度設定しなおす必要があります。2020-03-12追記
現在私が使っているExcel(Office)のバージョンはOffice365 Soloの中のExcel2016です。以前は単独のExcel2010でしたが、Excel2016になり、1年前にパソコンをWindows10に変えてから頻繁に「Excel応答なし」に遭遇するようになりました。

私の場合、「応答なし」にも次の2つのパターンがあります。
(1)Excel画面がフリーズして何も変化しなくなりタスクマネージャーを開き、Excelを見ると「応答なし」になっている。画面をクリックすると白っぽくなり異常終了する。

(2)「応答なし」と復帰(正常動作)を繰り返すので、動作が非常に遅くなる。遅いまま最後まで動くか、あるいはやがてうんともすんとも動かなくなる。タスクマネージャーは「応答なし」とはなってない(パフォーマンスでみると遅いままの時はCPU使用率が数%以下、うんともすんとも動かないときはCPU使用率が30%位のまま推移。正常に動く場合は、動作率がランダムにに変動)

どちらの場合も、結局はタスクマネージャーのタスク終了でExcelを閉じて、再度起動するしかありません。これまでのデータがセーブできないので、悲鳴をあげるばかり。

どちらも困った問題ですが、ネットを見ると(1)は結構多いようです。私が特に困っている問題は(2)の方ですが、パソコンを変えると何も問題なく動作するので、どこを治せばいいのかわからず試行錯誤を繰り返してきました。

問題が起こる傾向としては、
(1)VBAでAccess形式のmdbファイルとやり取り(特に読み込み時)で起こる。
(2)ボタンで次の動作をさせたり、シートを切り替えてマクロの新しい動作が始まる時に起こる(フリーズする)。
などに大別できます。特に(1)の状況が多いので、VBA+SQLでのAccessファイルのプログラムが適切ではないのかと思い、あれこれと試みました。ネットで見ると[DoEvents]を2回挿入すると効果があるというので、やってみました。確かにシートやフォームの操作には一定の効果があるようですが、解決には至りませんでした。

「応答なし」になる原因はよくわからないようですが、パソコンのCPU性能がアップし過ぎてソフトとの相性が狂ってきているのではないか?と考えています。DoEventsのことはよく理解できていませんが、Windows(OS)とソフトの時間的なズレを調整するのではないかと思います。

パソコン環境
(1)CPU AMD Ryzen 5 Pro 2.4GHz(コア数4、スレッド数8)グラフィックカードなし(HP社)
(2)RAM 8GB
(3)モニター 24インチ(WQHD) 2560x1440 +21インチ(FullHD)1920x1040 の2画面使用。
やってみたこと
(1)Excelの修復・・・しょっちゅう応答なしになるのでExcelのファイルが破損しているかもしれない。効果なし。
Officeアプリケーションを修復する

(2)Excelの再インストール・・・修復でも改善されないので、再インストールするもあまり改善なし。少しマシになった?
上と同じサイト

(3)ハードウエアグラフィックアクセラレータを無効にする
https://www.e-soft.net/post-1599.html
私のパソコンはグラフィックボードがないので、当然ながら改善できず。

(4)Accessでも同じ現象があるという事例を発見。「複数コアのCPU環境で実行中のAccessが応答なしになる」
https://support.microsoft.com/ja-jp/help/2646068
という記事に「レコードセットを処理するスレッドとメモリ管理を行うスレッド間の同期処理が、複数コア環境でのデータ処理量やタイミングに依存して正常に動作しなかった場合」応答なしや異常終了の現象が発生するとあります。まさに似た症状だ!
これはマルチコアなのにCPUを減らして使うというもの。せっかく性能を上げるためのマルチコアなのに・・・
Access自体は使わないソフトですが、mdbファイルを使うので念のためにやってみましたが効果なし。

しかし、Excelでコアを減らしたら、なんと効果がありました!応答時間がずっと速くなり、「応答なし」が出なくなりました。

(5)Excelで使うCPUコアの数を減らす。というのがここで紹介する私の改善策です。マルチコアなのになんで、なんで・・・?
しかし、私の場合は事実です。
改善方法(Excelが使うマルチコアCPUの数を調整する)
タスクマネージャーを開く。
(1)[プロセス]タブでExcelの項目を右クリック

(2)「詳細の表示」をクリック。

(3)[EXCEL.EXE]を右クリックし、[関係の設定]をクリック。

あるいは[詳細]タブから[EXCEL.EXE]を右クリック

(注)タスクマネージャーはタスクバーを右クリックするとメニューにあります。
 
[EXCEL.EXE]を右クリックし、[関係の設定]をクリック。
通常は「すべてのプロセッサ」にチェックが入っていて、マルチコアのCPUがすべて選択された状態です。

(注)まずは、ご自分のPCで後述のリソースモニターで波形とCPU使用率をチェックしてからの方がいいです。
 
すべてのプロセッサのチェックを外して、[CPU0][CPU1]の2つにチェックを入れて「OK」をクリック。  
以下は、効果の確認です。
タスクマネージャーで「パフォーマンス」タブを開き、下側の「リソースモニターを開く」をクリック。
「リソースモニター」の「CPU]タブを開き、[EXCEL.EXE]にチェックを入れる。(Excelの動きが赤い波形で示されます)

実際にExcelで作ったVBAプログラムを動かすと、CPU0/CPU1モニタに稼働状況が見られます。
すべてのプロセッサが使われていた状態では、どのCPUも10%以下の稼働状況で、画面の「状態」欄に時々「応答なし」の表示が赤字で出たり、消えたりを繰り返していました。

CPU2個にすると遅かったデータベースとのやり取りも劇的に速くなりました(元が遅すぎただけ)。CPU8個では20分かかったものが75秒に、「応答なし」も出なくなりました。

マルチコアなのになぜ、しっかり働かないの!?と思いました。
いろいろCPU数を試した結果は
(1)CPUが1つでも速度は20分から3分程度に速くなるが、「応答なし」が起こることがある。

(2)CPU3個だと2個と同様速くなるが、CPU間のモニタ波形のバランスが悪い。均等に動いていない感じ。応答なしは起こらない。

(3)CPU4個にすると速さは10分程度に遅くなり、応答なしも起こる。
という結果で、結局私のパソコンでは、CPU2個で使うのがベストのようです。
他のパソコンの状況
(1)13インチノートPC(Dell社、Windows10、CPU i5 コア数4個、スレッド数8) CPU8個状態で、個々のバランスは悪いが、どのCPUも動作。一番速い。
(2)15インチノートPC(マウス社、Windows8→10にアップグレード、CPU i7 コア数4個、スレッド数8) CPU8個状態で上と同様バランスは悪いがどのCPUも稼働。私の開発PCと同じかそれ以上の速さ。

開発環境のパソコンは昨年春にHPから購入。買いたかったi5コア機種の納期が遅かったので、AMD搭載機種を購入。初のAMD機種だったが、どれも同じだろうと思ったのが間違いだったかもしれない。13インチよりも速いと期待したが、一番遅い!。
WEBなどでのAMD Ryzen5は高評価だったが、Excelとの相性は悪いのかもしれない。まさかHPがマルチコアを活かしきれていないとは思わないが、せっかくマルチコアで性能がアップすると思ったのに、購入以来1年間何を無駄にしてきたのだろうかと腹立たしい。今度AMD機種とHPから買うことはないだろう。
考察
(1)CPUは速くなったのに、応答なしになるのは、モニターが24インチと21インチの2画面にすると表示に時間がかかり、CPUのタイミングが狂い、制御不能になり、応答なしになるのではないか?1画面のみならCPU8個でも行けるかもしれないが、私は、24インチでExcelのワークシートを表示し、21インチでVBAマクロの編集を行うので2画面利用は変えられない。

(2)ノートPCは画面が小さいので表示速度とCPU速度のタイミングがずれないので応答なしにならずに済んでいるのではないか?
 画面がフリーズして白くなる症状は今のところない。今後も応答なしで行ければ中途半端な使い方だが、仕方があるまい。応答なしでイライラするよりもマシ。これでも応答なしが起こるようならPCを買い替えなくては・・・
ご注意
皆さんの中でも「応答なし」でイラついている人がいればこの記述を参考にしてください。ただ皆さんの環境にあうかどうかは自己リスクで行ってください。まずは、タスクマネージャーでリソースモニタを見てCPUが活発に動いているかどうかをチェックしましょう。
(新たな事実:2020-03-11)上記のタスクマネージャーでCPUを減らす方法はパソコンやExcelを再起動すると元の設定に戻ってしまいます。
バッチファイルで設定をし直す方法はありそうですが、面倒ですがタスクマネージャーで設定しなおす方が楽かも。
どうもHPのAMD搭載PCを購入したことがそもそもの間違いのようです。なぜマルチコアなのに遅いのかHPに問い合わせましたが、通常のExcelが動くのであればPCの問題ではありませんという解釈でした。確かにVBAのことまで面倒見切れんだろうなあ。今までずっとIntelコアで来ましたが、何も問題なかったのでAMDも同じだろうと思ったのが間違いだったかな?
馬券を当てて、PCを買い替えたい!1年前に買ったばかりなのに。。。もうHP製は買わんぞい!

バッチ処理の方法は上記のAccessの現象の下の方にあります。(やってみましたが、うまく動きませんでした)
(補足実験データ)CPUの個数を変えて応答なしになるかどうかと処理時間の実験をしてみました。

使ったマクロは毎週の騎手・調教師・馬主の登録頭数を解析するマクロ。13インチノート(Intel i5コア)では1分あまりで完了します。
リソースモニターでEXCEL.EXEを中心にCPUの動作具合とCPU稼働率をチェック。波形が活発に動き、稼働率が高ければCPUがしっかり働いていると考えます。

(1)CPU8個の状態・・・パソコンの基本設定。本来は一番性能が高いはず(ですよね)
実験結果は、ほとんどのCPUの稼働波形が動かず、ちゃんと動いていないと思われる。CPU稼働率は1〜3%で推移。
あたかも沢山人数がいるために、「俺が動かんでも誰かが働くやろ」という無責任な烏合の衆みたい。
マクロの実行時間は1107秒(約18.5分)。応答なしも頻繁に起こります。(ただしタスクマネージャーの「プロセス」タブのExcelには応答なしは出ない)

(2)CPU2個の場合・・・CPU0/CPU1の2個のみ使用
実験結果は、2つのCPUの波形は活発に流れ、仕事がはかどる感じがわかります。
まるで責任感に目覚めて「俺がやらずに誰がやる」という意気込み。稼働率は15%位で推移。当然「応答なし」は起こりません・
マクロ実行時間:79秒。まあよしという時間

 (3)CPUを4個の場合・・・CPU0/CPU1/CPU2/CPU3の4個使用
CPU4個の場合はやはり「応答なし」が起こります。図ではCPU3が見えませんが、どんな波形か見てません。
マクロ実行時間:603秒(約10分)
その他実験
CPU3個の場合は「応答なし」は起こりませんが、CPU1/CPU2が動き、CPU0の動きが小さくなります。実行時間は115秒(約2分)。
CPU1個の場合は「応答なし」は起こりませんが、稼働率50%位ですが、実行時間は185秒(約3分)。

結論:CPU2個が稼働波形のバランスもよく、一番高速。
CPUが8個もあるのに役立たずとは・・・どんな検証をしてるのか。。
(次はIntelコアの評価もしてみるか)
(追加実験:2020-03-12)5年以上前のマウスコンピュータ社のPCでも同様な実験をしました。同じソフトで応答なしが起こらないことは確認済みですが、今の新PCがさらに情けないことになる結果となりました。
CPU8個のままで実験(問題が起こっていないのであえてCPUを減らす理由なし)
PC性能:Intel i'-3630QM(@2.4GHz) CPU稼働率20%。マクロ実行時間:70.3秒。新PCの方が遅い!
AMDは5年以上前のPCに負けている。
同じ条件でCPUの7側も確認。
ほぼ同じような波形で、それぞれがちゃんと働いていることが分かった。
CPU8個すべて使用の証拠
古いPCでも応答なしが起こらないので、速いことは想像できたが、数年前のPCの方がずっと速いとはショック。
2年前のノートPCもIntelコアでもっと速いので、もう確認する気がなくなった。

本質的な疑問
(1)なぜマルチコアなのに遅いのか?
(2)すべてのコアの作業がバランスよく進むような制御をしていないのか?
(3)VBAを含むようなアプリについては検証をしていないのだろうか。おそらくしていないのだろう。マイクロソフトもIntel系コアでしか評価していないのかもしれない。私のVBA+SQLがお粗末なのを棚に挙げての疑問。
(4)でも、素人の私が気づくようなお粗末なことを見逃すことがあるのだろうか・・・?
(HPサポートの声:通常のExcel/Wordが正常に動作するのであればパソコンの問題ではありません・・・が耳に残る)

これから毎日、Excelを起動するたびにCPUの個数を設定しなおさなければならないと思うと憂鬱になる。
[教訓]Excelを使うならCPUはAMDではなくIntelにしましょう。
速く切り替えるには?馬券さえ当たれば問題ないが・・・肝心の問題が残ったまま。

ページトップへ
 Copyright(C):Jaba 2000-