Как вставить данные из одной таблицы в другую в google excel
Основное преимущество Google Docs - возможность совместной работы в режиме "онлайн", просмотра изменений,
сделанных каждым участником, и автоматическим сохранением актуальной версии (не будет больше обидных вылетов и выключений компьютера без сохраненного текста или документа!).
Особенно полезны Google Spreadsheets, или Google таблицы - аналог приложения Excel.
Они могут пригодиться вам для:
- совместного планирования отпуска с друзьями и расчета бюджета;
- ведения реестров с вашими: клиентами, заказами, поставщиками и т.д., которые заполняются несколькими людьми параллельно;
- онлайн - координации совместных действий.
Я поделюсь несколькими простыми, но очень полезными функциями, которые могут сохранить вам немало времени. В статье приводятся русские названия функций, чтобы вы могли воспользоваться ими и в Excel (кроме функции IMPORTRANGE, которой в Excel нет).
Функция IMPORTRANGE
Для переноса данных из одного файла в другой в Google Таблицах используется функция IMPORTRANGE.
Для чего она может пригодиться?
Например, вам нужны актуальные данные из файла ваших коллег;
или вы хотите обрабатывать данные из файла, к которому у вас есть доступ "Только для просмотра";
или вы хотите собрать в одном документе таблицы из нескольких и вместе их обрабатывать или просматривать.
Эта функция позволяет получить копию диапазона из другой Google таблицы. Форматирование при этом не переносится - только данные.
Синтаксис функции следующий:
spreadsheet_key (ключ_таблицы) – последовательность символов в атрибуте "key left">Иначе говоря, ключ таблицы - это последовательность символов в ссылке на таблицы после "spreadsheets/d/"
Вместо ключа таблицы вы можете использовать полную ссылку на документ:
В файле, в котором вы введете эту формулу, будет отображаться диапазон A1:CM500 с Листа1 из файла, который находится по соответствующей ссылке.
Кроме того, ссылку на файл и ссылку на диапазон можно вводить не в саму функцию, а в ячейки вашего документа, и ссылаться на них.
Так, если в ячейку A2 вы введете ссылку на документ, из которого нужно загрузить данные, а в ячейку B2 - ссылку на лист и диапазон, то загружать данные можно будет с помощью следующей формулы:
Видео:
IMPORTRANGE как аргумент другой функции
IMPORTRANGE может быть аргументом другой функции, если диапазон, который вы импортируете, подходит на эту роль.
Рассмотрим простой пример - среднее значение по продажам из диапазона, находящегося в другом документе.
Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз)
Сначала импортируем этот диапазон:
А потом используем это как аргумент функции СРЗНАЧ (AVERAGE):
Получаем результат, который будет обновляться при добавлении новых строк в исходном файле в столбце D.
Функция ВПР (VLOOKUP)
Это функция - аналог функции ВПР в Excel. Она необходима для переноса данных из одной таблицы в другую.
У нее следующие аргументы:
VLOOKUP (искомое значение; таблица; номер столбца; интервальный_просмотр)
искомое значение - это то значение, которое мы будем искать в другой таблице.
Например, в отдельном файле у вас есть стандартная ставка перевода книг с английского - по трем категориям сложности.
А в таблице с текущими проектами вы просто указываете категорию сложности (это и будет искомое значение), по которому функцией ВПР (VLOOKUP) подтягиваете ставку.
Таблица - это диапазон данных, из которого вы подтягиваете информацию.
Аргумент "номер столбца" определяет , из какого столбца ТАБЛИЦЫ (а не листа! это важно) вы будете брать данные.
Интервальный_просмотр обычно равен нулю - в таком случае будет вестись точный, а не приблизительный поиск.
Функция ПОИСКПОЗ (MATCH)
Функция ПОИСКЗПОЗ (в английской версии Excel и Google Таблиц она называется MATCH) позволяет определить порядковый номер элемента (обычно - текста, записанного в ячейке) в определенном списке.
Эта функция очень удобна для быстрого сравнения двух списков: очевидно, что если функция не может найти порядковый номер какого-то элемента из первого списка во втором списке, то его там просто нет.
Таким образом, вы можете быстро понять, какие элементы одного списка отсутствуют во втором.
Синтаксис функции следующий:
MATCH (искомое_значение; список; точный поиск)
Искомое значение - то, что мы ищем (обычно ячейка с текстом), список - диапазон, в котором мы ищем. Последний аргумент должен равняться нулю, если вы хотите вести точный поиск (обычно именно это и нужно)
Сочетание функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH)
К сожалению, функция ВПР (VLOOKUP) не работает, когда искомые значения в исходной таблице находятся не в первом столбце.
Но вы можете воспользоваться сочетанием функций ПОИСКПОЗ (MATCH - обсуждалась ранее) и ИНДЕКС (INDEX).
Функция ИНДЕКС возвращает элемент из списка по его порядковому номеру.
А порядковый номер вы определяете с помощью MATCH.
В примере мы подтягиваем тематику по названию книги, хотя названия находятся не в первом столбце искомой таблицы.
Импортирует диапазон ячеек из одной электронной таблицы в другую.
Пример использования
IMPORTRANGE(" https://docs.google.com/spreadsheets/d/abcd123abcd123 ", "лист1!A1:C10")
Синтаксис
url_таблицы – URL таблицы, из которой импортируются данные.
- Значение url_таблицы должно быть текстом, заключенным в кавычки, или ссылкой на ячейку, в которой содержится таблица.
диапазон – строка в формате "[название_листа!]диапазон" (например, "Лист1!A2:B6" или "A2:B6" ). Этот параметр указывает на диапазон, который нужно импортировать.
Компонент название_листа в параметре диапазон не является обязательным. По умолчанию IMPORTRANGE импортирует данные из заданного диапазона первого листа.
Значение параметра диапазон должно быть текстом, заключенным в кавычки, или ссылкой на ячейку, которая содержит необходимую информацию.
Технические сведения и рекомендации
Если изменить исходный документ, функция IMPORTRANGE обеспечит обновление всех открытых принимающих документов. При этом на экране появится полоса зеленого цвета. Кроме того, функция IMPORTRANGE возвращает результаты в принимающий документ только после того, как в исходном документе будут завершены все расчеты, даже если в указанном диапазоне никаких расчетов нет.
Рекомендации
- Не используйте слишком много принимающих листов: каждый из них должен получать данные с исходного листа.
- Измените структуру документа, если вы импортируете данные с листа, который значительно или часто обновляется, такого как форма с ответами.
- Сократите количество пересчитываемых величин, встроенных в функцию IMPORTRANGE .
Изменения, внесенные при работе с функцией IMPORTRANGE , могут влиять на другие данные. Если на листе Б содержится функция IMPORTRANGE (листА), а на листе В – функция IMPORTRANGE (листБ), то при внесении изменений в данные на листе А будут также обновлены листы Б и В.
Рекомендации
- Используйте меньше цепочек в рамках функции IMPORTRANGE .
- Старайтесь не применять циклы в функции IMPORTRANGE .
- Изменения, внесенные на исходном листе, могут появиться на принимающем листе не сразу. Если функция IMPORTRANGE реализована много раз в нескольких связанных документах, от момента внесения изменений на исходном листе до появления результатов на принимающем листе может пройти немало времени.
Функция IMPORTRANGE обновляется, если один из пользователей открыл ее в течение последних пяти минут. Как и в случае с цепочкой обновлений, функция IMPORTRANGE вынуждена обращаться к каждому документу, из которого или в который импортируются данные.
Рекомендации
- Помните, что для обновления задействованных вами документов может потребоваться некоторое время. По возможности используйте меньше цепочек в рамках функции IMPORTRANGE .
Разрешение и доступ
Когда таблица 1 впервые импортирует данные из таблицы 2 при выполнении функции IMPORTRANGE , система запрашивает разрешение на доступ к информации. После получения разрешения все редакторы таблицы 1 смогут использовать IMPORTRANGE для импорта любых данных из таблицы 2. Разрешение будет действовать до тех пор, пока пользователь, давший его, не будет лишен прав доступа к таблице 2. Учтите, что предоставление доступа к принимающему листу учитывается в ограничении в 600 пользователей общего диска, которое действует для исходного листа.
Производительность
Функция IMPORTRANGE использует внешние данные, как и функции IMPORTXML и GOOGLEFINANCE . Это означает, что для работы функции необходимо подключение к интернету. Google Таблицы скачивают весь нужный диапазон на компьютер и на их работе скажется низкая скорость подключения к интернету. При этом действует ограничение на объем полученных данных (10 МБ для одного запроса). Если функция IMPORTRANGE работает медленно, попробуйте уменьшить размер диапазонов, которые следует импортировать. Вы также можете перенести сводные расчеты в исходный документ. Это позволит вам перемещать меньше данных в листы, находящиеся на компьютере, и выполнять больше расчетов удаленно.
Примечание. Вам доступны другие похожие инструменты. Apps Script может принимать данные из других документов и срабатывать при внесении изменений или по расписанию. Подключенные таблицы обновляются по расписанию и больше подходят для загрузки и импорта крупных наборов данных.
Если у вас остались вопросы, вы можете посетить справочный форум Редакторов Документов.
Похожие функции
IMPORTXML : Импорт данных из источников в формате XML, HTML, CSV, TSV, а также RSS и ATOM XML..
IMPORTHTML : Импортирует данные из таблицы или списка на веб-странице..
IMPORTFEED : Импортирует фид RSS или Atom..
IMPORTDATA : Импортирует данные в формате CSV (значения, разделенные запятыми) или TSV (значения, разделенные табуляцией). Для импорта необходимо указать ссылку на источник данных..
Я поделюсь несколькими простыми, но очень полезными функциями, которые могут сохранить вам немало времени. В статье приводятся русские названия функций, чтобы вы могли воспользоваться ими и в Excel (кроме функции IMPORTRANGE, которой в Excel нет).
Функция IMPORTRANGE
Для переноса данных из одного файла в другой в Google Таблицах используется функция IMPORTRANGE.
Для чего она может пригодиться?
Например, вам нужны актуальные данные из файла ваших коллег;
или вы хотите обрабатывать данные из файла, к которому у вас есть доступ «Только для просмотра»;
или вы хотите собрать в одном документе таблицы из нескольких и вместе их обрабатывать или просматривать.
Эта функция позволяет получить копию диапазона из другой Google таблицы. Форматирование при этом не переносится — только данные.
Синтаксис функции следующий:
IMPORTRANGE(spreadsheet_key; range_string)
spreadsheet_key (ключ_таблицы) – последовательность символов в атрибуте «key=» (ключ) в ссылке на таблицу.
Иначе говоря, ключ таблицы — это последовательность символов в ссылке на таблицы после «spreadsheets/d/»
=IMPORTRANGE(«abcd123abcd123»; «sheet1!A1:C10»)
Вместо ключа таблицы вы можете использовать полную ссылку на документ:
В файле, в котором вы введете эту формулу, будет отображаться диапазон A1:CM500 с Листа1 из файла, который находится по соответствующей ссылке.
Кроме того, ссылку на файл и ссылку на диапазон можно вводить не в саму функцию, а в ячейки вашего документа, и ссылаться на них.
Так, если в ячейку A2 вы введете ссылку на документ, из которого нужно загрузить данные, а в ячейку B2 — ссылку на лист и диапазон, то загружать данные можно будет с помощью следующей формулы:
=IMPORTRANGE(A2;B2)
IMPORTRANGE как аргумент другой функции
IMPORTRANGE может быть аргументом другой функции, если диапазон, который вы импортируете, подходит на эту роль.
Рассмотрим простой пример — среднее значение по продажам из диапазона, находящегося в другом документе.
Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз)
Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз)
Сначала импортируем этот диапазон:
А потом используем это как аргумент функции СРЗНАЧ (AVERAGE):
Получаем результат, который будет обновляться при добавлении новых строк в исходном файле в столбце D.
Функция ВПР (VLOOKUP)
Это функция — аналог функции ВПР в Excel. Она необходима для переноса данных из одной таблицы в другую.
У нее следующие аргументы:
VLOOKUP (искомое значение; таблица; номер столбца; интервальный_просмотр)
искомое значение — это то значение, которое мы будем искать в другой таблице.
Например, в отдельном файле у вас есть стандартная ставка перевода книг с английского — по трем категориям сложности.
А в таблице с текущими проектами вы просто указываете категорию сложности (это и будет искомое значение), по которому функцией ВПР (VLOOKUP) подтягиваете ставку.
Таблица — это диапазон данных, из которого вы подтягиваете информацию.
Аргумент «номер столбца» определяет , из какого столбца ТАБЛИЦЫ (а не листа! это важно) вы будете брать данные.
Интервальный_просмотр обычно равен нулю — в таком случае будет вестись точный, а не приблизительный поиск.
Функция ПОИСКПОЗ (MATCH)
Функция ПОИСКЗПОЗ (в английской версии Excel и Google Таблиц она называется MATCH) позволяет определить порядковый номер элемента (обычно — текста, записанного в ячейке) в определенном списке.
Эта функция очень удобна для быстрого сравнения двух списков: очевидно, что если функция не может найти порядковый номер какого-то элемента из первого списка во втором списке, то его там просто нет.
Таким образом, вы можете быстро понять, какие элементы одного списка отсутствуют во втором.
Синтаксис функции следующий:
MATCH (искомое_значение; список; точный поиск)
Искомое значение — то, что мы ищем (обычно ячейка с текстом), список — диапазон, в котором мы ищем. Последний аргумент должен равняться нулю, если вы хотите вести точный поиск (обычно именно это и нужно)
Соавтор(ы): Travis Boylls. Трэвис Бойллз — автор и редактор, пишущий о технологиях для wikiHow. Имеет опыт в написании статей на технические темы, оказании компьютерной технической поддержки и графическом дизайне. Специализируется на Windows, macOS, Android, iOS и Linux. Изучал графический дизайн в Муниципальном колледже Пайкс-Пик.
Из этой статьи вы узнаете, как извлечь данные из листов в Google Таблицах и из отдельной таблицы. Чтобы импортировать данные из отдельного документа, вам понадобится адрес таблицы, данные которой нужно извлечь.
- В противном случае войдите в свою учетную запись Google.
- Если в таблице только один лист, нажмите на знак «+» в левом нижнем углу страницы.
- Если в названии листа стоят пробелы или символы, заключите название в одинарные кавычки. Например, если вы хотите скопировать ячейку «А1» из листа под названием «Лист Бюджета $$$», то формула будет иметь следующий вид: ='Лист Бюджета $$$'!А1
Перетащите синий маркер заполнения, чтобы скопировать соседние ячейки. Если вы хотите импортировать другие ячейки из того же листа, нажмите на небольшой синий маркер в правом нижнем углу выделенной ячейки и перетащите его, чтобы скопировать другие ячейки.
- В противном случае войдите в свою учетную запись Google.
Откройте таблицу, из которой нужно импортировать данные. Нажмите на документ, из которого хотите импортировать данные.
- На компьютере Mac с трекпадом или компьютерной мультитач-мышью нажмите на трекпад двумя пальцами или зажмите клавишу Control и щелкните мышью.
- Чтобы вставить адрес, нажмите правой кнопкой мыши и выберите «Вставить» или нажмите Ctrl + V на Windows или ⌘ Command + V на Mac.
Нажмите Разрешить доступ во всплывающем окне. Когда вы впервые попытаетесь извлечь данные из другой таблицы, вам будет выдан запрос о разрешении на импорт данных. После этого ваши данные будут импортированы в эту таблицу.
Дополнительные статьи
Об этой статье
В рамках этой статьи я рассмотрю два метода импорта данных из одной Google Таблицы в другую. Первый метод подразумевает синхронизацию выбранного диапазона, что позволяет отслеживать все изменения. Второй завязан именно на переносе информации для дальнейшего редактирования. Выберите подходящий способ и следуйте инструкции, чтобы достичь желаемого результата.
Способ 1: Функция IMPORTRANGE
Функция IMPORTRANGE позволяет указать таблицу и диапазон клеток для импорта в другой файл. При этом все изменения в оригинале подхватываются и автоматически вносятся туда, куда произошел импорт. Это полезно не только для ведения сводок, но и выполнения других задач с участием нескольких таблиц.
Важно, чтобы импортируемая таблица была доступна по ссылке. Для этого понадобится изменить настройки следующим образом:
Откройте файл, который хотите перенести в другую таблицу, и щелкните по кнопке «Настройки доступа».
Если для таблицы еще не задано название, обязательно впишите его, после чего переходите к следующему шагу.
Щелкните по ссылке «Разрешить доступ всем, у кого есть ссылка».
Передайте информацию владельцу данного файла, чтобы он разрешил вам доступ как читателю или редактору. Если вы являетесь владельцем этой таблицы, можете пропустить этап настройки и приступить к вводу функции, однако сначала появится ошибка с кнопкой «Открыть доступ», которую и следует нажать для предоставления доступа.
Это было короткое предисловие для того, чтобы у вас не возникло проблем с использованием функции. Теперь перейдем непосредственно к импорту нужного диапазона с данными при помощи встроенного в Гугл Таблицы инструмента.
Выберите пустую клетку, которая послужит началом новой таблицы. Убедитесь в том, что пустых клеток вокруг нее достаточно для импорта данных, иначе действие завершится ошибкой и все равно придется стирать лишнее или выбирать другую ячейку. Напишите =IMPORTRANGE() для объявления соответствующей функции.
Перейдите к таблице, которую необходимо импортировать, и скопируйте код из ее ссылки. Можно скопировать и ссылку полностью, но в некоторых случаях она перестает работать, поэтому только код, расположенный между слешами (косыми линиями), является оптимальным вариантом.
Активируйте курсор между скобками функции и вставьте скопированный код в кавычках. Кавычки добавлять обязательно, поскольку эта информация является текстовой.
Поставьте точку с запятой и снова добавьте кавычки.
В них укажите название листа для импорта, если в таблице их более одного, в конце обязательно добавьте знак !, который нужен для соблюдения синтаксиса.
Проверьте название листа в оригинале таблицы, поскольку нужно учитывать и регистр букв, и все наличествующие пробелы.
Далее определитесь с тем, какой диапазон данных хотите импортировать.
Затем после восклицательного знака введите этот диапазон в формате A1:B4, где A1 - первая необходимая клетка сверху, а B4 – последняя справа снизу.
Нажмите Enter и дождитесь загрузки содержимого. Как видно, импорт прошел успешно, а в первой строке таблицы находится вся функция, которую можно редактировать, если это понадобится.
Вот полный пример написания функции, чтобы у вас не возникло никаких трудностей при ознакомлении с шагами инструкции:
Как уже было сказано ранее, все изменения, вносимые в оригинале, будут показаны и на том листе, куда произошел импорт, поэтому редактирование содержимого не имеет смысла, ведь оно все равно обновится. Если доступ к файлу будет закрыт, появится соответствующее уведомление об ошибке и весь диапазон пропадет.
Способ 2: Импорт через меню «Файл»
Быстро разберемся со вторым, простым методом импорта, который необходим для обычного копирования содержимого одной таблицы с переносом в другую. При этом изменения отслеживаться не будут, и вы можете редактировать значения и функции любым удобным для вас образом.
Откройте первую таблицу, вызовите меню «Файл» и в нем нажмите кнопку «Импортировать».
Отыщите существующую таблицу в предложенных вкладках или воспользуйтесь поиском.
Из выпадающего списка выберите подходящую цель для импорта. Вы можете создать новый файл, вставить листы в текущую таблицу или заменить ее. В некоторых случаях доступна вставка на текущий лист, если ячейки являются свободными.
Подтвердите импорт и дождитесь загрузки информации. Я выбрал вставку нового листа, поэтому вы видите, как он появился в текущей таблице. Любые ячейки можно скопировать и вставить в любое другое место с сохранением форматирования и функций.
Google предоставляет все необходимые функции для взаимодействия с разными таблицами, включая и быстрый импорт информации. Используйте один из двух методов, следуя инструкциям, и тогда не возникнет никаких трудностей.
Если вы параллельно работаете и с Excel-файлами, рекомендую к прочтению другой материал, связанный с переносом данных из Google Таблиц.
Читайте также: