Как создать эквивалент CHOOSE, используя список в ссылке на ячейку

Question

Выбор определенного значения из списка через запятую можно выполнить с помощью функции CHOOSE . Например:

=CHOOSE(A1,"A","B","C","D")

где A1 содержит значение индекса, а "A","B","C","D" - список.

Однако CHOOSE работает только с реальным списком, встроенным в функцию, а не с ссылкой на список.

Предположим, у вас есть ситуация, когда список является динамическим, и создается и хранится в ячейке. Скажем, прямо сейчас ячейка B1 содержит разделенную запятыми строку "A","B","C","D" , и это может быть другой список в другое время.

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

=CHOOSE(A1,CONTENTS(B1))

Есть ли способ сделать эквивалент этого?

• Я ищу общий подход. Список может быть любым и любым количеством терминов, что исключает решение, связанное с определенным количеством элементов, или элементов определенного типа или формата. Он должен обрабатывать общую ситуацию любого списка, соответствующего стандартам CSV. Однако список не будет настолько большим, чтобы превышать какие-либо ограничения Excel.
• Обратите внимание, что это отличается от действия функции INDIRECT, которая не будет работать для этой цели.
• Решение должно вести себя как функция (автоматически обновляться при изменении содержимого). Это исключает решение, требующее ручного вмешательства. Автоматически запускаемое решение VBA не будет исключено, если это единственно возможное решение, но оно нежелательно, поскольку VBA не всегда будет доступен и ограничит возможность переноса решения в другие приложения для работы с электронными таблицами.
• Использование вспомогательного столбца не исключается, но переменная природа данных делает его непрактичным для решения, которое включает в себя анализ списка в отдельные ячейки.

Answer 1

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

Столбец A содержит строку значений для выбора. Столбец B содержит значение индекса для выбора. Столбец C показывает выбранное значение.

Формула

• Поместите значение вхождения в столбец B.

• Запишите эту формулу в ячейку C2, если значения разделены пробелом:

  =TRIM(MID(SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2))),(B2-1)*LEN(A$2)+1,LEN(A$2)))
  

• Вы можете использовать эту формулу в ячейке C9 для значений, разделенных запятыми.

  =TRIM(MID(SUBSTITUTE(A$9,",",REPT(" ",LEN(A$9)),(B9-1)*LEN(A$9)+1,LEN(A$9)))
  

Как это устроено

Этот метод заменяет каждый разделитель пробелами, равными длине всей строки данных. Затем он вычисляет точки останова, которые начинаются с или перед каждым значением, и отсекает отрезок до всей исходной строки. Фрагмент всегда будет содержать целевое значение плюс некоторые отступы, и не будет никакой части какого-либо другого значения. Заполнение обрезается, и у вас остается целевое значение.

Позвольте мне нарушить формулу:

Когда в ячейке вспомогательного столбца B2 есть 3, мы ищем третье значение, Data3 .

LEN(A2) returns:  23

SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2)))  Returns: 

Data1                       Data2                       Data3                       Data4 

Затем:

(B2-1)*LEN(A2)+1 returns:  47  

Это позиция между данными Data2 и Data3, а через 23 символа - позиция после Data3. Функция MID получает этот фрагмент, содержащий отступы, Data3 и другие отступы.

TRIM removes the padding, leaving `Data3`.

Обратите внимание: если значение вспомогательного столбца равно 2, то формула

=(B2-1)*LEN(A2)+1

возвращает 24 вместо 47

и формула

=TRIM(MID(SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2))),24,23))

возвращает Data2 .

NB. При необходимости измените ссылки на ячейки в формуле.

Answer 2

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

Сначала я обнаружил надстройку Excel RegEx Find / Replace , так что используйте ее, но, вероятно, вы можете найти и другие бесплатные инструменты.

С этим самым простым способом было бы использовать эту формулу:
=RegExReplace($A$1,"([^,]*,){"&C1-1&"}([^,]*)(,.*)?","$2")

Как это устроено:

• =RegExReplace($A$1,"([^,]*,){"&C1-1&"}([^,]*)(,.*)?","$2")
• ([^,]*,){"&C1-1&"}
  • ([^,]*,) - соответствует символьной последовательности, не содержащей запятую (,), затем одну запятую после них
    • (также запоминает как группу № 1)
  • {"&C1-1&"} - предыдущая группа C1 минус один раз
  • ([^,]*) - соответствует последовательности символов, не содержащей запятую, также запоминает ее как группу № 2
  • (,.*)? - соответствует запятой, за которой следует любая последовательность символов. (?: эта часть является необязательной, может не существовать, если требуется последний элемент).
  • "$2" - заменяет исходную строку группой, сохраненной как # 2

Подробнее объяснение здесь.

Если input является допустимым индексом, верным элементом будет возвращаемый элемент, а для недопустимого индекса (0 или больше, чем количество элементов) результатом будет вся строка ввода.

Эта формула работает для вашего примера, но она не работает для всех "списков соответствия CSV", как в вашем вопросе. (как и другие ответы на ваши вопросы, как я видел, но это решение может быть легче улучшено, если вам нужно это сделать):

• как формула в моем исходном ответе, она не удаляет кавычки (") вокруг записей, однако это легко решить с помощью другой функции вокруг оригинала:
  =RegExReplace(...,"""(.*)""","$1")
• более сложная часть заключается в управлении разделителями внутри элементов ("first","second,","third") и экранированными разделителями ("quote "" within item")

Answer 3

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

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

Предположим, что ячейка A1 содержит индекс (N), а ячейка B1 содержит список, разделенный запятыми. Стандартный метод определения N-го вхождения символа (в данном случае, запятой):

=FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1))

CHAR(1) - это просто символ, который не будет частью содержимого ячейки. Функция SUBSTITUTE имеет необязательный четвертый параметр, чтобы определить конкретное вхождение для замены. Эта формула заменяет N-е вхождение (ячейка A1) запятой на CHAR(1), а затем находит его.

Первое и последнее значение списка имеют запятую только на одной стороне, поэтому эту формулу необходимо расширить, чтобы использовать функцию MID. Чтобы найти начальное местоположение N-го элемента списка, мы можем использовать:

=IF(A1=1,1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1-1))+1)

Обратите внимание, что отправной точкой для элемента 2 является ссылка на первую запятую, поэтому соответствующая запятая для поиска - N-1. Первый символ предмета - +1 позиция после запятой.

Чтобы найти конечное местоположение последнего элемента списка, мы можем использовать функцию LEN, нам просто нужно определить, что это последний элемент. Количество элементов списка будет на один больше, чем количество разделителей. Мы можем получить количество запятых с:

=LEN(B1)-LEN(SUBSTITUTE(B1,",",""))

Это заменит все запятые нулевой длиной, а затем найдет разницу в длине строки. Разница в количестве запятых.

После указания начальной точки MID использует длину текста, который нужно извлечь. Это достигается путем вычитания начальной точки из позиции после конца элемента списка, которая будет либо позицией следующей запятой, либо, что касается последнего элемента, позиции, где будет следующая запятая, через один символ после длины строка. Таким образом, конечная точка измерения будет:

=IF(A1>(LEN(B1)-LEN(SUBSTITUTE(B1,",",""))),LEN(B1)+1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1)))

Параметр длины определяется путем вычитания начального местоположения элемента из вышеуказанной точки измерения. Объединение всего этого вместе с функцией MID дает:

=MID(B1,IF(A1=1,1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1-1))+1),IF(A1>(LEN(B1)-LEN(SUBSTITUTE(B1,",",""))),LEN(B1)+1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1)))-IF(A1=1,1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1-1))+1))

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

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

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