Я пытаюсь использовать Excel, чтобы найти старшее трехзначное число, сумма кубов цифр, составляющих это число, равна самому числу. Пример: 3 ^ 3 + 7 ^ 3 + 1 ^ 3 = 371
1 ответ
2
Формула массива **
=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 заключает в формулу фигурные скобки {} (хотя не пытайтесь вставить их вручную).