Перенос данных из одной таблицы Excel в другую
Таблица в Excel – это сложный массив с множеством параметров. Он может состоять из значений, текстовых ячеек, формул и быть отформатирован разными способами (ячейки могут иметь определенное выравнивание, цвет, направление текста, специальные пометки и т.п.).
При копировании таблицы иногда требуется перенести не все ее элементы, а только некоторые. Рассмотрим, как это можно сделать.
Специальная вставка
Очень удобно выполнять перенос данных таблицы с помощью специальной вставки. Она позволяет выбрать только те параметры, которые нам необходимы при копировании. Рассмотрим пример.
Имеем таблицу с показателями по наличию макарон определенных марок на складе магазина. Наглядно видно, сколько килограмм было в начале месяца, сколько докуплено и продано, а также остаток на конец месяца. Два важных столбца выделены разными цветами. Остаток на конец месяца рассчитан по элементарной формуле.
Попробуем воспользоваться командой СПЕЦИАЛЬНАЯ ВСТАВКА и скопировать все данные.
Сначала мы выделяем имеющуюся таблицу, правой кнопкой вызываем меню и жмем КОПИРОВАТЬ.
В свободной ячейке снова вызываем меню правой кнопкой и нажимаем СПЕЦИАЛЬНАЯ ВСТАВКА.
Если мы оставим все как есть по умолчанию и просто нажмем ОК, то таблица вставится полностью, со всеми ее параметрами.
Попробуем поэкспериментировать. В СПЕЦИАЛЬНОЙ ВСТАВКЕ выберем другой пункт, например, ФОРМУЛЫ. Получили уже неформатированную таблицу, но с работающими формулами.
Теперь вставим не формулы, а только ЗНАЧЕНИЯ результатов их вычислений.
Чтобы новая таблица со значениями получила внешний вид аналогичный образцу выделим ее и вставим ФОРМАТЫ с помощью специальной вставки.
Теперь попробуем выбрать пункт БЕЗ РАМКИ. Получили полную таблицу, но только без выделенных границ.
Полезный совет! Чтобы перенести формат вместе с размерами столбцов нужно перед копированием выделить не диапазон исходной таблицы, а столбцы целиком (в данном случаи это диапазон A:F).
Аналогично можно поэкспериментировать с каждым пунктом СПЕЦИАЛЬНОЙ ВСТАВКИ, чтобы наглядно увидеть, как она работает.
Перенос данных на другой лист
Перенос данных на другие листы книги Excel позволяет связывать несколько таблиц. Это удобно тем, что при замене какого-то значения на одном листе, меняются значения на всех остальных. При создании годовых отчетов это незаменимая вещь.
Рассмотрим, как это работает. Для начала переименуем листы Excel в месяцы. Затем с помощью уже знакомой нам СПЕЦИАЛЬНОЙ ВСТАВКИ перенесем таблицу на февраль и удалим значения из трех столбов:
- На начало месяца.
- Приход.
- Расход.
Столбец «На конец месяца» у нас задан формулой, поэтому при удалении значений из предыдущих столбцов, он автоматически обнуляется.
Перенесем данные по остатку макарон каждой марки с января на февраль. Это делается буквально в пару нажатий.
- На листе ФЕВРАЛЬ ставим курсор в ячейку, показывающую количество макарон марки А на начало месяца. Можно посмотреть на рисунок выше – это будет ячейка D3.
- Ставим в этой ячейке знак РАВНО.
- Переходим на лист ЯНВАРЬ и кликаем на ячейку, показывающую количество макарон марки А на конец месяца (в нашем случае это ячейка F2 на листе «январь»).
Получаем следующее: в ячейке C2 образовалась формула, которая отсылает нас на ячейку F2 листа ЯНВАРЬ. Протянем формулу вниз, чтобы знать количество макарон каждой марки на начало февраля.
Аналогично можно перенести данные на все месяцы и получить наглядный годовой отчет.
Перенос данных в другой файл
Аналогично можно переносить данные из одного файла в другой. Данная книга в нашем примере так и называется EXCEL. Создадим еще одну и назовем ее ПРИМЕР.
Примечание. Создавать новые файлы Excel можно даже в разных папках. Программа будет автоматически искать указанную книгу, независимо от того, в какой папке и на каком диске компьютера она находится.
Скопируем в книгу ПРИМЕР таблицу с помощью все той же СПЕЦИАЛЬНОЙ ВСТАВКИ. И опять удалим значения из трех столбцов. Проведем те же действия, что и в предыдущем параграфе, но переходить уже будем не на другой лист, а на другую книгу.
Получили новую формулу, которая показывает, что ячейка ссылается на книгу EXCEL. Причем видим, что ячейка F2 выглядит как $F$2, т.е. она зафиксирована. И если мы хотим протянуть формулу на остальные марки макарон, сначала нужно удалить значки доллара, чтобы снять фиксацию.
Теперь вы знаете, как грамотно переносить данные из таблиц в рамках одного листа, с одного листа на другой, а также с одного файла в другой.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Как в excel скопировать данные из одного листа на другой лист?
Есть таблица с перечнем объектов состоящая из 4 столбцов: владелец, наименование объекта, населенный пункт, код. На каждый населенный пункт создал свой лист. Как из общего списка объектов скопировать строчку с названием населенного пункта и вставить в лист с аналогичным названием?
- Вопрос задан более трёх лет назад
- 2120 просмотров
Комментировать
Решения вопроса 0
Ответы на вопрос 2
shushpanio @shushpanio
shushpanio
По пунктам:
1. Копируете шапку таблицы на все листы;
2. На листе с общим списком щелкаете ЛКМ в любой ячейки шапки;
3. Вкладка Данные — Фильтр
4. В ячейке с заголовком «населенный пункт» нажимаете на появившийся в нижнем правом углу треугольник;
5. В появившемся окне выбираем первый населенный пункт и нажимаем ОК;
6. Копируем отфильтрованные строки;
7. Вставляем на лист с именем совпадающим выбранному в фильтре населенному пункту;
8. Повторить пункты 5-7 для каждого населенного пункта.
Вжууухх. И всё готово.
UPD
Для автоматизации можно поступить следующим образом:
1. на листе с общим списком заводите поле, например Служебный код. (сделать надо слева от данных которые надо перенести)
2. в ячейке служебный код прописываете формулу аналогичную скрину
т.о. получаем значения в формате Город_который раз этот город встречается в общем списке.
Пример для ячейки В2: =СЦЕПИТЬ(E2;СЧЁТЕСЛИ($E$1:E2;E2))
3. На примере листа Москва:
Делаем таблицу аналогично общему списку.
В поле служебный код руками вводим первые 2 значения: Москва1 и Москва2. Далее протягиваем их вниз т.о. получив Москва3, Москва4 и тд.
4. С помощью ВПР тянем данные с листа с общим списком. Формулы на скрине
Пример для ячейки В2:=ВПР(A2;Список!B:G;2;0), для С2 =ВПР(A2;Список!B:G;3;0) и т.д.
5. Убираем значения #Н/Д с помощью фильтра, т.к. если мы на листе с городами завели строк больше чем по факту город встречается в основном списке то формула выдаст #Н/Д
Пусть костыльная, но самая быстрая возможность организации автоматизации.
Перемещение и копирование листов или данных листов
Листы можно перемещать или копировать в пределах одной книги, чтобы упорядочить содержащиеся в ней данные.
С помощью команды Переместить или скопировать лист можно полностью переместить или скопировать листы в пределах одной или нескольких книг. Чтобы переместить или скопировать часть данных на другие листы или в другие книги, можно использовать команды Вырезать и Копировать.
Перемещение листа в книге
- Щелкните ярлычок листа и перетащите его в нужное место.
Внимание: После перемещения листа в другую книгу проверьте все формулы и диаграммы, которые ссылаются на данные этого листа: в результате этой операции в книге могут появиться ошибки или неожиданные результаты. Точно так же, если переместить лист, на который ссылается трехмерные ссылки, данные на нем могут быть включены или не включены в расчет.
Копирование листа в одной книге
- Удерживая нажатой клавишу CTRL, перетащите ярлычок листа на нужное место.
- Щелкните ярлычок листа правой кнопкой мыши и выберите команду Переместить или скопировать.
- Установите флажок Создать копию.
- В разделе перед листом укажите, куда вы хотите поместить копию.
- Нажмите кнопку ОК.
Если данные требуется переупорядочить, можно менять порядок листов в книге, а также копировать и перемещать листы в другие книги. Обратите внимание на то, что после перемещения листа в вычислениях и на диаграммах, в основе которых лежат содержащиеся на нем данные, могут использоваться неправильные значения.
Перемещение листа
- Перетащите ярлычок листа в нужное место в строке ярлычков.
Копирование листа
- Нажмите и удерживайте клавишу OPTION.
- Перетащите ярлычок листа в строке ярлычков в место, где нужно создать копию.
Важно: Отпустите сначала кнопку мыши, а затем — клавишу OPTION.
Перемещение листа в другую книгу
Внимание: После перемещения листа в другую книгу проверьте все формулы и диаграммы, которые ссылаются на данные этого листа: в результате этой операции в книге могут появиться ошибки или неожиданные результаты. Точно так же, если переместить лист, на который ссылается трехмерные ссылки, данные на нем могут быть включены или не включены в расчет.
- Откройте книгу, в которую требуется переместить лист.
- В меню Окно щелкните книгу, в которую входит перемещаемый лист. Щелкните лист, который требуется скопировать в другую книгу.
- В меню Правка выберите пункты Лист >Переместить или скопировать лист.
- В меню В книгу выберите книгу, в которую требуется переместить лист.
Совет: Чтобы создать новую книгу и переместить в нее лист, выберите вариант новая книга.
Копирование листа в другую книгу
- Откройте книгу, в которую требуется скопировать лист.
- В меню Окно щелкните книгу, в которую входит копируемый лист. Выберите лист, который требуется скопировать.
- В меню Правка выберите пункты Лист >Переместить или скопировать лист.
- В меню В книгу выберите книгу, в которую требуется скопировать лист.
Совет: Чтобы создать новую книгу и переместить в нее лист, выберите вариант новая книга.
Изменение цвета ярлычка листа
Изменение цвета ярлычков листов может облегчить отслеживание информации в больших книгах.
- Удерживая клавишу CONTROL, щелкните ярлычок листа.
- Выберите команду Цвет ярлычка и укажите нужный цвет.
В Excel в Интернете можно дублировать (или копировать) книги. Просто щелкните правой кнопкой мыши имя ярлычка в нижней части листа и выберите команду Дублировать.
Примечание: При дублировании листа, содержащего диаграмму, рисунок или фигуру, может появиться сообщение об ошибке. В этом случае лист можно продублировать вручную.
Дублирование листа вручную
Если ваш лист содержит элементы, из-за которых его невозможно продублировать, можно создать его копию вручную, скопировав все данные и вставив их на новый лист. Вот как это сделать:
- Выделите все данные на листе. Сочетания клавиш. Нажмите клавиши CTRL+ПРОБЕЛ на клавиатуре, а затем клавиши SHIFT+ПРОБЕЛ.
- Скопируйте все данные на листе, нажав CTRL+C.
- Щелкните знак плюса (+), чтобы добавить пустой лист.
- Щелкните первую ячейку на новом листе и нажмите CTRL+V, чтобы ввести данные.
Примечание: При вставке ячеек на новый лист примененное к ним условное форматирование будет потеряно.
Копирование листа в другую книгу
В Excel в Интернете, если щелкнуть правой кнопкой мыши явку листа, как в настольном приложении Excel, вы не сможете переместить или скопировать лист. В Excel в Интернете вы можете получить такой же результат, скопируя все данные на листе и вкопив их на пустой лист в другой книге.
Чтобы переместить или скопировать книгу в другую книгу в Excel в Интернете, выполните указанные Excel в Интернете.
- Выделите все данные на листе. Сочетания клавиш: нажмите клавиши CTRL+ПРОБЕЛ, а затем клавиши SHIFT+ПРОБЕЛ.
- Скопируйте все данные на листе, нажав CTRL+C.
- Откройте книгу, в которую необходимо вставить данные, и щелкните знак «плюс» (+) в строке состояния, чтобы добавить новый пустой лист.
- Щелкните первую ячейку нового таблицы, а затем нажмите CTRL+V, чтобы ввести данные на этот.
Примечание: При вставке ячеек в другую книгу примененное к ним условное форматирование будет потеряно.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как в excel перенести данные с одного листа на другой с определенных ячеек
Помогите пожалуйста с макросом, необходимо из большого числа уже имеющихся данных перенести некоторые на другой лист. Прикрепил файл с примером,где на листе «пример» в первой строке вписываются наименования ( в данном примере — OKE,PCG,PNY,POM)
, все значения которых необходимо перенести с листа «данные», включая и пустые ячейки, т.е. как есть.
Прикрепленные файлы
- post_188561.xls (33 КБ)
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
05.01.2011 23:31:18
Т.е. На листе «Пример» вписываем в первую строку нужное название столбца, и данные этого столбца с другого листа копируются под эту ячейку. Так?
Пользователь
Сообщений: 31 Регистрация: 01.01.1970
05.01.2011 23:39:49
Да, так, весь столбец.
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
05.01.2011 23:46:45
Прикрепленные файлы
- post_188568.xls (45 КБ)
Пользователь
Сообщений: 31 Регистрация: 01.01.1970
06.01.2011 00:03:47
не совсем, когда вставил все наименования, то получилась несоответствие. Хотелось бы, чтобы данная задача решалась с помощью макроса и на большом объеме данных (несколько тысяч наименований).
Прикрепленные файлы
- post_188570.xls (45.5 КБ)
Пользователь
Сообщений: 8839 Регистрация: 11.01.2013
06.01.2011 00:05:52
Вписывать вручную?! Это не наш метод 🙂
Нажмите на синюю кнопку.
Прикрепленные файлы
- post_188571.xls (49 КБ)
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
06.01.2011 00:09:53
Скорее всего Вы вставили сразу значения в несколько ячеек. Такого условия не ставилось. А разве сейчас задача решается не с помощью макроса?
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
06.01.2011 00:11:10
Конечно, — у меня же нет такой краски 🙂
Пользователь
Сообщений: 31 Регистрация: 01.01.1970
06.01.2011 00:29:20
Спасибо! буду разбираться с синими кнопочками.
06.01.2011 00:55:16
Наконец разобрался. Спасибо Юрий М, спасибо Казанский.
06.01.2011 03:50:33
К сожалению «синяя кнопочка» даёт выбрать данные только до столбца IE. Дело просто в том, что объём данным такой, что их не только руками списывать, но и галочки ставить займёт пол дня. Видимо я изначально не правильно выразил мысль. возможна ли доработка вышепредложенных вариантов, но только наименования не вписываются каждая отдельно, а копируются все сразу, а затем значения уже подставляются?
06.01.2011 10:48:03
Если «копируются все сразу», то зачем все это, в чем смысл? Копируйте лист целиком.
06.01.2011 15:10:49
Пользователь
Сообщений: 31 Регистрация: 01.01.1970
06.01.2011 17:19:47
«копируются все сразу» имеется ввиду,что копируются и вставляются все сразу те, которые нужны. Но не все те, которые на листе «данные».
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
06.01.2011 17:30:40
Rewa, я вот что-то никак не пойму: Вы выделяете несколько ячеек первой строки второго листа и хотите, чтобы столбцы выделенных ячеек скопировались на первый лист. Так выделите не ячейки, а столбцы целиком и скопируйте их. А вариант Казанского вообще очень удобен для Ваших целей — когда нужно скопировать несколько столбцов. Список в ListBox может быть продолжен по количеству задействованных ячеек на втором листе. Чем не угодил этот вариант?
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
06.01.2011 18:01:47
Скопируйте этот код в модуль листа «данные»
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(«B1:GR1»)) Is Nothing Then
Selection.Columns.EntireColumn.Copy Sheets(«пример»).Cells(1, 2)
End If
End Sub
Контролируется выделение ячеек в диапазоне B1:GR1
06.01.2011 18:19:20
Не совсем. У меня есть отдельный список с наименованиями, значения которых мне нужно найти на листе «данные». В оригинале на листе «данные» у меня примерно 3500 наименований, из них мне нужно найти и перенести значения на новый лист («пример»)в первом случае 1800, во втором случае 1500. В ручную делать не реально долго,потому и обратился за помощью, А вариант Казанского хорош, но в моём случай не совсем подходит. Вот я и хотел, вставить с отдельного файла нужные мне наименования, значения которых затем найти из листа «данные».