Excel教程专题：Excel 进阶大神教学

蔡振辉

不少新入职场的小伙伴都逐渐的接触了excel这一款办公软件，在平时，我们基本的日常工作，只要是涉及到数据统计或者是数据分析的工作，就肯定是离不开excel的，所以今天，小编给大家带来一篇excel教学，都是比较常用的小技巧，希望大家能在工作的过程中用上这些。
一、软件基础
1、文档操作
（1）多个表格显示
①、启动Excel，打开文档后，单击一个单元格，然后在“视图”种选择“窗口”，单击“全部重排”按钮打开“重排窗口”对话框，选择窗口排列方式，这里选择“平铺”方式。


②、完成设置后单击“确定”按钮关闭“重排窗口”对话框，此时文档窗口将在屏幕上平铺排列。
（2）文档加密
首先打开需要加密文档，选择文件选项，然后选择信息-保护工作簿-用密码进行加密；


最后在密码输入框重复输入想要设定的密码就可以了


（3）文档取消密码
取消密码的办法其实就是再把文件保存一次，在保存替换原文件或重命名时将密码取消掉。
点击工具选择常规选项后删除密码，点击确定并保存。


（4）输入对号
①、选择Excel插入菜单，然后选择“特殊符号”，这时会出现“插入特殊符号”窗口，选择“数学符号”选项卡，找到“√”这个对号符号，按确定即可：


②、Alt+符号法
这种方法在很多地方可以适用，也就是按住Alt键，再输入对号√对应的代码：41420，这个对号√符号就出来了。
（5）设置分列与合并
如果我们要对一个时间格式进行分列，汇总成我们需要的格式，我们可以这么做：
①、选定要进行分列的数据，然后点击“数据”工具栏，选择“分列”，然后选择“分隔符号”，点击下一步；


②、然后我们勾选空格，当然，由于这里要根据数据格式，如果是用其他字符分隔的，那可以选择其他字符，勾选完成以后，在数据预览的区域里预览效果，然后点击下一步：


③、这里需要注意的是，我们需要将列的格式改为文本，这主要是方便我们在后期进行操作，否则后期会出现因格式的原因造成数据格式的错误。


点击完成后，数据就完美的被分开了。
2、单元格设置
（1）身份证号码格式设置
有的时候我们得到的数据报表，身份证号码这一栏全都是科学计数方法显示，就算拉长单元格也是保持原样


按照顺序操作“单击右键→设置单元格格式→文本→确定”。


如果显示内容没有改变，只要双击一下单元格内容，然后就可以看到正确输入格式的数值


（2）设置条件格式
①、打开excel软件，点击需要设置条件格式的单元格区域；


②、选择“开始”-“样式”-“条件格式”-“突出显示单元格规则”命令，在弹出的列表中选择需要的命令。这里我们需要把成绩在60分以下的突出显示，所以选择“小于”命令。


③、弹出“小于”对话框，在“为小于以下值的单元格设置格式”文本框中输入60，如下图所示：


（3）如何高亮显示重复值
①、选择数据区域后，从“开始”选项卡中选择“条件格式”，并点击“新建规则”。


②、选择“仅对唯一值或重复值设置格式”，确认已选中“重复”，即针对所选数据区域中的重复值设置相应格式。


③、点击上图中右下角“格式”按钮，并设置高亮度显示颜色，如将背景填充色设置为浅蓝色。
点击“确定”后，即可看到重复的成绩，显示为蓝色。最终效果如下图所示。


（4）怎么转换成PDF格式
①、如果是Microsoft office Excel 2007以上的版本，我们可以直接将excel文件另存为pdf文件。


但是如果不是安装2007以上版本的话，我们就要利用转格式工具：


（5）多人同时编辑
在excel功能区点击“审阅”切换审阅选项，点击“共享工作簿”，如果弹出下面第二个图片对话框，则需要根据提示进行设置，点击确定。


（6）快速删除空白单元格
①、有的时候我们需要把空白的单元格快速删除，首先我们全选数据：


②、然后按F5键打开定位，点击定位条件按钮，选为空值。


③、点击确定按钮后，所有的空值都会被选中；


