Исправление: “Ошибка разбора формулы” с примерами в Google Sheets?

С ошибкой разбора в Google Sheets довольно часто сталкиваются как новички, так и опытные профессионалы. Это способ Sheets сообщить вам, что с вашей формулой что-то не так, и Sheet не может обработать инструкции, данные в формуле. Эти ошибки разбора могут быть довольно неприятными, поскольку вы ожидаете вычисленного результата, но вас «приветствует» ошибка разбора, особенно если ошибка возникает в длинной формуле и причина ошибки разбора не очевидна.

Когда Sheets показывает ошибку разбора, он просто говорит вам, что нужно исправить формулу, аргументы, типы данных или параметры. Ошибка разбора формулы — это не единственная ошибка, под ее капотом скрывается множество других ошибок, таких как #N/A error, #error и т.д. Ошибка разбора не показывает непосредственно неправильную формулу, она показывает ошибку (например, #error), но когда вы нажимаете на ошибку, то в боковом выпадающем списке появляется надпись «Ошибка разбора формулы», как показано на изображении ниже #Ошибка! Происходит в ячейке D11, но при нажатии на нее появляется ошибка разбора формулы.

В Google Sheets нет компилятора (обычно в компьютерном мире это связано с ошибкой разбора). Когда формула вводится в Google Sheet, Sheets разбирает синтаксис формулы, чтобы проанализировать, классифицировать и понять синтаксис с помощью функции синтаксического разбора. Процесс синтаксического разбора состоит из разбора текста и преобразования текста в лексемы.

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

Общие причины ошибки разбора в Google Sheet

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

Типы ошибок разбора формул в листе Google

Ниже перечислены наиболее распространенные типы ошибок разбора в Google Sheets.

Исправления для ошибки разбора в Google Sheet

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

1. ошибка #N/A

Эта ошибка происходит от фразы «Не доступно». В основном она возникает в Lookup, HLookup, ImportXML или подобных функциях, которые находят определенное значение в заданном диапазоне. Если значение недоступно в заданном диапазоне, то это приведет к ошибке #N/A в Google Sheet. Разъясним понятие на примере.

1. Посмотрите на лист Google на изображении ниже. Имеет данные в ячейках B3 — B6, тогда как ошибка N/A отображается в ячейке D3.
2. Затем посмотрите на ячейку D3 и вы увидите следующее Формула Vlookup:

   =VLOOKUP("Киви",B3:B6,1,0)

   

3. Теперь внимательно посмотрите на формулу, и вы заметите, что в ней поиск для Киви в списке «Фрукты», но что в списке нет киви, таким образом, ошибка N/A.
4. Затем вы можете удалить ошибку #N/A либо путем добавление киви к списку или изменение формулы искать другое значение как яблоки, как показано на рисунке ниже:
   

   

Вы можете использовать аналогичный подход для устранения ошибок #N/A на листе Google.

2. #Div/0 Ошибка

Деление на ноль обозначается как #Div/O в Google Sheets. Если какой-либо шаг в вашей формуле делит значение на ноль или пустую ячейку, то это приведет к ошибке #Dive/0. Давайте проясним это на следующем примере:

1. A ошибка #/Div/0 в ячейке D3 в приведенном ниже листе, который имеет 3 колонки: Числа, Деленное на и Результат:
   

   

2. Поскольку формула в D3 подразумевает, что значение в B3 (то есть 25) должно быть деление на значение в C3 (то есть ноль), поэтому формула запрашивает Google Sheets, чтобы выполнить 25/0, что математически невозможно, поэтому #Ошибка делителя.
   

   

3. Теперь эта ошибка может быть устранена с помощью удаление нуля из делителя (здесь ячейка C3) или, если это невозможно, то либо оставить формула как она есть (если не используется в другом вычислении) или маскировать результат с помощью IFERROR функция.
4. В приведенном примере давайте замаскируем #Div/0 как неправильное деление с помощью IFERROR. The общий синтаксис из IFERROR как показано ниже:

   =IFERROR(value, (value_if_error))

5. В нашем примере формула будет:

   =IFERROR("Неправильное деление",(B3/C3))

   

6. Теперь вы видите, что результат в ячейке D3 изменился на Неправильное деление.

3. #Ошибка в значении

Вы можете столкнуться с ошибкой #value на листе Google, если тип данных хотя бы одной ячейки не соответствует тому, что требуется для вычислений по определенной формуле. Другими словами, лист Google может показать ошибку #value, если вы попытаетесь вычислить один тип данных (например, число) из двух разных типов входных данных (например, число и текстовая строка). Разъясним это на примере.

1. Посмотрите на лист, изображенный на рисунке ниже, и вы заметите ошибка #значение ячейка D3, хотя другие ячейки’ рассчитаны правильно.
   

   

2. Затем внимательно проверьте формулу, и вы заметите, что ячейка D3 является результатом операции сложение значения в ячейке B3 (то есть 25) к значению ячейки C3 (то есть ноль).
   

   

3. Но ноль является не число, а текстовая строка, таким образом, Google Sheets не может сложить строку с числом (разные типы данных) и показывает ошибку #значение.
4. Теперь, либо вы изменить формулу или измените значение в ячейке C3 от нуля (текстовая строка) до 0 (числовое значение), как показано ниже:
   

   

4. #Имя Ошибка

Лист Google может показать ошибку #name, если имя функции написано неправильно, кавычки не присутствуют в синтаксисе формулы (если требуется), или имя ячейки/диапазона неверно. На нашем сайте есть очень подробная статья об ошибках #name, не забудьте ознакомиться с ней.

1. Обратитесь к листу, изображенному на рисунке ниже, и вы заметите ошибка #имя в ячейке D3.
   

   

2. The D3 ячейка объединяет значения B3 и C3.
3. Наши ссылки на ячейки (B3 и C3) действительны и не содержат опечаток, теперь внимательно посмотрите на формулу в D3, вы заметите, что формула имеет вид:

   =CONCATT(B3,C3)

   

4. В то время как КОНКАТТ (дополнительная буква T добавлена к правильному CONCAT) это недопустимая формула, в которой она должна была быть:

   =CONCAT(B3,C3)

5. Теперь посмотрите на изображение ниже, где ошибка #name убрана после исправления формулы CONCAT.
   

   

Давайте рассмотрим еще один пример, чтобы прояснить идею об ошибке #name из-за значений.

1. Обратитесь к листу на изображении ниже и вы заметите ошибку #имя ошибка в ячейке B3.
2. Теперь внимательно посмотрите на формулу и не все выглядит нормально? Написание функции CONCAT верно, лимон и сок также верны. Тогда что вызывает ошибку #name?
   

   

3. Лимон и сок это текстовые строки и в соответствии с синтаксисом Google Sheets это должно быть обернутый в двойные кавычки, Как видно на изображении ниже, после добавления кавычек вокруг лимона и сока ошибка #name исчезла из ячейки B3.
   

   

5. Ошибка #Num

Вы можете столкнуться с ошибкой #num на листе Google, если результат вычисления больше, чем максимальная емкость отображения Google Sheets i.e., 1.79769e+308. Например, если мы умножим пятьдесят пять миллиардов на четырнадцать миллиардов в ячейке Google Sheet, то это вызовет ошибку #num, поскольку Google Sheets не может отобразить такое большое число. Другая причина этой ошибки заключается в том, что вводимый тип числа не соответствует требуемому типу типа числа. Давайте обсудим это на примере:

1. Обратитесь к листу, изображенному на рисунке ниже, и вы заметите #num ошибка в C7.
   

   

2. Теперь проверьте формулу, и вы заметите, что столбец C это квадратный корень столбца B.
3. Затем проверьте ячейку B7 и вы увидите, что это отрицательное число но в базовой математике квадратный корень типа положительное число может быть вычислено только, поэтому Google Sheets выдает ошибку #name.
   

   

4. Вы можете исправить это либо изменив значение (для преобразования числа в положительное можно использовать функцию ABS), формула, или скрытие результат с помощью IFERROR (как обсуждалось ранее).

6. “#Ошибка”

