[[20160916170040]] 『セルの範囲の取り方』(こなつ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『セルの範囲の取り方』(こなつ)

いつもお世話になっています。

あるセルに "=sum(あれ:これ)" と
書いてみるのですがうまくいきません。
どなたか教えて頂けないでしょうか。

取りたい範囲は

Range("X1").End(xlDown).Offset(1,ー5)
から
Range("S" & Rows.Count).End(xlup)
までです。
この範囲をSumで合計を取ろうと。

起点のセルは
S列のあるセルで、いつも変わります。
しかし
X列からはいつも上記で取れるので
こんなふうになってしまいました。

よろしくお願いいたします。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 この範囲を【シート上の数式】で合計したいということですか?

(β) 2016/09/16(金) 17:32


はい。
そのあとに出た合計をオートフィルしたいので。
シート上がいいかと思いまして。
(こなつ) 2016/09/16(金) 17:38

 S列の関連領域の値は数値だと思いますが、X列のほうの 1行目からデータ最終行も数値ですか?それとも文字列ですか?

(β) 2016/09/16(金) 19:27


 こんばんわ。

 "=sum(あれ:これ)"のような記述の時の、あれ:これ はセルのアドレスを文字列で記述しますね。

 そして起点と終点のセルは、それぞれ
 Range("X1").End(xlDown).Offset(1,ー5)  
 Range("S" & Rows.Count).End(xlup) 
 で取得出来ているので、
 Range(Range("X1").End(xlDown).Offset(1, -5), Range("S" & Rows.Count).End(xlUp))
 これが範囲になりますね。

 アドレスの文字列を取得するには、Addressプロパティを使います。
 Range(Range("X1").End(xlDown).Offset(1, -5), Range("S" & Rows.Count).End(xlUp)).Address
 のように記述すれば、$S$1:$S$10 のように範囲のアドレスの文字列を絶対参照で取得出来ます。

 相対参照にしたければ、
 Range(Range("X1").End(xlDown).Offset(1, -5), Range("S" & Rows.Count).End(xlUp)).Address(False, False)
 のように記述すれば、S1:S10 のように相対参照のアドレスの文字列が取得出来ます。

 後は取得した文字列を"=sum(" & 取得したアドレス & ")"として繋げるだけです。

(sy) 2016/09/16(金) 19:39


βさんへ

SもXも数値です❗
(こなつ) 2016/09/16(金) 20:15


 学校内でエキスパートさんから回答があったコードを、意味もわからずつなぎ合わせてみました。

 =SUM(INDIRECT("S"&LOOKUP(9^9,X:X,ROW(X:X))+1&":S"&LOOKUP(9^9,S:S,ROW(S:S))))

(β) 2016/09/16(金) 20:26


syさん

初めての質問の時は大変お世話になりました。
"("& の""のくくりかたで
トンチンカンな質問をしたこなつです。

なるほど!
起点と終点を:でつなげていたからダメだったのかも。
,でつなげてみます。
あとAddressというのも書き足してみます。
もう会社を出てしまいましたので
月曜に試してお返事致します。
ありがとうございました❗
(こなつ) 2016/09/16(金) 20:36


βさん ありがとうございます。

私は初心者でしてさっぱり理解できませんが
月曜に早速試してからお返事差し上げたいと思います。
速いご回答をありがとうございます❗
(こなつ) 2016/09/16(金) 20:40


 To βさん

 >=SUM(INDIRECT("S"&LOOKUP(9^9,X:X,ROW(X:X))+1&":S"&LOOKUP(9^9,S:S,ROW(S:S))))
 この式だと、数式をセットするセルがS列の最後尾とかだと循環参照になりますよ。
 後X列に飛び飛びでデータがあった場合なども、起点のセルの行が最下行になってしまいます。

 関数のみで完結させるなら、以下ですね。
 =SUM(INDEX(S:S,MATCH(1,INDEX(($X:$X<>"")*1,0),0)+1):INDEX(S:S,ROW()-1))

(sy) 2016/09/16(金) 21:12


 X列でデータの初めの切れ目までなら以下でした。

 =SUM(INDEX(S:S,MATCH(1,INDEX(($X:$X="")*1,0),0)):INDEX(S:S,ROW()-1))

(sy) 2016/09/16(金) 22:26


 To syさん

 お世話様です。

 コメントしましたように、意味もわからずパクッテ組み合わせていますので。

 確かに 式を S列に配置すると、おかしなことになる可能性もあるんでしょうね。
 ただ、X列に関しては、とびとびであっても、そのとびとびの、一番最後のセルを取得していると思いますが。

(β) 2016/09/16(金) 23:57


 To βさん

 >Range("X1").End(xlDown).Offset(1,ー5)
 >X列からはいつも上記で取れるので 
 >こんなふうになってしまいました。

 とあるので、X1からデータがあって、End(xlDown)なので切れ目までかなぁと。

 飛び飛びデータとかがある場合だと初めの切れ目までになるので、最後のセルまで取得すると起点が下にずれてしまうんじゃないかと思いました。

 まぁ飛び飛びデータじゃ無ければ関係ないんですけどね。

(sy) 2016/09/17(土) 01:13


 >>とあるので、X1からデータがあって、End(xlDown)なので切れ目までかなぁと。

 あぁ、そうかもしれませんねぇ。
 なんとなく、連続していて、たまたま、xlDownを使っていて、それで、結果的に大丈夫だからということだと勝手に推測しちゃいました。
 (とびとびなので、あえて xlDownを使っている ということなら こなつ さんに失礼な推測でしたね)

(β) 2016/09/17(土) 04:14


 後、
 >そのあとに出た合計をオートフィルしたいので。
 とあったので、同じ列の最下段にセットして集計表みたいなのを作りたいのかなぁと想像してました。

 でもこなつさんから、同列にセットも、飛び飛びデータも明確に提示があった訳では無いので、
 私が勘繰り過ぎてるだけかも知れません。

 両方違ってたら初めのβさんの式で全く問題ないので、
 その時は申し訳ありません。
 ご容赦下さい。

(sy) 2016/09/17(土) 06:01


こんにちは。

早速試してみようとあれこれ書いてる途中で
誤って×を押してしまいました。
それっきり「使用者が自分」と表示されてしまい
編集ロックがかかってしまいました。
何をやってもダメなので
サーバー管理の担当者に連絡します。
お返事は水曜日になってしまいます。
ごめんなさい。。。
(こなつ) 2016/09/19(月) 17:15


こんにちは。

>それっきり「使用者が自分」と表示されてしまい
>編集ロックがかかってしまいました。

何度か経験があります。
わたしのところでは、そのファイルを保存してあるコンピュータを再起動すると直りました。
コンピュータのどこかに、「ファイルを使用しているのは誰か」を記録してあるようで
再起動することで、その情報を忘れてくれるようです。

よかったら試してみてください(って、本日の営業は終了でしょうか)
( 佳 ) 2016/09/19(月) 17:39


こんにちは。
返事が遅くなりましてすいません。

syさんから教えて頂いたAddressを使って
思い通りに動いてくれました❗

尚 syさんとβさんが
それぞれ考えてくださった別な数式について

いかんせん初心者なもので
どこからどこが文字列でどこに""や&を
入れたらよいのか考えてあぐねてしまい断念しました。
ごめんなさい。。。

お二人とも本当にありがとうございました。
また宜しくお願い致します❗
(こなつ) 2016/09/22(木) 17:14


 解決出来て何よりです。

 =SUM(INDEX(S:S,MATCH(1,INDEX(($X:$X="")*1,0),0)):INDEX(S:S,ROW()-1))
 この式を提示したのは、マクロを使わない方法です。

 マクロでセットしたセルに上記関数を入力すると同じ結果が得られます。
 ただそれだけの事です。

(sy) 2016/09/22(木) 22:08


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.