Excel实用技巧

环比增长：图表类型为-簇状柱形图



3、设置折线图的标记点显 示方式 ①设置折线图，线条填充样 式为：无线条 ②设置标记点：数据标记选 项为原型，大小为35 填充颜色为：白色 标记表框为蓝色：5磅，线 条类型为粗细结合式 4、设置数据标签及轴坐标。 点击选中次坐标，在设置坐 标轴格式中，更改坐标轴的 最大值为1，即100%；点 击环形图的数据点以后，单 击鼠标右键，选：添加数据 标签；选中数据标签后，在 设置数据标签格式中，更改 标签位置为：居中。
快速复制数据




合同到期提醒
设置条件格式，公式 为： =($C2>TODAY( ))*(C2TODAY()<=7) 使用两个条件对C2 单元格中的日期进行 判断，第一个条件是 大于系统当前日期， 目的是过滤掉已经截 止的合同。第二个条 件是和系统当前日期 的间隔小于等于7。 此处的$C2为混合引 用，行数可以变动， 列数固定。也就是每 一行中的C列日期， 都去和当前日期进行 对比。
Excel實用技巧
Excel
—快捷鍵小技巧

选中单元格，点 击左下角“十”， 按【shift+鼠标 左键】进行拖拽， 插入空行完成
1.快速插入空行
快速輸入當天日期及時間
快速求和：ALT+=

选中数据区 域后，按 Shift+ctr l+1，就可 以将小数快 速转换为整 数。
小数一键变整数

日期和星期同时显示
Excel 2017新功能
—快速填充Ctrl + E
1.合并日期
2.分段顯示號碼
3.身份證提取日期
4.批量添加前綴或後綴
5.順序調整帶括弧
ቤተ መጻሕፍቲ ባይዱ
6.拆分數據
7.混合複雜數據拆分
EXCEL維護 --數據變卡處理
如图，某个excel文件， 数据内容不多。 但是文 件本身却变得很大，打开 很慢。别的EXCEL文件 打开很快打开，这有这个 文件打开需要20秒左右， 另外在这个文件中进行其 他操作例如查找替换等操 作时候，处理起来也很慢。 导致此现象的原因一般是 不小心添加了一些不可见 的自选图形等对象。我们 使用以下的方法来完成检 查。点编辑菜单中的定位 命令。或者直接按快捷键 CTRL+G，或者按F5


5、设置图 表标题，删 除冗余刻度 线条 如果觉得坐 标轴的数值 比较多的话， 可以通过设 置坐标轴格 式，更改主 单位的大小 进行调整。
图表类型-仪表图
图表类型-动态图
图表类型-看板
快速匹配及數據對比
—VLOOKUP函數
根据姓名匹配职位、身份证号等信息，总不能一个一个查找复制，耗时 耗力，还经常容易出错。因此要用VLOOKUP函数查找引用数据，这也 是Excel中使用最频繁的操作

先定位到有效表 單区域任意位置， 然后使用快捷键 「Ctrl + A」， 即可快速选定该 区域所有数据。 工作薄中多表單， 兩表單全選 可用 例「Ctrl + Shift + 空格键」
快速選定有效數據區域
多單元格快速統一錄入

按照原表格空 出对应位置， 然后复制数据， 「选择性粘贴」 -「跳过空单 元格」即可。



3、30~40岁总人数 =COUNTIFS(F:F,">=30",F:F,"<40")
提成比率计算 =VLOOKUP(B3,$C$12:$E$21,3)


2、个人所得税计算 假如A2中是应税工资，则计算个税公式为： =5*MAX(A2*{0.6,2,4,5,6,7,9}%{21,91,251,376,761,1346,3016},)
婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元 =SUM((B9:AE9={"事";"旷";"病";"丧";"婚 "})*{30;50;20;10;10})
员工数据分析公式（以此圖為數據源）


1、本科学历人数 =COUNTIF(D:D,"本科")
2、办公室本科学历人数 =COUNTIFS(A:A,"办公室",D:D,"本科")
SUMIFS多條件求和2
10.对合并单元格求和
13.按特定顺序排序
15.对混合文本中的数字进行排序
多角度分析数据
—樞紐分析表
1.多角度分析数据
2.更直觀的分析数据
3.1 排名
3.2排名
4.让更改的数据源随时刷新
5.手工组合实现客户分级
6.銷售額分析
7.数据透视图分析

