어드바이저

하이픈·슬래시·공백 뒤쪽 텍스트만 가져오기 상품코드, 이메일, 경로, 부서명처럼 한 셀에 구분기호가 들어 있다면 TEXTAFTER 로 특정 문자 뒤의 값만 추출할 수 있습니다. 구버전 Excel용 RIGHT·LEN·FIND 방식도 함께 정리합니다. 뒷부분 추출 수식 바로 만들기 1. 하이픈 뒤쪽만 가져오기 A2에 SEOUL-001 이 있다면 하이픈 뒤의 001 만 추출할 수 있습니다. =TEXTAFTER(A2,"-") 2. 슬래시 뒤부분 추출 영업팀/서울지점 처럼 슬래시로 구분되어 있다면 다음과 같습니다. =TEXTAFTER(A2,"/") 3. 공백 뒤의 나머지 텍스트 가져오기 A2가 김민수 영업팀 이라면 첫 공백 뒤의 영업팀 을 가져올 수 있습니다. =TEXTAFTER(A2," ") 기준 문자만 바꾸면 같은 구조로 사용할 수 있습니다. 하이픈은 "-" , 슬래시는 "/" , 공백은 " " , 쉼표는 "," 입니다. 4. 마지막 하이픈 뒤쪽만 가져오기 A2가 AA-BB-CC 이고 마지막 하이픈 뒤의 CC 만 필요하다면 인스턴스 번호에 -1을 사용할 수 있습니다. =TEXTAFTER(A2,"-",-1) 5. 두 번째 하이픈 뒤쪽 가져오기 두 번째 하이픈을 기준으로 그 뒤를 가져오려면 다음과 같습니다. =TEXTAFTER(A2,"-",2) 6. 기준 문자가 없을 때 원본 표시 기준 문자가 없는 셀도 섞여 있다면 IFERROR를 함께 사용해 오류 대신 원본을 표시할 수 있습니다. =IFERROR(TEXTAFTER(A2,"-"),A2) 7. 구버전 Excel에서는 RIGHT + LEN + FIND TEXTAFTER를 지원하지 않는 Excel이라면 RIGHT, LEN, FIND를 조합할 수 있습니다. =RIGHT(A2,LE...

하이픈·슬래시·공백 앞부분만 바로 추출하기 상품코드, 사번, 주소, 파일명처럼 한 셀 안에 구분기호가 들어 있다면 TEXTBEFORE 로 특정 문자 앞부분만 가져올 수 있습니다. 구버전 Excel에서는 LEFT와 FIND 조합을 사용할 수 있습니다. 앞부분 추출 수식 바로 만들기 1. 하이픈 앞부분만 가져오기 A2에 SEOUL-001 이 있다면 하이픈 앞의 SEOUL 만 추출할 수 있습니다. =TEXTBEFORE(A2,"-") 2. 슬래시 앞부분 추출 예를 들어 영업팀/서울지점 처럼 슬래시로 구분된 값이라면 다음과 같습니다. =TEXTBEFORE(A2,"/") 3. 공백 앞의 첫 단어만 가져오기 A2가 김민수 영업팀 이라면 첫 공백 앞부분만 가져올 수 있습니다. =TEXTBEFORE(A2," ") TEXTBEFORE의 두 번째 인수는 기준 문자입니다. 하이픈은 "-" , 슬래시는 "/" , 공백은 " " 처럼 입력합니다. 4. 쉼표 앞부분 추출 서울,대한민국 처럼 쉼표로 구분되어 있다면 다음 수식을 사용할 수 있습니다. =TEXTBEFORE(A2,",") 5. 두 번째 하이픈 앞까지 가져오기 A2가 AA-BB-CC 일 때 두 번째 하이픈 앞의 AA-BB 를 가져오려면 인스턴스 번호를 지정할 수 있습니다. =TEXTBEFORE(A2,"-",2) 6. 기준 문자가 없을 때 원본 그대로 표시 TEXTBEFORE는 기준 문자를 찾지 못하면 오류가 날 수 있습니다. 원본을 그대로 보여주고 싶다면 IFERROR를 함께 사용합니다. =IFERROR(TEXTBEFORE(A2,"-"),A2) 7. 구버전 Excel에서는 LEFT + FIND TEXTBEFORE를 지원하지 않는 버전이라면 LEFT와 FIND를 조합해 같은 결과를 만들 수 있습니다. ...

엑셀 셀 두 개를 한 셀로 합치는 가장 쉬운 방법 이름과 성, 주소와 상세주소, 코드와 번호처럼 두 셀의 텍스트를 합칠 때는 & 연산자 나 TEXTJOIN을 사용할 수 있습니다. 중간에 공백·하이픈·쉼표를 넣는 방법까지 같이 정리합니다. 텍스트 합치기 수식 바로 만들기 1. 두 셀을 그대로 합치기 A2와 B2의 내용을 붙여서 하나의 텍스트로 만들려면 가장 간단하게 &를 사용합니다. =A2&B2 예를 들어 A2가 “김”, B2가 “민수”라면 결과는 “김민수”가 됩니다. 2. 두 셀 사이에 공백 넣기 성·이름이나 단어 사이에 한 칸을 넣고 싶다면 중간에 " " 을 추가합니다. =A2&" "&B2 3. 하이픈이나 쉼표를 넣어서 합치기 전화번호, 상품코드, 주소처럼 구분기호를 넣어야 한다면 원하는 문자를 따옴표 안에 넣으면 됩니다. =A2&"-"&B2 =A2&", "&B2 구분기호도 텍스트이므로 따옴표가 필요합니다. 공백은 " " , 하이픈은 "-" , 쉼표와 공백은 ", " 처럼 입력합니다. 4. TEXTJOIN으로 두 셀 합치기 TEXTJOIN을 사용하면 구분기호와 빈 셀 무시 여부를 한 번에 지정할 수 있습니다. =TEXTJOIN(" ",TRUE,A2,B2) 위 수식은 A2와 B2 사이에 공백을 넣고, 둘 중 하나가 비어 있으면 빈 셀은 건너뜁니다. 5. 여러 셀을 한 번에 합치기 A2:C2처럼 여러 셀을 한 번에 합치고 싶다면 TEXTJOIN이 편합니다. =TEXTJOIN(" ",TRUE,A2:C2) 주소의 시·군·구·상세주소처럼 여러 열의 값을 한 줄로 만들 때 유용합니다. 6. 한쪽 셀이 비어 있을 때 불필요한 공백 없애기 단순히 =A2&" ...

전체 중 일부가 몇 %인지 바로 계산하기 엑셀에서 “전체 200명 중 50명은 몇 퍼센트?”, “총매출 중 특정 상품 비중은 몇 %?”처럼 비율을 구할 때는 일부값 ÷ 전체값 으로 계산합니다. 비율 수식 바로 만들기 1. 전체 중 일부가 몇 퍼센트인지 계산 A2가 전체값, B2가 일부값이라면 비율은 다음과 같습니다. =B2/A2 결과 셀을 백분율(%) 형식으로 지정하면 0.25는 25%로 표시됩니다. 2. 예: 200명 중 50명은 몇 %? =50/200 결과는 0.25이며, 셀 서식을 백분율로 바꾸면 25%가 됩니다. 3. 오류를 막으려면 IFERROR 사용 전체값이 0이거나 비어 있을 수 있다면 IFERROR를 사용하면 #DIV/0! 오류를 방지할 수 있습니다. =IFERROR(B2/A2,0) 4. 특정 값이 전체의 몇 %인지 행별로 계산 A열에 전체 매출, B열에 특정 상품 매출이 있다면 C2에 아래 수식을 입력하고 아래로 복사할 수 있습니다. =IFERROR(B2/A2,0) 5. 합계 대비 각 항목의 비율 계산 B2:B10에 각 항목 금액이 있고 전체 합계 대비 각 항목의 비율을 구하려면 SUM과 절대참조를 함께 사용할 수 있습니다. =B2/SUM($B$2:$B$10) 아래로 복사할 때는 전체 합계 범위를 고정하세요. $B$2:$B$10 처럼 절대참조를 사용해야 행을 내려도 전체 합계 범위가 움직이지 않습니다. 6. 특정 항목이 전체 합계에서 차지하는 비율 예를 들어 지역별 매출 표에서 서울 매출이 전체 매출의 몇 %인지 구한다면, 먼저 서울 매출을 SUMIF로 계산한 뒤 전체 합계로 나눌 수 있습니다. =SUMIF(A:A,"서울",B:B)/SUM(B:B) 7. 퍼센트 값을 숫자로 표시하고 싶다면 백분율 서식을 사용하지 않고 숫자 25처럼 표시하려면 결과에 100을 곱할 수 있습니다. =B2/A2*100 다만 이 경우 셀에 % 서식을 다시 적용하면 안 됩니...

정가와 판매가만 있으면 할인율을 바로 계산할 수 있습니다. 엑셀에서 할인율은 (정가-할인가) ÷ 정가 로 계산합니다. 할인 금액, 할인 후 가격, 여러 상품의 할인율 비교까지 같은 구조로 정리할 수 있습니다. 할인율 수식 바로 만들기 1. 정가와 할인가로 할인율 계산 A2가 원래 가격, B2가 할인 후 판매가라면 할인율은 다음과 같습니다. =(A2-B2)/A2 결과 셀에 백분율(%) 서식을 적용하면 0.2는 20%로 표시됩니다. 2. 0으로 나누는 오류 방지 정가가 비어 있거나 0인 경우를 대비하려면 IFERROR를 함께 사용할 수 있습니다. =IFERROR((A2-B2)/A2,0) 3. 할인 금액 계산 할인율이 아니라 실제로 얼마가 할인됐는지 알고 싶다면 정가에서 판매가를 빼면 됩니다. =A2-B2 할인 금액과 할인율은 다릅니다. 정가 100,000원에서 80,000원에 판매한다면 할인 금액은 20,000원, 할인율은 20%입니다. 4. 할인율을 알고 있을 때 할인 후 가격 계산 A2가 정가, B2가 할인율이라면 실제 판매가는 다음과 같이 계산합니다. =A2*(1-B2) 예를 들어 B2에 20%가 입력되어 있으면 정가의 80%가 계산됩니다. 5. 할인율을 숫자 20으로 입력했다면 할인율 셀에 20%가 아니라 숫자 20을 입력한 경우에는 100으로 나눠야 합니다. =A2*(1-B2/100) 20과 20%는 Excel에서 다른 값입니다. 20%는 실제 값이 0.2이고, 숫자 20은 그대로 20입니다. 할인율을 어떤 방식으로 입력했는지에 따라 수식이 달라집니다. 6. 할인 후 가격에서 원래 가격 역산 B2가 할인 후 가격, C2가 할인율이라면 정가는 다음처럼 역산할 수 있습니다. =B2/(1-C2) 7. 여러 상품 할인율 비교 각 행의 정가가 A열, 판매가가 B열이라면 C2에 아래 수식을 입력한 뒤 아래로 복사하면 상품별 할인율을 비교할 수 있습니다. =IFERROR((A2-B2...

목표 대비 실적이 몇 %인지 바로 계산하기 매출 목표, 생산 목표, 상담 목표처럼 목표값과 실제 실적을 비교할 때는 실적 ÷ 목표 로 달성률을 계산합니다. 100%를 넘으면 목표 초과, 100% 미만이면 아직 목표에 도달하지 않은 상태입니다. 목표 달성률 수식 바로 만들기 1. 목표 달성률 기본 공식 A2가 목표값, B2가 실제 실적이라면 달성률은 다음과 같이 계산합니다. =B2/A2 결과 셀을 백분율(%) 형식으로 지정하면 0.85는 85%, 1.1은 110%로 표시됩니다. 2. 목표값이 0일 때 오류 방지 목표값이 0이면 나눗셈 오류가 발생할 수 있으므로 IFERROR를 함께 사용할 수 있습니다. =IFERROR(B2/A2,0) 3. 목표까지 얼마나 남았는지 계산 달성률과 함께 부족한 실적을 숫자로 보고 싶다면 목표에서 현재 실적을 빼면 됩니다. =A2-B2 이미 목표를 초과했을 때 음수가 나오지 않게 하려면 MAX를 사용할 수 있습니다. =MAX(A2-B2,0) 4. 목표 초과율 계산 목표보다 얼마나 초과했는지를 퍼센트로 보고 싶다면 다음과 같이 계산합니다. =IFERROR((B2-A2)/A2,0) 달성률과 초과율은 다릅니다. 목표 100, 실적 120이라면 달성률은 120%, 목표 대비 증감률은 +20%입니다. 5. 달성 여부를 자동으로 표시하기 달성률이 100% 이상이면 “달성”, 그렇지 않으면 “미달”로 표시하려면 IF를 함께 사용할 수 있습니다. =IF(B2/A2>=1,"달성","미달") 6. 목표 달성률에 따라 상태를 세 단계로 표시 예를 들어 100% 이상은 달성, 80% 이상은 진행중, 그 미만은 미달로 나누려면 중첩 IF를 사용할 수 있습니다. =IF(B2/A2>=1,"달성",IF(B2/A2>=0.8,"진행중","미달")) 원하는 결과 수식 목표 달성률 =...

엑셀에서 두 날짜가 며칠 차이인지 바로 계산하기 시작일과 종료일 사이의 일수를 구할 때는 단순 뺄셈이 가장 빠릅니다. 다만 시작일과 종료일을 모두 포함할지, 오늘까지 계산할지에 따라 수식이 달라집니다. 두 날짜 차이 수식 만들기 1. 두 날짜 사이 일수 계산 A2가 시작일, B2가 종료일이라면 가장 기본적인 날짜 차이는 다음과 같습니다. =B2-A2 예를 들어 9월 1일부터 9월 5일까지라면 결과는 4일입니다. 이는 두 날짜 사이의 경과 일수를 계산한 값입니다. 2. 시작일과 종료일을 모두 포함하려면 일정 관리처럼 첫날과 마지막 날까지 모두 포함해서 세고 싶다면 1을 더합니다. =B2-A2+1 +1을 언제 붙이나요? “두 날짜의 차이”를 구하면 +1 없이 계산하고, “시작일부터 종료일까지 총 며칠인지”를 구하면 +1을 붙이는 경우가 많습니다. 3. 특정 날짜부터 오늘까지 며칠 지났는지 계산 A2의 날짜부터 오늘까지 경과 일수를 구하려면 TODAY()를 사용할 수 있습니다. =TODAY()-A2 4. 오늘부터 마감일까지 며칠 남았는지 계산 B2가 마감일이라면 오늘부터 남은 날짜는 다음과 같이 계산할 수 있습니다. =B2-TODAY() 5. 몇 개월 차이인지 계산 단순 일수가 아니라 완료된 개월 수가 필요하다면 DATEDIF를 사용할 수 있습니다. =DATEDIF(A2,B2,"m") 6. 몇 년 차이인지 계산 완료된 년수를 구하려면 단위를 "y" 로 바꿉니다. =DATEDIF(A2,B2,"y") 원하는 결과 수식 두 날짜 사이 경과 일수 =B2-A2 시작일·종료일 모두 포함 =B2-A2+1 시작일부터 오늘까지 =TODAY()-A2 오늘부터 마감일까지 =B2-TODAY() 완료된 개월 수 =DATEDIF(A2,B2,"m") 완료된 년수 =DATEDIF(A2,B2,"y") 7. 주말을 빼...

값이 있으면 “완료”, 비어 있으면 “미완료”로 표시하기 엑셀 업무표에서 입력 여부에 따라 상태를 자동 표시하고 싶다면 IF 함수로 간단하게 만들 수 있습니다. “값이 있으면 완료”, “빈칸이면 미완료”, “날짜가 입력되면 처리완료” 같은 패턴에 그대로 적용할 수 있습니다. 내 조건으로 IF 수식 만들기 1. 값이 있으면 완료, 없으면 미완료 A2에 값이 입력되어 있으면 “완료”, 비어 있으면 “미완료”로 표시하려면 다음과 같이 작성합니다. =IF(A2<>"","완료","미완료") 2. 값이 있으면 완료, 없으면 빈칸 미완료라는 문자도 표시하고 싶지 않다면 거짓일 때 결과를 빈 문자열로 바꾸면 됩니다. =IF(A2<>"","완료","") 3. 날짜가 입력되면 처리완료 표시 예를 들어 C2에 처리일이 입력되면 “처리완료”, 날짜가 없으면 “대기”로 표시할 수 있습니다. =IF(C2<>"","처리완료","대기") 4. 특정 문자가 입력되면 완료 표시 B2가 정확히 “승인”일 때만 완료로 표시하고 싶다면 값 존재 여부가 아니라 특정 값을 조건으로 사용합니다. =IF(B2="승인","완료","미완료") 5. 두 셀에 모두 값이 있어야 완료 담당자와 처리일처럼 두 항목이 모두 입력된 경우에만 완료로 표시하려면 AND와 IF를 함께 사용할 수 있습니다. =IF(AND(A2<>"",B2<>""),"완료","미완료") 실무에서 자주 쓰는 형태 접수번호가 있으면 접수완료 · 처리일이 있으면 처리완료 · 담당자와 처리일이 모두 있으면 완료처럼 상태열을 자동화할 수 있습니다. 6. 값이...

엑셀에서 0 대신 빈칸으로 보이게 만들기 계산 결과가 0일 때 셀에 0을 표시하지 않고 빈칸처럼 보이게 하고 싶다면 IF 함수를 사용하는 방법이 가장 이해하기 쉽습니다. 원본 값을 유지할지, 수식 결과만 숨길지에 따라 방법이 달라집니다. 0이면 빈칸 IF 수식 만들기 1. 값이 0이면 빈칸, 아니면 원래 값 표시 A2의 값이 0일 때만 빈칸으로 표시하고, 0이 아니면 A2 값을 그대로 보여주려면 다음과 같이 작성합니다. =IF(A2=0,"",A2) 2. 계산 결과가 0이면 빈칸으로 표시 예를 들어 B2-C2의 계산 결과가 0일 때 빈칸으로 만들고 싶다면 계산식을 IF 안에 넣습니다. =IF(B2-C2=0,"",B2-C2) 핵심은 ""입니다. IF 함수의 결과 부분에 큰따옴표 두 개( "" )를 사용하면 화면상 빈 문자열을 반환할 수 있습니다. 3. 원본 셀이 비어 있으면 결과도 빈칸으로 두기 원본 A2가 비어 있는데 다른 계산식 때문에 0이 표시되는 상황이라면 먼저 A2의 빈칸 여부를 확인할 수 있습니다. =IF(A2="","",A2*10) 4. VLOOKUP 결과가 0으로 나올 때 빈칸 처리 조회 결과가 0이면 빈칸으로 보이게 만들 수도 있습니다. 기존 VLOOKUP 수식을 IF로 한 번 감싸는 방식입니다. =IF(VLOOKUP(A2,F:G,2,FALSE)=0,"",VLOOKUP(A2,F:G,2,FALSE)) #N/A 오류와 0은 다른 문제입니다. 찾는 값 자체가 없어서 #N/A가 발생한 경우에는 단순히 “0이면 빈칸” 조건으로 해결되지 않습니다. 조회 오류의 원인을 따로 확인해야 합니다. VLOOKUP #N/A 원인 확인하기 5. 0을 숨기는 것과 실제 빈 셀은 다릅니다 "" 를 반환하는 수식은 화면에서는 빈칸처럼 보이지만, 완전히 비어 있는 셀과 항...

엑셀에서 “완료”, “미완료”, “서울” 같은 특정 문자가 몇 개인지 세기 업무표에서 특정 상태나 지역명이 몇 번 나오는지 세고 싶다면 COUNTIF가 가장 간단합니다. 조건이 두 개 이상이면 COUNTIFS로 확장하면 됩니다. 내 조건으로 개수 수식 만들기 1. “완료”가 몇 개인지 세기 B열에 업무 상태가 있고 “완료”라는 값의 개수를 세고 싶다면 다음 수식을 사용할 수 있습니다. =COUNTIF(B:B,"완료") 2. “미완료”가 몇 개인지 세기 같은 방식으로 “미완료”라는 텍스트를 세려면 조건만 바꾸면 됩니다. =COUNTIF(B:B,"미완료") 3. 특정 문자가 포함된 셀 개수 세기 셀 내용이 정확히 “완료”가 아니라 “처리완료”, “최종완료”처럼 다른 글자와 함께 들어 있다면 와일드카드 * 를 사용할 수 있습니다. =COUNTIF(B:B,"*완료*") *의 의미 별표(*)는 앞이나 뒤에 다른 문자가 있어도 해당 글자가 포함되어 있으면 조건에 맞는 것으로 처리합니다. 4. 서울이면서 완료인 행 개수 세기 A열이 지역, B열이 상태라면 조건이 두 개이므로 COUNTIFS를 사용합니다. =COUNTIFS(A:A,"서울",B:B,"완료") 5. 조건이 세 개라면? COUNTIFS는 조건 범위와 조건을 계속 추가할 수 있습니다. 예를 들어 A열=서울, B열=완료, C열=영업팀이라면 다음과 같습니다. =COUNTIFS(A:A,"서울",B:B,"완료",C:C,"영업팀") 6. 숫자 조건과 문자 조건을 같이 세기 예를 들어 B열이 “완료”이고 C열 점수가 80 이상인 행만 세고 싶다면 다음과 같이 사용할 수 있습니다. =COUNTIFS(B:B,"완료",C:C,">=80") 7. 완료·미완료 현황표 만들 때 자주 쓰는...

엑셀에서 빈칸을 빼고 개수만 세고 싶다면 “데이터가 들어 있는 셀만 몇 개인지”, “빈칸은 제외하고 건수만 세고 싶은데 어떤 함수를 써야 하는지”가 헷갈릴 수 있습니다. 빈칸 제외 개수는 데이터 형태에 따라 COUNTIF, COUNTA 또는 COUNTIFS를 선택하면 됩니다. 내 조건으로 개수 수식 만들기 1. 빈칸이 아닌 셀 개수 세기 A열에서 빈칸이 아닌 셀의 개수를 세려면 COUNTIF를 사용할 수 있습니다. =COUNTIF(A:A,"<>") 조건 <> 는 “같지 않다”는 의미이고, 빈 문자열과 같지 않은 셀을 세는 방식으로 이해하면 됩니다. 2. 값이 들어 있는 셀 전체를 세려면 COUNTA 조건식보다 “무언가 입력된 셀 전체”를 세는 것이 목적이라면 COUNTA가 더 간단할 수 있습니다. =COUNTA(A:A) COUNTIF와 COUNTA 차이 COUNTIF는 조건을 지정해 셀 개수를 세고, COUNTA는 비어 있지 않은 셀의 개수를 세는 함수입니다. 3. 빈칸 개수만 따로 세고 싶다면 반대로 비어 있는 셀 자체의 개수를 알고 싶다면 COUNTBLANK를 사용할 수 있습니다. =COUNTBLANK(A:A) 원하는 작업 추천 수식 빈칸이 아닌 셀 개수 =COUNTIF(A:A,"<>") 입력된 셀 전체 개수 =COUNTA(A:A) 빈칸 셀 개수 =COUNTBLANK(A:A) 4. 특정 조건 + 빈칸 제외를 동시에 적용하기 예를 들어 A열이 서울이고 B열이 비어 있지 않은 행의 개수를 세려면 COUNTIFS를 사용할 수 있습니다. =COUNTIFS(A:A,"서울",B:B,"<>") A열의 지역 조건과 B열의 빈칸 제외 조건을 동시에 만족하는 행만 계산합니다. 5. 수식 결과가 빈 문자열("")인 셀은 주의 겉보기에는 빈칸처럼 보여도 셀에 수식이 들어 ...

엑셀 다른 표에서 값 가져오기, 어떤 함수를 써야 할까요? 사원번호, 이름, 상품코드처럼 기준값 하나를 이용해 다른 표의 부서명·단가·전화번호·상태값을 가져오는 작업은 실무에서 매우 자주 발생합니다. 이때 VLOOKUP 또는 XLOOKUP을 사용하면 됩니다. 내 표에 맞는 조회 수식 만들기 1. 가장 기본적인 상황 현재 시트 A2에 사원번호가 있고, 다른 표의 F열에는 사원번호, G열에는 부서명이 있다고 가정해보겠습니다. A2의 사원번호를 F열에서 찾고 같은 행의 G열 값을 가져오려면 아래처럼 사용할 수 있습니다. =XLOOKUP(A2,$F$2:$F$1000,$G$2:$G$1000,"찾는 값 없음") XLOOKUP을 사용할 수 없는 환경에서는 VLOOKUP도 사용할 수 있습니다. =VLOOKUP(A2,$F$2:$G$1000,2,FALSE) 2. VLOOKUP과 XLOOKUP 중 무엇을 쓰면 되나요? 상황 추천 최신 Excel을 사용하고 있음 XLOOKUP 기존 파일과 호환이 중요함 VLOOKUP 왼쪽 열의 값을 반환해야 함 XLOOKUP 열 추가·삭제가 자주 발생함 XLOOKUP 실무에서는 XLOOKUP이 편한 경우가 많습니다. 조회할 범위와 반환할 범위를 각각 지정하기 때문에 VLOOKUP처럼 몇 번째 열인지 직접 계산할 필요가 없습니다. 3. 이름으로 전화번호나 부서명 가져오기 기준값이 사원번호가 아니라 이름이어도 원리는 같습니다. =XLOOKUP(A2,$F$2:$F$1000,$G$2:$G$1000,"") A2의 이름을 F열에서 찾고 G열의 전화번호나 부서명을 가져오는 방식입니다. 4. 다른 시트에서 값 가져오기 조회표가 다른 시트에 있다면 범위 앞에 시트명을 붙이면 됩니다. =XLOOKUP(A2,직원목록!$A$2:$A$1000,직원목록!$C$2:$C$1000,"") 시트명에 공백이 있다면 작은따옴표로 감싸는 방식이 필요...

엑셀 공백 제거, TRIM만으로 해결되지 않을 때까지 셀 값이 똑같아 보이는데 VLOOKUP이나 XLOOKUP이 값을 찾지 못하거나, 복사한 데이터에 이상한 공백이 남는다면 일반 공백 외의 숨은 문자가 섞였을 수 있습니다. 문제 유형에 따라 TRIM, SUBSTITUTE, CLEAN을 다르게 사용해야 합니다. 내 데이터에 맞는 공백 제거 수식 만들기 1. 앞뒤 공백과 연속 공백 제거 가장 먼저 사용할 수 있는 함수는 TRIM입니다. =TRIM(A2) TRIM은 불필요한 일반 공백을 정리하고 단어 사이의 연속된 일반 공백을 한 칸으로 줄이는 데 적합합니다. 2. 띄어쓰기를 전부 없애고 싶다면 상품코드, 전화번호 형태의 문자열, 식별값처럼 공백 자체가 필요 없다면 SUBSTITUTE를 사용할 수 있습니다. =SUBSTITUTE(A2," ","") 이름이나 문장에는 주의하세요. 이 방식은 정상적인 단어 사이 띄어쓰기까지 모두 제거합니다. 단순 정리가 목적이라면 먼저 TRIM을 사용하는 편이 적합합니다. 3. TRIM을 했는데도 공백이 안 없어지는 경우 웹페이지나 외부 시스템에서 복사한 값에는 일반 스페이스와 다른 문자가 들어 있을 수 있습니다. 대표적으로 CHAR(160) 형태의 공백이 문제가 되는 경우가 있습니다. =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) 이 수식은 CHAR(160)을 일반 공백으로 바꾼 뒤 CLEAN과 TRIM으로 다시 정리합니다. 4. CLEAN은 언제 사용하나요? CLEAN은 가져온 데이터에 포함된 일부 인쇄되지 않는 제어문자를 제거할 때 사용합니다. =CLEAN(A2) 증상 우선 사용할 방식 앞뒤에 불필요한 공백이 있음 TRIM 모든 띄어쓰기를 없애야 함 SUBSTITUTE TRIM 후에도 이상한 공백이 남음 SUBSTITUTE + CLEAN + TRIM 외부 데이터에 숨은 제어문자가 의...

엑셀에서 증감률·퍼센트 계산하기 전월 대비 매출이 몇 % 늘었는지, 일부 값이 전체의 몇 %인지, 할인율이나 목표 달성률을 구하고 싶다면 계산 목적에 따라 수식이 달라집니다. A2와 B2처럼 값이 들어 있는 셀만 지정해도 바로 사용할 수 있습니다. 증감률·퍼센트 수식 자동 만들기 전월 대비 매출이 몇 % 늘었는지 바로 계산하기 이전 값과 현재 값을 비교해 증가율·감소율을 구하려면 기본 공식은 (현재값-이전값)÷이전값 입니다. 엑셀에서는 0으로 나누는 오류까지 고려해 IFERROR와 함께 쓰면 실무에서 편합니다. 증감률 수식 바로 만들기 전월 대비 증감률 계산 A2가 이전 달 매출, B2가 이번 달 매출이라면 증감률은 (현재값-이전값)÷이전값 으로 계산합니다. =(B2-A2)/A2 예시 지난달 매출이 1,000만원이고 이번 달 매출이 1,200만원이면 증감률은 20%입니다. 반대로 이번 달 값이 더 작다면 음수의 증감률이 표시됩니다. 실무 예제: 전월 대비 증감률 계산 A2가 전월 매출, B2가 이번 달 매출이라면 전월 대비 증감률은 다음과 같이 계산합니다. =(B2-A2)/A2 셀 서식을 백분율(%)로 지정하면 0.25는 25%처럼 표시됩니다. 0으로 나누는 오류까지 막으려면 전월 값이 0이면 나눗셈 오류가 발생할 수 있으므로 IFERROR를 함께 사용할 수 있습니다. =IFERROR((B2-A2)/A2,0) 전년 대비 증가율도 공식은 동일 A2가 전년도 값, B2가 올해 값이라면 같은 공식으로 전년 대비 증감률을 계산할 수 있습니다. =IFERROR((B2-A2)/A2,0) 증가율과 증가한 금액은 다릅니다. 증가한 금액은 =B2-A2 , 증가율은 =(B2-A2)/A2 입니다. 감소했을 때는 음수 퍼센트로 표시 이전 값이 100이고 현재 값이 80이라면 결과는 -20%입니다. 별도의 감소율 공식이 필요한 것이 아니라 같은 증감률 공식을 사용하면 됩니다. 목표 대비 달성률 계산...

엑셀에서 두 날짜 차이와 근속기간 계산하기 A2에 시작일, B2에 종료일이 있을 때 두 날짜 사이가 며칠인지, 몇 개월인지, 몇 년인지 또는 근속기간을 년·개월·일로 표시하려면 날짜 계산 수식을 사용할 수 있습니다. 날짜 차이·근속기간 생성기 열기 입사일만 있으면 근속기간을 자동으로 계산 입사일 기준으로 “몇 년 몇 개월 근무했는지”, 재직기간이 몇 개월인지, 오늘까지 며칠이 지났는지를 계산하려면 DATEDIF와 TODAY를 함께 사용할 수 있습니다. 근속기간 수식 바로 만들기 두 날짜 사이 일수 계산 가장 단순한 날짜 차이는 종료일에서 시작일을 빼면 됩니다. =B2-A2 시작일과 종료일을 모두 포함하려면 =B2-A2+1 예를 들어 9월 1일부터 9월 3일까지를 모두 포함하면 3일로 계산됩니다. 실무 예제: 입사일부터 오늘까지 근속기간 계산 A2에 입사일이 입력되어 있다면 오늘 날짜까지의 근속연수는 다음처럼 계산할 수 있습니다. =DATEDIF(A2,TODAY(),"y") 근속 개월 수 계산 완료된 개월 수를 기준으로 재직기간을 계산하려면 단위를 "m" 으로 사용합니다. =DATEDIF(A2,TODAY(),"m") 몇 년 몇 개월인지 한 셀에 표시 =DATEDIF(A2,TODAY(),"y")&"년 "&DATEDIF(A2,TODAY(),"ym")&"개월" 몇 년 몇 개월 며칠까지 표시 =DATEDIF(A2,TODAY(),"y")&"년 "&DATEDIF(A2,TODAY(),"ym")&"개월 "&DATEDIF(A2,TODAY(),"md")&"일" DATEDIF 단위 기억하기 "y" = ...

엑셀 셀 내용 합치기와 분리하기 A2의 이름과 B2의 부서를 한 셀로 합치거나, “홍길동-영업팀”처럼 한 셀에 들어 있는 내용을 하이픈·공백·슬래시 기준으로 나누고 싶다면 TEXTJOIN, TEXTBEFORE, TEXTAFTER 함수를 활용할 수 있습니다. 텍스트 합치기·분리 도구 열기 두 셀의 텍스트 합치기 A2와 B2의 내용을 공백으로 연결하려면 TEXTJOIN 함수를 사용할 수 있습니다. =TEXTJOIN(" ",TRUE,A2,B2) 예시 A2가 “홍길동”, B2가 “영업팀”이라면 결과는 “홍길동 영업팀”이 됩니다. 하이픈이나 슬래시를 넣어서 합치기 공백 대신 원하는 문자를 구분자로 사용할 수 있습니다. =TEXTJOIN("-",TRUE,A2,B2) =TEXTJOIN("/",TRUE,A2,B2) 원하는 결과 구분자 홍길동 영업팀 공백 홍길동-영업팀 - 홍길동/영업팀 / 구분자 앞부분만 가져오기 A2에 “홍길동-영업팀”이 들어 있고 하이픈 앞의 이름만 가져오고 싶다면 TEXTBEFORE를 사용할 수 있습니다. =TEXTBEFORE(A2,"-") 결과는 “홍길동”입니다. 구분자 뒷부분만 가져오기 같은 데이터에서 하이픈 뒤의 “영업팀”만 가져오려면 TEXTAFTER를 사용합니다. =TEXTAFTER(A2,"-") 활용 예 이름-부서, 상품코드-옵션, 지역/지점, 이메일 주소처럼 일정한 구분자가 들어 있는 데이터를 정리할 때 활용할 수 있습니다. 공백을 기준으로 나누려면? 텍스트 사이가 공백으로 구분되어 있다면 구분자에 공백 한 칸을 지정할 수 있습니다. =TEXTBEFORE(A2," ") =TEXTAFTER(A2," ") TEXTJOIN과 & 연산자의 차이 두 셀만 단순히 연결한다면 & 연산자도 사용할 수 있습니다. =A2&...

엑셀 중복값, 수식으로 바로 찾기 A열에 같은 이름이나 번호가 두 번 이상 들어 있는지 확인하려면 COUNTIF를 활용할 수 있습니다. “중복”이라고 표시하거나, 같은 값의 개수를 세거나, 조건부 서식으로 색을 표시하는 방식이 대표적입니다. 중복값 찾기 도구 열기 중복이면 표시하고, 몇 번 나오는지도 확인하기 엑셀에서 중복값을 찾는 목적은 크게 세 가지입니다. 중복 여부를 표시하거나, 같은 값이 몇 번 나오는지 세거나, 조건부 서식으로 한눈에 표시하는 것입니다. 중복값 수식 바로 만들기 중복이면 “중복”이라고 표시하기 A2의 값이 A열에 두 번 이상 존재하면 “중복”이라고 표시하려면 다음 수식을 사용할 수 있습니다. =IF(COUNTIF(A:A,A2)>1,"중복","") 사용 방법 예를 들어 B2에 위 수식을 입력한 뒤 아래 행으로 복사하면 A열의 각 값이 중복인지 확인할 수 있습니다. 실무 예제: 같은 값이 두 번 이상 나오면 “중복” 표시 A열에 사번, 주문번호, 이메일처럼 중복 여부를 확인할 값이 있다고 가정하겠습니다. A2의 값이 A열에 두 번 이상 존재하면 “중복”이라고 표시하려면 다음 수식을 사용할 수 있습니다. =IF(COUNTIF(A:A,A2)>1,"중복","") 같은 값이 몇 번 나오는지 개수 세기 중복 여부가 아니라 A2 값이 전체 범위에서 몇 번 등장하는지 알고 싶다면 COUNTIF만 사용하면 됩니다. =COUNTIF(A:A,A2) 조건부 서식으로 중복값 강조하기 별도 결과 열을 만들지 않고 중복된 셀 자체를 강조하려면 조건부 서식의 수식 규칙에 다음 수식을 사용할 수 있습니다. =COUNTIF($A:$A,A2)>1 어떤 방식을 선택하면 될까요? 중복 여부를 결과 열에 표시 → IF + COUNTIF 중복 횟수 확인 → COUNTIF 셀 자체를 강조 → 조건부 서식 + COUNT...

엑셀 조건에 맞는 개수 세기 A열에서 “서울”이 몇 개인지, 또는 A열이 서울이면서 B열이 완료인 행이 몇 개인지 알고 싶다면 COUNTIF와 COUNTIFS 함수를 사용할 수 있습니다. COUNTIF · COUNTIFS 생성기 열기 “완료가 몇 개인지”, “서울이면서 완료인 행이 몇 개인지” 바로 세기 조건 하나의 개수는 COUNTIF, 조건이 두 개 이상이면 COUNTIFS를 사용합니다. 함수 이름보다 먼저 내가 세고 싶은 조건이 몇 개인지 확인하면 수식 선택이 쉬워집니다. 내 조건으로 개수 수식 만들기 COUNTIF 함수 기본 공식 COUNTIF는 한 가지 조건에 맞는 셀의 개수를 셉니다. =COUNTIF(조건범위,조건) 예시: A열에서 서울이 몇 개인지 세기 =COUNTIF(A:A,"서울") A열에서 값이 “서울”인 셀의 개수를 반환합니다. 실무 예제: 완료된 건수만 세기 B열에 업무 상태가 있고 값이 ‘완료’인 행의 개수를 알고 싶다면 COUNTIF를 사용할 수 있습니다. =COUNTIF(B:B,"완료") 서울이면서 완료인 건수 세기 A열이 지역, B열이 업무 상태라면 조건이 두 개이므로 COUNTIFS가 적합합니다. =COUNTIFS(A:A,"서울",B:B,"완료") COUNTIF와 COUNTIFS 선택 기준 조건 1개 → COUNTIF 조건 2개 이상 → COUNTIFS 특정 숫자 이상인 셀 개수 세기 예를 들어 C열에서 80 이상인 값의 개수를 세려면 비교 연산자를 조건 안에 넣습니다. =COUNTIF(C:C,">=80") 빈칸이 아닌 셀 개수 세기 특정 범위에서 빈칸이 아닌 셀을 세는 문제도 자주 발생합니다. =COUNTIF(A:A,"<>") COUNTIF·COUNTIFS 수식 자동 만들기 COUNTIFS 함수 기본 공식 COUNTI...

엑셀 여러 조건에 맞는 값만 합계하기 A열이 서울이고 B열이 영업팀인 행의 D열 금액만 합계하려면 SUMIFS 함수를 사용할 수 있습니다. SUMIF가 한 가지 조건을 처리한다면 SUMIFS는 여러 조건을 동시에 적용할 때 적합합니다. SUMIFS 수식 생성기 열기 SUMIFS 함수 기본 공식 SUMIFS는 먼저 합계할 범위를 지정한 뒤 조건 범위와 조건을 차례대로 입력합니다. =SUMIFS(합계범위,조건범위1,조건1,조건범위2,조건2) 예시: 서울 + 영업팀 매출만 합계 A열이 지역, B열이 부서, D열이 금액이라면 다음처럼 작성할 수 있습니다. =SUMIFS(D:D,A:A,"서울",B:B,"영업팀") A열이 “서울”이면서 동시에 B열이 “영업팀”인 행의 D열 값만 합산합니다. SUMIF와 SUMIFS 차이 상황 추천 함수 지역이 서울인 금액 합계 SUMIF 지역이 서울이고 부서가 영업팀인 금액 합계 SUMIFS 3개 이상의 조건을 모두 만족하는 합계 SUMIFS 헷갈리기 쉬운 부분 SUMIF와 달리 SUMIFS는 합계 범위가 첫 번째 인수 입니다. 조건을 셀로 지정하는 방법 조건을 직접 입력하는 대신 F2에 지역, G2에 부서를 입력했다면 다음처럼 사용할 수 있습니다. =SUMIFS(D:D,A:A,F2,B:B,G2) F2와 G2의 값을 바꾸는 것만으로 합계 조건을 변경할 수 있어 반복 업무에 편리합니다. 숫자 조건도 사용할 수 있습니다 예를 들어 A열이 서울이면서 D열 금액이 100000 이상인 금액만 합계하려면 비교 연산자를 조건으로 사용할 수 있습니다. =SUMIFS(D:D,A:A,"서울",D:D,">=100000") SUMIFS 결과가 0으로 나올 때 1. 조건 텍스트가 정확히 일치하는지 확인 “서울”과 “서울 ”처럼 숨은 공백이 있으면 조건이 일치하지 않을 수 있습니다. 2. 숫자와 텍스트...

엑셀 IF 함수, 조건에 따라 결과 표시하기 A2가 80 이상이면 “합격”, 아니면 “불합격”처럼 조건에 따라 서로 다른 결과를 표시하려면 IF 함수를 사용합니다. 조건 셀과 비교값, 결과만 정하면 기본 수식을 만들 수 있습니다. IF 함수 생성기 열기 엑셀에서 조건에 따라 문자 표시하기 “80점 이상이면 합격”, “값이 있으면 완료”, “0이면 빈칸”처럼 조건에 따라 다른 결과를 보여주고 싶다면 IF 함수가 기본입니다. 내 조건으로 IF 수식 만들기 IF 함수 기본 공식 IF 함수는 조건이 참인지 거짓인지 판단한 뒤 서로 다른 결과를 반환합니다. =IF(조건,참일 때 결과,거짓일 때 결과) 예시 A2가 80 이상이면 “합격”, 그렇지 않으면 “불합격”을 표시하려면 다음 수식을 사용합니다. =IF(A2>=80,"합격","불합격") 실무 예제: 80점 이상이면 합격, 아니면 불합격 =IF(A2>=80,"합격","불합격") 값이 있으면 “완료”, 없으면 빈칸 =IF(A2<>"","완료","") 0이면 빈칸으로 표시 =IF(A2=0,"",A2) 빈칸이면 아무것도 표시하지 않기 =IF(A2="","",B2) IF 함수의 구조 =IF(조건, 조건이 참일 때 결과, 조건이 거짓일 때 결과) 숫자와 텍스트를 구분하세요. 80 같은 숫자는 따옴표 없이 쓰고, “합격”, “완료” 같은 텍스트 결과는 큰따옴표로 감싸야 합니다. IF 조건문 자동 생성하기 숫자 조건을 비교할 때 조건 연산자 예시 80 이상 >= =IF(A2>=80,"합격","불합격...