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

После ввода данных в ячейки листа для представления информации на экране и в распечатанном виде проводится форматирование. Основные ко­манды для этого (Ячейки…, Строка, Столбец, Лист, Автоформат…, Ус­ловное форматирование, Стиль) включены в пункт меню Формат (напом­ним общее правило: перед выполнением любой команды, направленной на объект, последний должен быть выделен).

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

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

При создании заголовков столбцов в пользовательских таблицах, так на­зываемой «шапке» таблицы, часто используют прием, позволяющий отобра­зить текст, введенный в одну ячейку таблицы, сразу в нескольких соседних с ней ячейках. Этот прием заключается в использовании кнопки Объединить и поместить в центре, расположенной на панели инструментов Формати­рование. Другой способ для объединения – пункт меню Формат –команда Ячейки - вкладка Выравнивание - флажок Объединение ячеек (чтобы от­казаться от объединения ячеек, нужно сделать активной зону объединен­ных ячеек и, вызвав тот же пункт меню, снять этот флажок).

Вкладка Шрифт диалогового окна Формат ячеек содержит команды для изменения шрифта, начертания, размера и цвета данных в выделенных ячейках. Для выделения итоговой информации, названия документа и других данных на листе применяются линии различной толщины, рамки и экзотиче­ские фигуры. Команды этой группы объединены во вкладке Граница диало­гового окна Формат ячеек. Для добавления заливки к ячейкам в диалоговом окне Формат ячеек необходимо выбрать вкладку Вид, а в ней нужные цвета и фоновый узор. Цвет фоновой заливки ячеек можно изменить кнопкой Цвет заливки на панели инструментов Форматирование.

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

Ширина столбцов может быть различной (в одном столбце ширина всех ячеек одинакова). В Excel при нехватке места для отображения числа ячейка листа расширяется. Изменение ширины столбца на точное значение осущест­вляется по команде Столбец - Ширина из пункта меню Формат (ввести но­вое значение для ширины столбца). Изменять ширину столбца можно с по­мощью мыши. Для этого указатель мыши необходимо поставить на правую границу заголовка столбца (примет вид двусторонней стрелки); перетащить указатель мыши влево или вправо до получения нужной ширины столбца; отпустить кнопку мыши. Для изменения ширины нескольких столбцов мы­шью следует выделить их, а затем установить ширину одного столбца, от­пустив кнопку мыши. Все выделенные столбцы примут одинаковую ширину.

Изменение высоты строки на точное значение осуществляется по ко­манде Строка - Высота из пункта меню Формат (ввести новое значение для высоты строки). Изменять высоту строки можно с помощью мыши. Для этого указатель мыши необходимо расположить на нижней границе заго­ловка строки (примет вид двусторонней стрелки); перетащить указатель мыши вверх или вниз, изменяя высоту строки; получив нужную высоту строки, отпустить кнопку мыши.

В меню Формат имеется команда Автоформат, которая при формати­ровании одновременно накладывает несколько атрибутов формата. По умол­чанию эта команда автоматически устанавливает следующие флажки: Вы­равнивание, Формат чисел, Рамка, Шифр, Узоры, Ширину и Высоту. В диалоговом окне Автоформат с помощью кнопки Параметры можно снять ненужные параметры.

Смысл условного форматирования заключается в автоматически изме­няющемся формате ячейки (диапазона ячеек) в зависимости от их содержа­ния (например при уменьшении или увеличении значения числа в ячейке, по­следнее автоматически выводится измененным шрифтом и фоном). Услов­ное форматирование выполняется по команде Условное форматирование из пункта меню Формат. До подачи команды надо выделить диапазон, в кото­ром предусмотрено условное форматирование. В диалоговом окне устанав­ливаются соответствующие параметры.

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

В Excel можно предварительно сформировать модель электронной таб­лицы, т.е. шаблон (трафарет или форму документов), содержащий эле­менты оформления таблицы, формулы и другие объекты, предназначенная для создания аналогичных листов в будущем (ввода данных в готовый бланк). Шаблон можно создавать как для книг, так и для отдельных листов (шаблон для книги по умолчанию имеет название Book.xlt, а для листов - Sheet.xlt). Шаблоны, входящие в Excel, позволяют автоматизировать получе­ние однотипных документов при каждом их создании. В состав Excel входят «свои» шаблоны, и можно спроектировать пользовательские шаблоны. Дос­туп к шаблонам осуществляется по команде Создать из пункта меню Файл - На моем компьютере - вкладка Решение. Если подходящего шаблона на вкладке нет, то его можно установить. Для этого необходимо:

