У меня есть лист Excel, содержащий около 6000 продуктов. В столбце Количество есть некоторые значения, заканчивающиеся на "60шт", а некоторые другие на "7pkts".
Мне нужно взять общее количество в конце, и я не могу этого сделать из-за этих букв, смешанных с цифрами. Как рассчитать общую сумму, если значения содержат текст?
Это менее общее, чем решение @Andi Mohr; он предполагает, что у вас есть только эти единичные меры, обе начинаются с "p". Единственным преимуществом является то, что вы получите окончательный результат с одной формулой.
=SUM(NUMBERVALUE(MID(A1:A6000,SEARCH("p",A1:A6000)-1)))
При необходимости измените ссылки на столбцы.
Это должно быть подтверждено нажатием Ctrl+Shift+Enter.
Эта довольно блестящая формула сделает работу за вас.
Если ваша комбинация чисел и текста находится в ячейке A1, введите ее в следующую ячейку, нажав Ctrl+Shift+Enter:
=NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))
Затем вы можете сложить этот столбец, чтобы рассчитать общую сумму.
Эта формула была первоначально опубликована в Группах Google кем-то по имени Лори - я читал об этом в ветке форума Chandoo из сообщения Sajan. Как это устроено:
Магия NPV - это формула расчета NPV, где каждый член умножается на обратное значение (1+ коэффициент)^ n, где n - это n-й член в ряду. например (1+ скорость)^ 1, (1+ скорость)^ 2 и т. д. Используя разные значения для скорости, мы можем получить разные результаты. В этом случае использование -0,9 дает нам 1+ коэффициент = 1+ -0,9 = 0,1. Таким образом, мы получаем такие значения, как {0,1; 0,01; 0,001; 0,0001; 0,00001}. Если принять обратное, мы получим {10; 100; 1000; 10000; 100000} и т.д. В сочетании с тем, что NPV пропускает текстовые значения, мы получаем желаемые результаты.
Изменить: Улучшенная скорость расчета
Máté Juhász предложил дополнение, чтобы ускорить формулу, если применить к большому диапазону. Он проверяет, является ли строка первым числом, чтобы сэкономить время Excel, вычисляя то, что ему не нужно.
=IF(ISNUMBER(A1),A1,NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,"")))