Порядок выполнения работы. Часть I: «Создание информационной системы, базирующейся на автономных файлах»

Часть I: «Создание информационной системы, базирующейся на автономных файлах»

1. Создать статистическую ведомость для группы III в первом семестре на Листе 1 в электронной таблице (как показано ниже на рис. 44, в закрашенные ячейки должны быть внесены формулы). Переименовать лист, присвоив ему имя Семестр 1.

2. Назначить стипендию студентам по следующим правилам:

· студентам, сдавшим сессию без двоек, начисляется минимальная стипендия (108 р.);

· хорошистам, имеющим средний бал <=4,5, стипендия повышается на 20%;

· хорошистам, средний бал у которых >4,5, стипендия повышается на 50%;

· отличникам стипендия повышается на 80%.

2. Построить диаграммы по данным таблиц: линейчатую диаграмму сравнения средних показателей по каждому предмету и круговую диаграмму сравнения числа отличников, хорошистов, троечников и двоечников в группе (в % отношении).

3. Создать подобные ведомости группы для 2-6 семестров на листах 2-6 электронной таблицы. Для этого:

· скопировать Лист 1 электронной таблицы на остальные листы;

· переименовать листы, присвоив им имена соответствующих семестров;

· внести следующие исправления:

- изменить номер сессии;

- изменить названия предметов;

- изменить оценки;

- во 2, 4 и 6 сессии добавить еще один предмет и исправить соответствующие формулы;

- добавить двух новых студентов в группу в 4 семестре;

- установить минимальную стипендию для 1,2 -го семестра 108 р., для 3,4-125 р., 5,6-130 р.

3. На Листе 7 электронной таблицы создать сводную ведомость о средней успеваемости студентов за 6 семестров, скопировав столбцы Среднее с каждого листа электронной таблицы.

4. На листе 7 электронной таблицы решить следующую задачу:

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

· 5-100 баллов;

· меньше 5, но больше или равно 4,5 - 50 баллов;

· меньше 4,5 но больше или равно 4 - 25 баллов;

· меньше 4, но больше 3,5 - 10 баллов;

· меньше 3,5 - 0 баллов.

Распределение по группам: 250-600 баллов - углубленное обучение; 80-249 баллов - обычное обучение; 0-79 баллов - выпускная группа.

Рисунок 41

Часть II: «Решение элементарных математических задач»

Каждую задачу выполнять на новом листе в одной рабочей книге.

1. Составить таблицу, вычисляющую n-ый член и сумму арифметической прогрессии, первый член которой равен –2, а разность равна 0,725.

Формула n-го члена: a_n=a₁+d(n-1).

Формула суммы n первых членов: S_n=(a₁+a_n)*n/2.

2. Составить таблицу значений линейной функции y=kx+b, выбрав по своему усмотрению угловой коэффициент k и свободный член b. Значение переменной х изменяется от -5 до 5, шаг изменения – 0,5. По полученной таблице построить график функции.

3. Составить вычисляемую таблицу квадратов, представленную на рисунке 45:

	А	В	С	D	E	F	G	H	I	J	K
	ТАБЛИЦА КВАДРАТОВ

Рисунок 42

Примечание:

1. Заполнить ячейки с А3 по А11 числами от 1 до 9.

2. Заполнить ячейки с В2 по К2 числами от 0 до 9.

3. Ввести в ячейку В3 формулу вычисления: (А3*10+В2)^2, где (А3*10+В2) – это число, возводимое в квадрат.

4. Решить систему уравнений:

Часть III: «Создание информационной системы, базирующейся на взаимосвязанных файлах»

Фирма «АБВГД» занимается изготовлением карнизов. Для этой цели были закуплены материалы. Наименование материалов и их покупная стоимость приведены втаблице 1.

Произвести расчет стоимости всех материалов (табл. 1) с учетом понесенных затрат.

Таблица 1

Наименование материалов	Покупная стоимость (в рублях за тонну)
Труба стальная
Труба алюминиевая
Труба бронзовая
Труба пластмассовая
Брусок дубовый
Брусок ясеневый
Брусок сосновый

При покупке материалов фирмой были понесены затраты, связанные с перевозкой/доставкой груза. В Таблице 2 приведены все статьи расходов, которые необходимо учесть при определении цены реализации.

Плановая стоимость любого товара складывается из:

Покупная стоимость (с учетом НДС) + все затраты, связанные с доставкой товара (с учетом НДС) + административные (парадные) расходы

 

		Ta6лица 2 1ица 2
	Расходы, связанные с доставкой и перевозкой	Руб.	НДС
	ж/д тариф		20%
	авто услуги		20%
	фрахт		20%
	погрузка /разгрузка в порту		20%
	выгрузка груза из трюма		20%
	выгрузка с борта на берег		20%
	естественная убыль (составляет 0,5% от покупной стоимости)		10%
	хранение		20%
	страхование (0,55% от суммы покупной стоимости и всех понесенных затрат с учетом НДС) =СУМM(G13;G15:G22;G24)*0,55%
	(банк % 1/3 ст. ЦБ)		-
	Прочие расходы:
	Административно хозяйственные 7% от (покупная стоимость + сумма расходов п. 1)		20%
	Снабженческо-сбытовая надбавка сумма расходов по п.1 и п.2 (админ. хоз.)
	Цена реализации: сумма: (покупная стоимость (из таблицы 1), сумма расходов п1, админ.хоз. п2)

1. Произвести расчет стоимости материала «Труба стальная». Для этого:

1.1. В среде Excel создать новую рабочую книгу.

1.2. На первом листе новой рабочей книги произвести расчет планово-расчетной цены для трубы стальной согласно Приложению 1 (рис. 46):

· Оформить шапку таблицы.

· В столбец В внести статьи расходов, используя таблицу 2.

· В столбец D внести значения затрат и процент на НДС из таблицы 2.

· В нужные ячейки внести формулы.

· Определить планово-расчетную цену для стальной трубы.

· Значения в ячейках, взятые из таблицы 2, выделить синим цветом. Расчетные значения, т.е. получаемые по формулам, оставить черными.

· Дать название листу «Сталь».

2. Произвести расчет стоимости других материалов из Таблицы 1. Для этого:

· Скопировать содержимое листа «Сталь» на 6 следующих листов.

· Произвести соответствующие изменения согласно значениям из таблицы 1.

· Всем листам дать соответствующие названия.

3. Произвести расчет товарооборота всех материалов

3.1. На листе 8 оформить сводную таблицу «Расчет товарооборота» согласно Приложению 2 (рис. 47).

· Наименование товара берется с соответствующего предыдущего листа прямым связыванием (см. примечание), т.е. в ячейке должна быть ссылка на ячейку с названием товара нужного листа, например, =Сталь!С4.

· Строка «Количество» заполняется как переменные вводимые данные.

· Колонку «Наименование затрат» скопировать с любого предыдущего листа.

· Значение затрат по всем статьям каждого наименования товара находится произведением значения соответствующей статьи затрат, взятой с нужного листа, на количество товара.

Например, в строке «Покупная стоимость» для трубы стальной (т.е. в ячейке С10) должна быть формула: ==Сталь!С13*С8.

Примечание. Чтобы связать 2 рабочих листа:

1. Открыть новое окно: выбрать менюОкно®Новое.

2.Расположить листы рядом: меню Окно®Упорядочить (Расположить)®Мозаикой (Рядом).

3. Набрать формулу.

Для ссылки на ячейку из другого листа, щелкнуть по нужной ячейке, чтобы ее адрес появился в строке формул. При этом адрес запишется с названием листа (или названием файла, если ссылка к другой рабочей книге), т.е. указывается полный адрес ячейки.

Рисунок 43

Рисунок 44

Варианты контрольных заданий

Вариант 1

Посчитайте площади различных фигур в предложенной ниже таблице, если сторона (радиус) a (r) меняет свои значения от 1 до 10, h (высота)=6, π=3,14159.

a	S треугольника	S квадрата	S параллелограмма	S куба	S круга

 

1. Создайте предложенную таблицу в ячейках A4:F14.

2. Для заголовка сделайте полужирный стиль начертания.

3. Для всей таблицы установите выравнивание по центру.

4. Последовательность чисел от 1 до 10 введите, используя автозаполнение.

5. В ячейку А16 введете h, в ячейку В16 введите число 6. В ячейку А17 введите p, в ячейку B17 число 3,14159.

6. Произведите расчет площадей фигур.

7. Выполните обрамление ячеек, как предложено выше.

8. Назовите лист «Площади фигур».

Вариант 2

1. Составьте смету своих расходов за полгода.

Обратите внимание на сложный заголовок таблицы. Месяцы вводятся с помощью автозаполнения.

 

Расходы	Месяц
январь	февраль	март	апрель	май	июнь
Питание
Квартплата
Телефон
Электричество
Транспорт
Бытовая химия
Литература
Одежда
Развлечения
Разное
Итого
Остаток

2. К пустым ячейкам примените денежный формат и заполните их данными.

3. В строке Итого посчитайте расход денежных средств помесячно с помощью автосуммирования.

4. Добавьте, если необходимо, дополнительные строки перед таблицей и внесите следующую информацию в ячейки А2:А6

БЮДЖЕТ
Составил:
Дата:
Доход в месяц

 

5. Соответственно в ячейках B3 и B6 введите необходимые данные. Ячейку B4 заполните, используя мастер функций.

6. Посчитайте разность между Доходом в месяц и потраченной суммой, результат заполните в строку Остаток.

7. Подведите итог по каждому пункту затрат за все шесть месяцев, результат запишите последней колонкой в таблице.

8. Задайте предложенное обрамление таблицы.

9. Присвойте листу имя «Личный бюджет».

Вариант 3

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

1. Подготовьте таблицу для более детального изучения туров, для её заполнения используйте ниже предложенную информацию. Для ввода одинаковых данных, для копирования формул пользуйтесь маркером заполнения.

Программа	Продолжительность тура в днях	Категория отеля	Транспорт	Стоимость тура на одного человека в $
Вена		**	Автобус
Прага		***	Автобус
Париж		**	Авиа
Париж		***	Авиа
Париж		****	Авиа
Кипр		**	Авиа
Кипр		***	Авиа
Кипр, Египет, Израиль		**	Авиа
Греция		***	Авиа	1 000
Австралия		***	Авиа	1 800

2. Дополните таблицу колонкой, в которой нужно посчитать стоимость тура в рублях, с учётом текущего курса. Посчитайте эту стоимость. К полученному результату примените денежный формат, с двумя десятичными знаками.

3. Подсчитайте, во сколько обойдётся для каждой программы один день проживания, в $.

4. Если Алла Петровна возьмет с собой дочь Женю, то на все туры, кроме поездки в Грецию, предлагается скидка 12%. Озаглавьте самостоятельно столбец и посчитайте в нем стоимость путевки для ребёнка, в $.

5. В следующем столбце вычислите, сколько будет стоить поездка, если Алла Петровна поедет с мужем и дочкой.

6. Озаглавьте таблицу.

7. Отсортируйте таблицу в порядке убывания стоимости туров.

8. Отформатируйте таблицу следующим образом:

· Для заголовка таблицы примените: полужирный стиль начертания, выравнивание по центру (и вертикальное, и горизонтальное). Также, для всех заголовков, кроме Программа, установите перенос по словам и разверните текст на 90º. После этого, при необходимости, подберите высоту строки и ширину столбцов.

· Задайте обрамление таблицы таким образом, чтобы внутренние линии были тонкими, общий контур – двойная линия.

9. Установите следующие параметра страницы: ориентация – альбомная, верхний колонтитул – дата (в центре), нижний колонтитул – Ваша Фамилия (слева), Имя (Справа).

10. Назовите данный лист «Стоимость туров».

Вариант 4

1. Вам необходимо построить плоскую круговую диаграмму, которая будет показывать доли различных типов фирм в общем числе коммерческих организаций Франции (в %). Для этого необходимо начертить и заполнить самостоятельно таблицу для построения такой диаграммы. У Вас есть показатели по следующим категориям: индивидуальные фирмы, товарищества, акционерные фирмы.

2. Построив диаграмму, расположите её на экране так, чтобы была видна и таблица, и диаграмма одновременно (если необходимо, измените пропорции диаграммы).

3. Измените плоский вид диаграммы на объёмный. Установите метки значений. Поверните диаграмму таким образом, чтобы подписи располагались, оптимально.

4. Вынесите из общего круга один из секторов и измените его цвет.

5. Измените начертание и цвет подписи выделенного сектора.

6. Озаглавьте лист «Доли фирм Франции».

Вариант 5

1. В ячейке А2 ввести заголовок: Расход денежных средств на автомобиль

2. Начиная с ячейки А4, ввести и заполнить ниже предложенную таблицу:

· В строке Цена за литр достаточно заполнить только одну ячейку В6 (7,50р.) и в дальнейшем на неё ссылаться.

· Посчитать общую Стоимость бензина.

· В ячейках Затраты на запчасти и Услуги вести любые данные.

· Посчитать, сколько всего потрачено в каждом месяце на автомобиль.

· Заголовки строк и столбцов форматировать так, как предложено ниже.

Месяц	январь	Февраль	март	апрель	май	июнь
Количество
Цена за литр
Стоимость бензина
Затраты на запчасти
Услуги (стоянка, автомойка, шиномонтаж и т.д.)
ИТОГО ПОТРАЧЕНО

3. Введите две дополнительные строки между строками Затраты назапчасти и Услуги и озаглавьте их соответственно: Скидка и Итого на запчасти. Добавьте столбец между заголовком и названиями месяцев (т.е. столбец между А и В) и введите в ячейку В9 12%. Подсчитайте эту скидку на запчасти для месяцев январь, март, июнь и посчитайте затраты на запчасти с учётом скидки.

4. Учитывая все данные, исправьте формулу в строке Итого потрачено.

5. Округлите до сотых (т.е. до копеек) и присвойте денежный формат ячейкам, где речь идёт о цене.

6. Выделите строки Месяц, Стоимость бензина, Итого на запчасти и Услуги. Постройте для них объёмную гистограмму (ряды данных: в столбцах), воспользовавшись при этом мастером диаграмм. Поверните гистограмму в пространстве так, чтобы были видны все данные, не забудьте подписать оси и названия диаграммы.

7. Постройте плоскую гистограмму на отдельном листе для строк Месяц, Стоимость бензина, Итого на запчасти, Услуги и Итого потрачено (ряды данных в строках), подпишите оси и вставьте название диаграммы.

8. На гистограмме, расположенной на отдельном листе, измените цвета маркеров. Удалите ряд данных показывающий затраты на запчасти по месяцам вместе со скидкой. Добавьте ряд данных, отображающий затраты на запчасти без скидки. Удалите легенду.

9. Назовите лист «Расход средств на автомобиль».

Вариант 6

Представьте, что Вы владелец крупной фирмы, Вам предлагают список стран, с которыми Вы можете сотрудничать.

1. Создайте предложенную ниже таблицу:

Страна	Объем поставки продукции, $ млн.
ВЕЛИКОБРИТАНИЯ
ВЬЕТНАМ
ГЕРМАНИЯ
ИТАЛИЯ
КИТАЙ
АВСТРАЛИЯ
ФРАНЦИЯ
ЮЖНАЯ КОРЕЯ
ЯПОНИЯ

2. Дополните таблицу ещё одной колонкой, в которой, используя логическую функцию ЕСЛИ, Вы должны разрешить следующую задачу:если Объем поставляемой продукции более 40 млн. $, то Ваша фирма будет сотрудничать с фирмами данной страны.

3. Добавьте колонку с названием Бытовая техника, $ млн. и заполните её данными на Ваше усмотрение.

4. Выполните в следующей колонке следующий логический расчёт: ЕСЛИ величина поставки Бытовой техники какой-либо страны превышает величину поставки из Италии, то Ваша фирма пересмотрит все контракты заключенные с фирмами этой страны, иначе всё остаётся по-прежнему.

5. Задайте выше предложенное оформление таблицы.

6. Присвойте листу имя «Логические функции».

Вариант 7

1. Необходимо начислить заработную плату работникам предприятия ООО «Окна плюс». Для этого в ячейке А2 введите название таблицы «Расчёт заработной платы за полгода».

2. В ячейках А4:D17 введите ниже предложенную таблицу:

Фамилия	Стаж	Оклад	ДВН
Алексеев		2 200р.
Ванин		1 500р.
Гарин		3 100р.
Григорьев		1 500р.
Демидов		1 800р.
Дроздов		2 600р.
Иванов		3 400р.
Леонтьев		1 200р.
Пантелеев		2 900р.
Петров		3 700р.
Сидоров		2 000р.
Сидорчук		2 000р.

3. Рассчитайте ДВН (Дальневосточная надбавка). Величина ДВН зависит от стажа следующим образом: ЕСЛИ стаж ≥ 5 годам, то ДВН составляет 30% от оклада; ЕСЛИ стаж ≥ 3 года, то ДВН составляет 20% от оклада; все остальные 10% от оклада.

4. В ячейках Е4 введите Сумма к выдаче. Ниже в ячейках Е5:J5 введите названия месяцев с января по июнь, используя маркер автозаполнения. При необходимости сдвиньте нужные ячейки и выровняйте таблицу.

5. Подсчитайте сумму к выдаче за январь. Используя абсолютные ссылки в адресах, скопируйте полученные суммы на остальные месяцы.

6. В ячейках I19:I21 введите соответственно Минимальная, Максимальная и Средняя.

7. Найдите минимальную, максимальную и среднюю сумму к выдаче в ячейках J19:J21, соответственно.

8. Выполните обрамление таблицы, как предложено выше.

9. Присвойте листу имя «Заработная плата».

Вариант 8

1. Создайте предложенную ниже таблицу:

Комната	Общая площадь	Квартира	Дом	Цена, у.е.	Задача 1	Задача 2	Задача 3
Б/л	Этаж	Материал
	30,00	БЛК		ПНЛ	15 700
	32,00	БЛК		КРП	14 500
	29,00	ЛДЖ		ПНЛ	15 000
гост. кух.	35,00	-		КРП	12 000
	36,00	БЛК		КРП	15 000
гост. кух.	24,00	БЛК		ПНЛ	10 500

2. Представьте, что Вы хотите купить квартиру. Для её приобретения необходимо выполнение следующих условий:

Задача 1. Общая площадь должна быть больше 30 м² и этаж не ниже третьего.

Задача 2. Этаж ниже пятого, дом кирпичный (КРП) и цена меньше либо равна 14 000.

Задача 3. Общая площадь должна быть больше 30м², цена меньше либо равна 15 000, квартира с балконом (БЛК) или лоджией (ЛДЖ).

3. Задайте предложенное выше обрамление таблицы.

4. Назовите лист «Выбор квартиры».

Вариант 9

1. Заполните таблицу (добавьте самостоятельно ещё 5-6 игроков и соответственно количество набранных очков в играх):

Игрок	Игра 1	Игра 2	Игра 3	Итого	Приз
Маша
Ира
Саша
Денис

2. Представьте, что Вы пришли с друзьями играть в боулинг, заказав при этом по 3 игры на каждого. По сумме очков Вы или Ваши друзья могут получить приз. Необходимо выяснить, кто получит приз, и какой.

3. В колонке Приз введите следующие условия получения призов: если в результате трёх игр набираете 98 очков, то Вы можете сыграть бесплатно ещё в одну игру, если набираете 252 очка, то приз – магнитофон, если набираете 367 очков, приз – телевизор.

4. Оформите таблицу, как предложено выше.

5. Присвойте листу имя «Игра».