Excel数据透视表空白类别未正确显示

I have two pivot tables:

我有两个数据透视表：

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.

数据透视表的数据源是Access导出到Excel，其涉及手动将数据从导出文件复制并粘贴到报告文件数据表中，该数据表将数据提供给数据透视表。

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.

数据透视表2的数据源是一个工作簿连接，用于创建旧导出文件的同一查询，该文件将日期提供给新报表文件的数据表，然后由数据透视表使用。

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.

我的问题是，从工作簿连接派生的数据创建的数据透视表显示空白作为实际空白“”，而不是原始表中的“（空白）”。

Screenshot 1 - Normal Blank

截图1 - 正常空白

Screenshot 2 - Weird Blank

截图2 - 奇怪的空白

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.

最奇怪的部分是，当我转到Excel中的源表时，数据连接实际上将数据放在工作簿中，当我去过表中的数据时，空白显示为“（空白）”。

Screenshot 3 - Weird Blank Source Table

截图3 - 奇怪的空白源表

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

我在网上找不到这样的东西。任何帮助将不胜感激！

1 个解决方案

#1

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.

Excel倾向于添加不可见的格式化字符，这些字符（通常）在使用命令导入时不会导入，但会进行复制粘贴。这些都需要删除。

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

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

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

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

#1