Для обучающихся по проведению практических занятий в программе MS Excel

МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ

ДЛЯ ОБУЧАЮЩИХСЯ ПО ПРОВЕДЕНИЮ ПРАКТИЧЕСКИХ ЗАНЯТИЙ В ПРОГРАММЕ MS EXCEL

Дисциплина «Информационное обеспечение профессиональной деятельности»

                                                               Пермь 2017                                                   

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

 

 

«Информационное обеспечение профессиональной деятельности»

(наименование дисциплины)

 

 

Рассмотрено на заседании ЦМК __________________________

______________________________________________________

 

Утверждено научно методическим советом

Составитель: Лутченко Т.В., преподаватель информатики и информационных технологий

 

 

ОГЛАВЛЕНИЕ

Пояснительная записка. 4

Глава 1 Интерфейс программы MS Excel 7

Глава 2 Ввод и редактирование данных. 9

Глава 3 Работа с формулами и функциями. 13

Глава 4 Построение диаграмм.. 17

Практические работы.. 19

Практическая работа: «Создание электронной книги, организация расчетов». 19

Практическая работа: «Организация расчетов с массивами и использованием функций» 24

Практическая работа «Построение графиков, поверхностей и диаграмм». 28

Практическая работа Вычисления в электронных таблицах с помощью встроенных функций Excel 32

Практическая работа «Применение текстовых и календарных функций». 36

Практическая работа Оценка рентабельности рекламной компании фирмы. 41

Практическая работа Составление сметы расчет стоимости заказов с помощью электронных таблиц MS Ехсеl 45

Практическая работа Создание и технология работы с базами данных в MS Excel. Создание сводных таблиц. 55

Практическая работа Создание штатного расписания с использованием функций подбора параметра 65

Практическая работа Подбор параметра. Совместное использование функций и формул 69

Практическая работа Составление плана выгодного производства с использованием функции Поиска решения. 71

Практическая работа Решение задач оптимизации (поиск решения). 77

ТЕСТ ДЛЯ САМОПРОВЕРКИ.. 80

СПИСОК ЛИТЕРАТУРЫ... 83

 

 

Пояснительная записка

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

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

Поскольку главной задачей обучения является подготовка профессионально ориентированных пользователей информационных технологий, то важной особенностью практических работ является ориентация на профессиональные задачи в рамках дисциплин ИТ. В основу обучения положен принцип, который заключается в следующем: информация, с которой работает обучающийся на ПК с помощью того или иного программного средства, связана с профессией. Так, изучение даже инструментальных программных средств, предназначенных для обработки информации любого содержания, таких как текстовой редактор Word, электронные таблицы Excel, СУБД Access, осуществляется  посредством рассмотрения задач, отражающих те или иные аспекты профессиональной деятельности, с которыми обучающиеся знакомятся на уроках специальных дисциплин.

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

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

- постановка задачи на содержательном уровне;

- выбор аппаратных и программных средств для реализации документа;

- создание дружественной информационной среды;

- ввод, редактирование и форматирование документа;

- расчет и анализ результатов расчета;

- принятие управленческого решения и его оформление;

- сохранение и рассылка документа.

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

Целями проведения практических занятий являются:

- обобщение, систематизация, углубление, закрепление полученных теоретических знаний по конкретным темам дисциплины «Информационное обеспечение профессиональной деятельности»;

- формирование умений применять полученные знания на практике, реализацию единства интеллектуальной и практической деятельности;

- формирования общих компетенций по дисциплине;

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

В результате изучения дисциплины «Информационное обеспечение в профессиональной деятельности» при выполнении практических работ формируются компетенции:

Общие компетенции:

ОК-1. Понимать сущность и социальную значимость своей будущей профессии, проявлять к ней устойчивый интерес;

ОК-2. Организовывать собственную деятельность, выбирать типовые методы и способы выполнения профессиональных задач, оценивать их эффективность и качество;

ОК-3. Принимать решения в стандартных и нестандартных ситуациях и нести за них ответственность;

ОК-4. Осуществлять поиск и оценку информации, необходимой для эффективного выполнения профессиональных задач, профессионального и личностного развития;

ОК-5. Использовать информационно-коммуникационные технологии в профессиональной деятельности;

ОК-6. Работать в коллективе, эффективно общаться с коллегами, руководством, потребителями;

ОК-7. Брать на себя ответственность за работу членов команды (подчиненных), за результат выполнения заданий;

ОК-8. Самостоятельно определять задачи профессионального и личностного развития, заниматься самообразованием, осознанно планировать повышение квалификации;

ОК-9. Ориентироваться в условиях частой смены технологий профессиональной деятельности. 

Профессиональные компетенции:

ПК-1.3. Производить расчеты технико-экономического обоснования предлагаемого проекта;

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

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

Отчет должен быть составлен по следующей примерной форме:

- дата;

- наименование темы;

- цель работы;

- ответы на вопросы для подготовки к работе;

- содержание работы;

- литература

 

 

Глава 1 Интерфейс программы MS Excel

Запуск Excel

Для запуска программы может быть использована традиционная команда Пуск, Все программы, Microsoft Office, Microsoft Office Excel 2007. В появившемся окне Excel создает новый документ - рабочую книгу, содержащую по умолчанию три пустых рабочих листа (рис.1). 

Строка заголовка

Кнопка Office

Панель быстрого доступа

 

Ярлыки рабочих листов

Область документа

                                                             

Лента

Поле имени

Строка формул

Ярлыки режимов просмотра

Элементы управления масштабом

                      

Рисунок 1 Окно программы с открытым документом Книга 1

Существуют и другие способы запуска программы, например список Недавние документы (Последние элементы) в меню Пуск. При многократном запуске приложения Excel операционная система Windows автоматически поместит ярлык для его вызова в список наиболее часто применяемых приложений. Это позволит начать работу с программой более короткой командой Пуск, Microsoft Office Excel 2007.

Если вы хотите, чтобы ярлык для вызова Excel всегда присутствовал в составе меню Пуск, то подайте следующую команду: Пуск, Все программы, Microsoft Office, Microsoft Office Excel2007. Откройте контекстное меню щелчком правой кнопки по пункту Microsoft Office Excel2007 и выберите в нем соответствующий пункт – Закрепить в меню Пуск. С помощью контекстного меню можно поместить этот ярлык и на панели быстрого запуска, выбрав в контекстном меню команду Добавить на панель быстрого запуска. При использовании различных операционных систем перечисленные команды могут отличаться от приведенных команд, однако эти отличия будут несущественными.

Главное окно Excel 2007

Кнопка Office. Эта кнопка открывает меню Office (рис. 2), в которое входят команды, которые в предыдущих версиях программы располагались в меню Файл.

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

Рисунок 2 Меню кнопки Office

Лента. Инструментальная лента представляет собой линейку вкладок: Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид. Каждая вкладка содержит набор управляющих функций, связанных друг с другом тематически. Принципы работы ленты аналогичны принципам работы с ней в Word 2007.

Поле имени. Это поле (рис.1) отображает адрес активной ячейки таблицы.

Рисунок 3 Панель быстрого доступа

Строка формул служит для введения и редактирования данных и формул.

Область документа содержит электронную таблицу.

Рисунок 4 Контекстное меню для работы с рабочими листами

Ярлыки рабочих листов. Количество рабочих листов, выведенных на экран, и их характеристики могут быть изменены с помощью контекстного меню (рис.4). Табличное пространство рабочего листа состоит из строк и столбцов, Столбцы озаглавлены латинскими прописными латинскими буквами или двухбуквенными комбинациями. Всего рабочий лист содержит 256 столбцов, пронумерованных от A до IV. Строки последовательно нумеруются числами от 1 до 65356. Всего рабочий лист содержит 16731136 ячеек.

Ярлыки режимов просмотра служат для изменения вида рабочего листа таблицы.

Элементы управления масштабом позволяют изменить объем листа, выводимого на экран.

Глава 2 Ввод и редактирование данных

Ввод данных

В ячейки электронных таблиц можно вводить два типа данных:

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

Формула - выражение из чисел, ссылок на ячейки, функций и математических операторов.

Ввод чисел. Для ввода числового значения надо установить курсор в требуемую ячейку и напечатать число. Числа могут включать цифры от 0 до 9 и следующие специальные символы:   + -., / р. Е е. Запятая в числе вводится как десятичный разделитель.

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

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

Заполнение соседних ячеек

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

Таблица 1

Тип данных	Исходные значения	Получающиеся значения
Числа	10,15	20,25,30,35,…
Месяцы и даты	Янв	Фев, Мар, Апр, Май,…
	Пн	Вт, Ср, Чт, Пт, Сб,...
	25.01.04	26.01.04, 27.01.04,…
Порядковые	Цех 1	Цех 2, Цех 3,...

 

Команды редактирования

Редактирование ячеек.

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

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

- двойным щелчком мыши по ячейке;

- щелчком мыши в строке формул (строка формул указана на рисунке стрелочкой)

- при нахождении курсора на той же ячейке, которую надо редактировать;

- нажатием клавиши F2 при нахождении курсора на редактируемой ячейке.

Перемещение курсора, выбор и редактирование текста внутри ячейки осуществляется также как и в других случаях работы с текстом в среде Windows.

Удаление ячеек

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

- Выделить строки, столбцы, блок ячеек, которые требуется удалить

- Ввести команду Правка Удалить

Очистка ячеек.

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

- Выделите ячейки, содержимое которых надо удалить.

- Ввести команду Правка Очистить или нажать клавишу Delete на клавиатуре.

Форматирование ячеек

В Excel существуют широкие возможности автоматического форматирования ячеек. Для этого нужно задать формат ячеек командой Формат Ячейки. Диалоговое окно содержит Вкладки: Число, Выравнивание, Шрифт, Граница, Вид, Защита.

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

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

Вкладка Шрифт задает особенности оформления текста: шрифт, начертание, размер, цвет и т.д.

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

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

Вкладка Защита позволяет включать и выключать защиту ячеек от изменения.

Таблица 2

+	Сложение
-	Вычитание (или знак отрицательного числа, который ставится перед значением)
/	Деление
*	Умножение
%	Процент (Знак ставится после значения)
^	Возведение в степень

Например, формула =20^2*15% возводит число 20 в степень 2 и умножает результат на 0.15; полученный результат равняется 60.

Операторы сравнения

Результатом сравнения двух значений является логическое значение TRUE (Истина) или FALSE (Ложь), Таблица3

Таблица 3

=	равенство		>	больше чем
<	меньше чем		<>	не равно
<=	меньше или равно		>=	больше или равно

Например, формула =А 1<50 будет принимать значение TRUE, если в ячейке А1 содержится значение меньше 50. В противном случае результатом будет значение FALSE

Текстовый оператор

Текстовый оператор & (конкатенация) объединяет два или более текстовых значения в одно комбинированное. Несколько текстовых значений превращаются в одну текстовую строку.

Например, если ячейка A1 содержит текст “Первое полугодие”, то формула =“Итоги продаж за ”&А1 создаст текстовое значение “Итоги продаж за Первое полугодие”.

Порядок выполнения действий

Если в одной формуле имеется несколько операторов, Excel выполняет их в последовательности, указанной в Таблице 4.

Таблица 4

-	знак отрицательного числа
%	процент
^	возведение в степень
* и /	умножение и деление
+и-	сложение и вычитание
&	объединение текста
= <> <= >= <>	все операции сравнения

 

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

Рисунок 7 Вставка диаграмм

В результате выбора программа автоматически выведет на экран контекстные инструментальные ленты Работа с диаграммами: Конструктор, Макет, Формат.

По умолчанию имя диаграммы будет совпадать с именем выделенного столбца. Это имя можно изменить непосредственно в поле имени диаграммы. Первоначально диаграмма размещается на том же листе, что и таблица. Изменить размещение диаграммы можно с помощью диалогового окна Перемещение диаграммы (рис.8). Это окно раскрывается командой Конструктор, Расположение, Переместить диаграмму.

Рисунок 8 Перемещение диаграммы

При необходимости можно изменить тип диаграммы, подав команду Конструктор, Тип Изменить тип диаграммы.

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

Рисунок 9 Изменение исходных данных

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

Таблица 5

Объекты	Параметры	Форматирование
Заголовок	Шрифт (тип, размер, начертание…) Положение	Команда Макет (главное меню), Название диаграммы
Оси	Шрифт Вид (тип линии, цвет, толщина…) Метка на оси Шкала Формат числа Выравнивание (ориентация текста)	Команда Макет (главное меню), Название осей
Легенда	Вид Шрифт Размещение	Команда Макет (главное меню) Контекстное меню: Формат легенды, Выбрать данные
Ряды данных	Вид Исходные данные Тип диаграммы	Команда Макет (главное меню), Подписи данных Контекстное меню: Добавить подписи данных
Область построения	Вид	Контекстное меню: Формат области диаграммы
Область диаграммы	Тип диаграммы Исходные данные Параметры диаграммы Размещение	Команда Макет (главное меню Контекстное меню: Формат области диаграммы, Переместить диаграмму

Практические работы

Время выполнения – 2 часа

Цель занятия: Изучение информационной технологии организации расчетов в таблицах Ms Excel. Использование относительной и абсолютной адресации (при работе с константами) в расчетах.

Вопросы для подготовки к работе:

1. Понятия: «величина»,  «формула», правила ввода данных и формул.

2. Определение типа данных в ячейках.

3. Редактирование и форматирование данных.

4. Применение метода автозаполнения.

5. Понятие абсолютной и относительной адресации.

Литература: Теоретическая часть методических указаний (Глава 1, 2, 3),

[1.стр. с.235-241]

Порядок работы:

Здание 1. Создать таблицу расчета рентабельности продукции.

1. Запустите редактор электронных таблиц MS Excel и создайте новую электронную книгу. Введите заголовок таблицы, используя объединение ячеек: вкладка Главная панель Выравнивание. Введите исходные данные представленные на рисунке 10:

Рисунок 10

 

2. При вводе номеров в колонку «А» используйте прием автозаполнения ряда чисел. Для этого наберите два первых числа ряда, выделите их мышкой и подведите курсор к правому нижнему углу выделенных ячеек до изменения вида курсора на черный крестик. Прихватите мышью маркер и протяните его вниз до нужного значения.

3. Выделите цветом ячейку со значением константы. Для удобства и формирования навыков работы с абсолютным видом адресации, рекомендуется окрашивать ячейку другим цветом, что будет служить напоминанием о том, что следует устанавливать абсолютную адресацию (набором символа $ с клавиатуры или нажатием клавиши F4).

4. Произведите расчеты во всех строках таблицы по формулам:

Выпуск продукции = Количество выпущенных изделий × Отпускная цена одного изделия, в ячейку С7 введите формулу = С5*$E$2 (ячейка Е2 задана в виде абсолютной адресации); Себестоимость выпускаемой продукции = Количество выпущенных изделий ×Себестоимость одного изделия; Прибыль от реализации продукции = Выпуск продукции – Себестоимость выпускаемой продукции; Рентабельность продукции = Прибыль от реализации продукции/Себестоимость выпускаемой продукции;

5. На строку расчета рентабельности продукции наложите Процентный формат чисел. Остальные расчеты производите в Денежном формате.

6. Формулу из колонки «С» скопируйте автокопированием (за маркер заполнения) вправо по строке в колонки «D» и «Е».

7. Переименуйте лист электронной книги, присвоив ему имя «Расчет рентабельности».

8. Выполните текущее сохранение файла под именем ПР «Расчеты в MS Excel» в своей папке. 

Задание 2: Создать таблицу расчета сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.

1.

Рисунок 11

На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию. Исходные данные представлены на рисунке 11:

 

2. Введите значения констант и исходные данные – столбец Оклад заполните произвольно. Форматы данных (денежный или процентный) задайте по образцу задания.

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

Подоходный налог = (Оклад - Необлагаемый налогом доход)* % подоходного налога, В ячейку D10 введите формулу = (С10 - $C$3)*$C$4; Отчисления в благотворительный фонд = Оклад *% отчисления в благотворительный фонд, в ячейку Е10 введите формулу = C10*$C$5;

Всего удержано = Подоходный налог + Отчисления в благотворительный фонд, в ячейку F10 введите формулу = D10+E10;

К выдаче = Оклад – Всего удержано, в ячейку G10 введите формулу = C10-F10.

4. Рассчитайте итоговую строку, используя функцию – Автосумма (вкладка Формулы).

5. Переименуйте лист электронной книги, присвоив ему имя «Доход сотрудников». Сохраните файл в своей папке.

Задание 3

Задание 4

1. Создайте таблицу по образцу (указать не менее 5 регионов), введите произвольные текстовые данные в столбец Регион, и числовые в столбцы: Театры, Стадионы. Рассчитайте столбцы Театры %, Стадионы %, итоговый столбец и строку.

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

3. Переименуйте лист электронной книги, присвоив ему имя Задание 4.

Задание 5

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

ОБРАЗЕЦ ВЫПОЛНЕНИЯ ЗАДАНИЙ

Здание 1.

 

Задание 2.

Здание 3.

 

КРИТЕРИИ ОЦЕНКИ

Все задания выполняются строго по порядку.

За каждое задание студент получает 1 балл.

Время выполнения – 2 часа

Цель занятия.  Изучение информационной технологии организации расчетов с использованием массивов, встроенных функций.  

Вопросы для подготовки к работе:

1. Редактирование и форматирование данных.

2. Применение метода автозаполнения.

3. Определение типа данных в ячейках.

4. Арифметические операторы.

5. Понятие абсолютной и относительной адресации.

6. Алгоритм работы с мастером функций: сумма, среднее значение.

7. Понятие абсолютной и относительной адресации.

8. Понятия: процентная ставка, период начисления

Литература: Теоретическая часть методических указаний, [ 1.стр. с.241-248]

Порядок работы:

Задание 1. Выполните вычисления по следующим формулам:

A= , B= , C=  

1. Откройте созданную ранее электронную книгу, присвойте имя листу

2. Введите название таблицы и поясняющий текст в ячейкиA2, B2, C2.

3. Введите в ячейки A3, B3 и C3 конкретные значения переменных, например 1,2, 3, 1,5, присвойте этим ячейкам соответственно имена X, Y, Z. Для присвоения имен ячейкам используйте команду Формулы/Присвоить имя.

4. В ячейки A5, A6 и A7 введите поясняющий текст как на рисунке, а в ячейки B5, B6 и B7 соответствующие формулы. Для вычисления первого значения можно ввести формулу =4+3*X+2*X^2+X^3. Предложенные формулы используют в качестве операндов, созданные имена ячеек X, Y. Z, что делает их похожими на соответствующие математически формулы. При необходимости, в формулах также можно использовать и ссылки на ячейки рабочей таблицы. В этом случае нужная формула имела бы вид = (A3+2)*A3+3)*A3+4.

Вид электронной таблицы приведен на рисунке 13.                      Рисунок 13

5. Введите соответствующие формулы в ячейке B6, В7.

6. Сохраните результат работы

Задание 2. Выполните вычисления с  одномерными массивами.

1. На листе 2 запишите формулу для вычисления произведения сумм двух одномерных массивов A и B, т.е. где a_i и b_i соответствующие элементы массивов, а n – их размерность.

2. Вид электронной таблицы приведен на рисунке 14. Конкретные данные, например, A={1,5; 1,23; 1,65; 2,44; 1,44} и B={2,11; 3,12; 2,14; 2,33; 3,12}введите соответственно в ячейки A2:E2 второй и A3:E3 третьей строки листа рабочей таблицы.

3.  В ячейку A5 введите формулу: =СУММ(A2:E2)*СУММ(A3:E3). Если диапазону A2:E2 присвоить имя А, а диапазону А3:Е3 – имя В, то можно применить формулу: =СУММ(A)*СУММ(В).                                                        

Рисунок 14

4. Для вычисления максимального значения массива будем использовать формулы: y _i =a_i / max (b_i), где i=1, 2,…, n, a_i и b_i элементы соответствующих массивов, а n – их размерность.

5. Конкретные данные { a_i}, i=1,2,…,5; { b_i}, i=1,2,…,5, введите соответственно в ячейки диапазонов A7:E7 и A8:E8 рабочей таблицы.

6. В ячейку A9 введите формулу: =A7/МАКС($A$8:$E$8) и с помощью маркера автозаполнения скопируйте ее в ячейки B9:F9. Во втором операнде использована абсолютная ссылка, поэтому на новое местоположение будет настраиваться только первый операнд.

Задание 3. Вычислите сумму положительных чисел массива, количество отрицательных чисел в массиве.

1. На листе 3 задайте произвольный массив чисел. Произвольные данные  введем, например, соответственно в ячейки A2:D6 листа рабочей таблицы.

2. Для вычисления суммы положительных чисел, в ячейку F4 введите формулу: =СУММЕСЛИ(A2:D6;”>0”; A2:D6);

3. Для вычисления количества отрицательных в ячейку F5 введите формулу: =СЧЕТЕСЛИ(A2:D6;”<0”).

Задание 4. Вычислите сумму чисел массива больших заданного в ячейке числа.

1. На листе 4 заполните произвольный диапазон любыми числами (не менее 10 значений).

2. Конкретные данные   введем, например, соответственно в ячейки A2:E2 листа рабочей таблицы. В ячейке А1 запишите произвольное число, а в ячейку A4 введите формулу: =СУММЕСЛИ(A2:E2;”>”&A1; A2:E2).

Задание 5. Вычислите среднее арифметическое положительных чисел и среднее арифметическое абсолютных величин отрицательных чисел в этом массиве.

1. На листе задайте произвольный массив чисел (не менее 10 значений).

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

3. Для решения задания используйте функции СУММЕСЛИ, СЧЕТЕСЛИ и ABS.

ОБРАЗЕЦ ВЫПОЛНЕНИЯ ЗАДАНИЙ

Задание 1.

 

Задание 2.

Задание 3.

 

КРИТЕРИИ ОЦЕНКИ

Все задания выполняются строго по порядку.

За каждое задание студент получает 1 балл.

Время выполнения – 2 час

Цель занятия. Изучение информационной технологии представления данных в виде диаграмм в MS Excel.. Формирование навыков решения задач в MS Excel.

Вопросы для подготовки к работе:

1. Правила ввода данных и формул.

2. Определение типа данных в ячейках.

3. Редактирование и форматирование диаграмм.

4. Применение метода автозаполнения.

5. Понятие абсолютной и относительной адресации.

6. Понятия: процентов, удельного веса.

Литература: Теоретическая часть методических указаний (Глава 4)   [1.стр. с.262-267]

Порядок работы:

Задание 1. Составить таблицу расчета доходов фирмы в абсолютном и процентном отношении и диаграмму роста доходов

1. Открыть электронную книгу, на новом листе создать таблицу с данными по образцу на основе данных о доходах фирмы. Таблица 7.

Таблица 7

2. Составить таблицу расчета доходов фирмы: определить тип, размер и стиль шрифтов для заголовков строк и столбцов: TimesNewRomanCyr, размер 12, стиль полужирный; для остального текста - TimesNewRomanCyr, размер 10, стиль обычный;

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

D3=(Ci-C$3)/C$3 где Сi – адрес ячейки i-го месяца графы Уровень доходов фирмы в 2013 году, С$3 – абсолютный адрес ячейки Уровень доходов фирмы за январь 2013 года;

4. Вычислить суммарный уровень доходов фирмы за 2012 и 2013 годы, результаты поместить в последней строке второго и третьего столбца соответственно;

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

6. Построить диаграмму зависимости уровня доходов фирмы за 2013 и 2012 годы по месяцам в виде гистограммы. Выделить нужный диапазон, на вкладке Вставка выбрать тип диаграммы Гистограмма.

7. Построить диаграмму зависимости уровня доходов фирмы в процентном отношении в виде линейного графика;

8. Рассмотреть другие типы диаграмм, освоить редактирование элементов диаграмм.

Задание 2. Построить график функции y= sinx. Значение аргумента х выбрать в пределах от –6 до 6 с шагом 0,5.

1. Построить таблицу следующего вида

X	-6,0	-5,5	-5,0	...
Y				…

2. Заполнить значениями строку Х как арифметическую прогрессию с разностью 0,5, заполнить диапазон до значения х=6.

3. В строку Y вставить формулу =Sin(Х) и протянуть до конца значений X.

4. Выделить построенный диапазон и построить диаграмму в виде графика.

Задание 3. Составьте электронную таблицу и создать график квадратичной функции , считая a, b и с постоянными параметрами (значения выбрать произвольно),  на интервале [-5;5] с шагом 0,2.

Задание 4. Составьте электронную таблицу и создать график функции , считая a, b и с постоянными параметрами (значения выбрать произвольно), на интервале [ n1; n2] с шагом h=(n2- n1)/30 (значения n1, n2 выбрать произвольно.