スプレッドシートに 2 つのリストがあります。両方のリストは 2 つの列で構成されています。1 つの列はコンピューターのシリアル番号で、もう 1 つの列は最初の列のコンピューターが割り当てられている人物です。
リストは 2 人の異なる人によって作成されたため、それらを比較して矛盾点を見つける必要があります。この問題の例を次に示します
。
この例では、データセット A とデータセット B の両方で人物 A に CPU1 が割り当てられていることを確認する必要があります。矛盾がある場合は、データセット B が優先されます。ただし、データセット A には存在するがデータセット B には存在しない人物がいる場合、その人物はリストに残る必要があります。
答え1
したがって、リスト A とリスト B を比較して、必要な場合はリスト B と一致するように変更し、そうでない場合はリスト A のデータを使用する必要があります。また、発生する問題についても知りたいと思うでしょう。
最初の部分に関しては、リスト A に新しい列が必要です。既存の 2 つの列の間に挿入すると、その列がすぐにスプレッドシート内の任意の範囲またはその他の参照の一部になります。次に、右に押し出された列を切り取って、その列の後ろに残すのではなく、その前に挿入します。こうすると、元の資料に戻り、そのすぐ右側に新しい列が追加されますが、新しい列はリスト A の資料への参照の一部になります。
2 番目については、関心があると思われるイベントが少なくとも 2 つあります。1) リスト A にはリスト B と一致しないデータがあります。列の結果は「より正しい」リスト B ですが、不一致があります。2) チェックを行った後、表示される結果は他の結果と重複しています。言い換えると、特定の CPU に割り当てられた人物が少なくとも 2 人表示され、リスト B に重複が含まれていない限り、これはリスト B に 1 人の人物が表示され、リスト A にはその CPU を持つ他の 1 人以上が表示されるためです。リスト B の結果の人物だけが正しい可能性があるため、他の人物は問題を示しています。
確認作業を行うには、新しい列を挿入し、押し込まれた列を元の位置に戻します。現在、リスト A のデータは列 A と B、黒い列は列 C、リスト B のデータは列 D と E であるとします。最終的には、新しい列 C が他のすべての列を 1 列右に押し出すため、リスト B は列 E と F になります。ヘッダー行はスプレッドシートの行 1 で、データは行 2:11 であると仮定します。
リスト B はリスト A との相違を上書きするため、数式はリスト B でリスト A の各人物を検索し、その人物のエントリがある場合はリスト B の内容を返します。リスト B のエントリがない場合はリスト A の内容を返します。
XLOOKUP($B2,$F$2:$F$5,$E$2:$E$5,$A2,0,1)
新しくて素敵なものを使用しましたXLOOKUP()が、持っていない場合は、Index/Matchリスト B で「左を見る」必要があるため、解決策が必要になります。
次に問題です。問題に対処するには、少なくとも 2 つの選択肢があります。おそらく、マクロを書いて対処するなど、他の方法もありますが、ここでは数式について考えます。1 つの方法は、XLOOKUP()問題のテストでそれを複雑にすることです。ほとんどの人は、大声で怒鳴り散らしてその方法に進もうとしているようです。しかし、はるかに簡単な方法があります。この方法では、必要に応じて大量の微調整が可能ですが、ここでは基本だけが必要です。それは、上記の数式を、完成したばかりでも本当に理解できない 20 行のモンスターにするのではなく、条件付き書式 ("CF") を使用してテストを行うことです。
CF では、ルールをリストする順序を選択するだけで、ルールが正しく機能する順序でテストされます。(ほとんどの人は最初にテストを考えます。最初に順序を選択した場合は、その順序に適合するテストを考えなければなりません。通常は逆の順序の方がはるかに簡単なので、人々はそうします。)
したがって、特定の CPU をリストしている人物が複数いる場合、それは「重複」と呼ばれ、CF にはそのためのルールが組み込まれています。最初にテストするのは簡単なので、それを設定します。適用する形式については、すぐに説明します。これには「トリック」があります。次に、テストに次の式を使用する 2 番目のルールを作成します。
=XLOOKUP($B2,$F$2:$F$5,$E$2:$E$5,$A2,0,1)<>$A2
この新しいルールは重複ルールの前に挿入されることに注意してください。これで問題ありません。これを 2 番目にチェックされるルールに移動すると、重複ルールは機能しなくなります。[True の場合は停止] ボックスをオンにしてください。
ここで、CF は数式の結果が人物の元のリスト A データと一致するかどうかを確認します。一致する場合は、ここで形式を適用して停止します。一致しない場合は、2 番目のルールに進みます。
2 番目のルールは重複があるかどうかを確認し、重複がある場合はその形式を適用します。
そこで、「トリック」は... まれな書式設定の可能性を使用して、エラー メッセージを新しいリスト A の結果に挿入することです (セルの数式にエラー メッセージを当てはめるという悪夢、作業の悪夢、更新または修正のためにすべてをもう一度理解しようとするという悪夢を実行するのではなく)。
通常の数値書式設定では、セル内の最大 4 種類のデータ (正の数、負の数、ゼロ、テキスト) の書式を設定できます。いずれの場合も、テキスト文字列 (複数) を書式に設定できます。実際、書式はテキスト文字列以外の何物でもありません。この事実は、これらの CF ルールの両方で使用します。最初のルールでは、次のように記述します。
;;;「リスト A が一致しません」
2 番目のルールでは、次のルールを使用します。
;;;">1 割り当て済み"
これらを目立たせるには (「CPU」のテキストよりも長いテキストですが...)、テキストを別の色 (少なくとも、きれいな赤色) でフォーマットするといいでしょう。
また、問題が何も記載されていないリストだけが必要な場合は、最初に数式を実行して、セルの数式を悪夢にするのではなく、セルに単純でわかりやすい数式を入力し、エラーをすべてテストして、CF の簡単なルールで適切なメッセージを表示したいときに、この CF のことを思い出してください。