Как выбрать первый номер в ячейке в Excel?
У меня есть таблица, в которой хранится как число, так и некоторый текст в ячейке, и я хочу сделать арифметическую операцию на основе чисел. Итак, моя таблица выглядит так:
Number1 | Number2 | Product
2 | 3 | 6
3 (some text) | 40 | 120
4 | 5 (here too) | 20
Таким образом, при наличии этих уродливых текстов, я все еще хочу иметь возможность производить колонку 3 автоматически. Я думаю, мне нужна функция, которая анализирует подстроку до первого нечислового символа в число. Могу ли я сделать это с помощью Excel?
PS: номера могут иметь более 1 цифры. Можно с уверенностью предположить, что они являются целыми числами, но было бы неплохо увидеть решение, которое также применимо к плавающим точкам:)
Спасибо,
2 ответа
To get the numbers if they are in the beginning of the string we can use this:
=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))
as our base formula, This will find the end of the number and return that as the end of the MID() Function.
There is a lot going on here:
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},""))) Поскольку эта часть перебирает числа, она заменяет последний экземпляр каждого числа на }}},
Третий критерий ЗАМЕНЫ - это экземпляр. Мы находим количество экземпляров с LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")), Он перебирает числа и заменяет каждое из них ничем. Затем он находит разницу в длине исходной строки и новой.
Таким образом, в случае A2, когда он повторяется к 2 он находит 2, а внешний заменитель заменяет последний на }}}, Это просто временный заполнитель.
Агрегатная функция - это многофункциональная функция. 14 говорит о функциях, которые мы используем Large() функция. 6 говорит функции игнорировать ошибки. Это важно, потому что многие из итераций не найдут ничего и вернут ошибку.
С 1 в конце он сообщает функции, что мы хотим получить максимальную отдачу от функции поиска, которая ищет эти временные }}} которые размещаются путем итерации в последнем экземпляре каждого числа.
Таким образом, Агрегат возвращает максимальное найденное число. Который мы переходим к критерию длины в функции 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))
Одно предупреждение Функция агрегирования была введена в 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},"")))))))
Он примерно такой же, как и приведенный выше, он должен сначала проверить наличие ошибок, прежде чем найти макс.
Предполагая, что число всегда отделяется от любого текста пробелом, вы можете использовать что-то вроде этого. Скажем, в вашем примере данные начинаются с A2, поэтому ваш первый результат в C2 обычно будет выглядеть так:
=A2*B2
Нам нужно заменить простые ссылки на ячейки формулой, которая рассматривает исходную ячейку как число, если это только число, но извлекает число, если есть также текст. Таким образом, C2 станет:
=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))
Если исходная ячейка не является числом, она находит первый пробел и возвращает символы перед ним. Технически, он возвращает текстовое значение, содержащее цифры, но Excel достаточно умен, чтобы рассматривать его как число при расчете продукта (нет необходимости вкладывать его в другую функцию, чтобы преобразовать его в число). И обратите внимание, что если число является плавающей точкой, это все равно будет работать.
Предостережение: это основывается на "чистых" исходных данных, т. Е. Либо на числовой записи, либо на текстовой записи, где она начинается с числа, за которым следует пробел. Если есть ячейка, которая не соответствует этому, вам нужно либо очистить данные, либо включить некоторую форму проверки ошибок. Лучшим решением было бы разделить два столбца данных так, чтобы любой текст находился в отдельной ячейке.