Загрузите образец книги
В этом руководстве показано, как использовать Функция КОСВЕННО в Excel в Excel, чтобы создать ссылку на ячейку из текста.
Косвенный обзор функции
Функция INDIRECT Создает ссылку на ячейку из текстовой строки.
(Обратите внимание, как появляются входные данные формулы)
Функция INDIRECT Синтаксис и входные данные:
| 1 | = ДВССЫЛ (ссылочный_текст; C1) |
ref_text - Строка, представляющая ссылку на ячейку или ссылку на диапазон. Строка может быть в формате R1C1 или A1 или может быть именованным диапазоном.
а1 - ДОПОЛНИТЕЛЬНО: указывает, в каком формате ссылка находится: R1C1 или A1. ЛОЖЬ для R1C1 или ИСТИНА / Не разрешено для A1.
Что такое КОСВЕННАЯ функция?
Функция ДВССЫЛ позволяет задать текстовую строку и заставить компьютер интерпретировать эту строку как фактическую ссылку. Это можно использовать для ссылки на диапазон на том же листе, другом листе или даже в другой книге.
ВНИМАНИЕ: КОСВЕННАЯ функция - одна из непостоянных функций. В большинстве случаев, когда вы работаете с таблицей, компьютер будет пересчитывать формулу только в том случае, если входные данные изменили свои значения. Однако изменчивая функция пересчитывает каждый раз вы вносите изменения в любую ячейку. Следует проявлять осторожность, чтобы не допустить увеличения времени пересчета из-за чрезмерного использования энергозависимой функции или наличия большого количества ячеек, зависящих от результата энергозависимой функции.
Создать ссылку на ячейку
Скажем, вы хотите получить значение из A2, но хотите убедиться, что ваша формула остается на A2 независимо от того, добавляются / удаляются новые строки. Вы можете написать формулу
| 1 | = КОСВЕННО («A2») |
Обратите внимание, что аргументом внутри нашей функции является текстовая строка «A2», а не ссылка на ячейку. Кроме того, поскольку это текстовая строка, нет необходимости указывать абсолютную ссылку, например $ A $ 2. Текст никогда не изменится, и поэтому эта формула всегда будет указывать на A2, независимо от того, куда она была перемещена.
КОСВЕННЫЙ номер строки
Вы можете объединить текстовые строки и значения из ячеек вместе. Вместо того, чтобы писать «A2», как мы делали ранее, мы можем взять числовое значение из ячейки B2 и использовать его в нашей формуле. Мы бы выписали формулу вида
| 1 | = КОСВЕННО («А» и В2) |
Здесь символ «&» используется для объединения текстовой строки «A» со значением из ячейки B2. Итак, если бы значение B2 в настоящее время было 10, то наша формула читала бы это как
| 123 | = КОСВЕННО ("A" & 10)= КОСВЕННО («A10»)= A10 |
КОСВЕННОЕ значение столбца
Вы также можете объединить ссылку на столбец. На этот раз предположим, что мы знаем, что хотим получить значение из строки 10, но мы хотим иметь возможность изменить, из какого столбца извлекать данные. Мы поместим нужную букву столбца в ячейку B2. Наша формула может выглядеть так
| 1 | = КОСВЕННО (B2 & "10") |
Если значение B2 равно «G», то наша формула имеет следующий вид.
| 123 | = КОСВЕННО ("G" & 10)= КОСВЕННО ("G10")= G10 |
КОСВЕННЫЙ стиль r1c1
В нашем предыдущем примере нам пришлось использовать букву для обозначения ссылки на столбец. Это потому, что мы использовали так называемое реферирование в стиле A1. В стиле A1 столбцы обозначаются буквой, а строки - числами. Абсолютные ссылки указываются с помощью символа «$» перед элементом, который мы хотим оставить абсолютным.
В r1c1 и строки, и столбцы начинаются с числа. Абсолютная ссылка на a1 будет записана как
| 1 | = R1C1 |
Вы можете прочитать это как «Строка 1, Столбец 1». Относительные ссылки даются в скобках, но число указывает положение относительно ячейки с формулой. Итак, если бы мы писали формулу в ячейке A10 и нам нужно было ссылаться на A1, мы бы написали формулу
| 1 | = R [-9] C |
Вы можете прочитать это как «Ячейка на 9 строк вверх, но в том же столбце.
Причина, по которой это может быть полезно, заключается в том, что INDIRECT может поддерживать использование нотации r1c1. Рассмотрим предыдущий пример, в котором мы извлекали значение из строки 10, но хотели иметь возможность изменить столбец. Вместо того, чтобы давать букву, предположим, что мы поместили число в ячейку B2. Тогда наша формула может выглядеть так:
| 1 | = КОСВЕННО ("R10C" & B2; ЛОЖЬ) |
Мы опустили 2nd аргумент до сих пор. Если этот аргумент опущен или True, функция будет оценивать, используя стиль A1. Поскольку это False, он будет оцениваться в r1c1. Предположим, значение B2 равно 5. Наша формула оценит это так:
| 12 | = КОСВЕННО ("R10C5"; ЛОЖЬ)= $ E $ 10 |
КОСВЕННЫЕ различия с A1 против r1c1
Помните, мы ранее показали, что, поскольку содержимое этой формулы было текстовой строкой, оно никогда не менялось?
| 1 | = КОСВЕННО («A2») |
Эта формула всегда будет смотреть на ячейку A2, независимо от того, куда вы переместите формулу. В r1c1, поскольку вы можете указывать относительное положение с помощью скобок, это правило не соблюдается. Если вы поместите эту формулу в ячейку B2
| 1 | = КОСВЕННО ("RC [-1]") |
Он будет смотреть на ячейку A2 (поскольку столбец A находится слева от столбца B). Если вы скопируете эту формулу в ячейку B3, текст внутри останется прежним, но КОСВЕННО теперь будет смотреть на ячейку A3.
КОСВЕННО с именем листа
Вы также можете объединить имя листа в свои КОСВЕННЫЕ ссылки. Важное правило, о котором следует помнить, заключается в том, что вы должны заключать имена в одинарные кавычки, и вам нужно отделить имя листа от ссылки на ячейку восклицательным знаком.
Допустим, у нас есть такая настройка, в которой мы указываем имя нашего листа, строку и столбец.
Наша формула для объединения всего этого в ссылку будет выглядеть так:
| 1 | = КОСВЕННО ("'" & A2 & "'!" & B2 & C2) |
Наша формула будет оцениваться следующим образом:
| 123 | = КОСВЕННО ("'" & "Лист2" & "'!" & "B" & "5")= КОСВЕННО ("'" Лист2'! B5 ")= 'Лист2'! B5 |
Технически, поскольку в слове «Лист2» нет пробелов, мы не необходимость одинарные кавычки. Вполне допустимо написать что-то вроде
| 1 | = Лист2! A2 |
Однако не помешает поставить кавычки, когда они вам не нужны. Лучше всего включать их, чтобы ваша формула могла обрабатывать экземпляры там, где они могут понадобиться.
КОСВЕННО к другой книге
Мы также отметим, что INDIRECT может создавать ссылку на другую книгу. Ограничение заключается в том, что INDIRECT не извлекает значения из закрытой книги, поэтому это конкретное использование имеет ограниченную практичность. Если книга, на которую указывает INDIRECT, не открыта, функция выдаст сообщение «#REF!» ошибка.
Синтаксис при написании имени книги заключается в том, что оно должно быть в квадратных скобках. Давайте воспользуемся этой настройкой и попробуем получить значение из ячейки C7.
Наша формула была бы
| 1 | = КОСВЕННО ("'[" & A2 & "]" & B2 & "'! C7") |
Опять же, обратите внимание на размещение одинарных кавычек, скобок и восклицательного знака. Наша формула будет оцениваться следующим образом:
| 123 | = КОСВЕННО ("'[" & "Sample.xlsx" & "]" & "Сводка" & "'! C7")= КОСВЕННО ("'[Sample.xslx] Сводка'! C7")= '[Sample.xlsx] Сводка'! C7 |
КОСВЕННО для создания динамического диапазона
Если у вас большой набор данных, важно попытаться оптимизировать формулы, чтобы они не выполняли больше работы, чем необходимо. Например, вместо того, чтобы ссылаться на весь столбец A, мы могли бы просто указать точное количество ячеек в нашем списке. Рассмотрим следующий макет:
В ячейку B2 мы поместили формулу
| 1 | = СЧЁТ (A: A) |
Компьютер может легко вычислить функцию COUNTA, поскольку она просто проверяет, сколько ячеек в столбце A имеют какое-либо значение, вместо того, чтобы выполнять какие-либо логические проверки или математические операции.
Теперь давайте создадим нашу формулу, которая суммирует значения в столбце A, но мы хотим убедиться, что она учитывает только точный диапазон значений (A2: A5). Запишем нашу формулу как
| 1 | = СУММ (КОСВЕННО ("A2: A" & B2)) |
Наш INDIRECT собирается взять число 5 из ячейки B2 и создаст ссылку на диапазон A2: A5. Затем СУММ может использовать этот диапазон для своего расчета. Если мы добавим другое значение в ячейку A6, тогда число в B2 обновится, и наша формула SUM также будет автоматически обновляться, чтобы включить это новое значение.
ВНИМАНИЕ. С появлением таблиц в Office 2007 гораздо эффективнее хранить данные в таблице и использовать структурную ссылку, а не строить формулу, которую мы использовали в этом примере, из-за изменчивой природы INDIRECT. Однако могут быть случаи, когда вам нужно создать список элементов, но нельзя использовать таблицу.
Динамическое построение графиков с КОСВЕННЫМ
Возьмем предыдущий пример и сделаем еще один шаг. Вместо того, чтобы писать формулу для получения суммы значений, мы создадим именованный диапазон. Мы могли бы назвать этот диапазон «MyData» и указать,
| 1 | = КОСВЕННО («A2: A» & COUNTA ($ A: $ A)) |
Обратите внимание: поскольку мы помещаем это в именованный диапазон, мы поменяли местами ссылку на B2 и вместо этого поместили туда функцию COUNTA.
Теперь, когда у нас есть этот именованный диапазон, мы можем использовать его в диаграмме. Мы создадим пустую линейную диаграмму, а затем добавим ряд данных. Для значений серии вы можете написать что-то вроде
| 1 | = Лист1! МоиДанные |
Теперь диаграмма будет использовать эту ссылку для построения значений. По мере добавления большего количества значений в столбец A, КОСВЕННЫЙ будет относиться к все большему и большему диапазону, и наша диаграмма будет продолжать обновляться со всеми новыми добавленными значениями.
Проверка динамических данных с помощью INDIRECT
При сборе информации от пользователей иногда возникает необходимость сделать один вариант выбора в зависимости от предыдущего выбора. Рассмотрим этот макет, где наш первый столбец позволяет пользователю выбирать между фруктами, овощами и мясом.
В 2nd столбец, мы не хотим иметь большой список, показывающий все возможные варианты, поскольку мы уже немного сузили круг вопросов. Итак, мы создали еще 3 списка, которые выглядят так:
Затем мы назначим каждому из эти списки в именованный диапазон. То есть все фрукты будут в диапазоне под названием «Фрукты», а овощи - в диапазоне «Овощи» и т. Д.
Вернувшись к нашей таблице, мы готовы настроить проверку данных в 2nd столбец. Мы создадим проверку типа списка с вводом:
| 1 | = КОСВЕННО (A2) |
КОСВЕННЫЙ будет читать в выборе, сделанном в столбце A, и видеть название категории. Мы определили диапазоны с этими именами, поэтому INDIRECT затем возьмет это имя и создаст ссылку на желаемый диапазон.
Дополнительные замечания
Используйте функцию ДВССЫЛ, чтобы создать ссылку на ячейку из текста.
Сначала создайте строку текста, представляющую ссылку на ячейку. Строка должна быть либо в виде буквы и номера строки в обычном стиле A1 (M37), либо в стиле R1C1 (R37C13). Вы можете ввести ссылку напрямую, но обычно вы будете ссылаться на ячейки, которые определяют строки и столбцы. Наконец, введите выбранный вами формат ссылки на ячейку. TRUE или опущено для ссылки в стиле A1 или FALSE для стиля R1C1.
При работе с НЕПРЯМЫМИ формулами вы можете использовать СТРОКА Функция чтобы получить номер строки ссылки или COLUMN Функция чтобы получить номер столбца (не букву) ссылки.
Вернуться к списку всех функций в Excel
КОСВЕННЫЙ в Google Таблицах
Функция КОСВЕННО работает в Google Таблицах точно так же, как и в Excel:
