EXCEL_Как преобразовать текст в число

Я работаю над отчетом о качестве и импортирую данные в таблицу. Но все числа зарезервированы как текст, в отдельных ячейках в одном столбце.

                        | Average
colour | red   | red    | 
weight | 1.6   | 1.56   | 1.58
length | 1.1   | 1.12   | 1.11
type   | A     | A      | A
colour | green | green  | 
weight | 1.4   | 1.5    | 1.45
length | 10    | 9.6    | 9.8
type   | B     | B

И я бы хотел посчитать их средние значения для одной строки. Вы можете оставить colour и type в стороне, я использовал функцию IF, чтобы определить, является ли тест этими двумя, тогда я сделаю другие вещи с результатом, и если тест нет (это означает, что результат должен быть числом) , Я хочу рассчитать среднее значение.

Но встречу ошибку DIV/0 из-за этой проблемы. Извините, что я не нашел кода для формулы или в VBA для завершения этой задачи, поэтому, если у вас есть какие-то идеи, оставьте комментарий, спасибо.


excel excel-formula vba worksheet-function
person Hiddenllyy    schedule 29.07.2016    source источник
comment
Вы пытались изменить формат ячеек в листе Excel с текста на число?   -  person The Shooter    schedule 29.07.2016
comment
Вы можете использовать CInt для преобразования вашего числа, хранящегося в виде строки, но в случаях, когда есть строка, вам также потребуется некоторая обработка ошибок.   -  person Shai Rado    schedule 29.07.2016
comment
Да, но не работает. number находится справа от ячейки, а text - слева. И ячейка средних значений по-прежнему ошибочна.   -  person Hiddenllyy    schedule 29.07.2016
comment
Обновите свой вопрос образцами данных в таблице Excel.   -  person The Shooter    schedule 29.07.2016
comment
@Hiddenllyy - это предоставленные вами образцы данных в 2 столбцах? или 1? какие средние значения вы пытаетесь вычислить? масса ? длина ?   -  person Shai Rado    schedule 29.07.2016
comment
Предположим, что первый красный - это столбец B, второй красный - столбец C, а строка 2 содержит значение 1,6, а столбец D содержит средние значения, тогда ввод формулы = СРЕДНЕЕ (B2: C2) в D2 должен дать вам ответ, а затем вы можете скопировать это в другие ячейки. .   -  person The Shooter    schedule 29.07.2016
comment
ааа хрень пример данные изменились!   -  person Forward Ed    schedule 29.07.2016
comment
Спасибо @TheShooter Но моя проблема в том, что AVERAGE не работает, потому что числа хранятся как text. Итак, я ищу способ заменить их на number, а затем произвести вычисления.   -  person Hiddenllyy    schedule 29.07.2016

Ответы (3)


arrow_upward
1
arrow_downward

Предположим, ваш массив A1: A10 содержит смесь текста и чисел, хранящихся в виде текста. Вы можете взять среднее числовых значений следующим образом:

=AVERAGE(IFERROR(VALUE(A1:A10),""))

Это формула массива, поэтому ее нужно будет ввести с помощью Ctrl + Shift + Enter.

Важно: пустые ячейки будут обрабатываться как нули! Если вы хотите игнорировать пробелы, вы можете добавить для этого обработку.

=AVERAGE(IFERROR(IF(ISBLANK(A1:A10),"",VALUE(A1:A10)),""))

Изменить: в вашем примере похоже, что вы можете усреднить определенные строки. В этом случае посмотрите на функцию AVERAGEIF.

Я также не могу сказать, находятся ли ваши данные в одном или двух столбцах. Если он в одном, вам сначала нужно использовать текст в столбцы с "|" в качестве разделителя, чтобы разделить его на два.

person Alexis Olson    schedule 29.07.2016
comment
Спасибо, похоже, ваш код должен работать, но все равно ошибка становится value error. Раньше я просто использовал AVERAGE(A1:A10), и когда я меняю числа на number, он может вычислять без ошибок, но с вашей новой формулой он все равно не работает. Это действительно странно. - person Hiddenllyy; 29.07.2016
comment
Вы уверены, что видите {} вокруг формулы после ее ввода? - person Forward Ed; 29.07.2016
comment
Хорошо! Я нашел ошибку, спасибо вам обоим! - person Hiddenllyy; 29.07.2016

arrow_upward
2
arrow_downward

Следуя за Алексисом Олсоном, прокомментируйте, как вы усредняете свои числа. Если это всего лишь образец данных, а ваши реальные данные представляют собой комбинацию текста и чисел, придерживайтесь ответа Алексиса Олсона.

Однако, если вы хотите усреднить только вес, поскольку усреднение веса и длины не имеет смысла с точки зрения единиц измерения, вы можете попробовать:

=SUMPRODUCT(($B$4:$B$11=E$4)*IFERROR(VALUE($C$4:$C$11),0))/COUNTIF($B$4:$B$11,E$4)

or

=SUM(($B$4:$B$11=E$4)*IFERROR(VALUE($C$4:$C$11),0))/COUNTIF($B$4:$B$11,E$4)

AVERAGEIF не совсем сработал, потому что третий аргумент запрашивает диапазон для усреднения, и мы упускаем возможность преобразовать ваши текстовые числа в фактические числа.

Обе приведенные выше формулы являются формулами CSE или ARRAY, что означает, что вам нужно использовать CONTROL + SHIFT + ENTER при завершении ввода формулы. После этого ваша формула в строке формул будет заключена в правые скобки {}. Обратите внимание, что {} нельзя добавить вручную для всей формулы.

Доказательство концепции

POC

ОБНОВИТЬ#

ПОСЛЕ ИЗМЕНЕНИЯ ДАННЫХ В ПРИМЕРЕ

=AVERAGE(VALUE(C9:D9))

Это формула массива с одной ячейкой. Поместите его в первую ячейку, в которой вы хотите получить среднее значение, и обязательно используйте CONTROL + SHIFT + ENTER при вводе формулы и найдите { } вокруг формулы после того, как вы ее ввели. Затем вы можете перетащить формулу вниз или скопировать ее в отдельные ячейки, где вы хотите.

Доказательство концепции II

POC II

person Forward Ed    schedule 29.07.2016
comment
Большое спасибо за ваше терпение, и извините, что я изменил пример наполовину! Я протестировал ваш код, и он работает хорошо, но мне все еще нужно выбрать ответ, и мистер Алексис идет первым, так что ... Хотя спасибо !! Хорошего дня:) - person Hiddenllyy; 29.07.2016

arrow_upward
1
arrow_downward

Я ищу способ превратить их в числа, а затем вычислить

Чтобы избежать недостатков формул массива, было бы намного проще использовать решение @Alexis Olson или @Forward Ed в форме без массива, но после выбора диапазона, содержащего зеленые треугольники, и нажатия на «Преобразовать в число "в" верхнем левом зеленом треугольнике.

Если зеленые треугольники не отображаются, либо включите проверку ошибок («Параметры»> «Формулы»), либо вставьте 0 в свободную ячейку, скопируйте эту ячейку, выберите подходящий диапазон и «Специальная вставка», «Добавить».

person pnuts    schedule 29.07.2016
comment
после преобразования в числа просто используйте среднее значение, если оно доступно - person Forward Ed; 29.07.2016
comment
предпочтительнее не просто среднее, я вспомнил первый набор данных, которые нам представили. Я не знал о фокусе с зеленым треугольником. Рад, что вы написали об этом. - person Forward Ed; 29.07.2016