Страницы: 1
RSS
Разница между МАКС и МИН в серии чисел между значениями =0
 
Добрый день.
Опять не могу решить задачку. Очень прошу помочь!

Суть проблемы:
Есть столбец с числовым рядом, где серии значений >0 и =0.
В серии значений >0 необходимо найти разницу между максимальным и минимальным значением.
 
так наверное
Код
=АГРЕГАТ(14;6;(A2:A9)/(A2:A9>0);1)-АГРЕГАТ(15;6;(A2:A9)/(A2:A9>0);1)
=АГРЕГАТ(14;6;(A12:A19)/(A12:A19>0);1)-АГРЕГАТ(15;6;(A12:A19)/(A12:A19>0);1)
 
С доп.столбцом (но можно и без него, просто лень городить)
F1 творит чудеса
 
copper-top, copper-top, не совсем понял - это в одной ячейке?
 
Цитата
Максим Зеленский написал:
С доп.столбцом (но можно и без него, просто лень городить)
Спасибо огромное,
попробую разобраться и объединить в одном столбце. Хотя гляжу на формулу как баран на новые ворота. Сильно сложная формула для моего уровня знания Excel.
Изменено: akc75 - 04.04.2017 19:44:04
 
Цитата
akc75 написал: в одной ячейке?
в разных, только у меня неверный вариант.
 
Все равно спасибо, за желание помочь!
 
Цитата
akc75 написал:
Сильно сложная формула для моего уровня знания Excel.
сначала считаем номер последней строки с 0, расположенной выше.
на примере расчета для 9 строки:
Код
=ЕСЛИ(
    (A10=0)*(A9<>0); ' тут определяем, что это последняя ячейка - после нее идет 0, но она не равна 0.
    МАКС(СТРОКА($A$1:A9)*(($A$1:A9=0)+ЕТЕКСТ($A$1:A9)));
        ' самая сложная часть:
        ' (1) берем массив номеров строк от 1 до текущей (9), получаем {1;2;3;4;5;6;7;8;9}
        ' (2) проверяем значения в ячейках столбца А от 1 до текущей на следующие условия: =0 ИЛИ это текст.
        ' Получаем для (2) следующий резульат: {ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}
        ' умножаем (1) на (2), при этом ИСТИНА и ЛОЖЬ становятся 1 и 0.
        ' результат умножения: {1;2;0;0;0;0;0;0;0}
        ' Считаем МАКС по этому массиву, получаем 2.
    0) ' ну или возвращаем 0, если условие не выполнено

Потом проще: если значение предыдущего расчета равно 0, то пишем пустую строку.
Если оно не равно 0, то значит оно равно номеру строки с последним 0/текстом выше.
Используем функцию СМЕЩ для получения диапазона от A1, смещенного на наше число (для этого примера новый диапазон начнется с А3), без смещения по столбцам, высотой равной (номер текущей строки) - результат предыдущего расчета. Текущая строка равна 9, 9-2 =7, получаем в итоге диапазон А3:А9.
Вот по нему считаем МАКС и МИН, чего и хотели.

Можно на самом деле написать более правильную, нелетучую, через ИНДЕКС+ПОИСКПОЗ, например, или тот же АГРЕГАТ, но немного лень :)
F1 творит чудеса
 
Ребята, а посмотрите пожалуйста, почему такая формула не работает.
=ЕСЛИ(И(A2>0;A3=0);МАКС(СМЕЩ(A2;МАКС(ЕСЛИ($A$1:A2=0;СТРОКА($A$1:A2)))-СТРОКА();;СТРОКА()-МАКС(ЕСЛИ($A$1:A2=0;СТРОКА($A$1:A2)))+1));"")
На одном из этапов вычисления, значения для СМЕЩ берутся в фигурные скобки, следующий шаг выдает ошибку.
Первое ЕСЛИ(И()) использую для отображения результата в нужном месте. Если это убрать, то работает корректно.
=МАКС(СМЕЩ(A2;МАКС(ЕСЛИ($A$1:A2=0;СТРОКА($A$1:A2)))-СТРОКА();;СТРОКА()-МАКС(ЕСЛИ($A$1:A2=0;СТРОКА($A$1:A2)))+1))
Может это ЕСЛИ и И так ведут себя в массиве? Жду Ваших ответов. Файл прикрепил.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Максим Зеленский, а есть у Вас пособие для решения подобных задач и составления таких формул? или сборник с решенными примерами сложных задач?
 
Цитата
Максим Зеленский написал:
сначала считаем номер последней строки с 0, расположенной выше.
на примере расчета для 9 строки:
Спасибо огромное еще раз!
Не ожидал такого, теперь гораздо больше шансов разобраться!  
 
Цитата
Bema написал:
Ребята, а посмотрите пожалуйста, почему такая формула не работает.
Всё правильно, И не работает с ЕСЛИ в массиве. ИЛИ тоже не работает. Вместо этого =ЕСЛИ(A2>0;ЕСЛИ(A3=0;МАКС(... - так работает.
 
Wanschh,  не работает. Опять появляются фигурные скобки в СМЕЩ.
Изменено: Bema - 04.04.2017 21:17:47
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Ничего не понимаю :(
 
Я тоже. Но чудес не бывает, должно быть рациональное объяснение.  
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Bema, проблема не в И/ИЛИ, и не просто в ЕСЛИ.

Проблема в сочетании ЕСЛИ и функции СТРОКА(). СТРОКА по умолчанию возвращает массив, а не скалярное значение. Это проверить легко - введите где-нибудь просто =СТРОКА(A1). Нажмите F2, потом F9.

Функция-агрегатор МАКС возвращает нам скалярное значение, а СТРОКА - массив. В итоге X-{Y}={Z}, {Y}-X = {-Z}
На это уже и ругается СМЕЩ.

Почему МАКС(СМЕЩ(... СТРОКА()...)) преобразовывает массив в скаляр, а ЕСЛИ(...;МАКС(СМЕЩ(... СТРОКА()...))) не преобразовывает - не знаю точно. Думаю, что по следующей причине:
Так как мы создаем массивную функцию, то ЕСЛИ не производит преобразование СТРОКА() в скалярное значение. А когда мы убираем ЕСЛИ, то внешней становится МАКС, и она уже преобразовывает результат СТРОКА в скаляр.

Обходим при помощи СУММ(СТРОКА()) - получаем СУММ({X})=X:

=ЕСЛИ(И(A9>0;A10=0);МАКС(СМЕЩ(A9;МАКС(ЕСЛИ($A$1:A9=0;СТРОКА($A$1:A9)))-СУММ(СТРОКА());;СУММ(СТРОКА())-МАКС(ЕСЛИ($A$1:A9=0;СТРОКА($A$1:A9)))+1));"")
F1 творит чудеса
 
Цитата
Bema написал: почему такая формула не работает: =ЕСЛИ(И(A2>0;A3=0)...
Цитата
Wanschh написал:  И не работает с ЕСЛИ в массиве. ИЛИ тоже не работает
=ЕСЛИ((A2>0)*(A3=0);...
Если не это, создайте отдельную тему с простым примером.
 
Максим Зеленский, vikttur, спасибо. Буду вникать.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
copper-top, ищите вот такую книгу:
http://www.mrexcel.com/2013books/cse2013book.html
но на самом деле лучший учебник - попытка разобрать такие вот формулы при помощи окна вычисления и кнопки F9 в строке редактирования.
Я учился на примерах с этого форума, и до наших гуру, таких как, например, Владимира и МСН (и не только их) мне еще далеко.
F1 творит чудеса
 
Максим Зеленский, спасибо. буду следовать Вашим советам.
 
copper-top, если не знакомы с этим инструментом, очень рекомендую познакомится.
http://www.excel-vba.ru/chto-umeet-excel/kak-prosmotret-etapy-vychisleniya-formul/
Действительно помогает писать и корректировать сложные формулы.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Bema, спасибо за совет. некоторые формулы просматриваю через окно"вычисление формулы". только это уже готовые формулы. а так, чтобы составить такие  формулы для решения задач,например,как в этой теме, мне еще не по силам. думаю, все еще впереди.
Страницы: 1
Наверх