Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск, суммирование, подстановка по нескольким условиям с подтягиванием ближайшей даты, ВПР и СУММЕСЛИ не подходят
 
#1
11.12.2024 17:33:57
Коллеги, добрый день. Не нашел решения подскажите. Стандартные функции ВПР и СУММЕСЛИ не подходят.
Есть массив данных (в приложенном примере лист "Данные"):
- Код товара - уникальный идентификатор.
- Кол-во товара.
- Дата отправки (возможные форматы значений: текстовый "НЕТ", текстовый "*" (означает товар на отгрузке, то есть в транзите), и собственно Дата)
- Дата поступления на склад.

Если отфильтровать исходную таблицу например по коду УП-00002960 (в приложенном примере лист "Данные"):
Видно что по этому коду есть 4 партии товара:
- 2 партии в транзите (по колонке "Дата отправки" имеют запись в ячейке "Дата" и "*").
- 2 партии в производстве (по колонке "Дата отправки" имеют запись в ячейке "НЕТ").
Все партии с разными датами поступления на склад.

Задача сделать сводную таблицу (в приложенном примере лист "Сводная"), где в строке по коду товара автоматически выполнялся бы расчет и подстановка:
1. Товар в транзите общее количество, шт. (определить что надо суммировать значения у которых по колонке "Дата отправки" имеется запись в ячейке "Дата" и "*" и посчитать сумму.).
2. Товар в транзите ближайшая партия, шт. (из значений определенных как "транзит", подставить количество по партии с ближайшей датой поступления на склад.).
3. Поступление из транзита на склад МСК ближайшая партия, дата (определить и подставить дату поступления ближайшей партии из транзита).
4. Товар в производстве общее количество, шт. (определить, что надо суммировать значения у которых по колонке "Дата отправки" имеется запись в ячейке "НЕТ" и посчитать сумму.).
5. Товар в производстве ближайшая партия, шт. (из значений определенных как "производство", подставить количество по партии с ближайшей датой поступления на склад.).
6. Поступление из производства на склад МСК ближайшая партия, дата (определить и подставить дату поступления ближайшей партии из производства).

Заранее, искренне благодарю за помощь!!!
Изменено: andrew.efc - 11.12.2024 17:35:10
 
 
 
#2
11.12.2024 23:15:20
andrew.efc, добрый вечер. PQ+PP
 
 
 
#3
12.12.2024 11:07:29
Интересный подход, но хотелось бы результата достичь через формулы и результат получить в простой таблице, а не сводной типовой. Потому что из этого массива необходима информация только по отдельным кодам или группам кодов. Будем думать, вопрос пока не решен.
 
 
 
#4
12.12.2024 11:47:58
Если формулой массива (Ctrl-Enter), то можно менять Массив!$C:$C на нужный столбец:
=ИНДЕКС(Массив!$C:$C;ПОИСКПОЗ(1;(Массив!$A:$A=$A4)*(Массив!$C:$C<>"Нет");0))
 
 
 
#5
12.12.2024 12:19:15
Во вложении
 
 
 
#6
12.12.2024 12:32:20
Вот без формулы массива (лучше избегать):
Ячейка B4 =СУММЕСЛИМН(Массив!$B:$B;Массив!$A:$A;$A4;Массив!$C:$C;"<>"&"НЕТ")
Ячейка C4 =ИНДЕКС(Массив!B:B;АГРЕГАТ(15;6;СТРОКА(Массив!$A$1:$A$500)/(Массив!$A$1:$A$500=$A4)/(Массив!$C$1:$C$500=$D4);1))
Ячейка D4 =АГРЕГАТ(15;6;(Массив!$C$1:$C$500)/(Массив!$A$1:$A$500=$A4)/(Массив!$C$1:$C$500<>"Нет");1)
Ячейка E4 =СУММЕСЛИМН(Массив!$B:$B;Массив!$A:$A;$A4;Массив!$C:$C;"НЕТ")
Ячейка F4 =ИНДЕКС(Массив!$B:$B;АГРЕГАТ(15;6;СТРОКА(Массив!$A$1:$A$500)/(Массив!$A$1:$A$500=$A4)/(Массив!$D$1:$D$500=$G4);1))
Ячейка G4 =АГРЕГАТ(15;6;(Массив!$D$1:$D$500)/(Массив!$A$1:$A$500=$A4)/(Массив!$C$1:$C$500="Нет");1)

Файл приложить не могу из-за корп.ограничений.
Изменено: Artur B. - 12.12.2024 14:26:54
 
 
 
#7
12.12.2024 14:59:31
Цитата
написал:
Во вложении

Прикрепленные файлы
Массив данных.xlsx  (25.75 КБ)
Формулы массива работают. Alex спасибо!
 
 
 
#8
12.12.2024 15:05:49
Цитата
написал:
Вот без формулы массива (лучше избегать):Ячейка B4 =СУММЕСЛИМН(Массив!$B:$B;Массив!$A:$A;$A4;Массив!$C:$C;"<>"&"НЕТ")Ячейка C4 =ИНДЕКС(Массив!B:B;АГРЕГАТ(15;6;СТРОКА(Массив!$A$1:$A$500)/(Массив!$A$1:$A$500=$A4)/(Массив!$C$1:$C$500=$D4);1))Ячейка D4 =АГРЕГАТ(15;6;(Массив!$C$1:$C$500)/(Массив!$A$1:$A$500=$A4)/(Массив!$C$1:$C$500<>"Нет");1)Ячейка E4 =СУММЕСЛИМН(Массив!$B:$B;Массив!$A:$A;$A4;Массив!$C:$C;"НЕТ")Ячейка F4 =ИНДЕКС(Массив!$B:$B;АГРЕГАТ(15;6;СТРОКА(Массив!$A$1:$A$500)/(Массив!$A$1:$A$500=$A4)/(Массив!$D$1:$D$500=$G4);1))Ячейка G4 =АГРЕГАТ(15;6;(Массив!$D$1:$D$500)/(Массив!$A$1:$A$500=$A4)/(Массив!$C$1:$C$500="Нет");1)Файл приложить не могу из-за корп.ограничений.
Artur - спасибо все формулы рабочие!
Изменено: andrew.efc - 12.12.2024 16:24:58
 
 
 
#9
12.12.2024 16:07:04
Извиняюсь, некорректно понял, какая дата интересует. Теперь ищет дату в столбце Поступления на склад.
Ячейка D4 =АГРЕГАТ(15;6;(Массив!$D$1:$D$500)/(Массив!$A$1:$A$500=$A4)/(Массив!$C$1:$C$500<>"Нет");1)
Ячейка C4 =ИНДЕКС(Данные!B:B;АГРЕГАТ(15;6;СТРОКА(Данные!$A$1:$A$500)/(Данные!$A$1:$A$500=$A4)/(Данные!$D$1:$D$500=$D4);1))
А здесь условие добавил на неотправленные:
Ячейка F4 =ИНДЕКС(Данные!$B:$B;АГРЕГАТ(15;6;СТРОКА(Данные!$A$1:$A$500)/(Данные!$A$1:$A$500=$A6)/(Данные!$C$1:$C$500="Нет")/(Данные!$D$1:$D$500=$G6);1))

Если нужно, поясню смысл:
АГРЕГАТ(15; - поиск наименьшего значения в массиве.
6 - исключение ошибок (ДЕЛ/0)
Далее получаем массив для поиска:

Массив!D$1:D$500 - это массив основных значений, в которых ищем.
/ - для проверки на условие. Если не выполняется, то получаем 0, на который делим и полуем ошибку ДЕЛ/0, которая исключается (см. 6).
Если условие выполняется, то ИСТИНА = 1 и деление на 1 не изменяет нужное значение.
Новые условия добавляются через /.

В конце ;1  - это первое по счету наименьшее.

ИНДЕКС - выборка из массива по номеру строки.
АГРЕГАТ(15;6;СТРОКА - это чтобы получить номера строк в массиве, которые мы также проверяем условиями через /.
Изменено: Artur B. - 12.12.2024 16:18:50
 
 
 
#10
12.12.2024 16:35:50
Цитата
написал:
Если нужно, поясню смысл:АГРЕГАТ(15; - поиск наименьшего значения в массиве.6 - исключение ошибок (ДЕЛ/0)Далее получаем массив для поиска:Массив!D$1:D$500 - это массив основных значений, в которых ищем./ - для проверки на условие. Если не выполняется, то получаем 0, на который делим и полуем ошибку ДЕЛ/0, которая исключается (см. 6).Если условие выполняется, то ИСТИНА = 1 и деление на 1 не изменяет нужное значение.Новые условия добавляются через /. В конце ;1  - это первое по счету наименьшее.ИНДЕКС - выборка из массива по номеру строки.АГРЕГАТ(15;6;СТРОКА - это чтобы получить номера строк в массиве, которые мы также проверяем условиями через /.
Артур спасибо, большое! Все формулы работают! За объяснения - ОТДЕЛЬНЫЙ РЕСПЕКТ! ИСКРЕННЕ БЛАГОДАРЮ!
 
 
 
Страницы: 1
Наверх