Я хотел удалить все буквы из ячейки, оставив только цифры.После некоторого поиска в Google я нашел эту формулу массива: =SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
Когда я нажимаю «оценить», он делает сотни истинных / ложных утверждений, которые чрезвычайно запутывают. Может кто-нибудь разобрать это и объяснить, что делает каждый компонент?
Давайте вместе пройдемся по процессу оценки:
В моем примере у меня есть значение abf5fb6 в ячейке A2 , которое оценивается как 56 .
Первый шаг, замените A2 на значение в ячейке A2
До:
= СУММА (СРЕДНИЙ (0 & A2, БОЛЬШОЙ (ИНДЕКС (НОМЕР (- СРЕДНИЙ (A2, ROW ($ 1:$ 99), 1))* ROW ($ 1:$ 99),), ROW ($ 1:$ 99))+1, 1)* 10 ^ СТРОКА ($ 1:$ 99)/10)
После:
= СУММА (СРЕДНИЙ (0 & "abf5fb6", БОЛЬШОЙ (ИНДЕКС (НОМЕР (- СРЕДНИЙ (A2, ROW (1:$ 99), 1))* ROW (1:$ 99),), ROW (1:$ 99))+1,1)* 10 ^ СТРОКА ($ 1:$ 99)/10)
Обратите внимание, что текст теперь заключен в кавычки. Это означает, что это текст, иначе известный как строка.
Сейчас самое время кратко объяснить функцию MID . Эта функция просто извлечет некоторый текст из строки. Первый аргумент - это текст или строка, с которой мы начнем. Второй аргумент - это начальная позиция, с которой мы хотим начать извлечение. Третий аргумент - это количество символов, для которых мы хотим извлечь, или длина конечного результата. Например, =MID("wizlog", 1, 3 вернет wiz а =MID("wizlog", 2, 5) вернет izlog
Итак, следующий шаг затем объединяет 0&"abf5fb6" , потому что, как отмечено выше, первый аргумент в функции MID требует текст (опять же, называемый строкой). Но чтобы добавить число в строку в Excel, вы используете символ & .
До:
= СУММА (СРЕДНИЙ (0 & "abf5fb6", БОЛЬШОЙ (ИНДЕКС (НОМЕР (- СРЕДНИЙ (A2, ROW (1:$ 99), 1))* ROW (1:$ 99),), ROW (1:$ 99))+1,1)* 10 ^ СТРОКА ($ 1:$ 99)/10)
После:
= СУММА (MID ("0abf5fb6", БОЛЬШОЙ (ИНДЕКС (НОМЕР (- СРЕДНИЙ (A2, ROW ($ 1:$ 99), 1))* ROW (1:$ 99),), ROW (1:$ 99))+1 , 1)* 10 ^ СТРОКА ($ 1:$ 99)/10)
Теперь мы перейдем к следующей функции MID чтобы заменить другой A2 на его значение снова, как и раньше.
После:
= SUM(MID("0abf5fb6", LARGE(INDEX(ISNUMBER(- MID("abf5fb6, ROW($ 1:$ 99), 1))* ROW($ 1:$ 99),), ROW($ 1:$ 99))+1,1)* 10 ^ СТРОКА ($ 1:$ 99)/10)
Далее мы имеем дело со вторым аргументом во второй функции MID , ROW($1:$99) . Помните, что второй аргумент в функции MID просто дает нам начальную позицию. С другой стороны, функция ROW просто возвращает заданную ей строку, поэтому, поскольку мы передаем ей диапазон от 1 до 99, она возвращает нам массив или список из 1–99. Это означает, что мы планируем использовать функцию MID 99 раз, каждый раз начиная с другой позиции от 1 до 99.
До:
= SUM(MID("0abf5fb6", LARGE(INDEX(ISNUMBER(- MID("abf5fb6", ROW(1:$ 99), 1))* ROW(1:$ 99),), ROW(1:$ 99))+1,1)* 10 ^ СТРОКА ($ 1:$ 99)/10)
После:
= SUM(MID("0abf5fb6", LARGE(INDEX(ISNUMBER(- MID("abf5fb6", {1; 2; 3; ...; 99}, 1))* ROW($ 1:$ 99),), ROW($ 1:$ 99))+1,1)* 10 ^ ROW($ 1:$ 99)/10)
(Чтобы сэкономить место, я не набрал все цифры от 1 до 99, но я уверен, что вы поняли.)
Теперь, когда у нас есть все части для второй функции MID , мы можем вычислить эту часть.
До:
= SUM(MID("0abf5fb6", LARGE(INDEX(ISNUMBER(- MID("abf5fb6", {1; 2; 3; ...; 99}, 1))* ROW($ 1:$ 99),), ROW($ 1:$ 99))+1,1)* 10 ^ ROW($ 1:$ 99)/10)
После:
= SUM(MID("0abf5fb6", LARGE(INDEX(ISNUMBER(- {"a"; "b"; "f"; "5"; "f"; "b"; "6"; ""; ")";" "; ...;" "})* ROW($ 1:$ 99),), ROW($ 1:$ 99))+1,1)* 10 ^ ROW($ 1:$ 99)/10)
Так что же здесь произошло? Ну, функция MID просто возвращает подстроку данной строки, начиная с заданного местоположения, для указанного количества символов. Поэтому мы дали ему строку, abf5fb6 , дали ей массив начальных позиций, указав, что мы хотим извлечь только 1 символ. Таким образом, функция возвращает нам массив каждого символа в нашей строке, от 1 до 99. Поскольку наша начальная строка была длиной всего 7 символов, то позиции 8-99 просто пусты, поэтому у нас есть все пустые подстроки после, ("";"";"";...;"";).
Следующая функция для вычисления - это функция ISNUMBER , но сначала давайте рассмотрим странную функцию, которую мы делаем в первую очередь. Обратите внимание, что перед нашим новым массивом стоит двойной минус. В то время как один знак минус полностью изменяет результат результата (если TRUE тогда возвращаются FALSE и visa-vera), но двойной минус означает принудительное использование строкового ответа в виде числа. Поэтому обычно это приводит к тому, что ответы TRUE превращаются в 1 а FALSE - в 0 , но в этом случае мы конвертируем каждый символ в нашем массиве в число. Таким образом, ввод --"a" приведет к # #VALUE! при вводе --"5" приведет к 5 .
Таким образом, когда мы выполняем функцию ISNUMBER :
= SUM(MID("0abf5fb6", LARGE(INDEX(ISNUMBER(- {"a"; "b"; "f"; "5"; "f"; "b"; "6"; ""; ")";" "; ...;" "})* ROW($ 1:$ 99),), ROW($ 1:$ 99))+1,1)* 10 ^ ROW($ 1:$ 99)/10)
То, что мы на самом деле выполняем за кулисами:
= СУММА (СРЕДНИЙ ("0abf5fb6", БОЛЬШОЙ (ИНДЕКС (НОМЕР ({# ЗНАЧЕНИЕ!; #ЗНАЧЕНИЕ!; #ЗНАЧЕНИЕ!; 5; #ЗНАЧЕНИЕ!; #ЗНАЧЕНИЕ!; 6; #ЗНАЧЕНИЕ!; #ЗНАЧЕНИЕ!; #ЗНАЧЕНИЕ!; ...; # ЗНАЧЕНИЕ!})* СТРОКА ($ 1:$ 99),), СТРОКА ($ 1:$ 99))+1,1)* 10 ^ СТРОКА ($ 1:$ 99)/10)
В результате получается массив TRUE или FALSE указывающий, было ли значение числом или нет. Отсюда получаем:
= СУММА (MID ("0abf5fb6", БОЛЬШОЙ (ИНДЕКС ({ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ...; FALSE})* ROW ($ 1:$ 99),), ROW ($ 1:$ 99))+1,1)* 10 ^ ROW ($ 1:$ 99)/10)
Время истекло Ну, я на работе делаю то, что не должен был делать, и это было настолько далеко, насколько я смог до того, как мне нужно было сделать что-то продуктивное сегодня. Надеюсь, я вернусь туда, где остановился позже.
Мы можем "развернуть" эту формулу
=SUM(
MID(
0&A2,
LARGE(
INDEX(
ISNUMBER(
--
MID(
A2,
ROW($1:$99),
1
)
)*ROW($1:$99)
,
),
ROW($1:$99)
)+1,
1
)*10^ROW($1:$99)/10
)
и следуйте всем преобразованиям операнда (ов).
Прежде всего, выражение ROW($1:$99) представляет массив растущих натуральных чисел {1,2,...,99} . Он часто используется как удобный блок для построения формулы массива.
Далее, A2 здесь - это адрес ячейки ввода, который содержит текстовую строку со смешанными числами, например, R824TX01rQ768 .
Итак, строительство
MID(
A2,
ROW($1:$99),
1
)
означает: создать массив всех символов (строки длиной 1 , согласно третьему параметру в MID(...,1)) из входа A2 <1>.
Элементы, индекс которых превышает длину строки в A2 являются пустыми строками.
К этому массиву символов добавляется двойной минус -- , который превратит числовой символ в соответствующее число, а другие символы в значение ошибки # #VALUE! <2>.
Затем функция ISNUMBER() работает с этим массивом смешанных символов и чисел и приводит к массиву логических значений true/false <3>,
который умножается поэлементно на знакомую ROW($1:$99) .
Когда значение true/false умножается на число, true интерпретируется как 1 , а false как 0 , таким образом, результатом является числовой массив, который для каждой позиции символа в A2 содержит либо 0 , если символ не является цифрой, или индекс, если символ является цифрой <4>.
функция
INDEX(<the array>,)
или, расширенный,
=INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),)
принимает этот числовой массив и пустой второй аргумент, что в основном приводит к тому же массиву <5>.
выражение
LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))
сортирует наш операнд массива, полученный из INDEX(...) в порядке убывания <6>
выражение
MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)
первый префикс строки в A2 с 0 и извлекает символы в отсортированном порядке, начиная с последней найденной цифры <7>.
И каждая цифра в полученном массиве умножается на 10^ROW($1:$99)/10 <8>.
MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10
<9>
В заключение,
=SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
суммирует все числа в массиве, что приводит к желаемому числу <10>.
Не уверен, почему используется повторяющаяся конструкция INDEX(<array>,) , похоже, что формула работает и без нее:
=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
<11>
Возможно, некоторые проблемы с совместимостью?