3. Применение функций

Определение функции в MS Excel. Рассмотрено применение некоторых функций ЕСЛИ, СУММ, СУММЕСЛИ, СЧЁТЕСЛИ, ЧАС, ГПР, СЧИТАТЬПУСТОТЫ, МАКС, МИН, СРЗНАЧ, РАНГ. Показана разница между простым суммированием ячеек и функцией СУММ.

Это подпрограмма написанная разработчиками, которая имеет своё имя в скобках аргумент или аргументы и самое главное – функция возвращает результат. Результат будет вычислен для того, что введено в скобках, это может быть число, ячейка или логическое выражение, и он будет отображён в той ячейке, в которой написана функция.

Рассмотрим, как можно просуммировать ячейки из колонки A, результат разместим в ячейке A7. Курсор помещаем в A7, и вводим последовательно операторы сложения =A1+A2+A3+A4+A5:

Но при большом количестве ячеек, данная процедура утомительна, и поэтому здесь применяют функцию суммирования которая имеет синтаксис СУММ(параметры), где в скобках через оператор ; можно указать одиночные ячейки, а через оператор : диапазон ячеек. Диапазон удобен, т.к. из множества смежных ячеек, указываются только две ячейки — первая и последняя.

Рассмотрим некоторые примеры диапазонов:

• Диапазон ячеек: A1:A5 или A1..A5 – все ячейки от A1 до A5
• Диапазон ячеек: A1:B10 или A1..B10 – все ячейки от A1 до B10
• Группа ячеек: A1;A3;A5 – просто три ячейки
• Два в одном диапазон и группа: A1;A3;A5:A10 – восемь ячеек

Теперь просуммируем ячейки из колонки B: вводим в ячейке B7 формулу =СУММ(B1:B5)

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

Введём в ячейки A1:A3 числа 10,20 и 30, а в ячейке A4 отобразим сумму:

1 способ: в ячейке A4 введём =СУММ(A1:A3), нажимаем [Enter]

2 способ: курсор в A4, перед строкой формул, нажимаем кнопку Вставить функцию , в диалоге Мастер функций в списке категорий выбираем Математические, ниже в списке функций выбираем СУММ нажимаем ОК. В диалоге Аргументы функции в поле Число1 указываем диапазон A1:A3. Нажимаем ОК

3 способ: в ячейке A4 введём =СУММ( и нажмём комбинацию [Shift+F3], далее диалоге Аргументы функции поступаем аналогично второму способу

Итак, уже известно что функция СУММ(), суммирует ячейки указанные в скобках и это удобно вместо «ручного» суммирования ячеек, т.е. функция=СУММ(A1:A4), заменяет формулу=A1+A2+A3+A4. Просуммируем этими способами одинаковые числа на двух разных:

Но при использовании функции, добавление ячеек в диапазон суммирования, автоматически изменит запись диапазона в функции, тогда как в обычной формуле, автоматически ничего не произойдёт. Добавим между 2-й и 3-й строкой, пустую строку, и введём в диапазон суммирования, число 5000:

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

В MS Excel истинна и ложь обозначается, как ИСТИННА и ЛОЖЬ, а можно числовыми значениями 1 и 0. Эти значения есть результат проверки исходных данных ни истинность или ложность. Проверку, делают операторы сравнения. Проверим исходные данные.

Откроем файл Логические функции, активируем лист Логические операторы.

Начиная с ячейки A1, введём числовую последовательность [-20..20] с шагом 10. Сравним эти элементы последовательности с некоторыми значениями:

• Введём в ячейку B1: =A1>0 и скопируем ячейку B1 вниз, до последнего элемента.
• Введём в ячейку C1: =A1<0 и скопируем ячейку C1 вниз, до последнего элемента.
• Введём в ячейку D1: =A1=0 и скопируем ячейку D1 вниз, до последнего элемента.

В ячейке E1 введём =ЕСЛИ( и нажмём [Shift+F3], в первом поле введём условие A1>0, во втором поле введём любое значение, которое отобразится в ячейке E1, если результат в первом поле истина например, ДА. В третьем поле введём любое значение которое отобразится в ячейке E1, если результат в первом поле ложь например, НЕТ

Копируем формулу в E1 для ячеек E2:E5:

меняет значение ИСТИНА на ЛОЖЬ и наоборот или значение 0 на 1 и наоборот. В колонке F отобразим изменённые значения колонки D: введём в ячейку F1 формулу: =НЕ(D1) и скопируем до последнего элемента

Это одновременное выполнение всех условий. Составим сложное условие. Выведем – ИСТИНА, если значения в колонке A положительные и в ячейке G7 будет слово “ромашки” и ЛОЖЬ в любом другом случае: в ячейку F1 вводим =И( и нажимаем [Shift+F3]. В первое поле вводим A1>0, во второе G$7=”ромашки”.

Копируем формулу в G1 для ячеек G2:G5:

Теперь вместо ИСТИНА выведем – ЛЮБИТ, а вместо ЛОЖЬ – НЕ ЛЮБИТ: курсор снова в ячейку G1.
В строке формул разместим курсор между знаком = и функцией И:

вводим ЕСЛИ( и нажимаем [Shift+F3]. Откроется диалог с аргументами функции И, нам он не нужен т.к. параметры этой функции корректны.

Курсор в конец строки и вводим символ ; Появится диалог функции ЕСЛИ:

Во второе поле вводим ЛЮБИТ, в третье НЕ ЛЮБИТ:

Нажимаем ОК и снова копируем формулу в G1 для ячеек G2:G5:

Это выполнение хотя бы одного условия из двух. Перепишем предыдущую формулу, но вместо И вставим ИЛИ:

проверим результат для ячеек G1:G5:

поменяем в ячейке G7 ромашки на лютики, смотрим результат:

Перейдём на лист Премия. Нужно начислить премию согласно условию, если стаж сотрудника больше 10 лет, то премия 20% от оклада, иначе премия 4000

В ячейке D2 введём =ЕСЛИ( и нажмём [Shift+F3], в первом поле введём условие B2>10, во втором поле введём C2*20%, в третьем — 4000. Скопируем ячейку в столбце D, для премий всех сотрудников

Перейдём на лист ЕСЛИ Вложенное. Здесь нужно вычислить сумму скидки, которая зависит от суммы покупки. Скидок три:

• если сумма покупки менее 2000 – скидка 12%,
• если сумма покупки от 2000 до 3000 – скидка 16%,
• если сумма покупки более 3000 – скидка 20%,

Обычная функция ЕСЛИ не подходит, т.к. у нас не два, а три условия. Нужно применить функцию ЕСЛИ, дважды в одной формуле:

В ячейке C3 введём =ЕСЛИ( и нажмём [Shift+F3], в первом поле введём условие B3<2000, во втором параметре введём 12%, а для третьего параметра нужно ввести вложенную функцию ЕСЛИ: в списке имён находим функцию ЕСЛИ. Если её там нет то выбираем пункт Другие функции:

Далее в мастере функций в категории Логические внизу ищем функцию ЕСЛИ. Перед нами снова диалог с тремя полями для вложенной функции. В первом поле вводим B3>3000 во втором поле – 20%, а в третьем поле 16%. Конечный вид формулы для ячейки C3:

Скопируем ячейку C3, для ячеек C4:C10

Колонка Скидка содержит произведение стоимости заказа на процент скидки

Колонка К оплате содержит разность между стоимостью заказа и скидкой

Изучим математические функции. Для этого откроем файл Математические функции, а в нём лист Функции

округляет до ближайшего меньшего целого числа. В колонке B округлим значения ячеек A2:A5:

Курсор в ячейку B2, вводим с клавиатуры =целое( пока скобка открыта, щёлкнем по ячейке A2. Закроем скобку и нажмём [Enter]

Копируем ячейку B2 и вставляем в ячейки B3:B5:

Обратим внимание, что -64,3 стало -65

отбрасывает дробную часть числа, т.е. оставляет целое без округления.

Вводим для C2 — =ОТБР( нажимаем [Shift+F3]. В диалоге в первом поле введём А2, во втором поле – 0. Нажмём ОК и копию C2 поместим в C3:C5

Тоже самое проделаем, но для второго поля введём 1:

округляет число до указанного количества разрядов.
Это количество указывается во втором параметре. Проведём 6 тестов для ячейки A3:

1. число разрядов 0, результат округлён до целого – 283
2. число разрядов 1, результат округлён до десятых 283,9
3. число разрядов 2, результат округлён до сотых 283,91
4. число разрядов -1, результат округлён до десяток 280
5. число разрядов -2, результат округлён до сотен 300
6. число разрядов -3, результат округлён до тысяч 0

Чтобы после 6-го теста получить тысячу достаточно в ячейке A3 ввести число ,больше или равно 500

В колонке D округлим до десятых, в колонке E округлим до целых, в колонке F округлим до сотен:

округляют до ближайшего по модулю большего и меньшего соответственно

Вводим для G2 — =ОКРУГЛВВЕРХ(A2;0). А копию G2 поместим в G3:G5. Вводим для H2 — =ОКРУГЛВНИЗ(A2;0). А копию H2 поместим в H3:H5:

округляет число до ближайшего большего по модулю числа, кратного указанному значению. Имеет два параметра, Число которое нужно округлить и Точность, которое указывает кратность.

Введём в ячейку I2 =окрвверх(, затем нажмём [Shift+F3]. Появится диалог Аргументы функции.

щёлкнем по ячейке A2. А теперь экспериментируем с точностью: ставим 3, а внизу диалога вычислен результат этой точности 78, т.е. это число, которое будет делиться на 3 без остатка.

Теперь ставим точность 9, а результат 81.

Ставим точность 4 и нажмём ОК. Копируем ячейку I2 и вставляем в ячейки I3:I5:

округляет число до ближайшего меньшего по модулю числа, кратного указанному значению. Имеет два параметра, Число которое нужно округлить и Точность, которое указывает кратность.

Аналогично функции ОКРВВЕРХ, округлим вниз значение в ячейке A2 с точностью до 3 и 9. Сравним результаты, с результатами полученными с функцией ОКРВВЕРХ.

Копируем ячейку J2 и вставляем в ячейки J3:J5:

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

Первая функция возвращает случайные числа в диапазоне от 0 до 1. Аргументов не имеет, просто вводим функцию в каждую ячейку.

Вторая функция возвращает случайные числа в заданном диапазоне. Два параметра отвечают за нижнюю и верхнюю границу диапазона. В колонке L сгенерированы случайные числа в диапазоне от 1 до 3:

ЧЁТН – возвращает ближайшее чётное целое. Положительные числа округляются в сторону увеличения, отрицательные — наоборот

НЕЧЁТН – возвращает ближайшее нечётное целое. Положительные числа округляются в сторону увеличения, отрицательные — наоборот:

преобразует арабские числа в целые:

получает модуль числа. В колонках P2:P5 получим модуль значений ячеек A2:A5:

Курсор в ячейку P2, далее вкладка Формулы, в группе Библиотека функций, в списке Математические находим функцию ABS

В диалоге Аргументы функции введём ячейку A2, либо щёлкнем по этой ячейке в таблице. Нажимаем ОК

Чтобы понять функцию СУММЕСЛИ нужно вернуться к логической функции ЕСЛИ. В документе Математические функции активируем лист Конфеты.

Вычислим в ячейках G4, G6, G8, G10:

1. Общий объём продаж – СУММ(диапазон проданного),
2. суммарный объём продаж конфет ирис – СУММЕСЛИ(диапазон наименований; условие поиска; диапазон проданного). Где диапазон продаж это данные колонки B, условие поиска это наименование искомых конфет: нужно в латинских кавычках ввести наименование конфеты Ирис и наконец диапазон проданного вводим данные колонки С: =СУММЕСЛИ(B2:B26;»Ирис»;C2:C26)
3. объём проданного после 20 июня 2014 — СУММЕСЛИ(диапазон дат; условие поиска; диапазон проданного ). Здесь аналогично предыдущему примеру, только диапазон дат — это данные колонки D, а в качестве условия поиска выбираем в кавычках «>20.06.2014″.
4. объём проданных конфет карамель после 20 мая 2004 года — СУММЕСЛИМН(диапазон проданного; диапазон наименований; условие для наименования; диапазон дат; условие для дат). Здесь два диапазона для каждого условия и один диапазон общий, он же безусловный. Общий диапазон — первый в функции и содержит данные колонки С, диапазон наименований — это данные колонки B, условие для наименования это — Карамель, диапазон дат — это данные колонки D, и условие для дат — это «>20.05.2014″.

В документе Математические функции активируем лист СР.

Вычислим в ячейках G4, G6, G8, G10:

1. Общий объём продаж – СУММ(диапазон проданного),
2. суммарный объём продаж киви – СУММЕСЛИ(диапазон наименований; условие поиска; диапазон проданного),
3. объём проданного после 10 октября 2004 — СУММЕСЛИ(диапазон дат; условие поиска; диапазон проданного)
4. объём проданного киви после 20 октября 2004 года — СУММЕСЛИМН(диапазон проданного; диапазон наименований; условие для наименования; диапазон дат; условие для дат)

Откроем файл Текстовые функции. В листе Разбить, дан список фамилий имён и отчеств в одном столбце C. Требуется разбить по столбцам фамилию, имя и отчество.

Разбивать будем инструментом Текст по столбцам. Для этого выделяем ячейки с ФИО, без заголовка.
Затем, вкладка Данные группа Работа с данными, команда Текст по столбцам:

Появится мастер по работе с текстом. На первом его шаге, выбираем с флажок с разделителями и нажимаем Далее.

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

Переключатель Считать последовательные разделители одним, позволяет в случае если между словами несколько пробелов НЕ СОЗДАВАТЬ для второго и последующего пробелов, отдельную колонку. (Проверим на примере с несколькими пробелами и снятым переключателем)

Список Ограничитель строк, позволяет поместить в один столбец несколько разделённых слов,
но ограниченных выбранным символом. Например, если выбранный символ «, то слова «ООО Новая Заря» будут помещены в один столбец. Нажимаем Далее

На 3-м шаге мастера указываем место, куда нужно расположить разделённый текст. Курсор поместим в поле Поместить в, затем щелкнем в ячейке таблицы, где появится разделённый текст. В нашем случае это F2:

Нажимаем Готово

Перейдём на лист СЦЕПИТЬ, и воспользуемся функцией СЦЕПИТЬ для того чтобы соединить несколько текстовых строк в одну. Например, чтобы в ячейке H2, появились данные из C2, C3 и C4:

Курсор в ячейку H2, далее вкладка Функции, в группе Библиотека функций, в списке Текстовые находим функцию СЦЕПИТЬ:

В диалоге Аргументы функции ставим курсор в первое поле и щёлкнем в ячейке C2 таблицы

Теперь ставим курсор во второе поле диалога и щёлкнем в ячейке D2. Однако такой простой подход, получит фамилию и имя одной строкой т.е. вместо Иванов Иван появится ИвановИван:

Изменим нашу конструкцию так, чтобы между словами был пробел, для этого между полями, где мы вводим слово, нажимаем пробел:

Нажимаем ОК, и вставляем копию ячейки H2 для ячеек H3:H23

Перейдём на лист Инициалы, и изучим работу функции ЛЕВСИМВ. Мы можем получить вместо полного имени только первый символ. Например:

=ЛЕВСИМВ(D1;1) из слова Иван, получим И

=ЛЕВСИМВ(D1;2) — получим Ив

Теперь соединим известной нам функцией СЦЕПИТЬ, слова из ячеек C2, C3 и C4, а также получим вместо имён инициалы. Для такой операции понадобится вложенная запись из двух функций СЦЕПИТЬ и ЛЕВСИМВ:

Курсор в ячейку H2, далее вкладка Функции, в группе Библиотека функций, в списке Текстовые находим функцию СЦЕПИТЬ.

В диалоге Аргументы функции в первом поле вводим ячейку C2, для второго поля нажимаем пробел. Курсор в третье поле, раскрываем список функций в поле ИМЯ и выбираем ЛЕВСИМВ:

Если в списке нет функции ЛЕВСИМВ, то в этом списке выбираем пункт Другие функции, затем в диалоге тип Текстовый, находим функцию ЛЕВСИМВ.

В диалоге Аргументы функции, в первом поле вводим ячейку D2, а во втором число 1, но!!! не нажимаем ОК!!!, а щёлкнем по слову СЦЕПИТЬ в строке формул:

В четвёртом поле ставим точку, для пятого повторим аналогично для третьего поля, для шестого вводим точку и нажимаем ОК:

Перейдём на лист Конкатенация. Повторим соединение строк, используя знак амперсанда (&). Это символ, который соединяет две и более строк

Введём в ячейку H2 формулу =C2&D2&E2. Однако так мы получим одно слово ИвановИванИванович, тогда добавим ещё по одному знаку &, а между ними, поставим пробел в кавычках(кавычки на английской раскладке!!!):

=C2&» «&D2&» «&E2

Перейдём на лист ПРОПИСН. Нам нужно чтобы в колонке E, были слова из колонки B, но прописными буквами:

Курсор в ячейку E2, вводим функцию =ПРОПИСН(B2), нажимаем [Enter]. После скопируем ячейку E2 в ячейки E3:E22

Перейдём на лист Пропнач. Нам нужно чтобы в колонке E, были слова из колонки B, но с маленькими буквами и большая заглавная:

Курсор в ячейку E2, вводим функцию =ПРОПНАЧ(B2), нажимаем [Enter]. После скопируем ячейку E2 для ячеек E3:E22

Теперь для колонки F получим ВСЕ маленькие буквы функцией =СТРОЧН(C2)

Перейдём на лист Строчн. Нам нужно чтобы в колонке C, были слова из колонки A, но с удалёнными лишними пробелами. Для этого воспользуемся функцией которая сжимает пробелы СЖПРОБЕЛ:

Курсор в ячейку C2, вводим функцию =СЖПРОБЕЛ(A2), нажимаем [Enter]. После скопируем ячейку C2 для ячеек C3:F10.

Вычислим в колонке B, сколько символов в ячейках колонки A. Затем в колонке D вычислим, сколько символов в ячейках колонки C. В обоих случаях воспользуемся функцией ДЛСТР:

Курсор в ячейку B2, вводим =ДЛСТР(A2) и нажимаем [Enter], скопируем ячейку B2 для ячеек B3:B10. Аналогично в колонке D вычислим длину строк в колонке C

Теперь в колонке E вычислим разницу, между и B и D.

Перейдём на лист РАЗБАДР. Требуется разбить строки в колонке A по городам и улицам для колонок B и C. Известно, что город и улицы разделены запятой.

Для этого нужна функция, которая вычисляет количество символов до запятой включительно, это функция НАЙТИ. Например, запись НАЙТИ(«,»;A2), возвратит 11, а НАЙТИ(«,»;A3), возвратит 8

Т.к. для колонки B, нужно получить только первую часть строки, то воспользуемся функцией ЛЕВСИМВ:

Т.к. для колонки B, нужно получить только первую часть строки, то воспользуемся функцией ЛЕВСИМВ:

Курсор в ячейку B2, далее вкладка Функции, в группе Библиотека функций, в списке Текстовые находим функцию ЛЕВСИМВ

В диалоге Аргументы функции в первом поле вводим ячейку A2, а для второго поля будем использовать вложенную функцию НАЙТИ через поле имя:

Курсор во второе поле, раскрываем список функций в поле ИМЯ и находим функцию НАЙТИ. Если в списке нет функции НАЙТИ, то выбираем пункт Другие функции, затем в диалоге тип Текстовый, находим функцию НАЙТИ:

В диалоге Аргументы функции для функции НАЙТИ, в первом поле вводим символ , во втором ячейку A2, но!!! не нажимаем ОК!!!, а щёлкнем в строке формул по слову ЛЕВСИМВ:

Нажимаем ОК. Вид формулы для B2:

=ЛЕВСИМВ(A2;НАЙТИ(«,»;A2))

Если нужно было удалить запятую, то в функции ЛЕВСИМВ добавим значение -1:

=ЛЕВСИМВ(A2;НАЙТИ(«,»;A2)-1)

скопируем ячейку B2 для ячеек B3:B4

Теперь получим адрес без города. Для этого нужна функция, которая вычисляет часть символов справа от строки – это ПРАВСИМВ. И функция, которая вычисляет длину строки — ДЛСТР. Т.е. из общей строки, вычесть строки только с городом.

Курсор в C2 и вводим:

=ПРАВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(B2))

Мы получили адрес и символ , с пробелом. Отнимем два символа:

=ПРАВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(B2)-2)

скопируем ячейку C2 для ячеек C3:C4

Перейдём на лист ЗАМЕНА. Требуется заменить в номере телефона первые два символа 8- на другие два символа +7. Для этого нужна функция, которая заменяет указанное количество символов, на текст – это ЗАМЕНИТЬ:

ЗАМЕНИТЬ(номер телефона; номер заменяемого символ; количество заменяемых символов; заменяемые символы)

Так для нашей задачи вид формулы будет таким:

=ЗАМЕНИТЬ(B2;1;2;»+7″)

Если нужно заменить только 8 на 7, то вид формулы такой:

=ЗАМЕНИТЬ(B2;1;1;»7″)

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

Откроем файл Статистические функции, В листе Функции дан список наименований продукции, количество проданной продукции и даты продаж. Проведём некоторые вычисления с помощью статистических функций:

1. Функция МАКС возвращает максимальное числовое значение из указанного диапазона. Вычислим максимальное значение из колонки C, результат поместим в ячейку G4(запись аналогична функции СУММ)
2. Функция МИН возвращает минимальное числовое значение из указанного диапазона. Вычислим минимальное значение из колонки C, результат поместим в ячейку G6(запись аналогична функции СУММ)
3. Функция СРЗНАЧ возвращает среднее значение из указанного диапазона. Вычислим среднее значение из колонки C, результат поместим в ячейку G8(запись аналогична функции СУММ)
4. Функция СЧЁТ возвращает количество числовых(не текстовых) значений из указанного диапазона. Вычислим количество заполненных числами ячеек в колонке C, результат поместим в ячейку G10(запись аналогична функции СУММ)

5. Функция СЧЁТЕСЛИ возвращает количество значений, из указанного диапазона используя определённое условие. Функция имеет два аргумента. Вычислим, сколько было сделок с ирис, результат поместим в ячейку G12:

=СЧЁТЕСЛИ(диапазон наименований; критерий)

6. Аналогично вычислим, сколько было сделок с объёмом партии менее 250 кг, результат поместим в ячейку G14

=СЧЁТЕСЛИ(диапазон проданной продукции; критерий)

7. Функция СРЗНАЧЕСЛИ возвращает среднее значение из указанного диапазона используя определённое условие. Вычислим средний объём продаж карамель, результат поместим в ячейку G16:

=СРЗНАЧЕСЛИ(диапазон наименований; критерий; диапазон проданной продукции)

8. Функция СЧЁТЕСЛИМН возвращает количество значений из указанного диапазона используя несколько условий. Вычислим, сколько было сделок с конфетами ирис, после 20 июля 2014. Результат поместим в ячейку G18:

=СЧЁТЕСЛИМН(диапазон наименований; критерий наименования; диапазон дат; критерий даты)

В данном случае функция СЧЁТЕСЛИМН работает так: проходит по колонке с наименованиями продукции, если находит ирис, то в этой же строке, но в колонке с датами проверяет больше ли дата чем 20 июля, если да то увеличивает свой счётчик на 1. Счётчик перед работой был равен 0.

Перейдём на лист МАРКЕТ. Аналогично работе с листом Функции проведём расчёты для ячеек: H4, H6, H8, H10, H12 и H14.

Перейдём на лист СЧЁТ. Здесь дана таблица с данными продаж. Продажи были проведены не везде. Поведём статистические расчёты.

Для ячеек колонки N вычислим общую сумму продаж каждого товара за все 12 месяцев

Для ячеек колонки O вычислим все заполненные ячейки с помощью функцией СЧЁТЗ. Использовать будем только один параметр:

=СЧЁТЗ(диапазон значений за 12 месяцев)

В колонке P нужно вычислить среднее значение проданных товаров в месяцев. Здесь можно воспользоваться функцией СРЗНАЧ или данные колонки N разделить на данные колонки O

В колонке Q нужно вычислить среднее значение проданных товаров за 12 месяцев. Если точно известно что число месяцев 12, то можно воспользоваться формулой N/12, но если число месяцев неизвестно, то вычислить количество месяцев удобно с помощью функции СЧЁТЗ, которая считает не только числовые ячейки, но и строковые:

=N/СЧЁТЗ(диапазон названий месяцев)

Здесь диапазон названий месяцев смещаться не должен, иначе получим ошибку деления на 0: #ДЕЛ/0

В колонке R нужно вычислить отсутствия продаж с помощью функции СЧИТАТЬПУСТОТЫ, которая воспринимает пустую ячейку как отсутствие продаж. Если будет стоять 0, то данная функция не посчитает что продаж не было.

Здесь нужно воспользоваться программной заменой 0 на пустоту, но только если указать что в ячейке один символ “0”, иначе нули будут убраны везде: Курсор в ячейку выше данных затем вкладка Главная/Редактирование/Найти и выделить/Заменить. В диалоге Заменить в первое поле ставим 0, во второе ничего. Теперь самое главное: раскрываем в диалоге параметры и ставим галку Ячейка целиком, ОК.

Теперь воспользуемся функцией СЧИТАТЬПУСТОТЫ

Самостоятельная работа

Перейдём на лист СР1. Аналогично работе с листом Функции проведём расчёты для ячеек: G4, G6, G8, G10, G12 и G14.

Перейдём на лист СР2. Аналогично работе с листом Маркет проведём расчёты для ячеек: H4, H6, H8, H10, H12 и H14.

Перейдём на лист СР3. Аналогично работе с листом СЧЁТ проведём расчёты для колонок: H, O, P, Q, R.

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

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

Откроем файл Финансовые функции, В листе Примеры даны три задачи:

Задача 1. Мы открываем счёт на год под 8%. Первый раз вносим 5000 руб. , затем каждый месяц докладываем по 1000 руб. Сколько мы получим в конце периода

Задача 2. Мы берём такой то кредит, под такое то количество процентов годовых. Сколько нужно платить каждый месяц

Задача 3. Мы берём такой то кредит, на такое то количество времени, можем ежемесячно платить такую то сумму. За сколько времени мы выплатим весь кредит

Функции, которыми будем пользоваться:

• БС – баланс на конец периода
• ПЛТ – платёж
• КПЕР – количество периодов

Эти функции взаимосвязаны, например, функция БС будет иметь в качестве параметров функции ПЛТ и КПЕР.

Решение задачи 1:

Курсор в A9, далее вкладка Формулы/Библиотека функций/Финансовые. В списке выбираем функцию БС

• Ставка – ставим процент из ячейки A7, делённый на 12
• КПЕР – количество периодов 12, сколько будем платить – B7
• ПЛТ – размер ежемесячного платежа – C7
• ПС – приведённый вклад, т.е. то, что внесли первоначально – D7

Тип платежа 0 или 1: в конце или в начале периода, для вклада это не важно 0 или 1. Это будет важно, когда мы будем отдавать кредит, если мы будем отдавать в тот день, в который взяли кредит – это начало периода, если мы отдадим в конце месяца – это конец периода. Нажимаем ОК.

Решение задачи 2:

Нужно рассчитать размеры одинаковых платежей, т.е. аннуитетный платёж

Курсор в A20, далее вводим =ПЛТ( и нажимаем [Shift+F3]. В диалоге те же самые аргументы:

• Ставка – процент из A18, делим на 12.
• КПЕР – количество периодов 12, сколько будем платить – B18
• Пс – приведённый вклад, т.е. сколько мы взяли в долг – C18
• Бс – баланс на конец периода 0
• Тип – теперь тип важно указать. Если мы получили деньги и в этот же день вносим платёж, то ставим 1, если вносим в конце месяца, то — 0. Ставим 0 и нажимаем ОК

Решение задачи 3:

Взяли кредит на 15000 под 18% годовых. Отдавать можем только по 2000 в месяц. Нужно узнать через сколько месяцев мы отдадим кредит.

Курсор в A31, далее вводим =КПЕР( и нажимаем [Shift+F3]. В диалоге те же самые аргументы:

• Ставка – процент из A29, делим на 12
• ПЛТ – размер ежемесячного платежа – B29
• ПС – сколько взяли – C29
• Бс – баланс в конце периода, должны всё отдать, значит 0
• Тип – 0

Нажимаем ОК

Если вместо 15000 ввести например, 1 000 000, то такую сумму банк не даст.

Минусы и плюсы: направление потока денег. ОТ нас – отрицательный поток, К нам – положительный поток

Примеры для закрепления:

Перейдём на лист Кредит. Здесь даны таблицы с некоторыми кредитами.

Мы берём кредит на покупку автомобиля. Функции для расчёта выплат те же что и ранее только срок кредита не 1 год, а 3.

Курсор в B5, далее вводим =ПЛТ( и нажимаем [Shift+F3]. В диалоге вводим аргументы:

• Ставка – процент из B3, делим на 12
• КПЕР – количество периодов B4*12, сколько будем платить
• Пс – приведённая стоимость, т.е. сумма нашего долга – B2
• Бс – баланс на конец периода 0
• Тип – ставим 0 и нажимаем ОК. Получаем ежемесячные выплаты

Теперь эта же задача, только если бы мы не платили проценты:

Для ячейки B6 вводим отношение кредита к периоду: B2/(B4*12)

Вычислим переплату: (B5+B6)*B4*12

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

Перейдём на лист Анализ кредитов. Здесь нужно вычислить оптимальный процент из 8-и предложенных для кредита 1000000, ежемесячная выплата за которого не должна превысить 33000

Здесь функцию ПЛТ соединим со смешанными ссылками:

Выделяем диапазон C4:J8 (от C4), вводим с клавиатуры =ПЛТ( и нажимаем [Shift+F3].

• В поле Ставка процент из C3, но т.к. при копировании вправо смещаться должен столбец, мы фиксируем строку и делим на 12
• КПЕР – количество периодов это B4, но т.к. при копировании смещаться должна строка, мы фиксируем столбец и умножаем на 12
• Пс – размер кредита всегда – D10 т.е. фиксируем столбец и строку
• Бс – баланс на конец периода 0
• Тип – ставим 0

Если нажмём ОК, то формула будет вычислена только для активной ячейки. Нажимаем [Ctrl+Enter].

Функции даты и времени

Функция ДЕНЬ

Откроем файл Функции ДАТА и ВРЕМЯ, лист День Месяц Год.

Нужно колонку Дата рождения, разбить на колонки День, Месяц и Год. Функция ДЕНЬ, вытаскивает из даты день, МЕСЯЦ – вытаскивает из даты месяц и функция ГОД, вытаскивает из даты год:

1. В ячейку E2 вводим =ДЕНЬ(D2), затем E2 копируем для своего столбца.
2. В ячейку F2 вводим =ДЕНЬ(D2), затем F2 копируем для своего столбца.
3. В ячейку G2 вводим =ДЕНЬ(D2), затем G2 копируем для своего столбца.

Колонка H содержит обратную задачу, т.е. мы должны собрать из колонок E, F и G дату с помощью функции ДАТА:

В ячейку E2 вводим =ДАТА(, затем [Shift+F3] и в поля вводим названия ячеек(обратим внимание что первое поле содержит год). теперь H2 копируем для своего столбца.

Функция ДЕНЬНЕД

Перейдём на лист ДЕНЬНЕД

Функция ДЕНЬНЕД выдаёт порядковый номер дня недели в виде числа: введём в A2 сегодняшнюю дату, а в B2 — =ДЕНЬНЕД(, затем [Shift+F3]. В первое поле вводим A2, во второе тип, который определяет отсчёт недели. Тип может быт 1, 2 или 3:

• если тип 1, то с Вс=1 до Сб=7
• если тип 2, то с Пн=1 до Вс=7
• если тип 3, то с Пн=0 до Вс=6

Нам нужен тип 2. Нажимаем ОК.

Теперь нам нужен не номер, а название недели. Для этого в ячейке C2, просто ссылаемся на ячейку
с датой: =A2, выходим из режима редактирования и для C2 вызываем диалог формат ячеек:
вкладка Главная/Число/Формат ячеек

В диалоге Формат ячеек в списке форматов выбираем (все форматы) в поле Тип, можем ввести:

• Д — получаем номер дня из одной цифры
• ДД — получаем номер дня из двух цифр
• ДДД — получаем название дня недели в сокращённом виде
• ДДДД — получаем название дня недели в полном виде
• М — получаем номер месяца из одной цифры
• ММ — получаем номер месяца из двух цифр
• МММ — получаем название месяца в сокращённом виде
• ММММ — получаем название месяца в полном виде
• Г — получаем год в сокращённом виде
• ГГ — получаем год в сокращённом виде
• ГГГ — получаем год в полном виде
• ГГГГ — получаем год в полном виде

Введём собственный шаблон даты например, для 8 фев. 14г. Нужно ввести в поле Тип Д МММ. ГГ”г.”. Проверим результат в ячейке C2.

При желании можно перед Д ввести “Сегодня — ”

Функция ЧИСТРАБДНИ

Перейдём на лист ЧИСТРАБДНИ.

У нас есть две даты 22.12.2011 и 30.12.2011, нужно посчитать количество дней между ними.

1 вариант: из конечной даты вычесть начальную дату =A4-A3, но мы получим 8, т.к. это просто разность между 30 и 22.

2 вариант: Если нужно между этими датами вычесть выходные, то воспользуемся функцией ЧИСТРАБДНИ:

Введём в C3, =ЧИСТРАБДНИ( и нажмём [Shift+F3], в первом поле вводим A3 во втором A4, и ОК мы получили количество только рабочих дней без субботы и воскресенья -7

Функция СЕГОДНЯ

Изучим, как можно вычислить сколько нам полных лет или какой у нас стаж работы:

Введём в ячейку E2 свою дату рождения, а в F2 с помощью функции СЕГОДНЯ текущую дату без параметра.

Функция ГОД и ДОЛЯГОДА

Посчитаем сколько нам сейчас лет:

1 вариант: формула =ГОД(F2)-ГОД(E2) правильна, только если сегодняшний день и месяц больше дня и месяца даты рождения,

2 вариант формула =F2-E2 возвратит количество дней

3 вариант: применяем функцию ДОЛЯГОДА, в первом её параметре вводим дату рождения, во втором сегодняшнюю дату и нажимаем ОК. Мы можем получить дробное число, если дни и месяцы не совпадают.

Функция РАЗНДАТ

И наконец, недокументированная функция РАЗНДАТ. В мастере её нет, поэтому аргументы вводим вручную, т.е. [Shift+F3] не сработает. Синтаксис функции РАЗНДАТ:

=РАЗНДАТ(нач дата; конечн дата; критерий работы функции)

Критерий работы функции: в кавычках символы на английском

• “d” – дни,
• “m” – месяцы
• “y” – годы,
• “ym” месяцы без учёта лет
• “md” дни без учёта месяцев

=РАЗНДАТ(E2;D2;”d”)

=РАЗНДАТ(E2;D2;”m”)

=РАЗНДАТ(E2;D2;”y”)

=РАЗНДАТ(E2;D2;”ym”)

=РАЗНДАТ(E2;D2;”md”)

Функции ссылок и подстановки

Откроем файл Функции ссылки и массивы, лист Заказы.

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

Курсор в D3, далее слева от строки формул нажимаем кнопку Вставить функцию, категория Ссылки и массивы, находим функцию ВПР и нажимаем ОК. В диалоге будут четыре аргумента:

• Искомое значение — в первом поле вводим ячейку B3,
• Таблица — это ссылка на таблицу с ценами, т.е. Прайс лист. Вводим G3:H19. Эту формулу будем копировать, но таблица не должна смещаться, значит формулу нужно фиксировать.
• Номер столбца – номер столбца таблицы, откуда нужно брать цену, указываем 2.
• Интервальный просмотр – нужно указать одно из четырёх значений: ИСТИНА, 1, ЛОЖЬ и 0. Если нужно искать точное совпадение например Яблок Яблоки, то вводим 0.

Конечная формула:

=ВПР(B3;G$3:H$19;2;0)

Самостоятельная работа

Выполним вычисления в электронной таблице используя функции: ЕСЛИ, СУММ, СУММЕСЛИ, СЧЁТЕСЛИ, ЧАС, ГПР, СЧИТАТЬПУСТОТЫ, МАКС, МИН, СРЗНАЧ и РАНГ.

Логическая функция ЕСЛИ

Рассмотрим таблицу финансовых операций в клубе проката роликовых коньков:

Колонка Бронь содержит информацию о брони. Колонка Доплата контролирует данные в колонке Бронь и выводит значение 100, если посетитель забронировал себе коньки, и 0 в противном случае. Этот контроль реализует функция ЕСЛИ, которая имеет следующий синтаксис:

ЕСЛИ(A;B;C) где:

A – логическое выражение, например 5>2, C2>7

B – истина, если логическое выражение истинно

C – ложь, если логическое выражение ложно

Значит, логическое выражение будет проверять установлен ли ”+” в ячейках колонки Бронь, если установлен, то функция выводит значение истины, иначе функция выводит ложь. Псевдокод нашей функции выглядит так:

ЕСЛИ(в ячейках колонки Бронь есть “+”; выведем 100; ведем 0):

Реализуем условие доплаты на практике:

1. Курсор в ячейку D6, вызываем команду Вставить функцию:

2. В окне Мастер функций, выбираем в списке Категория Логические, и функцию ЕСЛИ, нажимаем ОК:

3. Установим аргументы функции и нажимаем ОК:

4. За маркер автозаполнения копируем формулу вдоль колонки Доплата:

Слева результат работы функции ЕСЛИ, для колонки Доплата, справа действия маркера автозаполнения над формулой колонки Доплата:

Функция времени ЧАС

Аналогично с помощью функции ЕСЛИ, вычислим скидку. Скидка составляет 150 рублей и предоставляется, если посетитель катался 3 и более часа. Время катания у нас указано в колонках F и E, количество часов вычисляется разностью этих колонок, только это количество должно быть в числовом, а не временном формате. Функция ЧАС(ссылка), переводит параметр ссылка из формата время в числовой.

Например, количество проведённых часов первого в списке посетителя, можно вычислить по формуле ЧАС(F9)-ЧАС(E9), а условие для скидки будет выглядеть так:

(ЧАС(F9)-ЧАС(E9)>=3

Курсор в ячейку H6. Пишем функцию выводящую скидку:

=ЕСЛИ((ЧАС(F6)-ЧАС(E6))>=3;150;0)

Копируем ячейку H6:

выделяем диапазон H7:H10:

Вставляем:

Функция ГПР

Функция ГПР ищет значение внешней таблицы и возвращает как результат значение ячейки в указанной строке того же столбца. Функция ГПР имеет следующий синтаксис:

ГПР(искомое_значене;таблица;номер_строки;[интервальный_просмотр]), где

искомое_значение – это ячейка основной таблицы, а её значение должно совпасть со значениями 1-й строкой внешней таблицы

таблица – внешняя таблица, где производится поиск данных. 1-я строка должна быть отсортирована по возрастанию

номер_строки – номер строки внешней таблицы из которой берётся результат

интервальный_просмотр – логическое значение ИСТИНА или ЛОЖЬ

Нам нужно найти в 1-й строке внешней таблицы совпадение, которое есть в ячейках основной таблицы, затем выбрать из внешней таблицы значение 2-й строки:

Пример: Выведем в ячейку I6, значение 2-й внешней таблицы, а имя столбца этой таблицы указано в ячейке G6, т.е. в I6 должно быть 300:

1. Курсор в ячейку I6, [Shift+F3] — вызываем команду Вставить функцию
2. В окне Мастер функций, выбираем в списке Категория Ссылки и массивы, функцию ГПР и нажимаем ОК:

3. Установим аргументы функции и нажимаем ОК:

Формула в ячейке I6 имеет такую запись:

Копируем ячейку I6 и вставляем в диапазон I7:I10, но при этом не забываем, что перед копированием, ссылка в формуле на внешнюю таблицу, должна быть постоянной т.е. абсолютной G$1:I$1:

Клавишей [F2], проверим формулы в ячейках колонки I:

Сумма вычисляется по формуле Время катания ? Стоимость часа — Скидка + Доплата

сумма для Андреева =(ЧАС(F6)-ЧАС(E6))*I6-H6+D6

Итоговый вид таблицы:

Функция СУММЕСЛИ

Если функция СУММ(J6:J10), выведет общую сумму диапазона J6:J10 ячеек, то функция СУММЕСЛИ, суммирует только определённые ячейки. Рассмотрим несколько примеров:

СУММЕСЛИ(J6:J10;”>500”)

просуммирует ячейки J6, J8 и J9

СУММЕСЛИ(G6:G10;”Фристайл”;J6:J10)

суммирует только те значения из диапазона J6:J10, для которых соответствующие значения из диапазона G6:G10 равны Фристайл, т.е просуммируются все фристайлеры:

Просуммируем теперь всех, кто катался на роликах типа Фитнес и Агрессив:

СУММЕСЛИ(G6:G10;”Фитнес”;J6:J10)

СУММЕСЛИ(G6:G10;”Агресив”;J6:J10)

Функция СЧЁТЕСЛИ

Возвращает количество ячеек в диапазоне которые соответствуют в заданному условию. Примеры:

СЧЁТЕСЛИ(J6:J10;»>400″)

выведет количество посетителей заплативших более 400 рублей, в нашем случае это 3

СЧЁТЕСЛИ(G6:G10;»Фристайл»)

посчитает количество фристайлеров посетивших клуб:

Функции СЧИТАТЬПУСТОТЫ, МАКС, МИН, СРЗНАЧ, РАНГ

Дана таблица посещений бассейна учениками 3-х классов в неделю:

В ячейках B9, C9, D9 с помощью функции СУММ, выведем итоговое количество посещений учеников за неделю по классам:

Функция СЧИТАТЬПУСТОТЫ(диапазон) возвращает количество пустых ячеек в диапазоне. В ячейках B10, C10, D10 с помощью функции СЧИТАТЬПУСТОТЫ, выведем количество пропущенных дней по классам:

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

Функция РАНГ вычисляет ранг или рейтинг числа в списке чисел. Она имеет следующий синтаксис

РАНГ(число; ссылка; [порядок]), где

число – значение, которому нужно установить рейтинг

ссылка – список чисел для установки рейтинга

[порядок] – определяет сортировку рейтинга, 0 по возрастанию и 1 по убыванию

Пример, ячейки B9, C9, D9 содержат список количества учеников посетивших бассейн в неделю, указав любую из этих ячеек, например B9, функция РАНГ выведет порядковый номер этой ячейки из 3-х возможных. Вычислим рейтинг 9-А класса из 3-х классов:

1. Курсор в ячейку B15, вызываем команду Вставить функцию

2. В окне Мастер функций, выбираем в списке Категория Статистические, и функцию Ранг.РВ, нажимаем ОК

3. Установим аргументы функции и нажимаем ОК:

Копируем ячейку B15 и вставляем в B16:B17 не забываем перед копированием, изменить относительную ссылку на абсолютную вдоль строки $B9:$D9

Результат работы наших вычислений