Эта функция Excel извлекает N-значный номер из строки. Как это работает?

Я попытался написать функцию Excel, которая извлекает любое N-значное число из длинной буквенно-цифровой строки. Я нашел пример онлайн в нескольких местах ( 1, 2), который работает, но я не могу понять, как.

Например, учитывая строку0xabc_2014528888_abcde_30285_q!, функция должна возвратиться 2014528888(предполагая N = 10).

Я нашел следующий фрагмент Excel в нескольких местах (здесь для N=10):

=LOOKUP(10^10,MID(A1,ROW(INDIRECT("1:"&LEN(A1)-9)),10)+0)

Но я не могу понять, как это работает.

  1. В документации по функции LOOKUP() корпорации Майкрософт говорится, что первым аргументом LOOKUP()является значение lookup… как установка значения 10^N возвращает N символов?

  2. INDIRECT()Функция, похоже, получает аргумент 1:21(в моей строке примера). Как это допустимая ссылка на ячейку? Если я введу его в ячейку сам по себе, он не вычислит ничего разумного.

Может ли кто-нибудь объяснить, что эта функция делает для извлечения числовой последовательности?

1 ответ

  1. Я попробую, хотя есть некоторые части,в которых я не уверен.

    В принципе, то, что мы имеем здесь, — это формула массива, которая не вводится как массив-то есть, когда задан диапазон значений, он будет выполнять свою функцию для каждого возможного значения, а не для всего диапазона сразу.

    INDIRECT("

    I'll give this a shot, though there are some parts I'm not sure about.

    Basically, what we have here is an array formula that's not being entered as an array - that is, when given a range of values, it will perform its function for each possible value, rather than for the whole range at once.

    INDIRECT("$1:$22") returns a reference to all of rows 1 through 22. The ROW function then uses that reference to those rows to return a list or array (this is the part I'm not sure about - I'm not sure how it's creating an array) of numbers {1;2;3;4;5;6;7;8;9;...22}

    It then uses MID to return all strings of 10 characters from the source value, using the array of linear numbers from before as the starting points. MID returns another array, {"0xabc 2014";"xabc 20145";"abc 201452";..."e 30285 q!"}. The strings are then forced to perform a mathematical function by the +1. This forces any strings in the array that contain non-numeric characters to return #VALUE! errors, while numeric strings return themselves as a value. We get an array like {#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;2014528888;#VALUE!;...}

    Finally, LOOKUP looks through that array and finds the largest value that is smaller than the first argument - that is, the only result in the array that does not return #VALUE!

    I suspect - but cannot state for sure - that the key is using LOOKUP instead of VLOOKUP or any other database function. LOOKUP has two modes of operation - a Vector mode, and an Array mode. I suspect that if used in Array mode, it will force any data fed into its second argument to become an array.

    :") возвращает ссылку на все строки 1через 22. Затем ROWфункция использует эту ссылку на эти строки для возврата списка или массива (это часть, в которой я не уверен — я не уверен, как она создает массив) чисел {1;2;3;4;5;6;7;8;9;...22}

    Затем он используетMID, чтобы вернуть все строки 10 символов из исходного значения, используя массив линейных чисел от ДО в качестве отправной точки. MIDвозвращает другой массив,{"0xabc 2014";"xabc 20145";"abc 201452";..."e 30285 q!"}. Затем строки вынуждены выполнять математическую функцию с помощью +1. Это заставляет все строки в массиве, содержащие нечисловые символы, возвращать #VALUE!ошибки, в то время как числовые строки возвращают себя в качестве значения. Мы получаем массив как {#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;2014528888;#VALUE!;...}

    Наконец, LOOKUPпросматривает этот массив и находит наибольшее значение, которое меньше, чем первый аргумент — то есть единственный результат в массиве, который не возвращает #VALUE!

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