IT 기술 따라잡기

예전에 호부호형과 관련한 유머가 있었다.

홍판서: 왜 집을 떠나려고 하느냐?
홍길동: 서출이라는 이유로 호부호형(呼父呼兄)을 하지 못하거늘, 어찌 더 머무르고 싶겠습니까.
홍판서: 그래? 그럼 이제부터 호부호형을 허락하니 머물도록 하라.
홍길동: 그럴 수는 없사옵니다. 호부호형을 하면 뭐 합니까? 아버지를 아버지라 부르지 못하고 형을 형이라 부르지 못하는데.
홍판서: 알았다니까. 나를 아버지라 부르고 네 형을 형이라 부르도록 하라.
홍길동: 그럴 수는 없사옵니다. 아버지를 아버지라 부르고 형을 형이라 부르면 뭐 합니까? 호부호형을 못 하는데…흑흑흑…

오늘 경향신문의 한 기사를 보니 박근혜 유머가 새로 탄생하는 것 같다.

[서민의 기생충 같은 이야기] 대통령을 왜 욕하는가

* 증세 유머

대통령: 나라에 돈이 없어.
각료: 세금을 더 걷어야 하는 줄로 아뢰오.
대통령: 그렇게 하라고. 단, 증세는 안돼!

* 공약 파기 유머

대통령: 노인들한테 20만원씩 준다고 큰소리 쳐놨는데, 나라에 돈이 없소. 어쩌면 좋겠소?
각료: 소득으로 따져서 하위 70%만 줍시다. 상위 30% 노인들까지 줄 필요가 있겠어요?
대통령: 그렇게 되면 내가 공약을 안 지킨 게 되는 건가?
각료: 지당하신 말씀입니다.
대통령: 괜찮아요. 공약파기만 안하면 돼.

* 민영화 유머

대통령: 코레일에 적자가 너무 많다며? 그게 다 경영을 방만하게 해서 그런 거 아니요.
각료: 지당하신 말씀입니다.
대통령: 재벌이나 외국기업에 코레일을 넘겨주고 지네들보고 경영하게 하면 되잖소. 코레일 팔면 돈도 들어올 테고.
각료: 성은이 망극하옵니다.
대통령: 당장 시행해. 단, 민영화는 안돼!

* 국민 소통 유머

소통은 국민과 하는 것이지 불법과 하는 것은 아니다.
종북세력은 국민이 아니다.
친일파를 비판하고 박정희를 비판하고 박근혜를 비판하는 세력들은 종북세력들이다.
이들은 국민이 아니다.
친일파를 옹호하고 박정희를 반신반인으로 받들고 박근혜를 지지하는 이들만이 국민이다.
대통령께서는 이들 100% 국민과 아주 소통을 잘 하고 계신다.

지난번 사용한 테이블에다 Option1 과 Option2 필드를 추가했습니다.

위 그림처럼 Option1 의 0.00 으로 필터링 한 후 Aug 필드의 합계를 구하겠습니다.

며칠 전에 만든건데 지금와서 소스를 보니까 저도 생소하네요.

일단 Seq 필드와 option2 필드 Range를 담을 변수를 만들었습니다.

제 기억으로는 Option2는 사용되질 않는데 아마 Option1을 담을 Range인데 타이핑을 잘 못 했나 봅니다.

그리고 합계 구할 Row 의 범위를 알기 위해 firstRow와 lastRow를 Long 타입의 변수로 정의했습니다.

첫번째 줄은 그냥 아무 셀이나 Select 한 거구요.

그 다음을 보니까 링크를 클릭했습니다. 이건 지난번에 만들었던 소스코드에도 그대로 나오는 겁니다. 이렇게 하면 링크 걸린 페이지로 가겠죠? 링크 걸린 페이지는 위에 보이는 테이블이 있는 페이지 입니다.

그 다음은 일단 이전에 작업할 때 필터링이 된게 있을 지도 모르니까 이 필터링 된 걸 풀고 모든 값을 보이도록 하는 코드 입니다.

그런데 필터링이 안 돼 있으면 이 부분에서 에러가 나니까 이 에러를 나지 않도록 처리도 했습니다.

다음은 State 라는 값을 가진 Cell을 찾아서 Activate 시키고 여기서 쭉 밑으로 데이터가 있는 마지막 Cell 까지 값니다.

그리고 그 셀의 Row를 lastRow에 담습니다.

이 부분은 fistRow를 구하는 부분까지 잘랐는데 뭔 코드가 이렇게 긴지 모르겠네요.

한번 볼까요?

우선 Seq 라는 값을 가진 셀을 찾아서 그 셀을 seqRng에 담습니다. (이건 왜 만들었을까? 지금 봐서는 잘 이해가 안 가네요.)

하여간 그 다음에는 Option2로 검색해서 그것을 option2Rng에 담습니다.

이것도 왜 담는건지 모르겠네요.

다음은 Option1이라는 값을 가진 Cell을 찾아서 그 Cell을 Activate 시킵니다.

앗 잠깐만요...

그 아래에 필터링 하는 부분이 있군요.

거기서 seqRng와 option2Rng가 사용됩니다.

괜히 그 값을 구해서 변수에 담은게 아니었군요.

이렇게 되면 Option1을 Find로 찾아서 Activate 시킨 부분은 아무 의미 없는 코딩 같은데요.

한번 계속 볼까요?

일단 필터링이 있는 범위를 Range로 정하고 나서 몇번째 필드를 필터링 할 건지 알려주고 필터링 할 값은 0.00으로 정해 줍니다.

이렇게 되면 Option1 필드 중 0.00을 가진 놈들로만 필터링이 됩니다.

다음에 하는 일은 Aug라는 값을 가진 셀을 찾아서 그 아래 셀을 Activate 시킵니다.

여기까지 보니까 확실하게 Option1이라는 값을 가진 셀을 Find 한 부분은 필요가 없는 부분이란게 확실 하네요.

이제 Aug셀의 바로 아래 Row를 firstRow로 지정했습니다.

이렇게 되면 firstRow와 lastRow가 모두 구해 졌습니다.

첫번째 줄은 Aug 필드의 맨 마지막 Row 바로 밑을 Activate 시킨 겁니다.

거기다가 구한 합계를 넣기 위해서죠.

그 밑에 줄에서 SUBTOTAL을 사용하는데요.

이 함수는 필터링된 값만 가지고 합계를 내기 위해 사용했습니다.

그 안에 9는 합계를 낼거라는 의미이구요.

끝에 R[-1]C는 현재 Activate 된 셀의 바로 윗줄을 말하는 겁니다.

Aug 컬럼 중 합계를 구할 맨 밑줄이 되죠.

그 바로 전에는 Aug 셀의 바로 밑셀을 구하는 겁니다.

Aug 셀의 바로 밑셀부터 맨 밑셀까지를 정해 준거구요. 그 값들을 SUBTOTAL로 합계를 내게 됩니다.

그러면 그 합계가 맨 위에 있던 테이블 그림 처럼 Aug 필드의 맨 밑에 있는 셀 바로 밑에 표시 됩니다.

그 다음에는 그 값을 Copy 하구요. New_Sheet로 가서 적당한 위치에 복사한 값을 붙여넣기를 합니다.

이제 이정도 까지만 하면 제가 업무 하면서 만들고 싶었던 매크로를 만들 수 있겠네요.

필요한 기술은 다 Research 한 것 같습니다.

저는 이제 제가 필요한 매크로를 만들어야 겠습니다.

처음에는 새로운 Sheet를 만들었고 그 다음에는 특정 필드의 값들의 합계를 구했고 이번에는 필터링한 후 특정 필드의 값들의 합을 구했습니다.

지금까지 만든 소스코드는 Research 목적으로 만든거라서 실제 사용하려면 Refactoring 과정을 거쳐야 합니다.

불필요한 코드들은 없애고 좀 더 간단하게 만들 수 있는 로직은 간단하게 고치고 반복적으로 사용되는 로직은 따로 Component 를 만들어서 사용할 수 있도록 고치고 등등이요.

혹시 이 Refactoring 과정을 거치지 않은 소스코드라도 참고하시고 싶으신 분들을 위해서 제가 작업한 파일을 아래에 올려 놓을께요.

ForTesting.xlsm

이번에는 특정 컬럼의 숫자를 더하는 코드를 만들어 보겠습니다.

제가 그냥 만들어본 표 입니다. (State는 제가 있는 Minnesota 의 약자이구요. 그 안의 값들은 그냥 임의로 넣은 겁니다.)

여기서 Aug 컬럼의 숫자들을 더한 후 New_Sheet 라는 Sheet 에 그 더한 값을 넣겠습니다.

여기서 조건을 문서를작성하다 보면 양식은 위와 같이 되지만 값의 Cell 값은 바뀔 수 있는 상황입니다.

예를 들어 어떤 사람은 이 표를 엑셀 쉬트의 A1 서부터 작성하는 사람이 있고 또 어떤 사람은 한 한 B3 정도부터 작성하는 사람이 있습니다.

그러면 각 값들의 위치들도 그에 따라 달라질 건데요.

이럴 경우에도 구애 받지 않고 Aug 컬럼 값들의 합계를 구해 보겠습니다.

그러기 위해 우선 Aug 의 위치를 알아 내겠습니다.

그리고 바로 그 밑의 Cell 값을 받고 맨 마지막의 Cell 값을 알아 냅니다.

그래서 그 Cell 들의 값을 더하겠습니다.

여기서 어떤 달들은 중간중간 혹은 맨 처음이나 맨 마지막 값이 비어 있을 수 있습니다.

그래서 맨 마지막 Cell 값은 항상 값이 채워져 있는 Seq 나 State로 가서 값이 있는 맨 마지막 Cell을 알아 내겠습니다.

