Задания для самостоятельной работы. В рабочей книге каждый лист отвести для решения одной задачи
В рабочей книге каждый лист отвести для решения одной задачи. Каждому листу дать название задание 1, задание 2, …, задание 7.
Отчет по выполненной работе должен содержать распечатку каждой отформатированной расчетной таблицы и таблицы с включенными формулами.
Задание 1
В таблице приведены данные о количестве легковых автомобилей, выпущенных отечественными автомобильными заводами в первом полугодии 2001 года (задание 1 практической работы 2). Выделить полужирным курсивом выпуск автомобилей, превышающий среднее значение за первый квартал. Выделить тройку лучших месяцев среднего выпуска автомобилей с помощью разной степени заливки ячеек серым цветом.
Задание 2
Заработная плата каждого из 12 работников фирмы за каждый месяц первого квартала записана в таблице задания 2 практической работы 2. Выделить полужирным белым шрифтом на черном фоне максимальную и минимальную зарплату. Выделить полужирным курсивом заработную плату, не превышающую среднего значения.
Задание 3
Известны данные о количестве осадков, выпавших за каждый день июля (задание 10 второй практической работы). Выделить красным полужирным курсивом величину осадков, превышающих среднее значение.
Задание 4
Известны оценки по Информатике каждого из 15 студентов (задание 8 второй практической работы). Выделить другим цветом ячейки с фамилиями студентов, имеющих оценку 5. Для решения задачи следует выделить диапазон ячеек, содержащий фамилии, и задать условный формат формулой, ссылающейся на ячейку, принимающую конкретное значение.
Задание 5
Известен возраст 30 человек (задание 9 второй практической работы). Выделить серым цветом строки (Ф.И.О. и возраст), содержащие информацию о претендентах для приема на работу в фирму, если по условиям возраст претендентов не превышает 35 лет. Здесь следует задавать условия отдельно для каждого столбца.
Задание 6
Известна сумма баллов, набранная студентами при сдаче контрольной точки (задание 10 второй практической работы). Выделить серым цветом строки с информацией о студентах, не сдавших контрольную точку, и полученных ими, если минимальное количество баллов равно 60.
Задание 7
По результатам сданной сессии студентам начисляется стипендия (задание 5 четвертой практической работы). Выделить строки различными шрифтами и видами заливки для студентов, получающих максимальную, среднюю и минимальную стипендии.
Ссылки и массивы
Цель: освоение средств извлечения информации из диапазонов данных.
Краткие сведения
Очень часто в практической работе встречаются задачи, для решения которых требуется использовать справочную таблицу. Например, по заданному номеру телефона найти фамилию или по названию государства - его столицу. Для решения подобных задач следует составить справочную таблицу, в первом столбце которой расположены поисковые значения (все номера телефонов), а во втором – фамилии абонентов. Специальная функция ВПР осуществит поиск в справочной таблице значений, соответствующих введенному вами аргументу и автоматически подставит найденное значение в той же строке из указанного второго столбца таблицы.
Синтаксис функции ВПР (вертикальный поисковый ряд).
ВПР(искомое значение; таблица; номер столбца; интервальный просмотр):
- Искомое значение – это значение, которое должно быть найдено в первом столбце массива. Оно может быть значением, ссылкой или текстовой строкой.
- Таблица – таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала.
- Номер столбца – это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение.
- Интервальный просмотр – в это окно вводится 0(ЛОЖЬ), если необходимо точное соответствие или 1(ИСТИНА), если соответствие приближенное. По умолчанию устанавливается приближенное соответствие, при котором, если точное значение не найдено, возвращается максимальное из значений, меньших, чем искомое. Если введена ИСТИНА, то значения в первом столбце массива должны быть отсортированы в возрастающем порядке.
Например, методист учебного отдела может работать с документами, используя справочную таблицу, в первой колонке которой указано наименование дисциплины, а во второй – количество учебных часов. Как только Методист введет в основную таблицу название дисциплины, компьютер автоматически подставит количество часов по этому предмету.
Итак, составьте на отдельном рабочем листе (в нашем примере на первом рабочем листе) таблицу следующего вида, как показано на рисунке 30. Затем перейдите на другой рабочий лист (например, второй лист) и составьте выписку из ведомости на учащегося, как показано на рисунке 31.
Для того чтобы получить в колонке Количество часов число часов, из справочной таблицы вызовите Мастер функции и категории Ссылки и массивы, выберите функцию ВПР. Заполните аргументы функции ВПР, как показано на рисунке 33, и нажмите ОК.
Скопируйте функцию из ячейки В3 в В4.
По аналогии с функцией ВПР работает функция ГПР (горизонтальный поисковый ряд), но в отличие от нее ищет значение в верхней строке массива и выводит его в заданном столбце.
Функция ПОИСКПОЗ очень похожа на функцию ВПР, но она возвращает позицию искомого значения в массиве, а не само значение. Синтаксис: ПОИСКПОЗ(искомое_значение; массив; тип_сопоставления). Искомое_значение — значение, используемое при поиске значения в таблице. Просматриваемый_массив — блок, состоящий из одного столбца или одной строки.. Тип_сопоставления — число -1, 0 или 1. Функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение в неупорядоченном массиве.
Например, дополним таблицу Выписка успеваемость колонкой «Номер в учебном плане», для чего введем это название в ячейку D3, а в ячейку D3 формулу.
= ПОИСКПОЗ(А3;ЛИСТ1!$А$2:$А$5;0).
Формула возвращает значение 3, так как содержимое ячейки А3 Физика – третий элемент в массиве ЛИСТ1!$А$2:$А$5 (Математика, Информатика, Физика, Философия).
| 
|
| Рис. 30. Справочная таблица на первом листе | Рис. 31. Основная таблица, в которой присутствуют справочные данные |
Рис. 32. Аргументы функции ВПР