- ベストアンサー
VBAでSUMPRODUCTを使用して値を取得する方法について
- VBAのSUMPRODUCT関数を使用して、特定の条件に合致する値を取得する方法について説明します。
- 質問の例では、D列が「東京」となっており、BH列が「みかん」となっているAX列の数字の合計値を求めたい場合のコードが記載されています。
- ただし、(2)のコードではエラーが発生しており、括り方や演算子の使用方法に誤りがある可能性があります。
- みんなの回答 (1)
- 専門家の回答
質問者が選んだベストアンサー
関連するQ&A
- VBA SUMPRODUCTについて2
A B C 東京 みかん 200 大阪 りんご 150 愛知 りんご 200 愛知 みかん 170 東京 みかん 190 390(←出したい値:atai) 【やりたいこと】 A列が「東京」で、B列が「みかん」のC列の合計を出したいです。 つまり、200+190 = 390 下記、書いてみたのですがatai(値が0)になります。 ※本来は390と出したいところです。 何が間違っているのでしょうか? --------------------------- Sub monthResult2() Dim ans As String ans = "東京" atai = Evaluate("SUMPRODUCT((A1:A5= ""& ans &"" )*(B1:B5= ""みかん"") * C1:C5)") Range("C7") = atai End Sub ------------------- ""& ans &"" を ""東京"" とすれば正しい答え「390」が出てきます。 ans は変数で使いたいので固定で指定したくありません・・。 どうぞよろしくお願い致します。
- ベストアンサー
- SE・インフラ・Webエンジニア
- VBA SUMPRODUCTについて3
A B C 東京 みかん1 200 大阪 りんご3 150 愛知 いちご2 200 東京 みかん2 170 東京 いちご1 190 560(←出したい値:atai) 【やりたいこと】 A列が「東京」で、B列が「みかん」または「いちご」の文字列を含む C列の合計を出したいです。 つまり、200+170 +190 = 560 下記、書いてみたのですがatai(値が#VALUE!)になります。。 ※本来は560と出したいところです。 何が間違っているのでしょうか? --------------- Sub monthResult3() Dim ans As String ans = "東京" atai = Evaluate("SUMPRODUCT((A1:A5= """ & ans & """ )*((B1:B5 Lile ""*みかん*"")+(B1:B5 Lile ""*いちご*"")) * C1:C5)") Range("C7") = atai End Sub
- ベストアンサー
- SE・インフラ・Webエンジニア
- 【SUMPRODUCT】について
下記リストと条件で価格を算出したいのですが、A列の「7」以外の数字まで反映された値がかえってきてしまいました。適切な関数を使用しているかも不明です。 アドバイスのほどお願いいたしますm( _ _)m --------------------------------------------------------------- セル A列 B列 C列 D列 E列 1 5 A リンゴ店 TEL \100 2 5 B ミカン店 FAX \200 3 7 C リンゴ店 TEL \100 4 7 A ミカン店 FAX \200 5 7 B リンゴ店 TEL \100 6 7 C ミカン店 FAX \200 --------------------------------------------------------------- <条件> A列 「7」 B列 「B」と「C」 C列 「ミカン」を含む D列 「FAX」以外 <作成した数式>(かえってきた値は上の条件とは相反したデータで、恐らくA列の「7」以外の数字も含んでいるようです・・) =SUMPRODUCT((A$2:A$10=7)*(B$2:B$10="B")*(B$2:B$10="C")*(E$1:E$10)) +SUMPRODUCT((A$2:A$10=7)*ISNUMBER(FIND("ミカン",C$1*C$10))*(E$1:E$10))+SUMPRODUCT((A$2:A$10=7)*ISERROR(FIND("FAX",D$1:D$10))*(E$1:E$10)) 以上、質問が長くなってしまい申し訳ありません。 ご教授のほどどうぞ宜しくお願いいたします。
- 締切済み
- オフィス系ソフト
- 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関数について
対象は D10からK39 リストは AD1からAJ1まで で、 各列にリストの文字のある数をあらわしたい。 以前、(例えばDの列) 40行目に =SUMPRODUCT(COUNTIF(E10:E39,$AD$1:$AJ$2)) でどうかと教えてもらい、うまくいってはいますが、理解ができていません。 今後のために =COUNTIF(D10:D39,$AD$1:$AJ$2) ではなぜダメなのかを理解したいんですが?
- ベストアンサー
- オフィス系ソフト
- エクセルVBA
1ブック A列 B列 みかん 105 りんご 150 バナナ 198 2ブック A列 D列 みかん 105 りんご 150 バナナ 198 2ブックのA列が1ブックのA列と同じ値なら1ブックのB列の値を2ブックのD列に反映させたいのですが、どうしたら良いでしょうか。 A列の値を基準にB列だけでなく、他列も抽出したいのですが…。 宜しくお願いします。
- ベストアンサー
- Excel(エクセル)
- VBA 1年間売上トータルをマクロで集計
A(エリア) B(日付) C(商品) D金額 東京 2009/09 みかん 210 東京 2011/09 みかん 210 大阪 2011/06 りんご 150 東京 2011/07 りんご 200 東京 2011/08 みかん 170 大阪 2011/10 みかん 140 東京 2011/11 みかん 110 東京 2012/03 みかん 190 大阪 2012/04 りんご 200 東京 2012/05 りんご 220 【やりたいこと】 1年間のりんごの売上とみかんの売上のトータルをマクロで 計算したいと思っています。 一年間の区切りは、期初(スタート)月:6月 締め月:5月です。 ただし、1年間といっても指定した日付までのトータル金額を出したいのです。 例1)InputBoxで「2012/04」と入力した場合は B列の「2011/06」~「2012/04」かつC列がりんごの金額の総合計を出す。 というようなイメージです。 例2)InputBoxで「2011/06」と入力した場合は B列の「2011/06」~「2011/06」かつC列がりんごの金額の総合計を出す 例3)InputBoxで「2010/02」と入力した場合は B列の「2009/06」~「2010/02」かつC列がりんごの金額の総合計を出す 恐れ入りますがどたなかご教示頂ければ幸いです。 どうぞよろしくお願い致します。
- ベストアンサー
- SE・インフラ・Webエンジニア
- SUMPRODUCT関数の値としての#NUM!
Sheet2のB2に、 =SUMPRODUCT((A2=Sheet1!A:A)*1) というような式を入れると、#NUM!が出ます。 Sheet2のA2と一致する、Sheet1のA列の値の個数を求めるにはどうすればいいでしょうか(もっといえば、一致する行のK列の合計を求めたい)。 うえの簡単な式のどこがまちがってるのかわからなくて困ってます。
- ベストアンサー
- オフィス系ソフト
- 旅費を二重払いしないための関数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),"確認","")) よろしくお願いいたします。
- ベストアンサー
- Excel(エクセル)
- vbaにvlookupを組み込む
お世話になります。 エクセルのマクロにvlookupを組み込みたいのですが,エラーが出てしまいます。 用意したものは「顧客名簿」というシートで,A列に顧客番号(6ケタの数字),B列に顧客名が入っています。C列以降はその他のデータが入っています。 「トップメニュー」というシートにあるマクロボタン「顧客抽出」を押すと,インプットボックスが現れます。これに顧客番号を入力すると,メッセージボックスが現れます。 この時のメッセージボックスに,インプットボックスに入力された番号により「顧客名簿」から顧客名を取り出し,「○○さんのデータを表示しますか」としたいのですが,ここでエラーが出ます。(○○は,「顧客名簿」のB列にある名前です) 実行時エラー 1004 WorksheetFunctionクラスのVLookupプロパティを取得できません。 組んであるマクロは以下の通りです。 Sub 顧客抽出() Dim ans As String, ans2 As String ans = InputBox("顧客番号を入力", "入力", "") If ans = "" Then MsgBox "顧客番号が空白です" & vbNewLine & _ "処理を中止します", vbOKOnly Else If WorksheetFunction.CountIf(Worksheets("顧客名簿").Range("A1:B5000"), ans) = 0 Then MsgBox "顧客番号が登録されていません", vbOKOnly Else ans2 = WorksheetFunction.VLookup(ans, Worksheets("顧客名簿").Range("A1:B5000"), 2, False) If MsgBox(ans2 & " さんのデータを表示しますか", vbYesNo) = vbYes Then Sheets("顧客情報").Select Range("D4").Value = ans Else MsgBox "処理を中止します", vbOKOnly End If End If End If End Sub 途中にある ans2 = WorksheetFunction.VLookup(ans, Worksheets("顧客名簿").Range("A1:B5000"), 2, False) の部分でエラーが出るのですが,解決策が見当たりません。 おそらくRange("A1:B5000")の部分に不具合があると思うのですが,どう対処すればいいでしょうか。 詳しい方,ご教授願います。
- ベストアンサー
- Excel(エクセル)
お礼
早速のご回答ありがとうございます。 エラーは出なくなったのですが、msgBox(atai)で値を見ると「0」となり 本来、欲しい数字が出てきません。。。 変数のk や ans は値が取れております。 そもそもSUMPRODUCTには変数が使えず、(1)のように じかに値や検索値を指定しなければならないのでしょうか?? VBA初心者で申し訳ございません。
補足
色々と、試してみたのですが、やはりatai = 0 となってしまいます。。 頂きました書式で、以下部分を""東京""とした場合はちゃんと値が 出てきました。 "" & ans & "" ということで、略式な、もう1つ質問を作らせて頂きました。 http://okwave.jp/qa/q7418746.html どうぞよろしくお願い致します。