날짜를 기준으로 사용하지 않는 열은 자동으로 숨기기

안녕하세요.

 

구독자이신 정**님이 이메일로 질문을 하셨습니다.

 

한정의 강사님,
짤막한 강좌를 통해 많은 도움을 받고 있습니다.

전 오랫동안 공장을 관리 경험이 있었습니다.
현재는 모회사의 업무를 잠깐 도와주고 있는데요.
질문하나 하고 싶은데요

공장에서 원자재 관리를 하는데, 창고현장에서 일일 입출고관리 양식을 제공하여 사용하고자 합니다.
한달 즉 1일부터 31일까지 전일재고, 입고, 출고, 재고 형태로 하나의 시트에 한달간 입출고 및 재고를 나타내고자 합니다

시트를 31일까지 만들어 제공하려고 하니 시트가 넓어 사용하지 않는 부분들을 감추어서 사용하고 싶은데요
오늘 날짜를 입력하고 나면, 날짜를 추출하여 
오늘보다 2일이나 3일전까지의 입출고 데이터를 노출시켜주고 나머지는 모두 감추고 싶습니다.
즉 날이 바뀔 때마다 해당되는 2~3일간만의 데이터만을 보여주고 나머지는 모두 감추어 준다면 작업이 편해 질 것 같아서요
참고로 관련 유사 관리양식을 첨부하여 보내 드립니다.
도와주실 해법에 대해 먼저 감사를 드립니다.


추가로 
이미 기록한 데이터들에 대해서는 자동으로 보호가 될 수 있도록 동적으로 해 줄 수 있는 방법이 있을까요?


감사합니다.

정**올림

 

질문 글만 읽고 문제가 무엇인지, 어떤 걸 원하는지 한 번에 알 수 있는 경우는 흔하지 않습니다.

 

엑셀 기본 기능으로 구현할 수는 없고 VBA를 이용하면 어렵지 않게 해결될 수 있는 문제라고 판단했습니다.

 

실습 파일 다운로드

날짜를 기준으로 사용하지 않는 열은 자동으로 숨기기_실습.xlsx

0.02MB

 

완성 파일 다운로드

날짜를 기준으로 사용하지 않는 열은 자동으로 숨기기_완성.xlsm

0.05MB

 

 

메일에 첨부하신 파일을 조금 수정했습니다.

[CU1] 셀에 TODAY 함수로 오늘 날짜가 자동으로 나오도록 되어 있었는데, 설명을 위해 '2022-02-11'로 직접 입력했고, CT, CU 열 수식을 SUMIF 함수로 바꿨습니다.

 

첨부한 파일을 열어 보기 전에는 '이 정도는 그냥 수작업해도 되지 않나?'라고 생각했었습니다.

파일을 열어보니 하루마다 열 3개를 쓰므로 31일은 93개 열을 사용하고, 날짜에 따라 9개 열만 남기고 나머지를 숨겨야 하는 작업이니 수작업으로 하려면 상당히 귀찮고 실수할 가능성이 높은 일이란 것을 알았습니다.

이런 작업이 딱 자동화해야 하는 것입니다.

 

시트를 복사해 '확인'이란 이름으로 바꿨습니다.

 

원본 시트를 남겨둬야 어떤 결과를 내어야 하는지 참고하며 작업을 할 수 있기 때문에 시트를 복사해서 작업을 하는 것입니다.

 

'확인' 시트에서 D열부터 CT열까지 범위를 선택해서 마우스 오른쪽 클릭해서 '숨기기 취소'를 선택합니다.

숨긴 열을 모두 표시하니 옆으로 한참 움직여야 끝을 볼 수 있습니다.

열을 숨기려면 어떤  코드를 써야 하는지 알아보기 위해 매크로를 하나 만듭니다.

 

E:G 열을 선택한 뒤 [보기]-[매크로]-[매크로 기록]을 선택합니다.

코드를 확인하기 위한 목적으로 만드는 것이 때문에 [매크로 기록] 대화상자에 표시되는 내용을 바꾸지 않고 그대로 [확인]을 클릭합니다.

선택 영역에서 마우스 오른쪽 클릭해 '숨기기'를 선택합니다.

[보기]-[매크로]-[기록 중지]를 선택합니다.

[보기]-[매크로]-[매크로 보기]를 선택해 [매크로] 대화상자를 표시하고 [편집]을 클릭합니다.

VBE에 아주 간단한 코드가 표시됩니다.

Selection.EntireColumn.Hidden = True

숨기려면 True를, 숨기기 취소하려면 False를 입력하면 됩니다.

 

어떤 개체에, 어떻게 써야할지 잘 모르면 매크로를 만들어 보면 됩니다.

물론 모든 경우에 다 되는 것은 아닙니다. 그렇지만 대부분은 힌트를 얻을 수 있습니다.

 

D:H 열을 선택한 뒤 '숨기기 취소'를 선택합니다.

숨기거나 숨기지 않을 첫번째 열은 E열이고, 마지막 열은 CS열입니다.

문자로 열을 나타내는 것보다 숫자로 바꾸면 반복해서 처리하기 쉬워집니다.

E열은 5번째 열입니다.

CS열은 얼른 계산하기 어려워 [CS1] 셀에 COLUMN 함수로 수식을 작성해 확인합니다.

=COLUMN()

COLUMN 함수는 현재 선택한 셀의 열 번호를 알려 줍니다.

CS열은 97입니다.

 

이제 어떤 규칙이 있는지 생각해 봅니다. 바로 알고리즘을 찾는 과정입니다.

 

일 : 1 -> 5 ~ 7 표시 8~ 97 숨김

일 : 2 -> 5 ~ 10 표시 11 ~ 97 숨김

일 : 3 -> 5 ~ 13 표시 14 ~ 97 숨김

일 : 4 -> 5 ~ 7 숨김 8 ~ 16 표시 17 ~ 97 숨김

일 : 5 -> 5 ~ 10 숨김 11 ~ 20 표시 21 ~ 97 숨김

 

1~3일까지는 특이한 경우이고 4일부터 나머지는 앞 부분 숨김, 중간 나타냄, 뒷부분 숨김이란 규칙이 적용됩니다.

항상 시작은 5부터이고, 모든 끝은 97입니다.

 

하루에 입고, 출고, 재고의 3개 열을 나타내야 하므로 '1일*3'으로 하루에 해당하는 열을 나타낼 수 있습니다.

1일의 시작 열은 (1*3)+2인 5입니다. 2를 더하는 이유는 시작 열이 5부터이기 때문입니다. 

2일의 시작 열은 (2*3)+2인 8입니다.

3일의 시작 열은 (3*3)+2인 11입니다.

 

4일의 시작 열은 (4*3)+2인 14입니다.

앞 2일을 같이 나타내므로 표시해야 하는 열은 (4-2)*3+2인 8번째 H열입니다.

숨기기 시작 열은 (4+1)*3+2인 17번째 Q열입니다.

 

이걸 기준으로 코드를 작성합니다.

Sub 매크로1()
    d = Day(Range("CU1"))
    
    If d < 3 Then
        앞 = 5
    Else
        앞 = (d - 2) * 3 + 2
    End If

    뒤 = (d + 1) * 3 + 2
    
    For i = 5 To 앞 - 1
        Columns(i).EntireColumn.Hidden = True
    Next i
    
    For i = 앞 To 뒤 - 1
        Columns(i).EntireColumn.Hidden = False
    Next i
    
    For i = 뒤 To 97
        Columns(i).EntireColumn.Hidden = True
    Next i
End Sub

작은 따옴표로 시작하는 주석은 모두 지웠습니다.

 

만든 코드가 제대로 동작하는지 확인하기 위해 실행을 해야 하는데, 실행하기 전에 꼭 저장을 먼저 해야 합니다.

저장하지 않고 실행을 했다가 코드에 문제가 있어 제대로 실행되지 않으면 엑셀을 닫았다 다시 열어야 할 수 있고, 이때 저장하지 않은 내용을 모두 잃어버리게 됩니다.

 

<Ctrl + S>를 눌러 저장합니다.

경고창이 나타납니다.

지금 사용하고 있는 엑셀 파일은 XLSX 파일로 VBA를 제외하는 형식입니다.

[아니오]를 눌러 [다른 이름으로 저장] 대화상자에서 'Excel 매크로 사용 통합 문서(*.xlsm)'으로 저장합니다.

이제 [실행]-[실행]을 선택하거나 <F5>를 눌러 코드를 실행합니다.

제대로 동작한 것을 확인합니다.

혹시 오류가 난다면 작성한 코드를 살펴봐서 잘못된 부분을 찾고, 고쳐야 합니다.

 

다음 달은 현재 시트를 복사해서 쓰려고 하는데, 현재 코드가 작성된 곳은 모듈(Module)입니다.

모듈은 엑셀 파일 단위의 작성되는 위치로 시트만 복사해서는 복사되지 않습니다.

그래서 모듈에 작성한 코드를 시트에 포함해 저장하도록 옮깁니다.

 

모듈에 작성한 코드를 모두 범위 선택해서 '잘라내기'를 선택합니다.

[프로젝트 탐색기]에서 'Sheet2'를 더블 클릭합니다.

오른쪽 코드 창에 '붙여넣기' 합니다.

VBA가 실행되는 것에는 영향을 미치진 않지만 알아보기 쉽도록 매크로 이름을 '매크로1'에서 '실행'으로 바꿉니다.

<Alt + Q>를 눌러 VBE를 닫고 엑셀 창으로 돌아갑니다.

다시 VBE를 실행하려면 <Alt + F11>을 누르면 됩니다.

 

만들어 둔 매크로를 실행하는 단추를 시트에 추가합니다.

 

[개발 도구]-[컨트롤]-[삽입]-[양식 컨트롤] 중 [단추(양식 컨트롤)]을 선택합니다.

<Alt>를 누른 상태로 [CW1] 셀에서 마우스로 살짝 드래그한 뒤 마우스 왼쪽 단추에서 손을 떼면 [매크로 지정] 대화상자가 나타납니다.

'Sheet2.실행'을 선택하고 [확인]을 클릭합니다.

[CW1] 셀에서 마우스 오른쪽을 바로 클릭해 '텍스트 편집'을 선택합니다.

'실행'이라고 적고 비어 있는 다른 셀을 클릭합니다.

 

테스트해 보기 위해 [CU1] 셀을 더블 클릭해서 날짜를 바꿉니다.

'실행' 단추를 누르면 지정한 날짜에 맞게 '숨기기' 적용됩니다.

[CU1] 셀에 오늘 날짜가 나타나도록 TODAY 함수로 수식을 작성합니다.

 

'확인' 시트를 복사해서 사용할 때 한가지 해 줘야 하는 작업이 있습니다.

현재 '실행' 단추에 연결된 매크로 정보에 파일이름도 같이 등록되어 있습니다.

그래서 시트를 복사해서 '실행' 단추를 누르면 원본 파일이 실행됩니다.

이걸 해결하려면 시트를 복사한 뒤 '실행' 단추를 선택해서 '매크로 지정'을 다시 해 줘야 합니다.

 

시트 탭 '확인'에서 마우스 오른쪽 클릭해서 '이동/복사'를 선택합니다.

[이동/복사] 대화상자에서 아래쪽 '복사본 만들기'를 체크한 뒤 '대상 통합 문서'에서 '(새 통합 문서)'를 선택하고 [확인]을 누릅니다.

새 창으로 열린 복사된 시트 [CW1] 셀 '실행' 단추를 마우스 오른쪽 클릭한 뒤 '매크로 지정'을 선택합니다.

[매크로 지정] 대화상자에서 'Sheet2.실행'을 선택하고 [확인]을 클릭합니다.

 

VBA를 익히면 귀찮은 반복작업을 하지 않을 수 있습니다.

 

필요하신 분께 도움이 되길 바랍니다.