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

问题描述

你知道Excel中的一种方式，通过公式计算唯一值列表吗？

例如：范围包含值 red ， blue ， red 绿色 ， blue > 黑色

，我想要具有结果 red ， blue ， green ， 黑色 +最终2个其他空白单元格

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

解决方案

这是一个古老的，有一些解决方案，但是我想出了比任何其他我遇到的更简单和简单的公式，对于任何人都可能有用。

我已经命名了颜色< c>颜色（A2：A7），单元格 C2 中的数组公式是这个（

  = IFERROR（INDEX（Colors，MATCH（SUM（COUNTF（C $ 1：C1，Colors） ），COUNTIF（颜色，&颜色），0）），）
  

使用 Ctrl + Shift + Enter 在 C2 中输入公式，然后将C2复制到 C3：C7 。

样本数据说明{red; 蓝色; 红; 绿色; 蓝色; 黑色}：

1. COUNTIF（颜色，&颜色）返回一个数组（＃1），其数值小于数据{4; 1; 4; 3; 1; 0}中的每个项目（黑色= 0项目较小，蓝色= 1项目，红色= 4项）。这可以转换为每个项目的排序值。


2. COUNTIF（C $ 1：C ...，Colors） code>为排序结果中已经存在的每个数据项返回一个数组（＃2）。在C2中，它返回{0; 0; 0; 0; 0; 0}和C3 {0; 0; 0; 0; 0; 1}，因为黑是数据中的排序和最后一个。在C4 {0; 1; 0; 0; 1; 1}中，它表示黑色，并且所有出现的蓝色已经存在。


3. SUM 返回第k个排序值，通过计算已经存在的所有较小值出现次数（数组＃2的总和）。


4. MATCH 找到第k个排序值的第一个索引（数组＃1中的索引）。


5. IFERROR 只能在排序的唯一列表完成时隐藏底部单元格中的＃N / A 错误。要知道您有多少个独特的项目可以使用这种常规公式：

     = SUM（IF（FREQUENCY（COUNTIF（Colors，& Colors），COUNTIF（Colors，& Colors）） ，1））
     

   
   

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

   E.g: a 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.

   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.

   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)),"")
   

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

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

   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屋！