У меня есть рабочий лист с тысячами строк, которые содержат уникальный ID Aи один из 3 различных кодов ошибок M. Каждый индивидуальный ID может иметь 1, 2 или 3 ошибки. Они всегда будут в одном и том же порядке.
Я набрал вручную и то, Nчто нужно вывести. Я написал формулу, подобную этой, в столбцах O, P, Qс текстом ошибки:
=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")
Я попробовал что-то вроде этого, Rно по понятным причинам это не работает правильно. Есть ли способ сделать это так, чтобы это Rвыглядело как N?
=CONCATENATE(O42,"/", P42,"/",Q42)
Если это невозможно сделать с помощью формулы Excel, возможно, есть способ на VBA?
решение1
Ладно, пришлось немного почесать голову, но я понял:
Столбцы O, P& , Qкак у вас есть, озаглавленные Brand, Product& OEM. Превратите все это в таблицу с Ctrl- T(не обязательно, но удобно, и моя колонка Rопирается на это, но вы можете использовать ссылки на колонки, если хотите)
Столбец R:
=IF(LEN([Brand])>0,[Brand],IF(LEN([Product])>0,[Product],IF(LEN([OEM])>0,[OEM],"")))
Столбец S:
=IF(A2=A4,F2&"/"&F3&"/"&F4,IF(A2=A1,"",IF(A2=A3,F2&"/"&F3,F2)))
К сожалению, похоже, единственный способ использовать ссылки на таблицу для другой строки — использовать Offset, поэтому, чтобы упростить это, я вернулся к ссылкам на ячейки. Это как бы сводит на нет крутой/удобный фактор превращения всего этого в таблицу в первую очередь, но, как бы там ни было...
И... Вот фотография того, как это выглядит:
решение2
Я бы начал с оптимизации ваших формул O, P, и Q. У вас сейчас есть
=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")
IFERROR— отличная функция для отображения очищенной версии вычисленного значения, которое может быть кодом ошибки; я часто ее использую и рекомендую в ответах на Super User. Как вы, вероятно, знаете,
IFERROR(calculated_value, default_value)
это сокращение от
IF(ISERROR(calculated_value), default_value, calculated_value)
Но использование IFERRORдля создания очищенной версии некоторой ценности
а затем проверяем это значение, чтобы сделать что-то условно
является неоправданно неудобным способом использования IFERROR. Вышеприведенную формулу можно упростить до
=IF(ISERROR(SEARCH("Brand is not valid", M42)), "", "Brand")
И, как вы, я уверен, знаете, SEARCH("Brand is not valid", M42)
тесты, чтобы увидеть,M42 содержит Brand is not valid. Но, поскольку Column Mможет содержать только три строки ошибок, это можно сократить до
=IF(ISERROR(SEARCH("Brand", M42)), "", "Brand")
или упрощенно
=IF(M42 = "Brand is not valid", "Brand", "")
Хорошо, теперь я немного усложню формулы O, P, и :Q
O42→=IF($A42=$A41, O41, "") & IF(ISERROR(SEARCH("Brand", $M42)), "", "Brand")P42→=IF($A42=$A41, P41, "") & IF(ISERROR(SEARCH("Product", $M42)), "", "Product")Q42→=IF($A42=$A41, Q41, "") & IF(ISERROR(SEARCH("OEM", $M42)), "", "OEM")
Формула O42гласит:
Если это вторая или третья строка для этого идентификатора (Столбец
A), посмотрите на ячейку над этой (т. е.Oячейку Столбец для предыдущей строки), чтобы увидеть, установили ли мы уже, что у этой вещи недействительная марка. Также посмотрите на СтолбецMдля этой строки, чтобы увидеть, является ли онаBrand is not valid. Затем объедините результаты.
Поскольку уникальный идентификатор никогда не будет указан дважды с одной и той же ошибкой (правда?), эти два подрезультата никогда не будут оба непустыми, так что по сути это операция «ИЛИ»:
Показывать значение,
Brandесли эта строка ИЛИ одна из предыдущих строк для этого идентификатора содержит ошибку недопустимой марки.
Это приводит к перетаскиванию значений O, P, и Qвниз в последнюю строку для каждого идентификатора:
Обратите внимание, что в строках 41, 44, 47 и 49 показаны краткие формы всех ошибок, которые применяются к соответствующим им идентификаторам в столбцах O, P, и Q.
Я определил Column Rтак же, как вы. СмотритеСгенерировать список содержимого ячеек, разделенных запятыми, исключая пробелы.
для методов устранения нежелательных косых черт.
Если достаточно иметь желаемую конкатенацию только в строках 41, 44, 47 и 49, то все готово. В противном случае, определите N42как
=IF($A22=$A23, N23, R22)
или
=IF($A22<>$A23, R22, N23)
Это почти тот же самый прием, который я использовал в Columns O, Pи Q, но в противоположном направлении:
Если это последняя строка для этого идентификатора (т. е. если это строка 41, 44, 47 или 49), используйте конкатенацию значений из этой строки (которая является полным набором кодов ошибок для этого идентификатора). В противном случае посмотрите на ячейку под этой (т. е. ячейку Столбец
Nдля следующей строки), которая будет иметь правильный ответ.
Другими словами, желаемые значения просачиваются до первой строки для каждого идентификатора.