ТАБЛИЧНЫЙ ПРОЦЕССОР EXCEL. СРЕДСТВА ФОРМАТИРОВАНИЯ ТАБЛИЦ. ПОЛЬЗОВАТЕЛЬСКИЕ ФОРМАТЫ. УСЛОВНОЕ ФОРМАТИРОВАНИЕ

Форматирование рабочего листа - это оформление табличных данных, находящихся на рабочем листе, с целью повышения их наглядности, улучшения визуального восприятия. Форматирование рабочего листа сводится к форматированию его ячеек, т. е. к определению параметров форматирования ячеек рабочего листа. Различают следующие параметры форматирования:

• формат данных;
• формат шрифта. Шрифт - это гарнитура, рисунок цифр и символов;
• выравнивание содержимого ячеек;
• обрамление ячеек. Рамка - это линия, очерчивающая ячейку или выделяющая одну из ее сторон;
• палитра. Палитра - это совокупность используемых для оформления цветов;
• узоры;
• ширина и высота ячеек.

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

Форматирование данных заключается в задании форматов чисел и текста. По умолчанию Excel вводит значе ния в соответствии с числовым форматом Общш. Значения отображаются в том виде, в каком они были введены с клавиатуры. При этом Excel анализирует значения и автоматически присваивает им нужный формат, например 100 - число 100, а 10:52 - числовой формат даты и времени.

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

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

# - символ указывает, что в данную позицию выводятся только значащие цифры, незначащие нули не отображаются. Например, ###,# 123,46 => 123,5; 0,12=>,1;

0 (ноль) - символ указывает на отображение незначащих нулей. Например, #,00 1,2=>1,20; 1=>1,00;

При использовании этого символа до или после десятичной точки вместо незначащих нулей отображаются пробелы. Например, ???,??? 1,2 => 1,2;

(пробел) - символ указывает на необходимость вставки пробела в качестве разделителя групп разрядов числа. Например, # ### 10000=>10 000;

Символ определяет положение десятичной точки.

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

В числовом формате можно задавать цветовое представление значений. В этом случае первым элементом в шаблоне должен быть код цвета. Например, [синий] # ##0,00;[красный]-# ##0,00.==> все положительные значения будут выведены синим цветом, все отрицательные - красным.

В числовом формате можно задавать формат, используемый только для чисел, удовлетворяющих заданному условию. Условие должно состоять из оператора сравнения и значения, заключенных в квадратные скобки. Например, [красный][<1000]0;[синий][>1000]0 => все значения, большие тысячи, будут выведены синим цветом, остальные - красным.

Форматы даты и времени. Для форматирования данных типа данных и времени используются следующие символы:

Д - для отображения дней: Д - дни 1-31; ДД - 01-31; ДДД - Пн - Вс; ДДДД - Понедельник - Воскресенье.

М - для отображения месяца: М - 1-12; ММ - 01-1 12; МММ - янв - дек; ММММ - январь - декабрь; МММММ - первая буква месяца.

Г - для отображения лет: ГГ - 00-99; ГГГГ - 1900-9999.

Ч - для отображения часов: ч - 0-23; чч - 00-23.

М - для отображения минут: м - 0-59; мм - 00-59. ,

С - для отображения секунд: с - 0-59; cc - 00-59.

0 (ноль) - для отображения долей секунд: ч:мм:сс.00.

АМ/РМ - для отображения двенадцатичасовой системы.

Для отображения интервалов времени. Позволяет выводит значения, превышающие 24 часа, 60 минут или 60 секунд.

Форматы могут быть составными, т. е. иметь числовую и текстовую часть. Текстовая часть всегда является последней в формате. Символы шаблона:

• "" \ - двойные кавычки или обратная косая черта указывают на необходимость вставки, начиная с позиции шаблона, текстовых символов, следующих за ним в формате. Например, 0,00р. «Излишки»;[красный]-0,00р. «Недостатки»;
• @ - указывает, что начиная с этой позиции формата могут выводиться любые символы (текст);
• * - указывает, что, начиная с позиции символа в формате, необходимо многократно вывести символ, следующий за * (пока столбец не окажется заполненным по ширине);
• _ - символ подчеркивания, используется для выравнивания содержимого ячейки; вместо него проставляется пробел, по ширине равный следующему за ним символу.

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

Формат полозк.чисел;формат отриц.чисел;формат нулевых знач.;текст

Можно указать пустую секцию, если для данного вида значений формат отсутствует. Для этого следует сразу ввести знак «;».

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

Критерий условного форматирования состоит из условных форматов. В критерии можно указать до трех условных форматов. Условный формат задается в виде условия:

<что сравниваем> операция сравнения <с чем сравниваем>

Параметр Что сравниваем может быть задан значением выделенной ячейки, формулой выделенной ячейки (формула должна начинаться с символа «=», результатом формулы должно быть логическое значение ИСТИНА или ЛОЖЬ).

Параметр С чем сравниваем может быть задан константой или формулой. Формула должна начинаться с символа «=» и может содержать абсолютные и относительные ссылки.

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

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

1. Выделить любую ячейку или ячейку, имеющую условный формат;
2. выполнить команду Перейти меню Правка;
3. выбрать кнопку Выделение;
4. в диалоговом окне Выделение группы ячеек выбрать и установить переключатель Условные форматы;
5. для выделения всех ячеек листа, для которых заданы условные форматы, выбрать переключатель Всех;
6. для выделения ячеек листа с теми же условными форматами, что и активная ячейка, выбрать переключатель Этих же.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При форматировании таблиц необходимо придерживаться следующей последовательности:

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

2. Изменение параметров абзацев текста ячеек таблицы – по той же причине.

3. Установка режима расстановки переносов (если ранее не был установлен), т.к. это тоже сокращает тот объем, который занимает текст.

4. Изменение ширины столбцов таким образом, чтобы таблица заняла как можно меньше места. Этот момент хотелось бы подчеркнуть. Взыскательный читатель может сказать, что «не всегда требуется, чтобы таблица занимала минимум места». Такое бывает, однако. В большинстве случаев требуется именно уменьшение места, занимаемого таблицами.

5. Объединение и разбиение ячеек.

6. Изменение ширины столбцов после объединения или разбиения ячеек .

7. Изменение Выравнивания текста и обрамления(границ) .

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

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

Расстановка переносов устанавливается для всего документа (Се рвис ® Я зык ® Расстановка п ереносов ® Автоматическая расстановка переносов ). Для большинства документов данная настройка обязательна.

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

После чего отпустить удерживаемую кнопку мыши. Получим.

Теперь точно также изменим ширину столбца «Объем производства ». Получим:

Теперь объединим ячейки 2 и 3 первой строки. Для этого:

Выделим эти ячейки;

Вызовем контекстное меню щелчком правой кнопкой мыши;

Выберем пункт Объединить ячейки и произведем щелчок по нему левой кнопкой мыши;

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

Теперь необходимо также объединить ячейки 1 и 2 первого столбца, ячейки 1 и 2 четвертого столбца, а также ячейки 1 и 2 пятого столбца. Получим:

Выровняем ширину строк 1 и 2 с тем, чтобы ячейка «Объем производства» была бы одинакова по высоте с ячейками «план » и «факт ». Для этого подведем курсор мыши к горизонтальной граничной линии между этими ячейками (курсор мыши пример вид). Теперь необходимо перетащить горизонтальную линию так, чтобы визуально ячейки первой и второй строки были бы равны по высоте.

Теперь можно приступать к выравниванию и обрамлению:

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

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

В результате получим:

Теперь изменим обрамление ячеек шапки на линию большей толщины.

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

Изменение размеров строк и столбцов осуществляется двумя способами:

С помощью мыши .

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

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

!! Поупражняйтесь в изменении размеров строк и столбцов с помощью мыши.

С помощью команд меню Формат/Строка (Столбец) .

В общем случае для изменения высоты строки необходимо подать команду Формат/Строка/Высота… или в контекстном меню заголовка строки выбрать пунктВысота строки… . В результате появится окноВысота строки , в котором следует ввести требуемую высоту строки (в пунктах) и нажатьОК .

Если требуется изменить высоту строки до размеров, необходимых для отображения всех находящихся в ней данных, то используется команда Формат/Строка/Автоподбор высоты .

!! Поупражняйтесь в изменении размеров строк с помощью команд меню.

Изменение ширины столбца выполняется аналогично командой Формат/Столбец/Ширина… или выбором в контекстном меню заголовка столбца пунктаШирина столбца… . Для автоматического изменения ширины подается командаФормат/Столбец/Автоподбор ширины . Она позволяет выбрать ширину столбца таким образом, чтобы в него полностью помещалось содержимое наиболее заполненной ячейки.

!! Поупражняйтесь в изменении размеров столбцов с помощью команд меню.

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

выделить скрываемые столбцы (строки);

подать команду Формат/Столбец (Строка)/Скрыть Скрыть .

Наличие в электронной таблице скрытых столбцов (строк) легко обнаруживается по отсутствию их названий в заголовках столбцов (строк).

Для отображения скрытых столбцов или строк необходимо:

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

подать команду Формат/Столбец (Строка)/Отобразить или в контекстном меню выделенного блока выбрать пунктОтобразить .

!! Поупражняйтесь в скрытии и отображении строк и столбцов.

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

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

!! Активизируйте ячейку D 8 , откройте диалоговое окно Формат ячеек и перейдите на вкладку Число . Выбирая различные числовые форматы, просмотрите в поле Образец представление данных ячейки в соответствующем формате.

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

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

!! Выровняйте содержимое ячейки F 7 по центру.

В поле Отображение устанавливаются флажки:

переносить по словам – служит для автоматического переноса текста в границах ячейки;

автоподбор ширины – эквивалент команды Формат/Столбец/Автоподбор ширины ;

объединение ячеек – служит для объединения выделенных ячеек в одну с адресом верхней левой ячейки.

!! Объедините ячейки G 4: G 5 . Выровняйте содержимое по центру (по горизонтали и вертикали).

Операцию объединения ячеек с одновременным выравниванием текста по центру можно также выполнить с помощью кнопки Объединить и поместить в центре на панелиФорматирование .

!! Примените данную операцию к ячейкам G 1: H 1 .

Текст в ячейки можно вводить в виде нескольких строк. Принудительный перенос (или разрыв строки) в пределах ячейки осуществляется с помощью комбинации клавиш Alt + Enter .

!! Разорвите текст в ячейке А2 с помощью команды Alt + Enter .

В поле Ориентация задается наклон текста относительно горизонтали. Угол наклона можно устанавливать перемещением указателяНадпись с помощью мыши или счетчикаградусов .

!! Установите наклон текста в ячейке G 4 в 45 градусов.

Вкладка Шрифт предназначена для задания параметров шрифта выделенной ячейки или блока (тип, начертание, размер, цвет и др.).

!! Установите в ячейке А3 следующие параметры шрифта: тип - Times New Roman, начертание – полужирный курсив, размер – 12, подчеркивание – двойное, по значению, цвет - красный.

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

выбрать цвет и тип линии;

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

!! Задайте для диапазона ячеек A 1: D 3 следующие параметры границ: внешние – сплошная жирная линия синего цвета, внутренние – пунктирная жирная линия темно-бирюзового цвета.

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

!! Установите в ячейках А1:А3 цвет фона – бледно-желтый, узор – 12,50%-ый изумрудный, а в ячейках B 1: D 3 цвет фона – бледно-зеленый, без узора.

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

!! Проверьте действие кнопок панели инструментов Форматирование на блоке A 1: D 3 , задавая параметры по своему усмотрению.

Excelпредлагает несколько стандартных вариантов оформления таблицы (обрамление и заполнение фоном). Для этого нужно выделить таблицу и подать командуФормат/Автоформат… . Затем в открывшемся диалоговом окнеАвтоформат выбрать из предлагаемого списка форматов подходящий вариант оформления таблицы. Ее выбранный формат отображается в полеОбразец .

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

!! Примените к блоку A 1: D 3 формат Цветной 1.

!! Сохраните файл " фамилия ".

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

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

Как создать и форматировать как таблицу в Excel?

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

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

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

Чтобы очистить формат нужно предварительно выделить необходимый диапазон ячеек и выбрать инструмент на вкладке «Главная»-«Редактирование»-«Очистить форматы». Для быстрого удаления только границ ячейки используйте комбинацию горячих клавиш CTRL+SHIFT+(минус). Только не забудьте перед этим выделить саму таблицу.