통합 문서 전체의 다양한 소스에서 정보를 가져오는 테이블이 있습니다. 테이블이 정기적으로 필터링되어 Excel에 능숙하지 않은 사용자가 사용하는 4가지 범주가 있습니다.
아래 테이블을 필터링하는 목록으로 필터링 섹션을 설정하고 싶습니다. 스크린샷은 , , B4셀 에 목록 값이 있는 제안된 설정을 보여 주며 , 아래 표는 입력된 값을 기준으로 필터링되는 항목입니다.C4D4E4
이 작업이나 이와 유사한 작업을 수행하는 방법을 아는 사람이 있습니까?
(확대하려면 이미지를 클릭하세요)
답변1
기본 제공 테이블 필터링을 사용하여 필터링하는 대신 특정 셀 값을 기준으로 필터링하고 싶다는 뜻인가요?
글쎄, 매크로를 사용하여 자동 필터를 호출하고 셀 값을 기준으로 사용할 수 있습니다.
매크로를 시트에 넣고 관련 셀 중 하나가 Worksheet_Change하위 항목으로 변경될 때마다 자동으로 호출되도록 할 수 있습니다.
이를 작성하는 방법은 많지만 필터링할 테이블을 지정해야 하고 필터링할 행도 지정해야 합니다.
이것은 내 예를 위해 시트에 넣은 코드입니다(시트 탭을 마우스 오른쪽 버튼으로 클릭하고 "코드 표시" 선택).
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tabl As String, i As Long, C as Range
tabl = "Table1" 'Name of the Table
If Not Application.Intersect(Range(Target.Address), Range("B4:E4")) Is Nothing And Target.Count < 5 Then
For Each C In Target
tCol = Range(tabl & "[" & C.Offset(-1).Value2 & "]").Column
If C.Value2 = "" Then
ListObjects(tabl).Range.AutoFilter Field:=tCol
Else
ListObjects(tabl).Range.AutoFilter Field:=tCol, Criteria1:=C.Value2
End If
Next C
End If
End Sub
결과:
편집하다
모든 필터를 한 번에 지울 수 있는 루프가 추가되었습니다.
이 코드에서는 헤더가 입력 셀과 테이블에서 동일해야 합니다. 그렇지 않으면 오류가 발생합니다.
-
Worksheet_Change워크시트의 셀이 변경되면 호출됩니다. 이는 특정 영역을 대상으로 하지 않는 한 잠수함의 실행을 제한하기 위해
종종 와 함께 사용됩니다 .
If Not Application.Intersect(Range(Target.Address), Range("")) Is Nothing Then
코드가 충돌하지 않도록 제한을 두는 것도 Target.Count좋은 생각입니다. 이 경우처럼 선택을 반복하려는 의도가 아니라면 종종 단일 대상으로 제한하고 싶을 때가 있습니다.
옵션이 포함된 드롭다운 목록을 원할 경우 여기에 VBA 접근 방식이 있습니다.Excel-데이터 유효성 검사를 통한 중복 제거
답변2
나는 몇 가지 기준에 따라 여러 행을 추출하는 데 도움이 되는 배열(CSE) 공식을 제안하고 싶습니다.
작동 방식:
- 소스 데이터가 Range 내에 있다고 가정합니다
A2:E10. - 기준 범위는 입니다
A16:E16. - Cell에 이 수식을 입력하고
A20다음으로 마무리합니다. Ctrl+Shift+Enter, 오른쪽을 채운 다음 아래를 채웁니다.
{=IFERROR(INDEX($A$2:$E$10,SMALL(IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})=COUNTA($A$16:$E$16),MATCH(ROW($A$2:$A$10),ROW($A$2:$A$10)),""),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")}
메모:
- 기준 셀/셀이 비어 있으면 이 수식은 모든 레코드를 반환합니다.
- 관련 레코드를 필터링하기 위해 하나 이상의 기준을 설정할 수 있습니다.
명령의 메커니즘을 설명하겠습니다.
MMULT function부울 값을 사용할 수 없으므로 작동하려면 수식에서 배열에 1을 곱해야 합니다.
MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})
된다,
MMULT({0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0},{1;1;1;1;1;1;1})
그리고 돌아오다,
{0;0;1;0;0;2;1;1;2;1}
그리고,
MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16)
된다,
{0;0;1;0;0;2;1;1;2;1}=COUNTA($A$16:$E$16)
된다,
{0;0;1;0;0;2;1;1;2;1}=2
그리고 돌아오다,
{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.
IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")
된다,
IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")
작은함수는 배열에서 k번째로 작은 숫자를 가져옵니다.
색인함수는 행 및 열 번호를 기준으로 셀 범위 또는 배열의 값을 반환합니다.