为么Excel数据透视表不可直接计算中位数（值字段设置）？
<span style="color: black;"><span style="color: black;">大众</span>好，我是运营菌。</span><span style="color: black;">今天逛知乎<span style="color: black;">发掘</span>了这个问题，<strong style="color: blue;"><span style="color: black;">为么</span>Excel数据透视表<span style="color: black;">不可</span>直接计算中位数（值字段设置）？<span style="color: black;"><span style="color: black;">然则</span>却<span style="color: black;">能够</span>计算平均数和方差之类的。</span></strong></span><p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRha7ze8tcuGBPKDjfcggkdiaKT0sibiaq6yziaa0au2BKiaklgmRic5VoFYIibS9zbicIsd7VVuLwTiao6hnlxg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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 style="color: black;">一般人</span>理解求中位数比方差简单多，方差要计算一大堆公式。方差公式：<strong style="color: blue;"><span style="color: black;">S^2=1/n[(x1-m)^2+(x2-m)^2+.......+(xn-m)^2]</span></strong></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;">中位数</strong>与<strong style="color: blue;">平均数</strong>方差最<span style="color: black;">显著</span>的区别<span style="color: black;">便是</span><strong style="color: blue;">排序</strong>，<span style="color: black;">而后</span>再找到中间的数据。<span style="color: black;">倘若</span>是偶数，还需要取中间2个数的平均值。</span></p><span style="color: black;">先<span style="color: black;">经过</span>一个简单的例子<span style="color: black;">认识</span>中位数：找出这组数据：23、29、20、32、23、21、33、25 的中位数。</span><span style="color: black;">解：</span><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 style="color: black;"><strong style="color: blue;"><span style="color: black;">20、21、23、23、25、29、32、33</span></strong></span><span style="color: black;"><span style="color: black;">由于</span>该组数据一共由8个数据<span style="color: black;">构成</span>，即n为偶数，故按中位数的计算<span style="color: black;">办法</span><strong style="color: blue;">（23+25）/2=24</strong>，得到中位数，即第四个数和第五个数的平均数。</span><span style="color: black;">在Excel中，<span style="color: black;">能够</span><span style="color: black;">经过</span>Excel函数Median统计一组数的中位数。数据透视表确实<span style="color: black;">无</span>中位数的汇总依据，如需要中位数<span style="color: black;">表示</span>在透视表上，可<span style="color: black;">经过</span>添加辅助字段的<span style="color: black;">办法</span>求得。</span><strong style="color: blue;">（1）单<span style="color: black;">要求</span>求中位数</strong><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 style="color: black;">经过</span>输入公式：=<strong style="color: blue;">MEDIAN(</strong><strong style="color: blue;"><span style="color: black;">I</span></strong><strong style="color: blue;"><span style="color: black;">F(</span></strong>$B$2:$B$64=<span style="color: black;">B2</span>,$E$2:$E$64<strong style="color: blue;"><span style="color: black;">)</span></strong><strong style="color: blue;">)</strong></span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRhZuIib4TrlS2yoiaVOkHsyqJxfZwNMttpTp6yTDG2ThX6d15b4gZicphw3nH7ic8EggVSwPib0aWfYKCKA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p><span style="color: black;">这是个嵌套函数，<span style="color: black;">因为</span>公式<span style="color: black;">包括</span>数组，输入公式后，需要按下<strong style="color: blue;">Ctrl+shift+Enter</strong>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">结尾。</p>
</span><span style="color: black;"><span style="color: black;">选取</span>数据，<span style="color: black;">插进</span>数据透视表，在行标签<span style="color: black;">安置</span><strong style="color: blue;">【<span style="color: black;">分部</span>】【姓名】</strong>，值区域<span style="color: black;">安置</span><strong style="color: blue;">【工资】</strong>和<strong style="color: blue;">【<span style="color: black;">分部</span>中位数】</strong>。</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRhZuIib4TrlS2yoiaVOkHsyqJxjicEN1EUh4dhRPrRicPdxpP0W6K6OibjDTZVtIEPKsDyfiaiczH9YJh3jpA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p><span style="color: black;">值字段的汇总依据均需要改为<strong style="color: blue;">平均值</strong>，<span style="color: black;">倘若</span><span style="color: black;">插进</span><strong style="color: blue;">组合图</strong>，<span style="color: black;">能够</span>呈现出如下：</span><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRhZuIib4TrlS2yoiaVOkHsyqJx8xIUw27HHicjNhLGBA8u8xf236tjR89mkwdd8FcynD2XqWYRoatyKuQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><strong style="color: blue;">（2）多<span style="color: black;">要求</span>求中位数</strong><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>2个IF嵌套，公式为：</span><span style="color: black;">=<strong style="color: blue;">MEDIAN(</strong><strong style="color: blue;"><span style="color: black;">IF</span></strong><strong style="color: blue;"><span style="color: black;">(</span></strong>[<span style="color: black;">分部</span>]=[@<span style="color: black;">分部</span>],<strong style="color: blue;"><span style="color: black;">IF(</span></strong>[月份]=[@月份],[工资]<strong style="color: blue;"><span style="color: black;">)</span></strong><span style="color: black;">)</span><strong style="color: blue;">)</strong></span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRhYZFfQkDSSss48BQ01amnsA34vSax9yGkH6DCwElDpl7PEbDbJdWwNicyH98QHqIejKY9ef3yM1KnA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p><span style="color: black;"><span style="color: black;">由于</span>数据源提前转化为超级表，公式中<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>所在行对应该列的单元格内容。</span><span style="color: black;">这是<span style="color: black;">亦</span>是嵌套函数，<span style="color: black;">因为</span>公式<span style="color: black;">包括</span>数组，输入公式后，需要按下<strong style="color: blue;">Ctrl+shift+Enter</strong>结尾。</span><span style="color: black;">
<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>
</span><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRhYZFfQkDSSss48BQ01amnsAicbvbzhrZct0AHyrmV7oaIzbxBcpQT7ta4tMAQ5l3opClJoORTKa4icw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><strong style="color: blue;">（3）<span style="color: black;">重视</span>事项</strong><span style="color: black;">
<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>
</span><span style="color: black;">
<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>
</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRhYZFfQkDSSss48BQ01amnsAAdGyiaLWuKdD4neDABwDFPTpn4egicsbfibhOdczeSMuibVdiaFKA8m2Wcw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p><span style="color: black;">
<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>
</span><span style="color: black;"><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRhZuIib4TrlS2yoiaVOkHsyqJx8xIUw27HHicjNhLGBA8u8xf236tjR89mkwdd8FcynD2XqWYRoatyKuQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></span><span style="color: black;">以上<span style="color: black;">便是</span>数据透视表求中位数的<span style="color: black;">办法</span>。</span><span style="color: black;">数据透视表是<span style="color: black;">自己</span>录制并售卖的<span style="color: black;">第1</span>门课程，如今<span style="color: black;">已然</span>过了<strong style="color: blue;">3年有余</strong>，为感谢老学员的支持，于<strong style="color: blue;">2022年11月</strong>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">对内容做了一次大更新。老<span style="color: black;">朋友</span>能直接登录网易云课堂学习哈~</p>
</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRhZuIib4TrlS2yoiaVOkHsyqJxVnia8te9mechP3gwjhvZkgc5RqjeCf7URbDtX7yczb4q10kicZwcOHVw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p><span style="color: black;">数据透视表<span style="color: black;">做为</span>Excel中必学的功能，<span style="color: black;">举荐</span><span style="color: black;">大众</span>系统学习，学员们都反馈能<span style="color: black;">显著</span><span style="color: black;">加强</span>数据分析效率。</span><span style="color: black;"><strong style="color: blue;">《Excel最强功能，数据透视表》</strong></span><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRhZeeV3trpG95rYyAOiaTR2f1UEzoGoJciacSwE2PlMwsLVnq228mAudHARJMCpjePcOjZJqMIlWBFBw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<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;">数据透视表分为10章节，一共56课时，累计时长350分钟，分为获<strong style="color: blue;">取数据源、四大区域、组合、汇总依据、排序筛选、图表可视化</strong>
</span></p>
<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;"><img src="https://mmbiz.qpic.cn/mmbiz_png/6OkYNUQpRhaCpl85tQFicslgKaMaefOicDsqn4SPSdBWQOSC76XuPicExmGiaogP1r1FmdkXVavhvJ2o1morUjJwSA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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 style="color: black;">重点</span>是奔着我的课件，数据，可视化来的。<span style="color: black;">因此</span>，课件必须给<span style="color: black;">大众</span>安排上。网易云课堂有系统私信自动发送课件链接，<span style="color: black;">亦</span>可添加我的<span style="color: black;">微X</span>：Excel_1994 享受一对一辅导答疑。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_gif/6OkYNUQpRhaCpl85tQFicslgKaMaefOicD1wicw7UAAkK1hqX8rMITgEKgsuicW7K0UG99Lpz3puz8iaSyKT9T7O28A/640?wx_fmt=gif&tp=webp&wxfrom=5&wx_lazy=1" style="width: 50%; margin-bottom: 20px;"></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;"><img src="https://mmbiz.qpic.cn/mmbiz_gif/6OkYNUQpRhaCpl85tQFicslgKaMaefOicDx6ADFM6IxibSTiaHBMFwy7dcY9pXazHhUGpx3bJZoGIzRJ64IUw2MxbQ/640?wx_fmt=gif&tp=webp&wxfrom=5&wx_lazy=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">疫情数据看版▲</span></p><img src="https://mmbiz.qpic.cn/mmbiz_gif/7QRTvkK2qC6iavic0tIJIoZCwKvUYnFFiaibgSm6mrFp1ZjAg4ITRicicuLN88YodIuqtF4DcUs9sruBa0bFLtX59lQQ/640?wx_fmt=gif&tp=webp&wxfrom=5&wx_lazy=1" style="width: 50%; margin-bottom: 20px;">戳<span style="color: black;">“阅读原文”领取参加课程</span>!
“沙发”（SF，第一个回帖的人） 期待楼主的下一次分享！” 软文发布平台 http://www.fok120.com/
页:
[1]