Auto-Format Excel Groupby 및 Pivotby는 조건부 형식으로 결과입니다

Excel의 Groupby 및 Pivotby 공식을 사용하여 데이터를 명확하고 화려한 요약 보고서로 전환하는 방법을 알아보십시오.

Excel의 동적 배열 기능에 최근 두 가지 추가 된 두 가지 추가 사항 인 Groupby와 Pivotby에 대해 들었을 것입니다. 이러한 기능은 데이터를 그룹화하고 요약 할 때 탁월하지만, 결과는 종종 형식 부족으로 인해 명백하고 이해하기 어려운 것처럼 보입니다. 더 나은 가독성과 프리젠 테이션을 위해, 당신은 물론 헤더, 하위 운동 및 총계와 같은 주요 요소를 수동으로 포맷 할 수 있습니다. 그러나 결과 배열은 동적이므로 이러한 요소는 원래 데이터 세트의 변경 사항을 수용하기 위해 움직일 수 있습니다. 당연히 데이터가 변경 될 때마다 데이터를 재구성하고 싶지 않을 것입니다. 다행히도 설정 기준에 따라 동적으로 업데이트되는 조건부 형식 으로이 프로세스를 자동화 할 수 있습니다.

• Groupby 및 Pivotby 공식으로 조건부 서식을 사용하는 방법
• 조건부 형식 GroupBy 결과
• 조건부로 pivotby 결과를 형성합니다

Groupby 및 Pivotby 결과에 조건부 서식을 적용하는 방법

GroupBy 또는 Pivotby 공식의 결과를 자동으로 포맷하려면 공식을 기반으로 몇 가지 조건부 형식 규칙을 설정해야합니다. 단계는 헤더, 하위 토탈 또는 총계를 강조하든 모든 규칙에 대해 보편적입니다. 변형은 각 요소와 서식 세부 사항을 식별하는 데 사용되는 특정 공식에 있습니다. 최적의 결과를 보장하기 위해이 권장 사항을 자세히 따르십시오.

1. 형식으로 범위를 선택하십시오 . Groupby 또는 Pivotby 공식에서 반환 한 모든 결과를 선택하여 시작하십시오. 원래 데이터 세트가 변경 될 수 있고 향후 추가를 예상하는 경우 아래에 추가 빈 행을 포함하도록 선택을 확장하십시오. 이러한 방식으로 더 많은 데이터가 추가되면 조건부 서식이 자동으로 적용됩니다.
2. 조건부 서식에 액세스합니다 . 홈 탭에서 조건부 서식 > 새 규칙을 클릭하십시오…
3. 공식 기반 규칙을 만듭니다 . 새 서식 규칙 창에서 공식을 사용하여 포맷 할 셀을 결정하십시오 . 그런 다음 해당 상자에 공식을 입력하십시오.
4. 형식을 선택하십시오 . 형식 버튼을 누르면 서식 옵션을 엽니 다. 여기서는 규칙에 따라 적용될 원하는 형식을 선택할 수 있습니다.
5. 규칙을 저장하십시오 . 필요한만큼 확인을 클릭하여 설정을 확인하여 새로운 조건부 서식 규칙을 저장하고 적용합니다.

자세한 지침은 공식으로 Excel 조건부 서식 규칙을 설정하는 방법을 참조하십시오.

조건부 서식이있는 Excel Groupby 공식

이 예제는 헤더 행, 하위 토탈 및 총계와 같은 주요 요소를 강조하여 GroupBY 기능의 출력을보다 읽기 쉽게 만드는 방법을 보여줍니다.

조건부 서식에 적용된 범위를 선택하십시오

이 예에서 GroupBy Formula는 F3에서 H16에 이르는 동적 배열을 반환합니다. 그러나 조건부 서식 규칙의 경우 F3 ~ H32 범위를 선택합니다. 여기에는 향후 데이터 추가를 수용 할 수있는 추가 빈 행이 포함되어 새 데이터가 소스 테이블에 추가 될 때 서식이 자동으로 적용되도록합니다.

적절한 범위를 선택하면 조건부 형식 > 새 규칙…을 클릭하여 첫 번째 규칙을 설정하십시오.

이제 우리가 사용할 수있는 공식 및 서식 옵션을 정확히 살펴 보겠습니다.

헤더 행을 포맷하십시오

GroupBy 결과의 헤더 행을 포맷하는 것은 나머지 데이터와 구별하는 데 필수적입니다.

헤더 행을 식별하려면 데이터 세트의 다른 곳에 표시되지 않는 제목에 고유 한 특정 단어 나 문구를 검색하는 공식을 사용할 수 있습니다. 두 가지 방법은 다음과 같습니다.

정확히 일치 공식

헤더에 고유 한 일관된 용어가 포함 된 경우이 메소드를 사용하십시오. 예를 들어, "프로젝트 유형"이 반환 된 배열의 첫 번째 헤더 인 경우, 열에서 "프로젝트 유형"을 찾아서 식별 할 수 있습니다.

=$F3="Project type"

부분 경기 공식

헤더가 다를 수있는 경우 (예 : "Project Type"또는 "Project Name") 검색 기능을 사용하여 부분 일치.

=SEARCH("project", $F3)

검색 기능이 대소 문자를 사용하지 않으므로 문자 케이스에 텍스트를 입력 할 수 있습니다.

메모. 어떤 공식을 선택하든 혼합 세포 참조 (예 : $ F3)를 사용해야합니다. 이렇게하면 열이 잠그고 행 번호가 조정되어 전체 행에 서식이 일관되게 적용되도록합니다.

서식

공식을 설정하면 헤더에 대담한 형식을 적용하십시오. 형식 셀 대화 상자에서 글꼴 탭으로 이동하여 글꼴 스타일 에서 굵게 선택하십시오.

또한 헤더 행 아래에 컬러 라인을 추가 할 수 있습니다. 이를 위해, 원하는 라인 스타일과 색상을 선택하여 하단 셀 테두리에 적용하는 테두리 탭으로 이동하십시오.

이 간단하면서도 효과적인 형식 선택 선택은 Groupby 결과의 가독성과 전문적인 외관을 크게 향상시킵니다.

하위 감소

Groupby 결과 내에서 하위 토탈의 가시성을 향상 시키면 데이터 분석이보다 직관적이고 액세스 할 수 있습니다. 이를 달성하려면 소계 행을 정확하게 식별해야합니다. 일반적으로 이러한 행에는 1 단계 그룹화 열의 값 (이 예제의 프로젝트 유형 )과 집계 열 (예 : 수익 )의 값이 포함되며, 보조 그룹화 열의 셀 (예 : 상태 )은 비어 있습니다.

공식

적절한 공식을 구축하려면 두 조건을 확인하는 기능을 활용할 수 있습니다. 열 F ( 프로젝트 유형 )이 비어 있지 않아 g ( 상태 )가 비어 있는지 여부는 다음과 같습니다.

=AND($F3"", $G3="")

위의 공식은 두 조건 모두를 충족하기 때문에 그랜드 총 행을 형식화 할 것입니다. 그랜드 총계를 제외하면서 하위 토탈을 강조 표시하기위한 공식을 개선하려면 논리적 인 테스트를 하나 더 추가 할 수 있습니다.

=AND($F3"", $G3="", $F3"grand total")

서식

이 식별 된 행에 배경색을 적용하려면 형식 셀 대화 상자의 채우기 탭으로 이동하십시오. 거기에서 선호하는 색상을 선택할 수 있으며,이 색상은 소계 행에 적용됩니다.

선택적으로, 글꼴 탭 에서이 옵션을 선택하여 Bold Iatera의 하위 토탈 텍스트를 형식화 할 수도 있습니다.

그랜드 총 행을 형식화하십시오

Grand Total에주의를 기울이는 것은 데이터의 주요 요약을 즉시 눈에 띄게 만드는 데 중요합니다.

공식

그랜드 총 행을 식별하려면 헤더 행에 사용 된 것과 유사한 공식을 만들지 만 이번에는 텍스트 "Grand Total"을 검색하십시오. 공식은 대소 문자를 사용하지 않으므로 어떤 경우에도 텍스트를 입력 할 수 있습니다.

=$F3="grand total"

Excel 조건부 형식에서, 셀 참조는 적용된 범위의 왼쪽 상단 셀과 관련이 있습니다. 따라서 우리는 혼합 기준 - 절대 열과 상대 행을 사용하여 셀 $ F3에 대한 공식을 다시 작성합니다.

서식

Grand Total Row를 더 잘 구별하기 위해 우리는 글꼴과 셀 테두리의 두 가지 요소를 변경할 것입니다.

• 그랜드 총 텍스트를 강조하려면 글꼴 탭으로 이동하여 글꼴 스타일 에서 굵게 선택하십시오.
• 그랜드 총 행 위에 줄을 추가하려면 테두리 탭으로 전환하십시오. 여기에서 선호하는 선 스타일과 색상을 선택한 다음 상단 테두리를 적용하여 나머지 데이터와 명확하게 분리됩니다.

조건부 포맷 된 GroupBy 결과

조건부 형식으로 향상된 Groupby 공식의 결과는 잘 조직되어있을뿐만 아니라 탐색하기에 미적으로 유쾌하고 직관적입니다.

