旅費を二重払いしないための関数SUMPRODUCT
- エクセル2010を使っている者です。たとえば以下のようにエクセルの表において、旅費精算をする1つの旅行(出張)について一行で書いたシートがあったとします。
- 田中一郎さんについて見ていただきたいのですが、この場合、機械的に作業すると二重に旅費を支払ってしまいそうな場合に関係する行は、10行目以外のすべてです。氏名が同じで、出発日、到着日が重なっている部分がある場合はもちろんのこと、連続して出張している場合(11月5日、11月6日)もその危険性があります。
- 指定したセル(列)に「確認」という文字が入るように以下の関数を組んで下にドラッグしているのですが、完全にはうまく動いてくれません。12行目の11月15日の出張についても「確認」と入ってほしいのですが、11行目の14日の行については「確認」と入るのに、12行目には入りません。
- ベストアンサー
旅費を二重払いしないための関数SUMPRODUCT
エクセル2010を使っている者です。 たとえば以下のようにエクセルの表において、旅費精算をする1つの旅行(出張)について 一行で書いたシートがあったとします。 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 田中一郎さんについて見ていただきたいのですが、この場合、機械的に作業すると 二重に旅費を支払ってしまいそうな場合に関係する行は、10行目以外のすべてです。 氏名が同じで、出発日、到着日が重なっている部分がある場合はもちろんのこと、 連続して出張している場合(11月5日、11月6日)もその危険性があります。 (たとえば、11月3日から5日が東京→仙台、11月6日・7日が青森出張だった 場合、5日は東京に戻らずに仙台から青森まで行った可能性があるからです) こうした場合に、指定したセル(列)に「確認」という文字が入るように以下の関数を 組んで下にドラッグしているのですが、完全にはうまく動いてくれません。 12行目の11月15日の出張についても「確認」と入ってほしいのですが、11行目の14日の 行については「確認」と入るのに、12行目には入りません。 =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),"確認","")) よろしくお願いいたします。
- qazxcvfr4
- お礼率91% (833/910)
- Excel(エクセル)
- 回答数3
- ありがとう数3
- みんなの回答 (3)
- 専門家の回答
質問者が選んだベストアンサー
考えをまとめるため、はじめに、重複判定の方法を解説しておきます。 AとBで範囲が重複しているデータというのは、 A ===== B===== A===== B ===== A ======= B ===== A ===== B ======= 以上のパターンになります(端がそろっているものも同じパターンとみることにします) これに共通している条件は Aの始端<=Bの終端 かつ Bの始端<=Aの終端 です。 (念のため、重複しないデータは (A ==== (B ==== (Bの始端<=Aの終端の条件に合わないことがわかります。 さらに連続データも含めるため、検討するデータは両端を1日ずつ大きくして考えることにします。 これを式にすると (2行目に書く式の場合) {=if( sumproduct( (B$2:B$1000=B2) * (C$2:C$1000<=D2+1) * (C2-1<=D$2:D$1000) ) > 1,"確認","" ) } (1行目は、見出しが書かれているので、2行目からの検討にしています。) この式では、かならず自分自身のデータも比較されてしまう(たとえばC2=<D2+1)ので、ほんとなら条件に合わない場合でもsumproductの結果は1になってしまいます。なので、条件に合う時の値は2以上なので最後の比較は >=1 でなく >1 になっています。
その他の回答 (2)
- kagakusuki
- ベストアンサー率51% (2610/5101)
Excel2010を御使いなら、SUMPRODUCT関数ではなく、COUNTIFS関数を使った方が計算処理の負荷が軽くなるかと思います。 次の関数を「確認」と表示させたい列のセル(行番号は問いません)に入力してから、そのセルをコピーして、「確認」と表示させたい欄の他の全てのセルに貼り付けて下さい。 =IF(AND(INDEX($B:$B,ROW())<>"",COUNT(INDEX($C:$C,ROW()),INDEX($D:$D,ROW()))=2),SUBSTITUTE(IF(COUNTIFS($B:$B,INDEX($B:$B,ROW()),$C:$C,"<="&INDEX($C:$C,ROW()),$D:$D,">="&INDEX($C:$C,ROW())-1)>1,"出発確認","")&IF(COUNTIFS($B:$B,INDEX($B:$B,ROW()),$D:$D,">="&INDEX($D:$D,ROW()),$C:$C,"<="&INDEX($D:$D,ROW())+1)>1,"帰着確認",""),"確認帰","・帰"),"") すると、 出発日が「他の出張の、出発日から『帰着日の翌日』までの期間」に重なる場合には「出発確認」と表示され、 帰着日が「他の出張の、『出発日の前日』から帰着日までの期間」に重なる場合には「帰着確認」と表示され、 出発日が「他の出張の、出発日から『帰着日の翌日』までの期間」に重なっていて、尚且つ帰着日が「他の出張の、『出発日の前日』から帰着日までの期間」に重なっている場合には「出発・帰着確認」と表示されます。
お礼
素晴らしい関数をありがとうございます。 なんとか解読できました。
- picopico_7
- ベストアンサー率30% (11/36)
No.1の方がとても分かりやすくまとめて下さっているのでお気づきになったと思いますが、3つの条件を満たす解は最低1つは必ず存在します。 また、質問者さんは「11行目には確認が入るが12行目には入ってくれない」とおっしゃっておられましたが12行目の出発日(11/15)以上である到着日はデータ上1つ(12行自身)しかないので「確認」の文字が入りません。 もしまだ解決されていないようでしたら条件を箇条書きにして補足に書いてみて下さい。
お礼
ありがとうございます。 回答していただき、理解できました。 画像付きでありがとうございました。
関連するQ&A
- エクセルで旅費精算する際の工夫で困ってます。
たとえば以下のようなエクセルの表において、 名前(A列) 出発日(B列) 帰着日(C列) 田中 一郎 11/3 11/5 田中 次郎 11/1 11/1 田中 三郎 11/5 11/6 田中 四郎 11/2 11/2 田中 五郎 11/2 11/5 田中 一郎 11/7 11/8 田中 六郎 11/9 11/12 田中 一郎 11/5 11/7 最後の行は、同じ人物の帰着日と出発日がダブっているので、旅費の二重支払いをしてしまうおそれがあります。 D列に”チェック”という文字が出るなり、条件付書式でどこかのセルに色がつくなりで、注意するべきものであることがわかるよるにはどうすれば良いでしょうか? =IF(DATEDIF(B3,VLOOKUP(A3,A$1:C2,3,0),"D")<1,"チェック","") としてもVLOOKUPでは一つの行しか対象にできないのでダメですし、 大変困ってます。 詳しい方、よろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセル SUMPRODUCT関数でOR検索
添付画像のような表があります。 '=SUMPRODUCT((B:B="男")*(C:C="新潟")*(D:D="B")*ROW(A:A)) で、B列が男、C列が新潟、B列がB のデータがある行番号を返してくれます。 B列がBではなく、B列がAまたはABのデータがある番号を検索する場合はどうかなと思い、 '=SUMPRODUCT((B:B="男")*(C:C="新潟")*OR((D:D="A"),(D:D="AB"))*ROW(A:A)) としてみたら、該当データがないにもかかわらずB列がBの行番号が帰りました。SUMPRODUCTで検索する場合、ORを使う場合はどのように書けばよいのでしょうか? ’=SUM(SUMPRODUCT((B:B="男")*(C:C="新潟")*(D:D="A")*ROW(A:A)),SUMPRODUCT((B:B="男")*(C:C="新潟")*(D:D="AB")*ROW(A:A))) のようにそれぞれを求めて合算するしかないのでしょうか?
- ベストアンサー
- Excel(エクセル)
- SUMPRODUCT関数について
SUMPRODUCT関数を使って「タテ100行ヨコ10列のデータの入った表」からA1*B1+A2*B2+A3*B3+……+A99*B99+A100*B100の計算と A1*C1+A2*C2+A3*C3+……A99*C99+A100*C100というように10列共計算しました。 さらに各々の掛け算の結果を小数点以下を切り捨てたいのでTRUNC関数を使おうと思いましたが上手くできません。 他の関数でも良いのですが良い知恵をお貸しください。
- ベストアンサー
- その他MS Office製品
- SUMPRODUCT関数について
よろしくお願いいたします。EXCEL2013使用です。 上の画像、出張者リスト(名前、出発日、帰国日)から、別のシートに一目でわかる一覧表を作りたいのです。例えば、11/06-11/15まで出張だったとしたら、下の表で6日から15日までを黄色背景にする、等です。 下の表で、日付セルの下の各セルに次のような関数を入力しましたがうまく動作しません。 =SUMPRODUCT((リスト!$B$2:$B$300=$A4)*(INT(リスト!$C$2:$C$300)>=INT(B$2))*(INT(リスト!$D$2:$D$300)<=INT(B$2))) ※下の画像は12月ですが、その上に11月があり、上記関数内のセルのアドレスが間違っている訳ではありません。 SUMPRODUCT関数は大変便利なのでこれまで多用してきましたが、今回は完全に頓挫してしまいました。 どこがいけないのでしょうか。おわかりの方、是非ご教示下さいませ。 どうぞよろしくお願いいたします。
- ベストアンサー
- Excel(エクセル)
- SUMPRODUCT関数について
SUMPRODUCT関数を使いA列の対象値(LEFT関数を使い左一文字を検索対象としC列の該当値の合計を集計していますがLEFT関数で検索対象を複数にする場合はどのようにすればいいのでしょうか? 下記の場合はsとdを検索対象としたいのですが。 宜しくお願いいたします。 A B C 1 sb 20 2 sc 30 3 sd 40 4 db 50 5 dc 60
- ベストアンサー
- Excel(エクセル)
- エクセル 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 ・・・ この場合、「2」となるようにです。 IF、OR関数あたりかな と考えておりますが、SUMPRODUCT関数のどの部分で条件付ければ良いか解りません。 説明下手で大変申し訳ございませんが、ご助力いただければ幸いです。
- 締切済み
- オフィス系ソフト
- エクセルの関数(SUMPRODUCT?)
ご存知の方がいらっしゃいましたら教えていただけたら幸いです。 A列 B列 C列 D列… 1 ID 部名 課名 都道府県… 2 1 A a 北海道 3 2 A c 大阪 4 3 A b 東京 5 4 A a 東京 6 5 A a 福島 [シート名:入力] A列 B列 C列 D列 E列… 1 地域 a b c 合計(←上記シート「入力」の課名) 2 北海道 2(Q2) 0 0 2(Q3) 東北 3 東京 1(Q1) 1 0 2 4 関西 0 0 1 1 [シート名:合計] (Q1)=SUMPRODUCT((入力!$C$2:$C$6="a")*(入力!$D$2:$D$6="東京")) で、地名を1つにしている場合は問題なく反映されています。 (Q2)★ここが一番聞きたいところです★ =SUMPRODUCT((入力!$C$2:$C$6="a")*(入力!$D$2:$D$6="北海道")*(入力!$D$2:$D$6="青森")*(入力!$D$2:$D$6="岩手")*(入力!$D$2:$D$6="宮城")*(入力!$D$2:$D$6="秋田")*(入力!$D$2:$D$6="山形")*(入力!$D$2:$D$6="福島")) で、エラーがでてしまいます。(イヤミではないんですが…)東京など大きなところでは(1)の処理でよいのですが、例えばここだと北海道や青森、秋田…とあまりその都道府県だけでは数の少ないところは「地域」として「北海道・東北」とまとめたく、上の式では、 「aの課で、且つ北海道か、青森か、岩手か、宮城か、秋田か、山形か、福島であればカウントする」としたいのです。 ちなみに(2)の式を入力すると、エラーは出ないのですが該当するものがあるにも関わらずゼロ(2ではなく0)と表示されてしまいます。 (Q3)よって、本当であればここの関数も 「aの部で、且つaの課で、且つ北海道か、青森か、岩手か、宮城か、秋田か、山形か、福島であればカウントする」としたいところ、(2)でつまずいてしまっているため、E2のセルの表示「2」はそのシートのB2-D2のSUM関数で合計を出しているという状況です。 (3)は以上のような対処で問題はないかと思いますが、(2)の部分はどうしても表示されません。 前任者のデータを引き継ぐ形となり、去年とは少し違う形式になるに伴い出てきた問題です。できれば去年のものをできるだけ引き継ぎたいと考えているのですが…何か良い方法があれば教えて頂ければ幸いです。
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCTとワイルドカード
こんばんは。 <例> A B C (D) 1 北海道 500 100 =B1*C1 2 青森県 400 150 =B2*C2 3 新潟県 200 200 =B3*C3 4 東京都 200 100 =B4*C4 5 沖縄県 250 100 =B5*C5 合計=SUM(D1:D5) 作業列(D列)を作らず合計を求めるには、 =SUMPRODUCT(B1:B5,C1:C5) でできると思います。 ただ、ここに「県のみ合計」という条件が入った場合について質問します。 例のように作業列(D列)を作り、SUMIFですれば簡単にできるのですが、「作業列を作らず、SUMPRODUCTとワイルドカード(*県)を使って」数式を組み立てるにはどうすればいいのでしょうか? ステップ(D列)を踏まず、ひとつの数式だけで完結したいのです。 試行錯誤していますが、簡単そうなのになかなかうまくいきません…。 D1=COUNTIF(A1,"*県")で以下オートフィルコピーし、 =SUMPRODUCT(B1:B5,C1:C5,D1:D5) とすればできますが、結局作業列(D列)を作ってしまっていますし…。 あと…たとえば最初に示した数式なら、 =SUMPRODUCT((B1:B5)*(C1:C5)) という数式の組み方を回答でよく見かけますが、 =SUMPRODUCT(B1:B5,C1:C5) =SUMPRODUCT(B1:B5*C1:C5) としないのは、何か意味の違いがあるのでしょうか? お詳しい方よろしくお願いします。 Vista Excel2007です。
- ベストアンサー
- その他MS Office製品
- エクセル関数SUMPRODUCTについて
エクセル2000です。 A列とB列の和にC列を乗じたものの合計は、 =SUMPRODUCT(A1:A10,$C$1:$C$10)+SUMPRODUCT(B1:B10,$C$1:$C$10) または =SUM(SUMPRODUCT(A1:A10,$C$1:$C$10),SUMPRODUCT(B1:B10,$C$1:$C$10)) のような長ったらしいものになるのでしょうか? =SUMPRODUCT((A1:B10)*C1:C10) は、表中に文字列が入る場合があるのでエラーになります。 ご教示いただければ幸いです。
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数について
SUMPRODUCT関数について いつもお世話になっています。 以前こちらでSUMPRODUCT関数を教えていただき大変統計がしやすくなり、 仕事が効率化しました。ありがとうございます。 ところで、SUMPRODUCTだと思うのですが、計算式がうまく組み立てられないのでご質問させていただきます。 A列に部門名、B列に実施日が入力されているエクセルシートがあり、 部門ごとの実施件数を出したいのです。 SUMPRODUCT((A2:A100="○部門")*(CAUNTA(B2:B100))) としてみましたがうまくいきません。 たぶん *(CAUNTA(B2:B100)) の部分がいけないのだろうとは思うのですが、どう変化させればいいのでしょうか? 関数の選択が間違っているのかもしれない、とは思うのですが、適切なものが分かりません。 ご教授お願いします
- ベストアンサー
- オフィス系ソフト
お礼
ありがとうございます。 非常にわかりやすく、適切に書いていただき、大変助かりました。