☰

월별 및 지출 카테고리별로 값 할당

tag-icon • tag-icon microsoft-excel
월별 및 지출 카테고리별로 값 할당

다음 첫 번째 테이블이 데이터로 있습니다. 두 번째 표와 같이 결과를 얻을 수 있는 방법이 있습니까?

스크린샷

시작월과 종료월은 mm-yyyy로 표시됩니다.

계산을 명확히 하려면:

  • 두 번째 테이블에는 이벤트 간격에 포함된 각 월에 대한 열이 있습니다.
  • 각 이벤트 비용은 해당 이벤트의 달에 균등하게 분배됩니다.

  • 이 예에는 둘 이상의 이벤트에 사용되는 비용 범주가 표시되지 않지만 그런 일이 발생할 수 있습니다. 이 경우 특정 비용 범주에 대한 비용은 매월 집계됩니다.

    예를 들어, 두 이벤트 모두 광고 비용이 있었고 이벤트 A의 비용은 600달러라고 가정해 보겠습니다. 이벤트 A의 월 할당량은 3개월 이벤트이므로 $200입니다. 따라서 표 2의 4월 광고 금액은 이벤트 A의 경우 $200, 이벤트 B의 경우 $500, 즉 $700입니다.

답변1

솔직히 말하면, 첫 번째 표는 사람이 읽기에는 적합할 수 있지만 컴퓨터가 계산하거나 통계를 수행하기에는 적합하지 않습니다. 테이블 2를 얻으려면 이상적으로는 간단합니다. 즉, 분할된 테이블을 얻는 것입니다.매월 행사별 항목별 비용(그리고 이를 일부 도우미 셀에 저장한 다음) 그에 따라 요약합니다. 분할된 비용을 얻으려면 해당 월이 시간 범위에 속하는 경우 먼저 플래그를 가져와야 합니다.

내 생각엔 12개월이 필요할 것 같아. 또한 시작 월에 대한 셀의 날짜가 해당 월의 1일(예: 2017-04-01 B2, 04/2017)이라고 가정하고 그에 따라 종료 월의 셀에 해당 월의 마지막 날짜(예: 2017-04-01)가 있다고 가정합니다. 2017년 6월 06~30일 C2).

  1. 깃발: H1to를 S1매월 날짜로 사용합니다(예: 2017년 1월 1일(2017-01-01), 2017년 2월(2017-02-01), ..., 2017년 12월(2017-12-01)), 왼쪽을 사용합니다. 해당 월이 범위 내에 있는지 여부를 플래그로 H표시할 열의 셀입니다 . S에서는 H2수식을 사용합니다.=AND($B2<=H$1,H$1<=$C2) .주의 사항: 다른 사용법에 유의하세요.$주의 사항: 쉽게 복사할 수 있도록 셀에 대한 절대 참조를 포함하려면 수식에서 의현재 달(현재 열의 맨 위 행)이 시작 달과 끝 달 사이에 있으면 true를 반환하고, 그렇지 않으면 false를 반환한다는 의미입니다. 그런 다음 이 수식을 열 오른쪽까지 복사 S하고 이벤트의 마지막 부분까지 복사하세요.

  2. 세다: T비용을 분할할 월의 카운터로 열을 사용합니다. 의 수식은 T2입니다 =COUNTIF(H2:S2,TRUE). 수식을 아래로 복사하세요.

  3. 각 범주에 대한 분할 비용:$다시 한 번, 다음 수식의 사용법을 참고하세요 .

    3.1 1차 카테고리: 열을 첫 번째 범주(예: "광고")에 대한 분할 비용으로 사용합니다 U( AF12개 열의 첫 번째 그룹). U2is 의 공식은 =IF(H2,$E2/$T2,0)해당 월의 플래그가 true인 경우(즉, 해당 월이 범위 내에 있음) 분할된 비용(adv. 비용/월 수)을 반환하고, 그렇지 않으면 0을 반환함을 의미합니다. 그런 다음 오른쪽(열까지 AF)을 복사하고 아래로 복사합니다.

    3.2 두 번째 카테고리: 열을 두 번째 범주(예: "박람회 및 세미나")에 대한 분할 비용으로 사용합니다 AG( AR12개 열의 두 번째 그룹). 의 공식은 AG2입니다 =IF(H2,$F2/$T2,0). 그런 다음 오른쪽(까지 AR)을 복사하고 아래로 복사합니다.

    3.3 3번째 카테고리: 열을 세 번째 범주(예: "숙박")에 대한 분할 비용으로 사용합니다 AS( BD12개 열의 세 번째 그룹). 의 공식은 AS2입니다 =IF(H2,$G2/$T2,0). 그런 다음 오른쪽(까지 BD)을 복사하고 아래로 복사합니다.

  4. 요약해 보세요: 표 2에서는 매월 모든 행사에 대한 해당 비용을 합산합니다.

관련 정보