エクセルの関数について

例年この時期に必ず苦労して困っています。質問させてください…
毎年毎年、販売個数を出すのに列を削除する方がいて、ミスが起きたり作り直しとか大変でとても困ってます。

列を削除してもSUMproductの関数がつかいたいのですが、

=SUMPRODUCT($C＄3:(ADDRESS(3,(MATCH("枚数"),0)-1),1),C4:(ADDRESS(3,(MATCH("枚数"),0)-1),4)))

みたいな感じで考えて見たのですがエラーメッセージでお手上げです…

どうにか個数と書いたの手前の行までの掛け算をする数式ができないでしょうか

よろしくお願いします！

質問者からの補足コメント

• すみません、個数と書いたつもりが枚数になってました。
  そしてカッコの数はおかしいかもしれないです。

    補足日時：2019/04/20 08:44

• 

  色々不足があって申し訳ないです。
  関数に詳しいわけでもなく初心者なので、
  エクセルヘルプを参照しながら何とか考えた次第です。
  
  そしてMATCHの("枚数",＄C＄3:＄Z＄3,0)と書いてあったはずが…何故かこんな風に変わっていました。申し訳ございません

    補足日時：2019/04/20 12:47

• 色々不足があって申し訳ないです。
  もちろん関数に詳しいわけでもなく初心者なので、
  エクセルヘルプを参照しながら何とか考えた次第です。
  
  そしてMATCHの("枚数",＄C＄3:＄Z＄3,0)と書いてあったはずが…移動中に打ってたら何故こんなことに…。申し訳ございません。
  offset関数も考えたのですが、難しくて"枚数"の位置と一致させれないかな…そしてその一つ前の列まで金額が入っているのでその列の位置をaddressで出して
  SUM productにの配列の指定に組み込めないかと思っていた次第です…

  No.5の回答に寄せられた補足コメントです。 補足日時：2019/04/20 12:53

• addressとMATCHを使ったところでは予定通りの＄Y＄3とY4のアドレスをゲット出来ていたのですが…
  そうなのです。
  SUM productがおかしいです

  No.4の回答に寄せられた補足コメントです。 補足日時：2019/04/20 13:15

• =SUMPRODUCT($C＄3:(ADDRESS(3,(MATCH("枚数",＄C＄3:＄Z＄3,0)-1),1),C4:(ADDRESS(4,(MATCH("枚数",C4:Z4,0)-1),4)))
  
  と元々つくってSUMproductの配列の指定に失敗しています…。
  C3からの3行目に金額が入っておりそのラストが枚数と書いてあるので、
  その一つ前のセルのアドレスを指定したらいいのでは？と思ってやって見たのです。
  
  そして、シートの列保護ですが、目安を印刷したい理由で保護を解除して列を減らして印刷とかもあったので
  それで解決できていない現状です。
  
  質問する時にエラーが出てしまい、直しているうちにおかしな事をしていたようです。
  申し訳ございません。

    補足日時：2019/04/20 13:24

No.11 ベストアンサー 回答者： nouble1 回答日時： 2019/04/20 14:33

又　訂正です、

=SUMPRODUCT(INDIRECT("C3:"&ADDRESS(3,MATCH("枚数",INDIRECT("C3:Z3"),0)+2)))

何度も　申し訳ない、
こっちですね。


そして応用編が、
=SUMPRODUCT(INDIRECT("C3:"&ADDRESS(3,IFERROR(MATCH("枚数",INDIRECT("C3:Z3"),0),COUNTA(INDIRECT("C3:Z3")))+2)))

此方は、
もし　"枚数"記載列をも、
削除してしまっても、

まあ　取り敢えずは、
エラーには　ならない、
式です。


確認くださいね。


後、
解説を　ご所望に、
なられる場合は、

そう、
お申し付けくださいね。


追記、
いゃ〜、
楽しませて　頂きました、

久々に、
頭が　回る、
噛み応えの　ある、
課題でした、

いい課題を　有り難うです。

この回答へのお礼

感動的です…！後で質問文を見直してみて何がおかしというと質問文が一番おかしいという失礼な事をしてしまった中、
たくさんの答えを出していただきありがとうございます！！

出来ました！
indirectの関数の使い方、全く理解してなかったです。
本当にありがとうございました！
とりあえず出来ましたが、応用編も研究してみようと思います！

お礼日時：2019/04/20 14:51

No.10 回答者： nouble1 回答日時： 2019/04/20 14:14

しかし　此のままだと、

C列か、Z列の、
削除に　伴い、

エラーが　出たり、
検索範囲が　縮まったり、
する、
と　いう事ですね。


ならば、
=SUMPRODUCT(INDIRECT("C3:"&ADDRESS(MATCH("枚数",INDIRECT("C3:Z3"),0),3)))
ですかね？


確認くださいね。

No.9 回答者： 銀鱗 回答日時： 2019/04/20 13:57

2019/04/20 13:24 の補足に対する返答です。

＞シートの列保護ですが、目安を印刷したい理由で保護を解除して列を減らして印刷とかもあったので

(´・ω・`)
列を「削除」するのではなく「非表示」にすれば良いだけですよ。それ。
または印刷する場合は別のBookとして保存し直してから列を削除するようにルールを作る。

なんでそんな理由で責任を負う必要があるのか、自分には分からない。

関数やマクロが少しできるから対応してみようと思うのは悪いことではありません。
でもそこで考えて欲しい。
根本的に何が間違っているのか。そしてどうすれば解決できるかを。

No.8 回答者： nouble1 回答日時： 2019/04/20 13:52

あ、ごめんなさい、

舗装→補足
済みません。

No.7 回答者： nouble1 回答日時： 2019/04/20 13:50

舗装　頂いた、

内容を、
素直に　其のまま、
読めば、

=SUMPRODUCT(OFFSET(
$C$3,0,0,1,MATCH("枚数",$C$3:$Z$3,0)))
ごうですかな？

No.6 回答者： tom04 回答日時： 2019/04/20 09:52

こんにちは！

関数でどうしても列削除に対応したいのであれば、
INDIRECT関数を併用すればできそうですが、お示しの数式ではエラーになると思います。

具体的な配置がどのようになっていて、どのような処理をしたいのか？が判れば
少しはお役に立てるかも・・・m(_ _)m

No.5 回答者： nouble1 回答日時： 2019/04/20 09:47

VBを　学ぶのと、

VBAを　学ぶのとでは、

意味合いが　全く、
違います、

機械語と　高級言語程の、
差が　あるのですから。


そして、
VBAを　学ぶという事は、

シートを　知り尽くす、
と　いう事であって、

質問者が　仰られる、
此は、
其の内の　基礎的な、
最たるもの。


故に、
ヤジ内容は　不当ですよ？


さて、
MATCH("枚数")
此が　どうにも、
解せません、

此の部位で、
望み通りの　列位置を、
知り、
指定したいものと　思うのですが、

名前定義でも　無さそうですし、
構文構造からも　外れていますよね、


因みに　本筋なら、
MATCH構文は、
MATCH(検索指標値,検索範囲セルアドレス,0)
等々と　書きますよね？

又、
MAX(INDEX((検索指標値=検査範囲セルアドレス)*COLUMN(検査範囲セルアドレス),"")
等とも　書きますよね？


因みに　後者なら、
途中列を　削除しても、
エラーは　でなかったと、
思いますが、

作成意図は、
どの様な　ものだったのですか？


抑も、
アドレス構文でも、
まあ　良い訳ですが、

しかし、
二回　持ちいないと、
セル範囲指定が　出来ませんよね？

此を　例えば、
Offset構文等に　すれば、

位置算定等を　除けば、
1構文で　済みますよね？


兎に角　此のままでは、
ご意図が　伝わってきませんので、

ご意図の　解説を、
お願い　できますか？

No.4 回答者： enunokokoro 回答日時： 2019/04/20 09:39

式の中身を個別に確認してみては。

ADDRESS関数の行列指定は正しいのでしょうか？
http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/ …
MATDH関数の指定で列を取得できているのでしょうか？
http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/ …
SUMPRODUCT関数でのエラーの原因を探すほうが先だと思います。
http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/ …

No.3 回答者： GOMΛFU 回答日時： 2019/04/20 09:36

列削除できないようにセルにロックをけければ良いんじゃぁ

No.2 回答者： 銀鱗 回答日時： 2019/04/20 09:34

＞毎年毎年、販売個数を出すのに列を削除する方がいて～

この改善が先決でしょ。

シートの保護で対策できる気がする。