Excel формула возвращает значение ячейки

Функция INDIRECT (ДВССЫЛ) в Excel используется когда у вас есть ссылки в виде текста, и вы хотите получить значения из этих ссылок.

Что возвращает функция

Функция возвращает ссылку, заданную текстовой строкой.

Синтаксис

=INDIRECT(ref_text, [a1]) — английская версия

=ДВССЫЛ(ссылка_на_текст;[a1]) — русская версия

Аргументы функции

• ref_text (ссылка_на_текст) — текстовая строка, которая содержит в себе ссылку на ячейку или именованный диапазон;
• [a1] — логическое значение, которое определяет тип ссылки используемой в аргументе ref_text (ссылка_на_текст). Значения аргумента могут быть TRUE (ссылка указана в формате «А1») или FALSE (ссылка указана в формате «R1C1»). Если не указать этот аргумент, то Excel автоматически определит его значение как TRUE.

Дополнительная информация

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

Примеры использования функции ДВССЫЛ в Excel

Пример 1. Используем ссылку на ячейку для получения значения

Функция ДВССЫЛ получает ссылку на ячейку как исходные данные и возвращает значение ячейки по этой ссылке (как показано в примере ниже):

Формула в ячейке С1:

=INDIRECT(“A1”) — английская версия

=ДВССЫЛ(«A1») — русская версия

Функция получает ссылку на ячейку (в двойных кавычках) и возвращает значение этой ячейки, которая равна “123”.

Вы можете спросить — почему бы нам просто не использовать «=A1» вместо использования функции INDIRECT (ДВССЫЛ)?

И вот почему…

Если в данном случае вы введете в ячейку С1 формулу “=A1” или “=$A$1”, то она выдаст вам тот же результат, что находится в ячейке А1. Но если вы вставите в таблице строку выше, вы можете заметить, что ссылка на ячейку будет автоматически изменена.

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

Пример 2. Получаем данные по ссылке на ячейку

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

На примере выше, ячейка «А1» содержит в себе число “123”.

Ячейка «С1» ссылается на ячейку «А1».

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

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

Пример 3. Используем комбинацию текстового и числового значений в функции INDIRECT (ДВССЫЛ)

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

Например, если в ячейке С1 указано число “2”, то используя формулу =INDIRECT(“A”&C1) или =ДВССЫЛ(«A»&C1) вы получите ссылку на значение ячейки «А2».

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

Пример 4. Ссылаемся на диапазон ячеек с помощью функции INDIRECT (ДВССЫЛ)

С помощью функции вы можете ссылаться на диапазон ячеек.

Например, =INDIRECT(“A1:A5”) или =ДВССЫЛ(«A1:A5») будет ссылаться на данные из диапазона ячеек «A1:A5».

Используя функцию SUM (СУММ) и INDIRECT (ДВССЫЛ) вместе, вы можете рассчитать сумму, а также максимальные и минимальные значения диапазона.

Пример 5. Ссылаемся на именованный диапазон значений с использованием функции INDIRECT (ДВССЫЛ)

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

Например, представим что у вас есть оценки по 5 студентам по трем предметам как показано ниже:

Зададим для следующих ячеек названия:

• B2:B6: Математика
• C2:C6: Физика
• D2:D6: Химия

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

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

=INDIRECT(“Именованный диапазон”) — английская версия

=ДВССЫЛ(“Именованный диапазон”) — русская версия

Например, если вы хотите узнать средний балл среди студентов по математике — используйте следующую формулу:

=AVERAGE(INDIRECT(“Математика”)) — английская версия

=СРЗНАЧ(ДВССЫЛ(«Математика»)) — русская версия

Если имя диапазона указано в ячейке («F2» в приведенном ниже примере указан как “Матем”), вы можете использовать ссылку на ячейку прямо в формуле. В следующем примере показано, как вычислять среднее значение с использованием именных диапазонов.

Пример 6. Создаем зависимый выпадающий список с помощью INDIRECT (ДВССЫЛ)

C помощью этой функции вы можете создавать зависимый выпадающий список.

Например, предположим, что у вас есть две колонки с названиями «Россия» и «США», в строках указаны города этих стран, как указано на примере ниже:

Для того, чтобы создать зависимый выпадающий список вам нужно создать два именованных диапазона для ячеек «A2:A5» с именем “Россия” и для ячеек «B2:B5» с названием “США”.

Теперь, в ячейке «D2» создайте выпадающий список для «России» и «США». Так мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.

Теперь, для создания зависимого выпадающего списка:

• Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
• Кликните по вкладке “Data” -> “Data Validation”;
• На вкладке “Настройки” в разделе “Allow” выберите List;
• В разделе “Source” укажите ссылку: =INDIRECT($D$2) или =ДВССЫЛ($D$2);
• Нажмите ОК

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

Функция

ЯЧЕЙКА(

)

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

,

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

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

ЯЧЕЙКА()

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

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

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

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

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

ссылка —

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

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

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

ЯЧЕЙКА()

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

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

В

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

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

Большинство сведений об ячейке касаются ее формата. Альтернативным источником информации такого рода может случить только 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. Примеры использования функции ДВССЫЛ (INDIRECT)

Функция ДВССЫЛ (INDIRECT) — одна из наиболее трудных в освоении функций Excel. Однако умение использовать ее позволит вам решать многие из задач, кажущихся вам сейчас неразрешимыми. По сути, если в формуле есть раздел ДВССЫЛ со ссылкой на ячейку, эта ссылка обрабатывается как содержимое соответствующей ячейки. [1] Например (рис. 1), в ячейке С4 я ввел формулу =ДВССЫЛ(А4), и Excel возвратил значение, равное 6. Excel возвращает именно это значение, поскольку ссылка на А4 немедленно заменяется текстовой строкой В4. Следовательно, формула обрабатывается как =В4, что дает нам 6. По аналогии, если ввести в ячейке С5 формулу =ДВССЫЛ(А5), Excel вернет значение ячейки В5, то есть 9.

Рис. 1. Простой пример функции ДВССЫЛ

Скачать заметку в формате Word или pdf, также доступны примеры и Задание_3 в формате Excel2013

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

В диапазоне ячеек В4:Н16 (рис. 2) приведены данные о ежемесячных продажах шести товаров за 12 месяцев. Сейчас я подсчитаю общие продажи каждого товара за месяцы со 2 по 12. Простейший способ подсчитать это — скопировать формулу СУММ(С6:С16) из ячейки С18 в диапазон D18:H18. Предположим, вам потребовалось изменить месяцы, по которым производится подсчет. Скажем, вы решите подсчитать общие продажи за месяцы 3–12. Можно изменить формулу в ячейке С18 на СУММ(С7:С16) и затем скопировать ее в диапазон D18:H18. Однако может быть не всегда удобно, поскольку вам приходится копировать формулу из ячейки С18 в диапазон D18:H18, и, не просматривая формул, никто не узнает, какие строки суммируются.

Рис. 2. Функция ДВССЫЛ позволяет изменять ссылки на ячейки в формулах, не изменяя сами формулы

Функция ДВССЫЛ предлагает другое решение. Я указал в ячейках D2 и Е2 номера начальной и конечной суммируемых строк. Теперь, при использовании функции ДВССЫЛ, мне достаточно изменить значения в ячейках D2 и Е2, чтобы конечная сумма обновилась, включив только те строки, которые мы хотим. Кроме того, значения ячеек D2 и Е2 наглядно показывают, какие строки (месяцы) суммируются! Все, что мне требуется, — скопировать из ячейки С18 в диапазон D18:H18 формулу =СУММ(ДВССЫЛ(C$3&$D$2&»:»&C$3&$E$2)). Каждая ссылка на ячейку в этой формуле обрабатывается как содержимое соответствующей ячейки. С$3 обрабатывается как С, $D$2 — как 6, а $Е$2 — как 16. Используя символ конкатенации — & (сцепления), Excel обрабатывает эту формулу как СУММ(С6:С16), что нам и требуется. Формула в ячейке D18 обрабатывается как СУММ(D6:D16), что также дает нужный нам результат. Конечно, если нам захочется просуммировать продажи, скажем, с 4 по 6 месяц, мы просто введем 8 в ячейку D2 и 10 в ячейку Е2. После этого формула в ячейке С18 вернет 33 + 82 + 75 = 190.

2. В ячейке В1 книги Excel, начиная с Лист1 и заканчивая Лист7 (рис. 3) содержатся данные о продажах товара за месяц. Есть ли какой-нибудь простой способ написать и скопировать формулу, которая выводила бы данные о продажах этого товара за каждый месяц на одном листе?

Рис. 3. Данные о продажах товара за 1–7 месяц, выведенные с помощью функции ДВССЫЛ

Предположим, Лист1 содержит данные о продажах за первый месяц, Лист2 — за второй и т.д. Пусть в первом месяце продажи равны 1. Например, вы хотите вывести продажи за все месяцы на одном листе. Нудный способ — подсчитать продажи за первый месяц с помощью формулы =Лист1!В1 продажи за второй месяц с помощью формулы =Лист2!В1 и т.д. Если ваши данных охватывают 100 месяцев, такое решение грозит грандиозной головной болью. Гораздо более изящный способ — вывести данные о продажах за первый месяц в ячейке С4 листа Лист1 с помощью формулы =ДВССЫЛ($A$4&B4&»!B1″), Excel обработает $A$4 как «Лист», В4 — как 1, и «!B1» — как строку текста !В1. Формула целиком будет обработана как =Лист1!В1, то есть покажет данные о продажах за первый месяц, содержащиеся в ячейке В1 листа Лист1. Скопировав эту формулу в диапазон С5:С10, вы отобразите содержимое ячейки В1 листов со 2 по 7. Обратите внимание: при копировании формулы из ячейки С4 в ячейку С5 ссылка на В4 заменяется ссылкой на В5, и формула в ячейке С5 возвращает значение ячейки Лист2!В1 и т.д.

3. Предположим, я суммирую значения из диапазона А5:А10 посредством формулы СУММ(А5:А10). Если вставить где-нибудь между 5 и 10 строками пустую строку, формула автоматически изменится на СУММ(А5:А11). Можно ли написать формулу, которая при вставке пустой строки между 5 и 10 строками все равно суммировала бы значения из диапазона А5:А10?

Рис. 4 иллюстрирует несколько способов сложения чисел из диапазона А5:А10. В ячейке А12 я ввел обычную формулу СУММ(А5:А10). Аналогичным образом формула СУММ($А$5:$А$10) в ячейке С9 тоже возвращает значение 33. Тем не менее, если вставить строку между 5 и 10 строками, обе формулы попытаются сложить ячейки диапазона А5:А11.

Функция ДВССЫЛ (INDIRECT) предоставляет, как минимум, два способа сложения значений из диапазона А5:А10. В ячейке D9 я ввел формулу =СУММ(ДВССЫЛ(«A5:A10»)). Excel обрабатывает ДВССЫЛ(«A5:A10») как строку текста «A5:A10», и поэтому, даже если я добавлю строку в электронную таблицу, формула по-прежнему будет суммировать значения из диапазона А5:А10.

Рис. 4. Несколько способов сложения значений ячеек из диапазона А5:А10; под значением суммы написана формула

Еще один вариант сложения значений из диапазона А5:А10 при помощи функции ДВССЫЛ – формула =СУММ(ДВССЫЛ(«A»&C4&»:A»&D4)), которую я ввел в ячейке С5. Excel обрабатывает ссылку на С3 как 5, а ссылку на D3 — как 10, в результате чего формула преобразуется в СУММ(А5:А10). Вставка пустой строки между 5 и 10 строками никак не скажется на формуле, поскольку ссылка на С3 по-прежнему будет обрабатываться как 5, а ссылка на D3 — как 10. На рис. 5 показаны результаты суммирования, выполненные посредством наших четырех формул после того, как ниже 7-й строки была добавлена пустая строка.

Рис. 5. Результаты, возвращенные формулами СУММ после того, как ниже строки 7 была вставлена пустая строка

Обратите внимание: классические формулы СУММ, не включающие оператор ДВССЫЛ, автоматически изменились и суммируют значения из диапазона А5:А11, по-прежнему возвращая значение 33. Две формулы СУММ, включающие оператор ДВССЫЛ, суммируют значения из диапазона А5:А10, в результате при вычислениях теряется число 2 (оно теперь находится в ячейке A11). Формулы СУММ, содержащие оператор ДВССЫЛ, возвращают значение 31.

Контрольные задания (с ответами)

1. Функция АДРЕС возвращает адрес ячейки, сопоставленный со строкой и столбцом. Например, формула АДРЕС(3;4) возвращает $D$3. Что вернет формула =ДВССЫЛ(АДРЕС(3;4))?

2. Лист Задание_2 содержит данные о продажах пяти товаров в четырех регионах. С помощью функции ДВССЫЛ создайте формулы, которые позволят легко суммировать общие продажи любых последовательно пронумерованных товаров, например Товар 1—Товар 3, Товар 2—Товар 5 и т.д.

3. Книга Excel Задание_3 содержит шесть листов. На листе Лист1 записаны данные о продажах товаров за первый месяц. Эти данные всегда указываются в диапазоне Е5:Н5. Используя функцию ДВССЫЛ, составьте таблицу с информацией о продажах каждого товара по месяцам на отдельном листе.

Ответы

1. Формула =ДВССЫЛ(АДРЕС(3;4)) вернет значение ячейки $D$3.

2. Фрагмент формулы ДВССЫЛ(«B»&B8+1&»:»&»E»&B9+1) возвращает диапазон В3:Е5:

3. В ячейке Е9 записана формула =ДВССЫЛ($B$8&$D9&»!»&E$7&5), которая возвращает значение ячейки Лист1!Е5:

Возможно, вас также заинтересует:

[1] При написании заметки использованы материалы книги Уэйн Л. Винстон. Microsoft Excel. Анализ данных и построение бизнес-моделей, глава 22.

Функция ДВССЫЛ в Excel

Рассмотрим примеры использования функции ДВССЫЛ в Excel, которая позволяет преобразовывать текст из ячейки в ссылку.

Функция ДВССЫЛ чрезвычайно полезна тем, что при использовании данной функции есть возможность изменять ссылки на ячейки и диапазоны в формуле не изменяя при этом саму формулу.
Другими словами, введенная формула =B2 идентична формуле =ДВССЫЛ(«B2»), однако в первом варианте мы оперируем ссылкой, а во втором — текстом, который можно изменять.

Описание функции ДВССЫЛ

ДВССЫЛ(ссылка_на_ячейку; [a1])
Возвращает ссылку, заданную текстовой строкой.

• Ссылка на ячейку(обязательный аргумент) — ссылка в виде текста вида A1 или R1C1;
• A1(необязательный аргумент) — вид ссылки, в случае когда аргумент принимает значение ИСТИНА (или опущен), то ссылка трактуется как вид A1, когда принимает значение ЛОЖЬ, то как вид R1C1.

Примеры использования функции ДВССЫЛ

Пример 1. Ссылка на ячейку

Начнем с простой задачи, который мы уже частично разобрали.
Введем произвольное значение в ячейку A1, теперь чтобы сделать ссылку на ячейку введем формулу =ДВССЫЛ(«A1»), например, в ячейку A2:

Пример 2. Ссылка на другой лист

Немного усложним задачу, и применим формулу ДВССЫЛ для ссылки на другой лист.
Перейдем на любой другой лист книги и вводим формулу =ДВССЫЛ(«Пример_1!A1»), где лист Пример_1 — лист из первого примера:

Пример 3. Функции

Рассмотрим примеры с одновременным применением функции ДВССЫЛ и других функций.

Функция СУММ

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

Функция СУММ с прямой ссылкой на диапазон решает эту задачу, например, можно применить формулу =СУММ(B2:B5) для подсчета продаж апельсинов.
Однако тогда при изменении периода нам придется менять и диапазон в исходной формуле.
Обойдем эту проблему записав диапазон в текстовом виде с использованием ссылок на другие ячейки — запишем формулу =СУММ(ДВССЫЛ(B15&2&»:»&B15&(1+$A16))), где ячейка A16 отвечает за номер периода:

Расписывая по шагам данную формулу, мы в конце получим формулу =СУММ(B2:B5), что нам и требовалось.

Функция ПОИСКПОЗ

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

Записываем в оценку кандидатов формулу =ДВССЫЛ(«G»&ПОИСКПОЗ(B2;$F$1:$F$6;1)), где с помощью функции ПОИСКПОЗ находим относительное положение оценки кандидата в критерии оценок, а функцией ДВССЫЛ подтягиваем полученную оценку для каждого кандидата.

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

Функция ДВССЫЛ() в MS EXCEL

Функция ДВССЫЛ() , английский вариант INDIRECT(), возвращает ссылку на ячейку(и), заданную текстовой строкой . Например, формула = ДВССЫЛ(«Лист1!B3») эквивалентна формуле = Лист1!B3 . Мощь этой функции состоит в том, что саму ссылку ( Лист1!B3 ) также можно изменять формулами, ведь для ДВССЫЛ() это просто текстовая строка! С помощью этой функции можно транспонировать таблицы, выводить значения только из четных/ нечетных строк, складывать цифры числа и многое другое.

Функция ДВССЫЛ() имеет простой синтаксис.

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

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

Ссылка_на_ячейку — это текстовая строка в формате ссылки (т.е. указаны столбец и строка): = ДВССЫЛ(«B3») или = ДВССЫЛ(«Лист1!B3») или =ДВССЫЛ(«[Книга1.xlsx]Лист1!B3») . Первая формула эквивалентна формуле = B3 , вторая — = Лист1!B3 , третья = [Книга1.xlsx] Лист1!B3 Если какая-либо ячейка (например, А1) содержит текстовую строку в формате ссылки (например, Лист1!B3 ), то в ДВССЫЛ() можно указать ссылку на эту ячейку = ДВССЫЛ(А1) Эта запись будет эквивалентна = ДВССЫЛ(«Лист1!B3») , которая в свою очередь будет эквивалентна = Лист1!B3 . Зачем все это нужно — читайте ниже (см. раздел решение задач).

Второй аргумент а1 — это логическое значение (ИСТИНА или ЛОЖЬ), указывающее, какого типа ссылка содержится в аргументе Ссылка_на_ячейку .

• Если a1 имеет значение ИСТИНА или опущена, то ссылка_на_ячейку интерпретируется как ссылка в стиле A1.
• Если a1 имеет значение ЛОЖЬ, то ссылка_на_ячейку интерпретируется как ссылка в стиле R1C1.

Примечание: Формат ссылки = Лист1!B3 называется ссылкой в стиле А1, когда явно указывается адрес ячейки. Формат ссылки в стиле R1C1 — это относительная ссылка на ячейку (относительная относительно ячейки с формулой). Например, если в ячейке С5 имеется формула =R[-1]C, то это ссылка на ячейку С4. Чтобы записывать ссылки в стиле R1C1 необходимо переключить EXCEL в режим работы со ссылками в стиле R1C1 ( Кнопка Офис/ Параметры Excel/ Формулы/ Работа с формулами ).

Если ссылка_на_ячейку не является допустимой ссылкой, то функция ДВССЫЛ() возвращает значение ошибки #ССЫЛКА!

Рассмотрим несколько задач

Задача1 — Формируем ссылки на листы

Пусть на листах Лист1, Лист2, Лист3 и Лист4 в одних и тех же ячейках находятся однотипные данные (Продажи товаров за квартал) См. файл примера .

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

Для удобства в строке 9 на листе, где будет итоговая таблица, пронумеруем столбцы С, D, E, F как 1, 2, 3, 4 в соответствии с номером квартала и пронумеруем строки таблицы (см. столбец А).

Чтобы вывести данные с других листов используем формулу =ДВССЫЛ(«Лист»&C$9&»!B»&$A10+3)

Такая запись возможна, т.к. все листы имеют однотипные названия: Лист1, Лист2, Лист3 и Лист4, и все таблицы на этих листах имеют одинаковую структуру (одинаковое количество строк и столбцов, наименования товаров, также должны совпадать).

Вышеуказанная формула в ячейке С12 эквивалентна формуле =ДВССЫЛ(«Лист1!B4») , формула в ячейке D12 эквивалентна =ДВССЫЛ(«Лист2!B4») , т.е. ссылается на другой лист! Весь смысл использования функции ДВССЫЛ() состоит в том, чтобы написать формулу в ячейке С12 и затем ее скопировать в другие ячейки (вправо и вниз), например с помощью Маркера заполнения. Теперь данные с 4-х различных листов сведены в 1 таблицу!

Примечание: Обратите внимание на использование в формуле смешанных ссылок ( C$9 и $A12).

Задача2 — ссылки на четные/ нечетные строки

C помощью ДВССЫЛ() можно вывести только четные или нечетные строки из исходной таблицы. В качестве исходной используем предыдущую таблицу Продажи за год.

Записав формулу =ДВССЫЛ(СИМВОЛ(65+H$26)&$A12*2+11) и скопировав ее в нужное количество ячеек, получим только четные записи из исходной таблицы. Формула в ячейке H12 эквивалентна =ДВССЫЛ(«B13»)

Примечание: С помощью функции СИМВОЛ() можно вывести любой символ, зная его код. =СИМВОЛ(65) выведет букву А (английскую), =СИМВОЛ(66) выведет В, =СИМВОЛ(68) выведет D.

C помощью формулы =ДВССЫЛ(СИМВОЛ(65+N$26)&$A12*2+10) можно вывести только нечетные строки, а с помощью формулы =ДВССЫЛ(СИМВОЛ(65+B$26)&$A28+11) вообще произвольные строки, номера которых заданы в столбце А.

Задача3 — транспонирование таблиц/ векторов

С помощью нижеуказанной формулы можно транспонировать исходную таблицу (подробнее читайте здесь):

О транспонировании таблиц можно прочитать в этом разделе.

Примечание: О других применениях функции ДВССЫЛ() можно прочитать в статьях, список которых расположен ниже.

Задача 4 — использование с именами

Имена Имя1 и Имя4 — это именованные диапазоны, т.е. эти имена возвращают ссылки.

Имя Имя2 — это константа массива, т.е. массив чисел, а не ссылка.

Также массив значений будет возвращать функция СМЕЩ() . см. Имя5.

Имя Имя3 — это именованная формула, которая возвращает число, а не ссылку.

Создадим табличку, в которой укажем эти имена. Постараемся найти сумму значений, которые вернут эти имена, использовав формулу =СУММ(ДВССЫЛ(A2)) .

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

Формула =СУММ(ДВССЫЛ(A2)) эквивалентна =СУММ(ДВССЫЛ(«имя1»)) Вместо «имя1» подставляется ссылка =Имена!$A$14:$A$17 ( текстовая строка в формате ссылки ), которая успешно разрешается функцией ДВССЫЛ() . В итоге функция ДВССЫЛ() возвращает массив <1:2:3:4>из диапазона $A$14:$A$17 , который затем суммируется.

В случае с Имя2 все по-другому. Формула =СУММ(ДВССЫЛ(A3)) эквивалентна =СУММ(ДВССЫЛ(«имя2»)) Вместо «имя2» подставляется массив <10_20>, который не является текстовой строкой и не может быть обработан функцией ДВССЫЛ() . Поэтому она возвращает ошибку.
Аналогичный результат получим для имен: Имя3 и Имя5.

В чем разница между =СУММ(ДВССЫЛ(имя5)) и =СУММ(ДВССЫЛ(«имя5»)) ? Когда мы записываем =СУММ(ДВССЫЛ(«имя5»)) мы говорим функции ДВССЫЛ() работать с имя5 как с адресом. Это сработает, если имя5 содержит » Имена!$A$14:$A$17″ или что-то в этом роде. Но, имя5 указывает на формулу, которая возвращает значения из диапазона Имена!$A$14:$A$17. Т.к. это не ссылка, то функция вернет ошибку.

Функция ДВССЫЛ в Excel с примерами использования

Функция ДВССЫЛ возвращает ссылку, которая задана текстовой строкой. К примеру, формула = ДВССЫЛ (А3) аналогична формуле = А3. Но для этой функции ссылка является просто текстовой строкой: ее можно изменять формулами.

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

Синтаксис функции с описанием

Функция ДВССЫЛ в Excel: примеры

Начнем с хрестоматийного примера, чтобы понять принцип работы функции.

Имеется таблица с данными:

Примеры функции ДВССЫЛ:

Рассмотрим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках расположены однотипные данные (информация об образовании сотрудников фирмы за последние 5 лет).

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

Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и вправо). Данные с пяти различных листов собираются в итоговую таблицу.

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

Чтобы получить только нечетные записи, используем формулу:

Для выведения четных строк:

Примечание. Функция СИМВОЛ возвращает символ по заданному коду. Код 65 выводит английскую букву A. 66 – B. 67 – С.

Допустим, у пользователя имеется несколько источников данных (в нашем примере – несколько отчетов). Нужно вывести количество сотрудников, основываясь на двух критериях: «Год» и «Образование». Для поиска определенного значения в базе данных подходит функция ВПР.

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

Но ВПР информацию в таком виде не сможет переработать. Поэтому каждому отчету мы дали имя (создали именованные диапазоны). Отдельно сделали выпадающие списки: «Год», «Образование». В списке «Год» – названия именованных диапазонов.

Задача: при выборе года и образования в столбце «Количество» должно появляться число сотрудников.

Если мы используем только функцию ВПР, появится ошибка:

Программа не воспринимает ссылку D2 как ссылку на именованный диапазон, где и находится отчет определенного года. Excel считает значение в ячейке текстом.

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

Функции ВПР и ДВССЫЛ в Excel

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

Предположим, нужно извлечь информацию в зависимости от заданного значения. То есть добиться динамической подстановки данных из разных таблиц. К примеру, указать количество сотрудников с незаконченным высшим образованием в 2015 и в 2016 году. Сделать так:

В отношении двух отчетов сработает комбинация функций ВПР и ЕСЛИ:

Но для наших пяти отчетов применять функцию ЕСЛИ нецелесообразно. Чтобы возвратить диапазон поиска, лучше использовать ДВССЫЛ:

• $A$12 – ссылка с образованием (можно выбирать из выпадающего списка);
• $C11 – ячейка, в которой содержится первая часть названия листа с отчетом (все листы переименованы: «2012_отчет», 2013_отчет» и т.д.);
• _отчет!A3:B10 – общая часть названия всех листов и диапазон с отчетом. Она соединяется со значением в ячейке С11 (&). В результате получается полное имя нужного диапазона.

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

Разбор функции ДВССЫЛ (INDIRECT) на примерах

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку — в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:

Обратите внимание, что аргумент функции — ссылка на А1 — введен в кавычках, поэтому что, по сути, является здесь текстом.

«Ну ОК», — скажете вы. «И что тут полезного?».

Но не судите по первому впечатлению — оно обманчиво. Эта функция может выручить вас в большом количестве ситуаций.

Пример 1. Транспонирование

пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:

Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом «&» букву «А» и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN) .

Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического «морского боя». В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C 2 , C2=R1C 3 , D2=R1C 4 и т.д.

Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE) , то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:

Пример 2. Суммирование по интервалу

Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET) . Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM) :

Пример 3. Выпадающий список по умной таблице

Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные — Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:

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

Пример 4. Несбиваемые ссылки

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

Если ставить обычные ссылки (в первую зеленую ячейку ввести =B2 и скопировать вниз), то потом при удалении, например, Даши мы получим в соответствующей ей зеленой ячейке ошибку #ССЫЛКА! (#REF!). В случае применения для создания ссылок функции ДВССЫЛ такой проблемы не будет.

Пример 5. Сбор данных с нескольких листов

Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):

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

Собрать данные со всех листов (не просуммировать, а положить друг под друга «стопочкой») можно всего одной формулой:

Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в «живую». Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа — номера строк, которые нужно взять с каждого листа.

Подводные камни

При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:

• Если вы делаете ссылку в другой файл (склеивая имя файла в квадратных скобках, имя листа и адрес ячейки), то она работает только пока исходный файл открыт. Если его закрыть, то получим ошибку #ССЫЛКА!
• С помощью ДВССЫЛ нельзя сделать ссылку на динамический именованный диапазон. На статический — без проблем.
• ДВССЫЛ является волатильной (volatile) или «летучей» функцией, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только влияющих ячеек, как у обычных функций. Это плохо отражается на быстродействии и на больших таблицах ДВССЫЛ лучше не увлекаться.

ЯЧЕЙКА (функция ЯЧЕЙКА)

​Смотрите также​​Elena1884​ ячейки см. в​Чтобы переместить ссылку на​ в указанных ниже​ (например A1:G4), нажмите​ «Неделя2» как формула​ статье Выбор ячеек,​Предположим, существует лист с​=ДВССЫЛ(«R»&C2&»C»&C3;ЛОЖЬ)​abs_num​ относительный адрес (A1),​ADDRESS​»D9″​ в аргументе «тип_сведений»,​ в круглых скобках;​ В приведенном ниже​Примечание:​: ух, вроде встали​ статье Обзор формул.​ ячейку или диапазон,​ случаях.​

​ сочетание клавиш CTRL+SHIFT+ВВОД.​ массива​ диапазонов, строк или​ конфиденциальными данными (такими​Функция​(тип_ссылки) значение​ используйте значение​(АДРЕС). Она возвращает​ч:мм:сс​ возвращаются для последней​​ во всех остальных​​ списке указаны возможные​

Описание

​Мы стараемся как​ мозги на место.​К началу страницы​ перетащите цветную границу​Для отображения важных данных​Ссылка может быть одной​=Лист2!B2​ столбцов на листе.​ как зарплата сотрудников),​INDEX​4​

​4​ ​ адрес ячейки в​ ​»D8″​ измененной ячейки. Если​ случаях — 0.​

​ значения аргумента «тип_сведений»​ можно оперативнее обеспечивать​Elena1884​Colibris​ к новой ячейке​ в более заметном​ ячейкой или диапазоном,​Ячейка B2 на листе​На вкладке​

Синтаксис

​ которые не должен​

​(ИНДЕКС) также может​.​

• ​. Остальные варианты:​​ текстовом формате, используя​Примечание:​ аргумент ссылки указывает​Примечание:​ и соответствующие результаты.​ вас актуальными справочными​: Вариант ВПР() в​: какая функция позволяет​

• ​ отображаться на листе.​​ 5000 строк листа​, Вы должны указать​ не указано, функция​Давайте обратимся к сведениям​Скопируйте образец данных из​ возвращаемые функцией ЯЧЕЙКА,​ кавычка (‘) соответствует​»столбец»​ эта статья была​Fairuza​ определённым именем и​.​ итоговые ячейки, можно​ перед ссылкой на​

Коды форматов функции ЯЧЕЙКА

​ на другом листе​ поле​ Применение пользовательского формата​ Excel.​ значение FALSE (ЛОЖЬ)​ возвращает ссылку в​ по функции​ следующей таблицы и​ если в качестве​ тексту, выровненному влево,​

​1, если форматированием ячейки​​ помогла ли она​ пожалуйста еще раз.​ определённым именем и​и нажмите кнопку​ позволят увидеть итоговые​!​Изменение ссылки на ячейку​

Пример

​или щелкните нужный​Примечание:​ используем функцию​=ADDRESS($C$2,$C$3,1,FALSE)​ то в стиле​ ней. Если у​ отобразить результаты формул,​ на ячейку, отформатированную​ тексту, выровненному по​ предусмотрено изменение цвета​ вам, с помощью​дано 3 листа​ считать её строкой​

См. также:

​.​
​ книги на ее​ примере функция​
​ на ячейку​
​ или чисел.​ значениями кажутся пустыми,​(АДРЕС), чтобы получить​

support.office.com

30 функций Excel за 30 дней: АДРЕС (ADDRESS)

​Последний аргумент – это​​.​ информация или примеры,​​ нажмите клавишу F2,​ числового формата.​ черта () — тексту,​​ во всех остальных​​ Для удобства также​ накладная и этикетки)​ взять значение ячейки​Нажмите клавишу ВВОД или,​ первом листе.​​СРЗНАЧ​​Изменение ссылки на ячейку​​Совет:​​ их значения отображаются​

​ её адрес.​ имя листа. Если​sheet​​ пожалуйста, делитесь ими​​ а затем —​Формат Microsoft Excel​ распределенному по всей​ случаях — 0 (ноль).​ приводим ссылку на​нужно: когда вносишь​Добавлено через 3 минуты​ в случае формула​Для упрощения ссылок на​используется для расчета​

​ на именованный диапазон​ Для отмены выделения ячеек​​ в строка формул,​​Функция​ Вам необходимо это​_text​ в комментариях.​ клавишу ВВОД. При​Значение, возвращаемое функцией ЯЧЕЙКА​ ширине ячейки, а​

Функция 20: ADDRESS (АДРЕС)

​Примечание:​​ оригинал (на английском​​ информацию в лист​вообще нужен её​ массива, клавиши CTRL+SHIFT+ВВОД.​ ячейки между листами​ среднего значения в​Изменение типа ссылки: относительная,​ щелкните любую ячейку​ в которой с​​MAX​​ имя в полученном​​(имя_листа) – имя​​Функция​ необходимости измените ширину​Общий​ пустой текст («») —​

Как можно использовать функцию ADDRESS (АДРЕС)?

​ Это значение не поддерживается​​ языке) .​​ ФИО,​ аналог в таблицах​К началу страницы​ и книгами. Команда​ диапазоне B1:B10 на​

• ​ абсолютная, смешанная​ в таблице.​ ними можно работать.​
• ​(МАКС) находит максимальное​ результате, укажите его​ листа может быть​
• ​ADDRESS​ столбцов, чтобы видеть​

Синтаксис ADDRESS (АДРЕС)

​»G»​​ любому другому содержимому​​ в Excel Online,​В этой статье описаны​

​1. Листы этикетки​
​ гугл​

• ​Если после ввода ссылки​​Ссылки на ячейки​ листе «Маркетинг» в​​Щелкните ячейку, в которую​​ссылка на ячейку указывает​Выделите ячейку или диапазон​ число в столбце​ в качестве аргумента​ указано, если Вы​(АДРЕС) возвращает ссылку​​ все данные.​​0​​ ячейки.​​ Excel Mobile и​​ синтаксис формулы и​​ и накладные брали​
• ​Добавлено через 25 минут​​ на ячейку в​автоматически вставляет выражения​ той же книге.​ нужно ввести формулу.​ на ячейку или​​ ячеек, содержащий значения,​​ C.​sheet_text​​ желаете видеть его​​ на ячейку в​
• ​Данные​​»F0″​​Примечание:​ Excel Starter.​ использование функции ячейка​ информацию с ФИО​https://docs.google.com/spreadsheets…it?usp=sharing​ формулу задается имя​

Ловушки ADDRESS (АДРЕС)

​ с правильным синтаксисом.​​Ссылка на диапазон​​В строка формул​ диапазон ячеек листа.​ которые требуется скрыть.​=MAX(C3:C8)​(имя_листа).​ в возвращаемом функцией​ виде текста, основываясь​​75​​# ##0​ Это значение не поддерживается​»содержимое»​ в Microsoft Excel.​

Пример 1: Получаем адрес ячейки по номеру строки и столбца

​ и склад.​​AlexM​​ для ссылки на​Выделите ячейку с данными,​ ячеек на другом​введите​ Ссылки можно применять​ Дополнительные сведения читайте​=МАКС(C3:C8)​=ADDRESS($C$2,$C$3,1,TRUE,»Ex02″)​ результате.​ на номере строки​​Привет!​​»,0″​

​ в Excel Online,​
​Значение левой верхней ячейки​

Абсолютная или относительная

​ Вы найдете ссылки​на листе накладные​​: формула для Е3​​ ячейку, иногда требуется​ ссылку на которую​ листе той же​

​=​ в формула, чтобы​ в статье Выбор​Далее в игру вступает​​=АДРЕС($C$2;$C$3;1;ИСТИНА;»Ex02″)​​Функция​​ и столбца. Она​​Формула​

​0,00​
​ Excel Mobile и​

A1 или R1C1

​ в ссылке; не​​ на дополнительные сведения​​ — при значении​ Код =VLOOKUP(C3;’Лист2′!A$2:E$6;MATCH(A3;’Лист2′!A$1:E$1;0);0)​​ заменить существующие ссылки​​ необходимо создать.​ книги​(знак равенства).​​ указать приложению Microsoft​​ ячеек, диапазонов, строк​

​ функция​
​Функция​

Название листа

​ADDRESS​ может возвращать абсолютный​Описание​»F2″​ Excel Starter.​ формула.​​ о форматировании данных​​ склад Заставская -​

​Colibris​
​ на ячейки определенными​

Пример 2: Находим значение ячейки, используя номер строки и столбца

​Нажмите сочетание клавиш CTRL+C​​1. Ссылка на лист​​Выполните одно из указанных​ Office Excel на​ или столбцов на​ADDRESS​ADDRESS​(АДРЕС) возвращает лишь​ или относительный адрес​Результат​​# ##0,00​​»защита»​»имяфайла»​​ в ячейках и​​ в следующей ячейку​: О!​​ именами.​​ или перейдите на​ «Маркетинг».​​ ниже действий.​ значения или данные,​​ листе.​

​(АДРЕС) в сочетании​
​(АДРЕС) возвращает адрес​

​ адрес ячейки в​​ в стиле ссылок​​=ЯЧЕЙКА(«строка»;A20)​»,2″​​0, если ячейка разблокирована,​​Имя файла (включая полный​ применения стилей ячеек​ вставлять ФИО заказчика​​что-то работает​​Выполните одно из указанных​ вкладку​​2. Ссылка на диапазон​​Создайте ссылку на одну​ которые нужно использовать​

​Примечание:​
​ с​

​ ячейки в виде​​ виде текстовой строки.​​A1​Номер строки ячейки A20.​$# ##0_);($# ##0)​ и 1, если​

​ путь), содержащего ссылку,​
​ в разделе​

​ (формулу нашла, а​​щас буду разбираться​ ниже действий.​Главная​

Пример 3: Возвращаем адрес ячейки с максимальным значением

​ ячеек с B1​ или несколько ячеек​ в формуле.​ Выделенные ячейки будут казаться​​MATCH​​ текста, а не​ Если Вам нужно​

​или​​20​​»C0″​ ячейка заблокирована.​ в виде текстовой​

​См​
​ как ее можно​

​ в этих функциях​Выделите диапазон ячеек, содержащих​​и в группе​​ по B10 включительно.​   . Чтобы создать ссылку, выделите​​Ссылки на ячейки можно​​ на листе пустыми,​(ПОИСКПОЗ), которая находит​​ как действующую ссылку.​​ значение ячейки, используйте​R1C1​

​=ЯЧЕЙКА("содержимое";A3)​
​$# ##0_);[Красный]($# ##0)​

​Примечание:​ строки. Если лист,​
​.​
​ доработать не понимаю​

​Спасибо!​

office-guru.ru

Скрытие и отображение значений ячеек

​ формулы, в которых​​Буфер обмена​3. Ссылка на лист,​ ячейку или диапазон​ использовать в одной​ но при щелчке​ номер строки, и​ Если Вам нужно​ её в качестве​. К тому же​Содержимое ячейки A3.​»C0-«​ Это значение не поддерживается​ содержащий ссылку, еще​ЯЧЕЙКА Функция возвращает сведения​ и можно ли​всё прекрасно работает!​ необходимо заменить ссылки​нажмите кнопку​ отделенная от ссылки​ ячеек на том​ или нескольких формулах​

​ одной из ячеек​COLUMN​ получить значение ячейки,​ аргумента функции​ в результат может​Привет!​$# ##0,00_);($# ##0,00)​ в Excel Online,​ не был сохранен,​ о форматировании, расположении​ вообще? что бы​ещё раз спасибо​ на ячейки определенными​Копировать​ на диапазон значений.​ же листе.​

​ для указания на​​ в строке формул​(СТОЛБЕЦ), которая определяет​ можно использовать результат,​INDIRECT​ быть включено имя​=ЯЧЕЙКА(«тип»;A2)​

Скрыть значение ячейки

1. ​»C2″​ Excel Mobile и​ возвращается пустая строка​ или содержимом ячейки.​ при другом значении​ AlexM!​ именами.​.​

   ​Щелкните ячейку, в которую​​Можно переместить границу выделения,​ следующие элементы:​ отобразится значение.​ номер столбца.​ возвращаемый функцией​(ДВССЫЛ) или примените​

2. ​ листа.​​Тип данных ячейки A2.​​$# ##0,00_);[Красный]($# ##0,00)​​ Excel Starter.​ ​ («»).​​ Например, если перед​​ не ставило ЛОЖЬ,​

   

3. ​закрываю тему​​Чтобы заменить ссылки именами​​Нажмите сочетание клавиш CTRL+V​​ нужно ввести формулу.​​ перетащив границу ячейки,​

4. ​данные из одной или​​На вкладке​​=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))​

5. ​ADDRESS​​ одну из альтернативных​​Функция​ Тип данных «v»​

6. ​»C2-«​​»строка»​​Примечание:​

​ выполнением вычислений с​​ а брал следующее​Elena1884​ во всех формулах​

Отображение значений скрытых ячеек

1. ​ или перейдите на​В строка формул​ или перетащить угол​ нескольких смежных ячеек​Главная​=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))​(АДРЕС), как аргумент​

2. ​ формул, показанных в​​ADDRESS​​ указывает значение.​​0%​ ​Номер строки ячейки в​​ Это значение не поддерживается​​ ячейкой необходимо удостовериться​

   

3. ​ такое же значение)​: Уважаемые форумчане, очень​ листа, выделите одну​​ вкладку​​введите​​ границы, чтобы расширить​​ на листе;​нажмите кнопку​Урок подготовлен для Вас​

​ для​​ примере 2.​(АДРЕС) может возвратить​v​

support.office.com

Создание и изменение ссылки на ячейку

​»P0″​ аргументе «ссылка».​ в Excel Online,​ в том, что​ и в графу​ нужна ваша помощь.​ пустую ячейку.​Главная​=​ выделение.​

​данные из разных областей​Вызова диалогового окна​ командой сайта office-guru.ru​INDIRECT​При помощи функции​

• ​ адрес ячейки или​Изменение формата ячейки​0,00%​

• ​»тип»​ Excel Mobile и​

• ​ она содержит числовое​ телефон вставлять его​

​тем полно перерыла,​

​ данных в ячейке.​

​»формат»​ текст, можно использовать​2. Лист наклейки​

​ в этой области​в группе​

​нажмите кнопку​ использовать.​   . Чтобы создать ссылку на​

​ той же книги.​.​Автор: Антон Андронов​

​INDIRECT​ получить адрес ячейки​

​ чтобы:​функция адрес​

Создание ссылки на ячейку на том же листе

1. ​»S2″​ Значение «b» соответствует​

2. ​Текстовое значение, соответствующее числовому​​ следующую формулу:​​ брали информацию ФИО​​ не хватает, помочь​

3. ​Определенные имена​Вставить​

   • ​Щелкните ярлычок листа, на​ определенное имя, выполните​​Например:​В списке​Примечание:​(ДВССЫЛ) позже в​

     ​ в виде текста,​Получить адрес ячейки, зная​Добавление, изменение, поиск​# ?/? или #​ пустой ячейке, «l»​

   • ​ формату ячейки. Значения​=​​ и порядковый номер​ себе не смогла,​щелкните стрелку рядом​.​

     • ​ который нужно сослаться.​

     • ​ одно из указанных​Формула​​Числовые форматы​​Мы стараемся как​​ рамках марафона​​ используя номер строки​

       ​ номер строки и​​ и удаление условного​ ??/??​ — текстовой константе​ для различных форматов​ЕСЛИ(​

4. ​ по накладной.​ надеюсь на вашу​

   • ​ с кнопкой​По умолчанию при вставке​Выделите ячейку или диапазон​

   • ​ ниже действий.​Объект ссылки​выберите пункт​ можно оперативнее обеспечивать​

     ​30 функций Excel за​ и столбца. Если​ столбца.​ форматирования в ячейке​»G»​

​ в ячейке, «v» —​

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

​ показаны ниже в​ЯЧЕЙКА(«тип», A1) = «v»;​Надеюсь понятно написала.​ помощь.​Присвоить имя​ скопированных данных отображается​ ячеек, на которые​Введите имя.​​Возвращаемое значение​​(все форматы)​ вас актуальными справочными​​ 30 дней​​ Вы введёте только​Найти значение ячейки, зная​Вчера в марафоне​д.М.гг или дд.ММ.гг Ч:мм​ любому другому содержимому.​

​ таблице. Если ячейка​ A1 * 2;​очень надеюсь на​Нужно, что бы​

​и выберите команду​ кнопка​

​ нужно сослаться.​Нажмите клавишу F3, выберите​=C2​

​.​ материалами на вашем​.​

1. ​ эти два аргумента,​ номер строки и​

2. ​30 функций Excel за​​ или дд.ММ.гг​​»ширина»​​ изменяет цвет при​ 0)​ вашу помощь.​

3. ​ при проставлении №​Применить имена​

4. ​Параметры вставки​Примечание:​ имя в поле​

​Ячейка C2​​Выберите коды в поле​ языке. Эта страница​=INDIRECT(ADDRESS(C2,C3))​ результатом будет абсолютный​ столбца.​ 30 дней​​»D4″​​Ширина столбца ячейки, округленная​

​ выводе отрицательных значений,​

Создание ссылки на ячейку с помощью команды «Ссылки на ячейки»

​Эта формула вычисляет произведение​Ребята подскажите пожалуйста,​ дефектной ведомости (лист2)​.​​.​​ Если имя другого листа​Вставить имя​Значение в ячейке C2​Тип​ переведена автоматически, поэтому​

• ​=ДВССЫЛ(АДРЕС(C2;C3))​ адрес, записанный в​Возвратить адрес ячейки с​мы находили элементы​Д МММ ГГ или​ до целого числа.​ в конце текстового​ A1*2, только если​ реально ли? если​ ячейка с наименованием​Выберите имена в поле​Нажмите кнопку​ содержит знаки, не​и нажмите кнопку​=A1:F4​.​ ее текст может​Функция​

• ​ стиле ссылок​ самым большим значением.​ массива при помощи​​ ДД МММ ГГ​​ Единица измерения равна​ значения добавляется «-«.​

1. ​ в ячейке A1​ да, то как?​ брала информацию с​

2. ​Применить имена​Параметры вставки​ являющиеся буквами, необходимо​​ОК​​Ячейки A1–F4​​Тип​​ содержать неточности и​​INDIRECT​ ​A1​

   

3. ​Функция​ функции​»D1″​​ ширине одного знака​​ Если положительные или​​ содержится числовое значение,​​Elena1884​​ листа №1.​ ​, а затем нажмите​

   ​, а затем выберите​ заключить имя (или​.​​Значения во всех ячейках,​ ​;;​

4. ​ грамматические ошибки. Для​​(ДВССЫЛ) может работать​​.​ADDRESS​​MATCH​ ​д.м, или дд.ммм, или​

​ для шрифта стандартного​

Изменение ссылки на ячейку на другую ссылку на ячейку

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

2. ​Примечание:​ но после ввода​

   • ​(три точки с​ нас важно, чтобы​ и без функции​=ADDRESS($C$2,$C$3)​(АДРЕС) имеет вот​

   • ​(ПОИСКПОЗ) и обнаружили,​ Д МММ​ размера.​

   • ​ в круглых скобках,​ 0, если в​ о чем речь,​ № 1, в​​ОК​

   • ​Вставить связь​ кавычки (​​ Если в углу цветной​​ формулы необходимо нажать​​ запятой).​​ эта статья была​

3. ​ADDRESS​=АДРЕС($C$2;$C$3)​ такой синтаксис:​

​ что она отлично​

Изменение ссылки на ячейку на именованный диапазон

​»D2″​Примечание:​ в конце текстового​ ячейке A1 содержится​ но чтобы убрать​ ячейки наименование поставилась​.​.​

1. ​’​ границы нет квадратного​

   • ​ сочетание клавиш Ctrl+Shift+Enter.​Нажмите кнопку​ вам полезна. Просим​(АДРЕС). Вот как​Если не указывать значение​

   • ​ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])​ работает в команде​ммм.гг, ммм.гггг, МММ ГГ​ Это значение не поддерживается​

2. ​ значения добавляется «()».​​ текст или она​​ ЛОЖЬ исправьте формулу​​ информация с листа1​​К началу страницы​К началу страницы​​).​​ маркера, значит это​​=Актив-Пассив​​ОК​

   

3. ​ вас уделить пару​​ можно, используя оператор​​ аргумента​АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])​​ с другими функциями,​​ или МММ ГГГГ​

​ в Excel Online,​

Изменение типа ссылки: относительная, абсолютная, смешанная

1. ​Примечание:​

2. ​ пустая.​​ на Код =ЕСЛИ(‘ФИО​ с п/п 1.​

3. ​Выделите ячейку с формулой.​Дважды щелкните ячейку, содержащую​К началу страницы​

​ ссылка на именованный​Ячейки с именами «Актив»​.​ секунд и сообщить,​

​ конкатенации «​

support.office.com

Функция: взять значение из ячейки

​abs_num​​abs_num​ такими как​»D3″​ Excel Mobile и​ Это значение не поддерживается​ЯЧЕЙКА(тип_сведений;[ссылка])​
​ и склад’!A2=»Склад на​ наименование. тоже самое​В строке формул строка формул​ формулу, которую нужно​
​Также можно скопировать и​ диапазон.​ и «Пассив»​Совет:​
​ помогла ли она​&​
​(тип_ссылки) в формуле,​
​(тип_ссылки) – если​VLOOKUP​дд.мм​
​ Excel Starter.​
​ в Excel Online,​

​Аргументы функции ЯЧЕЙКА описаны​​ Заставской (везу от​ с годом и​

​выделите ссылку, которую​​ изменить. Каждая ячейка​
​ вставить ссылку на​
​Выполните одно из указанных​Разность значений в ячейках​
​ Для отмены выделения ячеек​
​ вам, с помощью​
​«, слепить нужный адрес​ то результатом будет​
​ равно​

CyberForum.ru

Значение ячейки в зависимости от значения другой ячейки

​(ВПР) и​​»D5″​Ссылка​
​ Excel Mobile и​ ниже.​ 10 чел)»;’ФИО и​ причиной списания.​ нужно изменить.​ или диапазон ячеек​ ячейку, а затем​
​ ниже действий.​ «Актив» и «Пассив»​ щелкните любую ячейку​ кнопок внизу страницы.​ в стиле​ абсолютная ссылка.​
​1​INDEX​ч:мм AM/PM​     — необязательный аргумент.​ Excel Starter.​Тип_сведений​ склад’!B2;»») Зачем объединяете​надеюсь понятно объяснила​
​Для переключения между типами​

​ в Excel, на​​ воспользоваться командой​
​Если требуется создать ссылку​{=Неделя1+Неделя2}​

​ в таблице.​​ Для удобства также​R1C1​

​Чтобы увидеть адрес в​​или вообще не​(ИНДЕКС).​
​»D7″​ Ячейка, сведения о​»скобки»​

​     — обязательный аргумент.​​ ячейки?​​smeckoi77​ ​ ссылок нажмите клавишу​​ которые ссылается формула,​

​Ссылки на ячейки​​ в отдельной ячейке,​Диапазоны ячеек «Неделя1» и​
​Выделите ячейку или диапазон​ приводим ссылку на​и в результате​
​ виде относительной ссылки,​ указано, то функция​20-й день нашего марафона​
​ч:мм:сс AM/PM​ которой требуется получить.​1, если форматированием ячейки​ Текстовое значение, задающее​
​И еще раз​: =ИНДЕКС(‘Заявка на списание’!B9:B713;$BP$6)​ F4.​ выделяются своим цветом.​для создания ссылки​ нажмите клавишу ВВОД.​ «Неделя2»​ ячеек, содержащий значения,​ оригинал (на английском​ получить значение ячейки:​ можно подставить в​ возвратит абсолютный адрес​ мы посвятим изучению​»D6″​ Если этот аргумент​ предусмотрено отображение положительных​ тип сведений о​
​ предлагаю посмотреть в​Это где наименование,​Дополнительные сведения о разных​Выполните одно из указанных​
​ на ячейку. Эту​
​Если требуется создать ссылку​Сумма значений в диапазонах​

​ которые скрыты. Дополнительные​ языке) .​=INDIRECT(«R»&C2&»C»&C3,FALSE)​​ качестве аргумента​​ ($A$1). Чтобы получить​ функции​ч:мм​ опущен, сведения, указанные​ или всех чисел​ ячейке при возвращении.​ сторону инструмента Слияние​ вместо Вашей формулы​ типах ссылок на​ ниже действий.​
​ команду можно использовать​ в формула массива​ ячеек «Неделя1» и​

CyberForum.ru

​ сведения читайте в​

Очень часто при работе в Excel необходимо использовать данные об адресации ячеек в электронной таблице. Для этого была предусмотрена функция ЯЧЕЙКА. Рассмотрим ее использование на конкретных примерах.

Функция значения и свойства ячейки в Excel

Стоит отметить, что в Excel используются несколько функций по адресации ячеек:

• – СТРОКА;
• – СТОЛБЕЦ и другие.

Функция ЯЧЕЙКА(), английская версия CELL(), возвращает сведения о форматировании, адресе или содержимом ячейки. Функция может вернуть подробную информацию о формате ячейки, исключив тем самым в некоторых случаях необходимость использования VBA. Функция особенно полезна, если необходимо вывести в ячейки полный путь файла.

Как работает функция ЯЧЕЙКА в Excel?

Функция ЯЧЕЙКА в своей работе использует синтаксис, который состоит из двух аргументов:

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

1. Тип_сведений – текстовое значение, задающее требуемый тип сведений о ячейке. При вводе функции вручную высвечивается выпадающий список где указаны все возможные значения аргумента «тип сведений»:



2. Ссылка – необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе тип_сведений, возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА() возвращает сведения только для левой верхней ячейки диапазона.



Примеры использования функции ЯЧЕЙКА в Excel

Пример 1. Дана таблица учета работы сотрудников организации вида:

Необходимо с помощью функции ЯЧЕЙКА вычислить в какой строке и столбце находится зарплата размером 235000 руб.

Для этого введем формулу следующего вида:

тут:

• – «строка» и «столбец» – параметр вывода;
• – С8 – адрес данных с зарплатой.

В результате вычислений получим: строка №8 и столбец №3 (С).

Как узнать ширину таблицы Excel?

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

Примечание. Высота строк и ячеек в Excel по умолчанию измеряется в единицах измерения базового шрифта – в пунктах pt. Чем больше шрифт, тем выше строка для полного отображения символов по высоте.

Введем в ячейку С14 формулу для вычисления суммы ширины каждого столбца таблицы:

тут:

• – «ширина» – параметр функции;
• – А1 – ширина определенного столбца.

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

Пример 3. В условии примера 1 нужно вывести содержимое только из первой (верхней левой) ячейки из диапазона A5:C8.

Введем формулу для вычисления:

Скачать примеры функции ЯЧЕЙКА в Excel

Описание формулы аналогичное предыдущим двум примерам.

Иногда бывает необходимо с помощью формул узнать о какой-либо ячейке подробную информацию и параметры, чтобы использовать это в расчетах. Например, выяснить число или текст в ячейке или какой числовой формат в ней установлен. Сделать это можно, используя функцию ЯЧЕЙКА (CELL).

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

=ЯЧЕЙКА(Параметр; Адрес)

где Адрес — это, понятное дело, ссылка на нужную ячейку, а вот Параметр — это кодовое слово, определяющее какую именно информацию о ячейке мы хотим получить. У этого аргумента несколько возможных значений:

Давайте рассмотрим пару трюков с применением этой функции на практике.

Например, можно получить имя текущего листа формулой, используя функцию ЯЧЕЙКА с параметром «имяфайла» и извлекающей все символы правее закрывающей квадратной скобки:

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

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

Ссылки по теме

• Включение / выключение подсветки незащищенных ячеек макросом
• Условное форматирование в Excel