EXCEL函数问题？

来源：蜘蛛抓取(WebSpider) 时间：2022-07-07 08:48 标签： EXCEL基础函数

近日在老菜鸟的班有人丢出来这样一道题目：

问题很简单，就是计算余额，原本使用了一个大家都能看得懂的公式：=A2+C2+E2-B2-D2-F2

就是将入库全部加起来，再减去所有的出库数量。

公式固然简单，但是不够方便，考虑到今后会有更多的列需要进入统计范围，这样逐列加减不能满足需要，因此考虑使用区域进行计算的公式。

好了，问题丢出来了，高手们开始大显身手，结果就出现了下面的情况：

这个公式其实也不算复杂，第一个sumif统计出入库的合计，第二个sumif统计出库的合计，然后相减。相比第一个公式，解决了增加数据区域的问题，在这个问题来说，已经完全满足要求了，可是，有人不满意了，说这个公式太长了，结果就……

sum带上数组横插一脚

注意这个是数组公式，需要按Ctrl、shift和enter。

这个公式的思路与前一个并无差别，只不过是将sumif中的条件使用逻辑值表示，公式整体看上去短了一点，但是从运行效率来说，并不比前一个公式有优势，因为数组的运算速度要低于sumif，当然就这么点数据，根本也感受不到速度方面的差距。

不过sum配合逻辑值与数组，功能方面确实强于sumif，可以解决多种统计问题。就在此时，有人发言，公式还是太长，众人皆惊……

-1^来了，大家瞬间懵了

这就是传说中的神公式啊！

A2:F2这个是实际求和的区域，几个公式中都是一样的表现形式，不多说了；

A$1:F$1=B$1这是一个条件的表示方法，上面那个公式也有用到，不过在这个公式中，少了一个条件就是$A$1:$F$1=$A$1。

其实深思一下，确实只需要一个条件，因为只有两种情况，原来的条件分别判断=入库，和=出库两种情况，实际上在表格中，也只有这两种情况，入库和非入库（出库）。

公式的这部分解释以后也还能理解，重点是-1^怎么解释？

这部分考察的是你的数学功底了，具体来说，-1就是一个数字，^代表幂运算，也就是乘方，-1作为底数，指数就是{0,1,0,1,0,1}，因为任何数的0次方都是1，任何数的1次方就是这个数本身，所以-1^(A$1:F$1=B$1就是{1,-1,1,-1,1,-1}。

关于这段话，你明白就是明白，不明白我也没办法了，去补习数学知识吧。

经过以上解释，公式实际上计算的是=SUM(A2:F2*{1,-1,1,-1,1,-1})，这部分就考察你的数组运算的功底了，简而言之就是入库全部相加，出库全部乘-1相加（实际就是相减，因为sum函数只能进行求和，如果要求差只能是把要减的数字变成负数）……

好了，又啰嗦了一大堆，估计是有人欢喜有人愁，欢喜的人是有了新的收获，愁的人是觉得自己不会的更多了（估计看到一半的时候就晕了）。

其实都无所谓的，站在解决问题的角度，只要学会了sumif的基本用法，这个问题本来就很简单。但是站在探寻函数乐趣的角度，看到神公式一开始都会晕，只要不断的巩固基础知识，慢慢琢磨神奇的思路，即便自己写不出来，能够看明白也是一件乐事！

最后当然是希望想学习Excel的朋友都来老菜鸟的班一起玩咯，qq群搜索老菜鸟的班，就能找到我们了！

看完有收获，别忘了点赞和转发哦~

搜索微信公众号：Excel基础学习园地（id：Excel000000）更多资料供你学习

Excel基础学习园地

公众号“Excel基础学习园地”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号，为了确保您能及时收到每天的教程，还请关注后将公众号设置星标。

加老师微信带你入Excel的坑

验证信息：加Excel交流群
Excel交流群免费加入

公众号回复2016，可以获得office2016的下载链接

今天我们通过一个很常见的编序号问题，来测试一下大家的函数功底，如果基础真的够扎实，解决问题并不需要掌握很多函数，而是能把简单的函数用到极致，正所谓大道至简，返璞归真！

问题如图，根据B列的部门在A列生成一组序号，部门发生变化，序号就增加1：

这个公式的原理讲解见《菜鸟的Excel修炼手册》一书11.4.6节。

点击文章最底部的【阅读原文】可以免费试读本书。

这个公式考察了两个基本功：

1、公式用B2<>B1，来对比B列当前行的部门是不是和上一行的部门名称相同，得到逻辑值TRUE或是FALSE，逻辑值在计算的时候TRUE相当于1，FALSE相当于0；

2、SUM函数可以忽略单元格中的文本，所以这里用了A1做参数并不会得到错误值。

公式解析，以A2单元格中的公式为例：

=SUM(B2<>B1,A1)，B2单元格的“企划”不等于B1的“部门”，因此B2<>B1得到逻辑值TRUE，用它和A1作为SUM函数的参数，SUM函数忽略A1单元格的文本，只计算TRUE，最终得到计算结果1。

到了A3单元格，公式变成：

其他单元格的计算过程也是如此。

这个公式更直接，拿逻辑值直接与上一个单元格相加，至于这个

N(A1)，只是为了将A1的文字变成0而已，否则就会得到错误值，因为文本不能直接拿来计算的。

关于N函数的用法，详见：

【Excel函数教程】揭秘Excel里最短的函数：N函数

这三个公式同样可以解决这个问题，你更喜欢哪一个呢？

老菜鸟一直说的函数基础，就包含了逻辑值的运用，如果基础真的够扎实，解决问题并不需要掌握很多函数，而是能把简单的函数用到极致，正所谓大道至简，返璞归真！

推荐一套函数课程：一套你梦寐以求的全套EXCEL函数技能

本课程共21讲，目录如下：

第01集 函数-相对绝对混合引用

第04集 IS函数家族介绍

第05集 条件格式加函数实现智能标记

第06集 函数常用错误和引用错误

第07集 Text等函数实现文本数值转换

第08集 FIND函数及大小写函数

第09集 截取函数家族应用

第10集 数据智能选择输入

第11集 有关日期格式处理的函数

第12集 隐藏函数算工龄休假

第13集 vlookup精确匹配的典型应用

第20集 条件函数计数应用案例

第21集 利用控件实现多字段下拉动态图表

课程视频可以下载反复观看，原价99元。

本公众号读者现在只需要29.9元即可获得，需要这套视频的扫码购买。

点击下边蓝色“阅读原文”就可以免费试读购买：

如图所示：我想做这样的公式：A列不是有jd1,jd1-1,jd2这样的数嘛，后面对应坐标B，C列。I列有jd和jd1,jd2,jd3这样的值，后面对应坐标M,N列。我想把I列jd1,jd2,jd3的对应的M,N列的坐标... 如图所示：我想做这样的公式：A列不是有jd1,jd1-1,jd2这样的数嘛，后面对应坐标B，C列。I 

你这个只能执行JD10以内的，如JD10，JD11都不能执行，我总共有两三百个JD

你是想把没带“_”的A栏数字复制到I栏中，而带“_”的A栏数字不替换，保留I栏的数字？

  1. 直接将A栏复制到新的栏位，然后将该栏位带“_”的A栏数字替换成空格，或筛选包含“_”的将它清除，也可以查找“*_*”，替换为（什么也不输），再将该栏选择性复制（忽略空格）到I栏即可 

不是，你看嘛，A栏jd1.2.3.4.5是被框了的，被框了的是我自己手动从 I 栏对应的坐标复制粘贴的，我只想换A栏jd1.2.3.4这些整数的坐标，他们的坐标又是从 I 栏对应的坐标来的，怎么换。
看不明白你要换什么，你改用单元格的的坐标来举例比如I1单元格换成A1单元格，这样好理解，我现在理解的是你要将A栏与I栏相同的B、C栏的数字用M、N的数字替换对吧？如果是，如果要在B、C栏存放，还需要在筛选状态下录入公式才可以做到：
先筛选A栏不含“_”的单元格，然后输入公式=VLOOKUP($A4,$I:$N,COLUMN(E4),FALSE)
如果是另外一栏，则不需要筛选，用IF函数来判数取哪个数就可以了：
=IF(ISERROR(FIND("_",$A1,1)),B1,VLOOKUP($A1,$I:$N,COLUMN(M1)-COLUMN($H1),FALSE))


· 贡献了超过313个回答

下载百度知道APP，抢鲜体验

使用百度知道APP，立即抢鲜体验。你的手机镜头里或许有别人想知道的答案。

我要回帖

更多关于 EXCEL基础函数 的文章

 

随机推荐