Несколько полезных функций 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 Таблиц
Как выделить уникальные элементы из списка?
Вставляем в ячейки Google Таблиц изображения
Переводим текст прямо в Google Таблицах
Функция QUERY в Google Таблицах
Функции Google Таблиц для работы с датой и временем
Ренат, использую функцию IMPORTRANGE как вы описали и как в Помощи написано, но выдается ERROR http://imageshack.com/i/pmtZRUkIp. Можете подсказать в чем может быть проблема? Спасибо.
Константин, сможете на почту кинуть ссылку на файл? Вообще нужно попробовать добавить апостроф перед и после названия листа во втором аргументе и проверить, что все номера столбцов набраны на латинице
У тебя стоит запятая, а нужна точка запятая в формуле.
Отличная статья, понятно и просто. Именно "IMPORTRANGE" искал. Большое спасибо!
Долго искал как получить данные из нескольких Google таблиц. Функция IMPORTRANGE, как раз то, что надо. Огромное Спасибо.
Ренат, а не подскажете, есть ли в Гугл Таблицах аналог функции "Вставить связь" из Экселя?
Роман, насколько я знаю, нет. В Таблицах связываем их через IMPORTRANGE.
Спасибо, помогло в решении вопроса!
здравствуйте Ренат. как это суммарный часть сложить. и как чтобы ячейки добав.автоматически формула изменилась
https://docs.google.com/spreadsheets/d/1_lna2_n-h806bF8EwYQ4tSEA26gaMQGDUR8DziC5LoQ/edit?usp=sharing
Добрый день. Не очень понял вопрос. Вообще для сложения нужно использовать функцию СУММ (SUM)
Помогите, пожалуйста. Не могу нигде добиться ответа. Когда скачала программу на телефон все отлично работало. Создала таблицы, вставила простейшие функции (сумма, разность, произведение). Спустя месяц надо было создать новую таблицу. Создала. Но ни одна функция не раьотает. Пишет value. Аргумент поддерживает только значение число. "1,4" это текст. Те все мои цифры в ячейках прога рассматривает как текст, а не число. Я попыталась даже изменить формат ячейки на число. Бесполезно. Тоже самое и на других страницах, в других таблицах, даже тех, что раньше работали. В чем проблема???
Помогите советом ??
Может быть, попробовать другой разделитель разрядов? 1.4 вместо 1,4?
Это в Excel или в Google Таблицах у вас?
Пробовала:( без результата. Даже цифры набирала не только на основной клавиатуре, но и в Меню функций. Всё равно видит цифры как текст. Программу сносила и устанавливала заново
Проблема только в Гугл таблицах iOS с телефона.
Нужно сделать в таблице google следующее:
Есть единица измерения (мл) допустим 1 мл стоит 30 рублей, в ячейке указано 120 мл. как сделать чтобы автоматически выводилась сумму за 120 мл? если такое вообще возможно
Дмитрий, да, возможно. Если ячейка со стоимостью (30руб) по адресу A1, а ячейка с объемом (120) - B1, то формула:
=A1*B1
Добрый день!
Возможно ли в формуле VLOOCUP в интервальном просмотре указать ссылку на другую таблицу гугл? И как это сделать?
Спасибо!
Илья, добрый день. Надо сначала подтянуть данные из другой таблицы Гугл с помощью функции ImportRange.
Здравствуйте!
Не могу понять, как можно заменить формулу сложения данных из одной ячейки диапазона листов, идущих подряд:
=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)
Но в гугль-таблице она не работает.
Ева, добрый день!
Действительно, такая форма записи в Таблицах не работает.
Можно попробовать пошаманить с формулами массива и INDIRECT'ом (ДВССЫЛ), где будут меняться номера листов в функции ADDRESS внутри INDIRECT.
Если найдется решение проще - напишу сюда.
Добрый день!
У меня вопрос, как связать excel и google sheets?
Можно загружать файлы Excel (импортировать или просто копировать данные)
Связать функциями или связью - нельзя
Добрый день, Ренат.
Только установила Google таблицы и уже сломала голову:))
Вставляю функцию суммирования через вставку функция, выдается сообщение - Неизвестная функция "СУММ".
Если меняю СУММ на SUM, то все работает.
В чем проблема? Пожалуйста, подскажите.
В Таблицах по умолчанию функции на английском.
Можно поменять в настройках :
http://prntscr.com/d0u5ea
Ренат, ссылки на документы битые 🙁
Тахир, привет.
Ты имеешь в виду гугл таблицу из примера?
Ну это ведь просто пример функции со ссылкой.
Доброго времяни суток.Подскажите какой формулой воспользоватся чтобы данные （постоянно меняющиеся）с одного листа автоматически меребрасывалось на другой лист.Например первый лист “итоги”,там приход,расход и остача.Второй лист “приход” но расписаны детально,третий лист “расход”,тоже расписан детально.Задача со второго и третего листа автоматически копировать данные на первый.
Анна, добрый день. С помощью QUERY или FILTER. Но нужно смотреть детальнее. Из текущего описания не очень понятно, что именно нужно сделать
Пропала вкладка меню в верху листа в таблицах spreadsheets. Как ее можно отобразить обратно?
Спасибо.
Попробуйте Ctrl+Shift+F
Добрый день, Ренат! Не могу найти решения следующей задачи. Есть таблица Google Spreadsheets с текстовыми и числовыми данными (это меняющийся периодически прайс, но будем использовать только числа из таблицы) и из этой таблицы необходимо "выдёргивать" значения данных отдельно по ячейкам и вставлять их в заранее определенные разные места на сайте. Каким образом это лучше сделать? Вставлять на сайт всю таблицу или её страницу с названием и кучей дополнительной информации не надо, только цена.
Ренат, спасибо за статью. Видео особенно помогло.
Есть вопрос. Например, при помощи функции IMPORTRANGE мы подтянули данные из Таблицы 1 в Таблицу 2. Связь работает и все, вроде бы, хорошо. Но есть потребность сделать так, чтобы в Таблице 2 можно было править подтянутые данные. Было бы идеально, если бы эти изменения синхронизировались с "материнской" Таблицей 1.
Есть решение такой задачи? Спасибо.
Дмитрий, к сожалению, такая связь в две стороны не предусмотрена.
Если только придумывать какое-то решение через скрипты. Но боюсь, что:
- оно будет сложным;
- таблицы начнут из-за этого сильно тормозить.
Спасибо Вам огроооомное! :))) искала штуку которая выглядит как сочетание функций index и match, даже сама разобралась и свои же ошибки в формуле (сначала не закрепила столбцы, потом закрепила всё, а ячейку там не нужно закреплять %))... В общем, нашлась нужная формулка, спасибо ещё раз 🙂
Добрый день, подскажите как подтянуть данные с помощью ВПР из одной таблицы в другую?
Когда выбираю диапазон в 2-й таблице функция не видит ее.
Алина, добрый день! Без скриншота / ссылки / формулы сложно что-то определенное ответить.
Добрый!
Как скопировать закрашенную ячейку в другую таблицу?
При помощи importrange переносятся только данные из ячейки, а очень нужно, что бы эти данные были окрашены также как и в исходном файле.
Олег, добрый день! Увы, функциями никак. Только копированием и специальной вставкой (вставить только формат)
Огромное спасибо, вы только что сэкономили мне полчаса в день)
Здравствуйте, подскажите, использую функцию importrange для 30 таблиц с одной чтобы импортировали основные данные, из которых пойдет выпадающий список. Но когда большая часть людей зашли в личные таблицы, все зависло, и не дало заполнять. Подскажите, есть ли аналог расширение какое-нибудь? Буду благодарна
Добрый день. А возможно ли в формулу ВПР добавить дату, чтоб она считала?
Спасибо огромное!