Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/поиск составляющих по условиям, один из которых процент - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » поиск составляющих по условиям, один из которых процент (Формулы/Formulas)
поиск составляющих по условиям, один из которых процент
visor Дата: Понедельник, 07.05.2018, 21:11 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Всех приветствую!

Прошу помочь в написании. Сижу несколько дней в голову не приходит решение.
Цель: Необходимо излишки товаров максимально перекрыть недогрузами.
Условия:
Товары имеют свой уникальный код, у которого первые четыре это группа - это первое условие, которое должно совпадать
Колонка "критерий 2" сделал специально, так как там ещё много условий - я их пропишу сам без проблем(в данном случае это условие 2 будет буква "j", то есть тупо её пресутствие)
Цена за штуку товара не должна отклонятся более или менее чем на 30%.

По итогу во второй вкладке("Излишки") должны подтянутся недогрузы по условиям выше и с результатом(колонками "код и кол-во товара"), и что самое для меня сложное - "на излишки могут быть несколько составляющих, то есть что бы перекрыть один излишек может быть от одного до примерно 10 товаров. Но не должно превышать кол-во этого излишка" Например 90 штук салата листового, к нему тянется 1 товар с кол-вом 50, а у второго товара недогруз всего 120, но надо подтянуть лишь 40, оставшееся кол-во от 120 может пойти к другим товарам.

Может я плохо изъясняюсь, задавайте непременно уточняющие вопросы! файл прилагаю

Заранее благодарен за помощь
К сообщению приложен файл: 3172195.xlsx(10.6 Kb)
 
Ответить
СообщениеВсех приветствую!

Прошу помочь в написании. Сижу несколько дней в голову не приходит решение.
Цель: Необходимо излишки товаров максимально перекрыть недогрузами.
Условия:
Товары имеют свой уникальный код, у которого первые четыре это группа - это первое условие, которое должно совпадать
Колонка "критерий 2" сделал специально, так как там ещё много условий - я их пропишу сам без проблем(в данном случае это условие 2 будет буква "j", то есть тупо её пресутствие)
Цена за штуку товара не должна отклонятся более или менее чем на 30%.

По итогу во второй вкладке("Излишки") должны подтянутся недогрузы по условиям выше и с результатом(колонками "код и кол-во товара"), и что самое для меня сложное - "на излишки могут быть несколько составляющих, то есть что бы перекрыть один излишек может быть от одного до примерно 10 товаров. Но не должно превышать кол-во этого излишка" Например 90 штук салата листового, к нему тянется 1 товар с кол-вом 50, а у второго товара недогруз всего 120, но надо подтянуть лишь 40, оставшееся кол-во от 120 может пойти к другим товарам.

Может я плохо изъясняюсь, задавайте непременно уточняющие вопросы! файл прилагаю

Заранее благодарен за помощь

Автор - visor
Дата добавления - 07.05.2018 в 21:11
Светлый Дата: Вторник, 08.05.2018, 08:15 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 564
Репутация: 132 ±
Замечаний: 0% ±

Excel 2007
Добрый день!
Да. С объяснениями проблема.
Недогрузы - до чего?
Перекрыть - что?
Тянется товар - кто его тянет? К чему тянет?

Покажите, что конкретно должно получиться. Со всеми недогрузами, подтягиваниями, непревышениями. И отклонениями цены не более 30%.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеДобрый день!
Да. С объяснениями проблема.
Недогрузы - до чего?
Перекрыть - что?
Тянется товар - кто его тянет? К чему тянет?

Покажите, что конкретно должно получиться. Со всеми недогрузами, подтягиваниями, непревышениями. И отклонениями цены не более 30%.

Автор - Светлый
Дата добавления - 08.05.2018 в 08:15
visor Дата: Вторник, 08.05.2018, 11:44 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Прошу прощения вчера жутко не выспался поэтому написал какую-то кашу.

Давайте попробую объяснить сам процесс поэтапно и с примером

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

Этапы в порядке приоритета, на примере товара 151303370205 Салат листовой Фриллис 100г, у которого излишек 120 штук



1. Поиск похожих товаров, простой формулой – «левсимв» 4 знака, тоесть вытаскиваю похожие товары

