Типы адресации в excel
В этой статье более подробно разберём виды адресации ячеек в Excel. В обзорном видео я уже об этом кратко рассказывал, ну а сейчас пришла пора разъяснить эту тему более подробно.
Для начала напомню, что у каждой ячейки в Excel есть свой уникальный адрес. Адрес может быть относительным и абсолютным. Что такое абсолютный и относительный адреса - об этом как-нибудь в другой раз.
Относительный адрес может быть, например, таким:
B3 - третья ячейка в столбце В.
Однако на другом листе тоже может быть ячейка B3. Чтобы однозначно определить ячейку в пределах книги Excel, можно перед её адресом написать имя листа.
Такой адрес в книге может выглядеть так:
То есть здесь уже идёт речь не о какой-то абстрактной ячейке В3, а о ячейке В3, расположенной на листе с именем “Лист2”.
Это только самые общие сведения об адресации ячеек в Excel, но для начала этого достаточно. Однако надо ещё рассказать о видах адресации.
Формат адреса ячейки в Excel
С одним форматом адреса вы уже знакомы. Это формат вида “буква-цифра”:
Где Б - это буквенное обозначение столбца, а Ц - это номер строки. Таким образом, каждая ячейка относительно текущего листа имеет уникальный адрес. Например,
А10 - это десятая строка в столбце А.
Однако в Excel есть и другой формат адресации ячейки:
где R - это ряд (строка), а С - это столбец. После буквы следует, соответственно, номер строки х и номер столбца у. Например:
R3C7 - это третья строка и седьмой столбец, что в формате “буква-цифра” будет тем же адресом, что и G3.
Лично мне больше нравится формат “буква-цифра”. И по умолчанию обычно такой формат и используется (видимо, он больше нравится не только мне, но и разработчикам Excel).
Однако иногда (во всяком случае в Excel 2003 это случается) формат адреса ячейки почему-то сам собой меняется на RxCy. И тогда приходится менять его в настройках программы вручную.
Начинающих это может ввести в состояние паники, потому что с первого раза найти эти настройки практически ни у кого не получается.
Поэтому подсказываю. В Excel 2007 изменить стиль адреса ячеек можно так:
- Нажать кнопку ОФИС (в левом верхнем углу)
- Нажать кнопку ПАРАМЕТРЫ EXCEL
- Выбрать вкладку ФОРМУЛЫ
- Найти там строку “Стиль ссылок R1C1”
Если вы поставите галочку напротив надписи “Стиль ссылок R1C1”, то адреса ячеек будут иметь формат RxCy. Если снимите галочку, то будет использоваться формат “буква-цифра”.
Есть два вида представления ссылок в Microsoft Excel:
Включить стиль ссылок R1C1 можно в настройках «Сервис» —> «Параметры» —> закладка «Общие» —> галочка «Стиль ссылок R1C1»
Ссылки в Excel бывают 3-х типов:
- Относительные ссылки (пример: A1);
- Абсолютные ссылки (пример: $A$1);
- Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).
Знак $ здесь никакого отношения к денежным единицам не имеет, это лишь способ указать Excel тип ссылки. Различия между разными типами ссылок можно увидеть, если потянуть за маркер автозаполнения активной ячейки или диапазона ячеек, содержащих формулу со ссылками.
Относительные ссылки
Абсолютные ссылки
Смешанные ссылки
Для упрощения работы с ячейками или диапазоном ячеек можно дать ей/им имя, и затем обращаться к ячейке или диапазону не по его адресу, а по символьному имени. Именованные ячейки можно использовать везде, где можно использовать то значение, на которое указывает ссылка.
Примечание! Именованные ячейки по умолчанию являются абсолютными ссылками.
Для создания именованной ячейки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ячейка может ссылаться на несвязный диапазон ячеек (выделенный с "Ctrl").
Для вставки именованной ячейки можно воспользоваться кнопкой со стрелкой вниз:
или нажать клавишу "F3", откроется следующее окно:
Пример использования: "=СУММ(tablica_1);"
Для того что бы убрать имя именованной ячейки (например: чтобы присвоить другой ячейке это имя) - Вставка/имя/присвоить/удалить. В Excel 2007 - Формулы/Диспетчер имен/Удалить.
Модуль 2
"Формулы"
Ссылки. Типы ссылок(относительные, абсолютные, смешанные). Виды представления ссылок. Именованные ссылки. Формулы в Microsoft Excel. Использование текста в формулах. Использование ссылок в формулах. Операторы. Арифметические операторы. Логические операторы. Оператор объединения 2-х строк текста в одну. Операторы ссылок. Выражения.
Ссылки
Типы ссылок (типы адресации):
Ссылки в Excel бывают 3-х типов:
- Относительные ссылки (пример: A1);
- Абсолютные ссылки (пример: $A$1);
- Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).
Знак $ (доллар) здесь никакого отношения к денежным единицам не имеет, это лишь способ указать Excel тип ссылки. Различия между разными типами ссылок можно увидеть, если потянуть за маркер автозаполнения активной ячейки или диапазона ячеек, содержащих формулу со ссылками.
Относительные ссылки
Если поставить в какой то ячейке знак «=», затем щелкнуть левой кнопкой мыши на какой то ячейке, Excel подставляет после «=» относительную ссылку на эту ячейку. Каждый раз, когда мы тянем за маркер автозаполнения или копируем Формулу, содержащую относительные ссылки, Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с новым положением ячейки.
Абсолютные ссылки
Смешанные ссылки
Виды представления ссылок
Есть два вида представления ссылок в Microsoft Excel:
Включить стиль ссылок R1C1 можно в настройках «Сервис» --> «Параметры» --> закладка «Общие» --> галочка «Стиль ссылок R1C1»
Именованные ссылки
Для упрощения работы с адресом ячейки или диапазона ячеек можно дать ему имя, и затем обращаться к ячейке или диапазону не по его адресу, а по символьному имени. Именованные ссылки можно использовать везде, где можно использовать то значение, на которое указывает ссылка.
Для создания именованной ссылки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ссылка может ссылаться на несвязный диапазон ячеек (выделенный с "Ctrl").
Для вставки именованной ссылки можно воспользоваться кнопкой со стрелкой вниз:
или нажать клавишу "F3", откроется следующее окно:
Пример использования: "=СУММ(tablica_1);"
Формулы в Microsoft Excel
Excel - программируемый табличный калькулятор. Все расчеты в Excel выполняют формулы. Формулой Excel считает все, что начинается со знака «=". Если в ячейке написать просто "1+1», Excel не будет вычислять это выражение. Однако, если написать «=1+1» и нажать Enter, в ячейке появится результат вычисления выражения - число 2. После нажатия Enter формула не пропадает, ее можно увидеть снова, если сделать двойной щелчок по ячейке, или если выделить ее и нажать F2 или просто нажать Ctrl+Апостроф. Также ее можно увидеть в панели инструментов «Строка формул», если опять же выделить ячейку. После двойного щелчка, нажатия F2 или после щелчка в строке формул, можно изменить формулу, и для завершения нажать клавишу Enter.
В формуле можно использовать различные типы операторов (арифметические и т. п.), текст, ссылки на ячейку или диапазон ячеек, круглые скобки, именованные диапазны. Естественно, в формулах соблюдается приоритет выполнения операций (умножение выполняется раньше сложения и т. п.). Для изменения порядка выполнения операций используются круглые скобки.
Использование текста в формулах
Использование ссылок в формулах
Для того, чтобы вставить в формулу адрес ячейки (ссылку на ячейку), не обязательно писать его вручную. Проще поставить знак «=», затем левой кнопкой щелкнуть на нужной ячейке или выделить нужный диапазон ячеек. При этом Excel подставит в формулу ссылку автоматически.
Операторы
Операторы в Excel бывают бинарные и унарные. Бинарные операторы работают 2 значениями. Например, оператор «*» умножает число слева от себя на число справа от себя. Если число слева или справа опустить, то Excel выдаст ошибку.
Унарные операторы оперируют одним значением. Пример унарных операторов: унарный «+» (ничего не делает), унарный «-» (меняет знак числа справа на противоположенный) или знак «%» (делит число слева на 100).
Арифметические операторы
Логические операторы
Оператор объединения 2-х строк текста в одну
Оператор «&» (амперсанд) служит для «склеивания» между собой двух текстовых строк. Например, в ячейке A1 текст «мама», в ячейке A2 текст «мыла раму». В A3 пишем формулу «=A1 & A2». В результате в ячейке A3 появится текст «мамамыла раму». Как видим, пробел между двумя строками автоматически не ставится. Чтобы вставить этот пробел, нужно изменить формулу вот так: «=A1 & " " & A2».
Операторы ссылок
Выражения
При записи формул приходится ссылаться на ячейки, в которых хранятся исходные значения. Каждая ячейка имеет адрес (ссылку). Возможны следующие способы адресации ячеек.
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?
| A | B | C |
| =A1+$B$1*A$2 | ||
| ? |
Ответ.С3: =B2+$B$1*B$2; B2: 7; C3: 21.
Задача. В ячейке B2 вычисляется сумма двух ячеек. Формулу из ячейки B2 скопировали в ячейку C3. Зависимость между ячейками изображена на рисунке.
Какая формула записана в ячейке 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!Итог.
Читайте также: