excel 宏 数据透视表_数据透视表宏和Excel赠品

excel 宏 数据透视表

My friend and client, Bob Ryan, from Simply Learning Excel, has just published a hands-on, no fluff, Excel book -- Simply Learning Excel 2007: Learn the Essentials in 8 Hours or Less.

我的朋友和客户Bob Simply来自Excel的简单学习Excel，刚刚出版了一本动手的Excel书籍，即Excel 2007：在8小时或更短的时间内学习基本知识。

To celebrate the book launch, I asked Bob to share one of his favourite Excel tips with you, and you can read it below.

为了庆祝这本书的发行，我请鲍勃与您分享他最喜欢的Excel技巧之一，您可以在下面阅读。

Bob is also having an Excel Giveaway on his Simply Learning Excel blog this week, and the details of that are also listed below. It's a great giveaway, even if you already know the Excel essentials.

Bob本周在他的Simply Learning Excel博客上也有一个Excel赠品，其详细信息也列在下面。 即使您已经知道Excel的要点，这也是一个很棒的礼物。

The top prize a personalized 1-hour online Excel session with Bob, and it's transferable! So, if your co-worker (or relative) is driving you crazy with Excel questions, you can send them to Bob for help. Sweet!

最高奖项是与Bob进行的个性化1小时在线Excel会话，它可以转让！ 因此，如果您的同事(或亲戚)因Excel问题而使您发疯，则可以将其发送给Bob寻求帮助。 甜！

And now, here's Bob...

现在，这里是鲍勃...

建立经典数据透视表设置的宏，再加上... (Macro to Establish Classic PivotTable Settings, Plus...)

Some time ago, I was getting ready to train a group of people about PivotTables. As I was documenting the steps, I started feeling more and more annoyed at the number of steps it took to create the kind of PivotTable I typically use.

前段时间，我准备向一群人介绍数据透视表。 在记录这些步骤时，我开始对创建通常使用的数据透视表所需的步骤数量感到越来越烦恼。

So, I wrote a macro to automate the steps. (I also submitted a suggestion to Microsoft to allow users to create a customized standard/default PivotTable, but I don't see it in Excel 2010.)

因此，我编写了一个宏来自动执行这些步骤。 (我还向Microsoft提出了一项建议，允许用户创建自定义的标准/默认数据透视表，但我在Excel 2010中看不到它。)

I wanted to share this macro, but since my website is generally geared to folks who don't know about or need macros (yet), I asked Debra if I could be a guest writer, and she kindly agreed.

我想共享这个宏，但是由于我的网站通常面向尚不了解或不需要宏的人们，所以我问Debra我是否可以做特邀作家，她表示同意。

A final note: While I appreciate Debra's willingness to share this information on her site, the content really belongs to her because most of this information came from her books, website, and/or her personally. I hope you find this useful.

最后一点：尽管我很欣赏Debra愿意在她的网站上共享此信息，但该内容确实属于她，因为这些信息大部分来自她的书本，网站和/或她个人。 希望这个对你有帮助。

宏做什么 (What the Macro Does)

Once you insert a PivotTable and enter a field(s) into the Values area, the code does the following to PivotTable(1) on the active sheet:

一旦插入数据透视表并在“值”区域中输入一个或多个字段，代码就会对活动工作表上的数据透视表(1)执行以下操作：

1. Applies the Classic PivotTable display, with gridlines and no colors (I like this so I can Copy the PivotTable and Paste Special Values and Formatting.);
   应用经典的数据透视表显示，没有网格线且没有颜色(我喜欢这样，所以我可以复制数据透视表并粘贴特殊值和格式。)；
2. Ensures that only data that still exists in the data that drives the PivotTable will appear in the PivotTable dropdown lists.
   确保仅在驱动数据透视表的数据中仍然存在的数据将出现在数据透视表下拉列表中。
3. Sets all fields to ascending order with no subtotals, including fields that are not in the Row Labels or Column Labels areas, and;
   将所有字段设置为升序，没有小计，包括不在“行标签”或“列标签”区域中的字段，以及；
4. For the data field(s) in the Values area, changes the setting to Sum, changes the number format, and, if the field in the Values area is named "Amount" or "Total Amount" it shortens the label in the PivotTable to "Sum Amt" or "Sum TtlAmt" respectively.
   对于“值”区域中的数据字段，将设置更改为“求和”，更改数字格式，并且，如果“值”区域中的字段名为“金额”或“总计”，则会将数据透视表中的标签缩短为分别为“ Sum Amt”或“ Sum TtlAmt”。

下载样本文件 (Download the Sample File)

You can view the pivot table formatting macro code, and download Bob's Format Pivot Table Macro sample file.

您可以查看数据透视表格式宏代码，并下载Bob的格式数据透视表宏示例文件 。

The file is zipped, and in Excel 2007 format. Because it contains a macro, you'll have to enable macros when you open the file.

该文件已压缩，并且为Excel 2007格式。 因为它包含一个宏，所以打开文件时必须启用宏。

“简单”赠品 (A "Simple" Giveaway)

赠品奖 (The Giveaway Prizes)

• The top prize, with a value of approximately $145, is a copy of the book Simply Learning Excel 2007 and a live, personal, one-hour Excel session with Bob via WebEx. Bob will cover and/or answer any questions about any topic from the book, e.g. PivotTables, Filters, VLOOKUP, etc. Bob will contact the winner to arrange a mutually convenient time, and also find out what the winner would like to cover. This prize is transferrable, so the winner can give it to a family member or co-worker, who needs some extra help, or they could even share it with a few co-workers, in a meeting room.
  最高奖金约为145美元，是《 Simply Learning Excel 2007》一书的副本，以及通过WebEx与Bob进行的实时，个人和一小时Excel会话。 鲍勃(Bob)将涵盖和/或回答关于本书中任何主题的任何问题，例如数据透视表，过滤器，VLOOKUP等。鲍勃(Bob)将与获胜者联系以安排双方都方便的时间，并找出获胜者希望涵盖的内容。 该奖项可以转让，因此获胜者可以将其赠予需要一些额外帮助的家庭成员或同事，或者甚至可以在会议室与几个同事分享。
• There will be four runners-up, and each one will win a free book, with a value of approximately $20 each. They, along with any reader of the book, can get their questions answered at no extra cost via a live WebEx session by submitting their questions to Ask the Author... LIVE!™ at www.SimplyLearningExcel.com.
  将有四名亚军，每人将赢得一本免费书，每本价值约20美元。 他们可以与书中的任何读者一起，通过将实时提问提交到www.SimplyLearningExcel.com上的Ask the Author ... LIVE！™，来通过在线WebEx会话免费获得答案。

输入方式 (How to Enter)

Simply submit your answer to this question at https://simplylearningexcel.com/blog/2010/08/15/a-simple-giveaway/:

只需在https://simplylearningexcel.com/blog/2010/08/15/a-simple-giveaway/提交您对这个问题的答案：

• In all the years (or days) that you have known Excel, what is the most valuable thing – feature, formula, etc – you have learned about Excel,
  在您熟悉Excel的所有这些年(或几天)中，最有价值的东西是-功能，公式等-您已经了解了Excel，

and/or

和/或

• What is the one thing about Excel that you have shared with others that they have valued most? Was it PivotTables? Keyboard shortcuts? Spreadsheet design? Macros? Please let us know.
  您与他人共享的Excel最有价值的一件事是什么？ 是数据透视表吗？ 键盘快捷键？ 电子表格设计？ 宏？ 请告诉我们。

赠品规则 (The Giveaway Rules)

• All responses must be submitted to Bob's blog at https://simplylearningexcel.com/blog/2010/08/15/a-simple-giveaway/.

  所有回复必须提交到Bob的博客， 网址为https://simplylearningexcel.com/blog/2010/08/15/a-simple-giveaway/ 。

• The entry deadline is 12:00 noon (Eastern time zone) on Monday, August 23rd, 2010.
  报名截止日期为2010年8月23日星期一中午12:00(东部时区)。

• One entry per person – any additional entries will be deleted from the draw, with one exception. If you answer both parts of the question, you will be entered twice.

  每人一个条目–除例外外 ，所有其他条目都将从抽奖中删除。 如果您同时回答问题的两个部分，则将输入两次。

• A random draw will select the winner from all valid entries.
  随机抽奖将从所有有效参赛作品中选择获胜者。
• The winner will be notified by email, so please provide a valid email address. This will not be publicly visible.
  获奖者将通过电子邮件收到通知，因此请提供有效的电子邮件地址。 这不会公开显示。
• The winner will be announced on Bob's blog on Tuesday, August 24th. Good Luck!
  获奖者将于8月24日星期二在Bob的博客上宣布。 祝好运！

观看视频 (Watch the Video)

To see how much time you can save by using a macro to format a pivot table, watch this video.

若要查看使用宏格式化数据透视表可以节省多少时间，请观看此视频。

It took me a couple of minutes to manually format the Excel pivot table, and change some of the pivot table options, and just a couple of seconds to do all the same steps with Bob's macro.

我花了几分钟来手动格式化Excel数据透视表，并更改了一些数据透视表选项，花了几秒钟来完成Bob宏的所有相同步骤。

Note: If you record your own pivot table formatting macro, follow Bob's example to add variables, so the macro works on any pivot table, no matter what the field names are, or where it's located.

注意 ：如果您记录自己的数据透视表格式宏，请按照Bob的示例添加变量，因此该宏可在任何数据透视表上使用，无论字段名称是什么，或它在何处。

演示地址

关于作者 (About the Author)

Robert Ryan, MBA, CPA is a long-time passionate user of Excel, the author of "Simply Learning Excel 2007: Learn the Essentials in 8 Hours or Less," a unique step-by step book designed for basic to intermediate users, and the host of "Ask the Author... LIVE!™" where Bob answers questions from readers of his book in live WebEx sessions at no extra cost.

罗伯特·瑞安(Robert Ryan)，工商管理硕士(MBA)，注册会计师(CPA)是一位长期热情的用户，其著作《简单学习Excel 2007：在8小时或更短的时间内学习基本知识》是一本专为基础到中级用户而设计的独特分步指南，主持人“问作者... LIVE！™”，其中Bob在实时WebEx会话中回答了他的书的读者的问题，无需任何额外费用。

翻译自: https://contexturesblog.com/archives/2010/08/16/pivot-table-macro-and-excel-giveaway/

excel 宏 数据透视表