Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Попадает ли дата в диапазон
 
#1
13.12.2017 18:49:44
Расскажу о общей цели: Хочу сделать универсальный шаблон для решения задач на банковскую тему.
Мне нужно чтобы выполнялось такое условие: берется на проверку несколько дат. На каждую дату стоит сумма операции.
Снятие120.04.20174500,00   ₽
Снятие231.05.201710000,00 ₽
Покупка115.04.2017500,00 ₽
Покупка217.04.2017500,00 ₽
Мне нужно, чтобы все даты сверялись с диапазоном
13.04.2017-13.05.2017
Если одна из дат входит в диапазон, то в ячейке стоит сумма операции на эту дату. Если же в диапазон входит несколько дат, то в ячейке стоит общая сумма.
К примеру в данном примере в диапазон входит 3 даты, в ячейке будет отображаться сумма 5500.

Я делаю шаблон, который будет мне помогать решать задачи с разными условиями. Поэтому мне нужна формула, которая будет учитывать изменения в датах.
Я пытался использовать функцию Если, И, ИЛИ. Но формула получалась очень длинной.
=ЕСЛИ(ИЛИ(E93<B93;B93<G93;E93<B94;B94<G93;E93<B95;B95<G93;E93<B96;B96<G93);ИЛИ(C93+C94+C95+C96);ЕСЛИ(И(E93<B93;B93<G93);C93;ЕСЛИ(И(E93<B94;B94<G93);C94;0)))
И это еще неполная формула. Поэтому я прошу вас помочь сделать ее меньше и универсальней. Спасибо.
Изменено: RamRiz - 13.12.2017 18:52:01
 
 
 
#2
13.12.2017 18:56:38
Формула для "G6":
=СУММЕСЛИМН(C5:C8;B5:B8;">="&F5;B5:B8;"<="&H5)
 
 
 
#3
13.12.2017 18:57:29
Посмотрите функцию суммеслимного sumifs
По вопросам из тем форума, личку не читаю.
 
 
 
#4
13.12.2017 19:00:10
Код
=СУММПРОИЗВ(($B$5:$B$8>=F5)*($B$5:$B$8<=H5)*$C$5:$C$8)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#5
13.12.2017 19:08:55
Karataev, я в шоке, настолько короткая формула, и самое главное рабочая :) Спасибо большое, сам убил несколько часов в попытках сделать что то подобное с использованием функций Если.
Ігор Гончаренко, Спасибо за другой вариант. С массивами вообще не дружу, надо будет почитать о них)

Можете объяснить есть ли разница в этих формулах? Они абсолютно одинаковые? Или у какой то из них есть больше преимуществ.
 
 
 
#6
13.12.2017 19:20:09
это не массивная формула
и записав ее в G6, потом G6 можно скопировать в I6 и K6 и дальше, когда заполните N)) и дальше
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#7
13.12.2017 19:24:49
СУММЕСЛИМН должно быстрее работать и не должно нагружать файл.
Использование в данном случае "СУММПРОИЗВ" не понятно, зачем надо. Обычно такую функцию используют, если нет подходящей функции.
 
 
 
#8
13.12.2017 19:33:28
Цитата
Karataev написал:
СУММЕСЛИМН должно быстрее работать и не должно нагружать файл.
Вопрос спорный, и подлежит тестированию и замерам. Но в данном конкретном случае , думаю Игорь перестарался.
По вопросам из тем форума, личку не читаю.
 
 
 
#9
13.12.2017 19:35:52
Понял. Спасибо.
Дальше возник такой вопрос.
К примеру
20.04.2017    4500,00   ₽
31.05.2017    10000,00 ₽
16.04.2017    500,00 ₽
12.05.2017    500,00 ₽
Нужна такая формула, которая определит что 16/04/17 была совершена первая операция.
Я опять же пытался использовать функцию Если. Но когда используются много вариантов, то формула получается сложной и большой.
=ЕСЛИ(B5<B7;C5-H9-(J13-H13);ЕСЛИ(B7<B5;C7-H9-(J13-H13);0)) и есть сомнения в его универсальности.
Изменено: RamRiz - 13.12.2017 19:38:13
 
 
 
#10
13.12.2017 19:38:10
используйте =МИН
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#11
13.12.2017 19:38:56
БМВ, использовании массивных формул вроде всегда нагружает файл. Сама по себе функция "СУММПРОИЗВ" не массивная (вводится без Ctrl+Shift+Enter), но смысл от этого не меняется, просто не надо нажимать сочетание клавиш Ctrl+Shift+Enter.
 
 
 
