Ссылки на ячейки за пределами текущего рабочего листа

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

Запишите:

1. Формат записи для ссылки на другой лист текущей книги имеет вид:

Имя листа. Адрес ячейки.

Пример формулы, использующей ссылку на ячейку другого листа =В3*Лист1.А1 (ячВ3 тек. Листа умножается на сод. ячейки А1 листа Лист1)

2. Формат записи для ссылки на ячейки другой рабочей книги:

[Полное имя книги] Имя листа. Адрес ячейки.

Пример формулы. =А2*‘С:\ Мои документы \[ Бюджет] Лист1’!А2.

Если же книга открыта, достаточно указать ее имя и лист

А2*‘[ Бюджет] Лист1’.А2.

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

Формулы массивов.

Массив-это набор элементов, которые могут обрабатываться как единая группа или каждый в отдельности.

В Ех массивы могут быть одно- или двухмерными. Одномерные массив –это группа ячеек, размещенные в одной строке (вектор-строка) или в одном столбце (вектор-столбец). Двухмерный массив размещается в нескольких строках и столбцах и представляет собой матрицу.

Рассмотрим пример. Требуется определить доход фирмы по месяцам года. При решении задачи нам поможет таблица.

Доход фирмы Империал по месяцам 1-го полугодия 2007 года.

Прибегнем к нашим знаниям и для расчета дохода

1. Введем в ячейку D6 формулу =С6 – В6, затем

2. Скопируем формулу и получим 6 отдельных формул в ячейках столбца D.

А теперь поступим следующим образом.

1. Выделим ячейки Е5:Е10 – а это и есть массив - массив результата

2. В строку формул введем формулу=С6:С11- В6:В11, т.е из вектора-столбца С вычтем вектор-столбец В. А теперь сохраним формулу, но не Enter, как обычно, а комбинацией клавиш (Ctrl+ Shift+ Enter).

Обратите внимание –во все выделенные ячейки ввелась одна формула и мы получили сразу все 6-ть искомых значений.

Запишите:

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

Чтобы ввести формулу массива следует:

1. Выделить диапазон ячеек, в которые вводятся формулы,

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

3. Нажать комбинацию клавиш Ctrl+ Shift+ Enter. При этом формула автоматически заключается в фигурные скобки. По фигурным скобкам Вы всегда сможете отличить формулу массива.

Мы решили нашу задачу двумя способами – набором отдельных формул и с использованием формулы массива. Отметим, что 2-ой способ, кроме удобства, имеет и другие преимущества.

1. Есть уверенность что, все формулы введены правильно,

2. Формулы массивов больше защищены от случайных изменений, поскольку Ехс6ен\l не позволит изменить одну формулу в ячейке, входящей в диапазон

формулы массива.

Запишите:

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

Редактирование табличных формул.

1. Выделите блок с формулами

2. Нажмите клавишу F2 и внесите изменения

3. Нажмите комбинацию клавиш Ctrl+Shift+Enter.

Если потребуется увеличить или уменьшить исходные диапазоны, проще всего,

очистить ячейки диапазона с формулой- результата и заново перезаписать ее во вновь выделенный диапазон.

Формулы массивов применяются при решении таких задач, как

1. Суммирование заданного числа наибольших (наименьших) значений массива

2. Поиск заданного значения в диапазоне

3. Определение местоположения мах, среднего, мин значения в диапазоне

4. Определение количества различий в двух диапазонах и т.д.

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

Запишите: