Проблема с использованием функции Excel OFFSET

Question

У меня есть книга Excel с многочисленными листами под названием "Неделя1", "Неделя2", "Неделя3" ... "Неделя7". Каждую неделю я вписываю идентификаторы клиентов людей, покупающих мой продукт, в столбец C, и - поскольку мне интересно знать, являются ли они постоянными или новыми, - я написал функцию для проверки, введены ли клиенты, которых я ввожу. в Week2-Week7 появляются новые клиенты или они уже купили мой продукт.

С этой целью я разработал следующую формулу, которую я ввел в столбец F для недели 2:

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

С соответствующей частью "Week1" выглядит так:

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

3|  ValPot1  |  *blank*  |

С соответствующей частью "Week2" выглядит так:

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

3|  ValPot1  |    Old    |

Теперь, хотя это работает хорошо, это становится довольно громоздким на неделе 7, когда я должен использовать следующую функцию:

= ЕСЛИ (ИЛИ (COUNTIF (Week1!$ C $ 3:$ C $ 100, С3)> = 1, СЧЕТЕСЛИ (Week2!$ C $ 3:$ C $ 100, С3)> = 1, СЧЕТЕСЛИ (Week3!$ C $ 3:$ C $ 100, С3)> = 1, СЧЕТЕСЛИ (Week4!$ C $ 3:$ C $ 10> 0, С3)> = 1, СЧЕТЕСЛИ (Week5!$ C $ 3:$ C $ 100, С3)> = 1, СЧЕТЕСЛИ (Week6!$ 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")

По сути, я просто заменяю свою предыдущую ссылку на лист (недели 1!$ 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")

Функция будет искать все предыдущие недели и сообщать вам, является ли клиент новым (ЛОЖЬ) или старым (ИСТИНА)

Answer 3

почему бы просто не использовать функцию, которая ищет идентификатор клиента во всех листах, и если он будет найден более одного раза, вернется "Старый клиент"?

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

Хорошего дня!