[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『SUMPRODUCT,COUNTIFとSUBTOTALの組合せ方』（coco）
出勤簿をつけています。 SUMPRODUCTとCOUNTIFを利用したそれぞれの関数にフィルタをかけたく、 SUBTOTALを組合せようとしていますが、うまくいきません。
1年分の出勤簿が縦に長く続いていて、 フィルタを掛けて、各月に消化した数値を表示出来るようにしたいです。 I列には午前休・午後休・全日休のみを入力しています。
現在入力しているものです。あとはフィルタが掛けられれば･･･
=COUNTIF(I5:I551,"午前休")*0.5+COUNTIF(I5:I551,"午後休")*0.5+COUNTIF(I5:I551,"全日休")
もう一つは休日の出張・出社のカウントで、4時間半未満が0.5、以上が1とカウントしています。 J列には休日の出張・出社、H列には勤務時間が入力されています。
=SUMPRODUCT((J5:J550="出張")*(H5:H550>=0.1875)*1)*1+SUMPRODUCT((J5:J550="出張")*(H5:H550<0.1875)*1)*0.5+SUMPRODUCT((J5:J550="出社")*(H5:H550<0.1875)*1)*0.5+SUMPRODUCT((J5:J550="出社")*(H5:H550>=0.1875)*1)*1
自分なりに調べてみましたが、どうしてもエラーになってしまいます。 SUMPRODUCTとCOUNTIFを使うのが初めてなので、遠回りしているかもしれませんが･･･ どなたかご存知でしたら教えてください。
＞フィルタを掛けて、各月に消化した数値を表示出来るようにしたいです。 オートフィルタで絞り込んで 表示されている部分に関してのみ 所定の計算を行い結果を知りたい と言う事ですかね？ その場合は、こちらがご参考になりそうだと思います。 [[20070502135246]]『COUNTIFについて』（kurop）
それとも 休 出 1月 2月 3月 この様に月ごとに集計したい と言う事ですかね？
(HANA)
早速のご回答ありがとうございます。
フィルターで絞り込んで、表示されている部分の合計が知りたい。 という事です、すいません。 集計ではなく、絞り込んだ月を表示させた時に、 その月に、何日休日を消化または取得したかを表示したいのです。
『COUNTIFについて』も拝見しましたが、 自分の関数が、数式を並べすぎていて、 どこに何を入れるべきか、わからないながら 何度か挑戦したがうまくいきません。。。 そこで行き詰っております。。。
こんにちは～♪
HANAさんが、私の回答を紹介されたので。。。
よく解りませんけれど。。
>フィルタを掛けて、各月に消化した数値を表示出来るようにしたいです。 >I列には午前休・午後休・全日休のみを入力しています。
>現在入力しているものです。あとはフィルタが掛けられれば･･･ > =COUNTIF(I5:I551,"午前休")*0.5+COUNTIF(I5:I551,"午後休")*0.5+COUNTIF (I5:I551,"全日休")
↑ こちらの方ですが。。。
こんな事でしょうか。。。？
こんな表の場合です。
┌─┬────┬────┬────┬────┬────┬────┬────┬────┬────┐ │ │ A │ B │ C │ D │ E │ F │ G │ H │ I │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 1│ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 2│ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 3│ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 4│氏名▼ │月▼ │項目3▼ │項目4▼ │項目5▼ │項目6▼ │項目7▼ │項目8▼ │休み▼ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 5│Aさん │ 1│ │ │ │ │ │ │午前休 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 6│Aさん │ 1│ │ │ │ │ │ │午後休 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 7│Bさん │ 2│ │ │ │ │ │ │全休 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 8│Aさん │ 2│ │ │ │ │ │ │午前休 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 9│Cさん │ 2│ │ │ │ │ │ │午前休 │ └─┴────┴────┴────┴────┴────┴────┴────┴────┴────┘
★ COUNTIF(I5:I551,"午前休")*0.5 の場合は
=SUMPRODUCT((SUBTOTAL(3,INDIRECT("A"&ROW(5:600))))*(I5:I600="午前休"))*0.5
と式を I1セル あたりへ入れみてください。
A列の氏名やB列の月で、フィルタを掛けますと "午前休"の時間は計算出来ます。 あとは、同じ様に"午後休"と"全休"をプラスすればいいですね。。。
エラー処理等は、していませんけれど でも、重くなりそうですね。。。
ご参考にどうぞ。。。
。。。Ms.Rin～♪♪
ふたたび～です。。。
↑では、直接計算する式を書きましたが。。
重くなりそうですので、作業列を使う方法を書きます。
┌─┬────┬────┐ │ │ I │ J │ ├─┼────┼────┤ │ 1│ │ │ ├─┼────┼────┤ │ 2│ │ │ ├─┼────┼────┤ │ 3│ │ │ ├─┼────┼────┤ │ 4│休み▼ │作業列▼│ ├─┼────┼────┤ │ 5│午前休 │ 0.5│ ├─┼────┼────┤ │ 6│午後休 │ 0.5│ ├─┼────┼────┤ │ 7│全休 │ 1│ ├─┼────┼────┤ │ 8│午前休 │ 0.5│ ├─┼────┼────┤ │ 9│午前休 │ 0.5│ └─┴────┴────┘
たとえばJ列に作業列を作ります。。。
J5セルへ =IF(I5="","",FIND(LEFT(I5),"午全")*0.5) 下へコピーします。。
J1セルあたりへ =SUBTOTAL(9,J5:J600)
で、午前休 午後休 全休 の合計が求められます。。
作業列を作れば、SUBTOTALをそのまま使えて簡単ですので こちらをお勧めしま～す。。。
。。。。Ms.Rin～♪♪
=SUMPRODUCT((J5:J550="出張")*(H5:H550>=0.1875))+SUMPRODUCT((J5:J550="出張")*(H5:H550<0.1875)*0.5)+SUMPRODUCT((J5:J550="出社")*(H5:H550<0.1875)*0.5)+SUMPRODUCT((J5:J550="出社")*(H5:H550>=0.1875))
=SUMPRODUCT((J5:J550="出張")*((H5:H550>=0.1875)*0.5+0.5))+SUMPRODUCT((J5:J550="出社")*((H5:H550>=0.1875)*0.5+0.5))
=SUMPRODUCT(((J5:J550="出張")+(J5:J550="出社"))*((H5:H550>=0.1875)*0.5+0.5))
>自分なりに調べてみましたが、どうしてもエラーになってしまいます。 どのようなエラーでしょうか?
どのような表でしょうか?、また、どのようなことをしたいのでしょうか? それがわからなければ、式自体が間違っているかどうか判断できません。
>SUMPRODUCTとCOUNTIFを利用したそれぞれの関数にフィルタをかけたく、 >SUBTOTALを組合せようとしていますが、うまくいきません。 SUMPRODUCT関数の式のなかにその条件も組み入れてはいかがですか? 例 =SUMPRODUCT((月の列=目的の月)*((J5:J550="出張")+(J5:J550="出社")))
何をなさりたいのか不明ですので、参考までに
By しげちゃん
ありがとうございますっ！！ 休日消化日数、フィルタで絞ることが出来ました。 今のところサクサク動くので作業列なしでやってますが、 ダメなら作業列作ります！えらい短くなるんですね。
＞しげちゃんさん
説明不足ですいません。 1人1sheet使って、1日1行使った出勤簿を作っています。こんな感じです↓↓
A B C D E F G H I J ・・・ 4 年度 年 月 日 曜日 出勤 退勤 勤務時間 休日消化 休日出勤 : 100 2007 07 9 21 金 9：30 19：00 9：30 101 2007 07 9 22 土 102 2007 07 9 23 日 8：00 19：00 11：00 出張 103 2007 07 9 24 月 14：00 18：00 4：00 社内 104 2007 07 9 25 火 13：30 18：30 5：00 午前休 ： ：
と言う表で、4/1～3/31を縦にずらっと打ち込んでいく予定です。 そこで、Cの月でフィルタを掛けて、1ヶ月にどのくらい休日を消化したか、 代休を取得したかを出せるような表を作っています。
代休取得のカウントの仕方も勤務時間によって1だったり、0.5だったり･･･ 自分でSUMPRODUCTでカウントできる式は作れましたが、フィルタで絞れず、 SUMPRODUCTとSUBTOTALを組み入れ方がわかりません。
･･･こんな説明で伝わりますでしょうか？
>Cの月でフィルタを掛けて、1ヶ月にどのくらい休日を消化したか、 >代休を取得したかを出せるような表を作っています。 「Cの月でフィルタを掛けて」とは、どのような条件でどのようにしたのでしょうか
「1ヶ月にどのくらい休日を消化したか」とは、どのようにすればわかるのでしょうか
H104,I104から考察すると3:00時間?、または、式から考えると半日（4:00時間）?
あなたの会社のことを私は知りません。
憶測で（9月分の休暇取得）
=SUMPRODUCT((C5:C550=9)*((I5:I550={"午前休","午後休","全休"})*{0.5,0.5,1}))
「代休を取得したかを出せるような表」とは、どこを見ればわかりますか?
>代休取得のカウントの仕方も勤務時間によって1だったり、0.5だったり･･･ >自分でSUMPRODUCTでカウントできる式は作れましたが、フィルタで絞れず、 >SUMPRODUCTとSUBTOTALを組み入れ方がわかりません。 何をしたいのか説明不足です
By しげちゃん
少し確認させてください。
ご希望のことは、Ms.Rin～♪♪さんのご説明で求められたのですか？ それとも、未だ解決していないのですか？
また、月ごとに集計を行いたいのではないのですよね？ ●2月分だったり、半年分だったり オートフィルタを用いて、任意で月の期間を絞り込み その情報を表示させ、表示しているデータでの 集計結果が知りたい。 のでしょうか？ それとも ●集計単位は月ごとだが、表示させた月の集計結果を 常に特定のセルに表示させたい と言うことがなさりたいのですかね？
まだ解決して居られないのなら 「絞り込んだデータ」である必要がある理由を教えて下さい。
「月ごとに個人毎の出勤簿として印刷する時に 特定の場所にその月の各計算結果を表示させたい為」 なんて理由なら、分かる気がするのですが。
(HANA)
度々すいません。休日消化は解決しました。 半日を4時間半としています。
代休を取得したかどうかは、休日出勤→代休取得となり、 勤務時間によって、4時間半以上→1日取得、以下→0.5日取得としています。 言い方が悪かったのですが、「表」とは、この出勤簿自体を言っていて、 出勤簿の1つのセルに、休日取得日数を常に表示できれば･･･と思い作ってます。
自分で作った式は、まず休日出勤の所に“出張”もしくは“社内”と入力のある日を探し、 勤務時間を見て、上記の判別で1日、もしくは0.5日、代休取得日数が計算される。というものですが、 これだと月でオートフィルタをかけても、1年分の取得日数しか表示されません。 この式（もしくは全く別の計算式）を使って、SUBTOTAL機能を組み入れて、 月毎で代休取得日数が表示されるようにできれば･･･と思っています。
＞HANAさん
休日消化に関しては、Ms.Rin～♪♪さんのご説明で解決いたしました。
絞り込みに関しては、HANAさんの言うとおり、 １月に１度、個人毎に出勤簿の確認として、 印刷して本人に確認してもらう。と言う作業があり、 その際に、当月の休日取得・消化日数を表示したい為です。
ですので集計単位は半年とかではなく、 オートフィルタで絞り込んだ月の分の集計結果が、 特定のセルに表示されていれば、印刷された出勤簿を見て、 本人も判り易く、計算の手間が省け、間違いも減るだろうと思い、作り始めました。
＞この式（もしくは全く別の計算式）を使って、SUBTOTAL機能を組み入れて、 と言う事は、最初にご提示の式は ●“一年間の物で有れば”正しい結果を求められる と言う事ですよね？
一月ごとにしか絞り込まないのであれば 私は、休日消化に関しても代休取得日数に関しても どこかのセルに月ごとの一覧表として集計しておき VLOOKUP関数等でその値を所定の位置に引っ張ってくる と言う方法をおすすめします。 [A] [B] [C] [D] [E] [F] [1] 月 確認 値 3 8 [2] 1 1 [3] 1 ○ 2 [4] 1 3 [5] 2 ○ 4 [6] 2 5 [7] 2 ○ 6 [8] 3 7 [9] 3 ○ 8 [10] 3 9 [11] [12] 集計 月 合計 [13] 1 2 [14] 2 10 [15] 3 8 月ごとに「○」が付いている値の合計を C13以降に集計します。 C13に =SUMPRODUCT(($A$2:$A$10=B13)*($B$2:$B$10="○")*($C$2:$C$10)) といれて、C15までコピー。
次に、E1に 「オートフィルタで、どの月が表示されているか表示」します。 =SUBTOTAL(4,A2:A10) F1に 「該当月の集計結果を表示」します。 =VLOOKUP(E1,$B$13:$C$15,2,FALSE)
もちろん、作業セル（E1やA12以降)を使わずに、直接 =SUMPRODUCT(($A$2:$A$10=SUBTOTAL(4,A2:A10))*($B$2:$B$10="○")*($C$2:$C$10)) この様にしても良いかもしれませんが 式が長くなると、後で検証が大変なので作業セルを使うのが好みです。 副産物として、年間の状況も確認できますし。
なお、SUBTOTAL関数の集計方法に 「4 , MAX」を使用して居りますので 絞り込みを行わない状態では 一番数の大きい月に関しての 計算結果が表示されます。
(HANA)
>自分で作った式は、まず休日出勤の所に“出張”もしくは“社内”と入力のある日を探し、 >勤務時間を見て、上記の判別で1日、もしくは0.5日、代休取得日数が計算される。というものですが、 >これだと月でオートフィルタをかけても、1年分の取得日数しか表示されません。 >この式（もしくは全く別の計算式）を使って、SUBTOTAL機能を組み入れて、 >月毎で代休取得日数が表示されるようにできれば･･･と思っています。
すでに回答済みですが、フィルタをかけなくても （例として９月分） =SUMPRODUCT((C5:C550=9)*((J5:J550="出張")+(J5:J550="出社"))*((H5:H550>=0.1875)*0.5+0.5))
例として5月から10月分 =SUMPRODUCT((C5:C550>=5)*(C5:C550<=10)*((J5:J550="出張")+(J5:J550="出社"))*((H5:H550>=0.1875)*0.5+0.5))
By しげちゃん
ご丁寧にありがとうございます。 やっぱり作業列作ったほうがよさそうですね。 教えていただいた式を参考に、表の改良も検討してみたいと思います。
＞しげちゃんさん
教えて頂いた式は、直接絞りたい月を式内に入力するって事ですよね？ 毎月、一人ひとり式を書換えていたら大変かなと思ったのですが、 HANAさんに教えて頂いたように月毎の一覧を作れば、この式で活用出来そうですね。 表を改良したら、色々式を試したいと思います。ありがとうございます。
せっかく教えて頂いたのに、ちょっとの間、この作業ができないので 今回教えて頂いたことを試した結果報告が来月になってしまいますが、 まずはお礼だけを先に･･･。毎回ありがとうございます。助かります。
coco
表の改良？ ・・・ってどの表ですかね？
しげちゃんさんが回答してくださっている式で 「月」が入力されている部分を、該当のセル番地に変更すれば 一覧表は簡単に作成出来ると思いますよ。
私が上でサンプルとして出した、C13以降の集計では C13に =SUMPRODUCT(($C$5:$C$550=B13)*(($J$5:$J$550="出張")+($J$5:$J$550="出社"))*(($H$5:$H$550>=0.1875)*0.5+0.5)) を入れて、下にフィルドラッグします。
あとは、F1の式を何処へ配置するか・・・ と言う事だと思いますが。
一覧表を作らないのであれば、「月」が入力されている部分を SUBTOTALに変更して =SUMPRODUCT((C5:C550=SUBTOTAL(4,C5:C550))*((J5:J550="出張")+(J5:J550="出社"))*((H5:H550>=0.1875)*0.5+0.5)) こんな感じで・・・。
休みに関しては（一覧表を作らないとして） =SUMPRODUCT((C5:C551=SUBTOTAL(4,C5:C551))*((I5:I551<>"")*0.5+(I5:I551="全日休")*0.5)) こんな感じで良いと思いますが。
(HANA)
>表を改良したら、色々式を試したいと思います。 参考までに
> A B C D E F G H I J ・・・ > 4 年度 年 月 日 曜日 出勤 退勤 勤務時間 休日消化 休日出勤
A B C D E F G H I ・・ 4 年度 年 月 日 曜日 出勤 退勤 勤務時間 休日消化 休日出勤
B列はシリアル値で入力、書式で設定。
=SUMPRODUCT((MONTH(B5:B550)=9)*((I5:I550="出張")+・・・・
F列は作業用列として、休日消化日数を入力（午前休、午後休は0.5、全休は1と入力） G列は出勤、退勤時刻およびF列の値により自動的に出力
=SUMPRODUCT((MONTH(B5:B550)=9)*(F5:F550))
H列は作業用列として、休日出勤数値で入力（出張は１、出社は２、○○は３と入力） I列はH列の値を元に表示
=SUMPRODUCT((MONTH(B5:B550)=9)*(I5:I550>0)*(($E$5:$E$550>=0.1875)*0.5+0.5))
エクセルは、表計算ソフトです。なるべく数値を使用する方が計算が楽になるのでは?
表の構成がわかりませんので、参考までに
By しげちゃん
=SUMPRODUCT((Sheet2!$B$4:$B$5000=Sheet1!$B6)*(Sheet2!$C$4:$C$5000=Sheet1!C$5),Sheet2!$D$4:$D$5000)
Ａシートを月でオートフィルタで絞ったものを、
Ｂシートに表示したいのですが、
サムプロダクト関数とサブトータルの組み合わせ方が
うまくいきません。
=SUMPRODUCT((SUBTOTAL(3,INDIRECT(Sheet2!$B$4:$B$5000=Sheet1!$B7)))*( Sheet2!$C$4:$C$5000=Sheet1!C$5,Sheet2!$D$4:$D$5000)))
以上のような関数を組んだのですが、エラーになってしまいました。
どなたか、おわかりになる方お知恵を拝借したいです。
どうぞよろしくお願い致します。
＜Ａシートのデータ＞
月 科目 会社 数字
4 諸費 Ｈ 1
4 雑費 Ｔ 2
5 新聞費 Ｓ 3
6 消耗品 Ｓ 4
＜Ｂシートのデータ＞
Ｈ Ｓ Ｔ 諸費 1 0 0 雑費 0 0 0 新聞費 0 3 0 消耗品 0 4 0
<NANAMI>
「古い記事なので、新しく質問をたてた方が良いですよ。」 って書こうと思ったら↓でしたね。 [[20110901005703]] 『サムプロダクトとサブトータルの組み合わせがうま』（ななみ）
後始末もお忘れなく。。。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.