Задания по работе с базой данных в Excel

Состав отчета по лабораторной работе

1. Титульный лист

2. Описание варианта задания

3. График функции

а) Таблица значений

б) Формула, использованная для вычисления функции

в) Диаграмма

4. Таблица данных

а) Исходная таблица

б) Примеры формул, использованных в таблице

в) Отсортированные данные

г) Результаты применения фильтров (указать параметры фильтра)

Вариант задания соответствует номеру в списке группы.

№ вари-анта	Функция	Значения констант	Интервал	№ базы данных
1	Asin(Bx)eCx	A=7; B=4; C=0,2	x Î [0; 10], шаг 0,5	А
2	Asin(eBx)	A=45; B=0,3	x Î [0; 11], шаг 0,55	Б
3	Asin(Bx)cos(Bx)	A=125; B=4	x Î [0; 14], шаг 0,7	В
4		A=42; B=2	x Î [1; 11], шаг 0,5	Г
5		A=33,5; B=3	x Î [1; 11], шаг 0,5	Д
6		A=25; B=20; C= -1,5	x Î [0,1; 14,1], шаг 0,7	Е
7	Asin(Bx2)	A=34,6; B=2,14	x Î [-2; 8], шаг 0,5	А
8		A=26; B=0,12	x Î [-2,5; 4,5], шаг 0,35	Б
9		A=35,5; B=3	x Î [0,1; 20,1], шаг 1	В
10	Asin(eBx)eCx	A=36,8; B=0,1; C= -0,1	x Î [-1,5; 11,5], шаг 0,65	Г
11	Ax2cos(Bx + C)	A=76; B=2,5; C=1,2	x Î [-5,5; 4,5], шаг 0,5	Д
12	Acos(Bx3 + C) – x2 + D	A=40; B=1,1; C=2,1; D=1	x Î [-10; 10], шаг 1	Е
13		A=38; B=2; C=0,2; D=5	x Î [0,5; 5,5], шаг 0,25	А
14		A=16; B=3; C= -0,6; D=8	x Î [0,1; 13,1], шаг 0,65	Б
15	Asin(x)eBx – x2 + C	A=20; B= -0,1; C= -0,2	x Î [-5; 5], шаг 0,5	В
16		A=25; B=3,7; C=4; D=0,125; E=4	x Î [-1; 18], шаг 0,95	Г
17	Acos(Bx)eCx	A=20; B=2; C=01	x Î [0; 10], шаг 0,5	Д
18		A=37; B=2,2; C= -0,78; D=10,5	x Î [0,1; 5,1], шаг 0,25	Е
19	Ax2sin(Bx + C)	A=65; B=1,6; C=0,6	x Î [-5; 5], шаг 0,5	А
20	Acos(BeCx)eDx	A=89; B=1,1; C=0,1; D= -0,105	x Î [-3; 7], шаг 0,5	Б
21		A=15; B=4	x Î [5; 15], шаг 0,5	В
22	Asin(x)eBx + x2 + C	A=2; B=0,5; C=-1	x Î [-6; 6], шаг 0,5	Г
23	Asin(eBx+С)	A=12; B=-3; C=5	x Î [0; 10], шаг 0,5	Д
24	Aсos(eBx)eCx	A=-7; B=2; C=-3	x Î [-10; 0], шаг 0,5	Е
25		A=6; B=5; C=-2; D=0,25; E=8	x Î [-1; 15], шаг 0,8	А
26		A=9; B=-6; C= 3	x Î [0,1; 12,1], шаг 0,6	Б
27		A=7; B=0,3; C=4; D=12	x Î [-2; 11], шаг 0,5	В
28	Asin(Bx)sin(Cx2)	A=3; B=-4; C= 5	x Î [-2; 4], шаг 0,3	Г
29		A=-16; B=0,4	x Î [-3; 3], шаг 0,25	Д
30		A=3; B=2	x Î [-10; -0,5], шаг 0,5	Е

Задания по работе с базой данных в Excel

А. Ведомость реализации товаров

1. Поля Цена, Сумма и Итого содержат данные типа Денежный.
2. Сформировать поле Сумма: <Сумма> = <Цена> × <Количество>.
3. Поле Скидка заполнить следующим образом: для суммы покупки до 10000 руб. скидка 0%, от 10000 руб. – скидка 5%.
4. Сформировать поле Итого: оно содержит сумму с учетом скидки.
5. Сортировать таблицу по значениям полей Поставщик и Дата реализации. С помощью команды Промежуточные итоги отобразить общую сумму и количество товаров, проданных каждым поставщиком.
6. С помощью фильтра отобрать данные по базам 1 и 3.
7. С помощью фильтра отобрать данные о продажах на сумму более 15000 руб.

Б. Информация о странах мира

1. Сформировать поле Полушарие Земли в зависимости от значения поля Часть света.

2. Сформировать поле Плотность населения: <Плотность населения> = <Население> / <Площадь>.

3. Сортировать таблицу по значениям полей Часть света и Полушарие Земли. С помощью команды Промежуточные итоги определить общую площадь и общее количество населения для каждой части света и полушария.

4. С помощью фильтра отобрать данные для стран, плотность населения которых меньше среднего значения.

5. С помощью фильтра отобрать данные для стран, площадь которых превышает 5 000 тыс. кв. м.

В. Список сотрудников

1. Поле Дата поступления на работу содержит данные типа Дата, поля Оклад и Начислено – данные типа Денежный.

2. Сформировать поле Оклад при следующих данных: ставка ведущего бухгалтера – 15 000 руб., ставка бухгалтера – 10 000 руб., ставка помощника бухгалтера – 6 000 руб.

3. Сформировать поле Надбавка в зависимости от стажа работы на предприятии: стаж более 3 лет – 10% от оклада; стаж более 10 лет – 20% от оклада; стаж более 20 лет – 30% от оклада. Для расчета стажа использовать формулы СЕГОДНЯ() (возвращает текущую дату), ГОД(<дата в числовом формате>) (возвращает год в виде целого числа).

4. Рассчитать поле Начислено с учетом надбавки.

5. Сортировать таблицу по значениям полей Должность и Ф.И.О. С помощью команды Промежуточные итоги определить средний оклад по каждой должности.

6. С помощью фильтра отобрать данные по всем сотрудникам, работающим на 0,5 ставки.

7. С помощью фильтра отобрать данные по всем сотрудникам, принятым на работу с 1 января 2000 г.

Г. Сведения о ряде геометрических тел

1. Сформировать поле Масса тела: <Масса тела> = <Плотность материала> × <Объем тела>.

2. По известным размерам тела сформировать поле Объем тела в зависимости от его вида (куб или шар).

3. Заполнить поле Вид материала следующим образом: если плотность более 1,5 г/см³ – металл, иначе пластмасса.

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

5. С помощью фильтра отобрать информацию о всех шарах.

6. С помощью фильтра отобрать информацию о всех телах массой более 100 г.

Д. Мониторы

1. Поля Цена и Стоимость содержат данные типа Денежный.

2. Сформировать поле Цена, руб., приняв в качестве условной единицы 1 евро с учетом текущего курса.

3. Сформировать поле Количество следующим образом: если цена меньше или равна 4000 руб., количество 10 шт.; если цена от 4000 руб. до 7000 руб., количество 7 шт.; если цена от 7000 руб., количество 5 шт.

4. Сортировать мониторы по полям Производитель и Диагональ. С помощью команды Промежуточные итоги вычислить средние цены в руб. и у.е. по каждому производителю.

5. С помощью фильтра отобрать данные о мониторах с диагональю от 19″.

6. С помощью фильтра отобрать данные о мониторах по цене от 3000 руб. до 6000 руб.

Е. Услуги распечатки

1. Поля Цена, Сумма и Итого содержат данные типа Денежный.

2. Поле Цена заполнить следующим образом:

односторонняя печать на листе А4 – 2 руб., двухсторонняя печать – 3 руб.;

односторонняя печать на листе А3 – 4 руб., двухсторонняя печать – 6 руб.

3. Сформировать поле Сумма: <Сумма> = <Цена> × <Количество>.

4. Поле Скидка заполнить следующим образом: при распечатке до 100 листов – скидка 0%, от 100 листов – скидка 10%.

5. Сформировать поле Итого: оно содержит сумму с учетом скидки.

6. Сортировать таблицу по полям Формат и Двухсторонняя печать. С помощью команды Промежуточные итоги отобразить общую сумму и количество использованных листов по каждому формату.

7. С помощью фильтра отобрать данные по всем распечаткам со скидкой.

8. С помощью фильтров отобрать данные по всем распечаткам до 20 листов А4.

Пример выполнения задания

Примечание: При необходимости переключиться к классическому способу представления ссылок на ячейки таблицы вместо способа R1C1 нужно снять флажок Стиль ссылок R1C1:

в версиях MS Excel 2003 и ранее: Сервис > Параметры > Общие > Стиль ссылок R1C1;

в версиях MS Excel 2007 и выше: Кнопка Office > Параметры Excel > Формулы > Стиль ссылок R1C1.

Построение графика функции

Функция Asin(Bx)e^Cx, A=20, B=2, C=0.1. Диапазон x Î [0; 5], шаг 0,5.

1. Откроем новую книгу, переименуем текущий лист.

2. Введем значения констант, соответствующие варианту, в ячейки столбцов A и B. Озаглавим ячейки с помощью инструмента   «Объединить и поместить в центре».

3. Заполним ячейки столбца D значениями заданного диапазона изменения аргумента. Для этого в D2 введем начальное значение диапазона, в D3 – следующее значение с учетом заданного шага. Выделим диапазон D2-D3, затем, ухватившись за черную точку в правом нижнем углу рамки выделения, растянем выделение до конца заданного диапазона изменения значений.

а)    б)    в)

4. Ячейки столбца E заполним значениями функции. Для этого в ячейку E2 введем формулу значения функции как =$B$2*SIN($B$3*D2)*EXP($B$4*D2). Обратите внимание: ячейки B2, B3, B4 содержат значения констант, общие для всех строк таблицы, поэтому они записываются через $, как абсолютные ссылки. Ячейка D2 соответствует текущему значению x для строки и записывается как относительная ссылка. Также перед формулой всегда записывается знак «=».

Выделив ячейку E2 и ухватившись за правый нижний угол рамки, растянем выделение за правый нижний угол до конца диапазона. Формулы будут скопированы.

5. Зададим точность вычисления значений функции до 4 знаков после запятой. Для этого, выделив диапазон значений функции, воспользуемся окном Формат ячеек…

6. Добавим на текущий лист график функции. Для этого выделим ячейки в столбцах D и E, выберем команду меню Вставка > График > График с маркерами (в версиях MS Excel 2003 и ранее команда главного меню Вставить > Диаграмма).

С помощью команды Конструктор > Выбрать данные вызовем окно Выбор источника данных. В нем, нажав кнопку Подписи горизонтальной оси > Изменить, зададим диапазон изменения значений аргумента по столбцу D.

Также удалим ряд значений x.

7. В графике функции подписи оси должны располагаться не в середине делений, а точно под делениями. Для этого выделим горизонтальную ось, в контекстном меню выберем Формат оси (Команда меню Макет > Оси > Основная горизонтальная ось > Дополнительные параметры основной горизонтальной оси), там установим переключатель Положение оси в значение По делениям.

До:    После:

8. С помощью команд меню вкладок Макет и Формат зададим форматирование диаграммы: добавим названия диаграммы и координатных осей, настроим вид осей, координатной сетки и т.п.

9. Пример окончательного вида листа:

Работа с базами данных

1. Перейдем на другой лист книги и переименуем его.

2. Составим таблицу и заполним ее данными.

3. Заполним поле «Зачет» следующим образом: если сумма баллов больше 55, зачет сдан, иначе не сдан. Для этого введем в ячейку D2 формулу: =ЕСЛИ(C2 >= 55; "Зачтено"; "Не зачтено") и скопируем ее в другие ячейки, растянув выделение за правый нижний угол.

4. Выполним сортировку таблицы по именам студентов. Для этого выделим ячейки с данными и выберем команду Данные > Сортировка. Зададим параметры сортировки.

Результат сортировки:

5. Найдем средний балл для каждого студента. Для этого выберем команду Данные > Промежуточные итоги (в MS Excel 2003 и ранее команда главного меню Данные > Итоги). Выберем тип операции «Среднее» и зададим поля подведения итогов.

6. С помощью фильтра отберем данные по дисциплине Информатика. Выделим произвольную ячейку таблицы и выберем команду Данные > Фильтр. Щелкнув по кнопке списка справа в поле «Дисциплина», выберем Текстовые фильтры > Настраиваемый фильтр (в MS Excel 2003 и ранее пункт Условие…). В появившемся окне можно настроить параметры фильтра.

Окно настройки фильтра:

Результат фильтрации:

7. Сняв фильтр с поля Дисциплина, отберем студентов с суммой баллов от 80 до 100.

Окно настройки фильтра:

Результат фильтрации: