セル書式の"aaa"は使用上の注意をよくお読みください

問題１：時系列データから土日を除外して平均を出したい

まず、このスプレッドシートの平均Aと平均Bを見て欲しい。同じ数字が表示されている。しかし、平均Aは土日を除外したセルが対象で、平均Bはすべてのセルが対象となっている。

フォーミュラは以下のとおり。なぜAVERAGEIFSは期待通りの結果を返さないのか分かりますか？　ちょっと考えてみてください。

平均A: =AVERAGEIFS(C13:C999, $A13:$A999, "<>土", $A13:$A999, "<>日")

平均B: =AVERAGE(C13:C999)

回答

Ａ列の曜日は、実はＢ列を参照しており、セル書式で "aaa" としているので 表示フォーマットは曜日だが、あくまでも値はシリアル値となっている。AVERAGEIFSの条件式は "<>土"となっているので問題なさそうだが、あくまでも値を見に行くのでヒットするはずがない。よって、両者ともに同じ数字を返してしまう。

解決方法：

 A列を泣く泣く月～日の文字にするか、CHOOSE関数を使ってセルの値を文字にするしかない。なぜなら、AFERAGEIFの条件式には条件付き書式のような形式では記述できないからです。

=CHOOSE(WEEKDAY(B13), "日", "月", "火", "水", "木","金", "土")

問題２： 条件付き書式で土日を赤く表示

ルールの種類を「数式を使用して、書式設定するセルを決定」にして、指定日付セルが 土日の場合は赤くしたい場合もセル書式の "aaa"は注意が必要です。

単純に次のように書いてしまうと、赤く表示されません。もちろん、①文字で月～日、または②CHOOSE関数で曜日列を設定すればこの書き方で問題ありません。

=OR(A18="土", A18 = "日")

じゃあ、どうすればいいかというと・・・TEXT関数をかませてやればいいわけです。

=OR(TEXT(A18, "aaa")="土", TEXT(A18, "aaa") = "日")

応用編 

週末だけではなく国民祝日も赤くしたい場合はどうすればいいでしょうか？ 簡単な方法はVBAで関数を書いて、その中で国民祝日もチェックすればいいでしょう。

=IsHoliday(A18)

当然ですが、国民祝日を管理するシートも必要になります。 

Public Function IsHoliday(ParamArray pParameter() As Variant) As Boolean

    Dim iDay    As Long
    Dim sDate   As String
 
    IsHoliday = True
    sDate = pParameter(0)
    iDay = Weekday(sDate)
    If iDay = vbSunday Or iDay = vbSaturday Then Exit Function

    Dim vMatch As Variant

    On Error GoTo Unmatch
    vMatch = Application.WorksheetFunction.Match(CLng(CDate(sDate)), wksCalendar.Range("K:K"), 0)
    IsHoliday = (vMatch > 0)
    Exit Function

Unmatch:
    IsHoliday = False

End Function

余談：
サンプルに使ったシートは、実はあるパチンコホールでの時間帯別大当たり回数を集計したものです。14:30から16:00までの時間帯が一番出しており、夜の部はそれほどでもないということが分かります。条件書式で平均の-10%、-5%、+5%、+10%で背景色を設定すればどの曜日で回収しているのか、７が付く日は出ているのかどうなのか、新台導入した週はどうなっているのか、なんてことが分かります。