Проверка наличия метки между двумя другими метками в столбце

Question

У меня есть столбец (назовите его A) в Excel списки различных элементов и их свойств. С каждым элементом связано слово "Тип", поэтому в столбце А есть ячейка с этой меткой, так что в столбце рядом с ним (В) может быть указан тип. С некоторыми (не всеми) элементами также связана метка "Виджет".

Поскольку каждый элемент имеет только одну метку "Тип", я хотел бы иметь возможность выполнить поиск в Excel для метки "Тип", а затем проверить, есть ли метка "Виджет" ДО следующей метки "Тип", и если вставьте значение в столбце B рядом с меткой "Тип" на другом листе. Это будет продолжаться до тех пор, пока больше не будет найдено ни одного экземпляра слова "Тип". Это то, что возможно осуществить? Чтобы сделать макет более понятным, ниже приведен пример столбца. Обратите внимание, что количество пробелов между каждой записью, к сожалению, не согласовано и поэтому не может использоваться как способ отследить, где вы находитесь. Точно так же слово Widget не обязательно должно быть непосредственно над Type, оно может появляться где угодно между двумя экземплярами "Type", которые его подставляют.

**Type**  01
Width     .5
Length    .4
Height    .3
Weight    15
Widget    Blue
**Type**  072
Width      .1 
Length     .1
Height     .1
Weight     50
**Type**   025
Width      .4
Length     .4
Widget     Red
Weight     11
Height     .4

Тогда желаемый результат на отдельном листе будет просто:

01
025

так как эти типы имеют связанный виджет.

Answer 1

Обновленная формула. Находит ячейку с первым типом и вторым типом и ищет виджет слова между ними. Если он существует, вернуть значение рядом с типом, если не вернуть пустым.

=IFERROR(IF(MATCH("widget",INDIRECT("A" & SMALL(IF(A:A="type",ROW(A:A),1000000),E1) & ":A" &                
SMALL(IF(A:A="type",ROW(A:A),1000000),E1+1)),0)>0,INDIRECT("B" & 
SMALL(IF(A:A="type",ROW(A:A),1000000),E1)),""),"")

Введено с помощью Ctrl + Shift + Enter

Ячейка E1 будет иметь номер 1, E2 будет иметь номер 2. Скопируйте формулу вниз, чтобы вернуть каждый экземпляр WIDGET. Будет ошибка, если ее больше нет, так что вы можете использовать iferror вокруг нее.

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

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

* Редактировал макрос для работы с поиском типа или виджета в строке, а не просто с точным соответствием

Sub get_types()

Dim lRow As Long, cRow As Long, nRow As Long
Dim FindType As Boolean

FindType = False
nRow = 2

With Sheets("sheet1")
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row

    For cRow = lRow To 1 Step -1
        If InStr(UCase(.Range("A" & cRow).Value), "WIDGET") > 0 Then FindType = True
        If FindType And InStr(UCase(.Range("A" & cRow).Value), "TYPE") > 0 Then
            .Range("E" & nRow).Value = .Range("B" & cRow).Value
            FindType = False
            nRow = nRow + 1
        End If
    Next
End With

End Sub

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

Answer 2

Вы можете сделать это с помощью оператора IF AND, например:

=IF(AND(A1="Widget",A2="**Type**"),B2,"")

Это в основном говорит Excel, ЕСЛИ текст "Виджет" находится в ячейке над ячейкой с текстом «** Тип **», введите данные из ячейки в столбце B рядом с «** Тип **», в противном случае оставьте это пусто

Вы в конечном итоге с этим:

Если вы хотите, чтобы данные (072 в этом примере) отображались на другом листе, тогда ваша формула должна ссылаться на ячейки на их конкретном листе. Предполагая, что у вас есть данные на листе под названием "Лист 1", используйте эту формулу на листе, где вы хотите, чтобы данные отображались:

=IF(AND(Sheet1!A2="Widget",Sheet1!A3="**Type**"),Sheet1!B3,"")

Answer 3

Вот мой пример ваших данных и требований. Обратите внимание, что я расширил ваши примеры данных, чтобы продемонстрировать, как рассматриваются несколько сценариев.

         

Стандартные формулы в D2:G2:

D2 is = IF(COUNTIF(A:A, "Widget")> COUNTIF(D $ 1:D1, "Widget"), "Widget", "")`

E2 =IF(LEN($D2),INDEX($B$1:$B$9999,SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"widget")*1E+99,,),ROW(1:1))),"")

F2 =IF(LEN($D2),INDEX($B$1:$B$9999,SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"**Type**")*1E+99,,),COUNTIF(INDIRECT("A1:A"&SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"widget")*1E+99,,),ROW(1:1))),"**Type**"))),"")

G2 =IF(LEN($D2),VLOOKUP("**Type**", INDIRECT("A"&SMALL(INDEX(ROW($1:$9999)+($A$1:$A$9999<>"widget")*1E+99,,),ROW(1:1))&":B9999"), 2, FALSE),"")

Я сократил диапазоны ссылочных ячеек из полных ссылок на столбцы, чтобы минимизировать задержку вычислений. И INDIRECT и INDEX (в форме массива здесь) считаются энергозависимыми функциями, которые пересчитываются всякий раз, когда происходит цикл вычисления; не только когда значение изменяется, что непосредственно влияет на них. Заполните при необходимости.

Я предоставил вам этот образец рабочей книги на своем OneDrive здесь для ознакомления и загрузки.

Widget_types.xlsx

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