Как найти трехзначное число в excel

Формула массива **

=99+MATCH(1,0/(MMULT(MID(ROW(INDEX(A:A,100):INDEX(A:A,999)),{1,2,3},1)^3,{1;1;1})=ROW(INDEX(A:A,100):INDEX(A:A,999))))

В качестве объяснения, часть:

ROW(INDEX(A:A,100):INDEX(A:A,999))

генерирует массив целых чисел от 100 до 999 включительно. Это строгая и минимально изменчивая (на самом деле, изменчивая на открытой книге книга, как я понимаю: http://www.decisionmodels.com/calcsecretsi.htm) конструкция, которая по этой причине предпочтительнее для обеих полностью изменчивых:

ROW(INDIRECT("100:999"))

и энергонезависимый, хотя и не строгий (а именно, подверженный вставке строк в лист):

ROW(100:999)

(или ROW($100:$999) — использование абсолютных ссылок никоим образом не влияет на вышеупомянутую восприимчивость.)

Поскольку мы хотим рассмотреть каждую из 3 цифр в каждой из этих 900 записей, мы применяем MID с массивом параметров start_num , таким образом:

MID(ROW(INDEX(A:A,100):INDEX(A:A,999)),{1,2,3},1)

который:

MID({100;101;102;103;104;105;...;999},{1,2,3},1)

т.е.

{"1","0","0";"1","0","1";"1","0","2";"1","0","3";"1","0","4";"1","0","5";,,,;"9","9","9"}

Затем мы берем куб каждого из этих значений, так что:

MID(ROW(INDEX(A:A,100):INDEX(A:A,999)),{1,2,3},1)^3

который:

{"1","0","0";"1","0","1";"1","0","2";"1","0","3";"1","0","4";"1","0","5";,,,;"9","9","9"}^3

дает:

{1,0,0;1,0,1;1,0,8;1,0,27;1,0,64;1,0,125;,,,;729,729,729}

MMULT затем используется для суммирования каждого из 900 триплетов в этом массиве, так что:

MMULT(MID(ROW(INDEX(A:A,100):INDEX(A:A,999)),{1,2,3},1)^3,{1;1;1})

который:

MMULT({1,0,0;1,0,1;1,0,8;1,0,27;1,0,64;1,0,125;,,,;729,729,729},{1;1;1})

то есть:

{1;2;9;28;65;126;,,,;2187}

Осталось только определить последнее (то есть самое большое) вхождение, для которого значение в этом результирующем массиве равно самому исходному значению, так что сравнение:

MMULT(MID(ROW(INDEX(A:A,100):INDEX(A:A,105)),{1,2,3},1)^3,{1;1;1})=ROW(INDEX(A:A,100):INDEX(A:A,999))

который:

{1;2;9;28;65;126;,,,;2187}={100;101;102;103;104;105;,,,;999}

то есть:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;,,,;FALSE}

Возврат этого массива с нуля производит:

{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;,,,;#DIV/0!}

т. е. массив, записи которого равны только 1 (если запись перед возвратом была TRUE) или #DIV/0! (если вход до взаимности был FALSE).

Таким образом, когда мы теперь передаем этот массив в MATCH с параметром match_type, равным 1 (или, что то же самое, опущен), он имеет подходящую форму, так что мы можем вернуть относительную позицию последнего числового значения, которое меньше нашего выбора lookup_value (1 здесь, хотя любое числовое значение больше нуля также будет достаточно).

Следовательно:

MATCH(1,0/(MMULT(MID(ROW(INDEX(A:A,100):INDEX(A:A,999)),{1,2,3},1)^3,{1;1;1})=ROW(INDEX(A:A,100):INDEX(A:A,999))))

который:

MATCH(1,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;,,,;#DIV/0!})

возвращает 308.

Поскольку массив, который мы передали на рассмотрение, начинается с 100, а не с 1, мы, конечно, должны добавить 99 к этому значению, так что:

99+MATCH(1,0/(MMULT(MID(ROW(INDEX(A:A,100):INDEX(A:A,999)),{1,2,3},1)^3,{1;1;1})=ROW(INDEX(A:A,100):INDEX(A:A,999))))

который

99+308

возвращает 407, и поэтому я заключаю, что это ответ на вопрос ОП.

С уважением

** Формулы массива вводятся не так, как «стандартные» формулы. Вместо того, чтобы просто нажимать ENTER, вы сначала удерживаете CTRL и SHIFT, и только потом нажимаете ENTER. Если вы сделали это правильно, вы заметите, что Excel заключает в формулу фигурные скобки {} (хотя не пытайтесь вставить их вручную).

Извлечем из числа 123 456 789 единицы (789), десятки (8), сотни (7), тысячи (456) и миллионы (123)

.

Введем число от 0 до 1 млрд. в ячейку

B1

. Нижеследующие формулы извлекают (см.

Файл примера

):

• миллионы:
  
  =ОКРУГЛВНИЗ(B1;-6)/1000000
  
• тысячи:
  
  =ОСТАТ(ОТБР(B1/1000);1000)
  
• 0-999:
  
  =ОСТАТ(B1;1000)
  
• сотни:
  
  =ОСТАТ(ОТБР(B1/100);10)
  
• десятки:
  
  =ОСТАТ(ОТБР(B1/10);10)
  
• единицы:
  
  =ОСТАТ(ОТБР(B1);10)
  

В ячейке

B1

можно отобразить, например, миллионы (123М), не изменяя само число. Это можно сделать, применив соответствующий

числовой пользовательский формат

.

ПРИМЕНЕНИЕ

: Приведенные выше решения использованы в статье

Число прописью

.

1 ответ:

Читайте также