EXCELでデータが入っている最終行までの合計

質問

現在、EXCEL2000で各月の全得意先の合計を求める式を作っています。
データは現在B列に得意先コード・C列に得意先名D列以降はVLOOKUP関数で別シートから求めた各月の得意先の売上金額を求めています。
D6セル：VLOOKUP(B6,'5月'!$B$6:$J$1500,9,0)

例えば、現在D6からD1149までにデータが入っており、別シートの得意先元リストの件数が増えれば、自動的に下の行にスライドするようになっています。その為、数行余分に予め関数を入れた空欄の行を作っています。
B6セル：OFFSET(元リスト!$B$6,ROW()-6,COLUMN()-2)

現在D1158にD6からデータの入っている最終行まで合計を関数で表示させたいのですがうまくいきません。ネットでも色々探しているのですが、私の検索の仕方が悪いのか中々見つかりません。
LOOKUP関数となにかを組み合わせて、得意先コード（B列）の空白になっている時、その一つ上のD列の行を参照できるようにできればいいんじゃないかとは思うのですが。
もし、いい方法があればご教示願います。

回答No.5

keithin · ベストアンサー

回答１の数式とは無関係に、今のあなたのエクセルで空白行に＃Ｎ／Ａが表示されているという事ですか？
基本的にエクセルを上手に使うには、関数のエラー値はできるだけ対処してエラーではない結果を表すよう工夫しなければいけません。

具体的には。
たとえばいまＤ６に
=VLOOKUP(B6,'5月'!$B$6:$J$1500,9,0)
を記入しているのなら、これを
=IF(ISERROR(VLOOKUP(B6,'5月'!$B$6:$J$1500,9,0)),"",VLOOKUP(B6,'5月'!$B$6:$J$1500,9,0))
などのように変更し、以下Ｄ１１５７までコピー貼り付けておきます。
これで空白行のエラーが消え、下流側の計算でもエラーが発生しなくなります。

Ｄ１１５８は回答した通り
=SUM(D6:D1157)
としておきます。

お礼

ご回答いただきありがとうございました。参考にさせていただきます。
エラー処理などの初歩的なことも分からない初心者で、説明が上手くできず、わかりづらかったと思います。ご丁寧な回答を頂きまして、ベストアンアンサーとお礼を申し上げます。
また、行き詰った時は質問させていただきたいと思います。
ありがとうございました。

回答No.4

keithin

D1158に
=SUM(D6:D1157)
としておくだけで良いのでは。

お礼

ありがとうございます。

補足

ご回答有り難うございます。私の説明不足で申し訳ありません。No2：KURUMITO様のご回答にてセルの状況を補足させていただいていおります。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

質問が理解できていないのですが
=SUM(D$6:INDEX(D:D,ROW()-1))
ということでしょうか？

お礼

ご回答いただきありがとうございました。参考にさせていただきます。
関数の使い方も勉強し始めたばかりで、説明が上手くできず、わかりづらかったと思います。
また、行き詰った時は質問させていただきたいと思います。

補足

ご回答ありがとうございます。説明不足で申し訳ございません。No.2のKURUMITO様の補足にシートのセルの状態を補足させていただいただきました。
ちなみに、CoalTar様の方法で、予備の行の関数を消したら上手く計算できました。
ちなみにこの予備の行の関数を消さずに計算をすることは可能でしょうか？
よろしければ教えていただければと存じます。

回答No.2

KURUMITO

例えばＤ６セルには次のような式を入力することでＤ列のデータの最後に合計の数値が表示されます。

=IF(B6<>"",VLOOKUP(B6,'5月'!$B$6:$J$1500,9,0),IF(AND(B5<>"",B6=""),SUM(D$5:D5),""))

最後の行かｒ１行はなして下方に表示させるのでしたら次のような式にします。

=IF(B6<>"",VLOOKUP(B6,'5月'!$B$6:$J$1500,9,0),IF(AND(B4<>"",B5="",B6=""),SUM(D$5:D5),""))

お礼

ご回答いただきありがとうございました。参考にさせていただきます。
関数の使い方も勉強し始めたばかりで、説明が上手くできず、わかりづらかったと思います。
また、行き詰った時は質問させていただきたいと思います。

補足

ご回答有り難うございます。これは合計の欄に計算式を入れるのではなく、各得意先の月別売上金額を参照する式に手を加える形ということでしょうか。あまり関数が詳しくない上に、前任者が作ったファイルなので、試行錯誤しながらやっています。
式の内容を把握してからお返事しようと思ったのですが、わからず、教えていただいている上に大変恐縮ですが、計算の流れを教えていただけますでしょうか?あと、補足として下に現在のシートの内容を書きます。
セル行番号　Ａ　　　　Ｂ　　　　　Ｃ　　　　　　Ｄ
4　　　　　　　　　　　　　コード　　名　称　　　5月
　　　　　　　　　　　　　　　　　～省　略～
1149　　　　　　　　　　 TUME　　 詰　替　　　　0
1150　　　　　　　　　　 空白　　　空　白　　　＃N/A
　　　　　　　　　　　　　　　　　～省　略～
1155　　　　　　　　　　 空白　　　空　白　　　＃N/A
1156　　　　　　　
1157　　　　　　　
1158　　　　　　　　　　　　　　　　　総合計　　　D5～D1149までの合計=SUBTOTAL(9,D6:D1149)
1159　　　　　　　　　　　　　　　　　総合計-現金売上げ 総合計からコード99999の現金売上げを引いた値=D1158-D1098

罫線がないので見づらくて申し訳ありません。A列と、1156・1157行はレイアウトの都合上空白です。オプションで０は非表示にしてあります。1150～1155行までは得意先リストが増えた際の予備の行です。なので現在は元リスト（別シート）には1149件しかデータが無い状態ですので、予備の行はエラーが出ています。
コードは英数混じっています。

回答No.1

K Kazz（@JazzCorp）

VLOOKUP(B6,'5月'!$B$6:$J$1500,9,0)を
VLOOKUP(B6,'5月'!$B:$J,9,0)
にするだけでは？？

お礼

ご回答有り難うございます。
これは、Ｂ列からＪ列までの全ての行を参照しているということですよね。データも正しく反映されました。有り難うございます。