Excel присвоить значение ячейке из другого листа
Как ссылаться на конкретное значение ячейки из предыдущего листа при создании копии в Excel? Например, при создании копии Sheet2 вы можете захотеть автоматически ссылаться на ячейку A1 Sheet2 на новый скопированный рабочий лист (говорит Sheet3). Как этого добиться? Эта статья вам поможет.
Использовать / ссылочное значение из предыдущего рабочего листа с пользовательской функцией
Честно говоря, ни один метод не может автоматически ссылаться на определенное значение ячейки при создании копии рабочего листа. Фактически, вы можете создать новый лист, а затем ссылаться на значение ячейки из предыдущего листа с помощью следующей пользовательской функции.
1. После создания нового пустого листа (говорит Sheet3), нажмите другой + F11 в то же время, чтобы открыть Microsoft Visual Basic для приложений окно.
2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модули. Затем скопируйте и вставьте следующий код VBA в окно кода.
VBA: использовать / ссылочное значение из предыдущего листа в Excel
3. нажмите другой + Q клавиши одновременно, чтобы закрыть Microsoft Visual Basic для приложений окно.
4. Выберите пустую ячейку Sheet3 (говорит A1), введите формулу = PrevSheet (A1) в Панель формул и затем нажмите Enter ключ.
Теперь вы получите значение ячейки A1 предыдущего листа (Sheet2) на текущем листе.
Внимание: Код автоматически идентифицирует рабочий лист, который принадлежит предыдущему из текущего рабочего листа.
Используйте / ссылочное значение из предыдущего рабочего листа с Kutools for Excel
Для Динамически обращаться к рабочим листам полезности Kutools for Excel, вы можете легко использовать или ссылаться на значение из предыдущего листа в Excel.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Если вы хотите связать значение ячейки A1 из предыдущего листа с текущим листом, выберите ячейку A1 в текущем листе и нажмите Кутулс > Еще > Динамически обращаться к рабочим листам. Смотрите скриншот:
2. в Заполнить рабочие листы Ссылки диалоговом окне, проверьте только предыдущее имя рабочего листа в Список рабочих листов поле, а затем щелкните Диапазон заполнения кнопку.
Затем вы можете увидеть, что значение ячейки A1 на предыдущем листе указано в текущем листе.
Внимание: С помощью этой утилиты вы можете одновременно ссылаться на одно и то же значение ячейки из разных листов в текущий лист.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Возможность использования значений с других листов Excel позволяет получать информацию в упрощенном виде. Другими словами, Вы можете на одном листе написать много значений, а вычисления по данным значениям проводить на другом листе.
Как лично я использую данную опцию в работе? Считаю свою зарплату :) В конце каждого месяца приходит расчетный лист, я делаю ссылку на него и вывожу все необходимые данные на один общий лист с подсчетами.
Так что пользуйтесь на здоровье в любых целях! Главное, чтобы Вам это пригодилось! А лист с зарплатой показывать не буду :) Но поверьте он есть!
Разберем простой пример.
В прошлой статье по графикам в Excel я показал таблицу. Чтобы не придумывать новые значения, просто воспользуемся ей. Она расположена на листе 4 моего документа.
1. Создадим новый лист (Лист5 в моем случае). Кто не знает как это делать - нажать на плюс возле последнего листа в вашем документе. Для удобства напишем какие данные будем выводить:
2. Чтобы получить значение заданной ячейки из листа 4, нужно выбрать любую ячейку, поставить знак " = ", внизу нажать на лист4 и выбрать нужную ячейку.
Я решил немного усложнить задачу и не просто выбрать одну ячейку, а найти сумму значений нескольких ячеек. Для этого необходимо в пустой ячейке начать писать " =СУММ( " и уже после этого перейти на лист4 :
3. Попадаем на лист 4, находим нужный массив чисел и выделяем его. Вверху автоматически формируется формула:
Разберем формулу для тех кто не понял:
- Знак равно означает начало функции. Так программа понимает, что необходимо будет выполнить какое-то действие.
- Слово " СУММ " означает, что необходимо будет выполнить сложение для выражения, указанного в круглых скобках.
- Слово " Лист4! " означает, что данные будут браться именно с Листа 4, ведь на других листах есть ячейки с такими же названиями.
- Слова " C21:I21 " - это диапазон значений, то есть программа начнет с первой указанной ячейки и будет повторять действие до последней указанной ячейки.
Нажимаем " Enter " и программа переносит нас к результату:
Теперь мы знаем сумму данных за указанный период (1941г). Аналогичным образом я сделал для 1944 года:
Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.
В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:
Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.
Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:
Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».
Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:
Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.
Обращение к ячейке по индексу
К ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз.
Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе, зависящему от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее.
Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.
По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):
Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2") .
Обращение к ячейке по имени
Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.
Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого") .
Запись информации в ячейку
Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:
Как известно, VBA обрабатывает информацию в массивах значительно быстрее, чем в ячейках рабочего листа Excel. Поэтому, при работе с большими объемами данных, удобнее использовать массивы, чем наблюдать во время выполнения кода за мерцанием изображения на экране или просто смотреть в неизменную картинку, если обновление экрана отключено (Application.ScreenUpdating = False). Здесь обмен значениями между массивом и диапазоном ячеек будет вполне уместен.
Копирование значений из диапазона ячеек в массив
Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (As Variant) и присвоить ей значения диапазона ячеек с помощью оператора присваивания (=):
VBA Excel автоматически преобразует объявленную переменную в двумерный массив, соответствующий размерности диапазона ячеек, в нашем случае в массив — a(1 To 3, 1 To 3), и заполняет его значениями. Нумерация измерений массивов, созданных таким образом, начинается с единицы (1).
Можно, в этом случае, объявить сразу динамический массив, чтобы изначально указать, что эта переменная будет массивом. Так как свойством диапазона ячеек по умолчанию в VBA Excel является значение (Value), его можно в коде явно не указывать, но, при желании, можно и указать. Получится такая конструкция, аналогичная первой:
Копирование значений из массива в диапазон ячеек
Значения в диапазон ячеек добавляются из массива с помощью оператора присваивания (=):
Обратите внимание, что вставить значения в диапазон ячеек можно только из двумерного массива. Размерность такого массива может начинаться с нуля (0). Количество элементов в измерениях массива должно совпадать с количеством строк и столбцов в диапазоне ячеек. Если вам нужно вставить значения в одну строку или в один столбец, укажите размерность единственной строки или единственного столбца как (0) или (1 To 1), если вы хотите использовать нумерацию измерений своего массива с единицы. Например, для записи десяти значений из массива в одну строку можно объявить такой массив — massiv(9, 0), или в один столбец — massiv(0, 9).
Для вставки значений в диапазон ячеек из массива идеально подойдет массив, созданный для копирования в него значений из диапазона. В этом случае, данные с рабочего листа Excel переносятся в массив, обрабатываются и, после обработки, вставляются обратно в ту же или другую таблицу на том же или другом рабочем листе.
Обмен значениями между двумя диапазонами
Обмен значениями можно осуществить в VBA Excel не только между массивом и диапазоном, но и между двумя диапазонами одинаковой размерности:
Как получить или ссылаться на ячейку с другого листа в Excel?
В некоторых случаях вам может потребоваться получить или сослаться на конкретное значение ячейки из других листов и сделать эти связанные ячейки динамическими с изменениями ячейки, на которую указывает ссылка. В этой статье мы познакомим вас с двумя способами достижения этой цели.
Combine multiple worksheets/workbooks into one worksheet / workbook:
Combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge task in your daily work. But, if you have Kutools for Excel, its powerful utility – Combine can help you quickly combine multiple worksheets, workbooks into one worksheet or workbook
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Получение ячеек из нескольких листов одну за другой с помощью формулы
Например, вам нужно связать ячейку B15 из рабочего листа «Первая четверть» с текущим листом, следующая формула поможет вам легко добиться этого. Пожалуйста, сделайте следующее.
1. На текущем листе выберите пустую ячейку, в которую вы хотите получить значение ячейки B15 листа «Первая четверть», затем введите в нее формулу.
= ‘The First Quarter’!B15
Заметки:
1. В формуле «Первая четверть» - это имя листа, содержащего ячейку, которую необходимо указать на другой лист, B15 - это ячейка, на которую указывает ссылка. Пожалуйста, измените их в соответствии с вашими потребностями.
2. Если содержимое имени листа разделено пробелами, заключите имя листа в одинарные кавычки в формуле. Если в имени листа нет пробелов, просто введите имя листа без одинарных кавычек.
2. Если вы хотите получить или ссылаться на ячейки с нескольких листов на текущий лист, повторите описанные выше операции.
Быстро получать ячейки с нескольких листов одновременно с помощью Kutools for Excel
Если вы хотите быстро сослаться на одну и ту же ячейку (говорит B15) с нескольких листов на текущий, Динамически обращаться к рабочим листам полезности Kutools for Excel сэкономит ваше время.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Если вы хотите получить значения в одной и той же ячейке (например, B10) на нескольких листах в текущей книге и поместить эти значения на целевой рабочий лист, сначала выберите ячейку B10 на конечном рабочем листе.
2. Затем щелкните Кутулс > Еще > Динамически обращаться к рабочим листам.
3. в Заполнить рабочие листы Ссылки диалоговое окно необходимо настроить следующим образом:
3.2 Щелкните значок Формула блокировки в Формула заполнения раздел (здесь отображается адрес ячейки, выбранный на шаге 1);
Затем вы можете увидеть, что значения в той же ячейке B10 на выбранных листах ссылаются на целевой рабочий лист, как показано ниже.
3. Закрой Заполнить рабочие листы Ссылки диалоговое окно.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: