カンマで区切られた一連の数字があります。各セットは、プロジェクトに関連する一連の一意の数字を表します。リスト内の任意の数の項目を参照シートで検索し、ハードコードされた値を返す数式を作成するにはどうすればよいでしょうか。
以下に各シートの例（匿名化）を示します（表1-5）と意図した/期待される出力。SUM、FREQUENCY、INDEX、またはその他の配列数式と関係があると思いますが、わかりません。私の質問を参照してください。ここもありますが、ほぼ同じ情報があります。
私は「ポお尻" "ふail"と"ま「issing」ですが、何でも大丈夫です。
論理：
数式では次の手順を実行する必要があります。
- C列の各セルから数字のリストを抽出します。表4。
- 検索表3（列D）でリスト内の数字のインスタンスを探します。2b
. リスト内の数字が表示された場合は、監査が失敗したかどうかを確認します（列Bに「失敗」というテキストで示されます）。表32c .
あれば、印刷するふ2d .
そうでなければ印刷するポ。 - リストに数字がない場合は印刷するま。
表:
表1
この表はメインの参照シートです。名前、サーバー、プロジェクトに対するすべての UID がリストされます。
Unique ID Name Server Address Project
10000 Company 1 Server 1 Project 1
10001 Company 2 Server 2 Project 2
10002 Company 3 Server 3 Project 3
10003 Company 3 Server 3 Project 4
10004 Company 3 Server 3 Project 5
10005 Company 3 Server 3 Project 6
10006 Company 4 Server 4 Project 7
10007 Company 4 Server 4 Project 7
10008 Company 4 Server 4 Project 7
10009 Company 5 Server 5 Project 8
10010 Company 6 Server 6 Project 9
10011 Company 7 Server 7 Project 10
10012 Company 8 Server 8 Project 11
10013 Company 8 Server 8 Project 11
10014 Company 8 Server 8 Project 11
10015 Company 8 Server 8 Project 11
10016 Company 8 Server 8 Project 11
10017 Company 8 Server 8 Project 11
10018 Company 8 Server 8 Project 11
10019 Company 8 Server 8 Project 11
10020 Company 8 Server 8 Project 11
10021 Company 9 Server 9 Project 12
10022 Company 10 Server 10 Project 13
10023 Company 11 Server 10 Project 14
10024 Company 12 Server 10 Project 15
10025 Company 13 Server 10 Project 16
10026 Company 14 Server 10 Project 17
10027 Company 15 Server 10 Project 18
10028 Company 16 Server 10 Project 19
10029 Company 17 Server 10 Project 20
10030 Company 18 Server 10 Project 21
10032 Company 19 Server 10 Project 23
10033 Company 19 Server 10 Project 24
表2
この表には、表1ただし、各 UID をそれぞれのプロジェクトにグループ化します。現在実際には使用していませんが、役立つかもしれません。役に立たない場合は削除します。
Company Trading Name Project UID UID UID UID
Company 1 Project 1 10000
Company 2 Project 2 10001
-----------------------------------------------------
Company 4 Project 7 10006 10007 10008
表3
このテーブルには監査結果が貼り付けられます。監査結果はこのレイアウトに正確に従っており、監査が不合格の場合はメモの列に「不合格」という単語が表示され（条件付き書式で現在強調表示されています）、UIDは単純なLEFT関数によって抽出されます。名前と会社の列では、UIDに対してVLOOKUPが使用されます。表1名前/サーバーを取得します。
Notes Time Rec. UID Name Server
Audit for company passed:10001 auto 10001 Company 2 Server 2
Audit for company passed:10006 auto 10006 Company 4 Server 4
Audit for company failed:10007 auto 10007 Company 4 Server 4
表4
これは監査シートです。数式は列 D に入力され、出力は各日の値としてコピー/貼り付けされます。ここでは、その後の外観の例をいくつか記入しました。
Server Name Project (UID) Today's Date
Server 1 Company 1 Project 1 (10000) P
Server 2 Company 2 Project 2 (10001) N
Server 3 Company 3 Project 3 (10002,10003,10004,10005) F
Server 4 Company 4 Project 4 (10006,10007,10008) P
-----------------------------------------------------
Server 10 Company 10 Project 13 (10022) P
Server 10 Company 11 Project 14 (10023) M
Server 10 Company 12 Project 15 (10024) P
Server 10 Company 13 Project 16 (10025) P
Server 10 Company 15 Project 18 (10027) F
Server 10 Company 16 Project 19 (10028) P
Server 10 Company 17 Project 20 (10029) M
Server 10 Company 18 Project 21 (10030) P
Server 10 Company 19 Project 22 (100310) P
Server 10 Company 19 Project 23 (10032) P
Server 10 Company 19 Project 24 (10033) M
あるいは5番目のテーブルがあります。表5、不足しているIDを自動的に検索します表3次の式を使用します。
=INDEX(Reference!$A$2:$A$160, SMALL(IF(ISERROR(MATCH(Reference!$A$2:$A$160, Check!$D$2:$D$350, 0)), (ROW(Reference!$A$2:$A$160)-MIN(ROW(Reference!$A$2:$A$160))+1), ""), ROWS($A$1:A1)))
いつ表3空の場合（毎日クリアされます）、すべてのプロジェクトがリストされます。任意の数の監査を貼り付けると、表3しかし、リストには表5どれが不足しているかを表示するように更新されます。おそらく、これを最終的な数式に組み込む方法があるかもしれません。
ご希望の方がいらっしゃれば、匿名化されたバージョンもございます。
ありがとう。
編集1: 誰かがシステムの詳細を要求しました。これは、Excel 365 を使用している Windows 10 環境です。
答え1
私は、あまりスケーラブルではない、いくつかのヘルパー列を使用するアプローチをまとめようとしました。カンマで区切られた値を一度に検索する直接的な方法があるかどうかはよくわかりません。可能かもしれませんが、私は Excel の専門家ではありません。
さらに、Office 365 デスクトップ バージョンは VBA マクロをサポートしていると思います。デスクトップ バージョンをお持ちの場合は、より合理化された VBA マクロ ソリューションを選択できます。ただし、オンライン バージョンではマクロはサポートされていないと思います。
下のスナップショットをご覧ください。
ヘルパー列は 5 つあります。H、M、N、O、P。H はオプションですが、サーバー名の最後の文字が Server 1、Server 2 のように数字であるとは限らないため、単純な数字シーケンスを使用することをお勧めします。
M4 に次の数式を入力して下にドラッグします。
=SUBSTITUTE(RIGHT(K4,LEN(K4)-FIND("(",K4)),")","")
これにより、適切なカンマ区切りのUIDリストが作成されます。
N4 に次の数式を入力し、暫定的なセル数まで下にドラッグします。表示するものがない場合はセルの内容は空白になりますが、UID の数は変化する可能性があるため、後でプロビジョニングできるように下に拡張します。
=VALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,$M$4:$M$22),",",REPT(" ",99)),(ROW(1:1)-1)*99+1,99)))
O4に次の数式を入力して下にドラッグします。
=IF(ISNUMBER(VALUE(N4)),MATCH("*"&N4&"*",$M$4:$M$22,0),"")
これは、後で使用される UID の位置をリストします。
P4に次の数式を入力して下にドラッグします。
=IF(ISNUMBER(VALUE(N4)),IF(ISNUMBER(FIND("passed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"P",IF(ISNUMBER(FIND("failed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"F","M")),"")
これにより、表 4 の各 UID のステータスが決定されます。
さて、私の例では出力列 L です。
L4に次の数式を入力して下にドラッグします。
=IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"M",IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"P")+COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"P","F"))
これにより、ヘルパー列の転置されたリストに基づいて監査ステータスの合格、不合格、または欠落を決定する最終ロジックが適用されます。
これらのデータ セットを Excel テーブルに変換し、Table[Header] 参照を使用してより動的にすることができます。
試してみて、これが役に立つかどうか判断してください。いくつかのヘルパー列を使用するので、それほど優れているわけではないことは承知しています。アイデアは、コンマ区切りのリストを連続したセルに転置し、ルックアップを適用することです。