안녕하세요. 행아아빠입니다.
이번 엑셀 내용에서는 Substitute함수를 활용해서 수식을 작성해 보는 내용으로 Substitute함수는 텍스트 문자열 내에서 특정 텍스트를 원하는 텍스트로 변경할 수 있는 함수로 사용할 수 있기 때문에 많은 양의 데이터에서 특정 문자를 원하는 텍스트로 변경하고자 하는 경우 유용하게 활용할 수 있는 함수입니다.
■ 행아아빠의 Youtube 채널.
● 주요 컨텐츠 주제 - 내일은로또왕 / 내일은엑셀왕
● Youtube 채널 주소 - https://www.youtube.com/@economic-freedom
■ 행아아빠 후원.
● Youtube 채널 맴버십 후원 - https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA/join
● Youtube 댓글 후원 - 각 영상 댓글에서 "Super Thanks" 구입후 댓글 작성.
● 내일은엑셀왕 대표 Youtube 영상 - https://youtu.be/oYVZHgv_sKM
1. SUBSTITUTE 함수 수식 구문과 예제.
1-1. SUBSTITUTE함수 수식 구문.
Substitute함수의 수식 구문은 다음과 같이 작성할 수 있습니다.
=Substitute(텍스트, 이전텍스트, 새텍스트, 발생)
● 텍스트 - 변경하고자 하는 데이터가 입력된 셀 또는 텍스트 문자열.
● old_text (이전텍스트) - 현재 데이터에 입력되어 있는 텍스트.
● new_text (새텍스트) - 현재 데이터에 입력되어 있는 텍스트를 바꾸고자 하는 텍스트.
● instance_num - 선택적 인수로 입력하지 않으면 이전 텍스트를 새텍스트로 모두 대체해서 변경.
1-2. SUBSTITUTE함수 수식 예제.
Substitute함수로 수식을 작성했을때 어떤 결과값을 나타내는지 예를 들면 다음과 같이 확인해 볼 수 있습니다.
① 예제1. 만약 A열에 제품 이름이 입력되어 있을때 제품 이름이 "파란색"이라는 단어를 포함하고 있을때 "파란색"이라는 단어를 "녹색"으로 변경하고자 하는 경우 B1셀에 Substitute함수로 수식을 작성하면 다음과 같이 작성할 수 있습니다.
=Substitute(A1,"파란색","녹색")
작성된 수식에서 "파란색"은 old_text (이전텍스트) 인수이고, "녹색"은 new_text(새텍스트)인수로 파란색 텍스트를 녹색 텍스트로 변경한 결과값을 수식이 입력된 셀에 표기할 수 있습니다.
1-3. SUBSTITUTE 함수 수식 실전 예제.
Substitute함수로 수식을 작성해서 결과값을 나타낼 수 있는 실전 예제를 확인해 보도록 하겠습니다.
A2셀부터 "00시간 00분"형식으로 입력된 형식을 분으로 환산하기 위해서 Substitute함수를 활용한 수식으로 결과값을 표기 하는 수식을 작성해 보도록 하겠습니다.
만약 데이터의 양이 많지 않다면 "6시간 10분"과 같은 형식으로 입력된 데이터를 "6:10"분의 표시형식으로 직접 작성한 후에 분으로 환산하는 수식을 작성해서 결과값을 얻을 수도 있습니다.
하지만 데이터의 양이 많을때 수식을 작성해서 빠르게 결과값을 표기 한다면 Substitute함수를 활용해서 수식을 작성한 결과값을 표기 하는것이 효율적이라고 할 수 있습니다.
B열의 결과값을 얻기 위한 수식을 작성하면 다음과 같이 수식을 작성 할 수 있습니다.
| =SUBSTITUTE(SUBSTITUTE(A2,"분",""),"시간 ",":")*24*60 |
6시간 10분이라는 데이터를 분으로 환산하기 위해서는 6시간 10분을 분으로 환산하면 (6시간*60분)+(10분)=370분이 되기 때문에 입력된 데이터 형식에는 텍스트가 포함된 6시간 10분으로 입력이 되어 있어서 "시간"이라는 텍스트와 "분"이라는 텍스트를 삭제하고 "시간 " 텍스트는 시간으로 시간 형식으로 데이터를 변경하기 위해서 ":"으로 변경한 뒤에 (6:10 )*24*60으로 6시간 10분을 총 소요시간을 분으로 환산해서 계산 할 수 있습니다.
따라서 변경해야할 old_text (이전텍스트)는 "시간(한칸 띄어쓰기)"를 ":" 기호로 변경하고 "분" 텍스트는 삭제 하기 위해서 ""으로 변경해야 하기 때문에 =Substitute(A2,"시간",":")의 수식을 작성하면 6시간 10분은 6:10분으로 수정할 수 있고 "분"이라는 텍스트를 지우기 위해서 =Substitute(A2,"분","")의 수식을 작성해서 "6:10"으로 결과값을 시간 형식으로 표기 할 수 있습니다.
그런데 B열에 텍스트를 포함한 시간형식의 데이터를 분으로 환산해서 결과값을 표기 하는것을 원하기 때문에 "시간" 텍스트와 "분" 텍스트를 한번에 새로운 텍스트로 변경하기 위한 수식으로 작성하기 위해서 =SUBSTITUTE(SUBSTITUTE(A2,"분",""),"시간 ",":")와 같은 수식을 작성하는것 입니다.
여기서 분리된 각각의 수식 =Substitute(A2,"시간",":")와 =Substitute(A2,"분","")의 수식을 결합하는 형태를 기억해둘 필요가 있는데 두 수식을 결합한 형태로 작성하게 되면 =SUBSTITUTE(SUBSTITUTE(A2,"분",""),A2,"시간 ",":") 이렇게 수식을 작성해야 할것 같지만, 실제로 해당 수식을 작성하게 되면 오류값을 반환하게 됩니다.
따라서 텍스트가 입력되어 있는 셀주소 A2셀을 이미 참조 하고 있기 때문에 =SUBSTITUTE(SUBSTITUTE(A2,"분",""),"시간 ",":")의 수식을 작성해서 "6시간 10분"을 "6:10"으로 표기한 뒤에 *24(시간)*60(분)을 연산해주게 되면 6시간10분을 총 분으로 계산한 결과값을 표기 할 수 있게 됩니다.
엑셀(Excel)에서 Substitute함수는 데이터 관리 작업을 간소화 하고 잘못 입력된 데이터를 원하는 데이터로 변경하는데 유용하게 사용할 수 있는 함수로 기억해 주시고, 여러분들의 실제 업무에서 활용해 보시길 바라겠습니다. 감사합니다.
Blog : https://hang-a-fafa.tistory.com
Youtube : https://www.youtube.com/@economic-freedom
Naver 까페 : https://cafe.naver.com/hatime
2. 샘플 예제 서식 다운로드.
'엑셀' 카테고리의 다른 글
| 엑셀 자주 사용하는 함수 - 조건을 판별하는 if함수. (0) | 2023.02.17 |
|---|---|
| 엑셀 Sumifs함수의 범위를 동적 범위로 수식을 작성 하는 방법. (0) | 2023.02.16 |
| 엑셀 기초 배우기 8편 - 틀고정, 셀숨기기, 시트편집(추가,삭제,이름변경,숨기기) (0) | 2023.02.13 |
| 엑셀 기초 배우기 7편 - 연속된 데이터 채우기 및 데이터 정렬, 필터, 찾기 및 바꾸기 기능 사용 방법 (0) | 2023.02.13 |
| 엑셀 기초 배우기 6편 - 엑셀 차트 만들기. (0) | 2023.02.10 |
댓글