엑셀을 사용하면 할수록 셀 표시 형식의 필요성을 인식하게 됩니다. 사용자는 기계가 아니라 사람이기 때문이죠. 좀 더 효과적인 표현과 다양한 활용을 위해서는 표시 형식을 대충 넘어갈 수 없다는 것을 알게 됩니다. 이번 강좌도 그 일부분으로 생각할 수 있을 것 같습니다.
기본적으로 하나의 셀에는 하나의 표시 형식만을 사용할 수 있습니다. 즉 숫자인 표시 형식이거나 날짜인 표시 형식만을 사용할 수 있습니다. 하지만 때때로 하나의 셀에 일부는 숫자, 일부는 날짜로 사용해야 하는 경우도 있습니다. 가령 어떤 날짜에 판매된 금액을 하나에 셀에 표시해야 하는 경우가 그렇겠죠. 이렇게 2가지, 3가지 표시 형식을 같이 사용해야 하는 경우는 어떻게 처리할 수 있을까요? 바로 표시 형식으로 반환된 결과를 텍스트로 변환하는 것입니다. 숫자와 날짜만 해당하는 것은 아닙니다. 시간이든, 백분율이든 무엇이든 텍스트로 변환할 수 있습니다. 그렇게 변환된 것들은 표시 형식의 제약을 벗어나기 때문에 혼합 사용이 가능해집니다.
TEXT특정 값(value)을 원하는 표시 형식(format_text)으로 반환합니다. 반환된 결과는 텍스트로 변환됩니다.
TEXT(value, format_text)| value | 텍스트로 변환할 특정 값 |
| farmat_text | value를 나타내고 싶은 표시 형식 |
TEXT 함수는 위와 같이 정리할 수 있습니다. 특정 값을 표시하고 싶은 표시 형식으로 지정하면 끝입니다. 간단하죠.
예제 파일을 보기 전에 TEXT 함수를 간단하게 사용해보고 반환된 값이 텍스트로 변환되는 것부터 확인하겠습니다.
위 그림에서 B3셀은 날짜 표시 형식으로 입력한 것입니다. 그리고 C3셀은 B3셀을 TEXT 함수로 변환한 것입니다.
차이점이 보이시나요? B3셀은 우측 정렬, C3셀은 좌측 정렬 되어있습니다. B3셀은 숫자로, C3셀은 문자로 취급한 것이죠. 날짜의 실제 값은 숫자였습니다. 그러니 B3셀이 우측 정렬되는 것은 당연한 결과입니다. 그리고 C3셀처럼 그 값을 TEXT 함수로 감싸면 좌측 정렬, 즉 텍스트로 변환된다는 것이죠.
날짜의 실제 값이 숫자라는 것을 모르신다면 아래 강좌를 봐주시고 더불어 시간 역시 실제 값은 숫자라는 사항까지 확인해 주시길 바랍니다.
그리고 날짜의 표시 형식까지 반드시 아래 강좌를 보시고 익히시길 바랍니다. 물론 알고 있는 사항이라면 건너뛰셔도 좋습니다.
이제 예제 파일을 보겠습니다.
예제 파일은 한 식당의 아침 식사 예약 현황입니다. 이 식당의 아침은 기본 반찬에 요일별로 국의 종류만 바뀌어 나가는 형태입니다. 그런데 특별히 금요일에는 기본 반찬에 하나의 반찬이 더 나가기 때문에 금액이 6,000원이고 나머지는 5,000원으로 고정되어 있습니다. 최종 알고 싶은 것은 예약자별 금액입니다.
주문일반에는 예약자명과 날짜를 한 번에 확인하기 위해 같이 표시하려 합니다. 두 셀 값을 연결해야 하니 & 기호의 사용은 필수입니다.
& 기호 사용법을 모르신다면 아래 강좌로 확인해 주세요. 알고 있다면 역시 넘어가셔도 좋습니다.
일단 어떤 결과가 나올지 두 셀을 연결해 보겠습니다.
H8셀을 선택하고 수식을 그림처럼 작성하면 간단하게 예약자와 날짜가 하나의 셀에 같이 표시될 것 같지만,
결과는 그림처럼 예약자 다음으로 연결된 날짜는 이상한 숫자로 표시됩니다. 엑셀이 날짜를 처리하는 방식을 이해하고 있다면 이 숫자가 무엇인지 아실 것입니다. 1900년 1월 1일부터 45936일째라는 것이죠. 원하는 결과가 아닙니다. 이걸로는 며칠인지 알 수 없죠. 날짜는 셀 표시 형식이 날짜여야만 확인할 수 있습니다. 그런데 주문일반 항목에는 예약자명이 있어 날짜 표시 형식으로는 표기할 수 없습니다.
바로 이런 경우 날짜의 표시 형식을 그대로 유지한 채 텍스트화시킨다면 모든 것이 해결됩니다. 바로 TEXT 함수를 사용해야 한다는 것이죠.
- 작성할 셀(H8)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 변경합니다.
- 참조된 B8셀까지는 그대로 사용합니다. => =B8
- & 기호롤 연결하고 날짜와의 구분을 위해 " / "를 입력. => =B8&" / "
- 다시 & 기호로 연결하고 날짜(D8)셀의 표시 형식(YYYY-MM-DD) 형태로 텍스트화하기 위해 TEXT 함수를 사용한 후 엔터
=> =B8&" / "&TEXT(D8,"YYYY-MM-DD")
- 최종, 작성한 H8셀로 나머지 셀을 자동 채우기(우측 하단에서 클릭 드래그) 합니다.
결과 그림처럼 예약자와 날짜가 원하는 형태로 작성됩니다.
다음, 금액을 작성하려 합니다. 그런데 일단 금액을 계산하기 위해선 요일을 알아야 합니다. 금요일은 금액이 다르기 때문이죠. 요일 항목에 일단 셀 표시 형식을 변경하여 요일을 반환해 보겠습니다.
- 작성할 셀(E8)을 클릭하여 선택합니다.
- 수식 입력줄에서 일단 날짜를 참조하고 엔터로 수식을 완성합니다.
- 다시 E8셀을 클릭하여 선택한 후 ctrl+1로 셀 서식 대화상자를 띄웁니다.
- 표시 형식 탭에서 사용자 지정을 클릭합니다.
- 형식란에 요일만 표시하는 표시 형식 aaa를 입력합니다.
- 셀 서식 대화상자의 확인 버튼을 클릭합니다. => E8셀이 날짜에서 요일만 반환됩니다.
- 다시 E8셀을 클릭하여 선택한 후 나머지 빈 셀들을 자동 채우기(우측 하단에서 클릭 드래그) 합니다.
요일은 반환되었습니다.
이제 금액 항목에는 해당 요일이 "금"이라면 6,000원으로, 아니라면 5,000원으로 일단 표시해 보겠습니다. 간단하게 IF 함수를 사용하겠습니다. 정상 표시된다면 나중에 인원만 곱해주면 금액 항목까지 완성될 것입니다.
- 작성할 셀(F8)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명 IF을 작성합니다.
- 첫 번째 인수 힌트(logical_test)를 확인하고 조건(요일이 "금")을 작성합니다.
- 쉼표로 구분, 두 번째 인수 힌트(value_if_true)를 확인하고 조건이 참인 경우(요일이 "금") 반환할 값(6000)을 참조합니다.
(주의! 자동 채우기 시 이동할 것을 방지하기 위해 절대 참조) - 쉼표로 구분, 세 번째 인수 힌트(value_if_false)를 확인하고 조건이 거짓인 경우 반환할 값(5000)을 참조한 후 엔터로 수식을 완성.
(주의! 자동 채우기 시 이동할 것을 방지하기 위해 절대 참조) - 최종, 작성한 F8셀로 나머지 셀을 자동 채우기(우측 하단에서 클릭 드래그) 합니다.
결과 금액은 반환되었지만 모두 5000으로 되었습니다. 예상대로라면 F11과 F14는 금요일이기 때문에 6000이 반환되었어야 합니다.
왜 이럴까요? 지금 작성한 F8:F14 범위의 수식에는 문제가 없습니다. 예상하신 분들이 있을 것으로 생각합니다.
바로 IF 함수 logical_test(조건) 인수로 참조한 요일 항목의 셀 때문입니다. E8:E14 범위의 값은 날짜를 참조하고 표시 형식만 요일로 나타나게 처리한 것이죠. 실제 값은 숫자입니다. 숫자와 "금"이 같다는 조건은 참을 반환할 수 없습니다. 당연히 5000밖에 나올 수 없는 형태로 logical_test를 만든 것입니다.
즉 요일 항목이 텍스트로 만들어졌어야 한다는 얘기가 되고 그러려면 TEXT 함수를 사용했어야 한다는 결론이 됩니다. 다시 요일 항목으로 TEXT 함수로 변경해 보겠습니다.
- 작성할 셀(E8)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 TEXT 함수를 사용하여 변경한 후 엔터로 셀을 완성합니다.
- 최종, 작성한 E8셀로 나머지 셀을 자동 채우기(우측 하단에서 클릭 드래그) 합니다.
결과 F11과 F14의 값이 6000으로 정상 반환됩니다.
이제 남은 것을 반영된 금액에 인원만 곱해주면 되겠습니다.
- 작성할 셀(F8)을 클릭하여 선택합니다.
- 수식 입력줄에서 작성된 수식에 인원수를 곱해주고 엔터로 셀을 완성. => =IF(E8="금",$I$5,$I$4)*C8
- 최종, 작성한 E8셀로 나머지 셀을 자동 채우기(우측 하단에서 클릭 드래그) 합니다.
결과 금액 값이 완성되었습니다.
마지막으로 주문일반 항목에 날짜를 요일만 표시하고 예약자의 총금액도 표시해 보겠습니다.
- 작성할 셀(H8)을 클릭하여 선택합니다.
- 수식 입력줄에서 날짜 표시 형식에서 요일 표시 형식으로 변경합니다. => =B9&" / "&TEXT(D9,"aaa")
- & 기호로 연결하고 금액과 구분을 위해 " / "를 입력. => =B9&" / "&TEXT(D9,"aaa")&" / "
- 다시 & 기호로 연결하고 최종 금액을 다시 TEXT 함수 #,##0(천 단위 구분기호 포함) 표시 형식의 텍스트로 추가 작성합니다.
=> =B9&" / "&TEXT(D9,"aaa")&" / "&TEXT(F9,"#,##0") - 다시 & 기호로 연결, 마지막으로 단위 "원"을 추가 작성한 후 엔터로 셀을 완성. => =B9&" / "&TEXT(D9,"aaa")&" / "&TEXT(F9,"#,##0")&"원"
- 최종, 작성한 H8셀로 나머지 셀을 자동 채우기(우측 하단에서 클릭 드래그) 합니다.
결과, 예약자, 요일, 금액이 모두 하나의 셀에 표시되는 주문일반 항목이 완성됩니다.
꽤 길게 설명되었지만, 핵심은 날짜의 표시 형식과 실제 값을 이해하고 있어야 한다는 것이고, TEXT 함수를 사용하면 표시 형식으로 반환되는 결과를 텍스트화할 수 있다는 것입니다. 텍스트화된 것은 실제 값이 아니라 반환된 결과 그대로 값이 되니 연결도 할 수 있고 표현된 그대로 값을 처리할 수 있다는 것이죠. 이해가 되셨길 바랍니다.
추가로 한 가지 더 말씀드리면, 사실 요일을 지금처럼 TEXT 함수로 사용하기보다는 WEEKDAY 함수를 사용하는 편이 좋습니다. WEEKDAY 함수를 사용하면 좀 더 다양한 형태로 요일을 계산하거나 구분할 수 있습니다. 다음 강좌는 WEEKDAY 함수를 알아보겠습니다.
의견글 입니다.
()