④、在选中的空值单击右键，选择删除，整行。


⑤、完成，效果图如下。


（7）冻结窗口
　用Excel工作表的冻结功能，可以达到固定窗口的效果，非常方便我们平时的工作，下面我们来看这种方法的具体操作步骤：
在下图，我们想让1~9行的表格固定在屏幕上，我们首先选中第10行；


然后我们选择“视图→冻结窗格→冻结拆分窗格”


这个时候，第9行下面多了一根灰色的线，滚动滚轮我们就会发现，表格虽然在移动，但是1~9行，是一直存在在界面上的。
二、输入技巧
1、设置数值格式
有的时候我们会发现，我们输入的数值杂乱无章，非常难看，那么我们要设定数值的位数该怎么办呢?
①、首先选中需要更改的单元格，在鼠标右击菜单中选择“设置单元格格式”如下图所示。


②、在“设置单元格格式”中选择“数值”选项，在右侧可以修改小数位数，修改完成后单击“确定”按钮。


③、设置完成后就可以看到所有的数值都四舍五入为两位小数了。


2、快速生成工资条
①、首先，我们要在工资表旁边的空白列，输入1、2......用填充柄填充要如序列号一般，作为我们做工资条的辅助列


②、我们复制一份刚刚做的数据，粘贴到紧接着的尾部


③、选中F19单元格，点击排序，升序(降序也可以)


④、排序后，辅助列将变成1122……，这样就达到插入空白列的效果了


⑤、我们到此，删除辅助列，复制一份表头数据，为下一步做准备


⑥、到此，我们需要选择工资表区域，然后按下F5，调出定位功能，这里我们定位空值，确定后，我们来看一下效果，空值区域都被选定了


⑦、最后，我们的鼠标悬停在刚刚选中的任意一个单元格，然后直接CTRL+V，进行粘贴，就大功告成啦。


3、防止重复录入
①选中需要建立输入身份证号码的单元格区域后，单击“数据→数据工具→数据验证”，打开“数据验证”对话框。


②、在“数据验证”对话框中，在“设置”标签下，按“允许”右侧的下拉按钮。选择“自定义”选项，然后在下面“公式”方框中输入公式：=COUNTIF(B$43:B$50,B43)=1，确定返回。


③、以后在上述单元格中输入了重复的身份证号码时，系统会弹出提示对话框，并拒绝接受输入的号码。


4、添加批注
选中单元格后，切换至“审阅”功能区，在“批注”分组中单击“批注”按钮。


然后直接输入批注内容就可以了
5、设置日期格式
怎么样才能输入数字后，直接让数字变成日期呢？
其实操作非常简单，我们只需要选择数字单元格后“右键→设置单元格格式→日期”，最后点击确定就可以啦~


三、表格制作
Excel表格中，最基础的就是表格的制作了。那么在表格制作方面，都有哪些值得学起来的技巧呢？
1、修改网格线颜色
（1）点击“文件”-“选项”，进入“高级”选项卡；
（2）找到其中的“网格线颜色”，选择要应用的颜色，确定。


2、自动调整列宽
自动调整列宽的方法有两个：
（1）选中要调整列宽的列，点击“开始”-“单元格”选项卡，选择“格式”下的“自动调整列宽”；
（2）将光标移至要调整列宽的标尺上，双击该列的最右边框，即可自动调整列宽。


3、行列转换
（1）选中要进行行列转换的单元格区域，复制；
（2）在要粘贴的空白单元格处，点击右键选择“选择性粘贴”-“转置”。


4、制作下拉菜单
（1）选中要制作下拉菜单的单元格，点击“数据”-“数据验证”；
（2）验证条件中选择“序列”，在“来源”中输入下拉菜单中的内容，多项用英文“,”隔开，确定。





5、合并内容相同单元格
（1）选中有相同内容的单元格区域，点击“数据”-“分类汇总”；
（2）按下“Ctrl+G”，点击“定位条件”，勾选“空值”；
（3）在“开始”中点击“合并单元格”，再次点击“数据”-“分类汇总”，选择“全部删除”；
（4）选中合并列，点击“格式刷”，将格式应用到有相同内容、要合并的单元格列中。


6、圈出无效数据
（1）选中要筛选无效数据的单元格区域，点击“开始”-“条件格式”；
（2）根据需要选择合适的规则，或者“新建规则”来对数据进行选择并作标记圈出。


四、数据技巧
Excel表格中最多的就是数据了，处理好了表格的数据，也就完成了Excel表格的绝大部分了。下面就让我们来了解下这方面的一些小技巧。
1、自动添加单位
（1）选中要添加单位的列中任一单元格，右键选择“单元格格式”；
（2）点击“自定义”，在右侧输入框中输入要添加的单位，确定。


2、绘制斜线表头
（1）选中表头单元格，点击“开始”-“边框”；
（2）选择“绘图边框”在单元格内画出斜线，或者直接选择“其他边框”应用“斜线”。


3、保护工作表不被修改
（1）点击“审阅”-“保护工作表”，根据需要在设置中进行勾选，确定；
（2）点击“撤销工作表保护”，恢复可修改状态；
（3）可在“保护”时设置密码，这样要撤销就需要输入密码，更好地保护表格不被修改到。


4、文字添加下划线
（1）选中要添加下划线的文字所在单元格，点击“开始”中的“下划线”；
（2）右键“设置单元格格式”，在“字体”选项卡可以看到其他的下划线类型，点击选择后，确定。


五、数据筛选
在制作Excel表格的时候，大家会需要对数据进行筛选，那当数据太多的情况下，我们怎么才能快速筛选出自己所需的内容呢？现在就一起来了解下Excel的筛选方法吧。
1、颜色筛选
有时候我们会在Excel表格中对各个单元格进行颜色批注，那当我们要筛选数据的时候，我们可能就会按照颜色来进行筛选，那怎么操作？
（1）打开数据表，看到不同颜色填充的不同的单元格，然后选中需要筛选的一列中，点击“开始——筛选”。


（2）点击该列上方的筛选按钮，然后选择“按颜色筛选”，并挑选颜色，然后就可以将对应颜色的数据筛选出来了。


2、条件格式运用
大家也知道，Excel中有条件格式的运用，将这些条件格式运用好了，那找数据也很简单，那我们来看下如何运用条件格式。
（1）先选中需要运用条件格式的行或列。然后点击“开始”选项卡下的“条件格式”的下拉按钮，找到“数据条”，再选择合适的颜色样式。


（2）这时候我们就可以看到数据条已经添加了。


（3）而且我们还可以设置只显示数据条，不显示数值。只需要选中有数值的单元格，然后点击“条件格式”的下拉按钮，选择“管理规则”。


（4）接着在弹出的窗口中，选中这一条数据条规则，点击“编辑规则”。


（5）勾选“仅显示数据条”，点击确定后，返回到规则管理界面再点击确定即可。这样表格中就只会显示数据条了。要是想要回到最初的显示状态，同样在“管理规则”中删除规则即可。


3、高级筛选功能
我们在做Excel数据表示，有时候需要找到某个数据项，单靠眼睛可能很难找到，那用高级筛选功能就行。
（1）打开你想要筛选的Excel表格，然后在其他的单元格中，输入筛选的条件，筛选条件上的标题要对应好。


（2）然后在“数据”中，点击“高级”。


（3）在弹出的高级筛选窗口中，“列表区域”会自动识别列表区域，然后我们再点击“条件区域”右侧的按钮。


（4）然后选取Excel的条件区域。


（5）在筛选窗口中点击确定后，就可以看到Excel的高级筛选已经完成了。


4、数据分列整理
我们有时候导出一些数据后，数据会都在同一行里，那样是不利于我们查看的，那我们要怎么将这些数据分列整理出来呢？
（1）选中需要分列的数据列，然后点击“数据——分列”。


（2）在弹出的窗口中，选中“分隔符号”，点击“下一步”。


（3）然后在“分隔符号”一列中，选择对应的符号后，接着选择“完成”就可以了。


5、自动筛选
（1）打开一篇你要筛选的文档，然后选中需要筛选的内容，点击“数据——筛选”。


（2）开启筛选后，在数据列中， 可以看到有个下拉三角形，点击它，再点击“数字筛选——自定义筛选”。


（3）然后在弹出的窗口中，设置自己需要的内容就行了。


六、图表插入
在做Excel时，我们经常会制作一些图表，通过图表可以更好地展示数据，那现在我们来看下怎么在Excel中插入表格吧。
1、绘制甘特图
（1）先选中需要绘制甘特图的数据区域，然后点击“插入——推荐的图表——堆积条形图”。




（2）然后点击“图表工具——设计——选择数据”，然后再将轴标签区域改为“确定项目、访问调查、实地执行……”。


（3）然后在图列项（系列）中，点击“编辑”，然后添加，将“系列名称”选为“开始时间”，将系列值选为对应的“4月1日……”。


（4）然后点击如下图的箭头，将开始时间调整到耗费天数上。


（5）然后双击图表中“确定项目”所在的坐标轴，然后在右侧的“设置坐标轴格式”中，勾选“逆序类别”。


（6）然后确定开始时间4月1日和完成时间4月28日的常规值备用，右击，然后点击“设置单元格格式——常规”。


（7）然后双击上方的时间，在右侧的“设置坐标轴格式”中，点击“坐标轴选项”。然后将最小值设为当归4月1日的常规值，将最大值设置为结束时间的常规值。


（8）然后双击图表，在右侧的“设置数据系列格式“中，点击“填充——无填充”。


（9）然后再双击网格线，在右侧的“设置主要网格线格式”中，勾选 “实线”再设置“颜色”以及“短划线类型”就行了。


2、建立迷你图
（1）在打开的Excel文档中，点击“插入”，然后选择“迷你图”中的所需的图表类型。


（2）在弹出的窗口中，点击“位置范围”右侧的按钮，选中要显示迷你图表的单元格，确定后表格就会显示在一个单元格中了。


3、图片基本处理
我们有时候会在Exccel中插入一些图片，这些图片，可以直接在Excel中，通过“插入——屏幕截图”等，也可以自己插入图片。那对于Excel中的图片，我们能够怎么处理呢？
（1）若是我们需要将图片的背景去掉的话，那可以通过这个来实现，将图片插入到Excel中后，点击“格式——删除背景”。


（2）选择要抠除背景的图片区域，然后点击“保留更改”，这样就可以将主体抠出来了。


4、折线图实虚线混排
有时候我们做了折线图后，想要展示一个实虚线混排的效果，应该怎么弄呢？
（1）选中数据列，然后插入折线图。


（2）然后选中前半段折线，右键设置数据系列格式，再设置线条的颜色，线型为实线。


（3）然后是后半段折线，同样右键设置数据系列格式，设置线条的颜色，线型为虚线。这样就可以看到折线的实虚线处理了。


5、折线图增加平均值
（1）打开表格，然后先在平均值一栏中，输入=ROUND(AVERAGE(B2:B7),1)，计算出平均值。


（2）然后选择需要增加平均值线的单元格区域，选择“插入——图表——折线图”。


（3）再来制作平均值线，右击图表，然后点击“选择数据”，在弹出的对话框中，点击“添加”按钮，在弹出的“编辑数据系列”对话框中，找到“系列名称”，然后输入=Sheet3!$A$8，在“系列值”中输入=Sheet3!$B$8，单击确定。




（4）点击图表中“平均分”的数据点，然后右键，选择“更改系列图表类型”，在弹出的“更改图表类型对话框”中，将“平均分”的图表类型改为“散点图”。




6、负数显示红色
在Excel表格中，有一些数据可能是负数的，那我们怎么可以快速地看出这些数据呢？可以将其设置为显示红色。
（1）选中该数据区域，然后点击“插入——条形图”。


（2）然后双击图表中的数据列，在弹出的“设置数据系列格式”对话框中，选中“填充”。然后选中“纯色填充”，勾选“以互补色代表负值”，然后再单击右侧的颜色选择按钮，选择一种颜色。


（3）这时候图表中的负值就会以红色显示了。

小小小小羽

占位编辑