초보 사용자가 아닌 경우 Excel의 모든 항목 중 99%가 매개변수 또는 속성(필드)이 열을 통과하고 개체 또는 이벤트에 대한 정보가 있는 세로 테이블과 함께 작동하도록 설계되었다는 사실을 이미 알고 있었을 것입니다. 라인에서 . 피벗 테이블, 부분합, 더블 클릭으로 수식 복사 등 모든 것이 이 데이터 형식에 맞게 특별히 조정되었습니다.
그러나 예외가 없는 규칙은 없으며 상당히 규칙적인 빈도로 수평 의미 방향이 있는 테이블 또는 행과 열의 의미가 동일한 테이블이 작업에서 발견되면 어떻게 해야 하는지 묻습니다.
Excel에서 여전히 수평으로 정렬하는 방법을 알고 있는 경우(명령을 사용하여 데이터 – 정렬 – 옵션 – 열 정렬), 필터링의 상황은 더 나쁩니다. Excel에는 행이 아니라 열을 필터링하기 위한 기본 제공 도구가 없습니다. 따라서 이러한 작업에 직면한 경우 다양한 복잡성 수준의 해결 방법을 찾아야 합니다.
방법 1. 새로운 FILTER 기능
새 버전의 Excel 2021 또는 Excel 365 구독을 사용 중인 경우 새로 도입된 기능을 활용할 수 있습니다. FILTER (필터), 행뿐만 아니라 열로도 소스 데이터를 필터링할 수 있습니다. 이 기능이 작동하려면 보조 수평 XNUMX차원 배열 행이 필요합니다. 여기서 각 값(TRUE 또는 FALSE)은 테이블의 다음 열을 표시할지 아니면 반대로 숨길지를 결정합니다.
테이블 위에 다음 줄을 추가하고 각 열의 상태를 기록해 보겠습니다.
- 항상 첫 번째 열과 마지막 열(머리글 및 합계)을 표시하려고 한다고 가정해 보겠습니다. 따라서 배열의 첫 번째 및 마지막 셀에 대한 값을 TRUE로 설정합니다.
- 나머지 열의 경우 해당 셀의 내용은 함수를 사용하여 필요한 조건을 확인하는 수식입니다. И (그리고) or OR (OR). 예를 들어, 합계는 300에서 500 사이입니다.
그 후에는 기능을 사용하는 일만 남습니다. FILTER 보조 배열이 TRUE 값을 갖는 열을 선택하려면:
마찬가지로 주어진 목록을 기준으로 열을 필터링할 수 있습니다. 이 경우 기능이 도움이 될 것입니다. 카운티 (카운티프), 허용 목록의 테이블 헤더에서 다음 열 이름의 발생 횟수를 확인합니다.
방법 2. 일반적인 테이블 대신 피벗 테이블
현재 Excel에는 피벗 테이블에서만 열을 기준으로 수평 필터링이 기본 제공되므로 원래 테이블을 피벗 테이블로 변환할 수 있다면 이 기본 제공 기능을 사용할 수 있습니다. 이렇게 하려면 소스 테이블이 다음 조건을 충족해야 합니다.
- 비어 있고 병합된 셀이 없는 "올바른" 한 줄 머리글 줄을 사용하십시오. 그렇지 않으면 피벗 테이블을 작성하는 데 작동하지 않습니다.
- 행과 열의 레이블에 중복을 포함하지 마십시오. 요약에서 고유 값 목록으로 "축소"됩니다.
- 피벗 테이블이 일종의 집계 기능(합계, 평균 등)을 확실히 적용하고 텍스트에서는 작동하지 않기 때문에 값 범위(행과 열의 교차점)에 있는 숫자만 포함합니다.
이러한 모든 조건이 충족되면 원래 테이블처럼 보이는 피벗 테이블을 작성하려면 이 테이블(원래 테이블)을 크로스탭에서 플랫 테이블(정규화됨)로 확장해야 합니다. 그리고 이를 수행하는 가장 쉬운 방법은 2016년부터 Excel에 기본 제공되는 강력한 데이터 변환 도구인 파워 쿼리 추가 기능을 사용하는 것입니다.
이들은 다음과 같습니다
- 테이블을 "스마트" 동적 명령으로 변환해 보겠습니다. 홈 – 테이블 형식 (홈 — 표 형식).
- 명령을 사용하여 파워 쿼리에 로드 데이터 – 테이블/범위에서(데이터 – 테이블/범위에서).
- 합계로 라인을 필터링합니다(요약에는 자체 합계가 있음).
- 첫 번째 열 제목을 마우스 오른쪽 버튼으로 클릭하고 다른 열 축소 (다른 열의 피벗 해제). 선택되지 않은 모든 열은 직원 이름과 지표 값의 두 가지로 변환됩니다.
- 열에 들어간 합계로 열 필터링 속성.
- 다음 명령을 사용하여 결과 플랫(정규화된) 테이블에 따라 피벗 테이블을 만듭니다. 홈 — 닫기 및 로드 — 닫기 및 로드… (홈 — 닫기 및 로드 — 닫기 및 로드…).
이제 피벗 테이블에서 사용 가능한 열을 필터링하는 기능을 사용할 수 있습니다. 이름과 항목 앞의 일반적인 확인 표시 서명 필터 (레이블 필터) or 값으로 필터링 (값 필터):
물론 데이터를 변경할 때 키보드 단축키로 쿼리와 요약을 업데이트해야 합니다. Ctrl 키+다른+F5 또는 팀 데이터 – 모두 새로 고침 (데이터 — 모두 새로 고침).
방법 3. VBA의 매크로
쉽게 볼 수 있듯이 이전의 모든 방법은 정확히 필터링되지 않습니다. 원래 목록에서 열을 숨기지 않고 원래 목록에서 주어진 열 집합으로 새 테이블을 구성합니다. 원본 데이터의 열을 필터링(숨기기)해야 하는 경우 근본적으로 다른 접근 방식, 즉 매크로가 필요합니다.
테이블 헤더의 관리자 이름이 노란색 셀 A4에 지정된 마스크를 충족하는 열을 즉석에서 필터링하려고 한다고 가정합니다. 예를 들어 문자 "A"로 시작하는 경우(즉, "Anna" 및 "Arthur " 결과적으로).
첫 번째 방법과 마찬가지로 먼저 보조 범위 행을 구현합니다. 여기서 각 셀에서 기준은 수식으로 확인되고 논리값 TRUE 또는 FALSE는 표시되는 열과 숨겨진 열에 대해 각각 표시됩니다.
그런 다음 간단한 매크로를 추가해 보겠습니다. 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택하십시오. 출처 (소스 코드). 열리는 창에 다음 VBA 코드를 복사하여 붙여넣습니다.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" then For Each cell In Range("D2:O2") If cell = True then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If 다음 셀 End If End Sub 그 논리는 다음과 같습니다.
- 일반적으로 이것은 이벤트 핸들러입니다. 워크시트_변경즉, 이 매크로는 현재 시트의 모든 셀이 변경될 때 자동으로 실행됩니다.
- 변경된 셀에 대한 참조는 항상 변수에 있습니다. 목표.
- 먼저 사용자가 기준(A4)에 따라 셀을 정확히 변경했는지 확인합니다. 이 작업은 운영자가 수행합니다. if.
- 그러면 사이클이 시작됩니다. 각각… 각 열에 대해 TRUE / FALSE 표시기 값을 사용하여 회색 셀(D2:O2)을 반복합니다.
- 다음 회색 셀의 값이 TRUE(true)이면 열이 숨겨지지 않고, 그렇지 않으면 숨깁니다(속성 숨겨진).
- Office 365의 동적 배열 기능: FILTER, SORT 및 UNIC
- 파워 쿼리를 사용하는 여러 줄 머리글이 있는 피벗 테이블
- 매크로 란 무엇이며 매크로를 만들고 사용하는 방법