[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数のシートをレイアウトを変えて1つにまとめたい』（末央）
こんにちは。現在このようなレイアウトのシートが約30あります。
[A] [B] [C] [D] [E] [F] [G] [H]
____________________________________________________________________________________
[1] | 項目1 項目2 項目3 項目4 項目5 項目6 項目7 項目8
____________________________________________________________________________________
[2] | ■■■ ■■■ ■■■ ■■■ ■■■ ■■■ ■■■ ■■■
[3] | 項目9 ■■■ 項目10 ■■■■■■■■
[4] | 項目11 ■■■ 項目12 ■■■■■■■■
____________________________________________________________________________________
[5] | ■■■ ■■■ ■■■ ■■■ ■■■ ■■■ ■■■ ■■■
[6] | 項目9 ■■■ 項目10 ■■■■■■■■
[7] | 項目11 ■■■ 項目12 ■■■■■■■■
_____________________________________________________________________________________
[8] | ■■■ ■■■ ■■■ ■■■ ■■■ ■■■ ■■■ ■■■
[9] | 項目9 ■■■ 項目10 ■■■■■■■■
[10] | 項目11 ■■■ 項目12 ■■■■■■■■
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
これを
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M]
________________________________________________________________________________________
[1] | 項目1 項目9 項目11 項目2 項目3 項目4 項目5 項目10 集計 集計 確認 確認
_________________________________________________________________________________________
[2] | （A2）（B3) （B4) （B2) （C2) （D2) （E2) （D.E3結合） □□ □□ □ □
[3] | （A5）（B6) （B7) （B5) （C5) （D5) （E5) （D.E6結合） □□ □□ □ □
[4] | （A8）（B9) （B10) （B8) （C8) （D8) （E8) （D.E9結合） □□ □□ □ □
このような表にして1枚にまとめたいと思っています。 たぶんVBAかマクロになるのでしょうがそちらの知識がないのでお知恵を貸してください。
参考： ①シート内のデータは今後も増え、シート自体も増える可能性があるので、更新作業も 楽にしたいと思っています。 ②事前に項目は作りセル幅も調整してあるので（）内のデータだけをコピーしたいです。 ③元のシートの項目6～8のデータは不要です。 ④集計は別表も参照する事になると思うので今は空白のままで、後で関数を入れても 不具合がでないようにしておきたいです。 ⑤L列の確認部分にはチェックボックスか「○」印が入ったらその行を黄色、 M列も同様でチェック済みはその行を緑 L、M両方にチェックが入った行は赤にしたいです。
このような感じでわかっていただけるでしょうか？ よろしくお願いします。
>たぶんVBAかマクロになるのでしょうがそちらの知識がないのでお知恵を貸してください。 ＶＢＡはまったく（画面を見た事）ないですか？
取り敢えず試運転ですが。 集計用シートの項目１はＢ列でいいと判断してます。
Sub test() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim r1 As Range Dim r2 As Range Dim rr As Range Dim i As Long Dim v
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set r2 = ws2.Range("B2")
With ws1
For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row - 2 Step 3
Set r1 = .Range("A" & i)
For Each rr In r1.Range("A1,B2,B3,B1,C1,D1,E1,D2")
v = v & rr.Value & ","
Next
v = Split(v, ",")
With r2.Resize(, UBound(v))
.Value = v
.Value = .Value
End With
v = ""
Set r2 = r2.Offset(1)
Next
End With
End Sub
Sheet1 は任意のデータシート名に変更して下さい。 Sheet2 は集計を行なうシート名に変更して下さい。 集計は１シート分しか実行しません。
Visual Basic Editor(VBE)の演習です． http://www.clas.kitasato-u.ac.jp/~takizawa/kagaku/ensyvba/vba2edit.html ・Visual Basic Editorを起動します． ・モジュールの挿入 を行なうと．"Module1"というオブジェクト名のモジュールが挿入されます ここに上の Sub test() ～ End Sub をコピペ
Excelの画面で「ツール」－「マクロ」－「マクロ」でtestを選択して実行です。 (じゅんじゅん)
じゅんじゅん様 ありがとうございます。こんなにはやく回答を頂けると思っていなくて感動しました。 今日は自宅なのでまだ試していないのですが、他の仕事も含めて明日休日出勤するので その時に試して夜にでも結果をご報告させていただきます。
＞ ＶＢＡはまったく（画面を見た事）ないですか？ とのご質問ですが、よくここを見せていただいているので 他の方の質問で比較的簡単そうなものを試したり http://www6.plala.or.jp/MilkHouse/menu.html http://www.sanynet.ne.jp/~awa/excelvba/kouza.html などを読ませていただいているのですが、まだ理解ができていない状態です。 まだExcelを使い始めて間がないのでまずは関数を覚えていこうと思っていますが 今回のものは関数では無理だと判断して質問させていただきました。
ついでと言ってはなんですが、前から気になっていることを一つ質問させて下さい。 現在の仕事内容的にはVBAが使えるようになるととても楽になると思うのです。 しかし、理数系が苦手な私が関数を理解していない現状でいきなりVBAに手を出すのは 無謀でしょうか？それとも関数とVBAは別物でしょうか？ 別物であればVBAに力を入れて勉強してみようと思うのですが・・・ アドバイスをお願いします。 （末央）
ひとつ見落としていたので補足します。 ＞集計用シートの項目１はＢ列でいいと判断してます。
それで結構です。A列は作業列として使用する予定なのであけておきたかったのです。（末央）
>しかし、理数系が苦手な私が関数を理解していない現状でいきなりVBAに手を出すのは >無謀でしょうか？それとも関数とVBAは別物でしょうか？ >別物であればVBAに力を入れて勉強してみようと思うのですが・・・ 私自身はExcelの経験は５年ほどありましたが、ＶＢＡを始めたのは１０ヶ月ほど前からです。
１つには、一般関数・一般機能でやりきれないと感じた事と、自動化というものがあるのだから それを習得したいと言う点から勉強を始めました。 (プログラムの経験は、２０年ほど前に工業高校でBasicを少しいじってましたので、ちょびっと 知識があったかな？程度ですがＶＢＡとはやはり違いますね。） ＊やりきれない・・・一般関数で複雑な数式が作れず悩んでいた、と言う事です。
アドバイスと言うほどのものではないかも知れませんが、一般関数・一般機能を色々やってみて そこで解決できないような問題が出てきた時に、こう言ったサイトで質問をしてみて、 やはり一般関数・一般機能で解決できるものなのか、ＶＢＡでの処理になってくるのかの回答を 得ても宜しいかと思います。
ＶＢＡ本来の処理で出来る場合もありますが、ExcelはＶＢＡに一般関数を組み合わせる事で 処理を簡単にしていく事も可能ですから、決して別物とは考えていません。
理数系が苦手だからＶＢＡに手を出すのは無謀・・・と思ってしまっては、そこで自ら止まってしまうだけですから。 頑張ってみて下さい。 (じゅんじゅん)
これは複数の（元データ）シートデータを片っ端から拾い出し、一気にSheet1へ吐き出す参考マクロです。
とりあえずはSheet2,Sheet3のデータをSheet1に拾い出しておりますが、shtaryの
カッコ内にSheet名を書き込めば、書き込んだSheet名のデータを全て拾い出します。
あくまで参考ですんで、細かいツッコミはご遠慮くらはい。（笑
（弥太郎）
'-------------------------
Option Explicit
Option Base 1
Sub 集計()
Dim i As Long, j As Long, n As Integer, t As Integer, x, ws, shtary, ary, tbl
shtary = Array("Sheet2", "Sheet3")
ary = Array(1, 2, 2, 2, 3, 4, 5, 4)
ReDim x(1 To Rows.Count, 1 To UBound(ary))
For Each ws In shtary
With Sheets(ws)
tbl = .Cells(1, 1).Resize(.Cells(Rows.Count, 1).End(xlUp).Row, 8)
For i = 2 To UBound(tbl, 1) Step 3 '
j = j + 1
For n = 1 To UBound(ary)
t = IIf(n = 2 Or n = 8, i + 1, IIf(n = 3, i + 2, i))
x(j, n) = tbl(t, ary(n))
Next n
Next i
End With
Next ws
With Sheets("sheet1")
.Cells(2, 1).Resize(.Cells(Rows.Count, 2).End(xlUp).Row, 8).ClearContents
.Cells(2, 2).Resize(j, UBound(ary)) = x
End With
End Sub
単純に、2番目以降にあるシートの内容を1番目のシートにまとめます。
項目10は、D3.E3をどうやって結合するのか分からないので
間に「●」を入れています。
また、データ量が多いと、エラーになるかもしれません。
Sub 最初のシートにまとめる()
Dim i As Long, ii As Long, mr As Long, gs As Long, x_r As Long, x_mr As Long
Dim x(), a
For i = 2 To Sheets.Count
With Sheets(i)
mr = .Cells(Excel.Rows.Count, 1).End(xlUp).Row
If mr > 4 Then
a = .Range("A1:H" & mr)
gs = (mr - 1) / 3
x_mr = x_mr + gs
ReDim Preserve x(1 To 8, 1 To x_mr)
For ii = 1 To mr - 1 Step 3
x_r = x_r + 1
x(1, x_r) = a(ii + 1, 1)
x(2, x_r) = a(ii + 2, 2)
x(3, x_r) = a(ii + 3, 2)
x(4, x_r) = a(ii + 1, 2)
x(5, x_r) = a(ii + 1, 3)
x(6, x_r) = a(ii + 1, 4)
x(7, x_r) = a(ii + 1, 5)
x(8, x_r) = a(ii + 2, 4) & "●" & a(ii + 2, 5)
Next
End If
End With
Next
With Sheets(1)
.Cells(2, 2).Resize(.Cells(Excel.Rows.Count, 2).End(xlUp).Row, 8).ClearContents
.Cells(2, 2).Resize(x_r, 8) = Application.Transpose(x)
End With
End Sub
(HANA)
＞L列の確認部分にはチェックボックスか「○」印が入ったらその行を黄色、 ＞M列も同様でチェック済みはその行を緑 ＞L、M両方にチェックが入った行は赤にしたいです。 これに関しては、条件付き書式設定を使うのはどうでしょうか。
2行目の条件付き書式の条件は 条件1 数式が[=($L2="○")*($M2="○") ] [セル色 赤] 条件2 数式が[=($L2="")*($M2="○") ] [セル色 緑] 条件3 数式が[=($L2="○")*($M2="") ] [セル色 黄]
＞シート内のデータは今後も増え、シート自体も増える可能性があるので と言う事ですが、シートが以降に増えた場合 データは以降に追加されますが 既にあるシートのデータが増えた場合、まとめシートのデータは下にシフトします。 そうなると、マクロを実行したときは、L,M列の値も削除する必要がある気がしますが どうなのでしょうか？ L,M列が関数等で、前の列の情報を元に算出されている物なら 心配ないと思いますが・・・。
例えば↓の様な事を言っています。 Sheet1とSheet2から「まとめ」が作成され B列に「○」をつけます。 Sheet1 [A] Sheet2 [A] まとめ [A] [B] [1] 項目１ [1] 項目１ [1] 項目１ 確認 [2] S1-1 [2] S2-1 [2] S1-1 ○ [3] S1-2 [3] S2-2 [3] S1-2 [4] [4] [4] S2-1 [5] [5] [5] S2-2 ○ [6] 次に、Sheet1のA4にデータが追加されて、A列がシフトしたとしても Sheet1 [A] まとめ [A] [B] [1] 項目１ [1] 項目１ 確認 [2] S1-1 [2] S1-1 ○ [3] S1-2 [3] S1-2 [4] S1-3 ←追加 [4] S1-3 ←データがココに挿入される [5] [5] S2-1 ○ ←本来はS2-2の○ [6] S2-2
○はB2とB5の位置から動きません。 もしも、この確認用の○が、VLOOKUP(項目１,範囲,2,FALSE)等の数式で 表示されている物であれば、A列が変わることで ○の位置も変わると思うので 特に問題ないと思いますがどうなのでしょう。
(HANA)
こんばんは。遅くなりました。 留守中に沢山のお答を頂きありがとうございます。
じゅんじゅん様 会社のデータで実行したところ驚くほど完璧にできました。 ただ、私の勘違いで集計の前に2行ほど別表データを入れる行を入れ忘れていたので これから自分なりに変更を試してみようと思っています。 また、貴重なアドバイスをありがとうございます。 今回、自分のやりたいことを形にしていただくと これまでよりもほんの少しですが理解できそうな気もしてきました。 がんばってみます＾＾
弥太郎様 ＞これは複数の（元データ）シートデータを片っ端から拾い出し、一気にSheet1へ吐き出す参考マクロです。 仕事柄このようなマクロはすごく魅力的です。 いろいろなサンプル？を頂くと比較したりできて勉強になります。 お心づかいありがとうございます。
HANA様 ＞項目10は、D3.E3をどうやって結合するのか分からないので これは元データが結合セルになっているということを表現したかったのですが、 言葉足らずで申し訳ありませんでした。 提示していただいたマクロはこれから試させていただきます。
L,M列についてですが、ここは目視で確認してチェックを入れようと思っていたので おっしゃる通りマクロでデータが追加されるとずれてしまいますね。 全く気付きませんでした。危うく大きなミスをするところを救って頂き 感謝の言葉もありません。 確かにここは条件付き書式が最適なようですね。そのようにしてみます。
みなさま本当にありがとうございます。 早速とりかかってみます。 また質問させて頂くかもしれませんがその時にはよろしくお願いします。（末央）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.