ДИСТАНЦИОННОЕ ОБУЧЕНИЕ!!!
учебно-методический материал по информатике и икт

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

Цель: познакомиться с различными типами диаграмм и графиков и научиться их строить; освоить основные приемы редактирования и оформления диаграмм.

Ход работы:

Задание №1:   Создайте таблицу для начисления заработной платы работникам фирмы «ЗАО Товарищ». Сохраните созданный документ.

Расчет заработной платы работников ЗАО «Товарищ»

Указания:

1. В ячейку А2 введите заголовок: Расчет заработной платы работников ЗАО «Товарищ»
2. Шапку таблицы расположите в диапазоне А5:К6

1. Расположите текст в ячейках E6:J6 в две строки: выделите указанные ячейки, выполните команду Формат/Ячейки, вкладка Выравнивание. Активизируйте флажок Переносить по словам.
2. Задайте вертикальное расположение текста в шапке таблицы:

• Выделите ячейки D6:J6, выполните команду Формат/Ячейки, вкладка Выравнивание.
• В группе Ориентация щелкните по верхнему ромбику над словом Надпись.

5. Выровняйте заголовок по центру относительно таблицы.
6. Заполните столбцы с №, ФИО, Окладом, а остальные столбцы рассчитайте по формулам:

• Коэффициент составляет 50% от оклада: установите курсор в ячейку D7, введите формулу на английском языке =С7*50% или =С7*0,5
• Скопируйте данную формулу в остальные ячейки столбца коэффициент.
• Аналогично рассчитайте Северную надбавку, которая составляет 80% от оклада.
• В столбце Всего начислено подсчитайте доходы сотрудника (сумма всех доходов)
• Подоходный налог рассчитывается по формуле: 12% от начисленной суммы.
• В фонд медицинского страхования производится выплата в размере 3% от начисленной суммы.
• В пенсионный фонд производится выплата в размере 1% от начисленной суммы
• В столбце Всего удержано подсчитываются расходы сотрудника (сумма всех налогов)
• В столбце К выдаче рассчитывается денежная  сумма, выдаваемая работнику на руки (доходы-расходы)

7. Отформатируйте таблицу: меню Формат/Ячейки (задайте параметры шрифта на вкладке Шрифт, выравнивание текста на вкладке Выравнивание, рамки на вкладке Граница,заливку на вкладке Вид)

7. Переименуйте Лист1 в Расчет, меню Формат/Лист/Переименовать.
8. Сохраните документ.

Задание №2: Постройте диаграмму, отражающую заработную плату всех сотрудников.

1. Выполните команду Вставка/Диаграмма выберите Гистограмма, в группе Вид -Объемный вариант обычной гистограммы .
2. После того как вставится пустое окно гистограммы. На вкладке Конструкто выбрать кнопку «Выбрать данные». В строке Диапазон указываются ячейки с данными, на основе которых будет построена диаграмма:

• Нажмите клавишу СТRL и не отпуская ее выделите два диапазона ячеек: В7:В16  и К7:К16
• В результате в строке Диапазон появится ссылка на  лист  Расчет:
  =Расчет!$В$7:$В$16;Расчет!$К$7:$К$16
• Если вы ошиблись при указании диапазона, очистите строку Диапазон и выделите его заново. Затем нажмите кнопку ОК.

3)        Сохраните документ.

Задание №3: Создайте таблицу учета выручки от продажи мороженого фирмы «ПИНГВИН», если известно, что фирма торгует по пяти округам города только в летние месяцы.

Продажа  мороженого фирмой ПИНГВИН в летний период.

                     
Указания;

1. Перейдите на Лист 2.
2. В ячейку АЗ введите текст: Продажа мороженого фирмой Пингвин
3. В ячейку А4 введите: летний период.

4)        Расположите шапку таблицы начиная с ячейки А6 (вся таблица будет располагаться в диапазоне А6:F11)

5. Заполните исходными данными столбцы: район/месяц, июнь, июль, август.
6. Для диапазонов ячеек В7:Е11 и В13:Е13 задайте денежный формат: выделите указанные диапазоны, меню Формат вкладка Число, в списке Числовые форматы выберите Денежный, в поле Число десятичных знаков введите 2.
7. В ячейку Е7 введите формулу, рассчитывающую выручки за 3 месяца по центральному округу. Скопируйте данную формулу в остальные ячейки столбца Всего по округу.
8. В   ячейку  В13   введите   формулу,   подсчитывающую   выручку   всех   округов   за   июнь, скопируйте данную формулу в ячейки С13:Е13.
9. В ячейку В14 введите текст: Всего за месяц, в ячейку Е14: Общая выручка.

10)Заполните  столбец  %  выручки  по  округу,  используя  абсолютную  ссылку  на ячейку, содержащую общую выручку:

• В ячейку F7 введите формулу: =Е7/$Е$13 (знак $ указывает на абсолютную адресацию)
• Используя автозаполнение, скопируйте формулу в остальные ячейки.

11. Задайте процентный формат: выполните команду Формат ячейки, вкладка Число Процентный, в поле Число десятичных знаков введите 2.
12. Отформатируйте  таблицу  (задайте   параметры   шрифта,   выравнивание  текста,  рамки  и заливку таблицы)

13) Заголовки расположите по центру относительно таблицы.

14) Вставьте любой рисунок: меню Вставка/Рисунок/Автофигуры.

15) Сохраните документ.

Задание №5:    Постройте диаграмму, отображающую вклад каждого округа в общую выручку.

1. Выберите объемный вариант круговой диаграммы.
2. В источнике данных укажите 2 диапазона: зажмите клавишу СТRL и не отпуская ее выделите два диапазона ячеек А 7:А11 и F7:F11

4. Переместите диаграмму ниже таблицы и измените ее размер с помощью маркеров.
5. Сохраните документ

5)        .                     

Задание №6: Подготовьте таблицу и диаграмму к печати.

Указания:

1. Выполните команду Файл/Предварительный просмотр, щелкните по кнопке Страница (появится диалоговое окно Параметры страницы)
2. На вкладке Поля задайте Верхнее и Нижнее поля по 2 см, Левое и Правое - по 1 см, в группе Центрировать на странице активизируйте флажок Горизонтально.
3. На вкладке Колонтитулы щелкните по кнопке Создать верхний колонтитул, перейдите в окно Справа и напишите Отчет 1, нажмите ОК. В списке Нижний колонтитул выберите Страница 1.

1. На вкладке Лист в группе Печать отключите флажок Сетка, нажмите ОК.

Если необходимо  подготовить  к печати только  диаграмму,   то для  ее  просмотра диаграмму нужно выделить щелчком мыши.

Задание №7:    Создайте самостоятельно таблицу «Производство бумаги» и постройте линейчатую диаграмму по данным таблицы.

                            Производство бумаги на душу населения, кг.

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

Цель: Научиться строить графики функций и форматировать их.

Ход работы:

Задание 1.:Построить графики следующих функций

Y1(x) = x2 – 1
Y2(x) = x2 + 1
Y3(x) = 10 * Y1(x)/Y2(x)
Диапазон изменения аргумента – [–2;2] с шагом 0,2..

Технология работы: подготовка таблицы

1. Перейдите на Лист2, переименуйте Лист2 в «Графики»:
щелкните правой клавишей мыши на ярлыке Лист2 – вызов контекстного меню;
выберите команду Переименовать.

Рис. 3

2. Подготовьте на листе «Графики» таблицу согласно рис 3: введите в ячейки A1:D1 названия столбцов; в дальнейшем названия функций Yl(x), Y2(x), Y3(x) составят текст легенды;
заполните столбец аргументов (х) от –2 до 2 с шагом 0,2. Для этого воспользуйтесь любым известным вам способом автозаполнения диапазона.

3. Заполните столбцы функций, т. е. введите в столбцы В, С, D значения функций в соответствующих точках. Для этого введите самостоятельно в ячейки В2, С2, D2 формулы, математическая запись которых, , Yl(x) = х2 – 1, Y2(x) = х2 + 1, Y3(x) = 10*Yl(x)/Y2(x)

4. Скопируйте формулы в остальные ячейки таблицы (используйте маркер заполнения).

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

4. Построение и редактирование графиков функций

1. Выделите диапазон данных для построения графиков A1:D22.

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

Рис. 4

3. Отредактируйте графики согласно рис. 4 и сохраните изменения в файле. Представьте результаты работы.

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

Задание 3. На листе 2 выполняем индивидуальное задание. Номер варианта соответствует номеру списка в классном журнале.

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

Цель: Систематизация, контроль и расширение знаний учащихся о  принципах работы с программой Электронные таблицы, формирование устойчивых знаний, что такое стандартные функции и использование их  при выполнении расчетов в решении задач;

Методические рекомендации.

Познакомьтесь с функциями пакета Excel

Функции предназначены для упрощения расчетов и имеют следующую форму: у=f(х) где у — результат вычисления функции, х — аргумент, f— функция.

Пример содержимого ячейки с функцией: =A5+sin(C7), где А5 — адрес ячейки:     sin () имя функции, в круглых скобках указывается аргумент; С7 — аргумент (число, текст и т.д.) в данном случае ссылка на ячейку, содержащую число.

Некоторые функции

КОРЕНЬ (х) — вычисляет положительный квадратный корень из числа х. Например. КОРЕНЬ(25)=5.

SIN(x) — вычисляет синус угла х, измеренного в радианах. Например: SIN(0,883)=0,72646.

МАКС(список) — возвращает максимальное число списка. Например МАКС(55, 39, 50, 28, 67, 43)=67

СУММ(список) — возвращает сумму чисел указанного списка (блока). Например СУММ(А1:А300) подсчитывает сумму чисел в трехстах ячейках диапазона А1:АЗ00.

Имена функций в русифицированных версиях могут задаваться на русском языке.

Для часто используемой функции суммирования закреплена кнопка на панели инструментов Σ.

Ход работы:

Запустите Excel и просмотрите справочную информацию о назначении и синтаксисе функций даты и времени. Для этого, вызвав справку Excel, на вкладке Содержание выберите раздел Справка по функциям, подраздел Функции даты и времени. Просмотрите справки по функциям ДАТА, ДЕНЬ, МЕСЯЦ, ДЕНЬ-НЕД, СЕГОДНЯ, ТДАТА. Для возврата на предыдущий экран справок используйте кнопку «Назад». После просмотра справочной информации закройте окно справки.

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

3.    В ячейке А1 введите заголовок таблицы «Календарь». Во второй строке в ячейки А2, В2, С2 введите названия граф: «Дата»,«Номер дня», «Название дня и месяца».

4.    Задайте в ячейке A3 формулу =ДАТА(2014;1;1) для ввода первой даты нужного года (в данном случае 1 января 2014 г.). В ячейке А4 задайте формулу =АЗ+1 для вычисления даты следующего дня. Скопируйте формулу из А4 в диапазон А5:А369. Получится календарь на 2014 год.

5.    В ячейку ВЗ введите формулу определения дня недели =ДЕНЬНЕД(АЗ;2). (Согласно параметру 2 порядок дней в неделе следующий: понедельник - 1, вторник - 2, среда - 3, ..., воскресенье - 7.) Скопируйте эту формулу из ВЗ в диапазон В4:В369. В столбце В будут выведены порядковые номера дней недели за весь год.

6.    В ячейку СЗ введите формулу =ТЕКСТ(АЗ;«ДДДДД ММММММ»), которая по дате определит текстовое название дня недели и месяца. Скопируйте формулу из СЗ в диапазон С4:С369.В столбце С будут выведены названия дней недели и месяцев завесь год.

8.    Выделите таблицу и оформите ее, выбрав в меню Формат команду Ячейки. На вкладке Граница выберите тип линии и включите внешние и внутренние границы, на вкладке Вид выберите цвет заливки и щелкните кнопку «ОК». Результат решения задачи будет выглядеть, как показано на рис. 2.

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

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

        Цель: научиться использовать абсолютную адресацию при заполнении таблиц.

Ход работы:

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

1. Сначала нужно определить исходные данные задачи: величину распределяемой суммы дохода (число), фамилии работников (текст), количество акций у каждого акционера (целое число). В качестве рассчитываемых данных в таблице будет суммарное значение числа всех акций (число) и сумма выплаты каждому акционеру (число).
2. Запустите Excel, переименуйте Лист1 в Дивиденды, опишите на нем структуру таблицы и введите исходные данные следующим образом
3. В ячейку В8 введите формулу суммы диапазона В4:В7; для этого, выделив диапазон В4:В7, щелкните кнопку Автосумма на панели инструментов Стандартная.

4. Введите в ячейку В2 величину распределяемого дохода, например, 10000.
5. Введите в ячейку С4 формулу расчета выплаты =$В$2/$В$8*В4. (Выплата каждому акционеру равна частному от всей суммы доходов акционерного общества и количества всех акций, умноженному на количество акций данного акционера.) Обратите внимание, что адреса ячеек В2 и В8 записаны в абсолютной форме, т.е. они не изменятся при копировании формулы из ячейки С4 в С5:С7.

