Microsoft Excel. От основ к задачам обработки и представления данных (стр. 28 )

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

Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива воздействует на несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен иметь соответствующий номер строки и столбца. Формула массива создается так же, как и простая формула. Выделяется ячейка или группа ячеек, в которых необходимо создать формулу, вводится формула, а затем нажимаются клавиши 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, … и возвратит их значения для функции СРЗНАЧ, которая найдет их среднее значение. Для остальных ячеек функция ЕСЛИ не возвратит никакого значения.

8.3.6. Расчет первых пяти наибольших

и последних пяти наименьших значений

Расчет построен на двух функциях: НАИБОЛЬШИЙ и НАИМЕНЬШИЙ. Функция НАИБОЛЬШИЙ (диапазон, k) возвращает k-ое наибольшее значение из множества данных, определенных диапазоном. Эта функция используется, чтобы выбрать значение по его относительному местоположению. Например, функцию НАИБОЛЬШИЙ можно использовать, чтобы определить наилучший, второй или третий результат в баллах, показанный при тестировании.

Замечания.

1.  Если массив пуст, то функция НАИБОЛЬШИЙ возвращает значение ошибки #ЧИСЛО!.

2.  Если k £ 0 или если k больше, чем число точек данных, то функция НАИБОЛЬШИЙ возвращает значение ошибки #ЧИСЛО!.

Аналогичные аргументы и замечания имеет функция НАИМЕНЬШИЙ, только возвращает наименьшее k-ое значение из диапазона данных.

Пример формул, используемых на листе "Температура":

=НАИБОЛЬШИЙ($AC$21:$AC$320;AJ21),

=НАИМЕНЬШИЙ($AC$21:$AC$320;AJ31).

Формулы содержат не конкретное значение параметра k, а ссылку на ячейку, содержащую необходимое значение. Это удобно, так как позволяет найти 7, 2, 15 (любое допустимое) наибольшее или наименьшее значения.

8.3.7. Расчет числа дней, в которых

температура была в одном из диапазонов

Расчет основан на одной функции СЧЁТЕСЛИ. Она уже Вам знакома. В качестве критерия на листе "Температура" задается ссылка на ячейку, в которой в виде текста находится условие. В ячейке AH42 находится следующая формула:

=СЧЁТЕСЛИ($AC$21:$AC$320;AG42)

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

Чтобы подсчитать, сколько дней температура была, скажем больше 25 °С и меньше 30 °С, необходимо подсчитать число дней, в которых температура была меньше 25 °С и число дней, в которых температура была меньше 30 °С. Затем вычесть из второго числа первое.

8.3.8. Составление таблицы температур

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

Справа от начальных данных находится большая область (часть ее заполнена), выделенная желтым фоном. В ней чередуются колонки с номерами дней и ссылками. В столбцах с названием "Ссылка" находится текст, который содержит адреса ячеек, содержащих температуру в день который написан в заголовке в колонке слева. Как вычислить адреса ячеек? Посмотрите на начальные данные. У них есть закономерность, через каждые десять строк расположены ячейки, содержащие температуру в один и тот же день, но в разные года. Если последовательно записать их в столбец, то получим температуру одного дня в разные года. Но не нужно вписывать их вручную – вспомните процедуру автозаполнения. Достаточно написать первые два значения, скажем для третьего столбца "Ссылка" это будет AC23, AC33. Затем выделить все ячейки в этом столбце, начиная с ячейки после заголовка столбца "Ссылка" (в нашем случае – AR23) до ячейки AR52 (количество ячеек равно числу лет наблюдений – 30). После этого нужно выполнить из меню «Правка» команду «Заполнить/Прогрессия» и указать «Тип» как автозаполнение. Excel автоматически заполнит все ячейки нужными значениями (буквы в названии ячеек – AC, останутся, а цифры изменятся с шагом десять – столько измерений у нас в году).

Теперь используя функцию ДВССЫЛ можно получить необходимые значения. Для этого в столбце слева от ссылок (с заголовком номера дня) необходимо написать формулу: =ДВССЫЛ(х)

х представляет собой имя ячейки, содержащей имя другой ячейки (с данными температуры какого-то дня). Т. е. в нашем случае ячейка AO23 содержит следующую формулу: =ДВССЫЛ(AP23)

Microsoft Excel возьмет значение ячейки AP23 (оно равно AC22) и возвратит значение ячейки по этому адресу (значение ячейки AC22 равно 18.61).

Задание № 36. Заполните область до конца и рассчитайте среднее, максимальное и минимальное значения для каждого дня (по аналогии с рассчитанными значениями). Учтите, что записать книгу в Excel Вы сможете только на собственную дискету.

8.3.9. Условное форматирование и автофильтр

Шаги для выполнения условного форматирования были описаны выше. Единственная подсказка – условное форматирование выполняется по формуле: =$AB21=1.

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

Вы, наверное, обратили внимание, что у некоторых ячеек имеются кнопки, вызывающие список значений (рис. 85).

Это образуется не с помощью элементов интерфейса, а с помощью автофильтра. Для его установления используют команду «Фильтр/Автофильтр» из меню «Данные». Он устанавливается на все выделенные ячейки в строке и смежные с ними ячейки с данными. Снятие автофильтра производится той же командой. Так, после столбца с температурой, отсортированной в порядке убывания, на листе пустой столбец. Если бы он имел значения, то автофильтр был бы установлен и на него (даже если его не выделить).

Выбор одного из значений приводит к отображению только тех строк, которые в данном столбце содержат выбранное значение.

8.3.10. Составление программы

В программе используется типовой алгоритм поиска максимума, минимума. Блок-схема алгоритма поиска максимума имеет вид (рис. 86).