#excel #excel-formula
#преуспеть #excel-формула
Вопрос:
Я изо всех сил пытаюсь отформатировать ВПР, чтобы он работал так, как мне нужно. #N / A повсюду.
Вероятно, я использую что-то совершенно неправильно и не по назначению.
У меня есть следующие данные:
Windows Version Table - AA2:AD3450
Hardware Model Column - P2:P85
Accepted SKU - M2:M55
Client Name - Sheet1 Cell A2:A9000
Client Model - Shee1 Cell J2:J9000
Client Area - Sheet1 Cell B2:B9000
Client OS - Sheet1 Cell E2:E9000
Мне нужно определить следующее
- ЕСЛИ «Версия Windows» находится в «клиентской ОС», а «Модель оборудования» соответствует «Модели клиента», выведите «Имя клиента» в столбец AG И выведите «Область клиента» в столбец AH И выведите «Клиентская ОС» в столбец AI
Я надеюсь, что в этом есть хоть какой-то смысл. Я уже некоторое время пытаюсь разобраться в этом вопросе.
Таблица ссылок на данные:
Таблица информации о клиенте
Ожидаемый результат
Комментарии:
1. Можете ли вы опубликовать пример данных? ваш ввод и ожидаемый результат
2. Вот ссылка на пример рабочей книги. Не уверен, как еще поделиться, извинения file.io/eRPBJDdQIBNx
3. Обновил сообщение дополнительными изображениями, поскольку документ Excel, вероятно, не был идеальным
4. Как вы проверяете ОС? Кроме того, в соответствии с предоставленными данными
name 9иname 4не соответствуют требованиям модели.5. В этом случае критерием проверки будет то, содержит ли строка «Windows 7». Моя ошибка с именем 9, это было бы неверно, поскольку артикул процессора равен 5. Моей ошибкой в этом случае был долгий день, когда я это сделал.
Ответ №1:
Требование: проверка записей в lo.DATA таблице на lo.Clients соответствие таблице и выводам valid и invalid записям в двух отдельных таблицах lo.Ok и lo.Error .
Примечание: допустимая запись должна иметь Client OS = 7 согласно OP.
lo.DATA таблица, расположенная в [B11:F21] и lo.Clients таблица, расположенная в [H11:M15]
Функция VLOOKUP: эта функция имеет ограничения, когда дело доходит до использования критериев, поэтому я предлагаю использовать ее только для прямых совпадений. Мы будем использовать эту функцию в таблицах lo.Ok и lo.Error возвращать связанные значения после определения соответствия записи.
Критерии:
Клиентская ОС:
= lo.DATA[@[Client OS]] = 7 Клиентская ОС должна быть 7 в соответствии с OP (измените по мере необходимости).
Функция СОПОСТАВЛЕНИЯ: используйте эту функцию для проверки соответствия записи модели и артикула.
Модель клиента:
= MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 )
Артикул:
= MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 )
Несколько критериев:
Формула:
= ( ( lo.DATA[Client OS] = 7 )
* ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
* ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) )
АГРЕГАТНАЯ функция: используйте эту функцию для возврата списка допустимых / недопустимых записей после применения соответствующих нескольких критериев.
Используйте Array form функцию AGGREGATE(function_num, options, array, [k]) , были:
Параметры АГРЕГИРОВАННОЙ функции:
function_num = 15 (МАЛЕНЬКИЙ)
options = 6 (Игнорировать значения ошибок)
array =
( ( lo.DATA[Client OS] = 7 )
* ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
* ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) )’
[k] = ROWS([Col$Row:ColRow) (Функция СТРОК ColRow = Адрес 1-й ячейки, в которую вводится формула, т.е. ROWS(W$12:W12) )
Это результирующая формула:
= AGGREGATE( 15, 6,
( ( lo.DATA[Client OS] = 7 )
* ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
* ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) ),
ROWS(W$12:W12) )
Хотя АГРЕГАТНАЯ функция возвращает действительную запись, она пропускает положение записей. Для получения позиции используйте функцию СТРОКИ
ROW (Col:Col) (т.е. Col = столбец, в который вводится формула)
Это результирующая формула:
= AGGREGATE( 15, 6,
ROW(AA:AA) / ( ( lo.DATA[Client OS] = 7 )
* ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
* ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) ),
ROWS(AA$12:AA12) )
МНОГОКРИТЕРИАЛЬНАЯ формула
Наконец, используйте функцию INDEX, чтобы вернуть соответствующую запись из `lo.Таблица ДАННЫХ.
= IFERROR( INDEX( lo.DATA[Client Name], AGGREGATE( 15, 6,
ROW(AC:AC) / ( ( lo.DATA[Client OS] = 7 )
* ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
* ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) ),
ROWS(AC$12:AC12) ) ), "" )
Предлагаемое решение:
Пожалуйста, убедитесь в согласованности заголовков таблиц, это ключ к VLOOKUP тому, чтобы формулы возвращали правильные значения.
Допустимая запись в таблице lo.Ok , расположенная по [AL11:AP21]
Формулы
Имя клиента:
= IFERROR( INDEX( lo.DATA[Client Name], AGGREGATE( 15, 6,
ROW(AL:AL) / ( ( lo.DATA[Client OS] = 7 )
* ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
* ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) ),
ROWS(AL$12:AL12) ) ), "" )
Модель клиента, Клиентская область, артикул, Клиентская ОС:
Введите эту формулу в [AM12] , затем скопируйте в [AM12:AP21]
= IFERROR( VLOOKUP( [@[Client Name]], lo.DATA25,
MATCH( AM$11, lo.DATA[#Headers], 0 ), 0 ), "" )
Недопустимая запись в таблице lo.Error , расположенная по [AT11:AY21]
Формулы
Имя клиента:
Проверка выполняется для lo.Ok таблицы (т.Е. Запись из lo.DATA есть invalid , если она есть not found lo.Ok ).
= IFERROR( INDEX( lo.DATA[Client Name], AGGREGATE( 15, 6,
ROW(AT:AT) / ISERROR( MATCH( lo.DATA[Client Name], Lo.OK[Client Name], 0 ) ),
ROWS(AT$12:AT12) ) ), "" )
Модель клиента, Клиентская область, артикул, Клиентская ОС:
Введите эту формулу в [AU12] , затем скопируйте в [AX12:AP21]
= IFERROR( VLOOKUP( [@[Client Name]], lo.DATA,
MATCH( AU$11, lo.DATA[#Headers], 0 ), 0 ), "" )
Статус:
= IF( LEN([@[Client Name]])=0, "",
SUBSTITUTE( CONCATENATE( "Error in: ,",
IF( [@[Client OS]] <> 7, ", OS", "" ),
IF( ISERROR( MATCH( [Client Model], lo.Clients.A[Model], 0 ) ), ", Model", "" ),
IF( ISERROR( MATCH( [SKU], lo.Clients.A[SKU], 0 ) ), ", SKU", "" ) ), ",,", "" ) )
