Лекция 4. Редактор электронных таблиц MicrosoftExcel

1. Формулы

1. Вычисление формул

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

Формулы состоят из следующих элементов:

• операции (арифметические, отношения);

• ссылки на ячейки;

• функции приложения MicrosoftExcel(например, СУММ или СРЗНАЧ).

Обычно формулы в рабочем листе вычисляются сразу. Если изменить значение, записанное в ячейку, которая используется в какой-либо формуле, значение этой формулы будет тотчас же пересчитано. При этом формулы вычисляются в естественном порядке, т.е. если формула в ячейке D12 зависит от результата вычисления формулы в ячейкеD11, то сначала вычисляется значение ячейкиD11 и только затем – в ячейкеD12.

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

2. Ссылки на ячейки и диапазоны

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

• Относительная. Ссылка является полностью относительной. Когда формула копируется, то ссылка изменяется в соответствии с новым местоположением формулы. Например, если скопировать формулу в ячейку, находящуюся на три строки ниже, то и ссылка будет указывать на ячейку, находящуюся на три строки ниже исходной, т.е. если формула в ячейкеB1 содержит ссылку на ячейкуA1, и если мы скопируем эту формулу в ячейкуB4, то ссылка будет указывать на ячейкуA4. Относительные ссылки представляют собой просто имя ячейки, например,A1.

• Абсолютная. Ссылка является полностью абсолютной. Когда формула копируется, ссылка не изменяется. Для задания абсолютной ссылки используются знаки доллара –$A$1.

• Абсолютная строка. Ссылка является частично абсолютной. Когда формула копируется, та часть ссылки, которая указывает столбец, изменяется в соответствии с новым местоположе­нием формулы, а часть ссылки, указывающая строку, остаётся неизменной. Для задания ссылки с абсолютной строкой необходимо включить в ссылку один знак доллара –A$1.

• Абсолютный столбец. Ссылка является частично абсолютной. Когда формула копируется, та часть ссылки, которая указывает строку, изменяется в соответствии с новым местоположе­нием формулы, а часть ссылки, указывающая столбец, остаётся неизменной. Для задания ссылки с абсолютным столбцом необходимо включить в ссылку один знак доллара – $A1.

---

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

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

---

3. Ссылки в стиле r1c1

Обычно в приложении MicrosoftExcelиспользуется формат ссылок, обозначаемый как А1. Адрес ячейки, отображаемый в таком формате, состоит из буквы, которая обозначает столбец, и числа, которое задаёт номер строки. Однако в приложенииMicrosoftExcelсуществует также формат ссылок, обозначаемый какR1C1. В этом формате записывается номер строки и номер столбца. Например, ячейка А1 обозначается какR1C1, ячейка А2 – какR2C1, ячейка В1 – какR1C2.

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

Ссылки типа R2C1 являются абсолютными ссылками. Для записи относительных ссылок используются квадратные скобки. Числа в квадратных скобках обозначают относительное местоположение ссылок. Например, ссылкаR[-5]C[-3] указывает на ячейку, которая находится на пять строк выше и на три столбца левее той ячейки, в которой расположена данная ссылка. СсылкаR[2]C[4] указывает на ячейку, которая находится на две строки ниже и на четыре столбца правее той ячейки, в которой расположена данная ссылка.

Для примера сравним простые формулы со ссылками, записанными в разных форматах. Предполагается, что эти формулы записаны в ячейке B1.

Стандартный	R1C1
=A1+1	=RC[-1]+1
=$A$1+1	=R1C1+1
=$A1+1	=RC1+1
=A$1+1	=R1C[-1]+1
=СУММ(A1:A10)	=СУММ(RC[-1]:R[9]C[-1])
=СУММ($A$1:$A$10)	=СУММ(R1C1:R10C1)

Формат ссылок R1C1 используется достаточно редко, т.к. ссылки в этом формате сложны для понимания. Однако с помощью этого формата легко найти ошибку в формулах – если используется формат ссылокR1C1, то все копии одной и той же формулы будут совершенно одинаковы. Это относится ко всем типам ссылок – относительным, абсолютным и смешанным. Кроме того, ссылки в стилеR1C1 используются в языке VBA.

4. Ссылки на другие листы или рабочие книги

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

---

=Лист1!$D$10

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

='E:\Документы\[Бюджет на 2010 год.xlsx]Лист1'!$D$10

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

---

5. Использование имён

Одна из самых существенных возможностей приложения MicrosoftExcel– это назначение содержательных имён самым разным объектам. Имена можно присваивать ячейкам, диапазонам ячеек, строкам, столбцам, диаграммам, а также константам и формулам.

Использование имён удобно при написании кода VBA, в котором применяются ссылки на отдельные ячейки или диапазоны. Дело в том, что если ячейку или диапазон, на которые ссылает оператор VBA, переместить в другое место, то в VBA-коде эти ссылки не будут автоматически обновляться. Использование имён решает эту проблему.

1. Присвоение имён ячейкам и диапазонам

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

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

2. Пересечение имён

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

3. Присвоение имён константам

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

4. Присвоением имён формулам

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

---

Смотрите также файлы

• Lection3.docx

• Lection2.docx

• landau.pdf

• Lab_rab_2_Ekv_k-ty.doc

• Lab_rab_2 эквиваленты.doc