Делаем кнопку группировки более наглядной

вот такой лайфхак от одного из подписчиков Бена Коллинза.

Функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (про нее подробнее тут) агрегирует только видимые ячейки.
Соответственно, мы можем проверять, раскрыта ли ячейка строкой ниже — если применим функцию COUNTA / СЧЁТЗ, то есть подсчет любых значений. В SUBTOTAL это функция номер 3 (номер функции в первом аргументе).

И если строка скрыта, то функция вернет 0. Тогда можно выдать текст в духе "Подробнее" или "Раскрыть". Если не 0 — значит, строка ниже видима, выдаем другую надпись ("Скрыть").

=IF( SUBTOTAL (103;ячейка)=0; "⬇️Подробнее" ; "⬆️Скрыть")

Что у нас есть для работы с маркетплейсами, бесплатно, без смс и помесячных оплат, дорогие продавцы:

1. WB, загружаем отчет по реализации и остатки FBO t.me/google_sheets/1143

2. WB, загружаем расходы в Таблицу по рекламе ВБ t.me/google_sheets/1460

3. WB, загружаем топ-100 товаров по запросу t.me/google_sheets/1116

4. WB загружаем характеристики и описание товаров из ВБ t.me/google_sheets/1404

5. WB Достаём ссылки на фотографии товара из карточки WB t.me/google_sheets/992

6. WB, формируем ссылки на изображения товара t.me/google_sheets/1310

7. API OZON ИЗ GOOGLE ТАБЛИЦЫ t.me/google_sheets/583

📌📌 А хотите любую другую автоматизацию по ВБ / ОЗОН / Яндекс Маркет - напишите в нашего бота @vas_mnogo_a_ya_bot

Сортируем "внутри" текста

Дано: есть текстовая строка с именами/названиями и числами.
Надо: отсортировать и снова получить текстовую строку.

То есть из "Бусинка 37, Лемур 22, Штрудель 75" сделать "Штрудель 75, Бусинка 37, Лемур 22"

Формула:

=JOIN(", ";BYROW(sort(WRAPROWS(SPLIT(A1;", ");2);2;0);LAMBDA(x;JOIN(" ";x))))

Сначала разделяем на отдельные значения — и числа, и имена (SPLIT по пробелу и запятой).
Потом делаем двумерным диапазоном (таблицей) с помощью WRAPROWS и сортируем по столбцу с числами (SORT).
Объединяем с помощью JOIN — сначала в пределах каждой строки (BYROW) имя и число через пробел, а потом эти значения уже в один текст через запятую еще одним верхним JOIN'ом.

Небольшой пост от нашего админа Михаила:

Мы тут вам подготовили табличку с примером, как можно перевести одни символы в другие. Например сделать транслитерацию.

У примера есть и практическая польза – можете проверить написание своих имени и фамилии в загранпаспорте и понять, надо ли бежать исправлять.

Таблица тут.

Поиск и окно "Найти и заменить" в Excel и Google Таблицах

Казалось бы, все просто — нажимай Ctrl+F да ищи, нажимай Ctrl+H да заменяй/удаляй.
Но есть приятные опции:
— Можно искать/заменять в диапазоне/на листе/на всех листах
— Можно искать/заменять с учетом регистра
— В Google Таблицах в окне "Найти и заменить" можно использовать регулярные выражения (смотрите примеры в статье: можно поменять формат дат на другие или сделать еще какую-нибудь магию), а в Excel символы подстановки
— В Google Таблицах можно искать по ссылкам (а в обоих редакторах — по формулам);
— В Excel можно менять формат ячеек — достаточно выбрать образец для поиска и образец для замены.

Пост от нашего читателя Алексея, передаем слово автору:

Сегодня случайно увидел у Бена Коллинса пост, решил попробовать сделать.
Получилось вроде не хуже 🙂
Может будет актуально после предыдущего поста про множественный выбор.

Описание такое же простое, как сама формула 🙂

ArrayFormula(SPLIT(C:C; ", "; ))

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

TOCOL(... ; 3)

Преобразуем двумерный массив (полученный после SPLIT) в один столбец, игнорируя пустые значения и ошибки.

С помощью хорошо знакомой всем QUERY считаем наши фрукты.

Экзотический прием от Бена Коллинза, но вдруг кому-то пригодится!

Объединяем два заголовка в одной ячейке.
Для этого можно воспользоваться формулой:
Склеиваем два текстовых значения (заголовка), добавляя между ними:
— перенос строки (функция СИМВОЛ/CHAR с кодом 10)
— несколько нижних подчеркиваний, которые мы повторяем с помощью функции ПОВТОР/REPT
— еще один перенос строки

После этого меняем поворот текста, чтобы заголовки были под углом.

В Excel можно вставлять в диаграммы иконки и рисунки вместо столбиков / горизонтальных столбиков (в линейчатых диаграммах)

В Таблицах нельзя, но мы сделаем такое формулой.

Изображение будем получать с помощью IMAGE. Ссылку на изображение — с помощью IMPORTXML с одного из сайтов с иконками, куда будем вставлять название нужной категории (у нас статистика по домашним животным в одной из стран). Если сайт иностранный, то название переводим GOOGLETRANSLATE.

С помощью INDEX берем одну из ссылок со страницы с результатами поиска — это далеко не всегда одно из первых значений, потому что в начале на сайте может быть много общих картинок, не относящихся к поиску, и выясняется это методом научного тыка.

Клонируем изображение в ячейки с помощью MAKEARRAY. Параметры: строка одна (если имитируем линейчатую диаграмму), а число изображений можно по такой логике:
выясняем максимальную разрядность у наших чисел (в примере 7), возводим 10 в СТЕПЕНЬ на единицу меньше, то есть в нашем примере миллион, и на это число делим.

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

Округление вверх — тогда даже маленькое значение будет отображаться как одна картинка.

Магия новых функций Excel: революция в формулах 🔥

Друзья, мы с Лемуром рады анонсировать новый мини-курс, посвященный новым же функциям Excel (и — по большей части — Google Таблиц).

Это функции, которые могут решать задачи, ради которых раньше пришлось бы писать макросы или создавать запросы в Power Query!

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

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

В честь запуска новой версии сайта и этого курса — зверская цена от Лемура — 890 рублей! 🔥
Никаких уловок и вечных скидок не будет: 18 ноября цена вырастет до 2300 и больше уже никогда не вернется к старой.

Внутри:
14 видео от 5 до 25 минут с качественным монтажом
Дополнительные текстовые материалы
Файлы со всеми примерами и формулами — исходные и готовые.

Покупать тут — и там же подробная программа и примеры:
https://shagabutdinov.ru/magic-excel

ИЗБРАННЫЕ СКРИПТЫ НАШЕГО КАНАЛА

Посмотрите, вдруг вы пропустили что-нибудь сочное:

— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →

— 🧞‍♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →

— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →

— ОТПРАВЛЯТОР 2, создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию →

— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →

— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →

— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →

— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →

— Скриптами определяем, когда освободится домен →

— Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц) →

— DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы →

— С помощью скрипта даём доступ к другой Таблице, чтобы IMPORTRANGE заработал сразу →

— Защищаем скрипты от редактирования →

— Регулярный бэкап Таблиц в формате XLSX в телеграм! →

— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →

— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →

— Важный скрипт. Связанные выпадающие списки из кэша →

🏄 Заказ у нас @vas_mnogo_a_ya_bot

Загружаем файлы прямо из Таблицы

Друзья, привет! Презентуем вам Таблицу со скриптом, с помощью нее вы сможете загружать файлы на Google Диск и возвращать в Таблицу ссылку на загруженный файл

Как вы можете это использовать? Допустим, вы торгуете на WB / OZON, делаете в Таблице каталог своих товаров и сразу же загружаете через эту Таблицу фотографии этих товаров.

Таблица (делаем копию)

Ниже просто код:

<form>
  <input type="file" name="file" onchange="upload(this.parentNode)" />
</form>
<script>
  function upload(e){
  const file = e.file.files[0];
  const fr = new FileReader();
  fr.onload = e => google.script.run.withSuccessHandler(function() { google.script.host.close() })
  .upload([[...new Int8Array(e.target.result)], file.type, file.name]);
  fr.readAsArrayBuffer(file);
};

</script>

const folderUrl = 'https://drive.google.com/drive/folders/1N9ztzVnG4Xz6AymLN5nByQXc9JOTWdou';

function openSidebar() {
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("index"));
};

function upload(e) {
  const url = DriveApp.getFolderById(getIdFromUrl(folderUrl)).createFile(Utilities.newBlob(...e)).getUrl();
  // SpreadsheetApp.getActiveRange().setValue(url);
  SpreadsheetApp.getActiveRange().offset(0, 0, 1, 3).setValues([[url, e[1], e[2]]]);
};

function getIdFromUrl(url) {
  return url.match(/[-\w]{25,}/)[0];
}
}

📗 Чат @google_spreadsheets_chat

📕 Оглавление канала

🏄 Заказ работы у нас

Сделали доработку и теперь скрипт возвращает в ячейки:

- ссылку на загруженный файл
- тип файла
- название

PS Если нужна только ссылка - то закомментируйте 10 строку и раскомментируйте 9 строку.

У вас есть дата, а вы хотите номера/названия месяцев в отдельном столбце (допустим, для сводной, для отчетов, для фильтрации) — 7 вариантов

Самый простой вариант — функция MONTH / МЕСЯЦ. Это число, порядковый номер (3 для марта, 11 для ноября).

С помощью функции TEXT / ТЕКСТ можно получить также вариант с нулем для коротких номеров (03 для марта, но 11 для ноября). С помощью нее же — текстовые варианты (мар. и марта для российских региональных настроек).

Если "марта" вам не нравится и вы хотите использовать абсолютно любые варианты, то можно брать их из диапазона с помощью INDEX. Или из виртуального массива внутри формулы с помощью того же INDEX или VLOOKUP или CHOOSE.

По ссылке — семь с половиной вариантов, включая формулу массива, выдающую ваши названия месяцев для всего столбца (без вспомогательного диапазона; названия внутри формулы).

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

Напоминаем, друзья: флажки (Checkboxes) в Google Таблицах (и в Excel 365, кстати) можно использовать не только как переключатели значений TRUE / FALSE, но и как визуальное представление этих значений, возвращаемых формулами.

Иначе говоря, если у вас формулы, что-то проверяющие, и выдающие ИСТИНА / ЛОЖЬ, можно вставить "поверх" флажки. Такие флажки уже нельзя переключить пробелом или мышкой.

Они будут переключаться при изменении значения, возвращаемого формулой.

Что это за формулы могут быть? Какие-нибудь проверки значений в ячейке (функции ISEMAIL, ISURL, ISBLANK, ISTEXT и другие), просроченных платежей и прочего, как на скриншоте, нескольких условий сразу через AND / OR.

Или что-нибудь многоэтажное через LAMBDA — например, был ли у сотрудника отпуск в 14 дней подряд (про это читайте тут)

#Лайфхак❗️Как в Google таблице с помощью условного форматирования сделать границы ячеек

Пост от активного члена нашего сообщества Алексея:

1. Создаём/наполняем данными нашу гугл таблицу как обычно.
2. Сохраняем (скачиваем) нашу таблицу в Excel-формате (меню Файл > Скачать > Microsoft Excel (.xlsx)).
3. Открываем таблицу из п.2 в Excel.
4. Создаём правила Условного форматирования в Excel.
5. Сохраняем (загружаем) таблицу из п.3 в Google Drive.
6. Открываем таблицу из п.4 и сохраняем в формате Гугл Таблиц (меню Файл > Сохранить как таблицу Google)

✅ Профит! Условное форматирование сохранилось!
Конечно, не в полном объёме, например, цвет и толщина границ не сохраняется.

⚠️И главный минус. Данное правило нельзя изменять!

Пример. Мы сделали с помощью УФ несколько границ: между разными группами, слева, справа и внизу...

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала

⚠️ 10 лайфхаков работы в 1С, которые упростят жизнь любому бухгалтеру и позволят сэкономить сотни часов рабочего времени.

Как бы вы оценили свой уровень владения обычной 1С:Бухгалтерией по 10-ти бальной шкале? Даже если на максимальную оценку — всегда интересно освоить новые трюки, которые облегчают работу.

Рекомендую подписаться на канал Мастер CFO. Авторы — Софья и Натали — специалисты в области фин. и бух. учёта со стажем 15 лет. Создали мотивирующее сообщество для бухгалтеров, финансистов, экономистов и аналитиков.

➡️ На канале вас ждут:
– уникальные таблицы, которые упростят вашу работу
– лайфхаки, которые позволят зарабатывать в 2 раза больше
– готовые инструкции по автоматизации фин.учёта
– экспертные кейсы и советы
– ежедневное живое общение с коллегами

📎Чек-лист «10 лайфхаков работы в 1С.

✅ подписывайтесь на канал и забирайте файл в закреплённом сообщении канала бесплатно.
@findir_pro

Создаем пакет документов для оформления нового сотрудника прямо из Google Таблицы

Коллеги, привет! Наш подписчик Василий поделился с нами своим решением, которое он написал на GAS-скриптах.

Василий решал задачу "у меня есть шаблон в Google Документе, в нём несколько документов, я хочу заполнять нужные мне документы данными из таблицы и сохранять результат в папку, а потом распечатать это и дать сотруднику подписать".

Чтобы все заработало, вы:

1) копируете Таблицу себе
2) вводите на лист "настройки" ссылку на папку, куда будут сохраняться созданные документы
3) копируете себе шаблон, меняете его под себя (если требуется) и также вносите ссылку на него на лист "настройки"
4) далее отмечаете чекбоксами какие документы вы хотите из шаблона оставить и заполнить
5) далее переходите на лист "ввод данных" и заполняете данные по сотруднику и смело нажимаете на кнопку "создать документы!"

И скрипт:

1) копирует ваш шаблон 
2) оставляет внутри него только выбранные документы
3) далее меняет теги в шаблоне на то, что вы занесли на лист "ввод данных"
4) переносит шаблон в указанную вами папку и вставляет ссылку на документ, который получился на лист "ввод данных"

Василий оставил комментарии по в скрипте по каждой строке кода, поэтому, если захотите - можно попробовать разобраться, как все работает

Таблица со скриптом
Шаблон документов

🔥 Если решение понравится – можете сделать донат Василию по реквизитам, мы передадим.

😈 А будут вопросы – пишите в наш чат.

Нумеруем только видимые строки

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

Решение:

=SUBTOTAL(103;$B$2:B2)

(если начало в строке два)

Считаем (103 = СЧЁТЗ / COUNTA) значения в каком-то столбце таблицы, но считаем только видимые (не скрытые), так как используем SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Ссылка вида $B$2:B2 — то есть всегда считаем с начала таблицы до текущей строки (при смещении формулы будет меняться только конец диапазона).