다둥이네(상똥/예뚱/영뚱/잼뚱)~♡

자동 필터를 적용해 데이터를 추출해야 한다면 자동 필터의 숫자 필터의 조건을 이용한다. 이 조건은 엑셀 2007 버전부투 추가된 것으로 사용자가 숫자 데이터에서 원하는 데이터를 좀 더 쉽고 빠르게 추출할 수 있도록 도와준다. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. [B]열의 판매수량 요약 값 중에서 [F2] 셀의 평균값보다 큰 실적을 달성한 담당자 데이터만 추출해 보자. [B1]셀의 ▼목록 화살표를 클릭한 다음 [숫자 필터(F)] - [평균 초과(A)]를 선택한다. [B2:B10] 범위에서 평균 값을 초과하는 실적을 보인 데이터만 추출한다. Tip : 평균 미만인 데이터를 추출하려면 [평균 초과]가 아니라 [평균 미만]을 선택한다. 평균이 아니라 특정 값을 기준으로 하겠다면 [같음]~[..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 10. 07:00

3차원 참조는 연속된 시트의 동일 셀(또는 범위)을 동시에 참조하는 방법을 말한다. 3차원 참조를 이용하면 동일한 템플릿을 사용한 집계표를 통합할 때 매우 간결하게 원하는 작업을 수행할 수 있다. 3차원 참조를 이용하여 같은 형식의 표를 통합하는 방법에 대해 알아보자. [3차원 참조를 사용할 수 있는 함수] 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. 시트탭에서 [A영업점], [B영업점], [C영업점]의 각각의 시트를 선택하면 위의 그림과 같은 표를 확인할 수 있다. 3개의 표가 모두 동일한 구조로 단가와 판매량, 매출이 집계되어 있다. 이 표를 3차원 참조를 이용해 하나로 통합해 보자. 시트탭에서 [집계표] 시트를 선택하면 상기의 표를 확인할 수 있다. [C]열의 [품명]은 다른 시트의 표..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 9. 07:00

자동필터를 적용해 데이터를 추출할 때 지정한 조건에 맞지 않는 결과가 반환될 수 있다. 이런 결과는 대부분 자동필터가 표 전체 범위에 적용되지 못했기 때문에 발생하는 것으로, 표 중간에 빈 행이 표함되어 있는 경우가 많다. 자동필터의 결과가 잘못 반환될 때 문제를 해결하는 방법을 알아보자. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. 이 표에 자동필터를 적용하고 [D]열의 지역에서 [서울특별시] 데이터만 축출해 보자. 자동필터를 적용하기 위해 표 내부의 임의의 셀을 선택하고 [데이터] - [정렬 및 필터] 그룹 - [필터]를 클릭한다. [서울특별시] 데이터만을 추출하기 위해 [D1] 셀의 ▼ 목록 화살표를 클릭한다. 필터 목록에서 [(모두 선택)]의 체크를 해제하고 [서울특별시]만 체크한 다..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 8. 07:00

월별 실적을 집계한 후 분기별 집계 작업을 추가하는 등 요약된 표에서 추가 분류 작업을 해야 하는 경우가 있다. 표 사이사이 집계 작업을 하는 경우는 조금 번거롭지만, 자동 합계 기능을 이용하면 편리하게 범위 내의 값을 추가 요약할 수 있다. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. 집계표는 실무에서 자주 사용되는 형태로 담당자 별 요약 값과 총합계 값을 계산해 완성한다. 첨부와 같은 표를 자동합계 기능으로 집계할 때는 주의할 내용이 있다. 범위를 선택하는 방법인데, 다음 순서대로 작업한다. 먼저 [C5:F5] 범위를 선택한 후 [Ctrl]을 누른 상태에서 [C9:F9], [C13:F13], [C14:F14], [G2:C14] 범위를 순서대로 선택한다. [홈] - [편집] - [자동 합계..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 5. 09:50

정렬 기능은 표를 행 방향(세로)으로 정렬하는 것뿐만 아니라 가로 방향으로도 정렬할 수가 있다. 열 방향 정렬 기능은 표를 왼쪽에서 오른쪽을호 정리할 때 유용하게 사용할 수 있다. 열 방향 정렬 방법과 이 기능을 사용할 때 주의해야 할 점을 알아보자. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. [B1:J1] 범위의 사원명 순으로 열 방향 정렬 작업을 진행한다. 먼저 열방향 정렬 작업을 진행하려면 정확하게 정렬할 데이터 범위를 선택해야 한다. [B1:J9] 범위를 선택하고 [데이터] - 정렬 및 필터] - [정렬]을 클릭한다. [정렬] 대화상자가 나타나면 상단의 [옵션]을 클릭한다. [정렬 옵션] 대화상자에서 [왼쪽에서 오른쪽(L)]을 선택하고 [확인] 단추를 클릭한다. [정렬] 대화상자에서..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 4. 07:00

VLOOKUP 함수를 사욜할 때는 꼭 지켜야 할 규칙이 있다. 참조할 값이 찾을 값의 오른쪽에 있어야 한다는 것으로 이 규칙이 지켜지지 않으면 값을 참조해 올 수가 없다. VLOOKUP 함수에 대한 규칙을 이해하고 중첩하여 사용할 수 있는 INDEX, MATCH 함수의 사용방법에 대해서 확인해 보자. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. [A1:C17] 범위의 표에서 [F2:F4] 범위와 같이 매출 순위 1, 2, 3의 실적을 집게한 경우, 해당 실적 지역을 [G2:G4] 범위에 참조하는 수식을 작성한다. VLOOKUP 함수를 이용해 값을 참조해보자. [G2] 셀을 선택하고 다음 수식을 입력하면 화면과 같이 #N/A 오류가 발생하게 된다. =VLOOKUP(F2,$A$2:$C$16,FA..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 3. 07:00

참조할 값이 여러 개의 표에 나누어 기록된 경우에는 VLOOKUP 함수의 두 번째 인수를 지정할 때 IF 함수를 사용해 참조할 표 범위를 조건에 따라 분리할 수 있다. 여러 개의 표에서 필요한 값을 참조하는 수식을 작성하는 방법에 대해 알아보자. 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. 제품이 입출고되는 창고 A와 B로 나누어져 있을 때, 이 두 개의 표를 참조하여 제품별 재고 값을 왼쪽 표ㅗ 통합하는 수식을 작성해 보자. 값을 참조할 표가 두 개이기 때문에 IF함수를 VLOOPKUP 함수 안에 중첩해서 사용한다. [B3] 셀을 선택하고 다음 수식을 입력한다. 채우기 핸들을 7행까지 드래그해서 수식을 복사한다. =VLOOKUP($A3,IF(B$2="창고A",$E$3:$H$5,$E$9:$H..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 2. 07:00

대/소문자를 구분해 값을 참조할 필요가 있다면 EXACT 함수와 INDEX, MATCH 함수를 중첩해서 사용하는 배열수식을 작성해야 한다. 배열수식이라고 해도 이전의 INDEX, MATCH 함수 구성과 같기 때문에 쉽게 사용할 수 있다. EXACT함수는 두 텍스트 값을 비교해 같으면 TRUE, 틀리면 FLASE를 반환한다. 특히 영어 대/소문자를 구분해 비교할 수 있다. 함수의 사용법은 다음과 같다. =EXACT(텍스트1, 텍스트2) 위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다. [F1] 셀에 입력된 제품 코드를 이용해 왼쪽 표의 [C2:C11] 범위의 가격을 참조해 오는 수식을 입력한다. 이때 주의할 점은 [A2:A11] 범위의 제품 코드에는 대/소문자가 구분되어 입력되었다는 것이다. [F2..

IT정보/엑셀 팁(Excel Tip) 2021. 12. 1. 07:00