Внешние ссылки на ячейки

Excel разрешает ссылаться на ячейки, находящиеся на других листах книги или в другой книге, или на данные другого приложения. Ссылки на ячейки других листов или других книг называются внешними ссылками ( Январь!В2; Лист!В2). Ссылки на данные других приложений называются удаленными ссылками ( '[Задание Excel.xls]Лист1'!$B$2 ).

Рассмотрим как включить в формулу внешние ссылки с помощью мыши. На листах рабочей книги январь и февраль приведена информация о кол-ве произведенной продукции в соответствующих месяцах. На листе итого необходимо вывести суммарное количество произведенной продукции за январь и февраль (см. рис. 6_4). Для этого необходимо выполнить следующие действия:

1. Выделить ячейку В2 на листе Итого. Набрать знак равенства (=) и щелкнуть на листе Январь.

2. Выделить ячейку В2 и ввести знак +. Щелкнуть на листе Февраль и выделить ячейку В2.

3. Для завершения ввода формулы нажать клавишу <ENTER>.

Абсолютные, относительные, смешанные адрес (ссылки)

Адреса ячеек или ссылки на ячейки, которые используют в Excel, бывают: относительные, смешанные и абсолютные. Ссылки вида А1 называются относительными, вида $A$1 – абсолютными, вида $A1 или A$1 – смешанными.

Если копируют формулы с относительными ссылками на одну или более строк вниз или вверх, то в адресах ячеек изменятся нумерация строк. Например, в примере (рис.6_1) мы копировали формулу =(D4-C4)*E4 из ячейки F4 в ячейку F5 (копировали формулу на одну строку вниз). В ячейке F5 формула будет такой =(D5-C5)*E5 (в формуле изменилась нумерация строк D4®D5, C4®C5, E4®E5).

Если копируют формулы с относительными ссылками на один или более столбец вправо или влево, то в адресах ячеек изменяются имена столбцов. Например, в примере (рис.6_1) мы скопировали формулу =(D4-C4)*E4 из ячейки F4 в ячейку G4 (копировали формулу на один столбец вправо). В ячейке G4 формула будет такой =(C4-E4)*F4 (в формуле изменились имена столбцов D4®C4, C4®E4, E4®F4)

Если копируют формулы с относительными ссылками на листе Excel по диагонали, то в адресах ячеек изменятся и нумерация строк, и имена столбцов. Например, в примере (рис.6_1) мы скопировали формулу =(D4-C4)*E4 из ячейки F4 в ячейку G5 (копировали формулу по диагонали). В ячейке G5 формула будет такой =(C5-E5)*F5 (в формуле изменились и нумерация строк, и имена столбцов D4®C5, C4®E5, E4®F5)

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

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

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

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

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

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

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

Пример

Дано:

Рис. 12

Необходимо определить:

1. На какую сумму продано каждого вида товаров в гривнах.

2. Долю продаж каждого вида товаров в общей сумме продаж.

3. На сколько цена каждого вида товаров отличается от средней цены.

4. На какую сумму продано каждого вида товаров в долларах.

Для выполнения первого задания необходимо:

1. щелкнуть в ячейке D2 и ввести =;

2. щелкнуть на ячейке В2, нажать *, а затем щелкнуть на ячейке С2;

3. нажать клавишу ENTER (см. рис. 13);

4. выделить ячейку D2 и скопировать с помощью автозаполнения формулу в диапазон ячеек D3:D5. Результат приведен на рис. 14.

Рис. 13

Рис. 14

Для выполнения третьего задания необходимо:

1. с помощью мастера функций вычислить в ячейке D6 общую сумму выручки от продаж (см. рис. 15);

Рис. 15

2. в ячейку Е2 ввести формулу =D2/D6 (см. рис. 16).

В дальнейшем эту формулу необходимо копировать вниз по строкам, следовательно, в адресах ячеек будут изменяться номера строк. Однако в этой формуле ссылка на ячейку D2 должна изменятся при копировании, а ссылка на ячейку D6 изменятся не должна. Поэтому необходимо заменить относительную ссылку D6 на смешанную D$6 (копируем вниз по строкам).

3. для того чтобы в формуле изменить относительную ссылку на смешанную, необходимо, щелкнуть в строке формул (см. рис. 17) внутри ссылки D6, а затем нажимать клавишу F4 до тех пор, пока не получим формулу: =D2/D$6. Затем нажать клавишу ENTER, выделить ячейку Е2 и скопировать с помощью автозаполнения формулу в диапазон ячеек Е3:Е5. Результат приведен на рис.18;

Рис. 16

Рис. 17

MS Office 2003

MS Office 2007

Рис. 18

4. для того чтобы в диапазоне ячеек Е2:Е3 установить процентный формат, нужно: выделить этот диапазон и щелкнуть на кнопке Процентный формат (см. рис. 18).

Для выполнения четвертого задания необходимо:

1. с помощью мастера функций вычислить в ячейке В6 среднее значение цены (см. рис. 19);

2. в ячейку G2 ввести формулу =B$6-B2 (см. рис. 20). Ссылка на ячейку В6 при копировании формулы вниз по строкам не должна изменяться, поэтому, в формуле была использована смешанная ссылка;

3. выделить ячейку G2 и скопировать с помощью автозаполнения формулу в диапазон ячеек G3: G5. Результат приведен на рис. 20.

Рис. 19

Рис. 20

Для выполнения пятого задания необходимо:

1. в ячейку Н2 ввести курс доллара;

2. в ячейку F2 ввести формулу =D2/H$2 (см. рис. 21). Ссылка на ячейку H2 при копировании формулы вниз по строкам не должна изменяться, поэтому, в формуле была использована смешанная ссылка;

3. выделить ячейку F2 и скопировать с помощью автозаполнения формулу в диапазон ячеек F3: F5. Результат приведен на рис. 21.

Рис. 21

Пример

Дано:

Рис. 22

Необходимо определить долю каждого года в общем объеме выпуска всей продукции.

Для выполнения задания необходимо:

1. с помощью мастера функций вычислить в ячейке G3 общий объем выпуска за все годы (введем в ячейку G3 формулу =СУММ(B3:F6));

2. с помощью мастера функций вычислить в ячейке В7 общий объем выпуска за 2000 год (см. рис. 23), а затем скопировать формулу с помощью автозаполнения в диапазон ячеек С7:F7;

3. в ячейку В8 ввести формулу =B7/$G3 (см. рис. 24). Ссылка на ячейку G3 при копировании формулы вправо по столбцам не должна изменяться, поэтому, в формуле была использована смешанная ссылка (копируем формулу по столбцам вправо);

4. выделить ячейку В8 и скопировать с помощью автозаполнения формулу в диапазон ячеек С8: F8. Установить в диапазоне ячеек С8: F8 процентный формат. Результат приведен на рис. 24.

Рис. 23

Рис. 24

Дата добавления: 2015-07-20