좋아, 수식 문제가 생겼어. 특정 구매자에 대해 나머지가 0보다 큰 모든 PO 라인을 나열해야 합니다.
구매자 대시보드 데이터의 데이터로 구매자 대시보드를 채워야 합니다. 참고: 각 PO에는 순서대로 나열해야 하는 하나 이상의 줄이 있을 수 있으며 두 시트는 모두 하나의 통합 문서에 있습니다.
구매자 대시보드:
구매자의 대시보드 데이터:
내가 시도한 접근 방식은 다음과 같습니다.
=IFERROR(INDEX(Download!$B$2:$B$50000,MATCH(TRUE,$B$3,Download!$C$2:$C$50000>0,0)),"")
=IFERROR(INDEX(Download!$B$2:$B$50000,MATCH($B$3&>0,Download!C2:C50000&DownloadG2:G50000,0)),"")
=IF(ISERROR(SMALL(IF(IF(ISERROR(SEARCH($B$3,$C$2:$C$50000)),FALSE,TRUE),ROW($C$2:$C$50000)),ROW($B$2:$B$50000))),"",INDEX($A$2:$K$50000,SMALL(IF(IF(ISERROR(SEARCH($B$3,$C$2:$C$50000)),FALSE,TRUE),ROW($$C$2:$C$50000)),ROW($B$2:B50000)),3))
=IF(ISERROR(INDEX(Download!A2:K50000,SMALL(IF(Download!C2:C50000=$B$3,ROW(Download!B2:B50000)),ROW(1:1))-1,3)),"",INDEX(Download!A2:K50000,SMALL(IF(Download!C2:C50000=$B$3,ROW(Download!B2:B50000)),ROW(1:1))-1,3))
어떤 아이디어라도????
답변1
Excel에서 동적 목록을 만드는 한 가지 접근 방식은 다음과 같습니다.
기준이 있는 행 번호 배열이 있다고 가정합니다 TRUE(예: 구매자 코드 = JDH 및 나머지 > 0). 그런 다음 SMALL()채워질 때 행 번호를 나열하는 함수 에 해당 배열을 공급할 수 있습니다 . 마지막으로 함수 에서 SMALL()the 를 사용하면 매개변수 로 지정한 모든 열의 적절한 값이 나열됩니다 .row_numsINDEX()column_num
그럼 행 번호의 배열을 가져오는 것부터 시작해 보겠습니다. 단순화를 위해 데이터 테이블에 행이 25개만 있고 대시보드가 데이터와 동일한 시트에 있다고 가정하겠습니다. 또한 셀 B30에 있는 구매자 코드 ELN을 사용하고 있습니다.
TRUE/FALSE먼저 기준에 해당하는 값 배열을 계산합니다 .
($C$1:$C$25=$B$30)*($G$1:$G$25>0)
첫 번째 표현식은 구매자 코드(C열) = ELN인 값의 배열을 제공하고 TRUE/FALSE, 두 번째 표현식은 남은(G열)이 0보다 큰 값의 배열을 제공합니다 TRUE/FALSE. 논리 값과 관련된 산술 연산에서 TRUE은 FALSE1과 0으로 변환됩니다. , 따라서 이 곱셈은 기준이 둘 다인 위치에 1이 있는 1과 0의 배열을 제공합니다 TRUE.
이제 이 배열을 기준이 있는 행 번호 목록으로 변환해야 합니다 TRUE. 연속된 숫자의 배열을 1과 0의 배열로 나누면 1이 있는 곳마다 행 번호 배열을 얻게 되고, #DIV/0!0이 있는 곳마다 오류가 산재해 있는 배열을 얻게 됩니다.
ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0))
다음으로 대신 오류를 무시하는 옵션(옵션=6)이 있다는 점을 제외하고는 동일한 작업 (함수=15)을 SMALL()수행하는 을 사용합니다 . 또한 를 사용하여 입력하지 않고도 배열을 처리할 수 있습니다 .AGGREGATE()SMALL()AGGREGATE()CTRLShiftEnter
따라서 아래 수식에서는 오류를 AGGREGATE()무시하고 #DIV/0!기준과 일치하는 행 번호만 포함하는 배열을 제공합니다.
AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33)
34행에 이 수식이 있으므로 "-33"을 사용하면 함수가 1에서 시작하고 채워지는 대로 행 번호가 나열됩니다. 상황에 맞게 이 매개변수를 변경하십시오.
이제 우리는 이것을 INDEX()에서 사용할 수 있고 채워질 때 공백을 삽입하기 위해 전체를 래핑할 수 IFERROR()있습니다 . 이 공식은 B34에서 바로 채워집니다.INDEX()row_nums
=IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH(B$32,$A$1:$G$1,0)),"")
아래에 표시된 결과를 제공합니다.
구매자 ELN의 경우 행 5와 11에만 남은 금액이 0이고 수식에는 이를 제외한 모든 행이 나열됩니다.
를 사용하여 MATCH()를 제공하는 대시보드 헤더를 에서 참조했습니다 . 수식이 채워지면 PO 번호 및 설명에 적용되지만 대시보드의 다른 제목은 데이터 테이블의 제목과 동일하지 않습니다.column_num'sB$32lookup_value
따라서 다른 열의 경우 셀 참조를 B$32인용된 텍스트로 바꿨습니다. PO 라인 열은 "LINE_NBR", 주문 수량 열은 "QUANTITY", 수령 수량 열은 "REMAINING"으로 대체했습니다. 이 마지막 열에는 계산이 포함되므로 공식은 실제로 다음과 같습니다.
=E34-IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH("REMAINING",$A$1:$G$1,0)),"")
마지막 열인 "필요"는 다음과 같이 계산됩니다.
=E34-F34, 그리고 채워졌습니다.
이것이 도움이 되기를 바랍니다. 행운을 빕니다.