Видео для новичков-новичков: "умные" таблицы

Друзья, для тех, кому удобнее формат видео, ролик про таблицы (tables) в Google Spreadsheets:
https://www.youtube.com/watch?v=Pn_b_mpr__k

Ранее мы писали про основные опции таблиц и ссылки на данные в них тут

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

В чем преимущества?
— Новые строки будут автоматом попадать в сводную
— Пустые строки не будут попадать в нее и их не придется фильтровать (как если бы мы брали в качестве источника столбцы, например, A:F)
— Таблица может начинаться не с первой строки листа и над/под ней может быть что-то еще. А если мы берем столбцы в качестве источника сводной, то в них больше ничего, кроме данных для нее, быть не может.

Форматируем данные как таблицу, выделяем любую ячейку, вставляем сводную —> видим, что в качестве источника фигурирует не диапазон, а имя таблицы (де-факто именованный диапазон, меняющий свои размеры при добавлении новых данных).

Вечная схватка двух ёкодзун: Google Таблицы и Excel

Ну что, друзья, с появлением таблиц в Google Spreadsheets пришло время обновить таблицу-сравнение двух табличных редакторов, а заодно напомнить про нее и рассказать тем, кто пропустил.

Она живет по этой постоянной ссылке:
https://docs.google.com/spreadsheets/d/10jhUBPLhmtx-km5sdITumlmkgIn8DKqsdxyULChVaq8

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

Как ускорить работу в Google Таблицах: 7 лайфхаков

Статья в РБК для новичков табличных дел! Делюсь 7 лайфхаками для работы в Google Spreadsheets: форматирование, горячие клавиши для вызова команд из меню, работа с ссылками, сортировка, вычисление фрагмента, пользовательские числовые форматы.

https://companies.rbc.ru/news/qnC1EmgtQx/kak-uskorit-rabotu-v-google-tablitsah-7-lajfhakov/

Почему объединение ячеек — это не очень хорошо?

Потому что за объединенной ячейкой всегда скрывается только одна заполненная (левая верхняя, первая, попросту говоря) и все остальные пустые!

А значит, формулами мы это дело не обработаем: в примере ломается ВПР / VLOOKUP, потому что название курса берется только из первой из объединенных ячеек.

Что если вы повторили все названия, но визуально хотите сохранить эффект объединения?

Как вариант — придумать формулу условного форматирования, которая будет "прятать" (применять белый цвет шрифта) все "лишние", нецентральные подписи.

Сортируем данные по фамилии (или другому слову в составе текстовой строки)

Вот такой лайфхак от маэстро табличных дел Бена Коллинса.

Если в ячейках данные формата Имя Фамилия, а нам надо сортировать по фамилии, а не по всей текстовой строке (включая имя), то можно в функцию SORT засунуть текстовые функции, которые будут извлекать фамилию:

=SORT(диапазон ;RIGHT(диапазон ;LEN(диапазон )-SEARCH(" ";диапазон ));1)

Здесь мы определяем, сколько символов в фамилии (находим с помощью SEARCH / ПОИСК или FIND / НАЙТИ разделитель — пробел — и вычитаем его позицию из общего числа символов в строке (LEN / ДЛСТР).
Затем извлекаем соответствующее число символов с конца строки с помощью функции RIGHT / ПРАВСИМВ.

А как это делается в Excel с новыми функциями (в частности, TEXTAFTER / ТЕКСТПОСЛЕ), можно посмотреть здесь:
https://tttttt.me/lemur_excel/273

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

Загружаем расходы в Таблицу по рекламе ВБ, API

Друзья, привет! Мы подготовили для вас Таблицу со скриптом, с помощью нее вы сможете загружать расходы по рекламным кампаниям из API ваших любимых диких ягод.

Копируйте Таблицу, вставляйте даты на первый лист (максимальный диапазон для загрузки - 31 день), вставляйте API-токен и запускайте скрипт из меню.

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

Также мы расшифровываем статус кампании и тип кампании.

Описание метода API: https://openapi.wildberries.ru/promotion/api/ru/#tag/Finansy/paths/~1adv~1v1~1upd/get

Таблица с кодом

Код отдельно:

function onOpen() {
  SpreadsheetApp.getUi().createMenu("🔥")
    .addItem('загрузить расходы за диапазон дат', 'google_sheets')
    .addToUi();
};

function google_sheets() {
  try {
    url_api = 'https://advert-api.wb.ru/';
    tz = Session.getScriptTimeZone();
    name = 'Расходы';

    ss = SpreadsheetApp.getActive();
    sh = ss.getSheetByName(name);
    d = sh.getDataRange().getValues();

    [start, end, token] = ss.getRange("!!b1:b3").getValues().flat()
      .map((f, i) => i < 2 ? d2s(f) : f);

    o = [];
    url = `${url_api}adv/v1/upd?from=${start}&to=${end}`

    r = request(url, 'get', token);

    r = r
      .map(k => {
        var [advertType, updTime, paymentType, advertStatus, updSum, advertId, campName] =
          [advert_type[k.advertType] || k.advertType, new Date(k.updTime), k.paymentType, obj_status[k.advertStatus], k.updSum, k.advertId, k.campName];

        updTime = d2s(updTime, 'yyyy-MM-dd HH:mm:ss', tz);
        key = updTime + '|' + advertId;
        if (!o.includes(key)) o.push(key);

        return [advertType, updTime, paymentType, advertStatus, updSum, advertId, campName];
      });

    if (r.length) {

      d = d.slice(1).filter(g => {
        date = d2s(g[1], 'yyyy-MM-dd HH:mm:ss', tz);
        key = date + '|' + g[5];
        return !o.includes(key);
      });

      console.log('стало: ' + d.length);
      r = [...d, ...r];

      console.log('суммарно вставляем: ' + r.length);
      sh.getRange("A2:M").clearContent();
      sh.getRange(2, 1, r.length, r[0].length).setValues(r);
    };
  } catch (err) {
    Browser.msgBox(err.message);
  }
};

obj_status = {
  4: 'готова к запуску',
  7: 'завершена',
  8: 'отказался',
  9: 'активна',
  11: 'приостановлена'
};

advert_type = {
  9: 'поиск',
  8: 'автокомпания'
};

function d2s(date, format = 'yyyy-MM-dd', tz = Session.getScriptTimeZone()) {
  return Utilities.formatDate(date, tz, format);
};

function request(url, method, token, data) {
  params = {
    method: method,
    muteHttpExceptions: true,
    contentType: 'application/json;',
    payload: JSON.stringify(data),
    'headers': {
      Authorization: token
    }
  };

  r = UrlFetchApp.fetch(url, params);
  r = JSON.parse(r)
  return r;
};

PS Токен ВБ нужен "продвижение", получить можно в личном кабинете продавца ВБ (как)

Что есть еще по WB + Таблицы

1) Таблица, которая загружает из API остатки и отчет по реализации t.me/google_sheets/1143

2) WB, достаём топ-100 товаров по запросу t.me/google_sheets/1116

3) Таблица, которая выгружает данные по артикулам WB, t.me/google_sheets/1104

---
⭐️ Заказ работы у нас

Телеграм бот для техподдержки, связанный с Google Таблицами

Друзья, привет! Пока сделаем анонс того, что скоро выложим на канал. Идея простая и уже проверенная нами в ряде компаний.

Концепция
– Телеграм бот, которому ваши сотрудники (ну или клиенты) задают вопросы, то есть просто отправляют сообщения

– Все сообщения приходят в Google Таблицу

– Оператор отвечает на сообщение прямо из Таблицы и оно отправляется автору вопроса в Телеграм

– Помимо этого можно сделать рассылку среди тех, кто боту писал и его не заблокировал

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

Горячая линия своими руками (телеграм бот + отвечаем на вопросы из Таблицы)

В видео - демонстрация работы.

Чтобы развернуть у себя - делаем копию Таблицы, открываем редактор скриптов, читаем лист "!!!ИНСТРУКЦИЯ". Всё очень просто, справится даже новичок.

Чтобы отформатировать сообщение, которое отправляем - используйте HTML style

Нужен бот посложнее – ⭐️ Заказ работы у нас

PS
Это – прекрасный пример того, как неожиданно мощны могут быть Таблицы. В этой Таблице мы развернули "сервер" с веб-приложением, который работает круглосуточно и бесплатно, ждёт сообщений от Телеграм бота и логирует их в Таблицу, далее также легко вы можете из этой Таблицы отвечать.

В последнее время в чатах и личных сообщениях с той или иной долей паники спрашивают, чем заместить доски Miro…

Которыми, как выясняется, пользуется едва ли не столько же компаний и команд, сколько и таблицами 🤠 Это такой же нужный инструмент для командной работы, в том числе, если не в первую очередь, для удаленных команд

Чем же заменить ушедший сервис? Опцию бесплатного переноса данных из Miro обещает VK Доска. Да и собственно весь сервис пока бесплатный. Число досок бесконечное, число участников тоже, так что если работаете удаленно (а раз вы пользуетесь Таблицами, то это весьма вероятно) — подойдет.

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

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

На Google Диске новые горячие клавиши

Старые, более короткие, увы, теперь холодны и не работают.

Например, для создания папки раньше нужно было нажать Shift + F и все.
А теперь Alt + C, а потом F.

Соответственно, для создания новых файлов теперь нужно нажать Alt + C (Control + C), а потом:
Документ — T
Таблица — S
Презентация — P
Форма — O

Переименовать файл или папку раньше можно было просто нажатием клавиши N. А теперь — F2 (или Ctrl + Alt + N).

Добавить в список помеченных (Starred) — раньше S, а теперь Ctrl + Alt + S 🤯 (Command + Option + S).

P.S. А почему поменялось? Добавилась возможность навигации по первым буквам файлов. То есть вводим букву и попадаем к файлу, имя которого начинается с нее.

Решаем проблему поиска ВПР-ом в трех диапазонах

Допустим, у нас есть три Таблицы и нам нужно найти наше значение сначала в одной, потом, если не нашли, то в следующей, не нашли в следующей - ищем в последней.

Два варианта, вариант олдскульный и классический:

Функция ищет в первом диапазоне, если возвращается NA - в следующим и так далее.

=IFNA(VLOOKUP(A6;A1:B3;2;0);IFNA( VLOOKUP(A6;D1:E3;2;0);VLOOKUP(A6;G1:H3;2;0)))

И вариант молодежный и прогрессивный:

С помощью VSTACK соединяем диапазоны в один и дальше используем один ВПР.

=VLOOKUP(A6; VSTACK(A1:B3;D1:E3;G1:H3) ; 2; 0)

Статья про VSTACK и другие модные формулы от нашего Михаила

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

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

Число строк по каждому городу разное, так что за это не зацепишься.

Какие варианты в такой ситуации возможны?

1 Смотреть на первое слово / символы в ячейке. Например, проверять, что первые 6 символов (ЛЕВСИМВ / LEFT) — это "филиал"
=ЕСЛИ(ЛЕВСИМВ(A2;6)="филиал"; A2; значение сверху)

2 А если не везде начало со слова "филиал", как в реальной жизни? Там было то "Филиал", то "Центр", то сразу город.

Тогда можно смотреть на следующую строку — если там подзаголовок другого уровня, значит, в текущей строке заголовок по городу. Это решение на скриншоте.

3 Можно смотреть на какой-то из соседних столбцов (B, C и так далее), но не конкретно в этом случае, т.к. здесь эти столбцы пустые и в строках с городами, и в строках с категориями услуг (Основные, дополнительные). Но в каких-то случаях это подойдет, можно будет проверять столбец на пустоту функцией ЕПУСТО / ISBLANK.

Показывает чекбоксы только на строках с данными / снижаем визуальный шум :)

Друзья, привет, представьте - вы выводите диапазон данных и хотите, чтобы слева или справа у вас выстроились чекбоксы по каждой строке выведенного диапазона, но не больше

Как?
1. создаём целый столбец чекбоксов (выделили столбец > вставка > флажок)

2. применяем белый цвет шрифта на этот столбец

3. и создаем правило условного форматирования формулой для этого столбца - если в соседнем столбце есть данные - крась шрифт ячейки со скрытым чекбоксом в черный (смотрите на на гифку)

ПС а еще чекбоксы называют крыжами, слышали про это?

Наш чат, про Таблицы, скрипты и крыжи: @google_spreadsheets_chat

Наша команда уже пять лет работает с маркетплейсами: с ОЗОНОМ, ВБ, Яндекс маркетом

Что мы делаем:

 1. выгружаем разные отчеты через API прямо в Таблицу, от остатков товаров (и их можно копить) до заказов / продаж / показателей по вашей рекламе;

 2. загружаем данные в маркетплейс, те же остатки ваших складов FBS или новые цены, меняем карточки товаров прямо из Талиц;

 3. и все зз удобного интерфейса Google Таблиц. Никаких проблем с доступом нет, если верно соблюдать тайминги и не стучаться каждую наносекунду - всё летает;

 4. Средний отчет (обращение к одному API с последующей обработкой, скажем, накоплением заказов по ВБ) у нас стоит 10 000 рублей;

 5. Помимо просто выгрузки сырых данных мы можем придумать из них дашборды, которые вам нужны;

 6. Ну и конечно, данные можно грузить не только с одного ключа / магазина, а с многих, а если данных много - мы их храним не в Таблицу, а в облачной базе Google Biqquery;

 7. Хотите продолжать выгружать отчетики из личного кабинета и как-то сводить с помощью бедных аналитиков - пожалуйста вы им даете работу, мы же, можем свести весь процесс для вас до одной кнопки (или вообще без кнопки, сделать автоматическую загрузку по расписанию);

Хотите из Таблицы / Локер Студио видеть все по своей империи - пишите мне, @namokonov, по всеми вопросам

Ключевая ставка ЦБ РФ на дату - или формула имени Эльвиры Сахипзадовны

Ребятки, привет!

Вот формула:

=LET(_data; IMPORTHTML("https://cbr.ru/hd_base/KeyRate/?UniDbQuery.Posted=True&UniDbQuery.From=17.09.2013&UniDbQuery.To=" & TEXT(TODAY(); "dd.mm.yyyy"); "table"; 1); REDUCE({"Дата"\ "Ключевая ставка"}; SEQUENCE(ROWS(_data) - 1; 1; ROWS(_data); -1); LAMBDA(_acc; _i; LET(_date; --INDEX(_data; _i; 1); _value; INDEX(_data; _i; 2) / 100; IF(INDEX(_acc; ROWS(_acc); 2) <> _value; {_acc; _date\ _value}; _acc)))))

Формула достаёт значения ключевой ставки ЦБ РФ с сайта с 17.09.2013 (до этой даты данных нет) по сегодняшний день, приводит строки с датой и значением ставки к числовым значениям (формат столбца с датой надо будет задать руками, чтоб красиво отображалось).

Ещё она оставляет только новые значения: строки с датами, когда ставка не менялась отфильтровываются. Так получается 55 строк вместо 2700+. А пользоваться также: VLOOKUP (ВПР) с 4-м параметром True.

Спасибо за внимание! Всех с 1-м сентября!

🏄 И спасибо нашему прекрасному Михаилу Смирнову, он хоть и не в ЦБ (пока), но многое сделал для извлечения информации про курсы валют и показатели

Задача: пронумеровать список, но так, чтобы пропуски игнорировались.

Если бы пропусков не было, можно просто использовать функцию СТРОКА / ROW, возвращающую номер строки, и одной формулой массива получить все номера:

=ArrayFormula(ЕСЛИ (ЕПУСТО(B2:B);""; СТРОКА(B2:B)-1))

А с пустыми строками?
Вот такой вариант — простой , но и без формулы массива (то есть формулы будут в каждой строке листа) — сработает. Берем диапазон с началом в первой ячейке (и ее закрепляем долларами) и с концом в текущей строке (для первой строки таблицы и начало и конец у диапазона совпадают, $B$2:B2.

И считаем количество значений в этом диапазона (СЧЁТЗ / COUNTA). Проверяем на пустоту и в таком случае ячейку с номерами тоже оставляем пустой.

=ЕСЛИ(B2="";"";СЧЁТЗ($B$2:B2))

А можно ли это решить одной формулой? Можно 🤠Но это будет посложнее. Можно через MAP и СМЕЩ (OFFSET). Какие еще предложения будут у вас?

А вот и ссылки

📗 Чат, где почти 6к активных подписчиков: @google_spreadsheets_chat

📕 Оглавление канала

⭐️ Заказ работы у нас