Использование встроенных функций Excel

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

В Excel имеется несколько функций для построения линейной регрессии, в частности:

· НАКЛОН и ОТРЕЗОК.

А также несколько функций для построения экспоненциальной линии тренда, в частности:

Следует отметить, что приемы построения регрессий с помощью функций ТЕНДЕНЦИЯ и РОСТ практически совпадают. То же самое можно сказать и о паре функций ЛИНЕЙН и ЛГРФПРИБЛ. Для четырех этих функций при создании таблицы значений используются такие возможности Excel, как формулы массивов, что несколько загромождает процесс построения регрессий. Заметим также, что построение линейной регрессии, на наш взгляд, легче всего осуществить с помощью функций НАКЛОН и ОТРЕЗОК, где первая из них определяет угловой коэффициент линейной регрессии, а вторая — отрезок, отсекаемый регрессией на оси ординат.

Достоинствами инструмента встроенных функций для регрессионного анализа являются:

· достаточно простой однотипный процесс формирования рядов данных исследуемой характеристики для всех встроенных статистических функций, задающих линии тренда;

· стандартная методика построения линий тренда на основе сформированных рядов данных;

· возможность прогнозирования поведения исследуемого процесса на необходимое количество шагов вперед или назад.

А к недостаткам относится то, что в Excel нет встроенных функций для создания других (кроме линейного и экспоненциального) типов линий тренда. Это обстоятельство часто не позволяет подобрать достаточно точную модель исследуемого процесса, а также получить близкие к реальности прогнозы. Кроме того, при использовании функций ТЕНДЕНЦИЯ и РОСТ не известны уравнения линий тренда.

Следует отметить, что авторы не ставили целью статьи изложение курса регрессионного анализа с той или иной степенью полноты. Основная ее задача — на конкретных примерах показать возможности пакета Excel при решении задач аппроксимации; продемонстрировать, какими эффективными инструментами для построения регрессий и прогнозирования обладает Excel; проиллюстрировать, как относительно легко такие задачи могут быть решены даже пользователем, не владеющим глубокими знаниями регрессионного анализа.

Примеры решения конкретных задач

Рассмотрим решение конкретных задач с помощью перечисленных инструментов пакета Excel.

Задача 1

С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг. необходимо выполнить следующие действия.

1. Построить диаграмму.

2. В диаграмму добавить линейную и полиномиальную (квадратичную и кубическую) линии тренда.

3. Вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них.

4. Используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 1995-2004 г.г.

5. Составить прогноз по прибыли предприятия на 2003 и 2004 гг.

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

1. В диапазон ячеек A4:C11 рабочего листа Excel вводим рабочую таблицу, представленную на рис. 4.

2. Выделив диапазон ячеек В4:С11, строим диаграмму.

3. Активизируем построенную диаграмму и по описанной выше методике после выбора типа линии тренда в диалоговом окне Линия тренда (см. рис. 1) поочередно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры (см. рис. 2), в поле Название аппроксимирующей (сглаженной) кривой вводим наименование добавляемого тренда, а в поле Прогноз вперед на: периодов задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R2 включаем флажки показывать уравнение на экране и поместить на диаграмму величину достоверности аппроксимации (R^2). Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда (см. рис. 3). Полученная диаграмма с добавленными линиями тренда представлена на рис. 5.

4. Для получения табличных данных по прибыли предприятия для каждой линии тренда за 1995-2004 гг. воспользуемся уравнениями линий тренда, представленными на рис. 5. Для этого в ячейки диапазона D3:F3 вводим текстовую информацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд. Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5:D13. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве аргумента стоит соответствующая ячейка из диапазона A4:A13. Аналогично для квадратичной регрессии заполняется диапазон ячеек E4:E13, а для кубической регрессии — диапазон ячеек F4:F13. Таким образом, составлен прогноз по прибыли предприятия на 2003 и 2004 гг. с помощью трех трендов. Полученная таблица значений представлена на рис. 6.

Задача 2

С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг., приведенной в задаче 1, необходимо выполнить следующие действия.

1. Построить диаграмму.

2. В диаграмму добавить логарифмическую, степенную и экспоненциальную линии тренда.

3. Вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них.

4. Используя уравнения линий тренда, получить табличные данные о прибыли предприятия для каждой линии тренда за 1995-2002 гг.

5. Составить прогноз о прибыли предприятия на 2003 и 2004 гг., используя эти линии тренда.

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

Следуя методике, приведенной при решении задачи 1, получаем диаграмму с добавленными в нее логарифмической, степенной и экспоненциальной линиями тренда (рис. 7). Далее, используя полученные уравнения линий тренда, заполняем таблицу значений по прибыли предприятия, включая прогнозируемые значения на 2003 и 2004 гг. (рис. 8).

На рис. 5 и рис. видно, что модели с логарифмическим трендом, соответствует наименьшее значение достоверности аппроксимации

R2 = 0,8659

Наибольшие же значения R2 соответствуют моделям с полиномиальным трендом: квадратичным (R2 = 0,9263) и кубическим (R2 = 0,933).

Задача 3

С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг., приведенной в задаче 1, необходимо выполнить следующие действия.

1. Получить ряды данных для линейной и экспоненциальной линии тренда с использованием функций ТЕНДЕНЦИЯ и РОСТ.

2. Используя функции ТЕНДЕНЦИЯ и РОСТ, составить прогноз о прибыли предприятия на 2003 и 2004 гг.

3. Для исходных данных и полученных рядов данных построить диаграмму.

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

Воспользуемся рабочей таблицей задачи 1 (см. рис. 4). Начнем с функции ТЕНДЕНЦИЯ:

1. выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия;

2. вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке ОК. Эту же операцию можно осуществить нажатием кнопки (Вставка функции) стандартной панели инструментов.

3. В появившемся диалоговом окне Аргументы функции вводим в поле Известные_значения_y диапазон ячеек C4:C11; в поле Известные_значения_х — диапазон ячеек B4:B11;

4. чтобы вводимая формула стала формулой массива, используем комбинацию клавиш + + .

Введенная нами формула в строке формул будет иметь вид: =<ТЕНДЕНЦИЯ(C4:C11;B4:B11)>.

В результате диапазон ячеек D4:D11 заполняется соответствующими значениями функции ТЕНДЕНЦИЯ (рис. 9).

Для составления прогноза о прибыли предприятия на 2003 и 2004 гг. необходимо:

1. выделить диапазон ячеек D12:D13, куда будут заноситься значения, прогнозируемые функцией ТЕНДЕНЦИЯ.

2. вызвать функцию ТЕНДЕНЦИЯ и в появившемся диалоговом окне Аргументы функции ввести в поле Известные_значения_y — диапазон ячеек C4:C11; в поле Известные_значения_х — диапазон ячеек B4:B11; а в поле Новые_значения_х — диапазон ячеек B12:B13.

3. превратить эту формулу в формулу массива, используя комбинацию клавиш Ctrl + Shift + Enter.

4. Введенная формула будет иметь вид: =<ТЕНДЕНЦИЯ(C4:C11;B4:B11;B12:B13 )>, а диапазон ячеек D12:D13 заполнится прогнозируемыми значениями функции ТЕНДЕНЦИЯ (см. рис. 9).

