엑셀 숨겨진 셀(필터링 된 셀) 제외하고 복사하기, 계산하기

엑셀에서 셀을 숨기거나 필터링 한 뒤 눈에 보이는 값만 계산하거나 복사하고 싶은 경우가 종종 있습니다.

눈에 보이는 값만 계산하는 경우 SUBTOTAL이라는 함수를 이용해야 하는데 엑셀의 모든 함수가 되는 것은 아니고 11개의 함수만 가능하며 3차원 참조는 #VALUE! 오류가 발생합니다.

 

 

엑셀 숨겨진 셀(필터링 된 셀) 제외하고 복사하기, 계산하기

 

 

목차

1. 엑셀에서 보이는 셀만 복사/붙여 넣기
2. 엑셀에서 보이는 셀만 계산하기

 

 

---

1. 엑셀에서 보이는 셀만 복사/붙여 넣기

 

엑셀에서 보이는 셀만 복사/붙여넣기 - 숨기기

 

1~12월까지 데이터가 있는 테이블에서 1~3월의 데이터를 숨기기 하여 안 보이도록 처리한 후 테스트 해보겠습니다.

 

 

엑셀에서 보이는 셀만 복사/붙여넣기 - 이동 옵션

 

 

1. 복사하고 싶은 셀을 드래그하여 선택합니다.

2. [홈] 탭을 클릭합니다.

3. [찾기 및 선택]을 클릭합니다.

4. [이동 옵션]을 클릭합니다.

 

 

엑셀에서 보이는 셀만 복사/붙여넣기 - 화면에 보이는 셀만

 

 

5. [화면에 보이는 셀만]을 선택합니다.

6. [확인]을 클릭합니다.

7. Ctrl + C를 클릭하여 아까 선택해 둔 범위를 복사합니다.

8. 붙여 넣기 원하는 위치(A19)에 커서를 위치시키고 붙여 넣기(Ctrl + V)를 합니다.

 

 

엑셀에서 보이는 셀만 복사/붙여넣기 - 비교

 

 

1~3월이 제외된 상태로 복사가 되었습니다.

같은 범위를 복사하여 붙여 넣더라도 이렇게 차이가 발생합니다.

복사 붙여 넣기 뿐만 아니라 함수에서도 보이는 부분만 계산할 수 있습니다.

 

 

 

 

---

2. 엑셀에서 보이는 셀만 계산하기

 

엑셀에서 보이는 데로 계산을 하기 위해서는 SUBTOTAL 함수를 사용해야 합니다.

 

SUBTOTAL(function_num,ref1,[ref2],...)

function_num (필수): 함수에 할당된 숫자를 입력합니다.    
ref1 (필수): function_num에서 지정한 함수에서 사용되는 셀 참조 범위를 입력합니다.
ref2,... (선택): 계산될 셀 참조 범위를 추가합니다. 254개까지 가능합니다.

 

function_num에서 사용할 수 있는 함수는 아래 11가지가 있습니다.

숨겨진 행 포함	숨겨진 행 무시	함수
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

 

SUBTOTAL을 이용해 SUM 함수를 사용하는 방법은 9와 109가 있습니다.

9를 넣으면 일반 함수와 같이 범위 안에 있는 모든 셀을 더하는 것이고 109이면 보이는 셀만 더할 것입니다.

 

 

엑셀에서 보이는 셀만 계산하기

 

 

숨겨진 셀이 없는 경우 SUBTOTAL에서 9와 109는 값이 같습니다.

이 상태에서 1~3월의 값을 숨겨보겠습니다.

 

 

엑셀에서 보이는 셀만 계산하기 - 보이는 셀만 계산된 모습

 

 

값이 109는 1~3월 값의 합인 450이 포함되지 않은 것을 알 수 있습니다.

 

모든 셀 계산

=SUBTOTAL(9,B2:B13)

 

보이는 셀만 계산

=SUBTOTAL(109,B2:B13)