Формирование рабочих листов

ЛЕКЦИЯ 2. ФОРМАТИРОВАНИЕ РАБОЧИХ ЛИСТОВ

Применение автоформатов.

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

Чтобы применить Автоформат к таблице следует:

1. Выделить диапазон, содержащий таблицу.

2. Выполнить ФорматАвтоформат. Откроется окно Автоформат.

3. Выделить желаемый формат в списке форматов и просмотреть образец.

4. Нажать OK.

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

Рекомендуемые материалы

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

Чтобы выбрать элементы автоформатирования нужно:

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

2. Выбрать команду ФорматАвтоформат.

3. Выделить желаемый формат в списке форматов.

4. Нажать кнопку Параметры. Диалоговое окно дополнится группой флажков Изменить.

5. Сбросить в группе Изменить флажки форматов, которые не надо использовать.

6. Просмотреть результат. Нажать OK.

 Копирование и вставка форматов

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

1. Выделить ячейку, из которой нужно скопировать формат (ячейка источник).

2. Щелкнуть кнопку Формат по образцу. К обычному указателю добавится значок кисти.

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

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

Копирование формата можно также осуществить вторым способом:

1. Выделить ячейки, которые нужно скопировать.

2. Нажать кнопку Копировать на Стандартной панели инструментов.

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

Форматирование шрифта, выравнивание содержимого, изменение ориентации текста и чисел

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

1. Выделить ячейку или диапазон.

2. Выполнить команду ФорматЯчейки. Откроется диалоговое окно Формат ячеек.

3. На вкладке Шрифт выбрать шрифт из списка, начертание, цвет, эффекты, размер шрифта (высота 28 пт равна приблизительно 1 см) и т.д. Нажать кнопку OK.

Можно выполнить форматирование с помощью комбинации клавиш, что значительно ускоряет процесс форматирования:

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

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

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

1. На вкладке Выравнивание выбрать один из переключателей в группе по горизонтали, затем один из переключателей в группе по вертикали.

2. Если в ячейку введен слишком длинный текст на вкладке Выравнивание в группе Отображение выбрать опцию переносить по словам. При этом MS Excel перенесет текст так, чтобы он соответствовал ширине ячейки. Высота ячейки увеличится, чтобы вместить несколько строк.

3. Опция Объединение ячеек используется с целью распределения текста по нескольким ячейкам. В объединенную ячейку Microsoft MS Excel помещает только данные верхнего левого угла из выделенного диапазона. Данные из других ячеек уничтожаются. В объединенной ячейке текст можно выровнять по усмотрению пользователя.

В MS Excel имеется возможность одновременного объединения и центрирования текста. Для этого:

1. Скопировать нужные данные в верхнюю левую ячейку внутри диапазона.

2. Выделить ячейки, которые необходимо объединить.

3. Для объединения ячеек в одной строке или одном столбце и выравнивания по центру содержимого ячеек нажать кнопку Объединить и поместить в центре на панели инструментов Форматирование (маленькая буква а между стрелками, направленными влево и вправо).

Чтобы выполнить разбивку объединенных ячеек:

Выделить объединенную ячейку.

Щелкните кнопку Объединить и поместить в центре на панели инструментов Форматирование, после чего будет отменено объединение. Можно также выключить опцию объединение ячеек (команда ФорматЯчейки вкладка Выравнивание). Нажать кнопку OK.

Если нужно текст или числа расположить под углом или вертикально, следует:

1. Выделить заголовок или надпись, которую нужно повернуть.

2. Выбрать ФорматЯчейкивкладка Выравнивание.

3. Выбрать ориентацию текста в группе параметров Ориентация.

Конструирование числовых форматов

Для создания пользовательских числовых форматов требуется знание нескольких специальных символов, которые MS Excel использует при расшифровке форматов.

При конструировании собственных форматов эти символы вводятся в текстовое поле Тип при выполнении команды ФорматЯчейкивкладка Числогруппа Числовые форматы все форматы.

Если ячейка заполнена символами # «решетка», это значит, что ширина столбца недостаточна для отображения числа в заданном формате и следует увеличить ширину.

Символы форматирования для пользовательских форматов:

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

2) # - Действует как заполнитель для цифр, незначащие нули не отображаются. Десятичная дробь округляется до числа символов # справа от запятой. Например значение числа 7,4 в формате ### ###,##грн отобразится как 7,4р, а число 0,257 отобразится как ,26р.

3) 0 - Действует как заполнитель для цифр. Используется для отображения нулей при отсутствии цифры, незначащие нули отображаются. Десятичная дробь округляется до числа нулей справа от запятой. Например значение числа 7,4 в формате ### #0,00р отобразится как 7,40р, а число 0,257 отобразится как 0,26р. Если нужно числа от 0 до 9 представить в виде 000, 001, 002 и т.д. до 009, то пользовательский формат для таких чисел будет иметь вид 000.

4) ? - Действует как заполнитель для цифр таким же способом как 0, но незначащие нули замещаются пробелами так, что числа выравниваются правильно. Например значение числа 7,4 в формате  #0,0?р отобразится как 7,4_р (перед р - пробел).

5) _ (символ подчеркивания) – Делает пропуск шириной в символ справа от него.

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

7) Разделитель групп разрядов (пробел внутри числового формата) – Разделяет группы разрядов в числе. Нужно пометить только первое его положение. Например число 876 456 234 в формате # ##0,00р будет иметь вид 876 456 234,00р.

8) % -  Умножает число на 100 и отображает его как проценты от единицы со знаком %. При чем при вводе числа в заранее отформатированную ячейку умножение не производится.

9) : р. + - ( ) – Данные символы отображаются в том же месте форматируемого числа, в котором вводятся.

10) ”текст“ – Отображает текст, заданный в кавычках. Например, если пользователю нужно числа от 10 до 100  представить в виде Расчетный счет № 0010, Расчетный счет № 0011,..., Расчетный счет № 0099, Расчетный счет № 0100, то пользовательский формат будет иметь вид: ”Расчетный счет № “0000.

11) @ - Указывает место в формате, где будет отображен введенный пользователем текст. Например, в ячейках содержится информация:

Для получения такого результата следует пользовательский формат написать как: ”Материал “@.

12) [цвет] – Форматирует содержимое ячейки (текст и числа) заданным цветом. Можно задать цвета [Черный], [Белый], [Красный], [Синий], [Зеленый], [Желтый] или [цвет #], где # обозначает номер цвета от 1 до 56 на цветовой палитре. Цветовую палитру MS Excel можно увидеть, выполнив команду СервисПараметрывкладка Цвет. Цвета на палитре нумеруются слева направо и далее вниз.

Например: если в ячейке задан формат вида [Красный]0,00%, то число 89, введенное в эту ячейку, будет представлено красным цветом в виде 89,00%

13) *символ – Заполняет остаток ширины ячейки символом, следующим за звездочкой. Например: дополним формат [Красный]0,00%*< (звездочкой и знаком «меньше»). После ввода в ячейку числа 89 получим: 89,00%<<<<<<<<<<<<<<<<<<<<<<<<<< (26 знаков «меньше»).

14) Пробел в конце числового формата – Осуществляет сдвиг десятичной запятой на три разряда влево. Соответственно, два пробела в конце числового формата сдвигают десятичную запятую на шесть разрядов влево. Например, введенное в ячейку число 500 000 в формате ###__” млн.грн“, будет получено в виде 5 млн.грн.; число 567 в формате ###_ будет получено в виде 0,567.

15) [значение условия] – Задает внутри числового формата условие, при котором будет применяться данный формат. Например, если число больше 999, то представить его в количестве тысяч гривен, иначе – в количестве гривен. Формат числа : [>999]0,00_”тыс.грн”; 0,00”грн”. В соответствии с этим форматом получим числа:

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

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

положительный формат; отрицательный формат; нулевой формат; формат текста

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

Примеры конструирования форматов

Пример 1.

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

Рассуждаем и конструируем формат.

Активизируем первую ячейку (число -35 000). Выполним по ней щелчок правой кнопкой мыши и выберем Формат ячееквкладка Числогруппа Числовые форматы(все форматы)щелчок в поле Тип. Слово Основной убрать.

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

Поскольку первая секция представляет формат для положительных чисел, запишем в ней: [Синий]”Поступило на счет” # #0,00”грн”;

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

[Красный] ”Снято со счета” # #0,00”грн”;

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

[Зеленый] ”Счет пуст”;

