내가 보유하고 있는 데이터 : 거래일자(A:A), 고객명(B:B), 판매주문번호(C:C), 제품명(D:D), 단위(E:E), 매출(F:F)
신규 주문은 고객이 지난 6개월 동안 또는 한 번도 주문하지 않은 모든 것을 의미합니다.
재주문은 고객이 지난 6개월 동안 해당 특정 제품을 구매한 경우입니다.
이 논리를 Excel 수식에 넣는 방법에 대해 머리를 감쌀 수 없습니다.
답변1
아마도 내가 뭔가를 오해하고 있는 것 같지만, 이것은 매우 간단한 것 같습니다. 질문에 대한 제가 이해한 바는 현재 행 B과 동일한 고객 이름(열), 동일한 제품 이름(열 D) 및 거래 날짜( 열 A)은 현재 거래일로부터 지난 6개월 이내입니다. 열 C및 무시할 수 있습니다 E. F나는 행이 거래 날짜를 기준으로 정렬되어 있다고 가정합니다(물론 그렇게 가정할 필요는 없을 것 같습니다).
거래 날짜 기준은 "가장 어렵다"(저는 이 용어를 느슨하게 사용합니다). 과거 날짜는 지난 6개월 이내입니다 A2.
> EDATE(A2,-6)
따라서 세 가지 기준을 충족하는 현재 행을 통해 행을 계산하려면 다음을 사용합니다.
=COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)
표기법 A$2:A2이 흥미롭습니다. 행 2에서 시작하여 현재 행에서 끝나는 범위를 나타냅니다. 즉, 현재 행을 포함한 모든 것입니다. 현재 행이 중요하므로 이 개수는 항상 1 이상입니다. 1보다 크면 일치하는 이전 행이 하나 이상 있는 것입니다. 그래서 대답은 입력하는 것입니다
=IF(COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)<=1, "New Order", "Reorder")
( H2또는 원하는 곳 어디든) 드래그/채우기.
행의 순서가 잘못된 경우 전체 테이블을 검색하여 날짜가 현재 날짜보다 작은지 테스트해야 합니다.
=IF(COUNTIFS(A$2:A$99, ">" & EDATE(A2,-6), A$2:A$99, "<" & A2,
B$2:B$99, B2, D$2:D$99, D2)=0, "New Order", "Reorder")
99여기서는 데이터의 마지막 행을 나타내는 데 사용됩니다 . 테스트가 현재 행을 제거하기 때문에 테스트를 에서 로 변경했습니다 <=1. 데이터에 고객 이름, 제품 이름, 거래 날짜가 동일한 여러 행이 포함될 수 있는 경우 처리 방법을 지정하세요.=0< A2
답변2
아, 이거 마음에 드실 거예요.
최종 공식은 꽤 복잡할 것이기 때문에 단계별로 나누어 보겠습니다.
설정
지정하신 형식으로 워크시트를 만들었습니다. 우리가 해야 할 일을 정리하기 위해 세 개의 열을 추가했습니다. 이에 대해서는 아래에서 개별적으로 설명하겠습니다. 이 첫 번째 스크린샷에서는 맨 위 행을 고정하고 약간 아래로 스크롤했습니다.
1단계: 검색할 범위 설정
먼저 6개월 전이 언제였는지(그리고 해당 날짜가 스프레드시트의 어디에 있는지) 알아야 합니다. 각 기록에서 우리는 EDATE알아내는 데 사용할 것입니다.
셀 내 G2:
VLOOKUP(EDATE(A2,-6),A:A,1,TRUE)
이것은 훌륭합니다. 이는 우리가 6개월 이상 전에 보유하고 있는 마지막 판매 기록을 제공합니다. 판매가 드물게 발생하는 경우(예: 판매 기록 간의 큰 격차), 제가 작성한 방식은 문제의 품목에 대한 마지막 주문이 6개월이었다면 오탐지를 '잡을' 수 있음을 의미합니다. 그리고 일주일 전 그리고 6개월에서 일주일 전, 그리고 정확히 6주 전에는 주문이 전혀 없었습니다. 나는 이것이 당신에게 '충분히 가깝다'고 결정했습니다.
유일한 다른 결함은 시트 상단 근처에 보기 흉해 보이는 오류가 있다는 것입니다. 왜냐하면 첫 번째 레코드(예: 행 2)에는 6개월 전 레코드가 전혀 없기 때문입니다. 그럼 다음으로 마무리해 보겠습니다 IFERROR.
=IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2)
즉, 6개월 이전의 가장 최근 주문이 언제였는지 알려주십시오. 단, 주문이 없는 경우에는 기록에 있는 첫 번째 주문을 알려주십시오.
이 수식은 아래로 복사됩니다. 여기 있습니다:
2단계: 범위 시작 데이터를 셀 위치로 변환
셀 내 H2:
=MATCH(G2,A:A,0)
이건 쉽습니다. 열의 어느 순서 위치에서 A1단계에서 설정한 6개월 전 날짜를 찾을 수 있나요?
이 수식은 아래로 복사됩니다. 여기 있습니다:
3단계: 수행
앞으로 건너뛰고 이것을 , 어때요 I21, 에 넣어서 어떻게 작동하는지 살펴보겠습니다.
=INDEX(INDIRECT("A"&H21&":D"&ROW()-1),MATCH(D21,INDIRECT("D"&H21&":D"&ROW()-1),0),1)
이는 기본적으로 단순한 INDEX MATCH검색일 뿐입니다. 하지만 우리가 한 일은 해당 함수 배열의 셀 참조를 INDIRECT방금 열에 시드한 값에 대한 참조 로 바꾸는 것입니다 H.
내 샘플 데이터에서 행 21은 날짜가 10/1/2018. 열은 G해당 날짜로부터 6개월 이상 이전의 가장 최근 판매 기록을 찾습니다. 내 샘플 날짜에서는 이 기록이 입니다 4/1/2018. 열은 8번째 행 H열에 해당 날짜의 (첫 번째) 위치를 고정합니다 . A따라서 의 값으로 식별되는 INDIRECT("A"&H21&의 행에서 검색을 위한 배열을 시작하라는 의미입니다 . 멋진! 그리고 의 나머지 절반은 현재 우리가 있는 레코드보다 한 행 위 의 행에서 배열을 끝내라고 말합니다 .AHINDIRECT":D"&ROW()-1)D
즉, 다음과 같이 쓸 수 있습니다.
=INDEX(A1:D999,MATCH(D21,D1:D9999,0),1)
D전체 판매 원장 에서 귀하의 제품 코드가 처음으로 나타나는 것을 찾으려고 하지만 우리는 그렇게 하고 싶지 않습니다. 따라서 셀 참조를 이전 두 단계를 사용하여 생성한 동적 범위로 바꿉니다. 나랑?
그래서 그것은 다음과 같습니다:
IFERROR해당 수식( 외관을 위해 다른 수식으로 포장)을 행까지 복사 2하고 다시 아래로 복사하면 다음과 같은 결과를 얻을 수 있습니다.
첫 번째 행에 거짓양성이 있습니다. 나도 그걸로 살거야.
4단계: 결합
I따라서 참조 의 공식 H은 G. 역확장은 다음과 같은 최종 공식을 얻습니다.
=IFERROR(INDEX(INDIRECT("A"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),MATCH(D2,INDIRECT("D"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),0),1),"NEW ORDER")
그리고 다음과 같습니다:
따라서 원하는 경우 해당 수식을 행의 '재정렬' 열에 복사한 2다음 복사하면 됩니다.
주의.
- 출력 필드에 Format Painter를 사용하게 됩니다. 왜냐하면 작성된 수식은 텍스트 날짜를
A날짜 일련 번호로 처리하고 일련 번호도 반환하기 때문입니다. - 1단계의 약점에 주목하세요.
helper13단계 필드의 스크린샷에서 샘플 데이터에서 10월 1일의 6개월 전 날짜는 4월 1일이고 10월의 6개월 전 날짜는 6개월 전 날짜라는 것을 알 수 있습니다. /5는 이전또한4/1, 4/1부터 4/5까지 주문이 전혀 없었기 때문이죠. 이로 인해 잠재적으로 오탐지가 발생할 수 있습니다. - 다시 한 번, 3단계에서 설명한 것처럼 이 방법을 사용하면 첫 번째 판매 기록에 대해 거짓 긍정이 발생하게 됩니다.
답변3
OP의 스레드를 선택했기 때문에 문제를 해결하기 위한 접근 방식이 약간 다릅니다.
신규 주문은 고객이 지난 6개월 동안 또는 한 번도 주문하지 않은 모든 것을 의미합니다.
재주문은 고객이 지난 6개월 동안 해당 특정 제품을 구매한 경우입니다.
- Cell의 배열(CSE) 수식은
H41다음으로 마무리됩니다.Ctrl+Shift+Enter.
{=IFERROR(LOOKUP(DATEDIF(IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match"),I41,"m"),{0,6,12},{"New Order","Order before 6 month","Order before 12 months"}),"Cust's. New Pro. Order")}
상황 1:
새로운 견인 날짜: 03/26/19.
고객 이름: Bob.
제품 이름: Cake.
주문 상태: Order before 12 months.
상황 2:
새로운 견인 날짜: 03/26/19.
고객 이름: Bob.
제품 이름: Milk.
주문 상태: New Order.
주의
기존 거래일( 10/01/18)과 신규 거래일( 03/26/19)의 차이가 6개월 미만이기 때문입니다.
상황 3:
새로운 견인 날짜: 03/26/19.
고객 이름: Bob.
제품 이름: Wheat.
주문 상태: Order before 6 months.
상황 4:
새로운 견인 날짜: 03/26/19.
고객 이름: Bob.
제품 이름: Fruit.
주문 상태: Cust's. New Pro.Order.
메모:
새 고객 이름과 기존 또는 새 제품 및 날짜를 입력하면 Cust's . New Pro. Order상태가 표시됩니다.
이제 공식이 어떻게 작동하는지 설명하겠습니다.
공식은 두 부분으로 나눌 수 있습니다.
Part 1
{=IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match")}
Old Transaction Date기본적으로 for 를 찾는 2개 기준 조회 Customer & the Product이며 공식은 이를 Start Datefor DATEDIF공식이 .. 내에 있는 것으로 간주합니다 A41:A47.
Part 2
원본 DATEDIF은,
{=LOOKUP(DATEDIF(A41:A47,I41,"m"),{0,6,12},{"New order","Order before 6 month","Order before 12 months"})}
는 수식 A41:A47으로 바뀌었고 은 셀에 있습니다 .Part 1Start DateEnd DateI41
그리고 두 부분 모두 IFERROR.
주의
- 필요에 따라 셀 참조를 조정할 수 있습니다.
- 공식이 포함된 메시지도 원하는 대로 변경할 수 있습니다.
답변4
다음 수식을 사용할 수 있습니다.
=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )
간단한 "range=" 테스트라는 구식 기술을 사용하여 각 항목을 알려주는 배열을 생성합니다(A열 범위에 있는 셀에 고객 이름이 있습니까? D열 범위에 제품 이름이 있습니까? 오늘 주문으로부터 183일 이내의 범위를 열에 입력한 다음 이를 곱하여 최종 배열을 얻습니다.
원시 배열 값은 TRUE/FALSE 값이지만 이를 곱하면 Excel에서 값이 잘 곱해지는 1/0 값으로 변경됩니다. 최종 결과는 위의 세 가지 조건이 모두 충족되는 경우 1이고 그렇지 않은 경우 0인 배열입니다. Excel에서는 배열 요소를 TREU/FALSE 값으로 다시 변환하지 않으므로 전체 배열이 숫자입니다.
SUM그런 다음 이를 모두 하나의 값으로 합산합니다. 결과가 0이 아닌 경우 지난 183일 동안 동일한 주문이 하나 이상 존재한 것입니다. 0이면 그러한 것이 존재하지 않습니다. 단순히 IF결과가 무엇인지 확인하고 "새로 만들기" 또는 "재주문"을 알려줍니다.
실제로 관심을 끄는 유일한 점은 범위 주소 지정에서 절대성과 상대성의 혼합입니다. 모든 범위 시작점은 완전히 절대적이므로 각 범위의 왼쪽 상단은 고정되어 움직이지 않습니다. 범위 끝점은 행을 추가할 때 범위가 커지도록 참조 열을 절대화하지만 현재 행은 포함하지 않습니다. 따라서 맨 위에서 시작하여 현재 행 위의 한 행에서 끝납니다.
처음부터 명확하지 않으면 방금 입력한 주문의 행 G열 셀에 나타나는 정보를 가정합니다. 그러나 동일한 행이나 동일한 시트가 아닌 다른 위치에 있을 수 있으며 조건부 서식 테스트를 수행하여 행 셀 색상을 변경하여 "새로 만들기/재주문"을 표시하는 데 사용할 수 있습니다.