Excel 데이터 분석-데이터 유효성 검사

데이터 유효성 검사는 워크 시트에 입력 된 데이터에 대한 데이터 유효성 검사를 설정할 수있는 Excel의 매우 유용하고 사용하기 쉬운 도구입니다.

워크 시트의 모든 셀에 대해 다음을 수행 할 수 있습니다.

  • 입력해야하는 내용에 대한 입력 메시지를 표시합니다.
  • 입력되는 값을 제한하십시오.
  • 선택할 값 목록을 제공하십시오.
  • 오류 메시지를 표시하고 잘못된 데이터 입력을 거부합니다.

식별 된 위험 정보를 입력하고 추적하는 데 사용할 수있는 다음 위험 추적기를 고려하십시오.

이 트래커에서 다음 열에 입력 된 데이터는 사전 설정된 데이터 제약 조건으로 검증되며 입력 된 데이터는 검증 기준을 충족하는 경우에만 허용됩니다. 그렇지 않으면 오류 메시지가 표시됩니다.

  • Probability
  • Impact
  • 위험 범주
  • 위험 원
  • Status

위험 노출 열에는 계산 된 값이 있으며 데이터를 입력 할 수 없습니다. 칼럼조차S. No. 행을 삭제하더라도 조정되는 계산 된 값을 갖도록 설정됩니다.

이제 이러한 워크 시트를 설정하는 방법을 배웁니다.

워크 시트의 구조 준비

워크 시트의 구조를 준비하려면-

  • 빈 워크 시트로 시작합니다.
  • 헤더를 행 2에 넣으십시오.
  • 열 머리글을 행 3에 넣습니다.
  • 열 헤더의 경우 확률, 영향 및 위험 노출-
    • 셀을 마우스 오른쪽 버튼으로 클릭합니다.
    • 드롭 다운에서 셀 서식을 클릭합니다.
    • 셀 서식 대화 상자에서 맞춤 탭을 클릭합니다.
    • 방향 아래에 90을 입력합니다.
  • 각 열 머리글에 대해 행 3, 4 및 5의 셀을 병합하고 가운데에 놓습니다.
  • 행 2 – 5에있는 셀의 테두리 서식을 지정합니다.
  • 행 및 열 너비를 조정합니다.

워크 시트는 다음과 같이 보일 것입니다.

위험 범주에 대한 유효한 값 설정

M5 – M13 셀에 다음 값을 입력합니다 (M5는 머리글이고 M6-M13은 값).

범주 값
최종 사용자
고객
조치
시간표
시간표
환경
생성물
계획
  • 위험 범주 (H6) 열 아래의 첫 번째 셀을 클릭합니다.
  • 리본에서 데이터 탭을 클릭합니다.
  • 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다.
  • 드롭 다운 목록에서 데이터 유효성 검사…를 선택합니다.

데이터 유효성 검사 대화 상자가 나타납니다.

  • 설정 탭을 클릭하십시오.
  • 유효성 검사 기준에서 Allow: 드롭 다운 목록, 옵션 선택 List.
  • 나타나는 소스 : 상자에서 M6 : M13 범위를 선택합니다.
  • 공백 무시 및 표시되는 셀 내 드롭 다운 상자를 선택합니다.

위험 범주에 대한 입력 메시지 설정

  • 데이터 유효성 검사 대화 상자에서 입력 메시지 탭을 클릭합니다.
  • 체크 박스 Show input message 셀이 선택되었을 때.
  • 제목 : 아래의 상자에 위험 범주를 입력합니다.
  • 입력 메시지 아래의 상자에서 : 목록에서 위험 범주를 선택합니다.

위험 범주에 대한 오류 경고 설정

오류 경고를 설정하려면-

  • 데이터 유효성 검사 대화 상자에서 오류 경고 탭을 클릭합니다.
  • 잘못된 데이터를 입력 한 후 오류 경고 표시 상자를 선택합니다.
  • 스타일 : 드롭 다운에서 중지를 선택합니다.
  • 제목 : 아래의 상자에 잘못된 항목을 입력합니다.
  • 오류 메시지 아래의 상자에 드롭 다운 목록에서 값 선택을 입력합니다.
  • 확인을 클릭하십시오.

위험 범주에 대한 데이터 검증 확인

위험 범주 아래에서 선택한 첫 번째 셀에 대해

  • 데이터 검증 기준이 설정 됨
  • 입력 메시지가 설정 됨
  • 오류 경고가 설정 됨

이제 설정을 확인할 수 있습니다.

데이터 유효성 검사 기준을 설정 한 셀을 클릭합니다. 입력 메시지가 나타납니다. 드롭 다운 버튼이 셀 오른쪽에 나타납니다.

입력 메시지가 올바르게 표시됩니다.

  • 셀 오른쪽에있는 드롭 다운 버튼을 클릭합니다. 선택할 수있는 값이있는 드롭 다운 목록이 나타납니다.

  • 드롭 다운 목록의 값과 드롭 다운 목록을 만드는 데 사용되는 값을 비교 확인하십시오.

두 값 세트가 모두 일치합니다. 값의 수가 더 많은 경우 드롭 다운 목록의 오른쪽에 스크롤 다운 막대가 표시됩니다.

드롭 다운 목록에서 값을 선택합니다. 셀에 나타납니다.

유효한 값 선택이 제대로 작동하고 있음을 알 수 있습니다.

마지막으로 잘못된 항목을 입력하고 오류 경고를 확인하십시오.

셀에 People을 입력하고 Enter 키를 누릅니다. 셀에 대해 설정 한 오류 메시지가 표시됩니다.

  • 오류 메시지를 확인하십시오.
  • 재시도 또는 취소 옵션이 있습니다. 두 옵션을 모두 확인하십시오.

셀에 대한 데이터 유효성 검사를 성공적으로 설정했습니다.

Note − 메시지의 철자와 문법을 확인하는 것은 매우 중요합니다.

위험 범주 열에 대한 유효한 기준 설정

이제 위험 범주 열의 모든 셀에 데이터 유효성 검사 기준을 적용 할 준비가되었습니다.

이 시점에서 두 가지를 기억해야합니다.

  • 사용할 수있는 최대 셀 수에 대한 기준을 설정해야합니다. 이 예에서는 워크 시트가 사용되는 위치에 따라 10 – 100까지 다양합니다.

  • 원하지 않는 셀 범위 또는 전체 열에 대한 기준을 설정하면 안됩니다. 이것은 불필요하게 파일 크기를 증가시킵니다. 이를 초과 서식이라고합니다. 외부 소스에서 워크 시트를 가져 오는 경우 초과 서식을 제거해야합니다 .이 자습서의 Inquire 장에서 배울 것 입니다.

아래 단계를 따르십시오-

  • 위험 범주에서 10 개의 셀에 대한 유효성 검사 기준을 설정합니다.
  • 첫 번째 셀의 오른쪽 하단 모서리를 클릭하면 쉽게 할 수 있습니다.
  • 표시되는 + 기호를 누르고 아래로 당깁니다.

선택한 모든 셀에 대해 데이터 유효성 검사가 설정됩니다.

선택한 마지막 열을 클릭하고 확인하십시오.

위험 범주 열에 대한 데이터 유효성 검사가 완료되었습니다.

위험 소스에 대한 검증 값 설정

이 경우 내부 값과 외부 값만 있습니다.

  • 위험 소스 (I6) 열 아래의 첫 번째 셀을 클릭하십시오.
  • 리본에서 데이터 탭을 클릭합니다.
  • 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다.
  • 드롭 다운 목록에서 데이터 유효성 검사…를 선택합니다.

데이터 유효성 검사 대화 상자가 나타납니다.

  • 설정 탭을 클릭하십시오.
  • 유효성 검사 기준의 허용 : 드롭 다운 목록에서 목록 옵션을 선택합니다.
  • 나타나는 소스 : 상자에 내부, 외부를 입력합니다.
  • 공백 무시 및 표시되는 셀 내 드롭 다운 상자를 선택합니다.

위험 소스에 대한 입력 메시지를 설정합니다.

위험 소스에 대한 오류 경고를 설정합니다.

위험 소스에서 선택한 첫 번째 셀의 경우-

  • 데이터 검증 기준이 설정 됨
  • 입력 메시지가 설정 됨
  • 오류 경고가 설정 됨

이제 설정을 확인할 수 있습니다.

데이터 유효성 검사 기준을 설정 한 셀을 클릭합니다. 입력 메시지가 나타납니다. 드롭 다운 버튼이 셀 오른쪽에 나타납니다.

입력 메시지가 올바르게 표시됩니다.

  • 셀 오른쪽에있는 드롭 다운 화살표 버튼을 클릭합니다. 선택할 수있는 값이있는 드롭 다운 목록이 나타납니다.

  • 입력 한 값 (내부 및 외부)이 동일한 지 확인합니다.

두 값 세트가 모두 일치합니다. 드롭 다운 목록에서 값을 선택합니다. 셀에 나타납니다.

유효한 값 선택이 제대로 작동하고 있음을 알 수 있습니다. 마지막으로 잘못된 항목을 입력하고 오류 경고를 확인하십시오.

셀에 Financial을 입력하고 Enter 키를 누릅니다. 셀에 대해 설정 한 오류 메시지가 표시됩니다.

  • 오류 메시지를 확인하십시오. 셀에 대한 데이터 유효성 검사를 성공적으로 설정했습니다.

  • 위험 소스 열에 대한 유효한 기준 설정

  • 위험 소스 열의 셀 I6-I15에 데이터 유효성 검사 기준을 적용합니다 (예 : 위험 범주 열과 동일한 범위).

선택한 모든 셀에 대해 데이터 유효성 검사가 설정됩니다. 위험 소스 열에 대한 데이터 유효성 검사가 완료되었습니다.

상태에 대한 유효성 검사 값 설정

  • 위험 소스에 대한 유효성 검사 값을 설정하는 데 사용한 것과 동일한 단계를 반복합니다.

  • 목록 값을 Open, Closed로 설정합니다.

  • 상태 열의 K6-K15 셀에 데이터 유효성 검사 기준을 적용합니다 (예 : 위험 범주 열과 동일한 범위).

선택한 모든 셀에 대해 데이터 유효성 검사가 설정됩니다. 열 상태에 대한 데이터 유효성 검사가 완료되었습니다.

확률에 대한 검증 값 설정

위험 확률 점수 값은 1-5 범위에 있으며 1은 낮음, 5는 높음입니다. 값은 1에서 5 사이의 정수가 될 수 있습니다.

  • 위험 소스 (I6) 열 아래의 첫 번째 셀을 클릭하십시오.
  • 리본에서 데이터 탭을 클릭합니다.
  • 데이터 도구 그룹에서 데이터 유효성 검사를 클릭합니다.
  • 드롭 다운 목록에서 데이터 유효성 검사…를 선택합니다.

데이터 유효성 검사 대화 상자가 나타납니다.

  • 설정 탭을 클릭하십시오.
  • 유효성 검사 조건의 허용 : 드롭 다운 목록에서 정수를 선택합니다.
  • 데이터 아래에서 선택 :
  • 최소값 아래의 상자에 1을 입력합니다.
  • 최대 값 아래의 상자에 5를 입력합니다.

확률에 대한 입력 메시지 설정

확률에 대한 오류 경고를 설정하고 확인을 클릭합니다.

확률에서 선택한 첫 번째 셀에 대해

  • 데이터 유효성 검사 기준이 설정되었습니다.
  • 입력 메시지가 설정됩니다.
  • 오류 경고가 설정되었습니다.

이제 설정을 확인할 수 있습니다.

데이터 유효성 검사 기준을 설정 한 셀을 클릭합니다. 입력 메시지가 나타납니다. 이 경우 입력 값이 목록에서가 아닌 범위에 있도록 설정되어 있기 때문에 드롭 다운 버튼이 없습니다.

입력 메시지가 올바르게 표시됩니다.

셀에 1에서 5 사이의 정수를 입력합니다. 셀에 나타납니다.

유효한 값을 선택하는 중입니다. 마지막으로 잘못된 항목을 입력하고 오류 경고를 확인하십시오.

셀에 6을 입력하고 Enter를 누르십시오. 셀에 대해 설정 한 오류 메시지가 표시됩니다.

셀에 대한 데이터 유효성 검사를 성공적으로 설정했습니다.

  • 확률 열에 유효한 기준을 설정합니다.

  • 데이터 검증 기준을 확률 열의 E6-E15 셀에 적용합니다 (예 : 위험 범주 열과 동일한 범위).

선택한 모든 셀에 대해 데이터 유효성 검사가 설정됩니다. 열 확률에 대한 데이터 유효성 검사가 완료되었습니다.

영향에 대한 유효성 검사 값 설정

Impact에 대한 유효성 검사 값을 설정하려면 확률에 대한 유효성 검사 값을 설정하는 데 사용한 것과 동일한 단계를 반복합니다.

영향 열의 F6-F15 셀에 데이터 유효성 검사 기준을 적용합니다 (즉, 위험 범주 열과 동일한 범위).

선택한 모든 셀에 대해 데이터 유효성 검사가 설정됩니다. Impact 열에 대한 데이터 유효성 검사가 완료되었습니다.

계산 된 값으로 열 위험 노출 설정

위험 노출은 위험 확률과 위험 영향의 곱으로 계산됩니다.

위험 노출 = 확률 * 영향

G6 셀에 = E6 * F6을 입력하고 Enter 키를 누릅니다.

E6 및 F6이 비어 있으므로 G6 셀에 0이 표시됩니다.

G6 – G15 셀의 수식을 복사합니다. G6-G15 셀에 0이 표시됩니다.

위험 노출 열은 계산 된 값을 의미하므로 해당 열에 데이터 입력을 허용해서는 안됩니다.

  • 셀 G6-G15 선택

  • 마우스 오른쪽 버튼을 클릭하고 나타나는 드롭 다운 목록에서 셀 서식을 선택합니다. 셀 서식 대화 상자가 나타납니다.

  • 보호 탭을 클릭하십시오.

  • 옵션 확인 Locked.

이는 해당 셀에서 데이터 입력이 허용되지 않도록하기위한 것입니다. 그러나 이것은 워크 시트가 보호 된 경우에만 적용되며 워크 시트가 준비된 후 마지막 단계로 수행됩니다.

  • 확인을 클릭하십시오.
  • 셀 G6-G15를 음영 처리하여 계산 된 값임을 나타냅니다.

일련 번호 값 형식 지정

S. No. 열은 사용자에게 맡기면됩니다. 그러나 S. No. 값의 서식을 지정하면 워크 시트가 더보기 좋게 보입니다. 또한 워크 시트에 서식이 지정된 행 수를 표시합니다.

B6 셀에 = row ()-5를 입력하고 Enter 키를 누릅니다.

B6 셀에 1이 나타납니다. B6-B15 셀의 수식을 복사합니다. 1-10 값이 나타납니다.

B6-B15 셀을 음영 처리합니다.

마무리

프로젝트가 거의 완료되었습니다.

  • 데이터 범주 값이 포함 된 M 열을 숨 깁니다.
  • B6-K16 셀의 테두리 서식을 지정합니다.
  • 워크 시트 탭을 마우스 오른쪽 버튼으로 클릭합니다.
  • 메뉴에서 시트 보호를 선택합니다.

시트 보호 대화 상자가 나타납니다.

  • 워크 시트 및 잠긴 셀의 내용 보호 옵션을 선택합니다.
  • 시트 보호를 해제하려면 암호 아래에 암호를 입력하십시오-
    • 비밀번호는 대소 문자를 구분합니다.
    • 비밀번호를 잊어 버리면 보호 된 시트를 복구 할 수 없습니다.
    • 워크 시트 이름과 암호 목록을 어딘가에 보관하는 것이 좋습니다.
  • 이 워크 시트의 모든 사용자에게 허용 : 잠금 해제 된 셀 선택 상자를 선택합니다.

위험 노출 열의 잠긴 셀을 데이터 입력으로부터 보호하고 잠금 해제 된 나머지 셀을 편집 가능한 상태로 유지했습니다. 확인을 클릭하십시오.

그만큼 Confirm Password 대화 상자가 나타납니다.

  • 비밀번호를 다시 입력하십시오.
  • 확인을 클릭하십시오.

선택한 셀에 대해 데이터 유효성 검사가 설정된 워크 시트를 사용할 준비가되었습니다.