SensaiKudisai

SensaiKudisai

Telegram: @CTables_support
Пикабушник
поставил 61 плюс и 35 минусов
проголосовал за 0 редактирований
2818 рейтинг 775 подписчиков 172 комментария 28 постов 25 в горячем
55

Как переносить большие данные между таблицами // Что делать если importrange не работает?

Котятки, привет)

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

Для теста нам понадобятся:

- тест импорта источник

- тест импорта назначение (скрипт пишем здесь)

Для начала объявляем переменные для документов:

Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост

В документе источника решаем, что будем импортить, как правило это конкретные столбцы. Допустим столбцы “запись 4” и “запись 7”

Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост

В скрипте эта запись выглядит так:

Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост

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

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

И указываем диапазоны :

Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост

Dest.getRange(1,1,Lr), где 1 номер начальной строки, lr номер последней строки и 1 посередине - номер столбца ,куда будет записываться данные

Все, готово:)

Готовый скрипт выглядит вот так, согласитесь крайне несложно)) :

Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост

Скрипт готов, теперь поговорим, как же его запустить. Есть два варианта:

1. Добавить триггер самообновления

Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост
Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост

Настроить периодичность обновления скрипта

Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост

Например: обновление раз в сутки или каждые 4 часа

Или же можно воспользоваться вторым способом

2) Вставить рисунок, и обозначить скрипт

Вставляем рисунок и нажимаем на 3 точки

Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост

Дальше выбираем “назначить скрипт”

Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост

Пишем название скрипта до скобок (в скрипте это название функции):

Как переносить большие данные между таблицами // Что делать если importrange не работает? Google Таблицы, Google, Формула, Длиннопост

Теперь при нажатии на эту картинку будет исполняться скрипт :)

Готово!) И не пугайтесь, если скрипт отрабатывает, как вам кажется слишком долго, учитывая объем инфы - полторы минуты эт норма.


Р.S. Готовый скрипт:

function import_data() {

const CURRENT = SpreadsheetApp.getActiveSpreadsheet();

const Dest = CURRENT.getSheetByName("Dest");

const SOURCE = SpreadsheetApp.openById("1JjqIJln5IKjNaLBMRVQAH72x8doTZLykf_3JeB1i0wQ");

const Source = SOURCE.getSheetByName("Source");

const dataArr = Source.getRange("A:A").getValues();

const zapis4Arr = Source.getRange("E:E").getValues();

const zapis7Arr = Source.getRange("H:H").getValues();

const lr = Source.getMaxRows();

Dest.clearContents();

Dest.getRange(1,1,lr).setValues(dataArr);

Dest.getRange(1,2,lr).setValues(zapis4Arr);

Dest.getRange(1,3,lr).setValues(zapis7Arr);

}

Показать полностью 10
15

Цветные таблицы. Кошаки. Старт нового проекта :)

Доброго времени суток, котяточки. У меня для вас классные новости, но обо всем по порядку.

Из - за моей великой любви к табличкам, год назад я начал вести этот блог. За это время несколько моих читателей переросли в моих клиентов, а откликов на посты и запросов на консультацию становилось все больше и было принято решение о старте собственного проекта.  С гордостью расскажу вам о своём детище, под названием Colorful Tables, да - да, цветные таблички). Старт своего проекта стал глотком свежего воздуха и раньше я не испытывал такого кайфа от работы. И виновники моего счастья - свобода самостоятельно принимать решения и построить команду в соответствии с той горой нонфикшн литературы, которой я вдохновлялся последние годы и собственно отличная команда, которую удалось собрать. Для себя я на старте решил, что мне не оч интересны большинство HR-ных метод и подбирал команду по двум критериям - чтобы человек хотел учиться и чтобы нам было круто общаться. Таким образом в команду попали 3 девочки-джуна (они просто невероятные *___*), которых буду учить таблицам с самого нуля и с первых дней - все решения по дальнейшей судьбе проекта мы принимаем совместно.

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

И в завершении, дорогие читатели, хочу вас поблагодарить, что интересуетесь моим блогом, читаете и ждёте новых постов. И раз уж Пикабу стал для меня одним из основных каналов продаж - принимаю заявки на новые заказы в телеграм :)

ЗЫ Прототип нашего лого на фото :)

Цветные таблицы. Кошаки. Старт нового проекта :) Google Таблицы, Обучение
Показать полностью 1
59

GSQ1 Задачки в гугл таблицах // Проба пера

Теплого времени суток, дорогие чатлане!
Давненько не писал посты, был завал на проектах. Ближе к НГ чуть подразгрузился и скоро будет новый пост про гугл таблицы.
А пока решил попробовать новый формат - задачки.
Суть такова - мне по работе нужно было найти то или иное хитрое решение над которым пришлось повозиться. Задачки решаются без скриптов (иначе это конкурс программистов, а не задачки в гугл таблицах). Мне эти кейсы показались интересными и будет так же интересно, сможет ли кто в комментариях найти решение, мб более изящное чем у меня. На решение будет даваться неделя (если этим вообще кто-то будет заниматься), потом я буду выкладывать то решение, которое получилось у меня.
Итак, к мясу:
https://docs.google.com/spreadsheets/d/1uwLWwznwqYZ9Vh57eiR7...

GSQ1 Задачки в гугл таблицах // Проба пера Microsoft Excel, Головоломка, Задача, Выживание

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

Показать полностью
114

Сводные таблицы часть 1 (GS20)

Привет, дорогие чатлане!

Сегодня поговорим о сводных таблицах. Можно сказать что это вторая веха в освоении таблиц после ВПРа и про них чаще всего спрашивают на собеседованиях.

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

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

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

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

Второе (и это уже обязательно) - в таблице не должно быть объединенных ячеек.

Покажу песочницу, которую буду использовать для демонстрации:

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Н.Б. Для эксельщиков: ГТ плохо работает с датами в сводах, поэтому месяц пришлось вынести в отдельный столбец, для экселя - это не обязательно.

Итак, как создать свод?

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Выделяем нужный диапазон, выбираем пункт меню Данные // Сводная таблица (в экселе аналогично в меню Вставить // Insert).

Конструкторы сводов отличаются для ГТ и экселя и дальше буду рассказывать только для ГТ.


Для наглядности буду собирать свод на этом же листе:

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Этап следующий:

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Красная зона - сама сводная таблица, синяя - конструктор свода.

Сделаю пример и расскажу о том что это и зачем:

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

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

В моем примере - я взял значения из столбца "Месяц" и сделал их строками новой таблицы. Аналогично - менеджеров я сделал столбцами, а на пересечении - сумма значений столбца Сумма.

Можно создать еще большую детализацию:

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Немного детальнее про конструктор:

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

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

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Если поставить последнюю галочку - в этой ячейке будет повторно записано значение "6".


И про значения на пересечениях:

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Здесь представлены все варианты того, что можно сделать со значениями на пересечениях (просуммировать, посчитать количество, посчитать среднее, максимальное, отклонение, произведение). Отдельно оставлю ссылку на документацию для вариантов VAR и VARP: https://support.microsoft.com/en-us/office/var-varp-functions-e08f5f59-22f3-43d1-863f-a195df09904f#:~:text=Remarks,a variance cannot be calculated.


Если сделать двойной клик по любой ячейке свода - будет создан лист, который покажет все строки оригинальной таблицы, которые были записаны в эту ячейку:

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Последнее - фильтры. Они позволяют отсечь те или иные значения из сводов и работает как обычный фильтр в таблицах:

Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост
Сводные таблицы часть 1 (GS20) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост
Показать полностью 12
44

Суммеслимн, счётеслимн в гугл таблицах (GS19)

Привет, дорогие чатлане!

Продолжаем цикл мануалов по гугл таблицам.

Сегодня поговорим о функциях ****еслимн. Для чего нужны эти функции?

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

Суммеслимн, счётеслимн в гугл таблицах (GS19) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Первый аргумент - столбец который суммируем, второй - столбец для первого условия, третий - первое условие. Все следующие аргументы идут парами.

Суммеслимн, счётеслимн в гугл таблицах (GS19) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Для счетеслимн - только столбцы с условиями и сами условия.

Как добавить более сложные условия в суммеслимн:

Суммеслимн, счётеслимн в гугл таблицах (GS19) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Во второй аргумент мы добавляем формулу массива, которая возвращает нам новый массив, состоящий из набора {TRUE, TRUE, FALSE, TRUE...} и сравниваем его со значением ИСТИНА/TRUE.


Как использовать сравнения в суммеслимн:

Суммеслимн, счётеслимн в гугл таблицах (GS19) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

Если мы хотим сравнить что-то с датой - дату нужно брать в кавычки:

Суммеслимн, счётеслимн в гугл таблицах (GS19) Google Таблицы, Microsoft Excel, Инструкция, Длиннопост

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

Показать полностью 5
44

Filter, sort, unique (GS18)

Привет, дорогие чатлане!

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

Сначала про синтаксис. Он очень простой.

Фильтр = сначала диапазон данных который фильтруем + логические условия (>,<,= или более сложные). При этом условия могут быть для столбца или строки, которые не входят в фильтруемый диапазон, главное чтобы размеры совпадали.

Сорт = сначала сортируемый диапазон, потом номер столбца внутри этого диапазона и параметр 0/1 (возрастание/убывание), либо можно как с фильтром - указать конкретный столбец и за пределами сорта.

Юник = тут все просто - массив указываем - он удаляет дубли.

Пример:

Filter, sort, unique (GS18) Google Таблицы, Microsoft Excel, Длиннопост

Теперь перейдем к более интересным кейсам (буду частично дублировать прошлые посты, но хочется собрать весь контент по этим функциям в одном месте).

Кейс 1 - Вывести только элементы, которых нет в справочном массиве (про это был отдельный пост: Фильтры и ВПРы в ГТ (GS2)):

Filter, sort, unique (GS18) Google Таблицы, Microsoft Excel, Длиннопост

Кейс 2 - обратная ситуация, вывести только те элементы, которые есть в справочнике:

Filter, sort, unique (GS18) Google Таблицы, Microsoft Excel, Длиннопост

Кейс 3 - двойная фильтрация (часто используется когда нужно сделать много фильтров и нужно протянуть формулу или когда нужно дать пользователю варианты фильтрации без погружения в формулу):

Filter, sort, unique (GS18) Google Таблицы, Microsoft Excel, Длиннопост

Мы выбираем столбец для условия фильтром внутри фильтра.

Кейс 4 - Найти все элементы, содержащие внутри себя регулярное выражение (кусок текста):

Filter, sort, unique (GS18) Google Таблицы, Microsoft Excel, Длиннопост

Обратите внимание - эта штука регистрочувствительная, поэтому я отсек первую букву.

Кейс 5 - выделить из мусорной (с лишними данными) таблицы ID и вывести список уникальных ID, содержащих внутри себя определенные символы (часто используется для выгрузки счетов или работы с индексами наименований):

Filter, sort, unique (GS18) Google Таблицы, Microsoft Excel, Длиннопост

Начнем с того, что обрежем хвостики от наших айдишников. Потом пропустим это все через формулу массива:

Filter, sort, unique (GS18) Google Таблицы, Microsoft Excel, Длиннопост

Теперь соберем из этого фильтр. Допустим нам нужны все ID, в которых есть цифра 4.

Filter, sort, unique (GS18) Google Таблицы, Microsoft Excel, Длиннопост

Обратите внимание, REGEXMATCH работает только с текстом, поэтому 4 мы берем в кавычки, делаем ее из цифры текстом.

Теперь собираем все в одну формулу:

Filter, sort, unique (GS18) Google Таблицы, Microsoft Excel, Длиннопост

Ну, и если бы у нас были дубли, то всю эту конструкцию запихиваем в unique().

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

Filter, sort, unique (GS18) Google Таблицы, Microsoft Excel, Длиннопост

Можно и сорт запихнуть в юник и юник в сорт.

Вот и все. Постарался собрать самые частые кейсы, по крайней мере из того что помню за последнее время. Если еще наберется - сделаю вторую часть этого поста.

Показать полностью 10
37

Картинки в гугл таблицах (GS17)

Привет, дорогие чатлане!

Сегодня расскажу про работу с изображениями в ГТ, а именно - о вставке изображений в ячейку и функции image.

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

Вставка в ячейку:

Картинки в гугл таблицах (GS17) Google Таблицы, Microsoft Excel, Длиннопост

Можно загрузить с компа или по ссылке:

Картинки в гугл таблицах (GS17) Google Таблицы, Microsoft Excel, Длиннопост

Выглядит это следующим образом:

Картинки в гугл таблицах (GS17) Google Таблицы, Microsoft Excel, Длиннопост

К контенту в ячейках можно обращаться с помощью других функций (напр. ВПР или filter):

Картинки в гугл таблицах (GS17) Google Таблицы, Microsoft Excel, Длиннопост

По поводму функции image. Делает она тоже самое, но только по ссылке.

Н.Б. Если нужно загрузить с компа - загружайте на гугл драйв и копируйте ссылку.

Картинки в гугл таблицах (GS17) Google Таблицы, Microsoft Excel, Длиннопост

Для функции image можно указать точные размеры для вывода фото в таблицу и сделать это можно четырьмя способами.

В данном случай я не буду пытаться переписать документацию ибо для image она дофига понятная:

https://support.google.com/docs/answer/3093333?hl=RU

размер – [ НЕОБЯЗАТЕЛЬНО – 1 по умолчанию ] – режим отображения изображения:

1 – изменяет размер изображения таким образом, чтобы оно целиком помещалось в ячейке. Сохраняет соотношение сторон изображения.

2 – растягивает или сжимает изображение таким образом, чтобы оно целиком помещалось в ячейке. Не сохраняет соотношение сторон изображения.

3 – размещает изображение в оригинальном размере. Может приводить к кадрированию изображения.

4 – позволяет указать размеры изображения вручную.

На всякий случай покажу что такое "кадрирование изображения":
Картинки в гугл таблицах (GS17) Google Таблицы, Microsoft Excel, Длиннопост

Вот и всё. Пост получился лайтовым, но функция полезная и далеко не все о ней знают.

Традиционно, ссылка на таблицу: https://docs.google.com/spreadsheets/d/1vGtFrokmboWuK7w9nkBM...

Показать полностью 6
73

Продвинутые спарлайны в гугл таблицах (GS16)

И снова здравствуйте.

Отдельно пилю пост про использование спарклайнов, как сделать таблицы нагляднее. Разберу один пример - как сделать заполняющуюся шкалу выполнения нескольких задач и сделать разные цвета в зависимости от прогресса. Меньше 25% - красная, от 25% до 50% - желтая, от 50% до 75% - зеленая, от 75% и выше - розовая (потому что я так хочу).

Про синтаксис можно прочитать в прошлом посте: Спарклайны в гугл таблицах (GS15)

Итак, к делу.

Сначала сделаю заготовку с задачами. Здесь будет список задач и выпадающий список - "Сделано", "В работе".

Продвинутые спарлайны в гугл таблицах (GS16) Google Таблицы, Microsoft Excel, Длиннопост

Делаем проверку данных для простого использования (создания выпадающего списка):

Продвинутые спарлайны в гугл таблицах (GS16) Google Таблицы, Microsoft Excel, Длиннопост

Список прописываем через запятую.

Продвинутые спарлайны в гугл таблицах (GS16) Google Таблицы, Microsoft Excel, Длиннопост

Заготовка под спарклайн:

Продвинутые спарлайны в гугл таблицах (GS16) Google Таблицы, Microsoft Excel, Длиннопост

Сейчас он считает кол-во задач в статусе Сделано и делит на общее кол-во задач. Получает % общего выполнения. В опциях прописываем тип Шкала и ставим максимальное значение 100%.

Теперь цвета:

Здесь есть два варианта - switch и ВПР. Мне проще пользоваться ВПРом, к его синтаксису я больше привык, поэтому возьму его.

Что нужно сделать - нужно внутри опции прописать ВПР, внутри ВПРа собрать сортированный массив с значениями % и цветами и прописать в конце ВПРа единицу, чтобы он адекватно работал с сортированным массивом.

Продвинутые спарлайны в гугл таблицах (GS16) Google Таблицы, Microsoft Excel, Длиннопост

И еще пример для зеленого:

Продвинутые спарлайны в гугл таблицах (GS16) Google Таблицы, Microsoft Excel, Длиннопост

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

=SPARKLINE(countifs(B3:B7,"Сделано")/counta(B3:B7),{"charttype","bar";"max",1;"color1",VLOOKUP(countifs(B3:B7,"Сделано")/counta(B3:B7),{{0%;25%;50%;75%},{"red";"yellow";"green";"pink"}},2,1)})

Для наглядности - вот так выглядит внутренний массив с % и цветами:

Продвинутые спарлайны в гугл таблицах (GS16) Google Таблицы, Microsoft Excel, Длиннопост

Ссылка на таблицу:

https://docs.google.com/spreadsheets/d/1uU53nws2r-fe1R-wse4D...

Показать полностью 7
88

Спарклайны в гугл таблицах (GS15)

Привет, дорогие чатлане!

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

Что такое спарклайн? Просто говоря - это график в ячейке. В екселе для них есть относительно удобный конструктор, а у нас - отдельный язык запросов =) . Ну, не время расстраиваться.

Вот самый простой вид спарклайна:

Спарклайны в гугл таблицах (GS15) Google Таблицы, Microsoft Excel, Длиннопост

Массив численных значений, формула, все готово.

Однако спарклайны бывают других видов и их можно относительно тонко настраивать.

Первое - вид спарклайна. Есть четыре charttype:

1 - Линия (это то что на скрине)

2 - Столбцы

3 - Винлосс график

4 - Шкала.

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

Спарклайны в гугл таблицах (GS15) Google Таблицы, Microsoft Excel, Длиннопост

Это тип графика шкала. Если мы хотим, например, поменять цвета - ставим ; внутри фигурных скобок и прописываем значения для color1 и color2. Подойдут как названия на английском, так и хекс (https://colorscheme.ru/html-colors.html):

Спарклайны в гугл таблицах (GS15) Google Таблицы, Microsoft Excel, Длиннопост

С помощью параметра max можно указать максимальное значение (минимальное - нельзя), а с помощью параметра rtl - центрирование по правому или левому краю.

Спарклайны в гугл таблицах (GS15) Google Таблицы, Microsoft Excel, Длиннопост

Аналогично для графиков по двум осям:

Спарклайны в гугл таблицах (GS15) Google Таблицы, Microsoft Excel, Длиннопост

Вместо max - здесь xmax и ymax (здесь есть минимальные значения). Верхняя строка - ось Х, нижняя - ось Y.

И аналогично для колонок и винлоса:

Спарклайны в гугл таблицах (GS15) Google Таблицы, Microsoft Excel, Длиннопост

Только здесь больше заморочек с цветами - можно отдельно указывать цвет колонки если она отрицательная - color или положительная - negcolor. (В документации опечатка и они пишут colour - этот вариант не работает. Скрин ниже).

Спарклайны в гугл таблицах (GS15) Google Таблицы, Microsoft Excel, Длиннопост

Ниже запись как в документации (не рабочая).

Спарклайны в гугл таблицах (GS15) Google Таблицы, Microsoft Excel, Длиннопост

В остальном по документации все ок. Вот ссылка на нее: https://support.google.com/docs/answer/3093289?hl=en-GB

Ссылка на док: https://docs.google.com/spreadsheets/d/1uU53nws2r-fe1R-wse4D...

Показать полностью 7
67

Массивы в гугл таблицах (GS14)

Привет, дорогие подписчики и читатели Пикабу.

Продолжая тему мануала по гугл таблицам решил сегодня написать пост про массивы.

Сразу скажу, что я не представляю как эта история реализована в Excel, поэтому не смогу сравнить синтаксис и область применения, да и большая часть формул, которые я покажу - не будут работать в детище чипирователя великой Руси.

Что такое массив (array)?

Прежде всего это объект. Объект, который содержит в себе набор данных. Массивы в ГТ могут быть одномерными (строка или столбец) и многомерными - целиковая таблица (дефакто это одномерный массив состоящий из одномерных массивов).

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


Формула типа ={1;2} будет являться "вертикальным" массивом, "верхний" элемент которого равен 1, а "нижний" - 2.

Формула типа ={1\2} будет являться "горизонтальным" массивом", "левый" элемент - 1, "правый" - 2. В английской версии эта формула записывается как ={1,2}.

Формула типа = { { 1;2} , {3; 4}} будет выглядеть так:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

В своем посте про switch case я показывал, как такого рода массивы могут быть использованы в функции ВПР (VLOOKUP). Ссылка: Swtich case в гугл таблицах или не самый очевидный ВПР (GS8)


И это далеко не единственный способ задать массив.

Второй вариант - arrayformula(). Эта функция не имеет русского эквивалента. arrayformula повторяет формулу, которая в ней записана для каждого элемента массива. На выходе, как правило, она также дает массив. Пример:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Что характерно - в случае arrayformula и еще ряда функция (таких как ВПР (второй аргумент), filter, счётеслимн и т.д.) они принимают аргументы в качестве массива, при этом не требуют от пользователя явной записи в виде массива (через фигурные скобки). Даже простая функция СУММ принимает на вход именно массив. Зная это - мы можем делать вложенные функции.

Приведу повторно пример из поста: Фильтры и ВПРы в ГТ (GS2)

У нас есть следующая таблица:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

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

Тут нам поможет функция REGEXMATCH() - соответствие текста определенному регулярному выражению. О самих регулярках мы поговорим в будущем. Пока нам нужно только находить букву Е. Сама функция REGEXMATCH() возвращает 1 или 0, т.е. входит регулярка в текст или не входит. Наглядно это выглядит так:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Н.Б. Формула прописана только в ячейке H1. Благодаря формуле массива она сама протянулась вдоль диапазона G1:G10.

Теперь поместим формулу из ячейки H1 в самый обычный фильтр.

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

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

Н.Б. Фильтр понимает, что все значения и все сверки ему нужно пройти построчно. Поэтому внутри самого фильтра arrayformula можно не использовать.

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Как мы используем arrayformula в работе?

Обычная история - есть пополняемый реестр, в котором нужно постоянно протягивать формулы.

Например, заполняемая форма. Положим у нас есть форма, которую заполняют сотрудники при тратах корп денег и нам нужно по логину почты сотрудника для каждой записи формы протянуть его ФИО. Делается это с помощью обычного ВПР. В excel нам помогла бы умная таблица, которая сама протягивает за нас формулы. Здесь такого нет. Давайте чуть усложним кейс и положим, что мы не знаем сколько сотрудников будут заполнять, а делать справочник заранее нам долго. Тогда нам понадобится список всех НОВЫХ логинов, которых мы еще не внесли в справочник. Приступим.

Первым делом сделаем имитацию формы. Вот такая получилась заготовка:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Первым делом прописываем фильтр, который будет показывать нам новые логины. Нам потребуется комбинация isna(vlookup()). Детально про нее я рассказывал в посте: Фильтры и ВПРы в ГТ (GS2)

Получилось следующим образом:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

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

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Косметическим исправлением будет добавление в M2 функции iferror, которая будет убирать записи #N/A.

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Выглядит это таким образом.Теперь заполнение таблицы будет поэтапным (нам не нужно заранее собирать справочник для ВПРа), формулы подтягиваться будут постоянно. Особенно это удобно для таблиц, работа которых происходит на бекенде, чтобы не нужно было в них постоянно заходить и эти формулы протягивать.


Какие могут быть ошибки связанные с фильтрами или массивами?

Первое -  синтаксис. ГТ автоматически закроет для вас обычные скобки, но за фигурными - нужно следить самостоятельно. Если получаете ошибку типа "Formula parse error" - с большой вероятность вы налажали с фигурными скобками.

Второе - если использовать arrayformula по беконечному диапазону (типа А:А), при это расположить ее в ячейке B2 - таблица будет пытаться постоянно достроить саму себя, т.к. формула массива всегда будет обработать на одну строку больше чем есть. Это бесконечный цикл. Нужно удалять формулу, чистить лишние строки и переписывать.

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

Третье - записи внутри массивов нельзя править руками. ЕСли в таблице сверху я попробую вручную внести данные в ячейку М4, то будет следующее:

Массивы в гугл таблицах (GS14) Google Таблицы, Microsoft Excel, Длиннопост

Там где есть риск ручных правок - лучше не использовать такие формулы или выстраивать архитектуру доки таким образом, чтобы ручных правок не было.


По традиции - ссылка на док: https://docs.google.com/spreadsheets/d/1mU6d4ZBzgXQyx3I7EQHi...

Показать полностью 11
Отличная работа, все прочитано!