plsql数据表重命名_重命名数据透视表明细表

最新推荐文章于 2022-03-24 18:44:54 发布

culiao2169

最新推荐文章于 2022-03-24 18:44:54 发布

阅读量1k

点赞数

文章标签： java python linux excel 大数据

原文链接：https://contexturesblog.com/archives/2018/09/13/rename-pivot-table-drilldown-sheets/

版权

本文介绍了如何使用Excel宏管理数据透视表明细表，包括重命名、删除和自定义设置。通过宏，可以方便地查找和删除明细表，同时提供设置选项，如工作表前缀、颜色和位置，以及在关闭工作簿时保存或删除明细表的功能。

摘要由CSDN通过智能技术生成

plsql数据表重命名

When you double-click a pivot table value cell, Excel adds a new sheet to your workbook, with the details for that summary value. To make it easy to find those sheets, and delete them later, use the latest version of my macros to rename Pivot Table DrillDown sheets.

当您双击数据透视表值单元格时，Excel将向您的工作簿添加一个新工作表，其中包含该摘要值的详细信息。为了便于查找这些工作表并在以后将其删除，请使用最新版本的宏来重命名数据透视表明细表。

显示详细信息命令 (Show Details Command)

Another way to create a DrillDown sheet is to right-click a pivot table value cell, and click the Show Detail commands.

创建DrillDown工作表的另一种方法是右键单击数据透视表值单元格，然后单击“显示详细信息”命令。

打开或关闭 (Turn On or Off)

There is a setting in PivotTable options, on the Data tab, where you can turn off this feature, if you don't want people to use it.

如果您不希望人们使用此功能，则在“数据”选项卡上的“数据透视表”选项中有一个设置，您可以在其中关闭此功能。

In this screen shot, I've removed the check mark, so a message will appear if anyone double-clicks a value cell.

在此屏幕快照中，我删除了复选标记，因此如果有人双击值单元格，则会出现一条消息。

显示详细信息或下钻 (Show Details or Drilldown)

If you record a macro while you change that setting, you'll see that Excel calls the feature Drilldown in the background.

如果在更改该设置的同时录制宏，则会看到Excel在后台调用功能Drilldown。

That's why I call these the DrillDown macros. And I capitalize the second D, just so it matches my initials!

这就是为什么我将这些称为DrillDown宏。我将第二个D大写，恰好与我的名字首字母匹配！

向下钻取宏–旧 (DrillDown Macros – Old)

Long ago, I posted a set of DrillDown macros, to help manage the details sheets. Instead of leaving Excel's default names for the sheets, the macro adds a prefix, "XShow_" at the start of the name.

很久以前，我发布了一组DrillDown宏，以帮助管理详细信息表。宏没有在工作表中保留Excel的默认名称，而是在名称的开头添加了前缀“ XShow_”。

That makes it easy to find the sheets, and there is another macro that deletes all the sheets that have a name with that prefix.

这样可以很容易地找到工作表，并且还有另一个宏可以删除所有具有该前缀名称的工作表。

更新的宏 (Updated Macros)

Last week, Rich E. asked a few questions about the old DrillDown macros, and that inspired me to take another look at them.

上周，Rich E.问了一些有关旧的DrillDown宏的问题，这启发了我重新审视它们。

For the macro that names each sheet, Rich asked if the prefix could be different, and if the tab colour could be set.
对于为每张工作表命名的宏，Rich询问前缀是否可以不同以及是否可以设置选项卡颜色。
For the Delete sheets macro, Rich asked about saving some or all of the sheets, when closing the workbook.
对于“删除工作表”宏，Rich询问关闭工作簿时是否保存部分或全部工作表。

Those were great questions, and I created a new download file, with custom settings for the DrillDown macros.

这些是个好问题，我创建了一个新的下载文件，并为DrillDown宏设置了自定义设置。

向下钻取宏设置 (DrillDown Macro Settings)

Here are the custom setting cells on the sample file's Admin sheet

这是示例文件的管理表上的自定义设置单元格

When a new drilldown sheet is created, the macro changes its name:

创建新的明细表后，宏将更改其名称：

The new name starts with the prefix from cell C4.
新名称以单元格C4中的前缀开头。
Next is the number from C12, formatted as 3-digits.
接下来是C12中的数字，格式为3位数字。
It ends with the name of the pivot table that was double-clicked.
它以双击的数据透视表的名称结尾。

NOTE: The first 31 characters of that string are used as the new sheet name.

注意：该字符串的前31个字符用作新的工作表名称。

其他选择 (Other Options)

The Admin sheet has a tab colour cell too, and the fill colour from that is used on the new sheet's tab.

管理员工作表中也有一个标签颜色单元格，并且新工作表标签上使用的填充颜色。

You can also choose a location for the new sheet – make it the first or last sheet, or leave it where it is (Do not move)

您还可以为新工作表选择一个位置-将其设为第一张或最后一张工作表，或将其保留在原位置(请勿移动)

The current setting is "Last", so the new sheet is the last tab in this workbook.

当前设置为“ Last”，因此新工作表是此工作簿中的最后一个选项卡。

删除明细表的宏 (Macro to Delete the DrillDown Sheets)

There is a Workbook Close procedure in the sample file, and it will delete All, Some or None of the DrillDown sheets.

示例文件中有一个“工作簿关闭”过程，它将删除所有，部分或无“向下钻取”工作表。

ALL – Each sheet with the designated prefix is deleted, and the Next Number is reset to 1
ALL –删除具有指定前缀的每个工作表，并将“下一个号码”重置为1
None – The drilldown sheets are left as is, with nothing deleted.
无 -向下钻取表保留不变，不删除任何内容。
Some – The macro shows a message for each sheet that has the designated prefix, and asks if you want to delete it. The Next Number is not reset.
某些 –宏会为每个具有指定前缀的工作表显示一条消息，并询问您是否要删除它。下一个号码不会重置。

追溯表信息 (DrillDown Sheet Info)

Another feature in the new DrillDown macro is the information added to the right of the detail table.

新的DrillDown宏的另一个功能是在明细表右侧添加的信息。

It shows the date that the sheet was created, the pivot table name and the pivot table's sheet name. The sheet name is hyperlinked, so you can get back there quickly.

它显示工作表的创建日期，数据透视表名称和数据透视表的工作表名称。工作表名称是超链接的，因此您可以快速回到那里。