[[20231024181100]] 『マトリクス表における平均値の出し方』(太郎くん) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『マトリクス表における平均値の出し方』(太郎くん)

https://www.excel.studio-kazu.jp/kw/20231020125859.html
こちらの続きで恐縮です。

以下のようなマトリクス表において、指定した列と行のみ平均値を求めたいです。
以下イメージ図におけるシート1A列の地域コードをキーに、
シート2に地域ごとの売上平均を出したいのですが、
更にそこに曜日での条件(平日or土日)を加えた場合、
どのような関数で計算可能かご教示いただけますと幸いです。

※シート1は10月のみのデータとなっています(11月以降はない)

■イメージ(シート1:売上データ)
    A    B    C    D    E    F  ・・・
1 地域コード 店舗ID 店舗名 10月1日 10月2日 10月3日
2                日    月   火
3   AAA   XXX  ●●●  100   120   130
4   AAA   YYY  ●●●  300   420   530
5   BBB   ZZZ  ●●●  600   620   630

■イメージ(シート2:サマリデータ)
    A    B    C        D  
1 地域コード 地域名 10月平日売上平均 10月休日売上平均
2   AAA   東京  ※ここを求めたい
3   BBB   大阪  
4   CCC   愛知 
 

< 使用 Excel:Microsoft365、使用 OS:Windows11 >


 祝日は関係ないんですよね?

 平日
 C2 =AVERAGE(FILTER(FILTER(Sheet1!$D$3:$AH$150,Sheet1!$A$3:$A$150=A2),WEEKDAY(0&Sheet1!$D$1:$AH$1,2)<6))

 休日
 D2 =AVERAGE(FILTER(FILTER(Sheet1!$D$3:$AH$150,Sheet1!$A$3:$A$150=A2),WEEKDAY(0&Sheet1!$D$1:$AH$1,2)>5))

 ですかね?
(笑) 2023/10/24(火) 19:47:23

いつもありがとうございます!
シート1の2行目の曜日判別を仮に文字列で行うとすると、どのようになりますでしょうか?
ご指摘のとおり祝日は手打ちしてまして、それも含め判別するとなるとどのような関数になるかご教示いただけますと幸いです。
(太郎くん) 2023/10/24(火) 19:59:25

 >祝日は手打ちしてまして
 どこに何を手打ちしてるんですか?

 以上、確認だけ
(笑) 2023/10/24(火) 20:13:18

シート1 の二行目です!
曜日は全て手打ちしています。
恐れ入りますが、その場合の関数もご教示いただけますと幸いです。
(太郎くん) 2023/10/24(火) 20:15:32

 曜日(文字列)の出し方がわからないってこと?

 D2 =TEXT(D1:AH1,"aaa;;")

 式をコピーする必要はありません。

 で、祝日は関係ないんですか?
 >祝日は手打ちしてまして
 祝日の場合、どこに何を手打ちしてるんですか、と聞いてるんですけど?

 以上
(笑) 2023/10/24(火) 20:27:08

説明がわかりづらく、大変申し訳ございません。
シート1の2行目ですが、曜日は D2=TEXT(D1,"aaa") という数式で出しているのですが、
祝日に該当する部分だけは、文字入力で直接"祝日"と入力しています。
(毎月カレンダーを確認し、該当日を"祝日"と入力しています)
非効率なのは重々承知しておりますが、この場合以下C2とD2の求め方をご教示いただきたいです。

■イメージ(シート2:サマリデータ)
    A    B    C        D  
1 地域コード 地域名 10月平日売上平均 10月休日売上平均(祝日含む)
2   AAA   東京  ※ここを求めたい
3   BBB   大阪  
4   CCC   愛知 
(太郎くん) 2023/10/24(火) 20:42:28


 >該当日を"祝日"と入力しています
 「祝」と1文字だけ入力してください。

 C2 =AVERAGE(FILTER(FILTER(Sheet1!$D$3:$AH$150,Sheet1!$A$3:$A$150=A2),ISERR(FIND(Sheet1!$D$2:$AH$2,"土日祝"))))

 D2 =AVERAGE(FILTER(FILTER(Sheet1!$D$3:$AH$150,Sheet1!$A$3:$A$150=A2),ISNUMBER(FIND(Sheet1!$D$2:$AH$2,"土日祝"))))

 ■ついでに・・・
 どこでもいいですけど、祝日の日付を入力
 例えば Sheet1のAS列に 2023/10/9 などを入力

 現在、D2:AH2に入れている数式を消去した上で
 D2 =IF(D1:AH1="","",IF(COUNTIF($AS:$AS,D1:AH1),"祝",TEXT(D1:AH1,"aaa")))

 式をコピーする必要はありません。

 以上
(笑) 2023/10/24(火) 21:09:21

無事に求めたい平均値が出ました!
ありがとうございます!
今回も大変勉強になりました。お礼申し上げます。
(太郎くん) 2023/10/24(火) 21:26:44

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.