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

Инструкция Show является методом, который показывает на экран объект класса UserForm. Объекта пока не существует, поэтому создадим его.

Для создания объекта класса UserForm нажмите на панели инструментов «Стандарт» соответствующую кнопку, либо через меню «Вставка» командой «UserForm». Необходимо изменить заголовок этой формы (свойство Caption) на фразу "Статистика по студентам". Размеры самого окна подправляются в процессе создания других объектов. Свойство StartUpPosition должно быть установлено в 1 – CenterOwner, чтобы окно выводилось посредине листа. Окно свойств вызывается или клавишей F4 или через контекстное меню на объекте командой «Свойства» или через меню «Вид» командой «Окно свойств».

Далее создается объект класса «Рамка». Объект данного класса может содержать другие объекты. Необходимо изменить название рамки на "Количество оценок", это делается с помощью свойства Caption. Если шрифт не является русским, то измените его на русский шрифт с помощью свойства Font. Стандартным системным экранным шрифтом (которым пишется меню) является MS Sans Serif. В созданном объекте класса «Рамка» располагают объекты класса «Надпись», с текстом "число оценок всего", "число двоек", "число троек", "число четверок", "число пятерок". Изменение текста производится с помощью свойства Caption, затем текст выравнивают по правому краю свойством TextAlign, устанавливая его значение в 3 – fmTextAlignRight. Цвет надписей меняется свойством ForeColor. После создается еще один объект класса «Надпись» и изменяют ему свойство SpecialEffect на значение 2 – fmSpecialEffectSunken. Данный объект можно скопировать (так же как Вы копируете текст в Word). Нажимаете один раз на объекте левой кнопкой мыши (если он не выделен), затем клавиши Ctrl+Insert, а после Shift+Insert. Этот объект будет содержать значение, получаемое при расчете с помощью программы. При необходимости меняют цвет шрифта (свойство ForeColor) и фона (свойство BackColor).

Аналогично создаются заготовки в виде объектов класса «Рамка» для информации об успеваемости студентов и стипендиальном фонде. Для информации об успевающих студентах создается только рамка. В объекте класса «Рамка» с заголовком "Стипендия" присутствует три объекта класса «Счетчик». Значения свойств Max, Min, Value первого объекта установлены в 120, 60, 80 соответственно. Второго объекта в 180, 120, 120, а третьего в 320, 180, 250. Первое значение указывает потолок стипендии: минимальной – для первого объекта, повышенной – для второго и именной – для третьего объекта. Второе значение указывает минимум тех же стипендий. А третье значение указывает на текущий размер этих стипендий (должно лежать в пределах первого и второго). С помощью данных объектов пользователь сможет изменять размер стипендий и получать суммы стипендиального фонда. Кроме того, эти объекты имеют особенные свойства ControlTipText – пояснительный текст к объекту, который выводится на желтом фоне, когда указатель мыши находится некоторое время на объекте. Данное свойство изменено так же у объектов класса «Надпись» стоящих рядом с объектами класса «Счетчик». Внимательно посмотрите на эти свойства.

И, наконец, последний объект класса «Рамка» содержит объект класса «Список». Данный объект также имеет особенное свойство ControlTipText, которое поясняет назначение объекта.

После создания всех этих объектов приступают к написанию программы. Переход в режим написания программы осуществляется либо двойным нажатием левой кнопки мыши на объекте класса UserForm (но не другом объекте, который содержит UserForm), либо нажатием кнопки программа в окне проекта (вызывается либо через меню «Вид» командой «Окно проекта», либо клавишами Ctrl+R). Вызвать режим написания программы можно через контекстное меню (вызывается правой кнопкой мыши) командой «Программа» на объекте класса UserForm.

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

Для написания программы, которая будет выполняться при появлении окна на экране (а нам нужна именно такая программа, когда мы нажимаем на кнопку, расположенную на листе, должно появляться окно с рассчитанными значениями) необходимо в левом списке выбрать объект «UserForm1», а в правом событие «Initialize», которое возникает при появлении окна на экране. Появится "заготовка" для процедуры:

Private Sub UserForm_Initialize()

End Sub

Далее нужно написать программу. Текст программы с комментариями приведен ниже (записаны после знака «'»). Все, что заключено в рамки в реальной программе отсутствует – это дополнительные объяснения.

Dim Obj As Object ' Вспомогательная переменная

Здесь объявляется новый для Вас тип переменной – Object. Переменная данного типа может содержать любой объект, который имеется в книге. В программе данная переменная будет содержать ячейку таблицы.

Dim i As Integer ' Вспомогательная переменная

Данная переменная будет использоваться в качестве цикловой переменной

Dim Count_Of_Note As Integer ' Число оценок

Dim Count2 As Integer ' Число двоек

Dim Count3 As Integer ' Число троек

Dim Count4 As Integer ' Число четверок

Dim Count5 As Integer ' Число пятерок

Объявляются переменные, которые будут содержать общее число оценок, число двоек, троек, четверок, пятерок.

' Выводим начальный размер минимальной стипендии

Label40.Caption = SpinButton1.Value

' Выводим начальный размер повышенной стипендии

Label41.Caption = SpinButton2.Value

' Выводим начальный размер именной стипендии

Label42.Caption = SpinButton3.Value

Выводим значения объектов класса «Счетчик» на экран. Данные значения будут содержать объекты класса «Надпись», расположенные рядом. Обратите внимание на то, как осуществляется доступ к свойствам объекта. Он производится через точку. Несмотря на то, что свойство Value является числом, а Caption текстом, преобразование типов данных не нужно. Откуда взять название объектов класса «Надпись» – из окна свойств, там необходимо посмотреть главное свойство любого объекта – Name.

' Обнуляем количество оценок

Count2 = 0: Count3 = 0: Count4 = 0: Count5 = 0

Count_Of_Note = 0

' Обнуляем количество студентов

Student2 = 0: Student3 = 0: Student4 = 0: Student5 = 0

' Просмотрим каждую ячейку в диапазоне двоек

For Each Obj In Worksheets("Сессия").Range("ao17:ao316")

Count2 = Count2 + Obj. Value ' Суммируем число двоек

' Считаем двоечников

If Obj. Value > 0 Then

Student2 = Student2 + 1

End If

Next Obj

Код участка программы можно выразить следующей блок-схемой (рис. 84).

Рис. 84. Блок-схема участка программы.

Здесь перебираются все ячейки в диапазоне от AO17 до AO316 на листе с названием "Сессия", что является диапазоном количества двоек у каждого студента. За перебор отвечает цикл For Each .. In … Next. Переменная Count2, содержащая количество двоек наращивается на величину Obj. Value, которая содержит значение ячейки (числовое или текстовое). Если попадется текст в этом диапазоне, то возникнет ошибка несоответствия типа (пытаемся числовой переменной прибавить текст). В этом же цикле удобно подсчитать количество двоечников. Если у ячейки значение Value не равно нулю, значит студент двоечник. Конечно, если в таблице окажется случайно число 123 (а предметов всего 12), то программа это не учтет. Для этого надо добавить в условие сравнение Obj. Value < 13. Если значение ячейки не равно нулю, то необходимо увеличить переменную Student2 (количество двоечников) на единицу. Кроме того, данный код зависит от формул на листе, так как не считает число двоек у каждого студента, а использует уже подсчитанные значения и суммирует их.

' Просмотрим каждую ячейку в диапазоне троек

For Each Obj In Worksheets("Сессия").Range("ap17:ap316")

Count3 = Count3 + Obj. Value ' Суммируем число троек

Next Obj

' Просмотрим каждую ячейку в диапазоне четверок

For Each Obj In Worksheets("Сессия").Range("aq17:aq316")

Count4 = Count4 + Obj. Value ' Суммируем число четверок

Next Obj

' Просмотрим каждую ячейку в диапазоне пятерок

For Each Obj In Worksheets("Сессия").Range("ar17:ar316")

Count5 = Count5 + Obj. Value ' Суммируем число пятерок

' Считаем отличников

If Obj. Value = 12 Then

Student5 = Student5 + 1

End If

Next Obj

Число отличников можно найти как сумму студентов имеющих число пятерок равное количеству предметов (в нашем случае 12). Число троечников определяется сложнее, так как у "потенциального" троечника (число троек не равно нулю) могут быть двойки. Число хорошистов тоже определяется сложнее по той же причине.

' Число оценок равно сумме 2,3,4,5

Count_Of_Note = Count2 + Count3 + Count4 + Count5

Label13.Caption = Count_Of_Note ' Выводим на экран число оценок

Label14.Caption = Count2 ' Выводим количество двоек

Label15.Caption = Count3 ' Выводим количество троек

Label16.Caption = Count4 ' Выводим количество четверок

Label17.Caption = Count5 ' Выводим количество пятерок

' Вычисление процентов

' Процент двоек

Label18.Caption = Format(Count2 / Count_Of_Note, "0.00%")

Функция Format осуществляет преобразование числа в определенный формат. В данном случае функция Format преобразует число к процентному формату (умножает на 100) с двумя знаками после запятой – "0.00%").

' Процент троек

Label19.Caption = Format(Count3 / Count_Of_Note, "0.00%")

' Процент четверок

Label20.Caption = Format(Count4 / Count_Of_Note, "0.00%")

' Процент пятерок

Label21.Caption = Format(Count5 / Count_Of_Note, "0.00%")

' Просмотрим каждую ячейку в диапазоне оценок

For i = 17 To 316

' Считаем троечников

If Worksheets("Сессия").Range("ao" + Trim(Str(i))) = 0 And _

Worksheets("Сессия").Range("ap" + Trim(Str(i))) > 0 Then

Student3 = Student3 + 1

End If

' Считаем хорошистов

If Worksheets("Сессия").Range("ao" + Trim(Str(i))) = 0 And _

Worksheets("Сессия").Range("ap" + Trim(Str(i))) = 0 And _

Worksheets("Сессия").Range("aq" + Trim(Str(i))) > 0 Then

Student4 = Student4 + 1

End If

' Заносим успевающих студентов в список

If Worksheets("Сессия").Range("ao" + Trim(Str(i))) = 0 Then

ListBox1.AddItem (Worksheets("Сессия"). Range("aa" +

+ Trim(Str(i))). Value & " – " & _

Worksheets("Сессия"). Range("an" + Trim(Str(i))).Value)

End If

Next i

Знак подчеркивания ( _ ) означает, что строка программы продолжается на следующей физической строке. Подсчет троечников осуществляется так: если у студента количество двоек равно 0 и количество троек больше нуля, то он – троечник. Аналогично делается вывод по хорошистам. Интересным является способ создания списка успевающих студентов. Объект класса «Список» имеет метод AddItem. Данный метод добавляет одну строку в список. Строка должна представлять собой текст. В нашем случае текст составляется из фамилии (берется значение ячейки из столбца AA, и добавляется к нему через знак минуса (с помощью знаков &, которые соединяют текстовые строки) значение среднего балла (столбец AN)). Обратите внимание на формирование адреса ячейки: "aa" + Trim(Str(i)). Функция Str переводит числовое значение в строку текста (например, число 234 переводит в "234"), а функция Trim обрезает пробелы в начале и в конце строковой переменной (например, строку " 34е " преобразует в "34е"). Итак, поскольку адрес ячейки представляет собой текст, состоящий из названия столбца и номера строки, то для его формирования мы к названию столбца – AA (мы его знаем заранее) нужно добавить в виде текста номер строки (номера меняются в теле цикла). Поскольку у нас имеется номер строки в виде числа (значение переменной i), то нужно преобразовать его в текст. Функция Str переводит число в текст, однако, она добавляет в начале пробел (если число положительное) или минус (если число отрицательное). У нас число положительное, но пробел в имени ячейки недопустим, поэтому используется функция Trim.

' Всего студентов – вычисляется как сумма студентов всех категорий

Students = Student2 + Student3 + Student4 + Student5

Label22.Caption = Students ' Выводим на экран число студентов

Label26.Caption = Student2 ' Выводим количество двоечников

Label25.Caption = Student3 ' Выводим количество троечников

Label24.Caption = Student4 ' Выводим количество хорошистов

Label23.Caption = Student5 ' Выводим количество отличников

' Выводим на экран процент

Label30.Caption = Format(Student2 / Students, "0.00%")

Label29.Caption = Format(Student3 / Students, "0.00%")

Label28.Caption = Format(Student4 / Students, "0.00%")

Label27.Caption = Format(Student5 / Students, "0.00%")

' Подсчитываем суммы стипендий

Label37.Caption = Student3 * SpinButton1.Value ' Минимальной

Label38.Caption = Student4 * SpinButton2.Value ' Повышенной

Label39.Caption = Student5 * SpinButton3.Value ' Именной

Сумма стипендий равна произведению числа студентов, получающих эту стипендию, на размер одной стипендии. Объекты класса «Счетчик» содержат размер одной стипендии для каждой категории студентов.

' Стипендиальный фонд

Label32.Caption = Student5 * SpinButton3.Value + Student4 * SpinButton2.Value + Student3 * SpinButton1.Value

Выход обратно из модуля к виду объекта класса UserForm можно выполнить, нажав мышью кнопку «Объект» в окне проекта, либо дважды нажав мышью на самом объекте UserForm.

После записи этой программы необходимо еще заставить программу реагировать на нажатия пользователя на счетчики. Для этого нажмите два раза кнопкой мыши на первом объекте класса «Счетчик». Вы увидите новую "заготовку" под процедуру:

Private Sub SpinButton1_Change()

End Sub

Данная процедура будет обрабатывать событие «Change», которое возникает при изменении значения счетчика пользователем (во время работы программы). Необходимо написать следующий код в "заготовке":

' Выводим измененный размер минимальной стипендии

Label40.Caption = SpinButton1.Value

' Сумма стипендий

Label37.Caption = Student3 * SpinButton1.Value ' Минимальной

' Стипендиальный фонд

Label32.Caption = Student5 * SpinButton3.

Value + Student4 * SpinButton2.

Value + Student3 * SpinButton1.Value

Данный код позволит обновить значения (текст) трех объектов, которые содержат: размер минимальной стипендии; стипендиальный фонд для троечников; стипендиальный фонд всех стипендий.

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

Данный код с небольшими изменениями в названиях объектов и меняемых значениях необходимо также написать для других объектов класса «Счетчик».

Осталось самое малое – запустить программу. Нажмите кнопку F5 или кнопку «Запуск программы/UserForm» на панели инструментов. Через небольшое время Вы увидите результат. Можно закрыть редактор Visual Basic (клавиши Alt+Q или через меню «Файл» командой «Закрыть и вернуться в Microsoft Excel»). Теперь, при каждом нажатии на созданную нами кнопку будет выполняться наша программа, и появляться наше окно.

Если необходимо изменить свойства кнопки, то нужно войти в режим конструктора, нажав кнопку «Режим конструктора» на панели инструментов «Visual Basic”. Выход осуществляется также.

Задание № 33. Внимательно изучите весь программный код. Попробуйте сами себе объяснить назначение каждой строчки программы. Изучите свойства объекта. Мысленно повторите процедуру создания программы. Если программа в Вашей заготовке отсутствует, то создайте ее.

8.3. Применение полученных знаний

для решения однотипных задач

8.3.1. Задача обработки информации

об измерениях температуры

Пусть имеются данные о результатах измерений температуры с 1960 по 1989 год. Температура измерялась в градусах Цельсия десять раз в году в определенные дни.

Требуется:

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

·  Упорядочить наблюдения по возрастанию и по убыванию, разместить упорядоченные значения в два столбца справа от заданного ряда наблюдений.

·  Рассчитать средние значения температуры для каждого дня, в которые производились измерения (округленные до двух знаков после запятой).

·  Рассчитать первые 5 наибольших значений и последние 5 наименьших значений температуры за весь срок наблюдений.

·  Рассчитать число дней (и процент их в общем числе дней) за срок наблюдений, в которых температура была:

ü  не выше 15 °С;

ü  не выше 20 °С;

ü  не выше 25 °С;

ü  не выше 30 °С;

ü  не выше 35 °С;

ü  выше 35 °С;

ü  выше 15 °С и ниже 20 °С;

ü  выше 20 °С и ниже 25 °С;

ü  выше 25 °С и ниже 30 °С;

ü  выше 30 °С и ниже 35 °С.

Построить диаграмму, отображающую число дней в каждом интервале температуры.

·  Составить таблицу значений температур 1-го, 2-го, ... ,10-го дня. Рассчитать для них среднее, максимальное и минимальное значения.

·  Выделить условным форматированием первый день каждого года.

·  Составить программу, рассчитывающую среднее, максимальное, минимальное значения за весь период наблюдения и за каждый день на протяжении периода наблюдений.

8.3.2. Некоторые особенности решения

задачи обработки температур

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

Задание № 34. Внимательно изучите весь лист "Температура". Обратите внимание на функции, используемые для решения задачи. Ниже будет пояснена некоторая часть формул используемых на этом листе. Особое внимание обратите на программный код. Попробуйте сами себе объяснить назначение каждой строчки программы.

8.3.3. Нахождение максимального,

минимального и среднего значения

Нахождение максимального значения производится с помощью функции МАКС с указанием диапазона поиска. Нахождение минимального значения производится с помощью функции МИН, среднее значение с помощью функции СРЗНАЧ. Число значений находится путем определения числа строк в диапазоне с данными. Округление значения среднего производится функцией ОКРУГЛ. Формулы для нахождения максимального, минимального и среднего значения за весь период наблюдения находятся на листе "Температура", в ячейках:

·  AH20 (число значений): =ЧСТРОК(AC21:AC320);

·  AH21 (максимальное значение): =МАКС(AC21:AC320);

·  AH22 (минимальное значение): =МИН(AC21:AC320);

·  AH23 (среднее значение): =ОКРУГЛ(СРЗНАЧ(AC21:AC320);2).

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

Задание № 35. Рассчитайте среднее, максимальное и минимальное значение для каждого года. Постройте график отражающий динамику рассчитанных значений по годам. Учтите, что записать книгу в Excel Вы сможете только на собственную дискету или в свой каталог в сети (сохранение файла понадобится, так как задание Вы будете выполнять несколько дней). Вы можете сохранить книгу Excel на дискету с новым именем. Затем открывайте два файла – один с оригинальной версией работы, а другой собственный – с дискеты.

8.3.4. Упорядочивание данных

Часто необходимо произвести сортировку данных. Для этого можно использовать следующие шаги:

1.  Скопировать данные (если не нужно сортировать начальные данные, а в нашем случае это выполнять не нужно, так как начальные данные расположены по годам) в новую область. На листе "Температура" столбцы (AD, AE) с дублированными данными выделены голубым цветом. Операцию копирования можно произвести так: встать на ячейку AC21 и удерживая клавиши Ctrl и Shift, нажать клавишу со стрелкой вниз (быстрое выделение области), затем нажать клавиши Ctrl+Insert (запоминание выделенного диапазона ячеек), после перейти на ячейку AD21 и нажать Shift+Insert (копирование ячеек из буфера в таблицу). Эти же операции можно произвести с помощью мыши (выделение диапазона) и через меню «Правка» с помощью команд «Копировать» (запоминание выделенного диапазона ячеек) и «Вставить» (копирование ячеек из буфера в таблицу).

2.  Выделить сортируемый диапазон ячеек, операция выделения описана выше.

3.  Отсортировать ячейки. В нашем случае необходимо нажать на панели инструментов «Стандартная» кнопку «Сортировка по возрастанию» (сортировка выбранных элементов в порядке от начала алфавита к его концу, от меньших чисел к большим и от более ранних дат к более поздним по столбцу, в котором находится курсор). Или (для второго столбца с дублированными данными – AE) «Сортировка по убыванию» (противоположный порядок), в зависимости от необходимой операции.

Ячейки можно сортировать по значениям ячеек одного или нескольких столбцов. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки (способ изменения относительного положения данных, основанный на значении или типе данных; данные можно сортировать по алфавиту, по числу или по дате). Ячейки можно сортировать в возрастающем (от 1 до 9, от А до Я) или убывающем (от 9 до 1, от Я до А) порядке.

По умолчанию ячейки сортируются в алфавитном порядке. Для сортировки месяцев и дней недели в соответствии с их логическим, а не алфавитным порядком следует использовать пользовательский порядок сортировки (неалфавитный и нечисловой порядок сортировки, например: воскресенье, понедельник, вторник; или – водород, кислород, железо). В Microsoft Excel определено несколько порядков сортировки, а с помощью вкладки «Списки» диалогового окна «Параметры» (меню «Сервис») можно определить свой собственный порядок сортировки. Такой порядок сортировки также используется, если требуется отсортировать список в другом, особом порядке. Например, если в одном из столбцов списка содержатся значения «Низкий», «Средний» или «Высокий», можно создать такой порядок сортировки, что строки, содержащие «Низкий», будут первыми, «Средний» – следующими и «Высокий» – последними.

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

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

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

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

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).

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