Excel

엑셀 매크로 – 여러 시트에서 값 가져오기

엑셀 파일에 일별로 시트를 만들어서 야근자 기록을 했다. 월말이 되어 각 직원의 야근 회수를 집계해야 하는데 일별로 작성된 시트가 20개 넘는다. 쉽게 집계할 수 있는 방법이 없을까?

아래는 각 시트에서 야근자 이름이 있는 컬럼만 하나의 시트로 전부 모아오는 매크로다. 이렇게 모아만 놔도 countif 등의 함수로 간단히 집계가 가능하다. ( 아래 매크로에서는 긁어 모은 야근자명을 distinct 한 후 카운트하는 함수는 별도로 만들어서 콜해서 사용했다. )

얼마 전부터 팀 단위로 관리하기 시작한 야근일지 집계 때문에 옆에 분이 도움 청해와서 급조해 본 매크로인데 여러 시트에서 특정 컬럼값들 긁어올 때 응용 가능할 것 같아서 Window Live Writer 테스트도 해볼 겸 메모.

Sub MakeSummary()
Dim c As Variant
Dim lc As Variant
Dim k As Integer
Dim l As Integer
Sheets(“SUMMARY”).Select

Range(“A1:H1”).EntireColumn.ClearContents
For i = 1 To Sheets.Count
A$ = Sheets(i).Name

If (A$ = “summary”) Then GoTo 10
l = 6
lc = Sheets(A$).Range(“B” + Format(l + 1)).End(xlDown).Address
For Each c In Sheets(A$).Range(“B” + Format(l + 1) + “:” & lc)
l = l + 1
k = k + 1
Range(“D” & k).Value = “='” + A$ + “‘!R” + Format(l) + “C2”
If (Range(“D” & k).Value = 0) Then
l = l – 1
k = k – 1
GoTo 10
End If

Range(“A” & k).Value = A$
Range(“B” & k).Value = “='” + A$ + “‘!R” + Format(l) + “C2”
Next c
10 Next i

Range(“D1”).EntireColumn.ClearContents

Call distinct
Call Count_Overwork
End Sub

Sub distinct()
….
End Sub

Sub Count_Overwork()
j = 1
lc = Range(“B1”).End(xlDown).Address
lc1 = Range(“E1”).End(xlDown).Address
For Each c In Range(“E1:” & lc1)
Range(“G” & j).Value = “=COUNTIF(B1:”+lc+”, E”+Format(j) + “)”
j = j + 1
Next c
End Sub

혹 꼼꼼히 함수 읽어 본 이라면 발견했을 수도 있는데 중간에 각 시트의 컬럼에서 마지막 값 있는데 까지 긁어 오는 부분( ‘ For Each c In Sheets(A$).Range(“B” + Format(l + 1) + “:” & lc) … ‘ )  에서 ***.End(xlDown).Address 함수의 문제 때문에 약간의 꽁수를 썼다. 이 부분을 깔끔하게 처리하면 좀 더 재활용성 높아질 듯 싶은데 누구 힌트 좀 주실 분 없으려나?

p.s : Window Live Writer, 꽤 깔끔하게 작동한다. 앞으로 ScribeFire 와 함께 잘 써먹어야겠다.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s