Excel, Google: автоматизируй это

Привет. Недавно я написал о зависимых выпадающих списках в Эксель. Там эта задача решается достаточно просто с помощью функции ДВССЫЛ (INDIRECT) в валидации данных. К сожалению, в гугл-таблицах нельзя использовать формулу при провреке данных, можно указать только диапазон или список значений (если идет речь о значениях из списка). Все статьи про зависимые списки в гугл-таблицах, которые я видел, предлагают только один подход: скрипт, который подвязывается на редактирование (onEdit(e)), который определяет в зависимый список и назначает его для валидации данных в соседней ячейке. Мне этот способ не нравится по нескольким причинам: Решение для гугл-таблиц без скрипта Ноу-хау моего метода в создании динамических списков для каждой зависимой строки. Создадим лист "Данные", в котором перечислем все проекты: Создадим лист, на котором мы будем делать динамические списки, назовем его "Рабочий лист". Сначала сделаем основной список. В ячейке A1 разместим формулу: =transpose(UNIQUE('Данные'!A:A)

Предположим, у вас в таблице есть проекты и подпроекты, и вы хотите сделать выпадающий список, в котором подпроекты показываются только для выбранного проекта. Показываю, как легко и быстро сделать зависимые «выпадайки». Готовим списки Создадим лист "Списки", в котором укажем проекты и подпроекты. Проекты перечислим в строке 2. Подпроекты – в стоблик под каждым проектом. Строку 1 оставим для служебных формул, которые будут указывать диапазон, в котором перечислены подпроекты. Формула в строке 1 (формула вводится в A1 и протягивается по всей строке, где есть проекты): =АДРЕС(3; СТОЛБЕЦ())&":"&АДРЕС(СЧЁТЗ(A2:A1048576)+1, СТОЛБЕЦ()) =ADDRESS(3,COLUMN())&":"&ADDRESS(COUNTA(A2:A1048576)+1,COLUMN()) Делаем «выпадайки» Если вы еще не делали выпадающие списки, рекомендую сначала ознакомиться со статьей https://zen.yandex.ru/media/excel/kak-sdelat-vypadaiuscii-spisok-v-excel-chast-1-prostaia-5f2bd9839bd10957f03c9d92 Создаем новый лист и делаем табличку, где будут выпадайки. Подсказка: если данн

Есть метод отладки формул, который работает в гугл-таблицах, эксель, да и везде где только можно – луковичная модель отладки формул. В этой модели вы берете сложную-сложную вложенную формулу, и как с луковицы, снимете с нее функции слой за слоем. Этот же метод отлично подходит для понимания, как работает сложная формула. Посмотрим на примере! Я подготовил таблицу, в которой формула в ячейке A3 переворачивает заданную в ячейке A1 строку: https://docs.google.com/spreadsheets/d/12zkIoCyWtW2IfcY7F6w7xDLWXpb1SFQ9lUYzlXnhQh8/edit?usp=sharing Вот сама формула: =join("";arrayformula(mid(A1;len(A1)-sequence(len(A1);1;0;1);1))) Давайте развернем ее слой за слоем: Стало понятнее? Наверное, нет. Давайте теперь соберем формулу в обратном порядке: =sequence(len(A1);1;0;1) Получаем последовательность чисел от 0 до длины слова в ячейке A1 - 1 =len(A1)-sequence(len(A1);1;0;1) Получаем последовательность в обратном порядке ( Для самостоятельной работы: мы могли бы сделать формулу чуть проще, убрать шаг

В больших таблицах очень часто первый столбец выделяют для указания порядкового номера – с такими таблицами потом удобнее работать. Этот столбец можно сделать несколькими способами, например, вручную писать номер, или использовать автозаполнение, или даже формулу. Сегодня рассмотрим эти способы, а также покажу вам, на мой взгляд, самый удобный способ. Способ 1 - Вручную или через автозаполнение Когда строк немного, можно проставить нумерацию вручную – много времени это не займет. Если строк чуть больше 3, проще будет использовать автозаполнение: заполнить первые 2-3 ячейки в столбце, выделить их, и протянуть вниз на столько строк, сколько нужно. Плюс только один: совем просто, самый базовый уровень. Минусы такого подхода очевидны: добавили несколько строк – нужно заново протягивать нумерацию. Добавили строку сверху или в середине – нужно протягивать нумерацию сверху или с середины до самого низа. Так себе решение. Способ 2 – формулы в ячейках Можно задать номер строки формулой и скопир

В гугл-таблицах и эксель все просто. В формуле мы числа и ссылки на другие ячейки пишем никак не выделяя, а текст заключаем в кавычки. Но что если текст сам должен содержать кавычку? Например, нам нужно заменить обычные кавычки на красивые, типографические: =ПОДСТАВИТЬ(ПОДСТАВИТЬ("Он сказал ""Поехали!"" и махнул рукой", " """, " “"), """", "”") =SUBSTITUTE(SUBSTITUTE("Он сказал ""Поехали!"" и махнул рукой", " """, " “"), """", "”") Посмотрим, как мы это написали: первым аргументом функции ПОДСТАВИТЬ идет текст, и мы этот текст обрамляем в кавычки, а так как в тексте тоже есть кавычки, то мы их удваиваем. Две кавычки подряд таблица воспринимает, как кавычка в тексте. Затем у нас идет странная конструкция: кавычка, пробел и три кавычки. На самом деле, все как и в предыдущем случае: текст заключается в две кавычки (первая и четвертая), а две кавычки в тексте заменяются на одну. А что с апострофом (одинарная кавычка)? Она используется в гугл-таблицах, и с помощью нее, например, можно де

Бухгалтерам часто нужно подсчитать количество рабочих дней в месяце, или между двух дат. Специально для этого в Эксель есть функция (и в гугл таблицах тоже): ЧИСТРАБДНИ (NETWORKDAYS) Эта функция принимает первым аргументом начало периода, а вторым — конец. И выводит количество рабочих дней между этими двумя датами. Введем в ячейке A1 функцияю ЧИСТРАБДНИ, а в A4 и A5 — начало и конец периода, и укажем эти ячейки, как аргумент функции. Но просто вычесть субботу и воскресенье — ума много не надо. Наверное, такую функцию можно было бы реализовать самостоятельно. Нужно еще и вычитать праздничные дни. Поэтому в функции ЧИСТРАБДНИ можно указать третий аргумент: список всех праздничных дней. Еще бы их добавить автоматически... Для этого можем воспользоваться одним из сервисов в интернете, например, http://xmlcalendar.ru, который позволяет загрузить производственный календарь в виде XML. Этот XML файл можно импортировать в Эксель: Прежде чем импортировать, нужно развернуть таблицу Days (клик н

Все пользователи* эксель и гугл-таблиц привыкли к A1-нотации, когда номер колонки кодируется буквами (от A до ZZZ), а номер строки — числом (* за исключением ретроградов, использующих R1C1 нотацию😂). Расскажу, как легко конвертировать буквенный номер столбца в числовой и обратно! От ссылки на ячейку к номеру столбца Все просто! Есть встроенная функция КОЛОНКА (COLUMN), которая возвращает номер колонки. Если вызвать ее без аргументов, она возвращает номер колонки для ячейки, в которой она указана. Если вызвать ее с ссылкой на ячейку, она вернет номер указанной колонки. От букв к номеру Укажем в ячейке A1 (и ниже) названия столбцов, которые мы хотим преобразовать в числовое представлением. Тогда формула в B1 (и ниже) будет выглядеть так: =КОЛОНКА(ДВССЫЛ(A1&1)) =COLUMN(INDIRECT(A1&1)) Разберем, как работает эта формула. A1&1 — это слияние двух строк. К строке в ячейке A1 дописывается строка "1". Кстати, вместо "1" может быть любое число, оно ни на что не влияет, и нужно только для того,

Вау, а ведь прошло уже больше месяца с момента последней публикации. Но чертов ремонт и обилие работы не позволяли даже приблизится к былому графику публикаций. Буду восстанавливать репутацию. Расскажу, что делал я (помимо работы и ремонта). У меня за это время было несколько интересных проектов — они сейчас в разной степени готовности, но по запросу могу довести до нужного заказчику вида. #Автопубликация объявлений в #авито, циан, яндекс недвижимость, домклик и юла. Самый мой большой долгострой. В полуавтоматическом режиме это уже работает - для одного заказчика я уже третий месяц публикую объявления с регулярными обновлениями. Источник — гугл таблицы, готовый xml размещается на специально сервере вместе с картинками и скармливается площадкам объявлений. Вместе мы узнали достаточно много нового, и я плавно двигаюсь к полной автоматизации процесса. Из интересных моментов: некоторым заказчикам выгоднее вместо использования платных опций поднятия объявлений просто заново размещать его.

А что если не ломиться в открытую дверь? Если нужны данные от ютуба, может можно ютуб попросить эти данные дать? В двух прошлых статьях мы рассмотрели варианты парсинга веб-страницы, которую ютюб отдает браузеру и извлечение из нее данных, сегодня мы попробуем попросить ютюб дать эти данные в удобном для нас виде. Это третья в цикле статей про парсинг ютуба, и пятая на тему парсинга вообще. Вот ссылки на предыдущие две: Введение. Что такое API? Современные сайты очень далеко ушли от простых веб-страничек времен становления интернета, по сути многие сайты сейчас представляют собой приложения, мигрировавшие в интернет. Очень часто сайт состоит из трех приложений: API (Application Programming Interface) — это специально оговоренный язык, на котором могут коммуницировать приложения, в том числе, фронтенд с бэкендом. Чем нам это может быть полезно? С помощью API зачастую можно получить больше данных, чем через прямой парсинг. Но на доступ к API могут быть свои ограничения: по количеству зап