Контрольная работа: Вычисление функций в Еxcel и построение графиков
Важная группы функций это логические функции (см. Таблица 3).
Таблица 3
| ФУНКЦИЯ | ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ |
| И |
Истина, если значение всех аргументов Истина , в противном случае – Ложь |
| ЕСЛИ |
Первое значение, если проверяемый аргумент равен Истине , и второе значение в противном случае |
| НЕ | Истина , если аргумент имеет значение Ложь , и Ложь , если аргумент имеет значение Истину |
| ИЛИ | Истина , если хотя бы один из аргументов имеет значение истина, и Ложь , если ни один не является истина |
Функции даты и времени (см. Таблицу 4) позволяют создавать формулы, производящие вычисления в зависимости от времени и даты а также выполнять вычисления с датами. Excel использует специальные числа (значения даты) для хранения и оперирования датами. Для преобразования обычных дат в значения, используемые в Excel, можно использовать функцию ДАТА . Функции ДЕНЬ , ГОД и МЕСЯЦ преобразуют значения дат в понятные человеку.
Таблица 4
| ФУНКЦИЯ | ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ |
| ДАТА | Значение общеиспользуемой записи даты в значения, используемые в Excel |
| ДЕНЬ | Целочисленная переменная, соответствующая дню месяца (подобные функции: ГОД , МЕСЯЦ , ЧАС , МИНУТЫ И СЕКУНДЫ ) |
| ТДАТА | Значение даты и времени на PC. Используйте СЕГОДНЯ для возвращения только даты и ВРЕМЯ – только времени |
| ДЕНЬНЕД | День недели |
| РАБДЕНЬ | Дата следующего рабочего дня после указанного |
| ДОЛЯГОДА | Десятичная дробь, означающая часть года, равную интервалу между датами. Удобно использовать для расчета премий сотрудникам |
Функции поиска и ссылок (см. таблицу 5) задают ячейки в диапазоне рабочего листа или значения в массиве. Функции из категории базы данных – вспомогательные, ими удобно пользоваться, если рабочий лист верно оформлен.
Таблица 5
| ФУНКЦИЯ | ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ |
| БИЗВЛЕЧЬ | Указанное значение из списка |
| ЧИСЛОСТ | Количество столбцов в диапазоне |
| ИНДЕКС | Значение указанной ячейки в диапазоне или адрес ячейки |
| АДРЕС | Возвращает ссылку на одну ячейку в рабочем листе в виде текста |
| ПОИСКПОЗ | Расположение предмета поиска в диапазоне (но не значения) |
| СМЕЩ | Ссылка на диапазон или ячейку, отстоящую на заданное число строк или столбцов от указанного диапазона |
Математические и тригонометрические функции (см. Таблицу 6) выполняют математические расчеты.
Таблица 6
| ФУНКЦИЯ | ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ |
| ABS | Модуль аргумента |
| COS | Косинус аргумента. В Excel присутствует весь набор тригонометрических функций |
| ФАКТР | Факториал аргумента |
| LOG | Логарифм аргумента (используют LN для получения натурального логарифма) |
| ПИ | Число ПИ |
| ПРОИЗВЕД | Произведение всех аргументов (можно указать диапазон ячеек, как в случае с функцией СУММ ) |
| РИМСКОЕ | Римские числа |
| ОКРУГЛ | Округленное значение до указанного числа или знака после запятой |
| ЗНАК | 1 – если аргумент положителен, 0 – если равен нулю, -1 – если аргумент отрицателен |
| КОРЕНЬ | Квадратный корень аргумента |
| СУМПРОИЗВ | Перемножает соответствующие элементы в двух или более массивах и затем суммирует результаты |
| ЦЕЛОЕ | Целая часть аргумента – отбрасывается часть после десятичной точки |
Текстовые функции (см. Таблицу 7) приводят текстовые переменные к нужному виду или выводят текст на основании числовых аргументов.
Таблица 7
| ФУНКЦИЯ | ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ |
| ПЕЧСИМВ | В тексте удаляются все непечатаемые символы |
| РУБЛЬ | Превращает числовое значение в текст, в денежном формате, с двумя знаками после десятичной точки |
| ЛЕВСИМВ | Указанное количество символов слева в строке (ПСТР и ПРАВСИМВ выбирают символы из центра или с правой стороны вводимой строки) |
| ДЛСТР | Число символов в строке |
| СТРОЧН | Текст строки переводится в нижний регистр |
| ТЕКСТ |
Превращает цифры в соответствующий текст, оформленный по указанному образцу |
| СЖПРОБЕЛЫ | Удаляет лишние пробелы (одиночные пробелы между словами сохранены) |
Поскольку главная задача Excel – помощь при ведении дел, в программе содержатся сотни функций для расчета долгов, инвестиций и подобных экономических величин (см. Таблицу 8).
Таблица 8
| ФУНКЦИЯ | ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ |
| ACCRINT | Вычисление процентных ставок по процентам к уплате |
| ПС | Возвращает приведенную стоимость инвестиций |
| СКИДКА | Возвращает норму скидки для ценных бумаг |
| БС | Прибыли от инвестиций при постоянной процентной ставке |
| ИНОРМА |
Возвращает процентную ставку для полностью инвестированных ценных бумаг |
| ПРПЛТ | Сумма платежей процентов по инвестициям или долгам |
| ДОХОД | Доход от ценных бумаг |
| ПЛТ | Возвращает сумму периодического платежа |
| ЧИСТНЗ | Чистая текущая стоимость инвестиций |
| ДОХОД | Начисления по процентным ставкам |
Около 80 статистических функций Excel используются не для математических вычислений, а для статистического анализа любых сложных данных (таблица 9). Ниже представлены некоторые из существующих функций, которые пригодятся даже ярым поклонникам математических расчетов.
Таблица 9
| ФУНКЦИЯ | ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ |
| СРЗНАЧ | Среднее арифметическое аргументов |
| ХИ2ТЕСТ | Тест независимости данных, основанный на хи-квадрат распределении |
| ДОВЕРИТ | Доверительный интервал для популяции |
| СЧЕТ | Число ячеек в указанном диапазоне, содержащих цифровые значения |
| ФИШЕР | Преобразование Фишера |
| ЧАСТОТА | Массив, содержащий пределы, в которых изменяются значения |
| НАИБОЛЬШИЙ | Значение в специальных последовательностях (для определения, например, третьих по объему продаж СD в третьем квартале). НАИМЕНЬШИЙ дает противоположный результат |
| МАКС | Наибольшее значение в последовательности. МИН дает наименьшее значение |
| МЕДИАНА | Медиана аргумента |
| MORE | Наиболее часто встречающееся значение |
| НОРМРАСП | Нормальное распределение |
| СТАНДОТКЛОН | Оценка стандартного отклонения |
Многие люди использует Excel в качестве менеджера баз данных простых файлов (без ссылок), сохраняя данные в строках и столбцах рабочего листа (столбец является полем, а строка – записью). В категории управления баз данными находится более десяти функций (см. таблицу 10). В большинстве случаев им требуется указать диапазон, содержащий базу данных, поля и критерии отбора.
Таблица 10
| ФУНКЦИЯ | ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ |
| СЧЕТЕСЛИ | Количество записей, соответствующих критерию |
| ДМАКС | Максимальное число среди соответствующих критерию |
| СУММЕСЛИ | Сумма значений в поле для всех записей, соответствующих критерию |
Определение именованных констант
Стандартный метод определения константы – поместить ее значение в ячейку и обращаться к этой ячейке, когда возникает необходимость использования константы в формуле. При изменении константы достаточно ввести в ячейку новое значение, и все зависящие результаты будут вычислены автоматически.
Если значение действительно неизменно, можно определить именованную константу, существующую только в памяти Excel, а не в ячейке рабочего листа. Выбрать команду Вставка / Имя / Присвоить .
В диалоговом окне Присвоение имени , ввести имя константы и ее значение в поле Формула , удалив существующие значения (не удалять знак равенства). В этом диалоговом окне можно просмотреть все именованные константы.
С помощью Microsoft Excel можно не только выполнить расчеты, вычислять логарифмические, тригонометрические функции, и на основании этих расчетов строить графики.