Ревчук И.Н. Практикум по MS Excel для экономистов

Подождите немного. Документ загружается.

161 162

Рисунок 149

Рисунок 150

Открыв список Дополнительные вычисления, выберем Приве-

денное отличие

(рис. 151).

Рисунок 151

В списке поле выбираем год, а в списке элемент – назад. На ри-

сунке 152 приведен результат применения формулы к сводной

таблице.

Задача 7.6. Пусть исходная база данных содержит следующие

поля: Фамилия, Вид страхования, План, Факт. Получить в

сводной таблице процент выполнения плана по страховым

суммам.

163 164

Решение

Построим сводную таблицу такого вида, как на рисунке

153.

Рисунок 152

Создадим в сводной таблице формулу для вычисления про-

цента выполнения плана по страховым платежам. Щелкнув

правой кнопкой в месте создания формулы, выполняем ко-

манду Формулы/Вычисляемое поле (рис.

154) и входим в

соответствующее окно диалога (рис.

155).

В поле ввода Имя введем название формулы – PRO-

CENT. В поле ввода Формула вводим формулу для вычис-

ления процента выполнения плана. Названия полей Факт и

План переносятся выбором из списка Поля и щелчком по

кнопке Добавить поле (рис.

156).

Рисунок 153

165 166

Рисунок 154

Рисунок 155

Рисунок 156

Далее следует выполнить форматирование полученных ре-

зультатов (в данном случае следует округлить полученные

при вычислениях проценты, например, до двух знаков после

запятой). На рисунке

157 приведена окончательная сводная

таблица с формулой.

Задача 7.7. Используя сводную таблицу задачи 7.1, получить

итоговые функции с максимальным и минимальным количе-

ством отработанных часов по всем проектам.

Решение. Воспользуемся таблицей на рисунке

158.

167 168

Рисунок 157

Рисунок 158

Запустив Мастер сводных таблиц, создадим два поля Сумма

по полю Часы (рис.

159).

Рисунок 159

Выполнив двойной щелчок по кнопке Сумма по полю

Часы, выберем функцию Минимум (рис.160), а затем

отформатируем поле ввода Имя. Аналогично поступаем с

кнопкой Сумма по полю Часы2 (рис. 161).

169 170

Рисунок 160

Рисунок 161

На рисунке 162 представлена полученная сводная таблица.

Рисунок 162

Задача 7.8. Имеются данные о выплатах за три месяца сотруд-

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

документ. Полученные результаты проиллюстрировать

графически.

Решение

1 вариант. Пусть данные расположены на одном листе в не-

скольких диапазонах. Этим диапазонам присвоены имена ян-

варь, февраль и март соответственно. На первом шаге мастера

сводных таблиц выбираем

вариант создания таблицы на основе

данных, находящихся в нескольких диапазонах консолидации

(рис. 163).

171 172

Рисунок 163

На рисунке 164 приведено второе окно диалога.

Рисунок 164

На рисунке 165 показано, как выбираются диапазоны таб-

лиц для консолидации.

Рисунок 165

Рисунок 166

173 174

На рисунке 167 приведен результат работы мастера свод-

ных таблиц.

Рисунок 167

2 вариант. Пусть данные расположены на трех листах в одной

рабочей книги. Этим листам присвоены имена январь, февраль

и март соответственно. Шаг 2б мастера сводных таблиц в

этом случае выглядит так, как на рисунке 168.

3 вариант. Пусть данные расположены в трех рабочих книгах.

Активной на момент построения сводной таблицы является

книга итог

1.xls. Остальные книги итог2.xls и итог3.xls также

открыты. Шаг 2б мастера сводных таблиц в этом случае вы-

глядит так, как на рисунке 169.

На рисунке 170 приведена диаграмма по результатам по-

строения сводной таблицы. Диаграмма построена с использо-

ванием соответствующей кнопки на панели инструментов

Сводная таблица.

Рисунок 168

Рисунок 169

175 176

Рисунок 170

Задачи для самостоятельного решения

1. База данных АВТОВЛАДЕЛЬЦЫ содержит следующие

поля: Фамилия, Имя, Отчество, Марка машины, Год выпуска,

Номерной знак, Серия, Цвет, Цена.

1.1. Есть ли в базе данных однофамильцы?

1.2. Есть ли в базе данных однофамильцы с совпадающими

именами?

1.3. Сгруппировать данные по году выпуска машин с за-

данным шагом.

1.4. Сколько машин

разных марок числится в базе данных?

1.5. Вывести максимальную, минимальную и среднюю це-

ны по маркам машин.

1.6. Вывести максимальную, минимальную и среднюю це-

ны по маркам машин и по годам выпуска, сгруппированным с

шагом 5 лет.

1.7. Сколько машин в базе данных с заданной серией?

1.8. Сколько машин числится за каждой

фамилией?

1.9. Вывести фамилии владельцев заданной марки машин.

1.10. Есть ли в базе данных несколько записей, относящих-

ся к машине с заданным номерным знаком и серией?

2. База данных ШКОЛЬНИКИ содержит следующие поля:

Фамилия, Имя, Отчество, IQ (коэффициент умственного раз-

витьия), Год обучения, Параллель, Рост, Оценка по алгебре,

Оценка по геометрии,

Оценка по физике, Оценка по физкуль-

туре.

2.1. Найти записи, относящиеся к 8 классам.

2.2. Найти записи, относящиеся к 8 и 9 классам.

2.3. Найти записи, относящиеся к 3, 5 и 9 классам.

2.4. Найти записи, относящиеся к отличникам.

2.5. Найти записи, в которых отсутствуют оценки ниже «4».

2.6. Есть ли в школе однофамильцы?

2.7.

Есть ли в школе однофамильцы в параллельных классах?

2.8. Есть ли в школе однофамильцы, которые учатся в одном

классе?

3. База данных БОЛЬНИЦА содержит следующие поля: Фами-

лия, Имя, Лечащий врач, Заболевание, Отделение, Дата поступ-

ления, Дата выписки, Исход.

3.1. Сколько больных у каждого врача?

3.2. Сколько больных в каждом отделении

3.3. Вывести количество записей с летальным исходом.

3.4. Вывести количество записей с летальным исходом, при-

ходящимся на зимние месяцы.

3.5. Вывести количество летальных исходов по каждому

врачу.

3.6. Найти записи, в которых срок лечения более 1 месяца.

3.7. Определить максимальный, минимальный и средний

срок лечения по каждому отделению.

4. База данных

СОТРУДНИКИ содержит следующие поля:

Фамилия, Имя, Отчество, Пол, Дата рождения (в формате

ДД.ММ.ГГ), Должность, Стаж, Оклад.

4.1. Есть ли в базе данных однофамильцы?

4.2. Вывести реальное штатное расписание по имеющейся

базе данных с разбивкой по половому признаку.

4.3. Вывести максимальный, минимальный и средний окла-

ды по должностям с

разбивкой по половому признаку.

4.4. Сколько сотрудников относится к людям предпенсион-

ного возраста (для женщин — 50–55 лет, для мужчин — 55–60

лет)?

4.5. Сколько сотрудников родилось в каждом месяце?

4.6. Есть ли сотрудники, у которых в этом году отмечается

50-летний юбилей?

177 178

5. База данных БАСКЕТБОЛИСТЫ содержит следующие по-

ля: Фамилия, Имя, Штат, Команда, Рост, Вес, Количество се-

зонов, Сыграно матчей, Подборы, Пасы, Перехваты, Очки.

5.1. Есть ли в базе данных однофамильцы?

5.2. Сколько очков набрала каждая команда?

5.3. Сколько команд у каждого штата?

5.4. Вывести максимальный, минимальный и средний рост

игроков каждой

команды.

5.5. Вывести отношение роста к весу для каждого игрока.

5.6. Вывести отношение роста к весу для каждой команды.

5.7. Вывести отношение роста к весу для всех команд

штата.

6. База данных ВИНА содержит следующие поля: Название,

Год урожая, Ёмкость, Цвет, Содержание сахара (сухое, полу-

сухое, сладкое, полусладкое), Содержание спирта,

Цена 1 бу-

тылки, Количество (в декалитрах).

6.1. Какого года вин в базе данных больше всего?

6.2. Вывести максимальную, минимальную и среднюю це-

ны всех вин, сгруппированных по содержанию сахара.

6.3. Вывести информацию о суммарном количестве вина

по годам.

6.4. Вывести информацию о суммарном количестве вина с

разбивкой по цвету вина и

содержанию спирта.

7. База данных КОМПЬЮТЕРНЫЕ ИГРЫ содержит следую-

щие поля: Место, Всего недель в чарте, Название, Производи-

тель, Категория, Самое высокое место, Цена.

7.1. Сколько игр принадлежит каждому производителю?

7.2. Сколько игр относится к каждой категории?

7.3. Вывести игры с максимальной, минимальной и сред-

ней ценой с разбивкой по

производителям.

7.4. Сколько игр у каждого производителя занимали

1–3 места?

7.5. Сколько игр по категориям занимали 1–3 места?

7.6. Сколько игр у каждого производителя занимали 1–3

места (с разбивкой по категориям)?

7.7. Вывести игры с максимальной, минимальной и сред-

ней ценой с разбивкой по категориям и производителям.

8. База данных КНИГИ содержит следующие

поля: Название

книги, Автор, Жанр, Год издания, Место издания, Издательство,

Количество страниц, Тираж, Спрос, Цена.

8.1. Сколько книг (по названиям) выпустило каждое изда-

тельство?.

8.2. Сколько книг (по названиям) относится к заданному

жанру?

8.3. Сгруппировать данные по тиражу с разбивкой по изда-

тельствам.

8.4. Вывести информацию по количеству страниц, опублико-

ванных каждым автором в каждом издательстве.

8.5. Найти книги, относящиеся к трем заданным

издательствам.

8.6. Вывести отношение тиража к количеству страниц по ка-

ждому жанру.

9. База данных ПОКУПКИ содержит следующие поля: Код за-

каза, Наименование товара, Характеристика, Цена, Дата оплаты,

Номер карточки, Фамилия владельца карточки, Срок действия

карточки.

9.1.

Какой товар пользуется наибольшим спросом?

9.2. Кто из покупателей сделал больше всего покупок?

9.3. Кто из покупателей сделал покупок на самую большую

сумму?

9.4. Сколько карточек у каждого покупателя ?

9.5. Вывести сведения о максимальной, минимальной и

средней цене покупки.

9.6. Использовалась ли одна и та же карточка разными поку-

пателями?

9.7.

Сгруппировать срок действия карточек по месяцам.

10. База данных ПРОГРАММЫ содержит следующие поля:

Программа, Код программы, Дата записи, Длительность (мин.),

Директор, Режиссер, Популярность (в баллах), Цена программы

(млн. руб.).

10.1. Определить, сколько программ относятся к каждому

директору.

10.2. Сгруппировать программы по дате записи по кварталам.

10.3. Работали ли вместе данный

директор и режиссер?

10.4. Вывести рейтинги наиболее и наименее популярных

программ каждого директора.

179 180

11. База данных ТЕСТ содержит следующие поля: Фамилия,

Возраст, Вес (кг), Рост (см), Индекс Кеттле, Вегетативный ин-

декс, Самочувствие, Настроение, Самооценка здоровья, Спри-

рометрия, Физическая работоспособность, Уровень физиче-

ского состояния.

11.1. Сгруппировать записи по возрастному диапазону с

шагом 3 года и весу (2 кг).

11.2. Для данного роста вывести наилучшие показатели по

спирометрии и физической работоспособности.

11.3. Вычислить отношение роста к весу и вывести этот

показатель с разбивкой по самооценке и уровню физического

состояния.

11.4. Есть в базе данных однофамильцы?

11.5. Есть ли в базе спортсмены с одинаковым весом и

ростом?

12. База данных ФИРМЫ содержит следующие поля: Фирма,

Форма собственности, Директор, Адрес

, Дата регистрации,

Номер счета, Банк, Годовой оборот, Вид деятельности.

12.1. Сколько фирм с заданной формой собственности?

12.2. Есть ли фирмы с одинаковыми названиями?

12.3. Есть ли фирмы, месяц регистрации которых прихо-

дится на март?

12.4. Найти названия фирм, год создания которых находит-

ся в заданном диапазоне.

12.5. Есть ли несколько

фирм с одним адресом?

12.6. Есть ли в списке фирмы, директором которых являет-

ся один и тот же человек?

13. База данных HAMMER THROW 2002 WR содержит сле-

дующие поля: Бросок, Атлет, Страна, Дата рождения, Занятое

место, Место выполнения броска, Дата проведения броска.

13.1. Сколько записей относится к летним месяцам выпол-

нения броска?

13.2. Сколько

записей, возраст спортсмена в которых нахо-

дится в заданном диапазоне?

13.3. Вывести максимальный и минимальный броски по

странам с разбивкой по годам.

13.4. Вывести фамилии и результаты белорусских спорт-

сменов.

13.5. Сколько спортсменов России, Беларуси и Украины вы-

полнили бросок в заданном диапазоне (с разбивкой по странам)?

14. База данных

КРУПНЕЙШИЕ ОЗЕРА ПЛАНЕТЫ содержит

следующие поля: Название, Часть света, Страна, Площадь,

Площадь в пределах страны, Наибольшая глубина, Наибольшая

глубина в пределах страны, Средняя глубина.

14.1. Сколько озер в каждой части света?

14.2. Сколько озер в каждой части света с разбивкой по стра-

нам?

14.3. Вывести максимальную, минимальную и среднюю глу-

бины

озер с разбивкой по частям света.

14.4. Определить процентное отношение площади озера в

пределах страны к общей его площади с разбивкой по частям

света.

14.5. Найти записи, относящиеся к озерам, расположенным в

Канаде.

15. База данных КРУПНЕЙШИЕ ОСТРОВА ЗЕМЛИ содержит

следующие поля: Название, Часть света, Принадлежность госу-

дарству, Площадь, Численность

населения.

15.1. Сколько островов находится в каждой части света?

15.2. Сколько записей относится к островам государств Ев-

ропы или Африки, площадь которых находится в заданных

пределах.

15.3. Вывести максимальную, минимальную и среднюю

площади островов Европы и Азии.

15.4. Сколько островов в каждой стране?

15.5. Есть ли в базе данных острова с

одинаковым названием?

16. База данных СТОЛИЦЫ содержит следующие поля: Назва-

ние, Часть света, Страна, Численность населения, Занимаемая

площадь, Средний возраст жителей, Количество мужчин старше

18 лет, Количество женщин старше 18 лет, Средняя стоимость

1м

жилой площади, Бюджет города.

16.1. Сколько стран в каждой части света?

16.2. Вывести максимальную, минимальную и среднюю

стоимость 1 м

жилья в разных частях света.

16.3. Сгруппировать население столиц по численности с не-

которым шагом, выполнив разбивку по частям света и половому

признаку.