Условное форматирование в Excel
Предметная область автоматизации. Возможности условного форматирования. Функции табличных процессоров. Описание входной и выходной информации. Перспективные направления в разработке электронных таблиц. Реализация функции условного форматирования в Excel.
Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
Microsoft Excel представляет собой электронные таблицы, которые предназначены для выполнения вычислений, анализа и построения разнообразных отчетов.
В настоящее время этот программный продукт является абсолютным лидером среди аналогичных программ электронных таблиц по своей простоте и вместе с тем обладает колоссальными возможностями.
Электронные таблицы Microsoft Excel разработаны фирмой Microsoft и обновляются примерно каждые 2 года.
Это вовсе не означает, что выходит совершенно новый продукт, работе с которым приходиться учиться заново, — в каждой новой версии Microsoft Excel расширяются функциональные возможности данной программы. Поэтому пользователь найдет все в следующей серии , плюс что-то новое.
В данной курсовой работе мы разбираемся с условным форматированием, что представляет собой обеспечение наглядности при исследовании и анализе данных ,обнаружение критических проблем, шаблонов и тенденций.
Современные табличные процессоры позволяют применять многочисленные средства автоматизации решения задач, так что возможным стало даже написание конкретных приложений на их основе. Кроме того, они обладают широкими графическими возможностями. Табличные процессоры особенно широко используются в аналитической деятельности, а также для подготовки документов сложной формы.
Основное назначение табличного процессора — автоматизация Расчетов в табличной форме.
Например, в табличном процессоре можно вести журнал успеваемости. Преподаватели смогут заносить в него оценки учащихся, а встроенные формулы позволят высчитывать средний балл для каждого ученика, общую успеваемость группы по предмету и др. Каждый раз, когда учитель вносит новую оценку, табличный процессор будет автоматически пересчитывать все результаты.
§ для представления результатов вычислений в виде диаграмм;
§ для моделирования, когда исследуется влияние различных значений параметров.
Табличный процессор получил широкое распространение во всей экономической системе: в бухгалтериях фирм и предприятий, в экономических отделах, в коммерческих банках и других организациях, что связано с большим количеством экономических операций и их универсальностью.
Условное форматирование — одна из очень удобных функций Excel, Это форматирование, которое применяется к данным только тогда, когда они удовлетворяют определенным критериям. Условное форматирование очень удобно использовать в таблицах, где данные часто изменяются.
Инструмент Microsoft Excel под названием «Условное форматирование» — это не самая известная из возможностей этой программы. Тем не менее, он позволяет добиться весьма интересных результатов.
Задача данной курсовой работы показать все прелести и возможности условного форматирования и проследить его изменение и усовершенствование.
Для решения поставленной задачи необходимо вначале рассмотреть теорию и для наглядности приводить в качестве примера диалоговые окна условного форматирования. А затем перейти к практике с помощью программ Microsoft Office Excel 2007 с ее расширенными возможностями условного форматирования и Microsoft Office Excel 2003 с ее возможностями в области условного форматирования.
В результате решения поставленной задачи в программах Microsoft Office Excel 2003 и 2007 будут показаны результаты условного форматирования, которые отражались теоретически.
— построение диаграмм, их модификация и решение экономических задач графическими методами;
— создание многотабличных документов, объединенных формулами;
— работа с электронными таблицами как с базами данных: сортировка таблиц, выборка данных по запросам;
— использование при построении таблиц информации из внешних баз данных;
— решение экономических задач типа “что — если” путем подбора параметров;
— разработка макрокоманд, настройка среды под потребности пользователя и т.д.
Единственное превосходство Lotus 1-2-3 — это скорость работы, но опять же, превышение небольшое.
Ситуация, сложившаяся на рынке электронных таблиц, в настоящее время характеризуется явным лидирующим положением фирмы Microsoft — 80% всех пользователей электронных таблиц предпочитают Excel. На втором месте по объему продаж — Lotus 1-2-3, затем Quattro Pro. Доля других электронных таблиц, например SuperCalc, совершенно незначительна.
Программа Excel обеспечивает как легкость при обращении с данными, так и их сохранность. Excel позволяет быстро выполнить работу для которой не нужно затрачивать много бумаги и времени, а также привлекать профессиональных бухгалтеров и финансистов.
Данная программа сумеет вычислить суммы по строкам и столбцам таблиц, посчитать среднее арифметическое, банковский процент или дисперсию, здесь вообще можно использовать множество стандартных функций: логических, математических, финансовых, статистических.
Функция условного форматирования пользуется популярностью именно в программе Excel. Возможно она привлекает своей легкостью в использовании.
Excel уже давно реализовала функции условного форматирования. Так давайте же обратим на них свой взгляд.
Критерий условного форматирования состоит из условных форматов. В критерии можно указать до трех условных форматов. Условный формат задается в виде условия:
Параметр «Что сравниваем» может быть задан значением выделенной ячейки, формулой выделенной ячейки (формула должна начинаться с символа «=», результатом формулы должно быть логическое значение) .
Параметр «С чем сравниваем» может быть задан константой или формулой. Формула должна начинаться с символа «=» и может содержать абсолютные и относительные ссылки.
Операции сравнения кроме всех известных операций содержат также операцию «Между» — для задания интервала значений, в который может попадать значение параметра «Что сравнивать», и операцию «Вне» для указания интервала, вне которого может находиться значение параметра «Что сравнивать».
В Excel существует возможность поиска ячеек, имеющих условный формат. Для этого следует:
Выделить любую ячейку или ячейку, имеющую условный формат;
в диалоговом окне Выделение группы ячеек выбрать и установить переключатель Условные форматы;
для выделения всех ячеек листа, для которых заданы условные форматы, выбрать переключатель Всех;
для выделения ячеек листа с теми же условными форматами, что и активная ячейка, выбрать переключатель Этих же.
Условное форматирование в Office Excel 2007 можно применять для ячейки или для пересечения ячеек.
В Excel 2007 нет больше ограничения в три критерия (цвета) в условном форматировании. Теперь для любых ячеек и диапазонов возможно задавать любое количество условий. Отображается все это вот в таком диалоговом окне ( рис.1):
значения больше, меньше, равные или не равные заданным
даты (вчера, сегодня, завтра, на прошлой неделе, в прошлом месяце и т.д.)
Теперь то, чего в старых версиях не сделать в принципе (рис.2):
цветовые шкалы (градиентная заливка в зависимости от значений, похоже на шкалу глубин-высот на географической карте или цветовую температуру)
Excel 2007 предоставило нам еще более мощные и удобные инструменты условного форматирования.
Такое форматирование является удобным для анализа данных — можно раскрасить рабочий лист так, что каждый цвет будет соответствовать определенным данным. В таком случае хватит даже беглого взгляда на лист документа, чтобы оценить проблемные места.
Для применения условного форматирования служит кнопка «Условное форматирование» на панели «Стили» ленты «Главная».
Чтобы лучше понять, как работает условное форматирование, выделите группу ячеек с уже введенными данными, нажмите кнопку «Условное форматирование» и посмотрите различные варианты форматирования.(рис.3)
По умолчанию программа автоматически определяет минимальное и максимальное значение в выделенном диапазоне и затем форматирует в равных процентных соотношениях.
Если вас не устраивают параметры форматирования по умолчанию, воспользуйтесь пунктом «Другие правила..» для формирования нужных правил форматирования.
Следует обратить внимание на возможность условного форматирования одной ячейки, содержащей текст, число или дату. Данный способ позволяет быстро найти ячейки с определенным значением. Для применения такого вида форматирования, необходимо выделить ячейку, нажать кнопку «Условное форматирование» и выбрать пункт «Правила выделения ячеек».( рис.4)
В появившемся окне задайте конкретные условия форматирования. Если не подходят стандартные операторы для форматирования, воспользуйтесь пунктом «Другие правила..».(рис.5)
В Excel 2007 имеется возможность условного форматирования верхних и нижних значений в диапазоне ячеек.
Для этого нажмите кнопку «Условное форматирование», выберите пункт меню «Правила отбора первых и последних значений», и соответствующее правило форматирования.(рис.7)
В появившемся окне можно подстроить условие форматирования.(рис.8)
В Excel 2007 к одному и тому же диапазону можно применять одновременно несколько правил условного форматирования. Для того чтобы расставить приоритеты форматирования, необходимо воспользоваться пунктом «Диспетчер правил условного форматирования» кнопки «Условное форматирование».(рис.9)
Правило, находящееся вверху списка является самым приоритетным.(рис.10)
Для прекращения действия какого-либо правила форматирования необходимо установить флажок «Остановить, если истина».
Условные форматы можно копировать (вы в этом уже убедились на примере выше). Для копирования формата в любую ячейку (диапазон ячеек) можно воспользоваться «метелкой» «Формат по образцу», расположенной на панели «Буфер обмена» ленты «Главная».
Для удаления условного форматирования необходимо выделить нужный диапазон ячеек, нажать кнопку «Условное форматирование» и выбрать пункт «Удалить правила».
Для сравнения изменений в условном форматировании посмотрим как оно проводилось в старой не обновленной программе.
Excel при вводе данных, соответствующих определенному условию, изменял оформление ячейки: заливку, границы, шрифт.
Сделать это можно с помощью окна «Условное форматирование», вызываемым так:
Здесь первое условие «=»»» прописано для того, чтобы Excel пустые ячейки не оформлял как ячейки, содержащие «0».
ВНИМАНИЕ. Имеет значение порядок условий! Если условие «=»»» поместить после «=ОСТАТ(C14;2)=0», то пустые ячейки и содержащие «0» будут подсвечиваться одинаково.
В условном форматировании можно использовать значения ячеек в формулах, в зависимости от результата вычисления формулы изменять оформление. На рисунке ниже показан пример различной подсветки ячеек с чётными числами, нечётными числами и пустых ячеек.(рис.13)
Функция «ОСТАТ(число;делитель)» находит остаток от деления. В даных формулах использована относительная ссылка на ячейку «C14» (первую ячейку диапазона), по остальным ячейкам Excel «пробежится» сам. Если бы ссылка была абсолютной (что происходит по умолчанию, если выделять из окна «УФ»), это бы работало только для ячейки «C14».
Удаление данных из ячейки с помощью «Delete» не приводит к удалению условного форматирования! Удалить его можно либо из окна «Условное форматирование» кнопка «Удалить. «, либо с помощью команды
Рассмотрев отличия старой и новой программ можно сказать, что с помощью условное форматирование в Office Excel 2007 значительно облегчается процесс визуализации данных, которые нужно анализировать или преподнести в более удобном виде.
Электронные таблицы служат для автоматизации вычислений и представлении результатов в удобной форме. При этом всю работу по вычислениям взял на себя компьютер. «Условное форматирование» — это одна из возможностей программы Excel , позволяющая эффективно работать с таблицами и проводить довольно сложные расчеты .
1. Биллиг В.А., Дехтярь М.И. VBA и Office ХР. Офисное программирование. -М.: Русская редакция, 2004. -693 с.
2.Волков, В.Б. Понятный самоучитель Excel 2007 [Текст] / В.Б. Волков.- СПб:Питер,2008.-253 с.: ил.
3.Попов, А. Excel [Текст] : практическое руководство. — М.:ДЕСС КОМ, 2000. — 302 с.
4.Шитов В.Н. Новейший самоучитель работы на компьютере. — М. ООО « Дом Славянской книги Вече» ,2008
5.Yandex [Электронный ресурс] : междунар. Поисковая система. — М., [199-]. — Режим доступа: www. yandex. ru, свободный.- Загл. С экрана
Подобные документы
Средства создания и форматирования электронных таблиц MS Excel. Условное форматирование с использованием инструмента. Анализ функции Excel следующих категорий: математические, статистические, условные, ссылки и массивы. Механизм создания сводных таблиц.
контрольная работа [480,4 K], добавлен 02.02.2015
Дисковые устройства ПК. Виды и характеристика. Сравнительные характеристики дисковых накопителей. Флэш-диски. EXCEL. Форматирование ячеек. Шрифты и форматирование текста. Форматирование числовых данных в ячейках. Применение условного форматирования.
контрольная работа [44,9 K], добавлен 17.01.2008
Назначение табличных процессоров. Форматирование шрифта, проверка орфографии, вставка объектов в редакторе Excel. Понятие функции, применение Мастера функций. Абсолютные и относительные адреса ячеек. Обработка данных средствами электронных таблиц.
Понятие электронных таблиц и табличных процессоров, их основные элементы. Типы данных и функции, используемых в электронных таблицах. Сравнительный обзор наиболее популярных табличных процессоров Microsoft Excel и OpenOffice Calc, области их применения.
Понятие и возможности MS Excel. Основные элементы его окна. Возможные ошибки при использовании функций в формулах. Структура электронных таблиц. Анализ данных в Microsoft Excel. Использование сценариев электронных таблиц с их практическим применением.
курсовая работа [304,3 K], добавлен 09.12.2009
Исследование функциональных возможностей табличных процессоров в информационном обеспечении управления. Структура информационной системы на предприятии. Понятие электронных таблиц и табличных процессоров. Тенденции развития табличных процессоров.
курсовая работа [45,4 K], добавлен 15.03.2012
Условное форматирование в сводных таблицах Excel 2010 — Сводные таблицы Excel 2010
Предположим, что в некоторой базе данных нужно определить числа, превышающие некие нормативы. Это говорит о том, что в одной какой-либо колонке могут быть заданы определенные значения, а в другой — конкретный норматив.
Условное форматирование в сводных таблицах
Сначала выделите все ячейки, которые подлежат условному форматированию; в нашем примере С4:С17. Перейдите на вкладку ленты Главная в группу Стили и щелкните на кнопке Условное форматирование (рис. 2).
Рис. 2. Для значений сводной таблицы выберите условное форматирование в виде гистограммы
В области сводной таблицы появятся гистограммы наряду со значениями в поле Сумма по полю Объем продаж2. Чтобы отобразить одну лишь гистограмму, выполните следующие действия.
- Щелкните на кнопке раскрывающегося меню Условное форматирование, и выберите пункт Управление правилами.
- В диалоговом окне Диспетчер правил условного форматирования выберите только что созданное правило Гистограмма и щелкните на кнопке Изменить правило.
- Установите флажок Показывать только столбец (рис. 3).
Рис. 3. Установите флажок Показывать только столбец, чтобы отображать только гистограммы
В следующем списке приведены готовые сценарии условного форматирования:
Чтобы удалить примененное ранее условное форматирование, поместите курсор в сводную таблицу, перейдите на вкладку Главная, щелкните на кнопке Условное форматирование и выберите в раскрывающемся меню команду Удалить правила → Удалить правила из этой сводной таблицы (см. рис. 2).
Создание пользовательских правил условного форматирования
Следует отметить, что в применении условного форматирования вы не ограничены только заранее разработанными сценариями. Вы всегда можете создать собственные правила. Обратите внимание на исходную таблицу, представленную на рис. 5. В ней добавлено вычисляемое поле, определяющее значение выручки за час (подробнее см. Вычисляемые поля и вычисляемые элементы в Excel 2013).
Рис. 6. Диалоговое окно Создание правила форматирования; установки по умолчанию
В этом диалоговом окне определяются ячейки с условным форматированием, задается тип применяемого правила и указываются параметры форматирования. Сначала нужно задать ячейки, в которых будет применяться условное форматирование. У вас небольшой выбор, включающий следующие три варианта:
- к выделенным ячейкам;
- ко всем ячейкам, содержащим значения «Объемпродаж». Условное форматирование применяется ко всем значениям в столбце Объем продаж, а также к промежуточным и общим итогам. Рекомендуется применять этот вариант в ходе анализа данных, для которых определяется среднее, процентное соотношение или другие величины, представляющие различные уровни одной и той же числовой величины.
- ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта». Условное форматирование применяется ко всем значениям в столбце Объем продаж на уровне Рынок сбыта; при этом исключаются промежуточные и общие итоги. Его лучше всего использовать для анализа отдельных значений.
Названия Объем продаж и Рынок сбыта диалогового окна Создание правила форматирования изменяются от одной таблицы к другой и отражают названия полей, содержащихся в области столбцов и активных элементов данных.
В рассматриваемом примере третий вариант кажется наиболее удачным, поэтому установите переключатель ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта» (рис. 7). В разделе Выберите тип правила укажите правило, согласно которому будет применяться условное форматирование:
Рис. 7. Диалоговое окно Создание правила форматирования; установки по умолчанию
Учтите, что гистограммы, цветовые шкалы и наборы значков применяются только в случае, если выделенные ячейки форматируются на основе введенных в них значений. Это означает, что для использования указанных индикаторов необходимо установить первый переключатель, Форматировать все ячейки на основании их значений.
В выбранном нами сценарии будем обозначать проблемные области с помощью набора значков. Поэтому в качестве типа форматирования нужно выбрать параметр Форматировать все ячейки на основании их значений.
Рис. 8. Условное форматирование применено к сводной таблице
Теперь примените такое же условное форматирование к полю Средняя выручка за час (рис. 9).
Рис. 9. Условное форматирование позволяет добиться весьма познавательных и важных результатов
[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 6.Условное форматирование в Excel
- Щелкните на кнопке раскрывающегося меню Условное форматирование, и выберите пункт Управление правилами.
- В диалоговом окне Диспетчер правил условного форматирования выберите только что созданное правило Гистограмма и щелкните на кнопке Изменить правило.
- Установите флажок Показывать только столбец (рис. 3).
Условные форматы можно копировать (вы в этом уже убедились на примере выше). Для копирования формата в любую ячейку (диапазон ячеек) можно воспользоваться «метелкой» «Формат по образцу», расположенной на панели «Буфер обмена» ленты «Главная».
