Excel函数太多太难学,汇总总括与计算

问题:怎么落到实处在B2输入排量,在C8自动呈现车船税的金额?(一次费改后的金额)谢谢赐教!

招来函数

  • vlookup
![](https://upload-images.jianshu.io/upload_images/5329960-0da2309f02f4fb81.png)

vlookup

<p>vlookup有多个参数,如上表所示

  • 首先个是参照值,正是五个表相关联的地点
  • 其次个是2个表,你要给函数2个追寻范围
  • 其四个就是2个数字,代表表里面第几列是您要物色的数额
  • 第四个是布尔值,用0精确相称

皇家赌场网址hj9292 1

示例vlookup.png

  • 求全年累计,工号相对
    在I4处输入=vlookup(H4)//查找100003
  • 输入表A2:F12//在这么些表里面查询
    <b>注意相对引用</b>
    明显表的限定后按<b>F四</b>,不然往下填充的时候表的界定会转移,按后改成$A$二:$F$1二(澳元符号)
  • 第5列是<b>大家给的表</b>的数码全年共计
    其多个参数为陆
  • 第五个不讲,暗中同意为0

输入=vlookup(H4,$A$2:$F$12,6,0)
<p>好了,其实函数也给了作者们提示,看英文

  • lookup_value 要找的值
  • table_array 表格数组
  • col_index_num 第几列的目录的数字值
  • 不用讲了,直接0

 (Excel)常用函数公式及操作技能之陆:

许多恋人觉得Excel函数太多了,而且每一个都有区别的接纳条件,根本记不苏醒。

只要您也有同壹的感受,不想学习太多的函数公式,那么也终将要学习的函数正是-SUMPRODUCT。

无论是标准求和、条件计数、加权平均,那些常用的计算测算它都能做获得!还在为函数太多记不住发愁呢?快来学习它吧~~

后天天津大学学纲:

一 SUMPRODUCT基础运用

贰 SUMPRODUCT多规格计数

③ SUMPRODUCT多规格求和

四 计算加权平均值(权重已知)

五 总括加权平均值(权重未知)

六 交叉查询

7 分组排行

SUMPRODUCT函数的办事原理为:

在给定的几组数组中,将数组间对应的因素先相乘(PRODUCT),后相加(SUM)。只是就凭那一个相乘、相加的估量,就能促成广大效应。

语法

SUMPRODUCT(array1, [array2], [array3], …)

其中:

array一 必需,它是对应成分必要开展相乘并求和的率先个数组参数。

array二, array三,… 为可选。可以是 二 到 ②五十四个数组参数,其相应成分要求实行相乘并求和。

数组参数必须持有同样的维数。 不然,函数 SUMPRODUCT 将再次来到 #VALUE!
错误值 #REF!。

回答:

求和函数

  • sumifs

皇家赌场网址hj9292 2

sumifs.png

<p>5个参数

  • sum_range 必要求和的实际单元格
  • Excel函数太多太难学,汇总总括与计算。critica_range一 总括关联条件的率先个区域
  • critica1 第四个区域满意条件
  • critica_range二 总结关联条件的第壹个区域
  • critica二 首个区域满意条件

皇家赌场网址hj9292 3

示例sumifs.png

<p>求男士语文成绩的和

  • 对语文战绩求和,语文战表范围为C2:C8
  • 供给性别为男子
    在性别里面 B二:B8
    标准为男人 文本”男” 记得引号,且在英文标点下
    =sumifs(c2:c8,b2:b8,”男”)

<p>同理,求语文和数学得分都当先等于捌拾陆分的学员总分之和
在G四单元格输入公式=SUMIFS(F二:F八,C二:C捌,”>=90″,D二:D八,”>=90″)
求和总分,语文里面//大于90,数学里面//大于90


  • sumprounduct
    <p>语法:SUMPRODUCT(array1,array2,array3, …)
    Array壹,array2,array三, … 为 二 到 二二十一个数组,其对应成分供给实行相乘并求和。///相乘再相加
    <p>条件求和:求丹佛仓库的出货量
    函数关系式:=SUMPRODUCT((C一三:C1玖=”塔林”)*(D13:D19))
    求解的是塔林仓房的出货总量。

皇家赌场网址hj9292 4

sumproduct.png


汇总计算与总括(1)

一、基础运用

SUMPRODUCT函数的最典型应用:

上面例子中有单价和数码,我们渴求具有产品的总价。

在那之中A1二单元格中的公式为:

=SUMPRODUCT(B3:B9,C3:C9)

公式所起到的效果,就像是自家在两旁深青莲虚框中标注的如出一辙,先将B三到B九中的每3个数,分别与C3到C九中的数相乘。然后再将这么些乘积实行加和,就获取了我们要的结果。

世家好,笔者是@EXCEL实例录制网址长@欢迎关切本人的头条,每日都会分享Excel实战技巧!

排序函数

  • rank

皇家赌场网址hj9292 5

rank.png

Excel函数太多太难学,汇总总括与计算。<p>假如A列从A一单元格起,依次有数量80、玖八、陆五、7九、陆五。
<p>在B1中编辑公式
=RANK(A1,$A$1:$A$5,0)
从B一单元格起依次重临值为二、壹、四、三、4。
<p>在C1中编辑公式
=RANK(A1,$A$1:$A$5,1)

此时从C一单元格起依次再次来到的值是四、五、一、三、一

  • 供给排名的数值
  • 急需排行的限定
  • 排序形式,0为正序,一为倒序

本条相比容易,不做详细探索

——通过文化共享树立个人品牌。

2、多规格计数

求战绩超乎七十九分的女子的数据:

咱俩总结有八个规范:

① 成绩,也就是D列的数,要大于80;

② 性别,也就是C列,为“女”。

D一3单元格中的公式为:

=SUMPRODUCT(($C$3:$C$11=$C$3)*($D$3:$D$11>80))

公式中,

$C$三:$C$1一=$C$三表示,C3到C1一区域等于C三,也便是“女”。符合条件的,会再次来到“TRUE”(也就是壹),不符合条件的,重回“FALSE”(相当于0).

$D$三:$D$1一>80,同理,那多少个单元格“战绩”大于80的,重临“TRUE”(一),不符合条件的,再次来到“FALSE”(0)。

然后七个数组分别相乘。

1*0=0

0*0=0

1*1=1

就取得上面第壹列的结果。

接下来在将那壹列结果开始展览相加,就获取二.

(此总括进程,上面几个例证很1般,就不再赘述)


取整函数

  • round

皇家赌场网址hj9292 6

round.png

4舍5入,没什么可说的
<b>注意第1个参数为小数位数,其他见图

 

3、多规格求和

上边求和的例子,跟计数的界别就在于,最后多乘三个数组。

D1三单元格中的公式为:

=SUMPRODUCT(($C$3:$C$11=$C$3)*($D$3:$D$11>80)*($D$3:$D$11))

也正是将刚刚的列再与“战表”那一列,D叁:D1一举办相乘,然后在进展加和,便可获得。

其1是典型的多规格取数,阶梯条件,你首先要重新整理条件为行业内部格式,按本身上面方法操作即可

字符串函数

  • left

皇家赌场网址hj9292 7

left.png

<p>LEFT( string, n )
<p>能够很便宜的去取文本的值,n是取多少个,left是从左侧去取,right是从左边去取
<p>假如A一=安徽省合肥市固镇县杨庙乡,则公式“=LEFT(A一,FIND(“省”,A一))”重临云南省。
<p>还有mid函数

皇家赌场网址hj9292 8

middle.png

皇家赌场网址hj9292 9

mid.png

留意字符串函数的妙用

 

四、加权平均值(权重已知)

加权平均值的用法也很广泛。例如期末成绩由上面三有些构成,每一有的的权重分别为五分一,30%,八分之四。

E肆单元格中公式为:

=SUMPRODUCT(B4:D4,B$3:D$3)

与在此之前例子不一致的是,本次是分化行之间先相乘,然后再把列进行相加。

B$3:D$三用了“混合引用”,目的是为着保障公式向下复制时,这1行数据的引用不会爆发变化。

EXCEL–多口径取值终极解法SUMPRODUCT高级应用

上次牵线了一个lookup举办多规格判断取值的解法,但是后天黑马意识那多少个解法有个沉痛的通病,正是只好解a<=A<b,就是当条件都以凌驾等于较小值且小于较大值的情景,明天又有人问了二个a<A<=b
及超越较小值而小于等于较大值的多规格
这么些公式在节点上就判断不准了,笔者又实在不想用if函数去做,嵌套一大堆得if
瞧着都累,先后试验了vlookup,Hlookup,match,发现它们的内在规律都以1律的
不可能只好请出SUMPRODUCT大神了

实例如下

输入500>=a1>0 的数的时候,B一展示为A1*0.1

输入2000>=A1>500 B1显示为A1*0.2

输入5000>=A1>2000 B1显示为A1*0.3

输入20000>=A1>5000 B1显示为A1*0.4

有人会说 那还不不难那 几个if就足以了 恭喜你 答对了! 然而作者未来要做的是
即使有N个以上那样的条件 你还能够if么,那公式写出来或然累死你
而且if只可以嵌套七层的,其实作者最想做的是将公式简化
简化成简单理解不难选拔的款型 废话少说 伊始分析

1、本难点基本是A一*X,X的取值依照A1的限量变更,好了 核心正是求X

二、抽出A壹口径和X对应结果 不难得出以下数列,

123>0<=5000.1>500<=20000.2>2000<=50000.3>5000<=200000.4

到后天 事情就变得相比较简单的 那几个难点抽象成数学逻辑就改为了

对1,2列数量开展逻辑与运算 其结果再乘以3列数据
由于唯有壹行的数据会符合供给 而别的行注定会等于0
所以对每行的演算结果求和 就是最后的X取值了

说了一大堆理论 估计很多少人看不懂 笔者表达能力实在点儿啊 看不知底的就略过啊

上边的下结论 最契合的公式就是SUMPRODUCT了 那么些函数其本质正是数组求和
实际上可以分解成sum函数的数组公式
可是它的亮点就是用平常公式达成了数组公式的功力
在那里它最大的效力正是进展逻辑与运算和数组求和 公式其实挺简单

纯数字版的:

=SUMPRODUCT((A1>{0,500,2000,5000})*(A1<={500,2000,5000,20000})*{0.1,0.2,0.3,0.4})

别看那觉得吓人 其实您多少个括号的值和地点的数列一相比较 就相当粗略了

实在行使的时候 能够用单元格区域代表{}例如

00.15000.220000.350000.420000

那是F九:G一三的单元格区域 公式就相应改为

=SUMPRODUCT((A1>$F$9:$F$12)*(A1<=$F$10:$F$13)*($G$9:$G$12))

此地 当你的标准化愈多时 一样能够套用这几个公式 只要把标准区域做相应变更即可

到此截止 那即便出了对应X的值 剩下的便是在公式前边加上A1*即可了

实例截图如下

皇家赌场网址hj9292 10

SUMPRODUCT实乃excel函数中大神 平常能够用它来达成部分不堪设想的天职

回答:

皇家赌场网址hj9292 11

不知底怎么图被吃了一张…

回答:

用vlookup匹配应该可以化解难点。

说其实的,供给的话私信,公布格给本人,方便交换难点。那样抛出几张图,录入数据的时间都急需好长。

欢迎关切自个儿的头条号,假如有excel方面包车型大巴题材,能够私信交换,为您回复解惑。

日子函数

<p>Month
函数指重返一个Variant(Integer),其值为1到1二时期的整数,表示一年中的某月
<p>mintue()
<p>hour()

<p>先介绍到那里,聪明的你学会了呢?

个人收入调节税公式

=MAX(($A1-1900)*{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45}
-{0,0,25,125,375,1375,3375,6375,10375,15375})
{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45} 为税率
{0,0,25,125,375,1375,3375,6375,10375,15375}  为税收扣除数

5、加权平均值(权重未知)

此处的权重未知,指的是从未1个显然的比重,唯有三个万万的多少。

诸如此类的情景也很好处理,大家只需用跟上个例子1样的进度,让“数量”和“花费”分别相乘后再相加,然后在除以“数量”的和即可。

图中A玖单元格中的公式为:

=SUMPRODUCT(B3:B6,C3:C6)/SUM(B3:B6)

上列公式的简化式 :

=MAX(应纳税所得额*0.05*{1,2,3,4,5,6,7,8,9}
-25*{0,1,5,15,55,135,255,415,615},0)

陆、交叉查询

陆续查询用在那边能够说又是一神作了。

平时我们耳熟能详的消除格局是用INDEX+MATCH函数来达成。

唯独,万能的SUMPRODUCT也如出1辙能行。

下图中,大家供给“超级市场叁”“火龙果”的标价。

D一3单元格中的公式为:

=SUMPRODUCT(($B$4:$B$9=B13)*($C$3:$G$3=C13)*($C$4:$G$9))

前两段分别看很好领会,正是判断是或不是符合条件,符合的回到”TRUE”,不切合的归来”FALSE”。

唯独,它俩分别是2个列数组和贰个行数组,相乘后得到的是四个2维的矩阵。

($B$4:$B$9=B13)*($C$3:$G$3=C13)

如下图:

然后,再把($C$4:$G$9),那一个区域的每1个单元格,分别与地点的结果相乘。

聊起底在将兼具的数相加,就收获玖.八.

算物价的函数

物价的不得了三7作5,二舍八入的倒数处理,做1个函数。正是小数点后边第一个人借使是一,贰的就舍掉,假如是3,4,五,陆,柒的都变为伍,假诺是八,九的小数点第一个人加1,第一个人就变为0。比如价格是3.3二、三.3一,作倒数处理便是三.三;价格是3.33、叁.3四、三.3陆、叁.三7,做尾数处理就是叁.35;价格是三.3捌、三.3九,做倒数处理正是三.4。

=CEILING(A1-0.02,0.05)

都是几位小数 

B2=ROUND(2*A2,1)/2

跨越四位小数 

B2=ROUND(2*ROUNDDOWN(A2,2),1)/2

七、分组排行

上面那些例子,”小组”那1列中有五个例外的值,“1组”和“二组”。将来大家要本着“销售额”按区别的组开始展览排行。

D叁单元格中公式为:

=SUMPRODUCT(($B$3:$B$10=B3)*($C$3:$C$10>C3))+1

($B$三:$B$拾=B三)不解释了

($C$3:$C$10>C3)尽管拿装有的“销售额”跟C三(那里是相对引用,向下复制时会变成C4,C5,……)比,比它大的会重返TRUE。

只是那样壹来排第三的数,是尚未比它大的,会重返0。所以大家在公式的终极面有个“+一”,那就改为大家想要的“排行”的样板了。

写了如此多,从来像是自说自话,

不然我们也来点互动?

在评论区告诉本人,

你最想学习的Excel知识是如何?

-END-

自动总计应收款滞纳金

须要在给定的应收日期、滞纳金率、当今天期(自动取)的基本功上自行测算出应收滞纳金。

解答:

=(DATEDIF(应收日期,NOW(),"d"))*滞纳金率(每天)*应收金额

淘汰率

题材如下:那一个工厂有1000人,前天抽出十二个人来做调查,那12个人1天的产量分别为101
10二 10伍 拾陆 玖八 玖5 九陆 十四 110 拾3 (A三-A1贰)。

一千人中间淘汰率为伍%,以那十一个人为规范那么那1000人他们的生育应该为多少才不会被淘汰,看看函数的帮带就领悟了哟,重临数组K百分比值点,你要一千人淘汰陆位就是百分之五十00=0.5%=0.00五,就是你以这12个抽样侦察的多寡为规范,只要产量达标这么些数就不会被淘汰了。(95.45)

公式=PERCENTILE(A3:A12,G1)

使用公积金的一个函数

笔者小卖部职工公积金比例为二六%
也正是私有和单位各一3%,给公积金投缴职员构建了2个函数。直接用基数乘以比例
基数*比例=投缴额, 对于投缴额的需求是:取最接近“投缴额”的偶数。

自家制作的函数是“=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),CEILING(A1*B1,2))

注:A一=基数 
B1=投缴比例

    也足以改成这么

=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),INT(A1*B1)+1)
或=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),EVEN(A1*B1))

什么利用公式将数值转为百分比格式

如用公式将一.289675出示为12八.九柒%,不是用格式来完成的。

=ROUND(B1*100,1)&"%"

比高得分公式

=RANK(B4,$B$4:$B$26,1)

电动评定奖级

=VLOOKUP(L179,IF({1,0},$D$204:$D$207,$B$204:$B$207),2)
=LOOKUP(L179,{0,4,7,12,24},{"一等奖","二等奖","三等奖","纪念奖","纪念奖"})

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

对含有单位的数额怎样进展求和

在数据后必须投入单位,到结尾还要总计总和,请问该怎么着自动求和?(例如:A一:2KG,A贰:陆KG…..,在终极一行自动测算出总KG数)。

=SUMPRODUCT(--LEFT(A1:A5,(LEN(A1:A5)-2)))&”KG”

对a列动态求和

能够趁机a列数据的增添,在“b一”单元格=sum(x)对a列动态求和。 

=SUM(OFFSET(A1,0,0,COUNTA(A:A),1))

动态求和公式

自A列A一单元格到近期行前边一行的单元格求和。

=SUM(INDIRECT("A1:A"&ROW()-1))

