Как изменить подключение в excel

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.

В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.

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

В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».

Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.

Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).

После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.

Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».

Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:

Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».

В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.

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

Теперь наше подключение будет находиться в книге и доступно в меню «(1) Данные» -> «(2) Подключения».

Как видно из кнопок формы, здесь мы можем:

«(1) Добавить» в книгу новое подключение из уже существующих;

«(2) Удалить» подключение из книги;

Отредактировать «(3) Свойства» подключения;

«(4) Обновить» данные, предоставляемые подключением.

Подытоживая весь вышеописанный опыт, хочется сказать, что конечно же по функционалу и кругу решаемых задач MS Excel не сравнится с такими системами как MS SQL Server Management Studio, Oracle SQL Developer и подобными. Но, в условиях ограниченного времени и доступных инструментов, при необходимости быстро получить доступ к данным из базы, MS Excel вполне справляется.

Как это работает

Инструменты для импорта расположены во вкладке меню «Данные».
Если подключение отключено, перейдите:
Далее:
На вкладке «Центр управления» перейдите:

Подключаем внешние данные из интернет

В Excel 2013 и более поздних версиях, по умолчанию для импорта информации из внешних источников используется надстройка Power Query. Как это работает? Перейдите:
Пропишите адрес сайта, с которого импортируются данные:
Выберите что отобразится, нажмите кнопку «Загрузить».
Информация подгрузится в лист Excel. Работайте с ними как с простым документом: используйте формулы графики, сводные таблицы.
Для обновления нажмите ПКМ по таблице:
Или:

Импорт внешних данных Excel 2010

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

Отличите нового и старого мастера импорта

Преимущества Power Query:

1. Поддерживается работа с большим числом страниц;
2. Промежуточная обработка информации перед загрузкой на лист;
3. Информация импортируется быстрее.

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

1. Заголовки таблицы должны находиться в первой строке.
2. Любая последующая строка должна содержать хотя бы одну непустую ячейку.
3. Объединения ячеек в любых строках запрещены.
4. Для каждой ячейки каждого столбца должен быть определен единый тип хранящихся данных.
5. Диапазон базы данных должен быть отформатирован в качестве списка и иметь свое имя.

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

Функция БИЗВЛЕЧЬ хорошо работает с корректно отформатированными таблицами.

Примеры использования функции БИЗВЛЕЧЬ в Excel

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

Вид таблиц данных и критериев:

В ячейке B2 запишем условие отбора данных следующим способом:

=МИН(СТОЛБЕЦ(B1))

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

В результате получим следующее:

В ячейке A4 запишем следующую формулу:

Описание аргументов:

• A8:F15 – диапазон ячеек, в которых хранится БД;
• 1 – числовое указание номера поля (столбца), из которого будет выводиться значение (необходимо вывести Бренд);
• A2:F3 – диапазон ячеек, в которых хранится таблица критериев.

Результат вычислений:

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

Тип данных: Числовые значения MS Excel

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

Фактически, к числовым типам данных относятся:

• сами числа (и целые и дробные и отрицательные и даже записанные в виде процентов)
• дата и время

Несколько особенностей числовых типов данных

Если введенное число не помещается в ячейку, то оно будет представлено в экспоненциальной форму представления, здорово пугающей неподготовленных пользователей. Например, гигантское число 4353453453453450 х 54545 в ячейку будет записано в виде 2,37459Е+20. Но, как правило в «жизни» появление «странных чисел» в ячейках excel свидетельствует о простой ошибке.

Если число или дата не помещается в ячейку целиком, вместо цифр в ней появляются символы ###. В этом случае «лечение» ещё более простое — нужно просто увеличить ширину столбца таблицы.

Иногда есть необходимостью записать число как текст, например в случае записи всевозможных артикулов товаров и т.п. дело в том, что если вы запишите 000335 в ячейку, Excel посчитав это значение числом, сразу же удалит нули, превратив артикул в 335. Чтобы этого не произошло, просто поместите число в кавычки — это будет сигналом для Excel, что содержимое ячейки надо воспринимать как текст, то есть выводить также, как его ввел пользователь. Естественно, производить с таким числом математических операций нельзя.

Что представляет собой дата в MS Excel?

Если с числами все более-менее понятно, то даты имеют несколько особенностей, о которых стоит упомянуть. Для начала, что такое «дата» с точки зрения MS Excel? На самом деле все не так уж и просто.

Дата в Excel — это число дней, отсчитанных до сегодняшнего дня, от некой начальной даты. По умолчанию этой начальной датой считается 1 января 1900 года.

А что же текущее время? Ещё интереснее — за точку отсчета каждых суток берется 00:00:00, которое представляется как 1. А дальше, эта единичка уменьшается, по мере того как уменьшается оставшееся в сутках время. Например 12.00 дня это с точки зрения MS Excel 0,5 (прошла половина суток), а 18.00 — 0,25 (прошли 3 четверти суток).

В итоге, дата 17 июня 2019 года, 12:30, «языком экселя» выглядит как 43633 (17.06.19) + 0,52 (12:30), то есть число 43633,52.

Классификация типов данных

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

Типы данных делятся на две большие группы: константы и формулы. Отличие между ними состоит в том, что формулы выводят значение в ячейку, которое может изменяться в зависимости от того, как будут изменяться аргументы в других ячейках. Константы – это постоянные значения, которые не меняются.

В свою очередь константы делятся на пять групп:

• Текст;
• Числовые данные;
• Дата и время;
• Логические данные;
• Ошибочные значения.

Текстовые значения

Текстовый тип содержит символьные данные и не рассматривается Excel, как объект математических вычислений. Это информация в первую очередь для пользователя, а не для программы. Текстом могут являться любые символы, включая цифры, если они соответствующим образом отформатированы. В языке DAX этот вид данных относится к строчным значениям. Максимальная длина текста составляет 268435456 символов в одной ячейке.

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

Дата и время

Ещё одним типом данных является формат времени и даты. Это как раз тот случай, когда типы данных и форматы совпадают. Он характеризуется тем, что с его помощью можно указывать на листе и проводить расчеты с датами и временем. Примечательно, что при вычислениях этот тип данных принимает сутки за единицу. Причем это касается не только дат, но и времени. Например, 12:30 рассматривается программой, как 0,52083 суток, а уже потом выводится в ячейку в привычном для пользователя виде.

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

• ч:мм:сс;
• ч:мм;
• ч:мм:сс AM/PM;
• ч:мм AM/PM и др.

Аналогичная ситуация обстоит и с датами:

• ДД.ММ.ГГГГ;
• ДД.МММ
• МММ.ГГ и др.

Есть и комбинированные форматы даты и времени, например ДД:ММ:ГГГГ ч:мм.

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

Логические данные

Довольно интересным является тип логических данных. Он оперирует всего двумя значениями: «ИСТИНА» и «ЛОЖЬ». Если утрировать, то это означает «событие настало» и «событие не настало». Функции, обрабатывая содержимое ячеек, которые содержат логические данные, производят те или иные вычисления.

Разновидности типов данных

Выделяются две большие группы типов данных:

• константы – неизменные значения;
• формулы – значения, которые меняются в зависимости от изменения других.

В группу “константы” входят следующие типы данных:

• числа;
• текст;
• дата и время;
• логические данные;
• ошибки.

Число

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

• числовой;
• денежный;
• финансовый;
• процентный;
• дробный;
• экспоненциальный.

Формат ячейки можно задать двумя способами:

1. Во вкладке “Главная” в группе инструментов “Число” нажимаем по стрелке рядом с текущим значением и в раскрывшемся списке выбираем нужный вариант.
2. В окне форматирования (вкладка “Число”), в которое можно попасть через контекстное меню ячейки.

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

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

Также можно поступить наоборот – сначала ввести значение в нужной ячейке, а формат поменять после.

Текст

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

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

Ошибки

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

• #ДЕЛ/О! – результат деления на число 0
  
• #Н/Д – введены недопустимые данные;
• #ЗНАЧ! – использование неправильного вида аргумента в функции;
• #ЧИСЛО! – неверное числовое значение;
• #ССЫЛКА! – удалена ячейка, на которую ссылалась формула;
• #ИМЯ? – неправильное имя в формуле;
• #ПУСТО! – неправильно указан адрес дапазона.

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

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

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

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

Способ 1: прямое связывание таблиц формулой

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

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

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

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

1. На первом листе выделяем первую ячейку столбца «Ставка». Ставим в ней знак «=». Далее кликаем по ярлычку «Лист 2», который размещается в левой части интерфейса Excel над строкой состояния.



2. Происходит перемещения во вторую область документа. Щелкаем по первой ячейке в столбце «Ставка». Затем кликаем по кнопке Enter на клавиатуре, чтобы произвести ввод данных в ячейку, в которой ранее установили знак «равно».



3. Затем происходит автоматический переход на первый лист. Как видим, в соответствующую ячейку подтягивается величина ставки первого сотрудника из второй таблицы. Установив курсор на ячейку, содержащую ставку, видим, что для вывода данных на экран применяется обычная формула. Но перед координатами ячейки, откуда выводятся данные, стоит выражение «Лист2!», которое указывает наименование области документа, где они расположены. Общая формула в нашем случае выглядит так:

   =Лист2!B2



4. Теперь нужно перенести данные о ставках всех остальных работников предприятия. Конечно, это можно сделать тем же путем, которым мы выполнили поставленную задачу для первого работника, но учитывая, что оба списка сотрудников расположены в одинаковом порядке, задачу можно существенно упростить и ускорить её решение. Это можно сделать, просто скопировав формулу на диапазон ниже. Благодаря тому, что ссылки в Excel по умолчанию являются относительными, при их копировании происходит сдвиг значений, что нам и нужно. Саму процедуру копирования можно произвести с помощью маркера заполнения.

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



5. Все данные из аналогичного столбца на Листе 2 были подтянуты в таблицу на Листе 1. При изменении данных на Листе 2 они автоматически будут изменяться и на первом.

Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ

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

1. Выделяем первый элемент столбца «Ставка». Переходим в Мастер функций, кликнув по пиктограмме «Вставить функцию».



2. В Мастере функций в группе «Ссылки и массивы» находим и выделяем наименование «ИНДЕКС».



3. Данный оператор имеет две формы: форму для работы с массивами и ссылочную. В нашем случае требуется первый вариант, поэтому в следующем окошке выбора формы, которое откроется, выбираем именно его и жмем на кнопку «OK».



4. Выполнен запуск окошка аргументов оператора ИНДЕКС. Задача указанной функции — вывод значения, находящегося в выбранном диапазоне в строке с указанным номером. Общая формула оператора ИНДЕКС такова:

   =ИНДЕКС(массив;номер_строки;[номер_столбца])

   «Массив» — аргумент, содержащий адрес диапазона, из которого мы будем извлекать информацию по номеру указанной строки.

   «Номер строки» — аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.

   «Номер столбца» — аргумент, носящий необязательный характер. Для решения конкретно нашей задачи мы его использовать не будем, а поэтому описывать его суть отдельно не нужно.

   Ставим курсор в поле «Массив». После этого переходим на Лист 2 и, зажав левую кнопку мыши, выделяем все содержимое столбца «Ставка».



5. После того, как координаты отобразились в окошке оператора, ставим курсор в поле «Номер строки». Данный аргумент мы будем выводить с помощью оператора ПОИСКПОЗ. Поэтому кликаем по треугольнику, который расположен слева от строки функций. Открывается перечень недавно использованных операторов. Если вы среди них найдете наименование «ПОИСКПОЗ», то можете кликать по нему. В обратном случае кликайте по самому последнему пункту перечня – «Другие функции…».



6. Запускается стандартное окно Мастера функций. Переходим в нем в ту же самую группу «Ссылки и массивы». На этот раз в перечне выбираем пункт «ПОИСКПОЗ». Выполняем щелчок по кнопке «OK».



7. Производится активация окошка аргументов оператора ПОИСКПОЗ. Указанная функция предназначена для того, чтобы выводить номер значения в определенном массиве по его наименованию. Именно благодаря данной возможности мы вычислим номер строки определенного значения для функции ИНДЕКС. Синтаксис ПОИСКПОЗ представлен так:

   =ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

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

   «Просматриваемый массив» — аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2.

   «Тип сопоставления» — аргумент, являющийся необязательным, но, в отличие от предыдущего оператора, этот необязательный аргумент нам будет нужен. Он указывает на то, как будет сопоставлять оператор искомое значение с массивом. Этот аргумент может иметь одно из трех значений: -1; 0; 1. Для неупорядоченных массивов следует выбрать вариант «0». Именно данный вариант подойдет для нашего случая.

   Итак, приступим к заполнению полей окна аргументов. Ставим курсор в поле «Искомое значение», кликаем по первой ячейке столбца «Имя» на Листе 1.



8. После того, как координаты отобразились, устанавливаем курсор в поле «Просматриваемый массив» и переходим по ярлыку «Лист 2», который размещен внизу окна Excel над строкой состояния. Зажимаем левую кнопку мыши и выделяем курсором все ячейки столбца «Имя».



9. После того, как их координаты отобразились в поле «Просматриваемый массив», переходим к полю «Тип сопоставления» и с клавиатуры устанавливаем там число «0». После этого опять возвращаемся к полю «Просматриваемый массив». Дело в том, что мы будем выполнять копирование формулы, как мы это делали в предыдущем способе. Будет происходить смещение адресов, но вот координаты просматриваемого массива нам нужно закрепить. Он не должен смещаться. Выделяем координаты курсором и жмем на функциональную клавишу F4. Как видим, перед координатами появился знак доллара, что означает то, что ссылка из относительной превратилась в абсолютную. Затем жмем на кнопку «OK».



10. Результат выведен на экран в первую ячейку столбца «Ставка». Но перед тем, как производить копирование, нам нужно закрепить ещё одну область, а именно первый аргумент функции ИНДЕКС. Для этого выделяем элемент колонки, который содержит формулу, и перемещаемся в строку формул. Выделяем первый аргумент оператора ИНДЕКС (B2:B7) и щелкаем по кнопке F4. Как видим, знак доллара появился около выбранных координат. Щелкаем по клавише Enter. В целом формула приняла следующий вид:

    =ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))



11. Теперь можно произвести копирование с помощью маркера заполнения. Вызываем его тем же способом, о котором мы говорили ранее, и протягиваем до конца табличного диапазона.



12. Как видим, несмотря на то, что порядок строк у двух связанных таблиц не совпадает, тем не менее, все значения подтягиваются соответственно фамилиям работников. Этого удалось достичь благодаря применению сочетания операторов ИНДЕКС—ПОИСКПОЗ.

Читайте также:
Функция ИНДЕКС в Экселе
Функция ПОИСКПОЗ в Экселе

Способ 3: выполнение математических операций со связанными данными

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

Посмотрим, как это осуществляется на практике. Сделаем так, что на Листе 3 будут выводиться общие данные заработной платы по предприятию без разбивки по сотрудникам. Для этого ставки сотрудников будут подтягиваться из Листа 2, суммироваться (при помощи функции СУММ) и умножаться на коэффициент с помощью формулы.

1. Выделяем ячейку, где будет выводиться итог расчета заработной платы на Листе 3. Производим клик по кнопке «Вставить функцию».



2. Следует запуск окна Мастера функций. Переходим в группу «Математические» и выбираем там наименование «СУММ». Далее жмем по кнопке «OK».



3. Производится перемещение в окно аргументов функции СУММ, которая предназначена для расчета суммы выбранных чисел. Она имеет нижеуказанный синтаксис:

   =СУММ(число1;число2;…)

   Поля в окне соответствуют аргументам указанной функции. Хотя их число может достигать 255 штук, но для нашей цели достаточно будет всего одного. Ставим курсор в поле «Число1». Кликаем по ярлыку «Лист 2» над строкой состояния.



4. После того, как мы переместились в нужный раздел книги, выделяем столбец, который следует просуммировать. Делаем это курсором, зажав левую кнопку мыши. Как видим, координаты выделенной области тут же отображаются в поле окна аргументов. Затем щелкаем по кнопке «OK».



5. После этого мы автоматически перемещаемся на Лист 1. Как видим, общая сумма размера ставок работников уже отображается в соответствующем элементе.



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



7. Возвращаемся на Лист 2 и изменяем размер ставки любого работника.



8. После этого опять перемещаемся на страницу с общей суммой. Как видим, из-за изменений в связанной таблице результат общей заработной платы был автоматически пересчитан.

Способ 4: специальная вставка

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

1. Выделяем значения, которые нужно будет «затянуть» в другую таблицу. В нашем случае это диапазон столбца «Ставка» на Листе 2. Кликаем по выделенному фрагменту правой кнопкой мыши. В открывшемся списке выбираем пункт «Копировать». Альтернативной комбинацией является сочетание клавиш Ctrl+C. После этого перемещаемся на Лист 1.



2. Переместившись в нужную нам область книги, выделяем ячейки, в которые нужно будет подтягивать значения. В нашем случае это столбец «Ставка». Щелкаем по выделенному фрагменту правой кнопкой мыши. В контекстном меню в блоке инструментов «Параметры вставки» щелкаем по пиктограмме «Вставить связь».
   

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



3. После этого открывается окно специальной вставки. Жмем на кнопку «Вставить связь» в нижнем левом углу ячейки.



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

Урок: Специальная вставка в Экселе

Способ 5: связь между таблицами в нескольких книгах

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

1. Выделяем диапазон данных, который нужно перенести в другую книгу. Щелкаем по нему правой кнопкой мыши и выбираем в открывшемся меню позицию «Копировать».



2. Затем перемещаемся к той книге, в которую эти данные нужно будет вставить. Выделяем нужный диапазон. Кликаем правой кнопкой мыши. В контекстном меню в группе «Параметры вставки» выбираем пункт «Вставить связь».



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

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

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

Разрыв связи между таблицами

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

Способ 1: разрыв связи между книгами

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

1. В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные». Щелкаем по значку «Изменить связи», который расположен на ленте в блоке инструментов «Подключения». Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.



2. Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь».



3. Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи».



4. После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Способ 2: вставка значений

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

1. Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать». Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C.



2. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения», которая размещена в группе инструментов «Параметры вставки».



3. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

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

Управление обновлением внешних ссылок (связей)

​Смотрите также​exLink = Range(«B1»).Value​БМВ​После этого все ссылки​ между областями одной​Но это ещё не​=ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))​на​такова:​ Конечно, это можно​ Связанные табличные диапазоны​

Дополнительные сведения о внешних ссылках

​ и нажмите кнопку​ каждую связь.​ текущее значение. Например​нажмите кнопку​.​ будет получать сообщения​При создании внешних ссылок​ ‘ в ячейке​: Zenkina, в зависимости​ на указанный файл​ книги, а между​ все. Как мы​Теперь можно произвести копирование​Листе 2​=ИНДЕКС(массив;номер_строки;[номер_столбца])​

​ сделать тем же​ могут находиться не​

​ОК​Нажмите​ если формулу​Изменить связи​

​В диалоговом окне​ о наличии разорванных​ (также называемых связями)​ B1 указан полный​

Управление связями с другими книгами

​ от того что​ в текущем документе​ файлами. Естественно, что​ помним, зарплата вычисляется​ с помощью маркера​.​«Массив»​ путем, которым мы​ только в пределах​.​Вручную​= сумм​.​Выделить группу ячеек​ связей​ можно задать для​ путь к файлу.’старое​ вы хотите получить​ будут заменены на​

Ручное обновление всех (или никаких) связей в книге

1. ​ все связанные книги​ путем умножения величины​ заполнения. Вызываем его​«Тип сопоставления»​— аргумент, содержащий​

2. ​ выполнили поставленную задачу​

3. ​ одного листа или​В диалоговом окне​​.​​([Budget.xls]Annual​Нажмите кнопку​установите флажок​​.​​ них поддержку и​

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

1. ​ имя связи​

2. ​ это перебор файлов​

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

   

4. ​ одной книги, но​​Управление связями​​Когда разорвать связь, все​! C10: C25)​

   ​Запрос на обновление связей​Текущий массив​Существует несколько вариантов выбора​ расписание обновления. В​

   ​MsgBox «Укажите файл​ PPTx , перебор​

5. ​Но вышеперечисленный способ подходит​​ быть открыты.​​ Поэтому снова выделяем​

Управление выдачей запроса на обновление всех связей

​ о котором мы​ необязательным, но, в​ которого мы будем​

Отключение выдачи запроса на обновление связей данной книги и обновление связей вручную

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

1. ​ — источник данных»​​ объектов в найденных​​ только в том​​Выделяем диапазон данных, который​​ ячейку, в которой​​ говорили ранее, и​​ отличие от предыдущего​

2. ​ извлекать информацию по​​ оба списка сотрудников​​ отдельных книгах (файлах).​

3. ​Закрыть​

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

Настройка обновления связей по запросу при открытии файла

​ случае, если нужно​ нужно перенести в​​ находится суммированная величина.​​ протягиваем до конца​

Если никакие связи не создавались

1. ​ оператора, этот необязательный​​ номеру указанной строки.​​ расположены в одинаковом​​ Последние два варианта​​.​​ преобразуются в их​​ в​

2. ​Примечание:​​Главная​​.​

3. ​ Microsoft Excel не​​& vbNewLine, vbInformation,​ . Вроде все​ полностью разорвать все​​ другую книгу. Щелкаем​

​ После этого переходим​ табличного диапазона.​ аргумент нам будет​«Номер строки»​ порядке, задачу можно​ на практике используют​Начните работу в Power​ текущее значение. Например​45​ При наличии разорванных связей​в группе​

Разрыв связей с источником

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

1. ​ Pivot​​ если формулу​​после ссылка не​​ будет появляться оповещение​​Буфер обмена​​Данные​​ значения, если это​

2. ​ файлу»​​ проверки, отладки, тем​​ книгами. Что же​ кнопкой мыши и​

   • ​ Дописываем к имеющейся​ то, что порядок​ на то, как​ номером этой самой​

   • ​ ускорить её решение.​ как целью указанной​

3. ​Выберите​​= сумм​​ работает.​

4. ​ об этом.​нажмите кнопку​в группе​

   ​ не задано пользователем.​

5. ​’ запрос пользователю​​ более что там​​ делать, если требуется​​ выбираем в открывшемся​​ в ней формуле​​ строк у двух​​ будет сопоставлять оператор​

6. ​ строчки. При этом​​ Это можно сделать,​​ технологии является как​Главная​([Budget.xls]Annual​​Откройте книгу, содержащую неработающую​​Нажмите кнопку​

7. ​Копировать​Подключения​Книга, содержащая связи, называется​ на новый источник​ объектная модель имеет​ разъединить связанные таблицы,​ меню позицию​

Замена одной формулы ее вычисляемым значением

​ знак умножения (​ связанных таблиц не​ искомое значение с​ важно знать, что​ просто скопировав формулу​ раз уйти от​>​! C10: C25)​​ ссылку.​ ​Обновить​

1. ​.​
   ​нажмите кнопку​ книгой назначения, а​ данных​ свои особенности. Тянет​
   ​ находящиеся в пределах​​«Копировать»​

   1. ​*​ совпадает, тем не​

   2. ​ массивом. Этот аргумент​​ номер строки следует​​ на диапазон ниже.​​ скопления данных, а​​представление диаграммы​​результаты в 45,​​На вкладке​​. Это позволит гарантировать​​Нажмите кнопку​

   3. ​Изменить связи​​ книга, с которой​​With Application.FileDialog(msoFileDialogFilePicker)​​ на раздел ,​​ одного файла? Сделать​

2. ​.​​), а затем щелкаем​​ менее, все значения​​ может иметь одно​​ указывать не относительно​​ Благодаря тому, что​ ​ нагромождение их на​

3. ​.​​ формула будет преобразовано​ ​данные​

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

Если известно, что подключение к исходному файлу отсутствует

​Затем перемещаемся к той​​ по элементу, в​​ подтягиваются соответственно фамилиям​ из трех значений:​ всего документа, а​ ссылки в Excel​ одной странице принципиально​Щелкните правой кнопкой мыши​ в​

Если требуется работать с имеющимися данными, а не заменять их новыми

​нажмите кнопку​​ самых последних данных.​​.​

Если обновление в последний раз заняло слишком много времени

​Нажмите кнопку​​ исходной книгой. Если​​strwPath = .SelectedItems(1)​Zenkina​ данные, а затем​ книге, в которую​ котором располагается показатель​ работников. Этого удалось​-1​​ только относительно выделенного​​ по умолчанию являются​​ проблему не решает.​​ линию связи между​​45​​Изменить связи​Связь с параметрическим запросом​Нажмите стрелку рядом с​

Если эта книга создана другим пользователем и вы не знаете, почему появился этот запрос

​Запрос на обновление связей​​ открытая книга (конечный​​ ‘сохранение пути в​: А вы не​ вставив на то​ эти данные нужно​ коэффициента. Для выполнения​​ достичь благодаря применению​​;​​ массива.​​ относительными, при их​​ Давайте узнаем, как​​ двумя таблицами и​

Если вы каждый раз одинаково отвечаете на этот запрос и хотите, чтобы он больше не отображался

​после ссылка не​.​ нельзя обновить без​ кнопкой​

​.​ файл) содержит связи​​ переменную​

​ согласитесь помочь?​ же место, как​ будет вставить. Выделяем​ вычисления щелкаем по​ сочетания операторов​0​«Номер столбца»​

1. ​ копировании происходит сдвиг​​ создавать и как​​ выберите команду​​ работает.​​Команды​​ открытия книги-источника.​​Параметры вставки​

   ​Щелкните​ с другими книгами​​End With​​TheBestOfTheBest​​ значения. Кстати, этим​​ нужный диапазон. Кликаем​​ клавише​

2. ​ИНДЕКС​​;​​— аргумент, носящий​​ значений, что нам​ работать с таким​​Удалить​Откройте книгу, содержащую неработающую​Изменить связи​Нажмите кнопку​

​, а затем​Не отображать оповещение и​​ или файлами (которые​

​’замена ссвязей с​​: Поможем, шлите файлы​ же способом можно​ правой кнопкой мыши.​Enter​—​1​ необязательный характер. Для​ и нужно. Саму​

1. ​ видом управления данными.​​. Чтобы выбрать несколько​​ ссылку.​​недоступна, если книга​​Не обновлять​​ выберите команду​​ не обновлять автоматические​

2. ​ называются исходными файлами),​​ старого источника данных​​ на почту.​

3. ​ проводить разрыв связи​

​ В контекстном меню​​на клавиатуре. Как​ПОИСКПОЗ​. Для неупорядоченных массивов​

Если известно, что исходный файл доступен для подключения к нему

​ решения конкретно нашей​​ процедуру копирования можно​​Прежде всего, давайте остановимся​ связей, щелкните каждую​В меню​

Если используется параметрический запрос

​ не содержит ссылки.​.​Значения​

1. ​ связи​​ а исходные файлы​​ на новый​

2. ​БМВ​

3. ​ между отдельными диапазонами​

4. ​ в группе​

5. ​ видим, программа рассчитала​​.​​ следует выбрать вариант​

support.office.com

Исправление недействительных связей с данными

​ задачи мы его​​ произвести с помощью​ на вопросе, какими​ из них, удерживая​Правка​В поле​Закройте конечную книгу.​.​.​ изменятся, то связи​ActiveWorkbook.ChangeLink Name:= _​: TheBestOfTheBest, на всякий​ данных различных книг​«Параметры вставки»​ общую заработную плату​Читайте также:​«0»​ использовать не будем,​ маркера заполнения.​ способами существует возможность​ нажатой клавишу CTRL.​

​выберите пункт​Источник файла​Откройте книгу-источник.​Нажмите кнопку​Возможно, связь была создана​ в открываемой книге​exLink _​ случай, вдруг в​ без разрыва общей​выбираем пункт​ по предприятию.​Функция ИНДЕКС в​. Именно данный вариант​ а поэтому описывать​Итак, ставим курсор в​ создать связь между​В диалоговом окне предупреждения​

​Связи​выберите неработающую ссылку,​

​Откройте конечную книгу.​​Не обновлять​ случайно путем перемещения​ могут указывать на​, NewName:= _​ новинку и​ связи между файлами.​«Вставить связь»​

Исправление поврежденную ссылку

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

1. ​ подтвердите удаление связи​.​

2. ​ которую нужно удалить.​​Нажмите кнопку​​. Обновление из источника​​ или копирования диапазона​​ устаревшие данные.​

   ​strwPath, _​​Zenkina​​ Посмотрим, как этот​.​

3. ​Лист 2​​Функция ПОИСКПОЗ в​​ случая.​ не нужно.​

   ​ элемента с формулой.​​Самый простой способ связывания​ и нажмите кнопку​​Команда​Примечание:​

4. ​Обновить​​ при отсутствии подключения​​ ячеек, листа или​

5. ​1. Конечная книга содержит​Type:=xlExcelLinks​

6. ​: Доброе утро! Дорогой​ метод работает на​​После этого значения будут​​и изменяем размер​

7. ​ Экселе​​Итак, приступим к заполнению​​Ставим курсор в поле​

Удаление недействительной ссылки

​ После этого курсор​ данных – это​ОК​связи​ Чтобы удалить несколько связей,​.​​ к нему невозможно.​​ диаграммы из одной​​ внешние ссылки.​​Range(«B1»).FormulaR1C1 = strwPath​ БМВ, благодаря вашей​ практике.​​ вставлены. При изменении​​ ставки любого работника.​Прямое связывание данных хорошо​

1. ​ полей окна аргументов.​«Массив»​

2. ​ должен преобразоваться в​​ использование формул, в​​.​​недоступна, если книга​​ удерживая нажатой клавишу​

   ​Примечание:​​ Например, если книга-источник​​ книги в другую.​2. Внешняя ссылка (или​

3. ​ ‘сохранение в ячейку​​ подсказке у меня​​Выделяем диапазон, в котором​ данных в исходной​

   ​После этого опять перемещаемся​​ ещё тем, что​ Ставим курсор в​​. После этого переходим​ маркер заполнения в​

4. ​ которых имеются ссылки​​Примечания:​​ не содержит ссылки.​

5. ​и затем щелкните​​ Мы стараемся как можно​​ расположена в сети,​

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

Исправление поврежденную ссылку

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

1. ​ оперативнее обеспечивать вас​ а подключение к​

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

   ​ из рабочей книги​​ общей суммой. Как​​ выводить в одну​«Искомое значение»​

3. ​Лист 2​​ Выполняем зажим левой​​ диапазоны. Он называется​Чтобы отобразить отношения в​

   ​исходный файл​​Щелкните элемент​ актуальными справочными материалами​​ сети отсутствует.​ связи и разорвите​

4. ​ ячеек в книге-источнике.​​ запуска​​ помощь. TheBestOfTheBest вам​

5. ​ Щелкаем по нему​ будет их подтягивать​

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

Отключите автоматическое обновление связанных данных

1. ​ способ интуитивно понятен,​ импорте нескольких таблиц​

2. ​ которую требуется удалить.​​.​​ Эта страница переведена​​Не обновлять​​ их значениями.​

   ​ значение из которой​​ЗЫ. возможно, кто-то​​ отзывчивость и готовность​ В раскрывшемся меню​

3. ​ не обязательно, чтобы​​ результат общей заработной​​ других табличных диапазонах,​«Имя»​

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

4. ​Нажмите кнопку​​ автоматически, поэтому ее​​.​

Удаление недействительной ссылки

​Важно:​ загружается и помещается​ знает спрособ попроще​ выручить. Всем хорошего​ выбираем пункт​ для этого были​​ платы был автоматически​​ но и производить​​на​​«Ставка»​Все данные из аналогичного​ нем связывание выполняется​​ таблиц Excel также​​ Чтобы удалить несколько связей,​Закрыть​

1. ​ текст может содержать​Нажмите кнопку​

2. ​ При разрыве связей с​​ в конечную книгу.​​ ))​​ дня.​​«Копировать»​

   ​ открыты обе книги.​​ пересчитан.​​ с ними различные​Листе 1​

3. ​.​​ столбца на​​ практически точно так​ создает модель. Преднамеренно,​

   ​ удерживая нажатой клавишу​​.​ неточности и грамматические​​Не обновлять​ источником все формулы,​

4. ​Когда обе связанные книги​​Vitalink​​БМВ​

См. также

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

support.office.com

Задание связей между таблицами в модели данных

​.​​После того, как координаты​Листе 2​ же, как создание​ можно создать модель​и щелкните каждую​Важно:​ ошибки. Для нас​. Если не требуется​ использующие источник, заменяются​ открыты на одном​: Необходима похожая функция.​: Zenkina, Я очень​ можно набрать альтернативную​ только рабочую книгу,​ Excel можно также​ деление, вычитание, умножение​После того, как координаты​ отобразились в окошке​были подтянуты в​ ссылок на данные​ данных, чтобы использовать​

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

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

​ значение. Например, связь​ автоматически. Когда конечная​ что:​ и в ответ​Ctrl+C​ подтянет данные из​ вставки.​Посмотрим, как это осуществляется​ в поле​ в поле​Листе 1​ массиве.​ для отчетов сводных​Разорвать​ Описанные ниже не​

​ полезна. Просим вас​

1. ​ не обновляя все​​ =СУММ([Бюджет.xls]Годовой!C10:C25) будет заменена​​ книга открыта, а​​1. Источников данных​​ могу только сказать,​

2. ​.​​ закрытого связанного документа,​​Выделяем значения, которые нужно​ на практике. Сделаем​

3. ​«Просматриваемый массив»​​«Номер строки»​​. При изменении данных​

4. ​Посмотрим, как на примере​ таблиц, сводных диаграммах​.​​ исчезает гиперссылок. Подробнее​​ уделить пару секунд​

5. ​ связи. После открытия​​ на =45. Эту​​ книга-источник не открыта,​​ несколько. Имена файлов​​ что вы молодец​

​Далее, не снимая выделения​ если в нем​

1. ​ будет «затянуть» в​​ так, что на​​и переходим по​​. Данный аргумент мы​​ на​

2. ​ можно образовать связь​ и Power View.​Импорт данных из файла​ о гиперссылках, читайте​​ и сообщить, помогла​​ книги нажмите на​ операцию нельзя отменить,​ в области сведений​ отличаются 2 символами​

3. ​ и относитесь к​ с того же​ ранее были проведены​​ другую таблицу. В​​Листе 3​

   ​ ярлыку​​ будем выводить с​

   • ​Листе 2​ путем прямого связывания.​ Дополнительные сведения в​ в формате CSV,​ в статье Создание,​ ли она вам,​ вкладке​ поэтому стоит сохранить​ о документе возможно​ (цифры и буквы).​ той категории ,​ фрагмента, опять кликаем​ изменения.​ нашем случае это​будут выводиться общие​

   • ​«Лист 2»​ помощью оператора​они автоматически будут​​ Имеем две таблицы​​ статье Создание модели​​ HTML или текстового​​ редактирование и удаление​ с помощью кнопок​Данные​ версию файла​ появление запроса на​ Имена файлов-источников содержат​ кто не сидит​ по нему правой​Но нужно отметить, что​ диапазон столбца​ данные заработной платы​, который размещен внизу​ПОИСКПОЗ​ изменяться и на​ на двух листах.​ данных в Excel.​

   • ​ файла​ гиперссылок​ внизу страницы. Для​в группе​.​ обновление связей. Можно​ наименование месяца (первые​ сложа руки со​ кнопкой мыши. На​ в этом случае​

   • ​«Ставка»​ по предприятию без​ окна Excel над​. Поэтому кликаем по​ первом.​ На одной таблице​Вы можно вернуться удаления​

​Примечание:​

support.office.com

Работа со связанными таблицами в Microsoft Excel

​Внимание:​ удобства также приводим​Подключения​На вкладке​ задать режим вывода​ три буквы: «ЯНВ,​ своей проблемой и​ этот раз в​ вставка будет произведена​на​ разбивки по сотрудникам.​ строкой состояния. Зажимаем​ треугольнику, который расположен​

​Но что делать, если​ производится расчет заработной​

​ в Excel, если​Мы стараемся как​ Это действие нельзя отменить.​ ссылку на оригинал​кнопку​Данные​ этого запроса, а​ ФЕВ, .., ИЮН,​ ждет что кто-то​ списке действий щелкаем​ в виде неизменяемого​Листе 2​ Для этого ставки​ левую кнопку мыши​ слева от строки​ перечень сотрудников в​ платы с помощью​ вы закроете​ можно оперативнее обеспечивать​ Сохранение резервной копии​ (на английском языке).​Изменить связи​в группе​ также режим обновления​ СЕН»)​ сделает все за​ по иконке​ массива. При попытке​. Кликаем по выделенному​ сотрудников будут подтягиваться​ и выделяем курсором​ функций. Открывается перечень​ табличных массивах расположен​ формулы путем умножения​Управление связями​ вас актуальными справочными​ книги перед началом​Если книга содержит ссылки​, а затем обновите​

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

​Подключения​ всех связей без​2. Ежемесячно в​ них. Так держать.​«Значения»​

Способ 1: прямое связывание таблиц формулой

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

​Lavr​, которая размещена в​ со вставленными данными​ мыши. В открывшемся​Листа 2​«Имя»​ Если вы среди​ порядке? В этом​ единый для всех​Отменить​ языке. Эта страница​ потребоваться.​

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

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

1. ​ коэффициент.​. При удалении связи​​ переведена автоматически, поэтому​​Откройте книгу, содержащую неработающую​ файл, который был​​Нажмите кнопку​​.​ содержит несколько связей,​​ как источники так​​ файла один «источник​«Параметры вставки»​ информирующее о невозможности​«Копировать»​

   

2. ​ функции​После того, как их​«ПОИСКПОЗ»​ ранее, одним из​​На втором листе расположен​​ в Power Pivot,​ ее текст может​​ ссылку.​​ перемещен в другое​Не обновлять​В списке​ можно обновлять только​ и «рабочий»-сводный файл.​​ данных» другой «рабочий»​​.​

   

3. ​ сделать это.​. Альтернативной комбинацией является​СУММ​ координаты отобразились в​, то можете кликать​ вариантов является установка​ табличный диапазон, в​ не существует способа​ содержать неточности и​В меню​ место, можно устранить​. Обратитесь к владельцу​Источник​ некоторые из них.​ Путь к папке​​В «рабочем» файле​​После этого все ссылки​Изменения в таком массиве,​ сочетание клавиш​) и умножаться на​ поле​

   ​ по нему. В​

   

4. ​ связи между каждой​ котором находится перечень​ для отмены удаления​ грамматические ошибки. Для​Правка​ ссылку обновление пути,​ книги. Вы также​выберите связь, которую​Закройте все книги. Если​ содержит год и​ много формул с​ в выделенном диапазоне​ связанном с другой​Ctrl+C​ коэффициент с помощью​«Просматриваемый массив»​ обратном случае кликайте​ из тех ячеек,​ сотрудников с их​ связи. Связь можно​ нас важно, чтобы​выберите пункт​ исходный файл. Если​ можете выяснить, какие​ требуется разорвать.​ одна книга-источник будет​ месяц. Имена файлов-отчетов​

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

   

5. ​Связи​ не удается найти​​ связи находятся в​​Для выделения нескольких связанных​ открыта, а остальные​​ содержат наименование месяца​​ ячейки в «источнике​ статические значения.​​ только разорвав связь.​​ на​Выделяем ячейку, где будет​«Тип сопоставления»​

Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ

​ пункту перечня –​ вручную. Но это​ в обоих случаях​ это действие требует​ вам полезна. Просим​.​ или не имеют​ книге. На вкладке​ объектов щелкните каждый​ закрыты, обновления не​ аналогично файлам-источникам.​ данных».​Как видим, в Excel​Иногда требуется разорвать связь​Лист 1​ выводиться итог расчета​и с клавиатуры​«Другие функции…»​ подойдет разве что​ представлен в одном​ завершения повторное вычисление​ вас уделить пару​Команда​ доступа к документу,​Данные​​ из них, удерживая​​ будут одинаковыми.​​3. Список файлов-источников​​Структура у «источника​ имеются способы и​ между табличными диапазонами.​.​ заработной платы на​ устанавливаем там число​

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

   

2. ​в группе​​ нажатой клавишу CTRL.​​Откройте книгу, содержащую связи.​​ предопределен. Может меняться​​ данных» не меняется,​ инструменты, чтобы связать​​ Причиной этого может​​Переместившись в нужную нам​

   

3. ​Листе 3​«0»​Запускается стандартное окно​ Для массивных диапазонов​Нужно сделать так, чтобы​ Таким образом всегда​ помогла ли она​недоступна, если книга​ можно запретить Excel​Подключения​Чтобы выделить все связи,​​Чтобы обновить связи, нажмите​​ в течении года​

   

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

   ​ не содержит ссылки.​

   ​ при попытке обновить​​нажмите кнопку​ нажмите клавиши CTRL+A.​ кнопку​ при появлении новых​ ячейках и название​

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

   ​В списке​​ связь, как отключить​Изменить связи​Щелкните элемент​Параметры​ объектов, но очень​ самого файла.​ табличные данные могут​ изменить массив, вставленный​

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

   

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

   

6. ​ случае это столбец​​Следует запуск окна​​ что мы будем​«Ссылки и массивы»​ в худшем –​​На первом листе выделяем​​ связях просмотреть подробные​ оригинал (на английском​ которую требуется экспортировать.​​Более новые версии​​ и запретить его​имя связи автоматически не​​ затем выберите команду​​ при открытии отчета​

   

7. ​ пользователя путь к​ в разных книгах.​​ нежелание пользователя, чтобы​​«Ставка»​Мастера функций​ выполнять копирование формулы,​. На этот раз​ на практике вообще​ первую ячейку столбца​ сведения.​ языке) .​Примечание:​​ Office 2011 ​​ вывод для этой​​ удаляется, поэтому его​​Включить это содержимое​

   ​ обновлялись ссылки на​

   ​ новому «источнику данных»​​ При необходимости эту​ данные в одной​. Щелкаем по выделенному​. Переходим в группу​ как мы это​ в перечне выбираем​ будет неосуществим. Но​«Ставка»​СВЯЗАННЫЕ функции выражений анализа​Таблицы в модели данных​ Чтобы исправить нескольких связей,​Важно:​​ книги в будущем.​​ может потребоваться удалить.​.​

   ​ источники.​​И заменить существующую​ связь можно легко​ таблице автоматически обновлялись​ фрагменту правой кнопкой​«Математические»​ делали в предыдущем​ пункт​ решить данную проблему​. Ставим в ней​​ данных (DAX) использует​​ могут иметь несколько​​ удерживайте нажатой​​ связанный объект не так​

   ​Не отображать запрос и​​Удаление имени​Закройте все книги.​Часть записанной макрорекордером​ связь на новую.​ разорвать.​ из другой.​ мыши. В контекстном​и выбираем там​ способе. Будет происходить​«ПОИСКПОЗ»​ можно при помощи​ знак​​ связи между таблицами,​​ связей. Скорее всего,​​и затем щелкните​​ же, как гиперссылки.​​ обновлять связи автоматически​​На вкладке​Откройте книгу, содержащую связи.​​ функции:​​З.Ы. Связь в​Автор: Максим Тютюшев​Разорвать связь между книгами​

   ​ меню в блоке​ наименование​ смещение адресов, но​. Выполняем щелчок по​​ связки операторов​​«=»​ чтобы найти соответствующие​​ будет несколько связей,​​ каждую связь.​​ Описанные ниже не​​    ​

   

8. ​Формулы​На вкладке​ActiveWorkbook.ChangeLink _​​ книге всего одна.​​Zenkina​ во всех ячейках​​ инструментов​​«СУММ»​ вот координаты просматриваемого​ кнопке​ИНДЕКС​. Далее кликаем по​ значения из другой​​ если вы специально​​Выберите команду​

   

9. ​ исчезает гиперссылок. Подробнее​Этот параметр влияет на​в группе​​Данные​​Name:=»\СетеваяШара20121202-ФЕВ_12ОБЖ08_ФЕВ.xls», _​​subtlety​​: Уважаемые коллеги, подскажите,​ можно, выполнив фактически​​«Параметры вставки»​​. Далее жмем по​ массива нам нужно​​«OK»​​–​ ярлычку​ таблицы. После удаления​ создаете дополнительные связи​Смена источника​ о гиперссылках, читайте​ все книги, которые​Определенные имена​в группе​NewName:=»\СетеваяШара20121203-МАР_12ОБЖ08_МАР.xls», _​: Связь в смысле​ как можно изменить​ одну операцию. При​щелкаем по пиктограмме​​ кнопке​​ закрепить. Он не​.​ПОИСКПОЗ​«Лист 2»​ связи, он будет​ между уже связанными​.​​ в статье Создание​​ открываются на компьютере.​

   

10. ​нажмите кнопку​Подключения​Type:=xlExcelLinks​​ — формула?​​ источник данных для​ этом данные в​«Вставить связь»​«OK»​ должен смещаться. Выделяем​Производится активация окошка аргументов​​. Посмотрим, как это​​, который размещается в​ возвращать другие результаты.​ таблицами или если​Выполните одно из следующих​ или изменение гиперссылки.​ Пользователи, которые откроют​​Диспетчер имен​​нажмите кнопку​​ActiveWorkbook.ChangeLink _​​Можно использовать св-во​ связей одним нажатием.​​ ячейках останутся, но​​.​.​ координаты курсором и​ оператора​​ можно осуществить, связав​​ левой части интерфейса​ Дополнительные сведения, Функция​

    ​ импортируются таблицы, которые​

    

11. ​ действий:​Внимание:​ книгу на другом​.​Изменить связи​Name:=»\СетеваяШара20121202-ФЕВ_12ОБЖ09_ФЕВ.xls», _​ Formula объекта Range.​Данные из excel​

    

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

​NewName:=»\СетеваяШара20121203-МАР_12ОБЖ09_МАР.xls», _​
​Например вот так:​ передаются связями в​
​ представлять собой статические​ Он, кстати, является​

Способ 3: выполнение математических операций со связанными данными

​ аргументов функции​ клавишу​. Указанная функция предназначена​ диапазонах, о которых​ состояния.​Помимо изменения результатов сводной​ связей, определенных в​Действия​ Сохранение резервной копии​ затронуты.​Имя​Из списка​

​Type:=xlExcelLinks​Worksheets(«Sheet1»).Range(«A1»).Formula = «=$A$4+$A$10″​ PowerPoint 2016. Изменен​​ не обновляемые значения,​​ единственным для более​СУММ​F4​ для того, чтобы​ шел разговор в​Происходит перемещения во вторую​ таблицы и формулы,​​ исходном источнике данных.​​Устранение Недействительная ссылка на​ книги перед началом​​На вкладке​​выберите имя, которое​Источник​Vitalink​

1. ​Lavr​ путь нахождения источника​ которые никак не​​ старых версий Excel.​​, которая предназначена для​. Как видим, перед​​ выводить номер значения​​ предыдущем способе.​

   

2. ​ область документа. Щелкаем​​ операции создания и​​При наличии нескольких связей​​ лист в другую​​ этой процедуры может​Файл​​ следует удалить, и​​выберите связанный объект,​: Воспользовался помощью​​: , например, на​​ Excel.​

   

3. ​ зависят от других​ В контекстном меню​​ расчета суммы выбранных​​ координатами появился знак​ в определенном массиве​Выделяем первый элемент столбца​ по первой ячейке​

   ​ удаления связей приводят​

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

   

4. ​ по его наименованию.​«Ставка»​ в столбце​ к пересчету книги,​ них выступает как​В диалоговом окне​Откройте книгу, содержащую неработающую​Параметры​Удалить​Для выделения нескольких связей​Sub ИзменениеСсылок()​=ЕСЛИ(‘C:Documents and SettingsUserDesktopcalcRRV[Shop_List_2012_last.xls]Пакет​​ изменить источник, только​​В книге, в которой​

   

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

   

6. ​ активная и реализующая​Открыть​ ссылку.​и выберите категорию​.​ щелкните каждый связанный​With Application​ ‘!$Q$16>0;»Считать»;»»)​ выбирая для каждой​ подтягиваются значения из​«Специальная вставка»​=СУММ(число1;число2;…)​ из относительной превратилась​​ возможности мы вычислим​​Мастер функций​. Затем кликаем по​ некоторое время.​ навигацию по данным​найдите книгу и​На вкладке​​Дополнительно​​Если используется диапазон внешних​ объект, удерживая нажатой​.ScreenUpdating = False​Мне нужно макросом​

   

7. ​ связи отдельно, это​​ других файлов, переходим​​. В открывшемся дополнительном​Поля в окне соответствуют​

   

8. ​ в абсолютную. Затем​ номер строки определенного​, кликнув по пиктограмме​ кнопке​К началу страницы​ и путь вычисления.​ нажмите кнопку​данные​

Способ 4: специальная вставка

​.​ данных, параметр запроса​ клавишу CTRL.​.EnableEvents = False​

1. ​ заменить во всех​ очень долго и​ во вкладку​ меню выбираем позицию​ аргументам указанной функции.​​ жмем на кнопку​​ значения для функции​​«Вставить функцию»​​Enter​При выполнении определенных задач​ Все дополнительные связи​Изменить​​нажмите кнопку​​В Excel 2007 нажмите​ может также использовать​​Чтобы выделить все связи,​​iExcelLinks = ThisWorkbook.LinkSources(xlExcelLinks)​ формулах заменить полное​​ слишком много презентаций​​«Данные»​

   

2. ​ с одноименным названием.​ Хотя их число​«OK»​ИНДЕКС​.​на клавиатуре, чтобы​​ в Excel иногда​​ между парой таблиц​.​Изменить связи​ кнопку Microsoft Office​ данные из другой​​ нажмите клавиши CTRL+A.​​iPath = ActiveWorkbook.Path​​ имя файла:​​ нужно обновить. Все​

   

   ​. Щелкаем по значку​После этого открывается окно​ может достигать 255​.​. Синтаксис​В​ произвести ввод данных​​ приходится иметь дело​​ считаются неактивными.​Исправление Недействительная ссылка на​.​

   

3. ​, щелкните​ книги. Может потребоваться​Нажмите кнопку​​ДлинаПути = Len(iPath)​​’C:Documents and SettingsUserDesktopcalcRRV[Shop_List_2012_last.xls]​ презентации на одном​

   

4. ​«Изменить связи»​ специальной вставки. Жмем​ штук, но для​Результат выведен на экран​ПОИСКПОЗ​Мастере функций​ в ячейку, в​ с несколькими таблицами,​Можно удалять существующие связи​ книгу или другой​

​Команды​Параметры Excel​

Способ 5: связь между таблицами в нескольких книгах

​ проверить и удалить​Обновить значения​Месяц = Mid(iPath,​на то каокое​ источнике.​, который расположен на​ на кнопку​ нашей цели достаточно​ в первую ячейку​представлен так:​в группе​ которой ранее установили​ которые к тому​ между таблицами, если​ документ Office​Изменить связи​и выберите категорию​ эти типы связей.​.​

1. ​ ДлинаПути — 5,​ укажет пользователь, например:​TheBestOfTheBest​ ленте в блоке​«Вставить связь»​ будет всего одного.​ столбца​​=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​​«Ссылки и массивы»​

   

2. ​ знак​ же связаны между​ вы уверены, что​Введите новое имя или​недоступна, если книга​Дополнительно​В Excel формула удаляется​Можно настроить автоматическое обновление​​ 3)​​’C:Documents and SettingsUserDesktop[новый_прайс.xls]​​: Одно из решений​​ инструментов​

   

3. ​в нижнем левом​ Ставим курсор в​«Ставка»​«Искомое значение»​находим и выделяем​«равно»​ собой. То есть,​ они не требуются,​ расположение для документа​ не содержит ссылки.​.​ при замене на​ связей при запуске​If IsArray(iExcelLinks) =​ikki​ — макросом в​«Подключения»​ углу ячейки.​

​ поле​. Но перед тем,​— аргумент, содержащий​ наименование​.​ данные из одной​ но имейте в​ в поле​В поле​В разделе​

​ возвращаемое значение. Чтобы​ или после запуска.​ True Then​: в ексель есть:​

Разрыв связи между таблицами

​ PP заменить описание​. Нужно отметить, что​Какой бы вариант вы​«Число1»​ как производить копирование,​ наименование или адрес​«ИНДЕКС»​Затем происходит автоматический переход​ таблицы подтягиваются в​ виду, что это​измените ссылки, чтобы​Источник файла​

Способ 1: разрыв связи между книгами

​Общие​ отменить случайную замену​Предупреждение:​For Each iLink​1. управление связями​ всех источников по​ если текущая книга​ не выбрали, значения​. Кликаем по ярлыку​ нам нужно закрепить​ ячейки стороннего диапазона,​.​

1. ​ на первый лист.​ другие и при​ может привести к​текст и нажмите​​выберите неработающую ссылку,​​снимите флажок​​ формулы ее значением,​​ Этот режим влияет на​ In iExcelLinks​ (до 2003 -​​ списку.​​ не содержит связей​ из одного табличного​«Лист 2»​ ещё одну область,​ в которой оно​Данный оператор имеет две​

   

2. ​ Как видим, в​ их изменении пересчитываются​ ошибкам в сводных​ кнопку​ которую требуется исправить.​Запрашивать об обновлении автоматических​ немедленно после ввода​​ всех пользователей книги.​​iLinks = iLinks​

   

3. ​ правка — связи;​Zenkina​ с другими файлами,​ массива будут вставлены​над строкой состояния.​ а именно первый​ находится. Именно позицию​ формы: форму для​​ соответствующую ячейку подтягивается​​ значения во всех​

   

4. ​ таблицах или в​ОК​Примечание:​ связей​ или вставки значения​

Способ 2: вставка значений

​ Если отключить обновление​ & vbCrLf &​ в 2007/2010 -​: Нет опыта написания​ то эта кнопка​ в другой. При​После того, как мы​ аргумент функции​ данного наименования в​ работы с массивами​ величина ставки первого​ связанных табличных диапазонах.​ формулах, которые ссылаются​.​ Чтобы исправить нескольких связей,​. Если этот флажок​ нажмите кнопку​ связей и выдачу​ iLink​ не знаю где,​ макроса в PP​ является неактивной.​ изменении данных в​ переместились в нужный​

1. ​ИНДЕКС​ целевом диапазоне и​ и ссылочную. В​ сотрудника из второй​Скачать последнюю версию​ на эти таблицы.​Откройте книгу, содержащую неработающую​​ удерживайте нажатой​​ снят, связи будут​Отменить​ запроса, пользователи этой​​Оригинал = Mid(iLink,​​ но тоже есть​

   

2. ​Zenkina​Запускается окно изменения связей.​ исходнике они также​ раздел книги, выделяем​. Для этого выделяем​ следует вычислить. В​ нашем случае требуется​ таблицы. Установив курсор​​ Excel​​ После удаления или​ ссылку.​​и затем щелкните​​ обновляться автоматически (без​

   

3. ​.​ книги не будут​ ДлинаПути + 2,​ :)​

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

​ выдачи запроса).​

lumpics.ru

Изменение связей источник Excel

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

​ActiveWorkbook.ChangeLink Name:=iLink, _​​что это значит?​ сотрудник помочь не​ их несколько) тот​ диапазоне.​ курсором, зажав левую​

​ перемещаемся в строку​​ будут выступать ссылки​ выбора формы, которое​

​ для вывода данных​​ большого объема информации.​ измениться непредвиденным образом.​выберите пункт​Смена источника​ пользователей этой книги​ формула массива, выделите​На вкладке​NewName:=iPath + «»​делаем небольшой пример​

​ смог. Нас покинул​​ файл, с которым​Урок: Специальная вставка в​ кнопку мыши. Как​

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

​Запуск в Excel​​Связи​.​    ​ диапазон ячеек, содержащих​Данные​ + Оригинал +​ из двух файлов,​ сотрудник, который почистил​ хотим разорвать связь.​ Экселе​ видим, координаты выделенной​ аргумент оператора​Листе 1​ его и жмем​ обычная формула. Но​

​ в одной таблице,​​Щелкните​.​

​Перейдите к расположению файла,​​Предупреждение:​ формулу массива.​

​в группе​​ Месяц + «.xls»,​ записываем макрорекодером действия​ за собой все,​

​ Щелкаем по кнопке​​Кроме того, можно организовать​ области тут же​ИНДЕКС​, в которых расположены​ на кнопку​ перед координатами ячейки,​ к тому же,​Данные​Команда​ содержащего связанных данных.​

​ Этот режим влияет на​​Выбор диапазона, содержащего массив​Подключения​ _​ по ручному изменению​ чудом нашли источник,​«Разорвать связь»​ связь между табличными​ отображаются в поле​(​ имена сотрудников.​«OK»​ откуда выводятся данные,​ если она не​

planetaexcel.ru

VBA. Изменить связь с внешней книгой.

​>​​связи​Выберите новый исходный файл​ всех пользователей книги.​
​   ​нажмите кнопку​Type:=xlExcelLinks​ связи, меняем записанный​ очень нужно восстановить​
​.​ областями в разных​ окна аргументов. Затем​B2:B7​«Просматриваемый массив»​
​.​ стоит выражение​ однородная, не очень​Отношения​
​недоступна, если книга​ и нажмите кнопку​
​ Если отключить обновление​Щелкните любую ячейку в​

​) и щелкаем по​​— аргумент, представляющий​Выполнен запуск окошка аргументов​
​«Лист2!»​ удобно. С подобными​
​.​ не содержит ссылки.​Изменить источник​ связей и выдачу​
​ диапазоне массив.​
​.​Else​
​если что не​

planetaexcel.ru

​ подтвердите удаление связи​

Подключение к другой книге

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

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

С помощью функции Получить и преобразовать данные» (Power Query) Excel можно подключиться к книге Excel.

На вкладке Данные щелкните Получить данные > Из файла > Из книги. Если вы не видите кнопки Получить данные, нажмите кнопку Создать запрос и выберите пункты Из файла > Из книги.

Найдите книгу в окне Импорт данных.

В окне Навигатор выберите таблицу или лист, которые вы хотите импортировать, а затем нажмите кнопку Загрузить или Изменить.

В Excel 2010 и 2013 существует два способа подключения к другой книге. Рекомендуется использовать Power Query (для этого нужно скачать надстройку Power Query). Если вам не удается скачать надстройку Power Query, вы можете использовать мастер подключения к данным.

На вкладке ленты Power Query щелкните Из файла > Из Excel.

Перейдите к книге.

В окне Навигатор выберите таблицу или лист, которые вы хотите импортировать, а затем нажмите кнопку Загрузить или Изменить.

Мастер подключения к данным

Шаг 1. Создание подключения к книге

На вкладке Данные нажмите кнопку Подключения.

В диалоговом окне Подключения к книге нажмите кнопку Добавить.

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

Найдите нужную книгу и нажмите кнопку Открыть.

В диалоговом окне Выбор таблицы выберите нужную таблицу (лист) и нажмите кнопку ОК.

В диалоговом окне Выбор таблицы листы называются таблицами.

Одновременно можно добавить только одну таблицу.

Вы можете переименовать таблицу, нажав кнопку Свойства. Вы также можете добавить описание.

Чтобы добавить дополнительные таблицы, повторите шаги 2–5.

Нажмите кнопку Закрыть.

Шаг 2. Добавление таблиц к листу

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

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

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

Мастер подключения к данным

Шаг 1. Создание подключения к книге

На вкладке Данные нажмите кнопку Подключения.

В диалоговом окне Подключения к книге нажмите кнопку Добавить.

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

Найдите нужную книгу и нажмите кнопку Открыть.

В диалоговом окне Выбор таблицы выберите нужную таблицу (лист) и нажмите кнопку ОК.

В диалоговом окне Выбор таблицы листы называются таблицами.

Одновременно можно добавить только одну таблицу.

Вы можете переименовать таблицу, нажав кнопку Свойства. Вы также можете добавить описание.

Чтобы добавить дополнительные таблицы, повторите шаги 2–5.

Нажмите кнопку Закрыть.

Шаг 2. Добавление таблиц к листу

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

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

Обновление данных книги

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

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

Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.

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

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

Первый шаг.

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

Второй шаг.

Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»

Третий шаг.

Перейти на лист документа «Excel», в который Вы планируете транслировать информацию из первой таблицы.

Четвертый шаг.

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

После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.

Результат вставки связи

Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».

При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.

Как обновить сводную таблицу

При клике правой кнопкой мыши по сводной таблице и нажатии на пункт «Обновить» сводная таблица автоматически перенесет все данные из связанного массива информации («таблицы донора»).

О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:

Как делать сводные таблицы в программе «Excel» и для чего они нужны.

Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».

Правда нужно отметить, что этот способ подходит только пользователям Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».

Смысл способа в следующем:

Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».

Из таблицы -Power Query

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

Источник данных для запроса Power Query

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

После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»

Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа (список «Запросы книги»). После клика правой кнопкой мыши в выпадающем контекстном меню следует нажать на пункт «Обновить»

Обновление запроса в PowerQuery

Сводные таблицы Excel

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

Способ 1. Функция ДВССЫЛ

В простом случае можно использовать функцию ДВССЫЛ (INDIRECT), чтобы сформировать правильную ссылку на внешний файл. Например, если необходимо создать выпадающий список с содержимым ячеек А1:А10 с листа Список из файла Товары.xls, нужно открыть окно проверки данных через вкладку Данные – Проверка данных (Data – Validation) и в поле Источник (Source) ввести следующую конструкцию: =ДВССЫЛ(«[Товары.xls]Список!$A$1:$A$10») .

Чтобы сформировать правильную ссылку на внешний файл можно использовать функцию ДВССЫЛ

Функция ДВССЫЛ (INDIRECT) преобразует текстовую строку аргумента в реальный адрес, используемый для ссылки на данные. Обратите внимание, что имя файла заключается в квадратные скобки, а восклицательный знак служит разделителем имени листа и адреса диапазона ячеек. Если имя файла содержит пробелы, то его надо заключить в апострофы.

Если файл с исходными данными для списка лежит в другой папке, необходимо указать полный путь к файлу, например, следующим образом: =ДВССЫЛ(«‘C:Поставщики[Товары.xls]Список’!$A$1:$A$10») . В данном случае не забудьте заключить в апострофы полный путь к файлу и имя листа. Минус этого способа только один – выпадающий список будет корректно работать только в том случае, если файл Товары.xls открыт.

Способ 2. Импорт данных

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

Сначала откройте файл-источник, где находятся эталонные значения для выпадающего списка (назовем его, допустим, Справочник.xlsx). Выделите диапазон с данными для списка и отформатируйте его как таблицу с помощью кнопки Форматировать как таблицу на вкладке Главная (Home – Format as Table). Обратите внимание, что у такой таблицы предварительно должна быть сделана «шапка» – строка заголовка. После этого файл Справочник можно сохранить и закрыть.

Теперь откроем книгу, где мы хотим создать выпадающий список (условно назовем ее Бланк.xlsx). Вставим чистый лист (Alt+F11), выберем на вкладке Данные – Существующие подключения – Найти другие (Data – Existing Connections – Browse for more) и укажем наш файл Справочник.xlsx. Появится диалоговое окно, в котором Excel спросит нас о том, какую именно таблицу мы хотим импортировать (если их в файле было несколько).

Теперь откроем книгу, где мы хотим создать выпадающий список

После нажатия на ОК появится еще одно последнее окно, где можно указать удобную ячейку для импорта и, нажав на кнопку Свойства (Properties), задать частоту обновления информации.

После нажатия на ОК появится еще одно последнее окно

Тут можно включить флажок Обновить при открытии файла (Refresh on open), чтобы каждый раз при открытии этой книги иметь последнюю версию списка.

Можно включить флажок Обновить при открытии файла

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

Excel загрузит данные из созданной таблицы

Если выделить импортированный список (диапазон А2:А7 в нашем случае), то в строке формул можно увидеть его имя, которое он автоматически получает при вставке.

В строке формул можно увидеть имя импортированного списка

Это имя также можно увидеть в Диспетчере имен на вкладке Формулы (Formulas – Name Manager).

Осталось создать выпадающий список, который будет ссылаться на эти данные. Для этого:

1. Выделяем ячейки, где хотим создать выпадающие списки.
2. На вкладке Данные жмем на кнопку Проверка данных (Data – Validation).
3. Выбираем в раскрывающемся списке разрешенных типов данных вариант Список (List) и вводим в поле Источник (Source) следующую формулу: =ДВССЫЛ(«Таблица_Справочник») . В англоязычной версии Excel это будет =INDIRECT(«Таблица_Справочник») .

Осталось создать выпадающий список

Логично было бы ввести просто имя нашего диапазона, но, к сожалению, Microsoft Excel почему-то не воспринимает имена таблиц в поле Источник. Поэтому мы используем тактическую хитрость – функцию ДВССЫЛ (INDIRECT), которая превращает свой аргумент (имя нашей таблицы) в рабочую ссылку.

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

После нажатия на ОК список начнет работать и автоматически обновляться

Сбор данных из файлов Excel в заданной папке

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

Для работы этого макроса, помимо него самого, вам понадобится добавить в свой файл:

1. функцию FilenamesCollection для получения списка файлов в папке
2. функцию GetFolder для вывода диалогового окна выбора папки с запоминанием выбранной папки
3. прогресс-бар для отображения процесса обработки файлов (модуль класса и форму)

Если при тестировании макроса у вас возникает ошибка, что не найдена та или иная функция,
— проверьте, все ли необходимые компоненты (которые перечислены выше) вы добавили в свой файл.

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

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

После того, как очередной файл обработан, он перемещается во вторую папку («архив»).

Во вложении — файл со всеми необходимыми макросами для сбора данных из других файлов Excel

Трюк №49. Эффективная вставка данных из другой рабочей книги Excel

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

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

В рабочей книге, где будет находиться ваша сводная таблица, вставьте новый рабочий лист и присвойте ему имя Data. Откройте рабочую книгу и рабочий лист с нужными данными. В любой пустой ячейке на этом рабочем листе введите формулу =IF(A1=»»;»»;A1) , в русской версии Excel =ЕСЛИ(А1=»»;»»;А1) , где А1 — первый заголовок таблицы данных.

Выделите ячейку А1. Затем вырежьте ее, активируйте исходную рабочую книгу и вставьте ячейку А1 в ячейку А1 на листе Data. Вы получите ссылку на другую рабочую книгу. Скопируйте эту ячейку в такое количество столбцов, сколько заголовков содержит источник данных. Затем выберите команду Формулы → Имя → Присвоить (Formulas → Name → Define) и в поле Имя (Names in workbook) введите PivotData. В поле Формула (Refers to) введите формулу =OFFSET($А$1;0;0;COUNTA($А:$А);COUNTA($1:$1)) , в русской версии Excel =СМЕЩ($А$1;0;0;СЧЁТЗ($А:$А);СЧЁТЗ($1:$1)) .

Щелкните на кнопке Добавить (Add), затем на кнопке ОК. После этого нужно добавить код, который будет выполняться каждый раз при открытии рабочей книги. Правой кнопкой мыши щелкните значок Excel (расположенный в левом верхнем углу окна), в контекстном меню выберите команду Исходный текст (View Code) и введите код из листинга 4.2.

// Листинг 4.2 Private Sub Workbook_0pen() With Worksheets(«Data») .Range(«2:1000»).Clear .Range(«1:1»).AutoFi11 .Range(«1:1000») .Range(«2:1000») = .Range(«2:1000»).Value End With End Sub

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

Теперь, если необходимо, можно скрыть этот лист, выбрав команду Формат → Лист → Скрыть (Format → Sheet → Hide) или воспользовавшись способом, описанным в разделе «Трюк №5. Как скрыть лист, чтобы его невозможно было отобразить».

Теперь, чтобы использовать в качестве основы сводной таблицы этот динамический именованный диапазон, выделите любую ячейку сводной таблицы и на панели инструментов Сводные таблицы (PivotTable) выберите команду Сводная таблица → Мастер сводных таблиц (PivotTable → Wizard). Щелкайте кнопку Назад (Back), пока не достигнете шага 1. Установите первый переключатель В списке или базе данных Microsoft Excel (Microsoft Excel List or Database), щелкните на кнопке Далее (Next) и на шаге 2 введите =PivotData (имя динамического именованного диапазона). Щелкните на кнопке Готово (Finish).

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