gooドクター有料プランが1ヶ月間無料!

Excel2019にて、ブック全体のファイルサイズを軽量化させたいと考えています。
入力シート×1、まとめシート×1、分析シート×6 の全8シートで構成されています。
このブックを原本とし、対象に合わせて項目数を減らし使用していく予定です。
(画像は項目数が1つの場合のもので、画像の範囲×15(横方向)が原本サイズの予定です)

ファイルサイズが大きい原因として一番疑わしいのが各分析シートのデータ数(数式)であり、以下の2式が『数式の検証』途中で応答なしのまま固まることが判明しました。
式そのものが思った通りの計算をしてくれているのかも検証できない為、数式の目的も併せて記載します。



 ①{=IFERROR(INDEX(入力用!$C$1:$C$10000,MATCH(LARGE(ISNUMBER(FIND("6",入力用!$A$1:$A$10000))*1/ROW(入力用!$A$1:$A$10000),ROWS(入力用!$A$1:$A1)),1/ROW(入力用!$A$1:$A$10000),0),COLUMNS(入力用!$A$1:$A1)),"")}

→目的:順不同に入力されている入力シートから、A列(作業列)に 6 を含んだ行のC列(データ)の値を全て抽出したい、ただしA列が空白の場合に出るエラーは表示させたくない
    (入力シートにデータを入れるだけで自動的に分析シートへ抽出してほしい)
  ・6を含むのが最高で900程、入力シートはデータが縦方向に増えていく
  ・分析シート1~6は参照先"6"を変えただけのコピーシート
  

 ② =IFERROR(IF((下限規格-階級幅)<(データ最小値-階級幅),(下限規格-階級幅),(データ最小値-階級幅)),(上限規格+階級幅)-(20*階級幅))
  (実際には同シートのセル番地で指定しています)

→目的:(下限規格-階級幅)と(データ最小値-階級幅)の小さい方を表示したい、もし下限規格がない場合(エラー出た場合)には((上限規格+階級幅)-(20*階級幅))を表示したい
  ・①で抽出されたデータからヒストグラムを作成するために使用している式(階級下限を決める)
  ・各分析シートに1~15セル分この式が入っている(1シートあたりの最高で15セルほど)
  ・各セルは隣接していないものもある



この2式は短縮、もしくは他の数式に置き換えなど可能でしょうか。
軽量化に加え、よりわかりやすい式になればぜひそちらに変更したいと考えております。

ですがインターネットから拾った数式を改変使用しているだけでなく、数式の計算が動作不良で検証出来ていない状態です。
(シート自体の計算は5~10秒ほどで出来るので、そちらでの確認のみ)
目的と数式が合ってない、という場合には目的の方を優先していただけると幸いです。


よろしくお願いいたします。

「excelにてサイズ軽量化のために数式を」の質問画像
gooドクター

A 回答 (3件)

No2です



文章の説明だけではわかりにくいと思いましたので、ミニチュア版のモデルを作成してみました。
投稿の都合上、1枚のシートで処理していますが、要領は同じです。

添付図で、
・左側が元データ(A:C列の3列のみ)とし、E列が「6」用の作業列とします。
・右側が抽出シートで、G列を作業列に使用しています。
※ 作業列を赤色にして目立たせてありますが、実際には、見えない位置の遠い空き列にしたり、非表示などにしておけば良いでしょう。


1)E2セルに
 =IF(ISERROR(FIND("6",A2)),"",COUNT(E$1:E1)+1)
を入れて、下方にデータ分だけフィルコピーします。
この結果、E列には「6」の該当行だけに、上から順に番号が表示されます。

2)抽出側の作業列のG列で、G3セルに
 =IFERROR(MATCH(ROW(A1),$E$2:$E$99,0)+1,"")
を入れて、下方に(適当な範囲まで)フィルコピーします。
この結果、G列には該当する行の元の行番号が詰めて抽出されます。
(↑の式は元データの対象範囲を、ひとまず2~99行としてあります)

3)元の表を参照するために、H3セルに
 =IF($G3="","",INDEX(A:A,$G3))
の式を入れて、H3:J3にフィルコピーし、更に下方にフィルコピーしています。

※ 作業列を使用するので、関数を入れるセルの数は増えますが、それぞれが単純で短かな式であり、計算負荷も少ないので、問題にはならないはずです。
※ 1枚のシート上のモデルなので、実際にはアドレスの調整等が必要になるとは思いますが、ご参考にでもなれば。
(一度、同じものを作成していろいろいじってみると、理解しやすいと思います)
「excelにてサイズ軽量化のために数式を」の回答画像3
    • good
    • 0
この回答へのお礼

わざわざモデルまで作成していただきありがとうございます。
こちらで挙げてくださったように作業列を追加し、ご教示いただいた数式へ変更を行ってみました。

アドレスの調整をしただけなのですが…本当に、理想としていた動きがそのまま再現されて感動しております。計算箇所を重複させないようにして、参照という形をとってあげるのが最適だということがよくわかりました。
質問文①式の変更のみで劇的に改善されましたので、②式の方はそのまま使って大丈夫そうです。もともとこちらは負荷がかかる計算ではなく、①式の負荷の影響だったのかも知れません。

