Министерство образования, науки и молодежной политики Краснодарского края ГБПОУ КК «Армавирский машиностроительный техникум» МЕТОДИЧЕСКАЯ РАЗРАБОТКА Методические указания по выполнению индивидуальных заданий по теме: «ЭЛЕКТРОННЫЕ ТАБЛИЦЫ EXCEL» по дисциплинам: «Информатика» и «Информационные технологии в профессиональной деятельности» для студентов всех специальностей:

РАССМОТРЕН цикловой методической комиссией «Естественнонаучных дисциплин» Протокол № от «» ____ 20__ г. Председатель ЦМК Е.А. Струкова _____________ УТВЕРЖДАЮ Зам. директора по УР _____________ Л.А.Тараненко «____» ____________ 20 ___ г. Автор: преподаватель Струкова Е.А. Рецензенты: преподаватель ГБПОУ КК «Армавирского машиностроительного техникума» Надеждина О.В._____________ преподаватель ГБПОУ КК «Армавирского машиностроительного техникума» Закарина Лариса Эдуардовна _______ 3

Введение Настоящие методические указания по проведению индивидуальных практических занятий по дисциплинам «Информатика» и предназначены для студентов всех специальностей «Информационные технологии в профессиональной деятельности». Знания в области экономической информатики и компьютерных систем, организации и ведения бухгалтерского учета и экономического анализа в компьютерной среде являются необходимыми в профессиональной деятельности бухгалтера, менеджера, товароведа. Работы носят репродуктивный характер, так как при их выполнении студенты пользуются подробными инструкциями, в которых указаны: цель работы, краткие теоретические сведения, порядок выполнения работы, контрольные вопросы, учебная и специальная литература. Практические занятия посвящены углубленному изучению базовых информационных технологий электронных таблиц. Microsoft Excel, используемых в работе специалистами различного профиля (экономистами, финансистами, бухгалтерами… и др.). Электронные таблицы являются идеальной средой для выполнения вычислений различной сложности. В Microsoft Excel включен развитый математический аппарат, специализированные информационные технологии статистического анализа, доступны графические средства представления и анализа данных. Microsoft Excel широко применяется для автоматизации бухгалтерского учета. Документы расчетного характера (разработочные и расчетные таблицы) могут переводиться в электронную форму, сохраняться как шаблоны для многократного использования. Microsoft Excel поддерживает электронный документооборот, обеспечивая создание, описание, поиск и хранение документов сложной структуры, взаимосвязь комплексов расчетных документов.. После проведения практических занятий студенты должны: Знать: ­ основные приемы работы с программой Microsoft Excel. уметь: ­ ­ ­ ­ ­ ­ ­ ­ вводить информацию в электронные таблицы; вводить формулы; редактировать формулы; пользоваться функциями; работать с автофильтром; работать с формой; применять в работе набор встроенных функций Microsoft Excel; решать задачи моделирования и анализа данных; 4

1. Составьте прайс­лист по образцу: Задание 16 Прайс­лист магазина "РОГА И КОПЫТА" 28.09.11 4,6 грн. Курс доллара Цена в у.е. Цена в грн. 0,92 грн. 0,92 грн. 9,20 грн. 2,30 грн. 0,92 грн. 1,38 грн. 1,84 грн. $0,20 $0,20 $2,00 $0,50 $0,20 $0,30 $0,40 Наименование товара Тетрадь в клеточку Тетрадь в линеечку Пенал Ручка Карандаш Линейка Резинка Этапы выполнения задания: 1. Выделите ячейку В1 и введите в нее заголовок таблицы Прайс­лист магазина "РОГА И КОПЫТА" 2. В ячейку С2 введите функцию СЕГОДНЯ (Поставьте знак «=»  Нажмите кнопку на панели инструментов. В поле КАТЕГОРИЯ выберите Дата и Время. В нижнем поле выберите функцию Сегодня). 3. В ячейку В3 введите слова «Курс доллара», в С3 – курс доллара на сегодняшний день. 4. К ячейке С3 примените денежный формат (Формат  Формат ячеек  Вкладка Число  Числовой формат  Денежный  Обозначение можно выбрать произвольное). В ячейки А5:В5 введите заголовки столбцов таблицы. 5. 6. Выделите их и примените полужирный стиль начертания и более крупный шрифт. 7. В ячейки А6:А12 и В6:В12 введите данные. 8. В ячейку С6 введите формулу: = В6*$C$3. ($ означает, что используется абсолютная ссылка). 9. Выделите ячейку С6 и протяните за маркер заполнения вниз до ячейки С13. 10. Выделите диапазон ячеек С6:С13 и примените к ним денежный формат. 11. Выделите заголовок – ячейки В1:С1 и выполните команду Формат Ячейки, вкладка Выравнивание и установите переключатель «Центрировать по выделению» (Горизонтальное выравнивание), «Переносить по словам». Увеличьте шрифт заголовка. 12. В левой части прайс­листа вставьте картинку по своему вкусу. 13. Измените название ЛИСТ1 на Прайс­лист. 5

