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

Формат Excel файлов

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

Как установить формат ячеек в Excel разберем на примере сравнения двух столбцов – количества запланированной к выпуску продукции и реально выпущенной.

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

После нажатия на нее мы увидим меню, в котором можно реализовать условное форматирование. Используем опцию «Меньше».


При выборе откроется окно, в котором можно установить значение и выбрать цвета.


Введем граничный параметр 2000 и выберем красный цвет. Жмем «ОК». Ячейка станет цветной, так как значение в ней меньше заданного.

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


Так мы сразу увидим месяца, в которых минимальный план выполнен не был.

Условное форматирование ячеек в Excel на основании значений другого диапазона

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


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


Если ввести в ячейку E3 новое значение, то и оформление в таблице Excel изменится.


Для сравнения с диапазоном следует немного изменить условие. Выделите столбец с фактически выпущенной продукцией и снова нажмите на кнопку условного форматирования. При выборе опции «Меньше» в окне укажите первую ячейку столбца «Запланировано». Это можно сделать мышкой.


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


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

Вместо чисел можно использовать любые значения: строка, дата и т.д. Например, выделим все автомобили в пункте проката, которые находятся в резерве. Значение «р» - означает, что авто свободно, а «с», что сдано.

Установим зеленый цвет для свободных автомобилей. Нам понадобится условие «Равно».


В качестве значения укажем «р».


Теперь мы легко можем увидеть, какой автомобиль свободен.


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

При его выборе откроется окно с перечнем правил.


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

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

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


При этом старое правило условного форматирования останется и будет действовать. Это можно увидеть в управлении.


Еще один способ – использовать пункт «Создать правило». Выделите диапазон и выберите его в меню условного форматирования.


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


Далее выберите цвета для оформления. Для этого нажмите кнопку «Формат».


Создайте еще одно правило.

Формат даты в Excel

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


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


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

Формат таблицы Excel с использованием формул

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


Функция СРЗНАЧ считает среднее значение для указанного диапазона.
Таким образом вы можете установить стиль для любой ячейки.

Установка стиля для целой строки

Чтобы оформление было применено ко всей таблице, выберите ее в качестве диапазона. Например, для наших автомобилей мы установим такое правило:


И применим его ко всему диапазону.


Это позволит раскрасить всю таблицу.

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

Формат числа в Excel

В конце расскажем, как быстро установить числовой формат в Excel. Иногда ваше число вдруг превращается в текст или дату. Если это произошло, преобразуйте значение в нужный формат.
Установите указатель на ячейку с числом и нажмите квадратик в правом нижнем углу панели «Число» вкладки «Главная».


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


Галочка «Разделитель групп разрядов» позволит устанавливать пробел между разрядами числа.


Также вы можете установить денежный или финансовый формат.


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

Отличного Вам дня!

При заполнении листов Excel данными, никому не удаться сразу все красиво и правильно заполнить с первой попытки.

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

Как задать формат ячейки в Excel ?

Содержимое каждой ячейки Excel состоит из трех элементов:

1. Значение: текст, числа, даты и время, логическое содержание, функции и формулы.
2. Форматы: тип и цвет границ, тип и цвет заливки, способ отображения значений.
3. Примечания.

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

Как изменить формат ячеек в Excel 2010?

Чтобы изменить формат ячеек следует вызвать соответствующее диалоговое окно комбинацией клавиш CTRL+1(или CTRL+SHIFT+F) или из контекстного меню после нажатия правой кнопкой мышки: опция «Формат ячеек».

В данном диалоговом окне доступно 6 закладок:

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

Какое форматирование применимо к ячейкам в Excel?

Каждая ячейка всегда имеет какой-то формат. Если не было никаких изменений, то это «Общий» формат. Он же является стандартным форматом Excel, в котором:

• числа выравниваются по правой стороне;
• текст выравнен по левой стороне;
• шрифт Colibri с высотой 11 пунктов;
• ячейка не имеет границ и заливки фона.