#12
13.12.2017 19:41:01
БМВ,
я данной ситуации вообще не старался (решение в фоновом режиме). склонен к мысли, что СУММЕСЛИМН - быстрее (понятно, что не на этих данных замерять)))
Изменено: Ігор Гончаренко - 13.12.2017 19:41:34
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#13
13.12.2017 19:42:40
Ігор Гончаренко,
Вы не могли бы написать подробней.
Я пишу =ЕСЛИ(МИН(B5;B6;B7;B8);
А дальше не пойму что писать в значении "Истина"?
Изменено: RamRiz - 13.12.2017 19:45:37
 
 
 
#14
13.12.2017 19:51:41
Код
=МИН(B5:B8)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#15
13.12.2017 19:51:43
Сейчас эту пытался сделать. Чувствую иду в правильном направлении, вот только почему не получается.
=ВПР(МИН(B93;B94;B95;B96);B93:C96;2;1)
 
 
 
#16
13.12.2017 19:55:30
Ігор Гончаренко, Спасибо. Чуть поковырялся и получилось.
=ВПР(МИН(B93:B96);B93:C96;2;0)
Тперь при поиске наименьшей даты, автоматически отображается сумма которая была совершена в эту дату.
 
 
 
#17
13.12.2017 19:58:05
Второй вопрос не по теме.
 
 
 
#18
13.12.2017 20:08:14
RamRiz,
понимаете, найти дату первой операции и сумму первой операции - это две большие разницы!
спрашивае6те:
Цитата
Нужна такая формула, которая определит что 16/04/17 была совершена первая операция.
а ищете потом сумму первой операции
Изменено: Ігор Гончаренко - 13.12.2017 20:14:07
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#19
13.12.2017 20:17:58
Ігор Гончаренко, Извините,  не так выразился.  
Можете написать аналог моей формулы,  только в другом варианте?  Мне для общего понимания,  чтобы знать как еще это можно сделать.  
 
 
 
#20
13.12.2017 20:19:26
vikttur, Как будет возникать новый вопрос, мне нужно будет создавать новую тему? Нельзя для этого использовать эту?  
 
 
 
#21
13.12.2017 20:19:49
RamRiz, о чем тема и о чем вопрос?
 
 
 
#22
13.12.2017 20:20:32
Offtop
Karataev,  Какая разница, массивная или нет, нагрузку может показать только тест или исходники этих функций. Первые покажут абсолютный результат, а второе методику.В любом случае расчет функции ведут с использованием внутренних массивов, а то что передается Range или Array в функцию, так это не сильно нагрузит.

UPD: Под рукой оказался файл с датами и временим на 300000 строк. Раницу почувствовал при множественных расчетах одного и то гоже и не в пользу SUMPRODUCT, при этом что в сравнении с SUMIFS, SUMIF и SUM.  
Изменено: БМВ - 13.12.2017 20:47:16
По вопросам из тем форума, личку не читаю.
 
 
 
#23
13.12.2017 20:21:13
vikttur, Понял,  новый вопрос- новая тема)
Спасибо всем отписавшимся.  
Изменено: RamRiz - 13.12.2017 20:22:10
 
 
 
#24
13.12.2017 20:37:28
БМВ, да, надо создать тестовый файл. Я сейчас попробовал сделать и не смог - массивные функции тоже быстро работают. Может быть Excel стал лучше работать, пропали проблемы с массивами.
 
 
 
#25
13.12.2017 20:56:54
Karataev,  я выше про тест написал. В целом вывод такой: SUMPRODUCT применять там, где надо, и не применять там, где ненадо :-)
По вопросам из тем форума, личку не читаю.
 
 
 
#26
14.12.2017 09:51:46
Доброе утро!
Возникла новая проблема с теми же датами.
Мне нужно определить комиссию за снятие по операциям, которые происходили в определенный промежуток времени.
Снятие1   20.04.2017   4 500,00   ₽
Снятие2   21.04.2017   10 000,00 ₽
Покупка1    0    0
Покупка2    0    0
Нужна формула, которая определит что эти даты входят в промежуток от 13/04/17 до 13/05/17.
И если входят, то комиссия по этим операциям должна составить (4500 +10 000)*5.9%. Если одна из дат не будет входить в диапазон, тогда только с одной операции будет взиматься комиссия.
Я пытался использовать формулу =ЕСЛИ(И(B5>=E5;B5<E5+F5);C5*5,9%;0).
Но она слишком простая и не универсальная. Работает только на одну дату. Если попытаться включить туда несколько, то будет как обычно большая сложная формула.


В прикрепленном файле ячейку H11 нужно вставить эту формулу.
Изменено: RamRiz - 14.12.2017 09:52:39
 
 
 
#27
14.12.2017 10:08:01
Придумал такую формулу.
=ЕСЛИ(СУММПРОИЗВ((B5:B6>=E5)*(B5:B6<=G5)*C5:C6);СУММПРОИЗВ((B5:B6>=E5)*(B5:B6<=G5)*C5:C6)*5,9%;)

Нашел в ней не состыковки.
Если Дата 2 не входит в диапазон, то он все равно использует в решении (C5:C6)*5,9%, наверное это потому что я не написал ничего в значении "ЛОЖЬ", но формула опять получается сложной и длинной. Есть простой аналог?
Изменено: RamRiz - 14.12.2017 10:36:40
 
 
 
#28
14.12.2017 10:56:42
Все таки нашел универсальную формулу.
=ЕСЛИ(СУММПРОИЗВ((E5<=B5:B6)*(B5:B6<=G5)*C5:C6);СУММПРОИЗВ((E5<=B5:B6)*(B5:B6<=G5)*C5:C6)*5,9%;ЕСЛИ(И(E5<=B5;B5<=G5);C5*5,9%;ЕСЛИ(И(E5<=B6;B6<=G5);C6*5,9%;)))
Все хорошо работает. Но формула большая. Есть более красивые аналоги? :)
 
 
 
#29
14.12.2017 10:58:20
RamRiz, а какой результат должен быть в итоге? 855.5? или 900?
 
 
 
#30
14.12.2017 11:10:28
sbirliko,
Просто размер комиссии округляется до сотых в большую сторону. поэтому я там стрелочку провел и отдельно указал округление.
Ответ должен быть в ячейке H11 - 855.5
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх