Как найти ошибку в отчете и выделить ячейку excel: Скрытие значений и индикаторов ошибок в ячейках

Содержание

Скрытие значений и индикаторов ошибок в ячейках

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

Существует множество причин, по которым формулы могут возвращать ошибки. Например, деление на 0 не допускается, и если ввести формулу =1/0, Excel возвращает #DIV/0. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!.

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

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

Создание примера ошибки

Откройте чистый лист или создайте новый.
Введите 3 в ячейку B1, в ячейку C1 — 0, а в ячейку A1 — формулу =B1/C1.
The #DIV/0! в ячейке A1.
Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.
После знака равно (=) введите ЕСЛИERROR и открываю скобку.
ЕСЛИERROR(
Переместите курсор в конец формулы.
Введите ,0), то есть запятую и закрываюю скобки.
Формула =B1/C1 становится=ЕСЛИERROR(B1/C1;0).
Нажмите клавишу ВВОД, чтобы завершить редактирование формулы.
Теперь в ячейке вместо ошибки #ДЕЛ/0! должно отображаться значение 0.

Применение условного формата

Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.
org/ListItem»>
Выберите команду Создать правило.
В диалоговом окне Создание правила форматирования выберите параметр Форматировать только ячейки, которые содержат.
Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.
Нажмите кнопку Формат.
На вкладке Число в списке Категория выберите пункт (все форматы).
В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку
ОК. Нажмите кнопку ОК еще раз.
Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.

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

Выделите диапазон ячеек, содержащих значение ошибки.
org/ListItem»>

На вкладке Главная в группе Стили щелкните стрелку рядом с командой Условное форматирование и выберите пункт Управление правилами.
Появится диалоговое окно Диспетчер правил условного форматирования.
Выберите команду Создать правило.
Откроется диалоговое окно Создание правила форматирования.
В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат.
В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие

выберите пункт Ошибки.
Нажмите кнопку Формат и откройте вкладку Шрифт.
Щелкните стрелку, чтобы открыть список Цвет, а затем в списке Цвета темывыберите белый цвет.

Иногда вы не хотите, чтобы в ячейках появлялись оценки ошибок и вместо них должна отображаться текстовая строка, например «#N/Д», тире или строка «0». Сделать это можно с помощью функций ЕСЛИОШИБКА и НД, как показано в примере ниже.

Описание функций

ЕСЛИERROR С помощью этой функции можно определить, содержит ли ячейка ошибку и возвращает ли ошибку формула.

НД Эта функция возвращает в ячейке строку «#Н/Д». Синтаксис =NA().

Выберите отчет сводной таблицы.
Появится область «Инструменты для работы со pivottable».
Excel 2016 и Excel 2013: на вкладке Анализ в группе Таблица щелкните стрелку рядом с кнопкой Параметры
ивыберите параметры.

Excel 2010 и Excel 2007: на вкладке Параметры в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.
org/ListItem»>
Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
- Изменение способа отображения ошибок. В поле Формат выберите значение ошибкиПоказывать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
- Изменение способа отображения пустых ячеек Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

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

Ячейка с ошибкой в формуле

В Excel 2016, Excel 2013 и Excel 2010: Выберите Файл >Параметры >Формулы.

In Excel 2007: Click the Microsoft Office button > Excel Options >Formulas.
В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.

Отображение связей между формулами и ячейками

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

Ячейки- ячейки, на которые ссылается формула в другой ячейке. Например, если ячейка D10 содержит формулу =B5,ячейка B5 является влияемой на ячейку D10.
Зависимые ячейки — это ячейки, содержащие формулы, которые ссылаются на другие ячейки. Например, если ячейка D10 содержит формулу =B5, ячейка D10 является зависимой от ячейки B5.

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

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

Щелкните Файл > параметры > Дополнительные параметры.

Примечание: Если вы используете Excel 2007; нажмите кнопку Microsoft Office , Excel параметры, а затем выберите категорию Дополнительные параметры.
В разделе Показать параметры для этой книги выберите книгу и убедитесь, что выбрано все в разделе Для объектов, показать.
Чтобы указать ссылки на ячейки в другой книге, эта книга должна быть открыта. Microsoft Office Excel не может перейти к ячейке книги, если она не открыта.
Выполните одно из указанных ниже действий.

Сделайте следующее:

Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.
Чтобы отобразить стрелку трассировки для каждой ячейки, которая непосредственно предоставляет данные активной ячейке, на вкладке Формулы в группе Зависимости формул нажмите кнопку Ячейки трассировки .
- Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку имеется ссылка из другого рабочего листа или книги, путь от выбранной ячейки к значку рабочего листа будет обозначен черной стрелкой . Другую книгу необходимо открыть до того, как Excel начнет отслеживать эти зависимости.
Чтобы определить следующий уровень ячеек, которые предоставляют данные для активной ячейки, снова нажмите кнопку ячейки.
Чтобы удалить стрелки трассировки по одному уровню, начните с влияемой ячейки, которая дальше от активной ячейки. Затем на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить стрелки . Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.

Сделайте следующее:

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

Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку ссылается ячейка на другом месте или в другой книге, при наконечнике выбранной ячейки на значок . Другую книгу необходимо открыть до того, как Excel начнет отслеживать эти зависимости.
Чтобы определить следующий уровень ячеек, зависящих от активной ячейки, снова нажмите кнопку Зависимые ячейки .
Чтобы удалить стрелки зависимости по одному уровню, начиная с зависимой ячейки, наиболее далекой от активной ячейки, на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить зависимые стрелки . Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.

Сделайте следующее:

В пустой ячейке введите = (знак равно).
Нажмите кнопку Выделить все.
Вы выберите ячейку, а затем на вкладке Формулы в группе Зависимости формул дважды нажмите кнопку трассировки.

Чтобы удалить все стрелки трассировки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Удалить стрелки .

Проблема: Microsoft Excel издает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки.

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

org/ListItem»>
Ссылки на текстовые поля, внедренные диаграммы или рисунки на таблицах.
Отчеты для отчетов в отчетах.
Ссылки на именуемые константы.
Формулы, расположенные в другой книге, которые ссылаются на активную ячейку, если другая книга закрыта.

Примечания:

Чтобы увидеть выделение цветом влияющих ячеек для аргументов формулы, выделите ячейку и нажмите клавишу F2.
org/ListItem»>
Для выбора ячейки на другом конце стрелки дважды щелкните эту стрелку. Если ячейка находится на другом листе или в другой книге, дважды щелкните черную стрелку для отображения диалогового окна Переход, а затем дважды щелкните требуемую ссылку в списке Переход.
Все стрелки трассировки исчезают, если изменить формулу, на которую указывают стрелки, вставить или удалить столбцы или строки либо удалить или переместить ячейки. Для восстановления стрелок зависимости после сделанных изменений, необходимо снова использовать на этом листе команды аудита. Для отслеживания первоначальных стрелок зависимости следует до внесения изменений распечатать лист с отображенными стрелками зависимости.

Поиск и исправление ошибок в вычислениях Excel новость от 13.

03.2014

Идентификация ошибок осуществляется несколькими способами. Один из них реализуется через отображение кода ошибки в ячейке.

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

Второй способ обнаружения ошибок – Excel отображает в левом верхнем углу ячейки зелёный треугольник (индикатор ошибки). При выборе такой ячейки появляется смарт-тег проверки ошибок.

Для проверки ошибок необходимо выполнить следующие шаги:

1. Выберите лист, который требуется проверить на наличие ошибок.

2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Проверка наличия ошибок. Откроется окно диалога Контроль ошибок.

3. В окне диалога Контроль ошибок просмотрите информацию о текущей ошибке в левой части окна.

4. Для просмотра более детального описания ошибки и возможных вариантов её исправления нажмите кнопку Справка по этой ошибке.

5. Нажмите кнопку Показать этапы вычисления. MS Excel откроет окно диалога Вычисление формулы, где вы сможете просмотреть значения различных частей вложенной формулы, вычисляемые в порядке расчёта формулы:

a) нажмите кнопку Вычислить, чтобы проверить значение подчёркнутой ссылки. Результат вычислений показан курсивом;

b) если подчёркнутая часть формулы является ссылкой на другую формулу, нажмите кнопку Шаг с заходом, чтобы отобразить другую формулу в поле Вычисление. Нажмите кнопку Шаг с выходом, чтобы вернуться в предыдущую ячейку и формулу;

c) Выполняйте указанные действия, пока не будет вычислена каждая часть формулы;

d) Чтобы снова увидеть вычисления, нажмите кнопку Заново;

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

6. Для изменения формулы в строке формул нажмите кнопку Изменить в строке формул.

7. Если ошибка является несущественной, вы можете нажать кнопку Пропустить ошибку. Помеченная ошибка при последующих проверках будет пропускаться.

8. Для перехода к следующей ошибке нажмите кнопку Далее. Для возврата к предыдущей – кнопку Назад.

9. Доведите до конца проверку ошибок и закройте окно диалога Контроль ошибок.

Трассировка связей между формулами и ячейками

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

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

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

Для отображения ячеек, в формулы которых входит какая-либо ячейка, её следует выделить и нажать кнопку Зависимые ячейки в группе Зависимости формул вкладки Формулы.

Один щелчок по кнопке Зависимые ячейки отображает связи с ячейками, непосредственно зависящими от выделенной ячейки. Если эти ячейки также влияют на другие ячейки, то следующий щелчок отображает связи с зависимыми ячейками. И так далее.

Связи в пределах текущего листа отображаются синими стрелками. Связи с ячейками других листов и книг отображаются чёрными пунктирными линиями и значком листа. Красные стрелки показывают ячейки, вызывающие ошибки.

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

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

В этом случае вашим помощником может выступать панель инструментов Окно контрольного значения.

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

Добавление ячеек в окно контрольных значений

Для добавления на панель контрольных значений выполните следующие действия:

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

Чтобы выделить все ячейки листа с формулами, на вкладке Главная в группе Правка нажмите кнопку Найти и выделить и выберите команду Формулы.

2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.

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

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

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

Например, ячейка С4 = Е7, Е7 = С11, С11 = С4. В итоге С4 ссылается на С4.

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

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

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

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

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

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

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

Если циклическая ссылка – одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.

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

Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.

На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.

Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После её корректировки или удаления – со второй и т. д.

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

Успехов!!!

Основные принципы работы с датами и временем в Excel

2799 27. 10.2012 Скачать пример

Видео

Как обычно, кому надо быстро — смотрим видео. Подробности и нюансы — в тексте ниже:

Как вводить даты и время в Excel

Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами — и понимает их все:

«Классическая» форма	3.10.2006
Сокращенная форма	3.10.06
С использованием дефисов	3-10-6
С использованием дроби	3/10/6

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т. д.) и задается через контекстное меню — правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):

Время вводится в ячейки с использованием двоеточия. Например

16:45

По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:

16:45:30

И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть

27.10.2012 16:45

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

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

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

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата — лучше воспользоваться функцией СЕГОДНЯ (TODAY):

Как Excel на самом деле хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек — вкладка Число — Общий), то можно увидеть интересную картинку:

То есть, с точки зрения Excel, 27. 10.2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так — как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем! 😉
Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они — числа! А вот это уже раскрывает перед пользователем массу возможностей.

Количество дней между двумя датами

Считается простым вычитанием — из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

Количество рабочих дней между двумя датами

Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).

Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.

Про то, как это правильно вычислять, лучше почитать тут.

Сдвиг даты на заданное количество дней

Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

Ссылки по теме

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

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

Автор Амина С. На чтение 9 мин Опубликовано 31.01.2021

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

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

Содержание

Изменение формата ячеек Excel
Отключение режима “Показать формулы” в Excel
Активизация автоматического пересчета формул в Excel
Исправление ошибок в формулах
Исправление ошибки в синтаксисе
Выводы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выводы

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

Оцените качество статьи. Нам важно ваше мнение:

Поиск и исправление ошибок в вычислениях — FINDOUT.SU

Поможем в ✍️ написании учебной работы

Имя

Поможем с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой

Выберите тип работыЧасть дипломаДипломная работаКурсовая работаКонтрольная работаРешение задачРефератНаучно — исследовательская работаОтчет по практикеОтветы на билетыТест/экзамен onlineМонографияЭссеДокладКомпьютерный набор текстаКомпьютерный чертежРецензияПереводРепетиторБизнес-планКонспектыПроверка качестваЭкзамен на сайтеАспирантский рефератМагистерская работаНаучная статьяНаучный трудТехническая редакция текстаЧертеж от рукиДиаграммы, таблицыПрезентация к защитеТезисный планРечь к дипломуДоработка заказа клиентаОтзыв на дипломПубликация статьи в ВАКПубликация статьи в ScopusДипломная работа MBAПовышение оригинальностиКопирайтингДругое

Нажимая кнопку «Продолжить», я принимаю политику конфиденциальности

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

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

Таблица 11

Часто встречающиеся значения ошибок

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

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

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

Excel определяет зависимости ячеек, отображая синюю стрелку проверки от влияющей ячейки к активной ячейке или, наоборот, от активной ячейки к зависимой.

Чтобы программа отобразила стрелки, выберите команду Сервис | Зависимости формул и укажите необходимый пункт в подменю (Влияющие ячейки или Зависимые ячейки).

Отслеживать зависимости удобно с помощью панели инструментов Зависимости. Чтобы открыть ее, воспользуйтесь командой Сервис | Зависимости формул | Панель зависимостей.

Пример

Пусть в ячейках А1 и А2:А5 содержатся некоторые числа. В ячейку В2 запишите формулу =$А$1*A2, с помощью автозаполнения скопируйте эту формулу в ячейки В3:В5. Активизируйте ячейку А1 и выберите команду Сервис | Зависимости формул | Зависимые ячейки или щёлкните по кнопке на панели инструментов Зависимости. В таблице появятся линии трассировки с синими стрелками, исходящими от активной ячейки и указывающими на зависимые ячейки в таблице. Чтобы отобразить стрелки для влияющих ячеек, активизируйте ячейку В5 и нажмите кнопку Влияющие ячейки .

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

Кнопки , и предназначены для того, чтобы убрать стрелки.

10.6. Ограничение при вводе данных

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

Перейдите на вкладку Параметры. Прежде всего определите тип данных в поле Тип данных. Можно, например, разрешить ввод только целых чисел или значений времени. В поле Значение выберите критерий проверки: больше, не равно и т.д. В остальных полях (их количество зависит от критерия) установите граничные значения.

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

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

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

Диаграммы в microsoft excel

Excel предоставляет в распоряжение пользователей множество функций для создания и форматирования диаграмм.

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

Элементы диаграммы

Excel предоставляет в распоряжение пользователя различные функции для работы с диаграммами. Для создания диаграмм лучше всего использовать панель инструментов Диаграммы или Мастер диаграмм.

Диаграмму можно поместить на лист диаграмм или на лист с данными, на основе которых она создана.

Приведем наиболее важные понятия.

В зависимости от вида графического представления данных диаграммы различаются по типу. Excel предлагает пользователю целый ряд различных типов диаграмм (см. рис. 11.1).

Значения одной строки (столбца) таблицы на диаграмме отображаются как ряд данных.

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

По умолчанию значения строки образуют ряд данных, а значение столбца – категорию. При необходимости можно изменить ориентацию данных на диаграмме.

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

Рис. 11.1. Типы диаграмм в Excel

Мастер диаграмм

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

Процесс создания диаграммы управляется Мастером диаграмм. Вызвать Мастер диаграмм можно с помощью команды Вставка | Диаграмма или, щёлкнув по кнопке панели инструментов Стандартная.

Рассмотрим процесс создания диаграммы на простом примере:

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

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

Рис. 11.2. Данные, необходимые для построения диаграммы

3. Запустите Мастер диаграмм. На экране появится первое диалоговое окно, в котором выберите тип диаграммы (рис. 11.1). Данное окно содержит две вкладки Стандартные и Нестандартные. В списке Тип первой вкладки следует выбрать тип диаграммы, а в поле Вид – её формат, т.е. способ представления. Диаграммы одного типа можно отформатировать по-разному. Рекомендуется выбирать такой формат, который в наибольшей степени соответствует цели построения диаграммы. В дальнейшем формат диаграммы можно изменить. Если вас не устраивают стандартные типы диаграмм, то можно перейти на вкладку Нестандартные и создать собственный тип диаграмм. В нашем примере мы построим заданную по умолчанию гистограмму.

4. Нажмите кнопку Далее для перехода к следующему этапу создания диаграммы. На экране появится второе диалоговое окно Мастера диаграмм, в котором нужно будет указать источник данных диаграммы. Имена ячеек, выделенных в процессе второго шага, теперь можно видеть в текстовом поле Диапазон на вкладке Диапазон данных. При необходимости эти адреса можно изменить. Селекторные кнопки в строках и столбцах области Ряды позволяют изменить ориентацию данных. Вкладка Ряд (рис. 11.3) предназначена для добавления и удаления рядов данных. Чтобы добавить подписи по оси Х, щёлкните на кнопке свёртки окна, расположенной справа от поля ввода Подписи по оси Х. Выделите диапазон А4:А7 и нажмите Enter.

Рис. 11.3. Вкладка Ряд диалогового окна второго этапа Мастера диаграмм

5. Нажмите кнопку Далее, чтобы перейти к третьему окну Мастера диаграмм. Диалоговое окно Параметры диаграммы используется для задания заголовков, легенд и подписей к данным и содержит шесть вкладок. Вкладка Оси используется для режима отображения меток на осях диаграммы. Вкладка Линии сетки позволяет нанести на диаграмму масштабную сетку. На вкладке Легенда можно указать, нужна или нет на диаграмме легенда и задать положение легенды относительно диаграммы (по умолчанию она справа). Вкладка Таблица данных позволяет разместить рядом с диаграммой таблицу значений, на основании которых построена диаграмма. На вкладке Заголовки введите заголовок диаграммы и названия осей, как показано на рис. 11.4 и щёлкните по кнопке Далее.

Рис. 11.4. Окно Мастера диаграмм Параметры диаграммы

6. Откроется последнее диалоговое окно мастера диаграмм – Размещение диаграммы. Оно позволяет выбрать местоположение диаграммы: на отдельном листе или на листе с исходной таблицей. В нашем примере установите переключатель в положение Имеющемся (рис. 11.5).

Рис. 11.5. Выбор в окне мастера местоположения диаграммы

7. Нажмите кнопку Готово. После этого созданная диаграмма появится на рабочем листе (рис.11.6). Обратите внимание, что диаграмма в данный момент выделена и вокруг неё находятся восемь маркеров выделения. Чтобы снять выделение, щёлкните в любом месте листа.

Рис. 11.6. Итог создания диаграммы, встроенной в лист с данными

8. Диаграммы, как и другие объекты, можно разместить в любом месте листа, перемещая с помощью мыши. Для изменения размеров диаграммы используйте маркеры выделения. Кроме того, у готовой диаграммы можно поменять любые параметры форматирования (цвета, линии, надписи, размеры и т.д.). Достаточно выделить изменяемый элемент диаграммы (щёлкнув по нему мышью), открыть его контекстное меню (правой кнопкой мыши) и выбрать команду Формат.

Форматирование диаграмм

Каждый раз при выделении диаграммы происходит изменение в главном меню. Вместо пункта Данные появляется пункт Диаграмма. Команды этого меню предназначены для изменения вида диаграммы.

Также для форматирования используется панель инструментов Диаграммы, показанная на рис. 11.7. В числе прочего она имеет раскрывающийся список Элементы диаграммы, в котором можно выбрать объект, предназначенный для редактирования. Многие кнопки этой панели инструментов соответствуют командам меню Диаграмма. Чтобы сделать панель инструментов Диаграммы видимой, выберите команду Вид | Панели инструментов | Диаграммы.

Рис. 11.7. Панель инструментов Диаграммы

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

Примеры использования диаграмм в учебном процессе

С помощью Мастера диаграмм удобно создавать и обрабатывать различные графики.

Например, при обработке результатов лабораторных работ можно создать таблицу расчётных (теоретических) значений, построить график зависимости исследуемых параметров с помощью Мастера диаграмм. При построении теоретических кривых рекомендуется выбирать в качестве типа графика Гладкие графики на вкладке Нестандартные. Затем после выполнения лабораторной работы к исходной таблице можно добавить строку (столбец) экспериментальных данных. В диаграмме также добавить ряд данных. Для этого нужно выделить диаграмму (щёлкнуть по ней мышью), выбрать команды Добавить данные в меню Диаграмма. Откроется диалоговое окно Новые данные (рис. 11.8).

Рис. 11.8. Диалоговое окно команды Добавить данные

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

Кроме того, можно построить трёхмерную диаграмму (тип диаграммы – Поверхность). При этом на диаграмме хорошо просматриваются минимумы и максимумы заданной функции (можно использовать диаграмму для проверки решений своих заданий из курса математики).

Обнаружение ошибок в формулах

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

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

Научитесь вводить простую формулу

Формулы — это уравнения, которые выполняют вычисления со значениями на вашем листе. Формула начинается со знака равенства (=). Например, следующая формула добавляет 3 к 1.

=3+1

Формула также может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.

Части формулы

Функции: включенные в Excel, функции представляют собой разработанные формулы, которые выполняют определенные вычисления. Например, функция PI() возвращает значение числа пи: 3,142…
.
Ссылки: относятся к отдельным ячейкам или диапазонам ячеек. A2 возвращает значение в ячейке A2.
9Оператор (вставка) возводит число в степень, а оператор * (звездочка) умножает. Используйте + и – для сложения и вычитания значений и / для деления.
Примечание. Для некоторых функций требуется так называемое аргументов . Аргументы — это значения, которые определенные функции используют для выполнения своих вычислений. При необходимости аргументы помещаются между скобками функции (). Функция PI не требует никаких аргументов, поэтому она пуста. Некоторые функции требуют одного или нескольких аргументов и могут оставлять место для дополнительных аргументов. Вам нужно использовать запятую для разделения аргументов или точку с запятой (;) в зависимости от настроек вашего местоположения.

Например, функция СУММ требует только один аргумент, но может содержать до 255 аргументов.

=СУММ(A1:A10) — это пример одного аргумента.

=СУММ(A1:A10, C1:C10) — пример нескольких аргументов.

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

Убедитесь, что вы	Дополнительная информация
Начинать каждую функцию со знака равенства (=)	Если вы опустите знак равенства, то, что вы вводите, может отображаться как текст или как дата. Например, если ввести СУММ(A1:A10) , Excel отобразит текстовую строку СУММ(A1:A10) и не выполнит вычисления. Если ввести 11/2 , Excel отобразит дату 2 ноября (при условии, что формат ячейки Общий ) вместо деления 11 на 2.
Совпадение со всеми открывающими и закрывающими скобками	Убедитесь, что все круглые скобки являются частью совпадающей пары (открывающей и закрывающей). Когда вы используете функцию в формуле, важно, чтобы каждая скобка находилась в правильном положении, чтобы функция работала правильно. Например, формула *=ЕСЛИ(B5<0),"Недействительно",B51,05) не будет работать, потому что есть две закрывающие скобки и только одна открывающая скобка, тогда как должна быть только одна. Формула должна выглядеть так: =ЕСЛИ(B5<0,"Недействительно",B51,05)* .
Используйте двоеточие для указания диапазона	При ссылке на диапазон ячеек используйте двоеточие (:), чтобы отделить ссылку на первую ячейку в диапазоне от ссылки на последнюю ячейку в диапазоне. Например, =СУММ(A1:A5) , а не =СУММ(A1 A5) , что вернет #NULL! Ошибка.
Введите все необходимые аргументы	Некоторые функции имеют обязательные аргументы. Также убедитесь, что вы не ввели слишком много аргументов.
Введите правильный тип аргументов	Некоторые функции, такие как SUM , требуют числовых аргументов. Другие функции, такие как REPLACE , требуют текстового значения по крайней мере для одного из своих аргументов. Если вы используете неправильный тип данных в качестве аргумента, Excel может вернуть неожиданные результаты или отобразить ошибку.
Гнездо не более 64 функций	Вы можете ввести или вложить не более 64 уровней функций в функцию.
Заключить другие имена листов в одинарные кавычки	Если формула ссылается на значения или ячейки на других рабочих листах или рабочих книгах, а имя другой рабочей книги или рабочего листа содержит пробелы или небуквенные символы, вы должны заключить ее имя в одинарные кавычки ( ‘ ), например = ‘Ежеквартально Данные’!D3 или =’123′!A1 .
Ставьте восклицательный знак (!) после имени рабочего листа, когда вы ссылаетесь на него в формуле	Например, чтобы получить значение из ячейки D3 на листе с названием «Квартальные данные» в той же книге, используйте следующую формулу: = «Квартальные данные»! D3 .
Включить путь к внешним книгам	Убедитесь, что каждая внешняя ссылка содержит имя книги и путь к ней. Ссылка на рабочую книгу включает имя рабочей книги и должна быть заключена в квадратные скобки ([ Workbookname.xlsx ]). Ссылка также должна содержать имя рабочего листа в рабочей книге. Если книга, на которую вы хотите сослаться, не открыта в Excel, вы все равно можете включить ссылку на нее в формулу. Вы указываете полный путь к файлу, как в следующем примере: =ROWS(‘C:\My Documents\[Q2 Operations.xlsx]Sales’!A1:A8) . Эта формула возвращает количество строк в диапазоне, который включает ячейки с A1 по A8 в другой книге (8). Примечание. Если полный путь содержит символы пробела, как в предыдущем примере, необходимо заключить путь в одинарные кавычки (в начале пути и после имени рабочего листа, перед восклицательным знаком).
Ввод чисел без форматирования	Не форматировать числа при вводе их в формулы. Например, если значение, которое вы хотите ввести, равно 1000 долларов США, введите в формулу 1000 . Если вы вводите запятую как часть числа, Excel обрабатывает ее как символ-разделитель. Если вы хотите, чтобы числа отображались так, чтобы они отображали разделители тысяч или миллионов или символы валюты, отформатируйте ячейки после ввода чисел. Например, если вы хотите добавить 3100 к значению в ячейке A3 и ввести формулу =СУММ(3,100,A3) , Excel добавит числа 3 и 100, а затем прибавит эту сумму к значению из A3, вместо добавления 3100 к A3, что будет =SUM(3100,A3) . Или, если ввести формулу =ABS(-2,134), Excel отобразит ошибку, поскольку функция ABS принимает только один аргумент: =ABS(-2134) .

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

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

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

Для Excel в Windows щелкните Файл > Параметры > Формулы или
для Excel на Mac щелкните меню Excel > «Настройки» > «Проверка ошибок ».
В Excel 2007 нажмите кнопку Microsoft Office > Параметры Excel > Формулы .
Под Проверка ошибок , проверьте Включить фоновую проверку ошибок . Любая обнаруженная ошибка будет отмечена треугольником в верхнем левом углу ячейки.
Чтобы изменить цвет треугольника, обозначающего место возникновения ошибки, в Укажите ошибки, используя это поле цвета , выберите нужный цвет.
В разделе Правила проверки Excel установите или снимите флажки любого из следующих правил:
- Ячейки, содержащие формулы, которые приводят к ошибке : Формула не использует ожидаемый синтаксис, аргументы или типы данных. Значения ошибок включают #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!. Каждое из этих значений ошибки имеет разные причины и устраняется по-разному.
  Примечание. Если вы введете значение ошибки непосредственно в ячейку, оно будет сохранено как значение ошибки, но не помечено как ошибка. Однако если формула в другой ячейке ссылается на эту ячейку, формула возвращает значение ошибки из этой ячейки.
- Несовместимая формула вычисляемого столбца в таблицах : Вычисляемый столбец может включать отдельные формулы, отличные от формулы основного столбца, что создает исключение. Исключения вычисляемого столбца создаются при выполнении любого из следующих действий:
  - Введите данные, отличные от формулы, в ячейку вычисляемого столбца.
  - Введите формулу в ячейку вычисляемого столбца, а затем используйте Ctrl + Z или нажмите Отменить на панели быстрого доступа .
  - Введите новую формулу в вычисляемый столбец, который уже содержит одно или несколько исключений.
  - Скопируйте в вычисляемый столбец данные, которые не соответствуют формуле вычисляемого столбца. Если скопированные данные содержат формулу, эта формула перезаписывает данные в вычисляемом столбце.
  - org/ListItem»>
    Переместите или удалите ячейку в другой области рабочего листа, на которую ссылается одна из строк в вычисляемом столбце.
- Ячейки, содержащие годы, представленные в виде 2 цифр : Ячейка содержит текстовую дату, которая может быть неверно истолкована как неправильное столетие при использовании в формулах. Например, дата в формуле =ГОД(«1/1/31») может быть 1931 или 2031. Используйте это правило, чтобы проверить наличие неоднозначных текстовых дат.
- Числа в текстовом формате или с предшествующим апострофом : Ячейка содержит числа, сохраненные в виде текста. Обычно это происходит, когда данные импортируются из других источников. Числа, хранящиеся в виде текста, могут привести к неожиданным результатам сортировки, поэтому лучше преобразовать их в числа. ‘=СУММ(A1:A10) рассматривается как текст.
- Формулы, несовместимые с другими формулами в области : Формула не соответствует шаблону других формул рядом с ней. Во многих случаях формулы, расположенные рядом с другими формулами, отличаются только используемыми ссылками. В следующем примере с четырьмя соседними формулами Excel отображает ошибку рядом с формулой =СУММ(A10:C10) в ячейке D4, поскольку соседние формулы увеличиваются на одну строку, а эта увеличивается на 8 строк — Excel ожидает формулу =СУММ (А4:С4).
  Если ссылки, используемые в формуле, не соответствуют ссылкам в соседних формулах, Excel отображает ошибку.
- org/ListItem»>
  Формулы, в которых пропущены ячейки в области : Формула не может автоматически включать ссылки на данные, которые вы вставляете между исходным диапазоном данных и ячейкой, содержащей формулу. Это правило сравнивает ссылку в формуле с фактическим диапазоном ячеек, примыкающим к ячейке, содержащей формулу. Если соседние ячейки содержат дополнительные значения и не являются пустыми, Excel отображает ошибку рядом с формулой.
  Например, Excel вставляет ошибку рядом с формулой =СУММ(D2:D4) при применении этого правила, поскольку ячейки D5, D6 и D7 находятся рядом с ячейками, на которые ссылается формула, и ячейкой, на которую ссылается формула. содержит формулу (D8), а эти ячейки содержат данные, на которые должна быть ссылка в формуле.
- незаблокированных ячеек, содержащих формулы : Формула не заблокирована для защиты. По умолчанию все ячейки на рабочем листе заблокированы, поэтому их нельзя изменить, когда рабочий лист защищен. Это может помочь избежать непреднамеренных ошибок, таких как случайное удаление или изменение формул. Эта ошибка указывает на то, что ячейка была разблокирована, но лист не защищен. Убедитесь, что вы не хотите блокировать ячейку или нет.
- Формулы со ссылкой на пустые ячейки : Формула содержит ссылку на пустую ячейку. Это может привести к непредвиденным результатам, как показано в следующем примере.
  Предположим, вы хотите вычислить среднее число чисел в следующем столбце ячеек. Если третья ячейка пуста, она не включается в расчет и результат равен 22,75. Если третья ячейка содержит 0, результат равен 18,2.
- org/ListItem»>
  Данные, введенные в таблицу, недействительны. : Ошибка проверки таблицы. Проверьте настройку проверки для ячейки, перейдя на вкладку Data > группу Data Tools > Data Validation .

Выберите рабочий лист, который вы хотите проверить на наличие ошибок.
Если рабочий лист вычисляется вручную, нажмите F9 для пересчета.
Если диалоговое окно Проверка ошибок не отображается, щелкните вкладку Формулы > Проверка формул > Проверка ошибок .
org/ListItem»>
Если вы ранее игнорировали какие-либо ошибки, вы можете снова проверить их, выполнив следующие действия: нажмите Файл > Опции > Формулы . Для Excel на Mac щелкните меню Excel > «Настройки» > «Проверка ошибок ».
В разделе Error Checking нажмите Сбросить пропущенные ошибки > OK .
Примечание. При сбросе пропущенных ошибок сбрасываются все ошибки на всех листах активной книги.
Совет: Может помочь, если вы переместите Ошибка проверки диалоговое окно сразу под строкой формул.
Нажмите одну из кнопок действий в правой части диалогового окна. Доступные действия различаются для каждого типа ошибки.
org/ListItem»>
Нажмите Далее .

Примечание. Если нажать Игнорировать ошибку , ошибка будет помечена как игнорируемая при каждой последующей проверке.

Рядом с ячейкой нажмите появившуюся кнопку Error Checking , а затем выберите нужный вариант. Доступные команды различаются для каждого типа ошибки, и первая запись описывает ошибку.
Если нажать Игнорировать ошибку , ошибка помечается как игнорируемая при каждой последующей проверке.

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

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

Тема	Описание
Исправьте ошибку ####	Excel отображает эту ошибку, если ширина столбца недостаточна для отображения всех символов в ячейке или ячейка содержит отрицательные значения даты или времени. Например, формула, которая вычитает дату в будущем из даты в прошлом, например =15/06/2008-01/07/2008, дает отрицательное значение даты. Совет. Попробуйте автоматически подогнать размер ячейки, дважды щелкнув между заголовками столбцов. Если ### отображается из-за того, что Excel не может отображать все символы, это исправит это.
Исправьте #DIV/0! ошибка	Excel отображает эту ошибку, когда число делится либо на ноль (0), либо на ячейку, которая не содержит значения. Совет: Добавьте обработчик ошибок, как в следующем примере: =IF(C2,B2/C2,0)
Исправьте ошибку #Н/Д	Excel отображает эту ошибку, когда значение недоступно для функции или формулы. Если вы используете такую функцию, как ВПР, имеет ли то, что вы пытаетесь найти, совпадение в диапазоне поиска? Чаще всего это не так. Попробуйте использовать ЕСЛИОШИБКА, чтобы подавить #Н/Д. В этом случае вы можете использовать: =ЕСЛИОШИБКА(ВПР(D2,$D$6:$E$8,2,ИСТИНА),0)
Исправить #ИМЯ? ошибка	Эта ошибка отображается, когда Excel не распознает текст в формуле. Например, имя диапазона или имя функции может быть написано неправильно. Примечание. Если вы используете функцию, убедитесь, что имя функции написано правильно. В этом случае СУММА написана неправильно. Удалите «е», и Excel исправит это.
Исправьте #NULL! ошибка	Excel отображает эту ошибку при указании пересечения двух областей, которые не пересекаются (пересекаются). Оператор пересечения — это символ пробела, который разделяет ссылки в формуле. Примечание: Убедитесь, что ваши диапазоны правильно разделены — области C2:C3 и E4:E6 не пересекаются, поэтому введите формулу =СУММ(C2:C3 E4:E6) возвращает #NULL! ошибка. Помещение запятой между диапазонами C и E исправит это =СУММ(C2:C3,E4:E6)
Исправьте #ЧИСЛО! ошибка	Excel отображает эту ошибку, когда формула или функция содержит недопустимые числовые значения. Вы используете повторяющуюся функцию, такую как IRR или RATE? Если да, то #ЧИСЛО! ошибка, вероятно, связана с тем, что функция не может найти результат. Обратитесь к разделу справки для шагов решения.
Исправьте #REF! ошибка	Excel отображает эту ошибку, если ссылка на ячейку недействительна. Например, вы могли удалить ячейки, на которые ссылались другие формулы, или вы могли вставить ячейки, которые вы переместили, поверх ячеек, на которые ссылались другие формулы. Вы случайно удалили строку или столбец? Мы удалили столбец B в этой формуле =СУММ(A2,B2,C2) и посмотрите, что получилось. Либо используйте Отменить (Ctrl+Z), чтобы отменить удаление, перестроить формулу, либо использовать ссылку на непрерывный диапазон, например: =СУММ(A2:C2), которая автоматически обновлялась бы при удалении столбца B.
9) с разными типами данных? Если это так, попробуйте вместо этого использовать функцию. В этом случае =СУММ(F2:F5) устранит проблему.

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

Эту панель инструментов можно перемещать или закреплять, как и любую другую панель инструментов. Например, вы можете закрепить его в нижней части окна. Панель инструментов отслеживает следующие свойства ячейки: 1) Рабочая книга, 2) Лист, 3) Имя (если ячейка имеет соответствующий именованный диапазон), 4) Адрес ячейки, 5) Значение и 6) Формула.

Примечание. В каждой ячейке может быть только одно наблюдение.

Добавить ячейки в окно Watch

Выберите ячейки, которые вы хотите просмотреть.
Чтобы выделить все ячейки на листе с формулами, на вкладке Главная в группе Редактирование нажмите Найти и выбрать (или можно использовать Ctrl+G или Control+G на Mac)> Перейти к Special > Formulas .
На вкладке Formulas в Formula Auditing , нажмите Окно просмотра .
Нажмите Добавить наблюдение .
Подтвердите, что вы выбрали все ячейки, которые хотите отслеживать, и нажмите Добавить .
Чтобы изменить ширину столбца окна наблюдения, перетащите границу справа от заголовка столбца.
Чтобы отобразить ячейку, на которую ссылается запись в панели инструментов Watch Window, дважды щелкните запись.
Примечание. Ячейки, имеющие внешние ссылки на другие книги, отображаются на панели инструментов окна наблюдения только тогда, когда другие книги открыты.

Удалить ячейки из окна Watch

Если панель инструментов окна наблюдения не отображается, на вкладке Формулы в группе Аудит формул щелкните Окно наблюдения .
Выберите ячейки, которые вы хотите удалить.
Чтобы выбрать несколько ячеек, нажмите клавишу CTRL и щелкните ячейки.
org/ListItem»>
Нажмите Удалить часы .

Иногда сложно понять, как вложенная формула вычисляет окончательный результат, потому что есть несколько промежуточных вычислений и логических тестов. Однако с помощью диалогового окна Evaluate Formula можно увидеть, как различные части вложенной формулы оцениваются в том порядке, в котором вычисляется формула. Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50,СУММ(E2:E5),0) легче понять, если увидеть следующие промежуточные результаты:

В диалоговом окне «Вычислить формулу»	Описание
=ЕСЛИ(СРЕДНЕЕ(D2:D5)>50,СУММ(E2:E5),0)	Первоначально отображается вложенная формула. Функция СРЗНАЧ и функция СУММ вложены в функцию ЕСЛИ. Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому результатом функции СРЗНАЧ(D2:D5) является 40.
=ЕСЛИ(40>50;СУММ(Е2:Е5),0)	Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому результат функции СРЗНАЧ(D2:D5) равен 40.
=ЕСЛИ(ЛОЖЬ,СУММ(E2:E5),0)	Поскольку 40 не больше 50, выражение в первом аргументе функции ЕСЛИ (аргумент логическая_проверка) равно False. Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не оценивается, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только в том случае, если выражение имеет значение Истина.

Выберите ячейку, которую вы хотите оценить. Одновременно может быть оценена только одна ячейка.
Выберите вкладку Formulas > Проверка формул > Оцените формулу .
Щелкните Оценить , чтобы проверить значение подчеркнутой ссылки. Результат оценки выделен курсивом.
Если подчеркнутая часть формулы является ссылкой на другую формулу, щелкните Шаг с шагом, чтобы отобразить другую формулу в Оценочная коробка . Щелкните Step Out , чтобы вернуться к предыдущей ячейке и формуле.
Кнопка Step In недоступна для ссылки во второй раз, когда ссылка появляется в формуле, или если формула ссылается на ячейку в отдельной книге.
Продолжайте нажимать Вычислить , пока не будет оценена каждая часть формулы.
Чтобы снова просмотреть оценку, нажмите Перезапустить .
Чтобы завершить оценку, нажмите Close .

Примечания:

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

Нужна дополнительная помощь?

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

См. также

Отображение отношений между формулами и ячейками

Как избежать неработающих формул

Скрыть значения ошибок и индикаторы ошибок в ячейках

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

Формулы могут возвращать ошибки по многим причинам. Например, деление на 0 не допускается, и если ввести формулу =1/0, Excel вернет #ДЕЛ/0. Значения ошибок включают #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!.

Преобразование ошибки в ноль и использование формата для скрытия значения

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

Создать пример ошибки

Откройте пустую книгу или создайте новый лист.
Введите 3 в ячейку B1, введите 0 в ячейку C1 и в ячейку A1 введите формулу =B1/C1 .
#DIV/0! ошибка появляется в ячейке A1.
Выберите A1 и нажмите F2, чтобы изменить формулу.
После знака равенства (=) введите ЕСЛИОШИБКА и открывающую скобку.
ЕСЛИ ОШИБКА(
org/ListItem»>
Переместите курсор в конец формулы.
Введите ,0 ) – то есть запятая, за которой следует ноль и закрывающая скобка.
Формула =B1/C1 становится =ЕСЛИОШИБКА(B1/C1 ,0) .
Нажмите Enter, чтобы завершить формулу.
Содержимое ячейки теперь должно отображать 0 вместо #DIV! ошибка.

Применить условный формат

Выберите ячейку, содержащую ошибку, и на Главная Вкладка, нажмите Условное форматирование .
Щелкните Новое правило .
В диалоговом окне Новое правило форматирования щелкните Форматировать только те ячейки, которые содержат .
Под Форматировать только ячейки с , убедитесь, что Значение ячейки отображается в первом поле списка, , равное , появится во втором поле списка, а затем введите 0 в текстовом поле справа.
Нажмите кнопку Формат .
org/ListItem»>
Щелкните вкладку Номер и затем в разделе Категория щелкните Пользовательский .
В поле введите введите ;;; (три точки с запятой), а затем нажмите OK . Нажмите OK еще раз.
0 в ячейке исчезает. Это происходит потому, что ;;; Пользовательский формат приводит к тому, что любые числа в ячейке не отображаются. Однако фактическое значение (0) остается в ячейке.

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

Выберите диапазон ячеек, содержащих значение ошибки.
На вкладке Главная в группе Стили щелкните стрелку рядом с Условное форматирование и выберите Управление правилами .
Появится диалоговое окно Диспетчер правил условного форматирования .
Щелкните Новое правило .
Появится диалоговое окно Новое правило форматирования .
В разделе выберите тип правила , щелкните Форматировать только те ячейки, которые содержат .
В разделе Изменить описание правила в списке Форматировать только ячейки со списком выберите Ошибки .
Щелкните Формат , а затем щелкните вкладку Шрифт .
Щелкните стрелку, чтобы открыть список Color , и в разделе Theme Colors выберите белый цвет.

Могут быть случаи, когда вы не хотите, чтобы в ячейках отображались значения ошибок, и предпочли бы, чтобы вместо них отображалась текстовая строка, такая как «#Н/Д», тире или строка «Н/Д». Для этого можно использовать ЕСЛИОШИБКА и NA работает, как показано в следующем примере.

Описание функций

ЕСЛИОШИБКА Используйте эту функцию, чтобы определить, содержит ли ячейка ошибку или результат формулы вернет ошибку.

NA Используйте эту функцию, чтобы вернуть строку #N/A в ячейке. Синтаксис = NA() .

Щелкните отчет сводной таблицы.
Появляются инструменты сводной таблицы .
Excel 2016 и Excel 2013: на вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с Параметры и выберите Параметры .
Excel 2010 и Excel 2007: на Вкладка Параметры в группе Сводная таблица щелкните стрелку рядом с Параметры и выберите Параметры .
org/ListItem»>
Перейдите на вкладку Макет и формат и выполните одно или несколько из следующих действий:
Изменить отображение ошибок Установите флажок Для значений ошибок показывать под Формат . В поле введите значение, которое вы хотите отобразить вместо ошибок. Чтобы ошибки отображались как пустые ячейки, удалите все символы в поле.
Изменить отображение пустых ячеек Установите флажок Для пустых ячеек показывать . В поле введите значение, которое вы хотите отобразить в пустых ячейках. Чтобы отобразить пустые ячейки, удалите все символы в поле. Чтобы отображались нули, снимите флажок.

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

Ячейка с формулой задачи

В Excel 2016, Excel 2013 и Excel 2010: нажмите Файл > Опции > Формулы .
В Excel 2007: нажмите кнопку Microsoft Office > Параметры Excel > Формулы .
В разделе Проверка ошибок снимите флажок Включить фоновую проверку ошибок .

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

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

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

Условное форматирование обычно работает одинаково в диапазоне ячеек, таблице Excel или отчете сводной таблицы. Однако условное форматирование в отчете сводной таблицы имеет некоторые дополнительные особенности:

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

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

Метод	Используйте этот метод, если вы хотите выбрать
Область действия по выбору	org/ListItem»> Непрерывный набор полей в области значений, например, все итоговые значения продуктов для одного региона. Несмежный набор полей в области значений, например итоговые данные по продуктам для разных регионов на разных уровнях иерархии данных.
Область действия по полю значения	Не делайте много несмежных выделений. org/ListItem»> Условное форматирование набора полей в области значений для всех уровней иерархии данных. Включить промежуточные и общие итоги.
Область действия по соответствующему полю	При условном форматировании полей в области значений для верхнего, нижнего, выше среднего или ниже среднего значения правило по умолчанию основано на всех видимых значениях. Однако, когда вы ограничиваетесь соответствующим полем, вместо того, чтобы использовать все видимые значения, вы можете применить условный формат для каждой комбинации:

Примечание. Быстрый анализ недоступен в Excel 2010 и предыдущих версиях.

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

Выберите данные, которые вы хотите условно отформатировать. Кнопка Quick Analysis появляется в правом нижнем углу выделения.
Нажмите кнопку Быстрый анализ или нажмите Ctrl+Q.
В появившемся всплывающем окне на вкладке Форматирование наведите указатель мыши на различные параметры, чтобы увидеть предварительный просмотр данных в реальном времени, а затем щелкните нужный параметр форматирования.
Примечания:
- Параметры форматирования, отображаемые на вкладке Форматирование , зависят от выбранных вами данных. Если ваш выбор содержит только текст, доступны следующие параметры: Текст , Дублировать , Уникальный , Равен и Очистить . Если выделение содержит только числа или текст и числа, то вариантов 9.0005 Панели данных , Цвета , Наборы значков , Больше , Верхние 10% и Очистить .
- Интерактивный предварительный просмотр будет отображаться только для тех параметров форматирования, которые можно использовать с вашими данными. Например, если выбранные вами ячейки не содержат совпадающих данных и вы выбрали Дублировать , предварительный просмотр в реальном времени не будет работать.
Если появится диалоговое окно Текст, содержащий , введите параметр форматирования, который вы хотите применить, и нажмите OK .

Если вы хотите посмотреть видео, в котором показано, как использовать Quick Analysis для применения условного форматирования, см. Видео: Использование условного форматирования.

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

Скачать: Примеры условного форматирования в Excel

Цветовые шкалы

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

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

Совет: Если какие-либо ячейки в выделенном фрагменте содержат формулу, возвращающую ошибку, условное форматирование к этим ячейкам не применяется. Чтобы обеспечить применение условного форматирования к этим ячейкам, используйте функцию IS или ЕСЛИОШИБКА , чтобы вернуть значение, отличное от значения ошибки.

Быстрое форматирование

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

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

Расширенное форматирование

Выберите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная , в Стили , щелкните стрелку рядом с Условное форматирование и выберите Управление правилами . Появится диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из следующих действий:
- Чтобы добавить совершенно новый условный формат, нажмите Новое правило . Появится диалоговое окно Новое правило форматирования .
- Чтобы добавить новый условный формат на основе уже указанного, выберите правило, затем нажмите Дублировать правило . Дублированное правило появится в диалоговом окне. Выберите дубликат, затем выберите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
- Чтобы изменить условный формат, сделайте следующее:
  1. Убедитесь, что в списке Показать правила форматирования для выбран соответствующий рабочий лист, таблица или отчет сводной таблицы.
  2. При желании измените диапазон ячеек, нажав Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, выбрав новый диапазон ячеек на листе, а затем выбрав Развернуть диалоговое окно .
  3. Выберите правило и нажмите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
В соответствии с Применить правило к , чтобы дополнительно изменить область действия полей в области значений отчета сводной таблицы на:
- Выбор: Щелкните Выбранные ячейки .
- org/ListItem»>
  Все ячейки для метки значения: нажмите Все ячейки со значениями <метки значения> .
- Все ячейки для метки значения, исключая промежуточные итоги и общий итог: щелкните Все ячейки, показывающие значения <метки значения> для <метки строки> .
Ниже Выберите тип правила , нажмите Форматировать все ячейки на основе их значений (по умолчанию).
В разделе Изменить описание правила в списке Стиль формата выберите 2-цветная шкала .
Для выбора типа в поле Тип для Минимум и Максимум , выполните одно из следующих действий:
- Формат минимального и максимального значений: Выберите Минимальное значение и Максимальное значение .
  В этом случае вы не вводите Минимум и Максимум Значение .
- Форматирование числа, даты или времени: Выберите Число , а затем введите Минимум и Максимум Значение .
- Формат в процентах Процент: Введите Минимум и Максимум Значение .
  Допустимые значения от 0 (ноль) до 100. Не вводите знак процента.
  Используйте процентное значение, если вы хотите визуализировать все значения пропорционально, поскольку распределение значений пропорционально.
- Форматирование процентиля: Выберите Процентиль , а затем введите Минимум и Максимум Значение . Допустимые процентили от 0 (ноль) до 100.
  Используйте процентиль, если вы хотите визуализировать группу высоких значений (например, верхний 20-й процентиль) в одной пропорции цветовой градации и низких значений (например, нижний 20-й процентиль) в другой пропорции цветовой градации, поскольку они представляют крайние значения, которые могут исказить визуализацию ваших данных.
- Форматирование результата формулы: Выберите Формула , а затем введите значения для Минимум и Максимум .
  - Формула должна возвращать число, дату или значение времени.
  - Начать формулу со знака равенства (=).
  - Недопустимые формулы приводят к тому, что форматирование не применяется.
  - Рекомендуется протестировать формулу, чтобы убедиться, что она не возвращает значение ошибки.
    Примечания:
    - Убедитесь, что минимальное значение меньше максимального значения .
    - Вы можете выбрать другой тип для Минимум и Максимум . Например, вы можете выбрать число для Минимум процент для Максимум .
org/ListItem»>
Чтобы выбрать цветовую шкалу Минимум и Максимум , щелкните Цвет для каждой из них, а затем выберите цвет.
Если вы хотите выбрать дополнительные цвета или создать собственный цвет, нажмите Больше цветов . Выбранная цветовая шкала отображается в поле Preview .

Цветовые шкалы

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

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

Быстрое форматирование

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

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

Расширенное форматирование

Выберите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стили щелкните стрелку рядом с Условное форматирование и выберите Управление правилами . Появится диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из следующих действий:
- Чтобы добавить новый условный формат, щелкните Новое правило . Появится диалоговое окно Новое правило форматирования .
- Чтобы добавить новый условный формат на основе уже указанного, выберите правило, затем нажмите Дублировать правило . Повторяющееся правило копируется и отображается в диалоговом окне. Выберите дубликат, затем выберите Редактировать правило . Появится диалоговое окно «Редактировать правило форматирования ».
- Чтобы изменить условный формат, сделайте следующее:
  1. Убедитесь, что в списке Показать правила форматирования для выбран соответствующий рабочий лист, таблица или отчет сводной таблицы.
  2. При необходимости измените диапазон ячеек, нажав Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, выбрав новый диапазон ячеек на листе, а затем выбрав Развернуть диалоговое окно .
  3. Выберите правило и нажмите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
В соответствии с Применить правило к , чтобы дополнительно изменить область действия полей в области значений отчета сводной таблицы на:
- Выбор: Нажмите Только эти ячейки .
- org/ListItem»>
  Соответствующее поле: Щелкните Все ячейки <поле значения> с одинаковыми полями .
- Поле значений: Нажмите Все ячейки <поле значения> .
В разделе Выберите тип правила , щелкните Отформатируйте все ячейки на основе их значений .
В разделе Изменить описание правила в списке Стиль формата выберите 3-цветная шкала .
Выберите тип для Минимум , Средняя точка и Максимум . Выполните одно из следующих действий:
- Формат минимального и максимального значений: Выберите среднюю точку .
  В этом случае вы не вводите Минимальное значение и Максимальное значение .
- Форматирование значения числа, даты или времени: Выберите Число , а затем введите значение для Минимум , Средняя точка и Максимум .
- Форматирование в процентах: Выберите Процент , а затем введите значение для Минимум , Средняя точка и Максимум . Допустимые значения от 0 (ноль) до 100. Не вводите знак процента (%).
  Используйте процентное значение, если вы хотите визуализировать все значения пропорционально, поскольку использование процентного соотношения обеспечивает пропорциональное распределение значений.
- Форматирование процентиля: Выберите Процентиль , а затем введите значение для Минимум , Средняя точка и Максимум .
  Допустимые процентили от 0 (ноль) до 100.
  Используйте процентиль, если вы хотите визуализировать группу высоких значений (например, верхний 20-й процентиль) в одной пропорции цветовой шкалы и низких значений (например, нижний 20-й процентиль) в другой пропорции цветовой шкалы, поскольку они представляют крайние значения, которые может исказить визуализацию ваших данных.
- Форматирование результата формулы: Выберите Формула , а затем введите значение для Минимум , Середина и Максимум .
  Формула должна возвращать число, дату или значение времени. Начните формулу со знака равенства ( = ). Недопустимые формулы приводят к тому, что форматирование не применяется. Рекомендуется протестировать формулу, чтобы убедиться, что она не возвращает значение ошибки.
  Примечания:
  - Вы можете установить минимальное, среднее и максимальное значения для диапазона ячеек. Убедитесь, что значение в Минимум меньше значения в Середина , которое, в свою очередь, меньше значения в Максимум .
  - Вы можете выбрать другой тип для Минимум , Средняя точка и Максимум . Например, вы можете выбрать минимум число, середина процентиль и максимум процентов.
  - org/ListItem»>
    Во многих случаях значение по умолчанию Midpoint , равное 50 процентам, работает лучше всего, но его можно изменить в соответствии с уникальными требованиями.
Чтобы выбрать цветовую шкалу Минимум , Средняя точка и Максимум , щелкните Цвет для каждой из них, а затем выберите цвет.

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

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

Совет: Если какие-либо ячейки в диапазоне содержат формулу, которая возвращает ошибку, условное форматирование к этим ячейкам не применяется. Чтобы обеспечить применение условного форматирования к этим ячейкам, используйте функцию IS или ЕСЛИОШИБКА , чтобы вернуть значение (например, 0 или «Н/Д») вместо значения ошибки.

Быстрое форматирование

org/ListItem»>
Выберите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стиль щелкните стрелку рядом с Условное форматирование , щелкните Панели данных , а затем выберите значок панели данных.

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

Расширенное форматирование

Выберите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стили щелкните стрелку рядом с Условное форматирование и выберите Управление правилами . Появится диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из следующих действий:
- Чтобы добавить условный формат, щелкните Новое правило . Появится диалоговое окно Новое правило форматирования .
- org/ListItem»>
  Чтобы добавить новый условный формат на основе уже указанного, выберите правило, затем нажмите Дублировать правило . Повторяющееся правило копируется и отображается в диалоговом окне. Выберите дубликат, затем выберите Редактировать правило . Появится диалоговое окно «Редактировать правило форматирования ».
- Чтобы изменить условный формат, сделайте следующее:
- 1. Убедитесь, что в списке выбран соответствующий рабочий лист, таблица или отчет сводной таблицы. 0005 Показать правила форматирования для списка .
  2. При необходимости измените диапазон ячеек, нажав Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, выбрав новый диапазон ячеек на листе, а затем выбрав Развернуть диалоговое окно .
  3. Выберите правило и нажмите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
org/ListItem»>
В соответствии с Применить правило к , чтобы дополнительно изменить область действия полей в области значений отчета сводной таблицы на:
- Выбор: Нажмите Только эти ячейки .
- Соответствующее поле: Щелкните Все ячейки <поле значения> с одинаковыми полями .
- Поле значения: Нажмите Все ячейки <поле значения> .
В разделе Выберите тип правила , щелкните Отформатируйте все ячейки на основе их значений .
Под Изменить описание правила , в списке Стиль формата выберите Панель данных .
Выберите минимум и максимум Введите . Выполните одно из следующих действий:
- Форматировать наименьшее и наибольшее значения: Выбрать Наименьшее значение и Наибольшее значение .
  В этом случае вы не вводите значение для Минимум и Максимум .
- Форматирование числа, даты или времени: Выберите Число , а затем введите Минимум и Максимум Значение .
- Форматирование в процентах: Выберите Процент , а затем введите значение для Минимум и Максимум .
  Допустимые значения от 0 (ноль) до 100. Не вводите знак процента (%).
  Используйте процентное значение, если вы хотите визуализировать все значения пропорционально, поскольку использование процентного соотношения обеспечивает пропорциональное распределение значений.
- Форматирование процентиля Выберите Процентиль , а затем введите значение для Минимум и Максимум .
  Допустимые процентили от 0 (ноль) до 100.
  Используйте процентиль, если вы хотите визуализировать группу высоких значений (например, верхний 20-й процентиль) в одной пропорции гистограммы и низких значений (например, нижний 20-й процентиль) в другой пропорции гистограммы, поскольку они представляют экстремальные значения, которые может исказить визуализацию ваших данных.
- Форматирование результата формулы Выберите Formula , а затем введите значение для Минимум и Максимум .
  - Формула должна возвращать число, дату или значение времени.
  - Начните формулу со знака равенства ( = ).
  - Недопустимые формулы приводят к тому, что форматирование не применяется.
  - Рекомендуется протестировать формулу, чтобы убедиться, что она не возвращает значение ошибки.
Примечания:
- Убедитесь, что минимальное значение меньше максимального значения .
- Вы можете выбрать другой тип для Минимум и Максимум . Например, вы можете выбрать минимум число и максимум процентов.
Чтобы выбрать цветовую шкалу Минимум и Максимум , нажмите Цвет полосы .
Если вы хотите выбрать дополнительные цвета или создать собственный цвет, нажмите Больше цветов . Выбранный цвет полосы отображается в поле Preview .
Чтобы показать только гистограмму данных, а не значение в ячейке, выберите Показать только гистограмму .
Чтобы применить сплошную границу к гистограммам, выберите Solid Border в списке Border и выберите цвет границы.
Чтобы выбрать сплошную полосу или полосу с градиентом, выберите Сплошная заливка или Градиентная заливка в списке Заливка .
Чтобы отформатировать отрицательные полосы, щелкните Отрицательное значение и ось , а затем в диалоговом окне Отрицательное значение и настройки оси выберите параметры заливки отрицательного столбца и цвета границ. Вы можете выбрать настройки положения и цвет оси. Когда вы закончите выбирать параметры, нажмите ОК .
Вы можете изменить направление баров, выбрав настройку в списке Bar Direction . По умолчанию установлено значение Context , но вы можете выбирать между направлением слева направо и справа налево, в зависимости от того, как вы хотите представить свои данные.

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

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

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

Вы можете выбрать отображение значков только для ячеек, соответствующих условию; например, отображение значка предупреждения для тех ячеек, значения которых ниже критического значения, и отсутствие значков для тех ячеек, которые превышают его. Для этого вы скрываете значки, выбирая No Cell Icon из раскрывающегося списка значков рядом со значком при настройке условий. Вы также можете создать свою собственную комбинацию наборов значков; например, зеленая галочка «символ», желтый «светофор» и красный «флажок».

Совет: Если какие-либо ячейки в выделенном фрагменте содержат формулу, возвращающую ошибку, условное форматирование к этим ячейкам не применяется. Чтобы обеспечить применение условного форматирования к этим ячейкам, используйте функцию IS или ЕСЛИОШИБКА , чтобы вернуть значение (например, 0 или «Н/Д») вместо значения ошибки.

Быстрое форматирование

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

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

Расширенное форматирование

Выберите ячейки, которые вы хотите условно отформатировать.
В Доме , в группе Стили щелкните стрелку рядом с Условное форматирование и выберите Управление правилами . Появится диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из следующих действий:
- Чтобы добавить условный формат, нажмите Новое правило . Появится диалоговое окно Новое правило форматирования .
- Чтобы добавить новый условный формат на основе уже указанного, выберите правило, затем нажмите Дублировать правило . Повторяющееся правило копируется и отображается в диалоговом окне. Выберите дубликат, затем выберите Изменить правило . Правило форматирования 9Появится диалоговое окно 0006.
- Чтобы изменить условный формат, сделайте следующее:
  1. Убедитесь, что в списке Показать правила форматирования для выбран соответствующий рабочий лист, таблица или отчет сводной таблицы.
  2. При необходимости измените диапазон ячеек, нажав Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, выбрав новый диапазон ячеек на листе, а затем выбрав Развернуть диалоговое окно .
  3. Выберите правило и нажмите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования «.
В соответствии с Применить правило к , чтобы дополнительно изменить область действия полей в области значений отчета сводной таблицы на:
- Выбор: Нажмите Только эти ячейки .
- org/ListItem»>
  Соответствующее поле: Щелкните Все ячейки <поле значения> с одинаковыми полями .
- Поле значения: Нажмите Все ячейки <поле значения> .
В разделе Выберите тип правила , щелкните Отформатируйте все ячейки на основе их значений .
В разделе Изменить описание правила в списке Стиль формата выберите Набор значков .
1. Выберите набор значков. По умолчанию используется 3 светофора (без оправы) . Количество значков, операторы сравнения по умолчанию и пороговые значения для каждого значка могут различаться для каждого набора значков.
2. Можно настроить операторы сравнения и пороговые значения. Диапазон значений по умолчанию для каждого значка одинакового размера, но вы можете настроить их в соответствии со своими уникальными требованиями. Убедитесь, что пороговые значения находятся в логической последовательности от самого высокого к самому низкому сверху вниз.
3. org/ListItem»>
  Выполните одно из следующих действий:
  Форматирование числа, даты или времени:     Выбрать Число .
  Форматировать в процентах:     Выбрать Процент .
  Допустимые значения от 0 (ноль) до 100. Не вводите знак процента (%).
  Используйте процентное значение, если вы хотите визуализировать все значения пропорционально, поскольку использование процентного соотношения обеспечивает пропорциональное распределение значений.
  Форматирование процентиля:     Выберите Процентиль . Допустимые процентили от 0 (ноль) до 100.
  Используйте процентиль, если вы хотите визуализировать группу высоких значений (например, верхний 20-й процентиль) с помощью определенного значка и низких значений (например, нижний 20-й процентиль) с помощью другого значка, так как они представляют экстремальные значения, которые могут исказить визуализация ваших данных.
  Форматирование результата формулы:     Выберите Formula , а затем введите формулу в каждое поле Value .
  Формула должна возвращать число, дату или значение времени.
  Начинайте формулу со знака равенства (=).
  Недопустимые формулы приводят к тому, что форматирование не применяется.
  Рекомендуется протестировать формулу, чтобы убедиться, что она не возвращает значение ошибки.
4. Чтобы первый значок представлял более низкие значения, а последний значок представлял более высокие значения, выберите Обратный порядок значков .
5. Чтобы отобразить только значок, а не значение в ячейке, выберите Показать только значок .
  Примечания:
  - Возможно, вам потребуется настроить ширину столбца, чтобы разместить значок.
  - org/ListItem»>
    Размер отображаемого значка зависит от размера шрифта, используемого в этой ячейке. При увеличении размера шрифта пропорционально увеличивается размер значка.

Чтобы упростить поиск определенных ячеек, вы можете отформатировать их с помощью оператора сравнения. Например, на листе инвентаризации, отсортированном по категориям, вы можете выделить желтым цветом продукты, в которых имеется менее 10 наименований. Или на сводной таблице розничных магазинов можно указать все магазины с прибылью более 10 %, объемом продаж менее 100 000 долларов США и регионом, равным «Юго-Восток».

Показанные здесь примеры работают с примерами встроенных критериев условного форматирования, таких как «Больше чем» и «Верхний %. Это форматирует города с населением более 2 000 000 человек с зеленым фоном и средними высокими температурами в верхних 30% с оранжевым цветом.

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

Быстрое форматирование

Выберите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стиль щелкните стрелку рядом с Условное форматирование и выберите Правила выделения ячеек .
org/ListItem»>
Выберите нужную команду, например Между , Равно тексту, содержащему , или Дата возникновения .
Введите значения, которые вы хотите использовать, а затем выберите формат.

Если вы хотите просмотреть видеоролики об этих методах, см. разделы Видео: условное форматирование текста и Видео: условное форматирование дат.

Расширенное форматирование

org/ListItem»>
Выберите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке «Главная» в группе «Стили» щелкните стрелку рядом с «Условное форматирование» и выберите «Управление правилами». Появится диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из следующих действий:
- Чтобы добавить условный формат, щелкните Новое правило . Появится диалоговое окно Новое правило форматирования .
- Чтобы добавить новый условный формат на основе уже указанного, выберите правило, затем нажмите Дублировать правило . Повторяющееся правило копируется и отображается в диалоговом окне. Выберите дубликат, затем выберите Редактировать правило . Появится диалоговое окно «Редактировать правило форматирования ».
- Чтобы изменить условный формат, сделайте следующее:
- 1. org/ListItem»>
    Убедитесь, что в списке выбран соответствующий рабочий лист, таблица или отчет сводной таблицы.0005 Показать правила форматирования для списка .
  2. При необходимости измените диапазон ячеек, щелкнув Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, выбрав новый диапазон ячеек на рабочем листе или на других рабочих листах, а затем выбрав Развернуть диалоговое окно .
  3. Выберите правило и нажмите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
org/ListItem»>
В соответствии с Применить правило к , чтобы дополнительно изменить область действия полей в области значений отчета сводной таблицы:
Выбор:     Нажмите Только эти ячейки .
Соответствующее поле:     Щелкните Все ячейки <поле значения> с одинаковыми полями .
Поле значения:     Нажмите Все ячейки <поле значения> .
В разделе выберите тип правила , щелкните Форматировать только те ячейки, которые содержат .
В разделе Изменить описание правила , в ячейках формата только со списком выполните одно из следующих действий:
- Форматировать по числу, дате или времени: Выберите Значение ячейки , выберите оператор сравнения, а затем введите число, дату или время.
  Например, выберите Между и введите 100 и 200 или выберите Равно и введите 1/1/2009 .
  Вы также можете ввести формулу, которая возвращает значение числа, даты или времени.
  - Если вы вводите формулу, начинайте ее со знака равенства (=).
  - Недопустимые формулы приводят к тому, что форматирование не применяется.
  - Рекомендуется протестировать формулу, чтобы убедиться, что она не возвращает значение ошибки.
- org/ListItem»>
  Формат по тексту: Выберите Особый текст , выбрав оператор сравнения, а затем введите текст.
  Например, выберите Содержит , а затем введите Silver или выберите Начиная с , а затем введите Tri .
  В строку поиска включены кавычки, и вы можете использовать подстановочные знаки. Максимальная длина строки составляет 255 символов.
  Вы также можете ввести формулу, которая возвращает текст.
  Если вы вводите формулу, начинайте ее со знака равенства (=).
  Недопустимые формулы приводят к тому, что форматирование не применяется.
  Рекомендуется протестировать формулу, чтобы убедиться, что она не возвращает значение ошибки.
  Чтобы просмотреть видео об этой методике, см. Видео: условное форматирование текста.
- Формат по дате: Выберите Даты возникновения , а затем выберите сравнение дат.
  Например, выберите Вчера или На следующей неделе .
  Чтобы просмотреть видео об этой методике, см. Видео: условное форматирование дат.
- org/ListItem»>
  Форматировать ячейки с пробелами или без пробелов: Выбрать Пробелы или Без пробелов .
  Пустое значение — это ячейка, которая не содержит данных и отличается от ячейки, содержащей один или несколько пробелов (пробелы считаются текстом).
- Форматировать ячейки с ошибками или без ошибок: Выбрать Ошибки или Нет ошибок .
  Значения ошибок включают: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM! и #NULL!.
Чтобы указать формат, нажмите Формат . Появится диалоговое окно Формат ячеек .
Выберите номер, шрифт, границу или формат заливки, которые вы хотите применить, когда значение ячейки соответствует условию, а затем нажмите ОК .
Вы можете выбрать более одного формата. Выбранные форматы отображаются в поле Preview .

Вы можете найти самые высокие и самые низкие значения в диапазоне ячеек, основанных на указанном вами пороговом значении. Например, вы можете найти 5 самых продаваемых продуктов в региональном отчете, 15 % продуктов с самым низким доходом в опросе клиентов или 25 самых высоких зарплат в отделе.

Быстрое форматирование

org/ItemList»>
Выберите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стиль щелкните стрелку рядом с Условное форматирование и выберите Верхние/нижние правила .
Выберите нужную команду, например, Верхние 10 элементов или Нижние 10 % .
Введите значения, которые вы хотите использовать, а затем выберите формат.

Расширенное форматирование

Выберите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На Вкладка Главная в группе Стили щелкните стрелку рядом с Условное форматирование и выберите Управление правилами . Появится диалоговое окно Диспетчер правил условного форматирования .
org/ListItem»>
Выполните одно из следующих действий:
Чтобы добавить условный формат, нажмите Новое правило . Появится диалоговое окно Новое правило форматирования .
Чтобы добавить новый условный формат на основе уже указанного, выберите правило, затем нажмите Дублировать правило . Повторяющееся правило копируется и отображается в диалоговом окне. Выберите дубликат, затем выберите Изменить правило . Правило форматирования 9Появится диалоговое окно 0006.
Чтобы изменить условный формат, сделайте следующее:
org/ListItem»>
Убедитесь, что в списке Показать правила форматирования для выбран соответствующий рабочий лист, таблица или отчет сводной таблицы.
При необходимости измените диапазон ячеек, нажав Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, выбрав новый диапазон ячеек на листе, а затем выбрав Развернуть диалоговое окно .
Выберите правило и нажмите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
В соответствии с Применить правило к , чтобы при необходимости изменить поля области в области значений отчета сводной таблицы на:
- Выбор: Нажмите Только эти ячейки .
- Соответствующее поле: Щелкните Все ячейки <поле значения> с одинаковыми полями .
- Поле значения: Нажмите Все ячейки <поле значения> .
В разделе Выберите тип правила , нажмите Форматировать только верхние или нижние ранговые значения .
Под Изменить описание правила , в значениях формата , которые ранжируются в выберите Top или Bottom .
org/ListItem»>
Выполните одно из следующих действий:
Чтобы указать верхнее или нижнее число, введите число, а затем снимите флажок % выбранного диапазона . Допустимые значения: от 1 до 1000.
Чтобы указать верхний или нижний процент, введите число, а затем выберите поле % выбранного диапазона . Допустимые значения: от 1 до 100.
При необходимости измените способ применения формата к полям в области «Значения» отчета сводной таблицы, область действия которых ограничена соответствующим полем.
По умолчанию условный формат основан на всех видимых значениях. Однако, когда вы ограничиваетесь соответствующим полем, вместо использования всех видимых значений вы можете применить условный формат для каждой комбинации:
- Столбец и его родительское поле строки, выбрав каждую группу столбцов .
- Строка и поле ее родительского столбца путем выбора каждой группы строк .
Чтобы указать формат, нажмите Формат . Появится диалоговое окно Формат ячеек .
Выберите номер, шрифт, границу или формат заливки, которые вы хотите применить, когда значение ячейки соответствует условию, а затем нажмите ОК .
Вы можете выбрать более одного формата. Выбранные форматы отображаются в поле Preview .

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

Быстрое форматирование

org/ItemList»>
Выберите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стиль щелкните стрелку рядом с Условное форматирование и выберите Верхние/нижние правила .
Выберите нужную команду, например, Выше среднего или Ниже среднего .
Введите значения, которые вы хотите использовать, а затем выберите формат.

Расширенное форматирование

Выберите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На Вкладка Главная в группе Стили щелкните стрелку рядом с Условное форматирование и выберите Управление правилами . Появится диалоговое окно Диспетчер правил условного форматирования .
org/ListItem»>
Выполните одно из следующих действий:
Чтобы добавить условный формат, нажмите Новое правило . Появится диалоговое окно Новое правило форматирования .
Чтобы добавить новый условный формат на основе уже указанного, выберите правило, затем нажмите Дублировать правило . Повторяющееся правило копируется и отображается в диалоговом окне. Выберите дубликат, затем выберите Изменить правило . Правило форматирования 9Появится диалоговое окно 0006.
Чтобы изменить условный формат, сделайте следующее:
org/ListItem»>
Убедитесь, что в списке Показать правила форматирования для выбран соответствующий рабочий лист, таблица или отчет сводной таблицы.
При необходимости измените диапазон ячеек, нажав Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, выбрав новый диапазон ячеек на листе, а затем выбрав Развернуть диалоговое окно .
Выберите правило и нажмите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
В соответствии с Применить правило к , чтобы дополнительно изменить область действия полей в области значений отчета сводной таблицы на:
- Выбор: Нажмите Только эти ячейки .
- Соответствующее поле: Щелкните Все ячейки <поле значения> с одинаковыми полями .
- Поле значения: Нажмите Все ячейки <поле значения> .
Под Выберите тип правила , щелкните Форматировать только значения выше или ниже среднего .
Под Изменить описание правила , в Отформатируйте значения из списка , выполните одно из следующих действий:
- Чтобы отформатировать ячейки, которые выше или ниже среднего для всех ячеек в диапазоне, выберите Выше или Ниже .
- Чтобы отформатировать ячейки, которые выше или ниже одного, двух или трех стандартных отклонений для всех ячеек в диапазоне, выберите стандартное отклонение.
При необходимости измените способ применения формата к полям в области «Значения» отчета сводной таблицы, область действия которых ограничена соответствующим полем.
По умолчанию условное форматирование основано на всех видимых значениях. Однако, когда вы ограничиваетесь соответствующим полем, вместо использования всех видимых значений вы можете применить условный формат для каждой комбинации:
- org/ListItem»>
  Столбец и его родительское поле строки, выбрав каждую группу столбцов .
- Строка и поле ее родительского столбца, путем выбора каждой группы строк .
Щелкните Формат , чтобы открыть диалоговое окно Формат ячеек .
Выберите число, шрифт, границу или формат заливки, которые вы хотите применить, когда значение ячейки соответствует условию, а затем нажмите OK .
Вы можете выбрать более одного формата. Выбранные форматы отображаются в поле Preview .

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

В приведенном здесь примере условное форматирование используется в столбце «Преподаватель» для поиска инструкторов, которые преподают более одного класса (повторяющиеся имена инструкторов выделены бледно-красным цветом). Значения оценки, встречающиеся только один раз в столбце Оценка (уникальные значения), выделяются зеленым цветом.

Быстрое форматирование

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

Расширенное форматирование

org/ItemList»>
Выберите ячейки, которые вы хотите условно отформатировать.
На вкладке Главная в группе Стили щелкните стрелку рядом с Условное форматирование и выберите Управление правилами . Появится диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из следующих действий:
Чтобы добавить условный формат, щелкните Новое правило . Появится диалоговое окно Новое правило форматирования .
Чтобы добавить новый условный формат на основе уже указанного, выберите правило, затем нажмите Дублировать правило . Повторяющееся правило копируется и отображается в диалоговом окне. Выберите дубликат, затем выберите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
Чтобы изменить условный формат, сделайте следующее:
org/ItemList»>
Убедитесь, что в списке Показать правила форматирования для выбран соответствующий рабочий лист или таблица.
При необходимости измените диапазон ячеек, щелкнув Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, выбрав новый диапазон ячеек на листе, а затем выбрав Расширенный диалог .
Выберите правило и нажмите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
org/ListItem»>
Ниже Выберите тип правила , щелкните Форматировать только уникальные или повторяющиеся значения .
В разделе Изменить описание правила в списке Форматировать все выберите уникальный или дубликат .
Щелкните Формат , чтобы открыть диалоговое окно Формат ячеек .
Выберите число, шрифт, границу или формат заливки, которые вы хотите применить, когда значение ячейки соответствует условию, а затем нажмите OK .
Вы можете выбрать более одного формата. Выбранные форматы отображаются в поле Preview .

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

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

Выберите Главная > Условное форматирование > Управление правилами , затем в диалоговом окне Диспетчер правил условного форматирования выберите правило из списка, а затем выберите Повторяющееся правило . Повторяющееся правило появится в списке.
Выберите повторяющееся правило, затем выберите Изменить правило .

Выберите ячейки, которые вы хотите отформатировать.
На вкладке Главная щелкните Условное форматирование > Новое правило .
Создайте правило и укажите параметры его формата, затем нажмите OK .
Если вы не видите нужных параметров, вы можете использовать формулу, чтобы определить, какие ячейки нужно отформатировать — шаги см. в следующем разделе).

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

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

Совет. Если какие-либо ячейки содержат формулу, возвращающую ошибку, условное форматирование к этим ячейкам не применяется. Чтобы решить эту проблему, используйте 9Функция IS 0005 или функция ЕСЛИОШИБКА в формуле возвращает указанное вами значение (например, 0 или «Н/Д») вместо значения ошибки.

На вкладке Главная в группе Стили щелкните стрелку рядом с Условное форматирование и выберите Управление правилами .
Появится диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из следующих действий:
- Чтобы добавить условный формат, щелкните Новое правило . Появится диалоговое окно Новое правило форматирования .
- Чтобы добавить новый условный формат на основе уже указанного, выберите правило, затем нажмите Дублировать правило . Повторяющееся правило копируется и отображается в диалоговом окне. Выберите дубликат, затем выберите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
- Чтобы изменить условный формат, сделайте следующее:
- 1. Убедитесь, что в списке Показать правила форматирования для выбран соответствующий рабочий лист, таблица или отчет сводной таблицы.
  2. org/ListItem»>
    При необходимости измените диапазон ячеек, нажав Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, выбрав новый диапазон ячеек на рабочем листе или других рабочих листах, а затем щелкнув Развернуть диалоговое окно .
  3. Выберите правило и нажмите Изменить правило . Появится диалоговое окно «Редактировать правило форматирования ».
В разделе Применить правило к , чтобы при необходимости изменить область действия полей в области значений отчета сводной таблицы, выполните следующие действия:
- org/ListItem»>
  Для выбора области: Щелкните Выбранные ячейки .
- Для охвата по соответствующему полю: Щелкните Все ячейки, отображающие значения <поля значений> .
- Для области по полю значения: Щелкните Все ячейки, отображающие <поле значений> для <строки> .

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

В разделе Изменить описание правила в поле со списком Значения формата , где эта формула верна , введите формулу.
Вы должны начать формулу со знака равенства (=), и формула должна возвращать логическое значение ИСТИНА (1) или ЛОЖЬ (0).
Щелкните Формат , чтобы открыть диалоговое окно Формат ячеек .

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

Вы можете выбрать более одного формата. Выбранные форматы отображаются в Превью коробка.

Пример 1. Использование двух условных форматов с критериями, в которых используются тесты И и ИЛИ

В следующем примере показано использование двух правил условного форматирования. Если первое правило не применяется, применяется второе правило.

Первое правило: покупатель дома заложил в бюджет до 75 000 долларов в качестве первоначального взноса и 1 500 долларов в месяц в качестве платежа по ипотеке. Если и авансовый платеж, и ежемесячные платежи соответствуют этим требованиям, ячейки B4 и B5 имеют зеленый цвет.

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

Формула для первого правила (применяется зеленый цвет)	=И(ЕСЛИ($B$4<=75000,1),ЕСЛИ(ABS($B$5)<=1500,1))
Формула для второго правила (применяется красный цвет)	=ИЛИ(ЕСЛИ($B$4>=75000,1),ЕСЛИ(ABS($B$5)>=1500,1))

Пример 2. Затенение каждой второй строки с помощью функций MOD и ROW

Условный формат, примененный к каждой ячейке на этом листе, затеняет каждую вторую строку в диапазоне ячеек синим цветом. Вы можете выбрать все ячейки на листе, щелкнув квадрат над строкой 1 и слева от столбца A. Функция MOD возвращает остаток после деления числа (первый аргумент) на делитель (второй аргумент). Функция ROW возвращает номер текущей строки. Когда вы делите текущий номер строки на 2, вы всегда получаете либо остаток 0 для четного числа, либо остаток 1 для нечетного числа. Поскольку 0 — это ЛОЖЬ, а 1 — ИСТИНА, каждая нечетная строка форматируется. В правиле используется следующая формула: =MOD(ROW(),2)=1.

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

В следующем видеоролике показаны основы использования формул с условным форматированием.

Если вы хотите применить существующий стиль форматирования к новым или другим данным на листе, вы можете использовать Format Painter для копирования условного форматирования в эти данные.

Щелкните ячейку с условным форматированием, которое вы хотите скопировать.
org/ListItem»>
Щелкните Главная > Формат по образцу .
Указатель превращается в кисть.
Совет: Вы можете дважды щелкнуть Формат по образцу , если вы хотите продолжать использовать кисть для вставки условного форматирования в другие ячейки.
Чтобы вставить условное форматирование, проведите кистью по ячейкам или диапазонам ячеек, которые вы хотите отформатировать.
Чтобы прекратить использование кисти, нажмите Esc.

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

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

Найти все ячейки с условным форматом

Щелкните любую ячейку, не имеющую условного формата.
org/ListItem»>
В Доме в группе Редактирование щелкните стрелку рядом с Найти и выбрать , а затем щелкните Условное форматирование .

Найти только ячейки с одинаковым условным форматом

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

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

Подробнее о приоритете правил условного форматирования
Вы создаете, редактируете, удаляете и просматриваете все правила условного форматирования в книге с помощью диалогового окна Диспетчер правил условного форматирования . (На вкладке Главная щелкните Условное форматирование , а затем щелкните Управление правилами .)
Появится диалоговое окно Диспетчер правил условного форматирования .
Если применяются два или более правил условного форматирования, эти правила оцениваются в порядке приоритета (сверху вниз) в зависимости от того, как они перечислены в этом диалоговом окне.
Вот пример, в котором указаны даты истечения срока действия идентификационных карт. Мы хотим отметить значки, срок действия которых истекает в течение 60 дней, но срок действия которых еще не истек, желтым цветом фона, а значки с истекшим сроком действия — красным цветом фона.
В этом примере ячейки с идентификационными номерами сотрудников, срок действия сертификатов которых истекает в течение 60 дней, отображаются желтым цветом, а идентификационные номера сотрудников с истекшим сроком действия сертификатов — красным. Правила показаны на следующем изображении.
Первое правило (которое, если оно равно True, устанавливает красный цвет фона ячейки) проверяет значение даты в столбце B на соответствие текущей дате (полученной с помощью функции СЕГОДНЯ в формуле). Назначьте формулу первому значению данных в столбце B, то есть B2. Формула для этого правила: =B2<СЕГОДНЯ () . Эта формула проверяет ячейки в столбце B (ячейки B2:B15). Если формула для какой-либо ячейки в столбце B оценивается как True, соответствующая ячейка в столбце A (например, A5 соответствует B5, A11 соответствует B11) форматируется с красным цветом фона. После того, как все ячейки, указанные под Применяется к оценивается с этим первым правилом, проверяется второе правило. Эта формула проверяет, являются ли значения в столбце B менее 60 дней от текущей даты (например, предположим, что сегодняшняя дата — 11.08.2010). Ячейке в B4, 04.10.2010, меньше 60 дней с сегодняшнего дня, поэтому она оценивается как True и имеет желтый цвет фона. Формула для этого правила: =B2 . Любая ячейка, которая сначала была отформатирована красным в соответствии с наивысшим правилом в списке, остается нетронутой.
Правило выше в списке имеет больший приоритет, чем правило ниже в списке. По умолчанию новые правила всегда добавляются в начало списка и, следовательно, имеют более высокий приоритет, поэтому вам следует следить за их порядком. Порядок приоритета можно изменить с помощью стрелок Вверх и Вниз в диалоговом окне.
Что происходит, когда несколько правил условного форматирования оцениваются как True
Иногда у вас есть более одного правила условного форматирования, которое оценивается как True. Вот как применяются правила, сначала когда правила не конфликтуют, а затем когда они конфликтуют:
Когда правила не конфликтуют Например, если одно правило форматирует ячейку полужирным шрифтом, а другое правило форматирует ту же ячейку красным цветом, ячейка форматируется и полужирным шрифтом, и красным цветом. Поскольку между двумя форматами нет конфликта, применяются оба правила.
Когда правила конфликтуют Например, одно правило устанавливает красный цвет шрифта ячейки, а другое правило устанавливает зеленый цвет шрифта ячейки. Поскольку два правила противоречат друг другу, может применяться только одно из них. Применяется правило с более высоким приоритетом (выше в списке в диалоговом окне).
Как вставка, заливка и формат по образцу влияют на правила условного форматирования
При редактировании рабочего листа вы можете копировать и вставлять значения ячеек с условным форматом, заполнять диапазон ячеек условным форматом или использовать средство форматирования. Эти операции могут повлиять на приоритет правил условного форматирования следующим образом: для ячеек назначения создается новое правило условного форматирования, основанное на исходных ячейках.
Если вы копируете и вставляете значения ячеек с условным форматом на лист, открытый в другом экземпляре Excel (другой процесс Excel.exe, запущенный в то же время на компьютере), правило условного форматирования не создается в другом экземпляре, а формат не копируется в этот экземпляр.
Что происходит, когда условный формат и ручное форматирование конфликтуют
Если правило условного форматирования оценивается как True, оно имеет приоритет над любым существующим ручным форматированием для того же выбора. Это означает, что если они конфликтуют, применяется условное форматирование, а не ручное форматирование. Если вы удалите правило условного форматирования, ручное форматирование диапазона ячеек останется.
Форматирование вручную не указано в Диспетчере правил условного форматирования и не используется для определения приоритета.

Управление остановкой оценки правила с помощью флажка Stop If True

Для обратной совместимости с версиями Excel, предшествующими Excel 2007, вы можете установить флажок Stop If True в Manage Rules для имитации того, как условное форматирование может отображаться в тех более ранних версиях Excel, которые не поддерживают более трех правил условного форматирования или несколько правил, применяемых к одному и тому же диапазону.

Например, если у вас есть более трех правил условного форматирования для диапазона ячеек и вы работаете с версией Excel, предшествующей Excel 2007, эта версия Excel:

org/ListItem»>
Оценивает только первые три правила.
Применяет первое правило приоритета, которое имеет значение True.
Игнорирует правила с более низким приоритетом, если они имеют значение True.

В следующей таблице приведены все возможные условия для первых трех правил:

Если правило	это	А если правило	это	А если правило	это	Затем
Один	Правда	Два	Правда или Ложь	Три	Правда или Ложь	Применяется первое правило, а второе и третье правила игнорируются.
Один	Ложь	Два	Правда	Три	Правда или Ложь	Применяется второе правило, а третье игнорируется.
Один	Ложь	Два	Ложь	Три	Правда	Применяется третье правило.
Один	Ложь	Два	Ложь	Три	Ложь	Правила не применяются.

Вы можете установить или снять флажок Stop If True , чтобы изменить поведение по умолчанию:

org/ListItem»>
Чтобы оценить только первое правило, установите флажок Stop If True для первого правила.
Чтобы оценить только первое и второе правила, установите флажок Stop If True для второго правила.

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

Если вы хотите посмотреть видео, показывающее, как управлять правилами условного форматирования, см. Видео: Управление условным форматированием.

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

На вкладке Главная в группе Стили щелкните стрелку рядом с Условное форматирование и выберите Управление правилами .
Появится диалоговое окно Диспетчер правил условного форматирования .
Отображаются правила условного форматирования для текущего выделения, включая тип правила, формат, диапазон ячеек, к которым применяется правило, и Остановить, если значение True .
Если вы не видите нужное правило, в списке Показать правила форматирования для убедитесь, что выбран правильный диапазон ячеек, рабочий лист, таблица или отчет сводной таблицы.
Выберите правило. Одновременно можно выбрать только одно правило.
Чтобы переместить выбранное правило вверх по приоритету, нажмите Переместить вверх . Чтобы переместить выбранное правило вниз по приоритету, нажмите Вниз .
При необходимости, чтобы остановить оценку правила для определенного правила, установите флажок Stop If True .

Удалить условное форматирование на листе

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

За весь рабочий лист

В диапазоне ячеек

Выберите ячейки, содержащие условное форматирование.
Нажмите кнопку Quick Analysis Lens , которая появится в правом нижнем углу выбранных данных.
Примечания: Линза быстрого анализа не будет видна, если:
- org/ListItem»>
  Все ячейки в выбранном диапазоне пусты или
- Имеется запись только в верхней левой ячейке выбранного диапазона, при этом все остальные ячейки в диапазоне пусты.
Щелкните Очистить формат .

Поиск и удаление одинаковых условных форматов на листе

org/ListItem»>
Щелкните ячейку с условным форматом, который вы хотите удалить на всем листе.
На вкладке Home щелкните стрелку рядом с Найдите и выберите , а затем щелкните Перейти к специальному .
Нажмите Условные форматы .
Нажмите То же под Проверка данных . чтобы выбрать все ячейки, содержащие одинаковые правила условного форматирования.
org/ListItem»>
На вкладке Главная щелкните Условное форматирование > Очистить правила > Очистить правила из выбранных ячеек .

Совет: В следующих разделах используются примеры, чтобы вы могли следовать им в Excel для Интернета. Для начала загрузите книгу «Примеры условного форматирования» и сохраните ее в OneDrive. Затем откройте OneDrive в веб-браузере и выберите загруженный файл.

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

Выберите ячейку с условным форматом, который вы хотите изменить. Или вы можете выбрать Главная > Стили > Условное форматирование > Управление правилами , чтобы открыть область задач Условное форматирование и выбрать существующее правило.
В области задач Условное форматирование отображаются все правила, применимые к определенным ячейкам или диапазонам ячеек.
Наведите указатель мыши на правило и выберите Изменить , щелкнув значок карандаша . Откроется панель задач для редактирования правила.
Измените настройки правила и нажмите Готово , чтобы применить изменения.

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

В открытой книге выберите Главная > Стили > Условное форматирование > Управление правилами .
Откроется панель задач Условное форматирование и отобразятся правила, относящиеся к вашему текущему выбору.

Отсюда вы можете:

org/ListItem»>
Выберите другую область действия в меню «Управление правилами» в меню . Например, если выбрать этот лист , Excel будет искать все правила на текущем листе.
Добавьте правило, выбрав Новое правило (знак плюс).
Удалите все правила в области действия, выбрав Удалить все правила (мусорное ведро).

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

В раскрывающемся списке Тип правила выберите Формула .

Введите формулу в поле. Вы можете использовать любую формулу, которая возвращает логическое значение ИСТИНА (1) или ЛОЖЬ (0), но вы можете использовать И и ИЛИ для объединения набора логических проверок.

Например, =AND(B3=»Зерно»,D3<500) верно для ячейки в строке 3, если обе B3=»Зерно» и D3<500 верны.

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

Чтобы отменить условное форматирование в выбранных ячейках, выберите ячейки на листе. Затем выберите Главная > Стили > Условное форматирование > Очистить правила > Очистить правила из выбранных ячеек .
org/ListItem»>
Чтобы удалить условное форматирование на всем листе, выберите Главная > Стили > Условное форматирование > Очистить правила > Очистить правила со всего листа .
Чтобы удалить правила условного форматирования, выберите Главная > Стили > Условное форматирование > Управление правилами и используйте удаление (мусорное ведро) для определенного правила или Удалить все правила кнопка.

Цветовые шкалы — это визуальные ориентиры, помогающие понять распределение и изменчивость данных. Excel предлагает как двухцветные, так и трехцветные шкалы.

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

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

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

Выберите ячейки, которые вы хотите условно отформатировать, используя цветовую шкалу.
Нажмите Главная > Стили > Условное форматирование > Цветовые шкалы и выберите цветовую шкалу.

Выберите ячейки, которые вы хотите условно отформатировать.
Выберите Главная > Стили > Условное форматирование > Панели данных и выберите стиль.

Используйте 9Набор значков 0005 для аннотирования и классификации данных по трем-пяти категориям, разделенным пороговым значением. Каждый значок представляет диапазон значений. Например, в наборе значков 3 стрелки зеленая стрелка вверх представляет более высокие значения, желтая стрелка вбок представляет средние значения, а красная стрелка вниз представляет более низкие значения.

Выберите ячейки, которые вы хотите условно отформатировать.
Выберите Главная > Стили > Условное форматирование > Наборы значков и выберите набор значков.

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

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

Выберите ячейки, которые вы хотите условно отформатировать.
Выберите Главная > Стили > Условное форматирование > Правила выделения ячеек .
Выберите сравнение, например Между , Равно, Текст, содержащий или Дата возникновения .

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

Некоторые примеры этого включают выделение пяти самых продаваемых продуктов в региональном отчете, 15 % самых продаваемых продуктов в опросе клиентов или 25 самых высоких зарплат в отделе.

org/ItemList»>
Выберите ячейки, которые вы хотите условно отформатировать.
Выберите Главная > Стили > Условное форматирование > Верхние/нижние правила .
Выберите нужную команду, например, Верхние 10 элементов или Нижние 10 % .
Введите значения, которые вы хотите использовать, и выберите формат (заливка, текст или цвет границы).

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

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

Выберите ячейки, которые вы хотите условно отформатировать.
Выберите Главная > Стили > Условное форматирование > Верхние/нижние правила .
Выберите нужный вариант, например Выше среднего или Ниже среднего .
org/ListItem»>
Введите значения, которые вы хотите использовать, и выберите формат (заливка, текст или цвет границы).

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

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

Щелкните ячейку с условным форматированием, которое вы хотите скопировать.
Щелкните Главная > Формат по образцу .
Указатель изменится на кисть.
Совет: Вы можете дважды щелкнуть Format Painter , если хотите продолжать использовать кисть для вставки условного форматирования в другие ячейки.
Проведите кистью по ячейкам или диапазонам ячеек, которые нужно отформатировать.
Чтобы прекратить использование кисти, нажмите Esc.

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

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

Нужна дополнительная помощь?

См. также

Проблемы совместимости с условным форматированием

Как выделить ошибки, пробелы и дубликаты в Microsoft Excel (с помощью формул)

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

от Avantix Learning Team | Обновлено 7 апреля 2021 г.

Применимо к: Microsoft ^® Excel ^® 2010, 2013, 2016, 2019 и 365 (Windows)

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

Условное форматирование отображается на вкладке «Главная» на ленте в группе «Стили».

Условное форматирование можно применять с помощью формулы и нового правила. Ниже приведен пример правила с формулой:

Рекомендуемая статья: 15 сочетаний клавиш Microsoft Excel для ускорения форматирования

Хотите узнать больше об Excel? Посетите наш виртуальный класс или живой класс Курсы Excel >

Выделение ошибок с помощью условного форматирования

Условное форматирование можно использовать для выделения ошибок в диапазоне ячеек.

В приведенном ниже примере в столбце C есть ошибки:

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

Выберите диапазон ячеек в столбце, некоторые из которых содержат ошибки. В этом примере выберите от C2 до C10.
Перейдите на вкладку «Главная» на ленте и выберите «Условное форматирование» в группе «Стили». Появится раскрывающееся меню.
Выберите «Новое правило» в раскрывающемся меню. Появится диалоговое окно.
Щелкните Использовать формулу, чтобы определить ячейки для форматирования. В поле формулы введите =ЕОШИБКА(ссылка на активную ячейку). В этом примере введите =ЕОШИБКА(C2). Введите формулу для первой ячейки диапазона. Excel по умолчанию использует относительную ссылку, поэтому формула будет скопирована и скорректирована для оставшихся ячеек.
Щелкните Формат. Появится диалоговое окно.
Перейдите на вкладку «Заливка» и выберите светлую заливку.
Дважды нажмите OK.

Выделение пробелов с помощью условного форматирования

Условное форматирование можно использовать для выделения пробелов в диапазоне ячеек.

В приведенном ниже примере в столбце C есть пробелы:

Чтобы выделить пробелы с помощью условного форматирования:

Выберите диапазон ячеек в столбце, где некоторые ячейки содержат пробелы. В этом примере выберите от C2 до C10.
Перейдите на вкладку «Главная» на ленте и выберите «Условное форматирование» в группе «Стили». Появится раскрывающееся меню.
Выберите в меню Новое правило. Появится диалоговое окно.
Щелкните Использовать формулу, чтобы определить ячейки для форматирования. В поле формулы введите =ПУСТО(ссылка на активную ячейку). В этом примере введите =ПУСТО(C2). Введите формулу для первой ячейки диапазона. Excel по умолчанию использует относительную ссылку, поэтому формула будет скопирована и скорректирована для оставшихся ячеек.
Щелкните Формат. Появится диалоговое окно.
Перейдите на вкладку «Заливка» и выберите светлую заливку.
Дважды нажмите OK.

Выделение дубликатов с помощью условного форматирования

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

В приведенном ниже примере в столбце A есть дубликаты:

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

Выберите диапазон ячеек в столбце, где некоторые из ячеек содержат дубликаты. В этом примере выберите от A2 до A10.
Перейдите на вкладку «Главная» на ленте и выберите «Условное форматирование» в группе «Стили». Появится раскрывающееся меню.
Выберите в меню Новое правило. Появится диалоговое окно.
Щелкните Использовать формулу, чтобы определить ячейки для форматирования. В поле формулы введите =СЧЁТЕСЛИ(абсолютный диапазон,ячейка, содержащая критерии)>1. В этом примере введите =СЧЁТЕСЛИ($A$2:$A$10,A2)>1. Введите формулу для диапазона ячеек с использованием абсолютной ссылки (поскольку этот диапазон не должен изменяться) и для ячейки, содержащей критерии, с использованием относительной ссылки и ссылки на активную ячейку. Затем Excel скопирует формулу и откорректирует оставшиеся ячейки.
Щелкните Формат. Появится диалоговое окно.
Перейдите на вкладку «Заливка» и выберите светлую заливку.
Дважды нажмите OK.

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

Подпишитесь, чтобы получать больше статей, подобных этой

Была ли эта статья полезной для вас? Если вы хотите получать новые статьи, присоединяйтесь к нашему списку адресов электронной почты.

Дополнительные ресурсы

Как использовать мгновенное заполнение в Excel (4 способа с помощью ярлыков)

Как закрепить заголовки строк и столбцов в рабочих листах Excel

3 Зачеркнутые ярлыки Excel для вычеркивания текста или значений в ячейках

Как заменить пустые ячейки значением из ячейки выше в Excel

Использование условного форматирования в Excel для выделения дат до сегодняшнего дня (3 способа)

Связанные курсы

Microsoft Excel: средний/продвинутый уровень

Microsoft Excel: анализ данных с функциями, информационными панелями и анализом «что, если» Инструменты

Microsoft Excel: Введение в Visual Basic для приложений (VBA)

ПОСМОТРЕТЬ ДОПОЛНИТЕЛЬНЫЕ КУРСЫ >

Наши курсы под руководством инструктора проводятся в формате виртуального класса или в нашем центре Торонто по адресу: 18 King Street East, Suite 1400, Toronto, Онтарио, Канада (некоторые очные курсы также могут проводиться в другом месте в центре Торонто). Свяжитесь с нами по адресу [email protected], если вы хотите организовать индивидуальный виртуальный класс под руководством инструктора или обучение на месте в удобное для вас время.

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

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

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

Microsoft, логотип Microsoft, Microsoft Office и связанные приложения Microsoft и логотипы являются зарегистрированными товарными знаками Microsoft Corporation в Канаде, США и других странах. Все остальные товарные знаки являются собственностью зарегистрированных владельцев.

Avantix Learning | 18 King Street East, Suite 1400, Торонто, Онтарио, Канада M5C 1C4 | Свяжитесь с нами по адресу [email protected]

Поиск и выделение данных в Excel (с условным форматированием)

Смотреть видео — поиск и выделение данных с использованием условного форматирования

Если вы работаете с большими наборами данных, могут необходимо создать функцию поиска, позволяющую быстро выделять ячейки/строки для искомого термина.
Хотя прямого способа сделать это в Excel нет, вы можете создать функцию поиска с помощью условного форматирования.
Например, предположим, что у вас есть набор данных, как показано ниже (на изображении). В нем есть столбцы для названия продукта, торгового представителя и страны.
Теперь вы можете использовать условное форматирование для поиска ключевого слова (введя его в ячейку C2) и выделить все ячейки, содержащие это ключевое слово.
Примерно так, как показано ниже (где я ввожу название элемента в ячейку B2 и нажимаю Enter, выделяется вся строка):
В этом уроке я покажу вам, как создать эту функцию поиска и выделения в Excel.
Позже в этом уроке мы немного продвинемся вперед и посмотрим, как сделать его динамическим (чтобы он выделялся, когда вы вводите текст в поле поиска).
Щелкните здесь, чтобы загрузить пример файла и следовать ему.
Это руководство охватывает:
Поиск и выделение совпадающих ячеек
В этом разделе. Я покажу вам, как искать и выделять только совпадающие ячейки в наборе данных.
Примерно так, как показано ниже:
Вот шаги для поиска и выделения всех ячеек с соответствующим текстом:
Выберите набор данных, к которому вы хотите применить условное форматирование (A4: F19 в этом примере) .
Перейдите на вкладку «Главная».
В группе «Стили» нажмите «Условное форматирование».
В раскрывающемся списке выберите Новое правило.

В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу для определения форматируемых ячеек».
Введите следующую формулу: =A4=$B$1
Нажмите кнопку «Форматировать».
Укажите форматирование (чтобы выделить ячейки, соответствующие искомому ключевому слову).
Нажмите OK.
Теперь введите что-нибудь в ячейку B1 и нажмите Enter. Он выделит совпадающие ячейки в наборе данных, которые содержат ключевое слово в B1.
Как это работает?
Условное форматирование применяется всякий раз, когда указанная в нем формула возвращает значение TRUE.
В приведенном выше примере мы проверяем каждую ячейку, используя формулу =A4=$B$1
Условное форматирование проверяет каждую ячейку и проверяет, что содержимое в ячейке такое же, как и в ячейке B1. Если это то же самое, формула возвращает ИСТИНА, и ячейка выделяется. Если это не то же самое, формула возвращает FALSE и ничего не происходит.
Щелкните здесь, чтобы загрузить пример файла и следовать ему.
Поиск и выделение строк с совпадающими данными
Если вы хотите выделить всю строку, а не только соответствующие ячейки, вы можете сделать это, немного изменив формулу.
Ниже приведен пример, когда вся строка выделяется, если тип продукта совпадает с типом в ячейке B1.
Вот шаги для поиска и выделения всей строки:
Выберите набор данных, к которому вы хотите применить условное форматирование (A4:F19 в этом примере).
Перейдите на вкладку «Главная».
: В группе «Стили» нажмите «Условное форматирование».
В раскрывающемся списке выберите Новое правило.

В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу для определения форматируемых ячеек».
Введите следующую формулу: =$B4=$B$1
Нажмите кнопку «Формат..».
Укажите форматирование (чтобы выделить ячейки, соответствующие искомому ключевому слову).
Нажмите OK.
Вышеуказанные шаги будут искать указанный элемент в наборе данных, и если он найдет соответствующий элемент, будет выделена вся строка.
Обратите внимание, что при этом будет проверяться только столбец элементов. Если вы введете здесь имя торгового представителя, это не сработает. Если вы хотите, чтобы это работало для имени торгового представителя, вам нужно изменить формулу на =$C4=$B$1
Примечание. Причина, по которой выделяется вся строка, а не только соответствующая ячейка, заключается в том, что мы использовали Знак $ перед ссылкой на столбец ($B4). Теперь, когда условное форматирование анализирует ячейки в строке, оно проверяет, равно ли значение в столбце B этой строки значению в ячейке B1. Таким образом, даже когда он анализирует A4, B4 или C4 и так далее, он проверяет только значение B4 (поскольку мы заблокировали столбец B с помощью знака доллара).
Подробнее об абсолютных, относительных и смешанных ссылках можно прочитать здесь.
Поиск и выделение строк (на основе частичного совпадения)
В некоторых случаях может потребоваться выделить строки на основе частичного совпадения.
Например, если у вас есть такие элементы, как «Белая доска», «Зеленая доска» и «Серая доска», и вы хотите выделить их все по слову «Доска», вы можете сделать это с помощью функции ПОИСК.
Как показано ниже:
Вот шаги для этого:
Выберите набор данных, к которому вы хотите применить условное форматирование (A4:F19 в этом примере).
Перейдите на вкладку «Главная».
В группе «Стили» нажмите «Условное форматирование».
В раскрывающемся списке выберите Новое правило.

В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу для определения форматируемых ячеек».
Введите следующую формулу: =И($B$1<>””,ЧИСЛО(ПОИСК($B$1,$B4)))
Нажмите кнопку «Форматировать..».
Укажите форматирование (чтобы выделить ячейки, соответствующие искомому ключевому слову).
Нажмите OK.
Как это работает?
Функция ПОИСК ищет строку/ключевое слово для поиска во всех ячейках подряд. Он возвращает ошибку, если ключевое слово для поиска не найдено, и возвращает число, если находит совпадение.
Функция IЧИСЛО преобразует ошибку в ЛОЖЬ, а числовые значения в ИСТИНА.
Функция И проверяет наличие дополнительного условия – ячейка C2 не должна быть пустой.
Итак, теперь всякий раз, когда вы вводите ключевое слово в ячейку B1 и нажимаете Enter, выделяются все строки, в которых есть ячейки, содержащие это ключевое слово.
Дополнительный совет: Если вы хотите сделать поиск чувствительным к регистру, используйте функцию НАЙТИ вместо ПОИСКА.
Щелкните здесь, чтобы загрузить пример файла и следовать ему.
Функция динамического поиска и выделения (выделение по мере ввода)
Используя описанные выше приемы условного форматирования, вы также можете пойти еще дальше и сделать его динамичным.
Например, вы можете создать панель поиска, в которой совпадающие данные будут подсвечиваться по мере того, как вы вводите текст в строку поиска.
Примерно так, как показано ниже:
Это можно сделать с помощью элементов управления ActiveX, и это может быть удобно при создании отчетов или информационных панелей.
Ниже видео, где я показываю, как это сделать:
<noscript><img loading='lazy' src='' /></noscript> youtube.com/embed/_WqBWv7_IkQ?feature=oembed&autoplay=1″ src=»data:text/html;https://www.youtube.com/embed/_WqBWv7_IkQ?feature=oembed&autoplay=1;base64,PGJvZHkgc3R5bGU9J3dpZHRoOjEwMCU7aGVpZ2h0OjEwMCU7bWFyZ2luOjA7cGFkZGluZzowO2JhY2tncm91bmQ6dXJsKGh0dHBzOi8vaW1nLnlvdXR1YmUuY29tL3ZpL19XcUJXdjdfSWtRLzAuanBnKSBjZW50ZXIvMTAwJSBuby1yZXBlYXQnPjxzdHlsZT5ib2R5ey0tYnRuQmFja2dyb3VuZDpyZ2JhKDAsMCwwLC42NSk7fWJvZHk6aG92ZXJ7LS1idG5CYWNrZ3JvdW5kOnJnYmEoMCwwLDApO2N1cnNvcjpwb2ludGVyO30jcGxheUJ0bntkaXNwbGF5OmZsZXg7YWxpZ24taXRlbXM6Y2VudGVyO2p1c3RpZnktY29udGVudDpjZW50ZXI7Y2xlYXI6Ym90aDt3aWR0aDoxMDBweDtoZWlnaHQ6NzBweDtsaW5lLWhlaWdodDo3MHB4O2ZvbnQtc2l6ZTo0NXB4O2JhY2tncm91bmQ6dmFyKC0tYnRuQmFja2dyb3VuZCk7dGV4dC1hbGlnbjpjZW50ZXI7Y29sb3I6I2ZmZjtib3JkZXItcmFkaXVzOjE4cHg7dmVydGljYWwtYWxpZ246bWlkZGxlO3Bvc2l0aW9uOmFic29sdXRlO3RvcDo1MCU7bGVmdDo1MCU7bWFyZ2luLWxlZnQ6LTUwcHg7bWFyZ2luLXRvcDotMzVweH0jcGxheUFycm93e3dpZHRoOjA7aGVpZ2h0OjA7Ym9yZGVyLXRvcDoxNXB4IHNvbGlkIHRyYW5zcGFyZW50O2JvcmRlci1ib3R0b206MTVweCBzb2xpZCB0cmFuc3BhcmVudDtib3JkZXItbGVmdDoyNXB4IHNvbGlkICNmZmY7fTwvc3R5bGU+PGRpdiBpZD0ncGxheUJ0bic+PGRpdiBpZD0ncGxheUFycm93Jz48L2Rpdj48L2Rpdj48c2NyaXB0PmRvY3VtZW50LmJvZHkuYWRkRXZlbnRMaXN0ZW5lcignY2xpY2snLCBmdW5jdGlvbigpe3dpbmRvdy5wYXJlbnQucG9zdE1lc3NhZ2Uoe2FjdGlvbjogJ3BsYXlCdG5DbGlja2VkJ30sICcqJyk7fSk7PC9zY3JpcHQ+PC9ib2R5Pg==»>
Этот урок был вам полезен? Дайте мне знать ваши мысли в разделе комментариев.
Вам также могут понравиться следующие учебные пособия по Excel:
Динамический фильтр Excel — извлекает данные по мере их ввода.
Создать раскрывающийся список с предложением поиска.
Создание тепловой карты в Excel.
Выделение строк на основе значения ячейки в Excel.
Выделите активную строку и столбец в диапазоне данных в Excel.
Как выделить пустые ячейки в Excel.
Как обнаружить ошибки в электронных таблицах
Учитывая, что большинство из нас используют Excel для работы с числами, многие аналитики не осознают важность проверки своей работы. Чтобы электронные таблицы были точными, необходимо уделять внимание деталям.
Конечным пользователям становится не по себе, когда вы просматриваете отчеты и видите такие ошибки, как #DIV/0!, #N/A и так далее. Их легко найти, хотя, возможно, это немного утомительно, когда вы только начинаете. Вот несколько советов: Используйте фоновую проверку ошибок Excel.
Строго говоря, это следует инициировать на всем этапе разработки модели, поскольку это может помочь аналитику на протяжении всего строительства.
Чтобы включить эту функцию, перейдите к параметрам Excel ( Файл -> Параметры или Alt + F + T ) и в разделе «Формулы» убедитесь, что установлен флажок «Включить фоновую проверку ошибок». После активации пользователь может выбрать, какие правила проверки ошибок следует учитывать, проверив раздел «Правила проверки ошибок» непосредственно под этим флажком.
Эта функция не предотвращает появление ошибок, но Excel выделяет потенциально ошибочные ячейки так же, как ячейки, содержащие комментарии:
лучше, чем ничего не делать.
Используйте инструменты аудита формул Excel
На вкладке «Формулы» на ленте используйте инструменты в разделе «Аудит формул» на панели инструментов. В частности, полезна «Проверка ошибок» (хотя она может применяться только к одному рабочему листу за раз), поскольку она выявляет множество проблем, которые Excel запрограммирован рассматривать как «сомнительные» (например, несогласованные формулы, #ДЕЛ/0! ошибок и так далее).
Для тех, кому посчастливилось иметь версию Excel 2019 Professional Plus или версию Office 365 Pro Plus, Spreadsheet Inquire дополняет встроенные функции Excel, позволяя пользователям анализировать связи между книгами, листами и/или отдельными ячейки:
Это может помочь убрать лишнее форматирование ячеек и помочь понять отношения между ячейками и/или рабочими листами.
Поиск ошибок prima facie
В Excel возникают сбои, и иногда может проскальзывать ошибка prima facie. Эти очевидные ошибки особенно неловко не заметить, так как они обычно выявляются конечными пользователями всего за несколько секунд после передачи модели.
Существует простая надежная проверка: Ctrl + F (выберите «Найти…» из выпадающего списка в «Найти и выбрать» в группе «Редактирование» на вкладке «Главная» ленты) .
Просто введите «#» в «Найти что» (все очевидные ошибки начинаются с «#»), но затем нажмите кнопку «Параметры», чтобы отобразить параметры, и измените параметр «В пределах» на «Рабочая книга», а затем просмотрите «Формулы», «Значения» и «Комментарии» по очереди, используя кнопку «Найти все», чтобы исправить выявленные проблемы:
Проверка несоответствий в формулах
Иногда данные представлены в виде формул. Представьте, что следующее представляет собой блок формул:
Предположим, что эти данные должны ссылаться на аналогичный блок данных в другом месте. Как узнать, правильно ли скопирована формула вдоль и поперек? Проверка на глаз здесь ничего не дает.
Один из вариантов — использовать сочетание клавиш Ctrl + ` (символ — это клавиша слева от 1 на стандартной QWERTY-клавиатуре США):

Этот ярлык переключает значения ячеек с их содержимым (то есть формулами). Это покажет формулы, которые не были скопированы должным образом, но это все еще чревато ошибкой пользователя (можете ли вы определить соответствующие ячейки?) и было бы громоздким с огромными массивами данных.
Вместо этого существует более простой автоматический подход. Выберите все данные (щелкните в любом месте диапазона и нажмите Ctrl + A ). Затем используйте сочетание клавиш Ctrl + \ , которое может открыть следующее:
Это автоматически выбирает все ячейки, содержимое которых отличается от ячейки сравнения в каждой строке (для каждой строки ячейка сравнения находится в том же столбце, что и активная ячейка).
Ctrl + Shift + \ выбирает все ячейки, содержимое которых отличается от ячейки сравнения в каждом столбце (для каждого столбца ячейка сравнения находится в той же строке, что и активная ячейка). В этом примере, где предполагается копирование формулы вдоль и поперек, разницы не будет.
Теперь эти ячейки можно выделить и просмотреть на досуге.
Создание «быстрых» диаграмм
Для основных выходных данных можно моментально отображать данные. Просто выделите данные и нажмите функциональную клавишу F11 , чтобы создать диаграмму на отдельном листе, или Alt + F1 , чтобы создать диаграмму на текущем рабочем листе.
После того, как диаграмма будет создана, подумайте, имеет ли смысл диаграмма (диаграммы): есть ли неуместные «всплески» или непоследовательные тенденции? Можно ли легко объяснить резкие изменения? Эти приблизительные и готовые диаграммы могут мгновенно выделить ошибочные данные.
Закройте и снова откройте
Получаете ли вы неожиданные сообщения об ошибках при открытии? Это частая оплошность моделистов. Расчеты установлены на «Автоматически»? Есть ли неожиданные ссылки, циклические аргументы или другие сообщения об ошибках (например, «Недостаточно памяти для отображения»)? Лучше, чтобы вы обнаружили эти проблемы до того, как это сделают ваши пользователи.
Подсветка ошибок
При желании есть два способа выделения ошибок в данных в Excel:
Использование условного форматирования
Выберите весь набор данных и перейдите на главную страницу -> Условное форматирование -> Новое правило . В диалоговом окне «Новое правило форматирования» выберите «Форматировать только те ячейки, которые содержат», затем в «Описании правила» выберите «Ошибки» из раскрывающегося списка:
Установите требуемый формат и нажмите «ОК». ‘. Это выделит любые значения ошибок в выбранном наборе данных.
Использование перехода к специальному
Выберите весь набор данных и нажмите F5 (или Ctrl + G , откроется диалоговое окно «Перейти»).

Скрытие значений и индикаторов ошибок в ячейках

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

Отображение связей между формулами и ячейками

Проблема: Microsoft Excel издает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки.

Поиск и исправление ошибок в вычислениях Excel новость от 13.

Основные принципы работы с датами и временем в Excel

Видео

Как вводить даты и время в Excel

Быстрый ввод дат и времени

Как Excel на самом деле хранит и обрабатывает даты и время

Количество дней между двумя датами

Количество рабочих дней между двумя датами

Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.

Сдвиг даты на заданное количество дней

Сдвиг даты на заданное количество рабочих дней

Вычисление дня недели

Вычисление временных интервалов

Ссылки по теме

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

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

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

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

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

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

Выводы

Поиск и исправление ошибок в вычислениях — FINDOUT.SU

Обнаружение ошибок в формулах

Научитесь вводить простую формулу

5

5 ,

Нужна дополнительная помощь?

См. также

Скрыть значения ошибок и индикаторы ошибок в ячейках

Преобразование ошибки в ноль и использование формата для скрытия значения

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

Цветовые шкалы

Цветовые шкалы

Нужна дополнительная помощь?

См. также

Как выделить ошибки, пробелы и дубликаты в Microsoft Excel (с помощью формул)

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

Выделение ошибок с помощью условного форматирования

Выделение пробелов с помощью условного форматирования

Выделение дубликатов с помощью условного форматирования

Подпишитесь, чтобы получать больше статей, подобных этой

Дополнительные ресурсы

Связанные курсы

Поиск и выделение данных в Excel (с условным форматированием)

Поиск и выделение совпадающих ячеек

Поиск и выделение строк с совпадающими данными

Поиск и выделение строк (на основе частичного совпадения)

Функция динамического поиска и выделения (выделение по мере ввода)

Как обнаружить ошибки в электронных таблицах

Используйте инструменты аудита формул Excel

Поиск ошибок prima facie

Проверка несоответствий в формулах

Создание «быстрых» диаграмм

Закройте и снова откройте

Подсветка ошибок

Добавить комментарий Отменить ответ