«Ввод и форматирование данных. Организация расчетов в табличном процессоре.»

ПРАКТИЧЕСКОЕ ЗАНЯТИЕ  учебной дисциплины  ИНФОРМАТИКА Тема:  «Ввод   и   форматирование   данных.   Организация   расчетов   в   табличном процессоре.» Цель:  изучить   основные   приемы   работы     по   форматированию   данных,   выполнению   расчетов   в табличном процессоре. Практическая работа.   Время выполнения: 90 минут. Запуск приложения (программы). 1. Двойной щелчок мышью на значке  2. Меню Пуск ­ Программы ­ Microsoft Office ­ Microsoft Office Excel  на Рабочем столе ЗАДАНИЕ №1. - Запустите программу. - Внимательно ознакомьтесь с элементами окна, используя рисунок.  ОКНО ТАБЛИЧНОГО РЕДАКТОРА. 1 Кнопка Office – открывает меню с основными командами по работе с документом Панель быстрого доступа – набор часто используемых команд Строка заголовка содержит имя файла (по умолчанию Книга 1,2….) Управляющие кнопки «Свернуть», «Развернуть», «Закрыть». Ленты или Вкладки: содержат инструменты текстового редактора.  Строка   формул  ­  служит   для   отображения   данных,   вводимых   в   ячейки  таблицы,   или   для непосредственного ввода данных. Рабочая область – ячеечная структура. Текущая ячейка –  ячейка, с которой работает пользователь в настоящий момент Ярлыки листов – инструменты для переключения между страницами книги Кнопка вставки листа – добавление листов в книгу 2 Основные понятия и приемы работы Ячейка – минимальный элемент таблицы. Имя ячейки, адрес или ссылка ­ большая буква английского алфавита, номер строки: А3, В5 и т.д. По умолчанию, все ссылки  относительные. При копировании формул, они изменяются на основании относительного расположения строк и столбцов. Например, если Вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула превратится в =A2+B2. Относительные ссылки особенно удобны, когда необходимо продублировать тот же самый расчет по нескольким строкам или столбцам. Абсолютные ссылки не изменяются при копировании или заполнении. Используются, чтобы сохранить неизменной   строку   или   столбец.   Абсолютная   ссылка   сопровождается   добавлением   знака   доллара ($).Например, если Вы скопируете формулу =$A$1+B1 из строки 1 в строку 2, формула превратится в =$A$1+B2. Нажатие F4 на клавиатуре ­ это самый простой и быстрый способ вставить абсолютную ссылку. Диапазон ячеек – набор ячеек. Обозначается адресами крайних ячеек. Пример: А1:Е1 – горизонтальный диапазон, состоящий из 5 ячеек в первой строке. А1:А7 – вертикальный диапазон, состоящий из7ячеек в первом столбце. А1:Е7 – прямоугольный диапазон, состоящий из 35 ячеек. Выделение ячеек: Выделение одной ячейки. Чтобы ввести данные или отредактировать содержимое ячейки, сначала ее нужно выделить. 1. Щелкните по ячейке, чтобы выделить ее. 2. Выбранная   ячейка   будет   обозначена   границами,   а   заголовки   столбца   и   строки   станут подсвеченными. Ячейка останется выделенной, пока Вы не выберите любую другую ячейку. Выделение диапазона ячеек. 1 способ: Щелкните левой кнопкой мыши по центру первой ячейки диапазона и, удерживая ее нажатой, протяните до последней. Указатель мыши при этом имеет вид белого крестика. 2 способ: Щелкните левой кнопкой мыши по центру первой ячейки диапазона и, удерживая ее нажатой клавишу SHIFT, щелкните по последней. Содержимое ячеек: текст, число или формула.  Вводимые символы сразу появляются в текущей ячейке и в строке формул. Ввод формул в ячейку завершается нажатием клавиши Enter. Удаление содержимого ячейки: выделите ячейку или группу ячеек и нажмите клавишу Delete. Замена   содержимого  ячеек:   выделите   ячейку,   введите   с   клавиатуры   новые   данные   нажмите  Enter. Предварительно удалять старое содержимое не нужно. Удаление ячеек. Если Вы удалите ячейку, то расположенные ниже ячейки будут смещаться вверх и заменять собой  удаленные. 1. Выберите ячейки, которые необходимо удалить. 2. Нажмите команду Удалить, которая находится в группе Ячейки на ленте Главная. Перемещение содержимого ячеек. 1. Выделите ячейку, которую хотите переместить. 2. Наведите указатель мыши на границу выделенной ячейки, курсор примет вид черного перекрестия со стрелками. 3. Нажмите левую кнопку мыши и, не отпуская ее, переместите курсор в требуемое место. Автозаполнение 1. Выделите   ячейку,   данные   которой   необходимо   продублировать.   В   правом   нижнем   углу выделенной ячейки появится маленький квадрат – это маркер автозаполнения. 2. Нажмите   и,   удерживая   левую   кнопку   мыши,   перетащите   маркер   автозаполнения,   пока   все требуемые ячейки не будут выделены. Разом вы можете заполнять ячейки, либо столбца, либо строки. 3. Отпустите кнопку мыши, чтобы заполнить выбранные ячейки. 3 Функции Функция  ­   это   программа,   которая   выполняет   различные   вычисления.   Каждая   функция   имеет собственный синтаксис, за которым необходимо следить, чтобы программа работала корректно. Ввод функции   начинаются   со   знака  =.   После   этого   знака   располагается   имя   функции,   а   затем   один   или несколько аргументов, заключенных в скобки.  Например: =СУММ(D5:D11)  ­ суммирует значения ячеек в диапазоне D5:D11 Категория Финансовые Дата и время Математические и  тригонометрические Статические Ссылки и массивы Работа с базами данных Логические Информационные Инженерные Текстовые Функции в Excel назначение Вычисляет процентные ставки, ежемесячные отчисления,  амортизационные отчисления Отображает текущий час, день недели, время или дату Вычисляют абсолютные величины, косинусы и логарифмы Вычисляют средние значения, наибольшие и наименьшие числа в  диапазоне, коэффициенты распределения; тестируют на независимость  выборок Вычисляют и возвращают значения из диапазона; создают гиперссылки для сетевых или Web­документов Вычисляют значения в таблицах баз данных Excel Вычисляют выражения и возвращают значения ИСТИНА или ЛОЖЬ,  которые используются при выполнении другого действия или  форматирования. Возвращают из Excel вWindows информацию о текущем статусе ячейки,  объекта или среды Входят в состав Excel , но должны устанавливаться отдельно из Analysis  Toolpack Преобразуют регистр текста, образуют символы с правого или левого  конца текстовой строки, объединяют текстовые строки Ввод функций 1. Перед вводом функции убедитесь, что ячейка для размещения является активной. 2. Вызовите Мастер функций: лента Формулы –Вставить функцию fx . 3. Выберите в списке нужную категорию, а ниже– функцию, в нижней части окна прочтите описание функции. Щелкните на кнопке ОК. 4. В окне Аргументы функции заполните необходимые поля, щелкните на кнопке ОК. Коды ошибок появляются в ячейках при обнаружении конфликтов. Код и название ошибки ##### #дел/0 #н/д #имя? #пусто! #число! Причина Данные   слишком   велики,   чтобы   поместить   в   ячейке.   Проверьте   формулу   или увеличьте ширину столбца Число или содержимое ячейки, на которое вы делите, равно нулю или ячейка пуста. Если такое происходит с только что заполненными ячейками, то, скорее всего, нужна абсолютная ссылка на ячейку в исходной формуле. Вы  пропустили  обязательный  параметр  функции,  или  ячейка,   содержащая   данные, пуста или содержит данные не того типа, который требуется Вы   неверно  указали   имя   диапазона   или   функции,   или   вы   использовали   ссылку   на несуществующее   имя,   или   вы   использовали   в   формуле   текст,   не   заключив   его   в кавычки, или вы пропустили двоеточие при задании диапазона Вы указали пересечение, которого на самом деле не существует Вы указали текстовый или пустой параметр там, где требуется число, или вы ввели формулу,   которая   при   вычислении   выдает   число,   слишком   большое   или   слишком 4 #ссылка! #знач! малое для Excel Вы   удалили   ячейки,   необходимые   формуле,   так   что  Excel  не   может   найти необходимую ячейку (вы могли удалить ячейки и путем вставки на их место других ячеек) Вы   ввели   текст,   там   где   требуется   числовое   значение   или   логический   оператор ИСТИННО или ЛОЖНО. ЗАДАНИЕ №2 Подготовьте таблицу для расчетов заработной платы сотрудников предприятия за октябрь. Выполните расчеты.  Если  число детей сотрудника  три  и более, то подоходный  налог не взимается.  Выполните сортировку сотрудников по алфавиту. Рассчитайте размер средней заработной платы сотрудников. Образец таблицы. ХОД РАБОТЫ: ПОСТРОЕНИЕ ТАБЛИЦЫ 1. Выделите диапазон ячеек А1:Н1.  2. Объедините их –лента Главная – группа Выравнивание – Объединить и поместить в центре 3. Внесите заголовок таблицы и нажмите Enter 4. Выделите диапазон ячеек А2:Н16.  Поставьте границы ­ лента Главная – группа Шрифт – Границы – Все границы 5. 6. Выделите диапазон А2:А3, объедините, внесите данные № п/п 7. Повторите действия для диапазонов В2:В3, С2:С3, D2:D3, заполните их. 8. Выделите диапазон Е2:G2, объедините, внесите данные Налоги 9. Заполните ячейки E3,F3,G3, используя данные из образца 10. Выделите диапазон Н2:Н3, объедините, внесите данные Сумма к выдаче 11. Заполните ячейки E4,F4,G4, используя данные из образца 12. Внесите в ячейку А5 цифру 1, в ячейку А6 цифру 2 13. Выделите две эти ячейки, наведите указатель мыши на нижний правый угол ячейки А6. 14. Указатель примет вид черного крестика. Используйте автозаполнение. (Протяните вниз или  дважды щелкните по нему) 15. Заполните таблицу данными из образца по столбцам ФИО, Число детей и Оклад. 5 16. Выделите диапазон D5:Н15. Выставите денежный формат – лента Главная – группа Число –  Денежный (используйте значок или выберите из списка ) 17. Выделите диапазон ячеек А2:Н3. 18. Установите для этих ячеек перенос текста по словам: лента Главная – группа Выравнивание –  Перенос текста 19. Выделите диапазон А2:Н16. Измените размеры столбцов:  1) лента Главная – группа Ячейки – Формат – Автоподбор ширины столбца 20. Выделите столбцы Е, F, G и установите для ни ширину 15:  1) Щелкните правой кнопкой мыши по имени столбца Е.  Указатель примет вид толстой  стрелочки, направленной вниз  2) удерживая кнопку мыши нажатой, протяните до столбца F  ПРИМЕЧАНИЕ: Или щелчок правой кнопкой мыши по столбцу Е. Нажмите и удержите  клавишу SHIFT  щелкните по столбцу  F 3) Лента Главная – группа Ячейки – Формат – Ширина столбца 4) Наберите с клавиатуры число 15 и нажмите ОК ПРИМЕЧАНИЕ:  Если  часть  заголовков  не  отображается,  то  увеличьте  высоту  строки  3. Наведите указатель мыши на границу между номерами строк 3 и 4, удерживая правую кнопку мыши нажатой,  потяните вниз. Как только все записи проявятся, отпустите правую кнопку мыши.  21. Измените ширину столбца  С согласно образцу. Выделите столбец. Наведите указатель мыши на   Удерживая границу между столбцами  С  и  D.   Указатель примет вид двойной стрелочки.  левую кнопку мыши потяните влево, пока столбец не примет вид образца.  ВЫПОЛНЕНИЕ РАСЧЕТОВ 1. Рассчитайте размер Профсоюзного взноса по формуле   Профсоюз = Оклад * % Премии, в ячейке E5 наберите формулу = D5*$Е$4 (ячейка E4 используется в виде абсолютной адресации) и скопируйте автозаполнением ПРИМЕЧАНИЕ:   Ввод   формулы   начинается   со   знака  =.  Чтобы   не   вводить   адрес   ячейки вручную, щелкните мышью по соответствующей ячейке, ее адрес пропишется в формуле 2. Аналогично рассчитайте Пенсионный взнос. 3. Для расчета подоходного налога используем стандартную функцию =ЕСЛИ( ) рис. 1 1) В   ячейку  G5   введем   формулу,   используя   мастер   функций:   лента  Формулы  –   группа Библиотека функция – Логические – Если 2) В диалоговом окне Аргументы функции в строке Логическое выражение внесите условие проверки количества детей сотрудника C5>=3 (если число детей больше или равно трем) 3) В строке Значение_если_истина ставим 0 (т.к. если детей больше или равно трем, то налог не взимается) 4) В строке Значение_если_ложь ставим формулу для расчета подоходного налога D5*$G$4 и нажмите ОК 6 Рис. 1 Функция ЕСЛИ() 5) Скопируйте формулу автозаполнением 6) Измените количество детей у сотрудников. Посмотрите, как сработает формула. 7) Отмените изменения 4. Расчет Сумма к выдачи выполните по формуле: Сумма к выдачи = Оклад­ Профсоюз­  Пенсионный взнос ­ Подоходный налог (=D5­E5­F5­G5) 5. Скопируйте ее автозаполнением для остальных сотрудников 6. Расчет итоговых значений по столбцам Налоги и Сумма к выдачи выполните с помощью  автосуммы: 1) Выделите диапазон Е5:Е15 2) Лента Главная – Редактирование – Автосумма 7. Распространите ее автозаполнением на остальные столбцы 8. Расчет Средней заработной платы сотрудника выполните с помощью формулы СРЗНАЧ() 9. Выделите диапазон F18:G18, объедините ячейки. 10. Внесите запись Средняя заработная плата 11. В ячейку Н18 вставьте формулу для расчета: 1) Лента Формулы – группа Библиотека функция – Другие функции – Статистические –  СРЗНАЧ 2) В диалоговом окне Аргументы функции в строке Число 1 внесите диапазон H5:H16,  выделив его из таблицы и нажмите ОК. рис 2 7 Рис. 2 Функция СРЗНАЧ() СОРТИРОВКА 1. Для сортировки сотрудников по алфавиту выделите диапазон В5:Н15 (выделение только столбца с фамилиями при сортировке перемешает данные в таблице) 2. Лента Главная – Редактирование – Сортировка и фильтр –  Сортировка от А до Я 8 РЕЗУЛЬТАТЫ РАБОТЫ ПО ЗАДАНИЮ 2 ЗАДАНИЕ №3 Подготовьте   таблицу   для   расчетов   заработной   платы   сотрудников   предприятия   ноябрь.   Исправьте размер профсоюзного взноса с 1% на 2%, Добавьте доплату сотрудникам в размере 10% от оклада. Образец таблицы ХОД РАБОТЫ СОЗДАНИЕ ТАБЛИЦЫ Таблица практически идентична таблице из задания 2. Чтобы не строить ее заново, скопируем лист целиком и внесем в него изменения в соответствии с образцом. 1. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь» Для этого дважды щелкните мышью по ярлычку и наберите новое имя, нажмите Enter. 2. Скопируйте содержимое листа «Зарплата октябрь» на новый лист.  1) Нажмите правой кнопкой мыши по ярлыку листа «Зарплата октябрь»  2) Выберите Переместить/Скопировать  Рис. 3 Копирование листа 9 3) Выберите перед листом: Лист 2 4) Поставьте галочку в окошке Создавать копию. рис. 3 ПРИМЕЧАНИЕ. Перемещать и копировать листы можно, перетаскивая их корешки (для  копирования удерживайте нажатой клавишу CTRL).  3. Выделите столбец Е,  4. Добавьте столбец слева от столбца Е. 1) Лента Главная – группа Ячейки – Вставить – Вставить столбцы на лист 5. Внесите заголовок столбца  Доплата и размер доплаты 5% ВЫПОЛНЕНИЕ РАСЧЕТОВ 1. Вычислите размер доплаты формуле  Доплата = Оклад * % Доплаты 2. Скопируйте ее автозаполнением для остальных сотрудников 3. Исправьте формулу Суммы к выдаче. К исходным результатам нужно добавить доплату: 1) Выделите ячейку I5 2) Перейдите в Строку формул 3) Прибавьте к исходным результатам значение ячейки Е5, нажмите Enter. 4. Скопируйте ее автозаполнением для остальных сотрудников 5. Выполните расчет итоговой суммы Доплаты в ячейке Е16 любым из известных способов  (Автозаполнение, Автосумма или расчет по формуле)  РЕЗУЛЬТАТЫ РАБОТЫ ПО ЗАДАНИЮ 3 Подготовьте отчет по итогам практической работы. Результаты работы окажите преподавателю.  Письменно ответьте на контрольные вопросы. КОНТРОЛЬНЫЕ ВОПРОСЫ  1. Что такое ячейка? 2. Что такое диапазон ячеек? 3. Назовите виды адресации ячеек? На примере объясните применение соответствующих  видов адресации.  10 4. Что такое функция? Как использовать мастер функций? 5. Как указать ячейки в формуле? 6. Что такое Автозаполнение? 11