Тем временем книга "Магия таблиц" получила статус "Хит" на Озоне и "Бестселлер", "Хит продаж" на Литресе🍾

Да, Excel'я в ней будет побольше, чем Google Таблиц, но в книге есть уточнения по тому, доступны ли те или иные функции или инструменты в Таблицах, есть отдельная информация про IMPORTRANGE, совместную работу, регулярные выражения в Google Таблицах, про новые функции (LAMBDA и вспомогательные функции, функции для работы с массивами), которые в Google даже доступнее, ибо есть у всех, а в Excel только у пользователей последней версии 🤠

А вот и первые отзывы:
отличная подача материала. намного дешевле курса!

Большое количество полезных фишек для ускорения работы

Отличная книга, великолепная подача, все «разжевано». Можно читать как подряд, так и местами, которые нужны. Пока это самое понятное пособие по Excel, которое я читала

Это руководство настолько подробно описывает процесс создания той или иной таблицы, что даже ребёнок лет 6 разберётся. Для взрослого это руководство находка - поскольку открываешь книгу и делаешь как написано.

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

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

На сайте издательства
Book24
Лабиринт
Озон
Литрес (электрическая) — кстати, электрокнига в цвете, в отличие от печатной (та черно-белая)
Wildberries

Если уже прочитали, напишите отзыв, будем признательны 😺

Вопрос жизни и смерти

В нашего бота, в котором можно заказать работу пришел вот запрос (скриншот).

Ну что же, давайте поможем, нельзя дать никому умереть.

Переходите по ссылке на наш пост с подборкой маленьких, но очень полезных onEdit скриптов:
t.me/google_sheets/1133

Многие скрипты с комментариями 😉

Нашему чату про Таблицы почти исполнилось 6 лет!

А еще в почти 5000 участников, давайте доберем: @google_spreadsheets_chat

Хотим провести жеребьевку и распределить команды (или что-то еще) по группам или корзинам?

Можно использовать функцию WRAPCOLS. Укажем вторым аргументом, сколько команд должно быть в группе — и готово. Чтобы все было случайно, можно перемещать исходный диапазон (Данные — Перемешать ячейки диапазона, Data — Randomize Range)

=WRAPCOLS(диапазон с командами; число в одной группе)

Но если не хотим перемешивать диапазон вручную, а чтобы формула сразу распределяла команды случайно, то воспользуемся сортировкой (SORT) по случайному диапазону (RANDARRAY).

SORT(диапазон; RANDARRAY(число элементов); 1)

И все вместе:
=WRAPCOLS(SORT(диапазон;RANDARRAY(число элементов в нем);1); число элементов в группе)

Спасибо нашему любимому автору Бену Коллинзу за идею!

Персональная техподдержка компаний от @google_sheets

Друзья, привет! Наша новая услуга – внедрение нашей команды (автор канала Евгений и его спецы) в ваш бизнес для помощи по Таблицам и GAS-скриптам.

💥Как это выглядит

Мы создаём чатик, вы добавляете своих коллег, мы добавляем наших асов.

Вы пишете в чатик вопросы по формулам, Таблицам, просите написать небольшие скрипты, обращения к api, телеграм-ботов, консультируетесь.

Мы со своей стороны быстро и профессионально* вам помогаем.

*наш канал существует с 2017 года, наш чат существует с 2018 года и каких только задач за это время не приходилось решать, а также авторы канала написали книгу и записали курс.

💥 Условия

Сейчас возьмем 2-3 компании, стоимость 225 000 р. за три месяца, далее по 75 000 р. в месяц, если захотите продолжить.

Пишите @namokonov, обсудим.

Что входит в наш пакет поддержки компаний:

1) Разумеется, помощь по формулам, напишем с вами самые сложные и оптимальные, перепишем ваши медленные;

2) Скрипты, пара примеров:

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

— Сможем работать скриптами с Googlе Диском, пришел работник: автоматически расшарили для него пакет документов, работник оказался слаб духом, не выдержал темпа и уволился, в тот же день уберем эти файлы из его доступа;

— Сможем создавать Google Документы / Word файлы со счетами из данных Таблиц;

3) Интеграции со внешними API, пример: торгуете на озон и ВБ: сможем настроить скрипты так, чтобы данные о ваших остатках, продажах и заказах попадали сразу в Таблицу, также сможете менять цены и описание товаров из Таблицы

