Как встроенные функции влияют на организацию расчетов в ms excel
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
Видеолекции для
профессионалов
- Свидетельства для портфолио
- Вечный доступ за 120 рублей
- 311 видеолекции для каждого
ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ В РАСЧЕТАХ MS EXCEL.
Цель занятия. Изучение информационной технологии использования в расчетах функций MS Excel.
Инструментарий. ПЭВМ IBM PC, программа MS Excel.
Литература. Практикум по информатике : учебное пособие-практикум / Елена Викторовна Михеева. – М.: Образовательно-издательский центр «Академия» , 2004.
ЗАДАНИЯ
Задание 1. Создать таблицу динамики розничных цен и произвести расчет средних значений.
Исходные данные представлены на рис.1.
Рис.1. Исходные данные для Задания 1
1. Запустите редактор электронных таблиц Microsoft Excel.
2. Откройте файл «Расчеты», созданный в практической работе № 1-2 ( Файл/ Открыть ).
3. Переименуйте ярлычок листа5, присвоив ему имя «Динамика цен».
4. На листе «Динамика цен» создайте таблицу по образцу как на рис.1.
5. Произведите расчет изменения цены в колонке «Е» по формуле:
Изменение цены = Цена на 01.06.2003 / Цена на 01.04.2003
Не забудьте задать процентный формат чисел в колонке «Е» ( Формат/ Ячейки/ Число/ Процентный ).
6. Рассчитайте средние значения по колонкам, пользуясь мастером Функций fx .
Функция СРЗНАЧ находится в разделе Статистические.
Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите Мастер функций (кнопкой «Вставка функции» fx или командой Вставка/ Функция ) и на первом шаге Мастера выберите функцию СРЗНАЧ (категория – Статистические/ СРЗНАЧ ) (рис.2).
Рис.2. Выбор функции расчета среднего значения СРЗНАЧ
После нажатия на кнопку «ОК» откроется окно для выбора диапазона данных для вычисления заданной функции.
В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК (рис.3).
В ячейке В14 появится среднее значение данных колонки «В».
Аналогично рассчитайте средние значения в других колонках.
Рис.3. Выбор диапазона данных для расчета функции среднего значения
7. В ячейке А2 задать функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере ( Вставка/ Функция/ Дата и Время/ СЕГОДНЯ ).
8. Выполните текущее сохранение файла ( Файл/ Сохранить ).
Задание 2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений.
Построить график по данным таблицы.
Исходные данные представлены на рис.4.
Рис.4. Исходные данные для Задания 2
1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.
Объединение выделенных ячеек производите кнопкой панели инструментов «Объединить и поместить в центре» или командой меню ( Формат/ Ячейки/ вкладка Выравнивание/ отображение – «Объединение ячеек »).
Краткая справка.
Изменение направления текста в ячейках производится путем поворота текста на 90 градусов в зоне «Ориентация» окна «Формат ячеек», вызываемого командой Формат/ Ячейки / вкладка Выравнивание/ Ориентация поворот надписи на 90 (рис.5).
Рис.5. Поворот надписи на 90 градусов
2. Произвести расчет средних значений по строкам и столбцам с использованием функции СРЗНАЧ.
3. Построить график изменения количества рабочих дней по годам и странам.
Подписи оси «Х» задайте при построении графика на втором экране Мастера диаграмм (вкладка Ряд , область Подписи оси «Х »).
4. После построения графика произведите форматирование вертикальной оси, задав минимальное значение 1500, максимальное значение 2500, цену деления 100 (рис.6).
Для форматирования оси выполните двойной щелчок мыши по ней и на вкладке «Шкала» диалогового окна «Формат оси» задайте соответствующие параметры оси.
Рис.6. Задание параметров шкалы оси графика
5. Выполните текущее сохранение файла «Расчеты» ( Файл/ Сохранить ).
Задание 3. Применение функции ЕСЛИ при проверке условий.
Создать таблицу расчета премии за экономию горюче смазочных материалов ГСМ.
Исходные данные представлены на рис.7.
1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.
Рис.7. Исходные данные для Задания 3
2. Произвести расчет Премии (25% от базовой ставки) по формуле:
Премия = Базовая ставка х 0,25
при условии, что План расходования ГСМ >Фактически израсходов ГМС.
Для проверки условия используйте функцию ЕСЛИ.
Для расчета Премии установите курсор в ячейке F4, запустите Мастер функций (кнопкой «Вставка функции» fx или командой Вставка/Функция ) и выберите функцию ЕСЛИ (категория – Логические/ ЕСЛИ ).
Задайте условие и параметры функции ЕСЛИ (рис.8.).
В первой строке «Логическое выражение» задайте условие C4>D4 .
Во второй строке задайте формулу расчета премии, если условие выполняется E4*0,25 .
В третьей строке задайте значение 0 , поскольку в этом случае (не выполнение условия) премия не начисляется.
Рис.8. Задание параметров функции ЕСЛИ
3. Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию.
Для сортировки установите курсор на любую ячейку таблицы, выберите в меню Данные команду Сортировка , задайте сортировку по столбцу «Фактически израсходовано ГСМ») (рис.9).
Рис.9. Задание параметров сортировки данных
4. Конечный вид расчетной таблицы начисления премии приведен на рис.10.
Рис.10. Конечный вид Задания 3.
5. Выполните текущее сохранение файла «Расчеты» ( Файл/ Сохранить ).
Недаром программа Microsoft Excel пользуется колоссальной популярностью как среди рядовых пользователей ПК, так и среди специалистов в разных отраслях. Все дело в том, что данное приложение содержит огромное количество интегрированных формул, называемых функциями. Восемь основных категорий позволяют создать практически любое уравнение и сложную структуру, вплоть до взаимодействия нескольких отдельных документов.
Понятие встроенных функций
Что же такое эти встроенные функции в Excel? Это специальные тематические формулы, дающие возможность быстро и качественно выполнить любое вычисление.
К слову, если самые простые из функций можно выполнить альтернативным (ручным) способом, то такие действия, как логические или ссылочные, - только с использованием меню «Вставка функции».
Таких функций в Excel очень много, знать их все невозможно. Поэтому компанией Microsoft разработан детальный справочник, к которому можно обратиться при возникновении затруднений.
Сама функция состоит из двух компонентов:
- имя функции (например, СУММ, ЕСЛИ, ИЛИ), которое указывает на то, что делает данная операция;
- аргумент функции - он заключен в скобках и указывает, в каком диапазоне действует формула, и какие действия будут выполнены.
Кстати, не все основные встроенные функции Excel имеют аргументы. Но в любом случае должен быть соблюден порядок, и символы «()» обязательно должны присутствовать в формуле.
Еще для встроенных функций в Excel предусмотрена система комбинирования. Это значит, что одновременно в одной ячейке может быть использовано несколько формул, связанных между собой.
Аргументы функций
Детально стоит остановиться на понятии аргумента функции, так как на них опирается работа со встроенными функциями Excel.
Аргумент функции – это заданное значение, при котором функция будет выполняться, и выдавать нужный результат.
Обычно в качестве аргумента используются:
- числа;
- текст;
- массивы;
- ошибки;
- диапазоны;
- логические выражения.
Как вводить функции в Excel
Создавать формулы достаточно просто. Сделать это можно, вводя на клавиатуре нужные встроенные функции MS-Excel. Это, конечно, при условии, что пользователь достаточно хорошо владеет Excel, и знает на память, как правильно пишется та или иная функция.
Второй вариант более подходит для всех категорий пользователей – использование команды «Функция», которая находится в меню «Вставка».
После запуска данной команды откроется мастер создания функций – небольшое окно по центру рабочего листа.
Там можно сразу выбрать категорию функции или открыть полный перечень формул. После того как искомая функция найдена, по ней нужно кликнуть и нажать «ОК». В выбранной ячейке появится знак «=» и имя функции.
Теперь можно переходить ко второму этапу создания формулы – введению аргумента функции. Мастер функций любезно открывает еще одно окно, в котором пользователю предлагается выбрать нужные ячейки, диапазоны или другие опции, в зависимости от названия функции.
Ну и в конце, когда аргумент уже выбран, в строке формул должно появиться полное название функции.
Виды основных функций
Теперь стоит более детально рассмотреть, какие же есть в Microsoft Excel встроенные функции.
Всего 8 категорий:
- математические (50 формул);
- текстовые (23 формулы);
- логические (6 формул);
- дата и время (14 формул);
- статистические (80 формул) – выполняют анализ целых массивов и диапазонов значений;
- финансовые (53 формулы) – незаменимая вещь при расчетах и вычислениях;
- работа с базами данных (12 формул) – обрабатывает и выполняет операции с базами данных;
- ссылки и массивы (17 формул) – прорабатывает массивы и индексы.
Как видно, все категории охватывают достаточно широкий спектр возможностей.
Математические функции
Данная категория является наиболее распространенной, так как математические встроенные функции в Excel можно применять для любого типа расчетов. Более того, эти формулы можно использовать в качестве альтернативы калькулятору.
Но это далеко не все достоинства данной категории функций. Большой перечень формул – 50, дает возможность создавать многоуровневые формулы для сложных научно-проектных вычислений, и для расчета систем планирования.
Самая популярная функция из этого радела – СУММ (сумма).
Она рассчитывает сумму нескольких значений, вплоть до целого диапазона. Кроме того, с ней очень удобно подсчитывать итог и выводить общую сумму в разных колонках чисел.
Еще один лидер категории – ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Позволяет считать итоговую сумму с нарастающим итогом.
Функции ПРОИЗВЕД (произведение), СТЕПЕНЬ (возведение чисел в степень), SIN, COS, TAN (тригонометрические функции) также очень популярны, и, что самое важное, не требуют дополнительных знаний для их введения.
Функции дата и время
Данная категория нельзя сказать, что уж очень распространена. Но использование встроенных функций Excel «Дата и время» дают возможность преобразовывать и манипулировать данные, связанные с временными параметрами. Если документ Microsoft Excel является структурным и сложноподчиненным, то применение временных формул обязательно имеет место.
Формула ДАТА указывает в выбранной ячейке листа текущее время. Аналогично работает и функция ТДАТА – она указывает текущую дату, преобразовывая ячейку во временной формат.
Логические функции
Отдельного внимания заслуживает категория функций «Логические». Работа со встроенными функциями Excel предписывает соблюдение и выполнение заданных условий, по результатам которых будет произведено вычисление. Собственно, условия могут задаваться абсолютно любые. Но результат имеет лишь два варианта выбора – Истина или Ложь. Ну или альтернативные интерпретации данных функций – будет отображена ячейка или нет.
Самая популярная команда – ЕСЛИ – предписывает указание в заданной ячейке значения являющего истиной (если данный параметр задан формулой) и ложью (аналогично). Очень удобна для работы с большими массивами, в которых требуется исключить определенные параметры.
Еще две неразлучные команды – ИСТИНА и ЛОЖЬ, позволяют отображать только те значения, которые были выбраны в качестве исходных. Все отличные от них данные отображаться не будут.
Формулы И и ИЛИ – дают возможность создания выбора определенных значений, с допустимыми отклонениями или без них. Вторая функция дает возможность выбора (истина или ложь), но допускает вывод лишь одного значения.
Текстовые функции
В данной категории встроенных функций Excel - большое количество формул, которые позволяют работать с текстом и числами. Очень удобно, так как Excel является табличным редактором, и вставка в него текста может быть выполнена некорректно. Но использование функций из данного раздела устраняет возможные несоответствия.
Так, формула БАТТЕКСТ преобразовывает любое число в каллиграфический текст. ДЛСТР подсчитывает количество символов в выбранном тексте.
Функция ЗАМЕНИТЬ находит и заменяет часть текста другим. Для этого нужно ввести заменяемый текст, искомый диапазон, количество заменяемых символов, а также новый текст.
Статистические функции
Пожалуй, самая серьезная и нужная категория встроенных функций в Microsoft Excel. Ведь известный факт, что аналитическая обработка данных, а именно сортировка, группировка, вычисление общих параметров и поиск данных, необходима для осуществления планирования и прогнозирования. И статистические встроенные функции в Excel в полной мере помогают реализовать задуманное. Более того, умение пользоваться данными формулами с лихвой компенсирует отсутствие специализированного софта, за счет большого количества функций и точности анализа.
Так, функция СРЗНАЧ позволяет рассчитать среднее арифметическое выбранного диапазона значений. Может работать даже с несмежными диапазонами.
Еще одна хорошая функция - СРЗНАЧЕСЛИ(). Она подсчитывает среднее арифметическое только тех значений массива, которые удовлетворяют требованиям.
Формулы МАКС() и МИН() отображают соответственно максимальное и минимальное значение в диапазоне.
Финансовые функции
Как уже упоминалось выше, редактор Excel пользуется очень большой популярностью не только среди рядовых обывателей, но и у специалистов. Особенно у тех, что очень часто используют расчеты – бухгалтеров, аналитиков, финансистов. За счет этого применение встроенных функций Excel финансовой категории позволяет выполнить фактически любой расчет, связанный с денежными операциями.
Excel имеет значительную популярность среди бухгалтеров, экономистов и финансистов не в последнюю очередь благодаря обширному инструментарию по выполнению различных финансовых расчетов. Главным образом выполнение задач данной направленности возложено на группу финансовых функций. Многие из них могут пригодиться не только специалистам, но и работникам смежных отраслей, а также обычным пользователям в их бытовых нуждах. Рассмотрим подробнее данные возможности приложения, а также обратим особое внимание на самые популярные операторы данной группы.
Функции работы с базами данных
Еще одна важная категория, в которую входят встроенные функции в Excel для работы с базами данных. Собственно, они очень удобны для быстрого анализа и проверок больших списков и баз с данными. Все формулы имеют общее название – БДФункция, но в качестве аргументов используются три параметра:
- база данных;
- критерий отбора;
- рабочее поле.
Все они заполняются в соответствии с потребностью пользователя.
База данных, по сути, это диапазон ячеек, которые объединены в общую базу. Исходя из выбранных интервалов, строки преобразовываются в записи, а столбцы – в поля.
Рабочее поле – в нем находится столбец, который используется формулой для определения искомых значений.
Критерий отбора – это интервал выбираемых ячеек, в котором находятся условия функции. Т. е. если в данном интервале имеется хотя бы одно сходное значение, то он подходит под критерий.
Наиболее популярной в данной категории функций является СЧЕТЕСЛИ. Она позволяет выполнить подсчет ячеек, попадающих под критерий, в выбранном диапазоне значений.
Еще одна популярная формула – СУММЕСЛИ. Она подсчитывает сумму всех значений в выбранных ячейках, которые были отфильтрованы критерием.
Функции ссылки и массивы
А вот данная категория популярна благодаря тому, что в нее вошли встроенные функции VBA Excel, т. е. формулы, написанные на языке программирования VBA. Собственно, работа с активными ссылками и массивами данных с помощью данных формул очень легка, настолько они понятны и просты.
Основная задача этих функций – вычленение из массива значений нужного элемента. Для этого задаются критерии отбора.
Таким образом, набор встроенных функций в Microsoft Excel делает данную программу очень популярной. Тем более что сфера применения таблиц этого редактора весьма разнообразна.
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
Видеолекции для
профессионалов
- Свидетельства для портфолио
- Вечный доступ за 120 рублей
- 311 видеолекции для каждого
Учебный материал для самостоятельного изучения дисциплины ЕН.01 Информатика
Тема занятия «ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ В РАСЧЕТАХ MS EXCEL»
Методические указания для самостоятельного изучения материала:
Цель занятия. Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel .
- Для получения оценки 3 (удовлетворительно), достаточно выполнить задание 18.1 и 18.2
- Для получения оценки 4 (хорошо), необходимо выполнить задания 18.1, 18.2 и 18.3
- Для получения оценки 5 (отлично), необходимо выполнить все задания Практической работы № 18.
Задание 18.1. Создать таблицу динамики розничных цен и произвести расчет средних значений.
Исходные данные представлены на рис. 18.1.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Программы/ Microsoft Excel ).
2. Откройте файл «Расчеты», созданный в Практических работах 16. 17 (Файл/Открыть).
Рис. 18.1. Исходные данные для задания 18.1
3. Переименуйте ярлычок Лист 5, присвоив ему имя «Динамика цен».
4. На листе «Динамика цен» создайте таблицу по образцу, как на рис. 18.1.
5. Произведите расчет изменения цены в колонке «Е» по формуле
Изменение цены = Цена на 01.06.2003/Цена на 01.04.2003.
Не забудьте задать процентный формат чисел в колонке «Е» ( Формат/ Ячейки/ Число/Процентный).
6. Рассчитайте средние значения по колонкам, пользуясь мастером функций f x . Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите мастер функций (кнопкой Вставка функции f x или командой Вставка/Функция) и на первом шаге мастера выберите функцию СРЗНАЧ (категория Статистические/СРЗНАЧ) (рис. 18.2).
После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК (рис. 18.3). В ячейке В14 появится среднее значение данных колонки «В».
Аналогично рассчитайте средние значения в других колонках.
7. В ячейке А2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере (Вставка/Функция/ Дата и Время/Сегодня).
8. Выполните текущее сохранение файла (Файл/Сохранить).
Рис. 18.2. Выбор функции расчета среднего значения СРЗНАЧ
Рис. 18.3. Выбор диапазона данных для расчета функции среднего значения
Задание 18.2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Построить график по данным таблицы.
Исходные данные представлены на рис. 18.4.
Порядок работы
На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию. Объединение выделенных ячеек произведите кнопкой панели инструментов Объединить и поместить в центре или командой меню ( Формат/Ячейки/вкладка Выравнивание/отображение – Объединение ячеек).
Рис. 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
Для проверки условия используйте функцию ЕСЛИ.
Для расчета Премии установите курсор в ячейке F 4, запустите мастер функций (кнопкой Вставка функции f x или командой Вставка/Функция) и выберите функцию ЕСЛИ (категория – Логические/ ЕСЛИ).
Задайте условие и параметры функции ЕСЛИ (рис. 18.8).
В первой строке «Логическое выражение» задайте условие С4 > D 4.
Во второй строке задайте формулу расчета премии, если условие выполняется Е4 * 0,25.
В третьей строке задайте значение 0, поскольку в этом случае (невыполнение условия) премия не начисляется.
3. Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию. Для сортировки установите курсор на любую ячейку таблицы, выберите в меню Данные команду
Рис. 18.8. Задание параметров функции ЕСЛИ
Рис. 18.9. Задание параметров сортировки данных
Рис. 18.10. Конечный вид задания 18.3
Сортировка, задайте сортировку по столбцу «Фактически израсходовано ГСМ» (рис. 18.9).
4. Конечный вид расчетной таблицы начисления премии приведен на рис. 18.10.
5. Выполните текущее сохранение файла «Расчеты» (Файл/Сохранить).
Офисная программа MS Excel одна из необходимых и трудозаменяемых программа в наше время. С каждым годом резко сокращается число предприятий и организаций, не имеющих компьютерной базы. Современные руководители, менеджеры и экономисты уже не представляют, как можно выполнять работу, не имея в своем распоряжении пакета офисных программ, электронной почты и Интернета. И это не случайно. Ведь в условиях конкуренции только эффективное ведение бизнеса позволяет выжить на рынке и добиться успеха.
MS Excel является весьма актуальной, потому, что табличные редакторы на сегодняшний день, одни из самых распространенных программных продуктов, используемые во всем мире. Они без специальных навыков позволяют создавать достаточно сложные приложения, которые удовлетворяют до 90% запросов средних пользователей.
Курсовая работа состоит из двух частей – теоретической и практической.
В теоретической части рассматривается тема «Обзор встроенных функций MS Excel»
В практической части помощью пакетов прикладных программ (ППП) будут решены и описаны следующие задачи: создание таблиц и заполнение таблиц данными; применение математических формул для выполнения запросов в ППП; построение графиков
Технические средства персонального компьютера, использованного для выполнения курсовой работы: процессор: CPU INTEL Pentium IV 2400 Мгц; оперативная память: SD RAM 512 Мб; жесткий диск: HDD 120 Гб;
Программные средства: операционная система Windows XP; Microsoft Word 2007; Microsoft Excel 2000.
Теоретическая часть
1. Общее представление о функциях MS Excel
Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.
Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “;”. Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы.
В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложенности функций.
Задаваемые входные параметры должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.
Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.
При помощи текстовых функций имеется возможность обрабатывать текст: извлекать символы, находить нужные, записывать символы в строго определенное место текста и многое другое.
Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения тех или иных условий будут совершать различные виды обработки данных.
В Excel широко представлены математические функции. Например, можно выполнять различные операции с матрицами: умножать, находить обратную, транспонировать.
Функции просмотра и ссылок позволяет «просматривать» информацию, хранящуюся в списке или таблице, а также обрабатывать ссылки.
MS EXCEL предоставляет широкие возможности для анализа статистических данных.
Функции для решения простых задач:
- Автосумма. Введите в ячейки А1, А2, А3 произвольные числа.
Активизируйте ячейку А4 и нажмите кнопку автосумма. Нажмите клавишу ввода. В ячейку А4 будет вставлена формула суммы ячеек А1..А3.
- Вычисление среднего арифметического последовательности чисел:
- Нахождение максимального (минимального) значения: =МАКС(числа)
- Вычисление медианы (числа являющегося серединой множества):
Функции предназначены для анализа выборок генеральной совокупности данных:
- Дисперсия: ДИСП(числа).
- Стандартное отклонение: =СТАНДОТКЛОН(числа).
- Ввод случайного числа: =СЛЧИС() .
Так же можно использовать в формулах вместо ссылок на ячейки таблицы заголовки таблицы.
По умолчанию Microsoft Excel не распознает заголовки в формулах. Чтобы использовать заголовки в формулах, нужно выбрать команду Параметры в меню Сервис. На вкладке Вычисления в группе Параметры книги установите флажок Допускать названия диапазонов.
Формулы, содержащие заголовки, можно копировать и вставлять, при этом Excel автоматически настраивает их на нужные столбцы и строки. Если будет произведена попытка скопировать формулу в неподходящее место, то Excel сообщит об этом, а в ячейке выведет значение ИМЯ?. При смене названий заголовков, аналогичные изменения происходят и в формулах.
2. Подробное описание каждой из встроенных функций Excel
2.1 Математические функции Excel
В Microsoft Excel имеется целый ряд встроенных математических функций, позволяющих легко и быстро выполнять различные специализированные вычисления. Кроме того, множество математических функций включено в надстройку Пакет анализа.
множество чисел. Эта функция имеет следующий синтаксис: =СУММ(числа).
Аргумент числа может включать до 30 элементов, каждый из которых может быть числом, формулой, диапазоном или ссылкой на ячейку, содержащую или возвращающую числовое значение. Функция СУММ игнорирует аргументы, которые ссылаются на пустые ячейки, текстовые или логические значения. Например, чтобы получить сумму чисел в ячейках А2, В10 и в ячейках от С5 до К12, введите каждую ссылку как отдельный аргумент: =СУММ(А2;В10;С5:К12).
- Функции ОКРУГЛ, ОКРУГЛВНИЗ, ОКРУГЛВВЕРХ. Функция
ОКРУГЛ (ROUND) округляет число, задаваемое ее аргументом, до указанного количества десятичных разрядов и имеет следующий синтаксис: =ОКРУГЛ(число;количество_цифр).
Аргумент число может быть числом, ссылкой на ячейку, в которой содержится число, или формулой, возвращающей числовое значение. Аргумент количство_цифр, который может быть любым положительным или отрицательным целым числом, определяет, сколько цифр будет округляться. Задание отрицательного аргумента количество_цифр округляет до указанного количества разрядов слева от десятичной запятой, а задание аргумента количество_цифр равным 0 округляет до ближайшего целого числа. Excel цифры, которые меньше 5, с недостатком (вниз), а цифры, которые больше или равны 5, с избытком (вверх).
(ROUNDUP) имеют такой же синтаксис, как и функция ОКРУГЛ. Они округляют значения вниз (с недостатком) или вверх (с избытком).
- Функции ЧЁТН и НЕЧЁТ. Для выполнения операций округления
можно использовать функции ЧЁТН (EVEN) и НЕЧЁТ (ODD). Функция ЧЁТН округляет число вверх до ближайшего четного целого числа. Функция НЕЧЁТ округляет число вверх до ближайшего нечетного целого числа. Отрицательные числа округляются не вверх, а вниз. Функции имеют следующий синтаксис: =ЧЁТН(число), =НЕЧЁТ(число).
-
Функции ЦЕЛОЕ и ОТБР. Функция ЦЕЛОЕ (INT) округляет число
вниз до ближайшего целого и имеет следующий синтаксис: =ЦЕЛОЕ(число).
Аргумент - число - это число, для которого надо найти следующее наименьшее целое число.
десятичной запятой независимо от знака числа. Необязательный аргумент количество_цифр задает позицию, после которой производится усечение. Функция имеет следующий синтаксис: =ОТБР(число;количество_цифр).
- Функции ОКРУГЛ, ЦЕЛОЕ и ОТБР удаляют ненужные десятичные
знаки, но работают они различно. Функция ОКРУГЛ округляет вверх или вниз до заданного числа десятичных знаков. Функция ЦЕЛОЕ округляет вниз до ближайшего целого числа, а функция ОТБР отбрасывает десятичные разряды без округления. Основное различие между функциями ЦЕЛОЕ и ОТБР проявляется в обращении с отрицательными значениями. Функции СЛЧИС и СЛУЧМЕЖДУ. Функция СЛЧИС (RAND) генерирует случайные числа, равномерно распределенные между 0 и 1, и имеет следующий синтаксис: СЛЧИС()
- Функция СЛЧИС является одной из функций EXCEL, которые не
имеют аргументов. Как и для всех функций, у которых отсутствуют аргументы, после имени функции необходимо вводить круглые скобки.
Значение функции СЛЧИС изменяется при каждом пересчете листа. Если установлено автоматическое обновление вычислений, значение функции СЛЧИС изменяется каждый раз при воде данных в этом листе.
- Функция СЛУЧМЕЖДУ (RANDBETWEEN), которая доступна,если
установлена надстройка "Пакет анализа", предоставляет больше возможностей, чем СЛЧИС. Для функции СЛУЧМЕЖДУ можно задать интервал генерируемых случайных целочисленных значений.
Синтаксис функции: =СЛУЧМЕЖДУ(начало;конец).
перемножает все числа, задаваемые ее аргументами, и имеет следующий синтаксис: =ПРОИЗВЕД(число1;число2. ).
- Функция ОСТАТ. Функция ОСТАТ (MOD) возвращает остаток
от деления и имеет следующий синтаксис: =ОСТАТ(число;делитель).
Значение функции ОСТАТ - это остаток, получаемый при делении аргумента число на делитель. Если число меньше чем делитель, то значение функции равно аргументу число.
Если число точно делится на делитель, функция возвращает 0. Если делитель равен 0, функция ОСТАТ возвращает ошибочное значение.
положительный квадратный корень из числа и имеет следующий синтаксис: =КОРЕНЬ(число)
определяет количество возможных комбинаций или групп для заданного числа элементов. Эта функция имеет следующий синтаксис: =ЧИСЛОКОМБ(число;число_выбранных)
Аргумент число - это общее количество элементов, а число_выбранных - это количество элементов в каждой комбинации.
является ли значение числом, и имеет следующий синтаксис: =ЕЧИСЛО(значение)
Пусть вы хотите узнать, является ли значение в ячейке А1 числом. Следующая формула возвращает значение ИСТИНА, если ячейка А1 содержит число или формулу, возвращающую число; в противном случае она возвращает ЛОЖЬ: =ЕЧИСЛО(А1)
положительного числа по заданному основанию. Синтаксис: =LOG(число;основание)
- Функция LN. Функция LN возвращает натуральный логарифм
положительного числа, указанного в качестве аргумента. Эта функция имеет следующий синтаксис: =LN(число)
- Функция EXP. Функция EXP вычисляет значение константов,
возведенных в заданную степень. Эта функция имеет следующий синтаксис:
EXP(число). Функция EXP является обратной по отношению к LN. Например, пусть ячейка А2 содержит формулу: =LN(10)
- Функция ПИ. Функция ПИ (PI) возвращает значение константы
пи с точностью до 14 десятичных знаков. Синтаксис: =ПИ()
- Функция РАДИАНЫ и ГРАДУСЫ. Тригонометрические
Вы можете преобразовать радианы в градусы, используя функцию ГРАДУСЫ. Синтаксис: =ГРАДУСЫ(угол).
Для преобразования градусов в радианы используется функция РАДИАНЫ, которая имеет следующий синтаксис: =РАДИАНЫ(угол).
- Функция SIN/COS/TAN.Возвращает синус/косинус/тангенс угла
и имеет следующий синтаксис: =SIN/ COS / TAN (число).
2.2.Текстовые функции Excel
Текстовые функции преобразуют числовые текстовые значения в числа и числовые значения в строки символов (текстовые строки), а также позволяют выполнять над строками символов различные операции.
- Функция ТЕКСТ. Функция ТЕКСТ (TEXT) преобразует число в
текстовую строку с заданным форматом. Синтаксис: =ТЕКСТ(значение;формат)
Аргумент значение может быть любым числом, формулой или ссылкой на ячейку. Аргумент формат определяет, в каком виде отображается возвращаемая строка. Для задания необходимого формата можно использовать любой из символов форматирования за исключением звездочки. Использование формата Общий не допускается.
- Функция РУБЛЬ. Функция РУБЛЬ (DOLLAR) преобразует число в
строку. Однако РУБЛЬ возвращает строку в денежном формате с заданным числом десятичных знаков. Синтаксис: =РУБЛЬ(число;число_знаков).
При этом Excel при необходимости округляет число. Если аргумент число_знаков опущен, Excel использует два десятичных знака, а если значение этого аргумента отрицательное, то возвращаемое значение округляется слева от десятичной запятой.
- Функция ДЛСТР. Функция ДЛСТР (LEN) возвращает количество
символов в текстовой строке и имеет следующий синтаксис: =ДЛСТР(текст)
Аргумент текст должен быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку. Функция ДЛСТР возвращает длину отображаемого текста или значения, а не хранимого значения ячейки. Кроме того, она игнорирует незначащие нули.
- Функция СИМВОЛ и КОДСИМВ. Любой компьютер для
представления символов использует числовые коды. Наиболее распространенной системой кодировки символов является ASCII. В этой системе цифры, буквы и другие символы представлены числами от 0 до 127 (255). Функции СИМВОЛ (CHAR) и КОДСИМВ (CODE) как раз и имеют дело с кодами ASCII. Функция СИМВОЛ возвращает символ, который соответствует заданному числовому коду ASCII, а функция КОДСИМВ возвращает код ASCII для первого символа ее аргумента. Синтаксис функций: =СИМВОЛ(число), =КОДСИМВ(текст)
Если в качестве аргумента текст вводится символ, обязательно надо заключить его в двойные кавычки: в противном случае Excel возвратит ошибочное значение.
- Функции СЖПРОБЕЛЫ и ПЕЧСИМВ. Часто начальные и конечные
пробелы не позволяют правильно отсортировать значения в рабочем листе или базе данных. Если вы используете текстовые функции для работы с текстами рабочего листа, лишние пробелы могут мешать правильной работе формул. Функция СЖПРОБЕЛЫ (TRIM) удаляет начальные и конечные пробелы из строки, оставляя только по одному пробелу между словами. Синтаксис: =СЖПРОБЕЛЫ(текст).
- Функция ПЕЧСИМВ (CLEAN) аналогична функции СЖПРОБЕЛЫ за
исключением того, что она удаляет все непечатаемые символы. Функция ПЕЧСИМВ особенно полезна при импорте данных из других программ, поскольку некоторые импортированные значения могут содержать непечатаемые символы. Эти символы могут проявляться на рабочих листах в виде небольших квадратов или вертикальных черточек. Функция ПЕЧСИМВ позволяет удалить непечатаемые символы из таких данных. Синтаксис: =ПЕЧСИМВ(текст)
- Функция СОВПАД. Функция СОВПАД (EXACT) сравнивает две
строки текста на полную идентичность с учетом регистра букв. Различие в форматировании игнорируется. Синтаксис: =СОВПАД(текст1;текст2)
- Функции ПРОПИСН, СТРОЧН и ПРОПНАЧ. Функция ПРОПИСН
преобразует все буквы текстовой строки в прописные, а СТРОЧН - в строчные. Функция ПРОПНАЧ заменяет прописными первую букву в каждом слове и все буквы, следующие непосредственно за символами, отличными от букв; все остальные буквы преобразуются в строчные. Эти функции имеют следующий синтаксис: =ПРОПИСН(текст), =СТРОЧН(текст), =ПРОПНАЧ(текст)
- Функции ЕТЕКСТ и ЕНЕТЕКСТ. Функции ЕТЕКСТ (ISTEXT) и
ЕНЕТЕКСТ (ISNOTEXT) проверяют, является ли значение текстовым. Синтаксис: =ЕТЕКСТ(значение), =ЕНЕТЕКСТ(значение)
Предположим, вы хотите определить, является ли значение в ячейке С5 текстом. Если в ячейке С5 находится текст или формула, которая возвращает текст, можно использовать формулу: =ЕТЕКСТ(С5). В этом случае Excel возвращает логическое значение ИСТИНА. Аналогично, если вы проверите ту же ячейку, используя формулу =ЕНЕТЕКСТ(С5) Excel возвращает логическое значение ЛОЖЬ.
Логические функции Excel
Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Например, каждая из представленных ниже формул является логическим выражением:
Цель занятия. Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel.
Задание 18.1. Создать таблицу динамики розничных цен и произвести расчет средних значений.
Исходные данные представлены на рис. 18.1.
1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Excel).
2. Откройте файл «Расчеты», созданный в Практических работах 16…17 (Файл/Открыть).
Рис. 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 (видео-урок)
Похожие статьи:
В общем случае функция – это переменная величина, значение которой зависит от значений других величин (аргументов). Функция имеет имя (например, КОРЕНЬ)…
Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Функция ЕСЛИ позволяет определить, выполняется ли…
Читайте также: