質問: excelにてサイズ軽量化のために数式を改良できますでしょうか excel2019にて、ブック全体のファイルサイズを軽量化させたいと考えています。 入力シート×1、まとめシート×1、分析シート×6 の全8シートで構成されています。 このブックを原本とし、対象に合わせて項目数を減らし使用していく予定です。 (画像は項目数が1つの場合のもので、画像の範囲×15(横方向)が原本サイズの予定です) ファイルサイズが大きい原因として一番疑わしいのが各分析シートのデータ数(数式)であり、以下の2式が『数式の検証』途中で応答なしのまま固まることが判明しました。 式そのものが思った通りの計算をしてくれているのかも検証できない為、数式の目的も併せて記載します。 1{=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"を変えただけのコピーシート 2 =iferror(if((下限規格-階級幅)<(データ最小値-階級幅),(下限規格-階級幅),(データ最小値-階級幅)),(上限規格+階級幅)-(20*階級幅)) (実際には同シートのセル番地で指定しています) →目的:(下限規格-階級幅)と(データ最小値-階級幅)の小さい方を表示したい、もし下限規格がない場合(エラー出た場合)には((上限規格+階級幅)-(20*階級幅))を表示したい ・1で抽出されたデータからヒストグラムを作成するために使用している式(階級下限を決める) ・各分析シートに1~15セル分この式が入っている(1シートあたりの最高で15セルほど) ・各セルは隣接していないものもある この2式は短縮、もしくは他の数式に置き換えなど可能でしょうか。 軽量化に加え、よりわかりやすい式になればぜひそちらに変更したいと考えております。 ですがインターネットから拾った数式を改変使用しているだけでなく、数式の計算が動作不良で検証出来ていない状態です。 (シート自体の計算は5~10秒ほどで出来るので、そちらでの確認のみ) 目的と数式が合ってない、という場合には目的の方を優先していただけると幸いです。 よろしくお願いいたします。
回答: こんにちは 具体的内容が理解できていませんけれど・・・ 式が長ければ、確かにブックのデータ量は増えますが、高々文字列の長さ分だけとも言えます。 「ファイルサイズが大きい」ことを問題視していらっしゃいますが、「『数式の検証』途中で応答なしのまま固まることが判明しました。」が本当の問題なのではないですか?? (いわゆる、「重い」と言われる反応が鈍い状態) ご提示の式(1)自体にも無駄があるように見受けられますが、そもそも配列計算自体には無駄な計算が多く含まれます。 例えば、「a1:a10000」の範囲を減ずるだけでも、計算量を相当に減少させることができます。 また、作業列を利用して抽出する方法に切り替えれば、計算量は大幅に減らせますし、ブック全体の式の文字数もかなり減らせるものと推測します。 (式を単純化することで、多少は軽くできると思われますが、配列計算であることに変わりはないので、効果はさほど出ないものと推測します。) 1の式が使用されているセル数がよくわかりませんが、セル数が多いほど負荷は大きくなり、重くなる原因となります。 ご提示の図で、どこに何があるのか不明ですが、式が面的に配置されているのならなおさらです。 作業列を用いて抽出する方法の一例として、入力用シートに作業列をあらかじめ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行目以降もまとめて一つにすることも可能ですが、あえて二つに分けてあります) 2の式の、具体的な内容はわかりませんが、例えば「下限規格」や「階級幅」などを何度も計算させているようであれば、一旦どこかの空きセルに計算させていおいて、それを参照するような式にすることで、計算量を減らせるでしょう。 とはいえ、個所数が15セル程度らしいので、よほど重い計算でない限りはあまり差はないものと推測します。 ちなみに、以下は、配列計算のまま1の式を多少効率的にしてみたものです。 (具体例が不明なため、机上で変換しただけなので、間違っているかもしれませんが...ご参考までに) =iferror(index(入力用!a:a,aggregate(15,6,row(a$1:a$1000)/(find("6",入力用!a$1:a$1000)>0),row(a1))),"") (※ ↑の式は配列数式として入力する必要はありません) とはいえ、上記のように内容的には配列計算のままですので、速度の点からは決してお薦めできるものではありません。