Вложенные функции Excel (функция ЕСЛИ и функция ВПР)

Итак, мы уже рассмотрели как работает функция Если и функция ВПР. Иногда требуется за один шаг и выполнить какое-либо условие и найти нужное значение в таблице. Рассмотрим простой пример. Допустим у нас есть таблица с данными о цене товара в рублях и в долларах, мы бы хотели не пользуясь фильтрами или поиском по странице выводить стоимость товара по интересующему нас артикулу. Возьмем уже использованную нами таблицу данных:

вложенные функции

таблица данных

Мы хотели бы что бы при вводе в ячейку С111 нужного артикула выводилась цена товара либо в рублях либо в у.е.  Договоримся, что в соседней ячейке Е111 мы будем указывать вручную в каких единицах выводить стоимость (в следующих статьях мы рассмотрим как избавиться от ручных операций и создать например Чекбокс (флажек) или список выбираемых значений) . Итак поиск по артиклу  мы выполняем с помощью функции ВПР:    ВПР(C111;C115:E128;2;0) — поиск по цене в рублях, и  ВПР(C111;C115:E128;3;0) — поиск по цене в у.е. Теперь осталось применить функцию Если для выбора между двумя функциями ВПР, напомним как работает функция Если:  ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь]), а в нашем случае ЕСЛИ(условие; [ВПР 1 ]; [ВПР 2]), получили вложенные функции:

=ЕСЛИ(E111="рубли";ВПР(C111;C115:E128;2;0);ВПР(C111;C115:E128;3;0)) 

 

пример вложенных функций

Готовая формула

Таким образом мы получили готовую форму, с двумя условиями: артикул, валюта.

 

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

Таблица готова

 

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

Поделиться:
23 Комментария
  • Lex95:

    вложение  -ппц.xlsx

    Помогите пожалуйста, я просто не могу понять 2 часть формулы, все перепробовал, исчитал, не знаю и все (1 впр), условия прилагаю

    • excel:

      Где лист «Общий»? В C1 ничего не написано.

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

      Видимо задача сформулирована неправильно

  • Дана:

    вложение  111111.xlsx

    кто-нибудь, помогите, пожалуйста разобраться:

    С помощью справочных таблиц должна автоматически заполняться итоговая таблица. С помощью функцию Если и ВПР вычислить штраф. Штраф составляет 15% от цены книги и начисляется, если Дата возврата -Дата выдачи больше 15 дней.

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

    Используя расширенный фильтр, отобразить фамилии читателей, заплативших штраф.

  • Рожден:

    вложение  .xlsx

    Здравствуйте! Можете пожалуйста помочь. Как ни пытался, никак не могу сделать так как указано в задании. Мне нужен только один пункт, пункт 3. Я его выделил желтой заливкой. Все ресурсы сделаны и подготовлены, нужно только посчитать столбец «Премия». В задании указано какими именно функциями нужно задать. В В файле два листа. Спасибо!

  • Andrii:

    Добрый день! Возможно ли такое реализовать просто? Есть два столбца: А и Б. В А постоянные значения цен, в Б количество проданых позиций (>0 или =0). В отдельной ячейке необходимо вывести сумму денег по всем продажам.

    Используя функцию СУММЕСЛИ(А1:А10; «>0»;Б1:Б10) все работает хорошо до тех пор пока не появляется 2 и более продажи по одной позиции, где необходимо сначала, например, Б5*А5, а уже потом полученное значение суммировать с остальными продажами.

    • excel:

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

      • Андрей:

        Черт возьми! Действительно все просто! Я думал нужны вложенные функции. просто теперь суммируются и перемножаются и нулевые значения (ячейки где продажи =0), но на небольших массивах данных это ерунда.

        Спасибо!

        • excel:

          Пожалуйста! Ну как бы любые значения умноженные на 0 равно 0, поэтому какие бы объемы не были это не помешает

  • Азиза:

    Здравствуйте!

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

    То же самое, только еще ЕСЛИ по ВПР найден, И ПРОСРОЧКА меньше 7 дней, то рассчитать вознаграждение умножив на 1% от суммы, если по ВПР не найден и просрочка меньше 7 дней, то умножить на 2%. Или так невозможно?

    • excel:

      Вот решение. Если впр найден и просрочка больше 7 дней и если не найден и просрочка больше 7 дней то будет пусто.

      • Азиза:

        Невероятно! Спасибо большое! Значит просто каждое условие нужно было брать в скобки. Спасибо!

  • Азиза:

    Добрый день! Помогите пожалуйста написать формулу.

    Данные в таблице состоят из «Дата», «Номер», «Сумма», и необходимо посчитать «Вознаграждение». Условия такие: если в поле «Дата», имеются значение, то тогда нужно найти по списку значений «Номер», в другом листе. Если Номер имеется на другом листе, то тогда рассчитать Вознаграждение след.образом: умножить «Сумма» на 4%, если Номер не найден, то на 8%.

    • excel:

      Приложите файл, тут ничего сложного нет, но лень писать.

      Нужно использовать с помощью ЕСЛИ проверить ячейку в Дата, если пустая, то возвращаем пусто, Если нет, то ВПР по номеру. Если впр по номеру выдает ошибку (значит, этого номера там нет) и тогда считаем вознаграждение с 4%, если ошибку ВПР не выдал, значит 8%

      • Азиза:

        вложение  -sirexcel-1.xlsx

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

        • excel:

          вложение  -sirexcel-1-1.xlsx

          Вот решение

          • Азиза:

            Огромное спасибо!

          • excel:

            вложение  -sirexcel-1-1-1-1.xlsx

            Действительно. ЕОШ — не подходит в данной случае. Поменяйте на ЕОШИБКА

          • Азиза:

            вложение  -sirexcel-1-1-1.xlsx

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

      • Азиза:

        вложение  -sirexcel.xlsx

        Здорово, моя задача решаема. Приложила файл, пожалуйста посмотрите!

  • Ксения:

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

    Нам надо, чтобы в ячейках С8:С12 находились значения в зависимости от комбинаций условий, которые устанавливаются в ячейках со списком: В5 и С5.

    Например:

    в ячейке В5 выбрана «Германия», а в ячейке С5 стоит «Внутренний тариф». Поиск должен выдать результат

    Рабочее время

    стандарт 10,00

    первый час переработки 11,00

    2й и следующие часы переработки 12,00

    воскресенье 13,00

    праздничный день 14,00

    Если в ячейке В5 выбрана «Франция», а в ячейке С5 стоит «Внешний тариф». Поиск должен выдать результат

    Рабочее время

    стандарт 10,00

    первый час переработки 8,00

    2й и следующие часы переработки 7,00

    воскресенье 5,00

    праздничный день 4,00

    Заранее спасибо за помощь

    • excel:

      Я бы сделал по-другому. Можно и через если, но слишком много вложенных если будет плюс не удобно добавлять новые страны.

      Вот посмотрите во вложении. Делаем доп.столбцы и используем только впр

  • Наталья:

    Отличная статейка!

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.