#excel #worksheet-function
#excel #рабочий лист-функция
Вопрос:
У меня есть 4 столбца со значениями. Я хочу суммировать лучшее из трех из них. Какую формулу я должен написать?
Если значение в любом из столбцов пустое или не является числом, его значение для целей вычисления должно по умолчанию равняться 0.
Примеры:
| 4| 2| 1| 3| (2,3,4 are best, sum = 9)
| 4| 1|AB| 5| (1,4,5 are best, sum = 10)
|AB|AB| 6| 7| (0,6,7 are best, sum = 13)
| 1|AB| | | (0,0,1 are best, sum = 1)
Как мне сделать так, чтобы в нечисловом поле по умолчанию использовалось числовое значение для вычислений?
Как мне суммировать только лучшие 3 из четырех столбцов?
Комментарии:
1. не проголосовал против, но, возможно, может понять, почему это было, заголовок вопроса, на самом деле не соответствует проблеме, и фактическая проблема становится ясной только в комментариях. Теперь, когда у вас есть свой ответ, вы можете отредактировать свой вопрос, чтобы он отражал всю проблему (возможные значения ‘AB’ и т. Д.)
Ответ №1:
Ответ, который учитывает возможные нечисловые значения в ячейках (пустой также не является числом)
=SUM(A1:D1)-MIN(IF(ISNUMBER(A1),A1,0),IF(ISNUMBER(B1),B1,0),IF(ISNUMBER(C1),C1,0), IF(ISNUMBER(D1),D1,0))
ISNUMBER(_cell_) возвращает true для чисел, но false для всего остального.
Ответ, который учитывает пустые ячейки:
=SUM(A1:D1)-MIN(IF(ISBLANK(A1),0,A1),IF(ISBLANK(B1),0,B1),IF(ISBLANK(C1),0,C1),IF(ISBLANK(D1),0,D1))
ISBLANK(_cell_) может использоваться для проверки наличия значения в ячейке, IF(bool, value_if_true, value_if_false) может использоваться для выбора между двумя значениями в зависимости от логического значения.
Оригинальный ответ: это не переносимо, поскольку мне нужны наилучшие поля X из Y, но лучше всего Y-1 из Y
=SUM(A1:D1)-MIN(A1:D1)
где ваши значения находятся в столбцах A, B, C, D
Комментарии:
1. Это не сработает, если значение фактически не введено (пусто ! = 0)
2. Похоже, это тот же ответ, который дает Винни.
3. Мои ячейки не пустые, но они имеют значение ‘AB’.
4. @Himadri, когда я ввел ответ, ответов еще не было. Не заметил новых ответов до отправки. Обновленный для работы с пустыми ячейками, ответ Матино легче читать, менее подвержен ошибкам и более версифицирован.
5. @Himadri Обновлен версией, которая обрабатывает нечисловые значения как 0.
Ответ №2:
Это быстрое решение, если у вас всего четыре значения и вы хотите удалить наименьшее. Ваш диапазон от A1 до A4. Суммируйте их все и вычтите наименьшее значение группы.
=SUM(A1:A4)-MIN(A1:A4)
Это позволит суммировать значения, в которых ячейки не являются «AB»
=SUMIF(C1:C4,"<>AB")-MIN(C1:C4)
Если вы хотите суммировать все значения, кроме отсутствующих студентов («AB»), затем удалите вычитаемое значение.
=SUMIF(C1:C4,"<>AB")
C1 = 2.5
C2 = AB
C3 = AB
C4 = 3
SUMIF = 5.5
Эта формула просматривает каждую ячейку, присваивает 0, если значение равно «AB», затем вычитает наименьшее значение из этого диапазона.
=SUM(IF(C1="AB",0,C1),IF(C2="AB",0,C2),IF(C3="AB",0,C3),(IF(C4="AB",0,C4)))-MIN(IF(C1="AB",0,C1),IF(C2="AB",0,C2),IF(C3="AB",0,C3),(IF(C4="AB",0,C4)))
Комментарии:
1. Предположим, что значения в 4 столбцах равны 2.5, AB, AB, 3, тогда ответ должен быть 5.5, но он показывает 3. Я должен написать AB, если студент отсутствует.
2. Добавлено что-то, чтобы убрать минимальное значение. Дайте мне знать, если это сработает для вас.
3. Это работает, если вы хотите вычесть наименьшее значение. Я обновил ответ только для значений суммы, которые не являются «AB»
4. Простая сумма подходит только для функции SUM. Я хочу вычесть минимум из этих четырех значений, в чем проблема.
5. То, что я написал, не очень красиво, но оно работает для того, что вы просите. Mr.Mindor’s проведет аналогичное сравнение, используя функцию ISNUMBER() , которую вы также можете использовать здесь.
Ответ №3:
=SUM(LARGE(A1:A4,{1,2,3}))
Где A1: A4 — диапазон первой строки
Комментарии:
1. AB не является числовым значением, которое ожидает БОЛЬШАЯ функция. Вам нужно либо подставить туда значение, либо использовать решение Mr.Mindors. Мой пост просто берет минимальное фактическое число и вычитает его.
2. если вы используете для этого свои 2.5, AB, AB, 4, то LARGE все равно пытается захватить первые три числа, но их всего два. так что #NUM! вставляется как третье…