​​В правиле условного форматирования можно использовать данные другого листа.

Друзья, привет! Речь о правиле условного форматирование формулой. Чтобы сослаться на другой лист - просто напишите диапазон и имя листа в кавычках и оберните в функцию INDIRECT (ДВССЫЛ). Такое правило будет работать.

Например: выделим города-миллионники.
Гифка: http://recordit.co/LBQufQSzIP

Формула из примера:
=COUNTIF(INDIRECT("'Города-миллионики'!A:A");A2)

=СЧЁТЕСЛИ(ДВССЫЛ("'Города-миллионики'!A:A");A2)

Таблица

Отправляем письма скриптом. Опять.

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

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

Рассказываем про решение

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

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

Доступы бывают:
- на просмотр без права копирования и скачивания - человек с таким доступом может только открыть файл и просмотреть его, но скопировать данные, распечатать файл или скачать его в любом формате он не сможет.
- на просмотр с правом копирования и скачивания - нельзя менять файл, но можно создать копию или скопировать данные. При создании копии владельцем станет тот, кто ее создал - и больше ни у кого доступа в новый файл не будет. Новый файл будет находиться на Google Диске того пользователя, который создал копию.
С любым из двух доступов на просмотр можно создавать временные фильтры - они не видны другим и удаляются после выхода из файла. Можно просматривать чужие фильтры (filter views), но нельзя их редактировать.

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

- на редактирование - можно изменять ячейки, добавлять и удалять листы, копировать документ, ставить обычный фильтр прямо на рабочем листе, создавать и удалять фильтры (filter views). Временные фильтры, созданные пользователями с правом на просмотр или редактирование в рамках сеанса не видны редакторам.
Доступ тоже бывает двух видов - с правом добавления новых редакторов или без него. В первом случае каждый из редакторов сможет добавить новых, которые в свою очередь смогут править документ. Во втором случае у пользователей будет только возможность редактировать документ, новых редакторов сможет добавлять только владелец.

Все эти доступы можно давать конкретным пользователям или по ссылке.
Доступ по ссылке означает, что любой человек, имеющий эту ссылку, сможет документ просматривать/комментировать/редактировать.
Если у вас G Suite (корпоративный аккаунт), вы сможете открывать доступ по ссылке только пользователям из вашего домена (вашим коллегам, имеющим корпоративную почту Gmail).

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

О том, как менять и как передавать доступ со скриншотами.

​​Наш читатель Коля из Тернополя спрашивает: "Ребят, есть список картинок, с названиями 1.jpg, 2.jpg и т.д. (шаг - единица). Как мне узнать, картинок с какими номерами нет, если известно, что всего их, например, 10?"

Коля, есть способ:
1) Формируем список от 1 до 10 с помощью функции ROW() / СТРОКА()
2) Добавляем к каждому элементу списка - ".JPG"
3) Помещаем полученный список из всех возможных картинок в функцию FILTER в диапазон вывода и в диапазон условия, критерием условия станет список картинок, которые у нас есть. Формула выведет только то, что есть в первом списке и отсутствует во втором.

Итоговая формула: =FILTER(ROW(A1:A10)&".jpg";ISNA(MATCH(ROW(A1:A10)&".jpg";A3:A6;0)))

Если писать функции на русском языке:
=FILTER(СТРОКА(A1:A10)&".jpg";ЕНД(ПОИСКПОЗ(СТРОКА(A1:A10)&".jpg";A3:A6;0)))

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

​​Задача из практики нашего клиента.

Дано: выгрузка из 1С, в которой есть вес и цена товара в каждом заказе. Нужно считать стоимость по товарным категориям без промежуточных расчетов.

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

Просто перемножение двух столбцов с условием на товарную категорию решается легко - функцией FILTER.
=СУММ(FILTER($B$2:$B*$C$2:$C;$A$2:$A=E3))

А чтобы автоматически приравнивать пустые ячейки в одном из столбцов к единице, добавим функцию ЕСЛИ/IF:
=СУММ(FILTER(ЕСЛИ($B$2:$B="";1;$B$2:$B)*$C$2:$C;$A$2:$A=E3))

(вид выгрузки на скриншоте упрощен для примера, но сути это не меняет).

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

Три простые формулы:

1) Номер строки:
=ROW( )
Если ничего не вводить в круглых скобках, то вы получите номер строки, в которую введена формула

2) Адрес ячейки:
=CELL("address";A3)

3) Буква столбца из адреса ячейки:
=REGEXEXTRACT(CELL("address";A6);"[A-Z]+")

Заполняем пустые ячейки в "рваном" диапазоне скриптом.

Привет, друзья! В Excel есть функция, которой мы частенько пользовались - возможность выбрать только пустые ячейки в диапазоне и заполнить эти ячейки содержимым из крайней непустой ячейки каждого диапазона.

Мы перенесли эту функцию в Google Таблицы, написав два скрипта:
- первый заполняет выбранный диапазон
- второй заполняет лист целиком

Гифка: http://recordit.co/TVg2CUyxpF.gif
Таблица со скриптом здесь.

Файл > создать копию и можно будет открыть инструменты > редактор скриптов и посмотреть код с комментариями.

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

P.S. Спасибо @oshliaer за помощь.

Друзья, привет! Сегодня повторяем наш пост о спарклайнах.

Сегодня поговорим о спарклайнах (графиках в ячейках).

Допустим, у вас есть данные по продажам помесячно, и вы хотите посмотреть динамику по каждому ряду, но при этом не создавать большого количества отдельных диаграмм (это долго и неудобно, они будут занимать много пространства на листе и т.д.). Для этого и нужны спарклайны — мини-графики в ячейках, которые появились в версии Excel 2010.

К счастью, в Таблицах они тоже есть и, в отличие от Excel, реализованы в виде функции (в Excel их нужно вставлять через меню “Вставка”).
Функция так и называется: SPARKLINE.

На гиф посмотрим, как сделать график и гистограмму:
https://goo.gl/IrDT6m

Копируйте таблицу с данными и пробуйте создать спарклайны самостоятельно:
https://goo.gl/fJD0Mk

В документе по ссылке подробная информация по спарклайнам:
https://goo.gl/uZhzYf

И еще один пак с примерами можно найти здесь: https://tttttt.me/google_sheets/324

​​Выводим из массива данные, которые отвечают нашему условию: наличию определенного слова в текстовой строке (с учетом регистра и без), или же наличию одного из нескольких слов

Привет, друзья! Сегодня соединяем функции FILTER и REGEXMATCH и получаем простые и мощные решения (смотрите скриншот).

1) Выведем из массива улиц все улицы, содержащие в названии мотор:
=FILTER(A:A,REGEXMATCH(A:A, C2))

2) Сделаем условие независимым от регистра (будут найдены все варианты написания):
=FILTER(A:A,REGEXMATCH(A:A,"(?i)"&E2))

3) Ну и напоследок ищем сразу по ряду условий:
=FILTER(A:A,REGEXMATCH(A:A,"(?i)мотор|акаДЕМИК|адмиРАЛ|АДВОКАТ"))

​​​​Отправляем СМС из Google Таблицы.

Привет, друзья! В статье в Medium покажем и расскажем, как работать с сервисом по отправке СМС через HTTP API прямо из Google Таблицы.

Все разберем на примере https://smsc.ru, но принцип вы сможете использовать где угодно.

Каникулы - хорошее время, чтобы читать, изучать и осваивать новое.
Собрали для вас несколько неплохих книг и статей по таблицам.

Отличная статья по спарклайнам (на английском, но в основном там скриншоты и формулы) - с интересными примерами.
https://www.benlcollins.com/spreadsheets/sparklines-in-google-sheets/

Google Sheet Functions: A step-by-step guide
https://www.amazon.com/Google-Sheet-Functions-step-step-ebook/dp/B01NBHMZI9
Хорошее руководство по формулам в Таблицах. Все показано на примерах. Особенно их много по QUERY.
5 долларов (электронная книга).

Полезные функции Google Таблиц, которых нет в Excel
https://habr.com/post/331360/
Наша статья на Habr, рассказываем с примерами и скриншотами.

Интересные примеры использования FILTER
https://contributor.pw/post/google-sheets-formulas-filter/
Например, выбираем только женские имена из списка.

Всемогущая функция Query — подробное руководство:
https://netpeak.net/ru/blog/vsemogushchaya-funktsiya-query-podrobnoe-rukovodstvo/
Огромная статья про QUERY от Netpeak

Гайд по возможностям Google Таблиц
https://texterra.ru/blog/google-tablitsy-bolshoy-gayd-dlya-novichkov.html
Тут есть о дополнении для интеграции Google Analytics и Google Таблиц.

Going GAS: From VBA to Google Apps Script
https://www.amazon.com/Going-GAS-Google-Apps-Script-ebook/dp/B01BUK6H6O
Единственное пособие по скриптам Google, в том числе в Таблицах. Больше рассчитана на тех, кто переходит в Таблицы с Excel и умеет писать макросы на VBA, но и начинающим тоже пригодится.

The Ultimate Guide to Google Sheets
https://www.amazon.com/Ultimate-Guide-Google-Sheets-spreadsheet-ebook/dp/B01IMV1NH2
Бесплатная электрокнига. Основы работы с таблицами, с формами, немного про формулы, 50 полезных дополнений и другие полезности (допустим, пример создания CRM-системы в таблицах).

Бесплатный онлайн-курс по скриптам
https://courses.benlcollins.com/p/apps-script-blastoff

Бесплатный онлайн-курс по формулам
https://www.benlcollins.com/advanced-formulas-course/

Курс Рената по таблицам - очередной поток стартует 10-го января
https://skillfactory.ru/google-spreadsheets

Всех с наступающим Новым годом! До встречи после праздников.

ВПР в массиве вместо тысячи CУММЕСЛИМН

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

В статье - разобранный пример такой формулы.

​​Теперь в Google Таблицах можно поменять цвет заливки отдельной точки данных - как в Excel.

Все просто:
1. Щелчок правой кнопкой на точку данных, которую вы хотите визуально выделить;
2. Цвет элемента;
3. Выбираем цвет на палитре.

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

Новость - по ссылке: https://gsuiteupdates.googleblog.com/2019/01/assign-unique-colors-to-chart-elements.html

​​Друзья! Если вы работаете с онлайн-кассой и хотите нам немного помочь, то пожалуйста, заполните форму (там всего два вопроса).

​​​​Очищаем текст от HTML-тегов c помощью функции REGEXREPLACE.

Функция с регулярным выражением:
=REGEXREPLACE(A1;"<.+?>";"")

Раздел про жадную и ленивую квантификацию на wiki.

Друзья, идею поста подал Павел Мрыкин, его канал: @eWorker. Спасибо ему!

​​Незадокументированая особенность QUERY.

Друзья, привет! Мы нашли для вас что-то, чего нет в официальной документации. Кляуза skipping n позволяет оставить в диапазоне вывода каждую n-нную строку

Например,
skipping 3 - выводит каждую третью строку
skipping 5 - каждую пятую

Кляуза пишется в конце запроса - как limit (количество строк, которые выведет запрос) и offset (количество строк сверху, которые мы пропускаем и не выводим).

​​SUMIF для нескольких условий в формуле массива. Объединяем диапазоны с помощью амперсанда (&).

(Пример 1) Считаем сумму по нескольким условиям (одна формула будет работать сразу для ряда ячеек):
=ARRAYFORMULA(SUMIF(A2:A15&B2:B15;E2:E4&F2:F4;C2:C15))

(Пример 2) Сумма по нескольким условиям, заполняем табличку с двумя измерениями:
=ARRAYFORMULA(SUMIF(A2:A&B2:B;E8:E13&F7:H7;C2:C))

Напоминаем синтаксис SUMIF: (диапазон условия; условие; диапазон суммирования).

P. S. Если будете внедрять - напишите нам, как будет работать формула на ваших больших датасетах, нам интересно.

​​Пересчет QUERY по запросу. Лайфхак.

Уже несколько раз мы сталкивались с тем, что при загрузке данных из других документов с помощью QUERY и вложенной в нее функции IMPORTRANGE бывают задержки с загрузкой.
Особенно если исходный файл очень тяжелый - обновления в нем могут отразиться лишь через некоторое время.

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

=ЕСЛИ(ячейка с флажком; QUERY(IMPORTRANGE(1)) ; QUERY(IMPORTRANGE(2)))

Главное - чтобы первая и вторая формулы в ЕСЛИ отличались. Например, можете в первом IMPORTRANGE указать диапазон вида A:E, а во втором - A1:E. Результат будет одинаковый. Но при щелчке на флажок данные будут сразу пересчитываться.

​​Фильтруем две строки от пустых значений и объединяем в одну.

Поехали, разбираем формулу поэтапно:
1) Объединяем две строки в массив:
={A1:F1\A2:F2}

2) Разворачиваем массив в столбец функцией TRANSPOSE / ТРАНСП:
=TRANSPOSE({A1:F1\A2:F2})

3) Добавляем результат в функцию QUERY, фильтруем от пустых ячеек и разворачиваем обратно с помощью TRANSPOSE / ТРАНСП:
=TRANSPOSE(QUERY(TRANSPOSE({A1:F1\A2:F2});"where Col1 is not null"))

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

P.S. В региональных настройках США, Канады и некоторых других создание массива будет выглядеть так: ={A1:F1,A2:F2}