Функция ТЕКСТ / TEXT: превращаем число в текстовое значение в заданном числовом формате

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

Для чего нужна?
Допустим, вы хотите "склеить" в одну текстовую строку текст и число.
Чтобы получить в таблице надпись вида "По состоянию на: 20.11.23" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты.

Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 45250") — потому что вот так даты хранятся в Таблицах (и Excel).

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

Итак, для даты в нашем примере нужна будет такая формула:

="По состоянию на: " & ТЕКСТ (дата; "DD.MM.YY DDD")

Это формат вида 20.11.2023 Пн (с коротким днем недели — за него отвечает DDD). Подробнее про пользовательские числовые форматы можно посмотреть в видео.


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

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

Пункты называются так:
Данные проверены (Data validated)
Данные не проверены (Data not validated)

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

Про проверку мы писали ранее тут:
Выбор нескольких значений https://tttttt.me/google_sheets/1488
Проверка данных (Data validation) с формулами https://tttttt.me/google_sheets/792


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

MATH.SUM для подсчета суммы В JS/GAS НЕТ, ЗАТО ЕСТЬ РЕДЬЮС!

Показываем пример попроще

function reduce() {
  let a = [1, 'a', 3, 3, 3, 'f'];

  console.log(
    a.reduce(
      (acc, v) => acc + (typeof v == 'number' ? v : 0),
      100
    )
  );
}

и посложнее

function reduce() {
  let a = [
    [1, 'a', 3, 3, 3, 3, 3, 3, 'f'],
    [1, 'a', 3, 3, 3, 3, 3, 3, 'f'],
    [1, 'a', 3, 3, 3, 3, 3, 3, 'f']
  ];

  console.log(
    a.reduce((acc, row) => 
      acc + row.reduce(
        (acc2, v) => acc2 + (typeof v == 'number' ? v : 0),0),
    )
  );
}

МЫ (@google_sheets_ запускаем свой! двухнедельный интенсив:

* 4 урока по формулам — на первой неделе
* 3 урока по скриптам — на второй

---

Уроки буду вести я — Намоконов Евгений,
а также наша прекрасная Лиза — Елизавета, еще возможны пара секретных гостей

Стоимость всего курса — 70 000 рублей.
Работаем как с СЗ (самозанятыми), так и с ПС (группами и компаниями).
Но можно выбрать только то, что нужно вам — участие гибкое.

---

А не не хотите все 7 уроков — выбирайте минимум 4 из списка:

* JS: база
* Работа с Таблицей через скрипты
* Google Диск и скрипты
* Отправка писем (а может, и событий в Календарь)
* Работа с API разных видов
* Автоматическое создание мемов каждое утро и отправка их в WhatsApp

---
Любые вопросы: @namokonov

Суть: три раза в неделю, а это примено 10-12 реальных часов занятий

Анпивот! Таблица

Плоская, неплоская и анпивот (превращаем одно в другое)

В свежем видео показываю:

— Чем отличается плоская таблица от неплоской
— Зачем вообще превращать таблицы в плоские
— Как написать формулу UNPIVOT в Google Таблицах
— И как всё это помогает в автоматизации и анализе

А ещё:

🎓 Мы запускаем новый интенсив!
4 урока по формулам + 3 урока по скриптам
Только практика и только для тех, кто действительно хочет учиться и прокачиваться. Детали / Отзывы

📌 Чатик, попробуйте наоборот - из плоской таблицы неплоскую, пишите в комментарии

Друзья, появилось место на программирование, Google Apps Script!

Старт — завтра, занятия начнем с Google Диска.

Что будет в программе:
— Автоматизация Google Drive и Gmail
— Работа с API: Ozon, Wildberries, Telegram
— Разворачиваем скрипт как веб-сервер (для приема сообщений от Telegram-бота)

🧠 5-6 занятий — понедельник, среда, пятница
🕚 Время: 11:00 по МСК
💰 Стоимость: 50 000 рублей
📩 Детали и запись: @namokonov

Апну, пишите @namokonov кто хочет попасть на курс: прикладное применение GAS-скриптов для работы с
— Google Диском
— Google Почтой
— также изучим обращения к API: WB, OZON, Telegram

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

Возьмем одного человека.

Лаконичная визуализация для сводной и не только: функция REPT

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

Здесь применяется функция ПОВТОР / REPT. Она просто повторяет текстовую строку, заданную в первом аргументе, столько раз, сколько указано во втором аргументе. На каждые 10 000 выручки она возвращает один символ валюты.

=REPT("₽ ";F5/10000)

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

=первая ячейка>СРЗНАЧ(диапазон)

📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

QUERY для группировки и ещё одно QUERY для итогов — всё в одной формуле!

Друзья, модернизировали наш старый пост.
Делаем сводную таблицу, используя LET, дважды QUERY и INDEX.
При этом к исходным данным обращаемся только один раз. Таблица.

Записаться на наш интенсив / заказать у нас работу

Формула

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

Друзья, не забываем и про базовые функции, ниже эпиграф Гоголя к его "сорочинской ярмарке"

Менi нудно в хатi жить.
Ой, вези ж мене iз дому,
Де багацько грому, грому
Де гопцюють все дiвки,
Де гуляють парубки!

Он на украинском, как перевести на другие языки?

Используем функцию googletranslate:

=googletranslate(
наш текст;
"ISO-код языка оригинала";
"ISO-код языка перевода")

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

Ну а результат перевода - на скриншоте

А как называете переменные вы?

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

Расскажите в комментариях, а как называете переменные вы? 

Кстати, сейчас в названиях можно использовать и кирилицу.

Делим текстовую строку на отдельные элементы при помощи =REGEXREPLACE

Друзья, про этот трюк писал в своей рассылке Бенн Коллинс, а недавно про него вспомнил модератор нашего чата Алексей Одиссей, за что ему спасибо.

И так, второй аргумент =REGEXREPLACE - регулярное выражение, мы его опускаем вовсе и третьим аргументом добавляем свой символ, в примере это три шарпа ###.

После мы получаем три шарпа перед каждым символом в нашей строке и дальше просто делим по этим шарпам строчку с помощью SPLIT и получаем все составляющие этой строчки.

Ну и добавляем TRANSPOSE, чтобы элементы шли построчно.

=TRANSPOSE(SPLIT(REGEXREPLACE(A1;;"###");"###"))

Записаться на наш интенсив / заказать у нас работу

PS Зачем-то усложненная версия с заполненным вторым элементом =REGEXREPLACE

=TRANSPOSE(SPLIT(REGEXREPLACE(A1;"(.)";"$1###");"###"))

Достаём курсы криптовалют, Таблица, описание API

Друзья, есть открытое API, которое возвращает курсы криптовалюты (5000 монет) по ссылке cryptorates.ai/files/standard.csv (при клике на ссылку у вас скачается csv-файл).

Загрузить данные по всем монетам в Таблицу можно с помощью функции:

=IMPORTDATA(A1;;"en_US")

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

А так с помощью QUERY можно на лету отфильтровать массив и оставить в нём только нужные нам монеты:

=QUERY(IMPORTDATA(A1;;"en_US");"where Col1 matches 'BTC|ETH|TRUMP'";1)

Записаться на наш интенсив / заказать у нас работу

PS Как мы помним, запрос QUERY - текстовая строка, поэтому строку с нужными для вывода монетами легко можно собрать из листа с помощью функции

="where Col1 matches '"&TEXTJOIN("|";1;I4:I30)&"'"

Функция ТИП.ОШИБКИ / ERROR.TYPE

Даем ей ячейку, получаем порядковый номер ошибки (число). Смотрим в справку:
1 для ошибки #NULL!
2 для ошибки #DIV/0!
3 для ошибки #VALUE!
4 для ошибки #REF!
5 для ошибки #NAME?
6 для ошибки #NUM!
7 для ошибки #N/A
8 для всех других ошибок.

То есть можно отлавливать определенные ошибки по схеме:

=ЕСЛИ(ТИП.ОШИБКИ(ячейка)=2; "Кажется, тут случилось деление на ноль"; вычисление)

Еще напомним про другие функции:
ЕОШ / ISERR — возвращает ИСТИНА / TRUE для всех ошибок, кроме Н/Д (N/A)
ЕНД / ISNA — ИСТИНА только для Н/Д
IFNA — сразу заменяет N/A на второй аргумент, а без ошибки возвращает первый аргумент.

Смотрите также:
Как выделить ячейки с ошибками REF внутри формул
Подробная статья про ошибки в формулах


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы @namokonov

В функции LET можно задавать свои функции! Это, конечно, экзотика (потому что, как правило, если нам нужно применять одно и то же вычисление много раз, мы используем LAMBDA + MAP или другую вспомогательную функцию), но тем не менее.

Если после имени переменной последует не константа / выражение, а функция LAMBDA, то это будет имя функции, которую потом можно в LET вызывать. В следующем примере у нас простая функция, умножающая число, данное ей на входе, на 2:

=LET(f; LAMBDA (a;a*2); f(10) + f(5) )

Здесь f – название функции, а – название переменной, аргумента этой функции. Затем в последнем аргументе LET мы уже ее вызываем с конкретными значениями 10 и 5.

Про базовые сценарии применения LET читайте в статье:
https://shagabutdinov.ru/tpost/47brblc2e1-novaya-funktsiya-excel-i-google-tablits


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы @namokonov

Пара примеров, как сделать ваш GAS-скрипт быстрее

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

1) Убирайте методы из глобальной области видимости

Смотрите на скриншот: userStates будет инициализироваться при запуске любого скрипта, даже того, где он не используется.

Это отнимет у скрипта некоторое время ещё до старта.

Оставляйте в глобальной области видимости только текстовые строки, числа, объекты или массивы, но не вызывайте методы.

2) Минимизируйте количество обращений к таблице

Если записываете или получаете данные — старайтесь делать это за одно действие: формируйте массив в скрипте и вставляйте его с помощью setValues().

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

📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы @namokonov

🎉 Давно думаете, как войти в мир данных, но не знаете, с чего начать?

Регистрируйтесь на бесплатный мини-курс по SQL! Это ключевой навык для работы с данными, который поможет вам открыть двери в перспективную профессию аналитика.

На курсе вас ждет:
✔️ 5 часов обучения, чтобы уверенно начать работать с SQL,
✔️ Пошаговые видео-разборы от эксперта, которые помогут во всем разобраться,
✔️ Поддержка куратора в чате и нетворкинг с другими участниками,
✔️ 30+ практических заданий для закрепления навыков,
✔️ Финальный кейс, который вы решите самостоятельно,
✔️ Сертификат о прохождении курса.

Обучение на курсе стартует 13 августа — успейте зарегистрироваться по ссылке ниже.

🎁 Всех ждет подарок — гайд «20 фреймворков аналитика» после регистрации!
Это супер-конспект на 133 страницы с описанием самых главных шаблонов и инструментов для анализа и поиска решений от наших экспертов!


🎁 Также все участники курса получат грант на дальнейшее обучение аналитики!

SQL — это старт к новой карьере в аналитике или важный навык, который сделает вас ценным специалистом в своей сфере.
Регистрируйтесь до 13 августа по ссылке

Сколько пятниц, 13 в периоде?

Можно вычислить это такой формулой:

=ArrayFormula(СЧЁТЕСЛИ(ТЕКСТ(SEQUENCE(конец периода-начало периода+1;1;B1);"D DDD");"13 пт"))

=ArrayFormula(COUNTIF(TEXT(SEQUENCE(B2-B1+1,1,B1),"D DDD"),"13 пт"))

С другими региональными настройками будет иначе, например, для американских — "13 fri", а не "13 пт".
Ваши варианты формул приветствуются!

P.S. А если нужны просто все вторники или другие дни недели в периоде?
Кол-во вторников в месяце:

=ЧИСТРАБДНИ.МЕЖД(КОНМЕСЯЦА(дата;-1)+1;КОНМЕСЯЦА(дата;0);"1011111")