Поиск цены товара, которая действует на дату отгрузки. Если в течении одного дня имеем две цены, то брать последнюю цену.
На форуме нашел пример с функцией ИНДЕКС. Но хотелось бы усовершенствовать формулу. 1) Сделать ее само-протягивающейся, т.к. реестр движений ТМЦ будет автоматически пополняться данными с других листов с помощью функции QUERY. Знаю, что формула ИНДЕКС не может быть формулой массива и формулу придется переделать.
2) Убрать необходимость сортировать исходный диапазон по дате. Сейчас этого требует формула
Поиск цены товара, которая действует на дату отгрузки. Если в течении одного дня имеем две цены, то брать последнюю цену.
На форуме нашел пример с функцией ИНДЕКС. Но хотелось бы усовершенствовать формулу. 1) Сделать ее само-протягивающейся, т.к. реестр движений ТМЦ будет автоматически пополняться данными с других листов с помощью функции QUERY. Знаю, что формула ИНДЕКС не может быть формулой массива и формулу придется переделать.
2) Убрать необходимость сортировать исходный диапазон по дате. Сейчас этого требует формула
Можно функцию XLOOKUP использовать. Она умеет искать строки от конца к началу (см. ниже последний параметр -1). Кроме того, из нее получаются саморазворачивающиеся формулы, которые вводятся в одну самую верхнюю ячейку диапазона (ячейки под ней должны быть свободны). Для ячейки N2 формула может выглядеть так (ячейки N3 и ниже - очистить!):
Можно функцию XLOOKUP использовать. Она умеет искать строки от конца к началу (см. ниже последний параметр -1). Кроме того, из нее получаются саморазворачивающиеся формулы, которые вводятся в одну самую верхнюю ячейку диапазона (ячейки под ней должны быть свободны). Для ячейки N2 формула может выглядеть так (ячейки N3 и ниже - очистить!):
Gustav, спасибо за отклик. Верно ли я понял, что функция XLOOKUP ищет только точное соответствие? В примере на скрин-шоте: 14 декабря швабры уже были в наличии, но формула не находит цену.
Gustav, спасибо за отклик. Верно ли я понял, что функция XLOOKUP ищет только точное соответствие? В примере на скрин-шоте: 14 декабря швабры уже были в наличии, но формула не находит цену.book
Верно ли я понял, что функция XLOOKUP ищет только точное соответствие?
Не совсем так. Вообще "точность" управляется предпоследним параметром функции:
Цитата
match_mode – [НЕОБЯЗАТЕЛЬНО, по умолчанию 0] – режим поиска совпадения для аргумента search_key. 0 – точное соответствие. 1 – точное соответствие или ближайшее значение, которое больше search_key. -1 – точное соответствие или ближайшее значение, которое меньше search_key. 2 – совпадение с подстановочным знаком.
У нас сейчас выставлено 0, и, по идее, вроде бы, для получения желаемого поведения нужно этот 0 заменить на -1, но в данном случае это не спасет (результаты будут не всегда правильные). Почему? Потому что у нас сейчас используются слитые (конкатенацией) массивы из нескольких сцепленных колонок (стандартный приём, часто используемый в Excel c функциями ВПР или ПОИСКПОЗ). При поиске точных совпадений это работает нормально, без вариантов. Но при использовании неточных (предыдущих или последующих) значений, это срабатывает не всегда, поскольку соседние строки в таблице могут относиться к разным номенклатурам и к "швабрам" могут "присоседиться", например, "ведра", что для поиска нежелательно и просто неправильно (можно поисследовать самостоятельно).
Не вдаваясь в некоторые другие подробности, приведу новую формулу для ячейки N2 (на этот раз она, увы, не саморазворачивающаяся): [vba]
Код
=XLOOKUP(L2; FILTER(A:A;C:C=M2); FILTER(E:E;C:C=M2); "не было поступлений"; -1; -1)
[/vba] А раз она не саморазворачивающаяся, то ее надо протянуть (скопировать) ниже по столбцу, начиная с ячейки N3.
Ну, а чтобы вернуться вновь к саморазворачивающемуся варианту, можно преобразовать предыдущую формулу в динамически-массивную, используя функцию MAP: [vba]
[/vba] Эту формулы, как и самые первые (из сообщения № 2), нужно поместить в одну единственную ячейку N2, очистив ячейки ниже.
[p.s.]Можно обойтись и без XLOOKUP, погрузив все условия в функцию FILTER:[/p.s.] а) вариант отдельной протягиваемой формулы для каждой ячейки диапазона, начиная с N2: [vba]
Код
=CHOOSEROWS( IFNA(FILTER(E:E; A:A<=L2; C:C=M2); "не было поступлений"); -1)
[/vba] CHOOSEROWS(массив, -1) выбирает из массива первую строку с конца, т.е. последнюю строку с начала.
б) динамически-массивный вариант - с одной единственной саморасширяющейся формулой для ячейки N2: [vba]
Верно ли я понял, что функция XLOOKUP ищет только точное соответствие?
Не совсем так. Вообще "точность" управляется предпоследним параметром функции:
Цитата
match_mode – [НЕОБЯЗАТЕЛЬНО, по умолчанию 0] – режим поиска совпадения для аргумента search_key. 0 – точное соответствие. 1 – точное соответствие или ближайшее значение, которое больше search_key. -1 – точное соответствие или ближайшее значение, которое меньше search_key. 2 – совпадение с подстановочным знаком.
У нас сейчас выставлено 0, и, по идее, вроде бы, для получения желаемого поведения нужно этот 0 заменить на -1, но в данном случае это не спасет (результаты будут не всегда правильные). Почему? Потому что у нас сейчас используются слитые (конкатенацией) массивы из нескольких сцепленных колонок (стандартный приём, часто используемый в Excel c функциями ВПР или ПОИСКПОЗ). При поиске точных совпадений это работает нормально, без вариантов. Но при использовании неточных (предыдущих или последующих) значений, это срабатывает не всегда, поскольку соседние строки в таблице могут относиться к разным номенклатурам и к "швабрам" могут "присоседиться", например, "ведра", что для поиска нежелательно и просто неправильно (можно поисследовать самостоятельно).
Не вдаваясь в некоторые другие подробности, приведу новую формулу для ячейки N2 (на этот раз она, увы, не саморазворачивающаяся): [vba]
Код
=XLOOKUP(L2; FILTER(A:A;C:C=M2); FILTER(E:E;C:C=M2); "не было поступлений"; -1; -1)
[/vba] А раз она не саморазворачивающаяся, то ее надо протянуть (скопировать) ниже по столбцу, начиная с ячейки N3.
Ну, а чтобы вернуться вновь к саморазворачивающемуся варианту, можно преобразовать предыдущую формулу в динамически-массивную, используя функцию MAP: [vba]
[/vba] Эту формулы, как и самые первые (из сообщения № 2), нужно поместить в одну единственную ячейку N2, очистив ячейки ниже.
[p.s.]Можно обойтись и без XLOOKUP, погрузив все условия в функцию FILTER:[/p.s.] а) вариант отдельной протягиваемой формулы для каждой ячейки диапазона, начиная с N2: [vba]
Код
=CHOOSEROWS( IFNA(FILTER(E:E; A:A<=L2; C:C=M2); "не было поступлений"); -1)
[/vba] CHOOSEROWS(массив, -1) выбирает из массива первую строку с конца, т.е. последнюю строку с начала.
б) динамически-массивный вариант - с одной единственной саморасширяющейся формулой для ячейки N2: [vba]
Gustav, большое спасибо! Не только за два варианта формулы, но и за подробные объяснения.
К сожалению, у меня не получилось применить эти формулы. Все варианты берут первое значение цены. Необходимо выводить последнюю цену, если в течение дня она менялась. В примере – это цена швабры на 2 марта.
Подскажите, пожалуйста, в чем тут дело?
Gustav, большое спасибо! Не только за два варианта формулы, но и за подробные объяснения.
К сожалению, у меня не получилось применить эти формулы. Все варианты берут первое значение цены. Необходимо выводить последнюю цену, если в течение дня она менялась. В примере – это цена швабры на 2 марта.
Я почему-то (уж не знаю почему) с самого начала ориентировался (получается, ошибочно) на колонку E, которая так и озаглавлена "Цена". А Вам, судя по всему, нужна колонка J с заголовком "СредЦенаНарастИтог". Ну, так замените самостоятельно во всех формулах ссылку E:E на J:J. Делов-то!
Я почему-то (уж не знаю почему) с самого начала ориентировался (получается, ошибочно) на колонку E, которая так и озаглавлена "Цена". А Вам, судя по всему, нужна колонка J с заголовком "СредЦенаНарастИтог". Ну, так замените самостоятельно во всех формулах ссылку E:E на J:J. Делов-то!Gustav
замените самостоятельно во всех формулах ссылку E:E на J:J.
«А ларчик просто открывался!»
Спасибо еще раз!
PS Какую формулу, на Ваш взгляд, лучше использовать? CHOOSEROWS или ПРОСМОТРX? Внешне формула с CHOOSEROWS формирует внизу пустые ячейки, а формула с ПРОСМОТРX – выводит «не было поступлений». Это видно в файле по ссылке выше.
замените самостоятельно во всех формулах ссылку E:E на J:J.
«А ларчик просто открывался!»
Спасибо еще раз!
PS Какую формулу, на Ваш взгляд, лучше использовать? CHOOSEROWS или ПРОСМОТРX? Внешне формула с CHOOSEROWS формирует внизу пустые ячейки, а формула с ПРОСМОТРX – выводит «не было поступлений». Это видно в файле по ссылке выше.book
Внешне формула с CHOOSEROWS формирует внизу пустые ячейки, а формула с ПРОСМОТРX – выводит «не было поступлений»
Вместо полностью открытых диапазонов, для которых формула вынуждена разворачиваться до самого конца таблицы (что обычно выглядит не очень опрятно): [vba]
Код
L2:L и M2:M
[/vba] используйте динамические саморасширяющиеся диапазоны - в зависимости от текущего кол-ва дат, введенных в колонку L: [vba]
Код
L2:INDEX(L2:L; COUNTA(L2:L)) и M2:INDEX(M2:M; COUNTA(L2:L))
[/vba]
Тогда самую последнюю из предыдущих формул можно записать как: [vba]
[/vba] По мере добавления в колонки L и M новых дат и номенклатуры в колонке N будут автоматически появляться новые значения для них.
ВАЖНО: Понятно, что для корректного разворачивания формулы в колонке N ячейки в колонке L (даты) должны заполняться подряд, без пустых ячеек. А также необходимо исключить наличие случайных записей (например, каких-то поясняющих комментариев) вниз по колонке L до нижнего края таблицы. Иными словами, в диапазоне L2:L должны быть только даты, идущие вниз, начиная с ячейки L2 и без пустых ячеек между ними. Пустые ячейки в этой колонке могут быть только ниже самой последней даты.
Да, всё это немножко громоздко выглядит, но с формулой можно поработать и вынести, например, повторяющееся вычисление COUNTA(L2:L) в отдельную ячейку, скажем, в O1: [vba]
Код
=COUNTA(L2:L)
[/vba] и использовать в основной формуле ссылку на нее: [vba]
Наконец, можно использовать функцию LET и организовать предварительные вычисления диапазонов прямо внутри формулы, не задействуя соседние вспомогательные ячейки (типа O1): [vba]
Внешне формула с CHOOSEROWS формирует внизу пустые ячейки, а формула с ПРОСМОТРX – выводит «не было поступлений»
Вместо полностью открытых диапазонов, для которых формула вынуждена разворачиваться до самого конца таблицы (что обычно выглядит не очень опрятно): [vba]
Код
L2:L и M2:M
[/vba] используйте динамические саморасширяющиеся диапазоны - в зависимости от текущего кол-ва дат, введенных в колонку L: [vba]
Код
L2:INDEX(L2:L; COUNTA(L2:L)) и M2:INDEX(M2:M; COUNTA(L2:L))
[/vba]
Тогда самую последнюю из предыдущих формул можно записать как: [vba]
[/vba] По мере добавления в колонки L и M новых дат и номенклатуры в колонке N будут автоматически появляться новые значения для них.
ВАЖНО: Понятно, что для корректного разворачивания формулы в колонке N ячейки в колонке L (даты) должны заполняться подряд, без пустых ячеек. А также необходимо исключить наличие случайных записей (например, каких-то поясняющих комментариев) вниз по колонке L до нижнего края таблицы. Иными словами, в диапазоне L2:L должны быть только даты, идущие вниз, начиная с ячейки L2 и без пустых ячеек между ними. Пустые ячейки в этой колонке могут быть только ниже самой последней даты.
Да, всё это немножко громоздко выглядит, но с формулой можно поработать и вынести, например, повторяющееся вычисление COUNTA(L2:L) в отдельную ячейку, скажем, в O1: [vba]
Код
=COUNTA(L2:L)
[/vba] и использовать в основной формуле ссылку на нее: [vba]
Наконец, можно использовать функцию LET и организовать предварительные вычисления диапазонов прямо внутри формулы, не задействуя соседние вспомогательные ячейки (типа O1): [vba]
[/vba] Эта формула вводится в одну единственную ячейку N2 (прямо копируется отсюда вот в таком буквальном, "сформатированном" переводами строк, виде).Gustav
для корректного разворачивания формулы в колонке N ячейки в колонке L (даты) должны заполняться подряд, без пустых ячеек. А также необходимо исключить наличие случайных записей
Да, это понятно. Планирую, что в рабочем файле диапазоны A:J и L:M (расположенные даже, может быть, на разных листах) будут формироваться с помощью функции QUERY и эти условия удастся выполнить.
И все же, подскажите, пожалуйста, в чем разница между CHOOSEROWS и ПРОСМОТРX. В каких случаях, какую формулу лучше использовать?
для корректного разворачивания формулы в колонке N ячейки в колонке L (даты) должны заполняться подряд, без пустых ячеек. А также необходимо исключить наличие случайных записей
Да, это понятно. Планирую, что в рабочем файле диапазоны A:J и L:M (расположенные даже, может быть, на разных листах) будут формироваться с помощью функции QUERY и эти условия удастся выполнить.
И все же, подскажите, пожалуйста, в чем разница между CHOOSEROWS и ПРОСМОТРX. В каких случаях, какую формулу лучше использовать?
в чем разница между CHOOSEROWS и ПРОСМОТРX. В каких случаях, какую формулу лучше использовать?
ПРОСМОТРX можно использовать при точном или приближенном поиске по одной физической колонке. Или при точном (и только точном!) поиске по одной виртуальной "колонке", полученной конкатенацией нескольких физических колонок.
Лично я предпочитаю использовать FILTER (т.е. вариант с CHOOSEROWS среди формул этой темы). В FILTER можно указать любое кол-во условий, в том числе и явные условия типа ИЛИ. Причем условия указываются в более естественном, что ли, виде: например, мне приятнее смотреть и воспринимать условие A:A<=_x, чем запоминать, в каком параметре ПРОСМОТРX мне нужно поставить -1 для достижения того же эффекта.
О технических (типа быстродействия) характеристиках обоих подходов судить не буду. Во-первых, не знаю и знать особо не хочу, а во-вторых, мне важнее именно ясность формулы.
в чем разница между CHOOSEROWS и ПРОСМОТРX. В каких случаях, какую формулу лучше использовать?
ПРОСМОТРX можно использовать при точном или приближенном поиске по одной физической колонке. Или при точном (и только точном!) поиске по одной виртуальной "колонке", полученной конкатенацией нескольких физических колонок.
Лично я предпочитаю использовать FILTER (т.е. вариант с CHOOSEROWS среди формул этой темы). В FILTER можно указать любое кол-во условий, в том числе и явные условия типа ИЛИ. Причем условия указываются в более естественном, что ли, виде: например, мне приятнее смотреть и воспринимать условие A:A<=_x, чем запоминать, в каком параметре ПРОСМОТРX мне нужно поставить -1 для достижения того же эффекта.
О технических (типа быстродействия) характеристиках обоих подходов судить не буду. Во-первых, не знаю и знать особо не хочу, а во-вторых, мне важнее именно ясность формулы.Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
