Выпадающие списки с последующим исключением ранее выбранных элементов

Оригинальное оформление статьи

Электронная таблица

Макет

Для решения поставленной задачи подготовим макет данных:

• Область размещения выпадающих списков, например, в колонке B

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

Для этого в ячейке D1 размещена следующая конструкция

или можно использовать такую конструкцию

Обе конструкции генерируют массив, состоящий из заголовка Исходный и набора компонентов выпадающего списка из 20 элементов

( Элемент 1,  Элемент 2,  Элемент 3,  ..., Элемент 20 )

• Остаточный массив элементов, который динамически будет исключать ранее выбранные элементы. На этот диапазон ссылаются все выпадающие списки в колонке B. Как видно на скрине ниже, в массиве остаточных элементов отсутствуют выбранные элементы ( Элемент 1,  Элемент 3,  Элемент 6 ).

Для формирования массива остаточных элементов будут применены несколько вариантов решения с использованием встроенных формул / именованных диапазонов / именованных функций / пользовательской функции. Последний вариант описывает решения задачи сугубо посредством встроенного языка разработки Google Apps Script.

Вариант I. Классический. Ссылки на диапазоны.

В ячейке E1 размещена следующая конструкция итогового массива

"Остаточный" - первая часть, заголовок остаточного массива.

FILTER( ЕСЛИ( СЧЁТЕСЛИ( .…. - вторая, основная часть. Динамически определяет остаточный массив с еще не выбранными элементами.

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

конструкция определяет, сколько раз встречается выбранный элемент в колонке B ( с размещенными выпадающими списками  ). Так как Исходный массив содержит полный список элементов выпадающих списков, конструкция выше выдаст 1 для всех выбранных позиций, для остальных позиций выдаст 0. Далее, с помощью логической обертки ЕСЛИ выводим пробел вместо единицы и оставшиеся элементы вместо нуля. Далее с помощью функции FILTER отсеиваем пустые элементы, получив на выходе список остаточных элементов.

Следует отметить, в области Промежуточные вычисления все конструкции отрабатывают только в контексте обработки массива данных, применена формула ArrayFormula. Однако же, при использовании функции FILTER в этом нет необходимости, так как данная функция оперирует в том числе и массивами, аналогично, например, функции СУММПРОИЗВ.

Вариант I-a. Классический, с использованием переменных.

Тот же способ, но с предварительным определением переменных.

Например, если область выпадающих списков небольшая, в несколько десятков строк, но сами листы содержат огромное количество строк. В таком случае, есть смысл жестко определять границы рабочих диапазонов выпадающих списков.  Для этих целей нужно найти последнюю строку ( 21 ) с заполненными данными, например, в диапазоне Исходный:

следующая конструкция  = "$D$2:$Dquot; & $G$3 образовывает диапазон $D$2:$D$21

Далее, используя ту же логику что описана выше, вместо прописывания вручную рабочих диапазонов можно ссылаться на предварительно определенные границы диапазонов с помощью функции ДВССЫЛ:

Вариант I-b. Именованные диапазоны

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

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

Вариант I-c. Именованные диапазоны + LET + ARRAYFORMULA

В следующем варианте использована одна из относительно новых функций в гугл-таблицах - LET.

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

Улучшенная производительность. Если вы записываете одно и то же выражение в формулу несколько раз, происходит вычисление этого результата несколько раз. LET позволяет вычислять выражение один раз и вызывать его по имени.

Простота чтения и компоновки. Больше не нужно помнить, что делал ваш расчет, к чему относится ссылка на конкретный диапазон/ячейку или копировать/вставлять одно и то же выражение. Имея возможность объявлять и называть переменные, вы можете дать значимый контекст себе и другим пользователям вашей формулы.

Схематично работу этой функции можно представить так:

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

Применяя данную функция к текущей задаче, обновленная формула будет более краткой и читаемой.

rngRez - переменная, объявленная внутри функции, содержит в себе результат вычисления в следующей части ArrayFormula( ЕСЛИ( СЧЁТЕСЛИ( 'Вар I-c'! Рабочий_Ic; 'Вар I-c'! Исходный_Ic ) < 1;   'Вар I-c'! Исходный_Ic; "" ) ) и представляет собой итоговый массив элементов, в котором ранее выбранные элементы заменены на пробелы. В дальнейшем из итоговой переменной-массива rngRez отсеиваются пробелы посредством функции FILTER(  rngRez; rngRez <>"" ).

Вариант I-d. Именованные функции

При реализации наиболее изящного варианта стоит применить возможности именованных функций - специальной обертки для базовой конструкции из формул. Инструмент доступен по адресу: Данные - Σ Именованные функции.

Базовая конструкция из формул взята из первого классического Варианта I:

Название функции определим следующее - RESIDUE_DROPDOWN.

Аргументами функции будут:

rngDropDownSel - диапазон, в котором размещены выпадающие списки

rngDropDownList - диапазон, в котором размещен полный набор элементов выпадающих списков

В поле Описание функции необходимо добавить краткое изложение предназначения функции.

В поле Определение формулы размещается базовая конструкция из формул.

После вставки формулы сразу же появляются рекомендованные аргументы, нажав на которые  нужно произвести определение.  Например, для первого аргумента

$B$2:$B$30 - указать наименование rngDropDownSel

Аналогично нужно произвести определение для всех аргументов. На следующем этапе можно ( но не обязательно ) задать описание и пример аргумента.

Заполненное окно данных Именованной функции

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

Именованным функциям также следует уделить особое внимание, поскольку их применение существенно упрощает итоговую конструкцию в строке формул:

Именованные функции можно использовать с именованными диапазонами

После создания функции RESIDUE_DROPDOWN для получения подробного описания ее функционала следует активировать ее в строке формул с помощью ЛКМ и затем нажать знак вопроса

Вариант II. Именованные диапазоны + ЕНД + ПОИСКПОЗ

Второй вариант решения более оптимален с точки зрения краткости итоговой формулы:

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

- данный участок в общей конструкции формул формирует список выбранных элементов

- следующая конструкция “ищет” элементы, сформированные на предыдущем этапе в Исходном массиве и присваивает номер найденной позиции в новом массиве. Если позиция не найдена - отображается ошибка #Н/Д - нет данных.

На следующем этапе -

происходит перехват ошибки #Н/Д с помощью функции ЕНД. Данная функция проверяет, является ли значение ошибкой, в положительном ключе отображает ИСТИНА, в противном случае - ЛОЖЬ.

- на последнем этапе функция FILTER отображает только те элементы в Исходном массиве, с которыми сопряжен статус ИСТИНА на предыдущем этапе. Данная функциональность предусмотрена разработчиками функции FILTER. Выдержка из справки:

условие_2… - [необязательный] повторяющийся

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

Второй вариант решения можно использовать в различных комбинациях с применением тех же дополнительных инструментов что были рассмотрены ранее в Варианте I:ДВССЫЛ / именованные диапазоны / именованные функции / LET

Вариант III. Google Apps Script. Custom Function

Остаточный массив элементов можно также получить с помощью кастомной (пользовательской) функции написанной на языке Google Apps Script. Код данной функции размещен в контейнере документа:

Расширения => Apps Script => CustFunction.js

/**
* Функция определяет количество остаточных элементов раскрывающихся списков.
*
* @param { $B$2:$B$30 } rngDrDownSel Диапазон в котором размещены выпадающие списки
* @param { $D$2:$D$30 } rngDrDownList Диапазон в котором размещен полный набор элементов выпадающих списков
* @return Остаточное количество элементов выпадающих списков с учетом ранее выбранных элементов
* @customfunction
*/
function RESIDUE_DROPDOWN_GAS( rngDrDownSel, rngDrDownList ) {
    
    const rngDrDownSelType = typeof rngDrDownSel;
    const rngDrDownListType = typeof rngDrDownList;
    
    // если введены аргументы с типом данных отличным от типа "object" функция результат не выдает
    if ( rngDrDownSelType !== "object" || rngDrDownListType !== "object" ) return;
    
    const arrRngDrDownSel = rngDrDownSel.filter( el => el != '' ).flat();
    const arrRngDrDownList = rngDrDownList.filter( el => el != '' );
    
    if ( arrRngDrDownSel.length === 0 ) return arrRngDrDownList;
    
    for ( let i = arrRngDrDownList.length - 1; i >= 0; i -- ) {
        if ( arrRngDrDownSel.includes( arrRngDrDownList[i][0] ) ) arrRngDrDownList.splice( i, 1 );
    }
    
    return arrRngDrDownList;
};

Функция RESIDUE_DROPDOWN_GAS:

на входе получает два аргумента:

• Аргумент 1. Диапазон, в котором размещены выпадающие списки;
• Аргумент 2. Диапазон, в котором размещены выпадающие списки;

на выходе формирует остаточное количество элементов выпадающих списков с учетом ранее выбранных элементов

В функции предусмотрена проверка типа данных введенных аргументов. Если тип данных не соответствует типу object, функция возвращает пустое значение.

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

Вариант III. Google Apps Script.

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

Для реализации задачи на языке Google Apps Script необходимо настроить выполнение инструкций в комплексе с триггерными функциями. Программный код скрипта размещен в контейнере документа:

Расширения => Apps Script => Script.js

Далее следует краткое описание блоков программного кода в соответствующей последовательности.

1. Определение констант

const sprApp = SpreadsheetApp;
const spreadsheet = sprApp.getActive();
const shName_GAS = 'Вар III GAS';
const shGAS = spreadsheet.getSheetByName( shName_GAS );
const arrRngListSel = [ 'B2', 'B4', 'B6', 'B8', 'B10', 'B12', 'B14', 'B16', 'B18', 'B20' ];
const rngListSel = shGAS.getRangeList( arrRngListSel );

2. Предварительные функции

// функция предварительной очистки ранее выбранных значений выпадающих списков
function clearDropDownList() { rngListSel.clearContent() }

// функция установки правил проверки - раскрывающихся списков
function setRuleForDropDowns() {
    const rngListInit = shGAS.getRange( 'D2:D' );
    const rule = sprApp.newDataValidation()
                 .requireValueInRange( rngListInit, true )
                 .setAllowInvalid( false )
                 .build();
    arrRngListSel.map( rngVal => shGAS.getRange( rngVal ).setDataValidation( rule ) );
};

3. Триггер-функция onOpen(e)

// триггер-функция срабатывает при запуске электронной таблицы
function onOpen(e) {
    clearDropDownList();
    setRuleForDropDowns();
}

При открытии документа будут выполнены последовательно функции:

clearDropDownList - очистка выпадающих списков, если таковые были заполнены ранее;

setRuleForDropDowns - установка правил проверки данных для выпадающих списков;

4. Основная функция

// функция определения остаточных элементов раскрывающихся списков
function setRuleForDropDownsWithResidItems() {
    
    const arrRngListSelVals = arrRngListSel.map( rngVal => shGAS.getRange( rngVal ).getValue() );
    
    if ( arrRngListSelVals.every( evr => evr === '' ) ) return setRuleForDropDowns();
    
    const rngListInit = shGAS.getRange( 'D2:D' );
    const arrRngListInitVals = rngListInit.getValues().filter( e => e != '' );
    
    for ( let i = arrRngListInitVals.length - 1; i >= 0; i -- ) {
        if ( arrRngListSelVals.includes( arrRngListInitVals[i][0] ) ) arrRngListInitVals.splice( i, 1 );
    }
    
    const rule = sprApp.newDataValidation()
                 .requireValueInList( arrRngListInitVals.flat(), true )
                 .setAllowInvalid( false )
                 .build();
    
    arrRngListSel.map( rngVal => shGAS.getRange( rngVal ).setDataValidation( rule ) );
};

5. Триггер-функция onEdit(e)

// триггер-функция срабатывает при редактировании ячеек с выпадающими списками
function onEdit(e) {
    
    const edRange = e.range
    const sheet = e.source.getActiveSheet();
    const sheetName = sheet.getSheetName();
    
    if ( sheetName !== shName_GAS ) return;
    
    const edRangeRow = edRange.getRow();
    const edRangeCol = edRange.getColumn();
    
    if ( edRangeCol !== 2 || edRangeRow < 2 || edRangeRow > 20 ) return;
    
    const edRangeA1Note = edRange.getA1Notation();
    
    if ( ! arrRngListSel.includes( edRangeA1Note ) ) return;
    setRuleForDropDownsWithResidItems();
}

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

[ 'B2', 'B4', 'B6', 'B8', 'B10', 'B12', 'B14', 'B16', 'B18', 'B20' ];

Для “возобновления”  полного набора элементов в выпадающих списках следует производить очистку (или удаление данных) также строго в пределах рабочей области выпадающих списков:

[ 'B2', 'B4', 'B6', 'B8', 'B10', 'B12', 'B14', 'B16', 'B18', 'B20' ];

ПЛЮСЫ

Гибкость.

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

МИНУСЫ

Отсутствует возможность программного изменения стилей.

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

/* На момент написания скрипта еще не предусмотрена возможность изменения стилей ( чип,   стрелка, обычный текст ) выпадающих списков */

Решение по умолчанию воспроизводит стрелочный стиль

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

Скорость выполнения.

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