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

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

Диапазон ячеек, к которому планируется применить условное форматирование, необходимо выделить. Затем следует нажать кнопку Условное форматирование в группе Стили на вкладке ленты Главная и исходя из условий решаемой задачи выбрать один из пунктов: Правила выделения ячеек или Правила отбора первых и последних значений. Далее выполняется нужная команда (например, Больше…, Текст содержит…, Первые 10 %..., Выше средне-го…). В появившемся после выполнения этих действий диалоговом окне создаются критерии, которые будут использованы для условного форматирования, и определяется формат, применяемый для выделения ячеек.

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

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

 

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

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

 

Правило, находящееся вверху списка является самым приоритетным.

Для прекращения действия какого-либо правила форматирования необходимо установить флажок "Остановить, если истина".

Условные форматы можно копировать (вы в этом уже убедились на примере выше). Для копирования формата в любую ячейку (диапазон ячеек) можно воспользоваться "метелкой" "Формат по образцу", расположенной на панели "Буфер обмена" ленты "Главная".

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

 

Стили

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

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

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

В ленте Главная в разделе Стили щелкните кнопку Стили ячеек. Появится таблица с готовыми стилями ячеек.

В таблице со стилями щелкните правой кнопкой мыши на ячейке Обычный. В появившемся меню выберите пункт Изменить. Откроется окно Стиль.

Нажмите кнопку Формат. Появится окно Формат ячеек.

В окне Формат ячеек на вкладке Шрифт в поле Размер задайте 14, на вкладке Заливка в поле Цвет фона задайте зеленый цвет.

Нажмите кнопку ОК в окне Формат ячеек и в окне Стиль.

 

Практическая часть

Задание 1

1) На листе 1 отформатировать ячейки по приведенному образцу. Цвет текста и заливку ячеек сделать произвольной.

2) На листе 2 отформатировать ячейки по приведенному образцу.

 

3) На листе 3 отформатировать ячейки по приведенному образцу.

4) Создать таблицу по указанному образцу и поместить эту новую таблицу на лист с названием "ИТОГОВЫЙ ЛИСТ" (создать собственное расписание по указанному образцу).

Сохраните файл под именем «Задание_1» в собственной папке.

Задание 2

1. Создать на рабочем листе MS Excel таблицу, включающую информацию о трудовой деятельности сотрудников фирмы «Инвест» в течение 2010 г.:

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

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

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

4. Скопировать созданную формулу в строки с информацией для других сотрудников.

5. Выполнить сортировку строк (записей) таблицы по фамилиям сотрудников (в алфавитном порядке).

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

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

8. Добавить к таблице строку с названием Итоги. Разместить в ячейках этой строки формулы для расчёта следующих характеристик:

ü суммарного количества дней, отработанных в течение года сотрудниками фирмы (функция СУММ);

ü первой даты ухода сотрудника фирмы в отпуск (функция МИН);

ü последней даты выхода сотрудника фирмы из отпуска (функция МАКС);

ü средней продолжительности отпуска (функция СРЗНАЧ);

ü количества сотрудников фирмы, не пропускавших рабочие дни по болезни (функция СЧИТАТЬПУСТОТЫ);

ü третьего по величине значения доли отработанного времени (функция НАИБОЛЬШИЙ).

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

ü Рассчитать в ячейках этого столбца данную характеристику, используя функцию ДЕНЬНЕД, полагая, что первым днём недели является понедельник.

11. Разместите ниже таблицы формулы для расчёта:

ü количества сотрудников фирмы, отработавших в течение года более 200 дней (функция СЧЁТЕСЛИ);

ü количества сотрудников фирмы, ушедших в отпуск в первом полугодии и пропустивших по болезни более 10 дней (функция СЧЁТЕСЛИМН).

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

12. Вставьте перед таблицей две пустые строки для создания в них названия таблицы. В первой строке введите слово ОТЧЁТ, во второй – текст о затратах времени сотрудниками фирмы «Инвест» в 2010 году. Выровняйте название таблицы по её ширине.

13. Отформатируйте созданную Вами таблицу:

ü название таблицы выделите полужирным курсивом (шрифт Times New Roman размером 16 пт);

ü между таблицей и её названием вставьте пустую строку;

ü заголовки столбцов и слово Итоги выравняйте по центру ячеек и выделите полужирным шрифтом Calibri размером 12 пт;

ü для значений данных примените шрифт Calibri размером 11 пт;

ü фамилии сотрудников выровняйте по левому краю ячеек, все числа и даты – по центру ячеек;

ü для заголовков столбцов и итоговой строки примените заливку синего цвета;

ü расчертите таблицу (исключая её название и данные, рассчитанные в пункте 11) одинарными линиями и заключите её в рамку из двойных линий;

ü при необходимости измените ширину столбцов и высоту строк.

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