Среднее из 10 лучших значений в строке, обрабатывающих пустые ячейки как единицы

Предположим, что у меня есть строка в excel, состоящая из n ячеек (фактически n=12 ). Некоторые из этих ячеек имеют числовые значения, некоторые могут быть пустыми. Я хочу найти среднее число верхних m ячеек в строке (фактически m=10 ), обрабатывая пустые ячейки, как если бы они содержали 1s.

(Контекст для этого состоит в том, что я хочу вычислить средние оценки некоторых учеников, чья домашняя работа I класса, и это формула, используемая в курсе. Пустые ячейки соответствуют домашним заданиям, которые не были сданы, но я хотел бы сохранить различие между домашними заданиями, оцененными 1 и отсутствующими.)

На основе данных в A1:L1 , формулы массива ** :

=AVERAGE(LARGE(IF(ISNUMBER(A1:L1),A1:L1,1),{1,2,3,4,5,6,7,8,9,10}))

С уважением

** Формулы массива не вводятся так же, как «стандартные» формулы. Вместо нажатия только ENTER, сначала удерживайте CTRL и SHIFT, а затем нажмите ENTER. Если вы сделали это правильно, вы заметите, что Excel помещает фигурные скобки {} вокруг формулы (хотя не пытайтесь вручную вставить их самостоятельно).

Поместите 12 предметов в ячейки B1 через M1 . В B2 введите:

 =IF(B1="",1,B1) 

и копировать. В A1 введите:

 =LARGE($B$2:$M$2,ROWS($1:1)) 

и копировать. Теперь, чтобы получить среднее значение из 4 лучших классов, просто выберите ячейку и используйте:

 =AVERAGE(A1:A4) 

введите описание изображения здесь

Чтобы получить верхнюю 5, используйте =AVERAGE(A1:A5) и т. Д.

Ну, это может быть сделано немного менее сложно, чем массивы, просто разбивая среднее на его части, сумму и деление.

 =(sum(B2:K2)+countif(B2:K2,0))/10 

Это для горизонтальных значений, начинающихся в строке B2. Вы суммируете баллы, затем подсчитываете нули (это то, что вы хотите добавить к сумме). Затем разделим на m.

  • Определение максимального расстояния двух точек в списке
  • Преобразование формулы sumproduct в общую формулу, которая может использоваться на всех листах в книге
  • Функция Excel Sumproduct OR
  • Как заполнять ячейки при сопоставлении данных
  • Как добавить требуемое число дней до даты, считайте счет в Excel?
  • Отобразить адрес последней непустой ячейки (если значения не уникальны)
  • excel усредняет каждые 10 строк
  • Как динамически изменять диапазон диаграмм в раскрывающемся списке в Excel?
  • Возврат «Нет данных», если ячейка пуста, есть «0» или «Н / Д»,
  • SQL Как формулы в excel
  • Создайте формулу в Excel, нужна помощь
  • Interesting Posts

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

    Удалить и перезагрузить «Добавить» из Excel (онлайн и настольное приложение)

    Скребок данных из PDF и Excel

    Сделать только один флажок в строке?

    VLOOKUP – проблема поиска и возврата значений

    Как ссылаться и обновлять QueryTable в Excel 2016 в VBA

    Powershell – скрипт зависает после чтения первого листа Excel

    Excel 2003, который сохраняет имя файла на основе значения ячейки

    Сумма всех значений в одном столбце с учетом двух других

    Преобразование таблицы Excel в массив 2d

    разделить таблицу / файл excel на несколько файлов csv с кодировкой UTF-8

    VBA для захвата ошибок в отдельном экземпляре excel

    Не удается удалить модули кода из рабочей книги назначения через VBA

    Отклонить Msgbox от внешнего макроса

    Как напечатать выходное значение столбца путем фиксации конкретного соответствующего опорного значения в другом столбце

    Давайте будем гением компьютера.