Связанная таблица - это таблица, созданная в Excel и связанная с таблицей в окне PowerPivot. Преимущество создания и поддержания данных в Excel перед их импортом состоит в том, что можно продолжать изменять данные на листе Excel и в то же время использовать их для анализа в PowerPivot.
Имя новой таблицы, появляющейся в окне PowerPivot, всегда совпадает с именем таблицы Excel. Поэтому перед созданием связанной таблице необходимо дать таблице Excel понятное имя. По умолчанию Excel формирует имена таблиц автоматически (Table1, Table2 и т. п.), но можно легко переименовать их с помощью интерфейса Excel.
Создание таблицы в Excel
В окне Excel выберите данные, которые необходимо представить в виде таблицы.
Нажмите кнопку Форматировать как таблицу на вкладке Корневая папка и выберите стиль таблицы. Обязательно выберите Таблица с заголовками .
Дайте таблице имя, как описано в следующей процедуре.
Переименование таблицы в Excel
В окне Excel на вкладке Работа с таблицами в группе Свойства на вкладке Конструирование введите имя для таблицы.
Создание связанной таблицы в Excel
Выберите в окне Excel созданную таблицу.
На вкладке PowerPivot в группе Excel щелкните Создать связанную таблицу .
Новая таблица появляется в окне PowerPivot. Данные этой таблицы связаны с выбранной таблицей Excel и могут изменяться в Excel, а не в окне PowerPivot.
Если переименовать таблицу Excel после создания связанной таблицы, то связь между таблицами будет прервана. При попытке обновить данные появляется диалоговое окно Ошибки в связанных таблицах . Чтобы выбрать вариант исправления ошибки, нажмите Параметры . Изменение имени в окне PowerPivot не влияет на связь.
При переименовании заголовков столбцов в таблице Excel изменения в связанной таблице Gemini применяются автоматически. Однако если переименовать заголовок столбца в Excel, который используется в связи PowerPivot, то эту связь PowerPivot придется создать заново. Дополнительные сведения о связях см. в разделе .
По умолчанию таблицы PowerPivot, связанные с таблицами Excel, обновляются автоматически при изменении данных. Кроме того, их можно обновить вручную в окне PowerPivot или в окне Excel.
Задание режима обновления в PowerPivot
Работа с таблицами перейдите на вкладку Связанная таблица и в группе Связанные таблицы нажмите кнопку Режим обновления , а затем нажмите кнопку Вручную или Автоматически .
Обновление связанных таблиц вручную в PowerPivot
В окне PowerPivot в разделе Работа с таблицами перейдите на вкладку Связанная таблица и в группе Связанные таблицы нажмите кнопку Обновить все или Обновить выделенные .
Обновление связанных таблиц вручную в Excel
В окне Excel перейдите на вкладку PowerPivot и выберите в группе Данные Excel пункт Обновить все .
Если нам нужно, чтобы автоматически данные из одной таблице Excel переносились в другую
, нужно связать эти таблицы. Есть несколько способов, как связать данные двух таблиц в Excel. Один способ -
с помощью формулы, смотрите в статье «Как связать таблицы в Excel ».
Здесь рассмотрим, как связать две таблицы в разных книгах Excel
, с помощью установки связи между таблицами. Когда все настроим, то достаточно открыть одну таблицу, чтобы данные перенеслись в эту таблицу из другой таблицы. Не нужно открывать все книги, чтобы связь работала. Она будет работать с одной открытой книгой.
Итак, у нас есть две книги Excel – Книга1 и Книга2. В Книге1 есть такая таблица, уже заполненная. Таблица1.
Внимание!
Если в таблице есть объединенные ячейки, то в них связь не будет работать или будет сбиваться.
В Книге2 делаем такую же таблицу, но не заполняем ее.
Переходим в Таблицу2. Выделяем ячейку А2. Нажимаем на закладке «Главная» стрелочку у кнопки «Вставить» и нажимаем на кнопку «Вставить связь».
Внимание!
Если нажмем клавишу «Enter», то связь может не установиться. Поэтому, когда установим связь, просто нажать на пустую ячейку. А пульсирующую рамку у ячейки А1 в Таблице1 убрать клавишей «Esc».
Когда установим связь в ячейке, в строке формул будет написан путь.
В ячейке А2 Таблицы2 появилось название столбца, как в Таблице1.
Теперь, чтобы скопировать связь по всему столбцу, в адресе пути в ячейке А1 уберем знак $ - заменим ссылку абсолютную на относительную. Подробнее о ссылках смотрите в статье «Относительные и абсолютные ссылки в Excel».
Копируем формулу со связью из ячейки А1 вниз по столбцу. Так же настраиваем и, затем, копируем, связь в других ячейках. Получилась такая таблица2.
О других функциях специальной вставки смотрите в статье "Специальная вставка Excel".
Как связать таблицы на разных компьютерах, смотрите в статье "Общий доступ к файлу Excel ".
В Excel можно настроить автоматический перенос данных из таблицы Excel в Word и наоборот. Смотрите в статье "
Если на одном рабочем листе используются данные из другого листа, то эти листы считаются связанными. С помощью связывания можно свести воедино значения ячеек из нескольких разных таблиц на одном рабочем листе.
Изменение содержимого ячейки на одном листе или таблице (источнике ) рабочей книги приводит к изменению связанных с ней ячеек в листах или таблицах (приемниках ). Этот принцип отличает связывание листов от простого копирования содержимого ячеек из одного листа в другой.
В зависимости от техники исполнения связывание бывает «прямым » и через команду «Специальная вставка» .
Прямое связывание листов используется непосредственно при вводе формулы в ячейку, когда в качестве одного из элементов формулы используется ссылка на ячейку другого листа. Например, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая использует ссылку на ячейку А4 другого рабочего листа (например, Листа 1) и оба листа загружены данными, то такое связывание листов называется "прямым".
Термин "прямое" связывание обозначает, что пользователь сам непосредственно при вводе формулы указывает имя листа и абсолютный адрес ячейки, разделенные восклицательным знаком "!".
Примеры формул: = C5*Лист1! A4
Лист3! В2*100%
Лист1! A1-Лист2! A1
Примечание. Для указания ссылки на ячейки и листы, находящиеся в незагруженных (неоткрытых) рабочих книгах, в формуле нужно без пробелов задать полный путь местонахождения файла. Путь задается в одинарных кавычках, где указывается название диска, каталога (папки), имя рабочей книги (имя файла) в квадратных скобках и имя листа, на который идет ссылка.
Например:
= "D:\Документы студентов[Расчет дохода.xls]Закупка продукции"!H19
Связывание через команду " Специальная вставка" производится, если какая либо ячейка таблицы на одном рабочем листе должна содержать значение ячейки из другого рабочего листа.
Чтобы отразить в ячейке С4 на листе Цена продукции значение ячейки Н4 на исходном листе Закупка продукции, нужно поместить курсор на ячейку Н4 исходного листа и выполнить команду Правка/Копировать. На листе Цена продукции поставить курсор на ячейку С4, которую необходимо связать с исходной, и выполнить команду Правка/Специальная вставка/Вставить связь (см. рис. 6). Тогда на листе Цена реализации появится указание на ячейку исходного листа Закупка продукции, например: = Закупка продукции!$Н$4
При таком связывании EXCEL автоматически использует абсолютный адрес на ячейку, т.к. относительный адрес обращения может привести к ошибкам, особенно если обращаться к незагруженным файлам (рабочим книгам).
Задание 7. Свяжите ячейки С4, С5, С6, С7, С8 в таблице Расходы на закупку на листе Цена реализации с соответствующими ячейками на листе Закупка продукции, используя при этом различные способы связывания ячеек (рис. 28).
Рис. 28. Связывание ячеек различных рабочих листов
К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.
Как разорвать связь
Один из способов решения данной проблемы - разрыв связи. Если в файле только одна связь, то сделать это довольно просто:
- Перейдите на вкладку Данные .
- Выберите команду Изменить связи в разделе Подключения .
- Нажмите Разорвать связь .
ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!
Как разорвать связь со всеми книгами
Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:
Код на VBA
Код макроса удаляющего все связи с книгой представлен ниже. Можете скопировать его в свой проект.
Sub UnlinkWorkBooks() Dim WbLinks Dim i As Long Select Case MsgBox("Все ссылки на другие книги будут удалены из этого файла, а формулы, ссылающиеся на другие книги будут заменены на значения." & vbCrLf & "Вы уверены, что хотите продолжить?", 36, "Разорвать связь?") Case 7 " Нет Exit Sub End Select WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) If Not IsEmpty(WbLinks) Then For i = 1 To UBound(WbLinks) ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks Next Else MsgBox "В данном файле отсутствуют ссылки на другие книги.", 64, "Связи с другими книгами" End If End Sub
Как разорваться связи только в выделенном диапазоне
Иногда в книге имеется много связей и есть опасения, что при удалении связи можно удалить лишнюю. Чтобы этого избежать с помощью надстройки можно удалить связи только в выделенном диапазоне. Для этого.
Листы рабочей книги
До сих пор работали только с одним листом рабочей книги. Часто бывает полезно использовать несколько рабочих листов.
В нижней части экрана видны Ярлычки листов. Если щелкнуть на ярлычке левой клавишей мыши, то указанный лист становится активным и перемещается наверх. Щелчок правой кнопкой на ярлычке вызовет меню для таких действий с листом, как перемещение, удаление, переименование и т.д.
В левом нижнем углу окна рабочей книги находятся кнопки прокрутки с помощью которых можно переходить от одного рабочего листа к другому.
Щелкнув правой кнопкой мыши на кнопках прокрутки ярлычков, можно открыть констектно зависимое меню для выбора нужного рабочего листа.
Расположение рабочих книг
Предположим, вы хотите видеть на экране сразу все открытые книги, Excel без труда выполнит ваше желание, причем вы сможете легко ориентироваться в своих книгах. С помощью команды Excel Окно Расположить можно расположить открытые рабочие книги на экране четырьмя способами.
* рядом -- рабочие книги открываются в маленьких окнах, на которые делится весь экран "плиточным" способом;
* сверху вниз -- открытые рабочие книги отображаются в окнах, имеющих вид горизонтальных полос,
* слева направо -- открытые рабочие книги отображаются в окнах, имеющих вид вертикальных полос;
* каскадом -- рабочие книги (каждая в своем окне) "выкладываются" на экране слоями.
Переходы между рабочими книгами
Независимо от того, решили ли вы расположить на экране все открытые рабочие книги или просто "уложили" их друг на друга в порядке открытия, вы можете легко переходить от одной книги к другой. В Excel предусмотрено несколько быстрых способов перехода к нужной книге. Для этого можно использовать мышь, клавиши экстренного доступа или меню Excel Окно. Вот эти способы:
* щелкните на видимой части окна рабочей книги;
* нажмите клавиши
* откройте меню Excel Окно. В нижней его части содержится список открытых рабочих книг. Для перехода в нужную книгу просто щелкните по имени.
Копирование данных из одной рабочей книги в другую
С помощью команды Excel Копировать можно копировать данные из одной рабочей книги в другую. Например, вы открыли две рабочих книги одна из которых содержит квартальный бюджет, а другая -- годовой. Для экономии времени было бы неплохо скопировать данные по первому кварталу из первой рабочей книги во вторую. При этом исходные данные в первой рабочей книге не изменяв появится копия этих данных.
Чтобы скопировать данные из одной рабочей книги в другую, откройте обе рабочие книги. Выделите данные в первой книге и щелкните на кнопке Копировать панели инструментов Стандартная переключиться в другую книгу, используйте любой из перечисленных выше методов. Например, согласно одному из них, выберите из меню Окно имя второй рабочей книги. Перейдите в нужный рабочий лист и выделите ячейку, в которую предполагаете вставить данные. Щелкните на кнопке Вставить панели инструментов Стандартная. Excel моментально скопирует данные во вторую рабочую книгу.
Перенос данных между рабочими книгами
Эта процедура аналогична копированию данных. Отличие заключается в использовании другой кнопки. Выделите данные в первой рабочей книге и щелкните на кнопке Вырезать панели инструментов Стандартная, чтобы извлечь данные. Перейдя в другую рабочую книгу, выделите нужную ячейку и щелкните на кнопке Вставить панели инструментов Стандартная. В результате Excel удалит данные из первой рабочей книги и вставит их во вторую.
Существует быстрый способ переноса данных рабочего листа (листов) между рабочими книгами. Он состоит в использовании метода "перетащить и опустить". Сначала откройте книги, задействованные в операции переноса данных. Выберите из меню Excel команду Окно/ Расположить. В открывшемся диалоговом окне Расположение окон выберите вариант рядом и щелкните на кнопке ОК. Вы должны видеть хотя бы небольшую часть окна каждой рабочей книги. Выделите ярлычок листа (листов), который вы хотите скопировать. Поместите указатель мыши поверх выделенного ярлычка листа, щелкните и, не отпуская кнопку мыши, перетащите ярлычок в окно другой рабочей книги. Когда вы отпустите кнопку мыши, лист будет "прописан" в новой (для него) рабочей книге.
Создание связей между рабочими листами и рабочими книгами.
Excel позволяет использовать в таблице данные с других листов и из других таблиц.
Связывание -- это процесс динамического обновления данных в рабочем листе на основании данных другого источника (рабочего листа или рабочей книги). Связанные данные отражают любые изменения, вносимые в исходные данные.
Связывание выполняется посредством специальных формул, которые содержат так называемые внешние ссылки. Внешняя ссылка может ссылаться на ячейку из другого рабочего листа той же рабочей книги или на ячейку любого другого рабочего листа любой другой рабочей книги. Например, связи между двумя листами достигается за счёт введения в один лист формулы связи со ссылкой на ячейку в другом листе.
Excel позволяет создавать связи с другими рабочими листами и другими рабочими книгами трех типов:
При работе с большим количеством данных и создании многочисленных paбочих листов для хранения этих данных возникают ситуации, когда формула на одном рабочем листе использует данные из другого рабочего листа. Такие формулы весьма полезны, поскольку избавляют вас от необходимости хранить избыточные данные на многих рабочих листах.
Чтобы сослаться на ячейку в другом рабочем листе, поставьте восклицательный знак между именем листа и именем ячейки. Синтаксис для этого типа формул выглядит следующим образом: =ЛИСТ!Ячейка. Если ваш лист имеет имя, то вместо обозначения лист используйте имя этого листа. Например, Отчет!B5.
Если имя содержит пробелы (например, Бюджет 99 ), то при создании ссылок на другие листы это имя необходимо заключать в одинарные кавычки.
Связывание нескольких рабочих листов
Часто встречаются ситуации, когда формула должна ссылаться на диапазон ячеек, включающий два или даже больше листов в рабочей книге. Обычно это происходит, когда создаются идентичные рабочие листы для распределения бюджета за разные периоды времени, для сведений о работе различных бригад или для сведений о продажах в различных регионах. У вас также могут быть разные рабочие листы, но их итоговые значения специально содержатся в ячейках с идентичными адресами. И все эти итоговые значения можно затем свести воедино для получения общего итога в одной формуле, содержащей ссылку на все эти листы и адреса ячеек.
В таких случаях Excel ссылается на диапазоны ячеек с помощью трехмерных ссылок. Трехмерная ссылка устанавливается путем включения диапазона листов (с указанием начального и конечного листа) и соответствующего диапазона ячеек. Например, формула, использующая трехмерную ссылку, которая включает листы от Лист1 до Лист5 и ячейки А4:А8, может иметь следующий вид: =SUM(ЛИСТ1:ЛИСТ5!А4:А8).
Трехмерные ссылки можно включить в формулу и другим способом. Для этого достаточно щелкнуть на рабочем листе, который нужно включить в формулу. Но сначала начните формулу в ячейке, где хотите получить результат. Когда дойдет черед до использования трехмерной ссылки, щелкните на ярлычке первого листа, подлежащего включению в ссылку, затем нажмите (и не отпускайте) клавишу
Связывание рабочих книг
При связывании рабочих книг используется ряд терминов, которые вам следует знать. Рабочая книга, содержащая формулу связывания, называется зависимой рабочей книгой, а рабочая книга, содержащая связываемые данные -- исходной рабочей книгой.
Связь между двумя файлами достигается за счет введения в один файл формулы связи со ссылкой на ячейку в другом файле, файл, который получает данные из другого, называется файлом назначения, а файл, который предоставляет данные, -- файлом-источником.
Как только связь устанавливается. Excel копирует величину из ячейки в файле-источнике в ячейку файла назначения. Величина в ячейке назначения автоматически обновляется.
При ссылке на ячейку, содержащуюся в другой рабочей книге, используется следующий синтаксис: [Книга]Лист!Ячейка. Вводя формулу связывания для ссылки на ссылку из другой рабочей книги, используйте имя этой книги, заключенное в квадратные скобки, за которыми без пробелов должно следовать имя рабочего листа, затем восклицательный знак (!), а после него -- адрес ячейки (ячеек). Например "C:Petrov[Журнал1.хls]Литература"!L3.
Обновление связей
Работая с несколькими рабочими книгами и формулам связывания, необходимо знать, как эти связи обновляются. Будут ли результаты формул обновляться автоматически, если изменить данные в ячейках, на которые есть ссылки в только в том случае, если открыты обе рабочие книги.
Если данные в исходной рабочей книге изменяются в тот момент, когда зависимая книга (которая содержит формулу связывания) закрыта, то связанные данные не обновляются немедленно. Открывая зависимую рабочую книгу следующий раз, Excel запросит от вас подтверждение на обновление данных. Чтобы обновить все связанные данные в рабочей книге, выберите ответ Да. Если у вас есть связи, которые обновляются вручную, или вы хотите сами обновить связи, выберите ответ Нет.
Для удаления листа выберите команду Удалить из контекстно-зависимого меню для ярлычков. Для удаления сразу нескольких рабочих листов предварительно выделите их при нажатой клавише Ctrl.
Проверьте:
· знаете ли вы, что такое: рабочая книга Excel; рабочий лист; правила записи формул для связи рабочих листов;
· умеете ли вы: вставлять рабочий лист; удалять; переименовывать; перемещать; копировать; открывать окна; закрывать; упорядочивать; осуществлять связь между листами одной и разных рабочих книг.
Лабораторная работа по Microsoft Excel.
