Минус на минус дает число: превращаем текст в число для дальнейших вычислений
Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки с помощью REGEXREPLACE (или последние/первые цифры с помощью функций RIGHT / LEFT), они не будут готовы к употреблению сразу — это будут текстовые значения. См. сумму в столбце B на скриншоте или в таблице по ссылке — там ноль, хотя внешне вроде бы числа извлеклись правильные.
Как превратить текст в число в Google Таблицах (и в Excel тоже)?
1. С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения
P.S. Если вы сразу используете число в вычислении, допустим, вам нужно его прибавить к другому — не добавляйте +, а потом два минуса. Достаточно два минуса — и текст преобразуется в число, и сложение случится.
Например:
Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки с помощью REGEXREPLACE (или последние/первые цифры с помощью функций RIGHT / LEFT), они не будут готовы к употреблению сразу — это будут текстовые значения. См. сумму в столбце B на скриншоте или в таблице по ссылке — там ноль, хотя внешне вроде бы числа извлеклись правильные.
Как превратить текст в число в Google Таблицах (и в Excel тоже)?
1. С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения
=--REGEXEXTRACT(...)
2. С помощью функции VALUE/ЗНАЧЕН. =ЗНАЧЕН(REGEXEXTRACT(...))3. С помощью еще какой-нибудь математической операции, не меняющей значение, например, умножения на единицу
=REGEXEXTRACT(...)*1
P.S. Если вы сразу используете число в вычислении, допустим, вам нужно его прибавить к другому — не добавляйте +, а потом два минуса. Достаточно два минуса — и текст преобразуется в число, и сложение случится.
Например:
=A2--B2
👍24🔥18🫡5❤2👎1😎1
Схватка двух... ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP. Старая и новая функции для объединения таблиц (поиска текста и чисел)
— VLOOKUP ищет только в первом столбце таблицы, а XLOOKUP ссылается на отдельные столбцы (где ищем и откуда возвращаем данные) — ей все равно, какая структура данных. Соответственно, VLOOKUP слетит, если вставить столбец между первым и тем, откуда возвращаем данные (потому что номер возвращаемого столбца зашит как константа). А XLOOKUP, ссылающаяся на столбцы, будет работать при вставке новых столбцов. И может возвращать данные, которые левее, чем столбец для поиска.
— XLOOKUP по умолчанию ищет текст (точное совпадение), а VLOOKUP — ближайшее наименьшее число.
— В режиме поиска числа (пятый аргумент, равный единице или минус единице) XLOOKUP не требует сортировки данных и умеет искать и ближайшее наибольшее тоже;
— У XLOOKUP есть отдельный необязательный (четвертый) аргумент для замены ошибок (когда ничего не найдено) на другое значение. А в случае VLOOKUP для этого дела надо добавлять отдельную функцию IFNA.
— VLOOKUP умеет работать с символами подстановки (* и ?) по умолчанию, а XLOOKUP — нет. Чтобы использовать символы подстановки в XLOOKUP, нужно задать пятый аргумент match_mode равным 2 (по умолчанию 0 - точный поиск).
— VLOOKUP умеет только вертикально (столбцы), для горизонтального поиска используется HLOOKUP / ГПР. XLOOKUP может работать и со строками, и со столбцами.
— VLOOKUP всегда ищет сверху вниз (то есть при 2 и более совпадениях найдет первое), а XLOOKUP умеет и снизу вверх (то есть найдет последнее) - для этого задаем последний аргумент search_mode равным -1.
— В Excel XLOOKUP есть только в 2021 / 365, то есть при скачивании таблицы в формате XLSX функция не будет работать в 2019 и более ранних версиях Excel.
___
Подборка постов про VLOOKUP
Поиск последнего значения с помощью XLOOKUP
💥 Другие наши посты можно найти в оглавлении канала.
=VLOOKUP(что ищем; таблица, в которой поиск идет в первом столбце; номер столбца, из которого забираем данные; [режим поиска])
=XLOOKUP (что ищем; в каком столбце ищем; из какого столбца забираем; [на что заменяем ошибку]; [ищем текст/число/текст с подстановочными символами] ; [ищем сверху или снизу])
— VLOOKUP ищет только в первом столбце таблицы, а XLOOKUP ссылается на отдельные столбцы (где ищем и откуда возвращаем данные) — ей все равно, какая структура данных. Соответственно, VLOOKUP слетит, если вставить столбец между первым и тем, откуда возвращаем данные (потому что номер возвращаемого столбца зашит как константа). А XLOOKUP, ссылающаяся на столбцы, будет работать при вставке новых столбцов. И может возвращать данные, которые левее, чем столбец для поиска.
— XLOOKUP по умолчанию ищет текст (точное совпадение), а VLOOKUP — ближайшее наименьшее число.
— В режиме поиска числа (пятый аргумент, равный единице или минус единице) XLOOKUP не требует сортировки данных и умеет искать и ближайшее наибольшее тоже;
— У XLOOKUP есть отдельный необязательный (четвертый) аргумент для замены ошибок (когда ничего не найдено) на другое значение. А в случае VLOOKUP для этого дела надо добавлять отдельную функцию IFNA.
— VLOOKUP умеет работать с символами подстановки (* и ?) по умолчанию, а XLOOKUP — нет. Чтобы использовать символы подстановки в XLOOKUP, нужно задать пятый аргумент match_mode равным 2 (по умолчанию 0 - точный поиск).
— VLOOKUP умеет только вертикально (столбцы), для горизонтального поиска используется HLOOKUP / ГПР. XLOOKUP может работать и со строками, и со столбцами.
— VLOOKUP всегда ищет сверху вниз (то есть при 2 и более совпадениях найдет первое), а XLOOKUP умеет и снизу вверх (то есть найдет последнее) - для этого задаем последний аргумент search_mode равным -1.
— В Excel XLOOKUP есть только в 2021 / 365, то есть при скачивании таблицы в формате XLSX функция не будет работать в 2019 и более ранних версиях Excel.
___
Подборка постов про VLOOKUP
Поиск последнего значения с помощью XLOOKUP
💥 Другие наши посты можно найти в оглавлении канала.
Telegram
Google Таблицы
ВПР
Друзья, ВПР - могущественная функция, чего только с помощью неё нельзя сделать.
На нашем канале мы написали много постов про ВПР, давайте вспомним их:
— Последний аргумент ВПР или ВПР с интервальным просмотром = 1: www.tgoop.com/google_sheets/13
— ВПР с ПОИСКПОЗ…
Друзья, ВПР - могущественная функция, чего только с помощью неё нельзя сделать.
На нашем канале мы написали много постов про ВПР, давайте вспомним их:
— Последний аргумент ВПР или ВПР с интервальным просмотром = 1: www.tgoop.com/google_sheets/13
— ВПР с ПОИСКПОЗ…
👍50❤9✍3🎄3👎1💊1
XLOOKUP — двойной, пожалуйста
Поиск по двум критериям (в строках и столбцах) — обычно это решается через сочетание INDEX / ИНДЕКС и MATCH / ПОИСКПОЗ.
Но ПРОСМОТРОМ (да не простым, а икс) тоже можно.
В нашем примере ищем в матрице оценку на основе двух оценок — профессиональных и поведенческих компетенций.
Сначала одной функцией получаем массив значений для профессиональной оценки (первого критерия) — у нас это C3:C6 (оранжевое) для первого сотрудника в списке.
Потом другой уже в этом массиве ищем значение, соответствующее второму критерию — поведенческой оценке (красное).
В итоге:
Поиск по двум критериям (в строках и столбцах) — обычно это решается через сочетание INDEX / ИНДЕКС и MATCH / ПОИСКПОЗ.
Но ПРОСМОТРОМ (да не простым, а икс) тоже можно.
В нашем примере ищем в матрице оценку на основе двух оценок — профессиональных и поведенческих компетенций.
Сначала одной функцией получаем массив значений для профессиональной оценки (первого критерия) — у нас это C3:C6 (оранжевое) для первого сотрудника в списке.
Потом другой уже в этом массиве ищем значение, соответствующее второму критерию — поведенческой оценке (красное).
В итоге:
=ПРОСМОТРX(критерий1;где ищем критерий1; ПРОСМОТРX(критерий2;где ищем критерий2;двумерный массив))Таблица с примером
👍44🔥12❤10👾2👎1
Убираем пустые ячейки из столбца / диапазона элегантно
Друзья, а у нас ведь не только сложные формулы, но и простые.
Нужно убрать пустые ячейки из диапазона и превратить его в строку или в столбец?
На помощь придут новые функции:
Поэтому теперь вам необязательно использовать конструкцию вида
Привыкайте к новым функциям и используйте!
---
Друзья, если читали или смотрели хорошие материалы по Таблицам / скриптам – а поделитесь ссылками в комментариях.
Если случится хорошая подборка, то мы ей обязательно поделимся в следующих постах.
Друзья, а у нас ведь не только сложные формулы, но и простые.
Нужно убрать пустые ячейки из диапазона и превратить его в строку или в столбец?
На помощь придут новые функции:
=torow(диапазон; 1)
=tocol(диапазон; 1)
Поэтому теперь вам необязательно использовать конструкцию вида
=filter(диапазон; один столбец из диапазона <>"")
, всё можно сделать проще. Привыкайте к новым функциям и используйте!
---
Друзья, если читали или смотрели хорошие материалы по Таблицам / скриптам – а поделитесь ссылками в комментариях.
Если случится хорошая подборка, то мы ей обязательно поделимся в следующих постах.
🔥30👍15❤3🍾2👎1👾1
@zadavai_vopros_bot
Друзья, а мы обновили модель, которая рисует картинки в нашем ИИ-боте, теперь там
Пользуйтесь, напоминаю, что в боте 10 бесплатных запросов каждый месяц, нужно больше – 500 рублей, их можно также оплатить через бота.
Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом / Как писать промпты!
Друзья, а мы обновили модель, которая рисует картинки в нашем ИИ-боте, теперь там
dall-e-3
.Пользуйтесь, напоминаю, что в боте 10 бесплатных запросов каждый месяц, нужно больше – 500 рублей, их можно также оплатить через бота.
Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом / Как писать промпты!
🔥12👍5👾4👎2
Давайте продолжим тему ПРОСМОТРов. И поговорим о старой функции LOOKUP / ПРОСМОТР.
Функция по синтаксису похожа на новую XLOOKUP / ПРОСМОТРX, о которой мы писали выше. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы :она требует постоянной сортировки данных, не особо подходит для поиска текста.
Поэтому лучше использовать VLOOKUP, XLOOKUP или INDEX+MATCH. Во всяком случае, для объединения таблиц по текстовому ключу.
Но все же в некоторых экзотических случаях LOOKUP используют до сих пор. Например, для нечеткого текстового поиска, когда нужно находить в названиях какое-то слово и заменять все значения с этим слово на одно и то же типовое (исправлять разные наименования компаний или товаров, например) — такой пример есть в статье.
https://teletype.in/@renat_shagabutdinov/wFymDX7fAN4
Функция по синтаксису похожа на новую XLOOKUP / ПРОСМОТРX, о которой мы писали выше. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы :она требует постоянной сортировки данных, не особо подходит для поиска текста.
Поэтому лучше использовать VLOOKUP, XLOOKUP или INDEX+MATCH. Во всяком случае, для объединения таблиц по текстовому ключу.
Но все же в некоторых экзотических случаях LOOKUP используют до сих пор. Например, для нечеткого текстового поиска, когда нужно находить в названиях какое-то слово и заменять все значения с этим слово на одно и то же типовое (исправлять разные наименования компаний или товаров, например) — такой пример есть в статье.
https://teletype.in/@renat_shagabutdinov/wFymDX7fAN4
Teletype
ПРОСМОТР (LOOKUP), да не X
Несколько слов о старой функции LOOKUP / ПРОСМОТР. Все сказанное актуально и для Google Таблиц, и для Excel (и для отечественного Р7...
👍10👾4🦄2❤1👎1
Немного формульно-датового многоэтажного ада под конец года. Выдаем одной формулой все недели года в формате "30 янв-5 фев"
В деле почти все функции Google Таблиц 🤠LET, LAMBDA, QUERY, SEQUENCE, NETWORKDAY.INTL, MIN, DATE, MONTH, YEAR, DAYS, WEEKNUM, WEEKDAY, BYROW, TRANSPOSE, CHOOSECOLS, TEXT, LEFT, REGEXREPLACE, IF, ARRAYFORMULA в тех или иных лютых комбинациях
Друзья, наверняка у вас есть отчеты и планы, где в заголовках идут недели. Если для вас привычен формат с первым и последним днем недели, забирайте формулы от нас и маэстро нашего чата (Михаил, спасибо!) — они выдадут все недели года одной строкой в таком формате.
Три варианта в таблице по ссылке.
— в первом случае недели идут с первого понедельника, последняя неделя будет с первыми днями следующего года ("30 дек - 5 янв"). Выдаются недели за текущий год (можете поменять
— во втором случае недели тоже с первого понедельника, последняя неделя до 31 декабря ("30-31 дек"), год выбирается в ячейке
— в третьем случае все недели (включая ту, что до первого понедельника), последняя до 31 декабря, год в ячейке
С наступающим Новым годом! Пусть в этих самых отчетах у вас все будет хорошо в следующем году 🤠Спасибо, что читаете нас!
В деле почти все функции Google Таблиц 🤠LET, LAMBDA, QUERY, SEQUENCE, NETWORKDAY.INTL, MIN, DATE, MONTH, YEAR, DAYS, WEEKNUM, WEEKDAY, BYROW, TRANSPOSE, CHOOSECOLS, TEXT, LEFT, REGEXREPLACE, IF, ARRAYFORMULA в тех или иных лютых комбинациях
Друзья, наверняка у вас есть отчеты и планы, где в заголовках идут недели. Если для вас привычен формат с первым и последним днем недели, забирайте формулы от нас и маэстро нашего чата (Михаил, спасибо!) — они выдадут все недели года одной строкой в таком формате.
Три варианта в таблице по ссылке.
— в первом случае недели идут с первого понедельника, последняя неделя будет с первыми днями следующего года ("30 дек - 5 янв"). Выдаются недели за текущий год (можете поменять
YEAR(TODAY())
на фиксированный год, если нужно)— во втором случае недели тоже с первого понедельника, последняя неделя до 31 декабря ("30-31 дек"), год выбирается в ячейке
— в третьем случае все недели (включая ту, что до первого понедельника), последняя до 31 декабря, год в ячейке
С наступающим Новым годом! Пусть в этих самых отчетах у вас все будет хорошо в следующем году 🤠Спасибо, что читаете нас!
🔥33👍22🎄9❤3👎1
This media is not supported in your browser
VIEW IN TELEGRAM
Автоматизация елки
На новый год принято ставить елку, мы не будем отходить от этой традиции и установим на нашем канале нарядную Google-Табличную ель.
В ветках у неё формула, обеспечивающая для каждой ячейки свой цв
А зажжет ёлку скрипт, он при каждом открытии Таблицы, сто раз, в цикле, вставит в яче
Таблица с ёлкой
Друзья, с наступающим новым годом, берегите себя, встречаемся здесь же в 2024! Счастья и мира!
PS А еще у старожила нашего сообщества Каната сегодня день рождения, поздравляем!🌟
---
🌲 Оглавление канала: ты-дыц
🌲 Самый табличный чат на свете: бадабум
На новый год принято ставить елку, мы не будем отходить от этой традиции и установим на нашем канале нарядную Google-Табличную ель.
В ветках у неё формула, обеспечивающая для каждой ячейки свой цв
ет:
=INDEX({"🟠";"🟡";"🟢";"🔵";"🟣";"🟤"};RANDBETWEEN(1;6
);1)А зажжет ёлку скрипт, он при каждом открытии Таблицы, сто раз, в цикле, вставит в яче
йк
у A1 число, тем самым запуская пересчёт формул, в частности RANDBET
WEEN:
function onOpen() {
for (var i = 0; i < 100; i++) {
SpreadsheetApp.getActiveSheet().getRange(1, 1).setValue(i);
SpreadsheetApp.flush();
};
};
Таблица с ёлкой
Друзья, с наступающим новым годом, берегите себя, встречаемся здесь же в 2024! Счастья и мира!
PS А еще у старожила нашего сообщества Каната сегодня день рождения, поздравляем!
---
🌲 Оглавление канала: ты-дыц
🌲 Самый табличный чат на свете: бадабум
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥76🎄39👍14☃8❤8😱2🍾2👎1
Бусты
Друзья, всех с наступившим! А давайте попробуем собрать для нашего канала бусты?
Будем иногда подсвечивать некоторые посты с помощью сторис:
https://www.tgoop.com/google_sheets?boost
Друзья, всех с наступившим! А давайте попробуем собрать для нашего канала бусты?
Будем иногда подсвечивать некоторые посты с помощью сторис:
https://www.tgoop.com/google_sheets?boost
👍20❤8👎4👾4🖕1💘1
Табличное, мощное, лучшее за 2023 год
То, про что мы хотим вам напомнить из репертуара прошедшего года.
Оглавление Таблицы скриптами
www.tgoop.com/google_sheets/1074
Достаем изображения из Google и Яндекса формулой
www.tgoop.com/google_sheets/1091
И из Рамблера
www.tgoop.com/google_sheets/1296
Новые функции в Google Sheets - статья Михаила Смирнова
https://telegra.ph/Novye-funkcii-v-Google-Sheets-2023-02-02-06-06
Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)
teletype.in/@renat_shagabutdinov/lambdasheets
Главная статья импорта. Руководство по функции IMPORTRANGE
https://teletype.in/@renat_shagabutdinov/importrange
Выводим все даты текущего месяца формулой
www.tgoop.com/google_sheets/1113
Импорт данных из всех Google Таблиц в списке с помощью формул
teletype.in/@renat_shagabutdinov/IMPORT-LAMBDA
Телеграм + Google Таблицы, наши решения
www.tgoop.com/google_sheets/1141
Запрашиваем из Таблиц ИНН и получаем название компании
www.tgoop.com/google_sheets/1159
Вычисляемое поле в сводной: умножаем сумму на одно значение
www.tgoop.com/google_sheets/1233
Случайная жеребьевка команд с условием
www.tgoop.com/google_sheets/1250
Парсим Ютуб ⚡️
www.tgoop.com/google_sheets/1285
ВПР-им с разных листов
www.tgoop.com/google_sheets/1306
Схватка двух... ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP
www.tgoop.com/google_sheets/1315
То, про что мы хотим вам напомнить из репертуара прошедшего года.
Оглавление Таблицы скриптами
www.tgoop.com/google_sheets/1074
Достаем изображения из Google и Яндекса формулой
www.tgoop.com/google_sheets/1091
И из Рамблера
www.tgoop.com/google_sheets/1296
Новые функции в Google Sheets - статья Михаила Смирнова
https://telegra.ph/Novye-funkcii-v-Google-Sheets-2023-02-02-06-06
Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)
teletype.in/@renat_shagabutdinov/lambdasheets
Главная статья импорта. Руководство по функции IMPORTRANGE
https://teletype.in/@renat_shagabutdinov/importrange
Выводим все даты текущего месяца формулой
www.tgoop.com/google_sheets/1113
Импорт данных из всех Google Таблиц в списке с помощью формул
teletype.in/@renat_shagabutdinov/IMPORT-LAMBDA
Телеграм + Google Таблицы, наши решения
www.tgoop.com/google_sheets/1141
Запрашиваем из Таблиц ИНН и получаем название компании
www.tgoop.com/google_sheets/1159
Вычисляемое поле в сводной: умножаем сумму на одно значение
www.tgoop.com/google_sheets/1233
Случайная жеребьевка команд с условием
www.tgoop.com/google_sheets/1250
Парсим Ютуб ⚡️
www.tgoop.com/google_sheets/1285
ВПР-им с разных листов
www.tgoop.com/google_sheets/1306
Схватка двух... ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP
www.tgoop.com/google_sheets/1315
Telegram
Google Таблицы
Оглавление Таблицы скриптами
Друзья, привет!
В первом посте 2023 года решим классическую проблему – выведем список всех листов Таблицы вместе с ссылками на каждый лист.
Базовый скрипт.
function sheets() {
//текущая таблица
const ss = Spreadsheet…
Друзья, привет!
В первом посте 2023 года решим классическую проблему – выведем список всех листов Таблицы вместе с ссылками на каждый лист.
Базовый скрипт.
function sheets() {
//текущая таблица
const ss = Spreadsheet…
🔥42❤25👍7👎1👌1🤓1
Сегодня про то, как Google Таблицы нам строить и жить помогают
Личный чек-лист: очень простая, но мощная штука.
Как говорит Скотт Адамс, системы лучше целей. Да и не только он об этом говорит. Действительно, если уделять обучению или делу немного времени, но каждый день, результаты будут мощные. Фокусируйтесь не на большой цели, а на повседневной работе.
Завести чек-лист в Google Таблице проще некуда. Просто флажки с теми пунктами, которые нужно выполнять ежедневно — полчаса на изучение той или иной темы, ежедневные шаги или тренировки, работа над проектами, отказ от сладкого. Да, каждый день отмечать все не получится. Но сам настрой на это и наглядное представление того, как вы идете, помогает сосредоточиться и делать больше важного.
То, что уже на автопилоте — ежедневный бег, например, а у вас может быть что-то другое — уже можно не заносить, смысла нет. Важно то, что можно забыть сделать.
Традиционно напоминаем, что флажки можно включать и выключать пробелом.
Создавайте личную копию шаблона, настраивайте для себя и используйте! В шаблоне зеленым горит текущая дата, серым — выходные (давайте немного расслабляться в эти дни!):
https://docs.google.com/spreadsheets/d/11pAWMxaQfoQcr1exqwsmCha_Xxm7BUE_ind6QVvispU/copy
Личный чек-лист: очень простая, но мощная штука.
Как говорит Скотт Адамс, системы лучше целей. Да и не только он об этом говорит. Действительно, если уделять обучению или делу немного времени, но каждый день, результаты будут мощные. Фокусируйтесь не на большой цели, а на повседневной работе.
Завести чек-лист в Google Таблице проще некуда. Просто флажки с теми пунктами, которые нужно выполнять ежедневно — полчаса на изучение той или иной темы, ежедневные шаги или тренировки, работа над проектами, отказ от сладкого. Да, каждый день отмечать все не получится. Но сам настрой на это и наглядное представление того, как вы идете, помогает сосредоточиться и делать больше важного.
То, что уже на автопилоте — ежедневный бег, например, а у вас может быть что-то другое — уже можно не заносить, смысла нет. Важно то, что можно забыть сделать.
Традиционно напоминаем, что флажки можно включать и выключать пробелом.
Создавайте личную копию шаблона, настраивайте для себя и используйте! В шаблоне зеленым горит текущая дата, серым — выходные (давайте немного расслабляться в эти дни!):
https://docs.google.com/spreadsheets/d/11pAWMxaQfoQcr1exqwsmCha_Xxm7BUE_ind6QVvispU/copy
👍42❤8💘4😎3🔥2👎1🤣1
Превращаем дату-как-текст в дату
Для этого есть целая функция — DATEVALUE / ДАТАЗНАЧ.
Любой текст она не осилит (и не только такой вольный текст, как "Позавчера" или "первый день года", но даже "17 октябрь 2022", увы — см 4 строку на скриншоте). Но текст, максимально похожий на стандартные даты, превратит в настоящие. Если года в текстовой строке нет — будет использован текущий (см строку 6). Если есть время — останется только дата (строка 8).
Альтернатива — умножить дату на единицу.
Для этого есть целая функция — DATEVALUE / ДАТАЗНАЧ.
Любой текст она не осилит (и не только такой вольный текст, как "Позавчера" или "первый день года", но даже "17 октябрь 2022", увы — см 4 строку на скриншоте). Но текст, максимально похожий на стандартные даты, превратит в настоящие. Если года в текстовой строке нет — будет использован текущий (см строку 6). Если есть время — останется только дата (строка 8).
Альтернатива — умножить дату на единицу.
👍27🔥9👎1
Суммируем каждую N-ую ячейку
Допустим, надо суммировать только каждый N столбец.
Можно использовать такую формулу:
Здесь мы проверяем, нулевой ли остаток при делении номера столбца на N — если нулевой, значит ,столбец нам нужен, и вся следующая конструкция:
будет в таком случае возвращать TRUE (1).
А для ненужных нам столбцов FALSE (0).
И получим мы на выходе массив {FALSE, TRUE, ...}
который умножим на наш диапазон. SUMPRODUCT потом все это дело просуммирует.
В нашем примере каждый четвертый столбец просуммируем так:
Вычитаем двойку мы здесь потому, что наш диапазон начинается в C, а не в A — отступаем два этих столбца, чтобы первый столбец диапазона был первым и по порядку в наших расчетах.
Допустим, надо суммировать только каждый 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 — отступаем два этих столбца, чтобы первый столбец диапазона был первым и по порядку в наших расчетах.
👍36👎1👾1
This media is not supported in your browser
VIEW IN TELEGRAM
Создаём QR-код в Таблице и Документе
Друзья, привет! В Таблицах QR-код создаётся формулой, которую нужно ввести в ячейку, как это сделать, мы рассказывали вот здесь: https://www.tgoop.com/google_sheets/912
А чтобы добавить QR в Google Документ нужен скрипт, например тот, который будет ниже.
Что скрипт делает:
1) ищет в тексте документа "###"
2) находит и вставляет вместо "###" QR-код, созданный из
3) изменяет размеры изображения на 75 и 75 пикселей
4) и добавляет к самой картинке исходную ссылку
PS Конечно, вы можете собрать ссылку из того, что есть в документе, например, искать с помощью регулярного выражения номер договора, соединять его с "
Документ со скриптом
Друзья, привет! В Таблицах QR-код создаётся формулой, которую нужно ввести в ячейку, как это сделать, мы рассказывали вот здесь: https://www.tgoop.com/google_sheets/912
А чтобы добавить QR в Google Документ нужен скрипт, например тот, который будет ниже.
Что скрипт делает:
1) ищет в тексте документа "###"
2) находит и вставляет вместо "###" QR-код, созданный из
ссылки www.tgoop.com/googl
e_sheets3) изменяет размеры изображения на 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 = 'www.tgoop.com/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-код.Документ со скриптом
👍34❤6🔥4👎1
Достаём курс евро из Московской Биржи.
Друзья, привет, Михаил поделился ссылкой, по которой можно вытянуть текущий курс евро к рублю из 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
Что с этой ссылкой делать дальше? Доставать данные скриптом в Таблицу, например:
```copy
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);
};
```Наш скрипт достанет либо цену последней сделки, либо, если её нет, например, не было торгов - последнюю актуальную цену.
А еще у нас есть большая статья от все того же кудесника Михаила, о том, как достать в Таблицу разные курсы формулами и скриптами.
Друзья, привет, Михаил поделился ссылкой, по которой можно вытянуть текущий курс евро к рублю из 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
Что с этой ссылкой делать дальше? Доставать данные скриптом в Таблицу, например:
```copy
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);
};
```Наш скрипт достанет либо цену последней сделки, либо, если её нет, например, не было торгов - последнюю актуальную цену.
А еще у нас есть большая статья от все того же кудесника Михаила, о том, как достать в Таблицу разные курсы формулами и скриптами.
👍10🔥10❤🔥2👎2❤1💋1
Вытаскиваем utm из ссылки (а точнее — все, что после знака вопроса)
Для этого можно воспользоваться следующим регулярным выражением:
Извлекаем выражение (круглые скобки) из не-пробелов, идущее после знака вопроса (\? = знак вопроса, а без слеша это был бы квантификатор (ноль или один символ)
Вся функция будет такая:
Хорошее про регулярные выражения:
Компактная памятка про регулярки от Vitalich
Извлекаем числа, едим пончики
Волшебство "найти и заменить". Приводим mm-dd к dd-mm
Для этого можно воспользоваться следующим регулярным выражением:
\?(\S+)
Извлекаем выражение (круглые скобки) из не-пробелов, идущее после знака вопроса (\? = знак вопроса, а без слеша это был бы квантификатор (ноль или один символ)
Вся функция будет такая:
=REGEXEXTRACT(A2;"\?(\S+)")
Хорошее про регулярные выражения:
Компактная памятка про регулярки от Vitalich
Извлекаем числа, едим пончики
Волшебство "найти и заменить". Приводим mm-dd к dd-mm
👍25❤6🔥4👎1
Google Таблицы
Вытаскиваем utm из ссылки (а точнее — все, что после знака вопроса) Для этого можно воспользоваться следующим регулярным выражением: \?(\S+) Извлекаем выражение (круглые скобки) из не-пробелов, идущее после знака вопроса (\? = знак вопроса, а без слеша это…
UTM-метки. Извлекаем все параметры и их значения формулой
Справедливый комментарий к прошлому посту: удобнее получать отдельные параметры (source, medium, etc.), а не все метки разом.
Можно так:
1. Взять все из ссылки после знака вопроса (с помощью комбинации текстовых функций или INDEX+SPLIT или REGEXEXTRACT — в примере последний вариант). Получится хвост ссылки с метками, где параметры и их значения разделены знаком "равно" (=), а пары разделены амперсандом (&).
2. Разделить их на пары по амперсанду с помощью SPLIT .
3. Транспонировать, чтобы в итоговом результате было два столбца — с параметрами и значениями. Функция TRANSPOSE.
4. Разделить по знаку "равно" с помощью SPLIT. Засунуть ее в ArrayFormula, ибо иначе получим результат только для первой пары.
Есть идеи по сокращению и оптимизации формулы? Добро пожаловать в комменты!
Справедливый комментарий к прошлому посту: удобнее получать отдельные параметры (source, medium, etc.), а не все метки разом.
Можно так:
1. Взять все из ссылки после знака вопроса (с помощью комбинации текстовых функций или INDEX+SPLIT или REGEXEXTRACT — в примере последний вариант). Получится хвост ссылки с метками, где параметры и их значения разделены знаком "равно" (=), а пары разделены амперсандом (&).
2. Разделить их на пары по амперсанду с помощью SPLIT .
3. Транспонировать, чтобы в итоговом результате было два столбца — с параметрами и значениями. Функция TRANSPOSE.
4. Разделить по знаку "равно" с помощью SPLIT. Засунуть ее в ArrayFormula, ибо иначе получим результат только для первой пары.
Есть идеи по сокращению и оптимизации формулы? Добро пожаловать в комменты!
=ArrayFormula(SPLIT(TRANSPOSE(SPLIT(REGEXEXTRACT(A2;"\?(\S+)");"&"));"="))
👍19🔥6👎1
Эх, дороги..
Друзья, к нам недавно пришел наш клиент, с такой задачей:
Задача от классной гитарной школы Guitardo
1) есть первая точка (долгота и широта)
2) есть еще сто точек, также с долготой и широтой
Нужно к первой точке найти ближайшую по расстоянию следующую точку, к этой точке - ближайшую следующую и так далее, пока точки не закончатся. Таким образом, в конце, мы получаем список точек от первой до последней с минимальным расстоянием друг от друга.
Как решили
Скриптом, конечно. В основе функция, которая ищет расстояние по прямой, ей нужно задать долготу и широту двух точек. А помогает функции скрипт, который начинает от первой точки, сопоставляет ее с остальными, находит ближайшую точку, дальше сопоставляет с остальными её и до конца, пока точки не закончатся.
Таблица со скриптом
Друзья, к нам недавно пришел наш клиент, с такой задачей:
Задача от классной гитарной школы Guitardo
1) есть первая точка (долгота и широта)
2) есть еще сто точек, также с долготой и широтой
Нужно к первой точке найти ближайшую по расстоянию следующую точку, к этой точке - ближайшую следующую и так далее, пока точки не закончатся. Таким образом, в конце, мы получаем список точек от первой до последней с минимальным расстоянием друг от друга.
Как решили
Скриптом, конечно. В основе функция, которая ищет расстояние по прямой, ей нужно задать долготу и широту двух точек. А помогает функции скрипт, который начинает от первой точки, сопоставляет ее с остальными, находит ближайшую точку, дальше сопоставляет с остальными её и до конца, пока точки не закончатся.
Таблица со скриптом
🔥40👍9❤8🤔2👾2👎1
121 из 200!
Друзья, доброе утро, а давайте поднажмем немного, нам не хватает 79 бустов. Пожалуйста!
Обещаем, что сторис будем создавать только по делу (ну либо если придумаем очень хорошую шутку 🙈)
https://www.tgoop.com/google_sheets?boost
❤️
Друзья, доброе утро, а давайте поднажмем немного, нам не хватает 79 бустов. Пожалуйста!
Обещаем, что сторис будем создавать только по делу (ну либо если придумаем очень хорошую шутку 🙈)
https://www.tgoop.com/google_sheets?boost
❤️
👍30🔥7🎉4🥴4👎3👾1