Функция excel адрес: Функция АДРЕС() в EXCEL. Примеры и описание

Функция АДРЕС() в 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. Примеры и описание

Функция

ЯЧЕЙКА(

)

, английская версия CELL()

,

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

Синтаксис функции

ЯЧЕЙКА()

ЯЧЕЙКА(тип_сведений, [ссылка])

тип_сведений

— Текстовое значение, задающее требуемый тип сведений о ячейке. В приведенном ниже списке указаны возможные значения аргумента

тип_сведений

и соответствующие результаты.

ссылка —

Необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе

тип_сведений

, возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция

ЯЧЕЙКА()

возвращает сведения только для левой верхней ячейки диапазона.

Тип_ сведений	Возвращаемое значение
«адрес»	Ссылка на первую ячейку в аргументе «ссылка» в виде текстовой строки.
«столбец»	Номер столбца ячейки в аргументе «ссылка».
«цвет»	1, если ячейка изменяет цвет при выводе отрицательных значений; во всех остальных случаях — 0 (ноль).
«содержимое»	Значение левой верхней ячейки в ссылке; не формула.
«имяфайла»	Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка («»).
«формат»	Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. ) — тексту, выровненному по центру, обратная косая черта (\) — тексту с заполнением, пустой текст («») — любому другому содержимому ячейки.
«защита»	0, если ячейка разблокирована, и 1, если ячейка заблокирована.
«строка»	Номер строки ячейки в аргументе «ссылка».
«тип»	Текстовое значение, соответствующее типу данных в ячейке. Значение «b» соответствует пустой ячейке, «l» — текстовой константе в ячейке, «v» — любому другому значению.
«ширина»	Ширина столбца ячейки, округленная до целого числа. Единица измерения равна ширине одного знака для шрифта стандартного размера.

Использование функции

В

файле примера

приведены основные примеры использования функции:

Большинство сведений об ячейке касаются ее формата. Альтернативным источником информации такого рода может случить только VBA.

Самые интересные аргументы это —

адрес

и

имяфайла

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

Нахождение имени текущей книги

.

Обратите внимание, что если в одном экземпляре MS EXCEL (см. примечание ниже) открыто несколько книг, то функция

ЯЧЕЙКА()

с аргументами

адрес

и

имяфайла

, будет отображать имя того файла, с который Вы изменяли последним. Например, открыто 2 книги в одном окне MS EXCEL:

Базаданных.xlsx

и

Отчет.xlsx.

В книге

Базаданных.xlsx

имеется формула

=ЯЧЕЙКА(«имяфайла»)

для отображения в ячейке имени текущего файла, т.е.

Базаданных.xlsx

(с полным путем и с указанием листа, на котором расположена эта формула). Если перейти в окно книги

Отчет.xlsx

и поменять, например, содержимое ячейки, то вернувшись в окно книги

Базаданных. xlsx

(

CTRL+TAB

) увидим, что в ячейке с формулой

=ЯЧЕЙКА(«имяфайла»)

содержится имя

Отчет.xlsx.

Это может быть источником ошибки. Хорошая новость в том, что при открытии книги функция пересчитывает свое значение (также пересчитать книгу можно нажав клавишу

F9

). При открытии файлов в разных экземплярах MS EXCEL — подобного эффекта не возникает — формула

=ЯЧЕЙКА(«имяфайла»)

будет возвращать имя файла, в ячейку которого эта формула введена.

Примечание

: Открыть несколько книг EXCEL можно в одном окне MS EXCEL (в одном экземпляре MS EXCEL) или в нескольких. Обычно книги открываются в одном экземпляре MS EXCEL (когда Вы просто открываете их подряд из Проводника Windows или через Кнопку Офис в окне MS EXCEL). Второй экземпляр MS EXCEL можно открыть запустив файл EXCEL.EXE, например через меню Пуск. Чтобы убедиться, что файлы открыты в одном экземпляре MS EXCEL нажимайте последовательно сочетание клавиш

CTRL+TAB

— будут отображаться все окна Книг, которые открыты в данном окне MS EXCEL. Для книг, открытых в разных окнах MS EXCEL (экземплярах MS EXCEL) это сочетание клавиш не работает. Удобно открывать в разных экземплярах Книги, вычисления в которых занимают продолжительное время. При изменении формул MS EXCEL пересчитывает только книги открытые в текущем экземпляре.

Другие возможности функции

ЯЧЕЙКА()

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

ЕТЕКСТ()

,

ЕЧИСЛО()

,

СТОЛБЕЦ()

и др.

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

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

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

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

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

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

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

Лист2!В3

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

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

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

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

БЦ

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

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

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

RxCy

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

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

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

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

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

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

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

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

Абсолютная адресация в Excel: 2 проверенных способа

Как известно, в таблицах Excel существует два вида адресации: относительная и абсолютная. В первом случае ссылка изменяется по направлению копирования на относительную величину сдвига, а во втором — является фиксированной и при копировании остается неизменной. Но по умолчанию все адреса в Экселе являются абсолютными. В то же время, довольно часто присутствует необходимость использовать абсолютную (фиксированную) адресацию. Давайте узнаем, какими способами это можно осуществить.

Применение абсолютной адресации

Абсолютная адресация нам может понадобиться, например, в том случае, когда мы копируем формулу, одна часть которой состоит из переменной, отображаемой в ряду чисел, а вторая имеет постоянное значение. То есть, данное число играет роль неизменного коэффициента, с которым нужно провести определенную операцию (умножение, деление и т.д.) всему ряду переменных чисел.

В Excel существует два способа задать фиксированную адресацию: путем формирования абсолютной ссылки и с помощью функции ДВССЫЛ. Давайте рассмотрим каждый из указанных способов подробно.

Способ 1: абсолютная ссылка

Безусловно, самым известным и часто применяемым способом создать абсолютную адресацию является применение абсолютных ссылок. Абсолютные ссылки имеют отличие не только функциональное, но и синтаксическое. Относительный адрес имеет такой синтаксис:

=A1

У фиксированного адреса перед значением координат устанавливается знак доллара:

=$A$1

Знак доллара можно ввести вручную. Для этого нужно установить курсор перед первым значением координат адреса (по горизонтали), находящегося в ячейке или в строке формул. Далее, в англоязычной раскладке клавиатуры следует кликнуть по клавише «4» в верхнем регистре (с зажатой клавишей «Shift»). Именно там расположен символ доллара. Затем нужно ту же процедуру проделать и с координатами по вертикали.

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

Теперь давайте рассмотрим, как применяется на практике абсолютная адресация путем использования абсолютных ссылок.

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

1. Итак, в первую ячейку столбца «Заработная плата» вводим формулу умножения ставки соответствующего работника на коэффициент. В нашем случае эта формула имеет такой вид:

   =C4*G3

2. Чтобы рассчитать готовый результат, щелкаем по клавише Enter на клавиатуре. Итог выводится в ячейку, содержащую формулу.
3. Мы рассчитали значение зарплаты для первого сотрудника. Теперь нам нужно это сделать для всех остальных строк. Конечно, операцию можно записать в каждую ячейку столбца «Заработная плата» вручную, вводя аналогичную формулу с поправкой на смещение, но у нас стоит задача, как можно быстрее выполнить вычисления, а ручной ввод займет большое количество времени. Да и зачем тратить усилия на ручной ввод, если формулу можно попросту скопировать в другие ячейки?

   Для копирования формулы применим такой инструмент, как маркер заполнения. Становимся курсором в нижний правый угол ячейки, где она содержится. При этом сам курсор должен преобразоваться в этот самый маркер заполнения в виде крестика. Зажимаем левую кнопку мыши и тянем курсор вниз до конца таблицы.

4. Но, как видим, вместо корректного расчета заработной платы для остальных сотрудников, мы получили одни нули.
5. Смотрим, в чем причина такого результата. Для этого выделяем вторую ячейку в столбце «Заработная плата». В строке формул отображается соответствующее данной ячейке выражение. Как видим, первый множитель (C5) соответствует ставке того работника, зарплату которого мы рассчитываем. Смещение координат по сравнению с предыдущей ячейкой произошло из-за свойства относительности. Впрочем, в конкретно данном случае это нам и нужно. Благодаря этому первым множителем стала ставка именно нужного нам работника. Но смещение координат произошло и со вторым множителем. И теперь его адрес ссылается не на коэффициент (1,28), а на пустую ячейку, расположенную ниже.

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

6. Для исправления ситуации нам нужно изменить адресацию второго множителя с относительной на фиксированную. Для этого возвращаемся к первой ячейке столбца «Заработная плата», выделив её. Далее перемещаемся в строку формул, где отобразилось нужное нам выражение. Выделяем курсором второй множитель (G3) и жмем на функциональную клавишу на клавиатуре.
7. Как видим, около координат второго множителя появился знак доллара, а это, как мы помним, является атрибутом абсолютной адресации. Чтобы вывести результат на экран жмем на клавишу Enter.
8. Теперь, как и ранее вызываем маркер заполнения, установив курсор в правый нижний угол первого элемента столбца «Заработная плата». Зажимаем левую кнопку мыши и тянем его вниз.
9. Как видим, в данном случае расчет был проведен верно и сумма заработной платы для всех работников предприятия рассчитана корректно.
10. Проверим, как была скопирована формула. Для этого выделяем второй элемент столбца «Заработная плата». Смотрим на выражение, расположенное в строке формул. Как видим, координаты первого множителя (C5), который по прежнему является относительным, сдвинулись по сравнению с предыдущей ячейкой на один пункт вниз по вертикали. Зато второй множитель ($G$3), адресацию в котором мы сделали фиксированной, остался неизменным.

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

=A$1

Этот адрес тоже считается смешанным:

=$A1

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

Посмотрим, как такую смешанную ссылку можно применить на практике на примере все той же таблицы заработной платы сотрудников предприятия.

1. Как видим, ранее мы сделали так, что все координаты второго множителя имеют абсолютную адресацию. Но давайте разберемся, обязательно ли в этом случае оба значения должны быть фиксированными? Как видим, при копировании происходит смещение по вертикали, а по горизонтали координаты остаются неизменными. Поэтому вполне можно применить абсолютную адресацию только к координатам строки, а координаты столбца оставить такими, каковыми они являются по умолчанию – относительными.

   Выделяем первый элемент столбца «Заработная плата» и в строке формул выполняем вышеуказанную манипуляцию. Получаем формулу следующего вида:

   =C4*G$3

   Как видим, фиксированная адресация во втором множителе применяется только по отношению к координатам строки. Для вывода результата в ячейку щелкаем по кнопке Enter.

2. После этого посредством маркера заполнения копируем данную формулу на диапазон ячеек, который расположен ниже. Как видим, расчет заработной платы по всем сотрудникам выполнен корректно.
3. Смотрим, как отображается скопированная формула во второй ячейке столбца, над которым мы выполняли манипуляцию. Как можно наблюдать в строке формул, после выделения данного элемента листа, несмотря на то, что абсолютную адресацию у второго множителя имели только координаты строк, смещение координат столбца не произошло. Это связано с тем, что мы выполняли копирование не по горизонтали, а по вертикали. Если бы мы выполнили копирование по горизонтали, то в аналогичном случае, наоборот, пришлось бы делать фиксированную адресацию координат столбцов, а для строк эта процедура была бы необязательной.

Урок: Абсолютные и относительные ссылки в Экселе

Способ 2: функция ДВССЫЛ

Вторым способом организовать абсолютную адресацию в таблице Excel является применение оператора ДВССЫЛ. Указанная функция относится к группе встроенных операторов «Ссылки и массивы». Её задачей является формирование ссылки на указанную ячейку с выводом результата в тот элемент листа, в котором находится сам оператор. При этом ссылка прикрепляется к координатам ещё крепче, чем при использовании знака доллара. Поэтому иногда принято называть ссылки с использованием ДВССЫЛ «суперабсолютными». Этот оператор имеет следующий синтаксис:

=ДВССЫЛ(ссылка_на_ячейку;[a1])

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

Аргумент «Ссылка на ячейку» является ссылкой на элемент листа Excel в текстовом виде. То есть, это обычная ссылка, но заключенная в кавычки. Именно это и позволяет обеспечить свойства абсолютной адресации.

Аргумент «a1» — необязательный и используется в редких случаях. Его применение необходимо только тогда, когда пользователь выбирает альтернативный вариант адресации, а не обычное использование координат по типу «A1» (столбцы имеют буквенное обозначение, а строки — цифровое). Альтернативный вариант подразумевает использование стиля «R1C1», в котором столбцы, как и строки, обозначаются цифрами. Переключиться в данный режим работы можно через окно параметров Excel. Тогда, применяя оператор ДВССЫЛ, в качестве аргумента «a1» следует указать значение «ЛОЖЬ». Если вы работает в обычном режиме отображения ссылок, как и большинство других пользователей, то в качестве аргумента «a1» можно указать значение «ИСТИНА». Впрочем, данное значение подразумевается по умолчанию, поэтому намного проще вообще в данном случае аргумент «a1» не указывать.

Взглянем, как будет работать абсолютная адресация, организованная при помощи функции ДВССЫЛ, на примере нашей таблицы заработной платы.

1. Производим выделение первого элемента столбца «Заработная плата». Ставим знак «=». Как помним, первый множитель в указанной формуле вычисления зарплаты должен быть представлен относительным адресом. Поэтому просто кликаем на ячейку, содержащую соответствующее значение оклада (C4). Вслед за тем, как её адрес отобразился в элементе для вывода результата, жмем на кнопку «умножить» (*) на клавиатуре. Затем нам нужно перейти к использованию оператора ДВССЫЛ. Выполняем щелчок по иконке «Вставить функцию».
2. В открывшемся окне Мастера функций переходим в категорию «Ссылки и массивы». Среди представленного списка названий выделяем наименование «ДВССЫЛ». Затем щелкаем по кнопке «OK».
3. Производится активация окошка аргументов оператора ДВССЫЛ. Оно состоит из двух полей, которые соответствуют аргументам этой функции.

   Ставим курсор в поле «Ссылка на ячейку». Просто кликаем по тому элементу листа, в котором находится коэффициент для расчета зарплаты (G3). Адрес тут же появится в поле окна аргументов. Если бы мы имели дело с обычной функцией, то на этом введение адреса можно было бы считать завершенным, но мы используем функцию ДВССЫЛ. Как мы помним, адреса в ней должны иметь вид текста. Поэтому оборачиваем координаты, которые расположись в поле окна, кавычками.

   Так как мы работаем в стандартном режиме отображения координат, то поле «A1» оставляем незаполненным. Щелкаем по кнопке «OK».

4. Приложение выполняет вычисление и выводит результат в элемент листа, содержащий формулу.
5. Теперь производим копирование данной формулы во все остальные ячейки столбца «Заработная плата» посредством маркера заполнения, как мы это делали ранее. Как видим, все результаты были рассчитаны верно.
6. Посмотрим, как отображается формула в одной из ячеек, куда она была скопирована. Выделяем второй элемент столбца и смотрим на строку формул. Как видим, первый множитель, являющийся относительной ссылкой, изменил свои координаты. В то же время, аргумент второго множителя, который представлен функцией ДВССЫЛ, остался неизменным. В данном случае была использована методика фиксированной адресации.

Урок: Оператор ДВССЫЛ в Экселе

Абсолютную адресацию в таблицах Excel можно обеспечить двумя способами: использование функции ДВССЫЛ и применение абсолютных ссылок. При этом функция обеспечивает более жесткую привязку к адресу. Частично абсолютную адресацию можно также применять при использовании смешанных ссылок.

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

39. Ссылки. Встроенные функции ms Excel

MS
Excel 2003 содержит 320 встроенных функций.
Для удобства функции в Excel 2003 разбиты
по категориям (матем, финансовые,
статистические и т.д.).

Обращение
к каждой функции состоит из двух частей:
имени функции и аргументов в круглых
скобках.

Встроенные
функции

Математические:

КОРЕНЬ(…)
Вычисление квадратного корня

ABS(…)
Вычисление абсолютного значения
(модуля) числа

ЦЕЛОЕ(…)
Округление числа или результата
выражения, указанного в скобках, до
ближайшего меньшего (!) целого

ПИ(
) * Значение математической константы
«ПИ» (3,1415926…)

НОД(…)
Наибольший общий делитель нескольких
чисел

НОК(…)
Наименьшее общее кратное нескольких
чисел

СЛЧИС(
) * Вычисление случайного числа в
промежутке между 0 и 1

Статистические

МИН(…)
Определение минимального из указанных
чисел

МАКС(…)
Определение максимального из указанных
чисел

СРЕДНЕЕ(…)
Определение среднего значения указанных
чисел

СУММ(…)
Определение суммы указанных чисел

Дата
и время

СЕГОДНЯ
( ) * Значение сегодняшней даты в виде
даты в числовом формате

МЕСЯЦ(дата)
Вычисление порядкового номера месяца
в году по указанной дате

ДЕНЬ(дата)
Вычисление порядкового номера дня в
месяце по указанной дате

ГОД(дата)
Вычисление года по указанной дате

Логические

И(условие1;
условие2;…)

Вычисление
значения (ИСТИНА, ЛОЖЬ) логической
операции И

ИЛИ(условие1;
условие2;…)

Вычисление
значения (ИСТИНА, ЛОЖЬ) логической
операции ИЛИ

ЕСЛИ(условие;
знач_ИСТИНА; знач_ЛОЖЬ)

Вычисление
значения в зависимости от выполнения
условия.

Виды
ссылок:

Относительная
(С3)

При
копировании: Меняется в соответствии
с новым положением ячейки

Технология
ввода: Щелкнуть в ячейке

Абсолютная
($C$3)

При
копировании: Не меняется

Технология
ввода: Щелкнуть в ячейке и нажимать F4
до преобразования адреса к нужному
виду

Смешанная

При
копировании: (С$3) Не меняется номер
строки

При
копировании: ($C3) Не меняется имя столбца

Относительные
ссылки в Excel

Использование
относительных ссылок в Excel аналогично
указанию направления движения по улице
— «идти три квартала на север, затем
два квартала на запад». Следование
этим инструкциям из различных начальных
мест будет приводить в разные места
назначения.

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

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

Абсолютные
ссылки в Excel

В
Excel абсолютная ссылка на ячейку или
область ячеек будет всегда ссылаться
на один и тот же адрес строки и столбца.
При сравнении с направлениями улиц это
будет примерно следующее: «Идите на
пересечение Арбата и Бульварного
кольца». Вне зависимости от места
старта это будет приводить к одному и
тому же месту. Если формула требует,
чтобы адрес ячейки оставался неизменным
при копировании, то должна использоваться
абсолютная ссылка (формат записи $А$1).
Например, когда формула вычисляет доли
от общей суммы, ссылка на ячейку,
содержащую общую сумму, не должна
изменяться при копировании.

Как создать пользовательские функции Excel

Microsoft Excel Pack поставляется с множеством предопределенных функций, которые выполняют для нас максимальную работу. Нам больше не нужно больше функций, кроме встроенных в большинстве случаев. Но что, если вам нужны некоторые функции, которые не были предоставлены какой-либо предварительно определенной функцией Excel?

