Я поделюсь несколькими простыми, но очень полезными функциями, которые могут сохранить вам немало времени. В статье приводятся русские названия функций, чтобы вы могли воспользоваться ими и в 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 может быть аргументом другой функции, если диапазон, который вы импортируете, подходит на эту роль.
Рассмотрим простой пример — среднее значение по продажам из диапазона, находящегося в другом документе.
Сначала импортируем этот диапазон:
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.
Пример:
В примере мы подтягиваем тематику по названию книги, хотя названия находятся не в первом столбце искомой таблицы.