Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Определить минимальный остаток средств на счете в течении месяца, упростить формулу
 
#1
31.12.2017 11:42:09
Доброе утро. С наступающим НГ!
Прошу помогите мне в этой формуле. Только начал узнавать как работают массивы, не до конца понимаю что это вообще. Как я знаю, массивы служат для упрощения написания формул. Без них формулы были бы длинными и сложными.

Итак.
Ячейка F10 определяет минимальный остаток по счету. Как видно в задаче, сначала на счете было 38тр., потом он снял 5тр., получилось 33тр., потом внес 9тр., получилось 41тр. В итоге минимальный остаток выходит 33тр.
В формуле все верно. Но, если передвинуть ячейки F8 и G8 на F9 и G9, то мин остаток становится 38тр., что не верно. А значит формула где то работает не правильно. Я первый раз начал использовать массивы, не знаю как они работают. Прошу помочь разобраться где стоит исправить.
 
 
 
#2
31.12.2017 11:51:55
Мало что понятно. На вскидку - используйте в формуле АБСОЛЮТНЫЕ ссылки
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
31.12.2017 12:10:54
Sanja, Я удалил все, что могло отвлекать. Оставил только то, что нужно мне на данный момент.
Посмотрите. Сейчас в файле соблюдаются условия, которые мне необходимы- мин остаток (F9)  рассчитывается верно. Но, если передвинуть ячейку F7 и H7 на F8 и H8, то мин. остаток (F9) будет показывать неверное значение, хотя я, по сути, ничего не менял, цифры те же, я просто передвинул значение в другую ячейку, которая тоже отвечает за операции по снятию.
Получается, что формула массива работает не полностью так, как мне нужно, а как сделать правильно я не знаю.

П.С, Я не представляю как мне должны помочь абсолютные ссылки в этой формуле)
Изменено: RamRiz - 31.12.2017 12:12:02
 
 
 
#4
31.12.2017 17:43:51
опишите конкретнее "логику" расчета минимального остатка, СЧЕТЕСЛИ вполне решит вашу проблему.
В exel дата это число, у вас в первом условии стоит =ЕСЛИ(F7=0;*, если вы переносите значения в ячейки ниже, формула возвращает значение "если ИСТИНА", т.е. G4.
Изменено: MOTO - 01.01.2018 20:08:51
 
 
 
#5
31.12.2017 18:58:06
MOTO, у нас на форуме (да и в быту тоже) к незнакомым людям принято обращаться на ВЫ.
 
 
 
#6
01.01.2018 17:28:18
MOTO, Расскажу принцип работы начисления процентов по дебетовой карте.
По дебетовой карте происходит начисление процентов на минимальный остаток средств на счете в течении месяца. К примеру Внес я 50тр на карту, потом пополнил его на 10. Хоть сумма увеличилась, но проценты мне начислят на минимальнцю сумму, которая получается 50 тр. Если же я сниму 10 тр, то получается минимальный остаток 40 тр.
В моей таблице я хочу чтобы определялся этот минимальный остаток, сейчас он не полностью правильно показывает. Формула показывает правильно, если операцию по снятию внести в ячейке F7, а на F8 он никак не реагирует.

Для сравнения, я скопировал таблицу и ввел новую операцию в строку снятие 2- мин остаток не изменился. Прошу помочь исправить формулу, чтобы она также учитывала операции в строке "Снятие 2".
 
 
 
#7
01.01.2018 17:56:35
Доброе время суток.
А не проще ли привести таблицу в хронологический порядок и в дополнительном столбце подсчитывать состояние счёта на каждую операцию, потом выбирая по этому столбцу минимум?
 
 
 
#8
01.01.2018 18:41:35
Андрей VG, Не плохая идея. Мне нравится) Правда придется добавить дополнительный столбик, но ничего, и так сойдет.
 
 
 
#9
01.01.2018 19:06:52
К сожалению ваш вариант тоже не подходит. Ключевая особенность этой таблицы в том, что даты будут постоянно другими.
Операции по снятию и пополнению могут начинаться в любом порядке, исходя из задачи.
К примеру ваша таблица. Сейчас тут все правильно.
ДатаСуммаСостояние
первое внесение10.07.20173800038000
снятие 119.07.2017600032000
пополнение 120.07.2017900041000
снятие222.07.2017500036000
Мин. остаток32000,00
Но если поменять даты операций, и сделать, чтобы пополнение произошло первым, то получится...
ДатаСуммаСостояние
первое внесение10.07.20173800038000
снятие 119.07.2017600041000
пополнение 118.07.2017900047000
снятие222.07.2017500036000
Мин. остаток36000,00
Мин остаток изменится, а в вашей таблице он не изменился. Если посмотрите на мою формулу, то там работает все как надо, только почему то не учитывает строку "Снятие 2".
 
 
 
#10
01.01.2018 19:08:46
А зачем нарушать хронологию?
 
 
 
#11
01.01.2018 19:51:29
RamRiz, смотрите пример
Изменено: MOTO - 01.01.2018 20:09:46
 
 
 
#12
01.01.2018 20:07:09
MOTO, ваш вариант можно проще записать
=SUMPRODUCT(K4:K16*((LEFT(I4:I16;1)="П")*2-1))
только это не минимальный ,а итоговый
По вопросам из тем форума, личку не читаю.
 
 
 
#13
01.01.2018 20:32:47
БМВ, если я правильно понял ТС, то ваше решение будет выглядеть
Код
=ЕСЛИ(СУММПРОИЗВ($K$5:$K16*((ЛЕВБ($I$5:$I16;1)="П")*2-1))>0;$K$4;$K$4+СУММПРОИЗВ($K$5:$K16*((ЛЕВБ($I$5:$I16;1)="П")*2-1)))
 
 
 
#14
01.01.2018 21:00:43
MOTO, нет

RamRiz, с Доп столбцом просто все.,
По вопросам из тем форума, личку не читаю.
 
 
 
#15
01.01.2018 21:02:51
Юрий М,
Есть 1 столбец, в котором идет перечисление операций. Он вообще не должен меняться.
первое внесение
пополнение 1
пополнение 2
снятие 1
снятие2
Мин. остаток
Меняется второй столбец, в котором указываются различные даты.
Дата        
10.07.2017
20.07.2017
19.07.2017
33000,00
В 3 столбце, исходя из дат, указанных во втором столбце, должен высчитываться минимальны остаток.

Моя формула {=ЕСЛИ(F7=0;G4;ЕСЛИ(F7:F8>F5:F6;ЕСЛИ(G7:G8>G5:G6;G4+(G5+G6-G7-G8);G4);ЕСЛИ(F7:F8<F5:F6;G4-G7:G8)))}
но, к сожалению массивы не работают как надо, работает только на ячейках F5 и F7, F6 и F8
игнорирует.
Изменено: RamRiz - 01.01.2018 21:04:18
 
 
 
#16
01.01.2018 21:10:50
БМВ,Благодарю. Ваш вариант получился универсальным, и надеюсь полностью рабочим)
В идеале конечно, лучше было бы, если бы можно было обойтись без дополнительной таблицы, потому что она отвлекает лишними цифрами.
Но все равно Большое Спасибо :)
 
 
 
#17
01.01.2018 21:22:51
RamRiz, столбец можно и скрыть но не суть.

в ваших формулах основная ошибка эвот это (F7:F8>F5:F6) сравнение будет два,  F7 c F5  И F8 с F6.
По вопросам из тем форума, личку не читаю.
 
 
 
#18
01.01.2018 21:33:21
БМВ,
Понял. А как можно скрыть столбец?
Я только начал использовать массивы, раньше старался их избегать, поэтому еще не до конца понял как их лучше использовать.

Посмотрите пожалуйста в прикрепленном файле полную версию таблицы. Она состоит из 4 частей: 1РП, 2РП, 3РП, 4РП, которые по сути значат 4 месяца. Это получается надо будет добавить дополнительный столбик к каждой части. Это действие здорово расширит таблицу, мне кажется будет не красиво смотреться.
Изменено: RamRiz - 01.01.2018 21:43:33
 
 
 
#19
01.01.2018 22:14:51
RamRiz,  При таком раскладе массивная
Код
=MIN(IF(F$7:F$8<>"";SUMIF(F$4:F$6;"<="&F$7:F$8;G$4:G$6)-G$7:G$8;""))
Лист1
Ну и если блок приходов и расходов так как показан, то для прихода/расхода больших 2х
Код
=MIN(IF(J$7:J$8<>"";SUMIF(J$4:J$6;"<="&J$7:J$8;K$4:K$6)-SUMIF(J$7:J$8;"<="&J$7:J$8;K$7:K$8);""))
лист2
и для произвольного порядка
Код
=IF(SUM(G4:G8)>0;MIN(IF(F$4:F$8<>"";SUMIFS(G$4:G$8;F$4:F$8;"<="&F$4:F$8;$E$4:$E$8;"<>-")-SUMIFS(G$4:G$8;F$4:F$8;"<="&F$4:F$8;$E$4:$E$8;"-");""));G$4)
Лист3


Изменено: БМВ - 02.01.2018 09:47:27
По вопросам из тем форума, личку не читаю.
 
 
 
