Копирование таблиц в excel в одну таблицу

Помогла ли Вам статья?

Сборка данных со всех листов книги в одну таблицу

Постановка задачи

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

Давайте будем исходить из следующих соображений:

• Структура и столбцов на всех листах одинаковая.
• Количество строк на всех листах разное.
• Листы могут в будущем добавляться или удаляться.

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

Способ 1. Сборка данных с листов с помощью Power Query

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

Шаг 1. Подключаемся к файлу

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

Если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку, то откройте вкладку Power Query, если у вас Excel 2016 или новее, то вкладку Данные (Data). Нажмите кнопку Получить данные / Создать запрос — Из файла — Книга Excel (Get Data / New Query — From file — From Excel) и укажите наш файл с исходными листами:

В появившемся окне Навигатора (Navigator) выберите слева любой лист и нажмите в правом нижнем углу кнопку Преобразовать данные (Transform Data) или Изменить (Edit):

Должно появиться окно редактора запросов Power Query, где отобразятся данные с выбранного листа. Поскольку нам нужен, на самом деле, не один лист, а все, то удалим в правой панели все шаги, кроме первого шага Источник (Source) используя крестик слева от названия шага:

То, что останется после удаления шагов — это список всех объектов, которые Power Query «видит» во внешних файлах, а это:

• листы (Sheet)
• «умные таблицы» (Table)
• именованные диапазоны (Defined Name)
• области печати (Print Area), которые, по сути, являются одним из видов именованного диапазона

Шаг 2. Отбираем нужные листы

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

Во-первых, легко можно отфильтровать нужные объекты по типу по столбцу Kind. Например, если вам нужны только листы:

Во-вторых, если нам нужны только видимые листы, то дополнительно можно отфильтровать ещё по столбцу Hidden.

В-третьих, если вы точно знаете размер таблиц, которые вам нужны, то можно легко добавить к нашему списку вычисляемый столбец с формулой, выводящей количество столбцов или строк и использовать потом эти числа для отбора. Для этого выберем на вкладке Добавление столбца — Настраиваемый столбец (Add Column — Custom Column) и введём в открывшееся окно следующую формулу (с учётом регистра):

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

В-четвёртых, можно извлечь с каждого листа содержимое любой ячейки (например, А1) и использовать его для отбора. Например, если там нет слова «Товар«, то это не наш лист. Для извлечения нужно будет также добавить вычисляемый столбец с такой конструкцией:

=[Data][Column1]{0}

Здесь:

• [Data] — имя столбца, где в каждой ячейке лежат таблицы с содержимым каждого листа (убийственная формулировка для рядового пользователя Excel, да, я знаю)
• [Column1] — имя столбца на листе, из которого мы хотим извлечь данные
• {0} — номер строки (считая с нуля), откуда мы хотим взять данные

Шаг 3. Разворачиваем таблицы

Теперь развернём содержимое таблиц в одно целое, используя кнопку с двойными стрелками в заголовке столбца Data, отключив флажок Использовать исходное имя столбца как префикс (Use original column name as prefix):

После нажатия на ОК Power Query соберёт для нас все данные в одну мегатаблицу со всех отобранных листов нашего файла:

Останется лишь «навести блеск», а именно:

1. Поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home).
2. Переименовать первый столбец в Город двойным щелчком на заголовку.
3. Удалить повторяющиеся шапки таблиц, попавшие в одну кучу вместе с данными, используя фильтр по столбцу Товар.

Всё. Осталось только дать нашему запросу подходящее имя (например, Сборка) в панели справа и выгрузить затем собранные данные обратно в Excel кнопкой Закрыть и загрузить на вкладке Главная (Home — Close & Load):

В будущем, при любых изменениях в исходном файле достаточно будет лишь обновить наш запрос, щелкнув по собранной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или такой же кнопкой на вкладке Данные (Data) или сочетанием клавиш Ctrl+Alt+F5.

Плюсы такого подхода:

• Не нужно уметь программировать, всё делается быстро и почти без использования клавиатуры.
• Последовательность столбцов на разных листах может быть различной — это не играет роли, столбцы правильно встанут друг под друга в итоговой сборке.
• Можно быстро обновлять запрос при изменении исходных данных.

Минусы этого способа:

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

Способ 2. Сборка данных с листов макросом на VBA

Похожего результата можно добиться и с помощью более «классического» подохода — макросом на VBA. Для этого на вкладке Разработчик (Developer) нажмите кнпоку Visual Basic или воспользуйтесь сочетанием клавиш Alt+F11. В открывшемся окне добавьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:

Sub CollectDataFromAllSheets()
    Dim ws As Worksheet
    
    Set wbCurrent = ActiveWorkbook
    Workbooks.Add
    Set wbReport = ActiveWorkbook
    
    'копируем на итоговый лист шапку таблицы из первого листа
    wbCurrent.Worksheets(1).Range("A1:D1").Copy Destination:=wbReport.Worksheets(1).Range("A1")
    
    'проходим в цикле по всем листам исходного файла
    For Each ws In wbCurrent.Worksheets
    
        'определяем номер последней строки на текущем листе и на листе сборки
        n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
        
        'задаем исходный диапазон, который надо скопировать с каждого листа - любой вариант на выбор:
        Set rngData = ws.Range("A1:D5")            'фиксированный диапазон или
        Set rngData = ws.UsedRange                 'всё, что есть на листе или
        Set rngData = ws.Range("F5").CurrentRegion    'область, начиная от ячейки F5 или
        Set rngData = ws.Range("A2", ws.Range("A2").SpecialCells(xlCellTypeLastCell))    'от А2 и до конца листа
        
        'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки
        rngData.Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1)
        
    Next ws
End Sub

Запустить созданный макрос можно на вкладке Разработчик кнопкой Макросы (Developer — Macros) или с помощью сочетания клавиш Alt+F8. Макрос автоматически создаст новую книгу и скопирует туда нужные вам данные.

Плюсы такого подхода:

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

Минусы этого способа:

• Последовательность столбцов на всех листах должна быть одинаковой, т.к. происходит, по сути, тупое копирование таблиц друг-под-друга.
• Защита от макросов должна быть отключена.
• Быстрого обновления, как это было с Power Query, здесь, к сожалению, не будет. При изменении исходных данных придётся запустить макрос повторно.

Способ 3. Готовый макрос из надстройки PLEX

Если лень возиться с макросами или Power Query, то можно пойти по пути наименьшего сопротивления — использовать готовый макрос (кнопка Собрать) из моей надстройки PLEX для Excel. Это, может, и не спортивно, но зато эффективно:

В общем, выбирайте любой удобный вам вариант и действуйте. Выбор — это всегда хорошо.

Ссылки по теме

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

Копирование таблицы в excel

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

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

Копирование таблицы с сохранением структуры

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

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

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

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

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

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

Нам потребуется «Ширины столбцов», поэтому выбираем соответствующий пункт и принимаем изменения.

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

Способ 2: Выделение столбцов перед копированием

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

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

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

Способ 3: Вставка формул с сохранением формата

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

Чтобы выполнить операцию, сделайте следующее:

1. Выделите и скопируйте исходник.
2. В контекстном меню вставки просто выберите «Значения» и подтвердите действие.

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

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

Копирование таблицы в Microsoft Excel

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

Копирование в Экселе

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

Способ 1: копирование по умолчанию

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

Выделяем область, которую хотим скопировать. Кликаем по выделенной области правой кнопкой мыши. Появляется контекстное меню. Выбираем в нём пункт «Копировать».

Есть и альтернативные варианты выполнение данного шага. Первый из них состоит в нажатии на клавиатуре сочетания клавиш Ctrl+C после выделения области. Второй вариант подразумевает нажатие кнопки «Копировать», которая располагается на ленте во вкладке «Главная» в группе инструментов «Буфер обмена».

Тут также существуют альтернативные варианты действий. Можно выделив ячейку нажать на клавиатуре сочетание клавиш Ctrl+V. Кроме того, можно нажать на кнопку «Вставить», которая размещена на самом левом краю ленты рядом с кнопкой «Копировать».

После этого, будет выполнена вставка данных с сохранением форматирования и формул.

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

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

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

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

Если вы хотите скопировать значения, но при этом сохранить исходное форматирование, то нужно во время вставки перейти в пункт меню «Специальная вставка». Там в блоке «Вставить значения» нужно выбрать пункт «Значения и исходное форматирование».

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

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

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

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

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

Можно поступить и другим способом. Из контекстного меню дважды переходим в пункт с одинаковым наименованием «Специальная вставка…».

Открывается окно. В блоке инструментов «Вставить» переставляем переключатель в позицию «Ширины столбцов». Жмем на кнопку «OK».

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

Способ 4: вставка в виде изображения

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

1. Выполняем копирование нужного диапазона.
2. Выбираем место для вставки и вызываем контекстное меню. Переходим в пункт «Специальная вставка». В блоке «Другие параметры вставки» выбираем пункт «Рисунок».

После этого, данные будут вставлены на лист в виде изображения. Естественно, редактировать такую таблицу уже будет нельзя.

Способ 5: копирование листа

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Как скопировать таблицу в Экселе и вставить

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

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

Способ 1. Стандартное копирование

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

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

Есть и другие способы воспользоваться инструментом копирования. Самым простым вариантом является использование горячей клавиши Ctrl+C. Также можно воспользоваться соответствующей специальной иконкой в панели главного меню.

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

Также можно ставить таблицу с помощью горячей клавиши Ctrl+V, предварительно выбрав ячейку, либо воспользоваться специальной кнопкой в контекстном меню.

После этой процедуры вы получите готовую таблицу с сохраненным форматом и формулами.

Способ 2. Копирование значений

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

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

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

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

Чтобы выполнить перенос только с сохранением форматирования чисел, нужно в соответствующем блоке выбрать пункт «Значения и форматы чисел».

Способ 3. Создание копии с сохранением ширины столбцов

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

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

Также можно сделать это другим способом. Дважды перейдите в меню с названием «Специальная вставка».

В появившемся окне выберите подходящие параметры вставки и нажмите «ОК».

Вы получите готовую таблицу с исходной шириной столбцов.

Способ 4: вставка в виде изображения

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

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

После этого, данные будут вставлены на лист в виде изображения. Естественно, редактировать такую таблицу уже будет нельзя.

Способ 5: копирование листа

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

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

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

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

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

Как скопировать таблицу в Excel

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

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

В вопросе, как скопировать таблицу Эксель в Эксель, необходимо воспользоваться одним из способов:

• копировать объект по умолчанию;
• копировать значения;
• копировать таблицу с сохранением ширины столбца;
• копировать лист.

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

Копирование по умолчанию

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

• Выделить диапазон, необходимый для копирования.
• Скопировать область понравившимся способом: кликнуть правой кнопкой мыши (ПКМ) по выделенной области и выбрать опцию «Копировать» или нажать CTRL+C, или активировать пиктограмму на панели инструментов в блоке «Буфер обмена» (вкладка «Главная»).

• Открыть другой лист или ту область, где будет размещаться дубликат.
• Активировать клетку, которая станет верхней левой ячейкой новой таблицы.
• Вставить объект одним из способов: через контекстное меню (ПКМ – Вставить) или CTRL+V, или нажатием на пиктограмму «Вставить» на панели инструментов на вкладке «Главная».

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

Копирование значений

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

1. Выделить диапазон, необходимый для копирования.
2. Скопировать область удобным способом.
3. Открыть другой лист или ту область, где будет размещаться дубликат.
4. Активировать клетку, которая станет верхней левой ячейкой новой таблицы.
5. Вставить объект удобным способом.
6. Раскрыть пиктограмму «Вставить».
7. Установить переключатель на опцию «Только значения».

Примечание:

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

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

Создание копии с сохранением ширины столбцов

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

1. Выполнить пункты 1-6 из алгоритма «Копирование значений».
2. При раскрытии пиктограммы вставки выбрать опцию «Сохранить ширину столбцов».

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

Копирование листа

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

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

• Скопировать диапазон нажатием CTRL+С.
• Перейти на другой лист или открыть новую книгу.
• Выделить таблицу любым способом из пункта 1.
• Нажать CTRL+V.

После выполнения алгоритма на новом листе появляется идентичная таблица, сохранившая формулы, форматирование и ширину столбцов.

Вставка в виде изображения

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

1. Выполнить пункты 1-4 из алгоритма «Копирование значений».
2. Перейти на вкладку «Главная».
3. В блоке «Буфер обмена» раскрыть пиктограмму «Вставить».
4. Выбрать пункт «Как рисунок».
5. Активировать опцию «Вставить как рисунок».

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

1. Перейти в меню «Вид».
2. Снять переключатель на опции «Сетка».

Сетка исчезла. Изображение корректно отображается. Если кликнуть ПКМ по вставленной таблице, то пользователь заметит, что таблица отображается в виде рисунка. Соответственно, отредактировать объект в качестве таблицы не получится, только как рисунок с помощью контекстного меню или вкладки «Формат».

Инструментарий MS Excel расширяет функциональность пользователя по автоматизации действий и копированию и вставке объектов.

Как копировать таблицу в Excel сохраняя формат ячеек

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

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

Как скопировать таблицу с шириной столбцов и высотой строк

Допустим, у нас есть такая таблица, формат которой необходимо сохранить при копировании:

При копировании на другой лист привычным методом Ctrl+C – Ctrl+V. Получаем нежелательный результат:

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

Способ1:Используем специальную вставку

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

Она получила исходные параметры и выглядит идеально точно.

Способ 2: Выделяем столбцы перед копированием

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

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

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

1. Выделяем целые строки листа, которые охватывают требуемый диапазон данных:
2. Ниже вставляем ее копию:

Полезный совет! Самый быстрый способ скопировать сложную и большую таблицу, сохранив ее ширину столбцов и высоту строк – это копировать ее целым листом. О том, как это сделать читайте: копирование и перемещение листов.

Вставка значений формул сохраняя формат таблицы

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

Чтобы решить такую задачу следует выполнить 2 операции, используя специальную вставку в Excel.

Операция I:

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

Операция II:

Так как скопированный диапазон у нас еще находится в буфере обмена после копирования, то мы сразу еще раз вызываем специальную вставку где выбираем опцию «форматы». Жмем ОК.

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

Полезный совет! Чтобы не выполнять вторую операцию можно воспользоваться инструментом «формат по образцу».

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

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

В
вопросе, как скопировать таблицу
Эксель в Эксель, необходимо
воспользоваться одним из способов:

• копировать объект по умолчанию;
• копировать значения;
• копировать таблицу с сохранением ширины
  столбца;
• копировать лист.

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

Копирование
по умолчанию

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

• Выделить диапазон, необходимый для
  копирования.
• Скопировать область понравившимся
  способом: кликнуть правой кнопкой мыши (ПКМ) по
  выделенной области и выбрать опцию
  «Копировать» или нажать CTRL+C, или активировать пиктограмму на панели
  инструментов в блоке «Буфер обмена»
  (вкладка «Главная»).

• Открыть другой лист или ту область, где
  будет размещаться дубликат.
• Активировать клетку, которая станет
  верхней левой ячейкой новой таблицы.
• Вставить объект одним из способов: через контекстное меню (ПКМ – Вставить) или CTRL+V, или нажатием на пиктограмму «Вставить»
  на панели инструментов на вкладке
  «Главная».

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

Копирование
значений

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

1. Выделить диапазон, необходимый для
   копирования.
2. Скопировать область удобным способом.
3. Открыть другой лист или ту область, где
   будет размещаться дубликат.
4. Активировать клетку, которая станет
   верхней левой ячейкой новой таблицы.
5. Вставить объект удобным способом.
6. Раскрыть пиктограмму «Вставить».
7. Установить переключатель на опцию
   «Только значения».

Примечание:

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

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

Создание
копии с сохранением ширины столбцов

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

1. Выполнить пункты 1-6 из алгоритма
   «Копирование значений».
2. При раскрытии пиктограммы вставки
   выбрать опцию «Сохранить ширину
   столбцов».

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

Копирование
листа

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

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

• Скопировать диапазон нажатием CTRL+С.
• Перейти на другой лист или открыть
  новую книгу.
• Выделить таблицу любым способом из
  пункта 1.
• Нажать CTRL+V.

После
выполнения алгоритма на новом листе
появляется идентичная таблица, сохранившая
формулы, форматирование и ширину
столбцов.

Вставка
в виде изображения

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

1. Выполнить пункты 1-4 из алгоритма
   «Копирование значений».
2. Перейти на вкладку «Главная».
3. В блоке «Буфер обмена» раскрыть
   пиктограмму «Вставить».
4. Выбрать пункт «Как рисунок».
5. Активировать опцию «Вставить как
   рисунок».

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

1. Перейти в меню «Вид».
2. Снять переключатель на опции «Сетка».

Сетка
исчезла. Изображение корректно
отображается. Если кликнуть ПКМ по
вставленной таблице, то пользователь
заметит, что таблица отображается в
виде рисунка. Соответственно,
отредактировать объект в качестве
таблицы не получится, только как рисунок
с помощью контекстного меню или вкладки
«Формат».

Инструментарий
MS Excel расширяет функциональность
пользователя по автоматизации действий
и копированию и вставке объектов.

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

Простое копирование без изменений

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

Обратите внимание! Простое копирование ничего не меняет в исходной информации.

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

• Выделение ячеек. Как правило, используется либо указание диапазона ячеек левой кнопкой мыши, либо комбинация клавиатуры «Shift+стрелка». В результате часть ячеек листа обводится черной рамкой, и они дополнительно подсвечиваются темным оттенком.
• Копирование в буфер обмена. Буфер обмена – это специальная область в памяти компьютера, предназначенная для перенесения данных внутри приложения или между приложениями. Копирование в нее воспроизводится либо нажатием клавиш «Ctrl+C», либо «Ctrl+Insert» (эти комбинации эквивалентны). Также его возможно осуществить через соответствующий пункт контекстного меню или с помощью ленты программы.
• Указание места для вставки. Переходим туда, куда мы хотим скопировать данные, и указываем курсором ячейку, которая будет верхней левой ячейкой вставляемых данных. Следует быть осторожным, если место для вставки уже содержит какие-то данные. Они могут быть стерты.
• Вставка содержимого буфера обмена в указанную область. Это делается клавишами «Ctrl+V» либо «Shift+Insert» либо соответствующим пунктом контекстного меню или ленты программы.

Если нужны только значения

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

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

Важно! Формулы и форматы при таком способе не сохраняются.

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

Когда нужны и значения, и форматы

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

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

Копирование в виде рисунка

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

Шаги по копированию в буфер обмена не отличаются от предыдущих вариантов, а вот для вставки используется пункт «Рисунок» в меню «Специальная вставка». Этот метод используется редко, поскольку данные ячеек, скопированных таким способом, невозможно изменить простым вводом значений.

Полное копирование всего листа

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

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

Заключение

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

Объединение двух или нескольких таблиц

​Смотрите также​​ париться, нужно вставлять​ каждого файла я​: Откуда в итоговом​номер дела, фио,​ случай, когда требуется​ фрагменту правой кнопкой​ математические операции (сложение,​. Именно данный вариант​ строчки. При этом​ о ставках всех​ Располагать всю информацию​ является частью сводной​Шаг 3​ отделов, а также​ первой ячейке столбца​20054​Примечание:​ допустим данные других​ не знаю.​ файле взялась строка​123, иванов​

​ изменить массив, вставленный​ мыши. В открывшемся​ деление, вычитание, умножение​ подойдет для нашего​ важно знать, что​ остальных работников предприятия.​ в одной таблице,​ таблицы.​укажите, следует ли​ элемент, содержащий сводные​ «Регион».​04.02.2014​Мы стараемся как​ регионов как я​Дано:​ с номером дела​И соответственно эти​ из другой книги,​ списке выбираем пункт​ и т.д.).​ случая.​

​ номер строки следует​ Конечно, это можно​ к тому же,​На странице​ добавлять сводную таблицу​ данные. В примере​На шаге 6 вместо 2​S1018MM​ можно оперативнее обеспечивать​ сделал примером ниже,​

Объединение двух таблиц с помощью функции ВПР

​12 листов с​ 2007?​ столбцы заполнены. Данная​ так и просто​«Копировать»​Посмотрим, как это осуществляется​Итак, приступим к заполнению​ указывать не относительно​ сделать тем же​ если она не​Шаг 1​ на новый или​ ниже показана сводная​ введите число 3,​20055​ вас актуальными справочными​ так?и обязательно ли​ данными (текст и​Для всех номеров​ таблица рассылается людям,​ нежелание пользователя, чтобы​. Альтернативной комбинацией является​ на практике. Сделаем​ полей окна аргументов.​ всего документа, а​

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

​в нескольких диапазонах консолидации​ лист, и нажмите​ выбраны одно поле​ примет такой вид:​C6077B​ языке. Эта страница​ «двоичной книги»?или просто​ листу на каждый​ что были в​ один столбик -​

​ данные заработной платы​, кликаем по первой​— аргумент, носящий​ оба списка сотрудников​ ним поиск. Указанную​Далее​.​Если велика вероятность того,​ различие: первая получает​E3331​

​ страницы, содержащее элемент​ раз при консолидации​ таблицы «Оранжевая», а​06.02.2014​ нас важно, чтобы​: Совершенно верно.​Задача:​Когда применяется статус​ из них приходит​ одну операцию. При​Переместившись в нужную нам​ Для этого ставки​на​ задачи мы его​ существенно упростить и​ таблицы, информация между​Шаг 2а​

​ для каждого исходного​

1. ​ данных исходный диапазон​ вторая — из столбца​D534X​ эта статья была​

2. ​Quote​Собрать всё в​ «да»?​ таблица, только с​

   ​ этом данные в​ область книги, выделяем​ сотрудников будут подтягиваться​Листе 1​

3. ​ использовать не будем,​ ускорить её решение.​ которыми распределена, но​выберите параметр​

   ​ диапазона, а также​

4. ​ данных изменится (например,​ 3.​Ниже приведены данные для​

   ​ вам полезна. Просим​(vet80)200?’200px’:»+(this.scrollHeight+5)+’px’);»>обязательно ли сохранять​

   ​ одну таблицу, чтобы​DYm00n​ еще одним столбцом​ ячейках останутся, но​ ячейки, в которые​

   ​ из​.​ а поэтому описывать​ Это можно сделать,​ в то же​Создать поля страницы​

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

6. ​ данные все шли​: Я же уже​ — статус.​

   

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

   ​ его в том​ секунд и сообщить,​ книги»?или просто в​ по порядку, сначала​

7. ​ 3 раза писал,​Нужно, все эти​ представлять собой статические​ значения. В нашем​, суммироваться (при помощи​ отобразились, устанавливаем курсор​

8. ​ не нужно.​ на диапазон ниже.​ Связанные табличные диапазоны​ кнопку​

   

   ​ следующее:​ для каждого из​ «Синяя» заполнены значениями.​ же листе. После​ помогла ли она​ обычном можно, если​ вся первая таблица,​

   ​ что выделено красным,​ таблицы слить в​ не обновляемые значения,​ случае это столбец​ функции​ в поле​Ставим курсор в поле​ Благодаря тому, что​ могут находиться не​Далее​

9. ​Добавьте мастер сводных таблиц​ исходных диапазонов на​ В них содержатся​ его вставки в​ вам, с помощью​

10. ​ есть разница, то​​ потом вся вторая​​ то значит пользователь​ одну и дополненные​​ которые никак не​​«Ставка»​

    

11. ​СУММ​«Просматриваемый массив»​​«Массив»​​ ссылки в Excel​

    

12. ​ только в пределах​.​ и диаграмм на​ разных листах. Эти​ формулы ВПР, но​

    ​ лист, нажмите клавиши​ кнопок внизу страницы.​ в чём?​

Дополнительные сведения о таблицах и функции ВПР

• ​ таблица и так​ сам добавит. Разные​ строки и столбцы​

• ​ зависят от других​. Щелкаем по выделенному​

• ​) и умножаться на​и переходим по​

support.office.com

Консолидация нескольких листов в одной сводной таблице

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

Консолидация нескольких диапазонов

​Шаг 2б​ Для этого:​ при консолидации диапазонов​ вы захотите заменить​ можно преобразовать в​ приводим ссылку на​ формате. Двоичный просто​ ничего суммировать, просто​ для примера, их​ в первоначальной ячейки.​В книге, в которой​ мыши. В контекстном​ формулы.​«Лист 2»​Лист 2​ копировании происходит сдвиг​

​ и располагаться в​сделайте следующее:​Щелкните стрелку рядом с​ на главном листе.​​ формулы ВПР в​​ таблицу, а затем​ оригинал (на английском​ меньше места занимает.​ собрать всё в​ пользователь сам проставляет​Для наглядности вложил​ подтягиваются значения из​ меню в блоке​Выделяем ячейку, где будет​

Настройка исходных данных

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

Поля страницы при консолидации данных

​ инструментов​ выводиться итог расчета​ окна Excel над​ кнопку мыши, выделяем​ и нужно. Саму​ Последние два варианта​ выделите диапазон ячеек,​ выберите​ исходного диапазона перед​ значениями.​Номер заказа​Можно объединить строк (объединение)​ с восстановлением вроде​ в данном случае​: Можно попробовать начать​ листе таблица которая​ во вкладку​«Параметры вставки»​ заработной платы на​ строкой состояния. Зажимаем​ все содержимое столбца​

Использование именованных диапазонов

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

Другие способы консолидации данных

​ в столбце «Код​Регион​ в другую путем​ чего-то, сейчас не​Mike noldman​Option Base 1​ втором листе, которая​. Щелкаем по значку​«Вставить связь»​. Производим клик по​​ и выделяем курсором​​.​​ маркера заполнения.​​ как целью указанной​​ таблиц и диаграмм​​В списке​

Консолидация нескольких диапазонов

​ на отдельном листе​ продажи» и нажмите​20050​ вставки данных в​ помню. Но на​: http://www.excel-office.ru/diapasontabliziexcel/svodnayatbvexcel​ Dim RowI As​ должна получится (упорядочивание​«Изменить связи»​

Консолидация данных без использования полей страницы

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

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

   1. ​ клавиши CTRL+C, чтобы​447​ первой пустой ячейки​​ формулы это не​​http://excelexpert.ru/kak-v-excel-ispolzovat-konsolidaciyu-dlya-obedineniya-dannyx-iz-raznyx-knig​

   2. ​ Integer, LastRowI As​​ идет по ФИО),​​, который расположен на​​Существует также альтернативный вариант.​​«Вставить функцию»​

   3. ​«Имя»​​ отобразились в окошке​ нижнюю правую область​​ раз уйти от​​Добавить​​выберите пункт​​ таким образом, чтобы​​ скопировать их.​

2. ​Запад​ под целевой таблицы.​ влияет.​Полосатый жираф алик​

3. ​ Variant Dim PathS​ красным я выделил​

4. ​ ленте в блоке​​ Он, кстати, является​​.​​.​​ оператора, ставим курсор​ элемента с формулой.​​ скопления данных, а​​.​

5. ​Все команды​​ включить в него​​На вкладке​​20051​​ Таблица приводит к​vet80​​: А просто скопировать,​​ As String, DirS​

6. ​ новые строки, которые​​ инструментов​​ единственным для более​

   1. ​Следует запуск окна​После того, как их​ в поле​ После этого курсор​ нагромождение их на​Совет:​​.​​ новые данные.​

      ​Главная​​398​ увеличению размера, чтобы​: _Boroda_, Огромнейшее спасибо!!​ как значения, руки​ As String, WrkbkNmS​

   2. ​ сам пользователь добавляет​​«Подключения»​ старых версий Excel.​​Мастера функций​​ координаты отобразились в​​«Номер строки»​ должен преобразоваться в​​ одной странице принципиально​​ Если диапазон ячеек находится​

7. ​Выберите в списке пункт​​В Excel также доступны​​щелкните стрелку под​Юг​ включить новые строки.​blue123​ отвалятся?​ As String Dim​​ в свою таблицу.​​. Нужно отметить, что​

Консолидация данных с использованием одного поля страницы

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

1. ​Мастер сводных таблиц и​ другие способы консолидации​ кнопкой​20052​

   1. ​ Если строк в​: Товарищи, помогите, пожалуйста​vet80​​ InputDataArrV() As Variant,​​ В номерах дел​

   2. ​ если текущая книга​​ наводим курсор на​​«Математические»​​«Просматриваемый массив»​​ будем выводить с​

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

2. ​ советом. Данные находятся​: Данные двух одинаковых​ OutputDataArrV() As Variant​ зависимости не какой​

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

4. ​и нажмите кнопку​​ работать с данными​​.​​Север​​ можно объединить столбцы​ в двух таблицах,​​ таблиц(они находятся на​​ Sub Consolidation() DirS​

5. ​ нет, данные номера​​ с другими файлами,​​«Специальная вставка»​

   1. ​ наименование​«Тип сопоставления»​ПОИСКПОЗ​ кнопки мыши и​ работать с таким​ данных.​​Добавить​​ в разных форматах​

      ​В коллекции параметров вставки​​20053​ из одной таблицы​ причём и в​ Лист1,Лист2 соответственно в​ = Worksheets(«data»).Cells(1, 2)​

6. ​ написал для примера.​​ то эта кнопка​​. В открывшемся дополнительном​

7. ​«СУММ»​​и с клавиатуры​​. Поэтому кликаем по​ тянем курсор до​ видом управления данными.​В разделе​, а затем — кнопку​ и макетах. Например,​​ нажмите кнопку​​447​

Консолидация данных с использованием нескольких полей страницы

​ с другой —​ первой и во​ одной книге), необходимо​ ‘ в ячейке​analyst​ является неактивной.​ меню выбираем позицию​. Далее жмем по​ устанавливаем там число​ треугольнику, который расположен​ самого низа столбца.​Прежде всего, давайте остановимся​Во-первых, укажите количество полей​ОК​ вы можете создавать​Значения​Запад​ путем вставки их​ второй первый столбец​

1. ​ свести в одну(общую),​ В1 указываем путь​: А что у​Запускается окно изменения связей.​

   1. ​ с одноименным названием.​ кнопке​«0»​​ слева от строки​​Все данные из аналогичного​

   2. ​ на вопросе, какими​​ страницы сводной таблицы​​.​​ формулы с объемными​​.​

   3. ​20054​​ в первой пустой​ — «номер», шестизначное​​ в той же​​ к папке с​​ вас конкретно не​​ Выбираем из списка​​После этого открывается окно​

2. ​«OK»​. После этого опять​ функций. Открывается перечень​ столбца на​

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

4. ​ книге, где находятся​​ файлами PathS =​​ получается?​​ связанных книг (если​​ специальной вставки. Жмем​.​​ возвращаемся к полю​​ недавно использованных операторов.​

5. ​Листе 2​​ создать связь между​​ которые вы хотите​

   1. ​ ячейку, которая не​ команду​ в столбце «Регион»,​Восток​ таблицы. В этом​ по порядку, но​​ предыдущие две(или более​​ DirS & «»​

      ​DYm00n​​ их несколько) тот​ на кнопку​Производится перемещение в окно​«Просматриваемый массив»​ Если вы среди​

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

   3. ​20055​​ случае также таблицы​ не полностью идентичны​ двух)таблицы,цифры в двух​ WrkbkNmS = Dir(PathS,​: Да я просто​ файл, с которым​«Вставить связь»​​ аргументов функции​. Дело в том,​ них найдете наименование​ таблицу на​Самый простой способ связывания​

      ​В группе​

      • ​ таблицы.​​(доступную на вкладке​ повторите шаги 10​​398​​ приводит к увеличению​​ в первой и​ и более таблицах,​ vbNormal) Do While​ вообще не знаю,​​ хотим разорвать связь.​​в нижнем левом​СУММ​ что мы будем​«ПОИСКПОЗ»​Листе 1​ данных – это​Затем выберите диапазон в​На странице​Данные​ и 11.​Юг​

      • ​ учетом новых столбцов.​​ второй таблице (то​ с каждым месяцем​​ WrkbkNmS <> «»​​ как сводятся таблицы​​ Щелкаем по кнопке​ углу ячейки.​​, которая предназначена для​​ выполнять копирование формулы,​, то можете кликать​. При изменении данных​​ использование формул, в​​ списке и укажите​Шаг 1​в группе​Теперь формулы ВПР в​20056​Объединение строк – довольно​ там, то тут​ изменяются,очень нужно помогите,​ Workbooks.Open Filename:=PathS &​Serge 007​«Разорвать связь»​Какой бы вариант вы​

   4. ​ расчета суммы выбранных​​ как мы это​​ по нему. В​

6. ​ на​​ которых имеются ссылки​​ метку элемента в​мастера выберите параметр​Работа с данными​ двух столбцах заменены​644​ простой процесс, но​​ попадаются номера, которых​​ если кто-то сможет,то​

​ WrkbkNmS ActiveWorkbook.ActiveSheet.Cells(1, 1).Activate​

support.office.com

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

​: Нарисуйте пример, как​.​ не выбрали, значения​ чисел. Она имеет​ делали в предыдущем​ обратном случае кликайте​Листе 2​ на другие табличные​ каждом из доступных​в нескольких диапазонах консолидации​).​ значениями.​Восток​

​ Объединение столбцов может​ не было в​

​ объясните как,какими формулами,файл​ LastRowI = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row​ по вашему это​Открывается информационное окошко, в​ из одного табличного​ нижеуказанный синтаксис:​ способе. Будет происходить​ по самому последнему​они автоматически будут​ диапазоны. Он называется​ окон полей. Повторите​, а затем нажмите​Для консолидации нескольких диапазонов​Как добавить или удалить​20057​ быть сложнее, если​ другой таблице, либо​ прилагаю​ InputDataArrV = ActiveCell.CurrentRegion.Offset(1,​ всё должно выглядеть​ котором находится предупреждение​ массива будут вставлены​=СУММ(число1;число2;…)​ смещение адресов, но​ пункту перечня –​ изменяться и на​ прямым связыванием. Этот​ операцию для каждого​ кнопку​ вы можете воспользоваться​ строку или столбец​1270​ не соответствуют строки​ отсутствуют). Остальные столбцы​_Boroda_​ 0).Resize(LastRowI — 1,​ и из чего​ о последствиях дальнейших​ в другой. При​

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

​Поля в окне соответствуют​ вот координаты просматриваемого​«Другие функции…»​ первом.​ способ интуитивно понятен,​

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

​ диапазона​Далее​ мастером сводных таблиц​ в таблице​Восток​ одной таблицы со​ показывают характеристики этих​: Можно так (см.​ 3) If OutputDataArrV​ складываться, а уж​ действий. Если вы​ изменении данных в​ аргументам указанной функции.​ массива нам нужно​.​

​Но что делать, если​ так как при​для каждого из​.​ и диаграмм. В​Использование структурированных ссылок в​20058​ строками в другой​ номеров, и в​ вложение)​ Is Nothing Then​ мы тогда подскажем​

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

​На странице​ нем можно указать,​ формулах таблиц Excel​885​ таблице. С помощью​

1. ​ двух таблицах они​Новые лист нужно​​ OutputDataArrV = InputDataArrV​​ как это сделать,​ что собираетесь делать,​​ автоматически будут изменяться​​ может достигать 255​ должен смещаться. Выделяем​​Мастера функций​​ табличных массивах расположен​ практически точно так​ диапазон ячеек, а​Шаг 2а​

   

2. ​ сколько полей страницы​Использование функции ВПР (учебный​Восток​ функции ВПР, можно​​ разные. Нужно добавить​​ вставлять между Лист1​ Workbooks(WrkbkNmS).Close WrkbkNmS =​​ а то сейчас​​ то жмите на​ и во вставленном​ штук, но для​ координаты курсором и​. Переходим в нем​​ не в одинаковом​​ же, как создание​

   

3. ​ затем укажите его​выберите параметр​ будет использоваться: ни​ курс)​Нужно ли обеспечить правильность​ избежать проблем выравнивания.​ данные из одной​ и Лист2​ Dir Loop End​ не понятно ничего…​ кнопку​ диапазоне.​ нашей цели достаточно​ жмем на функциональную​ в ту же​​ порядке? В этом​​ ссылок на данные​ имя.​Создать одно поле страницы​ одного, одно или​Консолидация данных представляет собой​

   ​ код продажи "и"​

   

4. ​В приведенном ниже примере,​ таблицы в другую​vet80​ Sub​DYm00n​«Разорвать связи»​Урок: Специальная вставка в​ будет всего одного.​ клавишу​ самую группу​ случае, как говорилось​ в одном табличном​Пример​, а затем нажмите​ несколько.​ удобный способ объединения​ Регион значения для​ вы увидите двух​ (практически, соединить их,​: _Boroda_,то есть, если​DYm00n​: В первом посте​.​ Экселе​ Ставим курсор в​F4​«Ссылки и массивы»​

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

   

5. ​ я вложил пример,​После этого все ссылки​​Кроме того, можно организовать​​ поле​. Как видим, перед​​. На этот раз​​ вариантов является установка​Посмотрим, как на примере​​Во-первых, укажите количество полей​​Далее​ диапазонов и создать​ источников в одном​

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

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

1. ​ Чтобы сделать это,​​ и «Оранжевая». В​​ таблицы, там где​​ что мне в​​ обойтись нельзя?​​ а на второй,​​ будут заменены на​

   

2. ​ книгах. При этом​​«Лист 2»​​ то, что ссылка​​«ПОИСКПОЗ»​​ которые следует связать,​ Имеем две таблицы​​1​​Шаг 2б​

   

3. ​ следующее:​ региональных филиалов вашей​ давайте вставьте заголовки​ таблице синий каждая​ в другой таблице​ формуле прописать необходимо?чтоб​Serge 007​ что должно получиться.​ статические значения.​ используется инструмент специальной​над строкой состояния.​​ из относительной превратилась​​. Выполняем щелчок по​

   

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

   ​ Красным выделено, что​

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

   ​ На одной таблице​​ диапазонов, а затем​Перейдите в книгу и​ и диаграмм на​ таблица расходов, с​ «и» Регион в​ элемент строки для​ просто оставить пустые​ «итоге»?и если там​ пример сделать и​

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

   ​ выделите диапазон ячеек,​​ панель быстрого доступа.​​ помощью консолидации данных​ ячеек справа от​​ заказа. Итак, код​​ поля по характеристикам,​ 5 или 45​ нормально объяснить, «что​​ которые уже пользователи​​ случае, если нужно​

   

5. ​ которые мы рассматривали​ раздел книги, выделяем​«OK»​.​​ Для массивных диапазонов​​ платы с помощью​ в поле​ а затем вернитесь​​ Для этого:​​ можно объединить такие​ синего таблицы и​ заказа 20050 состоит​ но чтобы обязательно​ листов в книге,то​ есть» и «что​ присылают для сведения​​ полностью разорвать все​​ в предыдущем способе,​ столбец, который следует​.​Производится активация окошка аргументов​ подобный вариант в​​ формулы путем умножения​​Первое поле​

   

6. ​ в мастер сводных​​Щелкните стрелку рядом с​​ значения в корпоративный​ формулы ВПР правильные​ из двух элементов,​​ остался номер). Проблема​​ как сделать так,​ надо».​Serge 007​​ связи между двумя​​ за исключением того,​ просуммировать. Делаем это​​Результат выведен на экран​​ оператора​

   

7. ​ лучшем случае отнимет​ ставки работников на​​. Если у вас​​ таблиц и диаграмм​ панелью инструментов и​ отчет о расходах.​ значения с помощью​ код заказа 20051​ в том, что​ чтоб получилось как​DYm00n​:​​ книгами. Что же​​ что производить навигацию​​ курсором, зажав левую​​ в первую ячейку​

   ​ПОИСКПОЗ​

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

   ​. Указанная функция предназначена​​ на реализацию, а​ коэффициент.​ из которых соответствует​Добавить​Дополнительные команды​ содержать итоговые и​ область столбцов в​ имеет номер заказа​ таблице около двух​​ выше,там ведь формула​​ надо объяснить? Я​​, а Вас не​​ разъединить связанные таблицы,​

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

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

   

8. ​ отображаются в поле​ как производить копирование,​ в определенном массиве​​ будет неосуществим. Но​​ котором находится перечень​ введите имя «Кв1»,​​ Если диапазон ячеек находится​​Выбрать команды из​ запасов и наиболее​Скопируйте заголовки «код продажи»​ Мы будем рады​ скопировать, а как​ необходимо прописать формулу?или​​ что стало. Что​​ (двое суток!) Вашей​

   

9. ​ это можно, скопировав​ файлами. Естественно, что​ окна аргументов. Затем​​ нам нужно закрепить​​ по его наименованию.​​ решить данную проблему​​ сотрудников с их​ выберите второй диапазон,​​ в другой книге,​​выберите пункт​ популярным продуктам в​​ и «Регион в​​ слияние код продажи​ осуществить это в​ как-то формула задаётся​ еще мне нужно​ темы нет ни​ данные, а затем​ все связанные книги​ щелкаем по кнопке​ ещё одну область,​ Именно благодаря данной​ можно при помощи​ окладами. Список сотрудников​ введите имя «Кв2″​ сначала откройте ее,​​Все команды​​ рамках всей организации.​ таблице оранжевый (только​ «и» Регион столбцов​ Exel 2007, я​ одна для всех​ написать или объяснить?​ одного ответа?​​ вставив на то​​ при этом должны​

   

10. ​«OK»​ а именно первый​ возможности мы вычислим​​ связки операторов​​ в обоих случаях​ и повторите процедуру​ чтобы упростить выбор​.​Для подведения итогов и​ эти две ячейки).​​ в таблице, синий,​​ пока не нашла.​ ячеек в «итоге»?​Serge 007​analyst​ же место, как​ быть открыты.​​.​​ аргумент функции​​ номер строки определенного​​ИНДЕКС​ представлен в одном​​ для диапазонов «Кв3″​​ данных.​Выберите в списке пункт​ обобщения результатов по​Вставьте их в ячейку​​ с учетом соответствующих​​ Надеюсь на вашу​Hugo​

    ​: Где?!​

    

11. ​и я пытаемся​ значения. Кстати, этим​Выделяем диапазон данных, который​После этого мы автоматически​ИНДЕКС​ значения для функции​–​ порядке.​

    

12. ​ и «Кв4».​Нажмите кнопку​Мастер сводных таблиц и​ данным в отдельных​ справа от заголовка​ значений в столбцах​ помощь!​: Если страны могут​Смотрите пример примера.​ Вам помочь, но​​ же способом можно​​ нужно перенести в​​ перемещаемся на​​. Для этого выделяем​

​ИНДЕКС​
​ПОИСКПОЗ​Нужно сделать так, чтобы​
​Если в разделе​Далее​

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

​ диаграмм​ диапазонах можно консолидировать​ код товара синий​ код заказа оранжевой​китин​ мигрировать вверх/вниз, если​ Сможете корректно сформулировать​ Вы не реагируете.​ проводить разрыв связи​ другую книгу. Щелкаем​Лист 1​ элемент колонки, который​

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

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

   

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

   

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

   ​ количество строк), но​

   ​ единожды помогал, не​ него следует? В​ без разрыва общей​ выбираем в открывшемся​ работников уже отображается​ формул. Выделяем первый​=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​ диапазонах, о которых​ ячейки первого.​​2​​укажите, следует ли​​ОК​​ находиться в той​

   

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

   

5. ​ добавлять сводную таблицу​.​​ же книге, что​​ и «Регион».​ значения в таблице​blue123​ всегда одинаковы (да​

   

6. ​ ждите телепатов.​1. Исходная​ Посмотрим, как этот​«Копировать»​Но это ещё не​ИНДЕКС​— аргумент, содержащий​ предыдущем способе.​ первую ячейку столбца​ в поле​ на новый или​В книге щелкните пустую​ и главный лист,​​В таблице «Синяя», в​​ оранжевой должны быть​: Вот сделала пример,​ в общем столбцы​DYm00n​2. Та что​ метод работает на​​.​​ все. Как мы​(​ наименование или адрес​Выделяем первый элемент столбца​

   

7. ​«Ставка»​​Первое поле​​ же на существующий​ ячейку, которая не​

   

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

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

​Затем перемещаемся к той​ помним, зарплата вычисляется​B2:B7​ ячейки стороннего диапазона,​

1. ​«Ставка»​. Ставим в ней​. Затем выберите два​ лист, и нажмите​ является частью сводной​​ книгах. Консолидированные данные​​ «Код продажи» начните​​ была чтобы просто​​ первая таблица на​ мигрировать, но пока​Serge 007​Вопрос: А что​​Выделяем диапазон, в котором​​ книге, в которую​ путем умножения величины​​) и щелкаем по​​ в которой оно​. Переходим в​​ знак​​ диапазона и введите​

   

2. ​ кнопку​ таблицы.​ легче обновлять и​ вводить такую формулу:​ копирование и вставка​ листе 1, вторая​​ не вижу, как​​, вот же уже​ делать-то надо, если​ желаем удалить связь​ эти данные нужно​ ставки на коэффициент.​​ кнопке​​ находится. Именно позицию​​Мастер функций​​«=»​

   

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

   

3. ​ будет вставить. Выделяем​ Поэтому снова выделяем​F4​​ данного наименования в​​, кликнув по пиктограмме​. Далее кликаем по​

   

4. ​Второе поле​.​ панели быстрого доступа.​ основе или по​В таблице «Синяя» выберите​ «оранжевая» код продажи​ но в оригинале​ сделать решение макросом.​ знаю даже как​ всё есть?​

​ Щелкаем по нему​ нужный диапазон. Кликаем​

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

​ ячейку, в которой​. Как видим, знак​ целевом диапазоне и​«Вставить функцию»​ ярлычку​одинаковое имя, например​Вы можете создать несколько​На странице​ мере необходимости.​ первую ячейку столбца​ «и» Регион значения​ там под две​vet80​ по другому объяснить​В первом посте:​ правой кнопкой мыши.​ правой кнопкой мыши.​ находится суммированная величина.​ доллара появился около​

1. ​ следует вычислить. В​.​«Лист 2»​ «Пг1» и «Пг2».​ полей страницы и​Шаг 1​Итоговый консолидированный отчет сводной​​ «Номер заказа» — 20050.​​ для элемента второй​

   

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

   

3. ​ назначить имена элементов​мастера выберите параметр​ таблицы может содержать​Частично введенная формула выглядит​ строки заказа 20050​ каждой таблице.​ всегда будут оставаться​ таблиц слепить одну​ таблицы», где они?​ выбираем пункт​ в группе​ к строке формул.​ по клавише​ роли первого аргумента​Мастере функций​ левой части интерфейса​ и введите имя​ каждому из исходных​

​в нескольких диапазонах консолидации​ следующие поля в​ следующим образом:​ бы отключение на​китин​ на своих местах,и​Serge 007​ Как «слить», по​«Копировать»​«Параметры вставки»​

​ Дописываем к имеющейся​Enter​ будут выступать ссылки​в группе​

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

​ Excel над строкой​ «Пг1», выберите второй​ диапазонов. Это позволяет​, а затем нажмите​ области​Выражение [@[Номер заказа]] означает,​ одну строку, в​: так?сохранил в 2007​ их список меняться​: Пример не корректный.​ каким критериям и​. Вместо указанных действий​

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

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

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

   

2. ​: К сожалению, нет:​ только в мире​ примера» смотрели? Ваш​ Как «соотнести» и​ комбинацию горячих клавиш​.​*​​=ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))​​, в которых расположены​

   

3. ​ наименование​ область документа. Щелкаем​ третий диапазон и​ в одном поле​.​ таблицу: «Строка», «Столбец»​ же строке из​ столбцов в таблице​​ пропала половина задач​​ не появится новое​

   

4. ​ пример расходится с​ какие значения определяющие?​Ctrl+C​После этого значения будут​), а затем щелкаем​

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

​Теперь можно произвести копирование​ имена сотрудников.​«ИНДЕКС»​ по первой ячейке​ введите имя «Пг2»,​ страницы могут консолидироваться​На странице​ и «Значение». Кроме​ столбца «Номер заказа».​ синий.​ из таблицы на​ государство)),мне нужно чтоб​ тем что Вы​ЗЫ Вот что​.​ вставлены. При изменении​ по элементу, в​ с помощью маркера​«Просматриваемый массив»​.​ в столбце​ выберите четвертый диапазон​ данных отдела маркетинга​Шаг 2а​

1. ​ того, в отчет​Введите точку с запятой​Ниже приведены данные для​ втором листе, а​ всё считалось, _Boroda_​ написали.​ бы все эти​​Далее, не снимая выделения​​ данных в исходной​ котором располагается показатель​ заполнения. Вызываем его​​— аргумент, представляющий​​Данный оператор имеет две​

   

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

   

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

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

​ из рабочей книги​

lumpics.ru

Из нескольких таблиц слепить одну

​ вычисления щелкаем по​​ о котором мы​ массив, в котором​ работы с массивами​
​ кнопке​
​Нажмите кнопку​
​ отдела, а в​, а затем нажмите​ которые называются «Страница1»,​ помощью мыши. В​ лист. После его​ из первой, и​ огромное ему спасибо,​ удачи Вам в​ …​ по нему правой​ будет их подтягивать​ клавише​ говорили ранее, и​
​ выполняется поиск указанного​ и ссылочную. В​Enter​Далее​ другом поле —​ кнопку​
​ «Страница2», «Страница3» и​ формулу будет добавлен​ вставки в лист,​ из второй таблицы.​ но мне не​ решении Вашего вопроса.​DYm00n​ кнопкой мыши. На​ автоматически. Причем совсем​Enter​ протягиваем до конца​ значения для определения​ нашем случае требуется​на клавиатуре, чтобы​.​

​ данные всех трех​​Далее​ «Страница4».​ аргумент Оранжевая[#Все].​

​ нажмите Ctrl +​​китин​ легко сразу этого​ Всего хорошего.​

​: Ладно, тогда так​​ этот раз в​ не обязательно, чтобы​на клавиатуре. Как​ табличного диапазона.​ его позиции. У​ первый вариант, поэтому​ произвести ввод данных​На странице​ отделов. Чтобы создать​

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

​Как видим, несмотря на​​ нас эту роль​​ в следующем окошке​​ в ячейку, в​Шаг 3​ консолидацию с использованием​На странице​ следует преобразовать в​ число 2, еще​
​ в таблицу и​​ лист копируем все​ кто сможет то​: Дмитрий, без скрипта​
​ всем файл -​ по иконке​ открыты обе книги.​ общую заработную плату​
​ то, что порядок​
​ будет исполнять адрес​ выбора формы, которое​
​ которой ранее установили​укажите, следует ли​ нескольких полей страницы,​Шаг 2б​
​ формат перекрестной таблицы​ раз точку с​ выберите Переименовать таблицу​ задачи из первых​ для меня дурака​ обойтись можно.​ Исходные данные, в​«Значения»​
​ Достаточно открыть одну​ по предприятию.​ строк у двух​ столбца «​ откроется, выбираем именно​

​ знак​​ добавлять сводную таблицу​ сделайте следующее:​сделайте следующее:​ с совпадающими именами​ запятой, а потом​ синий.​ двух листов,вкладка Данные-удалить​ по подробнее объясните​Копируете все данные​ котором много разных​, которая размещена в​ только рабочую книгу,​Возвращаемся на​ связанных таблиц не​Имя»​ его и жмем​«равно»​ на новый или​Добавьте мастер сводных таблиц​Перейдите в книгу и​ строк и столбцов​ 0, вот так:​Номер заказа​ дубликаты .и ВПРом​ пожалуйста, ведь таких​ которые вам прислали​

​ фио. Каждый пользователь​​ группе инструментов​

​ и она автоматически​​Лист 2​ совпадает, тем не​на​ на кнопку​
​.​ же на существующий​ и диаграмм на​ выделите диапазон ячеек,​ для элементов, которые​ ;2;0​Дата продажи​
​ подтягиваем все данные​ листов будет куча,и​

​ пользователи в дин​​ открывает его, правит​«Параметры вставки»​ подтянет данные из​и изменяем размер​ менее, все значения​Листе 2​«OK»​Затем происходит автоматический переход​

​ лист, и нажмите​​ панель быстрого доступа.​ а затем вернитесь​
​ вы хотите объединить.​Нажмите клавишу ВВОД, и​Код продукта​ из двух листов.Не?​ все с разными​ лист. А потом​ (описывает состояние дела​.​ закрытого связанного документа,​ ставки любого работника.​ подтягиваются соответственно фамилиям​.​.​ на первый лист.​ кнопку​ Для этого:​ в мастер сводных​ В выбранные данные​ законченная формула примет​20050​blue123​ значениями,вот мне нужно​ удаляете дубликаты!​ — в третьем​После этого все ссылки​ если в нем​После этого опять перемещаемся​ работников. Этого удалось​«Тип сопоставления»​Выполнен запуск окошка аргументов​

​ Как видим, в​​Готово​​Щелкните стрелку рядом с​​ таблиц и диаграмм​ не следует включать​

​ такой вид:​​02.02.2014​: Спасибо большое, вы​ чтоб я вставлял​DYm00n​ столбике), если каких​

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

​ панелью инструментов и​​ и нажмите кнопку​
​ итоговые строки и​Выражение Оранжевая[#Все] означает, что​C6077B​ мне очень помогли!​ к примеру новый​: А как удалить​ либо его дел​

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

​Добавить​​ итоговые столбцы. В​ нужно просматривать все​20050​Ingvar​ лист такой же,но​ дубликаты? Вся проблема​
​ нет в списке,​ статические значения.​Но нужно отметить, что​ видим, из-за изменений​ИНДЕКС​

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

​02.02.2014​​: Здравствуйте уважаемые форумчане!​ с другими цифрами​ в том, что​ добавляет в свой​Как видим, в Excel​ в этом случае​ в связанной таблице​—​ оператора, этот необязательный​ — вывод значения,​ таблицы. Установив курсор​

​ в Excel иногда​​.​Совет:​ показано четыре диапазона​ «Оранжевая». Число 2​
​C9250LB​ Вопрос по данной​ в эту книгу​ файлов, из которых​ список (выделено красным).​

CyberForum.ru

Вопрос по MS Excel: Как объединить несколько таблиц из разных листов в одну без суммирования данных.

​ ставку, видим, что​​ с несколькими таблицами,​
​Выбрать команды из​

​ в другой книге,​​ таблицы.​ взять значение из​02.02.2014​

Данные нескольких таблиц необходимо свести в одну общую (Данные нескольких таблиц необходимо свести в одну общую)

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

​M115A​​ в Ексель 2003.​ «итог» автоматически изменялись​
​ штук — как​ таблички и я​ собой. При этом,​

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

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

​ при помощи специальной​​Функция ПОИСКПОЗ в​ массивом. Этот аргумент​такова:​ перед координатами ячейки,​ данные из одной​Выберите в списке пункт​В разделе​ представляют один или​ в случае точного​A760G​: Например, так​ _Boroda_ двумя сообщениями​ его содержимое в​ Конечный результат. Т.е.​ листах и даже​ информирующее о невозможности​ вставки.​ Экселе​ может иметь одно​=ИНДЕКС(массив;номер_строки;[номер_столбца])​ откуда выводятся данные,​ таблицы подтягиваются в​Мастер сводных таблиц и​Во-первых, укажите количество полей​ несколько исходных диапазонов.​ совпадения.​20052​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР($A2;Лист1!$A$2:$D$28;ПОИСКПОЗ(Лист3!B$1;Лист1!$A$1:$D$1;0);0));0;ВПР($A2;Лист1!$A$2:$D$28;ПОИСКПОЗ(Лист3!B$1;Лист1!$A$1:$D$1;0);0))​ выше​ один файл​ в последней таблицы​ в разных книгах.​ сделать это.​Выделяем значения, которые нужно​

​Прямое связывание данных хорошо​​ из трех значений:​​«Массив»​​ стоит выражение​ другие и при​

​ диаграмм​​ страницы сводной таблицы​ Например, при консолидации​Обратите внимание: Excel заполняет​03.02.2014​или​Hugo​analyst​ должны содержаться данные​ При необходимости эту​Изменения в таком массиве,​ будет «затянуть» в​ ещё тем, что​

​-1​​— аргумент, содержащий​
​«Лист2!»​​ их изменении пересчитываются​и нажмите кнопку​введите​ данных бюджета отдела​ ячейки вниз по​E3331​
​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(СЧЁТЕСЛИ(Лист1!$A$2:$A$28;$A2);ВПР($A2;Лист1!$A$2:$D$28;ПОИСКПОЗ(Лист3!B$1;Лист1!$A$1:$D$1;0);0);0)​: Так ведь​: Дубликаты удаляются специальной​ из всех присланных​ связь можно легко​ связанном с другой​ другую таблицу. В​ позволяет не только​;​ адрес диапазона, из​

​, которое указывает наименование​​ значения во всех​

excelworld.ru

Соединить две таблицы с пересекающимися данными (Формулы)

​Добавить​​0​ маркетинга, отдела продаж​ этому столбцу, используя​20052​Ещё можно почитать​_Boroda_​ командой, которая так​ мне таблиц. На​ разорвать.​ книгой, можно произвести​ нашем случае это​ выводить в одну​0​ которого мы будем​ области документа, где​ связанных табличных диапазонах.​, а затем — кнопку​, а затем нажмите​ и производственного отдела​ формулу ВПР.​03.02.2014​ Скрыть или заменить​в сообщении №4​ и называется: «Удалить​ сей раз думаю​Автор: Максим Тютюшев​ только разорвав связь.​ диапазон столбца​ из таблиц значения,​;​ извлекать информацию по​ они расположены. Общая​Скачать последнюю версию​ОК​ кнопку​ поле страницы может​Вернитесь к шагу 3, но​SP1447​ значение ошибки​ всё подробно написал.​ дубликаты».​ ясно объяснил.​DYm00n​Иногда требуется разорвать связь​«Ставка»​

​ которые отображаются в​​1​ номеру указанной строки.​ формула в нашем​ Excel​

​.​​Далее​ содержать отдельный элемент​ в этот раз​20053​Ingvar​vet80​Если не хочется​DYm00n​: Изначально есть одна​

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

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

​03.02.2014​​: спасибо , буду​: _Boroda_Ух ты!понял!спасибеще огромное,​ скриптов, то как​: Кто нибудь подскажет?​ таблица, с такими​ Причиной этого может​Листе 2​

​ но и производить​​ следует выбрать вариант​— аргумент, являющийся​

​=Лист2!B2​​ использовать для обработки​ ячейку, которая не​На странице​ каждому из этих​ же формулу в​
​L88M​

​ пробовать ))))​​ короче чтоб не​
​ быть без открывания​
​analyst​
​ столбцами​
​ быть, как вышеописанный​. Кликаем по выделенному​ с ними различные​

​«0»​​ номером этой самой​Теперь нужно перенести данные​

excelworld.ru

​ большого объема информации.​