☰

Excel 表格标题总是转换为文本吗？

tag-icon • tag-icon microsoft-excel date excel-auto-fill
Excel 表格标题总是转换为文本吗？

我正在尝试让包含日期的表头正确自动填充，以便它们在列之间增加一天。

不幸的是，Excel 似乎将表头视为文本无论实际的单元格格式如何，尝试填充日期系列只会粗略地增加年份。

格式化示例Date：

在此处输入图片描述

绿色行：

  • 这是不是表格的一部分
  • 所有数据都以日期格式保存
  • 填充系列成功因为 Excel 理解这些是日期值

红色行：

  • 这是表头的一部分（这是唯一的区别）
  • 所有数据似乎都转换为文本，即使以日期格式正确输入，并且单元格已格式化为使用日期格式
  • 填充系列失败因为 Excel 无法理解这些是日期值

转换回General格式的示例：

在此处输入图片描述

  • 我们现在明白了为什么绿色行的日期填充成功，而红色行的日期填充失败
  • 尽管输入的是日期，且单元格的日期格式与绿色行相同，但红色行表头“日期”并不以日期形式存在，而是以文本形式存在

问题：

Excel 将所有值转换为文本（无论格式如何）作为表头，这正常吗？

有什么方法可以覆盖这种行为吗？

更新：

为了回答为什么我首先在​​表头中使用日期的问题，我有一个动态折线图，可以按天跟踪某些值。

随着新数据输入到表中，表格会水平扩展（正常表格行为），并且折线图也会使用新数据自动绘制新线。

工作表的一部分如下所示：

在此处输入图片描述

这在屏幕截图中看起来可能还不错，但在每个月末，日期会填满每个月的无限天数；它会一直数到 40 天甚至更久！

由于日期作为文本卡在标题中，我必须每个月手动更正它。

答案1

我理解你的问题的意思是，你已经有 X 个以日期格式开头的列，dddd, mmmm d, yyyy并且希望在它们的右边添加列，这样表格的大小就可以增加，新列就可以轻松地给出下一个日期，并按日计数。

首先，我要重申一下，无论如何，Excel 表格的列标题始终是文本。在继续操作之前，Excel 甚至不会在不向您展示这一事实的情况下创建表格。

因此，这里真正的好答案是 VBA。我没有资格编写您需要的内容，而且您可能不允许在电子表格中使用宏。但这个想法是有一个宏，它接受一个数字来表示要添加多少列（有几种方法可以做到这一点，从简单地将值放在某个单元格中供它读取到在它显示的对话框中放置结束日期并让它计算出要添加多少列，等等），查找并将表格标题中的最后一列转换为它可以使用的日期，创建应该放入添加的列标题中的值，适当地格式化它们，最后添加请求的列并将刚刚格式化的日期的文本版本放入适当的标题单元格中。

如果您可以忍受手动解决方案的想法，则可以使用如下公式创建命名范围：

=LET(Source,  INDIRECT(ADDRESS(1,COLUMN()-1)),
     Date,  DATEVALUE(RIGHT(Source, LEN(Source) - FIND(",",Source) - 1)),

 TEXT(Date+1,"dddd, mmmm d, yyyy")
)

然后选择需要新标题的单元格。选择最后一个单元格，然后键入=和您创建的命名范围的名称。（可能是HeaderLabel。）按 Enter。可能会出现错误，但没关系。复制该单元格并从它开始突出显示，向左向表格方向移动，直到选择了所有新的列标题单元格（我这样称呼它们，尽管它们还不是表格的一部分。）然后粘贴复制的单元格。

单元格粘贴后表格会扩展。错误仍然存​​在#VALUE!。一切似乎都失败了……但事实并非如此。实际上，粘贴时发生了几件事，您需要按 撤消级别Ctrl-Z。当您这样做时，单元格/列会从表格中退出，现在具有根据需要格式化的漂亮日期。

现在，您只需将这些（不是您最初遇到的那些不幸的错误标题）作为表格的一部分。通过抓住表格右下角的小标记并将其拖到右侧以包含这些列及其新标题来执行此操作。标题将按预期转换为文本。

如果由于表格的大小而无法手动扩展表格，您可以选择Table Design功能区菜单上的选项卡，查看其最左侧区域并查看Table Name。Resize Table选择Resize Table后会弹出一个对话框（某处），其中显示表格的当前范围，您只需将该范围右侧的列更改为所需的新最右侧列即可。

无法提供完全非手动的方法，因为 VBA 当然不够好，而且 Excel 根本不允许标题单元格包含公式。

您可能会想，“嗯...如果我按照他的做法做会怎么样...转到标题单元格并输入=HeaderLabel，按 Enter，然后看到不满意的结果，但然后按Ctrl-Z撤消一层看似一个操作但 Excel 将其视为几个操作的操作。这样不是会让我得到我需要的标题吗？”嗯，不会。在这种情况下，对于撤消目的来说，这确实是一个操作，按Ctrl-Z只会返回到不幸的条目，其中年份会增加，而不是日期。

据我目前所知，这是一个死路一条。

关于方法的最后一个想法是使用 Power Query。它可以读取现有表格，然后让您通过将表格标题条目的副本更改回日期来对其进行转换，对其进行格式化，然后将其呈现给您，然后您可以添加列。它可以采用公式（M我相信，但如果您知道 Excel 的公式，应该不难做到）来计算新的列标题单元格，只需将 1 添加到前一列的日期即可。调整后，将其重新加载到电子表格中。诀窍是将其添加回原始表格中，我从未尝试过但应该可以，或者将其添加到自己的表格中，然后将其复制并粘贴到原始表格上。关键是 PQ 可以轻松地获取文本标题并将其转换为实际日期，因此只需在该行的每个新列单元格中添加 1 即可。当它们再次成为 Excel 中的表格时，Excel 会将它们更改回文本，但您实现了目标，所以谁在乎呢？

答案2

我一直在标题中使用日期，也希望标题的格式不是一般的数字/文本，因为同一个表或其他表中有索引/匹配公式。我在标题列中输入 mmm-yy 文本（例如），并在其他地方使用 value(Table1[[#header],[Apr-21]]) 公式。我主要使用月末日期进行会计核算，因此 eomonth(value(Table1[[#header],[Apr-21]]),0)。它使每个公式不必要地变长，因为一个唯一的数字可以作为表头 - 但是当标题显示 44316 时，没有人可以读取表格数据!!!!!!!!!

相关内容