Контрольные задания. Табличный процессор EXCEL

Вариант 1. Домашний бюджет.

1. Средствами Excel создайте таблицу, макет которой представлен в табл.8.

2. Рассчитайте итоговые значения.

3. Постройте на ваше усмотрение любые диаграммы, анализирующие домашний бюджет.

Таблица 8

Домашний бюджет

Расходы в руб.	Январь	Февраль	Март	ИТОГО
Квартплата				?
Электричество				?
Телефон				?
Транспортные услуги				?
расходы на питание				?
прочие расходы				?
Суммарные расходы	?	?	?	?

Вариант 2. Переоценка основных средств.

1. Средствами Excel создайте таблицу аналогичную табл.9. Неизвестные показатели рассчитываются по формулам:

ОС = БС-ИД, ВП = БС*K

ВО = ОС*К, где К=3, если БC> 500млн.руб, иначе К = 2.8.

2. Оформите таблицу:

• расположите по центру все числовые данные и представьте их с точностью до двух знаков после запятой;
• измените цвет итоговой строки и размер шрифта;

3. Определите максимальный износ до переоценки.

4. Определите среднее значение остаточной стоимости.

5. Постройте гистограмму по данным графы «Восстановительная стоимость».

 

Таблица 9

Накопительная ведомость к заполнению таблицы по переоценке основных средств
Наименование объекта	Балансирова нная стоимость до переоценки	Износ до переоценки	Остаточ­ ная стоимость	Восстановительная стоимость
			Полная	Остаточная
	(БС)	(ИД)	(ОС)	(ВП)	(ВО)
Заводоуправление	1576, 2	568, 0	?	?	?
Диспетчерская	76, 0	15, 7	?	?	?
Цех №2	965, 3	367, 5	?	?	?
Цех №3	2200, 0	1002, 0	?	?	?
Склад	181, 6	18, 3	?	?	?
Итого	?	?	?	?	?

Вариант 3. Расчет себестоимости изделий.

1. Средствами Excel создайте таблицу, макет которой представлен в табл 3.

Формулы для расчетов

ПЗ = СМ+ЗР

ОЗ = ПЗ*0.5

СС – ПЗ + ОЗ

2. Оформите таблицу по своему усмотрению.

3. Определите изделие с максимальной и минимальнойсебестоимостью.

4. Постройте цилиндрическую диаграмму по данным графы «Прямые затраты».

5. Защитите клетки с формулами.

Таблица 3

Расчет себестоимости новых изделий в ателье ”Трикотажница”
№ п/п	Наименование изделий	Прямые затраты	Общехозяйственные затраты	Себестоимость изделий
Стоимость материалов	Зарплата рабочих	Всего
		(СМ)	(ЗР)	(ПЗ)	(О3)	(СС)
	Платье			?	?	?
	Костюм			?	?	?
	Юбка			?	?	?
	Джемпер дет.			?	?	?
	Брюки жен.			?	?	?
	Итого:	?	?	?	?	?

 

Вариант 4. Расчет дохода по кредитной операции.

1. Средствами Excel создайте таблицу, макет которой представлен в табл.4.

Формулы для расчетов:

АH = ВР*0, 003

СК = ВР*0, 015

БП = ВР – АН – СК + ПК

2. Оформите таблицу по своему усмотрению.

3. Рассчитайте среднее значение налога на пользователей автодорог и представьте его с точностью до 2-х знаков после запятой.

4. Постройте круговую диаграмму по данным графы «Баланс», «Прибыль».

 

Таблица 4

Расчет дохода по кредитной операций предприятий оптовой торговли (млн. руб.)
Наименование предприятий	Выручка от реализации	Налог на пользователей автодорог	Сбор на содержание	Сумма процентов по кредиту	Балансовая прибыль
	(ВР)	(АН)	(СК)	(ПК)	(БП)
" Мечта"		?	?		?
" Диета"		?	?		?
" Корона"		?	?		?
" Белочка"		?	?		?
Итого:	?	?	?	?	?

Вариант 5. Расчет коэффициента пропускной способности и производственной мощности

1. Средствами Excel создайте таблицу, макет которой представлен в табл.5.

Формулы для расчетов:

КП = Д/П

КЗ = П/Д, Используя логические формулы, предусмотрите деление на 0.

2. Определите максимальный и минимальный коэффициент загрузки оборудования;

3. Отсортируйте таблицу по данным графы «Группы оборудования»;

4. Проанализируйте с помощью графика «Коэффициент загрузки оборудования».

Таблица 5

Расчет коэффициента пропускной способности и производственной мощности
Группы оборудования	Действительный фонд времени оборудования (в часах)	Прогрессивная трудоемкость программы (в часах)	Коэффициент пропускной способности	Коэффициент загрузки оборудования
	(Д)	(П)	(КП)	(КЗ)
Токарные			?	?
Сверлильные			?	?
Шлифовальные			?	?
Фрезерные			?	?
Строгальные			?	?

Вариант 6. Расчет налога на прибыль.

1. Средствами Excel создайте таблицу, макет которой представлен в табл.6.

2. Введите формулы для расчета прибыли и налога, исходя из условий:

Прибыль = Продажа - Расходы;

Налог на прибыль до 20000 - 10% от прибыли;

Налог на прибыль свыше 20000 - 15% от прибыли.

3. Установите в ячейках с числовыми данными денежный формат;

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

 

Таблица 6

2002 год	январь	февраль	март	апрель
Продажа
Расходы
Прибыль	?	?	?	?
Налог на прибыль	?	?	?	?

Вариант 7. Расчет комиссионных.

1. Средствами Excel создайте таблицу, макет которой представлен в табл.7.

2. Введите формулы для расчета комиссионных, исходя из следующих условий:

комиссионные:

 

стоимость товара	комиссионные
до 10 000руб.	5% от стоимости
10001-30000 руб.	4% от стоимости;
30001-70000 руб.	3% от стоимости;
свыше 70000 руб.	1% от стоимости

3. Выполните условное форматирование в зависимости от стоимости товара;

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

 

Таблица 7

Наименование товара	Стоимость товара, руб.	Комиссионные, руб.	Продано, шт.	Получено, руб.
шкаф	35 000	?		?
шкаф	24 300	?		?
стенка	124 500	?		?
диван	3 500	?		?
Итого:	?	?	?	?

Вариант 8. Обработка результатов тестирования.

1. Средствами Excel создайте таблицу, макет которой представлен в табл.8.

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

3. Вычислите средний балл по результатам каждого теста;

4. Рассчитайте оценку по следующему критерию: если результат меньше 18, то оценка 2; если меньше 33, то оценка - 3; если результат меньше 49, то результат - 4, иначе - 5;

5. Найдите занимаемое место по результату тестирования с помощью функции РАНГ()

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

Таблица 8

№	Фамилия И.О.	Тест1	Тест	ТестЗ	Результат	Оценка	Место
	Михайлов А.А.				?	?	?
	Муравьев А.Н.				?	?	?
	Щеглов А.П.				?	?	?
	Алексеев Л.П.				?	?	?
	Солодов А.С.				?	?	?
	Петров И. А.				?	?	?
	Иванов С.С.				?	?	?
	Яковлев П. Р.				?	?	?
	Осипов А.В.				?	?	?
	Сидоров Ф.Т.				?	?	?
	средний балл	?	?	?	?	?	?

Вариант 9. Обработка данных метеостанции.

1. Средствами Excel создайте таблицу, макет которой представлен в табл. 9.

2. Рассчитайте суммарное количество осадков за каждый представленный год и за 3 года;

3. Найдите минимальное и максимальное значение за каждый год и за 3 года;

 

Обработка данных метеостанции

Количество осадков (в мм)

Таблица 9

Месяц/год				Среднее за 3 года
Январь	37, 2	34, 5		?
Февраль	11, 4	51, 3	1, 2	?
Март	16, 5	20, 5	3, 8	?
Апрель	19, 5	26, 9	11, 9	?
Май	11, 7	45, 5	66, 3	?
Июнь	129, 1	71, 5		?
Июль	57, 1	152, 9	50, 6	?
Август	43, 8	9б, 6	145, 2	?
Сентябрь	85, 7	74, 8	79, 9	?
Октябрь		14, 5	74, 9	?
Ноябрь	12, 5		56, 6	?
Декабрь	21, 2	22, 3	9, 4	?
Суммарно	?	?	?	?
Максимум	?	?	?	?
Минимум	?	?	?	?
Среднемесячное кол-во осадков	?	?	?	?
Стандартное отклонение от среднего	?	?	?	?
Кол-во засушливых месяцев	?	?	?	?
Осадки в не засушливые месяцы	?	?	?	?

 

4. Вычислите среднемесячное количество осадков;

5. Вычислите стандартное отклонение от среднего количества выпавших осадков;

6. Определите количество засушливых месяцев в каждом году и за три года, считая засушливым месяц, в котором выпавших осадков менее 10 мм (функция СЧЁТЕСЛИ());

7. Определите количество осадков в не засушливые месяцы, при условии, что количество выпавших осадков должно быть больше или равно 10мм (функция СУММЕСЛИ());

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

Вариант 10. Анализ температуры воздуха.

1. Средствами Excel создайте таблицу, макет которой представлен в табл. 10.

2. Найдите минимальные, максимальные и средние значения температуры за месяц и за каждую из трех декад;

3. Найдите средние значения температур для каждого из дней недели;

7. Построить диаграмму (График) изменения температуры в течение месяца.

8. Постройте диаграмму (Гистограмму) отражающую средние температуры по декадам.

Таблица 10

Сведения о температуре воздуха в сентябре 1999 года.

№ недели День недели					Средняя температура
понедельник	-9.8	-10.4	-10.9	-11.5	?
вторник	-11.7	-11.9	-11.8	-11.7	?
среда	-11.2	-10.3	-9.0	-7.4	?
четверг	-5.2	-3.7	-2.4	-1.1	?
пятница	-0.3	+0.4	+0.9	+0.8	?
суббота	+0.5	-0.2	-0.7	-1.1	?
воскресенье	-1.6	-2.2	-2.7	-3.1	?
	1-я декада	2-я декада	3-я декада	Февраль
Минимальные значения	?	?	?	?
Максимальные значения	?	?	?	?
Средние значения	?	?	?	?

Вариант 11. Моря Атлантического океана

1. Средствами Excel создайте таблицу, макет которой представлен в табл. 11.

2. Определить среднюю глубину каждого из морей;

3. Отсортировать моря по убыванию значения Средняя глубина;

4. Определите общую площадь морей, омывающих Россию

5. Определите суммарный объем воды в морях, не омывающих Россию;

6. Для каждого из морей рассчитать долю его площади от общей площади всех морей (не забудьте об абсолютной и относительной адресации!)

7. Построить диаграмму, на двух осях, на одной – значения площади морей, на другой оси – объем.

Таблица 11

Некоторые характеристики морей Атлантического океана.

№ п/п	Название	Площадь, 10³ км²	Объём, 10³ км³	Средняя глубина	Доля площади от общей площади всех морей
	Азовское		0.3	?	?
	Балтийское			?	?
	Карибское			?	?
	Мраморное			?	?
	Северное			?	?
	Средиземное			?	?
	Чёрное			?	?
	Общая площадь морей, омывающих Россию:	?
	Суммарный объем воды в морях, не омывающих Россию:	?

Вариант 12. Моря Северного Ледовитого океана

1. Средствами Excel создайте таблицу, макет которой представлен в табл. 12.

2. Определить среднюю глубину каждого из морей;

3. Отсортировать моря по убыванию значения Средняя глубина;

4. Определите общую площадь морей, омывающих Россию

5. Определите суммарный объем воды в морях, не омывающих Россию;

6. Для каждого из морей рассчитать долю его площади от общей площади всех морей (не забудьте об абсолютной и относительной адресации!)

7. Построить диаграмму, на двух осях, на одной – значения площади морей, на другой оси – объем.

Таблица 12

Некоторые характеристики морей Северного Ледовитого океана.

№ п/п	Название	Площадь, 10³ км²	Объём, 10³ км³	Средняя глубина	Доля площади от общей площади всех морей
	Баренцево			?	?
	Баффина			?	?
	Белое			?	?
	Бофорта			?	?
	В. Сибирское			?	?
	Гренландское			?	?
	Карское			?	?
	Лаптевых
	Норвежское
	Чукотское
	Общая площадь морей, омывающих Россию:	?
	Суммарный объем воды в морях, не омывающих Россию:	?

Вариант 13. Ледники и льды Земли.

1. Средствами Excel создайте таблицу, макет которой представлен в табл. 13.

2. Определить среднюю толщину (в метрах) для каждого ледника;

3. Отсортировать моря по убыванию значения Средняя толщина льда;

4. Определить максимальную и минимальную толщину ледяных покровов Земли

5. Для каждого из ледяных покровов рассчитать его долю от общей площади всех покровов (% площади всех покровов). Не забудьте об абсолютной и относительной адресации!

6. Построить круговую диаграмму, отражающую Доли площади ледников и льда Земли.

Таблица 13

Некоторые характеристики ледников и льда Земли

№ п/п	Ледники	Площадь, 10³ км²	Объём, 10³ км³	Средняя толщина льда	% площади всех покровов
	Баренцево			?	?
	Баффина			?	?
	Белое			?	?
	Бофорта			?	?
	В. Сибирское			?	?
	Гренландское			?	?
	Карское			?	?
	Лаптевых			?	?
	Норвежское			?	?
	Чукотское			?	?
	Максимальная толщина ледяных покровов Земли:	?
	Минимальная толщина ледяных покровов Земли:	?

Вариант 14. Обработка данных о загрязнении атмосферы.

1. Средствами Excel создайте таблицу, макет которой представлен в табл. 14.

2. Для каждого из загрязняющих веществ для трех сроков наблюдения определить:

среднюю концентрацию;

максимальные и минимальные значение концентрации.

3. Построить диаграмму (График) изменения концентраций за весь период наблюдения.

4. Постройте диаграмму (Гистограмму) отражающую средние концентрации по трем срокам наблюдений для двух загрязняющих веществ.

Таблица 14

Сведения о концентрации загрязняющих веществ в атмосферном воздухе (мг/м³)

Дата	Время	Азота оксид	Фенол
03.01.1996		0.0270	0.0020
03.01.1996		0.0170	0.0070
03.01.1996		0.0100	0.0050
04.01.1996		0.0140	0.0030
04.01.1996		0.0720	0.0010
04.01.1996		0.0160	0.0020
05.01.1996		0.0160	0.0020
05.01.1996		0.0070	0.0010
05.01.1996		0.0200	0.0030
06.01.1996		0.0140	0.0030
06.01.1996		0.0410	0.0030
06.01.1996		0.0550	0.0010
09.01.1996		0.0190	0.0020
09.01.1996		0.0160	0.0020
09.01.1996		0.0190	0.0010
10.01.1996		0.0230	0.0010
10.01.1996		0.0190	0.0030
10.01.1996		0.0290	0.0010
Максимальные концентрации:
Срок
Азота оксид	?	?	?
Фенол	?	?	?
Дата наблюдения:	?	?	?
Средние концентрации:
Азота оксид	?	?	?
Фенол	?	?	?

Вариант 15. Анализ работы правоохранительных органов.

Средствами Eхсеl выполните задание:

1. Создайте табл. 15 на листе 1. и. Рассчитайте по формулам графы:

«Всего», «Процент раскрытых дел» и «Процент нераскрытых дел».

Анализ работы правоохранительных органов

г. Санкт-Петербурга за I полугодие

Таблица 15

Район	Кол-во раскрытых дел	Кол-во нераскрытых дел	Всего	Процент раскрытых дел	Процент нераскрытых дел
Адмиралтейский			?	?	?
Василеостровский			?	?	?
Кировский			?	?	?
Красносельский			?	?	?
Московский			?	?	?
Невский			?	?	?
Петроградский			?	?	?
Приморский			?	?	?
Фрунзенский			?	?	?
Центральный			?	?	?
Всего:	?	?	?	?	?

2. Создайте табл. 16 на листе 1. Рассчитайте по формулам графы:

«Всего», «Процент раскрытых дел» и «Процент нераскрытых дел».

3. Переименуйте Лист 1 в «Анализ дел».

4. Постройте сводную таблицу с диаграммой на отдельном листе, используя данные табл. 15 и табл. 16 (район, кол-во раскрытых и нераскрытых дел). Переименуйте лист со сводной таблицей в «Сводные данные».

5. Постройте круговые диаграммы, по итоговым данным табл. 15 и табл. 16, расположенные на листе «Анализ дел».

6. Выполните условное форматирование на листе «Анализ дел». Проанализируйте графы: «Процент раскрытых дел» и «Процент нераскрытых дел». Условие и цветовое отображение придумайте по своему усмотрению.

Анализ работы правоохранительных органов

г. Санкт-Петербурга за II полугодие

Таблица 16

Район	Кол-во раскрытых дел	Кол-во нераскрытых дел	Всего	Процент раскрытых дел	Процент нераскрытых дел
Адмиралтейский			?	?	?
Василеостровский			?	?	?
Кировский			?	?	?
Красносельский			?	?	?
Московский			?	?	?
Невский			?	?	?
Петроградский			?	?	?
Приморский				?	?
Фрунзенский			?	?	?
Центральный			?	?	?
Всего:	?	?	?	?	?