一意の値を取り出す：Excel と Google Sheets

要点

Google Sheets では UNIQUE が最初に出現した順で一意の値をこぼします。テストは SKU-103、SKU-101、SKU-103、SKU-110、sku-103、空白、もう一つの SKU-101 です。UNIQUE は SKU-103、SKU-101、SKU-110、sku-103、および空白を返しました。sku-103 と SKU-103 は別の値として残ります。

=UNIQUE(A2:A8)
=UNIQUE(A2:A8,FALSE,TRUE)
=COUNTUNIQUE(A2:A8)
=COUNTA(UNIQUE(A2:A8))

確認のポイント

第 3 引数を TRUE にすると 1 回だけ現れる値になり、今回は SKU-110 と sku-103 です。2 回出る SKU-103 と SKU-101 は外れます。COUNTUNIQUE は空白を除いて 4、COUNTA(UNIQUE(...)) は 5 で、こぼし結果に空の項目が含まれるためです。

注意点と互換性

COUNTUNIQUE は Sheets の関数です。Microsoft の説明では現行 Excel に UNIQUE がありますが、今回はデスクトップ Excel では再テストしていません。UNIQUE は下に空きセルが必要です。

英語の詳細例・数式・著者メモ

Which versions this applies to

The results below come from Google Sheets on November 12, 2026. Microsoft documents UNIQUE for Excel 365 / Excel 2021. It is not in Excel 2016 or 2019. COUNTUNIQUE is a Google Sheets function; I am not quoting an Excel equivalent from this test. I did not re-run this list in desktop Excel today.

The list I used

A2:A8 held: SKU-103, SKU-101, SKU-103, SKU-110, sku-103, a blank cell, and SKU-101 again.

=UNIQUE(A2:A8)
=UNIQUE(A2:A8,FALSE,TRUE)
=COUNTUNIQUE(A2:A8)
=COUNTA(UNIQUE(A2:A8))

UNIQUE spilled SKU-103, SKU-101, SKU-110, sku-103, then a blank cell. The first occurrence of each value is the one that is kept, in that order. sku-103 was not merged into SKU-103. That is the opposite of COUNTIF and XLOOKUP, which ignored case in Case-Sensitive Lookup.

The third argument TRUE means “exactly once”: UNIQUE(A2:A8,FALSE,TRUE) spilled only SKU-110 and sku-103. SKU-103 and SKU-101 appear twice in the source, so they dropped out. The second argument FALSE means “by row,” which is the default I would pass explicitly if I also need the third argument.

COUNTUNIQUE and a blank cell

COUNTUNIQUE(A2:A8) returned 4. COUNTA(UNIQUE(A2:A8)) returned 5. The unique spill includes the blank as an extra item; COUNTUNIQUE did not count that blank, and COUNTA did, because a zero-length entry is not the same as an omitted cell for COUNTA. If you want a count of non-blank distinct codes, COUNTUNIQUE matched the four SKU strings in this file. If you need to know that a blank made it into the unique list, look at the spill, or compare the two counts.

Excel and Google Sheets differences

Current Excel has UNIQUE with the same by-column / exactly-once arguments, according to Microsoft. I did not re-test those arguments in Excel today. COUNTUNIQUE is the Sheets function I actually ran; I would not invent an Excel synonym for it here. Highlighting duplicates, rather than listing the unique set, is Highlight Duplicates Across Two Columns.

Common mistakes

Expecting UNIQUE to treat SKU-103 and sku-103 as one value. It kept both.
Comparing COUNTUNIQUE with COUNTA(UNIQUE(...)) and assuming they must match. They were 4 and 5 because of the blank.
Using UNIQUE(...,TRUE) and thinking the first argument flip means “exactly once.” The “exactly once” switch is the third argument.
Leaving values in the spill area. UNIQUE needs empty cells below the formula, the same way SPLIT needs empty cells to the right.

FAQ

How do I list unique values in Google Sheets?

=UNIQUE(A2:A8) spilled the distinct SKUs in first-seen order, and kept sku-103 separate from SKU-103.

How do I list values that appear only once?

=UNIQUE(A2:A8,FALSE,TRUE) spilled SKU-110 and sku-103 from this list.

Why is COUNTUNIQUE different from COUNTA of UNIQUE?

The unique spill included a blank from the source range. COUNTUNIQUE was 4; COUNTA(UNIQUE(A2:A8)) was 5.

Is UNIQUE case-sensitive?

In this file, yes: SKU-103 and sku-103 both appeared in the spill.

Will UNIQUE work in Excel 2016?

Microsoft does not list UNIQUE for Excel 2016 or 2019. I did not build an older-Excel workaround as part of this test.

Live-test findings

Google Sheets, November 12, 2026. Source: SKU-103, SKU-101, SKU-103, SKU-110, sku-103, blank, SKU-101. UNIQUE spilled SKU-103, SKU-101, SKU-110, sku-103, and a blank. UNIQUE(...,FALSE,TRUE) spilled SKU-110, sku-103. COUNTUNIQUE was 4. COUNTA(UNIQUE(...)) was 5.

Sources

Microsoft Support: UNIQUE function: https://support.microsoft.com/en-us/excel/functions/unique-function
Google Docs Editors Help: UNIQUE: https://support.google.com/docs/answer/10522653
Google Docs Editors Help: COUNTUNIQUE: https://support.google.com/docs/answer/3093487