関数の入れすぎで重い

連投失礼します。
エクセルでカレンダー兼タスクリストを作成しています。
６月～１０月までの期間を横長に作成していて、画像上部のように左側にタスク番号、
右側はプルダウンで済んだら×をつけれるようになっています。


そして別のシート（画像下部）でタスク一覧表を作成していて、
カレンダーシートで×をつけたらこちらの表の対応する番号のところに×がつくようになっています。
式は下記の通りです。
=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)

ここまで作ったのですが関数が重すぎてどこかを触るたびに固まってしまいます。
度々触るので自動計算をやめて手動計算にするなども難しいです。

ＶＢＡを組むと軽くなるという情報を見つけたのですが、
ＶＢＡはほとんど触ったことがないため自力で組むのが難しく困っています。
何か良い方法がありましたら教えてください。

質問者からの補足コメント

• コピーしてみたのですがうまくいかないようです…(涙)
  
  度々すみません、画像が見えないですね…
  実際の画面はこんな感じになっています。
  
  例えばsheet1の101の右横に×が入ったら（例えばsheet1のカレンダーのどこかにあらかじめ101が入っていて、そこに×が入ったら）Sheet2の、101が入っているセルの下であるB5:C8の結合セルに表示するといった感じのことをしたかったのです。
  表は右に6個~7個並んでいて、縦には1000行以上あります。
  またジャンルごとに番号をわけていて、画像でいうB4：M28までが一つのかたまりなのですが、
  一番先頭の1~3行に空白があるように、たまにジャンル区切りとして空白行をもうけています。
  
  （ところどころsheet2のタスク番号の横に「空」と入っていますが、必要データです）
  
  説明不足のせいでなにか問題があったらすみません・・・！

  
  No.5の回答に寄せられた補足コメントです。 補足日時：2020/06/21 21:08

No.8 ベストアンサー 回答者： tom04 回答日時： 2020/06/22 14:13

再度参上です。

＞入力間違いなどで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

この回答へのお礼

できました！
何度も質問してすみません。ありがとうございました！

お礼日時：2020/06/23 19:12

No.7 回答者： tom04 回答日時： 2020/06/21 22:46

続けてお邪魔します。

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

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

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

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

この回答へのお礼

すみません！！Sheet1とSheet2のタスク番号のセルにユーザー定義の表示形式で”号”という単位がつけてあったのを失念していました。
それを解除すると表示されるようになりました。大変お騒がせいたしました。。。

何から何まで聞いて申し訳ないのですが、最後に一点だけ教えていただけると・・・
Sheet1のタスク番号の横に×を入れるとSheet2のタスク番号の下に×が表示されるようになったのですが、
入力間違いなどでSheet1のタスク番号、もしくは×を消去した場合にSheet2のタスク番号の下のセルの×が消えないみたいなのですが、
これを解消する方法がもしありましたらご教授いただけませんでしょうか・・・

お礼日時：2020/06/22 00:25

No.6 回答者： tom04 回答日時： 2020/06/21 21:44

No.2・5です。

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

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

この回答へのお礼

tom04さんの画像のような感じをイメージしていました！希望しているのはそれなのですが、なんでか表示されないです…

１つ気づいたのですが、Sheet1の奇数列はプルダウンで「　」（スペース入り空白）か「×」かを選択しているので、
If Target.Value <> "" Then を　
If Target.Value <> "" And Target.Value <> "　" Then　
にしたら良いのかなと思ったのですが…（見当違いのことを書いていたらすみません。）それでも表示されませんでした。

もう少し自分の作ったものにおかしいところがないか探してみます。度々ありがとうございますm(_ _)m

お礼日時：2020/06/21 22:18

No.5 回答者： tom04 回答日時： 2020/06/21 19:06

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

No.4 回答者： mike_g 回答日時： 2020/06/20 10:38

[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)で試したら一瞬の(アッと云ふ)間の所要時間でしたヨ。御參考までに。

No.3 回答者： mike_g 回答日時： 2020/06/20 08:16

》 関数の入れすぎで重い

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

No.2 回答者： tom04 回答日時： 2020/06/20 02:50

こんばんは！

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

この回答へのお礼

詳細なコードまでありがとうございます。

こちらの説明不足だったのですが、実際に表示させたかった場所が、タスク番号の横ではなくて下の結合してあるセルでして・・・すみませんでした。

とりあえずC列に出るようにと思い、コードをそのままコピーしてみたのですが、何も表示されませんでした。。。いろいろ調べてみたのですがよくわからず・・・(涙)

お礼日時：2020/06/21 18:33

No.1 回答者： nekoronda 回答日時： 2020/06/20 00:46

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

もう少し丁寧に書かせてもらいますが、
以下の条件付き書式を設定すれば良いのではないでしょうか？

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

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

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

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