- •Задача: № 6
- •Задача: №7
- •Задача: № 8
- •Задача: № 9
- •Задача: № 10
- •Задача: № 11
- •Задача: № 15
- •Задача: № 16
- •Задача: № 17
- •Задача: № 12
- •Задача: № 13
- •Задача: №14
- •Задача: №18
- •Задача: № 19
- •Задача: № 20
- •Задача: № 21
- •Задача: № 22
- •Задача: № 23
- •Задача: № 24
- •Задача: № 25
- •Задача: № 26
- •Задача: № 27
- •Задача: № 28
- •Задача: № 29
- •Задача: № 30
Понятие электронной информации. Электронный документ. Электронная цифровая подпись.
Графики в MS Excel. Трендовый анализ в MS Excel.
Задача: № 30
Билеты утверждены на заседании кафедры
Протокол №1 от 6.09.2012 г. Зав. кафедрой_____
Задача 1. Создать электронную таблицу для расчетов по формулам. Выполнить необходимые расчеты, копирование, редактирование, форматирование. Построить диаграмму, отображающую переменные х и у. Очистить таблицу. Расчет среднего значения выполнять с помощью функции Excel.
Таблица 1. Задача 1
Номер расчета |
Переменные |
Формулы расчета |
||||
х |
у |
х·у |
у2 |
х2 |
||
1 |
42,3 |
19,2 |
|
|
|
|
2 |
30,1 |
28,7 |
|
|
|
|
3 |
45,4 |
49,5 |
|
|
|
|
Сумма, среднее |
Сумма |
Сумма |
Среднее |
Среднее |
Среднее |
|
Задача 2. С помощью функции ВПР рассчитать размер заработной платы работников в зависимости от должности. Заработная плата начисляется умножением коэффициента, соответствующего должности сотрудника на величину базовой оплаты (например, 20000 р.) труда за месяц.
Тарифная сетка Расчет заработной платы за март 2010 г.
Фамилия |
Должность |
Заработная плата |
Антонов Б. |
Менеджер |
|
Белова Д. |
Менеджер |
|
Горин П. |
Начальник отдела |
|
Зверева И. |
Бухгалтер |
|
Перова Т. |
Бухгалтер |
|
Должность |
Коэффициент |
Менеджер |
4,84 |
Бухгалтер |
6,11 |
Начальник отдела |
8,71 |
Секретарь |
4,12 |
Задача 3. Задача 1. На основании исходных таблиц 1 и 2 создать таблицу 3, объединяющую показатели продажи ГСМ в двух АЗС методом консолидации со связями с исходными данными. Указание. В таблицы 1 и 2 ввести необходимые формулы.
Таблица 1. АЗС № 1 Таблица 2. АЗС № 2
-
Месяц
Количество, л.
Месяц
Количество, л
Бензин 95
1325
Бензин 95
1455
Дизельное топливо
2180
Дизельное топливо
2200
Бензин 80
1500
Бензин 80
1450
Среднее
Среднее
Задача 4. На основании исходных таблиц 1 и 2 создать таблицу 3, суммирующую количество краж за два месяца в трех округах города N-ск методом консолидации со связями с исходными данными.
Указание. В таблицы 1 и 2 ввести необходимые формулы.
Таблица 1. Количество краж в феврале Таблица 2. Количество краж в марте
Округ |
Количество краж |
|
Округ |
Количество краж |
№ 1 |
132 |
|
№ 1 |
125 |
№ 2 |
89 |
|
№ 2 |
92 |
№ 3 |
79 |
|
№ 3 |
65 |
Итого |
|
|
Итого |
|
Задача 5. На основании исходных таблиц 1 и 2 создать таблицу 3, объединяющую показатели фирмы за два года методом консолидации со связями с исходными данными.
Таблица 1.2010 г. Таблица 2. 2011 г.
-
Фирма
Показатели
Фирма
Показатели
Отдел 1
19756,43
Отдел 1
19800,57
Отдел 2
27800,56
Отдел 2
28910,34
Отдел 3
15047,21
Отдел 3
14543,78
Итого
Итого
Задача 6. Создать электронную таблицу для расчета заработной платы. Тариф оплаты составляет 349,13 руб. за один рабочий день. Удерживается 13% от величины начисленной заработной платы. Выполнить необходимые расчеты, копирование, редактирование, форматирование. Использовать абсолютные ссылки. Построить диаграмму, отображающую сумму к выдаче. Расчет среднего значения выполнять с помощью функции Excel.
Таблица 1. Сводная ведомость за декабрь
-
Ф.И.О.
Число дней
Начислено
Удержано
Сумма к выдаче
Цветкова А.И.
23
Ромашкина И.Д.
18
Незабудкин Т.Н.
25
Лопухов О.П.
11
Астров И.А.
22
Итого, среднее
Среднее
Итого
Итого
Итого
Указание. Задача должна быть решена в общем виде, т. е. в формулах расчетов должны стоять ссылки на ячейки, в которых находятся значения тарифа и налога, а не числа!
Задача 7. Создать электронную таблицу для расчета заработной платы. Тариф оплаты составляет 453,67 руб. за один рабочий день. Удерживается 13% от величины начисленной заработной платы. Выполнить необходимые расчеты, копирование, редактирование, форматирование. Использовать абсолютные ссылки.
Таблица 1. Сводная ведомость за апрель
-
Ф.И.О.
Число
дней
Всего
начислено
Всего
удержано
Сумма к
выдаче
Ян Т.Т.
24
Тен Ю.Ю.
17
Мен Л.Л.
19
Хан И.Ю.
22
Итого, среднее
Среднее
Итого
Итого
Итого
Указание. Задача должна быть решена в общем виде, т. е. в формулах расчетов должны стоять ссылки на ячейки, в которых находятся значения тарифа и налога, а не числа!
Задача 8. Создать электронную таблицу для расчета заработной платы. Тариф оплаты составляет 79,63 руб. в час. Коэффициент (КФ) к основной заработной плате (ОСЗ) составляет 50%. От величины начисленной заработной платы удерживается налог 13%. Выполнить необходимые расчеты, копирование, редактирование, форматирование. Построить диаграмму, отображающую основную заработную плату служащих.
Таблица 1. Расчет почасовой заработной платы за март
Ф.И.О. |
Часы |
ОСЗ |
КФ (50%) |
Всего начислено |
Удержано |
Сумма к выдаче |
Сатин Ю.М. |
356 |
|
|
|
|
|
Яншин П.Р. |
437 |
|
|
|
|
|
Арбузова В.Н. |
267 |
|
|
|
|
|
Юрьева С.П. |
359 |
|
|
|
|
|
Итого, среднее |
Среднее |
Среднее |
Итого |
Итого |
Итого |
Итого |
Указание. Задача должна быть решена в общем виде, т. е. в формулах расчетов должны стоять ссылки на ячейки, в которых находятся значения тарифа, коэффициента КФ и налога, а не числа! Использовать абсолютные ссылки.
Задача 9. Создать электронную таблицу для анализа плановых и фактических показателей спроса на компьютеры. Выполнить необходимые расчеты, копирование, редактирование, форматирование. Построить диаграмму, отображающую плановые и фактические показатели спроса на компьютеры. Расчет среднего значения выполнять с помощью функции Excel.
Таблица 1. Показатели спроса на компьютеры в 2005 г.
-
Месяц
Спрос
План
Факт
% (Факт/ План)
Январь
1345
1360
Февраль
1460
1401
Март
1500
1634
Апрель
1575
1560
Май
1580
1537
Июнь
1530
1493
Итого, среднее
Итого
Итого
Среднее
Задача 10. На основании исходных таблиц 1 и 2 создать таблицу 3, объединяющую показатели продажи компьютеров в двух магазинах методом консолидации со связями с исходными данными. Указание. В таблицы 1 и 2 ввести необходимые формулы.
Таблица 1. Магазин № 1 Таблица 2. Магазин № 2
-
Месяц
Количество
Месяц
Количество
Январь
327
Январь
456
Февраль
189
Февраль
200
Март
500
Март
501
Среднее
Среднее
Задача 11.
На основании исходных таблиц 1 и 2 создать таблицу 3, суммирующую количество преступлений за два года в трех районах методом консолидации со связями с исходными данными.
Указание. В таблицы 1 и 2 ввести необходимые формулы.
Таблица 1. 2008 г. Таблица 2. 2009 г.
Район |
Количество преступлений |
|
Район |
Количество преступлений |
Светлый |
932 |
|
Светлый |
1125 |
Ясный |
1089 |
|
Ясный |
920 |
Тихий |
879 |
|
Тихий |
657 |
Итого |
|
|
Итого |
|
Задача 12. Создать электронную таблицу для расчета заработной платы. Тариф оплаты составляет 89,63 руб. в час. Коэффициент (КФ) к основной заработной плате (ОСЗ) составляет 50%. От величины начисленной заработной платы удерживается налог 13%. Выполнить необходимые расчеты, копирование, редактирование, форматирование. Построить диаграмму, отображающую основную заработную плату служащих.
Таблица 1. Расчет почасовой заработной платы за март
Ф.И.О. |
Часы |
ОСЗ |
КФ (50%) |
Всего начислено |
Удержано |
Сумма к выдаче |
Дронов К.М. |
356 |
|
|
|
|
|
Петрова Т.Р. |
437 |
|
|
|
|
|
Иванов Л.Н. |
267 |
|
|
|
|
|
Итого, среднее |
Среднее |
Среднее |
Итого |
Итого |
Итого |
Итого |
Указание. Задача должна быть решена в общем виде, т. е. в формулах расчетов должны стоять ссылки на ячейки, в которых находятся значения тарифа, коэффициента КФ и налога, а не числа! Использовать абсолютные ссылки.
Задача 13. Создать электронную таблицу для расчета заработной платы. Тариф оплаты составляет 179,93 руб. в час. Коэффициент (КФ) к основной заработной плате (ОСЗ) составляет 50%. От величины начисленной заработной платы удерживается налог 13%. Выполнить необходимые расчеты, копирование, редактирование, форматирование. Построить диаграмму, отображающую основную заработную плату служащих.
Таблица 1. Расчет почасовой заработной платы за март
Ф.И.О. |
Часы |
ОСЗ |
КФ (50%) |
Всего начислено |
Удержано |
Сумма к выдаче |
Бобров Ю.М. |
396 |
|
|
|
|
|
Гусев П.Р. |
457 |
|
|
|
|
|
Лосев В.Н. |
367 |
|
|
|
|
|
Уткина С.П. |
359 |
|
|
|
|
|
Итого, среднее |
Среднее |
Среднее |
Итого |
Итого |
Итого |
Итого |
Указание. Задача должна быть решена в общем виде, т. е. в формулах расчетов должны стоять ссылки на ячейки, в которых находятся значения тарифа, коэффициента КФ и налога, а не числа! Использовать абсолютные ссылки.
Задача 14. На основании исходных таблиц 1 и 2 создать таблицу 3, объединяющую показатели продажи компьютеров за два квартала методом консолидации со связями с исходными данными.
Таблица 1. 1 квартал Таблица 2. 2 квартал
-
Месяц
Количество
Месяц
Количество
Январь
327
Апрель
456
Февраль
189
Май
200
Март
500
Июнь
501
Среднее
Среднее
Задача 15. Создать электронную таблицу для расчета заработной платы рабочих за один месяц. Тариф оплаты составляет 279,93 руб. в день. Коэффициент (КФ) к основной заработной плате (ОСЗ) составляет 50%. От величины начисленной заработной платы удерживается налог, равный 13%. Выполнить необходимые расчеты, копирование, редактирование, форматирование. Построить диаграмму, отображающую основную заработную плату служащих.
Таблица 1. Расчет заработной платы за месяц
Ф.И.О. |
Число дней |
ОСЗ |
КФ (50%) |
Всего начислено |
Удержано |
Сумма к выдаче |
Бобров Ю.М. |
24 |
|
|
|
|
|
Гусев П.Р. |
17 |
|
|
|
|
|
Лосев В.Н. |
25 |
|
|
|
|
|
Уткина С.П. |
19 |
|
|
|
|
|
Итого, среднее |
Среднее |
Среднее |
Итого |
Итого |
Итого |
Итого |
Указание. Задача должна быть решена в общем виде, т. е. в формулах расчетов должны стоять ссылки на ячейки, в которых находятся значения тарифа, коэффициента КФ и налога, а не числа! Использовать абсолютные ссылки.
Задача 16. Выполнить расчет дохода от продажи молока в торгово-закупочном предприятии. Построить диаграмму, отображающую цены закупки и продажи молока. Выполнить необходимые расчеты, копирование, редактирование, форматирование.
Таблица 1. Расчет дохода от продажи молока во втором квартале 2011 г.
Месяц |
Количество, ц |
Цена, руб./ц. |
Всего, руб |
Доход |
||||
Закупка |
Продажа |
Закупка |
Продажа |
|
||||
Апрель |
250 |
240,5 |
350,5 |
|
|
|
||
Май |
240 |
251,1 |
372,3 |
|
|
|
||
Июнь |
180 |
253,7 |
377,7 |
|
|
|
||
Итого, среднее |
Итого |
Среднее |
Среднее |
Итого |
Итого |
Итого |
||
Задача 17. На основании исходных таблиц 1 и 2 создать таблицу 3, суммирующую показатели продажи ГСМ за два дня методом консолидации со связями с исходными данными. Указание. В таблицы 1 и 2 ввести необходимые формулы.
Таблица 1. Продажа ГСМ 14 марта Таблица 2. Продажа ГСМ 15 марта
АЗС |
Количество, л. |
|
АЗС |
Количество, л. |
№ 1 |
1327,47 |
|
№ 1 |
1456,87 |
№ 2 |
1189,73 |
|
№ 2 |
1200,43 |
№ 3 |
1500,65 |
|
№ 3 |
1501,39 |
Итого |
|
|
Итого |
|
Задача 18. Создать электронную таблицу для расчета заработной платы. Тариф оплаты составляет 253,67 руб. за один рабочий день. Удерживается налог 13% от величины начисленной заработной платы. Выполнить необходимые расчеты, копирование, редактирование, форматирование. Использовать абсолютные ссылки. Построить диаграмму, отображающую сумму к выдаче.
Таблица 1. Сводная ведомость за апрель
-
Ф.И.О.
Число
дней
Всего
начислено
Всего
удержано
Сумма к
выдаче
Дубов Я.Ю.
23
Осинин В.Т.
19
Кленова.Н.С.
23
Ивлева Т.Я.
24
Итого, среднее
Среднее
Итого
Итого
Итого
Указание. Задача должна быть решена в общем виде, т. е. в формулах расчетов должны стоять ссылки на ячейки, в которых находятся значения тарифа и налога, а не числа! Использовать абсолютные ссылки.
Задача 19. Выполнить расчет дохода от продажи яблок в торгово-закупочном предприятии «Заря». Построить диаграмму, отображающую цены закупки и продажи яблок. Расчет среднего значения выполнять с помощью функции Excel. Выполнить необходимые расчеты, копирование, редактирование, форматирование.
Таблица 1. Расчет дохода от продажи яблок в первом квартале 2003 г.
Месяц |
Количество, ц |
Цена, руб./ц. |
Всего, руб |
Доход |
||||
Закупка |
Продажа |
Закупка |
Продажа |
|
||||
Январь |
250 |
24,5 |
29,5 |
|
|
|
||
Февраль |
240 |
25,1 |
31,3 |
|
|
|
||
Март |
180 |
25,7 |
32,7 |
|
|
|
||
Итого, среднее |
Итого |
Среднее |
Среднее |
Итого |
Итого |
Итого |
||
Задача 20.
С помощью функции ВПР рассчитать размер основной заработной платы работников бюджетной отрасли при следующих условиях:
Минимальный размер заработной платы составляет 4600 р.
Основная заработная плата начисляется умножением коэффициента, соответствующего разряду работника из тарифной сетки, на минимальную заработную плату.
Тарифная сетка Основная заработная плата
Фамилия |
Разряд |
Основная заработная плата |
Акунин Б. |
12 |
|
Донцова Д. |
13 |
|
Дашкова П. |
13 |
|
Зверев И. |
14 |
|
Устинова Т. |
15 |
|
Разряд |
Коэффициент |
12 |
5,71 |
13 |
6,12 |
14 |
6,90 |
15 |
7,55 |
Указание. Задача должна быть решена в общем виде, т. е. в формуле расчета размера основной заработной платы должна стоять ссылка на ячейку, а не число 4600!
Задача 21. В зависимости от звания сотрудники отдела (таблица 1) получили денежное поощрение (Пзв), которое рассчитывается по формуле Пзв = КФзв* Пбаз. Здесь КФзв – коэффициенты по званию приведены в таблице 2. Пбаз – базовое значение денежного поощрения. Требуется создать формулу величины денежного поощрения, используя функцию ВПР. Указание. Можно принять Пбаз = 20000 р.
Таблица 1. Список сотрудников Таблица 2. Коэффициенты по званию
Звание |
Коэффициент по званию (КФзв) |
Майор |
70% |
Лейтенант |
35% |
Сержант |
40% |
ФИО |
Звание |
Ляхов А.А |
Сержант |
Ардов П.Р. |
Лейтенант |
Попова О.Л. |
Майор |
Томина Р.Г. |
Майор |
Сатин Я.Д. |
Сержант |
Гуров Т.Т. |
Лейтенант |
Задача 22.
В зависимости от занимаемой должности к заработной плате работников суда (таблица 1) ежемесячно начисляется прибавка (заработная плата умножается на коэффициент) в соответствии с данными таблицы 2. Требуется определить прибавки, используя функцию ВПР.
Таблица 1. Заработная плата за март 2011г. Таблица 2. Прибавка к зарплате
Должность |
Коэффициент зарплаты (КФЗ) |
Судья |
70% |
Секретарь |
35% |
Пристав |
40% |
ФИО |
Должность |
Заработная плата |
Петров А.А |
Секретарь |
25000 |
Седов П.Р. |
Пристав |
35000 |
Попова О.Л. |
Судья |
50000 |
Томина Р.Г. |
Судья |
55000 |
Юрьев Я.Д. |
Судья |
56000 |
Язова Т.Т. |
Секретарь |
27000 |
Задача 23. В зависимости от звания сотрудники отдела (таблица 1) получили денежное поощрение (Пзв), которое рассчитывается по формуле Пзв = КФзв* Пбаз. Здесь КФзв – коэффициенты по званию приведены в таблице 2. Пбаз – базовое значение денежного поощрения. Требуется создать формулу величины денежного поощрения, используя функцию ВПР. Указание. Можно принять Пбаз = 50000 р.
Таблица 1. Список сотрудников Таблица 2. Коэффициенты по званию
Звание |
Коэффициент по званию (КФзв) |
Капитан |
60% |
Лейтенант |
35% |
Сержант |
20% |
ФИО |
Звание |
Пухов А.А |
Сержант |
Седов П.Р. |
Лейтенант |
Лобова О.Л. |
Капитан |
Семина Р.Г. |
Капитан |
Лашин Я.Д. |
Сержант |
Гуров Т.Т. |
Лейтенант |
Задача 24. На листе бюджетного отчета ячейка A7 содержит формулу для расчета текущего бюджета. Составьте логическую функцию, соответствующую приведенным далее условиям. Если результат вычислений по формуле в ячейке A7 меньше или равен 47000, то ваша функция должна отображать "Бюджет соблюдается". В противном случае функция должна отображать "Бюджет превышен".
Задача 25. Составьте логическую функцию, соответствующую приведенным далее условиям. Предположим, что нужно вывести на экран содержимое ячейки С15, если она содержит число строго между 0 и 10 и сообщение "Значение вне интервала" в противном случае.
Задача 26. Законодательство о финансах и кредите
По делам, рассматриваемым в судах общей юрисдикции, государственная пошлина взимается в следующих размерах:
с исковых заявлений имущественного характера при цене иска:
свыше 50 млн. рублей до 100 млн. рублей – 1 млн. 610 тыс. рублей + 2 процента от суммы свыше 50 млн. рублей
свыше 100 млн. рублей до 500 млн. рублей – 2 млн. 610 тыс. рублей + 1 процент от суммы свыше 100 млн. рублей
С помощью логических функций MS Excel составьте формулу вычисления государственной пошлины, если цена иска заявителя равна 65000000 р.
Задача 27. С помощью логических функций MS Excel вычислите f(x), если:
при
-10
х
0 f(x)
=0,5х2,
при х > 0 f(x) =-0,5х2.
Задача 28. На листе бюджетного отчета ячейка В3 содержит формулу для расчета текущего бюджета. Составьте логическую функцию, соответствующую приведенным далее условиям. Если результат вычислений по формуле в ячейке В3 меньше или равен 17000, то ваша функция должна отображать "В пределах бюджета". В противном случае функция должна отображать "Превышение бюджета".
Задача 29. Составьте логическую функцию, соответствующую приведенным далее условиям. Предположим, что нужно вывести на экран содержимое ячейки Е5, если она содержит число строго между 10 и 100 и сообщение "Значение вне интервала" в противном случае.
Задача 30. Законодательство о финансах и кредите
По делам, рассматриваемым в судах общей юрисдикции, государственная пошлина взимается в следующих размерах:
с исковых заявлений имущественного характера при цене иска:
до 1 млн. рублей – 5 процентов от цены иска
свыше 1 млн. рублей до 10 млн. рублей – 50 тыс. рублей + 4 процента от суммы свыше 1 млн. рублей
С помощью логических функций MS Excel составьте формулу вычисления государственной пошлины, если цена иска заявителя равна 700000 р.
