Суммируем каждую N-ую ячейку

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

Можно использовать такую формулу:

=SUMPRODUCT((MOD(COLUMN(диапазон);N)=0)*(диапазон))

Здесь мы проверяем, нулевой ли остаток при делении номера столбца на N — если нулевой, значит ,столбец нам нужен, и вся следующая конструкция:

(MOD(COLUMN(диапазон);N)=0)

будет в таком случае возвращать TRUE (1).
А для ненужных нам столбцов FALSE (0).
И получим мы на выходе массив {FALSE, TRUE, ...}
который умножим на наш диапазон. SUMPRODUCT потом все это дело просуммирует.

В нашем примере каждый четвертый столбец просуммируем так:

=SUMPRODUCT((MOD(COLUMN(C2:R2)-2;4)=0)*(C2:R2))

Вычитаем двойку мы здесь потому, что наш диапазон начинается в C, а не в A — отступаем два этих столбца, чтобы первый столбец диапазона был первым и по порядку в наших расчетах.

Создаём QR-код в Таблице и Документе

Друзья, привет! В Таблицах QR-код создаётся формулой, которую нужно ввести в ячейку, как это сделать, мы рассказывали вот здесь: https://tttttt.me/google_sheets/912

А чтобы добавить QR в Google Документ нужен скрипт, например тот, который будет ниже.

Что скрипт делает:
1) ищет в тексте документа "###"
2) находит и вставляет вместо "###" QR-код, созданный из ссылки t.me/google_sheets
3) изменяет размеры изображения на 75 и 75 пикселей
4) и добавляет к самой картинке исходную ссылку

function createQRCode() {
  var Doc = DocumentApp.getActiveDocument();
  var totalElements = Doc.getNumChildren();

  for (var j = 0; j < totalElements; ++j) {
    var element = Doc.getChild(j);
    el = element.getText();

    if (el == '###') {
      element.removeFromParent();

      var url = 't.me/google_sheets';
      var blob = UrlFetchApp.fetch("https://chart.googleapis.com/chart?cht=qr&chs=300x300&chl=" + encodeURIComponent(url)).getBlob();

      Doc.insertImage(j, blob)
        .setHeight(75).setWidth(75)
        .setLinkUrl(url)
    };
  };

PS Конечно, вы можете собрать ссылку из того, что есть в документе, например, искать с помощью регулярного выражения номер договора, соединять его с "ссылкой/" в коде и из этого формировать QR-код.

Документ со скриптом

Достаём курс евро из Московской Биржи.

Друзья, привет, Михаил поделился ссылкой, по которой можно вытянуть текущий курс евро к рублю из MOEX:

https://iss.moex.com/iss/engines/currency/markets/selt/boards/CETS/securities/EUR_RUB__TOM.json?iss.only=securities,marketdata&securities.columns=PREVDATE,PREVPRICE&marketdata.columns=TIME,LAST

Что с этой ссылкой делать дальше? Доставать данные скриптом в Таблицу, например:

function eurRub() {
  const url = 'https://iss.moex.com/iss/engines/currency/markets/selt/boards/CETS/securities/EUR_RUB__TOM.json?iss.only=securities,marketdata&securities.columns=PREVDATE,PREVPRICE&marketdata.columns=TIME,LAST'
  
const r = JSON.parse(UrlFetchApp.fetch(url));
  const curr = r.marketdata.data[0][1] || r.securities.data[0][1];
  SpreadsheetApp.getActive()
    .getRange("лист5!a1")
    .setValue(curr);
};

Наш скрипт достанет либо цену последней сделки, либо, если её нет, например, не было торгов - последнюю актуальную цену.

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

Вытаскиваем utm из ссылки (а точнее — все, что после знака вопроса)

Для этого можно воспользоваться следующим регулярным выражением:
\?(\S+)

Извлекаем выражение (круглые скобки) из не-пробелов, идущее после знака вопроса (\? = знак вопроса, а без слеша это был бы квантификатор (ноль или один символ)

Вся функция будет такая:
=REGEXEXTRACT(A2;"\?(\S+)")

Хорошее про регулярные выражения:
Компактная памятка про регулярки от Vitalich
Извлекаем числа, едим пончики
Волшебство "найти и заменить". Приводим mm-dd к dd-mm

UTM-метки. Извлекаем все параметры и их значения формулой

Справедливый комментарий к прошлому посту: удобнее получать отдельные параметры (source, medium, etc.), а не все метки разом.

Можно так:
1. Взять все из ссылки после знака вопроса (с помощью комбинации текстовых функций или INDEX+SPLIT или REGEXEXTRACT — в примере последний вариант). Получится хвост ссылки с метками, где параметры и их значения разделены знаком "равно" (=), а пары разделены амперсандом (&).

2. Разделить их на пары по амперсанду с помощью SPLIT .

3. Транспонировать, чтобы в итоговом результате было два столбца — с параметрами и значениями. Функция TRANSPOSE.

4. Разделить по знаку "равно" с помощью SPLIT. Засунуть ее в ArrayFormula, ибо иначе получим результат только для первой пары.

Есть идеи по сокращению и оптимизации формулы? Добро пожаловать в комменты!

=ArrayFormula(SPLIT(TRANSPOSE(SPLIT(REGEXEXTRACT(A2;"\?(\S+)");"&"));"="))

Эх, дороги..

Друзья, к нам недавно пришел наш клиент, с такой задачей:

Задача от классной гитарной школы Guitardo

1) есть первая точка (долгота и широта)
2) есть еще сто точек, также с долготой и широтой

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

Как решили

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

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

Друзья, нам осталось всего 17 голосов, поднажмём?

Определяем первую и последнюю даты квартала по его номеру.

Ловите формулы для определения первой и последней даты квартала по номеру года и номеру квартала. Может пригодиться, если у вас отчеты с функциями SUMIFS, QUERY и другими, куда вы отправляете даты в качестве условий, а пользователю предлагаете выбирать только номер года/квартала в ячейках.

Начало квартала:

DATE(год; квартал * 3 - 2 ;1)

Конец квартала:

EOMONTH(DATE(год; квартал * 3 ;1);0)

Для QUERY, напоминаем, даты в запросе нужно будет засунуть в date'...' и получить нужный формат YYYY-MM-DD с помощью функции TEXT. Например, для начала квартала:

TEXT(DATE(год; квартал * 3 - 2 ;1) ; "yyyy-mm-dd")

Не таблицами едиными! Сегодня про Google Диск (Drive).
Вы знали, что там можно использовать операторы, позволяющие сделать поиск более точным?
Вот некоторые из них.

title:название файла

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

"Поиск фразы из нескольких слов"

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

-Исключение Другие слова

Поставьте минус перед словом, чтобы исключить его из поиска. Этот оператор сработает, если в запросе есть другие слова помимо исключаемого. Так, в нашем примере мы будем искать «Другие слова», а слово «Исключение» будет исключено из поиска.

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

-owner:me

to:[email protected]

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

from:[email protected]

Документы, доступ к которым вам открыл определенный пользователь.

Слово Around(n) Другое_слово

Оператор Around(n) позволяет искать слова (фразы), которые находятся на расстоянии в n (или менее) слов друг от друга. То есть с его помощью можно искать слова, которые находятся рядом, например, в одном предложении.
Например: запрос — поиск слов «формула» и «таблица» не дальше, чем на расстоянии 3 слов друг от друга:

формула around(3) таблица

Граждане, внимание!

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

Мы это заметили в таблице, где давно использовалась и работала функция IMPORTXML(). До нажатия кнопки "Allow access" импорт висел поломанный, после нажатия - всё ок, всё импортится, как раньше.

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

Выделяем цветом формулы по какому-то признаку

Вы хотите выделить визуально формулы массива или формулы, ссылающиеся на какой-то лист, или формулы с определенными функциями.

напоминаем, что получить текст формулы можно с помощью функции FORMULATEXT. Ну а далее искать в этом тексте какой-то признак можно с помощью функций REGEXMATCH или НАЙТИ / FIND, ПОИСК / SEARCH.
И если все это засунуть в условное форматирование, то мы получим возможность выделять визуально формулы, содержащие что-нибудь!

Определенные функции — по их названию. Например, XLOOKUP:
=НАЙТИ("XLOOKUP";FORMULATEXT(ячейка)

Формулы массива можно выделить по наличию функции ArrayFormula:
=REGEXMATCH(FORMULATEXT(первая ячейка форматируемого диапазона));"ArrayFormula")

Ссылки на лист с названием - по этому самому названию:
=НАЙТИ("название листа";FORMULATEXT(первая ячейка ...))

На скриншоте в условном форматировании следующая формула, с помощью которой выделяем ячейки с ссылками на "Лист3":
=REGEXMATCH(FORMULATEXT(B2);"Лист3")

УКРАШАТОР | переносим форматирование из источника во много Таблиц

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

Как использовать:
1) копируйте таблицу к себе
2) заполняйте лист настройки: вводим откуда скрипту брать шаблон с форматированием и куда его нужно будет вставлять
3) запускайте скрипт из меню с 🔥, скрипт пройдется по каждой таблице из списка "куда" и поставит дату и время в столбце C, если перенести форматирование получится

* Переносим только заливку ячеек, цвет шрифта, размер шрифта и сам шрифт, если нужно что-то еще - можете добавить в 16-21 строки кода по аналогии. Название методов можно подсмотреть вот здесь.

А обязательно ли вообще перечислять все методы или можно перенести форматирование "разом"? Можно, с помощью sheets api, попозже покажем вам пример, ну или вы покажите его в комментариях.

⭐️ ЗАКАЗАТЬ РАБОТУ
⭐️ Оглавление канала: ты-дыц

В Google Документах можно использовать переменные — увы, только в корпоративных аккаунтах Google Workspace (и в учебных), но не в личных бесплатных.

