Методическая разработка по информатике на тему: Выполнение расчётов в электронных таблицах

Лабораторная работа Тема: Выполнение расчётов в электронных таблицах Цель: овладеть основными приемами работы со списками и базами данных в приложении MS Excel. Порядок работы: Задание №1. Запустить приложение MS Excel и создать файл на рабочем столе под своей фамилией. Задание   №2.  В   своем   файле   на   Листе1   создать   и   отформатировать таблицу по образцу. Переименуйте Лист1 на «Подготовка к школе». Выберите цвет для ярлычка Листа1 на свое усмотрение. Порядок выполнения задания №2: 1. Объединить   ячейки   А1:Е1,   значение   выровнять   по   центру,   задать стиль начертания по образцу. 2. Для столбцов «№ п/п» и «сумма» установить денежный формат. 3. В ячейку Е3 внести формулу для расчета суммы (=С3*D3). 4. С помощью маркера заполнения распространить формулу до ячейки Е12. 5. В   ячейку   Е13   для   подсчета   общей   суммы   внести   формулу: =СУММ(E3:E12). 6. Отформатировать таблицу на свое усмотрение: залить цветом ячейки, задать необходимый стиль начертания текста.Задание №3.  На Листе2 желтого цвета с названием «Цельсия­Кельвина» составить   таблицу   перевода   температуры   из   градусов   по   шкале   Цельсия   в шкале   Кельвина, градусы   по Фаренгейта, Ренкина для температуры   от значений ­100о  до 100о  с 10о. шагом формулы перевода:     Т К  С Т 15,273 ; . Т  Т R 8,1*K Т F  С Т 8,1*  32 ; Порядок выполнения задания №3: 1. Объединить   ячейки   А2:D2,   значение   выровнять   по   центру, задать стиль начертания по образцу. 2. Столбец   «шкала   Цельсия»   заполнить   с   шагом   10,   используя маркер автозаполнения. 3. В ячейке В3 ввести формулу перевода температуры из градусов по шкале Цельсия в градусы по шкале Кельвина: =A3+273,15.  4. С  помощью  маркера  заполнения  распространить   формулу   до ячейки со значением 100о по шкале Цельсия. 5. Аналогично   заполнить   столбцы   «шкала  Фаренгейта»,   «шкала Ренкина». Задание   №4.  На   Листе3   с   названием   «Зарплата»   составить   таблицу расчета заработной платы для сотрудников организации по образцу.Порядок выполнения задания №4: 1. Для   вычисления   зарплаты   необходимо   минимальную   заработную плату умножить на коэффициент. Минимальная заработная плата является единой для всех сотрудников, поэтому ссылка на ячейку В11 должна быть абсолютной. В ячейку  D3 ввести формулу: =C3*$B$11 и распространить ее вниз до ячейки до ячейки D8 с помощью маркера заполнения. 2. Выполнить   расчет  Всего   начислено  в   ячейке  D9,   используя автосумму. 3. Установить денежный формат в столбце зарплаты. Задание №5.  Для   таблицы   задания   №4  построить   круговую   объемную диаграмму по образцу. Заработная плата Артемьев; 67,680.00р Протасов; 59,040.00р Филимонова; 67,680.00р Веригин; 91,440.00р Лисицына; 74,880.00р Жигалин; 97,200.00рПорядок выполнения задания №5: 1. Выделить   диапазоны   ячеек  D3:D8   и  D3:D8,   зажав   клавишу Ctrl. Вставить диаграмму нужного вида. 2. Вызвать   диалоговое   окно  Формат   подписей   данных  с → помощью   команды Дополнительные параметры подписей данных,  установить  флажки для полей «имена категорий», «значения», «линии выноски».  вкладка   Макет   Подписи   данных → Добавить название диаграммы. Далее   отредактировать   и   отформатировать   диаграмму   по 3. 4. образцу. Задание №6. Для таблицы задания №2 построить линейчатую диаграмму с группировкой по образцу. Подготовка к школе Обложки для учебников Обложки для тетрадей 900.00р 650.00р Пенал Ластик Дневник Карандаш Ручка 125.00р 68.00р 82.45р 102.00р 125.00р Тетрадь в линию Тетрадь в клетку Тетрадь общая 238.00р 228.90р 280.00р Задание   №7.  В   своем   файле   на   Листе4   создать   и   отформатировать таблицу по образцу. Переименуйте Лист4 на «База данных». Выберите цвет для ярлычка Листа4 на свое усмотрение.Порядок выполнения задания №7: 1. Задать стиль начертания текста– Times New Roman, размер– 14 пт (для 1­ой и 2­ой строк), 12 пт (для остальных). Установить высоту 1­ой и 2­ ой строки– 25, остальных строк– 20. 2. Для столбца «Дата заключения» установить тип «ДАТА». 3. Осуществить заливку ячеек на свое усмотрение, задать стиль границ по образцу. 4. Чтобы   пользоваться   базой   данных,   обращаемся   к   инструментам вкладки Данные. 5. Присвоить   базе   данных   имя. Для   этого   выделить   диапазон   с данными–   от   первой   ячейки   до последней,   и   с   помощью   команды Контекстное   меню   Присвоить имя   чтобы диапазон был правильным.   Проверить, → →   БД1 . Чтобы упростить поиск данных в базе, их необходимо упорядочить. Для этой цели предназначен инструмент «Сортировка».6. Выделить тот диапазон, который нужно отсортировать: столбец «Дата заключения» без заголовка. Вызвать инструмент «Сортировка». 7. При   нажатии   система   предлагает   автоматически   расширить выделенный диапазон. Соглашаемся. Если мы отсортируем данные только одного   столбца,   остальные   оставим   на   месте,   то   информация   станет неправильной.   Открывается   меню,   где   мы   должны   выбрать   параметры   и значения сортировки. Данные в таблице распределились по сроку заключения договора.Теперь менеджер видит, с кем пора перезаключить договор. А с какими компаниями продолжаем сотрудничество. БД   в   процессе   деятельности   фирмы   разрастается   до   невероятных размеров. Найти нужную информацию становится все сложнее. Чтобы отыскать конкретный   текст   или   цифры,   можно   воспользоваться   одним   из   следующих способов:  Одновременным нажатием кнопок   Ctrl  +   F или   Shift   +   F5. Появится   окно поиска  «Найти и заменить».  Командой  «Найти   и   выделить» («биноклем») вкладки Главная.Посредством  фильтрации   данных  программа   прячет   всю   не интересующую   пользователя   информацию.   Данные   остаются   в   таблице,   но невидимы. В любой момент их можно восстановить. В программе Excel чаще всего применяются 2 фильтра:  Автофильтр;  Фильтр по выделенному диапазону. Автофильтр  предлагает пользователю выбрать параметр фильтрации из готового списка.   8. На «Данные» кнопку «Фильтр». вкладке нажимаем   9. После   нажатия   в   шапке   таблицы   появляются   стрелки   вниз.   Они сигнализируют о включении «Автофильтра». 10. Чтобы   выбрать   значение   фильтра,   необходимо   щелкнуть   по стрелке   нужного   столбца.   В   раскрывающемся   списке   появляется   все содержимое   поля.   Если   нужно   спрятать   какие­то   элементы,   необходимо отключить галочки напротив их.11. При   нажатии   «ОК».   Например, скройте   клиентов,   с   которыми   заключали договоры в 2013 году. 12. Чтобы задать условие для фильтрации поля типа «больше», «меньше», «равно» и т.п. числа, в списке фильтра нужно выбрать команду «Числовые фильтры». 13. Отобразить   в   таблице   клиентов,   с   которыми   заключили договор   на   3   и   более   лет,   ввести   соответствующие   значения   в   меню пользовательского автофильтра.Задание   №8.  Подготовить   ведомость   на   выдачу   заработной   платы.  В своем   файле   на   Листе5   создать   и   отформатировать   таблицу   по   образцу. Переименуйте Лист5 на «Ведомость». Выберите цвет для ярлычка Листа5 на свое усмотрение. Порядок выполнения задания №8 1. Создайте таблицу следующего содержания. 2. Заполните   таблицу   текстовой   и   фиксированной   числовой информацией – это столбцы «ФИО», «Оклад», «Число детей». 3. Пусть   профсоюзный   и   пенсионный   налоги   составляют   по   1%   от оклада. Удобно ввести формулу в одну ячейку, а затем распространить ее на оба столбца. Самое важное не забыть про абсолютные ссылки, так как и профсоюзный и пенсионный налоги нужно брать от оклада, то есть ссылаться только на столбец «Оклад». Примерный вид формулы: =$СЗ*0,01. Послеввода  формулы в ячейку  D3  ее  нужно  распространить  вниз (протянув  за маркер выделения) и затем вправо на один столбец. 4. Подоходный   налог   подсчитаем   по   формуле:   12%   от   Оклада   за вычетом минимальной заработной платы и пенсионного налога. Примерный вид формулы: =(СЗ­Е3­75900)*12/100. После ввода формулы в ячейку F3, ее нужно распространить вниз. 5. Для подсчета Суммы к выдаче примените формулу, вычисляющую разность   оклада   и   налогов.   Примерный   вид   формулы:   =СЗ­D3­E3­F3, размещенной в ячейке G3 и распространенной вниз. 6. Выполните обрамление и форматирование таблицы. 7. Постройте   диаграмму,   отражающую   начисления   каждого сотрудника. 8. Выделите   заполненные   данными   ячейки   таблицы,   относящиеся   к столбцам   «Фамилия,   имя,   отчество»   и   «Сумма   к   выдаче».   Понятно,   что требуется   выделить   два   столбца   таблицы:   «Фамилия,   имя,   отчество»   и «Сумма к выдаче». Но эти столбцы не расположены рядом, и традиционным способом   мы   не   сможем   их   выделить.   Для  Excel  это   не   проблема.   Если удерживать нажатой клавишу Ctrl, то можно одновременно выделять ячейки в разных местах таблицы. 9. С помощью команды  вкладка Вставка  Круговая  Объемная круговая вставьте круговую диаграмму. → → 10. 11. Не выходя из мастера диаграмм, выберите вкладку Макет. Выберите   команду  Подписи   данных→  Дополнительные параметры. 12. Для параметров подписи включите имена категорий и доли, положение – у вершины, снаружи. 13. С   помощью   команды  Название   диаграммы→  Над диаграммой задайте название «Сумма к выдаче». 14. Установите   на   свой   выбор   формат   области   диаграммы, формат подписей данных, формат названия диаграммы. 15. И диаграмма примет вид:Задание №9.  Самостоятельно постройте и отформатируйте диаграмму, отражающую выплачиваемые налоги, и график, отражающий заработную плату каждого   сотрудника,   по   образцу.   Разместить   график   и   диаграмму   на диаграммных листах. Заработная плата 491,380.0р 396,648.0р379,424.0р 334,641.6р 207,184.0р 207,184.0р 396,648.0р 309,666.8р 309,666.8р 379,424.0р 588,695.6рНалоги профс. пенс. подох. Чудов А.Н. Храмов А.Л. Симонов Л.Е. Сидоров И.В. Петров М.В. Леонов И.И. Круглова А.Д. Котов И.П. Китова В.К. Иванова Е.П. Иванов А.Ф. Задание   №10.  Создание   документа   «График   погашения   кредита»   с использованием шаблона. Порядок выполнения задания №10: 1. Выбрать шаблон «Рассрочка» с помощью команды вкладка Файл→ Создать  Образцы шаблонов  Рассрочка. → → 2. Ввести   на   свое   усмотрение   значения   для   «Суммы   кредита», «Годовой процентной ставки», «Срока погашения ссуды», «Числа платежей в год», «Начала кредита». 3. Сохранить свой документ с именем «Рассрочка1».