[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『マクロで合計 4』(もみじ坂)
[[20140814193616]]『マクロで合計 1』
[[20140827212357]]『マクロで合計 2』
[[20141024002243]]『マクロで合計 3』
◎ HANA様 レッスン用
◆今回の課題 1. TEL 番号が入力後 TEL番ごとの合計を表示させる方法 & もろもろ
◆集計シート
[a] [b] [c] [d] [e] [f] [g] [h] [K] [1] 1234 [2] NO 商品名前 個数 名称 1 月 2 月 3 月 4 月 合計 [3] A01 10 10 [4] A02 [5] A03 [6] A04 30 30 [7] A05 70 70
◆データーシート
[a] [b] [c] [d] [e] [f] [g] [h] [g] [h] [1] 書類NO 日付 TEL 月検索値 NO NO検索値 単価 cs pc [2] 140124 2014年1月5日 1234 A01 1 10 [3] 140124 2014年1月5日 5678 A01 1 20 [4] 140124 2014年1月5日 1234 A04 0 30 [5] 140124 2014年1月5日 5678 A04 0 40 [6] 140124 2014年3月5日 8768 A02 1 50 [7] 140124 2014年3月5日 8768 A05 1 60 [8] 140124 2014年3月5日 1234 A05 1 70
◆コードの箇条書き
「集計」にSheet5をSetする 「データー」にSheet6をSetする NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する 月最終列 に 入力されているデーターの最終列の列数を取得し代入する A2〜データーの範囲にフィルターをクリアする E3〜データーの範囲の値をクリアする For〜Nextは条件により繰り返えして処理をする(3行目から値がある最終行まで) 集計シートにデーターシートと同じ値があるかを調べる(0=なし、1=1個ある、2以上はダブってる) カウントの結果が0と等しくないとき(1以上のとき) NO検索 に Noが集計シートの何行目に有るかMATCH関数で調べて代入する 月検索 に 日付から月だけを取り出して、集計シートの何列目に有るかMATCH関数で調べて代入する 集計シートの何行目に「NO」があるか・何列目に「月」があるか確認して=クロスするセルに値を足し算する。 集計シートの何3行目〜最終行まで1行ずつ= 最終列のセルにNOの値を足し算する。 カウントの結果が0と等しいとき(1未満のとき) ●●&のNOが登録しています。 A2〜データー範囲に、最終列が空欄の時非表示する
※HELP 機能は搭載していません。
< 使用 Excel:Excel2010、使用 OS:Windows7 >
>「番号が入力された場合」 どこの番号でしょうか? 状況がつかめないです (*´-`*)ゞ (もみじ坂) 2014/11/24(月) 23:24
番号・・・すみません。TELの事です。
★B1が空欄の時 月別&商品別の合計を の、「月別&商品別の合計」は完成したコードで良いと思います。 なので ★B1に 2913(TELNO) を入力されたとき TEL別&商品別&月別 の合計 の方に取り掛かっても良いかと思います。 (HANA) 2014/11/24(月) 23:49
了解です (*´-`*)ゞ
方法は2通りありますが。 Private Sub Worksheet_Change(ByVal Target As Range)
を使用した方がよさげでしょうか? やはりボタンクリックの方がいいんでしょうか? (もみじ坂) 2014/11/25(火) 22:12
いま ボタンクリックで動くコードを作っていますので マクロを実行した時点で(ボタンをクリックした時点で) B1セルにTELが入力されていたら TEL別&商品別&月別 の合計 入力されていなかったら 月別&商品別の合計 と 自動で集計結果をかえる様にしてもらうのが良いと思います。
とにかく、実行方法を考える前に 処理内容を考えないといけないですよ? (HANA) 2014/11/25(火) 22:58
ここが一番困ってるんですよ。
合計条件が3つ。。。。
IF関数でできるのでしょうか? ^^;;
できることを願って ≧人≦
★ が追加部分です。
NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する 月最終列 に 入力されているデーターの最終列の列数を取得し代入する A2〜データーの範囲にフィルターをクリアする E3〜データーの範囲の値をクリアする For〜Nextは条件により繰り返えして処理をする(3行目から値がある最終行まで) 集計シートにデーターシートと同じ値があるかを調べる(0=なし、1=1個ある、2以上はダブってる) カウントの結果が0と等しくないとき(1以上のとき) NO検索 に Noが集計シートの何行目に有るかMATCH関数で調べて代入する 月検索 に 日付から月だけを取り出して、集計シートの何列目に有るかMATCH関数で調べて代入する
★TEL索 に 集計シートのB1と同じTELがデーターシートの何行目にあるかMATCH関数で調べて代入する
★if TEL索 = 集計シートB1 と同じとき
集計シートの何行目に「NO」があるか・何列目に「月」があるか確認して=クロスするセルに値を足し算する。 集計シートの何3行目〜最終行まで1行ずつ= 最終列のセルにNOの値を足し算する。
カウントの結果が0と等しいとき(1未満のとき) ●●&のNOが登録しています。 A2〜データー範囲に、最終列が空欄の時非表示する
うTwT 分かりません。。。。
もうちょっと考えます。。。
(もみじ坂) 2014/11/27(木) 00:48
えっっ? 一応お伺いしますが、たとえば 集計シートの、集計結果部分(E3:K7)が入力されていない状態の表と データーシートの表が紙に印刷されたもの を渡されて「集計しておいてね」と言われたら 集計できますよね?
つまり、「エクセルを使わずに集計できるか?」って事ですが。。。 (HANA) 2014/11/27(木) 08:19
>エクセルを使わずに集計できるか?
こんな感じでしょうか。
TEL別&商品別&月別 の合計 の場合
B1のセルと同じTEL番号を絞ってから。
商品番号別 の 月別に合計して。
集計シートに書き込みます。 (もみじ坂) 2014/11/27(木) 19:57
「エクセルを使わずに」と言う事ですが >TEL番号を絞ってから。 ってのは具体的にどういう事ですか?
もみじ坂さんがやる方法を 私も出来るように説明してもらえませんか? (HANA) 2014/11/27(木) 20:57
B1のTEL番号を「1234」として。
データーシートのC列2行目から「1234」のみを探す。
[2]行目 1234 だったので → 1月 で A01 の商品なので、 集計シート E3 に 値 10 を書き込む [4]行目 1234 だったので → 1月 で、A04 の商品なので、 集計シート E7 に 値 30 を書き込む [8]行目 1234 だったので → 3月 で、A05 の商品なので、 集計シート G7 に 値 70 を書き込む
順々に1行ずつ「1234」のTELのみのものを絞って計算していく方法です。
◆集計シート [a] [b] [c] [d] [e] [f] [g] [h] [K] [1] 1234 [2] NO 商品名前 個数 名称 1 月 2 月 3 月 4 月 合計 [3] A01 10 10 [4] A02 [5] A03 [6] A04 30 30 [7] A05 70 70
◆データーシート [a] [b] [c] [d] [e] [f] [g] [h] [i] [j] [1] 書類NO 日付 TEL 月検索値 NO NO検索値 単価 cs pc [2] 140124 2014年1月5日 1234 A01 1 10 [3] 140124 2014年1月5日 5678 A01 1 20 [4] 140124 2014年1月5日 1234 A04 0 30 [5] 140124 2014年1月5日 5678 A04 0 40 [6] 140124 2014年3月5日 8768 A02 1 50 [7] 140124 2014年3月5日 8768 A05 1 60 [8] 140124 2014年3月5日 1234 A05 1 70 (もみじ坂) 2014/11/27(木) 22:09
> データーシートのC列2行目から「1234」のみを探す。 どうやって探しますか?
C2セルから順に一つずつ「1234」であるかどうかを確認しますか? それとも、それ以外の方法ですか? (HANA) 2014/11/28(金) 10:23
>どうやって探しますか?
C2セルから順に一つずつ「1234」であるかどうかを確認します。
(もみじ坂) 2014/11/28(金) 14:15
つまり、いろいろ端折ったりしながら書いてますが
For i = 2 to 最後の行 If Range("C" & i).Value = 1234 Then 集計シートに書き込む End If Next
こんな感じで作業を進めるって事ですよね?
それで >★TEL索 に 集計シートのB1と同じTELがデーターシートの何行目にあるかMATCH関数で調べて代入する >★if TEL索 = 集計シートB1 と同じとき の作業は何の目的で、どのタイミングで行うのですか? (HANA) 2014/11/28(金) 14:20
For i = 2 to 最後の行 If Range("C" & i).Value = 1234 Then 集計シートに書き込む End If Next
ではなく前回と同じように ↓数字のみを拾ってくれるようIsNumeric使うと思いました、 でも、IsNumericを使わなくてもエラーにならずにできるんですね (。´・ω・) IsNumericをこだわりすぎているのでしょうか。。。。
For i = 3 To データー.Cells(Rows.Count, 1).End(xlUp).Row
'TEL検索 に 集計シートのB1と同じTELがデーターシートの何行目にあるかMATCH関数で調べて代入する TEL検索 = Application.Match(データー.Cells(i, "C"), 集計.Range("B1"), 0)
''セルの値が数値かどうかを判断する
If IsNumeric(TEL検索) Then '集計シートに書き込む MsgBox データー.Cells(i, "C").Value & "と" & 集計.Range("B1").Value & "=同じ。"
End If Next i (もみじ坂) 2014/11/28(金) 17:58
あ!ΣΣ(゚д゚"")! なるほどそういうことですね。 謎が解けました。
NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する 月最終列 に 入力されているデーターの最終列の列数を取得し代入する A2〜データーの範囲にフィルターをクリアする E3〜データーの範囲の値をクリアする For〜Nextは条件により繰り返えして処理をする(3行目から値がある最終行まで)
NO検索 に Noが集計シートの何行目に有るかMATCH関数で調べて代入する 月検索 に 日付から月だけを取り出して、集計シートの何列目に有るかMATCH関数で調べて代入する ★TEL索 に 集計シートのB1と同じTELがデーターシートの何行目にあるかMATCH関数で調べて代入する ★TEL索の結果が数値かどうかを判断する
集計シートにデーターシートと同じ値があるかを調べる(0=なし、1=1個ある、2以上はダブってる) カウントの結果が0と等しくないとき(1以上のとき)
集計シートの何行目に「NO」があるか・何列目に「月」があるか確認して=クロスするセルに値を足し算する。 集計シートの何3行目〜最終行まで1行ずつ= 最終列のセルにNOの値を足し算する。 カウントの結果が0と等しいとき(1未満のとき) ●●&のNOが登録しています。 A2〜データー範囲に、最終列が空欄の時非表示する
IF関数で調べる内容の位置も気を付けないといけないですね。 条件が TEL 月 NO の順なので、その順番にしないとおかしなことに。。 (もみじ坂) 2014/11/28(金) 18:18
えっと 「なんでわざわざ MATCH関数つかうの?」 って事なんですが。
=MATCH(データ!C1,集計!$B$1,0) って式ですよね? これがエラーでない=「データ!C1と集計!$B$1が同じ」 とわかる と言う考えなんでしょうけど。
たとえば、ワークシート上で C1セルとB1セルが同じだったら「同じ」違ったら「違う」 と言う式を作る時 =IF(ISERROR(MATCH(C1,B1,0)),"違う","同じ") って式にはしませんよね?
=IF(C1=B1,"同じ","違う") =IF(C1<>B1,"違う","同じ") の様なシンプルな式にすると思いますが、どうですか? (HANA) 2014/11/28(金) 19:05
>「なんでわざわざ MATCH関数つかうの?」 なぜか使わないと分からないと思ってしまったんですよ TwT
>「データ!C1と集計!$B$1が同じ」を調べるには そんな感じです。
> の様なシンプルな式にすると思いますが、どうですか? はい =IF(C1=B1,"同じ","違う") を使いますね。
難しく考えてしまったんですね ≧x≦ (もみじ坂) 2014/11/28(金) 20:20
>難しく考えてしまったんですね ですね。
でも、いちばん基本は「自分だったらどうするか」です。
もしかして >C2セルから順に一つずつ「1234」であるかどうかを確認します。 この段階から MATCH関数でエラーになったら。。。 って考えてましたか?
近くで似たような事をすると、それを使いたいと思ってしまいますが 本当にそれを使うのが良いのかは 十分考えてもらうのが良いと思います。
さて >=IF(C1=B1,"同じ","違う") >を使いますね。 と言う事なので、 >★TEL索 に 集計シートのB1と同じTELがデーターシートの何行目にあるかMATCH関数で調べて代入する >★TEL索の結果が数値かどうかを判断する 部分を書き直してみてもらえますか? (HANA) 2014/11/28(金) 22:30
後はB1が空欄とTEL番号がある時の処理ですね。
★変更しました。
NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する 月最終列 に 入力されているデーターの最終列の列数を取得し代入する A2〜データーの範囲にフィルターをクリアする E3〜データーの範囲の値をクリアする For〜Nextは条件により繰り返えして処理をする(3行目から値がある最終行まで) NO検索 に Noが集計シートの何行目に有るかMATCH関数で調べて代入する 月検索 に 日付から月だけを取り出して、集計シートの何列目に有るかMATCH関数で調べて代入する
★データーシート3行目〜順に一つずつ集計シートのB1と同じTEL番号であるかどうかを確認する。
集計シートにデーターシートと同じ値があるかを調べる(0=なし、1=1個ある、2以上はダブってる) カウントの結果が0と等しくないとき(1以上のとき) 集計シートの何行目に「NO」があるか・何列目に「月」があるか確認して=クロスするセルに値を足し算する。 集計シートの何3行目〜最終行まで1行ずつ= 最終列のセルにNOの値を足し算する。 カウントの結果が0と等しいとき(1未満のとき) ●●&のNOが登録しています。 A2〜データー範囲に、最終列が空欄の時非表示する (もみじ坂) 2014/11/28(金) 23:04
やり直しですね。。。 一回クリアしたところは、クリアしてもらいたい所ですが。
その順番だと、NOがなかった時に、 > NO検索 に Noが集計シートの何行目に有るかMATCH関数で調べて代入する でエラーになりますよ?
それから、もういちど「自分だったらどうするか」順番に考えてみて下さい。
また、考えた通りにご自身で作業をしてみて下さい。 おかしなところに気づける。。。と思います。 (HANA) 2014/11/28(金) 23:24
> > NO検索 に Noが集計シートの何行目に有るかMATCH関数で調べて代入する (。´・ω・)ん? これで NOがなくても計算はできてますよ〜 どこが違うのだろう検証してみます。
Sub test13() '計算条件:集計B1 に入力TELのもののみ計算する ' 整数型 Integer ' 長整数型 Long ' 日付型 Date ' オブジェクト型 Object ' 文字列型 String ' バリアント型 Variant
Dim NO検索 As Long, NO最終行 As Long Dim 月検索 As Long, 月最終列 As Long Dim TEL検索 As Long Dim i As Long Dim 行番号 As Long Dim 集計 As Worksheet Dim データー As Worksheet Dim カウント As Long
'「集計」にSheet5をSetする Set 集計 = Worksheets("Sheet5")
'「データー」にSheet6をSetする Set データー = Worksheets("Sheet6")
'NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する NO最終行 = 集計.Cells(Rows.Count, 1).End(xlUp).Row
'月最終列 に 入力されているデーターの最終列の列数を取得し代入する 月最終列 = 集計.Cells(2, Columns.Count).End(xlToLeft).Column
'A2〜データーの範囲にフィルターをクリアする 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter Field:=月最終列
'E3〜データーの範囲の値をクリアする 集計.Range(集計.Cells(3, 5), 集計.Cells(NO最終行, 月最終列)).ClearContents
'For〜Nextは条件により繰り返えして処理をする(3行目から値がある最終行まで) For i = 3 To データー.Cells(Rows.Count, 1).End(xlUp).Row
'1行ずつデーターシート と 集計シートB1 と同じ番号を絞る If データー.Range("C" & i).Value = 集計.Range("B1").Value Then
'集計シートにデーターシートと同じ値があるかを調べる(0=なし、1=1個ある、2以上はダブってる) カウント = WorksheetFunction.CountIf(集計.Range("A3:A" & Rows.Count), データー.Cells(i, "E"))
'カウントの結果が0と等しくないとき(1以上のとき) If カウント <> 0 Then
'NO検索 に Noが集計シートの何行目に有るかMATCH関数で調べて代入する NO検索 = Application.Match(データー.Cells(i, "E"), 集計.Range("A:A"), 0)
'月検索 に 日付から月だけを取り出して、集計シートの何列目に有るかMATCH関数で調べて代入する 月検索 = Application.Match(Month(データー.Cells(i, "B")), 集計.Range("2:2"), 0)
'TEL検索 に 集計シートのB1と同じTELがデーターシートの何行目にあるかMATCH関数で調べて代入する TEL検索 = Application.Match(データー.Cells(i, "C"), 集計.Range("B1"), 0)
'集計シートの何行目に「NO」があるか・何列目に「月」があるか確認して=クロスするセルに値を足し算する。 集計.Cells(NO検索, 月検索) = 集計.Cells(NO検索, 月検索) + データー.Cells(i, "j")
'集計シートの何3行目〜最終行まで1行ずつ= 最終列のセルにNOの値を足し算する。 集計.Cells(NO検索, 月最終列) = 集計.Cells(NO検索, 月最終列) + データー.Cells(i, "j")
'カウントの結果が0と等しいとき(1未満のとき) Else
'●●&のNOが登録しています。 MsgBox データー.Cells(i, "E").Value & "のNOが登録していません。"
End If End If Next i
'A2〜データー範囲に、最終列が空欄の時非表示する 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter Field:=月最終列, Criteria1:="<>"
End Sub
(もみじ坂) 2014/11/29(土) 19:00
そのコードなら、エラーになりませんが。 2014/11/28(金) 23:04 の説明とは違っているのはわかってますか?
それから、先頭に Option Explicit ありますか? (HANA) 2014/11/29(土) 20:09
上のをコピーしたからいけなかったんですね。
今度コードにします ^^;;
Option Explicit はちゃんとありますよ ^−^
(もみじ坂) 2014/11/29(土) 21:45
>あ!順番がずれてる (*゚Å゚;*) >今度コードにします ^^;; 説明の方が正しくて、test13の方が間違いですか? 。。。まぁ、コードにして動かしてみてもらうとわかると思いますが。
>Option Explicit はちゃんとありますよ ^−^ あっ、本当ですね。変数の宣言 Dim TEL検索 As Long ありました。 すみません、見落としてました。
でも、この変数って書き込んでますが TEL検索 = Application.Match(データー.Cells(i, "C"), 集計.Range("B1"), 0) 使ってないですよね? (HANA) 2014/11/29(土) 21:57
>使ってないですよね?
そうですね。消しておきます。
そこを忘れてました。 ^^;;
Sub test13() '計算条件:集計B1 に入力TELのもののみ計算する ' 整数型 Integer ' 長整数型 Long ' 日付型 Date ' オブジェクト型 Object ' 文字列型 String ' バリアント型 Variant
Dim NO検索 As Long, NO最終行 As Long Dim 月検索 As Long, 月最終列 As Long Dim i As Long Dim 行番号 As Long Dim 集計 As Worksheet Dim データー As Worksheet Dim カウント As Long
'「集計」にSheet5をSetする Set 集計 = Worksheets("Sheet5")
'「データー」にSheet6をSetする Set データー = Worksheets("Sheet6")
'NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する NO最終行 = 集計.Cells(Rows.Count, 1).End(xlUp).Row
'月最終列 に 入力されているデーターの最終列の列数を取得し代入する 月最終列 = 集計.Cells(2, Columns.Count).End(xlToLeft).Column
'A2〜データーの範囲にフィルターをクリアする 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter Field:=月最終列
'E3〜データーの範囲の値をクリアする 集計.Range(集計.Cells(3, 5), 集計.Cells(NO最終行, 月最終列)).ClearContents
'For〜Nextは条件により繰り返えして処理をする(3行目から値がある最終行まで) For i = 3 To データー.Cells(Rows.Count, 1).End(xlUp).Row
'1行ずつデーターシート と 集計シートB1 と同じ番号を絞る If データー.Range("C" & i).Value = 集計.Range("B1").Value Then
'集計シートにデーターシートと同じ値があるかを調べる(0=なし、1=1個ある、2以上はダブってる) カウント = WorksheetFunction.CountIf(集計.Range("A3:A" & Rows.Count), データー.Cells(i, "E"))
'カウントの結果が0と等しくないとき(1以上のとき) If カウント <> 0 Then
'NO検索 に Noが集計シートの何行目に有るかMATCH関数で調べて代入する NO検索 = Application.Match(データー.Cells(i, "E"), 集計.Range("A:A"), 0)
'月検索 に 日付から月だけを取り出して、集計シートの何列目に有るかMATCH関数で調べて代入する 月検索 = Application.Match(Month(データー.Cells(i, "B")), 集計.Range("2:2"), 0)
'集計シートの何行目に「NO」があるか・何列目に「月」があるか確認して=クロスするセルに値を足し算する。 集計.Cells(NO検索, 月検索) = 集計.Cells(NO検索, 月検索) + データー.Cells(i, "j")
'集計シートの何3行目〜最終行まで1行ずつ= 最終列のセルにNOの値を足し算する。 集計.Cells(NO検索, 月最終列) = 集計.Cells(NO検索, 月最終列) + データー.Cells(i, "j")
'カウントの結果が0と等しいとき(1未満のとき) Else
'●●&のNOが登録しています。 MsgBox データー.Cells(i, "E").Value & "のNOが登録していません。"
End If End If Next i
'A2〜データー範囲に、最終列が空欄の時非表示する 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter Field:=月最終列, Criteria1:="<>"
End Sub (もみじ坂) 2014/11/29(土) 22:42
良いと思います。
あとは、これは TEL番号が入力された時には集計できますが 入力されていない時に「番号にかかわらず集計」ってのはできません。
なので '1行ずつデーターシート と 集計シートB1 と同じ番号を絞る If データー.Range("C" & i).Value = 集計.Range("B1").Value Then ここの条件をもう一度考えてみてもらうと良いと思います。
集計.Range("B1")に入力がない か 集計.Range("B1")に入力があって、データー.Range("C" & i).Value = 集計.Range("B1").Valueの時 に、その行(iの行)は集計の対象ですので、Thenの方に分岐してもらいたいです。
関数だと OR(集計!$B$1="",AND(集計!$B$1<>"",データ!C3=集計!$B$1)) って感じですね。 (HANA) 2014/11/29(土) 22:52
>OR(集計!$B$1="",AND(集計!$B$1<>"",データ!C3=集計!$B$1)) なんとなく ↓ と似てますか?
If 条件式1 Then 条件式1を満たした場合の処理 ElseIf 条件式2 Then 条件式2を満たした場合の処理 Else 条件式1と条件式2を満たさなかった場合の処理 End If
か
If 条件式1 And 条件式2 Then 条件式1,2が真だった場合 End If
と思ったんですが、なんか違う気が。。 (もみじ坂) 2014/11/29(土) 23:13
条件式1 集計!$B$1="" 条件式2 AND(集計!$B$1<>"",データ!C3=集計!$B$1)
と考えておられるなら、最初の方ですが 条件式1を満たした場合の処理 条件式2を満たした場合の処理 の所には、まったく同じコードを書くことになりますよね?
で、下に書いてある方だと、ANDが使ってあるので 条件式1と条件式2が【共に成立した時】ってなってます。 ですから、なにか違うと思います。
条件式1と条件式2は【どちらか成立した時】で 条件式2の中に 条件式2-1と条件式2-2が【共に成立した時】があります。
単純なサンプルコードをいろいろ作って、トライしてみて下さい。 (HANA) 2014/11/29(土) 23:27
>条件式1と条件式2は【どちらか成立した時】で If 集計.Range("B1").Value = "" Or 集計.Range("B1").Value <> "" Then
>条件式2の中に 条件式2-1と条件式2-2が【共に成立した時】があります。 ここで、すごく悩みました。。
コードで考えましたら、よくわからなくなりました ^^;; プチテストをやってみましたが、 こういうことでしょか?
*** Sub testif_2()********************************************************************** Dim 集計 As Worksheet
Set 集計 = Worksheets("Sheet5")
If 集計.Range("B1").Value = "" Or 集計.Range("B1").Value <> "" Then If 集計.Range("B1").Value = "" Then MsgBox 集計.Range("B1").Value & "空欄です。" ElseIf 集計.Range("B1").Value <> "" Then MsgBox 集計.Range("B1").Value & "です。" End If End If
End Sub **********************************************************************
しかし、考え方をどう練っても処理1と処理2は全部同じコードを書かなければ実行できない状況です、
****条件1と条件2 ?**********************************************************************
If データー.Range("C" & i).Value = 集計.Range("B1").Value Then カウント = WorksheetFunction.CountIf(集計.Range("A3:A" & Rows.Count), データー.Cells(i, "E")) If カウント <> 0 Then NO検索 = Application.Match(データー.Cells(i, "E"), 集計.Range("A:A"), 0) 月検索 = Application.Match(Month(データー.Cells(i, "B")), 集計.Range("2:2"), 0)
集計.Cells(NO検索, 月検索) = 集計.Cells(NO検索, 月検索) + データー.Cells(i, "j") 集計.Cells(NO検索, 月最終列) = 集計.Cells(NO検索, 月最終列) + データー.Cells(i, "j")
End If End If **********************************************************************
を指してますか? それとも
****条件1と条件2 ?********************************************************************** 集計.Cells(NO検索, 月検索) = 集計.Cells(NO検索, 月検索) + データー.Cells(i, "j") 集計.Cells(NO検索, 月最終列) = 集計.Cells(NO検索, 月最終列) + データー.Cells(i, "j") **********************************************************************
のみになりますか? そこでも迷いが。。。 (もみじ坂) 2014/12/03(水) 00:27
作りたいのは↓の様なコードです。
If 条件式1【OR】条件式2 Then MsgBox "処理実行" End If
条件式2 は、条件式2-1【AND】条件式2-2
なので、 If 条件式1【OR】(条件式2-1【AND】条件式2-2) Then MsgBox "処理実行" End If
そして、 条件式1 集計.Range("B1").Value = "" 条件式2-1 集計.Range("B1").Value <> "" 条件式2-2 データー.Range("C" & i).Value = 集計.Range("B1").Value ですね。 (HANA) 2014/12/03(水) 22:34
え?たった一行で事が済んじゃいました (´・ω・`) 二日間悩んだ私はいったい。。。 (HANA)様の発想もそうですが、 IF関数てすごいですね (´。✪ω✪。`) かなり感動しちゃいました〜 ♪
http://homepage1.nifty.com/rucio/main/shokyu/jugyou5.htm
ここで読んでるのに ≧≦ 日本語の理解ができなかったことと 条件2−2を思いつかなかったので、繋がらなかったんですねキット
Sub test14()
Dim NO検索 As Long, NO最終行 As Long Dim 月検索 As Long, 月最終列 As Long Dim i As Long Dim 行番号 As Long Dim 集計 As Worksheet Dim データー As Worksheet Dim カウント As Long
'「集計」にSheet5をSetする Set 集計 = Worksheets("Sheet5")
'「データー」にSheet6をSetする Set データー = Worksheets("Sheet6")
'NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する NO最終行 = 集計.Cells(Rows.Count, 1).End(xlUp).Row
'月最終列 に 入力されているデーターの最終列の列数を取得し代入する 月最終列 = 集計.Cells(2, Columns.Count).End(xlToLeft).Column
'A2〜データーの範囲にフィルターをクリアする 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter Field:=月最終列
'E3〜データーの範囲の値をクリアする 集計.Range(集計.Cells(3, 5), 集計.Cells(NO最終行, 月最終列)).ClearContents
'For〜Nextは条件により繰り返えして処理をする(3行目から値がある最終行まで) For i = 3 To データー.Cells(Rows.Count, 1).End(xlUp).Row
'集計シートのB1が空欄の時か(集計シートのB1が空欄じゃない時 で データーシートと集計シートB1が同じ時) If 集計.Range("B1").Value = "" Or (集計.Range("B1").Value <> "" And データー.Range("C" & i).Value = 集計.Range("B1").Value) Then
'集計シートにデーターシートと同じ値があるかを調べる(0=なし、1=1個ある、2以上はダブってる) カウント = WorksheetFunction.CountIf(集計.Range("A3:A" & Rows.Count), データー.Cells(i, "E"))
'カウントの結果が0と等しくないとき(1以上のとき) If カウント <> 0 Then
'NO検索 に Noが集計シートの何行目に有るかMATCH関数で調べて代入する NO検索 = Application.Match(データー.Cells(i, "E"), 集計.Range("A:A"), 0)
'月検索 に 日付から月だけを取り出して、集計シートの何列目に有るかMATCH関数で調べて代入する 月検索 = Application.Match(Month(データー.Cells(i, "B")), 集計.Range("2:2"), 0)
'集計シートの何行目に「NO」があるか・何列目に「月」があるか確認して=クロスするセルに値を足し算する。 集計.Cells(NO検索, 月検索) = 集計.Cells(NO検索, 月検索) + データー.Cells(i, "j")
'集計シートの何3行目〜最終行まで1行ずつ= 最終列のセルにNOの値を足し算する。 集計.Cells(NO検索, 月最終列) = 集計.Cells(NO検索, 月最終列) + データー.Cells(i, "j")
'カウントの結果が0と等しいとき(1未満のとき) Else
'●●&のNOが登録しています。 MsgBox データー.Cells(i, "E").Value & "のNOが登録していません。" End If End If
Next i
'A2〜データー範囲に、最終列が空欄の時非表示する 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter Field:=月最終列, Criteria1:="<>"
End Sub (もみじ坂) 2014/12/05(金) 00:55
>If 集計.Range("B1").Value = "" Or (集計.Range("B1").Value <> "" And データー.Range("C" & i).Value = 集計.Range("B1").Value) Then
これの意味は 3つの条件を勝手に判断してくれている感じでしょうか?
B1が空欄の時 → 以下処理へ B1の内容と同じやつだけ取り出して → 以下処理へ
簡単に説明するとこんな感じであってますか? (もみじ坂) 2014/12/05(金) 01:04
>ここで読んでるのに ≧≦ 日本語の理解ができなかったことと >条件2−2を思いつかなかったので、繋がらなかったんですねキット なんでわからなかったですかね?
そのページに If K = 5 Or M = 8 And P = 31 Then MsgBox "成就されました。" End If
Or と And の両方が同じ文で使われたときは And の方が強い結合力をしめします。 つまり、この例で「成就されました。」と表示されるのは 「k=5 の時」か「M=8 で P=31の時」のどちらかです。 と書いてあります。
また、それまでに私は >>条件式1 集計!$B$1="" >>条件式2 AND(集計!$B$1<>"",データ!C3=集計!$B$1) >>条件式1と条件式2は【どちらか成立した時】で >>条件式2の中に 条件式2-1と条件式2-2が【共に成立した時】があります と書いてますよね。
二つを上下にならべて書いてみると 「k=5 の時」か「 M=8 で P=31 の時」 集計!B1=""の時 か 集計!$B$1<>"" で データ!C3=集計!$B$1 の時 ですので、 If K = 5 Or M = 8 And P = 31 Then If 集計.Range("B1").Value = "" Or (集計.Range("B1").Value <> "" And データー.Range("C" & i).Value = 集計.Range("B1").Value) Then 同じですよね。
ここの所は見てませんでしたか? なぜ見てませんでしたか?見てたけど、今回の件とは違うと思いましたか? こちらに理由を書いてもらう必要は有りませんが、 自己問答し、ご自身の癖をつかんでおいてもらうのが良いと思います。
あるページの 必要な個所を見逃していたのでは そのページを見たことにはならないです。
>簡単に説明するとこんな感じであってますか? 「取り出して」と言う言葉にどういう表現が含まれていますか?
その段階で判断しているのは、「i行目は処理する対象かどうか?」と言う事です。 B1が空欄であれば、i行目は処理する対象です。 B1が空欄でない場合は、C列の値がB1セルと同じだったら、i行目は処理する対象です。
もしも B1が空欄の時 → 以下処理へ B1が空欄でなくても、(C列の値が)B1の内容と同じ時 → 以下処理へ でしたら、そうだと思いますし 「取り出して」に特に意味が無いのなら B1が空欄の時その行を取り出して → 以下処理へ B1が空欄でなくても、B1の内容と同じならその行を取り出して → 以下処理へ この様に書いてあれば、そうかな と思います。
ご自身でやってみる時、「取り出し」たりしませんよね? データを見て処理をするかしないか「判断」するだけだと思いますが。。。
<<B1セルの値を見る。>>、<<C列i行目の値を見る。>>と言う事を「取り出して」と表現していますか? そうならやはり >B1が空欄の時 → 以下処理へ の方にも「取り出して」のワードが出てこないとおかしいですよね? (HANA) 2014/12/05(金) 09:10
>なんでわからなかったですかね? ホント、返す言葉もありません TxT
根本のところの考えが違うからと思います。
HANA様は >If 集計.Range("B1").Value = "" Or (集計.Range("B1").Value <> "" And データー.Range("C" & i).Value = 集計.Range("B1").Value) Then
1.空欄なら → 以下の処理を 2.空欄じゃないなら B1と内容が同じ時 → 以下の処理を
を考えているんですが。
どうも私
まず、B1が空欄かどうかを調べてから
空欄なら → 処理1を 空欄じゃないなら → 処理2を
と考えているので、
>If K = 5 Or M = 8 And P = 31 Then を見てもピンとこなかったんです
>ご自身でやってみる時、「取り出し」たりしませんよね? 取り出していないですな。 上から順に同じ値をチェックしているだけですものね。
>B1が空欄の時 → 以下処理へ >B1が空欄でなくても、(C列の値が)B1の内容と同じ時 → 以下処理へ
こういい方の方がしっくりくるような気がします。 (もみじ坂) 2014/12/05(金) 23:37
>まず、B1が空欄かどうかを調べてから >空欄なら → 処理1を >空欄じゃないなら → 処理2を >と考えているので、 たとえば IF(集計!$B$1="","処理する",IF(データ!C3=集計!$B$1,"処理する","処理しない")) こんな感じですか?
これだと、"処理する"が二回出てきますよね? でも IF(OR(集計!$B$1="",AND(集計!$B$1<>"",データ!C3=集計!$B$1)),"処理する","処理しない") これだと、"処理する"は一回しか出てきません。
ちなみに、最初の方をコード風にすると If 集計.Range("B1").Value = "" Then Msgbox "処理する" Else If データー.Range("C" & i).Value = 集計.Range("B1").Value Then Msgbox "処理する" Else Msgbox "処理しない" End If End If こんな感じですかね。 「処理する」が2回出てくるのは変わりません。
今回作った様なコードにすると、「処理する」は1回出てくるだけですみます。
マクロ特有の事柄もありますが、処理を考える段階では 「関数での考え方」と共通の部分もたくさんあります。 それも踏まえて「自分で処理するときどうするか?」考えてみて下さい。
その時、処理を考える言葉の中に極力いらない言葉を混ぜない様にして下さい。 具体的には今回の「取り出し」ですね。
さて、完成したコードの話に戻りますが オートフィルタは使用者が自由に変更できる状態ですよね? すると 'A2〜データーの範囲にフィルターをクリアする 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter Field:=月最終列 最後の列でしかフィルタがかかっていないはずですが それ以外の場所で絞り込みが行われているかもしれません。 もみじ坂さんが処理をする時は、他の列でもフィルタがかかっていたらクリアしますよね?
フィルタリングを解除して、すべて表示させるコードがありますので それを探して変更してもらうと良いと思います。 (HANA) 2014/12/06(土) 22:58
>こんな感じですか? >こんな感じですかね。 「処理する」が2回出てくるのは変わりません。
はいそれです。 なので、「どう練っても同じ処理が2回でてしまう」 と書きました (´゚д゚`)
>それも踏まえて「自分で処理するときどうするか?」考えてみて下さい。 はい。分からない部分は質問するようにします、 私だけで考えると思い込みは激しいので、発想の起点を替えるのはまだ克服できないみたいです TxT
>フィルタリングを解除して 確か、最後に指定しなければフィルター自体をクリアしてくれます。 これとは違う方法でしょうか? 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter (もみじ坂) 2014/12/07(日) 16:54
たとえば Worksheets("Sheet1").AutoFilterMode = False これで、Sheet1にオートフィルタが設定されていたら解除できます。
すでに書きました様に、フィルタリングされた状態で 集計.Cells(Rows.Count, 1).End(xlUp).Row で取得できる行番号は、その表の最下行とは限りません。 ですから、先にオートフィルタを解除しておいて 最下行番号を取得するのが安全じゃないかと思います。
>分からない部分は質問するようにします。 そうですね。 でも、先に自分で調べてみて下さいね。
エクセルを使う事だって、最初は何もわからなかったと思います。 でも、やりたい事をどうやって実現するか調べたり、考えたり 繰り返しやっているうちに、色々な事が出来るようになると思います。 マクロだって、同じだと思います。
さて、コードはオートフィルタの所と 最終行の取得場所を考えてみて下さい。
それから、現在ループ処理はデータシートを1行目から処理しています。 でも当初は、集計シートから処理をしようとしていましたよね? もう一度、何をしようとしていたのか 考えてみてもらえますか?
「自分だったらどうするか」と考えるのはわかりましたよね? (HANA) 2014/12/07(日) 19:12
フィルター解除といってもいろいろありますね
>集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter ↑より↓のほうがスマートですね。ワザワザいろいろと調べずにすみます ^^;;
ActiveSheet.AutoFilterMode = False Worksheets("Sheet1").AutoFilterMode = False
>でも、先に自分で調べてみて下さいね。
もちろんです。調べるの結構好きです。
>さて、コードはオートフィルタの所と 最終行の取得場所を考えてみて下さい。 ここですね。クリアの部分ではないですよね。最終行はしようしなくなったので、
「 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter Field:=月最終列, Criteria1:="<>"」
「NO最終行 = 集計.Cells(Rows.Count, 1).End(xlUp).Row」
念のための確認ですが、
>もう一度、何をしようとしていたのか 考えてみてもらえますか? は、個々の部分を >コードはオートフィルタの所と 最終行の取得場所を考えてみて下さい。
「自分だったらどうするか」をもう一度考えるでよろしいでしょうか? (もみじ坂) 2014/12/07(日) 19:51
あぁ、そうですね。 先に >コードはオートフィルタの所と 最終行の取得場所を考えてみて下さい。 をやってみて下さい。 'NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する NO最終行 = 集計.Cells(Rows.Count, 1).End(xlUp).Row '月最終列 に 入力されているデーターの最終列の列数を取得し代入する 月最終列 = 集計.Cells(2, Columns.Count).End(xlToLeft).Column 'A2〜データーの範囲にフィルターをクリアする 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter Field:=月最終列 の所の事です。
「自分だったらどうするか」と言う事ではなく 何を、どの順番で処理するのが良いのか 考えて下さい。 (HANA) 2014/12/07(日) 20:19
'NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する NO最終行 = 集計.Cells(Rows.Count, 1).End(xlUp).Row '月最終列 に 入力されているデーターの最終列の列数を取得し代入する 月最終列 = 集計.Cells(2, Columns.Count).End(xlToLeft).Column 'A2〜データーの範囲にフィルターをクリアする 集計.Range(集計.Cells(2, 1), 集計.Cells(NO最終行, 月最終列)).AutoFilter Field:=月最終列
考えるというのは、以前 Rows.Count を使用すると
不具合になるかも。といったことに関係あるんですよねキット。
そうすると、コードの処理内容を考え直さないといけなくなりますよね。
( 何を、どの順番で処理するのが良いのか)
最終行を使わない場合の処理方法が必要になるということになるんでしょか?
考える個所は?
それとも、書き方をもう少しスマートする方法を考えるのか。
'集計シートのフィルターを解除する 集計.AutoFilterMode = False
'2行目の最終列が空欄の時、非常時する。 集計.Rows(2).AutoFilter Field:=月最終列, Criteria1:="<>"
。。。念のために
(もみじ坂) 2014/12/10(水) 00:05
2014/12/07(日) 19:12の投稿です。 読んで欲しいので、もう一度掲載します。
たとえば Worksheets("Sheet1").AutoFilterMode = False これで、Sheet1にオートフィルタが設定されていたら解除できます。
すでに書きました様に、フィルタリングされた状態で 集計.Cells(Rows.Count, 1).End(xlUp).Row で取得できる行番号は、その表の最下行とは限りません。 ですから、先にオートフィルタを解除しておいて・・・・・・・☆オートフィルタ解除 最下行番号を取得するのが安全じゃないかと思います。・・・・★最下行番号取得
今出来ている処理の流れは 'NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する・・・★最下行番号取得 '月最終列 に 入力されているデーターの最終列の列数を取得し代入する 'A2〜データーの範囲にフィルターをクリアする・・・・・・・・・・・・・・・☆オートフィルタ解除 になってますので、単純に ☆の所のコードを書き換えただけでは 順番が合いません。
それに気づいてもらえるか不安だったので、あえて >何を、どの順番で処理するのが良いのか 考えて下さい。 と書いてみました。 (HANA) 2014/12/10(水) 11:41
処理の順番 こういうことですね。
Sub test14()
Dim NO検索 As Long, NO最終行 As Long Dim 月検索 As Long, 月最終列 As Long Dim i As Long Dim 行番号 As Long Dim 集計 As Worksheet Dim データー As Worksheet Dim カウント As Long
'「集計」にSheet5をSetする Set 集計 = Worksheets("Sheet5")
'「データー」にSheet6をSetする Set データー = Worksheets("Sheet6")
'集計シートのフィルターを解除する 集計.AutoFilterMode = False
'NO最終行 に 入力されているデーターの最終セルの行数を取得し代入する NO最終行 = 集計.Cells(Rows.Count, 1).End(xlUp).Row
'月最終列 に 入力されているデーターの最終列の列数を取得し代入する 月最終列 = 集計.Cells(2, Columns.Count).End(xlToLeft).Column
'E3〜データーの範囲の値をクリアする 集計.Range(集計.Cells(3, 5), 集計.Cells(NO最終行, 月最終列)).ClearContents
'For〜Nextは条件により繰り返えして処理をする(3行目から値がある最終行まで) For i = 3 To データー.Cells(Rows.Count, 1).End(xlUp).Row
'集計シートのB1が空欄の時か(集計シートのB1が空欄じゃない時&データーシートと集計シートB1が同じ時) If 集計.Range("B1").Value = "" Or (集計.Range("B1").Value <> "" And データー.Range("C" & i).Value = 集計.Range("B1").Value) Then
'集計シートにデーターシートと同じ値があるかを調べる(0=なし、1=1個ある、2以上はダブってる) カウント = WorksheetFunction.CountIf(集計.Range("A3:A" & Rows.Count), データー.Cells(i, "E"))
'カウントの結果が0と等しくないとき(1以上のとき) If カウント <> 0 Then
'NO検索 に Noが集計シートの何行目に有るかMATCH関数で調べて代入する NO検索 = Application.Match(データー.Cells(i, "E"), 集計.Range("A:A"), 0)
'月検索 に 日付から月だけを取り出して、集計シートの何列目に有るかMATCH関数で調べて代入する 月検索 = Application.Match(Month(データー.Cells(i, "B")), 集計.Range("2:2"), 0)
'集計シートの何行目に「NO」があるか・何列目に「月」があるか確認して=クロスするセルに値を足し算する。 集計.Cells(NO検索, 月検索) = 集計.Cells(NO検索, 月検索) + データー.Cells(i, "j")
'集計シートの何3行目〜最終行まで1行ずつ= 最終列のセルにNOの値を足し算する。 集計.Cells(NO検索, 月最終列) = 集計.Cells(NO検索, 月最終列) + データー.Cells(i, "j")
'カウントの結果が0と等しいとき(1未満のとき) Else
'●●&のNOが登録しています。 MsgBox データー.Cells(i, "E").Value & "のNOが登録していません。" End If End If
Next i
'ヘッダーから2行目の最終列が空欄の時フィルターをかける。 集計.Rows(2).AutoFilter Field:=月最終列, Criteria1:="<>"
End Sub (もみじ坂) 2014/12/10(水) 20:35
はい。そういう事でした。
このコードはこれで一旦終わりにします。 それで >>それから、現在ループ処理はデータシートを1行目から処理しています。 >>でも当初は、集計シートから処理をしようとしていましたよね? >>もう一度、何をしようとしていたのか 考えてみてもらえますか? に関してですが、 「当初は、集計シートから処理をしようとしていましたよね?」 って覚えておられますか? [[20140814193616]] 『マクロで合計_1_自動合計作成考え方』(もみじ坂) の最初の書き込みにある test2 のコードは〜「商品コード」がどの範囲のつもりなのかわからないですが〜 With Worksheets("Sheet5") For r = 3 To 6 商品NO = Application.Match(.Cells(r, "A"), 商品コード, 0) Sheet5・・・集計シートのセルのループ ~~~~~~~~~~~~~~ になっています。
今は For i = 3 To データー.Cells(Rows.Count, 1).End(xlUp).Row NO検索 = Application.Match(データー.Cells(i, "E"), 集計.Range("A:A"), 0) なので、データシートのセルのループです。 ~~~~~~~~~~~~~~~~~~~~~~
この方針転換は、私が「とにかくデータシートの方からループするコードにする様に!!」と押し切ったと認識しています。 ですから、もう一度 この時 もみじ坂さんは何を思って集計シートの方からループする処理を考えていたのか。 思い出してみて下さい。
集計シートの方からのループでも集計出来ないわけではありません。 もみじ坂さんが、紙と鉛筆を使って集計シートの方からのループで集計する場合 どの様に集計して行きますか? (HANA) 2014/12/11(木) 11:34
>紙と鉛筆を使って集計シートの方からのループで集計する場合 どの様に集計して行きますか?
現在==OR(Sheet5!$B$1="",AND(Sheet5!$B$1<>"",Sheet6!C3=Sheet5!$B$1)) はデーターシートで関数入れているので。
今度は==OR(B$1="",AND($B$1<>"",Sheet6!C3=$B$1)) 集計シートに関数を入れなければいけないですよね。
そうしますと。
NO 商品名前 個数 名称 1 月 2 月 3 月 4 月 5 月 合計 A01 J3+J4 A02 J7 A03 A05 J8+J9
やはり1個ずつ調べる感じになると思います。
ただ、 for next はデーターシートで実行するのではなく 集計シートで実行することに なりませんか? ループも行と列両方が必要になりますね。
と、現在の処理をほぼ逆にしなければならないということでしょうか?
(もみじ坂) 2014/12/12(金) 18:51
>for next はデーターシートで実行するのではなく 集計シートで実行することに >なりませんか? ですから >>でも当初は、集計シートから処理をしようとしていましたよね? と言う事なのですが。
>ループも行と列両方が必要になりますね。 全部ループ処理で済ませようと思うのなら 集計シートの行方向のループと、列方向のループと、データシートのループが必要になると思います。
>現在の処理をほぼ逆にしなければならないということでしょうか? その辺りは、考え方(処理の進め方)によって変わってくると思います。
集計シートの列方向のループが必要 と書きましたが これも処理の進め方によっては 月検索 = Application.Match(Month(データー.Cells(i, "B")), 集計.Range("2:2"), 0) を使う事にすると、必要なくなると思います。
今のコードにとらわれずに、データシートと集計シートを渡されて「集計してね」と言われた時 どのように集計していくか、一つずつ手順を確認しながら 文字にしてもらうと良いと思います。 (HANA) 2014/12/12(金) 23:43
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.