Удаление формата – это изменение на стандартный формат «Общий» (без границ и заливок).

Стоит отметить, что формат ячеек в отличие от их значений, нельзя удалить клавишей DELETE.

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

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

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

• содержимое (то же что и клавиша DELETE);
• форматы;
• примечания;
• гиперссылки.

Опция «Очистить все» объединяет в себе все эти функции.

Удаление примечаний

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

1. Инструментом ластиком: опция «Очистить примечания».
2. Кликнуть по ячейка с примечанием правой кнопкой мышки, а из появившегося контекстного меню выбрать опцию «Удалить примечание».

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

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

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

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

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

Для изменения первоначального форматирования ячеек можно использовать:

кнопки панелей инструментов Стандартная иФорматирования ;

команду меню окна ФОРМАТ  Ячейки;

команду контекстного меню Формат ячеек.

Команды меню окна ФОРМАТ  Ячейки и команда контекстного менюФормат ячеек выводят на экран диалоговое окноФормат ячеек . Диалоговое окноФормат ячеек содержит шесть вкладок, с помощью которых можно определить все параметры ячейки или выделенного диапазона ячеек: число, выравнивание, шрифт, граница, вид, защита.

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

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

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

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

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

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

Анализ и обработка данных электронной таблицы

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

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

Excelпредоставляет разнообразные способы для автоматического обобщения и анализа данных:

автоматические вычисления;

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

средства автоматического подведения общих и промежуточных итогов;

возможность создания и использования структуры таблицы;

средства для консолидации (обобщения) данных;

возможность создания и модификации сводных таблиц, отчетов и диаграмм;

средства условного анализа (анализа «что, если») (подбор параметра, надстройка Поиск решения, сценарии, таблицы подстановки);

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

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

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

каждая строка списка рассматривается как запись базы данных;

столбцы списков считаются полями базы данных;

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

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

все строки таблицы должны содержать однородную информацию – во всех строках в одинаковых столбцах должны находиться однотипные данные;

заголовки столбцов должны находиться в первой строке списка;

названия строк должны находиться в левом столбце списка;

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

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

добавлять, изменять и удалять записи;

находить записи;

сортировать записи;

осуществлять фильтрацию (и выборку) данных с помощью Автофильтра иРасширенного фильтра ;

подводить общие и промежуточные итоги и т.д.

1). обрамление и заливка

2). выравнивание текста и формат шрифта

3). тип данных, ширина и высота

4). все варианты верны

6. Какой оператор не входит в группу арифметических операторов?

7. Что из перечисленного не является характеристикой ячейки?

3). размер

4). Значение

Какое значение может принимать ячейка

1). числовое

2). текстовое

3). возвращенное

4). все перечисленные

9. Что может являться аргументом функции?

2). константа

3). функция

4). все варианты верны

Указание адреса ячейки в формуле называется...

2). функцией

3). оператором

4). именем ячейки

Программа Excel используется для.

1). создания текстовых документов

2). создания электронных таблиц

3). создания графических изображений

4). все варианты верны

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

3). пробел

4). все равно с какого

13. На основе чего строится любая диаграмма?

1). Книги Excel

2). графического файла

3). текстового файла

4). данных таблицы

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

1). + и - затем * и /

2). операторы сравнения затем операторы ссылок

3). операторы ссылок затем операторы сравнения

4). * и / затем %

Минимальной составляющей таблицы является...

1). ячейка

2). формула

4). нет верного ответа

16. Для чего используется функция СУММ?

1). Для получения суммы квадратов указанных чисел

2). для получения суммы указанных чисел

3). для получения разности сумм чисел

4). для получения квадрата указанных чисел

Сколько существует видов адресации ячеек в Excel

4). четыре

18. Что делает Excel, если в составленной формуле содержится ошибка?

1). возвращает 0 как значение ячейки

2). выводит сообщение о типе ошибки как значение ячейки

3). исправляет ошибку в формуле

4). удаляет формулу с ошибкой

19. Для чего используется окно команды " Форма..."

1). для заполнения записей таблицы

2). для форматирования таблицы

3). для проверки орфографии на листе

4). для фильтрации записей таблицы по условию

Какая из ссылок является абсолютной

Упорядочивание значений диапазона ячеек в определенной последовательности называют...

1). форматирование

2). фильтрация

3). группировка

4). Сортировка

Адресация ячеек в электронных таблицах, при которой сохраняется ссылка на конкретную ячейку или область, называется....

1). конкретной

2). относительной

3). абсолютной

4). Комбинированной

4). Истина

26. Выделен диапазон ячеек A1:D3 электронной таблицы MS EXCEL. Диапазон содержит....

1). 6 ячеек

2). 9 ячеек

3). 2 ячейки

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

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

Кликнув по ней, откроется меню с видами условного форматирования. Давайте разберемся с ними более подробно.

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

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

Пример

Сравним все числа в выбранном диапазоне и если есть повторы, закрасим блоки с ними в определенный цвет. Нажимаем «Условное форматирование» – «Правила выделения ячеек» – «Повторяющиеся значения» . В списке выбираем «повторяющиеся» и тип заливки. Теперь все повторы в столбце выделены цветом. Как видите, в примере несколько раз встречаются шестерки и восьмерки.

Теперь давайте сравним данные в первом диапазоне со вторым, и если число в первом будет меньше, выделим прямоугольничек цветом. Выбираем из списка «Меньше» . Дальше делаем относительную ссылку на второй столбец: кликаем мышкой по первому числу. Доллар перед F значит, что сравнивать будем именно с этим столбцом, но в разных ячейках. В результате, все блоки в первом столбце, где числа записаны меньше, чем во втором, выделены цветом.

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

Отбор первых и последних значений

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

Пример

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

Гистограммы

Они показывают информацию в блоке в виде гистограммы. Ячейка принимается за 100%, которому соответствует максимальное число в выбранном диапазоне. Если значение в блоке будет отрицательное – гистограмма делится на половину, имеет другую направленность и цвет.

Пример

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

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

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

Внизу окна можно изменить описание для него. Ставим «Минимальное значение» – «Число» , и в поле «Значение» пишем «5» . Если Вы не хотите, чтобы в ячейках отображались числа, поставьте галочку в пункте «Показывать только столбец» . Здесь же можно изменить цвет и тип заливки.

В результате минимальное число для выделенных ячеек «5» , а максимальное выбирается автоматически. Как видно в примере, в блоках, где число меньше пяти: 4, -7, -8, или равно ему гистограмма просто не отображается.

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

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

Если открыть окно «Изменение правила форматирования» , как описано в предыдущем пункте, можно выбрать «Стиль формата» , «Цвет» заливки, максимальное и минимальное значение для выбранного диапазона.

Например, в поле «Минимальное значение» я поставила «3» . Выбранная область будет выглядеть следующим образом – блоки, значения в которых ниже 4-ох будут просто не закрашены.

Наборы значков

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

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

Как удалить

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

Как создать новое правило

Кнопка «Создать правило» позволит создать новые необходимые условия для выбранного диапазона.

Пример

Предположим, есть небольшая табличка, которая представлена на рисунке выше. Создадим для нее различные правила. Если числа в диапазоне выше «0» – закрасим блоки в желтый цвет, выше «10» – в зеленый, выше «18» – в красный.

Для начала, нужно выбрать тип – «Форматировать только ячейки, которые содержат» . Теперь в поле «Измените описание правила» задаем значение, выбираем цвет ячейки и нажимаем «ОК» . Создаем, таким образом, три правила для выделенного диапазона.

Таблица в примере отформатирована следующим образом.

Как управлять правилами

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