|
|
本帖最后由 miceqi 于 2018-7-13 11:09 编辑
~~~~~~~~~~本人初学函数，分享一下学习心得，请各位方家多指教~~~~~~~~~~
大家好，我叫奇奇，是E家刚来不久的一个新成员，请多指教哦~
奇奇开了一家新的早餐店，开店首日就卖出了不少营养又美味的早餐，看着手里花花绿绿的钞票，奇奇高兴极了。
一块，两块，五块……哎呀！奇奇正专心数着今天收到的钱，突然又忘了前面数到多少了！唉，对于小学没毕业的奇奇来说，数数真是太难了！~~(╯﹏╰)b 这可怎么办呀！都快愁死了！……呃，有了！我可以找E家的朋友来帮忙啊！哈哈，我可真聪明~
亲爱的E家朋友们，你们愿意帮助奇奇算一下今天的收入吗？
上面是奇奇首日销售的账单，如果要求所有包子的销售额，相信E家很多人都能快速给出公式，但对于初学函数的人来说，还是有一些难度的。下面来看看我的解法：
【解法一】
首先想到的就是条件求和的公式SUMIF，这个公式的用法是=SUMIF（条件区域，条件，求和区域）。
“包子”就是求和的“条件”，包含“包子”的A3:A7区域即为“条件区域”，“销售额”所在的D3：D7即为“求和区域”。
那么这个公式为：=SUMIF(A3:A7,"*包子",D3:D7) ①，结果为110。
注意这里用到了通配符“*包子”，这样只要是以“包子”结尾的商品名，都是求和的条件。能使用的通配符的函数很多，比如COUNTIF/S,SUMIF/S,MATCH,SEARCH,VLOOKUP,SUM等。
【解法二】
解法一实际上是已经求出了各个商品的销售额，然后根据条件相加。如果实现没有得到销售额（D列）的数据，我们是否能求得所有包子的销售额呢？答案是肯定的。
相信很多人脑袋里已经浮现了一个身影，没错，就是SUMPRODUCT！先相乘，再相加，没有比他更合适的啦!
=SUMPRODUCT((RIGHT(A3:A7,2)="包子")*(B3:B7)*(C3:C7)) ②
或者=SUMPRODUCT((RIGHT(A3:A7,2)="包子")*1,(B3:B7)*(C3:C7)) ③
这里面用到一个文本截取函数RIGHT，用法是RIGHT(被截取的字符，从右边开始截取几位)。RIGHT(A3:A7,2)="包子"即为判断A3:A7从右边开始截取两位文本后是“包子的”单元格，F9结果为：{TRUE;TRUE;FALSE;FALSE;FALSE}。
两个公式展开后为：
=SUMPRODUCT({TRUE;TRUE;FALSE;FALSE;FALSE}*({2.5;2;1.5;1.6;3})*({20;30;25;60;15})) ②’
=SUMPRODUCT({1;1;0;0;0},({2.5;2;1.5;1.6;3})*({20;30;25;60;15})) ③’
②’中，逻辑值直接相乘可转化为数值；③’中，需要先把RIGHT函数得到的逻辑型数组乘以1转化为数值常量数组。
【解法三】
数组公式
说到数组难免让初学者望而生畏，其实E家有很多前辈老师写了不少很好的教程，大家在论坛里搜一下就能得到帮助。
回到问题，我们借助解法二公式②的思路，转成数组公式为：
{=SUM((RIGHT(A3:A7,2)="包子")*B3:B7*C3:C7)} ④ 注意大括号{}是三键（Ctrl+Shift+Enter）结束自动生成的，非手动输入。
展开后：=SUM({TRUE;TRUE;FALSE;FALSE;FALSE}*{2.5;2;1.5;1.6;3}*{20;30;25;60;15}) ④’，怎么样，是不是和②’ 非常像！
我们再深入一下思考：什么是数组，什么是数组公式？②④这两个例子能为我们提供很好的判断依据。
另外，除了用RIGHT函数判断包含“包子”的单元格之外，还能用其他函数判断，例如：
{=SUM(IFERROR(1^FIND("包子",A3:A7),)*(B3:B7*C3:C7))} ⑤
先用FIND函数查找A3:A7区域包含”包子“文本在其单元格中开始的位置，如果没有则返回#VALUE!。FIND("包子",A3:A7)展开后为{2;2;#VALUE!;#VALUE!;#VALUE!}；
再巧妙地用1的多少次方来转化FIND函数查找”包子“位置的数值为1：1^FIND("包子",A3:A7)展开后是{1;1;#VALUE!;#VALUE!;#VALUE!}；
然后用IFERROR函数将错误值转化为数值”0“，IFERROR(1^FIND("包子",A3:A7),)展开后是{1；1；0；0；0}。
这其中的思路和转变，很值得我们咀嚼。
E家的朋友们，奇奇的难题得到了解答，而且不止一种方法。我们也相信还有更多的方法也能帮到奇奇，你有没有兴趣来试试呢？
奇奇的早餐店.zip
(8.54 KB, 下载次数: 4)
|
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2018-7-13 10:01
