借助GROUPBY函数，手搓数据透视表
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">GROUPBY函数的<span style="color: black;">功效</span>是按指定字段进行聚合汇总，<span style="color: black;">最后</span>的效果类似于数据透视表。</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 style="color: black;">功效</span>：</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;">GROUPBY(行标签,值字段,汇总的函数,[<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></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></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 style="color: black;">瞧瞧</span>这个函数的<span style="color: black;">有些</span>典型应用：</span></span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/51ce3d3461ed42dfa3f586f998d975c4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954441&x-signature=FUqklN8naFqdsETfSCQagoNIYhg%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;"><strong style="color: blue;"><span style="color: black;">1：汇总各<span style="color: black;">营销</span>人员的<span style="color: black;">营销</span>总量</span></strong></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;">G2单元格输入以下公式：</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;">=GROUPBY(B1:B201,D1:D201,SUM,3)</span></span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/527cd9a604e24912bebe6c66107babbb~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954441&x-signature=9U4QFPRmot2SO4ke%2F35ePvZ%2BmIk%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 style="color: black;">第1</span>个参数B1:B201，<span style="color: black;">暗示</span>要<span style="color: black;">按照</span>B列的<span style="color: black;">营销</span>人员进行汇总。<span style="color: black;">能够</span><span style="color: black;">按照</span>需要<span style="color: black;">选取</span>多列。</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;">第二个参数D1:D201，是要汇总的数值区域。<span style="color: black;">能够</span><span style="color: black;">按照</span>需要<span style="color: black;">选取</span>多列。</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;">第三个参数SUM，<span style="color: black;">暗示</span>要汇总的方式是求和。</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 style="color: black;">运用</span>3，<span style="color: black;">暗示</span><span style="color: black;">表示</span>字段标题。</span></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">2：汇总各<span style="color: black;">营销</span>人员的<span style="color: black;">营销</span>总额和<span style="color: black;">营销</span>额平均值</span></strong></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;">G2单元格输入以下公式：</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;">=GROUPBY(B1:B201,E1:E201,HSTACK(SUM,AVERAGE),,0)</span></span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/b855323b4c3b45fc91529165270cdd3d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954441&x-signature=LKDPUj9u2dzCt%2FrRvis%2BabV1HJw%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 style="color: black;">运用</span>HSTACK函数将SUM和AVERAGE连接到<span style="color: black;">一块</span>，<span style="color: black;">暗示</span>对第二参数分别执行求和以及平均值汇总。</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 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></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;">HSTACK(SUM,AVERAGE,COUNT)</span></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">3：汇总各<span style="color: black;">营销</span>人员的<span style="color: black;">营销</span>总量和<span style="color: black;">营销</span>额平均值</span></strong></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 style="color: black;">倘若</span>将第二参数设置成多列，第三参数<span style="color: black;">运用</span>HSTACK的方式，可对<span style="color: black;">区别</span>列分别执行<span style="color: black;">区别</span>的汇总方式。</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 style="color: black;">便是</span>对D列的数量进行求和，<span style="color: black;">同期</span>对E列的<span style="color: black;">营销</span>额计算平均值。</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;">=GROUPBY(B2:B201,D2:E201,HSTACK(SUM,AVERAGE))</span></span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/c244e8e8d57b4224b827f486e315385f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954441&x-signature=aic5jYB0QoS6gELWQzNC1YRcXHw%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;"><strong style="color: blue;"><span style="color: black;">4：生成带小计和总计的汇总表</span></strong></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 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>第五参数来设置<span style="color: black;">表示</span>总计和小计。</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;">如下图所示，G2单元格公式为：</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;">=GROUPBY(B1:C201,D1:E201,SUM,3,2)</span></span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/07919a78e9b84948ae86fd3ec0913d7c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954441&x-signature=z6bSkBcq7z0zao5pDF9F5Z4jago%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 style="color: black;">运用</span>2，<span style="color: black;">暗示</span><span style="color: black;">同期</span><span style="color: black;">表示</span>总计和小计。</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 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>小计时，公式结果将返回错误值。</span></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">5：生成可排序的汇总表</span></strong></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></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 style="color: black;">运用</span>-3，<span style="color: black;">暗示</span>对汇总表中的第三列进行排序，负数时为降序，正数时为升序。</span></span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/26953e3653094373b6c7479f6c9599c7~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954441&x-signature=iQIp%2BLWYiForzvArvWSmE6laKsc%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;"><strong style="color: blue;"><span style="color: black;">6：按<span style="color: black;">分部</span>汇总人员姓名</span></strong></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;">除了数值计算，GROUPBY函数还<span style="color: black;">能够</span>对文本内容进行聚合。</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 style="color: black;">运用</span>以下公式，可按<span style="color: black;">分部</span>对人员姓名进行汇总。</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;">=GROUPBY(A1:A20,B1:B20,ARRAYTOTEXT,,0)</span></span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/3238a3dd69fb4bc1906deab85624405a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954441&x-signature=4a4X%2B5Tox1fqy58fJvNIP3%2B3hX8%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;">第三参数ARRAYTOTEXT，<span style="color: black;">暗示</span>将第二参数的数组转换成文本形式。</span></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">7、按<span style="color: black;">要求</span>筛选的汇总表</span></strong></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 style="color: black;">能够</span>设置筛选<span style="color: black;">要求</span>，从而<span style="color: black;">得到</span>符合指定<span style="color: black;">要求</span>的汇总表。</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 style="color: black;">运用</span>以下公式，可对<span style="color: black;">区别</span><span style="color: black;">分部</span>的男士姓名进行聚合。</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;">=GROUPBY(A1:A20,B1:B20,ARRAYTOTEXT,3,0,,C1:C20="男")</span></span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/590dc4b4af094f7b933a6fcd08a7e1fe~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954441&x-signature=1mkScd5KuxDafVnYkWkQeC0Alns%3D" style="width: 50%; margin-bottom: 20px;"></div>
你的留言真是温暖如春，让我感受到了无尽的支持与鼓励。 感谢您的精彩评论，为我带来了新的思考角度。
页:
[1]