列的跳跃求和

若有20列(唯有壹行),需没间隔三列求和,该公式怎么做?

要是a一至t一为多少(共有20列),在肆意单元格中输入公式:

=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))

按ctrl+shift+enter甘休即可求出每隔叁行之和。

皇家赌场网址hj9292,跳行设置:如有1贰行,需每隔三行求和

=SUM(IF(MOD((ROW(1:12)),3)=0,(A1:A12)))

有规律的隔行求和

渴求正是在安插、实际、差距三项中对后边的11个月求和。

=SUMPRODUCT(--(MOD(COLUMN(F3:AO3)-CELL("Col",F3)+0,3)=0),F3:AO3)
=SUMIF($F$2:$AO$2,C$2,$F3:$AO3)
=SUMPRODUCT((MOD(COLUMN($F3:$AO3),3)=MOD(COLUMN(F3),3))*$F3:$AO3)

也足以拖动填充,插入行、列也不影响总括结果。

什么样落到实处奇数行或偶数行求和

设若数据在A1:A100

奇数行:=SUMPRODUCT(MOD(ROW($A$1:$A$100),2)*$A$1:$A$100)

偶数行:=SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=0)*($A$1:$A$100))

奇数行求和 =SUMPRODUCT((A1:A100)*MOD(ROW(A1:A100),2))

偶数行求和 =SUMPRODUCT((A1:A100)*NOT(MOD(ROW(A1:A100),2)))

单数行求和

隔行求和用哪些函数,即:A一+A叁+A5+A七+A玖…公式怎么样用。

{=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))}

{=SUM(IF(MOD(ROW(A1:A100),2)=1,A1:A100,0))}

总计偶数单元格合计数值

总计F四到F6二的偶数单元格合计数值
{=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))}

隔行求和公式设置

均为数组公式:

=SUM(IF(MOD(ROW(A1:A110),2),A1:A110,0))
=SUM(N(OFFSET($A$1,ROW(1:55)*2-2,,,)))
=SUM((MOD(ROW(A1:A100),2)=1)*(A1:A100))
=SUM((MOD(ROW(A1:A100),2)=0)*(A1:A100))
=SUMPRODUCT((MOD(ROW(A1:A100),2)=0)*A1:A100)

隔列将同样体系实行求和

隔列将出勤日和工资分别开始展览求和数组公式

=SUM(IF(($B$4:$B$25)=B26,($C$4:$C$25),0))
或;
=SUMPRODUCT(--(MOD(ROW(C5:C25),2)<>0),C5:C25)

隔行或隔列加总

隔2列加总

=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)

隔2栏加总

=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)

借问怎么在一百行内做隔行相加

数组公式

A1+A3+……+A99    单

=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))

A2+A4+……+A100   双

=SUM(N(OFFSET(A1,ROW(1:50)*2-1,)))

怎么着将间距一定的列的数额相加呢

赶上十0多列的数量将间隔一定的数目用手工业相加太烦了,也便于失误。假诺供给相加的数额均有雷同的称号(字段),能够用Sumif()来求解,借使未有,就供给用数组公式来缓解了。{=SUM((MOD(ROW(A1:A18),3)=1)*A1:A18)}   1、4、7……行相加。

隔列求和(A、B列)

=SUM(A:A,B:B)
=SUM(A:A,B:B,C:C)   (统计A、B、C列)

隔列求和的公式

花色及日期

1月1日

1月2日

1月3日

1月4日

1月5日

余额

 

 

A

1

1

2

5

3

2

7

9

8

1

3

=SUMIF($B$2:$K$2,"进",B3:K3)-SUMIF($B$2:$K$2,"出",B3:K3)
=SUM(SUMIF(B$2:K$2,{"进","出"},B3:K3)*{1,-1})

隔列求和

类别

产品代码

 单价

安贞

北辰

长安

长春

合计

库存

销售

库存

销售

库存

销售

库存

销售

库存

销售

皮带

V19201

  270.00

1

2

1

2

1

2

1

2

 

 

库存合计=SUMIF($D$3:$BS$3,"库存",$D$4:$BT$4),
销售合计=SUMIF($D$3:$BS$3,"销售",$D$4:$BT$4)
=SUMIF($D$3:$BS$3,BT$3,$D4:$BS4)
=SUMPRODUCT((MOD(COLUMN($D4:$BS4),2)=0)*$D4:$BS4)

关于隔行、隔列求和的标题

隔2列加总
=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)   
隔2行加总
=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)
均为数组公式。

EXCEL中求两列的呼应成分乘积之和

如:a1*b1+a2*b2+b3*b3...的和

=SUM(A1:A3*B1:B3) (数组公式)

=SUMPRODUCT(A1:A10,B1:B10)

计算900~一千之间的数值之和

sumif函数的盘算格式为: =sumif($a$1:$a$20,”>1000″)。即再次来到$a$1:$a$20中高于一千的数值的和,但一旦想总计900~一千之间的数值之和,应该什么编写。

请参考:{=SUM(IF((A1:A20>900)*(A1:A20<1000),A1:A20))}{=SUM((900<A1:A20)*(A1:A20<1000)*A1:A20)}

2、=SUMIF(A1:A20,”>900″)-SUMIF(A1:A20,”>1000″)

双准绳求和

壹、 求壹班女人的个数 :

=SUMPRODUCT((A2:A9=1)*(B2:B9=""女""))

二、求壹班战表的和 : 

=SUMIF(A2:A9,1,C2:C9) "    

叁、求一班匹夫成绩的和 : 

=SUMPRODUCT((A2:A9=1)*(B2:B9=""男""),C2:C9) "    

 如何落到实处那样的基准求和

求型号中含BC但不含ABC的量:

A

  B

C

 

 

型号

数量

 

1

CRVABC12

100

 

2

CVABC13

102

 

3

CVBC12

104

 

4

CNVBC13

106

 

=SUMIF($A$2:$A$12,"*"&"bc"&"*",$B$2:$B$12)-SUMIF($A$2:$A$12,"*"&"abc"&"*",$B$2:$B$12)

=SUMPRODUCT((ISNUMBER(FIND("BC",A2:A12))<>ISNUMBER(FIND("ABC",A2:A12)))*B2:B12)

A1:A十数字显为文本格式时,怎么样求和

=SUMPRODUCT(A1:A10)

求和

怀有本范例所使用的数据都为引用以下草绿区域,并定义为对应的标题。         

Name

Sex

Age

Position

Salary

张无忌

26

主角

10000

韦小宝

16

主角

13000

灭绝

55

配角

3000

周芷若

22

主角

8000

鳌拜

62

普通演员

2000

仪琳

18

配角

5000

岳灵珊

19

配角

4500

令狐冲

27

主角

15000

性空

88

普通演员

2200

东方不败

不详

45

主角

9000

A    求全体歌唱家报酬总额                          

71700    =SUM($G$7:$G$16)

一言以蔽之求和

B    求男歌唱家薪金总额  

42200   
=SUMIF($D$7:$D$16,”**
男”,$G$7:$G$16)**

       单条件求和.一

C   求年龄在20岁以下的扮演者薪资 

22500    =SUMIF($E$7:$E$16,”<20″,$G$7:$G$16)

       单条件求和.二

D   求主演和配角的工薪(不是司空见惯影星)

67500    =SUMIF($F$7:$F$16,”***角”,$G$7:$G$16)**

       单条件求和.3

E    求20岁以下女艺员薪俸      

9500      {=SUM(($D$7:$D$16=”**女”)*($E$7:$E$16<20)*$G$7:$G$16)}**

       多规格求和-同时知足条件

F    求男性或骨干的薪金     

59200    {=SUM(IF(($D$7:$D$16=”**男”)+($F$7:$F$16=”主角”),$G$7:$G$16))}**

       多规格求和-只须满意条件之一

G   求男性非主角或骨干非男性的薪金(即除男一号外的男性和主演) 

g.1  21200    {=SUM(IF(($D$7:$D$16=”**男”)-($F$7:$F$16=”主角”),$G$7:$G$16))}**

g.2  21200    {=SUM(IF(($F$7:$F$16=”**主角”)-($D$7:$D$16=”男”),$G$7:$G$16))}**

       多规格求和-只满意条件之一而不可能而且满足

H    啊~~~你不明了怎么是数组函数啊,但是你有时候也要用多规格求和?  

       无妨,教你用其余的主意:SUBTOTAL    

       求20岁以下女艺职员和工人资      

71700    =SUBTOTAL(9,$G$7:$G$16)

       未来你看到的还不是终极结果,请按如下操作   

       一、把数据区域设置成可筛选     

       2、把SEX筛选成”=女”, 把年纪筛选成<20   

       三、你再看下面的公式结果…     

去掉在那之中四个最大值和四个非常的小值,再求和

请问怎么着去掉四个最高分,几个最低分,剩余人士的分数求和,例如A一-A七中的多少个分,去掉五个最高分,多个最低分,剩余名员的分数求和。

=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2) 

=SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))

=TRIMMEAN(A1:A7,4/7)*(7-4)

=SUMPRODUCT(LARGE(A1:A7,ROW(A1:A7))*(ROW(1:7)>2)*(ROW(1:7)<6))

=SUMPRODUCT((A$1:A$7<LARGE(A$1:A$7,2))*(A$1:A$7>SMALL(A$1:A$7,2))*A$1:A$7)

=SUM (A!:A7)-LARGE(A!:A7,1)-LARGE(A!:A7,2)-SMALL(A!:A7,1)-SMALL(A!:A7,2)

将此函数横着使用(A一-G一)

=TRIMMEAN(A1:G1,4/7)*(7-4) 

=SUMPRODUCT(LARGE(A1:G1,COLUMN(A1:G1))*(COLUMN(A:G)>2)*(COLUMN(A:G)<6))

去掉七个最高分、最低分,呈现出被去掉的分数

被去掉的分数:

最大多少个:=large(data,{1;2})

小小的八个:=small(data,{1;2})

固定的求和

1、=SUM(OFFSET(A1,,,ROW()-ROW(A1)))可以对A列数值自动求和。

2、=SUM(INDIRECT("R2C:R[-1]C",FALSE))

3、=SUM(INDIRECT("A2:A"&ROW()-1))

=SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))

按字体颜色求和

做法:

G3={SUM(IF(($A$2:$A$19=E3)*($B$2:$B$19=F3),$C$2:$C$19))}

G4:G11公式为G3公式下拖.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

怎么分班计算男女子数

男=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))

=SUMPRODUCT(($B$2:$B$446=E3)*($C$2:$C$446=F$1))

=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))

{=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))}

{=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)}

女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1))

合计=COUNTIF($B$2:$B$446,E2)

 

Leave a Comment.