blocks|key|1648922|text|这是如何在Excel+2016%2B中完成的(屏幕截图是西班牙语的，但可以作为指南)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1648923|基于此设置：|1648924|​|1648925|📷|atomic|offset|length|1648926|1648927|步骤：|1648928|1)将数据转换为Excel+结构化表|1648929|选择数据范围|unordered-list-item|1648930|单击Home+_~_|1648931|1648932|1648933|1648934|2)将表添加到Power查询-选中表后，单击表或范围中的数据|1648935|3)调整每个列的数据类型--单击列标题中的图标并选择appropiate数据类型--在本例中，请确保日期表设置为日期类型。|1648936|1648937|1648938|1648939|4)单击“关闭并加载”(展开的选项)，然后选择“关闭”并将其加载到|1648940|1648941|1648942|1648943|5)选择“只连接”并选中“添加到数据模型”复选框(并单击“确定”)。|1648944|1648945|1648946|1648947|6)点击数据集管理数据模型|1648948|1648949|1648950|1648951|7)在表(如果没有重命名的话应该称为Table1+)，选择数据下面的网格的第一个单元格|1648952|1648953|1648954|1648955|8)将以下文本添加到公式栏并单击enter|1648956|Percentage+won+by+month:=COUNTROWS(Tabla1)+/+CALCULATE(COUNTROWS(Tabla1);all(Tabla1[Lead+stage]))|code-block|syntax|javascript|1648957|1648958|1648959|1648960|9)关闭窗口并返回到Excel|1648961|10)在新的空白表中，单击“插入数据透视表”|1648962|1648963|1648964|1648965|11)确保选中“使用数据模型”并单击“确定”。|1648966|1648967|1648968|1648969|12)将字段添加到透视表中，确保您添加的“按月计算的百分比”在values部分中。|1648970|1648971|1648972|1648973|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/7e6a29b8e110726dd74baf80c826e295.png|imageAlt|1|LINK|MUTABLE|url|https://support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664|2|https://developer.qcloudimg.com/http-save/yehe-900000/49df25eedf98b923f64e76541b3a25b5.png|3|https://support.office.com/en-us/article/introduction-to-the-query-editor-power-query-1d6cdb63-bf70-4ae8-a7d5-6ae9547004d9|4|https://developer.qcloudimg.com/http-save/yehe-900000/fabdccd86eb055888d65c3e72faa5749.png|5|https://developer.qcloudimg.com/http-save/yehe-900000/361951a768bc3abd889ae1d827367e5c.png|6|https://developer.qcloudimg.com/http-save/yehe-900000/45079d4cc9cfd37d400ef926516cd2c5.png|7|https://developer.qcloudimg.com/http-save/yehe-900000/bd97d40c8b37d1a222862efc423b26a1.png|8|https://developer.qcloudimg.com/http-save/yehe-900000/89f386f6c55d6500cd3396ea07cd87dc.png|9|https://developer.qcloudimg.com/http-save/yehe-900000/b169ad22c74f7860aa826f10cd72b64e.png|10|https://developer.qcloudimg.com/http-save/yehe-900000/4d475cc9c6cf5c3afedce5f08d722b7f.png|11|https://developer.qcloudimg.com/http-save/yehe-900000/3808e2aedc650d24112848c4c94ef8a3.png|12|https://developer.qcloudimg.com/http-save/yehe-900000/8381863b4a69a6fd060d03e3b052ae7f.png^0|0|0|0|0|1|0|0|0|0|E|4|1|0|0|0|0|0|1|2|0|0|M|8|3|0|0|0|0|1|4|0|0|0|0|0|1|5|0|0|0|0|0|1|6|0|0|0|0|0|1|7|0|0|0|0|0|1|8|0|0|0|0|0|0|1|9|0|0|0|0|0|0|1|A|0|0|0|0|0|1|B|0|0|0|0|0|1|C|0|0^^$0|@$1|2|3|4|5|6|7|3F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|3G|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|3H|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|3I|8|@]|9|@$I|3J|J|3K|1|3L]]|A|$]]|$1|K|3|E|5|6|7|3M|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|3N|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|3O|8|@]|9|@$I|3P|J|3Q|1|3R]]|A|$]]|$1|P|3|Q|5|R|7|3S|8|@]|9|@]|A|$]]|$1|S|3|T|5|R|7|3T|8|@]|9|@]|A|$]]|$1|U|3|E|5|6|7|3U|8|@]|9|@]|A|$]]|$1|V|3|G|5|H|7|3V|8|@]|9|@$I|3W|J|3X|1|3Y]]|A|$]]|$1|W|3|E|5|6|7|3Z|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|40|8|@]|9|@$I|41|J|42|1|43]]|A|$]]|$1|Z|3|10|5|6|7|44|8|@]|9|@]|A|$]]|$1|11|3|E|5|6|7|45|8|@]|9|@]|A|$]]|$1|12|3|G|5|H|7|46|8|@]|9|@$I|47|J|48|1|49]]|A|$]]|$1|13|3|E|5|6|7|4A|8|@]|9|@]|A|$]]|$1|14|3|15|5|6|7|4B|8|@]|9|@]|A|$]]|$1|16|3|E|5|6|7|4C|8|@]|9|@]|A|$]]|$1|17|3|G|5|H|7|4D|8|@]|9|@$I|4E|J|4F|1|4G]]|A|$]]|$1|18|3|E|5|6|7|4H|8|@]|9|@]|A|$]]|$1|19|3|1A|5|6|7|4I|8|@]|9|@]|A|$]]|$1|1B|3|E|5|6|7|4J|8|@]|9|@]|A|$]]|$1|1C|3|G|5|H|7|4K|8|@]|9|@$I|4L|J|4M|1|4N]]|A|$]]|$1|1D|3|E|5|6|7|4O|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|4P|8|@]|9|@]|A|$]]|$1|1G|3|E|5|6|7|4Q|8|@]|9|@]|A|$]]|$1|1H|3|G|5|H|7|4R|8|@]|9|@$I|4S|J|4T|1|4U]]|A|$]]|$1|1I|3|E|5|6|7|4V|8|@]|9|@]|A|$]]|$1|1J|3|1K|5|6|7|4W|8|@]|9|@]|A|$]]|$1|1L|3|E|5|6|7|4X|8|@]|9|@]|A|$]]|$1|1M|3|G|5|H|7|4Y|8|@]|9|@$I|4Z|J|50|1|51]]|A|$]]|$1|1N|3|E|5|6|7|52|8|@]|9|@]|A|$]]|$1|1O|3|1P|5|6|7|53|8|@]|9|@]|A|$]]|$1|1Q|3|1R|5|1S|7|54|8|@]|9|@]|A|$1T|1U]]|$1|1V|3|E|5|6|7|55|8|@]|9|@]|A|$]]|$1|1W|3|G|5|H|7|56|8|@]|9|@$I|57|J|58|1|59]]|A|$]]|$1|1X|3|E|5|6|7|5A|8|@]|9|@]|A|$]]|$1|1Y|3|1Z|5|6|7|5B|8|@]|9|@]|A|$]]|$1|20|3|21|5|6|7|5C|8|@]|9|@]|A|$]]|$1|22|3|E|5|6|7|5D|8|@]|9|@]|A|$]]|$1|23|3|G|5|H|7|5E|8|@]|9|@$I|5F|J|5G|1|5H]]|A|$]]|$1|24|3|E|5|6|7|5I|8|@]|9|@]|A|$]]|$1|25|3|26|5|6|7|5J|8|@]|9|@]|A|$]]|$1|27|3|E|5|6|7|5K|8|@]|9|@]|A|$]]|$1|28|3|G|5|H|7|5L|8|@]|9|@$I|5M|J|5N|1|5O]]|A|$]]|$1|29|3|E|5|6|7|5P|8|@]|9|@]|A|$]]|$1|2A|3|2B|5|6|7|5Q|8|@]|9|@]|A|$]]|$1|2C|3|E|5|6|7|5R|8|@]|9|@]|A|$]]|$1|2D|3|G|5|H|7|5S|8|@]|9|@$I|5T|J|5U|1|5V]]|A|$]]|$1|2E|3|E|5|6|7|5W|8|@]|9|@]|A|$]]|$1|2F|3|-4|5|6|7|5X|8|@]|9|@]|A|$]]]|2G|$2H|$5|2I|2J|2K|A|$2L|2M|2N|-4]]|2O|$5|2P|2J|2Q|A|$2R|2S]]|2T|$5|2I|2J|2K|A|$2L|2U|2N|-4]]|2V|$5|2P|2J|2Q|A|$2R|2W]]|2X|$5|2I|2J|2K|A|$2L|2Y|2N|-4]]|2Z|$5|2I|2J|2K|A|$2L|30|2N|-4]]|31|$5|2I|2J|2K|A|$2L|32|2N|-4]]|33|$5|2I|2J|2K|A|$2L|34|2N|-4]]|35|$5|2I|2J|2K|A|$2L|36|2N|-4]]|37|$5|2I|2J|2K|A|$2L|38|2N|-4]]|39|$5|2I|2J|2K|A|$2L|3A|2N|-4]]|3B|$5|2I|2J|2K|A|$2L|3C|2N|-4]]|3D|$5|2I|2J|2K|A|$2L|3E|2N|-4]]]]

This is how you can get it done in Excel 2016+ (screenshots are in spanish, but serve as a guide).

Based on this setup:

<a href="https://i.stack.imgur.com/WQrad.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WQrad.png" alt="enter image description here"></a>

Steps:

1) Convert your data to an Excel <a href="https://support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664" rel="nofollow noreferrer">structured table</a>

<ul>
<li>Select the data range</li>
<li>Click Home | Format as Table | mark (Use headers)</li>
</ul>

<a href="https://i.stack.imgur.com/U9A8d.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/U9A8d.png" alt="enter image description here"></a>

2) Add table to Power Query
- With table selected, click <a href="https://support.office.com/en-us/article/introduction-to-the-query-editor-power-query-1d6cdb63-bf70-4ae8-a7d5-6ae9547004d9" rel="nofollow noreferrer">Data | From table or range</a>

3) Adjust data types for each column
- Click on the icon in the columns headers and select the appropiate data type
- In this case, make sure Date table is set to the date type

<a href="https://i.stack.imgur.com/aEdKq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aEdKq.png" alt="enter image description here"></a>

4) Click Close and Load (the option that expands) and select Close and load to

<a href="https://i.stack.imgur.com/NlSmY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NlSmY.png" alt="enter image description here"></a>

5) Select "Connection only" and check the "Add to data model" checkbox (and click ok)

<a href="https://i.stack.imgur.com/CQlxZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CQlxZ.png" alt="enter image description here"></a>

6) Click on Data | Manage data model

<a href="https://i.stack.imgur.com/eWUz1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eWUz1.png" alt="enter image description here"></a>

7) On the table (which should be called Table1 if you didn't rename it) select the first cell of the grid below the data

<a href="https://i.stack.imgur.com/VgoBa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VgoBa.png" alt="enter image description here"></a>

8) Add the following text to the formula bar and click enter

<pre><code>Percentage won by month:=COUNTROWS(Tabla1) / CALCULATE(COUNTROWS(Tabla1);all(Tabla1[Lead stage]))
</code></pre>

<a href="https://i.stack.imgur.com/CRtmx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CRtmx.png" alt="enter image description here"></a>

9) Close the windows and go back to Excel

10) In a new blank sheet click Insert | Pivot Table

<a href="https://i.stack.imgur.com/Ds8Xn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ds8Xn.png" alt="enter image description here"></a>

11) Make sure Use Data Model is selected and click Ok

<a href="https://i.stack.imgur.com/0y9VR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0y9VR.png" alt="enter image description here"></a>

12) Add the fields to the pivot table, making sure the measure you added "Percentage won by month" is in the values section

<a href="https://i.stack.imgur.com/Asbog.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Asbog.png" alt="enter image description here"></a>

I have a table (below) and I would like to display the percentage in the column "Count of Value" I can do this but however if I filter the results the percentage applied is only to the filtered data. In here instead of getting what I need which is the percentage of won leads by month (Jan 50% and Feb 75%) I get the percentage based on the total won leads (5, which were 3 in Feb and 3 in Jan 60/40).

I'm not good with Excel at all, I'm trying to help the sales manager with this for 2 days and I can't calculate the totals. I've learned a bit about Excel but I can't figure out how to do this without doing it manually which will be a big pain in the backside.

Any help would be greatly appreciated.

Thank you.

<a href="https://i.stack.imgur.com/6g9Al.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6g9Al.png" alt="Excel Table and Pivot Table"></a>

<a href="https://i.stack.imgur.com/PWPbU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PWPbU.png" alt="Leads Percentage"></a>

Excel Pivot Tables, Calculating a Percentage of Total Values for filtered values

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我有一个表(下面)，我想在列“值计数”中显示百分比，我可以这样做，但是如果我过滤结果，应用的百分比仅用于过滤的数据。在这里，我没有得到我所需要的，即按月计算的韩元领先率(1月50%和2月75%)，而是根据总领先数(5，2月3，1月60/40)得出的百分比。我对Excel一点也不熟悉，我试着帮销售经理做了两天的工作，我无法计算总数。我对Excel有了一点了解，但如果不手动操作，我就搞不懂如何做到这一

问Excel数据透视表，计算筛选值的总值百分比
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel数据透视表，计算筛选值的总值百分比EN