Виды адресов ячеек

Типы адресации в Microsoft Excel

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

I. Адресация одной ячейки. Ячейка на пересечении столбца А и строки 3 имеет адрес А3. Всего на листе может быть 65536 строк и 256 столбцов. Столбцы нумеруются A, …, Z, AA, AB, …, IV.

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

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

Абсолютные ссылки не меняются при переносе или копировании ячейки со ссылками. Перед заголовком столбца и номера строки ячейки ставится знак доллара $. Примеры абсолютных ссылок $A$1, $B$67.

A	B
1	2
=$A$1+$B$1
=$A$1+$B$1

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

При переносе или копировании ячейки с относительными ссылками, ссылки меняются, сохраняя пространственное соотношение с ячейками, на которые они ссылаются. Относительная ссылка представляет адрес ячейки. Примеры относительных ссылок A2, CD45.

A	B
1	2
=A1+B1
1	2
=A4+B4

3. Смешанные ссылки.

В смешанных ссылках либо перед заголовком столбца, либо номером строки ставится знак доллара. Этот параметр не меняется при переносе или копировании ячейки со ссылками, как абсолютная ссылка, а параметр, перед которым знак доллара отсутствует – меняется, сохраняя пространственное соотношение, как относительная ссылка. Примеры смешанных ссылок T$2, $AC5.

A	B	C
1	2
=A$1+$B1
2
=B$1+$B4

Задача. Формулу из ячейки B2 скопировали в ячейку C3. Какое значение имеет формула в ячейке C3?

Ответ.С3: =B2+$B$1*B$2; B2: 7; C3: 21.

Задача. В ячейке B2 вычисляется сумма двух ячеек. Формулу из ячейки B2 скопировали в ячейку C3. Зависимость между ячейками изображена на рисунке.

A	B	C

Какая формула записана в ячейке B2?

Ответ. Формула в B2: =A2+$B1.

4. Трехмерные ссылки (объемные ссылки).

Трехмерная ссылка используется для ссылки на ячейку, находящуюся на другом листе, и состоит из двух частей: названия листа и абсолютной (относительной, смешанной) ссылки на ячейку, разделенных восклицательным знаком, например:

Если название листа содержит пробелы, знаки пунктуации, то название листа в ссылке заключается в апострофы, например:

5. Внешние ссылки.

Внешняя ссылка используется для ссылки на ячейку в другой книге. Внешняя ссылка состоит из названия книги в квадратных скобках и трехмерной ссылки, например:

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

‘[Книга 1.xls]Лист 1’!B1.

Расширение файла книги можно не указывать. Если книга закрыта, то необходимо указать полный путь к файлу книги до квадратных скобок с названием книги, например:

‘C:MyDocs[Книга 1.xls]Лист 1’!B1.

II. Адресация связных ячеек (диапазона). Диапазон определяется адресами верхней левой и нижней правой ячеек.

Например, три последовательные ячейки А1, В1, С1 можно адресовать как А1:С1.

Возможно задание диапазонов с использованием трехмерных ссылок. Например, адресация диапазона Лист1:Лист3!B1 задает все ячейки B1 с листа Лист1 по лист Лист3, а адресация диапазонов Лист1:Лист3!C1:D9 задает диапазон C1:D9 на листах Лист1-Лист3.

Трехмерные ссылки нельзя использовать для создания явного или неявного пересечения диапазонов.

III. Адресация несвязных ячеек. Непоследовательные ячейки перечисляются через точку с запятой. Например, ячейки А1, А3, В3, С3 можно адресовать как А1; А3:С3.

10.6.4. Присвоение имен ячейкам
и диапазонам в Microsoft Excel

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

Существует два типа имен ячеек и диапазонов:

1) на уровне листа;

2) на уровне книги.

Чтобы присвоить имя ячейке или диапазону на уровне листа необходимо выполнить следующие действия:

1) выделить ячейку или диапазон ячеек;

2) выбрать пункт меню Вставка | Имя | Присвоить;

3) в открывшемся окне Присвоение имени в поле Имя ввести имя ячейки или диапазона, причем имя должно начинаться как трехмерная ссылка с названия листа и знака восклицания (!); первый символ имени должен быть буквой или знаком подчеркивания, остальные символы имени могут быть буквами, цифрами, точками или знаками подчеркивания; регистр не учитывается;

4) в поле Формула будет записана ссылка на ячейку или диапазон;

5) нажать кнопку Добавить, чтобы ввести еще имена ячеек или диапазонов, или кнопку Ok, чтобы закрыть окно.

Чтобы присвоить имя ячейке и диапазону на уровне книги необходимо выполнить те же действия, но на шаге 3 при задании имени не указывать название листа.

Чтобы присвоить имя формуле или константе необходимо выполнить те же действия, что и при задании имени на уровне книги, но на шаге 4 в поле Формула необходимо записать формулу или константу, например «=25%».

Присвоенные имена, именованные формулы и константы используются в формулах. При использовании имен на уровне листа на другом листе необходимо записать название листа, знак восклицания и имя, как в трехмерной ссылке. Например, Лист1!Итог.

Адресация ячеек в Excel

Неизвестный Excel

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

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

Для начала напомню, что у каждой ячейки в Excel есть свой уникальный адрес. Адрес может быть относительным и абсолютным. Что такое абсолютный и относительный адреса — об этом как-нибудь в другой раз.

Относительный адрес может быть, например, таким:

B3 — третья ячейка в столбце В.

Однако на другом листе тоже может быть ячейка B3. Чтобы однозначно определить ячейку в пределах книги Excel, можно перед её адресом написать имя листа.

Такой адрес в книге может выглядеть так:

То есть здесь уже идёт речь не о какой-то абстрактной ячейке В3, а о ячейке В3, расположенной на листе с именем “Лист2”.

Это только самые общие сведения об адресации ячеек в Excel, но для начала этого достаточно. Однако надо ещё рассказать о видах адресации.

Формат адреса ячейки в Excel

С одним форматом адреса вы уже знакомы. Это формат вида “буква-цифра”:

Где Б — это буквенное обозначение столбца, а Ц — это номер строки. Таким образом, каждая ячейка относительно текущего листа имеет уникальный адрес. Например,

А10 — это десятая строка в столбце А.

Однако в Excel есть и другой формат адресации ячейки:

где R — это ряд (строка), а С — это столбец. После буквы следует, соответственно, номер строки х и номер столбца у. Например:

R3C7 — это третья строка и седьмой столбец, что в формате “буква-цифра” будет тем же адресом, что и G3.

Лично мне больше нравится формат “буква-цифра”. И по умолчанию обычно такой формат и используется (видимо, он больше нравится не только мне, но и разработчикам Excel).

Однако иногда (во всяком случае в Excel 2003 это случается) формат адреса ячейки почему-то сам собой меняется на RxCy. И тогда приходится менять его в настройках программы вручную.

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

Поэтому подсказываю. В Excel 2007 изменить стиль адреса ячеек можно так:

1. Нажать кнопку ОФИС (в левом верхнем углу)
2. Нажать кнопку ПАРАМЕТРЫ EXCEL
3. Выбрать вкладку ФОРМУЛЫ
4. Найти там строку “Стиль ссылок R1C1”

Если вы поставите галочку напротив надписи “Стиль ссылок R1C1”, то адреса ячеек будут иметь формат RxCy. Если снимите галочку, то будет использоваться формат “буква-цифра”.

Виды адресов ячеек

Вставка формул в ячейку

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

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

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

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

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

В Excel формула может использовать значения в ячейках для выполнения таких операций как сложение (+), вычитание (-), умножение (*), деление (/).

Например, формула =А1+В2 обеспечивает сложение чи­сел, хранящихся в ячейках А1 и В2, а формула =А1*5 — умножение числа, хранящегося в ячейке А1, на 5.

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

Ячейка, содержащая формулу называется зависимой ячейкой, если ее значение зависит от значений в других ячейках. Например, ячейка B2 является зависимой, если она содержит формулу =C2.

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

ВНИМАНИЕ! При изменении исходных значений , входящих в формулу, результат пересчитывается немедленно .

ВНИМАНИЕ! При вводе формулы в ячейке отображается не сама формула, а результат вычислений по этой формуле .

Чтобы увидеть формулы, необходимо выполнить команду «Формулы / Зависимости формул».

Относительные и абсолютные ссылки

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

I . Относительные ссылки в формулах используются для указания адреса ячейки, вычисляемого относительно ячейки, в которой находится формула. Относительные ссылки имеют следующий вид: А1, ВЗ и тому подобное. По умолчанию при наборе формул в Ехсе l используются относительные ссылки.

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

Например, при копировании формулы, содержащей относи­тельные ссылки, из ячейки С1 в ячейку В2 обозначения столбцов и строк в формуле изменятся на один шаг вправо и вниз:

=А1*В1

II . Абсолютные ссылки в формулах используются для указания фиксированного адреса ячейки (т.е. адреса ячейки, вычисляемого в абсолютной системе координат и не зависящего от текущей ячейки). В абсолютных ссылках перед неизменяемыми значениями адреса ячейки ставится знак доллара. Абсолютные ссылки имеют следующий вид: $А$1, $В$1 и тому подобное.

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

Например, при копировании формулы, содержащей абсолютные ссылки, из ячейки СЗ в ячейку В4 обозначения столбцов и строк в формуле не изменятся:

=$А$1*$В$1

III . Часто применяют не чисто относительные или абсолютные ссылки, а смешанные ссылки , например, С$1 или $С1.

Если символ доллара стоит перед буквой (например $А1), то координата столбца абсолютная, а строки — относитель­ная. Если символ доллара стоит перед числом (например, А$1), то, наоборот, координата столбца относительная, а строки — абсолютная.

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

Например, если формулу «=A$1*5» из ячейки C1 скопировать в ячейку C2, то формула не изменится, так как положение строки зафиксировано. А если эту же формулу из ячейки C1 скопировать в ячейку D1, то формула изменится на «=B$1*5».

=B$1*5

ВОПРОС. Что произойдет, если 1) формулу «=$A1*5» из ячейки C1 скопировать в ячейку C2;

