1 Додж М., Стинсон К. Эффективная работа: Excel 2002. – СПб.: Питер, 992 с.
2 Лавренов С.М. Excel: Сборник примеров и задач. – М.: Финансы и статистика, 2001 – 336 с.
Приложение А
(обязательное)
Варианты индивидуальных заданий
Лабораторные работы № 9 - Оформление таблицы и 10 - Вычисления
Вариант 1
Средствами Microsoft Excel оформить таблицу:
Таблица А.1 – Экономические показатели
Показатели | Год | Итого за год | |||
1 кв. | 2 кв. | 3 кв. | 4 кв. | ||
Продано единиц Торговые доходы Торговые расходы | |||||
Валовая прибыль | |||||
Расходы на зарплату Расходы на рекламу Накладные расходы фирмы | |||||
Общие затраты Производственная прибыль Удельная валовая прибыль |
Вычислить:
- “Валовая прибыль” = “Торговые доходы” – “Торговые расходы”;
- “Общие затраты” = “Расходы на зарплату” + “Расходы на рекламу” + “Накладные расходы фирмы”;
- “Производственная прибыль” = “Валовая прибыль” – “Общие затраты”;
- “Удельная валовая прибыль” = “Производственная прибыль”/“Торговые расходы”;
- “Итого за год” = “1 кв.” + “2 кв.” + “3 кв.” + “4 кв.”;
Для строки “Удельная валовая прибыль” задать процентный формат;
Числа, которые больше 250 000 выводить красным цветом;
Вариант 2
Средствами Microsoft Excel составить таблицу:
Таблица А.2 – Объем страховых сделок
Фамилия | Объем страховых сделок | Комиссионное вознаграждение | |||
за III кв. | июль | август | сентябрь | ||
Иванов Федоров Антонов Орлов Смирнов Егоров Громов | |||||
Всего: Средний объем сделок Максимальный объем сделок Минимальный объем сделок |
Вычислить:
- объем страховых сделок за III кв. как сумму столбцов “июль”, “август”, “сентябрь”
- средний, максимальный и минимальный объем сделок.
- комиссионное вознаграждение в расчете 5% от суммы сделок за III кв. при сумме сделок до 500 у.е. и 8% от суммы сделок за III кв. при сумме сделок выше 500 у.е.
В столбце “за III кв.” числа, которые меньше 1000 выводить шрифтом красного цвета, а числа, больше 1200 выводить шрифтом зеленого цвета.
Вариант 3
Средствами Microsoft Excel составить таблицу:
Таблица А.3 – Сумма вклада
№ лицевого счета | Вид вклада | Сумма вклада | |||
остаток входящий | приход | расход | остаток исходящий | ||
R6596 | до востребования | ||||
F6775 | праздничный | ||||
S3445 | срочный | ||||
G8746 | до востребования | ||||
Z7312 | срочный |
Вычислить:
- значение “остаток исходящий” с учетом того, что на все виды вкладов начисляются проценты, а процентная ставка по вкладам до востребования - 2 %; праздничный - 5 %; срочный - 3 %.
Построить диаграмму для столбца 6.
Вариант 4
Средствами Microsoft Excel составить таблицу:
Таблица А.4 – Ведомость кредита
Наименование организации | Дата получения кредита | Сумма кредита | Дата возврата (по договору) | Дата возврата (фактическая) | Сумма возврата |
АО “Роника” | 05.12.01 | 04.03.02 | 22.02.02 | ||
СП “Изотоп” | 25.01.02 | 24.04.02 | 15.05.02 | ||
ООО “Термо” | 03.02.01 | 02.06.02 | 22.06.02 | ||
АОЗТ “Чипы” | 21.11.01 | 20.05.02 | 18.05.02 | ||
АО “Медицина” | 12.05.02 | 11.07.02 | 20.09.02 | ||
АО “Колос” | 08.04.02 | 07.10.02 | 12.10.02 |
Вычислить:
- сумму возврата кредита (ст.6) при условии: если дата возврата фактическая не превышает договорную, то сумма возврата увеличивается на 20% от суммы кредита (ст.3), в противном случае сумма возврата увеличивается на 40% + 1% за каждый просроченный день.
По данным столбцов 1, 3 и 6 построить гистограмму.
Вариант 5
Средствами Microsoft Excel составить таблицу:
Таблица А.5 – Расчет оплаты за электроэнергию
Расчет оплаты за потребленную электроэнергию | ||||||
месяц. год | показания счетчика | тариф | срок оплаты | сумма | пени | всего к оплате |
01.2004 | 0.58 | 25.02.01 | 12.30 | 12.30 | ||
02.2004 | 0.70 | 25.03.01 | ||||
03.2004 | 0.70 | 25.03.01 | ||||
04.2004 | 0.70 | 25.04.01 | ||||
05.2004 | 0.70 | 25.05.01 | ||||
06.2004 | 0.70 | 25.06.01 | ||||
07.2004 | 0.70 | 25.07.01 | ||||
08.2004 | 0.70 | 25.08.01 |
Оплатив эл. энергию за январь месяц 2004 г., абонент не платил за февраль – август месяцы 2004 г. Обратившись в кассу оплаты за эл. энергию в августе месяце с показателями, приведенными в таблице, вычислить сумму к уплате за электроэнергию по месяцам и общую, если пени начисляются в размере 1% за каждый день просрочки.
Построить диаграмму, отражающую потребление эл. энергии.
Вариант 6
Средствами Microsoft Excel составить таблицу:
Таблица А.6 – Ведомость уплаты налога
Ведомость уплаты налога с владельцев транспортных средств | ||||||
марка автомобиля | Фамилия владельца | мощность двигателя | Ставка налога | Сумма | ||
кВт | л.с. | Рубли | Евро | |||
ВАЗ-2121 | Петров А.Е. | |||||
BMW M5 | Чуряк В.В. | |||||
Газ-3110 | Фролов А.Р. | |||||
ВАЗ-2112 | Иванов И.П. | |||||
Ока | Трос Г.В. | |||||
Всего: | человек |
Вычислить:
- мощность двигателя каждого автомобиля в киловаттах;
- ставку налога для каждого автомобиля мощностью до 70 кВт принять 1,6; свыше 70 кВт – 2,2;
- сумму налога в рублях и евро по курсу на текущую дату;
- в строке “Всего:” подсчитать количество фамилий в ведомости и общую сумму в рублях и евро.
Построить диаграмму, отображающую зависимость суммы налога от мощности двигателя автомобиля.
Вариант 7
Средствами Microsoft Excel составить таблицу:
Таблица А.7 – Ведомость сдачи экзаменов
Ведомость сдачи экзаменов на повышение категории | |||||||
Фамилия И.О. | Категория | Результаты тестов | Сумма баллов | Результат | |||
I | II | III | IV | ||||
Иванов А.А. | |||||||
Пронин С.А. | |||||||
Ведерчев К.Р. | |||||||
Медведев А.И. | |||||||
Фролов В.В. | |||||||
Средний балл |
Для каждого тестируемого произвольно определить категорию (числа от 1 до 4). Заполнить столбцы I, II, III и IV произвольными числами, имитирующими результаты тестирования.
Вычислить:
- сумму баллов, набранную каждым тестируемым;
- средний балл, набранный по каждому тесту;
- результат тестирования, который выразится числом – новым номером категории в том случае если сумма баллов по четырем тестам превышает 200, иначе категория не должна быть повышена и в ячейке столбца “Результат” должен остаться прежний номер категории. На ячейки столбца “Результат” наложить форматирование таким образом, чтобы повышенная категория отражалась красным цветом, а не повышенная – синим.
Построить диаграмму, отражающую сумму баллов, набранную каждым тестируемым.
Вариант 8
Средствами Microsoft Excel составить таблицу:
Таблица А.8 – Результаты вступительных испытаний
Результаты вступительных испытаний | |||||
Фамилия И.О | Количество баллов | Общее количество баллов | Результат | ||
Русский язык | Математика | Физика | |||
Иванов Р.А. | |||||
Петров О.Р. | |||||
Сидоров А.И. | |||||
Николаев Б.Г. | |||||
Мостовой К.Л. | |||||
Средний балл |
Вычислить:
- общее количество баллов по каждой фамилии;
- если общее количество баллов больше 40 то в соответствующей ячейке поля “Результат” вывести слово “поступил”, а иначе “не поступил”;
- построить диаграмму, для диапазона “средний балл по предмету”;
- слова “не поступил” выводить зеленым цветом.
Вариант 9
Средствами Microsoft Excel составить таблицу:
Таблица А.9 – Отчет по продажам автомобилей
Годовой отчет по продажам автомобилей. | |||||||
2004 год | Кв. 1 | Кв. 2 | Кв. 3 | Кв. 4 | Годовой итог | Ср. объем продажи в квартале | |
Класс | Объем | ||||||
Легковые: | до 1,5л | ||||||
до 2,4л | |||||||
>2,4л | |||||||
Всего по легковым | |||||||
Грузовые | до 2,5л | ||||||
до 4,5л | |||||||
>4,5л | |||||||
Всего по грузовым: | |||||||
Итого: |
Вычислить:
- объем продаж каждого класса автомобилей поквартально и за год;
- средний объем продаж по каждому виду (ст.8);
- вычислить итог продаж за квартал, год и средний объем продаж;
- построить диаграмму для столбца 7.
Вариант 10
Средствами Microsoft Excel составить таблицу:
Таблица А.10 – Реализация товара
реализация товара | |||||||
№ прейскур. | Наименование | Ед. изм. | Количество | Цена | Сумма | Продано | Результат продажи |
Пальто женское | шт. | ||||||
Юбка женская | шт. | ||||||
Пиджак мужской | шт. | ||||||
Пальто мужское | шт. | ||||||
Шубка детская | шт. | ||||||
Шапка меховая | шт. | ||||||
Пальто осеннее | шт. | ||||||
Кожаный плащ | шт. | ||||||
Среднее значение |
Вычислить:
- в столбце “Сумма” подсчитать стоимость проданных изделий;
- в столбце “Результат продажи” организовать автоматический вывод результата: если продано/количество > 0,8, значит товар “востребован”; иначе – “не востребован”. Слово “востребован” выводить красным.
Подсчитать среднее значение для столбцов цена и сумма.
Составить диаграмму для столбца “Цена”.
Вариант 11
Средствами Microsoft Excel составить таблицу:
Таблица А.11 – Распечатка разговоров
Телесеть Корунд | |||||
Распечатка разговоров абонента 21-77-18 | |||||
Стоимость минуты разговора | 4,15 | ||||
Номер телефона | Статус номера | Время разговора (с) | Статус звонка | Стоимость разговора | |
52-12-36 | городск | исх | |||
20-48-02 | сотовый | исх | |||
58-35-90 | сотовый | вход | |||
36-81-10 | городск | вход | |||
27-15-97 | сотовый | исх | |||
Всего (с) | Всего (руб) | ||||
Вычислить:
- общее время разговоров;
- стоимость каждого разговора и стоимость всех разговоров, если звонки, продолжительностью менее 11 секунд – бесплатны и входящие звонки с сотовых телефонов бесплатны.
Построить круговую диаграмму, отражающую процентное соотношение входящих и исходящих звонков.
Вариант 12
Средствами Microsoft Excel составить таблицу:
Таблица А.12 – График отгрузки цемента
Таблица отгрузки цемента | |||||
Наименование | количество | Ед | Цена за тонну | Сумма | |
М-400 | т | ||||
М-200 | т | ||||
ПЦ-900 | 0,3 | т | |||
М-400 | 0,25 | т | |||
М-400 | 1,2 | т | |||
Всего: | |||||
Заполнить столбцы 2 и 4 произвольными цифрами (по образцу).
Вычислить;
- значения ячеек столбца 5 “Сумма” учитывая, что при приобретении более 1 т. цемента предоставляется скидка – 5%, а более 10 т. – скидка составит 20%;
- среднюю стоимость 1 т. цемента марки М-400.
Построить диаграмму, отражающую количество приобретенного цемента каждой марки.
Вариант 13
Средствами Microsoft Excel составить таблицу:
Таблица А.13 – Анализ продаж
Предварительный анализ продаж | ||||||
Наименование | Ед. изм. | Количество | Цена | Продано | Сумма | Рекомендации |
Колбаса “Озерская” | шт. | |||||
Тушенка “Турист” | шт. | |||||
Суп гороховый | шт. | |||||
Сосиски “Вертел” | шт. | |||||
Сок “Привет” | шт. | 17,5 | ||||
Сырок “Дружба” | шт. | 6,40 | ||||
Макароны “Макфа” | шт. | 11,5 | ||||
Среднее значение |
Заполнить столбец “Продано” произвольными цифрами, согласовав их с цифрами из столбца “Количество”.
Вычислить:
- в столбце “Сумма” вычислить сумму проданного товара;
- в столбце “Рекомендации” организовать автоматический вывод слова “Перспективен” если продано больше половины товара, иначе – “не перспективен”. Слово “перспективен” выводить коричневым цветом;
- среднее значение для столбцов цена и сумма.
Составить диаграмму для столбца “Цена”.
Вариант 14
Средствами Microsoft Excel составить таблицу:
Таблица А.14 – Прайс-лист
Прайс-лист автомагазина | ||||||
Автомобиль | Год выпуска | Цена | Пошлина | Стоимость доставки | Общая стоимость | |
BMW – M5 | ||||||
MB – 124E | ||||||
Audi – 80 | ||||||
Volvo – 17A | ||||||
BMW – Z3 | ||||||
Всего: | ||||||
В столбце “Цена” самостоятельно ввести цену автомобилей.
Вычислить:
- сумму таможенной пошлины, если на автомобили старше семи лет пошлина составляет 200 %;
- пошлина на остальные автомобили составляет 150 %;
- стоимость доставки определяется как 30 % от цены автомобиля с пошлиной, общую стоимость каждого автомобиля и значения строки “Всего”;
Построить диаграмму, отражающую структуру расходов на покупку каждого автомобиля.
Вариант 15
Средствами Microsoft Excel составить таблицу:
Таблица А.15 – Ведомость расчета МП
Ведомость расчета сотрудников МП | Ставка | Премия | 75% | |||
Фамилия И.О. | Категория | Оклад | Премия | Уральский коэфф. | Подоходный налог | К выдаче |
Попов В.А. | ||||||
Васева К.П. | ||||||
Тринова С.С. | ||||||
Катова А.Р. | ||||||
Филатова М.М. | ||||||
Всего: |
Вычислить:
- оклад, определяемый как величина ставки, умноженная на категорию;
- премию, как процент от оклада;
- уральский коэффициент – 15% от “Оклад” + “Премия”;
- подоходный налог – 13% от “Оклад” + “Премия” + “Уральский коэфф.”;
- сумму “К выдаче” - “Оклад” + “Премия” + “Уральский коэфф.” – “Подоходный налог” и значения ячеек строки “Всего”.
Построить диаграмму, отражающую величину суммы, полученную каждым сотрудником.
Сумму, превышающую 10 тысяч, выводить шрифтом красного цвета.
Вариант 16
Средствами Microsoft Excel составить таблицу:
Таблица А.16 – Ведомость расхода тепловой энергии
Ведомость расхода тепловой энергии деревоперерабатывающей фабрикой | ||||||
Месяц | Заявлено (Гкал) | Получено (Гкал) | Потери (Ккал) | Перерасход (Гкал) | Цена (т.р. за Гкал) | Сумма к оплате (руб) |
Октябрь | 16 530 | 14 612 | 12 000 | |||
Ноябрь | 20 400 | 23 890 | 12 200 | |||
Декабрь | 31 700 | 30 400 | 15 850 | |||
Январь | 45 200 | 45 000 | 21 300 | |||
Февраль | 40 000 | 55 700 | 17 100 | |||
Март | 28 100 | 15 300 | 9 800 | |||
Всего: | Итого: |
Вычислить:
- потери тепла составляющие 10% от полученного количества;
- перерасход тепла;
- сумму к оплате, которая увеличивается на 50 % если отклонение от заявленного количества тепла составляет больше 10 000 Гкал и увеличивается на 20 % если отклонение составляет от 5 до 10 тыс. Гкал.
Построить диаграмму, отражающую фактическое потребление тепловой энергии.
Величину перерасхода тепла более чем на 5 000 Гкал выводить шрифтом красного цвета.
Вариант 17
Средствами Microsoft Excel составить таблицу:
Таблица А.17 – Количество осадков
Количество осадков (в мм) | ||||||||
Декабрь | Январь | Февраль | Всего | Максимум | Минимум | Среднее | Характер зимы | |
21,2 | 37,5 | 11,4 | ||||||
22,3 | 34,5 | 51,3 | ||||||
1,2 |
Вычислить значения столбцов “Всего”, “Максимум”, “Минимум”, “Среднее”. В столбце “Характер зимы” вывести слово “обычная” если количество осадков находится в диапазоне между 50 и 100 мм., иначе выводить слова “нет снега”, если количество осадков меньше 50 мм и слово “снежная” если количество осадков больше 100 мм.
Построить диаграмму, отражающую общее количество осадков.
Вариант 18
Средствами Microsoft Excel составить таблицу:
Таблица А.18 – Ведомость основных затрат
Ведомость основных затрат | ||||||||||
Сырье | Затраты на производства хлеба (тыс. руб.) | |||||||||
Наименование | Колич. (т) | Цена за тонну | Транспорт | Энергия | Зарплата | Накладные | Стоим. продукта | Оптовая цена | Налог на добавленную стоимость | |
Мука в.с. | 20 000 | 18738,684 | ||||||||
Мука I с. | 14 000 | 81576,275 | ||||||||
Мука II с. | 8 000 | 18594,936 | ||||||||
Всего: |
Предприятием приобретена мука трех сортов из которой выпечен хлеб. При производстве каждого сорта продукции предприятием были затрачены некоторые средства. Продукт был реализован предприятием по оптовым ценам, указанным в соответствующем столбце и уплачен налог на добавленную стоимость по каждому виду сырья в сумме, указанной в столбце “Налог…” тыс.руб.
Требуется проверить, правильно ли вычислена сумма налога, если транспортные расходы составляют 12%, расходы на энергию – 25%, расходы на зарплату – 8% от общей стоимости сырья. Накладные расходы составляют 1000 рублей на каждую тонну сырья.
Построить диаграмму, отражающую стоимость каждого вида продукта.
Вариант 19
Средствами Microsoft Excel составить таблицу:
Таблица А.19 – Ведомость расхода эл. энергии
Ведомость расхода эл. энергии химической лабораторией | Штраф (руб. за квт) | 2,12 | ||||
Месяц | Заявлено Мвт | Использовано Мвт | Перерасход Мвт | Цена | Сумма штрафа | Сумма к оплате (руб) |
Октябрь | 1 530 | 1 612 | 0,5 | |||
Ноябрь | 2 400 | 2 890 | 0,58 | |||
Декабрь | 2 700 | 2 428 | 0,62 | |||
Январь | 2 200 | 2 045 | 0,7 | |||
Февраль | 2 000 | 2 709 | 0,7 | |||
Март | 2 100 | 2 320 | 0,83 | |||
Всего: | Итого: |
Вычислить:
- величину перерасхода эл. энергии за каждый месяц;
- вычислить сумму штрафа за каждый киловатт эл. энергии израсходованный сверх нормы если перерасход составляет больше 100 мегаватт;
- вычислить сумму к оплате
Построить график, отражающий перерасход эл. энергии по месяцам.
Сумму к оплате, превышающую 2000 руб. выводить шрифтом красного цвета.
Вариант 20
Средствами Microsoft Excel составить таблицу:
Таблица А.20 – Ведомость отлова рыбы
Ведомость отлова рыбы за апрель - август месяцы (кг) | ||||||||
Рыба | апрель | май | июнь | июль | август | Всего: | Цена (руб за кг) | Выручка |
Карп | 0,12 | 0,03 | 0,9 | 12,7 | 33,9 | |||
Щука | 1,3 | 0,01 | 0,3 | 22,4 | 45,2 | |||
Сазан | 8,1 | 1,3 | 1,5 | 45,9 | 62,5 | |||
Форель | 0,01 | 0,1 | 100,3 | 65,1 | ||||
Сом | 12,6 | 10,1 | 15,9 | 15,8 | 14,7 | |||
Всего: | Максимальный улов | |||||||
Минимальный улов |
Вычислить:
- в столбце “Всего” общую величину улова;
- в столбце “Выручка” организовать автоматический вывод слова “максимум” в ячейке той строки, в которой получается наибольшая выручка от продажи улова, а в остальных строках вывести слово “обычное”;
- в строке “Всего” общий улов по месяцам;
- в ячейках максимальный и минимальный вывести число – максимальный и минимальный улов соответственно.
Построить диаграмму, отражающую величину отлова рыба по месяцам.
Приложение В
(обязательное)
Варианты индивидуальных заданий