информатика. Вопрос Понятие, структура и задачи информатики
 Скачать 6.17 Mb.
|
Вопрос 5. Проведение математических вычислений в таблицах MS Excel.Табличный процессор MS Excel позволяет проводить математические операции над данными, хранящимися в таблицах. При этом роль переменных в математических выражениях (a, b, c,…x, y, z и т.д.) выполняют адреса ячеек, в которых хранятся значения этих переменных. Например, если в ячейке А1 хранится значение переменной x, а в ячейке В1 хранится значение переменной y, то выражение для вычисления суммы z=x+y в Excel будет выглядеть так: =A1+B1. А результат вычисления будет помещен в ту ячейку, в которой записана приведенная формула. Заметим, что любое математическое (а также логическое) выражение начинается со знака равно. Вводить математические и логические выражения можно прямо в ячейку или в строку формул, так как вводимые в ячейку выражения (и данные) дублируются в строке формул. Проделаем упражнение по проведению в таблице Excel элементарных арифметических операций. 1. Создадим таблицу по образцу рис. 88.  Рис. 88. Таблица арифметической операции Сложение 2. Заполним последующие строки таблицы произвольными значениями переменных х и у для вычитания, умножения, деления и возведения в степень. В ячейки столбца D введем соответствующие формулы арифметических действий. 3. Результат показан на рис. 89.  Рис. 89. Таблица арифметических операций Обратите внимание на содержание строки формул. Если в активной ячейке хранятся числовые или текстовые значения, они дублируются в строке формул. А если в активной ячейке хранится математическое выражение (начинающееся со знака равно), то в строке формул показано это выражение, а в самой ячейке (не обязательно активной) показан результат его вычисления. Копирование формул. В таблицах MS Excel можно копировать не только данные, но и хранящиеся в ячейках формулы. Действия по копированию формул такие же, как и по копированию данных (см. вопрос 4), а именно, либо кнопками Копировать–Вставить из группы Буфер обмена, либо движением курсора, подцепив маркер заполнения. Однако при копировании и переносе формул в другие ячейки имеется одна особенность. В процессе данной операции Excel самостоятельно (без участия пользователя) изменяет в формулах адреса исходных ячеек в соответствии с величиной сдвига по строкам и столбцам. Например, если в приведенном выше примере (см. рис. 88) скопировать формулу сложения из ячейки D2 и вставить в ячейку D3 (сдвиг на одну строку), получим вместо исходного выражения =A2+C2 новую формулу =A3+C3. То есть Excel самостоятельно изменил номер строки во вставляемой формуле. И это оправданно: для вычисления суммы использовались данные из новой строки. Иногда такая особенность поведения очень полезна, например, если нужно в таблице (рис. 87) посчитать возраст сотрудников. Тогда формулу вычисления возраста достаточно вставить лишь в строку первого сотрудника, а затем скопировать ее для всех остальных. При этом в каждой следующей строке формула будет использовать дату рождения следующего сотрудника. Аналогичное явление можно наблюдать и при сдвиге по столбцам. Например, вставим ту же скопированную формулу в ячейку Е3. Новое выражение будет иметь вид =В3+D3. То есть изменились и имена исходных столбцов, и номер исходной строки в соответствии со сдвигом. В этом случае смысл вычисления потерян, поскольку в измененных адресах ячеек хранятся совсем другие данные. Поэтому в ряде случаев требуется зафиксировать исходные адреса ячеек в копируемых и переносимых формулах. Например, для создания таблицы умножения следует зафиксировать имя столбца (левого), содержащего значения первого сомножителя, и номер строки (верхней), содержащей значения второго сомножителя. Для фиксации адреса в формулу вставляется знак $ перед именем того столбца или номером той строки, которые в формуле не должны изменяться. Так, для создаваемой таблицы умножения формула для первой (результатной) ячейки таблицы (1х1) будет выглядеть так: =$A2*B$1 (рис. 90).  Рис. 90. Фиксация адресов ячеек в формуле таблицы умножения Далее следует завершить ввод формулы и протянуть курсором маркер заполнения по всем ячейкам таблицы умножения вниз и вправо. Адреса ячеек, содержащие знак $, называются абсолютными ссылками в отличие от обычных адресов, которые называются относительными ссылками. Встроенные или стандартные функции MS Excel.Табличный процессор MS Excel имеет огромное количество готовых стандартных функций (математических, логических, финансовых и др.), которые можно использовать при проведении вычислений. Чтобы вызвать необходимую стандартную функцию следует установить курсор в ту ячейку, куда будет помещен результат вычисления, и щелкнуть кнопку fx (Вставить функцию) в строке формул. При этом открывается диалоговое окно «Вставка функции» (рис. 91).  Рис. 91. Диалоговое окно «Вставка функции» Все встроенные функции распределены по категориям: математические, статистические, финансовые и т.д. (см. рис. 91). После выбора категории в одноименном поле окна «Вставка функции» (в рассматриваемом примере выбрали категорию Математические), в разделе «Выберите функцию» открывается список имеющихся функций выбранной категории. После выбора требуемой функции (например, десятичный логарифм) нажатие ОК открывает окно «Аргументы функции», в котором следует ввести необходимые значения аргументов (рис. 92).  Рис. 92. Диалоговое окно «Аргументы функции» После ввода значений аргументов функции результат вычисления показывается в окне «Аргументы функции» (см. рис. 92), а после закрытия окна кнопкой ОК получаем результат вычисления в активной ячейке. Исчисление временных интервалов в Excel. В  ычисление значений временных интервалов в Excel имеет некоторые особенности. Так, даты хранятся в виде последовательных номеров. Например, день 1 января 1900 г. имеет номер 1, а 1 января 2017 г. – 42736, и интервал между этими датами составляет 42736 дней. При вводе в ячейку таблицы значения некоторой даты (в формате даты), Excel переводит ее в число (дней), равное разности между вводимой датой и 01.01.1900 г. То есть, вычисляет число дней, прошедших с 01.01.1900 г. до вводимой даты, и хранит в памяти именно это число. Его можно увидеть, если ячейке, содержащей дату, присвоить числовой формат.Чтобы вычислить возраст человека (в днях) в Excel надо из сегодняшней даты вычесть дату рождения. Если полученный результат поделить на число дней в году, получим значение возраста в годах. Точно так же вычисляют стаж работы и другие временные интервалы. Значение сегодняшней даты дает встроенная функция «Сегодня» из категории «Дата и время» в диалоговом окне «Вставка функции» (Рис. 93. Функция Сегодня в категории Дата и время) Продемонстрируем использование функции Сегодня на примере вычисления возраста сотрудников во вновь созданной таблице, содержащей следующие столбцы: №, ФИО (Фамилия и инициалы), Дата рождения, Возраст. Ячейкам столбца Возраст назначим формат Числовой. Выполним следующие действия. 1. Щелкнем курсором в ячейке первого сотрудника в столбце «Возраст», чтобы сделать ее активной. 2. Щелкнем кнопку fx (Вставить функцию) в строке формул. 3. В открывшемся диалоговом окне «Вставка функции» (см. рис. 8.22) выберем категорию «Дата и время», а затем функцию «Сегодня»и щелкнем кнопку ОК. 4. Заметим, что функция Сегодня не имеет аргументов, что подтверждается содержимым диалогового окна «Аргументы функции», в котором нужно просто подтвердить ввод, щелкнув ОК (рис. 94).  Рис. 94. Подтверждение ввода функции Сегодня в диалоговом окне «Аргументы функции» 5. В активной ячейке, а также в строке формул появится запись выражения =Сегодня(). Щелкнем курсором в строке формул после скобок. Дальнейшие действия по созданию окончательного математического выражения будем производить в строке формул. 6. Вставим знак «минус» с клавиатуры и щелкнем в ячейке, соответствующей дате рождения первого сотрудника. В строке формул появится следующая запись выражения: =Сегодня()-С2. Это выражение дает возраст сотрудника в днях. 7.Полученный результат надо поделить на число дней в году (365,25 с учетом високосного года). Окончательное математическое выражение должно иметь следующий вид: =(Сегодня()-С2)/365,25. 8. Завершим ввод щелкнув кнопку Ввод (галочка) в строке формул. В ячейке D2 появится окончательный результат – возраст сотрудника в годах. Убедитесь, что ячейкам столбца Возраст назначен формат Числовой. 9. Чтобы заполнить все ячейки столбца «Возраст» надо скопировать формулу вычисления возраста из активной ячейки D2 и вставить ее во все остальные ячейки столбца. Для этого следует подцепить курсором и протащить вниз до конца столбца маркер заполнения активной ячейки D2 (рис. 95).  Рис. 95. Таблица сотрудников с вычисление возраста Точно таким же способом вычисляется и стаж работы сотрудника, исходя из даты поступления на работу. Задания для самостоятельной работы. 1. Составить таблицу вычисления результатов возведения в степень. Основание степени меняется от 1 до 10 с шагом 1 и показатель степени от 1 до 6 с тем же шагом. 2. Составить таблицу соответствия углов в градусах углам в радианах. Углы в градусах: 0, 30, 45, 60, 90, 120, 180, 270, 360. 3. Составить таблицу соответствия между весом в фунтах и весом в килограммах для значений 2, 5, 10, 20, 50, 100 фунтов (1 фунт = 453 грамма). 4. В рассмотренной выше таблице (см. рис. 87) создать новый столбец «Стаж» (после столбца «Дата приема на работу»), составить выражение для вычисления стажа работы и заполнить все ячейки нового столбца. 5. В той же таблице создать новый столбец «Налог» (после столбца «Оклад») и рассчитать величину подоходный налога (13 % от оклада). 6. Подготовить таблицу для вычисления диагонали, периметра и площади прямоугольника по заданным сторонам – А и В. 7. Создать таблицу для вычисления длины окружности и площади круга по заданной величине радиуса R. 8. Оформить лист Excel для вычисления значений функции y=3,5(a+b)x2+5,8x+3a при значениях х, изменяющихся от 1 до 10 с шагом 1, и произвольных значениях констант а и в. |