Для чего это нужно? У вас есть шаблонный документ или письмо, где нужно менять только определенные параметры (ФИО, даты и так далее). Причем некоторые параметры (ФИО сотрудника, например) фигурирует в нескольких местах. С переменной можно изменить значение в одном месте и не проверять, везде ли вы все исправили во всем документе.

Открываем квадратную скобку, вводим название переменной, закрываем скобку и нажимаем Tab. Другой вариант: меню Вставка -> Умные чипы -> Переменные.

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

Дано: в ячейке есть символы, нам надо получить текстовую строку с ними же, но отсортированными.

Решение: создаем виртуальный массив из всех символов — для этого создаем последовательность (функция SEQUENCE) чисел от единицы до числа символов в ячейке (LEN / ДЛСТР), извлекаем символы с помощью ПСТР / MID — сформированная последовательность выступает номерами символов, которые мы этой функцией извлекаем.

Далее сортируем полученный массив (SORT) и сразу же собираем его обратно в текстовую строку (CONCATENATE / СЦЕПИТЬ).

Получится формула:
=CONCATENATE(SORT(MID(ячейка;SEQUENCE(LEN(ячейка));1)))

Геокодер / Достаем по адресу координаты, используя скрипты

Друзья, недавно у нас заказали скрипт и заказчик (Иван, спасибо ❤️), разрешил его выложить на канал для всех.

Скрипт небольшой, но полезный, он собирает адрес из первого и второго столбца и если в столбце G стоит галочка "обновить", то идет в Maps.newGeocoder(), достает по этому адресу координаты, адрес еше раз (чтобы можно было сравнить адрес, которые запрашивали и который вернулся), JSON с остальным данными и выключаем галочку.

Пример работы на гифке, гифка со звуком.

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

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

PS Посмотрел на JSON еще раз - еще из него можно достать индекс адреса, который вы запрашиваете

Извлекаем актуальное число подписчиков телеграм-каналов из ссылки вида t.me/канал

Для этого извлечем текст "### subscribers", который есть по такой ссылке — с помощью IMPORTXML.
Ну а потом удалим (заменим на ничто) все не-цифры (\D) с помощью REGEXREPLACE.

Чтобы получить из 20 500 subscribers —> 20500.

Все вместе:
=REGEXREPLACE(IMPORTXML(A2;"//div[3]");"\D";"")

Если у вас не ссылки вида t.me/канал, а только @имя_канала, то можно собрать ссылку прямо в формуле, удалив @:
=REGEXREPLACE(IMPORTXML("https://tttttt.me/"&SUBSTITUTE(A3;"@";"");"//div[3]");"\D";"")

Меняем формат даты с ММ/ДД/ГГГГ на ДД.ММ.ГГГГ формулой

Эту задачу решает формула от постоянного участника нашего сообщества Льва:

=ArrayFormula(IF(A:A = "";;TEXT(REGEXREPLACE(TO_TEXT(A:A);"(\d{2})\/(\d{2})\/";"$2/$1/")*1;"dd.mm.yyyy HH:mm:SS")*1))

Это формула массива — одной формулой решаем задачу для всего столбца:
1. проверяем, не пусто ли в столбце — в случае пустых ячеек формула возвращает пустоту.
2. с помощью REGEXREPLACE меняем цифры дня и месяца местами. Здесь используется запись вида $1 — это ссылка на подвыражение (подвыражения берутся в круглые скобки). Таким образом, мы меняем подвыражения — две идущих подряд цифры с косой чертой после — местами.
3. Меняем формат результата с помощью функции TEXT / ТЕКСТ.
4. Делаем значение числом за счет умножения на единице (подробнее про это тут).

Регулярные выражения — компактная памятка от vitalich

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

Допустим, вы хотите разрешить ввод только кодов цветов в шестнадцатеричной форме.

Тогда можно использовать REGEXMATCH — эта функция будет возвращать TRUE / ИСТИНА, если текст, соответствующий регулярному выражению во втором аргументе, будет найден в текстовой строке из первого аргумента.

В случае проверки данных формула будет выглядеть так:
=REGEXMATCH(первая ячейка диапазона с проверкой ;"рег выражение")

В случае с кодами цветов:

=REGEXMATCH(A2;"^#[0-9A-Fa-f]{6}$")

То есть решетка + 6 символов из набора "все цифры, строчные и прописные буквы от A до F". Чтобы разрешить ввод только кодов (то есть в ячейке не должно быть больше ничего), добавляем ^ и $ (начало и конец строки).

Заказать работу

Ctrl+Backspace — возвращение к активной ячейке

Хорошее сочетание, но это, увы, тот случай, когда Excel все-таки выигрывает в схватке двух йокодзун.
Потому что в Google Таблицах это сочетание работает, когда ячейка просто выделена, а в Excel и при вводе формулы, и в диалоговых окнах (например, условное форматирование или расширенный фильтр).

А при вводе формулы в Google Таблицах это сочетание будет работать как в текстовых редакторах (и в Презентациях, и в браузере, и в других приложениях) — удалять последнее слово. Чтобы удалить слово, следующее после курсора, нажмите Ctrl+Delete.