Именованные функции (Named functions)
Если в Excel можно создавать функции (без макросов) с помощью LAMBDA и диспетчера имен, то в Таблицах есть интерфейс для этого — Named functions (именованные функции) в меню "Данные".
Вашему вниманию очень короткое видео (3 минуты, ибо там все просто) про создание своих функций таким образом. С примером функции, обрабатывающей отдельные ячейки и функции, обрабатывающей диапазоны (для такого придется засунуть в именованную функцию LAMBDA и одну из вспомогательных функций, например, MAP).
https://www.youtube.com/watch?v=ASPf1LdDoDQ
Это один из 95 уроков курса "Драйв. Гугл Драйв" в МИФе. В курсе не только Таблицы, но на них упор, конечно❤️
Если в Excel можно создавать функции (без макросов) с помощью LAMBDA и диспетчера имен, то в Таблицах есть интерфейс для этого — Named functions (именованные функции) в меню "Данные".
Вашему вниманию очень короткое видео (3 минуты, ибо там все просто) про создание своих функций таким образом. С примером функции, обрабатывающей отдельные ячейки и функции, обрабатывающей диапазоны (для такого придется засунуть в именованную функцию LAMBDA и одну из вспомогательных функций, например, MAP).
https://www.youtube.com/watch?v=ASPf1LdDoDQ
Это один из 95 уроков курса "Драйв. Гугл Драйв" в МИФе. В курсе не только Таблицы, но на них упор, конечно❤️
YouTube
Урок 12.1 Бонус: именованные функции и LAMBDA
+НАЙТИ МИФ:Наши книги: https://mif.to/vseknigiНаши курсы: https://mif.to/vsekursyВКонтакте: https://vk.com/mifbooksTelegram: https://tttttt.me/mifbooks
Уникальные пары значений
Нам нужны уникальные пары значений. Например, пары "Клиент" — "Продукт" (полный список клиентов и продуктов — кто что покупал)
Если столбцы рядом — то можно просто сослаться функцией UNIQUE на эти два столбца.
Если они не рядом — то предварительно выбрать их функцией CHOOSECOLS. Например, если нам нужны первый и третий столбцы:
Нам нужны уникальные пары значений. Например, пары "Клиент" — "Продукт" (полный список клиентов и продуктов — кто что покупал)
Если столбцы рядом — то можно просто сослаться функцией UNIQUE на эти два столбца.
Если они не рядом — то предварительно выбрать их функцией CHOOSECOLS. Например, если нам нужны первый и третий столбцы:
=UNIQUE(CHOOSECOLS(диапазон;1;3))Отбираем по ошибке #N/A строки в QUERY и FILTER
Друзья, иногда в наших датасетах может быть ошибка #N/A (not available или значение не доступно).
Показываем, как отобрать строки или значения с ошибкой и без:
1. Для QUERY, отбираем строки с N/A:
2. Для QUERY, отбираем строки без N/A:
3. Чтобы отобрать N/A в FILTER:
4. Ну и отбираем всё, кроме N/A в FILTER:
Друзья, иногда в наших датасетах может быть ошибка #N/A (not available или значение не доступно).
Показываем, как отобрать строки или значения с ошибкой и без:
1. Для QUERY, отбираем строки с N/A:
=QUERY(A1:A12;"where Col1 = '#N/A'";0)
2. Для QUERY, отбираем строки без N/A:
=QUERY(A1:A12;"where Col1 != '#N/A'";0)
3. Чтобы отобрать N/A в FILTER:
=FILTER(A1:A12;ISNA(A1:A12))
4. Ну и отбираем всё, кроме N/A в FILTER:
=FILTER(A1:A12;NOT(ISNA(A1:A12)))ВПР-им с разных листов
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит так:
Нам нужно подставлять внутри апострофов названия разных листов.
Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:
Чтобы превратить полученную текстовую строку в ссылку, используем функцию INDIRECT - она ровно для этого и используется.
И отправляем это внутрь ВПР'а как второй аргумент:
Ссылка на таблицу с примером
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит так:
='Москва'!A:B
Нам нужно подставлять внутри апострофов названия разных листов.
Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:
="'" & ячейка с названием листа & "'!диапазон"
Чтобы превратить полученную текстовую строку в ссылку, используем функцию INDIRECT - она ровно для этого и используется.
=INDIRECT("'" & ячейка с названием листа & "'!диапазон")
И отправляем это внутрь ВПР'а как второй аргумент:
=VLOOKUP(значение для поиска; INDIRECT("
) ; номер столбца ; 0)
Ссылка на таблицу с примером
Данные с разных листов, на которых разная структура
Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?
Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.
Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).
Вот ингредиенты нашего коктейля:
А логика формулы в общем виде такая:
Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?
Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.
Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).
Вот ингредиенты нашего коктейля:
INDIRECT("'"&название листа&"'!диапазон") - ссылка на ячейку или диапазон на нужном листе, с которого тянем данныеMATCH(заголовок;INDIRECT("'"&название листа&"'!диапазон");0) - поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)ROWS(INDIRECT("'"&название листа&"'!A:A") - число строк на листе, с которого нужно тащить данные)OFFSET(INDIRECT("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) - 1; число строк, как выше;1) - ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка. А логика формулы в общем виде такая:
=ИНДЕКС(СМЕЩ(который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;
ПОИСКПОЗ(ключ для поиска;СМЕЩ(который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))
Таблица с примеромTelegram
Google Таблицы
ВПР-им с разных листов
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит…
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит…
Любимый ВБ, формируем ссылки на изображения товара
Друзья, недавно у нас была задача по номенклатуре товара на Вайлдберрис создать ссылки на изображение этого товара.
Вот вам функция прямо из кода страницы WB, которую мы немного адаптировали (спасибо Роману) и теперь она может работать как пользовательская функция (смотрите скриншот) и в формуле массива.
Это нужно сохранить в редакторе скриптов:
И запускаем это чудо на листе Таблицы (номенклатуры у нас в B2:B):
🧑💻 Кто желает – перепишите под обычную формулу в Таблице, авторам лучших вариантов +7 кармы в нашем чате :)
Таблица с примером
Друзья, недавно у нас была задача по номенклатуре товара на Вайлдберрис создать ссылки на изображение этого товара.
Вот вам функция прямо из кода страницы WB, которую мы немного адаптировали (спасибо Роману) и теперь она может работать как пользовательская функция (смотрите скриншот) и в формуле массива.
Это нужно сохранить в редакторе скриптов:
function getImageUrl(nmid){
const basket={
143:'01',
287:'02',
431:'03',
719:'04',
1007:'05',
1061:'06',
1115:'07',
1169:'08',
1313:'09',
1601:'10',
1655:'11',
1919:'12',
2045:'13',
99999:'14'
}
return `https://basket-${basket[Object.keys(basket)
.filter(v=>Math.floor(nmid/100000)<=v)[0]]}.wb.ru/vol${Math.floor(nmid/100000)}/part${Math.floor(nmid/1000)}/${nmid}/images/big/1.jpg`
}
function images(nmds) {
return nmds.map(n => getimageUrl(n));
}И запускаем это чудо на листе Таблицы (номенклатуры у нас в B2:B):
={"IMG" ; ARRAYFORMULA( IMAGE( images(B2:B)))}Таблица с примером
Please open Telegram to view this post
VIEW IN TELEGRAM
Google Таблицы
Чат для патронов Мы давно хотели попробовать и вот наконец решились. Мы создаём чатик, в котором можно будет задавать вопросы по нашим скриптам, по формулам и по прекрасным Google Таблицам в целом, подписка на чат будет стоить 1000 рублей в месяц. Что даёт…
Что мы делаем в нашем патреон чате?
К примеру, взяли ссылку на крипто-API https://apilist.tronscanapi.com/api/account/wallet?address=TSTVYwFDp7SBfZk7Hrz3tucwQVASyJdwC7&asset_type=1
И написали пользовательскую функцию, которая обращается к этой ссылке, приводит результат к нормальному виду и вставляет данные прямо в Таблицу.
🌊 (здесь про наш патреон-чат, условия и какие темы мы в нём обсуждаем)
📌 В комментариях будет код из скриншота.
К примеру, взяли ссылку на крипто-API https://apilist.tronscanapi.com/api/account/wallet?address=TSTVYwFDp7SBfZk7Hrz3tucwQVASyJdwC7&asset_type=1
И написали пользовательскую функцию, которая обращается к этой ссылке, приводит результат к нормальному виду и вставляет данные прямо в Таблицу.
🌊 (здесь про наш патреон-чат, условия и какие темы мы в нём обсуждаем)
📌 В комментариях будет код из скриншота.
Минус на минус дает число: превращаем текст в число для дальнейших вычислений
Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки с помощью REGEXREPLACE (или последние/первые цифры с помощью функций RIGHT / LEFT), они не будут готовы к употреблению сразу — это будут текстовые значения. См. сумму в столбце B на скриншоте или в таблице по ссылке — там ноль, хотя внешне вроде бы числа извлеклись правильные.
Как превратить текст в число в Google Таблицах (и в Excel тоже)?
1. С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения
P.S. Если вы сразу используете число в вычислении, допустим, вам нужно его прибавить к другому — не добавляйте +, а потом два минуса. Достаточно два минуса — и текст преобразуется в число, и сложение случится.
Например:
Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки с помощью REGEXREPLACE (или последние/первые цифры с помощью функций RIGHT / LEFT), они не будут готовы к употреблению сразу — это будут текстовые значения. См. сумму в столбце B на скриншоте или в таблице по ссылке — там ноль, хотя внешне вроде бы числа извлеклись правильные.
Как превратить текст в число в Google Таблицах (и в Excel тоже)?
1. С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения
=--REGEXEXTRACT(...)
=ЗНАЧЕН(REGEXEXTRACT(...))3. С помощью еще какой-нибудь математической операции, не меняющей значение, например, умножения на единицу
=REGEXEXTRACT(...)*1
P.S. Если вы сразу используете число в вычислении, допустим, вам нужно его прибавить к другому — не добавляйте +, а потом два минуса. Достаточно два минуса — и текст преобразуется в число, и сложение случится.
Например:
=A2--B2Схватка двух... ВПР / 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: t.me/google_sheets/13
— ВПР с ПОИСКПОЗ…
Друзья, ВПР - могущественная функция, чего только с помощью неё нельзя сделать.
На нашем канале мы написали много постов про ВПР, давайте вспомним их:
— Последний аргумент ВПР или ВПР с интервальным просмотром = 1: t.me/google_sheets/13
— ВПР с ПОИСКПОЗ…
XLOOKUP — двойной, пожалуйста
Поиск по двум критериям (в строках и столбцах) — обычно это решается через сочетание INDEX / ИНДЕКС и MATCH / ПОИСКПОЗ.
Но ПРОСМОТРОМ (да не простым, а икс) тоже можно.
В нашем примере ищем в матрице оценку на основе двух оценок — профессиональных и поведенческих компетенций.
Сначала одной функцией получаем массив значений для профессиональной оценки (первого критерия) — у нас это C3:C6 (оранжевое) для первого сотрудника в списке.
Потом другой уже в этом массиве ищем значение, соответствующее второму критерию — поведенческой оценке (красное).
В итоге:
Поиск по двум критериям (в строках и столбцах) — обычно это решается через сочетание INDEX / ИНДЕКС и MATCH / ПОИСКПОЗ.
Но ПРОСМОТРОМ (да не простым, а икс) тоже можно.
В нашем примере ищем в матрице оценку на основе двух оценок — профессиональных и поведенческих компетенций.
Сначала одной функцией получаем массив значений для профессиональной оценки (первого критерия) — у нас это C3:C6 (оранжевое) для первого сотрудника в списке.
Потом другой уже в этом массиве ищем значение, соответствующее второму критерию — поведенческой оценке (красное).
В итоге:
=ПРОСМОТРX(критерий1;где ищем критерий1; ПРОСМОТРX(критерий2;где ищем критерий2;двумерный массив))Таблица с примером
Убираем пустые ячейки из столбца / диапазона элегантно
Друзья, а у нас ведь не только сложные формулы, но и простые.
Нужно убрать пустые ячейки из диапазона и превратить его в строку или в столбец?
На помощь придут новые функции:
Поэтому теперь вам необязательно использовать конструкцию вида
Привыкайте к новым функциям и используйте!
---
Друзья, если читали или смотрели хорошие материалы по Таблицам / скриптам – а поделитесь ссылками в комментариях.
Если случится хорошая подборка, то мы ей обязательно поделимся в следующих постах.
Друзья, а у нас ведь не только сложные формулы, но и простые.
Нужно убрать пустые ячейки из диапазона и превратить его в строку или в столбец?
На помощь придут новые функции:
=torow(диапазон; 1)
=tocol(диапазон; 1)
Поэтому теперь вам необязательно использовать конструкцию вида
=filter(диапазон; один столбец из диапазона <>""), всё можно сделать проще. Привыкайте к новым функциям и используйте!
---
Друзья, если читали или смотрели хорошие материалы по Таблицам / скриптам – а поделитесь ссылками в комментариях.
Если случится хорошая подборка, то мы ей обязательно поделимся в следующих постах.
@zadavai_vopros_bot
Друзья, а мы обновили модель, которая рисует картинки в нашем ИИ-боте, теперь там
Пользуйтесь, напоминаю, что в боте 10 бесплатных запросов каждый месяц, нужно больше – 500 рублей, их можно также оплатить через бота.
Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом / Как писать промпты!
Друзья, а мы обновили модель, которая рисует картинки в нашем ИИ-боте, теперь там
dall-e-3.Пользуйтесь, напоминаю, что в боте 10 бесплатных запросов каждый месяц, нужно больше – 500 рублей, их можно также оплатить через бота.
Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом / Как писать промпты!
Давайте продолжим тему ПРОСМОТРов. И поговорим о старой функции 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...
Немного формульно-датового многоэтажного ада под конец года. Выдаем одной формулой все недели года в формате "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 декабря, год в ячейке
С наступающим Новым годом! Пусть в этих самых отчетах у вас все будет хорошо в следующем году 🤠Спасибо, что читаете нас!
This media is not supported in your browser
VIEW IN TELEGRAM
Автоматизация елки
На новый год принято ставить елку, мы не будем отходить от этой традиции и установим на нашем канале нарядную Google-Табличную ель.
В ветках у неё формула, обеспечивающая для каждой ячейки свой цв
А зажжет ёлку скрипт, он при каждом открытии Таблицы, сто раз, в цикле, вставит в яче
Таблица с ёлкой
Друзья, с наступающим новым годом, берегите себя, встречаемся здесь же в 2024! Счастья и мира!
PS А еще у старожила нашего сообщества Каната сегодня день рождения, поздравляем!🌟
---
🌲 Оглавление канала: ты-дыц
🌲 Самый табличный чат на свете: бадабум
На новый год принято ставить елку, мы не будем отходить от этой традиции и установим на нашем канале нарядную Google-Табличную ель.
В ветках у неё формула, обеспечивающая для каждой ячейки свой цв
ет:
=INDEX({"🟠";"🟡";"🟢";"🔵";"🟣";"🟤"};RANDBETWEEN(1;6);1)А зажжет ёлку скрипт, он при каждом открытии Таблицы, сто раз, в цикле, вставит в яче
йку A1 число, тем самым запуская пересчёт формул, в частности RANDBETWEEN:
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
Табличное, мощное, лучшее за 2023 год
То, про что мы хотим вам напомнить из репертуара прошедшего года.
Оглавление Таблицы скриптами
t.me/google_sheets/1074
Достаем изображения из Google и Яндекса формулой
t.me/google_sheets/1091
И из Рамблера
t.me/google_sheets/1296
Новые функции в Google Sheets - статья Михаила Смирнова
telegra.ph/Novye-funkcii-v-Google-Sheets-2023-02-02-02-07
Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)
teletype.in/@renat_shagabutdinov/lambdasheets
Главная статья импорта. Руководство по функции IMPORTRANGE
https://teletype.in/@renat_shagabutdinov/importrange
Выводим все даты текущего месяца формулой
t.me/google_sheets/1113
Импорт данных из всех Google Таблиц в списке с помощью формул
teletype.in/@renat_shagabutdinov/IMPORT-LAMBDA
Телеграм + Google Таблицы, наши решения
t.me/google_sheets/1141
Запрашиваем из Таблиц ИНН и получаем название компании
t.me/google_sheets/1159
Вычисляемое поле в сводной: умножаем сумму на одно значение
t.me/google_sheets/1233
Случайная жеребьевка команд с условием
t.me/google_sheets/1250
Парсим Ютуб ⚡️
t.me/google_sheets/1285
ВПР-им с разных листов
t.me/google_sheets/1306
Схватка двух... ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP
t.me/google_sheets/1315
То, про что мы хотим вам напомнить из репертуара прошедшего года.
Оглавление Таблицы скриптами
t.me/google_sheets/1074
Достаем изображения из Google и Яндекса формулой
t.me/google_sheets/1091
И из Рамблера
t.me/google_sheets/1296
Новые функции в Google Sheets - статья Михаила Смирнова
telegra.ph/Novye-funkcii-v-Google-Sheets-2023-02-02-02-07
Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)
teletype.in/@renat_shagabutdinov/lambdasheets
Главная статья импорта. Руководство по функции IMPORTRANGE
https://teletype.in/@renat_shagabutdinov/importrange
Выводим все даты текущего месяца формулой
t.me/google_sheets/1113
Импорт данных из всех Google Таблиц в списке с помощью формул
teletype.in/@renat_shagabutdinov/IMPORT-LAMBDA
Телеграм + Google Таблицы, наши решения
t.me/google_sheets/1141
Запрашиваем из Таблиц ИНН и получаем название компании
t.me/google_sheets/1159
Вычисляемое поле в сводной: умножаем сумму на одно значение
t.me/google_sheets/1233
Случайная жеребьевка команд с условием
t.me/google_sheets/1250
Парсим Ютуб ⚡️
t.me/google_sheets/1285
ВПР-им с разных листов
t.me/google_sheets/1306
Схватка двух... ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP
t.me/google_sheets/1315
Telegram
Google Таблицы
Оглавление Таблицы скриптами
Друзья, привет!
В первом посте 2023 года решим классическую проблему – выведем список всех листов Таблицы вместе с ссылками на каждый лист.
Базовый скрипт.
function sheets() {
//текущая таблица
const ss = Spreadsheet…
Друзья, привет!
В первом посте 2023 года решим классическую проблему – выведем список всех листов Таблицы вместе с ссылками на каждый лист.
Базовый скрипт.
function sheets() {
//текущая таблица
const ss = Spreadsheet…
Сегодня про то, как Google Таблицы нам строить и жить помогают
Личный чек-лист: очень простая, но мощная штука.
Как говорит Скотт Адамс, системы лучше целей. Да и не только он об этом говорит. Действительно, если уделять обучению или делу немного времени, но каждый день, результаты будут мощные. Фокусируйтесь не на большой цели, а на повседневной работе.
Завести чек-лист в Google Таблице проще некуда. Просто флажки с теми пунктами, которые нужно выполнять ежедневно — полчаса на изучение той или иной темы, ежедневные шаги или тренировки, работа над проектами, отказ от сладкого. Да, каждый день отмечать все не получится. Но сам настрой на это и наглядное представление того, как вы идете, помогает сосредоточиться и делать больше важного.
То, что уже на автопилоте — ежедневный бег, например, а у вас может быть что-то другое — уже можно не заносить, смысла нет. Важно то, что можно забыть сделать.
Традиционно напоминаем, что флажки можно включать и выключать пробелом.
Создавайте личную копию шаблона, настраивайте для себя и используйте! В шаблоне зеленым горит текущая дата, серым — выходные (давайте немного расслабляться в эти дни!):
https://docs.google.com/spreadsheets/d/11pAWMxaQfoQcr1exqwsmCha_Xxm7BUE_ind6QVvispU/copy
Личный чек-лист: очень простая, но мощная штука.
Как говорит Скотт Адамс, системы лучше целей. Да и не только он об этом говорит. Действительно, если уделять обучению или делу немного времени, но каждый день, результаты будут мощные. Фокусируйтесь не на большой цели, а на повседневной работе.
Завести чек-лист в Google Таблице проще некуда. Просто флажки с теми пунктами, которые нужно выполнять ежедневно — полчаса на изучение той или иной темы, ежедневные шаги или тренировки, работа над проектами, отказ от сладкого. Да, каждый день отмечать все не получится. Но сам настрой на это и наглядное представление того, как вы идете, помогает сосредоточиться и делать больше важного.
То, что уже на автопилоте — ежедневный бег, например, а у вас может быть что-то другое — уже можно не заносить, смысла нет. Важно то, что можно забыть сделать.
Традиционно напоминаем, что флажки можно включать и выключать пробелом.
Создавайте личную копию шаблона, настраивайте для себя и используйте! В шаблоне зеленым горит текущая дата, серым — выходные (давайте немного расслабляться в эти дни!):
https://docs.google.com/spreadsheets/d/11pAWMxaQfoQcr1exqwsmCha_Xxm7BUE_ind6QVvispU/copy
Превращаем дату-как-текст в дату
Для этого есть целая функция — DATEVALUE / ДАТАЗНАЧ.
Любой текст она не осилит (и не только такой вольный текст, как "Позавчера" или "первый день года", но даже "17 октябрь 2022", увы — см 4 строку на скриншоте). Но текст, максимально похожий на стандартные даты, превратит в настоящие. Если года в текстовой строке нет — будет использован текущий (см строку 6). Если есть время — останется только дата (строка 8).
Альтернатива — умножить дату на единицу.
Для этого есть целая функция — DATEVALUE / ДАТАЗНАЧ.
Любой текст она не осилит (и не только такой вольный текст, как "Позавчера" или "первый день года", но даже "17 октябрь 2022", увы — см 4 строку на скриншоте). Но текст, максимально похожий на стандартные даты, превратит в настоящие. Если года в текстовой строке нет — будет использован текущий (см строку 6). Если есть время — останется только дата (строка 8).
Альтернатива — умножить дату на единицу.