[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『TRUEになるはずの数式を入れた条件付き書式が機能しない』（山田太郎）
初めて質問させていただきます。
日付が入力されているセルの１つ下のセルの条件付き書式に以下の数式を入れ、土曜日・日曜日以外の日付の１つ下のセルに網掛けをつけたいのですが、数式がTRUEになっているはずのセルにも網掛けがかかりません。
条件付き書式に使っている数式は、そのままセルに入れると「TRUE」と表示され正しく機能しています。
また、数式にAND関数を使っていますが、AND関数中の引数２つをそれぞれ単体で条件付き書式に入れると正しく機能します。
このような現象が起こる原因と、「土曜日・日曜日以外の日付の１つ下のセルに網掛けをつける」という目的の達成方法をご教授いただけますでしょうか？
実際のシートは１２か月分のカレンダーになっており、条件付き書式を設定する範囲がとびとびなためvbaを使いこのような数式での設定になっています。
なお、当方の環境では、新しくブックを作り適当なセルに土曜日の日付を入力、２つ下のセルの条件付き書式に以下の数式を入れた場合でも網掛けはかからず、何度でも再現可能です。
以下、数式になります。
=AND(WEEKDAY(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0))<>1,WEEKDAY(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0))<>7)
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
「土曜日・日曜日以外の日付の１つ下のセルに網掛けをつける」方法について
式の設定のところだけ書きます。
Dim rng As Range
Dim adrs As String
Set rng = Selection 'ここは条件付き書式の設定範囲の積もりです。
adrs = rng(1).Offset(-1).Address(False, False)
rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(WEEKDAY(" & adrs & ")<>1,WEEKDAY(" & adrs & ")<>7)"
でどうですか？
# 上手くいかない理由は、私の今日の学習内容に関連していると思いますが、
# どなたかに回答をお願いします。
（γ） 2020/10/19(月) 23:04
＞このような現象が起こる原因
経験則ですが、OFFSET関数とROW()などの「配列型」は非常に相性が悪いです。 何故かは不明。
※ROW()は１つでも配列型です。
なので、ROW()関数をスカラー型にすると治ります。 下案では、Max関数でラップしてスカラー型に変換します。
＞「土曜日・日曜日以外の日付の１つ下のセルに網掛けをつける」という目的の達成方法
ご提示の数式を生かすのであれば、 =AND(WEEKDAY(OFFSET(INDIRECT(ADDRESS(MAX(ROW()),MAX(COLUMN()))),-1,0))<>1,WEEKDAY(OFFSET(INDIRECT(ADDRESS(MAX(ROW()),MAX(COLUMN()))),-1,0))<>7)
ただ、少し冗長な気もします。これでもワークします。(月曜スタートに換算する） ↓ =WEEKDAY(OFFSET(INDIRECT(ADDRESS(MAX(ROW()),MAX(COLUMN()))),-1,0)+6)<6
（半平太） 2020/10/19(月) 23:19
半平太さん、「ROW()は１つでも配列型」という注釈を読んでようやく理解ができました。
また、「月曜スタートに換算する」という考え方は非常に美しいですね。
私はまだまだだと思い知りました。
今回はすでにあるコードを生かすためMAX()のほうを採用させていただきました。
やりたかったことができました。ありがとうございます。
（山田太郎） 2020/10/19(月) 23:40
解決後ですが、ちょっと気になっていることがあるので入れて置きます。
今日、別のトピックで、 ＞『条件付き書式関数』（MINARU） [[20201019104932]]
条件付き書式の数式は配列数式として評価される(ようだ） と言う展開になりました。・・が・・
本件もメカニズム的に何らかの関係は、多分あるのでしょうが、 実際にやってみると、AND関数があると＃VALUE!エラーですが、ANDを使わずに()*() の形にすると、 CSE入力しても正常値が返ります。(但し、F9キー押下で見ると｛#VALUE!｝となっています）
なので、条件付き書式で使うときだけ、旨く行かないと言うことになり、 上述の解釈だけでは説明が付かないと思いました。m(__)m
（半平太） 2020/10/20(火) 00:00
よくわかりませんけど ↓ でできませんか？
=WEEKDAY(INDIRECT("R[-1]C",FALSE),2)<6
祝日は関係ないんですよね？
参考まで （笑） 2020/10/20(火) 00:03
同じことを言っているようですね（8:33)
（γ） 2020/10/20(火) 08:28)
＞これも条件付き書式は、自然に配列コンテキストで処理をしてしまう ＞ということの影響かと思いました。 ＞同じことを言っているようですね（8:33)
成程です。「配列コンテキストで処理」と言うボキャブラリーは浮かばなかったです。
結局、条件付き書式の数式解釈エンジン(？）は、 「配列数式扱い」±アルファと理解して置くことにします。
（半平太） 2020/10/20(火) 09:51
どなたか教えてほしいのですが １つ上のセルを指定するのに ↓ とされていますが >OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)
>OFFSET(自セルのアドレス文字（相対参照）,-1,0) で良さげに思えるんですが INDIRECTを使うのは何故でしょうか？
_____ _________ _____________________________________________________
|_____|____A____|__________________________B__________________________|
|___1_| 10/20
|___2_| 10/20 =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)
|___3_|
|___4_| 10/20
|___5_| 10/20 =OFFSET(A5,-1,0)
|___6_|
|___7_| 10/20
|___8_| TRUE =WEEKDAY(INDIRECT("R[-1]C",FALSE),2)<6
|___9_|
|__10_| 10/20
|__11_| TRUE =WEEKDAY(OFFSET(A11,-1,0),2)<6
_____ _________ _____________________________________________________
|_____|____1____|__________________________2__________________________|
|__10_| 10/20
|__11_| TRUE =WEEKDAY(OFFSET(RC,-1,0),2)<6
（チオチモリン） 2020/10/20(火) 10:01
興味本位でテストしただけですが =WEEKDAY(OFFSET(INDIRECT(ADDRESS(MAX(ROW()),MAX(COLUMN()))),-1,0)+6)<6 と =WEEKDAY(OFFSET(A1,-1,0),2)<6 で 同じセルに色がつくことを確認しました。確かによさげですね。 （りぃ） 2020/10/20(火) 11:11
チオチモリンさんの指摘はごもっともですね。
OFFSETだけでINDIRECTは不要でしょうね。
以下、脇道です。
条件付き書式を使うとき、こんな風に考えることが多いです。
1.対象セル範囲の最初のセルに対することだけ考えます。
例えば、A2:P2だったとすると、A2に対する式だけを考えます。
2.それを式として指定すれば、他の対象セルには自動的に増幅されて反映されます。
3.その増幅のしかたは、通常のセルに対するものと同じです。
必要であれば、相対参照、絶対参照を使い分けします。
私が提示したマクロは、そういう考え方をとっていますので、
ひとつ上のセルを予め決めて、それを直接、指定しています。
Offsetすら陽に式中には入れていません。
もちろん、対象セルとの関係を(明示的に)Offsetで示した方が、可読性は高い、
というのも有力な考え方かと思います。
（γ） 2020/10/20(火) 11:56
＞>OFFSET(自セルのアドレス文字（相対参照）,-1,0) ＞で良さげに思えるんですが
↓ だからじゃないんですか？ ＞条件付き書式を設定する範囲がとびとびなためvbaを使いこのような数式での設定になっています。
範囲が1つで固定なら、A2セルから条件付き書式を設定する場合
=WEEKDAY(A1,2)<6
以上 （笑） 2020/10/20(火) 12:47
>条件付き書式を設定する範囲がとびとびなためvbaを使い... どのセルでも「同じ数式」という意味でしょうか？ それなら、一時的にR1C1形式にして ↓ なのでもいいかもですね。
Dim とびとびのセル As Range
Set とびとびのセル = ActiveWindow.RangeSelection 'TEST用
Cells.FormatConditions.Delete
Application.ReferenceStyle = xlR1C1
とびとびのセル.FormatConditions.Add Type:=xlExpression, Formula1:="=WEEKDAY(OFFSET(RC,-1,0),2)<6"
Application.ReferenceStyle = xlA1
（チオチモリン） 2020/10/20(火) 16:43
また、多くの方から「INDIRECT(ADDRESS(ROW(),COLUMN()))」と「OFFSET()」ではなく相対参照のアドレスで十分では、といったご指摘をいただいていますが、それに関しては 笑 さん、チオチモリンさんのおっしゃる通りの理由です。
VBAでアドレスを直接セルに入力する場合、セル番地の表示形式をR1C1かA1か、利用する方に一度直してやる必要があるのが面倒で、「INDIRECT(ADDRESS(ROW(),COLUMN()))」を知ってからはいつもこちらを使っていました。
少しの手間を惜しんでおかしなことはせず、素直に相対参照を利用しようと思います。
（山田太郎） 2020/10/20(火) 16:56
＞途中でエラーになってしまっている数式を使っていたと思うと恐ろしいです。
いや「最終的に」セルにどう表示されたか、条件付き書式で何と判定されたかが重要です。
この種のツール(※）の表示メカニズムは仕様が明らかにされておらず、 全幅の信頼がおけるものじゃないです。
「おもちゃ」とは言いませんが、あくまで参考利用です。(まぁまぁ便利ですけどね）
(※) 数式の検証、数式ボックスを含む
（半平太） 2020/10/20(火) 17:39
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.