Адресация в Excel

ПЛАН

Введение

Адресация

Стиль ссылок А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)

 

 

 

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

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

 

 

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

 

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

 

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

 

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

1. Щелкните ячейку, в которую требуется ввести формулу.
2. В строку формул (панель в верхней части окна, которая используется для ввода или изменения значений или формул в ячейках) введите = (знак равенства).
3. Выполните одно из следующих действий.
• Чтобы создать ссылку, выделите ячейку, диапазон ячеек, место в другом листе или место в другой книге. Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.
• Чтобы создать ссылку на именованный диапазон, нажмите клавишу F3, выберите имя в поле Вставка имени и нажмите кнопку ОК.
4. Нажмите клавишу ENTER

 

 

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

 

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

 

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

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

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