직장인의 엑셀, VBA 그리고 Python

들어가며  엑셀에서 날짜를 입력할 때 키보드로 날짜를 하나하나 타이핑하기가 귀찮을 때가 있습니다.손으로 타이핑하다 보면 실수를 할 수 있는 요소도 많습니다. 이번 블로그는 VBA 코드 작성이 아닌VBA로 만든 프로그램을 소개하는 블로그입니다.  설치하기  먼저 Google에서 Date Picker로 검색을 하면아래 주소의 사이트를 찾을 수 있습니다.Excel date picker add-in — Rad Sheets Excel date picker add-in — Rad SheetsA modern date picker for excelsamradapps.com 조금 스크롤을 내리면 아래 그림과 같은 화면을 찾을 수 있고Download Now를 클릭해 파일을 다운로드합니다.다운로드한 파일의 압축을 풀면아래와..

들어가며  엑셀에서 데이터를 분석하고 관리할 때 표 기능은 매우 유용합니다. 필터링, 정렬, 서식 지정 등 작업을 쉽게 수행하게 해 줍니다. 이번 블로그에서는 VBA 코드를 사용하여일반 셀 범위를 엑셀 표로 바꾸는 방법을 알아보겠습니다.  구현할 내용  왼쪽 그림과 같은 범위를오른쪽 그림의 표로 바꾸는 VBA 코드입니다.   코드 작성하기  Sub ConvertRangeToTable() Dim ws As Worksheet Dim dataRange As Range ' 작업할 워크시트 설정 Set ws = ThisWorkbook.Worksheets("Sheet1") ' 데이터 범위 설정 Set dataRange = ws.Range("A1:D10") ..

지난 블로그에 이어서 작성되는 블로그입니다.※ [ 엑셀 VBA ] 간단한 파일 관리 및 분류 프로그램 만들기-첫번째   워크시트 더블 클릭 시 바로가기 및 실행 창 열기  Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.row 7 Then Exit Sub If IsEmpty(Cells(Target.row, "A")) Then Exit Sub Cancel = True SelectedRow = Target.row UserForm2.ShowEnd Sub 더블클릭한 셀이 7행보다 작거나 7열 즉, G열보다 오른쪽에 있는 열을 클릭하거나,A열이 비어있는 셀을 선택하면 실..

파일을 분류할 때 이런 경우가 있습니다.어떤 파일은 A팀 자료도 있고 B팀 자료도 있지만,A팀에서 만들었기 때문에 A팀 폴더에 분류하는 경우 웹 스크래핑 코드이면서, 부동산 수집 데이터인 경우와 같이여러 가지 성질을 가지고 있지만,폴더 시스템 구조 상 여러 개로 분리하여 관리하기가 쉽지 않아서간단한 파일 관리 및 분류 프로그램을 VBA 코드 공부할 겸 제작한 것입니다. 정리하면 주요 기능은하나의 파일이 여러 분야에 속할 수 있는 경우 분류를 하는 것입니다.   기본 구성   파일 불러오기를 누르면 파일을 선택하고 파일을 분류합니다.위 파일은 workspace라는 json 파일을 파이썬과 VBA로 분류한 예시입니다. 셀을 더블클릭하면 아래 그림과 같이 바로가기와 실행하기 창이 열립니다. 바로가기를 누르면 ..

함수 기능 설명  ADVLOOKUP 함수는 인자를 세 개를 받습니다.첫 번째 인자는 기준 값두 번째 인자는 기준 값이 있는 열 범위세 번째 인자는 찾을 값이 기준 열 범위에서 좌측 또는 우측으로 얼마나 떨어져 있는지 ADVLOOKUP 함수는 중복값이 있을 경우 "중복값있음"을 반환하고 값을 찾지 않습니다.VLOOKUP 사용 시 중복값이 있을 경우에도 값이 찾아지는 부분을 보완했습니다. ADVLOOKUP 함수는 좌측의 값을 찾을 수 있습니다.세 번째 인자가 0일 경우 두 번째 인자에 지정한 범위에서 찾고양수를 입력하면 우측값음수를 입력하면 좌측값을 찾습니다.   동작 모습  F1셀의 수식입니다.=ADVLOOKUP(E1, B1:B5, 1) 기준 값 돼지는 B1~B5범위에 있고, 세 번째 인자 1을 입력하면 ..

