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

Вход

Регистрация

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

 

= Мир MS Excel/Количество изменений цен по двум критериям (дате и времени) - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Количество изменений цен по двум критериям (дате и времени) (Формулы/Formulas)
Количество изменений цен по двум критериям (дате и времени)
Egyptian Дата: Понедельник, 10.05.2021, 19:42 | Сообщение № 1
Группа: Проверенные
Ранг: Ветеран
Сообщений: 512
Репутация: 184 ±
Замечаний: 0% ±

Excel 2013/2016
Доброго времени суток.
Никак не могу справится с одной задачей. Есть таблица с ценами, датами и временем. Нужно посчитать сколько раз изменилась цена в заданный промежуток времени с привязкой к конкретной дате.
Например в ячейках А2:А5 она с 8 до 9 утра изменилась дважды. Начальная цена - 9700 увеличилась до 9800 и еще разок увеличилась до 9825. По времени регистрируется еще одно изменение, но поскольку на цене это не отразилось то изменение не засчитывается.
В таблице нужные значения прописал вручную. Спасибо.
К сообщению приложен файл: Price_Change.xlsx (9.6 Kb)


Сообщение отредактировал Egyptian - Понедельник, 10.05.2021, 21:23
 
Ответить
СообщениеДоброго времени суток.
Никак не могу справится с одной задачей. Есть таблица с ценами, датами и временем. Нужно посчитать сколько раз изменилась цена в заданный промежуток времени с привязкой к конкретной дате.
Например в ячейках А2:А5 она с 8 до 9 утра изменилась дважды. Начальная цена - 9700 увеличилась до 9800 и еще разок увеличилась до 9825. По времени регистрируется еще одно изменение, но поскольку на цене это не отразилось то изменение не засчитывается.
В таблице нужные значения прописал вручную. Спасибо.

Автор - Egyptian
Дата добавления - 10.05.2021 в 19:42
Pelena Дата: Понедельник, 10.05.2021, 21:49 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19158
Репутация: 4411 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Может, так подойдёт?
Код
=СУММПРОИЗВ(($B$2:$B$16=E2)*($C$2:$C$16>=F2)*($C$2:$C$16<G2)*($A$2:$A$16<>$A$3:$A$17))-1


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Может, так подойдёт?
Код
=СУММПРОИЗВ(($B$2:$B$16=E2)*($C$2:$C$16>=F2)*($C$2:$C$16<G2)*($A$2:$A$16<>$A$3:$A$17))-1

Автор - Pelena
Дата добавления - 10.05.2021 в 21:49
Egyptian Дата: Понедельник, 10.05.2021, 21:58 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 512
Репутация: 184 ±
Замечаний: 0% ±

Excel 2013/2016
Здравствуйте, Елена. Честно говоря я уже пробовал этот вариант, но он работает только для первых четырех строк.
Пробовал также:
Код
=COUNT(IF(COUNTIFS($A$2:$A$16,$A$2:$A$16,$B$2:$B$16,E2,$C$2:$C$16,">="&F2,$C$2:$C$16,"<="&G2)>1,COUNTIFS($A$2:$A$16,$A$2:$A$16,$B$2:$B$16,E2,$C$2:$C$16,">="&F2,$C$2:$C$16,"<="&G2)))

Но эта формула выдает 0 вместо изменений.
 
Ответить
СообщениеЗдравствуйте, Елена. Честно говоря я уже пробовал этот вариант, но он работает только для первых четырех строк.
Пробовал также:
Код
=COUNT(IF(COUNTIFS($A$2:$A$16,$A$2:$A$16,$B$2:$B$16,E2,$C$2:$C$16,">="&F2,$C$2:$C$16,"<="&G2)>1,COUNTIFS($A$2:$A$16,$A$2:$A$16,$B$2:$B$16,E2,$C$2:$C$16,">="&F2,$C$2:$C$16,"<="&G2)))

Но эта формула выдает 0 вместо изменений.

Автор - Egyptian
Дата добавления - 10.05.2021 в 21:58
Pelena Дата: Понедельник, 10.05.2021, 22:01 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 19158
Репутация: 4411 ±
Замечаний: ±

Excel 365 & Mac Excel
Видимо, нужен файл с бОльшим количеством примеров, особенно, там, где не работает, чтобы понять логику


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВидимо, нужен файл с бОльшим количеством примеров, особенно, там, где не работает, чтобы понять логику

Автор - Pelena
Дата добавления - 10.05.2021 в 22:01
Egyptian Дата: Понедельник, 10.05.2021, 22:19 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 512
Репутация: 184 ±
Замечаний: 0% ±

Excel 2013/2016
Нет, логика очень простая. Нужно просто посчитать количество изменений цены по времени, но если изменение по времени зафиксированно, а сама цена не меняется, то изменение не считается.
Напишу на примере первой и третьей четверки строк.
Первая четверка: на 26 июня дается часовой промежуток времени с 8 до 9 утра.
Начальная цена на 8:45:35 - 9700. В 8:45:37 она меняется на 9800 - это первое изменение. Затем в 8:50:06 она снова меняется на 9825 - это второе изменение. Через пять секунд снова фиксируется изменение по времени, но цена не меняется, поэтому изменение не засчитывается. Итого: 2 изменения за час в с 8 до 9 утра на 26 июня. Все.
Все абсолютно тоже самое для третьей четверки строк и остальных тоже. С 9950 цена меняется на 10000 (это один), затем меняется на 10050 (это два), затем снова меняется на 10000 (это три). Итого 3.
Если изменения по времени идут подряд, но не отражаются на цене (как в ячейках А4:А5), то они не фиксируются. Надеюсь, так будет понятнее.


Сообщение отредактировал Egyptian - Понедельник, 10.05.2021, 22:21
 
Ответить
СообщениеНет, логика очень простая. Нужно просто посчитать количество изменений цены по времени, но если изменение по времени зафиксированно, а сама цена не меняется, то изменение не считается.
Напишу на примере первой и третьей четверки строк.
Первая четверка: на 26 июня дается часовой промежуток времени с 8 до 9 утра.
Начальная цена на 8:45:35 - 9700. В 8:45:37 она меняется на 9800 - это первое изменение. Затем в 8:50:06 она снова меняется на 9825 - это второе изменение. Через пять секунд снова фиксируется изменение по времени, но цена не меняется, поэтому изменение не засчитывается. Итого: 2 изменения за час в с 8 до 9 утра на 26 июня. Все.
Все абсолютно тоже самое для третьей четверки строк и остальных тоже. С 9950 цена меняется на 10000 (это один), затем меняется на 10050 (это два), затем снова меняется на 10000 (это три). Итого 3.
Если изменения по времени идут подряд, но не отражаются на цене (как в ячейках А4:А5), то они не фиксируются. Надеюсь, так будет понятнее.

Автор - Egyptian
Дата добавления - 10.05.2021 в 22:19
Pelena Дата: Понедельник, 10.05.2021, 22:35 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19158
Репутация: 4411 ±
Замечаний: ±

Excel 365 & Mac Excel
Тогда не понятно, чем моя формула не устраивает
К сообщению приложен файл: 1263575.xlsx (9.9 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеТогда не понятно, чем моя формула не устраивает

Автор - Pelena
Дата добавления - 10.05.2021 в 22:35
Egyptian Дата: Понедельник, 10.05.2021, 22:48 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 512
Репутация: 184 ±
Замечаний: 0% ±

Excel 2013/2016
Pelena, Честно говоря я в шоке. Ваша формула работает! Я просто не понимаю, ведь я использовал точно такую же конструкцию, разве что вот эта часть
Код
($A$2:$A$16<>$A$3:$A$17)
была в начале. Просчитывались только первые 4 строки, дальше нет! А сейчас все ОК! Чудеса, да и только!
Спасибо!
 
Ответить
СообщениеPelena, Честно говоря я в шоке. Ваша формула работает! Я просто не понимаю, ведь я использовал точно такую же конструкцию, разве что вот эта часть
Код
($A$2:$A$16<>$A$3:$A$17)
была в начале. Просчитывались только первые 4 строки, дальше нет! А сейчас все ОК! Чудеса, да и только!
Спасибо!

Автор - Egyptian
Дата добавления - 10.05.2021 в 22:48
Светлый Дата: Понедельник, 10.05.2021, 23:15 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Неоптимизированная массивная:
Код
=СЧЁТ(1/(ИНДЕКС(A:A;Ч(ИНДЕКС(НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);СТРОКА($1:$15));)))-ИНДЕКС(A:A;Ч(ИНДЕКС(НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);СТРОКА($1:$15))+1;)))))-1


