1

У меня есть таблица значений в Excel (2103, если это имеет значение), которая при построении графика выглядит примерно так:

Я пытаюсь найти для каждой серии индекс значения, после которого все последующие значения лежат в определенном диапазоне (между 99 и 101 в этом случае). Обратите внимание, что значения не обязательно находятся в порядке возрастания или убывания, но все они в конечном итоге будут сходиться к одному и тому же значению (в этом случае 100).

Я провел некоторый поиск, и мне кажется, что какая-то комбинация MATCH, IF и / или VLOOKUP должна это сделать, но я не могу понять это. Кто-нибудь может помочь?

Спасибо!

1 ответ1

3

Пара возможных подходов:

ОПЦИЯ 1

1) Сортировка данных по значению X и сортировка по убыванию. Вы хотите, чтобы ваше наибольшее значение X было вашей первой точкой.

2) Определите наклон линии между каждой последующей точкой. В соседнем столбце.

3) Когда наклон линии рассчитывается, но превышает максимальное значение для точки 1 и минимальное значение для точки два (или наоборот), вы знаете, что вы находитесь за пределами вашей точки схождения.

ВАРИАНТ 2

1) Сортировка данных по значению X в порядке убывания. так же, как вариант1

2) Если у вас есть способ определить значение сходимости, поместите его в ячейку.

3) Найдите первое значение Y в списке, которое превышает значение сходимости на + или - ваш допуск.

НАЙТИ ПЕРВОЕ ПРОИЗВОДСТВО

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

AGGGREGATE (Function #, Error/Hidden Options, Your formula, Parameter)

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

То, что мы ищем, является первым появлением чего-либо. Нам либо понадобится значение этого чего-либо или его позиция в списке (или номер строки). Обычно в этой ситуации то, что я пытаюсь найти, обычно ставят в формулу в качестве числителя. Любые условия, которые должны быть выполнены и дают результаты ИСТИНА или ЛОЖЬ, помещаются в знаменатель формулы.

Хитрость заключается в том, что когда TRUE или FALSE помещаются в математическую операцию, они конвертируются в 1 для TRUE и в 0 для FALSE. (В целом, если такие функции, как IF, ищут ИСТИНА и ЛОЖЬ при принятии решений, 0 обрабатывается как ЛОЖЬ, а КАЖДЫЙ ДРУГОЙ НОМЕР ИСТИНА.) Таким образом, каждый раз, когда вы делите на FALSE, вы будете делить на 0, что приводит к ошибке для расчета, которая ранее была заявлена, что AGGREGATE будет установлен на игнорирование.

Таким образом, при правильном построении формулы вы получите отсортированный список элементов, которые соответствуют только вашим условиям в знаменателе. И поскольку это отсортированный список в порядке возрастания, когда он настроен на возврат 1-й записи, вы получите (в нашем случае) наименьший номер строки или наименьшее значение.

Пример. Предположим, что ваши значения X находятся в столбце B, а значения Y - в столбце C, а ваши данные - в строках 2: 100. Мы хотим найти первое значение Y, которое больше, чем 2,4 от 81,3

=AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1)

Теперь, чтобы вернуть значение этой ячейки, вы должны встроить формулу AGGREGATE в формулу INDEX.

=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1))

Обратите внимание, что полная ссылка на столбец B:B находится вне функции AGGREGATE, что в порядке. Если вы хотите узнать, что это за значение Y, измените B:B на C:C.

Исходя из ваших критериев от 101 до 99, вы можете использовать:

=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1))

OR

=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1))

Эти уравнения дадут вам значение первой точки за пределами ваших пределов. Таким образом, чтобы получить последнюю точку внутри ваших пределов, вам нужно вычесть 1 из номера строки, что дает новую формулу:

=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1)-1)

OR

=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1)-1)

Обратите внимание на * между двумя проверками условий в знаменателе. * Действует как функция AND (только 1 * 1 = 1; оба условия должны выполняться). Знак « + будет действовать как функция «ИЛИ» (либо результат, либо оба могут быть истинными, если результат не равен нулю).

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .