И. В. Степанченко Microsoft Excel От основ к задача
| Вид материала | Задача |
- Применение Microsoft Excel для обработки табличных данных. Выполнение расчетов в таблицах, 14.68kb.
- Пособие для начинающих Воробьев В. В. Microsoft Excel, 439.68kb.
- Курс лекций по дисциплине " основы компьютерных технологий" Часть II. Microsoft Excel, 457.54kb.
- Методика оформление учебно-методической документации учителем географии в текстовом, 644.79kb.
- Лабораторная работа №4 Тема: Панели Microsoft Excel, 44.05kb.
- Пособие для начинающих Воробьев В. В. Microsoft Excel 2000, 451.03kb.
- Назначение программы Microsoft Excel (или просто Excel ) и создание и обработка электронных, 184.32kb.
- Программы Microsoft Excel. После загрузки программы на экране монитора можно увидеть, 77.36kb.
- Основы работы с электронными таблицами в Microsoft Excel, 40.42kb.
- Лабораторна робота №9: "Табличний процесор Microsoft Excel", 130.45kb.
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. Упорядочивание данных
Часто необходимо произвести сортировку данных. Для этого можно использовать следующие шаги:
- Скопировать данные (если не нужно сортировать начальные данные, а в нашем случае это выполнять не нужно, так как начальные данные расположены по годам) в новую область. На листе "Температура" столбцы (AD, AE) с дублированными данными выделены голубым цветом. Операцию копирования можно произвести так: встать на ячейку AC21 и удерживая клавиши Ctrl и Shift, нажать клавишу со стрелкой вниз (быстрое выделение области), затем нажать клавиши Ctrl+Insert (запоминание выделенного диапазона ячеек), после перейти на ячейку AD21 и нажать Shift+Insert (копирование ячеек из буфера в таблицу). Эти же операции можно произвести с помощью мыши (выделение диапазона) и через меню «Правка» с помощью команд «Копировать» (запоминание выделенного диапазона ячеек) и «Вставить» (копирование ячеек из буфера в таблицу).
- Выделить сортируемый диапазон ячеек, операция выделения описана выше.
- Отсортировать ячейки. В нашем случае необходимо нажать на панели инструментов «Стандартная» кнопку «Сортировка по возрастанию»
(сортировка выбранных элементов в порядке от начала алфавита к его концу, от меньших чисел к большим и от более ранних дат к более поздним по столбцу, в котором находится курсор). Или (для второго столбца с дублированными данными – 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-ое наибольшее значение из множества данных, определенных диапазоном. Эта функция используется, чтобы выбрать значение по его относительному местоположению. Например, функцию НАИБОЛЬШИЙ можно использовать, чтобы определить наилучший, второй или третий результат в баллах, показанный при тестировании.
Замечания.
- Если массив пуст, то функция НАИБОЛЬШИЙ возвращает значение ошибки #ЧИСЛО!.
- Если 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).
Э
Рис. 85. Ячейки с кнопками.
то образуется не с помощью элементов интерфейса, а с помощью автофильтра. Для его установления используют команду «Фильтр/Автофильтр» из меню «Данные». Он устанавливается на все выделенные ячейки в строке и смежные с ними ячейки с данными. Снятие автофильтра производится той же командой. Так, после столбца с температурой, отсортированной в порядке убывания, на листе пустой столбец. Если бы он имел значения, то автофильтр был бы установлен и на него (даже если его не выделить).
Выбор одного из значений приводит к отображению только тех строк, которые в данном столбце содержат выбранное значение.
8
.3.10. Составление программы
В программе используется типовой алгоритм поиска максимума, минимума. Блок-схема алгоритма поиска максимума имеет вид (рис. 86).
О
Рис. 86. Блок-схема
алгоритма поиска
максимума.
тличием программы от этой блок-схемы является то, что цикл начинается с первого элемента (с первой ячейки), так как цикл предназначен для вычисления еще нескольких параметров. Программа достаточно простая и Вы с ней легко разберетесь. В главном цикле перебираются все ячейки с данными и накапливаются суммы температур (для вычисления среднего) за каждый день (первая колонка StDay(i, 1)). Кроме того, вычисляются максимальные (вторая колонка StDay(i, 2)) и минимальные (третья колонка StDay(i, 3)) значения за каждый день. В цикле использован интересный прием. Как узнать какой день обрабатывается? Для этого необходим счетчик – переменная Count, которая содержит номер дня, к которому относится текущее значение переменной Obj (текущая ячейка). Как только день обработан, переменная Count наращивает значение на единицу, а если это был последний день (т. е. Count стала равна 11 после добавления единицы), то переменную Count снова делают равной 1.
Рассмотрим участок, где формируется список дней.
For i = 1 To 10
' Для получения среднего необходимо разделить сумму на
' число лет наблюдения
' Заносим данные в список
ListBox1.AddItem "День " & i & ": " ' Первая колонка с названием дня
Для добавления новой строки в список используется метод AddItem (он позволяет занести данные в первую колонку списка, если их несколько). Объект ListBox1 имеет свойство ColumnCount равное четырем, а свойство ColumnWidth равное 49.95 пт; 35 пт; 35 пт; 35 пт (при задании свойства можно просто перечислять цифры через точку с запятой без букв "пт" – пункт). Первое свойство задает число колонок, а второе свойство их ширину (поэтому чисел четыре). Далее с помощью свойства List задаются значения еще трех колонок первой строки.
' Вторая колонка со средним значением
ListBox1.List(i – 1, 1) = Format(StDay(i, 1) / 30, "0.00")
Обратите внимание, что счет строк в объекте класса «Список» начинается с нуля, поэтому указывается номер строки как i –1. Следующий параметр отражает номер колонки – 1 (счет тоже начинается с нуля).
Sum = Sum + StDay(i, 1) / 30 ' накапливаем сумму средних значений
' Третья колонка с максимальным значением
ListBox1.List(i – 1, 2) = StDay(i, 2)
' Ищем максимальное значение
If Max < StDay(i, 2) Then
Max = StDay(i, 2)
End If
' Четвертая колонка с минимальным значением
ListBox1.List(i – 1, 3) = StDay(i, 3)
' Ищем минимальное значение
If Min > StDay(i, 3) Then
Min = StDay(i, 3)
End If
Next i