esc0rp 发表于 2024-10-1 09:29:46

excel怎么样实现按班级统计?


    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这个表有1-20个班(上表班级排名<span style="color: black;">能够</span>忽略不计)，需要计算<span style="color: black;">每一个</span>班级的总分排名的各段人数，分段<span style="color: black;">需求</span>是0-60名，61-200名，201-600名。最后结果如下。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/2a1c47f30a104db7a62b1ade330c36b2~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956299&amp;x-signature=2x06o4fSWhsLFGETGx07oOKvF74%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/23761ee7902b4088b1c91a8f23a69b85~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956299&amp;x-signature=9Mey3gOAxfqfJ%2Fh7J7JCu1xblt8%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 style="color: black;">倘若</span>年级有600<span style="color: black;">上下</span>的学生，先排好了校排名和班级排名，<span style="color: black;">而后</span>统计各班级在1－60名、61－200名、200名以后分组中，各有多少人？</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/4a4c5b60f0d64b34937338ccb0d7476d~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956299&amp;x-signature=sXo7qAlcBquEWdW25OvODiFljJ8%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 style="color: black;">能够</span><span style="color: black;">运用</span>数据透视表来做统计。</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><strong style="color: blue;"><span style="color: black;"><span style="color: black;">插进</span></span></strong><span style="color: black;">选项卡，</span><strong style="color: blue;"><span style="color: black;">表格</span></strong><span style="color: black;">，</span><strong style="color: blue;"><span style="color: black;">数据透视表</span></strong><span style="color: black;">，在弹出对话框中，设置数据透视表位置为当前工作表，<span style="color: black;">而后</span>点击</span><strong style="color: blue;"><span style="color: black;">确定</span></strong><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/527ed50d757d45a29de00cf151e33154~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956299&amp;x-signature=GljcqFgOOIgkZAOAnnlpZChybt4%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">2、</span>字段设置</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">将</span><strong style="color: blue;"><span style="color: black;">班级</span></strong><span style="color: black;">放在行字段，</span><strong style="color: blue;"><span style="color: black;">校名</span></strong><span style="color: black;">放在列字段、再将</span><strong style="color: blue;"><span style="color: black;">班级</span></strong><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/ad5314ef2cad416685b4bfe086febb38~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956299&amp;x-signature=Pob7oAYBQJlb6A%2FsbybTcKDuJOA%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>布局</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">选中数据透视表，在</span><strong style="color: blue;"><span style="color: black;">数据透视表工具</span></strong><span style="color: black;">，</span><strong style="color: blue;"><span style="color: black;">设置</span></strong><span style="color: black;">中，点开<span style="color: black;">归类</span>汇总的下拉选项，<span style="color: black;">选取</span></span><strong style="color: blue;"><span style="color: black;">不<span style="color: black;">表示</span><span style="color: black;">归类</span>汇总</span></strong><span style="color: black;">，点开报表布局的下拉选项，<span style="color: black;">选取</span></span><strong style="color: blue;"><span style="color: black;">以表格形式<span style="color: black;">表示</span></span></strong></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/abcd903879af4d168a5799a8ff2afebf~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956299&amp;x-signature=a4ebrCMbsW5tEOWJsEzL5uB0r5s%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 style="color: black;">此刻</span>有表格和数据源没什么差别，既然要统计排名段的人数，排名的确切数字就第二重要的了，首要的是要将排名做做分组。</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">用鼠标在排名数据上，点击右键，选中</span><strong style="color: blue;"><span style="color: black;">分组</span></strong><span style="color: black;">，在弹出对话框里，设置<span style="color: black;">起始</span>值为61，结束值为200，步长为140，<span style="color: black;">而后</span>点击</span><strong style="color: blue;"><span style="color: black;">确定</span></strong><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/d6ca882bcd1b4ab48b0e4a1f663b2d19~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956299&amp;x-signature=2BwGJ7Y%2BL%2FrWODgdmqykxAAAHhA%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 style="color: black;">为何</span>这么设置呢？题<span style="color: black;">重点</span>求分三级，<span style="color: black;">因此呢</span>一个<span style="color: black;">起始</span>、一个结束就<span style="color: black;">能够</span>将数字序列分成3段，题<span style="color: black;">重点</span>统计1－60段，就取<span style="color: black;">第1</span>个节点为61，</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">结束值直接设置成第二节点值（200）就<span style="color: black;">能够</span>了。</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>第二个节点是200，200-60=140，就设置步长为140，<span style="color: black;">这般</span>就将1－600以60和200为节点为，分<span style="color: black;">成为了</span>三段。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/fccc598df0114b0fb786e0df9b11bb53~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956299&amp;x-signature=zLpMBl%2F9j%2BUbrdtGE2O%2BO9l2xiw%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;">1.<span style="color: black;">重视</span>结束值的<span style="color: black;">体积</span>，按第二节点的值写，不要写大了。</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2.<span style="color: black;">倘若</span>节点多于2个，它们之间的间隔相同，<span style="color: black;">能够</span><span style="color: black;">这般</span>设置，如本例中，统计1-60，61-200，201-340，340-600，将结束值设置为340<span style="color: black;">就可</span>。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/745fbc6fa56149ada3ceaf75b83acae0~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956299&amp;x-signature=NmECtHeC9dUCzakz85RAi9V6SDA%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;">3.<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>




nqkk58 发表于 2024-10-14 21:26:06

你的言辞如同繁星闪烁，点亮了我心中的夜空。

qzmjef 发表于 2024-10-19 07:24:10

“NB”（牛×的缩写，表示叹为观止）‌
页: [1]
查看完整版本: excel怎么样实现按班级统计?