Несколько вопросов по Excel

[Red_Herring]

Закончились (в моих потоках) экзамены, я подал оценки, и опять я был вынужден решать некоторые вопросы, связанные с "вычислениями" ... Поскольку я был Class Coordinator большого курса (600 студентов вначале разделенных на 3 лекционных и 10 туториальных секций), эти вопросы приобрели особую остроту, т.к. manual override занял слишком много времени

1. Квизы. Обычно я даю 7 квизов, но считаю оценки только за 5 лучших. В Excel я их считаю как

=LARGE(F2:L2,1)+LARGE(F2:L2,2)+LARGE(F2:L2,3)+LARGE(F2:L2,4)+LARGE(F2:L2,5)

Проблема в том, что некоторые студенты пишут меньше 5 квизов. Я считаю, что ненаписанный квиз =0, а Excel "undefined". Поэтому, в частности, я не могу использовать

=SUM(F2:L2)-SMALL(F2:L2,1)-SMALL(F2:L2,1)

что даст неправильный ответ, если студент написал меньше 7 квизов. Но если студент написал <5 квизов, то LARGE(F2:L2,5) будет #NAME?, а вместе с ним и вся сумма.
Я лично считал отдельно SUM(F2:L2) и вручную исправлял клетки с #NAME?

Похоже, что есть выход: ввести колонки, соответственно M2=MAX(F2,0), N2=MAX(G2,0),....,S2=MAX(L2,0), которые возвращают "пусто" как "0" (но не #NAME?), и потом уже считать

=SUM(M2:S2)-SMALL(M2:S2,1)-SMALL(M2:S2,1)

Нет ли чего покороче?


2. Ну написал студент 4 квиза, но представил справки от врача, что пропустил 1,2,6 по болезни. У меня есть графа, куда я это занес (Q1,Q2,Q6). Теперь этому студенту сумму квизов надо умножить на 5/4 (поскольку он "по закону" писал только 4, если бы у него были справки на один-два квиза, то коэффициент 1, а если на четыре--то 5/3 и т.д.

(Оффтоп)

Героизм "принес справку, а все равно писал" не наказывается, но и не поощряется. Общий совет сразу "Don't be a freakin hero", начал писать значит все, справка или не справка, разве что из аудитории в реанимацию увезут. Иначе там полпотока запасется справками заранее, если не от врача, то от психолога, специалиста по традиционной китайской медицине, Social Work. Конечно, все это будет отдано на откуп Ms Trash и Mr Shredder, но разбираться придется.

Как это сделать автоматически? Не хотелось бы разводить кучи колонок (например, для каждого квиза отдельно "Q1 dr note"), и не хотелось бы экспортировать в tab-del текст, и прогонять какой нибудь скрипт, и затем обратно в Excel.

3. Ну и совсем дурацкий: если я ввожу в Excel 4/3, то он понимает это как "3 апреля". Как ему объяснить, чтобы не умничал?

[wrest]

Red_Herring в сообщении #1306353 писал(а):

Я считаю, что ненаписанный квиз =0, а Excel "undefined". Поэтому, в частности, я не могу использовать

Тогда, может, скорее будет заменить find/replace-ом пустые ячейки на нули?

[Red_Herring]

wrest в сообщении #1306361 писал(а):

Тогда, может, скорее будет заменить find/replace-ом пустые ячейки на нули?

Можно, только потом не разберешь, писал ли он квиз или не писал. А в некоторых случаях это необходимо. Именно поэтому я предпочитаю создавать лишние колонки, потом считать, потом экспортировать в  текст, потом обратно импортировать, что разрушает формулы, но сохраняет числа, и потом убивать лишние колонки.

[upgrade]

Red_Herring в сообщении #1306353 писал(а):

а Excel "undefined".

...+IFERROR (LARGE(F2:L2,5),0)...

возвращает значение 0, если до запятой итог вычисления ошибка.

-- 22.04.2018, 14:36 --

upgrade в сообщении #1306370 писал(а):

Можно, только потом не разберешь, писал ли он квиз или не писал.

счётесли() ? (считает количество ячеек, содержание которых удовлетворяет некоторому условию)

[Red_Herring]

upgrade в сообщении #1306370 писал(а):

возвращает значение 0, если до запятой итог вычисления ошибка.

Спасибо, надо попробовать

upgrade в сообщении #1306370 писал(а):

счётесли() ? (считает количество ячеек, содержание которых удовлетворяет некоторому условию)

А какой код (Excel естественно американский)

[Aritaborian]

COUNTIF().

[upgrade]

Red_Herring в сообщении #1306377 писал(а):

А какой код (Excel естественно американский)

Countif, но с такими функциями лучше сразу привыкать к операциям с массивами условий - countifS(), они понятнее и мощнее в будущем.

[Red_Herring]

С вопросом 1 у меня, спасибо upgrade, все ясно (я проверил на test file)

Вопрос 2: допустим я спасаю примечания о справках в колонке M в формате QN4, QN34, если пропущены соответственно квизы 4 и 3+4,
QNA256, QNB2346, QNC12356, QND123456, QNE1234567 если пропущены соответственно квизы 2+5+6 и 2+3+4+6, и т.д. Теперь я хочу в колонке N поместить множители 1 если префикс QN, 5/4 если префикс QNX, и т.д. Как

[Geen]

(Оффтоп)

Лучше бы, на мой взгляд, переключать Эксель в R1C1 режим - формулы понятнее

Red_Herring в сообщении #1306394 писал(а):

Теперь я хочу в колонке N поместить множители 1 если префикс QN, 5/4 если префикс QNX, и т.д

LENGTH

[upgrade]

Скорее еслиошибка(найти(),)
ищет подстроку, и если находит возвращает номер первого символа, а если не находит возвращает то что после запятой в еслиошибка.
полный скрипт

=if(iferror(find("QN","QN4"),0)>0;1;"")

а, ну да, еще обработка второго

=if(iferror(find("QN","QN4"),0)>0;1;if(iferror(find("QNX","...QNX..."),0)>0;5/4;""))

опять же лучше использовать ifS() (еслиМН, операции с множеством условий)

[Александрович]

Red_Herring в сообщении #1306353 писал(а):

если я ввожу в Excel 4/3, то он понимает это как "3 апреля". Как ему объяснить, чтобы не умничал?

$=3/4$.

[Andrey A]

Red_Herring в сообщении #1306353 писал(а):

Как ему объяснить, чтобы не умничал?

Он умничает в общем формате ячеек. Попробуйте поменять на числовой или, как вариант, на "дробный". Для квизов, если я правильно понял, может пригодиться функция СУММЕСЛИ (не знаю как по-английски), но там надо правильно задать условия.

[Red_Herring]

Александрович в сообщении #1306400 писал(а):

$=3/4$.

Спасибо

Geen в сообщении #1306397 писал(а):

LENGTH

Только не LENGTH, а LEN

Спасибо, все хорошо!