関数の入れすぎで重い

Question

連投失礼します。
エクセルでカレンダー兼タスクリストを作成しています。
６月～１０月までの期間を横長に作成していて、画像上部のように左側にタスク番号、
右側はプルダウンで済んだら×をつけれるようになっています。


そして別のシート（画像下部）でタスク一覧表を作成していて、
カレンダーシートで×をつけたらこちらの表の対応する番号のところに×がつくようになっています。
式は下記の通りです。
=IFERROR(INDIRECT("Sheet1!"&ADDRESS(SUMPRODUCT((Sheet1!$A$1:$KU$103=B4)*ROW($A$1:$A$103)),SUMPRODUCT((Sheet1!$A$1:$KU$100=B4)*COLUMN($A$1:$KU$1))+1))&"","")
※画像は一部で、タスク一覧表はシートの下のほうまでずっと続いています。
　１１００くらいまでです。

また、カレンダーシートで番号が重複しないように、カレンダーシートには条件付き書式を設定して
番号がかぶったらセルに色がつくように設定しています。
式は下記の通りです。
=(MOD(COLUMN(),2)=0)*(COUNTIF($B$5:$KU$103,B5)>1)

ここまで作ったのですが関数が重すぎてどこかを触るたびに固まってしまいます。
度々触るので自動計算をやめて手動計算にするなども難しいです。

ＶＢＡを組むと軽くなるという情報を見つけたのですが、
ＶＢＡはほとんど触ったことがないため自力で組むのが難しく困っています。
何か良い方法がありましたら教えてください。

tom04 · Accepted Answer

再度参上です。

＞入力間違いなどでSheet1のタスク番号、もしくは×を消去した場合にSheet2のタスク番号の下のセルの×が消えないみたいなのですが

前回のコードの２行を消去するだけで可能だと思います。
どの行とどの行を！と言っても判りにくいと思いますので、
もう一度コードを投稿します。

Private Sub Worksheet_Change(ByVal Target As Range) '//この行から//
　Dim c As Range
　Dim wS As Worksheet
　　If Intersect(Target, Range("B5:KU103")) Is Nothing Or Target.Count > 1 Then Exit Sub
　　　With Target
　　　　If .Column Mod 2 = 1 Then
　　　　　Set wS = Worksheets("Sheet2")
　　　　　　Set c = wS.Cells.Find(what:=.Offset(, -1), LookIn:=xlValues, lookat:=xlWhole)
　　　　　　　If Not c Is Nothing Then '//←念のため//
　　　　　　　　c.Offset(1) = .Value
　　　　　　　End If
　　　　End If
　　　End With
 End Sub '//この行まで//

※　Sheet1に入るのは「タスク番号」の右隣りのセルに「×」か「空白？」（データなし）という前提です。
尚、他のデータを入力した場合、そのデータが表示されます。

「×」か「空白？」(データなし）以外も入る可能性があり、
「×」だけに反応したい場合は、IFで分岐する必要が出てきます。m(_ _)m

nekoronda · Answer

先ほどの投稿で書き込ませてもらった者です。
もう少し丁寧に書かせてもらいますが、
以下の条件付き書式を設定すれば良いのではないでしょうか？

①条件付き書式を設定する範囲：タスク番号を入れる列
　書式の条件：一つ右隣の値が×を判定してTrueなら白色にする

②条件付き書式を設定する範囲：×を入れる列
　書式の条件：自身の値が×を判定してTrueなら白色にする

①条件付き書式を設定する範囲：カレンダー全体
　書式の条件：重複なら青色

各セルの条件付き書式に使われる数式が一つなので軽いはずです。

tom04 · Answer

こんばんは！

https://oshiete.goo.ne.jp/qa/11671230.html
　　↑のサイトの関連質問ですね。

上記サイトに
＞尚、「タスク番号」の重複はない！という前提です。
と記載していましたように、「タスク番号」が複数ある場合はお望みの結果にならないはずです。

そしてお示しの数式を拝見すると範囲がかなり広い範囲になっていますね。
SUMPRODUCT関数は配列数式になってしまいますので、
極端に広い範囲を指定するとPCにかなりの負担をかけ、計算速度が極度に落ちます。
質問のタイトルにある「関数の入れすぎで重い」は条件付き書式の方ではなく、
おそらくこのSUMPRODUCT関数が影響していると思います。

そこで「Sheet2」（←実際のシート名は不明ですが）の数式をすべて消去し、VBAで操作してみてはどうでしょうか？

画面左下にあるシート見出しの「Sheet1」上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）Sheet1の操作（「×」を入力）してみてください。
尚、上記サイトのSheet2の配置はA列が「タスク番号」でB列に「×」を表示するようにしていましたが、
数式を拝見するとB列に「タスク番号」があるようなので、C列に「×」が表示されます。

Private Sub Worksheet_Change(ByVal Target As Range) '//この行から//
　Dim c As Range
　Dim wS As Worksheet
　If Intersect(Target, Range("B5:KU103")) Is Nothing Or Target.Count > 1 Then Exit Sub
　　With Target
　　　If .Column Mod 2 = 1 Then
　　　　Set wS = Worksheets("Sheet2") '//←「Sheet2」は実際のシート名に！//
　　　　　If .Value <> "" Then
　　　　　　Set c = wS.Range("B:B").Find(what:=.Offset(, -1), LookIn:=xlValues, lookat:=xlWhole)
　　　　　　　If Not c Is Nothing Then '//←念のため//
　　　　　　　　c.Offset(, 1) = .Value
　　　　　　　End If
　　　　　End If
　　　End If
　　End With
End Sub '//この行まで//

これでSheet2のB列の「タスク番号」の行のC列に「×」が表示されるはずです。

※　ファイル保存時は「マクロ有効ブック」として保存してください。m(_ _)m

mike_g · Answer

》 関数の入れすぎで重い
貴方のPCの性能がイマドキ貧弱過ぎて“軽い”のでは？
御使ひのExcelのバージョン、ＯＳのバージョン、
実装RAM、内蔵HDの各容量を明記される事を御奨めします。

mike_g · Answer

[No.3]でコメントした者です。

[値が重複しているセルへの色つけ]エクセル 値が重複しているセルへの色つけ]
h ttps://oshiete.goo.ne.jp/qa/11716410.html
の[No.3お礼]で、此れ式の事で既に
》 いろいろな要因が重なってしまいかなり重くなってしまったようです
と仰って居ますネ。

「いろいろな要因」てのが不明だけど、私の環境(Win10 Pro、Ver.2004、OSビルド19041.329 / プロセッサ Intel Core 17-6700 CPU 3.40GHz、実装RAM 16.0GB、64ビット / Excel 2019 16.0.12827.20200、32ビット / Cドライブ空き容量 1.7TB)で試したら一瞬の(アッと云ふ)間の所要時間でしたヨ。御參考までに。

tom04 · Answer

No.2です。

＞実際に表示させたかった場所が、タスク番号の横ではなくて下の結合してあるセルでして・・・

というコトですが、おそらくお示しの画像の下側だと思います。
いくら画面を拡大しても詳細が不鮮明なのでお望み通りにならないかもしれませんが、
コードを少し変えてみました。
前回のコードはすべて消去し、↓のコードにしてみてください。

Private Sub Worksheet_Change(ByVal Target As Range) '//この行から//
　Dim c As Range
　Dim wS As Worksheet
　　If Intersect(Target, Range("B5:KU103")) Is Nothing Or Target.Count > 1 Then Exit Sub
　　　With Target
　　　　If .Column Mod 2 = 1 Then
　　　　　Set wS = Worksheets("Sheet2")
　　　　　　If .Value <> "" Then
　　　　　　　Set c = wS.Cells.Find(what:=.Offset(, -1), LookIn:=xlValues, lookat:=xlWhole)   '//★//
　　　　　　　　If Not c Is Nothing Then '//←念のため//
　　　　　　　　　c.Offset(1) = .Value    '//★//
　　　　　　　　End If
　　　　　　End If
　　　　End If
　　　End With
End Sub '//この行まで//

これでSheet1の「タスク番号」の右隣りのセルにデータを入力してみてください。m(_ _)m

tom04 · Answer

No.2・5です。

前回（No.5）のコードをSheet1のシートモジュールにし、
↓の画像のように「×」を入れると、Sheet2（画像では右側）のように結合セルでも表示されました。

こんな感じをご希望だと思って投稿したのですが、
こちらのExcelではちゃんと表示されていると思うのですが・・・m(_ _)m

tom04 · Answer

続けてお邪魔します。

No.5のコードはそのままで大丈夫のはずです。

前回アップした画像のような感じで良いのであれば・・・
原因として考えられるのは
No.5のコードはどこに記載していますか？
Changeイベントなので、「Sheet1」のシートモジュールにしないと全く動きません。

標準モジュールとか別シートのシートモジュールにしていませんか？

ん～～～こちらで思いつくとすればこのくらいですかね。m(_ _)m

関数の入れすぎで重い

先ほどの投稿で書き込ませてもらった者です。

こんばんは！