​​Пополняем обзор околотабличных книг еще тремя изданиями!
Книга из серии "для чайников" про Excel 2019, "Бизнес-анализ в Excel" и визуальное руководство по Google Workspace.
Эти и все остальные книги с обложками, подробными описаниями и ссылками - в обзоре:
https://teletype.in/@renat_shagabutdinov/excellent_books

Teach Yourself VISUALLY Google Workspace
Хорошее руководство по Google Workspace (бывший G Suite) для новичков и продолжающих.

Богато иллюстрированное (как полагается в этой серии, а это большая серия книг по разным приложениям) издание.

Excel 2019 для чайников
Как всегда в этой серии — подробно, по шагам, много таблиц, дополнительных разъяснений. Но выбор тем удивляет при этом. Очень мало про функции и формулы, практически ничего. Но есть небольшая вводная глава про макросы, например.

Бизнес-анализ в Excеl: Финансы, продажи, логистика
Все же скорее это не бизнес-анализ, а справочник по Excel. Вот, например, глава про текстовые функции — там нет бизнеса, только перечисление функций и описание их синтаксиса. Собственно бизнес-анализ — последние главы, они очень короткие и простые (теория очередей, складская логистика, инвестиционный портфель, точка безубыточности) — кажется, что эти темы нужно отдельно изучать, а Excel отдельно.

И такой скрипт у нас есть:

🧞‍♂️Превращатор Листа Таблицы в xlsx / pdf / csv
— файл мгновенно скачивается
— либо сохраняется на Google Диск
t.me/google_sheets/652

Другие вопросы можно задать в нашем чате: @google_spreadsheets_chat

И просто полистать оглавление нашего канала: goo.gl/HdS2qn

Привет, завтра выложим скрипт для автоматического превращения изображений в текст :)

Распознаем текст изображений / PDF скриптом

Друзья, в Google Docs есть технология OCR (оптическое распознавание текста).

Технология доступна из скриптов, задаете файл, далее скрипт используя Drive API создаёт новый файл на Google Диске, заменив все изображения заданного файла на распознанный текст.

Это сработает с файлами PDF, изображениями и Google Документами со вложенными изображениями.

Создали для вас Таблицу со всем нужным кодом. Как применить.
1) Делаем копию Таблицы со скриптом;

2) В ячейку листа "config" вставляем URL файла (пойдет PDF, картинка, Google Документ с картинками);

3) Запускаем скрипт кнопкой "GO!", скрипт создаст из заданного файла новый Google Документ с текстом и вставит весь текст из него на лист "OCR";

4) Хотите сразу удалять временный файл – уберите комментарий (//) с 29 строки кода;

Fin

⭐️ Наш чат: t.me/google_spreadsheets_chat

⭐️ Оглавление нашего канала, в котором множество других скриптов: goo.gl/HdS2qn

⭐️ Про OCR:
habr.com/ru/post/114670/
habr.com/ru/post/97173/

❗️ P.S. К сожалению, технология только развивается и некоторые скриншоты распознаются весьма примерно. Чтобы получить хороший результат – исходное изображение должно быть в высоком качестве.

Расскажите в комментариях о том, что получилось у вас

Схватка двух ёкодзун. Сравнение Google Таблиц и Excel

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

Пишите в комментариях, что, на ваш взгляд, стоит добавить в таблицу.

И с наступающим Новым годом! Увидимся после праздников 🥂

Кликаем на ✅ и защищаем от редактирования строку от всех, кроме владельца Таблицы

Мы подготовили простой скрипт, который это умеет.

Чтобы скрипт работал и строки закрывались:
— вставляйте код в редактор скриптов вашей Таблицы (расширения > apps script)
— в редакторе скриптов создайте триггер на редактирование таблицы (триггеры > добавление триггера > при редактирование > функция goClose).

Если вы создадите триггер от владельца Таблицы, то строка будет закрываться даже от того, кто поставит чекбокс. Разумеется, если владелец Таблицы не он :)

Для нескольких человек, которые пытаются разобраться в скриптах с 0 мы прокомментировали каждую строку кода: pastebin.com/gy6LBS60

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

PS ❗️ Друзья, триггер на редактирование работает неспешно, поэтому кликайте на чекбоксы не очень быстро, так скрипт ничего не пропустит.

⭐️ Наш чат: t.me/google_spreadsheets_chat
⭐️ Оглавление нашего канала, в котором множество других скриптов: goo.gl/HdS2qn
⭐️ Стать патроном нашего канала

Закрываем от редактирования столбцы по чекбоксу (✅)

В комментарии к нашему посту "кликаем на ✅ и закрываем строку от редактирования" пришло несколько интересных вопросов, отвечаем.

— А можно такое же, но для столбцов?

Можно! На ГИФКЕ показали, как этот скрипт будет работать, а ниже – ссылка на сам код с комментариями по каждой строке для тех, кто хочет научиться писать скрипты самостоятельно.

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


Cкрипты с максимальными комментариями по каждой строке:
Закрываем столбец по чекбоксу: pastebin.com/BH5mnD8d

Закрываем строку по чекбоксу:
pastebin.com/gy6LBS60

⭐️ Стать патроном нашего канала
⭐️ Наш чат: t.me/google_spreadsheets_chat
⭐️ Оглавление нашего канала, в котором множество других скриптов: goo.gl/HdS2qn

Активируем чекбокс ✔️ — создаем защиту строки, снимаем 🔘 — защита удаляется

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

Итак, еще один, уже третий скрипт. Чтобы скрипт заработал – вставьте код в свою Таблицу, заполняете переменные и ставьте скрипт на триггер редактирования Таблицы (триггер должен создать владелец Таблицы).

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

Cкрипты с комментариями по каждой строке
Текущий, создаем / удаляем защиту строки: pastebin.com/kVzm7BpX

Закрываем столбец по чекбоксу: pastebin.com/BH5mnD8d

Закрываем строку по чекбоксу:
pastebin.com/gy6LBS60

⭐️ Стать патроном нашего канала
⭐️ Оглавление нашего канала: goo.gl/HdS2qn

Несколько советов для работы в браузере Google Chrome

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

Пишите в комментариях свои лайфхаки - добавим в пост!

Видео: Пользовательские числовые форматы в Google Таблицах

Для тех, кто предпочитает формат видео - разбор основных правил создания пользовательских числовых форматов (Custom number formats) в Google Таблицах (в Excel все похоже, есть небольшие нюансы - о них в нашей таблице со сравнением Excel и Таблиц).

Таблица из видео (создать копию)

Еще по этой теме:
Статья
Памятка по применяемым символам

Случайный подбор и перестановка
– памятка от Михаила Смирнова

1) Выбор случайного значения из списка
2) Выбор k-случайных значений из списка с повторениями
3) Выбор k-случайных значений из списка без повторений
4) Случайная перестановка списка

Таблица c примерами от Михаила

Функции для генерации случайных чисел волатильные, то есть пересчитываются при любых изменениях. Поэтому результаты вычисления формул с ними пересчитываются. Если вам нужно их зафиксировать — скопируйте и вставьте как значения (Ctrl+Shift+V).

⭐️ Стать патроном нашего канала
⭐️ Оглавление нашего канала: goo.gl/HdS2qn

Рубрика "Это не помешало бы нам в Таблицах"

В Excel есть два приема для обработки нескольких листов в формулах. Надеемся, что когда-нибудь они доберутся и до Таблиц :)

Первый аспект известен многим - возможность ссылаться на несколько листов через двоеточие. Следующая формула суммирует числа из ячеек A1 на листах от Москвы и до Саратова включительно:

=СУММ('Москва:Саратов'!A1)

"От и до" значит, что эти листы (их ярлыки) идут слева направо в книге. Если какой-то из них переместится за пределы этого набора листов - ячейка с него перестанет вычисляться. И наоборот, если после Москвы или до Саратова появится новый лист - он попадет в вычисление.

А второй аспект - возможность применять символ подстановки * в ссылках на листы.
Допустим, у вас в книге много листов со словом "Расходы" в названии ("Расходы январь", "Расходы февраль", ... ). Следующая формула позволит просуммировать ячейки A1 со всех этих листов:

=СУММ('Расходы*'!A1)

Правда, в отличие от ссылки с двоеточием, звездочка в формуле не сохранится - после ввода такой формулы ссылка на лист со звездочкой превратится в формулу с отдельными ссылками:

=СУММ('Расходы январь'!A1;'Расходы февраль'!A1;'Расходы март'!A1;...)

Но все же!

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум
⭐️ Стать патроном нашего канала

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

Статья: telegra.ph/Programmno-dayom-dostup-dlya-IMPORTRANGE-k-drugoj-tablice-07-25

Скрипт из статьи (даём доступ к трём таблицам): pastebin.com/WfgqTEKD

Флаг вам в руки — суммируйте что хотите. SUMIFS с флажком

Задача: вставить в таблицу флажок, который будет включать или отключать один из критериев функции СУММЕСЛИМН / SUMIFS (ну или ее аналогов для подсчета/усреднения COUNTIFS, AVERAGEIFS, COUNTUNIQUEIFS).

Вставляем флажок и добавляем функцию IF / ЕСЛИ, которая в соответствующем аргументе будет возвращать условие, если флажок включен, и “*”, если флажок выключен. Так как звездочка — символ подстановки (wildcard), соответствующий любому тексту, то одна звездочка = любое значение = отсутствие всякого условия для соответствующего столбца.

=SUMIFS(F:F;B:B;IF(I4;J4;"*"))

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

=SUMIFS(F:F;B:B;IF(I4;"*"&J4&"*";"*"))

Понятное дело, таким образом и несколько флажков можно сделать для разных критериев — как в примере по ссылке (создать копию).

P.S. Благодарим функцию RANDBETWEEN / СЛУЧМЕЖДУ за предоставленные (сгенерированные случайно) данные

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум
⭐️ Стать патроном нашего канала

Ночью закрываем Таблицу от редактирования, а днем возвращаем редакторам их права. Скриптами.

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

Мы написали для вас скрипт и прокомментировали в нем каждую строку с кодом: pastebin.com/fACHx817

Внутри три функции:

from_edit_to_view — меняем права всех редакторов документа (кроме себя) с редактирования на просмотр;

from_view_to_edit — меняем права всех пользователей с просмотром на редактирование;

main — главная функция, определяет текущее время и запускает или первую или вторую функции;

Вставляете код в вашу Таблицу, выставляете во 1-2 строках временные рамки, ставите функцию на триггер, чтобы она запускалась каждые n-часов и получаете автоматический и ежедневный перевод из читателей в редакторы и обратно.

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

ЗАЩИЩАЕМ СКРИПТЫ ОТ РЕДАКТИРОВАНИЯ

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

Рассказываем Александру и другим как защитить свои скрипты от редактирования.

Способ первый – библиотека

Уносим скрипты в библиотеку, открываем библиотеку для всех на просмотр, подключаем библиотеку к Таблице и оставляем в редакторе скриптов Таблицы только функцию для вызова функции из библиотеки:

function something(){
lib.something();
}

Что такое библиотеки и как с ними работать отлично написал Миша Смирнов: t.me/google_sheets/828

Способ второй - "inject", подходит для скриптов, которые можно положить на триггер

Создаем в Таблице из другой Таблицы скрипт и триггер на него, который не увидят владельцы первой Таблицы вообще: t.me/google_sheets/753

Способ третий - развернутое приложение

1) переносим скрипты в другую таблицу / другой отдельный скрипт;
2) пишем там doGet функцию, которая будет принимать на вход название скрипта, разные аргументы, сами скрипты убираем в объект (смотрите в примере кода);
3) разворачиваем всё это как приложение, получаем ссылку приложения;
4) пишем функции в таблице, в которой вы хотите запускать скрипты, функции должны будут отправлять на адрес вашего приложения (пункт 3) названия скрипта, который надо запустить, а также аргументы, которые в этот скрипт надо передать (смотрите в примере кода, мы отправляем ключ и id таблицы);
5) всё! пользователь в Таблицы выбирает в меню своей Таблицы пункт, маленькая функция в Таблице отравляет на адрес развернутого приложения имя функции, которую нужно запустить и параметры. Далее приложение от имени того, кто его развернул запускает требуемый скрипт с переданными в него параметрами;

Примерный код: pastebin.com/TnBazuut

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Рубрика “Чего нам не хватало в Excel”

Теперь в Excel — увы, только у подписчиков Microsoft 365 — появился аналог GOOGLEFINANCE.
Функция называется STOCKHISTORY / ИСТОРИЯАКЦИЙ.

Аргументы:
stock / акции — это тикер (например, "GOOG") или коды валютной пары (например, "USDRUB" или "BTCRUB"). В кавычках или в виде ссылки на ячейку с текстом.
start_date и end_date / нач_дата и кон_дата — период, за который загружаем данные.
interval / интервал — периодичность. 0 = ежедневно(по умолчанию), 1 = еженедельно, 2 = ежемесячно.
headers / заголовки — по умолчанию 1 (1 строка с заголовками, дата и название параметра), еще есть варианты 0 — без заголовков и 2 — две строки, где первой будет название тикера / валютной пары.
свойства1; свойства2;…; свойства5 — с помощью этих аргументов мы набираем те параметры, которые будут возвращаться, в желаемом порядке. Вот набор параметров:
0 — Дата
1 — Закрытие (Close)
2 — Открытие (Open)
3 — Высокая (High)
4 — Низкая (Low)
5 — Объем (Volume)

Например, в следующем случае будeт выводиться дата (0), объем (5) и цена закрытия (1):

=ИСТОРИЯАКЦИЙ("GOOG";...;0;5;1)

По умолчанию будут дата и цена закрытия.

Функция будет возвращать динамический массив. Если ей не хватит места для возвращения результата — будет ошибка #SPILL! / #ПЕРЕНОС!

Подробнее про источники данных для функции

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Друзья, повторяем нашу подборку полезных постов:

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

Функции:
​​— Учимся извлекать из текста нужное. База знаний регулярных выражений для REGEXEXTRACT →→
— Памятка по синтаксису QUERY / FILTER / SUMIFS →→
— Функция FILTER. Список условий выбираем диапазоном прямо с листа →→
— Обоюдоострый FILTER. Убираем с помощью одной формулой из таблицы пустые строки и столбцы →→
— SUMIF для нескольких условий в формуле массива →→
— Формулой достаем изображения из поисковой выдачи Яндекса →→
​​— ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце) →→
— ВПР в массиве вместо тысячи CУММЕСЛИМН (заполняем весь лист одной формулой) →→
— Автоматически создаем фразы по определенным шаблонам в Google Таблицах →→
— Пользовательские числовые форматы, добавляем к числам 🔥🔥🔥 →→

Скрипты:
— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →→
— Простой скрипт копирования / фильтрации (поможет, когда IMPORTRANGE перестает работать) →→
— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →→
— Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →→
— Скрипт, отправляем письма на электропочты из диапазона, который вы выделяете →→
— Простой скрипт для ежедневной рассылки из Google Таблицы →→
— Важный скрипт. Связанные выпадающие списки из кэша →→

Проекты:
— Telegram bot + Google Sheets (используя вебхуки) →→
— Эмоджи форматирование выполнения плана (IMAGE, IFS) →→
— Создаем красивое расписание групповых занятий →→
— Создание оглавления в телеграм-канале: как автоматически загружать и сортировать публикации из вашего рабочего файла →→
— Google Форма + Google Таблица для проведения тестирования →→
— Применение IFTTT для отправки сообщений с данными из Таблиц →→
— Googlefinance, Парето и графики (#готовое решение) →→
— Создаем инфографику с помощью IMAGE. Размер картинок пропорционален значениям →→
— Импортируем таблицу из веб-страницы и оставляем только нужные нам столбцы (IMPORTHTML+QUERY) →→

Для новичков:
— Мы на Хабре: "Функции Таблиц, которых нет в Excel": https://habrahabr.ru/post/331360/
— Советы по оптимизации Таблиц →→
— Видеоурок по функции IMPORTRANGE (для связывания нескольких таблиц) →→
— Видеоурок по фильтрам и режиму фильтрации →→
— Про виды доступа к документам →→
— Совместная работа с фильтрами →→

Полное оглавление нашего канала: https://goo-gl.ru/5Kc3
Наш чат: @google_spreadsheets_chat