Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Как исправить ошибку синтаксического анализа в Google Таблицах
Это может раздражать, особенно если формула длинная и проблема синтаксического анализа не очевидна.
Не волнуйтесь! Мы научим вас определять возможные причины проблемы парсинга и как их исправить!
Вот пять наиболее распространенных ошибок синтаксического анализа формул в Google Таблицах, с которыми вы можете столкнуться:
Устранение ошибки # N/A в Google Таблицах
Когда появляется ошибка #N/A, это означает, что значение недоступно. Эта ошибка часто встречается при использовании функции VLOOKUP (ВПР), поскольку ключ поиска не может быть найден.
Однако в этом сценарии это не означает, что введенная нами формула неверна. Когда формула возвращает ошибку #N/A, это означает только то, что указанный ключ поиска не находится в выбранном диапазоне.
Давайте воспользуемся примером, чтобы улучшить визуализацию.
Следовательно, это приведет к тому, что формула вернет ошибку # N / A, означающую, что введенный нами ключ поиска не может быть найден.
# DIV/0! — Ошибка в Google Таблицах
# DIV / 0! ошибка появляется, когда формула делит число с нулевым значением. Это может произойти, когда знаменатель равен нулю. С математической точки зрения это не имеет смысла, поэтому формула возвращает # DIV / 0! ошибка.
Эта ошибка также может появиться, когда знаменатель пуст.
Как видите, поскольку B1 не имеет значения, формула не может разделить 40 на ноль.
Вы также часто можете увидеть это при использовании функции AVERAGE (СРЕДНИЙ). Ошибка появится, если диапазон, выбранный для формулы, пуст.
Просто убедитесь, что используемые или выбранные знаменатели имеют значение, и эта ошибка синтаксического анализа больше не появится!
#VALUE! — Ошибка в Google Таблицах
Если один или несколько параметров в вашей формуле имеют другой тип, чем ожидалось, вы получите эту ошибку. Итак, если функция принимает в качестве аргумента только числа, но выбранная ячейка имеет текстовое значение, вы получите #VALUE! ошибка.
Пробелы в ячейках также могут вызывать эту ошибку.
Несмотря на то, что A2 выглядит как пустое поле, мы ввели пробел внутри ячейки. Это привело к тому, что формула вернула #VALUE! ошибка.
Вот еще один пример:
Здесь мы видим, что введенная формула умножает числовое значение на текстовое значение, равное « пяти ». Эта формула не имеет математического смысла, так как параметры в формуле бывают разных типов.
Чтобы исправить эту ошибку, убедитесь, что выбранные ячейки содержат параметр одного и того же типа. При выполнении математической операции всегда не забывайте использовать только числовые значения.
Другой сценарий, в котором может возникнуть эта ошибка, — это смешивание формата дат в формуле.
Формат даты в США: ММ / ДД / ГГГ
Остальной мир: ДД / ММ / ГГГГ
Как видите, при вычитании двух дат в Google Таблицах можно было читать только 25/12/2021 как дату, поскольку это числовое значение. В Google Таблицах 25/11/2021 читается как текст, поэтому формула возвращает #VALUE! ошибка.
Чтобы исправить это, просто убедитесь, что даты, введенные в ячейки, имеют одинаковый формат.
#REF! — Ошибка в Google Таблицах
Если у вас недействительная ссылка, #REF! возникает ошибка. Наиболее распространены ситуации, когда выбранная ячейка отсутствует или формула ссылается на себя.
Отсутствует ссылка:
Это часто происходит, когда исходная выбранная ячейка была удалена (когда вы удаляете всю строку или столбец).
Другой сценарий — когда мы копируем формулу с выбранным диапазоном в угол ваших таблиц Google.
Возможно, что при копировании и вставке относительный диапазон смещается за пределы листа, что недопустимо и приведет к #REF! ошибка.
Круговая зависимость:
Когда введенная формула ссылается на себя, это называется циклической зависимостью. Это происходит, когда мы выбрали диапазон, который также состоит из самой формулы.
Как видите, формула содержит набор ячеек, в который входит сама формула.
Просто убедитесь, что при выборе ячеек для ввода всегда исключайте формулу, чтобы избежать появления таких ошибок.
#NAME? — Ошибка в Google Таблицах
#NAME? ошибка появляется, когда синтаксис введенной формулы имеет проблемы. Чаще всего это когда само имя функции написано с ошибкой.
Ошибка также может появиться, когда именованный диапазон не существует.
Другой сценарий — когда во введенном текстовом значении отсутствуют кавычки. Это также приведет к появлению #NAME? ошибка появится.
Если все сделано правильно, ошибка не появится.
Вот так! Узнав об этих пяти наиболее часто встречающихся ошибках синтаксического анализа формул, не нужно беспокоиться об их появлении. Всегда убедитесь, что понимаете, на что указывает ошибка, и исправляйте проблемы в формуле соответствующим образом.
Ошибки в формулах Google Таблиц
Благодарим Михаила Смирнова за помощь в подготовке статьи
А-а-а! Формула не работает! Что делать (кроме как сходить к коллеге, у которого б ольший опыт в Таблицах)? Давайте рассмотрим, какие ошибки существуют в Таблицах и что можно сделать, чтобы разобраться с ошибкой в формулах.
Типы ошибок
#ИМЯ! / #NAME! — ошибка в имени функции, именованном диапазоне, ссылке на диапазон. Пробегитесь по всем этим пунктам в вашей формуле. Кроме того, не забывайте, что текстовые значения указываются внутри формул в кавычках.
Помните, что ошибки в формулах могут быть и в том случае, если эти ошибки есть в ячейках, на которые формулы ссылаются. Надо разматывать всю цепочку. На следующем скриншоте в формуле нет ничего криминального: к ячейке A4 прибавляем число 10. Но в ячейке A4 ошибка #ИМЯ? — она и отображается в результате расчета новой формулы:
Еще эта ошибка может всплывать (редко — можно не беспокоиться) при использовании недокументированных функций:
#ЗНАЧ! / #VALUE! — неправильные значения аргументов (например, в функции СМЕЩ / OFFSET высота или ширина диапазона задана как ноль, что невозможно) или же арифметические операции с разными типами данных — сложение текста и чисел. А еще бывает, если дата задана как текстовое значение. Ошибки в запросе функции QUERY тоже будут выглядеть так.
Ещё две причины — из-за ограничения на длину текстовой строки.
Нельзя, чтоб получались строки длиннее 50 000 символов:
А для функции ПОВТОР / REPT ограничение — 32 000:
#ССЫЛ! / #REF — ссылка на несуществующий диапазон. Появляется, когда ячейка, на которую ссылалась формула, была удалена (вместе со строкой или столбцом, например), или когда вы пытаетесь, протягивая формулу, сослаться на ячейку A0, например (за пределами листа).
Циклическая ссылка (выглядит как #ССЫЛ! / #REF!). Возникает, если формула ссылается на собственное значение. При возникновении смотрите на диапазоны в формуле (бывает, что вы ссылаетесь на весь столбец, а формула стоит в нем же под таблицей, и т.д.)
Еще одна частая причина, которую бывает непросто побороть: несоответствие размеров диапазонов, которые надо соединить.
Ещё она возникает, если импортировать несуществующую таблицу:
Или не открыть / не иметь доступа на импорт:
#ЧИСЛО! / #NUM! — ошибка с параметром функции. Как правило, возникает, когда вводится (или формируется в ходе промежуточных расчетов) отрицательное число там, где ожидается положительное.
#Н/Д / #N/A — значение не найдено. Обычно случается с ВПР, ПОИСКПОЗ. Либо значения действительно нет в таблице и тогда это “нормальная” ошибка, либо оно введено по-разному в исходной таблице и в таблице, откуда оно берется для поиска.
#ДЕЛ/0! / #DIV/0! — деление на ноль. Вроде бы понятно — надо смотреть, на что делим, чтобы понять, откуда взялся ноль. Но также случается и с функцией СРЗНАЧЕСЛИ(МН) / COUNTIF(S) — допустим, по вашим критериям не найдено ни одного условия — а в логике расчета среднего арифметического есть деление, и в такой ситуации деление будет именно на ноль (найденных по критериям значений).
Синтаксическая ошибка и другие виды ошибок (тип #ОШИБКА! / #ERROR! — в Excel, например, такого вообще нет, в отличие от остальных перечисленных). Может возникнуть просто из-за случайно введенного символа, который Таблицы не смогут интерпретировать (точка с запятой перед формулой или точка после, например). Еще зачастую это могут быть незакрытые фигурные скобки массива. Забытый амперсанд между соединяемыми текстовыми строками тоже вызовет такую ошибку.
Еще одна причина: слишком большой диапазон попытались передать в пользовательскую функцию (Этот случай обсуждался в нашем чате).
Некоторые типовые ошибки в формулах
Системная ошибка (из перечисленных выше) — это еще ладно, во всяком случае Таблицы вам о ней сигнализируют. Бывают в сложных формулах и не такие явные ошибки. В результате расчета нет ошибок, но есть ноль или неправдоподобное / явно ошибочное число / текст / синтаксическая ошибка. Как искать причины, на что обращать внимание?
Функции ВПР / VLOOKUP, ПОИСКПОЗ / MATCH — если есть сомнения, что функция тянет все корректно, проверяйте, точно ли вы указали последний аргумент как ЛОЖЬ ( ноль, 0)? Если нет, будет по умолчанию 1, ИСТИНА (интервальный, а не точный поиск). И в случае с поиском текстовых значений возникнут ошибки.
Сравнение данных, поиск данных (те же ВПР и прочие) — если у вас ошибка Н/Д, может быть проблема в разных форматах данных. Например, в исходнике, который вы импортируете, артикулы текстового формата, а у вас в вашей таблице числового. Проверить можно с помощью функций ISTEXT / ЕТЕКСТ, ISNUMBER / ЕЧИСЛО.
Кроме того, всегда есть риск банальных ошибок ввода данных — лишние пробелы, перепутанные кириллица/латиница, сокращения. Для предотвращения таких ошибок используйте проверку данных. А для отлавливания — функцию UNIQUE (выводите список уникальных значений и смотрите, есть ли там разные варианты написания одного и того же значения).
Если вдруг ВПР или другая функция в упор не находит значение, а визуально они кажутся одинаковыми, не забывайте, что всегда можно сравнить две ячейки формулой (=A1=A2) или посмотреть, одинаковой ли они длины по количеству символов (с помощью функции ДЛСТР / LEN).
Убрать лишние пробелы (до и после текстовой строки и все, что свыше одного пробела между слов) помогает функция СЖПРОБЕЛЫ / TRIM.
Забыли протянуть формулу / изменить диапазон. Это классика 🙂 По возможности используйте формулу массива с открытым диапазоном. Так вы настроите ее раз и навсегда и не нужно будет беспокоиться о появлении новых столбцов. Допустим, вы пишете формулу, которая будет отправлять адресату по его емейлу из текущей строки письмо:
Вместо такой формулы сделайте ее по открытому диапазону E2:E, и она будет работать бесконечно для любого количества строк. Чтобы в пустых строках ничего не отображалось, добавьте проверку на пустоту ячейки с емейлом (с помощью ЕСЛИ / IF):
Проблемы со сложностью формулы. Иногда полностью правильно написанная формула не вернёт ничего или вернёт неправильное значение — к сожалению, мы иногда сталкивались с такой проблемой.
Такое случается, когда:
– вы ссылаетесь на большое количество строк с данными;
– и при этом написали слишком сложную формулу.
Что такое сложная формула для Таблиц? К примеру, функция FILTER, в условии которой – отбор большим регулярным выражением, которое сформировано из значений, которые вы внутри этой же формулы отбираете из другого большого листа, потом функцию заворачиваете в QUERY, делаете группировку и всё это безобразие вдобавок протягиваете на каждую строку.
Подобное можно провернуть только на небольших диапазонах с данными, на больших – может не работать.
Поэтому не забывайте, что сложность лимитирована, за ней нужно следить и не все, даже абсолютно правильно написанные формулы Таблица сможет переварить.
Что поможет написать/разобрать/починить сложную формулу
Принцип луковицы
Так этот подход назвал эксперт по Таблицам Бен Коллинз. Идея в следующем: вместо того, чтобы с ходу прописывать сложную формулу, сначала прописываем часть, смотрим, как она работает и правильно ли вычисляет промежуточное значение, затем ссылаемся на эту формулу из другой формулы, в которой она будет выступать одним из аргументов, и если все заработает, удаляем столбец с промежуточной формулой, а ее переносим в итоговую формулу (на то место, где мы на нее ссылались).
Иначе говоря, формируем отдельные части формулы в отдельных ячейках, ссылаясь из следующей на предыдущий этап, а потом уже соединяем в одну.
В приведенном выше примере с гиперссылкой можно сначала сформировать текстовую формулу, которая будет соединять mailto и адрес е-почты из ячейки, и сослаться на ячейку с этой формулой из ГИПЕРССЫЛКИ. И если все заработает, уже потом скопировать первую формулу и вставить вместо ссылки на ячейку с ней.
Переход на следующую строку в формуле
В строке формул можно переходить на следующую строку с помощью Alt+Enter. Это позволяет визуально разделить отдельные фрагменты/функции — тогда формулу будет проще воспринимать (вашим коллегам и вам самим в будущем, когда вы уже забудете ее логику).
Эта история может помочь, если у вас уже лютая многоэтажная формула, а в ней возникает синтаксическая ошибка.
Выделение фрагмента формулы
Напоминаем: выделяете ячейку в формуле — видите всплывающую подсказку с ее значением. Выделяете фрагмент формулы или одну функцию в рамках формулы — видите вычисляемое значение этого фрагмента / функции. Помогает понять, что на каких шагах вычисляется для конкретных данных.
Функция ЕСЛИОШИБКА / IFERROR и другие для “отлавливания” ошибок
Функция ЕСЛИОШИБКА / IFERROR позволяет отображать любое заданное вами значение (ее второй аргумент) вместо ошибки в формуле (которая указывается в первом аргументе). Это удобно — например, если значение не найдено, то вы показываете не #Н/Д, а пусто; если в сравнении с прошлым годом в конкретной строке возникает #ДЕЛ/0, так как по конкретному продукту не было продаж, а мы на них делим — то мы тоже показываем пустоту (по умолчанию, если будет пропущен второй аргумент, ЕСЛИОШИБКА выведет пустоту) или ноль (тогда придется указать его во втором аргументе) вместо ошибки.
Если хотите «отлавливать» только #Н/Д (например, при применении ВПР’а) — используйте IFNA — у нее такой же синтаксис, но среагирует она только на ненайденное ВПР-ом значение (и вернет вместо ошибки #Н/Д пустоту либо явно указанное во втором аргументе значение), а остальные ошибки (допустим, если у вас будет что-то с синтаксисом) отобразит.
Помимо этих двух функций, которые оставляют первый аргумент на месте, если ошибок нет, и заменяют вторым, если они есть, существует еще несколько функций для поиска специфичных ошибок.
Функция ТИП.ОШИБКИ / ERROR.TYPE будет выдавать код ошибки (смотрим в справку):
1 для ошибки #NULL! (такой ошибки пока на практике нет, тот же Бен Коллинз пишет, что ни разу с ней не сталкивался и просил подписчиков прислать примеры, но никто пока не прислал)
Соответственно, вы можете проверять формулу только на определенный тип ошибок.
Кроме того, есть функция ЕНД / ISNA для проверки конкретной ошибки Н/Д — только для нее будет возвращать ИСТИНА. Отличается от IFNA тем, что выводит ИСТИНА / ЛОЖЬ — это индикатор, она в любом случае не будет возвращать исходную формулу, даже если в ней нет ошибки.
Функция ЕОШ / ISERR обратная — возвращает ИСТИНА для всех типов ошибок, кроме Н/Д.
А ЕОШИБКА / ISERROR возвращает ИСТИНА для любого типа ошибок.
Ошибка синтаксического анализа формулы листов Google — как ее исправить
Анализ, категоризация и понимание синтаксиса могут быть разбиты и разделены путем выполнения функции синтаксического анализа. Процесс синтаксического анализа состоит из анализа текстового анализа, где текст состоит из последовательности токенов, определяющих его грамматическую структуру. Затем анализатор создаст структуру на основе полученных данных. Чтобы упростить синтаксический анализ, нужно просто взять большую структуру и разделить ее на отдельные компоненты для упрощения хранения или манипулирования.
Порой нередко запускать анализ и заканчивать с ошибкой. Когда происходит что-то подобное, вам выдается сгенерированная ошибка синтаксического анализа, чтобы предупредить вас, что что-то может быть не так с вашей формулой. Есть несколько разных причин для разбора, чтобы произвести ошибку. Ошибка разбора может произойти по любой из следующих причин:
Разбор ошибок в формулах электронных таблиц, таких как Google Sheets
Если формула электронной таблицы не была правильно отформатирована, то возможно, что она может привести к ошибке разбора. Это часто имеет место, если формула содержит дополнительные специальные символы или если формула была написана неправильно. Как правило, любая ошибка в синтаксисе с формулой приводит к возникновению ошибки разбора.
Чтобы избежать #ERROR! сообщение, вы хотите убедиться, что формула написана правильно с первого раза. Убедитесь, что вы проверили формулу на наличие любых неточностей, которые могут появиться, и вызвать формулу для создания ошибки. #ОШИБКА! сообщение специально уникально для Google Sheets и означает, что оно не может понять введенную формулу и, следовательно, не может выполнить команду для анализа данных.
Это также относится к случаю, если вы пропустили «» при сопоставлении текста и числовых значений.
Что формула должна читаться как: = «Общая» сумма (A1: A6), которая отображается как Total21 (числа в A1-A6 сложены вместе).
Убедитесь, что вы используете правильный синтаксис объединения, чтобы связать воедино текстовые и числовые значения.
Кроме того, можно получить ошибку разбора при попытке закрыть скобки в формуле. Одна скобка, слишком много или слишком мало, может вызвать осложнения при разборе данных.
Убедитесь, что используется правильное количество скобок и что они также размещены правильно.
В Google Sheets существует множество различных функций, которые могут генерировать ошибку разбора:
Просто назвать несколько. Избегайте осложнений и просмотрите ваши формулы перед анализом данных и исправьте все возможные ошибки.
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Использование функции ЕСЛИОШИБКА (IFERROR) в Google Таблицах с примерами
Если вы работаете с формулами в Google Таблицах, вы знаете, что ошибки могут всплывать в любой момент. Хотя получение ошибок является частью работы с формулами в Google Таблицах, важно знать, как правильно обрабатывать эти ошибки. В этом руководстве я покажу вам, как обрабатывать ошибки в Google Таблицах с помощью функции IFERROR (ЕСЛИОШИБКА).
Прежде чем мы перейдем к самой формуле, давайте быстро узнаем о различных ошибках, с которыми вы можете столкнуться в Google Таблицах, и о возможных причинах этих ошибок.
Различные типы ошибок в Google Таблицах
Вот различные ошибки, с которыми вы можете столкнуться при работе с Google Таблицами:
#DIV/0! Error
Вы, вероятно, увидите эту ошибку, когда число делится на 0. Это называется ошибкой деления. Если навести указатель мыши на ячейку с этой ошибкой, отобразится сообщение «Параметр 2 функции DIVIDE не может быть равен нулю».
#N/A Error
Это называется ошибкой «недоступно», и вы увидите это, когда используете формулу поиска, и она не может найти значение (следовательно, «Недоступно»).
Ниже приведен пример, в котором формула VLOOKUP используется, но возвращает ошибку, если не может найти значение подстановки в массиве таблицы.
#REF! Error
Это называется ошибкой ссылки, и вы увидите это, когда ссылка в формуле больше не действительна. Это может быть тот случай, когда формула ссылается на ссылку на ячейку, а эта ссылка на ячейку не существует (происходит, когда вы удаляете строку / столбец или рабочий лист, на которые ссылается формула).
#VALUE! Error
Ошибка значения возникает при использовании неправильного типа данных в формуле.
Например, в приведенном ниже примере, когда я пытаюсь добавить ячейки с 0 и символом A, это дает ошибку значения. Это происходит, поскольку вы можете добавлять только числовые значения, но вместо этого я попытался добавить число с текстовым символом.
#NAME? Error
Эта ошибка, вероятно, является результатом неправильного написания функции. Например, если вместо VLOOKUP вы по ошибке используете VLOKUP, это выдаст ошибку имени.
#NUM! Error
Ошибка Num может возникнуть, если вы попытаетесь вычислить очень большое значение в Google Таблицах. Например, = 145 ^ 754 вернет числовую ошибку.
Это также может произойти, если вы попытаетесь указать неверный номер. Например, в случае функции квадратного корня, если вы укажете отрицательное число в качестве аргумента, она вернет числовую ошибку.
Теперь я надеюсь, что вы лучше понимаете возможные ошибки в Google Таблицах и то, что может привести к таким ошибкам.
Теперь давайте разберемся, как использовать функцию ЕСЛИОШИБКА в Google Таблицах для обработки всех этих ошибок.
Синтаксис функции IFERROR
Входные аргументы
Дополнительные замечания:
Использование функции IFERROR в Google Таблицах — Примеры
Вот несколько примеров использования функции ЕСЛИОШИБКА в Google Таблицах.
Пример 1. Возврат пустого или значимого текста вместо ошибки
Вы можете легко создать условия, в которых вы указываете конкретное значение в случае, если формула возвращает ошибку (например, если ошибка, то пусто, а если ошибка, то 0).
Если у вас есть результаты формулы, которые приводят к ошибкам, вы можете использовать функцию IFERROR (ЕСЛИОШИБКА), чтобы обернуть формулу в нее, а в случае ошибки вернуть пустой или значимый текст.
В приведенном ниже наборе данных расчет в столбце C возвращает ошибку, если значение количества равно 0 или пусто.
В этом случае вы можете вернуть пустое поле вместо ошибки, используя следующую формулу:
В этом случае вы также можете использовать какой-нибудь значимый текст вместо возврата пустой ячейки.
Например, приведенная ниже формула вернет текст «Ошибка», если расчет дает значение ошибки.
Пример 2 — Возврат «Не найдено», когда функция VLOOKUP не может найти значение
Вы можете использовать функцию ЕСЛИОШИБКА для возврата значимого текста, такого как «Не найдено» или «Недоступно», вместо ошибки.
Ниже приведен пример, в котором функция VLOOKUP возвращает #N/A! error.
Ниже приведена формула, которую можно использовать для возврата текста «Нет в списке» вместо сообщения об ошибке.
