Excel数据透视表空白类别无法正确显示 [英] Excel Pivot Table Blank Category Not Displaying Properly

问题描述

我有两个数据透视表:

数据透视表的数据源是Access导出到Excel的过程，它涉及将数据从导出文件中手动复制并粘贴到报表文件数据表中，该报告文件数据将数据馈送到数据透视表.

Pivot table one's data source is an Access export to Excel which involves manually copying and pasting the data from the export file into the report file data table which feeds data to the pivot table.

数据透视表二的数据源是一个工作簿连接，它与用于创建旧的导出文件的查询相同，该查询将日期馈送到新报表文件的数据表中，然后由数据透视表使用.

Pivot table two's data source is a workbook connection, to the same query that is used to create the old export file, which feeds date to the new report file's data table which is then used by the pivot table.

我的问题是，从工作簿连接派生的数据创建的数据透视表将空白显示为实际的空白"，而不是原始表中的(空白)".

My problem is that the pivot table that is created from data that is derived from the workbook connection displays blanks as an actual blank " ", not "(blank)" which is found in the original table.

屏幕截图1-正常空白

截屏2-奇怪的空白

最奇怪的部分是，当我转到Excel中的源表时，数据连接实际上将数据放置在工作簿中，当我要过滤表中的数据时，空格显示为(空白)".

The strangest part is that when I go to the source table in Excel, where the data connection actually places the data in the workbook, blanks show up as "(blank)" when I go to filter the data in the table.

截屏3-奇怪的空白源表

我在网上找不到类似的东西.任何帮助将不胜感激！

I could not find anything like this online. Any help will be greatly appreciated!

推荐答案

Excel倾向于添加不可见的格式字符，这些字符(通常)在使用命令导入时通常不会被导入，但是会被复制粘贴.这些都需要删除.

Excel tends to add invisible formatting characters, that (usually) don't get imported when importing with a command, but do get copy-pasted. These need to be removed.

将以下功能粘贴到模块中:

Paste the following function into a module:

Public Function RemoveNonASCII(str As String) As String
    Dim i As Integer
    For i = 1 To Len(str)
        'Append the question marks
        If Mid(str, i, 1) = "?" Then
            RemoveNonASCII = RemoveNonASCII & "?"
        End If
        'Append anything that isn't a questionmark
        If Asc(Mid(str, i, 1)) <> 63 Then
            RemoveNonASCII = RemoveNonASCII & Chr(Asc(Mid(str, i, 1)))
        End If
    Next i
End Function

然后，执行以下查询以删除Access中所有多余的空格和格式化字符:

Then, execute the following query to remove all excess spaces and formatting characters in Access:

UPDATE MyTable SET MyTable.MyColumn = Trim(RemoveNonASCII(MyTable.Mycolumn))

这篇关于Excel数据透视表空白类别无法正确显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！