Google таблицы. Динамическая генерация данных в диапазоне. Функции ArrayFormula&Offset Урок 29.
ฝัง
- เผยแพร่เมื่อ 1 ต.ค. 2024
- В этом уроке вы узнаете как делать автоматическое заполнение формулами или значениями диапазона в зависимости от количества заполненных ячеек столбца.
Для этого мы будем использовать функции Гугл таблиц arrayformula и offset.
Как добавить формулу в новую строку.
В конце урока будет очень полезный лайфхак!.
Станьте спонсором канала,. Подробнее:
/ @stmsolution
💰 Поддержать монетой:
🥇Bitcoin - bc1q5ve3p8zmcm6z9tapts6padpmnvv572tsujukad
📲 Через кнопку "Спасибо"
👍 Или станьте спонсором канала!
Поддержи автора канала пальцем в верх и подпиской это для
меня лучший стимул продолжать развиваться.
Мой паблик в Telegram - t.me/stmsolution
Буду рад видеть вас в моей группе ВК - public1...
Twitter - / stm_solution
Мой сайт stmsolution.ru
Еще больше видео на моем канале / @stmsolution
Друзья, пишите сюда ваши вопросы и замечания! Постараюсь на все ответить по возможности.
Если вам нравится видео, вы можете его поддержать лайком а так же, при желании, поддержать канал монетой
🎊 stm-solution.diaka.ua/donate
🥇Bitcoin - bc1q5ve3p8zmcm6z9tapts6padpmnvv572tsujukad
📲 Через кнопку "Спасибо"
👍 Или станьте спонсором канала!
капец. гугл вообще мутит. кучи полезных простых опций экселя нет. конечно есть и то чего нет экселе но вес же .
например
функция продления в экселе это просто форматировтаь как таблицу и вее можно забыть про цифры все столюбики с названиями и автоматически пересчитываются. попробовла скопировать в гугл ??ybabuf ,только цвет проблевает. Думаю оо щас экспортирую будет норм. нифига опять толькко цвеь. там есть правило условного форматирования .. тоже не работает. так что в уггл еостается дважды тыкать на крестик автозаполенения .. ну капец. ил зарнее создавать продленный диапазон вперед и туда вставлять все новое. либо же вставлять нвоые строи сверху а не сниз уно это не оч удобно и формулу все равно протягивать вверх придется .. копец аа кстате как вариант можно закрепить строку в конце документа или в начале которая ссылается на последний элемент но вот если туда чтото ввести она по идее автоматом уйдет вперед .. короче непонятно .. лучше в экселе работать ))))) но общийдоступ никто не отменял
я в таких случаях использую формулу чуть другую. arrayformula(if(isblank("первый столбец"),"пусто", "перемножить значения"))
@@ДенисКузьменко-й1ш супер
@@DemidovD это извечная борьба добра с добром, гугл и ексель, у каждого свои преимущества и недостктки, главное что обе компании и гугл и майкрософт совершенствуют свои детища, от чего выигрываем только мы, пользователи. Но лично мой выбор падает на сторону гугл таблиц, как бы не было много функций в экселе, в гугле все таки удобнее
@@ДенисКузьменко-й1шфайл пример можна?
Боже храни этого человека) за 2 дня столько интересных ответов нашел на вашем канале. Огромное спасибо за проделанную работу!
Спасибо)) так меня ещё не хвалили;))
Это самое оно когда автоматически добавляются строки из Гугл форм, и при этом формула сохраняется в новых строках. Спасибо.
Здравствуйте!
А как это можно применить к формуле SUM(B2:F2) ?
Чтоб также автоматически применялась ко всем строкам.
У меня получается что она подсчитывает сразу всю таблицу в одной ячейке, а нужно чтоб каждая строка считалась отдельно.
Проще то же самое через array_constrain сделать
Одна из самых важных фишек! спасибо, куча запросов в инете типа "как добавить формулу в новую строку" , и ни одного ответа. добавь такое в описание ..... людям понравится
Спасибо, добавим.
не актуально, к сожалению, но за offset респект
вопрос решается более простой формулой:
=ARRAYFORMULA(ЕСЛИ(A2:A="";"";C2:C*D2:D))
спасибо!
Добрый день. Спасибо за труды. Подскажите. Возможно ли сохранять результат ячейки в другую ячейку с смещением? Условно: а*b=c - где «с» меняющиеся данные. Можно ли раз в час сохранять результат формулы в новую ячейку что б получить результат по суткам за каждый час
Прошло пять лет а всё еще актуально!
спасибо за такой подробный и доступный анализ. Подскажите. пожалуйста, а есть ли функция или возможность автоматически заполнять диапазон дат по ячейкам? Нужно, чтоб в ячейке было 03.01.2022-09.01.2022 и чтоб автоматически продолжить на следующую ячейку 10.01.2022-16.01.2022
Красава, спасибо! Про Array понятно, и очень полезно. Спасибо, что делишься знаниями бесплатно!
I realize it is quite randomly asking but do anyone know a good place to stream new movies online?
@Devon Briar Flixportal :P
Про Array очень круто, спасибо за полезный и доступный урок!
очень полезный урок, позволит решить опупенную задачу простейшим способом
Не совсем понятно применение формулы Ofset. Просто выделяешь диапазон с запасом или вообще до конца не указывая номер последней строки и ARRAYFOMULA работает во всём указанном диапазоне. Вноси новые данные в этот диапазон и все будет считать, даже если добавлять строки внутри диапазона.
Вопрос, у меня есть 1 таблица в которой, несколько категорий с наименованиями. Диапазон в этих категориях постоянно меняется, т.к туда добавляются и удаляются строки. Могу ли я в категории сводить данные, а не во всей таблице?
Возможно это применить к впр, когда строки постоянно добавляются в первом столбце, и функцию впр нужно постоянно протягивать?
без "offset" разве не работает?
Попробуйте))
Здравствуйте.спасибо за уроки. Почему- то моя формула с arrayformula не работает по строчно, или как можно исправить.
=ARRAYFORMULA(IF(OR(B:B=15;B:B=6;B:B=3);"BEKET";"DALI"))
Подскажите пожалуйста, а как быть, если мне не нужно производить математические операции, а нужно просто сцепить несколько ячеек и чтобы они так-же протягивались автоматически?
textjoin или concatenate
добрый день, у меня вопрос, делаю сводную таблицу, 2 вычисляемых поля и не могу к ним обратиться (вычислить из них) как это можно сделать?
Без примера тяжело понять что у вас не работает,но попробуйте через query все делать, я сводными вообще не пользуюсь
Помогите пожалуйста! есть такая формула скажем в столбце "D" =IMPORTXML(F2;"//title"). в столбце "F" ссылки лежат, как сделать так, чтоб при добавлении новой ссылки, столбец "D" автозаполнялся. Такое ощущение, что ответ очень прост, но всю голову уже сломал
Спасибо за подробности. Но как к примеру сделать строку итогов, чтобы при добавлении строка итогов смещалась без проблем. Особенно если мы добавляем динамический диапазон из другой таблицы по ссылке
всем салюшен
если несколько таблиц подряд вниз?? нет не так. Можно сделать, чтобы строки не просто заполнялись , а генерировались
Скриптами можно
А что делать если есть пустые строки?
Припустим що маєм 10 автомобілів і 10 водіїв на кожній із машин, потрібно заповнити ТТН автоматично. Тобто вибравши із випадаючого списку водія, ТТН автоматично заповнюється номерами машини і його вагою яку він перевозить або може перевозити. Інформацію по привязках водіїв до машин можна розташувати на окремому аркуші. Так вот запитання як зробити автоматичне заповнення ТТН вибравши тільки водія із випадаючого списку?
Когда сделаете привязку водителей , далее пишите ТТН и в местах где надо вывести вес прописываете формулу filter где указываете фильтруемый диапазон, столбец с весом, далее указываете на ячейку с выпадающим списком водителей и выбираете = столбец с водителями.
Подскажите, пожалуйста, если есть 1 строка, в которой ячейки с формулами, ячейки с условным форматированием и ячейки на проверку данных, где уже внутри прописана формула. Вносится несколько данных в несколько ячеек и в строке все отработало и заполнилось. Я хочу, чтобы курсор стал после ввода последней ячейки на новую строку и там все форматирование и формулы повторялись автоматом.
Протягивание не подходит. Форматирование на 1000 ячеек тормозит и оно лишнее, если строк еще этих нет. Проверка данных тоже на весь столбец тормозит процесс. Создал одну строку, заполнил, посчитало, перекинуло на новую, где необходимо лишь вставить пару данных в ячейки ( все остальное форматирование скопировалось). Надеюсь доступно объяснил.
Уф и проверка данных на 1000 строк вообще не заметны должны быть, тормозить может только если у вас много функций импорта , а так не мучайтесь , иначе можете попробовать получить скрипты, то что вы хотите только через них реализуется
@@stmsolution Благодарю! А можно через скрипт на одной странице и вводить вручную данные и функцией фильтр по условию, сводить необходимые данные с другой страницы?
Подскажите как сделать ограничение ввода с переходом. Поясняю: есть 15 строк для 1 группы учеников и рядом 15 строк для второй группы. Нужно чтобы таблица брала фамилии учеников из общей таблицы, но когда лимит группы (15 человек) закончен , сразу переходила на новую группу. Если можно покажите на примере пожалуйста clck.ru/FNuwD
Спасибо )
Снимите, пожалуйста, видео о том, как правильно строить прогнозы и какие формулы использовать. Коэффициент сезонности и т.д. в чем разница между трендом, ростом и предсказанием и что лучше. Как можно это совместить с импортом с других сайтов для прогноза например цен или курсов валют. И как можно по времени выгружать эти данные автоматически на отдельный лист?
Спасибо за вопрос, но это очень обширная тема требующая знаний не только в гугл таблицах, но и торговле , а так же минимальное знание скриптов. Если по скриптам ещё более менее то в торговле я на уровне продавца консультанта, но ни как не аналитика)) так же нужен конкретный случай с конкретными данными. Тяжело понять что вы хотите, если нет конкретики или примера. На сколько я понял все что вы хотите кроме, последнего вы сможете найти в моих предыдущих уроках, выгрузку по времени уже надо делать скриптами.
@@stmsolution Вы можете показать пример выгрузки по времени? У Вас такие интересные и понятные уроки, эксклюзив)
В плане скриптов не считаю себя ещё достаточно опытным человеком, по этому я впринципе не запускаю эту рубрику на канале, надо ещё опыта набраться. Не хочу людям показывать не качественный контент, который может и будет работать, но не совсем правильно в плане реализации. По этому пока что эту тему я не хочу трогать.
@@stmsolution Спасибо Вам за ответ. В любом случае с нетерпением ждём новых видео)
08/04/24
Кишинев+++
Подскажите пожалуйста, как 1 и вторую ячейку с датами поочередно продлить ну потянуть полузнок до конца не в ручную а быстрее как-нибудь
Интересно было бы совместить все это с QUERY, так как многие серьезные запросы делаются с помощью нее, а вот добавить запрос автоматически вызывает трудности
Query с диапазонами и так хорошо работает и условий можно много прописать, большинство задач решается с помощью собственных запросов функции. Нужен конкретный случай и уже его разбирать и строить динамические запросы ...
А можна рандомайзер для таблицы гугл установить ?
Что именно вы хотите?
@@stmsolution Чтобы теги для инстаграма смешать.
@@МаксМ-с7ы думаю да, тут надо ряд функций использовать, но надо знать конкретно задачу. Что куда и когда должно попадать и т.д.
@@stmsolution Спасиба
а как применить этот автоматический подсчет к такой функции как textjoin?
Дана функція перетворює число в текстовий формат, може бути корисне при формуванні довіреностей і рахунків.
function number_to_string2(_number) {
var _arr_numbers = new Array();
_arr_numbers[1] = new Array('', 'один', 'два', 'три', 'чотири', "п'ять", 'шість', 'сім', 'вісім', "дев'ять", 'десять', 'одиннадцать', 'дванадцать', 'тринадцать', 'чотирнадцять', "п'ятнадцать", 'шістнадцать', 'сімнадцать', 'вісімнадцать', "дев'ятнадцать");
_arr_numbers[2] = new Array('', '', 'двадцять', 'тридцять', 'сорок', "п'ятдесят", 'шестдесят', 'сімдесят', 'вісімдесят', "дев'яносто");
_arr_numbers[3] = new Array('', 'сто', 'двісті', 'триста', 'чотириста', "п'ятсот", 'шістсот', 'сімсот', 'вісімсот', "дев'ятсот");
function number_parser(_num, _desc) {
var _string = '';
var _num_hundred = '';
if (_num.length == 3) {
_num_hundred = _num.substr(0, 1);
_num = _num.substr(1, 3);
_string = _arr_numbers[3][_num_hundred] + ' ';
}
if (_num < 20) _string += _arr_numbers[1][parseFloat(_num)] + ' ';
else {
var _first_num = _num.substr(0, 1);
var _second_num = _num.substr(1, 2);
_string += _arr_numbers[2][_first_num] + ' ' + _arr_numbers[1][_second_num] + ' ';
}
switch (_desc){
case 0:
var _last_num = parseFloat(_num.substr(-1));
var _last_num2 = parseFloat(_num.substr(-2));
if (_last_num == 1 && _last_num2 != 11) _string += '';
else if (_last_num > 1 && _last_num < 5 && _last_num2 != 11 && _last_num2 != 12 && _last_num2 != 13 && _last_num2 != 14 && _last_num2 != 15) _string += '';
else _string += '';
break;
case 1:
var _last_num = parseFloat(_num.substr(-1));
var _last_num2 = parseFloat(_num.substr(-2));
if (_last_num == 1 && _last_num2 != 11) _string += 'тисяча ';
else if (_last_num > 1 && _last_num < 5 && _last_num2 != 11 && _last_num2 != 12 && _last_num2 != 13 && _last_num2 != 14 && _last_num2 != 15) _string += 'тисячі ';
else _string += 'тисяч ';
_string = _string.replace('один ', 'одна ');
_string = _string.replace('два ', 'дві ');
break;
case 2:
var _last_num = parseFloat(_num.substr(-1));
if (_last_num == 1) _string += 'мільйон ';
else if (_last_num > 1 && _last_num < 5) _string += 'мільйона ';
else _string += 'мільйонів ';
break;
case 3:
var _last_num = parseFloat(_num.substr(-1));
if (_last_num == 1) _string += 'мільярд ';
else if (_last_num > 1 && _last_num < 5) _string += 'мільярда ';
else _string += 'мільярдів ';
break;
}
_string = _string.replace(' ', ' ');
return _string;
}
function decimals_parser(_num) {
var _first_num = _num.substr(0, 1);
var _second_num = parseFloat(_num.substr(1, 2));
var _string = ' ' ;
if (_second_num == 1) _string += ' ';
else if (_second_num > 1 && _second_num < 5) _string += ' ';
else _string += ' ';
return _string;
}
if (!_number || _number == 0) return false;
if (typeof _number !== 'number') {
_number = _number.replace(',', '.');
_number = parseFloat(_number);
if (isNaN(_number)) return false;
}
_number = _number.toFixed(2);
if(_number.indexOf('.') != -1) {
var _number_arr = _number.split('.');
var _number = _number_arr[0];
var _number_decimals = _number_arr[1];
}
var _number_length = _number.length;
var _string = '';
var _num_parser = '';
var _count = 0;
for (var _p = (_number_length - 1); _p >= 0; _p--) {
var _num_digit = _number.substr(_p, 1);
_num_parser = _num_digit + _num_parser;
if ((_num_parser.length == 3 || _p == 0) && !isNaN(parseFloat(_num_parser))) {
_string = number_parser(_num_parser, _count) + _string;
_num_parser = '';
_count++;
}
}
if (_number_decimals) _string += decimals_parser(_number_decimals);
return _string;
}
Спасибо, надо будет потестить и вы забыли добавить что она подходит для Украинского языка, для других языков придется переделывать, но тут главное уже желание, основа то есть.
Очень полезное видео, спасибо!
Самое крутое что ты говоришь где это можно применить, для чего эта формула)
Уважаемый автор! Спасибо за твою работу. Очень прошу, подскажи как сделать выпадающие списки из диапазонов из других файлов? Очень нужно понять как работает эта функция, но так и не нашел этого решения.
Функцией importrange вытаскиваете в свою таблицу нужный диапазон и после ссылаетесь на него в проверке данных.Уроки на канале есть.
или например для обыкновенного SUM. Спасибо
Можно ли использовать array формулу в суммесли в качестве аргумента? как?
Не совсем понял что вы хотите?
Спасибо за видос! Очень познавательно. Как пропустить несколько определенных строк в формуле?
Добрый, никак
Огромное спасибо!
Помогите решить проблему. Хочу использовать arrayformula что бы автоматически рассчитывать значение в ячейке когда добавляется новая строка, но у меня в ячейке используется формула СУММ(диапазон) или СЦЕПИТЬ(диапазон) и получается, что весь результат записывается в ячейку где стоит формула, а не построчно. docs.google.com/spreadsheets/d/1JiY1lhVaIxlnGiIzFOo9jMwjSK9idq8rn24fuJ6zyXI/edit?usp=sharing
Попробуйте функции index+ split, чтобы разделить входящий массив на диапазоны
Буду свободен , гляну вашу таблицу.
Дорый день!
Спасибо за обучающее видео.
Есть дополнительный вопрос - у меня ситуация можно сказать обратная: есть формула =arrayformula(IFERROR(REGEXEXTRACT(A2:A,"\D+"),0)) и получается что, когда заканчиваются данные в столбце А, то в столбце с даной форнмулой до конца листа гугл таблицы проставляются нули, даже по тем строкам, где уже нет данных в столбце А, на который ссылается формула. Понятно, что можно в фрмуле заменить 0 а " ",например, и тогда это визуально не будет так напрягать. Но возможно есть какой-то другой более умный способ - как уйти от расчетов в строках, где их уже не должно быть ввиу отсутсвия исходной заполненной информации.
Заранее благодарю за ответ!
Arrayformula(If(A"";ваша формула;""))
@@stmsolution спасибо! я в принципе так и прописала через "если", но думала, что это как-то аж сложно очень. Спасибо за ответ!
Помогите пожалуйста с формулой, не могу заставить автоматом копироваться, все другие работают, а эта нет:
=MAXIFS(Bills!A2:A;Bills!A2:A;">="&B2;Bills!A2:A;"
Посмотрите что выдает результат этой части формулы =">="&B2? Я думаю сразу понятно будет)) Это первое, второе, не понятно что вы хотите конкретно, опишите что вы хотите вытянуть?
@@stmsolution Огромное спасибо что заметили мой вопрос и извините за длинный текст, просто уже неделю головой о комп стучусь, перекопал весь инет чтобы найти решение проблемы и не нашел и только после этого вам пишу.
В ячейку С2. Нужно поставить последнюю дату платежа по номеру договора в промежутке между датами.
например: найти все платежи по договору 325 (предположим их 5 шт.) между 20.03.2019 и 20.04.2019 и тот платеж, дата которого наибольшая, тот и выводить как результат, точнее его дату. Эта формула работает, но не могу заставить копироваться автоматом.
В ячейку D2. Нужно суммировать все платежи по определенному номеру договора между указанными датами платежа.
например: найти все платежи по договору 325 (предположим их 5 шт.) между 20.03.2019 и 20.04.2019 и суммировать их.
И эта формула работает, но тоже не копируется.
toster.ru/questionversion?question_id=618241#version_775134
Must have
Не получилось у вас с формулой? =MAXIFS(Bills!A2:A;Bills!A2:A;">="&B2;Bills!A2:A;"
У вас правильно работает ваша формула с query, просто растяните ее в низ и все, вы же указали такой то договор в таком то диапазоне дат, он вам и выдает, растяните вниз формулу и все
@@stmsolution Нужно чтобы она автоматом вниз копировалась
Спасибо! Все оч круто объяснил)!
спасибо что смотрите
спасибо большое. Очень помогают ваши урони в освоении
Рад помочь
Спасибо! Очень вовремя.
Только у меня получилась формула немного короче, без COUNTA и OFFSET. Использую IF. Если значение в такой-то ячейке >0, то формула расчета, если нет, то " ".
А как вы узнаете сколько заполненных ячеек? Можете полность формулу прислать?
@@stmsolution Первые две строки - шапка таблицы, поэтому начинаем с третьей строки
=ARRAYFORMULA(IF(A3:A>0;'Авторы 2019'!G$3/'Авторы 2019'!G$14*B3:B;""))
В столбце А у меня текстовые значения, условие >0 в этом случае означает "ячейка заполнена", а так условие может быть любым в зависимости от ситуации. Можно усложнить, задав условие не по одной ячейке, а по двум, например (ЕСЛИ (И(...
Если значение результата для невыполненного условия не задавать, у меня отобразились 0.00 во всех строчках до конца таблицы, а с "" они просо пустые. пока в ячейке А не появится какое-то значение (туда подтягиваются данные из нескольких таблиц импортранжем).
Хороший вариант, а если на пример будет заполнена только ячейка А8,а все остальные пустые, как себя поведет формула?
@@stmsolution нормально она себя ведет, прилично :) просто бежит проверять дальше.
Хорошо, спасибо за совет)))
Кто дошёл до этого уровня ?
Я;)
STM Solution кто тебя этому научил ? ( я не гусман)
Всемогущий Гугл...
STM Solution самоучка?
Конечно))) курсов я не проходил)))
Все хорошо, но избавляйтесь от "так же само"
Спасибо, буду стараться))
не нужно не отчего избавляться, все людт разные, языки тоже, местные диалекты, тем более. Будьте терпимы, Владимир, избавляйтесь от шовинизма в принципе)