さて、数式の問題があります。特定の購入者の、残余がゼロより大きいすべての PO 明細をリストする必要があります。
バイヤーのダッシュボードに、バイヤーのダッシュボード データからデータを入力する必要があります。注: 各 PO には、順番にリストする必要がある 1 つ以上の行がある場合があり、両方のシートが 1 つのワークブックに含まれています。
購入者ダッシュボード:
購入者のダッシュボードデータ:
私が試したアプローチは次のとおりです。
=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 で動的なリストを作成する 1 つの方法を紹介します。
条件が満たされる行番号の配列があるとしますTRUE(つまり、購入者コード = JDH かつ残り > 0)。次に、その配列を関数に渡すと、配列SMALL()が満たされるにつれて行番号が一覧表示されます。最後に、関数内でSMALL()を として使用して、パラメータで指定した列の適切な値を一覧表示できます。row_numsINDEX()column_num
それでは、まず行番号の配列を取得しましょう。簡単にするために、データ テーブルには 25 行しかなく、ダッシュボードはデータと同じシートにあると仮定します。また、セル B30 にあるバイヤー コード ELN を使用します。
TRUE/FALSEまず、基準に対応する値の配列を計算します。
($C$1:$C$25=$B$30)*($G$1:$G$25>0)
最初の式は、購入者コード (列 C) = ELN となる値の配列を返しTRUE/FALSE、2 番目の式は、TRUE/FALSE残り (列 G) が > 0 となる値の配列を返します。論理値を含む算術演算では、TRUEと はFALSE1 と 0 に変換されるため、この乗算では、条件が両方とも となる位置に 1 が入った 1 と 0 の配列が返されますTRUE。
ここで、この配列を、条件に該当する行番号のリストに変換する必要があります。連続した数字の配列をこの 1 と 0 の配列で割ると、1 がある行番号の配列が得られ、 0 があるTRUE行番号にはエラーが混在します。#DIV/0!
ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0))
次に、 の代わりにSMALL()を使用します。AGGREGATE()これは、 (function=15) と同じことを行いますSMALL()が、エラーを無視するオプション (option=6) があります。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()提供することで のダッシュボード ヘッダーをcolumn_num's参照したことに注意してください。 数式が 全体に入力されると、これは PO 番号と説明には適用されますが、ダッシュボードの他の見出しはデータ テーブルの見出しと同じではありません。B$32lookup_value
そのため、他の列については、セル参照をB$32引用符付きのテキストに置き換えました。発注明細列の場合は「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、そして埋め尽くされました。
これが役に立つことを祈ります。幸運を祈ります。