excel 宏隐藏分页表_隐藏没有宏的表详细信息-CSDN博客

excel 宏隐藏分页表

Last week, I was preparing reports for a client, and needed to find a quick and easy way to show or hide details in a long list. After a bit of thought, and experimenting, I found a way to do this – without macros.

上周，我正在为客户准备报告，需要找到一种快速简便的方法来显示或隐藏详细信息。经过一番思考和尝试，我找到了一种无需宏的方法。

You could use this technique on a table or pivot table, to make it easier to explore the data. You could also use this technique on an Excel dashboard, where real estate is in high demand!

您可以在表或数据透视表上使用此技术，以便更轻松地浏览数据。您还可以在对房地产有很高需求的Excel仪表板上使用此技术！

真正的宽桌子 (The Really Wide Table)

My client’s list had sales information, in a table with about 50 columns. He wanted to filter by product name, region or city, without seeing all the address details. Then, when the list was filtered down to just a few records, he wanted the option to open things up.

我的客户列表中有一个包含约50列的表格中的销售信息。他想按产品名称，地区或城市进行过滤，而无需查看所有地址详细信息。然后，当列表被筛选成仅几条记录时，他希望使用该选项来打开内容。

In the screen shot below, I created a smaller example table, with some fake data.

在下面的屏幕快照中，我创建了一个较小的示例表，其中包含一些虚假数据。

隐藏细节 (Hiding the Details)

My first thought was to create a couple of macros, to hide or show some columns. Buttons at the top of the list would run those macros. Then I remembered Excel’s Grouping feature, and decided to try that.

我的第一个想法是创建几个宏，以隐藏或显示一些列。列表顶部的按钮将运行这些宏。然后，我想起了Excel的分组功能，并决定尝试一下。

Usually we use grouping to show or hide details in a table with subtotal rows and columns. But grouping can also work nicely in other situations, like this wide table.

通常，我们使用分组来显示或隐藏具有小计行和列的表中的详细信息。但是分组在其他情况下也可以很好地工作，例如这张宽桌子。

To hide the street and postal code details:

隐藏街道和邮政编码详细信息：

Select the Street and Postal Code columns
选择街道和邮政编码列
On the Ribbon’s Data tab, click Group
在功能区的“数据”选项卡上，单击“组”。

A grey bar will appear above the Excel sheet, with a line and button over the grouped columns.

将在Excel工作表上方显示一个灰色条，并在已分组的列上显示一条线和一个按钮。

Click the – button to hide the columns
单击–按钮隐藏列
When it changes to a + button, click that to show the columns
当它变为+按钮时，单击该按钮以显示列

分组更多列 (Group More Columns)

To make the table narrower, I also grouped columns with product details, and date details

为了缩小表格范围，我还将产品详细信息和日期详细信息按列分组

With those +/- buttons, you can show or hide the individual groups. At the far left, you’ll see buttons with numbers 1 and 2. You can click those, to show or hide all the groups.

使用这些+/-按钮，您可以显示或隐藏各个组。在最左侧，您将看到带有数字1和2的按钮。您可以单击它们以显示或隐藏所有组。

In the screen shot below, I clicked the “1” button, and that hid all the grouped columns. To see all the detail, click the “2” button.

在下面的屏幕截图中，我单击了“ 1”按钮，该按钮隐藏了所有分组的列。要查看所有详细信息，请单击“ 2”按钮。

鸽友分组 (Fancier Grouping)

AlexJ shared his grouping code on this blog a while ago (yikes, that was 5 years ago!), if you’re looking for something fancier. His technique uses named ranges and macros, and you can download his sample file to see how it works.

如果您正在寻找更奇特的产品，AlexJ不久前在此博客上分享了他的分组代码 (可能是5年前！)。他的技术使用命名范围和宏，您可以下载他的样本文件以查看其工作原理。

To download the file, go to AlexJ’s page on my Contextures website, and in the VBA section, look for VB0001 – Hide Rows with Outlining

要下载文件，请转到我的Contextures网站上的AlexJ页面，然后在VBA部分中查找VB0001 –隐藏带轮廓的行

Excel仪表板课程推荐 (Excel Dashboard Course Recommendation)

As I mentioned earlier, you could use this technique to show or hide columns in a dashboard. Let people open up the details when they need them, and hide them the rest of the time.

如前所述，您可以使用此技术来显示或隐藏仪表板中的列。让人们在需要时打开细节，并在其余时间隐藏它们。

If you’d like to learn all about dashboards, Mynda Treacy from My Online training Hub is opening her Excel Dashboard Course, for a limited time.

如果您想了解所有有关仪表板的知识，那么我的在线培训中心的Mynda Treacy将在限定时间内开放她的Excel仪表板课程。

The course is video based, delivered online and is available 24/7. You'll receive comprehensive workbooks and sample dashboards to keep, and there’s even an option to download the videos.

该课程以视频为基础，在线提供，并且全天24/7可用。您会收到综合的工作簿和示例仪表板，以备保留，甚至还可以选择下载视频。

I've been through this course, and highly recommend it. The previous classes were very successful, and you can read the glowing reviews from the students, who loved all the techniques that they learned in the course, and are using them to impress their colleagues.

我已经完成了这门课程，并强烈推荐它 。以前的课程非常成功，您可以阅读学生的热烈评论，他们喜欢他们在课程中学到的所有技术，并用它们给同事留下深刻的印象。

Click here to find out the details, read the student comments, and watch the 'behind the scenes' video that shows you what you'll receive as a member.

单击此处了解详细信息，阅读学生的评论，并观看“幕后花絮”视频，该视频向您展示您将获得的会员资格。