엑셀(Excel)로 간단한 데이터베이스를 만드는 방법을 모르고 계신가요? 이 글에서는 단 7단계만 거치면 누구나 엑셀에서 데이터베이스를 구축할 수 있는 방법을 소개합니다.
MS 액세스(MS Access)가 데이터베이스 도구로는 다소 복잡하게 느껴진다면, 엑셀이 훌륭한 대안이 되어 줄 것입니다.
그럼 지금부터 그 방법을 하나씩 살펴보겠습니다.
아래 실습용 워크북을 다운로드하면 내용을 더욱 명확하게 이해하는 데 도움이 됩니다.
엑셀에서 데이터베이스 만들기: 7단계
엑셀 통합 문서를 올바른 구조로 설계하면 데이터베이스처럼 손쉽게 활용할 수 있습니다. 핵심은 통합 문서를 체계적으로 설계하는 것입니다. 이렇게 하면 데이터를 다양한 방식으로 정렬할 수 있고, 특정 조건에 맞는 데이터만 골라 볼 수 있도록 필터링하는 것도 가능합니다.
이번 글에서는 실제 예제를 바탕으로 엑셀 기반 데이터베이스를 만드는 전 과정을 단계별로 시연해 보겠습니다.
1단계: 데이터 입력하기
데이터베이스에서 열(column)은 '필드(Field)'라고 부르며, 필요한 만큼 자유롭게 추가할 수 있습니다.
이 예제 데이터베이스의 필드는 StdID(학번), StdName(이름), State(주), Age(나이), Department(학과)입니다.
필드를 정했다면 이제 데이터를 입력하면 됩니다. 새로운 데이터는 항상 필드 바로 아래의 첫 번째 빈 행에 추가됩니다.
몇 개의 데이터를 미리 입력해 두었습니다. 이번에는 새 레코드를 추가하는 과정을 살펴보겠습니다.
예를 들어 다음과 같은 정보를 데이터베이스에 삽입한다고 가정해 보겠습니다.
StdID: 1510060,
StdName: Jimmy,
State: Florida,
Student's Age: 23,
Department: ME
이처럼 엑셀 데이터베이스에 데이터를 입력하는 것은 매우 간단합니다.
2단계: 빈 행을 남기지 않기
- 데이터베이스에 데이터를 입력할 때는 행을 비워 두어서는 안 됩니다.
예를 들어 마지막 행 다음을 한 칸 건너뛰고 그다음 행에 데이터를 입력했다고 가정해 보겠습니다.
StdID: 1510060,
StdName: Jimmy,
State: Florida,
Student's Age: 23,
Department: ME
이 경우 데이터베이스가 깨져 버립니다. 물론 한 행 안에서 일부 셀만 비어 있는 것은 허용됩니다.
- 마찬가지로, 데이터베이스 안에 완전히 빈 열이 존재해서도 안 됩니다.
완전히 빈 행이나 열이 있으면 엑셀은 해당 행·열을 데이터베이스에 포함시키지 못합니다. 즉, 엑셀은 이 데이터베이스를 서로 연결되지 않은 두 개의 독립적인 정보 집합으로 인식합니다. 이후 어떤 기능을 수행하려 해도 이 떨어져 나간 정보에는 적용되지 않습니다. 예를 들어 필터링처럼 간단한 작업조차 실패하게 되는데, 직접 경험해 보면 바로 알 수 있습니다.
3단계: 꼭 알아야 할 두 가지 용어 익히기
다음으로 알아야 할 것은 데이터베이스에서 각각의 행(row)을 레코드(Records)라고 부른다는 점입니다.
모든 행이 곧 레코드입니다. 이해를 돕기 위해 몇 개를 표시해 두었습니다.
또한 모든 열은 필드(Fields)이며, 열의 제목은 필드 이름(Field Names)이라고 합니다.
즉, StdID, StdName, State, Age, Department가 이 데이터베이스의 다섯 가지 필드 이름입니다.
4단계: 엑셀 표(Table) 만들기
표를 만들려면 아래 단계를 따르세요.
- 먼저 삽입 탭으로 이동하여 표 명령을 클릭합니다.
- 그러면 표 만들기(Create Table) 창이 나타납니다. 마우스로 드래그하여 셀 범위, 즉 $B$4:$F$10을 선택합니다.
- '머리글 포함(My table has headers)' 옵션 앞의 확인란을 반드시 체크하세요.
그러면 즉시 표가 생성됩니다. 짜잔!
또한 각 열 제목에 나타나는 드롭다운 화살표를 이용해 데이터를 필터링할 수도 있습니다.
5단계: 데이터베이스 도구 활용하기
데이터베이스 도구는 데이터 분석과 해석에 큰 도움이 됩니다. 데이터베이스 도구에 대해 더 자세히 배워두는 것이 좋습니다.
6단계: 데이터베이스 확장하기
모든 것이 잘 작동한다면, 이제 데이터베이스에 더 많은 필드와 레코드를 추가할 수 있습니다. 방법은 1단계만큼이나 간단합니다.
7단계: 데이터베이스 서식 완성하기
마지막 단계는 데이터베이스 열의 서식을 지정하는 것입니다. 데이터베이스의 셀 서식을 지정할 수 있는 도구는 매우 다양합니다. 셀 스타일(Cell Styles)을 사용하거나, '표 서식' 드롭다운 메뉴의 스타일을 활용하거나, 셀 서식 대화 상자의 명령을 사용할 수 있습니다. 사용자 지정 숫자 서식(Custom Number Format)도 유용합니다. 이러한 기법들은 이전 강의에서 자세히 설명해 두었습니다.
이것으로 끝입니다! 여러분만의 데이터베이스를 엑셀로 성공적으로 만들었습니다(액세스를 완전히 익힐 때까지, 혹은 엑셀의 용량과 처리 한계에 도달할 때까지 말이죠).
검색 가능한 데이터베이스 만드는 방법
방대한 데이터 소스에서 원하는 데이터를 찾아야 할 때가 있습니다. 이럴 때 검색 가능한 데이터베이스가 있다면 손쉽게 데이터를 가져올 수 있습니다. 검색 가능한 데이터베이스를 만들려면 몇 가지 간단한 단계만 거치면 됩니다.
📌 단계:
- 먼저 셀 F5를 선택하고 아래 수식을 입력합니다.
수식 설명:
SEARCH 함수 → 사용자가 찾고자 하는 특정 값을 검색합니다.
ISNUMBER 함수 → 논리 함수로, SEARCH 함수의 결과가 숫자이면 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다.
FILTER 함수 → 원하는 조건에 맞는 결과값만 골라서 표시합니다.
- ENTER 키를 누른 후 채우기 핸들(Fill Handle) 도구를 사용합니다.
- 그러면 아래 그림과 같이 결과가 표시됩니다.
- 그다음 셀 C4를 선택하고 데이터 탭 >> 데이터 도구 >> 데이터 유효성 검사로 이동합니다.
- 데이터 유효성 검사 대화 상자가 나타나면 설정 >> 제한 대상 섹션에서 목록을 선택 >> 원본 상자에 필터링된 셀을 입력합니다. 즉, 원본 상자에 아래 수식을 입력하세요.
- 오류 경고 옵션으로 이동합니다.
- 오류 경고 탭에서 '무효한 데이터가 입력되면 오류 메시지 표시' 항목의 체크를 해제합니다.
- 확인을 누릅니다.
- 드디어 검색 가능한 데이터베이스가 완성되었습니다! 이제 B4 셀에 "P"를 입력하면 전체 이름인 "Peter"가 자동으로 나타납니다.
자동으로 업데이트되는 데이터베이스 만드는 방법
데이터베이스에 입력된 데이터는 자동으로 업데이트될 필요가 있습니다. 이를 위해 원본 데이터 세트로 피벗 테이블(PivotTable)을 만들고, 새로 고침 기능을 활성화하면 이후 입력되는 새 데이터가 기존 피벗 테이블에 자동으로 반영됩니다. 아래 단계를 따라 해 보세요.
📌 단계:
- 먼저 데이터가 있는 셀 범위를 모두 선택합니다. 삽입 탭 >> 피벗 테이블 >> 테이블/범위에서를 차례로 선택합니다.
- 피벗 테이블이 생성되면, 업데이트에 사용할 열을 선택할 수 있습니다.
- 마지막으로 아무 셀이나 마우스 오른쪽 버튼으로 클릭한 뒤 새로 고침 명령을 선택하면, 기본 워크시트에서 데이터를 변경할 때마다 피벗 테이블이 자동으로 업데이트됩니다.
또한 피벗 테이블을 업데이트하는 4가지 방법도 함께 참고해 보세요.
관계형 데이터베이스 만드는 방법
관계형 데이터베이스(Relational Database)는 여러 워크시트 간의 관계를 파악하는 데 중점을 둡니다. 관계형 데이터베이스를 활용하면 특정 정보를 빠르게 검색하고 추출할 수 있으며, 동일한 데이터 값을 여러 가지 방식으로 표시할 수도 있습니다.
예를 들어 Database1과 Database2라는 두 개의 데이터베이스가 있다고 가정해 보겠습니다. Database1에는 직원 이름과 급여(Salary) 정보가, Database2에는 직원 이름과 직급(Designation) 정보가 들어 있습니다. 이제 직원(Employee) 필드를 기준으로 두 데이터베이스를 연결하는 관계형 데이터베이스를 만들어 보겠습니다. 아래 단계를 따르세요.
📌 단계:
- 먼저 Dataset2의 전체 범위를 선택합니다.
- 그런 다음 삽입 탭 >> 피벗 테이블 >> 테이블/범위에서로 이동합니다.
- 이어서 Dataset1이라는 다른 워크시트로 이동하여 앞서 설명한 방법으로 표를 만듭니다.
참고: 표를 만들 때는 키보드 단축키 CTRL + T를 사용할 수도 있습니다.
- 피벗 테이블이 나타나면 관계를 맺고 싶은 필드를 선택합니다. 예를 들어 여기서는 서로 다른 두 워크시트에서 직급(Designation) 열과 급여(Salary) 열을 선택했습니다.
- 데이터 선택을 마친 후 피벗 테이블 필드 대화 상자의 모두 아래에 있는 만들기(CREATE) 옵션을 클릭합니다.
- 마침내 아래 그림과 같이 관계형 데이터베이스가 완성됩니다.
연습 섹션
각 시트 오른쪽에 연습용 섹션을 마련해 두었습니다. 직접 실습해 보시기 바랍니다.
마무리
오늘 세션은 여기까지입니다. 지금까지 엑셀에서 데이터베이스를 만드는 간단한 단계들을 살펴보았습니다. 질문이나 제안 사항이 있다면 댓글로 알려주세요. 이해를 돕기 위해 실습용 시트를 다운로드해서 활용해 보시기 바랍니다. 다양한 엑셀 활용법이 궁금하다면 올인원 엑셀 솔루션 제공업체인 Exceldemy 웹사이트를 방문해 보세요. 끝까지 읽어 주셔서 감사합니다.