Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
В этом разделе перечислены распространенные причины ошибки #ЗНАЧ! в функциях СУММЕСЛИ и СУММЕСЛИМН и способы их устранения.
Проблема: формула ссылается на ячейки в закрытой книге
Функции СУММЕ ЕСЛИ и СУММЕСС, которые ссылаются на ячейку или диапазон в закрытой книге, приведут к #VALUE! ошибку «#ВЫЧИС!».
Примечание. Это известная проблема с несколькими другими функциями Excel, такими как СЧЁТЕ ЕСЛИ, СЧЁТЕФМН и СЧИТАТЬBLANK. См. функции СУММЕ ЕСЛИ, СЧЁТЕФ и СЧИТАТЬ #VALUE. Ошибка дополнительные сведения.
Решение: Откройте книгу, указанную в формуле, и нажмите клавишу F9, чтобы обновить формулу.
Эту проблему также можно обойти, используя функции СУММ и ЕСЛИ вместе в формуле массива. См. сообщение об ошибке функций СУММЕФ, СЧЁТЕ ЕСЛИ и СЧИТАТЬ СЧИТАТЬ #VALUE! дополнительные сведения.
Проблема: длина строки условия превышает 255 символов
Функции СУММЕСЛИ и СУММЕСЛИМН возвращают неправильные результаты, если они используются для сопоставления строк длиннее 255 символов.
Решение: Сократите строку, если это возможно. Если сократить строку нельзя, используйте функцию СЦЕПИТЬ или оператор-амперсанд (&), чтобы разделить значение на несколько строк. Например:
=СУММЕСЛИ(B2:B12;»длинная строка»&»еще одна длинная строка»)
Проблема: в функции СУММЕСЛИМН аргумент диапазон_условия не совпадает с аргументом диапазон_суммирования.
Аргументы диапазонов в функции СУММЕСЛИМН должны совпадать. Это означает, что в аргументах диапазон_условия и диапазон_суммирования должно указываться одинаковое количество строк и столбцов.
В следующем примере формула должна возвращать сумму ежедневных продаж apples в Bellevue. Однако sum_range (C2:C10) не соответствует количеству строк и столбцов в аргументах criteria_range (A2:A12 & B2:B12). Использование синтаксиса =СУММЕ ЕСЛИМН(C2:C10,A2:A12,A14,B2:B12,B14) приведет к #VALUE! ошибку «#ВЫЧИС!».
Решение: В приведенном примере измените диапазон_суммирования на C2:C12 и выполните формулу повторно.
Примечание: В функции СУММЕСЛИ можно использовать диапазоны разного размера.
У вас есть вопрос об определенной функции?
Задать вопрос на форуме сообщества, посвященном Excel
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Исправление ошибки #ЗНАЧ! #BUSY!
Функция СУММЕСЛИ
Функция СУММЕСЛИМН
Видео о расширенных функциях ЕСЛИ
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Обнаружение ошибок в формулах
Все функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
|
Неправильно считает ф-ция СУММЕСЛИ |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
|
Моя таблица, которую я ежедневно подаю на стол руководителю, перестала подбивать общий итог при помощи формулы «СУММЕСЛИ». Сам отчет состоит из малых таблиц с итогами. Общий итог подсчитываю при помощи: =СУММЕСЛИ(R[-347]C[3]:R[-5]C[3];»итого»;R[-347]C[4]:R[-5]C[4]). Все было отлично, пока в одной из малых таблиц не прибавились еще две строки, в таблице это R319C8 и R320C8. Здесь начинаются проблемы! Я увеличиваю диапазон автосуммы на эти ячейки, и после этого в ячейке R357C4 где применяла формулу «СУММЕСЛИ» пропадает значение. В причине разобраться не могу. Проблема сильно затрудняет работу. На всякий случай уточняю, название фирмы в таблице малой «Русь» долг по накладным. |
|
|
Serge  Пользователь Сообщений: 11312 |
{quote}{login=dokuka}{date=08.12.2010 02:05}{thema=формула «СУММЕСЛИ» — не видит значения}{post}…На всякий случай уточняю, название фирмы в таблице малой «Русь» долг по накладным.{/post}{/quote}И где таблица? |
|
vikttur Пользователь Сообщений: 47199 |
Автор пишет «моя таблица». Может, не хочет делиться? |
|
Serge Пользователь Сообщений: 11312 |
{quote}{login=vikttur}{date=08.12.2010 02:15}{thema=}{post}Автор пишет «моя таблица». Может, не хочет делиться? :){/post}{/quote} |
|
Я её цепляла, сейчас еще раз попробую |
|
|
vikttur Пользователь Сообщений: 47199 |
Вы специально подгоняли под 100 Кб? |
|
В ячейке R68C8 циклическая ссылка. Измените формулу на =СУММ(R[-5]C:R[-1]C) |
|
|
Serge Пользователь Сообщений: 11312 |
{quote}{login=vikttur}{date=08.12.2010 02:41}{thema=}{post}Искать вредную функцию не стал…{/post}{/quote} Но Виктор прав — нафига столько? По теме: Вы протягиваете диапазон суммирования и включаете в него саму ячейку с итоговой формулой. Возникает циклическая ссылка. |
|
{quote}{login=vikttur}{date=08.12.2010 02:41}{thema=}{post}Вы Искать вредную функцию не стал…{/post}{/quote} А я нашел, и теперь она мне разрывает мозг, так же как и неправильная ВПР в одной их тем.:) |
|
|
У меня проблема именно с этой таблицей. Данные которыми я ежедневно пользуюсь. Так зачем мне приводить какой то абстрактный пример, когда я не знаю что с ЭТИМ делать!? |
|
|
Serge Пользователь Сообщений: 11312 |
{quote}{login=dokuka}{date=08.12.2010 02:51}{thema=}{post}У меня проблема именно с этой таблицей. Данные которыми я ежедневно пользуюсь. Так зачем мне приводить какой то абстрактный пример, когда я не знаю что с ЭТИМ делать!?{/post}{/quote}Не абстрактный. В этом файле адо было просто 300 строк из 350 удалить, пометить цветом ячейку с проблемной формулой и показать с какого на какой меняется диапазон (текстом). |
|
{quote}{login=Михаил}{date=08.12.2010 02:48}{thema=}{post}В ячейке R68C8 циклическая ссылка. Измените формулу на =СУММ(R[-5]C:R[-1]C){/post}{/quote} СПАСИБО ОГРОМНОЕ!!! А если не затруднит, не проясните, как туда попала циклическая ссылка и как мне в следующий раз действовать??? |
|
|
{quote}{login=Serge 007}{date=08.12.2010 02:54}{thema=Re: }{post}{quote}{login=dokuka}{date=08.12.2010 02:51}{thema=}{post}У меня проблема именно с этой таблицей. Данные которыми я ежедневно пользуюсь. Так зачем мне приводить какой то абстрактный пример, когда я не знаю что с ЭТИМ делать!?{/post}{/quote}Не абстрактный. В этом файле адо было просто 300 строк из 350 удалить, пометить цветом ячейку с проблемной формулой и показать с какого на какой меняется диапазон (текстом).{/post}{/quote} Спасибо Вам за помощь! |
|
|
По теме: Вы протягиваете диапазон суммирования и включаете в него саму ячейку с итоговой формулой. Возникает циклическая ссылка.{/post}{/quote} Спасибо за помощь! |
|
|
Serge Пользователь Сообщений: 11312 |
{quote}{login=}{date=08.12.2010 03:02}{thema=Re: Re: }{post}{quote}{login=Serge 007}{date=08.12.2010 02:54}{thema=Re: }{post}Вот автор «Михаил» не поучал что надо было сделать, а сразу помог! ЗЫ Вас никто не поучал. Просто на Планете такое отношение ко всем, кто не уважает её и не читает правила перед созданием тем. |
|
Serge Пользователь Сообщений: 11312 |
{quote}{login=}{date=08.12.2010 03:02}{thema=Re: Re: }{post}Вот автор «Михаил» не поучал что надо было сделать, а сразу помог! ЗЫ Вас никто не поучал. Просто на Планете такое отношение ко всем, кто не уважает её и не читает правила перед созданием тем. |
|
Serge Пользователь Сообщений: 11312 |
{quote}{login=}{date=08.12.2010 03:07}{thema=Re: Re: }{post} Я пользуюсь автосуммой, как можно было протянуть диапазон?! {/post}{/quote}Вы не знаете как меняется диапазон автосуммы? |
|
{quote}{login=dokuka}{date=08.12.2010 02:57}{thema=Re: }{post} А если не затруднит, не проясните, как туда попала циклическая ссылка и как мне в следующий раз действовать???{/post}{/quote}Понятия не имею, как туда попала циклическая ссылка. |
|
|
{quote}{login=Serge 007}{date=08.12.2010 03:11}{thema=}{post}{quote}{login=}{date=08.12.2010 03:02}{thema=Re: Re: }{post}Вот автор «Михаил» не поучал что надо было сделать, а сразу помог! ЗЫ Вас никто не поучал. Просто на Планете такое отношение ко всем, кто не уважает её и не читает правила перед созданием тем.{/post}{/quote} Извините, действительно, не внимательно прочитала правила. (Файл-пример, если он более 100К,- обязательно архивируйте! При этом архивированный файл также не должен превышать 100К. Не у всех безлимитный тариф. Совсем необязательно выкладывать файл полностью.) |
|
|
dokuka Гость |
#20 08.12.2010 15:59:00 {quote}{login=Михаил}{date=08.12.2010 03:51}{thema=Re: Re: }{post}{quote}{login=dokuka}{date=08.12.2010 02:57}{thema=Re: }{post} А если не затруднит, не проясните, как туда попала циклическая ссылка и как мне в следующий раз действовать???{/post}{/quote}Понятия не имею, как туда попала циклическая ссылка. ОК!! |
Модератор:Naeel Maqsudov
-
Gamser
- Сообщения:3
- Зарегистрирован:11 окт 2007, 05:42
Вчера нашел ошибку :-))
При некоторых условиях функция СУММЕСЛИ возвращает неверный результат
Условия следующие:
Объявляем колонку текстовым типом
например колонку А
Вносим в колонку значения:
20.1
20.2
20.3
20.01
20.02
20.03
В принципе так записываются счета бухучета, на чем и поймал косяк 
)
В колонку В пишем
1
2
2
4
5
6
В колонке С вносим формулы
=СуммЕсли(A1:A6;»=20.1″;B1:B6)
=СуммЕсли(A1:A6;»=20.2″;B1:B6)
=СуммЕсли(A1:A6;»=20.3″;B1:B6)
=СуммЕсли(A1:A6;»=20.01″;B1:B6)
=СуммЕсли(A1:A6;»=20.02″;B1:B6)
=СуммЕсли(A1:A6;»=20.03″;B1:B6)
И наслаждаемся результатом
Бред во ВСЕХ версиях EXCEL!!!!
Микрософту написал письмо и позвонил 
Обещали исправить в следующем выпуске заплатки
на просьбу о подарке — посмеялись :-)))
Сергей Гамзиков
-
Pavel55
- Сообщения:405
- Зарегистрирован:20 окт 2006, 11:40
- Откуда:Moscow
11 окт 2007, 18:48
Получается, что значения 20.1 и 20.01 (20.2 и 20.02; 20.3 и 20.03) — для функции СУММЕСЛИ одинаковы и она их суммирует). А вот 20.1 и 20.001 уже разные )
-
Avsha
- Сообщения:664
- Зарегистрирован:08 сен 2005, 13:47
- Откуда:KZ
11 окт 2007, 23:58
поставил запятые вместо точек в данных столбца A и в формулах столбца С.
нормально считает …
-
Avsha
- Сообщения:664
- Зарегистрирован:08 сен 2005, 13:47
- Откуда:KZ
12 окт 2007, 00:06
Ser Artur
в 2003
=SUMIF(A2:A4,20.1,B2:B4)
очень даже работает
У меня тоже работает ваш вариант, правда необходимо в «Языках и Стандартах» в качестве разделителя целой и дробной части установить «.» — точку.
-
Aent
- Сообщения:1108
- Зарегистрирован:01 окт 2006, 14:52
- Откуда:Saratov,Russia
- Контактная информация:
12 окт 2007, 00:16
Ну в общем понятно откуда у этой ситуации ноги растут.
Смотрим HELP по СУММЕСЛИ — видим:
————————————————————————————————————————————————
Синтаксис
СУММЕСЛИ(диапазон;условия;диапазон_суммирования)
Диапазон — диапазон ячеек, оцениваемый по условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки, содержащие числа. Пустые ячейки и ячейки, содержащие текстовые значения, не учитываются.
———————————————————————————————————————————————-
Т.е. Excel пытается перевести строку в диапазоне критерия выборки в число ….
А ЗНАЧЕН(«20.1») = ЗНАЧЕН(«20.01») = ЗНАЧЕН(«20.янв») = ЗНАЧЕН(«20.01.2007») = 39102
при десятичном разделителе — запятой
Так как строка распознаётся как дата а дата потом конвертируется в число …
Так что это не баг а системная функция
Большое спасибо Gamser, за интересный вопрос.
Отмечу, что в CALC (OpenOffice 2.3) подобная проблема не возникает …
-
Avsha
- Сообщения:664
- Зарегистрирован:08 сен 2005, 13:47
- Откуда:KZ
12 окт 2007, 10:46
Aent,
добавлю в ваш ряд 39102-ых еще пару значений:
=ЗНАЧЕН(«20/01»)
=ЗНАЧЕН(«20/1»)
Дата ведь и так вводится в Excel, через /
А когда в «Языках и Стандартах» в качестве разделителя целой и дробной части установлена «.» — точка, то наши равенства уже принимают другой вид…
ЗНАЧЕН(«20.1») = 20.1
ЗНАЧЕН(«20.01») =20.01
-
Gamser
- Сообщения:3
- Зарегистрирован:11 окт 2007, 05:42
15 окт 2007, 18:08
Весь писк в том, что меня не устраивали ни числовые значения, ни значения типа дата, нужно было обработать именно текст «20.1», «20.01»- выгружал данные из 1С в ЕХЕЛ. За что и поплатился 
-
Aent
- Сообщения:1108
- Зарегистрирован:01 окт 2006, 14:52
- Откуда:Saratov,Russia
- Контактная информация:
16 окт 2007, 18:09
Gamser, для строк нужно просто было использовать в колонке С не СУММЕСЛИ а массивную формулу типа
={СУММ(ЕСЛИ(A1:A6=»20.1″;1;0)*B1:B6)}
На всякий случай напомню, что для её ввода нужно одновременно нажимать Ctrl+Shift+Enter
Есть цифровая кодировка финансовых позиций, но список этой кодировки стоит с форматом ТЕКСТ
В случае если код типа
2.07 — позиция второго уровня вложения
или
2.01.03.01 — позиция четвертого уровня вложения
то СУММЕСЛИ или СУММЕСЛИМН считает так как и положено — находит искомое значение и суммирует если таких значений несколько.
Но если искомый код такого типа
2.04.13 — позиция третьего уровня вложения
то, несмотря на то, что формат стоит текстовый, эксель насколько «умный», что видимо все равно считает это как дату и подсчет суммы вообще неадекватный.
Как можно обойти эту проблему, если учесть, что кодировку поменять — это не вариант.
Заранее спасибо за помощь.