Блог

Главная - Вопросы по программированию - Формула Excel 365 для динамического раскрывающегося списка на основе уникальных данных из двух или более отдельных столбцов

Формула Excel 365 для динамического раскрывающегося списка на основе уникальных данных из двух или более отдельных столбцов

#excel-formula

Вопрос:

Я пытаюсь создать раскрывающееся меню Excel 365 на основе содержимого двух или более столбцов данных без сохранения промежуточного результата в электронной таблице, но исключительно с помощью формулы в поле Источник проверки данных. Я успешно делал это в прошлом, и в Интернете есть много ответов, но, похоже, в Office 365 что-то изменилось, и старые трюки больше не работают.

Добавление нескольких столбцов с источниками является тривиальным и не является основным здесь.

Для полноты данных столбцы данных содержат сотни имен (иногда дубликатов) или являются пустыми.

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

В настоящее время я могу объединить два диапазона (здесь B8:B29 и D8:D29), сортировать и извлекать уникальные значения в список разливов, используя эту формулу:

 =LET(r1g;'Sheet 1'!B8:B29;r2g;'Sheet 1'!D8:D29;r1s;ROWS(r1g);r2s;ROWS(r2g);c1s;COLUMNS(r1g);rix;SEQUENCE(r1s r2s);cix;SEQUENCE(1;c1s);LLL;IF(rix<=r1s;INDEX(r1gamp;"";rix;cix);INDEX(r2gamp;"";rix-r1s;cix));SORT(UNIQUE(LLL);;;FALSE))

(Вот где я действительно скучаю по Google листам {Лист 1′!B8:B29; ‘Лист 1’!D8:D29}

Чтобы сделать формулу немного более читаемой, вот версия, в которой формула разбита на разделы (однако эта версия не работает в качестве источника проверки данных из-за новых строк).:

 =LET(
r1g;'Sheet 1'!B8:B29;
r2g;'Sheet 1'!D8:D29;
r1s;ROWS(r1g);
r2s;ROWS(r2g);
c1s;COLUMNS(r1g);
rix;SEQUENCE(r1s r2s);
cix;SEQUENCE(1;c1s);
LLL;IF(rix<=r1s;INDEX(r1gamp;"";rix;cix);INDEX(r2gamp;"";rix-r1s;cix));
  SORT(
    UNIQUE(LLL)
    ;;;FALSE
  )
)

Размещение приведенной выше формулы в F8 создает хороший список, который затем можно использовать в качестве источника проверки данных, например ='Sheet 1'!$F$8#

Однако размещение всей формулы в качестве источника проверки данных приводит к ошибке: «Источник в настоящее время оценивается как ошибка» — и никакой дополнительной информации о том, какая ошибка. 🙁

From the past I remember something similar, and reading online I find that an OFFSET-COUNTA combination is required to trick Data Validation into accepting dynamically changing data as a source testing with the below in L8, produces an identical list, which can also be used as a Data Validation source using ='Sheet 1'!$L$8# 

 =OFFSET($F$8#;;;COUNTA($F$8#))

However, wrapping the entire formula from the top, in a spreadsheet cell (let’s say O8) like this:

 =OFFSET(LET(r1g;B8:B29;r2g;D8:D29;r1s;ROWS(r1g);r2s;ROWS(r2g);c1s;COLUMNS(r1g);rix;SEQUENCE(r1s r2s);cix;SEQUENCE(1;c1s);LLL;IF(rix<=r1s;INDEX(r1gamp;"";rix;cix);INDEX(r2gamp;"";rix-r1s;cix));
SORT(UNIQUE(LLL);;;FALSE));;;COUNTA(LLL))

produces the correct amount of data in the cells spill range, but they all have the value #VALUE and a green note attached — but no info when hovering on it.

If I try with #’s like the original wrapping, like below, I only get one #VALUE as output.

 =OFFSET(LET(r1g;B8:B29;r2g;D8:D29;r1s;ROWS(r1g);r2s;ROWS(r2g);c1s;COLUMNS(r1g);rix;SEQUENCE(r1s r2s);cix;SEQUENCE(1;c1s);LLL;IF(rix<=r1s;INDEX(r1gamp;"";rix;cix);INDEX(r2gamp;"";rix-r1s;cix));
SORT(UNIQUE(LLL#);;;FALSE));;;COUNTA(LLL#))

Needless to say, neither of the two formulas above are functioning as Data Validation sources, where they both produce the error «A named range you specified can not be found».

I’ve tried a number of things to resolve this, and have no luck so far.

So my questions are:

  1. Как я могу обернуть свою текущую формулу для использования в качестве формулы источника проверки данных?
  2. Если #1 невозможно, как еще я могу это сделать, не храня дополнительные данные в электронной таблице?

Правка: Добавлена форматированная версия основной формулы слияния.

Метки: