3.2.3. Расчет средних значений для каждого дня

Выполняя задание №6 Вы, наверное, указывали диапазон значений каждого года, отыскивая адреса в таблице. Следующее выполнение задания подскажет Вам более эффективный способ (но более сложный для понимания).

ВExcel'е (не вVisual Basic) есть понятиемассиваиформула массива. В общем-то, массивом вExcel'е называют группу ячеек, отличающуюся от диапазона характером ссылок на нее в формулах.

Обычно формула при обработке несколькихаргументов(значения функции, используемые для вычислений) возвращает одно значение; в качестве аргумента формулы может при этом выступать либо ссылка на ячейку, содержащую значение, либо само значение. Для создания ссылки на диапазон ячеек используетсяформула массива, позволяющая ввести в одну ячейкумассив значений. Этот массив значений называетсямассивом констант; удобен он тем, что при этом не требуется заполнять значениями вспомогательные ячейки.

Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива воздействует на несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен иметь соответствующий номер строки и столбца. Формула массива создается так же, как и простая формула. Выделяется ячейка или группа ячеек, в которых необходимо создать формулу, вводится формула, а затем нажимаются клавиши Ctrl + Shift + Enter.

Если необходимо вычислить одно значение, Microsoft Excel'у может понадобиться выполнить несколько действий для возврата такого значения. Например, следующая формула (вводится с помощью клавишCtrl + Shift + Enter) вычисляет среднее значение только тех ячеек, принадлежащих диапазону D5:D15, которым в столбце А поставлена в соответствие строка «авиалиния Небеса». ФункцияЕСЛИнаходит ячейки в диапазоне A5:A15, содержащие строку «авиалиния Небеса», и возвращает значения, соответствующие этой строке в диапазоне D5:D15, функцииСРЗНАЧ.

{=СРЗНАЧ(ЕСЛИ(A5:A15="авиалиния Небеса",D5:D15))}

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

Кроме того, формулу массива можно использовать для вычисления одного или нескольких значений для последовательности, которая не указана на листе. В формулу массива можно включать константы так же, как это делается в простой формуле, но массив констант должен вводиться в определенном формате.

В нашей задаче в ячейках с AH27 поAH36расположены формулы, вычисляющие средний балл для каждого из десяти дней на протяжении всех лет. Формула имеет вид:

{=ОКРУГЛ(СРЗНАЧ(ЕСЛИ($AB$21:$AB$320=AG27;$AC$21:$AC$320));2)}

Фигурные скобки {} у формулы означают, что этоформула массива. Они появляются после нажатия Ctrl + Shift + Enter, исчезают при начале редактирования формулы.

Внимание!Редактирование и ввод формулы массива всегда должен заканчиваться нажатием клавиш Ctrl + Shift + Enter.

Функции, используемые в формуле Вам знакомы. Как же работает эта формула? В функции ЕСЛИзадан диапазон проверяемых ячеек -AB21:AB320 (с абсолютной адресацией, чтобы не изменялся). Если значение ячейки из этого диапазона (номер дня) равно ячейкиAG27 (содержит номер дня, для которого находим среднее), то возвращается значение соответствующей ячейки из диапазонаAC21:AC320. Затем для всех найденных ячеек (удовлетворяющие условию) определяется среднее значение, которое после округляет до двух знаков после запятой. Например, ячейкаAG27 содержит значение один. Тогда функцияЕСЛИпроверит из диапазонаAB21:AB320 все ячейки, и сравнение условия в функции будет равно значениюИСТИНАдля ячеекAB21, AB31, AB41, … (они содержат тоже значение равное единице). Поэтому функцияЕСЛИвыберет из диапазонаAC21:AC320соответствующие ячейки -AC21, AC31, AC41, … и возвратит их значения для функцииСРЗНАЧ, которая найдет их среднее значение. Для остальных ячеек функцияЕСЛИне возвратит никакого значения.

План лабораторной работы: Контрольные вопросы