excel函数
excel公式如何统计至少在一列中满足条件的行数
在这篇文章中，探讨一种计算在至少一列中满足规定条件的行数的解决方案，示例工作表如下图1所示，其中详细列出了各个国家在不同年份废镍的出口水平。图1假设我们希望确定2004年出口总额大于或等于1000的国家的数量，可以使用公式：=COUNTIF(B2:B14,”>=1000″)或者：=SUMPRODUCT(N(B2:B14>=1000))如果希望计算2004年和2005年有多少个国家的出口总额大于或等于1000，可以使用公式：=COUNTIFS(B2:B14,”>=1000″,C2:C14,”>=1000″)或者：
如何中excel单元格中返回多个条件的数据
在实际工作中，我们经常需要从某列返回数据，该数据对应于另一列满足一个或多个条件的数据中的最大值。如下图1所示，需要返回指定序号（列A）的最新版本（列B）对应的日期（列C）。图1解决方案1：在单元格F2中输入数组公式：=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=F1,B2:B10)),IF(A2:A10=F1,B2:B10),0))注意这里有两个IF子句，不仅在生成参数lookup_value的值的构造中，也在生成参数lookup_array的值的构造中。千万不能忽略了这一要点，即如果采用以下简单方法：=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=F1,B2:B10)),B2:B10,0))尽管此公式构造仍可以返回正确的值，但完全不能保证所有情况下都正确。原因是与条件对应的最大值不是在B2:B10中，而是针对不同的序号。而且，如果该情况发生在希望返回的值之前行中，则MATCH函数显然不会返回我们想要的值。
excel表格获取指定区域中每行首次出现指定值的位置
给定一个任意大小的单元格区域，其每个单元格中的数据要么是0要么是1，并且每行至少有一个1，要求使用一个公式返回一个数组，该数组由区域每行中第一次出现1的相对列位置组成。例如下图1所示的单元格区域A1:E10，要求返回数组{2;1;1;2;1;5;1;4;1;3}。图1注意，公式中可以使用单元格区域A1:E10，但应该适用于任何其他区域。并且，所返回的数组中的元素对应的是区域内的相对列位置，例如将图1中的区域替换成H1:L10不应该影响公式的结果。先不看答案，自已动手试一试。公式下面列出了各种解决上述问题的公式。公式1：=COLUMNS(A1:E10)-INT(LOG(MMULT(A1:E10,2^(COLUMNS(A1:E10)-ROW(INDIRECT(“1:”&COLUMNS(A1:E10))))),2))公式2：
excel公式技巧：在方形区域内填充不重复的随机整数
本文分享一个基于公式生成n×n随机整数的解决方案，并且每个整数都是唯一的。例如，下图1显示了生成10行10列的不重复随机整数。图1解决方案在单元格A1中输入数组公式：=SMALL(IF(FREQUENCY(($A2:$J$11,B1:$K1),ROW(INDIRECT(“1:99”))-1)=0,ROW(INDIRECT(“1:100”))-1),RANDBETWEEN(1,100-COUNTA($A2:$J$11,B1:$K1)))向右向下拖拉至单元格J10。通常，将此矩阵放置在工作表中的某位置，对于输出结果的最左上角单元格的公式，引用的两个单元格区域包括：1）10×10的单元格区域从最左上角的单元格正下方的单元格开始，向下并向右延伸。2）最左上角单元格右侧的1×10单行单元格数组
excel公式技巧：在公式中用特定构造代替常量数组
在有些情形下，公式中不能直接显式使用数组常量。当然，可以将这些数组常量存储在定义的名称中，然后在公式中引用。这里所要求的公式非常简单，可生成下面的结果：{-1;1}要求：引用的单元格区域必须同时包含行和列，不能只是行（如1:2）或只是列（如A:B）；不能使用名称；公式使用的字符数最少。先不看答案，自已动手试一试。公式公式1：-1^ROW(A1:A2)公式2：2*ROW(A1:A2)-3下面是不符合要求的公式：
excel公式技巧：使用公式排序
Excel提供了排序功能，可以方便地对选中的列表进行排序。本文给出一个基于公式的排序解决方案，将指定区域内的数据按字母顺序排序。如下图1所示，在单元格区域A2:A11中是一组未排序的数据，在单元格区域B2:B11中是已排序的数据。图1解决方案在单元格B2中输入公式：=LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)),$A$2:$A$11)向下拉至单元格B11。工作原理让我们以单元格B8中的公式为例来分析：
excel公式技巧： 获取指定区域中2个及以上连续数值并按顺序排列
给定单元格区域A1:D5（其中每个单元格中都是整数，并且在该单元格区域内是唯一的），使用单个公式生成一个数组，该数组由该区域中所有连续的数值组成，连续的数值至少包含两个，且返回的数组中的元素按从小到大的顺序排列。图1也就是说，可以返回结果：{1;2;3;12;13;14;15;16;17;36;37}也可以返回结果：{1,2,3,12,13,14,15,16,17,36,37}注意，所给出的公式应包含最少的字符；在公式中必须同时包含行列引用，不允许全是列引用（如A:D）或行引用（如1:5）；公式中不允许使用名称。先不看答案，自已动手试一试。公式
VLOOKUP在多个工作表中查找相匹配的值
我们给出了基于在多个工作表给定列中匹配单个条件来返回值的解决方案。本文使用与之相同的示例，但是将匹配多个条件，并提供两个解决方案：一个是使用辅助列，另一个不使用辅助列。下面是3个示例工作表：图1：工作表Sheet1图2：工作表Sheet2图3：工作表Sheet3示例要求从这3个工作表中从左至右查找，返回Colour列中为“Red”且“Year”列为“2012”对应的Amount列中的值，如下图4所示的第7行和第11行。
excel公式技巧： 比较字符串中的字符
在单元格A2中给定一个字符串值，仅由大写字母组成且字符长度至少为2。在单元格B2中输入公式：如果A2中字符串的各个字符按字母升序排列（从左到右），则返回TRUE；否则返回FALSE。如图1所示。图1先不看答案，自已动手试一试。公式在单元格B2中的公式为：=AND(GESTEP(MMULT(CODE(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)-1))+{0,1},1)),{-1;1}),0))向下拖拉即可。公式解析首先，让我们看一下公式中的用于生成要传递给MID函数的参数的数组的结构：