Auditportal.ru

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

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

Как в Excel указать ссылки на одну и ту же ячейку, но с разных листов (Функция ДВССЫЛ)

Один из читателей нашего сайта SirExcel задал вопрос, который может встречаться очень часто. Вопрос заключался в следующем « Как сделать так, чтобы на одном листе показывалась информация из определенной ячейки к примеру $A$1 но с каждого листа из книги? Листов очень много надо сделать сводный список содержимого определенной ячейки со всех листов.»

Я как раз собирался написать о том, как работает функция DVSSYL, так почему бы не посмотреть на это на примере.

Теперь у нас есть большой файл Excel, содержащий множество листов. Давайте рассмотрим простой пример, в котором мы не меняем имена листов. То есть у нас есть Лист1, Лист2, Лист3 и так далее.

Рассмотрим 5 разных магазинов на 5 разных листах (Sheet1-Sheet5) с одинаковыми таблицами данных на каждом из них.

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

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

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

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

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

Одним из решений этой проблемы может быть простой макрос, который можно записать с помощью макрокодера. Наше решение заключается в использовании функции DVSSYL.

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

Следующий пример поможет вам понять, как работает эта функция.

Посмотрите на рисунок ниже. В ячейке А1 написан текст D4, а в самой ячейке D4 указано число 9999. Если мы напишем формулу ДВССЫЛ(A1), то в результате получим число 9999. То же самое мы получим если напишем формулу так:
=ДВССЫЛ(«D4»)
То есть мы написали текстом адрес ячейки D4 и функция ДВССЫЛ вернула нам то значение, которое находится по данному адресу (D4).

Пример работы ДВССЫЛ

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

=Fiche1!B2 (Январские рецепты из семинара 1)

Лист, отражающий выручку за январь по магазину 2 и т.д.

Заполняем таблицу с ДВВСЫЛ

Это сложно, поскольку если просто перетащить ячейку вниз, Sheet1 не превратится в Sheet2.

Если мы пропишем формулу
ДВCСЫЛ(«Лист1!B2»), ДВCСЫЛ(«Лист2!B2»)
и так далее, то функция будет возвращать нам то же самое, но это так же не решает нашу проблему, ведь «Лист1!B2» прописан обычным текстом и так же не будет изменяться при протягивании.

Для решения нашей задачи мы разделим текст «Лист1!B2» на две части (отдельно «Лист1» и отдельно «!B2» — обратите внимание на восклицательный знак во второй части текста) и потом их склеим. Текст Лист1, Лист2 и так далее мы пропишем напротив соответствующих магазинов, при этом написав Лист1, мы сможем просто протянуть ячейку вниз и автоматически получить список Лист1, Лист2 и так далее.

Читайте так же:
Как найти и заменить текст, а затем сохранить форматирование в Excel?

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

Протягиваем формулу с ДВССЫЛ

Итоговая формула выглядит именно так, но я специально расписал с самого начала по шагам, чтобы было понятно из чего состоит данная формула. В принципе, этого уже достаточно, чтобы за короткий срок решить задачу, указанную в начале статьи. Чтобы заполнить данные за февраль, нам необходимо в ячейке D2 написать такую же формулу, но поменять текст «!B2» на «!B3»
=ДВССЫЛ(A2&»!B3″)
и протянуть вниз, аналогично за март.

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

Итак, мы решили задачу, как быстро заполнить данные за определенный месяц, но нам необходимо заполнить данные за остальные месяца. Мы знаем, что данные за Январь соответствуют ячейке B2, февраль — B3, Март — B4. То есть буква «B» остается неизменной, меняется лишь цифра. Зная это мы просто разобьем текст внутри ссылки ДВССЫЛ не на 2 части, а на три. Добавим дополнительно строку над таблицей и напишем сверху цифры соответствующие ячейки месяца. Пропишем формулу.

=ДВССЫЛ( A3 &» !B «& C1 ), где A3 — это текст « Лист1 «, « !B » — это неизменный текст и С1 — это цифра 2 . Все это объединено с помощью знака & в общий текст « Лист1 !B 2 «. Нам также потребуется закрепить столбец A (с помощью знака $), чтобы он не менялся при протягивании формулы вправо и строку 1, чтобы она не менялась при протягивании формулы вниз. Мы получаем следующую итоговую формулу.

=ДВССЫЛ($A3&»!B»&C$1)
которую можно протянуть вправо и вниз.

Как работает функция ДВССЫЛ

Примечания по теме:

  • Если бы мы назвали листы Shop 1, Shop 2 и т.д., нам не понадобился бы дополнительный столбец со словами Shop1, Shop2 и т.д.
  • Функция DVSSYL часто используется, когда вы хотите изменить ссылку на ячейку в формуле без изменения самой формулы.

Если вам понравилась статья, пожалуйста, нажмите +1 и «Мне нравится». Так же подписывайтесь на нашу рассылку или вступайте в нашу группу ВКонтакте, чтобы не пропустить наши следующие уроки по Excel

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

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

Читайте так же:
Как легко преобразовать футы в дюймы, мили и метры в Excel?

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

Типы ссылок

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

  1. Относительная ссылка указывает на значение ячейки, которая находится на одинаковом расстоянии от нее самой. Когда вы переместите такую ссылку на один столбец вправо, ее значение будет соответствовать значению ячейки, которая находится на один столбец правее исходной ссылки. Microsoft Office Excel обычно использует этот тип ссылки по умолчанию.
  2. Абсолютная ссылка показывает значение той же ячейки в исходных данных. Он создается путем корректировки списка формул. Для этого перед номерами столбцов и строк в ссылке ставится символ $. Если знак доллара ставится только перед номером столбца/строки, его значения будут изменяться в указанных столбце/строке исходных данных по мере перемещения ссылки.
  3. Смешанная ссылка включает оба способа использования исходных данных.

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

Процесс создания ссылки на ячейку в другом файле

  1. Открываем исходный файл Excel, в котором проводится работа, и второй, данные которого необходимо использовать в текущем файле.
  2. В исходном выделяем ячейку, в которой требуется создать ссылку, то есть использовать значения другого документа.
  3. Вводим в ячейку знак равенства. Если с данными ссылки необходимо выполнить вычисления, то далее вносится функция либо другие значения, которые должны предшествовать значению ссылки.
  4. Переходим ко второму файлу с необходимыми нам данными, выбираем лист документа, содержащий ячейки, на которые нужно сослаться.
  5. Выделяем ячейку или группу ячеек, на данные которых требуется создать ссылку.

excel ссылка на ячейку в другом файле

ссылка на ячейку в другом файле

Внешний вид Excel ссылки на ячейку в другом файле

  1. Ссылка на значение одной ячейки: =[Источник.xls]Sheet1!A1
  2. Ссылка на диапазон ячеек: =[Источник.xls]Sheet1!A1:B5

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

При ссылке на закрытые файлы необходимо указать путь к ним в ссылке:

ссылка на ячейку другого файла

Обновление данных в файле

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

Если данные ячеек исходного файла изменяются, когда документ со ссылкой закрыт, то при следующем открытии этого файла программа выдаст сообщение с предложением обновить связи. Следует согласиться и выбрать пункт «Обновить». Таким образом, при открытии документа ссылка использует обновленные данные исходного файла, а пересчет формул с ее использованием произойдет автоматически.

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

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

Вот пример того, как я отображаю ширину столбца:

Я бы предпочел формулу, подобную этой:

12 ответов

  • Как абсолютная ссылка на ячейку в gnuplot

С помощью абсолютной ссылки на ячейку в Excel вы можете ссылаться на любую ячейку. Например, вы можете использовать $a$1 для ссылки на ячейку A1. Есть ли эквивалентный способ сделать то же самое в gnuplot? Спасибо.

Я сталкиваюсь с ошибкой, когда пытаюсь установить ячейку Excel на текущую дату каждый раз, когда кто-то вносит изменения в другие ячейки в электронной таблице. Вот код: Dim EditDate As Date RowNum = ActiveCell.Row ColNum = ActiveCell.Column If ColNum = 11 Or ColNum = 13 Or ColNum = 19 Or ColNum = 20 Or ColNum =.

Опоздали на несколько лет:

Чтобы дополнить картину, я хотел бы дать еще один ответ:

Сначала перейдите в Excel-Параметры ->Формулы и включите ссылки R1C1 . Затем используйте

R C относится к текущей строке и текущему столбцу, т. е. «эта ячейка».

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

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

На текущем листе выделите ячейку A1 (важно!).

Откройте диспетчер имен (Ctl+F3)

Введите "THIS_CELL" (или просто "THIS",, что я предпочитаю) в Name:

Введите следующую формулу в поле Reference (Ссылка):

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

В разделе Scope: выберите Workbook .

Нажмите OK и закройте Диспетчер имен.

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

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

Следует отметить, что приведенное мной решение предпочтительнее любого решения с использованием INDIRECT(), по двум причинам

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

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

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

  • Ссылка на конкретный лист/ячейку в Sharepoint Excel

У меня есть рабочая тетрадь Excel, загруженная на мой сайт Sharepoint. Я хочу создать ссылку http на этот Excel, но я хочу указать на конкретный лист и ячейку, поэтому Excel открывается в этой позиции. По следующей ссылке я нашел, как ссылаться на лист: http://support.microsoft.com/kb/197922 Итак.

