Здравствуйте! Есть книга с 3 листами На первом список видов растений (столбец D) с атрибутами редкости (столбец F), на втором тот же список (столбец A) c распределением находок по различным местообитанием. На третьем матрица, в ячейки число видов, соответствующих каждому из атрибутов редкости, встречающихся в каждом из местообитаний. То есть, надо задать подсчёт ненулевых значений в соответствующих столбцах листа 2, связав их с атрибутами на листе 1 через совпадение значений в списках на листах 1 и 2. Пробовал СЧЁТЕСЛИМН(Атрибуты!$F:$F;$A2;Атрибуты!$D:$D;ВxС!$A:$A;ВxС!B:B;">0"), но она не работает, подозреваю, именно из-за неправильного условия совпадения списков.
Подскажите, можно ли решить эту проблему без использования сводной таблицы? Копирование атрибутов на второй лист тоже по ряду причин не вариант.
Здравствуйте! Есть книга с 3 листами На первом список видов растений (столбец D) с атрибутами редкости (столбец F), на втором тот же список (столбец A) c распределением находок по различным местообитанием. На третьем матрица, в ячейки число видов, соответствующих каждому из атрибутов редкости, встречающихся в каждом из местообитаний. То есть, надо задать подсчёт ненулевых значений в соответствующих столбцах листа 2, связав их с атрибутами на листе 1 через совпадение значений в списках на листах 1 и 2. Пробовал СЧЁТЕСЛИМН(Атрибуты!$F:$F;$A2;Атрибуты!$D:$D;ВxС!$A:$A;ВxС!B:B;">0"), но она не работает, подозреваю, именно из-за неправильного условия совпадения списков.
Подскажите, можно ли решить эту проблему без использования сводной таблицы? Копирование атрибутов на второй лист тоже по ряду причин не вариант.Narak-zempo
Конечно, заманчиво, чтобы программа сама искала нужный столбец, но формула с проверкой позиции при попытке переписать её в настоящем файле с данными почему-то возвращает ненулевые значения там, где их не должно быть. Я не очень хорошо знаком с этими функциями, поэтому логика формулы для меня непрозрачна и сходу понять, где косячу, не могу.
Спасибо большое!
Первый вариант от qlinq сработал.
Конечно, заманчиво, чтобы программа сама искала нужный столбец, но формула с проверкой позиции при попытке переписать её в настоящем файле с данными почему-то возвращает ненулевые значения там, где их не должно быть. Я не очень хорошо знаком с этими функциями, поэтому логика формулы для меня непрозрачна и сходу понять, где косячу, не могу.Narak-zempo
У предлагаемых решений обнаружился подводный камень: они чувствительны к порядку названий в списках и при их несовпадении выдают неверные результаты. Поскольку первоначальная формулировка задачи была довольно туманна для меня самого , теперь постараюсь её уточнить и разбить на этапы. Итак:
1) Отфильтровать на первом листе виды по одному из 3-х атрибутов; 2) По каждому виду (столбец D) найти соответствие списке (т.е. по всему столбцу A) на листе ВхС; 3) Найдя соответствующую строку, проверить значение в ячейке на пересечении этой строки с определённым столбцом (столбец можно задавать вручную); 4) Если это значение больше нуля, увеличить результат на единицу
Здравствуйте ещё раз.
У предлагаемых решений обнаружился подводный камень: они чувствительны к порядку названий в списках и при их несовпадении выдают неверные результаты. Поскольку первоначальная формулировка задачи была довольно туманна для меня самого , теперь постараюсь её уточнить и разбить на этапы. Итак:
1) Отфильтровать на первом листе виды по одному из 3-х атрибутов; 2) По каждому виду (столбец D) найти соответствие списке (т.е. по всему столбцу A) на листе ВхС; 3) Найдя соответствующую строку, проверить значение в ячейке на пересечении этой строки с определённым столбцом (столбец можно задавать вручную); 4) Если это значение больше нуля, увеличить результат на единицуNarak-zempo
По Вашему новому описанию получается точно такая же таблица как на листе ВхС только значения на 1 больше , исключая 0 (нули). Задача полностью изменилась, совсем запутали.
Цитата
Найдя соответствующую строку, проверить значение в ячейке на пересечении этой строки с определённым столбцом (столбец можно задавать вручную); 4) Если это значение больше нуля, увеличить результат на единицу
а первоначально было
Цитата
надо задать подсчёт ненулевых значений
Оставил старый вариант только немного доработал. Если не так, покажите какой должен быть результат. Про суммировать что либо или подсчитать, в Сообщении №6 ничего нет.
По Вашему новому описанию получается точно такая же таблица как на листе ВхС только значения на 1 больше , исключая 0 (нули). Задача полностью изменилась, совсем запутали.
Цитата
Найдя соответствующую строку, проверить значение в ячейке на пересечении этой строки с определённым столбцом (столбец можно задавать вручную); 4) Если это значение больше нуля, увеличить результат на единицу
а первоначально было
Цитата
надо задать подсчёт ненулевых значений
Оставил старый вариант только немного доработал. Если не так, покажите какой должен быть результат. Про суммировать что либо или подсчитать, в Сообщении №6 ничего нет.gling
Но выглядит всё так, как надо, только пока порядок списков на первом и втором листе совпадает. Попробуйте упорядочить один из списков обратно алфавиту - и все значения изменятся.
Но выглядит всё так, как надо, только пока порядок списков на первом и втором листе совпадает. Попробуйте упорядочить один из списков обратно алфавиту - и все значения изменятся.Narak-zempo
Narak-zempo, я потому и прошу пример, если у вас по факту списки разные, то и решение будет совсем другим. Да и насколько они разные, возможны ли повторы - тут целый лес нюансов
Narak-zempo, я потому и прошу пример, если у вас по факту списки разные, то и решение будет совсем другим. Да и насколько они разные, возможны ли повторы - тут целый лес нюансовbuchlotnik
Сообщение отредактировал buchlotnik - Суббота, 29.11.2014, 14:33
buchlotnik, списки по факту одинаковые, в примере фрагмент реального рабочего списка. Но в процессе работы они могут меняться. Скажем, на одном из листов надо будет выделить дополнительную строку для шапки или сортировать список в другом порядке. Поэтому нужна формула, нечувствительная к несовпадению номера строки.
Собственно, я заметил недостаток первого решения, потому что в одном из полных списков 2 строки были поменяны местами - и они недосчитывались. Вы можете убедиться, поменяв местами строки в примере.
buchlotnik, списки по факту одинаковые, в примере фрагмент реального рабочего списка. Но в процессе работы они могут меняться. Скажем, на одном из листов надо будет выделить дополнительную строку для шапки или сортировать список в другом порядке. Поэтому нужна формула, нечувствительная к несовпадению номера строки.
Собственно, я заметил недостаток первого решения, потому что в одном из полных списков 2 строки были поменяны местами - и они недосчитывались. Вы можете убедиться, поменяв местами строки в примере.Narak-zempo
Сообщение отредактировал Narak-zempo - Суббота, 29.11.2014, 14:46
Narak-zempo, в начале темы пример, для которого вы получили решение. Потом выяснилось, что
Цитата
Скажем, на одном из листов надо будет выделить дополнительную строку для шапки или сортировать список в другом порядке.
пример должен показать как это выглядит никто не пишет абстрактно-универсальные формулы для любого случая, любой хотелки и чтобы это устроило всех и вся, особенно начальника
Narak-zempo, в начале темы пример, для которого вы получили решение. Потом выяснилось, что
Цитата
Скажем, на одном из листов надо будет выделить дополнительную строку для шапки или сортировать список в другом порядке.
пример должен показать как это выглядит никто не пишет абстрактно-универсальные формулы для любого случая, любой хотелки и чтобы это устроило всех и вся, особенно начальникаbuchlotnik
Вот пример. На листе 2 (ВхБ) список из 208 видов в произвольном порядке (повторов нет и не предусматривается); на листе 1 (Атрибуты) - 20 из них (для примера взял первые 20 по алфавиту). В таблице на листе 3 (Паттерны) надо подсчитать, сколько из этих 20 видов, имеющих атрибуты R, F и C, встречаются в каждом из биотопов (т.е., в каждом из столбцов на листе ВхБ). Очень желательно, чтобы добавление видов с соотв. атрибутами в список на листе 1 автоматически отражалось в таблице результатов на листе 3, кроме того, чтобы на эти результаты не влияло изменение порядка списков.
Спасибо, теперь понятно.
Вот пример. На листе 2 (ВхБ) список из 208 видов в произвольном порядке (повторов нет и не предусматривается); на листе 1 (Атрибуты) - 20 из них (для примера взял первые 20 по алфавиту). В таблице на листе 3 (Паттерны) надо подсчитать, сколько из этих 20 видов, имеющих атрибуты R, F и C, встречаются в каждом из биотопов (т.е., в каждом из столбцов на листе ВхБ). Очень желательно, чтобы добавление видов с соотв. атрибутами в список на листе 1 автоматически отражалось в таблице результатов на листе 3, кроме того, чтобы на эти результаты не влияло изменение порядка списков.Narak-zempo
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
, теперь постараюсь её уточнить и разбить на этапы. Итак: 
