Как уменьшить формулу (гигантскую)

[роман-талица]

Всем Здравствуйте ! Вот думал что все формула заработала , но когда начал увеличивать то размер стал иметь значение , в итоге тупик .
Формула достигла 8 000 символов а больше нельзя в эксель , а я хотел ее увеличить до 1.5 млн символов . Подробности программы во вложении в примечаниях (ячейка R5C10).

[IgorGO]

в ячейках (5,1347), (6,1347) ... (10,1347)
были разные формулы типа:

=ЕСЛИ(R1C1347=1;RC[-38])+ЕСЛИ(R1C1347=2;R[1]C[-38])+ЕСЛИ(R1C1347=3;R[2]C[-38])+ЕСЛИ(R1C1347=4;R[3]C[-38])+ЕСЛИ(R1C1347=5;R[4]C[-38])+ЕСЛИ(R1C1347=6;R[5]C[-38])+ЕСЛИ(R1C1347=7;R[6]C[-38])+ЕСЛИ(R1C1347=8;R[7]C[-38])+ЕСЛИ(R1C1347=9;R[8]C[-38])+ЕСЛИ(R1C1347=10;R[9]C[-38])+ЕСЛИ(R1C1347=11;R[10]C[-38])+ЕСЛИ(R1C1347=12;R[11]C[-38])+ЕСЛИ(R1C1347=13;R[12]C[-38])+ЕСЛИ(R1C1347=14;R[13]C[-38])+ЕСЛИ(R1C1347=15;R[14]C[-38])+ЕСЛИ(R1C1347=16;R[15]C[-38])+ЕСЛИ(R1C1347=17;R[16]C[-38])+ЕСЛИ(R1C1347=18;R[17]C[-38])+ЕСЛИ(R1C1347=19;R[18]C[-38])+ЕСЛИ(R1C1347=20;R[19]C[-38])+ЕСЛИ(R1C1347=21;R[20]C[-38])+ЕСЛИ(R1C1347=22;R[21]C[-38])+ЕСЛИ(R1C1347=23;R[22]C[-38])+ЕСЛИ(R1C1347=24;R[23]C[-38])+ЕСЛИ(R1C1347=25;R[24]C[-38])+ЕСЛИ(R1C1347=26;R[25]C[-38])+ЕСЛИ(R1C1347=27;R[26]C[-38])+ЕСЛИ(R1C1347=28;R[27]C[-38])+ЕСЛИ(R1C1347=29;R[28]C[-38])+ЕСЛИ(R1C1347=30;R[29]C[-38])+ЕСЛИ(R1C1347=31;R[30]C[-38])+ЕСЛИ(R1C1347=32;R[31]C[-38])+ЕСЛИ(R1C1347=33;R[32]C[-38])+ЕСЛИ(R1C1347=34;R[33]C[-38])+ЕСЛИ(R1C1347=35;R[34]C[-38])+ЕСЛИ(R1C1347=36;R[35]C[-38])

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

заменил одной формулой:

=ИНДЕКС(C[-38];ПОИСКПОЗ(ИНДЕКС(R1C1347:R1C1351;СТРОКА()-4);C[-40];))

которая выдает точно теже результаты

гиганской формулы в 1.5 млн. знаков не нашел (и хорошо...)

[Serge 007]

Сообщение от роман-талица

я хотел ее увеличить до 1.5 млн символов

В Вашем случае необходимо использовать доп столбцы или макросы

По укорачиванию формулы (в ячейке J5):
Вот этот фрагмент (буду объяснять на нём, потому что остальные аналогичны по структуре):

=ЕСЛИ(И($B1=J$1;$B2=J$2);1;0)+ЕСЛИ(И($B1=J$1;$C2=J$2);1;0)+ЕСЛИ(И($B1=J$1;$E2=J$2);1;0)+ЕСЛИ(И($B1=J$1;$F2=J$2);1;0)

длинной в 115 знаков, четырежды проверяет одно и тоже условие $B1=J$1. Это надо убирать, например так:

=($B1=J$1)*(($B2=J$2)+($C2=J$2)+($E2=J$2)+($F2=J$2))

Получается уже 51 знак, т.е. в 2,25 раз короче
Для формулы в 8 000 знаков это означает её сокращение до размера около 3 500 знаков
Пойдём далее. В Вашей формуле так же четырежды сравниваются значения диапазона B2:F2 и ячейки J2. Это тоже необходимо устранять, путём прямого однократного сравнения J2=B2:F2. В результате чего получаем вместо 115-ти знаков всего 28:

=СУММ(($B1=J$1)*(J$2=$B2:F2))

Исходная формула укорачивается в 4,1 раза (общая длинна не более 2000 знаков), причём она не будет увеличиваться при увеличении количества проверяемых столбцов в диапазоне, сейчас обозначенном как B2:F2.

И это только на первый взгляд. Наверняка можно сократить ещё. Всерьёз разбирать формулу в 8 000 знаков совершенно не хочется, да и время позднее

[роман-талица]

Пытаюсь понять то что вы мне предлагаете , но ни чего не работает .

Описание программы в ячейке J5

[staniiislav]

не совсем я понял чего вы добиваетесь, но для J5 попробуйте вот такую формулу:

=ЕСЛИ(И(СУММПРОИЗВ((N$1=$B1:$F1)*1)>0;СУММПРОИЗВ((N$2=$B2:$F12)*1)>0);СУММПРОИЗВ((N$2=$B2:$F12)*1);)

[роман-талица]

Сообщение от staniiislav

не совсем я понял чего вы добиваетесь, но для J5 попробуйте вот такую формулу:

=ЕСЛИ(И(СУММПРОИЗВ((N$1=$B1:$F1)*1)>0;СУММПРОИЗВ((N$2=$B2:$F12)*1)>0);СУММПРОИЗВ((N$2=$B2:$F12)*1);)

Спасибо огромное ! Ваша формула заработала сразу . Я просто сам запутался и перемудрил , а вы даже при этом сделали все правильно .
Мне оказывается нужно было все варианты раздельно а не вместе. А формула длинная оказывается даже мне не нужна была бы . Но все равно не маленькая где то 800 символов .
Всем большое спасибо !

Это моя была бы (т.е. я с ней работал) =ЕСЛИ(И($B1=J$1;$B2=J$2);1;0)+ЕСЛИ( И($B1=J$1;$C2=J$2);1;0)+ЕСЛИ(И($B1= J$1;$D2=J$2);1;0)+ЕСЛИ(И($B1=J$1;$E 2=J$2);1;0)+ЕСЛИ(И($B1=J$1;$F2=J$2) ;1;0)+ЕСЛИ(И($C1=J$1;$B2=J$2);1;0)+ ЕСЛИ(И($C1=J$1;$C2=J$2);1;0)+ЕСЛИ(И ($C1=J$1;$D2=J$2);1;0)+ЕСЛИ(И($C1=J $1;$E2=J$2);1;0)+ЕСЛИ(И($C1=J$1;$F2 =J$2);1;0)+ЕСЛИ(И($D1=J$1;$B2=J$2); 1;0)+ЕСЛИ(И($D1=J$1;$C2=J$2);1;0)+Е СЛИ(И($D1=J$1;$D2=J$2);1;0)+ЕСЛИ(И( $D1=J$1;$E2=J$2);1;0)+ЕСЛИ(И($D1=J$ 1;$F2=J$2);1;0)+ЕСЛИ(И($E1=J$1;$B2= J$2);1;0)+ЕСЛИ(И($E1=J$1;$C2=J$2);1 ;0)+ЕСЛИ(И($E1=J$1;$D2=J$2);1;0)+ЕС ЛИ(И($E1=J$1;$E2=J$2);1;0)+ЕСЛИ(И($ E1=J$1;$F2=J$2);1;0)+ЕСЛИ(И($F1=J$1 ;$B2=J$2);1;0)+ЕСЛИ(И($F1=J$1;$C2=J $2);1;0)+ЕСЛИ(И($F1=J$1;$D2=J$2);1; 0)+ЕСЛИ(И($F1=J$1;$E2=J$2);1;0)+ЕСЛ И(И($F1=J$1;$F2=J$2);1;0)

А в таком виде вместо моей ваша =ЕСЛИ(И(СУММПРОИЗВ((J$1=$B1:$F1)*1) >0;СУММПРОИЗВ((J$2=$B2:$F2)*1)>0);С УММПРОИЗВ((J$2=$B2:$F2)*1)

Еще раз всем спасибо !