使用 Excel OFFSET 函数时遇到问题

Question

我有一个 Excel 工作簿，其中包含许多工作表，标题为“第 1 周”、“第 2 周”、“第 3 周”...“第 7 周”。每周，我都会在 C 列中输入购买我产品的客户 ID，并且——因为我有兴趣知道这些是回头客还是新客户——我编写了一个函数来检查我在第 2 周至第 7 周输入的客户是新客户还是他们以前购买过我的产品。

为此，我制定了以下公式，并将其输入到第 2 周的 F 列中：

=IF(COUNTIF(Week1!$C$3:$C$100,C3)>=1,"Old","New")

“Week1”的相关部分如下所示：

_|-----C-----|-----F-----|

3|  ValPot1  |  *blank*  |

“Week2”的相关部分如下所示：

_|-----C-----|-----F-----|

3|  ValPot1  |    Old    |

现在，尽管这种方法效果很好，但是到了第 7 周时就变得相当麻烦了，因为我必须使用以下函数：

=IF(OR(COUNTIF(第 1 周！$C$3：$C$100，C3)>=1,COUNTIF(第 2 周！$C$3：$C$100，C3)>=1,COUNTIF(第 3 周！$C$3：$C$100，C3)>=1,COUNTIF(第 4 周！$C$3：$C$10>0，C3)>=1,COUNTIF(第 5 周！$C$3：$C$100，C3)>=1,COUNTIF(第 6 周！$C$3：$C$100，C3)>=1),"旧","新")

因此，我一直在尝试（但没有成功）使用以下 VBA 函数：

Function SHEETOFFSET(offset, Ref)

'   Returns cell contents at Ref, in sheet offset

    Application.Volatile

    With Application.Caller.Parent

        SHEETOFFSET = .Parent.Sheets(.Index + offset) _

         .Range(Ref.Address).Value

    End With

End Function

通过将其合并到工作表“Week2”的单元格中F3，方式如下：

=IF(COUNTIF(SHEETOFFSET(-1,$C$3):SHEETOFFSET(-1,$C$100),C3)>=1,"Old","New")

实际上，我只是Week1!$C$3:$C$100用我的新SHEETOFFSET函数替换了我以前的工作表引用......

不幸的是，这不起作用，只是返回错误#VALUE!。我做错了什么？我该如何修复我的函数？

Answer 1

我会将数据重新组织到一张工作表中，并添加一个“周”列。然后，您可以使用原始公式，将“周 1”引用替换为对单张工作表的引用。

这还有太多其他的好处，无法一一列举，使您的文件更易于使用、维护、格式化和分析。

Answer 2

您的 UDF 是正确的，但我会稍微改变一下逻辑。

我会给它指定查看的范围和要查找的值，然后使用调用它的表单的名称来决定要查看哪些其他表单。

Function CheckCustomer(offset, Ref)

Dim InitialSheet As String
Dim WeekNum As Long
Dim SheetLoop As Long

InitialSheet = Application.Caller.Parent.Name 
'know where the function is called from
WeekNum = Val(Mid(InitialSheet, 5)) - 1
' week to start searching from is one less than current sheet name

For SheetLoop = 1 To WeekNum
    If WorksheetFunction.CountIf(Sheet("Week" & SheetLoop).Range(offset.address), Ref.Value) > 0 Then
        'Found name - return true, and stop looking for more
        CheckCustomer = True
        Exit Function
    End If
Next
'all searches didn't find value, so new customer
CheckCustomer = False

End Function

要在您的函数中使用IF，您可以像这样调用它：

=IF(CheckCustomer($C$3:$C$100,C3),"Old","New")

该函数将搜索所有前几周的客户，并让您知道客户是新客户（FALSE）还是老客户（TRUE）

Answer 3

为什么不简单地使用在所有工作表中搜索客户 ID 的函数，如果找到多次，则返回“老客户”？

Function newclient(clientID As String) As String
Dim count As Integer

For Each Worksheet In Worksheets
    If Not Worksheet.UsedRange.Find(clientID, lookat:=xlWhole) Is Nothing Then
      count = count + 1
      MsgBox (count)
    End If
Next Worksheet

If count > 1 Then
  newclient = "Old client"
Else

newclient = "New client"
End If

End Function

祝你今天过得愉快！