Формула, возвращающая ошибку #VALUE для одной ячейки, но не для другой
Я использую Microsoft Excel 365, в котором использую формулу для очистки необработанных данных, полученных путем копирования и вставки информации из списка из поиска по картам MTG Salvation. Несколько замечаний по поводу необработанных данных:
- Я выбираю и выделяю, перетаскивая имя верхнего элемента, а затем Shift+ щелчок левой кнопкой мыши справа от цены последнего элемента в отображаемом списке;
- при вставке происходит перенос текста (даже если я сохраняю форматирование назначения из некоторой предварительной настройки);
- некоторые необработанные данные о ценах содержат поправку на отклонение, указанную либо после символа «+», либо в круглых скобках.
Я пытаюсь сохранить важные данные о ценах, удалить корректировки отклонений и преобразовать полученные данные в числовое значение, чтобы иметь возможность выполнять с ними дальнейшую числовую обработку. Формула, которую я использую, предназначена для получения необработанных данных о ценах, находящихся в столбце G, и вывода числового значения очищенного результата в столбце H следующим образом:
=NUMBERVALUE(SUBSTITUTE(LEFT($G2,LEN($G2)-IFERROR(FIND("+",$G2),IFERROR(FIND("(",$G2),0))),"$",""))
Формула была составлена так, как показано, по следующим причинам:
- Функции НАЙТИ/ПОИСК возвращают ошибку, если строка «+» или «(» в моем случае не найдена. Следовательно, в произвольном порядке я сначала пытаюсь увидеть, присутствует ли «+», если не тогда пытайтесь найти "(", и если ни один из них не найден, я доверяю данным, предполагая, что необработанные данные представляют собой стандартную цену.
- Поскольку мне нужна только фактическая цена, мне нужно все, что находится до «+» или «(», поэтому я извлекаю часть строки данных с помощью функции LEFT, вычитая из длины строки позицию «+» " или "(" или 0, если ни один из них не найден.
- Чтобы свести к минимуму любое неправильное толкование, я также удаляю все символы «$».
Дополнительные возможности включают в себя:
- Использование TRIM для удаления висящих пробелов в данных после выполнения вышеизложенного, но результат был таким же, как и в более простой формуле, показанной выше. Это неудивительно, поскольку функция NUMBERVALUE сама по себе игнорирует пробелы.
- Переключение порядка поиска «(» перед поиском «+», но это также не изменило результат в столбце H.
- Я даже протестировал отдельные части своей формулы, чтобы проверить результаты на каждом этапе. Проблема возникает при оценке функции NUMBERVALUE.
Вот фрагмент моего листа Excel:
Учитывая то, что я делаю, и видя поверхностное несоответствие результатов ошибки #VALUE, вот дополнительная информация об ошибке, которую мне сообщает Excel и которую я смог различить.
- Когда я навожу курсор на поле ошибки, мне сообщается, что «Значение, используемое в формуле, имеет неверный тип данных».
- Эту ошибку вызывают необработанные данные о ценах, в которых в скобках указаны поправки на отклонения.
- Если вы посмотрите на ячейку K64, вы увидите, что она отображает желаемый результат, хотя соответствующее значение столбца H не отображается. Следует отметить, что в формуле К64 используется длина ячейки К60, что меня немного озадачило.
- Я нашел решение: в соответствии с тем, что я поделился в приведенном выше пункте, я попробовал поиграть с длиной вывода функции НАЙТИ при поиске «(» и добавить 1 к этому начальному местоположению, что сокращает количество символов, которые я извлекаю из Другими словами, я явно избегаю захвата предыдущего пустого места перед открывающей скобкой, которая на самом деле может даже не быть настоящим пустым пространством — это какой-то специальный встроенный символ с веб-страницы.
- Частичное прозрение: я понял, что одной из проблем, которые я «просчитал», было количество символов, которые я извлекал с помощью LEFT. Круглые скобки удлиняют строку цены на один символ, что, в свою очередь, приводит к тому, что моя операция LEN()-FIND() извлекает дополнительное «пространство» для записей с поправкой на отклонение, содержащейся в скобках. Конечно, это все еще не объясняет, почему это может вызвать проблему, если только это пространство не было каким-то странным образом встроено.
Если кто-нибудь может сказать мне, как я могу выполнить простую и быструю надежную проверку данных, будь то после вставки в Excel или с самой веб-страницы, чтобы я мог определить, присутствует ли неясный специальный символ, я был бы признателен. Я знаю, что есть простой код VBE, который я могу набрать, но я ищу более быстрый и простой подход.
Спасибо!