Как встроенные функции влияют на организацию расчетов в ms excel
Обновлено: 07.07.2024
В MS Excel содержится большое количество стандартных формул, называемых функциями. Встроенные функции Excel делятся на следующие категории: математические, логические, тригонометрические, статистические, финансовые, информационные, текстовые, функции даты и времени, инженерные, функции для работы с базой данных, функции просмотра и ссылок.
Встроенные функции Excel – это специальные, заранее созданные формулы, которые позволяют легко и быстро выполнять сложные вычисления. Они подобны специальным клавишам на некоторых калькуляторах, предназначенным для вычисления квадратных корней, логарифмов и статистических характеристик.
Некоторые функции, такие как СУММ (SUM), SIN (SIN) и ФАКТР (FACT), являются эквивалентами длинных математических формул, которые можно создать самим. Другие функции, такие как ЕСЛИ (IF) и ВПР (VLOOKUP), в виде формул реализовать невозможно.
В тех случаях, когда нужна информация о функциях, следует обращаться к справочной системе Excel, где находится полное описание каждой встроенной функции.
Быстро получить информацию о функциях можно также с помощью кнопки Вставка функции.
Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции, например, СУММ (SUM) или СРЗНАЧ (AVERAGE) описывает операцию, которую эта функция выполняет. Аргументы функции Excel задают значения или ячейки, используемые функцией. Например, в следующей формуле СУММ – это имя функции, а С3:С5 – ее единственный аргумент. Эта формула суммирует числа в ячейках С3, С4 и С5:
Некоторые функции, такие как ПИ (PI) и ИСТИНА (TRUE), не имеют аргументов. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки:
При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Например, следующая формула указывает Excel, что необходимо перемножить числа в ячейках С1, С2 и С5:
В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, следующая функция имеет три аргумента, но суммирует числа в 29 ячейках (первый аргумент, А1:А5, ссылается на диапазон пяти ячеек от А1 до А5 и т.д.):
Указанные в ссылке ячейки, в свою очередь, могут содержать формулы, которые ссылаются на другие ячейки или диапазоны. Используя аргументы, можно легко создавать длинные цепочки формул для выполнения сложных операций.
Комбинацию функций можно использовать для создания выражения, которое Excel сводит к единственному значению и интерпретирует его как аргумент. Например, в следующей формуле: SIN(A1*ПИ()) и 2*COS(A2*ПИ()) – это выражения, которые вычисляются и используются в качестве аргументов функции СУММ:
Типы аргументов
Аргумент – выражение, задающее значение при обращении к процедуре или функции, от которого зависит результат ее выполнения.
В качестве аргументов используются числовые, текстовые и логические значения, имена диапазонов, массивы и ошибочные значения. Некоторые функции возвращают значения этих типов и их в дальнейшем можно использовать в качестве аргументов в других функциях.
Аргументы функции могут быть числовыми. Например, функция СУММ в следующей формуле суммирует числа 327, 209 и 176:
Обычно числа вводятся в ячейки листа, которые будут использоваться, а затем применяются ссылки на эти ячейки в качестве аргументов в функциях.
В качестве аргумента функции могут использоваться текстовые значения. Например:
=ТЕКСТ(ТДАТА();«Д МММ ГГГГ»).
В этой формуле второй аргумент функции ТЕКСТ «Д МММ ГГГГ», является текстовым и задает шаблон для преобразования десятичного значения даты, возвращаемого функцией ТДАТА(), в строку символов. Текстовый аргумент может быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку, которая содержит текст.
Аргументы ряда функций могут принимать только логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Логическое выражение возвращает значение ИСТИНА или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например, первый аргумент функции ЕСЛИ (IF) в следующей формуле является логическим выражением, которое использует значение:
=ЕСЛИ(А1=ИСТИНА, «Новая», «Старая»)& «цена».
Если значение в ячейке А1 равно ИСТИНА, то выражение А1=ИСТИНА возвращает значение ИСТИНА, и функция ЕСЛИ возвращает строку Новая, а формула в целом возвращает текстовое значение Новая цена.
В качестве аргумента функции можно указать имя диапазона. Например, если выбрать команду Присвоить подменю Имя меню Вставка и назначить диапазону С3:С6 имя Получено, то для вычисления суммы чисел в ячейках С3, С4, С5 и С6 можно использовать формулу:
Аргументом функции может быть массив. Некоторые функции, такие как ТЕНДЕНЦИЯ (TREND) и ТРАНСП (TRANSPOSE) требуют задания массива аргументов. Другие функции не требуют задания массива, но могут использовать такие аргументы. Массивы могут содержать числовые, текстовые или логические значения.
В одной функции можно использовать аргументы различных типов. Например, в следующей формуле аргументами являются имя диапазона (Группа 1), ссылка на ячейку (A3) и числовое выражение (5*3), а сама формула возвращает единственное числовое значение:
Ввод функций в рабочем листе
Вводить функции в рабочем листе можно прямо с клавиатуры или с помощью команды Функция меню Вставка. При вводе функции с клавиатуры лучше использовать строчные буквы. Когда закончится ввод функции, необходимо нажать клавишу Enter или выделить другую ячейку. Excel изменит буквы в имени функции на прописные, если оно было введено правильно. Если буквы не изменяются, это означает, что имя функции введено неверно.
Если выделить ячейку и выбрать в меню Вставка команду Функция, Excel выведет окно диалога Мастер функций – шаг 1 из 2, показанное на рис. 2.2. Открыть это окно можно также с помощью кнопки Вставка функции на стандартной панели инструментов.
В этом окне сначала выбирают категорию (или Полный алфавитный перечень) в списке Категория и затем в алфавитном списке Функция указывают нужную функцию. В качестве альтернативы после выбора категории можно щелкнуть на имени любой функции в списке Функция и нажать клавишу, соответствующую первой букве нужного имени. Чтобы ввести функцию, необходимо нажать кнопку ОK или клавишу Enter.
Excel введет знак равенства, имя функции и пару круглых скобок. Затем Excel откроет второе окно диалога Мастера функций (без строки заголовка).
Второе окно диалога Мастера функций содержит по одному полю для каждого аргумента выбранной функции. Если функция имеет переменное число аргументов, это окно диалога при вводе дополнительных аргументов расширяется. Описание аргумента, поле которого содержит точку вставки (курсор), выводится в нижней части окна диалога.
Рис. 2.2. Окно диалога Мастер функций – шаг 1 из 2
Справа от каждого поля аргумента отображается его текущее значение. Это очень удобно, когда используются ссылки или имена. Текущее значение функции отображается внизу окна диалога.
После нажатия кнопки ОК или клавиши Enter созданная функция появится в строке формул.
Некоторые функции, такие как ИНДЕКС (INDEX) имеют несколько форм (вариантов задания аргументов). Если выбрать такую функцию в списке Функция, Excel откроет дополнительное окно диалога Мастера функций, как на рис. 2.2, в котором можно выбрать нужную форму функции.
Перечень основных функций, расположенных по категориям с примерами выполнения приведен в табл. 2.1.
Цель занятия. Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel.
Задание 18.1. Создать таблицу динамики розничных цен и произвести расчет средних значений.
Исходные данные представлены на рис. 18.1.
1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Excel).
Рис. 18.1. Исходные данные для задания 18.1
3. Переименуйте ярлычок Лист 5, присвоив ему имя «Динамика цен».
4. На листе «Динамика цен» создайте таблицу по образцу, как на рис. 18.1.
5. Произведите расчет изменения цены в колонке «Е» по формуле
Изменение цены = Цена на 01.06.2003/Цена на 01.04.2003.
Не забудьте задать процентный формат чисел в колонке «Е» (Формат/ Ячейки/ Число/Процентный).
6. Рассчитайте средние значения по колонкам, пользуясь мастером функций fx. Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите мастер функций (кнопкой Вставка функции fx или командой Вставка/Функция) и на первом шаге мастера выберите функцию СРЗНАЧ (категория Статистические/СРЗНАЧ) (рис. 18.2).
После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК (рис. 18.3). В ячейке В14 появится среднее значение данных колонки «В».
Аналогично рассчитайте средние значения в других колонках.
7. В ячейке А2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере (Вставка/Функция/ Дата и Время/Сегодня).
8. Выполните текущее сохранение файла (Файл/Сохранить).
Рис. 18.2. Выбор функции расчета среднего значения СРЗНАЧ
Рис. 18.3. Выбор диапазона данных для расчета функции среднего значения
Задание 18.2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Построить график по данным таблицы.
Исходные данные представлены на рис. 18.4.
1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию. Объединение выделенных ячеек произведите кнопкой панели инструментов Объединить и поместить в центре или командой меню (Формат/Ячейки/вкладка Выравнивание/отображение – Объединение ячеек).
Рис. 18.4. Исходные данные для задания 18.2
Краткая справка. Изменение направления текста в ячейках производится путем поворота текста на 90° в зоне Ориентация окна Формат ячеек, вызываемого командой Формат/ Ячейки/вкладка Выравнивание/ Ориентация – поворот надписи на 90° (рис. 18.5).
Рис. 18.5. Поворот надписи на 90°
Рис. 18.6. Задание параметров шкалы оси графика
2. Произвести расчет средних значений по строкам и столбцам с использованием функции СРЗНАЧ.
3. Построить график изменения количества рабочих дней по годам и странам. Подписи оси «X» задайте при построении графика на втором экране мастера диаграмм (вкладка Ряд, область Подписи оси «X»).
4. После построения графика произведите форматирование вертикальной оси, задав минимальное значение 1500, максимальное значение 2500, цену деления 100 (рис. 18.6). Для форматирования оси выполните двойной щелчок мыши по ней и на вкладке Шкала диалогового окна Формат оси задайте соответствующие параметры оси.
5. Выполните текущее сохранение файла «Расчеты»
Задание 18.3. Применение функции ЕСЛИ при проверке условий. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ).
Исходные данные представлены на рис. 18.7.
1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.
2. Произвести расчет Премии (25 % от базовой ставки) по формуле
Премия = Базовая ставка х 0,25 при условии, что
План расходования ГСМФактически израсходовано ГСМ.
Рис. 18.7. Исходные данные для задания 18.3
Для проверки условия используйте функцию ЕСЛИ.
Для расчета Премии установите курсор в ячейке F4, запустите мастер функций (кнопкой Вставка функции fx или командой Вставка/Функция) и выберите функцию ЕСЛИ (категория – Логические/ ЕСЛИ).
Задайте условие и параметры функции ЕСЛИ (рис. 18.8).
В первой строке «Логическое выражение» задайте условие С4D4.
Во второй строке задайте формулу расчета премии, если условие выполняется Е4 * 0,25.
В третьей строке задайте значение 0, поскольку в этом случае (невыполнение условия) премия не начисляется.
3. Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию. Для сортировки установите курсор на любую ячейку таблицы, выберите в меню Данные команду
Рис. 18.8. Задание параметров функции ЕСЛИ
Рис. 18.9. Задание параметров сортировки данных
Рис. 18.10. Конечный вид задания 18.3
Сортировка, задайте сортировку по столбцу «Фактически израсходовано ГСМ» (рис. 18.9).
4. Конечный вид расчетной таблицы начисления премии приведен на рис. 18.10.
5. Выполните текущее сохранение файла «Расчеты» (Файл/Сохранить).
Задание 18.4. Скопировать таблицу котировки курса доллара (задание 16.1, лист «Курс доллара») и произвести под таблицей расчет средних значений, максимального и минимального значений курсов покупки и продажи доллара. Расчет произвести с использованием «Мастера функций».
Перемещать и копировать листы можно перетаскивая их ярлычки (для копирования удерживайте нажатой клавишу [Ctrl]).
Краткая справка. Для выделения максимального/минимального значений установите курсор в ячейке расчета, выберите встроенную функцию Excel МАКС <МИН) из категории
Рис. 18.11. Копирование листа электронной книги
«Статистические», в качестве первого числа выделите диапазон ячеек значений столбца В4: В23 (для второго расчета выделите диапазон С4: С23).
Практическая работа 19
Статьи к прочтению:
Функция ЕСЛИ в MS Excel (видео-урок)
Похожие статьи:
Цель занятия. Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel.
Задание 1. Создать таблицу динамики розничных цен и произвести расчет средних значений.
1. Запустите редактор электронных таблиц Microsoft Excel (Пуск à Программы à Microsoft office à Microsoft Excel).
2. Переименуйте ярлык Лист 1, присвоив ему имя «Динамика цен».
3. На листе «Динамика цен» создайте таблицу по образцу.
4. Произведите расчет изменения цены в колонке «Е» по формуле:
Изменение цены =
Цена на 01. 06.2003/Цена на 01. 04.2003
Не забудьте задать процентный формат чисел в колонке «Е».
5. Рассчитайте средние значения по колонкам, пользуясь мастером функций Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите мастер функций (кнопкой Вставка функции в строке формул или на вкладке Формулы àгруппа Библиотека функций à кнопка Вставить функцию или вызвать меню к кнопке à категория Статистические à функция СРЗНАЧ).
6. После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК. В ячейке В14 появится среднее значение данных колонки «В».
7. Аналогично рассчитайте средние значения в других колонках.
8. В ячейке А2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере (вкладка Формулы à группа Библиотека функций à вызвать меню к кнопке à функция СЕГОДНЯ).
9. Выполните текущее сохранение файла «Лабораторная работа 3».
Задание 2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Построить график по данным таблицы.
1. На листе 2 создайте таблицу по заданию. Объединение выделенных ячеек произведите используя кнопку Объединить и поместить в центре на вкладке Главная à группа Выравнивание или вызовите диалоговое окно Формат ячеек à вкладка Выравнивание à установите флажок .
Краткая справка. Изменение направления текста производится путем поворота текста на 90°, вызываемого на вкладке Главная à группа Выравнивание à вызовите диалоговое окно Формат ячеек à вкладка Выравнивание à установите Ориентацию — поворот надписи на 90°.
2. Произведите расчет средних значений по строкам и столбцам с использованием функции СРЗНАЧ.
3. Постройте график изменения количества рабочих дней по годам и странам. Подписи оси «X» задайте с помощью вкладки Работа с диаграммой à вкладка Конструктор à группа Данные à щелкнуть по кнопке Выбрать данные à в появившемся окне в области Элементы легенды (ряды) выделить пункт Ряд1, а в области Подписи горизонтальной оси выбрать кнопку Изменить. В открывшемся окне Подписи оси указать интервал ячеек B3:M3. Нажать ОК.
4. После построения графика произведите форматирование вертикальной оси, задав минимальное значение 1500, максимальное значение 2500, цену деления 100. Для форматирования оси выполните щелчок правой кнопкой мыши по ней, выбрать в контекстном меню пункт Формат оси и в разделе Параметры оси и задать соответствующие значения.
5. Выполните текущее сохранение файла «Лабораторная работа 3».
Задание 3. Применение функции ЕСЛИ при проверке условий. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ). Исходные данные:
1. На очередном свободном листе электронной книги создайте таблицу по заданию.
2. Произвести расчет Премии (25 % от базовой ставки) по формуле
Премия =
Базовая ставка * 0,25 при условии, что
План расходования ГСМ > Фактически израсходовано ГСМ.
Для расчета Премии установите курсор в ячейке F4, запустите мастер функций и выберите функцию ЕСЛИ (категория - Логические à ЕСЛИ).
3. Задайте условие и параметры функции ЕСЛИ.
В первой строке «Логическое выражение» задайте условие С4 > D4.
Во второй строке задайте формулу расчета премии, если условие выполняется Е4*0,25.
В третьей строке задайте значение 0, поскольку в этом случае премия не начисляется.
4. Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию. Для этого установите курсор в ячейку D4 и выберите на вкладке Данные à группа Сортировка и фильтр à щелкните по кнопке .
Задание 4. Скопировать таблицу котировки курса доллара (лист «Курс доллара») и произвести под таблицей расчет средних значений, максимального и минимального значений курсов покупки и продажи доллара. Расчет произвести с использованием «Мастера функций».
1. Откройте одновременно файлы MS Excel «Лабораторная работа 1» и «Лабораторная работа 3»
2. Скопируйте содержимое листа «Курс доллара» (Лабораторная работа 1, лист 1) на новый лист . Для этого воспользоваться командой Переместить /Скопировать контекстного меню ярлыка.
3. В разделе Переместить выбранные листы в книгу: выберите «Лабораторная работа 3». Не забудьте для копирования поставить галочку в окошке Создавать копию.
4. Перемещать и копировать листы можно перетаскивая их ярлычки, а для копирования удерживайте нажатой клавишу [Ctrl] (это осуществляется в пределах одной книги).
5. Рассчитайте максимальное, минимальное и среднее значения по колонкам «Курс покупки» и «Курс продажи», пользуясь мастером функций. Функция СРЗНАЧ, МАКС, МИН находятся в разделе «Статистические».
6. Сохраните измененияи распечатайте результаты выполнения лабораторной работы.
Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Все функции Excel можно найти на вкладке "формулы" на ленте.
Синтаксис функции Excel
В приведенном ниже примере функции ОКРУГЛ , округленной на число в ячейке A10, показан синтаксис функции.
1. Structure. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающую круглую скобку, аргументы функции, разделенные запятыми, и закрывающая круглая скобка.
2. имя функции. Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите клавиши SHIFT + F3, чтобы открыть диалоговое окно Вставка функции .
4. всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.
Ввод функций Excel
Диалоговое окно Вставить функцию упрощает ввод функций при создании формул, в которых они содержатся. После выбора функции в диалоговом окне Вставка функции Excel запустит мастер функций, который выведет на экран имя функции, каждый из ее аргументов, описание функции и каждый аргумент, текущий результат функции и текущий результат всей формулы.
Для упрощения создания и редактирования формул, а также для минимизации ошибок ввода и синтаксиса используйте Автозаполнение формул. После ввода знака = (знак равенства) и начальных букв функции Excel отобразит динамический раскрывающийся список допустимых функций, аргументов и имен, соответствующих этим буквам. Затем вы можете выбрать один из раскрывающегося списка, и приложение Excel введет его автоматически.
Вложение функций Excel
В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в следующей формуле используется вложенная функция СРЗНАЧ , а результат сравнивается со значением 50.
1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
<c0>Предельное количество уровней вложенности функций</c0>. В формулах можно использовать до семи уровней вложенных функций. Если функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, функция СРЗНАЧ и функция сумм являются функциями второго уровня, если они используются в качестве аргументов функции если. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня, и т. д.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Читайте также: