МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ
РОССИЙСКОЙ ФЕДЕРАЦИИ
федеральное государственное бюджетное образовательное учреждение
высшего профессионального образования
«Алтайский государственный университет»
Рубцовский институт (филиал)
УТВЕРЖДАЮ
Директор Рубцовского института (филиала) АлтГУ
_____________ К. Г. Анисимов
М. П.
«_____» ______________ 2015 г.
Программа дополнительного профессионального образования
«Информационные технологии. Microsoft Excel»
Рубцовск
2015
Программа рассмотрена и одобрена на заседании Ученого совета Рубцовского института (филиала) АлтГУ от «___» ___________20__г., протокол № ________
Программа рассмотрена и одобрена на заседании методической комиссии Рубцовского института (филиала) АлтГУ от «08» октября 2015 г., протокол № 3.
Руководитель центра:
Ст. преподаватель | _____________________ (подпись) | О. В.Рязанова |
Разработчик:
Ст. преподаватель | _____________________ (подпись) | О. В.Рязанова |
Председатель методической комиссии института:
Зам. директора
по учебной работе ________________________________________ Е. А. Жданова
Содержание
1. Общая характеристика программы.. 4
1.1. Цель реализации программы. 4
1.2. Планируемые результаты обучения. 4
1.3 Категория слушателей. 5
1.4 Трудоемкость обучения. 5
1.5 Форма обучения. 5
2. Содержание программы.. 6
2.1. Учебно-тематический план. 6
3. Условия реализации программы (организационно-педагогической) 6
3.1. Материально-технические условия. 6
3.2. Учебно-методическое и информационное обеспечение программы. 6
4. Оценка качества освоения программы(форма аттестации, оценочные и методические материалы) 6
5. Кадровые условия (составители программы) 6
1. Общая характеристика программы
1.1. Цель реализации программы
В большинстве случаев комплексное решение любых экономических задач/расчетов/анализов дает приложение Microsoft Excel.
MS Excel – является наиболее популярным вариантом электронных таблицам сегодня и представляет собой инструментальное средство высокого уровня, позволяющее решать далеко не тривиальные задачи, понятными и доступными методами, обеспечивающими автоматизацию самых разных аспектов экономики: бухгалтерия, финансовый учет и анализ, подготовка документов в различные инстанции, планирование и оценки деятельности предприятия и многое другое.
Целью реализации программы является формирование у слушателей компетенций, необходимых для выполнения широкого спектра задач при работе с электронными таблицами в современном офисе: вычисления, обработка, анализ, предоставление данных, решение задач оптимизации. Знание программы позволит слушателям своевременно получать нужные данные и уметь их предоставлять в наглядном виде, что крайне важно. Такой курс необходим для менеджеров и офисных работников всех уровней, руководителей, секретарей, помощников руководителей, бухгалтеров, экономистов, аналитиков, логистов и многих других специалистов.
1.2. Планируемые результаты обучения
В результате ознакомления с программой Excel слушатель должен не только научиться выполнять избранные операции в программном приложении, но и овладеть умением самостоятельно отыскать и освоить незнакомую операцию, которая ему потребуется.
По окончании изучения разделов программы слушатели должны:
Знать:
- назначение и возможности электронных таблиц;
- структуру книги Excel;
- правильно вводить данные, использовать заполнение ячеек списками и данных прогрессий;
- инструменты для работы с данными: получение внешних данных, сортировка, фильтрация, подведение итогов, сводные таблицы;
- понятие целевая функция, ограничения, переменные при составлении математических моделей линейных задач.
Уметь:
- осуществлять вычисления по формулам с использованием данных, находящихся как на одном, так и на разных листах;
- использовать все категории функций для экономических расчетов и получать результаты расчета данных по функциям;
- оформлять таблицы в соответствии с любыми требованиями;
- строить диаграммы и графики для наглядного представления табличных данных;
- осуществлять сортировку и фильтрацию данных, подведение итогов и сводные отчеты в базах данных, организованных на основе списков в MS Ecxel;
- подбирать вид графического отображения экономической информации в зависимости от ее характера;
- применять графические методы MS Excel для принятия экономически обоснованных решений;
- решать экономические задачи оптимизации с помощью MS Excel;
- грамотно трактовать полученный с помощью MS Excel результат.
Владеть:
- навыками использования MS Excel для работы с экономической информацией;
- навыками проведения основных операций с базами данных в MS Excel;
- приемами применения электронных таблиц для построения графиков;
- инструментами анализа «что если»: подбор параметров, диспетчер сценариев, поиск решения;
- навыками решения оптимизационных задач экономики с помощью MS Excel.
1.3 Категория слушателей
К освоению курса допускаются лица, имеющие навыки работы на ПК.
1.4 Трудоемкость обучения
Курс продолжительностью 16 часов, срок обучения – 4 недели, режим занятий – 4 часа в неделю.
1.5 Форма обучения
Очная, возможна реализация программы частично по индивидуальной траектории обучения.
2. Содержание программы
2.1. Учебно-тематический план
№ п/п | Наименование разделов и тем | Общая трудоемкость (часов, зачетных единиц) | Всего аудиторных часов (зачетных единиц) | Количество аудиторных часов | Самостоятельная работа слушателей, час. | ||
Лекции | Практические (семинарские) занятия | Лабораторные работы | |||||
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
Тема 1. Работа с листами книги. Ввод данных. | 2 | 2 | 2 | ||||
Тема 2. Вычисления в Microsoft Excel | 2 | 2 | 2 | ||||
Тема 3. Графическое представление данных | 2 | 2 | 2 | ||||
Тема 4. Работа с данными. | 4 | 4 | 4 | ||||
Тема 5. Инструмент для поиска решения уравнений и задач оптимизации | 4 | 4 | 4 | ||||
Тема 6. Решение практических задач | 2 | 2 | 2 | ||||
ИТОГО | 16 | 16 | 16 |
2.2 Содержание разделов учебного курса
Тема 1. Работа с листами книги. Ввод данных
Интерфейс Microsoft Office Excel 2010: лента, вкладки, группы, значки.
Вкладка Файл, панель Быстрого доступа.
Структура книги. Операции с листами: переименование, вставка, удаление, перемещение, копирование. Основные действия с элементами листа: ячейками, строками, столбцами.
Особенности ввода данных. Редактирование. Установка форматов данных. Очистка содержимого и форматов.
Автоматическое заполнение ячеек листа данными:
Прогрессии: арифметическая и геометрическая.
Списки: встроенные и пользовательские.
Оформление ячеек: формат числа, границы, заливка, выравнивание данных. Автоформаты таблиц. Условное форматирование
Добавление примечаний к ячейкам. Изменение, копирование, просмотр и отображение примечаний
Копирование форматов. Очистка форматирования.
Тема 2. Вычисления в Microsoft Excel
Создание и редактирование формул. Копирование формул.
Использование разных видов ссылок в расчетах: относительные ссылки, абсолютные ссылки. Ссылки на ячейки других листов.
Использование именованных ячеек в формулах. Панель инструментов Зависимости.
Встроенные функции Excel. Мастер Функций. Использование функций для математических, финансовых, статистических расчетов.
Ошибки в формулах: причины возникновения и действия по исправлению.
Тема 3. Графическое представление данных.
Работа с диаграммами:
Построение: рекомендуемые диаграммы, комбинированные диаграммы.
Настройка диаграммы: элементы диаграммы, стили диаграмм, фильтрация рядов и категорий.
Редактирование и удаление диаграммы.
Тема 4. Работа с данными.
Получение внешних данных, сортировка, фильтрация, подведение итогов, сводные таблицы. Анализ «что если»: подбор параметров, диспетчер сценариев.
Тема 5. Инструмент для поиска решения уравнений и задач оптимизации
Понятие «оптимизации прибыли». Задачи нахождения максимума. Необходимое условие максимизации прибыли. Проведение многовариантных расчетов при моделировании экономических ситуаций с условием максимизации прибыли на конкретном предприятии.
Инструмент «Поиск решения». Задача линейной оптимизации в Excel: поставка сырья; оптимальный выпуск продукции; оптимальное управление запасами; оптимальное распределение ресурсов; планирования инвестиций; оптимальный рацион (смесь, сплав); назначение на должность; оптимальная замена оборудования и т. д
Тема 6. Решение практических задач
Составление штатного расписания. План выгодного производства.
3. Условия реализации программы (организационно-педагогической)
3.1. Материально-технические условия (аудитории, лаборатории, классы, перечень средств обучения, включая стенды, тренажеры, модели, макеты, оборудование, в т. ч. компьютерные и телекоммуникационные и т. п.)
Реализация программы требует наличия компьютерного класса на 15 посадочных мест обучающихся, компьютерные столы, стулья для обучающихся и преподавателя, доска, 15 ноутбуков).
Технические средства обучения: видеопроектор; компьютеры; программное обеспечение общего и специального назначения:
3.2. Учебно-методическое и информационное обеспечение программы (учебно-методические материалы (учебники, учебные пособия, периодические издания, раздаточный материал и т. д.)
Основная литература:
1. И., Ф. Основы современной информатики / Ю. И. Кудинов, Ф. Ф. Пащенко – 2-е мзд., испр. - Лань, 2011 – 256с.
2. И., Ф., Ю. Практикум по основам современной информатики / Ю. И. Кудинов, Ф. Ф. Пащенко, А. Ю. Келина – 1-е мзд., - Лань, 2011 – 352с.
3. Модели и методы оптимизации в экономике и менеджменте: учеб. пос. для практ. занятий / В. И. Муравьев [и др. ]; Балт. гос. техн. ун-т. – СПб. , 2008. 197 с.
4. А. Информатика / В. А. Молодцов. - 4-е изд. доп. и перераб.- Ростов - н/Д: Феникс, 2010 - 262c Шипунова, А. В. Информатика: Учебно-справочное пособие / А. В. Шипунова. - М.: АСТ:Астрель, 2009 - 315c.
Базы данных, Интернет-ресурсы, информационно-справочные и поисковые системы
5. Единое окно доступа к образовательным ресурсам. Электронная библиотека [Электронный ресурс]: инф. система. – М.: ФГАУ ГНИИ ИТТ "Информика", 2005-2012. – Режим доступа: //www. http://window. edu. ru, свободный. – Загл. с экрана (дата обращения 11.04.2012)
6. Интернет-университет информационных технологий – дистанционное образование – INTUIT. ru [Электронный ресурс]: офиц. сайт. – М.: Открытые системы, 2003-2011. - Режим доступа: http://www. intuit. ru, свободный. - Загл. с экрана (дата обращения: 17.05.2012).
7. Поисковые системы: Google, Yandex, Rambler.
4. Оценка качества освоения программы
(форма аттестации, оценочные и методические материалы)
По окончании обучения на курсе проводится итоговая аттестация. Аттестация проводится на последнем занятии на основании оценок практических работ, выполняемых во время обучения на курсе.
ФОНД ОЦЕНОЧНЫХ СРЕДСТВ
4.1 Комплект оценочных средств по текущей аттестации
Комплект заданий по теме «Построение графиков и диаграмм»
Задача 1. Построить график изменения стоимости квадратного метра одно-, двух-, трех - и четырехкомнатных квартир на вторичном рынке жилья по месяцам в городе за полгода.
Задача 2. Дан прайс-лист с розничными, мелкооптовыми и оптовыми ценами товара (рис, вверху). Итоги годовой реализации товара №1 по кварталам представлены в таблице на рис., внизу. Требуется подсчитать квартальную и годовую выручку от реализации товара №1 и построить соответствующую диаграмму.
Комплект заданий по теме «Вычисления в Excel»
Задача 1. Пользователь Сбербанка внес вклад 5 млн. рублей. До какой суммы возрастет вклад через 7 лет, если процент годовых начислений равен а) 6 %, б) 10 %, в) 15%.
Задача 2. Вскоре после рождения сына родители решили внести в Сбербанк вклад с тем, чтобы через 22 года вклад вырос до суммы 30 млн. руб. Каков должен быть вклад, если процент годовых начислений равен 5 %, 8 %, 10%.
Задача 3. Через сколько лет начальный вклад 1000 $ увеличится до суммы 3000$, 5000 $ или 8000 $, если процент годовых начислений равен 6 %.
Задача 4. Какой процент годовых начислений должен обеспечить банк, чтобы первоначальный вклад 5000 $ увеличился втрое за 15, 18, 20 лет.
Задача 5. Рассчитать процентную ставку для 3-летнего займа размером 5000 руб. с ежеквартальным погашением по 1500 руб.
Задача 6. Определить эффективность инвестиции размером 200 млн, руб. по NPV, если ожидаемые ежемесячные доходы за первые пять месяцев составят соответственно: 20, 40, 50, 80 и 100 млн. руб. Издержки привлечения капитала составляют 13,5% годовых.
Задача 7. Определить, какие ежемесячные выплаты необходимо вносить по ссуде размером 200 тыс. руб., выданной на три года, при разных процентных ставках. Использовать Таблицу подстановки Excel.
Задача 8. Вкладчик Сбербанка решил ежегодно вносить вклад 1000$ в течение 10 лет. Сколько денег он сможет получить, если процент годовых начислений равен 5%, 10%, 15%.
Задача 9. Пусть вы откладываете 500 $ в конце каждого года на сберегательный счет при ставке 15 % годовых. Определить сумму накоплений к концу 6-го года.
Задача 10. Семья решила накопить для покупки дома сумму 20 000 $, вкладывая ежегодно сумму 1000$ (1500$ или 2000$). Сколько лет на это потребуется, если процент годовых начислений равен 6 %.
Комплект заданий по теме «Поиск решения»
Задачи линейного программирования
1. Составление штатного расписания
Пусть известно, что для нормальной работы больницы необходимо 5-7 санитарок, 8-10 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующая хозяйством и заведующий больницей. Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников больницы, при условии, что оклад санитарки не должен быть меньше прожиточного минимума 80 грн.
2. План выгодного производства
Предположим, что мы решили производить несколько видов конфет. Назовем их условно "A", "B" и "C". Известно, что реализация 10-и килограмм конфет "А" дает прибыль 9 грн., "В" - 10 грн. и "С" - 16 грн.
Определить затраты на рекламу, при которых прибыль от реализации товара будет максимальной. Решение этой задачи позволяет определить: "Стоит ли вкладывать дополнительные средства в рекламу, чтобы увеличить прибыль?".
Исходные данные задачи состоят из 2-х блоков: "Планируемые показатели" и "Данные о продукции". Так, в 1-м квартале запланированы следующие показатели реализации:
- сезонный фактор (ячейка В2) - равным 0.92); затраты на заработную плату персонала (ячейка В9) - 8 тыс. грн; затраты на рекламу (ячейка В10) - 10 тыс. грн.
Данные о продукции:
- цена реализации (ячейка В17) - 40 грн; себестоимость (ячейка В18) - 25 грн.
Расчет планируемых показателей производят так:
- объем сбыта продукции3) (ячейка В4) нелинейно зависит от сезонного фактора и затрат на рекламу
=35*B2*(B10+3000)^0,5
- доход с оборота определяется как ожидаемое количество проданных единиц продукции (ячейка В4), умноженное на себестоимость продукции, поэтому в ячейку В5 введем формулу
=B4*B17
- фраза "себестоимость реализованной продукции" на языке математики выглядит как
B6=B4*B18
- очевидно, что валовая прибыль, имеющая в электронной таблице адрес В7, определяется как
=B5-B6
- накладные расходы фирмы будем исчислять в объеме 15% дохода с оборота, то есть в ячейку B11 введем формулу
=0,15*B5
- валовые издержки рассчитаем как сумму затрат на заработную плату персонала, рекламу и накладные расходы, то есть
B12=СУММ(B9:B11)
- прибыль от продукции определим как валовую прибыль минус валовые издержки, то есть
B14=B7-B12
- рентабельность
B15=B14/B5
3. Предприятие выпускает 3 вида изделий. Для выпуска единицы изделия необходимо сырье в количестве 3 кг для 1-го вида, 8 кг для 2-го вида и 1 кг для 3-го вида. Общий запас сырья составляет 9500 кг. Изделия по видам входят в комплект в количестве 2, 1 и 5 штук соответственно. Определить оптимальное количество выпуска изделий, при котором количество комплектов будет максимальным. Комплекты немедленно отправляются потребителю. Склад вмещает не более 20 штук лишних изделий 2-го вида.
4. Производственная задача. Цех может производить стулья и столы. На производство стула идет 5 единиц материала, на производство стола - 20 единиц (футов красного дерева). Стул требует 10 человеко-часов, стол - 15. Имеется 400 единиц материала и 450 человеко-часов. Прибыль при производстве стула - 45 долларов США, при производстве стола - 80 долларов США. Сколько надо сделать стульев и столов, чтобы получить максимальную прибыль?
Пусть требуется определить план выпуска четырех видов продукции: A, B,C, D курсы трех видов: трудовые, материальные, финансовые, Количество каждогоi-го вида ресурса для производства каждого j-го вида про для изготовления которых используются ресдукции называют нормой расхода и обозначают 
. Количество каждого вида ресурса, которое имеется в наличии, обозначают 
.
Нормы расхода и наличие ресурсов приведены в таблице:
Ресурсы | Вид продукции | Запас ресурса | ||||
|
|
|
| |||
Трудовые Материальные Финансовые | 6 7 3 | 4 9 4 | 2 11 9 | 1 5 6 | 800 2000 12000 |
|
Граница: нижняя верхняя | 1 12 | - 2 | 3 - | - - | - - | |
План |
|
|
|
| - | |
Для выпуска единицы продукции, вида A требуется шесть единиц трудовых ресурсов, C - 11 единиц материальных ресурсов и т. д.
Предприятие располагает 12000 единицами финансовых ресурсов, 2000 единицами материальных, 800 единицами трудовых.
Исходя из рыночного спроса и производственно-технологических возможностей, заданы верхние и нижние предельные границы выпуска каждого вида продукции.
На основании исходных данных требуется составить математическую модель для определения плана выпуска продукции.
5. Транспортная задача. Уголь, добываемый в нескольких место-. рождениях, отправляется потребителям: заводам, электростанциям и т. п. Известно, сколько угля добывается в каждом из месторождений, скажем, за месяц и сколько его требуется на тот же срок любому из потребителей; расстояния между месторождениями и потребителями, а также условия сообщения между ними. Учитывая эти данные, можно подсчитать, во что обходится перевозка каждой тонны угля из любого месторождения в любой пункт потребления. Требуется при этих условиях спланировать перевозки угля таким образом, чтобы затраты были минимальными.
6. Задача о ценообразовании. Фирма-производитель предлагает потребителям однородную продукцию в нескольких филиалах по разным ценам. Каждый потребитель выбирает наиболее выгодный филиал, учитывая транспортные затраты до него, свой бюджет и стоимость продукции в филиале. Если сумма транспортных затрат и стоимости продукции превышает бюджет клиента, то клиент не покупает продукцию. Если же бюджета хватает на покупку и несколько филиалов дают минимальные затраты для клиента, то клиент выбирает филиал с минимальными транспортными затратами. Задача фирмы — назначить такую стоимость продукции в каждом филиале, чтобы получить максимальный суммарный доход от своих филиалов.
7. На склад экспресс-почты прибыло восемь посылок, которые требуется до ставить восьми адресатам. За хранение каждой посылки в течение одного часа взимается плата в размере ci y. e., время доставки посылки i до ад - ресата составляет ti часов, i = 1, . . . , 8, данные приводятся в табл.
В службе доставки работает только один курьер. Постройте математическую модель и найдите, в какой последовательности нужно доставить посылки, чтобы суммарная стоимость хранения была минимальной.
8. Пять парикмахеров решили подстричь друг друга. В табл. приводится время в минутах, затрачиваемое на стрижку.
Составьте математическую модель и найдите назначения между парикмахерами так, чтобы подстричь всех парикмахеров за наименьшее общее время.
Критерии оценки:
оценка «зачтено» выставляется, если:
- знание основных понятий предмета;
- умение использовать и применять полученные знания на практике;
- знание основных научных теорий, изучаемых предметов.
оценка «не зачтено», если:
- частичные знания по темам;
- незнание основных понятий;
- неумение использовать и применять полученные знания на практике.
5. Кадровые условия (составители программы)
Составитель программы – В., старший преподаватель кафедры математики и прикладной информатики.
Основные порталы (построено редакторами)