Файл: Вопрос Понятие, структура и задачи информатики.docx

 

Окончательный вид таблицы представлен на рис. 87.



Рис. 87. Окончательный вид таблицы

---

Вопрос 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 имеет огромное количество готовых стандартных функций (математических, логических, финансовых и др.), которые можно использовать при проведении вычислений. Чтобы вызвать необходимую стандартную функцию следует установить курсор в ту ячейку, куда будет помещен результат вычисления, и щелкнуть кнопку f_x (Вставить функцию) в строке формул. При этом открывается диалоговое окно «Вставка функции» (рис. 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.  Щелкнем кнопку f_x (Вставить функцию) в строке формул.

3.  В открывшемся диалоговом окне «Вставка функции» (см. рис. 8.22) выберем категорию «Дата и время», а затем функцию «Сегодня»и щелкнем кнопку ОК.

4.  Заметим, что функция Сегодня не имеет аргументов, что подтверждается содержимым диалогового окна «Аргументы функции», в котором нужно просто подтвердить ввод, щелкнув ОК (рис. 94).



Рис. 94. Подтверждение ввода функции Сегодня в диалоговом окне «Аргументы функции»

 

5.  В активной ячейке, а также в строке формул появится запись выражения =Сегодня(). Щелкнем курсором в строке формул после скобок. Дальнейшие действия по созданию окончательного математического выражения будем производить в строке формул.

6.  Вставим знак «минус» с клавиатуры и щелкнем в ячейке, соответствующей дате рождения первого сотрудника. В строке формул появится следующая запись выражения: =Сегодня()-С2. Это выражение дает возраст сотрудника в днях.

7.Полученный результат надо поделить на число дней в году (365,25 с учетом високосного года). Окончательное математическое выражение должно иметь следующий вид: =(Сегодня()-С2)/365,25.

8.   Завершим ввод щелкнув кнопку Ввод (галочка) в строке формул. В ячейке D2 появится окончательный результат – возраст сотрудника в годах. Убедитесь, что ячейкам столбца Возраст назначен формат Числовой.

---