000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как объединить и обновить таблицу из другой таблицы в Excel?

Exceltip

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

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

связи Excel

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

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

  1. Откройте обе рабочие книги в Excel
  2. В исходной рабочей книге выделите ячейку, которую нужно связать, и скопируйте ее (Ctrl+C)
  3. Перейдите в конечную рабочую книгу, щелкните правой кнопкой мыши ячейку, которую нужно связать. В раскрывающемся меню выберите Вставить специальный
  4. В появившемся диалоговом окне Вставить специальный выберите Вставить ссылку.

Специальная вставка

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

Инструменты копирования и автозаполнения для формул связи работают так же, как и для нормальных формул.

Прежде чем создавать связи между таблицами

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

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

Автоматические расчеты. Эта исходная книга должна работать автоматически в режиме Автоматических вычислений (установлен по умолчанию). В группе Расчеты перейдите на вкладку Формулы, чтобы переключить опции расчета. Выберите Параметры вычислений -> Автоматические.

Читайте так же:
Как обновить сводную таблицу в файле, открытом в Excel?

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

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

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

Изменить связи Excel

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

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

Разорвать связи в книгах Excel

Если вы разрываете ссылку на источник, существующие формулы ссылок заменяются возвращаемыми ими значениями. Например, ссылка =[Source.xlsx]Prices!$B$4 будет заменена на число 16. Разрыв связей нельзя отменить, поэтому я рекомендую сохранить книгу перед выполнением этой операции.

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

Как связать две таблицы в Excel, варианты

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

В то же время заведите на компьютере другую таблицу — «Даты» — и введите в нее значения. Например, «0.1.11.2019» Январь и так далее. Выделите столбцы таблицы, содержащие информацию, и нажмите правой кнопкой мыши на «Копировать» (Скриншот 1).

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

Запустите второй лист таблицы Excel. Затем щелкните компьютерной мышью по любой ячейке таблицы и нажмите на кнопки — «Вставить» и «Вставить ссылку» (скриншот 2).

что такое связанные таблицы

После нажатия на нее данные из другой таблицы появятся в вашей таблице (скриншот 3).

как работать со связанными таблицами

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

Создайте новый лист Excel на своем компьютере. Нажмите «Вставка», а затем «Объект» (скриншот 4).

как вставить таблицу в Excel

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

Читайте так же:
Как настроить область печати для нескольких листов одновременно в Excel?

Синхронизировать таблицы Excel с помощью формулы

Другой подход — создать формулу самостоятельно, не используя кнопку «Вставить ссылку».

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

Введите «=» в ячейку, из которой создается ссылка.

Создание ссылки на ячейку

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

Ссылка на лист в формуле.

И наконец, щелкните ячейку, на которую вы хотите сослаться. В строке формул вы увидите готовую формулу. Нажмите клавишу Enter.

Формула Excel для синхронизации ячеек

Синхронизация ячеек на отдельных рабочих книгах

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

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

Ссылка на другую книгу Excel

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

Полный путь к файлу для закрытой книги

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

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

Включить контент для обновления ссылок

3 Вставка в виде новых строк

Техника, которая работает в более поздних версиях Microsoft Word и является разновидностью копирования. Чтобы воспользоваться им, необходимо выполнить следующие действия:

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

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

Читайте так же:
Как ограничить количество строк и столбцов на листе в Excel?

Вставить новые столбцы в Excel

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

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

Вставить новый столбец (сочетание клавиш)

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

Ниже приведено сочетание клавиш, которое можно использовать для вставки столбца в Excel:

+ ControlShift (удерживайте Control и Shift, а затем нажмите клавишу «плюс»)

Command + I, если вы используете компьютер Mac

Ниже приведены инструкции по использованию этой комбинации клавиш для добавления столбца слева от выбранного столбца:

  • Выберите ячейку в столбце, слева от которого вы хотите добавить новый столбец
  • Используйте сочетание клавиш Control Shift +
  • В открывшемся диалоговом окне «Вставка» выберите параметр «Весь столбец» (или нажмите клавишу C).
  • Щелкните OK (или нажмите клавишу Enter).

Выполненные выше действия немедленно добавят новую колонку слева от выбранной колонки.

Другой способ добавить новый столбец — сначала выбрать весь столбец, а затем выполнить описанные выше действия. Когда вы выбираете весь столбец, использование сочетания клавиш Control Shift + не отображает диалоговое окно вставки.

Это немедленно добавит новую колонку.

Комбинация клавиш для выделения всего столбца (после выделения ячейки в столбце) приведена ниже:

Control + пробел (удерживайте Control и нажмите пробел).

Выбрав колонку, можно использовать клавишу Control-Shift + для добавления новой.

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

Это вставит столбец слева от столбца, в котором вы выбрали ячейку.

Добавить несколько новых столбцов (смежных)

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

Читайте так же:
Как не игнорировать пустые строки при фильтрации в Excel?

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

Вот шаги, которые необходимо выполнить для этого:

  • Выберите два столбца (начиная с того, слева от которого вы хотите вставить столбцы)
  • Щелкните правой кнопкой мыши в любом месте выделения
  • Нажмите «Вставить».

Вышеупомянутые шаги мгновенно вставили бы два столбца слева от столбца B.

Если вы хотите вставить другое количество столбцов (например, 3, 4 или 5 столбцов), выберите это число для начала.

Добавить несколько новых столбцов (несмежных)

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

Что делать, если вы хотите вставить столбцы, но они не смежные?

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

Хотя вы можете делать это по одному, есть способ лучше.

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

  • Выберите столбцы, в которые нужно вставить новый столбец.
  • Щелкните правой кнопкой мыши в любом месте выделения
  • Нажмите Вставить.

Вышеупомянутые шаги мгновенно вставят столбец слева от выбранных столбцов.

Вставлять новые столбцы после каждого другого столбца (с использованием VBA)

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

Более быстрый способ сделать это — использовать простой код VBA, чтобы просто вставить столбец после каждого столбца в вашем наборе данных.

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

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

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

Предупреждение. Приведенный выше код также работает, если данные отформатированы как таблица Excel.

Добавить столбец в таблицу Excel

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

Читайте так же:
Как остановить пустые строки при печати листа?

Например, нельзя одновременно выделить несмежные столбцы и вставить столбцы рядом с ними. Вам придется делать это по очереди.

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

Чтобы вставить столбец слева от столбца B, выделите любую ячейку в столбце, щелкните правой кнопкой мыши, перейдите к опции «Вставить» и выберите «Столбцы таблицы слева».

Это вставит столбец слева от выбранной ячейки.

В приведенном выше примере выбор ячейки в столбце B и еще одной в столбце D указывает на то, что опция «Столбцы таблицы слева» неактивна. В этом случае вам придется вставлять столбцы только по одному.

Удивительно, но, похоже, это позволяет выделить несмежные строки, но не столбцы.

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

Изменение исходных данных сводной таблицы

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

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

Нажмите на сводную таблицу. На ленте находятся БЕСПЛАТНЫЕ ИНСТРУМЕНТЫ.

Перейдите на вкладку АНАЛИЗЫ.

Нажмите кнопку Изменить источник данных в группе Данные.

Нажмите на поле сводки. На ленте появляются ЛОКАЛЬНЫЕ ИНСТРУМЕНТЫ.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector