ЗНАЧ в Excel — как исправить? В зависимости от причины требуется исправление проблемы с вычитанием, пробелами и текстом. Может потребоваться определение источника ошибка, замена «ЗНАЧ» другим значением, проверка подключение данных или использование помощи представителей форума сообщества. Ниже рассмотрим, что значите появление такой ошибки, чем она может быть вызвана, и каким способом ее можно устранить.
Что значит и когда возникает
Перед исправлением ошибки необходимо разобраться, почему в Эксель вместо чисел появляется ЗНАЧ. Это означает, что формула введена неправильно или имеются проблемы с ячейками, куда идут ссылки. Сбой может возникать в разных ситуациях и отыскать точную причину можно лишь путем поиска. Данные о странице включают разные проблемы. Они могут быть следующими:
- вычитание;
- пробелы или текст;
- появление ошибки ввода;
- неправильные значения и т. д.
Если разобраться, что означает в Экселе ЗНАЧ, можно быстрее справиться с проблемой и устранить ее. В большинстве случаев для решения задачи может потребоваться применение сразу нескольких решений, необходимых для устранения указанной ошибки в Excel.
Как исправить
Теперь разберемся подробнее, как исправить в Эксель ЗНАЧ в формуле, когда не удается сделать вычисление в обычном режиме. Рассмотрим разные проблемы и методы решения.
Вычитание
Наиболее распространенная ситуация, когда ошибка «ЗНАЧ» в Excel возникает из-за трудностей с вычитанием. Чтобы исправить сбой, могут помочь рассмотренные ниже шаги.
Для начала проверьте правильность ввода формулы вычитания. Сделайте следующие шаги, чтобы исправить ситуацию в Excel:
- Вычтите одну ссылку на ячейку из другой. Введите два параметра в 2-х отдельных секциях, а в третьей вычтите одну ссылку на ячейку из другой. К примеру, это может быть =A2-B2.
- Как вариант, используйте опцию «СУММ» с цифрами выше / ниже нуля. Для этого введите параметр выше нуля в одной, а ниже — в другой ячейке. В третьей применяется опция «СУММ» для складывания. Пример: =СУММ (D5;E5).
Еще один способ, как убрать ЗНАЧ в Excel — ввести правильную функцию вычитания. Алгоритм действий такой:
- Сделайте проверку. Для этого в новом файле введите «3» в А1 и 6 в В1. Если при формуле =В1-А1 появляется ЗНАЧ, переходите к следующему шагу.
- Войди на Виндовс в раздел «Региональные стандарты». В «десятке» в поисковой строке введите «регион», а после выберите панель управления «Региональные стандарты».
- На вкладке «Форматы» кликните на «Дополнительные параметры» и найдите «Разделитель элементов списка». При наличии здесь знака «минус» поменяйте его на другой элемент и жмите ОК.
- Войдите в книгу Excel. Если в ячейке имеется надпись #VALUE!, два раза жмите на нее для внесения правок. В случае, когда вместо «минуса» стоит запятые, замените их на «минус». Далее жмите «Ввод» и повторите эти шаги для других ячеек, для которых возникла ошибка.
Распространенная причина, почему в Экселе выходит ЗНАЧ — неправильные операции с датами. Для решения вопроса сделайте следующее:
- Вычтите одну ссылку на ячейку из второй.
- Используйте опцию РАЗНДАТ для 3-ей ячейке, что позволяет отыскать разницу в датах.
Следующий способ, как исправить ЗНАЧ в Excel — убедиться, что программа распознает текст, как дату для проведения проверки. Чтобы исправить сбой, сделайте следующее:
- Два раза жмите на дату, используемую в формуле.
- Разместите курсор на старте и убедитесь, можно ли выбрать один или более пробелов.
- Выделите столбец с датой, нажав на заголовок.
- Кликните на «Данные», а потом — «Текст по столбцам».
- Два раза жмите «Далее».
- На третьем шаге в секции «Формат данных столбца» и переместите переключатель «Дата».
- Выберите формат даты и кликните «Готово».
- Повторите эти шаги для разных столбцов, чтобы убедиться в них нет пробелов возле даты.
Пробелы и текст
Еще одно объяснение, что значит ЗНАЧ в Excel — проблемы с текстами / пробелом. В таком случае воспользуйтесь одним из рассмотренных ниже шагов.
Для начала удалите пробелы, из-за которых возможно появление ошибки ЗНАЧ. К примеру, формула направляет на ячейки, где имеются видимые или скрытые пробелы, как результат, ячейка кажется пустой.
Сделайте следующее в Excel:
- Выделите ячейки, куда направляют ссылки.
- Перейдите в раздел «Главная», а поле жмите «Найти и выделить».
- Кликните «Заменить».
- В поле «Найти» введите пробел, а в поле «Заменить» удалите все, что там имеется.
- Замените одно / все вхождения.
- Если ЗНАЧ не удалось исправить, включите фильтр в разделе «Главная», а далее «Сортировка и фильтр» — «Фильтр».
- Жмите стрелку внизу и удалите флажок с поля «Выделить все», а после установите отметку «Пустые».
- Поставьте флажки в местах без названия.
- Выделите незаполненные ячейки и удалите их.
- Кликните на стрелку вниз и выберите «Удалить фильтр из…».
Если ошибка ЗНАЧ появлялась из-за пробелов, рассмотренным методом удается исправить ситуацию. В ином случае может потребоваться применение другого варианта.
Причиной, почему вы видите ЗНАЧ в Эксель, может быть текст / специальные знаки. Обратите внимание на ячейки и удалите лишнее. Может потребоваться применение функции «Заменить» или «ПЕЧСИМВ» или «ЗАМЕНИТЬ».
Еще один способ, как исправить, если в Excel вместо цифр ЗНАЧ — использовать формулы вместо операций. При наличии «+» или «*» рассчитать ячейки с текстом / пробелом не получится. Для решения проблемы может потребоваться применение специальных функций. К примеру, вместо сложной формулы =А2+В2+С2 укажите =СУММ (А2:С2).
Дополнительные шаги
Рассматривая пути, как исправить ошибку ЗНАЧ в Excel, можно использовать и другие пути решения проблемы:
- Определите источник ошибки. Для начала выделите секцию, где имеется параметр ЗНАЧ, а после этого жмите на «Формулы», «Вычислить формулы», а после — «Вычислить». В этом случае Эксель обрабатывает каждую часть формулы отдельно. При этом можно увидеть, где проблема, а после исправить ее.
- Замените ЗНАЧ другим параметром Excel, чтобы исправить проблему. Попробуйте добавить формулу ЕСЛИОШИБКА, которая проверят наличие сбоев и заменяет ЗНАЧ другим значением, к примеру, двумя дефисами.
- Восстановите подключение. Попросите создателя файла Excel сделать новую книгу, чтобы в ней были только параметры без подключений. Для этого копируйте ячейки и вставьте только значения. Это происходит следующим образом — «Главная», а потом «Вставить» и «Специальная вставка» — «Значения».
Как вариант, можно зайти в сообщество Excel по ссылке go.microsoft.com/fwlink/?linkid=827514, где можно задать вопрос и исправить проблему с Excel.
Теперь вы знаете, почему в Экселе пишет ЗНАЧ и не считает формулу, как исправить ситуацию, и какие шаги необходимо осуществлять в первую очередь. Более подробные сведения можно найти на официальном сайте по ссылке support.microsoft.com/ru-ru/office/исправление-ошибки-знач-ошибка-15e1b616-fbf2-4147-9c0b-0a11a20e409e.
В комментариях расскажите, какое из приведенных решений вам помогло исправить проблему с Excel, и какие еще можно использовать варианты.
Отличного Вам дня!
Ошибка #ЗНАЧ! в 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 на Mac).Примечание: Формула выше написана с использованием функций ДАТА, ПРАВСИМВ, ПСТР и ЛЕВСИМВ. Обратите внимание, что формула записана с учетом того, что в текстовой дате используется два символа для дней, два символа для месяцев и четыре символа для года. Возможно, вам понадобится откорректировать формулу под свою запись даты.
Проблемы с пробелами и текстом
Часто ошибка #ЗНАЧ! возникает, потому что формула ссылается на другие ячейки, содержащие пробелы или (что еще сложнее) скрытые пробелы. Из-за этих пробелов ячейка может выглядеть пустой, хотя на самом деле таковой не является.
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 обработает каждую часть формулы по отдельности. В данном случае формула =E2+E3+E4+E5 выдает ошибку из-за скрытого пробела в ячейке E2. Пробела не видно, если смотреть на ячейку E2. Но его можно увидеть здесь. Он отображается как » «.
Иногда вам может быть нужно вместо ошибки #ЗНАЧ! выводить что-то свое, например собственный текст, ноль или пустую ячейку. В этом случае можно добавить в формулу функцию ЕСЛИОШИБКА. Она проверяет, есть ли ошибка, и если да, заменяет ее другим значением по вашему выбору. Если ошибки нет, вычисляется исходная формула. Функция ЕСЛИОШИБКА работает только в Excel 2007 и более поздних версиях. В предыдущих версиях можно использовать функцию ЕСЛИ(ЕОШИБКА()).
Предупреждение: Функция ЕСЛИОШИБКА скроет все ошибки, а не только ошибку #ЗНАЧ! . Ошибки не рекомендуется скрывать, так как они часто указывают на то, что какое-то значение нужно исправить, а не просто скрыть. Используйте эту функцию, только если вы абсолютно уверены, что формула работает правильно.
Ячейка с ошибкой #ЗНАЧ!
Вот пример формулы, в которой ошибка #ЗНАЧ! вызвана скрытым пробелом в ячейке E2.
Ошибка, скрытая функцией ЕСЛИОШИБКА
А вот эта же формула с добавленной функцией ЕСЛИОШИБКА. Ее можно прочитать как «Вычислить формулу, но если возникнет какая-либо ошибка, заменить ее двумя дефисами.» Помните, что также можно использовать «», чтобы ничего не отображать вместо двух дефисов. Или вы можете подставить свой текст, например: «Ошибка суммирования».
К сожалению, как вы видите, функция ЕСЛИОШИБКА не устраняет ошибку, а только скрывает ее. Так что используйте ее, если точно уверены, что ошибку лучше скрыть, чем исправить.
В какой-то момент подключения к данным могут стать недоступны. Чтобы исправить ошибку, восстановите подключение или, если это возможно, импортируйте данные. Если у вас нет доступа к подключению, попросите автора книги создать для вас новый файл. В идеале в нем будут только значения (без подключений). Для этого можно скопировать все ячейки и вставить только значения, щелкнув Главная > Вставить > Специальная вставка > Значения. При этом будут удалены все формулы и подключения, а ошибки #ЗНАЧ! исчезнут.
См. также
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Все курсы > Анализ и обработка данных > Занятие 5
На прошлом занятии, посвященном практике EDA, мы работали с «чистыми данными», то есть такими данными, в которых нет ни ошибок, ни пропущенных значений. К сожалению, так бывает далеко не всегда.
Сегодня мы научимся очищать данные от дубликатов, неверных и плохо отформатированных значений, а также исправлять ошибки в дате и времени. На следующем занятии мы поговорим про работу с пропусками.
Откроем ноутбук к этому занятию⧉
Ошибки в данных могут встречаться по многим причинам. Они могут быть связаны с человеческим фактором, например, простой невнимательностью, или вызваны сбоями в работе записывающего какие-либо показатели оборудования.
В качестве примера мы будем использовать несложный датасет, в котором содержатся данные за 2019 год об отдельных финансовых показателях сети магазинов одежды, представленной в нескольких городах мира. В частности, нам доступна следующая информация:
- month — за какой месяц сделана запись
- profit — прибыль (profit) по сети
- MoM — изменение выручки (revenue) сети по отношению к предыдущему месяцу
- high — магазин с наибольшей маржинальностью (margin) продаж
Создадим датафрейм из словаря.
|
financials = pd.DataFrame({‘month’ : [’01/01/2019′, ’01/02/2019′, ’01/03/2019′, ’01/03/2019′, ’01/04/2019′, ’01/05/2019′, ’01/06/2019′, ’01/07/2019′, ’01/08/2019′, ’01/09/2019′, ’01/10/2019′, ’01/11/2019′, ’01/12/2019′, ’01/12/2019′], ‘profit’ : [‘1.20$’, ‘1.30$’, ‘1.25$’, ‘1.25$’, ‘1.27$’, ‘1.11$’, ‘1.23$’, ‘1.20$’, ‘1.31$’, ‘1.24$’, ‘1.18$’, ‘1.17$’, ‘1.23$’, ‘1.23$’], ‘MoM’ : [0.03, —0.02, 0.01, 0.02, —0.01, —0.015, 0.017, 0.04, 0.02, 0.01, 0.00, —0.01, 2.00, 2.00], ‘high’ : [‘Dubai’, ‘Paris’, ‘singapour’, ‘singapour’, ‘moscow’, ‘Paris’, ‘Madrid’, ‘moscow’, ‘london’, ‘london’, ‘Moscow’, ‘Rome’, ‘madrid’, ‘madrid’] }) financials |
Воспользуемся методом .info() для получения общей информации о датасете.
|
<class ‘pandas.core.frame.DataFrame’> RangeIndex: 14 entries, 0 to 13 Data columns (total 4 columns): # Column Non-Null Count Dtype — —— ————— —— 0 month 14 non-null object 1 profit 14 non-null object 2 MoM 14 non-null float64 3 high 14 non-null object dtypes: float64(1), object(3) memory usage: 576.0+ bytes |
Перейдем к поиску ошибок в данных.
Дубликаты
Поиск дубликатов
Заметим, что хотя данные представлены за 12 месяцев, в датафрейме тем не менее содержится 14 значений. Это заставляет задуматься о дубликатах (duplicates) или повторяющихся значениях. Воспользуемся методом .duplicated(). На выходе мы получим логический массив, в котором повторяющееся значение обозначено как True.
|
# keep = ‘first’ (параметр по умолчанию) # помечает как дубликат (True) ВТОРОЕ повторяющееся значение financials.duplicated(keep = ‘first’) |
|
0 False 1 False 2 False 3 False 4 False 5 False 6 False 7 False 8 False 9 False 10 False 11 False 12 False 13 True dtype: bool |
|
# keep = ‘last’ соответственно считает дубликатом ПЕРВОЕ повторяющееся значение financials.duplicated(keep = ‘last’) |
|
0 False 1 False 2 False 3 False 4 False 5 False 6 False 7 False 8 False 9 False 10 False 11 False 12 True 13 False dtype: bool |
Результат метода .duplicated() можно использовать как фильтр.
|
# с параметром keep = ‘last’ будет выведено наблюдение с индексом 12 financials[financials.duplicated(keep = ‘last’)] |
Также заметим, что если смотреть по месяцам, у нас две дублирующихся записи, а не одна. В частности, повторяется запись не только за декабрь, но и за март. Проверим это с помощью параметра subset.
|
# с помощью параметра subset мы ищем дубликаты по конкретным столбцам financials.duplicated(subset = [‘month’]) |
|
0 False 1 False 2 False 3 True 4 False 5 False 6 False 7 False 8 False 9 False 10 False 11 False 12 False 13 True dtype: bool |
|
# посчитаем количество дубликатов по столбцу month financials.duplicated(subset = [‘month’]).sum() |
Создадим новый фильтр и выведем дубликаты по месяцам.
|
# укажем параметр keep = ‘last’, больше доверяя, таким образом, # последнему записанному за конкретный месяц значению financials[financials.duplicated(subset = [‘month’], keep = ‘last’)] |
Аналогичным образом мы можем посмотреть на неповторяющиеся значения.
|
( ~ financials.duplicated(subset = [‘month’])).sum() |
Этот логический массив можно также использовать как фильтр.
|
financials[ ~ financials.duplicated(subset = [‘month’], keep = ‘last’)] |
Обратите внимание, индекс остался прежним (из него просто выпали наблюдения 2 и 12). Мы исправим эту неточность при удалении дубликатов.
Удаление дубликатов
Метод .drop_duplicates() удаляет дубликаты из датафрейма и, по сути, принимает те же параметры, что и метод .duplicated().
|
# параметр ignore_index создает новый индекс financials.drop_duplicates(keep = ‘last’, subset = [‘month’], ignore_index = True, inplace = True) financials |
Неверные значения
Распространенным типом ошибок в данных являются неверные значения.
Базовый подход к поиску неверных значений — проверить, что данные не противоречат своей природе. Например, цена товара не может быть отрицательной.
В нашем случае мы видим, что в столбце MoM все строки отражают доли процента, а последняя строка — проценты. Из-за этого сильно искажается, например, средний показатель изменения выручки за год.
|
# рассчитаем среднемесячный рост financials.MoM.mean() |
С учетом имеющихся данных вряд ли среднее изменение выручки (в месячном, а не годовом выражении) составило 17,3%. Заменим проценты на доли процента.
|
# заменим 2% на 0.02 financials.iloc[11, 2] = 0.02 |
Вновь рассчитаем средний показатель.
Новое среднее значение 0,8% выглядит гораздо реалистичнее.
Форматирование значений
Тип str вместо float
Попробуем сложить данные о прибыли.
|
‘1.20$1.30$1.25$1.27$1.13$1.23$1.20$1.31$1.24$1.18$1.17$1.23$’ |
Так как столбец profit содержит тип str, произошло объединение (concatenation) строк. Преобразуем данные о прибыли в тип float.
|
# вначале удалим знак доллара с помощью метода .strip() financials[‘profit’] = financials[‘profit’].str.strip(‘$’) # затем воспользуемся знакомым нам методом .astype() financials[‘profit’] = financials[‘profit’].astype(‘float’) |
Проверим полученный результат с помощью нового для нас ключевого слова assert (по-англ. «утверждать»).
Если условие идущее после assert возвращает True, программа продолжает исполняться. В противном случае Питон выдает AssertionError.
Приведем пример.
|
# напишем простейшую функцию деления одного числа на другое def division(a, b): # если делитель равен нулю, Питон выдаст ошибку (текст ошибки указывать не обязательно) assert b != 0 , ‘На ноль делить нельзя’ return round(a / b, 2) |
|
# попробуем разделить 5 на 0 division(5, 0) |
Выражение
b != 0 превратилось в False и Питон выдал ошибку. Теперь вернемся к нашему коду.
|
# проверим превратился ли тип данных во float assert financials.profit.dtype == float |
Сообщения об ошибке не появилось, значит выражение верное (True). Теперь снова рассчитаем прибыль за год.
Названия городов с заглавной буквы
Остается сделать так, чтобы названия всех городов в столбце high начинались с заглавной буквы. Для этого подойдет метод .title().
|
financials[‘high’] = financials[‘high’].str.title() financials |
Дата и время
Как мы уже знаем, с датой и временем гораздо удобнее работать, когда они представляют собой объект datetime. В этом случае мы можем использовать все возможности Питона по анализу и прогнозированию временных рядов.
Начнем с того, что воспользуемся функцией pd.to_datetime(), которой передадим столбец month и формат, которого следует придерживаться при создании объекта datetime.
|
# запишем дату в формате datetime в столбец date1 financials[‘date1’] = pd.to_datetime(financials[‘month’], format = ‘%d/%m/%Y’) financials |
Мы получили верный результат. Как и должно быть в Pandas на первом месте в столбце date1 стоит год, затем месяц и наконец день. Теперь давайте попросим Питон самостоятельно определить формат даты.
|
# для этого подойдет параметр infer_datetime_format = True financials[‘date2’] = pd.to_datetime(financials[‘month’], infer_datetime_format = True) financials |
У нас снова получилось создать объект datetime, однако возникла одна сложность. Функция pd.to_datetime() предположила, что в столбце month данные содержатся в американском формате (месяц/день/год), тогда как у нас они записаны в европейском (день/месяц/год). Из-за этого в столбце date2 мы получили первые 12 дней января, а не 12 месяцев 2019 года.
|
# исправить неточность с месяцем можно с помощью параметра dayfirst = True financials[‘date3’] = pd.to_datetime(financials[‘month’], infer_datetime_format = True, dayfirst = True) financials |
Теперь мы снова получили верный формат.
|
# убедимся, что столбцы с датами имеют тип данных datetime financials.dtypes |
|
month object profit float64 MoM float64 high object date1 datetime64[ns] date2 datetime64[ns] date3 datetime64[ns] dtype: object |
Удалим избыточные столбцы и сделаем дату индексом.
|
financials.set_index(‘date3’, drop = True, inplace = True) # drop = True удаляет столбец date3 financials.drop(labels = [‘month’, ‘date1’, ‘date2’], axis = 1, inplace = True) financials.index.rename(‘month’, inplace = True) financials |
Посмотрим на еще один интересный инструмент. Предположим, что мы ошиблись с годом (вместо 2019 у нас на самом деле данные за 2020 год) или просто хотим создать индекс с датой с нуля. Для таких случаев подойдет функция pd.data_range().
|
# создадим последовательность из 12 месяцев, # передав начальный период (start), общее количество периодов (periods) # и день начала каждого периода (MS, т.е. month start) range = pd.date_range(start = ‘1/1/2020’, periods = 12, freq = ‘MS’) # сделаем эту последовательность индексом датафрейма financials.index = range financials |
Как мы уже знаем, когда индекс имеет тип данных datetime, мы можем делать срезы по датам.
|
# напоминаю, что для datetime конечная дата входит в срез financials[‘2020-01’: ‘2020-06’] |
Завершим раздел про дату и время построением двух подграфиков. Для этого вначале преобразуем индекс из объекта datetime обратно в строковый формат с помощью метода .strftime().
|
# будем выводить только месяцы (%B), так как все показатели у нас за 2020 год financials.index = financials.index.strftime(‘%B’) financials |
Теперь используем метод .plot() библиотеки Pandas с параметром subplots = True.
|
# построим графики для размера прибыли и изменения выручки за месяц financials[[‘profit’, ‘MoM’]].plot(subplots = True, # обозначим, что хотим несколько подграфиков layout = (1,2), # зададим сетку kind = ‘bar’, # укажем тип диаграммы rot = 65, # повернем деления шкалы оси x grid = True, # добавим сетку figsize = (16, 6), # укажем размер figure legend = False, # уберем легенду title = [‘Profit 2020’, ‘MoM Revenue Change 2020’]); # добавим заголовки |
Подведем итог
Сегодня мы рассмотрели типичные ошибки в данных и способы их исправления. В частности, мы изучили как выявить и удалить дубликаты, обнаружить неверные значения и скорректировать неподходящий формат. Кроме того, мы еще раз обратились к объекту datetime и посмотрели на возможности изменения даты и времени.
Перейдем к работе с пропущенными значениями.
При работе в Excel вы можете столкнуться с ошибкой #ЗНАЧ. В этой статье мы разберемся с несколькими ситуациями, которые приводят к этой ошибке и как ее исправить.
Ситуация 1. Формула СУММЕСЛИ на другой файл
Представим себе обычную ситуацию, когда у нас есть файл с базой данных, в которой представлены продажи за месяц и нам нужно в другом файле рассчитать сумму продаж за каждый месяц.
Когда оба файла открыты, то все работает отлично, но как только вы закроете их оба и откроете только файл, в котором вы суммировали итоги продаж, то получите ошибку #ЗНАЧ. Это связано с особенностями работы Excel, которые надо знать, и вообще есть несколько формул, которые приведут к ошибке #ЗНАЧ при ссылке на внешний файл:
- СУММЕСЛИ()
- СУММЕСЛИМН()
- СЧЁТЕСЛИ()
- СЧЁТЕСЛИМН()
- СЧИТАТЬПУСТОТЫ()
- СМЕЩ()
- ДВССЫЛ()
Какие есть пути решения ситуации? Первый: перенести базу данных в файл, где формируются итоги, тогда ссылок на внешние книги не будет и ошибка пропадет. Второй: Использовать формулы СУММ() и ЕСЛИ() совместно в массиве. Это несколько сложно для новичков, поэтому на этом способе останавливаться подробно не буду. Третий: Использовать формулу ЕСЛИОШИБКА и прописать в случае ошибки напоминание, что необходимо открыть файл с базой данных для корректной работы. Четвертый: Просто открыть файл, на который ссылается формула и ошибка пропадет 
Ситуация 2. Вычитание/сложение дат
Ошибка #ЗНАЧ может появиться при вычитании/сложении дат. Это связано с тем, что формат одной из ячеек участвующих в формуле отличен от типа Дата. Обычно есть лишние пробелы, из-за которых Excel считает что в ячейке не дата, а текст и соответственно из даты текст вычесть/сложить невозможно.
Решение проблемы следующее: Выделите ячейки с датами и замените в них пробелы на «ничего». После этого ошибка исчезнет.
Ситуация 3. Лишние пробелы в числах
При копировании данных из различных источников, может сложиться ситуация, что в числах будут лишние пробелы, из-за них арифметические операции с числами будут невозможны и приведут к ошибке #ЗНАЧ. Решение проблемы, как и в ситуации 2 — замените пробелы на «ничего» и проблема пропадет.
Есть еще несколько ситуаций, которые могут привести к рассматриваемой ошибке, но они настолько редки, что я рассматривать их в этой статье не буду.
Спасибо за внимание.
Будь то простая ошибка форматирования или более сложная проблема синтаксиса, знание того, как исправить ошибки #ЗНАЧ в Excel, важно для всех, кто хочет создавать безошибочные электронные таблицы и обеспечивать точность своих данных.
При работе с Excel часто возникают ошибки. Одной из самых неприятных ошибок является #ЗНАЧ, которая возникает, когда формула или функция не может обработать данные. Эта ошибка может быть вызвана несколькими факторами, включая неправильный синтаксис, несовпадающие ссылки на ячейки или скрытые символы. В этой статье мы рассмотрим различные причины ошибки #ЗНАЧ и дадим советы, как ее исправить.
Значение! Ошибка в Excel обычно возникает по следующим причинам:
- Неожиданный тип данных. Если функции Excel требуется определенный тип данных, например число или текст, а ячейка содержит другой тип данных.
- Неправильный синтаксис формулы. Когда аргументы функции неверны, отсутствуют или расположены в неправильном порядке.
- Пространства. Если формула ссылается на ячейку, содержащую пробел. Визуально такие ячейки выглядят абсолютно пустыми.
- Невидимые персонажи. Иногда ячейки могут содержать скрытые или непечатаемые символы, препятствующие правильному расчету формулы.
- Даты в текстовом формате. Когда даты вводятся в формате, который Excel не понимает, они обрабатываются как текстовые строки, а не как действительные даты.
- Диапазоны имеют несовместимые размеры. Когда формула ссылается на несколько диапазонов разного размера или формы, Excel не может вычислить формулу и выдает ошибку.
Как видите, ошибка #ЗНАЧ в Excel может быть вызвана множеством факторов. Поняв первопричину, вам будет легче найти правильное средство для ее устранения.
Как устранить и исправить ошибку #ЗНАЧ в Excel
Как только вы определите причину ошибки, выполните соответствующие шаги по устранению неполадок, чтобы решить проблему.
Проверьте, допустим ли тип данных
Чтобы избежать ошибки #ЗНАЧ в Excel, убедитесь, что тип данных в указанной ячейке правильный. Если для формулы или функции требуются числовые данные, убедитесь, что ячейка содержит число, а не текст.
Типичным примером являются математические операции, такие как сложение и умножение. Когда одно из суммируемых или умножаемых значений не является числовым, возникает ошибка #ЗНАЧ:
Чтобы исправить ошибку, вы можете использовать один из следующих вариантов:
- Введите недостающие числовые значения.
- Используйте функции Excel, которые автоматически игнорируют текстовые значения.
- Напишите оператор IF, который соответствует вашей бизнес-логике.
В этом примере вы можете использовать функцию ПРОДУКТ:
=ПРОИЗВЕД(B3, C3)
Если одна из указанных ячеек содержит текст, логическое значение или пуста, эта ячейка игнорируется. Результат будет таким, как если бы вы умножили другое значение на 1.
В качестве альтернативы вы можете построить оператор IF следующим образом:
= ЕСЛИ (И (ЧИСЛО (B3), ЧИСЛО (C3)), B3 * C3, 0)
Эта формула умножает две ячейки, только если оба значения являются числовыми, и возвращает ноль, если какая-либо ячейка содержит нечисловое значение. Для этого конкретного случая это имеет смысл.
Удалить пробелы и скрытые символы
В некоторых формулах ячейка с ошибочными пробелами или невидимыми символами также может вызвать ошибку #ЗНАЧ! ошибка, как показано на скриншоте ниже:
Визуально такие ячейки, как D3, B7 и C14, могут казаться совершенно пустыми. Однако они содержат один или несколько пробелов или непечатаемых символов. В Excel символ пробела считается текстом, и он потенциально может вызвать ошибку #ЗНАЧ! ошибка. На самом деле это просто еще один случай предыдущего примера, поэтому его можно исправить аналогичным образом:
- Убедитесь, что проблемные ячейки действительно пусты. Для этого выберите ячейку и нажмите клавишу Delete, чтобы удалить из нее все скрытые символы.
- Используйте функцию Excel, которая игнорирует текстовые значения, например функцию СУММ, вместо арифметической операции сложения.
Убедитесь, что указанные диапазоны совместимы
Многие функции Excel, которые принимают несколько диапазонов в своих аргументах, требуют, чтобы эти диапазоны были одного размера и формы. В противном случае формула вызывает ошибку #ЗНАЧ.
Например, функция ФИЛЬТР динамического массива приводит к ошибке #ЗНАЧ, когда аргументы включения и массива имеют несовместимые размеры. Например:
=ФИЛЬТР(A3:B20, A3:A22=»Яблоко»)
Как только ссылки на диапазон изменены соответствующим образом, ошибка исчезает:
=ФИЛЬТР(A3:B20, A3:A20=»Яблоко»)
Убедитесь, что даты не хранятся в виде текста
В Excel даты обычно хранятся в виде числовых значений. Однако некоторые даты на листе могут храниться в виде текстовых строк. Когда это произойдет, Excel вернет ошибку #ЗНАЧ! ошибка, если вы попытаетесь выполнить вычисления или операции с этими датами, так как текстовые значения нельзя складывать, вычитать или как-то еще вычислять.
Чтобы решить эту проблему, вам необходимо преобразовать даты в текстовом формате в действительные даты Excel.
Проверить синтаксис формулы
Другой возможной причиной ошибки #ЗНАЧ в Excel может быть синтаксическая ошибка в вашей формуле. Инструменты аудита формул Excel могут помочь вам выявить и исправить такие проблемы.
- Выберите ячейку с формулой, которая выдает ошибку #ЗНАЧ.
- На вкладке «Формулы» в группе «Аудит формул» нажмите «Оценить формулу» или «Проверка ошибок».
Excel будет проходить формулу по одному разделу за раз, показывая результат каждого шага. Если есть синтаксическая ошибка, Excel выделит конкретную часть формулы, вызывающую ошибку. Обнаружив синтаксическую ошибку, исправьте ее и повторно оцените формулу, чтобы убедиться, что теперь она работает должным образом.
Например, рассмотрим следующую формулу в наборе данных ниже:
=СОРТИРОВКА(ВЫБОР(A3:B20, 3))
Ошибка #VALUE возникает из-за того, что аргумент col_num (3) функции CHOOSECOLS больше, чем общее количество столбцов в указанном массиве (2).
Установка для последнего аргумента значения 2 решает проблему и возвращает желаемый результат — столбец «Итого», отсортированный от меньшего к большему:
Ошибка #ЗНАЧ в Excel XLOOKUP и VLOOKUP
Функция ВПР и ее современный преемник КСПР обычно используются в Excel для поиска и извлечения совпадающих данных. Однако при определенных обстоятельствах обе функции могут выдавать ошибку #ЗНАЧ.
Одной из распространенных причин ошибки #VALUE в XLOOKUP является несопоставимость размеров поискового и возвращаемого массивов. Например, вы не можете выполнять поиск в горизонтальном массиве и возвращать значения из вертикального массива. Кроме того, массив поиска не может быть больше или меньше возвращаемого массива. При наличии несоответствия размеров этих массивов функция XLOOKUP не сможет выполнить поиск и вернет ошибку #ЗНАЧ.
Например, приведенная ниже формула XLOOKUP возвращает ошибку #VALUE, поскольку массивы поиска и возврата содержат разное количество строк:
=XПРОСМОТР(D3, A3:A20, B3:B22)
Настройка ссылки return_array устраняет ошибку:
=XПРОСМОТР(D3, A3:A20, B3:B20)
В функции ВПР две распространенные причины ошибки #ЗНАЧ – это когда искомое значение превышает 255 символов и аргумент col_index_num меньше 1. Дополнительные сведения см. в разделе Ошибка #ЗНАЧ в функции ВПР.
Избавьтесь от ошибки #ЗНАЧ с помощью функции ЕСЛИОШИБКА
Чтобы устранить ошибку #ЗНАЧ на листах Excel, вы можете использовать функцию ЕСЛИОШИБКА в Excel 2007–365 или комбинацию ЕСЛИ ОШИБКА в более ранних версиях.
Предположим, вы используете формулу DATEDIF, чтобы найти разницу между датами в B3 и C3:
=РАЗНДАТ(B3, C3, «г»)
Если одна или обе даты неверны, формула выдаст ошибку #ЗНАЧ. Чтобы это исправить, оберните основную формулу функцией ЕСЛИОШИБКА следующим образом:
=ЕСЛИОШИБКА(РАЗНДАТ(B3, C3, «d»), «Недопустимая дата!»)
Если Excel не распознает указанное значение ячейки как дату, функция ЕСЛИОШИБКА явно укажет на это.
Кончик. Чтобы быстро найти все ошибки #VALUE на листе, вы можете использовать функцию «Перейти к специальному» или диалоговое окно «Найти и заменить». Мы подробно обсудили эти параметры при обнаружении ошибок #NAME. Для ошибок #VALUE действия практически такие же.
Вот как можно обнаружить и исправить ошибку #ЗНАЧ в Excel. Поняв причины и используя соответствующие методы устранения неполадок, вы сможете быстро вернуть свою электронную таблицу в нужное русло.
Практическая рабочая тетрадь для скачивания
Ошибка #ЗНАЧ в Excel — примеры (файл .xlsx)