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

Лекция №1.

Введение. Основы работы в Excel. Типы ссылок и копировние данных. Относительные ссылки. Абсолютные ссылки. Форматирование и оформление данных.

Основы работы в Excel

Если пакет Microsoft Office установлен, то запустите Excel: Пуск►Программы►Microsoft Excel. Окно Excel, приведенное на рисунке 1, содержит много элементов, присущих большинству программ в среде Windows.

Рисунок 1 - Вид окна рабочей книги Excel

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

Строки электронной таблицы пронумерованы в порядке возрастания, а столбцы поименованы буквами латинского алфавита. Ячейки таблицы организованы в рабочие листы. На рисунке 1 выбран рабочий лист с именем Лист1. Рабочие листы можно переименовывать, добавлять и удалять. Для чего следует щелкнуть правой кнопкой по закладке листа.

Ячейки таблицы адресуются (именуются) по номеру столбца и строки (как в игре морской бой), например, А1, С7 и т.д. При ссылке на другой лист дополнительно следует указать имя листа, например, Лист2!В5 – это ссылка на ячейку В5 второго листа. При ссылке на другую рабочую книгу кроме листа указывается и имя книги, например, [Книга2]Лист1!А3 – это ссылка на ячейку А3 листа 1 книги 2.

Окно Excel (рисунок 1) содержит специфические поля и кнопки. На активном рабочем листе (в нашем случае это Лист1) одна ячейка является активной или, другими словами, выделенной. Эта ячейка обрамлена черной рамкой. Адрес этой ячейки В2 (иначе это называется ссылкой на выбранную ячейку) отражается в поле Имя. Рядом расположена Строка формул, помеченная знаком =, которая отображает содержащиеся в текущей ячейке формулы и данные. На рисунке 1 в строке формул отображается текст из активной ячейки В2.

Приведенный в строке формул знак равно является подсказкой-напоминанием пользователям ВСЕ ФОРМУЛЫ ЗАПИСЫВАЮТСЯ СО ЗНАКОМ = ВНАЧАЛЕ. Формулы могут содержать имена (адреса, ссылки) других ячеек. Результат вычисления будет показан в ячейке, а сама формула – в строке формул.

Типы ссылок и копирование данных

Различают относительные и абсолютные ссылки ячеек. Абсолютные ссылки записываются со знаком $ :

A1 - относительная ссылка

$A$1 - абсолютная ссылка

$A1 - смешанная ссылка: на столбец ссылка абсолютная, на строку - относительная

A$1 - смешанная ссылка: на строку ссылка абсолютная, на столбец - относительная

Функциональное различие типов ссылок проявляется лишь при копировании ячеек: относительные ссылки корректируются при копировании, а абсолютные – нет.

Относительные ссылки

Рассмотрим пример. В ячейках столбца С требуется найти сумму ячеек столбцов А и В. Для этого введите в ячейку С1 формулу, как показано на рисунке 2 и нажмите клавишу Enter.

Рисунок 2 – Ввод формулы с относительными ссылками

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

Для того, чтобы в ячейке С2 получить сумму ячеек А2 и В2 достаточно просто скопировать формулу из ячейки С1 в ячейку С2. Копирование можно выполнить двумя способами: через буфер обмена или при помощи маркера заполнения (в этом случае следует навести указатель мыши на правый нижний угол ячейки С1, так чтобы указатель мыши изменился на +). Поскольку в ячейке С1 формула с относительными адресами, то в ячейку С2 скопируется формула =А2+В2 (рисунок 3)

Рисунок 3 – Копирование относительной ссылки по столбцу вниз

На рисунке 3 показано, что относительные адреса при копировании корректируются. Для закрепления материала скопируйте формулу из ячейки С1 в ячейку D1 (рисунок 4).

Рисунок 4 – Копирование относительной ссылки по строке влево

Как видно из рисунка 4 в ячейке D1 при копировании получена формула =В1+С1, т.е. сумма двух соседних слева ячеек относительно активной ячейки D1.

Абсолютные ссылки

Теперь введите в ячейку С1 формулу с абсолютными адресами. Для автоматизации ввода знаков $ воспользуйтесь клавишей F4. Затем скопируйте формулу из ячейки С1 в ячейку С2 и далее в любую другую ячейку рабочего листа (рисунок 5)

Рисунок 5 – Абсолютная ссылка

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

Смешанные ссылки

Вернемся к расчетам, приведенным на рисунке 4. Для того, чтобы при копировании формулы, введенной в ячейку С1, вправо по строке в последующих ячейках появлялась сумма первых двух столбцов, необходимо в формуле использовать смешанные ссылки. Для этого следует ответить на вопрос: «Что не будет меняться при копировании: строка или столбец?» В нашем примере – столбец. Перед именем столбца поставьте знак $. Теперь формула примет вид =$A1+$B1. Затем скопируйте формулу из ячейки С1 в ячейку С2 а также в любую другую ячейку этой же строки (рисунок 6).

Рисунок 6 – Смешанная ссылка

Следует отметить, что рассмотренный пример (рисунок 6) носит учебный характер, демонстрируя поведение смешанных ссылок при копировании.

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

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

Рисунок 7 – Формат типов данных ячеек

В Excel существует понятие типа данных ячейки. Формат данных распознается автоматически при вводе данных. Например, если вы введете дату 15.02.2006, то по умолчанию установится формат Дата, если проценты 34%, то установится Процентный формат и т.д. Если формат не удается установить, то устанавливается Общий формат. Для большинства числовых форматов можно выбрать тип и число десятичных знаков дробной части. Здесь следует отметить, что дробные числа в вводятся с десятичной запятой, а не с точкой. Например, при вводе числа 1,25 вместо десятичной запятой была введена точка – 1.25. В этом случае Excel автоматически назначит ячейке формат Дата и отобразит введенную информацию как дату - Янв.25. И даже в случае исправления точки на запятую – Excel все равно будет отображать дату, но только Янв.00,что связано с внутренним представлением дат в Excel в виде чисел. Для того, чтобы избавиться от автоматически «распознанного» формата следует «вручную» назначить ячейке формат Числовой на вкладке Число диалогового окна Формат ячеек.

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

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

Рамки вокруг выделенной ячейки или диапазона ячеек создается с помощью кнопки Границы на панели инструментов Форматирование. Вкладка Граница диалогового окна Формат ячеек позволяет дополнительно задавать тип и цвет линий оформления (рисунок 10)

Рисунок 8 – Форматирование выравнивания ячеек

Рисунок 9 – Форматирование шрифтов ячейки

Вкладка Вид позволяет изменить цвет фона активной ячейки или диапазона ячеек.

На вкладке Защита можно запретить/разрешить изменение конкретных ячеек с помощью флага Защищаемая ячейка. После установки/снятия флагов защиты нужных ячеек следует выполнить защиту листа: пункт меню Сервис►Защита►Защитить лист… По умолчанию на листе все флажки Защищаемая ячейка включены, т.е. выполнение пункта меню Сервис►Защита►Защитить лист… приведет к защите всех ячеек.

Рисунок 10 – Форматирование типов границ