Блог

У меня есть таблица Excel с пользовательскими данными и когда они путешествуют в течение месяца:

  User    01/01/2019    02/01/2019   03/01/2019    04/01/2019    05/01/2019
 Joe                        A           X             X              X
 Jane                                                 A              X
 Bob         A              X           X             Z
  

Где A — первый в серии дней пути, который заканчивается на Z.

На отдельном листе у меня есть сводка, где я хочу знать за конкретный день, кто начинает путешествовать.

т. е.

 Date Outbound    Name
02/01/2019       Joe
  

Если я заполняю исходящую дату, я бы хотел, чтобы она возвращала имя пользователя с «A» в столбце, соответствующем этой дате.

Я ни за что в жизни не смогу разобраться с этим с помощью индекса, совпадений или VLookup. Я не придаю значения формату листа, это просто то, что было унаследовано.

Любые указания с благодарностью!

 =INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$4)/(INDEX($B$2:$F$4,0,MATCH($A$8,$B$1:$F$1,0))="A"),ROW(A1)))
  

Агрегат с функцией 15 выполняет операции, подобные массиву. Поэтому не используйте полные ссылки на столбцы в функции array, иначе вы столкнетесь с большим количеством избыточных вычислений.

Настройте ссылки на ячейки в соответствии с вашими данными. скопируйте по мере необходимости, чтобы получить несколько имен. чтобы избежать сообщения об ошибке, когда отправление не найдено или количество имен, отправляющихся за день, превысило допустимое, оберните все это в функцию IFERROR следующим образом:

 =IFERROR(=INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$4)/(INDEX($B$2:$F$4,0,MATCH($A$8,$B$1:$F$1,0))="A"),ROW(A1))),"NOT FOUND")
  

Измените «НЕ НАЙДЕНО» на «», если вы предпочитаете пробелы или свое собственное сообщение.

Этот макрос найдет несколько имен для заданной даты.

 Function GetNameofTraveler(CellIn)

SourceShtNm = "Sheet1"  ' <---- Name of the shet that has the traveller data
DateRow = 1 ' <----- Chnage to match the row that has the travel dates
NameCol = 1 ' <----- Chnage to match the column that has the names

Set Datasheet = ActiveWorkbook.Sheets(SourceShtNm)

TargetDate = CellIn.Value

'* Get the range for the traveler data
LastCol = Datasheet.UsedRange.Columns.Count
LastRow = Datasheet.UsedRange.Rows.Count


NameList = ""
NumNames = 0
' Look for the date that matches
For i = 1 To LastCol
    If Datasheet.Cells(DateRow, i).Value = TargetDate Then
        ' Look for names on rows that have an "A"
        For j = 1 To LastRow
            If UCase(Datasheet.Cells(j, i)) = "A" Then
                NameList = NameList   Datasheet.Cells(j, NameCol)   vbLf
                NumNames = NumNames   1
            End If
        Next j
    End If
Next i

If NumNames = 0 Then
    NameList = "  "
End If
GetNameofTraveler = Left(NameList, Len(NameList) - 1)

End Function
  

В ячейке используется

 =GetNameofTraveler(A2)
  

где A2 имеет интересующую дату.

Таблица путешественника выглядит следующим образом:

Если на дату присутствует только один пользователь, вы можете использовать следующее:

 =INDIRECT(ADDRESS(MATCH("A",INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH($H$3,A1:F1,0)),"$1","")amp;":"amp;SUBSTITUTE(ADDRESS(1,MATCH($H$3,A1:F1,0)),"$1","")),0),1))
  

Может быть, существует уменьшенная версия, но я не смог так быстро до нее додуматься.
Если на дату больше пользователей, я думаю, вам нужна формула vba.

Если Лист1 выглядит следующим образом:

Лист2 может выглядеть следующим образом:

Формула в столбце B:

 =OFFSET(Sheet1!$A$1,MATCH("A",OFFSET(Sheet1!$A$1,0,MATCH(A1,Sheet1!$B$1:$F$1,),10,1),0)-1,0)
  

10 — это глубина поиска.

Если вам нужно более одного на дату, вам понадобится макрос VBA.