아래 그림과 같이 형태를 바꾸는 VBA 사용자 정의함수입니다.이렇게 각 반의 각 과목 평균을 정리한 데이터가 있습니다. 이런 데이터를 아래 그림과 같이 형태를 바꾸려합니다.이번 블로그에서는 점수까지 채우지는 않겠습니다. 이 기능을 함수로 구현하려면 매우 복잡한 함수식을 사용해야 하는 것으로 알고 있습니다. 이렇게 조합을 입력받아 데이터의 형태를 바꿔주는 사용자 정의함수를 만들어 보겠으며,두 개의 범위를 입력받아 위 그림과 같이 출력하는 기능을 수행합니다.   구현 모습  함수가 구현되는 모습은 다음과 같습니다.  전체 코드  전체 코드는 다음과 같습니다.Function Combine_Range(range1 As Range, range2 As Range) As Variant Dim result() ..

엑셀을 사용하다보면특정 색상의 셀에만 포함된 값의 합계를 구하고 싶을 때가 있습니다.예를들어, 빨간색으로 표시된 중요한 데이터만 계산하거나,색으로 구분된 데이터의 비용을 합산해야 하는 경우와 같은 것들입니다. 이번 블로그에서는엑셀에서 특정 색상의 셀에 포함된 값의 합계를 구하는 사용자 정의함수를간단하게 만들어 보겠습니다.※ 사용자 정의함수 등록 방법 : 사용자 정의함수 등록하고 다른 엑셀 파일에서 호출하기  구현내용  =SumByColor(기준셀, 계산 범위) 아래 그림과 같이 같은 색의 셀이 있는 데이터의 합계만 구하는 함수입니다.  전체코드  아래는 전체 코드입니다.Alt + F11로 VBA 편집기 실행 후 모듈에 추가하시면 됩니다.Function SumByColor(cellAddress As Ran..

입력된 두 범위의 값을 비교하는 코드입니다.   전체코드  Option ExplicitSub CompareRangesWithInput() Dim ws As Worksheet Dim rng_A As Range, rng_B As Range Dim cell_A As Range, cell_B As Range Dim dict_A As Object, dict_B As Object Dim commonValues As String, only_A As String, only_B As String Dim msg As String Dim answer As VbMsgBoxResult Dim input_A As Range, input_B As Range ' 현재 시트 설정..

제 블로그에서 몇 가지 사용자 정의함수를 만들어 본 적이 있습니다.1. 번역 사용자 정의 함수 만들기2. 윤년을 확인하는 사용자 정의함수 만들기3. UNIQUE 함수와 유사한 기능을 하는 사용자 정의 함수 만들기4. 문자열에서 숫자만 추출 이렇게 만든 사용자 정의함수를 엑셀에 등록을 해 놓으면현재 컴퓨터의 모든 엑셀 파일에서 등록한 사용자 정의함수를 사용할 수 있습니다. 사용자 정의함수를 만들고 등록하는 절차는 다음과 같습니다.  사용자 정의함수 만들기  먼저 VBA 편집기를 실행하고 모듈을 삽입합니다.그다음 함수를 작성을 해줍니다.Function 키워드를 사용해 함수를 작성합니다. 저는 지난 블로그에서 작성한 문자열에서 숫자만 추출하는 사용자 정의함수를 사용하겠습니다. 아래 그림과 같이 코드를 입력합니..

지난 블로그에서 문자열에서 숫자를 추출하는 함수식을 소개했었습니다.※ 문자열에서 숫자 추출 : 숫자만 추출하기 이 함수식과 유사한 기능을 하도록VBA를 사용해 사용자 정의함수를 만들어 사용하면더욱 편리하게 사용할 수 있습니다.  지난 블로그에서 작성한 예시처럼정제되지 않은 문자열에서 숫자를 추출할 수도 있고,전화번호에서 숫자를 추출하거나,제품 코드에서 숫자 부분만 필요할 때 등상황에 맞게 활용할 수 있습니다.  전체 코드  Function ExtractNumbers(txt As String) As Long Dim i As Integer Dim result As String result = "" For i = 1 To Len(txt) If Mid(txt, i, 1) Lik..