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

Вход

Регистрация

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

 

= Мир MS Excel/Зависимые выпадающие списки - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Зависимые выпадающие списки
yurakhl Дата: Понедельник, 27.05.2024, 12:55 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

2021
Умные люди помогите)

Итак есть файл https://docs.google.com/spreads....0807426

В нем я хочу чтобы мне согласно листу-справочнику "ОПУ" на других листах при вводе значений из списка выпадали вариации их статусов. Т.е. по сути выпадающий список, зависящий от введенного первоначального значения

для этого я написал вот такой скрипт в Apps Script

Цитата

function dependList() {
let ss = SpreadsheetApp.openById('1UDrQpUKfdT9SHsOZTQE-z6kIAtUJ2vkUgi59075UCs4');
let sheet = ss.getSheetByName('111')
let opy = ss.getSheetByName('ОПУ');

let ar = sheet.getActiveCell();
let valueToFind = ar.getValue();

let values = opy.getRange(4, 3,opy.getLastRow(), 1).getValues();
console.log(values);
let row = values[0].indexOf(valueToFind)+5;
console.log(row);

let status = opy.getRange(row, 4,1,5).getValues();
console.log(status);

let validation = SpreadsheetApp.newDataValidation().requireValueInList(status).build();

ar.offset(0,1).setDataValidation(validation);

}



Вопросов несколько

1. Самое главное) Скрипт выдает мне все что нужно через консоль, но почему то не появляется выпадающий список на листе "111"

2. Когда написал let row = values[0].indexOf(valueToFind) и вывел в консоль мне выдало -1 - не могу понять почему так. Пришлось сделать +5

3. Мне нужно чтобы скрипт наботал на листах, начинающихся со слова WBS. и при этом только в определенных столбцах. Не придумал как задать это ограничение


Сообщение отредактировал yurakhl - Понедельник, 27.05.2024, 12:57
 
Ответить
СообщениеУмные люди помогите)

Итак есть файл https://docs.google.com/spreads....0807426

В нем я хочу чтобы мне согласно листу-справочнику "ОПУ" на других листах при вводе значений из списка выпадали вариации их статусов. Т.е. по сути выпадающий список, зависящий от введенного первоначального значения

для этого я написал вот такой скрипт в Apps Script

Цитата

function dependList() {
let ss = SpreadsheetApp.openById('1UDrQpUKfdT9SHsOZTQE-z6kIAtUJ2vkUgi59075UCs4');
let sheet = ss.getSheetByName('111')
let opy = ss.getSheetByName('ОПУ');

let ar = sheet.getActiveCell();
let valueToFind = ar.getValue();

let values = opy.getRange(4, 3,opy.getLastRow(), 1).getValues();
console.log(values);
let row = values[0].indexOf(valueToFind)+5;
console.log(row);

let status = opy.getRange(row, 4,1,5).getValues();
console.log(status);

let validation = SpreadsheetApp.newDataValidation().requireValueInList(status).build();

ar.offset(0,1).setDataValidation(validation);

}



Вопросов несколько

1. Самое главное) Скрипт выдает мне все что нужно через консоль, но почему то не появляется выпадающий список на листе "111"

2. Когда написал let row = values[0].indexOf(valueToFind) и вывел в консоль мне выдало -1 - не могу понять почему так. Пришлось сделать +5

3. Мне нужно чтобы скрипт наботал на листах, начинающихся со слова WBS. и при этом только в определенных столбцах. Не придумал как задать это ограничение

Автор - yurakhl
Дата добавления - 27.05.2024 в 12:55
Gustav Дата: Понедельник, 27.05.2024, 18:27 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2763
Репутация: 1140 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
мне выдало -1

-1 - это значит "значение не найдено". В скриптах нумерация массивов начинается с 0, т.е. первый элемент имеет индекс 0. Поэтому на значении -1 остановились как на признаке "не найдено". К этому значению не надо ничего добавлять - ни 5, ни 6, иначе получится какая-то ерунда.

.getValues() всегда возвращает двумерный массив, даже если в диапазон входит только одна ячейка (не путать с .getValue() без "s" на конце). Значение этой одной ячейки можно получить как values[0][0]. Т.е. указывать надо всегда два индекса: 1-й - строка, 2-й столбец. [0][0] означает 1-й столбец 1-й строки. Если указан только первый индекс values[0], то это означает целиком первую строку, возможно состоящую из нескольких ячеек.

Выражение values[0].indexOf(valueToFind) ищет в первой СТРОКЕ диапазона. Если нужен поиск по СТОЛБЦУ, то значения этого столбца сначала нужно подготовить в отдельном одномерном массиве, "вынув" их из диапазона. Обычно для таких целей у массивов используется метод map:
[vba]
Код
function findInRange1stColumn() {
  // поиск в первой колонке диапазона
    var vals = SpreadsheetApp.getActiveRange().getValues();
    var colarr = vals.map(v => v[0]);
    Logger.log(colarr.indexOf('значениеДляПоиска'));
}
[/vba]

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


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

Сообщение отредактировал Gustav - Понедельник, 27.05.2024, 19:08
 
Ответить
Сообщение
мне выдало -1

-1 - это значит "значение не найдено". В скриптах нумерация массивов начинается с 0, т.е. первый элемент имеет индекс 0. Поэтому на значении -1 остановились как на признаке "не найдено". К этому значению не надо ничего добавлять - ни 5, ни 6, иначе получится какая-то ерунда.

.getValues() всегда возвращает двумерный массив, даже если в диапазон входит только одна ячейка (не путать с .getValue() без "s" на конце). Значение этой одной ячейки можно получить как values[0][0]. Т.е. указывать надо всегда два индекса: 1-й - строка, 2-й столбец. [0][0] означает 1-й столбец 1-й строки. Если указан только первый индекс values[0], то это означает целиком первую строку, возможно состоящую из нескольких ячеек.

Выражение values[0].indexOf(valueToFind) ищет в первой СТРОКЕ диапазона. Если нужен поиск по СТОЛБЦУ, то значения этого столбца сначала нужно подготовить в отдельном одномерном массиве, "вынув" их из диапазона. Обычно для таких целей у массивов используется метод map:
[vba]
Код
function findInRange1stColumn() {
  // поиск в первой колонке диапазона
    var vals = SpreadsheetApp.getActiveRange().getValues();
    var colarr = vals.map(v => v[0]);
    Logger.log(colarr.indexOf('значениеДляПоиска'));
}
[/vba]

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

Автор - Gustav
Дата добавления - 27.05.2024 в 18:27
yurakhl Дата: Вторник, 28.05.2024, 08:51 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

2021
https://docs.google.com/spreads....sharing - вот с доступом
 
Ответить
Сообщениеhttps://docs.google.com/spreads....sharing - вот с доступом

Автор - yurakhl
Дата добавления - 28.05.2024 в 08:51
Gustav Дата: Вторник, 28.05.2024, 12:48 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2763
Репутация: 1140 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Вот в таком виде процедура вроде бы отрабатывает как надо (переименовал ее, добавив "2" на конце):
[vba]
Код
function dependList2() {
    let ss = SpreadsheetApp.getActive();
    let sheet = ss.getSheetByName('111')
    let opy = ss.getSheetByName('ОПУ');

    let ar = sheet.getActiveCell();
    let valueToFind = ar.getValue();

    let range = opy.getRange(4, 3, opy.getLastRow(), 1);
    let values = range.getValues();
    // console.log(values);

    let colarr = values.map(v => v[0]);
    let row = colarr.indexOf(valueToFind) + 4; // + 5;
    // console.log(colarr);
    // console.log(row);

    let status = opy.getRange(row, 4, 1, 5).getValues();
    let validation = SpreadsheetApp.newDataValidation().requireValueInList(status[0]).build();
    // console.log(status[0]);

    ar.offset(0, 1).setDataValidation(validation);
}
[/vba]
А как Вы ее собираетесь запускать массово? Не для каждой же ячейки вручную? Видимо, нужен цикл по каким-то особым ячейкам на всех листах WBS*. А что в этих ячейках особенного? Что они находятся в определенных столбцах этих листов?


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеВот в таком виде процедура вроде бы отрабатывает как надо (переименовал ее, добавив "2" на конце):
[vba]
Код
function dependList2() {
    let ss = SpreadsheetApp.getActive();
    let sheet = ss.getSheetByName('111')
    let opy = ss.getSheetByName('ОПУ');

    let ar = sheet.getActiveCell();
    let valueToFind = ar.getValue();

    let range = opy.getRange(4, 3, opy.getLastRow(), 1);
    let values = range.getValues();
    // console.log(values);

    let colarr = values.map(v => v[0]);
    let row = colarr.indexOf(valueToFind) + 4; // + 5;
    // console.log(colarr);
    // console.log(row);

    let status = opy.getRange(row, 4, 1, 5).getValues();
    let validation = SpreadsheetApp.newDataValidation().requireValueInList(status[0]).build();
    // console.log(status[0]);

    ar.offset(0, 1).setDataValidation(validation);
}
[/vba]
А как Вы ее собираетесь запускать массово? Не для каждой же ячейки вручную? Видимо, нужен цикл по каким-то особым ячейкам на всех листах WBS*. А что в этих ячейках особенного? Что они находятся в определенных столбцах этих листов?

Автор - Gustav
Дата добавления - 28.05.2024 в 12:48
yurakhl Дата: Вторник, 28.05.2024, 13:27 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

2021
Да это те колонки где заголовок статус

ну я вижу 2 варианта
либо
1. взять определенные колонки в каждом листе с 7 строки до "ластроу"
пометил в скриншоте
либо
2. Взять колонки, где в 4 строке заголовок "Статус" также с 7 строки и до "ластроу"

а вообще по долгу службы достаточно часто нужно делать зависимые списки, поэтому хотелось бы сделать скрипт более-менее универсальным

+ я не знаю технически сам способ как в других таблицах включать тот скрипт который писал для этой
ну или макрос
К сообщению приложен файл: 7672859.jpg (44.1 Kb)


Сообщение отредактировал yurakhl - Вторник, 28.05.2024, 14:12
 
Ответить
СообщениеДа это те колонки где заголовок статус

ну я вижу 2 варианта
либо
1. взять определенные колонки в каждом листе с 7 строки до "ластроу"
пометил в скриншоте
либо
2. Взять колонки, где в 4 строке заголовок "Статус" также с 7 строки и до "ластроу"

а вообще по долгу службы достаточно часто нужно делать зависимые списки, поэтому хотелось бы сделать скрипт более-менее универсальным

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

Автор - yurakhl
Дата добавления - 28.05.2024 в 13:27
yurakhl Дата: Вторник, 28.05.2024, 13:42 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

2021
а есть способ что то вроде "активации" скрипта
т.е я 1 раз его запускаю, и потом ВСЕГДА когда я что то ввожу в определенную ячейку (стоблец, массив, лист), он автоматически подставляет мне варианты статусов?

вот пример на ютубе (использовал как основу, только там столбцы, а у нас строки)
https://www.youtube.com/watch?v=cT4P7GBht_8&t=99s


Сообщение отредактировал yurakhl - Вторник, 28.05.2024, 14:04
 
Ответить
Сообщениеа есть способ что то вроде "активации" скрипта
т.е я 1 раз его запускаю, и потом ВСЕГДА когда я что то ввожу в определенную ячейку (стоблец, массив, лист), он автоматически подставляет мне варианты статусов?

вот пример на ютубе (использовал как основу, только там столбцы, а у нас строки)
https://www.youtube.com/watch?v=cT4P7GBht_8&t=99s

Автор - yurakhl
Дата добавления - 28.05.2024 в 13:42
Gustav Дата: Вторник, 28.05.2024, 21:31 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2763
Репутация: 1140 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
а есть способ что то вроде "активации" скрипта
т.е я 1 раз его запускаю, и потом ВСЕГДА когда я что то ввожу в определенную ячейку (стоблец, массив, лист), он автоматически подставляет мне варианты статусов?

Eсть предопределенная функция-триггер onEdit. Если она создана в проекте, т.е. в проекте присутствует оформленная функция с таким названием, то она срабатывает, когда пользователь меняет в режиме редактирования (т.е. ручками, не скриптом) содержимое ячейки - любой ячейки на любом листе табличного файла. Т.е. функция срабатывает (запускается), а вот какое конкретное действие (или бездействие) она при этом выполняет - зависит от ее наполнения скриптовым кодом.

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

В вашем случае, как я понимаю, нужно будет проверять имя листа - начинается ли оно с букв "WBS". И если начинается, то далее проверять, попадает ли измененная ячейка в один из "жёлтых" диапазонов на этом листе. И если попадает, то в соседней ячейке справа от редактируемой нужно будет создать/обновить выпадающий список, т.е. фактически вызвать для редактируемой ячейки вышеупомянутую функцию dependList2.


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

Сообщение отредактировал Gustav - Вторник, 28.05.2024, 21:44
 
Ответить
Сообщение
а есть способ что то вроде "активации" скрипта
т.е я 1 раз его запускаю, и потом ВСЕГДА когда я что то ввожу в определенную ячейку (стоблец, массив, лист), он автоматически подставляет мне варианты статусов?

Eсть предопределенная функция-триггер onEdit. Если она создана в проекте, т.е. в проекте присутствует оформленная функция с таким названием, то она срабатывает, когда пользователь меняет в режиме редактирования (т.е. ручками, не скриптом) содержимое ячейки - любой ячейки на любом листе табличного файла. Т.е. функция срабатывает (запускается), а вот какое конкретное действие (или бездействие) она при этом выполняет - зависит от ее наполнения скриптовым кодом.

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

В вашем случае, как я понимаю, нужно будет проверять имя листа - начинается ли оно с букв "WBS". И если начинается, то далее проверять, попадает ли измененная ячейка в один из "жёлтых" диапазонов на этом листе. И если попадает, то в соседней ячейке справа от редактируемой нужно будет создать/обновить выпадающий список, т.е. фактически вызвать для редактируемой ячейки вышеупомянутую функцию dependList2.

Автор - Gustav
Дата добавления - 28.05.2024 в 21:31
yurakhl Дата: Среда, 29.05.2024, 08:49 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

2021
В вашем случае, как я понимаю, нужно будет проверять имя листа - начинается ли оно с букв "WBS". И если начинается, то далее проверять, попадает ли измененная ячейка в один из "жёлтых" диапазонов на этом листе. И если попадает, то в соседней ячейке справа от редактируемой нужно будет создать/обновить выпадающий список, т.е. фактически вызвать для редактируемой ячейки вышеупомянутую функцию dependList2.


да именно!
 
Ответить
Сообщение
В вашем случае, как я понимаю, нужно будет проверять имя листа - начинается ли оно с букв "WBS". И если начинается, то далее проверять, попадает ли измененная ячейка в один из "жёлтых" диапазонов на этом листе. И если попадает, то в соседней ячейке справа от редактируемой нужно будет создать/обновить выпадающий список, т.е. фактически вызвать для редактируемой ячейки вышеупомянутую функцию dependList2.


да именно!

Автор - yurakhl
Дата добавления - 29.05.2024 в 08:49
yurakhl Дата: Среда, 29.05.2024, 10:48 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

2021
Врде бы сам допер))))

теперь вопрос как мне их раскрасить?

просто сделать условное форматирование? прописать все статусы и им какой то формат присвоить?

Цитата

function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();
var editedColumn = range.getColumn();
var editedRow = range.getRow();
var colname = sheet.getRange(4,editedColumn,1,1).getValue();

if(
sheet.getName().match("WBS.") && colname.match('Документы'))

{
dependList2();
}

}
 
Ответить
СообщениеВрде бы сам допер))))

теперь вопрос как мне их раскрасить?

просто сделать условное форматирование? прописать все статусы и им какой то формат присвоить?

Цитата

function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();
var editedColumn = range.getColumn();
var editedRow = range.getRow();
var colname = sheet.getRange(4,editedColumn,1,1).getValue();

if(
sheet.getName().match("WBS.") && colname.match('Документы'))

{
dependList2();
}

}

Автор - yurakhl
Дата добавления - 29.05.2024 в 10:48
yurakhl Дата: Среда, 29.05.2024, 15:38 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

2021
Тоже допер )))

Цитата

var formatcell = opy.getRange('B1') ;
ar.offset(0, 1).clearContent().clearDataValidations();
formatcell.copyTo(ar.offset(0, 1), SpreadsheetApp.CopyPasteType.
PASTE_CONDITIONAL_FORMATTING,false);
 
Ответить
СообщениеТоже допер )))

Цитата

var formatcell = opy.getRange('B1') ;
ar.offset(0, 1).clearContent().clearDataValidations();
formatcell.copyTo(ar.offset(0, 1), SpreadsheetApp.CopyPasteType.
PASTE_CONDITIONAL_FORMATTING,false);

Автор - yurakhl
Дата добавления - 29.05.2024 в 15:38
yurakhl Дата: Среда, 29.05.2024, 16:28 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

2021
Gustav,

Можете подсказать как мне это скрипт как то сохранить чтобы использовать в будущем

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

Можете подсказать как мне это скрипт как то сохранить чтобы использовать в будущем

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

Автор - yurakhl
Дата добавления - 29.05.2024 в 16:28
Gustav Дата: Четверг, 30.05.2024, 14:09 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2763
Репутация: 1140 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
как мне это скрипт как то сохранить чтобы использовать в будущем


https://developers.google.com/apps-script/guides/libraries?hl=ru
https://developers.google.com/apps-script/guides/standalone?hl=ru

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

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


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
как мне это скрипт как то сохранить чтобы использовать в будущем


https://developers.google.com/apps-script/guides/libraries?hl=ru
https://developers.google.com/apps-script/guides/standalone?hl=ru

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

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

Автор - Gustav
Дата добавления - 30.05.2024 в 14:09
  • Страница 1 из 1
  • 1
Поиск:

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