Для чего предназначено связывание рабочих листов

Опубликовано: 17.09.2024

На этом шаге мы рассмотрим связывание рабочих книг.

Связывание - это использование ссылок на ячейки из внешних рабочих книг в целях получения из них данных для своего рабочего листа. При связывании рабочие листы связываются таким образом, что один становится зависим от другого. Рабочая книга, содержащая связанные формулы (т.е. формулы с внешними ссылками), называется зависимой . А рабочая книга, в которой находится информация, используемая в форме с внешними ссылками, называется исходной . Исходная рабочая книга необязательно должна быть открыта в то время, когда открыта зависимая рабочая книга.

Существует несколько способов создания формулы с внешними ссылками:

Набрать ссылку на ячейку вручную . Эти ссылки могут быть достаточно длинными, так как они содержат, кроме адреса ячейки, имена рабочей книги и рабочего листа. Приемущество этого метода состоит в том, что исходная рабочая книга необязательно должна быть открыта.
Указать ссылку на ячейку . Если исходная рабочая книга открыта, то чтобы создать формулы с внешними ссылками, можно воспользоваться стандартным методом указания.
Выполнить команду Правка | Специальная вставка , а затем щелкнуть на кнопке Вставить связь . Этот метод требует, чтобы исходная рабочая книга была открыта.
Воспользоваться командой Данные | Консолидация .

Общий синтаксис для формулы с внешней ссылкой выглядит следующим образом:
=[Имя_Рабочей_книги] Имя_Листа! Адрес_Ячейки .

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

Откройте исходную рабочую книгу.
Выберите ячейку (или диапазон), с которой Вы хотите установить связь, и скопируйте ее в буфер обмена.
Активизируйте зависимую рабочую книгу и выберите ячейку, в которой должна нажодиться связанная формула. Если же Вы вставляете диапазон, выберите только левую верхнюю ячейку вставляемого диапазона.
Выберите команду Правка | Специальная вставка и в открывшемся диалоговом окне Специальная вставка щелкните на кнопке Вставить связь .

Excel позволяет создавать формулы, связанные с несохраненными и даже с несуществующими рабочими книгами. Предположим, у Вас открыты две рабочие книги, причем ни одна из них не созранена (их имена - Книга1 и Книга2 ). Если в рабочей книге Книга2 Вы создадите формулу, связанную с книгой Книга1 , а затем сохраните Книга2 , то Excel отобразит диалоговое окно, показанное на рисунке 1. Такой ситуации следует избегать.

Рис. 1. Сообщение о том, что в рабочей книге есть ссылка на несохраненный документ

Excel позволяет также создавать связи с несуществующими документами. Это может понадобиться в случае, если в качестве исходной Вы собираетесь использовать рабочую книгу своего коллеги, но этот файл Вы еще не получили. Когда Вы будете вводить формулу с внешней ссылкой на несуществующую рабочую книгу, Excel отобразит диалоговое окно Файл не найден (рис. 2). Если Вы щелкните на кнопке Отмена , то в формуле останется введенное Вами имя рабочей книги, но эта формула вернет ошибку. А когда станет доступной исходная рабочая книга, ошибка исчезнет, и формула будет возврашать правильное значение.

Рис. 2. Диалоговое окно Файл не найден

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

Если рабочая книга связана с несколькими другими рабочими книгами, то иногда необходимо просмотреть список всех исходных рабочих книг. Стобы это сделать, выберите команду Правка | Связи . В результате на экране появится диалоговое окно Связи (рис. 3). В нем перечислены все исходные рабочие книги, а также другие типы связей с другими документами.

Рис. 3. Диалоговое окно Связи

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

До сих пор работали только с одним листом рабочей книги . Часто бывает полезно использовать несколько рабочих листов.

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

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

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

Расположение рабочих книг

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

• рядом — рабочие книги открываются в маленьких окнах, на которые делится весь экран "плиточным" способом;

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