Интересно, можете ли вы мне помочь? У меня есть документ Excel с двумя листами, «данные» и «вывод». Я использую ссылку на ячейку =data!A1 . Но я бы хотел, чтобы он продвигался на десять строк, например, =data!A11 каждый раз при обновлении Excel (под обновлением я имею в виду каждый раз.

Мне бы пригодилось

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

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

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

Без INDIRECT(): =CELL(«width», OFFSET($A$1,ROW()-1,COLUMN()-1) )

Вот что я нашел, что лучше всего подходит для меня (для меня):

Надеюсь, что это будет полезно.

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

Поместите его в колонку [Баланс], если у вас есть две колонки [Изменение] и [Баланс]:

Обратите внимание, конечно, что это зависит от порядка строк (как и большинство других решений), поэтому оно немного неустойчиво.

Более того, есть более простой способ, который безопаснее и не замедляет работу приложения. Исходя из того, как устроен Excel, ячейка может содержать либо значение, либо формулу. Формула не может содержать ссылку на собственную ячейку. Новое значение вызовет новое вычисление, и так далее. Добавьте в электронную таблицу вспомогательный столбец, чтобы вычислить значение на основе данных другой ячейки. например:

В колонке A — истина или ложь; в колонке B — денежное выражение; в колонке C — формула: = B1

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

Затем можно скрыть колонку C

Полностью заслуга верхнего ответа @rick-teachey, но вы можете расширить этот подход для работы с условным форматированием. Чтобы завершить этот ответ, я повторю ответ Рика в краткой форме, а затем уточню его:

  1. Выберите ячейку A1 на любом листе.
  2. Создайте именованный диапазон с именем THIS и установите значение Refers to: в значение =!A1 .

При попытке использовать ЭТО в формулах условного форматирования произойдет ошибка:

Если вы хотите, чтобы THIS работал в формулах условного форматирования

  1. Создайте другой именованный диапазон с именем THIS_CF и установите значение Refers to: в значение =THIS .

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

Вы также можете использовать эту технику для создания других относительных именованных диапазонов, таких как THIS_COLUMN , THIS_ROW , ROW_ABOVE , и COLUMN_LEFT.

И ЗМЕНЕНИЕ: следующее неверно, поскольку cell(«width») возвращает длину последней измененной ячейки.

Cell(«width») возвращает ширину текущей ячейки, поэтому вам не нужна ссылка на текущую ячейку. Однако, если вам это нужно, cell(«address») возвращает адрес текущей ячейки, поэтому, если вам нужна ссылка на текущую ячейку, используйте indirect(cell(«address»)) . См. документацию: http://www.techonthenet.com/excel/ формулы/ячейка.php

Похожие вопросы:

Мне нужен умный способ split ссылки на ячейку Excel, чтобы получить имя листа и ссылку на ячейку. Как правило, ссылка Excel может принимать следующую форму: =Sheet1!$A$1 —> Лист1, $A$1 =’Лист.

Можно ли скопировать созданный combobox и вставить его в другую ячейку так, чтобы ссылка на ячейку также изменилась? Я выполняю эту работу в Excel 2007. Пример: у меня есть combobox в A5, ссылка на ячейку.

Я пытаюсь сравнить двоичные значения между двумя рабочими листами в Excel. Вот быстрый взгляд на то, как могут выглядеть данные для строки 1, столбцов A-D в двух рабочих листах. A B C D 1 1 1 1 1 <--- Лист 1 A B.

В excel абсолютная ссылка на ячейку означает, что вы можете ссылаться на любую желаемую ячейку. Например, для ссылки на ячейку A1 можно использовать $a$1. есть ли эквивалентный способ сделать то же.

Когда я пытаюсь установить текущую дату в ячейке excel, когда кто-то изменяет другие ячейки на листе, я получаю ошибку. Код следующий: Dim EditDate As Date RowNumber = ActiveCell. Row.

У меня есть рабочая тетрадь Excel, загруженная на мой сайт Sharepoint. Я хочу создать ссылку http на этот Excel, но я хочу указать на конкретный лист и ячейку, поэтому Excel открывается в этой.

Хотел бы узнать, можете ли вы мне помочь. ‘data’ и ‘output’ — это вкладки в документе Excel. Я использую ссылку на ячейку =data!A1 . Однако я хотел бы, чтобы десять строк продвигались вперед.

Он прост в использовании и поставляется из Yahoo Finance. Когда я нажимаю CTRL-ALT-F9, ячейки обновляются и все данные заполняются.

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector