[[20230123171258]]　『filter関数の処理を軽くしたい』（tama）　ページの最後に飛ぶ

『filter関数の処理を軽くしたい』（tama）

=@IFERROR((FILTER(sheet2!$W:$W,($H8=sheet2!$I:$I)*(sheet2!$M:$M=TEXT(K$7,"yyyymm")))+FILTER(sheet2!$AA:$AA,($H8=sheet2!$I:$I)*(sheet2!$M:$M=TEXT(K$7,"yyyymm"))))/60,"")

上記関数を最適化したいです。
分かりにくいですが、

　　　　　　　　10月　　11月
佐藤　太郎　　　　〇　　　〇

〇部分に、氏名と年月が合致しているときに、別シートの参照セルを計算し出力したいです。
列で選択していたり、セルに名前を付けて範囲選択したりしていないのには理由がありまして、
Sheet2の参照データが毎回変動する（追加ではなく総入れ替え。列は変わらないが行が変動する）ため、名前を付けたり消したりの作業工程を増やしたくないです。関数が壊れるのも困ります。
これが数十人かつ6ヶ月分の集計を行うので、再計算をかけると数分時間がかかります。
なにか代替案や上手なやり方はありませんでしょうか。

＜使用 Excel：Microsoft365、使用 OS：Windows10 ＞

 >分かりにくいですが、

認識されているのであれば、もう少し情報を載せてほしいです。

 1 シート（またはブック）構成
 2 表のレイアウト（関数を入れる場所を含む）
 3 表内データと結果図のサンプル

など
（フォーキー） 2023/01/23(月) 17:22:24

[sheet1]
　
　　　　　　10月　　11月　　12月
佐藤太郎　　36.6　　31.6　　　20←
鈴木次郎　　20　　　11　　　　35←質問部分です
　　↑UNIQUE関数でsheet2氏名をスピルさせています

[sheet2]
　氏名　　　年月　　時間A　　時間B
佐藤太郎　　202210　1200　　1000
佐藤太郎　　202211　800　　 1100
鈴木次郎　　202210　1050　　1300

[該当部分を書き換えました]
=@IFERROR((FILTER(sheet2!時間A,(sht1名前=sheet2氏名)*(sheet2!年月=TEXT(K$7,"yyyymm")))+FILTER(sheet2!時間B,(sht1名前=sheet2氏名)*(sheet2年月=TEXT(K$7,"yyyymm"))))/60,"")

[フロー](質問部以外にも関数を使う部分があるが、関係しないので割愛します）
・社内データをシート2へ張り付けて更新する
・ユニーク関数にて氏名をスピルする
・スピルされた氏名に該当し、かつ10月である時間Ａをセルへ出力する
（以下繰り返しです）

（tama） 2023/01/23(月) 18:32:31

 ＞なにか代替案や上手なやり方はありませんでしょうか。

 中身はよく分かってないですが、重いなら列全体の参照は止めた方がいいと思います。

（半平太） 2023/01/23(月) 21:48:31

列全体参照がよくないだろうとは私も思いまして、いくつか代替案を考えたりもしました。
「VBAを使ってオートで範囲に名前をつけ、その範囲を指定する」
使用したいデータを一度全て削除してから再び社内データをペーストしたい都合上、
都度範囲の名前情報が参照不可になってしまい、だめでした。

書いていて今思いついたのですが、
?@VBAで名前の定義を削除する
?A質問の関数を空白にしておく
?B新しくペーストしたデータの列にVBAで名前の定義をする
?CVBAで関数をセルにいれる
このループであれば、VBAでは関数は文字列であるため参照不可で関数が壊れることもなく運用できるような気がしました。
いったん実践してみます。
他にもなにかあればご意見いただきたいです。
（tama） 2023/01/24(火) 09:53:05

 参照する範囲をテーブルにしちゃえばいいんでは？
（´・ω・`） 2023/01/24(火) 10:06:21

テーブルにしたときに、中身を全て入れ替えてデータの増減をすることはできますか？
例）
現在A1からA75でデータが入っている。
そのデータを一度オールクリアし、
今度はA1からA100までデータをペーストする。

以上作業を行った場合、A1～A75に設定していたテーブルはA1～A100になることはないですよね？
（tama） 2023/01/24(火) 10:21:27

 テーブルを全部消したらそうりゃそうでしょう

 テーブルの見出し（最初の１行目）を残すようにすれば
 テーブルの範囲はExcelが自動で再設定してくれます

 名前ボックスでテーブル名を選択すると、
 テーブルのデータ領域が全選択されるので、その状態で削除
（´・ω・`） 2023/01/24(火) 10:36:49

テーブル名[列名]
で列の指定ができることを知らず、
テーブル指定をしても1列指定をするときはセルに名前をつける必要があると勘違いしておりました。
そのせいでテーブル化することをあきらめていましたが、ご指摘があり最初から調べなおした結果、
テーブル化をしておくことにより列指定の行可変に成功し、嘘みたいに軽くなりました。
本件解決いたしました。
ご意見くださった方々、ありがとうございました。
また、初歩的な勉強不足で申し訳ありませんでした。
（tama） 2023/01/24(火) 15:17:44

コメント返信：

[ 一覧(最新更新順) ]

[[20230123171258]] 『filter関数の処理を軽くしたい』（tama） ページの最後に飛ぶ

[[20230123171258]]　『filter関数の処理を軽くしたい』（tama）　ページの最後に飛ぶ