  Арктический морской институт имени В.И. Воронина филиал Федерального государственного бюджетного образовательного учреждения ВО «Государственный университет морского и речного флота
имени адмирала с.о. макарова»
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
к проведению практических работ
Дисциплина «Информатика»
Тема “Основы работы в MS Excel 2013”
Разработала преподаватель информатики
Т.А. Ведилина
г. Архангельск
2014
Содержание
Пояснительная записка
|
3
|
1. Оформление таблицы. Условное форматирование. Автосуммирование
|
4
|
2. Автозаполнение ячеек. Вычисления по формулам. Форматирование таблицы
|
7
|
3. Абсолютные и относительные адреса ячеек. Копирование формул. Построение диаграмм
|
12
|
4. Встроенные функции Excel. Логические и статистические функции
|
16
|
Список литературы
|
20
|
Пояснительная записка
Данная разработка содержит методические указания к выполнению практических работ по теме «MS Excel 2013» дисциплины «Информатика» учащимися 2 курса специальностей «техник-судоводитель» и «техник-судомеханик».
Методические указания – это пошаговые инструкции, помогающие обучающимся в выполнении практических работ и закреплении на практике полученных на уроках знаний. Также в работе указаны тема, цель и контрольные вопросы по каждой работе.
В результате выполнения представленных практических работ у будущих специалистов должны быть сформированы устойчивые навыки решения различных задач посредством Excel, которые могут быть востребованы в их дальнейшей профессиональной деятельности.
Практическая работа 1
Оформление таблицы. Условное форматирование.
Автосуммирование
Цель работы: Изучить основные элементы окна Excel 2013; научиться создавать и оформлять электронную таблицу, выполнять автосуммирование и применять условное форматирование к ячейкам таблицы.
Выполнение работы:
1. Задание. Заполните таблицу данными, используя автозаполнение; выполните автосуммирование и оформите таблицу по образцу, используя автозаполнение для повторяющихся значений ячеек и условное форматирование:
2. Технология выполнения работы.
2.1 Запустите программу и выберите вариант Пустая книга. Выделите диапазон ячеек A1:E17 и примените к ним границы:
2.2 Выделите диапазон ячеек А1:Е1 и объедините их щелчком по кнопке Объединить и поместить в центре на вкладке Главная. Не снимая выделения, увеличьте размер заголовка, выберите начертание полужирное, измените цвет и введите заголовок.
2.3 С помощью кнопки Объединить и поместить в центре объедините диапазоны А2:А3, В2:В3, С2:С3, D2:E2 и введите в них заголовки столбцов полужирного начертания. Объедините так же ячейки А17:В17.
2.4 Заполните остальные ячейки. Повторяющиеся значения достаточно ввести один раз, а затем выполнить автозаполнение остальных ячеек, протянув вниз с нажатой клавишей мыши маркер автозаполнения: 
2.5 Чтобы текст в ячейках переносился на следующие строки, надо выделить их и щелкнуть кнопку Перенести текст на вкладке Главная. 
2.6 Выделите диапазон ячеек С4:С16 и щелкните кнопку Сумма для вычисления суммы по столбцу С. Сумма появится автоматически.
2.7 В Excel 2013 есть возможность красочно оформить ячейки в зависимости от их содержимого: например, разными цветами выделить ячейки с максимальными, минимальными и промежуточными числовыми значениями и т.п. Для оформления используется условное форматирование.
Выделите числовые значения в столбце С, на вкладке Главная выберите Условное форматирование, в выпадающем списке Цветовые шкалы и примените один из вариантов. Для остальных столбцов с числовыми значениями примените Гистограммы и Наборы значков.
Контрольные вопросы:
Объединение ячеек.
Границы таблицы.
Автозаполнение.
Перенос текста в ячейках.
Автоматическое сложение.
Условное форматирование.
Практическая работа 2
Автозаполнение ячеек с числовыми данными.
Вычисления по формулам. Форматирование таблицы
Цель работы: Изучить возможность автозаполнения ячеек числовыми данными; рассмотреть особенности форматирования таблицы, научиться вводить формулы на языке Excel.
Выполнение работы:
1. Задание. Заполните таблицу данными и оформите по образцу, применяя автозаполнение для номеров строк и объединение ячеек для ввода заголовка.
2. Технология выполнения работы.
2.1 Запустите программу и выберите вариант Пустая книга. Объедините ячейки А1:С1 для ввода заголовка и примените к нему Стиль заголовка на ленте Главная (группа Стили, команда Стили ячеек).
2.2 В ячейку А3 введите цифру 1 и протяните вниз за маркер автозаполнения до ячейки А10. При этом внизу справа появляется кнопка Параметры автозаполнения. В выпадающем меню выберите опцию Заполнить. Список из одинаковых цифр сменится на порядковые номера.
 
При вводе наименований ноутбуков также используйте копирование ячеек с одинаковым или частично совпадающим текстом. Заполните остальные ячейки, обращая внимание на выравнивание содержимого ячеек.
2.3 Выделите любую ячейку получившейся таблицы. На вкладке Главная выберите в группе Стили команду Форматировать как таблицу и выберите один из стилей.
 
Появится окно с диапазоном ячеек, который отражает весь наш диапазон ячеек таблицы, включая заголовок. Заголовок исключите из диапазона, выделив с нажатой клавишей мыши только ячейки с А2 по С10. Нажмите Ок (можно было сразу выделить нужный диапазон ячеек до применения команды форматирования таблицы.)
При этом не только изменится внешний вид таблицы, но и появится возможность работать с таблицей особенным образом, т.к. она станет динамической и будет автоматически отслеживать изменение своих размеров.
Введите в первый столбец следующий номер 9. При этом в таблицу автоматически добавится новая строка.
Введите название еще одного столбца. Он также добавится в таблицу. Введите формулу в ячейку D3: начните со знака «равно», затем щелкните мышью по ячейке С3, нажмите на клавиатуре знак умножения и допишите формулу. Обратите внимание, что в результате указания в формуле ячейки D3 ее адрес автоматически преобразовался в [@Цена]. В нем отображается название столбца, а символ «собачка» указывает на то, что данные берутся из той же строки.
После ввода формулы и нажатии на Enter весь столбец автоматически заполнится вычисленными по формуле значениями. Мы создали вычисляемый столбец.
Оформление таблицы (например: цветовую схему, чередующиеся строки, чередующиеся столбцы) можно изменять на вкладке Конструктор, которая появляется при установке курсора внутри таблицы.
Контрольные вопросы:
Автозаполнение ячеек порядковыми номерами.
Стили заголовков.
Правила ввода формул в ячейки таблицы.
Форматирование таблицы: применение различного оформления, автоматическое добавление строк и столбцов, создание вычисляемых столбцов, особенности адресов ячеек в формулах таблицы.
Практическая работа 3
Абсолютные и относительные адреса ячеек. Копирование формул. Построение диаграмм
Цель работы: Изучить возможность использования в формулах Excel абсолютных и относительных адресов ячеек; научиться копировать формулы протягиванием с абсолютными и относительными адресами, строить диаграммы.
Выполнение работы:
1. Задание. Заполните таблицу данными и оформите по образцу, применяя автозаполнение для названий месяцев, объединение ячеек, форматирование таблицы. Последний столбец рассчитайте по формулам. Добавьте строку итогов. Постройте диаграмму.
2. Технология выполнения работы.
2.1 Запустите программу и выберите вариант Пустая книга. Объедините ячейки А1:С1 для ввода заголовка.
2.2 Выделите диапазон ячеек А2:С2 и выполните заливку на вкладке Главная в группе Шрифт, выбрав команду Цвет заливки  .
2.3 Выделите диапазон ячеек А3:С6 получившейся таблицы. На вкладке Главная выберите в группе Стили команду Форматировать как таблицу и выберите один из стилей.
2.4 Выделив любую ячейку таблицы, на вкладке Конструктор в группе Параметры стилей таблиц поставьте флажок у команды Строка итогов.
2.5 В ячейку С4 введите формулу для перевода рублей в доллары и нажмите Enter:
Протяните формулу ниже для копирования за маркер автозаполнения. Адрес ячейки В4 – относительный, поэтому при копировании он будет меняться на В5 и В6. Адрес же ячейки В2 в формулах меняться не должен, поэтому его «заморозили» знаками доллара. Такие адреса называются абсолютными. 
Вот так выглядят формулы в столбце С:
Курс доллара постоянно меняется, поэтому достаточно изменить его в ячейке В2, и остальные данные таблицы будут автоматически пересчитаны.
2.6 Диаграммы служат для наглядного представления числовых данных. Для построения диаграммы, показывающей динамику изменения зарплаты в рублях в зависимости от месяца, выделите диапазон ячеек А4:В6 и на вкладке Вставка в группе Диаграммы выберите объемную гистограмму:
При выделении диаграммы появляются дополнительные кнопки справа для работы с диаграммой:
В той же группе команд есть рекомендуемые диаграммы, где программа предлагает наиболее подходящие для нашей таблицы стили диаграмм:
 
