Знач в excel как исправить
Перейти к содержимому

Знач в excel как исправить

  • автор:

Знач в excel как исправить

суббота, 8 мая 2021 г.

Как исправить ошибку #ЗНАЧ в Excel

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

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

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

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

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

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

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

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

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

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

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

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

Исправление ошибки #ЗНАЧ! в функции ЕСЛИ

ЕСЛИ является одной из самых универсальных и популярных функций в Excel и часто используется несколько раз в одной формуле, а также в сочетании с другими функциями. К сожалению, из-за сложности, с которой можно построить инструкции IF, довольно легко выполнить #VALUE! ошибку #ЗНАЧ!. Обычно ошибку можно подавить, добавив в формулу определенные функции обработки ошибок, такие как ISERROR, ISERR или IFERROR.

Проблема: аргумент ссылается на ошибочные значения.

При наличии ссылки на ячейку на значение ошибки если отображается #VALUE! ошибку #ЗНАЧ!.

Решение: используйте с функцией ЕСЛИ функции для обработки ошибок, такие как ЕОШИБКА, ЕОШ и ЕСЛИОШИБКА. В следующих разделах описывается, как использовать функции ЕСЛИ, ЕОШИБКА, ЕОШ и ЕСЛИОШИБКА в формуле, если аргумент ссылается на ошибочные значения.

  • Исправление ошибки #ЗНАЧ! в функции СЦЕПИТЬ
  • Исправление ошибки #ЗНАЧ! в функции СРЗНАЧ или СУММ
  • ФУНКЦИЯ IFERROR была введена в Excel 2007 и гораздо более предпочтительна, чем ISERROR или ISERR, так как для этого не требуется избыточное создание формулы. ISERROR и ISERR принудительно вычисляют формулу дважды, сначала чтобы увидеть, имеет ли она ошибку, а затем снова для возврата результата. IFERROR вычисляет только один раз.
  • Конструкция =ЕСЛИОШИБКА(Формула;0) гораздо лучше конструкции =ЕСЛИ(ЕОШИБКА(Формула;0;Формула)).

Проблема: неправильный синтаксис.

Если синтаксис функции построен неправильно, она может вернуть #VALUE! ошибку #ЗНАЧ!.

Решение: проверьте правильность синтаксиса. Ниже приведен пример правильно составленной формулы, в которой функция ЕСЛИ вкладывается в другую функцию ЕСЛИ для расчета вычетов на основе уровня доходов.

Пример правильно построенного выражения ЕСЛИ

Обычным языком это можно выразить так: ЕСЛИ значение в ячейке A5 меньше чем 31 500, значение умножается на 15 %. Но ЕСЛИ это не так, проверьте, меньше ли это значение, чем 72 500. ЕСЛИ это так, значение умножается на 25 %; в противном случае — на 28 %.

Чтобы использовать функцию ЕСЛИОШИБКА с уже имеющейся формулой, просто вложите готовую формулу в функцию ЕСЛИОШИБКА:

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

Примечание: Значения в вычислениях разделяются точкой с запятой. Если разделить два значения запятой, функция ЕСЛИ будет рассматривать их как одно дробное значение. После процентных множителей ставится символ %. Он сообщает Excel, что значение должно обрабатываться как процентное. В противном случае такие значения пришлось бы вводить как дробные множители, например «E2*0,25».

Сообщение Excel, появляющееся при добавлении запятой в значение

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Знач в excel как исправить

Друзья, снова нужна ваша помощь!)

Есть два отдельных файла: один — с данными («2020_1»), второй — с сводной таблицей («2020 свод»). Когда открыты оба файла, все работает хорошо. Но, когда файл с данными закрыт — сводная таблица во втором файле не работает, выдает ошибки ЗНАЧ#

Очень прошу, помогите пожалуйста решить данную проблему! Заранее спасибо!)

Прикрепленные файлы

  • 2020 свод.xlsx (44.02 КБ)
  • 2020_1.xlsx (56.84 КБ)

Пользователь
Сообщений: 4611 Регистрация: 10.09.2017
08.03.2021 17:30:35

Некоторые функции не работают с закрытыми книгами (и начинают работать, если эти книги открыть): СУММЕСЛИ, СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН, СМЕЩ, .

Пользователь
Сообщений: 16 Регистрация: 24.04.2020
08.03.2021 17:43:17
как можно отредактировать мои функции, для того, чтобы они работали с закрытыми файлами?
Пользователь
Сообщений: 831 Регистрация: 11.02.2021
08.03.2021 17:59:37
Изменено: Marat Ta — 08.03.2021 17:59:56
Пользователь
Сообщений: 4611 Регистрация: 10.09.2017
08.03.2021 19:28:38
В естествознании формулах Excel я слаб. Попробуйте, например, для ячейки D14:

=СУММПРОИЗВ(('C:\Temp\[2020_1.xlsx]Пластик'!$E$27:$E$999) * ('C:\Temp\[2020_1.xlsx]Пластик'!$A$27:$A$999>=ДАТА($C14;E$11;1)) * ('C:\Temp\[2020_1.xlsx]Пластик'!$A$27:$A$999<=ДАТА($C14;E$11+1;1)-1) * (('C:\Temp\[2020_1.xlsx]Пластик'!$B27:$B$999="вікна") +('C:\Temp\[2020_1.xlsx]Пластик'!$B$27:$B$999="двері")))

Измените C:\temp на Ваш каталог, где лежат обе книги.
Изменено: sokol92 - 08.03.2021 19:36:10
Сообщений: 22789 Регистрация: 28.12.2016
Excel 2013, 2016
08.03.2021 20:27:19
По вопросам из тем форума, личку не читаю.
Пользователь
Сообщений: 16 Регистрация: 24.04.2020
08.03.2021 20:46:46

sokol92, сделал так как Вы написали, но после того как файл закрыть и снова открыть выдает такие ошибки (фото прилагаю):

Прикрепленные файлы

  • unnamed.jpg (106.23 КБ)
  • unnamed (1).jpg (104.36 КБ)

Пользователь
Сообщений: 16 Регистрация: 24.04.2020
08.03.2021 21:26:09
sokol92, БМВ, помогите пожалуйста решить данную проблему, я в тупике, не знаю что делать . ((
Пользователь
Сообщений: 4611 Регистрация: 10.09.2017
09.03.2021 13:13:24

Здесь вряд ли есть хорошее решение (могу ошибаться). Если Вы на первый вопрос в #7 ответите "Изменить связи" / Обновить / Пластик, то формула, указанная в #5, будет пересчитана.

Пользователь
Сообщений: 16 Регистрация: 24.04.2020
09.03.2021 18:19:36
sokol92, понял. В любом случае спасибо за помощь!
Пользователь
Сообщений: 16 Регистрация: 24.04.2020
09.03.2021 18:21:39
sokol92, Power Query или какие-то другие инструменты также не решат вопрос?
Пользователь
Сообщений: 4611 Регистрация: 10.09.2017
09.03.2021 18:26:16

Я стараюсь вообще не работать с закрытыми книгами. Может быть, коллеги что-нибудь позитивного расскажут..

Пользователь
Сообщений: 3 Регистрация: 13.08.2021
13.08.2021 17:13:14

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

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

С недавнего времени у некоторых пользователей, которые проверяют часть этой книги, появилась проблема, значения с некоторых ячеек стали с ошибкой "ЗНАЧ".
Да, там присутствовала формула СЧЁТЕСЛИ, но она раньше всегда показывала значения, и сейчас показывает при закрытой основной книги у других пользователей.

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2013 Excel Web App Excel Starter 2010 Еще. Меньше

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

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

  • Проблемы с вычитанием
  • Проблемы с пробелами и текстом
  • Другие решения

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

Какую функцию вы используете?

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

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

Выполнение базового вычитания

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

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

Ячейка D2 со значением 2000,00 ₽, ячейка E2 со значением 1500,00 ₽, ячейка F2 с формулой =D2-E2 и результатом 500,00 ₽

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

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

Ячейка D6 со значением 2000,00 ₽, ячейка E6 со значением 1500,00 ₽, ячейка F6 с формулой =СУММ(D6;E6) и результатом 500,00 ₽

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

Ошибка #ЗНАЧ! при базовом вычитании

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

  1. Для начала выполните быструю проверку. В новой книге введите 2 в ячейке A1. Введите 4 в ячейке B1. Затем введите формулу =B1-A1 в ячейке C1. Если возникнет ошибка #ЗНАЧ! перейдите к следующему шагу. Если сообщение об ошибке не появилось, попробуйте другие решения на этой странице.
  2. В Windows откройте панель управления "Региональные стандарты".
    • Windows 10: нажмите кнопку Пуск, введите Регион, а затем выберите панель управления Регион.
    • Windows 8. На начальном экране введите Регион, выберите Параметры, а затем — Регион.
    • Windows 7: выберите Пуск, введите Регион, а затем выберите Регион и язык.
  3. На вкладке Форматы выберите Дополнительные параметры.
  4. Найдите пункт Разделитель элементов списка. Если в поле разделителя элементов списка указан знак "минус", замените его на что-то другое. Например, разделителем нередко выступает запятая. Также часто используется точка с запятой. Однако для вашего конкретного региона может подходить другой разделитель элементов списка.
  5. Нажмите кнопку ОК.
  6. Откройте книгу. Если ячейка содержит ошибку #VALUE!, дважды щелкните ее для редактирования.
  7. Если там, где для вычитания должны быть знаки "минус", стоят запятые, замените их на знаки "минус".
  8. Нажмите клавишу ВВОД.
  9. Повторите эти действия для других ячеек, в которых возникает ошибка.

Вычитание дат

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

Ячейка D10 со значением 01.01.2016, ячейка E10 со значением 24.04.2016, ячейка F10 с формулой =E10-D10 и результатом 114

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

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

Ячейка D15 со значением 01.01.2016, ячейка E15 со значением 24.04.2016, ячейка F15 с формулой =РАЗНДАТ(D15;E15;

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

Ошибка #ЗНАЧ! при вычитании дат в текстовом формате

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

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

Ячейка, в которой выделен пробел перед значением 01.01.2016

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

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

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

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

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

Изображение значка кнопки команд в Mac

  1. Создайте такую формулу: =ДАТА(ПРАВСИМВ(A1;4);ЛЕВСИМВ(A1;2);ПСТР(A1;4;2))
  2. Результат будет 31.12.2017.
  3. Чтобы использовать формат дд.мм.гг, нажмите клавиши CTRL+1 (или

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *