Вложенные функции 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))

 

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

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

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

 

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

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

 

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

Поделиться:
34 Комментария
  • Геннадий says:

    Вложение  -3-листа.xlsx

    Очень нужные функция и статья!
    Интерсно, а можно ли функцию ВПР использовать с логическим ИЛИ?
    Есть необходимость собирать данные в таблицу, но при это нужно как то сравнить их с такими же данными на других листах этой же книги. Как это сделать в случае с одной таблицей для сравнения разобрался, но попытки сделать сравнение сразу по двум листам не получается. Манипулирую конструкцией вида
    =ИЛИ(ВПР(A1;$A$21:$D$29;4;ЛОЖЬ); [ВПР[A1;Лист2!A1:D9;4;ЛОЖЬ];…)
    но excel постоянно ругается на начало второго логического условия.
    Возможно, в каких то скобках или запятых запутался, мозгов не хватает…
    В идеале конечно хотелось бы, чтобы в соседнем столбце даже не показывалась данная фраза, а просто сразу автоматически удалялась, ну или просто подсвечивалась. Но это. как говорится, вопрос уже второй…
    Буду признателен, если поможете…

    • excel says:

      Не совсем понятна задача. Нужно возращать значение если хотя бы на каком-то листе есть совпадение?
      Сколько листов макс. может быть?

      • Геннадий says:

        Если совпадение фразы есть, значит эта фраза в этой таблице лишняя. Цель – добиться уникальности фраз по всей книге. Значение в идеале можно не возвращать, просто удалить эту фразу. Но если иначе эта фунция не работает, то все равно, можно и чего то вывести или подсветить, потом это достаточно просто можно будет удалить простой сортировкой.
        Количество листов не принципиально, главное чтобы хотя бы на двух заработало, дальше уже думаю, понятно будет.

        • Геннадий says:

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

  • Anna says:

    Подскажите, пожалуйста, ломаю голову над вложенной формулой.
    Есть две таблицы, в Таблице1 даны ячейки:
    Номера заказов, Заказчики, Комплектность отгрузки (“все” или “часть”)
    Есть Таблица2, с другими данными, которые я подтягиваю из Таблицы 1, а также в которую мне нужно подтянуть Комплектность отгрузки (“все” или “часть”).
    Мне нужно сделать, чтобы в Таблице2 подтягивало по номеру заказу (а они все разные в Таблице 1), отгрузила я “все” или “часть”….проблема в том, что один заказ в Таблице 1 может встречаться несколько раз, смотря сколько раз я его отгружала и может стоять “часть”, “часть”, “все”…и к сожалению итогувую “все” ВПР не подтягивает…оно подтягивает одно значение за раз.
    Какую вложенную формулу использовать?
    Или как на моем примере использовать ИНДЕКС, где постоянные значения могут быть или “все” или “часть.”
    Буду премного благодарна за помощь.

    • excel says:

      Не очень понятно без примера

  • Андрей says:

    А такую задачу можно решить похожим способом? Есть закупка товара. Предположим мы купили товар А (в ID 1053) по цене 9000 руб. товар Б (в ID 1003) по цене 19000 руб 10.05 (10-го мая)
    Спустя неделю 22.05 (22-го мая) мы купили товар А по цене 8000 руб. товар Б по цене 15000 руб
    Нам нужно отследить конечную стоимость этого товара, для этого мы сделали уникальный ID для каждого товара.
    ID товара А: YP051071053001 купили 10.05
    ID товара Б: YP051071003001 купили 10.05,
    где
    YP – название фирмы,
    05107 – дата покупки, чтобы понимать за сколько купили этот товар (05 месяц, 10- число, 7 – год)
    1003 – тип товара
    001 – номер модели (предположим в этой партии было 15 штук, значит последние цифры будут от 001 до 015)

    Задача.
    Есть ряд ID,
    YP051071053001
    YP051071003001
    YP051071053002
    YP052271053001
    YP051071053003
    YP052271053002
    YP052271003001

    YP051071053043
    Нужно понять по каждому ID какая была закупочная цена этого товара.

    • excel says:

      Сделайте доп. столбец, где из YP052271053001 вытащите последние цифры дату и id
      Далее там где цена нужно сделать доп. столбец, где объединить цену и id.
      Все теперь можно по ВПР подтягивать данные по дате и id

  • Марина says:

    Вложение  _Карта.xlsx

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

  • Lex95 says:

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

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

    • excel says:

      Где лист “Общий”? В C1 ничего не написано.
      В данной таблице в примечании напротив “осветительные сети производственных зданий” нет необходимости использоваться ВПР так как год книги итак указав в ячейке D11.
      Видимо задача сформулирована неправильно

  • Дана says:

    Вложение  111111.xlsx

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

  • Рожден says:

    Вложение  .xlsx

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

  • Andrii says:

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

    • excel says:

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

      • Андрей says:

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

        • excel says:

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

  • Азиза says:

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

    • excel says:

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

      • Азиза says:

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

  • Азиза says:

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

    • excel says:

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

      • Азиза says:

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

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

        • excel says:

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

          Вот решение

          • Азиза says:

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

          • excel says:

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

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

          • Азиза says:

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

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

      • Азиза says:

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

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

  • Ксения says:

    Добрый день. Больше спасибо за статью! Помогите, пожалуйста, разобраться с моим случаем и вложить функцию поиска ЕСЛИ в ВПР
    Нам надо, чтобы в ячейках С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 says:

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

  • Наталья says:

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

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

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

×
Рекомендуем посмотреть