Несколько полезных функций 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 как аргумент другой функции

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 легче и быстрее

Индивидуальные консультации и тренинги по Google Таблицам

  1. Константин says:

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

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

      Константин, сможете на почту кинуть ссылку на файл? Вообще нужно попробовать добавить апостроф перед и после названия листа во втором аргументе и проверить, что все номера столбцов набраны на латинице

  2. Дмитрий says:

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

  3. Иван says:

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

  4. Роман says:

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

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

      Роман, насколько я знаю, нет. В Таблицах связываем их через IMPORTRANGE.

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

      Добрый день. Не очень понял вопрос. Вообще для сложения нужно использовать функцию СУММ (SUM)

  5. Ирина says:

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

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

      Может быть, попробовать другой разделитель разрядов? 1.4 вместо 1,4?
      Это в Excel или в Google Таблицах у вас?

      • Ирина says:

        Пробовала:( без результата. Даже цифры набирала не только на основной клавиатуре, но и в Меню функций. Всё равно видит цифры как текст. Программу сносила и устанавливала заново

  6. Дмитрий says:

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

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

      Дмитрий, да, возможно. Если ячейка со стоимостью (30руб) по адресу A1, а ячейка с объемом (120) – B1, то формула:
      =A1*B1

  7. Илья says:

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

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

      Илья, добрый день. Надо сначала подтянуть данные из другой таблицы Гугл с помощью функции ImportRange.

  8. Здравствуйте!
    Не могу понять, как можно заменить формулу сложения данных из одной ячейки диапазона листов, идущих подряд:
    =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)
    Но в гугль-таблице она не работает.

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

      Ева, добрый день!
      Действительно, такая форма записи в Таблицах не работает.
      Можно попробовать пошаманить с формулами массива и INDIRECT’ом (ДВССЫЛ), где будут меняться номера листов в функции ADDRESS внутри INDIRECT.
      Если найдется решение проще – напишу сюда.

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

      Можно загружать файлы Excel (импортировать или просто копировать данные)
      Связать функциями или связью – нельзя

  9. Екатерина says:

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

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

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

  10. Анна says:

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

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

      Анна, добрый день. С помощью QUERY или FILTER. Но нужно смотреть детальнее. Из текущего описания не очень понятно, что именно нужно сделать

  11. Андрей says:

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

  12. Дмитрий says:

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

Оставьте комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

CAPTCHA image
*