В MS EXCEL находится более чем 300 функций, которые разделены на категории. Также с помощью VBA можно создавать свои функции.
Функция – это готовая специализированная формула, которая составляется из имени функции и аргумента или нескольких аргументов.
Синтаксис (правило написания) функции:
Имя_Функции (Аргумент)
Аргументом функции может быть: пустой аргумент (), константа, ссылка на ячейку, диапазон, несколько аргументов, выражение, другие функции.
Введение функции в ячейку:
1. Выделить ячейку, в которую вставляем функцию.
2. Формулы ð Вставить функцию или <fх> или <Shift + F3>
3. В поле Категория выбрать нужную категорию.
4. В поле Функция -функцию. ОК
Внизу диалогового окна появится короткое описание этой функции. Если необходимо узнать подробнее, то нужнот вызвать справку.
5. В открывшемся диалоговом окне, ввести аргументы.
6. После выбора аргументов в нижней части диалогового окна будет виден результат. Если он правильный, то <ОК>.
7. Если аргументом функции должна быть другая функция, то щелкнуть в поле введения аргумента и выбрать нужную функцию из списка.
Для создания расчетов с помощью функций необходимо использовать «Мастер функций».
Для этого в офисе существует следующая команда:
Формулы – Вставка функции,
или можно воспользоваться инструментом 
Вставка функции на вкладке Формулы.
Потом выбрать определенную функцию в выбранной категории: математические, статистические, логические.
Рисунок 5.25 - Окно мастера функций
Формулу с применением функций можно также вводить вручную. В этом случае нужно помнить, что аргументы функции отделяются один от другого точкой с запятой.
В нижней части окна мастера функций содержатся короткие сведения о функции. Для более детальной справки о назначении функции с примерами применения можно воспользоваться кнопкой 
. Для продолжения создания формулы необходимо нажать кнопку Ок.
На втором шаге определяются аргументы функции. Аргументами могут быть числа, ячейки электронной таблицы, диапазоны ячеек, логические условия.
Рассмотрим синтаксис наиболее часто используемых функций
Логические функции:
Чтобы вызвать определенную функцию из категории «логические» существует команда: Формулы – 
.
Из данной категории мы рассмотрим следующие.
Функция ЕСЛИ() дает возможность организовать разветвление в зависимости от истинности некоторого логического условия.
Функция ЕСЛИ() имеет три аргумента. Первым аргументом функции является логическое условие, истинность которой проверяется. Второй аргумент - это значения или выражения для вычисления значения ячейки при истинности условия. Третий аргумент - это значения или выражения для вычисления значения ячейки, если условие не является истиной.
=ЕСЛИ(условие;истина;ложь)
Пример
=ЕСЛИ(D3>=0; E3*0,4; E3*0,1)
Если содержимое ячейки D3 больше или равно «0», то для расчетов используется выражение E3*0,4. В другом случае (когда содержимое ячейки меньше «0») расчеты выполняется за выражением E3*0,1.
Функция И() дает возможность объединить несколько логических условий. Аргументами функции И() могут быть от двух до тридцати логических условий. Функция И() возвращает значение “Истина” лишь тогда, когда каждый ее аргумент имеет значение “Истина”.
Пример
= ЕСЛИ(И(В3>=0;С3>=0;D3>=0); E3*0,22;0)
Функция ИЛИ() используется для объединения нескольких логических условий. Аргументами функции ИЛИ() могут быть от двух до тридцати логических условий. Функция ИЛИ() возвращает значение “Истина” тогда, когда хотя бы один ее аргумент имеет значение “Истина”.
Пример
= ЕСЛИ(ИЛИ(В3>=0;С3>=0;D3>=0); E3*0,22;0)
Математические функции:
Чтобы вызвать определенную функцию из категории «математические» в офисе 2007 существует команда: Формулы - математические
Из данной категории мы рассмотрим следующие.
Функция СУММ() используется для суммирования значений из заданного интервала. Аргументами функции являются ячейки, содержание которых необходимо подытожить, логические значения и текст игнорируются.
Пример
=СУММ(С3:С10)
Функция СУММЕСЛИ() используется для суммирования значений из заданного интервала, которые удовлетворяют необходимому критерию. Первым аргументом функции является интервал ячеек. Вторым аргументом является критерий в виде числа, выражение или текста, который определяет, какая ячейка добавляется. Третьим аргументом являются фактические ячейки для суммирования.
Пример
= СУММЕСЛИ(С3:С10; «>2»)
Статистические функции:
Чтобы вызвать определенную функцию из категории «статистические» в офисе 2007 существует команда: Формулы – другие функции - статистические
Из данной категории мы рассмотрим следующие.
Функция СЧЕТЕСЛИ() используется для подсчета количества ячеек, которые удовлетворяют заданному критерию внутри интервала. Первым аргументом функции является интервал, в котором происходит подсчет. Вторым аргументом является критерий в виде числа, выражения или текстовой строки, которая определяет, какие ячейки учитываются.
Пример
= СЧЕТЕСЛИ(С3:С10; «>0»)
Функция СРЗНАЧ() используется для определения среднего арифметического значения аргументов. Аргументами функции являются числа, массивы или адресные ссылки на диапазон ячеек. Функция позволяет задавать от 1 до 30 аргументов.
Пример
= СРЗНАЧ(D3:D10)
Функция МАКС() используется для определения самого большого значения из списка аргументов. Аргументами могут быть от 1 до 30 чисел. Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями.
Пример
= МАКС(D3:D10)
Функция МИН() используется для определения наименьшего значения из списка аргументов. Аргументами могут быть от 1 до 30 чисел; игнорируются значения ошибки или текст, которые не могут быть преобразованы в числа.
Пример
=МИН(D3:D10)
Рассмотрим функцию:
РАЗНДАТ (начальная_дата; конечная_дата; способ_измерения) , где
аргумент способ_измерения определяет каким образом и в каких единицах будет измеряться интервал между начальной и конечной датами. Этот параметр будет принимать значение:
| Средство измерения | Значение параметра |
| «y» | Разница в полных годах |
| «m» | разница в полных месяцах |
| «d» | разница в полных днях |
| «yd» | разница в днях с начала года без счета лет |
| «md» | разница в днях без счета месяцев и лет |
| «ym» | разница в полных месяцах без счета лет |
Функция СЕГОДНЯ () возвращает текущую дату в числовом формате, дает возможность использовать текущую дату в формуле, выражении и т.п. Она не нуждается в аргументах.
Примеры использования стандартных функций в экономических расчетах
Пример 1. Рассчитать сколько месяцев эксплуатировался автомобиль на момент продажи.
Методические указания.
В ячейку А2 необходимо ввести дату приобретения автомобиля, в ячейку В2 необходимо ввести дату продажи автомобиля, а в С2 необходимо ввести в следующую формулу:
=РАЗНДАТ(A2;B2;"m")
Пример 2. Рассчитать количество дней, в течении которых, товар находится на складе временного хранения.
Методические указания.
В ячейку А2 необходимо ввести дату изготовления товара, а в В2 необходимо ввести в следующую формулу:
=РАЗНДАТ(A2;СЕГОДНЯ();"d")
Пример 3. Рассчитать стаж работы работника в виде " 9 г. 4 мес. 12 дн."
Методические указания.
В ячейку А2 необходимо ввести дату вступления в работу, в ячейку В2 необходимо ввести дату увольнения с работы, а в С2 необходимо ввести следующую формулу:
=РАЗНДАТ( A 2; B 2;" y ") & "г. " & РАЗНДАТ( A 2; B 2;" ym ") & " мес. " & РАЗНДАТ(A2;B2;"md") & " дн."
Пример 4. Рассчитать сумму скидки товара. Если количество приобретенного товара более 3 шт., то сумма скидки составляет 10 %.
Рисунок 5.26 – Пример таблицы для расчета данных с использованием формул
Методические указания.
1. Рассчитать Сумму приобретенного товара:
1.1. в ячейке D3 создать формулу: =В3*С3;
1.2. используя маркер заполнения скопировать формулу до конца вычислений;
2. Рассчитать Сумму скидки приобретенного товара:
2.1. в ячейке Е3 создать формулу: =Если(В3>3;D3*0,1;0)
Для этого выполните команды: Формулы - Логические – ЕСЛИ.
Заполнить окно диалога по образцу.
2.2. используя маркер заполнения скопировать формулу до конца вычислений.
3. Рассчитать Сумму с учетом скидки:
3.1. в ячейке F3 создать формулу: = D3-E3;
3.2. используя маркер заполнения скопировать формулу до конца вычислений.
Пример №5. Рассчитать сумму скидки товара. Если количество приобретенного товара больше 3 шт., то сумма скидки составляет 10 %. Если количество приобретенного товара больше 10 шт., то сумма скидки - 15%.
Методические указания.
1. Рассчитать Сумму скидки приобретенного товара:
1.1. в ячейке Е3 создать формулу: =Если(В3<=3;0;Если(В3>10;D3*0,15;D3*0,1))
1.2. используя маркер заполнения скопировать формулу до конца вычислений.
Пример №6. Рассчитать сумму скидки товара. Если покупатель приобрел больше 3 дверей, то ему предоставляется скидка в размере 5%. Если покупатель приобрел больше 4 окон, ему предоставляется скидка в размере 10%.
Методические указания.
1. Рассчитать Сумму скидки приобретенного товара:
1.1. в ячейке Е3 создать формулу:
=Если(И(А3=”Двери”;В3>3);D3*0,05;Если(И(А3=”Окно”;В3>4);D3*0,1;0))
1.1.1 Вызвать функцию ЕСЛИ, установить курсор в поле Логическое_выражение.
1.1.2. Открыть в строке формул, список, который растворяется с помощью инструмента 
и выбрать функцию И. Если название в списке нет, то элемент выбирается с помощью списка Другие функции.
1.1.3. Заполняем окно функции И
1.1.4. В строке формул с клавиатуры дописать выражение ;D3*0,05;
1.1.5. Открыть в строке формул, открывающийся список, инструментов 
и выбрать функцию ЕСЛИ, поля не заполнять.
1.1.6. Открыть в строке формул открывающийся список и выбрать функцию И. Заполняем окно функции И.
1.1.7. Потом снова с клавиатуры дописываем выражение ;D3*0,1;0))
1.2 используя маркер заполнения скопировать формулу до конца вычислений.
Пример №7. Рассчитать в таблице максимальные и минимальные значения
Методические указания.
1. Сделать активной ячейку В12.
2. Вызвать мастер функций 
, слева в окне выбрать категорию Статистический по правую сторону выбрать функцию МАКС.
3. Щелкнуть на кнопке свернуть 
, выделить диапазон В3:В10.
4. Развернуть окно мастера функций щелкнув на кнопке 
. Щелкнуть на кнопке ОК.
Самостоятельно рассчитать максимальное значение в ячейке Е12. Можно скопировать формулу используя маркер заполнения.
5. Сделать активной ячейку В13.
6. Вызвать мастер функций 
, слева в окне выбрать категорию Статистические, по правую сторону выбрать функцию МИН.
7. Щелкнуть на кнопке свернуть 
, выделить диапазон В3:В10.
8. Развернуть окно мастера функций щелкнув на кнопке 
. Щелкнуть на кнопке ОК
Самостоятельно рассчитать минимальное значение в ячейке Е13. Можно скопировать формулу используя маркер заполнения.
Пример №8. Рассчитать в таблице количество скидок.
Методические указания.
1. Сделать активной ячейку Е14.
2. Вызвать мастер функций , слева в окне выбрать категорию Статистический по правую сторону выбрать функцию СЧЕТЕСЛИ.
3. Щелкнуть на кнопке свернуть , выделить диапазон Е3:Е10.
4. Развернуть окно мастера функций щелкнув на кнопке .
5. В поле Условие ввести >0
6. Щелкнуть на кнопке ОК.
Примеры задач по теме «Табличный процессор MS Excel. Создание диаграмм»
1. Заполните данными и рассчитайте таблицу согласно варианту.
2. Создайте нумерацию столбиков и строк, с помощью маркера автозаполнения и клавиши CTRL.
3. Создайте автосписок для заполнения первой или второй колонки таблицы.
4. Проанализируйте данные с помощью диаграмм.
5. Скопируйте таблицу на Лист 2. Установите режим вывода формул.
6. Переименуйте Лист 2 дав ему имя Формулы.
7. Подготовьте созданные документы в печать в режиме Предварительный просмотр:
8. ориентация страниц – альбомная;
9. колонтитулы: верхний - дата создания, нижний – фамилия студента и группа.
10. Распечатайте созданные документы.
11. Сохраните файл на диске, дав ему имя (фамилия студента).
Вариант №1
Сведения реализации товаров
| № п/п | ||
Наименование товара
2.
Итог:
Торговая наценка
1. Построить круговую диаграмму анализа сумм товара. . Расположить на одном листе с таблицей.
2. С помощью гистограммы сделать сравнительный анализ суммы товаров и сумм продажи с учетом наценки. Расположить на отдельном письме.
Вариант №2
Расчет прибыли
| Статья дохода | Первый квартал | Вместе | Удельный вес | ||
| январь | февраль | март | |||
| Компьютеры | 35870 | 30090 | 25000 | * | * |
| Комплектующие | 43500 | 45000 | 42000 | * | * |
| Программное обеспечение | 5431 | 3000 | 6000 | * | * |
| Доход всего: | * | * | * | * | |
| Статьи затрат | |||||
| Реклама | 10000 | 100000 | 4000 | * | * |
| Аренда | 25000 | 25000 | 25000 | * | * |
| Налоги | 3000 | 3500 | 4000 | * | * |
| Затраты всего: | * | * | * | * | |
| Прибыль: | * | * | * | * | |
1. Построить круговую диаграмму анализа дохода за первый квартал. Расположить на одном письме с таблицей.
2. Построить гистограмму сравнения затрат февраля и марта. Расположить на отдельном письме.
Примеры задач по теме «Табличный процессор MS Excel. Анализ данных с помощью сводных таблиц»
1. Заполните таблицу согласно варианту.
2. Рассчитайте ячейки где содержатся звездочки (*).
3. Создайте сводные таблицы согласно вариантам задач.
4. Для третьей сводной таблицы постройте объемную диаграмму.
5. Выполните фильтрацию данных в начальной таблице с помощью автофильтра.
6. Сохраните файл на диске, дав ему имя (фамилия студента).
Вариант № 1
Дата: 2019-02-02, просмотров: 303.
