- 締切済み
エクセルのＳＵＭＰＲＯＤＵＣＴ関数について
日 名前 2人工 10/1 山田 ＊ 10/1 田中 10/1 佐藤 ＊ 10/1 山田 10/3 佐藤 この表から一日の稼働率をシート2に導きたいと考えています。 2人工のセルに＊が入っている場合、0.5と計算し、 10/1日の山田さんの稼働率は0.5＋１の1.5です。 自分でチャレンジしたのはW列に日付を数字を書き出して SUMPRODUCT((B2:B13="山口")*(W2:W13=1))というのを考えたのですが、 ここから、2人工にチェックが入っている場合はどうして計算すればいいのか、＊を0.5として計算すればいいのか悩んでます。 そしてW列に日付を書かなくても計算できたらなおありがたいです。 どうかどうかお願いいたします。
- fwaha
- お礼率60% (3/5)
- その他MS Office製品
- 回答数5
- ありがとう数7
- みんなの回答 （5）
- 専門家の回答
みんなの回答
- tom04
- ベストアンサー率49% (2537/5117)
No.2・3です！ 返答が遅くなってごめんなさい。 もう一度画像をアップさせてもらいます。 最終的には↓のようにしたいという事ですよね？ 前回回答した表の作業列はそのまま利用できます。 日付と氏名の作業列と領域の範囲指定の違いだけだと思います。 もう一度数式を書いておきます。 （もしかして前回と若干異なるかもしれませんので、その場合はご容赦を！） 作業用の列の数式は前回のままでOKだと思います。 Sheet2の氏名のA2セルに =IF(COUNT(Sheet1!$W$2:$W$1000)<ROW(A1),"",INDEX(Sheet1!$Q$2:$Q$1000,SMALL(Sheet1!$W$2:$W$1000,ROW(A1)))) と入れて、オートフィルで下へコピーします。 日付のB1セルに =IF(COUNT(Sheet1!$V$2:$V$1000)<COLUMN(A1),"",INDEX(Sheet1!$A$2:$A$1000,SMALL(Sheet1!$V$2:$V$1000,COLUMN(A1)))) とし、これも右（列方向）にコピーします。 最後にB2セルに =IF(OR($A2="",B$1=""),"",SUMPRODUCT((Sheet1!$A$2:$A$1000=B$1)*(Sheet1!$Q$2:$Q$1000=$A2)*(Sheet1!$U$2:$U$1000=""))+SUMPRODUCT((Sheet1!$A$2:$A$1000=B$1)*(Sheet1!$Q$2:$Q$1000=$A2)*(Sheet1!$U$2:$U$1000="*")*0.5)) という数式を入れ、行と列方向にコピーすると 画像のような感じになります。 尚、前回は元データが100行までの数式みたいだったようですが、 今回は1000行まで対応できるようにしています。 以上、何度も顔を出してしまいましたが、 参考になれば幸いです。m(__)m
- KURUMITO
- ベストアンサー率42% (1835/4283)
- tom04
- ベストアンサー率49% (2537/5117)
No.2です！ たびたびすみません。 投稿した後でもう一度質問文を読み返してみると 前回の回答は大きく外しているみたいでした。 もう一度画像をアップさせていただきます。 各日付ごとに、それぞれのメンバーの稼働率を表示させれば良いのかと判断しました。 Sheet1にもう1列氏名用の作用列を使わせてもらいました。 Sheet1のV2セル（日付用の作業列）は =IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"") W2セル（氏名用の作業列）は =IF(COUNTIF($Q$2:Q2,Q2)=1,ROW(A1),"") とし、V2・W2セルを範囲指定し、オートフィルで1000行目くらいまでコピーします。 そしてSheet2のB1セル（氏名を表示させるセル）に =IF(COUNT(Sheet1!$W$2:$W$1000)>=COLUMN(A1),INDEX(Sheet1!$Q$2:$Q$1000,SMALL(Sheet1!$W$2:$W$1000,COLUMN(A1))),"") という数式を入れ、列方向にオートフィルでコピーします。 A2セルに =IF(COUNT(Sheet1!$V$2:$V$1000)>=ROW(A1),INDEX(Sheet1!$A$2:$A$1000,SMALL(Sheet1!$V$2:$V$1000,ROW(A1))),"") B2セルに =IF($A2="","",SUMPRODUCT((Sheet1!$A$2:$A$1000=Sheet2!$A2)*(Sheet1!$Q$2:$Q$1000=Sheet2!B$1)*(Sheet1!$U$2:$U$1000=""))+SUMPRODUCT((Sheet1!$A$2:$A$1000=Sheet2!$A2)*(Sheet1!$Q$2:$Q$1000=Sheet2!B$1)*(Sheet1!$U$2:$U$1000="*")*0.5)) という数式を入れ、列方向にオートフィルで氏名欄が埋まっている列までコピーします。 最後にA2～B列の最後の列までを範囲指定し、2行目の最後のセルのフィルハンドルで 下へコピーすると画像のような感じになります。 これで個人個人の数値が表示されるはずです。 以上、長々と書きましたが 参考になれば幸いです。 尚、これも的外れなら読み流してくださいね。m(__)m
お礼
凄く参考になりました。
補足
有難うございます。 日付ごとの個人の稼働率を表示したかったので、大変参考になりました。日付と名前の配置を移動したいなと思い、挑戦いたしましたがなかなか思うように出来ません。よろしけば教えてください。 私は日付のところに=IF(COUNT(Sheet1!$Y$2:$Y$1000)>=COLUMN(A1),INDEX(Sheet1!$A$2:$A$1000,SMALL(Sheet1!$Y$2:$Y$1000,COLUMN(A1))),"") 名前のところにIF(COUNT(Sheet1!$Z$2:$Z$1000)>=ROW(A1),INDEX(Sheet1!$R$2:$R$1000,LARGE(Sheet1!$Z$2:$Z$1000,ROW(A1))),"") をいれました。稼働率の式はわからなかったのです。 お願いいたします。
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは！ 解釈が違っていたらごめんなさい。 結局氏名は関係なく1日の稼働率だけを表示できれば良いわけですかね？ 例えば、10/1の場合は「3」という具合に・・・ 一応そういうことだとしての回答になります。 一例ですが、↓の画像で説明させていただくと、 Sheet1のV列を作業列とさせてもらいます。 V2セルに =IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"") としてオートフィルで下へコピーします （Sheet2の数式が1000行まで対応できるようにしていますので、 1000行くらいまでコピーしても構いません） 次にSheet2の「日付」のA2セルに =IF(COUNT(Sheet1!$V$2:$V$1000)>=ROW(A1),INDEX(Sheet1!$A$2:$A$1000,SMALL(Sheet1!$V$2:$V$1000,ROW(A1))),"") B2セルに =IF(A2="","",SUMPRODUCT((Sheet1!$A$2:$A$1000=Sheet2!A2)*(Sheet1!$U$2:$U$1000=""))+SUMPRODUCT((Sheet1!$A$2:$A$1000=Sheet2!A2)*(Sheet1!$U$2:$U$1000="*")*0.5)) として、A2・B2を範囲指定し、B2セルのフィルハンドルで 下へコピーすると画像のような感じになります。 希望はこんな形でしょうか？ 参考になれば幸いですが、 的外れなら読み流してくださいね。m(__)m
お礼
こちらも大変参考になりました。 有難うございます。 10/1 10/2・・・・・・ 山田 田中 佐藤 という風に求めたいのですが、申し訳ありませんが教えてください。
- kmetu
- ベストアンサー率41% (562/1346)
関連するQ&A
- ＳＵＭＰＲＯＤＵＣＴ関数の書式
以前下記内容の回答をいただきました。 Ａ Ｂ Ｃ Ｄ Ｅ １ 日付 コード 担当者 金額 合計 2 4/1 販促 佐藤 2000 2000 3 4/1 交通 鈴木 1000 3000 4 4/2 交際 佐藤 2000 5000 5 4/10 旅費 山田 1500 6500 6 5/2 販促 佐藤 1500 8000 7 5/16 交通 佐藤 1500 9500 以上の様な書式があります この書式から 4月の佐藤の販促代は幾らか？ の合計の出し方ありますか？ の回答をいただき ＝SUMPRODUCT((MUNTH($A$2:$A$7)=4)*($B#2:$B$7="販促”)*($C$2:$C$7="佐藤")*($D$2:$D$7) の計算式で出す事が出来ました。 そこで欲張って4月の佐藤の販促代と交際代の合計を出す事出来ますでしょうか？ ＝SUMPRODUCT((MUNTH($A$2:$A$7)=4)*($B#2:$B$7="販促”)*($C$2:$C$7="佐藤")*($D$2:$D$7)、(MUNTH($A$2:$A$7)=4)*($B#2:$B$7="交際”)*($C$2:$C$7="佐藤")*($D$2:$D$7) とやりましたら、エラーにはならずに０になりました。 もう一度お知恵をお借りしたいです。 宜しくお願い致します
- ベストアンサー
- Windows XP
- SUMPRODUCT関数について
SUMPRODUCT関数について いつもお世話になっています。 以前こちらでSUMPRODUCT関数を教えていただき大変統計がしやすくなり、 仕事が効率化しました。ありがとうございます。 ところで、SUMPRODUCTだと思うのですが、計算式がうまく組み立てられないのでご質問させていただきます。 A列に部門名、B列に実施日が入力されているエクセルシートがあり、 部門ごとの実施件数を出したいのです。 SUMPRODUCT（（A2:A100="○部門"）＊（CAUNTA(B2:B100)）） としてみましたがうまくいきません。 たぶん ＊（CAUNTA(B2:B100)） の部分がいけないのだろうとは思うのですが、どう変化させればいいのでしょうか？ 関数の選択が間違っているのかもしれない、とは思うのですが、適切なものが分かりません。 ご教授お願いします
- ベストアンサー
- オフィス系ソフト
- エクセルにて「期間指定」で「データ集計」する方法
エクセルで営業商談用顧客管理を行っています。 シート１には、商談日を下記のように記載しており Ａ列 Ｂ列 日付 担当 1/1 山田 1/6 田中 1/8 佐藤 2/3 山田 2/4 山田 2/6 佐藤 2/9 田中 3/1 佐藤 3/2 山田 3/5 佐藤 別シートにて、例えば1月にどの担当が何件商談をしたか、を 下記のように管理したいのですが 山田 ○件 田中 ○件 佐藤 ○件 こちらの関数の指定方法について、ご教授いただけないでしょうか？ よろしくお願いします
- 締切済み
- マーケティング・企画
- 旅費を二重払いしないための関数SUMPRODUCT
エクセル2010を使っている者です。 たとえば以下のようにエクセルの表において、旅費精算をする１つの旅行（出張）について 一行で書いたシートがあったとします。 NO(A列） 名前（B列） 出発日（C列） 帰着日（D列） 1 田中 一郎 11/3 11/5 2 田中 一郎 11/2 11/4 3 田中 三郎 11/5 11/6 4 田中 四郎 11/2 11/2 5 田中 五郎 11/2 11/5 6 田中 六郎 11/9 11/12 8 田中 一郎 11/6 11/7 9 田中 一郎 11/3 11/3 10 田中 一郎 11/10 11/11 11 田中 一郎 11/14 11/14 12 田中 一郎 11/15 11/15 田中一郎さんについて見ていただきたいのですが、この場合、機械的に作業すると 二重に旅費を支払ってしまいそうな場合に関係する行は、１０行目以外のすべてです。 氏名が同じで、出発日、到着日が重なっている部分がある場合はもちろんのこと、 連続して出張している場合（１１月５日、１１月６日）もその危険性があります。 （たとえば、１１月３日から５日が東京→仙台、１１月６日・７日が青森出張だった 場合、５日は東京に戻らずに仙台から青森まで行った可能性があるからです） こうした場合に、指定したセル（列）に「確認」という文字が入るように以下の関数を 組んで下にドラッグしているのですが、完全にはうまく動いてくれません。 １２行目の１１月１５日の出張についても「確認」と入ってほしいのですが、１１行目の１４日の 行については「確認」と入るのに、１２行目には入りません。 =IF(SUMPRODUCT((B$1:B$1000=B1)*(C$1:C$1000<=D1+1)*(D$6:D$1000>=C1))>1,"確認","")) うまくいかなかったので、以下のようにしたのですが、もっとダメでした。 上の関数はずっと前にここの掲示板で教えていただいたものを少しだけ変えたものなのですが、 そもそもなぜ、sumproduct関数の値を「>=1」ではなく「>1」としているのでしょうか？ =,IF(OR(SUMPRODUCT((B$6:B$1000=B1)*(C$1:C$1000<=D1+1)*(D$1:D$1000>=C1))>=1,SUMPRODUCT((B$1:B$1000=B1)*(D$1:D$1000=C1-1))>=1),"確認","")) よろしくお願いいたします。
- ベストアンサー
- Excel（エクセル）
- エクセル関数について
エクセル２００３です。 宜しくお願いします。 A列に担当者名、B列に金額が入力されており、 山田 １００００ 田中 ８０００ 山田 ２６０００ 田中 ２００００ 山田 ２００００ 山田 －２６０００ 山田 －１００００ 例えば、担当山田で、なおかつ20000以上の金額の合計、または販売本数を出す場合、以前質問させて頂いたのですが、SUMPRODUCT関数でできたのですが、上記のようにキャンセル（－）が入ってくる場合にどのような式にしたらいいでしょうか この場合ですと、山田の20000円以上の合計金額が２００００円、販売本数が１と表示させたいです。 何卒お願いします
- ベストアンサー
- オフィス系ソフト
- エクセル SUMPRODUCT関数について教えてください
エクセルで作成した表から ○○かつ●●のデータ個数を求めたく、 SUMPRODUCT関数を こちらで調べておりましたが どうしても解決しない部分があります。 どうぞ よろしくお願いいたします。 下記8桁数字は、日付です。 カウントしたい数は、B列（終了）が、A1セルに入力された年月かつ、区分（C列）が「A」の数です。 =SUMPRODUCT((LEFT(B2:B100,6)=A1)*(C2:C100="A")) こちらで、『B列=A1セル かつ C列="A"』のみのカウントは出来たのですが、 終了延長（D列）が空欄でない場合は、D列日付データの方をカウントする。 早期終了（E列）が空欄でない場合は、D列・B列よりも E列を優先し、カウントする。という条件を付けたいのですが、 Sheet1 A1セルに「200810（数を調べたい年月を入力）」 （開始） （終了） （区分） （終了延長） （早期終了） A列 B列 C列 D列 E列 20061001 20081001 A - - 20070926 20081020 A - - 20071102 20081009 A 20081201 - 20080101 20081013 B - - 20080208 20081010 A - 20080210 ・・・ この場合、「２」となるようにです。 IF、OR関数あたりかな と考えておりますが、SUMPRODUCT関数のどの部分で条件付ければ良いか解りません。 説明下手で大変申し訳ございませんが、ご助力いただければ幸いです。
- 締切済み
- オフィス系ソフト
- SUMPRODUCT関数に関して
A列に日付、B列に曜日、C列に記号が入力されている表から 曜日後との記号の個数を出したい時にSUMPRODUCTを使っていますが、 曜日の表現の仕方が悪いのか、しっかりカウントされません。 解消方法が分からないので、教えていただきたいです。 A列 B列 C列 4/1 金 a 4/1 金 a 4/2 土 p 4/3 日 a 4/3 日 a 4/8 金 e 【作りたい状態】 a e p 金 2 1 0 土 0 0 1 日 2 0 0 ※元の表の曜日はA列の日付に依存して、aaaで表記しています。
- ベストアンサー
- Excel（エクセル）
- SUMPRODUCT関数について
アンケートの集計をするにあたって、 SUMPRODUCT関数を使いました。 シートが2つあります。 【データ】シート・・・アンケートのデータ A:No. B：データ 1 0 2 1 3 2 4 3 5 空欄が入っている ・ ・ 【集計】シート・・・B列の空欄、1～3の個数を集計する B列に数式 =SUMPRODUCT((データ!$A$4:$A$40000<>"")*(データ!B$4:B$40000=$A4)) を入れました。 A B 0 2 ・・・・※ 1 1 2 1 3 1 空欄 2・・・・※ ０（ゼロ）と空欄を区別しないで同じと計算してしまいます。 データに、ゼロと空欄が混在している場合、SUMPRODUCTは使えないのでしょうか？ この場合、どの数式を使うのがベストでしょうか？ 分かりにくくて申し訳ありませんが、 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセル SUMPRODUCT関数でこんな場合は？
Excel2000です。 添付画像のような表があります。 A列、B列に数値、C列に文字列です。 A列の数値は単なる数値ではなく、計算式で求められています。 したがって表内A列の何も表示されていないセルは、計算の結果 ="" になったものです。 B列の数値は直接入力した単なる数値です。 この表で仮に、C列がAの場合のA列合計を求めるため、D6に =SUMPRODUCT((A1:A5)*(B1:B5)*(C1:C5="A")) としてみましたが、="" を乗じているためVALUEエラーになります。 =""を排除しようと、 =SUMPRODUCT((A1:A5<>"")*(A1:A5)*(B1:B5)*(C1:C5="A")) としてみましたが、やはりVALUEエラーになります。 こういう場合はどうしたらよいのでしょうか？ ="" を =0 に変えるという回答以外でお願いいたします。
- ベストアンサー
- オフィス系ソフト
お礼
有難うございます。 綺麗にできました。 何度も何度も悩んでいたので、嬉しいです！！！ 私もtom04さんみたいに関数をもっともっと理解できるよう がんばりたいと思います！！ 本当に助かりました。感謝しています。