3小时整理了15个实用的excel小技巧，动图演示，让你一看就懂
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Hell，<span style="color: black;">大众</span>好，很久没跟<span style="color: black;">大众</span>分享Excel小技巧，今天花费了3小时整理汇总了15个，我觉得工作经常用到的excel小技巧，并且<span style="color: black;">协同</span>动图演示，让你一看就懂，现学现用，快速<span style="color: black;">加强</span>工作效率，话不多说，让<span style="color: black;">咱们</span>直接<span style="color: black;">起始</span>吧</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">1、</span>批量添加单位</h1>
<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>演示下。</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】调出格式窗口，点击【自定义】在类型中输入0"<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/pgc-image/ba4e02d08db34df79ce0c387dcacccb9~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=SkzZjSX0zfSZCEZ58ANw76UFwkI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">2、</span>设置万元<span style="color: black;">表示</span></h1>
<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>1位小数</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+1调出格式窗口，随后点击【自定义】在类型中输入：0!.0,"万元"<span style="color: black;">而后</span>点击确定，至此设置完毕了</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/pgc-image/524b6c72164e482a88750ee02d978969~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=X%2F7u%2Bk3SwMm930Q%2BdYza3yws%2F9Y%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">3、</span>在数字前面<span style="color: black;">表示</span>0</h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">在数字前面<span style="color: black;">表示</span>0<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;"><span style="color: black;">选取</span>数据区域，<span style="color: black;">而后</span>按下【Ctrl+1】调出格式设置窗口，随后点击自定义，在类型中输入000000<span style="color: black;">而后</span>点击确定，<span style="color: black;">这般</span>的话输入23就会<span style="color: black;">表示</span>为000023，<span style="color: black;">亦</span><span style="color: black;">便是</span>说你的数据是几位就设置几个0</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/bea7f443c00f4cea8fb96d3403f95cd1~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=4ROIy5esEotY8GF4Wg%2FAhpjtPGg%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">4、</span>快速制作动图图表</h1>
<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><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>点击切片器中的姓名来快速切换图表，达到制作动态图表的效果</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/f928a89a79ef49faaad4b2015bb83976~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=stZJ4gDAAK0MSMehT3ONoQ6Lttw%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">5、</span>核对格式<span style="color: black;">同样</span>的表格</h1>
<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>性粘贴来核对</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>复制数据表1，<span style="color: black;">而后</span>点击数据表2对应的位置，点击鼠标右键找到<span style="color: black;">选取</span>性粘贴，随后找到运算<span style="color: black;">选取</span>减<span style="color: black;">就可</span>，<span style="color: black;">倘若</span>结果为0就<span style="color: black;">暗示</span>两表数据是<span style="color: black;">同样</span>的，结果不为0的<span style="color: black;">便是</span>差异数据</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/366c93da6c2d4ec59ec8d4f7005bf978~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=Jciiz1DAhOTy9OdUsbqOLV64VhM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">6、</span>禁止合并单元格</h1>
<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>合并单元格呢？最简单的<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>点击表格，<span style="color: black;">而后</span>按下Ctrl+T就<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/pgc-image/760949ff439d4f1ead5607b667c5ba3a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=gvoEIMvNrZrPRyXPJTjjD4by%2FXE%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">7、</span>禁止输入重复数据</h1>
<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><span style="color: black;">选取</span>需要设置的数据区域，<span style="color: black;">而后</span>点击【数据】功能组找到数据验证，将【<span style="color: black;">准许</span>】设置为【自定义】随后在下方将公式设置为=COUNTIF(A:A,A4)=1，在<span style="color: black;">这儿</span>A4<span style="color: black;">便是</span><span style="color: black;">第1</span>个姓名的位置</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/3be2d4fe643741acbe1f7144bbabb9c5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=g7mQkU5vAIineic%2FnoE15r1u63w%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">8、</span><span style="color: black;">照片</span>跟随表格变化</h1>
<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>利用复制粘贴</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></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/9725eb85fe05432c94af268a3526b1e4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=8EtZ9KWA0CtGxer0CSfi5DEMpAE%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">9、</span>批量添加下划线</h1>
<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><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>批量生成下划线了</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/2bbc74d6d3f1450d81d309305d8270e7~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=mn08P5RiUWeo82qh34tJ3AKusjU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">十、批量运算（加减乘除）</h1>
<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>5分</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">首要</span>输入一个5，<span style="color: black;">而后</span>复制这个5，随后<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/pgc-image/4c668d8174634fcb9551e6d97e959e4b~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=0IVZXeRJUVpM1YEBnjgjn6%2B%2F%2B5U%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">十<span style="color: black;">1、</span>取消合并批量填充</h1>
<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+g调出定位，点击【定位<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>点击<span style="color: black;">第1</span>个数据，最后按下Ctrl+回车就能实现批量填充数据了</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/871fa5df4a8244268019980aeff5aacc~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=cVImaJ18Gksjuod84lgpuLiPM6w%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">十<span style="color: black;">2、</span>设置手机号码<span style="color: black;">表示</span>格式</h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">假设在这，<span style="color: black;">咱们</span>想要把手机号码设置为344的<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>按下Ctrl+1调出格式窗口，点击自定义在类型中输入000-0000-0000点击确定<span style="color: black;">就可</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/7946ce7cb2204b8db64d096d7c44d960~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=QjMF3cLOknX0SxfObSgnijZ0heE%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">十<span style="color: black;">3、</span>限制数据长度</h1>
<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>想要设置仅仅只能在单元格中录入11位的数据</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>【等于】，将长度设置为11点击确定<span style="color: black;">就可</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/da2eac6b50b04b26a05331a4cb9b5b82~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=ukcd5KfhKRfCwEksT9YKENNB7vk%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">十<span style="color: black;">4、</span>下拉菜单自动更新</h1>
<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>在制作下拉菜单之前，将数据按下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>同步更新的</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/e1040553c96d4b45b261ffe576b4f8e0~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=X9Uuk%2BEuixrtYTc%2FwxTRGk2sqks%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">十<span style="color: black;">5、</span>自动填充公式</h1>
<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+T把普通表转换为超级表之后，在旁边输入公式，公式是<span style="color: black;">能够</span>自定向下填充的，非常的方便</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/5922ec55943844c5b98f76ec4a56dbf0~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727969962&x-signature=8gDQlsYUoIHrAp5WRYQ6aBGX8Uc%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>今天分享的15个Excel动图小技巧，你觉得实用吗？有疑问的话欢迎留言讨论</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>excel技巧</p>
<div style="color: black; text-align: left; margin-bottom: 10px;">
<div style="color: black; text-align: left; margin-bottom: 10px;">（此处已添加圈子卡片，请到今日头条客户端查看）</div>
</div>
你的言辞如同繁星闪烁，点亮了我心中的夜空。
页:
[1]