#20
01.01.2018 22:53:50
Код
=МИН(МУМНОЖ(ТРАНСП($G$4:$G$8)*ТРАНСП($E$4:$E$8="Пополнение")*(ТРАНСП($F$4:$F$8)<=Период);G4:G8^0)-МУМНОЖ(ТРАНСП($G$4:$G$8)*ТРАНСП($E$4:$E$8="Снятие")*(ТРАНСП($F$4:$F$8)<=Период);G4:G8^0))
формула массива
где период
Код
=СТРОКА(ИНДЕКС(Лист1!$F:$F;МИН(Лист1!$F$4:$F$8)):ИНДЕКС(Лист1!$F:$F;МАКС(Лист1!$F$4:$F$8)))
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#21
01.01.2018 23:02:09
пополнения и снятия могут быть в произвольном порядке (могут быть вперемешку)
даты - в произвольном порядке
ограничение только 1: одна строка это 1 операция, КАЖДАЯ ОПЕРАЦИЯ должна содержать дату и сумму
допускаются другие типы операций (кроме Пополнение, Снятие) - они будут проигнорированы формулой
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#22
02.01.2018 09:47:02
Ігор Гончаренко, MMULT все никак времени нет более внимательно изучить.

если объединить эту идею

Цитата
БМВ написал:
=SUMPRODUCT(K4:K16*((LEFT(I4:I16;1)="П")*2-1))

и MMULT, то
Код
=MIN(MMULT(TRANSPOSE($G$4:$G$8)*TRANSPOSE((LEFT($E$4:$E$8;1)<>"С")*2-1)*(TRANSPOSE($F$4:$F$8)<=Период);G4:G8^0))
А для итоговой автора совсем просто
Код
=MIN(MMULT(TRANSPOSE($G$4:$G$8)*TRANSPOSE(($E$4:$E$8<>"-")*2-1)*(TRANSPOSE($F$4:$F$8)<=Период);G4:G8^0))
По вопросам из тем форума, личку не читаю.
 
 
 
#23
02.01.2018 12:22:51
БМВ,
Объясните пожалуйста разницу между листами в вашем комментарии http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=99794&amp...

в 1 листе все работает как надо, тогда чем отличается 2 и 3 лист?
 
 
 
#24
02.01.2018 12:53:28
Ігор Гончаренко,
Пытался ввести вашу формулу в мою таблицу, но выходит ошибка #ИМЯ?
Подскажите как сделать, чтобы она работала.
 
 
 
#25
02.01.2018 12:54:41
Цитата
Ігор Гончаренко написал:
формула массива
На это обратили внимание?
 
 
 
#26
02.01.2018 13:07:31
внимание нужно обратить на это:
где период:
Код
=СТРОКА(ИНДЕКС(Лист1!$F:$F;МИН(Лист1!$F$4:$F$8)):ИНДЕКС(Лист1!$F:$F;МАКС(Лист1!$F$4:$F$8)))
это имя обьявлено в диспетчере имен
или обьявите в диспечетре такую же переменную,
или скопируйте в формулу (2 раза) эту запись вместо слова период в ней.
суть формулы в том, что она за период от минимальной даты (операция с наименьшей датой) до максимальной даты для каждого дня определяет разницу сумм пололнений и снятий за все предыдущие дни. минимальное значение из них и есть минимальный размер остатка средств за период.
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#27
02.01.2018 14:25:15
Цитата
RamRiz написал:
Объясните пожалуйста разницу между листами
Лист3 - не важно сколько строк и не важен порядок приходов расходов. Главное чтоб были даты и расходы помечены минусом.
Лист2 - не важно сколько строк, но обязательна группировка приходы и расходы.
Лист1 - не более двух списаний за период и  приходы расходы сгруппированы

Ігор Гончаренко, на реальных данных Ваш вариант дает сбой.
По вопросам из тем форума, личку не читаю.
 
 
 
#28
02.01.2018 14:27:48
на каких? можно посмотреть (реальные данные)
формула не заточена под те 5 строк, которые есть в примере (но понятно, ссылвется на них)
формула заточена под решение задачи:
определить минимальный остаток средств на счете в результате серии его пополнений и снятий (задача решена шире чем звучит в заголовке "в теении месяца", там нет ограничений ра рассматриваемый период времени)
Изменено: Ігор Гончаренко - 02.01.2018 14:41:00
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#29
02.01.2018 14:39:38
Ігор Гончаренко, #18
По вопросам из тем форума, личку не читаю.
 
 
 
#30
02.01.2018 14:57:27
Цитата
БМВ написал: Лист3... Главное чтоб были даты и расходы помечены минусом.
Что значит "Чтобы были даты и расходы помечены минусом"? Расходы понятно, а даты для чего? и как их помечать?
Изменено: RamRiz - 02.01.2018 15:00:34
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх