이 튜토리얼에서는 엑셀 수식을 활용하여 특정 값을 초과하지 않도록 설정하는 방법을 소개합니다. 대량의 데이터를 다룰 때는 데이터가 일정 범위를 벗어나지 않도록 상한선을 지정해야 하는 경우가 매우 흔합니다. 기업이나 교육기관에서는 우수 등급이나 이익률을 측정하기 위해 이런 기준선을 설정하곤 합니다. 이 글에서는 데이터 유효성 검사(Data Validation), MAX, MIN, RANDBETWEEN, IF 함수 등 다양한 수식을 활용하여 특정 값을 초과하지 않도록 설정하는 방법을 단계별로 살펴보겠습니다.
특정 값을 초과하지 않도록 설정하는 6가지 방법
설명을 쉽게 이해할 수 있도록 샘플 데이터셋을 예시로 사용하겠습니다. 예를 들어, B열에는 셔츠 브랜드명, C열에는 사이즈, D열에는 가격이 입력된 데이터셋이 있다고 가정합니다. 아래 단계를 그대로 따라 하면 스스로도 엑셀에서 특정 값을 초과하지 않도록 설정하는 방법을 익힐 수 있습니다.
1. 데이터 유효성 검사로 값 범위 제한하기
첫 번째 방법은 데이터 유효성 검사 기능을 사용하는 것입니다. 특정 셀이나 범위에 입력되는 값을 제한하고 싶을 때 이 기능을 활용합니다. 데이터 수집 과정에서 특히 유용하며, 입력 오류를 사전에 방지해 줍니다. 예를 들어 고객의 나이를 기록할 때 숫자만 입력받도록 설정할 수 있고, 상한값과 하한값을 지정하면 그 범위를 벗어나는 값은 입력 자체가 거부됩니다. 또한 오류 메시지 대신 안내 문구를 띄워 좀 더 유연하게 처리할 수도 있으며, 사용자 지정 수식을 활용하면 복잡한 조건도 구현 가능합니다.
단계:
- 먼저 사이즈(SIZE) 범위를 선택한 후 데이터 탭 > 데이터 유효성 검사 옵션으로 이동합니다.
- 데이터 유효성 검사 창이 화면에 나타나면 설정 탭에서 제한 대상(허용)은 정수, 데이터(between)는 다음 사이, 최소값은 100, 최대값은 200으로 지정합니다.
- 그다음 오류 경고 탭에서 스타일은 경고, 제목은 Out_of_Range, 오류 메시지는 "100~200 사이의 값을 입력하세요"로 입력합니다.
- 이후 범위를 벗어나는 값을 입력하면 아래와 같은 경고창이 화면에 표시됩니다.
- 마지막으로 값을 직접 입력했을 때 범위 내에 있으면 결과가 정상적으로 반영되며, 모든 셀에 동일하게 적용하면 원하는 결과를 얻을 수 있습니다.
2. 데이터 유효성 검사의 사용자 지정 수식 활용하기
이번에는 데이터 유효성 검사의 사용자 지정 수식을 사용하여 허용 범위를 설정해 보겠습니다. 데이터 유효성 검사 기능은 사용자 지정 수식을 통해 일정 값 범위를 유지할 수 있게 해줍니다. AND 함수는 두 개의 조건을 결합하여 하나의 결과로 반환하므로, 최솟값 조건과 최댓값 조건을 함께 지정하는 데 적합합니다.
단계:
- 앞서와 마찬가지로 사이즈(SIZE) 범위를 선택한 후 데이터 탭 > 데이터 유효성 검사 옵션으로 이동합니다.
- 설정 탭에서 제한 대상(허용)을 사용자 지정으로 변경한 뒤 아래 수식을 입력합니다.
=AND(D5>=100,D5<=200)
- 확인을 클릭한 후 값을 직접 입력하여 범위 내에 있는지 확인하고, 모든 셀에 같은 과정을 반복하면 됩니다.
3. MAX 함수 활용하기
세 번째 방법은 MAX 함수를 사용하는 것입니다. MAX 함수는 엑셀 통계(STATISTICAL) 함수 범주에 속하며, 주어진 인수 목록 중 가장 큰 값을 반환합니다. 두 값 중 더 큰 쪽을 결과로 돌려주기 때문에, 계산 결과가 특정 값보다 작아지지 않도록 하는 '하한선' 설정에 활용할 수 있습니다.
단계:
- 먼저 아래 이미지처럼 데이터셋을 배치합니다.
- 셀 E5에 다음 수식을 입력합니다.
=MAX(0.8*D5,90)
- Enter 키를 누르면 해당 셀의 결과가 표시됩니다. 이후 채우기 핸들(Fill Handle)을 이용해 수식을 나머지 셀에 복사합니다.
- 마지막으로 원하는 결과를 확인할 수 있습니다.
4. MIN 함수 적용하기
이번에는 MIN 함수를 사용해 보겠습니다. MIN 함수는 숫자 데이터가 들어 있는 셀 범위에서 가장 작은 값을 추출하는 데 일반적으로 사용됩니다. MAX 함수와 반대로 작동하기 때문에, 계산 결과가 특정 값보다 커지지 않도록 하는 '상한선' 설정에 적합합니다.
단계:
- 먼저 아래 이미지처럼 데이터셋을 배치합니다.
- 셀 E5에 다음 수식을 입력합니다.
=MIN(0.8*D5,140)
- Enter 키를 누르면 해당 셀의 결과가 표시됩니다.
- 채우기 핸들을 이용해 수식을 모든 셀에 적용합니다.
- 마지막으로 원하는 결과를 얻을 수 있습니다.
5. IF 함수 사용하기
IF 함수를 사용해서도 허용 범위를 설정할 수 있습니다. 복잡하고 강력한 데이터 분석을 수행할 때는 여러 조건을 한 번에 판단해야 하는 경우가 많습니다. 엑셀에서 IF 함수는 조건을 처리하는 강력한 도구 역할을 합니다.
단계:
- 먼저 아래 이미지처럼 데이터셋을 배치합니다.
- 셀 F5에 다음 수식을 입력합니다.
=IF(D5<100,0,IF(D5>200,0,D5))
- Enter 키를 누르면 해당 셀의 결과가 표시됩니다. 이후 채우기 핸들을 이용해 수식을 전체 셀에 적용합니다.
- 마지막으로 원하는 결과를 확인할 수 있습니다.
6. RANDBETWEEN 함수 사용하기
마지막으로 RANDBETWEEN 함수를 활용해 보겠습니다. 최솟값과 최댓값 사이의 난수를 생성할 때 RANDBETWEEN 함수를 사용합니다. 이 함수는 상한값과 하한값 사이에서 무작위 데이터를 만들어 줍니다. 예를 들어 셔츠 사이즈를 22~30 사이의 임의의 값으로 생성하고 싶다고 가정해 보겠습니다.
단계:
- 먼저 아래 이미지처럼 데이터셋을 배치합니다.
- 셀 C8에 다음 수식을 입력합니다.
=RANDBETWEEN($C$5,$C$4)
- Enter 키를 누르면 해당 셀에 무작위 값이 표시됩니다.
- 채우기 핸들을 이용해 수식을 모든 셀에 적용합니다.
- 마지막으로 원하는 결과를 얻을 수 있습니다.
기억해야 할 사항
- 처음 두 가지 방법에서는 최대값과 최소값이 가장 중요한 역할을 합니다. 값을 입력할 때 이 점을 항상 염두에 두세요. 범위를 변경하면 결과도 달라집니다.
- 수식을 사용할 때는 올바른 구문으로 입력하는 것이 중요합니다. 그렇지 않으면 아무런 결과도 반환되지 않습니다.
- 더 나은 이해를 위해 엑셀 파일을 다운로드한 후 실제로 수식을 적용해 보면서 학습하는 것을 권장합니다.
결론
지금까지 소개한 방법들을 차례대로 따라 해 보세요. 이 방법들이 엑셀에서 특정 값을 초과하지 않도록 설정하는 데 도움이 되기를 바랍니다. 다른 방식으로 작업을 수행할 수 있다면 의견을 공유해 주셔도 좋습니다. 이와 같은 유용한 글을 더 보고 싶다면 ExcelDemy 웹사이트를 참고하세요. 궁금한 점이나 제안 사항, 질문이 있다면 아래 댓글 섹션에 자유롭게 남겨주세요. 성심껏 답변드리겠습니다.