· создать книгу, содержащую один лист, и поместить на нем элементы форма­тирования для всех листов этого типа;

· подать команду Сохранить как из пункта меню Файл;

· в поле Тип файла из раскрывающегося списка выбрать пункт Шаблон;

· в поле папка указать папку, в которой должен быть сохранен шаблон;

· в поле Имя шаблона ввести имя шаблона и щелкнуть на кнопку Сохра­нить.

Для создания листа в следующий раз по сохраненному шаблону следует подать команду Создать из пункта меню Файл - найти шаблон и дважды щелкнуть по его пиктограмме.

Средства анализа данных в таблицах

В MS Excel в целях анализа имеется возможность графического пред­ставления данных в виде диаграмм. При этом можно создать либо внедрен­ную диаграмму, либо лист диаграммы. Внедренная диаграмма - это объект, расположенный на листе и сохраняемый вместе с листом при сохранении книги. Лист диаграммы - лист книги, содержащий только диаграмму (вне­дренные диаграммы и листы диаграммы связываются с данными таблиц и обновляются при изменении данных в таблице). Для того чтобы построить диаграмму необходимо выделить ячейки, которые содержат данные (и кото­рые должны быть отражены на диаграмме), нажать кнопку Мастер диа­грамм и следовать инструкциям Мастера. Для создания диаграмм из не­смежных диапазонов нужно выделить первую группу ячеек, содержащих не­обходимые данные, далее, удерживая клавишу CTRL, выделить необходимые дополнительные группы ячеек и нажать кнопку Мастер диаграмм. При этом можно выбирать: тип диаграммы (Мастер диаграмм – на вкладках Стан­дартные или Нестандартные выбирается необходимый тип); изменять за­головки и другие тексты диаграммы - имена рядов данных или текст ле­генды, изменять подписи значений; отображать ряды данных на вспомога­тельной оси; изменять цвет, узор, линии и рамки на диаграмме и др.

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

· список обязательно должен содержать строку заголовков;

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

· в списке не должно быть пустых строк;

· рекомендуется помещать список на отдельный лист.

Для работы со списками в Excel имеются следующие средства:

· пополнение списка с помощью формы;

· сортировка списка;

· фильтрация списка;

· подведение промежуточных итогов;

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

При вводе данные можно непосредственно добавлять в ячейки списка, а можно воспользоваться специальной формой ввода (команда Форма из пункта меню Данные). В окне диалога выводятся значения записей. Окно содержит кнопки управления: Добавить, Удалить, Вернуть, Назад, Далее, Критерии, Закрыть. Для добавления новых записей необходимо воспользо­ваться кнопкой Добавить (появится пустая форма) и ввести значения в ка­ждое поле. Для редактирование записей с помощью формы необходимо найти нужную запись с помощью кнопок Назад и Далее (или клавишами управления курсором на полосе прокрутки формы) и ввести нужную правку. Форму ввода можно использовать не только для ввода данных. Она позво­ляет просматривать существующие записи, редактировать их, удалять и вы­борочно отображать данные по определенному критерию.

В MS Excel записи, организованные в списки, можно сортировать (по алфавиту, по убыванию, по возрастанию). Сортировка выполняется по соот­ветствующим полям (ключам). Чтобы отсортировать список необходимо:

· поместить курсор в тот столбец таблицы, по которому нужно выполнить сортировку;

· затем щелкнуть по кнопке Сортировать по возрастанию или Сортировать по убыванию.

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

· выбрать команду Сортировка из пункта меню Данные;

· в появившемся окне «Сортировка диапазона» в поле Сортировать по вы­брать название столбца и указать порядок сортировки;

· затем сделать то же самое для второго и третьего поля;

· нажать кнопку ОК.

Упорядочивание текстовых строк чаще всего выполняют по алфавиту. Но это не всегда удобно. Для того чтобы установить особый порядок сорти­ровки, например по годам или месяцам, необходимо выбрать команду Сор­тировка из пункта меню Данные. Затем следует нажать кнопку Параметры. В открывшемся диалоговом окне Параметры сортировки из раскрывающе­гося списка «Сортировка по первому ключу» выбрать полные названия меся­цев. Теперь при сортировке по полю «Год» и/или «Месяц» названия месяцев будут располагаться в привычном порядке.

