Excel присвоить значение ячейке из другого листа

Обновлено: 07.07.2024

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

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

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

Введите = (знак равенства).

Если вы хотите использовать функцию, например СУММ, введите ее имя и открываю скобки. Например, =СУММ(.

Перейдите в книгу-источник и щелкните книгу, содержаную ячейки, которые вы хотите связать.

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

Примечание: Если вы выберете несколько ячеек, например =[SourceWorkbook.xlsx]Лист1!$A$1:$A$10 и у вас есть текущая версия Microsoft 365,то можно просто нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формула должна быть введена как формула массива устаревшей, нажав CTRL+SHIFT+ВВОД. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Excel возвращается в 9-ю книгу и отображаются значения из нее.

Обратите внимание Excel возвращает ссылку с абсолютными ссылками ,поэтому если вы хотите скопировать формулу в другие ячейки, вам потребуется удалить знак доллара ($):

=[SourceWorkbook.xlsx]Лист1! $ A$1

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

Создание внешней ссылки на определенное имя в другой книге

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

Введите = (знак равенства).

Перейдите в книгу-источник и щелкните книгу, содержаную ячейки, которые вы хотите связать.

Нажмите F3, выберите имя, на которое будет ссылаться ссылка, и нажмитеввод.

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

Excel вы вернетесь к книге назначения и отобразит значения из именоваемого диапазона в книге-источнике.

Определение имени, содержаного внешнюю ссылку на ячейки в другой книге

Откройте 9-ю книгу и книгу-источник.

В книге назначения перейдите к пункту Формулы > определенные имена >определить имя.

В диалоговом окне Новое имя в поле Имя введите имя диапазона.

В поле Ссылки удалите содержимое, а затем храните курсор в поле.

Если вы хотите использовать имя функции, введите имя функции и наведите курсор в то место, где вы хотите использовать внешнюю ссылку. Например, введите =СУММ()и расположить курсор между скобами.

Перейдите в книгу-источник и щелкните книгу, содержаную ячейки, которые вы хотите связать.

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

Внешние ссылки особенно полезны, если не целесообразно хранить большие модели в одной книге.

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

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

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

Формулы с внешними ссылками на другие книги отображаются двумя способами в зависимости от того, является ли книга-источник (которая содержит данные в формулу) открытой или закрытой.

Когда источник открыт, внешняя ссылка включает имя книги в квадратных скобках([ ]), за которым следуют имя таблицы, восклицательный пункт(!) и ячейки, от которых зависит формула. Например, следующая формула добавляет ячейки C10:C25 из книги с именем Budget.xls.

Внешняя ссылка

Если источник не открыт, внешняя ссылка включает весь путь.

Внешняя ссылка

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

В формулах, которые ссылаются на определенное имя в другой книге, используется имя книги, за которым следуют восклицательный пункт (!) и имя. Например, следующая формула добавляет ячейки из книги "Продажи" Budget.xlsx.

Внешняя ссылка

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

Введите = (знак равенства).

Если вы хотите использовать функцию, например СУММ, введите ее имя и открываю скобки. Например, =СУММ(.

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

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

Примечание: Если выбрать несколько ячеек (=Лист1! A1:A10) и текущую версию Microsoft 365 ,после чего можно просто нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формула должна быть введена как формула массива устаревшей, нажав CTRL+SHIFT+ВВОД. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Excel вернуться на исходный и отобразить значения из исходного.

Создание внешней ссылки на ячейки в разных книгах

Откройте книгу, которая будет содержать внешнюю ссылку (9-ю книгу, также называемую книгой формул) и книгу с данными, с которых должна быть связь (книга-источник данных, также называемая книгой данных).

В книге-источнике выберите ячейку или ячейки, которые нужно связать.

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

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

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

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

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

Создание связи с книгой

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

Введите = (знак равенства).

Если вы хотите использовать функцию, например СУММ, введите ее имя и открываю скобки. Например, =СУММ(.

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

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

Excel вернуться на исходный и отобразить значения из исходного.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Как лично я использую данную опцию в работе? Считаю свою зарплату :) В конце каждого месяца приходит расчетный лист, я делаю ссылку на него и вывожу все необходимые данные на один общий лист с подсчетами.

Так что пользуйтесь на здоровье в любых целях! Главное, чтобы Вам это пригодилось! А лист с зарплатой показывать не буду :) Но поверьте он есть!

Разберем простой пример.

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

1. Создадим новый лист (Лист5 в моем случае). Кто не знает как это делать - нажать на плюс возле последнего листа в вашем документе. Для удобства напишем какие данные будем выводить:

2. Чтобы получить значение заданной ячейки из листа 4, нужно выбрать любую ячейку, поставить знак " = ", внизу нажать на лист4 и выбрать нужную ячейку.

Я решил немного усложнить задачу и не просто выбрать одну ячейку, а найти сумму значений нескольких ячеек. Для этого необходимо в пустой ячейке начать писать " =СУММ( " и уже после этого перейти на лист4 :

3. Попадаем на лист 4, находим нужный массив чисел и выделяем его. Вверху автоматически формируется формула:

Разберем формулу для тех кто не понял:

Знак равно означает начало функции. Так программа понимает, что необходимо будет выполнить какое-то действие.
Слово " СУММ " означает, что необходимо будет выполнить сложение для выражения, указанного в круглых скобках.
Слово " Лист4! " означает, что данные будут браться именно с Листа 4, ведь на других листах есть ячейки с такими же названиями.
Слова " C21:I21 " - это диапазон значений, то есть программа начнет с первой указанной ячейки и будет повторять действие до последней указанной ячейки.

Нажимаем " Enter " и программа переносит нас к результату:

Теперь мы знаем сумму данных за указанный период (1941г). Аналогичным образом я сделал для 1944 года:

Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.

В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:

Workbooks ( "Книга2.xlsm" ) . Sheets ( "Лист2" ) . Cells ( 5 , 3 ) Workbooks ( "Книга2.xlsm" ) . Sheets ( "Лист2" ) . Cells ( 5 , "C" )

Удобнее обращаться к ячейке через свойство рабочего листа 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 является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:

Чтобы скопировать значения из диапазона ячеек в массив, необходимо объявить переменную универсального типа (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 переносятся в массив, обрабатываются и, после обработки, вставляются обратно в ту же или другую таблицу на том же или другом рабочем листе.

Простейшие примеры обмена значениями

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

Пример 1

Заполнение двумерного массива значениями и и их присвоение диапазону ячеек на рабочем листе Excel:

Но, как ни странно, внутри процедуры Function работает метод Range.Replace, которым мы воспользуемся для изменения значений других ячеек из пользовательской функции.

Пример 1
Эта функция заменяет значение ячейки Cell1 на значение ячейки Cell2 увеличенное на 100. Сама функция размещается в третьей ячейке, чтобы не возникла циклическая ссылка.

В этом примере мы не присваиваем пользовательской функции значение, поэтому отображается значение по умолчанию – 0. Если объявить эту функцию как строковую: Function Primer1(Cell1 As Range, Cell2 As Range) as String , будет возвращена пустая строка.

Изменение значения ячейки C1 (Cell2) приведет к пересчету значения ячейки B1 (Cell1).

Попробуйте очистить или перезаписать ячейку B1 (Cell1), ничего не получится, так как функция Primer1 вновь перезапишет ее значением C1 (Cell2) + 100.

Метод Application.Volatile

Application.Volatile – это метод, который запускает пересчет функции при изменении значения любой ячейки рабочего листа, а не только той, которая присвоена объявленной в функции переменной. Метод Application.Volatile используется только в функциях.

Рассмотрим пересчет функции на следующем примере:

Пример 2

Function Primer2 ( Cell1 As Range , Cell2 As Range ) As String

Эта функция будет пересчитываться только при изменении значений ячеек B1 и C1, присвоенных переменным Cell1 и Cell2. При изменении значения ячейки C2, значение ячейки B2 не изменится, так как не будет запущен пересчет функции Primer2.

Функция Primer2 начнет вести себя по-другому, если добавить в нее оператор Application.Volatile (переименуем ее в Primer3):

Пример 3

Function Primer3 ( Cell1 As Range , Cell2 As Range ) As String

Теперь при смене значения в ячейке C2, значение ячейки B2 тоже изменится.

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

Безопасное использование функции

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

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

В простых случаях для выбора можно использовать функцию Choose или, в более сложных, оператор If…Then…Else и оператор Select Case.

Пример 4
Используем функцию Choose для выбора способа вычисления пользовательской функции в зависимости от значения дополнительного аргумента:

Function Primer4 ( Cell1 As Range , Cell2 As Range , a As Byte ) Primer4 = Choose ( a , Cell1 + Cell2 , Cell1 - Cell2 , Cell1 * Cell2 )

В функцию Primer4 добавлен дополнительный аргумент a, от которого зависит, какое действие будет произведено со значениями ячеек B1 и C1:

На следующем скриншоте представлены результаты вычисления функции в зависимости от значения аргумента a:

В ячейке A1 вычисляется сумма значений ячеек B1 и C1 – аргумент a=1.
В ячейке A2 вычисляется разность значений ячеек B2 и C2 – аргумент a=2.
В ячейке A3 вычисляется произведение значений ячеек B3 и C3 – аргумент a=3.

Пример 5
Используем оператор If…Then…Else в сокращенном виде (If…Then…) для выбора способа вычисления функции в зависимости от значения дополнительного аргумента:

Читайте также: