Учебно-методическое пособие "КОДИРОВАНИЕ И ОБРАБОТКА ЧИСЛОВОЙ ИНФОРМАЦИИ. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ MS EXCEL"

ГОСУДАРСТВЕННОЕ ПРОФЕССИОНАЛЬНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ «ТУЛЬСКИЙ КОЛЛЕДЖ СТРОИТЕЛЬСТВА И ОТРАСЛЕВЫХ ТЕХНОЛОГИЙ» ТУЛЬСКОЙ ОБЛАСТИ «УЧЕБНО­МЕТОДИЧЕСКОЕ ПОСОБИЕ – 2018» КОНКУРС КОДИРОВАНИЕ И  ОБРАБОТКА ЧИСЛОВОЙ ИНФОРМАЦИИ. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ MS EXCEL Разработчик:  преподаватель информатики Ашурова О.А. СОДЕРЖАНИЕ Тула – 2018 Введение  1 . 2 . Электронные таблицы MS Excel Практические работы MS Excel 2.1. Практическая работа №1 «Назначение и интерфейс MS Excel» 2.2. Практическая   работа   №2   «Ввод   и   редактирование   информации   в  MS  Excel. Форматирование ячеек» 2.3. Практическая работа №3 «Ввод данных и формул в ячейки электронной таблицы MS Excel» 2.4. Практическая   работа   №4   «MS   Excel.   Создание   и   редактирование   табличного документа»  2.5. Практическая работа №5 «Ссылки. Встроенные функции MS Excel» 2.6. Практическое занятие №6 «Введение формул в MS Excel. Стандартные функции MS Excel» 2.7. Практическая работа №7  «MS Excel. Фильтрация (выборка) данных из списка. Построение диаграмм» 2.8. Практическая работа №8 «Построение графиков функций в MS Excel»  2.9. Практическая работа №9 «Построение диаграмм и графиков»  2.10. Практическая работа №10 «Функция ЕСЛИ. Кроссворд с проверкой» Тест «Интерфейс программы MS Excel» 3 .  4 .  Список литературы, интернет ­ ресурсы Контрольная работа по MS Excel 3 4 10 10 11 14 16 18 20 21 25 28 30 33 36 40 2 ВВЕДЕНИЕ В данном учебно­методическом пособии представлено руководство по основным приемам работы   в   «Microsoft   Excel   2010».   Предназначено   для   обучающихся   1   курсов   среднего профессионального образования.  В результате освоения учебно­методического пособия, обучающиеся смогут:   освоить интерфейс MS Excel;  вводить и изменять информацию на рабочем листе MS Excel;  научиться форматировать элементы и данные рабочего листа MS Excel;  производить вычисления;  изучить графические возможности MS Excel;  научиться моделировать и строить задачи в MS Excel;  составлять алгоритм решения задачи в MS Excel. Данное   учебно­методическое   пособие   входит   в   рабочую   программу   учебных   дисциплин «Информатика» и «Информатика и ИКТ». Пособие рассчитано на 16 аудиторных часов, в состав которых входят:  теоретический материал (2 часа)  практические работы (11 часов)  тестирование (1 час)  контрольная работа (2 часа) Необходимое   программное   обеспечение   и   оборудование   для   использования   учебно­ методического пособия:  компьютерный класс с лицензионным программным обеспечением:    операционная система Windows 7 (и выше) пакет программ MS Office 2007 (и выше) антивирусная программа Kaspersky для Windows.  мультимедийный проектор  интерактивная доска 3 1. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ MS EXCEL Microsoft Excel (Microsoft Office Excel) ­ программа для работы с электронными таблицами, созданная   корпорацией Microsoft для Microsoft а также Android, iOS и Windows Phone. Она предоставляет возможности экономико­статистических расчетов,   графические   инструменты   и,   за   исключением   Excel   2008   под Mac   OS   X,   язык макропрограммирования VBA (Visual   Basic   for   Application).   Microsoft   Excel   входит   в состав Microsoft   Office и   на   сегодняшний   день   Excel   является   одним   из   наиболее   популярных приложений в мире.   Windows, Windows   NT и Mac OS,   История В   1982   году   Microsoft   запустила   на   рынок   свой   первый   электронный табличный процессор Multiplan, который был очень популярен на CP/M системах, но на MS­DOS системах он уступал Lotus 1­2­3. Первая версия Excel предназначалась для Mac и была выпущена в 1985 году, а первая версия для Windows была выпущена в ноябре 1987 года. Lotus не торопилась выпускать 1­2­ 3 под Windows, и Excel с 1988 года начала обходить по продажам 1­2­3, что в конечном итоге помогло Microsoft достичь позиций ведущего разработчика программного обеспечения. Microsoft укрепляла своё преимущество с выпуском каждой новой версии, что имело место примерно каждые два года. Текущая версия для платформы Windows ­ Excel 16, также известная как Microsoft Office Excel 2016. Текущая версия для платформы macOS ­ Microsoft Excel 2016. Рабочее пространство Excel Рабочая область  Excel  называется рабочей книгой, которая состоит из рабочих листов. То есть, в одном файле­книге может располагаться одна или несколько таблиц, называемых Листами. Каждый лист состоит из множества ячеек, образующих таблицу данных. Строки нумеруются по порядку от 1 до 1 048 576. Столбцы именуются буквами от А до XFD. В этих ячейках может храниться огромное количество информации. Рис. 1 «Ячейки и координаты в Excel» 4 Каждая ячейка имеет свои координаты. Например, ячейка не пересечении 3­й строки и 2­го столбца имеет координаты B3 (рис. 1). Координаты ячейки всегда подсвечены на листе цветом. Основные элементы рабочей области Excel:  Заголовок страницы отображает название текущего рабочего документа  Выбор представления – переключение между вариантами отображения рабочего листа  Лента – элемент интерфейса, на котором расположены кнопки команд и настроек. Лента разделена на логические блоки вкладками. Например, вкладка «Вид» помогает настроить внешний вид рабочего документа, «Формулы» — инструменты для проведения вычислений и т.д.  Масштаб   отображения –   соотношение   между   реальным   размером   листа   и   его представлением на экране.  Панель быстрого доступа – зона размещения элементов, которые используются чаще всего и отсутствуют на ленте  Поле имени отображает координаты выделенной ячейки или имя выделенного элемента  Полосы прокрутки – позволяют прокручивать лист по горизонтали и по вертикали  Строка   состояния отображает   некоторые   промежуточные   вычисления,   информирует   о включении «Num Lock», «Caps Lock», «Scroll Lock»  Строка формул служит для ввода и отображения формулы в активной ячейке. Если в этой строке   формула,   в   самой   ячейке   вы   увидите   результат   вычисления   или   сообщение об ошибке.  Табличный курсор – отображает ячейку, которая в данный момент активна для изменения содержимого  Номера строк и имена столбцов – шкала по которой определяется адрес ячейки.   Вкладки листов помогают переключаться между всеми листами рабочей книги. Рис. 2 «Рабочая область Excel» Навигация по рабочему листу Excel Основные комбинации клавиш и результат их использования: 5  Клавиши   стрелок –   перемещение   активной   ячейки   в   сторону,   на   которую   указывает стрелка.   Например,   если   нажать   кнопку Вниз при   активной   ячейке   A5  –   активизируется ячейка А6  Кнопки Page  Up и Page  Down –   перемещение   на   один   экран   вверх   и   один   экран   вниз, соответственно. Если табличный курсор находится в ячейке А1, а на экране одновременно отображается   31   строка,   нажатие Page  Down переведёт   курсор   в   ячейку   А32. Комбинации Alt+Page Up и Alt+Page Down сдвигают курсор на один экран вправо и влево, соответственно  Кнопки   цифровой   клавиатуры –   при   неактивной   кнопке Num   Lock выполняют   те   же функции, что и клавиатурные стрелки  Клавиши   стрелок   с   активированной   кнопкой Scroll   Lock –   смещают   экран   в   сторону, указанную на стрелке, без изменения положения курсора  Комбинация Ctrl+Backspace возвращает экран в то место, где находится табличный курсор (активная ячейка)  Навигация   с   помощью   мыши –   такая   же,   как   и   во   всех   окнах   MS   Windows:   для прокручивания   листа   используем   горизонтальную   и   вертикальную   полосы   прокрутки. Вращая колесо мыши – можно прокручивать лист по вертикали. При зажатой клавише Ctrl, колёсико мыши изменяет масштаб отображения рабочего листа. Лента и группы инструментов Лента –   это   горизонтальная   панель   инструментов   вверху   экрана.   На   ней   собраны   и логически структурированы все главные команды, которые понадобятся в работе. Все элементы управления на ленте собраны в блоки, которые соответствуют одному типу задач. Например, блок «Шрифт»   на   вкладке   «Главная»,   отвечает   за   размер,   тип   и   цвет   шрифта,   способ   начертания, заливку и обводку активной ячейки (или диапазона ячеек). Ленточные вкладки – это подписанные группы команд прямо над лентой. Например, если Вы выберете вкладку «Вид», на ленте отобразятся инструменты для настройки отображения на листе.  Ленточные вкладки:  Файл – это не ленточная вкладка, хоть и расположена рядом с ней. Использование этой Рис. 3 «Ленточные вкладки» команды приводит к открытию специального окна, Backstage  Главная –   вкладка   открывается   по   умолчанию,   в   ней   собраны   инструменты,   которые используются наиболее часто  Разметка страницы – отвечает за разметку и структуру листа при печати. Здесь можно настроить заголовки, поля, ориентацию листа при печати и многое другое  Формулы –   вкладка   поможет   подобрать функцию, присвоить   ячейкам   имена, провести анализ введенных формул и др.  Данные – с помощью этой вкладки можно подбирать, сортировать, очищать, группировать данные  Рецензирование – целостность данных и защита от нежелательных изменений  Вид – позволяет настроить некоторые возможности отображения листа на экране  Разработчик. Эта вкладка по умолчанию скрыта. 6  Надстройки –   используются   для   работы   с   пользовательскими   (разработанными пользователем) меню, модулями, панелями инструментов и др.  Контекстные вкладки отображаются лишь тогда, когда активирован какой­то конкретный элемент. Например, когда выделяется диаграмма, на ленте появляются  еще две вкладки с настройками именно диаграмм – «Конструктор» и «Формат» Типы ленточных команд  Простая   команда –   при   её   выполнении   мгновенно   происходит   действие.   Например, кнопка Пересчёт заставляет Excel пересчитать все формулы в книге  Переключатель – внешне мало отличается от простой команды, но работает иначе. Такая кнопка имеет два положения – активно и неактивно. При нажатии кнопки Ж   содержимое ячейки   станет   полужирным,   а   сама   кнопка   выделится   цветом.   При   нажатии   повторно, форматирование текста и выделение кнопки исчезнут  Раскрывающийся список – например, список Шрифт  позволяет выбрать из перечня шрифт     перед   для активной ячейки.     Комбинированный   элемент –  выполняет   действие,   заданное   по  умолчанию,   либо   нужно сделать   Например, команда Заливка окрасит фон ячейки в выбранный цвет. Если нужно сменить цвет заливки – нажать на маленькую стрелку справа от кнопки, после выбора нового цвета ячейка будет автоматически им залита дополнительные выполнением. настройки  Флажок –   опция,   которая   либо   включает   что­то,   либо   выключает.   При   снятии   флажка Заголовки на   вкладке Вид,   с   листа   пропадут   номера   строк   и   имена   столбцов.   При постановки галку обратно, все вернется в изначальное состояние.  Счётчик представлен лишь одним инструментом – Масштаб, в нижнем правом углу окна. Управление лентой с помощью клавиатуры Удобно выполнять навигацию по ленте с помощью мыши. Но если нужно воспользоваться клавиатурой – нажимается клавиша Alt. При этом  Excel  покажет возле всех вкладок и кнопок определённую букву (рис. 4). Нажав её на клавиатуре, вы перейдёте в нужный раздел ленты или выполните   нужную   команду.   Чтобы   выйти   из   режима   ручного   управления   –   нажать клавишу Alt или Esc. Рис. 4 «Управление лентой с помощью клавиатуры» Контекстное меню Некоторые   настройки   и   команды   можно   выполнить   с   помощью   контекстного   меню.   По аналогии с другими приложениями Windows, оно появляется после нажатия правой кнопкой мыши на объекте настройки. Содержание контекстного меню зависит от того, на какой элемент кликнули. При   вызове   контекстного   меню   для   ячейки,   дополнительно   появляется   мини­панель настройки   шрифта   и   формата.   Это   позволяет   меньше   «бегать»   мышью   от   ячейки   к   ленте   и существенно сократить время на форматирование данных. 7 Рис. 5 «Контекстное меню Excel и мини­панель» Панель быстрого доступа Это команды, расположенные в верхнем левом углу экрана (рис. 6). Не зависимо от того, какая вкладка ленты выбрана, панель быстрого доступа всегда видна и всегда отображает самые нужные команды. Обычно на эту панель выносят команды, которых нет на ленте, но использовать их приходится часто. По умолчанию здесь кнопки Создать, Сохранить, Отменить, Вернуть. Рис. 6 «Настройка панели быстрого доступа» На   эту   вкладку   можно   вынести   большинство   доступных   команд.   Нужно   нажать маленькую стрелку «Вниз», открывается меню Настроить панель быстрого доступа со списком команд для добавления. Если инструмент уже есть на панели, слева от него будет стоять галочка. Выбрать нужный пункт из списка и нажать на него левой кнопкой мыши, он будет немедленно добавлен на панель (рис. 7) Рис. 7 «Настройка панели быстрого доступа» Добавление команды на панель быстрого доступа 8 Если в списке нет нужной команды, нужно выбрать в списке Другие команды, открывается диалоговое окно добавления. Здесь можно выбрать в какой группе или вкладке искать команду, а в списке   доступных   инструментов   –   выбрать   нужный.   Установить   на   него указатель   и нажать Добавить (рис. 8) Рис. 8 «Другие команды панели быстрого доступа» Диалоговые окна Диалоговые окна – это окна, с помощью которых Excel запрашивает дополнительные данные и настройки для выполнения операций.  Окна бывают:  Модальными, т.е. до их закрытия невозможно продолжать работу на рабочем листе. При нажатии   правой   кнопкой   мыши   на   любой   ячейке   и   выборе   в   контекстном   меню пункта Формат   ячеек.   Открывается   модальное   окно   настроек   выбранной   ячейки.   До закрытия этого окна кнопками Ок или Отмена, нельзя продолжить работу на листе.  Рис. 9 «Модальное диалоговое окно»  Немодальными,  т.е.   они   могут   быть   открыты   и   не   будут   мешать   работе. Навигация   и   управление   окнами   выполняют   с   помощью   мыши   или   клавиатуры.   Для последовательного   перехода   от   одного   элемента   в   окне   к   другому,   используют клавишу Tab (прямое направление) и Shift+Tab (в обратном направлении). Панель задач Панель задач – это диалоговое окно, способное оставаться на экране бесконечно долго, а не только   на   время   настройки   какой­то   операции.   Пример   такого   окна   – «Формат   диаграммы», которое   располагается   справа   от   рабочего   пространства   и   не   ограничивает   работу. 9 2. ПРАКТИЧЕСКИЕ РАБОТЫ MS EXCEL 2.1. Практическая работа №1  «Назначение и интерфейс MS Excel» Время выполнения: 15 мин Задание: Познакомиться практически с основными элементами окна MS Excel. Выполнить отчет  по поставленным вопросам.  Выполнения задания: 1. Запустите программу Microsoft Excel. Рассмотрите окно программы.  Одна из ячеек выделена (обрамлена черной рамкой). Чтобы выделить другую ячейку достаточно щелкнуть по ней мышью, указатель мыши в это время должен иметь вид светлого креста. Выделите различные ячейки таблицы. Для перемещения по таблице воспользуйтесь полосами прокрутки.  Для того чтобы ввести текст в одну из ячеек таблицы, необходимо ее выделить. 2. Введите в любую выделенную (активную) ячейку сегодняшний день недели, например: среда. 3. Основным отличием работы электронных таблиц от текстового процессора является то, что после ввода данных в ячейку, их необходимо зафиксировать, т.е. дать понять программе, что вы закончили вводить информацию в эту конкретную ячейку. Зафиксировать данные можно одним из способов:  нажать клавишу Enter; 10  щелкнуть мышью по другой ячейке;  воспользоваться кнопками управления курсором на клавиатуре (перейти к другой ячейке). Зафиксируйте введенные вами данные. Выделите ячейку таблицы, содержащую день недели, и воспользуйтесь кнопками выравнивания абзацев. 4. Таблица состоит из столбцов и строк, причем у каждого из столбцов есть свой заголовок (А, В, С…), и все строки пронумерованы (1, 2, 3...). Для того чтобы выделить столбец целиком, достаточно   щелкнуть  мышью   по   его  заголовку,   чтобы   выделить   строку  целиком,   нужно щелкнуть мышью по ее заголовку.  Чтобы достичь быстро конца таблицы по горизонтали или вертикали, необходимо нажать комбинации клавиш:   Ctrl+→ ­ конец столбцов   Ctrl+↓ ­ конец строк  быстрый возврат в начало таблицы ­ Ctrl+Home. Выделите ту ячейку таблицы, которая находится в столбце С и строке 4. Обратите внимание на то, что в Поле имени, расположенном выше заголовка столбца А, появился адрес выделенной ячейки С4. Выделите другую ячейку, и вы увидите, что в Поле имени адрес изменился. 5. Определите количество листов в Книге1. Вставьте   через   контекстное   меню Вставить–Лист два   дополнительных   листа. Для   этого встаньте на ярлык листа Лист 3 и щелкните по нему правой кнопкой, откроется контекстное меню   выберите   опцию  Вставить  и   выберите   в   окне   Вставка   Лист.   Добавлен   Лист   4. Аналогично добавьте Лист Измените порядок следования листов в книге. Щелкните по Лист 4 и, удерживая левую кнопку, переместите лист в нужное место. Отчет: 1. В ячейке А3 Укажите адрес последнего столбца таблицы. 2. Сколько строк содержится в таблице? Укажите адрес последней строки в ячейке B3. 3. Введите в ячейку N35 свое имя, выровняйте его в ячейке по центру и примените начертание полужирное.  4. Введите в ячейку С5 текущий год.  5. Переименуйте Лист 1 Сохраните работу с названием «Практическая работа №1» в своей рабочей папке  2.2. Практическая работа № 2 «Ввод и редактирование информации в MS Excel. Форматирование ячеек» Время выполнения: 1 час Задание №1: Составьте прайс­лист по образцу: 11 Этапы выполнения задания: 1. Выделите   ячейку В1 и   введите   в   нее   заголовок   таблицы «Прайс­лист   магазина "Канцтовары"» 2. Выделите   заголовок   –   ячейки В1:С1 и   выполните   команду:   щелчок   правой   клавишей, Формат   Ячеек,   вкладка Выравнивание и   установите   переключатель   «по   центру выделения» (Горизонтальное выравнивание), «Переносить по словам». Увеличьте шрифт заголовка. 3. В   ячейку С2 введите   функцию СЕГОДНЯ (Поставьте   знак «=»,   на   панели   инструментов вкладка ФОРМУЛЫ выберите Дата и Время вставить функцию СЕГОДНЯ). 12 4. В ячейку В3 введите слова «Курс евро», в С3 – курс евро на сегодняшний день ­ 76. 5. К ячейке С3 примените денежный формат ­ Главная, Вкладка Число, Числовой формат, Денежный. (Обозначение можно выбрать произвольное). 6. В ячейки А5:С5 введите заголовки столбцов таблицы. 7. Выделите их и примените полужирный стиль начертания и более крупный шрифт. 8. В ячейки А6:А12 и В6:В12 введите данные. 9. В ячейку С6 введите формулу: = В6*$C$3 ($ означает, что используется абсолютная  ссылка). 10. Выделите ячейку С6 и протяните за маркер заполнения вниз до ячейки С12. 11. Выделите диапазон ячеек С6:С12 и примените к ним денежный формат. 12. Измените название ЛИСТ1 на Прайс­лист. Задание №2: Рассчитайте ведомость выполнения плана товарооборота 13 1. Заполнение столбца Месяц можно выполнить протяжкой маркера. 2. Значения столбцов Выполнение, % и Отклонение от плана вычисляются по формулам:   Выполнение = фактически / план   Отклонение от плана = фактически – план  3. Переименуйте ЛИСТ 2 в Ведомость. Ответы: Сохраните работу с названием «Практическая работа №2» в своей рабочей папке  14 2.3. Практическая работа №3 «Ввод данных и формул в ячейки электронной таблицы MS Excel» Время выполнения: 1 час Выполнения задания: 1. Запустите программу Microsoft Excel. 2. В   ячейку А1 введите   текст:   «Год   основания   Колледжа».   Зафиксируйте   данные   в   ячейке любым известным вам способом. 3. В ячейку В1 введите число – год основания Колледжа (1940). 4. В ячейку C1 введите число – текущий год (2018) 5. Выделите ячейку D1 установите знак «=», далее щелкните мышкой по ячейке C1, поставьте знак «–» и щелкните по ячейке B1, нажмите Enter. 6. В ячейку А2 введите текст «Мой возраст». 7. В ячейку B2 введите свой год рождения. 8. В ячейку С2 введите текущий год. 9. Введите в ячейку D2 формулу для вычисления Вашего возраста в текущем году (= C2­ B2). 10. Выделите  ячейку С2. Введите  номер  следующего  года.  Обратите  внимание,  перерасчет в ячейке D2 произошел автоматически. 11. Определите свой возраст в 2025 году. Для этого замените год в ячейке С2 на 2025. Самостоятельная работа  Задание № 1:  Посчитайте, хватит ли вам 550 рублей, чтоб купить все продукты, которые вам заказала мама, и хватит ли купить чипсы за 30 рублей? № 1 2 3 4 5 Наименование Цена в рублях Количество Хлеб Кофе Молоко Пельмени Чипсы 27 150 50 170 30 2 1 3 1 1 Стоимость =С2*D2 =С3*D3 =С4*D4 =С5*D5 =С6*D6 Итого =Е2+Е3+Е4+Е5+Е6   Ответ: денег не хватит 15 Задание №2:  Рассчитать ежемесячный платеж за кредит в 150000?, взятый на 3 года, под 25% годовых (при расчете всех платежей использовать денежный формат) D А E В С кредит 1 2 3 4 % годовые 5 150000,00 ? (ячейка А2) 25,00% (ячейка А5) 6 7 Срок выплаты (лет) 8 3 (ячейка А8) Проценты 1 год =(А2*А5)/100%   (ячейка С2) Ежемесячный платеж =С17/А11 (ячейка Е2) Выплата за 1 год =А2+С2 (ячейка С5) Проценты 2 год Самостоятельно (ячейка С8) 9 1 0 1 1 1 2 1 3 1 4 1 5 1 6 1 7 Срок выплаты (месяцев) Выплата 2 год =А8*12 (ячейка А11) Самостоятельно  (ячейка С11) Проценты 3 год Самостоятельно (ячейка С13)  Выплата 3 год Самостоятельно  (ячейка С17) Ответ: 8138, 02 ?  ежемесячный платеж 16 Сохраните работу с названием «Практическая работа №3» в своей рабочей папке 2.4. Практическая работа №4  «MS Excel. Создание и редактирование табличного документа»  время выполнения: 1 час Задание: 1. Создайте   таблицу,   содержащую   расписание   движения   поездов   от   станции   Саратов   до станции Самара. Общий вид таблицы «Расписание» отображен на рисунке: 2. Выберите ячейку А3, замените слово «Золотая» на «Великая» и нажмите клавишу Enter. 3. Выберите ячейку А6, щелкните по ней левой кнопкой мыши дважды и замените «Угрюмово» на «Веселково» 4. Выберите ячейку А5 зайдите в строку формул и замените «Сенная» на «Сенная 1». 5. Дополните таблицу «Расписание» расчетами времени стоянок поезда в каждом населенном пункте   (вставьте   столбцы).   Вычислите   суммарное   время   стоянок,   общее   время   в   пути, время, затрачиваемое поездом на передвижение от одного населенного пункта к другому. 17 Выполнения задания: 1. Измените формат чисел для блоков С2:С9 и Е2:Е9. Для этого выполните следующие действия: ­ Выделите блок ячеек С2:С9; ­   Выполните   команду   основного   меню Главная   – Число   –   Формат   ячеек­  вкладка  Число   ­   Числовые форматы   ­   Время   и   установите   параметры   (часы: минуты). 2. Посчитайте время стоянок:  ­ в ячейке С3 введите формулу: = D3­В3 ­ аналогично посчитайте ячейки С4:С7 3. Посчитайте время в пути: ­ в ячейке Е2 введите формулу: =В3­D2 ­ аналогично посчитайте ячейки Е3:Е7 4. Вычислите суммарное время стоянок. ­ Выберите ячейку С9; ­ Щелкните кнопку   ­ Выделите блок ячеек С3:С7 и нажмите клавишу Enter.  : Формулы ­ Автосумма на панели инструментов; 5. Вычислите суммарное время в пути (аналогично 4 пункту) 6. Оформите таблицу цветом (выделить ­ щелчок правой клавишей мыши ­ формат ячеек ­ заливка ­ выбрать цвет ­ ок) и выделите границы таблицы (выделить таблицу ­ щелчок правой клавишей мыши ­ формат ячеек ­ граница ­ выбрать границы ­ ок). Итог:  Самостоятельная работа Рассчитайте   с   помощью   табличного   процессора Exel расходы   студентов,   собравшихся D 18 поехать на экскурсию в другой город. Оформите таблицу цветом.              А С В 1 Вид расходов Билеты Экскурсия в музей Обед Посещение выставки    2 3 4 5 6   Итог: Количество студентов 25 15 25 10   Цена в Р Общий расход в Р 520, 00 Р 400, 00 Р 220, 00 Р 380, 00 Р Всего:           Сохраните работу с названием «Практическая работа №4» в своей рабочей папке 2.5. Практическая работа №5 «Ссылки. Встроенные функции MS Excel» Время выполнения: 1 час Виды ссылок Название Запись При копировании Технология ввода Относительная C3 Абсолютная Смешанная $C$3 С$3 $C3 Меняется в соответствии с новым положением ячейки Не меняется Не меняется номер строки Не меняется имя столбца Щелкнуть в ячейке Щелкнуть в ячейке и нажимать F4 до   преобразования   адреса   к нужному виду Задание: Заданы стоимость 1 кВт./ч. электроэнергии и показания счетчика за предыдущий и текущий месяцы.   Необходимо   вычислить   расход   электроэнергии   за   прошедший   период   и   стоимость израсходованной электроэнергии.  19 Выполнение работы: 1. Выровняйте текст в ячейках. Выделите ячейки А3:Е3. Главная ­ Формат –Формат ячейки – Выравнивание: по горизонтали – по центру, по вертикали – по центру, отображение – переносить по словам. 2. В ячейку А4 введите: Кв. 1, в ячейку А5 введите: Кв. 2. Выделите ячейки А4:А5 и с помощью маркера автозаполнения заполните нумерацию квартир по 7 включительно. 3. Заполните ячейки B4:C10 по рисунку. 4. В ячейку  D4  введите формулу для нахождения расхода эл/энергии. И заполните строки ниже с помощью маркера автозаполнения. 5. В ячейку E4 введите формулу для нахождения стоимости эл/энергии =D4*$B$1 и заполните строки ниже с помощью маркера автозаполнения.  6. В ячейке А11 введите текст «Статистические данные» выделите ячейки A11:B11 и щелкните на панели инструментов кнопку «Объединить и поместить в центре».  В ячейках A12:A15 введите текст, указанный на рисунке. 7. 8. Щелкнуть   мышью   по   ячейке  B12  и   ввести   математическую   функцию СУММ,   для   этого необходимо щелкнуть в строке формул а также подтвердить диапазон ячеек В4:В10. по знаку fx и выбрать функцию, 9. Аналогично функции задаются и в ячейках B13:B15. 10. Таблицу заполнить цветом, выделить границы. 11. Расчеты выполнить на Листе 1, переименовать его в Электроэнергию. Итог: 20 Самостоятельная работа Рассчитайте   свой   возраст,   начиная   с   текущего   года   и   по   2030   год,   используя   маркер автозаполнения. Год вашего рождения является абсолютной ссылкой. Расчеты выполняются   на Листе 2. Лист 2 переименуйте в Возраст. Таблицу заполнить цветом, выделить границы Итог: Сохраните работу с названием «Практическая работа №5» в своей рабочей папке 2.6. Практическое занятие №6 «Введение формул в MS Excel. Стандартные функции MS Excel» Время выполнения: 1 час Задание №1 Заполнить таблицу по образцу, рассчитать возраст каждого. Выполнение: 21 1. Заполнить таблицу по образцу. В С2:С11 отметить формат Время (главная – число ­ дата). 2. В D2 введите формулу: =(ГОД(СЕГОДНЯ()­С2)­1900) Эта формула будет вычислять всегда правильное количество полных лет человека, т.к. для вычисления   используется   функция   СЕГОДНЯ,   которая   в   каждый   конкретный   момент времени использует текущую дату. (Таблица была составлена 01.10.2015, при использовании этого примера позже 5.12.2015 будут другие данные в столбце С) Протяните значения на С2:С11 3. Заполните таблицу цветом. Итог:  Задание №2 Посчитать формулу Выполнение: 1. Заполните таблицу аналогично картинке. Заполните цветом 2. Посчитайте по формуле значение m  22 Итог: Задание №3 Самостоятельно оформить таблицу для вычисления формулы:  =2 R/Т по окружности. Не менее 10 пунктов. Таблицу заполнить цветом. υ π  ­ скорость при движении  Сохраните работу с названием «Практическая работа №6» в своей рабочей папке 2.7. Практическая работа №7 «MS Excel. Фильтрация (выборка) данных из списка. Построение диаграмм» Время выполнения: 1 час Задание №1 Создайте таблицу в соответствие с образцом, приведенным на рисунке. Переименуйте лист 1 под именем «Расчет». 23 Выполнение: 1. Установите курсор­рамку внутри таблицы. 2.  Выполните команду меню Данные ­ Сортировка. 3. Выберите Сортировать по ­ отдел (Все отделы в таблице расположатся по алфавиту). Для того чтобы ежедневно распечатывать список товаров, оставшихся в магазине (имеющих ненулевой остаток), нужно получить отфильтрованные данные. 4. Установите курсор­рамку внутри таблицы данных. 5. 6. Снимите выделение в таблицы.  Выполните команду меню Данные – Фильтр 9. У каждой ячейки заголовка таблицы появилась кнопка «Стрелка вниз», она не выводится на печать, позволяющая задать критерий фильтра. Мы хотим оставить все записи с ненулевым остатком. 10. Щелкните по кнопке со стрелкой, появившейся в столбце Количество остатка. Раскроется список,   по   которому   будет   производиться   выборка.   Снимите   галочку   с   цифры   ноль. Нажмите Enter. Данные в таблице будут отфильтрованы. 24 11. Вместо   полного   списка   товаров,   мы   получим   список   проданных   на   сегодняшний   день товаров. 12. Фильтр   можно   усилить.   Если   дополнительно   выбрать   какой­нибудь   отдел,   то   можно получить список неподанных товаров по отделу. 13. Для того, чтобы снова увидеть перечень всех непроданных товаров по всем отделам, нужно в списке «Отдел» выбрать критерий «Все». 14. Чтобы   не   запутаться   в   своих   отчетах,   в   ячейку  J1  вставьте   дату,   которая   будет автоматически  меняться  в   соответствии   с  системным  временем   компьютера Формулы   – Вставить функцию ­ Дата и время ­ Сегодня. 15. Восстановите   исходный   вариант   таблицы   и   отмените   режим   фильтрации.   Для   этого щелкните   по   кнопке   со   стрелкой   и   в   раскрывшемся   списке   выберите   строку  Все,   либо выполните команду Данные ­ Фильтр – Выделить все.   Задание №2 Построить график для формулы Выполнение: 1. Откройте свою практическую работу №6 2. Постройте диаграммы   для столбца  m  (выделить весь столбец,  на панели инструментов выбрать вкладку Вставка – Диаграммы – График – выбрать любой ­ Enter) 25  для всех столбцов: выделить все столбцы, Вставка – Диаграммы – Гистограмма – выбрать любую ­  Enter)  для столбцов h   и ω: выделить столбцы, Вставка – Диаграммы – Линейчатая – выбрать любую ­  Enter)  для   столбцов  c  и  m:   выделить   столбцы,  Вставка   –   Диаграммы   –   Другие   – Поверхность ­ выбрать любую ­  Enter) 3. Пересохранить работу с названием «Практическая работа №7» в своей рабочей папке 26