Страницы: 1 2 След.
RSS
ВПР с поиском минимального значения
 
Гуру экселя, прошу Вас помочь.
На бытовом уровне всегда сам справлялся, а сейчас не могу подобрать нужную функцию.

Условная таблица по задаче:
http://www.picshare.ru/view/6094812/


Пояснение к таблице:
1ый столбик (А) - порядковый номер месяца
2ой столбик (B) - календарный номер месяца (по факту лишняя информация)
3ий столбик (С) - остаток основного долга после погашения ежемесячным платежом

Задача:
1) получить формулу, которая будет определять минимальное положительное число (ближе всего к нулю) из столбика С и брать соответствующее ему значение из столбика А.
2) получить формулу, которая будет определять минимальное отрицательное число (ближе всего к нулю) из столбика С и брать соответствующее ему значение из столбика А.

Заранее большое спасибо.

P.S.
Минимальное положительное: =НАИМЕНЬШИЙ(C8:C23;СЧЁТЕСЛИ(C8:C23;"<=0";)+1)
Минимальное отрицательное значение: =НАИБОЛЬШИЙ(C8:C23;СЧЁТЕСЛИ(C8:C23;">=0";)+1)

В моем понимании вот так должна решаться задача 1, но мне выдается неправильный ответ:
=ВПР(=НАИМЕНЬШИЙ(C8:C23;СЧЁТЕСЛИ(C8:C23;"<=0")+1);A8:C23;1;1)
Изменено: smd1987 - 22.03.2015 15:06:33
 
Название темы ни о чём. Предложите новое - я поменяю.
 
Юрий, извините, может быть так: ВПР с поиском минимального значения
 
Не получится: ВПР() ищет значение только в крайнем левом столбце. Здесь лучше связка
Код
ИНДЕКС(A:A;ПОИСКПОЗ(МИН(C:C);C:C;0))
Изменено: МВТ - 22.03.2015 15:12:33
 
См. вариант.
 
вариант ВПР
Лень двигатель прогресса, доказано!!!
 
Мотя, спасибо. Насколько я понимаю, в Вашем варианте надо руками задавать положительный и отрицательный диапазоны.
Поправьте меня, если я не прав, пожалуйста.
В моем случае значения в столбике 3 меняются и "переход через ноль" постоянно меняется, соответственно надо будет руками диапазоны выставлять/перепроверять.
А что за значение "tab", не подскажите?
 
МВТ, спасибо.
Подставил Вашу формулу:
=ИНДЕКС(A8:A23;ПОИСКПОЗ(МИН(C8:C23);C8:C23;0))
но оно мне выдает значение 16, то есть значение ячейки в ряду А, соответствующую минимальному значению в ряду С.
Как поправить эту формулу, чтобы бралось значение, соответствующее последнему (минимальному) положительному числу в ряду С?
 
Цитата
smd1987 написал: надо руками задавать положительный и отрицательный диапазоны
Разумеется, т.к. у Вас таблица исходных данных будет меняться.
Эти формулы тривиальны: главное - не ошибиться при выделении "+" и "-" диапазонов.
Формулы с ВПР остаются неизменными.
ТАВ - это именованный диапазон Ваших данных.
Он также не меняется, ибо "захватывает" Ваши данные "столбами".
Изменено: Мотя - 23.03.2015 01:46:53
 
Чтобы не мучиться, предлагаю пару UDF'ок:
Код
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() и внутри скобок выделяете нужный диапазон (он может быть и не из одного столбика :))
 
Цитата
smd1987 написал:
а сообщает об ошибке.
а почему в функции не прописываете все аргументы в К12 у вас должно быть так
Код
=ВПР(K13;C8:D23;2;0)
Лень двигатель прогресса, доказано!!!
 
См.
 
Цитата
Сергей написал: а почему в функции не прописываете все аргументы в К12 у вас должно быть такКод?1=ВПР(K13;C823;2;0)
Насколько я понял, последний аргумент "0" не является обязательным.
Вот что написано в справке:
Интервальный_просмотр Необязательный. Логическое значение, определяющее, какое совпадение должна найти функция ВПР — точное или приблизительное.

Но и если добавить "0", то все-равно не считает.
Изменено: smd1987 - 23.03.2015 01:47:12
 
Цитата
МВТ написал: В прикрепленном файле макросы уже вставлены
МВТ, спасибо большое Вам! А без макросов не подскажете как можно решить задачу?
 
Мотя, не вижу формул.
В ячейки F3 и G3 Вы руками подставили значения?
 
Как включить или отключить макросы в Excel 2003, 2007 и 2010
Уважаемый smd1987!
Включите макросы и используйте файл smd1987.xlsm (макрос в нем внедрен).
Вам потребуется только менять исходные данные, вставляя их с 7 строки.
 
Цитата
smd1987 написал: В ячейки F3 и G3 Вы руками подставили значения?
Разумеется, нет.
Макрос "написал".
 
Цитата
Мотя написал:
МВТ, спасибо большое Вам! А без макросов не подскажете как можно решить задачу?
Мотя, спасибо Вам большое! А без макросов не подскажете как можно решить задачу?
Просто в следующий раз при необходимости я этого (с макросами) не повторю.
Хочется уметь решать подобную задачу самостоятельно, а для этого нужно понимать процесс ОТ и ДО.
 
smd1987,а так Вам чем не нравится? Пользуйтесь пользовательскими формулами, как обычными. Как альтернатива, придется накручивать достаточно сложную конструкцию. Не уверен, что правильно, но у меня как-то получились такие формулы (попробуйте, расскажете - работают ли):
Код
=ИНДЕКС($B$8:$B$23;СЧЁТЗ($B$8:$B$23)-МАКС(СУММПРОИЗВ(($B$8:$B$23<0)*($B$8:$B$23=$B$8:$B$23)))+1)
 
 =ИНДЕКС($B$8:$B$23;МИН(СУММПРОИЗВ(($B$8:$B$23>0)*($B$8:$B$23=$B$8:$B$23))))


 
Цитата
smd1987 написал: Насколько я понял, последний аргумент "0" не является обязательным.
ну да он не обязателен но когда его поставить он чет становится обязательным или справка кривая или мое понимание в ней кривое
Лень двигатель прогресса, доказано!!!
 
А так не подойдёт? Формулы массива вводятся нажатием сочетания клавиш Ctrl+Shift+Enter
Код
=МАКС(ЕСЛИ($B$1:$B$41<0;$A$1:$A$41))
Код
=МИН(ЕСЛИ($B$1:$B$41>0;$A$1:$A$41))
 
Насколько я имел возможность убедиться, последний аргумент 0 является необязательным, если просматриваемый массив отсортирован по возрастанию (примерно так в справке и написано, но без упоминания третьего аргумента). Если массив не сортирован, а 3 аргумент не задан, то ВПР() выдает первый похожий результат, т.е. работает как при интервальном просмотре
 
Цитата
МВТ написал: Насколько я имел возможность убедиться, последний аргумент 0 является необязательным, если просматриваемый массив отсортирован по возрастанию
вот МВТ,  наверное мою мысль более правильно выразил справку читать надо так чтоб знать, что буржуины обычны переводят автоматом все, не парясь о пользователях
Лень двигатель прогресса, доказано!!!
 
Сергей, чудеса. Я перед тем как Вам ответить проверил и результата не было.
А теперь работает. Спасибо большое. Работает. Задача решена!
 
Формулы Pelena намного проще: см. файл.
 
Мотя, тоже просто и дает правильный результат. Спасибо Вам большое.

Цитата
Pelena написал: =МАКС(ЕСЛИ($B$1:$B$41<0;$A$1:$A$41))
Выдает ноль почему-то.
А для МИН работает. Выдает значение следующей строчки, то есть номер платежа после возврата долга.
Спасибо. Вроде просто, а задачу решает.
Страницы: 1 2 След.
Наверх