4) Телеграм боты, полезные для вашего бизнеса:

— Сотрудник работает на полях? Сделаем бота для удобной отправки данных прямо в Таблицы / базу данных BQ;

— Менеджменту нужно отслеживать KPI в режиме реального времени? Настроим бота, который будет отправлять сообщения в чаты, когда выбранные метрики в Таблицах выйдут за установленные рамки;

— Бот, который мы делали для компании DiDI: пользователь садится в такси, быстро находит через поиск номер а/м и отвечает на ряд вопросов по водителю и по поездке (в моменте ботом пользовались 100 человек), все отправляется и в Таблицу и в BQ;

...
Друзья, это просто примеры того, что можно сделать. Когда мы говорим "Таблицы", то подразумеваем не только формулы, пускай даже самые сложные, Таблицы – площадка, на которой можно развернуть любые скрипты, сделать веб-приложение и получить действительно большую пользу для своего бизнеса. Это не курс от коуча, который вас научит как "зарабатывать" деньги, это действительно полезные интеграции, которые мы можем внедрить у вас.

Если остались вопросы по пакету, пишите @namokonov, обсудим.

Сколько все стоит и какие условия

Обращаемся к Телеграм и достаём

Друзья, мы к вам опять с подарками: Таблица и скрипт, который достаёт по юзернейму канала в Телеграме ссылку на последний пост и сам этот пост, его просмотры и ссылку на изображение в посте.

Всё просто, делайте копию Таблицы, вводите юзернеймы каналов в A2:A и запускайте скрипт из меню.

Это уже третий наш пост про то, что можно достать из Телеграм, два предыдущих:

Достаём скриптом количество подписчиков чатов и каналов в Телеграм

Достаём из телеграм каналов посты и просмотры

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

А вот еще одна новинка, но несопоставимого масштаба... код Python будет работать прямо в Excel!

Пока все только начинается — с бета-каналом обновлений группа Python не появилась сама, ее можно добавить как на скриншоте — через параметры ленты инструментов. Но все равно ничего еще не работает.

Работать будет прямо в ячейках — можно будет начинать ввод с =PY в формуле или через ленту инструментов.
А вычисления будут производиться в облаке. То есть будет нужен доступ к сети.

Для ссылок на объекты (ячейки и диапазоны, данные в "умных" таблицах) Excel будет использоваться функция Python xl().

Ссылки:
Официальная новость
Общие сведения о Python в Excel
Начало работы с Python в Excel
Python in Excel (на странице дистрибутива Anaconda)

Выведем, когда и кем менялся конкретный файл на Google Диске сегодня

Друзья, опять мы к вам с подарками и с простым скриптом.

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

Чтобы скрипт заработал не забудьте добавить Drive Api в сервисах в редакторе скриптов:

function main() {
const dt = new Date(new Date().setHours(0,0,0,0));
const url = 'https://docs.google.com/...';
Logger.log(listFileRevisions(url).filter(x => x[1] >= dt));
};

function listFileRevisions(file) {
return Drive.Revisions.list(file.match(/[-\w]{25,}/)[0])
.items.map(revision => {
return [
revision.id,
new Date(revision.modifiedDate),
revision.lastModifyingUserName,
revision.lastModifyingUser.emailAddress,
];
});
};

@zadavai_vopros_bot

Опыт использования нашего ИИ-бота от Веро - пишем текст для регулярной рассылки.

Да-да, поздравления с 23 февраля и 8 марта для коллег вы тоже сможете писать с помощью ИИ и даже в «стихах» :)

Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом

КопиПастер от Александра

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

Написал для работы простую, но удобную программку. Может кому тоже пригодится. Смысл такой: юзер добавляет в колонку дежурные фразы, которые ему приходится часто отправлять по почте, в чатах, в ТМС и т.д. Чтобы каждый раз не писать много текста, можно один раз эти фразы написать в таблице. Они списком попадают в сайдбар, откуда их можно отредактировать, если надо и скопировать в буфер кнопкой. Казалось бы ерунда, но может сэкономить кучу времени в рутинной работе.

PS Ну понятно, что скрипт не наш, мы бы это назвали "вставлятор" 😅

--
продаём разные скрипты для маркетплейсов, к примеру - загружаем статистику по рекламным кампаниям и в озоне и в ВБ скриптами и в Таблицу (пишите @namokonov)

Вычисляемое поле в сводной: умножаем сумму на одно значение

Бывает, что нужно умножить сумму всех значений (например, продаж в штуках или выработки в тоннах) на одно значение (например, цену).

По умолчанию у нас будут суммироваться все значения всех полей.
То есть при такой формуле:

=Продажи*Цена

В рассчитываемом поле в сводной просуммируются все продажи (в том контексте, который задан в строках и столбцах сводной) и все цены, и на выходе будет произведение этих двух сумм. Многовато!

А нам надо:

= Все продажи * одна цена

В таком случае выбираем в редакторе сводной в рассчитываемом поле "Суммировать по:" — "Другое".
И тогда мы получим только первую продажи и только первую цену.
Добавляем к полю с продажами (тому, которое нужно суммировать) функцию SUM / СУММ и будет нам счастье.

=СУММ('Поле, которое суммируем') * 'Поле, которое не суммируем'

Для любителей видео — пара минут со звуком.

P.S. В Excel такую задачу можно решить, построив сводную на основе модели данных и потом создав меру с функцией SUMX, которая обрабатывает данные построчно.

Друзья, присылайте нам (контакты в профиле) ваши материалы про Google Таблицы, скрипты и интеграции вокруг. То, что будет интересно нашей аудитории - будем выкладывать. А с нас – плюшки :)

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

-
🤖 Представляем вам готовое решение для возможности развернуть своего Telegram бота с технологией оптического распознавания символов (OCR)!

С помощью этого кода вы сможете создать бота, который будет распознавать текст на изображениях и отправлять его обратно вам. И самое лучшее – для работы бота используются только бесплатные сервисы Google!

🔍 Оптическое распознавание символов (OCR) – это технология, которая позволяет распознавать текст на изображениях или сканированных документах. С ее помощью можно автоматизировать процесс распознавания текста и сэкономить время.

📝 Для разворачивания бота необходимо выполнить следующие шаги:

1️⃣ Создайте новый Telegram бот.
2️⃣ Получите api-токен вашего бота.
3️⃣ Скопируйте себе данный standalone script.
4️⃣ Опубликуйте скрипт как веб-приложение (от лица своего аккаунта и для всех, если не понятно — вот видео) и скопируйте URL.
5️⃣ Заполните переменные botToken, webAppUrl, folderId.
6️⃣ В разделе "Сервисы" добавьте "Drive API" (если не добавился при копировании).
7️⃣ "Взведите" бота с помощью функции setWebhook().
8️⃣ Отправляйте боту изображение с текстом для теста.

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

⚠️ Важно отметить, что технология оптического распознавания символов (OCR) не всегда может быть 100% точной. Некоторые факторы, такие как качество изображения, шрифт, язык и другие факторы могут повлиять на точность распознавания текста. В результате могут возникать неточности или ошибки в распознанном тексте.

При использовании этого кода и технологии OCR рекомендуется учитывать возможность возникновения неточностей и проверять результаты распознавания перед их использованием.

НАШ ЧАТ, про Таблицы и скрипты

Проверка данных (Data validation) с формулами

Если вам не хватает готовых решений в проверке данных (проверка на числа, даты, вхождение символа/слова в текстовую строку, соответствие адресу электропочты) — пользуйтесь вариантом "Ваша формула" и создавайте свои собственные правила проверки.

Принцип здесь такой же, как и с использованием формул в условном форматировании: вы пишете формулу для первой ячейки диапазона, к которому применяете правила, и используете абсолютные и относительные ссылки ($). Представляйте, что вы вводите формулу прямо в первую ячейку диапазона проверки и потом будете ее "протягивать". Проверка данных будет запрещать ввод везде, где формула будет возвращать ЛОЖЬ / FALSE.

Примеры правил
Только уникальные значения
=СЧЁТЕСЛИ(диапазон;ячейка со значением)<=1
Проверка будет ругаться, если введенное значение уже встречалось в столбце A.

Только рабочие дни:
=ДЕНЬНЕД(первая ячейка;2)<6
Проверка будет ругаться, если номер дня недели у введенной даты - 6 или 7, то есть это суббота или воскресенье. Напомним, что у ДЕНЬНЕД / WEEKDAY нужно указать второй аргумент = 2, именно такой тип нумерации соответствует привычному для нас (где понедельник = 1).

Текст не длиннее 8 символов:
=ДЛСТР(первая ячейка)<=8
Здесь проверка будет ругаться, если вы ввели более 8 любых символов.

Только формулы:
=ISFORMULA(первая ячейка)

Таблица с примерами

Наш чат про Таблицы и про скрипты: @google_spreadsheets_chat

Выделил в Таблице – отправилось в Телеграм

Привет, друзья! Ну что, очередной вариант отправки из Таблиц в Телеграм чаты или каналы. Точнее, очередной скрипт.

Скрипт работает так: выделяете диапазон, в котором в первом столбце chatId (уникальный Id пользователя или чата в телеграм, узнать можно с помощью @myidbot), а во втором столбце текст, который мы хотим отправить.

Далее запускаем скрипт и он проходит по каждой строке, пытаясь отправить сообщение, если получается – пишет получилось, если нет – то выводит ошибку.

ПС не забудьте внести токен своего бота в редактор скриптов.

Таблица

Добавляем таблицам огонька: пользовательские числовые форматы

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

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

Таблица

Другие наши посты по теме:
Статья про пользовательские форматы
Видео

⭐️ Оглавление канала: ты-дыц

Превращаем строку с числами в спарклайн одной формулой

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

В A1 на скриншоте — значения, разделенные запятой. Превратим их в спарклайн (диаграмму в одной ячейке). Используем формулу:

=SPARKLINE(SPLIT(A1;",");{"charttype"\"column";"color"\"green"})

С помощью SPLIT с запятой в качестве разделителя (второй аргумент функции) делим нашу строку на отдельные числа, далее превращаем всё это в спарклайн типа "гистограмма" (столбики) "charttype"\"column", а "color"\"green" отвечает за цвет столбцов, green сможете поменять на нужный вам цвет. Можно использовать и шестнадцатеричные коды цветов.

Другие наше про спарклайны:
Спарклайн с условием \ Видеоурок: Синтаксис и примеры \ Что такое спарклайны? \ Динамический спарклайн

Курсы по Excel и Google Драйву

Друзья, несколько новостей по табличным курсам в МИФе

Во-первых, добавил новые видео и модули в эти курсы:
- В оба курса — модуль про функцию LAMBDA и вспомогательные функции BYROW/ BYCOL, SCAN и REDUCE, MAP и ISOMITTED (последняя в Excel). В случае с Гугл Драйвом — отдельное видео про именованные функции в Google Таблицах.
- Видео про ПРОСМОТРX / XLOOKUP в Google Таблицах — добавлено некоторое время назад, благо функция там появилась вскоре после Excel. В курсе про Excel эта функция и так, разумеется, была.
- Большое видео про Power Pivot (модель данных) в Магии Excel будет скоро.

Во-вторых, с 1 ноября цена на оба курса вырастет. Так что если думали про то, чтобы изучить основательно ту или иную тему или обучить свою команду/компанию (а на этих курсах у нас учатся сотрудники самых разных компаний, включая компанию из топ-10 крупнейших в России и национальный исследовательский университет) — самое время брать курс по старой цене с новыми материалами. Тем более несколько дней будет скидка — см ниже.

Немного фактов.
В каждом курсе есть исходные и заполненные файлы с примерами к каждому уроку, а в отдельных тарифах и подробные конспекты (суммарно как книга).

В Магии Excel 60+ уроков, в Гугл Драйве 90+.

Урокам можно ставить оценки. За все время у Гугл Драйва 600 оценок (из них 7 четверок и 2 тройки), у Магии Excel 245 оценок (тоже почти все пятерки: только 4 четверки, ни одной тройки).

Коллеги подкинули промокод на скидку, ловите:
GOOGLE
35% до 5 октября.

https://www.mann-ivanov-ferber.ru/courses/magicexcel/
https://www.mann-ivanov-ferber.ru/courses/gdrive/

Простое. Вопрос из чата: "кто подскажет, как из строки вывести четвертое по счету слово в другой столбец?"

Подсказываем. Используем функцию SPLIT и делим строку по пробелу на отдельные слова. Далее используем INDEX и берём четвертое слово.

=INDEX(SPLIT(W3;" ");4)

Либо тоже самое, но с новой функцией выбора столбца, который хотим оставить. Про новые функции.

=CHOOSECOLS(split(W3;" ");4)

Двигаем время формулами

💥 заказать работу | наш "пакет поддержки компаний"