Функция НЕ
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 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Используйте логическую функциюНЕ, если вы хотите убедиться, что одно значение не равно другому.
Пример
Технические сведения
Функция НЕ меняет значение своего аргумента на обратное.
Обычно функция НЕ используется для расширения возможностей других функций, выполняющих логическую проверку. Например, функция ЕСЛИ выполняет логическую проверку и возвращает одно значение, если при проверке получается значение ИСТИНА, и другое значение, если при проверке получается значение ЛОЖЬ. Использование функции НЕ в качестве аргумента «лог_выражение» функции ЕСЛИ позволяет проверять несколько различных условий вместо одного.
НЕ(логическое_значение)
Аргументы функции НЕ описаны ниже.
- Логическое_значение Обязательный. Значение или выражение, принимающее значение ИСТИНА или ЛОЖЬ.
Если аргумент «логическое_значение» имеет значение ЛОЖЬ, функция НЕ возвращает значение ИСТИНА; если он имеет значение ИСТИНА, функция НЕ возвращает значение ЛОЖЬ.
Примеры
Ниже представлено несколько общих примеров использования функции НЕ как отдельно, так и в сочетании с функциями ЕСЛИ, И и ИЛИ.
A2 НЕ больше 100
50 больше 1 (ИСТИНА) И меньше 100 (ИСТИНА), поэтому функция НЕ изменяет оба аргумента на ЛОЖЬ. Чтобы функция И возвращала значение ИСТИНА, оба ее аргумента должны быть истинными, поэтому в данном случае она возвращает значение ЛОЖЬ.
=ЕСЛИ(ИЛИ(НЕ(A3<0);НЕ(A3>50)); A3; «Значение вне интервала»)
100 не меньше 0 (ЛОЖЬ) и больше чем 50 (ИСТИНА), поэтому функция НЕ изменяет значения аргументов на ИСТИНА и ЛОЖЬ. Чтобы функция ИЛИ возвращала значение ИСТИНА, хотя бы один из ее аргументов должен быть истинным, поэтому в данном случае она возвращает значение ИСТИНА.
Расчет комиссионных
Ниже приводится решение довольно распространенной задачи: с помощью функций НЕ, ЕСЛИ и И определяется, заработал ли торговый сотрудник премию.
- =ЕСЛИ(И(НЕ(B14 <$B$7);НЕ(C14<$B$5));B14*$B$6;0)– ЕСЛИ общие продажи НЕ меньше целевых И число договоров НЕ меньше целевого, общие продажи умножаются на процент премии. В противном случае возвращается значение 0.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Функция ТЕКСТ
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 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
С помощью функции ТЕКСТ можно изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.
Примечание: Функция TEXT преобразует числа в текст, что может затруднить ссылку в последующих вычислениях. Лучше сохранить исходное значение в одной ячейке, а затем использовать функцию TEXT в другой ячейке. Затем, если потребуется создать другие формулы, всегда ссылайтесь на исходное значение, а не на результат функции ТЕКСТ.
Технические сведения
ТЕКСТ(значение; формат)
Аргументы функции ТЕКСТ описаны ниже.
Имя аргумента
Числовое значение, которое нужно преобразовать в текст.
Текстовая строка, определяющая формат, который требуется применить к указанному значению.
Общие сведения
Самая простая функция ТЕКСТ означает следующее:
- =ТЕКСТ(значение, которое нужно отформатировать; «код формата, который требуется применить»)
Ниже приведены популярные примеры, которые вы можете скопировать прямо в Excel, чтобы поэкспериментировать самостоятельно. Обратите внимание: коды форматов заключены в кавычки.
=ТЕКСТ(1234,567;«# ##0,00 ₽»)
Денежный формат с разделителем групп разрядов и двумя разрядами дробной части, например: 1 234,57 ₽. Обратите внимание: Excel округляет значение до двух разрядов дробной части.
=ТЕКСТ(СЕГОДНЯ();«ДД.ММ.ГГ»)
Сегодняшняя дата в формате ДД/ММ/ГГ, например: 14.03.12
=ТЕКСТ(СЕГОДНЯ();«ДДДД»)
Сегодняшний день недели, например: понедельник
=ТЕКСТ(ТДАТА();«ЧЧ:ММ»)
Текущее время, например: 13:29
=ТЕКСТ(0,285;«0,0 %»)
Процентный формат, например: 28,5 %
Дробный формат, например: 4 1/3
=СЖПРОБЕЛЫ(ТЕКСТ(0,34;«# ?/?»))
Дробный формат, например: 1/3 Обратите внимание: функция СЖПРОБЕЛЫ используется для удаления начального пробела перед дробной частью.
=ТЕКСТ(12200000;«0,00E+00»)
Экспоненциальное представление, например: 1,22E+07
Дополнительный формат (номер телефона), например: (123) 456-7898
=ТЕКСТ(1234;«0000000»)
Добавление нулей в начале, например: 0001234
=ТЕКСТ(123456;«##0° 00′ 00»»)
Пользовательский формат (широта или долгота), например: 12° 34′ 56»
Примечание: Функцию ТЕКСТ можно использовать для изменения форматирования, но это не единственный способ. Вы можете изменить формат без формулы, нажав клавиши CTRL+1 (или +1 на компьютере Mac), а затем выберите нужный формат в диалоговом окне Формат ячеек > число .
Скачивание образцов
Предлагаем скачать книгу, в которой содержатся все примеры применения функции ТЕКСТ из этой статьи и несколько других. Вы можете воспользоваться ими или создать собственные коды форматов для функции ТЕКСТ.
Другие доступные коды форматов
С помощью диалогового окна Формат ячеек можно найти другие доступные коды форматирования:
- Нажмите клавиши CTRL+1 (+1 на компьютере Mac), чтобы открыть диалоговое окно Формат ячеек .
- На вкладке Число выберите нужный формат.
- Выберите параметр Пользовательский .
- Нужный код формата будет показан в поле Тип. В этом случае выделите всё содержимое поля Тип, кроме точки с запятой (;) и символа @. В примере ниже выделен и скопирован только код ДД.ММ.ГГГГ.
- Нажмите клавиши CTRL+C , чтобы скопировать код форматирования, а затем нажмите кнопку Отмена , чтобы закрыть диалоговое окно Формат ячеек .
- Теперь осталось нажать клавиши CTRL+V, чтобы вставить код формата в функцию ТЕКСТ. Пример: =ТЕКСТ(B2;»ДД.ММ.ГГГГ«). Убедитесь, что код формата вставляется в кавычки («код форматирования»), в противном случае Excel выдаст сообщение об ошибке.
Коды форматов по категориям
Ниже приведены некоторые примеры того, как можно применить различные числовые форматы к значениям с помощью диалогового окна Формат ячеек , а затем с помощью параметра Custom скопировать эти коды форматирования в функцию TEXT .
Выбор числового формата
- Выбор числового формата
- Нули в начале
- Разделитель групп разрядов.
- Числовые, денежные и финансовые форматы
- Даты
- Значения времени
- Проценты
- Дроби
- Экспоненциальное представление
- Дополнительные форматы
Почему программа Excel удаляет нули в начале?
Excel воспринимает последовательность цифр, введенную в ячейку, как число, а не как цифровой код, например артикул или номер SKU. Чтобы сохранить нули в начале последовательностей цифр, перед вставкой или вводом значений примените к соответствующему диапазону ячеек текстовый формат. Выделите столбец или диапазон, в который нужно поместить значения, нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, и выберите на вкладке Число пункт Текстовый. Теперь программа Excel не будет удалять нули в начале.
Если вы уже ввели данные и Excel удалил начальные нули, вы можете снова добавить их с помощью функции ТЕКСТ. Создайте ссылку на верхнюю ячейку со значениями и используйте формат =ТЕКСТ(значение;»00000″), где число нулей представляет нужное количество символов. Затем скопируйте функцию и примените ее к остальной части диапазона.
Если по какой-либо причине потребуется преобразовать текстовые значения обратно в числа, можно умножить их на 1 (например: =D4*1) или воспользоваться двойным унарным оператором (—), например: =—D4.
В Excel группы разрядов разделяются пробелом, если код формата содержит пробел, окруженный знаками номера (#) или нулями. Например, если используется код формата «# ###», число 12200000 отображается как 12 200 000.
Пробел после заполнителя цифры задает деление числа на 1000. Например, если используется код формата «# ###,0 «, число 12200000 отображается в Excel как 12 200,0.
- Разделитель групп разрядов зависит от региональных параметров. Для России это пробел, но в других странах и регионах может использоваться запятая или точка.
- Разделитель групп разрядов можно применять в числовых, денежных и финансовых форматах.
Ниже показаны примеры стандартных числовых (только с разделителем групп разрядов и десятичными знаками), денежных и финансовых форматов. В денежном формате можно добавить нужное обозначение денежной единицы, и значения будут выровнены по нему. В финансовом формате символ рубля располагается в ячейке справа от значения (если выбрать обозначение доллара США, то эти символы будут выровнены по левому краю ячеек, а значения — по правому). Обратите внимание на разницу между кодами денежных и финансовых форматов: в финансовых форматах для отделения символа денежной единицы от значения используется звездочка (*).
Чтобы получить код формата для определенной денежной единицы, сначала нажмите клавиши CTRL+1 (на компьютере Mac — +1) и выберите нужный формат, а затем в раскрывающемся списке Обозначение выберите символ.
После этого в разделе Числовые форматы слева выберите пункт (все форматы) и скопируйте код формата вместе с обозначением денежной единицы.
Примечание: Функция ТЕКСТ не поддерживает форматирование с помощью цвета. Если скопировать в диалоговом окне «Формат ячеек» код формата, в котором используется цвет, например «# ##0,00 ₽;[Красный]# ##0,00 ₽», то функция ТЕКСТ воспримет его, но цвет отображаться не будет.
Способ отображения дат можно изменять, используя сочетания символов «Д» (для дня), «М» (для месяца) и «Г» (для года).
В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы «М» и «м», «Д» и «д», «Г» и «г».
Минда советует.
Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать.
Способ отображения времени можно изменить с помощью сочетаний символов «Ч» (для часов), «М» (для минут) и «С» (для секунд). Кроме того, для представления времени в 12-часовом формате можно использовать символы «AM/PM».
Если не указывать символы «AM/PM», время будет отображаться в 24-часовом формате.
В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы «Ч» и «ч», «М» и «м», «С» и «с», «AM/PM» и «am/pm».
Для отображения десятичных значений можно использовать процентные (%) форматы.
Десятичные числа можно отображать в виде дробей, используя коды форматов вида «?/?».
Экспоненциальное представление — это способ отображения значения в виде десятичного числа от 1 до 10, умноженного на 10 в некоторой степени. Этот формат часто используется для краткого отображения больших чисел.
В Excel доступны четыре дополнительных формата:
- «Почтовый индекс» («00000»);
- «Индекс + 4» («00000-0000»);
- «Номер телефона» («[
- «Табельный номер» («000-00-0000»).
Дополнительные форматы зависят от региональных параметров. Если же дополнительные форматы недоступны для вашего региона или не подходят для ваших нужд, вы можете создать собственный формат, выбрав в диалоговом окне Формат ячеек пункт (все форматы).
Типичный сценарий
Функция ТЕКСТ редко используется сама по себе, а чаще применяется в сочетании с чем-то еще. Предположим, что вы хотите объединить текст и числовое значение, например, чтобы получить строку «Отчет напечатан 14.03.12» или «Еженедельный доход: 66 348,72 ₽». Такие строки можно ввести вручную, но суть в том, что Excel может сделать это за вас. К сожалению, при объединении текста и форматированных чисел, например дат, значений времени, денежных сумм и т. п., Excel убирает форматирование, так как неизвестно, в каком виде нужно их отобразить. Здесь пригодится функция ТЕКСТ, ведь с ее помощью можно принудительно отформатировать числа, задав нужный код формата, например «ДД.ММ.ГГГГ» для дат.
В примере ниже показано, что происходит, если попытаться объединить текст и число, не применяя функцию ТЕКСТ. Мы используем амперсанд (&) для сцепления текстовой строки, пробела (» «) и значения: =A2&» «&B2.
Вы видите, что значение даты, взятое из ячейки B2, не отформатировано. В следующем примере показано, как применить нужное форматирование с помощью функции ТЕКСТ.
Вот обновленная формула:
- ячейка C2:=A2&» «&ТЕКСТ(B2;»дд.мм.гггг») — формат даты.
Вопросы и ответы
Как преобразовать числа в текст, например 123 в «сто двадцать три»?
К сожалению, вы не можете сделать это с помощью функции TEXT; необходимо использовать код Visual Basic для приложений (VBA). Следующая ссылка содержит метод: Как преобразовать числовое значение в слова на английском языке в Excel.
Можно ли изменить регистр текста?
Да, вы можете использовать функции ПРОПИСН, СТРОЧН и ПРОПНАЧ. Например, формула =ПРОПИСН(«привет») возвращает результат «ПРИВЕТ».
Можно ли с помощью функции ТЕКСТ добавить новую строку (разрыв строки) в ячейке, как при нажатии клавиш ALT+ВВОД?
Да, но для этого необходимо выполнить несколько действий. Сначала выберите ячейку или ячейки, в которых это произойдет, и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат > ячеек, а затем элемент управления Выравнивание > текст > проверка параметр Обтекать текстом. После этого добавьте в функцию ТЕКСТ код ASCII СИМВОЛ(10) там, где нужен разрыв строки. Вам может потребоваться настроить ширину столбца, чтобы добиться нужного выравнивания.
В этом примере использована формула =»Сегодня: «&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();»ДД.ММ.ГГ»).
Почему Excel преобразует введенные числа во что-то вроде «1,22E+07»?
Это называется научной нотацией, и Excel автоматически преобразует числа, превышающие 12 цифр, если ячейки форматируются как общие, и 15 цифр, если ячейки отформатированы как число. Если вам нужно ввести длинные числовые строки, но не нужно их преобразовывать, отформатируйте ячейки, о которой идет речь, как Текст , прежде чем вводить или вставлять значения в Excel.
Даты на разных языках
Минда советует.
Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать.
Поиск ошибок в формулах
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Кроме неожиданных результатов, формулы иногда возвращают значения ошибок. Ниже представлены некоторые инструменты, с помощью которых вы можете искать и исследовать причины этих ошибок и определять решения.
Примечание: В статье также приводятся методы, которые помогут вам исправлять ошибки в формулах. Это не исчерпывающий список методов для исправления каждой возможной ошибки формулы. Для получения справки по конкретным ошибкам поищите ответ на свой вопрос или задайте его на форуме сообщества Microsoft Excel.
Ввод простой формулы
Формулы — это выражения, с помощью которых выполняются вычисления со значениями на листе. Формула начинается со знака равенства (=). Например, следующая формула складывает числа 3 и 1:
Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.
Части формулы
- Функции: это специальные формулы Excel, которые выполняют определенные вычисления. Например, функция ПИ() возвращает значение числа Пи: 3,142.
- Ссылки: это ссылки на отдельные ячейки или диапазоны. Например, A2 возвращает значение ячейки A2.
- Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
- Операторы: оператор * (звездочка) служит для умножения чисел, а оператор ^ (крышка) — для возведения числа в степень. С помощью + и – можно складывать и вычитать значения, а с помощью / — делить их.
Примечание: Для некоторых функций требуются так называемые аргументы. Аргументы — это значения, которые некоторые функции используют при вычислениях. Аргументы функции указываются в ее скобках (). Функция ПИ не требует аргументов, поэтому у нее пустые скобки. Некоторые функции требуют одного или нескольких аргументов и могут оставить место для дополнительных аргументов. Аргументы разделяются точкой с запятой (;).
Например, функция СУММ требует только один аргумент, но у нее может быть до 255 аргументов (включительно).
Пример одного аргумента: =СУММ(A1:A10).
Пример нескольких аргументов: =СУММ(A1:A10;C1:C10).
Исправление распространенных ошибок при вводе формул
В приведенной ниже таблице собраны некоторые наиболее частые ошибки, которые допускают пользователи при вводе формулы, и описаны способы их исправления.
Начинайте каждую формулу со знака равенства (=)
Если опустить знак равенства, введенные данные могут отображаться в виде текста или даты. Например, если ввести SUM(A1:A10), Excel отображает текстовую строку SUM(A1:A10) и не выполняет вычисление. Если ввести 11/2, вместо деления 11 на 2 Excel отображается дата 2–ноябрь (при условии, что ячейка имеет формат «Общий«) вместо деления 11 на 2.
Следите за соответствием открывающих и закрывающих скобок
Для указания диапазона используйте двоеточие
Указывая диапазон ячеек, разделяйте с помощью двоеточия (:) ссылку на первую ячейку в диапазоне и ссылку на последнюю ячейку в диапазоне. Например, =SUM(A1:A5), а не =SUM(A1 A5), которые возвращают #NULL! Ошибка.
Вводите все обязательные аргументы
У некоторых функций есть обязательные аргументы. Старайтесь также не вводить слишком много аргументов.
Вводите аргументы правильного типа
В некоторых функциях, например СУММ, необходимо использовать числовые аргументы. В других функциях, например ЗАМЕНИТЬ, требуется, чтобы хотя бы один аргумент имел текстовое значение. Если в качестве аргумента используется неправильный тип данных, Excel может возвращать непредвиденные результаты или выводить ошибку.
Число уровней вложения функций не должно превышать 64
В функцию можно вводить (или вкладывать) не более 64 уровней вложенных функций.
Имена других листов должны быть заключены в одинарные кавычки
Если формула содержит ссылки на значения или ячейки на других листах или в других книгах, а имя другой книги или листа содержит пробелы или другие небуквенные символы, его необходимо заключить в одиночные кавычки (‘), например: =’Данные за квартал’!D3 или =‘123’!A1.
Указывайте после имени листа восклицательный знак (!), когда ссылаетесь на него в формуле
Например, чтобы возвратить значение ячейки D3 листа «Данные за квартал» в той же книге, воспользуйтесь формулой =’Данные за квартал’!D3.
Указывайте путь к внешним книгам
Убедитесь, что каждая внешняя ссылка содержит имя книги и путь к ней.
Ссылка на книгу содержит имя книги и должна быть заключена в квадратные скобки ([Имякниги.xlsx]). В ссылке также должно быть указано имя листа в книге.
В формулу также можно включить ссылку на книгу, не открытую в Excel. Для этого необходимо указать полный путь к соответствующему файлу, например: =ЧСТРОК(‘C:\My Documents\[Показатели за 2-й квартал.xlsx]Продажи’!A1:A8). Эта формула возвращает количество строк в диапазоне ячеек с A1 по A8 в другой книге (8).
Примечание: Если полный путь содержит пробелы, как в приведенном выше примере, необходимо заключить его в одиночные кавычки (в начале пути и после имени книги перед восклицательным знаком).
Числа нужно вводить без форматирования
Не форматируйте числа, которые вводите в формулу. Например, если нужно ввести в формулу значение 1 000 рублей, введите 1000. Если вы введете какой-нибудь символ в числе, Excel будет считать его разделителем. Если вам нужно, чтобы числа отображались с разделителями тысяч или символами валюты, отформатируйте ячейки после ввода чисел.
Например, если вы хотите добавить 3100 к значению в ячейке A3 и ввести формулу =СУММ(3,100,A3),Excel добавит числа 3 и 100, а затем добавит их итог к значению из A3, а не 3100 к A3, что будет =СУММ(3100,A3). Другой пример: если ввести =ABS(-2 134), Excel выведет ошибку, так как функция ABS принимает только один аргумент: =ABS(-2134).
Исправление распространенных ошибок в формулах
Вы можете использовать определенные правила для поиска ошибок в формулах. Они не гарантируют исправление всех ошибок на листе, но могут помочь избежать распространенных проблем. Эти правила можно включать и отключать независимо друг от друга.
Существуют два способа пометки и исправления ошибок: последовательно (как при проверке орфографии) или сразу при появлении ошибки во время ввода данных на листе.
Вы можете устранить ошибку с помощью параметров, отображаемых в Excel, или игнорировать ошибку, выбрав Игнорировать ошибку. Ошибка, пропущенная в конкретной ячейке, не будет больше появляться в этой ячейке при последующих проверках. Однако все пропущенные ранее ошибки можно сбросить, чтобы они снова появились.
Включение и отключение правил проверки ошибок
- Для Excel в Windows перейдите в раздел Параметры >файлов >формулы или
для Excel на Mac выберите меню Excel > Параметры > проверка ошибок. В Excel 2007 нажмите кнопку Microsoft Office >Параметры Excel >формулы. - В разделе Поиск ошибок установите флажок Включить фоновый поиск ошибок. Обнаруженная ошибка помечается треугольником в левом верхнем углу ячейки.
- Чтобы изменить цвет треугольника, которым помечаются ошибки, выберите нужный цвет в поле Цвет индикаторов ошибок.
- В разделе Правила поиска ошибок установите или снимите флажок для любого из следующих правил:
- Ячейки, содержащие формулы, которые приводят к ошибке. Формула не использует ожидаемый синтаксис, аргументы или типы данных. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!и #VALUE!. Каждое из этих значений ошибок имеет разные причины и разрешается по-разному.
Примечание: Если ввести значение ошибки непосредственно в ячейку, оно сохраняется как это значение ошибки, но не помечается как ошибка. Но если на эту ячейку ссылается формула из другой ячейки, эта формула возвращает значение ошибки из ячейки.
- Ввод данных, не являющихся формулой, в ячейку вычисляемого столбца.
- Введите формулу в ячейку вычисляемого столбца, а затем нажмите клавиши CTRL+Z или выберите Отменить на панели быстрого доступа.
- Ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений.
- Копирование в вычисляемый столбец данных, не соответствующих формуле столбца. Если копируемые данные содержат формулу, эта формула перезапишет данные в вычисляемом столбце.
- Перемещение или удаление ячейки из другой области листа, если на эту ячейку ссылалась одна из строк в вычисляемом столбце.
Если ссылки, используемые в формуле, не соответствуют ссылкам в смежных формулах, Excel отображает ошибку.
Последовательное исправление распространенных ошибок в формулах
- Выберите лист, на котором требуется проверить наличие ошибок.
- Если расчет листа выполнен вручную, нажмите клавишу F9, чтобы выполнить расчет повторно. Если диалоговое окно Проверка ошибок не отображается, выберите Формулы >аудит формул >проверка ошибок.
- Если вы ранее игнорировали какие-либо ошибки, вы можете снова проверка их, выполнив следующие действия: перейдите в раздел Параметры >файлов >Формулы. Для Excel на Mac выберите меню Excel > Параметры > проверки ошибок. В разделе Проверка ошибок выберите Сброс пропущенных ошибок >ОК.
Примечание: Сброс пропущенных ошибок применяется ко всем ошибкам, которые были пропущены на всех листах активной книги.
Совет: Советуем расположить диалоговое окно Поиск ошибок непосредственно под строкой формул.
Примечание: Если выбран параметр Игнорировать ошибку, ошибка помечается как игнорируемая для каждой последовательной проверка.
Исправление распространенных ошибок по одной
- Рядом с ячейкой выберите Проверка ошибок
, а затем выберите нужный параметр. Доступные команды различаются для каждого типа ошибки, и первая запись описывает ошибку. Если выбран параметр Игнорировать ошибку, ошибка помечается как игнорируемая для каждой последовательной проверка.
Исправление ошибки с #
Если формула не может правильно вычислить результат, в Excel отображается значение ошибки, например #####, #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА!, #ЗНАЧ!. Ошибки разного типа имеют разные причины и разные способы решения.
Приведенная ниже таблица содержит ссылки на статьи, в которых подробно описаны эти ошибки, и краткое описание.
Эта ошибка отображается в Excel, если столбец недостаточно широк, чтобы показать все символы в ячейке, или ячейка содержит отрицательное значение даты или времени.
Например, результатом формулы, вычитающей дату в будущем из даты в прошлом (=15.06.2008-01.07.2008), является отрицательное значение даты.
Совет: Попробуйте автоматически изменить ширину ячейки, дважды щелкнув между заголовками столбцов. Если ### отображается, так как Excel не может отобразить все символы, это исправит его.
Эта ошибка отображается в Excel, если число делится на ноль (0) или на ячейку без значения.
Совет: Добавьте обработчик ошибок, как в примере ниже: =ЕСЛИ(C2;B2/C2;0).
Эта ошибка отображается в Excel, если функции или формуле недоступно значение.
Если вы используете такую функцию, как ВПР, есть ли для искомого значения соответствие в диапазоне поиска? Скорее всего, нет.
Используйте функцию ЕСЛИОШИБКА для подавления ошибки #Н/Д. В этом случае можно ввести следующее:
=ЕСЛИОШИБКА(ВПР(D2;$D$6:$E$8;2;ИСТИНА);0)
Эта ошибка отображается, если Excel не распознает текст в формуле. Например, имя диапазона или функция могут быть написаны неправильно.
Примечание: Если вы используете функцию, убедитесь, что ее имя написано неправильно. В данном случае слово СУММ введено с ошибкой. Удалите «e», и Excel исправит его.
Эта ошибка отображается в Excel, когда вы указываете пересечение двух областей, которые не пересекаются. Оператором пересечения является пробел, разделяющий ссылки в формуле.
Примечание: Убедитесь, что диапазоны разделены правильно: области C2:C3 и E4:E6 не пересекаются, поэтому ввод формулы =СУММ(C2:C3 E4:E6) возвращает #NULL! ошибку «#ЗНАЧ!». Если поместить запятую между диапазонами C и E, она исправляет ее =СУММ(C2:C3;E4:E6)
Эта ошибка отображается в Excel, если формула или функция содержит недопустимые числовые значения.
Используете ли вы функцию, которая выполняет итерацию, например IRR или RATE? Если да, то #NUM! ошибка, вероятно, из-за того, что функция не может найти результат. Инструкции по устранению неполадок см. в разделе справки.
Эта ошибка отображается в Excel при наличии недопустимой ссылки на ячейку. Например, вы могли удалить ячейки, на которые ссылаются другие формулы, или вставить ячейки, которые вы переместили поверх ячеек, на которые ссылались другие формулы.
Вы случайно удалили строку или столбец? Смотрите, что произошло после удаления столбца B в формуле =СУММ(A2;B2;C2).
Нажмите кнопку Отменить (или клавиши CTRL+Z), чтобы отменить удаление, измените формулу или используйте ссылку на непрерывный диапазон (=СУММ(A2:C2)), которая автоматически обновится при удалении столбца B.
Эта ошибка отображается в Excel, если в формуле используются ячейки, содержащие данные не того типа.
Вы используйте математические операторы (+, -, *, / ^) с разными типами данных? В таком случае попробуйте использовать вместо них функцию. В этом случае =СУММ(F2:F5) поможет устранить проблему.
Просмотр формулы и ее результата в окне контрольного значения
Если ячейки не видны на листе, вы можете watch эти ячейки и их формулы на панели инструментов контрольного окна. С помощью окна контрольного значения удобно изучать, проверять зависимости или подтверждать вычисления и результаты формул на больших листах. При этом вам не требуется многократно прокручивать экран или переходить к разным частям листа.
Эту панель инструментов можно перемещать и закреплять, как и любую другую. Например, можно закрепить ее в нижней части окна. На панели инструментов выводятся следующие свойства ячейки: 1) книга, 2) лист, 3) имя (если ячейка входит в именованный диапазон), 4) адрес ячейки 5) значение и 6) формула.
Примечание: Для каждой ячейки может быть только одно контрольное значение.
Добавление ячеек в окно контрольного значения
- Выделите ячейки, которые хотите просмотреть. Чтобы выделить все ячейки на листе с формулами, перейдите на страницу Главная >Редактирование > выберите Найти & Выбрать (или можно использовать клавиши CTRL+G или CONTROL+G на компьютере Mac)> Перейти к специальным >формулам.
- Перейдите в раздел «Формулы » >аудит формул > выберите Контрольное окно.
- Выберите Добавить контрольные значения.
- Убедитесь, что выбраны все ячейки, которые нужно watch, и нажмите кнопку Добавить.
- Чтобы изменить ширину столбца, перетащите правую границу его заголовка.
- Чтобы открыть ячейку, ссылка на которую содержится в записи панели инструментов «Окно контрольного значения», дважды щелкните запись.
Примечание: Ячейки, содержащие внешние ссылки на другие книги, отображаются на панели инструментов «Окно контрольного значения» только в случае, если эти книги открыты.
Удаление ячеек из окна контрольного значения
- Если панель инструментов Контрольного окна не отображается, перейдите в раздел Формулы >аудит формул > выберите Контрольное окно.
- Выделите ячейки, которые нужно удалить. Чтобы выделить несколько ячеек, нажмите клавишу CTRL, а затем выделите ячейки.
- Выберите Удалить контрольные значения.
Вычисление вложенной формулы по шагам
Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью диалогового окна Вычисление формулы вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) проще понять, если вы увидите следующие промежуточные результаты:
В диалоговом окне «Вычисление формулы»
Сначала выводится вложенная формула. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40.
Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40.
Поскольку 40 не больше 50, выражение в первом аргументе функции ЕСЛИ (аргумент лог_выражение) имеет значение ЛОЖЬ.
Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только тогда, когда выражение имеет значение ИСТИНА.
- Выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.
- Перейдите к разделу >аудит формул >вычисление формулы.
- Нажмите Вычислить, чтобы проверить значение подчеркнутой ссылки. Результат вычисления отображается курсивом. Если подчеркнутая часть формулы является ссылкой на другую формулу, выберите Шаг В , чтобы отобразить другую формулу в поле Оценка . Нажмите Шаг с выходом, чтобы вернуться к предыдущей ячейке и формуле. Кнопка Шаг с заходом недоступна для ссылки, если ссылка используется в формуле во второй раз или если формула ссылается на ячейку в отдельной книге.
- Продолжайте выбирать Вычислять , пока не будет выполнена оценка каждой части формулы.
- Чтобы снова просмотреть оценку, выберите Перезапустить.
- Чтобы завершить оценку, нажмите кнопку Закрыть.
- Некоторые части формул, использующие функции IF и CHOOSE , не вычисляются. В этих случаях #N/A отображается в поле Оценка .
- Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).
- Некоторые функции вычисляются заново при каждом изменении листа, так что результаты в диалоговом окне Вычисление формулы могут отличаться от тех, которые отображаются в ячейке. Это функции СЛЧИС, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, ЧСТРОК, ЧИСЛСТОЛБ, ТДАТА, СЕГОДНЯ, СЛУЧМЕЖДУ.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Функция ЕСЛИ: проверяем условия с текстом
В статье приводятся примеры использования функции ЕСЛИ в Excel в том случае, если в ячейке находится текст. Рассмотрены условия полного либо частичного совпадения сравниваемых текстовых значений.
Будьте особо внимательны в том случае, если для вас важен регистр, в котором записаны ваши текстовые значения. Функция ЕСЛИ не проверяет регистр – это делают функции, которые вы в ней используете. Поясним на примере.
Проверяем условие для полного совпадения текста.
Проверку выполнения доставки организуем при помощи обычного оператора сравнения «=».
При этом будет не важно, в каком регистре записаны значения в вашей таблице.
Если вас интересует именно точное совпадение текстовых значений с учетом регистра, то можно рекомендовать вместо оператора «=» использовать функцию СОВПАД(). Она проверяет идентичность двух текстовых значений с учетом регистра отдельных букв.
Вот как это может выглядеть на примере.
Обратите внимание, что если в качестве аргумента мы используем текст, то он обязательно должен быть заключён в кавычки.
Использование ЕСЛИ + СОВПАД для полного совпадения текста.
Результат вы видите на скриншоте ниже.
Как видите, варианты «ВЫПОЛНЕНО» и «выполнено» не засчитываются как правильные. Засчитываются только полные совпадения. Будет полезно, если важно точное написание текста — например, в артикулах товаров.
Использование функции ЕСЛИ с частичным совпадением текста.
Выше мы с вами рассмотрели, как использовать текстовые значения в функции ЕСЛИ. Но часто случается, что необходимо определить не полное, а частичное совпадение текста с каким-то эталоном. К примеру, нас интересует город, но при этом совершенно не важно его название.
Первое, что приходит на ум – использовать подстановочные знаки «?» и «*» (вопросительный знак и звездочку). Однако, к сожалению, этот простой способ здесь не проходит.
Использование ЕСЛИ + ПОИСК
Нам поможет функция ПОИСК (в английском варианте – SEARCH). Она позволяет определить позицию, начиная с которой искомые символы встречаются в тексте. Синтаксис ее таков:
=ПОИСК(что_ищем, где_ищем, начиная_с_какого_символа_ищем)
Здесь нам на помощь приходит еще одна функция EXCEL – ЕЧИСЛО. Если ее аргументом является число, она возвратит логическое значение ИСТИНА. Во всех остальных случаях, в том числе и в случае, если ее аргумент возвращает ошибку, ЕЧИСЛО возвратит ЛОЖЬ.
В итоге наше выражение в ячейке G2 будет выглядеть следующим образом:
Еще одно важное уточнение. Функция ПОИСК не различает регистр символов.
Использование ЕСЛИ + НАЙТИ, чтобы учесть регистр букв
В том случае, если для нас важны строчные и прописные буквы, то придется использовать вместо нее функцию НАЙТИ (в английском варианте – FIND).
Синтаксис ее совершенно аналогичен функции ПОИСК: что ищем, где ищем, начиная с какой позиции.
Изменим нашу формулу в ячейке G2
Результат вы видите ниже.
То есть, если регистр символов для вас важен, просто замените ПОИСК на НАЙТИ.