Excel生成自动序号，这么简单，快快来学！
<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>，今天分享几种加序号的小技巧</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">1、普通序号</h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">当<span style="color: black;">咱们</span>想生成从1<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>先输入前2个序号，<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/36f81cabbd5f4bb19ccd9a71b1c0daf4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=PCGXlAf%2BO9CImi5f0n5AKxVP7LE%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>公式法来生成，<span style="color: black;">咱们</span>输入的公式是：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=N(A1)+1</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">N函数中的内容为数字时，返回数字本身，否则返回为0，<span style="color: black;">由于</span>A1是放的文本，<span style="color: black;">因此</span><span style="color: black;">便是</span>0+1=1</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/96a94f4165d54b3a878e6169fdc6898d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=uXZRtWlQvfbNkPjde3KRus1S%2Fc4%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">2、分组序号</h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">例如，市场部，<span style="color: black;">咱们</span>需要从1<span style="color: black;">起始</span>编号，<span style="color: black;">而后</span>遇到下一个<span style="color: black;">分部</span>，<span style="color: black;">咱们</span>又要重新从1<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>公式：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=IF(B2=B1,N(A1)+1,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>和上一条一致，<span style="color: black;">倘若</span>一致的<span style="color: black;">状况</span>下，上一个数据+1，否则就从1<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/1d6853950d6a4e0ea8315fd992222218~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=ZDKSMvZ9okTEdo7urFlzhg6O5DM%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>的<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/d1420f090c414f71bedb3360072c1143~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=4MG%2FBLYwAGoWL%2F8RxGHyXfp0bns%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>，<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;">=COUNTIFS($B$2:B2,B2)</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/d13bc6c7959145ebbe8d816550fdfba7~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=IX9s8MA8h7DGRxktNTWNsH%2BEgEM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">3、智能编号</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>智能序号</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>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=ROW()-1</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">ROW()函数返回的是当前的行号，<span style="color: black;">由于</span>从第2行<span style="color: black;">起始</span>输入公式，<span style="color: black;">因此</span>需要减1</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/46d5f5ddcd4e45ea911d2d51f4d6bc37~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=dIHBGB9cmSM24gWSLhDFpNXyYZw%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>删除掉几行数据，它的序号还是保持连续的</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/9b0c0ff4cd094663a4eee3a3a7a31315~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=ZxPUqfQXzjlleeQqPEavAOSNChU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">4、筛选编号</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>自动的连接上</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/2c1a5bae8eee44c7975ea2ef17c30da4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=4fCm8EsuxhtzdHiW55P5QJ4P4HM%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>需要使用公式：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=SUBTOTAL(103,$B$2:B2)</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/59e2f5a417464e948dd809695f95403e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=4aTa0yMz5A%2FTUPNWHtsUEtAXoxk%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>是从1<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/38cc5be7e0214cd991ca8e169666905c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=JRTm9xV%2BvjZmLa0uy4NE%2BFUdesI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">5、合并单元格编号</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>需要快速的添加序号</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/9ca5b34344dd4b77be489ff5bffeb9b1~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=YubExa9P6tism2CQcclNF1jzQFM%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>选中所有的合并单元格，A2:A8单元格，<span style="color: black;">而后</span>在公式编辑栏输入的公式是：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=MAX($A$1:A1)+1</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">按CTRL+回车，就<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/ef811decc6a241a1b3eb1af2c4bb426c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727980880&x-signature=Kog79oEUqQ%2FKvGtJz%2FNsp5XNDCU%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>
这篇文章真的让我受益匪浅，外链发布感谢分享！ 期待与你深入交流，共探知识的无穷魅力。 同意、说得对、没错、我也是这么想的等。 我完全同意你的看法，期待我们能深入探讨这个问题。
页:
[1]