Содержание

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

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

Ход работы:

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

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

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

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

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 по форме:

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

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

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

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

5.      Покажите работу преподавателю .

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

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

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

Ход работы:

Задание № 1

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

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

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

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

4.      Сохранить работу в файле Практичекая работа 2.

Задание № 2

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

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

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

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

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

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

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

Задание № 3

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

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

3.      Покажите работу преподавателю.

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

Задание № 1

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

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

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

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

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

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

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

Задание № 2

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

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

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

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

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

Тарифные ставки сотрудников фирмы "Рога и копыта"

Задание № 3

Работа с вложенными функциями ЕСЛИ

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

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

3. Добавить столбцы Ставка, Начислено, Налог, Заработная плата и заполнить их таким образом:

Ставка = произвольное число от 500 до …

Начислено = Ставка * Тарифные ставки

Налог = 0, если Начислено меньше 1000, 12%, если Начислено больше 1000, но меньше 3000, и 20%, если Начислено больше или равно 3000

4. Сохранить документ в своей папке.

5. Показать работу преподавателю .

Заработная плата сотрудников фирмы "Рога и копыта"

Тема: Построение диаграмм и графиков

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

Научиться строить, форматировать и редактировать диаграммы и графики.

Ход работы:

Задание № 1

1.Открыть файл Практическая работа 2, лист Функция.

2.Построить график функции по данным таблицы.

3.Сохранить сделанные изменения.

Задание № 2

1.Открыть новую рабочую книгу.

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

3.Выполнить соответствующие вычисления (использовать абсолютную ссылку для курса доллара).

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

5.Построить сравнительную круговую диаграмму цен на товары и диаграмму любого другого типа по количеству проданного товара.

6.Диаграммы красиво оформить, сделать заголовки и подписи к данным.

7.Лист1 переименовать в Стоимость. Сохранить в файле Практическая работа 4

Расчет стоимости проданного товара

Задание № 3

1.Перейти на Лист2. Переименовать его в Успеваемость.

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

Успеваемость

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

4.Построить гистограмму по успеваемости по предметам.

5.Построить пирамидальную диаграмму по средней успеваемости каждого ученика

6.Построить круговую диаграмму по средней успеваемости по предметам. Добавить в этой диаграмму процентные доли в подписи данных.

7.Красиво оформить все диаграммы.

8.Показать работу преподавателю.

Тема: Сортировка и фильтрация данных

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

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

Ход работы:

Задание № 1

Открыть файл pricetovar.xls, который хранится на диске _____ в папке «Задания для EXCEL». Сохранить его в своей папке. С содержанием файла выполнить следующие действия:

1.      Найти в нем сведения о предлагаемых процессорах фирмы AMD (воспользоваться командой ПРАВКА ðНАЙТИ).

2.      Найти и заменить в этой таблице все вхождения символов DVD-R на  DVD-RW

3.      Вывести сведения о товарах, которые произведены фирмой ASUS (воспользоваться автофильтром).

Задание № 2

Открыть файл  Фильмы.xls, который хранится на диске _____ в папке «Задания для EXCEL». Сохранить его в своей папке. С содержанием файла выполнить следующие действия:

1.                         На новом листе с соответствующим названием упорядочить информацию в таблице сначала по магазинам, затем по жанрам, затем по фильмам.

2.                         На новом листе с соответствующим названием разместить все фильмы жанра Драма, которые есть в магазине Стиль.

3.                         На новом листе с соответствующим названием разместить информацию о результатах продаж в разных магазинах фильмов ужасов и построить сравнительную диаграмму по этим данным.

4.                         На новом листе с соответствующим названием разместить информацию о фильмах жанра Фантастика, которые были проданы на сумму, больше 10000 р.

5.                         На новом листе с соответствующим названием разместить информацию о фильмах, которые продаются в магазинах Наше кино, Кинолюб, Стиль.

6.                         Определить, в каких магазинах в продаже есть фильм Синий бархат.

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

Показать работу преподавателю.

Контрольная работа по теме:

 «Электронные таблицы.

Задание № 2

                              Задание № 3

Создать таблицу и отформатировать ее по образцу.

• Столбец «Количество дней проживания» вычисляется с помощью функции ДЕНЬ и значений в столбцах «Дата прибытия» и «Дата убытия»
• Столбец «Стоимость» вычисляется по условию: от 1 до 10 суток – 100% стоимости, от 11 до 20 суток –80% стоимости, а более 20 – 60% общей стоимости номера за это количество дней.

Ведомость регистрации проживающих

в гостинице «Рога и копыта».

Задание № 4

Составить таблицу умножения

Для заполнения таблицы используются формулы и абсолютные ссылки.

Таблица умножения

Контрольная работа по теме:

 «Электронные таблицы. Ввод, редактирование и форматирование данных. Стандартные функции».

Теоретические сведения:

1. Правила техники безопасности и поведения в КОТ.
2. Операции с ячейками и диапазонами.
3. Типы данных.
4. Форматирование ячеек.
5. Вставка функций.

Вариант № 2

Задание № 1

Построить на промежутке [-2, 2] с шагом 0,4 таблицу значений функции:

К таблице применить один из видов автоформата.

Задание № 2

Создать таблицу и отформатировать ее по образцу.

Вычисления в столбце Отчетный год в % к предыдущему выполняются по формуле:

Отчетный год, тонн / Предшествующий год, тонн,

А в столбце Выполнение поставок с помощью функции ЕСЛИ (больше или равно 100%  – выполнено, иначе – нет)

Выполнение договора поставки овощей и фруктов

для нужд детских учреждений Соломенского района

                                                                 Задание № 3

Создать таблицу расчета оптимального веса  и отформатировать ее по образцу.

• Столбец «Оптимальный вес» вычисляется по формуле:

                            Оптимальный вес = Рост- 100

• Если вес человека оптимальный, то в столбце «Советы» напротив его фамилии должна появиться запись «Оптимальный вес». Если вес меньше оптимального – «Вам надо поправиться на», с указанием в соседней ячейке количества недостающих килограмм. Если вес больше оптимального – «Вам надо похудеть на» с указанием в соседней ячейке количества лишних килограмм.

Сколько мы весим?

Задание № 4

Составить таблицу умножения

Для заполнения таблицы используются формулы и абсолютные ссылки.

Таблица умножения

Комплексная практическая работа по теме:

«Создание таблиц в EXCEL».

Вариант № 1

В папке МОИ ДОКУМЕНТЫ создать папку КР EXCEL и сохранить в ней все таблицы.

Значения в затененных ячейках вычисляются по формулам!

Задание 1.

     1. Создать таблицу по образцу. Выполнить необходимые вычисления.

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

     3. Построить сравнительную диаграмму (гистограмму) по уровням продаж  разных товаров в регионах и круговую диаграмму по среднему  количеству товаров.

Продажа товаров для зимних видов спорта.

Задание 2

1. Создать таблицу по образцу. Выполнить необходимые вычисления.

Всего затрат =Общий пробег * Норма затрат

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

3. Построить круговую диаграмму «Общий пробег автомобилей» с указанием процентных долей каждого и столбиковую диаграмму «Затраты на ремонт автомобилей».

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

“Учет затрат на техническое обслуживание и текущий ремонт автомобилей”

                 Задание 3

2       Дана функция:

     Протабулировать эту функцию на промежутке [0, 7] с шагом 0,2 и построить график этой функции.

Задание 4

     1. Создать таблицу и отформатировать ее по образцу.

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

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

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

     5. С помощью фильтра вывести сведения только о военнообязанных сотрудниках (Пол -м, возраст от 18 до 45 лет).

Сведения о сотрудниках фирмы "Рога и копыта"

Комплексная практическая работа по теме:

«Создание таблиц в EXCEL».

Вариант № 2

В папке МОИ ДОКУМЕНТЫ создать папку КР EXCEL и сохранить в ней все таблицы.

Значения в затененных ячейках вычисляются по формулам!

Задание 1

1. Создать таблицу по образцу. Выполнить необходимые вычисления.

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

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

Средняя температура по месяцам.

Задание 2

1. Создать таблицу по образцу. Выполнить необходимые вычисления.

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

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

4. Построить столбиковую диаграмму средней успеваемости студентов и круговую диаграмму средней оценки по предметам.

Результаты сессии:

                         Задание 3

Дана функция:

         Протабулировать эту функцию на промежутке [0, 5] с шагом 0,2 и построить график этой функции.

Задание 4

1. Создать таблицу и отформатировать ее по образцу.

2. Данные в столбце Цена за блок вычисляются как 90% от цены за 10 единиц товара.

3. Данные в столбце Количество блоков вычисляются с помощью функции ЦЕЛОЕ,

4. Данные в столбце Количество единиц вычисляются как разность

                        Количество- Количество блоков

5. Стоимость вычисляется:

Цена за блок* Количество блоков + Цена за единицу* Количество единиц

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

7. Построить круговую диаграмму по количеству проданного товара. Подписать доли.

8. С помощью фильтра вывести сведения только о тех товарах, стоимость которых выше средней.

Ведомость оптово-розничной торговли фирмы "Рога и копыта"

Комплексная практическая работа по теме:

«Создание таблиц в EXCEL».

Вариант № 3

В папке МОИ ДОКУМЕНТЫ создать папку КР EXCEL и сохранить в ней все таблицы.

Значения в затененных ячейках вычисляются по формулам!

Задание 1

         1. Создать таблицу по образцу. Выполнить необходимые вычисления.

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

       3. Построить сравнительную диаграмму (гистограмму) по уровням продаж в разные месяцы в регионах и круговую диаграмму по среднему количеству продаж в регионах.

Показатели продажи товаров фирмы «Рога и копыта».

Задание 2

1. Создать таблицу по образцу. Выполнить необходимые вычисления.

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

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

4. С помощью средства Фильтр определить отдел и код изделия, которое имеет максимальную сумму всех затрат.

Себестоимость опытно-экспериментальных работ

                     Задание 3

Дана функция:

Протабулировать эту функцию на промежутке [0, 6] с шагом 0,2 и построить график этой функции.

Задание 4

1. Создать таблицу и отформатировать ее по образцу.

2. Стаж работы вычислить, используя данные из столбца Дата приема и стандартные функции СЕГОДНЯ и ГОД.

3. Тариф вычислить в зависимости от стажа таким образом:

до 5 лет -1, от 5 до 10 лет -1.5, более 10 -2.

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

5. С помощью фильтра вывести сведения только о тех сотрудниках, стаж работы, которых больше 10 лет.

Сведения о сотрудниках фирмы "Рога и копыта"

Комплексная практическая работа по теме:

«Создание таблиц в EXCEL».

Вариант № 4

В папке МОИ ДОКУМЕНТЫ создать папку КР EXCEL и сохранить в ней все таблицы.

Значения в затененных ячейках вычисляются по формулам!

Задание 1

1. Создать таблицу по образцу. Выполнить необходимые вычисления.

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

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

Процент жителей России, посещающих театры и стадионы.

Задание 2

1.      Создать таблицу по образцу. Рассчитать:

Прибыль = Выручка от  реализации –Себестоимость.

Уровень рентабельности = (Прибыль / Себестоимость)* 100.

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

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

4. С помощью средства Фильтр определить виды продукции, себестоимость которых превышает среднюю.

Расчет уровня рентабельности продукции

                                                                Задание 3

Дана функция:

          Протабулировать эту функцию на промежутке [0, 5] с шагом 0,2 и построить график этой функции.

Задание 4

1. Создать таблицу и отформатировать ее по образцу.

2. Данные в столбце Сколько месяцев…  вычисляются с помощью функций ГОД и МЕСЯЦ, в столбце Действия с товаром с помощью функции ЕСЛИ по такому принципу:

Выбросить - если срок хранения истек,

Срочно продавать - остался один месяц до конца срока хранения,

Можно еще хранить - до конца срока хранения больше месяца.

3. Отсортировать данные в таблице по Сроку хранения.

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

5. С помощью фильтра вывести сведения только о тех товарах, которые могут храниться от трех до шести месяцев, но которые приходится выбросить.

Учет состояния товара на складе фирмы "Рога и копыта"

Список литературы:

1.                   Тарханова, О. В. ТюмГАСУ. Кафедра информатики и информационных технологий (Тюмень).     Информатика: м/у к контрольной работе по теме: "Табличный процессор MS Excel" для студ. спец.: 100101 - "Сервис", 100103 - "СКТ" очной формы обучения/ О. В. Тарханова.- Тюмень: ТюмГАСУ.     Ч.2.- 2009.- 14 с.

2.                   Тарханова, О. В. ТюмГАСУ. Кафедра информатики и информационных технологий (Тюмень).     Информатика: м/у к лабораторным работам по теме: "Табличный процессор MS Excel" (на базе Ms Excel 2003) для студ. спец.: 100101 - "Сервис", 100103 - "СКТ" очной формы обучения/ О. В. Тарханова.- Тюмень: ТюмГАСУ.  Ч.1.- 2009.- 38 с.

3.                   Фрай Кертис Д.  Microsoft Office Excel 2007. Русская версия: пер. с англ./ К. Д. Фрай.- М.: ЭКОМ Паблишерз, 2007.- 480 с.: ил..-(Шаг за шагом)

4.                   Долженков, В.   Microsoft Excel 2003: Наиболее полное руководство в подлиннике/ В. Долженков, Ю. Колесников.- СПб.: БХВ-Петербург, 2004.- 1024 с.

5.                   Подгорнова, Н. Н. Методические указания к выполнению лабораторных работ по дисц. "Информатика", разд. "Электронные таблицы MS Excel": для студ. спец. 270205 " АД и А" очной формы обучения/ Н. Н. Подгорнова.- Тюмень: ТюмГАСУ, 2009.- 42 с..-(Кафедра информатики и информационных технологий)

6.                   Левин, А. Ш.  Excel - это очень просто!: включая Excel 2007. Новая версия/ А. Ш. Левин.- 2-е изд..- СПб.: Питер, 2010.- 108 с.: ил.

7.                   Дабижа, Г. Работа на компьютере: краткий курс/ Г. Дабижа.- СПб.: Питер, 2003.- 256 с.: ил.

8.                   Зайден, Марк. Excel 2000/ Марк Зайден.- М.: Лабор Базовых Знаний, 1999.- 336 с..-(Компьютер)

9.                   Кашаев, С. М. Работа в Excel 2007/ С. М. Кашаев.- СПб.: Питер, 2009.- 144 с.: ил.

10.               Мехалая, Ж.И. Основы современной информационной технологии: учеб. пособие/ ред. Павлов А.Б..- М.: АСВ Ч.2: EXCEL.- 2002.- 96 с.: 64 ил.

11.               Лабораторный практикум по информатике [Текст]: учебное пособие мо/ под ред. В. А. Острейковского.- 2-е изд., стер..- М.: Высшая школа, 2006.- 376 с.: ил.

12.               Конев, Ф. Б. Иформатика для инженеров: учебное пособие мо/ Ф. Б. Конев.- М.: Высшая школа, 2004.- 272 с.: ил.

13.               Пасько, В. Краткий самоучитель работы на персональном компьютере/ В. Пасько.- 2-е изд..- СПб.: Питер, 2006.- 416 с.: ил.

14.               Безручко, В. Т. Практикум по курсу "Информатика". Работа в Windows 2000, Word, Excel: учебное пособие/ В. Т. Безручко.- М.: Финансы и статистика, 2003.- 544 с.

15.               Безручко, В.Т. Практикум по курсу "Информатика".Работа в Window,Word,Excel: учеб. пособие/ В. Т. Безручко.- М.: Финансы и статистика, 2002.- 272 с.: ил.