뛰어나다

여러 워크시트에서 값 검색

Search Multiple Worksheets

Excel 수식: 여러 워크시트에서 값 검색일반 공식 |_+_| 요약

통합 문서의 여러 워크시트에서 값을 검색하고 개수를 반환하려면 다음을 기반으로 수식을 사용할 수 있습니다. 카운티프 그리고 간접 기능. 일부 사전 설정을 통해 이 접근 방식을 사용하여 전체 통합 문서에서 특정 값을 검색할 수 있습니다. 표시된 예에서 C5의 공식은 다음과 같습니다.





= COUNTIF ( INDIRECT ('''&sheetname&''!'&'range'),criteria)

컨텍스트 - 샘플 데이터

통합 문서에는 총 4개의 워크시트가 있습니다. 시트1 , 시트2 , 그리고 시트3 각각은 다음과 같은 1000개의 무작위 이름을 포함합니다.

샘플 데이터 - 전체 통합 문서 또는 여러 시트 검색





설명

범위 B7:B9에는 검색에 포함할 시트 이름이 포함됩니다. 이것은 단지 텍스트 문자열이며 유효한 시트 참조로 인식되도록 하려면 몇 가지 작업을 수행해야 합니다.

Excel에서 날짜에 연도를 추가

내부에서 외부로 작업하는 이 표현식은 전체 시트 참조를 작성하는 데 사용됩니다.



 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),$C)

공백이 있는 시트 이름을 허용하기 위해 작은따옴표가 추가되었으며 느낌표는 시트 이름을 포함하는 범위에 대한 표준 구문입니다. 텍스트 '1:1048576'은 워크시트의 모든 행을 포함하는 범위입니다.

B7이 평가되고 값이 연결된 후 위의 식은 다음을 반환합니다.

Excel의 채우기 핸들은 무엇입니까
 
'''&B7&''!'&'1:1048576'

에 들어가는 것 INDIRECT 함수 'ref_text' 인수로. INDIRECT는 이 텍스트를 평가하고 모든 셀에 대한 표준 참조를 반환합니다. 시트1 . 이것은 COUNTIF 함수에 범위로 들어갑니다. 기준은 다음과 같이 제공됩니다. 절대 참조 C4(수식을 C열 아래로 복사할 수 있도록 잠김).

그런 다음 COUNTIF는 값이 'mary'(이 경우 25)인 모든 셀의 개수를 반환합니다.

Excel 2010에서 차트 만들기

참고: COUNTIF는 대소문자를 구분하지 않습니다.

포함 대 같음

모든 셀을 계산하려면 포함하다 모든 셀 대신 C4의 값 동일한 C4에 추가할 수 있습니다. 와일드카드 다음과 같은 기준으로

 
''Sheet1'!1:1048576'

이제 COUNTIF는 셀의 아무 곳에서나 하위 문자열 'John'이 있는 셀을 계산합니다.

성능

일반적으로 모든 워크시트 셀을 포함하는 범위를 지정하는 것은 좋지 않습니다. 범위에 수백만 및 수백만 개의 셀이 포함되기 때문에 그렇게 하면 성능 문제가 발생할 수 있습니다. 이 예에서는 수식이 INDIRECT 함수를 사용하기 때문에 문제가 복잡합니다. 휘발성 기능 . 휘발성 함수는 워크시트가 변경될 때마다 다시 계산되므로 성능에 미치는 영향이 클 수 있습니다.

가능하면 범위를 적절한 크기로 제한하십시오. 예를 들어 데이터가 1000행 이후에 나타나지 않는다는 것을 알고 있다면 다음과 같이 처음 1000행만 검색할 수 있습니다.

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),'*'&C4&'*')
저자 데이브 브런스


^