Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти ближайшее значение больше или меньше, чем в Excel?

Функция ЕСЛИ в Excel

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

Функция ЕСЛИ — это логическая функция в MS Excel, которая позволяет строить очень сложные алгоритмы, основанные на проверке условий и выборе из двух или более альтернатив.

Вкладывая одну функцию ЕСЛИ в другую в excel, можно строить алгоритмы ветвления, деревья решений и другие системы и формулы. Ограничение на количество вложенных функций в Excel 2010 составляет 64. Это позволяет создавать поистине грандиозные алгоритмы вычислений.

Функция «ЕСЛИ» в Excel имеет следующий синтаксис:

IF(log_expression;значение_если_истинно;значение_если_ложно)

Если условие (лог_выражение) принимает определенное значение, функция возвращает одно из двух значений. Если условие равно TRUE, то функция IF вернет значение IF_TRUE, а если нет, то IF_FALSE.

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

Выражением Log_expression может быть любое значение, выражение или формула, принимающая в качестве значения TRUE или FALSE. Например, C5=»с НДС» — это логическое выражение. Логическое выражение принимает значение TRUE, если ячейка C5 содержит слова «с НДС»; в противном случае оно принимает значение FALSE, если содержит слова «без НДС. «.

Value_if_truth — значение или выражение (формула), которое возвращается, если аргумент «log_expression» имеет значение TRUE. Эта формула, например, выполняет вычисление, если значение логического выражения равно TRUE.

Если аргумент «log_expression» установлен в TRUE и аргумент «value_if_truth» не установлен, возвращается 0 (ноль).

Value_if_false — значение или выражение (формула), которое возвращается, если аргумент «log_expression» имеет значение FALSE. Так, например, если этот аргумент C12*1 или просто C12, если значение логического выражения равно FALSE, то вычисление производится по этой формуле, т.е., как в нашем примере, берется только значение суммы в ячейке C12.

Если аргумент «log_expression» равен FALSE, а аргумент «value_if_ false» опущен (т.е. после аргумента «value_if_truth» нет точки с запятой), возвращается логическое значение FALSE. Если аргумент «log_expression» равен FALSE, а аргумент «value_if_truth» пуст (т.е. после аргумента «value_if_truth» стоит точка с запятой, за которой следует закрывающая скобка), возвращается 0 (ноль).

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

Пример 1. Использование текстовых значений.

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

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

Создайте формулу с использованием функции IF:

=ЕСЛИ(Р20>1000;“превышение лимита”;“в рамках лимита”)

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

Пример 2. Скрытие значений. Скрытие нулевых значений.

Почти повсеместной ошибкой является #DEL/0! при подготовке исходных форматов. Мы готовим исходный формат, в котором рассчитываем темп роста одного показателя по сравнению с другим в одном из столбцов. Как вы знаете, для этого используется формула A/B*100%, но так как у нас еще нет данных, возникает ошибка, когда мы делим значения из столбца A на нулевые значения из столбца B.

Читайте так же:
Как импортировать / скопировать данные из закрытой книги в текущую?

Чтобы избежать этого, мы используем функцию IF в качестве одной из опций.

=ЕСЛИ(В10=0;0;А10/В10) или =ЕСЛИ(В10=0;””;А10/В10)

В первом случае вместо #DEL/0 будет возвращен 0! ошибка, а во втором случае это будет просто пустая ячейка. Ячейки, содержащие двойные кавычки, являются пустыми.

Другим примером может быть проверка сходимости баланса. В результате нам необходимо сравнить сумму активов и сумму пассивов баланса, и если они равны, т.е. C85-C160=0, мы должны скрыть нулевое значение, а если они различны, мы должны отобразить разницу.

Пример 3. Многоуровневые, вложенные вычисления.

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

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

Типичным примером может быть расчет скидки в зависимости от нескольких уровней продаж. Заработок, зависящий от нескольких уровней выпуска.

Предположим, что у нас имеется следующая матрица скидок:

До 100 000 руб. — 0%.

От 100 001 рубля до 300 000 рублей — 3%.

От 300 001 до 500 000 рублей — 5 %.

Более 500,001 — 7

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

Создайте формулу с помощью функции IF:

Предположим, что в ячейке D10 указана стоимость продажи.

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

Операторы сравнения.

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

= Больше или равно.

Менее или равно .

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

Чтобы указать диапазон ссылок в примере №3, мы просто использовали >= больше или равно.

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

Функции И, ИЛИ, НЕ

Использовать AND в Excel можно следующим способом:

Функция возвращает TRUE, если результат вычисления всех аргументов равен TRUE; она возвращает FALSE, если результат вычисления хотя бы одного из аргументов равен FALSE.

Использование OR в excel — это то же самое, что:

Однако он возвращает TRUE, если хотя бы один из аргументов равен TRUE. Если все аргументы равны FALSE, возвращается FALSE.

Читайте так же:
Как извлечь или получить год, месяц и день из списка дат в Excel?

Функция «НЕТ» в Excel имеет следующий синтаксис:

Используя функцию NOT, можно убедиться, что значение не равно определенному значению. Если значение равно TRUE, она вернет FALSE и наоборот.

Пример 4. Использование нескольких условий.

Рассмотрим сценарий, в котором нам нужно отобрать должников, задолжавших нам 1000,00 российских рублей или более за три месяца. Если формула выводит критерий «злостный должник», предположим, что она выводит этот критерий здесь.

Ячейка B10 включает срок задолженности в месяцах, а ячейка C10 — сумму задолженности.

В этом случае формула будет выглядеть следующим образом

Если считать критическими либо долги старше 3 месяцев, либо долги свыше 100 000 рублей, то формулу можно записать следующим образом

В общем, как вы можете себе представить, возможности использования функции ЕСЛИ в Excel безграничны.

Встроенных средств MS Excel вполне достаточно для реализации алгоритма расчета, что является наиболее важным шагом.

Microsoft Excel содержит дополнительные функции, которые можно использовать для анализа данных с помощью условий. Например, чтобы подсчитать количество вхождений текстовой строки или числа в диапазоне ячеек, можно использовать функции READ и READ. Чтобы вычислить сумму значений в заданном диапазоне текстовой строки или числа, можно использовать функции SUMMESLY и SUMMESLIMN.

Как правильно искать в таблицах Excel

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

Чтобы открылось диалоговое окно Найти и заменить, выполните команду Главная * Правка * Найти и выделить * Найти (или нажмите Ctrl+F). Замените данные, выбрав команду Главная ► Правка ► Найти и выделить ► Заменить (или нажмите Ctrl+H). В зависимости от того, какую команду вы выполняете, диалоговое окно открывается на одной из двух вкладок.

Дополнительные параметры поиска вы увидите в диалоговом окне Найти и заменить, которое открывается при нажатии кнопки Параметры (рис. 13). 21.1).

Рис. 21.1. Вкладка Найти диалогового окна Найти и заменить

Рисунок 21.1. Вкладка «Найти» диалогового окна «Найти и заменить

Во многих случаях в поиске лучше указывать не точный текст, а приблизительный, например, настенные клавиатуры. Например, чтобы найти данные о клиенте Иване Смирнове, вы, конечно, можете ввести точный текст в строку поиска. Однако, скорее всего, вы ничего не найдете, так как могли ввести имя клиента по-другому, например IvanSmirnov или I. Smirnov, или даже сделать ошибку в фамилии. Для поиска имени такого типа лучше всего использовать подстановочные знаки.

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

Читайте так же:
Как найти 5 наименьших и высших XNUMX значений в списке в Excel?

При поиске в диалоговом окне «Найти и заменить» можно использовать два знака подстановки:

  • ? — соответствует любому символу;
  • * соответствует любому количеству символов.

Кроме того, подстановочные знаки можно использовать для поиска числовых значений. При вводе 3* в строку поиска будут показаны все записи, начинающиеся с 3, а при вводе 1?9 в строку поиска будут показаны все трехзначные записи, начинающиеся с 1 и заканчивающиеся 9.

Используйте тильду () для поиска вопросительного знака или звездочки

).
Например, следующая строка поиска находит текст *NONE*: -*N0NE

*
Чтобы найти символ тильды, поставьте в строке поиска две тильды.

При возникновении проблем с поиском вы можете проверить следующие настройки (которые иногда меняются сами по себе).

  • Флажок Uppercase Matching — установите этот флажок, чтобы сопоставить регистр искомого текста с регистром указанного текста. Например, если вы ищете слово Иван и установите этот флажок, слово Иван не будет появляться в результатах поиска.
  • Флажок Полная ячейка: установите этот флажок, чтобы найти ячейку, содержащую именно тот текст, который указан в строке поиска. Например, если вы введете слово Excel в поле поиска и установите флажок, вы не найдете ячейку, содержащую слово Microsoft Excel.
  • Выпадающий список окна поиска: список содержит три элемента: значения, формулы и примечания. Например, если вы зададите в поле поиска число 900 и выберете в раскрывающемся списке «Значения» поле поиска, вы не увидите ячейку со значением 900 в результатах поиска, если оно получено с помощью формулы.

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

Аналогично, если вы хотите найти отформатированные числовые значения, вы не можете использовать окно «Найти и заменить». Например, если вы введете $5* в поле поиска, значение с примененным форматированием, которое выглядит как $54.00, не будет найдено.

С датами в Excel работать непросто, поскольку он поддерживает так много форматов дат. Даже если дата отформатирована по-другому, Excel найдет ее, даже если применяется формат по умолчанию. Если в вашей системе используется формат m/d/y, то поисковая строка 10/*/2010 найдет все даты октября 2010 года, независимо от их формата.

Используйте пустое поле Заменить на, чтобы быстро удалить какую-нибудь информацию на рабочем листе. Например, введите — * в поле Найти и оставьте поле Заменить на пустым. Затем нажмите кнопку Заменить все, чтобы Excel нашел и убрал все звездочки на листе.

Функция ВПР в Excel

Функция ВПР в Excel

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

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

Читайте так же:
Как заставить формулу всегда использовать одну и ту же ячейку в Excel?

Таблица Excel

Левая часть таблицы должна содержать цену из прайс-листа справа.

Как создать функцию ВПР в Excel

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

В колонке цены (C2) напишите формулу. Это можно сделать двумя способами:

Выделите ячейку и наберите функцию.

Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

Затем вы сможете заполнить ячейки аргументами формул в новом окне.

аргументы функции ВПР

Синтаксис функции FPC имеет следующий вид

В нашем случае это — формула:

Аргументы ВПР в Эксель

Аргументы функции ВПР

Теперь нам нужно подумать о том, что и где написать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию.

В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.

Функция ВПР, искомое значение

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании.

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

Функция ВПР, таблица

Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат.

Формула сканирует таблицу вертикально.

Находит совпадение с искомым значением в левом столбце.

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

Передаёт данные в ячейку, содержащую формулу.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

Функция ВПР, принцип работы

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

Читайте так же:
Как использовать Stop If True при условном форматировании в Excel?

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

Автозаполнение

В конце потяните формулу вниз, что приведет к автозаполнению.

Автозаполнение ячеек в Excel

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

В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу.

ВПР и приблизительный интервальный просмотр

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

Задача. В магазин поступило несколько товаров. Вам нужно назначить размер партии для каждого товара в зависимости от его количества.

Задача для Excel, ВПР

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Окно для формулы ВПР в Excel

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Сортировка в Excel с помощью ВПР

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает.

Некорректная работа функции ВПР

Итоги

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

Синтаксис функции: =FRP(поисковое значение; таблица; номер столбца; тип интервала).

Функция может быть введена вручную или в специальном окне (Shift+F3).

Искомое значение – относительная ссылка, а таблица – абсолютная.

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

Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector