MS Excel - 제품 및 월(연간)로 분류된 차트를 생성하기 위해 데이터를 모델링하는 방법은 무엇입니까?

Question

내 원시 데이터에 하나의 날짜 열이 있습니다. 날짜 범위는 2018년과 2018년입니다. 여러 제품이 있고 각 행은 판매 거래를 나타냅니다. 상품당 거래가 여러 개 있습니다.

피벗 테이블에 2018년 날짜와 2019년 날짜에 대한 두 개의 열을 포함하려면 어떻게 해야 합니까? 이것이 가능한가?

제가 이 작업을 수행하려는 이유는 2018년과 2019년 날짜 값이 나란히 있는 피벗 막대 차트를 만들 수 있기 때문입니다. 예. 2018년 1월, 2019년 1월, 2018년 2월, 2019년 2월 등의 판매 수량. 이를 수행하는 가장 좋은 방법은 무엇입니까?

추신. 수천 개의 행에 해당하는 데이터가 있습니다.

또한, 위와 같이 날짜별로 여러 상품의 판매 수량을 표시하고 싶습니다. 2018년 1월 vs 2019년 1월, 2018년 2월 vs 2019년 2월 등. 이것을 피벗 막대 차트에 추가하려면 어떻게 해야 합니까? 내가 가지고 있는 데이터를 가장 잘 재구성하는 방법에 대한 귀하의 조언은 크게 감사하겠습니다.

내 원시 데이터

내가 원하는 차트의 모습. 단, 각 제품에 대한 분석을 원하므로 연간 추세를 확인할 수 있습니다.

내 차트가 어떻게 보이길 원하는지에 대한 예를 온라인에서 찾았으므로 내 피벗도 다음과 같아야 한다고 가정합니다.

Answer 1

저는 Excel을 사용할 준비가 되어 있지 않기 때문에 LibreOffice Calc에서 기본적인 내용을 대략적으로 설명했습니다.

날짜 및 수량 열은 귀하의 예에서 나온 것입니다. 원하는 대로 데이터를 구성하는 간단한 방법으로 몇 가지 도우미 열을 추가했습니다.

월 열은 날짜에서 월 번호를 추출합니다. C2:

=MONTH(A2)

연도 열은 날짜에서 연도를 추출합니다. D2:

=YEAR(A2)

월을 적절한 순서로 차트로 표시하려면 월 번호를 사용하면 됩니다. 월 이름은 알파벳 순서로 차트에 표시되므로 피벗 테이블은 대신 월 번호를 기반으로 합니다. 그러나 여전히 월 이름을 차트에 레이블로 지정하려고 합니다.

월 이름을 얻는 방법에는 몇 가지가 있습니다. E열에는 하나의 접근 방식이 포함되어 있습니다(다른 접근 방식을 사용하는 경우에는 필요하지 않음). E2:

=TEXT(A2,"mmm")

이는 월 약어만 표시하도록 날짜 형식을 지정하는 것입니다. 피벗 테이블 결과를 차트로 만들 때 G열에 표시된 피벗 테이블의 월 번호와 월 이름을 연결하려고 합니다. 이를 위해 E열을 사용하려면 G3에 다음이 포함됩니다.

=VLOOKUP(H3,$C$2:$E$7,3)

이는 값을 조회하는 방법 중 하나일 뿐입니다. VLOOKUP은 데이터의 월 열에 있는 피벗 테이블에서 월 번호를 찾고 첫 번째 일치 항목에 대해 열 E에서 월 이름을 가져옵니다.

또 다른 접근 방식에는 열 E가 필요하지 않습니다. 단지 월 번호만 변환합니다.

=CHOOSE(H3,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

CHOOSE는 월 번호를 인덱스로 사용하여 목록에서 월 이름을 선택합니다. 월 번호 "N"은 목록에서 N번째 이름을 선택합니다.

피벗 테이블의 경우 Qty, Month 및 Year 열을 소스 데이터로 선택합니다. 피벗 테이블 대화 상자에서 연도를 열 창으로, 월을 행 창으로, 수량을 값 창으로 끌어옵니다(집계 방법이 합계로 설정되어 있는지 확인). 그러면 차트 작성을 위해 구성된 데이터가 제공됩니다.

오랜만에 엑셀로 차트를 작성해봤습니다. 내 기억에는 데이터 범위를 정의할 때 축 레이블의 월 이름을 선택할 수 있다는 것입니다. LO Calc는 그렇게 하지 않습니다. 샘플 차트의 경우 축 레이블을 생략하고 축 제목을 추가한 다음 제목에 월 이름을 넣고 모든 항목을 정렬하기 위해 간격을 두었습니다.