У меня есть таблица со списком растений, количествами и ценами. Почему-то большинство цен имеют начальные пробелы, которые, как я предполагаю, вызывают ошибку #VALUE, когда я пытаюсь применить множитель наценки.
Я пробовал Trim, Trim(Clean), копировать/вставить специально, найти/заменить, но ничего не помогает — в конце у меня все еще есть числа с ведущими пробелами.
Я даже экспортировал в CSV, а затем импортировал обратно в Excel, думая, что какое-то форматирование вызывает проблемы.
Что за фигня? А есть ли способ опубликовать часть своего листа для проверки?
решение1
В ваших числах есть разные типы пробелов (неразрывные пробелы). Обычно это CHAR(160). Ни TRIM, ни CLEAN не удалят их, поскольку Excel видит их как допустимые символы. Но это делает текстовую строку "числа" и, как таковую, не может умножаться.
Поэтому мы используем SUBSTITUTE, чтобы удалить эти символы, и добавляем TRIM и CLEAN для ровного счета:
=TRIM(CLEAN(SUBSTITUTE(C2,CHAR(160),"")))*2.5
решение2
Вы можете столкнуться с кучей "неразрывные пробелы", которые не обрабатываются CLEAN(). Это очень распространено при извлечении данных с веб-страниц, поскольку они, как и PDF-файлы, касаются только визуального представления, а не сбора данных для дальнейшего использования. Но это происходит в большом количестве из программ, выводящих данные, особенно когда они не отображают общее использование данных, а скорее ограниченный круг людей, использующих их. Или отображали 28 лет назад, когда была написана функциональность вывода CSV, которая никогда не обновлялась.
Эту задачу проще решить в электронной таблице, поскольку вам нужна только числовая часть без всего, что не является числовой цифрой.
Если вам нужно сохранить индикаторы отрицательного значения, это будет сложнее. Или символы, которые были чем угодно, кроме этих пробелов. Или, как я, получить строки, которые требуют вывода в виде дискретных чисел. В строке типа "90.02 45.55 .062", но сохраненные, чтобы их можно было извлечь.
Однако вам просто нужны цифры в одной строке и рассматриваемые числа в Excel. Следующая формула делает это:
=VALUE(TEXTJOIN(,TRUE,IFERROR(MID(C1,SEQUENCE(1,LEN(C1)),1)*1,"")))
Он используется SEQUENCE()для получения ряда значений, которые увеличиваются на 1 и имеют такую же длину, как и длина вашего целевого объекта. Затем он использует их для указания начальной точки для функции, MID()которая выводит матрицу символов в целевом объекте. Затем он выполняет математическую операцию ( *1) над этим, которая работает для цифр в матрице, но выдает ошибки для всего, что не является цифрой (0-9). IFERROR()преобразует эту строку в цифры с "" везде, где возникли ошибки (где в целевом объекте НЕ было цифры). TEXTJOIN()затем преобразует эту матрицу в простую выходную строку, игнорируя эти элементы "", так что теперь у вас есть полезная строка только из цифр, которые существовали в целевом объекте. Она рассматривается как текст и будет отображаться так.
На данный момент эта формула полезна для вас, если у вас достаточно свежая версия Excel. Это потому, что Excel распознает строку цифр, даже если она отформатирована как текст (они только отформатированы, а не изменены каким-то фундаментальным образом), как именно цифры, если она представлена функции, которая ТРЕБУЕТ или обоснованно ожидает только числовых данных в качестве входных данных. Так что даже "C1+1" будет считать этот текст числом и выполнит сложение, а не выдаст ошибку или 0+1=1.ОДНАКО,не все функции будут обрабатывать вывод таким образом. VLOOKUP()будет обрабатывать его как то, чем он кажется, текст в данном случае, и просмотр таблицы чисел не находит совпадений, выдавая вам ошибку. Поэтому я обернул выше VALUE()для завершения, чтобы результат считался числом, и если вы используете результат в качестве входных данных для функции поиска, как, похоже, мог бы сделать кто-то другой, это сработает.
Таким образом, ВЫ можете удалить эту функцию из него, и он все равно будет выполнять умножение разметки так, как нужно.
Однако если у вас нет новых SPILLфункций или, особенно, если у вас довольно старая версия Excel, следующая формула будет работать практически во всех версиях Excel для Windows и всегда будет выдавать числовой результат:
{=ROUND(NPV(-0.9,0,IFERROR(MID(L1,LEN(C1)-ROW(INDIRECT("C1:I"&LEN(C1)))+1,1)/100,"")),3)}
Обратите внимание, что этоЕГЭформула. При использовании в качестве обычной формулы она иногда отбрасывает последние две цифры... и вывод форматируется как денежная единица. (Excel полезен... потому что NPV()это финансовая функция, и вы ДОЛЖНЫ хотеть, чтобы она была отформатирована как денежная единица, верно?)
Вышеприведенная формула взята "где-то в интернете когда-то довольно давно"... Хотелось бы указать источник, но я его не помню. Приношу извинения некоторым полезным гуру.
Рад, что вы спросили об этом, потому что у меня это есть во многих местах и мне нужно было обновить, чтобы SEQUENCE()узнать, как можно помочь. Не говоря уже о замене изменения внутренней матрицы в функции на полезную строку, которая выводится, TEXTJOIN()поскольку это теперь тоже доступно! Немедленная выгода! Это замечательная вещь!