Результатом работы этой формулы будет ссылка. При выделении ячейки с ссылкой сам адрес появится во всплывающей ячейке:
Функцией можно воспользоваться, чтобы получить сразу много ссылок на разные объекты, не вводя их вручную. Например, нам нужно получить ссылки на большое количество книг МИФа – по списку, имеющемуся в таблице.
Для начала зайдем на сайт, введем название любой книги (или текст «Название книги», как в примере) в поиск и заберем ссылку из адресной строки:
Уберем все, что после знака «равно» (после него мы будем добавлять в формулу название книги из ячеек первого столбца):
=HYPERLINK("http://www.mann-ivanov-ferber.ru/book/search?query="&A2;A2).
Первый аргумент в этой формуле – это ссылка на поиск на сайте с прикрепленным названием книги из ячейки A2. Второй – само название книги из той же ячейки (чтобы в ней отображалось название, а не текст ссылки). Протягиваем формулу на весь столбец и получаем ссылки на все книги из списка:
Функцию HYPERLINK можно использовать в связи с другими функциями. Например, я использую ее с IF и DETECTLANGUAGE (подробнее о последней вы сможете прочитать в разделе «Переводим текст прямо в Google Таблице»):
=HYPERLINK(IF(DETECTLANGUAGE(A2)="EN";"https://www.amazon.com/s/url=search-alias%3Daps&field-keywords="&A2;"http://www.ozon.ru/?context=search&text="&A2)).
=HYPERLINK(IF(DETECTLANGUAGE(A2)="EN";ссылка на англ. сайт"&A2;"ссылка на рус. сайт"&A2)).
Эта формула проверяет, на каком языке указано название книги в ячейке A2. И если язык английский, то выдает ссылку на поиск этого названия в Амазоне, а иначе (формально – если текст не на английском, по факту это означает русский в моем файле) на поиск его же в Озоне.
Функция Importrange (перенос данных из файла в файл)
Для переноса данных из одного файла в другой в Google Таблицах используется функция IMPORTRANGE.
Для чего она может, например, пригодиться?
• Вам нужны актуальные данные из файла ваших коллег.
• Вы хотите обрабатывать данные из файла, к которому у вас есть доступ «Только для просмотра».
• Вы хотите собрать в одном документе таблицы из нескольких и вместе их обрабатывать или просматривать.
Эта формула позволяет получить копию диапазона из другой Google Таблицы. Форматирование при этом не переносится – только данные.
Синтаксис формулы следующий:
IMPORTRANGE(spreadsheet_key; range_string)
spreadsheet_key (ключ_таблицы) последовательность символов в атрибуте «key=» (ключ) в ссылке на таблицу. В новых Google Таблицах необходимо вставить ссылку полностью. Иначе говоря, ключ таблицы – это длинная последовательность символов в конце ссылки на таблицу после «spreadsheets/…/».
Пример формулы:
=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)
Функция Vlookup (перенос данных из таблицы в таблицу)
Эта функция – аналог функции ВПР в Excel. Она необходима для переноса данных из одной таблицы в другую.
У нее следующие аргументы:
VLOOKUP (искомое значение; таблица; номер столбца; интервальный_просмотр)
Искомое – то значение, которое мы будем искать в другой таблице. Важно отметить: если значение встречается несколько раз, функция найдет первое вхождение – и подтянет данные из соответствующей ему строки таблицы.
Например, в отдельном файле у вас есть стандартная ставка перевода книг с английского – по трем категориям сложности.
А в таблице с текущими проектами вы просто указываете категорию сложности (это и будет искомое значение), по которому функцией ВПР (VLOOKUP) подтягиваете ставку.
Таблица – это диапазон данных, из которого вы подтягиваете информацию.
Аргумент «Номер столбца» определяет, из какого столбца ТАБЛИЦЫ (а не листа – это важно!) вы будете брать данные.
Интервальный_просмотр обычно равен нулю – в таком случае будет вестись точный, а не приблизительный поиск.
Пример:
В примере мы подтягиваем тематику книги по ее названию из искомой таблицы.
Примечание. Если исходная таблица в будущем расширится, указывайте диапазон не как в примере – $A$2:$C$13, а без строк – $A:$C. Иначе уже из 14-й строки, когда она добавится, формула не будет подтягивать данные.
ФУНКЦИЯ MATCH (СРАВНЕНИЕ ДВУХ СПИСКОВ)
Функция ПОИСКПОЗ (в английской версии Excel и в Google Таблицах она называется MATCH) позволяет определить порядковый номер элемента (обычно текста, записанного в ячейке) в определенном списке.
Эта функция очень удобна для быстрого сравнения двух списков: очевидно, что если она не может найти порядковый номер какого-то элемента из первого списка во втором списке, то его там просто нет.
Таким образом, вы можете быстро понять, какие элементы одного списка отсутствуют во втором.
Синтаксис функции следующий:
