2. Задание условия.

Методические указания по этому пункту приведены в предыдущем задании.

Рис. 1.

3. Обнаружение значений, не отвечающих поставленным условиям.

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

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

2.     На панели инструментов «Зависимости» (рис.2) нажмите кнопку «Обвести неверные данные».

Рис. 2. Панель инструментов «Зависимости»

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

1.     Скопировать таблицу, созданную в задании 2 в новую Рабочую книгу.

2.     Удалить условия, наложенные на значения.

3.     Обеспечить возможность заливки ячеек поля «Долг»:

     зелёным цветом, если сумма долга равна нулю;

     красным цветом шрифта, если сумма долга больше нуля;

     синим, если сумма долга меньше нуля.

4.     Скопировать таблицу, на второй лист.

5.     Удалить ранее созданные условия.

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

7.     Сохранить выполненное задание.

Методические указания для выполнения задания 3

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

Алгоритм проведения процедуры:

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

2.     В меню Формат выбрать команду Условное форматирование.

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

4.     С помощью кнопки Формат указать параметры форматирования, присваиваемые ячейкам, значения которых соответствуют заданному условию.

5.     Добавление дополнительного условия осуществляется с помощью кнопки «А также>>». По завершении ввода условий нажать OK.

Рис. 1. Диалоговое окно Условное форматирование

Рассмотрим подробнее особенности задания условий каждого типа.

1.     Значение. Условие данного типа выбирается, если на предмет соответствия поставленному условию необходимо проверить все ячейки выделенного диапазона (п.3 задания). Для такого условия необходимо указать знак сравнения (между, вне, равно, не равно, больше, меньше, больше или равно, меньше или равно) после чего ввести критериальные значения (рис.2). В качестве критериального значения может выступать число, адрес ячейки или формула. Если в критериальном значении используется ссылка на ячейку, необходимо задуматься о типе адресации (абсолютная, относительная или смешанная ссылка). Вариант с абсолютной ссылкой обычно используется, если для всех проверяемых ячеек критериальное значение одно и то же. Если же для каждой из проверяемых ячеек критериальное значение задается отдельно, необходимо сослаться на первую ячейку из тех, в которых содержаться критерии, после чего сделать выбор между относительной или одним из вариантов (абсолютная по столбцу, абсолютная по строке) смешанной ссылки. Выбор типа адресации зависит от того, каким образом расположены критериальные значения на листе книги MS Excel.

Рис. 2.

2.     Формула. Используется, если на предмет соответствия поставленному условию должны проверяться не все ячейки выделенного диапазона, а только некоторые из них (п.5 задания) или условия накладываются на внешние, по отношению к выделенному диапазону, ячейки. В этом случае условие задается в виде полноценного неравенства в левой части которого находится адрес ячейки, значение из которой должно проверяться на предмет соответствия поставленному условию, в правой части – критериальное значение в виде числа, адреса ячейки или формулы. При необходимости создать составное условие (состоящее из нескольких простых) используют функции объединения логических условий (И, ИЛИ, НЕ) синтаксис которых можно увидеть на примере, приведенном на рис.3.

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

Рис. 3.