ФУНКЦИИ ИМПОРТА

IMPORTRANGE: ПЕРЕНОС ДАННЫХ ИЗ ОДНОГО ФАЙЛА В ДРУГОЙ

Для переноса данных из одного файла в другой в Google Таблицах используется функция IMPORTRANGE.

В каких случаях она может пригодиться?

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

Эта формула позволяет получить копию диапазона из другой Google Таблицы. Форматирование при этом не переносится — только данные.

Синтаксис формулы следующий:

IMPORTRANGE(spreadsheet key; range string)

IMPORTRANGE(ключ; диапазон)

spreadsheet_key (ключ) — последовательность символов атрибута key= (ключа) в ссылке на таблицу (после spreadsheets/…/). В новых Google Таблицах необходимо вставить ссылку полностью.

Пример формулы с ключом:

=IMPORTRANGE("abcd123abcd123";"sheet1!A1:C10")

Вместо ключа таблицы вы можете использовать полную ссылку на документ:

=IMPORTRANGE("https://docs.google.com/a/company_site.ru/spreadsheet/ccc?key=0A601pBdE1zIzHRxcGZFVT3hyVyWc";"Лист1!A1:CM500")

В вашем файле будет отображаться диапазон A1:CM500 с Листа1 из файла, который находится по соответствующей ссылке.

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

если будут добавляться строки:
Лист1!A1:CM
если будут добавляться столбцы:
Лист1!A1:1000

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

Так, если в ячейку A2 вы введете ссылку на документ, из которого нужно загрузить данные, а в ячейку B2 — ссылку на лист и диапазон, то импортировать данные можно будет с помощью следующей формулы:

=IMPORTRANGE(A2;B2)

Видеоурок по функции IMPORTRANGE вы найдете по ссылке:

ИМПОРТ ФОРМАТИРОВАНИЯ ИЗ ИСХОДНОЙ ТАБЛИЦЫ

Как мы уже заметили, IMPORTRANGE загружает только данные, но не форматирование исходной таблицы. Как с этим быть? Заранее «подготовить почву», скопировав форматирование из исходного листа. Для этого зайдите на исходный лист и скопируйте его в вашу книгу:

После нажатия кнопки Копировать в… выберите книгу, в которую будете импортировать данные. Обычно нужная таблица есть на вкладке Недавние (если вы действительно недавно работали с ней).

После копирования листа выделите все данные (нажав на левый верхний угол):

Нажмите Delete. Все данные исчезнут, а форматирование останется. Теперь можно ввести функцию IMPORTRANGE и получить полное соответствие исходного листа — как в части данных, так и в части формата:

FILTER: СБОР ДАННЫХ ИЗ РАЗНЫХ ФАЙЛОВ

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

Как быть?

Можно «подтянуть» данные на один лист с помощью IMPORTRANGE (для этого придется прописать несколько таких функций по количеству файлов — одна под другой через определенное количество строк (такое, чтобы хватило на все данные из каждого файла).
Размещайте импортируемые данные на одном листе с запасом в 5% строк (например, первую IMPORTRANGE в 1-й строке, вторую — в 1050-й).
Затем нужно создать отдельный сводный лист, на котором функция FILTER будет фильтровать данные из листа с функциями IMPORTRANGE, удаляя строки с пустыми значениями (это будут «запасные» строки между соседними файлами). Синтаксис у FILTER в данном случае такой:
=FILTER(лист с импортом!таблица; NOT(ISBLANK(‘лист с импортом’!контрольный столбец)); ’лист с импортом’!любой столбец<>"заголовок")

Первый аргумент — вкладка, на которую тянутся данные из всех файлов. Второй — проверка на отсутствие пустых ячеек (мы убираем «запасные» строки и склеиваем данные вместе). Третий аргумент исключает все заголовки из каждой импортированной таблицы. Для этого выберите любой столбец и вручную укажите после знаков <> его заголовок.

IMPORTHTML: ЗАГРУЗКА ТАБЛИЦ ИЗ ВЕБ-СТРАНИЦ

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

Синтаксис функции:

=IMPORTHTML(ссылка; запрос; индекс)

ссылка — ссылка на веб-страницу. Задается как текст в кавычках или как ссылка на ячейку с адресом;

запрос — это параметр table для таблиц и list для списков;

индекс — порядковый номер элемента веб-страницы (начиная с 1), данные из которого будут импортированы (нумерация для списков и таблиц на сайтах ведется независимо).

Попробуем загрузить данные по лидерам роста и падения с главной страницы FINAM. Функция будет выглядеть следующим образом:

=IMPORTHTML("http://www.finam.ru";"table";4)

Она выгрузит четвертую таблицу с сайта (то, что нужная таблица — четвертая, мы определили перебором):

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

IMPORTFEED: ЗАГРУЗКА ДАННЫХ ИЗ RSS-ФИДОВ

Функция IMPORTFEED позволяет загрузить на рабочий лист Google Таблиц данные из RSS-фида.

Ее первый и единственный главный аргумент — ссылка на фид (например, ). Как и в аналогичных функциях, ссылка либо берется в кавычки, либо вносится в ячейку, на которую вы потом ссылаетесь.

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

Второй аргумент — запрос — определяет, какие именно данные загружать.

Рассмотрим все возможные варианты.

1.-feed — только информация о фиде:

2.-feed title, feed description, feed URL или feed author — только один параметр фида (название, описание, ссылка или автор соответственно):

3.-items — то же, что и вариант по умолчанию — таблица со всеми элементами фида.

4.-items title (заголовки), items summary (содержимое элемента за исключением гиперссылок и изображений), items url (URL отдельных статей) или items created (дата создания статей):

Третий аргумент — «заголовки» — по умолчанию равен false, что означает отсутствие заголовков. Поправим на true, и в первой ячейке появится заголовок текущего параметра:

Последний аргумент — число объектов, которое вы хотите загрузить, начиная с последнего. Например, такая формула загрузит заголовки (items title) последних 10 статей:

=IMPORTFEED("http://blog.mann-ivanov-ferber.ru/feed/";"items title";true;10)

IMPORTXML

Функция IMPORTXML позволяет загружать данные из HTML-кода сайта с помощью языка запросов XPath.

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

Ее синтаксис:

=IMPORTXML(ссылка; запрос XPath)

Следующая функция загрузит все ссылки со страницы:

=IMPORTXML("http://shagabutdinov.ru";"//a/@href")

А эта — все заголовки уровня h2 (в случае с моим сайтом это будет список всех последних статей):

=IMPORTXML("http://shagabutdinov.ru";"//h2")

С помощью функции IMPORTXML можно получить курс валют ЦБ РФ. В примере — курс доллара. Чтобы получить курс другой валюты, поменяйте USD в формуле на код нужной вам валюты):

=IMPORTXML("http://www.cbr.ru/scripts/XML_daily.asp";"// *[*=""USD""]/Value")

(источник: )

Биржевой курс можно получить с помощью функции GOOGLEFINANCE. О ней мы поговорим позднее.

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

Вики:

The ImportXML Guide for Google Docs:

Примеры xpath-запросов к html:

IMPORTXML: ЗАГРУЖАЕМ ИЗОБРАЖЕНИЯ С ВЕБ-СТРАНИЦЫ

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

Первый аргумент функции IMPORTXML — ссылка на сайт. Второй — запрос, который для изображений выглядит так:

"//img/@src".

Кроме того, сразу отобразим изображения в ячейках Google Таблицы. Для этого IMPORTXML используем как аргумент функции IMAGE в массиве — внутри функции ARRAYFORMULA.

Таким образом, следующая формула сразу выведет все изображения с сайта без промежуточных вычислений:

=ARRAYFORMULA(IMAGE(IMPORTXML(ссылка_на_сайт;"//img/@src")))

IMPORTDATA: ЗАГРУЗКА ИНФОРМАЦИИ ИЗ CSV-ФАЙЛОВ

Функция IMPORTDATA загружает данные из файла в формате CSV (разделенные запятой) или в формате TSV (разделенные табуляцией). Единственный аргумент — ссылка на файл (в кавычках) или на ячейку, в которой стоит ссылка на файл.

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

О том, как разделять данные по столбцам, смотрите выше в разделе .

Если же вам нужно загрузить данные из CSV-файла, выложенного в Сети, пользуйтесь формулой IMPORTDATA. На примере мы загружаем данные из образца CSV-файла от Microsoft с адресной книгой:

Назад: Логические функции

Дальше: HYPERLINK (ГИПЕРССЫЛКА)