엑셀 tip(40)
-
[ 엑셀 Tip ] vlookup과 배열을 이용한 2개 특정기간 합계 구하기
vlookup과 배열을 활용해서 2개 이상의 컬럼의 합계를 구해 보도록 하겠습니다. vlookup은 한개의 값을 반환하나 배열을 사용하면 2개 이상의 값을 반환 후 연산이 가능합니다. 만약 가로형 데이터에서 특정 기간의 합계를 구하고 싶을 때나 2개의 컬럼의 합을 구하고 싶을 때 사용합니다. 데이터를 준비 후 a ~ e의 12/2 ~ 3일까지의 합을 구해 보도록 하겠습니다. vlookup 함수를 사용해서 2일과 3일을 구해보면 다음과 같습니다. 수식을 한번에 쓰기 위해서 절대 참조와 상대 참조를 활용해서 수식을 작성했습니다. =VLOOKUP( $J2, $A$2:$H$6, K$1+1, 0 ) 값을 찾아줘 J2의 값과 동일한 열을 해당 테이블에서 3번째 컬럼을 정확하 k 컬럼에 수식을 작성하고 L열로 드래그..
2022.12.16 -
[ 엑셀 Tip ] weeknum 함수 활용한 주차별 실적 집계 만들기
엑셀 weeknum 함수를 알아보도록 하겠습니다. 보통 한주는 월 ~ 일로 돌아가는데 가끔 아닌 경우가 발생합니다. 해외의 다른 문화를 가진 나라라던지 아니면 아니면 행사가 시작되는 날이 월요일이 아니라 다른 날짜라던지 아니면 월 ~ 일이 아닌 요일을 기준으로 데이터를 분석해 보고 싶다던지 아니면 일 ~ 월이 한주라고 생각하는 선배나 거래처가 있다든지 하는 경우입니다. 이런 경우 사용하면 매우 유용한 함수입니다. 한주가 7일이고 3주를 한번 보기 위해서 21일을 준비해 보았습니다. 한주의 시작하는 날의 요일을 같이 보기 위해서 요일도 추가해 놓았습니다. [ 엑셀 Tip ] text 함수로 요일 구하기 엑셀에서 요일 구하는 방법을 알아보겠습니다. 그런데 왜 "aaa"일까요? 더 추가하면 어떻게 될지 궁금합..
2022.12.15 -
[ 엑셀 Tip ] 배열 수식 활용
배열 수식을 활용해서 다중 열 계산을 진행해 보도록 하겠습니다. 배열이란 수학 시간에 배우는 행렬이라 생각하시면 됩니다. 테이블 또한 그 행렬의 확장이라고 생각하면 이해가 편합니다. 엑셀에서 보이는 행렬은 스프레드시트에 보이는 부분이나 다음과 같은 데이터는 오른쪽 중괄호의 내용과 동일한 구조입니다. 엑셀의 아무 곳에 = { 1, 2, 3 } 이라고 넣어 보시면 왼쪽과 같은 결과가 나올 것입니다. 수식창에 = { 1, 2, 3 ; 4, 5, 6 } 과 같이 입력하면 그림과 같이 결과가 나오는 것을 알 수 있습니다. 이 데이터 구조를 기억해 주시면 아래에 하는 내용을 이해하시 쉬우실 것입니다. 단가 * 수량을 해서 납품금액을 구해 보도록 하겠습니다. ① 수식을 이용해 구하기 B2 * C2를 곱한 후 아래까지..
2022.12.14 -
[ 엑셀 Tip ] 두 가지 조건 vlookup
다중 조건 vlookup에 대해서 알아보도록 하겠습니다. 데이터를 만지다 보면 여러 가지 조건의 값을 구해야 하는 상황이 많이 발생하는 데 유용한 방법입니다. 데이터를 표로 변환해 주도록 하겠습니다. 구분 "e"의 중구분 "나"의 실적 다음과 같이 조건열을 추가해 줍니다. 구분 & 중구분을 하나로 합쳐 줍니다. =VLOOKUP( H2&I2, 표1[[조건]:[실적]], 2, 0 ) 찾아줘 e나의 값을 조건에서 실적 컬럼에서 2번째 컬럼( 실적 )을 정확하게 e나의 실적은 38로 값을 잘 찾을 것을 볼 수 있습니다. 피벗테이블로 두 가지 조건값 구하기 피벗테이블에서 표1로 범위를 지정 후 확인을 눌러 줍니다. 필터에 구분, 중구분을 넣어주고 값에 실적을 넣어 준 후 구분에 "e"를 선택 및 중구분 "나" 선..
2022.12.13 -
[ 엑셀 Tip ] 대시보드 만들기_(4) ( 슬라이서 디자인 변경 )
[ 엑셀 Tip ] 대시보드 만들기_(1) ( 라인 그래프 만들기 ) 엑셀 대시보드를 만들어 보도록 하겠습니다. 원래 계획은 파워 BI로 진행하려고 하였으나 엑셀로 한번 만들어 보았습니다. 제가 사과를 너무 좋아해서 8년째 먹고 있는데 사과가 너무 비싸서 좀 sunconnector.tistory.com 그래프를 만들다 보니 과일 / 채소를 제철이라는 단어를 사용하는 경우가 많아 계절을 넣어주면 그래프의 가독성이 좋아질 것 같습니다. 파워피벗의 switch 함수를 사용해서 계절을 넣어 주도록 하겠습니다. [ 파워 BI ] Dax 함수를 이용한 등급 구분( feat. switch ) Dax switch 함수를 이용한 등급 구분을 해보도록 하겠습니다. 90점 이상은 "A", 80점 이상은 "B".....국민 ..
2022.12.12 -
[ 엑셀 Tip ] 대시보드 만들기_(3) ( 파이차트 만들기 )
[ 엑셀 Tip ] 대시보드 만들기_(1) ( 라인 그래프 만들기 ) 엑셀 대시보드를 만들어 보도록 하겠습니다. 원래 계획은 파워 BI로 진행하려고 하였으나 엑셀로 한번 만들어 보았습니다. 제가 사과를 너무 좋아해서 8년째 먹고 있는데 사과가 너무 비싸서 좀 sunconnector.tistory.com 사과의 품종 중에서도 어떤 품종은 거래가 거의 없는 것들이 있어서 걸러내고 봐야 할 거 같습니다. 홍추라는 품종을 선택했더니 '21.9월 한 달만 경매 이력이 있습니다. 품종 수량의 구성비를 만들어서 구성비가 높은 것 위주로 보면 시간이 많이 절약될 것 같습니다. 파이 차트를 만들어 보겠습니다. 그래프 우클릭 후 차트 종류 변경 후 원형을 선택 해 줍니다. 품종을 범례에 넣자 "차트 하나에 사용할수 있는 ..
2022.12.09