В предыдущих статьях я показывал, как применять формулы в Excel. Практически любая формула содержит переменные в виде адресов ячеек, которые можно использовать наравне с числами и встроенными функциями.
Адреса ячеек в Excel позволяют обращаться как к отдельной ячейке, так и к группе ячеек. Моя практика проведения курсов Excel показывает, что при изучении Excel с адресами ячеек у начинающих пользователей этой программы часто возникают сложности. Сейчас я покажу, как происходит адресация ячеек в Excel.
КАК УЗНАТЬ АДРЕС АКТИВНОЙ ЯЧЕЙКИ В EXCEL ЧЕРЕЗ ФУНКЦИЮ АДРЕС
Адреса ячеек обязательно вам понадобятся при использовании формул, в том числе и при копировании формул Excel.
Смотрите видео после статьи
видео может дополнять статью или содержать её полностью
Как адресуются ячейки в Excel
При создании обычных таблиц, не использующих средства автоматизации Excel, адреса у ячеек не используются, поскольку в этом просто нет необходимости. Однако в сложных таблицах применение различных встроенных инструментов Эксель позволяет сократить время на создание таблицы, сделать таблицу более удобной в использовании (пример смотрите тут), а также избегать ошибок при вводе большого количества данных.
На рисунке ниже приведена небольшая таблица Excel.
[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов
Поскольку весь документ Эксель представляет из себя одну большую таблицу, то каждая ячейка этой таблицы относится к определённому столбцу и определённой строке. Как правило, столбцы в Excel принято обозначать буквами латинского алфавита: A, B, C и так далее. Поскольку столбцов может быть много, а букв всего 26, то столбцы также могут именоваться сочетаниями букв: AA, AB, AC и так далее. Нумерация последовательная, то есть не может быть такого, что после столбца А идет С. Если вы такое увидели, то значит промежуточные столбцы просто скрыты.
Строки в Excel нумеруются всегда числами: 1,2,3 и так далее. Нумерация также последовательная и непрерывная. Строк в таблице может быть очень много.
Если на листе выделить одну ячейку, то будет подсвечена буква столбца и номер строки, к которым эта ячейка принадлежит.
Адрес ячейки обычно указывается в формате XY,
где X — буква столбца, а Y — номер строки.
Например, первая ячейка на листе будет иметь адрес А1, справа от неё В1 и так далее.
Наличие у ячеек адресов делает очень удобным обращение к ним в формулах, а также в некоторых других случаях. Допустим, нужно сложить два числа, одно из которых находится в ячейке А1, а второе А2, тогда формула для будет выглядеть как «=А1+А2». Буквы в адресах ячеек используются только английские!
Адресация объединённых ячеек
Если выполнить объединение нескольких ячеек в одну, то её адресом будет считаться адрес левой верхней ячейки группы до объединения. На картинке ниже показан образец адресации ячеек Excel после слияния.
[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов
Обращение к ячейкам по именам
Ячейке можно присвоить некоторое выбранное вами имя, по которому к ней в дальнейшем можно обращаться, например, в формулах. Чтобы присвоить имя ячейке, щёлкните на ней, после чего впишите имя в строку адреса ячейки, как показано на рисунке ниже.
[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов
Если ячейке А1 присвоить имя «первая», а ячейке В1 — «вторая», то формула для сложения будет выглядеть как «=первая + вторая». Адресация ячеек по именам может быть удобна в случае с большими таблицами, содержащими ячейки, к которым требуется часто обращаться — имя гораздо удобнее запомнить, чем простой адрес вроде А1 или DF400. Имя ячейки должно быть уникальным в пределах листа.
Замечу, однако, что при использовании произвольных имён диапазонов или отдельных ячеек в формулах, некоторые возможности будут недоступны. Типичный пример — использование символа $.
Подведём итоги
Адреса у ячеек в Excel используются, в основном, в формулах. Однако возможно и другое их применение. В любом случае адреса у ячеек нужны для того, чтобы к этим ячейкам можно было обратиться, что очень удобно при автоматизации ряда выполняемых в Эксель операций.
Научиться правильно применять адресацию ячеек в таблицах Excel Вы можете на онлайн курсах Excel. Про преимущества онлайн занятий Вы можете прочитать здесь.
Уникальная метка публикации: 6D5A7CB5-1A9D-7325-7D90-9467153F954E
Источник: //artemvm.info/information/uchebnye-stati/microsoft-office/zachem-nuzhny-adresa-yacheek-v-tablicah-excel/
Источник: artemvm.info
АДРЕСАЦИЯ ЯЧЕЕК В MS EXCEL
Для простоты использования в Microsoft Excel используется буквенно-цифровое обозначение адреса ячеек.
Адрес ячейки можно определить несколькими способами:
1 способ: в строке формул, в левой ее части отражается адрес текущей ячейки
2 способ: в окне рабочей таблицы буква столбца и цифра строки, на пересечении которых находится ячейка, выделены цветом
3 способ:На самой рабочей таблице текущая ячейка выделена табличным курсором ( ячейка обрамлена черным прямоугольником)
Относительные ссылки Относительной называется такая адресация, которая при копировании в составе формулы в другую ячейку автоматически изменяется
Относительной называется такая адресация, которая при копировании в составе формулы в другую ячейку автоматически изменяется .
Абсолютные ссылки Абсолютной называется адресация не подлежащая изменению при копировании формулы
Абсолютной называется адресация не подлежащая изменению при копировании формулы. Абсолютные ссылки в формулах используются для указания фиксированного адреса ячейки. В абсолютных ссылках перед неизменяемым именем ставится знак доллара.
Смешанные ссылки Знак $ ставится только перед именем столбца (буквенная часть)
Знак $ ставится только перед именем столбца (буквенная часть)
Знак $ ставится только перед номером строки (числовая часть)
ПРАКТИЧЕСКАЯ РАБОТА Для выполнения практической работы откройте файл:
РЕШЕНИЕ ЗАДАЧ Относительная адресация
Абсолютная и смешанная адресация
В электронной таблице значение формулы =
1. В электронной таблице значение формулы = СУММ(А1:А3) равно 8. Чему равно значение ячейки А4, если значение формулы =СРЗНАЧ(А1:А4) равно 3?
1). 5 2). 2 3). 8 4). 4
В электронной таблице значение формулы =СУММ (А1:А4) равно 13, а значение формулы =СРЗНАЧ(А1:А5) равно 3
2. В электронной таблице значение формулы =СУММ (А1:А4) равно 13, а значение формулы =СРЗНАЧ(А1:А5) равно 3. Чему равно значение формулы = СУММ(А1:А5)?
1). 15 2). 16 3). 24 4). 28
При работе с электронной таблицей в ячейку
3.При работе с электронной таблицей в ячейку А1 записана формула =С3+$С4. Какой вид приобретет формула после того, как ячейку А1 скопируют в В1?
1). =D4+$D2 2). =D3+$D1 3). =D3+$C4 4). =C4+$C2
При копировании произошло смещение вправо поэтому изменяются только буквы: адрес С3 стал D3, однако адрес $C4 не изменился т.к. у него абсолютная адресация (знак $ перед С). Получаем результат: D3+$C4
При работе с электронной таблицей в ячейку
4.При работе с электронной таблицей в ячейку В1 записана формула =$С3-E$3. Какой вид приобретет формула после того, как ячейку B1 скопируют в C2?
При копировании формулы произошло смещение вправо и вниз, При таком копировании изменяются и буквы адреса, и цифры, если перед ними не стоит знак $. Адрес $С3 стал $C4, адрес Е$3 изменился на F$3 (в адресах использована смешанная адресация: знак $ перед С и перед цифрой 3).
Получаем результат: $C4-F$3
Дан фрагмент электронной таблицы :
5. Дан фрагмент электронной таблицы :
Чему станет равно значение ячейки С2, если в нее скопировать формулу из ячейки С1
1). 40 2). 50 3). 20 4). 30
В электронной таблице значение формулы =СУММ (B1:B5) равно 24
1. В электронной таблице значение формулы =СУММ (B1:B5) равно 24. Чему равно значение ячейки В6, если значение формулы = СРЗНАЧ(B1:B6) равно 4?
1). 1 2). 2 3). 0 4). 4
Для самостоятельного решения
2. В электронной таблице значение формулы =СУММ (А1:В1) равно 12, а значение формулы =СУММ(D1:E1) равно 7. Чему равно значение ячейки С1, если значение формулы =СРЗНАЧ(A1:E1) равно 5?
1). 6 2). 2 3). 3 4). 7
3. В электронной таблице значение формулы =СУММ (В2:В4) равно 18, а значение формулы =СУММ(В4:В6) равно 14. Чему равно значение ячейки В4, если значение формулы =СРЗНАЧ(В2:В6) равно 5?
1). 5 2). 7 3). 8 4). 4
Для самостоятельного решения 4
Для самостоятельного решения
4. При работе с электронной таблицей в ячейку А1 записана формула =2*$B$4-$C1. Какой вид приобретет формула после того, как ячейку А1 скопируют в ячейку В3?
5. Дан фрагмент электронной таблицы. Чему станет равным значение ячейки D3, если в нее скопировать формулу из ячейки С2?
Источник: znanio.ru
Адрес ячейки в excel
Как прописать зависимость адреса ячейки от числовых значений
О том, как записать значение координат ячейки.
Для записи координат (адреса) ячейки в «Эксель» используется функция «АДРЕС».
Сама по себе эта функция используется довольно редко , но в тех случаях когда нужно в формуле прописать меняющийся в зависимости от значения адрес ячейки эта функция незаменима.
Пример записи функции «Адрес» :
- Установить курсор в ячейку или в формулу;
- Выбрать в мастере функций «Адрес»;
- В появившемся окне выбрать номер строки (число обозначающее, какая по счету строка, как бы ось Y);
- Выбрать номер столбца (число обозначающее, какой по счету столбец, как бы ось X);
- Тип ссылки – это число от 1 до 4 (определяет в каком формате писать ссылку: 1 или опущен =Абсолютный адрес ячейки. Точный адрес ячейки в формуле, ссылающийся на данную ячейку независимо от положения ячейки с формулой. Абсолютный адрес ячейки имеет формат $A$1.; 2 = Строка абсолютная, а столбец относительный; 3 = Относительная строка и абсолютный столбец; 4 = полностью относительный адрес.)
- Значение А1 – это аргумент отвечающий за формат ссылки типа A1 или R1C Этот аргумент может иметь значение 1(«ИСТИНА») и 0 (ЛОЖЬ).
- Так же можно указать имя листа, на котором расположена ячейка.
- Нажать «ENTER» или «OK»
Функция АДРЕС() в MS EXCEL
Функция АДРЕС() , английский вариант ADDRESS(), возвращает адрес ячейки на листе, для которой указаны номера строки и столбца. Например, формула АДРЕС(2;3) возвращает значение $C$2.
Функция АДРЕС() возвращает текстовое значение в виде адреса ячейки.
Синтаксис функции
АДРЕС(номер_строки, номер_столбца, [тип_ссылки], [a1], [имя_листа])
Номер_строки Обязательный аргумент. Номер строки, используемый в ссылке на ячейку.
Номер_столбца Обязательный аргумент. Номер столбца, используемый в ссылке на ячейку.
Последние 3 аргумента являются необязательными.
[Тип_ссылки] Задает тип возвращаемой ссылки:
- 1 или опущен: абсолютная ссылка, например $D$7
- 2: абсолютная ссылка на строку; относительная ссылка на столбец, например D$7
- 3: относительная ссылка на строку; абсолютная ссылка на столбец, например $D7
- 4: относительная ссылка, например D7
[а1] Логическое значение, которое определяет тип ссылок: А1 или R1C1. При использовании ссылок типа А1 столбцы обозначаются буквами, а строки — цифрами, например D7 . При использовании ссылок типа R1C1 и столбцы, и строки обозначаются цифрами, например R7C5 (R означает ROW — строка, С означает COLUMN — столбец). Если аргумент А1 имеет значение ИСТИНА или 1 или опущен, то функция АДРЕС() возвращает ссылку типа А1; если этот аргумент имеет значение ЛОЖЬ (или 0), функция АДРЕС() возвращает ссылку типа R1C1.
Чтобы изменить тип ссылок, используемый Microsoft Excel, нажмите кнопку Microsoft Office, затем нажмите кнопку Параметры Excel (внизу окна) и выберите пункт Формулы. В группе Работа с формулами установите или снимите флажок Стиль ссылок R1C1.
[Имя_листа] Необязательный аргумент. Текстовое значение, определяющее имя листа, которое используется для формирования внешней ссылки. Например, формула =АДРЕС(1;1;;;»Лист2″) возвращает значение Лист2!$A$1.
Как видно из рисунка ниже (см. файл примера ) функция АДРЕС() возвращает адрес ячейки во всевозможных форматах.
Чаще всего адрес ячейки требуется, чтобы вывести значение ячейки. Для этого используется другая функция ДВССЫЛ() .
Формула =ДВССЫЛ(АДРЕС(6;5)) просто выведет значение из 6-й строки 5 столбца (Е). Эта формула эквивалентна формуле =Е6 .
Возникает вопрос: «Зачем весь этот огород с функцией АДРЕС() ?». Дело в том, что существуют определенные задачи, в которых использование функции АДРЕС() очень удобно, например Транспонирование таблиц или Нумерация столбцов буквами или Поиск позиции ТЕКСТа с выводом значения из соседнего столбца.
Читать еще: Excel найти объединенные ячейки в excel
Адресация ячеек в Excel
Неизвестный Excel
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. Если снимите галочку, то будет использоваться формат “буква-цифра”.
Компьютер для чайника
В основе этой книги лежит курс лекций, специально разработанный для обучения базовым навыкам работы на компьютере пользователей одного из промышленных предприятий. Практически все эти пользователи работали с компьютером не один год но, как показала практика, большинство из них не знали элементарных вещей. Подробнее.
Читать еще: Excel пакет анализа
Примеры функции АДРЕС для получения адреса ячейки листа Excel
Функция АДРЕС возвращает адрес определенной ячейки (текстовое значение), на которую указывают номера столбца и строки. К примеру, в результате выполнения функции =АДРЕС(5;7) будет выведено значение $G$5.
Примечание: наличие символов «$» в адресе ячейки $G$5 свидетельствует о том, что ссылка на данную ячейку является абсолютной, то есть не меняется при копировании данных.
Функция АДРЕС в Excel: описание особенностей синтаксиса
Функция АДРЕС имеет следующую синтаксическую запись:
Первые два аргумента данной функции являются обязательными для заполнения.
- Номер_строки – числовое значение, соответствующее номеру строки, в которой находится требуемая ячейка;
- Номер_столбца – числовое значение, которое соответствует номеру столбца, в котором расположена искомая ячейка;
- [тип_ссылки] – число из диапазона от 1 до 4, соответствующее одному из типов возвращаемой ссылки на ячейку:
- абсолютная на всю ячейку, например — $A$4
- абсолютная только на строку, например — A$4;
- абсолютная только на столбец, например — $A4;
- относительная на всю ячейку, например A4.
- [a1] – логическое значение, определяющее один из двух типов ссылок: A1 либо R1C1;
- [имя_листа] – текстовое значение, которое определяет имя листа в документе Excel. Используется для создания внешних ссылок.
- Ссылки типа R1C1 используются для цифрового обозначения столбцов и строк. Для возвращения ссылок такого типа в качестве параметра a1 должно быть явно указано логическое значение ЛОЖЬ или соответствующее числовое значение 0.
- Стиль ссылок в Excel может быть изменен путем установки/снятия флажка пункта меню «Стиль ссылок R1C1», который находится в «Файл – Параметры – Формулы – Работа с Формулами».
- Если требуется ссылка на ячейку, которая находится в другом листе данного документа Excel, полезно использовать параметр [имя_листа], который принимает текстовое значение, соответствующее названию требуемого листа, например «Лист7».
Примеры использования функции АДРЕС в Excel
Пример 1. В таблице Excel содержится ячейка, отображающая динамически изменяемые данные в зависимости от определенных условий. Для работы с актуальными данными в таблице, которая находится на другом листе документа требуется получить ссылку на данную ячейку.
На листе «Курсы» создана таблица с актуальными курсами валют:
На отдельном листе «Цены» создана таблица с товарами, отображающая стоимость в долларах США (USD):
В ячейку D3 поместим ссылку на ячейку таблицы, находящейся на листе «Курсы», в которой содержится информация о курсе валюты USD. Для этого введем следующую формулу: =АДРЕС(3;2;1;1;»Курсы»).
- 3 – номер строки, в которой содержится искомая ячейка;
- 2 – номер столбца с искомой ячейкой;
- 1 – тип ссылки – абсолютная;
- 1 – выбор стиля ссылок с буквенно-цифровой записью;
- «Курсы» — название листа, на котором находится таблица с искомой ячейкой.
Для расчета стоимости в рублях используем формулу: =B3*ДВССЫЛ(D3).
Функция ДВССЫЛ необходима для получения числового значения, хранимого в ячейке, на которую указывает ссылка. В результате вычислений для остальных товаров получим следующую таблицу:
Как получить адрес ссылки на ячейку Excel?
Пример 2. В таблице содержатся данные о цене товаров, отсортированные в порядке возрастания стоимости. Необходимо получить ссылки на ячейки с минимальной и максимальной стоимостью товаров соответственно.
Исходная таблица имеет следующий вид:
Для получения ссылки на ячейку с минимальной стоимостью товара используем формулу:
Функция АДРЕС принимает следующие параметры:
- число, соответствующее номеру строки с минимальным значением цены (функция МИН выполняет поиск минимального значения и возвращает его, функция ПОИСКПОЗ находит позицию ячейки, содержащей минимальное значение цены. К полученному значению добавлено 2, поскольку ПОИСКПОЗ осуществляет поиск относительно диапазона выбранных ячеек.
- 2 – номер столбца, в котором находится искомая ячейка.
Читать еще: Если множественное в excel
Аналогичным способом получаем ссылку на ячейку с максимальной ценой товара. В результате получим:
Адрес по номерам строк и столбцов листа Excel в стиле R1C1
Пример 3. В таблице содержится ячейка, данные из которой используются в другом программном продукте. Для обеспечения совместимости необходимо предоставить ссылку на нее в виде R1C1.
Исходная таблица имеет следующий вид:
Исходная таблица.» src=»https://exceltable.com/funkcii-excel/images/funkcii-excel78-9.png» >
Для получения ссылки на ячейку B6 используем следующую формулу: =АДРЕС(6;2;1;0).
- 6 – номер строки искомой ячейки;
- 2 – номер столбца, в котором содержится ячейка;
- 1 – тип ссылки (абсолютная);
- 0 – указание на стиль R1C1.
В результате получим ссылку:
Примечание: при использовании стиля R1C1 запись абсолютной ссылки не содержит знака «$». Чтобы отличать абсолютные и относительные ссылки используются квадратные скобки «[]». Например, если в данном примере в качестве параметра тип_ссылки указать число 4, ссылка на ячейку примет следующий вид:
Так выглядит абсолютный тип ссылок по строкам и столбцам при использовании стиля R1C1.
АДРЕС (функция АДРЕС)
В этой статье описаны синтаксис формулы и использование функции АДРЕС в Microsoft Excel. Чтобы узнать больше о работе с почтовыми адресами и создании почтовых наклеек, см. по ссылкам в разделе См. также.
Функцию АДРЕС можно использовать для получения адреса ячейки на листе по номерам строки и столбца. Например, функция АДРЕС(2;3) возвращает значение $C$2. Еще один пример: функция АДРЕС(77;300) возвращает значение $KN$77. Чтобы передать функции АДРЕС номера строки и столбца в качестве ее аргументов, можно использовать другие функции (например, функции СТРОКА и СТОЛБЕЦ).
Аргументы функции АДРЕС указаны ниже.
Номер_строки Обязательный аргумент. Номер строки, используемый в ссылке на ячейку.
Номер_столбца Обязательный аргумент. Номер столбца, используемый в ссылке на ячейку.
Тип_ссылки Необязательный аргумент. Задает тип возвращаемой ссылки.
Возвращаемый тип ссылки
Абсолютная строка; относительный столбец
Относительная строка; абсолютный столбец
A1 Необязательный аргумент. Логическое значение, которое определяет тип ссылок: А1 или R1C1. При использовании ссылок типа А1 столбцы обозначаются буквами, а строки — цифрами. При использовании ссылок типа R1C1 и столбцы, и строки обозначаются цифрами. Если аргумент А1 имеет значение ИСТИНА или опущен, то функция АДРЕС возвращает ссылку типа А1; если этот аргумент имеет значение ЛОЖЬ, функция АДРЕС возвращает ссылку типа R1C1.
Примечание: Чтобы изменить тип ссылок, используемый Microsoft Excel, откройте вкладку Файл, а затем нажмите кнопку Параметры и выберите пункт Формулы. В группе Работа с формулами установите или снимите флажок Стиль ссылок R1C1.
Имя_листа Необязательный аргумент. Текстовое значение, определяющее имя листа, которое используется для формирования внешней ссылки. Например, формула =АДРЕС(1;1;;;»Лист2″) возвращает значение Лист2!$A$1. Если аргумент имя_листа отсутствует, имя листа не используется, и адрес, возвращаемый функцией, ссылается на ячейку текущего листа.
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Источник: lengto.ru