Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Гугл скрипт, авто растяжение формулы - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Гугл скрипт, авто растяжение формулы (Формулы/Formulas)
Гугл скрипт, авто растяжение формулы
negodyaj Дата: Воскресенье, 08.08.2021, 17:09 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 115
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Есть табличка в который идет импортрендж и к нему форумла.
Нужен скрипт, который в случае если в определнной ячейке FALSE, выполнит растяжение формулы в следующие 1000 строк.
И так же Добавит такое же кол-во новых строк.

https://docs.google.com/spreads....sharing

Вот тут в примере более наглядно описал. Скрипт вроде простой, но чот не могу сообразить.
 
Ответить
СообщениеЕсть табличка в который идет импортрендж и к нему форумла.
Нужен скрипт, который в случае если в определнной ячейке FALSE, выполнит растяжение формулы в следующие 1000 строк.
И так же Добавит такое же кол-во новых строк.

https://docs.google.com/spreads....sharing

Вот тут в примере более наглядно описал. Скрипт вроде простой, но чот не могу сообразить.
Автор - negodyaj
Дата добавления - 08.08.2021 в 17:09
Gustav Дата: Понедельник, 09.08.2021, 14:02 | Сообщение № 2
Группа: Админы
Ранг: Участник клуба
Сообщений: 2809
Репутация: 1184 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Выглядит несколько надуманно со скриптом. Если интересно, я подобные задачки с саморасширяющимися формулами решаю примерно так.

В отдельную ячейку, например, C1 - счётчик заполнений в интересующей колонке, в простейшем случае с функцией COUNTA:
[vba]
Код
=COUNTA(A:A)
[/vba]
А в ячейку B1 - саморасширяющуюся формулу:
[vba]
Код
=ArrayFormula(10+A1:INDEX(A:A;C1))
[/vba]

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеВыглядит несколько надуманно со скриптом. Если интересно, я подобные задачки с саморасширяющимися формулами решаю примерно так.

В отдельную ячейку, например, C1 - счётчик заполнений в интересующей колонке, в простейшем случае с функцией COUNTA:
[vba]
Код
=COUNTA(A:A)
[/vba]
А в ячейку B1 - саморасширяющуюся формулу:
[vba]
Код
=ArrayFormula(10+A1:INDEX(A:A;C1))
[/vba]
Автор - Gustav
Дата добавления - 09.08.2021 в 14:02
negodyaj Дата: Понедельник, 09.08.2021, 15:25 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 115
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
А если надо строки добавить? бЫвает что импортренж не дает данных так как не хватает строк?
 
Ответить
СообщениеА если надо строки добавить? бЫвает что импортренж не дает данных так как не хватает строк?
Автор - negodyaj
Дата добавления - 09.08.2021 в 15:25
Gustav Дата: Понедельник, 09.08.2021, 16:19 | Сообщение № 4
Группа: Админы
Ранг: Участник клуба
Сообщений: 2809
Репутация: 1184 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Насколько я знаю, IMPORTRANGE обычно сам добавляет строки в заведомо достаточном количестве, причем, не менее 500 + сколько надо. Сейчас уменьшил диапазон в вашей формуле с IMPORTRANGE до "Лист2!A1:A5" и удалил строки, начиная с 6-й - строк в таблице осталось 5. Потом исправил диапазон на "Лист2!A1:A6", т.е. добавил всего одну ячейку - строк в таблице стало 506.

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеНасколько я знаю, IMPORTRANGE обычно сам добавляет строки в заведомо достаточном количестве, причем, не менее 500 + сколько надо. Сейчас уменьшил диапазон в вашей формуле с IMPORTRANGE до "Лист2!A1:A5" и удалил строки, начиная с 6-й - строк в таблице осталось 5. Потом исправил диапазон на "Лист2!A1:A6", т.е. добавил всего одну ячейку - строк в таблице стало 506.
Автор - Gustav
Дата добавления - 09.08.2021 в 16:19
negodyaj Дата: Понедельник, 09.08.2021, 16:52 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 115
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
А если мне надо растянуть формулу
Код
=MINIFS(A:A;A:A;A1)
такого плана, то как быть? Подставл
Код
=MINIFS(A:A;A:A;A1:INDEX(A:A;C1))
, но не помогает
 
Ответить
СообщениеА если мне надо растянуть формулу
Код
=MINIFS(A:A;A:A;A1)
такого плана, то как быть? Подставл
Код
=MINIFS(A:A;A:A;A1:INDEX(A:A;C1))
, но не помогает
Автор - negodyaj
Дата добавления - 09.08.2021 в 16:52
Gustav Дата: Понедельник, 09.08.2021, 19:29 | Сообщение № 6
Группа: Админы
Ранг: Участник клуба
Сообщений: 2809
Репутация: 1184 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Можно, например, с помощью следующего скрипта, генерирующего последовательность формул:
[vba]
Код
function onEdit(e) {  
    var rng = e.range;
    var val = rng.getValue();
    if(~val.indexOf('FILLFORMULA') && rng.getNumRows()==1 && rng.getNumColumns()==1) {
        var arr = val.split('|');
        rng.offset(0,0,Number(arr[1]),1).setFormula(arr[2]);
    }
}
[/vba]Этому скрипту на "вход" (в произвольную ячейку, например, D1, с которой начинается генерация) подается строка специального вида:
[vba]
Код
FILLFORMULA|10|=MINIFS(A:A;A:A;A1)
[/vba]
В строке просматриваются три элемента, разделенные символом "|" ("вертикальная черта"). Первый элемент - кодовое слово "FILLFORMULA", второй элемент - число повторений формулы вниз, считая от ячейки ввода, третий элемент - сама формула, начинающаяся со знака равенства. Последовательность элементов диктуется удобством использования: удобно сначала отладить формулу в начальной ячейке, а затем приписать в ее начало строку вида FILLFORMULA|25| и нажать Enter - сгенерируется 25 копий формулы.

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Вторник, 10.08.2021, 01:29
 
Ответить
СообщениеМожно, например, с помощью следующего скрипта, генерирующего последовательность формул:
[vba]
Код
function onEdit(e) {  
    var rng = e.range;
    var val = rng.getValue();
    if(~val.indexOf('FILLFORMULA') && rng.getNumRows()==1 && rng.getNumColumns()==1) {
        var arr = val.split('|');
        rng.offset(0,0,Number(arr[1]),1).setFormula(arr[2]);
    }
}
[/vba]Этому скрипту на "вход" (в произвольную ячейку, например, D1, с которой начинается генерация) подается строка специального вида:
[vba]
Код
FILLFORMULA|10|=MINIFS(A:A;A:A;A1)
[/vba]
В строке просматриваются три элемента, разделенные символом "|" ("вертикальная черта"). Первый элемент - кодовое слово "FILLFORMULA", второй элемент - число повторений формулы вниз, считая от ячейки ввода, третий элемент - сама формула, начинающаяся со знака равенства. Последовательность элементов диктуется удобством использования: удобно сначала отладить формулу в начальной ячейке, а затем приписать в ее начало строку вида FILLFORMULA|25| и нажать Enter - сгенерируется 25 копий формулы.
Автор - Gustav
Дата добавления - 09.08.2021 в 19:29
negodyaj Дата: Понедельник, 09.08.2021, 19:33 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 115
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Тут проблема что ОнЭдит работает только при изменение ручном в таблице. Но если эта таблица испортная чисто и я туда не заходу не когда, то не сработает...(
 
Ответить
СообщениеТут проблема что ОнЭдит работает только при изменение ручном в таблице. Но если эта таблица испортная чисто и я туда не заходу не когда, то не сработает...(
Автор - negodyaj
Дата добавления - 09.08.2021 в 19:33
Gustav Дата: Понедельник, 09.08.2021, 19:51 | Сообщение № 8
Группа: Админы
Ранг: Участник клуба
Сообщений: 2809
Репутация: 1184 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Цитата negodyaj, 09.08.2021 в 19:33, в сообщении № 7 ()
Тут проблема что ОнЭдит работает только при изменение ручном в таблице.

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

МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата negodyaj, 09.08.2021 в 19:33, в сообщении № 7 ()
Тут проблема что ОнЭдит работает только при изменение ручном в таблице.

Событие onEdit выбрано чисто для сиюминутной иллюстрации. Не по душе оно вам - повесьте скрипт на триггер по времени, это не проблема.
Автор - Gustav
Дата добавления - 09.08.2021 в 19:51
negodyaj Дата: Вторник, 10.08.2021, 14:03 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 115
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата Gustav, 09.08.2021 в 19:29, в сообщении № 6 ()
function onEdit(e) {  
    var rng = e.range;
    var val = rng.getValue();
    if(~val.indexOf('FILLFORMULA') && rng.getNumRows()==1 && rng.getNumColumns()==1) {
        var arr = val.split('|');
        rng.offset(0,0,Number(arr[1]),1).setFormula(arr[2]);
    }
}

Попробовал этот скрипт, он то хороший) Ну тут надо руками вставять
Код
FILLFORMULA|10|=MINIFS(A:A;A:A;A1)
а мне то надо что б автоматом все срабатовало растяжение дальше формулы что была, а не руками вставлять в ячейку продолжение) Так я могу и сам растянуть фомрулу)
 
Ответить
Сообщение
Цитата Gustav, 09.08.2021 в 19:29, в сообщении № 6 ()
function onEdit(e) {  
    var rng = e.range;
    var val = rng.getValue();
    if(~val.indexOf('FILLFORMULA') && rng.getNumRows()==1 && rng.getNumColumns()==1) {
        var arr = val.split('|');
        rng.offset(0,0,Number(arr[1]),1).setFormula(arr[2]);
    }
}

Попробовал этот скрипт, он то хороший) Ну тут надо руками вставять
Код
FILLFORMULA|10|=MINIFS(A:A;A:A;A1)
а мне то надо что б автоматом все срабатовало растяжение дальше формулы что была, а не руками вставлять в ячейку продолжение) Так я могу и сам растянуть фомрулу)
Автор - negodyaj
Дата добавления - 10.08.2021 в 14:03
negodyaj Дата: Вторник, 10.08.2021, 20:21 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 115
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Написал код в итоге сам
Вешается на тригер по изменению таблицы или времени

Код
function autofill() {
var spreadsheet = SpreadsheetApp.openById("КОД ТАБЛИЦЫ");
var sheetname = spreadsheet.getSheetByName('НАЗВАНИЕ ЛИСТ ГДЕ ТЯНУТЬ ФОРМУЛУ');
var sheetname2 = spreadsheet.getSheetByName('НАЗВАНИЕ ЛИСТА ГДЕ ЯЧЕЙКА С КАКОЙ РАСТЯГИВАТЬ И УСЛОВИЕ НАДО ИЛИ НЕТ');
var triger = sheetname2.getRange('A3').getValue(); // Тут или тру или фолс, выполняем или нет скрипт
var iz = sheetname2.getRange('A1').getValue(); //С какой ячейки растягиваем и где лежит формула какую тянуть

var cell = 'A{name2}'.replace('{name2}', iz);
var iz2 = iz + 500; // на сколько ячеек тянем
var cell2 = 'A{name2}'.replace('{name2}', iz2);

if ( triger == false ) {
sheetname.getRange(cell).activate();
sheetname.getActiveRange().autoFill(sheetname.getRange(cell+':'+cell2), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheetname.getRange(cell+':'+cell2).activate();

}
};


Сообщение отредактировал negodyaj - Вторник, 10.08.2021, 20:22
 
Ответить
СообщениеНаписал код в итоге сам
Вешается на тригер по изменению таблицы или времени

Код
function autofill() {
var spreadsheet = SpreadsheetApp.openById("КОД ТАБЛИЦЫ");
var sheetname = spreadsheet.getSheetByName('НАЗВАНИЕ ЛИСТ ГДЕ ТЯНУТЬ ФОРМУЛУ');
var sheetname2 = spreadsheet.getSheetByName('НАЗВАНИЕ ЛИСТА ГДЕ ЯЧЕЙКА С КАКОЙ РАСТЯГИВАТЬ И УСЛОВИЕ НАДО ИЛИ НЕТ');
var triger = sheetname2.getRange('A3').getValue(); // Тут или тру или фолс, выполняем или нет скрипт
var iz = sheetname2.getRange('A1').getValue(); //С какой ячейки растягиваем и где лежит формула какую тянуть

var cell = 'A{name2}'.replace('{name2}', iz);
var iz2 = iz + 500; // на сколько ячеек тянем
var cell2 = 'A{name2}'.replace('{name2}', iz2);

if ( triger == false ) {
sheetname.getRange(cell).activate();
sheetname.getActiveRange().autoFill(sheetname.getRange(cell+':'+cell2), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
sheetname.getRange(cell+':'+cell2).activate();

}
};
Автор - negodyaj
Дата добавления - 10.08.2021 в 20:21
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Гугл скрипт, авто растяжение формулы (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!