У меня есть столбцы лабораторных аналитических данных в Excel, некоторые ячейки содержат символ «<», а некоторые нет. Мне нужна формула, которая говорит Excel, чтобы усреднить столбцы, но использовать 1/2 значение для ячеек, содержащих символ <. Есть идеи? введите описание изображения здесь
3 ответа
3
Если вы хотите обойтись без вспомогательного столбца, следующая формула даст вам среднее значение для столбца A:
=SUMPRODUCT(--SUBSTITUTE(A1:A12,"<","")*(1/2+1/2*(LEFT(A1:A12,1)<>"<")))/COUNTA(A1:A12)
1
Во-первых, вот формула для нормализации значений, потому что любое значение, начинающееся с '<', будет считаться строковым значением, которое Excel не может усреднить или выполнить любую другую математическую операцию.
Создайте целевой диапазон с тем же числом строк и столбцов, что и диапазон исходных данных; ваш пример имеет матрицу 12 на 4 столбца, поэтому пункт назначения также будет 12x4. Поместите эту формулу в каждую ячейку целевого диапазона, чтобы эта операция выполнялась для каждой ячейки целевого диапазона, от A1 до D12:
= ЕСЛИ (НОМЕР (ПОИСК ("<", А1)), ПРАВО (А1, ЛЕН (А1)-1)/2, А1)
будет смотреть на ячейку А1.
Если его значение начинается с «<», то оно удалит начальный «<» и разделит оставшееся числовое значение на 2.
Если его значение не начинается с '<', эта формула вернет свое первоначальное значение.
Если у вас есть матрица исключительно числовых значений, то под строкой 12 усредните каждый столбец с помощью = AVERAGE(X 1:X 12), где X - обозначение столбца. Вот скриншот результата:
0
Это немного круто и предполагает, что вы не возражаете против дополнительного листа в вашей рабочей тетради. Если ваш стол в A1:D4 на Sheet1
На Sheet2 используйте следующую формулу:
=IF(LEFT(Sheet1!A1,1)="<",RIGHT(Sheet1!A1,LEN(Sheet1!A1)-1)/2,Sheet1!A1)
Затем скопируйте вниз и поперек, чтобы соответствовать размерам таблицы в Sheet1 .
Вернитесь на Sheet1 , используйте =AVERAGE(Sheet2!A1:A12) и скопируйте для каждого столбца
(Sheet2 может быть скрыт при необходимости, это не повлияет на формулы)