본문 바로가기

Excel/엑셀 함수49

엑셀] index, match함수로 다중조건에 해당하는 값 찾기 여러 조건에 맞는 값 찾기 - index, match함수 이전 글에서 여러 조건에 맞는 데이터를 vlookup 함수를 이용하는 방법에 대해 포스팅했었다. 이번 글에서는 index, match함수를 이용해서 다중 조건으로 데이터를 찾는 방법에 대해 정리해본다. - 엑셀팁] 여러 조건에 맞는 값을 vlookup함수로 찾는 방법 엑셀팁] 여러 조건에 맞는 값을 vlookup함수로 찾는 방법 엑셀에서 vlookup 함수로 다중 조건에 맞는 값 찾기 엑셀에서 vlookup 함수를 통해 원하는 데이터를 쉽게 찾을 수 있다. 상당히 유용한 함수이지만 몇가지 제한점이 있는데 그 중 한가지 ttend.tistory.com 위 포스팅과 동일한 상황을 전제로 vlookup 대신 index, match함수를 이용해서 원하는 .. 2021. 1. 12.
엑셀] 표에서 왼쪽에 있는 값 찾기(lookup, index, match 함수) 엑셀에서 데이터를 찾을 때 vlookup 함수를 많이 사용한다. (직장인이 엑셀에서 가장 많이 사용하는 기능 순위에서 sum 다음으로 많이 사용하는게 vlookup이라던가... ) vlookup을 사용하기 위한 조건이 있다. 첫번째는 vlookup함수는 찾는 표에서 기준이 되는 값의 오른쪽 데이터만 검색 가능하다. 그러니까 표에서 검색 기준이 되는 키 값의 오른쪽에 있는 데이터만 검색 가능하다는 뜻이다. 두번째는 검색범위를 설정할 때 검색범위의 제일 왼쪽에 검색하려는 데이터가 오도록 범위를 설정해야 한다. - 엑셀] 표에서 원하는 값 찾기(1) - VLOOKUP 엑셀] 표에서 원하는 값 찾기(1) - VLOOKUP 업무용으로 엑셀을 사용하는 경우 소스가 되는 데이터들로부터 필요한 데이터들을 추출해서 사용.. 2021. 1. 8.
엑셀] 두 개의 리스트를 비교해서 일치하는 값 찾기 - countif 이전에 작성했던 블로그 글 중에서 두 개의 리스트를 비교해서 일치하는 셀을 조건부서식을 이용해서 표시하는 글이 있었다. - 엑셀] 두개의 리스트를 비교해서 일치하는 셀 표시하기 엑셀] 두개의 리스트를 비교해서 일치하는 셀 표시하기 두개의 리스트를 비교해서 일치하는 셀 표시하기 한 개의 시트에는 회원 명단이 있고, 다른 시트에 이번 달 회비를 납부한 사람들의 이름이 있을 때 첫번째 시트에 회비 납부한 사람들만 다른 ttend.tistory.com 요청사항(?)으로 조건부서식으로 표시하는 방법말고 별도로 "납부" "미납"처럼 두 리스트의 비교 결과를 수식으로 표현해 달라는 청이 있어서 이번 글은 두 개의 리스트를 비교해서 비교 결과를 나타내는 방법에 대해서 정리해본다. 이전 작성 글과 마찬가지로 아래와 같이.. 2021. 1. 7.
엑셀] 입력에 따라 범위를 변경해서 합계 구하기 - offset, counta함수 엑셀에서 동적 범위를 구해서 합계 계산하기 엑셀에서 계산할 때 입력되는 값에 따라 범위를 정해서 합계를 구하는 방법 아래와 같은 표가 있는 경우, 2020년의 각 달에 입력되는 값에 따라 2019년의 합계를 구해서 비교하고 싶은 경우에 사용하는 수식이다. 가령 2020년이 1월~3월까지 값이 입력되면 2019년의 합계도 1월~3월까지만 계산하고 2020년 5월까지 입력되면 2019년의 합계도 1월~5월까지로 범위를 변경해서 합계를 구하는 범위가 동적으로 계속 변경되는 수식을 만들어야 한다. 수식은 offset함수를 이용해서 계산 범위를 구하면되고여기에 counta함수가 이용된다. offset은 다음처럼 이동해서 범위를 반환한다. OFFSET(기준셀, 행방향 이동 수, 열방향 이동 수, 참조 높이, 참조 .. 2020. 12. 12.
엑셀] 조건부서식에서 match함수를 사용할 때 이전 블로그 포스팅 중에서 조건부서식을 이용해서 해당 날짜가 공휴일인 경우 셀 색깔을 바꾸는 포스팅에 match함수를 이용했다. - 엑셀] 공휴일을 찾아서 글꼴색 바꾸기 댓글로 수식에 대해 물어보시는 분이 많아서 따로 정리해본다. 위 블로그 포스팅에서는 따로 공휴일 목록을 만들어두고검색하는 날짜가 공휴일 목록과 일치하면 셀서식을 변경하는 조건부서식에 관한 글이었는데 수식은 다음과 같다. =MATCH(A4,공휴일,0) >= 1 댓글 질문들은 수식 뒤에 >=1 이 무엇을 의미하는지에 대한 것이었다. 음... 부실한 설명 탓. ㅎㅎ match함수는 원래 지정한 항목을 조건으로 주어진 범위 내에서 찾고, 범위 내에서 해당 항목의 위치값을 반환하는 함수이다. 범위에 다음과 같은 과일 리스트가 있고 수박사과배딸기 .. 2020. 12. 7.
엑셀] row 함수와 column 함수 row 함수와 column 함수 row 함수와 column 함수는 쓰임이나 성격이 비슷하다. row 함수가 행에 대한 값을 반환하고 column 함수가 열에 대한 값을 반환한다는 차이만 있다. 일단 row 함수에 대해 알아보자. row 함수는 특별한 인수 없이 row 함수만 사용한다면 그 수식이 입력된 셀의 행값을 반환한다. 아래 그림에서 파란색 셀에 입력된 수식은 전부 "=row()"이다 그 셀이 A 행이거나 B행이거나 상관없이 해당 셀의 행 값을 반환한다. row 함수를 이용해서 연번을 입력하도록 하면 삭제나 삽입을 해도 연번이 자동으로 조정된다. 위 그림에서 연번을 입력하는 수식은 = row()-3 이다. 1행과 2행이 비어있고 3행이 제목행으로 4행부터 연번 "1"번을 사용하기 때문에 4행에 ro.. 2020. 8. 6.
엑셀] 가중치를 부여한 계산 - sumproduct함수 각 시험에 따라 가중치를 각각 부여하고 이 가중치를 반영해서 백점 만점으로 환산하는 경우를 생각해보자. 아래 그림처럼 각 학년별 점수가 있을 때 1학년은 20%, 2학년 30%, 3학년은 50%를 반영해서 백점 만점으로 환산해야 하는 경우를 예를 든다. 시간이 조금 걸리지만 제일 무식한(?) 방법으로 계산하면 각 학년 점수에 해당 가중치를 곱하고 그 결과를 합하면 되긴 된다. 엑셀을 다루는 만큼 조금 세련되게(?) Sumproduct함수를 이용해서 한번에 계산해보자. sumproduct함수는 "배열 또는 범위의 대응되는 값끼리 곱해서 그 합을 구하는" 함수이다. 아래 그림처럼 각각 학년별 가중치를 배열로 만들고 그 배열과 각 학년 점수를 곱한 다음 그 합을 구하도록 한다. J5셀의 수식은 다음과 같다 =.. 2017. 3. 28.
엑셀] 등수(순위) 구할 때 동점자 처리(가산점 부여) 엑셀에서 등수(순위)를 구하기 위해서 Rank함수를 사용한다. - 엑셀] RANK함수로 순위 구하고, 그룹별 순위 구하기 위 글에서 순위 구하기, 그룹 안에서 별도의 순위를 구하는 방법에 대해서 알아보았다. 이번 글에서는 순위를 구했을 때 동점자 처리 방법에 대해서 알아본다. 보통 어떤 평가든 동점자 처리의 원칙을 명시하게 마련이다. 사내 근무성적 평정에서도 동점자인 경우 경력점수를 우선한다던지, 평가에서 실기 점수보다 이론 점수를 우선한다든지... 아래 그림과 같이 직원들 평가를 실시했을 때 동점자인 경우 이론 평가 점수가 높은 직원에게 가점을 주는 상황을 예시로 들어보고자 한다. 이론 평가에 가산점을 주기 위한 수식은 아래와 같다. =G2+COUNTIFS($F$2:$F$13,F2,$D$2:$D$13,.. 2016. 12. 1.
엑셀] 어떤 조건에서의 최댓값/최솟값 구하기 max if maxifs 함수 어떤 조건 하에서 (최대값 또는 최소값.. 아~ 적응 안된다) 최댓값 또는 최솟값을 구하려면 max 함수와 if 함수를 중복시키고 배열 수식으로 작성해야 한다. 아래 그림과 같은 직원들의 급여표가 있을 때 총무팀에 근무하는 직원 중에서 가장 큰 급여가 얼마인지 계산하려면 if 함수로 총무팀 직원인지 판단하고 max 함수로 총무팀 급여 중에서 큰 값을 찾아야 한다. 수식은 다음과 같다. {=MAX(IF($B$2:$B$49="총무팀",$G$2:$G$49))} (Ctrl+Shift+Enter로 입력하는 배열수식임) [Excel/엑셀 기본, 서식] - 엑셀] 배열 수식의 기초적인 이해 엑셀2016부터 max 함수와 if 함수를 혼합한 maxifs 함수를 제공한다. maxifs함수.. 2016. 11. 3.