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

ПРАКТИЧЕСКОЕ ЗАНЯТИЕ № 3

ТЕМА ЗАНЯТИЯ: Вычисления с помощью электронных таблиц

УЧЕБНЫЕ ВОПРОСЫ:

1. Использование автовычислений.

2. Ввод формул, абсолютные и относительные ссылки.

3. Использование при вычислениях мастера функций.

4. Сортировка данных.

КОНТРОЛЬНЫЕ ВОПРОСЫ:

1. Как изменить размер строки или столбца?

2. В каких единицах измеряется ширина столбца?

3. Как выделить цветом столбец или строку?

Основные действия и приемы работы при выполнении лабораторной работы

Использование автовычислений

Если в Excel требуется отобразить итоговое значение для диапазона ячеек, используется Автовычисление. При выделении ячеек Excel отображает сумму для диапазона в строке состояния. Она отображается в виде горизонтальной области под окном листа Excel. Если строка состояния не отображается, выберите команду Строка состояния в меню Вид.

На рис. 24.1. выделены четыре ячейки, сумму которых (922) средство автовычислений отображает в строке состояния.

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

Получение суммы. Существует возможность автоматически вставить сумму диапазона ячеек при помощи автосуммы. При выделении ячейки, в которую требуется вставить сумму, и нажатии кнопки Автосумма, Excel предлагает формулу. Для того чтобы реализовать формулу, нажмите клавишу Enter.

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

Общие итоги. Если лист содержит несколько итоговых значений, созданных с использованием функции СУММ, можно создать общую сумму этих значений с помощью кнопки Автосумма.

Автосумма предлагает формулу =СУММ(Н24;Н22), которая суммирует ячейки Н24 и Н22 (рис. 24.3).

Ввод формул, абсолютные и относительные ссылки

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

Построение формул

Формула в Excel всегда начинается со знака равенства (=) или (+). Формулы подчиняются определенному синтаксису, согласно которому в формулу включается знак равенства (=), за которым идут вычисляемые элементы (операнды), разделенные операторами. В качестве операндов могут выступать постоянные значения (константы), ссылки на ячейки или диапазоны ячеек, заголовки, имена или функции.

В Excel действия выполняются слева направо – с учетом приоритета операторов. Порядком вычислений управляют с помощью скобок, группируя порядок действий скобками.

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

В приведенном примере скобки вокруг первой части формулы определяют порядок вычислений. Первым определяется значение B4+25, затем полученный результат делится на сумму значений в ячейках D5, E5 и F5.

Пример:

=(B4+25)/СУММ(D5:F5)

Если меняется ячейка, на которую ссылается формула, по умолчанию значение в зависимой ячейке меняется. Например, если значение одной из следующих ячеек меняется, результат формулы =B2+C2+D2 также изменится (рис. 24.4).

Если формула использует не ссылки на ячейки, а константы (например, =30+70+110), результат изменится только при изменении самой формулы.

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

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

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

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

Относительные ссылки

При создании формулы относительные ссылки учитывают расположение относительно ячейки, содержащей формулу. Например, ячейка B6 содержит формулу =A5; искомое значение находится на одну ячейку выше и левее ячейки B6 (рис. 24.5).

При копировании формулы с относительными ссылками Excel автоматически изменяет ссылки во вставляемой формуле. Например, формула в ячейке B6 (=A5), которая ссылается на ячейку, расположенную на одну строку выше и на один столбец левее, скопирована в ячейку B7 (рис. 24.6). Excel изменил формулу в ячейке B7 на =A6, ссылаясь на ячейку, которая расположена на одну строку выше и на один столбец левее ячейки B7.

Абсолютные ссылки

Чтобы ссылки не изменялись при копировании формулы в другую ячейку, используют абсолютные ссылки. Например, если имеется формула, умножающая содержимое ячейки A5 на ячейку C1 (=A5*C1), то при копировании формулы в другую ячейку изменятся обе ссылки. Для создания абсолютной ссылки на ячейку C1 поставьте знак доллара ($) перед той частью ссылки, которая не должна изменяться. Чтобы создать абсолютную ссылку на ячейку C1, поместите знак доллара так, как показано в примере: =A5*$C$1