☰

シート1

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
シート1

2 つのワークシートを含む Excel スプレッドシートがあります。最初のシートは、ヘッダー行とアイテム名の 1 列のみです。2 番目はアイテム グループのリストで、ヘッダー行と左端の列にタイトルがあり、後続の各行には他のシートの 1 つのアイテムが含まれています。

Sheet1:            Sheet2:
+-------+--+--+    +-------+-------+-------+-------+-------+
| Item  |  |  |    | Group | Item1 | Item2 | Item3 | ...
+-------+--+--+    +-------+-------+-------+-------+-------+
| Shirt |  |  |    | A     | Shirt | Hat   | Tie   |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Hat   |  |  |    | B     | Socks | Shirt | SHOES |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Socks |  |  |    | C     | Hat   | Socks |       |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Tie   |  |  |    | D     | Tie   | Tie   | Socks |
+-------+--+--+    +-------+-------+-------+-------+-------+
| ...   |  |  |
+-------+--+--+

「Sheet2」のすべてのセルを条件付きで書式設定し、「Sheet1」の最初の列の値と一致しない値には赤い背景が、一致する値には緑の背景がそれぞれ表示されるようにしたいと思います。したがって、この例では、B2 から始まるすべてのセルは、「SHOES」という値を除いて緑色になります。その下の値には何も入力されていないため、書式設定はまったく行われません。

私が試した緑の書式設定ルールは次のとおりです。

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)>0)

赤の場合もほぼ同じです。

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)<1)

両方のルールは、ある程度任意の範囲に「適用」されます (一番上と一番左の行/列を除いて、シート全体に適用したいと思います)。

=$C$3:$E$10,$C$36:$Q$50,$E$11,$C$11,$C$2,$E$2:$Q$2,$C$12:$E$35,$F$3:$Q$35

これは半分は機能しますが、結果は予測できません。一部の値は期待どおりにハイライトされますが、数行のみで、他の値はハイライトされません。おそらく範囲が何らかの理由でずれているのでしょうが、以前ほど Excel を使用しなくなりました。誰か手助けしてくれませんか?

ありがとう！

答え1

ドクトロー・ライヒャルトが言うように、条件付き書式これを実行するには、次の 3 つのルールが必要です。

  1. セルが空白の場合は背景を変更しないでください
  2. セルに一致がある場合は背景を緑色にする
  3. セルに一致するものがない場合、背景を赤にする

申し訳ありませんが、私の Excel は日本語です。多言語の日です。

これを行うには、これらの条件ごとにTRUEまたは を返す 3 つの数式が必要です。データが次のようになっていると仮定します。FALSE

シート1

ここに画像の説明を入力してください

シート2

ここに画像の説明を入力してください

ルール1

次の数式はセルが空白かどうかを返します。

ここに画像の説明を入力してください

=ISBLANK(B2)

相対参照を持つセルを選択していることに注意してくださいB2:D5。これにより、選択した範囲内のすべてのセルのセル参照を変更する同じ数式が適用されます。この条件が満たされている場合は、背景色を白 (または任意の色) に設定します。

ルール2

次の数式は、シート 1 のリストに完全一致があるかどうかを返します。

ここに画像の説明を入力してください

=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))

ルール3

次の数式は、シート 1 のリストに完全一致がないかどうかを返します。

ここに画像の説明を入力してください

=ISERROR(MATCH(B2,Sheet1!$A:$A,0))

注文

一番上のルールが最初に実行されます。したがって、すべての空白セルは不一致となるため、空白ルールを最初に置く必要があります。#2 と #3 の順序は重要ではありません (重複することはありません)。

答え2

必要なのはセルの条件付き書式のようです。

がここにありますリンクこれが何であるかを説明します。

私の写真は Excel 2003 のポルトガル語版のものですが、その機能は Office 2010 でも利用できるはずです。実際、Office 2010 ではシート間で条件付き書式を設定できますが、これは 2003 ではできないため、すべてを 1 つのシートで実行しています。

まず、表：

テーブル

やりたいことは、2 番目のテーブルの要素を 1 番目のテーブルのすべての要素と比較することです。そのため、次のような関数を記述する必要があります。

=OR(EXACT($B$6;E3);EXACT($B$5;E3);EXACT($B$4;E3);EXACT($B$3;E3))

はEXACT()、2 つのテキスト文字列を比較します。内部の論理条件が である場合、はどうOR()なるでしょうか。TrueTrue

そうは言っても、次に 2 番目の表からすべてのセルを選択し、図のように条件付き書式を押します。

条件付き書式にアクセスする方法

Office 2010 とその新しいリボン インターフェイスについて知っている場合は、これについては [書式] ウィンドウを確認する必要があります。私の記憶では、これはアイコンとして表示されます。

そのアイコンをクリックすると、次のようなウィンドウが表示されます。

条件付き書式設定ウィンドウの一部

そこで、まず数式を選択し、次に前述の数式を貼り付けます。条件を検証するすべてのセルを緑色にするには、書式を変更するだけです。赤色の書式設定を行うには、 を使用します。NOT(OR(...))これにより、設定した条件の逆が返されます。

何も入力されていないセルがフォーマットされないようにするには、数式が ISBLANK(E3) (E3 が左上隅) となる 3 番目の条件を作成します。

最初の草稿を書いた後に私がチェックしたのは優先順位です。少なくとも私のバージョンでは、条件 1 は条件 2 より先に検証され、以下同様です。したがって、条件は互いに干渉しないような順序にする必要があります。つまり、

1st condition - =ISBLANK(E3)
2nd condition - =OR(...)
3rd condition - =NOT(OR(...))

したがって、次のようなウィンドウが表示されます。

最終ウィンドウ

これを自分の状況に合わせて調整してみてください。私の記憶が正しければ、私が示しているものとそれほど違いはありません。結果は次のようになるはずです。

最終結果

関連情報