2) формулу «=$A1*5» из ячейки C1 скопировать в ячейку D1

# ДЕЛ/0! – в формуле задано деление на 0 или на значение пустой ячейки

#ИМЯ? – программа не может распознать введенное имя ячейки или диапазона, или имя функции введено с ошибкой

#ЧИСЛО! – ввод неправильного числа в формулу

#ССЫЛКА! – в формуле задана неверная ссылка на ячейку

#ЗНАЧ! – в формуле используется оператор или аргумент неправильного типа

#ПУСТО! – задано пересечение двух областей, не имеющих общих ячеек

Виды адресации ячеек в электронных таблицах

Презентация к уроку

Загрузить презентацию (258,6 кБ)

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

Тип урока: обобщение и контроль знаний.

Обучающие цели урока:

Развивающие цели урока:

Воспитательные цели урока:

Компьютеры IBM PC, программа Microsoft Excel, Microsoft PowerPoint, презентация к уроку, карточки с заданиями контрольной работы (4 варианта).

К этому уроку учащиеся знают:

• Что такое “электронные таблицы”, их виды, функции; назначение. Интерфейс табличного процессора Microsoft Excel.
• Арифметические действия в электронных таблицах, автосуммирование числовых данных, возможности использования встроенных функций.
• Относительные и абсолютные ссылки.
• Правила копирования формул.

1. Организационный момент.
2. Активизация опорных ЗУН учащихся.
3. Контрольная работа.
4. Подведение итогов, выставление оценок.

1. Организационный момент.

Приветствие, проверка присутствующих учащихся.

2. Активизация опорных ЗУН учащихся.

Повторение ранее изученного материала производится с помощью презентации.

На предыдущих уроках мы уже познакомились с тем как вводятся формулы в таблицах. Цель нашего урока – проверить и закрепить знания, приобретенные на предыдущих занятиях. Давайте вспомним.

• Для чего предназначены Электронные таблицы?
• Какова структура Электронной таблицы?
• Какие основные элементы электронной таблицы вам известны?
• Как задается имя ячейки (строки, столбца) в электронной таблице?
• Какие данные можно ввести в ячейку электронной таблицы?

СЛАЙДЫ 1-2. Вступление. Объявляется тема урока, понятия, которые будут обсуждаться на уроке. Мы продолжаем изучать электронные таблицы и основная задача нашего сегодняшнего урока — это закрепление понятия относительной и абсолютной адресации ячеек в электронных таблицах.

Итак, что мы с вами знаем и умеем делать:

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

Давайте вспомним: Что такое формула? Какие правила записи формул в электронных таблицах вы знаете? Как отображается формула при вводе и по окончании ввода?

СЛАЙД 3. (Рис. 1) Основным достоинством электронной таблицы Excel является наличие мощного аппарата формул. Можно складывать, умножать, делить, извлекать квадратные корни, вычислять синусы и косинусы и т.д.

СЛАЙД 4. (Рис. 2). На данных примерах мы вспомним, как вычисляется результат по формуле. (Ответ отобразится на слайде по щелчку мыши)

Мы видим, что в формулах используются ссылки на адреса ячеек. Существуют два основных типа ссылок: относительные и абсолютные, кроме этого, могут быть использованы смешанные ссылки. Различия между относительными и абсолютными ссылками проявляются при копировании формулы из активной ячейки в другую ячейку (СЛАЙД 5).

СЛАЙД 6. На прошлых уроках мы выяснили, что всякое изменение места расположения формулы введет к автоматическому изменению адресов ячеек в этих формулах. Таким образом реализован принцип относительной адресации. По умолчанию при наборе формул в Excel используются относительные именно ссылки. На примере мы видим, как изменяется адрес ячеек в формулах при копировании.

СЛАЙД 7. Но как выяснилось, в некоторых случаях необходимо, чтобы при копировании адрес ячейки не изменялся – принцип абсолютной адресации. Абсолютная ссылка в формуле используется для указания фиксированного адреса ячейки. Для абсолютных ссылок в имени ячейки употребляется символ $. На примере видно, что адрес ячеек в формуле не изменился при копировании.

СЛАЙД 8. В формулах можно использовать смешанные ссылки, в которых координата столбца абсолютная, а строки – относительная, или, наоборот, координата столбца относительная, а строки – абсолютная.

А теперь давайте рассмотрим копирование формул, содержащих относительные и абсолютные ссылки на примерах, разберем задания. Рис. 3, 4. (СЛАЙДЫ 9-10).

3. Контрольная работа.

Теперь вам предстоит выполнить контрольную работу. Вы получите карточки с заданиями (Приложение 1) и бланки для ответов. Обращаю ваше внимание на то, что в первых трех заданиях надо записать формулу, а в двух последних – число. Желаю вам успеха!

4. Подведение итогов, выставление оценок.

Н.Д. Угринович, Базовый курс: Учебник для 8 класса, Москва, БИНОМ. Лаборатория знаний АО “Московские учебники”, 2007 г.
1. К.М. Златопольский, 1700 заданий по Microsoft Excel, “БХВ-Петербург”, 2003 г.

Оценка статьи:

Загрузка...