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

Вход

Регистрация

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

 

= Мир MS Excel/Построение матрицы сходства растений - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Построение матрицы сходства растений (Формулы/Formulas)
Построение матрицы сходства растений
oscillat8 Дата: Вторник, 14.03.2023, 17:27 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Друзья, нужна помощь.

ЗАДАЧА: Многократно сравнивать разные длинные списки видов растений попарно между собой по типу: "Вставил перечни видов в Лист 1, а на Листе 2 матрица нарисовалась".
Есть документ с двумя листами:
Лист 1-списки разных видов растений (столбцы);
Лист 2-квадратная матрица коэффициентов сходства каждого столбца с каждым другим стобцом (т.е. сколько общих видов между первым 1ым и 2ом стобцом, 1ым и 3им, 2ым и 3им и т.д. ).

ПРОБЛЕМА: Я прописал в каждую ячейку матрицы какой с каким столбцом должен попарно сравниваться. В принципе, всё работает. Но процесс вписывания весьма утомительный, а размер матрицы получается ограниченный (в силу утомительности).

ВОПРОС: Как автоматизировать процесс заполнения матрицы, чтобы коэффициенты сходства после попарного сравнивания столбцов вписывались в нужную ячейку матрицы, а матрица могла быть очень большой?
К сообщению приложен файл: matrix_builder.xlsx (66.0 Kb)


Сообщение отредактировал oscillat8 - Вторник, 14.03.2023, 17:28
 
Ответить
СообщениеДрузья, нужна помощь.

ЗАДАЧА: Многократно сравнивать разные длинные списки видов растений попарно между собой по типу: "Вставил перечни видов в Лист 1, а на Листе 2 матрица нарисовалась".
Есть документ с двумя листами:
Лист 1-списки разных видов растений (столбцы);
Лист 2-квадратная матрица коэффициентов сходства каждого столбца с каждым другим стобцом (т.е. сколько общих видов между первым 1ым и 2ом стобцом, 1ым и 3им, 2ым и 3им и т.д. ).

ПРОБЛЕМА: Я прописал в каждую ячейку матрицы какой с каким столбцом должен попарно сравниваться. В принципе, всё работает. Но процесс вписывания весьма утомительный, а размер матрицы получается ограниченный (в силу утомительности).

ВОПРОС: Как автоматизировать процесс заполнения матрицы, чтобы коэффициенты сходства после попарного сравнивания столбцов вписывались в нужную ячейку матрицы, а матрица могла быть очень большой?

Автор - oscillat8
Дата добавления - 14.03.2023 в 17:27
NikitaDvorets Дата: Четверг, 16.03.2023, 09:58 | Сообщение № 2
Группа: Авторы
Ранг: Ветеран
Сообщений: 548
Репутация: 123 ±
Замечаний: 0% ±

Excel 2019
oscillat8, добрый день.
Поясните, пожалуйста, формулу:
Код
{=2*СУММ(СЧЁТЕСЛИ('Lists of species'!B3:B10000;'Lists of species'!C3:C10000))/(СЧЁТЗ('Lists of species'!B3:B10000)+СЧЁТЗ('Lists of species'!C3:C10000))}

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


Сообщение отредактировал NikitaDvorets - Четверг, 16.03.2023, 22:17
 
Ответить
Сообщениеoscillat8, добрый день.
Поясните, пожалуйста, формулу:
Код
{=2*СУММ(СЧЁТЕСЛИ('Lists of species'!B3:B10000;'Lists of species'!C3:C10000))/(СЧЁТЗ('Lists of species'!B3:B10000)+СЧЁТЗ('Lists of species'!C3:C10000))}

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

Автор - NikitaDvorets
Дата добавления - 16.03.2023 в 09:58
oscillat8 Дата: Пятница, 17.03.2023, 11:17 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Цитата NikitaDvorets, 16.03.2023 в 09:58, в сообщении № 2 ()
Поясните, пожалуйста, формулу:


Да, конечно. Это общеупотребимая формула флористического сходства Сёренсена (она же Сёренсена-Чекановского, она же Dice)
x=2n/a+b, где n-число одних и тех же, т.е. общих видов растений для двух списков, а-кол-во видов растений в первом списке, b-кол-во видов растений во втором списке.

В нашем случае числитель - мы соотносим все идентичные ячейки из листа 'Lists of species' из столбца B с идентичными ячейками того же листа, но столбца C. Затем суммируем общее кол-во совпадающих пар и умножаем на 2.
А знаменатель-сумма всех элементов (непустых ячеек) в столбцах В и С.
 
Ответить
Сообщение
Цитата NikitaDvorets, 16.03.2023 в 09:58, в сообщении № 2 ()
Поясните, пожалуйста, формулу:


Да, конечно. Это общеупотребимая формула флористического сходства Сёренсена (она же Сёренсена-Чекановского, она же Dice)
x=2n/a+b, где n-число одних и тех же, т.е. общих видов растений для двух списков, а-кол-во видов растений в первом списке, b-кол-во видов растений во втором списке.

В нашем случае числитель - мы соотносим все идентичные ячейки из листа 'Lists of species' из столбца B с идентичными ячейками того же листа, но столбца C. Затем суммируем общее кол-во совпадающих пар и умножаем на 2.
А знаменатель-сумма всех элементов (непустых ячеек) в столбцах В и С.

Автор - oscillat8
Дата добавления - 17.03.2023 в 11:17
NikitaDvorets Дата: Пятница, 17.03.2023, 11:44 | Сообщение № 4
Группа: Авторы
Ранг: Ветеран
Сообщений: 548
Репутация: 123 ±
Замечаний: 0% ±

Excel 2019
oscillat8, понятно, благодарю.
Прилагаю проект решения - результат (только) работы макроса.
К сообщению приложен файл: matrica_skhodstva_rastenij_17_.xlsm (57.8 Kb)
 
Ответить
Сообщениеoscillat8, понятно, благодарю.
Прилагаю проект решения - результат (только) работы макроса.

Автор - NikitaDvorets
Дата добавления - 17.03.2023 в 11:44
Gustav Дата: Пятница, 17.03.2023, 13:24 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Получил те же результаты, что и NikitaDvorets в сообщении № 4, с помощью протягиваемой формулы массива - см. в прилагаемом файле на листе "Sorensen (Dice) (2)". Для ячейки B3 этого листа формула выглядит так:

Код
=2*СУММ(СЧЁТЕСЛИ(ИНДЕКС('Lists of species'!$A$2:$H$10000;0;B$1); ИНДЕКС('Lists of species'!$A$2:$H$10000;0;$A3))) / (СЧЁТЗ(ИНДЕКС('Lists of species'!$A$2:$H$10000;0;B$1)) + СЧЁТЗ(ИНДЕКС('Lists of species'!$A$2:$H$10000;0;$A3)))

Формула - массивная, т.е. с Ctrl+Shift+Enter. Не знаю, с какой строки листа "Lists of species" правильно начинать используемые диапазоны - с 2-й или с 3-й. Со 2-й показалось логичнее - её и воплотил в формуле. Но исправить недолго.
К сообщению приложен файл: matrica_skhodstva_rastenij_17.xlsm (65.2 Kb)


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

Сообщение отредактировал Gustav - Пятница, 17.03.2023, 13:33
 
Ответить
СообщениеПолучил те же результаты, что и NikitaDvorets в сообщении № 4, с помощью протягиваемой формулы массива - см. в прилагаемом файле на листе "Sorensen (Dice) (2)". Для ячейки B3 этого листа формула выглядит так:

Код
=2*СУММ(СЧЁТЕСЛИ(ИНДЕКС('Lists of species'!$A$2:$H$10000;0;B$1); ИНДЕКС('Lists of species'!$A$2:$H$10000;0;$A3))) / (СЧЁТЗ(ИНДЕКС('Lists of species'!$A$2:$H$10000;0;B$1)) + СЧЁТЗ(ИНДЕКС('Lists of species'!$A$2:$H$10000;0;$A3)))

Формула - массивная, т.е. с Ctrl+Shift+Enter. Не знаю, с какой строки листа "Lists of species" правильно начинать используемые диапазоны - с 2-й или с 3-й. Со 2-й показалось логичнее - её и воплотил в формуле. Но исправить недолго.

Автор - Gustav
Дата добавления - 17.03.2023 в 13:24
NikitaDvorets Дата: Пятница, 17.03.2023, 13:46 | Сообщение № 6
Группа: Авторы
Ранг: Ветеран
Сообщений: 548
Репутация: 123 ±
Замечаний: 0% ±

Excel 2019
Цитата
Формула - массивная


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


Интересный подход) Осталось уточнить максимальный размер матрицы.

Автор - NikitaDvorets
Дата добавления - 17.03.2023 в 13:46
Gustav Дата: Пятница, 17.03.2023, 15:01 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
В новомодних версиях Excel (для Microsoft 365, для Интернета, 2021) с помощью функции LET можно оформить очень прозрачную расчетную формулу (для той же ячейки B3):

Код
=LET(диапазон; 'Lists of species'!$A$2:$H$10000; столбец; ИНДЕКС(диапазон;0;B$1); строка; ИНДЕКС(диапазон;0;$A3); N; СУММ(СЧЁТЕСЛИ(столбец; строка)); A; СЧЁТЗ(столбец); B; СЧЁТЗ(строка); 2*N / (A+B))


И с другим тэгом - для большей наглядности:
[vba]
Код
=LET(
диапазон; 'Lists of species'!$A$2:$H$10000;
столбец;  ИНДЕКС(диапазон;0;B$1);
строка;   ИНДЕКС(диапазон;0;$A3);
N;        СУММ(СЧЁТЕСЛИ(столбец; строка));
A;        СЧЁТЗ(столбец);
B;        СЧЁТЗ(строка);

2*N / (A+B)
)
[/vba]
Можно вставлять в ячейку прямо именно в таком (втором) виде: пробелы и переносы не мешают, а, наоборот, способствуют.

Интересно, что эту формулу с LET не надо вставлять как формулу массива. По крайней мере, в имеющейся у меня в доступе корпоративной Excel для Интернета, где я сочинял эту формулу, этого делать не пришлось - ввелась при помощи обычного завершения ввода по Enter.

И в этой версии Excel, похоже, вообще не присутствует такого понятия как формула массива, во всяком случае комбинация Ctrl+Shift+Enter никак себя не проявляет и ни к чему не приводит... Что-то подобное происходит в "обычной" Excel при вводе формулы для условного форматирования или для настройки "Проверки данных". Т.е. формула на рабочем листе - массивная, а при вводе в УФ или ПД становится "обычной", потому что как массивную её туда просто не ввести. Но после ввода в УФ или ПД она функционирует там именно как массивная - Excel как бы неявно сама добавляет "массивность" к этой формуле.


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

Сообщение отредактировал Gustav - Пятница, 17.03.2023, 15:31
 
Ответить
СообщениеВ новомодних версиях Excel (для Microsoft 365, для Интернета, 2021) с помощью функции LET можно оформить очень прозрачную расчетную формулу (для той же ячейки B3):

Код
=LET(диапазон; 'Lists of species'!$A$2:$H$10000; столбец; ИНДЕКС(диапазон;0;B$1); строка; ИНДЕКС(диапазон;0;$A3); N; СУММ(СЧЁТЕСЛИ(столбец; строка)); A; СЧЁТЗ(столбец); B; СЧЁТЗ(строка); 2*N / (A+B))


И с другим тэгом - для большей наглядности:
[vba]
Код
=LET(
диапазон; 'Lists of species'!$A$2:$H$10000;
столбец;  ИНДЕКС(диапазон;0;B$1);
строка;   ИНДЕКС(диапазон;0;$A3);
N;        СУММ(СЧЁТЕСЛИ(столбец; строка));
A;        СЧЁТЗ(столбец);
B;        СЧЁТЗ(строка);

2*N / (A+B)
)
[/vba]
Можно вставлять в ячейку прямо именно в таком (втором) виде: пробелы и переносы не мешают, а, наоборот, способствуют.

Интересно, что эту формулу с LET не надо вставлять как формулу массива. По крайней мере, в имеющейся у меня в доступе корпоративной Excel для Интернета, где я сочинял эту формулу, этого делать не пришлось - ввелась при помощи обычного завершения ввода по Enter.

И в этой версии Excel, похоже, вообще не присутствует такого понятия как формула массива, во всяком случае комбинация Ctrl+Shift+Enter никак себя не проявляет и ни к чему не приводит... Что-то подобное происходит в "обычной" Excel при вводе формулы для условного форматирования или для настройки "Проверки данных". Т.е. формула на рабочем листе - массивная, а при вводе в УФ или ПД становится "обычной", потому что как массивную её туда просто не ввести. Но после ввода в УФ или ПД она функционирует там именно как массивная - Excel как бы неявно сама добавляет "массивность" к этой формуле.

Автор - Gustav
Дата добавления - 17.03.2023 в 15:01
Gustav Дата: Пятница, 17.03.2023, 16:44 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
В новомодних версиях Excel (для Microsoft 365, для Интернета, 2021)