今までが嘘だったかのように動作が軽くなり、手動更新でしか運用できなかったシートが自動更新できるまでに変化しました。なんと御礼を申し上げたら良いか…本当にありがとうございました。
無理に長ったらしく複雑な計算をさせるより、場合によってしっかりと作業セルをとり見やすい数式を作れるよう心掛けていきたいと思います。
本当にありがとうございました。

お礼日時:2021/02/10 09:06

No1です



>①の配列数式は900セル(150行6列)に面として入力されています。
各配列計算は同じような負荷のかかる計算を、全部のセルで行っていることになります。
抽出の場合、大抵は、該当行の行番号を基にIndex関数で参照する場合が多いですが、行番号の計算(検索)にかなりの量の計算を行っています。

表形式で抽出する場合、横一行は同じ行を参照しているので、まったく同じ行番号を求める計算を繰り返しているということになります。
例えば、これに作業列を追加して、一旦、作業列に行番号を計算しておいて、表での参照はこの作業列の値を参照して行うようにすれば、一つの行で行番号算出の計算は1回だけですむことになり、負荷のかかる計算を大幅に削減することが可能です。

更に、(No1でも述べましたが)行番号の算出計算も、配列計算で行うよりも作業列を利用して検索するような方式にすれば、計算負荷を減少させることができます。
要は、同じ計算を繰り返し行うことを避け、計算をした結果を作業列(セル)等に一旦保持させておいて、これを参照するような方法をとることで、重複した計算をなくし、大幅に計算量を減らすことができるということです。

このような方法のメリットは、使用セルの数が多くなるものの、個々の関数式は単純なものになるので、将来のメンテナンスや変更を行う際に容易であるということもあります。
ご質問文に例示の①の式は、簡単には修正できないと思いますけれど・・・
    • good
    • 0

こんにちは



具体的内容が理解できていませんけれど・・・

式が長ければ、確かにブックのデータ量は増えますが、高々文字列の長さ分だけとも言えます。
「ファイルサイズが大きい」ことを問題視していらっしゃいますが、「『数式の検証』途中で応答なしのまま固まることが判明しました。」が本当の問題なのではないですか??
(いわゆる、「重い」と言われる反応が鈍い状態)

ご提示の式(①)自体にも無駄があるように見受けられますが、そもそも配列計算自体には無駄な計算が多く含まれます。
例えば、「A1:A10000」の範囲を減ずるだけでも、計算量を相当に減少させることができます。
また、作業列を利用して抽出する方法に切り替えれば、計算量は大幅に減らせますし、ブック全体の式の文字数もかなり減らせるものと推測します。
(式を単純化することで、多少は軽くできると思われますが、配列計算であることに変わりはないので、効果はさほど出ないものと推測します。)

①の式が使用されているセル数がよくわかりませんが、セル数が多いほど負荷は大きくなり、重くなる原因となります。
ご提示の図で、どこに何があるのか不明ですが、式が面的に配置されているのならなおさらです。

作業列を用いて抽出する方法の一例として、入力用シートに作業列をあらかじめ6列設け、分析シート"1"~"6"用としておきます。
"6"用が仮にZ列だとして…
 Z1セルに =IF(ISERROR(FIND("6",A1)),"",1)
 Z2セルに =IF(ISERROR(FIND("6",A2)),"",COUNT(Z$1:Z1)+1)
の式を入れ、Z2セルを下方に10000行までフィルコピーしておきます。
これによって、抽出すべき行だけ上から順に1、2、3、・・・と表示されるはずです。
分析シートからは、この列を参照して番号順に抽出すれば、配列計算なしに抽出することができるようになります。
これによって、計算量が大幅に減少することが見込めます。
(上式の1行目と、2行目以降もまとめて一つにすることも可能ですが、あえて二つに分けてあります)


②の式の、具体的な内容はわかりませんが、例えば「下限規格」や「階級幅」などを何度も計算させているようであれば、一旦どこかの空きセルに計算させていおいて、それを参照するような式にすることで、計算量を減らせるでしょう。
とはいえ、個所数が15セル程度らしいので、よほど重い計算でない限りはあまり差はないものと推測します。


ちなみに、以下は、配列計算のまま①の式を多少効率的にしてみたものです。
(具体例が不明なため、机上で変換しただけなので、間違っているかもしれませんが…ご参考までに)
=IFERROR(INDEX(入力用!A:A,AGGREGATE(15,6,ROW(A$1:A$1000)/(FIND("6",入力用!A$1:A$1000)>0),ROW(A1))),"")
(※ ↑の式は配列数式として入力する必要はありません)
とはいえ、上記のように内容的には配列計算のままですので、速度の点からは決してお薦めできるものではありません。
    • good
    • 0
この回答へのお礼

ご回答をありがとうございます。
大変分かりやすく、こちらの意図も汲んでいただけたようなご意見で安心致しました…。

具体例を挙げきる事が出来ず申し訳ありません。ですがご教示頂いた内容で相違なく、やりたい事をしっかり反映していただけたように思います。
仕事から帰宅してしまい実際に試すのは明日になってしまいますので、明日実際に触ってみてからベストアンサーとさせていただきますことをお許しください。

ご指摘のとおり、①の配列数式は900セル(150行6列)に面として入力されています。これが原因で動作が重くなつているんだろうなとは感じておりましたが、やはりこのような使い方だと無駄が多かったのですね…。入力シートになんとか6列の作業スペースを確保して臨みたいと思います。

取り急ぎ御礼だけ失礼致します。

お礼日時:2021/02/09 18:51

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング