TG Telegram Group Link
Channel: Google Таблицы
Back to Bottom
This media is not supported in your browser
VIEW IN TELEGRAM
В Google Документах можно использовать переменные — увы, только в корпоративных аккаунтах Google Workspace (и в учебных), но не в личных бесплатных.

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

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

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

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

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

Получится формула:
=CONCATENATE(SORT(MID(ячейка;SEQUENCE(LEN(ячейка));1)))
Media is too big
VIEW IN TELEGRAM
Геокодер / Достаем по адресу координаты, используя скрипты

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

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

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

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

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

PS Посмотрел на JSON еще раз - еще из него можно достать индекс адреса, который вы запрашиваете
Извлекаем актуальное число подписчиков телеграм-каналов из ссылки вида hottg.com/канал

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

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

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

Если у вас не ссылки вида hottg.com/канал, а только @имя_канала, то можно собрать ссылку прямо в формуле, удалив @:
=REGEXREPLACE(IMPORTXML("https://hottg.com/"&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.
Извлекаем все числа из текстовой строки в отдельные ячейки

И еще пример с применением подвыражений в регулярках — от Бена Коллинса.

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

=SPLIT(REGEXREPLACE(текст;"(\d+)|.";"🔥$1");"🔥")
This media is not supported in your browser
VIEW IN TELEGRAM
Друзья, сегодня у нас для вас простой скрипт для обучения Google Apps Script в Таблицах.

Скрипт работает так – встаём на ячейку и нажимаем на кнопку либо с плюсом, либо с минусом, значение в ячейке либо увеличивается на один, либо уменьшается на один. Если в ячейке будет текст, то скрипт выведет ошибку.

Сам скрипт, снабженный комментариями:


function main(n) {
//обращаемся к активной Таблице
const ss = SpreadsheetApp.getActive();

//обращаемся к диапазону
const range = ss.getActiveRange();

//берём значение диапазона
const value = range.getValue();

//проверяем, должна быть или пустая ячейка или у значения должен быть тип число
if (typeof value == 'number' || !value) {

//вставляем в ячейку значение и аргумент, который мы передали в функцию
range.setValue(value + n);
} else {

//если в ячейке не число - выводим ошибку
ss.toast('Кажется, что в ячейке не число :(');
}
};

//ну и две функции, которые мы повесили на кнопки + и -, в каждой вызываем главную функцию, в которую передаем либо +1 либо -1
function minus() {
main(-1);
};

function plus() {
main(1);
};


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

Заказать работу (если требуется что-то посложнее)
Что еще у нас есть про телеграм?

🍏 выгружатор, достаем скриптом количество подписчиков чатов и каналов в телеграм hottg.com/google_sheets/855

🍏 выгружатор постов / просмотров / картинок из каналов Telegram hottg.com/google_sheets/841

🍏 отправлятор / удалятор постов и сообщений в телеграм hottg.com/google_sheets/937

🍏 отправка сообщения в телеграм чат при любом редактировании столбца hottg.com/google_sheets/950

🍏 отправлятор 2, отправляем по расписанию диапазоны из Таблицы в Телеграм hottg.com/google_sheets/643

🍏 регулярный бекап Таблиц в XLSX в Телеграм hottg.com/google_sheets/884

---
⭐️ Заказ работы
Условное форматирование. Выделяем дубликаты и не только.

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

В формулах используется функция СЧЁТЕСЛИ / COUNTIF — мы считаем, сколько раз конкретное значение (ссылка относительная без $ — мы проверяем формулу для каждого очередного значения) встречается в диапазоне (диапазон закреплен, он не меняется для всех проверяемых ячеек).
Формула в условном форматировании вводится один раз — при ее формировании учитывайте, что вы вводите формулу для левой верхней ячейки диапазона, а далее на все остальные ячейки она будет «протягиваться» (как если бы мы ввели формулу в одну ячейку и далее скопировали и вставили во все остальные ячейки в трех столбцах). И ссылки на ячейки будут меняться, если они являются относительными.

Для более чем 2 столбцов используйте COUNTIFS.

Для создания правила условного форматирования:
1) Выделите диапазон
2) Формат — условное форматирование (Format — Conditional formatting) или Alt+O + F
3) "Ваша формула" (Custom formula is) — введите соответствующую формулу.

Таблица с примером
Media is too big
VIEW IN TELEGRAM
Регулярный бэкап Таблиц в формате XLSX в телеграм!

Таблица со скриптом (делайте копию) работает так:

1) вставляете ссылки на Таблицы, бэкап которых вы хотите сделать, в столбец A:A

2) нужно заполнить настройки: открываете редактор скриптов (расширения > apps script) и заполняете:
chatId – чат телеграма, в который будут отправляться бэкапы Таблицы, чат может быть как личным, так и общественным, чтобы узнать chatId - используйте @myidbot в телеграм
botToken - токен телеграм бота, чтобы получить его, зарегистрируйте бота через @botfather
hours - часы отправки через запятую

3) Как заполните всё – запустите из меню "🐞" скрипт "создать триггер на каждые 15 минут"

Все! После этого скрипт будет каждые 15 минут запускаться и проверять, есть ли Таблицы, бекап которых нужно сделать, если есть - скрипт будет конвертировать эти Таблицы в XLSX и отправлять файлы в выбранный в настройках чат.

Таблица и скрипт
Криптовалюта, достаём курсы

Есть сайт: https://cryptoprices.cc/, из него мы можем достать курс криптовалюты (биткоина, скажем), собрав ссылку вида:

"https://cryptoprices.cc/" + "btc"

Чтобы вытянуть цифры - поместим эту ссылку в функцию importdata.

Если нужно поменять разделитель числа с точки на запятую (а загружаться всё будет с точкой) - добавляем функцию substitute / подставить.

Итоговая формула, которая у нас получилаcь:
=--SUBSTITUTE(IMPORTDATA("https://cryptoprices.cc/btc");".";",")

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

📌 Поделитесь в комментариях ресурсами, из которых вы достаёте разное в свои Таблицы

---
⭐️ Заказ работы
Пользовательская сортировка

Вам нужно сортировать данные в своем порядке — не по алфавиту.
Например, какой-то регион в вашем бизнесе важнее других, хотя название города не первое по алфавиту.
Или речь про размеры одежды S, M, L...

Как быть? Можно создать дополнительный столбец с нумерацией.
Заполнять его будем формулой, в общем виде так:
=XMATCH (ссылка на ячейку со значением; {массив всех вариантов в нужном порядке})


В случае с одеждой так:
=XMATCH(B2;{"S";"M";"L";"XL"})


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

Не хочется дополнительный столбец? Можно сразу сделать значения вида "1 S", "2 M" или "1-S" и так далее (сначала номер для сортировки и потом само название элемента). Но такой трюк сработает только при числе элементов до 10. Потому что если их больше, то данные будут сортироваться так: 1-текст, 10-текст, 11... 19, 2, 20, 21...

📌 А в следующем посте покажем, как добавить этот столбец как виртуальный в функцию SORT и отсортировать данные
Как сделать бекап Google Диска

1. Самый простой и правильный способ забекапить данные
https://takeout.google.com/

2. А также – нажатие на кнопку Download / Скачать в контекстном меню по щелчку на папке Google Диска создаёт ZIP-архив с файлами (при этом файлы редакторов Google будут преобразованы в форматы Microsoft Office, например, Google Таблицы в XLSX) и скачивает его на ваш компьютер.

3. Вот здесь мы писали о скрипте, с помощью которого можно сохранить лист Таблицы в выбранном формате (PDF / CSV / XLSX).

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

---
⭐️ Заказ работы
Чувствуешь, что деградируешь, решая на работе однотипные скучные задачи? А заказы/подработки высасывают остатки свободного времени и сил?

Если ты программист или другой специалист в IT, то тебе срочно нужна стажировка (лучше, если оплачиваемая) в крупной IT-компании:
а) Тебя могут заметить и ты сразу влетишь в высшую лигу
б) Увидишь изнутри и поймешь, что тебе нужно, чтобы ничем не уступать ребятам, которые работают там.

✔️ Можно составить резюме, сопроводительное письмо и беспощадно атаковать почтовые ящики компаний, но, к сожалению, почти всегда такое письмо окажется в папке «Спам» даже до прочтения.

✔️ Есть курсы, которые обещают гарантированную стажировку, но чтобы их приобрести, нужно уже сейчас получать зарплату как минимум на уровне мидла 😒

✔️ А можно стать участником интенсива от «Школы 21».

В чем плюсы:

Это совершенно бесплатно. Да, ты бесплатно учишься, бесплатно проживаешь, но проходишь платную стажировку

Крупные IT-компании знают о «Школе 21», сам факт обучения там уже дает незримое преимущество при трудоустройстве или стажировке

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

Хорошие новости – ближайший поток стартует 1 апреля, документы можно подавать уже сейчас.

Отличные новости – все подробности и внутряки публикуются в этой группе: весенний набор Великий Новгород 2024г.

За подписку дарим 🎁 - гайд о том, как стать успешным программистом

Реклама. ООО «Цифровая жажда» Erid 2Vtzqvi9F8h
Рассчитываемое поле не работает в сводной таблице?

Проверьте формат заголовков.
Если вы применили формат "Расчеты" (Accounting) не только к числам, но до кучи в заголовках, то в ячейках появится отступ. Выглядит как пробел, но вы не найдете его в значении — ни в строке формул, ни текстовыми функциями ДЛСТР / LEN или ЛЕВСИМВ / RIGHT, он себя никак не проявит.

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

Или, что будет правильнее, не поменяете формат в ячейках с заголовками на "Обычный текст" (Plain text) или "Автоматический" (Automatic).
Раскладываем длинную строку на столбцы и строки в два этапа

Друзья, к нам приходит выписка вот в таком формате:


26 Марта
Пополнение
#173/Лицевой счет
20000.00 ₽
26 Марта
Пополнение
#1730/Лицевой счет
50000.00 ₽
25 Марта
Пополнение
#1726/Лицевой счет
25000.00 ₽


Как бы нам из этого получить нормальную Таблицу, со строками и столбцами?

Поехали получать:

1) Сначала разделим наш длинный текст по переносу строки (символ char(10)), с этим поможет формула
=SPLIT(A1;char(10))

2) Получаем строки, в каждой строке у нас четыре элемента (дата, суть платежа, наименование и сумма) , чтобы каждую строку разложить на эти элементы, используем функцию WRAPROWS (подробнее про функции работы с массивами), а в аргументах будет полученный массив строк и на сколько частей каждую строку нужно разделить:
=WRAPROWS(SPLIT(A1;char(10));4)

Ура, все вышло
HTML Embed Code:
2024/03/29 02:06:46
Back to Top