[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『SUMPRODUCT関数を使用した複数行データ（値データ有）の合計』(CM）
標記の件、色々と試しましたが上手くいかず、
質問させてください。
A列 B列 C列
４月 ５月
りんご 3000 2000
みかん 4000 1000
４月(*) ５月(*)
りんご 7000 3000
すいか 3500 1500
A列の”りんご”の４月と５月のデータを合計して表示させたいです。
Sumproductを使用して以下の数式を作りましたが、
列の途中に文字(*の部分)を入れるとエラーになってしまいます。
解決方法をご教授いただけないでしょうか。
=SUMPRODUCT(($A$4:$$$10=$C$4)*1,$B$4:$C$10)
４月と５月のりんごの値段を合計した金額を出力したいです。
（3000+7000+2000+3000＝15000）
当例題では４月と５月のみの合計ですが、条件によって
４月から７月まで、７月から９月までの合計などを
算出する必要がありますので、
sumifの足し算だと式が非常に複雑になってしまいます。
sumproductの式をベースにしてこの条件の部分を
offsetやindirectを使用して変更して計算できるファイルが
作れればと思っていますが、
何かいい方法があればご教授頂けたらうれしいです。
それさえ出来れば、sumproductにはこだわりません。
なお、excel使用バージョンは2003です。
(CM）
A B C D 1 4月 5月 6月 2 りんご 3000 2000 1000 3 みかん 4000 1000 2000 4 4月 5月 6月 5 りんご 7000 3000 4000 6 すいか 3500 1500 2500
1行目と4行目の月の部分は4、5と入力して表示形式で 0"月" にしておき =SUMPRODUCT((B1:D1>=4)*(B1:D1<=5)*(A2:A6="りんご")*B2:D6)
でどうでしょうか？ （se_9） 2014/02/05(水) 07:50
・実際の表は月が”Apr”等と名称になっており、
Apr自体が他の式（VLOOKUP）などのキーになる値になっているため、
動かすと大工事になってしまいそう。
（毎月集計範囲が変わるため、
ドロップダウンリストで月の名称を選択した後に
集計範囲が変わる関数をくんでいる。）
ちなみに、SUMPRODUCTの関数が良くわかっていなくて申し訳ないのですが、
ご指摘頂いた式はどういう意味になるのでしょうか。
=SUMPRODUCT（A2:A6="りんご")*B2:D6)
だけでも同様の内容が集計されるのかなぁと思ったのですが、
((B1:D1>=4)*(B1:D1<=5)をあえて頭につけることでどのような効果がありますでしょうか。
申し訳ありませんが、ご教授頂けたら大変たすかります。
（CM） 2014/02/05(水) 08:24
＞((B1:D1>=4)*(B1:D1<=5)をあえて頭につけることでどのような効果がありますでしょうか
=SUMPRODUCT((A2:A6="りんご")*B2:D6) だと6月のりんごの金額まで合計されてしまいますよね。 4月から5月までのりんごの合計金額を求めるわけですから(B1:D1>=4)*(B1:D1<=5)と したわけです。
ちなみに ＞実際の表は月が”Apr”等と名称になっており
これはただ単に Apr と入力してあるのでしょうか？それとも 2013/4/1 と入力して 表示形式で mmm にしてあるのでしょうか？ （se_9） 2014/02/05(水) 09:05
これはただ単にAprとして入力してあります。
（表示形式で設定しているわけではない）
このファイルを使用するのが、
私ではない他の方です。（人のこと言えませんが、エクセル知識は少ない）
月度のセルの表示形式をいじってしまうと、
実際に使用したときにシリアル値ではなく文字列を入力してしまい、
情報が上手く読み取れずにファイルが上手く使えない可能性が出てきます。
またSumproductもその回避法ですと
別の空白行に何か文字を知らずに入力してしまって
sumproduct関数がエラーになってしまったりということも考えられるので
リスクを出来るだけ減らしたいなと思っています。
上記の理由もあり、表示形式等でエラーを回避する形よりは、
数式を読めば内容がわかるという、わかりやすいファイルを作れればと。。
せっかくご提案いただいているのにすみません。
初めに書いたsumproductの式は*1等を入れることで文字列が入っても
計算されるようにしているのですが、
合計列が１列だけなら上手くいきますが
２列以上になるととたんにエラーになってしまいます。
＞ =SUMPRODUCT((A2:A6="りんご")*B2:D6)
＞だと6月のりんごの金額まで合計されてしまいますよね。
＞4月から5月までのりんごの合計金額を求めるわけですから(B1:D1>=4)*(B1:D1<=5)と
＞したわけです。
ありがとうございます。
その場合、=SUMPRODUCT((A2:A6="りんご")*B2:C6)
という風に後ろのD6をC6に移せばいいのかなとも思ったのですが、
4-5月までの合計などと条件を設定する際に設定がしやすそうだからということで
ご提案頂いたということでしょうか？有難うございます。
ただ、実際の合計数値の際に、
数値が4月-3月までの合計となり、
累計期間（4月から1月までの10ヶ月、等）によっては条件式がまた複雑になるため、
単純に列を指定することで合計が出来たらなぁと思っています。（Offset等）
ご回答感謝いたします。
（CM） 2014/02/05(水) 09:22
SUMの配列数式ではどうですか？
[A] [B] [C] [D] [E] [F] [G] [H]
[1] ______ Apr May Jun Jul Aug Sep Oct
[2] りんご 100 200 300 400 500 600 700
[3] みかん 400 100
[4] Apr May Jun Jul Aug Sep Oct
[5] りんご 700 300 400 500 600 700 800
[6] すいか 350 150 250 350 450 550 650
[7]
[8] 集計列 Apr Oct
[9] 1 7
[10] りんご 6800
[11] すいか 2750
B8に集計開始の月名、C8に集計最後の月名
B9=MATCH($B$8,$1:$1,0)-1
C9=MATCH($C$8,$1:$1,0)-1
A10に集計させたい果物名
B10=SUM(IF($A$1:$A$6=$A10,OFFSET($A$1:$A$6,,$B$9,,$C$9),0))
といれて、Ctrl+Shift+Enterで配列数式
数式バーに
{=SUM(IF($A$1:$A$6=$A10,OFFSET($A$1:$A$6,,$B$9,,$C$9),0))}
と{}で囲まれた数式になっていればOKです。
（稲葉） 2014/02/05(水) 10:41
＞Sumproductを使用して以下の数式を作りましたが、 ＞列の途中に文字(*の部分)を入れるとエラーになってしまいます。 ＞解決方法をご教授いただけないでしょうか。 ＞=SUMPRODUCT(($A$4:$A$10=$C$4)*1,$B$4:$C$10) ↑ C4に何が入っているんですか？
文字の問題じゃなくて、配列の要素数が第一引数と第二引数間で整合してないのが原因ですよ？ ※SUMPRODUCTは、各引数内の要素が文字だったら無視してくれます。
これで試してみてください。 =SUMPRODUCT(($A$4:$A$10=$A$4)*COLUMN($B$4:$C$10)^0,$B$4:$C$10)
（半平太） 2014/02/05(水) 11:12
＞稲葉さま
頂いた式を参考にして、下記の数式を作りましたが、
（すみません、こちら当方の実ファイルデータ）
配列数式を作る前の段階でエラーになってしまいました。（VALUEのエラー）
=SUM(IF(Sales!$C$14:$C$160=$AB11,OFFSET(Sales!$C$14:$C$160,,$AC$2,,$AC$3),0))
・$AC$2、$AC$3にはそれぞれ取り急ぎ数字の5、6を入力しています。
OFFSETを上手く理解できていないため、ここが問題でしょうか。
・わざわざ月名で検索する式を使っていただいて大変申し訳ありませんが、
検索範囲の中にAprが３つ位あるのです。
（計画のApr,実績のApr,前年度のApr）
そのため、もし開始列数と終了列数のみがわかればOFFSETを使えるやり方を教えて
いただけますでしょうか。
なお、同じようなやり方で、下記の式であれば上手くデータが飛んできますが、
配列の形式をいちいち変更しなければいけなかったため（※）、上手く使用できませんでした。
これを上手く加工することで対処可能でしょうか？
=SUM(SUMIF(Sales!$C$14:$C$160,$AB11,OFFSET(Sales!$J$14:$J$160,,{0,1,2})))
（※）{0,1,2｝、{0,1｝、等
＞半平太さま
どうもありがとうございました。
初めに質問した関数が上手く働かなかった原因がなんとなくわかりました。
（配列１つ目は１行しかデータがないのに２つめは２行にわたっているから、ということでしょうか？）
りんごのテストデータではこちらで上手く行ったのですが、
実際のデータを使用しようとした際に、
OFFSET関数をどのように組み合わせたら上手くいきますでしょうか。（キーは基準列からの列数）
余り式を複雑にしたくないのですが、COLUMNを使うとその中にもOFFSETを入れなければなりませんか？
OFFSET、SUMPRODUCT,MATCH関数などの理解が不十分なのに、
難しい関数を使おうとしてお手数をお掛けして申し訳ありません。。
ファイル作成が上手くいくと大変助かります。
（CM） 2014/02/05(水) 12:11
＞・実際の表は月が”Apr”等と名称になっており、 ＞ Apr自体が他の式（VLOOKUP）などのキーになる値になっているため、 ＞ 動かすと大工事になってしまいそう。
と
＞ 検索範囲の中にAprが３つ位あるのです。
矛盾してない？ 一意の値じゃないのに、「キーになる値」にはなりえないと思うのですが。 （稲葉） 2014/02/05(水) 12:56
ご確認有難うございます。
別シートにVlookでマスタを作って、
そこにある行数でキーにしています。
（例）
（A)マスタシート
実績 計画 前年
Apr １ １５ ２９
May ２ １６ ３０
（行数を記載）
（B)作業シート
１．ある基準セルにてAprを選択
２．データセルには自動的に実績、計画、前年の
Aprのデータが転記（Vlook,Sumif、Offsetなどの式を組み合わせて記載）
※このデータセルには単月データ、累計データなど色々な形のデータを
転記する必要があります。
今回はこの部分で毎回選択月が変わる箇所の式を作る必要があり、
質問させて頂いています。
（C）にデータベース用シートがあり、
こちらにすべてのデータが入っています。
（実績、計画、前年）
AのマスタシートでCのキー列（りんご、等が書いている列）からの列数を定義しています。
Bの作業シートでAのマスタシートにある行数をもとに（C）のデータベースの数字を
集計しています。
（CM） 2014/02/05(水) 13:16
よく読んでなかったけど、 ＞配列数式を作る前の段階でエラーになってしまいました。（VALUEのエラー） これは当たり前ですよ？ 配列数式用の式なんですから。 配列数式にしない限りエラーです。
実データもないし、VLOOKUPで「マスタを作る」とかよくわからないのでこれ以上私には 答えられませんが…
（稲葉） 2014/02/05(水) 15:00
＞（配列１つ目は１列しかデータがないのに２つめは２列にわたっているから、ということでしょうか？） その通りです。 ※第１引数内で配列同士を掛け合わせるのとは、意味が違います。(念の為)
＞OFFSET関数をどのように組み合わせたら上手くいきますでしょうか。（キーは基準列からの列数）
＞ 余り式を複雑にしたくないのですが、COLUMNを使うとその中にもOFFSETを入れなければなりませんか？
いえ、こんな→{1,1,・・・,1｝配列が作れるなら何でもいいです。
＞これを上手く加工することで対処可能でしょうか？
＞=SUM(SUMIF(Sales!$C$14:$C$160,$AB11,OFFSET(Sales!$J$14:$J$160,,{0,1,2})))
それに代わるものが作りたいなら、こんなのでいいです。
=SUMPRODUCT(SUMIF(Sales!$C$14:$C$160,$AB11,OFFSET(Sales!$J$14:$J$160,,COLUMN($A1:INDEX(1:1,3))-1))) ↑ この「3」をどこかしらのセルから見繕って作るだけです。
（半平太） 2014/02/05(水) 16:36
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.