Расчёты с использованием функций

Для выполнения сложных расчетов Excel включает около сотни различных функций, некоторые из них будут описаны ниже. Нужную функцию можно вызвать, набрав ее имя и список аргументов в информационной строке, или выбрать из списка, который выводится на экран при помощи кнопки Вставить функцию .

Далее открывается диалоговое окно Мастер функций (рис.3.10), в котором перечислены категории функций (Математические, Статистические, Финансовые и т.д.), после выбора которых можно указать название функции, относящейся к выбранной категории.

Рис.3.10 Выбор категории функции

Математические функции

Одна из наиболее часто используемых математических функций –суммирование. Вызов функции суммирования с использованием мастера функций сопровождается появлением окна, которое дает возможность правильно ввести функцию и записать ее аргументы (рис. 3.11). Аналогичные окна используются и при вызове других функций Excel. После ввода всех аргументов нажимают кнопку «Ok». Допускается вводить слагаемые либо как числа, либо как адреса ячеек, в которых эти числа содержатся.

Рис.3.11 Окно математической функции Суммирования

На панели вкладки Главная имеется кнопка автосуммирования, помеченная знаком S, упрощающая процедуру вызова указанной функции. При автосуммировании аргументами функции СУММ() являются ячейки, расположенные либо сверху, либо слева от выделенной ячейки и содержащие числовые данные.

Там же быстрый доступ к функциям среднее, максимум, минимум.

Среди прочих математических функций чаще других используются:

СУММКВ(...)–сумма квадратов, аргументами, которой являются числа или адреса ячеек таблицы. Функция возвращает значение суммы квадратов аргументов.

СУММПРОИЗВ(...)–сумма произведений. Ее аргументами являются массивы чисел или адресов ячеек таблицы. функция СУММПРОИЗВ(...) возвращает сумму произведений элементов массивов–аргументов, имеющих одинаковые номера (a1b1c1+a2b2c2+...).

Расчет величины определителя квадратной матрицы производится с использованием функции МОПРЕД. Аргументом является массив ячеек, в котором находятся элементы матрицы. Например, МОПРЕД(А1:С3).

Среди математических функций следует отметить также округление, перевод градусной меры углов в радианную и радианной в градусную, тригонометрические функции и др., выполняемые в соответствии с обычными математическими формулами.

Упражнение

Пример с использованием функций представлен на рис. 3.12 и 3.13, где вычисляются расстояния от начала координат до точек замкнутого теодолитного хода, заданных координатами Х и У.

Рис. 3.12 Вычисление расстояний

Рис. 3.13 Вычисление расстояний с формулами

Здесь формула вычисления расстояния вводится в ячейку D3, а затем копируется в ячейки D4:D9. Затем вычисляются сумма расстояний и среднее расстояние.

Задание 4

Начертите в произвольном масштабе замкнутый полигон с десятью вершинами. В таблицу типа 3.12 внесите номера и координаты вершин полигона в метрах с двумя знаками после запятой (ось x располагается по горизонтали).

Вычислите, как в таблице 3.13, расстояния от каждой вершины полигона до начала координат, сумму расстояний и среднее расстояние.

В следующем столбце таблицы рассчитайте расстояния от k-ой точки (k – ваш номер в журнале) до каждой вершины полигона, как корень из суммы квадратов разностей соответствующих координат.

Вычислите расстояния между каждой парой смежных точек замкнутого полигона, суммарную длину сторон полигона и площадь полигона внутри хода по формулам

Контроль расчета площади выполните по формуле:

Организуйте исходные данные так, чтобы каждую формулу можно было набирать только один раз и затем копировать в нужный диапазон. Для этого перед координатами первой вершины проставьте координаты последней вершины и после координат последней вершины проставьте координаты первой. Формулы следует набирать в основной строке первой вершины и копировать до строки n-ой вершины.

Постройте в Excel график полигона по координатам его вершин.

Убедитесь, что полученный график соответствует исходному рисунку полигона.

Функции даты и времени

Другая группа часто используемых функций–функции даты и времени. Самая простая из этой группы функций–функция Сегодня(), возвращающая дату, определяемую системными часами компьютера. Эта функция не имеет аргументов. Формат даты можно выбирать при помощи команды Формат→ Ячейки. Если в ячейке вместо даты появится бессмысленная комбинация символов, следует поменять формат или увеличить ширину ячейки. Другая аналогичная функция–ТДАТА(). В отличие от Сегодня(), ТДАТА() наряду с текущей датой возвращает и время. Имеются функции, возвращающие год, месяц, день недели и т.д. Для определения интервала между двумя датами достаточно из конечной даты вычесть начальную. Например, = 08.04.97–06.04.97 – ›2. Если даты поменять местами, ответ получится отрицательный. Допускается использование адресов ячеек, содержащих исходную информацию.

Логические функции

Наиболее распространённой логической функцией является функция ЕСЛИ. По своим возможностям она соответствует условному оператору в обычных алгоритмических языках. Форма записи этой функции следующая ЕСЛИ(логическое_ выражение; значение_ если_ истина; значение_ если_ ложь).

Используются также функции:

логическое сложениеИЛИ(логическое_ выражение1; логическое_ выражение2; ..;логическое_ выражениеN);

логическое умножениеИ(логическое_ выражение1; логическое_ выражение2;…; логическое_ выражениеN);

отрицание НЕ(логическое_ выражение).

Упражнение

Дан список студентов и даты их рождения (рис. 3.14, 3.15). Встолбце D вычисляется возраст каждого студента. В столбцах E и F, с использованием логической функции ЕСЛИ, отбираются лица, достигшие 18 лет и не достигшие 18 лет.

Рис.3.14 Расчет и анализ возраста

Рис.3.15 Расчет и анализ возраста с формулами

Столбцы «От 20 до 30» и «От 30 до 40» рассчитайте самостоятельно. Выведите фамилии в случае попадания в диапазон и выведите пробел в случае не попадания в него. Для определения попадания в диапазон используйте логическую функцию И. Например, И(E4>=20;E4<30).