Бизнесхак на каждый день. Экономьте время, деньги и силы - Ренат Шагабутдинов Страница 42

Результатом работы этой формулы будет ссылка. При выделении ячейки с ссылкой сам адрес появится во всплывающей ячейке:

Функцией можно воспользоваться, чтобы получить сразу много ссылок на разные объекты, не вводя их вручную. Например, нам нужно получить ссылки на большое количество книг МИФа – по списку, имеющемуся в таблице.

Для начала зайдем на сайт, введем название любой книги (или текст «Название книги», как в примере) в поиск и заберем ссылку из адресной строки:

Уберем все, что после знака «равно» (после него мы будем добавлять в формулу название книги из ячеек первого столбца):

=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. И если язык английский, то выдает ссылку на поиск этого названия в Амазоне, а иначе (формально – если текст не на английском, по факту это означает русский в моем файле) на поиск его же в Озоне.

Для переноса данных из одного файла в другой в 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)

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

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

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

Искомое – то значение, которое мы будем искать в другой таблице. Важно отметить: если значение встречается несколько раз, функция найдет первое вхождение – и подтянет данные из соответствующей ему строки таблицы.

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

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

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

Аргумент «Номер столбца» определяет, из какого столбца ТАБЛИЦЫ (а не листа – это важно!) вы будете брать данные.

Интервальный_просмотр обычно равен нулю – в таком случае будет вестись точный, а не приблизительный поиск.

Пример:

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

Примечание. Если исходная таблица в будущем расширится, указывайте диапазон не как в примере – $A$2:$C$13, а без строк – $A:$C. Иначе уже из 14-й строки, когда она добавится, формула не будет подтягивать данные.

ФУНКЦИЯ MATCH (СРАВНЕНИЕ ДВУХ СПИСКОВ)

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

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

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

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