[Outlook 2003, 2007]받는 사람을 선택하면 전자 메일 주소와 팩스 번호 연락처가 두 번 나열된다
[Excel] 문서에 오류가 있는지 확인하는 방법
Excel 문서를 편집하는 도중에
- "셀 서식이 너무 많습니다." 메시지가 나오면서 서식을 더 이상 추가할 수 없거나,
- 문서의 크기가 예상보다 너무 클 때,
- 특정 이름이 이미 있다는 메시지가 나오면서 '이름 충돌' 메시지가 계속 나올 때가 있을 것입니다.
문서에 오류가 있는지 확인하는 방법에 대해서 설명합니다.
※ 문서를 수정하기 전에 수정 과정에서 데이터가 손실될 가능성이 있으므로 백업 본을 하나 만들어 놓습니다.
현상 및 원인
"셀 서식이 너무 많습니다."
Excel의 Workbook은
- 97-2003 버전의 경우 약 4,000개
- 2007 버전의 경우 약 64,000개
의 서로 다른 셀 서식 조합을 가질 수 있습니다.
셀 서식 조합이라는 것은 글꼴 서식(예- 글꼴 종류, 크기, 기울임, 굵은 글꼴, 밑줄 등)이나 괘선(괘선의 위치, 색상 등), 무늬나 음영, 표시 형식, 맞춤, 셀 보호 등을 포함합니다. Excel 2007에서는 1,024개의 전역 글꼴 종류를 사용할 수 있고 통합 문서당 512개까지 사용할 수 있습니다. 따라서 셀 서식 조합의 개수 제한을 초과한 경우에는 "셀 서식이 너무 많습니다." 메시지가 발생하는 것입니다.
그러나 대부분의 경우, 사용자가 직접 넣은 서식으로 개수 제한을 초과하는 경우는 드뭅니다.
- 셀 서식이 개수 제한을 넘도록 자동으로 서식을 추가해 주는 Laroux나 Pldt 같은 매크로 바이러스에 감염이 되었거나,
- 매크로 바이러스에 감염이 되었던 문서의 시트를 [시트 이동/복사]하여 가져온 경우 시트의 서식, 스타일이 옮겨와 문제가 될 수 있습니다.
"셀 서식이 너무 많습니다." 메시지가 발생하지 않도록 하기 위한 예방법
- 글꼴(종류, 크기, 색, 굵기, 기울임, 밑줄), 셀 채우기 색, 행 높이, 열 너비, 테두리(선 종류, 굵기, 색) 등을 하나로 변경, 통합합니다.
- 다른 파일로부터 시트 이동/복사를 해 올 때에는 이동 복사하려는 원본에 오류가 있지 않은지 사전에 점검을 하고 이동/복사합니다.
이번 포스트의 내용대로 오류를 수정하였음에도 불구하고 셀 서식을 통합할 수 없는 상황이라고 한다면, 시트를 여러 파일로 나누어 사용하셔야 합니다.
문서의 크기가 예상보다 너무 클 때
문서에 그림이나 도형, 차트와 같은 개체가 몇 개 들어가 있지 않고, 시트 수도 작으며, 데이터 입력 범위도 좁은데 비해, 파일 크기가 큰 경우가 있습니다.
대부분의 원인은 눈에 보이지 않는 개체가 많이 삽입되어 있는 경우입니다.
- 눈에 보이지 않는 개체가 들어가게 되는 원인으로는 다른 문서의 내용을 [시트 이동/복사] 해 왔는데, 그 원본에 이미 눈에 보이지 않는 개체가 많이 있었거나
- 웹의 내용이나 다른 Application의 내용을 복사한 뒤 붙여 넣었을 때 도형으로 삽입되는 경우가 있습니다.
이름 충돌 오류가 발생하는 경우
Excel에서 이름 정의를 이용하면 간편하게 수식을 편집하거나, Word, Outlook, Access와 같은 다른 Office 프로그램과 연동할 수 있습니다. 그러나 이름 정의에 잘못된 이름이 많이 포함된 경우 '이름 충돌' 오류가 발생하여 새로운 이름으로 적으라는 메시지가 발생할 수 있습니다.
Ctrl+F3을 누르면, 이름 관리자 (또는 이름 정의) 창이 열립니다. 새 문서에서 확인하면, 이름 정의는 기본적으로 비어 있습니다.
직접 입력하지 않은 이름이 많이 들어가 있다면 다른 문서에서 [시트 이동/복사]를 통해서 옮겨와 졌을 가능성이 높습니다. 물론 표 삽입과 같은 기능을 통해서 자동으로 삽입되는 경우는 있습니다.
문제가 있는 이름의 경우 이름이 무엇인지 알 수 없도록 이름의 글자가 깨져 있거나, 'ㄱ', 'ㄴ'과 같은 이름으로 추가 되어 있는 경우가 많습니다.
문서 점검하기
아래의 순서대로 문서를 점검해 보십시오.
문서에 따라서 잘 해결될 수도 있으나 적용이 안 되는 문서도 있을 수 있습니다.
매크로 바이러스가 포함되어 있는지 확인
Excel의 기능에는 '매크로'라는 것을 이용하여 원하는 기능을 자동화할 수 있습니다. 기능으로 제공되지 않는 것을 매크로 코드를 통해 자동화 할 수 있으므로 매우 편리한 기능입니다. 그러나 이 기능을 악용하여 셀 서식을 한꺼번에 개수 제한을 초과하도록 넣어주는 Laroux, Pdlt와 같은 매크로 바이러스가 있습니다. 바이러스가 치료되었다고 하더라도 셀 서식, 스타일들은 그대로 남아 있기 때문에 문서를 그대로 이용할 경우 문제가 발생할 수 있습니다.
아래와 같은 방법으로 바이러스에 감염되었던 파일을 치료할 수 있습니다.
문제 있는 시트 삭제
1. Alt+F11을 누르면 'Microsoft Visual Basic' 창이 열립니다.
2. [보기]-[프로젝트 창]을 선택하면 왼쪽에 '프로젝트-VBAProject' 창이 열립니다.
3. 시트 이름 중에서 'XXXXXXX', 'VXXXXX'와 같은 이름의 시트 또는 차트가 있는지 확인합니다.
4. [보기]-[속성 창]을 누릅니다.
5. 'XXXXXXX' 시트 또는 차트를 선택하면 그에 해당하는 속성이 '속성' 창에 표시됩니다.
6. 'Visible' 속성이 '2 – xlSheetVeryHidden'으로 설정되어 있는지 확인합니다. '2 – xlSheetVeryHidden'으로 설정되어 있는 경우, 파일에 시트가 있음에도 불구하고, VBA창을 열기 전까지는 알 수 없습니다.
7. 'Visible' 속성을 ' -1 – xlSheetVisible'로 변경하면, 시트에 표시가 됩니다.
8. Excel 시트 창을 선택하면 숨겨져 있던 시트 또는 차트가 보입니다.
9. 시트 또는 차트에 사용하지 않는 내용이 들어 있으면, 마우스 오른쪽 버튼을 눌러 [삭제]를 누릅니다.
시트 이름에 Sheet1111111(Sheet1), Sheet11111111111111(Sheet2) 와 같은 이름이 있는 경우
또한, 프로젝트-VBAProject 창에서 Microsoft Excel 개체를 확인 시 일반적으로 Sheet1(Sheet1), Sheet2(Sheet2)와 같은 순서로 시트가 증가 합니다. 그러나 시트 이름이 Sheet1111111(Sheet1), Sheet11111111111111(Sheet2)와 같은 순서로 시트가 증가한다면, 새로운 문서로 파일 전체 내용을 옮기도록 합니다.
[새로운 파일로 문서를 옮기려면 다음과 같은 방법으로 합니다.]
A. 새 문서를 엽니다.
B. 시트를 원본의 수만큼 삽입합니다.
C. 각 시트의 이름을 원본의 시트 이름과 같게 설정합니다.
D. 첫 번째 시트의 전체 셀을 선택 후 복사 합니다.
E. 새 문서의 동일 이름의 시트에 붙여넣기 합니다.
F. 나머지 시트들도 동일한 방법으로 전체 셀 선택 후 복사/붙여넣기 합니다.
G. 새로 생성된 파일을 저장합니다.
H. Excel 2007에서는 [데이터] 메뉴의 [연결 편집], Excel 2003에서는 [편집]의 [연결] 메뉴를 선택합니다.
I. 연결 정보 중 원본 문서와 연결된 이름이 있다면 선택합니다.
J. [원본 변경]을 누르고 새로 저장한 문서 이름으로 선택한 뒤 [닫기]를 누릅니다.
문제 있는 모듈 삭제
1. Alt+F11을 눌러 다시 'Microsoft Visual Basic' 창을 엽니다.
2. 아래의 그림과 같이 laroux1 와 같은 이름의 모듈이 있다면, 마우스 오른쪽 버튼을 눌러 [<Module name> 제거]를 선택합니다.
3. "제거하기 전에 <Module name>을(를) 내보내시겠습니까? " 메시지에서 [아니오]를 누릅니다.
불필요한 코드 삭제
'Microsoft Excel 개체' 하위의 Sheet, Chart를 각각 더블 클릭하여 열었을 때 코드가 들어 있는지 확인합니다. 필요에 의해서 만든 매크로가 아니라면 코드를 모두 지웁니다.
불필요한 매크로 삭제
1. Microsoft Visual Basic 창은 닫고 Alt+F8을 누릅니다.
2. Worksheet에 포함된 매크로가 표시됩니다.
3. 사용하지 않는 매크로는 선택 후 [삭제]를 누릅니다.
4. 삭제가 되지 않는 것은 그대로 둡니다.
문서를 다른 이름으로 저장해 둡니다.
불필요한 개체 삭제
1. 2007 버전인 경우
A. [홈]-[찾기 및 선택]-[선택 창]을 누릅니다.
B. 시트에 삽입된 개체들이 표시됩니다.
C. 아래의 그림과 같이 되어 있다면 '직사각형 24'는 화면에 보이고, 나머지 개체는 보이지 않습니다.
D. 어떠한 개체인지 확인하려면 [모두 표시]를 누릅니다.
단, 개체가 너무 많을 경우 화면에 표시하느라 시간이 오래 걸릴 수 있으므로 주의하십시오.
E. 제거를 원하는 개체를 선택 후 삭제 합니다.
Ctrl 키를 누르면서 마우스를 클릭하면 다중 선택이 가능합니다.
F. 한꺼번에 제거를 원하는 경우
[홈]-[찾기 및 선택]-[이동 옵션]-[개체]를 선택 후 확인을 누릅니다.
[Delete] 키를 눌러 제거 합니다.
G. 나머지 시트 들에 대해서도 같은 방법으로 불필요한 개체들을 제거 합니다.
2. 2007 이전 버전인 경우
A. [편집]-[이동]-[옵션]을 선택 후 [개체]를 확인을 누릅니다.
B. 시트에 포함된 개체가 모두 선택되면, Ctrl 키를 누른 상태에서 필요한 개체는 마우스로 클릭하여 선택 해제한 뒤 선택되어 있는 개체들을 Delete 키로 삭제 합니다.
불필요한 이름 삭제
현재 Workbook에 포함된 이름의 개수를 확인하려면 다음과 같이 합니다.
1. Alt+F11을 눌러 'Microsoft Visual Basic'를 엽니다.
2. [보기]-[직접 실행 창]을 선택합니다.
3. 직접 실행 창에 다음과 같이 입력 후 Enter를 누르면 이름의 개수가 표시가 됩니다.
?activeworkbook.names.count
이름 제거하기
1. 선택하여 제거하기
A. Alt+F11을 누릅니다.
B. Microsoft Visual Basic 창이 열리면, [삽입]-[모듈]을 누릅니다.
C. 아래의 코드를 복사한 뒤 열린 새 창에 붙여 넣습니다.
Sub Delete_Names()
Application.ScreenUpdating = False
On Error Resume Next
Dim n As Name
For Each n In ThisWorkbook.Names
n.Visible = True
n.Delete
Next n
Application.ScreenUpdating = True
End Sub
D. Microsoft Visual Basic 창을 닫고 Alt+F8을 누릅니다.
E. Display_Names를 선택 후 [실행]을 누릅니다.
F. Ctrl+F3의 이름 관리자(또는 이름 정의) 창에서 이름을 확인합니다.
G. 불필요한 이름은 선택하여 제거 합니다.
i. 이름 여러 개를 한꺼번에 제거하는 방법은 2007에서만 제공됩니다.
ii. 첫 번째 이름을 선택 후 연속된 이름 중 마지막 이름을 Shift를 누른 채 선택한 뒤 제거 합니다.
H. Display_Names 매크로는 사용이 끝났으므로 제거합니다. 제거하려면 Alt+F11을 누르고 [보기]-[프로젝트 탐색기] 창에서 Display_Names가 적힌 모듈을 마우스 오른쪽 버튼을 눌러 제거 합니다.
2. 모든 이름 제거하기
이름을 모두 제거해도 되는 경우에만 작업합니다.
이 작업을 하면 Print_Area 이름 정의도 삭제가 되기 때문에 페이지 설정을 다시 해야 합니다.
A. Alt+F11을 누릅니다.
B. Microsoft Visual Basic 창이 열리면, [삽입]-[모듈]을 누릅니다.
C. 아래의 코드를 복사한 뒤 붙여 넣습니다.
Sub Delete_Names()
Application.ScreenUpdating = False
On Error Resume Next
Dim n As Name
For Each n In ThisWorkbook.Names
n.Visible = True
n.Delete
Next n
Application.ScreenUpdating = True
End Sub
D. Microsoft Visual Basic 창을 닫고 Alt+F8을 누릅니다.
E. Delete_Names를 선택 후 [실행]을 누릅니다.
F. Ctrl+F3을 누릅니다.
G. 남아 있는 이름이 있다면 창을 닫고, 다시 Alt+F8을 눌러 Delete_Names 매크로를 실행합니다.
이름 정의가 너무 많이 있을 경우 여러 번 반복합니다.
그래도 제거가 안 되는 항목이 있다면 하나씩 선택 후 [삭제]를 눌러 봅니다. 제거가 안된 이름이 있다면 그대로 둡니다.
H. 이름 제거가 끝난 경우 Alt+F11을 누르고 [보기]-[프로젝트 탐색기] 창에서 Delete_Names가 적힌 모듈을 마우스 오른쪽 버튼을 눌러 제거 합니다.
문서를 다른 이름으로 저장합니다.
이름이 제거가 안 되는 것이 있는 경우
1. 2007 이전 버전인 경우에는 Microsoft Script Editor를 이용하면 제거가 될 수 있습니다. 다음 단계인 스타일 제거 부분에서 이 작업이 수행될 것이므로 다음 단계로 넘어 갑니다.
2. 2007에서는 Microsoft Script Editor 기능이 지원되지 않습니다. 따라서 새 문서로 전체 셀을 선택하여 복사/붙여 넣기 하여 만듭니다. '시트 이동/복사'가 아닌 전체 셀 선택, 복사/붙여 넣기는 불필요한 이름을 옮기지 않습니다.
새로운 파일로 문서를 옮기는 방법은 포스트 윗부분의 [새로운 파일로 문서를 옮기려면 다음과 같은 방법으로 합니다.]를 참고 하시기 바랍니다.
문서를 다른 이름으로 저장합니다.
불필요한 스타일 제거
2007 버전인 경우 [홈]-[스타일] 그룹의 [셀 스타일]에 2003 버전의 경우 [서식]-[스타일] 그룹에 문서에 대한 스타일을 정의할 수 있습니다.
아래의 그림은 각 버전의 기본 스타일입니다.
2007:
2003:
스타일은 셀에서 많이 사용하는 글꼴, 테두리, 무늬 등을 미리 추가를 해 놓고 매번 서식을 적용할 필요 없이 스타일에서 선택만으로도 원하는 서식이 적용될 수 있도록 하는 기능입니다.
2007에서는 [사용자 지정]에 스타일이 추가됩니다.
이름 정의와 마찬가지로 이름이 알 수 없는 글자로 깨져 있는 스타일이 많이 있을 수 있습니다.
매크로 바이러스나 다른 파일에서 사용했던 시트를 이동/복사를 해오는 경우 추가될 수 있습니다.
1. 선택적으로 제거
A. 2007에서는 불필요한 스타일을 마우스 오른쪽 버튼을 눌러 [삭제]를 누릅니다.
B. 2003에서는 불필요한 스타일을 선택 후 오른쪽의 [삭제] 단추를 눌러 삭제 합니다.
2. 전체 이름 제거
사용자 정의 스타일은 지워지기 때문에 셀에서 사용하기 위해 만든 스타일을 지우면 서식이 지워지게 됩니다.
A. 2007에서 스타일 제거
i. Alt+F11을 누릅니다.
ii. Microsoft Visual Basic 창이 열리면, [삽입]-[모듈]을 누릅니다.
iii. 아래의 코드를 복사한 뒤 붙여 넣습니다.
Sub Delete_Styles()
Application.ScreenUpdating = False
Dim s As Style
On Error Resume Next
For Each s In ThisWorkbook.Styles
If Not (s.BuiltIn) Then s.Delete
Next s
Application.ScreenUpdating = true
End Sub
iv. Microsoft Visual Basic 창을 닫고 Alt+F8을 누릅니다.
v. Delete_Styles를 선택 후 [실행]을 누릅니다.
vi. [홈]-[스타일] 영역의 [셀 스타일]에서 스타일이 제거되었는지 확인합니다.
vii. 스타일 제거가 끝난 경우 Alt+F11을 누르고 [보기]-[프로젝트 탐색기] 창에서 Delete_Styles가 적힌 모듈을 마우스 오른쪽 버튼을 눌러 제거 합니다.
B. 2003에서 스타일 제거
i. [도구]-[매크로]-[Microsoft Script Editor]를 선택합니다.
설치가 안되어 있는 경우 설치 시 CD를 요구할 수 있으므로, CD를 준비해 두고 작업하십시오.
ii. Html 태그가 보입니다. 빈 행에 Enter를 여러 번 누릅니다.
iii. Microsoft Script Editor 창을 열어 두고 Excel 시트 창을 선택하면 아래와 같은 메뉴가 나타납니다.
iv. [새로 고침]을 누릅니다.
v. 이 과정에서 제거되지 않았던 이름과 불필요한 스타일이 제거될 수 있습니다.
vi. [서식]-[스타일]을 눌러 남아 있는 스타일을 제거 합니다.
문서를 다른 이름으로 저장합니다.
모두 적용을 하여도 문제가 있거나, 잘 적용이 안 되는 사항이 있다면 먼저 새 파일로 기존 문서 내용을 복사/붙여 넣기 해 놓고 확인하십시오.
새로운 파일로 문서를 옮기는 방법은 포스트 윗부분의 [새로운 파일로 문서를 옮기려면 다음과 같은 방법으로 합니다.]를 참고 하시기 바랍니다.
참고 자료
"셀 서식이 너무 많습니다."라는 오류가 발생하면서 파일이 열리지 않는다
https://support.microsoft.com/kb/601085/ko
"더 이상 사용자 정의 셀 서식을 추가할 수 없습니다." 오류
https://support.microsoft.com/kb/600945/KO/
XL2000: 손상된 엑셀 파일 복구하는 방법
https://support.microsoft.com/kb/214253/KO/
Excel 사양 및 제한
https://office.microsoft.com/ko-kr/help/HP100738491042.aspx
이전 버전의 Excel에서 지원되지 않는 Office Excel 2007 기능
https://office.microsoft.com/ko-kr/excel/HA100778231042.aspx?pid=CH100648071042
"더 이상 사용자 정의 셀 서식을 추가할 수 없습니다." 오류
https://support.microsoft.com/kb/600945/KO/
"Macros may contain viruses" error message when you open files without macros in Excel
https://support.microsoft.com/kb/329950/en-us
XL2000: How to Reduce the Chances of Macro Virus Infection
https://support.microsoft.com/kb/269613/en-us
매크로 보안이 높음으로 설정되어 있으면 XLM 매크로가 포함된 통합 문서를 열 수 없다
https://support.microsoft.com/kb/820739/
HOW TO: Use Global and Local References in Formulas in Excel 2000
https://support.microsoft.com/kb/274504/en-us
HOW TO: Set a Print Area by Using a Defined Name in Excel 2000
https://support.microsoft.com/kb/213648/en-us
How to use cell references and defined names in criteria in Excel
https://support.microsoft.com/kb/61090/en-us
XL2000: Consolidated Labels Use Name of Workbook Instead of Sheet Name
https://support.microsoft.com/kb/214283/en-us
Excel 2007에서 연결을 끊을 때 정의된 이름이 삭제되지 않는다
https://support.microsoft.com/kb/924458/ko
Defined name is not removed when you break a link in Excel
https://support.microsoft.com/kb/288853/en-us
The link source for a defined name is not available in Excel
https://support.microsoft.com/kb/837238/en-us
Error Messages When You Open an Excel 2003 Workbook
https://support.microsoft.com/kb/826714/en-us
"#NAME?" errors may appear in some cells when you open an Office Excel 2007 .xlsx or .xlsm file in an earlier version of Excel
https://support.microsoft.com/kb/945828/en-us
작성자: 한규진
[Excel] 중복하여 기록된 데이터에서 동일한 데이터는 하나만 추출하여 고유한 목록을 생성하는 방법
아래와 같이 상품에 대한 주문을 Excel로 작성하였다고 가정할 때, 이 업체에 상품을 공급한 공급자의 목록을 추출하여 봅시다.
방법 1: 고급 필터 이용하기
1. 필터를 적용할 공급자 열을 선택합니다.
2. [데이터] 탭의 [정렬 및 필터] 그룹에서 [고급] 단추를 선택합니다.
[97-2003 버전]
[데이터] 메뉴의 [필터]-[고급 필터]를 선택합니다.
3. [다른 장소에 복사]를 선택한 뒤, [복사 위치]에는 데이터가 표시될 범위의 첫 번째 셀 위치를 선택합니다.
4. [동일한 레코드는 하나만]을 선택 후 [확인]을 누릅니다.
[97, 2000 버전]
[동일한 레코드는 하나만] 대신 [고유 레코드만] 메뉴를 선택합니다.
5. [복사 위치]에 선택했던 셀을 기준으로 고유한 목록이 표시됩니다.
방법2: 수식과 이름 정의 이용하기
수식과 이름 정의에 대한 자세한 설명을 보려면 아래 '부연설명' 섹션을 참고하시기 바랍니다.
A. A2:A29 셀을 선택합니다.
B. [수식] 탭의 [정의된 이름] 그룹 중 [이름 관리자]를 선택합니다.
[새로 만들기]를 누릅니다.
[97-2003인 경우]
[삽입]-[이름]-[정의]를 누릅니다.
C. '이름' 필드에는 수식에서 사용할 이름 '공급자범위'를 적습니다.
[97-2003인 경우]
[통합 문서에 있는 이름]에 '공급자범위'를 적습니다.
D. '참조 대상'에는 실제 데이터가 들어있는 셀 범위의 주소 =시트이름!$A$2:$A$29가 맞는지 확인 합니다.
E. 확인을 눌러 닫습니다.
F. B2 셀을 선택합니다.
G. 아래의 수식을 입력합니다.
=IF(ISERROR(SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))))
H. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력 줄에서 { =IF(ISERROR(SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1)))) } 로 입력된 것을 확인할 수 있습니다.
I. ROW(A1)의 행 번호를 자동으로 증가시키기 위해서 B29셀까지 자동 채우기 핸들로 채워 내립니다.
부연설명:
수식을 이용할 때에는 다음과 같은 순서대로 구합니다.
(1) 공급자 범위(A2:A29)에서 특정 공급자가 나타난 위치들 중 제일 처음 나온 위치를 찾아 기록합니다. 'A 식품 ㈜'의 경우 제일 처음 발생한 위치가 A2:A29 범위 내에서 1행이므로 1이라는 숫자 값이 나타납니다.
(2) A2는 A2:A29 범위에서 1행입니다. (1)의 결과 값 중 1이라는 위치 정보를 가진 항목은 8개입니다. 각 위치 번호의 발생 빈도 수를 구합니다.
(3) 빈도 수가 0보다 큰 경우 위치 번호를 반환합니다.
(4) 위치 번호를 순서대로 표시하기 위해서 정렬 시킵니다.
(5) 위치에 해당하는 공급자 이름 가져와 표시합니다.
1. 공급자 범위에서 특정 공급자가 나타난 위치 중 첫 번째 위치 찾아 기록하기: MATCH 함수
MATCH 함수
MATCH 함수는 셀 범위에서 지정된 항목을 검색한 다음 범위 내에서 해당 항목의 상대 위치를 반환합니다.
참고 자세한 사항은 Excel에서 F1을 눌러 도움말 창을 열고, MATCH로 검색하여 MATCH 함수에 대한 도움말을 참고 하시기 바랍니다.
공급자 목록에서 특정 공급자가 제일 처음에 나온 위치를 찾으려면 아래와 같이 함수를 작성합니다.
A. B2:B29 범위를 선택합니다.
B. 아래와 같이 수식을 입력합니다.
=MATCH($A$2:$A$29, $A$2:$A$29, 0)
C. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력줄에서 확인하면 { =MATCH($A$2:$A$29, $A$2:$A$29, 0) } 으로 표시됩니다.
배열 수식
배열 수식을 사용하면 다른 수식으로 할 수 없는 계산을 수행할 수 있습니다.
위 수식의 경우 A2를 A2:A29에서 찾아 상대 위치를 반환하고, 그 다음 값인 A3를 A2:A29에서 찾아 상대 위치를 반환합니다. A4에서부터 A29 역시 동일한 방법으로 찾아 상대 위치를 반환하게 됩니다.
참고 배열 수식에 대한 자세한 사항은 Excel에서 F1을 눌러 도움말 창을 열고, 배열로 검색한 뒤, 배열 수식 지침 및 예제를 참고하시기 바랍니다.
2. 각 위치 번호의 발생 빈도 수 구하기 : FREQUENCY 함수
FREQUENCY 함수
FREQUENCY 함수를 사용하여 지정한 범위에 들어가는 값의 개수를 구할 수 있습니다. 배열을 반환하는 수식은 배열 수식으로 입력해야 합니다.
아래의 예제를 참고하십시오.
참고 자세한 사항은 Excel에서 F1을 눌러 도움말 창을 열고, FREQUENCY로 검색하여 도움말을 참고 하시기 바랍니다.
아래의 표와 같이 B열의 결과의 각 항목을 FREQUENCY 함수를 이용하여 빈도수를 구할 수 있습니다.
A. B2:B29 범위를 선택합니다.
B. 수식을 아래와 같이 입력합니다.
=FREQUENCY( MATCH($B$2:$B$29, $B$2:$B$29, 0) , MATCH($B$2:$B$29, $B$2:$B$29, 0) )
C. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력줄을 확인하면 { =FREQUENCY(MATCH($B$2:$B$29, $B$2:$B$29, 0), MATCH($B$2:$B$29, $B$2:$B$29, 0)) } 로 표시됩니다.
3. 빈도 수가 0보다 큰 경우 위치 번호를 반환: IF 함수, ROW 함수
IF 함수
지정한 조건이 TRUE이면 특정 값을 반환하고 FALSE이면 다른 값을 반환합니다.
=IF(A2<=100,"예산 내","예산 초과")
위 함수는 A2의 값이 100보다 작거나 같은 경우 "예산 내"로 표시하고, 100보다 큰 경우 "예산 초과"로 표시합니다.
ROW 함수
참조의 행 번호를 반환합니다.
=ROW(A3)
위 함수는 3을 반환 합니다.
만약, 위치 정보가 0보다 크면, A2:A29의 행 번호를 표시합니다.
아래의 표와 같이 구할 수 있습니다.
A. B2:B29 범위를 선택합니다.
B. 수식을 아래와 같이 입력합니다.
=IF( FREQUENCY(MATCH($A$2:$A$29, $A$2:$A$29, 0), MATCH($A$2:$A$29, $A$2:$A$29, 0)) >0, ROW($A$2:$A$29))
C. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력줄에서 확인하면 { =IF(FREQUENCY(MATCH($A$2:$A$29, $A$2:$A$29, 0), MATCH($A$2:$A$29, $A$2:$A$29, 0))>0, ROW($A$2:$A$29)) } 로 표시됩니다.
4. 위치 정보를 정렬: SMALL 함수
SMALL 함수
데이터 집합에서 k번째로 작은 값을 반환합니다. 이 함수를 사용하면 데이터 집합에서 특정 상대 순위를 갖는 값을 반환할 수 있습니다.
참고 자세한 사항은 Excel에서 F1을 눌러 도움말 창을 열고, SMALL로 검색하여 SMALL 함수에 대한 도움말을 참고 하시기 바랍니다.
아래의 표와 같이 ROW(A1)은 1, ROW(A2)는 2, ROW(A3)은 3입니다.
{=SMALL($B$2:$B$29, ROW(A1) )}의 경우 B2:B29에서 1번째로 작은 숫자를 반환하고, {=SMALL($B$2:$B$29, ROW(A2) )}의 경우 B2:B29에서 2번째로 작은 숫자를 반환합니다.
첫 번째 셀에 수식을 입력 후 아래로 드래그하여 채우면 A1, A2, A3... 으로 셀 주소가 자동으로 증가하므로 순서대로 정렬할 수 있습니다.
A. B2에 아래의 수식을 입력합니다.
=SMALL( IF(FREQUENCY(MATCH($A$2:$A$29, $A$2:$A$29, 0), MATCH($A$2:$A$29, $A$2:$A$29, 0))>0, ROW($A$2:$A$29)) , ROW(A1))
B. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력줄에서 확인하면 { =SMALL(IF(FREQUENCY(MATCH($A$2:$A$29, $A$2:$A$29, 0), MATCH($A$2:$A$29, $A$2:$A$29, 0))>0, ROW($A$2:$A$29)), ROW(A1)) } 로 표시되어 있는 것을 확인할 수 있습니다.
C. ROW(A1)의 행 번호를 자동으로 증가시키기 위해서 B29셀까지 자동 채우기 핸들로 채워 내립니다.
자동 채우기 핸들
5. 위치 정보에 따른 A열 데이터 값 가져오기: INDIRECT 함수, ISERROR 함수
INDIRECT 함수
텍스트 문자열로 지정된 참조를 반환합니다. 참조가 바로 계산되어 해당 내용이 표시됩니다. 수식 자체는 변경하지 않고서 수식 안에 있는 셀에 대한 참조를 변경하려는 경우에 INDIRECT 함수를 사용합니다.
위와 같은 표에서 =INDIRECT($A$1)을 구하면 A1 셀에서 참조하는 B2의 값, 즉 31이 반환됩니다.
=INDIRECT("$B$1")을 구하면 B1 셀의 값, 즉 27이 반환됩니다.
ISERROR 함수
오류 값으로는 #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! 및 #VALUE!가 있습니다.
ISERROR는 참조 셀의 결과가 오류인지 여부를 판단해 주며, IF문과 함께 사용하여 오류 처리를 할 수 있습니다.
=IF(ISERROR(A1), "", A1)
이 수식은 A1의 값이 오류이면 공백("")을, 오류가 아니면 A1의 값을 반환합니다.
만약, 공급자 위치가 없고 오류 값이 있다면 빈 공백("")으로 표시하고, 그렇지 않다면 A열에서 값을 찾아옵니다.
공급자의 실제 이름을 가져오려면 A열에 있기 때문에 =INDIRECT("A"&공급자 위치)로 구할 수 있습니다.
A. B2 셀을 선택합니다.
B. 아래의 수식을 입력합니다.
=IF(ISERROR(SMALL(IF(FREQUENCY(MATCH($A$2:$A$29,$A$2:$A$29,0),MATCH($A$2:$A$29,$A$2:$A$29,0))>0,ROW($A$2:$A$29)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH($A$2:$A$29,$A$2:$A$29,0),MATCH($A$2:$A$29,$A$2:$A$29,0))>0,ROW($A$2:$A$29)),ROW(A1))))
C. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력줄을 확인하면 { =IF(ISERROR(SMALL(IF(FREQUENCY(MATCH($A$2:$A$29,$A$2:$A$29,0),MATCH($A$2:$A$29,$A$2:$A$29,0))>0,ROW($A$2:$A$29)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH($A$2:$A$29,$A$2:$A$29,0),MATCH($A$2:$A$29,$A$2:$A$29,0))>0,ROW($A$2:$A$29)),ROW(A1)))) } 로 입력된 것을 확인할 수 있습니다.
D. ROW(A1)의 행 번호를 자동으로 증가시키기 위해서 B29셀까지 자동 채우기 핸들로 채워 내립니다.
6. 이름 정의하기
수식에서는 공급자 범위가 변경되면, 수식을 다시 수정해야 하는 어려움이 있습니다.
공급자 범위에 Excel의 이름 정의 기능을 이용하면, 좀 더 쉽게 범위를 수정할 수 있습니다.
A1:A4를 '데이터범위'로 이름을 정의해 놓으면, =SUM($A$1:$A$4)의 수식 대신 =SUM(데이터범위)로 입력하여 결과를 구할 수 있습니다. (결과, 16)
이름 정의하기
1. A1:A4를 선택합니다.
[수식] 탭의 [정의된 이름] 그룹 중 [이름 관리자]를 선택합니다.
[97-2003인 경우]
[삽입]-[이름]-[정의]를 누릅니다.
3. [새로 만들기]를 누릅니다.
4. '이름' 필드에는 수식에서 사용할 이름을 원하는 대로 적습니다.
'참조 대상'에는 실제 데이터가 들어있는 셀 범위의 주소를 적습니다.
[97-2003인 경우]
[통합 문서에 있는 이름]에 적습니다.
5. [확인]을 누릅니다.
6. 셀 범위 A1:A4를 선택한 상태에서 Excel의 이름 상자를 확인하면 '데이터범위'라는 이름으로 정의되어 있음을 확인할 수 있습니다.
아래와 같이 5행이 추가 되었을 때
'데이터범위'의 셀 범위를 A1:A5로 수정하면 수식의 결과는 자동으로 25로 변경됩니다.
이름을 수정하려면
A. [수식] 탭의 [정의된 이름] 그룹 중 [이름 관리자]를 선택합니다.
B. '데이터범위'이름을 선택 후 [편집]을 눌러 수정하거나, [참조 대상]을 수정합니다.
[97-2003인 경우]
[삽입]-[이름]-[정의]를 누릅니다.
'데이터범위'를 선택 후 참조 대상을 수정한 뒤 [확인]을 누릅니다.
따라서 아래의 수식에서 $A$2:$A$29 범위를 '공급자범위'로 변경하면 공급자가 더 추가 기록 되었을 때 이름 정의된 범위만 수정하면 간단하게 결과를 변경할 수 있습니다.
=IF(ISERROR(SMALL(IF(FREQUENCY(MATCH( $A$2:$A$29, $A$2:$A$29,0),MATCH( $A$2:$A$29, $A$2:$A$29,0))>0,ROW( $A$2:$A$29)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH( $A$2:$A$29, $A$2:$A$29,0),MATCH( $A$2:$A$29, $A$2:$A$29,0))>0,ROW ($A$2:$A$29)),ROW(A1))))
=IF(ISERROR(SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))))
A. A2:A29 셀을 선택합니다.
B. [수식] 탭의 [정의된 이름] 그룹 중 [이름 관리자]를 선택합니다.
[새로 만들기]를 누릅니다.
[97-2003인 경우]
[삽입]-[이름]-[정의]를 누릅니다.
C. '이름' 필드에는 수식에서 사용할 이름 '공급자범위'를 적습니다.
[97-2003인 경우]
[통합 문서에 있는 이름]에 '공급자범위'를 적습니다.
D. '참조 대상'에는 실제 데이터가 들어있는 셀 범위의 주소 =시트이름!$A$2:$A$29가 맞는지 확인 합니다.
E. 확인을 눌러 닫습니다.
F. B2 셀을 선택합니다.
G. 아래의 수식을 입력합니다.
=IF(ISERROR(SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))))
H. 배열 수식이므로 Ctrl+Shift+Enter를 누릅니다. 수식 입력 줄에서 { =IF(ISERROR(SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(FREQUENCY(MATCH(공급자범위,공급자범위,0),MATCH(공급자범위,공급자범위,0))>0,ROW(공급자범위)),ROW(A1)))) } 로 입력된 것을 확인할 수 있습니다.
I. ROW(A1)의 행 번호를 자동으로 증가시키기 위해서 B29셀까지 자동 채우기 핸들로 채워 내립니다.
작성자: 한규진
Comments
Post a Comment