2. Рассчитайте ведомость выполнения плана товарооборота киоска №5 по форме: Задание 17 Отклонение от плана выполнение, % Vi Oi № Месяц i Mi 1 Январь 2 Февраль 3 Март 4 Апрель 5 Май 6 Июнь 7 Июль 8 Август 9 Сентябрь 10 Октябрь 11 Ноябрь 12 Декабрь Отчетный год фактически Fi 8 500,00 р. 2 700,00 р. 7 800,00 р. 4 590,00 р. 7 650,00 р. 5 670,00 р. 5 430,00 р. 8 700,00 р. 6 500,00 р. 6 570,00 р. 6 520,00 р. 10 000,00 р. план Pi 7 800,00 р. 3 560,00 р. 8 900,00 р. 5 460,00 р. 6 570,00 р. 6 540,00 р. 4 900,00 р. 7 890,00 р. 6 540,00 р. 6 540,00 р. 6 540,00 р. 8 900,00 р. 1. Заполнение столбца Mi можно выполнить протяжкой маркера. 2. Значения столбцов Vi и Oi вычисляются по формулам: Vi=Fi / Pi; Oi=Fi – Pi 3. Переименуйте ЛИСТ2 в Ведомость. 4. Сохраните таблицу в своей папке под именем Практическая работа 1 Задание № 18 1. Перейти на новый рабочий лист и назвать его Возраст. 2. Создать список из 10 фамилий и инициалов. 3. Внести его в таблицу с помощью автозаполнения. 4. Занести в таблицу даты рождения. 5. В столбце Возраст вычислить возраст этих людей с помощью функций СЕГОДНЯ и ГОД 6. Отформатировать таблицу. 7. Сделать заголовок к таблице «Вычисление возраста» ФИО Дата рождения Возраст № 1 2 3 … 10 Иванов И.И. Петров П.П. Сидоров С.С. Мышкин М.М. 6

Задание № 19 Ячейки, в которых выполнена заливка серым цветом, должны содержать формулы! 1. Создать и отформатировать таблицу по образцу (Фамилии ввести из списка с помощью автозаполнения) Вычислить стаж работы сотрудников фирмы по формуле: 2. =ГОД(СЕГОДНЯ()­Дата приема на работу)­1900 (Полученный результат может не совпадать со значениями в задании. Почему?) 3. Переименовать Лист1 в Сведения о стаже сотрудников Сведения о стаже сотрудников фирмы "Рога и копыта" О И Ф Иванов И.И. Петров П.П. Сидоров С.С. Кошкин К.К. Мышкин М.М. Мошкин М.М. Собакин С.С. Лосев Л.Л. Гусев Г.Г. Волков В.В. ь т с о н ж л о Д Директор Водитель Инженер Гл. бух. Охранник Инженер Техник Психолог Техник Снабженец а м е и р п у а т т о а б Д а р а н 01 января 2003 г. 02 февраля 2002 г. 03 июня 2001 г. 05 сентября 2006 г. 01 августа 2008 г. 04 декабря 2005 г. 06 ноября 2007 г. 14 апреля 2005 г. 25 июля 2004 г. 02 мая 2001 г. ж а т С 5 6 7 1 0 2 0 3 4 7 Задание № 20 Работа с функцией ЕСЛИ 1. Скопировать таблицу из задания № 1 на Лист2 и переименовать его в Тарифные ставки 2. Изменить заголовок таблицы 3. Добавить столбец Тарифные ставки и вычислить их таким образом: 1­ если стаж меньше 5 лет, 2­ если стаж больше или равен 5 лет Тарифные ставки сотрудников фирмы "Рога и копыта" О И Ф Иванов И.И. Петров П.П. Сидоров С.С. Кошкин К.К. Мышкин М.М. Мошкин М.М. ь т с о н ж л о Д Директор Водитель Инженер Гл. бух. Охранник Инженер а м е и р п у а т т о а б Д а р а н 01 января 2003 г. 02 февраля 2002 г. 03 июня 2001 г. 05 сентября 2006 г. 01 августа 2008 г. 04 декабря 2005 г. е ы н ф и р а Т и к в а т с 2 2 2 1 1 1 ж а т С 5 6 7 1 0 2 7

Собакин С.С. Лосев Л.Л. Гусев Г.Г. Волков В.В. Техник Психолог Техник Снабженец 06 ноября 2007 г. 14 апреля 2005 г. 25 июля 2004 г. 02 мая 2001 г. 0 3 4 7 1 1 1 2 8

Работа с вложенными функциями ЕСЛИ Задание № 21 1. Скопировать таблицу из задания № 2 на Лист3 и переименовать его в Налоги. 2. Изменить заголовок таблицы. 3. Добавить столбцы Ставка, Начислено, Налог, Заработная плата и заполнить их таким образом: Ставка = произвольное число от 500 до … Начислено = Ставка * Тарифные ставки Налог = 0, если Начислено меньше 1000, 12%, если Начислено больше 1000, но меньше 3000, и 20%, если Начислено больше или равно 3000 4. Сохранить документ в своей папке. 5. Показать работу учителю. Заработная плата сотрудников фирмы "Рога и копыта" О И Ф ь т с о н ж л о Д Директор Иванов И.И. Петров П.П. Водитель Сидоров С.С. Инженер Кошкин К.К. Гл. бух. Мышкин М.М. Охранник Мошкин М.М. Инженер Собакин С.С. Лосев Л.Л. Гусев Г.Г. Волков В.В. Техник Психолог Техник Снабженец а н а м е и р п а т а Д у т о б а р 01 января 2003 г. 02 февраля 2002 г. 03 июня 2001 г. 05 сентября 2006 г. 01 августа 2008 г. 04 декабря 2005 г. 06 ноября 2007 г. 14 апреля 2005 г. 25 июля 2004 г. 02 мая 2001 г. е ы н ф а и к р в а а Т т и С к в а т с 2 2 2 1 1 1 1 1 1 2 5000 1000 3000 4000 3000 4000 2000 3000 500 3500 о н е л с и ч а Н г о л а Н 10000 2000 2000 240 6000 1200 800 4000 3000 360 800 4000 240 2000 3000 360 0 500 7000 1400 я а н т о б а р а З а т а л п 8000 1760 4800 3200 2640 3200 1760 2640 500 5600 ж а т С 5 6 7 1 0 2 0 3 4 7 Задание № 22 Расчет стоимости проданного товара Цена в рублях Количество Стоимость Цена в дол. $4,00 $5,00 $2,00 $1,70 $0,40 Товар Шампунь Набор для душа Дезодорант Зубная паста Мыло Курс доллара. Стоимость покупки 9

Задание № 23 1.. Переименовать лис вв Успеваемость. 2.Ввести информацию в таблицу. Успеваемость ФИО Математика Информатик а Физика Среднее Иванов И.И. Петров П.П. Сидоров С.С. Кошкин К.К. Мышкин М.М. Мошкин М.М. Собакин С.С. Лосев Л.Л. Гусев Г.Г. Волков В.В. Среднее по предмету 3.Вычислить средние значения по успеваемости каждого ученика и по предметам. 4.Построить гистограмму по успеваемости по предметам. 5.Построить пирамидальную диаграмму по средней успеваемости каждого ученика 6.Построить круговую диаграмму по средней успеваемости по предметам. Добавить в этой диаграмму процентные доли в подписи данных. 7.Красиво оформить все диаграммы. 10

Контрольная работа по теме: «Электронные таблицы. Ввод, редактирование и форматирование данных. Стандартные функции». Задание № 1 Создать таблицу и отформатировать ее по образцу. Содержание столбца «Кто больше» заполнить с помощью функции ЕСЛИ. Количество спортсменов среди учащейся молодежи. а н а р т С Италия Россия Дания Украина Швеция Польша Минимум Максимум и к ш у в е Д 37% 25% 32% 18% 33% 23% 18% 37% и ш о н Ю 36% 30% 24% 21% 28% 34% 21% 36% о т К е ш ь л о б Девушки Юноши Девушки Юноши Девушки Юноши Задание № 2 Создать таблицу и отформатировать ее по образцу.  Столбец «Количество дней проживания» вычисляется с помощью функции ДЕНЬ и значений в столбцах «Дата прибытия» и «Дата убытия»  Столбец «Стоимость» вычисляется по условию: от 1 до 10 суток – 100% стоимости, от 11 до 20 суток –80% стоимости, а более 20 – 60% общей стоимости номера за это количество дней. Ведомость регистрации проживающих в гостинице «Рога и копыта». О И Ф Иванов И.И. Петров П.П. Сидоров С.С. Кошкин К.К. Мышкин М.М. р е м о Н 1 2 4 8 13 и к т у с в а р е м о н ь т с о м и о т С 10 грн 20 грн 30 грн 40 грн 100 грн й е н д о в т с е ч и л о К я и н а в и ж о р п ь т с о м и о т С я и т ы б у а т а Д я и т ы б и р п а т а Д 2.10.2004 2.09.2004 10.09.2004 3.09.2004 25.09.2004 1.09.2004 30.09.2004 3.10.2004 25.09.2004 20.10.2004 Общая стоимость 11

Задание № 3 Для заполнения таблицы используются формулы и абсолютные ссылки. Составить таблицу умножения Таблица умножения 1 2 3 … 9 1 1 2 3 9 2 2 4 6 3 3 6 9 18 27 4 4 8 12 36 5 5 10 15 45 6 6 12 18 54 7 7 14 21 63 8 8 16 24 72 9 9 18 27 81 12

Задание № 1 Создать таблицу и отформатировать ее по образцу. Вычисления в столбце Отчетный год в % к предыдущему выполняются по формуле: Отчетный год, тонн / Предшествующий год, тонн, А в столбце Выполнение поставок с помощью функции ЕСЛИ(больше или равно 100% – выполнено, иначе – нет) Выполнение договора поставки овощей и фруктов для нужд детских учреждений Соломенского района я и ц к у д о р П Огурцы Яблоки Сливы Морковь Лук Всего, н н о т ю у в т с е д ш о г д й е р и П щ 9,7 13,4 5,7 15,6 20,5 64,9 й ы н т е ч т О н н о т, д о г 10,2 15,3 2,8 14,6 21 63,9 у м е щ у д ы д е р п к % в д о г й ы н т е ч т О 105,15 114,18 49,12 93,59 102,44 98,46 е и н е н л о п ы В к о в а т с о п Выполнено Выполнено Не выполнено Не выполнено Выполнено Не выполнено Задание № 2 Создать таблицу расчета оптимального веса и отформатировать ее по образцу.  Столбец «Оптимальный вес» вычисляется по формуле: Оптимальный вес = Рост­ 100  Если вес человека оптимальный, то в столбце «Советы» напротив его фамилии должна появиться запись «Оптимальный вес». Если вес меньше оптимального – «Вам надо поправиться на», с указанием в соседней ячейке количества недостающих килограмм. Если вес больше оптимального – «Вам надо похудеть на» с указанием в соседней ячейке количества лишних килограмм. Сколько мы весим? ФИО Вес, кг Рост, см Оптимальный вес, кг Советы Разница веса, кг Иванов И.И. Петров П.П. Сидоров С.С. Кошкин К.К. Мышкин М.М. 65 55 64 70 78 160 155 164 170 180 60 55 64 70 80 Вам надо похудеть на Оптимальный вес Оптимальный вес Оптимальный вес Вам надо поправиться на 5 2 13

Для заполнения таблицы используются формулы и абсолютные ссылки. 2 2 4 6 3 3 6 9 1 2 3 … 9 1 1 2 3 9 18 27 36 45 54 Задание № 3 Составить таблицу умножения Таблица умножения 6 4 4 6 12 8 12 18 5 5 10 15 7 7 14 21 63 8 8 16 24 72 9 9 18 27 81 Комплексная практическая работа по теме: «Создание таблиц в EXCEL». Вариант № 1 В папке МОИ ДОКУМЕНТЫ создать папку КР EXCEL и сохранить в ней все таблицы. Значения в затененных ячейках вычисляются по формулам! Задание 1. 1. Создать таблицу по образцу. Выполнить необходимые вычисления. 2. Отформатировать таблицу. 3. Построить сравнительную диаграмму (гистограмму) по уровням продаж разных товаров в регионах и круговую диаграмму по среднему количеству товаров. Продажа товаров для зимних видов спорта. Регион Лыжи Коньки Киев Житомир Харьков Днепропетровск Одесса Симферополь Среднее Задание 2 3000 200 400 500 30 40 7000 600 400 3000 1000 500 Всего Санки 200 700 500 400 300 266 1. Создать таблицу по образцу. Выполнить необходимые вычисления. Всего затрат =Общий пробег * Норма затрат 2. Отформатировать таблицу. 3. Построить круговую диаграмму «Общий пробег автомобилей» с указанием процентных долей каждого и столбиковую диаграмму «Затраты на ремонт автомобилей». 4. С помощью средства Фильтр определить марки автомобилей, пробег которых превышает 40000 км и марки автомобилей, у которых затраты на техническое обслуживание превышают среднее. “Учет затрат на техническое обслуживание и текущий ремонт автомобилей” 14

№ Марка автомобиля Общий пробег тыс. км Норма затрат на 1 000 км, грн. Всего затрат, тыс. грн. 1. Жигули 2 Москвич 3 Мерседес 4 Опель Среднее 12 50 25 45 2000 1800 3000 2500 15

Задание 3 1. Создать таблицу и отформатировать ее по образцу. 2. Данные в столбце Возраст вычисляются с помощью функций СЕГОДНЯ и ГОД 3. Отсортировать данные в таблице по возрасту. 4. Построить сравнительную гистограмму по возрасту и в качестве подписей на оси Х использовать должности сотрудников. 5. С помощью фильтра вывести сведения только о военнообязанных сотрудниках (Пол ­м, возраст от 18 до 45 лет). Сведения о сотрудниках фирмы "Рога и копыта" ФИО Арнольдов Тарас Бульбович Голубков Леня Мавродиевич Барабуля Сэм Джонович Симеоненко Жорж Жорикович Рыбак Карп Карпович Графченко Дракул Дракулович Кара­Мурза Лев Филиппович Сидоров Петр Иванович Прекрасная Василиса Ивановна Поппинс Мэри Джоновна Должность Дата рожд. Пол Директор м м Водитель м Снабженец Гл. бух. м м Инженер м Менеджер Охранник м м Техник ж Секретарь Психолог ж 01.12.45 20.09.78 05.08.68 04.11.84 05.05.55 03.06.68 04.03.79 20.10.85 30.05.80 04.07.68 Возраст Комплексная практическая работа

Практическая работа №1

Задание

Составьте прайс-лист по образцу:

	Прайс-лист магазина "РОГА И КОПЫТА"
		10.01.13
	Курс доллара
Наименование товара	Цена в у.е.	Цена в руб.
Тетрадь в клеточку
Тетрадь в линеечку
Карандаш

Этапы выполнения задания:

Выделите ячейку В1 и введите в нее заголовок таблицы Прайс-лист магазина "РОГА И КОПЫТА"

В ячейку С2 введите функцию СЕГОДНЯ (Поставьте знак =, Нажмите кнопку f x > на панели инструментов. В поле КАТЕГОРИЯ выберите Дата и Время . В нижнем поле выберите функцию Сегодня ).

В ячейку В3 введите слова «Курс доллара », в С3 – курс доллара на сегодняшний день.

К ячейке С3 примените денежный формат (Формат  Формат яче ек  Вкладка Число  Числовой формат  Денежный  Обозначение можно выбрать произвольное).

В ячейки А5:С5 введите заголовки столбцов таблицы.

Выделите их и примените полужирный стиль начертания и более крупный шрифт.

В ячейки А6:А12 (можно скопировать) и В6:В12 (копировать нельзя – знак доллара нужнно сделать через формат ячеек) введите данные.

В ячейку С6 введите формулу: = В6*$ C $3 . ($ означает, что используется абсолютная ссылка).

Выделите ячейку С6 и протяните за маркер заполнения вниз до ячейки С13 .

Выделите диапазон ячеек С6:С13 и примените к ним денежный формат (Меню «Формат» как описано выше или правая кнопка мыши на ячейках, выбрать «формат ячеек» и как описано в п. 4).

Выделите заголовок – ячейки В1:С1 и выполните команду Формат Ячейки , вкладка Выравнивание и установите переключатель «Центрировать по выделению » (Горизонтальное выравнивание), «Переносить по словам ». Увеличьте шрифт заголовка.

В левой части прайс-листа вставьте картинку по своему вкусу.

Измените название ЛИСТ1 на Прайс-лист .

Рассчитайте ведомость выполнения плана товарооборота киоска №5 по форме:

	Месяц	Отчетный год			Отклонение от плана
		план	фактически	выполнение, %
	Сентябрь

Заполнение столбца Mi можно выполнить протяжкой маркера.

Значения столбцов Vi и Oi вычисляются по формулам: Vi=Fi / Pi; Oi=Fi – Pi

Переименуйте ЛИСТ 2 в Ведомость .

Сохраните таблицу в своей папке под именем Практическая работа 1

Покажите работу учителю.

	Месяц	Отчетный год			Отклонение от плана
		план	фактически	выполнение, %
	Сентябрь

Заполнение столбца Mi можно выполнить протяжкой маркера.

Значения столбцов Vi и Oi вычисляются по формулам: Vi=Fi / Pi; Oi=Fi – Pi

Переименуйте ЛИСТ 2 в Ведомость .

Сохраните таблицу в своей папке под именем Практическая работа 1

Покажите работу учителю.

Практическая работа № 2

Тема: Использование встроенных функций и операций ЭТ

Цель : получить практические навыки работы в программе Ms Excel,

вводить и редактировать стандартные функции ЭТ

Ход работы:

Задание № 1

Протабулировать функцию

на промежутке с шагом 0,2.

Вычисления оформить в виде таблицы, отформатировать ее с помощью автоформата и сделать заголовок к таблице.

Рабочий лист назвать Функция .

Задание № 2

Перейти на новый рабочий лист и назвать его Возраст .

Создать список из 10 фамилий и инициалов.

Внести его в таблицу с помощью автозаполнения.

Занести в таблицу даты рождения.

В столбце Возраст вычислить возраст этих людей с помощью функций СЕГОДНЯ и ГОД

Отформатировать таблицу.

Сделать заголовок к таблице «Вычисление возраста »

		Дата рождения	Возраст
	Иванов И.И.
	Петров П.П.
	Сидоров С.С.
	Мышкин М.М.

Задание № 3

Откройте файл с Практической работой 1 , перейдите на лист Ведомость .

В эту таблицу добавьте снизу ячейки по образцу и выполните соответствующие вычисления. (Используйте статистические функции МАКС и СРЗНАЧ )

	Месяц	Отчетный год			Отклонение от плана
		план, р.	фактически, р.	выполнение, %
	Сентябрь
			Максимум
			Среднее

Покажите работу учителю.

Практическая работа № 3

Тема: Использование логических функций

Задание № 1

Работа с функциями Год и Сегодня

Ячейки, в которых выполнена заливка серым цветом, должны содержать формулы!

Создать и отформатировать таблицу по образцу (Фамилии ввести из списка с помощью автозаполнения)

Вычислить стаж работы сотрудников фирмы по формуле:

=ГОД(СЕГОДНЯ()-Дата приема на работу)-1900

(Полученный результат может не совпадать со значениями в задании. Почему?)

Переименовать Лист1 в Сведения о стаже сотрудников

Сведения о стаже сотрудников фирмы "Рога и копыта"
	Должность	Дата приема на работу	Стаж
