ИНФОРМАТИКА. Лабораторные работы 5,6,7, 8-EXCEL


Лабораторная работа №5. Microsoft Excel, настройка рабочего места, простейшие вычисления, форматирование и редактирование таблицыЦель работы
Изучение методов настройки внешнего вида Microsoft Excel.
Научиться настраивать рабочее место (включать/отключать элементы окна, формировать панели инструментов, устанавливать режимы просмотра текста);
Освоение автоматических возможностей Microsoft Excel.
Изучение методов форматирования текста.
Освоение приемов создания своего формата ячейки.
Задание случайных чисел.
Простейшие вычисления.
1 Настройка рабочего места Microsoft Excel
Задания
Запустите табличный процессор Microsoft Excel, затем последовательно выполняйте все пункты задания. Во время выполнения заданий кратко записывайте в тетрадь последовательность действий (этот текст можно набирать в текстовом редакторе Microsoft Word).
Скройте/покажите полосы прокрутки. Для этого необходимо выполнить команду: Кнопка Offise -параметры Excel-раздел Дополнительно-раздел Показывать параметры для следующей книги.
Скройте/покажите ярлычки листов. Для этого необходимо выполнить команду: Кнопка Offise -параметры Excel-раздел Дополнительно-раздел Показывать параметры для следующей книги.
Скройте/покажите названия строк и столбцов. Для этого необходимо выполнить команду: Кнопка Offise -параметры Excel-раздел Дополнительно-раздел Показывать параметры для следующего листа.
Сделайте обозначение заголовки столбцов цифровыми. Для этого необходимо выполнить команду: Кнопка Offise -параметры Excel-раздел Формулы-раздел Работа с формулами, отметить флажок Стиль ссылок R1C1. Верните буквенное обозначение столбцов.
Добавьте на панель Быстрого доступа кнопку (удалить столбцы) и кнопку (удалить строки). Для этого надо выполнить команду Кнопка Offise -параметры Excel-раздел Настройка. Уберите с панели кнопку.
В одну из ячеек (например, G10) наберите текст: х2-у2=(х+у)*(х-у). Для этого необходимо использовать команду Главная – Шрифт- флажок Надстрочный
Установите масштаб просмотра документа 100%, 50%, 10%.
Сделайте видимыми границы страницы. Для этого выполните команду Кнопка Offise -параметры Excel-раздел Дополнительно-раздел Показывать параметры для следующего листа.
Маркер разбиения
Добавьте разрыв страницы после некоторой ячейки (например, G20). Для этого поставьте курсор в ячейку H21 и выполните команду Разметка страницы – блок Параметры страницы - Разрывы, в результате чего лист разобьется на 4 части – A1:G20, H21:K30, A21:G30 и H1:К20). Удалите разрыв листа.
С помощью маркера разбиения,
расположенного на горизонтальной или вертикальной полосе прокрутки, разделите лист на 2 или 4 части.
Скройте столбцы C и D. Для этого необходимо выделить эти два столбца и в контекстном меню выбрать пункт меню Скрыть. Верните на экран отображение этих столбцов.
Установите у каждого ярлыка листа свой цвет.
На новом листе в ячейку A1 введите «х», в ячейку B1 – «y». В ячейки с А2 до А5 введите значения х с некоторым шагом (например, числа 1; 1,5; 2; 2,5). Выделите ячейки A2:A26 и заполните пустые ячейки с помощью арифметической прогрессии (команда Главная – блок Редактирование - кнопка Заполнить - Прогрессия).
В ячейках B2:B50 по некоторой формуле вычислите значения y, который зависит от х. Это может быть, например, функция у=2х-4.
Выделите ячейки A1:B50 и придайте им вид законченной таблицы с помощью команды Формат (меню Главная – блок Ячейки – Формат).
С помощью маркера разбиения разделите таблицу на две части после 5-й строки. Закрепите области с помощью команды Вид - Окно – Закрепить области. С помощью вертикальной полосы прокрутки просмотрите оставшуюся часть таблицы. Снимите закрепление областей с помощью команды Вид - Окно – Снять закрепление областей.
С помощью возможностей предварительного просмотра таблицы перед печатью продемонстрируйте, как будет выглядеть Ваша таблица, при этом необходимо заголовок таблицы показывать на каждой странице. Для этого выполните команду Разметка страницы – блок Параметры страницы – вкладка Лист, в дорожке Сквозные строки отметить диапазон заголовка таблицы.
Создайте свой элемент автозамены. Для этого: Главная – блок редактирования, кнопка
2 Простейшие вычисления, форматирование и редактирование таблицы
Известны показатели работы завода. Они приведены в таблице 4.
Таблица 4
A B C D E F G H I
1 Код изделия Наименование изделия Количество проданных изделий, шт. Цена
Объем выручки, в руб. Недополученная выручка, в %
2 план факт в валюте в рублях план факт Требуется
Создать таблицу 4, заполнить ее и выполнить задания.
Заполнение таблицы
1 Курс валюты определяется в ячейке К1 по формуле:
30 + случайное число от 0,03 до 0,08 (т.е. от 3% до 8%).
Для вычисления использовать функцию СЛЧИС.
2 Колонки A и B заполнить произвольно (не менее 6-7 записей).
3 Поля «Количество проданных изделий, шт. (план)» и «Количество проданных изделий, шт. (факт)» вычисляются по формуле : случайное число от 100 до 500.
Для вычисления использовать функцию СЛЧИС.
4 Поле «Цена (в валюте)» вычисляется как случайное число из диапазона от 100 до 2250. Для вычисления использовать функцию СЛЧИС.
5 Поле «Цена (в рублях)» вычисляется : «Цена (в валюте)»*«Курс валюты».
6 Поле «Объем выручки (план)» вычисляется : «Количество изделий (план)»* «Цена (в рублях)».
7 Поле «Объем выручки (факт)» вычисляется : «Количество изделий (факт)»* «Цена (в рублях)».
8 Поле «Недополученная выручка» вычисляется по формуле : («Объем выручки (факт)» - «Объем выручки (план)») / «Объем выручки (факт)»
Задания
Между колонками H и I необходимо вставить колонку «Фактический объем выручки, в валюте» и вычислить ее по формуле:
«Объем выручки в руб. (факт)» * «Курс валют».
Задайте собственный формат для граф «Цена (в рублях)» и «Цена (в валюте)», (например, чтобы были рубли и копейки, тысячи рублей).
Выделить заголовок таблицы другим цветом и изменить шрифт.
Поменяйте вид записи заголовка «Количество проданных изделий, шт.» на вертикальное.
В конец таблицы ввести строчку «Итого», в которой вычислить сумму для числовых полей. Для вычисления использовать функцию СУММ.
По полям «Количество проданных изделий, шт. (план)», «Количество проданных изделий, шт. (факт)» определить максимальное значение. Для вычисления использовать функцию МАКС.
По полю «Недополученная выручка, в %» определить среднее значение. Для вычисления использовать функцию СРЗНАЧ.
8 По всем числовым полям полученные результаты округлить с точностью до двух знаков (с помощью формата), по полям «Количество изделий (план)» и «Количество изделий (факт)» округлить до целых чисел. Для этого использовать команду ФОРМАТ → ЯЧЕЙКИ → ЧИСЛО.
Контрольные вопросы
Почему необходимо уметь настраивать свое рабочее место?
Когда необходимо использовать автоформат таблиц?
Каким образом работает автозаполнение?
Каким образом с помощью автозамены можно исправлять ошибки в наборе текста?
Назовите способы выделения частей таблицы.
Как скопировать или переместить выделенный фрагмент?
Как вставить дополнительный столбец, строку или ячейку в таблицу?
Как осуществляется удаление строки, столбца, ячеек, рабочего листа в Excel?
Как задается пользовательский формат? Как задать условный формат ячеек?
В чем отличие относительной и абсолютной ссылки?
Лабораторная работа №6. Логические функции в EXCELЦель работы
Изучение логических функций ЕСЛИ, И, ИЛИ.
Освоение приемов работы со вложенными функциями.
Работа с функциями СЧЁТЕСЛИ, СУММЕСЛИ.
Данные
Известны данные о посещении зрителями кинотеатров. Они приведены в таблице 5.
Таблица 5
A B С D E F G
1 Отчетная ведомость
2 Название фильма Жанр Цена билета, в руб. Кинотеатр Количество билетов Скидка, в % К оплате, в руб.
3 Вийприключения … … … … … … … …
20 Маленький Мук мультфильм Требуется
Создать таблицу 4, заполнить ее и выполнить задания.
Заполнение таблицы
1 Колонки А, B, D заполнить произвольно (не менее 15-20 записей).
2 Поле "Цена билета" вычислить по формуле : случайное число от 30 до 200. Для вычисления использовать функции СЛЧИС, ЦЕЛОЕ.
3 Поле "Количество билетов" – целое случайное число от 10 до 100. Для вычисления использовать функции СЛЧИС, ОКРУГЛ.
4 Поле "Скидка" : случайное число от 0,03 до 0,50. Для вычисления использовать функции СЛЧИС, ОКРУГЛ.
5 Поле "К оплате" вычислить по формуле :«Цена билета»*«Количество билетов»*(1 - «Скидка»).
Задания
1 В конец таблицы ввести еще одну колонку «Группа», в этой колонке поставить число 1 если на фильм идет более 3-х человек (т.е. куплено больше трех билетов), в противном случае поставить знак 0 (использовать функцию ЕСЛИ).
В конец таблицы ввести еще одну колонку "Показатель 1" и заполнить ее по следующему правилу :если жанр "комедия" или "приключение", то поставить 1, иначе - 0.
Для вычисления использовать функции ЕСЛИ и ИЛИ.
В конец таблицы ввести еще одну колонку "Показатель 2" и заполнить ее по следующему правилу :если количество билетов более 40, но менее 80, то вывести наименование фильма, в остальных случаях вывести «НЕТ».
Для вычисления использовать функции ЕСЛИ, И.
В конец таблицы ввести еще одну колонку "Показатель 3" и заполнить ее по следующему правилу:
Если количество билетов больше 50, а скидка больше 20%, то записать название кинотеатра, иначе поставить прочерк.
В столбцах «Цена билета» и «К оплате» задайте денежный формат, числа в столбце «Количество билетов» сделайте целыми.
При выполнении заданий с 5-го по 8-ое необходимо внизу таблицы вводить новые строки и давать им соответствующее названия. Для вычисления ответов на эти задания необходимо использовать функции СУММЕСЛИ, СЧЕТЕСЛИ, ЕСЛИ.
Посчитать двумя способами сколько раз посещались сеансы с ценой билета более 100 руб. (первый способ – с помощью функции СУММЕСЛИ, второй – СЧЕТЕСЛИ).
Посчитать сколько раз посещались комедии.
Определить суммарное количество билетов, проданных на фантастические фильмы и комедии по цене менее 100 рублей.
Определить, какое число фильмов посещено группой зрителей (более 5 человек) со скидкой.
Определить, каково общее количество билетов, проданных на приключения и фантастику.
Контрольные вопросы
Что понимают под функцией в Excel?
Перечислите основные категории функций в Excel.
В каких случаях следует использовать абсолютную, а в каких относительную адресацию?
Как пользоваться справочной информацией Мастера функций?
Как записываются вложенные функции с помощью Мастера функций?
Какие логические функции Вы знаете?
Перечислите правила записи функций.
Лабораторная работа №7. Построение диаграмм, графиковЦель работы
Изучение экономических функций и проведение расчетов с их применением.
Построение диаграмм, графиков.
Изменение отдельных элементов диаграмм, графиков.
Печать таблиц, графиков и диаграмм.
Вариант 7.1Данные
Имеются данные о сумме вкладов по центральной сберегательной кассе города в декабре 1992 года (тыс. руб.) (таблица 6). Необходимо проследить динамику изменения суммы вкладов в течение 4-ех лет (таблица 7).
Таблица 6
A B
1 Фамилия И.О. вкладчика Сумма вклада на декабрь 1992 года
Требуется
Разместить таблицы 6 и 7 на различных листах.
Переименовать листы соответственно во «Вклад» и «Динамика».
Выполнить задания.
Таблица 7
A B C D E
1 Месяц 1993 1994 1995 1996
2 Январь 3 Февраль 4 Март 5 Апрель 6 Май 7 Июнь 8 Июль 9 Август 10 Сентябрь 11 Октябрь 12 Ноябрь 13 Декабрь Заполнение таблиц
Для таблицы 6.
Колонка A заполняется произвольным образом. Количество записей - не менее 20.
Колонка B заполняется целыми случайными числами от 200 до 300. При заполнении использовать функции СЛЧИС, ОКРУГЛ.
Для таблицы 7.
У колонок B, C, D, E каждая строка заполняются по своей формуле, которая высчитывает сумму вкладов при начислении от 2% до 10% годовых, причем процент меняется каждый месяц (т.е. является случайным числом) и начисляется в конце каждого месяца. При заполнении таблицы сначала заполняется помесячно 1993 год, затем 1994 год и т.д. При вычислении использовать функции БС, ОКРУГЛ, СЛЧИС, СУММ. Про функцию БС смотрите в приложениях А и Б.
Задания (выполняются на листе «Динамика»)
Для колонок B, C, D, E задайте денежный формат.
Построить плоский график изменения суммы вклада в 1993 и 1996 годах (на одном графике оба года). При построении графика ему необходимо дать название «Динамика изменения суммы вклада» и дать соответствующее название обоим осям.
Построить линейчатую объемную диаграмму изменения суммы вклада в 1993, 1994 и 1996 годах. При построении диаграммы необходимо обозначение 1996 года сделать красным, 1994 - синим, а 1993 - фиолетовым. Диаграмме необходимо дать название, а также дать название обоим осям.
Построить круговую (плоскую или объемную - по выбору) диаграмму для 1995 года. Для данной диаграммы изменить цвет фона диаграммы, цвет фона легенды.
Вариант 7.2Данные
Имеются данные о сумме вкладов по центральной сберегательной кассе города в декабре 1992 года (тыс. руб.) (таблица 8). Необходимо проследить динамику изменения суммы чистого текущего объема вкладов в банке в течении 4-ех лет, если считать что ежегодно в один месяц снимается одинаковая сумма (таблица 9).
Таблица 8
A B
1 Фамилия И.О. вкладчика Сумма вклада на декабрь 1992 года
Таблица 9
A B C D E F
1 Месяц Снято, руб. 1993 1994 1995 1996
2 Январь 3 Февраль 4 Март 5 Апрель 6 Май7 Июнь 8 Июль 9 Август 10 Сентябрь 11 Октябрь 12 Ноябрь 13 Декабрь Требуется
Разместить таблицы 8 и 9 на различных листах.
Переименовать листы соответственно во «Вклад» и «Динамика».
Выполнить задания.
Заполнение таблиц
Для таблицы 8.
Колонка A заполняется произвольным образом. Количество записей - не менее 15.
Колонка B заполняется целыми числами от 240 до 380. При заполнении использовать функции СЛЧИС, ОКРУГЛ.
Для таблицы 9.
Колонка «Снято» заполняется случайными числами от 70 до 100.
У колонок C, D, E, F каждая строка заполняются по своей формуле, которая высчитывает сумму чистого текущего объема вкладов при начислении в течении всех 4-ех лет 6% годовых, которые начисляются в конце каждого месяца и при снятии в течении 4-ех лет указанной в колонке B суммы. При заполнении таблицы сначала заполняется помесячно 1993 год, затем 1994 год и т.д. При вычислении использовать функции ЧПС. Про функцию ЧПС смотрите в приложениях А и Б.
Задания (выполняются на листе «Динамика»)
Для колонок B, C, D, E задайте денежный формат.
Построить линейчатую плоскую диаграмму изменения суммы чистого текущего объема вклада в 1993 и 1996 годах. (на одном графике оба года). При построении графика ему необходимо дать название «Динамика изменения суммы чистого вклада» и дать соответствующее название обоим осям.
Построить плоский график изменения суммы чистого текущего объема вклада в 1993, 1995 и 1996 годах. При построении диаграммы необходимо обозначение 1996 года сделать красным, 1995 – зеленым, а 1993 – черным. Диаграмме необходимо дать название, а также дать название обоим осям.
Построить круговую (плоскую или объемную – по выбору) диаграмму для 1994 года. Для данной диаграммы изменить цвет фона диаграммы, цвет фона легенды.
Вариант 7.3Данные
Имеются следующие данные о стоимости фондов завода и величине амортизации этих фондов (табл. 10). Необходимо по данным таблицы 10 составить таблицу 11.
Таблица 10
A B C D E
1 № завода Наименование завода Стоимость фондов завода в 1980 году Стоимость фондов завода в 1990 году Величина амортизации за этот период
2 010 Литейный Таблица 11
A B C D E F
1 № завода Величина амортизации в 1982 году Величина амортизации в 1984 году Величина амортизации в 1986 году Величина амортизации в 1988 году Величина амортизации в 1990 году
2 010 Требуется
Разместить таблицы 10 и 141на различных листах.
Переименовать листы соответственно в «Сведения» и «Величина амортизации».
Выполнить задания.
Заполнение таблиц
Для таблицы 10.
Колонка A, B заполняются произвольным образом.
Колонка C заполняется целыми числами от 6700 до 10500. При заполнении использовать функции СЛЧИС, ОКРУГЛ.
Колонка D заполняется целыми числами от 2000 до 5500. При заполнении использовать функции СЛЧИС, ОКРУГЛ.
Колонка Е высчитывается методом уменьшающего остатка. Для этого использовать функцию ДДОБ (см. в приложение Б).
Для таблицы 11.
Колонка A – копии колонки А таблицы 13.
У колонок B, C, D, E каждая колонка заполняются по своей формуле. При вычислении использовать функцию ДДОБ. Про функцию ДДОБ смотрите в приложении Б.
Задания (выполняются на листе «Величина амортизации»)
Для колонок B, C, D, E задайте денежный формат.
Построить плоский график изменения амортизационных отчислений в 1982 и 1990 годах (на одном графике оба года). При построении графика ему необходимо дать название «Динамика изменения амортизации» и дать соответствующее название обоим осям.
Построить линейчатую объемную гистограмму изменения амортизационных отчислений в 1984, 1986 и 1990 годах. При построении гистограммы необходимо обозначение 1986 года сделать красным, 1990 – голубым, а 1984 – фиолетовым. Диаграмме необходимо дать название, а также дать название обоим осям.
Построить круговую (плоскую или объемную – по выбору) диаграмму для 1982 года. Для данной диаграммы изменить цвет фона диаграммы, цвет фона легенды.
Вариант 7.4Данные
Имеются следующие данные стоимости фондов предприятий в 1980 и в 1990 годах (табл. 12). Необходимо по данным таблицы 12 определить величину амортизации на одном из предприятий в течение 10 лет различными способами и заполнить таблицу 13.
Таблица 12
A B C D
1 Номер предприятия Наименование предприятия Стоимость фондов предприятия в 1980 году Стоимость фондов предприятия в 1990 году
2 Таблица 13
A B C D
1 Номер предприятия Наименование предприятия 2 Год Расчет АПЛ Расчет АМЧ Расчет ДДОБ
3 1981 4 1982 5 1983 6 1984 7 1985 8 1986 9 1987 10 1988 11 1989 12 1990 Требуется
Разместить таблицы 12 и 13 на различных листах.
Переименовать листы соответственно в «Данные» и «Расчет».
Выполнить задания.
Заполнение таблиц
Для таблицы 12.
Колонки A, B заполняются произвольно 5-6 записей.
Колонка C заполняется целыми числами от 6700 до 10500. При заполнении использовать функции СЛЧИС, ОКРУГЛ.
Колонка D заполняется целыми числами от 2000 до 4700. При заполнении использовать функции СЛЧИС, ОКРУГЛ.
Для таблицы 13.
В ячейку B1 вводится любой номер предприятия из номеров, имеющихся в таблице 15. В ячейку D1 с помощью функции ВПР переносится наименование соответствующего предприятия из таблицы 15.
Колонки D, E, F вычисляется по формулам, использующим соответственно функции АПЛ, АМЧ, ДДОБ, при этом Стоимость и Ликвидная_стоимость берутся из таблицы 15 с помощью функции ВПР. Про функции АПЛ, АМЧ и ДДОБ смотрите в приложениях А и Б.
Задания (выполняются на листе «Расчет»)
Построить плоский график "Расчет АПЛ", "Расчет ДДОБ" (на одном графике оба). При построении графика ему необходимо дать название «Показатели» и дать соответствующее название обоим осям.
Построить линейчатую объемную гистограмму сравнения разных способов расчета амортизации за 4-й, 6-й и 10-й года. При построении гистограммы необходимо обозначение 4-о года сделать желтым, 6-о года - голубым, а 10-о - зеленым. Диаграмме необходимо дать название, а также дать название обоим осям.
Построить круговую (плоскую или объемную - по выбору) диаграмму для "Расчет АМЧ". Для данной диаграммы изменить цвет фона диаграммы, цвет фона легенды.
Вариант 7.5Данные
В таблице 14 приведена схема погашения займа в 70000 руб., выданного сроком на 3 года под 17 % годовых.
Таблица 14
A B C D E F
1 Год Сумма займа на начало года Общая сумма платежа Платежи по процентам Сумма основного платежа по займу Сумма займа на конец года
2 1 70000 3 2 4 3 5 Итого Требуется
Разместить таблицу 14 на новом листе.
Переименовать лист в «Расчет 5».
Выполнить задания.
Заполнение таблицы
Колонка «Общая сумма платежа» рассчитывается для каждого года отдельно по формуле для расчета постоянных периодических выплат. При расчете необходимо учитывать снижение суммы займа с каждым годом. При расчете воспользоваться функцией ПЛТ. Про функцию ПЛТ смотрите в приложениях А и Б.
Колонка «Платежи по процентам» рассчитывается для каждого года отдельно по формуле для расчета платежей по процентам. При расчете воспользоваться функцией ПРПЛТ. Про функцию ПРПЛТ смотрите в приложениях А и Б.
Колонка «Сумма основного платежа по займу» рассчитывается для каждого года отдельно по формуле для расчета основных платежей по займу. При расчете воспользоваться функцией ОСПЛТ. Про функцию ОСПЛТ смотрите в приложениях А и Б.
Колонка «Сумма займа на конец года» рассчитывается по формуле :«Сумма займа на начало года» - «Сумма основного платежа по займу»
Колонка «Сумма займа на начало года» для 2-ого и 3-его года заполняется данными из колонки «Сумма займа на конец года», взятыми из предыдущего года, т. е. 1-ого и 2-ого.
Строчка «Итого» высчитывается для колонок «Общая сумма платежа», «Платежи по процентам» и «Сумма основного платежа по займу» как сумма стоящих в них цифр.
Задания (выполняются на листе «Расчет 5»)
Для колонок B, C, D, E и F задайте денежный формат.
Построить плоский график "Сумма займа на начало года", "Сумма займа на конец года" (на одном графике оба). При построении графика ему необходимо дать название «Данные по займу» и дать соответствующее название обоим осям.
Построить линейчатую объемную гистограмму "Сумма займа на начало года", «Сумма займа на конец года», «Сумма основного платежа по займу». При построении гистограммы необходимо обозначение "Суммы займа на начало года" сделать синим цветом, "Сумма займа на конец года" - голубым, а "Сумма основного платежа по займу" - фиолетовым. Диаграмме необходимо дать название, а также дать название обоим осям.
Построить круговую (плоскую или объемную - по выбору) диаграмму для колонки "Платежи по процентам". Для данной диаграммы изменить цвет фона диаграммы, цвет фона легенды.
Вариант 7.6Данные
В таблице 15 приведены данные по нескольким проектам. Необходимо ответить на вопрос : какие проекты целесообразны ?
Таблица 15
A B C D E F
1 Показатель 1-ый проект 2-ой проект 3-ий проект 4-ый проект 5-ый проект
2 Затраты 3 Доход в 1-ый год 4 Доход во 2-ой год 5 Доход в 3-ий год 6 Доход в 4-ый год 7 Доход в 5-ый год 8 Рыночная норма дохода 9 Внутренняя скорость оборота инвестиций Требуется
Разместить таблицу 15 на новом листе.
Переименовать лист в «Расчет 5».
Внизу таблицы ввести дополнительную строку и в этой строке вывести какие проекты целесообразны, т.е. внутренняя скорость оборота инвестиций для этих проектов больше рыночной нормы дохода.
Заполнение таблицы
Строка «Затраты» рассчитывается по формуле: отрицательное целое случайное число от 50000 до 70000. При расчете воспользоваться функцией ОКРУГЛ, СЛЧИС.
Строки «Доход» заполняются положительными случайными целыми числами от 10000 до 20000. При заполнении воспользоваться функциями ЦЕЛОЕ, СЛЧИС.
Строка «Рыночная норма дохода» заполняется числами от 10 до 12.
Для строки «Рыночная норма дохода» ввести формат чисел, округляющий их до двух знаков после запятой и подписывающий около каждого числа знак «%».
Строка «Внутренняя скорость оборота инвестиций» высчитывается по формуле для расчета внутренней нормы доходности инвестиций. При вычислении использовать функцию ВСД. Про функцию ВСД смотрите в приложениях А и Б.
Задания (выполняются на листе «Расчет 5»)
Для колонок B, C, D, E, F задайте денежный формат.
Построить плоский график доходов в 1-ый и во 2-ой год (на одном графике оба года). При построении графика ему необходимо дать название «Динамика изменения доходов» и дать соответствующее название обоим осям.
Построить линейчатую плоскую диаграмму изменения суммы доходов в 1-ый, 2-ой и 5-ый годах. При построении диаграммы необходимо обозначение 1-ого года сделать красным, 2-ого - зеленым, а 3-его - черным. Диаграмме необходимо дать название, а также дать название обоим осям.
Построить круговую (плоскую или объемную - по выбору) диаграмму дохода для 4-ого года. Для данной диаграммы изменить цвет фона диаграммы, цвет фона легенды.
Вариант 7.7Данные
В таблице 16 приведены данные по годовой ставке помещения по операциям с ценными бумагами (облигациями) при заданной купонной ставке и разности курсов покупки и погашения за указанный период действия ценной бумаги. Ценные бумаги приобретены (Дата_согл) 25.04.2003, купонный доход выплачивается с периодичностью (Частота) – раз в полугодие. Предполагаемая дата погашения облигации (Дата_вступл_в_силу) – 27.04.2005 по курсу (Погашение) 130 руб. Необходимо произвести анализ доходности облигаций при разных курсах покупки (Цене) с учетом того, что в году 365 дней (Базис).
Таблица 16
A B C D E F
1 Купон, % Курс покупки
2 70 90 110 130 150
3 0 % 4 10 % 5 20 % 6 30 % 7 40 % Требуется
Разместить таблицу 16 на новом листе.
Переименовать лист в «Расчет 5».
Заполнение таблицы
Каждая из колонок B, C, D, E, F таблицы заполняется по своей формуле. При вычислении использовать функции ДОХОД, ДАТА. Про функцию ДОХОД смотрите в приложении Б.
Задания (выполняются на листе «Расчет 5»)
Построить плоский график изменения доходов при операциях с ценными бумагами при курсе покупки 90, 110 и 150 (на одном графике). При построении графика ему необходимо дать название «Изменение доходов» и дать соответствующее название обоим осям.
Построить линейчатую плоскую диаграмму изменения суммы доходов при купонной ставке в 10, 30 и 40%. При построении диаграммы необходимо обозначение линии при ставке 10 % сделать красным, 30 % - зеленым, а 40% - черным. Диаграмме необходимо дать название, а также дать название обоим осям.
Построить круговую (плоскую или объемную - по выбору) диаграмму дохода при курсе покупки 130. Для данной диаграммы изменить цвет фона диаграммы, цвет фона легенды.
Вариант 7.8Данные
В таблице 17 приведены данные изменения курса ценных бумаг в зависимости от даты их покупки и купонной ставки. Ценные бумаги приобретены (Дата_согл) 25.04.2003, купонный доход выплачивается с периодичностью (Частота) – раз в квартал. Предполагаемая дата погашения облигации (Дата_вступл_в_силу) указана в таблице, курс продажи ценных бумаг (Погашение) - 130 руб., ожидаемая годовая ставка помещения (Доход) – 12%.
Таблица 17
A B C D E F
1 Купон, % Даты погашения
2 1.05.2003 3 0 % 4 10 % 5 20 % 6 30 % 7 40 % 8 50 % Требуется
Разместить таблицу 17 на новом листе.
Переименовать лист в «Цена».
Заполнение таблицы
Строка «Дата выплаты купонов» заполняется датами с периодичностью 3 месяца. При расчете воспользоваться функцией ДАТАМЕС или заполнить строку арифметической прогрессией.
Заполнение колонок B, C, D, E, F таблицы производится по своей формуле. При вычислении использовать функцию ЦЕНА и учитывать, что в году 365 дней (Базис) Про функцию ЦЕНА смотрите в приложении Б.
Задания (выполняются на листе «Расчет 5»)
Для колонок B, C, D, E, F задайте денежный формат.
Построить плоский график изменения доходов при операциях с ценными бумагами при купонах в 10%, 30% и 40% (на одном графике). При построении графика ему необходимо дать название «Изменение доходов» и дать соответствующее название обоим осям.
Построить линейчатую плоскую диаграмму изменения суммы доходов при купонной ставке в 10%, 20% и 50%. При построении диаграммы необходимо обозначение линии при ставке 10 % сделать красным цветом, 30 % - зеленым, а 40% - черным. Диаграмме необходимо дать название, а также дать название обоим осям.
Построить круговую (плоскую или объемную - по выбору) диаграмму дохода при выплате купонов на 25.10.2004. Для данной диаграммы изменить цвет фона диаграммы, цвет фона легенды.
Вариант 7.9Данные
Имеется некоторое количество ценных бумаг нескольких предприятий (Таблица 18). Необходимо подсчитать накопленный доход с момента покупки до сегодняшнего дня.
Таблица 18
A B C D E F G H I
1 Вид ценных бумаг Предприятие-эмитент Коли-чествоДата покупки Номинал Ставка, в % Дата выпуска Периодичность выплат Накопленный доход
2 Требуется
Разместить таблицу 18 на новом листе.
Переименовать лист в «Расчет дохода».
Заполнение таблицы
Поле «Вид ценных бумаг» заполняется значениями «акция», «облигация», «вексель» и т.п. Записей в таблице должно быть не менее 15.
Колонки B, D и G заполняются произвольным образом. При этом надо учитывать, что купить ценные бумаги раньше, чем они выпущены нельзя. Записей в таблице должно быть не менее 15.
Поле «Количество» заполняется случайными целыми числами от 10 до 2000. При заполнении воспользоваться функциями ЦЕЛОЕ, СЛЧИС.
Поле «Номинал» заполняется случайными числами от 100 до 300. При заполнении воспользоваться функциями ОКРУГЛ, СЛЧИС.
Поле «Ставка» заполняется целыми случайными числами от 10% до 15%. При заполнении воспользоваться функциями ОКРУГЛ, СЛЧИС.
Поле «Периодичность выплат» заполняется произвольным образом и содержит числа 1 (если проценты выплачиваются один раз в год), 2 (если проценты выплачиваются раз в полугодие) или 4 (если проценты выплачиваются ежеквартально).
Поле «Накопленный доход» вычисляется по формуле определения накопленного дохода по ценным бумагам, при этом необходимо учитывать количество ценных бумаг. Для расчета использовать функцию НАКОПДОХОД, в расчете за дату первого дохода принять текущую дату и учесть, что в году 365 дней (Базис). Про функцию НАКОПДОХОД смотрите в приложении Б.
Задания (выполняются на листе «Расчет дохода»)
Для колонок «Номинал» и «Накопленный доход» задайте денежный формат.
Для поля «Ставка» ввести формат чисел, округляющий их до двух знаков после запятой и подписывающий около каждого числа знак «%».
Построить плоский график накопленных доходов и номинала (на одном графике обе линии). При построении графика ему необходимо дать название «Динамика изменения доходов» и дать соответствующее название обоим осям.
Построить линейчатую плоскую диаграмму изменения номинала. При построении диаграммы необходимо обозначение акций сделать красным, облигаций - зеленым, а векселей - голубым. Диаграмме необходимо дать название, а также дать название обоим осям.
Построить круговую (плоскую или объемную - по выбору) диаграмму полученных доходов. Для данной диаграммы изменить цвет фона диаграммы, цвет фона легенды.
Вариант 7.10Данные
Имеется некоторое количество ценных бумаг нескольких предприятий номинальной ценой в 100 рублей. Необходимо подсчитать процент прибыли с момента покупки до сегодняшнего дня (Таблица 19).
Таблица 19
A B C D E F G H
1 Вид ценных бумаг Предприятие-эмитент Коли-чествоДата покупки Рыночная цена Ставка, в % Периодичность выплат Накопленный доход
2 Требуется
Разместить таблицу 19 на новом листе.
Переименовать лист в «Расчет дохода».
Заполнение таблицы
Поле «Вид ценных бумаг» заполняется значениями «акция», «облигация», «вексель» и т.п. Записей в таблице должно быть не менее 15.
Колонки B и D заполняются произвольным образом. Записей в таблице должно быть не менее 15.
Поле «Количество» заполняется случайными целыми числами от 30 до 200. При заполнении воспользоваться функциями ЦЕЛОЕ, СЛЧИС.
Поле «Рыночная цена» заполняется случайными числами от 60 до 100. При заполнении воспользоваться функциями ОКРУГЛ, СЛЧИС.
Поле «Ставка» заполняется целыми случайными числами от 4% до 15%. При заполнении воспользоваться функциями ОКРУГЛ, СЛЧИС.
Поле «Периодичность выплат» заполняется произвольным образом и содержит числа 1 (если проценты выплачиваются один раз в год), 2 (если проценты выплачиваются раз в полугодие) или 4 (если проценты выплачиваются ежеквартально).
Поле «Накопленный доход» вычисляется по формуле определения накопленного дохода по ценным бумагам. Для расчета использовать функцию ДОХОД, в расчете за дату погашения бумаг (Дата_вступл_в _силу) принять текущую дату и учесть что в году 365 дней (Базис). Про функцию ДОХОД смотрите в приложении Б.
Задания (выполняются на листе «Расчет дохода»)
Для колонки «Номинал» задайте денежный формат.
Для полей «Ставка» и «Накопленный доход» ввести формат чисел, округляющий их до двух знаков после запятой и подписывающий около каждого числа знак «%».
Построить график процента дохода и рыночной стоимости (на одном графике обе линии). При построении графика ему необходимо дать название «Динамика изменения доходов» и дать соответствующее название обоим осям. При построении использовать две оси, для этого необходимо использовать нестандартный вид графика.
Построить линейчатую плоскую диаграмму изменения количества ценных бумаг. При построении диаграммы необходимо обозначение акций сделать красным, облигаций - зеленым, а векселей - голубым. Диаграмме необходимо дать название, а также дать название осям.
Построить круговую (плоскую или объемную - по выбору) диаграмму полученных доходов. На диаграмме отобразить процентное соотношение доходности ценных бумаг. Для данной диаграммы изменить цвет фона диаграммы, цвет фона легенды.
Распечатать таблицу 22 в текстовом и формульном виде, при этом необходимо в верхнем колонтитуле написать свою фамилию, поставить инициалы и номер группы, а в нижнем колонтитуле - поставить номер таблицы, наименование таблицы и указать лист, на котором она находится.
Распечатать круговую диаграмму.
Контрольные вопросы
Как редактируются отдельные элементы диаграммы (графика)?
Как одновременно можно использовать несколько типов диаграмм?
Что называется диаграммой?
Каким образом можно добавить данные к уже существующей диаграмме?
Как можно изменить тип диаграммы?
Как отредактировать заголовок диаграммы?
Как вставить (удалить) на графике линии сетки?
Как переместить диаграмму на другое место в пределах одного рабочего листа?
Как удалить диаграмму?
Каким образом вставляется название диаграммы?
Как производится форматирование отдельных элементов диаграммы?
Лабораторная работа №8. Создание базы данных. Фильтрация баз данных, создание сводных таблицЦель работы
Создание базы данных.
Отбор данных из базы данных с помощью расширенного фильтра.
Добавление, удаление записей в базу данных с помощью формы.
Вариант 8.1Данные
В таблице 20 приведены данные по почтовым отправлениям.
Таблица 20
A B C D E F G H
1 Номер почтового извещения Вид почтового отправления Пункт отправления Вес Кому Пункт назначения Дата отправки Дата доставки
Требуется
Создать таблицу на одном из листов рабочей книги.
Переименовать лист в «Товар».
Отсортировать таблицу, расположив данные по возрастанию количества товара.
Отсортировать таблицу, расположив данные по убыванию даты получения товара.
Заполнение таблицы
Поле «Номер почтового извещения» заполняется целыми числами.
Поле «Вид почтового отправления» заполняется одним из значений: «Письмо», «Бандероль», «Посылка».
Поле вес заполняется случайными числами в зависимости от значения поля «Вид почтового отправления» -
Письмо – до 500 гр.
Бандероль – от 500 гр. до 3 кг.
Посылка – от 3 кг. до 10 кг.
Для выполнения задания необходимо использовать функции ЕСЛИ, ОКРУГЛ и СЛЧИС.
Значения полей «Дата отправки», «Дата доставки», «Пункт назначения», «Кому», «Пункт отправления» заполняются произвольными значениями полей.
Количество записей в таблице - не менее 15. Вид почтового отправления, Пункт отправления, Пункт назначения должны повторяться.
Задания
Для колонки "Вес" задайте формат, отображающий килограммы и граммы.
С помощью расширенного фильтра вывести записи удовлетворяющие условию: посылки из города Х, весом более G кг. (наименование города выбирается произвольно из имеющегося списка, вес берется произвольно).
С помощью расширенного фильтра вывести записи удовлетворяющие условию: почтовые пересылок между городами К и L (пункт назначения и пункт отправления выбирается произвольно из имеющихся
Создать дополнительную колонку «Доставка», в которой вычислить сколько дней производилась доставка груза. Для этого необходимо использовать функцию ДНЕЙ360 или простое вычитание одной даты из другой.
С помощью расширенного фильтра определить груз, доставленный быстрее всего из пункта оправления R (пункт отправления выбирается произвольно из имеющихся). При выполнении задания использовать функцию МИН и данные из колонки «Доставка».
Создать 3 сводные таблицы :а) одну - с подсчетом суммы по одному из полей;
б) одну - с подсчетом количества по одному из полей;
в) одну - с размещением любого поля сводной таблицы для фильтрации.
Используя автофильтр, выведите информацию из таблицы 23 только по бандеролям.
Используя автофильтр, выведите информацию из таблицы 23 только по бандеролям, отправленным из Красноярска.
Используя автофильтр, выведите информацию из таблицы 23 только по бандеролям, весом от 1 кг до 2,5 кг.
Вариант 8.2Данные
В таблице 21 приведены данные по выходу книг из нескольких типографий.
Таблица 21
A B C D E F
1 Автор Наименование книги Издательство Дата выхода Количество экземпляров Наличие соавторов
Требуется
Создать таблицу 21 на одном из листов рабочей книги с помощью формы.
Переименовать лист в «Книги».
Отсортировать таблицу, расположив данные по возрастанию количества экземпляров книги.
Отсортировать таблицу, расположив по алфавиту фамилии авторов.
Заполнение таблицы
Таблица заполняется произвольными значениями полей. Колонка F заполняется значениями «+», если есть соавторы (т.е. авторов больше одного), и значениями «-», если их нет. Количество записей в таблице - не менее 15.
Задания
Для колонки "Количество экземпляров" задайте соответствующий формат.
С помощью расширенного фильтра вывести записи, удовлетворяющие условию: книги авторов X и Y в издательстве Z, начиная с даты D (авторы и издательство выбираются произвольно из имеющихся, дата берется произвольно).
С помощью расширенного фильтра вывести записи, удовлетворяющие условию: книги автора Y, если тираж превышает K1 экземпляров и меньше K2 экземпляров (автор выбирается произвольно из имеющихся, числа берутся произвольно).В конец таблицы ввести дополнительную колонку «Раритет», в которой поставить знак «+» если книга вышла более 7 лет назад в количестве менее 500 экземпляров, в противном случае поставить знак «-».
С помощью расширенного фильтра выдать данные о самой редкой книге, т.е. она является раритетной и у нее самый маленький тираж. При выполнении задания использовать функцию МИН и данные колонки «Раритет».
Создать 3 сводные таблицы :а) одну - с подсчетом суммы по одному из полей;
б) одну - с подсчетом количества по одному из полей;
в) одну - с размещением любого поля сводной таблицы для фильтрации.
Используя автофильтр, выведите информацию из таблицы 21 только по Пушкину, книги которого выпущены тиражом более 2000 экземпляров.
Используя автофильтр, выведите информацию из таблицы 21 только по Пушкину, книги которого выпущены тиражом от 1000 до 1700 экземпляров.
Отмените автофильтр и выведите все строки таблицы.
Используя автофильтр, выведите информацию из таблицы 21 только по Пушкину и Лермонтову.
Вариант 8.3Данные
В таблице 22 приведены данные по сдаче студентами зачета и курсовой работы по дисциплине Высшая математика.
Таблица 22
A B C D E F G
1 Номер студента Фамилия студента Номер группы Зачет Дата сдачи зачета Курсовая работа Дата сдачи курсовой работы
2 001 01.10.2003 12.02.2004
Требуется
Создать таблицу 22 на одном из листов рабочей книги с помощью формы.
Переименовать лист в «Ведомость».
Отсортировать таблицу, расположив данные по возрастанию дат.
Отсортировать таблицу, расположив данные по убыванию отметки за курсовую работу.
Заполнение таблицы
В поле «Зачет» ставится символ «+», если зачет получен, и знак «-», если нет.
Поле «Курсовая работа» заполняется цифрами от 3 до 5 или ставится символ «-» курсовая работа не сдана.
Если зачет или курсовая работа не сданы, то вместо дат ставится «-» соответственно.
Поля таблицы A, B и C заполняется произвольными значениями полей.
Количество записей в таблице - не менее 15.
Задания
В колонках "Номер студента", "Дата сдачи зачета" и "Дата сдачи курсовой работы" задать формат, который показа в примере.
С помощью расширенного фильтра вывести записи удовлетворяющие условию: студенты групп N1 и N2, получившие за курсовую работу 5. (номера групп выбираются произвольно из имеющихся).
С помощью расширенного фильтра вывести записи удовлетворяющие условию: студенты сдали зачет и получили за курсовую работу 4 или 5.
С помощью расширенного фильтра вывести записи удовлетворяющие условию: студенты из группы N сдавали курсовую работу с даты D1 до даты D2 (номер группы выбирается произвольно из имеющихся, даты берутся произвольно).
В конец таблицы ввести дополнительную колонку «Выход на сессию», в которой отметить знаком «+», если есть зачет и имеется отметка по курсовой работе, и знаком «-», если это не так
Создать 3 сводные таблицы :а) одну - с подсчетом суммы по одному из полей;
б) одну - с подсчетом количества по одному из полей;
в) одну - с размещением любого поля сводной таблицы для фильтрации.
Используя автофильтр, выведите информацию из таблицы 22 только по студентам группы номер 81-2, у которых отметка по курсовой работе 4.
Используя автофильтр, выведите информацию из таблицы 22 только по студентам номер 81-01, которые сдали курсовую работу в декабре (год выбирается согласно данным таблицы).
Отмените автофильтр и выведите все строки таблицы.
Используя автофильтр, выведите информацию из таблицы 22 только по студентам группы номер 81-1 и 81-2.
Вариант 8.4Данные
В таблице 23 приведены сведенья о жителях, обслуживаемых поликлиникой.
Таблица 23
A B C D Е F
1 Номер амбулаторной карты Ф И.О. Дата рождения Дата выдачи страхового полиса Номер полиса обязательного медицинского страхования Сумма, выплаченная по полису
Требуется
Создать таблицу 23 на одном из листов рабочей книги с помощью формы.
Переименовать лист в «Поликлиника».
Отсортировать таблицу, расположив данные по возрастанию номера амбулаторных карт.
Отсортировать таблицу, расположив данные по возрастанию даты рождения.
Заполнение таблицы
Таблица заполняется произвольными значениями полей. Количество записей в таблице - не менее 15.
Задания
С помощью расширенного фильтра вывести записи удовлетворяющие условию: люди, родившихся в годах X и Y (дата рождения выбираются произвольно из имеющихся).
С помощью расширенного фильтра вывести записи удовлетворяющие условию: выплаченные суммы от N1 до N2 (численные значения сумм выбирается произвольно из имеющихся).
В конец таблицы введите колонку «Новый полис», в которой необходимо поставить знак «+», если полис выдан менее 6 месяцев назад, и знак «-», если это не так.
С помощью расширенного фильтра вывести данные о людях, недавно получивших полис и имеющих минимальные страховые выплаты. При выполнении задания использовать функцию МИН и данные из столбца «Новый полис».
Создать 3 сводные таблицы :а) одну - с подсчетом суммы по одному из полей;
б) одну - с подсчетом количества по одному из полей;
в) одну - с размещением любого поля сводной таблицы для фильтрации.
Используя автофильтр, выведите информацию из таблицы 23 только по одной из дат выдачи страхового полиса.
Используя автофильтр, выведите информацию из таблицы 23 только по полисам, выданным за последние три месяца.
Используя автофильтр, выведите информацию из таблицы 23 только по полисам, выданным за последний год и по которым выплачено более 10000 руб.
Отмените автофильтр и выведите все строки таблицы.
Используя автофильтр, выведите информацию из таблицы 23 только по двум людям (необходимо выбрать только две фамилии).
Отмените автофильтр и выведите все строки таблицы.
Вариант 8.5Данные
В таблице 24 приведены данные по прохождению технического осмотра машин.
Таблица 24
A B C D E
1 Модель Код региона Цвет Фамилия владельца Дата последнего технического осмотра
Требуется
Создать таблицу 24 на одном из листов рабочей книги с помощью формы.
Переименовать лист в «Машины».
Отсортировать таблицу, расположив данные по возрастанию даты последнего техосмотра.
Отсортировать таблицу, расположив данные по убыванию кода региона.
Заполнение таблицы
Таблица заполняется произвольными значениями полей.
Количество записей в таблице - не менее 15.
Задания
С помощью расширенного фильтра вывести записи удовлетворяющие условию: машины из региона Х прошли технический осмотр в году С (код региона и год выбираются произвольно из имеющихся).
С помощью расширенного фильтра вывести записи удовлетворяющие условию: регионы C1 и C2 (коды регионов выбираются произвольно из имеющихся).
С помощью расширенного фильтра вывести записи удовлетворяющие условию: машины синего и белого цвета прошли технический осмотр в регионе X (шифр региона выбирается произвольно из имеющихся).
В конец таблицы ввести еще одну колонку «Показатель», в которой поставить 1 если машина серого, черного или серебристого цвета, в противном случае поставить 0.
С помощью расширенного фильтра вывести данные по минимальной дате осмотра машин серого, черного или серебристого цвета. При выполнении задания использовать функцию МИН и данные из колонки «Показатель».
Создать 3 сводные таблицы :а) одну - с подсчетом суммы по одному из полей;
б) одну - с подсчетом количества по одному из полей;
в) одну - с размещением любого поля сводной таблицы для фильтрации.
Используя автофильтр, выведите информацию из таблицы 24 только по одной модели.
Используя автофильтр, выведите информацию из таблицы 24 только по машинам синего цвета из региона 24.
Используя автофильтр, выведите информацию из таблицы 24 только по регионам 24 и 12.
Отмените автофильтр и выведите все строки таблицы.
Используя автофильтр, выведите информацию из таблицы 24 только по «Жигулям» и «Москвичам».
Отмените автофильтр и выведите все строки таблицы.
Вариант 8.6Данные
В таблице 25 приведены данные по заключенным договорам продажи продукции на предприятии.
Таблица 25
A B C D E
1 Номер договора Фамилия Вид продукции (доски, брус и т.д.) Объем, м3 Сумма договора, рубТребуется
Создать таблицу 25 на одном из листов рабочей книги с помощью формы.
Переименовать лист в «Договора».
Отсортировать таблицу по убыванию объема древесины.
Отсортировать таблицу по виду продукции.
Заполнение таблицы
Таблица заполняется произвольными значениями полей.
Количество записей в таблице - не менее 15.
Задания
Для колонки "Сумма договора" задайте денежный формат.
Для колонки "Объем древесины" задайте соответствующий формат.
С помощью расширенного фильтра вывести записи удовлетворяющие условию: древесина, которую продали в виде досок и фанеры.
С помощью расширенного фильтра вывести записи удовлетворяющие условию: продукция объемом от N1 до N2 (объемы берутся произвольно).
В конец таблицы введите колонку «Цена за 1 м3», в которой подсчитать по формуле : «Объем» / «Сумма договора».
С помощью расширенного фильтра вывести данные по продукции с максимальной ценой за 1 м3. При выполнении задания использовать функцию МАКС и данные из столбца «Цена за 1 м3».
Создать 3 сводные таблицы :а) одну - с подсчетом суммы по одному из полей;
б) одну - с подсчетом количества по одному из полей;
в) одну - с размещением любого поля сводной таблицы для фильтрации.
Используя автофильтр, выведите информацию из таблицы 26 только по доскам.
Используя автофильтр, выведите информацию из таблицы 26 только по договорам на брус на сумму более 200000 руб.
Используя автофильтр, выведите информацию из таблицы 26 только по договорам с номерами с 12 по 35.
Отмените автофильтр и выведите все строки таблицы.
Используя автофильтр, выведите информацию из таблицы 26 только по бревнам и доскам.
Отмените автофильтр и выведите все строки таблицы.
Вариант 8.7Данные
В таблице 26 приведены данные по предлагаемым рабочим местам из базы вакансий службы занятости.
Таблица 26
A B C D E F
1 Фирма Должность Заработная плата Минимальный возраст претендента Минимальный стаж работы Пол
(м / ж / любой)
Требуется
Создать таблицу 26 на одном из листов рабочей книги с помощью формы.
Переименовать лист в «Предложения».
Отсортировать таблицу, расположив данные по возрастанию требуемого стажа работы.
Отсортировать таблицу, расположив данные по требуемому полу претендента.
Заполнение таблицы
Таблица заполняется произвольными значениями полей.
Количество записей в таблице - не менее 15.
Задания
С помощью расширенного фильтра вывести записи удовлетворяющие условию: людей, для которых предлагается работа по специальности X или Y (наименования специальностей выбираются произвольно из имеющихся).
С помощью расширенного фильтра вывести записи удовлетворяющие условию: сотрудники в возрасте от 20 до 35 лет.
В конец таблицы ввести еще одну колонку «Предложения молодым», в которой поставить 1 если требование к стажу работы менее 3 лет и возраст претендента может быть меньше 28 лет, в противном случае поставить 0.
С помощью расширенного фильтра вывести данные по предлагаемым работам для молодежи с максимальной заработной платой. При выполнении задания использовать функцию МАКС и данные из столбца «Предложения молодым».
Создать 3 сводные таблицы :а) одну - с подсчетом суммы по одному из полей;
б) одну - с подсчетом количества по одному из полей;
в) одну - с размещением любого поля сводной таблицы для фильтрации.
Используя автофильтр, выведите информацию из таблицы 29 только по фирме «Снежинка».
Используя автофильтр, выведите информацию из таблицы 29 только по официантам с минимальным требуемым стажем работы больше 4 лет.
Используя автофильтр, выведите информацию из таблицы 29 только по вакансиям, предлагаемым женщинам, с заработной платой от 3000 до 6500 руб.
Отмените автофильтр и выведите все строки таблицы.
Используя автофильтр, выведите информацию из таблицы 29 только по официантам и шоферам.
Отмените автофильтр и выведите все строки таблицы.
Вариант 8.8Данные
В таблице 27 приведены данные из базы предприятий бытового обслуживания.
Таблица 27
A B C D E
1 Название предприятия Услуга
(ремонт телевизоров, обуви и т.д.) Стоимость Адрес Телефон
Требуется
Создать таблицу 27 на одном из листов рабочей книги с помощью формы.
Переименовать лист в «Предприятия».
Отсортировать таблицу, расположив данные по возрастанию стоимости услуги.
Отсортировать таблицу, расположив данные по наименованиям услуг, оказываемых предприятиями.
Заполнение таблицы
Таблица заполняется произвольными значениями полей.
Количество записей в таблице - не менее 15.
Задания
С помощью расширенного фильтра вывести записи удовлетворяющие условию: услуги Х, которое выполнило предприятие С (наименование услуги и наименование предприятия выбираются произвольно из имеющихся).
С помощью расширенного фильтра вывести записи удовлетворяющие условию: услуги M предприятий C1 и C2 (наименование предприятий и услуги выбираются произвольно из имеющихся).
В конец таблицы ввести еще одну колонку «Ремонт», в которой поставить 1 если предприятие оказывает услуги по ремонту (т.е. ремонт видеотехники, телевизоров, часов и т.д.), в противном случае поставить 0.
С помощью расширенного фильтра вывести данные по ремонту с минимальной стоимостью. При выполнении задания использовать функцию МИН и данные колонки «Ремонт».
Создать 3 сводные таблицы :а) одну - с подсчетом суммы по одному из полей;
б) одну - с подсчетом количества по одному из полей;
в) одну - с размещением любого поля сводной таблицы для фильтрации.
Используя автофильтр, выведите информацию из таблицы 30 только по предприятию «Бытовик»
Используя автофильтр, выведите информацию из таблицы 30 только по услугам предприятий, расположенных на улице Кирова и стоимостью более 80 руб.
Используя автофильтр, выведите информацию из таблицы 30 только по услугам предприятий, расположенных на улице Кирова и стоимостью от 40 до 120 руб.
Отмените автофильтр и выведите все строки таблицы.
Используя автофильтр, выведите информацию из таблицы 30 только по ремонту телевизоров и ремонту телефонов.
Отмените автофильтр и выведите все строки таблицы.
Вариант 8.9Данные
В таблице 28 приведены данные о вкладчиках нескольких банков.
Таблица 28
A B C D E F G
1 Наименование банка Номер лицевого счета Фамилия И.О. Текущая сумма вклада Последняя операция Дата последней операции Снятая / добавленная сумма
Требуется
Создать таблицу 28 на одном из листов рабочей книги с помощью формы.
Переименовать лист в «Банк».
Отсортировать таблицу, расположив данные по возрастанию даты последней операции.
Отсортировать таблицу, расположив данные по убыванию номера лицевого счета.
Заполнение таблицы
Поля A, B, С и F таблицы заполняется произвольными значениями полей.
Поле «Текущая сумма вклада» заполняется произвольными числами от 1200 до 20000.
Поле «Снятая / добавленная сумма» заполняется произвольными числами от 200 до 1900.
Поле «Последняя операция» заполняется текстовыми значениями «Снятие со счета» или «Положить на счет»
Количество записей в таблице - не менее 15.
Задания
Определить сумму вклада после проведения операции. Для этого в конец таблицы ввести дополнительную колонку «Итоговая сумма вклада» и рассчитать данные в ней по формуле:
если последняя операция «Положить на счет», то «Текущая сумма вклада» + «Снятая / добавленная сумма», если последняя операция «Снятие со счета», то «Текущая сумма вклада» - «Снятая / добавленная сумма».
Для расчета использовать функцию ЕСЛИ.
С помощью расширенного фильтра вывести записи удовлетворяющие условию: снятые суммы с даты D1 до даты D2 (даты выбираются произвольно).
С помощью расширенного фильтра вывести записи удовлетворяющие условию: снятые со счетов суммы от 500 до 1200 рублей начиная с даты R (дата выбирается произвольно).
В конец таблицы ввести еще одну колонку «Последние поступления», в которой поставить 1 если поступление на счет было с течение последних 2 месяцев от текущей даты, в противном случае поставить 0.
С помощью расширенного фильтра вывести данные по последней минимальной поступившей сумме. При выполнении задания использовать функцию МИН и данные колонки «Последние поступления».
Создать 3 сводные таблицы :а) одну - с подсчетом суммы по одному из полей;
б) одну - с подсчетом количества по одному из полей;
в) одну - с размещением любого поля сводной таблицы для фильтрации.
Используя автофильтр, выведите информацию из таблицы 31 только по положенным суммам.
Используя автофильтр, выведите информацию из таблицы 31 только по суммам, положенным после 12.8.2001.
Используя автофильтр, выведите информацию из таблицы 31 только по суммам, положенным в текущем году.
Отмените автофильтр и выведите все строки таблицы.
Используя автофильтр, выведите информацию из таблицы 31 только по вкладчикам Иванов и Сидоренко.
Отмените автофильтр и выведите все строки таблицы.
Вариант 8.10Данные
В таблице 29 приведены данные о рабочих цеха и их заработной плате.
Таблица 29
A B C E D F G
1 Табельный номер Фамилия И.О. Профессия Разряд Стаж работы Заработная плата по окладу Заработная плата по тарифу
Требуется
Создать таблицу 32 на одном из листов рабочей книги с помощью формы.
Переименовать лист в «Рабочие».
Отсортировать таблицу, расположив данные по убыванию табельных номеров.
Отсортировать таблицу, расположив данные по профессиям и табельным номерам.
Заполнение таблицы
Таблица заполняется произвольными значениями полей.
Количество записей в таблице - не менее 15.
Задания
С помощью расширенного фильтра вывести записи удовлетворяющие условию: люди профессии Х, работающего по разряду С (наименование профессии и разряд работающего выбираются произвольно из имеющихся).
С помощью расширенного фильтра вывести записи удовлетворяющие условию: люди профессий C1 и C2 со стажем работы более 20 лет. (наименование профессий выбираются произвольно из имеющихся).
С помощью расширенного фильтра вывести записи удовлетворяющие условию: рабочие 6 и 11 разрядов с окладом более 6000 руб.
В конец таблицы ввести еще одну колонку «Анализ», в которой поставить 1 если рабочий грузчик имеет 3 или 4 разряд, в противном случае поставить 0.
С помощью расширенного фильтра вывести данные по грузчикам с минимальной заработной платой. При выполнении задания использовать функцию МИН и данные дополнительной колонки «Анализ».
Создать 3 сводные таблицы :а) одну - с подсчетом суммы по одному из полей;
б) одну - с подсчетом количества по одному из полей;
в) одну - с размещением любого поля сводной таблицы для фильтрации.
Используя автофильтр, выведите информацию из таблицы 32 только по специалистам 6 разряда.
Используя автофильтр, выведите информацию из таблицы 32 только по специалистам 6 разряда и требуемым стажем работы больше 6 лет.
Используя автофильтр, выведите информацию из таблицы 32 только по работам, предлагаемым мужчинам с заработной платой от 6000 до 12000 руб.
Отмените автофильтр и выведите все строки таблицы.
Используя автофильтр, выведите информацию из таблицы 32 только по шоферам и слесарям.
Отмените автофильтр и выведите все строки таблицы.
Контрольные вопросы:
Какие виды фильтров вы знаете?
В чем преимущество расширенного фильтра?
Как можно задать сложные условия в автофильтре?
Для чего нужны сводные таблицы?
Как строится сводная диаграмма?

Приложенные файлы

  • docx 5519334
    Размер файла: 92 kB Загрузок: 0

Добавить комментарий