• слева направо — открытые рабочие книги отображаются в окнах, имеющих вид вертикальных полос;

• каскадом — рабочие книги (каждая в своем окне) "выкладываются" на экране слоями.

Переходы между рабочими книгами

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

• щелкните на видимой части окна рабочей книги;

• нажмите клавиши <Ctrl+F6> для перехода из окна одной книги в окно другой.

• откройте меню Excel Окно. В нижней его части содержится список открытых рабочих книг. Для перехода в нужную книгу просто щелкните по имени.

Копирование данных из одной рабочей книги в другую

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

Чтобы скопировать данные из одной рабочей книги в другую, откройте обе рабочие книги. Выделите данные в первой книге и щелкните на кнопке Копировать панели инструментов Стандартная переключиться в другую книгу, используйте любой из перечисленных выше методов. Например, согласно одному из них, выберите из меню Окно имя второй рабочей книги. Перейдите в нужный рабочий лист и выделите ячейку, в которую предполагаете вставить данные. Щелкните на кнопке Вставить панели инструментов Стандартная. Excel моментально скопирует данные во вторую рабочую книгу.

Перенос данных между рабочими книгами

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

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

Создание связей между рабочими листами и рабочими книгами.

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

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

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

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

• ссылка на другой рабочий лист в формуле связывания с использован ссылки на лист;

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

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

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

При работе с большим количеством данных и создании многочисленных paбочих листов для хранения этих данных возникают ситуации, когда формула на одном рабочем листе использует данные из другого рабочего листа. Такие формулы весьма полезны, поскольку избавляют вас от необходимости хранить избыточные данные на многих рабочих листах.

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

Чтобы сослаться на ячейку в другом рабочем листе, поставьте восклицательный знак между именем листа и именем ячейки. Синтаксис для этого типа формул выглядит следующим образом: =ЛИСТ!Ячейка. Если ваш лист имеет имя, то вместо обозначения лист используйте имя этого листа. Например, Отчет! B 5.

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

Связывание нескольких рабочих листов

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

В таких случаях Excel ссылается на диапазоны ячеек с помощью трехмерных ссылок. Трехмерная ссылка устанавливается путем включения диапазона листов (с указанием начального и конечного листа) и соответствующего диапазона ячеек. Например, формула, использующая трехмерную ссылку, которая включает листы от Лист1 доЛист5 и ячейки А4:А8, может иметь следующий вид: = SUM (ЛИСТ1:ЛИСТ5!А4:А8).

Трехмерные ссылки можно включить в формулу и другим способом. Для этого достаточно щелкнуть на рабочем листе, который нужно включить в формулу. Но сначала начните формулу в ячейке, где хотите получить результат. Когда дойдет черед до использования трехмерной ссылки, щелкните на ярлычке первого листа, подлежащего включению в ссылку, затем нажмите (и не отпускайте) клавишу <Shift> и щелкните на ярлычке последнего листа, подлежащего включению в ссылку. После этого выделит нужные ячейки. Завершив построение формулы, нажмите клавишу <Enter>.

Связывание рабочих книг

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

Как только связь устанавливается. Excel копирует величину из ячейки в файле-источнике в ячейку файла назначения. Величина в ячейке назначения автоматически обновляется.

При ссылке на ячейку, содержащуюся в другой рабочей книге, используется следующий синтаксис: [Книга]Лист!Ячейка. Вводя формулу связывания для ссылки на ссылку из другой рабочей книги, используйте имя этой книги, заключенное в квадратные скобки, за которыми без пробелов должно следовать имя рабочего листа, затем восклицательный знак (!), а после него — адрес ячейки (ячеек). Например ' C :\Petrov\[ Журнал1 .хls]Литература'!L3.

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

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

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

Дата добавления: 2018-09-23 ; просмотров: 137 ; Мы поможем в написании вашей работы!

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

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

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

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

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

Чтобы сослаться на ячейку в другом рабочем листе, поставьте восклицательный знак между именем листа и именем ячейки. Синтаксис для этого типа формул выглядит следующим образом: =ЛИСТ!Ячейка. Если ваш лист имеет имя, то вместо обозначения лист используйте имя этого листа. Например, Отчет!B5.

Если имя содержит пробелы (например, Лабораторная №8), то при создании ссылок на другие листы это имя необходимо заключать в одинарные кавычки.

Связывание нескольких рабочих листов

В таких случаях Excel ссылается на диапазоны ячеек с помощью трехмерных ссылок. Трехмерная ссылка устанавливается путем включения диапазона листов (с указанием начального и конечного листа) и соответствующего диапазона ячеек. Например, формула, использующая трехмерную ссылку, которая включает листы от Лист1 до Лист5 и ячейки А4:А8, может иметь следующий вид: =SUM(ЛИСТ1:ЛИСТ5!А4:А8).

Трехмерные ссылки можно включить в формулу и другим способом. Для этого достаточно щелкнуть на рабочем листе, который нужно включить в формулу. Но сначала начните формулу в ячейке, где хотите получить результат. Когда дойдет черед до использования трехмерной ссылки, щелкните на ярлычке первого листа, подлежащего включению в ссылку, затем нажмите (и не отпускайте) клавишу <Shift> и щелкните на ярлычке последнего листа, подлежащего включению в ссылку. После этого выделит нужные ячейки. Завершив построение формулы, нажмите клавишу <Enter>.

Связывание рабочих книг

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

При ссылке на ячейку, содержащуюся в другой рабочей книге, используется следующий синтаксис: [Книга]Лист!Ячейка. Вводя формулу связывания для ссылки на ссылку из другой рабочей книги, используйте имя этой книги, заключенное в квадратные скобки, за которыми без пробелов должно следовать имя рабочего листа, затем восклицательный знак (!), а после него -- адрес ячейки (ячеек). Например 'C:Petrov[Журнал1.хls]Литература'!L8.

Обновление связей

Связывание рабочих листов таблицы

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

1. Прямое связывание

Для ссылки из таблицы, расположенной на Листе 1, на данные таблицы, расположенной на Листе 2, надо в ячейке таблицы Листа 1 указать абсолютный адрес ячеек с данными Листа 2, в виде:

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

=d:\ excel\[primer] лист 2'!$n$20

Доп устим, мы имеем электронный журнал. Для простоты ограничимся 3 предметами: литература, алгебра и геометрия.

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

Необходимо выставить итоговую оценку за I четверть по каждому предмету, используя функцию СРЗНАЧ - среднее значение .

Как работать со встроенными функциями можно познакомиться на странице http://kursach.com/!inforactehnolog/4.4.8.htm

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

Сравните свои результаты, с результатами на рисунке

2. Консолидация данных

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

1. создать таблицы одинаковой структуры на разных рабочих листах,

2. ввести данные в каждую таблицу за разные периоды,

3. объединить данные в итоговой таблице путем консолидации.

В Microsoft Excel существует два метода консолидации данных:

По расположению, если сводимые области расположены идентично;

По категориям, если сводимые области отличаются по расположению.

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

Консолидация по расположению

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

Установить курсор в верхнюю левую ячейку области консолидации.

Задать команду консолидация из меню данные .

В параметре функция указать сумма

В параметре ссылка поочередно определять область-источник (включающую лишь подлежащие суммированию данные без меток) и нажимать кнопку добавить .

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

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

Исходные данные располагаются на листах Январь, Февраль и Март. Необходимо произвести расчеты на этих листах: количество рабочих дней, количество дней по временной нетрудоспособности, количество дней отпуска и количество дней в командировке. Для расчета использовать функцию СЧЁТЕСЛИ

Подробнее об этой функции смотрите на странице

На листе с именем I квартал необходимо свести данные вместе путем их консолидации по расположению.

Сравните свои результаты,
с результатами на рисунке

Консолидация по категориям

В данном случае не обязательным является одинаковое расположение данных в сводимых областях. Для выполнения свода используется следующая процедура:

Ввести в область консолидации (т.е. в результирующую таблицу) категории (заголовки шапки или боковик).

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

Задать команду консолидация из меню данные .

В параметре функция указать сумма

В параметре ссылка поочередно определять область-источник (включающую категории и данные) и нажимать кнопку добавить .

Если категориями являются заголовки шапки, то включить параметр в верхней строке . Если категориями являются названия в боковике, то включить параметр в левом столбце .

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

В таблице, полученной по команде консолидация , можно работать как со структурой: нажимать кнопку 2 и раскрывать все внутренние данные, входящие в сумму. Нажатие кнопки 1 восстанавливает вариант таблицы с итоговыми данными. Нажатие кнопки + (плюс) приводит к раскрытию промежуточных данных для соответствующей метки (категории). Нажатие кнопки - (минус) убирает промежуточные данные для данной категории.

На листах книги 1квартал, 2квартал, 3квартал, 4квартал ведены исходные данные о закупке канцтоваров в течение года. Необходимо рассчитать стоимость закупки канцтоваров за каждый квартал и стоимость товаров по группам.

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

Связывание — это процесс динамического обновления данных в рабочем листе на основании данных другого источника (рабочего листа или рабочей книги). Связанные данные отражают любые изменения, вносимые в исходные данные.
Связывание выполняется посредством специальных формул, которые содержат так называемые внешние ссылки . Внешняя ссылка может ссылаться на ячейку из другого рабочего листа той же рабочей книги или на ячейку любого другого рабочего листа любой другой рабочей книги. Например, связи между двумя листами достигается за счёт введения в один лист формулы связи со ссылкой на ячейку в другом листе.
Excel позволяет создавать связи с другими рабочими листами и другими рабочими книгами трех типов:
• ссылка на другой рабочий лист в формуле связывания с использован ссылки на лист;
• ссылка на несколько рабочих листов в формуле связывания с использованием трехмерной ссылки,
• ссылка на другую рабочую книгу в формуле связывания. Формула связывания вводится в ячейку, в которой нужно получить результат.

Использование в формуле связывания ссылки на другой рабочий лист
При работе с большим количеством данных и создании многочисленных paбочих листов для хранения этих данных возникают ситуации, когда формула на одном рабочем листе использует данные из другого рабочего листа. Такие формулы весьма полезны, поскольку избавляют вас от необходимости хранить избыточные данные на многих рабочих листах.

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

Чтобы сослаться на ячейку в другом рабочем листе, поставьте восклицательный знак между именем листа и именем ячейки. Синтаксис для этого типа формул выглядит следующим образом: =ЛИСТ!Ячейка . Если ваш лист имеет имя, то вместо обозначения лист используйте имя этого листа. Например, Отчет!B5 .

Если имя содержит пробелы (например, Бюджет 99 ), то при создании ссылок на другие листы это имя необходимо заключать в одинарные кавычки.
Связывание нескольких рабочих листов
Часто встречаются ситуации, когда формула должна ссылаться на диапазон ячеек, включающий два или даже больше листов в рабочей книге.
В таких случаях Excel ссылается на диапазоны ячеек с помощью трехмерных ссылок. Трехмерная ссылка устанавливается путем включения диапазона листов (с указанием начального и конечного листа) и соответствующего диапазона ячеек. Например, формула, использующая трехмерную ссылку, которая включает листы от Лист1 до Лист5 и ячейки А4:А8, может иметь следующий вид: =SUM(ЛИСТ1:ЛИСТ5!А4:А8).

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

При ссылке на ячейку, содержащуюся в другой рабочей книге, используется следующий синтаксис: [Книга]Лист!Ячейка . Вводя формулу связывания для ссылки на ссылку из другой рабочей книги, используйте имя этой книги, заключенное в квадратные скобки, за которыми без пробелов должно следовать имя рабочего листа, затем восклицательный знак (!), а после него — адрес ячейки (ячеек). Например 'C:\Petrov\[Журнал1.хls]Литература'!L3.

Порядок выполнения работы

В рабочую книгу добавьте два листа. Переименуйте рабочие листы:
Лист1 – Закупка , Лист2 – Реализация , Лист3 – Цена , Лист4 – Выручка , Лист5 – Доход .
Все задания нужно выполнять на разных рабочих листах в одной рабочей книге.

Создание и заполнение таблиц данными. Ввод формул.
На рабочем листе "Закупка" создайте таблицу и внесите данные, как показано на рис. 1.

- Перед вводом данных задайте форматы ячеек таблицы – Числовой, число десятичных знаков 0, выравнивание – по центру. Наименование предметов – формат Текстовый. Выравнивание – по левому краю (отступ) – 1.
- В ячейках H4:Н8 – введите формулы суммирования по строкам (обратите внимание на предлагаемый диапазон суммируемых ячеек, если диапазон не захватывает все исходные данные – укажите мышью весь необходимый диапазон, затем нажмите Enter).

Рис 1. Общий вид исходной таблицы на рабочем листе «Закупка»

На рабочем листе "Реализация" внесите исходные данные в таблицу и оформите, как показано на рисунке 2.

Рис 2. Общий вид таблицы на рабочем листе «Закупка»

На рабочем листе "Цена" создайте и заполните две таблицы – Расходы на закупку и Расчет цен как показано на рисунке 3.

Рис 3. Общий вид рабочего листа «Цена»

Указание. Оформление заголовков таблиц выполняется аналогично предыдущим заданиям. Задайте форматы ячеек в таблице Расходы на закупку:
А4:А9 – текстовый;
В4:В8 – денежный, число десятичных знаков – 2, обозначение – р.
C4:C8 – числовой, число десятичных знаков – 0.
D4:D9 – денежный, число десятичных знаков – 2, обозначение – р.
В ячейки А4:С8 внесите данные с клавиатуры.
В ячейку D4 введите формулу = В4*С4.
Для этого наберите с клавиатуры знак =, затем щелкните левой кнопкой мыши на ячейке В4, нажмите клавишу знак умножения – * на дополнительной клавиатуре и щелкните мышью на ячейке С4 , подтвердите формулу – нажмите клавишу Enter .
Чтобы не повторять набор формулы в ячейках D5, D6, D7, D8. Скопируйте в эти ячейки содержимое ячейки D4 вместе с формулой.
Для снятия команды копирования с ячейки D4 нажмите клавишу Esc .
Обратите внимание на изменение ссылок в формулах суммирования, т.к. они относительные.

Задайте форматы ячеек в таблице Расчет цен:
А14:А18 – текстовый;
В14:В18 – денежный, число десятичных знаков – 2, обозначение – р.
C14:C18 – процентный, число десятичных знаков – 0.
D14:D18 – денежный, число десятичных знаков – 2, обозначение – р.
В ячейки А14:С18, В14:В18 и С14:С18 внесите данные с клавиатуры.
В ячейку D14 введите формулу = В14*С14+В14
Для этого наберите с клавиатуры знак =, затем щелкните левой кнопкой мыши на ячейке В14 , нажмите знак умножения – *, щелкните мышью на ячейке С14 , затем нажмите знак сложение – + и подтвердите формулу и нажатием клавиши Enter .
Аналогично предыдущей таблице скопируйте содержимое ячейки D14 с формулой в ячейки D15, D16, D17, D18.
Примените к таблицам обрамление, как показано на рисунке 3.

Создайте и заполните таблицы на листе Выручка, как показано на рисунке 4.

Рис 4. Общий вид рабочего листа «Выручка»

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

Заполните и оформите таблицы на листе Доход (рис. 5).

Рис 5. Общий вид рабочего листа «Доход»

Указание. Обратите внимание, что в таблицах добавились данные столбца Цена закупки из таблицы Расчет цен расположенной на листе Цена .
В таблицах задайте форматы ячеек, выравнивание данных в них и обрамление ячеек (рис 5).
Внесите данные в таблицы.
В таблице Доход от реализации за полугодие количество проданного товара, число в столбце Н , совпадает с суммой за полугодие на листе Реализация .
В таблице Доход от реализации за 1 квартал количество проданного товара также составляет сумму, но только за январь, февраль и март месяцы.
В ячейках E4:E8 находится формула =D4*(B4-C4).
В строке ИТОГО в ячейке E9 находится формула, суммирующая ячейки E4:E8 .
В ячейках E14:E18 находится формула =D14*(B14-C14).
В строке ИТОГО в ячейке E19 находится формула, суммирующая ячейки E14:E18 .

Связывание таблиц в Excel.

Если на одном рабочем листе используются данные из другого листа, то эти листы считаются связанными. С помощью связывания можно свести воедино значения ячеек из нескольких разных таблиц на одном рабочем листе.
Изменение содержимого ячейки на одном листе или таблице (источнике) рабочей книги приводит к изменению связанных с ней ячеек в листах или таблицах (приемниках). Этот принцип отличает связывание листов от простого копирования содержимого ячеек из одного листа в другой.
В зависимости от техники исполнения связывание бывает “прямым“ и через команду СПЕЦИАЛЬНАЯ ВСТАВКА.

1 способ – "Прямое связывание ячеек"

Прямое связывание листов используется непосредственно при вводе формулы в ячейку, когда в качестве одного из элементов формулы используется ссылка на ячейку другого листа. Например, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая использует ссылку на ячейку А4 другого рабочего листа (например, Листа1) и оба листа загружены данными, то такое связывание листов называется “прямым”.
Термин “ прямое ” связывание обозначает, что пользователь сам непосредственно при вводе формулы указывает имя листа и абсолютный адрес ячейки, разделенные восклицательным знаком " ! ".

Примеры формул: = C5*Лист1! A4
= Лист3! В2*100%
= Лист1! A1- Лист2! A

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

2 способ – Связывание ячеек через команду "Специальная вставка"

Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКА производится, если какая либо ячейка таблицы на одном рабочем листе должна содержать значение ячейки из другого рабочего листа.
Чтобы отразить в ячейке С4 на листе Цена значение ячейки Н4 на исходном листе Закупка, нужно поместить курсор на ячейку Н4 исходного листа и выполнить команду Правка–Копировать . На листе Цена поставить курсор на ячейку С4 , которую необходимо связать с исходной, и выполнить команду Правка–Специальная вставка– Вставить связь (см рис. 6). Тогда на листе Цена появится указание на ячейку исходного листа Закупка , например: = Закупка!$4
При таком связывании Excel 2003 автоматически использует абсолютный адрес на ячейку, т.к. относительный адрес обращения может привести к ошибкам, особенно если обращаться к незагруженным файлам (рабочим книгам).

Задание. Свяжите ячейки С4, С5, С6, С7, С8 в таблице Расходы на закупку на листе Цена с соответствующими ячейками на листе Закупка, используя различные способы связывания ячеек (рис. 6).

Рис 6. Связывание ячеек различных рабочих листов.

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

Контрольные вопросы:

Как меняется активный лист?

Как быстро сделать первый (последний) лист рабочей книги активным?

Как добавлять, удалять и переименовывать листы рабочей книги?

Как копируется информация с одного листа на другой?

Как пересылается информация с одного листа на другой?

Как передавать информацию между таблицами, расположенными на разных листах рабочей книги?

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