Размещено на http://www.allbest.ru/

Курсовой проект

Встроенные функции Excel: СУММ, СРЗНАЧ, МИН, МАКС

Введение

массив надстройка программирование

Я взял эту тему для курсового проекта т.к функции этой программы очень удобны, и пригодятся мне для последующего обучения. Microsoft Excel средство для работы с электронными таблицами, намного превышающее по своим возможностям существующие редакторы таблиц, первая версия данного продукта была разработана фирмой Microsoft в 1985 году. Табличные процессоры - удобный инструмент для экономистов, бухгалтеров, инженеров, научных работников - всех тех, кому приходится работать с большими массивами числовой информации. Microsoft Excel - это программа управления электронных таблицами общего назначения, которая используется для вычислений, организации и анализа деловых данных.

1. Общее представление о функциях MS Excel

Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций вы можете использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel. Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если вы нарушите правила синтаксиса, Excel выдаст сообщение о том, что в формуле имеется ошибка.

Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.

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

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

Функции - один из основных функционалов MS-Excel. По сути, функция - это заранее подготовленный кусок кода, выполняющий какую-то конкретную задачу. Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах.

2. Категории встроенных функций

Все встроенные функции Excel разделены на несколько категорий. Например, функции категории Текстовые используются, в основном, для работы с текстовыми строками. Математические функции, с помощью которых можно составлять различные математические выражения, отнесены к категории Математические и т.п. Основные категории функций перечислены ниже. Все названия категорий соответствуют названиям команд, расположенным в группе Библиотека функций на вкладке Формулы. Полный список всех встроенных функций Excel приводится в приложении А.

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

Категория Логические содержит семь функций, в том числе функции ЕСЛИ и ЕСЛИОШИБКА. Использование логических функций делает формулы более гибкими, а использование функции ЕСЛИ наделяет формулу способностью “принимать решения”. Благодаря этому функция ЕСЛИ стала самой используемой логичекой функцией.

Функция ЕСЛИОШИБКА имеется в библиотеке встроенных функций только в Excel 2010 (2007). Об этом необходимо помнить, если ваши рабочие книги используются в разных версиях Excel.

Текстовые функции

Текстовые функции предназначены для обработки текста. Например, с помощью функций ПРОПНАЧ или ДЛСТР можно изменить регистр или определить длину текстовой строки. Используя текстовые функции, можно объединить несколько строк в одну или, наоборот, разделить одну текстовую строку на несколько строк. Например, формула =СЦЕПИТЬ(A1;A2) объединяет две текстовые строки, содержащиеся в ячейках A1 и A2, в одну.

Функции категории Проверка свойств и значений

Функции этой категории часто называют информационными. Функция ЯЧЕЙКА этой категории позволяет получить информацию о ячейке. Другие информационные функции проверяют выполнение какоголибо условия и, в зависимости от результата, возвращают значение ИСТИНА или ЛОЖЬ (или числовое значение). Например, с помощью функции ЕЧИСЛО можно проверить, данные какого типа содержит ячейка. Если в ячейке содержится число, функция ЕЧИСЛО возвращает логическое значением ИСТИНА, в противном случае функция возвращает логическое значение ЛОЖЬ.

Функции Дата и время

Функции, принадлежащие к этой категории, предназначены для работы со значениями даты и времени. По сути, эти функции работают с числовыми значениями, потому что дата и время в Excel являются числами, к которым применен один из числовых форматов даты и времени. С помощью функции этой категории можно вычислить количество рабочих дней между двумя датами (функция ЧИСТРАБДНИ), преобразовать дату в год (функция ГОД), месяц (функция МЕСЯЦ) или день недели (функция ДЕНЬНЕД) и т.п.

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

Математические функции позволяют выполнять простые и сложные вычисления. В категорию Математические входят тригонометрические функции, например SIN, COS, ACOS; функции, выполняющие арифметические действия, например СУММ, ПРОИЗВЕД, ЧАСТНОЕ; и многие другие функции. К этой же категории относятся функции, позволяющие работать с массивами значений или матрицами, -- МУМНОЖ, МОПРЕД и МОБР, а также функции АГРЕГАТ и ПРОМЕЖУТОЧНЫЕ.

ИТОГИ, которые используются для получения итоговых значений (суммы, среднего арифметического, минимального или максимального значений и т.п.) в массивах данных или списках.

Функции категории Ссылки и массивы

Эти функции (другое название этой категории -- функции просмотра) используются для поиска значений в списках или таблицах (например, функции ПРОСМОТР, ВПР или ГПР), а также для получения дополнительной информации (например, функции ЧСТРОК, ЧИСЛСТОЛБ и ОБЛАСТИ возвращают количество строк, столбцов или областей в ссылке на диапазон). Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ извлекает данные, хранящиеся в сводной таблице. Некоторые функции этой категории позволяют манипулировать диапазоном данных. Например, функция ТРАНСП транспонирует диапазон ячеек на рабочем листе.

Статистические функции и категория

Статистические функции предназначены для проведения статистического анализа.

С помощью статистических функций можно вычислить статистические характеристики набора данных, такие как среднее арифметическое значение (функция СРЗНАЧ), стандартное отклонение (функция СТАНДОТКЛОН.Г или СТАНДОТКЛОН.В) или дисперсия (функции ДИСП.В и ДИСП.Г), построить статистический ряд (функция ЧАСТОТА), вычислить доверительные интервалы (функции ДОВЕРИТ.НОРМ и ДОВЕРИТ.СТЬЮДЕНТ). В категории Статистические имеются также функции для проверки статистических гипотез, вычисления известных статистических распределений и проведения корреляционного и регрессионного анализа.

В версии Excel 2010 изменились алгоритмы и названия некоторых статистических функций. Например, функция СЛЧИС использует новый алгоритм получения случайных чисел; название этой функции не изменилось. Для вычисления дисперсии вместо функций ДИСП и ДИСПР используются функции ДИСП.В и ДИСП.Г соответственно. Функции ДИСП и ДИСПР остались для совместимости с предыдущими версиями и находятся в категории Совместимость. В Excel 2010 для вычисления дисперсии можно использовать как функции ДИСП.В и ДИСП.Г, так и функции ДИСП и ДИСПР.

Инженерные функции

Инженерные функции используются, главным образом, в инженерных и научных расчетах. К инженерным функциям относятся:

* функции для работы с комплексными числами;

* функции, выполняющие преобразование чисел из одной системы счисления в другую (десятичную, восьмеричную, шестнадцатеричную и двоичную);

* функции, выполняющие преобразование величин из одной системы мер и весов в другую.

Финансовые функции

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

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

Функции для работы с базами данных

Функции этой категории используются для анализа данных, содержащихся в обычных электронных таблицах (списках данных) и таблицах Excel. С помощью функций баз данных можно быстро получить нужную информацию, например подсчитать количество записей в базе данных (функция БСЧЁТ) или вычислить сумму значений (функция БДСУММ), удовлетворяющих некоторому условию. На ленте команд эта категория функций не отображается.

Аналитические функции и куб данных OLAP

В традиционных базах данных типа OLTP (Online Transaction Processing -- оперативная обработка транзакций), например Access или SQL Server, данные хранятся в нескольких таблицах, связанных между собой; каждая ячейка таблицы содержит один элемент данных. В Excel можно создать подключение к такой базе данных и получать оперативные данные из нее, например, через сводные таблицы. Однако, если в базе данных содержится несколько миллионов (или миллиардов) записей, Excel не в состоянии их обработать. В этом случае используют более эффективные средства обработки информации, например программу Microsoft SQL Server 2000, с помощью которой можно быстро обрабатывать большие объемы данных. Эту же программу можно использовать для создания хранилища данных и кубов OLAP.

Аббревиатура OLAP расшифровывается как Online Analitical Processing -- Оперативный анализ данных. Сервер OLAP содержит средства, позволяющие связываться с хранилищем данных. Также сервер OLAP обрабатывает записи из хранилища данных и преобразует таблицы в куб данных. Таким образом, куб данных представляет собой многомерную структуру данных. В отличие от таблиц, используемых в реляционных базах данных, каждая ячейка куба содержит множество элементов. Для работы с кубом данных необходимо подключиться к нему и создать сводную таблицу.

Аналитические функции предназначены для работы с кубом данных OLAP. Например, функция КУБЭЛЕМЕНТ возвращает элемент или кортеж из куба, функция КУБСВОЙСТВОЭЛЕМЕНТА возвращает значение свойства элемента из куба.

Пользовательские функции

К пользовательским функциям относятся все функции, которые были созданы пользователем. При вставке пользовательских функций в нижней части диалоговых окон Аргументы функции и Мастер функций - шаг 1 из 2 не отображаются краткая справка о пользовательской функции и краткое описание аргументов этой функции.

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

Функции надстроек и программирования объектов

В Excel имеется еще одна категория функций -- Внешние функции. Внешние функции загружаются с программами надстройки и обычно используются для получения каких-либо данных из внешних источников. К этой категории, например, относятся функции, преобразующие значение из национальных валют в евро и наоборот. Преобразование валют и форматирование данных в единицах евро становится доступным в Excel 2010 после установки надстройки “Инструменты для евро”. На ленте команд эти функции отображаются на вкладке Формулы в группе команд Решения (Solutions) после установки надстройки.

3. Функции СУММ, СРЗНАЧ, МИН, МАКС

Функции СУММ, СРЗНАЧ, МАКС, МИН, предназначены для работы с наборами значений, содержащихся в некотором диапазоне ячеек. В диалоговом окне Мастер функций - шаг 1 из 2 эти функции можно найти в категории Статистические. Функция СУММ относится к математическим и предназначена для подсчета суммы в некотором диапазоне ячеек. Несмотря на то что эти функции относятся к разным категориям и вычисляют различные параметры для некоторого набора числовых значений, между ними много общего. Они имеют похожий синтаксис и часто используются в вычислениях. Excel, как видно из всего сказанного выше очень мощный инструмент для решения задач с использованием различных функций.

Функция СУММ

Функция СУММ, вне всяких сомнений, является наиболее используемой функцией Excel. Функция СУММ имеет следующий синтаксис:

СУММ(число1;число2; ...;число255)

где число1, число2 -- количество аргументов (от 1 до 255), сумму которых необходимо вычислить.

Аргументами функции СУММ должны быть числа, заданные в виде конкретных значений, ссылок на ячейки или диапазоны ячеек, массивов констант. Вместо ссылок можно использовать имена ячеек или диапазонов ячеек. Массив констант представляет собой массив чисел, заключенных в фигурные скобки, например {1;2;3} или {1:2:3}. Числа в фигурных скобках должны разделяться точкой с запятой либо двоеточием.

Предположим, что в ячейках A1, A2 и A3 содержатся значения 1, 2 и 3. Тогда формулы =СУММ(1;2;3), =СУММ(A1;A2;A3), =СУММ(A1:A3), =СУММ(Данные) и =СУММ({1;2;3}) возвращают один и тот же результат -- 6. Здесь имя Данные присвоено диапазону A1:A3. В первой формуле в качестве аргументов функции СУММ используются ссылки на ячейки, во второй формуле аргументом функции СУММ является массив значений.

С помощью функции СУММ можно подсчитать следующее.

§ Сумму чисел, содержащихся в одном диапазоне ячеек. В этом случае достаточно задать один аргумент (число1). Для указания ссылки на непрерывный диапазон ячеек используется двоеточие (:) в качестве разделителя между первой и последней ячейками диапазона. Например, на рис. 6.25 формула =СУММ(C5:E5) в ячейке F5 возвращает сумму чисел, содержащихся в диапазоне ячеек C5:E5.

· Сумму чисел, содержащихся в нескольких диапазонах (как смежных, так и несмежных). В этом случае можно задать до 255 аргументов. Для создания ссылки на два несмежных диапазона используется оператор объединения диапазонов, обозначаемый точкой с запятой (;). Например, формула =СУММ(C5:C7;C9:C11; C13:C15;C17:C19) в ячейке C24 (рис. 6.25) возвращает сумму чисел, которые находятся в диапазонах C5:C7, C9:C11, C13:C15 и C17:C19.

§ Сумму чисел, содержащихся в диапазоне, который является пересечением диапазонов, заданных в качестве аргументов. Для создания ссылки на такой диапазон используется оператор пересечения диапазонов -- пробел. Например, формула в ячейке C21=СУММ(C$5:C$7 $C5:$E5;C$9:C$11 $C9:$E9;C$13:C$15 $C13:$E13;C$17:C$19 $C17:$E17) возвращает сумму чисел, которые содержатся на пересечении диапазонов: C5:C7 и C5:E5 (ячейка C5), C9:C11 и C9:E9 (ячейка C9), C13:C15 и C13:E13 (ячейка C13), C17:C19 и C17:E17 (ячейка C17), т.е. заданная таким образом функция вычисляет сумму чисел, содержащихся в ячейках C5, C9, C13 и C17 (см. рис. 6.25).

В последней формуле использованы смешанные ссылки на диапазоны. Использование смешанных ссылок позволяет сократить время на ввод достаточно громоздких формул в диапазон C21:E23. Достаточно ввести только одну формулу в ячейку C21 и затем скопировать ее в остальные ячейки диапазона C21:E23.

Если на рабочем листе определены имена, то использование имен в качестве аргументов функции СУММ делает формулы если не менее громоздкими, то, по крайней мере, более осмысленными (рис. 6.26).

Особенности использования функции СУММ

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

Отметим, что логическое значение ИСТИНА может влиять на результат вычислений функции СУММ. Например, формула =СУММ(1;3;4;ИСТИНА), в которой логическое значение ИСТИНА указано явно, вернет значение 9. Формула =СУММ ({1;3;4;ИСТИНА}), в которой в качестве аргумента функции СУММ используется массив констант, возвращает тот же результат, что и формула =СУММ(E2:E6).

Если логические значения непосредственно используются в качестве аргументов функции СУММ, то они преобразуются в числа. Логическое значение ИСТИНА преобразуется в число 1, а значение ЛОЖЬ -- в 0. Вот почему формула =СУММ (1;3;4;ИСТИНА) (см. рис. 6.27) возвращает другой результат по сравнению с формулой =СУММ(E2:E6).

Если хотя бы один из аргументов функции СУММ является значением ошибки, функция СУММ возвращает значение ошибки (рис. 6.28).

Функция СРЗНАЧ

Функция СРЗНАЧ вычисляет среднее арифметическое своих аргументов. Если заданы n действительных чисел a1, a2, ..., an, то число1 2...nna a aAn+ + +=называется средним арифметическим чисел a1, a2, ..., an.

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

_ =СРЗНАЧ(Диапазон1)

_ =СУММ(Диапазон1)/СЧЁТ(Диапазон1)

Пример использования этих формул показан на рис. 6.31. В качестве аргументов функций СРЗНАЧ, СУММ и СЧЁТ используются имена Диапазон1 и Диапазон2, содержащие ссылку на диапазоны A2:C6 и E2:G6 соответственно. Всего функция СРЗНАЧ может иметь до 255 аргументов.

Как видно из рис. 6.31, при вычислении среднего арифметического функция СРЗНАЧ игнорирует логические и текстовые значения, но учитывает нулевые значения.

Если при определении среднего арифметического необходимо учитывать пустые ячейки, логические значения и текст, используйте функцию СРЗНАЧА. При вычислении средне го арифметического с помощью функции СРЗНАЧА логическое значение ИСТИНА полагается равным 1, логическое значение ЛОЖЬ и текст (кроме чисел, представленных в виде текста) -- равными 0 (нулю). На рис. 6.31 хорошо видно, что функции СРЗНАЧ и СРЗНАЧА возвращают разные значения для одного и того же диапазона данных. Кроме того, если исходный диапазон содержит логическое значение ИСТИНА, формулы =СРЗНАЧА(Диапазон2) и =СУММ(Диапазон2)/СЧЁТЗ(Диапазон2) возвращают разные значения.

Функции МАКС и МИН

Функция МАКС возвращает наибольшее значение из набора значений, функция МИН -- наименьшее значение. В качестве аргументов обеих функций могут использоваться числа, ссылки на ячейки или диапазоны ячеек, имена ячеек или диапазонов, массивы констант. Аргументов может быть до 255.

При определении максимального и минимального значений с помощью функций МАКС и МИН учитываются только те ячейки, которые содержат числа; пустые строки, текст (за исключением чисел, представленных в виде текста) и логические значения игнорируются (рис. 6.32).

Функции МИН и МАКС возвращают значение ошибки, если хотя бы одна ячейка диапазона содержит значение ошибки. Если диапазон не содержит ячеек с числовыми значениями, функции МАКС и МИН возвращают значение 0 (нуль).

Если при определении наибольшего и наименьшего значений в заданном диапазоне требуется учитывать пустые ячейки, логические значения и текст, используйте функции МАКСА и МИНА соответственно. На рис. 6.33 хорошо видно, что функции МИН и МИНА, МАКС и МАКСА возвращают разные значения для одного и того же диапазона

Заключение

Данная глава является введением в функции Excel. В этой главе вы узнали, что собой представляет встроенная функция Excel, как правильно ввести функцию в формулу и получить подробную справку о функции. Вы научились также работать с функциями СУММ, СРЗНАЧ, МИН, МАКС.

Список литературы

1) Финансовое моделирование и оптимизация средствами Excel. Автор: Васильев А.Н. Издательство: Питер 2009 год.

2) Excel «без воды». Все, что нужно для уверенной работы. Автор: Голышева А. В., Корнеев В. Н. Издательство: Наука и техника 2014 год.

3) Функции и формулы Excel. Автор: Веденеева Е. А. Издательство: Питер 2010 год.

Размещено на Allbest.ru