Обложка канала

Google Таблицы. Страница 28

17221 @google_sheets
Открыть

#GoogleТаблицы — это аналог #Excel с веб-интерфейсом и возможностями для удобной совместной работы. Они похожи, и все же в Таблицах есть свои особенности, функции, плюсы и минусы. Работать в Google Таблицах просто и мы покажем как.

  • Google Таблицы

    Сапер и морской бой в Таблицах
    Уже пошли игры

    Друзья, вокруг нашего канала собирается сообщество людей, которые по-настоящему увлечены Таблицами и всем, что в них можно сделать. Поэтому сегодня мы выкладываем целых две игры — Сапёр и Морской Бой, их написал наш подписчик Лео. Он прошёл путь от «ночью заснуть не мог, думал-думал, но не придумал» до «ловите парни, я сделал».

    Сапёр
    Морской бой

  • Google Таблицы

    Пока что наш лучший скрипт для сбора. С интерфейсом для ввода.

    Друзья, если вам нужно регулярно собирать десятки тысяч строк из Таблиц, укладываясь в 6 минут (лимит времени работы одного скрипта) — вам сюда.

    Просто скопируйте Таблицу себе и укажите в диапазоне «A2:C7» ссылки на Таблицы, листы и номера столбцов, которые нужно скопировать.

    После запука скрипт в цикле пробежится по Таблицам, соберёт данные в массив и вставит их начиная с десятой строки.

    Самое главное — вы можете поставить скрипт на регулярное выполнение и собирать данные каждую ночь, раз в два часа, etc. Для этого создайте триггер: нужно открыть редактор скриптов, меню изменить > триггеры текущего проекта, добавить триггер и поставить функцию get_google_sheets на нужную периодичность выполнения.

    После этого скрипт сможет собирать тысяч строк из указанных Таблиц, даже если ваш компьютер в это время будет выключен. 🚀😈

    >> Таблица со скриптом

    Будут вопросы, пишите в наш чат: @google_spreadsheets_chat

  • Google Таблицы

    Объединяем любое количество диапазонов Таблицы в одной функции.

    Друзья, ДВССЫЛ / INDIRECT в массиве не работает, но очень часто вам нужно сослаться на ряд ячеек с диапазонами и собрать их в один массив. Например, у вас в Таблице двенадцать листов с расходами за разные месяца и вы хотите собрать все строки этих листов с помощью одной формулы, просто выделив диапазоны мышкой.

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

    Чтобы функция работала в вашей Таблице - скопируйте код и вставьте его в редактор скриптов этой Таблицы.

    Функция отдельно:
    function indirects(ranges){
    var ss = SpreadsheetApp
    .getActiveSpreadsheet();

    if(ranges.map){
    return [].concat.apply([], ranges.map(function(i){return ss.getRange(i).getValues()}));}
    else{return ss.getRange(ranges).getValues();}
    }

    Таблица с примером

  • Реклама

  • Google Таблицы

    Курс по Excel и Google Таблицам "С нуля до PRO"

    Изучите все возможности Excel и Google Таблиц и увеличите скорость работы.

    На этом курсе вы сможете освоить Таблицы на базовом и продвинутом уровне - от основ работы с формулами до всех кляуз QUERY и записи макросов.
    И отработать новые навыки, ведь курс - это не только десятки видео с примерами, но и домашние задания.

    Вторая половина курса, посвященная Excel, тоже содержит все необходимые аспекты - от основ до программирования на VBA.

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

    Всем, кто приобретет курс в январе — скидка 40%.
    Зарегистрироваться на курс можно по ссылке.

    #реклама

  • Google Таблицы

    Видеоурок: спарклайны в Google Таблицах. Синтаксис и примеры

    Друзья, привет.
    Сегодня вашему вниманию предлагаем видеоурок про спарклайны.

    В видео учимся строить спарклайны, задавать их параметры в ячейках рабочего листа и в массиве внутри функции SPARKLINE, рассматриваем несколько примеров:
    - Динамика выручки
    - Анализ структуры продаж
    - Позиционное сравнение нескольких товаров
    - Визуализация выполнения плана: базовый уровень и превышение плана
    - Строим график динамики котировок акций одной формулой

    https://www.youtube.com/watch?v=ijkGAXCWaKU
    Спарклайны в Google Таблицах. Синтаксис и примеры

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

    YouTube

  • Google Таблицы

  • Google Таблицы

    Пользовательские форматы чисел

    Друзья, привет! С наступившим Новым годом!

    Возвращаемся в рабочий режим. И оформляем наши таблицы наглядно! Для этого мы собрали в одну статью всю информацию про пользовательские форматы чисел:
    — где они настраиваются
    — какие символы используются в пользовательских числовых форматах
    — как настроить отдельные форматы для положительных и отрицательных чисел, нуля и текста
    — как добавить цвет в форматы
    — как добавить условия в пользовательских форматах - и какие там есть ограничения
    — пользовательские форматы для номеров телефонов
    — округление до тысяч и миллионов с помощью формата
    — выравнивание по десятичной запятой
    — заполнение ячейки определенным символом
    — функция ТЕКСТ
    — пользовательские форматы даты и времени

    https://shagabutdinov.ru/custom_format/

  • Google Таблицы

    Дорогие читатели! Сегодня мы хотим поблагодарить вас за внимание к нашему каналу. Надеемся, он был полезен вам в работе в этом году.

    И отдельное спасибо тем, кто присылал нам свои решения, мы начислили вам +10 кармы в чате за каждый опубликованный пост:

    @mike59 (+30 кармы), целых три поста:
    — Изменяем наш Google Calendar с помощью скриптов
    — Скрипт для работы с Google Календарем. Напоминалка про дни рождения
    — Таблица для учета своих активов

    @mildly_disastrous (+20 кармы):
    — Прочитаем, что написано на стикерах. OCR в Google Docs
    — Важный скрипт. Связанные выпадающие списки из кэша
    — Перемещение мебели

    @IT_sAdmin (+10 кармы):
    — Вывод названия месяца из даты в именительном падеже

    @IT_sAdmin, @mrykin, @avazbek88, Макс Махров (всем по +10)
    — База знаний регулярных выражений для REGEXEXTRACT

    @akunilovskiy (+10)
    — Планировщик для занятий в тренажерном зале

    Друзья, будут новые идеи — пишите @namokonov, @renat_shagabutdinov

  • Google Таблицы

    Превращаем Таблицы в полноценное приложение:

    Планировщик для занятий в тренажерном зале от нашего подписчика

    Друзья, сегодня отличный пример того, что на базе бесплатных Google Таблиц можно собрать что угодно. Ниже — пост от нашего подписчика, он сделал планировщик для занятий в тренажёрном зале. Занятия заносятся из связанного выпадающего списка по группам, а на листе «как выполнять» можно посмотреть фото и видео выбранного занятия, они будут загружаться с помощью прекрасной функции =IMPORTXML.

    Передаем слово @akunilovskiy:
    Те, кто хотят добиться результатов в тренажерном зале знают, что тренировки нужно планировать, а результаты записывать. Когда я решил делать это с помощью смартфона я попробовал несколько приложений для Android, но ни одно не показалось удобным.
    Мне нужны были: легко доступная история тренировок, быстрое составление плана тренировки (в т.ч. на основе предыдущих) и, желательно, не только посредством экрана мобильного устройства, широкий выбор упражнений или возможность добавлять свои.
    В итоге я сделал журнал/планировщик тренировок с нужным мне функционалом на базе Google Таблиц.
    Поскольку результат меня устраивает, то я решил им поделиться с подписчиками канала и чата Google Таблицы.
    Если при использовании возникнут вопросы, предложения - можете задать их в группе: https://t.me/workoutplanner
    1. Текущая версия планировщика/журнала тренировок.
    2. Инструкция.

  • Google Таблицы

    Скрипт: создаем каталог из файлов выбранной папки в Google Таблице. Google Drive Api.

    Как работает:
    1) вам нужно сделать копию таблицы
    2) ввести в ячейку «B1» ID папки
    3) запустить из меню [ с к р и п т ы ] единственный скрипт

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

  • Google Таблицы

    Пользовательские форматы: округление до тысяч и миллионов

    Продолжим исследовать бездонный мир пользовательских форматов и сегодня поговорим про округление.

    Достаточно одной запятой в конце числового формата, чтобы округлить число до тысяч:
    0,

    Если хочется отображать в тысячах с десятыми/сотыми/прочими долями тысячи, можно добавить знаки после запятой:
    0.00,

    Сработает округление и для форматов с разделителями разрядов:
    #,##0,
    (То есть мы будем показывать число в тысячах, но если округленный результат будет иметь больше 10 тыс. - то в нем появятся разделители разрядов)

    Можно добавить надпись после числа - чтобы не запутаться и точно понимать, что здесь числа в тысячах:
    #,##0, тыс

    Помня о том, что мы можем задавать отдельные форматы (через точку с запятой) для положительных и отрицательных чисел, можно задать формат с округлением для положительных чисел, а отрицательные показывать в обычном формате:
    #,##0,;#,##0

    И, конечно, можно пойти дальше и отображать число в миллионах или миллиардах:
    #,##0,, - миллионы с разделителями разрядов
    0,,, - миллиарды без разделителей разрядов

    Файл с примерами

  • Google Таблицы

    Курс по Excel и Google Таблицам "С нуля до PRO"

    Изучите все возможности Excel и Google Таблиц и увеличите скорость работы.

    На этом курсе вы сможете освоить Таблицы на базовом и продвинутом уровне - от основ работы с формулами до всех кляуз QUERY и записи макросов.
    И отработать новые навыки, ведь курс - это не только десятки видео с примерами, но и домашние задания.

    Вторая половина курса, посвященная Excel, тоже содержит все необходимые аспекты - от основ до программирования на VBA.

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

    До конца декабря действует скидка 50% на стоимость курса.
    Зарегистрироваться на курс можно по ссылке.

    #реклама

  • Google Таблицы

    Сегодня два кейса.

    У нас есть ряд чисел от 0 до 1231, как посчитать одной формулой количество чётных элементов?

    Разбор — на скриншоте и в Таблице.

    Итоговая формула:
    =COUNTIF( ARRAYFORMULA( ISEVEN(SEQUENCE(1232;1;0)));true)

    Мы выбрали любой месяц, как посчитать в нем количество суббот?

    Разбор — на скриншоте и в Таблице.

    Итоговая формула:
    =COUNTIF( ARRAYFORMULA( WEEKDAY(SEQUENCE(DAY( EOMONTH(I1241;0) );1;I1241);2))

    Таблица с примером

  • Google Таблицы

    Добавляем к QUERY итоги

    Функция QUERY не умеет добавлять итоги по строкам / столбцам к данным, которые выводятся.

    Зато, вы можете написать несколько QUERY: одну для вывода запроса, другие для вывода итогов и объединить все эти функции в один массив.

    Подробно про объединение массивов мы писали здесь.

    В результате вы получите одну формулу и эта формула будет выводить всё.

    Примеры формул, QUERY с итогами по столбцам:
    ={ QUERY(B3:D11;"SELECT B, SUM(D) GROUP BY B PIVOT C");
    TRANSPOSE( QUERY(B3:D11;"SELECT SUM(D) GROUP BY C"))     }

    QUERY с итогами по столбцам, строкам и общей суммой:
    ={
    { QUERY(B3:D11;"SELECT B, SUM(D) GROUP BY B PIVOT C");TRANSPOSE( QUERY(B3:D11;"SELECT SUM(D) GROUP BY C")) }\
    { QUERY(B3:D11;"SELECT SUM(D) GROUP BY B");QUERY(B3:D11;"SELECT SUM(D) label SUM(D) ''") }
    }

    Подробный разбор - на скриншоте.
    Таблица с примером

  • Google Таблицы

    Пользовательские форматы для номеров телефонов

    Чтобы отображать телефонные номера, введенные просто как последовательность цифр, в нормальном виде - нужны пользовательские форматы.
    Давайте посмотрим, как ими воспользоваться.

    Формат (Alt+O) -> Числа (N) -> Другие форматы - > Другие форматы чисел

    А дальше можно вводить формат. В самом простом виде просто вводим нули для цифр и знаки, которые хотим отображать:
    0(000)000-00-00

    Но такой формат не будет работать для номеров вида 9101234567, 1234567.
    Только для 79161234567 или 89161234567.

    Поэтому можно пойти дальше и вспомнить, что есть возможность задавать условия в пользовательских форматах.
    Так, можно предусмотреть отдельный формат для чисел меньше 9999999 - без кода:
    [<9999999]000-00-00;0(000)000-00-00

    Такой вариант корректно отобразит и номера из 11 цифр, и номера из 7.
    Но все еще не справится с номером из 10 цифр - как 9101234567.

    Так что можно сделать еще один шаг и прописать два условия: [<9999999]000-00-00; [<10000000000](000)000-00-00; 0(000)000-00-00
    Теперь у нас предусмотрены три варианта.

    Файл с примером

  • Реклама

  • Google Таблицы

    Решили в чате отличный кейс: добавляем ячейку с любым текстом после каждой ячейки нашего массива.

    В примере (таблица с ним) после каждой ячейки добавляем новую со словом «СТРОКА».

    Формула по шагам разобрана на скриншоте.

    Итоговая формула:
    =TRANSPOSE(SPLIT(JOIN("vasyaСТРОКАvasya";A5:A8);"vasya";0))

    Наш стремительный чат: @google_spreadsheets_chat,

    Спешите, в нём скоро закончится место, нас там почти 1500 🤗

  • Google Таблицы

    Налетай, скрипты покупай

    Наша артель уже четыре года пишет скрипты для Google Таблиц, Документов и Диска.

    С помощью скриптов и нашей магии можно сделать что угодно. ВОТ СОВСЕМ.

    Например, открываем Excel файл на вашем диске, превращаем в Таблицу, форматируем, конвертируем в текстовый файл, отправляем по почте, сохраняем на Google Диск и делаем post-запрос одновременно. Это реально и мы это делали.

    Что еще делали
    ✔ выгружали письма из gmail ящика и использовали их, чтобы создать в календаре события (Маша З, 30 ноября, делаем ноготочки)
    ✔ все виды конвертаций файлов на вашем диске
    ✔ реестр файлов диска и смена прав доступа к файлам через интерфейс Таблицы (уберите сотрудника из всех файлов разом)
    ✔ интеграции со внешними API, забираем остатки, продажи и вставляем в Таблицу

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

    Дорого. Эффективно.

    За скриптами писать сюда: @namokonov

  • Google Таблицы

    Друзья, подписчик нашего канала создал Таблицу для учета своих активов и написал про это статью.

    Вводная от меня. Основа его Таблицы — функция IMPORTXML, она позволяет вытаскивать почти любую строку из веб-страницы, вам нужно только научиться писать xpath запрос.

    Таким образом вы всегда сможете собирать нужные вам котировки и другую информацию без скриптов / интеграции с АПИ.

    Кстати, скриптами это делать даже сложнее — чтобы обычный parsexml из скрипта проглотил сайт, html нужно сначала обработать, экранировать экранируемое, с тегами порядок навести, с кавычками, etc.

    Так что разбирайтесь, как писать xpath и пользуйтесь этой мощной функцией.

    Статья: https://vc.ru/finance/92990-upravlencheskiy-uchet-lichnyh-aktivov
    Таблица с портфелем: https://clck.ru/K9CLT