Если у вас проблемы с ютубом: перезалили несколько видео на Kinescope. Учитесь на здоровье!
Пользовательские числовые форматы в Google Таблицах:
https://kinescope.io/n8ohYBpQ3w3gufd8Ab4owc
Умные таблицы в Google Spreadsheets:
https://kinescope.io/navBqmzZt1UBqUCgBgxGjx
Видео про относительные и абсолютные ссылки. В интерфейсе Excel, но актуально для всех, кто только начинает работать с формулами и хочет окончательно разобраться со ссылками и долларами:
https://kinescope.io/9tPfY1ZSxa1NFXoTRoqeU2
Пользовательские числовые форматы в Google Таблицах:
https://kinescope.io/n8ohYBpQ3w3gufd8Ab4owc
Умные таблицы в Google Spreadsheets:
https://kinescope.io/navBqmzZt1UBqUCgBgxGjx
Видео про относительные и абсолютные ссылки. В интерфейсе Excel, но актуально для всех, кто только начинает работать с формулами и хочет окончательно разобраться со ссылками и долларами:
https://kinescope.io/9tPfY1ZSxa1NFXoTRoqeU2
Kinescope
Пользовательские числовые форматы в Google Таблицах (Custom number formats in Google Sheets)
Рассматриваем основные символы и правила для создания пользовательских числовых форматов в Google Таблицах.
Таблица с примерами из видео: https://docs.google.com/spreadsheets/d/1Nn7HLI0y_eEVBnS-qhsDUPuwpsZU6FgCK7KT7ACKbQE/edit?usp=sharing
Наш канал "Google…
Таблица с примерами из видео: https://docs.google.com/spreadsheets/d/1Nn7HLI0y_eEVBnS-qhsDUPuwpsZU6FgCK7KT7ACKbQE/edit?usp=sharing
Наш канал "Google…
Давненько мы про производственный календарь не говорили
Есть прекраснейший сайт http://xmlcalendar.ru/ — спасибо его создателям — с производственным календарем за разные годы и в разных форматах. XML, TXT, CSV и другие.
Ссылки каждый год отличаются только на номер года.
А значит, можно загружать данные в таблицы.
Например, в формате TXT список всех выходных и праздничных дней года. Ссылка для 2024 года такая:
http://xmlcalendar.ru/data/ru/2024/calendar.txt
Соответственно, можно получить список для текущего года в Google Таблицах следующей формулой:
Но есть еще такое неприятное явление, как рабочие выходные дни (СБ-ВС).
Например, в 2024 году это 27 апреля и 28 декабря.
Как получить список таких дат?
В XML формате календарь есть тип дня t — единица для выходных, 2 для сокращенных рабочих, 3 — то, что нам нужно (рабочий день в СБ / ВС).
Можно получить этот список такой формулой (спасибо Михаилу Смирнову за более лаконичный вариант в комментариях):
Соответственно, если нужны праздники, то t = 1, если сокращенные рабочие, то t = 2.
Правда, формат дат в XML календаре такой: MM.DD (04.27). Таблицы его не поймут, даже если применить ДАТАЗНАЧ / DATEVALUE.
Поэтому сначала придется поменять день и месяц местами (можно с помощью REGEXREPLACE), а потом добавить DATEVALUE:
Есть прекраснейший сайт http://xmlcalendar.ru/ — спасибо его создателям — с производственным календарем за разные годы и в разных форматах. XML, TXT, CSV и другие.
Ссылки каждый год отличаются только на номер года.
А значит, можно загружать данные в таблицы.
Например, в формате TXT список всех выходных и праздничных дней года. Ссылка для 2024 года такая:
http://xmlcalendar.ru/data/ru/2024/calendar.txt
Соответственно, можно получить список для текущего года в Google Таблицах следующей формулой:
=IMPORTDATA("http://xmlcalendar.ru/data/ru/" & YEAR(TODAY()) & "/calendar.txt")Но есть еще такое неприятное явление, как рабочие выходные дни (СБ-ВС).
Например, в 2024 году это 27 апреля и 28 декабря.
Как получить список таких дат?
В XML формате календарь есть тип дня t — единица для выходных, 2 для сокращенных рабочих, 3 — то, что нам нужно (рабочий день в СБ / ВС).
Можно получить этот список такой формулой (спасибо Михаилу Смирнову за более лаконичный вариант в комментариях):
=IMPORTXML("http://xmlcalendar.ru/data/ru/" & YEAR(TODAY()) & "/calendar.xml"; "//day[@t = 3]/@d")Соответственно, если нужны праздники, то t = 1, если сокращенные рабочие, то t = 2.
Правда, формат дат в XML календаре такой: MM.DD (04.27). Таблицы его не поймут, даже если применить ДАТАЗНАЧ / DATEVALUE.
Поэтому сначала придется поменять день и месяц местами (можно с помощью REGEXREPLACE), а потом добавить DATEVALUE:
=ArrayFormula(DATEVALUE(REGEXREPLACE(IMPORTXML("http://xmlcalendar.ru/data/ru/" &YEAR(TODAY()) & "/calendar.xml"; "//day[@t = 3]/@d");"(\d{2})\.(\d{2})";"$2\.$1")))xmlcalendar.ru
Производственный календарь в формате XML для России
Производственный календарь в формате XML на 2025 год для России
Декартово произведение (все возможные комбинации фраз) формулой
Забираем первый список слов (фраз) функцией TOCOL (второй аргумент = 1 исключает пустые значения)
Из второго списка тоже забираем все без пустых и вдобавок транспонируем (делаем строкой) — функция TOROW:
Добавляем между ними пробел и склеиваем:
Результатом этого шага станет двумерный диапазон с нужными значениями. Останется превратить его в список (TOCOL или FLATTEN) и применить ArrayFormula или INDEX сверху, чтобы получить не одно значение, а весь массив:
Забираем первый список слов (фраз) функцией TOCOL (второй аргумент = 1 исключает пустые значения)
TOCOL(A:A; 1)
Из второго списка тоже забираем все без пустых и вдобавок транспонируем (делаем строкой) — функция TOROW:
TOROW(B:B; 1)
Добавляем между ними пробел и склеиваем:
TOCOL(A:A; 1)& " " &TOROW(B:B; 1)
Результатом этого шага станет двумерный диапазон с нужными значениями. Останется превратить его в список (TOCOL или FLATTEN) и применить ArrayFormula или INDEX сверху, чтобы получить не одно значение, а весь массив:
=ArrayFormula(FLATTEN(TOCOL(A:A; 1)& " " &TOROW(B:B; 1)))
INDIRECT / ДВССЫЛ — функция, которая делает текст ссылкой на диапазон
*
Для чего это нужно? Ну, помимо ссылки, которая не слетит никогда (даже при удалении строк):
1 Вы собираете ссылку из нескольких кусочков. Например, название листа берете из столбца или в результате проверки какого-то условия функцией IF / ЕСЛИ. В результате склейки (конкатенации) на выходе уже текст, а не ссылка. ДВССЫЛ превратит текст в ссылку.
Пример (поиск на разных листах)
И более лютый пример (формула, которая ищет при разной структуре листов)
2 Вам нужно ссылаться в условном форматировании на другой лист. Напрямую не получится, а через ДВССЫЛ можно (а в Excel еще пригодится для ссылки на умные таблицы в проверке данных)
Пример
3 Вы формируете ссылку на динамический диапазон — по параметрам. Например, берете строку начала и конца из ячеек:
Такая замена OFFSET для формировании динамической ссылки на диапазон.
=A1 — это ссылка на ячейку A1="A1" — это текстовая строка из двух символов, буквы A и единицы=INDIRECT("A1") — это ссылка на ячейку A1 (причем даже если вы удалите строку 1, это будет ссылка на A1, только теперь это будет другая ячейка; а обычная ссылка =A1 превратится в ошибку в таком случае)=INDIRECT(A1) — ссылка на диапазон/ячейку, адрес которого(-ой) находится в ячейке A1 🤯*
Для чего это нужно? Ну, помимо ссылки, которая не слетит никогда (даже при удалении строк):
1 Вы собираете ссылку из нескольких кусочков. Например, название листа берете из столбца или в результате проверки какого-то условия функцией IF / ЕСЛИ. В результате склейки (конкатенации) на выходе уже текст, а не ссылка. ДВССЫЛ превратит текст в ссылку.
Пример (поиск на разных листах)
И более лютый пример (формула, которая ищет при разной структуре листов)
2 Вам нужно ссылаться в условном форматировании на другой лист. Напрямую не получится, а через ДВССЫЛ можно (а в Excel еще пригодится для ссылки на умные таблицы в проверке данных)
Пример
3 Вы формируете ссылку на динамический диапазон — по параметрам. Например, берете строку начала и конца из ячеек:
=INDIRECT("A" & ячейка с номером первой строки & ":A"
& ячейка с номером последней строки)Такая замена OFFSET для формировании динамической ссылки на диапазон.
Telegram
Google Таблицы
ВПР-им с разных листов
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит…
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит…
ИЗБРАННЫЕ СКРИПТЫ НАШЕГО КАНАЛА
Посмотрите, вдруг вы пропустили что-нибудь сочное:
— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →
— 🧞♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →
— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →
— ОТПРАВЛЯТОР 2, создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию →
— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →
— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →
— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →
— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →
— Скриптами определяем, когда освободится домен →
— Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц) →
— DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы →
— С помощью скрипта даём доступ к другой Таблице, чтобы IMPORTRANGE заработал сразу →
— Защищаем скрипты от редактирования →
— Регулярный бэкап Таблиц в формате XLSX в телеграм! →
— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →
— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →
— Важный скрипт. Связанные выпадающие списки из кэша →
🏄 Заказ у нас @vas_mnogo_a_ya_bot
Посмотрите, вдруг вы пропустили что-нибудь сочное:
— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →
— 🧞♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →
— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →
— ОТПРАВЛЯТОР 2, создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию →
— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →
— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →
— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →
— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →
— Скриптами определяем, когда освободится домен →
— Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц) →
— DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы →
— С помощью скрипта даём доступ к другой Таблице, чтобы IMPORTRANGE заработал сразу →
— Защищаем скрипты от редактирования →
— Регулярный бэкап Таблиц в формате XLSX в телеграм! →
— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →
— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →
— Важный скрипт. Связанные выпадающие списки из кэша →
🏄 Заказ у нас @vas_mnogo_a_ya_bot
This media is not supported in your browser
VIEW IN TELEGRAM
Делаем кнопку группировки более наглядной
вот такой лайфхак от одного из подписчиков Бена Коллинза.
Функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (про нее подробнее тут) агрегирует только видимые ячейки.
Соответственно, мы можем проверять, раскрыта ли ячейка строкой ниже — если применим функцию COUNTA / СЧЁТЗ, то есть подсчет любых значений. В SUBTOTAL это функция номер 3 (номер функции в первом аргументе).
И если строка скрыта, то функция вернет 0. Тогда можно выдать текст в духе "Подробнее" или "Раскрыть". Если не 0 — значит, строка ниже видима, выдаем другую надпись ("Скрыть").
вот такой лайфхак от одного из подписчиков Бена Коллинза.
Функция 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
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"
Формула:
Сначала разделяем на отдельные значения — и числа, и имена (SPLIT по пробелу и запятой).
Потом делаем двумерным диапазоном (таблицей) с помощью WRAPROWS и сортируем по столбцу с числами (SORT).
Объединяем с помощью JOIN — сначала в пределах каждой строки (BYROW) имя и число через пробел, а потом эти значения уже в один текст через запятую еще одним верхним JOIN'ом.
Дано: есть текстовая строка с именами/названиями и числами.
Надо: отсортировать и снова получить текстовую строку.
То есть из "Бусинка 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 можно менять формат ячеек — достаточно выбрать образец для поиска и образец для замены.
Казалось бы, все просто — нажимай Ctrl+F да ищи, нажимай Ctrl+H да заменяй/удаляй.
Но есть приятные опции:
— Можно искать/заменять в диапазоне/на листе/на всех листах
— Можно искать/заменять с учетом регистра
— В Google Таблицах в окне "Найти и заменить" можно использовать регулярные выражения (смотрите примеры в статье: можно поменять формат дат на другие или сделать еще какую-нибудь магию), а в Excel символы подстановки
— В Google Таблицах можно искать по ссылкам (а в обоих редакторах — по формулам);
— В Excel можно менять формат ячеек — достаточно выбрать образец для поиска и образец для замены.
Teletype
Поиск и окно "Найти и заменить" в Excel и Google Таблицах
Чтобы искать какой-нибудь текст, символ - в обоих редакторах нужно нажать Ctrl+F.
Пост от нашего читателя Алексея, передаем слово автору:
Сегодня случайно увидел у Бена Коллинса пост, решил попробовать сделать.
Получилось вроде не хуже 🙂
Может будет актуально после предыдущего поста про множественный выбор.
Описание такое же простое, как сама формула 🙂
Разбиваем каждую ячейку в колонке C на отдельные элементы, используя последовательность символов (запятая и пробел) в качестве разделителя.
Преобразуем двумерный массив (полученный после SPLIT) в один столбец, игнорируя пустые значения и ошибки.
С помощью хорошо знакомой всем QUERY считаем наши фрукты.
Сегодня случайно увидел у Бена Коллинса пост, решил попробовать сделать.
Получилось вроде не хуже 🙂
Может будет актуально после предыдущего поста про множественный выбор.
Описание такое же простое, как сама формула 🙂
ArrayFormula(SPLIT(C:C; ", "; ))
Разбиваем каждую ячейку в колонке C на отдельные элементы, используя последовательность символов (запятая и пробел) в качестве разделителя.
TOCOL(... ; 3)
Преобразуем двумерный массив (полученный после SPLIT) в один столбец, игнорируя пустые значения и ошибки.
С помощью хорошо знакомой всем QUERY считаем наши фрукты.
Экзотический прием от Бена Коллинза, но вдруг кому-то пригодится!
Объединяем два заголовка в одной ячейке.
Для этого можно воспользоваться формулой:
Склеиваем два текстовых значения (заголовка), добавляя между ними:
— перенос строки (функция СИМВОЛ/CHAR с кодом 10)
— несколько нижних подчеркиваний, которые мы повторяем с помощью функции ПОВТОР/REPT
— еще один перенос строки
После этого меняем поворот текста, чтобы заголовки были под углом.
Объединяем два заголовка в одной ячейке.
Для этого можно воспользоваться формулой:
Склеиваем два текстовых значения (заголовка), добавляя между ними:
— перенос строки (функция СИМВОЛ/CHAR с кодом 10)
— несколько нижних подчеркиваний, которые мы повторяем с помощью функции ПОВТОР/REPT
— еще один перенос строки
После этого меняем поворот текста, чтобы заголовки были под углом.
В Excel можно вставлять в диаграммы иконки и рисунки вместо столбиков / горизонтальных столбиков (в линейчатых диаграммах)
В Таблицах нельзя, но мы сделаем такое формулой.
Изображение будем получать с помощью IMAGE. Ссылку на изображение — с помощью IMPORTXML с одного из сайтов с иконками, куда будем вставлять название нужной категории (у нас статистика по домашним животным в одной из стран). Если сайт иностранный, то название переводим GOOGLETRANSLATE.
С помощью INDEX берем одну из ссылок со страницы с результатами поиска — это далеко не всегда одно из первых значений, потому что в начале на сайте может быть много общих картинок, не относящихся к поиску, и выясняется это методом научного тыка.
Клонируем изображение в ячейки с помощью MAKEARRAY. Параметры: строка одна (если имитируем линейчатую диаграмму), а число изображений можно по такой логике:
выясняем максимальную разрядность у наших чисел (в примере 7), возводим 10 в СТЕПЕНЬ на единицу меньше, то есть в нашем примере миллион, и на это число делим.
Потому что делить на десятки миллионов смысла нет — везде будет по одному изображению, на сотни тысяч тоже — будет слишком много картинок. Тут каждая будет символизировать миллион, для других чисел все равно будет адекватное количество картинок с той же формулой.
Округление вверх — тогда даже маленькое значение будет отображаться как одна картинка.
В Таблицах нельзя, но мы сделаем такое формулой.
Изображение будем получать с помощью IMAGE. Ссылку на изображение — с помощью IMPORTXML с одного из сайтов с иконками, куда будем вставлять название нужной категории (у нас статистика по домашним животным в одной из стран). Если сайт иностранный, то название переводим GOOGLETRANSLATE.
С помощью INDEX берем одну из ссылок со страницы с результатами поиска — это далеко не всегда одно из первых значений, потому что в начале на сайте может быть много общих картинок, не относящихся к поиску, и выясняется это методом научного тыка.
Клонируем изображение в ячейки с помощью MAKEARRAY. Параметры: строка одна (если имитируем линейчатую диаграмму), а число изображений можно по такой логике:
выясняем максимальную разрядность у наших чисел (в примере 7), возводим 10 в СТЕПЕНЬ на единицу меньше, то есть в нашем примере миллион, и на это число делим.
Потому что делить на десятки миллионов смысла нет — везде будет по одному изображению, на сотни тысяч тоже — будет слишком много картинок. Тут каждая будет символизировать миллион, для других чисел все равно будет адекватное количество картинок с той же формулой.
Округление вверх — тогда даже маленькое значение будет отображаться как одна картинка.
Forwarded from Магия Excel
Магия новых функций Excel: революция в формулах 🔥
Друзья, мы с Лемуром рады анонсировать новый мини-курс, посвященный новым же функциям Excel (и — по большей части — Google Таблиц).
Это функции, которые могут решать задачи, ради которых раньше пришлось бы писать макросы или создавать запросы в Power Query!
Некоторые из них делают то же, что и стандартные инструменты Excel (как сортировка, фильтрация, удаление дубликатов или разделение текста на столбцы). Но, будучи формулами, делают это в режиме реального времени, с обновлением при изменении данных.Некоторые из этих функций кажутся сложными. Но на самом деле они проще тех же самых макросов и мы в этом убедимся.
Некоторые из них кажутся бесполезными. Но их просто нужно уметь готовить — и вся их мощь раскрывается зачастую только при сочетании нескольких в одной формуле. Мы рассмотрим мно-о-ого таких комбинаций.
В честь запуска новой версии сайта и этого курса — зверская цена от Лемура — 890 рублей! 🔥
Никаких уловок и вечных скидок не будет: 18 ноября цена вырастет до 2300 и больше уже никогда не вернется к старой.
Внутри:
14 видео от 5 до 25 минут с качественным монтажом
Дополнительные текстовые материалы
Файлы со всеми примерами и формулами — исходные и готовые.
Покупать тут — и там же подробная программа и примеры:
https://shagabutdinov.ru/magic-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
Посмотрите, вдруг вы пропустили что-нибудь сочное:
— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →
— 🧞♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →
— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →
— ОТПРАВЛЯТОР 2, создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию →
— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →
— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →
— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →
— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →
— Скриптами определяем, когда освободится домен →
— Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц) →
— DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы →
— С помощью скрипта даём доступ к другой Таблице, чтобы IMPORTRANGE заработал сразу →
— Защищаем скрипты от редактирования →
— Регулярный бэкап Таблиц в формате XLSX в телеграм! →
— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →
— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →
— Важный скрипт. Связанные выпадающие списки из кэша →
🏄 Заказ у нас @vas_mnogo_a_ya_bot
Media is too big
VIEW IN TELEGRAM
Загружаем файлы прямо из Таблицы
Друзья, привет! Презентуем вам Таблицу со скриптом, с помощью нее вы сможете загружать файлы на Google Диск и возвращать в Таблицу ссылку на загруженный файл
Как вы можете это использовать? Допустим, вы торгуете на WB / OZON, делаете в Таблице каталог своих товаров и сразу же загружаете через эту Таблицу фотографии этих товаров.
Таблица (делаем копию)
Ниже просто код:
📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас
Друзья, привет! Презентуем вам Таблицу со скриптом, с помощью нее вы сможете загружать файлы на 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
📕 Оглавление канала
🏄 Заказ работы у нас
Google Таблицы
Загружаем файлы прямо из Таблицы Друзья, привет! Презентуем вам Таблицу со скриптом, с помощью нее вы сможете загружать файлы на Google Диск и возвращать в Таблицу ссылку на загруженный файл Как вы можете это использовать? Допустим, вы торгуете на WB / OZON…
Сделали доработку и теперь скрипт возвращает в ячейки:
- ссылку на загруженный файл
- тип файла
- название
PS Если нужна только ссылка - то закомментируйте 10 строку и раскомментируйте 9 строку.
- ссылку на загруженный файл
- тип файла
- название
PS Если нужна только ссылка - то закомментируйте 10 строку и раскомментируйте 9 строку.
У вас есть дата, а вы хотите номера/названия месяцев в отдельном столбце (допустим, для сводной, для отчетов, для фильтрации) — 7 вариантов
Самый простой вариант — функция MONTH / МЕСЯЦ. Это число, порядковый номер (3 для марта, 11 для ноября).
С помощью функции TEXT / ТЕКСТ можно получить также вариант с нулем для коротких номеров (03 для марта, но 11 для ноября). С помощью нее же — текстовые варианты (мар. и марта для российских региональных настроек).
Если "марта" вам не нравится и вы хотите использовать абсолютно любые варианты, то можно брать их из диапазона с помощью INDEX. Или из виртуального массива внутри формулы с помощью того же INDEX или VLOOKUP или CHOOSE.
По ссылке — семь с половиной вариантов, включая формулу массива, выдающую ваши названия месяцев для всего столбца (без вспомогательного диапазона; названия внутри формулы).
Таблица с примерами формул
Самый простой вариант — функция MONTH / МЕСЯЦ. Это число, порядковый номер (3 для марта, 11 для ноября).
С помощью функции TEXT / ТЕКСТ можно получить также вариант с нулем для коротких номеров (03 для марта, но 11 для ноября). С помощью нее же — текстовые варианты (мар. и марта для российских региональных настроек).
Если "марта" вам не нравится и вы хотите использовать абсолютно любые варианты, то можно брать их из диапазона с помощью INDEX. Или из виртуального массива внутри формулы с помощью того же INDEX или VLOOKUP или CHOOSE.
По ссылке — семь с половиной вариантов, включая формулу массива, выдающую ваши названия месяцев для всего столбца (без вспомогательного диапазона; названия внутри формулы).
Таблица с примерами формул