Несколько полезных функций Google Spreadsheets: IMPORTRANGE, VLOOKUP, MATCH, INDEX

Google Docs - это веб-версия приложений MS Office.

Основное преимущество Google Docs - возможность совместной работы в режиме "онлайн", просмотра изменений,
сделанных каждым участником, и автоматическим сохранением актуальной версии (не будет больше обидных вылетов и выключений компьютера без сохраненного текста или документа!).

Особенно полезны Google Spreadsheets, или Google таблицы - аналог приложения Excel.

Они могут пригодиться вам для:

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

Функция IMPORTRANGE

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

Для чего она может пригодиться?

Например, вам нужны актуальные данные из файла ваших коллег;

или вы хотите обрабатывать данные из файла, к которому у вас есть доступ "Только для просмотра";

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

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

Синтаксис функции следующий:

IMPORTRANGE(spreadsheet_key; range_string)

spreadsheet_key (ключ_таблицы) – последовательность символов в атрибуте "key=" (ключ) в ссылке на таблицу.

Иначе говоря, ключ таблицы - это последовательность символов в ссылке на таблицы после "spreadsheets/d/"

Пример функции:

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

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

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

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

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

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

=IMPORTRANGE(A2;B2)

Пример:


Видео:

IMPORTRANGE как аргумент другой функции

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

Рассмотрим простой пример - среднее значение по продажам из диапазона, находящегося в другом документе.

Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз)

Сначала импортируем этот диапазон:

IMPORTRANGE("https://docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4";"Книги!D2:D")

А потом используем это как аргумент функции СРЗНАЧ (AVERAGE):

=СРЗНАЧ(IMPORTRANGE("https://docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4";"Книги!D2:D"))

=AVERAGE(IMPORTRANGE("https://docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4";"Книги!D2:D"))

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

Функция ВПР (VLOOKUP)

Это функция - аналог функции ВПР в Excel. Она необходима для переноса данных из одной таблицы в другую.

У нее следующие аргументы:

VLOOKUP (искомое значение; таблица; номер столбца; интервальный_просмотр)

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

Например, в отдельном файле у вас есть стандартная ставка перевода книг с английского - по трем категориям сложности.

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

Таблица - это диапазон данных, из которого вы подтягиваете информацию.

Аргумент "номер столбца" определяет , из какого столбца ТАБЛИЦЫ (а не листа! это важно) вы будете брать данные.
Интервальный_просмотр обычно равен нулю - в таком случае будет вестись точный, а не приблизительный поиск.

Функция ПОИСКПОЗ (MATCH)

Функция ПОИСКЗПОЗ (в английской версии Excel и Google Таблиц она называется MATCH) позволяет определить порядковый номер элемента (обычно - текста, записанного в ячейке) в определенном списке.

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

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

Синтаксис функции следующий:

MATCH (искомое_значение; список; точный поиск)

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

Пример:

В примере ошибка #N/A возникает только в тех случаях, в которых соответствующего элемента нет во втором списке.

Сочетание функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH)

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

Но вы можете воспользоваться сочетанием функций ПОИСКПОЗ (MATCH - обсуждалась ранее) и ИНДЕКС (INDEX).

Функция ИНДЕКС возвращает элемент из списка по его порядковому номеру.

А порядковый номер вы определяете с помощью MATCH.

Пример:

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

Канал "Google Таблицы - это просто" в Телеграме

Регулярно публикуем новые кейсы и советы. Заглядывайте в гости:

https://telegram.me/google_sheets

Другие статьи о функциях Google Таблиц

Текстовые функции Google Таблиц

Функция OFFSET (СМЕЩ)

Как выделить уникальные элементы из списка?

Вставляем в ячейки Google Таблиц изображения

Переводим текст прямо в Google Таблицах

Функция QUERY в Google Таблицах

Функции Google Таблиц для работы с датой и временем

HYPERLINK: быстро ставим гиперссылки в Google Таблице

Делаем документы Google Таблиц и Excel легче и быстрее

Рубрики:
Комментарии
24 Цепочка комментария
18 Ответы по цепочке
0 Последователи
 
Популярнейший комментарий
Цепочка актуального комментария
26 Авторы комментариев
Константин

Ренат, использую функцию IMPORTRANGE как вы описали и как в Помощи написано, но выдается ERROR http://imageshack.com/i/pmtZRUkIp. Можете подсказать в чем может быть проблема? Спасибо.

Алекс

У тебя стоит запятая, а нужна точка запятая в формуле.

Дмитрий

Отличная статья, понятно и просто. Именно "IMPORTRANGE" искал. Большое спасибо!

Иван

Долго искал как получить данные из нескольких Google таблиц. Функция IMPORTRANGE, как раз то, что надо. Огромное Спасибо.

Роман

Ренат, а не подскажете, есть ли в Гугл Таблицах аналог функции "Вставить связь" из Экселя?

Степан

Спасибо, помогло в решении вопроса!

shuhkrat

здравствуйте Ренат. как это суммарный часть сложить. и как чтобы ячейки добав.автоматически формула изменилась
https://docs.google.com/spreadsheets/d/1_lna2_n-h806bF8EwYQ4tSEA26gaMQGDUR8DziC5LoQ/edit?usp=sharing

Ирина

Помогите, пожалуйста. Не могу нигде добиться ответа. Когда скачала программу на телефон все отлично работало. Создала таблицы, вставила простейшие функции (сумма, разность, произведение). Спустя месяц надо было создать новую таблицу. Создала. Но ни одна функция не раьотает. Пишет value. Аргумент поддерживает только значение число. "1,4" это текст. Те все мои цифры в ячейках прога рассматривает как текст, а не число. Я попыталась даже изменить формат ячейки на число. Бесполезно. Тоже самое и на других страницах, в других таблицах, даже тех, что раньше работали. В чем проблема???
Помогите советом ??

Дмитрий

Нужно сделать в таблице google следующее:
Есть единица измерения (мл) допустим 1 мл стоит 30 рублей, в ячейке указано 120 мл. как сделать чтобы автоматически выводилась сумму за 120 мл? если такое вообще возможно

Илья

Добрый день!
Возможно ли в формуле VLOOCUP в интервальном просмотре указать ссылку на другую таблицу гугл? И как это сделать?
Спасибо!

Ева

Здравствуйте!
Не могу понять, как можно заменить формулу сложения данных из одной ячейки диапазона листов, идущих подряд:
=SUM('01'!F15;'02'!F15;'03'!F15;'04'!F15;'05'!F15;'06'!F15;'07'!F15;'08'!F15;'09'!F15;'10'!F15;'11'!F15;'12'!F15;'13'!F15;'14'!F15;'15'!F15;'16'!F15;'17'!F15;'18'!F15;'19'!F15;'20'!F15;'21'!F15;'22'!F15;'23'!F15;'24'!F15;'25'!F15;'26'!F15;'27'!F15;'28'!F15;'29'!F15;'30'!F15;'31'!F15)
От 01 до 31 — имена листов.
F15 — имя ячейки.
В Excel эта формула была такая:
=СУММ('01:31'!F15:F15)
Но в гугль-таблице она не работает.

Баке

Добрый день!
У меня вопрос, как связать excel и google sheets?

Екатерина

Добрый день, Ренат.
Только установила Google таблицы и уже сломала голову:))
Вставляю функцию суммирования через вставку функция, выдается сообщение - Неизвестная функция "СУММ".
Если меняю СУММ на SUM, то все работает.
В чем проблема? Пожалуйста, подскажите.

Тахир

Ренат, ссылки на документы битые 🙁

Анна

Доброго времяни суток.Подскажите какой формулой воспользоватся чтобы данные (постоянно меняющиеся)с одного листа автоматически меребрасывалось на другой лист.Например первый лист “итоги”,там приход,расход и остача.Второй лист “приход” но расписаны детально,третий лист “расход”,тоже расписан детально.Задача со второго и третего листа автоматически копировать данные на первый.

Андрей

Пропала вкладка меню в верху листа в таблицах spreadsheets. Как ее можно отобразить обратно?
Спасибо.

Дмитрий

Добрый день, Ренат! Не могу найти решения следующей задачи. Есть таблица Google Spreadsheets с текстовыми и числовыми данными (это меняющийся периодически прайс, но будем использовать только числа из таблицы) и из этой таблицы необходимо "выдёргивать" значения данных отдельно по ячейкам и вставлять их в заранее определенные разные места на сайте. Каким образом это лучше сделать? Вставлять на сайт всю таблицу или её страницу с названием и кучей дополнительной информации не надо, только цена.

Дмитрий

Ренат, спасибо за статью. Видео особенно помогло.

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

Есть решение такой задачи? Спасибо.

Ренат Шагабутдинов

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

Анастасия

Спасибо Вам огроооомное! :))) искала штуку которая выглядит как сочетание функций index и match, даже сама разобралась и свои же ошибки в формуле (сначала не закрепила столбцы, потом закрепила всё, а ячейку там не нужно закреплять %))... В общем, нашлась нужная формулка, спасибо ещё раз 🙂

Алина

Добрый день, подскажите как подтянуть данные с помощью ВПР из одной таблицы в другую?
Когда выбираю диапазон в 2-й таблице функция не видит ее.

Ренат Шагабутдинов

Алина, добрый день! Без скриншота / ссылки / формулы сложно что-то определенное ответить.

Олег

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

Ренат Шагабутдинов

Олег, добрый день! Увы, функциями никак. Только копированием и специальной вставкой (вставить только формат)

Владислав

Огромное спасибо, вы только что сэкономили мне полчаса в день)

Энкира

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

Жака

Добрый день. А возможно ли в формулу ВПР добавить дату, чтоб она считала?

MvK

Спасибо огромное!

Темы, на которые я пишу: