취미로 엑셀하기

VBA 활용해서 특정 폴더 내 파일 이름을 추출하는 것과 모든 워크시트의 이름을 추출하는 VBA 코드를 블로그에 올렸다. 하는 김에 선택된 폴더 내 모든 하위 폴더를 스캔해서 모든 파일명을 조사한 뒤에 그파일명과 경로까지 엑셀에 리스트 업 해주는 코드를 만들었다(사실 GPT한테 만들어달라고 했다) Sub ListFilesInFolder() Dim FolderPath As String Dim FileName As String Dim i As Integer Dim j As Integer ' Get the folder path With Application.FileDialog(msoFileDialogFolderPicker) .AllowMultiSelect = False If .Show = -1 Then Fol..

업무를 하다보면 여러곳에서 온 파일들을 하나의 폴더에 취합하는 일이 자주 있습니다. 그때 그 폴더내의 모든 파일들을 관리하고자 할때 파일명을 리스트업 해야하는 상황을 자주 맞닥드리게 됩니다. 그때 꼭 CMD를 사용하지 않고도 엑셀로 바로 폴도 내 파일명을 모두 추출 할 수 있는 방법이 있습니다. 블로그에서도 몇번 소개드렸던 VBA 코드를 사용 하실 수 있어야 합니다. ALT+F11 -> 삽입 -> 모듈 -> 실행 삽입-모듈까지하시면 빈창이 하나 뜨는데 다음 코드를 붙여 보세요 Sub GetFileList() Dim MyFolder As String Dim MyFile As String Dim i As Integer ' 폴더 선택 다이얼로그를 엽니다. With Application.FileDialog(ms..

엑셀로 업무를 하다보면 정말 수많은 시트가 한 파일에 있는 경우가 있습니다. 이 워크시트들의 업데이트 여부나 필요여부 등을 조사할때 시트명을 추출할 필요가 있는데요 그때 하나씩 워크시트를 선택해가며 이름을 복사하기에는 손이 너무 많이 갑니다. 그럴때는 ALT+F11을 누른뒤 삽입-모듈 을 선택하여 다음 코드를 활용하면 좋습니다. Sub ListAllSheetNames() Dim ws As Worksheet Dim i As Integer '새 시트 생성 Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet Names" i = 1 For Each ws In Worksheets '시트 이름 리스트업 If ws.Name "Sheet Names" T..

엑셀로 업무를 하시다 보면 종종 부딪히는 문제입니다. 예를들어 A(1월)라는 시트를 기초자료로 사용하고 B라는 시트에 잔뜩 계산 함수를 만들어 놓은 상태일때 다음달에 A(2월) 자료가 오면 B시트의 함수를 그대로 적용하고 싶을때가 있습니다. 물론 A(1월) 시트 안에다가 A(2월) 시트를 값으로 붙여놓는 것도 방법이긴하지만 이러저러한 사정으로 시트자체를 바꿔서 적용하고 싶을때가 있습니다. 예를들면 다음과 같은 상황인거죠 이때 B시트의 함수를 하나씩 바꾸기에는 너무 손이 많이 갑니다. 그럴때는 함수를 한번에 바꾸는 방법을 쓰시면 됩니다. 현재 함수가 걸려있는 B 컬럼을 선택한뒤 컨트롤+F 를 눌러봅니다 찾기밎 바꾸기가 뜨면 바꾸기를 누르시고 기존 함수에 입력된 값을 찾아서 함수가 대상으로 하는 워크시명을 ..

오늘은 엑셀 두개 시트를 비교해서 틀린 부분이 있는 경우 자동으로 색을 칠해주는 VBA 코드를 알려드리겠습니다. 예를들어 엑셀 파일 내에 sheet1과 sheet2 두개 워크시트가 있는데 아주 일부분의 값만 차이가 날때 모든 셀이 같은지 수식을 거는것도 방법이긴 합니다. 다만 시트 안에 값이 너무 많은 경우 수식을 거는거 자체가 귀찮고 번거로울 수 있습니다. 그때는 다음 VBA 코드를 돌리면 자동으로 비교해서 색을 칠해줍니다. 예를들어 위 두 시트에서 차이가 나는 부분을 비교한다면 눈으로 차이를 찾기가 매우 어렵습니다. 엑셀을 켜두고 Alt+F11을 눌러 다음 화면을 봅니다 뭔가 심란한 화면이 나왔지만 무서워하지 마세요 삽입-모듈을 누릅니다 나오는 새 창에 아래 코드를 복사 붙여넣기 합니다. Sub Co..

오늘도 엑셀 함수 관련 간단한 포스팅 입니다~ 만약에 관리하고 있는 명단 양식에 동명이인이 있고, 동명이인인 경우 이름 뒤에 숫자를 붙여서 관리하고 있는 경우에 이름만 뽑아내는 방법 입니다. 예를들면 이런 상황인거죠 1) 보시면 번호가 있거나 없을수 있고 2) 이름 길이가 제각각인 경우가 있습니다. 이럴때 순수하게 이름만 뽑아야 하는 경우 함수를 걸기가 난감해집니다. 그럼 일단 규칙을 보면 다음과 같습니다. 1) 현재 명단에는 이름 뒤에 붙는 숫자가 10을 넘지 않는다(즉 한자리 수 이다) 2) 숫자를 항상 이름 뒤에 띄어쓰기가 없는 채로 붙는다 => 숫자가 붙은 경우는 숫자를 제거하고 없는 경우라면 이름만 출력하면 된다. 자 그래서 바로 함수를 확인하면 다음과 같습니다.(A2에 대상 이름이 있는 경우)..

엑셀 작업 중 수식이 틀린 경우 찾아야 할 때가 있습니다. 수식 하나씩 찍어보면서 찾으면 어떻게 어떻게 찾을 수 있긴한데 내가 편집하고 있는 워크 시트 전체가 수식이거나 하면 영 당황스러울수밖에 없지요 특히나 다른사람과 동시에 편집하는 파일의 경우에는 누군가 수식을 값으로 바꿔서 저장해 놓는 경우가 흔합니다. 예를들어 정해진 등급에 따라 수수료율이 변경되어 수수료가 결정되는 위와 같은 표에서 누군가 열심히 일하면서....아 나는 계산기를 통해 완벽히 검증한 숫자를 업로드 해야 하면서 손으로 타이핑해서 자료를 넘겨주었다고 합시다. 지금은 별도의 검증 셀을 등급별 수수료 수식과 다르게 만들어두었기 때문에 틀렸다는 점을 바로 인지 할 수 있습니다. 이렇게 미리 미리 검증 수식을 만들어 두는것 자체도 도움이 되..

예를들어 A열에 특정 날짜가 있고 B열에 또다른 특정날짜가 있을때 C열에 해당 날짜를 조합해서 보고싶을때 사용하는 방법입니다. 이건 뭐 함수라고 보기는 어렵고 & 글자를 사용하면 바로 하실수있는 방법업니다. =가져올셀위치1&가져올셀위치2 하게되면 합쳐야 할 셀에 첫번째 셀의값과 두번째 셀의 값을 동시에 보여줍니다. 위가 그 예시이고 &를 잘 활용하는 경우 문자도 한번에 입력이 가능합니다. 예를들어 위의 경우 & 를 통해 두가지 셀 값을 가져오기만 했는데요 만약에 신규계약은 1/1일이며, 계약전환은 2/1일 입니다 라고 자동으로 작성이 되게 하고 싶다면 ="신규계약은 "&A2&"일이며, 계약전환은 "&B2&"일 입니다." 라고 입력하면 위처럼 왠만한 보고서 문구를 자동으로 작성되제 할 수 있습니다. 보통 ..

안녕하세요 오랜만에 블로그에 글을 쓰네요 이번내용은 엑셀로 많은 작업을 하시던 분이라면 한번쯤 당황하실만한 내용입낟. 우리가 누군가에게 전달받거나 전달해주는 데이터의 경우 RAW 데이터 형태를 띄고있는 경우는 잘 없습니다. 아래처럼 표 형태로 만들어서 잘 정리해서 전달을 하겠지요 (아래자료는 통계청에서 아무거나 예시로 내려받아 편집한 것입니다.) 근데 사실 이 표 형태가 다시 재 가공을 하기가 매우 까다롭습니다. 다시 표를 데이터 형태로 만들어놓아야 피벗을 걸고 할텐데 저상태로는 절대 피벗을 걸수가 없어요 그럼 저 표 형태를 쉽게 다시 데이터 형태로 변경하면 다음커럼 1 2열은 구분값을 3열은 데이터가 나와야 할것입니다. 이걸 다시 만들어내는게 여간 노가다가 아닙니다. 그런데 파워피벗에 해당 기능이 있더..

명색이 취미로 엑셀하기 블로그인데 취미생활을 해봐야겠네요 회계 계정별 명세서 데이터를 넣으면 보고서가 튀어나오는 양식 관리시트 만들면서 익혀봅시다 필요한 양식을 댓글에 달아주세요