Заполним четвертую секцию пользовательского формата. При вводе последнего числа 8478.57 допущена ошибка: в качестве разделителя целой и дробной части стоит точка. Настройки Windows XP предполагают использование запятой в качестве разделителя целой и дробной части. Поэтому число 8478.57 будет интерпретировано MS Excel как текст, и, следовательно, как ошибка. Формат четвертой секции:

@” Ошибка заполнения”

В итоге получим:

[Синий]”Поступило на счет”# #0,00”грн”;[Красный]”Снято со счета”# #0,00”грн”;[Зеленый]”Счет пуст”;@” Ошибка заполнения”

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

[Синий]”Поступило на счет”# #0,00”грн”;[Красный]”Снято со счета”# #0,00”грн”;;@” Ошибка заполнения”

В самом конце построенного формата точка с запятой не ставится.

Формат построен. Нажимаем.OK. Копируем построенный формат во все остальные ячейки данной графы.

Пример 2.

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

Конструируем формат. Активизируем первую ячейку данного столбца чисел. Проанализируем содержимое ячеек. Форматирование следует начинать с чисел имеющих максимальную разрядность в столбце. В данном случае максимальная разрядность – миллион. Вспомним, что пробел в конце числового формата осуществляет сдвиг десятичной запятой на три разряда влево. Соответственно, два пробела в конце числового формата сдвигают десятичную запятую на шесть разрядов влево.  Поэтому запишем:

[>999 999]# #0,00_ _” млн. грн”;[>999]# #0,00_” тыс. грн”;# #0,00” грн”

Пример 3.

Для контроля ошибок при вводе данных в ячейку отобразить числа:

– красным цветом, если они меньше или равны 500;

– синим цветом, если они больше или равны 1000;

– черным цветом все промежуточные значения.

[Красный] [<=500] 0,00;[Синий] [>=1000] 0,00; 0,00

Пример 4.

При вводе семизначного либо шестизначного числа в ячейку, эти числа должны идентифицироваться как телефонный номер. Например, введенное в ячейку число 7895458 должно быть представлено в виде 789-54-58, а число 348954 – в виде 34-89-54. Число 45678 не будет идентифицировано как телефонный номер, поскольку содержит менее шести цифр. В этом случае будет выдано сообщение «Ошибка ввода номера». Если число имеет более семи цифр, то оно может быть принято как телефонный номер (как мобильный или как номер с указанием кода города).

Формат:

[>999999]000-00-00; [>99999]00-00-00;”Ошибка ввода номера”

Форматирование чисел

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

Чтобы форматировать ячейки с помощью команд меню, следует:

1. Выделить ячейку.

2. Выполнить ФорматЯчейкиЧисло и выбрать числовой формат из представленного списка форматов. При этом:

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

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

Форматирование даты и времени

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

1. В ячейку ввести 23.06.04.

2. Выделить ячейку.

3. Выполнить ФорматЯчейкивкладка ЧислоДата. Выбрать необходимый формат из списка Тип и нажать OK.

4. Скопировать формат на весь столбец.

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

Комбинация клавиш Ctrl+; вставляет в ячейку текущую дату.

Комбинация клавиш Ctrl+: вставляет в ячейку текущее время.

 Автозаполнение

Чтобы выполнить автозаполнение ячеек столбца или строки, следует:

– ввести значение в первую ячейку столбца или строки;

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

Чтобы заполнить активную ячейку содержимым ячейки, расположенной выше (т.е. заполнить вниз), следует нажать клавиши CTRL+D. Чтобы заполнить активную ячейку содержимым ячейки, расположенной слева (т.е. заполнить вправо),  следует нажать клавиши CTRL+R.

Для выбора варианта заполнения выделенных ячеек, первая из которых заполнена, существует возможность ПравкаЗаполнить. Например, можно выбрать режим Влево, Вправо, Вниз, Вверх, Прогрессия.

Рекомендуем посмотреть лекцию "21 - Организационный механизм управления".

Например, первая ячейка заполнена цифрой 45. Выделим ячейки для автозаполнения. Выполнив команду ПравкаЗаполнитьПрогрессия и выбрав Тип прогрессии Арифметический, шаг прогрессии 2, получим в выделенных ячейках значения:

45        47       49       51       53

 Форматирование группы листов в рабочей книге

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

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

Чтобы снова разделить группу на отдельные листы нужно выполнить щелчок правой кнопкой мыши по любому из ярлычков группы и выполнить команду Разгруппировать листы.