Excel - 跨多个工作表处理文本

发布于 2024-12-15 07:13:58 字数 743 浏览 0 评论 0原文

我有一个 Excel 文件，它使用多个工作表来汇总多个房产的付款和付款时间表。

它的设置使得第一个工作表是所有内容的摘要，后续工作表详细说明了不同的属性。我有一个列（仅用于描述），我想将文本带回第一张纸，如果后续纸上有多个条目，请让我知道。

我知道 Excel 不喜欢跨多个工作表处理文本，因此下面的代码可以很好地为我显示一个条目。它将始终位于后续工作表的同一单元格中。

=IF(COUNTA(Sheet1:Sheet5!D15)=0,"", IF(COUNTA(Sheet1:Sheet5!D15)=1, (Sheet1!D15&Sheet2!D15&Sheet3!D15&Sheet4!D15&Sheet5!D15), "--> Multiple Entries"))

然而，我遇到过一种情况，我确实需要在后续工作表中放置两个相同的条目。

例如使用 |作为换页符

"" | “付费#1”| “” | ""

"已支付#1" | “付费#1” | “”| “”

“” | “付费#2”| “付费#1” | ""

情况#2 很奇怪，可以有多个相同的条目，但#3 不同的条目需要突出显示，就像我现在拥有的那样（--> 多个条目）就足够了。现在这两种情况都会被突出显示。

我真的不知道从哪里开始，我找不到处理文本的 3D 参考（沿着匹配线的东西），有什么建议吗？

一如既往，提前致谢，我非常感谢所有的帮助。

原文

I have an excel file that uses multiple worksheets to summarize payments and the payment schedule for multiple properties.

It's set up so that the first sheet is a summary of everything, with subsequent sheets detailing different properties. I have a column (just for descriptions) that I want to bring back the text to the first sheet, and if there are multiple entries on subsequent sheets for it to let me know.

I know excel doesn't like working with text across multiple worksheets so this code below worked well to bring out a single entry for me. It will always be in the same cell on subsequent sheets.

=IF(COUNTA(Sheet1:Sheet5!D15)=0,"", IF(COUNTA(Sheet1:Sheet5!D15)=1, (Sheet1!D15&Sheet2!D15&Sheet3!D15&Sheet4!D15&Sheet5!D15), "--> Multiple Entries"))

However I've come across a situation where I do need to put two identical entries in subsequent sheets.

For an example using | as a sheet break

"" | "Paid #1" | "" | ""

"Paid #1" | "Paid #1" | "" | ""

"" | "Paid #2" | "Paid #1" | ""

Case #2 is the odd one where its ok to have multiple identical entries but #3 where they are different needs to be highlighted, as I have it now (--> Multiple Entries) is enough. Right now both cases would be highlighted.

I don't know where to start really, I can't find a 3d reference (something along the lines of match) that handles text, any suggestions?

As always, thanks in advance, I really appreciate all the help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

大海や 2024-12-22 07:13:59

一种方法是编写返回所需字符串的 UDF。不幸的是，UDF 参数不支持 3D 范围，因此需要解决方法。

根据您的问题，您似乎想要考虑除摘要表之外的所有工作表中的相同单元格。在此基础上，此 UDF 将返回除放置公式的工作表之外的所有工作表中相同单元格的串联。您可以调整它以根据需要返回其他内容。

请注意，我保持了简单的演示方法，您需要处理错误情况，例如 r 引用 > 1 个单元格，或另一个工作表/工作簿上的单元格。

Function MergeSheets(r As Range) As Variant
    Dim a As String, s As String
    Dim sh As Worksheet
    Application.Volatile

    a = r.Address
    For Each sh In r.Worksheet.Parent.Worksheets
        If sh.Name <> r.Worksheet.Name Then
            s = s & sh.Range(a).Value
        End If
    Next
    MergeSheets = s
 End Function

One way would be to write a UDF that returns the required string. Unfortunately UDF parameters don't support 3D ranges, so a work around is required.

Based on your question it apears you want to consider the same cell in all sheets except the summary sheet. On this basis, this UDF will return a concatenation of the same cell in all sheets except the sheet the formula is placed on. You can adapt it to return other things as you wish.

Note that I have kept it simple to demo a method, you would need to handle error cases, like r refering to >1 cell, or a cell on another sheet/workbook.

Function MergeSheets(r As Range) As Variant
    Dim a As String, s As String
    Dim sh As Worksheet
    Application.Volatile

    a = r.Address
    For Each sh In r.Worksheet.Parent.Worksheets
        If sh.Name <> r.Worksheet.Name Then
            s = s & sh.Range(a).Value
        End If
    Next
    MergeSheets = s
 End Function

回复收藏 0 原文

~没有更多了~