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

Вход

Регистрация

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

 

= Мир MS Excel/Наличие/потребление с определением даты превышения - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Наличие/потребление с определением даты превышения (Макросы/Sub)
Наличие/потребление с определением даты превышения
user0 Дата: Четверг, 11.05.2017, 06:07 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 125
Репутация: 8 ±
Замечаний: 0% ±

Excel 2013, 2016
Доброго времени,

Делаю наличия/потребления материала (вызывается по двойному клику на номере материала на листе stock).
Потребление указывается на листе demand, там же и расчитывается на сколько хватит текущего кол-ва материала (вторая строка).
Потом номер материала подставляется в сводную (по demand) на листе chart, обновляется график и выводится в форму.

Возникла трудность с расчетом количества дней оставшихся до даты исчерпания запаса материала на складе (F2 на листе demand).
1. Сначала данные сортируются по дате (G), потом фильтруются по материалу (A).
2. Вычисляется потребление в день (H) для каждой заявки.
3. Вычисляется сколько осталось потребить для уже начавшихся заявок (I).
4. Вычисляется сколько израсходуется материала исходя из всех случаев потребления (J) и максимум подставляется в J2.
5. Вычисляется сколько дней осталось до истечения запаса материала на складе (K) и минимальное положительное подставляется в K2.
6. Находится дата заявки на которой кончается запас материала из 5 пункта и из нее вычитается кол-во дней через которые закончится материал и получаем точную дату когда закончится материал.

Вот на 5ом пункте возникла проблема, ни сабтотал максимум по положительным не делает, ни
Код
{=MIN(IF(K3:K50>0,K3:K50))}
со сортироваными данными не работает.
Нашел несколько вариантров с массивами на vba, но не смог их применить для сортированого списка.

Подскажите, пожалуйста, как лучше реализовать 5ый и возможно 6ой пункты.
И в целом возможно есть варианты как лучше и проще прийти к дате когда кончится запас?
К сообщению приложен файл: 5221166.png(119Kb) · 5191455.xlsm(78Kb)


Сообщение отредактировал user0 - Четверг, 11.05.2017, 09:44
 
Ответить
СообщениеДоброго времени,

Делаю наличия/потребления материала (вызывается по двойному клику на номере материала на листе stock).
Потребление указывается на листе demand, там же и расчитывается на сколько хватит текущего кол-ва материала (вторая строка).
Потом номер материала подставляется в сводную (по demand) на листе chart, обновляется график и выводится в форму.

Возникла трудность с расчетом количества дней оставшихся до даты исчерпания запаса материала на складе (F2 на листе demand).
1. Сначала данные сортируются по дате (G), потом фильтруются по материалу (A).
2. Вычисляется потребление в день (H) для каждой заявки.
3. Вычисляется сколько осталось потребить для уже начавшихся заявок (I).
4. Вычисляется сколько израсходуется материала исходя из всех случаев потребления (J) и максимум подставляется в J2.
5. Вычисляется сколько дней осталось до истечения запаса материала на складе (K) и минимальное положительное подставляется в K2.
6. Находится дата заявки на которой кончается запас материала из 5 пункта и из нее вычитается кол-во дней через которые закончится материал и получаем точную дату когда закончится материал.

Вот на 5ом пункте возникла проблема, ни сабтотал максимум по положительным не делает, ни
Код
{=MIN(IF(K3:K50>0,K3:K50))}
со сортироваными данными не работает.
Нашел несколько вариантров с массивами на vba, но не смог их применить для сортированого списка.

Подскажите, пожалуйста, как лучше реализовать 5ый и возможно 6ой пункты.
И в целом возможно есть варианты как лучше и проще прийти к дате когда кончится запас?

Автор - user0
Дата добавления - 11.05.2017 в 06:07
Pelena Дата: Четверг, 11.05.2017, 08:52 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11599
Репутация: 2572 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
user0, здравствуйте, оформите формулу тегами (кнопка fx)

По теме: вроде, вся таблица на формулах, п.5 тоже можно сделать формулой. Или Вам решение нужно макросом (тема в разделе макросов)?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениеuser0, здравствуйте, оформите формулу тегами (кнопка fx)

По теме: вроде, вся таблица на формулах, п.5 тоже можно сделать формулой. Или Вам решение нужно макросом (тема в разделе макросов)?

Автор - Pelena
Дата добавления - 11.05.2017 в 08:52
Pelena Дата: Четверг, 11.05.2017, 09:36 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11599
Репутация: 2572 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Такая формула массива подойдёт?
Код
=МИН(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(5;СМЕЩ($K$1;СТРОКА($2:$50);0))>0;K3:K51))
К сообщению приложен файл: 8156732.xlsm(73Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеТакая формула массива подойдёт?
Код
=МИН(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(5;СМЕЩ($K$1;СТРОКА($2:$50);0))>0;K3:K51))

Автор - Pelena
Дата добавления - 11.05.2017 в 09:36
user0 Дата: Четверг, 11.05.2017, 09:38 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 125
Репутация: 8 ±
Замечаний: 0% ±

Excel 2013, 2016
Можно формулой (если она не на две строки :) ), можно и на vba, как проще.

Формулами потому что так проще и не уверен, что понадобится через некотрое время, может еще какие линии дорисовывать придется..
Пока планирую оставить столбцы с А до N, а O-Q удалить после того как найдутся ответы 5ый и 6ой пункты.
 
Ответить
СообщениеМожно формулой (если она не на две строки :) ), можно и на vba, как проще.

Формулами потому что так проще и не уверен, что понадобится через некотрое время, может еще какие линии дорисовывать придется..
Пока планирую оставить столбцы с А до N, а O-Q удалить после того как найдутся ответы 5ый и 6ой пункты.

Автор - user0
Дата добавления - 11.05.2017 в 09:38
_Boroda_ Дата: Четверг, 11.05.2017, 09:39 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11538
Репутация: 4751 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Вообще-то у Вас вполне нормально работает Ваша формула (посмотрите в файле ячейку К2). Только ее вводить нужно одновременным нажатием Контрл Шифт Ентер
Формула для даты (учитывает вероятность того, что срок с минимальными днями может быть несколько и у каждой из них могут быть свои даты заявки
Тоже формула массива (в файле желтая)
Код
=МИН(ЕСЛИ(K3:K50>0;G3:G50))-K2


Добавлено
Подождите, Вы под сортировкой что, подразумеваете фильтрацию?
К сообщению приложен файл: 5191455_1.xlsm(70Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВообще-то у Вас вполне нормально работает Ваша формула (посмотрите в файле ячейку К2). Только ее вводить нужно одновременным нажатием Контрл Шифт Ентер
Формула для даты (учитывает вероятность того, что срок с минимальными днями может быть несколько и у каждой из них могут быть свои даты заявки
Тоже формула массива (в файле желтая)
Код
=МИН(ЕСЛИ(K3:K50>0;G3:G50))-K2


Добавлено
Подождите, Вы под сортировкой что, подразумеваете фильтрацию?

Автор - _Boroda_
Дата добавления - 11.05.2017 в 09:39
user0 Дата: Четверг, 11.05.2017, 09:44 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 125
Репутация: 8 ±
Замечаний: 0% ±

Excel 2013, 2016
Такая формула массива подойдёт?

да, работает. Спасибо!

Добавлено
Подождите, Вы под сортировкой что, подразумеваете фильтрацию?

Так точно.. прошу прощения, дописал в первое сообщение.


Сообщение отредактировал user0 - Четверг, 11.05.2017, 09:46
 
Ответить
Сообщение
Такая формула массива подойдёт?

да, работает. Спасибо!

Добавлено
Подождите, Вы под сортировкой что, подразумеваете фильтрацию?

Так точно.. прошу прощения, дописал в первое сообщение.

Автор - user0
Дата добавления - 11.05.2017 в 09:44
_Boroda_ Дата: Четверг, 11.05.2017, 09:56 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11538
Репутация: 4751 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так точно.. прошу прощения

Тада так обычной формулой можно
Код
=АГРЕГАТ(15;3;K3:K50/(K3:K50>0);1)

Код
=АГРЕГАТ(15;3;G3:G50/(K3:K50>0);1)-K2
К сообщению приложен файл: 5191455_2.xlsm(70Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Так точно.. прошу прощения

Тада так обычной формулой можно
Код
=АГРЕГАТ(15;3;K3:K50/(K3:K50>0);1)

Код
=АГРЕГАТ(15;3;G3:G50/(K3:K50>0);1)-K2

Автор - _Boroda_
Дата добавления - 11.05.2017 в 09:56
and_evg Дата: Четверг, 11.05.2017, 10:06 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 223
Репутация: 39 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, А что за АГРЕГАТ? В надстройке? Какой?
 
Ответить
Сообщение_Boroda_, А что за АГРЕГАТ? В надстройке? Какой?

Автор - and_evg
Дата добавления - 11.05.2017 в 10:06
_Boroda_ Дата: Четверг, 11.05.2017, 10:07 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11538
Репутация: 4751 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Не, это с 2010-го Excel начиная

https://support.office.com/ru-ru....3fa26df


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНе, это с 2010-го Excel начиная

https://support.office.com/ru-ru....3fa26df

Автор - _Boroda_
Дата добавления - 11.05.2017 в 10:07
Pelena Дата: Четверг, 11.05.2017, 10:11 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11599
Репутация: 2572 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Я тоже пробовала с АГРЕГАТ(), но он почему-то не срабатывает с фильтрованными строками


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЯ тоже пробовала с АГРЕГАТ(), но он почему-то не срабатывает с фильтрованными строками

Автор - Pelena
Дата добавления - 11.05.2017 в 10:11
user0 Дата: Четверг, 11.05.2017, 10:13 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 125
Репутация: 8 ±
Замечаний: 0% ±

Excel 2013, 2016
но он почему-то не срабатывает с фильтрованными строками

Вот я тоже смотрю пример Бороды и понять не могу почему у меня не работает )
 
Ответить
Сообщение
но он почему-то не срабатывает с фильтрованными строками

Вот я тоже смотрю пример Бороды и понять не могу почему у меня не работает )

Автор - user0
Дата добавления - 11.05.2017 в 10:13
_Boroda_ Дата: Четверг, 11.05.2017, 16:58 | Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11538
Репутация: 4751 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
У меня тоже не работает. Дома переделаю


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеУ меня тоже не работает. Дома переделаю

Автор - _Boroda_
Дата добавления - 11.05.2017 в 16:58
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Наличие/потребление с определением даты превышения (Макросы/Sub)
Страница 1 из 11
Поиск:

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