[решено] Как обойти ограничение количества вложенных друг в друга функций ЕСЛИ

[Алекс Амолайнен]

Доброй ночи.
Помогите составить формулу. Требуется реализовать возвращение значения из заданного условиями столбца в текущей строчке, плюс округлить эти значения. В приложенном документе присутствует диапазон ячеек, заполненными числами. Самостоятельно провёл работу в направлении 2 функций, ЕСЛИ и ПРОСМОТР:

ЕСЛИ: на листе Microsoft Office Excel 97-2003, при сохранении формулы появляется оповещение с текстом: "He удается ввести указанную формулу, поскольку она использует больше уровней вложенности, чем допускается текущим форматом файла". На листе, созданном 2007-м Офисом, такой проблемы не наблюдается, но этот не вариант не подходит, т.к. документ будет часто редактироваться на младших версиях программы.

Также не работает функция ПРОСМОТР. При попытке вбить в форму массива номера ячеек через знак равенства, появляется сообщение об ошибке в формуле (не говоря о том, что эти значения нужно ещё и округлить).

Все, абсолютно все клетки диапазона, будут постоянно содержать значения (простые числа, числа с десятыми, сотыми и т.д. или ошибки #Н/Д, #ССЫЛКА, и т.п.), поэтому функции, типа ГПР, неверное не подойдут. Хотя, в этом я не уверен и обращаюсь к опытным пользователям за помощью. Подскажите, есть ли возможность научить документ делать вычисления без создания дополнительных столбцов для обработки промежуточных данных или укажите иной способ решения задачи.
Спасибо.

[okshef]

Для ячейки Н10:
=ОКРУГЛ(СУММПРОИЗВ(($K$9:$U$9=G10)*(K10:U10));0)
и протяните вниз

P.S. У вас в ячейке H24 будет ошибка, т.к. в N24 - пусто
...
С помощью функции ПРОСМОТР (ячейка I10):
=ОКРУГЛ(ПРОСМОТР(G10;$K$9:$U$9;K10:U10);0)

Но тут еще больше ошибок. Поиск багов - ваш :)
...
Формула массива в J10:
=ОКРУГЛ(СУММ((G10=$K$9:$U$9)*(K10:U10));0)
Ошибки те же, что и в случае 1.

[Алекс Амолайнен]

okshef, спасибо.
С функцией СУММПРОИЗВ расчёты производятся отлично.
По функции ПРОСМОТР, я не уверен, но смею предположить, что расчёт в некоторых ячейках некорректен ввиду большого количества символов в аргументе: 10S10 и 11S11, вместо 1S1, 2S2, 3S3 и т.д.
А по функции СУММ: тут вообще появляется #ЗНАЧ! Возможно дело в присутствии буквы с аргументе, либо это связано с версией программы.

[okshef]

А по функции СУММ: тут вообще появляется #ЗНАЧ! »
Формула массива »
Ввод - Ctrl + Shift + Enter

[Алекс Амолайнен]

okshef, спасибо. Вы не в первый раз приводите формулы решения задач по расчётам в Excel, с такими составляющими: (E39:$E$40)*(E39:$E$40=E40) и (G10=$K$9:$U$9)*(K10:U10). Поделитесь источником, где черпаете информацию? Хотелось бы самому научиться обрабатывать данные в Excel в таком варианте. А то, что это такое: если бы старые версии программы поддерживали больше 7 функций ЕСЛИ, вложены друг в друга в качестве значений аргументов, моё творение в документе, приложенном к этой теме, выглядело бы так:
=ЕСЛИ(G10="1S1";ОКРУГЛ(K10;0);ЕСЛИ(G10="2S2";ОКРУГЛ(L10;0);ЕСЛИ(G10="3S3";ОКРУГЛ(M10;0);ЕСЛИ(G10="4S4";ОКРУГЛ(N10;0);ЕСЛИ(G10="5S5";ОКРУГЛ(O10;0);ЕСЛИ(G10="6S6";ОКРУГЛ(P10;0);ЕСЛИ(G10="7S7";ОКРУГЛ(Q10;0);ЕСЛИ(G10="8S8";ОКРУГЛ(R10;0);ЕСЛИ(G10="9S9";ОКРУГЛ(S10;0);ЕСЛИ(G10="10S10";ОКРУГЛ(T10;0);ЕСЛИ(G10="11S11";ОКРУГЛ(U10;0);"ЛОЖЬ")))))))))))Ужас!

[okshef]

Алекс Амолайнен, источников много: сайты и форумы. Могу привести некоторые из моих закладок:
Планета Excel (http://www.planetaexcel.ru/) - блог и форум

Learn Excel Blog (http://www.mrexcel.com/learnexcel/blog/)

Мир MS Excel (http://www.excelworld.ru/)

Excel - это не сложно! Трюки и приемы работы в Excel (http://www.excel-vba.ru/)

и немного экзотики Excel Automation - Ron de Bruin (http://www.rondebruin.nl/index.htm)

Не могу, к сожалению, похвастаться глубоким изучением всего этого...

[Алекс Амолайнен]

Ну это Вы слукавили. Мне, даже после Ваших подсказок и готовых решений, иногда требуется довольно много времени, чтобы сообразить, как это применить в случае изменения условий. Вот, например, я долго думал как подредактировать формулы, чтобы они продолжали работать при изменении условий задачи и очистить некоторые неинформативные клетки от значений. В этом случае, 2 из 3 приведённых Вами формул, перестают работать, а наиболее подходящей окажется функция ПРОСМОТР (=ОКРУГЛ(ПРОСМОТР(G10;$K$9:$U$9;K10:U10);0)), т.к. она не перемножает в себе массивы, но она также давала ошибки в некоторых клетках. А требовалось-то всего лишь отсортировать заголовки по алфавиту. Видимо, по этой причине Вы и шутнулиПоиск багов - ваш »


okshef, спасибо за ссылки.

[okshef]

2 из 3 приведённых Вами формул, перестают работать »
ну это совсем не сложно. Используйте ЕСЛИ и обработчики ошибок. К сожалению, в 2003-м их арсенал невелик: Функции проверки типа (http://office.microsoft.com/ru-ru/excel-help/HP005209147.aspx)

Решение такое (пример):
ЕСЛИ(ЕОШИБКА([ваша формула]);[вычисление или значение в случае ошибки];[вычисление по вашей формуле])
Подсвеченное фактически является одним и тем же.