Незащищенная формула excel что значит
К сожалению, слишком много вещей может пойти не так. Но поскольку мы живем в мире, который следует принципу Парето, то проверив некоторые общие проблемы, это, вероятно, решит 80% проблемы (или, может быть, даже 90% или 95% проблем).
В этой статье я хочу выделить те распространенные проблемы , которые, вероятно, являются причиной того, что ваши формулы Excel не работают. Изучите их внимательно, и тогда вы быстро и легко сможете понять где вы совершили ошибку.
Итак, приступим!
1. Неправильный синтаксис функции
Начнем с очевидного.
Каждая функция в Excel имеет определенный синтаксис, например количество или тип аргументов, которые она может принимать. И во многих случаях причиной того, что формулы Excel не работают или дают неправильный результат, может быть неправильный аргумент (или отсутствующие аргументы).
Например, функция ВПР принимает три обязательных аргумента и один необязательный аргумент. Если вы укажете неправильный аргумент или не укажете необязательный аргумент (где он нужен для работы формулы), он даст вам неправильный результат.
Например, предположим, что у вас есть набор данных, показанный ниже, в котором вам нужно знать оценку по экзамену 2 (в ячейке F2).
Если я использую приведенную ниже формулу, я получу неправильный результат, потому что я использую неправильное значение в третьем аргументе (тот, который запрашивает номер индекса столбца).
В этом случае формула вычисляет (поскольку возвращает значение), но результат неверен (вместо оценки за экзамен 2 он дает оценку за экзамен 1). Правильная формула должна выглядеть так:
2. Лишние пробелы, приводящие к неожиданным результатам
Начальные и конечные пробелы трудно найти, и они могут вызвать проблемы при использовании ячейки, в которой они есть в формулах.
Хотя видно, что формула верна и имя явно есть в списке, трудно заметить, что в ячейке с именем (в ячейке Е2) есть конечный пробел, а он есть.
Excel не считает содержимое этих двух ячеек одинаковым и поэтому считает его несоответствием при получении значения с помощью ВПР (или это может быть любая другая формула поиска).
Чтобы решить эту проблему, вам необходимо удалить эти лишние пробелы . Вы можете сделать это, используя любой из следующих методов:
- Очистите ячейку и удалите все начальные / конечные пробелы, прежде чем использовать ее в формулах.
- Используйте функцию =СЖПРОБЕЛЫ() в формуле, чтобы игнорировать любые начальные / конечные / двойные пробелы.
Одна из вещей, которые могут иметь разрушительный эффект на ваши существующие формулы в Excel, - это удаление любой строки / столбца, которые использовались в вычислениях. Когда это происходит, иногда Excel сам корректирует ссылку и проверяет правильность работы формул.
А иногда… не корректирует .
Ниже я использовал формулу СУММ (костыльный метод), чтобы получить итог ячеек от A2 до A8.
Теперь, если я удалю любую из этих ячеек / строк, формула СУММ покажет ошибку в вычислении. Это происходит потому, что когда я удалил 2 строки формула не знает, на что теперь ссылаться.
4. Неправильное использование абсолютных / относительных ссылок на ячейки
Когда вы копируете и вставляете формулы в Excel, он автоматически корректирует ссылки. Иногда это именно то, что вам нужно (в основном, когда вы копируете формулы вниз по столбцу).
Абсолютная ссылка - это когда вы закрепляете ссылку на ячейку (или ссылку на диапазон), чтобы она не менялась при копировании и вставке формул, а относительная ссылка - это ссылка, которая изменяется.
В ы можете получить неверный результат, если забудете изменить ссылку на абсолютную (или наоборот). Это то, что часто случается со мной, когда я использую формулы поиска. Вот пример:
Ниже у меня есть набор данных, в котором я хочу получить оценку в экзамене 1 для имен в столбце E (простой вариант использования ВПР)
Ниже приведена формула, которую я использую в ячейке F2, а затем копирую во все ячейки под ней:
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 Еще. Меньше
Эта ошибка означает, что формула в ячейке не соответствует шаблону формул рядом с ней.
Выяснение причины несоответствия
Щелкните Формулы > Показать формулы.
Это позволяет просматривать в ячейках формулы, а не вычисляемые результаты.
Сравните несогласованную формулу с соседними формулами и исправьте любые случайные несоответствия.
По завершении щелкните Формулы > Показать формулы. Это переключит отображение на вычисляемые результаты для всех ячеек.
Если это не помогает, выберите смежную ячейку, в которой отсутствует проблема.
Щелкните Формулы > Влияющие ячейки.
Выделите ячейку, содержащую проблему.
Щелкните Формулы > Влияющие ячейки.
Сравните синие стрелки или синие диапазоны. Исправьте все проблемы с несогласованной формулой.
Щелкните Формулы > Убрать стрелки.
Другие решения
Выделите ячейку с несогласованной формулой и, удерживая клавишу SHIFT, нажимайте одну из клавиш со стрелками. В результате несогласованная формула будет выделена вместе с другими. Затем выполните одно из указанных ниже действий.
Если выделены ячейки снизу, нажмите клавиши CTRL+D, чтобы заполнить формулой ячейки вниз.
Если выделены ячейки сверху, выберите Главная > Заполнить > Вверх, чтобы заполнить формулой ячейки вверх.
Если выделены ячейки справа, нажмите клавиши CTRL+R, чтобы заполнить формулой ячейки справа.
Если выделены ячейки слева, выберите Главная > Заполнить > Влево, чтобы заполнить формулой ячейки слева.
При наличии других ячеек, в которые нужно добавить формулу, повторите указанную выше процедуру в другом направлении.
Выделите ячейку с проблемой.
Если это не подходит и требуется формула из ячейки снизу, выберите Главная > Заполнить > Вверх.
Если требуется формула из ячейки справа, выберите Главная > Заполнить > Влево.
Если формула не содержит ошибку, можно ее пропустить:
Щелкните Формулы > Поиск ошибок.
Примечание: Если не нужно использовать в Excel этот способ проверки на несогласованные формулы, закройте диалоговое окно "Поиск ошибок". Выберите Файл > Параметры > Формулы. В нижней части снимите флажок Формулы, не согласованные с остальными формулами в области.
На компьютере Mac выберите Excel > Параметры > Поиск ошибок и снимите флажок Формулы, несогласованные с формулами в смежных ячейках.
Если формула не похожа на смежные формулы, отображается индикатор ошибки. Это не всегда означает, что формула неправильная. Если формула неправильная, проблему часто можно решить, сделав ссылки на ячейки единообразными.
Например, для умножения столбца A на столбец B используются формулы A1*B1, A2*B2, A3*B3 и т. д. Если после A3*B3 указана формула A4*B2, Excel определяет ее как несогласованную, так как ожидается формула A4*B4.
Щелкните ячейку с индикатором ошибки и просмотрите строку формул, чтобы проверить правильность ссылок на ячейки.
Щелкните стрелку рядом с появившейся кнопкой.
В контекстном меню приведены команды для устранения предупреждения.
Выполните одно из указанных ниже действий.
Скопировать формулу сверху
Согласует формулу с формулой в ячейке сверху. В нашем примере формула изменяется на A4*B4 в соответствии с формулой A3*B3 в ячейке выше.
Удаляет индикатор ошибки. Выберите эту команду, если несоответствие является преднамеренным или приемлемым.
Изменить в строке формул
Позволяет проверить синтаксис формулы и ссылки на ячейки.
Параметры проверки ошибок
Здесь можно выбрать типы ошибок, которые должен помечать Excel. Например, если вы не хотите, чтобы выводились индикаторы ошибки для несогласованных формул, снимите флажок Помечать формулы, несогласованные с формулами в смежных ячейках.
Чтобы пропустить индикаторы одновременно нескольких ячеек, выделите диапазон с этими ячейками. Затем щелкните стрелку рядом с появившейся кнопкой и в контекстном меню выберите команду Пропустить ошибку.
Чтобы пропустить индикаторы ошибок на всем листе, сначала щелкните ячейку с индикатором. Затем выделите лист, нажав клавиши +A. Затем щелкните стрелку рядом с появившейся кнопкой и в контекстном меню выберите команду Пропустить ошибку.
Дополнительные ресурсы
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функция IFERROR (ЕСЛИОШИБКА) в Excel лучше всего подходит для обработки случаев, когда формулы возвращают ошибку. Используя эту функцию, вы можете указать, какое значение функция должна возвращать вместо ошибки. Если функция в ячейке не возвращает ошибку, то возвращается её собственный результат.
Указанное вами значение, в случае если в ячейке есть ошибка.
добавить знак в ссылку вручную с клавиатуры, поместив курсор мыши в нужное место в имени, или выделить ссылку и нажать на клавиатуре клавишу F4. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Для поиска и устранения недочетов внутри редактора есть отдельный инструмент, который находится во вкладке Формулы на Панели управления. Функция вычислить формулу позволяет пошагово отследить работу выражения и позволяет определить место сбоя.
Как закрепить в формуле ячейку в Excel — MS Office Excel — Работа на компьютере: инструкции и советы — Образование, воспитание и обучение — Сообщество взаимопомощи учителей
- Если вы используете кавычки («») в качестве аргумента value_if_error (значение_если_ошибка), ячейка ничего не отображает в случае ошибки.
- Если аргумент value (значение) или value_if_error (значение_если_ошибка) ссылается на пустую ячейку, она рассматривается как пустая.
Как скрыть или показать строку формул в Excel?
Самый простой способ скрыть отображение формул — это непосредственно убрать строку формул из интерфейса программы Excel.
Для реализации такого способа в панели вкладок переходим на Вид -> Отображение и снимаем галочку из Строка формул, после чего строка сразу исчезает:
Однако как очень просто убрать строку формул, так очень просто и вернуть ее обратно, повторив все те же самые действия и вернув галочку на место.
Теперь давайте поподробнее поговорим про более сложные и надежные варианты.
В приведенном ниже примере учащийся имеет право на участие только в том случае, если он набрал более 80 баллов и имеет посещаемость более 80. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Для автоматизации процесса число 1,8 заменяем на ссылку на ячейку, его содержащую. В ячейке B3 получим формулу =A3*B1. Но при копировании в ячейку B4 формула автоматически примет вид =A4*B2 и результаты вычислений будут неправильными.
Функция ЕСЛИ в Excel. Как использовать? ЭКСЕЛЬ ХАК
- Защищаемая ячейка. В этом случае при включении защиты листа данные ячейки не будут защищаться (т.е. другими словами мы задаем исключения из защиты);
- Скрыть формулы. Аналогичный принцип только со свойством скрытия формул.
В формуле, использованной в примере выше, вычисление суммы комиссионных выполняется в самой функции ЕСЛИ . Если объем продаж находится между 50-100K, то формула возвращает B2 * 2%, что составляет 2% комиссии в зависимости от объема продажи.
Пример 2. Использование вложенной функции IF (ЕСЛИ) для проверки условия выражения
Функция может принимать до 64 условий одновременно. Несмотря на то, что создавать длинные вложенные функции нецелесообразно, то в редких случаях вы можете создать формулу, которая множество условий последовательно.
В приведенном ниже примере мы проверяем два условия.
- Первое условие проверяет, сумму баллов не меньше ли она чем 35 баллов. Если это ИСТИНА, то функция вернет “Не сдал”;
- В случае, если первое условие — ЛОЖЬ, и сумма баллов больше 35, то функция проверяет второе условие. В случае если сумма баллов больше или равна 75. Если это правда, то функция возвращает значение “Отлично”, в других случаях функция возвращает “Сдал”.
Пример 3. Вычисляем сумму комиссии с продаж с помощью функции IF (ЕСЛИ) в Excel
Функция позволяет выполнять вычисления с числами. Хороший пример использования — расчет комиссии продаж для торгового представителя.
В приведенном ниже примере, торговый представитель по продажам:
Рассчитать размер комиссионных для торгового агента можно по следующей формуле:
В формуле, использованной в примере выше, вычисление суммы комиссионных выполняется в самой функции ЕСЛИ . Если объем продаж находится между 50-100K, то формула возвращает B2 * 2%, что составляет 2% комиссии в зависимости от объема продажи.
Пример 4. Используем логические операторы (AND/OR) (И/ИЛИ) в функции IF (ЕСЛИ) в Excel
Вы можете использовать логические операторы (AND/OR) (И/ИЛИ) внутри функции для одновременного тестирования нескольких условий.
Например, предположим, что вы должны выбрать студентов для стипендий, основываясь на оценках и посещаемости. В приведенном ниже примере учащийся имеет право на участие только в том случае, если он набрал более 80 баллов и имеет посещаемость более 80%.
Вы можете использовать функцию AND (И) вместе с функцией IF (ЕСЛИ) , чтобы сначала проверить, выполняются ли оба эти условия или нет. Если условия соблюдены, функция возвращает “Имеет право”, в противном случае она возвращает “Не имеет право”.
Пример 5. Преобразуем ошибки в значения “0” с помощью функции IF (ЕСЛИ)
С помощью этой функции вы также можете убирать ячейки содержащие ошибки. Вы можете преобразовать значения ошибок в пробелы или нули или любое другое значение.
Формула для преобразования ошибок в ячейках следующая:
Формула возвращает “0”, в случае если в ячейке есть ошибка, иначе она возвращает значение ячейки.
ПРИМЕЧАНИЕ. Если вы используете Excel 2007 или версии после него, вы также можете использовать функцию IFERROR для этого.
Точно так же вы можете обрабатывать пустые ячейки. В случае пустых ячеек используйте функцию ISBLANK, на примере ниже:
Еще больше полезных приемов в работе с функциями и списками данных в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!
Как раз в этом случае мы можем воспользоваться функцией IFERROR (ЕСЛИОШИБКА) , для того, чтобы результат вычислений выглядел корректно, без ошибок. Добиться этого мы можем с помощью формулы:
Если вы используете кавычки в качестве аргумента value_if_error значение_если_ошибка , ячейка ничего не отображает в случае ошибки. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!
Как скрыть формулы в ячейках в Excel?.
про фиксирование ячейки, столбца и строки понятно. Есть необходимость немного в другом.
Я прописываю, скажем, а1=а2+а3
затем добавляю колонку между первой и второй
формула в а1 автоматически преобразовывается в =а3+а4
Как заставить ее не изменяться, а запомнить, что мне нужно именно а2+а3
Каждый, кто работает в Excel, рано или поздно, сталкивался с ошибками, которые Excel любезно подсвечивает для нас. Обычно это хорошо, но что делать, если нам нужно их убрать?
Конечно, можно нажать выбрать ячейку и в появившемся меню выбрать действия с ошибкой, пропустить ошибку или узнать о ней подробную информацию.
Чтобы не заморачиваться с каждой ячейкой, зайдите в настройки Excel: Файл ► Параметры ► Формулы , раздел Проверка ошибок и отключите проверку ошибок или отключите причину, которая её вызывает в Правилах проверки ошибок :
Решить задачу, не забираясь каждый раз в параметры, можно используя VBA и свойства объекта ErrorCheckingOptions .
Чтобы задать или вернуть параметры проверки ошибок, объект ErrorCheckingOptions , имеет следующие основные свойства:
- BackgroundChecking ( включает/отключает функцию проверки, значение параметра True-Включен/False-Выключен );
- IndicatorColorIndex ( определяет цвет ярлычка ошибки, по умолчанию xlColorIndexAutomatic );
- EvaluateToError ( отключает ошибки в формулах, например, деление на 0, True/False );
- TextDate(дата в виде текста с двумя цифрами года, True/False );
- NumberAsText ( числа отформатированные, как текст, True/False );
- InconsistentFormula ( отключает проверку несогласованности формул, True/False ).
Перейдём к макросам, тут ничего сложного. Если параметр включен ( True), отключить ( False) , иначе ( Else) включить, получается замкнутый круг:
Следующий макрос, скроет ярлычки, не отключая проверку ошибок:
Макрос заменяет стандартный зелёный цвет ярлычка ошибки на белый, но при наведении на ячейку с ошибкой, подсказка по ней будет доступна.
Заключительный макрос, отключит ошибки для чисел введённых, как текст:
Самостоятельно поэкспериментируйте с остальными свойствами.
Часто сталкиваетесь с ошибками в Excel? Пишите в комментариях. Подписывайтесь на канал, до новых встреч.
Каждый, кто более-менее часто имеет дело с формулами Excel (пишет сам или пользуется файлами, созданными другими), знает, что иногда в этих формулах появляются крайне неприятные ошибки, которые приводят к их полной неработоспособности. В разнообразии ошибок легко запутаться. Но чтобы уметь быстро их исправлять, нужно знать, почему возникает та или иная ошибка и что с ней делать. Разберем их основные виды.
Если ячейка вдруг целиком заполнилась символами решётки, то варианта всего два: либо значение ячейки не помещается в нее, либо в ней введено отрицательное значение времени (случается, если формат ячейки - "Время").
Самая распространенная ошибка. Возникает тогда, когда функция поиска данных не находит искомое значение в диапазоне. Функции поиска данных это: ВПР, ГПР, ПОИСКПОЗ, ПРОСМОТР. Соответственно, решается либо изменением поискового запроса ("что ищем"), либо внесением в диапазон искомого значения. Однако, чаще всего эта ошибка вполне ожидаема и просто помогает проверить наличие того или иного значения в списке. Многие пользователи предпочитают выводить вместо нее пустые значения или какой-то значимый текст с помощью функции ЕСЛИОШИБКА. Например:
Возникает, когда в формуле используется нераспознанное программой имя. Именем Excel считает любой текст, не являющийся названием функции, ссылкой на ячейку/диапазон и не взятый в кавычки. Например, в формуле =СЕГОДНЯ()+СЕГ-A4 слово СЕГ будет распознано как имя.
Когда распознанного имени нет в списке именованных диапазонов, появляется данная ошибка. Способы решения:
- Создать нужное имя в диспетчере имен;
- Проверить правильность написания уже существующего имени;
- Проверить, верно ли написаны функции рабочего листа (опечатки приведут к возникновению ошибки)
Данная ошибка возникает в случае, когда ячейка или диапазон, на который ссылается формула, был удален, перемещен или стал недоступным. Например, если есть формула
Другой вариант возникновения - файлы, на которые есть ссылки были перемещены, удалены или переименованы. Любое из этих действий при последующем обновлении связей выдаст указанную ошибку. Вывод - будьте аккуратнее с файлами, на которые делаете ссылки.
Возникает чаще всего тогда, когда в формуле использован неверный тип данных. Помните, что текст, число или дата - разные типы данных и обрабатываются по разному. Если передать, например, функции ДЕНЬНЕД() ссылку на ячейку с текстом, то появится указанная ошибка. То же самое произойдет, если попытаетесь вычесть из 100 слово "десять". Для исправления - проверьте все аргументы Соответствуют ли они требуемым типам данных? Если нет - укажите правильные типы.
Крайне редкая ошибка, так как мало кто использует в работе оператор пересечения диапазонов (про него можно почитать тут ). Собственно, возникает тогда, когда диапазоны не пересекаются. Для исправления - укажите пересекающиеся диапазоны. Например, формула:
выдаст ошибку. А формула:
будет работать безошибочно и вернет диапазон A5:B5
выдаст эту ошибку, так как аргумент "Ставка" не может быть отрицательным.
Для исправления - введите допустимый числовой аргумент.
Знать, что означают ошибки - полбеды. Нужно еще понять, какая именно часть формулы генерирует ошибку. Для этого формулу нужно проанализировать. Советы на эту тему можно найти в этой статье . Желаем Вам только исправных формул!
Видеоверсию данной статьи смотрите на нашем канале на YouTube
Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel - подписывайтесь на наш канал в Telegram Excel Everyday
Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) - на нашем канале в Telegram Office Killer
Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot
Вопросы по другому ПО (кроме Excel) задавайте второму боту - @KillOfBot
Читайте также: