Здравствуйте! Недавно я, несмотря на имеющиеся в Интернете варианты решения головоломки Судоку, захотел найти собственное решение. В общем-то всё получилось, но на этапе "доводки", когда стал избавляться от дополнительных столбцов с расчётами, главная формула вдруг выдала ошибку #ЗНАЧ! Многократные проверки формулы показали, что отдельные фрагменты работают без ошибок и только объединенные в одну большую формулу приводят к появлению ошибки. Хотелось бы услышать мнение специалистов Excel по этому поводу.
Поскольку полное описание проблемы требует довольно много текста, поместил его в прилагаемый файл.
Ошибку вызывают два фрагмента в первой строке формулы (аргументы функции СМЕЩ "смещение по строкам" и "смещение по столбцам"): ЦЕЛОЕ((СТРОКА()-13)/9) и (СТРОКА()-13)-9*ЦЕЛОЕ((СТРОКА()-13)/9) Если эти фрагменты вывести в отдельные вспомогательные столбцы, а в формулу поместить ссылки на эти столбцы, тогда всё работает...
Изображение окна "Вычислить формулу" непосредственно перед появлением ошибки и следующего шага с ошибкой:
Здравствуйте! Недавно я, несмотря на имеющиеся в Интернете варианты решения головоломки Судоку, захотел найти собственное решение. В общем-то всё получилось, но на этапе "доводки", когда стал избавляться от дополнительных столбцов с расчётами, главная формула вдруг выдала ошибку #ЗНАЧ! Многократные проверки формулы показали, что отдельные фрагменты работают без ошибок и только объединенные в одну большую формулу приводят к появлению ошибки. Хотелось бы услышать мнение специалистов Excel по этому поводу.
Поскольку полное описание проблемы требует довольно много текста, поместил его в прилагаемый файл.
Ошибку вызывают два фрагмента в первой строке формулы (аргументы функции СМЕЩ "смещение по строкам" и "смещение по столбцам"): ЦЕЛОЕ((СТРОКА()-13)/9) и (СТРОКА()-13)-9*ЦЕЛОЕ((СТРОКА()-13)/9) Если эти фрагменты вывести в отдельные вспомогательные столбцы, а в формулу поместить ссылки на эти столбцы, тогда всё работает...
Изображение окна "Вычислить формулу" непосредственно перед появлением ошибки и следующего шага с ошибкой: VictorK
Спасибо, Сергей. Я эту тему читал и файлы смотрел. Но меня больше интересует другое - почему правильная формула выдаёт ошибку. Видимо, с подобным мало кому пришлось сталкиваться на практике. Наверное, какая нибудь внутренняя ошибка Excel.
Предельно упростил формулу, оставил только проблемный участок:
Код
=СУММПРОИЗВ((СМЕЩ(A1;СТРОКА();;;))*1)
На пустой лист вставляю эту формулу в любую ячейку и получаю #ЗНАЧ! Отдельно СМЕЩ(A1;СТРОКА();;;) прекрасно работает, а в качестве аргумента СУММПРОИЗВ вдруг становится источником ошибки, причём ошибку вызывает именно СТРОКА()
Спасибо, Сергей. Я эту тему читал и файлы смотрел. Но меня больше интересует другое - почему правильная формула выдаёт ошибку. Видимо, с подобным мало кому пришлось сталкиваться на практике. Наверное, какая нибудь внутренняя ошибка Excel.
Предельно упростил формулу, оставил только проблемный участок:
Код
=СУММПРОИЗВ((СМЕЩ(A1;СТРОКА();;;))*1)
На пустой лист вставляю эту формулу в любую ячейку и получаю #ЗНАЧ! Отдельно СМЕЩ(A1;СТРОКА();;;) прекрасно работает, а в качестве аргумента СУММПРОИЗВ вдруг становится источником ошибки, причём ошибку вызывает именно СТРОКА()VictorK
Сообщение отредактировал VictorK - Понедельник, 16.12.2013, 12:20
VictorK, Прив) Не знаю, не пропал ли ещё интерес к этому вопросу или может ответ уже сами нашли за 8 лет, да и не факт что загляните, но раз ответа так и не дали, то напишу его я для тех кто тоже наткнется на эту тему.
Дело в том что СУММПРОИЗВ подобно АГРЕГАТу рассчитывает формулы внутри себя как массивы, даже если в таких формулах как СТРОКА и СТОЛБЕЦ выделена всего одна ячейка, при вычислении они считаются массивом, что хорошо видно по фигурным скобкам по бокам числа 13 на картинке, которое получилось в результате вычисления строки. А СМЕЩ не вычисляется когда в её параметрах смещения по строкам и столбцам есть массивы, она тогда выдает ошибку.
Решить это легко, надо вместо СТРОКА и СТОЛБЕЦ использовать формулу ЯЧЕЙКА("строка";A1) ЯЧЕЙКА("столбец";A1), в ней тоже есть возможность выдавать номер строки и стобца, но при этом она не превращает результат в массив. Собственно вот и ответ.
VictorK, Прив) Не знаю, не пропал ли ещё интерес к этому вопросу или может ответ уже сами нашли за 8 лет, да и не факт что загляните, но раз ответа так и не дали, то напишу его я для тех кто тоже наткнется на эту тему.
Дело в том что СУММПРОИЗВ подобно АГРЕГАТу рассчитывает формулы внутри себя как массивы, даже если в таких формулах как СТРОКА и СТОЛБЕЦ выделена всего одна ячейка, при вычислении они считаются массивом, что хорошо видно по фигурным скобкам по бокам числа 13 на картинке, которое получилось в результате вычисления строки. А СМЕЩ не вычисляется когда в её параметрах смещения по строкам и столбцам есть массивы, она тогда выдает ошибку.
Решить это легко, надо вместо СТРОКА и СТОЛБЕЦ использовать формулу ЯЧЕЙКА("строка";A1) ЯЧЕЙКА("столбец";A1), в ней тоже есть возможность выдавать номер строки и стобца, но при этом она не превращает результат в массив. Собственно вот и ответ.ZetMenChavo
Сообщение отредактировал ZetMenChavo - Четверг, 11.11.2021, 21:03
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
