ВПР

Друзья, ВПР - могущественная функция, чего только с помощью неё нельзя сделать.

На нашем канале мы написали много постов про ВПР, давайте вспомним их:

— Последний аргумент ВПР или ВПР с интервальным просмотром = 1: t.me/google_sheets/13

— ВПР с ПОИСКПОЗ в массиве: t.me/google_sheets/78

— ВПР по нескольким диапазонам: t.me/google_sheets/145

— Видео про функцию ВПР в Google Таблицах: t.me/google_sheets/182

— ВПР (VLOOKUP) по нескольким условиям: t.me/google_sheets/265

— ВПР в массиве вместо тысячи CУММЕСЛИМН: t.me/google_sheets/355

— ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце): t.me/google_sheets/397

— Храним данные в Properties и обращаемся к ним
Аналог функции ВПР: t.me/google_sheets/483

— ВПР, достаём последнее значение по ключу: t.me/google_sheets/558

— Массивный ВПР: t.me/google_sheets/577

— ВПР / VLOOKUP со звездочкой: t.me/google_sheets/640

— Условное форматирование + ВПР: выделяем цветом имена сотрудников из определенного отдела: t.me/google_sheets/925

— ВПР-им с разных листов: t.me/google_sheets/945

— Объединяем ВПР и СУММПРОИЗВ (SUMPRODUCT): t.me/google_sheets/253

🔥 Посты про другие функции и скрипты в оглавлении нашего канала: перейти

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

Там для обновления сводной (а точнее, ее кэша - при создании сводной данные загружаются в память и крутится этот "мгновенный снимок", а не то, что сейчас в ячейках) нужно:
- Щелкнуть правой кнопкой на сводной и нажать "Обновить" (Refresh)
- Нажать Alt + F5 для обновления активной сводной (одна из ячеек которой активна в моменте)
- Нажать на ленте на вкладке "Данные" (Data) "Обновить" (Refresh) для обновления активной сводной

Для обновления всех сводных в книге можно:
- Нажать Ctrl+Alt+F5
- Нажать на ленте на вкладке "Данные" (Data) "Обновить все" (Refresh All).

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

На уровне рабочей книги может подойти событие SheetActivate - активизация любого листа (можно создать и событие для конкретного листа, если на нем одном нужно обновлять сводные).
Alt+F11, далее выбираем рабочую книгу и добавляем код для этого события (его можно выбрать в выпадающем списке).
Пробегаемся по коллекции сводных таблиц на активированном листе и обновляем кэш у каждой:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Pivo As PivotTable
For Each Pivo In Sh.PivotTables
    Pivo.PivotCache.Refresh
Next Pivo
End Sub

при открытии таблицы вставим на каждом листе его название
(триггер onOpen)

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

Сегодня простой летний скрипт – вставим при загрузке Таблицы в ячейку A1 каждого листа название этого листа:

function pasteNames() {
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();

sheets.forEach(sheet => {
const sheetName = sheet.getSheetName();
sheet.getRange("A1").setValue(sheetName);
})
};

Чтобы скрипт отрабатывал при каждом открытии Таблицы пользователем – добавьте его в функцию onOpen:

function onOpen(){
pasteNames();

SpreadsheetApp.getUi()
.createMenu("тут может быть меню со скриптами")
.addItem(скрипт, '...')
.addToUi();

};

Если нам нужно игнорировать некоторые листы и ничего не вставлять в них:

function pasteNames() {
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();
const ignoreNames = ['Sheet1', 'Sheet2']

sheets.forEach(sheet => {
const sheetName = sheet.getSheetName();
if (!ignoreNames.includes(sheetName)) {
sheet.getRange("A1").setValue(sheetName);
}
})
};

🔥 оглавление нашего канала: перейти

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

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

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

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

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

Решение:
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)

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

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

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

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

😎

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

Друзья, чтобы "перевернуть" данные в 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/ЛОЖЬ, то есть, сортировка по виртуальному столбцу пойдет по по убыванию.

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

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

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

Импорт писем из Яндекс Почты в 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

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

Достаём ссылки на фотографии товара из карточки 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 Диска

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

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

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

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

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

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

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

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

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

Друзья, привет! Делимся с вами уроком из курса "Драйв. Гугл Драйв" - про работу с текстовыми значениями: окно "Найти и заменить", функция 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/

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

Друзья, 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);
};

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

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

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

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

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

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

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

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

TOO MUCH IMPORTRANGE

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Господа, после того, как в таблицах появились функции 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 выводит аккумулятор, он же - накопительный итог, по каждой строке в Таблицу

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