12个超实用的Excel小技巧，2个小时熬夜整理的，我头发都要熬秃了
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">我是【桃大喵学习记】，欢迎<span style="color: black;">大众</span>关注哟~，<span style="color: black;">每日</span>为你分享职场办公软件<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>分享12个超实用的Excel小技巧，<span style="color: black;">每一个</span>都很实用，花2个小时熬夜整理的，学会后让你从小白秒变大神！<span style="color: black;">大众</span>有时间都要看一看哦！话不多说，下面直接上干货！！</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧<span style="color: black;">1、</span>快速行列转置</strong></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;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">先复制左侧表格数据→选中要复制到的位置任意单元格→点击【右键】选中【<span style="color: black;">选取</span>性粘贴】→在弹出的“<span style="color: black;">选取</span>性粘贴”对话框中勾选【转置】，最后点击确定<span style="color: black;">就可</span>。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/153a5b7465954d5687950b8a7573651f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=Z%2Bx67pfBZl1R6iw4BwCGWCK1sAE%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧<span style="color: black;">2、</span>用分列把文本数据转换成数值</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">有时候Excel表格数据数字左上角会<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 style="color: black;">处理</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">首要</span>选中数据区域→<span style="color: black;">而后</span>点击【数据】-【分列】→在弹出的“文本分列向导”对话框中点击2次【下一步】，直至点击【完成】<span style="color: black;">就可</span>，<span style="color: black;">这般</span><span style="color: black;">能够</span>快速把文本格式改成常规格式了。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/6654f3f25d8349409d47e3f086ff8c6a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=BHtkoIuQKB9LCEIfQlnum1UjQH4%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧<span style="color: black;">3、</span>双击锁定格式刷，开启无限刷格式模式</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">先选中<span style="color: black;">已然</span>设置好格式的单元格，<span style="color: black;">而后</span>双击格式刷，开启连环无限刷格式模式，最后只需再点击一次格式刷<span style="color: black;">就可</span>结束。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/c2a9b7d809d14ad49007c566cea3dc3c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=Z10t02vlS1OdeacpDbsSIaPv4Pk%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧<span style="color: black;">4、</span>F9快速预览公式结果</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">首要</span>选中想预览结果的公式→<span style="color: black;">而后</span>按F9键<span style="color: black;">就可</span>，<span style="color: black;">倘若</span>想还原之前的公式只需再按组合键【Ctrl+Z】<span style="color: black;">或</span>【Esc】<span style="color: black;">就可</span>。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/f0de5771effd47559a5b69470bcdb019~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=Hp2BePsC8RBoJ%2BLBlJFNWApqtqI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">备注：在有多个函数嵌套的公式中选中部分公式预览，有助于理解公式。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧<span style="color: black;">5、</span>不改变列宽<span style="color: black;">表示</span>变成#号的日期</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">如下图所示，<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 style="color: black;">或</span>说日期字体<span style="color: black;">体积</span><span style="color: black;">怎样</span>自适应列宽呢？</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/f884a231f2ab4629a11f9c9f5a7e593c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=kj20j6Ttu6UAgHaxGoi3rdtMQUw%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">首要</span><span style="color: black;">选取</span>所有变成#号的日期单元格→<span style="color: black;">而后</span><span style="color: black;">经过</span>快捷键【Ctrl+1】调出“单元格格式”窗口→点击【对齐】选项，勾选【文本<span style="color: black;">掌控</span>】版块下的【缩小字体填充】选项，<span style="color: black;">这般</span>日期字体<span style="color: black;">体积</span>就<span style="color: black;">能够</span>自适应列宽了。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/cd7b9ffd99454a798ba666597cf30ac3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=npQhSkBMu515Ef1rzzPBozt1mec%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧<span style="color: black;">6、</span>双击鼠标<span style="color: black;">表示</span><span style="color: black;">隐匿</span>行列</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">把鼠标放在表格<span style="color: black;">隐匿</span>区域的列标或行号边线上，待光标变成双向箭头时，双击鼠标<span style="color: black;">就可</span>。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/2eb776f3f94c46e5a9e84f4f0707b002~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=8cyRyDIC%2BzMzw%2FISaJI697h5I00%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧<span style="color: black;">7、</span>红色字体标记不及格分数</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">首要</span>选中<span style="color: black;">目的</span>区域单元格→按快捷键【Ctrl+1】，调出“单元格格式”窗口→<span style="color: black;">而后</span>点击【数字】选项卡【<span style="color: black;">归类</span>】中的【自定义】→在【类型】文本框中输入：[红色][<60];[黑色][>=60]</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/be3b4061ece548df8a737218e6ec843f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=RMRmS4UE9BdrJsVxpM5uujxNCPU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧<span style="color: black;">8、</span>新增数值数据前面用0占位</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">如下图所示，<span style="color: black;">咱们</span>想把在员工编号前面加上3个0，<span style="color: black;">便是</span>以0001<span style="color: black;">起始</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/4f20543f4a524480a5b7643e3382a440~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=l2GeheUoq%2BVt5%2Fo%2FjF2veVLndfQ%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">首要</span>选中<span style="color: black;">目的</span>区域单元格→按快捷键【Ctrl+1】，调出“单元格格式”窗口→<span style="color: black;">而后</span>点击【数字】选项卡【<span style="color: black;">归类</span>】中的【自定义】→在【类型】文本框中输入：0，需要<span style="color: black;">表示</span>几位数就输入几个0（上面实例是一共<span style="color: black;">表示</span>4位数，所有输入4个0<span style="color: black;">就可</span>）</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/8e561952b3c1495a90b005eb1de57ff6~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=2Y8CQG5UWq9V%2FgSHabPVaObChBw%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧<span style="color: black;">9、</span>制作可自动更新数据的下拉菜单</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">首要</span><span style="color: black;">选取</span>需要设置下拉菜单的数据→<span style="color: black;">而后</span>按快捷键【Ctrl+T】把它转换为超级表→接着点击点击【数据】-【有效性】调出“数据有效性”窗口→在弹出的“数据有效性”对话框中“有效<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 style="color: black;">就可</span>。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/de4f137ad80545d88ebe72b3403b3150~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=I0AWR%2BogzzRR%2ByeOEdS5VzbQjSg%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧十、表格每页打印标题</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">咱们</span>在打印多页表格数据时默认<span style="color: black;">仅有</span><span style="color: black;">第1</span>页有标题行，<span style="color: black;">那样</span>，<span style="color: black;">怎样</span>让每一张打印纸都<span style="color: black;">表示</span>标题行呢？</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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>，如下图所示</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/58808d3e29cb41d8baa751dc3a35bcf6~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=aiZMTD4EelP5CvFDi9UCNpVZCUY%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧十<span style="color: black;">1、</span>双击批量填充内容</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">把鼠标放到单元格右下角，当变成十字型时，双击鼠标<span style="color: black;">就可</span>快速调试数据到底行。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/f4a3659a06394580802578f9b9418620~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=vnyfGqDDIDCBJ8QKBRY1g4%2B6cWo%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">技巧十<span style="color: black;">2、</span>快速数据核对</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">办法</span>：</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">选取</span>要核对的2列数据→<span style="color: black;">运用</span>快捷键【CTRL+G】<span style="color: black;">或</span>按F5调出【定位】窗口→<span style="color: black;">选取</span>【行内容差异单元格】点击确定，就会选中差异数据了。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/97d566c934c342f2a99a2a1be113601e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727968473&x-signature=IS7HZrmC8XwzBIFX6UnX%2BvUERjs%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">以上是【桃大喵学习记】今天的干货分享~觉得内容对你有<span style="color: black;">帮忙</span>，记得顺手点个赞哟~。我会经常分享职场办公软件<span style="color: black;">运用</span>技巧干货！<span style="color: black;">大众</span>有什么问题欢迎留言关注！</p>
请问、你好、求解、谁知道等。 这篇文章真的让我受益匪浅，外链发布感谢分享！
页:
[1]