Поиск приближенного числа в массиве

[Яна Варзова]

Доброго всем дня.
Вопрос:

1) в таблице даны значения чисел по возрастанию и, есть число, например 46:56:30.
Так вот, нужно найти в этой таблице приближенное в большую сторону (или такое же число) к 46:56:30 . В формуле можно прописать номер столбца. Ответом будет 47:33:00 (во втором столбце).

2) далее, напротив найденного уже 47:33:00, нужно записать число напротив, из последнего (правого) столбца.

Может, кто подскажет как. (Пожалуйста не VBA).

[Pelena]

Здравствуйте.
Так подойдёт?

[Czeslaw]

Предлагаю вариант формулы немного попроще.

Код Выделить Развернуть

=AGGREGATE(15;6;A$1:M$31/(A$1:M$31>Q3);1)

И вторая формула

Код Выделить Развернуть

=IFERROR(INDEX(A$1:M$31;AGGREGATE(15;6;ROW($1:$31)/(A$1:M$31=R3);1);COLUMNS($A:$M));"")

[Pelena]

Предлагаю вариант формулы немного попроще.

Красиво! Это плюс)

[ShAM]

Предлагаю вариант формулы немного попроще.

Красиво! Это плюс)

Согласен. Только:
или диапазон А1:М31 нужно превратить в числа (выделить А1:М31 - Ctrl+H - Найти: ":" - Заменить на: ":" (без кавычек) - Заменить все).
или формулу чутка дописАть:

Код Выделить Развернуть

=АГРЕГАТ(15;6;A$1:M$31/(--A$1:M$31>Q3);1)

[ShAM]

Ну, и вторую формулу немного изменить:

Код Выделить Развернуть

=ЕСЛИОШИБКА(ИНДЕКС(M$1:M$31;АГРЕГАТ(15;6;СТРОКА($1:$31)/(A$1:M$31=R3);1));"")

[Яна Варзова]

Невозможно передать здесь столько моей радости.
Глядя на формулы, которые вы мне щедро и любезно предоставили, поняла, что знания мои по Excel узки.

Всем моим помощникам по решению вопроса Благодарность и низкий поклон.
Дай вам Бог всех Благ!

[Czeslaw]

Яна здравствуйте!
Забыл предупредить Вас, что представленные Вами данные необходимо через специальную вставку умножить на единицу и потом преобразовать в формат времени и только тогда мои формулы будут работать нормально.
На всякий случай прицепляю переделанный пример Вашего файла.

[Яна Варзова]

Здравствуйте.

Хорошо! Посмотрю.

[ShAM]

Czeslaw, здравствуйте. Зачем искать по всей таблице: ИНДЕКС(A$1:M$31... и затем указывать номер столбца: ЧИСЛСТОЛБ($A:$M)? Можно же сразу искать по столбцу М: ИНДЕКС(M$1:M$31..., тогда номер столбца не нужен.
И Ваша формула:

Код Выделить Развернуть

=ЕСЛИОШИБКА(ИНДЕКС(A$1:M$31;АГРЕГАТ(15;6;СТРОКА($1:$31)/(A$1:M$31=R3);1);ЧИСЛСТОЛБ($A:$M));"")
превращается в:

Код Выделить Развернуть

=ЕСЛИОШИБКА(ИНДЕКС(M$1:M$31;АГРЕГАТ(15;6;СТРОКА($1:$31)/(A$1:M$31=R3);1));"")
PS: Извиняюсь за русские формулы. Надеюсь, разберетесь.
PS:PS: Да, забыл про "плюс", ловите.

[Яна Варзова]

Всем доброго дня.

У меня есть формула, которая выдает ошибку.
Задача, сейчас, наоборот, нужно, чтобы, Excel нашел число 142:57:00 по правому столбцу, в котором число 0:00:00.

Есть формула, которая всегда действовала, но сейчас же выдает #н/д.
Может, подскажете в чем причина, разобраться не могу.

[Czeslaw]

Приведите данные к единому формату, как указано в моем предыдущем посте.

[Яна Варзова]

Благодарю.

[Яна Варзова]

Помогите, пожалуйста, вот еще в чем.

Сделала как вы сказали. Получилось. Но. При копировании таблицы с формулами на другой Лист выдает ошибку в ячейках #знач. И я не в силах, как быть.

[vikttur]

Ошибочные данные в ячейке К2.

В теме несколько вопросов. какой из них решает формула, не разбирался.

Код Выделить Развернуть

=СУММПРОИЗВ(--(--(--($A$1:$L$31)>=Q3)=0)) безболезненно заменяется на

Код Выделить Развернуть

=СУММПРОИЗВ(1-(--$A$1:$L$31>=Q3))

А может, всю формулу заменить?

Код Выделить Развернуть

=НАИБОЛЬШИЙ((--A1:L31>Q3)*A1:L31;СЧЁТ(1/(--A1:L31>Q3)))

[Czeslaw]

Опять данные у Вас в текстовом формате, поэтому проделайте ту же процедуру и представляю пример с моими формулами.

[Яна Варзова]

Czeslaw, очень прошу, объяснить мне, чтобы не ошибаться в дальнейшем, и извиняюсь, знания мои скудны в Excel, может, для вас, я спрашиваю слишком элементарные вещи:

1. Вы умножаете данные на единицу через специальную вставку. Это поняла, в интернете смотрела как это делать и так было сделано.
2. Установить формат времени. Где вы посмотрели, что у меня текстовый формат, ведь, точно вижу "все форматы --> ч:мм:сс или это и есть текстовый.

Может, вас растрою - я бы очень хотела применить ваши формулы, но не могу, потому что у меня excel 2007, а в нем Агрегата нет. Когда, поставлю 2010, то ваши формулы, и формулы других здесь, помощников - участников, будут задействованы.

Vikttur - Благодарю вас за ваше подключение к вопросу.

[vikttur]

точно вижу "все форматы --> ч:мм:сс или это и есть текстовый

Если кошке прицепить хобот, она слоном не станет
Введите  ячейку "ааа", назначьте ячейке формат даты - станет строка датой?

Ошибочные данные в ячейке К2

Это нашли?

[Яна Варзова]

Да, нашла ошибку. Ведь, точно.

Ввела "ааа", установила, если правильно установила, формат Дата, ничего не изменилось, если, что-то должно было измениться.

[vikttur]

Да ничего не должно было измениться! слон - это число, кот - это "ааа"

Формат ячейки не изменяет тип данных, изменяется только визуальное представление данных. Если в ячейке число на самом деле не число, а текст, то оно не станет числом при изменении формата на числовой.  И визуально не поменяется, т.к. задан формат, который неприемлем для текста.