Ссылки на ячейки.
При выполнении вычислений в таблице ссылки на ячейки таблицы имеют вид a1, a2, b1, b2 и так далее, где буква указывает на столбец, а номер представляет строку. В Microsoft Word ссылки на ячейки, в отличие от ссылок Microsoft Excel, всегда являются абсолютными ссылками (абсолютный адрес ячейки – это часть формулы, являющаяся адресом ячейки, и ссылающаяся на данную ячейку независимо от местоположения ячейки с формулой; абсолютный адрес ячейки имеет формат $A$1) и отображаются без значков доллара. Например, ссылка на ячейку a1 в Microsoft Word идентична ссылке $A$1 в Microsoft Excel. Чтобы ссылаться на ячейки в формулах, используйте точку с запятой (;) в качестве разделителя ссылок на отдельные ячейки и двоеточие (:) для разделения первой и последней ячеек, определяющих диапазон. Например, чтобы найти среднее значение ячеек (в нашем случае – средний возраст) в последнюю ячейку (у нас – с3) необходимо ввести формулу =AVERAGE(c2;c3 ) или =AVERAGE(c2:c3 ).
ЗАДАНИЕ 2.6. Создайте шаблон сводной ведомости по образцу.
ЗАДАНИЕ 2. 7. Работа с формулами Создайте формулы по образцу.
(3)
Указания к выполнению задания: Для вставки формул, содержащих знаки суммирования, интегрирования, надстрочные и подстрочные индексы нескольких уровней в MS Word 2010 существует специальная программа – редактор формул (Microsoft Equation 3.0). Для того, чтобы его запустить, выполните следующий алгоритм действий: 1. Установите курсор в то место, куда нужно вставить формулу. 2. Перейдите на вкладку Вставка, выберите в группе Текст команду Объект. В открывшемся диалоговом окне Вставка объекта из предложенного списка выберите команду Microsoft Equation 3.0. Нажмите кнопку ОК. 3. Используя встроенные шаблоны, введите формулу. В MS Word 2007-2013 также существует и другой редактор формул. Его можно активизировать, перейдя на вкладку Вставка и выполнив команду Формула в группе Символы (данная кнопка на ленте будет активна только в том случае, если файл имеет расширение *.docx).
ЗАДАНИЕ 2.8. Работа с фигурами. а) Создайте схему по образцу.
б) Создайте рисунок по образцу.
Раздел «Назначение и функциональные возможности табличного процессора Microsoft Office Excel» ЗАДАНИЕ 3.1. Задание выполняется по вариантам. Решите задачи, используя MS Excel соответственно Вашему варианту. В контрольной работе выполненное задание должно содержать: - текст задачи; - исходную таблицу (создается по тексту задания и содержит не менее 12 произвольных записей, при этом ячейки, в которых будут проводиться вычисления в исходной таблице, должны быть пустыми); - таблицу с полученным решением (числовой вид); - таблицу с полученным решением в формульном виде; - таблицу с выполненной сортировкой данных (сортировка выполняется по двум полям); - построенную диаграмму. Важно! Все таблицы должны быть не распечатанными, а скриншотами (рис. 1), где видны названия столбцов и номера строк!
Вариант 1 Задача 1 В таблице содержится информация о сотрудниках предприятия: ФИО, должность, разряд, отработано дней, оклад. Рассчитать: - премию, исходя из условия: если отработано более 22 дней, размер премии составляет 90% от оклада; для остальных случаев премия составляет 50% от оклада; - подоходный налог в размере 13% от суммы всех начислений; - сумму к выплате каждому сотруднику; - количество сотрудников предприятия, которые имеют 1 или 2 разряд. Выполнить сортировку данных в таблице по полю должность, второе поле сортировки – ФИО. Построить гистограмму по полю к выплате для всех сотрудников.
Задача 2 В таблице содержится информация об успеваемости студентов ВУЗа: ФИО, № группы, результаты сдачи экзаменов (математика, ЭТ, информатика). Рассчитать: - средний балл каждого студента; - начислить стипендию в размере 1500 руб. при условии, что все экзамены сданы без троек, иначе стипендия не назначается; - общую сумму выплат студентам; - количество студентов, имеющих средний бал > 4, но меньше 4,5. Выполнить сортировку данных в таблице по полю № группы, второе поле сортировки – ФИО. Построить гистограмму по полю средний балл для всех студентов.
Вариант 2 Задача 1 В таблице содержится информация о постояльцах гостиницы: ФИО, паспортные данные, дата приезда, дата отъезда, тип номера (одноместный, двухместный и т.д.). Рассчитать: - срок проживания в гостинице; - оплату за гостиницу, исходя из условия: если срок проживания более 7 дней, стоимость оплаты снижается на 5%. В остальных случаях стоимость оплаты – полная. Стоимость одного дня проживания взять равной 950 руб; - количество постояльцев, заселившихся в период с 01.10.2014 по 10.10.2014. Выполнить сортировку данных в таблице по полю тип номера; второе поле сортировки – ФИО. Построить гистограмму по полю оплата за гостиницу для всех постояльцев.
Задача 2 В списке агентства недвижимости, содержится информация об объектах, предлагаемых к продаже: адрес, количество комнат, общая площадь, этаж. Рассчитать: - стоимость квартир, если стоимость 1 кв. м. составляет 40 000 руб.; - комиссионные выплаты агентству, которые составляют 18% от стоимости, для однокомнатных квартир, расположенных на первом этаже; для остальных квартир – 28%. - полную стоимость квартиры (с учетом комиссионных); - количество однокомнатных квартир, расположенных не на первом этаже. Выполнить сортировку данных в таблице по полю количество комнат, второе поле сортировки – адрес. Построить гистограмму по полю к выплате для всех сотрудников.
Вариант 3 Задача 1 В таблице содержится информация о сотрудниках предприятия: ФИО, должность, разряд, отработано дней, оклад. Рассчитать: - премию исходя, из условия: если отработано более 20 дней, размер премии составляет 70% от оклада; для остальных случаев премия составляет 40% от оклада; - подоходный налог в размере 13% от суммы всех начислений; - сумму к выплате каждому сотруднику; - общую сумму, которую выплатят сотрудникам 1 разряда. Выполнить сортировку данных в таблице по полю должность, второе поле сортировки – ФИО. Построить гистограмму по полю к выплате для всех сотрудников.
Задача 2 В списке содержится информация о поступлении на склад, бытовой техники: наименование продукции, марка, страна производитель, закупочная цена, количество. Рассчитать: - сумму, которую заплатили за поступивший товар; - цену продажи, которая превышает закупочную цену на 30%, если товар поступил в количестве более 10 единиц; в остальных случаях цена продажи превышает закупочную на 35%; - на какую сумму продано товара, поступившего в период с 01.09.2014 по 20.12.2014. Выполнить сортировку данных в таблице по полю страна производитель, второе поле сортировки – марка продукции. Построить гистограмму по полю цена продажи для всей поступившей продукции.
Вариант 4 Задача 1 В таблице содержится информация об успеваемости студентов ВУЗа: ФИО, № группы, результаты сдачи экзаменов (математика, история, информатика). Рассчитать: - средний балл каждого студента; - начислить стипендию в размере 1300 руб., при условии, что все экзамены сданы без троек, иначе стипендия не назначается; - общую сумму выплат студентам; - какую сумму по стипендиям нужно выплатить всем отличникам; Выполнить сортировку данных в таблице по полю номер группы, второе поле сортировки – ФИО. Построить круговую диаграмму по полю средний балл для всех студентов.
Задача 2 В списке содержится информация о сотрудниках предприятия: ФИО, должность, год приема на работу, оклад. Рассчитать: - стаж сотрудника; - надбавки к окладу, которые составляют 50% от оклада для сотрудников, стаж которых от 10 лет и более, для остальных – 30%; - подоходный налог в размере 13% от суммы всех начислений; - сумму к выплате каждому сотруднику; - общую сумму премий, которую выплатят сотрудникам 1 и 2 разряда. Выполнить сортировку данных в таблице по полю должность, второе поле сортировки – ФИО. Построить график по полю стаж для всех сотрудников.
Вариант 5 Задача 1 В списке содержится информация о поступлении на склад, бытовой техники: наименование продукции, марка, страна производитель, закупочная цена, количество, дата поступления. Рассчитать: - цену продажи, которая превышает закупочную цену на 25%, если товар поступил в количестве более 20 единиц; в остальных случаях цена продажи превышает закупочную на 40%; - стоимость поступившего товара (с учетом количества); - на какую сумму продано товара марки «LG». Выполнить сортировку данных в таблице по полю наименование продукции, второе поле сортировки – страна производитель. Построить гистограмму по полю стоимость для всей поступившей продукции.
Задача 2 В таблице содержится информация о постояльцах гостиницы: ФИО, паспортные данные, дата приезда, дата отъезда, тип номера (одноместный, двухместный и т.д.). Рассчитать: - срок проживания в гостинице; - оплату за гостиницу, исходя из условия: если номер одноместный, стоимость проживания в сутки равна 1200 руб., для остальных номеров – 1000 руб. в сутки; - сумму, которую выручила гостиница за проживание постояльцев в период с 15.10.2014 по 31.10.2014. Выполнить сортировку данных в таблице по полю тип номера, второе поле сортировки – срок проживания. Построить гистограмму по полю оплата за гостиницу для всех постояльцев.
Вариант 6 Задача 1 В списке содержится информация о продаже продукции, производимой предприятием: наименование товара, цена продажи единицы товара, количество продаваемого товара. Цена продажи единицы товара снижается на 5%, если количество продаваемого товара более 100 единиц, Рассчитать: - новую цену продажи; - налог на добавленную стоимость (20% от новой цены продажи); - сумму продажи товара (с учетом налога на добавленную стоимость, и количества продаваемого товара). - количество продукции марки «Samsung». Выполнить сортировку данных в таблице по полю наименование товара, второе поле сортировки – количество продаваемого товара. Построить график по полю новая цена для всех видов товаров.
Задача 2 В таблице туристического агентства содержится информация: страна, продолжительность тура (дн.), стоимость одного дня ($), стоимость перелета ($). Рассчитать: - полную стоимость путевки (с учетом перелета), учитывая, что если путевка берется более чем на 12 дней, скидка на стоимость одного дня составляет 2%. В остальных случаях путевка продается без скидки; - провести перерасчет полной стоимости путевки из $ в рубли, при заданном курсе (курс доллара задать самостоятельно); - сумму, которую выручит агентство за продажу путевок во Францию с продолжительностью тура более 12 дней. Выполнить сортировку данных в таблице по полю страна, второе поле сортировки – продолжительность тура. Построить гистограмму по полю стоимость путевки в руб. для всех записей.
Вариант 7 Задача 1 В списке библиотеки содержится информация о литературе, выданной читателям: название книги, № читательского билета, дата выдачи, дата возврата. Рассчитать: - срок нахождения книги у читателя; - штраф в размере 3 рублей за каждый день сверх установленного срока (срок – 25 дней); - количество читателей библиотеки; - сумму штрафов, начисленных в период с 01.01.2014 по 31.01.2014. Выполнить сортировку данных в таблице по полю название книги, второе поле сортировки – № читательского билета. Построить график по полю срок нахождения книги для всех читателей.
Задача 2 В таблице содержится информация о постояльцах гостиницы: ФИО, паспортные данные, дата приезда, дата отъезда, тип номера (одноместный, двухместный и т.д.). Рассчитать: - срок проживания в гостинице; - оплату за гостиницу, исходя из условия, что если срок проживания более 5 дней, стоимость оплаты снижается на 3%; в остальных случаях стоимость оплаты – полная. Стоимость одного дня проживания взять равной 1100 руб.; - количество постояльцев, проживающих в одноместных номерах. Выполнить сортировку данных в таблице по полю оплата за гостиницу, второе поле сортировки – тип номера. Построить гистограмму по полю оплата для всех проживающих.
Вариант 8 Задача 1 В таблице имеется информация о товарах, предлагаемых к продаже комиссионным магазином: наименование товара, дата поступления товара, цена продажи. Рассчитать: - срок хранения товара (от текущего дня); - уценку, которая составляет 5% от цены продажи, если товар хранится более 30 дней; - новую цену продажи. - количество товара, цена продажи которого > 100 руб., но < 200 руб. Выполнить сортировку данных в таблице по полю наименование товара, второе поле сортировки – дата поступления. Построить график по полю уценка для всех товаров.
Задача 2 В списке содержится информация о поступлении на склад, бытовой техники: наименование продукции, марка, страна производитель, закупочная цена, количество, дата поступления. Рассчитать: - цену продажи, которая превышает закупочную цену на 30%, если товар поступил в количестве более 50 единиц; в остальных случаях цена продажи превышает закупочную на 45%; - стоимость поступившего товара (с учетом количества); - на какую сумму продано товара, поступившего в период с 01.07.2014 по 15.07.2014. Выполнить сортировку данных в таблице по полю страна производитель, второе поле сортировки – марка. Построить график по полю цена продажи для всех видов продукции.
Вариант 9 Задача 1 В списке библиотеки содержится информация о литературе, выданной читателям: название книги, № читательского билета, дата выдачи, дата возврата. Рассчитать: - срок нахождения книги у читателя; - штраф в размере 2 рублей за каждый день сверх установленного срока (срок – 30 дней). - сумму штрафов; - количество читателей с фамилией «Иванов». Выполнить сортировку данных в таблице по полю название книги, второе поле сортировки – дата выдачи. Построить гистограмму по полю срок нахождения книги для всех читателей.
Задача 2 В таблице содержится информация о постояльцах гостиницы: ФИО, паспортные данные, дата приезда, дата отъезда, тип номера (одноместный, двухместный и т.д.). Рассчитать: - срок проживания в гостинице; - оплату за гостиницу, исходя из условия: для одно- и двухместных номеров стоимость проживания в сутки равна 1100 руб., для остальных номеров – 1300 руб. в сутки; - количество постояльцев, проживающих в двухместных номерах. Выполнить сортировку данных в таблице по полю оплата за гостиницу, второе поле сортировки – тип номера. Построить гистограмму по полю оплата для всех проживающих.
Вариант 10 Задача 1 В таблице туристического агентства содержится информация: страна, продолжительность тура (дн.), стоимость одного дня ($), стоимость перелета ($). Рассчитать: - полную стоимость путевки (с учетом перелета), учитывая, что если путевка берется более чем на 14 дней, скидка на стоимость одного дня составляет 3%. В остальных случаях путевка продается без скидки; - провести перерасчет полной стоимости путевки из $ в рубли, при заданном курсе (значение курса доллара задать самостоятельно); - количество путевок, проданных в Италию, сроком более 10 дней. Выполнить сортировку данных в таблице по полю страна, второе поле сортировки – полная стоимость путевки. Построить график по полю полная стоимость путевки для всех записей.
Задача 2 В таблице содержится информация о поступлении на склад магазина бытовой техники: наименование продукции, марка, страна производитель, закупочная цена, количество. Рассчитать: - сумму, которую заплатили за поступивший товар; - цену продажи, которая превышает закупочную цену на 30%, если товар поступил в количестве более 20 единиц. в остальных случаях цена продажи превышает закупочную на 45%; - количество товара, поступившего в период с 01.10.2014 по 31.10.2014. Выполнить сортировку данных в таблице по полю страна производитель, второе поле сортировки – наименование продукции. Построить график по полю цена продажи для пяти первых записей.
Указания к выполнению задания (теоретические сведения). Вычисления в таблицах Excel производятся с помощью формул. Результат вычисления помещается в ячейку, в которой находится формула. Формула представляет собой совокупность математических операторов, констант, ссылок и функций. Константа – это готовое (не вычисляемое) значение, которое всегда остается неизменным. Приложение Microsoft Excel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок. Арифметические операторы служат для выполнения базовых арифметических операций, таких как сложение (знак «плюс», +), вычитание (знак «минус», –), умножение (звездочка,*), деление чисел (косая черта, слеш, /), возведение в степень (крышка, ^), вычитание процента (знак процента, %). Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ. Среди операторов сравнения выделяют = (знак равенства), > (знак «больше»), < (знак «меньше»), >= (знак «больше или равно»), <= (знак «меньше или равно»), <> (знак «не равно») Текстовый оператор есть только один – Амперсанд (&) – используется для объединения (соединения) одной или нескольких текстовых строк в одну. Операторы ссылок. Для определения ссылок на диапазоны ячеек можно использовать операторы: : (двоеточие) – оператор диапазона, который образует одну ссылку на все ячейки, находящиеся между первой и последней ячейками диапазона, включая эти ячейки; , (запятая) – оператор объединения; объединяет несколько ссылок в одну ссылку; (пробел) – оператор пересечения множеств, используется для ссылки на общие ячейки двух диапазонов. Значения обрабатываются формулой в определенном порядке. Формула в Microsoft Excel всегда начинается со знака равенства (=). Знак равенства свидетельствует о том, что следующие за ним знаки составляют формулу. Элементы, следующие за знаком равенства, являются используемыми в расчетах операндами (например, константами или ссылками на ячейки), которые разделяются операторами вычислений. Формула в Excel вычисляется слева направо в соответствии с порядком, определенным для каждого оператора в формуле. Функция – это разработанная вспомогательная программа, выполняющая конкретные задачи. Все функции делятся на следующие категории: - Финансовые функции; - Дата и время; - Математические; - Статистические; - Ссылки и массивы; - Работа с базой данных; - Текстовые; - Логические; - Проверка свойств и знаний; - Инженерные; - Аналитические; - Совместимость.
Рассмотрим несколько функций категории Математические: ABS (число) – возвращает модуль числа. КОРЕНЬ (число) – возвращает положительное значение квадратного корня. Если число отрицательное, то функция возвращает значение ошибки #ЧИСЛО. ЦЕЛОЕ (число) – округляет до ближайшего меньшего целого. СУММ (число1; число2;…) – суммирует аргументы. ПИ() – возвращает 3,14 SIN (число) – возвращает синус заданного угла COS (число) – возвращает косинус заданного угла TAN (число) – возвращает тангенс заданного угла, где число – это угол в радианах, для которого производится вычисление. Если аргумент задан в градусах, нужно умножить его на ПИ()/180, чтобы преобразовать в радианы: = SIN(30*ПИ()/180)
|
(1)
(2)
