推荐阅读
Excel合并单元格的数据查询
我原来的一位学生，做电商数据分析。今天提了一个问题：他给老板看销售数据的时候，老板说：“能不能做个查询，让我自己选择要查看的仓库与商品的销售量？”我这学生犯难了：数据中的“仓库”列是合并单元格的形式，不知道该怎么查找。根据学生描述，做了一个样表，老板要求的查询效果如下：公式实现在G2单元格输入公式：=VLOOKUP(F2,OFFSET(B1:C1,MATCH(E2,A2:A10,0),,3),2,)即可实现查询效果。公式解析
怎样在wps表格中查找指定行
当wps中的数据有很多条时，查询数据便是困难又麻烦，如果我们要查找wps表格中指定行，应该怎样操作呢?下面就让小编告诉你怎样在wps表格中查找指定行 的方法。 在wps表格中查找指定行的方法： 打开一个数据很多的表格。 接下来找到工具栏 找到编辑选项处。 点击找到查找，点击进入。 输入你要查找的内容进行定位。 点击查找全部，我们可以看到查询结果。 点击下方的结果词条，便可以定位具体位置。
如何修改MathType公式上下标大小
MathType公式编辑器功能十分强大，能够快速输入各种公式，有的时候因为上下标的存在而导致排版不美观，其实可通过修改公式上下标大小来改变公式大小。以下教程详解修改MathType公式上下标大小的具体方法。具体操作步骤如下：1.打开MathType编辑如下图所示公式；在MathType中编辑公式2.选中需要改变大小的上下标，点击上方菜单栏的“大小（Z）”，如下图所示：在MathType中选择大小菜单3.在“大小（Z）”菜单下，如果是想变大，点击选择“较大”，这时上下标就改变大小了（如下图所示）。反之如果想要变小，点击选择“较小”。 在大小菜单下选择较大选项4.当然，如果想直接设置为一个固定的比例大小，不需要像这样一个个地改变，点击“大小（Z）”，然后点击“定义”（如下图所示）。在大小菜单下选择定义选项
Excel Offset实现多行多列转单列
绝对是干货。也绝对是大家常常需要的。今天来分享下offset是如何实现多行多列转单列的。我想得到单列怎么办：经过我的研究最终得到公式是=OFFSET($A$1,(ROW(A1)-1)/4,MOD(ROW(A1)-1,4))找个E1输入上面的公式，然后一个个下拉就行了。稍微解析下公式，offset最简单的语法结构为（基点，偏移行，偏移列）.(ROW(A1)-1)/4,是为了偏移行时实现每4次偏移1行。对应如果源数据是5列，就改为5.MOD(ROW(A1)-1,4)，是为了偏移列时实现偏移都是1，2，3，4进行循环。对于如果源数据是5列，这里也改成5.这样解释可能不能把原理解释非常明白，但我这种解释对于工作实际应用已经够了，我已经告诉你们如何修改公式进行嵌套了。
最新发布
教师不用为比较成绩烦恼了,透视搞定它
每次都是要手写进去，弄排名，弄比较，简直太痛苦了~~其实这个问题可用公式解决，相对也会麻烦一点，因为还有一个需求，学生还不一定是那一批人，这里介绍一个最简单的办法，用透视表一、改造数据源要用数据库的格式来做数据源，一个学生一次考试一行数据排名用公式可以用RANK函数来做，大家有兴趣可以搜下公众号有写排名的文章二、做透视表选中范围，点击插入选项卡里的数据透视表
excel图表统计至少在一列中满足条件的行数
在这篇文章中，探讨一种计算在至少一列中满足规定条件的行数的解决方案，示例工作表如下所示，其中详细列出了各个国家在不同年份废镍的出口水平。假设我们希望确定2004年出口总额大于或等于1000的国家的数量，可以使用公式：=COUNTIF(B2:B14,”>=1000″)或者：=SUMPRODUCT(N(B2:B14>=1000))如果希望计算2004年和2005年有多少个国家的出口总额大于或等于1000，可以使用公式：=COUNTIFS(B2:B14,”>=1000″,C2:C14,”>=1000″)或者：=SUMPRODUCT(N(B2:B14>=1000),N(C2:C14>=1000))
从excel图表字符串中提取指定长度的连续数字子串
本文给出了一种从可能包含若干个不同长度的数字的字符串中提取指定长度的数字的解决方案。在实际的工作表中，存在着许多此类需求，例如从字符串中获取6位数字账号。下面是一个示例：20/04/15 – VAT Reg: 1234567: Please send123456 against Order #98765, Customer Code A123XY, £125.00从该字符串中提取出现的一个6位数字（123456）。在字符串中正确定位一个6位数字，需要考虑在与任意6个连续数字的字符串相邻的之前和之后的字符，并验证这两个字符都不是数字。在这里，将介绍两种解决方案，第一种是静态的，要提取的数字长度是固定的；第二种是动态的，允许长度变化。假设字符串在单元格A1中，则公式为：=0+MID(“ζ”&A1&”ζ”,1+MATCH(26,MMULT(N(ISERR(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5)),8),{1,2,3,4,5,6,7,8},1))),{13;1;1;1;1;1;1;13}),0),6)先看看公式中的：ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-5))
excel怎么同时定位字符串中的第一个和最后一个数字
在很多情况下，我们都面临着需要确定字符串中第一个和最后一个数字的位置的问题，这可能是为了提取包围在这两个边界内的子字符串。然而，通常的公式都是针对所需提取的子字符串完全由数字组成，如果要提取的数字中有分隔符（例如电话号码）则无法使用。当然，可以先执行替换操作来去掉字符串中的分隔符，这可能会更复杂些。本文仅涉及被提取的字符串内包含唯一的数字子字符串的情况。我们以示例来解。先看一下要提取的数字中没有分隔符的情形，例如在单元格A1中的字符串如下：Account No. 1234567890: requires attention显然，我们要提取出1234567890。下面是我们曾经使用的一个公式：=-LOOKUP(1,-(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&”**0″))注意，必须在MID函数生成的值的末尾添加“**0”，以保证能够在任何情况下都得到正确的结果。例如，如果单元格A1中的字符串是：Account No. 12-Jun: requires attention使用没有添加“**0”的公式：
Excel公式中的降维技术
看过前面一系列文章的朋友，一定会熟悉“重新定义数组维度”的概念。这是一项非常有用且非常重要的技术，使我们可以接受二维数组并将其转换为一维数组，同时将元素保留在该数组中。如果希望进一步操纵某二维数组的元素，则需要使用这种技术。例如，由于某种原因，在某种情形下，需要将二维数组中的每个元素传递给一个或多个参数进行进一步处理。但是，由于需要使用的Excel函数不能处理多维数组，因此必须首先将原数组转换为一维数组。以示例来说明，如下所示的工作表。 可以构造各种公式，如：=MID(A1,1,1)结果显然是“A”。下面的公式：=MID(A1,{1,2},1)
SUMIFS/COUNTIFS函数内的间接列引用变化
使用Excel朋友都知道，将包含相对列引用的公式复制到其他列时，这些引用也会相应地更新。例如，公式：=SUMIFS(C:C,$A:$A,”X”,$B:$B,”X”)向右拖放时，将会变成：=SUMIFS(D:D,$A:$A,”X”,$B:$B,”X”)=SUMIFS(E:E,$A:$A,”X”,$B:$B,”X”)等等。因此，我们有一个相对简单的方法，可以从连续的列中获得条件和。但是，如果我们希望增加的单元格区域是间接引用的，那该怎么办？例如，如果我们使用上述公式版本，但所引用的工作表是动态的：=SUMIFS(INDIRECT(“‘”&$A$1&”‘!C:C”),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)其中，A1包含要引用的工作表名称（例如“Sheet1”）。
如何给统计函数（GROWTH，LINEST，LOGEST，TREND）提供合法的参数值
绝大多数Excel函数都可以忽略传递给它们的布尔值（有时还有其他非数字值）。因此，它们可以有效地缩小操作的范围，该范围内仅包含非布尔值（或数字），这样使我们可以在函数中包含条件语句（通常使用IF函数），从而限制公式构造最终要处理的值。例如下所示的工作表：下面的公式：=AVERAGE(IF(A2:A11=”X”,B2:B11))可解析为：=AVERAGE(IF({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE},B2:B11))解析为：=AVERAGE({FALSE;58;FALSE;23;FALSE;93;FALSE;35;27;40})像大多数函数一样，AVERAGE函数忽略传递给它的数组中的布尔值，因此上述公式等价于：
在Excel条件格式中使用公式来突出显示单元格
条件格式与公式相配合，往往能够发挥很大的威力，其中之一就是用来突出显示单元格。如下所示，在“新建格式规则”对话框中：1. 选择“使用公式确定要设置格式的单元格”2. 在“为符合此公式的值设置格式”框中输入适当的公式3. 单击“格式”按钮，设置想要的格式。本文以交替突出显示所选单元格区域颜色为例来解。如下所示，给单元格区域中偶数行添加背景色。可使用公式：MOD(ROW(),2)=0其运算过程如下所示：
SUMPRODUCT函数基本原理详解
SUMPRODUCT函数返回相应的数组或区域乘积的和。其语法为：SUMPRODUCT(数组1, [数组2], [数组3], …)其中：1. 数组1，必需，指定想要相乘并相加的数组数据。2. 数组2,数组3,…，可选，指定想要相乘并相加的数组数据。数组个数不超过255个。3. 指定的数组可以是数组、单元格区域或者代表单元格区域的名称、代表公式的名称，例如{1;2;3}、A1:A10或者MyRange。例如，如下所示：公式1：=SUMPRODUCT({1;2;3})
Excel怎么获取满足多个条件的数值之和
有一组数据，我们想要求出剔除某些数据后余下的数据之和，如下所示，要求数据区域中除代码FA、PD、SS之外的分数之和。可以使用公式：=SUM(B2:I2)-(SUMIF(B1:I1,”FA”,B2:I2)+SUMIF(B1:I1,”PD”,B2:I2)+SUMIF(B1:I1,”SS”,B2:I2))然而，如果数据很多，这个公式会很长，很不简洁。此时，我们可以使用SUMPRODUCT函数的公式：=SUM(B2:I2)-SUMPRODUCT(((B1:I1)=({“FA”;”PD”;”SS”}))*(B2:I2))下面对这个公式的运行原理进行解释。公式的主要部分：SUMPRODUCT(((B1:I1)=({“FA”;”PD”;”SS”}))*(B2:I2))包含两部分：((B1:I1)=({“FA”;”PD”;”SS”}))