엑셀 히스토그램 만들기, 구간 설정과 FREQUENCY

엑셀 히스토그램은 숫자 열 하나를 선택하고 삽입 > 통계 차트 삽입 > 히스토그램을 누르면 만들어집니다. 값을 구간으로 나누고, 구간마다 몇 개가 들어 있는지를 막대로 보여 주는 차트입니다. "주문부터 출고까지 평균 2일"이라는 숫자는 괜찮아 보여도, 대부분 하루 만에 나가고 일부 주문이 열흘 넘게 묶여 있다면 고객이 겪는 일은 전혀 다릅니다. 평균이 가리는 이런 꼬리를 보여 주는 것이 히스토그램입니다. 세 가지 만드는 방법과 구간을 정하는 기준, 납기 리드타임 분포를 끝까지 따라가는 예시까지 정리했습니다.

히스토그램과 막대 그래프의 차이

막대 그래프는 거래처, 품목처럼 이름이 있는 항목을 비교합니다. 히스토그램은 리드타임, 주문 금액, 불량 수처럼 연속된 숫자를 구간으로 나눠 분포를 봅니다. 그래서 히스토그램의 막대는 서로 붙어 있고, 막대 순서를 바꿀 수 없습니다. 모양을 읽는 법은 간단합니다. 가운데가 높고 양옆이 낮으면 값이 평균 주변에 모여 있는 것이고, 오른쪽으로 길게 꼬리가 늘어지면 소수의 큰 값이 평균을 끌어올리고 있는 것입니다. 봉우리가 두 개면 성격이 다른 두 집단(채널, 창고, 운송사)이 섞여 있을 가능성이 높습니다.

방법 1: 통계 차트로 바로 만들기 (Excel 2016 이상)

1. 숫자가 든 열 하나를 머리글과 함께 선택합니다.
2. 삽입 > 통계 차트 삽입 > 히스토그램을 누릅니다. 같은 메뉴에 파레토(정렬된 히스토그램)와 상자 수염 그림도 있습니다.
3. 엑셀이 구간을 자동으로 정해 차트를 그립니다.

자동 구간은 출발점일 뿐입니다. 가로축을 더블 클릭하면 축 서식 > 축 옵션에 구간 설정이 나옵니다.

• 구간 너비: 한 막대가 담는 값의 폭. 리드타임이면 1일, 금액이면 10만 원처럼 업무 단위에 맞춥니다.
• 구간 수: 막대 개수를 직접 정합니다. 너비는 엑셀이 계산합니다.
• 오버플로 구간: 이 값보다 큰 값을 마지막 막대 하나로 묶습니다. "10일 초과"처럼 꼬리를 한 칸에 모을 때 씁니다.
• 언더플로 구간: 이 값 이하를 첫 막대 하나로 묶습니다.

통계 차트 히스토그램의 가로축 레이블은 [36, 46], (46, 56]처럼 표시됩니다. 대괄호는 그 값을 포함하고 소괄호는 포함하지 않는다는 뜻이라, 첫 구간을 빼면 각 구간은 아래 경계를 빼고 위 경계를 포함합니다. 경계값 46은 첫 구간에 들어갑니다. 원본 데이터를 고치면 차트가 바로 다시 그려지는 것이 이 방법의 장점이고, 구간별 개수를 셀에 숫자로 남기지 않는다는 것이 단점입니다. 숫자 표가 필요하면 방법 2나 3을 씁니다.

같은 메뉴의 파레토 차트는 히스토그램과 쓰임이 다릅니다. 파레토는 불량 유형, 클레임 사유, 결품 품목처럼 이름이 있는 항목을 개수가 많은 순서로 세우고 누적 비율 선을 겹쳐, "상위 몇 개 원인이 전체의 80%를 차지하는가"를 보여 줍니다. 숫자 구간의 분포를 볼 때는 히스토그램, 원인별 비중을 볼 때는 파레토를 고릅니다. 상자 수염 그림은 여러 그룹의 중앙값과 퍼짐을 한 차트에 비교할 때 씁니다.

방법 2: 분석 도구로 빈도표와 차트 만들기

분석 도구는 기본으로 꺼져 있는 추가 기능입니다. 파일 > 옵션 > 추가 기능에서 아래쪽 관리가 Excel 추가 기능인 상태로 이동을 누르고 분석 도구에 체크합니다. Mac은 도구 > Excel 추가 기능에서 켭니다. 켜고 나면 데이터 탭 오른쪽에 데이터 분석이 생깁니다.

먼저 다른 열에 구간의 위쪽 경계값을 차례로 적어 둡니다(예: 0, 1, 2 … 9). 데이터 > 데이터 분석 > 히스토그램에서 입력 범위에 값 열을, 계급 구간에 경계값 열을 넣고, 출력 위치를 고른 뒤 차트 출력을 체크합니다. 누적 백분율을 체크하면 구간마다 누적 비율이 함께 나와 "2일 안에 몇 %가 출고되는가"를 바로 읽을 수 있습니다.