Контрольные вопросы:
Абсолютные и относительные адреса ячеек.
Копирование формул.
Строка итогов в таблице.
Построение диаграмм.
Практическая работа 4
Встроенные функции Excel.
Логические и статистические функции
Цель работы: ознакомиться с некоторыми статистическими и логическими функциями Excel; научиться применять их при решении задач.
Выполнение работы:
1. Задание. Заполните таблицу, содержащую следующие данные:
фамилии абитуриентов; оценки по математике и физике; сумму баллов за два экзамена;
вывод значения «Истина», если абитуриент сдал экзамены без «троек», и значения «Ложь» в противном случае;
вывод значения «Истина» при наличии хотя бы одной «пятерки», и значения «Ложь» в противном случае;
вывод результатов о зачислении в институт – «Зачислен», если сумма баллов больше или равна проходному баллу, иначе «Не зачислен»;
число зачисленных в институт с использованием функции СЧЕТЕСЛИ;
минимальное и максимальное число баллов у абитуриентов.
2. Технология выполнения работы.
2.1 Запустите программу и выберите вариант Пустая книга. Заполните таблицу данными по образцу. Оформите заголовки, используя команду Стили ячеек на вкладке Главная. Оформите таблицу границами.
2.2 Вычислите значение ячейки Е4, используя кнопку Автосумма и скопируйте формулу в остальные ячейки столбца (если сделать таблицу динамической командой Форматировать как таблицу, копирование формулы происходит автоматически. К данной таблице эту команду не применяем).
2.3 Формулы в ячейки можно вводить вручную или с помощью Мастера функций. В ячейку F4 введите формулу =И(C4<>3; D4<>3) и скопируйте ее в остальные ячейки (символы «меньше» и «больше», записанные подряд, означают «не равно»). Результатом действия формулы будет значение «Истина», если обе оценки одновременно не являются «тройками», и значение «Ложь» при наличии хотя бы одной оценки «3».
2.4 В ячейку G4 введите формулу =ИЛИ(C4=5; D4=5) и скопируйте ее в остальные ячейки. Результатом действия формулы будет значение «Истина», если за экзамен хотя бы одна оценка «5», и значение «Ложь» в случае отсутствия «пятерок».
2.5 Для ввода формулы в ячейку H4 вызовите окно Вставка функции щелчком по кнопке , выберите функцию ЕСЛИ и заполните поля появившегося окна Аргументы функции.
 
Протяните формулу по всему столбцу.
2.6 В ячейку D19 вставьте статистическую функцию СЧЕТЕСЛИ для подсчета числа зачисленных в институт абитуриентов:
Для ввода диапазона ячеек в первое поле ввода достаточно выделить нужный диапазон мышью.
2.7 Для определения максимального и минимального числа баллов используйте статистические функции МИН и МАКС, указав соответствующие диапазоны ячеек: =МАКС(E4:E17), =МИН(E4:E17).
Контрольные вопросы:
Встроенные функции Excel. Вставка функций.
Логические функции ЕСЛИ, И, ИЛИ.
Статистические функции СЧЕТЕСЛИ, МИН, МАКС.
Список литературы:
Дружинин А.Ю. Excel 2013: пошаговый самоучитель + справочник пользователя / А.Ю. Дружинин, Р.Г. Прокди, В.В. Серогодский; – Спб.: Наука и техника, 2014. – 400 с.
Ресурсы Интернет:
Уокенбах Джон - Excel 2013. Трюки и советы Джона Уокенбаха – 2014, pdf-файл.
http://potapova.ucoz.ru/load/tablicy/tablica_morja_omyvajushhie_territoriju_rossii/6-1-0-39
-
http://www.c3000.ru/price.asp?subpart=186
Видеоуроки: Умные таблицы в Microsoft Excel (Николай Павлов, эксперт по продуктам MS Office), Продвинутый курс по Excel 2013 (автор Екатерина Нечипоренко).
|
