Функция _ это специальная, заранее подготовленная формула, которая выполняет операции над заданными значениями и возвращает результат. Значения, над которыми функция выполняет операции, называются аргументами. В качестве аргументов могут выступать числа, текст, логические значения, ссылки. Аргументы могут быть представлены константами или формулами. Формулы в свою очередь могут содержать другие функции, т. е. аргументы могут быть представлены функциями. Функция, которая используется в качестве аргумента, является вложенной функцией. В общем виде любая функция может быть записана в виде:
Имя_функции(аргументы)
Существуют следующие правила ввода функций:
Имя функции всегда вводится после знака «=».
Аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов.
Между именем функции и знаком « (» пробел не ставится.
Вводить функции рекомендуется строчными буквами. Если ввод функции осуществлен правильно, Excel сам преобразует строчные буквы в прописные. Для ввода функций можно использовать Мастер функций, вызываемый нажатием кнопки Вставка функции на панели инструментов. Мастер функций позволяет выбрать нужную функцию из списка и выводит для нее панель формул. На панели формул отображаются имя и описание функции, количество и тип аргументов, поле ввода для формирования списка аргументов, возвращаемое значение.
Методика использования Мастера функций:
Выделить ячейку для записи формулы.
Вызвать Мастер функции с помощью команды Вставка Функция, или кнопкой Вставка функции на панели инструментов Стандартная.
- 1 шаг. Выбрать категорию функции. В списке Функция содержится полный перечень доступных функций выбранной категории. В нижней части окна приведен краткий синтаксис и справка о назначении выбираемой функции.
- 2 шаг. Ввести аргументы функции (щелкая по ним мышью).
Если аргумент является результатом расчета другой встроенной функции Excel, возможно организовать вычисление вложенной, встроенной функции путем вызова Мастера функции через поле, расположенное на месте Поля имени.
Нажать кнопку ОК.
Excel содержит широкий набор функций, позволяющих выполнять стандартные вычисления.
Арифметические и тригонометрические функции.
Инженерные функции, предназначенные для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую). Информационные функции, предназначенные для определения типа данных, хранимых в ячейках. Логические функции, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ). Статистические функции, предназначенные для выполнения статистического анализа. Финансовые функции, предназначенные для осуществления типичных финансовых расчетов, таких как вычисление суммы платежа по ссуде, объема периодической выплаты по вложению или ссуде, стоимости вложения или ссуды по завершении всех платежей.
Функции баз данных, предназначенные для анализа, данных из списков или баз данных. Текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т. д.). Функции работы с датой и временем. Они позволяют анализировать и работать со значениями даты и времени в формулах. Одной из наиболее часто используемых функций является функция СУММ. Для нее на панели инструментов вынесена отдельная кнопка Автосумма.
Технология работы с кнопкой Автосумма:
выделить ячейку, в которой должен располагаться итог;
щелкнуть по кнопке Автосумма;
будет предложен диапазон для суммирования (он окружен подвижной рамкой). Если диапазон неверен, следует выделить нужный диапазон.
щелкнуть по кнопке Автосумма.
Пример 2.1.
Рассмотрим функцию ПРОСМОТР из категории Ссылки и массивы. Функция ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор) в поисках определенного значения и возвращает соответствующее ему значение из другого столбца или строки. Синтаксис данной функции:
ПРОСМОТР (Искомое_значение; Вектор_просмотра; Вектор_результата)
Для корректной работы функции значения в аргументе Вектор_просмотра должны быть отсортированы в порядке возрастания. Предположим, что в ячейку С2 Листа1 будет заноситься код покупателя, а по этому коду требуется найти из имеющегося списка покупателей полное наименование организации и ее реквизиты (см. задание для выполнения контрольной работы). Условимся, что список покупателей располагается на листе Покупатели в диапазоне ячеек А2:D8. В столбце А располагаются коды покупателей, а в столбце В - полное наименование. В качестве Искомого значения будет выступать внесенное значение кода покупателя, задаваемое адресом ячейки. Т.е. первым аргументом функции будет адрес ячейки, в которую введен код покупателя, в нашем случае, С2.
Вектор просмотра - это диапазон ячеек листа Покупатели, в которых введены коды покупателей, т.е. А2:А8. Вектором результата будет соответственно диапазон В2:В8 из того же листа Покупатели. Вид окна функции ПРОСМОТР (рис. 3):
Рис. 3.
Первоначально курсор находится в поле Искомое_значение. Задать это значение можно, щелкнув мышью по ячейке с введенным кодом Покупателя. После этого следует переместиться в поле Вектор_просмотра. Диапазон ячеек вектора просмотра находится на листе Покупатели, поэтому нужно щелкнуть по ярлыку этого листа и выделить диапазон ячеек с кодами покупателей. И, наконец, перемещаемся в поле Вектор_результата. В качестве диапазона ячеек выбираем ячейки из столбца В листа Покупатели. После нажатия на кнопку ОК Excel отобразит полученный результат на рабочем листе.
Недаром программа Microsoft Excel пользуется колоссальной популярностью как среди рядовых пользователей ПК, так и среди специалистов в разных отраслях. Все дело в том, что данное приложение содержит огромное количество интегрированных формул, называемых функциями. Восемь основных категорий позволяют создать практически любое уравнение и сложную структуру, вплоть до взаимодействия нескольких отдельных документов.
Понятие встроенных функций
Что же такое эти встроенные функции в Excel? Это специальные тематические формулы, дающие возможность быстро и качественно выполнить любое вычисление.
Вам будет интересно:
К слову, если самые простые из функций можно выполнить альтернативным (ручным) способом, то такие действия, как логические или ссылочные, - только с использованием меню «Вставка функции».
Таких функций в Excel очень много, знать их все невозможно. Поэтому компанией Microsoft разработан детальный справочник, к которому можно обратиться при возникновении затруднений.
Использование функций имеет много преимуществ, в том числе:
Код внутри функции может быть повторно использован.
Гораздо проще изменить или обновить код в функции (что делается один раз), нежели искать и изменять все части кода в функции main() «на месте». Дублирование кода — хороший рецепт для ошибок и ухудшения производительности.
Упрощается чтение и понимание кода, так как вам не нужно знать реализацию функции, чтобы её использовать (предполагается наличие информативного названия функции и ).
В функциях поддерживается проверка типов данных для гарантии того, что передаваемые аргументы соответствуют параметрам функции.
Однако, одним из главных недостатков использования функций является то, что каждый раз, когда она вызывается, происходит расход ресурсов, что влияет на производительность программы. Это связано с тем, что ЦП должен хранить адрес текущей команды (инструкции или стейтмента), которую он выполняет (чтобы знать, куда нужно будет вернуться позже) вместе с другими данными. Затем точка выполнения перемещается в другое место программы. Дальше все параметры функции должны быть созданы и им должны быть присвоены значения. И только потом, после выполнения функции, точка выполнения возвращается обратно. Код, написанный «на месте», выполняется значительно быстрее.
Для функций, которые являются большими и/или выполняют сложные задачи, расходы на вызов обычно незначительны по сравнению с количеством времени, которое отводится на выполнение кода этой функции. Однако, для небольших, часто используемых функций, время, необходимое для выполнения вызова, часто превышает время, необходимое для фактического выполнения кода этой функции. А это, в свою очередь, может привести к существенному снижению производительности.
C++ предлагает способ сочетания преимуществ функций со скоростью кода, написанного «на месте»: встроенные функции . Ключевое слово inline используется для запроса, чтобы компилятор рассматривал вашу функцию как встроенную. При компиляции вашего кода, все встроенные функции раскрываются «на месте», то есть вызов функции заменяется копией содержимого самой функции, и ресурсы, которые могли бы быть потрачены на вызов этой функции, сохраняются! Минусом является лишь увеличение компилируемого кода за счёт того, что встроенная функция раскрывается в коде при каждом вызове (особенно если она длинная и/или её вызывают много раз). Рассмотрим следующий фрагмент кода:
#include
#include int max (int a , int b ) return a < b ? b : a ; int main () std :: cout << max (7 , 8 ) << "\n" ; std :: cout << max (5 , 4 ) << "\n" ; return 0 ; |
Эта программа дважды вызывает функцию max(), т.е. дважды расходуются ресурсы на вызов функции. Поскольку max() является довольно таки короткой функцией, то это идеальный вариант для её конвертации во встроенную функцию:
inline int max(int a, int b) { return a < b ? b: a; }
inline int max (int a , int b ) return a < b ? b : a ; |
int main() { std::cout << (7 < 8 ? 8: 7) << "\n"; std::cout << (5 < 4 ? 4: 5) << "\n"; return 0; }
int main () std :: cout << (7 < 8 ? 8 : 7 ) << "\n" ; std :: cout << (5 < 4 ? 4 : 5 ) << "\n" ; return 0 ; |
Такой код выполниться быстрее ценой несколько увеличенного объёма.
Из-за возможности подобного «раздувания», встроенные функции лучше всего использовать только для коротких функций (не более нескольких строк), которые обычно вызываются внутри циклов и не имеют . Также, обратите внимание, ключевое слово inline является лишь рекомендацией — компилятор может игнорировать ваш запрос на встроенную функцию. Подобное произойдёт, если вы попытаетесь сделать встроенной длинную функцию!
Наконец, современные компиляторы автоматически конвертируют соответствующие функции во встроенные - этот процесс автоматизирован настолько, что даже лучше ручной выборочной конвертации программистом. Даже если вы не пометите функцию как встроенную, то компилятор автоматически выполнит её как таковую, если посчитает, что это способствует улучшению производительности. Таким образом, в большинстве случаев нет особой необходимости использовать ключевое слово inline. Компилятор всё сделает сам.
данные по строке “Производственная прибыль” рассчитайте как разность между валовой прибылью и общими затратами;
данные по строке “Удельная валовая прибыль” рассчитайте как результат деления производственной прибыли на торговые доходы;
данные в колонке “Итого за год” рассчитайте суммированием квартальных данных.
8. Задайте для строки “Удельная валовая прибыль” Процентный формат, а для всех остальных строк – Формат с разделителями.
9. На строке 1 (при необходимости вставьте строку) введите заголовок, задав для него более крупный жирный шрифт и расположение по центру всей таблицы.
10.На листе с таблицей по данным Торговых расходов фирмы за первые три квартала постройте разрезанную круговую диаграмму (при выделении данных для построения диаграммы используйте и названия кварталов).
11.Добавьте на диаграмму данные по 4 кварталу.
12.По данным Валовой прибыли постройте гистограмму . Оформите заголовки и легенды диаграмм, расположите диаграммы рядом под таблицей.
13.Измените числовое значение за 4 квартал по Торговым расходам фирмы. Проследите зависимость графических данных в диаграммах от числовых в таблице.
14. На отдельном листе постройте нормированную гистограмму со столбцами в виде цилиндров по данным всей таблицы (исключая графу Итого за год).
Тема III. Основные правила работы со встроенными функциями
1. Использование встроенных функций Excel
Встроенные в Excel функции призваны значительно облегчить работу пользователя, поскольку сокращают написание формул. Функции – подпрограммы, выполняющие вычисления и возвращающие единственное значение. Все функции имеют уникальные имена (идентификаторы). Функции имеют параметры, посредством которых передаются значения аргументов - исходных данных для вычислений. Синтаксис функции имеет вид:
ИМЯ_ФУНКЦИИ(список параметров)
Список параметров может задаваться адресными ссылками или ссылками посредством имен на ячейки или диапазоны, в виде имен функций, а также литералами, отделенными друг от друга символом ; (точка с запятой).
Примеры функций:
СУММ(А2:A10) – суммируются значения всех ячеек диапазона. СУММ(СуммаКредита) – суммируются значения всех ячеек диапазона с
именем СуммаКредита
СУММ(12; 10; 13) – суммируются значения, заданные списком числовых литералов.
Список параметров, заключенный в круглые скобки следует после имени функции безо всяких интервалов. Даже если список параметров пуст (как у функции СЕГОДНЯ () ), круглые скобки должны присутствовать.
Для задания функции используют следующие правила .
Прежде всего, следует установить курсор в ячейку , которая должна содержать результат выполнения функции . Далее можно выполнить одно из следующих действий:
нажать клавиши
задать команду ФУНКЦИЯ из меню ВСТАВКА ;
нажать кнопку ВСТАВКА ФУНКЦИИ [ f x ] на стандартной панели.
Далее следует выбрать категорию функции и саму функцию в правой части окна Мастера функций. Для выполнения пошаговой подстановки аргументов с помощью Мастера функций нажать кнопку ОК . На следующих шагах следует указать адреса (имена) ячеек, значения которых будут использованы в качестве аргументов функции.
Примечание : При использовании в качестве аргумента вводимой функции другой какой-либо функции следует выбрать имя требуемой функции из списка функций в строке формул слева и произвести требуемые шаги Мастера функций. После завершения ввода аргументов вложенной функции щелкните указателем мыши в строке формул (в конце вводимой формулы).
1.1. Суммирование ячеек, удовлетворяющих определенному критерию
СУММЕСЛИ(ДИАПАЗОН; УСЛОВИЕ; ДИАПАЗОН_ СУММИРОВАНИЯ) - группа
математических функций.
Функция предназначена для суммирования только ячеек, удовлетворяющих некому критерию.
условие - указывается в форме числа, выражения или текста;
диапазон_суммирования - это диапазон суммируемых ячеек.
Пример III.1. В ячейки A1,A2,A3,A4,A5 введена информация:
A1=1000, A2=2000, A3=900, A4=800, A5=1500.
СУММЕСЛИ (А1:А5;’’>=1000’’).
В ячейке А6 получится число 4500.
Измените значение ячейки А3 на любое большее 1000. Проследите изменение результата вычислений.
1.2. Подсчет количества значений в диапазоне
Для подсчета количества числовых значений в диапазоне:
СЧЕТ (ЗНАЧЕНИЕ 1; ЗНАЧЕНИЕ 2;…) - группа статистических функций.
Пример III.2. В ячейки В1,В2,В3,В4,В5 введена информация:
В1=’’Текст’’, В2=2000, В3= , В4=800, В5=1500.
Пошаговыми действиями Мастера функций в ячейку В6 следует ввести формулу:
СЧЕТ (В1:В5)
В ячейке В6 получится число 3.
Для подсчета количества всех значений в списке аргументов и непустых ячеек: СЧЕТЗ (ЗНАЧЕНИЕ 1; ЗНАЧЕНИЕ 2;…) - группа статистических функций.
Если эта функция будет введена в примере 2 в ячейку В7, то результат в ячейке В7 будет равен 4.
1.3. Подсчет количества пустых ячеек в диапазоне
Пример III.3. В ячейки С1,С2,С3,С4,С5 скопируйте информацию из соответствующих ячеек столбца А, оставив пустой ячейку С3:
С1=1000, С2=2000, С3= , С4=800, С5=1500.
Пошаговыми действиями Мастера функций в ячейку С6 следует ввести формулу:
В ячейке С6 получится число 1.
1.4. Подсчет количества непустых ячеек в диапазоне, удовлетворяющих
заданному условию
СЧЕТЕСЛИ (ДИАПАЗОН ; УСЛОВИЕ ) - группа статистических функций.
диапазон - это диапазон, в котором определяется критерий;
условие - указывается в виде числа, выражения или текста и определяет какие ячейки надо подсчитывать.
Результат должен быть получен в ячейке С7. Пошаговыми действиями Мастера функций в ячейку С7 следует ввести формулу:
СЧЕТЕСЛИ (С1:С5;’’>=1000’’).
В ячейке С7 получится число 3.
Измените значение ячейки С1 на любое, меньшее 1000. Проследите изменение результата вычислений.
1.5. Расчет среднего значения
СРЗНАЧ (ДИАПАЗОН 1; ДИАПАЗОН 2;...) - группа статистических функций.
В текущую ячейку возвращается среднее значение для чисел указанного диапазона.
Пример III.5. В диапазоне ячеек A1:A5 из примера 1 определить среднее значение.
Результат должен быть получен в ячейке А7.
Пошаговыми действиями Мастера функций в ячейку А7 следует ввести формулу:
СРЗНАЧ (А1:А5).
1.6. Определение максимального значения
МАКС (ДИАПАЗОН 1; ДИАПАЗОН 2;...) - группа статистических функций.
В текущую ячейку возвращается максимальное число из данного диапазона.
Пример III.6. В диапазоне ячеек A1:A5 из примера 1 определить максимальное значение.
Результат должен быть получен в ячейке А8.
Пошаговыми действиями Мастера функций в ячейку А8 следует ввести формулу:
МАКС (А1:А5).
В ячейке А8 получится число 2000.
1.7. Определение минимального значения
МИН (ДИАПАЗОН 1; ДИАПАЗОН 2;...) - группа статистических функций.
В текущую ячейку возвращается минимальное число из данного диапазона.
Пример III.7. В диапазоне ячеек В1:В5 из примера 2 определить минимальное значение.
Результат должен быть получен в ячейке В8.
Пошаговыми действиями Мастера функций в ячейку В8 следует ввести формулу:
МИН (В1:В5).
В ячейке В8 получится число 800.
1.8. Генерация случайного числа
СЛЧИС () - группа математических функций.
В текущую ячейку возвращается равномерно распределенное случайное число большее либо равное 0 и меньшее 1. Чтобы получить случайное вещественное число между a и b, можно использовать следующую формулу: СЛЧИС()*(b-a)+a.
Новое случайное число возвращается каждый раз, когда рабочий лист вычисляется повторно. Чтобы число, полученное случайным образом не менялось в дальнейшем, можно ввести =СЛЧИС() в строку формул и нажать F9.
Пример III.8. Для генерации случайного числа большего или равного 0, но меньшего 50 можно использовать формулу: СЛЧИС()*50.
Самостоятельно получите случайным образом последовательность чисел от 100 до 200.
1.9. Функции прогнозирования
ТЕНДЕНЦИЯ(ИЗВЕСТНЫЕ ЗНАЧЕНИЯ_ Х; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ_ У; НОВОЕ
ЗНАЧЕНИЕ _ У ) - группа статистических функций В текущую ячейку возвращается новое значение_Х, рассчитанное на основании
известных значений.
Пример III.9. Ввести таблицу, начиная с ячейки G1:
Годы Цена
Результат прогнозирования должен быть получен в ячейке H5. Пошаговыми действиями Мастера функций в ячейку H5 вводим формулу:
ТЕНДЕНЦИЯ (H2:H4;G2:G4;G5).
В ячейке H5 получится число 626.6667 1 .
Самостоятельно рассчитайте тенденцию для 2004 и 2005 годов.
1.10. Определение ранга числа
РАНГ (АДРЕС ЯЧЕЙКИ ; ДИАПАЗОН ) - группа статистических функций.
В текущую ячейку возвращается величина, соответствующая положению (рангу) числа, заданного адресом ячейки, в указанном диапазоне.
Пример III.10. В ячейки D1,D2,D3,D4,D5 скопируйте информацию из соответствующих ячеек столбца А. Для каждой ячейки из диапазона D1:D5 определить ранг числа.
Результат должен быть получен в ячейках E1:E5. Функция ранга вводится сначала в ячейку E1, затем копируется для всех ячеек до E5.
Пошаговыми действиями Мастера функций в ячейку E1 вводим формулу:
1 Результат прогнозирования может быть получен и другим способом: выделить диапазон ячеек с известными значениями цен и растянуть этот диапазон за правый нижний угол на одну ячейку вниз.
MS Excel 2003 содержит 320 встроенных функций. Для удобства функции в Excel 2003 разбиты по категориям (матем, финансовые, статистические и т.д.).
Обращение к каждой функции состоит из двух частей: имени функции и аргументов в круглых скобках.
Встроенные функции
Математические:
КОРЕНЬ(...) Вычисление квадратного корня
ABS(...) Вычисление абсолютного значения (модуля) числа
ЦЕЛОЕ(...) Округление числа или результата выражения, указанного в скобках, до ближайшего меньшего (!) целого
ПИ() * Значение математической константы «ПИ» (3,1415926...)
НОД(…) Наибольший общий делитель нескольких чисел
НОК(…) Наименьшее общее кратное нескольких чисел
СЛЧИС() * Вычисление случайного числа в промежутке между 0 и 1
Статистические
МИН(...) Определение минимального из указанных чисел
МАКС(…) Определение максимального из указанных чисел
СРЕДНЕЕ(...) Определение среднего значения указанных чисел
СУММ(...) Определение суммы указанных чисел
Дата и время
СЕГОДНЯ () * Значение сегодняшней даты в виде даты в числовом формате
МЕСЯЦ(дата) Вычисление порядкового номера месяца в году по указанной дате
ДЕНЬ(дата) Вычисление порядкового номера дня в месяце по указанной дате
ГОД(дата) Вычисление года по указанной дате
Логические
И(условие1; условие2;...)
Вычисление значения (ИСТИНА, ЛОЖЬ) логической операции И
ИЛИ (условие1; условие2;...)
Вычисление значения (ИСТИНА, ЛОЖЬ) логической операции ИЛИ
ЕСЛИ (условие; знач_ИСТИНА; знач_ЛОЖЬ)
Вычисление значения в зависимости от выполнения условия.
Виды ссылок:
Относительная (С3)
При копировании: Меняется в соответствии с новым положением ячейки
Технология ввода: Щелкнуть в ячейке
Абсолютная ($C$3)
При копировании: Не меняется
Технология ввода: Щелкнуть в ячейке и нажимать F4 до преобразования адреса к нужному виду
Смешанная
При копировании: (С$3) Не меняется номер строки
При копировании: ($C3) Не меняется имя столбца
Использование относительных ссылок в Excel аналогично указанию направления движения по улице - "идти три квартала на север, затем два квартала на запад". Следование этим инструкциям из различных начальных мест будет приводить в разные места назначения.
Например, формула, которая суммирует числа в столбце или строке, затем часто копируется для других номеров строк или столбцов. В таких формулах используются относительные ссылки.
В Excel абсолютная ссылка на ячейку или область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.
