Google Таблицы

А как в Excel?

Ну если у вас Microsoft 365, то наслаждайтесь LAMBDA и вспомогательными функциями - все будет работать аналогично.
А если версия до 2021?

Всегда есть простор для формульно-массивного безумия!
Вот один из вариантов - предлагайте в комментариях свои, как бы вы решили такую задачу😉

=СЧЁТЗ(СМЕЩ(F3;СТРОКА(3:11)-3;0;1;100))

В более общем виде:

=СЧЁТЗ(СМЕЩ(первая ячейка диапазона;СТРОКА(строки диапазона)-корректировка ;0;1;число столбцов в диапазоне))

Что тут происходит?
Функция СМЕЩ / OFFSET выдает диапазон - шириной в сто столбцов (с запасом), высотой в одну строку, с началом в столбце F. Каждый раз смещаемся исходя из номера строки - на 0, 1, 2 и так далее строк вниз, получая тем самым ссылки на диапазоны, начинающиеся в F2, F3 и т.д. И с помощью СЧЁТЗ / COUNTA считаем количество значений.

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

Задача: посчитать количество значений (или что-то еще, не столь важно - мы рассмотрим на примере счета) в каждой строке одной формулой.

Здесь можно воспользоваться одной из новых функций, предназначенных для использования вместе с LAMBDA, а именно BYROW.
Она позволяет применить вычисление к каждой строке массива.
Синтаксис:

BYROW(массив данных ; LAMBDA(строка; вычисление (строка)))

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

Если нужно считать количество значений в каждой строке, применяем СЧЁТЗ / COUNTA:

=BYROW(D2:Z;LAMBDA(массив;СЧЁТЗ(массив)))

P.S. Если нужно обрабатывать столбцы - то, соответственно, пользуем BYCOL.

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

Смотрите также:
Накопительный итог с помощью функций SCAN и LAMBDA

onEdit скрипт, который предлагает вернуть старое значение

Друзья, привет!

Очередная задачка наших клиентов - есть цветные строки, редактирование которых производить нежелательно.

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

Сам код
function onEdit(e) {
var range = e.range
//проверяем фон ячейки, которая редактируется
if (range.getBackground() != '#ffffff') {
var old_value = e.oldValue;
var ui = SpreadsheetApp.getUi();
//выводим диалоговое окно
var response = ui.alert(
Сохранить изменения - OK\n\nВернуть старое значение [${old_value}] - CANCEL,
ui.ButtonSet.OK_CANCEL);

//обрабатываем результат выбора пользователем, CANCEL - возвращаем старое значение, ОК - ничего не делаем
response == ui.Button.CANCEL ? range.setValue(old_value) : '';
}
}

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

PS Недавно в нашем чате был вопрос о том, как переносить строки по чекбоксу с помощью onEdit, вот пост и про это

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

Друзья, хотим напомнить про символы подстановки (wildcard), тем более что в новых функциях они работают несколько иначе.

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

Символы подстановки работают по умолчанию в следующих функциях:
- ВПР / VLOOKUP и ПОИСКПОЗ / MATCH
- СУММЕСЛИ (МН) / SUMIF(S), СЧЁТЕСЛИ(МН) / COUNTIF(S), СРЗНАЧЕСЛИ(МН) / AVERAGEIF(S)
- COUNTUNIQUEIFS
- Функциях баз данных. БДСУММ / DSUM, ДСРЗНАЧ / DAVERAGE, БСЧЁТ / DCOUNT, БСЧЁТА / DCOUNTA и других
- ПОИСК / SEARCH

В новых функциях ПОИСКПОЗX / XMATCH и ПРОСМОТРX / XLOOKUP символы подстановки по умолчанию не работают! Но у них есть аргумент "режим_сопоставления" (match_mode), в котором есть следующие варианты:
0 - точный поиск (по умолчанию). Символы подстановки не работают.
1 ближайшее большее значение (или точное совпадение)
-1 ближайшее меньшее значение (или точное совпадение)
2 - точный поиск с символами подстановки.

То есть чтобы все работало как в ВПР с последним аргументом, равным нулю, нужен режим 2. Иначе магия звездочек и знаков вопросов не будет работать.

P.S. В Excel символы подстановки работают еще и в окне "Найти и заменить" и в условиях расширенного фильтра.

Всякое по теме:
Примеры условий с символами подстановки в функциях СУММЕСЛИ / SUMIF, СЧЁТЕСЛИ / COUNTIF, СРЗНАЧЕСЛИ / AVERAGEIF
СУММЕСЛИМН / SUMIFS с флажком (включаем и выключаем условие)
Функции баз данных
ВПР со звездочкой

Накопительный итог в новых реалиях

Господа, после того, как в таблицах появились функции LAMBDA, SCAN, MAP, BYROW, BYCOL, REDUCE, MAKEARRAY считать накопительный итог по каждой строке стало гораздо проще.

Рассказываем. Функция SCAN умеет возвращать промежуточные значения (читайте - по каждой строке), путем применения вложенной функции LAMBDA.

Синтаксис такой:
=SCAN(начальное значение [аккумулятор]; диапазон; LAMBDA)

Формула для накопительного итога по каждой строке:
=SCAN(0; A1:A5; LAMBDA(acc;x; acc+x))

Значит,
1) задаём аккумулятор и диапазон значений 0; A1:A5
2) обращаемся к ним в лямбде, как к acc и x, добавляя каждое значение, построчно, к аккумулятору, acc + x
3) SCAN выводит аккумулятор, он же - накопительный итог, по каждой строке в Таблицу

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

Перемещение после ввода данных в ячейку

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

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

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

Ну а в Excel еще есть Ctrl+Enter - после ввода остаемся в той же ячейке. А если выделено несколько ячеек, то ввод будет осуществляться в каждую из них! Даже если это несмежные диапазоны/ячейки.

И в Excel можно изменить то направление, куда мы перемещаемся после нажатия Enter - в параметрах (в разделе "Дополнительно" / Advanced).

Чего нам не хватало в Таблицах

Ну, например, функций ПРОСМОТРX / XLOOKUP, ПОИСКПОЗX / XMATCH и LAMBDA.
И вот они здесь (точнее, на подходе - ждем, когда все они будут доступны у всех).

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

Про функцию LAMBDA мы писали здесь (она и в Excel появилась недавно). Новые пользовательские функции (Named functions) - по сути и есть LAMBDA, с возможностью задать имя и делать это все в специальном интерфейсе (это будет боковая панель, вызываемая в меню Data / Данные - пока можно посмотреть на гифку в новости от Google).
Про ПРОСМОТРX здесь.
ПОИСКПОЗX / XMATCH - это тот же MATCH, но в новой функции по умолчанию ведется точный поиск, а еще можно искать снизу вверх, а не сверху вниз.

В ближайшее время расскажем подробнее про новинки.

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

Чего нам не хватало в Excel

Ну, например, функции IMAGE.
В русскоязычном Excel она будет называться ИЗОБРАЖЕНИЕ.

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

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

Попробовали закинуть Excel с этой функцией в Таблицы - все работает.

В обратную сторону - в формуле будет @IMAGE (которая будет вызывать ошибку ИМЯ / NAME - дескать, не знает Excel такого), то есть если у вас Excel на русском, то придется вызвать окно "Найти и заменить" и поменять на русскоязычное название. В Excel на английском - убрать собачку из формул. Разумеется, все это при наличии актуальных обновлений и Microsoft 365.

TOO MUCH IMPORTRANGE

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

Мы подготовили скрипт, который решит эту проблему (скрипт и короткое описание: pastebin.com/EcRZwhg0).

А зачем это может понадобиться?
Расскажу про проблему наших клиентов – одна Таблица была использована внутри IMPORTRANGE слишком много раз (>500) и после очередного раза всё перестало работать, данные из Таблицы перестали передаваться, пользователи видели ошибку как на картинке, а еще в эту несчастную Таблицу стало нельзя добавлять новых пользователей с правами чтение или редактирование.

Наше решение:
1) Мы выяснили, какие Таблицы у нас лежат внутри рабочей папки нашим скриптом Drive Columbus;

2) Прошлись по этому списку Таблиц скриптом и узнали, какие Таблицы указаны внутри IMPORTRANGE в этих Таблицах;

3) Сделали копию Таблицы-донора и написали скрипт, который вставил формулу IMPORTRANGE с этой Таблицей в ряд Таблиц по списку;

4) Использовали скрипт от Михаила Смирнова, который прошелся по списку Таблиц с обновленной формулой IMPORTRANGE и раскрыл доступ к Таблицам автоматически, кликать на "расшарить доступ" не пришлось;

В итоге достаточно неприятная проблема была решена и сейчас сотни Таблиц по-прежнему работают.

Нарастающий итог: закрепляем только первую ячейку в диапазоне

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

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

2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.
=СУММ($B$2:B2)

3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке.

Альтернативное решение, одна формула массива:
=ARRAYFORMULA(SUMIF(row(B1:B16),"<="&row(B1:B16),B1:B16))

Про такую формулу мы писали вот здесь

Кликаем – строки выделяются

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

Скрипт из ГИФки проверяет, какой диапазон был сохранён в ScriptProperties в предыдущий раз, убирает с этих строк заливку, далее закрашивает строки, которые были выделены сейчас и сохраняет этот диапазон в ScriptProperties (чтобы при следующем запуске убрать заливку с него).

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

🤓 Если найдёте триггеру применение в своем Табличном хозяйстве – напишите нам в комментариях.

const pr_key = 'lr';
const colour = '#800080'; //en.wikipedia.org/wiki/Web_colors

function onSelectionChange(e) {
const range = e.range;
const sh = e.source.getActiveSheet();
const range_a1 = ${range.rowStart}:${range.rowEnd};
const last_range_a1 = ScriptProperties.getProperty('lr');
if (last_range_a1) {
sh.getRange(last_range_a1)
.setBackground(null);
};
sh.getRange(range_a1)
.setBackground(colour);
ScriptProperties.setProperty('lr', range_a1);
};

Оглавление нашего канала: тыц
Наш чат: тыц-тыц

Видеоурок для новичков: текстовые функции

Друзья, привет! Делимся с вами уроком из курса "Драйв. Гугл Драйв" - про работу с текстовыми значениями: окно "Найти и заменить", функция TRIM, изменение регистра, разделение текста (командой "Разделить текст на столбцы" и функцией SPLIT), объединение текстовых значений, извлечение фрагментов из текста.

https://www.youtube.com/watch?v=Fdkdm3h7Ylc

В курсе таких видео 90 - про диск, таблицы (конечно, ко всем урокам прилагаются исходные и заполненные таблицы), документы, календарь, почту, презентации (от ведущего дизайнера МИФа Ольги Королёвой).

Слушатели уже поставили 249 оценок урокам курса. 244 из них (98%) - пятерки 5️⃣

Коллеги из МИФа сделали промокод на скидку для вас - 40%DGD. Действует до 29 августа (12-00).

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

Несколько 🔥 клавиш Google Диска

Создать новые документы/папки в текущей папке
Документ: Shift + t
Презентация: Shift + p
Таблица: Shift + s
Форма: Shift + o
Папка: Shift + f

Переместить документ: z
Переименовать: n
В Избранное: s

Открыть выделенную папку/документ в новой вкладке браузера: Ctrl + Enter

Открыть настройки доступа: . (точка)

Поменять режим просмотра (сетка/список): v

Скопировать ссылку на выделенную папку/документ: Ctrl+C
Скопировать название: Ctrl+Shift+C

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

Оглавление нашего канала: тыц
Наш чат: тыц-тыц

Достаём ссылки на фотографии товара из карточки WB

Привет, друзья! Чтобы получить ссылки на фотографии товара – берём ссылку на карточку WB

wildberries.ru/catalog/86123932/detail.aspx?targetUrl=XS

и превращаем её в

img1.wbstatic.net/big/new/86120000/86123932-1.jpg (-2.jpg, -3.jpg, ...)

Разберём по пунктам:
1) Достаём из ссылки на карточку – артикул товара, отбросив последние четыре цифры:
wildberries.ru/catalog/86123932/detail.aspx?targetUrl=XS
=REGEXEXTRACT($A1;"(\d+)\d{4}")
//8612

2) Добавим слева "https://img1.wbstatic.net/big/new/" и добавим справа "0000/", а также артикул полностью, а также {"-1.jpg" \ "-2.jpg" \ "-3.jpg"}, чтобы получить три ссылки.

Итоговая формула:
=ARRAYFORMULA(
"https://img1.wbstatic.net/big/new/"
& REGEXEXTRACT($A1;"(\d+)\d{4}") & "0000/"
& REGEXEXTRACT($A1;"\d+")
& {"-1.jpg" \ "-2.jpg" \ "-3.jpg"})

Формула возращает:
https://img1.wbstatic.net/big/new/86120000/86123932-1.jpg
https://img1.wbstatic.net/big/new/86120000/86123932-2.jpg
https://img1.wbstatic.net/big/new/86120000/86123932-3.jpg

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

📌 А еще мы сделали своё дополнение для продавцов WB

🚜 Делитесь своими трюками в WB в комментариях :)

Наш чат, где могут помочь с Таблицами: @google_spreadsheets_chat

Импорт писем из Яндекс Почты в Google Таблицу?

Недавно к нам пришел клиент с таким запросом. К сожалению, в лоб это реализовать нельзя – у ЯП нет http-API, к которому можно обратиться, используя Google Apps Script.

Но задачу можно решить, простой способ:
1) Создаём правило для пересылки писем из ящика ЯП на наш ящик Gmail. К сожалению, правило будет работать только для входящих сообщений.

2) Далее загружаем письма из Gmail в Таблицу с помощью скрипта t.me/google_sheets/802

И еще способ (от Александра Иванова, он же @oshliaer). Подключаем ящик Яндекс Почты в Gmail через POP3:
1) Активируем POP3 в Яндекс Почте (видео)
2) Производим настройки в веб-клиенте Gmail (видео)
3) Письма приходят в наш ящик в Gmail. Загружаем их в Таблицу с помощью скрипта: t.me/google_sheets/802

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

PS А если вы решите посмотреть на номера строк и просто напишите формулу =ROW(A1:A6) в ячейку, то увидите значение только для первой ячейки диапазона, A1 (смотрите ячейку C1 на скриншоте).

Чтобы увидеть всё – нужна формула массива:
=ARRAYFORMULA(row(A1:A6))

Оглавление нашего канала: тыц
Наш чат: тыц-тыц

Сортировка не по содержанию, а по номеру строки

Друзья, чтобы "перевернуть" данные в A1:B6 (чтобы последняя строка данных – стала первой, предпоследняя – второй, ...) мы можем воспользоваться формулой
=SORT(A1:B6;ROW(A1:A6);FALSE)

Разберём её по частям:
=SORT(A1:B6;ROW(A1:A6);FALSE) – диапазон данных для сортировки.

=SORT(A1:B6;ROW(A1:A6);FALSE) – хитрость, мы создаём виртуальный столбец с номерами строк исходного диапазона данных {1;2;3;4;5;6}. Виртуальность столбца означает, что он никуда не выводится и используется только внутри формулы.

=SORT(A1:B6;ROW(A1:A6);FALSE) – сортировка по возрастанию FALSE/ЛОЖЬ, то есть, сортировка по виртуальному столбцу пойдет по по убыванию.

Чат нашего канала: @google_spreadsheets_chat

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

2) если задаёте вопрос - приложите к нему Таблицу с примером, Таблица должна быть открыта на редактирование;

3) перед тем как задать вопрос – поищите его в оглавлении нашего канала;

😎

Извлекаем номера телефонов из массива

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

Сегодня у нас простой, и, надеемся полезный для вас пост.

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

Уберем лишнее и поместим каждый телефонный номер в отдельную ячейку.

Решение:
1) С помощью REGEXREPLACE заменим все не цифры и не запятые на пустоту:
=ARRAYFORMULA(REGEXREPLACE(A2:A;"[^\d,]";""))

2) Добавляем SPLIT и разделим строки по "," (для ячеек, где перечислены несколько телефонов через запятую):
=ARRAYFORMULA(SPLIT(REGEXREPLACE(A2:A;"[^\d,]";"");","))

3) Поместим весь результат в один столбец, добавив FLATTEN
=ARRAYFORMULA(FLATTEN(SPLIT(REGEXREPLACE(A2:A15;"[^\d,]";"");",")))

4) И последний штрих– отфильтруем от пустых ячеек, добавив QUERY с фильтрацией по первому столбцу

Итог:
=QUERY(ARRAYFORMULA(flatten(SPLIT(REGEXREPLACE(A2:A15;"[^\d,]";"");",")));"where Col1 is not null";0)