Страницы: 1
RSS
Таблица остатков на момент пересчета с учетом месячных проходов\расходов, Голову сломал, не могу соорудить :(
 
Добрый день. Как мне кажется довольно непростая задачка передо мной встала.

Есть конечная таблица в которой показываются остатки НА МОМЕНТ ПЕРЕСЧЕТА.
Есть общая таблица куда вносятся поставки материалов и расход материала.

Ситуация следующая: Материалы расходуются постоянно, но каждую неделю мы считаем остатки и по банальной формуле последний пересчет плюс последние поставки - текущий пересчет = определяем расход. Вот этот расход мы вносим в общую таблицу. Таким образом в общей таблице числятся все приходы товара и все расходы (расходы с примечанием Первый пересчет\Второй.....). Нужно научить конечную таблицу выдавать ФАКТИЧЕСКИЙ остаток на момент пересчета. пересчеты в таблице соответственно пронумерованы.

P.S На случай если у кого возникнут вопросы а-ля "нафиг так всё сложно", отвечу - полная версия таблицы избавит от подобных лишних и не информативных комментариев.

Если кому удастся это, буду премного благодарен. Битый час мучаюсь :(
 
Нумеровать пересчеты не стоит, раз Вы в основной таблице их текстом пишите. Вот такой вариант можно (см. файл) - формулы в сторону не протягиваются, изменение ссылок делать вручную. Если автопротяжка необходима, нужно увеличивать объем формулы за счет закрепления столбцов смарт-таблицы.
Или Вам остаток на определенную выбираемую дату нужен без использования столбца комментариев?
Изменено: Влад - 20.09.2017 16:10:09
 
Влад, Очень круто! Понятия не имею как Вам это удалось... Если не сложно, не могли бы пояснить логику работы используемой формулы чтобы я смог перенести решение из таблицы-примера в основную таблицу?

Я понимаю как работает СУММЕСЛИМН, но не пойму как действует "<="&ПРОСМОТР(2;(Движ[Позиция]=$B$2)*ПОИСК($A3;Движ[Комментарий]);Движ[Дата])

P.S В четвертом пересчете по всем материалам стоят нули. Видать что-то там не так. Так как я не знаю как работает формула не могу исправить.

UPD: Четвертый пересчет назван "четверый".) Исправлено) Всё работает.

Тем не менее мне очень хотелось бы вникнуть в метод которым была решена задача. Буду признателен за пояснения.
Изменено: SilentSpirit - 20.09.2017 16:33:27
 
Пардон, в слове "Четвертый" в таблице букву "т" пропустил) Сами исправите?
"ПРОСМОТР(2;(Движ[Позиция]=$B$2)*ПОИСК($A3;Движ[Комментарий]);Движ[Дата])" - ищет в массиве комментариев, отфильтрованных по товару в В2, позицию последнего комментария со словом "Первый" или "Второй" (т.е. то, что в А3) и выдает соответствующую этой позиции дату из столбца дат (последний комментарий = последняя дата пересчета).
 
Если я правильно понял, то функцией ПОИСК по комментарию (искомое в A3) мы определяем конец массива для функции ПРОСМОТР? Таким образом с последующим пересчетом у нас идёт в расчёт больше и больше данных? А дальше всё по стандартной логике СУММЕСЛИМН?
Изменено: SilentSpirit - 20.09.2017 16:37:36
 
Именно так. Это позволяет следующему результату пересчета не зависеть от предыдущего. Только массив через ф-цию "Поиск" не ограничивается, а фильтруется.
Изменено: Влад - 20.09.2017 16:39:21
 
 а так ?
 
Цитата
Влад написал:
Это позволяет следующему результату пересчета не зависеть от предыдущего.
Эту проблему я и пытался решить.
Это гениально)) Огромное спасибо за решение задачи и за объяснение логики функций ПРОСМОТР и ПОИСК.
Изменено: SilentSpirit - 20.09.2017 16:52:29
 
Цитата
Vik_tor написал:
а так ?
Та же логика, но с конкретной датой)
 
Цитата
Vik_tor написал:
а так ?
Интересно, смотреть данные по запрошенной дате, но эта опция не требуется. Спасибо.
Цитата
Влад написал:
Именно так.
Последний вопрос. Мне не ясно как Эксель читает "<" , "<=" и "<>" в некоторых местах формулы. То-есть понятно что эти знаки означают, но в контексте данной формулы не ясно как Эксель их воспринимает.

Например - Движ[Комментарий];"<>" что идёт сюда за условие?
Или отрывок из предыдущего моего уточнения - "<="&ПРОСМОТР(2;(Движ[Позиция]=$B$2)*ПОИСК($A4;Движ[Комментарий]);Движ[Дата]))
В начале "<="& что это означает? Почему в функции ПРОСМОТР искомое значение у нас 2 и почему идет умножение на возвращенное значение функцией ПОИСК?
Что-то я натянуть себе на голову это не могу. Сложновато)
 
Читает и воспринимает буквально) Меньше заданного значения (<), меньше или равно заданному значению (<=), не равно пустому значению (<>). Функция умножения - это фильтрация массива, "2" для ф-ции "Просмотр" - значение, превышающее все возможные в полученном массиве, дабы функция вернула последнее из такого массива. Т.к. все комменты у Вас начинаются со слов "Первый", "Второй" и т.д., то Поиск будет всегда выдавать либо 1 (позиция слова в комменте), либо ошибку. А вообще нажмите в меню "Вычислить формулу" и посмотрите как работает в данном случае "Просмотр".
Изменено: Влад - 20.09.2017 17:07:44
 
Если с < и <= было в принципе понятно, но <> меня запутало)

Еще раз большое спасибо! Пошёл дальше разбирать всю формулу!
 
Цитата
Влад написал:
Т.к. все комменты у Вас начинаются со слов "Первый", "Второй" и т.д.
вот тут моментик... в примере это так. В основной таблице комментариев куча и они совершенно разные... и слова "первый" "второй" и так далее могут встречаться и в комментариях к строкам совершенно не относящимся к материалам(не то чтобы к нужным или не нужным материалам, а совсем не к материалам), а например к технике или инструментарию.
Изменено: SilentSpirit - 20.09.2017 17:13:21
 
А вот из-за этого считать уже правильно не будет. Те, что относятся не к материалам - не страшно, фильтрацией ненужные отбрасываются, а вот не первая позиция - это нужно учесть.
 
Так... что-то я совсем сломался...

=СУММЕСЛИМН(Движ[Кол-во];Движ[Позиция];$B$2;Движ[Комментарий];"";Движ[Дата];"<"&ПРОСМОТР(9^9;(Движ[Позиция]=$B$2)*ПОИСК($A3;Движ[Комментарий]);Движ[Дата]))-СУММЕСЛИМН(Движ[Кол-во];Движ[Позиция];$B$2;Движ[Комментарий];"<>";Движ[Дата];"<="&ПРОСМОТР(9^9;(Движ[Позиция]=$B$2)*ПОИСК($A3;Движ[Комментарий]);Движ[Дата]))

СУММЕСЛИМН - это понятно. Суммируем данные в столбце кол-во по условиям:
Позиция - B2. Это понятно.
Комментарий - "". - не пустой. Тоже понятно
Дата - вот здесь совсем не могу понять что происходит:
В диапазоне условий - столбец Дата - мы находим условие которое ищется функцией ПРОСМОТР.
ПРОСМОТР ищет значение 9^9 (девять в девятой степени? не пойму что это. Почему это искомое значение). Массив отсеивается по Позиции равной B2. Это понятно. Далее идёт умножение на значение возвращенное функцией ПОИСК. ПОИСК оставляет только те строки в которых в столбце Комментарий стоит значение A3, так что-ли? И в конце возвращается массив по Дата.
Получается что в СУММЕСЛИМН по условию Дата возвращается массив отсеянный по позиции B2 благодаря условию в функции ПРОСМОТР и по наличию слова A3 в столбце комментария?

Как замудрённо то... У меня прям уже спортивный интерес понять логику этого метода

UPD: Методом проб и ошибок мне удалось перенести решение в основную таблицу.
За помощь выражаю огромную благодарность, Влад:)

Тем не менее я совсем не против все-таки вникнуть в логику функции. Если не затруднит, объясните пожалуйста, как Вы до такого решения дошли?
Изменено: SilentSpirit - 20.09.2017 18:22:45
 
Ну почти все правильно) ПРОСМОТР дает последнюю дату, когда в комментариях встречается искомое слово А3. До этой даты все данные и считаются в СУММЕСЛИМН. 9^9 - это такое большоооое число для ПРОСМОТР, чтобы он среди всех чисел (позиций слова, выданных ф-цией ПОИСК), взял последнее в массиве (это фича данной функции:).
 
Цитата
Влад написал:
ПРОСМОТР дает последнюю дату, когда в комментариях встречается искомое слово А3
Тааак... теперь понял) Кошмар))))
В диапазоне условий cтолбец Дата условием является значение меньшее или равно 387420489 (9^9) из возможных позиций в столбце Позиция отсеянное по B2(название позиции) при условии что в этой же строке по столбцу комментарий функцией ПОИСК находилось наличие A3(Первый\второй\третий...). И для того чтобы добавить еще одно условие для фильтрации мы используем функцию умножения к функции ПОИСК.


Но... как, Холмс... Как Вы догадались до такого?

Изменено: SilentSpirit - 20.09.2017 18:47:38
 
Изменю немного формулу, т.к. при несплошном расположении данных пересчета будет выбираться неверная дата...
Работа функции ПРОСМОТР в примере подробнее:
ПРОСМОТР(2;1/((Движ[Позиция]=$B$2)*ПОИСК($A3;Движ[Комментарий]));Движ[Дата]))
- 2 - число, большее чем максимальное в получаемом массиве - просто "лопата для поиска", в расчетах не участвует;
- ПОИСК($A3;Движ[Комментарий]) - массив из чисел (позиции искомого слова А3 в комментах) и ошибок (если слово А3 не найдено), возвращенный функцией ПОИСК;
- (Движ[Позиция]=$B$2)*... - фильтрация полученного через ПОИСК массива по товару В2 - найденные позиции слова, не принадлежащие товару В2, обнуляются;
- 1/((Движ[Позиция]=$B$2)*ПОИСК($A3;Движ[Комментарий])) - единица делится на весь отфильтрованный массив, чтобы нули превратить в ошибки - остаются ошибки и только числа (меньше или равны 1), где ПОИСК нашел в комменте слово А3, принадлежащее товару В2.
- ПРОСМОТР(2;1/((Движ[Позиция]=$B$2)*ПОИСК($A3;Движ[Комментарий]));Движ[Дата])) - функция ПРОСМОТР ищет в полученном массиве из чисел и ошибок самое последнее число (для этого и нужна "2" - больше максимального) и возвращает из столбца дат дату по позиции этого последнего числа в массиве.
 
2 чая этому господину
Страницы: 1
Наверх