2. Удовлетворение условию колонки «Критерий 2», если в строчке буква «j», то хорошо(тоже просто, но прошу сделать за меня, так как условий будет очень много, я их сам пропишу.)

3. Теперь самое сложное для меня: В оставшемся списке, поиск товара по цене за штуку с отклонением не больше 30%. Плюс приоритетом будет пользоваться товар с максимально подходящей ценой.

Поэтому к этому товару с кол-вом 120 штук, первым подойдёт товар «151303055969 Салат дуболистный зеленый 1шт», у которого недогруз 30 штук. Остаётся найти ещё 90штук. Смотрим дальше, следующий товар будет «151303305012 Салат листовой в горшочке (Россия) 1шт», у которого недрогруз 111 штук, но нам нужно лишь 90. Поэтому в итоге надо показать что искомый товар мы полностью перекрыли, первого товар забрали полностью, а второго забрали лишь часть. И всё это надо видеть в итоге. Ещё один момент: не обязательно всегда перекрываются все излишки



Как вариант предлагаю итог ввиде как во вложении, не обязательно чётко такой же, я немного шарю в екселе и смогу сам всё переделать.

Добрый день,

Этот файл будет некий шаблон с двумя вкладками, во вкладку «выгрузка» я вставляю все товары недогрузы и излишки, а во вторую вкладку «излишки» буду вставлять только излишки(тупо копипастом из первой вкладки), далее по построенным формулам подтягиваются товары колонками.

Заранее благодарю за помощь !
К сообщению приложен файл: _2.xlsx(14.3 Kb)
 
Ответить
СообщениеПрошу прощения вчера жутко не выспался поэтому написал какую-то кашу.

Давайте попробую объяснить сам процесс поэтапно и с примером

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

Этапы в порядке приоритета, на примере товара 151303370205 Салат листовой Фриллис 100г, у которого излишек 120 штук



1. Поиск похожих товаров, простой формулой – «левсимв» 4 знака, тоесть вытаскиваю похожие товары

2. Удовлетворение условию колонки «Критерий 2», если в строчке буква «j», то хорошо(тоже просто, но прошу сделать за меня, так как условий будет очень много, я их сам пропишу.)

3. Теперь самое сложное для меня: В оставшемся списке, поиск товара по цене за штуку с отклонением не больше 30%. Плюс приоритетом будет пользоваться товар с максимально подходящей ценой.

Поэтому к этому товару с кол-вом 120 штук, первым подойдёт товар «151303055969 Салат дуболистный зеленый 1шт», у которого недогруз 30 штук. Остаётся найти ещё 90штук. Смотрим дальше, следующий товар будет «151303305012 Салат листовой в горшочке (Россия) 1шт», у которого недрогруз 111 штук, но нам нужно лишь 90. Поэтому в итоге надо показать что искомый товар мы полностью перекрыли, первого товар забрали полностью, а второго забрали лишь часть. И всё это надо видеть в итоге. Ещё один момент: не обязательно всегда перекрываются все излишки



Как вариант предлагаю итог ввиде как во вложении, не обязательно чётко такой же, я немного шарю в екселе и смогу сам всё переделать.

Добрый день,

Этот файл будет некий шаблон с двумя вкладками, во вкладку «выгрузка» я вставляю все товары недогрузы и излишки, а во вторую вкладку «излишки» буду вставлять только излишки(тупо копипастом из первой вкладки), далее по построенным формулам подтягиваются товары колонками.

Заранее благодарю за помощь !

Автор - visor
Дата добавления - 08.05.2018 в 11:44
Светлый Дата: Вторник, 08.05.2018, 13:52 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 564
Репутация: 132 ±
Замечаний: 0% ±

Excel 2007
По первой прикидке для строки искомого товара получается вот такая массивная формула (J2):
Код
=ПОИСКПОЗ(МИН(ЕСЛИ((ЛЕВБ($B2;4)=ЛЕВБ(выгрузка!$B$1:$B$99;4))*(выгрузка!$D$1:$D$99<0)*($G2=выгрузка!$G$1:$G$99)*(СЧЁТЕСЛИ(J$1:J1;СТРОКА($1:$99))=0);ABS($A2-выгрузка!$A$1:$A$99)/$A2));ЕСЛИ((ЛЕВБ($B2;4)=ЛЕВБ(выгрузка!$B$1:$B$99;4))*(выгрузка!$D$1:$D$99<0)*($G2=выгрузка!$G$1:$G$99)*(СЧЁТЕСЛИ(J$1:J1;СТРОКА($1:$99))=0);ABS($A2-выгрузка!$A$1:$A$99)/$A2);)

Для количества выбранного товара:
Код
=МИН(-ИНДЕКС(выгрузка!D:D;$J2);ОТБР($F2/M2))

Пока не учёл неполное использование товара для других замещений.
К сообщению приложен файл: _2_1.xlsx(15.8 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеПо первой прикидке для строки искомого товара получается вот такая массивная формула (J2):
Код
=ПОИСКПОЗ(МИН(ЕСЛИ((ЛЕВБ($B2;4)=ЛЕВБ(выгрузка!$B$1:$B$99;4))*(выгрузка!$D$1:$D$99<0)*($G2=выгрузка!$G$1:$G$99)*(СЧЁТЕСЛИ(J$1:J1;СТРОКА($1:$99))=0);ABS($A2-выгрузка!$A$1:$A$99)/$A2));ЕСЛИ((ЛЕВБ($B2;4)=ЛЕВБ(выгрузка!$B$1:$B$99;4))*(выгрузка!$D$1:$D$99<0)*($G2=выгрузка!$G$1:$G$99)*(СЧЁТЕСЛИ(J$1:J1;СТРОКА($1:$99))=0);ABS($A2-выгрузка!$A$1:$A$99)/$A2);)

Для количества выбранного товара:
Код
=МИН(-ИНДЕКС(выгрузка!D:D;$J2);ОТБР($F2/M2))

Пока не учёл неполное использование товара для других замещений.

Автор - Светлый
Дата добавления - 08.05.2018 в 13:52
visor Дата: Вторник, 08.05.2018, 16:41 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Ога, покручу в выходные и дам фидбек.
Добрый человек, покажи пжлста кусок по поиску товара с отклонением цены не более 30%
Спасибо
 
Ответить
СообщениеОга, покручу в выходные и дам фидбек.
Добрый человек, покажи пжлста кусок по поиску товара с отклонением цены не более 30%
Спасибо

Автор - visor
Дата добавления - 08.05.2018 в 16:41
Светлый Дата: Вторник, 08.05.2018, 18:11 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 564
Репутация: 132 ±
Замечаний: 0% ±

Excel 2007
Фактически до конца докрутил, но формулы не оптимизированы и протягиваются только вниз. Замена идёт не более, чем тремя товарами и ограничена суммой основного товара. Мне кажется, что-то ещё не доработал, но пока не могу сообразить.
Проверка на 30%:
Код
(ABS($A2-выгрузка!$A$2:$A$99)/$A2<30%)

Проверяйте.
Автоматизировал выбор товара на лист излишки. Файл перезалил.
К сообщению приложен файл: _2_2.xlsx(35.3 Kb)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 08.05.2018, 23:13
 
Ответить
СообщениеФактически до конца докрутил, но формулы не оптимизированы и протягиваются только вниз. Замена идёт не более, чем тремя товарами и ограничена суммой основного товара. Мне кажется, что-то ещё не доработал, но пока не могу сообразить.
Проверка на 30%:
Код
(ABS($A2-выгрузка!$A$2:$A$99)/$A2<30%)

Проверяйте.
Автоматизировал выбор товара на лист излишки. Файл перезалил.

Автор - Светлый
Дата добавления - 08.05.2018 в 18:11
Светлый Дата: Четверг, 10.05.2018, 12:54 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 564
Репутация: 132 ±
Замечаний: 0% ±

Excel 2007
Подкорректировал, чтобы не выдавались товары при полной замене.
Сделал универсальные копируемые и протягиваемые формулы, но они громоздкие и для больших массивов будут ещё больше тормозить.
К сообщению приложен файл: _2_3.xlsx(33.4 Kb)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Четверг, 10.05.2018, 13:23
 
Ответить
СообщениеПодкорректировал, чтобы не выдавались товары при полной замене.
Сделал универсальные копируемые и протягиваемые формулы, но они громоздкие и для больших массивов будут ещё больше тормозить.

Автор - Светлый
Дата добавления - 10.05.2018 в 12:54
visor Дата: Четверг, 10.05.2018, 13:31 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Работает, согласен долго грузится- много массивов, но некоторые массивы уберу ВПРом, побыстрее будет. В любом случае это быстрее, чем руками искать.
Алгоритм в одном месте не верный. Отчёт пытается убрать недогрузы, пример: товар фрилис, к нему нашёл первый товар дуболистный 30 штук, остаётся найти ещё на 120-30=90, а второй пихает на 95 листового в горшочке, думаю потому что в другом месте тоже есть этот товар на 16 штук. То есть отчёт пытается убрать недогрузы, а нужно излишки.
P.S. Блин спасибо, новые штуки нашёл, например ЛЕВБ- не понял чем отличается от ЛЕВСИМВ. ABS- это чаще тёмный лес )))
 
