엑셀은 체계적인 구조와 깔끔한 서식, 숨겨진 로직 레이어, 버튼과 폼, 동적 인터랙티브 요소를 결합하면 강력한 미니 앱 플랫폼으로 탈바꿈할 수 있습니다. 버튼, 폼 컨트롤, 숨겨진 로직을 조합하면 데이터 입력 도구, 대시보드, 업무 진행 상황 추적기 등 다양한 인터랙티브 툴을 직접 만들 수 있습니다.
이 튜토리얼에서는 버튼, 폼, 숨겨진 로직을 활용해 엑셀을 기본적인 앱 형태로 변환하는 방법을 단계별로 알아보겠습니다.
1단계: 앱 기능 설계하기
엑셀을 앱처럼 만들려면 먼저 어떤 기능이 필요한지, 앱이 무엇을 해야 하는지 명확하게 계획해야 합니다. 여기서는 폼을 통해 주문을 접수받고, 해당 주문 데이터를 저장하는 앱을 만든다고 가정하겠습니다.
이를 위해 아래와 같은 시트들을 준비합니다.
- Home(홈): '주문 추가', '주문 데이터', '대시보드' 등 큰 버튼으로 구성된 깔끔한 시작 페이지
- Form(폼): 드롭다운 목록, 날짜, 숫자 필드 등 사용자가 입력하는 화면과 '주문 제출' 버튼
- OrderData(주문 데이터): 모든 기록을 저장하는 단일 엑셀 테이블(데이터베이스 역할)
- Logic(로직): 보조 테이블, 이름 정의 범위, 유효성 검사 규칙, ID 카운터를 담아두는 숨김 시트
- Dashboard(대시보드): 필요하다면 Logic 시트에서 데이터를 받아 KPI 카드와 차트로 구성된 대시보드 생성 가능
2단계: 주문 입력 폼 시트 만들기
- 'Order Form'이라는 이름의 새 시트를 생성합니다.
- A열에 다음 입력 항목 라벨을 나열합니다.
- 주문 ID(Order ID)
- 날짜(Date)
- 카테고리(Category)
- 제품(Product)
- 수량(Units)
- 단가(Unit Price)
- 총 금액(Total Amount)
- B열에는 입력을 위한 빈 셀을 남겨둡니다.
- 폼 서식을 보기 좋게 꾸며줍니다:
- 열 너비를 적절히 조정합니다.
- 셀 테두리를 추가합니다.
3단계: 폼 컨트롤 추가하기
폼을 역동적이고 인터랙티브하게 만들려면 드롭다운 목록을 활용하면 됩니다. 로직 시트에 카테고리, 제품, 가격 등 모든 정보를 나열한 뒤, 이를 폼 컨트롤에서 사용할 수 있도록 이름 정의 범위를 만듭니다.
이름 정의 범위 만들기:
- 제품명과 함께 카테고리 목록을 작성합니다.
- 수식 탭 >> 이름 관리자 선택 >> 새로 만들기를 클릭합니다.
Category(카테고리):
- 이름: Category 입력
- 찾는 범위: 카테고리 목록 범위 지정
Products(제품):
- 카테고리와 제품 영역을 선택합니다.
- 수식 탭 >> 선택 영역으로부터 만들기를 클릭합니다.
- 맨 윗줄을 선택합니다.
- 확인을 클릭합니다.
Unit_Price(단가):
- 제품명과 가격 영역을 선택합니다.
- 수식 탭 >> 선택 영역으로부터 만들기를 클릭합니다.
- 왼쪽 열을 선택합니다.
- 확인을 클릭합니다.
드롭다운 목록 만들기:
카테고리(Category):
- B4 셀을 선택합니다.
- 데이터 탭 >> 데이터 유효성 검사를 선택합니다.
- 제한 대상에서 목록을 선택합니다.
- 원본:에 이름 정의 범위를 입력합니다.
- 확인을 클릭합니다.
제품(Product):
- B5 셀을 선택하고 종속 드롭다운 목록을 만듭니다.
- 데이터 탭 >> 데이터 유효성 검사를 선택합니다.
- 제한 대상에서 목록을 선택합니다.
- 원본:에 아래 수식을 입력합니다.
- 확인을 클릭합니다.
- 카테고리에 따라 제품을 선택할 수 있게 됩니다.
수량(Units):
- B6 셀을 선택합니다.
- 데이터 탭 >> 데이터 유효성 검사를 선택합니다.
- 제한 대상에서 목록을 선택합니다.
- 원본:에 10까지의 목록을 입력합니다.
- 확인을 클릭합니다.
단가(Unit Price):
- B7 셀을 선택합니다.
- 데이터 탭 >> 데이터 유효성 검사를 선택합니다.
- 제한 대상에서 목록을 선택합니다.
- 원본:에 아래 수식을 입력합니다.
- 확인을 클릭합니다.
=INDIRECT(SUBSTITUTE(B5, " ", "_"))
- 이 역시 종속 드롭다운 목록입니다.
- 제품에 따라 해당 가격을 자동으로 선택할 수 있습니다.
제출 버튼 추가하기:
- 개발 도구 탭 >> 삽입 >> 양식 컨트롤에서 단추(Button)를 선택합니다.
- 폼 아래쪽에 버튼을 그립니다.
- 버튼 이름을 '주문 제출(Submit Order)'로 지정합니다.
- 일단 여기까지 두고, 매크로 연결은 5단계에서 진행합니다.
4단계: 주문 데이터베이스 및 대시보드 시트 만들기
- OrderData라는 새 시트를 추가합니다.
- 1행에 다음 헤더를 입력합니다:
- Order_ID
- Date
- Category
- Product
- Unit_Price
- Units
- Total_Amount
- 나중에 사용자에게 백엔드를 노출하지 않도록 이 시트는 숨길 예정입니다.
5단계: VBA 로직 추가하기
이제 VBA 코드를 사용해 폼에 입력된 데이터를 OrderData 시트에 저장하겠습니다. 이 코드는 폼 데이터를 데이터베이스로 복사한 후, 다음 입력을 위해 폼을 초기화하는 역할을 합니다.
- SubmitOrder 버튼을 마우스 오른쪽 버튼으로 클릭 >> 매크로 지정 >> 새로 만들기를 클릭합니다.
- 아래 코드를 삽입합니다.
Sub SubmitOrder()
Dim wsForm As Worksheet, wsDB As Worksheet
Dim nextRow As Long
Dim lastOrderID As String
Dim newOrderNum As Long
Set wsForm = ThisWorkbook.Sheets("Order Form")
Set wsDB = ThisWorkbook.Sheets("OrderData")
' 데이터베이스에서 다음 빈 행 찾기
nextRow = wsDB.Cells(wsDB.Rows.Count, "A").End(xlUp).Row + 1
' 마지막 주문 ID 가져오기 (헤더 제외)
If nextRow = 2 Then
' 아직 주문이 없으면 ? 1001부터 시작
newOrderNum = 1001
Else
lastOrderID = wsDB.Cells(nextRow - 1, 1).Value ' 예: ORD-1005
newOrderNum = CLng(Replace(lastOrderID, "ORD-", "")) + 1
End If
' 현재 주문을 데이터베이스에 저장
wsDB.Cells(nextRow, 1).Value = "ORD-" & newOrderNum
wsDB.Cells(nextRow, 2).Value = wsForm.Range("B3").Value ' 날짜
wsDB.Cells(nextRow, 3).Value = wsForm.Range("B4").Value ' 카테고리
wsDB.Cells(nextRow, 4).Value = wsForm.Range("B5").Value ' 제품
wsDB.Cells(nextRow, 5).Value = wsForm.Range("B6").Value ' 수량
wsDB.Cells(nextRow, 6).Value = wsForm.Range("B7").Value ' 단가
wsDB.Cells(nextRow, 7).Value = wsForm.Range("B8").Value ' 매출
' === 안전한 초기화: 값만 삭제 ===
Application.EnableEvents = False
wsForm.Range("B3").Value = vbNullString
wsForm.Range("B4").Value = vbNullString ' 카테고리 (유효성 검사 유지)
wsForm.Range("B5").Value = vbNullString ' 제품 (유효성 검사 유지)
wsForm.Range("B6").Value = vbNullString ' 수량 (유효성 검사 유지)
wsForm.Range("B7").Value = vbNullString ' 단가 (유효성 검사 유지)
wsForm.Range("B8").Formula = "=B6*B7" ' 매출 수식 복원
Application.EnableEvents = True
' 다음 입력을 위한 주문 ID 자동 생성
wsForm.Range("B2").Value = "ORD-" & (newOrderNum + 1)
MsgBox "주문이 성공적으로 제출되었습니다!", vbInformation
End Sub
코드 설명:
- 주문 ID는 제출할 때마다 자동으로 증가합니다.
- 데이터베이스가 비어 있다면 첫 번째 제출 시 ORD-1001부터 시작합니다.
- 클릭할 때마다:
- 매크로가 마지막으로 저장된 주문 번호를 확인합니다.
- 번호를 1 증가시킵니다.
- 폼의 B2 셀에 다음 사용 가능한 ID를 채워 넣습니다.
- 값만 삭제하고 수식이나 데이터 유효성 검사는 그대로 유지되므로, 다음 입력이 항상 깨끗한 상태에서 시작됩니다.
6단계: 대시보드 시트 만들기
이제 주문 데이터를 기반으로 대시보드를 만들 수 있습니다.
- KPI 지표 만들기: 총 주문 건수, 매출액, 판매 수량, 평균 주문 금액 등을 계산합니다.
- 차트 삽입: 동적 차트를 만들거나 피벗 차트(PivotChart)를 삽입합니다.
7단계: 앱 같은 느낌을 위한 시트 서식 지정
홈페이지 만들기:
- 삽입 탭 >> 일러스트레이션 >> 도형을 선택합니다.
- Button(단추) 도형을 선택합니다.
- 도형을 셀 위로 끌어다 놓습니다.
- 도형을 마우스 오른쪽 버튼으로 클릭 >> 연결(Link)을 선택합니다.
- 문서 내 위치를 선택 >> 이동할 시트의 셀을 지정합니다(코드 없이 작동하는 내비게이션 버튼).
- Order Form 시트를 선택합니다.
- 확인을 클릭합니다.
- 같은 방법으로 대시보드와 OrderData 시트에 대한 하이퍼링크 버튼도 추가합니다.
- 추후 보안을 위해 OrderData 시트는 잠그는 것이 좋습니다.
로직 시트 숨기기:
엑셀이 더욱 앱처럼 작동하도록 하려면:
- 시트 탭을 선택합니다.
- 마우스 오른쪽 버튼 클릭 >> 숨기기를 선택합니다.
- 입력 셀만 수정할 수 있도록 Order Form 시트를 보호합니다.
- 보기 탭에서:
- 수식 입력줄의 체크를 해제합니다.
- 눈금선의 체크를 해제합니다.
8단계: 주문 앱 테스트하기
- 예시 주문을 하나 입력해 봅니다:
- 주문 ID: 자동으로 입력됩니다.
- 날짜: 2025-03-01 날짜를 입력합니다.
- 카테고리: 드롭다운 목록에서 카테고리를 선택합니다.
- 제품: 종속 드롭다운에서 Mouse(마우스)를 선택합니다.
- 수량: 목록에서 수량을 선택합니다.
- 단가: 종속 드롭다운에서 가격을 선택합니다.
- 매출(Total Amount): 자동 계산됩니다.
- 주문 제출 버튼을 클릭합니다.
- 다음 주문 ID가 자동으로 표시됩니다.
- 폼이 초기화되어 다음 주문을 바로 입력할 수 있습니다.
- 제출이 성공하면 메시지 상자가 나타납니다.
- 확인을 클릭합니다.
- OrderData 시트를 확인하면 입력한 내역이 자동으로 저장된 것을 볼 수 있습니다.
결론
위의 단계를 따라 하면 일반 엑셀 시트를 하나의 앱으로 변신시킬 수 있습니다. 버튼, 폼, 숨겨진 로직을 활용하면 누구나 동적인 앱을 만들 수 있습니다. 이런 앱 스타일의 도구는 전용 소프트웨어에 비용을 들이지 않고도 데이터 입력 과정을 간소화하고 사용자 실수를 줄이는 강력한 방법입니다. 깔끔한 프런트엔드 폼, 데이터 유효성 검사 드롭다운, 숨겨진 데이터베이스 시트, VBA 자동화를 결합하여 완전히 작동하는 주문 관리 시스템을 완성했습니다. 여기에 더해 대시보드, 요약 보고서, 심지어 Power Query 연동까지 확장하면 한층 고급 분석 환경을 구축할 수 있습니다.