엑셀 : 값 비교하기 (상, 하한, 사잇 값)

 

---

엑셀로 많은 숫자를 다루다 보면 값을 비교해야 하는 상황이 많이 있습니다.

데이터의 양이 적은 경우라도 눈으로 확인하다보면 눈이 쉽게 피로해지고, 휴먼에러(착각이나 실수로 인한 에러)가 다수 발생합니다. 따라서 업무 효율을 위해 이러한 작업은 엑셀의 기능을 사용하면 편합니다.

​

---

조건 1. 고정 된 기준 값으로 전체 데이터를 비교하는 경우

---

​

아래의 경우, 변수 A와 B에 대한 측정값이 제시되어 있고 해당 값에 대한 상한, 하한에 대한 기준값이 있습니다.

​

​

상한으로는 17.50, 하한으로는 17.20의 값을 확인하기 위해서는 조건부 서식을 활용하면 쉽게 찾을 수 있습니다.

​

보다 큼, 보다 작음 조건을 사용하여 조건 자체에 값을 입력해도 되고, D4와 E4를 참조하여 입력해도 상관없습니다. 저는 기준 값이 계속 변경되는 전제하에 그때마다 편하게 보기 위해 셀을 참조하는 방법으로 하겠습니다.

​

​

이렇게 '다음 값보다 큰 셀의 서식 지정'에 값을 클릭하면 달러($)가 행과 열 단위 안에 붙게 되는데, 이는 해당 행, 열의 위치를 고정하겠다는 의미입니다. 따라서 B15, B16, C17 모든 셀의 값은 D4의 기준 값을 따라가겠다는 의미로 해석하시면 됩니다.

​

하한 값도 보다 작음을 사용해서 적용할 서식의 조건을 빨강 채우기가 아닌, 초록 채우기나 다른 사용자 서식으로 설정하면 한눈에 비교된 데이터를 볼 수 있습니다.

​

 

​

참고 사항으로, 기준값은 17.30인데 항상 동일하게 상한 기준은 +0.20, 하한 기준은 -0.10으로 가져간다고 가정하면 상, 하한을 매번 설정하는 방법보다 아래처럼 설정된 기준값에 +0.20/-0.10을 조건부 서식에 포함시켜 적용시키면 더 효율적으로 확인할 수 있습니다.

​

​

---

조건 2. 가변 되는 기준 값으로 전체 데이터를 비교하는 경우

---

매 번 같은 비교값으로 확인할 수 있는 데이터면 1번 조건으로 간단하게 할 수 있지만, 저의 업무의 경우 각 측정 조건마다 기준 값이 다르기 때문에 해당 방법은 살짝 불편합니다. 아래와 같은 상황에서 어떻게 하는지 알려드리겠습니다.

​

​

데이터가 많지는 않지만 기준 값도 딱 떨어지지 않고, 허용 편차까지 고려해서 값을 하나씩 비교하면 벌써부터 머리가 아파오는데요, 이런 경우 조건 1과 비슷한 방식이지만 달러($)를 제외하고 상대 값으로 비교를 해야합니다.

​

E4에 달러($)를 붙인 '$E$4'의 형태가 아닌 그냥 'E4'로 설정하면 상대적으로 C4=E4, B4=F4, C5=E5와 같이 상대적인 셀의 위치로 비교를 할 수 있습니다.

​

참고: 기준값이 A/B로 나뉘지 않은 경우에는 열만 고정하여 '$E4'로 표현하시면 됩니다.

​

근데, 조건부 서식을 보면 '기준값 + 0.30'에 해당하지 않는 셀들도 모두 붉은 음영으로 표시된 걸 볼 수 있습니다. 해당 문제는 셀이 병합되며 첫 번째 셀에만 값이 입력되거나, 병합되면서 다른 셀들은 모두 빈 셀로 처리되면서 생기는 오류입니다. (빈 셀은 0으로 판단되어 0.3보다 크면 모두 음영 표시 됨)

 

기준 값이 적힌 셀의 병합을 풀어보면 아래와 같습니다.

​

 

​

해당 빈 셀을 채우고 병합하는 방법은 간단히 설명드리면 아래와 같습니다.

1. E4:F23까지 셀을 드래그한다.
2. 'Ctrl + Z';이동 탭 -> '옵션(S)' 선택
3. 빈 셀(K) 선택 후 확인
4. '=' -> '윗쪽 방향키' -> 'Ctrl+엔터'

• 1~4번까지 하면 빈셀에 첫 번째 셀을 기준으로 모든 데이터가 채워집니다.

​

5. 병합할 셀의 크기와 동일하게 데이터가 없는 영역의 빈 셀 병합

6. 서식 복사 하여 첫번째 셀 클릭

​

​

이해가 어려우신 분은 아래의 링크에 방법이 자세하게 나와있습니다.

참고 바랍니다.

​

https://blog.naver.com/tellmeim/223303526491

엑셀 : 셀 값 지워지지 않도록 병합하는 방법

 

 

​

이 과정이 끝나면 처음과 동일한 형식을 띠고 있지만 병합된 셀 안에 빈 셀 없이 데이터가 채워지면서 저희가 원하는 비교데이터를 한눈에 볼 수 있습니다.

​

---

조건 3. 사이 값을 비교하고 싶은 경우

---

아래와 같이 일정 범위의 값 확인이 필요한 경우도 있습니다.

​

​

미만과, 초과의 경우 조건 1,2에서 사용한 '보다 큼, 보다 작음'을 사용하면 되는데, 이와 같은 경우에는 '다음 값의 사이에 있음'을 사용해야 합니다.

​

이전과 동일하게 비교할 데이터(C3:C13)를 드래그 한 뒤 '다음 값의 사이에 있음'에 들어간 뒤 아래와 같이 설정한다.

하지만, 이렇게 설정할 경우 22,33를 포함하는 값이 조건에 성립하므로 (초과, 미만 조건 설정 불가) 18.5 이상 23 미만의 조건이 정상에 해당하는 경우 23-0.01(최소 단위)로 설정하면 초과 및 미만을 설정할 수 있다.

​

* 혹시 초과, 미만 조건을 다른 방법으로 설정하는 법을 아시면 알려주시면 감사하겠습니다.

​

---

조건 4. 비교한 데이터를 활용하는 경우

---

​

이렇게 비교한 데이터를 불러와서 사용해야 하는 경우도 있습니다.

조건 1,2,3에서 비교된 데이터를 불러와서 사용해야하는 경우 FILTER 함수를 사용하면 간단하게 사용 가능한데, FILTER까지 설명하려고 하면 글이 너무 길어지기 때문에, 다음 게시글에 FILTER 함수 사용 방법에 대한 내용을 간단하게 다루도록 하겠습니다.

​

이상입니다.

질문이 있거나 조언이 있다면 댓글 달아주시면 감사하겠습니다.