분석이 절실히 필요한 데이터가 있는데 어떻게 진행해야 할지 막막합니다. 데이터는 세 개의 열에 있습니다. 첫 번째는 날짜입니다. 다양한 이유로 날짜가 순서대로 정렬되지 않았으며 새 데이터가 목록 맨 아래에 추가되었습니다. 또한 두 번째 열에는 제품의 판매 가격이 있고 세 번째 열에는 제품 유형이 있습니다. 예를 들어:
계산은 특정 유형의 가장 최근 가격 7개를 기준으로 해야 합니다. 세 번째 최저 가격과 두 번째 최고 가격은 각각 셀 “A”( F12) 및 “B”( ) 부터 시작하여 표에 들어갑니다 G12.
세 번째 공식은 최근 7개의 가격 중 가장 높은 가격과 가장 낮은 가격을 제거(무시)하고 나머지 5개 가격의 평균을 계산해야 합니다. 이는 셀 “C”( )로 시작하는 테이블의 마지막 열에 표시됩니다 H12.
그런 다음 가로 축에 "유형"이 있고 세로 축에 "가격"이 있는 결합된 막대/선 그래프가 필요합니다. 낮은 범위(셀 “A”)와 높은 범위(셀 “B”)를 표시하는 누적 막대 그래프가 필요하며 선 그래프는 평균(셀 “C”)과 겹쳐집니다.
저는 Excel 마법사가 아니며 이 문제를 해결하기 위해 제가 할 수 있는 모든 자가 진단 튜토리얼을 시도했습니다. 나는 그것이 유용할 것이라고 생각하는 데까지 이르렀 VLOOKUP지만 지금은 헤매고 있습니다.
답변1
좋습니다. Scott이 언급한 특정 날짜에 동일한 유형을 여러 번 판매하는 것에 대한 제한으로 인해 이것이 가능해졌습니다. 하지만 여전히 꽤 복잡합니다. VBA를 사용하는 것이 훨씬 더 간단할 수 있으며 여기 누군가가 절차를 게시할 수도 있습니다.
중간 데이터를 확인하고 모든 것이 올바르게 계산되었는지 다시 확인하는 데 유용하기 때문에 "도우미" 테이블을 사용하여 시작하겠습니다. 그러나 도우미 테이블을 사용하지 않는 수식도 표시하겠습니다. 도우미 테이블은 결과와 함께 아래에 표시됩니다.
나는 임의의 가격과 날짜를 사용했지만 날짜별로 특정 유형의 다중 판매에 대한 위의 제한 사항을 준수했습니다. 이제 F2에 들어가는 공식을 만들어 보겠습니다.
먼저, 각 유형에 대한 가장 최근 가격 7개의 배열이 필요합니다. 일단 사용 가능해지면 해당 배열의 결과 테이블에서 세 가지 측정항목을 쉽게 계산할 수 있습니다.
다음 표현식으로 시작합니다 ($C$2:$C$55=ROW()-1). 이는 F2의 공식의 일부이므로 ROW()-11과 같습니다. 이 표현식은 True/False 값의 배열을 제공하며 Type이 1인 곳은 모두 True이고 그 외의 곳은 False입니다. 채워지면 ROW()증가하므로 다음 행에서는 Type이 2인 True 배열을 제공합니다.
이제 이 배열에 날짜 열을 곱합니다 ($A$2:$A$55)*($C$2:$C$55=ROW()-1). 이는 Type이 1이고 다른 모든 곳에서는 False인 날짜를 포함하는 배열을 제공합니다.
이제 우리는 해당 날짜 중 가장 최근 7개를 원하고 함수를 사용하여 해당 날짜를 얻습니다 LARGE(). 위의 제한으로 인해(각 날짜에 하나의 유형 1 판매만 있음) 이는 유형 1의 가장 최근 판매 7개 날짜를 제공합니다.
LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})
중괄호 안의 일련의 숫자는 LARGE()1번째부터 7번째까지 가장 큰 값을 반환함을 의미합니다.
이제 IF()해당 날짜에 해당하는 가격을 얻기 위해 an을 사용합니다.
IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55)
의 논리 테스트 의 첫 번째 부분은 IF()A열의 날짜가 위의 7개 날짜 중 하나인 배열을 반환하며, 해당 7개 날짜에 다른 유형이 판매되었기 때문에 이 배열에 유형 = 1인 배열을 다시 곱합니다. 따라서 IF()A열의 날짜가 가장 최근 날짜 7개의 배열 중 하나와 같은지 테스트합니다.그리고해당 날짜의 특정 판매가 유형 = 1에 대한 것인지 여부입니다. 두 조건이 모두 true이면 IF()열 B의 판매 가격을 반환하고 그렇지 않으면 False를 반환합니다.
이제 도우미 테이블의 가격을 높은 것부터 낮은 것 순으로 나열하려면 다음을 사용합니다 LARGE().
(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55),COLUMN()-5)
이 수식은 F열에 있으므로 COLUMN()-51과 같으며 수식은 해당 열에 있는 배열의 첫 번째로 큰 값을 반환합니다. 채워지면서 COLUMN()증가하므로 다음 열에서는 두 번째로 큰 값 등을 제공합니다.
마지막으로 수식이 래핑되어 IFERROR()특정 유형의 판매량이 7개 미만인 경우 공백을 반환합니다. 최종 공식:
=IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55),COLUMN()-5),"")
F2에 입력됩니다. 배열 수식이므로 CTRLShiftEnter그냥 가 아닌 , 로 입력해야 합니다 Enter. 올바르게 입력하면 Excel은 수식 표시줄에서 수식을 중괄호 {}로 묶습니다. 입력한 후 F2를 선택하고 수식을 아래로 채운 다음 위의 도우미 테이블을 제공합니다.
이제 결과 테이블을 쉽게 채울 수 있습니다. 이 두 공식
=SMALL(F2:L2,3) and =LARGE(F2:L2,2)
도우미 테이블의 첫 번째 행에서 세 번째로 작은 값과 두 번째로 큰 값을 계산합니다. 그리고 이 공식은
=AVERAGE(IFERROR(LARGE(F2:L2,{2,3,4,5,6}),""))
중간 5개 값의 평균을 계산합니다. 역시 배열 수식이므로 위와 같이 입력해야 합니다. 이 수식을 채우면 결과 테이블이 제공됩니다.
도우미 테이블을 사용하지 않고 결과 테이블을 채우려면 F, G 및 H12에서 다음 세 가지 배열 수식을 사용하고 채우십시오.
=SMALL(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),3)
=LARGE(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),2)
=AVERAGE(IFERROR(LARGE(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),{2,3,4,5,6}),""))
이것이 도움이 되기를 바라며 이 데이터가 있으면 원하는 그래프를 생성할 수 있다고 확신합니다.
어떤 의견이나 제안이라도 환영합니다.