МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ ПО ВЫПОЛНЕНИЮ ПРАКТИЧЕСКИХ РАБОТ В ТАБЛИЧНОМ РЕДАКТОРЕ MS EXCEL ДЛЯ СТУДЕНТОВ 1-ГО КУРСА ПО ДИСЦИПЛИНЕ ИНФОРМАТИКА

Министерство образования и науки Самарской области Министерство имущественных отношений Самарской области Государственное бюджетное профессиональное образовательное учреждение Самарской области «Чапаевский губернский колледж им. О. Колычева» МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ ПО ВЫПОЛНЕНИЮ ПРАКТИЧЕСКИХ РАБОТ В ТАБЛИЧНОМ РЕДАКТОРЕ MS EXCEL ДЛЯ СТУДЕНТОВ 1­ГО КУРСА ПО ДИСЦИПЛИНЕ ИНФОРМАТИКА   специальности 09.02.01 Компьютерные системы и комплексы  44.02.02 Преподавание в начальных классах 54.02.01 Дизайн (по отраслям) в культуре и искусстве  44.02.01 Дошкольное образование  38.02.04 Коммерция (по отраслям) г.о. Чапаевск, 2018 год 2 Публикуется на основании решения  Методического совета  ГБПОУ СОЧГК им. О. Колычева Протокол № 2 от 16.01.2018 г. 3 Составитель:  Суворова   Л.Е.,   преподаватель   специальных   дисциплин образовательной   программы   среднего   профессионального   образования программы   подготовки   специалистов   среднего   звена   ГБПОУ   СОЧГК   им.   О. Колычева; Захарова Е.М.., заместитель директора по учебно­методической Редактор: работе   образовательной   программы   среднего   профессионального   образования программы   подготовки   специалистов   среднего   звена   ГБПОУ   СОЧГК   им.   О. Колычева; Рецензент:  работе, учитель информатики ГБОУ СОШ №22 г.о. Чапаевск Сухобрус О.С., заместитель директора у учебно­воспитательной Методические   рекомендации   по   работе   с   электронными   таблицами  MS  Excel предназначены для студентов  I  курса реализуемых   специальностей колледжа, также   могут   быть   использованы   преподавателями   на   уроках   Информатики. Данное пособие включает рассмотрение интерфейса и практические примеры для работы   с   электронными   таблицами  MS  Excel  2007   и   описание   работы   с основными функциями. Рекомендации   составлены   в   соответствии   с   требованиями   к   результатам обучения   по   рабочей   программе   Информатика,   могут   быть   использованы студентами   и   преподавателями   других   средних   профессиональных   учебных заведений. 4 Содержание Пояснительная записка Первое знакомство Работа с диаграммами Работа с формулами и функциями Сортировка строк по столбцам ВТОРОЙ УРОК Работа со списками Работа с диаграммами Работа с функциями ТРЕТИЙ УРОК Работа с функциями Сортировка Шаблоны ЧЕТВЕРТЫЙ УРОК Фильтрация Работа с диаграммами Вычисления и функции ПЯТЫЙ УРОК Трехмерные ссылки Консолидация данных Работа с диаграммами ШЕСТОЙ УРОК Создание сводной таблицы Функция «ЕСЛИ» Работа с диаграммами СЕДЬМОЙ УРОК Диаграммы Абсолютные и относительные ссылки Использование фильтров ВОСЬМОЙ УРОК Защита нескольких ячеек рабочего листа Защита рабочего листа за исключением нескольких ячеек Проведение анализа документа ДЕВЯТЫЙ УРОК Таблицы подстановок Анализ «что­если» Работа со сценариями 4 5 7 12 14 15 15 17 19 22 22 23 24 26 26 28 29 31 31 32 34 35 35 37 38 39 39 43 46 49 49 51 54 55 55 58 59 Поиск решений ДЕСЯТЫЙ УРОК Базы данных в Excel Список литературы   5 62 65 65 70 ПОЯСНИТЕЛЬНАЯ ЗАПИСКА Методическое   пособие   разработано   в   соответствии   с   рабочей   программой   по общеобразовательной дисциплине Информатика.  Соответствуют требованиям к результатам обучения: метапредметных:  умение   использовать   средства   информационных   и   коммуникационных технологий   (далее   ­   ИКТ)   в   решении   когнитивных,   коммуникативных   и организационных   задач   с   соблюдением   требований   эргономики,   техники безопасности, гигиены, ресурсосбережения, правовых и этических норм, норм информационной безопасности; предметных:  сформированность представлений о компьютерно­математических моделях и необходимости   анализа   соответствия   модели   и   моделируемого   объекта (процесса); о способах хранения и простейшей обработке данных; понятия о базах данных и средствах доступа к ним, умений работать с ними;  владение компьютерными средствами представления и анализа данных;   Использовать   информационно­коммуникационные   технологии   в  Методические рекомендации направлены на формирование общих компетенций: ОК 2. Организовывать собственную деятельность, выбирать типовые методы и способы  выполнения  профессиональных  задач,   оценивать  их эффективность  и качество. ОК   4.   Осуществлять   поиск   и   использование   информации,   необходимой   для эффективного   выполнения   профессиональных   задач,   профессионального   и личностного развития. ОК   5. профессиональной деятельности. Данное пособие рассматривает назначение, структуру и основные   определения табличного   редактора  MS  EXCEL  2007,   его   интерфейс,   а   также   содержит наглядные примеры и пошаговое выполнение практических работ нацеленных на закрепление теоретического материала и навыков работы с редактором.  Данные   рекомендации   могут   использоваться   студентами   при   выполнении самостоятельных и практических работ по дисциплине Информатика, которые обеспечивают формирование у студентов умений самостоятельно и избирательно применять  различные   средства   ИКТ,  пользоваться     комплексными   способами представления   и   обработки   информации,   а   также   изучить   возможности использования ИКТ для профессионального роста. Методическое   пособие   может   использоваться   как   студентами   для самостоятельного   изучения   функций   и   средств   табличного     редактора  MS EXCEL 2007 и при оформлении самостоятельных работ, так и преподавателями при проведении практических занятий по дисциплине Информатика. 6 7 Первое знакомство Запуск Microsoft Excel Для запуска Excel необходимо щелкнуть кнопку Пуск на панели задач в группе меню Программы выбрать пункт Microsoft Excel. При запуске Excel на экране появляется окно с пустой таблицей. При первом запуске этот документ имеет стандартное имя Книга 1, которое указывается в строке заголовка. Использование справочной системы В любой момент времени вы можете получить помощь от справочной системы. Для работы с ней предназначен пункт меню со знаком вопроса. Так же можно воспользоваться горячей клавишей F1. Общие сведения о книгах и листах Microsoft Excel Книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи. Листы служат для организации и анализа данных. Можно вводить и изменять данные одновременно на нескольких листах, а также выполнять вычисления на основе   данных   из   нескольких   листов.   При   создании   диаграммы   ее   можно поместить   на   лист   с   соответствующими   данными   или   на   отдельный   лист диаграммы. Вкладки листов. Имена листов отображаются на вкладках в нижней части окна книги. Для перехода с одного листа на другой следует выбрать соответствующую вкладку. Элементы экрана Строка заголовка находится в верхней части экрана и имеет обычный вид для программ,   работающих   под   управлением   Windows,   дополнительно   здесь установлена кнопка Office, которая предназначена для вывода списка возможных действий с документом, включая открытие, сохранение и печать. Также на строке заголовка есть панель быстрого доступа. Рисунок 1. Строка заголовка Строка меню. Под строкой заголовка находится строка меню, в которой перечислены группы команд:   Данные, Рецензирование, Вид. Каждая   группа   объединяет   набор   команд,   имеющих   общую   функциональную направленность.   Разметка   страницы,   Формулы,   Главная,   Вставка, 8 Рисунок 2. Строка меню Перемещение и прокрутка внутри листа Для   перехода   между   ячейками   листа   используется   мышь   или   клавиши   со стрелками. При переходе на ячейку она становится активной. Для перехода в другую   область   листа   используются   полосы   прокрутки.   Полоса   прокрутки   ­ полосы вдоль правой и нижней сторон окна. Чтобы прокрутить содержимое окна, перетаскивайте бегунок или нажимайте кнопки со стрелками. Переход на другой лист книги Укажите ярлычок листа, содержащего нужные данные. Если ярлычок нужного листа не виден, то для его отображения используйте кнопки прокрутки листов. Затем укажите необходимый ярлычок. Рисунок 3. Панель перехода на другие листы  Нажимаем кнопку Office и выбираем Создать.  Чтобы создать новую пустую книгу, выберите вкладку Пустые и последние и Создание, закрытие и сохранение файлов  Создание книги 1. 2. дважды щелкните Новая книга.  Открытие книги 1. Нажимаем кнопку Office и нажимаем Открыть или просто нажимаем кнопку Открыть на панели быстрого доступа. 2.   В   поле   Папка   укажите   путь,   где   находится   книга,   которую   требуется открыть. Также для удобства, при нажатии на кнопку Office появляется окошко, где показаны последние ранее открывавшиеся документы. 3. Нажмите кнопку Открыть. Сохранение книги При первом сохранении книги указывается имя файла и место, куда он будет помещен. Во время каждого последующего сохранения книга будет обновляться в соответствии с внесенными в нее изменениями. Для этого можно просто нажать кнопку Сохранить  Сохранение новой книги 1. вид сохранения документа (например, Книга Excel).   Выберите команду кнопка Office /Сохранить как и выберите необходимый 9 2. В   окне   Сохранение   документа   укажите   диск   и   папку,   в   которую   будет помещена книга. 3. В поле Имя файла введите имя книги. 4. Нажмите кнопку Сохранить. Закрытие книги Выберите команду Кнопка Office/ Закрыть. Ввод и форматирование данных Ввод данных 1. Укажите ячейку, в которую необходимо ввести данные. 2. Наберите данные и нажмите клавишу ENTER. Изменение содержимого ячейки 1. Дважды щелкните ячейку, содержимое которой необходимо изменить. 2. Измените содержимое ячейки. 3. Для сохранения изменений нажмите клавишу ENTER. Для отмены изменений нажмите клавишу ESC. Очистка ячейки Выделите ячейки, строки или столбцы, которые следует очистить.  В меню Главная в подменю Редактирование выберите команду Очистить а затем — Все, Содержимое, Форматы или Примечания Работа с диаграммами Создание диаграммы Диаграмму   можно   создать   на   отдельном   листе   или   поместить   в   качестве внедренного объекта на лист с данными. Создадим таблицу: Рисунок 4. Таблица Далее будет подробно описываться, как заполнять поля таблицы. 1. Поместите курсор мыши в рабочую область программы. Наберите в ячейке C3 имя поля: квартал 1. Выделите ячейку С3, далее нажмите на черный квадратик, находящийся в правом нижнем углу ячейки, затем, не отпуская, кнопку мыши перетащите курсор до ячейки F3. У вас должно получиться следующее: Рисунок 5. Заполнение заголовков столбцов 2. В   ячейки   B4   и   B5   введите   имена   полей:   плановые   и   фактические соответственно Должно получиться так: 10 3. В диапазон ячеек С4:Б5 ввести числовые данные. Рисунок 6. Заполнение заголовков строк Рисунок 7. Заполнение таблицы данными   введенные   4. Выделите ячейки B3:F5 и установите шрифт Times New Roman 12 пунктов, а затем выровняйте по центру. В итоге мы получим исходную таблицу. Далее выполним команду: 1. Выделить данные, Гистограмма/Гистограмма с группировкой.   Вставка/(Диаграммы) далее   Получится диаграмма:  Рисунок 8. Выбор типа диаграммы 11 Рисунок 9. Готовая диаграмма Диаграмма связана с данными, на основе которых она создана, и обновляется автоматически при изменении данных. Microsoft   Excel   2007   создает   значения   оси   на   основе   указанных   данных.   В приведенном   примере   значения   оси   изменяются   от   0   до   100000,   что соответствует значениям ячеек диапазона на листе. В качестве имен оси категорий Excel 2007 использует заголовки столбцов или строк   данных.   В   приведенном   примере   в   качестве   имен   оси   категорий отображаются   заголовки   строк,   соответствующие   первому   кварталу,   второму кварталу и т.д. Маркеры данных одного цвета представляют один ряд данных. Каждый маркер соответствует одному значению данных листа. Область диаграммы. Вся диаграмма и ее элементы. Область построения. На плоских диаграммах — ограниченная координатными осями   область,   содержащая   все   ряды   данных.   На   объемных   диаграммах   — ограниченная   координатными   осями   область,   содержащая   все   ряды   данных, имена категорий, подписи делений и названия осей. Внедренная диаграмма рассматривается как графический объект и сохраняется в качестве части листа, на котором она создана. Внедренные диаграммы следует использовать в случаях,  когда требуется  отобразить или напечатать  одну или несколько диаграмм вместе с данными листа. Лист   диаграммы   —   это   отдельный   лист   в   книге,   имеющий   собственное   имя. Листы диаграмм следует использовать в случаях, когда требуется просмотреть или изменить большие или сложные диаграммы отдельно от данных, или когда требуется сохранить пространство экрана для работы с листом. Изменение типа диаграммы. 1. Для того чтобы изменить тип диаграммы, щелкните на диаграмму. 2. Выполните команду Конструктор/(Тип) Изменить тип диаграммы/Объемная коническая. 12 Рисунок 10. Изменение типа диаграммы Выбор   цвета,   границ   и   заливки   допускается   в   маркерах   данных,   в   области диаграммы, в области построения и в других элементах диаграммы.  Следующая процедура используется для изменения цветов, узора, ширины линии или типа рамки для маркеров данных, области диаграммы, области построения, сетки, осей и подписей делений на плоских и объемных диаграммах: 1. Щелкните левой кнопкой мыши на область диаграммы. 2. При необходимости заливки фигуры выберите Формат/(Стили фигур)Заливка фигуры и укажите нужный вид во вкладках Градиентная или Текстура.(В нашем примере Формат/(Стили фигур)Заливка фигуры/Желтый цвет 3. При   необходимости   изменения   вида   диаграммы   пользуйтесь   вкладками Конструктор, Макет или Формат. 13 Рисунок 11. Измененные элементы диаграммы Использование рисунков в диаграммах. Следующая   процедура   используется   для   добавления   рисунка   —   например, точечного   —   к   определенным   типам   маркеров   данных,   области   диаграммы, области построения или легенде на плоских и объемных диаграммах.  Рисунок 12. Использование рисунка  в диаграммах Для того, чтобы у вас получилось также, выполните следующие команды: 1. Щелкните левой кнопкой мыши на область диаграммы. 2. Выполните команду Формат/(Стили фигур)Заливка фигуры/Рисунок. 3. В появившемся окне Вставка рисунка выберите диск или папку, где находится нужный рисунок, а затем дважды щелкните его. 4. Далее   вы   полните   команду   Макет/Текущий   фрагмент.   Выберите   Область построения. Далее Формат выделенного фрагмента/Нет заливки. 14 Рисунок 13. Изменение формата диаграммы Работа с формулами и функциями Вставка формул. В Microsoft Excel 2007 можно использовать формулы, которые интегрированы в систему.   Для   этого   достаточно   выполнить   следующую   команду Формулы/Библиотека функций и выбрать нужную вам функцию. Также можно вставлять функцию вручную. Далее этот способ будет описан в примере. Ввод формулы, содержащей функцию. 1. Создайте таблицу для подсчета одной единицы товара и общей стоимости товаров: Рисунок 14. Макет таблицы 2. Щелкните   дважды   левой   кнопкой   мыши   на   ячейку   Е4.Нажмите   знак равенства(=),затем нажмите левой кнопки мыши на ячейку C4, потом поставьте знак «звездочку»(*),а после левой кнопкой нажмите на ячейку D4. 3. Нажмите ENTER. Рисунок 15. Ввод формулы в ячейку 4. Выделите ячейку  E4,  далее нажмите на черный квадратик,  находящийся  в правом нижнем углу ячейки, затем, не отпуская, кнопку мыши перетащите курсор до ячейки E7.  15 Рисунок 16. Выделение данных столбца 5. Выделите   ячейку   E8. функций/Автосумма.    Далее   выполняем:   Формулы/Библиотека 6. Нажмите ENTER. Рисунок 17. Выбор формулы Автосумма Рисунок 18. Правильный результат Если получилась такая таблица, то вы все выполнили верно. Сортировка строк по столбцам Лучше   всего,   если   сортируемый   список   будет   иметь   заголовки   столбцов. Создайте таблицу данного типа: 16 Рисунок 19. Таблица для разработки 1. Выделите ячейки, которые требуют сортировки. В нашем случае ячейки A2­ A6. 2. 3. Укажите в выплывшем окне Сортировка в полях: Столбец Сортировать по Ф.И.О., Сортировка Значение и Порядок От А до Я.   Выполните команду Данные/Сортировка и фильтр/Сортировка. 4. Нажмите кнопку OK.  Рисунок 20. Процесс сортировки данных Рисунок 21. Конечный результат 17 Второй урок Работа со списками Понятие списка В Microsoft Excel 2007 под списками подразумеваются таблицы со связанными между собой данными. Имеется набор функций облегчающих обработку и анализ данных в системе. Списком может быть справочник, ведомость и т.д. Список ­ это   база   данных,   т.к.   воспринимается   программой   в   виде   определенной структуры. Это дает возможность доступа к таким операциям как сортировка или   поиск   информации.   Программа   формирует   списки   из   отдельных   частей таблицы, при этом учитываются основные элементы списка. Создадим документ следующего вида:   Создайте   новую   книгу.   Для   этого   выполните   команду   Кнопка Рисунок 22. Создание таблицы  Выделите ячейки А1­Е1 и установите шрифт Times New Roman 14 пунктов, а 1. Office/Создать/Новая книга и нажмите Создать. 2. Поместите курсор мыши в рабочую область программы. Наберите в ячейках А1­ Е1 имена полей: фамилия, имя, отчество, адрес, телефон. 3. затем выровняйте по центру.  Заполните все остальные ячейки шрифтом Arial 10 пунктов. 4. 5.  Установите внутренние и внешние границы для таблицы. Для этого выделите таблицу   и   выберите   команду   Главная/(ячейки)Формат/Формат   ячеек.   Затем выберите вкладку Граница и щелкните на кнопки: Внешние и Внутренние. 6. Отрегулируйте   ширину   столбцов   по   содержимому.   Для   этого   выделите таблицу   и   выполните   команду   Главная/(ячейки)Формат/Автоподбор   ширины столбца. Таким   образом,   строки   списка   становятся   записями,   а   заголовки   столбцов   ­ именами базы данных. Нужно помнить, что пустая строка указывает на окончание списка, поэтому не следует оставлять пустых строк между заголовками столбцов. Имена   полей   должны   располагаться   в   верхней   части   списка   и   находиться   в отдельных ячейках. Сортировка. 18   Выполните команду Данные/(сортировка и фильтр)Сортировка. После чего При   вводе   записей   можно   не   обращать   внимание   на   их   порядок,   т.   к.   в дальнейшем есть возможность сортировать списки. 1. Для того чтобы программа Microsoft Excel 2007 обнаружила список нужно щелкнуть на любой ячейке, относящейся к списку. 2. программа сама выделит таблицу. 3. В   окне   Сортировка   выберите   сортировать   по   Фамилия,   затем Сортировка/Значения,   в   последнюю   очередь   Порядок/От   А   до   Я.   Нажмите кнопку ОК. Получим документ следующего вида: Рисунок 23. Таблица после преобразований В Microsoft Excel 2007 можно производить многоуровневую сортировку, когда совпадают одно или несколько полей. Сортировка данных. Создадим документ следующего вида: Рисунок 24. Макет таблицы Создайте таблицу в соответствии с образцом.  Выполните сортировку по трем ключам в следующем порядке (по убыванию): музеи театры, магазины. Для этого: 1. Выделите всю таблицу.   Выполните   команду   Данные/(сортировка   и   фильтр)Сортировка.   В 2. выпадающем   списке   Сортировать   по   выбрать   "Музеи",   установить Сортировка/Значение   и   Порядок/По   убыванию.   Далее   Добавить   уровень   в выпадающих списках Затем по выбрать соответственно "Театры" и "Магазины". Установить переключатели в позиции По убыванию. Нажмите кнопку OK. В результате у вас получиться таблица следующего вида: 19 Рисунок 25. Готовая диаграмма Работа с диаграммами Откройте   файл   Сортировка   данных.   Попытаемся   представить   в   графическом виде данные по количеству магазинов в округах. Создадим документ следующего вида: Рисунок 26. Построение круговой диаграммы Выделите два столбца таблицы, содержащие названия округов и данные по числу магазинов.   Для   выделения   несмежных   ячеек,   удерживайте   нажатой   клавишу CTRL. Включите в выделение заголовки. Выполните команду Вставка/(диаграммы) Круговая диаграмма. Выберите тип диаграммы ­ круговую объемную.