Bazaprogram.ru

Новости из мира ПК
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel ссылка на текущую ячейку

Ссылка Excel На Текущую Ячейку

Как получить ссылку на текущую ячейку?

например, если я хочу отобразить ширину столбца A, я мог бы использовать следующее:

однако, я хочу, чтобы формула была примерно такой:

11 ответов:

создайте именованную формулу с именем THIS_CELL

  1. в текущем листе выберите ячейку A1 (это важно!)
  2. открыть Name Manager (Ctl+F3)
  3. клик New.
  4. введите » THIS_CELL «(или просто «это», что является моим предпочтением) в Name:

введите следующую формулу в Refers to:

Примечание: убедитесь, что ячейка A1 выбрана. Эта формула является относительно активной ячейки.

под Scope: выберите Workbook .

  • клик OK закрыть Name Manager
  • используйте формулу на листе точно так, как вы хотели

    EDIT: лучшее решение, чем использование INDIRECT()

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

    1. он энергонезависим, в то время как INDIRECT() является изменчивой функцией Excel, и в результате резко замедлит расчет книги, когда он используется много.
    2. это гораздо проще, и не требует преобразования адреса (в виде ROW() COLUMN() ) к ссылке диапазона на адрес и обратно к ссылке диапазона снова.

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

    EDIT: см. Также @и imix это ниже для вариации этой идеи (с использованием ссылок на стиль RC). В этом случае вы можете использовать =!RC на THIS_CELL именованная формула диапазона, или просто использовать RC напрямую.

    вы могли бы использовать

    несколько лет слишком поздно:

    просто для полноты картины хочу дать еще один ответ:

    во-первых, перейти к Excel-Options ->Формулы и включения ссылки R1C1. Тогда используйте

    RC всегда ссылается на текущую строку, текущий столбец, т. е.»эта ячейка».

    решение Рика Тичи это в основном настройка, чтобы сделать то же самое возможно в A1 эталонный стиль (см. также GSerg это!—18—> к ответу и записке Джоуи комментарии на ответ Патрика Макдональда).

    =ADDRESS(ROW(),COLUMN(),4) даст нам относительный адрес текущей ячейки. =INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)) даст нам содержимое ячейки слева от текущей ячейки =INDIRECT(ADDRESS(ROW()-1,COLUMN(),4)) даст нам содержимое ячейки над текущей ячейкой (отлично подходит для расчета текущих итогов)

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

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

    без косвенных(): =CELL(«width», OFFSET($A,ROW()-1,COLUMN()-1) )

    внутри таблицы вы можете использовать [@] , который (к сожалению) Excel автоматически расширяет до Table1[@] но это действительно работает. (Я использую Excel 2010)

    например, при наличии двух столбцов [Change] и [Balance] , поставив в

    Я нашел лучший способ справиться с этим (для меня) использовать следующее:

    надеюсь, что это помогает.

    есть лучший способ, который является более безопасным и не замедлит ваше приложение. Как Excel настроен, ячейка может иметь либо значение, либо формулу; формула не может ссылаться на свою собственную ячейку. Вы в конечном итоге с бесконечным циклом, так как новое значение приведет к другому вычислению. . Используйте вспомогательный столбец для вычисления значения на основе того, что вы помещаете в другую ячейку. Например:

    столбец A является истинным или ложным, столбец B содержит денежное значение, столбец C содержит следующее формула: =B1

    теперь, чтобы вычислить, что столбец B будет выделен желтым цветом в условном формате, только если столбец A истинен, а столбец B больше нуля.

    затем вы можете скрыть столбец C

    EDIT: следующее неверно, потому что ячейка («ширина») возвращает ширину последние изменения клеток.

    Зачем нужен стиль ссылок R1C1

    «У меня в Excel, в заголовках столбцов листа появились цифры (1,2,3…) вместо обычных букв (A,B,C…)! Все формулы превратились в непонятную кашу с буквами R и С! Что делать. Помогите!»

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

    Что это

    Классическая и всем известная система адресации к ячейкам листа в Excel представляет собой сочетание буквы столбца и номера строки — морской бой или шахматы используют ту же идею для обозначения клеток доски. Третья сверху во втором столбце ячейка, например, будет иметь адрес B3. Иногда такой стиль ссылок еще называют «стилем А1». В формулах адреса могут использоваться с разным типом ссылок: относительными (просто B3), абсолютными ($B$3) и смешанного закрепления ($B3 или B$3). Если с долларами в формулах не очень понятно, то очень советую почитать тут про разные типы ссылок, прежде чем продолжать.

    Однако же, существует еще и альтернативная малоизвестная система адресации, называемая «стилем R1C1». В этой системе и строки и столбцы обозначаются цифрами. Адрес ячейки B3 в такой системе будет выглядеть как R3 C2 (R=row=строка, C=column=столбец). Относительные, абсолютные и смешанные ссылки в такой системе можно реализовать при помощи конструкций типа:

    • R C — относительная ссылка на текущую ячейку
    • R2 C2 — то же самое, что $B$2 (абсолютная ссылка)
    • R C5 — ссылка на ячейку из пятого столбца в текущей строке
    • R C[-1] — ссылка на ячейку из предыдущего столбца в текущей строке
    • R C[2] — ссылка на ячейку, отстоящую на два столбца правее в той же строке
    • R[2] C[-3] — ссылка на ячейку, отстоящую на две строки ниже и на три столбца левее от текущей ячейки
    • R5 C[-2] — ссылка на ячейку из пятой строки, отстоящую на два столбца левее текущей ячейки
    • и т.д.
    Читать еще:  Внешние данные excel

    Ничего суперсложного, просто слегка необычно.

    Как это включить/отключить

    Мало кто использует этот режим осознанно. Обычно он случайно включается сам, например, при открытии кривых выгрузок из 1С в Excel и в некоторых других ситуациях. Отключить его совсем несложно. Самый простой путь:

    В Excel 2007/2010: кнопка Офис (Файл) — Параметры Excel — Формулы — Стиль ссылок R1C1 (File — Excel Options — Formulas — R1C1-style)


    В Excel 2003 и старше: Сервис — Параметры — Общие — Стиль ссылок R1C1 (Tools — Options — General — R1C1-style)


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

    Можно сохранить его в личную книгу макросов и повесить на кнопку на панели инструментов или на сочетание клавиш (как это сделать описано тут).

    Где это может быть полезно

    А вот это правильный вопрос. Если звезды зажигают, то это кому-нибудь нужно. Есть несколько ситуаций, когда режим ссылок R1C1 удобнее, чем классический режим А1:

      При проверке формул и поиске ошибок в таблицах иногда гораздо удобнее использовать режим ссылок R1C1, потому что в нем однотипные формулы выглядят не просто похоже, а абсолютно одинаково. Сравните, например, одну и ту же таблицу в режиме отладки формул (CTRL+

    ) в двух вариантах адресации:

    Найти ошибку в режиме R1C1 намного проще, правда?

    • Если большая таблица с данными на вашем листе начинает занимать уже по нескольку сотен строк по ширине и высоте, то толку от адреса ячейки типа BT235 в формуле немного. Видеть номер столбца в такой ситуации может быть гораздо полезнее, чем его же буквы.
    • Некоторые функции Excel, например ДВССЫЛ (INDIRECT) могут работать в двух режимах — A1 или R1C1. И иногда оказывается удобнее использовать второй.
    • В коде макросов на VBA часто гораздо проще использовать стиль R1C1 для ввода формул в ячейки, чем классический A1. Так, например, если нам надо сложить два столбца чисел по десять ячеек в каждом (A1:A10 и B1:B10,) то мы могли бы использовать в макросе простой код:

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

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

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

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

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

    данные из одной или нескольких смежных ячеек на листе;

    данные из разных областей листа;

    данные на других листах той же книги.

    Значение в ячейке C2

    Значения во всех ячейках, но после ввода формулы необходимо нажать сочетание клавиш Ctrl+Shift+Enter.

    Ячейки с именами «Актив» и «Пассив»

    Разность значений в ячейках «Актив» и «Пассив»

    Диапазоны ячеек «Неделя1» и «Неделя2»

    Сумма значений в диапазонах ячеек «Неделя1» и «Неделя2» как формула массива

    Ячейка B2 на листе Лист2

    Значение в ячейке B2 на листе Лист2

    Щелкните ячейку, в которую нужно ввести формулу.

    В поле строка формул введите = (знак равенства).

    Выполните одно из указанных ниже действий.

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

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

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

    Читать еще:  Не открывается видео с видеорегистратора

    Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.

    Примечание: Если в углу цветной границы нет квадратного маркера, значит это ссылка на именованный диапазон.

    Выполните одно из указанных ниже действий.

    Если требуется создать ссылку в отдельной ячейке, нажмите клавишу ВВОД.

    Если требуется создать ссылку в формула массива (например A1:G4), нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

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

    Примечание: Если у вас установлена текущая версия Office 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

    На ячейки, расположенные на других листах в той же книге, можно сослаться, вставив перед ссылкой на ячейку имя листа с восклицательным знаком ( !). В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же книге.

    1. Ссылка на лист «Маркетинг».

    2. Ссылка на диапазон ячеек с B1 по B10 включительно.

    3. Ссылка на лист, отделенная от ссылки на диапазон значений.

    Щелкните ячейку, в которую нужно ввести формулу.

    В поле строка формул введите = (знак равенства) и формулу, которую вы хотите использовать.

    Щелкните ярлычок листа, на который нужно сослаться.

    Выделите ячейку или диапазон ячеек, на которые нужно сослаться.

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

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

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

    Для упрощения ссылок на ячейки между листами и книгами. Команда Ссылки на ячейки автоматически вставляет выражения с правильным синтаксисом.

    Выделите ячейку с данными, ссылку на которую необходимо создать.

    Нажмите клавиши CTRL + C или перейдите на вкладку Главная и в группе буфер обмена нажмите кнопку Копировать .

    Нажмите клавиши CTRL + V или перейдите на вкладку Главная , в группе буфер обмена нажмите кнопку Вставить .

    По умолчанию при вставке скопированных данных появляется кнопка Параметры вставки .

    Нажмите кнопку Параметры вставки , а затем выберите команду Вставить связь .

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

    Выполните одно из указанных ниже действий.

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

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

    В строка формул выделите ссылку в формуле и введите новую ссылку .

    Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.

    Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.

    Примечание: Если у вас установлена текущая версия Office 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

    Выполните одно из указанных ниже действий.

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

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

    На вкладке Формулы в группе Определенные имена щелкните стрелку рядом с кнопкой Присвоить имя и выберите команду Применить имена.

    Выберите имена в поле Применить имена, а затем нажмите кнопку ОК.

    Выделите ячейку с формулой.

    В строке формул строка формул выделите ссылку, которую нужно изменить.

    Для переключения между типами ссылок нажмите клавишу F4.

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

    Читать еще:  Excel строку в число функция

    Дополнительные сведения

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

    Функция ЯЧЕЙКА() в 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 пересчитывает только книги открытые в текущем экземпляре.

    Другие возможности функции ЯЧЕЙКА() : определение типа значения, номера столбца или строки, мало востребованы, т.к. дублируются стандартными функциями ЕТЕКСТ() , ЕЧИСЛО() , СТОЛБЕЦ() и др.

    Ссылка на основную публикацию
    Adblock
    detector