Страницы: 1
RSS
ЕСЛИ данные в 6 последующих ячейках расположены в возрастающем порядке..., Писать макрос или формулами?
 
Думаю уже второй день. Решение пока не найдено. Помогите, пожалуйста.

1. Нужно брать данные из каждой ячейки листа 1 и искать точное совпадение в листе 2. Например,2019-04-012.

2. Найти данные в столбце В листа 2 напротив найденных данных. Напротив 2019-04-01 в Листе2 находится 31.553. Проанализировать 6 ячеек ниже найденной ячейки и ЕСЛИ данные в этих 6 последующих ячейках расположены в возрастающем порядке или убывающем не более чем на 1%, то закрасить исходную ячейку Листа 1 в зеленый цвет. (Если закрасить не получается, то дописать определенный текст в исходную ячейку к имеющемуся уже тексту, например, добавить "+1", тогда я смогу позже закрасить все ячейки с текстом "+1").

В приведенном примере данные в 6 последующих ячеек от найденной возрастают, следовательно исходная ячейка на Листе1 должна быть закрашена в заленый цвет (или добавлено "+1" к существующему тексту.

Файлы удалены - превышение допустимого размера вложения [МОДЕРАТОР]
 
Макрос нужно писать или формулами?
 
.
 
Доброе время суток.
artyrH, коллега, не уловил, а что сделано-то в вашем файле?
Изменено: Андрей VG - 14.07.2019 08:46:52
 
Андрей VG, здравствуйте. не то что надо тс
можно исправить: формулу из уф в столбец Н и на листе 1(так называемом) в уф формулу
Код
=ВПР(A2;data!$A$2:$H$4488;8;)
 
Цитата
Андрей VG написал:
что сделано-то в вашем файле?
Листы переименованы :-)

Цитата
tamilla8484 написал:
убывающем не более чем на 1%,
- это поясните.
Если просто по возрастающей то на листе 2 например =AND(B2:B6<B3:B7)  и протянуть, на основании этого УФ на листе 1.
По вопросам из тем форума, личку не читаю.
 
типа это надо. формулу в H2 протянуть
 
Цитата
БМВ написал:
=AND(B2:B6<B3:B7)
этого не достаточно для поиска низкой волатильности :)  
 
Цитата
artyrH написал:
низкой волатильности  
пока у меня низкая понимаемость что хотел ТС
в Вашей формуле, ну то что много лишних скобок, это как я понимаю, для наглядности, но проще, правда массивная становится.
PRODUCT((B2*99%<B3:B8)*(B3:B8<B2*101%)) или
=AND(B2*99%<B3:B8;B3:B8<B2*101%)
Изменено: БМВ - 14.07.2019 09:46:59
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
пока у меня низкая понимаемость что хотел ТС
Да вроде всё кристально ясно.
Для блока из шести последовательно идущих ячеек убедится, что есть только положительные и нулевые разности или только отрицательные и нулевые разности. Если выполнено  условие невозрастания или неубывания, то проверить, что максимальное абсолютное отклонение в разностях не превышает 1%.
До же для отношений. Убеждаемся что есть только отношения последующего к текущему меньше или равные единицы или только больше или равные единицы, и абсолютная разница отношения с 1 не превышает 1%.
Как-то так
Цитата
artyrH написал:
можно исправить: формулу из уф в столбец Н и на листе 1
спасибо, понял. Нашёл поиском по существующим Уф.
Изменено: Андрей VG - 14.07.2019 09:50:58
 
БМВ, правда
как минимум, мое решение облегчило, в какой то степени, понимание задачи)
а так я за оптимизацию решений)
 
Цитата
Андрей VG написал:
максимальное абсолютное отклонение в разностях не превышает 1%
это, наверное, достаточно . к значению, соответствующей дате
тс че то молчит
Изменено: artyrH - 14.07.2019 09:57:55
 
artyrH, размер файла...
 
vikttur, с протянутой формулой не уложился в рамки, а без протяжки - 261кб  
 
Цитата
Андрей VG написал: Да вроде всё кристально ясно.
возможно, но убывания прямого не описано, а вот
Цитата
tamilla8484 написал: убывающем не более чем на 1%
меня вводит в недопонимание. 1% от первой (как это у artyrH) или от предыдущей.
По вопросам из тем форума, личку не читаю.
 
Цитата
artyrH написал: с протянутой формулой не уложился в рамки
Да я не о границе. Конечно, косяк автора, но  неужели для ответа нужен такой объем и недостаточно 20-30 кБ?
 
Цитата
БМВ написал:
меня вводит в недопонимание. 1% от первой (как это у  artyrH ) или от предыдущей.
Ну, тут, Михаил, каждый ТС ожидает, что отвечать на вопрос будет корифей того же профиля, что и ТС, а не некоторые любопытствующие, пусть и не забывшие математику :)  Некоторые так об этом явно пишут. А если это корифей, то зачем ему писать об очевидном в данной отрасли знания - только по напрасну пальцы об клавиатуру отбивать - туннельный синдром приобретать?
Только вот часто ли сюда для оказания помощи заглядывают такие личности?
Изменено: Андрей VG - 14.07.2019 17:00:26
 
Уважаемые знатоки! :) Спасибо за подсказки и решение проблемы.

Формулу я поняла и немного модифицировала:
Код
  =(--ISNUMBER(AGGREGATE(15,6,ROW($A$2:$A$27)/(Возрастающий!$A$2:$AO$27=A2),1))=1)*((B2[COLOR=#ff0000][B]<=[/B][/COLOR]B3*101%)*(B2<=B4*101%)*(B2<=B5*101%)*(B2<=B6*101%)*(B2<=B7*101%)*(B2<=B8*101%)*(B2<=B9*101%)*(B2<=B10*101%))

Работает все отлично. Остался один маленький нерешенный вопрос.

Иногда на листе data отстствует искомая дата (выходных и праздников нет в списке) и она не анализируется по формуле. В таких случаях необходимо, чтобы формула была применена к предыдущей ближайшей дате.
Как показано на примере (скриншот): На странице Возрастающий есть дата 2004-08-09 и 2004-09-05, которые отсутствуют на листе data. В этом случае необходимо, чтобы формула была применена на предыдущие ближайшие даты: на 2004-08-27 и 2004-09-03 соответственно.

Как это сделать?
 
Прикрепляю сокращенный файл, чтобы вписаться в лимит.
 
tamilla8484, к вам здесь столько вопросов..
 
Цитата
tamilla8484 написал:
Формулу я поняла
сомневаюсь, ибо если б поняли и поняли #9 то сильно она сокращается. А вопросов, да, много.

artyrH, А для чего эта часть?
=(--ISNUMBER(AGGREGATE(15,6,ROW($A$2:$A$27)/(Возрастающий!$A$2:$AO$27=A2),1))=1) .  Ну то что записать можно просто так
=ISNUMBER(AGGREGATE(15,6,ROW($A$2:$A$27)/(Возрастающий!$A$2:$AO$27=A2),1) , так как следом умножение, то и сравнение и -- не нужны. но я смысла применительно к задаче не уловил. Красить на втором листе вроде задачи не было, а тяжелее расчет серьезно.
Изменено: БМВ - 15.07.2019 07:50:50
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
для чего эта часть?
все просто. сначала я понял задачу так что нужно подсветить значения на втором листе. выложил на форум и понял что нужно подсветить даты на первом листе. по быстрому исправился в #7, не удалив эту часть. так и прижилась она) я согласен что ваша формула с произвед лаконичнее
к тому же моя формула прошла модификацию :D  
 
Вот так я поняла формулу, которая работает:
Код
=(--ISNUMBER(AGGREGATE(15,6,ROW($A$2:$A$27)/(Возрастающий!$A$2:$AO$27=A2),1))=1)*
((B2<=B3*101%)*(B2<=B4*101%)*(B2<=B5*101%)*(B2<=B6*101%)*(B2<=B7*101%)*(B2<=B8*101%)*(B2<=B9*101%)*(B2<=B10*101%))

ISNUMBER - ищет цифровые значения
AGREGATE - пропускает ошибки на листе Возрастающий, в пределах $A$2:$A$27
$A$2:$AO$27=A2 - данные из диапазона на Возрастающем листе ищет на листе data  в пределах колонки А2
А дальше формула последовательности не больше одного процента... B2<=B3*101%  

Попоследнему моему вопросу о предыдущих датах - ответ нашелся сам собой, поковырялась в формуле для форматирования на листе Возрастающий и модифицировала немного ее, а именно добавила TRUE:
Код
 =VLOOKUP(A2,data!$A$2:$H$4488,8,TRUE)   

Теперь отсутствующие даты на листе data не пропускаются, а ищется предыдущее ближайшее значение. Работает!!

Спасибо всем. Очень оперативно получилось :)
 
Цитата
tamilla8484 написал:
Вот так я поняла формулу, которая
вот я и говорю, что не поняли.
artyrH, ну я так приблизительно и думал, тогда надо было просто через countif . Важен факт наличия, а порядок нет.
Изменено: БМВ - 15.07.2019 08:34:39
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
факт наличия
и вправду с счетесли уф работает. а я привык все с агрегат решать)
с другой стороны, чем толще формула, тем лучше продается. сделаешь с впр и скажешь "с вас 500 рублей" - в ответ получишь "а что вы сделали? за что такая сумма? у вас там формула всего в 17 символов!!". а в случае с двух и более -этажной формулой получишь 500рэ без ебэ)
 
Цитата
artyrH написал:
в ответ получишь "а что вы сделали? за что такая сумма? у вас там формула всего в 17 символов!!".
Ответ - просто каждый символ по 30р :-)

для H2  =PRODUCT(--(B2<=B3:B10*101%)) массивная
Для УФ на листе 2 =COUNTIF(Возрастающий!$A$2:$AO$27;A2)*PRODUCT(--(B2<=B3:B10*101%))
Для уф на листе 1 или VLOOKUP, как реализовано
, или в имена  =PRODUCT(--(INDEX(data!$B:$B;MATCH(Возрастающий!B23;data!$A:$A;1);)<=INDEX(data!$B:$B;MATCH(Возрастающий!B23;data!$A:$A;1)+1):INDEX(data!$B:$B;MATCH(Возрастающий!B23;data!$A:$A;1)+8)*101%)) , при этом без доп. столбца на листе 2.
Изменено: БМВ - 15.07.2019 10:00:00
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх