Суммеслимн ошибка знач

В этом разделе перечислены распространенные причины ошибки #ЗНАЧ! в функциях СУММЕСЛИ и СУММЕСЛИМН и способы их устранения.

Проблема: формула ссылается на ячейки в закрытой книге

Функции СУММЕ ЕСЛИ и СУММЕСС, которые ссылаются на ячейку или диапазон в закрытой книге, приведут к #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 (по категориям)

При работе в Excel вы можете столкнуться с ошибкой #ЗНАЧ. В этой статье мы разберемся с несколькими ситуациями, которые приводят к этой ошибке и как ее исправить.

 

Ситуация 1. Формула СУММЕСЛИ на другой файл

Представим себе обычную ситуацию, когда у нас есть файл с базой данных, в которой представлены продажи за месяц и нам нужно в другом файле рассчитать сумму продаж за каждый месяц.

Когда оба файла открыты, то все работает отлично, но как только вы закроете их оба и откроете только файл, в котором вы суммировали итоги продаж, то получите ошибку #ЗНАЧ. Это связано с особенностями работы Excel, которые надо знать, и вообще есть несколько формул, которые приведут к ошибке #ЗНАЧ при ссылке на внешний файл:

• СУММЕСЛИ()
• СУММЕСЛИМН()
• СЧЁТЕСЛИ()
• СЧЁТЕСЛИМН()
• СЧИТАТЬПУСТОТЫ()
• СМЕЩ()
• ДВССЫЛ()

Какие есть пути решения ситуации? Первый: перенести базу данных в файл, где формируются итоги, тогда ссылок на внешние книги не будет и ошибка пропадет. Второй: Использовать формулы СУММ() и ЕСЛИ() совместно в массиве. Это несколько сложно для новичков, поэтому на этом способе останавливаться подробно не буду. Третий: Использовать формулу ЕСЛИОШИБКА и прописать в случае ошибки напоминание, что необходимо открыть файл с базой данных для корректной работы. Четвертый: Просто открыть файл, на который ссылается формула и ошибка пропадет

 

Ситуация 2. Вычитание/сложение дат

Ошибка #ЗНАЧ может появиться при вычитании/сложении дат. Это связано с тем, что формат одной из ячеек участвующих в формуле отличен от типа Дата. Обычно есть лишние пробелы, из-за которых Excel считает что в ячейке не дата, а текст и соответственно из даты текст вычесть/сложить невозможно.

 Решение проблемы следующее: Выделите ячейки с датами и замените в них пробелы на «ничего». После этого ошибка исчезнет.

 

Ситуация 3. Лишние пробелы в числах

При копировании данных из различных источников, может сложиться ситуация, что в числах будут лишние пробелы, из-за них арифметические операции с числами будут невозможны и приведут к ошибке #ЗНАЧ. Решение проблемы, как и в ситуации 2 — замените пробелы на «ничего» и проблема пропадет.

Есть еще несколько ситуаций, которые могут привести к рассматриваемой ошибке, но они настолько редки, что я рассматривать их в этой статье не буду.

Спасибо за внимание.

Примеры функции «СУММЕСЛИМН» в Excel.

​Смотрите также​​ у меня на​ ​ т.д. условий без​​ и третью пару​Жмем​ необходимо просуммировать услуги.​ формате. Текст необходимо​ суммирования, а несколько,​Нажимаем «ОК». Формула получилась​​находится на закладке​​8677​ нашем примере диапазон_суммирования​ C).​диапазон условия (A2:A12 и​ статье «»СУММПРОИЗВ» в​
​ один и тот​
​ напишем такое условие​Рассмотрим​ 3000-4000 строк. Можно​ каких-либо ограничений.​ (​ОК​ Допустим, «Кемерово». Условие​
​ заключать в кавычки.​
​ то применяем функцию​ такая. =СУММЕСЛИ(D2:D7;B10;C2:C7)​ «Формулы» в разделе​Южный​
​ — это диапазон​Вот формулу можно использовать​ B2:B12)​ Excel».​ же диапазон дат,​ функции. «*я*»​примеры функции «СУММЕСЛИМН» в​ ли как-то задать​
​В категории​
​Диапазон_условия3​
​ и вводим ее аргументы:​ 1 – ссылка​Возвращается неверный результат суммирования.​ «СУММЕСЛИМН». Про эту​
​Третий пример.​
​ «Библиотека функций». Нажимаем​Егоров​ D2:D11, представляющий собой​ для acomplish это:​. Использование синтаксиса =СУММЕСЛИМН(​В этом разделе перечислены​ но указали разные​
​Формула будет такой.​Excel​ соответствующие значения пустым​Базы данных (Database)​​-​Диапазон​​ на ячейку с​​ Если в диапазоне​​ функции читайте в​​Сумма ячеек​ на «Математические» и​Мясо​ столбец с числами,​
​=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»)​C2:C10​ распространенные причины ошибки​ условия (от даты​ =СУММЕСЛИМН(D2:D8;C2:C8;»*я*»)​. Как написать условия​ ячейкам? Чтобы например,с2​ можно найти функцию​Условие3​
​- это те​ названием города (С3).​ для нахождения суммы​ статье «Суммирование ячеек​
​Excel с условием.​ выбираем функцию «СУММЕСЛИ».​
​450​ которые вы хотите​Результатом является значение 14,719.​;​
​ #ЗНАЧ! в функциях​
​ от той, что​Если нужно, то​ для различных способов​ и с3 =с1,​БДСУММ (DSUM)​​), и четвертую, и​ ячейки, которые мы​Для учета вида услуг​ ячейки содержат значение​ в Excel по​Посчитаем сумму продаж​Появится диалоговое окно​Южный​ просуммировать; диапазон_условия1 —​
​Рассмотрим изучить все части​A2:A12​ СУММЕСЛИ и СУММЕСЛИМН​ в ячейке С11​ добавляем условия в​ выбора данных для​ с5 и с6=с4​, которая тоже способна​ т.д. — при​
​ проверяем на выполнение​ задаем второй диапазон​
​ ИСТИНА, то функция​ условию» тут.​ сметаны по всем​ «Аргументы функции». Заполняем​Егоров​ это диапазон A2.A11,​ формулы.​;A14;​ и способы их​ и больше, до​
​ функцию СУММЕСЛИМН по​
​ суммирования, в т.​ ​ и тд?​​ решить нашу задачу.​
​ необходимости.​Критерия​ условий – столбец​ возвращает результат 1.​Какие ещё, есть​
​ отделам за все​ его так.​Мясо​ представляющий собой столбец​= СУММЕСЛИМН является формулой​B2:B12​
​ устранения.​ даты, что в​ менеджерам и покупателям.​
​ ч. с датами.​ikki​ Нюанс состоит в​Если же у вас​. В нашем случае​
​ «Услуга» (D2:D11). Условие​ ЛОЖЬ – 0.​ способы суммирования в​ дни. В ячейке​​В строке «Диапазон»​
​7673​ с данными, в​ арифметического. Он вычисляет​;B14) приведет к ошибке​Если функция СУММЕСЛИ или​ ячейке С12 и​Пример 3.​ Как установить​:​ том, что для​ пока еще старая​ — это диапазон​
​ 2 – это​
​Функция работает некорректно, если​ Excel, не обязательно​ В11 будем писать​ указываем диапазон первого​​Восточный​ котором находится​ числа, которые находятся​ #ЗНАЧ!.​
​ СУММЕСЛИМН ссылается на​
​ меньше).​Нам нужно посчитать​функцию «СУММЕСЛИМН» в Excel​доп​ работы этой функции​
​ версия Excel 2003,​
​ с фамилиями менеджеров​ ссылка на определенную​ количество строк и​
​ формулами, смотрите в​
​ название нужного продукта.​ столбца, с номерами​Песоцкий​условие1​ в этом случае​
​В функции СУММЕСЛИМН​
​ ячейку или диапазон​
​Получится так.​ сумму продаж всех​, какие можно указать​олнительный​​ необходимо создать на​ но задачу с​ продаж.​
​ услугу. В частности,​
​ столбцов в диапазонах​ статье «Суммирование в​ Сейчас мы написали​ отделов, п. ч.​Сельхозпродукты​— “Южный”.​ в столбце D.​ аргумент диапазон_условия должен​ в закрытой книге,​Поменяем в формуле фамилию​ фруктов, кроме яблок.​ условия, смотрите в​
​столбец​ листе специальный диапазон​ несколькими условиями решить​Критерий​ услугу 2 (D5).​ для проверки условий​ Excel».​ «сметана». Получилось так.​
​ мы хотим посчитать​664​По мере того, как​ Первым делом нужно​ совпадать с аргументом​ это приводит к​ менеджера «Сергеева» на​ В условии функции​ статье «Суммирование ячеек​в файле -​ критериев — ячейки, содержащие​

excel-office.ru

Как исправить #VALUE! сообщение об ошибке в функции СУММЕСЛИ и СУММЕСЛИМН

​ нужно, то придется​- это то,​Вот так выглядит формула​ не совпадает с​При написании формулы,​Эту функцию можно​

Проблема: формула ссылается на ячейки в закрытой книге

​ данные только по​Северный​ вы вводите формулу,​ указать расположение номера:​ диапазон_суммирования​ ошибке #ЗНАЧ!.​

​ «Васечкин». Проверяем формулу.​​ напишем так. “<>яблоки”​ в Excel по​ с зеленой заливкой.​ условия отбора — и​ извращаться — см.​ что мы ищем​ с двумя условиями​ числом строк и​ мы можем допустить​ использовать для заполнения​

​ первому отделу. Мы​​Егоров​ в автозавершении формулы​=СУММЕСЛИМН(D2:D11;​Решение:​

​Примечание.​ Получилось так.​Но, в нашей таблице​ условию».​пс. у меня​ указать затем этот​ следующие способы.​ в предыдущем указанном​ для суммирования: =СУММЕСЛИМН(E2:E11;C2:C11;C3;D2:D11;D5).​

Проблема: длина строки условия превышает 255 символов

​ столбцов в диапазоне​ ошибку (по незнанию​ бланка, выбрав данные​ указали столбец A.​Сельхозпродукты​

​ появятся остальные аргументы​​Другими словами нужно ввести​В приведенном примере​Это известная проблема​Формула работает. Можно менять​ есть разные наименования​Пример 1.​ Ex’2003, поэтому вашей​ диапазон функции как​

​Добавим к нашей таблице​ диапазоне. Разрешается использовать​

Проблема: в функции СУММЕСЛИМН аргумент диапазон_условия не совпадает с аргументом диапазон_суммирования.

​Результат расчета – 68.​ для суммирования.​ или опечатка, т.д.).​ из большой таблицы.​​В строке «Критерий»​​1500​​ (здесь они не​​ формулу для суммирования​ измените​ с рядом других​

​ даты периода в​ – яблоки зеленые,​Посчитаем сумму, если​ формулы там нет.​ аргумент:​​ еще один столбец,​​ символы * (звездочка)​Гораздо удобнее для данного​Бонусы при использовании функции​​ Какие бывают ошибки,​ Смотрите статью «Как​​ указываем, какой отдел​​Южный​​ показаны);​​ чисел в столбце,​​диапазон_суммирования​​ функций Excel, в​​ ячейках С11 и​ красные яблоки. Чтобы​

​ в ячейке наименования​maslow​=БДСУММ(A1:D26;D1;F1:G2)​ который будет служить​

​ и  ? (вопросительный​​ примера сделать выпадающий​ СУММЕСЛИМН:​​ что они означают,​​ посчитать в Excel​ из столбца А​

​Маринова​​диапазон_условия2​ если они удовлетворяют​на C2:C12 и​

У вас есть вопрос об определенной функции?

​ том числе СЧЁТЕСЛИ,​ С12, т.д.​

Помогите нам улучшить Excel

​ и их исключить​ товара написаны не​: Очень интересное решение,​maslow​ своеобразным индикатором: если​ знак) как маски​ список для городов:​

support.office.com

Суммирование значений с учетом нескольких условий

​Возможность применения подстановочных знаков​​ как их найти​ ячейки в определенных​ нам нужно посчитать.​Мясо​— это диапазон​ условиям. Что диапазон​ выполните формулу повторно.​ СЧЁТЕСЛИМН и СЧИТАТЬПУСТОТЫ.​Пример 5.​ из суммы всех​ одним словом, а​ но есть одно​: Всем добрый вечер.​ заказ был в​ или символы подстановки.​Теперь можно посмотреть, сколько​ при задании аргументов.​ и исправить? Чтобы​ строках».​ Здесь можно указать​6596​

​ C2:C11, представляющий собой​ ячеек — это​Примечание:​ Дополнительные сведения см.​Можно в условиях​ фруктов, условие в​ несколькими. Здесь нужно​ «но»- что если​ Нужна ваша помощь.​

​ «Копейку» и от​ Звездочка подменяет собой​ услуг 2 оказано​ Что позволяет пользователю​ ответить на эти​​Какими способами можно​​ не только номер​​Формула​​ столбец с данными,​ первый​  В функции СУММЕСЛИ​

​ в статье Функции​ функции «СУММЕСЛИМН» указать​

​ функции напишем такое.​

​ вставить в условие​

​ данные в объединённой​Проблема в следующем:​

​ Григорьева, то в​ любое количество любых​ в том или​ находить сходные, но​ вопросы, читайте статью​ сделать самостоятельно любой​ отдела (например, 1),​

​Описание​

​ в котором находится​аргумент​ можно использовать диапазоны​ СУММЕСЛИ, СЧЁТЕСЛИ и​ и символы «>»​ «<>*яблоки*»​ функции специальные символы.​​ из 3 ячеек​​ попытался составить конечный​ ячейке этого столбца​ символов, вопросительный знак​ ином городе (а​ не совпадающие значения.​

​ «Ошибки в формулах​ бланк в Excel,​ но и текст,​Результат​условие2​в этой формуле​ разного размера.​ СЧИТАТЬПУСТОТЫ возвращают ошибку​ и « Получится​Символы «<>» означают​Таблица такая.​ находятся не в​

​ отчёт с помощью​

​ будет значение 1,​ — один любой​ не только в​

​Можно использовать операторы отношения​ Excel».​ смотрите в статье​ символы, адрес отдельной​’ = СУММЕСЛИМН (a2:​— “Мясо”.​ — первая часть​Задать вопрос на форуме​ #ЗНАЧ!.​ такая формула.​​ – не равно.​​Нам нужно посчитать, сколько​ первой, а во​ функции Суммеслимн, но​

​ иначе — 0.​

​ символ. Так, например,​ Кемерово). Формулу немного​ (, = и​В версиях Excel 2007​ «Как сделать бланк​ ячейки с номером​​ a11; D2: D11​​Если вы нажмете​ данных, функция требует​ сообщества, посвященном Excel​Решение:​

​=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;E2:E8;»>500″)​ О других символах,​ менеджер Васечкин продал​ второй или третьей​ один из критериев​ Формула, которую надо​ чтобы найти все​ видоизменим: =СУММЕСЛИМН($E$2:$E$11;$C$2:$C$11;F$2;$D$2:$D$11;$D$5).​ др.).​ и выше работает​ в Excel» здесь.​ отдела, код, т.​«Южно» C2: C11,​СУММЕСЛИМН​​ в качестве входных​​У вас есть предложения​

​Откройте книгу, указанную​Здесь последний критерий​ которые можно вставить​ яблок красных, зеленых,​ строке?​ не хочет учитываться,​​ ввести в этот​​ продажи у менеджеров​Все диапазоны для суммирования​​ функция СУММЕСЛИМН, которая​​Четвертый пример.​​ д. Какие символы​

​ «Мясо»)​​в автозавершении формулы,​​ данных.​ по улучшению следующей​

Попробуйте попрактиковаться

​ в формуле, и​ написали так –​ в формулу и​ других покупателю Иванову.​Serge 007​

​ так как находится​ столбец очень простая:​ с фамилией из​ и проверки условий​У нас есть таблица​ позволяет при нахождении​Сумма​ можно использовать в​Суммирует продаж мяса в​

​ откроется статья, справкой.​После этого необходимо найти​ версии Excel? Если​ нажмите клавишу F9,​ «>500».​ что они означают,​В ячейку Н4​: Тогда уж лучше​

​ с объединённой ячейке​	​=(A2=»Копейка»)*(B2=»Григорьев»)​	​ пяти букв, можно​	​ нужно закрепить (кнопка​
​ с данными об​	​ суммы учитывать сразу​	​Excel, если несколько условий.​	​ формуле, что они​
​Столбец C в​	​Если вы хотите поэкспериментировать​	​ данные, которые соответствуют​	​ да, ознакомьтесь с​
​ чтобы обновить формулу.​	​Пример 6.​	​ смотрите в статье​	​ вставляем функцию «СУММЕСЛИМН».​
​ объединение убрать​	​ и соответственно не​	​Логические равенства в скобках​	​ использовать критерий​
​ F4). Условие 1​	​ оказанных услугах клиентам​	​ несколько значений. В​	​Если нужно посчитать​
​ означают, смотрите в​	​ южном​	​ с функцией СУММЕСЛИМН,​	​ двух условий, поэтому​
​ темами на портале​	​Также можно обойти эту​	​Если посчитать в​	​ «Символы в формулах​
​ В условии функции​	​Работает. Только надо​	​ соответствует диапазону суммирования​	​ дают значения ИСТИНА​
​?????​	​ – название города​	​ из разных городов​	​ самом названии функции​
​ сумму по двум​	​ статье «Символы в​	​область в столбце​	​ то некоторые образцы​
​ введите свой первый​		​ пользовательских предложений для​	​ проблему, используя функции​
​ непустых ячейках, то​ Excel».​ ​ напишем «*яблоки*». По​ было и второй​		​ и второму критерию.​ ​ или ЛОЖЬ, что​. А чтобы найти все​ ​ – ссылка на​ с номерами договоров.​ заложено ее назначение:​	​ и более столбцам,​ ​ формулах Excel» тут.​ A (результат —​

​ данных и формулы,​​ условие, указав для​

• ​ Excel.​ СУММ и ЕСЛИ​

• ​ ставим последний критерий»​Формула будет такая.​ этому условию Excel​ диапазон смещать:​ Например, нужно суммировать​ для Excel равносильно​

support.office.com

Функция «СУММЕСЛИ» в Excel.

​ продажи менеджеров, у​ первую ячейку выпадающего​​Предположим, нам необходимо подсчитать​ ​ сумма данных, если​​ то можно в​В строке «Диапазон​ 14 719).​ использующей функцию.​ функции расположение данных​Примечание:​
​ вместе в формуле​​ — «>0». Формула​ ​ =СУММЕСЛИМН(D2:D8;C2:C8;»<>*яблоки*»)​
​ будет искать все​Код =СУММПРОИЗВ(((A5:A16=»b»)*(C5:C16=»ag1″))*B5:B16+((A6:A17=»b»)*(C5:C16=»ag1″))*B6:B17+((A7:A18=»b»)*(C5:C16=»ag1″))*B7:B18) Такого​
​ все значения в​
​ 1 и 0.​ ​ которых фамилия начинается​
​ списка. Ссылку на​ количество услуг в​ совпадает множество условий.​ формулу вставить несколько​ суммирования» мы указываем​= SUMIFS(D2:D11,A2:A11,​Вы можете работать с​
​ (a2: a11), а​ ​Мы стараемся как​​ массива. Дополнительные сведения​ получится такая.​Если нужно посчитать​ яблоки. Диалоговое окно​ быть в Excel​
​ столбце B с​ Таким образом, поскольку​ на букву «П»,​
​ условие 2 тоже​ определенном городе с​Аргументы функции СУММЕСЛИМН:​ функций «СУММЕСЛИ». Например,​ диапазон столбца с​«Южно» C2: C11,​ демонстрационными данными непосредственно​ также какие условием​
​ можно оперативнее обеспечивать​ см. в статье​=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;E2:E8;»>0″)​ не сумму в​ функции заполнили так.​ не может. Данные​ двумя критериями: «b»​ мы перемножаем эти​ а заканчивается на​ делаем постоянной. Для​ учетом вида услуги.​Диапазон ячеек для нахождения​ в нашу таблицу​ данными, которые нужно​ «Мясо»)​ в этой книге​ является — наилучшим​
​ вас актуальными справочными​ Функции СУММЕСЛИ, СЧЁТЕСЛИ​Пример 7.​ выбранных ячейках, а​Формула получится такая.​ в объединённых ячейках​ в столбце А​
​ выражения, единица в​ «В» — критерий​
​ проверки из списка​Как использовать функцию СУММЕСЛИМН​
​ суммы. Обязательный аргумент,​ из примера добавим​ посчитать. У нас​Примечания:​ веб-приложения Excel Online.​ образом «Южный». Обратите​ материалами на вашем​ и СЧИТАТЬПУСТОТЫ возвращают​Нужно посчитать продажи​ количество ячеек по​=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;A2:A8;»Иванов»;C2:C8;»*яблоки*»)​
​ всегда хранятся в​
​ и «ag1» с​
​ конечном счете получится​ ​П*В​
​ городов выберем «Кемерово»:​ в Excel:​ где указаны данные​ столбец В с​ – это столбец​ ​ Изменяйте значения и​ внимание, запятые между​ языке. Эта страница​ ошибку #ЗНАЧ!.​ Васечкиным по всем​
​ определенному условию, то​Получилось, что всяких​
​ левой верхней ячейке​ столбце С. Проблема​​ только если оба​. Строчные и прописные​
​Скачать примеры с использованием​
​Вызываем «Мастер функций». В​ ​ для суммирования.​
​ новыми данными. Формулу​ «Сумма».​Дополнительные примеры можно найти​ формулы или добавляйте​ отдельные аргументы:​ переведена автоматически, поэтому​Функции СУММЕСЛИ и СУММЕСЛИМН​ филиалам конкретного магазина.​
​ воспользуйтесь функцией «СЧЕТЕСЛИ».​ яблок Васечкин продал​ объединяемого диапазона.​ со столбцом С​ условия выполняются. Теперь​ буквы не различаются.​ функции СУММЕСЛИМН в​ категории «Математические» находим​
​Диапазон ячеек для проверки​ в ячейке напишем​Заполнили диалоговое окно​ в статьеФункция СУММЕСЛИМН.​ свои собственные, чтобы​= SUMIFS(D2:D11,A2:A11,»South»,​
​ ее текст может​
​ возвращают неправильные результаты,​ ​ Таблица такая.​
​ Подробнее об этой​ Иванову на сумму​ЗЫ Есть одно​ там все ячейки​ стоимости продаж осталось​Диапазон_суммирования​ Excel.​ СУММЕСЛИМН. Можно поставить​ условия 1. Обязательный​ такую. =СУММЕСЛИ(A2:A7;B10;C2:C7)+СУММЕСЛИ(A2:A7;B10;D2:D7)​ так.​Если вы хотите подсчитать​
​ увидеть, как мгновенно​Кавычки вокруг слова «Южный»​ содержать неточности и​ если они используются​В условиях функции напишем​
​ функции читайте в​ ​ 7897 рублей.​​ исключение, но вероятность​ объедены, то есть​ умножить на значения​- это те​Результат тот же –​ в ячейке знак​ аргумент, к которому​
​Этой формулой мы​Нажимаем «ОК». Получилась такая​ итоговое значение только​ изменятся результаты.​ укажите, что это​ грамматические ошибки. Для​ для сопоставления строк​
​ так — «*Монетка*».​ статье «Функция «СЧЕТЕСЛИ»​Пример 2.​ что Вы в​ ячейке С1 соответствует​ получившегося столбца и​ ячейки, значения которых​ 68.​ «равно» и начать​
​ применяется заданное условие​ считаем выборочные данные​ формула. =СУММЕСЛИ(A2:A7;1;C2:C7)​ для одного диапазона​Скопируйте все ячейки из​ текстовые данные.​ нас важно, чтобы​ длиннее 255 символов.​ Формула получится такая.​
​ в Excel».​Нужно посчитать, сколько​ свой жизни с​ ячейки А1, А2​ просуммировать отобранное в​ мы хотим сложить,​
​По такому же принципу​ вводить название функции.​ поиска. Найденные в​ из столбцов C​Мы рассмотрели этот​ с учетом значения​ приведенной ниже таблицы​Наконец, введите аргументы для​ эта статья была​Решение:​=СУММЕСЛИМН(D2:D8;B2:B8;»Васечкин»;A2:A8;»*Монетка*»)​Пример 4.​

excel-office.ru

Использование функции СУММЕСЛИМН в Excel ее особенности примеры

​ продано всех фруктов​ ним столкнётесь, намного​ и А3 и​ зеленой ячейке:​ т.е. нашем случае -​ можно сделать выпадающий​ Excel покажет список​ этом массиве данные​ и D по​ вариант установки формулы​

Синтаксис СУММЕСЛИМН и распространенные ошибки

​ в другом диапазоне,​

1. ​ и вставьте их​ второго условия –​ вам полезна. Просим​Сократите строку, если​
2. ​Последним условием мы​Функция «СУММЕСЛИМН» в​ на букву «Я».​ меньше чем вероятность​ если критерий «b»​Если вы раньше не​ стоимости заказов.​ список для услуг.​ функций, которые имеют​
3. ​ суммируются в пределах​ первому отделу.​ в ячейку, чтобы​ используйте функцию СУММЕСЛИМН.​ в ячейку A1​ диапазон ячеек (C2:​ вас уделить пару​ это возможно. Если​ написали «*Монетка*». Слово​Excel с датами​
4. ​ В условиях функции​ встречи с динозавром​ находится в А2​ сталкивались с такой​Если условий больше одного​Имеем таблицу по продажам,​ в названии такое​ диапазона для суммирования​Функцией «СУММЕСЛИ» в​

​ понять принцип работы​Рассмотрим одну из​

1. ​ нового листа Excel.​ C11), содержащий слово​ секунд и сообщить,​ сократить строку нельзя,​ «Монетка» заключили в​.​
2. ​ «СУММЕСЛИМН» напишем так.​Легко. Выделяете диапазон​ или А3 то​ замечательной возможностью Excel​ (например, нужно найти​ например, следующего вида:​ начало. Выбираем необходимую​
3. ​ (первого аргумента).​Excel​ функции «СУММЕСЛИ». Когда​ популярных​ Вы можете отрегулировать​ «Мясо», плюс само​ помогла ли она​ используйте функцию СЦЕПИТЬ​

​ звездочки. Это значит,​Нужно сложить данные​

• ​ «=я*» Вся формула​ в котором есть​ функция их не​ как формулы массива,​ сумму всех заказов​
• ​Задача​ двойным щелчком мыши​Условие 1. Обязательный аргумент,​

​можно посчитать, только,​

Примеры функции СУММЕСЛИМН в Excel

​ поймем принцип составления​функций в​ ширину столбцов, чтобы​ слово (заключено в​ вам, с помощью​

​ или оператор-амперсанд (&),​ что Excel будет​ из таблицы по​ будет такая. =СУММЕСЛИМН(D2:D8;C2:C8;»=я*»)​

​ пустые строки, нажимаете​ «видит».​

1. ​ то советую почитать​ Григорьева для «Копейки»),​: просуммировать все заказы,​ или просто смещаем​ составляющий пару предыдущему.​ отрицательные или, только,​ формулы с функцией​Excel «СУММЕСЛИ»​ формулы лучше отображались.​ кавычки) таким образом,​ кнопок внизу страницы.​ чтобы разделить значение​ искать все слова​ нескольким условиям, в​Этой формулой посчитаются​ клавишу F5, Выделить​
2. ​Надеюсь смог подробно​ предварительно про них​ то функция​ которые менеджер Григорьев реализовал​ курсор стрелкой на​ Критерий, по которому​ положительные числа. Об​ «СУММЕСЛИ», можно формулу​. Эта функция выбирает​
3. ​Регион​ Excel может соответствовать​ Для удобства также​ на несколько строк.​ «Монетка» с разными​ т. ч. по​ все слова в​ — Пустые ячейки​ объяснить проблему)​ много хорошего здесь.​СУММЕСЛИ (SUMIF)​ для магазина «Копейка».​ клавиатуре вниз по​ определяются ячейки для​ этом способе, читайте​
4. ​ написать, сразу, в​ и складывает данные​Продавец​ его. Завершить формулу​ приводим ссылку на​ Например:​ номерами и буквами,​ датам.​
5. ​ столбце, которые начинаются​ — ОК, НЕ​Serge 007​

​ Ну, а в​

​не поможет, т.к.​Если бы в нашей​ списку и жмем​

​ суммирования в диапазоне​ в статье «Сумма​ ячейке, не вызывая​ из таблицы по​Что следует ввести​ с закрывающую скобку​ оригинал (на английском​

​=СУММЕСЛИ(B2:B12;»длинная строка»&»еще одна длинная​ разные филиалы магазина​Удобно писать даты​ на букву «Я».​ снимая выделения нажимаете​: На скорую руку:​ нашем случае задача​ не умеет проверять​ задаче было только​ клавишу TAB.​ условия 1. Условие​ отрицательных чисел в​

​ окно функции.​ определенным условиям, которые​Продажи​

​)​ языке) .​

​ строка»)​ «Монетка». Смотрите статью​ в отдельных ячейках.​

exceltable.com

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

​Если нужно посчитать, что​ знак «=» (равно,​

​Код =СУММПРОИЗВ(((A5:A16=»b»)*(C5:C16=»ag1″)+(A6:A17=»b»)*(C5:C16=»ag1″)+(A7:A18=»b»)*(C5:C16=»ag1″))*B5:B16)​​ решается одной формулой:​ больше одного критерия.​ одно условие (все​

Способ 1. Функция СУММЕСЛИ, когда одно условие

​В нашем примере диапазон​ может иметь числовой​ Excel».​Получились такие данные.​ мы пишем в​Южный​и нажмите клавишу​Допустим, вам нужно суммировать​Аргументы диапазонов в функции​ «Подстановочные знаки в​​ Так сможем посчитать​​ продал​​ без кавычек), стрелка​​maslow​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​ Поэтому начиная с​​ заказы Петрова или​​ суммирования – это​ формат, текстовый; «воспринимает»​​Чтобы сложить и​​Второй пример.​

​ формуле.​​Орехов​​ ВВОД. Результат, снова​

• ​ значения с более​​ СУММЕСЛИМН должны совпадать.​ Excel» тут.​ данные любого периода,​​именно​​ вверх один раз​: К сожалению формула​После ввода этой формулы​ версии Excel 2007​
• ​ все заказы в​​ диапазон ячеек с​ математические операторы. Например,​ удалить дубли в​Пример функции «СУММЕСЛИ» в​Разберем​Напитки​ нажмите — 14,719.​ чем одного условия,​ Это означает, что​Есть ещё одна​ изменив даты в​Васечкин и​ или мышкой выделите​ не работает и​ необходимо нажать не​ в набор функций​ «Копейку», например), то​ количеством оказанных услуг.​​ 45; «​​ таблице, тоже применяем​Excel по дате.​примеры функции «СУММЕСЛИ» в​3571​=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»)​ например сумму продаж​​ в аргументах​​ функция в Excel,​ этих отдельных ячейках.​
• ​именно​​ самую первую сверху​ суммирует ячейки соответствующие​ Enter, как обычно,​ была добавлена функция​ задача решалась бы​

Способ 2. Функция СУММЕСЛИМН, когда условий много

​ В качестве первого​Диапазон ячеек для проверки​ функцию «СУММЕСЛИ». Смотрите​Теперь посчитаем сумму​Excel».​​Западный​​По мере ввода функции​ товара в определенной​диапазон_условия​ которая считает по​В ячейке С11​Иванову, то добавим​ ячейку — Ctrl+Enter​​ первой из трёх​​ а​СУММЕСЛИМН (SUMIFS)​ достаточно легко при​ аргумента выбираем столбец​ условия 2; условие​ статью «Как сложить​​ за определенные даты.​​У нас такая​Егоров​ СУММЕСЛИМН в Microsoft​

​ области. Это хороший​и​ условию. Эта функция​ напишем дату начала​ эти условия в​​maslow​​ строчек, то есть​​Ctrl + Shift +​​- в ней​ помощи встроенной функции​ «Количество» (Е2:Е11). Название​

​ 2; … необязательные​ и удалить дубли​ Можно в формуле​ таблица.​Молочные продукты​ Excel, если вы​ обращения для использования​диапазон_суммирования​

Способ 3. Столбец-индикатор

​ проще. Читайте о​ периода.​ функцию, как рассмотрели​: То что надо!)​ критерию «а»​ Enter​ количество условий проверки​ Excel​ столбца не нужно​ аргументы для назначения​ в Excel».​ указать эту дату.​Первый пример.​

​3338​

​ не помните аргументы​ функции СУММЕСЛИМН в​должно указываться одинаковое​ ней в статье​В ячейке С12​ это выше.​ Спасибо огромное Serge​ikki​- тогда Excel​ увеличено аж до​СУММЕСЛИ (SUMIF)​ включать.​ дополнительных диапазонов и​Ещё примеры этой​ А можно в​Сумма по условию​

Способ 4. Волшебная формула массива

​Восточный​ справки готов под​ формуле.​ количество строк и​ «Функция «СУММЕСЛИ» в​ напишем дату окончания​Но, у нас​ 007 и Ikki_cf​: допстолбец? и не​ воспримет ее как​

​ 127! Функция находится​

​ из категории​Первое условие, которое нужно​ условий для них.​ функции смотрите в​​ формуле указать адрес​Excel.​​Песоцкий​ рукой. После ввода​Просмотрите в этом примере,​ столбцов.​ Excel».​ периода, данные в​ в таблице, есть​ за помощь!​ мучиться​ формулу массива и​ в той же​Математические (Math&Trig)​ соблюсти при нахождении​

Способ 4. Функция баз данных БДСУММ

​ Excel может проработать​​ статье «Функция «СУММЕСЛИ»​​ ячейки, в которой​​Нам нужно посчитать​​Напитки​= СУММЕСЛИМН (​ в котором у​В приведенном ниже примере​В Excel есть​ котором мы хотим​ строка «Красные яблоки».​ikki​maslow​ сам добавит фигурные​ категории​. Выделяем пустую ячейку​

​ суммы, – определенный​

planetaexcel.ru

СУММЕСЛИМН

​ до 127 массивов​​ в Excel, примеры»​ будем писать разные​
​ на какую сумму​5122​, автозавершение формул появится​ нас есть двумя​ формула должна возвращать​ еще одна функция,​ посчитать.​ Они по этой​:​: Всмысле допстолбец?​ скобки. Вводить скобки​Математические​ для результата, жмем​ город. Диапазон ячеек​ и критериев.​ здесь. В этой​ даты. В ячейку​ продал продукты первый​Северный​ под формулы, в​ условиями: необходимо узнать​ итоговое значение ежедневных​ которая сначала умножает​В диалоговом окне​ формуле не посчитаются,​Не по теме:​Тут проблема в​ с клавиатуры не​и работает похожим​
​ кнопку​ для проверки условия​

​Возможные ошибки при работе​​ статье мы рассмотрели,​
​ E10 устанавливаем функцию​

​ отдел за все​​Песоцкий​ списке аргументов в​ сумму продаж​ продаж яблок в​ ячейки в столбцах,​ условия функции «СУММЕСЛИМН»​

​ п.ч. первое слово​​ровно 50%. либо -​ объединенных ячейках. и​

​ надо. Легко сообразить,​​ образом, но имеет​
​fx​ 1 – столбец​ функции СУММЕСЛИМН:​ как использовать символы,​ «СУММЕСЛИ».​ дни. В ячейке​Молочные продукты​ их правильном порядке.​мяса​ Омске. Однако аргумент​ затем складывает результаты.​ по датам написали​ в ячейке начинается​ встретит, либо -​

​ если разделять их​​ что этот способ​​ больше аргументов:​​в строке формул,​​ с названиями городов​
​Результат равен 0 (что​ для поиска конкретных​
​В ячейке B10​ С9 устанавливаем функцию​6239​

​Если посмотреть на изображение​​в​диапазон_суммирования (C2:C10)​ Можно настроить функцию,​ так.​ не с буквы​ не встретит. :D​ то 2/3 строк​ (как и предыдущий)​При помощи полосы прокрутки​

​ находим функцию​​ (С2:С11). Условие 1​ является ошибкой). Так​
​ данных.​ пишем нужную дату.​ «СУММЕСЛИ».​
​Южный​ использования функции автозавершения​южном​не соответствует количеству​ чтобы считала по​Получилась такая формула.​ «Я».​
​Не по теме:​ в столбце будут​ легко масштабируется на​ в правой части​СУММЕСЛИ​ – это название​ бывает при определении​
​Если нужно указать​Диалоговое окно функции​В​Маринова​ формул и списка​районе (из столбца​ строк и столбцов​ условию. Смотрите об​=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;A2:A8;»Иванов»;D2:D8;»>=»&C11;D2:D8;» Здесь мы​Чтобы посчитать и​Комментарий модератора​ пустыми, а отчёт​ три, четыре и​

​ окна можно задать​​в списке:​ города, для которого​ условий в текстовом​ не одно условие​

​ заполнили так.​​Excel функция «СУММЕСЛИ»​

​Сельхозпродукты​

​ аргументов, то в​ A) (из столбца​ в аргументах​

​ этой функции в​

​ два раза указали​

CyberForum.ru

​ эти, «красные яблоки»,​

В этом разделе перечислены распространенные причины ошибки #ЗНАЧ! в функциях СУММЕСЛИ и СУММЕСЛИМН и способы их устранения.

Проблема: формула ссылается на ячейки в закрытой книге

Функции СУММЕ ЕСЛИ и СУММЕСС, которые ссылаются на ячейку или диапазон в закрытой книге, приведут к #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 Tech Community или попросить помощи в сообществе Answers community.

Почему не суммируются ячейки в Excel? Как сложить цифры в Excel, если не работает формула суммы? Причины и способы их решения

По роду своей деятельности, связанной с финансовыми расчетами, я очень много работаю с Excel. Поэтому ситуация, когда не суммируются ячейки в экселе, мне хорошо знакома. В такой ситуации стандартные приемы не работают и надо срочно найти какие-либо решение, чтобы все-таки договориться с этой программой.

Описать проблему суммирования данных в Excel я решила после того, как недавно позвонила моя коллега-бухгалтер. Она экспортировала данные из 1С в эксель, при выгрузке сбились форматы и формула суммы не работает.

Вдруг подумала: ведь с этим вопросом ко мне обращались несколько коллег, а значит эта проблема весьма актуальная. На самом деле очень просто решается, и я хочу об этом рассказать далее…

КАК Я УЗНАЛА ПОЧЕМУ ЭКСЕЛЬ НЕ СЧИТАЕТ СУММУ ВЫДЕЛЕННЫХ ЯЧЕЕК

Вспоминаю свой опыт знакомства с такого рода ситуациями. Тогда я была начинающим специалистом. Мне поручили собрать данные из 1С и на их основе сделать отчет.

Я выгрузила информацию, стала группировать данные для отчета. Вдруг поняла — значения не складываются. Это было ужасно. Пришлось потратить кучу времени, чтобы разобраться в чем дело. До сдачи отчета оставалось менее 20 минут. Паниковала я страшно. Внезапно, по какому-то наитию, вдруг заметила: в файле точки, а не запятые в разрядах… Мне повезло: репутация и отчет были спасены.

С двумя другими разновидностями этой проблемы я познакомилась позже, найдя решение методом проб и ошибок.

ФУНКЦИЯ АВТОМАТИЧЕСКОГО СУММИРОВАНИЯ В EXCEL

Хочу обратить Ваше внимание: в Excel может быть выключена функция суммирования.

В таком случае нужно просто включить вычисления: зайти в Параметры программы, далее выбрать Формулы, затем включить автоматические вычисления в книге.

Итак, вернемся к нашей теме.

ОСНОВНЫЕ ПРИЧИНЫ ПОЧЕМУ В EXCEL НЕ РАБОТАЕТ ФОРМУЛА СУММИРОВАНИЯ

В большинстве случаев проблемы возникают, когда данные экспортируются в эксель из других источников.

По-моему опыту, есть три основных ситуации, когда Excel отказывается суммировать данные:

1. Точка в разрядах
2. Пробелы
3. Формат: число как текст

ТОЧКА ВМЕСТО ЗАПЯТОЙ

Последний раз я столкнулась с этой проблемой, когда нужно было скопировать значения курса доллара за месяц из Интернет в Excel. Найдя информацию на профильном сайте, скопировала и вставила в файл экселя. На скриншоте видно, что формула для суммирования выдала результат 0.

В чем же проблема? В рассматриваемом случае у цифр знаком разделения разрядов является не запятая, а точка. Точку эксель не понимает, поэтому он числа воспринимает текстовым значением.

Это делается следующим образом:

• выделяем столбец с данными, которые нам нужно поправить.

• затем нажимаем Ctrl+F, вызываем меню “Найти и заменить”.

• “Найти” — вставляем точку (1), “Заменить” — вставляем запятую (2), нажимаем “Заменить все” (3). После этого появляется сообщение, что сделано определенное количество замен (4), а формула показывает результат (5).

НЕРАСПОЗНАВАЕМЫЕ ПРОБЕЛЫ

Следующая ситуация обычно бывает, когда мы берем данные, например, из 1С или другой учетной программы. В рассматриваемом случае разрядность правильная — запятая, но цифры все равно не складываются. Разгадка в том, что из 1С или другой учетной программы, скопировались неверные пробелы между разрядами значений.

В нашем примере цифры без разрядов: 114,96 и 954.

Делается это способом, похожим на предыдущий:

• в столбце с данными, которые нам нужно поправить встаем в ячейку с цифрой 1795,04, выделяем левой клавиши мышки пробел между цифрой 1 и 7 и копируем.

• выделяем столбец с цифрами, которые нам нужно поправить.
• затем нажимаем Ctrl+F, вызываем меню “Найти и заменить” (как в предыдущем случае).
• В “Найти” — вставляем скопированный пробел (1), “Заменить” — оставляем пустым, (2) далее нажимаем “Заменить все” (3)

• Затем появляется сообщение, что сделано определенное количество замен (5) и формула показывает результат (4).

ТЕКСТОВЫЙ ФОРМАТ ЯЧЕЕК

Данная ошибка, на мой взгляд, самый простой для исправления случай.

При выгрузке из других программ данные экспортируются в текстовом формате, о чем Excel нам сообщает зелененьким треугольничком. Если мы нажмем на него, то увидим сообщение:

Чтобы перевести из текстового формата в цифровой, необходимо выделить все ячейки, где указан текстовой формат, затем нажать на “Преобразовать в число”.

Далее все зеленые треугольнички пропадут, значения станут восприниматься экселем цифрами.

В этой заметке были рассмотрены самые распространенные случаи ошибок в форматах данных, из-за которых не суммируются ячейки в Excel (основные выводы представлены ниже).

Функция Excel СУММЕСЛИ (SUMIF) — примеры использования

Добрый день, уважаемые подписчики и посетители блога statanaliz.info. Совсем недавно мы разобрались с формулой ВПР, и сразу вдогонку я решил написать о другой очень полезной функции Excel – СУММЕСЛИ. Обе эти функции умеют «связывать» данные из разных источников (таблиц) по ключевому полю и при некоторых условиях являются взаимозаменяемыми. В то же время есть и существенные отличия в их назначении и использовании.

Если назначение ВПР в том, чтобы просто «подтянуть» данные из одного места Excel в другое, то СУММЕСЛИ используют, чтобы числовые данные просуммировать по заданному критерию.

Функцию СУММЕСЛИ можно успешно приспособить для решения самых различных задач. Поэтому мы в этой статье рассмотрим не 1 (один), а 2 (два) примера. Первый связан с суммированием по заданному критерию, второй – с «подтягиванием» данных, то есть в качестве альтернативы ВПР.

Пример суммирования с использованием функции СУММЕСЛИ

Этот пример можно считать классическим. Пусть есть таблица с данными о продажах некоторых товаров.

В таблице указаны позиции, их количества, а также принадлежность к той или иной группе товаров (первый столбец). Рассмотрим пока упрощенное использование СУММЕСЛИ, когда нам нужно посчитать сумму только по тем позициям, значения по которым соответствуют некоторому условию. Например, мы хотим узнать, сколько было продано топовых позиций, т.е. тех, значение которых превышает 70 ед. Искать такие товары глазами, а потом суммировать вручную не очень удобно, поэтому функция СУММЕСЛИ здесь очень уместна.

Первым делом выделяем ячейку, где будет подсчитана сумма. Далее вызываем Мастера функций. Это значок fx в строке формул. Далее ищем в списке функцию СУММЕСЛИ и нажимаем на нее. Открывается диалоговое окно, где для решения данной задачи нужно заполнить всего два (первые) поля из трех предложенных.

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

Теперь давайте рассмотрим, пример, когда критерий не совпадает с диапазоном суммирования. Такая ситуация встречается гораздо чаще. Рассмотрим те же условные данные. Пусть нам нужно узнать сумму не больше или меньше какого-то значения, а сумму конкретной группы товаров, допустим, группы Г.

Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров. Далее сам критерий прописываем либо вручную, оставив в соответствующем поле запись «группа Г», либо просто указываем мышкой ячейку с нужным критерием. Последнее окошко – это диапазон, где находятся суммируемые данные.

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Затем прописывается формула для первой группы и протягивается на все остальные. Здесь только нужно обратить внимание на относительность ссылок. Диапазон с критериями и диапазон суммирования должны быть абсолютным ссылками, чтобы при протягивании формулы они не «поехали вниз», а сам критерий, во-первых нужно указать мышкой (а не прописать вручную), во-вторых, должен быть относительной ссылкой, так как каждая сумма имеет свой критерий суммирования.

Заполненные поля Мастера функций при подобном расчете будут выглядеть примерно так.

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.

Пример использования функции СУММЕСЛИ для сопоставления данных

Функцию СУММЕСЛИ можно использовать для связки данных. Действительно, если просуммировать одно значение, то получится само это значение. Короче, СУММЕСЛИ легко приспособить для связки данных как альтернативу функции ВПР. Зачем использовать СУММЕСЛИ, если существует ВПР? Поясняю. Во-первых, СУММЕСЛИ в отличие от ВПР нечувствительна к формату данных и не выдает ошибку там, где ее меньше всего ждешь; во-вторых, СУММЕСЛИ вместо ошибок из-за отсутствия значений по заданному критерию выдает 0 (нуль), что позволяет без лишних телодвижений подсчитывать итоги диапазона с формулой СУММЕСЛИ. Однако есть и один минус. Если в искомой таблице какой-либо критерий повторится, то соответствующие значения просуммируются, что не всегда есть «подтягивание». Лучше быть настороже. С другой стороны зачастую это и нужно – подтянуть значения в заданное место, а задублированные позиции при этом сложить. Нужно просто знать свойства функции СУММЕСЛИ и использовать согласно инструкции по эксплуатации.

Теперь рассмотрим пример, как функция СУММЕСЛИ оказывается более подходящей для подтягивания данных, чем ВПР. Пусть данные из примера ваше – это продажи некоторых товаров за январь. Мы хотим узнать, как они изменились в феврале. Сравнение удобно произвести в этой же табличке, предварительно добавив еще один столбец справа и заполнив его данными за февраль. Где-то в другом экселевском файле есть статистика за февраль по всему ассортименту, но нам хочется проанализировать именно эти позиции, для чего требуется из большого файла со статистикой продаж всех товаров подтянуть нужные значения в нашу табличку. Для начала давайте попробуем воспользоваться формулой ВПР. В качестве критерия будем использовать код товара. Результат на рисунке.

Отчетливо видно, что одна позиция не подтянулась, и вместо числового значения выдается ошибка #Н/Д. Скорее всего, в феврале этот товар просто не продавался и поэтому он отсутствует в базе данных за февраль. Как следствие ошибка #Н/Д показывается и в сумме. Если позиций не много, то проблема не большая, достаточно вручную удалить ошибку и сумма будет корректно пересчитана. Однако количество строчек может измеряться сотнями, и рассчитывать на ручную корректировку не совсем верное решение. Теперь воспользуемся формулой СУММЕСЛИ вместо ВПР.

Результат тот же, только вместо ошибки #Н/Д СУММЕСЛИ выдает нуль, что позволяет нормально рассчитать сумму (или другой показатель, например, среднюю) в итоговой строке. Вот это и есть основная идея, почему СУММЕСЛИ иногда следует использовать вместо ВПР. При большом количестве позиций эффект будет еще более ощутимым.

Функция СУММЕСЛИМН — как суммировать ячейки в Excel, когда много условий?

В этом руководстве объясняется различие между функциями СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) с точки зрения их синтаксиса и использования, а также приводятся примеры формул для суммирования значений с несколькими критериями в Excel 2016, 2013, 2010, 2007, 2003 и ниже.

Как известно, Microsoft Excel предоставляет множество функций для выполнения различных расчетов с данными. Мы уже рассмотрели СУММЕСЛИ, которая суммирует числа, соответствующие указанным критериям. Теперь пришло время перейти к расширенной версии этой функции СУММЕСЛИМН, которая позволяет найти сумму по нескольким условиям.

Те, кто знаком с функцией СУММЕСЛИ, могут подумать, что преобразование ее в СУММЕСЛИМН потребует лишь букв «МН» и некоторого количества дополнительных критериев. Это может показаться вполне логичным… но «логично» — это не всегда имеет место при работе с Microsoft:)

Как работает СУММЕСЛИМН?

При помощи СУММЕСЛИМН можно найти сумму величин, для которых есть много условий. Она появилась впервые в MS Excel 2007, поэтому вы можете использовать ее во всех современных версиях программы.

По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:

СУММЕСЛИМН(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2],…)

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

диапазон_суммирования — требуется одна или более ячеек для суммирования. Это может быть отдельная клеточка, область или именованный диапазон. Суммируются только ячейки с чисами; пустые и текстовые значения игнорируются.

диапазон_условия1 — обязательный первый диапазон, который должен быть оценен по соответствующим критериям.

условие1— обязательное первое условие, которое должно быть выполнено. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, «> = 10», A1, «яблоко» или СЕГОДНЯ().

Все, что следует далее — это дополнительные диапазоны и связанные с ними критерии. Они не являются обязательными, но если у вас только одно ограничение, то зачем вам эта функция? Просто используйте СУММЕСЛИ. Тем не менее, вы можете использовать до 127 пар диапазон/условие.

Важно! Функция СУММЕСЛИМН работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным критериям (все требования соблюдаются для этой ячейки).

Использование СУММЕСЛИМН и СУММЕСЛИ в Excel — что нужно запомнить?

Поскольку целью этого руководства является охват всех возможных способов суммирования значений по большому количеству ограничений, мы обсудим примеры выражений с обеими функциями — СУММЕСЛИМН и СУММЕСЛИ с несколькими критериями. Чтобы использовать их правильно, вам необходимо четко понимать, что общего между этими двумя функциями и чем они отличаются.

Хотя общая часть ясна — схожий пункт назначения и параметры – но различия не так очевидны, хотя и достаточно существенны.

1. Порядок аргументов

Аргументы применяются по-разному. В частности, диапазон_сумирования является 1-м параметром в СУММЕСЛИ, но является третьим в СУММЕСЛИМН.

На первый взгляд может показаться, что Microsoft намеренно усложняет процесс обучения для своих пользователей. Однако при ближайшем рассмотрении вы увидите причины этого. Дело в том, что этот диапазон является необязательным в СУММЕСЛИ. Если вы его опустите, то нет никаких проблем, ваша формула будет складывать в диапазоне поиска (первый параметр).

В СУММЕСЛИМН он, напротив, очень важен и обязателен, и поэтому и стоит первым. Вероятно, ребята из Microsoft подумали, что после добавления 10- й или 100- й пары диапазон/критерий кто-то может забыть указать диапазон для суммирования:)

2. Диапазон суммирования и область критериев должны быть одинакового размера

В функции СУММЕСЛИ эти аргументы не обязательно должны иметь одинаковую размерность. Достаточно указать начальную точку. В СУММЕСЛИМН они должны содержать одинаковое количество строк и столбцов.

Выражение =СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F22;I3) вернет сообщение об ошибке #ЗНАЧ!, так как второй параметр поиска (F2:F22) не совпадает по размеру с остальными (E2:E21) и (C2:C21).

Хорошо, хватит стратегии (т.е. теории), давайте перейдем к тактике (к примерам).

Суммирование с множеством условий.

Имеются данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».

Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.

=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I3)

В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.

В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.

Если ОБА эти требования выполняются, то такой заказ нам подходит, и его стоимость мы учтём.

Как видите, у нас найдено 2 совпадения, в которых был продан молочный шоколад.

Использование операторов сравнения.

Рассчитаем по покупателю «Красный» стоимость заказов, в которых было более 100 единиц товара. Как видим, здесь нужно использовать и текстовый, и числовой критерий.

Критерии можно записать в саму формулу, и выглядеть это будет так:

=СУММЕСЛИМН(E2:E21;B2:B21;”Красный”;D2:D21;”>100”)

Но более рационально использовать ссылки, как это и сделано на рисунке:

=СУММЕСЛИМН(E2:E21;B2:B21;I2;D2:D21;I4)

Примечание. Обратите внимание, что в формулах логические выражения с операторами сравнения всегда должны быть заключены в двойные кавычки («»).

Синтаксис, а также работа с числами, текстом и датами у этой функции точно такие же, как и СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу о условном суммировании.

А как еще можно решить нашу задачу?

Способ 2. Используем функцию СУММПРОИЗВ.

Разберем подробнее, как работает СУММПРОИЗВ():

=СУММПРОИЗВ(—(B2:B21=$I$12);—(D2:D21>I13);E2:E21)

Результатом вычисления B2:B21=$I$12 является массив

{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}.

ИСТИНА означает соответствие кода покупателя условию, т.е. слову Красный. Массив этот можно увидеть, выделив в строке формул B2:B21=$I$12, а затем нажав F9.

А что за странные знаки «минус» перед этими выражениями? Дело в том, что нам необходимы не эти логические выражения, а числа, чтобы их затем можно было перемножать и складывать. Если Эксель производит математическую операцию с логическим выражением, то он автоматически преобразует его в число. А знак минус означает умножение на -1. А если дважды умножить на -1, то число в результате не изменится. Это мы помним еще из школьной математики 😊

И в результате логический массив превратится в массив чисел {0:1:0:0:0:0:0:0:0:0:0:0:1:0:0:0:1:0:0:0}.

Результатом вычисления D2:D21>I13 является массив

{ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}.

ИСТИНА соответствует ограничению «количество больше 100». Здесь мы также применяем двойное отрицание, чтобы преобразовать логические переменные в числа.

И, наконец, результатом вычисления В2:В13 является массив {11250:23210:12960:3150:5280:9750:3690:18300:5720:6150: 8400:2160:7200:1890:17050:3450:15840:2250:7200:8250}, т.е. просто числа из столбца E.

Результатом поэлементного умножения этих трех массивов является {0:23210:0:0:0:0:0:0:0:0:0:0:0:0:0:0:15840:0:0:0}. Суммируем эти произведения и получаем 39050.

Способ 3. Формула массива.

И еще один вариант расчета – применим формулу массива. В I14 запишем:

=СУММ((B2:B21=I12)*(D2:D21>I13)*(E2:E21))

Не забудьте в конце нажать комбинацию клавиш CTRL+SHIFT+ENTER, чтобы обозначить это выражение как формулу массива. Фигурные скобки в начале и в конце программа добавит автоматически. Вновь получим результат 39050.

Способ 4. Автофильтр.

Еще один альтернативный вариант – применение автофильтра. Для этого преобразуйте диапазон данных A1:F21 в «умную» таблицу. Напомню, что для этого в меню «Главная» выберите «Форматировать как таблицу». После этого добавьте в нее строку итогов (вкладка «Конструктор») и установите необходимые фильтры.

Без всяких формул итог по отфильтрованным строкам будет определён.

Как СУММЕСЛИМН работает с датами?

Если вы хотите отобрать и сложить какие-то показатели в определенном временном интервале на основе текущей даты, используйте функцию СЕГОДНЯ() в ваших ограничениях, как это показано ниже.

Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце А попадает в последние 7 дней, включая сегодняшний день (предполагается, что сегодня 7 февраля):

=СУММЕСЛИМН(D2:D21;A2:A21;»<=»&СЕГОДНЯ();A2:A21;»>=»&СЕГОДНЯ()-6)

Замечание. Когда вы при составлении ограничения используете другую функцию Excel вместе с логическим оператором, нужно использовать амперсанд (&) для объединения всего выражения в виде текста, например «<=»&СЕГОДНЯ().

Аналогичным образом вы можете использовать функцию Excel СУММЕСЛИ для суммирования каких-то показателей в заданном диапазоне дат. Например, следующая формула также решит нашу задачу:

=СУММЕСЛИ(A2:A21;»>=»&СЕГОДНЯ()-6;D2:D21) — СУММЕСЛИ(A2:A21;»<=»&СЕГОДНЯ();D2:D21)

Однако СУММЕСЛИМН сложение делает гораздо проще и понятнее, не так ли?

Суммирование по пустым и непустым ячейкам.

При анализе отчетов и других данных вам часто может потребоваться суммировать данные, соответствующие пустым или непустым клеткам таблицы.

Критерии	Описание	Содержание
Пустые ячейки	«=»	Суммируйте числа, соответствующие пустым, которые не содержат абсолютно ничего — ни формулы, ни строки нулевой длины.	=СУММЕСЛИМН(C2:C10;A2:A10;»=»;B2:B10,»=») Суммируйте в C2:C10, если соответствующие ячейки в столбцах A и B абсолютно пусты.
	«»	Суммируйте числа, соответствующие «визуально» пустым, включая те, которые содержат пустые строки, возвращаемые какой-либо другой функцией Excel (например, ячейки с формулой вроде = «»).	=СУММЕСЛИМН(C2:C10;A2:A10;»»;B2:B10,»»)   Суммируйте в C2:C10 с теми же параметрами, что и в приведенной выше формуле, но с пустыми строками.
Непустые ячейки	«<>»	Суммируйте числа, соответствующие непустым, включая строки нулевой длины.	=СУММЕСЛИМН(C2:C10;A2:A10;»<>»;B2:B10,»<>») Суммируйте в C2: C10, если соответствующие ячейки в столбцах A и B не пусты, включая ячейки с пустыми строками.
		Суммируйте числа, соответствующие непустым, не включая строки нулевой длины.	=СУММ(C2:C10) — СУММЕСЛИМН(C2:C10;A2:A10;»»;B2:B10,»») или {=СУММ((C2:C10)*(ДЛСТР(A2:A10)>0)*(ДЛСТР(B2: B10)>0))} Если в столбцах A и B содержится текст ненулевой длины, тогда соответствующее число из C складывается. Внимание! Это формула массива! Фигурные скобки вводить не нужно!

А теперь давайте посмотрим, как вы можете использовать формулу СУММЕСЛИМН с «пустыми» и «непустыми» ячейками для реальных данных.

По покупателю «Красный» рассчитаем количество товара в невыполненных заказах. Для этого в столбце B ищем соответствующее название клиента, а в F – пустую ячейку. Если оба требования совпадают, складываем количество товара из столбца D.

=СУММЕСЛИМН(D2:D21;F2:F21;»»;B2:B21;»Красный»)

или

=СУММЕСЛИМН(D2:D21;F2:F21;»=»;B2:B21;»Красный»)

Каждое из этих выражений дает верный результат – 144 единицы в заказе от 4 февраля.

Сумма нескольких условий.

А теперь давайте рассчитаем общую стоимость выполненных заказов по двум товарам.

Если мы просто добавим второй критерий в I3, и вместо I2 используем область I2:I3, то расчет будет неверным, поскольку в C2:C21 будем искать товар, в названии которого есть И «черный», И «молочный» одновременно. Ведь таких просто нет.

Поэтому первый вариант расчета таков:

=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I4)+СУММЕСЛИМН(E2:E21;C2:C21;I3;F2:F21;I4)

Просто складываем выполненные заказы сначала по первому, а затем по второму наименованию.

Второй вариант: используем элемент массива критериев и функцию СУММ.

=СУММ(СУММЕСЛИМН(E2:E21;C2:C21;{«*молочный*»;»*черный*»};F2:F21;I4))

Как видите, результаты получены одинаковые. Выбирайте способ, который будет для вас проще и понятнее.

Ещё примеры расчета суммы:

Как в экселе исправить ошибку #ЗНАЧ!?

Ошибка #ЗНАЧ! довольно часто преследует пользователей программы эксель и при этом не все пользователи знают, как с ней бороться. Поэтому рассмотрим подробную инструкцию, как в программе эксель исправить ошибку #ЗНАЧ!.

Первый шаг. Перед нами столбец, в котором возникла подобная ошибка.

Второй шаг. Чтобы выяснить причину ошибки, выделите ту ячейку, в которой она появляется. В правом верхнем углу появится восклицательный знак в желтом ромбике и рядом стрелочка. После нажатия на стрелочку, появится небольшое меню, в нем выбираем строку «Показать этапы вычисления».

Третий шаг. На экране появится меню, в котором показывается формула: 13 “0.5”. Данную формулу можно легко расшифровать, первая цифра без кавычек – это сумма всех числе. А вторая цифра с кавычками, сообщает, что данное значение – это не число. Следовательно, нужно проверить формат ячейки содержащую цифру “0.5”. Закрываем данное меню.

Четвертый шаг. Выделим ячейку «А3» со значением 0.5, нажмем на правую кнопку мыши, в появившемся меню выберем строку «Формат ячейки».

Пятый шаг. В появившемся меню, мы увидим, что 0.5 это текст, а не цифра, поэтому программа не может её посчитать.

Шестой шаг. Поменяем формат данной ячейки на «Числовой» и закроем меню.

Седьмой шаг. Теперь в ячейке «А3» поменяем точку на запятую. В итоге ошибка исчезнет, а программа эксель произведет необходимые расчеты.

Исправление ошибки #ЗНАЧ! ошибка

Ошибка #ЗНАЧ! в Excel означает: «Формула вводится неправильно. Или что-то не так с ячейками, на которые указывают ссылки». Эта ошибка возникает в самых разных случаях, и найти ее точную причину может быть сложно. Сведения на этой странице включают распространенные проблемы и решения ошибки. Возможно, понадобится попробовать одно или несколько решений, чтобы устранить конкретную ошибку.

Исправление ошибок определенных функций

• Какую функцию вы используете?
• СРЗНАЧ
• СЦЕПИТЬ
• СЧЁТЕСЛИ, СЧЁТЕСЛИМН
• ДАТАЗНАЧ
• ДНИ
• НАЙТИ, НАЙТИБ
• ЕСЛИ
• ИНДЕКС, ПОИСКПОЗ
• ПОИСК, ПОИСКБ
• СУММ
• СУММЕСЛИ, СУММЕСЛИМН
• СУММПРОИЗВ
• ВРЕМЗНАЧ
• ТРАНСП
• ВПР
• * Другая функция

Ваша функция не отображается в этом списке? Попробуйте другие решения, перечисленные ниже.

Проблемы с вычитанием

Если вы раньше не работали в Excel, вероятно, вы неправильно вводите формулу вычитания. Это можно сделать двумя способами:

Вычтите одну ссылку на ячейку из другой

Введите два значения в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D2 содержит плановую сумму, а ячейка E2 — фактическую. F2 содержит формулу =D2-E2.

Или используйте функцию СУММ с положительными и отрицательными числами

Введите положительное значение в одной ячейке и отрицательное — в другой. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки. В этом примере ячейка D6 содержит плановую сумму, а ячейка E6 — фактическую как негативное число. F6 содержит формулу =СУММ(D6;E6).

Если используется Windows, ошибка #ЗНАЧ! может возникнуть даже при вводе самой обычной формулы вычитания. Проблему можно решить следующим образом.

Для начала выполните быструю проверку. В новой книге введите 2 в ячейке A1. Введите 4 в ячейке B1. Затем введите формулу =B1-A1 в ячейке C1. Если возникнет ошибка #ЗНАЧ! перейдите к следующему шагу. Если сообщение об ошибке не появилось, попробуйте другие решения на этой странице.

В Windows откройте панель управления «Региональные стандарты».

Windows 10. Встроке поиска введите регион и выберите панель управления Региональные стандарты.

Windows 8. На начальном экране введите регион, щелкните Параметры и выберите пункт Региональные стандарты.

Windows 7. Нажмите кнопку Пуск, введите регион и выберите пункт Язык и региональные стандарты.

На вкладке Форматы нажмите кнопку Дополнительные параметры.

Найдите пункт Разделитель элементов списка. Если в поле разделителя элементов списка указан знак «минус», замените его на что-то другое. Например, разделителем нередко выступает запятая. Также часто используется точка с запятой. Однако для вашего конкретного региона может подходить другой разделитель элементов списка.

Нажмите кнопку ОК.

Откройте книгу. Если ячейка содержит ошибку #VALUE!, дважды щелкните ее для редактирования.

Если там, где для вычитания должны быть знаки «минус», стоят запятые, замените их на знаки «минус».

Нажмите клавишу ВВОД.

Повторите эти действия для других ячеек, в которых возникает ошибка.

Вычтите одну ссылку на ячейку из другой

Введите две даты в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D10 содержит дату начала, а ячейка E10 — дату окончания. F10 содержит формулу =E10-D10.

Или используйте функцию РАЗНДАТ

Введите две даты в двух отдельных ячейках. В третьей ячейке используйте функцию РАЗНДАТ, чтобы найти разницу дат. Дополнительные сведения о функции РАЗНДАТ см. в статье Вычисление разницы двух дат.

Растяните столбец по ширине. Если значение выравнивается по правому краю — это дата. Но если оно выравнивается по левому краю, это значит, что в ячейке на самом деле не дата. Это текст. И Excel не распознает текст как дату. Ниже приведены некоторые решения, которые помогут решить эту проблему.

Проверка наличия начальных пробелов

Дважды щелкните дату, которая используется в формуле вычитания.

Разместите курсор в начале и посмотрите, можно ли выбрать один или несколько пробелов. Вот как выглядит выбранный пробел в начале ячейки:

Если в ячейке обнаружена эта проблема, перейдите к следующему шагу. Если вы не видите один или несколько пробелов, перейдите к следующему разделу и проверьте параметры даты на компьютере.

Выделите столбец, содержащий дату, щелкнув его заголовок.

Выберите Данные > Текст по столбцам.

Дважды нажмите кнопку Далее.

На шаге 3 из 3 в мастере в разделе Формат данных столбца установите переключатель дата.

Выберите формат даты и нажмите кнопку Готово.

Повторите эти действия для других столбцов, чтобы убедиться, что они не содержат пробелы перед датами.

Проверка параметров даты на компьютере

Excel полагается на систему дат вашего компьютера. Если дата в ячейке введена в другой системе дат, Excel не распознает ее как настоящую дату.

Например, предположим, что компьютер отображает даты в формате мм.дд.гггг. Если ввести такую дату в ячейке, Excel распознает ее как дату, а вы сможете использовать ее в формуле вычитания. Однако если ввести дату в формате мм.дд.гг, Excel не распознает ее как дату. Вместо этого дата будет рассматриваться как текст.

Существует два решения этой проблемы: Вы можете изменить систему дат, которая используется на компьютере, чтобы она соответствовала системе дат, которая нужна в Excel. Или в Excel можно создать новый столбец и использовать функцию ДАТА, чтобы создать настоящую дату на основе даты в текстовом формате. Вот как это сделать, если система дат вашего компьютера — дд.мм.гггг, а в ячейке A1 записан текст 12/31/2017.

Создайте такую формулу: =ДАТА(ПРАВСИМВ(A1;4);ЛЕВСИМВ(A1;2);ПСТР(A1;4;2))

Результат будет 31.12.2017.

Чтобы использовать формат дд.мм.гг, нажмите клавиши CTRL+1 (или + 1 на Mac).

Выберите другой языковой стандарт, в котором используется формат дд.мм.гг, например Немецкий (Германия). После применения формата результат будет 31.12.2017, причем это будет настоящая дата, а не ее текстовая запись.

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

Проблемы с пробелами и текстом

Часто ошибка #ЗНАЧ! возникает, потому что формула ссылается на другие ячейки, содержащие пробелы или (что еще сложнее) скрытые пробелы. Из-за этих пробелов ячейка может выглядеть пустой, хотя на самом деле таковой не является.

1. Выберите ячейки, на которые указывают ссылки

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

2. Найдите и замените

На вкладке Главная щелкните Найти и выделить > Заменить.

3. Удалите пробелы

В поле Найти введите один пробел. Затем в поле Заменить удалите все, что там может быть.

4. Замените одно или все вхождения

Если вы уверены, что следует удалить все пробелы в столбце, нажмите кнопку Заменить все. Если вы хотите просмотреть и удалить пробелы по отдельности, можно сначала нажать кнопку Найти далее, а затем — Заменить, когда вы будете уверены, что пробел не нужен. После этого ошибка #ЗНАЧ! должна исчезнуть. Если нет — перейдите к следующему шагу.

5. Включите фильтр

Иногда из-за скрытых символов (а не просто пробелов) ячейка может выглядеть пустой, хотя на самом деле таковой не является. Например, это может происходить из-за одинарных кавычек в ячейке. Чтобы убрать эти символы из столбца, включите фильтр, последовательно выбрав Главная > Сортировка и фильтр > Фильтр.

6. Установите фильтр

Щелкните стрелку фильтра и снимите флажок Выделить все. Затем установите флажок Пустые.

7. Установите все флажки без названия

Установите все флажки, напротив которых ничего не указано, как на этом рисунке.

8. Выделите пустые ячейки и удалите их

Если Excel вернет пустые ячейки, выделите их. Затем нажмите клавишу DELETE. Все скрытые символы в ячейках будут удалены.

9. Очистите фильтр

Щелкните стрелку фильтра и выберите команду Удалить фильтр из. для отображения всех ячеек.

10. Результат

Если причиной появления ошибки #ЗНАЧ! были пробелы, вместо ошибки отобразится результат формулы, как показано в нашем примере. Если нет — повторите эти действия для других ячеек, на которые ссылается формула. Или попробуйте другие решения на этой странице.

Примечание: В этом примере обратите внимание, что ячейка E4 содержит зеленый треугольник, а число выравнивается по левому краю. Это значит, что число имеет текстовый формат. Это может вызвать проблемы в дальнейшем. Если вы заметили эту проблему, рекомендуем преобразовать числа из текстового формата в числовой.

Ошибку #ЗНАЧ! могут вызвать текст и специальные знаки в ячейке. Но иногда сложно понять, в каких именно ячейках они присутствуют. Решение: используйте функцию ЕТЕКСТ для проверки ячеек. Обратите внимание, что функция ЕТЕКСТ не устраняет ошибку, она просто находит ячейки, которые могут ее вызывать.

Пример с ошибкой #ЗНАЧ!

Вот пример формулы с ошибкой #ЗНАЧ! . Ошибка, скорее всего, возникает из-за ячейки E2. Здесь есть специальный знак, который выглядит как небольшой прямоугольник «00». Или, как показано на следующем рисунке, можно использовать функцию ЕТЕКСТ в отдельном столбце для поиска текста.

Этот же пример с функцией ЕТЕКСТ

Здесь в столбец F добавлена функция ЕТЕКСТ. Все ячейки в порядке, кроме одной со значением ИСТИНА. Это значит, что ячейка E2 содержит текст. Чтобы решить эту проблему, можно просто удалить содержимое ячейки и еще раз ввести число 1865,00. Вы также можете использовать функцию ПЕЧСИМВ, чтобы убрать символы, или функцию ЗАМЕНИТЬ, чтобы заменить специальные знаки на другие значения.

Использовав функцию ПЕЧСИМВ или ЗАМЕНИТЬ, вы можете скопировать результат в буфер обмена, а затем выбрать Главная > Вставить > Специальная вставка > Значения. Кроме того, может потребоваться преобразовать числа из текстового формата в числовой.

Формулам с математическими операторами (такими как + и *) не всегда удается вычислить ячейки, содержащие текст или пробелы. В таком случае попробуйте использовать вместо них функцию. Функции часто пропускают текстовые значения и определяют все значения как числовые, избегая ошибки #ЗНАЧ! . Например, вместо =A2+B2+C2 введите =СУММ(A2:C2). Или вместо =A2*B2 введите =ПРОИЗВЕД(A2,B2).

Удаление (скрытие) ошибок #ЗНАЧ! и Н/Д в формулах «Excel»

Часто при использовании формул в программе «Excel» из за не корректно введенных значений могут возникать ошибки #ЗНАЧ! и Н/Д в формулах.

В идеале следует вносить информацию для расчетов таким образом, чтобы ошибок не возникало, но бывают случаи, что ошибки должны возникать при определенных условиях в динамично заполняемых файлах и избавиться от них нельзя. Так же нельзя и удалить сами формулы из ячеек.

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

Рассмотрим функцию «Excel» при помощи, которой можно убрать (спрятать) ошибки #ЗНАЧ! и Н/Д в ячейках.

Эта функция обработки ошибок «ЕСЛИОШИБКА()».

Еслиошибка()

Как работает функция ЕСЛИОШИБКА()?

Данная функция позволяет заменить, возникающие ошибки, на любое значение.

Например, дефис («-») или звездочку («*»).

Функция ЕСЛИОШИБКА() работает следующим образом:

В поле «ЗНАЧЕНИЕ» вводиться формула, в которой может возникать ошибка.

Поле значение

В поле «Значение_если_ошибка» вводится то значение, которое присваивается ячейке при возникновении ошибки в формуле.

Значение, если ошибка

Ошибки в Excel

Если Excel не может правильно оценить формулу или функцию рабочего листа; он отобразит значение ошибки – например, #ИМЯ?, #ЧИСЛО!, #ЗНАЧ!, #Н/Д, #ПУСТО!, #ССЫЛКА! – в ячейке, где находится формула. Разберем типы ошибок в Excel, их возможные причины, и как их устранить.

Ошибка #ИМЯ?

Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.

Причины возникновения ошибки #ИМЯ?:

1. Если в формуле используется имя, которое было удалено или не определено.

Ошибки в Excel – Использование имени в формуле

Устранение ошибки: определите имя. Как это сделать описано в этой статье.

1. Ошибка в написании имени функции:

Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ

Устранение ошибки: проверьте правильность написания функции.

1. В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel – Ошибка в написании диапазона ячеек

Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

1. В формуле используется текст, не заключенный в двойные кавычки. Excel выдает ошибку, так как воспринимает такой текст как имя.

Ошибки в Excel – Ошибка в объединении текста с числом

Устранение ошибки: заключите текст формулы в двойные кавычки.

Ошибки в Excel – Правильное объединение текста

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:

1. Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

Устранение ошибки: проверьте корректность введенных аргументов в функции.

1. Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Ошибки в Excel – Ошибка в формуле из-за слишком большого значения

Устранение ошибки: откорректируйте формулу так, чтобы в результате получалось число в доступном диапазоне Excel.

Ошибка #ЗНАЧ!

Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.

Причины ошибки #ЗНАЧ!:

1. Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:

Ошибки в Excel – Суммирование числовых и текстовых значений

Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.

1. В аргументе функции введен диапазон, а функция предполагается ввод одного значения.

Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения

Устранение ошибки: укажите в функции правильные аргументы.

1. При использовании формулы массива нажимается клавиша Enter и Excel выводит ошибку, так как воспринимает ее как обычную формулу.

Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter .

Ошибки в Excel – Использование формулы массива

Ошибка #ССЫЛКА

В случае если формула содержит ссылку на ячейку, которая не существует или удалена, то Excel выдает ошибку #ССЫЛКА.

Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А

Устранение ошибки: измените формулу.

Ошибка #ДЕЛ/0!

Данная ошибка Excel возникает при делении на ноль, то есть когда в качестве делителя используется ссылка на ячейку, которая содержит нулевое значение, или ссылка на пустую ячейку.

Ошибки в Excel – Ошибка #ДЕЛ/0!

Устранение ошибки: исправьте формулу.

Ошибка #Н/Д в Excel означает, что в формуле используется недоступное значение.

Причины ошибки #Н/Д:

1. При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:

Ошибки в Excel – Искомого значения нет в просматриваемом массиве

Устранение ошибки: задайте правильный аргумент искомое значение.

1. Ошибки в использовании функций ВПР или ГПР.

Устранение ошибки: см. раздел посвященный ошибкам функции ВПР

1. Ошибки в работе с массивами: использование не соответствующих размеров диапазонов. Например, аргументы массива имеют меньший размер, чем результирующий массив:

Ошибки в Excel – Ошибки в формуле массива

Устранение ошибки: откорректируйте диапазон ссылок формулы с соответствием строк и столбцов или введите формулу массива в недостающие ячейки.

1. В функции не заданы один или несколько обязательных аргументов.

Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента

Устранение ошибки: введите все необходимые аргументы функции.

Ошибка #ПУСТО!

Ошибка #ПУСТО! в Excel возникает когда, в формуле используются непересекающиеся диапазоны.

Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны

Устранение ошибки: проверьте правильность написания формулы.

Причины возникновения ошибки

1. Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.

Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке

Устранение ошибки: увеличение ширины столбца/столбцов.

1. Ячейка содержит формулу, которая возвращает отрицательное значение при расчете даты или времени. Дата и время в Excel должны быть положительными значениями.

Ошибки в Excel – Разница дат и часов не должна быть отрицательной

Устранение ошибки: проверьте правильность написания формулы, число дней или часов было положительным числом.

9 ошибок Excel, которые вас достали

Как надоели уже эти непонятные символы в Excel, неверные значения, вычисления. В этой статье рассмотрим 9 распространенных ошибок, которые вы устали видеть. Почему они появляются, причины и как их исправить.

Как исправить ошибки Excel?

Вполне вероятно, вы уже хорошо знакомы с этими мелкими ошибками. Одно случайное удаление, один неверный щелчок могут вывести электронную таблицу из строя. И приходится заново собирать/вычислять данные, расставлять их по местам, что само по себе может быть сложным занятием, а зачастую, невозможным, не говоря уже о том, что это отнимает много времени.

И здесь вы не одиноки: даже самые продвинутые пользователи Эксель время от времени сталкиваются с этими ошибками. По этой причине мы собрали несколько советов, которые помогут вам сэкономить несколько минут (часов) при решении проблем с ошибками Excel.

В зависимости от сложности электронной таблицы, наличия в ней формул и других параметров, быть может не все удастся изменить, на какие-то мелкие несоответствия, если это уместно, можно закрыть глаза. При этом уменьшить количество таких ошибок вполне под силу даже начинающим пользователям.

Несколько полезных приемов в Excel

Прежде чем перейти к ошибкам и советам, мы хотели бы поделиться несколькими полезными приемами, которые помогут избежать ошибок в дальнейшем, сэкономить массу времени и сделать вашу жизнь намного проще:

• Начинайте каждую формулу со знака «=» равенства.
• Используйте символ * для умножения чисел, а не X.
• Сопоставьте все открывающие и закрывающие скобки «()», чтобы они были в парах.
• Используйте кавычки вокруг текста в формулах.

9 распространенных ошибок Excel, которые вы бы хотели исправить

Всем знакома маленькая зеленая стрелочка в верхнем левом углу ячейки. Вы знаете, этот противный флажок, который Excel использует, чтобы указать, что что-то пошло не так со значениями в ячейке.

Во многих случаях, нажав на эту стрелку, вы получите достаточно информации, чтобы решить проблему на месте. Вот так это выглядит:

Но не всегда этих сведений достаточно для того, чтобы понять, что не так с таблицей. Поэтому, пожалуйста, ознакомьтесь со списком распространенных ошибок, а также советами по их устранению. Найдите подходящее для себя решение, чтобы исправить ошибки и вернуться к нормальной работе.

1. Excel пишет #ЗНАЧ!

#ЗНАЧ! в ячейке что это

Ошибка #ЗНАЧ! появляется когда в формуле присутствуют пробелы, символы либо текст, где должно стоять число. Разные типы данных. Например, формула =A15+G14, где ячейка A15 содержит «число», а ячейка G14 — «слово».

Эксель требует, чтобы формулы содержали только цифры, и не будет отвечать на формулы, связанные с текстом, поэтому он покажет вам ошибку.

Как исправить #ЗНАЧ! в Excel

Простое решение этой ошибки — дважды проверить формулу, чтобы убедиться, что вы использовали только цифры. Если вы все еще видите ошибку, проверьте наличие пустых ячеек, пропущенных формул, связанных с ячейками, или каких-либо специальных символов, которые могли использовать.

В приведенном выше примере текст «Февраль» в ячейке G14 относится к текстовому формату. Программа не может вычислить сумму числа из ячейки A15 с текстом Февраль, поэтому дает нам ошибку.

2. Ошибка Excel #ИМЯ?

Более сложная ошибка. Вот краткое изложение того, почему это может появиться в ячейке, в которой вы работаете.

Почему в ячейке стоит #ИМЯ?

#ИМЯ? появляется в случае, когда Excel не может понять имя формулы, которую вы пытаетесь запустить, или если Excel не может вычислить одно или несколько значений, введенных в самой формуле. Чтобы устранить эту ошибку, проверьте правильность написания формулы или используйте Мастер функций, чтобы программа построила для вас функцию.

Нет, Эксель не ищет ваше имя в этом случае. Ошибка #ИМЯ? появляется в ячейке, когда он не может прочитать определенные элементы формулы, которую вы пытаетесь запустить.

Например, если вы пытаетесь использовать формулу =A15+C18 и вместо «A» латинской напечатали «А» русскую, после ввода значения и нажатия Enter, Excel вернет #ИМЯ?.

Допустим, вы правильно написали формулу, но недостаточно информации, введенной в отдельные ее записи. Запись в массиве таблиц неполная. Требуется фактическое имя таблицы, чтобы узнать, где искать желаемое значение.

Как исправить #ИМЯ? в Экселе?

Чтобы исправить ошибку #ИМЯ?, проверьте правильность написания формулы. Если написана правильно, а ваша электронная таблица все еще возвращает ошибку, Excel, вероятно, запутался из-за одной из ваших записей в этой формуле. Простой способ исправить это — попросить Эксель вставить формулу.

• Выделите ячейку, в которой вы хотите запустить формулу,
• Перейдите на вкладку «Формулы» в верхней части навигации.
• Выберите «Вставить функцию«. Если вы используете Microsoft Excel 2007, этот параметр будет находиться слева от панели навигации «Формулы».

После этого, в правой части вашей электронной таблицы появится Мастер функций, где вы сможете выбрать нужную формулу. Затем Excel проведет вас через каждый шаг формулы в отдельных полях, чтобы избежать ошибок и программа могла правильно прочитать вашу ячейку.

3. Excel отображает ##### в ячейке

Когда вы видите ##### в таблице, это может выглядеть немного страшно. Хорошей новостью является то, что это просто означает, что столбец недостаточно широк для отображения введенного вами значения. Это легко исправить.

Как в Excel убрать решетки из ячейки?

Нажмите на правую границу заголовка столбца и увеличьте ширину столбца.

4. #ДЕЛ/0! в Excel

В случае с #ДЕЛ/0!, вы просите Excel разделить формулу на ноль или пустую ячейку. Точно так же, как эта задача не будет работать вручную или на калькуляторе, она не будет работать и в Экселе.

Как устранить #ДЕЛ/0!

Лечится довольно просто. Измените значение на значение, не равное 0, или добавьте значение, если ваша ячейка была пустой.

5. #ССЫЛКА! в ячейке

Иногда это может немного сложно понять, но Excel обычно отображает #ССЫЛКА! в тех случаях, когда формула ссылается на недопустимую ячейку. Вот краткое изложение того, откуда обычно возникает эта ошибка:

Что такое ошибка #ССЫЛКА! в Excel?

#ССЫЛКА! появляется, если вы используете формулу, которая ссылается на несуществующую ячейку. Если вы удалите из таблицы ячейку, столбец или строку, и создадите формулу, включающую имя ячейки, которая была удалена, Excel вернет ошибку #ССЫЛКА! в той ячейке, которая содержит эту формулу.

Теперь, что на самом деле означает эта ошибка? Вы могли случайно удалить или вставить данные поверх ячейки, используемой формулой. Например, ячейка B16 содержит формулу =A14/F16/F17.

Если удалить строку 17, как это часто случается у пользователей (не именно 17-ю строку, но… вы меня понимаете!) мы увидим эту ошибку.

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

Как исправить #ССЫЛКА! в Excel?

Прежде чем вставлять набор ячеек, убедитесь, что нет формул, которые ссылаются на удаляемые ячейки. Кроме того, при удалении строк, столбцов, важно дважды проверить, какие формулы в них используются.

6. #ПУСТО! в Excel

Ошибка #ПУСТО! возникает, когда вы указываете пересечение двух областей, которые фактически не пересекаются, или когда используется неправильный оператор диапазона.

Чтобы дать вам некоторый дополнительный контекст, вот как работают справочные операторы Excel:

• Оператор диапазона (точка с запятой): определяет ссылки на диапазон ячеек.
• Оператор объединения (запятая): объединяет две ссылки в одну ссылку.
• Оператор пересечения (пробел): возвращает ссылку на пересечение двух диапазонов.

Как устранить ошибку #ПУСТО!?

Прежде всего, убедитесь, что вы используете правильный синтаксис в формуле. Используйте двоеточие, чтобы отделить первую ячейку от последней, когда вы ссылаетесь на непрерывный диапазон ячеек в формуле. С другой стороны, использование запятой поможет правильно вывести формулу, если вы ссылаетесь на две ячейки, которые не пересекаются.

Когда вы видите #Н/Д, это обычно означает, что числа, на которые вы ссылаетесь в вашей формуле, не могут быть найдены. Возможно, вы случайно удалили число или строку, которые используются в вашей формуле, или ссылаетесь на лист, который был удален или не сохранен.

Как устранить эту ошибку

3 раза проверьте все свои формулы и внимательно посмотрите, какие листы или строки могут быть удалены или неправильно указаны. Если у вас есть несколько формул, связанных вместе, убедитесь, что в каждой формуле присутствуют значения.

8. Ячейка Excel выдает ошибку #ЧИСЛО!

Если ваша формула содержит недопустимые числовые значения, появится ошибка #ЧИСЛО!. Это часто происходит, когда вы вводите числовое значение, которое отличается от других аргументов, используемых в формуле.

И еще, при вводе формулы, исключите такие значения, как $ 1000, в формате валюты. Вместо этого введите 1000, а затем отформатируйте ячейку с валютой и запятыми после вычисления формулы. Просто число, без знака $ (доллар).

Как устранить эту ошибку

Проверьте, не ввели ли вы какую-либо отформатированную валюту, дату или спецсимвол. Обязательно удалите их из формулы, сохранив только цифры.

Заключение

Напишите в комментариях, а что вы думаете по этому поводу. Хотите узнать больше советов по Excel? Обязательно поделитесь этой статьей с друзьями.

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

В этом разделе перечислены распространенные причины ошибки #ЗНАЧ! в функциях СУММЕСЛИ и СУММЕСЛИМН и способы их устранения.

Проблема: формула ссылается на ячейки в закрытой книге

Функции СУММЕСЛИ и СУММЕСЛИМН, которые ссылаются на ячейку или диапазон в закрытой книге, приводят к #VALUEу. .

Примечание. Это известная проблема, связанная с несколькими другими функциями Excel, такими как СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СЧИТАТЬПУСТОТЫ и т. д., чтобы присвоить имя. Посмотрите , какие функции СУММЕСЛИ, СЧЁТЕСЛИ и СЧИТАТЬПУСТОТЫ возвращают «#VALUE!» Ошибка статья для получения дополнительных сведений.

Решение: Откройте книгу, указанную в формуле, и нажмите клавишу F9, чтобы обновить формулу.

Кроме того, вы можете обойти эту ошибку, используя функции сумм и если вместе в формуле массива. Посмотрите , как функции СУММЕСЛИ, СЧЁТЕСЛИ и СЧИТАТЬПУСТОТЫ возвращают #VALUE! Error статья для получения дополнительных сведений.

Проблема: длина строки условия превышает 255 символов

Функции СУММЕСЛИ и СУММЕСЛИМН возвращают неправильные результаты, если они используются для сопоставления строк длиннее 255 символов.

Решение: Сократите строку, если это возможно. Если сократить строку нельзя, используйте функцию СЦЕПИТЬ или оператор-амперсанд (&), чтобы разделить значение на несколько строк. Например:

=СУММЕСЛИ(B2:B12;»длинная строка»&»еще одна длинная строка»)

Проблема: в функции СУММЕСЛИМН аргумент диапазон_условия не совпадает с аргументом диапазон_суммирования.

Аргументы диапазонов в функции СУММЕСЛИМН должны совпадать. Это означает, что в аргументах диапазон_условия и диапазон_суммирования должно указываться одинаковое количество строк и столбцов.

В приведенном ниже примере формула должна возвращать сумму ежедневных продаж яблоки в Омск. Аргумент сум_ранже (C2: C10) не соответствует тому же количеству строк и столбцов в аргументах критериа_ранже (a2: A12 _Амп_ B2: B12) . При использовании синтаксиса = СУММЕСЛИМН ( C2: C10, a2: A12, A14, B2: B12, B14) будет получено #VALUE! .

Решение: В приведенном примере измените диапазон_суммирования на C2:C12 и выполните формулу повторно.

Примечание: В функции СУММЕСЛИ можно использовать диапазоны разного размера.

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

Произведем сложение значений находящихся в строках, поля которых удовлетворяют сразу двум критериям (Условие И). Рассмотрим Текстовые критерии, Числовые и критерии в формате Дат. Разберем функцию СУММЕСЛИМН( ) , английская версия SUMIFS().

В качестве исходной таблицы возьмем таблицу с двумя столбцами (полями): текстовым «Фрукты» и числовым «Количество на складе» (См. файл примера ).

Задача1 (1 текстовый критерий и 1 числовой)

Найдем количество ящиков товара с определенным Фруктом И, у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики (ячейка D2), у которых остаток ящиков на складе >=6 (ячейка E2). Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число):

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

• B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
• A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
• D2 и «>=»&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

Порядок аргументов различен в функциях СУММЕСЛИМН() и СУММЕСЛИ() . В СУММЕСЛИМН() аргумент интервал_суммирования является первым аргументом, а в СУММЕСЛИ() – третьим. При копировании и редактировании этих похожих функций необходимо следить за тем, чтобы аргументы были указаны в правильном порядке.

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);—(B2:B13>=E2))
Разберем подробнее использование функции СУММПРОИЗВ() :

• Результатом вычисления A2:A13=D2 является массив <ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики. Массив можно увидеть, выделив в Строке формул A2:A13=D2 , а затем нажав F9;
• Результатом вычисления B2:B13 является массив<3:5:11:98:4:8:56:2:4:6:10:11>, т.е. просто значения из столбца B;
• Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является <0:0:0:0:4:8:56:0:0:0:0:0>. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
• Разберем второе условие: Результатом вычисления —( B2:B13>=E2) является массив <0:0:1:1:0:1:1:0:0:1:1:1>. Значения в столбце «Количество ящиков на складе», которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
• Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL+SHIFT+ENTER

5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15).

Примечание: для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

В версиях Excel 2007 и выше работает функция СУММЕСЛИМН, которая позволяет при нахождении суммы учитывать сразу несколько значений. В самом названии функции заложено ее назначение: сумм а данных, если совпадает мн ожество условий.

Синтаксис СУММЕСЛИМН и распространенные ошибки

Аргументы функции СУММЕСЛИМН:

1. Диапазон ячеек для нахождения суммы. Обязательный аргумент, где указаны данные для суммирования.
2. Диапазон ячеек для проверки условия 1. Обязательный аргумент, к которому применяется заданное условие поиска. Найденные в этом массиве данные суммируются в пределах диапазона для суммирования (первого аргумента).
3. Условие 1. Обязательный аргумент, составляющий пару предыдущему. Критерий, по которому определяются ячейки для суммирования в диапазоне условия 1. Условие может иметь числовой формат, текстовый; «воспринимает» математические операторы. Например, 45; « , = и др.).

Примеры функции СУММЕСЛИМН в Excel

У нас есть таблица с данными об оказанных услугах клиентам из разных городов с номерами договоров.

Предположим, нам необходимо подсчитать количество услуг в определенном городе с учетом вида услуги.

Как использовать функцию СУММЕСЛИМН в Excel:

1. Вызываем «Мастер функций». В категории «Математические» находим СУММЕСЛИМН. Можно поставить в ячейке знак «равно» и начать вводить название функции. Excel покажет список функций, которые имеют в названии такое начало. Выбираем необходимую двойным щелчком мыши или просто смещаем курсор стрелкой на клавиатуре вниз по списку и жмем клавишу TAB.
2. В нашем примере диапазон суммирования – это диапазон ячеек с количеством оказанных услуг. В качестве первого аргумента выбираем столбец «Количество» (Е2:Е11). Название столбца не нужно включать.
3. Первое условие, которое нужно соблюсти при нахождении суммы, – определенный город. Диапазон ячеек для проверки условия 1 – столбец с названиями городов (С2:С11). Условие 1 – это название города, для которого необходимо просуммировать услуги. Допустим, «Кемерово». Условие 1 – ссылка на ячейку с названием города (С3).
4. Для учета вида услуг задаем второй диапазон условий – столбец «Услуга» (D2:D11). Условие 2 – это ссылка на определенную услугу. В частности, услугу 2 (D5).
5. Вот так выглядит формула с двумя условиями для суммирования: =СУММЕСЛИМН(E2:E11;C2:C11;C3;D2:D11;D5).

Результат расчета – 68.

Гораздо удобнее для данного примера сделать выпадающий список для городов:

Теперь можно посмотреть, сколько услуг 2 оказано в том или ином городе (а не только в Кемерово). Формулу немного видоизменим: =СУММЕСЛИМН($E$2:$E$11;$C$2:$C$11;F$2;$D$2:$D$11;$D$5).

Все диапазоны для суммирования и проверки условий нужно закрепить (кнопка F4). Условие 1 – название города – ссылка на первую ячейку выпадающего списка. Ссылку на условие 2 тоже делаем постоянной. Для проверки из списка городов выберем «Кемерово»:

Результат тот же – 68.

По такому же принципу можно сделать выпадающий список для услуг.