엑셀 마스터를 꿈꾸다

INDEX 함수의 활용 4번째! SMALL 함수와 같이 사용해서 조건에 맞는 값을 목록 형태로 출력하기!! 이 방법의 공식은{=INDEX(출력할데이터의범위,SMALL(IF(조건,ROW(조건을충족할때출력할데이터범위)),ROW(열번호)))} 이 공식 역시 배열 수식이므로 Ctrl + Shift + Enter로 입력해야한다. 아래의 예제 파일로 본격적으로 설명 시작!!  위 파일을 열면 Sheet1 A:B에 아래와 같이 데이터가 있다. 이 데이터를 가지고 오른쪽 E:F에 학생명을 입력하면 수강과목 목록이 출력되게 하는 수식을 만들어 보도록 하자. 위의 데이터에 공식을 넣어보면 {=INDEX(출력할데이터의범위,SMALL(IF(조건,ROW(조건을충족할때출력할데이터범위)),ROW(열번호)))} F2=INDEX($B..

INDEX, MATCH 활용 3번째  INDEX, MATCH 함수로 중복값 제거하기!!  엑셀에서의 중복값 제거는 데이터 도구에 있는 중복된 항목 제거를 주로 사용하지만 수식으로도 이를 구현할 수 있다. 일단 공식은{=INDEX(중복값제거할영역,MATCH(0,COUNTIF( 중복값제거된값의입력범위★, 중복값제거할영역),0),)} + Ctrl + Shift + Enter (배열수식) 이렇게 되는데공식을 가지고 아래의 데이터에 적용하면서 보자. 참고로 배열수식이긴 한데 내가 사용하고 있는 엑셀 365버전에서는 배열수식이 자동적용되기 때문에그 보다 이전의 버전을 사용하는 환경에서는 배열수식으로 입력해주어야 한다. 배열수식을 사용하면 수식 앞과 뒤에 { } 괄호가 표시된다.  위 파일의 INDEX, MATCH ..

INDEX, MATCH 함수의 두 번째다중조건에서의 값 찾기!! VLOOKUP을 사용할 때 다중조건으로 값을 찾기 위해서는 기본 데이터를 일부 변형해서 값을 찾을 수 있었다. 아래의 포스팅 참조~  [EXCEL/엑셀] VLOOKUP 함수2 2가지 기준으로 값 찾기, 2개 이상의 값 찾기(FEAT.COUNTIF)VLOOKUP 함수는 어떤 데이터 안에서 행기준을 잡고 그 기준에 맞는 열에서 데이터를 찾는 기능을 한다.VLOOKUP은 하나의 데이터 값이 일치하는 열을 찾는데, 데이터를 조금 변형시켜서 2가지 이상의excelai.tistory.com 하지만 INDEX, MATCH 함수를 이용하면 기본 데이터의 변형 없이 값을 구할 수 있다. 조건은 2개 이상 설정할 수 있으며, 공식은 아래와 같다. =INDEX..

오랜만에 엑셀 포스팅 시작! VLOOKUP 함수를 사용할 때 불편했던 점을 보완할 수 있는 INDEX, MATCH 함수를 알아보자. VLOOKUP 함수를 사용하는 경우 범위에서 검색할 값을 오른쪽에서만 찾을 수 있지만 INDEX, MATCH함수를 활용하면 왼쪽에서도 값을 찾을 수 있다. 예제파일 기본데이터 탭에 아래와 같은 데이터가 있다.여기에서 아래와 같은 표에 데이터를 찾아서 넣어주려면VLOOKUP을 사용하는 경우 [순번]에 값을 찾기 위해서는 기본데이터를 일부 수정하여 순번을 학생명 오른쪽으로 옮긴다던지 해야 값을 구할 수 있다. 하지만 INDEX, MATCH 함수를 활용하면 찾고자하는 값의 왼쪽에 있는 데이터를 구할 수 있다. C2의 값을 찾으려고 할 때 _ 강OO의 학교 데이터를 가져올 때 VL..

엑셀 함수 LEN은 텍스트 문자열 내의 문자의 개수를 구하는 함수이다. 나는 LEN 함수를 주로 기업메시지로 학원 수강료 안내할때 사용한다. 엑셀에서 금액을 아래처럼 표현하는 경우 표시형식을 회계로 하는 경우 표시될때는 200,000 처럼 표시되는데 메시지 양식에 붙여넣으면 표시형식이 아닌 실제 값으로 처리되는 거라 200000으로 표시된다.  문자로 발송되다보니 중간 쉼표가 없는 경우 숫자가 잘 읽히지 않아서 불편하다. 그래서 LEN 함수를 활용해서 문자의 개수를 구하고 IF를 활용해서 해결했었다.이렇게 나오도록 수식을 짜고 기업메시지 양식에 붙여넣을때는 값 붙여넣기로 수식을 날리면 해결!! D2=IF(LEN(C2)=7,LEFT(C2,1)&","&MID(C2,2,3)&","&MID(C2,5,3)&"원"..

VLOOKUP 함수는 어떤 데이터 안에서 행기준을 잡고 그 기준에 맞는 열에서 데이터를 찾는 기능을 한다.VLOOKUP은 하나의 데이터 값이 일치하는 열을 찾는데, 데이터를 조금 변형시켜서 2가지 이상의 기준으로 값을 찾을 수도 있다. 1. 2가지 기준으로 값 찾기위 표와 같이 학생별 수강과목, 세부강좌명이 있는 데이터에서 학생별, 과목별 어떤 세부강좌를 듣는지 확인하는 수식을 짜보자. VLOOKUP을 활용하서 기본적으로는강O민 학생의 수강강좌명을 찾는 수식을 작성하면 VLOOKUP(A2,A:C,3,FALSE) = 값이 미적분A 값이 나오지만 과목별로 어떤 세부강좌를 수강하는지 확인할 수 없다. 그럴때 이름과 과목을 "&"로 합쳐서 데이터를 만든 다음 그 데이터를 통해 강좌명을 선택하는 방식으로 수식을 ..

엑셀 함수 COUNTIF, COUNTIFS 사용법을 알아보자. 1. COUNTIFCOUNTIF는 조건에 맞는 데이터의 개수를 구하는 함수이다. 찾는 조건이 하나일 때 사용한다.=COUNTIF(조건범위, 조건)위 표에서처럼 국어강좌를 신청한 학생 수를 찾는다고 하면 아래와 같이 지정할 수 있다. =COUNTIF(조건범위, 조건) .. 조건을 충족하는 셀의 개수를 찾을 데이터 범위 D2:D17 =COUNTIF(조건범위, 조건) .. 국어강좌 G4=COUNTIF(D2:D17,G4) 2. COUNTIFSCOUNTIFS는 조건에 맞는 데이터의 개수를 구하는 함수이다. 찾는 조건이 하나이상일 때 사용한다.=COUNTIFS(조건범위1,조건1,조건범위2,조건2....)위 표에서처럼 1학년 학생 중 수학과목을 선택한 학..

엑셀 함수 SUMIF, SUMIFS의 사용법을 알아보자. 1. SUMIFSUMIF는 조건을 만족하는 합계를 구하는 함수다. 조건이 하나일때 사용한다.=SUMIF(조건범위, 조건지정_수식 혹은 텍스트, 합을 구할 범위) 위 표에서 처럼 김AR학생의 수강료 합계를 찾는다고 하면 아래와 같이 지정할 수 있다.=SUMIF(조건범위, 조건지정, 합을구할범위) .. 이름 전체 목록 A2:A17 =SUMIF(조건범위, 조건지정, 합을구할범위) .. 찾고자 하는 이름 E4(김AR) =SUMIF(조건범위, 조건지정, 합을구할범위) .. 수강료 전체 범위 C2:C17=SUMIF(A2:A17,$E$4,C2:C17)  2. SUMIFSSUMIFS는 여러 조건을 만족하는 합계를 구하는 함수다. 1개 이상의 조건을 만족하는 함수..

VLOOKUP 함수와 함께 MATCH 함수를 사용하는 경우가 있는데 아래와 같은 기본 데이터가 있는 경우 강OO의 학생연락처를 찾아오는 수식을 작성하면  =VLOOKUP(A2,$A$1:$D$8,4,FALSE) 처럼 작성할 수 있다. 만약 기본데이터의 B와 C 사이에 학년 데이터를 추가한다고 하면 아래와 같이 데이터가 변경되게 된다. 기본 데이터가 변경되면 기존 사용하던 수식도 변경해주어야하는데=VLOOKUP(A2,$A$1:$E$8,4,FALSE) .. 이 부분은 중간에 열 삽입하면 자동으로 변경된다. =VLOOKUP(A2,$A$1:$E$8,5,FALSE) .. 찾고자 하는 데이터의 열의 위치이기 때문에 맞게 변경해주어야 한다. 하지만 MATCH 함수를 사용한다면 데이터 열의 위치를 숫자로 기입하지 않더라..

업무 특성상 많이 사용하는 함수들이 있는데 그 중 VLOOKUP을 살펴보면, VLOOKUP 함수는 어떤 데이터 안에서 행기준을 잡고 그 기준에 맞는 열에서 데이터를 찾는 기능을 한다. =VLOOKUP(검색할값,데이터범위,몇번째열,논리값) ABCD1이름학교학년전화번호2김OOOO고1학년010-A111-22223이OOAA고2학년010-1A11-22234조OOBB고2학년010-11A1-2224 위 데이터에서 김OO의 학교를 찾고자 하면=VLOOKUP(A2,$A$1:$D$4,2,FALSE)를 입력하면 된다. =VLOOKUP(A2,$A$1:$D$4,2,FALSE)  .. 김OO의 데이터 값이 있는 위치=VLOOKUP(A2,$A$1:$D$4,2,FALSE)  .. 데이터 전체 범위=VLOOKUP(A2,$A$1:$D$..