☰

엑셀 데이터 쿼리

tag-icon • tag-icon microsoft-excel worksheet-function date
엑셀 데이터 쿼리

예산 모니터링 스프레드시트를 작성하려고 합니다. 카테고리별, 월별 항목 합계를 가져오고 싶습니다. 데이터는 다음과 같은 행으로 표시되며 지출을 다운로드함에 따라 매달 증가합니다. 샘플 데이터는 다음과 같습니다.

Date       Category    Amount
03-15-21   Auto        500.00
02-12-21   House       375.00

궁극적으로 예산을 모니터링할 수 있도록 여러 열과 범주에 걸쳐 월을 표시하게 됩니다.

카테고리별, 월별 데이터를 어떻게 추출하나요?

sumifs, sumproduct, index match를 살펴봤지만 해결책을 찾지 못했습니다. 어떤 지침이라도 감사드립니다.

답변1

피벗 테이블! 전체 테이블을 선택하고 삽입 > 피벗 테이블을 클릭합니다. 피벗 테이블 필드 사이드 바가 나타날 때까지 프롬프트를 따릅니다. 이제 필드를 아래의 올바른 상자로 끌어야 합니다. 당신은 당신이 그것을 좋아하는 방식으로 그것을 얻기 위해 놀 수 있습니다. 하지만 우선,

필터를 비워 두세요. 날짜를 '열' 섹션으로 드래그하세요. 범주를 "행" 섹션으로 드래그합니다. 금액을 '값' 섹션으로 드래그하세요.

Excel이 날짜를 올바르게 인식하면 피벗이 매월 자동 그룹화됩니다. 그렇지 않은 경우 월 헤더가 있는 새 열 B를 삽입하고 수식을 =EOMONTH(A2,0)B2에 추가할 수도 있습니다. 피벗 테이블에서 날짜 대신 월을 사용하세요. 먼저 피벗을 생성한 후 열을 추가하는 경우 피벗 테이블을 새로 고쳐야 합니다. EOMONTH에서 여전히 오류가 발생하면 날짜 형식이 지역 날짜 형식 설정과 일치하는지 확인하세요.

팁: 피벗 테이블과 차트는 Excel 기능의 절대 보석입니다. 처음에는 이해하기가 혼란스러울 수 있지만 일단 이해하면 결코 뒤돌아보지 않을 것입니다. YouTube에서 "피벗 테이블 사용 방법" 비디오를 시청해 보세요.

답변2

피벗 테이블을 직접 사용할 수 있습니다.

여기에 이미지 설명을 입력하세요

답변3

이렇게 하면 문제가 해결됩니다.

여기에 이미지 설명을 입력하세요

작동 방식:

  • 도우미 데이터에 대한 Y34의 공식:

    =TEXT(V34,"mmmm")

  • 고유한 월 이름에 대한 Z34의 배열(CSE) 공식:

    {=IFERROR(INDEX($Y$34:$Y$49,MATCH(0,COUNTIF($Z$33:Z33,$Y$34:$Y$49),0)),"")}

  • 고유 범주 이름에 대한 AA33의 배열(CSE) 공식:

    {=IFERROR(INDEX($W$34:$W$49,MATCH(0,COUNTIF($Z$33:Z33,$W$34:$W$49),0)),"")}

주의

  • 다음으로 배열 수식을 마무리합니다.Ctrl+Shift+Enter.

  • 나중에 깔끔하게 정리하기 위해 Y열의 도우미 데이터를 숨길 수도 있습니다.

  • 셀 AA34의 수식:

    =SUMPRODUCT((TEXT($V$34:$V$49,"MMMM")=$Z34)*($W$34:$W$49=AA$33)*($X$34:$X$49))

범위 전체에 걸쳐 수식을 채웁니다.

필요에 따라 수식의 셀 참조를 조정합니다.

관련 정보