Аналогично заполняется ряд данных с помощью функции РОСТ, которая используется при анализе нелинейных зависимостей и работает точно так же, как ее линейный аналог ТЕНДЕНЦИЯ.

На рис.10 представлена таблица в режиме показа формул.

Для исходных данных и полученных рядов данных построена диаграмма, изображенная на рис. 11.

Задача 4

С таблицей данных о поступлении в диспетчерскую службу автотранспортного предприятия заявок на услуги за период с 1 по 11 число текущего месяца необходимо выполнить следующие действия.

1. Получить ряды данных для линейной регрессии: используя функции НАКЛОН и ОТРЕЗОК; используя функцию ЛИНЕЙН.

2. Получить ряд данных для экспоненциальной регрессии с использованием функции ЛГРФПРИБЛ.

3. Используя вышеназванные функции, составить прогноз о поступлении заявок в диспетчерскую службу на период с 12 по 14 число текущего месяца.

4. Для исходных и полученных рядов данных построить диаграмму.

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

Отметим, что, в отличие от функций ТЕНДЕНЦИЯ и РОСТ, ни одна из перечисленных выше функций (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ) не является регрессией. Эти функции играют лишь вспомогательную роль, определяя необходимые параметры регрессии.

Для линейной и экспоненциальной регрессий, построенных с помощью функций НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ, внешний вид их уравнений всегда известен, в отличие от линейной и экспоненциальной регрессий, соответствующих функциям ТЕНДЕНЦИЯ и РОСТ.

1. Построим линейную регрессию, имеющую уравнение:

y = mx+b

с помощью функций НАКЛОН и ОТРЕЗОК, причем угловой коэффициент регрессии m определяется функцией НАКЛОН, а свободный член b — функцией ОТРЕЗОК.

Для этого осуществляем следующие действия:

1. заносим исходную таблицу в диапазон ячеек A4:B14;

2. значение параметра m будет определяться в ячейке С19. Выбираем из категории Статистические функцию Наклон; заносим диапазон ячеек B4:B14 в поле известные_значения_y и диапазон ячеек А4:А14 в поле известные_значения_х. В ячейку С19 будет введена формула: =НАКЛОН(B4:B14;A4:A14);

3. по аналогичной методике определяется значение параметра b в ячейке D19. И ее содержимое будет иметь вид: =ОТРЕЗОК(B4:B14;A4:A14). Таким образом, необходимые для построения линейной регрессии значения параметров m и b будут сохраняться соответственно в ячейках C19, D19;

4. далее заносим в ячейку С4 формулу линейной регрессии в виде: =$C*A4+$D. В этой формуле ячейки С19 и D19 записаны с абсолютными ссылками (адрес ячейки не должен меняться при возможном копировании). Знак абсолютной ссылки $ можно набить либо с клавиатуры, либо с помощью клавиши F4, предварительно установив курсор на адресе ячейки. Воспользовавшись маркером заполнения, копируем эту формулу в диапазон ячеек С4:С17. Получаем искомый ряд данных (рис. 12). В связи с тем, что количество заявок — целое число, следует установить на вкладке Число окна Формат ячеек числовой формат с числом десятичных знаков 0.

2. Теперь построим линейную регрессию, заданную уравнением:

y = mx+b

с помощью функции ЛИНЕЙН.

1. вводим в диапазон ячеек C20:D20 функцию ЛИНЕЙН как формулу массива: =<ЛИНЕЙН(B4:B14;A4:A14)>. В результате получаем в ячейке C20 значение параметра m, а в ячейке D20 — значение параметра b;

2. вводим в ячейку D4 формулу: =$C*A4+$D;

3. копируем эту формулу с помощью маркера заполнения в диапазон ячеек D4:D17 и получаем искомый ряд данных.

3. Строим экспоненциальную регрессию, имеющую уравнение:

y = bmx

с помощью функции ЛГРФПРИБЛ оно выполняется аналогично:

1. в диапазон ячеек C21:D21 вводим функцию ЛГРФПРИБЛ как формулу массива: =< ЛГРФПРИБЛ (B4:B14;A4:A14)>. При этом в ячейке C21 будет определено значение параметра m, а в ячейке D21 — значение параметра b;

2. в ячейку E4 вводится формула: =$D*$C^A4;

3. с помощью маркера заполнения эта формула копируется в диапазон ячеек E4:E17, где и расположится ряд данных для экспоненциальной регрессии (см. рис. 12).

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

Величина R 2 называется коэффициентом детерминации.

Задачей построения регрессионной зависимости является нахождение вектора коэффициентов m модели (1) при котором коэффициент R принимает максимальное значение.

Для оценки значимости R применяется F-критерий Фишера, вычисляемый по формуле

где n — размер выборки (количество экспериментов);

k — число коэффициентов модели.

Если F превышает некоторое критическое значение для данных n и k и принятой доверительной вероятности, то величина R считается существенной. Таблицы критических значений F приводятся в справочниках по математической статистике.

Таким образом, значимость R определяется не только его величиной, но и соотношением между количеством экспериментов и количеством коэффициентов (параметров) модели. Действительно, корреляционное отношение для n=2 для простой линейной модели равно 1 (через 2 точки на плоскости можно всегда провести единственную прямую). Однако если экспериментальные данные являются случайными величинами, доверять такому значению R следует с большой осторожностью. Обычно для получения значимого R и достоверной регрессии стремятся к тому, чтобы количество экспериментов существенно превышало количество коэффициентов модели (n>k).

Для построения линейной регрессионной модели необходимо:

1) подготовить список из n строк и m столбцов, содержащий экспериментальные данные (столбец, содержащий выходную величину Y должен быть либо первым, либо последним в списке); для примера возьмем данные предыдущего задания, добавив столбец с названием "№ периода", пронумеруем номера периодов от 1 до 12. (это будут значения Х)

2) обратиться к меню Данные/Анализ данных/Регрессия

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

3) в диалоговом окне "Регрессия" задать:

· входной интервал Y;

· входной интервал X;

· выходной интервал — верхняя левая ячейка интервала, в который будут помещаться результаты вычислений (рекомендуется разместить на новом рабочем листе);

Поиск в Excel по нескольким критериям

Функция ВПР в Excel – это действительно мощный инструмент для выполнения поиска определённого значения в базе данных. Однако, есть существенное ограничение – её синтаксис позволяет искать только одно значение. Как же быть, если требуется выполнить поиск по нескольким условиям? Решение Вы найдёте далее.

Пример 1: Поиск по 2-м разным критериям

Предположим, у нас есть список заказов и мы хотим найти Количество товара (Qty.), основываясь на двух критериях – Имя клиента (Customer) и Название продукта (Product). Дело усложняется тем, что каждый из покупателей заказывал несколько видов товаров, как это видно из таблицы ниже:

Руководство по функции ВПР в Excel

Обычная функция ВПР не будет работать по такому сценарию, поскольку она возвратит первое найденное значение, соответствующее заданному искомому значению. Например, если Вы хотите узнать количество товара Sweets, заказанное покупателем Jeremy Hill, запишите вот такую формулу:

Есть простой обходной путь – создать дополнительный столбец, в котором объединить все нужные критерии. В нашем примере это столбцы Имя клиента (Customer) и Название продукта (Product). Не забывайте, что объединенный столбец должен быть всегда крайним левым в диапазоне поиска, поскольку именно левый столбец функция ВПР просматривает при поиске значения.

Читайте также:  Калькулятор величин и конвертер единиц измерений ПРЕОБР в Excel

Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2. Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&» «&C2. После этого можно использовать следующую формулу:

=VLOOKUP(«Jeremy Hill Sweets»,$A$7:$D$18,4,FALSE) =ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)

Руководство по функции ВПР в Excel

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

Если Вам необходимо обновить основную таблицу (Main table), добавив данные из второй таблицы (Lookup table), которая находится на другом листе или в другой рабочей книге Excel, то Вы можете собрать искомое значение непосредственно в формуле, которую вставляете в основную таблицу.

Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.

Итак, формула с ВПР может быть такой:

Чтобы сделать формулу более читаемой, Вы можете задать имя для просматриваемого диапазона, и тогда формула станет выглядеть гораздо проще:

Руководство по функции ВПР в Excel

Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&» «&C2).

Соглашусь, добавление вспомогательного столбца – не самое изящное и не всегда приемлемое решение. Вы можете сделать то же самое без вспомогательного столбца, но в таком случае потребуется гораздо более сложная формула с комбинацией функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ).

Как применить МАКС, ВПР и ПОИСКПОЗ для решения задач

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

Рассмотрим комбинацию этих функций на примере решения задачи в Excel для экономистов. Составим комбинированную формулу, чтобы найти компанию с наибольшей суммой задолженности.

Для начала составим список должников, укажем сумму их задолженности и выставим дату окончания кредитного договора.

МАКС, ВПР и ПОИСКПОЗ для решения задач

Для решения задачи, можно применить функции последовательно:

  • Найти самый крупный долг поможет функция МАКС (=МАКС(B2:B10)), где B2:B10 — столбец с данными по задолженности.
  • Чтобы найти номер компании-должника в списке, нужно в таблицу добавить столбец с нумерацией. Так как функция ПОИСКПОЗ ищет данные только в крайнем левом столбце выделенного диапазона.

Составляем функцию по формуле:
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

В нашем случае это будет =ПОИСКПОЗ(14569;C2:C10;0), где искомое — максимальная сумма долга. Тип сопоставления будет “0”, потому что к столбцу с долгами мы не применяли сортировку.

  • Чтобы узнать название компании-должника, применим знакомую функцию ВПР.

Выглядеть она будет так =ВПР(D14;A2:B10;2), где D4 — искомое, A2:B10 — таблица или выделенный диапазон с названиями компаний и нумерацией, а “2” — номер столбца с должниками.

решение финансовых задач в excel примеры

Этот же результат можно было получить, собрав одну формулу из 3-х:

=ВПР (ПОИСКПОЗ (МАКС (C2:C10); C2:C10;0); A2:B10;2).

В экономических расчетах функция ВПР помогает быстро извлечь нужное значение из огромного диапазона данных. Причем значение можно найти по разным критериям отбора. Например, цену товара можно извлечь по идентификатору, налоговую ставку — по уровню дохода и пр.
Кроме вышеупомянутых функций, экономисты часто используют формулу СРЗНАЧ, например, для расчета средней заработной платы. Функцию СЧЁТ, когда нужно рассчитать количество отгрузок в разрезе клиентов или стоимости товара за определенный период. Кстати, на примере отгрузок, формула МИН/МАКС поможет отследить диапазон, в котором изменялась стоимость товара.

Существует еще сотня математических и финансовых функций в Ехсеl, с помощью которых можно решить экономические задачи разной сложности. Например, обрабатывать заказы, планировать загрузку производства, рассчитывать налоги, вести кадровый учет и управлять сбытом. Но для решения подобных задач, подсказок в Excel будет уже недостаточно. Тут необходимо глубже погружаться в программу и учиться работать с разными ее инструментами: от функций и сводных таблиц, до диаграмм и макросов.

Пример преимущества функции ПОИСК в Excel перед функцией НАЙТИ

  • Выделить диапазон: столбец или ячейки с числами, которые нужно сложить.
  • Выбрать Вставка → Функции → СУММ или ∑ → СУММ.
  • Выбрать первую пустую ячейку в конце диапазона.
  • Выбрать ∑ → СУММ.
  • Вписать в свободную ячейку значения нужных диапазонов.

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать ∑ → СРЗНАЧ.
  • Вписать в свободную ячейку значения диапазонов.

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать подходящую функцию из списка: ∑ → МИН.
  • Вписать в свободную ячейку значения нужных диапазонов.

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать функцию ∑ → МАКС.
  • Вписать в свободную ячейку значения нужных диапазонов.

  • Выбрать первую свободную ячейку в конце диапазона.
  • Выбрать функцию ∑ → СЧЕТ.
  • Вписать в свободную ячейку значения нужных диапазонов.

Excel + Google-таблицы
с 0 до PRO

Подробнее

Функция ЕСЛИ

Является одной из самых полезных, имеющихся в Excel, функций. Она проверяет результат переданного ей логического выражения и возвращает результаты в зависимости от того истинно он или ложно.

Синтаксис:

Примеры использования функции:

Рассмотрим первый простой пример, чтобы понять, как функция работает.

Умышлено в первый аргумент функции вставить функцию ИСТИНА. В результате проверки, будет возвращен 2 аргумент (значение_если_истина), 3 аргумент будет опущен.

пример использования функции ЕСЛИ

Теперь приведем пример использования вложенности одной функции ЕСЛИ в другую. Такой подход может понадобиться, когда при выполнении (или невыполнении) одного условия требуется дополнительная проверка.

Имеются банковские карточки с номерами, начинающимися с первых четырех цифр, которые являются идентификатором вида карты:

  • 1111 – Visa;
  • 2222 – Master Card.

Используем нашу функцию для определения типа карты.

вложение функции ЕСЛИ

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

=ЕСЛИ( ЛЕВСИМВ(A2;4)=»1111″ ; «Visa» ;ЕСЛИ( ЛЕВСИМВ(A2;4)=»2222″ ; «Master Card» ;»карта не определена»))

Помимо самой рассматриваем функции, в примере используется текстовая функция ЛЕВСИМВ, которая возвращает часть текста из строки, начиная с левого края, в количестве символов, заданном вторым ее аргументом. С ее помощью мы проверяем, являются ли они равными строке «1111», если да, возвращаем результат «Visa», если нет, то выполняем вложенную функцию ЕСЛИ.

Подобным образом можно достичь значительной вложенности и организовывать сложные проверки.

Функции для работы с базами данных

Формул в данном разделе довольно много. Рассмотрим несколько самых основных и наиболее востребованных. К ним относятся:

  • БИЗВЛЕЧЬ – поиск записи в базе данных, которая соответствует указанному условию выборки;
  • БДСУММ – сумма всех чисел, которые находятся в указанном поле и соответствуют определенным условиям;
  • ДМИН – поиск минимального значения среди всей выборки данных из базы;
  • ДМАКС – поиск максимального значения среди всей выборки данных из базы;

Различные способы использования пользовательской функции в Excel

Создав пользовательскую функцию в VBA, вы можете использовать ее по-разному.

Давайте сначала рассмотрим, как использовать функции на листе.

Использование пользовательских функций в рабочих листах

Мы уже видели примеры использования функции, созданной в VBA, на листе.

Все, что вам нужно сделать, это ввести имя функции, и оно отобразится в intellisense.

Public VBA function used in Worksheet

Обратите внимание, что для того, чтобы функция отображалась на рабочем листе, она должна быть функцией Public (как описано в разделе выше).

Вы также можете использовать диалоговое окно «Вставить функцию» для вставки пользовательской функции (используя шаги ниже). Это будет работать только для публичных функций.

  • Перейдите на вкладку «Данные».
  • Нажмите «Вставить функцию».
  • В диалоговом окне «Вставка функции» выберите «Определено пользователем» в качестве категории. Эта опция отображается только тогда, когда у вас есть функция в редакторе VB (и функция Public).
  • Выберите функцию из списка всех общедоступных пользовательских функций.
  • Нажмите кнопку ОК

Вышеуказанные шаги вставят функцию в лист. Он также отображает диалоговое окно «Аргументы функции», которое предоставит вам подробную информацию об аргументах и результате.

Information dialog box when you insert the Function

Вы можете использовать пользовательскую функцию, как и любую другую функцию в Excel. Это также означает, что вы можете использовать его с другими встроенными функциями Excel. Например. приведенная ниже формула даст название рабочей книги в верхнем регистре:

Использование пользовательских функций в процедурах и функциях VBA

Когда вы создали функцию, вы можете использовать ее и в других подпроцедурах.

Если функция Public, она может использоваться в любой процедуре в том же или другом модуле. Если это Private, его можно использовать только в том же модуле.

Ниже приведена функция, которая возвращает имя рабочей книги.

Приведенная ниже процедура вызывает функцию, а затем отображает имя в окне сообщения.

Вы также можете вызвать функцию из другой функции.

В приведенных ниже кодах первый код возвращает имя рабочей книги, а второй возвращает имя в верхнем регистре, вызывая первую функцию.

Вызов пользовательской функции из других книг

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

Есть несколько способов сделать это:

  1. Создание надстройки
  2. Функция сохранения в персональной макрокоманде
  3. Ссылка на функцию из другой рабочей книги.

Поиск значений в таблице Excel

Для решения данной задачи проиллюстрируем пример на схематической таблице, которая соответствует выше описанным условиям.

Лист с таблицей для поиска значений по вертикали и горизонтали:

Над самой таблицей расположена строка с результатами. В ячейку B1 водим критерий для поискового запроса, то есть заголовок столбца или название строки. А в ячейке D1 формула поиска должна возвращать результат вычисления соответствующего значения. После чего в ячейке F1 сработает вторая формула, которая уже будет использовать значения ячеек B1 и D1 в качестве критериев для поиска соответствующего месяца.

Поиск значений по вертикали в списке неизвестного размера с точным соответствием

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

Примечание: Этот подход используется, если данные находятся в диапазоне внешних данных, который вы обновляете каждый день. Вы знаете, что в столбце B есть Цена, но вы не знаете, сколько строк данных возвращает сервер, а первый столбец не отсортирован по алфавиту.

C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).

Match («апельсины»; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

1 — количество столбцов справа от начальной ячейки, для которых должно быть возвращено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Sales .

В Excel существует множество полезных функций со своим уникальным набором аргументов. Существует даже такие, которые не имеют ни одного аргумента, такие например как функции “ СЕГОДНЯ() ” (Возвращает текущую дату) и “ ПИ() ” (возвращает число 3,14159265358979 — математическую константу «пи» с точностью до 15 цифр).

Для вставки функции можно воспользоваться диалоговым окном Мастер функций. Это окно можно вызвать одним из следующих способов:

  • кнопку Вставить функцию, расположенную в группе Формулы ► Библиотека функций.
  • Нажмите кнопку Вставить функцию слева от строки формул.
  • Нажмите Shift+F3.

Вставки функции в Excel

Часто используемые функции довольно быстро запоминаются и воспользоваться ими не заставит труда, но, если вы забыли или не знаете её имя, можно использовать поисковое поле и нажать на кнопку “Найти”. После того как функция нашлась нажмите на “ОК” и следом откроется окно “Аргументы функции”, в котором можно ознакомится с аргументами данной функции.

Аргументы функции в Excel

При вводе функцию вручную (без помощи окна Мастер функций) для вызова диалогового окна “Аргументы функции” используйте сочетание клавиш Ctrl+A . Следует заметить, что данное сочетание не работает после ввода каких-либо аргументов данной функции.

Сочетание клавиш Ctrl+Shift+A заполняет функцию фиктивными аргументами. Это может быть полезно, если вы еще не знаете, какими данными заполнить функцию. Формула будет возвращать ошибку и позже следует аргументы заменить. Например при вводе функции “ =СУММЕСЛИ ” зажать Ctrl+Shift+A , то получим вот такой результат: =СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

Функция ГПР возвращает любой тип данных, такой как строка, число, дата и т.д. Если вы введете ЛОЖЬ для параметра интервальный_просмотр и точное совпадение не будет найдено, тогда функция ГПР вернет #Н/Д. Если вы укажете ИСТИНА для параметра интервальный_просмотр и точное совпадение не найдено, то будет возвращено следующее меньшее значение. Если номер_строки меньше 1, функция ГПР вернет #ЗНАЧ!. Если номер_строки больше, чем количество столбцов в таблице, функция ГПР вернет #ССЫЛКА!.

  • См. также функцию ВПР, чтобы выполнить вертикальный поиск.

Функции Excel.Функция И(), ИЛИ()

На прошлом уроке «Функции Excel. Функция ЕСЛИ» была рассмотрена работа со встроенной функцией ЕСЛИ(). Все бы хорошо, но возникают такие ситуации, и довольно часто, когда вложенностью условия в условие не обойтись. Например, элементарно необходимо проверить следующее: 1 больше или равно x и x меньше или равно 5 . Реализовать такое условие несколькими ЕСЛИ довольно громоздко и проблематично, а в некоторых ситуациях — вообще невозможно. Для расширения функционала ЕСЛИ и облегчения составления формул с условием, в Excel имеются еще пара полезных функции — И() и ИЛИ().

Функция И()

Функция И() используется тогда, когда необходимо проверить несколько условий следующим образом — Условие 1 И Условие 2. При этом все условия должны быть истинными. Результатом работы данной функции является ИСТИНА или ЛОЖЬ (TRUE / FALSE). Пример: ЕСЛИ а = b И а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Как видно из примера, значение 1 будет только в том случае, если все условия верны.

Функция ИЛИ()

Функция ИЛИ() используется тогда, когда необходимо проверить несколько условий следующим образом — Условие 1 ИЛИ Условие 2. Результат функции будет истинным, если хотя бы одно из условий истинно. Пример: ЕСЛИ а = b ИЛИ а=с ТОГДА значение 1 ИНАЧЕ значение 2.

Синтаксис функций И() и ИЛИ() одинаков: Функция(Условие 1; Условие 2; Условие 3 и до 30-ти условий). Результат ИСТИНА или ЛОЖЬ.

Примеры использования функции И и ИЛИ

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

Читайте также:  Панели инструментов на полосе панели в Excel

Пример 1.
Столбец А, начиная с первой строки, содержит 56, 55, 88, 6, 74. Столбец В — 52, 55, 88, 4, 25. Столбец С — 53, 55, 88, 6, 25. С помощью функций ЕСЛИ и И необходимо определить строки, значения которых равны следующим образом А=В и В=С.

Переходим в ячейку D1 и с помощью мастера функций вводим следующее

функция И пример

Формула будет выглядеть так: “ =ЕСЛИ(И(A1=B1;B1=C1);»Все значения равны»;»Значения не равны») “

Пример 2.

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

Мастер функций выглядит так:

функция ИЛИ пример

Формула: “ =ЕСЛИ(ИЛИ(A1=B1;B1=C1;A1=C1);»Есть равные значения»;»Нет равных значений»)”

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

И напоследок рассмотрим еще один пример с функцией И и ИЛИ из реальной жизни.

Пример 3.

В таблицу занесены процент выполнения заданий, необходимо проценты перевести в пятибалльную систему следующим образом:

Таблица следующего вида:

сложные условные формулы в Excel

Составим формулу следующего вида:

Пища для разума Почему значения указаны в вещественном виде т.е. 0,41, 0,76, можете прочитать в уроке «Формат данных в Excel»!?

Результат работы формулы:

П.С.: Если есть варианты сократить формулу(а они есть) пишите в комментариях. А пока – пока

Все так же наша фраза «Здравствуйте я ваша тётя!» на своем месте в ячейке B3, установим курсор в ячейку B7 и нажмем на иконку Fx в строке формул.

ПОИСК в эксель

Откроется мастер формул, в категории Текстовые выберем функцию ПОИСК.

ПОИСК в эксель

В поле Искомый_текст введем слова «ваша тётя», Текст_для_поиска B3, Нач_позиция 1. Результатом станет число 16, оно означает, что наш текст находится на 16 символов правее начала строки.

ПОИСК в эксель

Функцию ПОИСК чаще всего не используют самостоятельно, а в сочетании с другими, например с функцией ЗАМЕНИТЬ.

В этом видео подробно показано применение функции ПОИСК в excel:

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

Примеры поиска функцией Find

Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

Обратите внимание : Когда поиск достигнет конца диапазона, функция продолжит искать с начала диапазона. Таким образом, если значение найденной ячейки не менять, то приведенный выше пример зациклится в бесконечном цикле. Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

В ниже следующем примере используется другой вариант продолжения поиска — с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.

Пример 3: Продолжение поиска с использованием Find с параметром After.

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:

Следующий пример — применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

Пример 5: Найти последнюю колонку и столбец, заполненные данными

Для поиска функцией Find по маске (шаблону) можно применять символы:
* — для обозначения любого количества любых символов;
? — для обозначения одного любого символа;

— для обозначения символов *, ? и

. (т.е. чтобы искать в тексте вопросительный знак, нужно написать

?, чтобы искать именно звездочку (*), нужно написать

* и наконец, чтобы найти в тексте тильду, необходимо написать

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

  • Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
  • В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

Приведу несколько примеров поиска даты.

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

Пример 8: Найти 1 марта 2018 г.

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

Excel. Функции ИНДЕКС и ПОИСПОЗ

Для извлечения данных вместо ВПР можно использовать связку функций ИНДЕКС и ПОИСКПОЗ. Формулы с ними работают быстрее и намного гибче. Лично я использую ВПР 90% времени, но иногда переключаюсь на ИНДЕКС + ПОИСКПОЗ, например, когда «ключевой» столбец не является первым слева в таблице.

Для меня функция ИНДЕКС совершенно непонятна. Проблема: я недавно прочитал справку Excel по этой функции. Кто в здравом уме будет использовать =ИНДЕКС(В4:G22;2;4), чтобы указать на ячейку F6 (рис. 5.1)?

Рис. 5.1. Функция ИНДЕКС кажется бесполезной

Рис. 5.1. Функция ИНДЕКС кажется бесполезной

Скачать заметку в формате Word или pdf, примеры в формате Excel

Стратегия: вы никогда не будет использовать ИНДЕКС, не используя ПОИСКПОЗ в качестве второго или третьего аргумента. Поэтому давайте вернемся к функции ИНДЕКС после того как вы увидите, что делает ПОИСПОЗ.

На самом деле, вы уже знакомы с тем, как работает функция ПОИСКПОЗ. Действительно, если вы используете ВПР, вы уже знаете кое-что и о ПОИСКПОЗ. Давайте сравним эти функции (рис. 5.2):

  • Первым параметром является поисковое значение, как ВПР.
  • Таблица подстановки включает один столбец, а не прямоугольный диапазон.
  • Вам не нужно указывать номер столбца, поэтому отсутствует третий аргумент.
  • Последний аргумент – ЛОЖЬ, как и в ВПР (также можно использовать ноль).

Пока всё очень похоже.

А вот и отличие… ПОИСКПОЗ не возвращает значение из таблицы. ПОИСКПОЗ расскажет вам, какая строка в таблице содержит искомое значение. Какая… по номеру. В нашем примере – вторая.

Трюк заключается в том, что вы всегда будете использовать ПОИСКПОЗ внутри функции ИНДЕКС. Итак, вернемся к индексу.

Рис. 5.2. ПОИСКПОЗ – замаскированная ВПР

Рис. 5.2. ПОИСКПОЗ – замаскированная ВПР

Стратегия: фокус в том, чтобы использовать ИНДЕКС для возврата значения. Примените при этом ПОИСПОЗ в качестве второго и/или третьего аргумента, чтобы налету вычислить к какой строке или столбцу обратиться. (В примере выше функция ПОИСКПОЗ искала в вертикальном списке. Она также можете найти значение в горизонтальном списке. По сути, аналог ГПР.)

Далее будут приведены примеры использования связки ИНДЕКС + ПОИСКПОЗ для решения проблем.

Поиск слева от ключевой колонки. Проблема: таблица подстановки ведется в другом отделе. Колонка с ценой у них расположена слева от столбца с номенклатурой. Могу ли я указать «–1» (минус один) в качестве третьего аргумента функции ВПР?

Рис. 5.3. Поиск значение слева от номенклатуры

Рис. 5.3. Поиск значение слева от номенклатуры

Стратегия: к сожалению, команда разработчиков Excel не предоставляет возможности для ВПР искать слева от ключевого поля. Иногда решение может заключаться в том, чтобы просто вырезать столбец F и вставить его справа от столбца с артикулами, например, в столбце I. И после этого применить ВПР. Мы исходим из того, что такие манипуляции вам недоступны. Вы можете использовать ПОИСКПОЗ, чтобы выяснить, какую цену извлечь из таблицы для того или иного артикула.

Итак, артикулы расположены в столбце G. Они могут быть не отсортированы, но не должно быть дублей. Посмотрите на формулу в С6 (рис. 5.4) =ПОИСКПОЗ(A6;$G$2:$G$29;0). Она говорит Excel найти СR50-3 в диапазон G2:G29. Третий аргумент – ноль – указывает на то, что вы ищете точное совпадение.

Рис. 5.4. ПОИСПОЗ находит CR50-3 во второй строке таблицы подстановки

Рис. 5.4. ПОИСПОЗ находит CR50-3 во второй строке таблицы подстановки

ПОИСКПОЗ возвращает значение 2, т.е. говорит, что CR50-3 находится во второй строке диапазона. И на самом деле CR50-3 находится в G3. ПОИСКПОЗ возвращает относительное положение элемента в пределах диапазона поиска. Ответ «2» говорит, что CR50-3 находится во второй ячейке G2:G29.

Теперь, когда вы знаете позицию элемента в таблице поиска, вы можете использовать функцию ИНДЕКС, чтобы извлечь цену из таблицы. Укажите диапазон цен в качестве первого аргумента функции ИНДЕКС (рис. 5.5). Второй аргумент задает строку в таблице подстановки. И этим вторым аргументом является функции ПОИСКПОЗ. Поскольку у вас только один столбец в таблице подстановки, вам не нужен третий аргумент функции ИНДЕКС. В ячейке С6 формула =ИНДЕКС($F$2:$F$29;ПОИСКПОЗ(A6;$G$2:$G$29;0)).

Рис. 5.5. По существу, вы применили левую ВПР

Рис. 5.5. По существу, вы применили «левую» ВПР

Быстрый поиск по множеству столбцов. Проблема: мне нужно сделать двенадцать столбцов поиска. Таблица подстановки очень большая. Набор данных также весьма велик. Значения постоянно меняются и формулы пересчитываются.

ВПР – это дорогая (по ресурсам) функция. Довольно много времени нужно, чтобы найти точное соответствие в таблице подстановки. Возможно придется искать среди 200 строк таблицы подстановки, чтобы найти артикул при поиске январского значения. Когда Excel начнет искать февральское значение, он должен всё начать заново. По времени, ПОИСКПОЗ и ВПР работают приблизительно одинаково. Зато ИНДЕКС работает существенно быстрее, напрямую обращаясь к указанной строке и считывая значение.

Стратегия: добавить столбец «Где?», который вскоре будет скрыт. Использовать в нем формулу ПОИСКПОЗ, возвращающую номер строки в таблице подстановки, где находится артикул (рис. 5.6).

Рис. 5.6. Продукт A308 находится в 208-й строке таблицы подстановки

Рис. 5.6. Продукт A308 находится в 208-й строке таблицы подстановки

Теперь можно использовать функцию ИНДЕКС в 12 столбцах основной таблицы для извлечения значений из соответствующего столбца таблицы подстановки (рис. 5.7). Важно спланировать размещение знаков доллара в формуле:

  • В ячейку C3 введите =ИНДЕКС(R$3:R$226;$B3;0). Вы используете $ перед 3 и 226, чтобы ссылка не менялась при копировании формулы вдоль столбца С. Однако, вы не используете знаки доллара перед буквой R. При копировании этой части формулы вправо в ячейку D3 ссылка изменится на S$3:S$226. Будет использован столбец S таблицы подстановки, относящийся к февралю. Второй аргумент функции ИНДЕКС использует один знак доллара перед обозначением столбца B. При копировании формулы вправо она по-прежнему будет ссылаться на столбец В.
  • Скопируйте С3 в диапазон D3:Н3.
  • Выберите С3:Н3. Скопируйте этот диапазон вниз на все строки, дважды щелкнув маркер заполнения в правом нижнем углу ячейки Н3.
  • Скройте столбец В.

Рис. 5.7. Такая таблица в десять раз быстрее, чем аналогичная, использующая функции ВПР

Рис. 5.7. Такая таблица в десять раз быстрее, чем аналогичная, использующая функции ВПР

Решение на основе ПОИСКПОЗ + ИНДЕКС, показанное здесь, позволяет решить проблему редактирования третьего аргумента функции ВПР описанного ранее.

Использование функции ПОИСКПОЗ для нахождения чуть большего значения. Проблема: мне нужно найти продукт, объем которого равен или больше, чем искомое значение. Я продаю порошок для покрытия, расфасованный в контейнеры различного размера. Содержимое каждого контейнера может покрыть определенную площадь (в квадратных метрах). Если клиенту необходимо покрыть N квадратных метров, мне нужно найти ближайший (но больший) контейнер.

ВПР позволяет найти следующий меньшего размера, но не следующий большего размера (см. главу 6). На рис. 5.8 показан «правильный» продукт для покрытия 131 кв. м. Это продукт с артикулом N2557, который позволяет покрыть до 144 кв. м.

Рис. 5.8. Используя в качестве последнего аргумента функции ПОИСКПОЗ «–1»

Рис. 5.8. Используя в качестве последнего аргумента функции ПОИСКПОЗ «–1» можно начти следующее большее значение

Стратегия: используйте ПОИСКПОЗ с третьим аргументом равным –1 (минус 1). Если использование в качестве третьего аргумента нуля, позволяет найти точное совпадение, то при «–1» ПОИСКПОЗ ищет значение, равное или ближайшее большее, чем искомое значение. Например, ПОИСКПОЗ(B2;$H$2:$H$9;-1) вернет 2, поскольку 144 кв. м – ближайшее большее искомых 131 кв. м. А =ИНДЕКС($I$2:$I$9;2;-1) вернет N2557.

Обратите внимание, что для использования функции ПОИСКПОЗ с аргументом «–1», таблица подстановки должна быть отсортирована в порядке убывания. Также заметьте, что ПОИСКПОЗ может найти значение равное искомому. Например, в строке 4 поиск по значению 144 вернул артикул N2557, а не следующий большего размера.

Чтобы закончить анализ, добавьте в ячейку D2 формулу =ИНДЕКС($K$2:$K$9;ПОИСКПОЗ(B2;$H$2:$H$9;-1)) или =ВПР(C2;$I$2:$K$9;3;ЛОЖЬ).

Использование ПОИСКПОЗ для определения, присутствует ли элемент в списке. Проблема: у меня есть список элементов (товаров), по которым сегодня поступил заказ. Мне нужно сравнить этот список со списком позиций, которые есть в наличии на складе. Это же вопрос был задан в начале главы 4. Однако, сейчас с ПОИСКПОЗ ответ может быть немного проще.

Читайте также:  Формула форматирует число суммы как текст в одной ячейке Excel

Рис. 5.9. Какие элементы из столбца С есть в наличии, т.е. присутствуют в столбце D

Рис. 5.9. Какие элементы из столбца С есть в наличии, т.е. присутствуют в столбце D?

Стратегия: введите в В2 формулу =ПОИСКПОЗ(A2;$D$3:$D$16;0). Если элемент найден в списке Товары на складе, ПОИСКПОЗ вернет число – номер элемента в списке. Если товара нет в наличии, ПОИСКПОЗ вернет ошибку #Н/Д. Воспользовавшись рекомендациями главы 4, можно с помощью функций ЕСЛИ и ЕОШИБКА модифицировать формулу, чтобы она возвращала что-то более человеческое, например, =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$D$3:$D$16;0)); » нет на складе » ; » в наличии » ).

Рис. 5.10. Использование ПОИСКПОЗ для определения, присутствует ли элемент в списке

Рис. 5.10. Использование ПОИСКПОЗ для определения, присутствует ли элемент в списке

Различия

Проанализировав результаты, полученные при использовании инструментов ПОИСК и НАЙТИ, можно выделить несколько ключевых отличий:

  1. Функция НАЙТИ чувствительна к регистру букв, то есть алгоритм распознает большие и маленькие символы. Это четко видно на примере фрукта Апельсин. ПОИСК вернул 1, а НАЙТИ выдало ошибку, поскольку маленькой буквы а нет в ячейке. Подобная ситуация и для Ананаса с Айвой
  1. Дополнительным различием является то, что ПОИСК умеет работать с подстановочными символами типа вопросительный знак (?) и звездочка (*), которые используются для неточного формирования поискового запроса.

На заметку! Чтобы найти именно эти символы в тексте необходимо использовать тильду (

  1. Инструмент НАЙТИ реализован как в виде отдельной кнопки на главной панели, так и в виде встроенной функции редактора.

Как видите, excel позволяет осуществлять поиск несколькими способами, каждый из которых обладает своими преимуществами. При помощи одного можно осуществить поиск по нескольким значениям, а другой можно вызвать используя горячие клавиши, что, несомненно, ускоряет процесс работы с документом.

Банковские функции в Excel

Функция ПЛТ в Excel: расчёт кредитных платежей

Если предприятие работает с заёмными средствами, то возникает необходимость в расчёте денежных потоков по кредитам. С этой целью в состав Excel включены функции, позволяющие рассчитывать текущие платежи по кредитам.

Функция ПЛТ позволяет рассчитать полный аннуитетный платеж (платеж, включающий в себя выплату тела кредита и проценты по нему, размер которого не изменяется в течение всего периода). Первый аргумент функции – банковская ставка в процентах, второй – количество периодов (лет или месяцев), третий – сумма займа. Есть ещё два необязательных аргумента: четвёртый – сумма кредита после периода выплат (обычно считается, что кредит погашается полностью и эта сумма равна нулю), пятый – показатель условия, когда осуществляется очередной платеж – в начале или конце периода, это немного влияет на итоговую величину платежа.

Переключитесь на лист «кредит». Заполните начальные значения, например сумма кредита – 1 млн.руб., срок – 1,5 года, ставка – 24% годовых. Присвойте значениям удобные имена, например Сумма, Срок_кредита, Ставка_кредита. В ячейку В10 введите формулу ежемесячного платежа: «=-ПЛТ(Ставка_кредита/12;Срок_кредита*12;Сумма_кредита)». Обратите внимание, что платеж представляет собой отрицательный поток денег, поэтому Excel выдаёт отрицательное значение, которое мы для удобства отображения меняем в формуле на положительное. Обратите также внимание на корректировки периода: ставку нужно поделить на 12, а срок умножить, т.к. расчётный период должен быть везде одинаковый (месяц). Протяните формулу вниз на соответствующее число месяцев.

Кроме расчёта ежемесячного платежа, финансисту желательно знать сумму процентных платежей (эта информация нужна для точного расчёта налога на прибыль) и остаток долга (для составления прогноза баланса). Для этого используются функции ОСПЛТ и ПРПЛТ, которые для конкретного месяца показывают сумму погашения основного платежа и сумму уплаченных процентов соответственно. Состав аргументов тот же, что в функции ПЛТ, плюс порядковый номер месяца.

В ячейку С10 введите «=-ОСПЛТ(Ставка_кредита/12;A10;Срок_кредита*12;Сумма_кредита)», а в ячейку D10 – «=-ПРПЛТ(Ставка_кредита/12;A10;Срок_кредита*12;Сумма_кредита)». Здесь второй аргумент А10 – порядковый номер месяца. Протяните все формулы вниз, до 18 месяца.

В правом столбце рассчитаем остаток долга. В ячейку Е10 введите «=Сумма_кредита-C10», в ячейку Е11 «=E10-C11» и протяните ячейку Е11 до конца таблицы. В строчке, соответствующей 18 месяцу, должен появиться 0. Теперь можно посчитать итоговые значения аннуитетных платежей, платежей по основной сумме и процентных платежей.

Текстовые функции в Excel

В финансовом моделировании изредка применяются функции работы с текстом. Полезны будут следующие функции: оператор конкатенации & и функция ТЕКСТ.

Оператор конкатенации & объединяет строчки текста в теле формулы и текстовых значений в одну строку.

На листе «основные» файла Задание к разделу 2-1 (функции).xls в произвольной ячейке введите выражение «=»фактическая выручка за «&A2&» составляет «&C2&» рублей»». Программа подставит соответствующие значения из ячеек А2 и С2 и сформирует текстовую фразу. Обратите внимание на оператор конкатенации &, который используется между каждыми «кусочками» фразы, будь то текст или ссылка на ячейку.

Также в произвольном месте введите выражение «=»Среднемесячная выручка равна: «&A20». Результатом будет текст «Среднемесячная выручка равна: 1060833,33333333», так как в ячейке А20 именно такое значение. Чтобы привести фразу к читаемому виду, используем функцию ТЕКСТ. Эта функция имеет два аргумента: первый – ссылка на ячейку с числовым значением, второй – формат, к которому надо это значение преобразовать. Формат задаётся следующим образом:

  • # ###– числа с разделением разрядов;
  • # ###,## – числа с разделением разрядов и с двумя знаками после запятой;
  • #,#% — процентный формат с одним знаком после запятой и т.п.

�?справьте выражение следующим образом: «=»Среднемесячная выручка равна: «&ТЕКСТ(A20;»### ###,##»)» и получите искомый результат.

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

Оформление и примеры использования

Алгоритм написания логических формул в Эксель следующий:

  1. Нужно выделить пустую ячейку, в которую будет записываться формула и выводиться результат действия.
    Выделение ячейкиВписывать можно и в строке формул, после выделения ячейки.Строка формул
  2. Перед формулами в программе ставится знак «=». Поставить его.
  3. Напечатать название оператора.
  4. После этого вписываются аргументы, если они есть. Начинается запись со знака «открывающаяся круглая скобка “(“».
  5. Аргументы вводятся последовательно через знак ”;”. Также, если после ввода названия функции нажать клавиши Ctrl + A, то откроется меню аргументов и вписать их можно здесь.Ввод аргументов
  6. В конце ставится символ «закрывающаяся круглая скобка “)”». Контролировать написание можно в строке формул.Строка формул
  7. После завершения нажать кнопку ENTER. Результат появится в ячейке.

ИСТИНА, ЛОЖЬ

Пример таблицы

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

Результат формулы

После применения формулы «=ЕСЛИ(ЛЕВСИМВ(В3;4)=”8800”;ИСТИНА();ЛОЖЬ())», получается:

Сравнение происходит по первым четырем цифрам номера (оператор ЛЕВСИМВ(В3;4)). Если номер начинается с 8800, то звонок бесплатный, в противном случае — нет.

Отрицание — НЕ

Пример отрицания

Функция ссылается на ячейку или аргумент, где есть логический ответ, и меняет его на противоположный. Чаще всего применяется в составе формул. Пример:

Здесь оператор «=НЕ(F2)» инвертирует аргумент в столбце F.

Применение ЕСЛИ

«ЕСЛИ» всегда включает знаки сравнения и применяется в формулах с условием. Логика при его использовании такова:

  1. Задается вопрос, содержащий элемент сравнения.
  2. Далее вписываются 2 значения. Первая величина отобразится в ячейке в случае ответа «TRUE», вторая — если ответ «FALSE».
  3. Возможно создание многоуровневых вложений «ЕСЛИ».

таблица продаж

Например, работникам компании установлен минимальный порог продаж в размере 1 млн. рублей. При выполнении плана сотрудник получит зарплату в 20 тыс. рублей и надбавку в 5%. В случае, если продано на меньшую сумму, то премия не выплачивается. Результаты деятельности работников отображены в списке.

Постановка условия

Требуется разделить сотрудников в таблице по критерию исполнения плана. Для этого в программе создается таблица с дополнительными колонками E (Выполнение плана) и F (Зарплата за месяц).

Для выделения сотрудников применяется формула =ЕСЛИ(D4>=1000000;»Молодец!»;»План не выполнен:(«). Расшифровывается так:

Используется автозаполнение

  1. D4>=1000000. Создается запрос на проверку ячейки D. В случае если показатель в D4 больше или равен 1 млн., то ответ «TRUE». Если нет, то «FALSE».
  2. «Молодец!«. При положительном ответе в ячейке E4 появится надпись «Молодец!».
  3. «План не выполнен:(«. В противном случае отобразится «План не выполнен».
  4. Нажать Enter.
  5. Применив автозаполнение к E4, можно распространить действие формулы на все строки столбца E.

Результатом будет таблица, в которой указано, выполнил менеджер план или нет.

ЕСЛИМН или УСЛОВИЯ

В предыдущем примере было одно условие. Но в большинстве случаев при составлении отчетов учитывается много факторов. Приходится составлять многоуровневые вложенные «ЕСЛИ».

Ввод формулы

Например, если требуется разделить начисление премии в зависимости от процента продаж. При выручке менее 90% от плана, дополнительное вознаграждение не выплачивается. 90-95% — премия 10%, более 95% — 20%, продажи сверх плана награждаются премией в 30%. С оператором «ЕСЛИ» формула будет выглядеть так: «=ЕСЛИ(В2 0,9;0;ЕСЛИ(В2 0,95;0,1; ЕСЛИ(В2 1;0,2;0,3)))».

Пример использования

Запись сложна при написании и проверке. Можно пропустить скобку или неверно указать порядок аргументов. Для упрощения в 2016 году была введена «ЕСЛИМН». При ее использовании не нужно писать «ЕСЛИ» для каждого условия и следить за количеством скобок. Та же задача с «ЕСЛИМН»:

Запись упростилась, указываются условия и соответствующие им значения. Последним аргументом можно указать оператор ИСТИНА и задать нужную величину. В случае если ни одно из условий не выполнилось, будет возвращен параметр функции ИСТИНА. В данном случае, если В2 >=1, то награда 30%.

При задании условий проявлять осторожность. Большое количество значений может привести к некорректной работе оператора. Условия проверяются по очереди, в порядке указания. Поэтому, если какое-то из них будет исполнено, то функция не станет проверять оставшиеся значения, и в итоге появится ошибка. Надо внимательно продумывать порядок размещения, так, чтобы отработали все аргументы.

Работа с ПЕРЕКЛЮЧ

Сравнивает указанную величину в ячейке или формулу со списком данных и вписывает в ячейку первое совпавшее значение. Если совпадений не будет, и не проставлена величина по умолчанию, оператор выдаст ошибку «#Н/Д». Функция схожа с ЕСЛИМН, но в отличие от нее условие ставится точно, без сравнительных знаков.

Ввод формулы

Работа оператора иллюстрируется на рисунке.

Здесь вместо чисел 1, 2, 7 — нужно проставить прописью дни недели им соответствующие. Если будут другие цифры, то возвратится значение по умолчанию «Нет совпадений (No match)».

Использование ЕСЛИОШИБКА

Оператор используется для нахождения ошибки в таблице. Найдя ее, функция не пишет в ячейке какую-либо из ошибок, а возвращает указанный ответ, который может быть текстом, пустой строкой: =ЕСЛИОШИБКА(Что_проверять;Что_выводить_вместо_ошибки).

Ввод формулы

Например, нужно поделить значения в столбце А на величины в столбце В. Если по ошибке в строках стоят 0, то получится деление на 0.

Если ошибка

Применение оператора «=ЕСЛИОШИБКА(A2/B2;»»)» скрывает ошибки.

Здесь сравнивается выражение A2/B2. В случае обнаружения ошибки в ячейку ставится пустая строка, указанная пробелом в кавычках ““.

ЕСЛИОШИБКА появилась в Excel 2007. До этого использовалась функция ЕОШИБКА, которая самостоятельно не могла обработать ошибку, так как имела только один аргумент, проверяющий указанную ячейку. Для ввода ответа в случае обнаружения ошибки, нужно было использовать оператор ЕСЛИ: «ЕСЛИ(ЕОШИБКА(А2/В2);”“;А2/В2)».

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

Формула ИЛИ

На рисунке показан принцип действия функции И.

Пример использования: «=И(A1>B1; A2<>25)». Здесь созданы два условия:

  1. Значение в ячейке А1 должно быть больше числа в В1.
  2. Число в А2 должно быть не равно 25.

Выполнение условий

При исполнении обоих получается ИСТИНА.

Отрицание

Если одно из заданий нарушено, получается ЛОЖЬ. В данном случае число в А1 меньше чем в В1.

Оператор ИЛИ

Ниже представлен алгоритм функционирования оператора ИЛИ.

Варианты использования

Пусть даны 3 выражения: A1>B1; A2>B2; A3>B3. Требуется применить к ним действие ИЛИ: «=ИЛИ(A1>B1; A2>B2; A3>B3)». Возможные варианты показаны на рисунках:

Функция ложь

Здесь конечный результат ИСТИНА, так как из трех выражений одно верно: A3>B3. На следующем изображении функция выдала ответ «ЛОЖЬ», так как на все вопросы получены аналогичные ответы.

Действие ИСКИЛИ

В программировании функция соответствует операция «сложение по модулю 2» или XOR. Если имеется больше двух аргументов, то действуют следующие правила:

Функция ИСКИЛИ

  • результат «ИСТИНА», если количество таких ответов нечетно;
  • результат «ЛОЖЬ», если количество ответов «TRUE» четно;
  • результат «ЛОЖЬ», при условии, что все «FALSE».

Даны 4 условия A1>B1; A2>B2; A3>B3; A4>B4. В зависимости от данных ячеек результат действия функции может быть различным.

Варианты использования

На рисунке ниже получен результат «ИСТИНА», так есть 3 условия с аналогичным результатом: A1>B1 (100 ); А2 В2 (100>80); А3>В3 (100>70). Число условий с ответом ИСТИНА нечетно.

Функция ложь

В следующем варианте решением будет «ЛОЖЬ», так как есть 4 ответа «ИСТИНА» — четное количество.

На последнем рисунке функция также обретет значение ЛОЖЬ, так как не выполнено ни одно условие.

ЕСЛИ (IF)

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

  • премия начисляется только при выполнении сотрудником плана (функция заполнит ячейку с суммой премии или оставит её пустой в зависимости от того, что указано в ячейке его продаж);
  • расчёт за поставку производится через определённое количество дней или после распродажи (когда известен процент возврата);
  • дальнейшее финансирование проекта осуществляется, если он начинает приносить прибыль (или выделяемая сумма привязана к этой прибыли).

Фиксация положения одной из ячеек в формуле

Если вам требуется зафиксировать положение (ячейку) для одной из переменных в формуле, то требуется просто заменить в самой формуле значение вида =F2 на значение =$F$2 (вставить знак доллара). После чего, вы сможете «протягивать» формулы для всей строки или столбца с фиксацией одной из переменный (ячеек). Пример использования:

* Стоит отметить, что функция условного форматирования быстро работает только с небольшими и средними таблицами и плохо справляется с большими массивами данных.

Ссылка на основную публикацию