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

 

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

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

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

 

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

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

 

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

Поделиться:
28 Комментариев
  • Андрей 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

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

    Спасибо большое за помощь!

    • excel says:

      Вложение

      Это можно сделать с помощью условному форматированию по условию. Вот по этому уроку:

      sirexcel.ru/formaty-i-oto...rmulami-v-excel/

      В вашем случае для простоты, в 4-й строке лучше поставить именно даты а не цифры. Я сделал на примере января, вам нужно февраль тоже переделать. Можно и так думаю придумать формулу, но геморно. Легче даты поменять.

  • 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 не будет опубликован. Все поля обязательны для заполнения.