- ベストアンサー
SUMPRODUCT関数の検索範囲について
私は公共施設に勤めているPC超初心者で、今、エクセル2000を使って日々提出される施設利用申請書の内容を集計するシステムを作ろうとしています。 申請日が○○年△△月◇◇日の場合、表の日付欄に○○△△◇◇、利用室名や時間帯などを数値化して入力・蓄積し、別シートに集計したいことがらの集計表を作成しておいて、集計の際に任意の期間を設定すれば、その表に自動的に集計されるようにしたいのです。 そこで、SUMPRODUCT関数の出番なのですが、検索範囲になる最初のデータ行は超初心者の私でもわかるのですが、最後のデータ行を指定するにあたって入力済の最後のデータ行が指定されるようにする方法はないでしょうか。 ご教示の程、よろしくお願いします。
- kenkouundo
- お礼率80% (29/36)
- オフィス系ソフト
- 回答数2
- ありがとう数2
- みんなの回答 (2)
- 専門家の回答
質問者が選んだベストアンサー
SUMPRODUCT関数で列全体を指定することはできなかったはずですので、仮に1行目が項目名だとすれば、その1行目を除いて たとえば =SUMPRODUCT((A2:A65536="A")*(B2:B65536=1)) のようにすれば、最終データまで自動的に反映します。
その他の回答 (1)
- imogasi
- ベストアンサー率27% (4737/17068)
念のために言いますとSUMPRODUCT関数は本来検索に使うものではありません。 ただし2003以前では、SUMIF関数の複数条件版の関数として利用されることがあります。2007ではSUMIFSが出来ました ので使わなくて済みます。 だからSUMPRODUCTの特殊な使い方です。 ーーー だから条件的なものを含めて、実例を挙げて質問すべきです。 ある1条件についてA列全体とかを対象に考えたいのなら、余分目に行を指定しておいても、それが害になることはないでしょう。 だからこの質問はつまらないこものになる。 だから65536行目を指定してもよいが、下方行に別表が有るとか 計算時間に神経質になるなら別ですが、あまり無いケースでしょう。 ーーー データ最終行を求めるなら Googleで「エクセル関数 データ最下行」で照会すれば http://www.kotaete-net.net/Default.aspx?pgid=14&qid=120491492468 など、ここに聞かなくてもすぐ出てくるのでは。 ーー A列以外の式でA2以下のデータの最終行は =COUNTA(A:A)+1 で判るし、それがわかれば、それを使ってINDEX関数で範囲指定 出来る。 途中で空白行もある場合の最終行を求める式もそこに載っています。 ーー VBAでユーザー関数を作る方法もある。 標準モジュールに Function saikag(A) Application.Volatile saikag = Range(A & "65536").End(xlUp).Row End Function これは有名な常套手段です。 これで =saikag("a")と入れる。この場合は途中の空白行も含めて最終行です。
お礼
ご指導有り難うございました。質問の仕方さえわからないのに不相応なシステム作りをしているみたいで申し訳ありませんでした。 具体的にご指導いただいた方法を理解するには大変な時間が必要でしょうがじっくり勉強してみます。また、宜しくお願いします。
関連するQ&A
- エクセル「SUMPRODUCT関数」で困っています
SUMPRODUCT関数 初心者です。 項目数15くらい、約10000件のデータベースから、3項目の条件でSUMPRODUCT関数を使って20種別×15種別の表を作成してみようと思いました。 …が、結果すべて0になってしまいます。 SUMPRODUCT関数の使い方(考え方)はあっていると思います。(小さい表で使ってみると正しく計算されるため) たしかに、SUMPRODUCT関数は沢山計算しているので、大きな?表を集計するのに向かないのでしょうか? そのあたりがわかりません。 ただ、ピボットテーブルで同じものを作成すると、さくさく完成します。 こんな漠然とした質問でお答えいだけるのか不安ですが、どなたかお詳しい方よろしくお願いします。
- 締切済み
- Windows 7
- SUMPRODUCT関数の日付関数で困っています
SUMPRODUCT関数でコードが一緒で月も一緒のものについて集計したいと思っています。 添付の写真の【実績】の表は、販売した実績です。 【集計】はそれぞれのコード(商品)の月ごとの集計をしたものです。 集計の"201401"というのは表示形式を変えて日を変えて表示していますが、もとは "2014/1/1"とその月の初めの日を入力しています。 質問の本題ですが、コードが同じで、【実績】の販売日の“年月”と【集計】の年月が同じものを 集計させるには日付をどのようにすればいいでしょうか?
- ベストアンサー
- Excel(エクセル)
- VLOOKUP SUMPRODUCT
初心者なのですが、VLOOKUPとSUMPRODUCTの使い方を知りたいです。 表 A列 B列 行1 サーバ1 5 行2 サーバ2 4 行3 サーバ3 3 行4 サーバ1 2 行5 サーバ3 2 という表でサーバ1を検索後、その合計値(7)を求めたく思います。 WEBや参考書を見たところVLOOKUPとSUMPRODUCTの組合せで出来るようなのですが、うまくいきませんでした。 =SUMPRODUCT(VLOOKUP(C6,A1:B5,2,FALSE)) 正しい式を教えてご教授いただきたく思います。
- ベストアンサー
- オフィス系ソフト
- 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製品
- エクセルの関数がわかりません。ご教示ください。
公共の施設に勤めるPCの超初心者ですが担当する施設の予約状況表の作成をエクセルでスピードアップできないかと考え、関数のガイド書を片手に取り組んでいますが行き詰まって困っています。 まず、勤務する施設は月間約530件くらいの利用があるので清掃担当者が空きの間隙を縫って作業に明け暮れているので、スピードアップできれば少しくらい手助けができると思うのです。 施設予約状況表は半月分ずつ1日1枚の紙を使って縦軸に室名、横軸に時間帯(午前・午後・夜間)でなる単純な表でそれぞれの枠内には行事名や利用者名等が入ります。それを作るのに施設が採用している施設管理システムから一定期間の予約状況をエクセルの表にして取り出せることから別にエクセルを立ち上げておいて貼り付けました。 そして、貼り付けた表のうち施設内の諸室名(13室)と利用時間帯(午前・午後・夜間・午前+午後・午後+夜間・終日の6時間帯)を数値化、利用日の数値と利用者名と行事名の一覧表ができるようにしました。 私のもくろみは、別に用意する日々の予約状況表にVLOOKUP関数を埋め込んでおいて自動的に予約状況表が作成されるミニシステム的なファイルにすることで、ここまでできればセルの結合作業が残るだけですから大幅なスピードアップになると思うのですが、VLOOKUP関数で検索値となる数値、すなわち例えば利用される諸室名(B列)は「8」、利用日(C列)は「12」、利用時間帯(D列)が「5」、データが入っている行が「21~120」という場合、この3項目を全て満たすデータがどの行にあるかを探す関数がわからず、これだけでハンドブック片手に寝る時間も惜しんで3日間の試行錯誤していますがうまくいきません。文章がまずくて質問内容がよくわからないといわれるかもしれませんが宜しくご教示のほどお願いします。ーペコリー
- ベストアンサー
- オフィス系ソフト
- エクセル 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を使って、件数を出しています。 A B(抽出条件を入力) 1都道府県 ● 2職業 ● 3性別 ● 4購買日 ● A B C D 11 都道府県 職業 性別 購買日 12 東京 会社員 男 20080901 13 ・ ・ ・ ・ 14 ・ ・ ・ ・ 15 ・ ・ ・ ・ 上記のような表を作り、A11:E100までデータが入っています。 B1:B5に条件を入れて、C1:C4に =SUMPRODUCT(($A$12:$A$100=$B$1)*($B$12:$B$100=$B$2)*($C$12:$C$100=$B$3)*($C$12:$D$100=$B$4)*1) で件数をカウントしています。 抽出条件をすべて指定すると、件数が出てきますが、特に指定しないで 空欄のままで件数をカウントする方法はないでしょうか? (たとえば、職業を絞らないでほかの条件だけで抽出するとか) 何か解決策を教えてください。
- ベストアンサー
- オフィス系ソフト
- 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 と OFFSET
いつもお世話になります。 SUMPRODUCT関数で集計したいのですが、OFFSET関数を組合せてA1に関数を入力し、右にひっぱるだけで36ヶ月分を集計したいと思い、下記の計算式を入力したのですが#VALUE!になり困ってます。 A1=SUMPRODUCT((OFFSET(data!$A$1,1,COLUMN(A1)*3-3,99,1)=$G$1)*(OFFSET(data!$B$1,1,COLUMN(A1)*3-3,99,1)=$H$1),(OFFSET(data!$C$1,1,COLUMN(A1)*3-3,99,1))) dataシートには、A列:商品CD、 B列:営業所CD、 C列:売上金額 の3列のデータが、36か月分108列あります。 集計するシートのG1に商品CDを、H1に営業所CDを入力すると、A1~A36に集計結果を返したいのですが、教えてくださいませんか
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数について
指定された条件にあったセルの数を数えたいのですが、うまくいきません。 具体的に言いますと、競馬のデータなのですが、 セルG7:G33にコースの距離(1400,1600,2000など) L7:L33に着順(1,2,3,4,など)が入力されています。 ちなみにどちらも数値のみで[m]や[~着]などの記号は入力していません。 この中で、1600以下のレース(1000~1500まで)の着順や1600以上のレースの着順をそれぞれ1着、2着、3着、4着以下というように具体的に分けたいのですが、どうすれば良いでしょうか? 表にすると a b c d e 1600> (1着の数) (2着の数) (3着の数) (4着以下の数) 1600> (1着の数) (2着の数) (3着の数) (4着以下の数) こんなイメージです。 COUNTIFで1600以上のレース数はだせたのですが、着順を個別に出すことが出来ません。SUMPRODUCT関数かなとも思ったのですが、エラーか[0]になってしまいます。
- ベストアンサー
- オフィス系ソフト
お礼
早々のご教示有り難うございました。 私のような超初心者でもわかるドンピシャのご回答でした。ファイルの大きさや計算時間の関係で、この先困ることがあるかもしれませんが、この方法でシステムを完成させてみます。また、宜しくお願いします。