Иванов И.И.	Директор
Петров П.П.	Водитель
Сидоров С.С.
Кошкин К.К.
Мышкин М.М.	Охранник
Мошкин М.М.
Собакин С.С.
Лосев Л.Л.	Психолог
Гусев Г.Г.
Волков В.В.	Снабженец

Задание № 2

Работа с функцией ЕСЛИ

1. Скопировать таблицу из задания № 1 на Лист2 и переименовать его в Тарифные ставки

2. Изменить заголовок таблицы

3. Добавить столбец Тарифные ставки и вычислить их таким образом:

1- если стаж меньше 5 лет, 2- если стаж больше или равен 5 лет

Книга 2

учитель информатики высшей категории,

старший учитель, г. Киев

Практические и

© Copyright

Проект «Уроки информатики»

http:// uchinfo. com. ua

Стр.

Практическая работа № 1 Создание и редактирование электронных таблиц, ввод формул в таблицу, сохранение таблицы на диске………………………………………. Практическая работа № 2 Использование встроенных функций и операций ЭТ………... Практическая работа № 3 Использование логических функций …………………………. Практическая работа № 4 Построение диаграмм и графиков…………………………….. Практическая работа № 5 Сортировка и фильтрация данных…………………………… Контрольная работа по теме: «Электронные таблицы. Ввод, редактирование и форматирование данных. Стандартные функции». Вариант 1……………………………………………………………………………………… Вариант 2……………………………………………………………………………………… Вариант 3……………………………………………………………………………………… Вариант 4………………………………………………………………………………………

Практическая работа №1

Тема: Создание и редактирование электронных таблиц, ввод формул в таблицу, сохранение таблицы на диске.

Цель: Получить практические навыки создания и редактирования электронных таблиц, ввода формул в таблицу, сохранения таблицы на диске.

Ход работы:

1. Составьте прайс-лист по образцу:

	Прайс-лист магазина "РОГА И КОПЫТА"
		28.09.11
	Курс доллара	4,6 грн.
Наименование товара	Цена в у. е.	Цена в грн.
Тетрадь в клеточку	$0,20	0,92 грн.
Тетрадь в линеечку	$0,20	0,92 грн.
Пенал	$2,00	9,20 грн.
Ручка	$0,50	2,30 грн.
Карандаш	$0,20	0,92 грн.
Линейка	$0,30	1,38 грн.
Резинка	$0,40	1,84 грн.

Этапы выполнения задания:

1. Выделите ячейку В1 и введите в нее заголовок таблицы Прайс-лист магазина "РОГА И КОПЫТА"

2. В ячейку С2 введите функцию СЕГОДНЯ (Поставьте знак «=» ð ê Нажмите кнопку < f x > на панели инструментов. В поле КАТЕГОРИЯ выберите Дата и Время . В нижнем поле выберите функцию Сегодня ).

3. В ячейку В3 введите слова «Курс доллара », в С3 – курс доллара на сегодняшний день.

4. К ячейке С3 примените денежный формат (Формат ð Формат яче ек ð Вкладка Число ð Числовой формат ð Денежный ð Обозначение можно выбрать произвольное).

5. В ячейки А5:В5 введите заголовки столбцов таблицы.

6. Выделите их и примените полужирный стиль начертания и более крупный шрифт.

7. В ячейки А6:А12 и В6:В12 введите данные.

8. В ячейку С6 введите формулу: = В6*$ C $3 . ($ означает, что используется абсолютная ссылка).

9. Выделите ячейку С6 и протяните за маркер заполнения вниз до ячейки С13 .

10. Выделите диапазон ячеек С6:С13 и примените к ним денежный формат.

11. Выделите заголовок – ячейки В1:С1 и выполните команду Формат Ячейки , вкладка Выравнивание и установите переключатель «Центрировать по выделению » (Горизонтальное выравнивание), «Переносить по словам ». Увеличьте шрифт заголовка.

12. В левой части прайс-листа вставьте картинку по своему вкусу.

13. Измените название ЛИСТ1 на Прайс-лист .

2. Рассчитайте ведомость выполнения плана товарооборота киоска №5 по форме:

№	Месяц	Отчетный год	Отклонение от плана
		план	фактически	выполнение, %
	Январь	7 800,00 р.	8 500,00 р.
	Февраль	3 560,00 р.	2 700,00 р.
	Март	8 900,00 р.	7 800,00 р.
	Апрель	5 460,00 р.	4 590,00 р.
	Май	6 570,00 р.	7 650,00 р.
	Июнь	6 540,00 р.	5 670,00 р.
	Июль

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

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

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

Выбор и рассмотрение факторов необходимо осуществлять на основе анализа их надежности и достоверности. Не следует учитывать при планировании факторы, не основанные на точных данных и не имеющие надежного доказательства их достоверности. Такой подход позволяет следить за возникающими возможностями и адаптироваться к изменениям, происходящим на рынке. План товарооборота должен быть надежным инструментом управления на предприятии и основываться на достоверной информации. План продажи должен быть реально осуществимым. Это - управленческий инструмент, основа для планирования и разработки товарной политики предприятия, основных показателей хозяйственной деятельности.

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

На практике целесообразно использовать оба эти подхода, поскольку планирование является текущим и непрерывным процессом, в котором необходимо постоянно осуществлять координацию планов и их корректировку в процессе реализации.

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

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

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

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

Экономико-статистические методы - это наиболее распространенные методы планирования товарооборота. Их применение зависит от определенных условий.

Если предприятие располагает только рядом статистических данных, характеризующих изменение товарооборота во времени, то плановые показатели можно рассчитать путем экстраполяции. Экстраполяция осуществляется различными приемами. Выбор метода экстраполяции зависит от особенностей динамического ряда.

Плановый показатель рассчитывается по формуле :

Оп = Оn-1 * Тс,

Оп - плановый товарооборот;

О n-1 - товарооборот последнего периода в ряду динамики;

Тс - средний темп роста товарооборота.

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

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

К экономико-статистическим методам планирования общегоплановому.

Полученный выровненный ряд скользящих средних позволяет определить среднее изменение прироста товарооборота:

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

Планируемый объем товарооборота определяется так:

где t?О - среднегодовой прирост товарооборота, тыс. руб.:

t?О = ? 1 +? 2 + …+? k,

О n - товарооборот n-го года;

О 1 - товарооборот первого года;

N - номер товарооборота n-го года.

Если целью планирования товарооборота является повышение эффективности использования ресурсов, то планирование основывается на сравнении показателей эффективности использования ресурсов на данном предприятии и аналогичных предприятиях района, деятельности или показателей конкурентов.

Расчеты проводятся в определенной последовательности.

На первом этапе определяется соотношение (уровень) использования отдельных видов ресурсов на аналогичных, но лучших предприятиях по формуле:

Упр - уровень превышения эффективности использования соответствующего вида ресурса, %;

Эл - показатель эффективности использования соответствующего вида ресурса (фондоотдачи, производительности труда, товарооборота на 1м 2 площади торгового зала и т.п.), достигнутый на торговом предприятии, избранном в качестве эталона;

Эд - показатель эффективности использования соответствующего вида ресурса, достигнутый на данном предприятии в предплановом периоде.

На втором этапе определяется приоритетный вид ресурса, по которому на предприятии намечается достичь наибольшей эффективности использования.

Этот выбор осуществляется с учетом результатов сравнительного анализа эффективности использования отдельных ресурсов и приоритетного значения отдельных из них для развития данного предприятия.

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

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

В реальной хозяйственной деятельности часто возникают ситуации, когда важно знать, какое влияние на прибыль окажет изменение объема реализации и какой объем продаж может покрыть затраты на покупку и реализацию. (Объем реализации - один из важнейших факторов изменения издержек обращения, прибыли.)

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

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

Об - объем реализации товаров, обеспечивающий достижение «точки безубыточности»;

Увд - уровень валового дохода от реализации, %;

Ипост - сумма постоянных издержек обращения;

УИпер - уровень переменных издержек обращения к товарообороту, %.

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

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

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

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

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

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

Следующий этап планирования - расчет товарооборота по кварталам и месяцам.

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

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

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

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

При планировании могут использоваться экономико-статистические, расчетно-аналитические, экономико-математические методы.