仅使用公式在 Excel 中获取唯一值 [英] Getting unique values in Excel by using formulas only

问题描述

您知道在 Excel 中通过公式计算"唯一值列表的方法吗?

Do you know a way in Excel to "calculate" by formula a list of unique values ?

例如:垂直范围包含值"red"、"blue"、红色"、绿色"、蓝色"代码>、<代码>黑色"
我想要结果 "red, "blue", "green", "black" + 最终 2 个其他空白单元格.

E.g: a vertical range contains values "red", "blue", "red", "green", "blue", "black"
and I want to have as result "red, "blue", "green", "black" + eventually 2 other blank cells.

我已经找到了一种使用 SMALL 或 LARGE 结合 INDEX 来获得计算排序列表的方法，但我也希望在不使用 VBA 的情况下进行这种计算排序.

I already found a way to get a calculated sorted list using SMALL or LARGE combined with INDEX, but I'd like to have this calculated sort as well, WITHOUT USING VBA.

推荐答案

这是一个老问题，有一些解决方案，但我想出了一个更短更简单的公式其他我遇到的，可能对路过的人有用.

This is an oldie, and there are a few solutions out there, but I came up with a shorter and simpler formula than any other I encountered, and it might be useful to anyone passing by.

我已经将颜色列表命名为Colors (A2:A7)，而数组公式放在C2单元格中是这样的(>固定):

I have named the colors list Colors (A2:A7), and the array formula put in cell C2 is this (fixed):

=IFERROR(INDEX(Colors,MATCH(SUM(COUNTIF(C$1:C1,Colors)),COUNTIF(Colors,"<"&Colors),0)),"")

使用Ctrl+Shift+Enter在C2中输入公式，并将C2向下复制到C3:C7.

Use Ctrl+Shift+Enter to enter the formula in C2, and copy C2 down to C3:C7.

用样本数据解释 {"red";蓝色";红色的";绿色";蓝色";黑色"}:

Explanation with sample data {"red"; "blue"; "red"; "green"; "blue"; "black"}:

1. COUNTIF(Colors,"<"&Colors) 返回一个数组 (#1)，其中的值计数小于数据 {4;1;4; 中的每一项.3;1;0}(黑色=0 个较小的项目，蓝色=1 个项目，红色=4 个项目).这可以转换为每个项目的排序值.
2. COUNTIF(C$1:C...,Colors) 返回一个数组 (#2)，其中每个已在排序结果中的数据项为 1.在 C2 中，它返回 {0;0;0;0;0;0}，在 C3 中返回 {0;0;0;0;0;1}，因为黑色"在排序中排在第一位，在数据中排在最后.在 C4 {0;1;0;0;1;1} 中，它表示黑色"并且所有出现的蓝色"都已经存在.
3. SUM 返回 k-th 排序值，通过计算已经存在的所有较小值出现(数组 #2 的总和).
4. MATCH 查找第 k 个排序值的第一个索引(数组 #1 中的索引).
5. IFERROR 只是在排序的唯一列表完成时隐藏底部单元格中的 #N/A 错误.

1. COUNTIF(Colors,"<"&Colors) returns an array (#1) with the count of values that are smaller then each item in the data {4;1;4;3;1;0} (black=0 items smaller, blue=1 item, red=4 items). This can be translated to a sort value for each item.
2. COUNTIF(C$1:C...,Colors) returns an array (#2) with 1 for each data item that is already in the sorted result. In C2 it returns {0;0;0;0;0;0} and in C3 {0;0;0;0;0;1} because "black" is first in the sort and last in the data. In C4 {0;1;0;0;1;1} it indicates "black" and all the occurrences of "blue" are already present.
3. The SUM returns the k-th sort value, by counting all the smaller values occurrences that are already present (sum of array #2).
4. MATCH finds the first index of the k-th sort value (index in array #1).
5. The IFERROR is only to hide the #N/A error in the bottom cells, when the sorted unique list is complete.

要知道您有多少独特的商品，您可以使用这个常规公式:

To know how many unique items you have you can use this regular formula:

=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))

这篇关于仅使用公式在 Excel 中获取唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！