Программировать проще, чем писать стихи.
 
Ответить
СообщениеНеоптимизированная массивная:
Код
=СЧЁТ(1/(ИНДЕКС(A:A;Ч(ИНДЕКС(НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);СТРОКА($1:$15));)))-ИНДЕКС(A:A;Ч(ИНДЕКС(НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);СТРОКА($1:$15))+1;)))))-1

Автор - Светлый
Дата добавления - 10.05.2021 в 23:15
Egyptian Дата: Понедельник, 10.05.2021, 23:40 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 512
Репутация: 184 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый, Ваша формула тоже замечательно работает, спасибо!
Кстати, я кажется разобрался в чем была моя ошибка. Я по невнимательности менял уникальные цены, само собой счет не менялся...
Всем еще раз большое спасибо!
 
Ответить
СообщениеСветлый, Ваша формула тоже замечательно работает, спасибо!
Кстати, я кажется разобрался в чем была моя ошибка. Я по невнимательности менял уникальные цены, само собой счет не менялся...
Всем еще раз большое спасибо!

Автор - Egyptian
Дата добавления - 10.05.2021 в 23:40
Светлый Дата: Вторник, 11.05.2021, 00:48 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Немного оптимизировал:
Код
=СЧЁТ(1/МУМНОЖ(ЕСЛИОШИБКА(-ИНДЕКС(A:A;Ч(ИНДЕКС({0;-1}+НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);СТРОКА($1:$15));))););{1:-1}))-1
но сомневаюсь в правильности. Надо на большем количестве данных проверить.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеНемного оптимизировал:
Код
=СЧЁТ(1/МУМНОЖ(ЕСЛИОШИБКА(-ИНДЕКС(A:A;Ч(ИНДЕКС({0;-1}+НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);СТРОКА($1:$15));))););{1:-1}))-1
но сомневаюсь в правильности. Надо на большем количестве данных проверить.

Автор - Светлый
Дата добавления - 11.05.2021 в 00:48
Egyptian Дата: Вторник, 11.05.2021, 01:34 | Сообщение № 11
Группа: Проверенные
Ранг: Ветеран
Сообщений: 512
Репутация: 184 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Светлый, 11.05.2021 в 00:48, в сообщении № 10 ()
но сомневаюсь в правильности.

Проверил, есть пара моментов, если к примеру в ячейке А8 заменить цену 9900 на 9950 (да та 26 июня, период с 12:00 до 13:00) то лишь формула с MMULT правильно фиксирует изменение цены и выдает 1. Другие формулы выдают 0. И при этом эта же формула начинает неправильно подсчитывать кол-во изменений на 27/06/2019 в период с 8 до 9 часов.
Выдает 2, должно быть 3.
К сообщению приложен файл: 1111.xlsx (10.0 Kb)


Сообщение отредактировал Egyptian - Вторник, 11.05.2021, 02:31
 
Ответить
Сообщение
Цитата Светлый, 11.05.2021 в 00:48, в сообщении № 10 ()
но сомневаюсь в правильности.

Проверил, есть пара моментов, если к примеру в ячейке А8 заменить цену 9900 на 9950 (да та 26 июня, период с 12:00 до 13:00) то лишь формула с MMULT правильно фиксирует изменение цены и выдает 1. Другие формулы выдают 0. И при этом эта же формула начинает неправильно подсчитывать кол-во изменений на 27/06/2019 в период с 8 до 9 часов.
Выдает 2, должно быть 3.

Автор - Egyptian
Дата добавления - 11.05.2021 в 01:34
Светлый Дата: Вторник, 11.05.2021, 10:18 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Формулы исправил. Массивные:
Код
=СЧЁТ(1/(ИНДЕКС(A:A;Ч(ИНДЕКС(НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);СТРОКА($1:$15));)))-ИНДЕКС(A:A;Ч(ИНДЕКС(НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);СТРОКА($1:$15)+1);)))))
Код
=СЧЁТ(1/МУМНОЖ(ЕСЛИОШИБКА(-ИНДЕКС(A:A;Ч(ИНДЕКС(НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);{0;1}+СТРОКА($1:$16));))););{1:-1}))-1
К сообщению приложен файл: 1111-1.xlsx (11.7 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеФормулы исправил. Массивные:
Код
=СЧЁТ(1/(ИНДЕКС(A:A;Ч(ИНДЕКС(НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);СТРОКА($1:$15));)))-ИНДЕКС(A:A;Ч(ИНДЕКС(НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);СТРОКА($1:$15)+1);)))))
Код
=СЧЁТ(1/МУМНОЖ(ЕСЛИОШИБКА(-ИНДЕКС(A:A;Ч(ИНДЕКС(НАИБОЛЬШИЙ((B$2:B$16=E2)*(C$2:C$16>=F2)*(C$2:C$16<=G2)*СТРОКА($2:$16);{0;1}+СТРОКА($1:$16));))););{1:-1}))-1

Автор - Светлый
Дата добавления - 11.05.2021 в 10:18
Egyptian Дата: Вторник, 11.05.2021, 10:46 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 512
Репутация: 184 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый, Потестил на рабочем файле (там тоже не очень много записей) теперь вроде все корректно. Еще раз спасибо!
 
Ответить
СообщениеСветлый, Потестил на рабочем файле (там тоже не очень много записей) теперь вроде все корректно. Еще раз спасибо!

Автор - Egyptian
Дата добавления - 11.05.2021 в 10:46
Светлый Дата: Вторник, 11.05.2021, 11:57 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Всё-таки добил простую формулу:
Код
=СУММПРОИЗВ(($B$2:$B$17=E2)*($C$2:$C$17>=F2)*($C$2:$C$17<G2)*($B$3:$B$18=E2)*($C$3:$C$18>=F2)*($C$3:$C$18<G2)*(A$2:A$17<>A$3:A$18))
*Или массивная:
Код
=СЧЁТ(1/($B$2:$B$17=E2)/($C$2:$C$17>=F2)/($C$2:$C$17<G2)/($B$3:$B$18=E2)/($C$3:$C$18>=F2)/($C$3:$C$18<G2)/(A$2:A$17<>A$3:A$18))
**Может быть, даже эта будет работать:
Код
=СЧЁТ(1/($B$2:$B$17=E2)/($C$2:$C$17>=F2)/($C$3:$C$18>=F2)/($C$3:$C$18<G2)/(A$2:A$17<>A$3:A$18))


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 11.05.2021, 12:15
 
Ответить
СообщениеВсё-таки добил простую формулу:
Код
=СУММПРОИЗВ(($B$2:$B$17=E2)*($C$2:$C$17>=F2)*($C$2:$C$17<G2)*($B$3:$B$18=E2)*($C$3:$C$18>=F2)*($C$3:$C$18<G2)*(A$2:A$17<>A$3:A$18))
*Или массивная:
Код
=СЧЁТ(1/($B$2:$B$17=E2)/($C$2:$C$17>=F2)/($C$2:$C$17<G2)/($B$3:$B$18=E2)/($C$3:$C$18>=F2)/($C$3:$C$18<G2)/(A$2:A$17<>A$3:A$18))
**Может быть, даже эта будет работать:
Код
=СЧЁТ(1/($B$2:$B$17=E2)/($C$2:$C$17>=F2)/($C$3:$C$18>=F2)/($C$3:$C$18<G2)/(A$2:A$17<>A$3:A$18))

Автор - Светлый
Дата добавления - 11.05.2021 в 11:57
Egyptian Дата: Вторник, 11.05.2021, 14:44 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 512
Репутация: 184 ±
Замечаний: 0% ±

Excel 2013/2016
Да, добили. Двойные условия - я вот до такого бы точно не додумался, возьму на вооружение)
Две отработали как надо, последняя изначально считает некорректно, но, уже не суть.
 
Ответить
СообщениеДа, добили. Двойные условия - я вот до такого бы точно не додумался, возьму на вооружение)
Две отработали как надо, последняя изначально считает некорректно, но, уже не суть.

Автор - Egyptian
Дата добавления - 11.05.2021 в 14:44
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Количество изменений цен по двум критериям (дате и времени) (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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