Примечание. Для создания абсолютной ссылки на ячейки В2, В8 нужно при вводе формулы щелкнуть на данной ссылке клавишей F4.

6. Задайте финансовый формат для значений данных в ячейках С4:С7, для чего, выделив этот диапазон, выберите в меню Формат опцию ячейки, а затем на вкладке Число окна Формат ячеек выберите вариант числового формата: Финансовый, после чего укажите число десятичных знаков 2, а в поле Обозначение выберите р. и щелкните кнопку «ОК».
7. Измените ширину столбца Е, чтобы в нем правильно отображались выплаты в финансовом формате.
8. Проверьте действие таблицы, изменяя данные в ячейках В2,В4:В7.
9. Постройте диаграмму, отображающую долю выплат каждому акционеру от общей суммы доходов. Для этого выделите диапазон ячеек А4:С7 и щелкните кнопку «Мастер диаграмм» в панели инструментов Стандартная. В начале построения диаграммы выберите тип Разрезная круговая диаграмма и щелкните кнопку «Далее». На этапе определения источника данных диаграммы выберите для отображения в диаграмме данные из рядов в столбцах, уточните диапазон. На вкладке Ряд в поле Имя задайте диапазон данных, отображающих название Лист1!$А$1. Щелкнув кнопку «Далее», на вкладке Подписи данных в поле Подписи значений выберите опцию доля. Щелкнув кнопку «Далее», выберите место размещения диаграммы на отдельном листе с названием Диаграмма1 и щелкните кнопку «Готово».
10. Измените формат области диаграммы; для этого, щелкнув правой кнопкой мыши на диаграмме, в контекстном меню выберите команду Формат области диаграммы. Выбрав вкладку Вид, щелкните кнопку «Способы заливки», затем в окне Заливка выберите вкладку Текстура, затем щелкните кнопку с текстурой (например, белый мрамор). Щелкнув кнопку «ОК», закройте окно Заливка, а затем щелкните «ОК» для закрытия окна Формат области диаграммы. Просмотрите вид полученной диаграммы на отдельном листе Диаграмма1.

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

Цель: Научиться создавать базы данных в MS Excel. Изучить основные способы обработки данных в базе данных MS Excel.

Ход работы:

1.    Запустите Excel

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

3.    Опишите структуру таблицы и заполните ее данными, например, как показано в таблице.

4.    Отсортируйте список участников, упорядочив его в порядке возрастания весовых категорий. Для этого установите курсор в столбец С и щелкните кнопку «Сортировка» по возрастанию в панели инструментов Стандартная. После этого порядок размещения записей о спортсменах в таблице изменится.

5.    Найдите участников соревнований из Самары. Для этого в меню Правка выберите команду Найти, затем в окне Найти и заменить в поле Найти введите образ поиска «Самара», щелкнув кнопку «Параметры», задайте опции поиска: не учитывать регистр, просматривать по строкам. Для начала поиска щелкните кнопку «Найти далее». Курсор выделяет ячейку с найденным текстом. Щелчок кнопки «Найти далее» переводит курсор на следующую ячейку таблицы, удовлетворяющую условиям поиска. Если данных нет, то на экране будет выведено сообщение об этом. Закройте окно поиска.

6.    Отфильтруйте список спортсменов и просмотрите участников состязаний в весовой категории до 130 кг. Для этого выберите команду Фильтр в меню Данные, а затем выберите опцию Автофильтр. После этого в правой части заголовков всех граф таблицы появятся стрелки. Выбрав графу Вес, нажмите кнопку со стрелкой вниз, выберите значение в списке, например 130.

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

Можно применить фильтр к уже отфильтрованному списку. Щелкнув стрелку в графе Имя, установите фильтр Александр. После этого в таблице будут отображаться только записи о спортсменах, у которых Вес=130 и Имя=«Александр».

7.    Удалите фильтр Имя=«Александр»; для этого нажмите кнопку со стрелкой в графе Имя, а затем выберите из развернувшегося списка Все.

Чтобы удалить фильтры для всех столбцов списка, нужно выбрать пункт Фильтр в меню Данные, а затем - команду Отобразить все. Для удаления автофильтра выберите пункт Фильтр в меню Данные, а затем - команду Автофильтр.

8.    Для просмотра записей таблицы с использованием формы выберите команду Форма в меню Данные. После этого на экране раскроется форма с отображением записей таблицы на листе 1,как показано на рис. 4.

Рис. 4. Форма с отображением записей таблицы

Для получения подсказки о назначении какого-либо параметра формы нажмите кнопку «?» в правом верхнем углу окна и щелкните интересующий вас параметр.

Сначала в форме отображается информация из первой строки таблицы. В окне формы над кнопкой «Добавить» отображается информация 1 из 9, что означает: в форме отображается текущая запись с номером 1, всего записей в таблице 9. Для просмотра следующей записи щелкните кнопку «Далее», предыдущей записи -«Назад».

9.    Для того чтобы задать фильтр отбора данных, щелкните кнопку «Критерии», затем в поле Регион введите значение «Самара» и нажмите клавишу Enter.

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

10. Для отмены фильтрации данных следует щелкнуть кнопку«Критерии» и очистить поле, в котором задано значение фильтра отбора данных (в данном примере в поле Регион удалить значение «Самара»).

11. Для вставки записи в таблицу укажите ячейку списка, начиная с которой следует добавлять записи (например, 5), затем щелкните кнопку «Добавить» и введите значения полей новой записи, используя клавишу Tab для перемещения к следующему полю. Задайте следующие данные новой записи: Петров, Василий, 97, Тула, 5, 2. Завершите ввод данных, нажав клавишу Enter. Обратите внимание, что количество записей в таблице, отображаемое над кнопкой «Добавить», увеличилось на 1.

12. Измените запись об участнике из Ульяновска, для чего, щелкнув кнопку Критерии, в поле Регион введите значение «Ульяновск» и нажмите Enter, затем измените данные в записи (например, измените фамилию «Зуйков» на «Зайцев»). После изменения данных нажмите клавишу Enter для обновления текущей записи.

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

13. Для удаления записи об участнике из Ульяновска установите ее текущей, а затем щелкните кнопку «Удалить». Подтвердите удаление записи, щелкнув кнопку «ОК» на панели сообщения Microsoft Excel.

Примечание. Чтобы отменить добавление записи, нажмите кнопку «Вернуть» перед нажатием клавиши Enter или кнопки «Закрыть».

14. Нажмите кнопку «Закрыть» для выхода из формы и просмотрите изменения, внесенные в таблицу в режиме формы.

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

Цель: Отработать навык работы с таблицами.

Задачи:

1. закрепить навыки по заполнению и форматированию таблиц
2. Изучить понятие абсолютной ссылки
3. Отработать навык вставки абсолютной функции

4. Деловая графика область компьютерной графики, предназначенная для наглядного представления различных показателей работы учреждений. Плановые показатели, отчетная документация, статистические сводки - вот объекты, для которых с помощью деловой графики создаются иллюстративные материалы. Программные средства деловой графики включаются в состав электронных таблиц.
5. Назначение деловой графики - создание иллюстраций, часто используемых в работе различных учреждений. Плановые показатели, отчетная документация, статистические сводки - вот объекты, для которых с помощью деловой графики создаются иллюстративные материалы.
6. Цель: формирование ключевых компетенций.
7. Задание: Сформируйте и заполните накопительную ведомость начисления оплаты номеров гостиницы «Старт» за март 2012 года. Номера в гостинице 1-местные стоят для каждою клиента 1800 руб. в день, 2-местные 1200 руб. с каждого клиента. Номер можно забронировать. Бронь в гостинице может быть двух видов: групповая и индивидуальная и оплачивается отдельно. При бронировании на группу оплат за первый день проживания увеличивается на 25% от стоимости номера, в тех случаях, когда брони нет или она индивидуальная, доплаты нет.
8. Тип брони и количество дней проживания в каждом номере представлены в таблице.
9. Рассчитайте оплату за бронь для каждого номера, если такая есть. Рассчитайте оплату за все дни проживания по каждому номеру гостиницы. Рассчитайте итоговые данные по гостинице: оплату за бронь, количество дней проживания в месяц, полную оплату по гостинице за месяц. Определите среднее количество дней проживания, величину максимальной и минимальной оплаты за дни проживания.
10. Ход работы:
11. Используя навыки работы с электронными таблицами, заполните предложенную таблицу исходными данными.

Ведомость начисления оплаты номеров гостиницы «Старт» за март 2012 г.

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

• Круговую диаграмму по графе «Количество дней проживания».
• Гистограмму по графе «Оплата за дни проживания».