메모. 워크 시트에서 GroupBy 출력을 서식 할 때는 특정 데이터 세트의 레이아웃과 일치하도록 조건부 형식 형식의 참조를 올바르게 조정해야합니다. 자세한 내용은 Excel 조건부 서식의 셀 참조를 참조하십시오.

조건부 서식이있는 Excel Pivotby 공식

아시다시피, Excel Pivottables는 자동으로 포맷되어 헤더, 총 및 하위 토탈을 강조 표시합니다. 이 예를 들어, 우리는 그 그룹을 그룹화하고 동일한 방식으로 소스 데이터를 집계하는 피봇 공식과 피벗 테이블을 만들었습니다. 그리고 이제 조건부 형식을 사용하여 피벗 테이블의 형식을 복제하여 Pivotby 공식 출력에 대한 유사한 모양을 달성하려고합니다.

조건부 서식 규칙을 만들기 전에 Pivotby 공식의 결과를 선택하십시오. 선택적으로, 아래에 몇 개의 빈 셀을 포함하여 나중에 추가 될 수있는 새로운 데이터를 자동으로 포맷하십시오. 이 경우 범위 G3 : M32를 선택한 다음 조건부 형식 > 새 규칙… 을 클릭하여 헤더 행의 규칙을 만들기 시작합니다.

헤더 행을 강조 표시하십시오

첫 번째 단계는 헤더 행을 돋보이게하는 것입니다.

공식

Pivotby 공식에 의해 반환 된 동적 배열에서 헤더 행의 첫 두 셀은 비워집니다. 다음 공식을 사용 하여이 두 가지 조건을 확인할 수 있습니다.

=AND($G3="", $H3="")

서식이 전체 행에 올바르게 적용하려면 열을 잠그고 $ G3 및 $ H3과 같은 혼합 셀 참조를 사용하여 행 번호를 조정하도록하십시오.

서식

헤더가 시각적으로 팝 되려면 세 가지 형식 요소를 적용하는 것을 고려하십시오.

• 굵은 텍스트 형식 . 이것은 헤더 텍스트를 두드러집니다.
• 아래 줄을 추가하십시오 . 선택한 색상으로 하단 셀 테두리를 형식으로하여 명확한 분리를 만듭니다.
• 하이라이트 . 헤더 행의 채우기 색상을 시각적으로 구별하도록 설정하십시오.

하위 감소

Pivotby Formula가 하위 토탈을 표시하도록 구성된 경우 더 나은 가시성 및 조직을 위해이를 형식화하는 것이 합리적입니다.

공식

소계 행을 식별하는 공식은 g의 셀이 비워지지 않았고 h의 셀이 비어 있는지 확인합니다. 또한, 다르게 포맷 해야하는 Grand Total Row가 아닌지 확인합니다.

=AND($G3"", $H3="", $G3"grand total")

서식

하위 토탈의 서식을 위해서는 스타일의 조합을 적용 할 수 있습니다.

• 대담한 텍스트 . 하위 텍스트에 대담한 형식을 적용하여 더 잘 보이게하십시오.
• 아래 줄 . 하위 줄 아래 줄을 추가하여 다음 데이터와 시각적으로 분리하십시오.

그랜드 총 행을 형식화하십시오

Grand Total Row는 데이터의 주요 요약이므로 빠른 참조를 위해 눈에 띄게 강조 표시되어야합니다.

공식

그랜드 총 행을 식별하려면 셀 에이 텍스트가 포함되어 있는지 확인하십시오.

=$G3="grand total"

그랜드 총계는 반환 배열의 마지막 행에 있지만, 공식은 적용 범위에서 상단 왼쪽 셀을 참조합니다.

서식

Grand Total Row를 강조하려면 다음 형식을 적용하십시오.

• 대담한 텍스트 . 그랜드 총 텍스트를 대담하게하여 그 중요성을 강조하십시오.
• 위의 줄 . 위의 데이터와 분리하기 위해 그랜드 총 행 위에 줄을 추가하십시오.
• 배경색 . 그랜드 총 행에 독특한 채우기 색상을 적용하여 눈에 띄게하십시오.

조건부 포맷 된 피봇 비 결과

조건부 서식을 적용하면 피봇 공식의 출력은 시각적으로 피벗 테이블과 비교하여 정말 좋고 유익합니다.

기사를 마무리 할 때이 조건부 서식 기술을 사용하여 Groupby와 Pivotby 공식이 설득력있는 이야기를하고 놀랍게 보이게 할 준비가 되었기를 바랍니다.

다운로드를위한 연습 통합 문서

조건부 서식 (.xlsx 파일)이있는 Groupby 및 Pivotby

위 내용은 Auto-Format Excel Groupby 및 Pivotby는 조건부 형식으로 결과입니다의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!