У меня есть таблица, которая хранит в ячейке и число, и текст, и я хочу выполнить арифметическую операцию на основе чисел. Поэтому моя таблица выглядит так:
Number1 | Number2 | Product
2 | 3 | 6
3 (some text) | 40 | 120
4 | 5 (here too) | 20
Итак, при наличии этих уродливых текстов я все равно хочу иметь возможность автоматически выводить столбец 3. Думаю, мне нужна функция, которая разбирает подстроку до первого нечислового символа в число. Могу ли я сделать это с помощью Excel?
P.S.:Числа могут иметь более 1 цифры. Можно смело предположить, что это целые числа, но было бы неплохо увидеть решение, применимое и к числам с плавающей точкой :)
Спасибо,
решение1
Чтобы получить числа, если они находятся в начале строки, мы можем использовать это:
=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))
в качестве нашей базовой формулы. Она найдет конец числа и вернет его как конец функции MID().
Здесь много всего происходит:
SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))Поскольку эта часть перебирает числа, она заменяет последнее вхождение каждого числа на }}}.
Третий критерий SUBSTITUTE — это экземпляр. Мы находим количество экземпляров с LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")). Он перебирает числа и заменяет каждое из них ничем. Затем он находит разницу в длине исходной строки и новой.
Так что в случае A2, когда он переходит к , 2он находит 2, а внешний Substitute заменяет последний на }}}. Это всего лишь временный заполнитель.
Функция Aggregate — это многофункциональная функция. 14 сообщает функциям, что мы используем Large()функцию. 6Сообщает функции игнорировать ошибки. Это важно, так как многие итерации ничего не найдут и вернут ошибку.
Символ 1в конце сообщает функции, что мы хотим получить наибольший возврат от функции поиска, которая ищет те временные элементы, }}}которые помещаются посредством итерации в последний экземпляр каждого числа.
Итак, Aggregate возвращает максимальное найденное число, которое мы передаем критерию длины в функции Mid.
Итак, теперь мы нашли число в начале строки.
Итак, мы можем перемножить два из них, чтобы получить желаемый результат (любая математическая функция превратит возвращаемую строку в число):
=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))),1))
Одно предостережениеФункция Aggregate появилась в Excel 2010. Она может не работать в более старых версиях.
Если у вас старая версия, вам придется использовать эту длинную формулу:
=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))))))
Он делает примерно то же самое, что и предыдущий, за исключением того, что сначала необходимо проверить наличие ошибок, прежде чем находить максимум.
решение2
Предполагая, что число всегда отделено от текста пробелом, вы можете использовать что-то вроде этого. Допустим, ваш пример содержит данные, начинающиеся в A2, поэтому ваш первый результат в C2 обычно будет выглядеть так:
=A2*B2
Нам нужно заменить простые ссылки на ячейки формулой, которая обрабатывает исходную ячейку как число, если это только число, но извлекает число, если есть также текст. Таким образом, C2 станет:
=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))
Если исходная ячейка не является числом, она находит первый пробел и возвращает символы перед ним. Технически, то, что она возвращает, является текстовым значением, содержащим цифры, но Excel достаточно умен, чтобы рассматривать его как число при вычислении произведения (нет необходимости вкладывать это в другую функцию, чтобы преобразовать его в число). И обратите внимание, что если число является числом с плавающей точкой, это все равно будет работать.
Предостережение: это основано на «чистых» исходных данных, т. е. либо числовой записи, либо текстовой записи, которая начинается с числа, за которым следует пробел. Если есть ячейка, которая не соответствует этому, вам нужно будет либо очистить данные, либо включить какую-то форму проверки ошибок. Лучшим решением будет разделить два столбца данных так, чтобы любой текст находился в отдельной ячейке.