Электронное пособие является практическим руководством по электронным таблицам для студентов колледжа, в котором описаны основные приёмы и правила работы, представлены теоретические сведения об электронном редакторе Microsoft Office Excel. Материал для выполнения практических заданий, задания для самостоятельного выполнения и тестовые работы, предназначенные для самопроверки знаний по табличному процессору Excel.
Данное пособие адресовано для самостоятельного обучения студентов колледжа, слушателей компьютерных курсов, а так же может быть использовано для проведения занятий в классах информатики.
Содержание
Введение
Таблицы пришли на ПК едва не раньше, чем текст. Ничего удивительного – ведь «компьютер» создавался прежде всего как « вычислитель», а не как заместитель пишущей машинки. И именно редакторы электронных таблиц ходили в лидерах программного рынка на заре компьютерной эры. Пользователь электронных таблиц, в отличие от работающего в Word «текстовика», живет уже не в одном, а в двух измерениях, и расставлять цифры по клеточкам таблицы, связывая и преобразуя их с помощью хитрых формул, для него не менее увлекательно, чем игра в шахматы. Рабочая область электронной таблицы напоминает по своей структуре шахматную доску. Она состоит из строк и столбцов, имеющие свои имена.
С возрастом жизнь ставит перед нами все более сложные вычислительные задачи, для решения которых приходится совершенствовать свой математический арсенал. Попробуйте вспомнить хотя бы один день в своей жизни, когда вы обошлись без чисел и вычислений. Наверное, не получится, ведь ежедневно вы выполняете хотя бы одну из операций: рассчитываетесь в магазине, набираете номер телефона, пользуетесь кодовым замком и т.д. Все ли вычисления можно выполнить в уме? А если вы, ко всему прочему, выбрали профессию бухгалтера, экономиста или менеджера?
Числа и вычисления являются неотъемлемой частью нашей жизни, а жизнь всегда хочется облегчить. Если обычный калькулятор помогает в простых вычислениях, то для выполнения сложных профессиональных калькуляций, необходимо более совершенное средство. В этом случае выбирают программы обработки электронных таблиц, которые созданы, чтобы быстро и просто помочь решить любую, даже очень сложную задачу, избежать ошибок, облегчить работу и добиться удивительных результатов.
Это интересно
В 1979г. два студента – экономиста Боб Френкстон (Bob Frankston) и Дэн Бриклин (Dan Briclin) написали первую в истории программу электронных таблиц и назвали ее VisiCalk, сокращенное название от Visible Calkulator (наглядный калькулятор). Так что все программы электронных таблиц, включая и Excel, обязаны своим появлением Бобу и Дэну.
Табличные процессоры как средство обработки финансово-экономической и статистической информации
Табличные процессоры относятся к программным средствам, позволяющим автоматизировать табличные расчеты, создавать, редактировать и печатать ведомости.
При запуске программы Microsoft Excel появляется рабочая книга. Книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи. Листы можно переименовать, вставлять, удалять, перемещать или копировать в пределах одной книги или из одной книги в другую.
Талица представляет собой сложный объект, который состоит из элементарных объектов:
Строка.Заголовки строк представлены в виде целых чисел, начиная с 1.
Столбец. Заголовки столбцов задаются буквами латинского алфавита сначала от А до Z, затем от АА до АZ, от ВА до ВZ и т.д.
Ячейка. Адрес ячейки определяется ее местоположением в таблице и образуется из заголовков столбца и строки, на пересечении которых она находится. Сначала записывается заголовок столбца, а затем номер строки. Например: А3, В4, АВ45 и т.п.
Диапазон ячеек. Это группа смежных ячеек: строка или часть строки, столбец или его часть, а также несколько смежных ячеек, образующих прямоугольную область. Диапазон ячеек задается указанием адресов первой и последней его ячеек, разделенных двоеточием.
Вычислительные возможности Excel
Для решения ряда экономических и финансовых задач целесообразно использовать многочисленные возможности электронных таблиц.
Арифметические операторы используются для обозначения основных математических операций над числами. Результатом арифметической операции всегда является число. Операторы сравнения предназначены для обозначения операций сравнения двух чисел. Результат операции сравнения – логическое значение ИСТИНА или ЛОЖЬ.
Оператор
Значение
Пример
Арифметические операторы
Вычитание или унарный минус
Возведение в степень
Операторы сравнения
Текстовый оператор
Объединение последовательностей символов в одну последовательность символов
= «Значение ячейки В2 равняется:» &В2
Ссылка на все ячейки между границами диапазона включительно
Объединение (точка с запятой)
Ссылка на объединение ячеек диапазонов
Ссылка на общие ячейки диапазонов
Выполнять вычисления в программе Excel позволяют формулы. С помощью формул можно, например, складывать, умножать и сравнивать данные таблиц, т.е. формулами следует пользоваться, когда необходимо ввести в ячейку листа вычисляемое значение.
Словарь по теме «Электронная таблицаMSExcel»
Порядок изложения терминов в словаре соответствует порядку изучения темы «Электронная таблица MS Excel» в учебной программе
Параметры, используемые при характеристике диаграммы:
Диаграмма состоит из следующих объектов:
25. Сортировка данных– упорядочение данных по заданному признаку с целью удобства использования. 26.Фильтрация данных– отсеивание данных, в которых нет необходимости для принятия решений, снижающее уровень шума и повышающее достоверность и адекватность данных. 27. Формализация данных– приведение данных, поступающих из разных источников, к одинаковой форме, что позволяет сделать их сопоставимыми между собой. 28. Форма – это специальное средство для ввода данных, предоставляющее конечному пользователю возможность заполнения только тех полей базы данных, к которым у него есть право доступа.
Финансовые функции
Мастер функций позволяет использовать в работе большое количество специализированных финансовых функций.
Предположим, что необходимо произвести расчет амортизации оборудования. В этом случае следует в зоне поиска функции ввести слово «Амортизация».
Программа подберет все функции по амортизации (рис.1). После заполнения соответствующих полей функции будет произведен расчет амортизации оборудования.
Часто нужно произвести сложением чисел, удовлетворяющих какому-либо условию. В этом случае следует использовать функцию СУММЕСЛИ. Рассмотрим конкретный пример. Имеем таблицу зависимости комиссионных от стоимости имущества ( рис. 2). Нам необходимо подсчитать сумму комиссионных, если стоимость имущества превышает 75000 руб.
Действия в этом случае таковы. Устанавливается курсор в ячейки В6, кнопкой . запускается Мастер функций, в категории Математические выбирается функция СУММЕСЛИ, задаются параметры. В качестве диапазона суммирования – В2:В5, (комиссионные), при этом условие имеет вид (> 75000). Результат нашего расчета составит 27000 руб.
Функция
Описание
Возвращает накопленный процент по ценным бумагам с периодической выплатой процентов.
Возвращает величину амортизации для каждого периода, используя коэффициент амортизации.
Возвращает величину амортизации для каждого периода.
Возвращает общую выплату, произведенную между двумя периодическими выплатами.
Возвращает общую выплату по займу между двумя периодами.
Возвращает величину амортизации актива для заданного периода, рассчитанную методом фиксированного уменьшения остатка.
Возвращает величину амортизации актива за данный период, используя метод двойного уменьшения остатка или иной явно указанной метод.
Обозначение ячейки, составленное из номера столбца и номера строки (А5, В7 и т. д.) называется относительным адресом или просто адресом. При некоторых операциях копирования, удаления, вставки Excel автоматически изменяет этот адрес в формулах. Относительная адресация ячеек используется в формулах чаще всего – по умолчанию.
При копировании ячейки, содержащей формулу, в ячейку, отстоящую от исходной на некоторое число столбцов и строк, адреса ячеек в формуле изменяются на такое же число столбцов и строк.
Пример 1: Исходная ячейка B2 с формулой =С2+1. При копировании ячейки формула изменится следующим образом:
Таким образом, вместо того, чтобы набирать формулы заново, достаточно их скопировать.
Абсолютный адрес
Изменение адресов при копировании или перемещении формул
При перемещении или копировании формулы абсолютные ссылки (адреса) не изменяются, а относительные автоматически обновляются в зависимости от нового положения формулы. Изменение типа ссылки (относительный и абсолютный) для редактируемого адреса производится с помощью клавишиF4.
Пример 2: Исходная ячейка B2 с формулой =$C$2+$C3+C$4. При копировании ячейки формула изменится следующим образом:
На данные, расположенные в соседних ячейках, можно ссылаться в формулах, как на единое целое. Такую группу ячеек называют диапазоном. Чтобы сослаться на диапазон ячеек (например, на группу смежных ячеек в строке), можно указать через двоеточие адреса начальной (первой) и конечной (последней) ячейки в диапазоне. Например, обозначение А7:Е7 адресует ячейки А, В, С, D, Е в строке 7 (т. е. весь диапазон находится в одной строке), обозначение В3:В6 адресует ячейки 3, 4, 5, 6 в столбце В (т. е. весь диапазон находится в одном столбце) и т. п.
Чтобы ввести ссылку на всю строку или столбец, нужно набрать номер строки или букву столбца дважды и разделить их двоеточием, например, А:А, 2:2.
Для обозначения адреса ячейки с указанием листа используются имя листа и восклицательный знак, например, Лист2!В5, Итоги! В5.
Для обозначения адреса ячейки с указанием книги используются квадратные скобки, например, [Книга1]Лист2!А1.
Диапазоном можно обозначить и прямоугольный блок ячеек. Ссылки (адрес) на блок ячеек состоит из адреса ячейки, находящейся в левом верхнем углу прямоугольного блока ячеек, двоеточия и адреса ячейки, находящейся в правом нижнем углу этого блока. Например, B2:D5 адресует блок, в котором адрес левой верхней ячейки равен B2 и адрес правой нижней ячейки – D5 (см. рис).
ВExcelпредусмотрен очень удобный способ ссылки на ячейку с помощью присвоения этой ячейке произвольного собственного имени. Имена используют в формулах вместо адресов. Имена ячеек в формулах представляют собой абсолютные ссылки. Имена присваиваются ячейкам или диапазонам ячеек для придания наглядности вычислениям в таблице и удобства работы, например, собственными именами можно обозначать постоянные величины, коэффициенты, константы, которые используются при выполнении вычислений в электронной таблице.
Типы и формат данных
В работе с электронными таблицами можно выделить три основных типа данных: текст, число и формула. Для эффективной работы с электронными таблицами необходимо четко определить назначение каждого типа и допустимый набор символов.
Табличный процессор должен «знать», какого типа данные хранятся в конкретной ячейке таблицы, для того, чтобы правильно интерпретировать ее содержимое. Тип входных данных, содержащихся в каждой ячейке, определяется первым символом, который должен трактоваться не только как часть данных, но и как команда переключения режима.
Первые два типа данных (число и текст) считаются константами (постоянными величинами), то есть представляют собой информацию, которая не изменяется без вашего участия. Введя в ячейку электронной таблицы числа (например, 6; 7890; 3,8) или текст (например, «Налоги», «Начисления»), вы как бы «впечатываете» свои данные в ячейку и видите их (или хотя бы часть этих данных) на экране.
В чем же заключается отличие числа от текста? Есть ли необходимость разделять эти типы данных? Оказывается, есть. Число, в отличие от текста, может участвовать в вычислительных операциях. Так, например, можно число 6 умножить на число 3 и получить результат вычислений. Однако если вы попробуете из текста «Начисления» вычесть текст «Налоги», то получите сообщение об ошибке.
Число
Число в Excel может состоять только из следующих символов: цифры от 0 до 97 8 9), знаки плюс (+) и минус (-), круглые скобки ( ), запятая (,) (используется в качестве десятичного разделителя), косая черта (/) (в рациональных дробях, например ½), знак процента (%), точка (.), знак мантиссы (Е е). Все другие комбинации, состоящий из цифр и нецифровых символов, интерпретируются как текст.
Первыми символами в ячейке с числовыми данными могут быть: число, знак числа (плюс или минус) и левая круглая скобка. Знак плюс, стоящий перед числом, игнорируется. Перед отрицательным числом необходимо ввести знак минус или заключить его в круглые скобки. В качестве десятичного разделителя в России используют запятую.
По умолчанию числа выравниваются в ячейке по правому краю. Это объясняется тем, что при размещении чисел друг под другом (в столбце таблицы) удобно иметь выравнивание по разрядам (единицы под единицами, десятки под десятками и т. д.). При выравнивании чисел в ячейке незначащие («хвостовые») нули в дробной части числа отбрасываются. Например, число 25,10 превратится в 25,1.
Большие и малые (по модулю) числа удобно записывать в экспоненциальной форме, т. е. в виде условного сочетания мантиссы и порядка. Например, число 4890000 можно представить как произведение 4,89х106 (4,89 – мантисса, 6 – порядок) и записать так: 4,89Е+06.
В Excel установлено ограничение для используемых числовых данных – 15 разрядов, т. е. числа хранятся с точностью до 15 знака независимо от количества разрядов, отображаемых на экране. Если число имеет больше 15 знаков, то разряды после 15-го преобразуются в нули.
Текст
Текстом в Excel является любая последовательность, состоящая из цифр, пробелов и нецифровых символов (буквы и специальные символы) – например, следующие записи (987$$78, 100 рублей) обрабатываются как текст. В качестве первого символа для обозначения текстовых данных иногда используется апостроф (`), кавычки (“) или пробел. Текст (текстовые данные, символьные данные) имеет описательный характер и не подвергается каким-либо преобразованиям. Общая длина вводимого в ячейку текста не может превышать 32767 символов. При необходимости можно расположить текст в ячейке в несколько строк. По умолчанию текст выравнивается в ячейке по левому краю. Это объясняется традиционным способом письма (слева направо).
Вводя в ячейку текст, в ней отображается этот текст или его часть, если текст целиком не помещается в ячейке. Если вводимый в ячейку текст превысит по длине видимую ширину столбца, возможны два случая:
Если число не помещается в ячейке электронной таблицы, то в отличие от текста вместо числа в ячейке появляются символы ####. Это обусловлено тем, что частичное представление чисел в ячейках может привести к ошибкам в вычислениях. Для полного отображения числовых данных необходимо изменить формат числа или увеличить ширину столбца.
Формулы
Формулами в электронной таблице называют арифметические и логические выражения. Формулы в Excel всегда начинаются со знака равенства (=). После знака равенства в формулу вводят необходимые элементы (операнды) либо непосредственно (в виде чисел), либо в виде ссылок на ячейки и соединяют их операторами, определяющими, какие действия следует выполнить над этими элементами. Формула возвращается на экран в виде вычисленного значения, т. е. в ячейке отображается не сама формула, а результат вычислений по этой формуле, а сама формула отображается в строке (поле) ввода и редактирования.
Например, ввод формулы =А1+А2 означает, что нужно сложить содержимое ячеек А1 и А2 и вывести результат в активной ячейке. По умолчанию Excel вычисляет формулы при их вводе и автоматически пересчитывает формулы при каждом изменении входящих в них исходных данных.
Правило использования формул в программе Excel состоит в том, что, если значение ячейки действительно зависит от других ячеек таблицы, всегда следует использовать формулу, даже если операцию легко можно выполнить в «уме». Это гарантирует, что последующее редактирование таблицы не нарушит ее целостности и правильности производимых в ней вычислений.
Формулы могут включать константы (чаще всего постоянные числовые значения), ссылки на ячейки, операторы (знаки арифметических, логических и других операций), функции, скобки.
Формула может содержать ссылки, то есть адреса ячеек, содержимое которых используется в вычислениях. Это означает, что результат вычисления формулы зависит от числа, находящегося в другой ячейке. Ячейка, содержащая формулу, таким образом, является зависимой. Значение, отображаемое в ячейке с формулой, пересчитывается при изменении значения ячейки, на которую указывает ссылка. При выполнении вычислений на место ссылки вставляется значение, находящееся в ячейке, на которую указывает ссылка. Адреса ячеек вводят в формулы по латинскому регистру, а знаки арифметических операций (сложения, вычитания, умножения и деления) – с цифровой клавиатуры.
Для выполнения вычислений с помощью формул и функций в Excel имеются четыре вида операторов:
· арифметические операторы (сложение +, вычитание —, умножение *, деление /, процент %, возведение в степень ^) используются для выполнения арифметических операций над операндами. Например, =А1+В3, =400-С4, =А3*G13, =D3/G7, =10%, =C6^2;
· операторы ссылок (диапазон : (используется для формирования ссылок на диапазоны ячеек) и объединение ; (объединяет несколько ссылок в одну ссылку)) используются для формирования ссылок на диапазоны ячеек или на несколько отдельных ячеек. Например, =СУММ(В5:В10), =СУММ(В5;С6;D7);
· текстовый оператор конкатенации &(амперсанд) применяется для объединения нескольких последовательностей символов в одну строку, т. е. для объединения содержимого ячеек в одну текстовую строку. Например, =А1&В5.
Структура или порядок элементов в формуле определяет конечный результат вычислений. При использовании в формулах арифметических операторов необходимо соблюдать принятый в математике порядок арифметических операций. Сначала производится возведение в степень, затем – умножение и деление, в последнюю очередь – сложение и вычитание. Скобки позволяют изменять стандартный порядок выполнения действий: операции в скобках выполняются первыми. Если два оператора имеют одинаковый приоритет, то вычисление производится слева направо.
Формулы могут включать функции. В общем случае функция – это переменная величина, значение которой зависит от значений других величин (аргументов). Функции представляют собой программы с уникальными именами. Это заранее определенные формулы, для которых пользователь должен задать конкретные значения аргументов. Все функции имеют одинаковый формат записи и включают имя функции и перечень аргументов (список параметров). Аргументы записываются в круглых скобках после имени функции. Скобки – обязательная принадлежность функции, даже если у нее нет аргументов. Если аргументов несколько, то один аргумент от другого отделяется точкой с запятой.
В качестве аргументов функции могут использоваться числа (константы), адреса ячеек или диапазонов, имена ячеек или диапазонов, текст, другие функции, логические значения и др.
Общее количество символов в формуле не может быть больше 1024. Строка аргумента может содержать не более 255 символов.
Функции, в которых в качестве аргумента используется другая функция, называются вложенными. В одной формуле разрешается использовать вложение функций до седьмого уровня.
Excelсодержит более 400 встроенных функций, условно разделенных на несколько категорий: Математические, Статистические, Финансовые, Логические, Текстовые и др. В русифицированных версияхExcelимена многих функций записываются на русском языке. Имена функций лучше набирать строчными буквами. После завершения ввода имен правильно введенных функций строчные буквы автоматически преобразуются в прописные, что служит критерием правильности ввода функции.
Математические функции выполняют различные математические операции, например, суммирование чисел (СУММ), вычисление логарифмов (LOG, LN), тригонометрических функций (SIN, COS), преобразование радиан в градусы (ГРАДУСЫ) и т. п.
Статистические функции выполняют операции по вычислению параметров случайных величин или их распределений, представленных множеством чисел, например, среднего значения (СРЗНАЧ), стандартного отклонения (СТАНДОТКЛОН) и т. п.
Финансовые функции используются в сложных финансовых расчетах, например, определение амортизационных отчислений, определение размера ежемесячных выплат для погашения кредита и т. п.
Идея создания электронной таблицы возникла у студента Гарвардского университета (США) Дэна Бриклина (Dan Bricklin) в 1979 г. Выполняя скучные вычисления экономического характера с помощью бухгалтерской книги, он и его друг Боб Франкстон (Bob Frankston), который разбирался в программировании, разработали первую программу электронной таблицы, названную ими VisiCalc.
VisiCalc скоро стала одной из наиболее успешных программ. Первоначально она предназначалась для компьютеров типа Apple II, но потом была трансформирована для всех типов компьютеров. Многие считают, что резкое повышение продаж компьютеров типа Apple в то время и было связано с возможностью использования на них табличного процессора VisiCalc. В скоропоявившихся электронных таблицах-аналогах (например, SuperCalc) основные идеи VisiCalc были многократно усовершенствованы.
Разработчикам Excel удалось найти золотую середину, максимально облегчив пользователю освоение программы и работу с ней. Благодаря этому Excel быстро завоевала популярность среди широкого круга пользователей. В настоящее время, несмотря на выпуск компанией Lotus новой версии электронной таблицы, в которой использована трехмерная таблица с улучшенными возможностями, Excel занимает ведущее место на рынке табличных процессоров.
Имеющиеся сегодня на рынке табличные процессоры способны работать в широком круге экономических приложений и могут удовлетворить практически любого пользователя.
ИНТЕРФЕЙС ТАБЛИЧНОГО ПРОЦЕССОРА
Что такое электронная таблица
Строки, столбцы, ячейки и их адреса
Максимальное количество строк и столбцов определяется особенностями используемой программы и объемом памяти компьютера, Современные программы дают возможность создавать электронные таблицы, содержащие более 1 млн. ячеек, хотя для практических целей в большинстве случаев этого не требуется.
Пересечение строки и столбца образует ячейку таблицы, имеющую свой уникальный адрес. Для указания адресов ячеек в формулах используются ссылки (например, А2 или С4).
Указание блока ячеек
Адрес ячейки, образованной на пересечении столбца G и строки 3, будет выражаться ссылкой G3.
Адрес блока, образованного в виде части строки 1, будет А1..Н1.
Адрес блока, образованный в виде столбца B, будет В1..В10.
Адрес блока, образованный в виде прямоугольника, будет D4..F5.
Каждая команда электронной таблицы требует указания блока (диапазона) ячеек, в отношении которых она должна быть выполнена.
Блок используемых ячеек может быть указан двумя путями: либо непосредственным набором с клавиатуры начального и конечного адресов ячеек, формирующих диапазон, либо выделением соответствующей части таблицы при помощи клавиш управления курсором. Удобнее задавать диапазон выделением ячеек.
Типичными установками, принимаемыми по умолчанию на уровне всех ячеек таблицы, являются: ширина ячейки в 9 разрядов, левое выравнивание для символьных данных и основной формат для цифровых данных с выравниванием вправо.
Типовая структура интерфейса
Как видно на рис. 14.1, при работе с электронной таблицей на экран выводятся рабочее поле таблицы и панель управления. Панель управления обычно включает: Главное меню, вспомогательную область управления, строку ввода и строку подсказки. Расположение этих областей на экране может быть произвольным и зависит от особенностей конкретного табличного процессора.
Строка главного меню содержит имена меню основных режимов программы. Выбрав один из них, пользователь получает доступ к ниспадающему меню, содержащему перечень входящих в него команд. После выбора некоторых команд ниспадающего меню появляются дополнительные подменю.
Рис. 14.1. Вид электронной таблицы на экране
Вспомогательная область управления включает:
• вертикальную и горизонтальную линейки прокрутки.
Строка подсказки предназначена для выдачи сообщений пользователю относительно его возможных действий в данный момент.
Приведенная структура интерфейса является типичной для табличных процессоров, предназначенных для работы в среде Windows. Для табличных процессоров, работающих в DOS, чаще всего отсутствуют командные кнопки панелей инструментов и линейки прокрутки.
Текущая ячейка и экран
Текущей (активной) называется ячейка электронной таблицы, в которой в данный момент находится курсор. Адрес и содержимое текущей ячейки выводятся в строке ввода электронной таблицы. Перемещение курсора как по строке ввода, так и по экрану осуществляется при помощи клавиш движения курсора.
Возможности экрана монитора не позволяют показать всю электронную таблицу. Мы можем рассматривать различные части электронной таблицы, перемещаясь по ней при помощи клавиш управления курсором. При таком перемещении по таблице новые строки (столбцы) автоматически появляются на экране взамен тех, от которых мы уходим. Часть электронной таблицы, которую мы видим на экране монитора, называется текущим (активным) экраном.
Окно, рабочая книга, лист
Рабочая книга представляет собой документ, содержащий несколько листов, а которые могут входить таблицы, диаграммы или макросы. Вы можете создать книгу для совместного хранения в памяти интересующих вас листов и указать, какое количество листов она должна содержать. Все листы рабочей книги сохраняются в одном файле. Заметим, что, термин «рабочая книга» не является стандартным. Так, например, табличный процессор Framework вместо него использует понятие Frame (рамка).
ДАННЫЕ, ХРАНИМЫЕ В ЯЧЕЙКАХ ЭЛЕКТРОННОЙ ТАБЛИЦЫ
Типы входных данных
В каждую ячейку пользователь может ввести данные одного из следующих возможных видов: символьные, числовые, формулы и функции, а также даты.
Пример 14.2. Символьные данные:
Ведомость по начислению премии ‘Группа N142
Числовые данные не могут содержать алфавитных и специальных символов, поскольку с ними производятся математические операции. Единственными исключениями являются десятичная точка (запятая) и знак числа, стоящий перед ним.
Пример 14.3. Числовые данные:
Пример 14.4. Предположим, что в ячейке находится формула +В5 + ( С5 + 2 * Е5) / 4. В обычном режиме отображения таблицы на экране вы увидите не формулу, а результат вычислений по ней над числами, содержащимися в ячейках В5, С5 и Е5.
Функции. Функция представляет собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов функции, стоящих в скобках после ее имени. Функцию (так же, как и число) можно считать частным случаем формулы. Различают статистические, логические, финансовые и другие функции.
Пример 14.5. Ячейка содержит функцию вычисления среднего арифметического значения множества чисел, находящихся в ячейках В4, В5, В6, В8. в следующем виде:
Даты. Особым типом входных данных являются даты. Этот тип данных обеспечивает выполнение таких функций, как добавление к дате числа (пересчет даты вперед и назад) или вычисление разности двух дат (длительности периода). Даты имеют внутренний (например, дата может выражаться количеством дней от начала 1900 года или порядковым номером дня по Юлианскому календарю) и внешний формат. Внешний формат используется для ввода и отображения дат. Наиболее употребительны следующие типы внешних форматов дат:
Внимание! Тип входных данных, содержащихся в каждой ячейке, определяется первым символом, который должен трактоваться не как часть данных, а как команда переключения режима:
если в ячейке содержатся числа, то первый их символ является либо цифрой, либо десятичной точкой, либо знаком числа (плюсом или минусом);
если в ячейке содержится формула, то первый ее символ должен быть выбран определенным образом в соответствии со спецификой конкретного табличного процессора. Для этого часто используются левая круглая скобка, знак числа (плюс или минус), знак равенства и т.п.;
ячейка, содержащая функцию, всегда использует в качестве первого специальный символ @ ;
если ячейка содержит символьные данные, ее первым символом может быть одинарная (апостроф) или двойная кавычка, а также пробел.
Форматирование числовых данных в ячейках
Основной формат используется по умолчанию, обеспечивая запись числовых данных в ячейках в том же виде, как они вводятся или вычисляются.
Денежный формат обеспечивает такое представление чисел, где каждые три разряда разделены запятой. При этом пользователем может быть установлена определенная точность представления (с округлением до целого числа или в два десятичных знака). Например, введенное число 12345 будет записано в ячейке как 12,345 (с округлением до целого числа) и 12,345-00 (с точностью до двух десятичных знаков).
Научный формат, используемый для представления очень больших или очень маленьких чисел, обеспечивает представление вводимых чисел в виде двух компонентов:
— мантиссы, имеющей один десятичный разряд слева от десятичной точки, и некоторого (определяемого точностью, заданной пользователем) количества десятичных знаков справа от нее;
Форматирование символьных данных в ячейках
По умолчанию символьные данные выравниваются по левому краю ячейки. Вы можете изменить формат представления символьных данных в электронной таблице. Для этого существуют следующие возможности.
Выравнивание к левому краю ячейки располагает первый символ вводимых вами данных в крайней левой позиции ячейки. Для многих программ этот режим используется по умолчанию как основной.
Выравнивание к правому краю ячейки располагает последний символ вводимых в ячейку данных в ее крайней правой позиции.
Выравнивание по центру ячейки располагает вводимые данные по центру ячейки.
Изменение ширины колонки
Внимание! Если ширина вводимого числа превышает ширину ячейки (колонки), ячейка заполняется звездочками, сигнализирующими о том, что ширина ячейки недостаточна для отображения данных.
Вычисления в таблицах производятся с помощью формул. Результат вычисления помещается в ячейку, в которой находится формула.
Формула начинается со знака плюс или левой круглой скобки и представляет собой совокупность математических операторов, чисел, ссылок и функций.
При вычислениях с помощью формул соблюдается принятый в математике порядок выполнения арифметических операций.
Формулы состоят из операторов и операндов, расположенных в определенном порядке. В качестве операндов используются данные, а также ссылки отдельных ячеек или блоков ячеек. Операторы в формулах обозначают действия, производимые с операндами. В зависимости от используемых операторов различают арифметические (алгебраические) и логические формулы.
В арифметических формулах используются следующие операторы арифметических действий:
^ возведение в степень.
Арифметические формулы могут также содержать операторы сравнения: равно (=), не равно ( ), больше (>), меньше ( =). Результатом вычисления арифметической формулы является число.
Логические формулы могут содержать указанные операторы сравнения, а также специальные логические операторы:
Пример 14.7. Приведем несколько примеров вычисления арифметических и логических формул по следующим данным:
Содержимое ячейки В1 умножается на 3, и результат складывается с содержимым ячейки А1. (Умножение выполняется первым).
Содержимое ячейки В3 вычитается из содержимого ячейки А2, а затем к результату добавляется содержимое ячейки С2. (Сложение и вычитание как действия одного уровня выполняются слева направо).
Содержимое ячейки С1 умножается на содержимое А2, и затем содержимое ячейки В2 делится на полученный результат. (Любые действия в скобках выполняются первыми).
Поскольку содержимое ячеек А1 (3>0) и С3 (6>0) представляет собой положительные числа, всему выражению присваивается численная величина 1 (‘Истинно’).
По умолчанию электронная таблица вычисляет формулы при их вводе, пересчитывает их повторно при каждом изменении входящих в них исходных данных, формулы могут включать функции.
Функции
функции даты и времени и др.
Математические функции выполняют различные математические операции, например, вычисление логарифмов, тригонометрических функций, преобразование радиан в градусы и т. п.
Статистические функции выполняют операции по вычислению параметров случайных величин или их распределений, представленных множеством чисел, например, стандартного отклонения, среднего значения, медианы и т. п.
Текстовые функции выполняют операции над текстовыми строками или последовательностью символов, вычисляя длину строки, преобразовывая заглавные буквы в строчные и т.п.
Логические функции используются для построения логических выражений, результат которых зависит от истинности проверяемого условия.
Финансовые функции используются в сложных финансовых расчетах, например определение нормы дисконта, размера ежемесячных выплат для погашения кредита, определение амортизационных отчислений и др.
Все функции имеют одинаковый формат записи и включают имя функции и находящийся в круглых скобках перечень аргументов, разделенных запятыми. Приведем примеры наиболее часто встречающихся функций.
Относительная и абсолютная адресация
При копировании или перемещении формулы в другое место таблицы необходимо организовать управление формированием адресов исходных данных. Поэтому в электронной таблице при написании формул наряду с введенным ранее понятием ссылки используются понятия относительной и абсолютной ссылок.
Форма написания относительной ссылки совпадает с обычной записью.
Правило относительной ориентации клетки
Рассмотрим правило относительной ориентации клетки на примере.
Пример 14.11. Клетка со ссылкой С2 содержит формулу-шаблон сложения двух чисел, находящихся в ячейках A1 и В4. Эти ссылки являются относительными и отражают ситуацию взаимного расположения исходных данных в ячейках A1 и В4 и результата вычисления по формуле в ячейке С2. По правилу относительной ориентации клеток ссылки исходных данных воспринимаются системой не сами по себе, а так, как они расположены относительно клетки С2:
ссылка A1 указывает на клетку, которая смешена относительно клетки С2 на одну клетку вверх и на две клетки влево;
ссылка В4 указывает на клетку, которая смешена относительно клетки С2 на две клетки вниз и одну клетку влево.
Копирование формул
Другой особенностью электронных таблиц является возможность автоматического изменения ссылок при копировании и перемещении формул.
Копирование содержимого одной ячейки (блока ячеек) в другую (блок ячеек) производится для упрощения ввода однотипных данных и формул. При этом осуществляется автоматическая настройка относительных ссылок операндов. Для запрета автоматической настройки адресов используют абсолютные ссылки ячеек.
Исходная формула, подлежащая копированию или перемещению, воспринимается как некий шаблон, где указывается местоположение входных данных относительно местоположения клетки с формулой.
Автоматическое изменение ссылок происходит не только при копировании субъекта (т.е. формул, содержащих ссылки), но и при перемещении объекта (т.е. ячейки, на которую имеются ссыпки в других местах).
Перемещение формул
В электронной таблице часто перемещают данные из одной ячейки (диапазона ячеек) в другую заданную ячейку (блок ячеек). После перемещения данных исходная ячейка окажется пустой. Это главное отличие перемещения от процесса копирования, в котором копируемая ячейка сохраняет свои данные. Перемещение формул также связано с автоматической подстройкой входящих в нее адресов операндов. При перемещении формул, так же как при их копировании, действует правило относительной ориентации клеток. Поэтому после перемещения относительное расположение клеток, содержащих перемещенную формулу и исходные данные (заданные относительными адресами), сохраняется таким же, как в формуле-оригинале.
На рис. 14.3в мы видим перемещение содержимого ячейки A3 а ячейку С3, когда адрес переносимой ячейки входит в другую формулу. Это случай перемещения зависимых ячеек. Например, имеется дополнительная ячейка В1, содержимое которой зависит от содержимого перемещаемой ячейки A3. В данном случае содержимое перемещаемой ячейки не изменяется, но изменяется содержимое зависимой ячейки В1 (хотя она не перемещается). Автоматическая подстройка адресов и в данном случае отразит изменения в электронной таблице так, чтобы результат формулы, содержащейся в ячейке В1, не изменился.
Последний случай, не рассмотренный на рис. 14.3, связан с возможностью использования абсолютных адресов. Нетрудно заметить, что использование абсолютных адресов при выполнении команды перемещения не имеет смысла, поскольку над ними также выполняется автоматическая подстройка адресов для отражения изменений, происходимых в таблице.
В целом команда перемещения является непростой командой, и вам следует хорошо подумать, прежде чем перемещать формулы и функции, содержащие ссылки. При перемещении символьных данных никаких трудностей не возникает.
. 