Если Google Sheet не может понять определенную формулу, но не может указать причину ошибки (как в других ошибках, где мы получаем подсказку, например в #num error, мы знаем, что проблема связана с числами), то это может привести к “#error” ошибке. Поскольку причина ошибки не указана, она носит более общий характер, или можно сказать, что если Google Sheet не может связать ошибку с какими-либо другими типами ошибок разбора, то он покажет “#error” ошибку. Она может быть результатом отсутствия символов, таких как запятые, апострофы, значения и параметры. Давайте разберемся в этом на следующем примере:

1. Посмотрите на лист на изображении ниже и вы заметите “#error ошибка” в ячейке D11.

1. Теперь внимательно посмотрите на формула в ячейке D11 и вы увидите, что она выглядит следующим образом (поскольку мы пытаемся суммировать суммы):

   = "Итого "SUM(B3:B10)

   

2. Но общая сумма не допустимая функция и нам понадобится только функция Sum для суммирования сумм, например:

   =SUM(B3:B10)

3. Теперь проверьте лист ниже после внесения вышеуказанной поправки, которая устраняет ошибку #error:
   

   

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

1. Убедитесь, что открытие и закрывающие круглые скобки в формуле в соответствии с требуемой суммой.
2. Если специальные символы такие как двоеточия, точки с запятой, запятые и апострофы являются размещённые неправильно (если это требуется формулой).
3. Если данные содержат доллар или знаки процента, убедитесь, что они не часть из ваших формула. Убедитесь, что введенные как обычные числа. Если вам необходимо использовать эти знаки, то форматируйте результаты как валюту (например, доллар) или проценты, а не как вводные данные.

7. Ошибка #Ref

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

• Ссылки на удаленные ячейки
• Круговая зависимость
• Ссылка на ячейку вне диапазона данных

Ошибка #Ref из-за удаленных ссылок на ячейки

Если формула ссылается на диапазон ячеек, но этот диапазон ячеек удален, то это приведет к ошибке #ref в ячейке формулы. Давайте рассмотрим пример на эту тему:

1. Обратитесь к изображению ниже и вы увидите Сумма столбец установлен в ячейках D3 — D7 то есть добавление столбцов B и C.
   

   

2. Теперь мы удалить столбец C и это вызовет #реф ошибка в столбце D, поскольку столбец C удаляется, что является частью формулы, таким образом #ref error.
   

   

3. Здесь либо удаление столбца C или измените формулу чтобы удалить ссылки на удаленные ячейки.

#Ref Error Due to Circular Dependency (Ошибка из-за круговой зависимости)

Если ячейка формулы ссылается на себя как на диапазон ввода, то это приведет к ошибке #ref из-за циклической зависимости. Давайте поясним это понятие на следующем примере:

1. Обратитесь к листу, изображенному на рисунке ниже, и вы заметите ошибка #ref в ячейке B11.
   

   

2. Теперь посмотрите на формула в ячейке B11:

   =SUM(B2:B11)

   

3. Затем вы заметите, что в ячейке B11 ячейка также ссылается на себя в диапазон и также является входной ячейкой для самой себя, поэтому ошибка #ref из-за круговой зависимости.
4. В данном случае, редактировать формула для удаления ячейки из диапазона ссылок, которая убирает ошибку #ref из B11:

   =SUM(B2:B10)

   

#Ошибка из-за выхода ячейки за пределы диапазона данных

Если вы используете функцию (например, VLOOKUP) для поиска/извлечения записи в выбранном диапазоне ячеек, но указанная ссылка на ячейку находится за пределами выбранного диапазона, то возникает ошибка #REF! ошибка из-за того, что ссылка на ячейку находится вне диапазона данных. Давайте обсудим это на примере:

1. Обратитесь к листу, показанному на рисунке ниже, и вы заметите #ref ошибка в ячейке F4.
   

   

2. Теперь посмотрите на формулу, и вы увидите, что она ссылается на ячейку 3^rd столбец в диапазоне (B3 — C7), в то время как диапазон имеет только два столбца (B и C), таким образом #ref error из-за ссылки на ячейку вне диапазона данных.
   

   

3. Затем редактировать формула для использования 2^nd (столбец цены) и, таким образом, это устраняет ошибку #ref.
   

   

8. Всплывающее окно «Возникла проблема

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

1. Обратитесь к листу, показанному на рисунке ниже, и вы заметите, что Всплывающее окно «Возникла проблема отображается при добавлении формулы суммы в ячейку B11:
   

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

   

3. Тогда удалите / из формулы, и это устранит ошибку:
   

   

9. Ошибка #Null

Эта ошибка в основном возникает в Excel и если вы скопируете данные из листа Excel в лист Google, то это может привести к ошибке #null. Если лист Excel загружается в Google Sheets, то эти данные могут показать следующее
#error” ошибку, а не ошибку #null. Тогда вы либо убираете ошибку #null в Excel, либо убираете ошибку “#error” в Google Sheets (обсуждалось ранее).

Функции для работы с ошибками на большом листе Google

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

Функция ISNA

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

=ISNA(значение)

ISERR Function (Функция ISERR)

Если вас интересуют все остальные ошибки в диапазоне, кроме ошибки #N/A, то эта функция выведет список всех таких ошибок. Ниже приведен синтаксис этой функции:

=ISERR(значение)

Функция ТИП ОШИБКИ

Эта функция Google Sheets перечисляет все ошибки на листе в числах. Она имеет следующий синтаксис:

= ОШИБКА.TYPE(value)

Обнаруженные ошибки и соответствующие им номера выглядят следующим образом:

#NULL!=1

#DIV/0!=2

#Значение=3

#Ref=4

#NAME?=5

#NUM!=6

#N/A!=7

Все остальные случайные ошибки на листе Google = 8

Если функция Error

Если ошибку разбора невозможно исправить в силу обстоятельств, то ее можно скрыть с помощью функции IFERROR, если при этом не нарушаются другие вычисления. Пожалуйста, используйте эту функцию в крайнем случае, потому что она может вызвать непредвиденные проблемы в будущем. Вы можете обратиться к разделу «Ошибка #Div/0», чтобы понять процесс.

Лучшие практики, чтобы избежать ошибки разбора

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

1. Убедитесь, что не использовать символы как % или $ в формуле.
2. Знаки препинания в формуле изменяются в соответствии с вашим регионом и языком в Google Sheet, поэтому, если вы столкнулись с ошибкой разбора в Google Sheet, вы можете поменять запятые на точки с запятой или наоборот, чтобы убрать ошибку. В некоторых регионах, возможно, придется использовать \ вместо запятых или точек с запятой.
3. Помните, что вы должны написать формулу в Google Sheets в Английский, даже если вы используете Google Таблицы на неанглийском языке, например, французском.
4. Убедитесь, что Локаль в настройках электронных таблиц Google Sheets и Временная зона устанавливаются на то же место как Соединенные Штаты, а не как Локаль установить на Соединенные Штаты, и часовой пояс установленный на Москва.
   

   

5. Если на листе Google возникла ошибка разбора, не забудьте проверить, не размещение кавычек вокруг текста, ссылок, источников изображений и т. д. Также следите за тем, когда следует использовать одиночные кавычки и когда использовать двойные кавычки.
6. При обращении к ячейка в другой лист в формуле, убедитесь, что выберите нужную ячейку на этом листе, а не вводить его, так как иногда он может выдать ошибку разбора.
7. Обратите внимание, что когда знак плюс и запятая используются в формуле (это может произойти при работе с телефонными номерами), как, например, в следующем случае, это приведет к ошибке разбора на листе Google.

   +123,456 // Это приведет к ошибке
   
   +123456 // Это не приведет к ошибке

8. При копировании или ссылке на целые столбцы или строки на листе с другого листа, всегда начинайте с 1^st столбец или строка, иначе, несовпадение строки и столбцы между исходным и конечным листами приведут к ошибке ошибка разбора.
9. И последнее, но не менее важное, здесь ссылка на лист Google (без каких-либо макросов, надстроек и т.д.). но вы должны скопировать лист в свой Google Sheets). Это автоматизированный инструмент, созданный как Оценить парсер формул (в Google Sheets его нет, в то время как в Excel он есть). Этот лист может быть использован для оцените формулу, которая показывает ошибку разбора. Вы должны использовать этот лист на свой страх и риск, и мы не несем ответственности за любые проблемы, вызванные этим листом.

Надеемся, нам удалось устранить ошибки разбора в вашем листе Google. Если у вас есть какие-либо вопросы или предложения, мы будем рады видеть вас в разделе комментариев.