결과 표의 각 구간은 그 경계값 이하이면서 앞 경계값보다 큰 값의 개수이고, 마지막 경계값보다 큰 값은 기타 행에 모입니다. 주의할 점은 분석 도구의 결과가 수식이 아니라 값이라는 것입니다. 원본이 바뀌어도 표와 차트는 그대로라, 매달 갱신하는 보고서라면 방법 3이 맞습니다.

방법 3: FREQUENCY 함수로 빈도표 만들기

FREQUENCY(값범위, 구간범위)는 구간별 개수를 배열로 돌려줍니다. Microsoft의 FREQUENCY 설명에 따르면 돌려주는 개수는 구간 수보다 하나 많고, 그 마지막 칸은 가장 높은 구간보다 큰 값의 개수입니다. 빈 셀과 텍스트는 세지 않습니다. 구간이 9개면 결과는 10칸이 필요합니다.

Microsoft 365와 Excel 2021에서는 첫 셀에 =FREQUENCY(B2:B51,D6:D13)만 입력하면 결과가 아래로 자동으로 펼쳐집니다. 이전 버전에서는 결과가 들어갈 칸(구간 수 + 1)을 먼저 선택하고 수식을 입력한 뒤 Ctrl + Shift + Enter로 배열 수식으로 넣습니다. 구간 값은 각 구간의 위쪽 경계이고, 그 값 자체가 해당 구간에 포함됩니다.

빈도표가 생기면 경계값 대신 "30~39점"처럼 읽기 쉬운 레이블 열을 만들고, 레이블과 개수를 선택해 묶은 세로 막대 차트를 넣습니다. 막대를 우클릭해 데이터 계열 서식 > 간격 너비를 0~5%로 줄이면 막대가 붙어 히스토그램 모양이 됩니다. 구간별 개수가 수식이라 원본이 바뀌면 차트도 따라 바뀝니다. 레이블을 직접 정해야 하는 경우 COUNTIFS(범위,">=30",범위,"<40")처럼 구간마다 조건을 거는 방법도 있지만, 경계를 한 칸씩 잘못 적기 쉬우니 구간이 많으면 FREQUENCY가 안전합니다.

예시: 세진물산 3분기 주문 출고 리드타임

세진물산 물류팀장은 "평균 2일 안에 출고한다"는 영업팀 안내와 달리 납기 문의가 계속 들어오는 이유를 확인하기로 했습니다. 3분기 주문 1,849건의 주문일과 출고일을 한 시트로 뽑았습니다.

1. 정리: 출고일이 비어 있는 37건(아직 출고 전)은 따로 목록으로 빼고, 텍스트로 저장된 날짜 열은 데이터 > 텍스트 나누기로 날짜로 바꿨습니다. 남은 주문은 1,812건입니다.
2. 리드타임 계산: 보조 열에 =출고일-주문일을 넣고 표시 형식을 일반으로 바꿨습니다. 결과가 음수인 행이 2건 나와, 출고일이 잘못 입력된 것을 고쳤습니다.
3. 구간 정하기: 최소값 0, 최대값 23이었습니다. 하루 단위로 보려고 경계값 0부터 9까지 10개를 적고, 그보다 큰 값은 FREQUENCY의 마지막 칸이 자동으로 "10일 이상"으로 모으게 했습니다.
4. 빈도표와 차트: 0일 380건, 1일 700건, 2일 470건, 3~9일 213건, 10일 이상 49건이 나왔습니다. 막대 차트로 그리고 간격 너비를 0%로 줄였습니다.
5. 요약 숫자: =AVERAGE로 평균 1.8일, =MEDIAN으로 중앙값 1일을 구하고, 2일 이내 출고 비율 =COUNTIF(리드타임,"<=2")/COUNT(리드타임)은 85.5%였습니다.

평균 1.8일은 영업팀 안내와 맞지만, 차트에는 3일 이상 걸린 262건(14.5%)이 오른쪽 꼬리로 길게 보였습니다. 10일 이상 49건을 필터로 뽑아 보니 대부분 특정 수입 원료가 들어가는 품목이었고, 재고가 떨어진 날 들어온 주문이 다음 입고까지 기다린 것이었습니다. 팀장은 해당 품목의 안전재고를 올리고, 영업팀 안내 문구를 "주문의 85%는 2일 안에 출고"로 바꿨습니다. 다음 분기에는 같은 시트에 주문만 바꿔 넣고 10일 이상 막대가 줄었는지부터 확인하기로 했습니다.

👉 엑셀 표준편차 구하기, STDEV.S와 STDEV.P 차이

히스토그램과 함께 적어 둘 숫자

히스토그램은 모양을 보여 주지만, 보고서에는 그 모양을 요약한 숫자 몇 개가 함께 있어야 읽는 사람이 비교할 수 있습니다.

• 중앙값 =MEDIAN(범위): 값을 순서대로 세웠을 때 가운데 값입니다. 꼬리가 긴 분포에서는 평균보다 중앙값이 "보통 주문"을 더 잘 보여 줍니다.
• 백분위수 =PERCENTILE.INC(범위,0.9): 90%의 값이 이 값 이하라는 뜻입니다. "주문의 90%는 3일 안에 출고"처럼 고객에게 약속할 수 있는 숫자가 됩니다.
• 최대값과 꼬리 건수 =MAX(범위), =COUNTIF(범위,">=10"): 가장 늦은 주문과 오래 걸린 주문 수입니다. 꼬리가 줄어드는지 매달 추적하기 좋습니다.
• 표준편차 =STDEV.S(범위): 값이 평균에서 얼마나 퍼져 있는지입니다. 두 창고나 두 운송사의 안정성을 비교할 때 씁니다.

예시의 세진물산이라면 중앙값 1일, 90번째 백분위수, 10일 이상 건수 세 숫자를 차트 제목 아래에 적어 두면, 다음 분기에 안전재고 조정의 효과가 꼬리 건수로 바로 드러납니다.

구간 설정에서 자주 하는 실수

1. 구간이 겹치거나 비어 있습니다. COUNTIFS로 직접 나눌 때 "50 이상 60 이하"와 "60 이상 70 이하"처럼 잡으면 60이 두 번 셉니다. 한쪽 경계만 포함하는 "이상, 미만"으로 통일합니다.
2. 최대값이 빠집니다. 마지막 구간 경계가 최대값보다 작으면 큰 값이 사라집니다. FREQUENCY는 마지막 칸에, 통계 차트는 오버플로 구간에 모이는지 확인합니다.
3. 구간이 너무 넓거나 좁습니다. 너무 넓으면 꼬리가 첫 막대에 묻히고, 너무 좁으면 막대가 들쭉날쭉해 모양이 안 보입니다. 하루, 1만 원처럼 업무에서 쓰는 단위에서 출발해 막대가 7~15개 정도 되게 조정합니다.
4. 구간 폭이 들쭉날쭉합니다. 0~1일, 2~5일, 6~30일처럼 폭이 다르면 넓은 구간의 막대가 실제보다 커 보입니다. 같은 폭을 쓰고 꼬리만 오버플로로 묶습니다.
5. 값이 텍스트입니다. 텍스트 숫자와 날짜는 세지 않으니 숫자로 바꾼 뒤 만듭니다.

버전별 차이

통계 차트의 히스토그램, 파레토, 상자 수염 그림은 Excel 2016부터 Windows와 Mac에 들어왔고, Microsoft의 차트 종류 목록에도 히스토그램과 파레토가 별도 종류로 올라 있습니다. Excel 2013 이하는 분석 도구나 FREQUENCY로 만듭니다. FREQUENCY가 수식 하나로 펼쳐지는 동적 배열은 Microsoft 365와 Excel 2021부터이고, 그 전 버전은 배열 수식으로 입력합니다. 분석 도구는 웹용 엑셀에서 쓸 수 없으니, 웹에서는 통계 차트나 FREQUENCY를 씁니다.

엑셀 히스토그램 자주 묻는 질문 (FAQ)

Q. 히스토그램 메뉴가 보이지 않습니다.

Excel 2013 이하에는 통계 차트가 없습니다. 분석 도구를 켜서 데이터 분석 > 히스토그램을 쓰거나 FREQUENCY로 빈도표를 만든 뒤 막대 차트로 그립니다.

Q. 자동 구간이 23.5, 47.0처럼 애매하게 나옵니다.

축 서식 > 구간 너비에 업무 단위(1, 10, 100)를 직접 넣으면 경계가 깔끔한 숫자로 바뀝니다. 첫 구간 시작점을 맞추려면 언더플로 구간 값을 함께 지정합니다.

Q. 날짜 데이터로 히스토그램을 만들 수 있나요?

날짜 자체보다 두 날짜의 차이(일수)를 계산한 열로 만듭니다. 리드타임, 재고 체류 일수, 미수금 경과 일수가 대표적입니다.

Q. 그룹별 분포를 비교하려면요?

채널별로 FREQUENCY를 따로 계산해 같은 구간 기준으로 나란히 놓고 묶은 막대 차트로 그리거나, 상자 수염 그림으로 중앙값과 퍼짐을 한 차트에 비교합니다.

inline AI로 분포 분석 맡기기

히스토그램은 만드는 것보다 날짜 정리, 구간 설계, 튀는 구간의 원인 확인에 시간이 더 듭니다. inline AI는 열어 둔 주문 파일에서 리드타임을 계산하고 구간을 나눠 히스토그램과 요약 숫자를 만든 뒤, 10일 이상 걸린 주문을 품목과 거래처별로 묶어 보여 줍니다. 주문과 출고가 ARP에 있으면 분기마다 데이터를 뽑아 정리하는 단계 없이 바로 분포를 볼 수 있습니다.