Работа с данными

Применение существующего формата к данным

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

В диалоговом окне Стиль (Style) вы можете присвоить ячейке существующий стиль. Если в списке стилей отсутствует нужный вам стиль, вы можете создать ваш собственный, набрав имя в поле Имя стиля (Style Name) и затем нажав кнопку Изменить (Modify). Откроется диалоговое окно Формат ячеек (Format Cells). Настроив характеристики вашего нового стиля, нажмите ОK, чтобы добавить его в список стилей.

В диалоговом окне Стиль (Style) довольно много параметров, и использовать его лишь для того, чтобы применять формат, присвоенный вами одной ячейке, к содержимому других ячеек, нерационально. Это удобнее сделать с помощью кнопки ; просто щелкните на ячейке, имеющей формат, который вы хотите применить к другим ячейкам, нажмите кнопку Формат по образцу (Format Painter) и выберите ячейки для присвоения им формата.

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

На панели инструментов Стандартная нажмите кнопку Открыть (Open). Появится диалоговое окно Открытие документа (Open).

Дважды щелкните на файле CreateNew.xls. Документ CreateNew.xls откроется.

Перейдите к листу "январь", щелкнув на соответствующем ярлычке листа.

Щелкните на ячейке С3.

В меню Формат (Format) выберите пункт Стиль (Style). Откроется диалоговое окно Стиль (Style), в строке Имя стиля (Style Name) будет выделен стиль Обычный (Normal).

В строке Имя стиля (Style Name) удалите текущее имя и введите Особый. Станет доступной кнопка Добавить (Add - добавить).

Нажмите кнопку Изменить (Modify). Откроется диалоговое окно Формат ячеек (Format Cells).

Щелкните на вкладке Шрифт (Font). Откроется вкладка Шрифт (Font).

В строке Начертание (Font Style) выберите Полужирный курсив (Bold Italic). Текст в окне просмотра в правом нижнем углу диалогового окна отобразится в выбранном вами начертании.

Щелкните на вкладке Выравнивание (Alignment). Откроется вкладка Выравнивание (Alignment).

В строке По горизонтали (Horizontally) щелкните на кнопке списка и выберите По центру (Center).

Нажмите ОK. Диалоговое окно Формат ячеек (Format Cells) закроется.

Нажмите ОK. Диалоговое окно Стиль (Style) закроется, и текст в ячейке C3 отобразится в выбранном стиле.

На панели инструментов Стандартная нажмите кнопку Формат по образцу (Format Painter). Указатель мыши изменит вид на белый крест со значком кисти рядом с ним.

Щелкните на ячейке B5. Ячейке B5 присвоится формат ячейки C3.

На панели инструментов Стандартная нажмите кнопку Сохранить (Save). Excel сохранит ваши изменения.

Нажмите кнопку . Документ CreateNew.xls закроется.

Повышение удобочитаемости чисел

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

В качестве примера рассмотрим телефонные номера США. Эти номера, состоящие из десяти цифр, имеющие 3-значный код штата, 3-значный номер АТС и 4-значный номер линии, записываются в виде (###) ###-####. Конечно, можно вводить в ячейки телефонные номера, соблюдая правила их написания, но гораздо легче просто ввести последовательность цифр, чтобы они автоматически преобразовались должным образом.

Вы можете настроить Excel так, чтобы введенная последовательность цифр преобразовывалась в телефонный номер. Откройте диалоговое окно Формат ячеек (Format Cells), перейдите во вкладку Число (Number) и, щелкнув на числовом формате Дополнительный (Special), выберите в поле Тип (Type) формат Телефонный номер (Phone Number).

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

Совет. Если вы ввели 9-значный номер в ячейке, в которой установлена опция распознавания телефонного номера, сообщения об ошибке не последует; в данном случае, код штата будет двухзначным. Например, номер 425555012 отобразится как (42)555-5012. 11-значные номера отображаются как номера с 4-значным кодом штата.

Точно так же, как и в случае с преобразованием телефонного номера, можно настроить Excel и для отображения даты или денежных сумм. Эти изменения можно сделать из диалогового окна Формат ячеек (Format Cells), выбрав либо числовой формат Дата (Date), либо Финансовый (Currency). В формате Дата (Date) можно настроить ячейку на отображение даты (и включить или выключить изменение представления даты в зависимости от настроек Local Settings операционной системы компьютера при просмотре рабочей книги). Таким же образом, выбрав Финансовый (Currerncy) формат числа, можно настроить число знаков после запятой (десятичной точки), а также выбрать нужный символ валюты и способ отображения отрицательного баланса.

Также можно создать свой числовой формат с возможностью добавления слова или фразы к числу в ячейке. Например, можно добавить фразу "в месяц" в ячейку с формулой, которая подсчитывает средний объем ежемесячных продаж за год, чтобы запись в ячейке воспринималась именно как средний объем продаж за месяц. Чтобы создать свой собственный числовой формат, выберите пункт Ячейки (Cells) в меню Формат (Format), чтобы открыть диалоговое окно Формат ячеек (Format Cells). Затем перейдите во вкладку Число (Number).

В списке числовых форматов выберите Все форматы (Custom), чтобы отобразить существующие форматы в списке Тип (Type). Теперь вы можете выбрать подходящий вам базовый формат для редактирования его в строке Тип (Type). Например, при выборе формата "0.00", Excel будет отображать все числа в ячейках как числа с двумя знаками после запятой.

Чтобы изменить формат, щелкните на строке Тип (Type) и добавьте к формату любые нужные вам символы или текст. Например, если ввести перед форматом значок "$", а справа от формата ввести "в месяц", число 1500 будет отображаться в виде "$1500.00 в месяц".

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

На панели инструментов Стандартная нажмите кнопку Открыть (Open). Появится диалоговое окно Открытие документа (Open).

Дважды щелкните на файле EasyRead.xls. Документ EasyRead.xls откроется.

Щелкните на ячейке B4.

В меню Формат (Format) выберите Ячейки (Cells). Откроется диалоговое окно Формат ячеек (Format Cells).

Перейдите во вкладку Число (Number).

В списке Формат числа (Category) выберите формат Дата (Date). В списке

Выберите в списке Тип (Type) "*3/14/01".

Нажмите ОK. Excel присвоит ячейке выбранный формат.

На панели инструментов Стандартная нажмите кнопку Формат по образцу (Format Painter). Ячейка B4 выделится пунктирной рамкой.

Щелкните на ячейке B5 и перетащите указатель на ячейку B23. Excel присвоит формат ячейки B4 ячейкам В5:В23.

Щелкните на ячейке J4.

В меню Формат (Format) выберите пункт Ячейки (Cells). Откроется диалоговое окно Формат ячеек (Format Cells).

В поле Формат числа (Category) выберите формат Дополнительный (Special). Поле Тип (Type) отобразит дополнительные числовые форматы.

В поле Тип (Type) выберите Номер телефона (Phone Number) и нажмите ОK. Диалоговое окно Формат ячеек (Format Cells) закроется.

На панели инструментов Стандартная нажмите кнопку Формат по образцу (Format Painter). Ячейка J4 выделится пунктирной рамкой.

Щелкните на ячейке J5 и перетащите на ячейку J23. Excel присвоит формат ячейки J4 ячейкам J5:J23.

Щелкните на ячейке K4.

В меню Формат (Format) выберите пункт Ячейки (Cells). Откроется диалоговое окно Формат ячеек (Format Cells).

В поле Формат числа (Category) выберите Все форматы (Custom). В окне поля

В поле Тип (Type) выберите формат #,##0.00. В строке Тип (Type) отобразится "#,##0.00".

Щелкните на месте слева от формата в строке Тип (Type) и введите $, затем щелкните справа от формата и введите всего.

Нажмите ОK. Диалоговое окно Формат ячеек (Format Cells) закроется.

На панели инструментов Стандартная нажмите кнопку Формат по образцу (Format Painter). Ячейка K4 выделится пунктирной линией.

Щелкните на ячейке K5 и перетащите указатель на ячейку K23. Excel присвоит формат ячейки K4 ячейкам K5:K23.

В ячейке В4 введите 25 января 2001 года и нажмите (Enter). Содержимое ячейки сменится на "1/25/01", соответственно тому формату, который вы установили ранее.

В ячейке С4 наберите С100001.

В ячейке D4 наберите Стивен.

В ячейке E4 наберите Леви.

В ячейке F4 наберите 6789 Элм Стрит.

В ячейке G4 наберите Рэдмонд.

В ячейке H4 наберите штат Вашингтон.

В ячейке I4 наберите 87063.

В ячейке J4 наберите 4255550102. Содержимое ячейки изменится на "(425) 555-0102", согласно заданному вами формату.

В ячейке K4 наберите 2400. Содержимое ячейки изменится на "$2,400.00 всего", согласно созданному вами ранее формату.

На панели инструментов Стандартная нажмите кнопку Сохранить (Save) для сохранения изменений.

Нажмите кнопку , чтобы закрыть документ EasyRead.xls.

Изменение представления данных в зависимости от их значения

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

Другой способ, позволяющий упростить работу с информацией - настроить Excel так, чтобы оформление данных изменялось в зависимости от их значения. Такие форматы называются условными форматами, или форматами с условием, т.к. данные должны отвечать определенным условиям, чтобы к ним применился тот или иной формат. Например, если владелец компании Кэтрин Тернер хочет выделить все дни субботы, в которые объем продаж компании "Все для сада" составил более $4.000, ей потребуется создать условный формат, проверяющий значение в ячейке с данными по продажам и изменяющий формат содержимого ячейки при выполнении условия.

Чтобы создать условный формат, выделите ячейки, к которым вы хотите его применить, и откройте диалоговое окно Условное форматирование (Conditional Formatting). На рисунке ниже показаны настройки в диалоговом окне Условное форматирование (Conditional Formatting) по умолчанию.

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

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

После того как вы создали условие, вам нужно установить формат, который будет применяться к данным, отвечающим данному условию. Формат создается в диалоговом окне Формат ячеек (Format Cells). С помощью этого диалогового окна можно настроить шрифт, которым отображается значение в ячейке. Определенный вами формат появится в диалоговом окне Условное форматирование (Conditional Formatting).

Если это необходимо, можно не ограничиваться присвоением лишь одного условия каждой ячейке. Можно создать дополнительные условия, нажав кнопку А также (Add) в диалоговом окне Условное форматирование (Conditional Formatting). После нажатия кнопки А также (Add) появится окно второго условия.

Excel выполняет условия в том порядке, в котором вы задали их в диалоговом окне Условное форматирование (Conditional Formatting) и, при нахождении условия, которому отвечают данные, прекращает сравнения. Например, предположим, что Кэтрин хочет визуально разделить лимиты кредитования покупателей компании "Все для сада" на две различные категории: лимиты меньше 1.500$, и лимиты в пределах от 1.500$ до 2.500$. Она может установить отображение лимитов кредитования покупателей с применением условного формата, использующего условия, показанные на рисунке ниже.

В этом случае, Excel будет сопоставлять значение "1250" с первым значением, "<2500", и применять форматирование к ячейке, содержащей это значение. Второе условие, "<1500", ближе по модулю к сравниваемому с ним значению; как только Excel найдет условие, отвечающее этому значению, сравнение прекратится.

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

В панели инструментов Стандартная нажмите кнопку Открыть (Open). Появится диалоговое окно Открытие документа (Open).

Дважды щелкните на файле Conditional.xls. Документ Conditional.xls откроется.

Щелкните на ячейке K4.

В меню Формат (Format) выберите Условное форматирование (Conditional Formatting). Откроется диалоговое окно Условное форматирование (Conditional Formatting).

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

В первой строке аргумента введите 1000.

Во второй строке аргумента введите 2000.

Нажмите кнопку Формат (Format). Откроется диалоговое окно Формат ячеек (Format Cells).

Перейдите во вкладку Шрифт (Font).

В строке Цвет (Color) щелкните на кнопке списка и выберите из появившейся палитры синий (Blue) цвет. Цветовая палитра исчезнет, и цвет текста в области просмотра станет синим.

Нажмите ОK. Диалоговое окно Формат ячеек (Format Cells) закроется.

Нажмите кнопку А также (Add). В диалоговом окне появится секция Условие 2 (Condition 2).

Во второй строке со списком щелкните на стрелке и выберите из списка пункт "между".

В строке первого аргумента наберите 2000.

В строке второго аргумента наберите 2500.

Нажмите кнопку Формат (Format). Откроется диалоговое окно Формат ячеек (Format Cells).

В строке Цвет (Color) щелкните на стрелке и выберите из появившейся палитры зеленый цвет. Цветовая палитра исчезнет, и цвет текста в области просмотра изменится на зеленый.

Нажмите ОK. Диалоговое окно Формат ячеек (Format Cells) закроется.

Нажмите ОK. Диалоговое окно Условное форматирование (Conditional Formatting) закроется.

В ячейке K4 щелкните на прямоугольной точке в правом нижнем углу и перетащите указатель на ячейку K6. Содержимое ячеек K5 и K6 сменится на "$2.400.00", и появится кнопка Параметры автозаполнения (Auto Fill Options).

Щелкните на кнопке Параметры автозаполнения (Auto Fill Options) и в появившемся меню выберите Заполнить только форматы (Fill Formatting Only). Содержимое ячеек K5 и K6 изменится на их прежние значения, и Excel применит к выделенным ячейкам условное форматирование.

На панели инструментов Стандартная нажмите кнопку Сохранить (Save), чтобы сохранить сделанные изменения.

Нажмите кнопку Закрыть (Close) для закрытия документа Conditional.xls.