Ну, и, наконец, чума от слова "совсем" - созданная в версии "Excel для Интернета" мегаформула массива (ах, всё-таки! правда, с помощью функции), вводимая в одну-единственную ячейку B2 листа "Sorensen (Dice) (2)". И, собственно, на этом всё: "единички" главной диагонали, пустые ячейки выше диагонали, рассчитанные значения ниже диагонали - всё универсально вычисляется этой одно-единственной мегаформулой. Во все остальные ячейки диапазона B2:I9 ничего вводить не надо, они должны быть полностью очищены перед вводом формулы в B2 (иначе в B2 возникнет ошибка #ПЕРЕНОС!). Сознательно использую тэг VBA для более прозрачной демонстрации формулы в виде, облегчающем ее анализ:

[vba]
Код
=MAKEARRAY(ЧСТРОК(A2:A9); ЧИСЛСТОЛБ(B1:I1);
LAMBDA(row; col;
ЕСЛИМН(
row = col; 1;
row < col; "";
row > col; LET(
диапазон; 'Lists of species'!$A$2:$H$10000;
столбец;  ИНДЕКС(диапазон; 0; col);
строка;   ИНДЕКС(диапазон; 0; row);
N;        СУММ(СЧЁТЕСЛИ(столбец; строка));
A;        СЧЁТЗ(столбец);
B;        СЧЁТЗ(строка);

2*N / (A+B)
)
)))
[/vba]


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
В новомодних версиях Excel (для Microsoft 365, для Интернета, 2021)

Ну, и, наконец, чума от слова "совсем" - созданная в версии "Excel для Интернета" мегаформула массива (ах, всё-таки! правда, с помощью функции), вводимая в одну-единственную ячейку B2 листа "Sorensen (Dice) (2)". И, собственно, на этом всё: "единички" главной диагонали, пустые ячейки выше диагонали, рассчитанные значения ниже диагонали - всё универсально вычисляется этой одно-единственной мегаформулой. Во все остальные ячейки диапазона B2:I9 ничего вводить не надо, они должны быть полностью очищены перед вводом формулы в B2 (иначе в B2 возникнет ошибка #ПЕРЕНОС!). Сознательно использую тэг VBA для более прозрачной демонстрации формулы в виде, облегчающем ее анализ:

[vba]
Код
=MAKEARRAY(ЧСТРОК(A2:A9); ЧИСЛСТОЛБ(B1:I1);
LAMBDA(row; col;
ЕСЛИМН(
row = col; 1;
row < col; "";
row > col; LET(
диапазон; 'Lists of species'!$A$2:$H$10000;
столбец;  ИНДЕКС(диапазон; 0; col);
строка;   ИНДЕКС(диапазон; 0; row);
N;        СУММ(СЧЁТЕСЛИ(столбец; строка));
A;        СЧЁТЗ(столбец);
B;        СЧЁТЗ(строка);

2*N / (A+B)
)
)))
[/vba]

Автор - Gustav
Дата добавления - 17.03.2023 в 16:44
Serge_007 Дата: Пятница, 17.03.2023, 16:47 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
в этой версии Excel, похоже, вообще не присутствует такого понятия как формула массива
В новых версиях формулы массива уже не вводятся Ctrl+Shift+Enter, хотя само понятие осталось


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
в этой версии Excel, похоже, вообще не присутствует такого понятия как формула массива
В новых версиях формулы массива уже не вводятся Ctrl+Shift+Enter, хотя само понятие осталось

Автор - Serge_007
Дата добавления - 17.03.2023 в 16:47
Gustav Дата: Пятница, 17.03.2023, 18:14 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
уже не вводятся Ctrl+Shift+Enter

В принципе, да, но с некоторыми оговорками. Вот надёргал фирменных цитат по теме:

Цитата от Microsoft, здесь ()
WINDOWS

Начиная с обновления для Microsoft 365 за сентябрь 2018 г., любая формула, которая может возвращать несколько результатов, автоматически переносит их вниз или в соседние ячейки. Это изменение поведения также сопровождается несколькими новыми функциями динамического массива. Формулы динамического массива, независимо от того, используют ли они существующие функции или функции динамического массива, необходимо вводить только в одну ячейку, а затем подтверждать, нажав ВВОД. Ранее в устаревших формулах массива сначала требовалось выбрать весь выходной диапазон, а затем подтвердить формулу с помощью CTRL+SHIFT+ВВОД. Их часто называют формулами CSE (т.е. Ctrl Shift Enter).

Если у вас есть текущая версия Microsoft 365, можно просто ввести формулу в левую верхнюю ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы.

ИНТЕРНЕТ

В Excel в Интернете вы можете просмотреть формулы массива, если они уже есть в открытой книге. Но вы не сможете создать формулу массива в этой версии Excel, нажав клавиши CTRL+SHIFT+ВВОД, которые вставляют формулу между парой открывающих и закрывающих фигурных скобок ({ }). Ввод фигурных скобок вручную также не превращает формулу в формулу массива.

Если у вас есть Excel, нажмите кнопку "Открыть в Excel", чтобы открыть книгу и создать формулу массива.


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
уже не вводятся Ctrl+Shift+Enter

В принципе, да, но с некоторыми оговорками. Вот надёргал фирменных цитат по теме:

Цитата от Microsoft, здесь ()
WINDOWS

Начиная с обновления для Microsoft 365 за сентябрь 2018 г., любая формула, которая может возвращать несколько результатов, автоматически переносит их вниз или в соседние ячейки. Это изменение поведения также сопровождается несколькими новыми функциями динамического массива. Формулы динамического массива, независимо от того, используют ли они существующие функции или функции динамического массива, необходимо вводить только в одну ячейку, а затем подтверждать, нажав ВВОД. Ранее в устаревших формулах массива сначала требовалось выбрать весь выходной диапазон, а затем подтвердить формулу с помощью CTRL+SHIFT+ВВОД. Их часто называют формулами CSE (т.е. Ctrl Shift Enter).

Если у вас есть текущая версия Microsoft 365, можно просто ввести формулу в левую верхнюю ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы.

ИНТЕРНЕТ

В Excel в Интернете вы можете просмотреть формулы массива, если они уже есть в открытой книге. Но вы не сможете создать формулу массива в этой версии Excel, нажав клавиши CTRL+SHIFT+ВВОД, которые вставляют формулу между парой открывающих и закрывающих фигурных скобок ({ }). Ввод фигурных скобок вручную также не превращает формулу в формулу массива.

Если у вас есть Excel, нажмите кнопку "Открыть в Excel", чтобы открыть книгу и создать формулу массива.

Автор - Gustav
Дата добавления - 17.03.2023 в 18:14
Gustav Дата: Пятница, 17.03.2023, 22:48 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Немного оптимизнул формулу в плане того, чтобы функция СЧЁТЕСЛИ не сканировала столбцы по 10 тысяч ячеек, а сканировала бы их гораздо меньшее количество. Для этого вычисляю номер последней занятой строки внутри исходного диапазона 'Lists of species'!A2:H10000 и усекаю его до актуальных размеров с помощью функции СМЕЩ (переменная "диапазон"). Теперь массивы-переменные "столбец" и "строка" во фрагменте "СЧЁТЕСЛИ(столбец; строка)" имеют длину не более количества реально используемых строк в переменной "занято". Ну, а сама формула уже выглядит практически как адская программа:

[vba]
Код
=MAKEARRAY(ЧСТРОК(A2:A9); ЧИСЛСТОЛБ(B1:I1);
LAMBDA(row;col;
ЕСЛИМН(
row = col; 1;
row < col; "";
row > col; LET(
исходный; 'Lists of species'!A2:H10000;
занято;   МАКС(BYROW(исходный; LAMBDA(r; СТРОКА(r)*(СЧЁТЗ(r)>0))))-СТРОКА(ИНДЕКС(исходный;1;0))+1;
диапазон; СМЕЩ(исходный;;;занято);
столбец;  ИНДЕКС(диапазон; 0; col);
строка;   ФИЛЬТР(ИНДЕКС(диапазон; 0; row);НЕ(ЕПУСТО(ИНДЕКС(диапазон; 0; row))));
N;        СУММ(СЧЁТЕСЛИ(столбец; строка));
A;        СЧЁТЗ(столбец);
B;        СЧЁТЗ(строка);

2*N / (A+B)
)
)))
[/vba]


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеНемного оптимизнул формулу в плане того, чтобы функция СЧЁТЕСЛИ не сканировала столбцы по 10 тысяч ячеек, а сканировала бы их гораздо меньшее количество. Для этого вычисляю номер последней занятой строки внутри исходного диапазона 'Lists of species'!A2:H10000 и усекаю его до актуальных размеров с помощью функции СМЕЩ (переменная "диапазон"). Теперь массивы-переменные "столбец" и "строка" во фрагменте "СЧЁТЕСЛИ(столбец; строка)" имеют длину не более количества реально используемых строк в переменной "занято". Ну, а сама формула уже выглядит практически как адская программа:

[vba]
Код
=MAKEARRAY(ЧСТРОК(A2:A9); ЧИСЛСТОЛБ(B1:I1);
LAMBDA(row;col;
ЕСЛИМН(
row = col; 1;
row < col; "";
row > col; LET(
исходный; 'Lists of species'!A2:H10000;
занято;   МАКС(BYROW(исходный; LAMBDA(r; СТРОКА(r)*(СЧЁТЗ(r)>0))))-СТРОКА(ИНДЕКС(исходный;1;0))+1;
диапазон; СМЕЩ(исходный;;;занято);
столбец;  ИНДЕКС(диапазон; 0; col);
строка;   ФИЛЬТР(ИНДЕКС(диапазон; 0; row);НЕ(ЕПУСТО(ИНДЕКС(диапазон; 0; row))));
N;        СУММ(СЧЁТЕСЛИ(столбец; строка));
A;        СЧЁТЗ(столбец);
B;        СЧЁТЗ(строка);

2*N / (A+B)
)
)))
[/vba]

Автор - Gustav
Дата добавления - 17.03.2023 в 22:48
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Построение матрицы сходства растений (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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