в вишлисте
Личная скидка {{ profile.personalDiscount.discount }}%
в корзине
на сумму
До бесплатной доставки
осталось
{{ cartCount }}
Корзина
Доставим в город {{ headerCity.name }}
сегодня в течение  от {{ headerCity.estimatesMin }} до {{ headerCity.estimatesMax }} 
В город {{ headerCity.name }}
пока не доставляем
Посмотрите
другие города
Город, населенный пункт
{{ city.region }}
Сюда пока не доставляем книги
biznesxak6-1
Бизнес
Бизнесхак на каждый день. Выпуск №6. Полезные функции для работы в Google Таблицах
14.11.2016 Просмотров: 2 517
Бизнес
Бизнесхак на каждый день. Выпуск №6. Полезные функции для работы в Google Таблицах
14.11.2016 Просмотров: 2 517

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

Сегодня в рубрике «Бизнесхак на каждый день» — целый «урожай» бизнесхаков. Вы научитесь переводить текст на другой язык (или на свой) прямо в Google Таблицах, ускорять работу документов и делать их легче, выделять в них уникальные элементы и автоматически проставлять ссылки, быстро переводить данные из файла в файл (по этой теме есть даже видеоурок), а также сравнивать между собой два списка. Готовы? Тогда за дело.

Знакомимся с Google Docs

Основное преимущество Google Docs — возможность совместной работы в режиме онлайн, просмотра изменений, сделанных каждым участником, и автоматического сохранения актуальной версии. Особенно полезны для работы с данными Google Spreadsheets, или Google Таблицы — аналог приложения Excel.

Google Docs могут пригодиться вам в следующих целях:

biznesxak6-13

  • для совместного планирования отпуска с друзьями и расчета бюджета;

  • ведения реестров с вашими клиентами, заказами, поставщиками и т.д., которые заполняются несколькими людьми параллельно;

  • онлайн-координации любых совместных действий.

Все данные в примерах ниже вымышлены.

Как сделать документ Google Таблиц быстрее и «легче»

Есть разные способы это сделать. Попробуйте несколько, чтобы выбрать подходящие. Возможно, придется сделать все по очереди. Но результат того стоит. Итак, рецепты «ускорения и облегчения».

biznesxak6-14

  • Удалить неиспользуемые строки на каждой вкладке (по умолчанию создается тысяча строк — если у вас на вкладке сейчас используется 200, удалите лишние 800, а при необходимости просто добавьте нужное количество) и столбцы (аналогично).

  • Оптимизировать количество вкладок (если есть несколько вкладок с маленькими таблицами или списками — попробуйте объединить их в одну).

  • Если есть формулы поиска данных, например ВПР/VLOOKUP, ИНДЕКС/INDEX, ПОИСКПОЗ/MATCH и другие, попробуйте сохранить часть формул как значения (если не нужно будет эти значения обновлять). Например, если у вас подтягиваются данные за много месяцев с помощью VLOOKUP — оставляйте текущий месяц формулами, а остальные данные сохраняйте как значения.

  • Не заливать строки/столбцы цветом целиком (и вообще стараться избегать излишнего форматирования).

  • Проверить, нет ли условного форматирования на (излишне) большом диапазоне ячеек.

  • Не ставить фильтр на все столбцы.

  • Очистить примечания, если их много и они не нужны

  • Выяснить, нет ли проверки данных на большом диапазоне ячеек.

Как выделить уникальные элементы из списка в Google Таблицах?

Дано: список с текстовыми значениями в Google Таблице.

Задача — получить список, содержащий только уникальные значения из исходного.

Для этого нужна функция UNIQUE, единственный аргумент которой — исходный список:

biznesxak6-15

Если ваша задача — только вычислить количество уникальных элементов в списке, понадобится функция COUNTUNIQUE. Она работает аналогично, но возвращает лишь количество уникальных элементов.

Но что, если исходный список будет со временем меняться (то есть к нему станут добавляться новые строки)? Не менять же формулу каждый раз. Решение есть.

Чтобы функция UNIQUE автоматически обновляла список уникальных значений при обновлении исходного списка (а COUNTUNIQUE, соответственно, обновляла количество), в качестве аргумента укажите не диапазон A2:A14, а диапазон A2:A.

Ставим ссылки автоматически. Функция HYPERLINK (ГИПЕРССЫЛКА)

Функция HYPERLINK (ГИПЕРССЫЛКА) возвращает ссылку на страницу в сети. Ее первый аргумент — собственно ссылка (записывается в кавычках), второй — текст, который будет отображаться в ячейке (тоже в кавычках):

biznesxak6-5

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

biznesxak6-9

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

Например, нам нужно получить ссылки на большое количество книг МИФа — по списку, имеющемуся в таблице. Для начала зайдем на сайт, введем название любой книги (или текст «Название книги», как в примере) в поиск и заберем ссылку из адресной строки[u1] :

biznesxak6-7

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

=HYPERLINK(«http://www.mann-ivanov-ferber.ru/book/search?query=«&A2;A2).

Первый аргумент в этой формуле — это ссылка на поиск на сайте с прикрепленным названием книги из ячейки A2. Второй — само название книги из той же ячейки (чтобы в ней отображалось название, а не текст ссылки). Протягиваем формулу на весь столбец и получаем ссылки на все книги из списка:

biznesxak6-11

Функцию 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)).

biznesxak6-12
источник

Эта формула проверяет, на каком языке указано название книги в ячейке 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«)

biznesxak6-8
источник

В файл, в котором вы введете эту формулу, будет отображаться диапазон A1:CM500 с Листа 1 из файла, который находится по соответствующей ссылке.

Кроме того, ссылки на файл и диапазон можно вводить не в саму формулу, а в ячейки вашего документа и ссылаться на них. Так, если в ячейку A2 вы введете ссылку на документ, из которого нужно загрузить данные, а в ячейку B2 — ссылку на лист и диапазон, то загружать данные можно будет с помощью следующей формулы:

=IMPORTRANGE(A2;B2)

Функция MATCH (сравнение двух списков)

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

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

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

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

  • MATCH (искомое_значение; список; точный поиск);

  • искомое значение — то, что мы ищем (обычно ячейка с текстом), список — диапазон, в котором мы ищем. Последний аргумент должен равняться нулю, если вы хотите вести точный поиск (обычно именно это и нужно).

Пример:

biznesxak6-6

В примере ошибка #N/A возникает только в тех случаях, когда соответствующего элемента нет во втором списке.

Переводим текст прямо в Google Таблице: функция GOOGLETRANSLATE

В Google Таблицах есть функция, позволяющая переводить текст прямо в ячейках:

biznesxak6-3

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

GOOGLETRANSLATE(text,[source_language], [target_language])

  • text — это текст, который нужно переводить; можно, конечно, взять текст в кавычки и записать прямо в формулу, а можно сослаться на ячейку, в которой он содержится;

  • [source_language] — язык, с которого мы переводим;

  • [target_language] — соответственно, язык, на который мы переводим.

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

Обратите внимание, что оба аргумента необязательные — если их не указать, перевод будет осуществляться на английский. Язык исходного текста будет определяться автоматически:

biznesxak6-3

А как быть, если мы все-таки хотим переводить не только на английский, но и на другие языки? И при этом не хотим каждый раз указывать язык исходника вручную?

Тут пригодится функция DETECTLANGUAGE. У нее единственный аргумент — текст, язык которого нужно определить:

biznesxak6-10

Осталось ее «внедрить» в функцию TRANSLATE. Укажем справа от текста, на какие языки хотим переводить исходный текст (в столбце B). В столбец C введем формулу GOOGLETRANSLATE. Первым аргументом будет текст в столбце A, вторым — функция DETECTLANGUAGE, которая определит, с какого языка переводить, а третья — код языка из столбца B.

Как и с любой другой функцией, прелесть здесь в автоматизации. Можно быстро поменять текст или язык; быстро перевести одну фразу на десять языков и так дале. Конечно, мы понимаем, что это текст онлайн-переводчика — качество будет соответствующим. Quel merveilleux tableau!

Если вам показались полезными эти советы, еще больше их можно найти в новой книге Игоря Манна и Рената Шагабутдинова «Бизнесхак на каждый день». Следите за новыми выпусками рубрики. Как говорится, не переключайтесь.

Обложка поста: pexels

Рубрика
Бизнес