안녕하세요, 나보다 Excel을 더 잘하는 사람들! 당신이 제공할 수 있는 도움에 감사드립니다. 간략하게 설명하겠습니다.
3열 테이블이 있습니다. 월(1,2,3 등…), 영업사원 이름 및 매출. 각 행은 해당 달에 판매원이 얻은 매출 $입니다.
다음과 같이 배열됩니다. 처음 1000개 행은 모두 1월/1이고 다음 1000개 행은 모두 2월/2입니다.
지난 3개월 동안 각 영업사원이 판매한 평균 금액($)을 구해야 합니다. 하지만--마지막 3개월 중 하나가 비어 있다면 여전히 3개월 평균이 필요합니다.
따라서 예를 들어 빈 9월 결과를 인식하고 대신 다음 가장 최근 결과(8월, 7월 등 무엇이든)를 얻으려면 공식이 필요합니다. 따라서 항상 해당 영업 사원의 결과 중 최근 3개월의 평균을 구합니다. 결과가 1~2개가 아닙니다.
지금은 이를 위한 매우 간단한 피벗 테이블이 있습니다. 이 문제를 깨달을 때까지 제 목적에 맞게 잘 작동했습니다. :(
이를 수행할 수 있는 방법(피벗 테이블 여부)이 있습니까? 영업사원이 1000명이 넘으니 수작업으로는 불가능합니다. 어떤 이유로든 한 달 동안 숫자가 없는 사람들이 많기 때문에 이를 계산할 필요가 있습니다.
어떤 도움을 주셔서 감사합니다! 다른 내용을 포함할 수 있는지 또는 명확하지 않은 경우 알려 주시기 바랍니다.
답변1
비어 있지 않은 가장 최근 3개월의 평균을 계산하는 좋은 방법은 모르겠지만 다음은매우그것을하는 해키 방법. 이는 이를 달성하기 위한 더 나은 방법을 촉발할 수 있습니다.
원시 데이터에서 테이블을 만든 다음 월(내림차순) 및 사람별로 정렬합니다.
그런 다음 Sales 열을 필터링하고 공백을 선택 취소합니다. 그런 다음 해당 내용을 다른 영역에 복사하여 붙여넣습니다.
그런 다음 "순위" 열을 만듭니다. 각 사람에 대해 매월 순위를 매기는(가장 최근은 1 등) 셀 D2의 수식입니다.
=IF(B2=B1,D1+1,1)
그런 다음 고유한 사람들(F열)과 최근 3개월 동안의 자리 표시자 열(1, 2, 3으로 표시된 열)이 포함된 또 다른 영역을 만듭니다.
셀 G2 공식:
=SUMIFS($C:$C,$B:$B,$F2,$D:$D,G$1)
열 제목 3 아래로 드래그하세요. 마지막으로 평균 수식을 만듭니다.
답변2
재정렬, 필터링 또는 복사 없이 원본 데이터를 그대로 유지하는 다른 접근 방식을 취하면 다음과 같이 3개의 도우미 열이 데이터 목록에 추가됩니다.
열열1, 열에서 시작하는 간단한 인덱스 시리즈입니다.사람2와 같다사람그러나 IF 함수를 사용하여 0이 있는 행에 널 문자열을 삽입합니다.매상.M개수개수를 제공합니다.사람, 0이 아닌 달매상.
M개수다음과 같이 각 영업사원에 대한 행이 포함된 결과 목록과 상호 작용합니다.
결과 목록에서 열개월단순히 각 영업사원의 데이터 목록에서 매출이 0이 아닌 개월 수를 계산합니다. 셀 I2의 수식은 다음과 같습니다.
=COUNTIFS(A$2:A$21,H2,$C$2:$C$21,">0")
행M1보여줍니다열각 판매원의 매출이 0이 아닌 첫 번째 달에 해당하는 데이터 목록의 값입니다. J2 셀의 수식은 다음과 같습니다.
=MATCH(H2,$E$2:$E$21,0)
열월 1,2개월그리고3개월열 안의 월 숫자는 무엇입니까?M개수(데이터 목록의) 3개월 평균을 계산하는 데 사용된 첫 번째, 두 번째 및 세 번째 달에 해당합니다.매출1,매출2그리고매출33개월 동안의 판매 금액입니다.평균3개월 평균을 계산한 것입니다.
셀 K2, L2 및 M2의 공식은 각각 다음과 같습니다.
=I2-1
=K2+1
=L2+1
N2 셀의 수식은 다음과 같습니다.
=SUMIFS($C$2:$C$21,$E$2:$E$21,$H2,$F$2:$F$21,K2)
그리고 그 값에 의존합니다.M개수데이터 목록의 열(범위 $F$2:$F$21, 다음 참조) 셀 N2가 셀 O2 및 P2에 복사됩니다.
I2:Q2 범위는 결과 목록의 모든 후속 행에 복사될 수 있습니다.
데이터 목록 - 열M개수
F2 셀의 수식은 다음과 같습니다.
=IF(C2>0,1,0)
그러면 셀에 0 또는 1이 입력됩니다. 0은 셀 E2에 있는 사람이 셀에 표시된 달에 매출이 없었음을 나타내고 B2, 1은 이 사람의 첫 번째 달에 매출이 발생했음을 나타냅니다.
F3 셀의 수식은 훨씬 더 복잡하며 다음과 같습니다.
=IF(LEN(E3)=0,0,IF(VLOOKUP(E3,H$2:J$3,3,FALSE)=D3,1,1+MAXIFS(F$2:F2,E$2:E2,E3)))
첫 번째 IF의 TRUE 부분은 행이 다음에 해당하는 경우 단순히 0을 전달합니다.매상제로.
그렇지 않으면,매상0이 아니고 두 번째 IF가 트리거됩니다. 이 두 번째 IF에서는사람E3 셀의 내용이 결과 테이블에서 조회되고, 행M1값(판매 첫 달에 해당하는 데이터 목록의 행 번호)이열D3 셀의 값은 행 3이 셀 E3에서 식별된 사람의 매출이 0이 아닌 첫 번째 행에 해당함을 의미합니다.
만약행M1그리고열값이 다르면 데이터 테이블의 3행은 E3 셀에 있는 사람의 다음 달 판매(첫 번째 달 이후)에 해당합니다. 이 경우 데이터 목록의 이전 행에 있는 E3 셀의 판매원에 대한 F열의 최대값에 1을 더하여 월 수를 얻습니다. MAXIFS 함수는 이 최대값을 결정하는 데 사용됩니다.
F3 셀의 수식은 데이터 목록의 다음 행에 복사됩니다. 수식에서 상대 및 절대 주소 지정을 적절하게 사용하면 MAXIFS 함수가 복사본에서 적절한 셀 범위를 사용하게 됩니다.
노트
- MAXIFS를 사용하려면 Excel 2019 이상이 필요합니다.
- (i) 데이터는 시간순으로 되어 있고 (ii) 각 판매원 및 월 조합에 대해 0이 아닌 판매 데이터 행이 최대 1개 있다고 가정합니다.
- 이미지에서 파란색 글꼴은 공식을 나타내고 검은색 글꼴은 정적 값을 나타냅니다.