返回目录:excel表格制作
一、无论你删除、隐藏、筛选,你的序号永远按顺序显示不变
(一)删除行后,序号顺序不变。
技巧:在"A2"中输入公式"=ROW()-1",双击A2表格右下角+,填充公式。
(二)隐藏行后,序号顺序不变。
技巧:在"A2"中输入公式"=SUBTOTAL(103,B$2:B2)",双击A2表格右下角+,填充公式。
(三)筛选后,序号顺序不变。
技巧:在"A2"中输入公式"=SUBTOTAL(3,B$2:B2)",双击A2表格右下角+,填充公式。
二、智能表格(Table)应用(一键美化表格,结构化引用和高级筛选,多表透视)。
以下内容均需要创建智能表格(Table)后方可操作,创建智能表格快捷键:选择数据后按Ctrl+T
(一)、一键美化表格
(二)、多表筛选
在普通工作表中使用筛选时只允许创建一个数据筛选区域,而智能表可以在同一个工作表中对多个数据表进行筛选。
(三)、自动生成数据,公式自动填充、自动扩展
(四)、快速数据计算-结合筛选器查看各种条件下的统计结果。
查看方式有:平均值、计数、数值技术、最大值、最小值、求和、标准偏差、方差、添加其他函数。
(五)、插入切片器-筛选分类数据
快速查看各类数据情况,不需要像常规表不停点筛选。
(六)透视表动态引用(更改原始表格数据,透视表中数据刷新后可同步更改)
常规数据表透视时,原始数据有改变时,则要重新创建一个数据透视表。使用智能表透视,原始数据有更改,透视表刷新后可直接更改。
(七)透视表多表关联分析
多表关联分析:在一个透视表中,同时可以分析多个表(前提条件:所有表必须有一个相同的字段)中的字段,而无需将这些字段合并在同一个表中。
为方便展示,只做两个表格透视(大家熟悉后可做三个以上表格透视)
1、首先把两个表数据内容创建为智能表(快捷键:Ctrl+T)
2、创建数据透视表(以数据最丰富的表创建)
步骤:创建透视表→在页面最右边 数据透视表字段中点“更多表格”→在创新新的数据透视表窗口中点击“是”→透视表字段面板中出现关联的相关表格。
3、为两个表创建关系
步骤:在透视表页面点击“分析”→“关系”→在“管理关联”对话框点击“新建”→在“创建关系”对话框中选择两个表相同字段点确定→关闭“管理关联”对话框。
4、透视表多表关联分析
(八)、智能数据验证(下拉列表选项自动更新)
三、对excel表格中各类数据进行排名
(一)、按各类数据排名(相同数据,不占名次)
技巧:在排名单元格中输入公式“=SUMPRODUCT(($G$2:$G$9>=G2)*(1/COUNTIF($G$2:$G$9,$G$2:$G$9)))”,下拉鼠标,填充公式。
公式解析:$G$2→指想对数据排行列的首个数据表格,$G$9→指想对数据排行列的最后一个数据表格,$G$2:$G$9→指想对数据排行列“首个数据表格至最后一个数据表格”,可举一反三。
(二)、按各类数据排名(相同数据,占用名次)
技巧:在排名单元格中输入公式“=RANK(F2,$F$2:$F$9)”,下拉鼠标,填充公式。
(三)、按固定类别排名
技巧:在排名单元格中,输入公式"=COUNTIF(C$2:C2,C2)",下拉鼠标,填充公式。
掌握以上技巧在工作、学习中,能让你在同事、同学面前快人一步。