Skip to content

検索関数#

UNIQUE関数#

UNIQUE関数は、Excelの新しい動的配列関数の1つであり、重複を除いた一意の値のリストを返すことができます。以下は、UNIQUE関数の使い方と構文の解説です。

構文:

UNIQUE(array, [by_col], [exactly_once])
  • array: 必須。一意の値を抽出する範囲を指定します。
  • by_col: オプション。論理値を指定します。TRUEを指定すると、列単位で一意の値を抽出します。FALSEを指定すると、行単位で一意の値を抽出します。デフォルト値はFALSEです。
  • exactly_once: オプション。論理値を指定します。TRUEを指定すると、一意の値が正確に1回だけ出現した場合のみ、その値を返します。FALSEを指定すると、1回以上出現した場合でも、一意の値を返します。デフォルト値はFALSEです。

例えば、次の式を使用して、範囲A2:A11から一意の値を抽出することができます。

=UNIQUE(A2:A11)

この式を入力すると、A2:A11の範囲から一意の値が抽出され、新しい列に表示されます。また、範囲が列単位で指定されている場合は、by_col引数をTRUEに設定することができます。

=UNIQUE(A2:A11, TRUE)

この式を使用すると、A2:A11の範囲から一意の値が抽出され、新しい行に表示されます。

一意の値が1回だけ出現する場合にのみ抽出するには、exactly_once引数をTRUEに設定します。

=UNIQUE(A2:A11, FALSE, TRUE)

SORT 関数と併用#

=SORT(UNIQUE(A4:A10))

UNIQUE関数代用#

IFERROR(INDEX($A$2:$A$11,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$11),0,0),0)),"")

分かりやすく階層構造にすると以下のとおり

IFERROR(
    INDEX($A$2:$A$11,
        MATCH(0,
            INDEX(COUNTIF($B$1:B1,$A$2:$A$11),0,0),
            0)
        ),
    ""
)
  1. COUNTIF関数を使用して、A2:A11の値がB1からB1の上のセルまでに何回出現するかを数えます。これにより、A2:A11内の各値の重複数が得られます。
  2. INDEX関数を使用して、1つの列の値を取得します。これにより、COUNTIF関数によって返される重複数の範囲が1つの列に変換されます。
  3. MATCH関数を使用して、COUNTIF関数で返される範囲の中で、最初に0に一致する行の位置を検索します。つまり、A2:A11の値のうち、まだ一意の値として表示されていない最初の値の位置を見つけます。
  4. INDEX関数を使用して、検索された位置にある一意の値を返します。つまり、重複を除いたA列の値の中で、最初に現れた値を返します。
  5. IFERROR関数を使用して、エラーが発生した場合に空白を返すようにします。これにより、一意の値がない場合に空白セルが表示されます。
  6. 注意点として、この式を新しいセルに入力する場合は、B1の範囲参照を適宜調整する必要があります。例えば、式をC2に入力する場合は、COUNTIF関数の範囲参照を$C$1:C1に変更する必要があります。また、式をコピーして他のセルに貼り付ける場合は、B1のセル参照の相対参照を使用して、自動的に範囲を更新するようになっています。

MATCH関数

MATCH 関数は、範囲 のセルの範囲で指定した項目を検索し、その範囲内の項目の相対的な位置を返します。 たとえば、範囲 A1:A3 に値 5、25、38 が含まれている場合、数式「=MATCH(25,A1:A3,0)」を入力すると、範囲内では 25 が 2 番目の項目であるため、数字 2 が返されます。

INDEX関数

条件付き書式#

セルの値が列の中に含まれていない場合に色付けする条件付き書式の数式として、以下の式を使用することができます。

=ISERROR(MATCH(A1, $B$1:$B$10, 0))

上記の式では、セルA1の値が、範囲B1:B10に含まれていない場合にTRUEを返します。 この式を条件付き書式のルールに適用することで、セルの値が範囲に含まれていない場合に色を付けることができます。

以下は、この式を使用した条件付き書式の設定方法です。

条件付き書式を選択します。 1. 「新しいルール」を選択します。 2. 「使用する式を選択する」を選択します。 3. 「書式する値を入力」に、上記の式を入力します。 4. 「書式」を選択して、色を選択します。 5. 「OK」をクリックして、条件付き書式を適用します。

このようにすることで、セルの値が範囲に含まれていない場合に、条件付き書式によって色が付けられます。

その他便利な数式#

条件付き書式で使用すると便利な数式の例として、以下のようなものがあります。

COUNTIF関数

範囲内に指定した値がいくつ含まれているかを数える関数です。条件付き書式で、ある値の出現回数に応じてセルの色を変えたい場合に使えます。 例: 「A1:A10」の範囲において、「B1」に入力された数値の出現回数に応じて色を変える場合、以下の式を使えます。

=COUNTIF($A$1:$A$10,$B$1)>0

IF関数

条件分岐を行う関数です。ある条件を満たす場合にはAの色、満たさない場合にはBの色にするなど、条件に応じて異なる色を設定する場合に使えます。 例: 「A1:A10」の範囲において、セルの値が100より大きい場合に赤色、それ以外の場合には黒色にしたい場合、以下の式を使えます。

=IF(A1>100,TRUE,FALSE)

SUM関数

範囲内の数値の合計を計算する関数です。条件付き書式で、ある値が合計値の一定割合以上である場合にセルの色を変えたい場合に使えます。 例: 「A1:A10」の範囲において、セルの値が合計値の30%以上である場合に赤色、それ以外の場合には黒色にしたい場合、以下の式を使えます。

=A1/SUM($A$1:$A$10)>=0.3