윤슬을 향해

2025.09.27 / 09:47

2025.09.26 / 10:54

REPLACE, SUBSTITUTE 함수 - 문자열 교체하기 이전 강좌에서 특정 데이터의 문자열을 추출하는 방법을 알아보았습니다. 기본이 되는 함수는 LEFT, RIGHT, MID, LEN이었습니다. 하지만 단순 추출이 아니라 문제열 중 일부를 교체하려면 이 함수들만으로는 부족합니다. & 기호, IF, FIND, SEARCH 함수들을 사용해야 합니다. 수식이 복잡해질 수밖에 없죠. 이번엔 좀 더 단순하게 특정 문자열을 다른 문자로 교체하는 함수에 관해 알아보겠습니다. 예제 파일에서 첫 번째로 하고 싶은 것은 "다팔아 사원 목록"에서 주민등록번호 앞자리만 표시하고 나머지 뒷자리는 "*"로 대체하는 것입니다. 이건 이미 알아본 바 있습니다. LEFT 함수로 일부를 추출하고 & 기호를 사용하는 방법이었죠. 이 방법을 모르신다면 아래 강좌를 확인해 주세요. LEFT, RIGHT, MID 함수 - 문자의 개수로 특정 문자를 추출하기 엑셀 LEFT, RIGHT, MID 함수의 사용법을 설명합니다. 각 함수의 인수를 정리하고 문자 개수로 특정 문자를 추출하는 방법을 소개합니다. 시작 문자에서 몇 개의 글자를 추출할지, 끝 문자 몇 개를 추출할지에 따라 LEFT와 RIGHT 함수 사용을 결정하고, 가져올 개수를 num_chars 인수로 사용한다는 것을 설명합니다. MID 함수는 시작할 위치 start_num 인수가 반드시 필요하다는 것을 알아봅니다.https://flylight.kr/ __adsens-adv1__이 경우는 사실 주민등록번호 뒷자리를 교체했다고 보기보단 "*"로 재작성했다고 보는 편이 맞습니다. 물론 이 경우는 이렇게 사용하셔도 전혀 문제 될 것은 없습니다. 하지만 말 그대로 교체하는 방법도 있습니다. REPLACE 함수입니다. REPLACE지정된 텍스트(old_text)에서 일부(start_num에서 num_chars 만큼)를 다른 텍스트(new_text)로 바꿉니다.REPLACE(old_text, start_num, num_chars, new_text)old_text일부 문자를 바꿀 대상 문자열.start_numold_text에서 바꿀 첫 문자의 위치.num_chars바꿀 첫 문자의 위치(start_num)로부터의 개수.new_text바꿔 넣을 새 문자열. 함수 정리는 위와 같습니다. MID 함수와 굉장히 유사합니다. 단지 바꿔 넣을 새 문자열 new_text 인수가 4번째 인수로 존재한다는 것이 다른 뿐 나머지 인수는 모두 MID 함수와 같습니다. 즉 old_text에서 일부 문자를 MID 함수처럼 추출하여 new_text로 교체하는 것입니다. 주민번호(보안)를 작성해 보겠습니다. 작성할 셀(G5)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =REPLACE(첫 번째 인수 힌트(old_text)를 확인하고 참조로 값을 가져옵니다. => =REPLACE(D5쉼표로 구분, 두 번째 인수 힌트(start_num)를 확인하고 가져올 문자의 시작 위치(8)를 입력합니다. => =REPLACE(D5,8쉼표로 구분, 세 번째 인수 힌트(num_chars)를 확인하고 가져올 문자 개수(7)를 입력합니다. => =REPLACE(D5,8,7쉼표로 구분, 네 번째 인수 힌트(new_text)를 확인하고 대체할 문자(*******)를 입력한 후 엔터(닫힌 괄호 자동 생성 후 셀 완성). => =REPLACE(D5,8,7,"*******")주의! 문자는 항상 큰따옴표로 감싸서 사용.다시 완성된 G5셀을 클릭하고 나머지는 자동 채우기(우측 하단에서 클릭 드래그)로 완성합니다. 결과, 그림처럼 완성됩니다. 이전에 알아본 수식보다 훨씬 직관적으로 처리되었습니다. =LEFT(D5,6)&"-*******" → =REPLACE(D5,8,7,"*******") __adsens-adv1__ 다음, 지금처럼 바꿀 문자열을 위치와 개수로 반환하지 않고, 특정 문자열 자체를 변경하는 함수도 있습니다. 예를 들어 위 예제의 이메일에서 "kaver"란 문자열을 "maber"로 변경하고 싶은 경우입니다. SUBSTITUTE 함수입니다. SUBSTITUTE대상 문자열(text)에서 바꿀 문자열(old_text)을 찾아 새 문자열(new_text)로 교체합니다.SUBSTITUTE(text, old_text, new_text, [instance_num])text대상 문자열.old_text바꿀 문자열.new_text새 문자열.instance_num[선택] 대상 문자열에 바꿀 문자열(old_text)가 많을 경우 몇 번째를 바꿀 것인지 지정하는 수입니다.- 지정하지 않을 경우 모든 old_text 바꾸며, 지정할 경우 해당 위치의 old_text만 바꿉니다. 함수 정리는 위와 같습니다. 인수가 많아 보이지만 직관적이라 사용법이 간단합니다. 이메일에서 "kaver"를 "maber"로 바꿔보겠습니다. 작성할 셀(H5)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =SUBSTITUTE(첫 번째 인수 힌트(text)를 확인하고 참조로 값을 가져옵니다. => =SUBSTITUTE(E5쉼표로 구분, 두 번째 인수 힌트(old_text)를 확인하고 바꿀 문자열을 입력합니다. => =SUBSTITUTE(E5,"kaver"쉼표로 구분, 세 번째 인수 힌트(new_text)를 확인하고 교체할 새 문자열 입력한 후 엔터(닫힌 괄호 자동 생성 후 셀 완성). => =SUBSTITUTE(E5,"kaver","maber")- text에 바꿀 문자열 old_text가 여러 개 있지 않으니 네 번째 instance_num 인수를 작성할 필요 없습니다.다시 완성된 H5셀을 클릭하고 나머지는 자동 채우기(우측 하단에서 클릭 드래그)로 완성합니다. 결과, 그림처럼 이메일에 "kaver"가 모두 "maber"로 교체되었습니다. __adsens-adv1__ 추가로 SUBSTITUE 함수에서 instance_num을 사용하는 경우를 알아보겠습니다. 일반 전화 번호 표기법을 예를 들면, 방식은 다양하지만 보통 "OOO-OOO-OOOO"과 "OOO) OOO-OOOO"을 많이 사용합니다. 만약 "OOO-OOO-OOOO" 표기법을 "OOO) OOO-OOOO"로 바꾸고 싶다면 instance_num을 사용하여 간단하게 처리할 수 있습니다. instance_num을 사용하지 않으면 B3셀처럼 모두 교체, instance_num을 1로 사용하면 B4셀처럼 첫 번째만 교체, instance_num을 2로 사용하면 B5셀처럼 두 번째만 교체합니다. 원하는 결과를 반환하려면 instance_num을 1로 사용해야 하는 것이죠. 이런 기능을 활용하면 위처럼 특정 기호를 원하는 문자로 변경하거나 삭제하는 데 효과적으로 사용할 수 있습니다. REPLACE SUBSTITUTE 함수.xlsx(12.2 Kb)save_alt

2025.09.25 / 10:44

문자 개수 세기 LEN 함수, 특정 문자의 순서를 찾는 FIND와 SEARCH 함수 문자의 개수를 이용해 특정 문자를 반환하는 함수 LEFT, RIGHT, MID를 알아보았습니다. 문자 앞에서는 LEFT, 뒤에서는 RIGHT, 중간 부분을 반환해야 할 때는 MID 함수를 사용하면 되었죠. 하지만, 이 함수들은 사실 전제 조건이 있습니다. 동일한 위치에서 같은 개수의 문자만 반환할 수 있다는 것이죠. 앞에서 2글자, 뒤에서 3글자, 세 번째 글자에서 2글자 같은 것만 반환할 수 있습니다. 그런데 현실에서는 동일 위치가 아닌 다른 위치, 같은 개수가 아닌 다른 개수의 문자를 반환해야 하는 경우도 있습니다. 이 경우 LEFT, RIGHT, MID 함수만으로는 원하는 결과를 얻을 수 없습니다. __adsens-adv1__ 예제 파일을 보겠습니다. 예제 파일에서 "이메일 도메인" 항목은 원본 "이메일" 항목 데이터에서 @ 다음 문자들을 반환해야 합니다. 물론 원본 데이터가 변경될 일이 없어 함수를 사용하지 않을 것이라면, 빠른 채우기가 가장 좋은 방법입니다. 텍스트 나누기를 통해서도 할 수 있죠. 두 가지 방법을 모르신다면 아래 강좌를 참고해 주시고요. 특정 문자 추출하기 ❶ 빠른 채우기이미 만들어진 표의 데이터를 기반으로 하여 사용자가 입력한 패턴대로 빈 셀을 빠르게 완성해 주는 빠른 채우기 기능에 관해 설명합니다. 빠른 채우기를 해야 하는 상황을 소개하고 빠른 채우기를 할 수 있는 표의 상태를 만들어 봅니다. 여러 가지 상황으로 빠른 채우기를 진행해 보고 빠른 채우기를 할 수 없는 상태를 설명합니다.https://flylight.kr/ 특정 문자 추출하기 ❷ 텍스트 나누기이미 만들어진 표의 데이터를 특정 기호나 너비로 나누는 방법을 소개합니다. 텍스트 나누기의 3단계를 설명합니다. 특정 구분 기호(구분자)로 텍스트를 나누는 방법과 너비(문자의 개수)로 나누는 방법을 설명하고 불필요한 부분은 버리고 특정 부분만을 가져오는 방법을 설명합니다.https://flylight.kr/원본이 변할 가능성이 있다면 역시 LEFT, RIGHT, MID 함수 중에 적당한 함수를 사용해야 합니다. 이것도 잘 모르신다면 아래 강좌를 참고해 주세요. LEFT, RIGHT, MID 함수 - 문자의 개수로 특정 문자를 추출하기 엑셀 LEFT, RIGHT, MID 함수의 사용법을 설명합니다. 각 함수의 인수를 정리하고 문자 개수로 특정 문자를 추출하는 방법을 소개합니다. 시작 문자에서 몇 개의 글자를 추출할지, 끝 문자 몇 개를 추출할지에 따라 LEFT와 RIGHT 함수 사용을 결정하고, 가져올 개수를 num_chars 인수로 사용한다는 것을 설명합니다. MID 함수는 시작할 위치 start_num 인수가 반드시 필요하다는 것을 알아봅니다.https://flylight.kr/ 지금에 경우는 MID 또는 RIGHT 함수를 사용해야겠네요. 그런데 문제가 있죠. MID 함수를 사용하려고 보니 @ 다음 문자의 위치를 특정할 수 없습니다. 그리고 @ 다음 몇 개의 문자를 가져와야 할지도 특정할 수 없습니다. RIGHT 함수 역시 뒤 문자 몇 개를 가져와야 할지 특정할 수 없습니다. 그렇다고 데이터 한 개씩 사용할 순 없습니다. 너무 많은 작업이 되겠죠. __adsens-adv1__ 문제를 확인했으니, 해결 방법을 알아보겠습니다. "@" 위치와 "@" 다음 문자 개수를 특정할 수 없다는 것 때문에 생긴 문제죠. 하지만 특정할 수 있다면, 그렇다면 모든 것이 해결되겠죠. 이번 강좌는 특정 문자의 위치(숫자)를 알아내는 함수 FIND와 SEARCH 함수를 알아보고 이하 문자 개수를 알아내기 위한 LEN 함수에 대해서도 알아보겠습니다. FIND특정 문자열에서(within_text)에서 찾고자하는 문자(find_text)를 찾아 처음 찾아진 위치(숫자)를 반환FIND(find_text, within_text, [start_num])find_text찾고자하는 문자. 대소문자를 구분하고 와일드 카드(* ? ~)는 사용 안됨 within_text찾고자하는 문자가 포함된 특정 문자열start_num[선택] within_text에서 검색을 시작할 문자 위치(숫자), 생략시 첫 문자(1)부터 검색SEARCH특정 문자열에서(within_text)에서 찾고자하는 문자(find_text)를 찾아 처음 찾아진 위치(숫자)를 반환SEARCH(find_text, within_text, [start_num])find_text찾고자하는 문자. 대소문자를 구분하지 않고 와일드 카드(* ? ~)도 사용 가능 within_text찾고자하는 문자가 포함된 특정 문자열start_num[선택] within_text에서 검색을 시작할 문자 위치(숫자), 생략시 첫 문자(1)부터 검색FIND, SEARCH 함수는 위와 같이 정리됩니다. 두 함수는 기능도, 인수도 모두 같습니다. 찾고자 하는 문자(find_text)의 영문 대소문자를 구분할 것인지, 와일드 카드를 사용할 것인지에 따라 FIND냐 SEARCH냐를 선택하시면 됩니다. FIND 함수는 엄격하고, SEARCH 함수는 유연하다 정도로 기억하면 되겠네요. 와일드 카드는 필터 편에서 알아본바 있습니다. 잘 모르시는 분들은 아래 강좌를 참고해 주세요. 엑셀 필터 ❸ 특정 문자 데이터 필터링 하기 - 와일드 카드 *엑셀에서 필터 사용 시 와일드 카드를 사용하는 방법을 소개합니다. 와일드 카드의 의미와 종류를 알아봅니다. 와일드 카드 중 *의 의미를 알아보고 이름의 첫 글자와 끝 글자로 필터링하는 방법을 설명합니다. 이름 중간 글자는 와일드 카드 *로 사용할 수 없는 이유를 설명합니다.https://flylight.kr/ 엑셀 필터 ❹ 와일드 카드 ?엑셀에서 필터 사용 시 와일드 카드 ?를 설명합니다. 와일드 카드 ?의 의미를 *와 비교하여 알아보고 3글자 이름에서 중간 글자를 필터링하는 방법을 소개합니다. 4글자 5글자에서 이름의 중간 글자 필터링은 다른 방법으로 해야 하는 이유를 설명합니다.https://flylight.kr/ 엑셀 필터 ❺ 와일드 카드 ~과 와일드 카드 종합 정리엑셀에서 필터 사용 시 와일드 카드 ~을 설명합니다. 와일드 카드 ~이 왜 필요한지 알아보고 어떻게 사용하는지 소개합니다. 와일드 카드 ~을 사용하여 필터링할 데이터에 * ? ~을 문자로 처리하여 필터링해 봅니다. 3가지 와일드 카드를 종합 정리합니다.https://flylight.kr/ __adsens-adv1__ 일단 원본 이메일에서 @의 위치를 찾아보겠습니다. 지금은 FIND와 SEARCH 중 아무거나 사용해도 되겠네요. FIND로 진행하겠습니다. 작성할 셀(K5)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =FIND(첫 번째 인수 힌트(find_text)를 확인하고 "@"를 입력합니다. => =FIND("@"수식에서 문자는 반드시 큰따옴표로 감싸야 한다는 것 주의해 주세요.쉼표로 구분, 두 번째 인수 힌트(within_text)를 확인하고 가져올 데이터를 참조하고 엔터로 K5셀을 완성합니다. => =FIND("@",E5)다시 완성된 K5셀을 클릭하고 나머지는 자동 채우기(우측 하단에서 클릭 드래그)로 완성합니다. 결과, @의 위치가 앞에서부터 글자 순서로 반환됩니다. 하지만 여전히 몇 개의 글자를 가져와야 하는지는 알 수 없습니다. 이걸 알기 위해서 하나의 함수를 더 알아야 합니다. LEN 함수입니다. LENtext 문자열의 전체 문자 수를 반환합니다.LEN(text)text문자 수를 확인하려는 대상 문자열. 공백도 문자로 계산됨 함수 정리는 위와 같습니다. LEN은 LENGTH의 약자로 길이를 의미하며 인수를 하나만 갖는 아주 단순한 함수입니다. 전체 문자 수를 알 수 있고 "@"의 위치를 알 수 있으니, "@" 다음 문자 수는, 전체 문자 수에서 "@"까지의 문자 수를 빼주면 되겠죠. (전체 문자 수 - @ 위치) K5셀을 클릭하여 다시 선택합니다. => =FIND("@",E5)수식 입력줄에서 FIND 함수 앞에 LEN 함수명을 추가 작성합니다. => =LEN(FIND("@",E5)LEN 함수의 인수 힌트(text)를 확인하고 가져올 값을 참조한 후 괄호를 닫아 LEN 함수를 완성합니다. => =LEN(E5)FIND("@",E5)작성된 LEN 함수 뒤에 -(빼기)를 입력하고 엔터를 눌러 K5셀을 완성합니다. => =LEN(E5)-FIND("@",E5)나머지 데이터 확인을 위해 다시 완성된 K5셀을 클릭하고 자동 채우기(우측 하단에서 클릭 드래그)를 진행합니다. 결과, @ 다음 문자열의 개수가 반환되었습니다. __adsens-adv1__ 이제 남은 것은 실제 추출할 함수, MID나 RIGHT 중 하나를 사용하면 끝입니다. 간단하게 RIGHT 함수를 사용하겠습니다. K5셀을 클릭하여 다시 선택합니다. => =LEN(E6)-FIND("@",E6)수식 입력줄에서 LEN 함수 앞에 RIGHT 함수명을 추가 작성합니다. => =RIGHT(LEN(E6)-FIND("@",E6)RIGHT 함수의 인수 힌트(text)를 확인하고 가져올 값을 참조한 후 쉼표를 입력합니다. => =RIGHT(E6,LEN(E6)-FIND("@",E6)RIGHT 함수의 두 번째 인수 num_chars는 이미 작성한 수식입니다. RIGHT 함수를 완성하기 위해 수식 마지막에 닫힌 괄호를 입력하고 엔터를 누릅니다. => =RIGHT(E6,LEN(E6)-FIND("@",E6))함수 안에 함수를 중첩 사용할 경우 그냥 엔터는 에러가 발생한다고 했습니다. 반드시 닫힌 괄호를 작성하고 엔터를 눌러 주세요.마지막으로 다시 완성된 K5셀을 클릭하고 자동 채우기(우측 하단에서 클릭 드래그)를 진행합니다. 결과, 원본 이메일에서 도메인만 추출되었습니다. RIGHT 함수 대신 MID 함수를 사용해도 되겠죠. 이건 직접 해 보시길 바랍니다. 힌트, MID 함수의 text와 num_chars 인수는 지금 한 RIGHT 함수의 text와 num_chars 인수와 동일하고 start_num 인수는 @ 다음 위치입니다. 수식만 확인하겠습니다. 주의하실 것을 start_num 인수입니다. "@"부터가 아니라 "@" 다음 문자부터라는 것이 핵심이죠. MID 함수로는 좀 복잡해 보입니다. RIGHT를 사용하는 것이 좋겠네요. LEN FIND SEARCH.xlsx(13.1 Kb)save_alt __adsens-adv1__

2025.09.25 / 09:47

홀짝 ISODD, ISEVEN 함수, 나누기의 몫과 나머지 QUOTIENT, MOD 함수 지난 강좌에서 주민등록번호 뒷자리의 첫 숫자를 반환하여 성별(숫자)까지 반환했습니다. 그런데 그 첫 숫자는 사실 몇 가지 정보가 더 있습니다. 대부분 1과 2, 3과 4는 아실 것 같습니다. 1900년대 남자는 1, 여자는 2로 나타내고 2000년대 남자는 3, 여자는 4로 나타내죠. 그런데 5와 6, 7과 8 그리고 9와 0도 존재합니다. 총 10개의 숫자가 사용될 수 있습니다. 중요한 얘기는 아니지만 신기한 얘기라서 알려드리겠습니다. 숫자 설명숫자 설명 11900년대 출생 남자21900년대 출생 여자32000년대 출생 남자42000년대 출생 여자 5 1900년대 출생 외국인 남자61900년대 출생 외국인 여자 72000년대 출생 외국인 남자82000년대 출생 외국인 여자 91800년대 출생 남자01800년대 출생 여자 즉, 출생년대와 내·외국인 정보까지 있다는 것이죠. 그리고 홀수는 남자, 짝수는 여자라는 것은 모두 같습니다. 예제 파일을 보겠습니다. 성별 숫자를 확인하면 임청하는 2000년대 외국인 여자라는 것을 알 수 있습니다. __adsens-adv1__ 어쨌든 그걸 모두 알고 싶은 것은 아니고 단순히 성별만 알고 싶은 것이니까 성별 숫자가 홀수인지 짝수인지를 확인해 홀수면 "남자", 짝수면 여자로 표시하고 싶습니다. 이 경우 IF 함수를 여러 번 사용하던가 IF와 OR 함수를 같이 사용하면 만들 수 있습니다. IF와 OR 함수는 이미 알아본 내용이니, 궁금하시면 아래 강좌를 확인해 주시기 바랍니다. IF 함수 ❸ IF의 짝꿍, 논리 함수 AND와 OR엑셀의 IF 함수 안에 사용하는 논리 함수 AND와 OR에 관해 알아봅니다. AND와 OR 함수는 단독 사용보다는 IF의 짝꿍처럼 같이 사용된다는 것을 확인하고 두 함수의 공통점과 차이점을 설명합니다.https://flylight.kr/ 하지만 위 두 함수를 사용할 경우의 수가 여러 개가 되기 때문에 역시 수식이 지저분해질 수밖에 없습니다. 수식이 지저분해지면 에러가 날 확률은 높아질 수밖에 없겠죠. 그래서 좀 더 단순하고 직관적인 함수를 사용할 필요가 있는데, 바로 ISODD, ISEVEN 함수입니다. ISODD숫자가 홀수면 TRUE를, 짝수면 FALSE를 반환합니다.ISODD(number)number홀수인지 짝수인지 검정할 숫자 ISEVEN숫자가 짝수면 TRUE를, 홀수면 FALSE를 반환합니다.ISEVEN(number)number홀수인지 짝수인지 검정할 숫자간단하죠. 인수 number가 홀수냐 짝수냐에 따라 참과 거짓을 반환하는 함수들입니다. 참고로 엑셀에서 IS~로 시작하는 함수들이 꽤 있는데 대부분 있냐, 없냐, 맞냐, 틀리냐를 판단하는 함수입니다. 즉, 결과로 TRUE나 FALSE를 반환하는 함수들이죠. ISODD, ISEVEN도 마찬가지입니다. ODD는 홀수가, EVEN은 짝수가 맞냐 틀리냐를 반환하는 함수입니다. 하지만 반환할 것은 맞냐 틀리냐가 아니라 "남자"냐 "여자"냐 입니다. 그러니 TRUE냐 FALSE냐에 따라 반환할 값을 정해야겠죠. IF함수는 사용해야 한다는 것입니다. __adsens-adv1__ 그래서 위 결과를 반환하기 위해선, 주민등록번호에서 MID 함수로 성별(숫자)를 걸러내고 (이건 이전 강좌에서 이미 한 내용입니다.)걸러낸 숫자가 홀수인지 짝수인지를 ISODD 또는 ISEVEN 함수로 판단판단 결과 참(TRUE), 거짓(FALSE)에 따라 IF 함수로 "남자" 또는 "여자"를 반환하는 3단계의 순서를 거쳐야 합니다. ❶은 진행된 상태니까 ❷부터 해 보겠습니다. ❶를 모르시는 분들은 이전 강좌를 참고하시기 바랍니다. LEFT, RIGHT, MID 함수 - 문자의 개수로 특정 문자를 추출하기 엑셀 LEFT, RIGHT, MID 함수의 사용법을 설명합니다. 각 함수의 인수를 정리하고 문자 개수로 특정 문자를 추출하는 방법을 소개합니다. 시작 문자에서 몇 개의 글자를 추출할지, 끝 문자 몇 개를 추출할지에 따라 LEFT와 RIGHT 함수 사용을 결정하고, 가져올 개수를 num_chars 인수로 사용한다는 것을 설명합니다. MID 함수는 시작할 위치 start_num 인수가 반드시 필요하다는 것을 알아봅니다.https://flylight.kr/ 성별의 첫 셀(J5)을 클릭하여 선택합니다. => =MID(D5,8,1)수식 입력줄에서 MID로 반환된 성별(숫자) 값이 홀수인지 확인하기 위해 ISODD를 MID 앞에 작성합니다. => =ISODD(MID(D5,8,1)이때 이미 작성된 수식(MID(D5,8,1))이 ISODD 함수의 number 인수가 됩니다.ISODD 함수를 완성하기 위해 수식 마지막에 닫힌 괄호를 입력하고 엔터를 눌러 셀을 완성합니다. => =ISODD(MID(D5,8,1))수식이 하나일 경우는 그냥 엔터를 눌러도 되지만 지금처럼 함수가 중첩되면 에러가 발생하니 반드시 닫힌 괄호를 입력한 후에 엔터를 눌러야 합니다.나머지도 결과를 확인하기 위해 다시 J5셀을 클릭후 자동 채우기(우측 하단에서 클릭 드래그) 합니다.=> 결과 홀수는 TRUE로, 짝수는 FALSE로 반환됩니다. 이렇게 함수 안에 함수를 사용해야 하는 경우는 항상 닫힌 괄호에 신경 써야 합니다. 그리고 반드시 ISODD 함수를 사용해야 하는 것은 아닙니다. ISEVEN 함수를 사용해도 됩니다. 그렇다면 TRUE와 FALSE는 반대의 결과가 되었겠죠. IF 함수를 추가 사용할 것이기 때문에 일단 구분만 되면 됩니다. __adsens-adv1__ 이제 TRUE의 경우는 "남자", FALSE는 "여자"로 바꾸기 위해 최종 IF 함수를 추가 사용합니다. 성별의 첫 셀(J5)을 다시 클릭하여 선택합니다. => =ISODD(MID(D5,8,1))수식 입력줄에서 작성된 수식을 나누기 위해 ISODD 앞에 IF 함수를 작성합니다. => =IF(ISODD(MID(D5,8,1))이때 이미 작성된 수식(ISODD(MID(D5,8,1)))은 IF 함수의 logical_test 인수가 됩니다.쉼표로 구분, IF 함수 두 번째 인수 힌트(value_if_true)를 확인하고 "남자"로 작성합니다. => =IF(ISODD(MID(D5,8,1)), "남자"쉼표로 구분, IF 함수 세 번째 인수 힌트(value_if_false)를 확인하고 "여자"로 작성한 후 IF 함수 종료를 위해 닫힌 괄호를 입력하고 엔터로 셀을 완성합니다. => =IF(ISODD(MID(D5,8,1)), "남자","여자")나머지도 결과를 확인하기 위해 다시 J5셀 클릭 후 자동 채우기(우측 하단에서 클릭 드래그) 합니다. => 결과 TRUE는 남자로, FALSE는 여자로 반환됩니다. 정리하면, 그림처럼 각 단계의 함수가 다음 단계의 인수로 사용된 것입니다. 그리고 앞으로 이런 식의 함수 사용을 많이 경험하시게 될 겁니다. 함수에 익숙하지 않으신 분들은 지금처럼 먼저 사용될 함수를 작성한 후 다음 단계 함수로 감싸는 구조로 사용하시면 좀 더 직관적으로 함수를 사용할 수 있습니다. 그리고 어느 정도 훈련이 된 다음에는 바로 마지막 단계(=IF~)로 진행할 수 있게 될 것입니다. 그땐 수식 작성 순서가 로직의 반대 순서가 되겠죠. 위의 경우 ISODD 함수를 사용했지만 ISEVEN 함수를 사용해도 됩니다. 그 경우에는 IF 함수에서 "남자", "여자"의 위치가 바뀌어야겠죠. __adsens-adv1__ ISODD와 ISEVEN 함수를 알려드렸지만 좀 더 확장성이 좋고 비교할 만한 함수가 있어서 추가 설명하겠습니다. 바로 MOD 함수입니다. MOD 함수 설명을 위해 QUOTIENT 함수도 같이 설명해야겠군요. 별거 없습니다. 수학 시간에 배운 나누기의 몫과 나머지를 구하는 함수입니다. 예제 파일에서 "몫과 나머지" 시트를 확인해 주세요. 위 예제에서 일반적인 나누기의 경우 몫과 나머지를 분리하지 않고 무한소수로 나타냅니다. 하지만 몫과 나머지를 구해야 할 경우도 있죠. 이 경우 몫을 반환하는 함수가 QUOTIENT 함수이고 나머지를 반환하는 함수가 MOD 함수입니다. MOD는 MODULO의 약자로 각각 나누기의 몫(QUOTIENT), 나누기의 나머지(MODULO)라는 의미라고 하네요. QUOTIENTnumerator를 denominator로 나눈 몫의 정수 부분을 반환합니다.QUOTIENT(numerator, denominator)numerator나눌 수(피제수)denominatornumerator를 나누는 수(제수) MODnumber를 divisor로 나눈 나머지를 반환합니다. MOD(number, divisor)number나눌 수(피제수)divisornumber를 나누는 수(제수) 함수의 정리는 위와 같습니다. 아쉽게도 함수의 인수명이 다르네요. 어쨌든 나눌 수와 나누는 수를 인수로 갖는다는 것은 동일합니다. __adsens-adv1__ 예제 파일의 결과를 반환해 보겠습니다. 나누기는 별거 없죠. B3셀을 C3셀로 나누면 끝입니다. 이건 직접 해 보시고요 몫부터 보겠습니다. 작성할 셀(E3)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =QUOTIENT(첫 번째 인수 힌트(numerator)를 확인하고 나눌 수를 가진 셀(B3)을 참조합니다. => =QUOTIENT(B3쉼표로 구분, 두 번째 인수 힌트(denominator)를 확인하고 나누는 수를 가진 셀(C3)을 참조합니다. => =QUOTIENT(B3,C3함수가 하나이므로 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =QUOTIENT(B3,C3) 다음, 나머지 작성할 셀(F3)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =MOD(첫 번째 인수 힌트(number)를 확인하고 나눌 수를 가진 셀(B3)을 참조합니다. => =MOD(B3쉼표로 구분, 두 번째 인수 힌트(divisor)를 확인하고 나누는 수를 가진 셀(C3)을 참조합니다. => =MOD(B3,C3함수가 하나이므로 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =MOD(B3,C3)결과 몫과 나머지가 정상 반환됩니다. 함수 설명은 여기까지입니다. __adsens-adv1__ 그럼, MOD 함수를 ISODD나 ISEVEN과 어떻게 비교할 수 있을까요? MOD 함수의 divisor 인수를 2로 사용하면 됩니다. 이 경우 0 아니면 1이라는 결과만 반영되겠죠. 즉 두 가지 결과가 나온다는 것입니다. 0이면 "여자", 1이면 "남자"가 되겠죠. 이건 위에서 했던 것처럼 IF 함수로 처리하면 됩니다. 다음, 확장성에 좋다고 한 것에 관해 설명하겠습니다. ISODD나 ISEVEN 함수는 홀수냐 짝수냐만 판단하기 때문에 2가지 경우만 사용할 수밖에 없습니다. 하지만 MOD 함수의 divisor 인수를 2가 아니라 3으로 사용한다면, 3가지 수(0, 1, 2)가 반환됩니다. 4로 사용하면 (0, 1, 2, 3) 4가지 수가 반환될 것이고요. 즉 divisor로 사용한 수만큼 경우의 수가 생긴다는 것입니다. 이렇게 MOD 함수를 사용하면 나머지 값에 따라 다른 결과를 반영하는 수식을 만들 수 있습니다. 예를 들어 어느 여행사에서 4일 여행은 강원도로, 5일 여행은 부산으로, 6일 여행은 제주도로 간다고 가정해 보겠습니다.이 경우 MOD 함수를 사용하고 여행일을 3(divisor)으로 나누면 해당 여행일에 따라 자동으로 여행지(강원, 부산, 제주)를 분리할 수 있습니다. 1일 경우 강원, 2일 경우 부산, 0일 경우 제주로 분리하면 되겠죠. 여행일에 따른 여행지가 또 있다면 divisor 인수를 4, 5, ... 로 조정하여 경우의 수를 더 만들면 됩니다. ISODD나 ISEVEN으로 할 수 없는 것들이죠. 또 다른 예로, 위 예제 성별(숫자)에서 2000년대 여성만을 구분할 수도 있습니다. 2000년대 여성은 4(내국인) 또는 8(외국인)이죠. 4로 나누었을 때 나머지가 0인 경우입니다. 같은 방법으로 1900년대 남성만을 구분할 수도 있겠죠. 1900년대 남성은 1(내국인) 또는 5(외국인)입니다. 4로 나누었을 때 나머지가 1인 경우입니다. 모두 MOD 함수를 사용하고 divisor 인수를 4로 사용한 후 나오는 결과에 따라 구분하면 되는 것이죠. 그래서 성별 ISODD나 ISEVEN 대신 MOD 함수를 사용한다면 수식은, 위와 같이 됩니다. IF 함수의 logical_test 인수를 MOD(MID(D5,8,1),2)=1로 하면, value_if_true 인수를 "남자"로, 즉 ISODD 함수와 동일해지며, logical_test 인수를 MOD(MID(D5,8,1),2)=0으로 하면, value_if_true 인수를 "여자"로, 즉 ISEVEN 함수와 동일해집니다. __adsens-adv1__ 추가로 한 가지 더 말씀드리겠습니다. IF 함수뿐 아니라 TRUE(참)와 FALSE(거짓)를 따지는 논리연산에서 TRUE는 숫자 1로, FALSE는 숫자 0으로 사용할 수 있습니다. 즉, 위와 같이 logical_test 인수가 1과 0으로 반환되는 경우라면 굳이 "=1"을 추가하실 필요는 없습니다. 1은 TRUE고 0은 FALSE기 때문이죠. 그래서 위 수식은, 위와 같이 "=1"을 삭제하여 조금 더 단순하게 표현할 수 있습니다. ISODD ISEVEN QUOTIENT MOD.xlsx(14.2 Kb)save_alt

2025.09.23 / 09:42

2025.09.22 / 13:16

2025.09.18 / 13:14

~IFS 함수 (SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS) - 다중 조건 기능 추가하기 이번엔 ~IFS로 끝나는 함수를 알아보겠습니다. ~IF처럼 기본 함수가 확장한 형태입니다. S가 하나 더 붙었네요. 영어는 복수가 되면 S가 붙죠. ~IF가 여러 개 붙는다는 의미입니다. 즉 하나만이 아니라 여러 개의 조건 기능을 원래 함수 기능에 추가하는 것입니다. 함수기능 함수 기능SUM 범위의 합계 반환SUMIFS 특정 조건들에 모두 만족하는 범위의 합계 반환AVERAGE 범위의 평균 반환AVERAGEIFS 특정 조건들에 모두 만족하는 범위의 평균 반환 COUNT 범위의 개수 반환COUNTIFS 특정 조건들에 모두 만족하는 범위의 개수 반환MAX 범위 중 최대값 반환MAXIFS 특정 조건들에 모두 만족하는 범위 중 최대값 반환MIN 범위 중 최소값 반환MINIFS 특정 조건들에 모두 만족하는 범위 중 최소값 반환 각 함수의 기능은 위와 같이 정리할 수 있습니다. 역시 별거 없습니다. ~IF 함수와 거의 유사하지만 단지 조건을 여러 개 사용할 수 있으며 그 조건들에 모두 만족하는 데이터만 원래 함수의 기능을 사용하는 것입니다. 그리고 꼭 여러 개를 사용해야 하는 것은 아닙니다. 1개만 사용해도 됩니다. 이 경우라면 ~IF 함수와 동일한 결과를 반환하겠죠. 추가로 말씀드리면 MAX와 MIN의 경우 MAXIF, MINIF 함수는 없습니다. 없어도 상관없습니다. MAXIFS, MINIFS가 있으니, 조건 기능을 사용해야 한다면 MAXIFS, MINIFS를 사용하면 됩니다. __adsens-adv1__ 예제 파일을 열고 추가 작업을 진행하겠습니다. 예제 파일은 ~IF 함수 편에서 작성한 파일입니다. 다중 조건의 항목 셀(L4)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 J4셀을 클릭하여 참조 합니다.다음, 참조와 문자(" & ")를 연결하기 위해 &를 입력하고 문자(" & ")를 작성한 후 다시 다른 참조와 연결하기 위해 &를 한번 더 입력합니다.최종, K4셀을 클릭하여 참조하고 엔터로 L4를 완성합니다. 결과 "경기 & 전자제품"이 반환됩니다. 서식 보기 좋지 않네요. 서식 복사도 진행하겠습니다. 복사할 서식을 갖는 범위(K4:K7)를 클릭 드래그로 선택합니다.홈 > 서식 복사를 클릭합니다.붙여 넣기를 할 범위의 첫 셀(L4)을 클릭합니다. 결과, 그림처럼 2가지 조건 항목이 만들어집니다. 뭘 하고 싶은지 눈치채셨죠? 경기 지역에 판매된 전자제품의 금액 합, 금액 평균, 개수를 알고 싶은 겁니다. 조건을 1개가 아니라 2개로 만든 것이죠. 물론 3개, 4개 만들어도 되지만, 간단하게 2개만 해 보셔도 3개, 4개, ... 도 만든는 데 문제가 없을 것으로 생각합니다. __adsens-adv1__ 혹시 지금 한 작업 중 & 기호로 참조와 문자를 연결한 것을 잘 모르겠다면 아래 강좌를 확인해 주세요. 수식에서 문자, 숫자, 참조의 연결 &엑셀 수식에서 값을 연결하는 방법을 설명합니다. 수식에서 숫자와 문자를 혼합하여 사용하면 어떤 결과가 나오는지 확인합니다. 숫자와 문자를 구분하고 & 기호 연결하면 수식에서도 문자를 사용할 수 있다는 것을 확인합니다. 숫자, 문자, 참조를 & 기호로 연결해 보고 문자의 경우 반드시 큰따옴표를 사용해야 한다는 것을 알아봅니다.https://flylight.kr/ 첫 번째와 마지막 &는 연결을 위한 &이고 중간 " & "는 문자로, 두 참조가 붙어 있으면 구분이 좋지 않아 문자로 구분해 놓은 것입니다. 위 그림에서 초록 박스의 수식을 보면 바로 이해하실 수 있을 것으로 생각합니다. =J4&K4 나 =J4&"&"&K4 로 사용하면 경기와 전자 제품이 보기 좋게 구분되지 않기 때문에 =J4&" & "&K4 로 사용하여 보기 좋게 만든 것입니다. __adsens-adv1__ 이제 함수 정리를 하겠습니다. SUMIFS여러 조건을 충족하는 모든 데이터의 합을 계산SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)sum_range합계를 구할 셀 범위.criteria_range1첫 번째 조건 범위criteria1criteria_range1에서 찾고자 하는 첫 번째 조건criteria_range2[선택] 두 번째 조건 범위criteria2[선택] criteria_range2에서 찾고자 하는 두 번째 조건 AVERAGEIFS여러 조건을 충족하는 모든 데이터의 평균을 계산AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)average_range평균를 구할 셀 범위.criteria_range1첫 번째 조건 범위criteria1criteria_range1에서 찾고자 하는 첫 번째 조건criteria_range2[선택] 두 번째 조건 범위criteria2[선택] criteria_range2에서 찾고자 하는 두 번째 조건MAXIFS여러 조건을 충족하는 모든 데이터 중 최대값 반환MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)max_range최대값를 구할 셀 범위.criteria_range1첫 번째 조건 범위criteria1criteria_range1에서 찾고자 하는 첫 번째 조건criteria_range2[선택] 두 번째 조건 범위criteria2[선택] criteria_range2에서 찾고자 하는 두 번째 조건MINIFS여러 조건을 충족하는 모든 데이터 중 최소값 반환MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)min_range최소값를 구할 셀 범위.criteria_range1첫 번째 조건 범위criteria1criteria_range1에서 찾고자 하는 첫 번째 조건criteria_range2[선택] 두 번째 조건 범위criteria2[선택] criteria_range2에서 찾고자 하는 두 번째 조건COUNTIFS여러 조건을 충족하는 데이터의 개수를 반환COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)criteria_range1첫 번째 조건 범위criteria1criteria_range1에서 찾고자 하는 첫 번째 조건criteria_range2[선택] 두 번째 조건 범위criteria2[선택] criteria_range2에서 찾고자 하는 두 번째 조건좀 많네요. 하지만 기능들은 기본 함수들의 확장이니, 모두 아실 것으로 생각합니다. 그리고 COUNTIFS를 제외한 나머지 함수의 첫 번째 인수(sum_range, average_range, max_range, min_range)는 계산하거나 찾을 실제 범위라는 것만 이해하시면 나머지는 쉽습니다. COUNTIFS는 COUNTIF와 동일하게 실제로 찾거나 계산할 범위가 필요 없습니다. 그냥 개수를 세기만 하면 되죠. 나머지 인수들은 모두 동일합니다. criteria_range1, criteria1, criteria_range2, criteria2가 반복되는 형태입니다. __adsens-adv1__ 그럼, 이 인수들이 왜 필요한지 논리적으로 생각해 보겠습니다. 이미 알고 있는 SUMIF 함수의 인수들을 생각하면 쉽게 이해할 수 있습니다. 일단, 실제 계산할 범위는 금액(원) 항목의 모든 셀입니다. 이건 기본 함수 SUM의 범위와 동일합니다. 다음, 조건 기능이 추가되면서 함수는 SUM → SUMIF가 됩니다. 그리고 2가지 인수가 더 필요해졌습니다. 지역이 경기라는 조건으로 "지역"이라는 criteria_range(조건 범위)와 "경기"라는 criteria(조건)가 필요해진 것입니다. 그럼, 여기에 더해서 제품분류가 전자제품이라는 조건이 하나 더 생긴다면, 역시 "제품분류"라는 criteria_range(조건 범위)와 "전자제품"이라는 criteria(조건)이 더 필요해집니다. 즉 조건이 하나씩 추가되면, criteria_range(조건 범위)와 criteria(조건)가 쌍으로 필요하다는 얘기가 됩니다. 그래서 정리한 함수의 구분을 보면 criteria_range2와 criteria2가 대괄호로 묶인 것입니다. 핵심은 조건을 추가 사용할 것이라면 인수가 2개씩 늘어난다는 얘기입니다. 만약 여기에 고객명이 김씨인 조건이 추가된다면, 역시 "고객명"이라는 criteria_range와 "김*"이라는 criteria가 또 쌍으로 필요하게 되겠죠. 이렇게 조건은 127개까지 계속 추가할 수 있습니다. 물론 쌍으로 추가입니다. 그리고 필요 없다면 조건 1개만 사용하면 됩니다. criteria_range1과 criteria1만 있으면 된다는 얘기고 나머지는 선택 사항이란 것입니다. 인수들의 설명을 끝났지만, 순서도 조금 설명해야겠습니다. ~IF와 ~IFS가 기능과 인수가 비슷하지만 위치는 좀 다릅니다. ~IF 함수의 경우 실제 계산 범위가 제일 뒤에 나옵니다. 조건을 처리한 후 실제 계산을 하는 형태죠. 하지만 ~IFS 함수의 경우는 실제 계산 범위가 제일 앞에 나옵니다. 인수들의 명칭을 잘 기억하시면 사실 인수 힌트만 잘 보시면 됩니다. 하지만 기억하시라고 조금 더 설명하겠습니다. 함수가 자신의 기능을 처리하기 위해서 인수는 위치로 판단합니다. 위를 예로 보면 SUMIF의 3번째 인수가 실제 합칠 범위죠. 여기서는 위치가 바뀔 일이 없습니다. 3번째는 무조건 합칠 범위입니다. SUMIFS의 sum_range 인수도 함수의 마지막 위치에 있다고 가정해 보겠습니다. 그럼 몇 번째 인수가 sum_range 일까요? 조건이 하나라면 3번째, 조건이 두 개라면 5번째, 세 개라면 7번째가 될 것입니다. 조건의 개수가 바뀜에따라 sum_range의 위치가 변할 것입니다. 즉 몇 번째 인수가 sum_range인지 특정할 수 없습니다. sum_range을 알 수 없다는 얘기가 됩니다. 이런 이유 때문에 실제 계산 범위는 제일 앞에 있어야 합니다. 제일 앞에 있으면 1번째가 무조건 합칠 범위라는 정할 수 있으니까요. 그리고 같은 이유로 생략돼도 되는 인수들은 마지막 순서에 위치하는 것이죠. 추가로, 실제 계산 범위(sum_range, average_range, max_range, min_range)는 ~IFS 함수에서는 필수 인수입니다. ~IF 함수에서는 range 인수와 같다면 작성하지 않아도 되는 선택 사항이었지만, ~IFS 함수에서는 criteria_range1과 같다 해도 반드시 작성해야 한다는 것도 기억해 주세요. __adsens-adv1__ 함수들의 설명이 끝났으니 "지역이 경기이고 제품분류가 전자제품인 것의 금액 합"을 반환해 보겠습니다. SUMIF 함수를 작성할 셀(L5)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =SUMIFS(첫 번째 인수 힌트(sum_range)를 확인하고 금액(원) 항목의 모든 데이터 범위를 참조합니다. => =SUMIFS(F5:F67(범위가 길기 때문에 키보드 단축키를 활용하는 것이 좋습니다. F5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace)쉼표로 구분, 두 번째 인수 힌트(criteria_range1)를 확인하고 지역 항목의 모든 데이터 범위를 참조 => =SUMIFS(F5:F67,D5:D67(역시 범위가 길기 때문에 키보드 단축키를 활용. D5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace)쉼표로 구분, 세 번째 인수 힌트(criteria1)를 확인하고 범위 중 찾고자 하는 값을 가진 셀(J4)을 참조합니다. => =SUMIFS(F5:F67,D5:D67,J4쉼표로 구분, 네 번째 인수 힌트(criteria_range2)를 확인하고 제품분류 항목의 모든 데이터 범위를 참조 => =SUMIFS(F5:F67,D5:D67,J4,E5:E67(역시 범위가 길기 때문에 키보드 단축키를 활용. E5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace)쉼표로 구분, 다섯 번째 인수 힌트(criteria2)를 확인하고 범위 중 찾고자 하는 값을 가진 셀(K4)을 참조합니다. => =SUMIFS(F5:F67,D5:D67,J4,E5:E67,K4복잡해 보이지만 함수는 하나입니다. 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =SUMIFS(F5:F67,D5:D67,J4,E5:E67,K4) 결과, 지역이 경기이고 제품분류가 전자제품인 것의 금액 합이 반환되었습니다. 나머지 평균과 개수는 직접 해 보시길 바랍니다. 평균은 함수명만 다를 뿐 나머지는 동일하게 하시면 되고, 개수는 실제 계산 범위가 불필요하니 첫 번째 인수가 없다는 것만 유의하시면 됩니다. MAXIFS와 MINIFS는 작성란이 없는데 최대값과 최소값을 반환한다는 함수 기능만이 다르고 작성법은 모두 같으니 아무 셀에서 해 보시기 바랍니다. 결과만 확인하겠습니다. 그리고 J4셀과 K4셀은 이미 데이터 유효성 검사로 목록화했다는 것도 기억하시죠? 간단하게 선택하여 지역과 제품분류를 변경할 수 있습니다. 아무거나 변경해 보세요. J4셀과 K4셀의 값이 바뀌면 L4셀의 조건도 바뀌고 결과도 자동 반영되는 것을 확인할 수 있습니다. 좋죠. ~IFS 함수.xlsx(14.9 Kb)save_alt __adsens-adv1__

2025.09.17 / 14:11

~IF 함수 criteria(조건)에 부등호, 등호 사용하기 이전 강좌에 이어 ~IF 함수 criteria(조건) 인수에 부등호와 등호를 사용하는 방법을 설명하겠습니다. SUMIF, AVERAGEIF, COUNTIF 함수의 사용법을 모르신다면 이전 강좌를 보신 후 돌아와 주세요. 모두 아는 함수라면 넘어가셔도 됩니다. ~IF 함수 (SUMIF, AVERAGEIF, COUNTIF) - 원래 함수의 기능에 조건 추가하기엑셀 함수 중 뒤에 IF가 붙은 함수(SUMIF, AVERAGEIF, COUNTIF)에 대해 설명합니다. ~IF로 끝난 함수는 원래 함수의 기능에 조건 기능이 추가된 것임을 알아봅니다. 각 함수의 인수에 range와 criteria가 왜 필요한지 설명하고 실제로 3가지 함수를 작성해 봅니다. 작성된 함수와 데이터 유효성 검사로 자동화하는 방법을 설명하고 SUBTOTAL 함수와도 비교해 봅니다.https://flylight.kr/ 그리고 추가로 알고 있어야 하는 사항이 좀 있습니다. 일단 수식에서 숫자, 문자, 참조의 연결을 & 기호로 사용한다는 것을 알고 있어야 합니다. 잘 모르신다면 아래 강좌 확인 후 돌아와 주세요. 수식에서 문자, 숫자, 참조의 연결 &엑셀 수식에서 값을 연결하는 방법을 설명합니다. 수식에서 숫자와 문자를 혼합하여 사용하면 어떤 결과가 나오는지 확인합니다. 숫자와 문자를 구분하고 & 기호 연결하면 수식에서도 문자를 사용할 수 있다는 것을 확인합니다. 숫자, 문자, 참조를 & 기호로 연결해 보고 문자의 경우 반드시 큰따옴표를 사용해야 한다는 것을 알아봅니다.https://flylight.kr/ 다음, 고급 필터 사용 시 조건 범위에 부등호 등호를 사용하는 방법도 아시는 편이 좋습니다. 이것도 모르신다면 아래 강좌를 확인해 주시기 바랍니다. 엑셀 필터 ⓬ 고급 필터 ⑤ 조건 범위 만들기 - <, >, = 사용하기엑셀에서 고급 필터 사용 시 and 조건과 or 조건을 혼합하고 부등호(<, >)와 등호(=)를 이용하여 이상, 이하, 초과, 미만을 만드는 방법을 소개합니다. 하나의 항목을 두 개의 열로 나누어 사잇값을 필터링하는 조건 범위를 만들어 보고 부등호를 이용해 특정 값이 아닌 경우를 찾는 조건을 만드는 방법을 설명합니다.https://flylight.kr/ 3가지 내용을 모두 알아보셨을 것으로 믿고 진행해 보겠습니다. 물론 알고 있는 얘기라면 아래 내용으로 바로 들어가셔도 됩니다. 예제 파일을 열어 주세요. 예제 파일에서 알고 싶은 것은 최저 금액(1,000,000) 이상의 개수, 금액 합, 금액 평균입니다. __adsens-adv1__ 먼저 개수 COUNTIF 함수부터 알아보겠습니다. 이전까지는 ~IF 함수의 range 인수 범위에서 criteria와 같은 값만 찾았으면 됐습니다. 하지만 지금은 같은 값을 찾는 것이 아니라 1,000,000 이상을 찾는 것이니 ">="를 사용해야 합니다. criteria 인수가 ">=1000000" 돼야 한다는 얘기겠죠. COUNTIF 함수를 작성할 셀(I7)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =COUNTIF(첫 번째 인수 힌트(range)를 확인하고 금액의 모든 데이터 범위를 참조합니다. => =COUNTIF(F5:F18쉼표로 구분, 두 번째 인수 힌트(criteria)를 확인, 이상의 값을 작성해야 하니 부등호와 등호를 사용하여 입력합니다. => =COUNTIF(F5:F18,>=1000000하나의 함수이므로 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =COUNTIF(F5:F18,>=1000000)별반 다른 게 없어 보이지만 결과는 오류가 납니다. 이상하죠. 수식 중 인수 criteria를 큰따옴표로 감싸보겠습니다. criteria 인수를 큰따옴표로 감싸고 적용하니 문제없이 금액이 1,000,000 이상인 셀의 개수가 반환됩니다. 단지 큰따옴표로 감쌌냐 감싸지 않았냐의 차이로 결과가 나오거나 오류가 난다는 것을 알 수 있습니다. 즉, 이 얘기는 criteria에 부등호, 등호를 사용해야 한다면 문자로 인식시켜야 한다는 얘기가 됩니다. __adsens-adv1__ 고급 필터로 1,000,000 이상의 금액을 필터링한다고 생각해보면 왜 여기서 문자로 인식시켜야 하는지를 비교해 볼 수 있습니다. 고급 필터라면 조건 범위를 만들어야겠죠. 위 그림처럼 조건 범위를 만들어 줘야 합니다. 이때 조건 셀 H12는 >,= 기호를 사용했으니 당연히 문자입니다. 정렬 역시 좌측 정렬되어 있죠. ~IF 함수의 criteria 함수도 동일하게 생각하시면 됩니다. 부등호, 등호 같은 기호가 들어가야 한다면 문자로 처리해 줘야 한다는 것입니다. 기호가 들어가는 경우는 또 있습니다. 와일드 카드(*, ?, ~)도 기호죠. 동일합니다. 큰따옴표로 감싸 줘야 합니다. 그런데 지금처럼 최저 금액을 I4셀에서 참조할 수 있다면 굳이 1,000,000 값을 입력할 필요 없습니다. 참조하면 되죠. 그렇다면 기호(부등호, 등호 등)와 함께 I4셀을 참조하려면 어떻게 할까요? ">=I4"로 하면 될까요? 당연히 안 됩니다. 여기서 I4는 큰따옴표 안에 있으니 참조가 아니라 그냥 문자 I4입니다. 1,000,000 이상을 찾는 것이 아니라 I4 이상을 찾으라는 명령입니다. I4는 그냥 문자이니 I4 이상이란 것은 없습니다. 그럼, ">="I4는 될까요? 역시 되지 않습니다. 문자와 참조를 그냥 붙여서 사용할 수 없습니다. 즉 문자와 참조를 연결해 줘야 한다는 얘기입니다. & 기호를 사용하란 것이죠. 그래서 부등호, 등호를 사용하고 참조를 해야 한다면 결론은 ">="&I4가 돼야 한다는 얘깁니다. I7 셀을 클릭 선택하고 수식의 criteria에서 1000000을 지웁니다. => =COUNTIF(F5:F18,">=")큰따옴표 밖에서 연결 기호 &를 입력합니다. => =COUNTIF(F5:F18,">="&)연결할 셀(I4)을 클릭하여 참조합니다. => =COUNTIF(F5:F18,">="&I4)최종, 엔터를 누르면 에러 없이 동일한 결과를 확인할 수 있습니다. 왠지 >=&I4도 될 것 같지만 되지 않습니다. 반드시 기호는 큰따옴표로 감싸서 ">="&I4 로 만들어야만 에러가 발생하지 않습니다. 고급 필터처럼 문자로 만들어야 하기 때문이죠. __adsens-adv1__ 다음, 금액 합입니다. SUMIF 함수를 작성할 셀(I8)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =SUMIF(첫 번째 인수 힌트(range)를 확인하고 금액의 모든 데이터 범위를 참조합니다. => =SUMIF(F5:F18쉼표로 구분, 두 번째 인수 힌트(criteria)를 확인하고 COUNTIF의 criteria와 동일하게 작성합니다. => =SUMIF(F5:F18,">="&I4다음 sum_range를 작성할 차례입니다. 그런데 sum_range의 범위도 금액 범위입니다. range 범위와 동일하죠. 이 경우는 sum_range는 작성하지 않아도 됩니다. 물론 작성해도 됩니다. 하지만 일부러 더 작업할 이유는 없습니다. 합칠 범위 sum_range와 조건 범위 range가 같다면 생략하셔도 됩니다.최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =SUMIF(F5:F18,">="&I4)결과, 1,000,000 이상의 금액 합이 반환됩니다. 금액 평균은 함수명만 다르니 직접 해 보시기 바랍니다. 같은 이유로 average_range 인수는 작성할 필요 없습니다. 금액 평균까지 결과는 위와 같습니다. __adsens-adv1__ 위처럼 criteria의 값을 참조하면 또 좋은 점이 생깁니다. 최저 금액인 I4셀 값을 변경하면 자동 변경된다는 점입니다. I4셀 값을 1,500,000으로 변경해 보세요. 결과, 바뀐 값 1,500,000 이상의 개수, 금액 합, 금액 평균이 자동 계산되어 나옵니다. 물론 다른 값으로 변경해도 원하는 결과로 자동화됩니다. ~IF 함수에서 등호 부등호 사용하기.xlsx(11.2 Kb)save_alt

2025.09.16 / 13:10

~IF 함수 (SUMIF, AVERAGEIF, COUNTIF) - 원래 함수의 기능에 조건 추가하기 기본 함수를 설명할 때 기본 함수들은 상황에 따라 조금씩 변하여 다른 함수로 확장한다고 알려드린 바 있습니다. 이번에 알려드릴 함수가 대표적인 예입니다. SUMIF, AVERAGEIF, COUNTIF입니다. 일단 함수명만 봐도 낯설진 않습니다. SUM, AVERAGE, COUNT에 또 다른 함수 IF가 붙은 형태입니다. 2가지 함수가 붙은 형태이니, 2가지 기능을 하겠죠. 합, 평균, 개수를 반환하는 기능에 조건 기능이 추가된 것입니다. 함수기능 함수 기능SUM 범위의 합계 반환SUMIF 특정 조건에 만족하는 범위의 합계 반환AVERAGE 범위의 평균 반환AVERAGEIF 특정 조건에 만족하는 범위의 평균 반환 COUNT 범위의 개수 반환COUNTIF 특정 조건에 만족하는 범위의 개수 반환 각 함수의 기능은 위와 같이 정리할 수 있습니다. 별거 없습니다. 함수 원래 기능에 조건이라는 기능이 포함된 형태입니다. __adsens-adv1__ 아직은 조금 어려울 수 있으나 예제를 보고 예를 들어보면 금방 이해할 수 있을 것으로 생각합니다. 예제 파일을 열어 주세요. 예제 파일은 이전 강좌에서 "지역"과 "제품분류"를 "중복 항목 제거", "데이터 유효성 검사"의 순으로 목록화한 파일입니다. 주문 내역의 일부 항목은 화면 편의상 일부 삭제했고, 고윳값을 찾기 위해 복사한 범위 역시 화면 편의상 위치만 변경했습니다. 전체의 합계, 평균, 개수는 기본 함수(SUM, AVERAGE, COUNT)를 사용하면 쉽게 구할 수 있습니다. 여기서 알고 싶은 것은 지역이 경기인 데이터의 합계, 평균, 개수입니다. 그리고 제품 분류가 사무용품인 합계, 평균, 개수도 알고 싶습니다. 즉 금액 합계를 알고 싶긴 한데 지역이 경기라는 조건을 가진 것입니다. 마찬가지 금 평균을 구할 것인데 지역인 경기인 것들만 평균을 구하고 싶은 것이죠. SUM의 기능과 함께 IF의 기능도 필요하고 AVERAGE의 기능과 함께 IF의 기능이 필요하단 얘기입니다. 먼저 전체 합계, 평균, 개수는 직접 만들어 주세요. 자동 합계를 사용하셔도 좋고 함수 기본 작성법을 사용하셔도 좋습니다. 잘 안된다면 아래 강좌를 참고하시길 바랍니다. AVERAGE 함수와 기본 함수 5개의 자동 계산엑셀 AVERAGE 함수의 사용법을 설명합니다. SUM 함수와 AVERAGE 함수의 기능과 인수를 정리하고 함수 기본 작성법으로 AVERAGE 함수를 만들어 봅니다. 엑셀의 기본 함수 5개를 확인하고 자동 합계를 통해 SUM 함수와 AVERAGE 함수를 만들어 봅니다. 자동 계산 시 기본으로 설정하는 범위를 확인하고 잘못된 경우 올바르게 설정하는 방법을 설명합니다.https://flylight.kr/ 셀의 개수 세기 COUNT, COUNTA 함수엑셀에서 셀의 개수를 세는 COUNT와 COUNTA 함수의 사용법을 설명합니다. 두 가지 함수의 인수를 정리하고 각 함수의 기능을 알아봅니다. COUNT로 범위 중 셀 값이 숫자인 셀 수를, COUNTA로 범위 중 비어 있지 않은 셀 수를 세는 것을 확인합니다. 함수 안에 인수 범위를 교체하는 방법을 설명합니다.https://flylight.kr/ 쉽게 하셨을 것으로 생각합니다. 결과는 위와 같습니다. __adsens-adv1__ 이제 이해가 쉬운 SUMIF와 AVERAGEIF 함수부터 정리하겠습니다. SUMIF지정한 조건을 충족하는 범위 값을 합산하여 반환SUMIF(range, criteria, [sum_range])range특정 조건이 포함되는 항목의 범위. => 조건 범위criteriarange 범위에서 찾고자 하는 실제 조건 (와일드 카드, 등호, 부등호 사용 가능)(주의! 와일드 카드, 등호, 부등호 사용 시 반드시 큰따옴표 사용)[sum_range][선택] 실제 합칠 값의 범위 (range와 동일한 범위라면 생략. => 예: 금액이 100,000 이상인 금액 합)(주의! range 범위의 크기와 일치하는 크기로 실제 합칠 범위를 지정해야 함) AVERAGEIF지정한 조건을 충족하는 범위의 평균 값을 반환AVERAGEIF(range, criteria, [average_range])range특정 조건이 포함되는 항목의 범위. => 조건 범위criteriarange 범위에서 찾고자 하는 실제 조건 (와일드 카드, 등호, 부등호 사용 가능)(주의! 와일드 카드, 등호, 부등호 사용 시 반드시 큰따옴표 사용)[average_range][선택] 실제 평균 값을 계산할 범위 (range와 동일한 범위라면 생략. => 예: 금액이 100,000 이상인 금액 평균)(주의! range 범위의 크기와 일치하는 크기로 실제 합칠 범위를 지정해야 함) 함수 정리는 위와 같이 됩니다. 두 함수 모두 동일한 인수를 가지니 사용법은 동일하단 것이고 결과가 합계와 평균을 반환한다는 차이만 있습니다. "지역이 경기인 금액 합"을 예로 인수들을 확인해 보겠습니다. 합계를 알아볼 것이니 SUMIF 함수를 사용해야겠죠. 일단 SUM 함수는 합계 낼 범위가 인수로 필요합니다. SUMIF 역시 필요하겠죠. 지금의 경우 금액의 합계를 낼 것이니 당연히 합계 범위는 금액(원)의 전체 범위가 됩니다. 이 범위가 인수 sum_range입니다. range는 어떤 영역을 의미하니 sum_range는 합칠 범위를 의미합니다. 하지만 금액을 무조건 합치는 것은 아니죠. 지역이 경기라는 조건이 붙었습니다. 여기서 경기라는 조건이 criteria라는 인수가 되고 이 criteria(경기)를 찾을 지역 항목의 범위가 range가 됩니다. 즉, 조건 범위 range(지역 범위)에서 조건 criteria(조건)를 찾고 해당 위치의 실제 계산 항목 범위 sum_range(금액 범위)에 값만 계산(합계)하는 방식입니다. __adsens-adv1__ 인수가 무엇인지 아셨을까요? 실제로 만들어 보겠습니다. SUMIF 함수를 작성할 셀(J5)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =SUMIF(첫 번째 인수 힌트(range)를 확인하고 지역의 모든 데이터 범위를 참조합니다. => =SUMIF(D5:D67(범위가 길기 때문에 키보드 단축키를 활용하는 것이 좋습니다. D5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace)쉼표로 구분, 두 번째 인수 힌트(criteria)를 확인하고 범위 중 찾고자 하는 값을 가진 셀(J4)을 참조합니다. => =SUMIF(D5:D67,J4쉼표로 구분, 세 번째 인수 힌트(sum_range)를 확인하고 실제 합계를 낼 범위인 금액(원) 항목의 모든 데이터 범위를 참조합니다. => =SUMIF(D5:D67,J4,F5:F67(범위가 길기 때문에 키보드 단축키를 활용하는 것이 좋습니다. F5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace)복잡해 보이지만 함수는 하나입니다. 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =SUMIF(D5:D67,J4,F5:F67)range와 sum_range 범위는 길기 때문에 클릭 드래그로 선택하기 보다 단축키를 활용하시는 편이 좋습니다. 단축키 선택이 어려우신 분들을 아래 강좌를 참고하셔서 진행해 주세요 단축키를 사용하여 엑셀 표의 범위 선택하기 - ctrl+shift+↓와 ctrl+backspace엑셀에서 작성한 내용이 많아 드래그 선택이 어려울 경우, 단축키를 사용하여 표의 범위를 선택하는 방법을 소개합니다. 연속된 범위의 셀 선택 단축키 ctrl+방향키와, shift키를 함께 사용하는 방법을 알아보고, 이어서 짝꿍처럼 사용하는 단축키 ctrl+backspace로 작업 셀로 돌아오는 방법을 설명합니다.https://flylight.kr/ 결과, 경기 지역의 금액 합이 반환되었습니다. 인수들만 잘 참조하면 복잡해 보이지만 별거 없습니다. 경기 지역의 금액 평균은 직접 해 보시길 바랍니다. 지금 한 것과 완전히 동일합니다. 함수명만 AVERAGEIF가 되겠죠. __adsens-adv1__ 다음, COUNTIF 함수를 정리하겠습니다. COUNTIF기준을 충족하는 셀의 개수를 계산COUNTIF(range, criteria)range특정 조건이 포함되는 항목의 범위. => 조건 범위criteriarange 범위에서 찾고자 하는 실제 조건 (와일드 카드, 등호, 부등호 사용 가능)(주의! 와일드 카드, 등호, 부등호 사용 시 반드시 큰따옴표 사용) COUNTIF 역시 SUMIF나 AVERAGEIF처럼 range와 criteria가 존재합니다. 동일한 인수입니다. 하지만 sum_range나 average_range가 없습니다. 왜일까요? SUM, AVERAGE 함수처럼 확장 함수로 생각하면 있어야 할 것 같습니다. 그런데 없죠. 함수의 기능을 다시 생각해 보겠습니다. SUMIF나 AVERAGEIF의 경우 계산해야 할 범위가 필요합니다. 합치거나 평균을 계산할 범위가 있어야 하죠. 하지만 COUNT는 단지 셀의 개수를 세기만 하면 됩니다. 위 예제에서 이제 해야 하는 것은 지역인 경기인 것만 세면 됩니다. 굳이 지역이 경기인 것의 금액 항목 개수를 셀 필요가 없죠. 셀을 계산할 범위가 불필요한 것입니다. 필요 없는 것을 억지로 끼워 넣을 이유가 없기 때문입니다. COUNTIF도 작성해 보겠습니다. COUNTIF 함수를 작성할 셀(J7)을 클릭하여 선택합니다.수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =COUNTIF(첫 번째 인수 힌트(range)를 확인하고 지역의 모든 데이터 범위를 참조합니다. => =COUNTIF(D5:D67(범위가 길기 때문에 키보드 단축키를 활용하는 것이 좋습니다. D5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace)쉼표로 구분, 두 번째 인수 힌트(criteria)를 확인하고 범위 중 찾고자 하는 값을 가진 셀(J4)을 참조합니다. => =COUNTIF(D5:D67,J4역시 하나의 함수이므로 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =COUNTIF(D5:D67,J4) 결과, 지역이 경기 셀의 개수가 반환됩니다. 기능만 이해하면 오히려 사용법은 SUMIF와 AVERAGEIF 보다 간단합니다. __adsens-adv1__ 3가지 함수의 기본 사용법을 설명했으니 "제품분류가 사무용품인 것의 금액 합, 평균, 개수"는 직접 해 보시길 바랍니다. 최종, 결과는 위와 같습니다. 만약 값이 다르다면 인수들의 참조를 잘못하셨을 가능성이 높습니다. 잘못되었다면 아래 수식과 비교해 보세요. K5셀 : =SUMIF(E5:E67,K4,F5:F67) K6셀 : =AVERAGEIF(E5:E67,K4,F5:F67) K7셀 : =COUNTIF(E5:E67,K4) 여기서 한 가지 더, 위 문서는 criteria에 해당하는 J4셀과 K4셀을 데이터 유효성 검사로 목록화시켜 놓은 상태입니다. 즉 조건을 경기에서 서울로, 서울에서 전라로 등 자유롭게 바꿀 수 있습니다. 제품 분류 역시 사무용품에서 가구로, 가구에서 전자제품으로 바꿀 수 있죠. 조건을 마음대로 바꿀 수 있게 자동화시켜 놓은 것이죠. 그렇다는 것은 조건을 바꾸면 반환 값도 자동으로 바뀐다는 얘기입니다. J4셀과 K4셀의 값은 변경해 보세요. 결과, 조건(J4, K4셀) 값의 변화에 따라 자동으로 금액 합, 평균, 개수가 변경되는 것을 알 수 있습니다. 데이터 유효성 검사와 함수를 함께사용한 장점이죠. 추가로 한 가지 더 말씀드리겠습니다. 사실 위와 동일한 결과를 얻을 수 있는 함수를 이미 알아본 바 있습니다. 바로 SUBTOTAL 함수였죠. 필터를 사용하고 SUBTOTAL 함수를 사용하면 위와 동일한 값을 반환할 수 있습니다. 하지만 필터를 반드시 사용해야 한다는 단점이 있죠. 위처럼 원본을 유지하고 결과를 얻고 싶다면 위 3가지 함수를 사용하는 것이 좋습니다. SUBTOTAL 함수가 기억나지 않는다면 아래 강좌를 확인해 주세요. SUBTOTAL 함수 ❶ 필터링 제외된 데이터를 함수(SUM, AVERAGE, ...)에서 제외하기엑셀 필터 사용 시 반드시 알아야 하는 SUBTOTAL 함수 사용법을 설명합니다. 일반 함수로는 불필요한 데이터를 제외할 수 없다는 것을 확인하고 SUBTOTAL 함수로 필터링 제외된 데이터를 함수에서도 제외하는 방법을 설명합니다. SUBTOTAL 함수의 기능과 인수를 정리합니다. function_num 인수가 무엇인지 확인하고 사용할 수 있는 함수가 어떻게 구성되는지 알아봅니다.https://flylight.kr/ ~IF 함수에 대한 기본적인 얘기는 여기서 마칩니다. 사실 좀 더 하고 싶은 얘기가 있으나 길어지는 관계로 다음 강좌로 이어가겠습니다. SUMIF AVERAGEIF COUNTIF.xlsx(14.4 Kb)save_alt __adsens-adv1__

2025.09.15 / 09:15