Можно:
1. создаем вспомогательную Таблицу [название картинки], [ссылка на нее]
2. создаем выпадающий список со всеми названиями картинок
3. создаем около списка формулу вида =image(VLOOKUP(A2;C2:D4;2;0))
где А2 — вып. список, а C2:D4 — диапазон таблицы из пункта 1
4. Теперь при выборе картинки в выпадающем списке она будет отображаться в ячейке, в которую мы ввели формулу.
Здравствуйте! Вот здесь написал про простой скрипт, который позволяет ускорить работу с ресурсоемкими формулами: https://habrahabr.ru/post/331360/#comment_10277466
Я довольно долго работал с Excel (аналитиком в разных московских компаниях), но после того, как столкнулся с Google Табличками, стал ярым фанатом этого решения и возвращаюсь к Excel только если меня просят чем-то помочь.
В прошлом году нам удалось при помощи Google табличек и скриптов собрать систему отчетности для 60-ти человек, со всякими сложными сводными таблицами и графиками, триггерным выполнением скриптов в облаке (даже если файлы закрыты). Тоже бесплатно, оперативно и решениями из коробки. Да и большое удовольствие работать с интерфейсом не обремененным лишними кнопками :)
Кстати, по поводу скрипта — у меня есть еще одна его версия, где скрипт берет ряд формул из одной строки и циклом вставляет эти формулы построчно (каждая строка — посчитанные с помощью importrange показатели из разных док-тов) и заменяет на значения. Такое может помочь если у нас много формул и мы не хотим, чтобы они постоянно обновлялись.
Пара слов про то, как ускорить работу таблицы с помощью скрипта.
Нам частенько приходится с помощью IMPORTRANGE собирать данные из многих таблиц (скажем, из 50-ти) и если достаем из каждой таблицы еще и большие массивы данных, то такая формула может начать тормозить. Очень.
Другой пример, у нас таблица на 20к строк и мы на дашборде выводим различные показатели с помощью сложных формул, они обращаются к этой таблице и что-то там высчитывают. Такое тоже может очень быстро начать тормозить, особенно, если в рабочей книге много листов, много данных и много формул.
Как можно это побороть? Пишем скрипт, который по нажатию кнопки (либо по расписание несколько раз в день) вставляет в выбранную ячейку формулу IMPORTRANGE из всех нужных таблиц, данные загружаются и он сразу же заменяет их на значения, убирая формулу. Нашему документу не приходится обновлять постоянно ресурсоемкую формулу, он облегчено вздыхает и начинает работать быстрее.
Под спойлером пример такого скрипта.
Заголовок спойлера
function formulasbor()
{
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sh0 = ss.getSheetByName("sh0")
//f - наша глобальная и тормозящая формула
var f = "=sum(a1+a2)"
sh0.getRange("a2:r").clearContent()
sh0.getRange("a2").setFormula(f)
Utilities.sleep(15000)
var last0 = (sh0.getLastRow() + 1)
var range = sh0.getRange("A2:R"+last0)
range.setValues(range.getValues())
}
По поводу сбора с Pivot table — экселевской формулы (getpivotdata, или как-то так) в Google Табличках нет, но можно обойтись и без нее.
Набросал пример, допустим, пивот в диапазоне a1:c7. Используем формулу СМЕЩ (она позволяет вывести диапазон или ячейку отступив от заданного диапазона введенное кол-во строк и столбцов).
Например, мы хотим вывести ячейку на пересечение «центр А» и «доход 1», с помощью ПОИСКПОЗ находим в нашем пивоте строку-столбец (условия поиска в E1 и F1) и в итоге СМЕЩ смещается на правильное кол-во полей и мы значение той ячейки, которую искали.
Приветствую! У меня был похожий случай, было около 30 документов и из них нужно было взять по тысяче строк. Владелец таблички все собирал с помощью importrange и документ нечеловечески тормозил.
Что можно сделать:
1) скрипт в этом гугл-доке, который по временному триггеру либо по кнопке будет вставлять все importrange, далее формулы просчитываются и скрипт меняет их на значения. 2) другой скрипт, он будет открывать каждый файл по списку и брать оттуда нужный нам диапазон и копировать его в сводный файл.
Напишу чуть попозже про это, как раз думал следующий пост посвятить тому, что можно сделать для оптимизации табличек и ускорения их работы.
Рад был рассказать что-то полезное! Да, раз есть интерес, то напишу в следующий про скрипт, с помощью которого можно ускорить работу табличек, если в них слишком уж много тормозящих формул.
Вообще я веду канал в телеграмме про формулы в гугл табличках и разные кейсы с ними, не знаю, можно ли по правилам ресурса давать ссылки?
Можно:
1. создаем вспомогательную Таблицу [название картинки], [ссылка на нее]
2. создаем выпадающий список со всеми названиями картинок
3. создаем около списка формулу вида =image(VLOOKUP(A2;C2:D4;2;0))
где А2 — вып. список, а C2:D4 — диапазон таблицы из пункта 1
4. Теперь при выборе картинки в выпадающем списке она будет отображаться в ячейке, в которую мы ввели формулу.
Таблица с примером: docs.google.com/spreadsheets/d/1yiSHolO9iUFZRnE2ND4Cp4lirGs2AfSKH1zXfOOmMpA/edit#gid=0
Не могу понять, как скриптом вставить сразу несколько формул в двумерный массив (разом, по-формуле-то можно)
Вот здесь пример:
В прошлом году нам удалось при помощи Google табличек и скриптов собрать систему отчетности для 60-ти человек, со всякими сложными сводными таблицами и графиками, триггерным выполнением скриптов в облаке (даже если файлы закрыты). Тоже бесплатно, оперативно и решениями из коробки. Да и большое удовольствие работать с интерфейсом не обремененным лишними кнопками :)
Кстати, по поводу скрипта — у меня есть еще одна его версия, где скрипт берет ряд формул из одной строки и циклом вставляет эти формулы построчно (каждая строка — посчитанные с помощью importrange показатели из разных док-тов) и заменяет на значения. Такое может помочь если у нас много формул и мы не хотим, чтобы они постоянно обновлялись.
Нам частенько приходится с помощью IMPORTRANGE собирать данные из многих таблиц (скажем, из 50-ти) и если достаем из каждой таблицы еще и большие массивы данных, то такая формула может начать тормозить. Очень.
Другой пример, у нас таблица на 20к строк и мы на дашборде выводим различные показатели с помощью сложных формул, они обращаются к этой таблице и что-то там высчитывают. Такое тоже может очень быстро начать тормозить, особенно, если в рабочей книге много листов, много данных и много формул.
Как можно это побороть? Пишем скрипт, который по нажатию кнопки (либо по расписание несколько раз в день) вставляет в выбранную ячейку формулу IMPORTRANGE из всех нужных таблиц, данные загружаются и он сразу же заменяет их на значения, убирая формулу. Нашему документу не приходится обновлять постоянно ресурсоемкую формулу, он облегчено вздыхает и начинает работать быстрее.
Под спойлером пример такого скрипта.
Набросал пример, допустим, пивот в диапазоне a1:c7. Используем формулу СМЕЩ (она позволяет вывести диапазон или ячейку отступив от заданного диапазона введенное кол-во строк и столбцов).
Например, мы хотим вывести ячейку на пересечение «центр А» и «доход 1», с помощью ПОИСКПОЗ находим в нашем пивоте строку-столбец (условия поиска в E1 и F1) и в итоге СМЕЩ смещается на правильное кол-во полей и мы значение той ячейки, которую искали.
Что можно сделать:
1) скрипт в этом гугл-доке, который по временному триггеру либо по кнопке будет вставлять все importrange, далее формулы просчитываются и скрипт меняет их на значения.
2) другой скрипт, он будет открывать каждый файл по списку и брать оттуда нужный нам диапазон и копировать его в сводный файл.
Напишу чуть попозже про это, как раз думал следующий пост посвятить тому, что можно сделать для оптимизации табличек и ускорения их работы.
Вообще я веду канал в телеграмме про формулы в гугл табличках и разные кейсы с ними, не знаю, можно ли по правилам ресурса давать ссылки?