엑셀 마스터를 꿈꾸다

최대값, 최소값을 구하는 함수인 MAX, MIN, DMAX, DMIN 함수를 알아보자. MAX, MIN은 최대값, 최소값을 구하는 함수이고= MAX(범위)= MIN(범위)DMAX, DMIN은 조건에 맞는 값의 최대값, 최소값을 구하는 함수이다.= DMAX(데이터베이스,데이터베이스에서 열의 위치, 조건범위) = DMIN(데이터베이스,데이터베이스에서 열의 위치, 조건범위)  예제 파일은 글 제일 아래에 있으니 참고!아래와 같이 시험성적 데이터로 과목별 점수의 최대값과 최소값을 구해보자. 별 조건 없이 전체 성적 데이터 기준으로 최대값과 최소값을 구하는 수식을 작성해보면 국어 점수 최대값 = MAX(C:C) 국어 점수 최소값= MIN(C:C) 이런식으로 간단하게 수식을 작성할 수 있다. MAX, MIN의 인수..

특정 문자의 위치를 표시하는 함수인 FIND, SEARCH 함수를 알아보자. 이 함수들은 문자열에서 특정 문자의 위치를 찾아낼 수 있는 함수인데 살짝 다르다. FIND는 영문자의 대소문자를 구분할 수 있지만, 와일드카드 문자를 사용할 수 없고, SEARCH는 영문자의 대소문자를 구분할 수 없지만, 와일드카드 문자를 사용할 수 있다. 용도는 살짝 다르지만 한글만을 찾을때는 똑같이 써도 된다.인수도 같다. FIND(찾으려는문자, 찾을 문자가 있는 텍스트가 입력된 셀, 시작 위치)SEARCH(찾으려는문자, 찾을 문자가 있는 텍스트가 입력된 셀, 시작 위치)  ※ 와일드카드 문자는 (?,*) 를 말한다.와일드카드 문자 ? 는 모든 단일문자와일드카드 문자 * 는 임의의 문자 나는 FIND나 SEARCH를 쓸 때 ..

엑셀에서는 조건을 설정하고 해당조건을 만족할 때 혹은 만족하지 않을때의 값을 다른 값으로 변경하여 결과값을 표시할 수 있다.예를 들면 조건이 TRUE일때 A의 값으로 표시하고 FALSE일때 B로 표시하고자 하면 =IF(조건1,조건1이 TRUE일때 나타낼 값, 조건1이 FALSE일때 나타낼 값) 이렇게 수식을 설정할 수 있다. 아래의 데이터에서 영어 점수가 80점 이상인 학생을 찾아내기 위해 수식을 사용해서 F열에 "O", "X"를 표시하는 수식을 작성해보자. F3=IF(E3>=80,"O","X") .. 조건1 설정 : E3의 셀의 값이 80 이상 F3=IF(E3>=80,"O","X") .. 조건1이 TRUE일때 텍스트 O를 표시 F3=IF(E3>=80,"O","X") .. 조건1이 FALSE일때 텍스트 ..

엑셀에서 평균을 구하는 함수 AVERAGE와 AVERAGEA 함수를 알아보자. 사실 이 함수는 기본 중에 기본인데 쉬어가는 페이지라고 보면 될 것 같다.  AVERAGE 함수와 AVERAGEA 함수는 둘 다 평균을 구하는 함수이나 용도가 다르다. AVERAGE는 지정한 영역에서 숫자 셀의 평균을 구하는 함수이고,AVERAGEA는 지정한 영역에서 빈 셀을 제외한 값이 입력된 셀의 평균을 구하는 함수이다. 아래의 성적 데이터를 과목별로 AVERAGE와 AVERAGEA를 이용하여 평균을 구해보자.  ※ 이 데이터에서 미응시를 서식으로 표시한 부분은 직전 포스팅인 자동테두리(자동서식) 조건부 서식이 적용되었다. [EXCEL/엑셀] 데이터를 입력하면 자동 테두리 표시 (조건부서식)엑셀에서 서식이 있는 데이터에서 ..

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 값이 나오지만 과목별로 어떤 세부강좌를 수강하는지 확인할 수 없다. 그럴때 이름과 과목을 "&"로 합쳐서 데이터를 만든 다음 그 데이터를 통해 강좌명을 선택하는 방식으로 수식을 ..