篇一:Excel根据一个条件将一个表分成几个表
Excel根据一个条件将一个表分成几个表
Sub hjs()
Dim irow, irow1, i, j As Integer
Dim H As New Collection
Dim sht As Worksheet
Dim A
Dim ICol
Sheets(1).Name = "总表"
Set A = ActiveCell
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sht In Sheets
If sht.Name <> "总表" Then sht.Delete '删除所有分表
Next
Sheets("总表").Copy Before:=Sheets(1) '加入新表来操作,以防破坏原数据中的公式或格式
ICol = Application.InputBox("请输入你所要分的列:(如按B列分请输入2)", "提示:", "2", Type:=1)
If ICol = "" Then Exit Sub
On Error Resume Next
With Sheets("总表 (2)")
irow = .[a1].CurrentRegion.Rows.Count
For i = 2 To irow
Cells(i, ICol) = "'" & Cells(i, ICol) '在原工作表生成文本符号
Next
For i = 2 To irow
H.Add .Cells(i, ICol), CStr(.Cells(i, ICol))
Next '建立一个不重复的筛选条件
For i = 1 To H.Count
.Cells.AutoFilter field:=ICol, Criteria1:=H(i)
Sheets.Add(after:=Sheets(Sheets.Count)).Name = H(i)
.[a1].CurrentRegion.Copy Sheets(CStr(H(i))).[a1] '自动筛选,并复制到新建的表中
irow1 = [a1].CurrentRegion.Rows.Count
For j = 2 To irow1
Cells(j, ICol) = Right(Cells(j, ICol), Len(Cells(j, ICol))) '消除新工作表文本符号 Next j
.Cells.AutoFilter
Debug.Print H(i)
Next i
.Delete'操作表此时已多余,故删除End With
A.Parent.Activate '激活汇总表的原来激活的单元格
A.Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
篇二:EXCEL如何按条件显示
EXCEL如何按条件显示
统计学生成绩时,希望输入60以下的分数时,显示为“不及格”;输入60以上的分数时,显示为“及格”。这样的效果,不妨称之为“条件显示”,用IF函数可以实现。假设分数在B3单元格中,要将等级显示在C3单元格中。那么在C3单元格中输入以下公式实现不同的等级: =if(b3<=60,“不及格”,“及格”) 分“不及格”和“及格”2个等级 =if(b3<=60,“不及格”,if(b3<=90,“及格”,“优秀”)) 分3个等级 =if(b3<=60,“不及格”,if(b3<=70,“及格”,if(b3<90,“良好”,“优秀”))) 分为4个等级注意:符号为半角,IF与括弧之间不能有空格,而且最多嵌套7层。
篇三:Excel满足特定条件的单元格进行求和或汇总
Excel满足特定条件的单元格进行求和或汇总 如果要计算单元格区域中某个文本串或数字出现的次数,则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本串或数字求和,则可使用 SUMIF 工作表函数。关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。这里重点介绍COUNTIF的应用。
COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000元以上的员工数。 语法形式为COUNTIF(range,criteria)。其中Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。例如,条件可以表示为 32、"32"、">32"、"apples"。
1、成绩表
这里仍以上述成绩表的例子说明一些应用方法。我们需要计算的是:每位学生取得优秀成绩的课程数。规则为成绩大于90分记做优秀。如图8所示
根据这一规则,我们在优秀门数中写公式(以单元格B13为例):
=COUNTIF(B4:B10,">90")
语法解释为,计算B4到B10这个范围,即jarry的各科成绩中有多少个数值大于90的单元格。
在优秀门数栏中可以看到jarry的优秀门数为两门。其他人也可以依次看到。
2、 销售业绩表
销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。比如,可能希望计算销售人员的订单数,然后汇总每个销售人员的销售额,并且根据总发货量决定每次销售应获得的奖金。
原始数据表如图9所示(原始数据是以流水单形式列出的,即按订单号排列)
图9 原始数据表
按销售人员汇总表如图10所示
如图10所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售人员汇总的表单样式,如图所示。然后分别计算订单数、订单总额、销售奖金。
(1) 订单数 --用COUNTIF计算销售人员的订单数。
以销售人员ANNIE的订单数公式为例。公式:
=COUNTIF($C$2:$C$13,A17)
语法解释为计算单元格A17(即销售人员ANNIE)在"销售人员"清单$C$2:$C$13的范围内(即图9所示的原始数据表)出现的次数。
这个出现的次数即可认为是该销售人员ANNIE的订单数。
(2) 订单总额--用SUMIF汇总每个销售人员的销售额。
以销售人员ANNIE的订单总额公式为例。公式:
=SUMIF($C$2:$C$13,A17,$B$2:$B$13)
此公式在"销售人员"清单$C$2:$C$13中检查单元格A17 中的文本(即销售人员ANNIE),然后计算"订单金额"列($B$2:$B$13)中相应量的和。
这个相应量的和就是销售人员ANNIE的订单总额。
(3) 销售奖金--用IF根据订单总额决定每次销售应获得的奖金。
假定公司的销售奖金规则为当订单总额超过5万元时,奖励幅度为百分之十五,否则为百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式为:
=IF(C17<50000,10%,15%)*C17
如果订单总额小于 50000则奖金为 10%;如果订单总额大于等于 50000,则奖金为 15%。
图10 销售人员汇总表
图8
SUMIF
用途:根据指定条件对若干单元格、区域或引用求和。
语法:SUMIF(range,criteria,sum_range)
参数:Range为用于条件判断的单元格区域,Criteria是由数字、逻辑表达式等组成的判定条件,Sum_range为需要求和的单元格、区域或引用。
实例:某单位统计工资报表中职称为“中级”的员工工资总额。假设工资总额存放在工作表的F列,员工职称存放在工作表B列。则公式为“=SUMIF(B1:B1000,"中级",F1:F1000)”,其中“B1:B1000”为提供逻辑判断依据的单元格区域,"中级"为判断条件,就是仅仅统计B1:B1000区域中职称为“中级”的单元格,F1:F1000为实际求和的单元格区域。
例如:sumif(A1:A20,"a",B1:B20)
意思就是:在A1到A20这个区域中,凡是"a"的就把它的数量求和。如果是不同一个工作表,只要在区域的前面加上工作表的名称就行了,如sumif(sheet1!A1:A20,"a",sheet1!B1:B20)
但要注意的一点就是在判断条件时,条件一定要与字段名相同,就算差一个点或一个空格,公式都无法判断,所以条件与字段名一定要一致
COUNTIF函数(计数求和)
COUNT函数,顾名思义是用来计数的,统计所选择区域的数值型单元格个数。COUNTIF是COUNT函数的引伸与拓展,在计数时加上先前条件,只有符合计数的条件才进行统计计算。比如,从员工信息表中,计算出有多少人的年龄大于35岁。下面我们来看一个典型的分类计数汇总的例子。这里有一张销售流水记录表,每名销售人员累计做了多少“销售订单个数”呢?
COUNTIF正常工作需要两个参数——条件区域(本例为左侧表中“销售人员”一列)和计数条件(本例为右侧表中的人员姓名)。要计算第一位销售人员的“订单数”,考试,大提示很简单,输入函数公式 “=COUNTIF($C$2:$C$16,E2)”即可。
二:SUMIF函数(条件求和)
SUM函数的作用是对数据求和,而SUMIF对它进行了引伸和拓展,比如计算“金额”在1元以上的数据总和、按照人员或产品分类计算数据总和等等。它有3个参数,分别是条件区域、判断条件、实际的求和区域(如果它与“条件区域”是一个区域,就可省略)。
在上例中,计算每位“销售人员”的订单总金额,就要使用SUMIF函数来协助了。如果要计算每个人的销售订单总金额,把左侧表的“销售人员”一列当作“条件区域”,把右侧表的每个名单当作求和“条件”,把左侧表的每笔“订单金额”当作“实际求和区域”,在G2单元格中输入数据计算公式 “=SUMIF($C$2:$C$16,E2,$B$2:$B$16)”,第1名销售人员的“订单总额”就瞬间产生了。
小提示:
在本例的COUNTIF函数和SUMIF函数中,由于“销售人员”区域与“订单总额”区域都是固定的,所以在函数中引用这两列地址时,要使用“绝对地址”,也就是在地址前添加“$”符号。三:IF函数
逻辑高手IF函数是一个条件函数,它可以通过设置的条件进行逻辑判断。如果在刚才的数据汇总表中再添加一列“销售奖金”数据,“销售奖金”发放的方法是:如果某个人的订单总额大于¥1500,000,那么“奖金”数用总额×5%,否则“奖金”就用总额×3%。所以,第1位销售人员“销售奖金”的计算公式应为“=IF(G2>1500000,G2*0.05,G2*0.03)”。
结合刚才的知识,推测一下这个公式中3个参数的涵义吧。“G2>1500000”是IF函数的判断条件,“G2*0.05”是条件成立的操作,“G2*0.03”是条件不成立的操作。
点睛:
*数据的分类汇总是Excel最常见的应用。分类汇总的方法很多,包括使用函数和公式。设置的条件可看作是分类的依据,用“COUNTIF函数”进行分类计数汇总,用“SUMIF函数”进行分类求和汇总,非常方便。
*IF函数的作用是根据判断条件的真假,自动进行分支操作。比如根据身份证号码的奇偶来判断性别,根据绩效数据来填写成绩等等。在实际应用时,一个非常实用的应用是:把IF函数的“真”或“假”参数嵌套成另一个IF函数,实现多种分支操作。如2个IF函数的嵌套可写成“=IF(条件,真,IF(条件,真,假))”,这样一来就可以实现3个分支判断了(在Excel中,最多是7层函数嵌套)。
*重复数据的筛选问题常常让我们感到非常棘手。COUNTIF函数除了可以实现分类计数汇总外,它和IF函数配合,还可以实现对重复数据的标识与筛选,从而将1列中的重复数据删除。使用了公式“=IF(COUNTIF($C$1:C1,C1)>1,”1”,”0”)”为重复数据的后面添加了标识“1”,为不重复数的后面添加了标识“0”。在公式中,COUNTIF是条件计数,可利用它统计出从这列的第1个数据起,某个数出现了几次,再用IF函数判断,若出现2次以上就添加“1”,若出现1次则就添加“0”。最后用Excel中的自动筛选功能,将所有标识为“1”的数据行删除,就能把重复数据统统清理掉了。