选中数据透 视表中任一 数据—— 【分析】选 项卡—插入 切片器—右 键单击切片 器—报表连 接—勾选需 要控制的多 个表格。
多个数据透视表
综合运用上面的功能，一张人员基本情 况分析的看板就实现了
以此表做為數據源來做透視數據分析
各部门人数占比
各个年龄段人数和占比
各部门学历统计
按年份统计各部门入职人数

只需在单元 格中输入【= 第一位數字 **一共有幾 個零】
快速錄入長數值
空白處快速求和
快速刪除空值

选中数据单 元格，点击 【开始】选 项卡--【条 件格式】-【数据条】， 让单元格直 接嵌上了类 似于“柱状 图”的效果。
数据管理的小闹钟：条件格式

按住ctrl,出 现+号后移动， 就可以复制 数据到指定 区域了
6.填充合并单元格内容
12.批量复制
4.正確錄入身份證號碼


正确的方法是，全 选已经处于科学记 数法的单元格，右 击并选择“设置单 元格格式”，接下 来在弹出对话框中 选择“数字” → “分类”→ “自定义” 将默认的“G/通用 格式”更改为“0” 即可。
恢复超11位数字
快速核对excel表格

显示美元： Ctrl+shift+$ 显示人民币： Ctrl+shift+￥ 显示百分比： Ctrl+shift+% 切换工作表： Ctrl+PgDn / PgUp 快速转换单元格：Ctrl+ 方向键 横纵向选择单元格： Ctrl+shift+方向键
快捷键总结

Alt+F1一秒 生成图表， 默认生成柱 形图后，可 以再修改成 其他图表类 型

选中要设 置斜线的 单元格， 设置单元 格格式— —边框— —点击斜 线即可
快速设置表格斜线

选中单元格 （可以选中 需要打√或 ×的一列）， 设置单元格 格式，自定 义代码为 [=1]"√";[ =2]"×"
在excel中快速输入"√""× "
Excel
—函數公式小技巧
Match： 查找到你的位置 Index： 提取出相应位置 的数据

查找界的王者： Index+match+数据有效性
员工信息表公式（以此表為數據源）


1、计算性别(F列) =IF(MOD(MID(E3,17,1),2),"男","女") 2、出生年月(G列) =TEXT(MID(E3,7,8),"0-00-00") 3、年龄公式(H列) =DATEDIF(G3,TODAY(),"y") 4、退休日期 (I列) =TEXT(EDATE(G3,12*(5*(F3="男 ")+55)),"yyyy/mm/dd aaaa")
5、籍贯(M列) =VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,) 注：附带示例中有地址库代码表


6、社会工龄(T列) =DATEDIF(S3,NOW(),"y")
7、公司工龄(W列) =DATEDIF(V3,NOW(),"y")&"年 "&DATEDIF(V3,NOW(),"ym")&"月 "&DATEDIF(V3,NOW(),"md")&"天" 8、合同续签日期(Y列) =DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3), DAY(V3))-1



9、合同到期日期(Z列) =TEXT(EDATE(V3,LEFTB(X3,2)*12)TODAY(),"[<0]过期0天;[<30]即将到期0天;还早 ")
10、工龄工资(AA列) =MIN(700,DATEDIF($V3,NOW(),"y")*50) 11、生肖(AB列) =MID("猴鸡狗猪鼠牛虎兔龙蛇马羊 ",MOD(MID(E3,7,4),12)+1,1)
VLOOKUP精确匹配
VLOOKUP模糊匹配：不再为等级匹配 发愁，取代if多层嵌套
1.定位空值匹配填充

使用VLOOKUP函数快 速查找匹配数据 输入公式 =VLOOKUP(A11,$B $2:$C$8,2,0)
2.输入姓名找年龄

选中排班日期区域，然 后在依次打开【字体】 属性，然后选择【数字】 下的【自定义】，添加 「aaaa」

單條件求和：SUMIF&多條件求和WUMIFS
條件求和


以求数学成绩 大于（包含等 于）80分的 同学的总分之 和： 公式如下： =SUMIF(C2 :C22,">=8 0",I2:I22)