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

Действия с листами рабочей книги

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

Предлагаемое по умолчанию количество листов можно скорректировать командой Сервис – Параметры – Общие.

Листы можно добавлять и удалять. Чтобы добавить в рабочую книгу лист, можно выбрать команду Вставка – Лист. Чтобы удалить лист, нужно щелчком левой кнопки мыши по ярлычку лист сделать лист активным (ярлычок активного листа выводится на первый план), затем выбрать команду Правка – Удалить лист. Ярлыки рабочих листов расположены в порядке возрастания номеров. Листу можно присвоить имя, вызвав контекстное меню на ярлыке листа и выбрав команду Переименовать (не более 31 символа).

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

Электронная таблица состоит из столбцов и строк. Каждый столбец имеет уникальное имя, состоящее из букв латинского алфавита. Общее число столбцов на каждом рабочем листе равно 256. Имена столбцов отображаются в верхней части электронной таблицы прописными латинскими буквами. Имена строк отображаются слева от строки. Имена строк и столбцов нельзя изменить. Чтобы попасть в самую нижнюю слева ячейку листа, можно один раз или несколько раз (в зависимости от заполненности электронной таблицы) нажать одновременно две клавиши на клавиатуре: Ctrl и «стрелка вниз». Чтобы попасть в самую нижнюю справа ячейку листа, можно один раз или несколько раз нажать одновременно две клавиши: Ctrl и «стрелка вправо». Чтобы попасть в самую верхнюю слева ячейку листа А1, можно нажать комбинацию клавиш: Ctrk + Home.

С помощью MS Excel можно вводить и обрабатывать следующие данные.

Текст

Текстом является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например приведенные ниже записи интерпретируются как текст: 10АА109, 127AXY, 12-976, 208 4675. Введенный текст выравнивается в ячейке по левому краю.

Числа

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

Символы, которые рассматриваются как числа, зависят от установленных параметров в компоненте Языки стандарты Панели правления.

Перед рациональной дробью следует вводить 0 (нуль), чтобы избежать ее интерпретации как формата даты; например, вводите 0 1/2.

Перед отрицательным числом необходимо вводить знак минус (–) или заключать его в круглые скобки ().

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

Даты и время

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

• точка, например, 01.04.09 или 01.04.2009;

• дефис, например, 01-04-09 или 01-апр-2009;

• косая черта с наклоном вправо, например, 01/04/09 или 01/04/2009.

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

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

Для отображения времени суток в 12-часовом формате следуем вводить букву а или р, отделенную пробелом от значения времени, например 9:00 р. В противном случае время будет интерпретировано на основе 24-часового формата. С датами можно производить вычисления: складывать, вычитать, использовать в функциях. Правила составления формул и использования функций, оперирующих с числами в формате Дата, точно такие же, как при работе с обычными числами.

Часто при вычислениях приходится опираться на текущую дату. Чтобы не вводить ее каждый раз, рекомендуется использован, функцию СЕГОДНЯ. Особенность этой функции заключается в том, что у нее нет параметров. Эта функция выдает текущую дату по системному таймеру. Доступ к функции СЕГОДНЯ, а также к другим функциям из категории Дата и время.

Последовательность дат

Для создания ряда дат, в котором меняется день (например: 01.04.09, 02.04.09, 03.04.09), можно ввести с клавиатуры первый элемент ряда, в нашем примере это число 01.04.09, затем подвести мышь к маркеру заполнения и протащить его вниз на нужное число ячеек.

Для создания ряда дат, в котором меняется месяц или год (например: 01.04.09, 01.05.09, 01.06.099), можно ввести с клавиатуры один элемента ряда в разные ячейки, в нашем примере это числа 01.04.09 и 01.05.09. Затем выделить две ячейки с введенными датами, подвести мышь к маркеру заполнения и протащить его вниз на нужное число ячеек. Аналогичные действия нужно выполнить, чтобы создать ряд с датами, в которых изменяется год.

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

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

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

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

Адресация

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

Для задания адреса ячейки в Excel можно использовать два режима адресации ячеек: в формате R1C1 и в формате А1 (переключение с одного на другой осуществляется командой Сервис – Параметры – вкладка Общие – Стиль ссылок). Например, адреса ячеек в формате R1C1 выглядят так:

RC Текущая ячейка

RC[-1] Ячейка слева от текущей (относительная адресация)

R[1]C Ячейка под активной (относительная адресация)

R10C12 Ячейка в 10-й строке и в 12-м столбце (абсолютная адресация).

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

Для указания ссылки на ячейку вводится буква заголовка столбца и номер строки. Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой.

Адресация может быть абсолютной, относительной и смешанной.

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

Рис. 9.2. Пример записи формул

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

Если знак доллара стоит в таких вариантах А$4 или $А4, то адресация называется смешанной. То измерение, у которого стой знак доллара, при копировании данного адреса не изменяется, а то измерение, где нет доллара – настраивается на новое местоположение.

Заголовки и имена в формулах

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

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

Если данные не имеют заголовков или размещены на другом листе книги, можно создать имя, описывающее ячейку или группу ячеек. Использование имен может упростить понимание формулы. Например, формула «=СУММ(Продано_в_первом_квартале)» проще для понимания, чем формула «=СУММ(Продажа!С20:С30)». В этом примере имя «Продано_в_первом_квартале» представляет группу ячеек «С20:С30» на листе «Продажа».

Имена можно использовать в любом листе книге. Например, если имя «Контракты» ссылается на группу ячеек «А20:А30» в первом листе рабочей книге, то это имя можно применить в любом другом листе той же рабочей книги для ссылки на эту группу. Имя можно присвоить формуле или постоянному значению (константе). Например, имя «Процентная Ставка», которому присвоено значение 10%, можно использовать в любом месте для вычисления процентов. По умолчанию имена являются абсолютными ссылками.

Присваивание имени ячейкам рабочей книги

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

Как присвоить ячейке имя?

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

2. Укажите поле имени, которое расположено слева в строке формул.

3. Введите имя ячеек.

4. Нажмите клавишу Enter.

Или выберите команду Вставка – Имя – Присвоить.

Формулы и функции

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

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

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

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

Таблица 9.1

Арифметические операторы Excel

Арифметический оператор	Значение	Пример
+ (знак плюс)	Сложение	3+3
– (знак минус)	Вычитание	3–1
Унарный минус		–1
* (звездочка)	Умножение	3*3
/ (косая черта)	Деление	3/3
% (знак процента)	Процент	20%
^ («крышка»)	Возведение в степень	3^2

Операторы сравнения используются для обозначения операции сравнения двух чисел (табл. 9.2). Результатом выполнения операции сравнения является логическое значение Истина или Ложь.

Таблица 9.2

Операторы сравнения Excel

Оператор сравнения	Значение	Пример
= (равенства)	Равно	A1=B1
> (больше)	Больше	А1> В1
< (меньше)	Меньше	А1< В1
>= (больше и равенства)	Больше или равно	А1>=В1
<= (меньше и равенства)	Меньше или равно	А1<=В1
< > (больше и меньше)	Не равно	А1<>В1

Текстовый оператор «&» (амперсант) используется для обозначения операции объединения последовательностей символов в единую последовательность. Например, результатом выполнения выражения «Северный» & «ветер» будет: «Северный ветер».

Адресные операторы объединяют диапазоны ячеек для осуществления вычислений (табл. 9.3).

Таблица 9.3

Адресные операторы Excel

Адресный оператор	Значение	Пример
: (двоеточие)	Оператор диапазона, ссылающийся на ячейки между границами диапазона включительно	В5:В15
, (запятая)	Оператор объединения, который ссылается на объединение ячеек диапазонов	СУММ(В5:В15,D5:D15)
(пробел)	Оператор пересечения, который ссылается на общие ячейки диапазонов	СУММ(В5:В15 А7:D7) В этом примере ячейка В7 является общей для двух диапазонов

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

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

Для использования функций в формуле нужно указать ее имя и аргументы. Имена большинства записываются с использованием символов кириллицы, однако есть функции, названия которых не переведены на русский язык. Функции выполняют вычисления над аргументами в указанном порядке, называемом синтаксисом. Например, функция СУММ складывает значения группы ячеек (для этой часто используемой функции есть специальная кнопка на панели инструментов Автосумма , а функция ПЛТ вычисляет величину выплаты за один период годовой ренты на основе постоянных выплат и постоянной процентной ставки.

Список аргументов может состоять из чисел, текста, логических величин (например, ИСТИНА или ЛОЖЬ), массивов, значений ошибок (например, #Н/Д), функций или ссылок. Необходимо следить за соответствием типов аргументов. Аргументы перечисляются в круглых скобках после имени функции, отделяясь друг от друга точкой с запятой. Максимальное количество аргументов функции не должно превышать 30, а длина формулы не может быть более 1024 символов.

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

• выбрав значок в панели инструментов Стандартная или в строке ввода формул;

• нажать Shift+F3;

• Вставка – Функция.

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

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

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

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

После вставки функции в Панели формул отображается имя функции и ее аргументы, а также возвращаемое функцией и формулой значение (рис. 9.3).

Рис. 9.3. Панель формул

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

Для ввода формулы, содержащей функцию, нужно:

• указать ячейку, в которую следует ввести формулу, и нажать кнопку «=» – Изменить формулу;

• в списке поля Функции с помощью «стрелки вниз» выбрать подходящую функцию и ввести ее аргументы. Если функция отсутствует в списке, выберите команду Другие функции...

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

Формулы массива и их ввод

Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Формула массива обрабатывает несколько наборов значений, называемых аргументами массива. Каждый аргумент массива должен включать одинаковое число строк и столбцов. Формула массива создается так же, как и другие формулы, с той разницей, что для ввода такой формулы используются клавиши Ctrl+Shift+Enter.

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