Вычислить количество общих элементов в ячейках

Question

Я изо всех сил пытаюсь получить формулу calc, чтобы сделать следующие вычисления:

        A B D   G H A
A B C    2     1
A D E    2     1
A F G    1     2
B H I    1     1

Как и в примере, мне нужно, чтобы в формуле сравнивались две ячейки и говорилось, сколько общих элементов они имеют - без указания каких элементов есть в любой из двух ячеек, хотя они всегда являются буквами алфавита, и любые данное письмо никогда не повторяется в одной и той же клетке. Просто для пояснения, пример сравнивает то, что было бы B1 с A2, A3, A4 и A5, и C1 с A2, A3, A4 и A5 соответственно.

Любые идеи о том, как достичь этого с LibreOffice? Спасибо!

Answer 1

Если у вас всегда есть 3 буквы в ячейках в строке 1, вы можете использовать:

=IF(ISNUMBER(SEARCH(MID(B$1;1;1);$A2));1;0) + IF(ISNUMBER(SEARCH(MID(B$1;2;1);$A2));1;0) + IF(ISNUMBER(SEARCH(MID(B$1;3;1);$A2));1;0)

в ячейке B2 и скопируйте / вставьте в другие ячейки ($, чтобы исправить столбцы / строки уже в формуле)

Есть 3 IF, по одному на каждую букву для проверки. Если буква найдена, результатом является число, и IF заменяет его на 1, если не на 0, и добавляется результат 3 IF.

Если в столбце больше букв, добавьте только один IF в формулы этого столбца.

Количество букв в столбце 1 может меняться без каких-либо изменений в формуле.

Если между буквами в строке 1 есть пробел, например, A B D' instead of ABD`, измените 1, 2 и 3 в параметре MID функции 2nd на 1, 3 и 5.

Answer 2

Если у вас всегда есть три буквы в ячейках в строке 1, вы можете использовать:

=SUM(--ISNUMBER(SEARCH(MID(B$1,{1,2,3},1),$A2)))

Это постепенная эволюция ответа Лорана.  Я сделал пару оптимизаций.  Прежде всего,

IF ( Boolean_value , 1 , 0)

немного избыточен, так как TRUE имеет значение 1, а FALSE имеет значение 0.  Логические значения могут быть легко преобразованы в их эквивалентные целые числа с префиксом -- ; сокращение от - ( - ( value ) ) , это сохраняет числовое значение, но приводит тип к целому числу.  И тогда {1,2,3} - это массив, который (с функцией SUM) устраняет необходимость добавления трех почти идентичных терминов.

Для обработки строк в строке 1 различной длины используйте

=SUM(--ISNUMBER(SEARCH(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1),$A2)))

Это похоже на первый ответ, за исключением того, что мы получаем длину строки в верхней части столбца, LEN(B$1) , и соединяем ее со строкой « 1: », образуя что-то вроде « 1:3 ».  Это выглядит как диапазон строк, и функция INDIRECT обрабатывает его именно так, возвращая диапазон адресов $1:$3 .  И тогда ROW() этого возвращает массив {1,2,3} .  Это хитрость для создания массива последовательных чисел, начало и конец которого не определены заранее.

Приведенная выше формула должна быть введена с помощью Ctrl+Shift+Enter.  Я не уверен, почему это необходимо, а первый нет.

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

=SUMPRODUCT(--(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1)<>" "),--ISNUMBER(SEARCH(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1),$A2)))

который также должен быть введен с помощью Ctrl+Shift+Enter.