Для поиска и отбора записей (обработки их по заданному значению или критерию) в MS Excel применяется метод, который называется Фильтрация. Для фильтрации данных применяются команды Автофильтр (для простых критериев) и Расширенный фильтр (для сложных критериев).

Перед тем как использовать Автофильтр, следует выделить любую ячейку списка. Затем подать из пункта меню Данные команды - Фильтр – Автофильтр. При включении Автофильтра возле имен полей списка появ­ляются кнопки со стрелками. При щелчке по любой из этих кнопок раскры­вается меню, при помощи которого можно отобрать все записи с заданным значением поля. Иногда стандартных условий Автофильтра оказывается не­достаточно, поэтому создают собственный Автофильтр. Для этого необхо­димо выполнить следующие действия:

· в списке выделить любую ячейку;

· для включения автофильтра подать из пункта меню Данные команды - Фильтр - Автофильтр (в каждом столбце в верхней ячейке появляется кнопка раскрывающегося списка);

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

· в списке операторов сравнения выбрать нужный, а в списке значений вы­брать значение поля-ключа;

· задать критерий выборки во втором поле таблицы;

· щелкнуть по кнопке ОК (на листе будут отображены записи, отвечаю­щие заданным критериям).

Часто для отбора нужной информации из списка бывает вполне доста­точно Автофильтра или пользовательского фильтра. Однако для решения сложной задачи приходится прибегать к помощи расширенной фильтрации. Расширенный фильтр гораздо гибче Автофильтра. С помощью расширенного фильтра можно:

· определить более сложный критерий фильтрации;

· помещать результат отбора данных в любое место рабочей книги;

· устанавливать вычисляемый критерий отбора.

Чтобы воспользоваться расширенным фильтром, необходимо задать диапазон критериев (область рабочего листа, в которой формируется усло­вия отбора). Диапазон критериев должен состоять, по крайней мере, из двух строк, первая из которых содержит все или некоторые названия полей списка (названия полей, не используемых при фильтрации, можно не помещать в диапазон критериев, но если предполагается, что в дальнейшем может по­надобиться и другая информация из списка, надо скопировать строку, со­держащую названия полей списка, целиком). Условия отбора следует вносить в пустые ячейки диапазона критериев (условия, расположенные в ячейках од­ной строки, соединяются оператором И, а условия, расположенные на раз­ных строках, соединяются оператором ИЛИ). Диапазон критериев может состоять из любого количества строк. Область ячеек, содержащих критерии, должна отделяться от списка, по крайней мере, одной пустой строкой. При использовании вычисляемого критерия отбор производится «по несущест­вующему полю». При создании формул вычисляемых критериев следует ссылаться на первую строку списка, а не на строку заголовков. Если в фор­мулу будут подставляться значения вне списка, необходимо использовать аб­солютные ссылки. Для того чтобы отключить расширенный фильтр, исполь­зуют команды Фильтр - Отобразить все из пункта меню Данные.

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

· отсортировать список (сортировку следует проводить по тому полю спи­ска, при изменении значений которого должны рассчитываться проме­жуточные итоги);

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

· выбрать команду Итоги из пункта меню Данные;

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

В раскрывающемся списке При каждом изменении в... диалогового окна «Промежуточные итоги» имеются названия всех столбцов таблицы и следует выбрать заголовок того поля, по которому проводилась сортировка. В раскрывающемся списке Операция на выбор даются 11 функций, которые могут быть использованы для расчета итогов. В окне Добавить итоги по «галочкой» следует отметить названия полей списка, для которых надо под­вести итоги. При каждом повторном использовании команды Итоги можно выбирать новую функцию. Если необходимо, чтобы промежуточные итоги накапливались, то есть каждый последующий уровень итогов не уничтожал предыдущий, опция Заменить текущие итоги должна быть отключена.

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

В MS Excel имеется возможность построения динамической таблицы итоговых данных (извлеченных или рассчитанных на основе информации, со­держащейся в списках), так называемой Сводной таблицы. Для ее создания следует установить курсор в одну из ячеек списка и выполнить команду Сводная таблица из пункта меню Данные. На экране появится окно Мас­тера сводных таблиц и диаграмм. На первом шаге определяется источник данных. На втором – Мастер просит уточнить диапазон, в котором располо­жены исходные данные. На третьем шаге диалогового окна «Мастер сводных таблиц и диаграмм» следует щелкнуть по кнопке Макет. В открывшемся окне поля списка представлены в виде кнопок, и надо перетащить эти кнопки в соответствующие области макета сводной таблицы.

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