Excel2010での平日のある文字の合計について

質問

Excel初心者です。何回かこちらにお世話になっておりますがまた、助けてほしい事があります。写真に映っていますAさんという方の「B」という文字を下のB勤平日合計欄(オレンジ色の部分に数字で表したいのですがどのようにしたらいいのでしょうか？平日だけだしたいので土曜、日曜日を除く関数はどのようにするのでしょうか？

日付の一日がA3=B1の日付に繋がっており、表示形式はdにしています。下に+1で連続しています。土曜日の表示はB3=A3です。下に同様にB4の場合は=A4になっています。表示形式がaaaで表示させております。WEEKDAY形式を使うのでしょうか？

どなたか分かる方よろしくお願い致します。

またこの様な細かい事まで書いてあります参考書、またはオススメな本などありましたら（自分で探したのですが求めている内容の物が無く）お願い致します。

回答No.4

bunjii · ベストアンサー

>写真に映っていますAさんという方の「B」という文字を下のB勤平日合計欄(オレンジ色の部分に数字で表したいのですがどのようにしたらいいのでしょうか？
「B勤平日合計欄」へは日数をカウントすれば良いでしょうか？

=SUMPRODUCT((WEEKDAY(B3:B6,2)<6)*1,(C3:C6="B")*1)
　　　　省略　↓
=SUMPRODUCT((WEEKDAY(B3:B6,2)<6)*(C3:C6="B"))

説明ではB列に日付のシリアル値があるとのことなので曜日の判定をWEEKDAY関数を使います。
尚、SUMPRODUCT関数を使うと複数の条件に合った値を集計できます。
B3:B6は必要に応じて範囲を広げてください。

お礼

皆様からの回答ホントにありがとうございます！参考にさせていただきます。bunjiiさんの回答が求めていた内容でしたのでベストアンサーに選ばせていただきます。bunjiiさんに度々ご質問なのですが=SUMPRODUCT((WEEKDAY(B3:B6,2)<6)*(C3:C6="B"))の部分の2)<6)が平日のWEEKDAY関数なのでしょうか？例えばそれぞれ月、水、金や土曜日、日曜日だけを求めたい場合この部分をどのように変更すればよろしいのでしょうか？

お忙しいところ申し訳ございませんがよろしくお願い致します。

回答No.8

bunjii

>土曜日の求め方に関しましては=SUMPRODUCT((WEEKDAY(B3:B6,2)>5)*1*(C3:C6="B"))の入力で正しいのでしょうか？
IF関数の論理式をWEEKEND(B3:B6,1)>6に変更してください。
WEEKDAY(日付,1)　→　日～土を1～7
また、SUMPRODUCT関数の配列を省力型にするときは *1 も省略してください。

>入力してもエラーになってしまい最後の入力するところが間違っているのでしょうか？
データの配列が合った入ればエラーになりませんが正しい値を得られないでしょう。

お礼

回答ありがとうございます。土曜日の求め方出来ました！
これらを応用して自分なりに勉強してみます。
また分からないことありましたらよろしくお願いします。
この度はありがとうございました。

回答No.7

bunjii

>bunjiiさんに度々ご質問なのですが=SUMPRODUCT((WEEKDAY(B3:B6,2)<6)*(C3:C6="B"))の部分の2)<6)が平日のWEEKDAY関数なのでしょうか？
WEEKDAY関数の引数は次のように指定します。
第1引数は日付のシリアル値、第2引数は戻り値の種類を指定します。
1は日～土を1～7で戻ります。
2は月～日を1～7で戻ります。
3は月～日を0～6で戻ります。
従って、（WEEKDAY（B3:B6,2)<6）*1はB3～B6に月～金が何日含まれるかをカウントできます。

>例えばそれぞれ月、水、金や土曜日、日曜日だけを求めたい場合この部分をどのように変更すればよろしいのでしょうか？
月、水、金の場合はチェックする条件が連続していないので個別に扱うことになり次のようになります。

(WEEKDAY(B3:B6,2)=1)+(WEEKDAY(B3:B6,2)=3)+(WEEKDAY(B3:B6,2)=5)

土、日の場合は連続しているので次のようになります。

（WEEKDAY（B3:B6,2)>5）*1

配列の値をどのように評価するかは応用力によって方法が異なります。
論理式のANDは乗算で代替し、ORは加算で代替します。

お礼

回答ありがとうございます。ご連絡遅くなりました。月、水、金の場合は=SUMPRODUCT(WEEKDAY(B3:B6,2)=1)+(WEEKDAY(B3:B6,2)=3)+(WEEKDAY(B3:B6,2)=5)*(C3:C6="B"))

土曜日の求め方に関しましては=SUMPRODUCT((WEEKDAY(B3:B6,2)>5)*1*(C3:C6="B"))の入力で正しいのでしょうか？入力してもエラーになってしまい最後の入力するところが間違っているのでしょうか？

回答お願い致します。

回答No.6

MarcoRossiItaly

No.1 です。日付の課題では、間違えやすい注意点があるので、補足します。

COUNTIFS 関数「のみ」で、質問文に示されている列の他には作業列を用意せずにいきなり計算しようとすると、失敗するはずです。つまり、少なくともイキナリでは、「"<>土"」などの条件により計算することは、できません。

なぜならご質問のシートの場合、B 列の各セルの値は文字列ではなく、日付のシリアル値という数値になっています。セルの書式により、見かけだけ、その代理となる文字列を表示しているに過ぎない状況だからです。ワークシート関数でセルの書式を取り出すことは、基本的にはできません。前述のような指定の仕方をしてもエラーは出ませんが、正しい答えも出ません。SUMIFS 関数であっても、同様です。

既出のご回答のうち、作業列を他に用意している案では、SUMIFS や COUNTIFS を使って計算できると思います。用意しないのであれば、SUMPRODUCT になってしまいます。

なお SUMIFS と COUNTIFS はどちらも基礎的な関数ですが、両者の違いを改めて確認してみてください。どこかの列を合計していくなら SUMIFS、皆さんのおっしゃるとおり行の個数を数えるというお話ならば COUNTIFS を使うことになります。

どちらも、使用すべき場面が非常に多い関数だと思います。しかし実際には、中級くらいのユーザーでも、うまく使えていない人が少なくないようです。もちろん、単一条件の SUMIF や COUNTIF でも同じことが言えます。本当はちっとも難しくないのですが、日頃から意識しておかないと、何だか出番に気付きにくいのかもしれませんね。

No.1 の SUMPRODUCT では合計を求めましたが、行の個数を求めることもできます。No.1 に示した各数式から「,D3:D33」という部分を削除するだけで、個数になります。

回答No.5

esupuresso

excelのバージョンが2010ですので折角ですから「COUNTIFS」という関数を使いましょう。
条件は、土日を除いた平日の「B」の個数合計。

セルC8には次の式で希望の合計個数が返ってきます。
セルC8　=COUNTIFS(B3:B6,"<>日",B3:B6,"<>土",C3:C6,"=B")　とします。

もし、「Aさん」だけではなく、「Bさん」とか「Cさん」等、隣の列にある場合は、セルC8に入る式の参照形式を若干変更して、=COUNTIFS($B3:$B6,"<>日",$B3:$B6,"<>土",C$3:C$6,"=B") として、右方向へ数式をコピーします。

＞細かい事まで書いてあります参考書、またはオススメな本などありましたら・・・
http://www.amazon.co.jp/Excel-2010-%E5%AE%8C%E5%85%A8%E5%88%B6%E8%A6%87%E3%83%91%E3%83%BC%E3%83%95%E3%82%A7%E3%82%AF%E3%83%88-%E9%88%B4%E6%9C%A8-%E5%85%89%E5%8B%87/dp/4798121584
　↑　この中の『逆引き・・・」位かなぁ・・・？　（あえて言うのなら・・・。）

回答No.3

gyouda1114

作業列を使う方法です。
画像で
作業列D3に
=IF(WEEKDAY(B3,2)<6,C3,"")
下方にオートフィル
C8に　=COUNTIF(D3:D6,"B")
WEEKDAY関数の使い方
http://excel.onushi.com/function/weekday.htm

回答No.2

keithin

まず、Ａ３の数式を
=IF(MONTH($B$1+ROW(A1)-1)=MONTH($B$1),$B$1+ROW(A1)-1,"")
に変えて、下向けにコピーします
セルの書式設定はdのままでいいです

Ｂ３の数式を
=TEXT(A3,"aaa;;;")
に改めて以下コピーします
セルの書式設定は標準に変えておきます

Ｃ８の数式を
=COUNTIFS(C3:C6,"B",B3:B6,"<>土",B3:B6,"<>日")
とします。

＞この様な細かい事まで書いてあります参考書

上述のように、ご相談の事は「参考書に書いてあるふつーの関数」だけを使って、十分に作成できます。
ただ、参考書に書いてなくて今必要なのは、ふつーの関数を組み合わせて「応用する方法」です。
関数の組み合わせはそれこそ無数に考えられますので、それを全部を網羅してたら、やっぱり百科事典並みにきっとなっちゃいますね。まぁでも一応限定的には、「逆引き」タイプの参考書が、それに近いモノと言えるかもしれません。

でもそんな無駄なことをしなくても、今はネットで幾らでも調べモノができる時代になりました。何を使ってどう応用したらいいのか、知ってる人にちゃちゃっと聞いちゃった方が、遥かに簡単です。

＃もっとも、それはそれで「聞き方」に注意しないといけないって別の問題はあるんですけどね。

回答No.1

MarcoRossiItaly

「『B』という文字を……に数字で表したい」とは、「A 列が平日かつ C 列に「B」という文字列が記載されている行のみ D 列の値を合計し、……に表示する」という意味ですか？

そうであるとすれば、次式。どうしても 1 本の数式のみで求めようとするならば、ですが。

G1　=sumproduct((weekday(a3:a33)<>1)*(weekday(a3:a33)<>7)*(c3:c33="B"),d3:d33)
　　あるいは
　　=sumproduct((2<=weekday(a3:a33))*(weekday(a3:a33)<=6)*(c3:c33="B"),d3:d33)

＞……この様な細かい事まで書いてあります参考書、またはオススメな……

細かいというか、易しくない内容です。そのため、なかなかピッタリの本はないと思います。とりあえず、ヘルプで SUMPRODUCT 関数と WEEKDAY 関数の仕様などをそれぞれ調べてください。さらにインターネットでの検索でも、両者の使用例を探してみてください。

作業列を使ってもよいなら、SUM 関数ほかの易しい数式のみで答えが出るので、ご質問のケースではもっとお勧めなのですが。