Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
конспект_лек_Ииформатика.doc
Скачиваний:
7
Добавлен:
01.07.2025
Размер:
6 Мб
Скачать
☆

3. Введение и форматирование данных в программе ms Excel.

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

Введение данных завершается нажатием клавиши <Enter>. Часто гораздо удобнее для завершения ввода использовать клавиши управления курсором. В этом случае после ввода текущая ячейка смещается в соответствующем направлении. Отменить ввод позволяют клавиша <Esc>.

Ввод чисел

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

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

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

Использованая перед числом, или в числе, одна запятая воспринимается как десятичная запятая.

При вводе числа можно использовать пробел как разделитель тысяч.

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

После числа можно отметить символ процента.

Можно вводить числа в виде рациональных дробей, разделяя всю и дробную части пропуском «3 ½». При отсутствии целой части вместо нее обязательно указывается 0.

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

Если число слишком длинное и не может быть отражено в ячейке целиком, вместо него отображаются специальные символы (###). Для корректного отображения числа увеличит ширину столбца. Числовые значения выравниваются в ячейке по правому полю.

Введение значений даты и времени

Непосредственно с клавиатуры можно вводить значения даты в интервале от 01.01.1900 к 31.12.2078. При этом для разделения отдельных частей значения даты рядом с точкой можно использовать косую черту (/) или дефис (-), выполнив установки в окне «Свойства– Язык и стандарты», вызываемом из «Панели управления» Windows.

В смысле времени часы, минуты и секунды разделяются двоеточием (:). Excel использует по умолчанию 24-часовой формат. При необходимости использования 12-часового формата, укажите после значения времени через символ пропуска am (или просто a) или pm (или просто p). Если в ячейку нужно записать и значения даты, и значение времени, разделите их символом пропуска.

Во внутреннем представлении Excel хранит и обрабатывает даты и время как десятичные числа. Значение дат хранятся в виде целых чисел из интервала от 1 до 65380, значение времени - в виде десятичных дробей из интервала от 0 до 1. Так, дате 01.01.1900 соответствует число 1. А, например, 6 часам (это 6/24 суток ) соответствует число 0,25. Благодаря этому значения дат и времени могут быть использованы в вычислениях.

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

Введение текста

Любые вводимые данные, которые Excel не воспринимает как числовые значения, значения даты, времени или формулы, интерпретируются как текст. Как текстовые воспринимаются также любые данные, перед которыми при введении указанный апостроф (').

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

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

Длинный текст можно разбить в ячейке на несколько строк, задавая нажатием клавиш <Alt + Enter> переход к новой строке.

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

Введение формул

Формула в Excel должна начинаться с математического оператора, например, со знака равенства (=), или знака плюс (+), или знака минус (-). Формулы кроме операций (часто употребляют термин - операторы) могут содержать константы, адреса ячеек, имена диапазонов, функции. Сразу после завершения ввода выполняются вычисления, и в ячейке отображается результат вычислений. Сама формула, сохраняется в активной ячейке, отображается в строке формул. Иногда после введения формулы появляется сообщение об ошибке, по которому, как правило, нетрудно определить ее характер. Возможности, предоставляемые формулами для обработки данных в Excel, рассмотрены ниже в разделе "Расчет".

Правка данных

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

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

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

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

Выделение области

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

Внутри выделенного диапазона можно перемещать указатель активной ячейки клавишами <Tab> и <Shift + Tab>. Нажатие любой другой клавиши управления курсором или щелчок мышью приведут к снятию выделения диапазона, при этом выбранная ячейка станет активной.

Выделение диапазона

Выделенный на рабочем листе диапазон всегда имеет прямоугольную форму. Наименьший диапазон состоит из одной ячейки. Активная ячейка автоматически считается выделенной.

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

Для выполнения этой же операции с помощью клавиатуры нужно поместить указатель активной ячейки в начале диапазона, выделяемого нажать клавишу <Shift> и, удерживая ее нажатой, клавишами управления курсором расширить выделенный диапазон. Альтернативой клавиши <Shift> является функциональная клавиша <F8>. После нажатия этой клавиши в строке состояния появляется индикатор ВДЛ, который свидетельствует о том, что режим выделения включен. Пользуясь клавишами управления курсором можно расширить выделенный диапазон. Повторное нажатие клавиши <F8> выключает режим выделения.

Кроме того, выделить диапазон можно, вписав его координаты (адреса левого верхнего и правого нижнего углов области, например, B5: Z125) в поле имен строки формул. Это удобно, когда выделяется область большого размера, координаты которой заранее известны.

Выделение несмежных диапазонов

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

Несовместимые диапазоны можно выделять с помощью мыши (как описано выше), если дополнительно нажата клавиша <Ctrl>.

Можно, выделив первый диапазон, нажать сочетание клавиш <Shift + F8>. При этом в строке состояния отображается значок ДОБ, что указывает на включение режима несмежных выделений. В этом режиме нужно последовательно выделить все диапазоны (используя любые из рассмотренных возможностей), после чего повторно нажать ту же комбинацию клавиш для выхода из режима выделения.

Выделение рабочего листа

Для выделения всего рабочего листа можно воспользоваться кнопкой, расположенной в левом верхнем углу таблицы, на пересечении строки, содержащей заголовки колонок и колонки, содержащей заголовки строк. Кроме того, для выполнения указанной действия можно воспользоваться также комбинацией клавиш <Ctrl + A>.

Создание собственных списков пользователя

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

Выполните команду «Сервис – Параметры» и в окне диалога «Параметры», перейдите на вкладку «Список».

 

В поле «Списки» выведено имена всех определенных списков. По умолчанию выделен элемент - «Новый список».

В поле «Элементы списка» введите отдельные элементы списка, используя для разделения элементов клавишу <Enter>.

  Нажмите кнопку «Добавить». Новый список будет добавлен в поле «Списки».

Элементы списка можно сначала ввести в ячейки, выделить соответствующий диапазон, а затем выполнить команду «Сервис – Параметры». Ссылка на выделенный диапазон будет указано в поле «Импорт списка из ячеек» на вкладке «Списки». Проверив ссылки на диапазон ячеек, нажмите кнопку «Импорт».

 

Разбивка и фиксирования окна

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

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

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

Установите указатель мыши на горизонтальный (или вертикальный) маркер разбивки. При этом форма указателя мыши изменится на два горизонтальных (вертикальных) штриха со стрелками.

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

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

Для разбивки окна можно воспользоваться командой «Окно – Разделить». При этом место разбивки определяется выделенной (активной) ячейкой (влево вверх от выделенной ячейки). Отменить разбивку можно командой «Окно – Снять разделение».

 

Каждое из окон-фрагментов, полученных в результате разбивки, имеет свои собственные линейки прокрутки. Теперь можно независимо просматривать информацию в различных окнах-фрагментах, сделать одновременно видимыми (расположенными рядом) данные, которые находятся в отдаленных частях таблицы. Однако помните, в окнах-фрагментах представлена та же таблица данных. Перемещаться между окнами-фрагментами можно с помощью мыши или клавиши <F6>. Границу между двумя окнами можно в любой момент сместить, используя указатель мыши и удерживая нажатой ее левую кнопку.

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

 

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

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

Для задания параметров форматирования в окне диалога можно выполнить команду «Формат – Ячейки ...», или выбрать из контекстного меню команду «Формат ячеек ...», или воспользоваться комбинацией клавиш <Ctrl + 1>.

 

Окно диалога «Формат ячеек» состоит из шести вкладок:

Вкладка «Число» позволяет выбрать один из встроенных или созданных пользователем форматов, а также создать новый числовой формат.

Вкладка «Выравнивание» позволяет определить горизонтальное и вертикальное выравнивание, ориентацию содержания ячейки, а также представить текст в нескольких строках.

Вкладка «Шрифт» позволяет выбрать вид и размер шрифта, а также задать другие параметры, определяющие, например, цвет, подчеркивание и некоторые другие эффекты.

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

Вкладка «Вид» позволяет выбрать узор и цвет, а также цвет заливки.

Вкладка «Защита» позволяет задать режим защиты ячейки и сокрытия формул.

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

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

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

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

На панели инструментов «Стандартная» расположена кнопка - «Формат по образцу», с помощью которой можно применить формат определенной ячейки в других ячеек и диапазонов. Для использования этой возможности:

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

Нажмите на кнопку - «Формат по образцу» . Курсор мыши примет форму кисти.

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

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

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

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

Числовые форматы

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

В числовом формате «Общий» Excel представляет числа как целые, десятичные или экспоненциальные. Экспоненциальная форма представления числа будет выбрана автоматически, если ширина ячейки недостаточна для записи двумя другими способами. В этом формате максимальное количество отображаемых десятичных знаков равно 10. Уже при вводе числа в ячейку с форматом «Общий», добавив, например, символ валюты или процента, можно задать для ячейки другой встроенный или числовой формат пользователя.

Для задания встроенного формата выделите необходимые ячейки и в окне диалога «Формат ячеек», что вызывается командой «Формат – Ячейки ...», перейдите на вкладку «Число». В списке «Числовые форматы» выберите категорию, а в правой части окна уточните некоторые дополнительные параметры формата, зависят от выбранной категории. Дополнительные параметры формата уточняют, например, количество десятичных знаков, формат отрицательных чисел, отображения символа валюты и распределителя групп разрядов (тысяч), вид и точность отображения рациональных дробей. С помощью пункта «Дополнительный» можно выбрать формат для отображения почтового индекса, номера телефона и т.д., использование которых можно рекомендовать в соответствующих полях баз данных. Выбрав нужный формат и уточнив параметры, в поле «Образец» можно увидеть отображение содержимого выделенной ячейки. Категория «Текстовый» позволяет задать форматирование числового значения в виде текста. При вводе можно задать форматирование числового значения в виде текста путем указания перед числом апострофа (').

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

Наряду с применением встроенных форматов можно создавать и применять форматы пользователя, которые имеют следующие возможности:

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

  2. Форматы пользователя могут быть удалены. Удалить встроенные форматы невозможно.

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

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

  5. Различные секции отделяются друг от друга точкой с запятой (;).

  6. В первой секции следует задать шаблон для отображения положительных значений, во второй - для отрицательных значений, а в третий - для нулевых значений.

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

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

  1. Если для форматирования чисел заданы только две секции, то Excel применяет первую секцию для форматирования положительных и нулевых значений, а вторую - для форматирования отрицательных значений.

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

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

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

  0 - используется для того, чтобы слева и справа от десятичного разделителя вывести не менее определенного количества цифр. Например, для формата 00,000 число 1,23 отобразится в виде 01,230, а число 123,4567 - в виде 123,457 (произошло округления, а целая часть выводится полностью).

# - Действует так же, как и шаблон 0, но незначительные нули не выводимы.

? - Действует так же, как и шаблон 0, но незначительные нули заменяются пробелами.

  , (Запятая) - определяет, сколько цифр будет выводиться слева и справа от десятичного разделителя.

% (Проценты) - при выводе число умножается на 100,справа добавляется знак %.

/ (Наклонная) - символ шаблона, предназначенный для обычной (рациональной) дроби.

пробел - разделитель разрядов. Может использоваться также для масштабирования и округления. Например, один пропуск в конце числа - округление до тысяч, два пробела в конце числа - округление до миллионов.

E- E + e- e + - используются в экспоненциальном (научном) формате, разделяя части шаблона для задания мантиссы и порядка.

$ - + () P. - Стандартные отображаемые символы, непосредственно вставляются в шаблон, тогда как другие символы нужно брать в кавычки ("") или ставить перед ними обратную наклонную (\).

\ (Обратная наклонная) - используется для ввода следующего непосредственно за ней символа, сама при этом не выводится.

_ (Подчеркивание) - используется для вывода пропуска.

"Текст" - кавычки используются для отображения любого текста, который стоит между ними.

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

@ - Текстовый шаблон. Если ячейка содержит текст, то он выводится в том месте формата, где расположен символ @. Например, если ячейка содержит текст Кредит, то в формате "Графа" @ "." В ней появляется информация "Графа Кредит.".

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

Для удаления формата пользователя воспользуйтесь вкладкой «Число» окна диалога «Формат ячеек» (команда - «Формат – Ячейки ...») и выберите из списка «Числовые форматы» элемент «(все форматы)». Из списка справа выберите код формата, необходимо удалить, и нажмите кнопку «Удалить».

Выравнивание и размеры ячейки

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

Как обычно, для выравнивания содержания ячеек их необходимо выделить. Далее можно воспользоваться вкладкой «Выравнивание» окна диалога «Формат ячеек» (команда - «Формат – Ячейки ...»), где и задать нужное выравнивание.

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

Поля «по горизонтали» и «по вертикали» позволяют задать любую комбинацию горизонтального и вертикального способов выравнивания текста в ячейке. Задание выравнивания «по ширине» приведет, при необходимости, к автоматической разбивке текста в середине ячейки на строки, с учетом установленной ширины ячейки, что имеет смысл, если текст ячейки не помещается в одной строке. Разбивка текста ячейки на строки можно задать, установив флажок параметра «переносить по словам». Задать переход к новой строке в середине ячейки можно и непосредственно при вводе текста: для перехода к новой строке надо нажать клавиши <Alt + Enter>. При вводе текста и его отображении в нескольких строках Excel автоматически изменит высоту строки.

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

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

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

Для изменения ширины столбца (высоты строки) с помощью окна диалога поместите указатель ячейки на ячейку, расположенную в столбце (строке), ширину (высоту) которой необходимо изменить. Можно выделить несколько столбиков (строк) и изменить их ширину (высоту) сразу. Для изменения ширины (высоты) всех колонок (строк) рабочего листа достаточно выделить весь рабочий лист. Воспользуйтесь командой «Формат – Столбец – Ширина ...» или, соответственно, «Формат – Строка – Высота ...» и введите вместо отраженного текущего значения нужный размер. Ширина колонки измеряется в символах и может принимать значение от 0 до 255. Высота строки измеряется в пунктах (примерно 1 / 28сантиметра) и может принимать значение от 0 до 409,5. Если указать значение ширины столбца (высоты строки) равно 0, то столбик (строка) будет скрыт. Команду, выводит окно диалога для изменения ширины столбца (высоты строки) можно выбрать из контекстного меню выделенного столбца (выделенной строки).

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

Кроме указанных выше способов сокрытия столбцов и строк путем выбора нулевой ширины и высоты можно, выделив нужные ячейки или строки, воспользоваться командами «Формат – Столбец – Скрыть» и «Формат – Строка – Скрыть». Эквивалентные клавиши <Ctrl + 0 »- для колонок и <Ctrl + 9» - для строк. Можно выбрать также соответствующие команды из контекстного меню выделенного столбца (строки). Отметим, что в рабочем листе со скрытыми столбиками или строками нумерация столбцов и строк не является сквозной. Чтобы снова отобразить на экране скрытые столбцы или строки, достаточно выделить диапазон ячеек (можно несколько столбцов или строк) таким образом, чтобы в него входили скрытые столбцы или строки и выполнить команды «Формат – Столбец – Отобразить» и «Формат – Строка – Отобразить» . Эквивалентные клавиши <Ctrl + Shift + 0 »- для колонок и <Ctrl + Shift + 9» - для строк. Если выделить весь рабочий лист и задать указанные команды отображения столбцов и строк, то на экране будут отображены все скрытые ранее столбики или строки.

Рамки и цветовое оформление

К любому диапазону ячеек рабочего листа можно добавить рамку и узор. Для этого, как обычно, выделите нужные ячейки, иначе добавление рамки или узора будет относиться только к активной ячейке и выполните команду «Формат – Ячейки ...». Для добавления рамки в окне диалога «Формат ячеек», перейдите на вкладку «Граница» и выберите позицию рамки, тип и цвет линии. Добавление рамки значительно легче распознать, если задано сокрытия линий сетки разграфки рабочего листа (параметр «сетка» на вкладке «Вид» окна диалога «Параметры», которое вызывается командой «Сервис – Параметры ...»).

Добавить рамку можно и с помощью кнопки - «Границы» панели инструментов «Форматирование», открыв список и выбрав нужную линию. Для добавления узора воспользуйтесь вкладкой «Вид» окна диалога «Формат ячеек», где выберите узор (поле «Узор») и цвет (поле «Цвет»), контролируя результат в поле «Образец». Для выбора цвета фона можно использовать также палитру цветов - кнопка - «Цвет заливки» панели инструментов «Форматирование», открыв список и выбрав нужный цвет.

Введение формулы

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

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

  • Вставьте как первый символ знак равенства.

  • Введите часть формулы до первой ссылки, как обычный текст.

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

  • После ввода остальной части формулы, завершите ввод нажатием клавиши <Enter>.

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

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

В формуле можно использовать имена ячеек и диапазонов ячеек. Список определенных в текущей рабочей книге имен можно открыть, щелкнув на кнопке со стрелкой в поле «Имя», которое расположено слева в строке формул. Можно также нажать клавишу <F5> и выбрать имя диапазона в окне диалога «Переход».

Типы адресации

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

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

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

Абсолютная ссылка задает абсолютные координаты ячейки в рабочем листе (относительно левого верхнего угла таблицы). Можно приказать Excel интерпретировать номера строки и (или) стобца как абсолютные путем указания символа доллара ($) перед именами строки и (или) столбца. Например, $ A $ 7. При перемещении или копировании формулы абсолютные ссылки на ячейку (или диапазон ячеек) изменены не будут, и на новом месте скопированая формула будет ссылаться на ту же самую ячейку (диапазон ячеек).

Вид адресации, используемой в ссылке для указания строки, не зависит от вида адресации, используемой для указания колонки. Если для строки и колонки используются различные способы адресации, получим смешанные ссылки. Например, A $ 7, $ A7. При копировании или перемещении формулы абсолютная часть ссылки (с символом $) не изменится, а относительная часть ссылки может измениться в соответствии с правилами изменения относительных ссылок (учитывая направление копирования или перемещения).

При задании ссылки методом указания можно изменить тип ссылки нажатием клавиши <F4>. Тип текущего ссылка будет циклически меняться при каждом нажатии клавиши <F4>.

Нажатие <F4>

Адрес

Ссылка

Однажды

$ A $ 7

Абсолютная ссылка

Два раза

A $ 7

Абсолютная ссылка на строку

Три раза

$ A7

Абсолютная ссылка на столбец

Четырежды

A7

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

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

Абсолютная ссылка может быть задано также путем введения символа $ непосредственно с клавиатуры. Символ $ можно ввести с клавиатуры и в режиме правки содержания ячейки.

Как уже отмечалось, абсолютные и смешанные ссылки можно задавать и для диапазонов ячеек.

По умолчанию Excel использует формат ссылки A1: столбцы рабочего листа обозначаются буквами, а строки - цифрами. Можно изменить формат, используемый, задавая столбики своими номерами. Для этого в окне диалога «Параметры», что вызывается командой «Сервис – Параметры ...», перейдите на вкладку «Общие» и в группе «Параметры» установите флажок «Стиль ссылок R1C1». При использовании этого формата, например, выражения R2C3 (R - строка, C - столбик) соответствует абсолютные ссылки $ B $ 3.

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

Например, R [2] C [3] - ссылка на ячейку, которая расположена на две строки ниже и на три колонки справа ячейки, в которой записана формула. Отрицательные значения задают ссылки на ячейку, расположенную на указанное количество строк (столбцов) выше (слева) ячейки, содержащей ссылку. Например, R [-2] C [-1] - ссылка на ячейку, которая расположена на две строчки выше и на один столбец слева ячейки, содержащей ссылку.

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

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

Для задания ссылки на ячейки другого рабочего листа текущей рабочей книги проще воспользоваться методом указания. Записав часть формулы до того места, в котором должно быть указано ссылки, выберите рабочий лист, щелкнув на его ярлыке, и выделите в листе нужны ячейку или диапазон ячеек. Как обычно, введение всей формулы надо закончить нажатием клавиши <Enter>. Excel сам подставит в необходимом виде ссылки на ячейки другого рабочего листа. В формуле перед ссылкой на ячейку будет отображено имя рабочего листа, после которого указан восклицательный знак (например, Лист2! $ D $ 5).

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

При переименовании рабочего листа его имя, является составной частью ссылки в формуле, автоматически меняется. Перемещение ячеек, влияющие на другие рабочие листы, приводит к автоматическому восстановлению имени листа в ссылке формулы. Удаление такого зависимого листа приведет к возникновению ошибки «# ССЫЛКА!».

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

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

Например, "C: \ EXCEL \ EXAMPLES \ [SKLAD.XLS] Продажи_2009 '! $ B $ 2. Если зависимая и исходная рабочие книги сохранены в одной папке, указание пути не обязательно. В случае переименования исходной рабочей книги необходимо открыть зависимую рабочую книгу. Только в этом случае внешняя ссылка будет автоматически восстановлено. Можно удалить внешнюю ссылку, заменив формулу или соответствующую часть формулы, содержащей внешнюю ссылку, результатом ее вычисления. При необходимости обновить существующие связи вручную можно воспользоваться окном диалога «Связи», что активизируется командой «Правка – Связи ...», в котором перечислены все связи текущей рабочей книги. Изменение связи может быть выполнена с помощью кнопок «Изменить ...» и диалогового окна «Изменить связи», что позволяет задать другой путь к документу, с которым установлена связь. Удаление зависимой рабочей книги приведет к возникновению ошибки «# ССЫЛКА!».

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

Существует еще одна возможность использования объемных (трехмерных) ссылок, которая позволяет обрабатывать за один раз несколько диапазонов различных рабочих листов. В объемной ссылке можно указать диапазон ячеек с одинаковым адресом нескольких смежных листов текущей рабочей книги. Задать объемные ссылки проще всего методом указания. Записав часть формулы до того места, где должно быть указано объемное ссылки, выделите нужные письма в рабочей книге. После этого выделите нужный диапазон в листе. Завершить ввод формулы надо, как обычно, нажатием клавиши <Enter>. В формуле перед ссылкой на диапазон ячеек будет представлено ссылки на диапазон выделенных рабочих писем, после которых указан восклицательный знак. Например, = СУММ (Лист1: Лист3! B5: F5). Можно задать объемные ссылки и введением с клавиатуры, но этот путь более трудоемкий и поэтому может привести к ошибкам. Объемные ссылки не могут быть указаны в формулах массива и при применении оператора пересечения диапазонов.