Эксель почему не работает формула

Почему не работает формула ВПР (VLOOKUP) в Excel — Решение

Функция ВПР самая популярная и часто используемая функция. Знание данной функции очень поможет вам в вашей работе. Описание работы функции ВПР с примером вы можете посмотреть в специальной статье, а в данном уроке мы рассмотрим примеры, когда функция ВПР не работает или выдает ошибки.

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

Столбец поиска искомого значения не является крайним левым.

Самая распространенная ошибка, когда ВПР не работает. Давайте рассмотрим тот же пример, что мы рассматривали при описании функции ВПР. У нас есть таблица заказов и Прайс лист.

Обратите внимание, что в данном примере таблица Прайс лист отличается. Названия товаров теперь находятся не в первом столбце, а в третьем, после цены товара.

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

Решение

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

2. Обычно переносить столбец не очень удобно, например в случаях, когда эту таблицу нам присылаются постоянно в таком формате, поэтому как правило применяют прием добавления слева таблицы дублирующего вспомогательного столбца.

Выделяем первый столбец прайс листа, выбираем «Главная», «Ячейка», «Вставить», «Вставить столбцы на лист». Либо используйте пустой столбец, если он у вас уже есть.
Встаем в первую ячейку вспомогательного столбца и просто делаем ссылку на нужный нам столбец и протягиваем формулу вниз.

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

3. Можно использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.

Функция ВПР возвращает ошибку #Н/Д (#N/A)

#Н/Д (#N/A) означает «Нет данных» («not available»). При протягивании формулы ВПР у вас появляется #Н/Д. Вопрос даже не в том, что ВПР не работает, а почему появляются данные значения.

Решение

1. Не закреплен диапазон таблицы

Если при протягивании формулы ВПР у вас первое значение подтянулось а в остальных отобразилось #Н/Д то скорее всего вы не закрепили диапазон таблицы с помощью $ и при протягивании у вас таблица начала съезжать вниз. Еще раз прочитайте внимательно описание функции ВПР с примером.

2. Искомое значение и значение в просматриваемом диапазоне не совпадает

На рисунке выше есть #Н/Д два раза. Один раз напротив товара «Савок» и другой напротив слова «Стол».

В первом случае понятно — товара «Савок» у нас нет в прайс листе, отсюда и ошибка.

Во втором случае непонятно, товар «Стол» у нас есть, но все равно Н/Д#. Как правило ошибка заключается в написании слов и форматах либо в таблице заказов либо на странице Прайс лист.

В нашем случае у нас после слова «Стол » стоит пробел, поэтому данное слово не находится в прайс листе. Для начала проверьте содержимое ячеек на невидимые пробелы. Особенно часто они встречаются в конце строки. Кликните на ячейку и переместитесь в конец формулы. Удалите лишние пробелы, если они есть.

Читайте статью как удалить лишние пробелы в Excel

В данном примере можно так же использовать функцию СЖПРОБЕЛЫ

=ВПР( СЖПРОБЕЛЫ(A3) ;$F$2:$I$16;3;0)

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

В нашем случаем мы ищем текстовые значения — «Наименование товара», иногда поиск идет по числовым значениям. В данном случае #Н/Д может появляться, когда искомое значение, например в формате числа, а в таблице в виде текста. Для исправления ошибку потребуется перевести формат текста в числа или наоборот, то есть сделать одинаковый формат.

Еще более редкий случай, это когда в слове может быть вместо русских слов латинские и наоборот. Например A и А это разные буквы разных алфавитов, поэтому и слова будут разными для ВПР. Чтобы проверить, просто сделайте в отдельном столбце ссылку одной ячейки на другую =A4=F6 в нашем примере эта формула вернет «Ложь» так как эти ячейки не равны (в одном слове есть лишний пробел), аналогичная ситуация будет если вместо русской буквы «С» будет английская буква «C».

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

Функция ВПР возвращает ошибку #ЗНАЧ! (#VALUE!)

Обычно Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Если брать функцию ВПР, то обычно стоит рассмотреть две основные причины ошибки #ЗНАЧ!.

1. Искомое значение больше 255 символов.

В функции ВПР есть ограничение на длину искомого значения – оно не должно быть более 255 символов. Если вы столкнулись с такой проблемой, то рекомендуем использовать для этих целей все ту же связку ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH).

2. Неправильно указан полный путь на другую книгу.

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

3. Аргумент номер_столбца в функции ВПР меньше 1

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

Другие важные особенности работы функции ВПР

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

1. ВПР не чувствительна к регистру

Функция ВПР не чувствительна к регистру и для нее все символы нижнего и верхнего регистра будут одинаковые. То есть, слово «Стол», «СТОЛ» и «стол» для функции ВПР будут одинаковыми.

Если вам необходимо учитывать регистр при использовании ВПР, то используйте другую функцию Excel, наприме (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр и возвращается ИСТИНУ или ЛОЖЬ при совпадении или не совпадении с искомым значением.

2. ВПР возвращает первое найденное значение

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

3. Работа функции ВПР и добавлении или удалении столбца

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

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

Для решения этой проблемы можно использовать функцию ПОИСКПОЗ для поиска номера столбца. В качестве искомого значения мы можем указать текст «Цена» и искать в строке — шапке таблице Прайс лист. Теперь если добавлять столбцы в таблицу Прайс лист, то функция ПОИСКПОЗ все равно найдет ячейку со словом «Цена» и вернет на номер позиции данной ячейки.

Рекомендации, позволяющие избежать появления неработающих формул

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для планшетов с Android Excel 2010 Excel 2007 Excel Starter 2010 Еще. Меньше

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

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

Совет: Существует несколько распространенных функций, в которых могут возникнуть проблемы. Чтобы узнать больше, проверка СЧЁТЕСЛИ, СУММЕСЛИ, ВПР или ЕСЛИ. Список функций также можно просмотреть здесь.

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

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

Примечание: Если вы используете Microsoft 365 для Интернета, могут возникать другие ошибки или решения могут не сработать.

Возникает ошибка с символом решетки (#)?

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

Примечание: #### не указывает на ошибку, связанную с формулой, а означает, что столбец недостаточно широк для отображения содержимого ячеек. Просто перетащите границу столбца, чтобы расширить его, или воспользуйтесь параметром Главная > Формат > Автоподбор ширины столбца.

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

• Исправление ошибки #ЧИСЛО! ошибка
• Исправление ошибки #ЗНАЧ! ошибка
• Исправление ошибки #Н/Д
• Исправление ошибки #ДЕЛ/0! ошибка
• Исправление ошибки #ССЫЛКА! ошибка
• Исправление ошибки #ИМЯ? ошибка
• Исправление ошибки #ПУСТО!

Формула содержит недействительные ссылки

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

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

Вы также можете отключить показ этого диалогового окна при открытии файла. Для этого перейдите в раздел Файл > Параметры > Дополнительно > Общие и снимите флажок Запрашивать об обновлении автоматических связей.

Важно: Если раньше вам не приходилось сталкиваться с недействительными ссылками в формулах, вы хотите обновить в памяти сведения о них или не знаете, нужно ли обновлять ссылки, см. статью Управление обновлением внешних ссылок (связей).

Формула возвращает синтаксическую конструкцию, а не значение

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

Убедитесь, что в Excel настроен показ формул в электронных таблицах. Для этого на вкладке Формулы в группе Зависимости формул выберите Показать формулы.

Совет: Вы также можете нажать CTRL+` (эта клавиша расположена над клавишей TAB). При этом столбцы автоматически расширятся таким образом, чтобы в них поместились формулы. Не беспокойтесь: как только вы вернетесь в обычный режим, прежний размер столбцов восстановится.

Если решить проблему с помощью описанного выше действия не удалось, возможно, ячейка имеет текстовый формат. Чтобы изменить формат, щелкните ячейку правой кнопкой мыши и выберите Формат ячеек > Общий (или нажмите CTRL+1), а затем нажмите F2 > ВВОД.

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

Формула не вычисляется

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

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

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

Формула содержит одну или несколько циклических ссылок

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

Начинается ли функция со знака равенства (=)?

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

Если ввести СУММ(A1:A10), Excel отобразит текстовую строку СУММ(A1:A10) вместо результата формулы. Если ввести 11/2, отобразится дата (например, «11.фев» или «11.02.2009») вместо результата деления 11 на 2.

Чтобы избежать подобных неожиданных результатов, всегда начинайте формулу со знака равенства. Например, введите =СУММ(A1:A10) и =11/2.

Соблюдается ли соответствие открывающих и закрывающих скобок?

Если в формуле используется функция, для ее правильной работы важно, чтобы у каждой открывающей скобки была закрывающая. Убедитесь, что у каждой скобки есть соответствующая пара. Например, формула =ЕСЛИ(B5 <0);"Недопустимо";B5*1,05)не будет работать, так как в ней две закрывающие и только одна открывающая скобка. Правильный вариант этой формулы выглядит следующим образом: =ЕСЛИ(B5<0;"Недопустимо";B5*1,05).

Включает ли синтаксис все обязательные аргументы?

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

Например, функция ПРОПИСН принимает в качестве аргумента только одну текстовую строку или ссылку на ячейку: =ПРОПИСН(«привет») или =ПРОПИСН(C2).

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

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

Если вам нужно быстро просмотреть синтаксис определенной функции, см. список функций Excel (по категориям).

Есть ли в формулах неформатированные числа?

Не вводите в формулах числа со знаком доллара ($) или разделителем (;), так как знаки доллара используются для обозначения абсолютных ссылок, а точка с запятой — в качестве разделителя аргументов. Вместо $1,000 в формуле необходимо ввести 1000.

Если в качестве аргументов использовать форматированные числа, функция может вернуть неожиданные результаты вычисления или ошибку #ЧИСЛО!. Например, если ввести формулу =ABS(-2,134) для получения абсолютной величины числа -2134, Excel выведет ошибку #ЧИСЛО!, так как функция ABS принимает только один аргумент, но воспринимает -2, и 134 как отдельные аргументы.

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

Имеют ли ячейки, на которые указывают ссылки, правильный тип данных?

Формула может не возвратить ожидаемые результаты, если тип данных ячейки не подходит для вычислений. Например, если ввести простую формулу =2+3 в ячейке, которая имеет текстовый формат, Excel не сможет вычислить введенные данные. В ячейке будет отображаться строка =2+3. Чтобы исправить эту ошибку, измените тип данных ячейки с Текстовый на Общий, как описано ниже.

1. Выделите ячейку.
2. Выберите Главная и нажмите стрелку, чтобы развернуть группу Числовой или Числовой формат (или нажмите клавиши CTRL+1). Затем выберите Общий.
3. Нажмите клавишу F2, чтобы перейти в режим правки, а затем — клавишу ВВОД, чтобы подтвердить формулу.

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

Пытаетесь ли вы выполнить умножение, не используя символ *?

В качестве оператора умножения в формуле часто используют крестик (x), однако в этих целях в Excel необходимо использовать звездочку (*). Если в формуле использовать знак «x», появится сообщение об ошибке и будет предложено исправить формулу, заменив x на «*».

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

Отсутствуют ли кавычки вокруг текста в формулах?

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

Например, формула =»Сегодня » & ТЕКСТ(СЕГОДНЯ();»дддд, дд.ММ») объединяет текстовую строку «Сегодня » с результатами функций ТЕКСТ и СЕГОДНЯ и возвращает результат наподобие следующего: Сегодня понедельник, 30.05.

В формуле строка «Сегодня » содержит пробел перед закрывающей кавычкой, который соответствует пробелу между словами «Сегодня» и «понедельник, 30 мая». Если бы текст не был заключен в кавычки, формула могла бы вернуть ошибку #ИМЯ?.

Включает ли формула больше 64 функций?

Формула может содержать не более 64 уровней вложенности функций.

Заключены ли имена листов в апострофы?

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

Например, чтобы возвратить значение ячейки D3 листа «Данные за квартал» в книге, введите =’Данные за квартал’!D3. Если не заключить имя листа в кавычки, будет выведена ошибка #ИМЯ?.

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

Если формула ссылается на внешнюю книгу, правильно ли указан путь к книге?

При вводе ссылки на значения или ячейки в другой книге включайте ее имя в квадратных скобках ([]), за которым следует имя листа со значениями или ячейками.

Например, для создания ссылки на ячейки с A1 по A8 на листе «Продажи» книги «Операции за II квартал», открытой в Excel, введите: =[Операции за II квартал.xlsx]Продажи!A1:A8. Если не добавить квадратные скобки, будет выведена ошибка #ССЫЛКА!.

Если книга не открыта в Excel, введите полный путь к файлу.

Например =ЧСТРОК(‘C:\Мои документы\[Операции за II квартал.xlsx]Продажи’!A1:A8).

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

Совет: Чтобы получить путь к другой книге, проще всего открыть ее, ввести в исходной книге знак равенства (=), а затем с помощью клавиш ALT+TAB перейти во вторую книгу. После выбора любой ячейки на необходимом листе нужно закрыть исходную книгу. Формула автоматически обновится, и в ней отобразится полный путь к имени листа с правильным синтаксисом. При необходимости этот путь можно копировать и вставлять.

Пытаетесь ли вы делить числовые значения на нуль?

При делении одной ячейки на другую, содержащую нуль (0) или пустое значение, возвращается ошибка #ДЕЛ/0!.

Чтобы устранить эту ошибку, можно просто проверить, существует ли знаменатель. Вы можете использовать:

Смысл это формулы таков: ЕСЛИ B1 существует, вернуть результат деления A1 на B1, в противном случае вернуть 0.

Ссылается ли формула на удаленные данные?

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

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

• Если формула ссылается на ячейки, которые были удалены или заменены другими данными, и возвращает ошибку #ССЫЛКА!, выделите ячейку с этой ошибкой . В строке формул выберите текст #ССЫЛКА! и удалите его. Затем повторно укажите диапазон для формулы.
• Если отсутствует определенное имя, а формула, зависящая от него, возвращает ошибку #ИМЯ?, определите новое имя для нужного диапазона или вставьте в формулу непосредственную ссылку на диапазон ячеек (например, A2:D8).
• Если лист отсутствует, а формула, которая ссылается на него, возвращает ошибку #ССЫЛКА!, к сожалению, эту проблему невозможно решить: удаленный лист нельзя восстановить.
• Если отсутствует книга, это не влияет на формулу, которая ссылается на нее, пока не обновить формулу. Например, если используется формула =[Книга1.xlsx]Лист1′!A1, а такой книги больше нет, значения, ссылающиеся на нее, будут доступны. Но если изменить и сохранить формулу, которая ссылается на эту книгу, появится диалоговое окно Обновить значения с предложением ввести имя файла. Нажмите Отмена и обеспечьте сохранность данных, заменив формулу, которая ссылается на отсутствующую книгу, ее результатами.

Выполняли ли вы копирование и вставку ячеек, связанных с формулой, в таблице?

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

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

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

1. На листе выделите ячейки с итоговыми значениями формулы, которые требуется скопировать.
2. На вкладке Главная в группе Буфер обмена нажмите Копировать . Сочетание клавиш: CTRL+C.
3. Выделите левую верхнюю ячейку области вставки.

Совет: Чтобы переместить или скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните ярлычок другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.

Если имеется вложенная формула, вычисляйте ее по шагам

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

1. Выделите формулу, которую вы хотите вычислить.
2. Выберите Формулы >Вычислить формулу.
3. Нажмите Вычислить, чтобы проверить значение подчеркнутой ссылки. Результат вычисления отображается курсивом.
4. Если подчеркнутая часть формулы является ссылкой на другую формулу, нажмите Шаг с заходом, чтобы вывести другую формулу в поле Вычисление. Нажмите Шаг с выходом, чтобы вернуться к предыдущей ячейке и формуле. Кнопка Шаг с заходом будет недоступна, если ссылка используется в формуле во второй раз или если формула ссылается на ячейку в другой книге.
5. Продолжайте этот процесс, пока не будут вычислены все части формулы. Инструмент «Вычисление формулы» вряд ли сможет рассказать вам, почему формула не работает, но поможет найти проблемное место. Этот инструмент может оказаться очень полезным в больших формулах, когда очень трудно найти проблему иным способом.

• Некоторые части функций ЕСЛИ и ВЫБОР не будут вычисляться, а в поле Вычисление может появиться ошибка #Н/Д.
• Пустые ссылки отображаются как нулевые значения (0) в поле Вычисление.
• Некоторые функции пересчитываются при каждом изменении листа. Такие функции, в том числе СЛЧИС, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, ЧСТРОК, ЧИСЛСТОЛБ, ТДАТА, СЕГОДНЯ и СЛУЧМЕЖДУ, могут приводить к отображению в диалоговом окне Вычисление формулы результатов, отличающихся от фактических результатов в ячейке на листе.

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

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

Совет: Если вы владелец малого бизнеса и хотите получить дополнительные сведения о настройке Microsoft 365, посетите раздел Справка и обучение для малого бизнеса.

Почему не работают формулы в Excel

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

И к сожалению, этих причин очень-очень много. Из-за этого сложно сразу определить почему именно не работает какая-либо формула.

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

В данной статье мы как раз-таки и рассмотрим эти стандартные ошибки.

Формула вызвана неправильно

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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

К примеру, возьмем функцию ВПР. У неё 3 аргумента, которые пропустить никак нельзя, а также 1 дополнительный, который можно и не прописывать.

Допустим, у нас есть такая табличка:

Вот банальный пример из-за которого может возникнуть ошибка(или мы получим неверный результат).

=ВПР(A2;A2:C6;2;FALSE)

Вместо того чтобы получить оценку за первый экзамен (как и задумывалось), мы получаем оценку за второй. А это ведь невероятно критично в расчетах! Вы можете даже не заметить этого!

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

Допустим, у нас есть такая табличка:

Используем ВПР таким образом:

=ВПР(E2;$A$2:$C$6;2)

Аргумент соответствия мы не указали и посмотрите что стало с расчетами! Мы получили 2 неверных значения, а во всех остальных случаях вообще ошибки.

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

Пробелы в значениях

Их довольно сложно заметить, но из-за них может быть куча ошибок.

Допустим, такой пример:

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

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

Если мы удалим пробел — все заработает как надо.

Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.

Чтобы не проверять вручную каждую ячейку, мы можем заменить все двойные пробелы на один. Или использовать функцию СЖПРОБЕЛЫ.

Комбинация функций для нашего примера:

=ВПР(СЖПРОБЕЛЫ(D2);$A$2:$B$6;2;0)

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

Ручной режим расчета в формулах

Вы можете не знать что такое режимы расчета. Я сейчас объясню:

Изначально, в Excel включен автоматический режим, это значит — что при каждом изменении в значениях, программа автоматически пересчитывает все значения.

Да, это так. Каждый раз когда вы изменяете значение в какой-либо ячейке, если она указана в качестве данных для какой-либо формулы — измениться и результат этой формулы.

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

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

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

Что делать? Либо нажать F9 и заставить Excel выполнить перерасчет, либо включить автоматический режим, как и должно быть.

Как это сделать?

• Щелкните «Формулы»;

• В опции «Параметры вычислений» укажите «Автоматически».

Ошибка #REF

Чаще всего, она возникает из-за того, что указанная в формуле ссылка на ячейку (или диапазон ячеек) недоступна. Это может быть из-за того, что вы, например, удалили ячейку или столбик/строку с ней.

Обычно Excel автоматически скорректирует вашу функцию, но так бывает не всегда.

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

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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

Исправить эту ошибку довольно просто — обновите ссылки в формуле.

Неправильный порядок вычислений

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

Например, у нас есть такой пример:

Сначала выполняется умножение, а потом только сложение.

Но что если нужно сначала выполнить сложение? Тогда мы можем заключить сложение в скобки:

Так вот, если вы неправильно использовали, например, эти скобки в формуле — результат будет неверен.

Неправильное указание ссылок

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

Читатели нашего сайта знаю, что есть 3 типа ссылок в Excel. Но для нас, сейчас, важны только 2 — относительный и абсолютный тип. Относительный тип — который будет адаптироваться под каждую новую ячейку, а абсолютный — который изменяться не будет.

Давайте сразу рассмотрим пример.

Нам нужно вывести количество баллов за Exam 1.

Используем такую формулу:

=ВПР(E2;A2:B6;2;0)

Вот и ошибка. Но в чем же она?

Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.

Мы не заключили область поиска в $, то есть не сделали ссылку абсолютной и Excel адаптировал её для каждой последующей ячейки. Тут и кроется ошибка.

Есть способ быстро сделать ссылку абсолютной — выделите ячейку (или диапазон) в формуле и нажмите F4.

Неправильная ссылка на файл или лист

Тут похожая ситуация, мы сделали ссылку на файл или лист, но сделали её неправильно. Из-за этого возникает ошибка.

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

=Лист1!A1

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

А если мы делаем ссылку на файл и лист одновременно, то все это должно быть заключено в одинарные кавычки:

='[пример.xlsx]лист1'!$A$1

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

='C:\Users\Excel\Desktop\[пример.xlsx]лист1'!$A$1

Но если вы измените название файла или листа — получите ошибку.

Циклическая ссылка

Что это? Это когда вы указываете ссылку на ту ячейку, которая является источником формулы.

Например, мы прописываем эту формулу в A4

=СУММ(A1:A4)

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

К счастью, легко проверить где у вас есть циклические ссылки.

Щелкните на «Формулы» и «Проверка ошибок», там увидите параметр «Циклические ссылки», наведите на него и увидите ячейки, где Excel заметил циклические ссылки.

Текстовой формат ячейки

Если вы видите что-то подобное:

То знайте — вы столкнулись с текстовым форматом ячейки. Формула не выполняется, а просто отображается как текст.

Как сделать так, чтобы формула снова выполнялась:

1. Поменяйте формат ячейки на «Общий»;
2. А затем пересохраните значение ячейки.

Если вам это не помогло — проверьте есть ли “‘“ перед формулой (это можно посмотреть только в режиме редактирования).

Преобразование чисел в дату

Excel внимательно следит за тем, что вы вводите. И если что-то из введенного похоже на дату — он немедленно преобразует число в дату.

Часто это помогает, но иногда, просто раздражает.

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

Как это сделать?

• Выделите элементы, которые нужно преобразовать;
• Щелкните на «Главная»;

• И укажите формат «Текст».

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

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

Скрытые элементы

Сразу рассмотрим пример.

У нас есть столбик С с значениями продаж:

Давайте найдем сумму всех продаж:

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

Допустим, включим отображение продаж принтеров:

Видим неожиданное — результат не поменялся.

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

Чтобы правильно считать сумму в таких ситуациях — используйте функцию СУММ в комбинации с ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

Надеюсь, эта статья оказалась полезна для вас!

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

Решаем проблемы с формулами в Excel. Что если формула не работает или показывает ошибку

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

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

Изменение формата ячеек Excel

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

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

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

1. Выделить ячейку и перейти на вкладку «Главная» на ленте. После этого перемещаем курсор мыши в группу «число». Здесь также есть поле, в котором демонстрируется формат, в данный момент присвоенные ячейке.
2. Нужно нажать на стрелку вниз возле этого поля. После этого появится список с разными форматами ячеек. Нужно выбрать тот, который соответствует тому, который нужно применять в конкретной формуле. После этого делается клик по выбранному варианту.

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

1. Определить, нам нужно изменить формат одной ячейки или нескольких. После этого выбираем ту, которая нам подходит (или целый диапазон выделяем), после чего делаем правый клик по ней (ним). Далее ищем в появившемся перечне пункт «Формат ячеек». Задача может быть существенно упрощена, если пользователь нажмет сочетание Ctrl+1.

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

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

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

Что следует делать на практике?

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

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

Отключение режима “Показать формулы” в Excel

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

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

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

Активизация автоматического пересчета формул в Excel

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

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

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

Исправление ошибок в формулах

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

Кроме этого, нередко ошибки появляются в синтаксисе функций. Причина этого проста — не все они просты для заполнения обычными людьми. Особенно это касается ситуаций, когда применяется сразу несколько аргументов. Поэтому ручной ввод формул рекомендуется лишь если пользователь опытный, и в таком случае синтаксис нужно тщательно перепроверять. Для того, чтобы сделать ввод функций более простым, был создан Мастер функций, который вызывается путем нажатия клавиши fx слева от строки ввода формулы.

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

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

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

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

1. Выделяем ту ячейку, в которой есть ошибочная формула. Далее переходим во вкладку «Формулы». Там нужно найти инструмент «Зависимости формул» — «Вычислить формулу».
2. Перед глазами пользователя появится диалоговое окно. В нем детально будет описано, как осуществлять отчет правильно. Чтобы решить проблему, необходимо нажать кнопку «Вычислить». После этого функция будет последовательно проверяться на каждом шагу. Поэтому кнопку нужно нажать несколько раз. Таким образом можно отследить, на каком шагу возникла проблема и оперативно ее решить.

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

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

Чтобы исправить проблему, необходимо нажать на кнопку «Изменить в строке формул». После этого формула будет работать правильно.

Исправление ошибки в синтаксисе

Надо сказать, что все особенности исправления ошибок в синтаксисе были описаны выше, потому что это частный случай ошибок в формулах. Тем не менее, давайте резюмируем то, что было сказано, поскольку ошибка в синтаксисе — очень частая проблема начинающих пользователей Excel. Для проверки синтаксиса существует два специализированных инструмента: проверка формул и вычисление. Также можно воспользоваться ручным методом проверки. Когда какой лучше использовать?

1. В целом, если формула небольшая, ее можно проверить и самостоятельно. Это не потребует большого количества времени. Также можно без проблем проверять формулы самостоятельно, если их количество небольшое. Дело в том, что при увеличении количества формул увеличивается и количество ошибок при проверке. Мозг загромождается большим количеством информации, пусть и простой для обработки.
2. Если есть большое количество формул или сами формулы довольно большие, лучше воспользоваться специализированным инструментом. Каким именно — решать только вам. Каждый из них подходит, просто один предусматривает выполнение действий более в ручном режиме, а другой — автоматическом.

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

Выводы

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