Google-таблицы: получение данных с других веб-сайтов - узнаем курс рубля, температуру, последние новости, заголовки H1 и др. (+ несколько полезных примеров, которые "сложно" провернуть в Excel)

Автор: Alexandr, 16.02.2022 Рубрики: Офис

Google таблицыДоброго времени всем!

Сегодня будет небольшая заметка, посвященная офисным задачам - работе с табличками...

Excel, конечно, вещь хорошая, но для некоторых задач его не совсем удобно использовать (а то и вовсе невозможно). И в этом случае весьма неплохим выбором может стать Google-таблица (тем паче, что она бесплатна и некоторые вещи здесь можно решать без знания скриптов!).👌

Собственно, в этой небольшой заметке хочу привести несколько примеров работы с ней, которые могут быть весьма полезными для широкого круга читателей. Прим.: для тех, кто уже лет 5-10 "ковыряется" ежедневно с таблицами, боюсь, что ничего нового я здесь не открою...

Ремарка: чтобы создать свою первую "Гугл-табличку" — перейдите на сервис 📌Google Drive, зарегистрируйтесь (или войдите под своим логином), кликните правой кнопкой мыши по свободному месту диска, и выберите функцию "Создать таблицу". См. скрин ниже.

Создать пустую таблицу (Google Drive)

Создать пустую таблицу (Google Drive)

 

*

Примеры работ с Google-таблицей

Импорт в табличку нужных данных с сайтов

Итак...

Начать заметку я хочу с одной довольно замечательной формулы: IMPORTXML(ссылка; запрос-xpath). Она позволяет получать практически любые данных с открытых веб-страничек в сети Интернет. Например, вы можете подгрузить в свою таблицу:

*

📌 Как этим пользоваться (на примере):

  1. вставляете в одну из ячеек URL-адрес веб-странички, на которой есть нужная вам информация (например, я добавил в A1 адрес: https://rambler.ru/);
  2. во вторую ячейку (в моем случае A2) нужно добавить запрос Xpath (его можно скопировать в браузере, чуть ниже покажу как);
  3. теперь в одной из ячеек (например A3) используем формулу =IMPORTXML(A1;A2) и получаем в ней то, что было на указанной странице и пути Xpath! В своем примере я просто получил курс рубля к доллару с Рамблера (и, разумеется, дальше этот курс будет обновляться автоматически, и вы эту информацию можете использовать для расчетов).
  4. Удобно?!
Это число Google-таблица подгрузила автоматически (и будет это делать регулярно раз в час!)

Это число Google-таблица подгрузила автоматически (и будет это делать регулярно раз в час!)

 

📌 Примечание: как узнать путь Xpath (в моем примере //*[@id="main"]/div[2]/div/div/div/div/div[3]/div/div[2]/div/a[1]/span[2])

Для этого откройте веб-страницу в браузере Chrome и кликните правой кнопкой мыши по нужному элементу (цифре / заголовку и т.д.), и выберите в меню "Просмотреть код".

Затем появится консоль разработчика - в ней кликните еще раз ПКМ по строке, на которую курсор установится автоматически, и выберите в меню "Copy Xpath". Все, теперь этот элемент вы можете вставить в нужную вам ячейку таблицы (по Ctrl+V).

Получаем запрос Xpath в Chrome

Получаем запрос Xpath в Chrome

 

***

📌 Еще один пример: получение списка фильмов, музыки и прочих данных из однотипных блоков

Для примера я взял URL "Кинопоиска" со списком из самых новых фильмов. Но тут больше всего интересен будет Xpath запрос:

//div[@class='styles_main__2fFcM']

Чтобы повторить что-то подобное: вам нужно на веб-странице найти повторяющийся div-блок (например, в котором заключены заголовки фильмов) и посмотреть его класс (делается это через консоль разработчика в Chrome || аналогично, как мы сделали чуть выше). Далее заменить мой класс в примере на свой. В результате: получите в таблице целый список названий...

См. пару скринов ниже. 👇

Список новинок получен в табличку!

Список новинок получен в табличку!

Скриншот с сайта Kinopoisk — просмотр класса div блока

Скриншот с сайта Kinopoisk — просмотр класса div блока

 

*

Для работы с финансами (курсами валют, акциями и пр.)

Информация носит справочный характер. Не принимайте торговых решений на основе работы этой формулы (во всяком случае - перепроверяйте ее работу!). 

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

*

Для получения курсов более оперативно есть в "Гугл-таблицах" другая формула:

=GOOGLEFINANCE("CURRENCY:USDRUB")

См. ниже несколько примеров. Подставив свой тикер (или валюту) - вы сможете относительно-оперативно узнавать ее курс (а значит и пересчитывать свои значения в таблицах 😉👇).

Как работает формула GOOGLEFINANCE

Как работает формула GOOGLEFINANCE

 

 

*

Перевод текста с иностранного языка на русский

Допустим вы импортировали в табличку какие-нибудь данные с иностранных сайтов. И было бы неплохо теперь все эти строки перевести. В этом плане Google-таблица отлично решает проблему!

Пример:

  1. пишем формулу вида: =GOOGLETRANSLATE(A9;"en";"ru"). Вместо "en" и "ru" нужно указать язык оригинала и перевода, а A9 — это ячейка с иностранным текстом;
  2. далее просто "растягиваете" (копируете) формулу на все ячейки, напротив которых требуется перевод. См. пример ниже. 👇
Переводим все строки в таблице

Переводим все строки в таблице

 

 

*

Замена (или удаление) ненужных символов (автоматически)

Тоже довольно популярная операция. Мне импонирует ее выполнять с помощью регулярных выражений (это быстро, легко, и универсально).

Простой пример:

=REGEXREPLACE(A30 ;"[^0-9]+";"")

где A30 — ячейка с исходным текстом;

"[^0-9]+" — всё, что не относится к цифрам - обработать! (если бы было "[0-9]+" — обработать только цифры, остальное не трогать). Значок "^" - решает!

"" — если в кавычках ничего нет - менять любые символы, кроме цифр, будем на пустоту!

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

Регулярными выражениями очищаем строку

Регулярными выражениями очищаем строку

Кстати, для удаления лишних пробелов удобно также использовать формулу: =СЖПРОБЕЛЫ("text")

 

 

*

Совместная работа с документами

С документами на Google Drive могут одновременно работать неск. человек! Это очень удобно, если (скажем) в вашу табличку заносят данные 3-4 пользователя, а вы сразу же видите результат расчета (анализа) их данных.

Причем, для доступа к табличке другим пользователям - достаточно сделать правый клик мышки по файлу, и выбрать опцию "Открыть доступ" (или "Получить ссылку"). Затем можно либо дать доступ для конкретного пользователя, либо всем, у кого есть ссылка...

Кстати, с телефона документ тоже будет доступен! (и это большой плюс Google-таблице)

📌 В помощь (по теме)!

Совместная работа над документами (текстом, таблицами и пр.), или как создать общую папку в интернете — см. неск. способов

Получить ссылку на документ

Получить ссылку на документ

Пользователи видят, кто и что редактирует...

Пользователи видят, кто и что редактирует...

 

 

*

Вставка картинок с других сайтов

Если вам нужно вставить картинку в конкретную ячейку (и чтобы она из нее никуда не "делась") — воспользуйтесь формулой =IMAGE(). В скобках потребуется лишь указать ссылку на эту картинку.

Обратите внимание, что я привел два варианта: в одном размеры будут подобраны автоматически, во втором- указываются вручную! 👇

=IMAGE("https://ocomp.info/wp-content/themes/wikiwp/images/logo10-2.png")

=IMAGE("https://ocomp.info/wp-content/uploads/2021/09/Proverka-diska.png";4;100;150)

Второй вариант позволяет вручную задать размеры картинки: 100 - высота, 150 - ширина (см. мой пример выше).

Загрузка картинок в ячейки

Загрузка картинок в ячейки

 

*

Создание QR-кода

Тема-таки прям модная...

Делается это при помощи простой формулы:

=IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=100x100&chl="&A43)

Обратите внимание, на конце формулы стоит A43 — вместе нее нужно указать свою ячейку со ссылкой (текстом). Кстати, размер 100x100 - тоже можно поменять на нужный, например, 250x250.

📌 В помощь!

Как создать и читать QR-коды (штрих-коды) на Андроид (и как сделать оплату по QR-коду с телефона) — см. пошаговую инструкцию

Создание QR-кода в Гугл табличке

Создание QR-кода в Гугл табличке

 

*

Дополнения и комментарии по теме - приветствуются!

Успехов!

👋

Полезный софт:

  • видеомонтаж
  • Видео-Монтаж

  • Отличное ПО для создания своих первых видеороликов (все действия идут по шагам!).
    Видео сделает даже новичок!
  • утилита для оптимизации
  • Ускоритель компьютера

  • Программа для очистки Windows от "мусора" (удаляет временные файлы, ускоряет систему, оптимизирует реестр).

Написать комментарий

 

Прикрепить картинку, скрин:

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

  • Интересное
  • Оптимизируем и ускоряем
  • Удаляем вирусы
  • Настраиваем звук
  • Содержание статьи
  •