Ответить
СообщениеРаботает, согласен долго грузится- много массивов, но некоторые массивы уберу ВПРом, побыстрее будет. В любом случае это быстрее, чем руками искать.
Алгоритм в одном месте не верный. Отчёт пытается убрать недогрузы, пример: товар фрилис, к нему нашёл первый товар дуболистный 30 штук, остаётся найти ещё на 120-30=90, а второй пихает на 95 листового в горшочке, думаю потому что в другом месте тоже есть этот товар на 16 штук. То есть отчёт пытается убрать недогрузы, а нужно излишки.
P.S. Блин спасибо, новые штуки нашёл, например ЛЕВБ- не понял чем отличается от ЛЕВСИМВ. ABS- это чаще тёмный лес )))

Автор - visor
Дата добавления - 10.05.2018 в 13:31
Светлый Дата: Четверг, 10.05.2018, 14:10 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 564
Репутация: 132 ±
Замечаний: 0% ±

Excel 2007
Если таблицы большие, надо диапазоны во всех формулах расширить.
Подгружает на второй лист только с положительным количеством товара, а заменяет только из отрицательных.
Если товар одинаковый в разных строках, воспринимает как разные.
Для большего списка замен все формулы надо переделывать. Сейчас только три замены работают.
ABS- это чаще тёмный лес

ABS - это абсолютное значение, т.е. всегда положительное.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеЕсли таблицы большие, надо диапазоны во всех формулах расширить.
Подгружает на второй лист только с положительным количеством товара, а заменяет только из отрицательных.
Если товар одинаковый в разных строках, воспринимает как разные.
Для большего списка замен все формулы надо переделывать. Сейчас только три замены работают.
ABS- это чаще тёмный лес

ABS - это абсолютное значение, т.е. всегда положительное.

Автор - Светлый
Дата добавления - 10.05.2018 в 14:10
visor Дата: Четверг, 10.05.2018, 14:51 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Да, я сразу понял что тянет на второй лист только положительные. Сейчас вставил объём данных с работы и вставил для пробы, соответственно продлил формулы на одной м второй странице по 1500 и 2000 строк соответственно. Много раз повторяется один товар, который можно было бы перекрыть чутли не единожды(подсветил цветом тоыар, в поиске первого товара). Не работает правило цены 30%(подсветил пример строчкой). В некоторых местах тянет товар с ценой за штуку, но не тянет кол-во.
Вижу что только под три товара заточено. Не проблема будем обрабатывать частями.
 
Ответить
СообщениеДа, я сразу понял что тянет на второй лист только положительные. Сейчас вставил объём данных с работы и вставил для пробы, соответственно продлил формулы на одной м второй странице по 1500 и 2000 строк соответственно. Много раз повторяется один товар, который можно было бы перекрыть чутли не единожды(подсветил цветом тоыар, в поиске первого товара). Не работает правило цены 30%(подсветил пример строчкой). В некоторых местах тянет товар с ценой за штуку, но не тянет кол-во.
Вижу что только под три товара заточено. Не проблема будем обрабатывать частями.

Автор - visor
Дата добавления - 10.05.2018 в 14:51
visor Дата: Четверг, 10.05.2018, 14:52 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Вот что получилось, забыл вложить.
 
Ответить
СообщениеВот что получилось, забыл вложить.

Автор - visor
Дата добавления - 10.05.2018 в 14:52
StoTisteg Дата: Четверг, 10.05.2018, 15:32 | Сообщение № 12
Группа: Авторы
Ранг: Ветеран
Сообщений: 920
Репутация: 78 ±
Замечаний: 0% ±

Excel 2010
Вот что получилось, забыл вложить.

Опять забыли :)


Проверь всё. ThisWorkbook.Save. On Error Resume Next.
 
Ответить
Сообщение
Вот что получилось, забыл вложить.

Опять забыли :)

Автор - StoTisteg
Дата добавления - 10.05.2018 в 15:32
visor Дата: Четверг, 10.05.2018, 17:39 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Не увидел ограничений в 100кб. Сейчас высылают побольше данных. Если протянуть формулы, то полезут ошибки, которые описывал. Если я протяну в этом файле, то он увеличит размер семи кратно.и не смогу выслать.
 
Ответить
СообщениеНе увидел ограничений в 100кб. Сейчас высылают побольше данных. Если протянуть формулы, то полезут ошибки, которые описывал. Если я протяну в этом файле, то он увеличит размер семи кратно.и не смогу выслать.

Автор - visor
Дата добавления - 10.05.2018 в 17:39
visor Дата: Четверг, 10.05.2018, 17:40 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
:)
К сообщению приложен файл: _3_3-2.xlsx(92.6 Kb)
 
Ответить
Сообщение:)

Автор - visor
Дата добавления - 10.05.2018 в 17:40
Светлый Дата: Четверг, 10.05.2018, 19:56 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 564
Репутация: 132 ±
Замечаний: 0% ±

Excel 2007
Расширил до пяти товаров.
Оставил 11 строк, чтобы файл загрузился. Последнюю можно протянуть сколько надо.
Выгрузка может быть заполнена до 999 строки.
Ошибок не обнаружено.
К сообщению приложен файл: _3_3-2_1.xlsx(92.9 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеРасширил до пяти товаров.
Оставил 11 строк, чтобы файл загрузился. Последнюю можно протянуть сколько надо.
Выгрузка может быть заполнена до 999 строки.
Ошибок не обнаружено.

Автор - Светлый
Дата добавления - 10.05.2018 в 19:56
visor Дата: Пятница, 11.05.2018, 17:37 | Сообщение № 16
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, добрый день ! Могу попросить слегка изменить логику?
Сейчас расчёт использованного остатка идёт от деления общей цены на цену найденной штуки.
Вот высылают пример. Суть такова что: не полностью убрано кол-во, компенсирована финансовая часть, а мне надо схлопнуть кол-во

Кол-во всех найденных гвоздей это 276+1200+9=1485, а набрать надо 1280. То что цена будет не биться ну чёрт с ним, главное что мы убрали излишек кол-венный. Или по другому: надо найти товаров на кол-во не более 1280, или равное ему, согласно условий таких как: отклонение не более 30% цены на штуку, первым 4 цифрам кода и прочим условиям
К сообщению приложен файл: _3_3-2_2.xlsx(42.0 Kb)
 
Ответить
СообщениеСветлый, добрый день ! Могу попросить слегка изменить логику?
Сейчас расчёт использованного остатка идёт от деления общей цены на цену найденной штуки.
Вот высылают пример. Суть такова что: не полностью убрано кол-во, компенсирована финансовая часть, а мне надо схлопнуть кол-во

Кол-во всех найденных гвоздей это 276+1200+9=1485, а набрать надо 1280. То что цена будет не биться ну чёрт с ним, главное что мы убрали излишек кол-венный. Или по другому: надо найти товаров на кол-во не более 1280, или равное ему, согласно условий таких как: отклонение не более 30% цены на штуку, первым 4 цифрам кода и прочим условиям

Автор - visor
Дата добавления - 11.05.2018 в 17:37
Светлый Дата: Пятница, 11.05.2018, 22:47 | Сообщение № 17
Группа: Проверенные
Ранг: Ветеран
Сообщений: 564
Репутация: 132 ±
Замечаний: 0% ±

Excel 2007
Переделал. Сейчас только количество учитывает.
К сообщению приложен файл: _3_3-2_3.xlsx(94.1 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеПеределал. Сейчас только количество учитывает.

Автор - Светлый
Дата добавления - 11.05.2018 в 22:47
Мир MS Excel » Вопросы и решения » Вопросы по Excel » поиск составляющих по условиям, один из которых процент (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2018 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!