『エクセルの処理が遅い集計の動作緩慢の改善』のクチコミ掲示板

[ぶうぶうぶう。でも人間です]

【使いたい環境や用途】
Windows11 Pro
CPU i5-13500 2.5GHz
メモリ 8GB

Excel 2021 32ビット版

情報量が多いだろうExcelファイルの集計の緩慢な動作の改善ができたら良いと考えています

ファイルを開くときに長く時間を要するや、セルを書き換えたときに更新完了までに操作不能で長い待ち時間がある等

ファイルは行数が多い（数十万行）・数百MBや
if()で判別、vlookup()で特定やindirect()で引用
および算術式で演算など

複雑なことはしていないと思います

タスクマネージャではメモリはエクセルの使用だけで占有されている様に見えます。90%超くらい。CPUは数%で働いていない様に見えます

【重視するポイント】
対策には何があるでしょうか
又は有効な対策は無いなら諦めて現状のままか
或いは対策を検討する為に必要な未実施の調査

１）エクセルは64bit版の導入で改善できるか
２）メモリ増設の効果は期待できるかどうか
３）１と２の両方で効果はアップするか
４）追加の調査でまた考える
５）その他

【予算】
数万円

【比較している製品型番やサービス】
無し

【質問内容、その他コメント】
現在は最初のファイルである程度処理を進めて
途中迄の集計結果を別のファイルに値で貼付け
次のファイルで処理を再開し…と進めています

ファイル・作業の分割が足りなければ
ファイル・作業を３つ以上に分けるなど

現在はこれでも良いけれど
改善できるのであれば改善したい
何かをするときにいちいち待ち時間が長過ぎる

原始ファイルが大き過ぎるのは、与えられるものなので対策できません

書込番号：26553148　スマートフォンサイトからの書き込み

[キハ65]

>>１）エクセルは64bit版の導入で改善できるか
>>２）メモリ増設の効果は期待できるかどうか
>>３）１と２の両方で効果はアップするか

Microsoftサイトから。

>大きなデータ セットと 64 ビット版の Excel
>Excel 2010 の 64 ビット バージョンは、32 ビット バージョン アプリケーションのような 2 GB の RAM や、Large Address Aware 32 ビット バージョン アプリケーションのような最大 4 GB の RAM に制限されません。 そのため、64 ビット版の Excel 2010 では、ユーザーはより大きなブックを作成できます。 64 ビット版の Windows では、より大きなアドレス可能メモリ容量を使用でき、Excel は、その容量を利用できるように設計されています。

https://learn.microsoft.com/ja-jp/office/vba/excel/concepts/excel-performance/excel-performance-and-limit-improvements

redditから。

>Excel 用の超すごいパソコンを見つけた方法 (実験データ付き)

>RAM サイズ (GB)
>オンラインでも職場でも、RAM サイズが高速な Excel にどれほど重要であるかを常に聞いていました。まあ、これは本当です... ある程度は。RAM (または短期メモリのスペース) が問題になるのは、ワークブックが大きすぎてコンピューターのスペースが不足し始めた場合だけです。だから、RAM が 4 GB や 8 GB のように小さすぎると、これがボトルネックになります。ただし、RAM が十分に大きい場合、リターンは急速に減少します。

>RAM モンスター Excel ブックの処理にかかる時間 (分)
>8 17
>16 9
>28 8
>32 7.5

https://www.reddit.com/r/excel/comments/1e2iu49/how_i_found_an_absolute_beast_of_a_computer_for/?tl=ja

スレ主さんの場合、Excelを32bit版から64bit版へ変更し、メモリを8GBから16GBへ変更すれば、処理時間の高速化（短縮化）が大いにに期待されます。

書込番号：26553183

[おかめ@桓武平氏]

＞ぶうぶうぶう。でも人間ですさん

>> Excel 2021 32ビット版

Excel 2021 64bit版を選択し、
インストール出来ませんか？
まず、動作を確認して見ることをおすすめします。

次点で、メモリを8GBから16GBにされるといいかと思います。
但し、メモリの価格は、まだ高いのがあります。

書込番号：26553204

[ミッキー2021]

＞ぶうぶうぶう。でも人間ですさん

パット見ですが、処理するデータ量があまりにも多すぎると、と感じましたのでAI検索してみました。
AI回答ですので過信はできませんが、素人掲示板で解決するのは難しいと感じました。

一応、解決するための対策なども書いてありましたが、32ビット版を使うとかメモリ増設である程度
改善されるかもしれませんが、会社の情報処理部門担当の方など、専門の人に相談するこオススメします。


私の質問

>Excelファイルは行数が多い（数十万行）・数百MBや
>if()で判別、vlookup()で特定やindirect()で引用
>および算術式で演算などは時間がかかりますか


AI回答の先頭部分のみ抜粋します。

>はい、非常に時間がかかります。 最悪の場合、Excelがフリーズして（固まって）動かなくなることもあります。
>Excelは数十万行もの大量のデータを処理したり、複雑な計算を何度も繰り返したりするのがあまり得意ではありません。
>なぜ重くなってしまうのか、その理由と対策を分かりやすく解説します。

書込番号：26553262

[ミッキー2021]

↑訂正します

誤　32ビット版を使う
正　64ビット版を使う

書込番号：26553269

[猫猫にゃーご]

VBAを使用しているとして。

大量のデータを処理しながらセル表示を行うと、すごく時間が掛かります。
そんなときは、処理開始時にセル描画を停止し、終了後に描画再開させる
ようにすると、恐ろしい程速度アップします。

↓のように、描画停止と再開で挟むだけ。
Application.ScreenUpdating = False '描画停止
処理(変更不要)
Application.ScreenUpdating = True '描画再開

描画停止中は処理内のセル表示(更新)は行われず、
描画再開で一括表示される。

書込番号：26553288

[ぶうぶうぶう。でも人間です]

了解です

Excelの64ビット版とメモリ増設に期待します
両方を購入しても合計で５万円を超えません

個人で使うのであればExcel64ビット版を試し
結果でメモリ増設を考えるところですが

事業所で使います
Excelを購入して結果でメモリ増設は
集計は様々（今後で新しい集計もあり得る）
結果の判断は計測できるけれど体感で分かる
次にExcel手当てだけでは不足と訴求は難しい
購入の申請と手続きは一度が良いと考えました

Excelが応答不能になるのはしばしばあります
Excelが再起動することもあります
ファイルは復元できることも有るし無いことも

夫々の部署に業務プログラム担当者は居ます
全体の管理者は居ないも同じです
各部署とメーカを中継する専任の人員は居ます
考えられるのはソフトウェアの変更くらいです
有償で数字はおそらくは高額と予想できます

現状では集計担当者が苦労する方が妥当です
あるいは少額の投資

書込番号：26553292　スマートフォンサイトからの書き込み

[ぶうぶうぶう。でも人間です]

了解です

プログラミングはできます
画面の非表示と表示再開の実装は経験あります
VBAは使わずに関数だけで処理します

システムの運用管理をする部署がありません

そういう風なので、ユーザが用意するプログラムは不適当でしょう。ユーザがプログラムを作成して実行することも疑問ですが、許されています

作成者が居なくなると保守が続きません。今後でデータ側に変更が発生して結果が不正でも、気づかないかもしれません

集計用のExcelファイルを個々に用意しました
データのファイル（CSVまたはTXT）のシート全体をコピーして
集計用のExcelファイルの所定のシートに値で貼り付けます
集計用のExcelファイルの別のシートでデータのファイル（シート）の必要な列だけを引用・展開します
次に関数だけで処理します

そう言えばデータは数値を含めて文字列を扱う関数も多用します。find()、mid()やcountifs()等
未だあるかもしれません
手続きは稚拙です

手続き・関数の割り当てで工夫する余地はあまり残されていないと思います。返信１件目で案内されたURL先を閲覧して、新しく組み込まれた関数に変更すれば、改善はあるかもしれないと思いました。ありがとうございます

今は関数だけで処理するのが良いと考えます

用意した集計用のExcelファイルが使えないときが来たら以前と同じ様に、Excelのフィルタの動作の多用で、集計対象の情報を抽出することになるのだろうと予想します

書込番号：26553323　スマートフォンサイトからの書き込み

[むうsan]

ハードの改善効果はあると思いますが、自分からは設定での編集操作感の改善です。

・自動計算を手動に変更してください。
　値の変更をひと通り実施後、再計算させる。再計算時間は変わらないですが、ファイルを開く時と編集中のストレスが減ります。

変更方法)
1.上部メニュー[数式]-[計算方法]-[計算方法の設定]を選択する
2.[手動]を選択する
Excel本文に設定方法を書いておけば忘れないです。

参考)
https://atmarkit.itmedia.co.jp/ait/articles/2002/13/news008.html

書込番号：26553359

[ありりん00615]

大量データーを扱う場合は、2021でも利用可能なXLOOKUPに置き換えることで処理時間の短縮が可能です。
https://note.com/super_godwit303/n/n644c318af50b

しかし、メモリ容量はより重要だと思います。メモリーが十分にあってもコアを制限しないと性能が発揮できないケースもあるようです。
https://www.reddit.com/r/excel/comments/1gcv21s/results_of_testing_large_excel_model_calculation/?tl=ja

書込番号：26553369

[turion]

＞Excelのフィルタの動作の多用で、集計対象の情報を抽出する

データをaccessで使う、インポートするとか

数値データであればピボットテーブルを使用するとかは考えられます

書込番号：26553396

[ぶうぶうぶう。でも人間です]

ご提案ありがとうございます
自動計算を止めて手動にする
そうでした
そうします
すっかり忘れていました

なるほどです
lookup()関数をxlookup()に変更で変化を見ます 他にも変更して効果を感じられる関数はあるかもしれません
試します

そういうこともあると思います
Accessは使えません
ピボットテーブルは使い方を知ることが必要
データを出力する操作の手順は固定にできます
手順書を用意すれば誰でも出力できるでしょう
出力したデータをExcelで開いて
集計用のExcelファイルを開き
所定のシートにデータを値で貼り付ける
用意した結果が得られる
一連の作業は簡単なことが求められます
あれはこうしてこれはああしては避けたいです

書込番号：26553435　スマートフォンサイトからの書き込み

[turion]

＞データを出力する操作の手順は固定にできます
＞手順書を用意すれば誰でも出力できるでしょう
＞出力したデータをExcelで開いて

一つのデータベースから複数のピボットテーブルを予め作成しておけばできそうに思います

書込番号：26553449

[ぶうぶうぶう。でも人間です]

はい
分かりました

目的の集計の(複数の)ピボットテーブルによる実現を考えます

集計の振り分け、集計単位が不定で月により有り無しあると思うけれど、対応できるかどうか得られる結果の並びは順不同かもしれません

そういうことがあるが故に、lookup()とindirect()が重宝します

ピボットテーブルが何とでもなるif()と同様に使えるかどうかもあると思います

書込番号：26553482　スマートフォンサイトからの書き込み

[ぶうぶうぶう。でも人間です]

https://learn.microsoft.com/ja-jp/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance

計算速度の向上と問題の軽減

複雑な大量の数式や配列数式を使用しないで
行と列を増やして、複雑な計算を減らす

とあります

ひとつのセルでif()で実行可否を判断してlookup()で目的のセル、行と列を特定し、indirect()で特定したセルのデータを引用する
↓
式の数だけ列を増やしてそれぞれの列に割り当てる

やってみれば効果は分かるでしょうと思います

とか

揮発性関数？のindirect()は使わない方が良いらしいから、他の処理で置き換えることを考える

とか

全行で結果を得るまでを同じ様に処理せずに
ひとつの処理、判断で次に処理する対象行を特定して、次の処理の前に整列して並べ替える
処理が進むに連れて処理する行が徐々に減る

とか

処理で減るはずの処理すべき行数、関数を割り当てる行数は予めには分からないから、「多分これくらい」で用意して、処理できる行数の上下に達するかは調べて、不足するなら処理をする行数をコピーとペーストで下方に展開する

とか

とりあえずは

書込番号：26553543　スマートフォンサイトからの書き込み

[揚げないかつパン]

個人的にはVBA+PowerQueryで自動集計するロジックを組んだ方が良さそうとは思いますが

書込番号：26553571　スマートフォンサイトからの書き込み

[ぶうぶうぶう。でも人間です]

提供されるデータのファイルは月単位のことも多く、長期間の集計では複数の（５年で60くらいまで）ファイルをひとつにまとめて集計します

手作業でひとつにまとめるのは辛いので
固定したパス名のフォルダにファイルを集めて
ひとつに纏める作業はプログラミングしました

マクロ（VBA）のファイルを開いてフォルダ以下のファイルはワイルド・カードや拡張子などで指定して特定して

データのファイルをひとつ開く
全体をコピーする
まとめ用のファイル（VBAで記述したファイル）に切り替えて値で貼る
カーソルは有意行の末尾の次の行に位置づける
データのファイルに切り替えファイルは閉じる
次のファイルを開いて同じ様に処理する…

毎年毎月の個々の集計をプログラミングするのは、記述済みの理由でどうかと考えています

読む気があればセルに割り当てた関数の記述を読めば動作は分かる方が敷居は低いと思います

書込番号：26553613　スマートフォンサイトからの書き込み