No Image

Форматирование ячеек в excel 2007

СОДЕРЖАНИЕ
270 просмотров
10 марта 2020

Учебная дисциплина
Информатика и компьютерная техника
Модуль 2. Офисные прикладные программы Microsoft Office 2007

2.2.3. Форматирование ячеек и применение стилей

Форматирование ячеек

Форматирование ячеек электронных таблиц является необходимым условием работы с данными в Excel 2007. Форматирование ячеек осуществляется с помощью раскрывающегося списка "Числовой формат" или окна диалога "Формат ячеек". Это окно имеет шесть вкладок: Число, Выравнивание, Шрифт, Граница, Заливка, Защита (рисунок 1). Окно диалога открывается при щелчке левой кнопкой мыши на стрелке группы "Число" на вкладке "Главная".

На вкладке "Число" окна "Формат ячеек" можно назначать числовые форматы ячейкам электронных таблиц. Причем форматы ячейкам электронных таблиц можно назначать как до ввода данных, так и после их ввода в ячейки. К числовым форматам относятся: Общий, Числовой, Денежный, Финансовый и т.д. (рис. 1).

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

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

К форматированию ячеек относятся и такие операции как объединение ячеек, выравнивание и направление текста в ячейках, перенос по словам и т.д. Эти операции можно выполнить в окне диалога "Формат ячеек" на вкладке "Выравнивание" (рис. 1) или в группе "Выравнивание" на вкладке "Главная" (ris. 2).

Форматирование шрифта можно осуществлять в окне диалога "Формат ячеек" с помощью инструментов на вкладке "Шрифт" или в группе "Шрифт" на вкладке "Главная". Необходимо отметить, что шрифт и другие параметры Excel 2007, действующие в режиме по умолчанию, можно изменить в окне диалога "Параметры Excel". Это окно можно открыть, выполнив команду Кнопка "Office"/"Параметры Excel" (ris. 3)

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

Кроме того, в Excel 2007 имеется инструмент "Формат" в группе "Ячейки" на вкладке "Главная". Этот интрумент применяется для изменения (форматирования) высоты строки или ширины столбца, защиты или скрытия ячеек, строк, столбцов, листов, упорядочения листов (ris. 4).

Применение стилей

Набор атрибутов форматирования ячеек, сохраненный под уникальным именем, называется стилем. Стили ячеек можно создавать и применять к ячейкам. Инструменты стилей ячеек помещены в группу "Стили" на вкладке "Главная" (рисунок. 5).

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

Быстрое форматирование диапазона ячеек и преобразование его в таблицу с помощью выбора определенного стиля можно осуществить инструментами "Форматировать как таблицу" из группы "Стили" на вкладке "Главная" (рисунок 7)

Быстрое форматирование ячейки с помощью выбора одного из определенных стилей осуществляется инструментами "Стили ячеек" из группы "Стили" на вкладке "Главная" (рис. 8).

Кроме того, для изменения внешнего вида рабочей книги в Excel 2007 применяется инструмент "Тема". В Excel 2007 имеется набор встроенных тем, которые открываются на вкладке "Разметка страницы" в группе "Темы" (рисунок 9).

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

Как сделать условное форматирование в Excel

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

Читайте также:  Вырезать нано сим из обычной шаблон

При нажатии на стрелочку справа открывается меню для условий форматирования.

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

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

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

Выходим из меню нажатием кнопки ОК.

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

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» – «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» – «Правила выделения ячеек» – «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

  1. Выделите первую ячейку диапазона с условным форматированим.
  2. Откройте меню инструмента, нажмите «Управление правилами».

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

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

  • 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» – «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
  • 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

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

Выделяем диапазон с датами.

Применим к нему «УФ» – «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

Условное форматирование в Excel с использованием формул

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

Читайте также:  Электроемкость цилиндрического конденсатора вывод формулы

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Условное форматирование строки по значению ячейки

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

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» – «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

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

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

Товар Месяц День Склад Продано Менеджер Заказчик
1 Грейпфрут январь 31 #001 56 Васин Южный
2 Абрикос январь 31 #002 45,98 Васин Салют
3 Морковь январь 31 #002 3,98 Васин Шанхай
4 Нектарин январь 31 #002 34,23 Васин Звезда
5 Баклажан январь 31 #001 18,05 Иванов Шанхай
6 Грейпфрут февраль 1 #001 4,87 Петров Южный
7 Лук февраль 1 #002 3 Петров Тандем
8 Персик февраль 1 #001 4,32 Васин Тандем
9 Морковь февраль 1 #002 2,9 Михайлов Шанхай
10 Картофель февраль 1 #001 67,4 Михайлов Шанхай
11 Абрикос февраль 2 #001 2,3 Васин Салют
12 Морковь февраль 2 #002 45 Петров Шанхай

Для начала нужно этот список в Excel оформить. В первом столбце Вы видите номер сделки. Допустим, нужно, чтобы номер был не 1, а 0001, не 2 а 0002 и так далее. Как быть в таком случае? Если Вы попробуете поставить нули перед числами, это Вам сделать не удастся — нули исчезнут.

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

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

Читайте также:  Glpi установка centos 7

Теперь посмотрим на столбец Продано . В нем прописаны суммы сделок, но нет валюты. Также денежные суммы обычно прописываются с двумя числами после запятой, например 2,00 $ . Измените формат ячеек Excel этого столбца. Выделяете весь столбец, и выбираете формат Денежный , либо соответствующую иконку ниже. Числа прописались с рублями. Еще раз раскройте список с форматами, и выберите Другие числовые форматы . Выберите Денежный в поле слева, и в поле Обозначение прокручиваете список вниз. Если Вы хотите, чтобы значок доллара был в ячейках справа от цифр, выберите, к примеру, обозначение Французский (Канада) . Нажимаете ОК.

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

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

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

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

Например, введите в какую-то пустую ячейку 13:30, а в другую 14:40. В третью ячейку введите формулу Excel: вычтите из второй ячейки первую. Будет показана разница во времени, 1:10:00, и формат третьей ячейки будет Время . Если Вы сделаете формат ячейки Общий , то результат будет 0,0486. Это происходит потому, что формат Время при вычислениях преобразуется в количество суток. 1:10 — это 1 час 10 минут, или 0,0486 суток. А уже потом это число преобразуется в формате Время в 1:10, и мы этот результат видим.

Попробуйте проделать похожую операцию с датами. В одну ячейку поставьте, например, 01.01.13, а в другую 24.02.13. Эти данные преобразуются в 01.01.2013 и 24.02.2013, а форматы ячеек Excel станут Дата . Теперь в новой ячейке вычтите из второй ячейки первую. Получится 54, что означает количество дней между двумя датами.

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

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

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

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

Также Вы можете подписаться на блог, и узнавать о всех новых статьях.
Это не займет много времени. Просто нажмите на ссылку ниже:
Подписаться на блог: Дорога к Бизнесу за Компьютером

Вам понравилась статья? Поделитесь, буду весьма признателен:

Комментировать
270 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock
detector