Уважаемые коллеги, вопросы, как правило, вызывает один из пунктов Задания 3, а именно
По результатам сдачи сессии группой студентов (таблица Итоги экзаменационной сессии), определить
…
фамилию студента, с наивысшим средним баллом.
Представляется, что оптимальным способом выполнения этого задания все-таки является способ с задействованием функции, упомянутой в ЛР 3.5, а именно ВПР(). Для предыдущего задания — определения самого «сложного» предмета из трех — вполне достаточно использования несколько вложенных функций ЕСЛИ() , и данный вариант, в общем, возможен и в этом случае, однако он, очевидно, не оптимален.
Функция ВПР() ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Синтаксис функции в общем случае таков:
ВПР(искомое_значение;таблица ;номер_столбца ;интервальный_просмотр)
Искомое_значение в данном случае — максимальный средний балл — если средние оценки студентов располагаются в диапазоне C2:C8, то он, соответственно, будет найден как результат выполнения функции МАКС(C2:C8).
Некоторое неудобство состоит в том, что в просматриваемой Таблице — столбец со значениями средних баллов, должен быть крайним левым, то есть где-то справа от него должен быть продублирован уже существующий столбец с фамилиями студентов (допустим, столбец продублирован в диапазоне E2:E8)
Соответственно, в этом случае параметр таблица необходимо задать как C2:E8, то есть диапазон, включающий в себя столбец со средними оценками (крайний левый) и столбец с фамилиями.
Параметр номер_столбца в этом случае примет значение 3 (номер столбца с фамилиями), а параметр интервальный_просмотр задается как ЛОЖЬ или 0, то есть указывает, что средние баллы не отсортированы по возрастанию.
При таком расположении исходных данных формула, дающая в результате фамилию студента с максимальным средним баллом может быть записана как
=ВПР(МАКС(C2:C8);C2:E8;3;0)
Альтернативный способ состоит в использовании функции ИНДЕКС ()
ИНДЕКС(ссылка ;номер_строки;номер_столбца;номер_области) возвращает ссылку на указанные ячейки или ячейки в аргументе «ссылка».
с одной стороны, эта функция менее «требовательна» к размещению части исходных данных — диапазона, из которого будет выбрано значение (т.е. фамилия студента с максимальным средним баллом), однако в том, что касается способа выбора — необходимо явное указание номера строки, где находится фамилия — необходимы дополнительные действия по поиску этого номера, то есть задействование еще одной функции, например, ПОИСКПОЗ ()
Эта функция возвращает относительное положение элемента массива , который соответствует указанному значению в указанном порядке. Синтаксис функции
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)
где тип_сопоставления — число -1, 0 или 1, которое указывает, каким образом сопоставляется искомое_значение со значениями в аргументе просматриваемый_массив. Типы -1 и 1 требуют того, чтобы массив был упорядочен, в данном случае требуется выбор типа 0, при котором функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение и просматриваемый_массив может быть не упорядочен.
При расположении исходных данных — фамилии студентов — в диапазоне C2:C8, а их средние баллы — в диапазоне D2:D8 — формула для решения задачи выглядит так:
=ИНДЕКС(C2:C8;ПОИСКПОЗ(МАКС(D2:D8);D2:D8;0))
Еще один альтернативный, и, возможно более простой для запоминания, хотя и несколько более сложный для реализации вариант — выполнение в два действия. Сначала в каждую ячейку дополнительного диапазона (F12:F18), по размерности совпадающего с диапазоном списка фимилий (C12:C18) вводится формула:
=ЕСЛИ(D12=МАКС($D$12:$D$18);C12;»»)
то есть в случае совпадения значения средней оценки (значения в диапазоне D12:D18) с максимальной (МАКС($D$12:$D$18)) выводится соответствующее значение из ячейки диапазона C12:C18, то есть фамилия, в противном случае в ячейку помещается пустое значение «». А затем тексты ячеек диапазона F12:F18 (содержащего одну фамилию) «сцепляются»:
=СЦЕПИТЬ(F12;F13;F14;F15;F16;F17;F18)