Поиск (текстовых) значений в массиве Excel

Question

Я искал в Интернете и играл в Excel 2010, но не мог найти ответ.

Ради чистоты я сделал реферат, но реальный файл намного больше.

На другом листе я хочу посмотреть текст A и получить результат относительного времени (столбец B). Однако, если строка 2 простирается до столбца Z (или даже больше), я хочу, чтобы все времена вывода возвращали одно и то же значение, как видно для A, B и C на рисунке ниже.

Столбец D, вероятно, пропадет / будет скрыт, когда я исправлю условное форматирование. Также возможно, что «K» предшествует «H», поэтому значения могут быть не в порядке (как видно для F, G и H.

Это первая проблема. Я попытался решить это с помощью INDEX-MATCH, но для неизвестного количества строк и столбцов это довольно утомительно. Есть также много пробелов, поэтому мне нужно создать сейфы для этого.

Формула только для 6 столбцов будет выглядеть так:

= IFERROR(INDEX(T0.02!$ B $ 1:$ B $ 100; ЕСЛИ (ЕНД (ПОИСКПОЗ ($ A3; T0.02!$ D $ 1:$ D $ 100; 0))= FALSE; ПОИСКПОЗ ($ A3; T0.02!$ D $ 1:$ D $ 100; 0); IF (ISNA (MATCH ($ A3; T0.02!$ E $ 1:$ E $ 100; 0))= FALSE; ПОИСКПОЗ ($ A3; T0.02!$ E $ 1:$ E $ 100; 0); IF (ISNA (MATCH ($ A3; T0.02!$ F $ 1:$ F $ 100; 0))= FALSE; ПОИСКПОЗ ($ A3; T0.02!$ F $ 1:$ F $ 100; 0); IF (ISNA (MATCH ($ A3; T0.02!$ G $ 1:$ G $ 100; 0))= FALSE; ПОИСКПОЗ ($ A3; T0.02!$ G $ 1:$ G $ 100; 0); IF (ISNA (MATCH ($ A3; T0.02!$ H $ 1:$ H $ 100; 0))= FALSE; ПОИСКПОЗ ($ А3; T0.02!$ H $ 1:$ H $ 100; 0); IF (ISNA (MATCH ($ A3; T0.02!$ I $ 1:$ I $ 100; 0))= FALSE; ПОИСКПОЗ ($ A3; T0.02!$ I $ 1:$ I $ 100; 0); ""))))))); "")

Главный вопрос для меня: возможно ли сделать это более простым способом в Excel или есть способ сделать это с помощью VBA. Я не очень «хорош» с VBA, поэтому мне тогда действительно понадобится помощь.

Answer 1

Вот подход, который использует некоторые VBA для упрощения поиска без каких-либо вспомогательных ячеек.

Часть VBA - это определенная пользователем функция (обычно называемая UDF), которая служит той же цели, что и функция MATCH, которую вы пытались использовать, чтобы найти строку, в которой находится текст:

Function FindRow(valueToFind, searchRange As Range, Optional IsCaseSensitive As Variant) As String
   Dim aCell As Range
   Dim lastCell As Range
   If IsMissing(IsCaseSensitive) Then
      IsCaseSensitive = False
   End If
   Set lastCell = searchRange.Cells(searchRange.Rows.Count, searchRange.Columns.Count)
   Set aCell = searchRange.Find(valueToFind, After:=lastCell, LookIn:=xlValues, MatchCase:=IsCaseSensitive, LookAt:=xlWhole)
   FindRow = aCell.Row - searchRange.Cells(1, 1).Row + 1
End Function

Что делает это полезным, так это то, что функция VBA FIND, в отличие от функции MATCH на рабочем листе, может выполнять поиск в диапазоне, который охватывает несколько столбцов и строк. Как и MATCH, эта функция VBA возвращает строку в диапазоне поиска, в которой найдено указанное значение, и возвращает значение #VALUE! ошибка, если не удается найти значение.

Первые два аргумента FindRow - это значение для поиска (или ячейка, в которой находится это значение) и диапазон для поиска. Существует третий необязательный аргумент: если установлено значение TRUE или 1, при поиске будет учитываться регистр; если он опущен или имеет значение ЛОЖЬ или 0, при поиске регистр не учитывается.

На втором листе вы будете использовать INDEX в сочетании с FindRow для поиска времени, например,

=INDEX(Sheet1!$B$2:$B$8,FindRow(A3,Sheet1!$D$2:$Z$9))

со вторым аргументом для FindRow, установленным в диапазон, который содержит значения комментариев. Хотя я показал диапазон поиска, расширяющийся до столбца Z и строки 9, FindRow может обрабатывать диапазоны любого размера.

Чтобы установить функцию VBA, сначала выберите "Visual Basic" на вкладке "Разработчик" на ленте. Выберите имя рабочей книги на панели "Проект" в левом верхнем углу (рабочая книга будет обозначена как «VBAProject (YourSheetName)» и вставьте модуль с помощью строки меню вверху. Наконец, вставьте код функции в большую область кода, которая появляется справа.

Answer 2

Вы можете сделать это относительно просто с помощью "формулы массива" без добавления каких-либо столбцов, например, для диапазона до столбца Z

=MIN(IF(T0.02!$D$2:$Z$100=$A3;T0.02!$B$2:$B$100))

подтверждено с помощью CTRL+SHIFT+ENTER

Если совпадения нет, то вы получите ноль, а если хотите вместо этого пробел, чтобы различать допустимые нулевые значения, вы можете использовать SMALL с IFERROR, т.е.

=IFERROR(SMALL(IF(T0.02!$D$2:$Z$100=$A3;T0.02!$B$2:$B$100);1);"")

Answer 3

Я использовал столбец C на вашем первом листе в качестве временного столбца:

1. Столбец C во входном листе используют:

   =», "& D3, &", "& Е3 &", "& F3 &", "& G3 &", "& H3 &", "& I3 &", "& J3 &", "& К3 &", "& L3 &", "& M3 &", "& Н3 &", "& О3 &" , "& P3 &", "& Q3 &", "& R3 &", "& S3 &", "& T3 &", "& У3 &", "& V3 &", "& W3 &", "& Х3 &", "& У3 &", "& Z3

   - объединить все возможные комментарии в одну ячейку
2. в C3 (и других ячейках) на листе агрегации: найдите во всех объединенных ячейках в столбце помощника комментарий и используйте его в индексной функции для возврата времени:

   = IFERROR (INDEX (Лист1!$ B: $ B, MATCH (FALSE, ISERROR (FIND (" "& A3 &"," Лист1!$ C: $ C)), 0)), "")

   Введите это как формулу массива, т.е. используйте Ctrl - Shift - Enter.

НТН!