EXCEL操作及公式汇总

3.符合某一条件单元格计数=COUNT(范围，条件） 分别选取要合并的单元格→点击“合并后居中”→Ctrl+G（定位）空值→编辑栏中输入“=”→点向 上箭头“↑”→ Ctrl+ Enter 假设A1为待拆分，数值B1=--LEFT(A1,2*LEN(A1)-LENB(A1)),单位C1=SUBSTITUTE(A1,B1,) 多表合并 全部工作表快速汇总（分散在12张工作表中全年12个月数据汇总）选中要填充数据的单元格区域，在 连续多工作表汇总（会并汇总的表格位于不同工作表，工作表名称1月工资、2月工资……、12月工 资）选中要填充数据的单元格区域，在左起第一个单元格B2输入=sum(1月工资：12月工资!b2) 多工作表多列数据合并,选中B2单元格输入=indirect（B$2&"!b"）&row 选取多个文件按回车键 需要合并并保留A1：A2单元格内容：先合并B1：B2单元格→选择A1：A2单元格，按Ctrl+C，“复制” →双击合并后的单元格B1：B2，使其进入编辑状态→点击剪贴板，并单击刚才复制的内容→按Enter 1.分类汇总：首先按某一项目进行排序→点“ 数据 - 分级显示 - 分类汇总”→分类字段、选定汇 总项、每组数据分页等勾选 2.分页打印设置：点“页面布局 - 页面设置 - 打印标题 - 顶端标题行，选择第一行的标题” 3.分内汇总合计行颜色标注：→选中整个工作表（A1-H60，且汇总在C列）→开始 - 样式 - 条件格 式 - 新建规则-使用公式确定要设置格式的单元格：=ISNUMBER(FIND("汇总",$C1))； 1.限定输入日期内容和格式：选中日期数据区域→数据 - 数据工具 - 数据有效性 - 数据有效性→ 允许：日期-在“开始日期”内容中，我们输入最早的日期输入-“结束日期”可以截止到当天，我们 2.身份证18位且文本格式输入：数据有效性 - 自定义，在“公式”一栏中输入“ =and(istext(g2),len(g2)=18)”，这个公式的意思是同时满足输入的格式是文本格式并且长度是 18 3.用下拉菜单规范输入内容：例如学历，选中要输入数据的区域→ 数据 - 数据工具 - 数据有效性 - 数据有效性-允许：序列，来源：初中,高中,专科，本科,研究生（“来源”中输入内容时，是以英 4.制作二级下拉菜单 工作簿2复制公式结果是跟工作簿1的公式一样，就是 “=sheet 1!A1”， 而不要前面带有工作簿1的 文件名称。 有2个方法： 方法1 先在AAA文件中，用替换工具（在选项>查找范围>公式）把Sheet2里的"="替换为"CCC=". 复制AAA的sheet2到BBB的sheet2. 用替换工具把CCC=，替换为 = 方法2 复制AAA的sheet2到BBB的sheet2.
序号 1
项目 数据透视表：
2
IF函数：
3
四舍五入ROUND：
5 6
VLOOKUP： 年终奖个税：
7
工资个税：
8
多个e来自百度文库cel工作表批量另 存为单独的工作簿:
9
花名册
10
数据统计COUNT：
10
数据统计COUNT：
11 12
智能填充合并单元格 数值和单位放在一个单 元格
13
多表合并
14 15
打开多个EXCEL文件 保留合并单元格中的所 有数据
16
分类汇总+打印+合计行 颜色标注
17
数据有效性
18
两个工作簿公式的复制
操作公式 1.快捷方式ALT+D+PP→多重数据合并区域→选取数据 2.排序：制作排序表（B1、A2单元格填序号二字，然后列入项目→透视表区域从A1开始先选1-2行项 目添加后，再选1-2列项目添加箭头）→点行序号下面单元格→点自定义排序（选升序+从上到下）→ 点列序号右面单元格→点自定义排序（选升序+从左到右） 例某A1元格计算结果封底数0封顶数15=IF(A1>15,15,IF(A1<0,0,A1))) 1.四舍五入保留两位小数：A1单元格四舍五入保留两位小数=ROUND（A1,2) 2.向下舍入取整数：A1单元格取整数=ROUNDDOWN(A1,0)例如1.99=1 3.向上舍入取整数：A1单元格取整数=ROUNDUP(A1,0)例如1.01=2 A1单元格为姓名，要匹配数据区域A:F的第3列数据=VLOOKUP(A1，A:F注意绝对引用问题，3,0）（若 让未匹配到数字的单元格显示0用IFERROR(公式,0) A1为纳税额（四舍五入保留两位小数）=ROUND((A1+MIN(3500,L65)3500)*LOOKUP((A1+MIN(3500,A1)3500)/12,{0,1500.01,4500.01,9000.01,35000.01,55000.01,80000.01},{0.03,0.1,0.2,0.25,0.3,0 .35,0.45})-LOOKUP((A1+MIN(3500,L65)A1为纳税额（四舍五入保留两位小数）=ROUND(IF(A1>83500,(A1-3500)*0.4513505,IF(A1>58500,(A1-3500)*0.35-5505,IF(A1>38500,(A1-3500)*0.3-2755,IF(A1>12500,(A13500)*0.25-1005,IF(A1>8000,(A1-3500)*0.2-555,IF(A1>5000,(A1-3500)*0.1105,IF(A1>3500,(A1-3500)*0.03,0))))))),2) 点“文件”→“选项”→“自定义功能区”→添加“开发工具”下“查看代码”、“查看宏”→点“ 查看代码”输入： Sub SaveSheetAsWorkbook() Dim theName As String On Error GoTo Line1 For Each sht In ActiveWindow.SelectedSheets sht.Copy theName = ThisWorkbook.Path & ThisWorkbook.Name & "_" & sht.Name & ".xls" ActiveWorkbook.SaveAs Filename:=theName, FileFormat:=xlNormal ActiveWindow.Close Next Line1: End Sub 关闭并选中需要单独另存的工作表→点“查看宏”→选择“SaveSheetAsWorkbook”宏执行即可。 （如不可行请点“选项”→“信任中心”→“信任中心设置”→“启用所有宏”） 1.身份证的输入：先把该列设置为文本型格式，或输入前先输入单撇(')再输入身份证号码 2.性别：A1为身份证号则性别=IF(ISEVEN(MID(A1,17,1)),"女","男") 3.出生日期：A1为身份证号则出生日期=1*TEXT(MID(A1,7,8),"0000-00-00") 4.年龄：A1为出生日期则年龄=DATEDIF(A1,TODAY(),"Y")或A1为身份证号则年龄=YEAR(TODAY())MID(A1,7,4) 5.属相：A1为身份证号则属相=CHOOSE(MOD(MID(A1,7,4)-1900,12)+1,"鼠","牛","虎","兔","龙"," 6.星座：A1为身份证号则星座=VLOOKUP(--TEXT(MID(A1,11,4),"2015-00-00"),{0,"摩羯";42024,"水 瓶";42054,"双鱼";42084,"白羊";42114,"金牛";42145,"双子";42177,"巨蟹";42208,"狮子 ";42239,"**";42270,"天秤";42301,"天蝎";42330,"射手";42360,"摩羯"},2) 7.入职年数：A1是入职日期入职年数=Datedif(a1,today(),"Y") 8.入职月数：A1是入职日期入职年数=Datedif(a1,today(),"M") 1.多条件统计：多条件统计=COUNTIFS(第一个条件区,第一个对应的条件,第二个条件区,第二个对应 的条件,第N个条件区,第N个条件对应的条件) 2.统计A部门有多少人（部门在b列）=Countif(b:b,"A部门")