Где отображается адрес выделенной ячейки в excel
Функция ЯЧЕЙКА( ) , английская версия CELL() , возвращает сведения о форматировании, адресе или содержимом ячейки. Функция может вернуть подробную информацию о формате ячейки, исключив тем самым в некоторых случаях необходимость использования VBA. Функция особенно полезна, если необходимо вывести в ячейки полный путь файла.
Синтаксис функции ЯЧЕЙКА()
ЯЧЕЙКА(тип_сведений, [ссылка])
тип_сведений - Текстовое значение, задающее требуемый тип сведений о ячейке. В приведенном ниже списке указаны возможные значения аргумента тип_сведений и соответствующие результаты.
ссылка - Необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе тип_сведений , возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА() возвращает сведения только для левой верхней ячейки диапазона.
Тип_ сведений | Возвращаемое значение |
---|---|
"адрес" | Ссылка на первую ячейку в аргументе «ссылка» в виде текстовой строки. |
"столбец" | Номер столбца ячейки в аргументе «ссылка». |
"цвет" | 1, если ячейка изменяет цвет при выводе отрицательных значений; во всех остальных случаях — 0 (ноль). |
"содержимое" | Значение левой верхней ячейки в ссылке; не формула. |
"имяфайла" | Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка (""). |
"формат" | Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется «-». Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется «()». |
"скобки" | 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, и скучно щелкать каждую ячейку, чтобы просмотреть ссылку на нее. Здесь я расскажу о трех методах отображения всех ссылок на ячейки вместо значений и наоборот в текущем листе, а также для отображения указанных ссылок на ячейки в виде текста вместо значений и наоборот.
- Показывать всю ссылку на ячейку вместо значения и наоборот в указанном диапазоне
- Показывать всю ссылку на ячейку вместо значения и наоборот в активном листе
- Показывать все ссылки на ячейки вместо значений и наоборот на всех листах
Показывать все ссылки на ячейки вместо значений и наоборот с помощью функции Показать формулу
Мы можем быстро показать все ссылки на ячейки вместо вычисленных результатов формул, нажав Формулы > Показать формулу. Смотрите скриншот ниже:
Если вам нужно снова отобразить все рассчитанные результаты в ячейках, нажмите кнопку Показать формулу снова.
Показывать ссылку на ячейку в виде текста вместо значения и наоборот в указанном диапазоне
С другой стороны, некоторые пользователи могут захотеть отображать указанную ссылку на ячейку как текст вместо значений, но не все ссылки на все ячейки формулы. Здесь я рекомендую вам попробовать Kutools for Excel's Преобразовать формулу в текст полезности и Преобразовать текст в формулу утилита.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
Выделите ячейки формулы, в которых вы хотите отображать ссылки на ячейки в виде текста, а не результатов вычислений, и щелкните Кутулс > Content > Преобразовать формулу в текст.
Примечание: Напротив, для отображения вычисленных результатов вместо формул выберите эти формулы и нажмите Кутулс > Content > Преобразовать текст в формулу. Смотрите скриншот выше.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Показывать все ссылки на ячейки вместо значений и наоборот на всех листах
Как мы знаем, формулы> Показать формулы могут отображать / скрывать формулы только на активном листе. Чтобы показать или скрыть все ссылки на ячейки вместо значений или наоборот на всех листах, попробуйте Kutools for Excel's параметры просмотра функцию.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1, Нажмите Кутулс > Показать / Скрыть> Параметры просмотра. Смотрите скриншот ниже:
2. В открывшемся диалоговом окне «Параметры просмотра» установите флажок Формулы вариант, а затем щелкните Применить ко всем листам кнопки и Ok кнопку последовательно. Смотрите скриншот ниже:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демонстрация: показывать ссылку на ячейку (как текст) вместо значения и наоборот в Excel
Как определить и вернуть номер строки и столбца ячейки в Excel?
Обычно мы можем определить номер строки и номер столбца ячейки по ее адресу. Например, адрес A2 означает, что он находится в столбце 1 и строке 2. Но может быть немного сложно определить номер столбца ячейки NK60. И если у вас есть только адрес столбца или адрес строки ячейки, как определить номер строки или номер столбца? Эта статья покажет вам решения.
Если вы знаете только адрес, как вы можете определить номер строки и столбца?
Определить номер строки или столбца довольно легко, если вы знаете адрес ячейки.
Если адрес ячейки NK60, он показывает номер строки 60; и вы можете получить столбец с формулой = Столбец (NK60) .
Конечно, вы можете получить номер строки по формуле = Ряд (NK60) .
Если вы знаете только адрес столбца или строки, как вы можете определить номер строки или столбца?
Иногда вы можете знать значение в определенном столбце или строке и хотите определить его номер строки или номер столбца. Вы можете получить их с помощью функции Match.
Предположим, у вас есть таблица, как показано на следующем снимке экрана.
Предположим, вы хотите узнать номер строки «чернила"И вы уже знаете, что он находится в столбце A, вы можете использовать эту формулу = ПОИСКПОЗ ("чернила"; A: A; 0) в пустой ячейке, чтобы получить номер строки. После ввода формулы и нажатия клавиши Enter отобразится номер строки ячейки, содержащей "чернила".
Предположим, вы хотите узнать номер столбца «чернила"И вы уже знаете, что он находится в строке 4, вы можете использовать эту формулу = ПОИСКПОЗ ("чернила"; 4: 4,0) в пустой ячейке, чтобы получить номер строки. После ввода формулы и нажатия клавиши Enter отобразится номер строки ячейки, содержащей "чернила".
Выберите всю строку / столбец, если значения ячеек соответствуют определенному значению в Excel
Сравнение, чтобы вернуть номер строки значения столбца, если значение ячейки соответствует определенному значению, Kutools for Excel's Выбрать определенные ячейки Утилита предоставляет пользователям Excel другой выбор: выбрать всю строку или весь столбец, если значения ячеек соответствуют определенному значению в Excel. И номер строки в крайнем левом углу или буква столбца вверху будут выделены, как показано на скриншоте ниже. Легче и понятнее в работе! Полнофункциональная бесплатная 30-дневная пробная версия!
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Вчера в марафоне 30 функций Excel за 30 дней мы находили элементы массива при помощи функции MATCH (ПОИСКПОЗ) и обнаружили, что она отлично работает в команде с другими функциями, такими как VLOOKUP (ВПР) и INDEX (ИНДЕКС).
20-й день нашего марафона мы посвятим изучению функции ADDRESS (АДРЕС). Она возвращает адрес ячейки в текстовом формате, используя номер строки и столбца. Нужен ли нам этот адрес? Можно ли сделать то же самое с помощью других функций?
Давайте обратимся к сведениям по функции ADDRESS (АДРЕС) и изучим примеры работы с ней. Если у Вас есть дополнительная информация или примеры, пожалуйста, делитесь ими в комментариях.
Функция 20: ADDRESS (АДРЕС)
Функция ADDRESS (АДРЕС) возвращает ссылку на ячейку в виде текста, основываясь на номере строки и столбца. Она может возвращать абсолютный или относительный адрес в стиле ссылок A1 или R1C1. К тому же в результат может быть включено имя листа.
Как можно использовать функцию ADDRESS (АДРЕС)?
Функция ADDRESS (АДРЕС) может возвратить адрес ячейки или работать в сочетании с другими функциями, чтобы:
- Получить адрес ячейки, зная номер строки и столбца.
- Найти значение ячейки, зная номер строки и столбца.
- Возвратить адрес ячейки с самым большим значением.
Синтаксис ADDRESS (АДРЕС)
Функция ADDRESS (АДРЕС) имеет вот такой синтаксис:
- abs_num (тип_ссылки) – если равно 1 или вообще не указано, то функция возвратит абсолютный адрес ($A$1). Чтобы получить относительный адрес (A1), используйте значение 4. Остальные варианты: 2=A$1, 3=$A1.
- a1 – если TRUE (ИСТИНА) или вообще не указано, функция возвращает ссылку в стиле A1, если FALSE (ЛОЖЬ), то в стиле R1C1.
- sheet_text (имя_листа) – имя листа может быть указано, если Вы желаете видеть его в возвращаемом функцией результате.
Ловушки ADDRESS (АДРЕС)
Функция ADDRESS (АДРЕС) возвращает лишь адрес ячейки в виде текстовой строки. Если Вам нужно значение ячейки, используйте её в качестве аргумента функции INDIRECT (ДВССЫЛ) или примените одну из альтернативных формул, показанных в примере 2.
Пример 1: Получаем адрес ячейки по номеру строки и столбца
При помощи функции ADDRESS (АДРЕС) Вы можете получить адрес ячейки в виде текста, используя номер строки и столбца. Если Вы введёте только эти два аргумента, результатом будет абсолютный адрес, записанный в стиле ссылок A1.
Абсолютная или относительная
Если не указывать значение аргумента abs_num (тип_ссылки) в формуле, то результатом будет абсолютная ссылка.
Чтобы увидеть адрес в виде относительной ссылки, можно подставить в качестве аргумента abs_num (тип_ссылки) значение 4.
A1 или R1C1
Чтобы задать стиль ссылок R1C1, вместо принятого по умолчанию стиля A1, Вы должны указать значение FALSE (ЛОЖЬ) для аргумента а1.
Название листа
Последний аргумент – это имя листа. Если Вам необходимо это имя в полученном результате, укажите его в качестве аргумента sheet_text (имя_листа).
Пример 2: Находим значение ячейки, используя номер строки и столбца
Функция ADDRESS (АДРЕС) возвращает адрес ячейки в виде текста, а не как действующую ссылку. Если Вам нужно получить значение ячейки, можно использовать результат, возвращаемый функцией ADDRESS (АДРЕС), как аргумент для INDIRECT (ДВССЫЛ). Мы изучим функцию INDIRECT (ДВССЫЛ) позже в рамках марафона 30 функций Excel за 30 дней.
Функция INDIRECT (ДВССЫЛ) может работать и без функции ADDRESS (АДРЕС). Вот как можно, используя оператор конкатенации «&«, слепить нужный адрес в стиле R1C1 и в результате получить значение ячейки:
Функция INDEX (ИНДЕКС) также может вернуть значение ячейки, если указан номер строки и столбца:
1:5000 – это первые 5000 строк листа Excel.
Пример 3: Возвращаем адрес ячейки с максимальным значением
В этом примере мы найдём ячейку с максимальным значением и используем функцию ADDRESS (АДРЕС), чтобы получить её адрес.
Функция MAX (МАКС) находит максимальное число в столбце C.
Далее в игру вступает функция ADDRESS (АДРЕС) в сочетании с MATCH (ПОИСКПОЗ), которая находит номер строки, и COLUMN (СТОЛБЕЦ), которая определяет номер столбца.
Функция АДРЕС() возвращает текстовое значение в виде адреса ячейки.
Синтаксис функции
АДРЕС(номер_строки, номер_столбца, [тип_ссылки], [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 .
Возникает вопрос: "Зачем весь этот огород с функцией АДРЕС() ?". Дело в том, что существуют определенные задачи, в которых использование функции АДРЕС() очень удобно, например Транспонирование таблиц или Нумерация столбцов буквами или Поиск позиции ТЕКСТа с выводом значения из соседнего столбца.
Читайте также: