推荐阅读
excel文件加密
给excel表格添加密码可以防止别人查看自己的工作簿，但是密码一定要记住，否则没有密码自己也进不去，excel2003文件加密的具体方法有下面的几种。 excel加密码方法一、 1、我们单击文件菜单，然后选择另存为命令，在弹出的另存为对话框中输入文件名称，如图1。 2、我们在该对话框中选择工具中的常规选项。图1 3、这时会弹出一个保存选项对话框，我们在里面分别输入打开权限密码和修改权限密码，最后单击确定会让你重新输入一遍密码，然后单击再次单击确定按钮，保存文件即可，如图2所示。图2 excel加密码方法二、
wps表格求积怎么操作
问在wps中编辑文档时，有时会需要插入简单的表格，但是数据求积就不知道怎么办了。有什么办法可以解决吗？1、首先将光标定位在D2单元格，然后点击布局-数据-公式。2、在“公式”对话框中输入公式“=B2*C2”，然后点击“确定”按钮。3、下面的数据求积也和以上步骤一样，效果所示。
excel2010输入特殊符号的方法
Excel中的特殊符号具体该如何输入呢?接下来是小编为大家带来的excel2010输入特殊符号的方法，供大家参考。 excel2010输入特殊符号的方法： 输入特殊符号步骤1：打开一个Excel2010文件，我们选中一个输入单元格。如下图所示。 输入特殊符号步骤2：在输入法小窗口，我们点击最后一个工具键。如下图所示。 输入特殊符号步骤3：出现的小窗口，我们看到笑脸，这就是符号输入。如下图所示。 输入特殊符号步骤4：点击笑脸，然后我们选择“特殊符号”得到如下的窗口，在窗口中选择我们需要的特殊符号。然后点击确定。 输入特殊符号步骤5：我们就在要输入符号的地方吗输入了特殊的符号。
Excel中进行制作饼图的操作方法
有人说，Excel功能虽强大，但是80%的使用者只用了它的20%功能，其余的80%的功能，只有20%的人在使用。此话不假，Excel中的VBA编程以及许多函数的使用，大多数使用者比较陌生，有的甚至从没接触过，即使图表功能，很多使用者接触也不多。但是有些问题，不是我们的工作不需要，而是我们对Excel的使用方法需要进一步熟悉，比如复合饼图。今天，小编就教大家在Excel中进行制作饼图的操作方法。 Excel中进行制作饼图的操作步骤如下： 有某城市调查队的低收入家庭基本结构统计表如图。 为作图，可列出辅助图表： 作出一般饼图如下： 这样的饼图，并不能体现“老弱病残幼人员”的细分，所以，此类图表适合使用复合饼图。 复合饼图是将饼图分成两部分，把占总量较少的部分或指定的部分单独拿出来做成一个小饼以便查看得更清楚。复合饼图的作法与一般饼图的作法基本相似，只是在一些细节上有所不同，所以凡与饼图相同的调整方法，就不再赘述。 上述例子就是要将指定的“老弱病残幼人员”部分单独表达得更清楚。利用上述辅助表格，该表格将要指定的几行内容放在表格最后。 1.选取表格所在单元，单击工具栏上的【图表按钮】，打开【图表向导-4步骤之1-图表类型】对话框，在“标准类型”列表框中选“饼图”选项，在子图表类型列表中选第一行最后的“复合饼图”或第二行最后的“复合条饼图”，在这里我们选“复合饼图”。 2.单击【下一步】按照图表向导的步骤进行，直到完成复合饼图的基本制作。如下图：
最新发布
数据透视表IV——任务向导用户界面，或“为我们
PivotTables part 4: Task-oriented UI, or “improvements the Ribbon affords us”, and some bonus talk about dialogs数据透视表4：任务向导用户界面，或“为我们提供改进的 Ribbon”，及对话框的一些其它优点。As I mentioned a few posts back, one of the key goals for PivotTables in Excel 12 was to use the Ribbon and new dialogs to expose PivotTables’ capabilities to a much broader range of users. Today I want to take a closer look at the new user interface – especially the ribbon – and how we have tried to make commonly-used features and functions much more visible and available with very few clicks of the mouse. I am also going to briefly cover some changes and additions to the PivotTable Options and Field Settings dialogs.正如我在前面的文章中提到的，Excel12中数据透视表的一个主要目标就是通过使用RIBBON和新的对话框来提高性能，使更多的用户易于使用它。今天我想更进一步介绍新的用户界面——特别是RIBBON——以及我们如何使常用的特性和功能更容易被发现和利用，并只需点几下鼠标就能实现。首先，我简要的介绍一下数据透视表选项和字段设置对话框的一些改变和新增功能。PivotTable tab I – the Options tabWhen a PivotTable is active (meaning the active cell is inside a PivotTable), you will see two extra PivotTable tabs in the ribbon: Options and Styles. Here is what the PivotTable Options tab looks like in the beta build (note, there is a fair bit not done in this tab, so it is definitely not what you will see in the next beta or when we release Office 12). 数据透视表标签I：选项标签当数据透视表激活时（意味着活动单元格在数据透视表中），在RIBBON上你会看到两个额外的数据透视表标签：选项和样式。如下图是透视表选项标签在测试版中所看到的样子（注：其中有相当一部分未完成，所以你在下一个测试版或最终版的Office12中看到的会不一样）。This tab is designed to hold all the commands you would commonly use when working with a PivotTable. In addition to giving us more room to expose all the functionality that already exists in Excel PivotTables, it also provides space to advertise new features we have added in Excel 12. In our testing, we have found that it allows all users (both beginning and power users) to take advantage of a wider range of features than in previous versions of Excel. We are pretty excited to see this sort of improvement.
IX 对SQL服务器分析服务的更强大支持（二）
All that said, let’s return to Excel 12, and take a look at what the PivotTable Field List looks like when connected to an Analysis Services 2005 model.我们说了那么多，让我们回到Excel 12，看看当连接到Analysis Services 2005模型时数据透视表的字段清单长什么样。Measure groupsWhen connected to Analysis Services, a PivotTable exposes three types of fields – “measures”, or the numbers (like “sales” and “profit”) that appear on your PivotTables, as well as “KPIs” and “dimensions” (both discussed below). Measures can be grouped together in Analysis Services (by the person that designs the model) into something called “measure groups”. In the Excel 12 field list, each measure group has a “sigma” icon to communicate to the user that the fields in the group are numerical and that they belong in the Values area of the PivotTable. Measure groups essentially represent different sets of business metrics available for analysis; typically a measure group contains related measures from the same business application. In the image below, the Exchange Rates measure group folder is open and there are two measures listed which can be added to the PivotTable – Average Rate and End of Day Rate.衡量组合当连接到Analysis Services时，数据透视表会显示三类字段——“衡量”，或者数字（如“销售”和“利润”），还有“KPIs”和“维度”（下面都会讨论）。衡量可以在Analysis Services里组合（由设计该模型的人）为名叫“衡量组合”的东西。在Excel 12字段清单里，每个衡量都有一个“西格马”图标，告诉用户该组合里的字段是数字型的，并且它们都属于数据透视表中的数值区域。衡量组合本质上代表不同的分析可用的业务方法（译者：作者经常提到Business Metrics，不明所以，暂且译为业务方法）；衡量组合通常包含来自相同业务软件的相关衡量。在下面的图像上，Exchange Rates衡量组合是开启的，有两个衡量，它们可以添加到数据透视表——Average Rate和End of Day Rate。Key Performance Indicators (KPIs)Below the measure group folders are is a KPI folder (assuming KPIs have been defined in an Analysis Services model). This folder contains Key Performance Indicators defined on the Analysis Services server. (Key Performance Indicators are a big subject unto themselves – for the sake of this article, suffice to say that they track key business metrics and that they are defined in Analysis Services). The different components of a KPI (Value, Goal, Status and Trend) can be added to the Values area of the PivotTable so you can track the latest values of your key business metrics. Here is a screenshot of the KPIs folder … in the image, the Product Gross Margins KPI is open and all you have to do to add the Value, Goal, Status or Trend of the KPI to the PivotTable is to check the checkbox next to it.关键性能指标（KPIs）
数据透视表IV——任务向导用户界面
Field Settings and PivotTable Options dialogsThe goal of the ribbon is to provide all the functionality needed for most users. More advanced options are available in the Field Settings dialog and the PivotTable Options dialog. While both of these dialogs existed in previous versions of Excel, we have updated them to achieve two things. First, to group the options more intuitively together, and second, to include settings for new features as well as some features previously only available through the object model. We have used tabs to group options logically. Let’s take a brief look. (Note, this is more detailed than the rest of the post, but since I am feeling productive today, and since many of the changes we made were in direct response to places where we had received a bunch of customer feedback, I decided to walk through the dialogs and highlight some of the changes we made.)字段设置和数据透视表选项对话框RIBBON的目的是为绝大多数用户提供所有的功能。更高级的选项可利用在字段设置对话框和数据透视表选项对话框。这两个对话框在以前的Excel版本中就存在了，现在我们已经将其升级以实现两个功能：第一是将各种选项更直观地归类，第二是包含新功能的设置和以前只能通过对象模型才能实现的那些功能的设置。我们已经使用标签将选项逻辑地分组。让我们大致来看一下。（注：这比本文其余部分更详细，但由于我觉得今天比较能写，而且我们所做的许多更改是对我们收到的一连串用户反馈的直接回应，所以我决定详细讲一下这些对话框并重点说明我们所做的一些改变。）Here is a screenshot of the first tab of the new Field Settings dialog for a field on rows or columns – this allows users to set a number of options on a field-by-field basis.这是一个行或列字段新的字段设置对话框的第一个标签的截图——在这里允许用户设置各个字段的基本选项。On this tab there is a checkbox for “Display items from the next field in the same column”. With this you can control on an individual field basis whether to display items of that field in the new compact form or not (see my previous post for an explanation of the three forms – Compact Form, Tabular Form and Outline Form). And here is the second tab of the Field Settings dialog.在此标签中有一个复选框，在同一列中显示下一个字段的项目。这里你可以控制每一个单独字段的基本要素，即是否在新的紧凑形式下显示该字段项目（详见我以前的文章 previous post，那里讲明了三种形式：紧凑形式、平板形式、大纲形式）。下面是字段设置对话框的第二个标签。The new “Include New Items in Filter” checkbox allows you to control whether new items appearing in the source data will automatically show up in the PivotTable when the field is manually filtered ("manually filtered" meaning not all items are checked in the filter UI).
最终界面以及更多的行与列
最终界面我曾经在以前的文章中不断的展示给大家Excel 2007 Beta1的界面截图，并且注明“这不是最终的界面”。今天，这个界面已经向最终界面跨出一大步。在今天早上的德国CeBIT中，微软公司向公众展示了即将成为最终界面的Office 2007，以下是Excel的截图：下面是可用的另一个风格：你所看到的Beta 1的界面发生了许多改变，其中包括更具美感的颜色和背景——以及其他更多的细节。比如，“快捷工具栏”放到了标题栏，群组（原名“chunk”）标题放到了上面，新加了一个大大的Office按钮，一个包含视图和窗口管理的“视图”标签，等等。Jensen Harris’ UI blog 中有其他Office组件的截图，以及相关的说明。“Beta1” 的测试者们需要升级到以上截图的出处—— “Beta1 technical refresh”，后者马上就会向大家提供。我们仍将继续对界面做出一些调整，比如改换一个图标，移动一个按钮，直到最终版本发布。但整体上来讲，现在的版本已经非常接近最终版本了。更多的行与列许多人问我在最终版中是否会增加单个工作表的容量，我想我有必要再次声明一下：“是的，我们已经增加了工作表的容量。Excel 2007的工作表将有1,048,576 行，16,384 列，那是Excel 2003工作表行数的1,500% 和列数的 6,300%。现在最后一个列标将从原来的IV变成XFD”。
数据透视表III—更多可选择、更简洁精美的样式
PivotTables part 3: More clickable, more compact, and nicely styled数据透视表3：更多可选择、更简洁精美的样式Today, I would like to cover some of the improvements we made in Excel 12 to make PivotTables easier to read and explore.今天，我介绍一下Excel 12的一些改进，这些改进使得数据透视表更易于阅读与理解。Expand CollapseOne of the nice exploration features of PivotTables is the ability to expand and collapse items in order to view values at different levels of detail. In Excel 12, we have added expand/collapse indicators to the PivotTable to make it easy to discover when there are more details to explore (and to make it obvious that this feature even exists!). The expand indicator is a “+” and the collapse indicator is a “-”.展开折叠数据透视表中一个值得探索的特征就是展开和折叠项目的功能，使用户能够按照不同的明细等级察看数据。在Excel 12 里，我们给数据透视表增加了展开/折叠指示器，让用户很容易就能看出是否含有隐藏的明细数据。这个展开指示器是一个“+”，折叠指示器是一个“-”。Let’s look at an example. In the PivotTable below, I have added three fields to the row area and the sales amount field to the values area. Currently only items of the first field, year, are showing. To display the details below 2001, all I have to do is to click the expand indicator:
VII －条件格式与数据透视表（二）
Let me briefly explain the three options. (Note, we are still working on the wording of the last option. It’s also worth noting that these options are also exposed in the conditional formatting creation and management UI, so you don’t have to rely on the on-object UI.)我简单解释一下这三个选项。（注意，我们还在考虑最后一个选项的措辞。同样，值得注意的是这些选项同样会出现在条件格式创建和管理的用户界面上，因此你不必依赖于该对象上的用户界面。）• Selected cells – this will leave the conditional formatting applied to just the selected cells• All “Sum of Sales Amount” cells – this will apply the conditional formatting to all Sum of Sales Amount cells in the PivotTable, regardless of level, and including subtotals. This will be useful in cases for measures that aren’t sums – if you have an “Average Retention” measure, for instance, all values (including subtotals and grandtotals) will be between 0 and 1 and can be sensibly formatted using a single rule.• All “Sum of Sales Amount” cells with the same fields – this will apply conditional formatting to all Sum of Sales Amount cells at this level in the PivotTable, which excludes subtotals. I suspect this will be the most commonly used.• 所选单元格——仅所选单元格会保留条件格式• 所有“Sum of Sales Amount”单元格——这将应用条件格式到数据透视表里所有的Sum of Sales Amount单元格，不管其层次，并包括小计在内。当这些衡量标准没有加和时，这会很有用——例如，如果你有一个“Average Retention”衡量的话，那么所有的数值（包括小计和总计）都会在0和1之间，并且可以敏感地使用单一规则设置格式。• 所有“Sum of Sales Amount”中具有相同字段的单元格——这将应用条件格式到数据透视表该层次中所有的Sum of Sales Amount单元格上，不包括小计。我觉得这将会最常用的使用In this case, I want to apply the rule to all cells displaying sales for individual bike models and individual years. To do this, I’ll pick: All “Sum of Sales Amount” cells with the same fields. After I have made this selection, the PivotTable will now show the conditional formatting in all cells showing sales for an individual product category and an individual year.
图形－Shapes
One More Great-Looking Documents Post – Shapes又一篇关于“精美文档”的文章——图形A few weeks ago I posted a series of articles about great-looking documents. I have had a few questions about shapes since I wrote those posts, so I thought I would write a quick post on changes to shapes in Excel 2007 (and Office 2007 really – anything I write here applies to all the apps).几周前我发布了关于“精美文档”的一系列文章，在那之后，我收到了一些有关图形的提问，所以我想我要尽快撰写有关于Excel 2007里图形的变化(包括Office 2007，所有我在这里写的都能适用于所有的Office应用)的文章。Much the same way that charts were improved (new great-looking visuals, results-oriented ribbon UI), shapes have been improved as well. Here is a summary of the changes, which fall into a number of categories.我们已经知道，2007版本里面的图表在许多方面进行了改进(如新增漂亮的可视化效果，效果生成向导的用户界面)，同样的，图形方面也进行了与此相似的改进。下面是这些变化的总结，可以分为几种类型。1. New graphics effects and options – like charts, shapes will look a lot better in Excel 2007 … in fact, charts are built using shapes in Excel 2007, so the improvements in shapes and charts in this area are identical. Specifically,· Better shadows· Better gradients
非常酷的状态栏和精美图表
Quick detour – cool things on the status bar and great-looking charts快速入门-非常酷的状态栏和精美图表Today I decided to take a quick break from Excel Services totalk about a few small but useful changes that have been made to the status bar andshow off a few chartsFirst, the status barZoom control – we have added a slider that allows the user to adjust the “zoom” of the document without needing to pop up any windows. When you slide the control, the document resizes as you slide, so you can adjust to just the “zoom” you want before you let go of the slider. You can also click on the + and – buttons to increment or decrement “zoom” by 10% per click. Finally, for those of you that like using the zoom dialog, you can just click on the 100% (which is a button) and it will launch the Zoom dialog.我今天要谈Excel2007中质的突破：1. 小巧变化的状态栏
X 服务器格式，翻译和成员属性
PivotTables X: Server formatting, translations, member properties数据透视表 X：服务器格式，翻译，成员属性In this post I’ll walk you through three Analysis Services features that we now support in Excel PivotTables – server formatting, translations, and member properties. One thing to keep in mind as you read is that since all these are defined in Analysis Services (i.e. on a server), every PivotTable created that pulls data from Analysis Services will get the benefit of these features without the PivotTable author or user needing to do anything.在本文里，我将带领你浏览我们现在在Excel 数据透视表里支持的Analysis Services功能——服务器格式，翻译和成员属性。你在阅读的时候请牢记一件事，因此这些都是在Analysis Services（例如在服务器上）上定义的，从Analysis Services获得的数据而创建的每个数据透视表将会从中获益而不需要数据透视表作者或者用户去做任何事情。Server formattingWhen designing a model in Analysis Services, formatting can be associated with values. Excel PivotTables will display this formatting by default (you can control it in the connection properties dialog for the connection being used by the PivotTable, so if you want to turn off the formatting, you can).服务器格式在Analysis Services设计模型时，设计数据的同时也可以设置格式。Excel数据透视表将会默认地显示这些格式（你可以在连接属性对话框里控制它，因此如果你想要关闭格式的话，你可以做到）Here is an example of a PivotTable displaying number formatting as defined on the server – in this case dollars with two decimals. As you add fields to the Values area of the PivotTable, the formatting is done automatically.
IX 对SQL服务器分析服务的更强大支持（一）
PivotTables 9: Great support for SQL Server Analysis Services数据透视表 9：SQL服务器分析服务的强大支持Today, I’ll start a series of articles on the improvements we’ve made to PivotTables connected to OLAP (OnLine Analytical Processing) data sources, specifically Microsoft SQL Server Analysis Services models (in addition to its relational database product, SQL Server includes a feature named Analysis Services which provides business intelligence and data mining capabilities). Excel has worked with SQL Server Analysis Services for several versions now, but we have put a lot of time and effort into Excel 12 in order to make it a great front end to SQL Server Analysis Services, especially Microsoft SQL Server 2005 Analysis Services (Microsoft SQL Server 2005 Analysis Services was recently released as part of Microsoft SQL Server 2005 and introduced many new, powerful features for analyzing data … for more information on Analysis Services, please take a look here and here).今天，我将开始我们在数据透视表和OLAP（OnLine Analytical Processing联机分析处理）数据源，特别是Microsoft SQL Server Analysis Services模式（除了它有关的数据库产品之外，SQL Server还包括了一个名叫Analysis Services的功能，它提供业务情报和数据发掘能力）连接方面所作改进的系列文章。Excel已经有好几个版本能和SQL Server Analysis Services协作了，但是，为了使其作为SQL Server Analysis Services的前端能更好的工作，我们仍花了很多时间和精力在Excel 12上，特别是与Microsoft SQL Server 2005 Analysis Services （Microsoft SQL Server 2005 Analysis Services在前不久，作为Microsoft SQL Server 2005的一部分发布了，并且增加了很多新的，强大的数据分析功能……有关Analysis Services的更多详细信息，请看看这里）的协作上。Before I launch into discussing how Excel 12 works with SQL Server Analysis Services, I wanted to summarize what I see as several key benefits to using Analysis Services as a tool for working with business data.在我进入讨论Excel 12如何和SQL Server Analysis Services协作之前，我想总结一下我所认为的使用Analysis Services作为分析业务数据的注意好处。• Friendliness. Business data is typically stored in relational databases optimized for data input or storage and not analysis of that data. Names of columns etc. are typically not intuitive to end users, there are no clear relationships between fields, etc. Analysis Services provides a user-friendly model where you can provide understandable business names, specify relationships between fields (Product Category – Product Subcategory – Product) so that it is possible for business users to design their own reports without help from IT. • 友好：业务数据通常储存于相关的数据库里，这些数据库适于数据输入或者存储，但是不适于数据分析。诸如列名之类的元素对于终端用户不是很直观，字段之间没有清楚的关系，等等。Analysis Services提供了一个用户友好的模型，你可以提供易于理解的业务名称，明确字段（Product Category – Product Subcategory – Product）之间的关系，这样一来，业务用户就可能设置他们自己的报告，不必求助于IT部门了。• Personalization. Analysis Services offers tools for personalizing individual users’ reporting experience by only showing them the data that they care about and have permissions to see; in addition, Analysis Services can translate data into users’ preferred languages.