ЗАДАЧА: Многократно сравнивать разные длинные списки видов растений попарно между собой по типу: "Вставил перечни видов в Лист 1, а на Листе 2 матрица нарисовалась". Есть документ с двумя листами: Лист 1-списки разных видов растений (столбцы); Лист 2-квадратная матрица коэффициентов сходства каждого столбца с каждым другим стобцом (т.е. сколько общих видов между первым 1ым и 2ом стобцом, 1ым и 3им, 2ым и 3им и т.д. ).
ПРОБЛЕМА: Я прописал в каждую ячейку матрицы какой с каким столбцом должен попарно сравниваться. В принципе, всё работает. Но процесс вписывания весьма утомительный, а размер матрицы получается ограниченный (в силу утомительности).
ВОПРОС: Как автоматизировать процесс заполнения матрицы, чтобы коэффициенты сходства после попарного сравнивания столбцов вписывались в нужную ячейку матрицы, а матрица могла быть очень большой?
Друзья, нужна помощь.
ЗАДАЧА: Многократно сравнивать разные длинные списки видов растений попарно между собой по типу: "Вставил перечни видов в Лист 1, а на Листе 2 матрица нарисовалась". Есть документ с двумя листами: Лист 1-списки разных видов растений (столбцы); Лист 2-квадратная матрица коэффициентов сходства каждого столбца с каждым другим стобцом (т.е. сколько общих видов между первым 1ым и 2ом стобцом, 1ым и 3им, 2ым и 3им и т.д. ).
ПРОБЛЕМА: Я прописал в каждую ячейку матрицы какой с каким столбцом должен попарно сравниваться. В принципе, всё работает. Но процесс вписывания весьма утомительный, а размер матрицы получается ограниченный (в силу утомительности).
ВОПРОС: Как автоматизировать процесс заполнения матрицы, чтобы коэффициенты сходства после попарного сравнивания столбцов вписывались в нужную ячейку матрицы, а матрица могла быть очень большой?oscillat8
oscillat8, добрый день. Поясните, пожалуйста, формулу:
Код
{=2*СУММ(СЧЁТЕСЛИ('Lists of species'!B3:B10000;'Lists of species'!C3:C10000))/(СЧЁТЗ('Lists of species'!B3:B10000)+СЧЁТЗ('Lists of species'!C3:C10000))}
По идее, коэффициент сходства столбцов мог бы рассчитываться по-другому: как отношение числа схожих пар значений для обоих столбцов к общему числу значений в обоих столбцах. Ответ. Автоматизировать процесс заполнения матрицы можно с помощью макроса.
oscillat8, добрый день. Поясните, пожалуйста, формулу:
Код
{=2*СУММ(СЧЁТЕСЛИ('Lists of species'!B3:B10000;'Lists of species'!C3:C10000))/(СЧЁТЗ('Lists of species'!B3:B10000)+СЧЁТЗ('Lists of species'!C3:C10000))}
По идее, коэффициент сходства столбцов мог бы рассчитываться по-другому: как отношение числа схожих пар значений для обоих столбцов к общему числу значений в обоих столбцах. Ответ. Автоматизировать процесс заполнения матрицы можно с помощью макроса.NikitaDvorets
Сообщение отредактировал NikitaDvorets - Четверг, 16.03.2023, 22:17
Да, конечно. Это общеупотребимая формула флористического сходства Сёренсена (она же Сёренсена-Чекановского, она же Dice) x=2n/a+b, где n-число одних и тех же, т.е. общих видов растений для двух списков, а-кол-во видов растений в первом списке, b-кол-во видов растений во втором списке.
В нашем случае числитель - мы соотносим все идентичные ячейки из листа 'Lists of species' из столбца B с идентичными ячейками того же листа, но столбца C. Затем суммируем общее кол-во совпадающих пар и умножаем на 2. А знаменатель-сумма всех элементов (непустых ячеек) в столбцах В и С.
Да, конечно. Это общеупотребимая формула флористического сходства Сёренсена (она же Сёренсена-Чекановского, она же Dice) x=2n/a+b, где n-число одних и тех же, т.е. общих видов растений для двух списков, а-кол-во видов растений в первом списке, b-кол-во видов растений во втором списке.
В нашем случае числитель - мы соотносим все идентичные ячейки из листа 'Lists of species' из столбца B с идентичными ячейками того же листа, но столбца C. Затем суммируем общее кол-во совпадающих пар и умножаем на 2. А знаменатель-сумма всех элементов (непустых ячеек) в столбцах В и С.oscillat8
Получил те же результаты, что и 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-й показалось логичнее - её и воплотил в формуле. Но исправить недолго.
Получил те же результаты, что и 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
В новомодних версиях Excel (для Microsoft 365, для Интернета, 2021) с помощью функции LET можно оформить очень прозрачную расчетную формулу (для той же ячейки B3):
[/vba] Можно вставлять в ячейку прямо именно в таком (втором) виде: пробелы и переносы не мешают, а, наоборот, способствуют.
Интересно, что эту формулу с LET не надо вставлять как формулу массива. По крайней мере, в имеющейся у меня в доступе корпоративной Excel для Интернета, где я сочинял эту формулу, этого делать не пришлось - ввелась при помощи обычного завершения ввода по Enter.
И в этой версии Excel, похоже, вообще не присутствует такого понятия как формула массива, во всяком случае комбинация Ctrl+Shift+Enter никак себя не проявляет и ни к чему не приводит... Что-то подобное происходит в "обычной" Excel при вводе формулы для условного форматирования или для настройки "Проверки данных". Т.е. формула на рабочем листе - массивная, а при вводе в УФ или ПД становится "обычной", потому что как массивную её туда просто не ввести. Но после ввода в УФ или ПД она функционирует там именно как массивная - Excel как бы неявно сама добавляет "массивность" к этой формуле.
В новомодних версиях Excel (для Microsoft 365, для Интернета, 2021) с помощью функции LET можно оформить очень прозрачную расчетную формулу (для той же ячейки B3):
[/vba] Можно вставлять в ячейку прямо именно в таком (втором) виде: пробелы и переносы не мешают, а, наоборот, способствуют.
Интересно, что эту формулу с LET не надо вставлять как формулу массива. По крайней мере, в имеющейся у меня в доступе корпоративной Excel для Интернета, где я сочинял эту формулу, этого делать не пришлось - ввелась при помощи обычного завершения ввода по Enter.
И в этой версии Excel, похоже, вообще не присутствует такого понятия как формула массива, во всяком случае комбинация Ctrl+Shift+Enter никак себя не проявляет и ни к чему не приводит... Что-то подобное происходит в "обычной" Excel при вводе формулы для условного форматирования или для настройки "Проверки данных". Т.е. формула на рабочем листе - массивная, а при вводе в УФ или ПД становится "обычной", потому что как массивную её туда просто не ввести. Но после ввода в УФ или ПД она функционирует там именно как массивная - Excel как бы неявно сама добавляет "массивность" к этой формуле.Gustav
В новомодних версиях Excel (для Microsoft 365, для Интернета, 2021)
Ну, и, наконец, чума от слова "совсем" - созданная в версии "Excel для Интернета" мегаформула массива (ах, всё-таки! правда, с помощью функции), вводимая в одну-единственную ячейку B2 листа "Sorensen (Dice) (2)". И, собственно, на этом всё: "единички" главной диагонали, пустые ячейки выше диагонали, рассчитанные значения ниже диагонали - всё универсально вычисляется этой одно-единственной мегаформулой. Во все остальные ячейки диапазона B2:I9 ничего вводить не надо, они должны быть полностью очищены перед вводом формулы в B2 (иначе в B2 возникнет ошибка #ПЕРЕНОС!). Сознательно использую тэг VBA для более прозрачной демонстрации формулы в виде, облегчающем ее анализ:
В новомодних версиях Excel (для Microsoft 365, для Интернета, 2021)
Ну, и, наконец, чума от слова "совсем" - созданная в версии "Excel для Интернета" мегаформула массива (ах, всё-таки! правда, с помощью функции), вводимая в одну-единственную ячейку B2 листа "Sorensen (Dice) (2)". И, собственно, на этом всё: "единички" главной диагонали, пустые ячейки выше диагонали, рассчитанные значения ниже диагонали - всё универсально вычисляется этой одно-единственной мегаформулой. Во все остальные ячейки диапазона B2:I9 ничего вводить не надо, они должны быть полностью очищены перед вводом формулы в B2 (иначе в B2 возникнет ошибка #ПЕРЕНОС!). Сознательно использую тэг VBA для более прозрачной демонстрации формулы в виде, облегчающем ее анализ:
Начиная с обновления для Microsoft 365 за сентябрь 2018 г., любая формула, которая может возвращать несколько результатов, автоматически переносит их вниз или в соседние ячейки. Это изменение поведения также сопровождается несколькими новыми функциями динамического массива. Формулы динамического массива, независимо от того, используют ли они существующие функции или функции динамического массива, необходимо вводить только в одну ячейку, а затем подтверждать, нажав ВВОД. Ранее в устаревших формулах массива сначала требовалось выбрать весь выходной диапазон, а затем подтвердить формулу с помощью CTRL+SHIFT+ВВОД. Их часто называют формулами CSE (т.е. Ctrl Shift Enter).
Если у вас есть текущая версия Microsoft 365, можно просто ввести формулу в левую верхнюю ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы.
ИНТЕРНЕТ
В Excel в Интернете вы можете просмотреть формулы массива, если они уже есть в открытой книге. Но вы не сможете создать формулу массива в этой версии Excel, нажав клавиши CTRL+SHIFT+ВВОД, которые вставляют формулу между парой открывающих и закрывающих фигурных скобок ({ }). Ввод фигурных скобок вручную также не превращает формулу в формулу массива.
Если у вас есть Excel, нажмите кнопку "Открыть в Excel", чтобы открыть книгу и создать формулу массива.
Начиная с обновления для Microsoft 365 за сентябрь 2018 г., любая формула, которая может возвращать несколько результатов, автоматически переносит их вниз или в соседние ячейки. Это изменение поведения также сопровождается несколькими новыми функциями динамического массива. Формулы динамического массива, независимо от того, используют ли они существующие функции или функции динамического массива, необходимо вводить только в одну ячейку, а затем подтверждать, нажав ВВОД. Ранее в устаревших формулах массива сначала требовалось выбрать весь выходной диапазон, а затем подтвердить формулу с помощью CTRL+SHIFT+ВВОД. Их часто называют формулами CSE (т.е. Ctrl Shift Enter).
Если у вас есть текущая версия Microsoft 365, можно просто ввести формулу в левую верхнюю ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы.
ИНТЕРНЕТ
В Excel в Интернете вы можете просмотреть формулы массива, если они уже есть в открытой книге. Но вы не сможете создать формулу массива в этой версии Excel, нажав клавиши CTRL+SHIFT+ВВОД, которые вставляют формулу между парой открывающих и закрывающих фигурных скобок ({ }). Ввод фигурных скобок вручную также не превращает формулу в формулу массива.
Если у вас есть Excel, нажмите кнопку "Открыть в Excel", чтобы открыть книгу и создать формулу массива.
Немного оптимизнул формулу в плане того, чтобы функция СЧЁТЕСЛИ не сканировала столбцы по 10 тысяч ячеек, а сканировала бы их гораздо меньшее количество. Для этого вычисляю номер последней занятой строки внутри исходного диапазона 'Lists of species'!A2:H10000 и усекаю его до актуальных размеров с помощью функции СМЕЩ (переменная "диапазон"). Теперь массивы-переменные "столбец" и "строка" во фрагменте "СЧЁТЕСЛИ(столбец; строка)" имеют длину не более количества реально используемых строк в переменной "занято". Ну, а сама формула уже выглядит практически как адская программа:
Немного оптимизнул формулу в плане того, чтобы функция СЧЁТЕСЛИ не сканировала столбцы по 10 тысяч ячеек, а сканировала бы их гораздо меньшее количество. Для этого вычисляю номер последней занятой строки внутри исходного диапазона 'Lists of species'!A2:H10000 и усекаю его до актуальных размеров с помощью функции СМЕЩ (переменная "диапазон"). Теперь массивы-переменные "столбец" и "строка" во фрагменте "СЧЁТЕСЛИ(столбец; строка)" имеют длину не более количества реально используемых строк в переменной "занято". Ну, а сама формула уже выглядит практически как адская программа:
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