Microsoft Excel позволяет нам создавать пользовательские функции Excel или пользовательские функции , используя VBA . Мы можем создавать пользовательские функции Excel с нужной нам функциональностью, и к ним можно обращаться в листе Excel в виде обычных функций Excel, используя «=» и имя функции. Я проведу вас через шаги по созданию пользовательских функций Excel с использованием VBA.

Создание пользовательских функций Excel

Поскольку мы будем создавать пользовательскую функцию Excel с использованием VBA, нам нужно сначала включить вкладку «Разработчик». По умолчанию он не включен, и мы можем включить его. Откройте лист Excel и нажмите кнопку Excel, а затем нажмите «Параметры Excel». Затем установите флажок рядом с Показать вкладку разработчика на ленте .

Теперь, чтобы открыть редактор Visual Basic, нажмите на вкладку «Разработчик» и щелкните значок «Visual Basic», чтобы запустить редактор Visual Basic.

Вы даже можете использовать сочетание клавиш « Alt + F11 » для запуска редактора Visual Basic. Если вы используете эту комбинацию клавиш, нет необходимости включать вкладку «Разработчик».

Теперь все готово для создания пользовательской функции Excel. Щелкните правой кнопкой мыши «Объекты Microsoft Excel», нажмите «Вставить», а затем нажмите «Модуль».

Он открывает простое окно, в котором можно написать код.

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

 Функция myFunction (arguments) возвращает тип 

 myFunction = some_calculation 

 End Function 

Здесь нет оператора «Return», как у нас с обычными языками программирования.

Вставьте ваш код в открывшемся окне. Например, я создам функцию «FeesCalculate», которая вычисляет «8%» от значения, предоставленного функции. Я использовал тип возвращаемого значения как «Double», поскольку значение также может быть в десятичных числах. Вы можете видеть, что мой код соответствует синтаксису VBA.

Теперь пришло время сохранить книгу Excel. Сохраните его с расширением «.xslm», чтобы использовать лист Excel с макросом. Если вы не сохраните его с этим расширением, оно выдаст ошибку.

Это оно!

Теперь вы можете использовать пользовательскую функцию на листе Excel как обычную функцию Excel, используя «=». Когда вы начинаете вводить «=» в ячейке, она показывает вам созданную функцию вместе с другой встроенной функцией.

Вы можете увидеть пример ниже:

Пользовательские функции Excel не могут изменить среду Microsoft Excel и, следовательно, имеют ограничения.

Ограничения пользовательских функций Excel

Пользовательские функции Excel не могут выполнять следующие действия:

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

Есть еще много таких ограничений, и упомянуты некоторые из них.

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

Функция АДРЕС Excel для получения адреса ячейки и других данных

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

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

• Функция АДРЕС Excel — синтаксис и основы использования
• Как использовать функцию АДРЕС в Excel — примеры формул

Функция АДРЕС Excel — синтаксис и основы использования

Функция АДРЕС предназначена для получения адреса ячейки в Excel на основе указанных номеров строк и столбцов. Адрес ячейки возвращается в виде текстовой строки, а не фактической ссылки.

Синтаксис функции АДРЕС следующий:

АДРЕС (row_num, column_num, [abs_num], [a1], [sheet_text])

Требуются первые два аргумента:

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

column_num — номер столбца для построения ссылки на ячейку.

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

abs_num — тип ссылки, абсолютный или относительный. Может принимать любое из следующих чисел; значение по умолчанию — абсолютное.

• 1 или опущено — абсолютная ссылка на ячейку, например $ A $ 1
• 2 — смешанная ссылка: относительный столбец и абсолютная строка, например 1

австралийских долларов

• 3 — смешанная ссылка: абсолютный столбец и относительная строка, например $ A1
• 4 — относительная ссылка на ячейку, например A1

a1 — эталонный стиль, A1 или R1C1.Если не указано, используется стиль по умолчанию A1.

• 1 или ИСТИНА или опущено — возвращает адрес ячейки в стиле ссылки A1, где столбцы представляют собой буквы, а строки — числа.
• 0 или FALSE — возвращает адрес ячейки в стиле ссылки R1C1, где строки и столбцы представлены числами.

sheet_text — имя рабочего листа для включения во внешнюю ссылку. Имя листа должно быть представлено в виде текстовой строки и заключено в кавычки, например.грамм. «Лист2». Если этот параметр не указан, имя рабочего листа не используется, а адресом по умолчанию является текущий лист.

Например:

= АДРЕС (1,1) — возвращает адрес первой ячейки (т.е. ячейки на пересечении первой строки и первого столбца) как абсолютную ссылку на ячейку $ A $ 1.

= АДРЕС (1,1,4) — возвращает адрес первой ячейки как относительную ссылку на ячейку A1.

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

Как использовать функцию АДРЕС в Excel — примеры формул

Примеры ниже показывают, как использовать функцию АДРЕС в больших формулах для выполнения более сложных задач.

Вернуть значение ячейки в заданной строке и столбце

Если ваша цель — получить значение из определенной ячейки на основе номеров ее строк и столбцов, используйте функцию АДРЕС вместе с КОСВЕННО:

КОСВЕННЫЙ (АДРЕС (row_num; column_num))

Функция АДРЕС выводит адрес ячейки в виде текста. Функция ДВССЫЛ превращает этот текст в обычную ссылку и возвращает значение из соответствующей ячейки.

Например, чтобы получить значение ячейки на основе номера строки в E1 и номера столбца в E2, используйте эту формулу:

= КОСВЕННО (АДРЕС (E1, E2))

Получить адрес ячейки с наибольшим или наименьшим значением

В этом примере мы сначала найдем максимальное и самое низкое значения в диапазоне B2: B7 с помощью функций MAX и MIN и выведем эти значения в специальные ячейки:

Ячейка E2: = МАКС (B2: B7)

Ячейка F2: = МИН (B2: B7)

Затем мы будем использовать АДРЕС в сочетании с функцией ПОИСКПОЗ, чтобы получить адреса ячеек.

Ячейка с максимальным значением:

= АДРЕС (СООТВЕТСТВИЕ (E2, B: B, 0), КОЛОНКА (B2))

Ячейка с минимальным значением:

= АДРЕС (СООТВЕТСТВИЕ (F2, B: B, 0), КОЛОНКА (B2))

Если вы не хотите, чтобы наибольшее и наименьшее значения в отдельных ячейках, вы можете вложить функцию MAX / MIN в первый аргумент MATCH. Например:

Ячейка с наибольшим значением:

= АДРЕС (СООТВЕТСТВИЕ (МАКС (B2: B7), B: B, 0), СТОЛБЕЦ (B2))

Ячейка с наименьшим значением:

= АДРЕС (МАТЧ (МИН (B2: B7), B: B, 0), КОЛОНКА (B2))

Как работают эти формулы

Чтобы найти номер строки, вы используете функцию MATCH (lookup_value, lookup_array, [match_type]), которая возвращает относительное положение lookup_value в lookup_array.В нашей формуле значение поиска — это число, возвращаемое функцией MAX или MIN, а массив поиска — это весь столбец. Следовательно, относительное положение значения поиска в массиве точно соответствует номеру строки на листе.

Чтобы найти номер столбца, используйте функцию СТОЛБЕЦ. Конечно, ничто не мешает ввести число прямо в формулу, но COLUMN избавляет от необходимости ручного подсчета в случае, если целевой столбец находится в середине листа.

Получить букву столбца из номера столбца

Чтобы преобразовать любое заданное число в букву столбца, используйте функцию АДРЕС внутри ЗАМЕНЫ:

ЗАМЕНА (АДРЕС (1, номер_столбца , 4), «1», «»)

В качестве примера найдем букву столбца, соответствующую числу в A2:

= ЗАМЕНА (АДРЕС (1, A2,4), «1», «»)

Глядя на результаты ниже, мы можем сказать, что первый столбец на листе — это A, что очевидно; 10-й столбец — J, столбец 50 ^-й — AX, а столбец 100 ^-й — CV:

Как работает эта формула

Для начала настройте функцию АДРЕС, чтобы она возвращала относительную ссылку на первую ячейку в целевом столбце:

• В качестве номера строки используйте 1.
• В качестве номера столбца укажите ссылку на ячейку, содержащую число, в нашем примере A2.
• В качестве аргумента abs_num введите 4.

В результате ADDRESS (1, A2,4) вернет A1.

Чтобы избавиться от координаты строки, оберните приведенную выше формулу в функцию ЗАМЕНА и замените «1» пустой строкой («»). Выполнено!

Получить адрес именованного диапазона

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

Как получить адрес первой ячейки в диапазоне

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

АДРЕС (СТРОКА ( диапазон ), КОЛОНКА ( диапазон ))

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

= АДРЕС (СТРОКА (Продажи), КОЛОНКА (Продажи))

И возвращает адрес левой верхней ячейки в диапазоне:

В этой формуле функции ROW и COLUMN возвращают массив всех номеров строк и столбцов в диапазоне соответственно.На основе этих чисел функция АДРЕС строит массив адресов ячеек. Но поскольку формула вводится в одной ячейке, отображается только первый элемент массива, который соответствует первой ячейке в диапазоне.

Как получить адрес последней ячейки в диапазоне

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

АДРЕС (СТРОКА ( диапазон ) + СТРОКИ ( диапазон ) -1, COLUMN ( диапазон ) + COLUMNS ( диапазон ) -1)

Применительно к нашему ассортименту под названием «Продажи» формула принимает следующий вид:

= АДРЕС (СТРОКА (Продажи) + СТРОКИ (Продажи) -1, СТОЛБЦА (Продажи) + СТОЛБЦЫ (Продажи) -1)

И возвращает ссылку на правую нижнюю ячейку диапазона:

На этот раз нам потребуются более сложные вычисления, чтобы определить номер строки.Как и в предыдущем примере, функция ROW дает нам массив всех номеров строк в диапазоне, в нашем случае {4; 5; 6; 7}. Нам нужно «сдвинуть» эти числа на общее количество строк минус 1, чтобы первый элемент в массиве стал номером последней строки. Чтобы найти общее количество строк, мы используем функцию ROWS и вычитаем 1 из ее результата: (4-1 = 3). Затем мы добавляем 3 к каждому элементу исходного массива, чтобы выполнить требуемый сдвиг: {4; 5; 6; 7} + 3 = {7; 8; 9; 10}.

Номер столбца рассчитывается аналогичным образом: {2,3,4} + 3-1 = {4,5,6}

Из приведенных выше массивов номеров строк и столбцов функция ADDRESS собирает массив адресов ячеек, но возвращает только первый, соответствующий последней ячейке в диапазоне.

Такого же результата можно достичь, выбрав максимальные значения из массивов номеров строк и столбцов. Однако это работает только в формуле массива, которая требует нажатия Ctrl + Shift + Enter для правильного выполнения:

= АДРЕС (МАКС. (СТРОКА (Продажи)), МАКС. (СТОЛБЕЦ (Продажи)))

Как получить полный адрес именованного диапазона

Чтобы вернуть полный адрес именованного диапазона, вам просто нужно объединить две формулы из предыдущих примеров и вставить между ними оператор диапазона (:).

ADDRESS (ROW ( range ), COLUMN ( range )) & «:» & ADDRESS (ROW ( range ) + ROWS ( range ) -1, COLUMN ( range ) + COLUMNS () диапазон ) -1)

Чтобы это работало для нашего набора данных, мы заменяем общий «диапазон» на настоящее имя диапазона «Продажи»:

= АДРЕС (СТРОКА (Продажи), СТОЛБЦА (Продажи)) & ":" & АДРЕС (СТРОКА (Продажи) + СТРОКИ (Продажи) -1, СТОЛБЕЦ (Продажи) + СТОЛБЦЫ (Продажи) -1)

И получить полный адрес диапазона в виде абсолютной ссылки $ B $ 4: $ D $ 7:

Чтобы вернуть адрес диапазона как относительную ссылку (без знака $, например, B4: D7), установите для аргумента abs_num в обеих функциях ADDRESS значение 4:

= АДРЕС (СТРОКА (Продажи), СТОЛБЦА (Продажи), 4) & «:» & АДРЕС (СТРОКА (Продажи) + СТРОКИ (Продажи) -1, СТОЛБЕЦ (Продажи) + СТОЛБЦЫ (Продажи) -1, 4)

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

Вот как вы возвращаете адрес ячейки в Excel.Чтобы ближе познакомиться со всеми формулами, обсуждаемыми в этом руководстве, вы можете загрузить нашу книгу примеров функции АДРЕС в Excel. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

Отображение содержимого ячейки в другой ячейке в Excel

Ссылка на диапазон (несколько ячеек)

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

Получение значений из нескольких ячеек в Excel

Часто мы относим несколько ячеек к единой ячейке. Мы можем ссылаться на несколько ячеек и диапазонов в Excel, чтобы объединить текст или выполнить вычисления.

Получение текста из нескольких ячеек

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

= E2 и F2

Объединить текст из нескольких ячеек:

Мы можем использовать оператор & для объединения текста из нескольких ячеек.Мы можем предоставить пробел или — для разделения текста на несколько слов. В следующем примере показано, как получить данные из нескольких ячеек и отобразить объединенный текст в одной ячейке.

= E2 & ”” & F2

Получение значений из нескольких ячеек:

Иногда нам нужно извлечь значения из другой ячейки и выполнить некоторые вычисления в ячейке. Например, у нас есть значения стоимости и количества в двух разных ячейках (B2, C2), и мы можем найти общую стоимость в другой ячейке (A2).

= B2 * C2

Объединение значений нескольких ячеек:

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

= «Общая стоимость» & B2 * C2

Обращение к данным из диапазона ячеек

В следующем примере показано обращение к диапазону и выполнение математических вычислений.Он будет ссылаться на диапазон от D1 до E5 и поместит сумму данных в диапазон A1

.

= СУММ (D1: E5)

Ссылка на ячейки с другого листа

Ссылка на ячейки с одного листа очень проста в Excel. Нам нужно передать имя листа в формуле, за которым следует символ «!». Восклицательный знак используется для обозначения рабочего листа в формуле Excel.

В следующем примере содержимое ячейки будет ссылаться на другой рабочий лист (данные) и отображаться в ячейке.

= Данные! A1

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

= ’Технические данные’! A1

Ссылки и объединение ячеек

Мы можем объединить ячейки, используя оператор Concatenate или функцию CONCATENATE в Excel. Давайте посмотрим на оба метода объединения ячеек в Excel.

Использование оператора объединения : Вы можете ссылаться на ячейки, используя адрес ячейки, и использовать оператор & (амперсанд) для объединения ячеек.Например, следующая формула объединит ячейки (D1 и E1).

= D1 и E1

‘OR

= D1 & ”“ & E1

Использование функции CONCATENATE : мы можем использовать функцию Excel для ссылки и объединения нескольких ячеек и диапазонов в Excel. Вы используете, (запятую) t0, чтобы указать ссылку на каждую ячейку в формуле.

= СЦЕПИТЬ (D1, E1)

Ссылка на именованный диапазон

Мы можем ссылаться на именованные диапазоны в Excel с помощью формулы.Имя диапазона должно следовать за оператором =, чтобы ссылаться на именованный диапазон в Excel. Следующая формула относится к именованному диапазону под названием «TotalSales».

= Всего продаж

Будет отображено значение ячейки, названное «TotalSales».

В следующих примерах показано, как ссылаться на диапазон ячеек в Excel. Диапазон («Столбец продаж») используется в функции СУММ для возврата общего значения именованного диапазона «Столбец продаж».

= СУММ («Столбец продаж»)

Отображение значений ячеек на фигуре

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

• Выберите форму
• Щелкните по строке формул
• Введите оператор = (присвоение)
• И введите требуемый адрес ячейки (например: = $ A $ 3)

Отображение значения ячейки в заголовке диаграммы

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

• Выберите диаграмму, на которой вы хотите отобразить значение ячейки
• Перейдите на вкладку «Дизайн диаграммы» на ленте.
• И показать заголовок диаграммы из элементов диаграммы
• Щелкните заголовок выбранной диаграммы
• Нажмите на панель формул
• Введите оператор = (присвоение)
• И введите требуемый адрес ячейки (например: = Sheet2! $ K $ 8)

Получить номера ячеек определенного текста в Excel с помощью функции АДРЕС

Вы можете использовать функцию АДРЕС в Excel, чтобы получить адрес определенной ячейки на листе, указав функции номер строки и столбца.Я собираюсь показать, как можно использовать функцию АДРЕС в формуле, чтобы получить номер ячейки определенного текста на листе Excel.

Я сказал: номер ячейки определенного текста. Смотрите это изображение ниже.

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

Вот формула!

= ЕСЛИ (C4 = «воробей», АДРЕС (СТРОКА (C4), КОЛОНКА (C4), 4), «»)

В приведенной выше формуле я использовал функцию АДРЕС () внутри функции ЕСЛИ (). Вы можете использовать ADDRESS () внутри других функций.

Удерживая ячейку, перетащите формулу вниз в другие строки.

Функция ЕСЛИ () имеет три параметра. Теперь здесь я проверяю, имеет ли конкретная ячейка (например, C4) значение воробей, если истина, то показывать АДРЕС ячейки, если ложь, то ничего не показывать.

Функция АДРЕС () имеет три параметра. См. Полный синтаксис ниже. Первый параметр — это номер строки; второй — номер столбца, а третий — тип возвращаемого значения (4).

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

Измените тип птицы внутри формулы и посмотрите результат.

Синтаксис функции АДРЕС

АДРЕС (row_num, column_num, [abs_num], [a1], [sheet])

Первые два параметра важны и необходимы.Остальные три параметра необязательны.

Примечание : вы можете просто ввести = АДРЕС (внутри ячейки на листе, чтобы увидеть синтаксис.

row_num: это числовое значение в виде номера строки. Это требуется .

номер_столбца: числовое значение, указывающее номер используемого столбца. Это тоже , требуется .

abs_num: тип адреса, возвращаемого функцией.Это числовое значение от 1 до 4. Хотя я использую этот параметр в своей формуле (см. Выше), это необязательный параметр.

абс означает абсолютное. Если вы опустите это значение в функции АДРЕС, то тип возвращаемого значения по умолчанию будет равен абсолютному или 1.

Четыре значения и возвращаемые типы:

Попробуйте это : В приведенной выше формуле я использовал значение 4 и перенастроил ячейки C7, C10 и т. Д.Измените число 4 на 1, 2 или 3 и посмотрите результат.

a1: эталонный стиль: A1 или R1C1. Значения должны быть TRUE или FALSE. По умолчанию используется стиль A1 или TRUE. Лист
: имя используемого рабочего листа. Если вы его опустите, функция будет считать текущий лист вашим рабочим.

Теперь давайте заполним все параметры функции ADDRESS () и посмотрим на результат.

= АДРЕС (1,2,4; ИСТИНА; «Лист1»)

Результат :

Лист1! B1

Ну вот и все.