Инд задания. Программа для работы с электронными таблицами, позволяющая обрабатывать, анализировать данные и представлять их графически
![]()
|
Формулы, функции и диаграммы в табличном процессоре Microsoft Excel 2013 Microsoft Excel 2013 – программа для работы с электронными таблицами, позволяющая обрабатывать, анализировать данные и представлять их графически. Документ Microsoft Excel представляет собой рабочую книгу, состоящую из нескольких листов. Лист Excel имеет вид сетки из столбцов и строк. Столбцы обозначены латинскими буквами, а строки цифрами. На пересечении столбцов и строк образуются ячейки. Каждая ячейка имеет свой уникальный адрес, состоящий из имени столбца и номера строки, например А2. Ячейки могут содержать различные форматы данных (текст, числа, даты и др.). Важным элементом интерфейса программы является строка формул ![]() отображается содержимое активной ячейки, а в левой – ее адрес или имя диапазона. Любая формула в Excel начинается со знака «=». Кнопка ![]() ![]() Рис. 35. Мастер функций Библиотеку функций можно также найти на вкладке ленты Формулы (рис. 36). ![]() Выполните упражнение. Откройте табличный процессор Microsoft Excel 2013 и создайте рабочую книгу с именем Лабораторная работа №1. Необходимо создать таблицу расчета заработной платы сотрудников предприятия. Для упрощения ввода данных в таблицу создайте раскрывающийся список (рис. 37), содержащий ФИО сотрудников предприятия. ![]() Рис. 37. Раскрывающийся список Вставьте еще один лист в рабочую книгу Excel, используя ярлычок Новый лист ![]() ![]() Рис. 38. Список сотрудников предприятия Для сортировки Ф. И. О по алфавиту выполните команду: вкладка ленты Данные ► группа Сортировка и фильтр ► кнопка Сортировка от А до Я ![]() Выделите диапазон ячеек А1:А10 и щелкните поле Имя у левого края строки формул. Введите имя для ячеек, например Сотрудники ![]() Для того чтобы запретить другим пользователям просмотр и изменение полученного списка, защитите и скройте лист, на котором он находится. Правой кнопкой мыши щелкните по ярлычку листа. В контекстном меню выберите команду Защитить лист ![]() В диалоговом окне Защита листа (рис. 39) введите пароль для отключения защиты листа. В разделе Разрешить всем пользователям этого листа снимите флажки со всех элементов. Нажмите кнопку ОК. ![]() Рис. 39. Диалоговое окно Защита листа В диалоговом окне Подтверждение пароля введите пароль еще раз. Правой кнопкой мыши щелкните по ярлычку листа и в контекстном меню выберите команду Скрыть. Перейдите на Лист 1 и создайте таблицу Расчет заработной платы (рис. 40). Столбец Ф. И. О заполните, используя раскрывающийся список. Для этого выделите диапазон ячеек, в который требуется поместить раскрывающийся список. На вкладке Данные в группе Работа с данными выберите команду Проверка данных. В диалоговом окне Проверка данных укажите тип и источник данных (рис. 41). Откройте вкладку Сообщение для ввода (рис. 42). Заполните пустые поля. ![]() Рис. 40. Структура таблицы ![]() Рис. 41. Диалоговое окно Проверка данных ![]() Рис. 42. Сообщение при вводе данных Перейдите на вкладку Сообщение об ошибке (рис. 43). Заполните поля Вид, Заголовок и Сообщение. ![]() Рис. 43. Сообщение при ошибке ввода данных Для заголовков таблицы установите перенос текста (кнопка Перенести текст ![]() вкладки ленты Главная). Закрепите два первых столбца и строку заголовков таблицы. Для этого выделите ячейку C5 и выполните команду: вкладка ленты Вид ► группа Окно ► кнопка Закрепить области ![]() Столбец Оклад заполните произвольными данными и установите денежный формат ячеек, используя команду: вкладка ленты Главная ► панель инструментов Число ► в раскрывающемся списке форматов выберите Денежный формат. Составим формулу для вычисления премии, которая составляет 20 % от оклада. Любая формула начинается со знака =, поэтому переходим в ячейку F5 и вводим формулу =E5*20% (или =Е5*0,2). С помощью маркера автозаполнения (черный крестик возле правого нижнего угла выделенной ячейки) скопируйте формулу в область F6:F11. Между столбцами Премия и Подоходный налог вставьте столбец Итогоначислено, в котором посчитайте сумму Оклад+Премия. Заполните остальные столбцы таблицы, учитывая, что подоходный налог составляет 13 % от начисленной суммы. Посчитайте сумму к выдаче в долларах, для этого задайте текущий курс доллара, например 32, и в ячейку J5 введите формулу: =I5/$C$14. Знак $ используется в формуле для того, чтобы при копировании с помощью маркера автозаполнения адресация ячейки не изменялась. Для ячеек, в которых содержатся денежные данные, установите соответствующий формат. Используя функцию СУММ, посчитайте общую сумму подоходного налога. Для этого: − установите курсор в ячейку Н12; − поставьте знак =; − в строке формул нажмите кнопку ![]() − в появившемся диалоговом окне мастера функций (рис. 44) выберите категорию Математические, функцию СУММ; − в качестве аргумента функции СУММ выделите диапазон суммирования Н5:Н11; − нажмите кнопку ОК. Аналогичным образом посчитайте общую сумму к выдаче в долларах и общую сумму к выдаче в рублях. ![]() Рис. 44. Мастер функций Найдите среднюю (СРЗНАЧ), минимальную (MИН) и максимальную (MAКС) заработные платы. Используя условное форматирование, обозначьте красным цветом Суммы к выдаче, менее 5500 руб. Для этого выполните команду: вкладка ленты Главная ► группа Стили ► раскрывающийся список Условное форматирование ► Правила выделения ячеек. Постройте диаграмму Заработная плата сотрудников предприятия (рис. 45). Выделите одновременно столбцы Ф.И.О. и Сумма к выдаче (удерживая клавишу Сtrl), и на вкладке ленты Вставка на панели инструментов Диаграммы выберите вид Гистограмма ![]() Используя вкладку ленты Конструктор (панель инструментов Макеты диаграмм ► команда ![]() ![]() ![]() Рис. 45. Пример оформления диаграммы Постройте круговую диаграмму, показывающую соотношение между общей суммой к выдаче и суммарным подоходным налогом (рис. 46). ![]() Рис. 46. Пример оформления круговой диаграммы Индивидуальные задания10 спортсменов принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает не более 100 очков. Определить среди 10 спортсменов участника с наибольшим суммарным количеством очков. Построить диаграмму, показывающую соотношение количества набранных очков каждым спортсменом по каждому виду спорта. 10 студентов сдают экзамены по 5 дисциплинам. По каждой дисциплине можно получить оценку – 2, 3, 4, 5. Определить среди 10 студентов человека с наибольшим средним баллом. Построить диаграмму, показывающую соотношение оценок, полученных каждым студентом по каждой дисциплине. Для 10 человек по данным о ежемесячном доходе рассчитать подоходный налог 13 %, единый социальный налог 5 %. Округление произвести до копеек. Посчитать сумму к выдаче в рублях и $. Построить диаграмму, показывающую соотношение сумм уплаты налога по каждому виду налога. Билет на пригородный поезд стоит 20 руб., если расстояние до станции не более 20 км; 50 руб., если расстояние до станции больше 20 км, но меньше 75 км; 100 руб., если расстояние больше 75 км. Составить таблицу со следующими столбцами: пункт назначения, расстояние, стоимость билета, количество проданных билетов до данного пункта назначения. Установить число станций в радиусе 50 км от города. Построить диаграмму, показывающую, какая станция пользуется наибольшей популярностью по отношению к остальным. Телефонная компания взимает плату за услуги телефонной связи по тарифу: 370 мин в месяц – абонентская плата 200 руб., за каждую минуту сверх нормы – 2 руб. Составить ведомость оплаты услуг телефонной связи для 10 абонентов за 1 месяц, самостоятельно указав количество потребляемого времени каждым. Построить диаграмму, показывающую сравнительную характеристику сумм оплаты услуг телефонной компании каждым абонентом. Компания снабжает электроэнергией клиентов по тарифу: 5 руб. за 1 кВт/ч за первые 500 кВт/ч; 10 руб. за 1 кВт/ч свыше 500 кВт/ч, но не более 1000 кВт/ч; руб. за 1 кВт/ч свыше 1000 кВт/ч. Для 10 клиентов посчитать плату. Определить число клиентов, потребляющих более 1000 кВт/ч. Построить диаграмму, демонстрирующую сравнение потребляемой электроэнергии каждым клиентом. Билет на пригородный поезд стоит 10 монет, если расстояние до станции не более 20 км; 15 монет, если расстояние до станции больше 20 км, но меньше 75 км; 25 монет, если расстояние больше 75 км. Составить таблицу со следующими столбцами: пункт назначения, расстояние, стоимость билета, «Количество проданных билетов» до данного пункта назначения. Отсортировать таблицу по полю количество проданных билетов. Установить число станций в радиусе 60 км от города. Построить диаграмму, показывающую, какая станция пользуется наименьшей популярностью по отношению к остальным. Билет на пригородный поезд стоит 6 монет, если расстояние до станции не более 20 км; 10 монет, если расстояние до станции больше 20 км, но меньше 75 км; 15 монет, если расстояние больше 75 км. Составить таблицу со следующими столбцами: пункт назначения, расстояние, стоимость билета, количество проданных билетов до данного пункта назначения. Отсортировать таблицу по полю «Количество проданных билетов». Установить число станций в радиусе более 70 км от города. Построить диаграмму, показывающую, какая станция пользуется наименьшей популярностью по отношению к остальным. 10 студентов сдают экзамены по 5 дисциплинам. По каждой дисциплине студент может получить оценку – 2, 3, 4, 5. Определить средний балл учащихся. Посчитать количество 5, 4, 3 и 2. Найти студента с наибольшим средним баллом и студента с наименьшим средним баллом. Построить диаграмму, показывающую соотношение оценок, полученных каждым слушателем по каждой дисциплине. Для отдела из 10 человек составить ведомость расчета заработной платы. Таблица содержит следующие сведения: Ф. И. О., должность, оклад, стаж работы. Для каждого человека посчитать подоходный налог 13 %, надбавку 5000 руб., если стаж работы более 3 лет и сумму к выдаче. Построить диаграмму, показывающую з/п каждого сотрудника. Для отдела из 10 человек составить ведомость расчета заработной платы. Таблица содержит следующие сведения: Ф.И.О., должность, оклад, стаж работы. Для каждого человека посчитать подоходный налог 13 %, надбавку и сумму к выдаче. Надбавка составляет 10 % от оклада, если стаж работы более 5 лет. Построить диаграмму, показывающую з/п каждого сотрудника. Компания снабжает электроэнергией клиентов по тарифу: руб. за 1 кВт/ч за первые 500 кВт/ч; 20 руб. за 1 кВт/ч свыше 500 кВт/ч. Для 10 клиентов посчитать плату. Определить число клиентов, потребляющих не более 500 кВт/ч и найти суммарное количество потребляемой энергии. Построить диаграмму, демонстрирующую сравнение потребляемой электроэнергии каждым клиентом. Составьте экзаменационную ведомость, в которую входят следующие данные: №, Ф. И. О. студентов, оценки за экзамены. Посчитать средний балл для каждого студента. Если сданы все экзамены и средний балл равен 5, то выплачивается 50% надбавка к минимальной стипендии, если средний балл меньше 5, но больше или равен 4, то выплачивается минимальная стипендия. Построить диаграмму, показывающую количество оценок определенного вида, полученных в данной группе. Телефонная компания взимает плату за услуги телефонной связи по тарифу: 300 мин в месяц – абонентская плата 250 руб., за каждую минуту сверх нормы – 2 руб. Составить ведомость оплаты услуг телефонной связи для 10 абонентов за 1 месяц, самостоятельно указав количество потребляемого времени каждым. Найти людей с максимальной и минимальной оплатой услуг. Построить диаграмму, показывающую сравнительную характеристику сумм оплаты услуг телефонной компании каждым абонентом. 10 спортсменов принимают участие в некотором соревновании. Каждый спортсмен может набрать не более 30 очков. Указать номер места, которое занял спортсмен в данном соревновании. За 1 место выплачивается премия 100000 руб., за 2 место 50000 руб. и за 3 место 30000 руб. Построить диаграмму, показывающую количество набранных очков каждым спортсменом. Составьте ведомость контроля остаточных знаний студентов по какой-либо дисциплине. Контроль остаточных знаний проходит в форме теста, по результатам которого выставляется оценка. Если студент набрал от 95 до 100 баллов, выставляется оценка «5», от 80 до 94 – «4», от 60 до 79 – «3», менее 60 – «2». Посчитайте: количество студентов, получивших оценку «5», «4», «3», «2»; средний балл в группе, максимальный и минимальный баллы. С помощью диалогового окна Условное форматирование выделите все «2» красным цветом. Постройте круговую диаграмму, показывающую процентное соотношение оценок в группе. |