우선 이런한 작업을 할 때 값을 담을 변수들을 만들겠습니다.

firstCell과 lastCell 은 Aug 컬럼의 첫번째와 마지막번째 Cell 값입니다. 

state1stCell 은 state 컬럼의 첫번째 Cell 값이구요.

sumCell은 Aug 컬럼의 값들을 모두 더한 값을 넣을 변수 입니다.

그리고 lastRow는 Long 타입인데요. State 컬럼에서 값이 있는 맨 마지막 Row를 구해서 넣을 변수 입니다.

지난번에 올린 엑셀 파일을 보면 저 테이블은 두번째 Sheet에 있고 첫번째 Sheet 에 저 Sheet로 가는 링크가 있습니다.

우선 이 링크를 클릭하는 코드를 작성하겠습니다.

일단 첫번째 sheet 에서 이 함수를 실행시킨다고 가정하구요.

아무곳에나 커서를 놓습니다. (여기서는 B4).

그리고 첫번째 링크를 클릭합니다.

그러면 두번째 Sheet로 이동했을 겁니다. 여기서 Find 명령어로 Aug라는 필드를 찾아서 그 Cell 을 Activate 합니다.

(위 코드는 Macro를 실행해서 만든 코드 입니다.)

여기까지 하면 아래와 같이 Aug 컬럼이 선택 됩니다.

그러면 Aug 컬럼의 첫번째 Cell 을 구할 수 있습니다.

현재 활성화된 Cell (여기서는 Aug) 에서 한칸 아래로 내려가서 그 Cell을 firstCell 에 담았습니다.

이제 firstCell 에는 62.28 이 있는 Cell 이 담겨져 있습니다.

그 다음에는 Find 를 사용해서 State 가 있는 Cell을 찾아서 활성화 시킵니다.

이제 그 활성화 된 Cell 바로 아래 Cell을 state1stCell 에 담았습니다.

그리고 그 state1stCell을 다시 활성화 시킵니다.

이제 State 컬럼의 첫번째 Row 에 있는 Cell 이 활성화 돼 있을 겁니다.

이제 End(xlDown)을 이용해서 State 컬럼의 맨 마지막 Row로 이동합니다.

그리고 그 row의 값을 lastRow에 담습니다.

이제 값이 있는 맨 마지막 Row의 값을 알았으니 다시 Aug 컬럼으로 가겠습니다.

firstCell 에서 State 컬럼에서 얻은 맨 마지막 Row 값을 이용해서 Aug의 맨 마지막 Row로 갑니다.

그리고 이 Aug의 맨 마지막 Row를 lastCell에 담습니다.

이러면 Aug의 맨 첫번째 Cell 과 맨 마지막 Cell을 얻게 됐습니다.

이것만 알면 Aug의 첫번째 Cell 에서 마지막 Cell 에 걸쳐 있는 값의 총계를 알아 낼 수가 있습니다.

구한 합계는 Aug의 맨 마지막 Cell의 밑에 Row에 적어 넣을겁니다.

그래서 sumCell은 lastCell의 바로 밑의 Cell로 설정해 두었습니다.

위에서도 사용했던 Offset을 여기서도 사용했습니다.

이제 Aug의 첫번째 셀에서부터 마지막 셀까지 범위 설정을 하고 그 합을 sumCell에 넣었습니다.

그리고 그 값을 Copy 했습니다.

이제 클립보드 내에는 Aug 컬럼의 값들의 합이 담겨져 있습니다.

이제 New_Sheet로 이동해서 D3 위치에 복사한 값을 붙여넣기 합니다.

Selection.PasteSpecial은 Macro를 이용해서 구한 코드 입니다.

여기서는 단순하게 붙여넣기를 한게 아니라 그 Value 를 붙여넣기 했습니다.

제가 필요한 것은 그 셀의 공식이 아니라 값이니까요.

이제 복사해 넣은 값의 왼쪽 셀(C3)로 가서 AugSum 이라고 글자를 써 넣습니다.

이렇게 해서 Aug 컬럼의 모든 값을 더해서 New_Sheet 에 복사해 넣는 코드가 완성됐습니다.

이제 저 양식만 맞추면 표를 어느 위치에 작성하든 Aug 필드의 값들의 합을 구할 수가 있게 됐습니다.

전체 소스코드는 아래와 같습니다.

제가 엑셀 프로그래머가 아니라서 코드가 깔끔하지는 않을 겁니다.

배우시는 분들은 이걸 좀 더 깔끔하게 다듬으시면 더 공부가 잘 될 겁니다.

ForTesting.xlsm

필요하신 분은 위 파일 다운 받으시면 안에 소스코드가 있습니다.

Cntr-Shift-A 는 지난 글에서 다뤘던 Sheet 만드는 매크로이고 Cntr-Shift-B 는 그 새로 만든 매크로에 Aug 컬럼의 값들의 합계를 구해 넣는 매크로 입니다.

둘 다 첫번째 Sheet 에서 실행 하셔야 됩니다.