KPI 카드와 조건부 강조 표시

KPI 카드는 독자가 가장 먼저 알아야 할 한 가지를 알려 주는 하나의 핵심 수치입니다. 총매출, 오늘의 주문 수, 평균 주문 금액 등이 그 예입니다. 대시보드는 보통 이러한 카드 행으로 시작합니다.

좋은 카드는 명확한 레이블, 수식에서 가져온 큰 수치, 그리고 이전 기간 대비 변화와 같은 작은 비교 정보 등 세 부분으로 구성됩니다.

이 학습에서는 집계 수식으로 KPI 수치를 만들고, 기간 대비 비교를 추가하며, 주의가 필요한 값을 조건 논리로 강조합니다.

핵심 지표부터 시작합니다. 매출 시트의 총매출은 금액 열에 SUM을 적용하면 됩니다.

카드의 값 셀에 다음을 입력합니다.

주문 수에는 주문 ID처럼 항상 값이 있는 열에 COUNTA를 사용합니다. 카드 위에 있는 일반 텍스트 셀인 카드 레이블은 수치의 의미를 나타냅니다. 각 카드에는 수치 하나만 넣어 즉시 읽히게 하십시오.

많은 KPI는 비율입니다. 평균 주문 금액은 총매출을 주문 수로 나눈 값입니다. 두 집계값으로 만들어 보겠습니다.

간결한 방법은 AVERAGE를 직접 사용하는 것입니다.

직접 계산하려면 총합 카드의 값을 개수 카드의 값으로 나누어도 됩니다. 어느 방법이든 이 카드는 일반적인 주문 금액을 보여 주며 데이터가 바뀔 때마다 업데이트됩니다.

KPI는 목표와 함께 볼 때 의미가 더 분명합니다. 월별 매출 목표가 B1에 있다고 가정합니다. 목표보다 얼마나 높거나 낮은지 계산합니다.

차이는 현재 값에서 목표 값을 뺀 값입니다.

=SUM(Sales!C2:C500)/B1을 사용해 목표 대비 백분율로 나타낼 수도 있습니다. 1.12로 표시되면 목표의 112%를 달성했다는 뜻입니다. 비교를 통해 단순한 수치에 의미를 부여할 수 있습니다.

독자는 추세를 확인하기를 좋아합니다. 이전 기간 대비 백분율 변화를 계산합니다. 이번 기간의 총합이 D2에 있고 이전 기간의 총합이 D3에 있다고 가정합니다.

증가율 수식은 다음과 같습니다.

이번 기간이 120이고 이전 기간이 100이면 0.2가 되며, 이는 20% 증가를 의미합니다. 셀에 백분율 서식을 적용합니다. 이처럼 작은 상승 또는 하락 표시 하나만으로도 단순한 카드를 한눈에 추세로 바꿀 수 있습니다.

IF를 사용해 단어나 기호로 방향을 표시할 수 있습니다. 변화량 셀을 읽고 레이블을 선택합니다.

다음 수식은 백분율과 함께 상승 또는 하락 표시를 반환합니다.

E1의 변화량이 양수이면 상승 표시가 나타나고, 그렇지 않으면 하락 표시가 나타납니다. 텍스트와 서식이 지정된 수치를 결합하면 차트 없이도 카드를 간결하고 읽기 쉽게 유지할 수 있습니다.

조건부 서식은 규칙에 따라 셀의 색을 바꾸어 중요한 값이 눈에 띄게 합니다. KPI 셀을 선택하고 조건부 서식을 연 다음 수식으로 작동하는 규칙을 추가합니다.

목표 미만인 카드를 빨간색으로 표시하려면 다음과 같은 수식 규칙을 사용합니다.

규칙이 TRUE인 셀에는 선택한 서식이 적용됩니다. 이렇게 하면 수치를 자세히 읽지 않아도 매출이 목표에 미달하면 대시보드가 빨간색으로, 목표를 초과하면 초록색으로 바뀝니다.

때로는 색상뿐 아니라 셀 자체에 텍스트로 표시하고 싶을 수 있습니다. 비교 연산자와 IF를 사용하면 상태 단어를 만들 수 있습니다.

목표 대비 실적에 레이블을 지정하려면 다음과 같이 합니다.

목표를 달성하면 정상 진행, 미달하면 뒤처짐이 표시됩니다. 이와 같은 상태 열은 단어에 색을 지정하는 조건부 서식과 잘 어울리며, 시각적 신호와 텍스트 신호를 모두 제공합니다.

실제 KPI에는 양호, 주의, 위험처럼 두 가지보다 많은 상태가 있는 경우가 많습니다. IFS 함수는 조건을 순서대로 확인하고 처음 일치하는 결과를 반환하므로 중첩된 IF보다 읽기 쉽습니다.

실적을 세 구간으로 분류하려면 다음과 같이 합니다.

IFS는 위에서 아래 순서로 조건을 확인하므로 가장 엄격한 조건을 먼저 배치합니다. 각 구간에 색상 규칙을 함께 적용하면 카드가 한눈에 상태를 전달합니다.

단일 규칙을 넘어 색상 눈금은 수식 없이 숫자 범위를 색상 변화로 음영 처리하여 가장 높은 값과 가장 낮은 값이 눈에 띄게 합니다. 지역별 합계나 일별 수치 열에 이상적입니다.

범위를 선택하고 조건부 서식을 연 다음 녹색에서 빨간색으로 이어지는 색상 눈금과 같은 옵션을 선택합니다.

• 가장 큰 값은 녹색으로, 가장 작은 값은 빨간색으로 표시되고 중간 값은 그 사이의 색으로 섞여 표시됩니다.
• 데이터가 바뀌면 눈금이 자동으로 다시 계산됩니다.

색상 눈금은 KPI 카드와 잘 어울립니다. 카드는 핵심 수치를 보여 주고, 눈금이 적용된 열은 분포에서 각 값의 위치를 보여 줍니다.

대시보드 상단에 카드를 깔끔한 행으로 배치합니다. 각 카드는 레이블 셀, 값 수식, 그 아래의 비교 정보 또는 상태로 구성된 작은 블록입니다.

• 카드 1: 총매출, =SUM(Sales!C2:C500), 목표 대비 차이 포함
• 카드 2: 주문 수, =COUNTA(Sales!A2:A500)
• 카드 3: 평균 주문 금액, =AVERAGE(Sales!C2:C500), 증가율 포함

조건부 서식이 비교 셀의 색을 지정합니다. 이 행은 독자가 세부 내용으로 스크롤하기 전에 즉시 요약을 제공합니다.

대시보드의 핵심 수치 영역을 만들었습니다.

• SUM, COUNTA, AVERAGE와 같은 집계 수식으로 큰 KPI 수치를 만들었습니다.
• 차이와 증가율 수식으로 목표 및 이전 기간과 비교할 수 있는 맥락을 추가했습니다.
• IF와 IFS로 수치를 정상 진행, 주의, 위험과 같은 상태 단어로 변환했습니다.
• 수식 규칙을 적용한 조건부 서식으로 셀의 색을 자동으로 지정해 중요한 항목을 표시했습니다.

앞선 학습에서 만든 요약 표, 수식 피벗, 대화형 드롭다운과 결합하면 이제 완전한 실시간 수식 기반 대시보드를 만들 수 있습니다.