|
|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Excel软件在数据处理方面具有非常全面的功能，特别是内部函数的应用，在财务核算、统计分析、数据处理、模型设计和文本处理等方面拥有无可比拟的优势。可能大家对很多常见函数运用得非常熟练，但是可能经常遇到一些特殊函数不太熟悉；有些初学者可能在学习函数时就某个函数而学某个函数，但往往在遇到实际问题时还是无从下手。基于这些问题，我想通过一些实际工作中经常会应用到的实例，和大家一起探讨一下某些常用函数的技巧、配合使用方法以及数组函数的运用。由于时间和水平有限，在此也只能抛砖引玉，希望各位版主、高手在关键时刻多伸援手、不吝赐教！同时也希望更多的朋友能提供自己在运用EXCEL软件工作时遇到的比较具有代表性的应用实例或疑难问题，让我们一起来分享用EXCEL函数功能解决问题带来的快乐和满足~~
【2009.2.19】如何提取不重复值
一、问题描述
请见下图：
我们经常在使用EXCEL软件处理数据时，会遇到需要在一列含有重复数值的数据中选取其中的不重复项目并放置在指定位置的单元格内，比如上图中，我们需要对A列省份数据进行筛选，结果区域中只存放不同的省份数据以供列表等控件进一步取数使用。可能有的朋友会说，这类应用最好采用自动或高级筛选的操作方法来完成，但有时候在表格里并不都适合采用这样的操作的方法，而只能通过公式来实现自动数据筛选，而我们今天所要探讨的就是这样的应用。
在这个案例里有如下特点：1、源数据以一列的形式存放；2、存在重复的数据列中，相同的数据存放在一起；3、提取的结果以多个值组成的数组的形式存放在同一张工作表中。下面我们就来看一下如何实现这样的筛选。
二、公式分析
先把公式罗列一下，{=INDEX($A:$A,SMALL(IF(MATCH(SF,SF,)=ROW(SF)-1,ROW(SF),65536),ROW(1:1))&"")} ，其中SF是一个定义的名称，内容是 =offset($A$2,,,COUNTA($A$A)-1,)
我们分析一个公式，最好像剥笋壳一样，逐层分析理解，那么我们现在就来分析一下吧。
首先，我们看到SF这个定义名称，它用到了一个OFFSET函数，该函数的作用是以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。该函数有五个参数，含义分别为：①指定作为参照标准的单元格引用②相对于偏移量参照系的左上角单元格上（下）偏移的行数③相对于偏移量参照系的左上角单元格左（右）偏移的列数④所要返回的引用区域的行数⑤所要返回的引用区域的列数。在这个例子里，该函数返回了一个新的单元格区域的引用，即：从A2单元格开始向下至最后一个有数据的单元格为止的单元格区域。
其次，我们看到在公式的最内部，使用了一个ROW函数，这个函数比较简单，用来返回引用的行号，有一个参数，含义为需要得到其行号的单元格或单元格区域。在这里，ROW(SF)的意思是，返回SF引用的单元格区域的行号（当然不止一个）。
再次，我们看到了MATCH函数，这个函数的意思是返回在指定方式下与指定数值匹配的数组中元素的相应位置。这个函数与LOOKUP函数的区别在于，如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是 LOOKUP 函数。这个函数有三个参数，含义分别为：①需要在 Look_array 中查找的数值②可能包含所要查找的数值的连续单元格区域，即在这个参数指定的范围内进行查找③指明 Microsoft Excel 如何在第二个参数指定的范围中查找与第一个参数匹配的数据位置。这个参数有三个取值，即数字 -1、0 或 1。如果 match_type 为 1，函数 MATCH 查找小于或等于 lookup_value 的最大数值，限制在于Lookup_array 必须按升序排列；如果 match_type 为 0，函数 MATCH 查找等于 lookup_value 的第一个数值，这里的Lookup_array 可以按任何顺序排列；如果 match_type 为 -1，函数 MATCH 查找大于或等于 lookup_value 的最小数值。Lookup_array 必须按降序排列。如果省略 match_type，则假设为 0。在这个例子里，MATCH(SF,SF,)的意思是在我们定义的SF名称返回的单元格区域（即A2向下到最后一个有数据的单元格为止的区域）查找单元格值等于其中某一个对应单元格值的第一个位置，例如“广东”，A2-A10都是“广东”，则通过MATCH函数可以找到第一个等于“广东”的单元格在区域中的位置，即1。这个是比较关键的地方。
然后，使用了一个IF函数。由于IF函数大家都比较熟悉，这里就不再介绍它的语法了。IF(MATCH(SF,SF,)=ROW(SF)-1,ROW(SF),65536),这个部分的含义是在SF这个定义名称指定的区域中，如果某一个单元格的值在这个区域中对应的具有相同值的第一个位置的值，和这个单元格的位置相同，那么就返回这个单元格的行号，否则返回65536这个最大行号。也就是说，比如“四川”，在这个区域中值为“四川”的单元格的第一个位置是10，那么如果当前单元格的位置是10的话，返回10，否则返回65536。
接着，又用到了一个SMALL函数。这个函数的作用是返回数据集中第 k 个最小值，使用此函数可以返回数据集中特定位置上的数值。这个函数有2个参数，第一个参数的意思是需要找到第 k 个最小值的数组或数字型数据区域；第二个参数的意思是返回的数据按照从小到大的顺序在数组或数据区域里的位置。在这个例子中，SMALL(IF(MATCH(SF,SF,)=ROW(SF)-1,ROW(SF),65536),ROW(1:1))&"")这部分的意思是按照IF函数返回的一系列值，找到其中的最小的一个。注意，这里的ROW(1:1)，是一种常见的技巧，就是返回第一行的行号，即1；为什么不直接写1呢？因为1不能根据单元格位置的改变而自动递增或递减，而单元格引用就可以。一会我们就可以看到这种现象。
最后，在最外层使用了一个INDEX函数。这个函数的作用是返回表或区域中的值或值的引用，在这里用到的是第一个用法，即返回区域中的值。这个函数的这种用法有3个参数，作用分别为：①单元格区域或数组常量②指定数组中某行的行序号③指定数组中某列的列序号。这个例子里，表示在A列中，返回行号为SMALL函数返回的结果的单元格的值。由于只有一列，所以省略了第三个参数，即列号。
好了，现在我们把这个公式自己填写在某一个单元格中，然后按照数组公式的要求，按【CTRL】+【SHIFT】+【ENTER】来确认数组公式。然后按拖放手柄，向下拖动3个单元格，瞧，结果出来了吧？分别用鼠标单击每一个单元格，看看有什么变化？是不是ROW(1:1)中的“1:1”部分变成了“2:2”和“3:3”？
三、举一反三
好了，问题是解决了，可是这个公式存在如下问题：
1、需要拖放操作才能提取所有值；2、无法判断有几个不同的值；3、数组公式无法用在有效性的序列公式里；4、公式很长，加上定义名称就更长了，能否简化。大家可以在这些方面进一步探讨。
实例如下：
提取不重复值实例.rar
(4.76 KB, 下载次数: 177)
关于数组公式的介绍，将在下一次继续讨论，敬请期待~~ |
评分
-
1
查看全部评分
-
|
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-2-19 13:33
楼主
