Пояснение к таблице: 1ый столбик (А) - порядковый номер месяца 2ой столбик (B) - календарный номер месяца (по факту лишняя информация) 3ий столбик (С) - остаток основного долга после погашения ежемесячным платежом
Задача: 1) получить формулу, которая будет определять минимальное положительное число (ближе всего к нулю) из столбика С и брать соответствующее ему значение из столбика А. 2) получить формулу, которая будет определять минимальное отрицательное число (ближе всего к нулю) из столбика С и брать соответствующее ему значение из столбика А.
Мотя, спасибо. Насколько я понимаю, в Вашем варианте надо руками задавать положительный и отрицательный диапазоны. Поправьте меня, если я не прав, пожалуйста. В моем случае значения в столбике 3 меняются и "переход через ноль" постоянно меняется, соответственно надо будет руками диапазоны выставлять/перепроверять. А что за значение "tab", не подскажите?
МВТ, спасибо. Подставил Вашу формулу: =ИНДЕКС(A8:A23;ПОИСКПОЗ(МИН(C8:C23);C8:C23;0)) но оно мне выдает значение 16, то есть значение ячейки в ряду А, соответствующую минимальному значению в ряду С. Как поправить эту формулу, чтобы бралось значение, соответствующее последнему (минимальному) положительному числу в ряду С?
smd1987 написал: надо руками задавать положительный и отрицательный диапазоны
Разумеется, т.к. у Вас таблица исходных данных будет меняться. Эти формулы тривиальны: главное - не ошибиться при выделении "+" и "-" диапазонов. Формулы с ВПР остаются неизменными. ТАВ - это именованный диапазон Ваших данных. Он также не меняется, ибо "захватывает" Ваши данные "столбами".
Function MinPlus(ByVal Rng As Range) As Variant
'Возвращает минимальное положительное число из диапазона
'Возвращает #ССЫЛКА при незаданном диапазоне
'Возвращает #ЗНАЧ!при отсутствии в диапазоне положительных чисел
Dim aMin As Double
If Rng.Count = 0 Then
MinPlus = CVErr("#REF")
Exit Function
End If
aMin = Application.WorksheetFunction.Max(Rng)
If aMin <= 0 Then
MinPlus = CVErr("#NUM!")
Exit Function
End If
For Each Cell In Rng
With Cell
If .Value > 0 And .Value < aMin Then aMin = .Value
End With
Next Cell
MinPlus = aMin
End Function
Function MinMinus(ByVal Rng As Range) As Variant
'Возвращает максимальное отрицательное число (т.е. с минимальным модулем
'Возвращает #ССЫЛКА при незаданном диапазоне
'Возвращает #ЗНАЧ! при отсутствии в диапазоне отрицательных чисел
Dim aMin As Double
If Rng.Count = 0 Then
MinMinus = CVErr("#REF")
Exit Function
End If
aMin = Application.WorksheetFunction.Min(Rng)
If aMin >= 0 Then
MinMinus = CVErr("#NUM!")
Exit Function
End If
For Each Cell In Rng
With Cell
If .Value < 0 And .Value > aMin Then aMin = .Value
End With
Next Cell
MinMinus = aMin
End Function
Мотя,не совсем правильно: макрос должен искать не минимальное отрицательное число, а отрицательное число, которое ближе всего к нулю (т.е., отрицательное число с минимальным модулем). У ТС словами заданы одни условия, а формулами а при попытке прописать формулами - внесена некоторая путаница.
МВТ, Мотя, спасибо, но предлагаемый Вами вариант, к сожалению, за гранью возможностей моего понимания и имеющихся знаний. Может можно какими-то более простыми инструментами решить задачу? Пусть даже в 2-3 шага.
Без макросов я смог формулой находить минимальное положительное значение в столбце С: =НАИМЕНЬШИЙ(C8:C23;СЧЁТЕСЛИ(C8:C23;"<=0";)+1) Далее я продублировал столбик А с порядковыми номерами месяцев справа от столбца С (теперь это еще и D), чтобы иметь возможность пользоваться функцией ВПР (с учетом комментария в данной теме), но оно все-равно не выдает результат, а сообщает об ошибке. Не могу понять почему. Вот прикрепил файл со всеми расчетами. Помогите разобраться, пожалуйста.
В прикрепленном файле макросы уже вставлены, просто разрешите их применение в настройках. Далее, пользуетесь пользовательскими формулами так же, как обычными: набираете, например, MinPlus() или MinMinus() и внутри скобок выделяете нужный диапазон (он может быть и не из одного столбика )
Сергей написал: а почему в функции не прописываете все аргументы в К12 у вас должно быть такКод?1=ВПР(K13;C823;2;0)
Насколько я понял, последний аргумент "0" не является обязательным. Вот что написано в справке: Интервальный_просмотр Необязательный. Логическое значение, определяющее, какое совпадение должна найти функция ВПР — точное или приблизительное.
Мотя написал: МВТ, спасибо большое Вам! А без макросов не подскажете как можно решить задачу?
Мотя, спасибо Вам большое! А без макросов не подскажете как можно решить задачу? Просто в следующий раз при необходимости я этого (с макросами) не повторю. Хочется уметь решать подобную задачу самостоятельно, а для этого нужно понимать процесс ОТ и ДО.
smd1987,а так Вам чем не нравится? Пользуйтесь пользовательскими формулами, как обычными. Как альтернатива, придется накручивать достаточно сложную конструкцию. Не уверен, что правильно, но у меня как-то получились такие формулы (попробуйте, расскажете - работают ли):
Насколько я имел возможность убедиться, последний аргумент 0 является необязательным, если просматриваемый массив отсортирован по возрастанию (примерно так в справке и написано, но без упоминания третьего аргумента). Если массив не сортирован, а 3 аргумент не задан, то ВПР() выдает первый похожий результат, т.е. работает как при интервальном просмотре
МВТ написал: Насколько я имел возможность убедиться, последний аргумент 0 является необязательным, если просматриваемый массив отсортирован по возрастанию
вот МВТ, наверное мою мысль более правильно выразил справку читать надо так чтоб знать, что буржуины обычны переводят автоматом все, не парясь о пользователях
Выдает ноль почему-то. А для МИН работает. Выдает значение следующей строчки, то есть номер платежа после возврата долга. Спасибо. Вроде просто, а задачу решает.