Decode a formula someone else wrote

1. 한 문장 요약

현재 행의 거래처코드와 같은 거래처 중 발주일자가 가장 큰 행을 찾아, 그 행의 D열 단가를 반환하며 문제가 생기면 빈 문자열을 반환합니다.

2. 안쪽에서 바깥쪽으로

수식:

=IFERROR(
  INDEX($D$2:$D$500,
    MATCH(
      1,
      ($A$2:$A$500=A2)*
      ($B$2:$B$500=MAX(IF($A$2:$A$500=A2,$B$2:$B$500))),
      0
    )
  ),
  ""
)

• ($A$2:$A$500=A2)

  • 각 행의 거래처코드가 현재 행의 A2와 같은지 검사합니다.
  • 결과는 TRUE/FALSE 배열입니다.

• IF($A$2:$A$500=A2,$B$2:$B$500)

  • 같은 거래처인 행에서는 발주일자를 반환합니다.
  • 다른 거래처인 행에서는 FALSE를 반환합니다.

• MAX(IF(...))

  • 같은 거래처의 발주일자 중 가장 큰 값을 반환합니다.
  • Excel 날짜가 숫자형이라면 가장 최근 날짜에 해당합니다.

• ($B$2:$B$500=MAX(...))

  • 각 행의 발주일자가 해당 거래처의 최댓값과 같은지 검사합니다.

• 두 조건의 곱:

  ($A$2:$A$500=A2)*($B$2:$B$500=최대발주일)
  

  • TRUE*TRUE인 행만 1이 됩니다.
  • 나머지는 0입니다.

• MATCH(1,...,0)

  • 값이 정확히 1인 첫 번째 행을 찾습니다.
  • 따라서 가장 최근 발주일이 여러 건이면 그중 위쪽에 있는 첫 번째 행을 선택합니다.

• INDEX($D$2:$D$500,...)

  • 찾은 상대 행 번호에 해당하는 D열 값을 반환합니다.

• IFERROR(...,"")

  • 일치하는 행이 없거나 계산 오류가 발생하면 빈 문자열을 반환합니다.
  • 따라서 실제 오류와 정상적인 빈 단가를 구분할 수 없습니다.

3. 데이터에 대한 가정

• 정렬되어 있을 필요는 없습니다. MAX가 가장 큰 날짜를 찾습니다.
• B열의 발주일자는 Excel이 날짜로 인식하는 숫자형이어야 합니다.
• 거래처코드는 공백, 숫자, 문자열 등이 서로 일관되게 저장되어 있어야 합니다. 예를 들어 숫자 123과 문자열 "123"은 문제가 될 수 있습니다.
• 같은 거래처의 최신 발주일이 여러 건이면 첫 번째 행만 사용합니다.
• A2가 현재 행의 거래처코드라는 가정을 합니다.
• 데이터가 2행~500행 안에만 있다고 가정합니다.
• 수식이 복사되는 경우 A2는 상대참조라 행에 따라 바뀌지만, A/B/D 범위는 $가 붙은 절대참조라 항상 2:500만 봅니다.
• 500행 이후에 추가된 자료는 반영되지 않습니다. 범위 안에 행을 삽입하면 Excel이 범위를 자동 조정할 수도 있지만, 범위 밖에 데이터를 추가하거나 참조가 조정되지 않는 방식으로 행을 추가하면 누락됩니다.
• 구버전 Excel에서는 이 수식이 배열수식으로 입력되어야 합니다. 실제로 Ctrl+Shift+Enter로 입력되었는지는 수식만으로는 확인할 수 없습니다.

4. 조용히 잘못된 결과를 내는 경우

• 최신 발주일이 여러 건이면 가장 위의 단가를 반환합니다. 실제로는 동일 날짜의 다른 단가가 더 최신 입력이거나 유효한 값이어도 이를 판단하지 않습니다.
• 발주일자가 텍스트로 저장되어 있으면 MAX가 해당 값을 제대로 고려하지 않을 수 있습니다. 그 결과 최신 행을 찾지 못하고 빈칸을 반환하거나 다른 행을 선택할 수 있습니다.
• B열에 빈 날짜가 있으면 빈 셀은 숫자 0처럼 비교될 수 있습니다. 모든 해당 거래처의 날짜가 비어 있거나 날짜가 0인 경우 빈 날짜 행이 선택될 수 있습니다.
• 거래처코드가 빈칸인 행에서는 모든 빈 거래처코드 행이 하나의 거래처로 묶일 수 있습니다.
• 거래처코드에 앞뒤 공백이 있으면 같은 거래처로 보이더라도 서로 다른 값으로 처리됩니다.
• 최신 발주일의 D열 단가가 빈칸이면 빈칸을 반환합니다. 이는 “단가가 없음”, “거래처 없음”, “오류 발생”을 모두 같은 결과로 보이게 합니다.
• 500행 밖의 최신 발주가 있으면 수식은 이를 모른 채 500행 안의 오래된 단가를 반환할 수 있습니다. 이 경우 오류가 발생하지 않아 특히 위험합니다.
• IFERROR가 #N/A, #VALUE! 등의 오류를 모두 빈칸으로 숨깁니다. 따라서 원인 파악이 어렵습니다.
• 이 수식은 “가장 최근 발주일의 D열 값”만 반환합니다. D열이 실제로 유효한 최종단가인지, 취소 발주나 반품 발주를 제외해야 하는지는 수식에서 검증하지 않습니다.

즉, 수식의 실제 동작과 의도는 대체로 일치하지만, 동일 최신일 중 첫 번째 행을 임의로 선택하고, 데이터 범위 밖의 자료를 조용히 무시한다는 차이가 있습니다.

5. 더 단순한 대안

최신 Excel이라면 MAXIFS와 XLOOKUP을 사용할 수 있습니다.

=XLOOKUP(
  1,
  ($A$2:$A$500=A2)*
  ($B$2:$B$500=MAXIFS($B$2:$B$500,$A$2:$A$500,A2)),
  $D$2:$D$500,
  ""
)

배열수식 입력이 필요 없고, 구조가 더 명확합니다. 다만 결과는 여전히 동일 최신일이 여러 건일 때 첫 번째 행을 반환하며, 고정 범위 2:500 문제도 그대로 남습니다. MAXIFS와 XLOOKUP을 지원하지 않는 구버전 Excel에서는 원래 수식을 사용해야 합니다.