[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『SUMIF関数のﾏｸﾛ』（ﾏｸﾛの初心者ﾏｰｸ）
いつも拝見させてもらっています。
I9=SUMIF(Sheet2!$M$8:$M$200,$C9,Sheet2!$T$8:$T$200) この式をﾏｸﾛにて、実行するにはどうしたら良いでしょうか？
また、あるセルに特定の文字が含まれていたらの数式、含まれていなかったら②の式を実行するようにしたいのですが、 どうすれば良いでしょうか？ ①：IF(COUNTIF(Sheet3!$I$10:$I$100,$L8),VLOOKUP($L8,Sheet3!$I$10:$M$100,5,0)*$Q8,"")/1000 ②：IF(COUNTIF(Sheet3!$I$10:$I$100,$L8),VLOOKUP($L8,Sheet3!$I$10:$M$100,5,0)*$Q8,"") (ﾏｸﾛの初心者ﾏｰｸ)
とりあえず、最初の数式。
Sub Sample1()
Dim tbl1, tbl2, ky, x()
Dim i As Integer, n As Integer
With Worksheets("Sheet2")
tbl1 = .Range("M8:M200")
tbl2 = .Range("T8:T200")
End With
ky = Worksheets("Sheet1").Range("C9").Value
n = 0
For i = 1 To UBound(tbl1, 1)
If tbl1(i, 1) = ky Then
ReDim Preserve x(n)
x(n) = tbl2(i, 1)
n = n + 1
End If
Next
On Error Resume Next
Worksheets("Sheet1").Range("I9").Value = WorksheetFunction.Sum(x)
If Err.Number = 5 Then Worksheets("Sheet1").Range("I9").Value = 0
On Error GoTo 0
End Sub
(ROUGE)
2つ目。
Sub Sample2()
Dim C As Range
With Worksheets("Sheet1")
Set C = Worksheets("Sheet3").Range("I10:I100").Find(what:=.Range("L8").Value)
If Not C Is Nothing Then
.Range("I10").Value = C.Value * .Range("Q8").Value / 1000 '←(1)と(2)はこの/1000の有無の違いのみです。
Else
.Range("I10").Value = ""
End If
End With
End Sub
(ROUGE)
ROUGEさん、早速なレスありがとうございます。それもｺｰﾄﾞ付で！！ Sample1のｺｰﾄﾞですが、実行した所、バッチグーでした。 このｺｰﾄﾞをSheet1のC列の値が入力されている分だけ計算を行うにはどうすればいいのかな？ (値が入力されている最後の行までって事…)
Sample2のｺｰﾄﾞですが、実行した所、「型が一致しません。」とエラーが出ます。
>.Range("I10").Value = C.Value * .Range("Q8").Value / 1000 '←(1)と(2)はこの/1000の有無の違いのみです。
エラーが出るのは上のｺｰﾄﾞの部分です。
それと、これもSample1でお願いした様にL列の値が入力されている最後の行まで行いたいのですが…
(ﾏｸﾛの初心者ﾏｰｸ)
>I9=SUMIF(Sheet2!$M$8:$M$200,$C9,Sheet2!$T$8:$T$200)
>この式をﾏｸﾛにて、実行するにはどうしたら良いでしょうか？
MsgBox Application.SumIf(Sheets("Sheet2").Range("M8:M200"), Sheets("？？？").Range("C9").Value, _
Sheets("Sheet2").Range("T8:T200"))
VLOOKUPの分はいっしょに書くと余計に解らなくなると思うから、 カッコを気にせずにVLOOKUP単体で答えを取り出した方が良いです。 BJ
これを書いたときは寝ぼけてましたね・・・orz
Offsetを忘れていました。
.Range("I10").Value = C.Offset(,4).Value * .Range("Q8").Value / 1000
でどうでしょう？
(ROUGE)
ROUGEさん。上のｺｰﾄﾞを実行した所、少し問題が… 現在のｺｰﾄﾞでは1つのセルにしか対応していませんが、 Sheet1のL列の最終行までを一気にやってしまいたいのです。(これはSample1についても同様に行いたいです。) また、Sample2のｺｰﾄﾞについては同一列に配置してある為、特定の文字をｺｰﾄﾞ内で 判別し、式を使い分けれるようにしたいのですが。 無理ばかりお願いしてすいませんが、どうぞよろしくおながいしまつ。 (ﾏｸﾛの初心者ﾏｰｸ)
BJさん。BJさんの提示して頂いたコード試してみました。 ですが、計算結果が別ウィンドウで表示されます。 計算結果は別ウィンドウではなく、セル内に表示したいのです。 (ﾏｸﾛの初心者ﾏｰｸ)
> 計算結果は別ウィンドウではなく、セル内に表示したいのです。 う～ん。 なんて書けば良いのか・・。 結果をセルに書き込めば良いですって事なんだけど・・・。 この違いというか、これもわからないのでしょうか？ BJ
Msgbox 12345
Range("A1").Value = 12345
BJさんのおっしゃる事は分かります。 ですが、出来れば、ﾏｸﾛ実行後に別ウィンドウを出さないで、 ﾏｸﾛを実行したら、そのままセルに表示をしたいのです。 (ﾏｸﾛの初心者ﾏｰｸ)
解ってないと思います。
Msgbox 12345 ←これがメッセージ
Range("A1").Value = 99999999 ←これがセルに書き込み
BJ
↑のｺｰﾄﾞを実行すると、
>Msgbox
で指定したメッセージの内容が１度別ウィンドウで表示され、その後、
>Range("A1").Value =
で指定した、セルに表示すると言う事ですよね？
(ﾏｸﾛの初心者ﾏｰｸ)
多分、結構な時間がかかると思います。
'----
Sub Sample1()
Dim tbl1, tbl2, ky, x()
Dim i As Integer, n As Integer
Dim j As Long
With Worksheets("Sheet2")
tbl1 = .Range("M8:M200")
tbl2 = .Range("T8:T200")
End With
For j = 9 To Worksheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
ky = Worksheets("Sheet1").Range("C" & j).Value
n = 0
For i = 1 To UBound(tbl1, 1)
If tbl1(i, 1) = ky Then
ReDim Preserve x(n)
x(n) = tbl2(i, 1)
n = n + 1
End If
Next i
On Error Resume Next
Worksheets("Sheet1").Range("I" & j).Value = WorksheetFunction.Sum(x)
If Err.Number = 5 Then Worksheets("Sheet1").Range("I" & j).Value = 0
On Error GoTo 0
Erase x
Next j
End Sub
Sub Sample2()
Dim C As Range
Dim i As Long
With Worksheets("Sheet1")
For i = 8 To .Range("L" & Rows.Count).End(xlUp).Row
Set C = Worksheets("Sheet3").Range("I10:I100").Find(what:=.Range("L" & i).Value)
If Not C Is Nothing Then
.Range("H" & i).Value = C.Offset(, 4).Value * .Range("Q" & i).Value / 1000
Else
.Range("H" & i).Value = ""
End If
Next i
End With
End Sub
'(ROUGE)
Dictionaryの試作品です。
(ROUGE)
'----
Sub Sample1_1()
Dim dic As Object
Dim tbl1, tbl2
Dim l_r As Long, i As Long
With Worksheets("Sheet2")
l_r = .Range("M" & Rows.Count).End(xlUp).Row
tbl1 = .Range("M8:M" & l_r)
tbl2 = .Range("T8:T" & l_r)
End With
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(tbl1, 1)
If Not dic.Exists(tbl1(i, 1)) Then
dic.Add tbl1(i, 1), tbl2(i, 1)
Else
dic(tbl1(i, 1)) = dic(tbl1(i, 1)) + tbl2(i, 1)
End If
Next i
Erase tbl1
Erase tbl2
With Worksheets("Sheet1")
l_r = .Range("C" & Rows.Count).End(xlUp).Row
tbl1 = .Range("C9:C" & l_r)
ReDim tbl2(1 To UBound(tbl1, 1))
For i = 1 To UBound(tbl1, 1)
If dic.Exists(tbl1(i, 1)) Then
tbl2(i) = dic(tbl1(i, 1))
Else
tbl2(i) = 0
End If
Next i
.Range("I9").Resize(UBound(tbl1, 1)).Value = Application.Transpose(tbl2)
End With
Erase tbl1
Erase tbl2
Set dic = Nothing
End Sub
Sub Sample2_1()
Dim dic As Object
Dim tbl1, tbl2, tbl3
Dim l_r As Long, i As Long
With Worksheets("Sheet3")
l_r = .Range("I" & Rows.Count).End(xlUp).Row
tbl1 = .Range("I10:I" & l_r)
tbl2 = .Range("M10:M" & l_r)
End With
Set dic = CreateObject("Scripting.Dictionary")
On Error Resume Next
For i = 1 To UBound(tbl1, 1)
dic.Add tbl1(i, 1), tbl2(i, 1)
Next i
If Err.Number = 457 Then: MsgBox ("検索データが重複しています！"): Exit Sub
On Error GoTo 0
Erase tbl1
Erase tbl2
With Worksheets("Sheet1")
l_r = .Range("L" & Rows.Count).End(xlUp).Row
tbl1 = .Range("L8:L" & l_r)
tbl2 = .Range("Q8:Q" & l_r)
ReDim tbl3(1 To UBound(tbl1, 1))
For i = 1 To UBound(tbl1, 1)
If dic.Exists(tbl1(i, 1)) Then
tbl3(i) = dic(tbl1(i, 1)) * tbl2(i, 1)
Else
tbl3(i) = ""
End If
Next i
.Range("H8").Resize(UBound(tbl1, 1)).Value = Application.Transpose(tbl3)
End With
Erase tbl1
Erase tbl2
Erase tbl3
Set dic = Nothing
End Sub
ROUGEさん、ありがとうございます。凄すぎて感動してしまいました。 Sample1のｺｰﾄﾞでは確かに実行に時間が掛かりましたが、思い通りの結果だったので、 やった！！と思っていた矢先に、Sample1_1のｺｰﾄﾞが更新してあった為、試した所、 結果は同じだったのですが、実行が一瞬の内に終わってしまった為、ﾏｼﾞで凄過ぎて感激です。 Sample2について、実行した所、特定の文字の有無の所がうまく実行出来ていないのか、全てが同じ計算式での計算になってしまいました。 試しにSample2の >.Value / 1000 の『/1000』を消したｺｰﾄﾞをSample2の下に続けて置いてみたのですが、 結果は同じで、全て同じ計算式が実行されてしまいました。 また、Sample2_1を実行した所、『検索データが重複しています！』とメッセージが出て、ｺｰﾄﾞが実行されませんでした。 なぜなのでしょうか？
VLOOKUP関数を使われていたので、Sheet3のI列に重複があるとまずいのかと思いました。 そのメッセージはSheet3のI列に重複があったときに出るようにしています。 重複があっても最初にヒットしたものを表示すればいいのであれば、多少改変が必要ですが可能です。
時間については、後でアップしたものはすべて変数に格納しているので早くなっています。 (ROUGE)
そうだったのですね。ですが、 Sheet3のI列には、同じ値が重複して入力されている事はありませんが… Sheet1のL列には、同じ値が連続して入力されています。それが影響しているのでしょうか？ 教えてもらってもすぐに理解できるかどうか分かりませんが、 変数に格納するとはどういった事なのでしょうか？ (ﾏｸﾛの初心者ﾏｰｸ)
> Sheet3のI列には、同じ値が重複して入力されている事はありませんが… えっ？そうなの？・・・orz
> If Err.Number = 457 Then: MsgBox ("検索データが重複しています！"): Exit Sub
この一行をコメントブロックにしてみてください。
> 変数に格納するとはどういった事なのでしょうか？ 直接シートを弄くると時間がかかるようです。（キリキせんせぇに教わりました） ↓が参考になるかもしれません。 http://www.officetanaka.net/excel/vba/speed/s11.htm (ROUGE)
>> Sheet3のI列には、同じ値が重複して入力されている事はありませんが… > えっ？そうなの？・・・orz そうなんですよ。実際使っていた数式はVLOOKUP関数を使用していました。 ですが、ROUGEさんに指摘を頂いてもしかしてと思い、再検証した結果、 IF関数とSUMIF関数を使用して出来る事が分かりました。私が複雑に考え過ぎていたのと、VLOOKUP関数にこだわり過ぎていたようです。 今まで、色々とｺｰﾄﾞを作って頂いたのですが、本当に申し訳無いです。 本当なら、もっと簡単に出来ていたかも知れないのに、私の力不足でお手間を取らせてしまい本当にすいません… 一応、変更して出来た数式を載せておきます。 >①：IF(COUNTIF(Sheet3!$I$10:$I$100,$L8),VLOOKUP($L8,Sheet3!$I$10:$M$100,5,0)*$Q8,"")/1000 >②：IF(COUNTIF(Sheet3!$I$10:$I$100,$L8),VLOOKUP($L8,Sheet3!$I$10:$M$100,5,0)*$Q8,"") 変更後:IF($R8="mm",SUMIF(Sheet3!$I$10:$I$811,$L8,Sheet3!$M$10:$M$811)*$Q8/1000, SUMIF(Sheet3!$I$10:$I$811,$L8,Sheet3!$M$10:$M$811)*$Q8)
>> If Err.Number = 457 Then: MsgBox ("検索データが重複しています！"): Exit Sub
>この一行をコメントブロックにしてみてください。
↑をｺﾒﾝﾄﾌﾞﾛｯｸにして実行してみました。ですが、計算値が表示されるセルが１行ズレて表示されます。
というか、一番最初に空白が表示されてしまいます。
また、特定の文字列と言うのはｺｰﾄﾞのどの部位で決めているのでしょうか？
実際実行された、計算値は全て同じ数式を計算した値でした。
変数に関しての資料拝見させてもらいました。ですが、内容が私のスキルでは 分からない事ばかりだったので、これからじっくり勉強させてもらいます。 勉強になるサイトを紹介して頂き、ありがとうございます。 (ﾏｸﾛの初心者ﾏｰｸ)
サンプルデータがないので、Testが十分にできません。
変更後の数式についてコードを書いてみました。
ところで、掲示板には半角カタカナは使用されない方が良い（らしい）です。
(ROUGE)
'----
Sub Sample2_2()
Dim dic As Object
Dim tbl1, tbl2, tbl3, tbl4
Dim l_r As Long, i As Long
Set dic = CreateObject("Scripting.Dictionary")
With Worksheets("Sheet3")
l_r = .Range("I" & Rows.Count).End(xlUp).Row
tbl1 = .Range("I10:I" & l_r)
tbl2 = .Range("M10:M" & l_r)
End With
For i = 1 To UBound(tbl1, 1)
If Not dic.Exists(tbl1(i, 1)) Then
dic.Add tbl1(i, 1), tbl2(i, 1)
Else
dic(tbl1(i, 1)) = dic(tbl1(i, 1)) + tbl2(i, 1)
End If
Next i
Erase tbl1
Erase tbl2
With Worksheets("Sheet1")
l_r = .Range("L" & Rows.Count).End(xlUp).Row
tbl1 = .Range("R8:R" & l_r)
tbl2 = .Range("L8:L" & l_r)
tbl3 = .Range("Q8:Q" & l_r)
ReDim tbl4(1 To UBound(tbl1, 1))
For i = 1 To UBound(tbl1, 1)
If dic.Exists(tbl2(i, 1)) Then
If tbl1(i, 1) = "mm" Then
tbl4(i) = dic(tbl2(i, 1)) * tbl3(i, 1) / 1000
Else
tbl4(i) = dic(tbl2(i, 1)) * tbl3(i, 1)
End If
Else
tbl4(i) = 0
End If
Next i
.Range("H8").Resize(UBound(tbl4)).Value = Application.Transpose(tbl4)
End With
Erase tbl1
Erase tbl2
Erase tbl3
Erase tbl4
Set dic = Nothing
End Sub
ROUGEさん ↑のコードでバッチシ出来ました。ありがとうございます。 半角カタカナでのカキコの指摘ありがとうございます。これから気を付けます。 それと、差し出がましいとは思うのですが、よろしければ、もう２つ教えて頂けないでしょうか？ ①：N9=IF(数式１>0,0,数式１)⇒数式１と言うのはここで教えてもらったSample1_1を変更して使用します。 ②：L9=SUMIF($P$7:$AG$7,"<>",$P9:$AG9) お手数ですが、よろしくお願いします。 (マクロの初心者マーク)
注意点その２ｗｗｗ 丸付き文字も掲示板では使用を控えた方が良い（らしい）です。
とりあえず１番目
２番目のループを下記のように変更すれば良いのではないかと思います。
（あいかわらずTestが出来ませんが・・・）
'----
For i = 1 To UBound(tbl1, 1)
If dic.Exists(tbl1(i, 1)) Then
If dic(tbl1(i, 1)) > 0 Then
tbl2(i) = 0
Else
tbl2(i) = dic(tbl1(i, 1))
End If
Else
tbl2(i) = 0
End If
Next i
(ROUGE)
２番目はSample1をちょっと弄くればできそうです。
'----
Sub Sample3()
Dim tbl1, tbl2, x()
Dim i As Integer, n As Integer
With Worksheets("Sheet1")
tbl1 = .Range("P7:AG7")
tbl2 = .Range("P9:AG9")
n = 0
For i = 1 To UBound(tbl1, 2)
If tbl1(1, i) <> "" Then
ReDim Preserve x(n)
x(n) = tbl2(1, i)
n = n + 1
End If
Next
On Error Resume Next
.Range("L9").Value = WorksheetFunction.Sum(x)
If Err.Number = 5 Then Worksheets("Sheet1").Range("L9").Value = 0
On Error GoTo 0
End With
Erase tbl1
Erase tbl2
Erase x
End Sub
(ROUGE)
2をFind Methodで,,,,
Sub IfSum()
Dim r As Range, mySum As Double, rng As Range, n As Integer
Dim ff As String, c As Range, i As Long, myRng() As Range
Set rng = Range("P7:AG7")
Set r = rng.Find("*")
If Not r Is Nothing Then
ff = r.Address
Do
Redim Preserve myRng(i)
Set myRng(i) = r
i = i + 1
Set r = rng.FindNext(r)
Loop Until ff = r.Address
End If
Set rng = Nothing:n =2
For Each c In Range("l9:l892")
For i = 0 To UBound(myRng)
If Not IsDate(myRng(i).Offset(,n)) Then
With CreateObject("VBScript.RegExp")
.Pattern = "^\d+(\.\d+)?$"
If .Test(myRng(i).Offset(,n).Value) Then
mySum = mySum + myRng(i).Offset(,n).Value
End If
End With
End If
Next
c.Value = mySum:mySum = 0 : n = n + 1
Next
End Sub
(seiya)
seiya先生へ ^\d+(\.\d+)$ は、最初に数値が1回以上繰り返されていて、. 1回以上の数値の繰り返しで終わる ということでよろしかったですか？ 例えば 12.34 のような・・・ (ROUGE)
おっと、?が抜けていました。
"^\d+(\.\d+)?$"
でしたね。 [0-9]が一つ以上で始まってそのまま最後まで続くか、少数点一つと[0-9]が一つ以上で終わる文字列 という意味です。
IsNumericは以前 1,,,, とか 1d5 等でTrueを返すと指摘されましたので... (seiya)
> IsNumericは以前 1,,,, とか 1d5 等でTrueを返すと指摘されましたので... これは知らなかった・・・ 正規表現を覚えないと・・・ メタ文字よく分からない (ROUGE)
IsNumeric関数はOffice12で改良されるべきでしょうね... でも傾向として、VBAには今後あまり積極的に改良されないような気が.... (seiya)
ROUGEさん 丸付き数字についての指摘もありがとうございます。 今後、気を付けます。 ご提示頂いたコード早速、試させて頂きました。 1番目の数式については完璧です。 2番目の数式についてですが、私の説明不足でした。すいません… 2番目の数式はL9:L892まで入力されており、 >L9=SUMIF($P$7:$AG$7,"<>",$P9:$AG9) ^^^^^^^^←ここが$P10:$AG11…と言う風に変化しています。 ROUGEさんのご提示して頂いたコードはL9のみの計算でした。
seiyaさんについても同じで、私の説明不足だった為、L9のみの計算でした。 また、質問ですが、 >Find Method とはなんでしょうか？ (マクロの初心者マーク)
私のコードは修正しておきました。 Find Method というのは [編集]-[検索]で該当セルを検索して、その値を（この場合はそこからOffsetで移動しますが） 合計して行くものです。 ちなみに、何故関数をコード化する必要があるのですか？ (seiya)
> ちなみに、何故関数をコード化する必要があるのですか？
σ(^-^;)も興味ある～。
(ROUGE)
'----
Sub Sample3_1()
Dim l_r As Long, i As Long, j As Integer
Dim tbl1, tbl2, tbl3
Dim ttl As Double
With Worksheets("Sheet1")
.Range("L9:L" & Rows.Count).ClearContents
l_r = .Range("P" & Rows.Count).End(xlUp).Row
tbl1 = .Range("P7:AG7")
tbl2 = .Range("P9:AG" & l_r)
ReDim tbl3(1 To UBound(tbl2, 1))
For i = 1 To UBound(tbl2, 1)
ttl = 0
For j = 1 To UBound(tbl2, 2)
If tbl1(1, j) <> "" Then ttl = ttl + tbl2(i, j)
Next j
tbl3(i) = ttl
Next i
.Range("L9").Resize(UBound(tbl3)).Value = Application.Transpose(tbl3)
End With
Erase tbl1
Erase tbl2
Erase tbl3
End Sub
ROUGEさん、seiyaさんありがとうございます。 ちゃんと出来ました。ありがとうございます。 > ちなみに、何故関数をコード化する必要があるのですか？ なぜ関数をコード化する必要があるかと言うと、 １つめはマクロの勉強をしてみたい。 ２つめは今使っているファイルの容量が４Ｍあり、通常は計算方法を手動にしているので特に問題はないのですが、 再計算にとても時間が掛かってしまうのです。 普通に関数をセルの中に入力したままだと、再計算をする度に、全てのシートをしてしまいますよね。 マクロなら、セル内に計算式を置く必要が無く、ファイルの容量を小さく出来ますし、計算もコマンドボタン等を使用すれば、 全てのシートの計算をしなくて済むので、こうなったらマクロでやってみようと思ったわけなのです。 なので、関数をコード化したかったのです。
私からもお伺いしたいのですが、ROUGEさんとseiyaさんはマクロ歴はどのくらいでしょうか？ また、どうやって勉強をしているのでしょうか？ (マクロの初心者マーク)
σ(^-^;)はせいぜい半年程度です。 勉強はこのサイトを見て、試して、の繰り返しですかねぇ。 (ROUGE)
ROUGEさんはマクロ歴が半年位なのですね。 驚きです。もっと何年もやられているのかと思いました。 私も頑張ってみます。 一つ思う事が、数式の関数に対するコードと言うのは、 ある程度決まったものなのでしょうか？ 例えば、IF関数であれば、コードはこんな感じになるとか… (マクロの初心者マーク)
> ↑のｺｰﾄﾞを実行すると、
>>Msgbox
>で指定したメッセージの内容が１度別ウィンドウで表示され、その後、
>>Range("A1").Value =
>で指定した、セルに表示すると言う事ですよね？
そうなんだけど、まるで解ってないみたいなので....。
メッセージボックスが不要なら、MsgBox XXX を書かなければ良いんです。
こちらは、最低でもセルに文字を書き込むことができ、
メッセージボックスで表示された内容をセルに書き込めるであろうと思ってます。
だから、たいていの回答は、デスクトップのパスが知りたいですという質問に対して、
MsgBox CreateObject("WScript.Shell").SpecialFolders("Desktop")
こんな具合に書いてます。
>ちなみに、何故関数をコード化する必要があるのですか？ これは、質問者と意図が違いましたが、場合によっては有効な手段になります。 重い関数をセルに書いたため動作が重いのを避けるため、エクセル関数で 計算させた方が速い。と言った理由だと思っていたので必要性の疑問など 私は、全く沸きませんでしたけど・・・。 BJ
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.