Вложенные функции 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))
Готовая формула
Таким образом мы получили готовую форму, с двумя условиями: артикул, валюта.
Таблица готова
Если статья была Вам полезна, пожалуйста, поделитесь ей со своими друзьями с помощью кнопок расположенных ниже.
Вложение -3-листа.xlsx
Очень нужные функция и статья!
Интерсно, а можно ли функцию ВПР использовать с логическим ИЛИ?
Есть необходимость собирать данные в таблицу, но при это нужно как то сравнить их с такими же данными на других листах этой же книги. Как это сделать в случае с одной таблицей для сравнения разобрался, но попытки сделать сравнение сразу по двум листам не получается. Манипулирую конструкцией вида
=ИЛИ(ВПР(A1;$A$21:$D$29;4;ЛОЖЬ); [ВПР[A1;Лист2!A1:D9;4;ЛОЖЬ];…)
но excel постоянно ругается на начало второго логического условия.
Возможно, в каких то скобках или запятых запутался, мозгов не хватает…
В идеале конечно хотелось бы, чтобы в соседнем столбце даже не показывалась данная фраза, а просто сразу автоматически удалялась, ну или просто подсвечивалась. Но это. как говорится, вопрос уже второй…
Буду признателен, если поможете…
Не совсем понятна задача. Нужно возращать значение если хотя бы на каком-то листе есть совпадение?
Сколько листов макс. может быть?
Если совпадение фразы есть, значит эта фраза в этой таблице лишняя. Цель – добиться уникальности фраз по всей книге. Значение в идеале можно не возвращать, просто удалить эту фразу. Но если иначе эта фунция не работает, то все равно, можно и чего то вывести или подсветить, потом это достаточно просто можно будет удалить простой сортировкой.
Количество листов не принципиально, главное чтобы хотя бы на двух заработало, дальше уже думаю, понятно будет.
Простите, немного туманно выразился. На двух листах итак работает, имеется ввиду проверку на двух и более листах, не считая первого, куда вводятся данные
Подскажите, пожалуйста, ломаю голову над вложенной формулой.
Есть две таблицы, в Таблице1 даны ячейки:
Номера заказов, Заказчики, Комплектность отгрузки (“все” или “часть”)
Есть Таблица2, с другими данными, которые я подтягиваю из Таблицы 1, а также в которую мне нужно подтянуть Комплектность отгрузки (“все” или “часть”).
Мне нужно сделать, чтобы в Таблице2 подтягивало по номеру заказу (а они все разные в Таблице 1), отгрузила я “все” или “часть”….проблема в том, что один заказ в Таблице 1 может встречаться несколько раз, смотря сколько раз я его отгружала и может стоять “часть”, “часть”, “все”…и к сожалению итогувую “все” ВПР не подтягивает…оно подтягивает одно значение за раз.
Какую вложенную формулу использовать?
Или как на моем примере использовать ИНДЕКС, где постоянные значения могут быть или “все” или “часть.”
Буду премного благодарна за помощь.
Не очень понятно без примера
А такую задачу можно решить похожим способом? Есть закупка товара. Предположим мы купили товар А (в 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 какая была закупочная цена этого товара.
Сделайте доп. столбец, где из YP052271053001 вытащите последние цифры дату и id
Далее там где цена нужно сделать доп. столбец, где объединить цену и id.
Все теперь можно по ВПР подтягивать данные по дате и id
Вложение _Карта.xlsx
Добрый день. Помогите, пож.: нужно что бы при указании даты в одном столбце (в левой таблице) закрашивалась нужная ячейка в календарном графике (правая таблица).
Спасибо большое за помощь!
Вложение
Это можно сделать с помощью условному форматированию по условию. Вот по этому уроку:
https://sirexcel.ru/formaty-i-otobrazhenie/uslovnoe-formatirovanie-po-usloviyam-v-drugix-yachejkax-formulami-v-excel/
В вашем случае для простоты, в 4-й строке лучше поставить именно даты а не цифры. Я сделал на примере января, вам нужно февраль тоже переделать. Можно и так думаю придумать формулу, но геморно. Легче даты поменять.
Вложение _Карта-1.xlsx
вот и сам файл
Вложение -ппц.xlsx
Помогите пожалуйста, я просто не могу понять 2 часть формулы, все перепробовал, исчитал, не знаю и все (1 впр), условия прилагаю
Где лист “Общий”? В C1 ничего не написано.
В данной таблице в примечании напротив “осветительные сети производственных зданий” нет необходимости использоваться ВПР так как год книги итак указав в ячейке D11.
Видимо задача сформулирована неправильно
Вложение 111111.xlsx
кто-нибудь, помогите, пожалуйста разобраться:
С помощью справочных таблиц должна автоматически заполняться итоговая таблица. С помощью функцию Если и ВПР вычислить штраф. Штраф составляет 15% от цены книги и начисляется, если Дата возврата -Дата выдачи больше 15 дней.
Построить объемную столбиковую диаграмму количества книг по авторам, автоматически корректируемую при изменении исходной таблицы (функция Суммесли).
Используя расширенный фильтр, отобразить фамилии читателей, заплативших штраф.
Вложение .xlsx
Здравствуйте! Можете пожалуйста помочь. Как ни пытался, никак не могу сделать так как указано в задании. Мне нужен только один пункт, пункт 3. Я его выделил желтой заливкой. Все ресурсы сделаны и подготовлены, нужно только посчитать столбец “Премия”. В задании указано какими именно функциями нужно задать. В В файле два листа. Спасибо!
Вложение -1.xlsx
Вот решение задачи
Добрый день! Возможно ли такое реализовать просто? Есть два столбца: А и Б. В А постоянные значения цен, в Б количество проданых позиций (>0 или =0). В отдельной ячейке необходимо вывести сумму денег по всем продажам.
Используя функцию СУММЕСЛИ(А1:А10;”>0″;Б1:Б10) все работает хорошо до тех пор пока не появляется 2 и более продажи по одной позиции, где необходимо сначала, например, Б5*А5, а уже потом полученное значение суммировать с остальными продажами.
Используйте функцию СУММПРОИЗВ в качестве аргументов указываете столбец А и столбец Б. Функция сначала все перемножит, а потом просуммирует.
Черт возьми! Действительно все просто! Я думал нужны вложенные функции. просто теперь суммируются и перемножаются и нулевые значения (ячейки где продажи =0), но на небольших массивах данных это ерунда.
Спасибо!
Пожалуйста! Ну как бы любые значения умноженные на 0 равно 0, поэтому какие бы объемы не были это не помешает
Вложение -sirexcel-c-дополнительным-условием.xlsx
Здравствуйте!
Вы невероятно выручили с предыдущей задачей, но у меня сейчас появилось к предыдущей формуле еще одно дополнительное условие. Скажите пожалуйста, его можно будет добавить?
То же самое, только еще ЕСЛИ по ВПР найден, И ПРОСРОЧКА меньше 7 дней, то рассчитать вознаграждение умножив на 1% от суммы, если по ВПР не найден и просрочка меньше 7 дней, то умножить на 2%. Или так невозможно?
Вложение -sirexcel-c-дополнительным-условием-1.xlsx
Вот решение. Если впр найден и просрочка больше 7 дней и если не найден и просрочка больше 7 дней то будет пусто.
Невероятно! Спасибо большое! Значит просто каждое условие нужно было брать в скобки. Спасибо!
Добрый день! Помогите пожалуйста написать формулу.
Данные в таблице состоят из «Дата», «Номер», «Сумма», и необходимо посчитать «Вознаграждение». Условия такие: если в поле «Дата», имеются значение, то тогда нужно найти по списку значений «Номер», в другом листе. Если Номер имеется на другом листе, то тогда рассчитать Вознаграждение след.образом: умножить «Сумма» на 4%,если Номер не найден, то на 8%.
Приложите файл, тут ничего сложного нет, но лень писать.
Нужно использовать с помощью ЕСЛИ проверить ячейку в Дата, если пустая, то возвращаем пусто, Если нет, то ВПР по номеру. Если впр по номеру выдает ошибку (значит, этого номера там нет) и тогда считаем вознаграждение с 4%, если ошибку ВПР не выдал, значит 8%
Вложение -sirexcel-1.xlsx
Пожалуйста помогите как рассчитать в данном случае вознаграждение?
Вложение -sirexcel-1-1.xlsx
Вот решение
Огромное спасибо!
Вложение -sirexcel-1-1-1-1.xlsx
Действительно. ЕОШ – не подходит в данной случае. Поменяйте на ЕОШИБКА
Вложение -sirexcel-1-1-1.xlsx
Кажется тут формула не дописана, потому что расчет вознаграждения идет как для найденных так и не найденных по списку по четырем процентам. Можете посмотреть пожалуйста, где ошибка?
Вложение -sirexcel.xlsx
Здорово, моя задача решаема. Приложила файл, пожалуйста посмотрите!
Вложение Montagekosten-Excel-Formel.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
Заранее спасибо за помощь
Вложение Montagekosten-Excel-Formel-1.xlsx
Я бы сделал по-другому. Можно и через если, но слишком много вложенных если будет плюс не удобно добавлять новые страны.
Вот посмотрите во вложении. Делаем доп.столбцы и используем только впр
Отличная статейка!