Добрый день. Подскажите, пожалуйста, возникают очень большие странности с функцией СУММПРОИЗВ и вложенной в нее МАКС и ОСТАТ: возникает вообще бред: одна и та же формула, записанная в разных ячейках, дает разный результат! Как такое вообще может быть? Использую вот такую формулу (в столбце F): =СУММПРОИЗВ(МАКС($B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1))) Она то и работает неправильно
При просмотре как вычисляется формула, обнаруживается что почему-то в ОСТАТ не берется диапазон, а берется одна текущая ячейка. Тем не менее при расчете не МАКС, а просто суммы, все считается корректно, ОСТАТ берет именно диапазон а не текущую ячейку. Т.е. СУММПРОИЗВ не может использовать вложенные буквенные формулы? Что делать, чем пользоваться?
Прикладываю Excel, в котором все это видно.
Что самое интересное - если нажать F9 (принудительный расчет), то результат считается правильно. Может какие-то настройки Excel?
Добрый день. Подскажите, пожалуйста, возникают очень большие странности с функцией СУММПРОИЗВ и вложенной в нее МАКС и ОСТАТ: возникает вообще бред: одна и та же формула, записанная в разных ячейках, дает разный результат! Как такое вообще может быть? Использую вот такую формулу (в столбце F): =СУММПРОИЗВ(МАКС($B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1))) Она то и работает неправильно
При просмотре как вычисляется формула, обнаруживается что почему-то в ОСТАТ не берется диапазон, а берется одна текущая ячейка. Тем не менее при расчете не МАКС, а просто суммы, все считается корректно, ОСТАТ берет именно диапазон а не текущую ячейку. Т.е. СУММПРОИЗВ не может использовать вложенные буквенные формулы? Что делать, чем пользоваться?
Прикладываю Excel, в котором все это видно.
Что самое интересное - если нажать F9 (принудительный расчет), то результат считается правильно. Может какие-то настройки Excel?Serrg
Здравствуйте 1) Чтобы ввести формулу массива, поставьте курсор в строку формул и нажмите сочетание клавиш Ctrl+Shift+Enter 2) на мой взгляд, нет смысла в функции СУММПРОИЗВ, так как МАКС не возвращает массив, а только одно значение. Что Вы хотите посчитать этой формулой?
Здравствуйте 1) Чтобы ввести формулу массива, поставьте курсор в строку формул и нажмите сочетание клавиш Ctrl+Shift+Enter 2) на мой взгляд, нет смысла в функции СУММПРОИЗВ, так как МАКС не возвращает массив, а только одно значение. Что Вы хотите посчитать этой формулой?Pelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Чтобы ввести формулу массива, поставьте курсор в строку формул и нажмите сочетание клавиш Ctrl+Shift+Enter
Да это я все знаю. Я как раз в файле и привел пример - что по формуле массива МАКС работает корректно. Но почему некорректно работает СУММПРОИЗВ? Тут какой-то очень тонкий нюанс, я подозреваю насчет 2-го уровня вложенности буквенных формул. Посмотрите сами столбец F и столбец H. Формулы абсолютно одинаковы, только в одном СУММПРОИЗВ, а в другом - формула массива для МАКС.
Я естественно знаю что МАКС мне вернет одно значение - оно мне и нужно.
Ведь все нормально считается МАКС с помощью СУММПРОИЗВ, когда нет 2-х уровней вложенности. Но почему тут возникла такая проблема.
Посмотрите внимательнее - абсолютна одна и та же формула, введенная в разные ячейки, дает разный результат. Это просто уму непостижимо!! как такое может быть?
Чтобы ввести формулу массива, поставьте курсор в строку формул и нажмите сочетание клавиш Ctrl+Shift+Enter
Да это я все знаю. Я как раз в файле и привел пример - что по формуле массива МАКС работает корректно. Но почему некорректно работает СУММПРОИЗВ? Тут какой-то очень тонкий нюанс, я подозреваю насчет 2-го уровня вложенности буквенных формул. Посмотрите сами столбец F и столбец H. Формулы абсолютно одинаковы, только в одном СУММПРОИЗВ, а в другом - формула массива для МАКС.
Я естественно знаю что МАКС мне вернет одно значение - оно мне и нужно.
Ведь все нормально считается МАКС с помощью СУММПРОИЗВ, когда нет 2-х уровней вложенности. Но почему тут возникла такая проблема.
Посмотрите внимательнее - абсолютна одна и та же формула, введенная в разные ячейки, дает разный результат. Это просто уму непостижимо!! как такое может быть?Serrg
прежде, чем кричать "абсурд" и "мы фсе умрём", неплохо бы вдуматьсяв то, что Вам отвечают. Ваши формулы в столбце F, будучи введёнными как формула массива, дают тот же результат, что и в G.
прежде, чем кричать "абсурд" и "мы фсе умрём", неплохо бы вдуматьсяв то, что Вам отвечают. Ваши формулы в столбце F, будучи введёнными как формула массива, дают тот же результат, что и в G.ikki
Эта часть правильно сработает только в формуле массива
А почему тогда эта же часть работает абсолютно корректно (как массив) в формуле для СУММПРОИЗВ в столбце J, где я считаю просто сумму а не МАКС? Согласны? ведь работает же корректно? Значит не все так просто, порядок расчета ОСТАТ зависит от того, что в формуле перед ним стоит МАКС.
Тогда как определить логику, в каких формулах для СУММПРОИЗВ корректно сработает ОСТАТ, а в каких - нет?
Эта часть правильно сработает только в формуле массива
А почему тогда эта же часть работает абсолютно корректно (как массив) в формуле для СУММПРОИЗВ в столбце J, где я считаю просто сумму а не МАКС? Согласны? ведь работает же корректно? Значит не все так просто, порядок расчета ОСТАТ зависит от того, что в формуле перед ним стоит МАКС.
Тогда как определить логику, в каких формулах для СУММПРОИЗВ корректно сработает ОСТАТ, а в каких - нет?Serrg
Ну т.е. смотрите, переформулирую вопрос так: Почему эта формула работает корректно, когда НЕ введена как массив ({}): =СУММПРОИЗВ($B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1))
А вот эта формула уже не работает без массива: =СУММПРОИЗВ(МАКС($B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1)))
В чем их принципиальное отличие?
Ну т.е. смотрите, переформулирую вопрос так: Почему эта формула работает корректно, когда НЕ введена как массив ({}): =СУММПРОИЗВ($B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1))
А вот эта формула уже не работает без массива: =СУММПРОИЗВ(МАКС($B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1)))
и в том, и в другом случае даёт массив {0;0;3;0;0;6;0;0;9;0} Функция СУММПРОИЗВ() может с этим массивом работать, а функция МАКС - только, если ввести формулу как формулу массива. У Вас же в столбце Н формула
Код
=МАКС($B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1))
не работает без массивного ввода
Вот эта часть
Код
$B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1)
и в том, и в другом случае даёт массив {0;0;3;0;0;6;0;0;9;0} Функция СУММПРОИЗВ() может с этим массивом работать, а функция МАКС - только, если ввести формулу как формулу массива. У Вас же в столбце Н формула
Смотрите: 1. кусок $B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1) дает нам массив 2. с помощью СУММПРОИЗВ мы складываем КАЖДЫЙ элемент этого массива, получаем сумму 2а. с помощью МАКС (при массивном вводе) мы выбираем ОДНО значение массива (максимальное). И как потом его не складывай, все равно получится только оно. Поэтому СУММПРОИЗВ тут избыточна (но и не мешает).
Смотрите: 1. кусок $B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1) дает нам массив 2. с помощью СУММПРОИЗВ мы складываем КАЖДЫЙ элемент этого массива, получаем сумму 2а. с помощью МАКС (при массивном вводе) мы выбираем ОДНО значение массива (максимальное). И как потом его не складывай, все равно получится только оно. Поэтому СУММПРОИЗВ тут избыточна (но и не мешает)._Boroda_
и в том, и в другом случае даёт массив {0;0;3;0;0;6;0;0;9;0} Функция СУММПРОИЗВ() может с этим массивом работать, а функция МАКС - только, если ввести формулу как формулу массива. У Вас же в столбце Н формула
На самом деле МАКС работает нормально с массивами, когда она под СУММПРОИЗВ, без формул массива.
Тогда посмотрите столбец G. Формула записана в виде: =СУММПРОИЗВ(МАКС($B$5:$B$14*($E$5:$E$14))) без всяких формул массива. И в ней функция МАКС принимает на вход тот же самый массив {0;0;3;0;0;6;0;0;9;0} И все работает без формул массива. Но тут отличие в том что массив был вычислен по отдельному столбцу.
и в том, и в другом случае даёт массив {0;0;3;0;0;6;0;0;9;0} Функция СУММПРОИЗВ() может с этим массивом работать, а функция МАКС - только, если ввести формулу как формулу массива. У Вас же в столбце Н формула
На самом деле МАКС работает нормально с массивами, когда она под СУММПРОИЗВ, без формул массива.
Тогда посмотрите столбец G. Формула записана в виде: =СУММПРОИЗВ(МАКС($B$5:$B$14*($E$5:$E$14))) без всяких формул массива. И в ней функция МАКС принимает на вход тот же самый массив {0;0;3;0;0;6;0;0;9;0} И все работает без формул массива. Но тут отличие в том что массив был вычислен по отдельному столбцу.Serrg
Сообщение отредактировал Serrg - Четверг, 14.08.2014, 19:39
кусок $B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1) дает нам массив
Да вот нет к сожалению, часть (ОСТАТ($C$5:$C$14;3)=1) не дает массив, она вычисляет не диапазон, а текущую ячейку.
Тогда как вы объясните что в разных ячейках одна и та же формула дает разный результат? Как??? Потому что из вашей логики она бы давала один и тот же результат в каждой ячейке. Посмотрите столбец F - одна и та же формула в разных ячейках дает разный результат.
кусок $B$5:$B$14*(ОСТАТ($C$5:$C$14;3)=1) дает нам массив
Да вот нет к сожалению, часть (ОСТАТ($C$5:$C$14;3)=1) не дает массив, она вычисляет не диапазон, а текущую ячейку.
Тогда как вы объясните что в разных ячейках одна и та же формула дает разный результат? Как??? Потому что из вашей логики она бы давала один и тот же результат в каждой ячейке. Посмотрите столбец F - одна и та же формула в разных ячейках дает разный результат.Serrg
Давайте еще раз: МАКС с массивом работает нормально, а вот МАКС в сочетании с ОСТАТ (как и с большинством остальных функций, например, с ЕСЛИ) с массивом без массивного ввода НЕ РАБОТАЕТ!
Давайте еще раз: МАКС с массивом работает нормально, а вот МАКС в сочетании с ОСТАТ (как и с большинством остальных функций, например, с ЕСЛИ) с массивом без массивного ввода НЕ РАБОТАЕТ!_Boroda_
а вот МАКС в сочетании с ОСТАТ (как и с большинством остальных функций, например, с ЕСЛИ) с массивом без массивного ввода НЕ РАБОТАЕТ!
Ну это я уже и сам понял, я пробовал с разными функциями играться. Но вопрос то на форуме я решил задать для того, чтобы разобраться с логикой - почему именно не работает?
Тогда так сформулировать вопрос - какие функции не могут работать с другими, а с какими могут? Как составить такое соответствие и на чем оно будет основано? Например я выяснил что не только МАКС не работает с вложенными функциями, но и СУММ и МИН. Может и какие-то другие есть. Но только как определить что и с чем сочетается? Потому что можно написать много функций, а потом выяснить, что оказывается что-то с чем-то не сочетается. Причем Excel-то никакую ошибку не показывает, просто цифра неправильная и все. А это только вручную проверять на калькуляторе.
а вот МАКС в сочетании с ОСТАТ (как и с большинством остальных функций, например, с ЕСЛИ) с массивом без массивного ввода НЕ РАБОТАЕТ!
Ну это я уже и сам понял, я пробовал с разными функциями играться. Но вопрос то на форуме я решил задать для того, чтобы разобраться с логикой - почему именно не работает?
Тогда так сформулировать вопрос - какие функции не могут работать с другими, а с какими могут? Как составить такое соответствие и на чем оно будет основано? Например я выяснил что не только МАКС не работает с вложенными функциями, но и СУММ и МИН. Может и какие-то другие есть. Но только как определить что и с чем сочетается? Потому что можно написать много функций, а потом выяснить, что оказывается что-то с чем-то не сочетается. Причем Excel-то никакую ошибку не показывает, просто цифра неправильная и все. А это только вручную проверять на калькуляторе.Serrg
Выделите этот кусок в строке формул и нажмите клавишу F9 - Вы увидите массив
А вот если нажать "вычислить формулу" и посмотреть этапы вычисления, то можно увидеть, что при вычислении ОСТАТ($C$5:$C$14;3) по непонятной причине подставляется не указанный диапазон, а только текущая ячейка, соответствующая той строке, в которой мы находимся.
Так что в этом-то и проблема - что в сочетании с МАКС формула ОСТАТ не генерирует массив, а Excel зачем-то подставляет только одну текущую ячейку. Бред!
Выделите этот кусок в строке формул и нажмите клавишу F9 - Вы увидите массив
А вот если нажать "вычислить формулу" и посмотреть этапы вычисления, то можно увидеть, что при вычислении ОСТАТ($C$5:$C$14;3) по непонятной причине подставляется не указанный диапазон, а только текущая ячейка, соответствующая той строке, в которой мы находимся.
Так что в этом-то и проблема - что в сочетании с МАКС формула ОСТАТ не генерирует массив, а Excel зачем-то подставляет только одну текущую ячейку. Бред!Serrg
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
