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

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

· если при копировании происходит смещение формулы на N столбцов вправо (влево), то индекс столбцов в относительных ссылках увеличивается (уменьшается) на N единиц;

· если при копировании происходит смещение формулы на N строк вниз (вверх), то индекс строк относительной ссылки на ячейку увеличивается (уменьшается) на N единиц.

Абсолютные же ссылки при копировании формул не изменяются.

Ссылки

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

По умолчанию MS Excel использует стиль ссылок A1, в котором столбцы именуются буквами (от A до IV, всего не более 256 столбцов), а строки нумеруются числами (от 1 до 65536).

В формулах возможно использование специальных функций, которые выполняют операции над несколькими данными, хранящимися в различных ячейках. Для обращения к группе (диапазону) ячеек используются интервальные ссылки, которые задаются адресом левого верхнего угла, отделенного от адреса правого нижнего угла двоеточием («:»). Если в формуле используется несколько ссылок (список), то элементы этого списка отделяются друг от друга точкой с запятой («;»).

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

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

Ссылки по форме записи подразделяются на относительные, абсолютные и смешанные.

Таблица 3.3

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

Абсолютная ссылка в формуле – это всегда ссылка на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей такую формулу, абсолютная ссылка не изменяется. Чтобы превратить относительную ссылку в абсолютную, необходимо перед индексами столбца и строки поставить знак «$» (нажать клавишу F4). Например:

1) С1 – относительная ссылка;

2) $C$1 – абсолютная ссылка;

3) C$1, $C1 – смешанные ссылки.

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка по столбцу приобретает вид $A1, $B1 и т. д. Абсолютная ссылка по строке выглядит как A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. Для перехода от относительной ссылки к абсолютной или к смешанной ссылке можно использовать клавишу F4.

В формулах можно использовать данные, расположенные на разных листах. Для обращения к этим данным используются трехмерные (объемные) ссылки.

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

1) трехмерные ссылки могут быть использованы для создания ссылок на ячейки других листов, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЕТ, СЧЕТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, ДИСП, ДИСПА, СТАНДОТКЛОНПА, ДИСПР, ДИСПРА;

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

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

1) Вставка или копирование. Если между Лист2 и Лист6 книги вставить новые листы, то MS Excel добавит в сумму содержимое ячеек с A2 по A5 на новых листах.

2) Удаление. Если между Лист2 и Лист6 книги удалить листы, то MS Excel исключит из суммы содержимое ячеек удаленных листов.

3) Перемещение. Если переместить листы, находящиеся между Лист2 и Лист6 книги, и разместить их таким образом, что они будут расположены перед Лист2 или после Лист6, то MS Excel исключит из суммы содержимое ячеек перемещенных листов.

4) Перемещение граничного листа. Если переместить Лист2 или Лист6 в новое место книги, то MS Excel включит в сумму содержимое ячеек листов, находящихся между Лист2 и Лист6 включительно.

5) Удаление граничного листа. Если удалить Лист2 или Лист6, то MS Excel включит в сумму содержимое ячеек листов, находившихся между ними.

Поиск по сайту: