Статистика в Excel, включая измененные текстовые значения

221
martin

У меня есть таблица в Excel (2013), которая выглядит примерно так:

V1 V2 V3 V4 2.1 300 40.5 6.89 6.9 204 <0.02 <0.001 <0.3 96 6.5 1.2

Для каждого из столбцов с V1 по V4 я хотел бы сделать некоторые основные статистические данные, такие как среднее, максимальное и минимальное значения.

Однако я также хотел бы включить значения с <символом. Но эти ячейки интерпретируются как текст и поэтому исключаются из формул.

Как я могу рассчитать это без необходимости вручную / полуавтоматически удалить <символ? Мне также нравится избегать копирования всего на новый лист, так как это может привести к ошибкам при вводе новых значений.

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

0
Неравенства не являются числами, поэтому вы не можете включить их в расчеты. Кроме того, их значение не эквивалентно значению, исключая "<". fixer1234 7 лет назад 0
Первое, что я понял, но искал способ использовать значение после `<`. Если это невозможно, я думаю, мне нужно создать второй лист, который будет копировать все из первого, кроме этих символов, а затем делать там свою статистику. Непрактично, но если это единственный способ, я возьму это. Я знаю, что это не эквивалентно только одному значению, но в данном случае это разумное приближение, и оно намного лучше, чем просто полное исключение значений. martin 7 лет назад 0
Вы можете сделать что-то вроде замены ссылки на ячейку формулой; скажем для A2: = IF (LEFT (A2,1) = "<", VALUE (MID (A2,2, LEN (A2) -1)), A2) Если вы хотите применить статистику к диапазонам, создайте вспомогательные столбцы с очищенные ценности, а затем работать с этим. fixer1234 7 лет назад 0
Имея около 15 столбцов и несколько сотен строк данных, я надеялся избежать этого, тем более что эти значения будут обновляться вручную довольно часто, а строки будут добавляться и удаляться. Думаю, на этот раз я слишком много просил :) В любом случае, спасибо! martin 7 лет назад 0
Введите формулу в первой ячейке, а затем скопируйте или перетащите ее, чтобы заполнить столько строк и столбцов, сколько вам нужно. Если вы поместите его в тест для пустой исходной ячейки и используете NA (), вы можете предварительно заполнить произвольно большой диапазон ячеек для обработки добавлений и удалений в исходных данных; пустые исходные ячейки приведут к значению «недоступно», которое не повлияет на ваши расчеты статистики. fixer1234 7 лет назад 0

1 ответ на вопрос

2
Scott Craner

To do this without helpers use --SUBSTITUTE() as the array:

--SUBSTITUTE($A$2:$D$4,"<","")

So to get the average:

=AVERAGE(--SUBSTITUTE($A$2:$D$4,"<",""))

To get the Median:

=MEDIAN(--SUBSTITUTE($A$2:$D$4,"<",""))

By doing this the formulas need to be entered as array formulas. To do so After typing the formula hit Ctrl-Shift-Enter instead of Enter, Tab, or clicking out the cell. If done properly Excel will put {} around the formula.

enter image description here

Спасибо! Это именно то, что я искал! Однако, поскольку у меня было несколько пустых ячеек между ними, мне пришлось использовать это в конце концов: `= AVERAGE (IF ($ A $ 2: $ D $ 4 <>" ", (- ЗАМЕНА ($ A $ 2: $ D $ 4 , "<", ""))) ` martin 7 лет назад 0

Похожие вопросы