Страницы: 1 2 След.
RSS
Поиск максимального значения в массиве, ограниченного определенными пределами
 
Например есть массив (A1:C10), в нем содержатся некие числовые данные. Надо найти в нем максимальное значение (MAX), которое бы условию: ПАРАМЕТР1<=MAX<=ПАРАМЕТР2.
Результатом должно быть а) само это значение, б) ссылка на ячейку, в котором находится это значение.
NB: Заметьте, что функция ДМАКС для поиска значения не подходит. И не подходит она по двум причинам: 1) она не ищет в массиве, а ищет толко в БД, где однородные данные должны находиться строго в строках ИЛИ столбцах, но не и там, и там одновременно; 2) для задания условия в функции ДМАКС в качестве аргумента нужна ссылка на диапазон ячеек, содержащий эти условия, а мне нужно, чтоб эти условия (а не ссылки на них) были непосредственым аргументом функции.
Для понимания задачи опишу где это используется. Это используется при подборе значения методом перебора с последующим удалением это значения из списка. Например надо уложить паркетную доску. При ее укладке образовываются обрезки в начале и конце комнаты. Эти обрезки можно (и нужно) использовать. Потому и стоит задача поиска минимального по размеру обрезка в массиве обрезков, но который больше заданой величины (т.е. величины места под него). А для того, чтоб после использования искомого обрезка исключить его из массива обрезков - нужен адрес ячекйки, где он хранился.
Я голову сломал не нашел решение... А вам слабо?  :)
Изменено: Barmaldon - 11.10.2014 22:29:46
 
три формулы массива
upd формулы для определения адреса - неправильные! см. сообщение #12
Изменено: ikki - 11.10.2014 20:49:33
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
Barmaldon пишет:
А вам слабо?
А вот без этого можно?
 
Цитата
ikki пишет: три формулы массива
Спасибо, только не могу понять как работает первая функция... Зачем там произведение (A1:C10<=16)*(A1:C10>=5) ?
Цитата
Юрий М пишет: А вот без этого можно?
Да можно конечно, но это была шутка и смайлик после этой фразы тому доказательство. Прошу прощения у всех, у кого отсутствует чувство юмора.
 
Цитата
Barmaldon пишет: Зачем там произведение
потому что функция И() в формулах массива не работает

пс. это не форум юмористов
Изменено: ikki - 11.10.2014 22:30:21
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Вы бы могли объяснить смысл ЕСЛИ((A1:C10<=16)*(A1:C10>=5)   ?
Получается булевое значение умножаем на булевое... А так можно?
 
Barmaldon, чувство юмора и смайлик тут ни причём: можно послать подальше и поставить смайлик с улыбкой - будет смешно? Просто здесь не любят, когда пытаются брать "на слабо".
 
Понял, больше так шутить не буду.
 
Цитата
Barmaldon пишет:Получается булевое значение умножаем на булевое... А так можно?
можно.
только они при этом автоматически преобразуются в числа.
и результат получается - число.
а для функции ЕСЛИ это число обратно преобразовывается в буль.
тоже автоматически.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
Barmaldon пишет: объяснить смысл
Логическое умножение. Замена функции И( ), которая, как вам уже сказали, в формулах массивов не работает.
F1 творит чудеса
 
Цитата
ikki пишет: только они при этом автоматически преобразуются в числа.
Спасибо большое. Теперь все понял.
Тема раскрыта, а раз раскрыта, то закрыта.
Спасибо большое всем за участие, а главное спасибо - ikki за раскрытие темы.
 
не совсем раскрыта.
точнее - если максимум окажется не один, то формулы для адреса могут наврать.
зависит от того, где именно находятся максимумы.

исправленный вариант приложил.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
ikki пишет: если максимум окажется не один, то формулы для адреса могут наврать.
Я об этом подумал когда впервые смотрел вашу таблицу, и даже стал писать соответствующий месседж, но потом подумал, что для моей задачи не имеет значения какой именно максимум имел ввиду эксель когда его находил, и какой о покажет в адресе. Ведь главное, что он верно нашел максимум и верно дал ссылку на расположение максимума. Имхо тема раскрыта. Но раз уж заговорили... Подскажите пожалуйста, чем заменяется функция ИЛИ в формулах массива?
 
Цитата
Barmaldon пишет: и верно дал ссылку на расположение максимума
в том-то и дело, что неверно.
Цитата
Barmaldon пишет: чем заменяется функция ИЛИ в формулах массива?
я Вам в личку ответил
Изменено: ikki - 11.10.2014 20:46:36
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
ikki пишет: в том-то и дело, что неверно.
Да, точно, теперь понял... Эксель взял строку от одной ячейки, а столбец - от другой. Очень странно, значит у него неодинаковый алгоритм поиска от раза к разу, раз он просматривает массив в разных направлениях... Очень странно...
 
причем тут "просматривает массив в разных направлениях"?
"просматривает" он его одинаково, и неважно, как именно.
просто в результате вложенной функции ЕСЛИ(A1:C10=E10;СТРОКА(A1:C10)) получается массив
а нам нужно одно значение
поэтому мы применяем к этому массиву МИН
а функции МИН, примененные по отдельности к номерам строк и к номерам столбцов - это в данном случае логическая ошибка.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
ikki пишет:  в результате вложенной функции ЕСЛИ(A1:C10=E10;СТРОКА(A1:C10)) получается массив
Правильно, но и в результате вложенной функции ЕСЛИ(A1:C10=E10;СТОЛБЕЦ(A1:C10)) получается тоже массив. Только в первом случае ex берет значение из одной ячейки массива (когда находит совпадение ячейки массива с E10) и дальше уже массив не просматривает, а в другом случае из другой. Вот я и имел ввиду, что он просматривает массив в этих двух случаях поразному - в первом случае он "натыкается" сначала на значение "15" в ячейке B4, и соответственно выдает строку 4, а во втором случае он почему-то сначала находит значение "15" уже в ячейке A7 и выдает столбец 1. Странно то, что он просматривает массив по-разному в этих двух случаях.
 
Цитата
ikki пишет: "просматривает" он его одинаково
Цитата
Barmaldon пишет: просматривает массив в этих двух случаях поразному
ещё раз рассказать?  :)

просматривает одинаково.
в первом случае получается массив, в котором два номера строк с максимумом: 4 и 7, остальные значения - ЛОЖЬ
во втором - массив, в котором два номера столбцов с максимумом: 2 и 1, остальные значения - ЛОЖЬ
эти массивы абсолютно параллельны между собой.
но фишка в том, что функции МИН для номера строки берет 4, а для номера столбца - 1.
т.е. от разных ячеек. (в принципе - она ж об этом и не "знает", она просто работает с массивом чисел)
а на пересечении этих строки и столбца находится вовсе не максимум.
Изменено: ikki - 11.10.2014 21:52:23
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
ikki пишет: просматривает одинаково .
"Холмс, вы правы. Правы как всегда." (с)
 
Цитата
Замена функции И( ), которая, как вам уже сказали, в формулах массивов не работает
Ради справедливости :)
Не обижайте функцию, работает в формулах массивов и она, и подруга ИЛИ. Но в другом случае:
=И(здесь_может_быть_обработка_массива).
 
Цитата
ikki пишет: функции МИН для номера строки берет 4, а для номера столбца - 1.
А зачем вообще здесь используется функция МИН ? С таким же успехом могла быть функция МАКС ? Это потому, что функция ЕСЛИ не работает напрямую с массивами?
 
уже писал:
Цитата
просто в результате вложенной функции ЕСЛИ(A1:C10=E10;СТРОКА(A1:C10)) получается массив
а нам нужно одно значение
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Предположим, если бы нам было достаточно знать только номер строки (столбец нам предположим не нужен) можно было бы и так например:=МАКС(ЕСЛИ(A1:C10=E10;СТРОКА(A1:C10))). То есть вопрос в чем: зачем в этом случае функция МИН (или МАКС) - чтоб выбрать по сути любое значение из массива - правильно?
 
не любое.
а то, которое нужно - минимальное или максимальное.
но одно.
если бы была абсолютная уверенность, что значение всегда одно - можно и СУММ использовать.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Все пишете, пишете... Настолько интересно? Пойду файл смотреть :)
 
Цитата
ikki пишет: не любое
В последнем примере (когда не нужно пересечение множеств, а достаточно выбрать любое значение из одного множества, удовлетворяющее условию), при определении номер строки все же мне кажется можно использовать и мин, и макс - результат будет в первом случае строка 4, во втором - строка 7, но сути это не меняет - и первый и второй вариант даст верный результат - номер строки, содержащей ячейку со значением, удовлетворяющим условию. (повторюсь: это только в том случае, если знание номера столбца нам не нужно).
 
Если поиск максимального - зачем задавать нижнюю границу?
=НАИБОЛЬШИЙ(A1:A10;СЧЁТЕСЛИ(A1:A10;">16")+1)
 
а если все числа будут больше или равны 16?  :)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
...то что, в таком случае, покажет МАКС?
Ну да, 16 тоже в доле... Исправил.
 
ну вот как-то так получилось из файла ikki,
F1 творит чудеса
Страницы: 1 2 След.
Наверх