☰

Как быть с пустыми значениями в формуле?

tag-icon • tag-icon microsoft-excel worksheet-function
Как быть с пустыми значениями в формуле? 
=VLOOKUP(VLOOKUP(A7,InventoryLog,5,FALSE),Price_list,IF(Q2InventoryLog!F7="S",2,3),FALSE)

Я хочу, чтобы моя функция искала номер идентификатора заказа и на его основе сопоставляла его с названием товара. Если описание товара маленькое ("S"), то она должна вернуть цену в столбце 1, а если товар не маленький, то она должна вернуть товар в столбце 3.

Проблема в том, что иногда у меня есть пустая строка, так что она все сбивает. Поэтому, когда она отстает на одну строку, когда появляется пустая строка.

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

Есть 3 возможности:

IF(Q2InventoryLog!F7="L"...), 
IF(Q2InventoryLog!F7="S"...)
IF(Q2InventoryLog!F7=""...)

решение1

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

=VLOOKUP(VLOOKUP(A7,InventoryLog,5,FALSE),Price_list,IF(Q2InventoryLog!F7="S",2,3),FALSE)

VLOOKUP(A7,InventoryLog,5,FALSE: В A7 есть некий идентификационный номер заказа, и поиск использует его для нахождения ItemName в столбце 5 записи, которую он находит в InventoryLog.

Затем внешний поиск использует это имя элемента для поиска записи в Price_list и на основе строки в Q2InventoryLog возвращает столбец 2 или 3 из этой записи в Price_list.

Логично, возникает вопрос, ПОЧЕМУ F7 должна быть строкой с записью, которая нужна A7? Очевидно, что это может быть не связано: вы имели в виду "7" и просто набрали "F" и "7" Но почему тогда "F", и как мы можем знать, что искать? Возможно, Q2InventoryLog должен сопоставлять InventoryLog построчно и делает это ОЧЕНЬ хорошо (или... это было бы довольно бессмысленно после того, как данные использовались, а затем обновлялись несколько раз!) ЗА ИСКЛЮЧЕНИЕМ того, что время от времени по какой-то причине проглядывают пустые строки.

Знание того, почему записи совпадают (почему F7 — это ячейка для ЖЕСТКОГО КОДИРОВАНИЯ в эту ячейку, а не в какую-то другую ячейку), не рассматривается в вашем вопросе. Вы, кажется, спрашиваете об этом снова и снова, каждый раз с большим количеством деталей, так что, возможно, вы ответите на этот вопрос в следующей итерации. И кто-то может дать вам более конкретную помощь, более скользкую помощь. Менее общую. Но до тех пор я дам два указания:

  1. Вы можете попытаться организовать данные Q2InventoryLog таким образом, чтобы они включали либо имя элемента, которое находит внутренний поиск, либо номер идентификатора заказа, с которого все начинается (первый вариант был бы золотым стандартом в некотором смысле, поскольку это были бы «лучшие» данные (не в последнюю очередь из-за того, что они более широко полезны), но второй подход был бы совершенно допустимым, поскольку можно было бы предположить, что данные «на месте» здесь будут включать информацию о размерах в информации, связанной с заказом, хотя это не обязательно работает в более общей версии вашей проблемы). Но в любом случае вы можете использовать второй внутренний поиск, чтобы найти точную запись и выбрать данные, которые столбец F7 представляет, и использовать их в своем IF()тесте.

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

Я имею в виду сделать что-то совсем простое. Использовать FILTER()для простой фильтрации пустых строк. Не использовать функцию фильтрации Excel, так как она сохранит адреса строк и все равно потребует больше работы для этого. А просто FILTER()фактический набор данных Q2InventoryLog с простым условием "что-то <> пустое", чтобы исключить вызывающие трудности пустые строки. Поскольку данные Q2InventoryLog, похоже, должны соответствовать один к одному, это сработает. В вашем поиске используйте таблицу/диапазон, которые FILTER()выдают вместо фактических данных.

Упрощенная версия функции FILTER(), используемой таким образом, может быть такой:

(Допустим, запись C7:F7 в Q2InventoryLog, и пустыми являются целые строки, а не только некоторые ячейки в строке. Таким образом, если C12 пуста, то вся строка является проблемной и ее необходимо удалить, поскольку данные, необходимые в строке 12, в настоящее время находятся в строке 13 и будут перемещены в правую строку, если строка 12 будет удалена. Также предположим, что данные находятся в C3:F27.)

=FILTER(C3:F27,C3:C27<>"")

Разумеется, вам придется адаптировать это под свои данные.

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

Наконец, если нет однозначного соответствия с неудачно вставленными случайными пустыми строками, которые все портят, ну, это ничего не решит (ммм... если только не окажется, что вы МОЖЕТЕ использовать структуру Q2InventoryLog для поддержки этого дополнительного внутреннего поиска). Так что вам придется попробовать спросить еще раз...

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

решение2

Поэтому, чтобы сделать вашу формулу терпимой к пустым ячейкам, мы будем использовать функцию ISBLANK():

=IF(ISBLANK(A7), "", VLOOKUP(VLOOKUP(A7,InventoryLog,5,FALSE),Price_list,IF(Q2InventoryLog!F7="S",2,3),FALSE))

Это вернет пустой текст, если A7 пуст. Однако, если вы хотите вернуть цену для элемента A8, вам просто нужно заменить "" на формулу A8:

=IF(ISBLANK(A7), VLOOKUP(VLOOKUP(A8,InventoryLog,5,FALSE),Price_list,IF(Q2InventoryLog!F8="S",2,3),FALSE), VLOOKUP(VLOOKUP(A7,InventoryLog,5,FALSE),Price_list,IF(Q2InventoryLog!F7="S",2,3),FALSE))

Связанный контент