- ベストアンサー
データベース抽出とSUMPRODUCT関数
- エクセル2007で請求書NOごとに購入金額を合算して表示する方法について調べています。
- 同じ購入者に複数の請求書NOが付番されているため、金額の合計が上手くできません。
- sumproduct関数を試しましたが、エラーが発生しています。
- aidorumary
- お礼率5% (16/297)
- オフィス系ソフト
- 回答数14
- ありがとう数2
- みんなの回答 （14）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (13)
- MackyNo1
- ベストアンサー率53% (1521/2850)
- MackyNo1
- ベストアンサー率53% (1521/2850)
請求書番号をどこかのセルに入力し、ご希望のデータを引っ張ってくることも可能ですが、請求書のレイアウトにするなら、添付画像のように購入者を入力し（もちろん請求書番号を入力でも可能）、その関連データを一覧にする方が良いのではないでしょうか？ まずF2セルに購入者を入力します（購入番号でも表示可能） 請求金額合計のG3セルには以下の式を入力します。 =SUMIF(B2:B10,F2,D2:D10) F6セルに以下の式を入力し右方向に３つ、および下方向にオートフィルします。 =INDEX(A:A,SMALL(INDEX(($B$2:$B$10<>$F$2)*1000+ROW($B$2:$B$10),),ROW(A1)))&"" 最後にG列の購入者の表示されているセル範囲を選択し、右クリック「削除」で「左方向にシフト」すれば完成です。
- tom04
- ベストアンサー率49% (2537/5117)
こんにちは！ 外していたらごめんなさい。 ↓の画像で説明します。 Sheet2の購入者は飛び飛びではなく同じ人が続いているという前提です。 とりあえずSheet2の1000行目まで対応できるようにしてみました。 Sheet1のB2セル =IF(D2="","",INDEX(Sheet2!A2:A1000,MIN(IF(Sheet2!B2:B1000=D2,ROW(A1:A999))))) これは配列数式になってしまいますので、この画面からB2セルに貼り付け後、 F2キーを押す、またはB2セルをダブルクリック、または数式バー内で一度クリックします。 編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定します。 数式の前後に｛ ｝マークが入り配列数式になります。 C2セルは =IF(A2="","",INDEX(Sheet2!A2:A1000,MAX(IF(Sheet2!B2:B1000=D2,ROW(A1:A999))))) （これも配列数式） D2セルは =IF(A2="","",INDEX(Sheet2!B2:B1000,A2)) （配列数式ではありません） E2セルは =IF(COUNTIF(Sheet2!$B$2:$B$1000,$D$2)<ROW(A1),"",INDEX(Sheet2!$C$2:$C$1000,SMALL(IF(Sheet2!$B$2:$B$1000=$D$2,ROW($A$1:$A$999)),ROW(A1)))) （配列数式です。） としてオートフィルで下へずぃ～～！っとコピー F2セルは =IF(D2="","",SUMIF(Sheet2!B2:B1000,D2,Sheet2!D2:D1000)) （配列数式ではありません） これでA2セルに何か数値を入力すると 希望に近い表示にならないでしょうか？ 長々と失礼しました。m(__)m
補足
tom04さん、ありがとうございます。 いつもすごいです！ 完ぺきでした。一つ質問なのですが、 購入商品の件数を、種類ごとにカウントして どこかに表示することはできるのでしょうか。 グミ 4件 タンブラー 6件 上記のような表示にしたいのですが。 よろしくお願いします。
- 1
- 2
関連するQ&A
- SUMPRODUCT関数で困っています
エクセル2007にて、SUMPRODUCT関数を使ってデータ集計をしたいが 下記の現象で困っています。 SUMPRODUCTの特徴として参照先(sheet1)の指定範囲 「($B$2:$E$6)の範囲を($B$2:$G$20)」を変更すると SUMPRODUCT関数がn/aになり値が表示できない。 (やりたいこと) sheet1に元データが入力されています。 A B C D E 1 NO 社名 商品 購入日 金額 2 01 A商事 肉 1/3 50円 3 02 B電気 野菜 1/10 70円 4 02 B電気 肉 1/12 50円 5 03 C工業 魚 1/20 60円 6 03 C工業 肉 1/30 50円 sheet2はsheet1より必要なデータを、NOをキーにして、 社名・商品・金額を VLOOKUP関数にて取ってきて表示しています。 A B C D E 1 NO 社名 商品 金額 2 01 A商事 肉 50円 3 02 B電気 野菜 70円 4 02 B電気 肉 50円 5 03 C工業 魚 60円 6 03 C工業 肉 50円 6 03 C工業 鉄 90円 sheet3はsheet2より必要なデータを、NOをキーにしてSUMPRODUCT関数にて取ってきて表示しています。 次の式をB1に入力しています (=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2<>"鉄")*(sheet2!$D$2:$D$6=D2)) A B C D E 1 NO 金額 2 01 50円 3 02 120円 4 03 110円 sheet3はsheet2より必要なデータを、NOをキーにしてSUMPRODUCT関数にて取ってきて表示しています。 次の式をB1に入力しています (=SUMPRODUCT((sheet2!$A$2:$A$6=A2)*(sheet2!$B$2:$B$6=B2"鉄")*(sheet2!$D$2:$D$6=D2)) A B C D E 1 NO 金額 2 03 90円
- 締切済み
- その他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関数について いつもお世話になっています。 以前こちらでSUMPRODUCT関数を教えていただき大変統計がしやすくなり、 仕事が効率化しました。ありがとうございます。 ところで、SUMPRODUCTだと思うのですが、計算式がうまく組み立てられないのでご質問させていただきます。 A列に部門名、B列に実施日が入力されているエクセルシートがあり、 部門ごとの実施件数を出したいのです。 SUMPRODUCT（（A2:A100="○部門"）＊（CAUNTA(B2:B100)）） としてみましたがうまくいきません。 たぶん ＊（CAUNTA(B2:B100)） の部分がいけないのだろうとは思うのですが、どう変化させればいいのでしょうか？ 関数の選択が間違っているのかもしれない、とは思うのですが、適切なものが分かりません。 ご教授お願いします
- ベストアンサー
- オフィス系ソフト
- データベースとデータの抽出について
データベースとデータの抽出について エクセル2003で以下のような、表を作っています。 データは現在、A列に10000万件程度入っています。 4列目の購入日付データの年、月、日がそれぞれのセル（D2、E2、F2） に分割入力されています。 このデータベースから、氏名と購入日付を基準キー にして、顧客NOと購入日付（1つのセルに結合）、 購入商品を抽出し、シート2に転記していきたいです。 抽出方法は、シート2に以下のような形で検索 表示したいです。 （シート2） 氏名（B1に表示）→C1に入力 抽出開始年月日（D1に表示）→年はE1、月はG1、日はI1に入力 抽出終了年月日（D2に表示）→年はE2、月はG2、日はI2に入力 検索後、表示される顧客NOと購入日付、購入商品は 同じシート2の以下のセルに表示したいです。 顧客NO（L1に表示）→L2から下に表示 購入日付（M1に表示）→M2から下に表示 購入商品（N1に表示）→N2から下に表示 したいです。 日付が分割入力されているため、抽出がうまくできません。 氏名、または名字だけでうまく検索できるコードはあるでしょうか。 どうぞよろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数とROUND関数を一緒に
幾度どなくお世話になっております。 ４月からEXCEL中心の仕事になりましたが、まだまだ超初心者です。 今回は、ブック中の結合_OKシートのPセル列の文字がブック中の別シートのA列に記載している文字と一致したらその数分の結合_OKのAOセルに記載している金額を合計した値を別シートのB列に、整数値で表示させようとしています。 結合_OKのAOの金額のセルの数字は小数点3桁表示になっています。 ROUND関数を式に入れなければ、以下の式で、エラーは出力されず、別シートのB列に金額は出せましたが、この式にROUND関数を入れる為に色々とやってみましたが、エラーが表示されてしまっています。 =SUMPRODUCT((結合_OK!$P$3:$P$1000=$A2)*結合_OK!$AO$3:$AO$1000) 以下ではエラーが出ます =ROUNDDOWN(SUMPRODUCT((結合_OK!$P$3:$P$1000=$A2)*結合_OK!$AO$3:$AO$1000)) 因みに、結合_OKシートのPセル列には、空白のセルが混在している為、ブック中の別シートのA列に記載している文字は、手作業で作成しています。 別シートのA列、B列は以下のような記載です。 ID 合計金額 22NM5001 29470 22NM5002 11660 22NM5005 12045 22NM5007 2200 22NM5011 5500 22NM5012 16280 大変、お手数ですが、SUMPRODUCTとROUND関数を一緒に記載する方法を教えて頂けると大変助かります。 また、上記の式についても、SUMPRODUCT関数が最適なのか分からず使っています、こちらについてもご教示頂けると大変幸いです。 分かりずらい説明ではありますが、よろしくお願いいたします。
- ベストアンサー
- Excel（エクセル）
- SUMPRODUCT関数の使い方
いつもお世話になっております。SUMPRODUCT関数の使い方が解らず困っています。 どなたか、教えてください。 I列（3～1034）に101と入力されており、F列（3～1034）に○と入力されているセルの数を数えたくて、 SUMPRODUCT関数を使いましたが、答えが0になるか、NAME？になるかで、正答が表示されません。 関数が苦手なので、SUMPRODUCTは、関数の表示ダイアログボックスを使って作成しています。 すると、配列1、配列2、配列3と出てくるので、 配列1に I3：I1034＝101 配列2に F3：F1034＝○ （絶対参照して）入力すると =SUMPRODUCT($I$3:$I$1034=101,$F$3:$F$1034=○) となり、答えは ＃NAME？ と出てしまいます。 また、やり方が解らないのですが、他の質問の回答をみて、SUMPRODUCT関数を手入力し、 =SUMPRODUCT（($I$3:$I$1034=101）*（$F$3:$F$1034=○)） と修正入力すると、答えは 0 になってしまいます。 （だいたい、どうして * が出てくるのかも理屈が解りません。） 101や○を””でくくっても、うまくいきません。 もはや何がどう良くないのかわかりません（泣） どなたか、素人の私にも解るようにご解説お願いいたします。
- ベストアンサー
- オフィス系ソフト
- 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関数
エクセル2003を使っています。 sumproduct関数で条件付の合計計算をしたいのですが 計算できる列とできない列があります。 計算可能な書式をできない列にコピーしても変わらなくエラーになります。（＃VALUE！） 試しに同じ数値をCSVで保存したシートでは計算できました。 考えられる要因と対策は何でしょうか。
- ベストアンサー
- その他MS Office製品
- SUMPRODUCT関数について
SUMPRODUCT関数を使いA列の対象値（LEFT関数を使い左一文字を検索対象としC列の該当値の合計を集計していますがLEFT関数で検索対象を複数にする場合はどのようにすればいいのでしょうか？ 下記の場合はsとdを検索対象としたいのですが。 宜しくお願いいたします。 A B C 1 ｓｂ 20 2 ｓｃ 30 3 ｓｄ 40 4 ｄｂ 50 5 ｄｃ 60
- ベストアンサー
- Excel（エクセル）
- SUMPRODUCT関数で0(ゼロ)と
はじめまして。arumikan091と申します。 （Excel初心者です(^^;） ExcelのSUMPRODUCT関数について質問があります。 A B (1) 2 0 (2) 2 4 (3) 2 (空白) (4) 2 3 (5) 2 0 (6) 3 4 (7) 3 (空白) というシートの中で、 "A列が2で、かつB列が0の個数を数える" ということをしようと思い =SUMPRODUCT((A1:A7=2)*(B1:B7=0)) と入力したのですが、 これだとB列の0(ゼロ)と一緒に""(空白)も 数えられてしまうようです。 (上記シートだと式の期待値は2なのですが、 実際値は3が返ってきます) このような場合、0(ゼロ)だけを数えられる ようにするにはどのようにすればいいか 教えていただけたら幸いです。 一応、自分でも少ない知識の中で色々トライしてみて、 =SUMPRODUCT((A1:A7=2)*(B1:B7=0))-SUMPRODUCT((A1:A7=2)*(B1:B7="")) のように式を入力するとちゃんと0(ゼロ)だけを 数えてくれるようなのですが、さらに簡単な 方法がありましたら教えてください。 Windows ME、Excel2000です。 ※１ B列単独で =COUNTIF(B1:B7,0)とするとちゃんと 0(ゼロ)だけを数えてくれます。 ※２ 一応既存のQ/A集で同じ質問がないか探しましたが、 (「SUMPRODUCT 空白セル」のANDで） 同様、類似の質問が既にありましたら 申し訳ありません。 長文失礼しました(m_ _m
- 締切済み
- Windows Me
補足
ありがとうございます。 すごくきれいなレイアウトです。 勉強して、作ってみます。