Excel表格常用技巧大全（共57个），再不保藏便是你的错了
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_gif/ArH8mtnPGicARia56ZCHyXHHeRID8viatMKC6qSxPtC74wxPHW6o1ud58lmRAxcc43fJOcf7QcPyHiaaPibIvqjdRkA/640?wx_fmt=gif&tp=webp&wxfrom=5&wx_lazy=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">编辑：人力君</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">源自</span>：Excel精英培训（excelpx-tete）</span></p><span style="color: black;">人力君给<span style="color: black;">大众</span><span style="color: black;">举荐</span>个干货贴：Excel实用小技巧，上班之后记得用起来~（<strong style="color: blue;">适合版本 Excel2007及以上</strong>）</span><span style="color: black;">人力君</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1</span></p><span style="color: black;"><strong style="color: blue;">文件操作</strong></span><span style="color: black;">1、为excel文件添加打开<span style="color: black;">秘码</span></span><span style="color: black;">文件 - 信息 - <span style="color: black;">守护</span>工作簿 - 用<span style="color: black;">秘码</span>进行加密。</span><span style="color: black;">2、为文件添加作者信息</span><span style="color: black;">在该excel文件图标上右键单击 - 属性 - 摘要 - 在作者栏中输入</span><span style="color: black;">3、让多人<span style="color: black;">经过</span>局域网共用excel文件</span><span style="color: black;">审阅 - 共享工作簿 - 在打开的窗口上选中“<span style="color: black;">准许</span>多用户<span style="color: black;">同期</span>编辑...”</span><span style="color: black;">4、<span style="color: black;">同期</span>打开多个excel文件</span><span style="color: black;">按ctrl或shift键<span style="color: black;">选择</span>多个要打开的excel文件，右键菜单中点“打开”</span><span style="color: black;">5、<span style="color: black;">同期</span>关闭所有打开的excel文件</span><span style="color: black;">按shift键<span style="color: black;">同期</span>点右上角关闭按钮。</span><span style="color: black;">6、设置文件自动<span style="color: black;">保留</span>时间</span><span style="color: black;">文件 - 选项 - <span style="color: black;">保留</span> - 设置<span style="color: black;">保留</span>间隔</span><span style="color: black;">7、恢复未<span style="color: black;">守护</span>的excel文件</span><span style="color: black;">文件 - <span style="color: black;">近期</span>所用文件 - 点击“恢复未<span style="color: black;">保留</span>的excel文件”</span><span style="color: black;">8、在excel文件中创建日历</span><span style="color: black;">文件 - 新建 - 日历</span><span style="color: black;">9、设置新建excel文件的默认字体和字号</span><span style="color: black;">文件 - 选项 - 常规 - 新建工作簿时：设置字号和字体</span><span style="color: black;"><span style="color: black;">十、</span>把A.xlsx文件图标<span style="color: black;">表示</span>为<span style="color: black;">照片</span>形式</span><span style="color: black;">把A.xlsx 修改为 A.Jpg</span><span style="color: black;">11、一键新建excel文件</span><span style="color: black;">Ctrl + N</span><span style="color: black;">12、把工作表另存为excel文件</span><span style="color: black;">在工作表标签上右键 - 移动或复制 - 移动到”新工作簿”</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2</span></p><span style="color: black;"><strong style="color: blue;">窗口设置</strong></span><span style="color: black;">1、<span style="color: black;">同期</span>打开对比两个或多个excel文件</span><span style="color: black;">视图 - <span style="color: black;">所有</span>重排 - 选<span style="color: black;">摆列</span>的方向</span><span style="color: black;">2、<span style="color: black;">同期</span><span style="color: black;">查询</span>一个工作簿中的两个工作表</span><span style="color: black;">视图 - 新建窗口 - <span style="color: black;">所有</span>重排 - 选<span style="color: black;">摆列</span>方向</span><span style="color: black;">3、<span style="color: black;">隐匿</span>或<span style="color: black;">表示</span>excel最上面的功能区</span><span style="color: black;">Ctrl+F1</span><span style="color: black;">4、<span style="color: black;">隐匿</span>excel工作表界面</span><span style="color: black;"><span style="color: black;">选择</span>要<span style="color: black;">隐匿</span>的工作表 - 视图 - <span style="color: black;">隐匿</span></span><span style="color: black;">5、让标题行始终<span style="color: black;">表示</span>在最上面</span><span style="color: black;">视图 - 冻结窗格 - 冻结首行</span><span style="color: black;">6、把窗口拆分成上下两部分，都<span style="color: black;">能够</span>上下翻看</span><span style="color: black;"><span style="color: black;">选择</span>要拆分的位置 - 视图 - 拆分</span><span style="color: black;">7、<span style="color: black;">调节</span>excel工作表<span style="color: black;">表示</span>比例</span><span style="color: black;">按ctrl+滚轮</span><span style="color: black;">8、快速切换到另一个excel窗口</span><span style="color: black;">ctrl + tab键</span><span style="color: black;">9、全屏<span style="color: black;">表示</span>excel界面</span><span style="color: black;">视图 - 全屏<span style="color: black;">表示</span></span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">3</span></p><span style="color: black;"><strong style="color: blue;">单元格<span style="color: black;">表示</span></strong></span><span style="color: black;">1、<span style="color: black;">隐匿</span>0值</span><span style="color: black;">文件 - 选项 - 高级 - 去掉“在<span style="color: black;">拥有</span>零值.....”勾选</span><span style="color: black;">2、<span style="color: black;">隐匿</span>单元格内所有值</span><span style="color: black;">ctrl+1打开单元格设置窗口 - 数字 - 自定义 - 右边文框中输入三个分号;;;</span><span style="color: black;">3、<span style="color: black;">隐匿</span>编辑栏、灰色表格线、列标和行号</span><span style="color: black;">视图 - <span style="color: black;">表示</span> - 去掉 各项的勾选</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">4</span></p><span style="color: black;"><strong style="color: blue;">单元格<span style="color: black;">选择</span></strong></span><span style="color: black;">1 、<span style="color: black;">选择</span>当前表格</span><span style="color: black;">按ctrl+a全选当前数据区域</span><span style="color: black;">2 、选取表格中的值/公式</span><span style="color: black;">按ctrl+g打开定位窗口 - 定位<span style="color: black;">要求</span> - 值/公式</span><span style="color: black;">3 、<span style="color: black;">查询</span>值</span><span style="color: black;">按ctrl+f打开<span style="color: black;">查询</span>窗口，输入<span style="color: black;">查询</span>的值 - <span style="color: black;">查询</span></span><span style="color: black;">4、<span style="color: black;">选择</span>最下/最右边的非空单元格</span><span style="color: black;">按ctrl + 向下/向右箭头</span><span style="color: black;">5、快速<span style="color: black;">选择</span>指定<span style="color: black;">体积</span>的区域</span><span style="color: black;">在左上的名<span style="color: black;">叫作</span>栏中输入单元格<span style="color: black;">位置</span>，如a1:a10000，<span style="color: black;">而后</span>按回车</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">5</span></p><span style="color: black;"><strong style="color: blue;">单元格编辑</strong></span><span style="color: black;">1、设置单元格按回车键光标<span style="color: black;">转</span>方向</span><span style="color: black;">文件 - 选项 - 高级 - 最上面“按Enter键后移动所选内容” - 设置方向</span><span style="color: black;">2、设置下拉菜单，<span style="color: black;">表示</span>A,B,C三个选项</span><span style="color: black;">数据 - 数据有效性 - <span style="color: black;">准许</span> - 序列 - 输入A,B,C</span><span style="color: black;">3、复制表格<span style="color: black;">保留</span>行高列宽不变</span><span style="color: black;">整行<span style="color: black;">选择</span>复制 - 粘贴后点粘贴选项中的“<span style="color: black;">保存</span>列宽”</span><span style="color: black;">4、输入到F列时，自动转到下一行的首列</span><span style="color: black;"><span style="color: black;">选择</span>A:F列，输入后按回车<span style="color: black;">就可</span>自动<span style="color: black;">转</span></span><span style="color: black;">5、设置三栏表头</span><span style="color: black;"><span style="color: black;">插进</span> - 形状 - 直线 - 拖入文本框中输入的字体并把边框设置为无</span><span style="color: black;">6、<span style="color: black;">同期</span>编辑多个工作表</span><span style="color: black;">按ctrl或shift键<span style="color: black;">选择</span>多个工作表，直接编辑当前工作表<span style="color: black;">就可</span>。</span><span style="color: black;">7、输入身份证号或以0<span style="color: black;">起始</span>的数字</span><span style="color: black;">把单元格格式设置成文本，<span style="color: black;">而后</span>再输入</span><span style="color: black;">8、快速删除空行</span><span style="color: black;"><span style="color: black;">选择</span>表中某列 - ctrl+g定位 - 定位<span style="color: black;">要求</span> - 空值 - 删除整行</span><span style="color: black;">9、快速<span style="color: black;">插进</span>空行</span><span style="color: black;">在表右侧输入序号1,2,3..N,<span style="color: black;">而后</span>再复制该序号到下面空行，最后按序号列排序<span style="color: black;">就可</span>。</span><span style="color: black;"><span style="color: black;">十、</span>快速合并多行数据</span><span style="color: black;"><span style="color: black;">插进</span>批注 - <span style="color: black;">选择</span>多列数据复制 - 粘贴到批注中，<span style="color: black;">而后</span>再从批注中复制粘至单元格区域中<span style="color: black;">就可</span>。</span><span style="color: black;">11、<span style="color: black;">插进</span>特殊符号</span><span style="color: black;"><span style="color: black;">插进</span> - 符号</span><span style="color: black;">12、<span style="color: black;">查询</span>重复值</span><span style="color: black;"><span style="color: black;">选择</span>数据列 - <span style="color: black;">起始</span> - <span style="color: black;">要求</span>格式 - <span style="color: black;">明显</span><span style="color: black;">表示</span>单元格规则 - 重复值</span><span style="color: black;">13、删除重复值</span><span style="color: black;"><span style="color: black;">选择</span>区域 - 数据 - 删除重复项</span><span style="color: black;">14、单元格分区域需要<span style="color: black;">秘码</span><span style="color: black;">才可</span>编辑</span><span style="color: black;">审阅 - <span style="color: black;">准许</span>用户编辑区域</span><span style="color: black;">15、用excel进行汉英互译</span><span style="color: black;">审阅 - 翻释</span><span style="color: black;">16、不复制<span style="color: black;">隐匿</span>的行</span><span style="color: black;"><span style="color: black;">选择</span>区域 - ctrl+g定位 - 定位<span style="color: black;">要求</span> - 可见单元格 - 复制 - 粘贴</span><span style="color: black;">17、单元格强制换行</span><span style="color: black;">在需要换行字符后按alt+enter键</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">6</span></p><span style="color: black;"><strong style="color: blue;">单元格格式设置</strong></span><span style="color: black;">1、万元<span style="color: black;">表示</span></span><span style="color: black;">自定义格式代码：0“,"0,</span><span style="color: black;">2、日期按年年年年-月月-日日<span style="color: black;">表示</span></span><span style="color: black;">自定义格式代码：yyyy-mm-dd</span><span style="color: black;">3、手机号分段<span style="color: black;">表示</span></span><span style="color: black;">自定义格式代码：000 0000 0000</span><span style="color: black;">4、数字1<span style="color: black;">表示</span>成1月</span><span style="color: black;">自定义格式代码: 0“月"</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">7</span></p><span style="color: black;"><strong style="color: blue;"><span style="color: black;">照片</span>处理</strong></span><span style="color: black;">1、删除工作表中所有<span style="color: black;">照片</span></span><span style="color: black;">ctrl+g定位 - 定位<span style="color: black;">要求</span> - 对象 - 删除</span><span style="color: black;">2、工作表<span style="color: black;">插进</span>背景<span style="color: black;">照片</span></span><span style="color: black;">页面布局 - 背景 - <span style="color: black;">选取</span><span style="color: black;">插进</span><span style="color: black;">照片</span></span><span style="color: black;">3、<span style="color: black;">插进</span><span style="color: black;">能够</span>打印的背景</span><span style="color: black;"><span style="color: black;">插进</span> - 页眉页脚 - <span style="color: black;">选择</span>页眉后点<span style="color: black;">照片</span> - <span style="color: black;">选择</span><span style="color: black;">照片</span>并拖入</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">8</span></p><span style="color: black;"><strong style="color: blue;">打印设置</strong></span><span style="color: black;">1、打印标题行</span><span style="color: black;">页面布局 - 打印标题 - 点顶端标题行后的折叠按钮，<span style="color: black;">选择</span>要打印的标题行。</span><span style="color: black;">2、多页强制打印到一页上</span><span style="color: black;">页面布局 - 打印标题 - 页面 - <span style="color: black;">调节</span>为1页宽1页高</span><span style="color: black;">3、按厘米设置行高</span><span style="color: black;">视图 - 页面布局，在页面布局下行高单位是厘米</span><span style="color: black;">4、<span style="color: black;">插进</span>分页符</span><span style="color: black;"><span style="color: black;">选择</span>要<span style="color: black;">插进</span>的位置 - 页面布局 - 分页符 - <span style="color: black;">插进</span>分页符</span><span style="color: black;"><img src="https://mmbiz.qpic.cn/mmbiz_gif/ArH8mtnPGicBon7tep9GUNRrD0sibs1vvwLu8NapNMrRkSNFNMUhSGKlOMx3o3NAUl2158NLmQFtSozeiaFicagpVg/640?wx_fmt=gif&tp=webp&wxfrom=5&wx_lazy=1" style="width: 50%; margin-bottom: 20px;"></span><span style="color: black;"><strong style="color: blue;"><span style="color: black;">往期<span style="color: black;">举荐</span>：</span></strong></span><span style="color: black;"><a style="color: black;">累死你的不是工作，而是工作方式</a></span><a style="color: black;"><span style="color: black;">离职，最能看清一个员工的本质</span></a><a style="color: black;"><span style="color: black;">1张<span style="color: black;">朋友</span>聚会各阶层<span style="color: black;">状况</span>表<span style="color: black;">揭发</span>：高层次的圈子，有多可怕？</span></a><a style="color: black;"><span style="color: black;">甲有5套房，不上班，收房租；乙有1套房，上班赚工资；丙无房，租房子…...</span></a>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">爱好</span>，点个“</span><span style="color: black;"><strong style="color: blue;">在看</strong></span><span style="color: black;">”</span><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></p>
页:
[1]