Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет остатков в НЕ отсортированном массиве данных
 
#1
21.02.2017 01:30:03
Доброго времени суток, уважаемые гуру Excel !

Очень прошу Вас мне подсказать, если это возможно в принципе, решение следующего вопроса. Может какой-либо составной формулой это можно сделать, функцией или макросом?
Имеется неотсортированный массив данных (см. приложенный файлы), в котором в том числе есть дата поступления/списания средств с различных счетов по организациям.
Задача состоит в том, чтобы корректно и последовательно (по датам) рассчитать остатки после поступления и списания средств на счетах в столбце Остатки (выделен зеленым). Отображать остаток за одну дату только напротив одной (последней) из этих дат.
Изначально предполагается, что на счете было 0, затем имеется приход - расход, получается какой-то остаток. Данные взяты примерные, возможны и отрицательные остатки.
А также есть ли возможность сделать так, что бы красным подсвечивались одинаковые (дублирующиеся) строки? Подсвечивалось красным и выводилось сообщение, что есть дубль (не знаю, насколько это осуществимо).

Было бы решением данного вопроса отсортировать данный массив по дате и счетам, а затем элементарными формулами +/- рассчитать остаток. Но вся сложность в том, что периодически и довольно часто в конец этого массива будут добавляться новые строки, естественно в таком же виде (неотсортированном). При формировании массива уже в несколько тысяч данный процесс становится очень трудоемким и затратным по времени.
Изменено: bond_0790 - 21.02.2017 17:20:46
 
 
 
#2
21.02.2017 02:04:07
См. вариант. Арифметику не проверял )
 
 
 
#3
21.02.2017 06:34:35
если правильно объедините формулы
Лень двигатель прогресса, доказано!!!
 
 
 
#4
21.02.2017 09:58:31
Результаты расчёта на отдельном листе.
Сортировка исходных данных не нужна.
И ещё пару новых вариантов
Изменено: Valera2 - 21.02.2017 11:42:40
 
 
 
#5
21.02.2017 13:24:18
Юрий М, Сергей, Valera2, спасибо Вам, но не совсем то, что надо.

Здесь и мое упущение, что в описании задачи не указал, что эти остатки необходимо рассчитывать в столбце "Остатки" (выделен зеленым).
Что-то похожее есть у Сергей, но расчет получается некорректным.

Вот методом сортировки и простых формул получаются такие остатки (см. файл в приложении), также изменил даты.
Отсортировал на 1-м уровне по счету, на 2-м по дате. Затем идет расчет по формулам в зеленом столбце. Но, как уже писал, данные периодически в таком же виде добавляются в конец массива, и после многочисленных сортировок уже каша какая-то (надо искать пустые ячейки о корректно в них вставлять нужные формулы).
А также есть ли возможность сделать так, что бы красным подсвечивались одинаковые (дублирующиеся) строки? Может подсвечивалось красным и выводилось сообщение, что есть дубль (не знаю, насколько это осуществимо).
 
 
 
#6
21.02.2017 15:57:50
Может так, у вас даты текст
 
 
 
#7
21.02.2017 16:17:43
Vik_tor, вот такие даты имеются в массиве (это выгрузка).
По Вашему способу решения получаются некорректные остатки (в зеленом столбце мой расчет, справа Ваша формула).
Хотя можно сказать корректные, но на конкретную дату, а не после конкретной операции (списания/поступления). Это не совсем удобно. Например, в случае построения сводной таблицы на основе этого массива, остатки по датам будут завышены ровно во столько раз, сколько операций на эту дату было.
Изменено: bond_0790 - 21.02.2017 16:24:30 (Не отображался в тексте скриншот, приложил в виде файла.)
 
 
 
#8
21.02.2017 16:50:11
те если в 1 день несколько операций остаток нужно вывести только 1 строке
 
 
 
#9
21.02.2017 17:06:08
Если правильно понял Ваш вопрос, то по одной строке не надо. Необходимо как раз по каждой строке выводить остаток после каждой предыдущей операции (как у меня на скриншоте выше, только там отсортированный массив).
 
 
 
#10
21.02.2017 17:07:25
а как отличать последовательность операций проведенных в один день
Изменено: Vik_tor - 21.02.2017 17:08:34
 
 
 
#11
21.02.2017 17:17:35
Vik_tor, на самом деле даже не понял, как у Вас формула работает. Еще раз посмотрел, и какие-то непонятные цифры в остатках получаются.

Последовательность операций в один день не имеет значение.
Хотя вот тут и посетила мысль, что выводить остаток по каждой строчке тоже не совсем верно.  :(
Может есть возможность выводить остаток в столбце Остатки за одну дату только напротив одной (последней) из этих дат?
То есть так:
Вх.номерДатаПоступлениеСписаниеОрганизацияСчетОстатки
2728.04.2016140 69304323
81328.04.201601 2904323
77628.04.201601 2904323138 113
 
 
 
#12
21.02.2017 17:17:46
остаток = поступления да период с искомой даты и ранее минус списание от искомой даты и ранее ваш текст даты при этом перевожу в число для сравнений, если вы отсортируете по возрастанию увидите ка изменяется остаток по датам
 
 
 
#13
21.02.2017 17:19:15
вот вариант напротив первой строчки каждой даты  
 
 
 
#14
21.02.2017 17:20:41
Цитата
bond_0790 написал:
в случае построения сводной таблицы на основе этого массива, остатки по датам будут завышены
а вы в сводной используйте не сумму а среднее
 
 
 
#15
21.02.2017 18:02:49
Цитата
Vik_tor написал: а вы в сводной используйте не сумму а среднее
Это верно подмечено!  :)

Vik_tor,  Ваш метод не учитывает разные счета.
Цитата
Vik_tor написал: если вы отсортируете по возрастанию увидите ка изменяется остаток по датам
Отсортировал, непонятные цифры получаются.
 
 
 
#16
21.02.2017 18:41:51
А если сразу сводную строить (только даты нужно преобразовать в даты, копируем 0, вспцвставка значения сложить- далее нудный формат даты)
Изменено: Vik_tor - 21.02.2017 18:43:22
 
 
 
#17
21.02.2017 20:09:38
Почитал ваши хотелки.
Посмотрел на ваше творчество.
Пришел к выводу - у вас не правильный подход к анализу записей вашего реестра.

ПРАВИЛЬНОЕ ОТНОШЕНИЮ К РЕЕСТРУ.
Реестр предназначен для записей ваших документов.Каждая запись должна быть полной,
т.е. в записи должны быть заполнены все нужные поля.
ЭТО у вас получается хорошо, за исключением

- Не соблюдён формат 1.поля ДАТА,  д.б. формата типа "Дата", а не "Общий" или тем
более не "Текстовый"
Дальше вроде всё нормально, но следить за этим надо.
Хотя у поля "Остаток" формат без десятичных знаков - потеряны копейки.
2. поля "Поступление", "Списание" д.б. формата "числовой" с "двумя десятичными знаками"
3. поля "Счёт" и "Вх.номер" д.б. формата "Текстовый"

- Поле "Остаток" из рееста  должно быть исключено.
Занесли документы в РЕЕСТР и забыли про него.
Далее формирование оборотов и остатков производится в Оборотно-сальдовых ведомостях(ОСВ)
А они уже давно настоены и ОСВ формировалась автоматически
- так что работа после набора документов закончилась.
-------
ДАЛЕЕ
На основании РЕЕСТРА должны формироваться ОСВ.
ОСВ это компактная запись документов РЕЕСТРА.(максимум десятки записей вместо тысяч)

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

Форма ОСВ стандартная
это  колонки  Счёт, Вх.Остатки, Приход, Расход, Исх.Остаток
Или    Организация, Вх.Остатки, Приход, Расход, Исх.Остаток

В заголовке ОСВ может быть ДАТА, на которую расчитывается ОСВ. Или период ДАТ(Две даты)
И эти ДАТЫ будет использоваться формулами ОСВ для расчётов ОБОРОТОВ и ОСТАТКОВ.
------
Функции для расчёта ОСВ =СУММЕСЛИ() или =СУММЕСЛИМН() или =СУММПРОИЗВ()
Могут быть и другие.в т.ч. и  макросы VBA.

САМОЕ ГЛАВНОЕ
- ОТКАЖИТЕСЬ от ОСТАТКОВ в РЕЕСТРЕ
- перенесите все расчёты в ОСВ.
Иначе это будет постоянная головная боль. И на вас будут вешать работу с РЕЕСТРОМ.
------
Я знаю, что говорю.
Когда я для своего бухгалтера сделал эту связку РЕЕСТР - ОСВ,
Она, Увидев в первый раз, как ОСВ реагирует на запись каждого нового документа,
сказала - я сама,я САМА буду набирать РЕЕСТР.
А до этого всю работу с реестом приходилось делать мне.
------
Вы можете верить мне или не верить. НО
В любом случае - исправьте формат дат - установите "ДАТА" , а не "Общий".
------
Можно ещё вести РЕЕСТР отдельно на каждый месяц или квартал или год, а не общий за несколько лет.
Естественно и аналогичные ОСВ.
------
 
 
 
#18
21.02.2017 23:45:44
Valera2, в таком формате выгружаются данные и не подразумевается их дальнейшее форматирование. Опять же, если периодически будут добавлять данные, этого никто делать не будет (постоянно форматировать и приводить в нужный вид).

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

А также есть ли возможность отображать повторяющиеся строки в реестре, чтобы избежать случайных задвоений?
 
 
 
#19
22.02.2017 00:45:48
-----
Форматировать надо АВТОМАТИЧЕСКИ, сразу же после выгрузки данных  - Макросом VBA.
-----
Проверять правильность остатков желательно на малом количестве документов.
Лучше всего один-два документа на каждом счёте.
-----
Входящие остатки надо определить самому - занести их в ОСВ и больше не трогать.
Документы пойдут на обороты и естественно на исх.остатки.
-----
Изменено: Valera2 - 22.02.2017 00:47:25
 
 
 
#20
22.02.2017 02:30:45
Цитата
Valera2 написал:
Входящие остатки надо определить самому - занести их в ОСВ и больше не трогать.
Valera2, не совсем понял. Если мы смотрим остаток на конкретную дату, то входящий остаток уже должен быть (если это вовсе не первая дата в реестре), т.к. на предыдущую дату уже был исходящий остаток.
Как таким образом определить входящий остаток самому или почему тогда он не считается?
 
 
 
#21
22.02.2017 10:40:00
ВЫДАВИ из бухгалтера.

Когда-то Вх.остаток были равны 0.
Это было в момент создания колхоза.
С тех пор утекло много времени. Бухгалтерский учет за это время
несколько раз переделывался, и каждый раз для корректного отображения
настоящих остатков(исх.остатки) входящие остатки фиксировались как
исх.остатки предыдущего периода учёта, и от них "плясали" приходными и
расходными документами.

Если СМОЖЕШЬ найти все документы движения с момента организации
колхоза ТОГДА ДАВИТЬ бухгалтера не надо.
Достаточно посчитать весь приход и отминусовать из него весь расход за
этот период, получатся вх.остатки на начало нового периода.
Решай сам как поступить.
 
 
 
#22
22.02.2017 13:01:52
Valera2, у нас на форуме к незнакомым людям принято обращаться на Вы.
 
 
 
#23
22.02.2017 13:06:29
И предложения не нужно рвать на разные строки.
 
 
 
Страницы: 1
Читают тему
Наверх