Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


11249 / 13646 ツリー ←次へ | 前へ→

【17124】行数が特定されていないデータの計算について かーくん 04/8/23(月) 15:25 質問[未読]
【17125】Re:行数が特定されていないデータの計算に... IROC 04/8/23(月) 15:30 回答[未読]
【17138】Re:行数が特定されていないデータの計算に... かーくん 04/8/23(月) 18:10 質問[未読]
【17139】Re:行数が特定されていないデータの計算に... IROC 04/8/23(月) 18:19 回答[未読]
【17140】Re:行数が特定されていないデータの計算に... かーくん 04/8/23(月) 19:00 質問[未読]
【17141】Re:行数が特定されていないデータの計算に... IROC 04/8/23(月) 20:37 回答[未読]
【17144】Re:行数が特定されていないデータの計算に... かーくん 04/8/23(月) 23:13 質問[未読]
【17152】Re:行数が特定されていないデータの計算に... IROC 04/8/24(火) 8:35 回答[未読]
【17160】Re:行数が特定されていないデータの計算に... ichinose 04/8/24(火) 12:15 発言[未読]
【17178】Re:行数が特定されていないデータの計算に... かーくん 04/8/24(火) 16:40 質問[未読]
【17211】Re:行数が特定されていないデータの計算に... ichinose 04/8/24(火) 19:42 発言[未読]
【17219】Re:行数が特定されていないデータの計算に... かーくん 04/8/24(火) 21:58 お礼[未読]

【17124】行数が特定されていないデータの計算につ...
質問  かーくん  - 04/8/23(月) 15:25 -

引用なし
パスワード
   みなさん、こんにちは。VBA始めたばかりの初心者です。
今、とある疑問で立ち止まっており、参考書とか見ても
理解ができないので、今回投稿させていただきました。

質問の内容は題名だけでは分からないと思いますが、
なんとか説明させていただきます。

例えば、A列の1行目に数値があるとします。それが、
確実に10行目まであれば、計算なりカウントなり
セル間を指定してできますが(例えば、RANGE(A1:A10))
、データ数が確定していない場合は、前述の例のような指定は
当然ながらできません。

参考書を見ると、変数などを利用してやるみたいですが、
勉強しても、理解が難しく、悩んでいるところです。

こういった場合には、どのように考えて、どのように処理すれば
良いのか、どなたか教えていただけないでしょうか?
できれば、簡単な例も併せて教えていただけると
非常に助かります。

初歩的な質問で、大変申し訳ありませんが、宜しくお願い致します。

【17125】Re:行数が特定されていないデータの計算...
回答  IROC  - 04/8/23(月) 15:30 -

引用なし
パスワード
   データの最下行の取得は、
一般操作では、 ctrl + ↓ などのショートカットキーを使用します。
これをマクロの記録すると、Endプロパティが記録されます。


それを用いて以下のようにできます。

Dim r As Long

r = Range("A65536").End(Xlup).Row

Range("A1:A" & r)

【17138】Re:行数が特定されていないデータの計算...
質問  かーくん  - 04/8/23(月) 18:10 -

引用なし
パスワード
   ▼IROC さん:
>データの最下行の取得は、
>一般操作では、 ctrl + ↓ などのショートカットキーを使用します。
>これをマクロの記録すると、Endプロパティが記録されます。
>
>
>それを用いて以下のようにできます。
>
>Dim r As Long
>
>r = Range("A65536").End(Xlup).Row
>
>Range("A1:A" & r)

IROCさん、早速ありがとうございます。
ご教授の内容を参考にすると、例えば、
合計を求める時には、
 Range("A1:A" & r).Formula = "=Sum("A1:A" & r)"
と、このように入力するのでしょうか?
って言っても、この内容ではできませんでした。
合計は、最終行の次の行に出力させたいです。
すみません、本当に初心者なので、
どうか、教えてください。宜しくお願いいたします。

【17139】Re:行数が特定されていないデータの計算...
回答  IROC  - 04/8/23(月) 18:19 -

引用なし
パスワード
   > Range("A1:A" & r).Formula = "=Sum("A1:A" & r)"

おしいです。

Range("A" & r + 1).Formula = "=Sum(A1:A" & r & ")"

【17140】Re:行数が特定されていないデータの計算...
質問  かーくん  - 04/8/23(月) 19:00 -

引用なし
パスワード
   ▼IROC さん:
>> Range("A1:A" & r).Formula = "=Sum("A1:A" & r)"
>
>おしいです。
>
>Range("A" & r + 1).Formula = "=Sum(A1:A" & r & ")"

なるほど、、、と言いたいのですが、「Range("A" & r + 1).Formula 」
は理解できました。ですが、「Range("A1:A" & r+1).Formula 」でも
できないのは何故なんでしょう?

また、「"=Sum(A1:A" & r & ")"」についてですが、「" & r & "」の部分が
理解できません。特に最後の「&」が何故に必要なのか?
すみません、別にけちを付けているわけではなく、
本当に疑問に思っているだけです。
またまた宜しくお願いします。

【17141】Re:行数が特定されていないデータの計算...
回答  IROC  - 04/8/23(月) 20:37 -

引用なし
パスワード
   > Range("A1:A" & r+1).Formula 」でも
>できないのは何故なんでしょう?

たとえば r = 5 のとき
Range("A1:A" & r+1) は、 A1〜A6 のすべてのセルに
数式を入れることになります。
この説明で、この記述だとダメだということが分かりますか?


>また、「"=Sum(A1:A" & r & ")"」についてですが、「" & r & "」の部分が
>理解できません。特に最後の「&」が何故に必要なのか?

r は変数です。 ""で囲まれた文字は、文字列となります。

変数と、文字列の接続には & を用います。

たとえば、メッセージボックスでも
Dim myStr As String '文字列型の変数宣言

myStr = "テスト" '変数に値を格納

MsgBox "これは、" & myStr & "です。"

のように使います。

【17144】Re:行数が特定されていないデータの計算...
質問  かーくん  - 04/8/23(月) 23:13 -

引用なし
パスワード
   なるほど、非常に理解できました(^^)
となると、件数をカウントするときは、(合計を算出したセルの次のセル
にカウント数を出力します)
 Range("A" & r + 2).Formula = "=COUNT(A1:A" & r & ")"
となるわけですね。

それで、大変恐縮ですが、最後に応用的なものの考え方を
教えていただけますでしょうか?

先ほどの例を使い、仮にセルA1〜A20までに1〜20までの
連番の数値があるとします。
そのA列にある数値の中で、もし「1」という数字を含む数値があったら、
その数値の合計を算出し、なおかつ件数をカウントするという内容なのですが、
どのように考えたらいいのでしょうか?
申し訳ありませんが、宜しくお願い致します。

【17152】Re:行数が特定されていないデータの計算...
回答  IROC  - 04/8/24(火) 8:35 -

引用なし
パスワード
    >先ほどの例を使い、仮にセルA1〜A20までに1〜20までの
>連番の数値があるとします。
>そのA列にある数値の中で、もし「1」という数字を含む数値があったら、
>その数値の合計を算出し、なおかつ件数をカウントするという内容なのですが、

それはVBAを使わなくても数式で出来ますよね?

=COUNTIF(A1:A20,1)
 

これをVBAの中で使う場合、
つまりワークシート関数をVBAで使うときは、
WorksheetFunctionプロパティを用います。

MsgBox "個数は、" & Application.WorksheetFunction.CountIf(Range("A1:A20"), 1)



【17160】Re:行数が特定されていないデータの計算...
発言  ichinose  - 04/8/24(火) 12:15 -

引用なし
パスワード
   ▼IROC さん、かーくん さん、こんにちは。

> >先ほどの例を使い、仮にセルA1〜A20までに1〜20までの
>>連番の数値があるとします。
>>そのA列にある数値の中で、もし「1」という数字を含む数値があったら、
>>その数値の合計を算出し、なおかつ件数をカウントするという内容なのですが、
これが、A1〜A20の中で「1」という文字を含む数値と解釈すれば・・・、
答えは、件数では、A1とA10〜a19の11件
合計の答えは、146となりますよね?

上記の解を求めたいなら、配列数式を使いますが、VBAで記述すると、
'=============================================================
Sub test()
  Dim rw As Long
  rw = Range("a" & Rows.Count).End(xlUp).row
  With Range("a" & rw + 1)
   .NumberFormatLocal = "G/標準"
   .FormulaArray = "=SUM(IF(ISERROR(FIND(1,A1:A" & _
           rw & ")),0,1))"
   End With
  With Range("a" & rw + 2)
   .NumberFormatLocal = "G/標準"
   .FormulaArray = "=SUM(IF(ISERROR(FIND(1,A1:A" & rw & _
           ")),0,A1:A" & rw & "))"
   End With
End Sub


こんな感じでしょうか?
確認して下さい。

【17178】Re:行数が特定されていないデータの計算...
質問  かーくん  - 04/8/24(火) 16:40 -

引用なし
パスワード
   ▼ichinose さん、こんにちは。わざわざありがとうございます。
ichinose さんの案の中身についてですが、自分でも調べましたが
何点か解決できなかったので、お手数ですが、ご教授をお願い致します。
なお、前回のIROCさんとのやりとりはご存知かと思いますが、
まだ初心者であり、失礼でおバカな質問だとは思いますが、どうか
宜しくお願い致します。恐らく、私自身、VBAより関数を先に
勉強したほうがいいのかも知れませんが。


1.まず、以下の文ついてですが、これは書式のことだと理解していますが
どのようなことを想定して入れたのでしょうか?なければないで
良いものなのでしょうか?
  .NumberFormatLocal = "G/標準"

2.次の配列数式について、カウントを求める数式だと思うのですが、
IS関数である「ISERROR」とは、私が調べた解釈だと、
エラー値を検索するときに使用すると
理解していたのですが、これは、どのように解釈すればいいのでしょうか?
また、最後の部分の「,0,1)」が、これも調べたのですが、
ヘルプには載っていなくて分かりませんでした。
さらに、カウント数を求めるのに、何故、「SUM」なのでしょうか?

  .FormulaArray = "=SUM(IF(ISERROR(FIND(1,A1:A" & _
              rw & ")),0,1))"

>これが、A1〜A20の中で「1」という文字を含む数値と解釈すれば・・・、
>答えは、件数では、A1とA10〜a19の11件
>合計の答えは、146となりますよね?
>
>上記の解を求めたいなら、配列数式を使いますが、VBAで記述すると、
>'=============================================================
>Sub test()
>  Dim rw As Long
>  rw = Range("a" & Rows.Count).End(xlUp).row
>  With Range("a" & rw + 1)
>   .NumberFormatLocal = "G/標準"
>   .FormulaArray = "=SUM(IF(ISERROR(FIND(1,A1:A" & _
>           rw & ")),0,1))"
>   End With
>  With Range("a" & rw + 2)
>   .NumberFormatLocal = "G/標準"
>   .FormulaArray = "=SUM(IF(ISERROR(FIND(1,A1:A" & rw & _
>           ")),0,A1:A" & rw & "))"
>   End With
>End Sub

【17211】Re:行数が特定されていないデータの計算...
発言  ichinose  - 04/8/24(火) 19:42 -

引用なし
パスワード
   ▼かーくん さん:
こんばんは。

>1.まず、以下の文ついてですが、これは書式のことだと理解していますが
>どのようなことを想定して入れたのでしょうか?なければないで
>良いものなのでしょうか?
>  .NumberFormatLocal = "G/標準"
これは、配列数式を設定するセルの書式が文字列に設定されていたりすると
数式の計算をしてくれません。よって、数式を設定する前に書式を標準に
しておくために記述しました。書式が文字列でなければ要りませんよ!!


>
>2.次の配列数式について、カウントを求める数式だと思うのですが、
>IS関数である「ISERROR」とは、私が調べた解釈だと、
>エラー値を検索するときに使用すると
>理解していたのですが、これは、どのように解釈すればいいのでしょうか?
>また、最後の部分の「,0,1)」が、これも調べたのですが、
>ヘルプには載っていなくて分かりませんでした。
>さらに、カウント数を求めるのに、何故、「SUM」なのでしょうか?
>
>  .FormulaArray = "=SUM(IF(ISERROR(FIND(1,A1:A" & _
>              rw & ")),0,1))"
Find関数は、検索する文字が見つからないとエラー値を返しますから、
Iserror関数を使って分岐しています。

つまり、Iserror関数の結果がTrueで1が含まれない、Falseで含まれる
ということになりませんか?

さらにIserror関数の結果がTrueなら、0、Falseなら1の合計(SUM関数)が
件数に相当します。

新規ブックに以下のコードをコピーして実行してみて下さい。
'====================================================
Sub Macro1()
  With Range("A1")
   .Value = 1
   .AutoFill Destination:=Range("A1:A20"), Type:=xlFillSeries
   End With
  Range("b1:b20").Formula = "=find(1,a1)"
  Range("c1:c20").Formula = "=iserror(b1)"
  Range("d1:d20").Formula = "=if(c1,0,1)"
End Sub

アクティブシートにコードの実行結果が表示されます。
B列では、
A1〜A20に対して1があるか否かを知るためにFind関数を使用しています。
結果、B列では、1が含まれないセルは、エラーになっていますよね?

C列では、
Find関数の結果をIserror関数を使用してTrueかFalseで結果を出しています。
1が含まれない(エラー値)は、True、1が含まれる(エラーではない)がFalse
という結果を出しているはずです。

D列では、
1が含まれるセルは1、含まれないセルは0

よって、このD列の合計が件数に相当します。

前回の配列数式は、この計算をしてくれる数式です。

尚、配列数式については、ここでは何回かリンクを貼りましたが、

http://pc21.nikkeibp.co.jp/special/hr/

を参考にして下さい。

わかりやすく説明されていると思います。

【17219】Re:行数が特定されていないデータの計算...
お礼  かーくん  - 04/8/24(火) 21:58 -

引用なし
パスワード
   ▼ichinose さん、IROCさん、ご丁寧な説明を
色々ありがとうございました。
非常に勉強になりました。
これから、更に精進させていただきます。
どうもありがとうございました。

11249 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free