Ирина Эланс

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

Microsoft Excel. 2

ПЛАН

Введение

Адресация

Стиль ссылок А1

Относительные, абсолютные, смешанные ссылки

Стиль трехмерных ссылок

Стиль ссылок R1C1

Адресация данных по имени

ИСПОЛЬЗОВАНИЕ ФОРМУЛ

Простые формулы

Формулы, содержащие ссылки на имена.

Формулы, содержащие функцию.

Вложенные функции

Мастер функций

Создание пользовательских функций

ЗАКЛЮЧЕНИЕ

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ.

ВВЕДЕНИЕ

Microsoft Excel – это мощное приложение для работы с электронными таблицами. Он содержит средства программирования на основе языка VBA (Visual Basic for Application). Мощные возможности вычисления и программирования на рабочем листе Excel связаны в первую очередь с понятием формулы и ее хранением в ячейках рабочего листа, а также с возможностью именования как ячеек, так и формул или отдельных значений, не находящихся непосредственно в ячейках таблицы.

АДРЕСАЦИЯ

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

По умолчанию Microsoft Excel использует стиль ссылок A1, определяющий столбцы буквами (от A до IV, всего не более 256 столбцов), а строки номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов. Например, верхняя левая ячейка таблицы имеет адрес А1. На рисунке 1 активная (текущая) ячейка имеет адрес В3.

Для ссылки на ячейку введите букву столбца, а следом номер строки. Например, ссылка B3 указывает на ячейку, расположенную на пересечении столбца B и строки 3.

Ячейка или диапазон	Использование
Ячейку в столбце A и строке 10	A10
Диапазон ячеек: столбец А, строки 10-20.	A10:A20
Диапазон ячеек: строка 15, столбцы B-E.	B15:E15
Все ячейки в строке 5.	5:5
Все ячейки в строках с 5 по 10.	5:10
Все ячейки в столбце H.	H:H
Все ячейки в столбцах с H по J.	H:J
Диапазон ячеек: столбцы А-E, строки 10-20.	A10:E20

Если нужно задать адрес ячейки, находящейся на другом рабочем листе или даже в другой рабочей книге, перед адресом ячейки указывается название этого рабочего листа (например, Лист3!B7) или рабочей книги ([Книга1]Лист1!$A$1). После названия рабочего листа всегда ставится восклицательный знак, а имя рабочей книги заключается в квадратные скобки.

Существует универсальный способ, позволяющий переходить прямо на заданную ячейку рабочего листа нужной рабочей книги: выполнить команду Правка -> Перейти (или нажать клавишу <F5>). Затем в поле Ссылка появившегося диалогового окна Переход набрать адрес требуемой ячейки и нажать кнопку ОК. В дальнейшем можно использовать ранее набранные адреса из списка Перейти к.

Относительные, абсолютные и смешанные ссылки.

Относительные ссылки. Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейку, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании относительной ссылки из ячейки B2 в ячейку B3, она автоматически изменяется с =A1 на =A2.

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

Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании смешанной ссылки из ячейки A1 в ячейку B1, она изменяется с =В$1 на =С$1, а из ячейки В2 в ячейку В3 – с =$A1 на =$A2.

Стиль трехмерных ссылок.

Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

Стиль ссылок, в котором нумеруются как строки, так и столбцы.

Стиль ссылок R1C1 полезен при вычислении положения столбцов и строк в макросах. В стиле ссылок R1C1 Microsoft Excel указывает положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за которой идет номер столбца.

Ссылка	Значение
R[-2]C	Относительная ссылка на ячейку, расположенную на две строки выше и в том же столбце
R[2]C[2]	Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
R2C2	Абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце
R[-1]	Относительная ссылка на строку, расположенную выше текущей ячейки
R	Абсолютная ссылка на текущую строку

При записи макроса Microsoft Excel записывает некоторые команды с использованием стиля ссылок R1C1. Например, если записывается такая команда как нажатие кнопки Автосумма для вставки формулы, суммирующей диапазон ячеек, Microsoft Excel использует при записи формулы стиль ссылок R1C1, а не A1.

Для задания стиля адресов R1C1 используется команда Сервис-> Параметры-> вкладка Общие.

Адресация данных по имени

Хотя номера ячеек лежат в основе всего, что делает Excel, намного проще иногда запоминать имена, например Идентификационный номер или Сумма, чем номера ячеек, например А1:А157. Поэтому в Excel существует техника использования именованных ячеек и диапазонов. Чтобы присвоить имя ячейке или диапазону, необходимо выделить их, ввести нужное имя в поле имени и нажать клавишу Еnter. Раскрывающийся список справа в поле имени позволяет находить именованные диапазоны и ячейки.

ИСПОЛЬЗОВАНИЕ ФОРМУЛ

Формулы представляют собой выражения, по которым выполняются вычисления на странице. Формула начинается со знака равенства (=). Ниже приведен пример формулы, умножающей 2 на 7 и прибавляющей к результату 5.

Формула также может включать следующие элементы:

Функции
Ссылки
Операторы
Константы.

Пример формулы	Описание
=128+345	Складывает 128 и 345
=5^2	Возводит 5 в квадрат

Простые формулы.

Простые формулы содержат операторы и константы.

Константа – постоянное (не вычисляемое) значение. Например, число 307 и текст «Сумма налога» являются константами. Выражение и результат вычисления константами не являются.

Оператор – это знак или символ, задающий тип вычисления в выражении. Существуют арифметические, текстовые, операторы сравнения и операторы ссылок.

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

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

Операторы сравнения - используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.

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

Текстовый оператор конкатенации. Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку.

Текстовый оператор	Значение (пример)
& (амперсанд)	Объединение последовательностей знаков в одну последовательность ("Северный"&"ветер")

Оператор ссылки - для описания ссылок на диапазоны ячеек используются следующие операторы.

Оператор ссылки	Значение (пример)
: (двоеточие)	Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (B5:B15)
; (точка с запятой)	Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(B5:B15;D5:D15))
(пробел)	Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8)

Для создания простой формулы необходимо:

Щелкнуть ячейку, в которую требуется ввести формулу.
Ввести = (знак равенства).
Ввести формулу.
Нажать клавишу ENTER.

Формулы, содержащие ссылки на имена.

Приведенные ниже формулы содержат относительные ссылки на другие ячейки и их имена . Ячейка, содержащая формулу, называется зависимой ячейкой, если ее значение зависит от значений в других ячейках. Например, ячейка B2 является зависимой, если она содержит формулу =C2.

Пример формулы	Описание
=C2	Использует значение в ячейке C2
=Лист2!B2	Использует значение в ячейке B2 на Лист2
=Ответственность-Актив	Вычитает ячейку Ответственность из ячейки Актив

Для создания формул, содержащих ссылки на имена:

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

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

Нажмите клавишу ENTER

Формулы, содержащие функцию.

Функции — заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные

вычисления. Например, функция ОКРУГЛ округляет число в ячейке A2 до трех знаков после запятой.

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

Имя функции. Для появления списка доступных функций щелкните ячейку и нажмите клавиши SHIFT+F3.

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

Всплывающая подсказка с синтаксисом и аргументами появляется после ввода функции. Например, всплывающая подсказка появится после ввода =СУММ( Всплывающие подсказки появляются только для встроенных функций.

Вложенные функции

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

Вложенная функция, используемая в качестве аргумента, должна вычислять соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение либо ИСТИНА, либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна давать логическое значение либо ИСТИНА, либо ЛОЖЬ. Иначе появится сообщение об ошибке «#ЗНАЧ!».

В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, функции И считается функцией второго уровня, потому что она является аргументами функции ЕСЛИ. Функция ИЛИ, вложенная в качестве аргумента в функцию И, будет функцией третьего уровня и так далее.

Мастер функций

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

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

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

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

Для отображения краткого описания функции и ее синтаксиса под полем Выберите функцию щелкните мышью ее имя.

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

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

Создание пользовательских функций

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

Предположим, имеется налоговая ставка 18%, которая используется в рабочей книге для различных вычислений. Вместо того чтобы вводить значение 18% во всех формулах, для которых необходима эта ставка, можно просто ввести слово Н, и Ехсеl автоматически подставит значение 18%, которому присвоено это имя.

Для этого выберите команду Вставка -> Имя -> Присвоить и в поле Имя введите Н. Теперь в любую ячейку можно ввести любую формулу, а вместо значения 18% использовать имя Н. Вероятно, одно из самых больших преимуществ этого метода заключается в том, что, когда налоговая ставка увеличивается или уменьшается и необходимо отразить в формулах новое значение, можно просто выбрать команду Вставка -> Имя -> Присвоить, а затем выбрать имя Н и изменить соответствующее значение.

ЗАКЛЮЧЕНИЕ

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

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

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

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ

Должеников В.А., Колесников Ю.В. Microsoft Excel 2000. – СПб.:БХВ – Санкт-Петербург, 1999. – 1088 с.:ил.

Холи Р., Холи Д. Excel. Трюки. – СПб.: Питер, 2005. – 287 с.: ил.

Справка: Microsoft Excel.