Как Скопировать Листы с Таблицей и Сводной Таблицей в Excel • Исходные данные
Как Скопировать Листы с Таблицей и Сводной Таблицей в Excel
Типичная задача при обработке информации полученной из разных источников. Типовое решение — взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?
Итак, приступим к решению. Мы имеем на входе 3 таблицы, расположенные на разных листах рабочей книги:
Начинаем манипуляции. Переходим в главном меню во вкладку ЁXCEL и нажимаем кнопку «Таблицы», в выпавшем меню выбираем команду «Объединить таблицы»:
В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем «ОК»:
Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:
Осталось совсем немного. Переходим в главном меню во вкладку «Данные» в разделе «Получение внешних данных» нажимаем кнопку «Существующие подключения»:
В открывшемся диалоговом окне выбираем «Подключения в этой книге» — «Запрос из Excel Files» и нажимаем «Открыть»:
В открывшемся диалоговом окне устанавливаем переключатели в положения «Отчет сводной таблицы» и «Новый лист», нажимаем «ОК»:
Программа создаст в книге новый лист на который выведет макет сводной таблицы. Обратите внимание — программа создаст в сформированной таблице новый столбец с названиями листов из которых были получены данные:
Достройте сводную таблицу до необходимого состояния, добавьте в любую из связанных таблиц новую строчку, обновите сводную таблицу — в ней появятся добавленные данные.
Чтобы обновление сводной таблицы происходило автоматически вставьте в модуль каждого листа содержащего таблицы следующий код ( Как вставлять макросы? ):
Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль «ЭтаКнига» следующий код:
Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.
Чтобы оценить всю прелесть — выньте файл из архива и при загрузке файла включите макросы.
Возможные ошибки при использовании этого метода:
- В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: «В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.» Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
- Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
- В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос — текстовые данные появятся.
Добавить комментарий
Комментарии
Здравствуйте. При создании сводной из запроса выскакивает такая ошибка: Драйвер ODBC Excel Недопустимое использование скобок с именем ‘Эл.
Сводная таблица в excel – как сделать в разных версиях программы
- Список полей;
Список всех заголовков столбцов исходной таблицы с данными. - Фильтры;
Добавление дополнительного среза для детализации данных. - Строки;
Поля таблицы вынесенные в строки. - Столбцы;
Поля таблицы вынесенные в столбцы; - Значения.
Вычисляемые числовые данные по соответствующим полям из строк и столбцов (единственный вычисляемый элемент в таблице).
Сводная таблица готова, а значит, можно начать с ней работать. Разберемся с тем, для чего нужен фильтр отчета. Для этого кликнем по ячейке «Все». В выпадающем списке появятся наши заказчики. Можно отсортировать таблицу, выкинув из нее данные по одному из них.
Думал, что проблема в названии листов (с точкой), где находятся таблицы: Эл. и Осн.
Убрал из названия точки, создаю запрос заново, а в существующих подключениях он не отображается.
В чем может быть проблема?
Добрый день!
Попробуйте удалить все запросы через Данные->Запросы и Подключения. И повторите операцию.
Сергей, скачала надстройку. Но в сводной таблице с двух листов, в названия для добавления появились не названия полей моих таблиц, а F1,F2. F10 . А так же не перетаскиваются эти F ни в строки ни в столбцы. В чем может быть ошибка?
Екатерина, у вас, наверное, лишние столбцы на листах или таблицы начинаются не с первой строки. Надо удалить все пустые строки над таблицами.
Александра, здравствуйте!
Перейдите во вкладку меню «Данные», в разделе «Запросы и подключения» или «Подключения» (в старых версиях), нажмите кнопку «Запросы и подключения» или «Подключения» (в старых версиях). В открывшемся диалоговом окне выберите и удалите ненужные запросы.
Александра, эта ошибка может появляться если вы после формирования запроса пересохранили файл в другой папке или под другим именем.
Сводная таблица в excel – как сделать в разных версиях программы
- Список полей;
Список всех заголовков столбцов исходной таблицы с данными. - Фильтры;
Добавление дополнительного среза для детализации данных. - Строки;
Поля таблицы вынесенные в строки. - Столбцы;
Поля таблицы вынесенные в столбцы; - Значения.
Вычисляемые числовые данные по соответствующим полям из строк и столбцов (единственный вычисляемый элемент в таблице).
Сводная таблица готова, а значит, можно начать с ней работать. Разберемся с тем, для чего нужен фильтр отчета. Для этого кликнем по ячейке «Все». В выпадающем списке появятся наши заказчики. Можно отсортировать таблицу, выкинув из нее данные по одному из них.
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.