Microsoft Excel을 사용한 예산 계획

Question

정기적으로 일일 비용이 입력되는 Excel 워크시트가 있습니다.

그런 다음 각 카테고리에 지출된 금액에 대한 보고서를 추출하기 위해 아래와 같은 피벗 테이블을 사용합니다.

계획된 예산을 이 보고서에 어떻게 통합할지 잘 모르겠습니다. 계획된 예산이 별도의 테이블에 저장되어 있다고 가정해 보겠습니다.

계획에 따라 각 범주에서 지출된 금액을 알 수 있도록 이 계획을 내 보고서에 원활하게 통합하려면 어떻게 해야 합니까? 즉, 다음과 같은 보고서를 얻는 가장 좋은 방법은 무엇입니까(모두 하나의 테이블에 있음):

그러한 보고서를 얻기 위해 피벗 테이블 필드를 조작할 수 있습니까?

Answer 1

vlookup을 사용하여 카테고리 링크로 테이블을 보완하세요.

• 계획된 예산을 참조하는 '상태'라는 새 테이블[시트]을 만듭니다(선택 사항이지만 더 쉬움).

• A1 셀과 같은 첫 번째 열을 만드는 공식은 =Planned!A1A7까지입니다 [더 커질 수 있음])

• 다음을 사용하여 두 번째 열을 만듭니다.
  • B1 = 소비됨
  • B2에서 B7까지의 셀 b2 수식과 같은 것은 입니다 =vlookup(A2;PivotSpent!$A$2:$b$7;false). 일반적으로 vlookup이 작동하려면 피벗 테이블이 범주(첫 번째 열)를 기준으로 정렬되어야 합니다.
• 다음을 사용하여 세 번째 열을 만듭니다.
  • C1 = 계획됨
  • c2부터 C7까지의 공식=Planned!b7
• D열과 E열은 B열을 기준으로 한 계산 기준입니다.
  • D: =b2/C2(% 단위의 셀 형식) + 조건부 서식
  • E: =c2-b2+ 및 조건부 형식
• 아래쪽 셀에 합계 추가

Planed그리고 PivotSpend데이터가 있는 테이블을 포함하는 시트에 부여하는 이름입니다(그러나 이 새 배열을 포함할 시트와 동일할 수도 있고 동일한 시트에 있을 수도 있음). 내부 배열은 A1 셀 이후에 위치한다고 가정합니다(다른 곳에 있는 경우 참조 적용).

피벗 테이블을 사용하여 완전히 동적으로 수행할 수 있지만 두 소스 테이블 모두를 사용하여 임시 데이터 저장 시트를 생성해야 합니다.