Прошу у сообщества помощи по следующему вопросу - в большом массиве данных мне нужно считать количество строк, в определенных столбцах которых содержатся нужные значения. Для этой задачи я использую функцию COUNTIFS.
Функция работает хорошо до тех пор, пока в критерий не требуется вводить несколько аргументов. Приведу пример - нужно посчитать количество строк в таблице, у которых в столбце С находятся значения "яблоко" или "груша", а в столбце D значения "красный" или "зеленый".
Вы скажете, что можно просуммировать значения функций для всех возможных сочетаний аргументов:
Хорошо если таких сочетаний всего 4, как в примере... но что делать если их несколько десятков? Я думаю, не для того мы с Вами формулы используем, чтобы ручками такую работу делать...
Покопавшись в интернетах я нашел на зарубежных форумах следующий вариант решения задачи (на рускоязычных форумах такого не видел):
Однако такое написание формулы на моем компе по незивестной мне причине не работает. Формула считается по первому значению, вбитому в массив с фигурными скобками - см. прикрепленное изображение.
Подскажите, пожалуйста, в чем может быть проблема?
Спасибо.
Всем добрый день!
Прошу у сообщества помощи по следующему вопросу - в большом массиве данных мне нужно считать количество строк, в определенных столбцах которых содержатся нужные значения. Для этой задачи я использую функцию COUNTIFS.
Функция работает хорошо до тех пор, пока в критерий не требуется вводить несколько аргументов. Приведу пример - нужно посчитать количество строк в таблице, у которых в столбце С находятся значения "яблоко" или "груша", а в столбце D значения "красный" или "зеленый".
Вы скажете, что можно просуммировать значения функций для всех возможных сочетаний аргументов:
Хорошо если таких сочетаний всего 4, как в примере... но что делать если их несколько десятков? Я думаю, не для того мы с Вами формулы используем, чтобы ручками такую работу делать...
Покопавшись в интернетах я нашел на зарубежных форумах следующий вариант решения задачи (на рускоязычных форумах такого не видел):
Однако такое написание формулы на моем компе по незивестной мне причине не работает. Формула считается по первому значению, вбитому в массив с фигурными скобками - см. прикрепленное изображение.
Подскажите, пожалуйста, в чем может быть проблема?
Да что Вы говорите? Только на этом форуме таких решений море Читайте Правила форума. Исправляйте свой пост - все формулы засовывайте в теги (в режиме правки поста выделяйте формулу и жмите кнопку fx) Прикладывайте пример в Excel И будет Вам решение
Да что Вы говорите? Только на этом форуме таких решений море Читайте Правила форума. Исправляйте свой пост - все формулы засовывайте в теги (в режиме правки поста выделяйте формулу и жмите кнопку fx) Прикладывайте пример в Excel И будет Вам решение_Boroda_
Про перевод формул я догадался изначально. Мой Excel русские формулы не оспринимает, а в английских просит исключить запятые и двоеточие. Если я это делаю, когда ввожу формулу из примера __Boroda__, то значение меняется на 2. Как я понимаю, теперь либо яблоки считаются и зеленые и красные, либо зеленые грушы и яблоки.
vikttur, _Boroda_,
Про перевод формул я догадался изначально. Мой Excel русские формулы не оспринимает, а в английских просит исключить запятые и двоеточие. Если я это делаю, когда ввожу формулу из примера __Boroda__, то значение меняется на 2. Как я понимаю, теперь либо яблоки считаются и зеленые и красные, либо зеленые грушы и яблоки.UncleT
Для того, чтобы проверялись все условия для всех условий, диапазоны должны быть разновекторные. Попробуйте заменить ; на запятую (не в Вашей - в исходной формуле)
Для того, чтобы проверялись все условия для всех условий, диапазоны должны быть разновекторные. Попробуйте заменить ; на запятую (не в Вашей - в исходной формуле)vikttur
Сообщение отредактировал vikttur - Пятница, 30.06.2017, 15:49
vikttur, При попытке ввести в ячейку формулу, содержащую двоеточие или запятую, Excel выдаёт ошибку, указывая на недопустимый аргумент. Кстати в варианте с приложенного выше скрина считаются яблоки зеленые и груши красные
vikttur, При попытке ввести в ячейку формулу, содержащую двоеточие или запятую, Excel выдаёт ошибку, указывая на недопустимый аргумент. Кстати в варианте с приложенного выше скрина считаются яблоки зеленые и груши красные UncleT
_Boroda_, vikttur, Оказалось что мой Excel видит сиволы следующим образом:
=SUMPRODUCT(COUNTIFS(C:C;{"яблоко";"груша"};D:D;{"зеленый"\"красный"})) Не пишу в тегах, чтобы созранить синтаксис.
Огромное спасибо за помощь!
Если Вас не затруднит, могли бы Вы подсказать, как установить в качестве критерия не конкретное значение, а ссылку на ячейку. Чтобы значения "зеленый" и "красный" брались из ячеек J2 и J3?
И ещё есть теоретический вопрос - возможно ли добавить ещё один фильтр с массивом?
Спасибо.
_Boroda_, vikttur, Оказалось что мой Excel видит сиволы следующим образом:
=SUMPRODUCT(COUNTIFS(C:C;{"яблоко";"груша"};D:D;{"зеленый"\"красный"})) Не пишу в тегах, чтобы созранить синтаксис.
Огромное спасибо за помощь!
Если Вас не затруднит, могли бы Вы подсказать, как установить в качестве критерия не конкретное значение, а ссылку на ячейку. Чтобы значения "зеленый" и "красный" брались из ячеек J2 и J3?
И ещё есть теоретический вопрос - возможно ли добавить ещё один фильтр с массивом?
Все время забываю, что там действительно можно через СУММ Или вот так
Код
=СУММ(СЧЁТЕСЛИМН(C:C;I7:I8;D:D;ТРАНСП(J7:J8)))
, но тогда это формула массива (вводится одновременным нажатием Контрл Шифт Ентер) Файл перевложил
Фильтр с массивом - какой у Вас пример был, такой ответ мы и дали. Но теоретически - так, напрямую, не прокатит. Придется извращаться. Но это уже другая тема и решение не обязательно будет через СЧЁТЕСЛИМН
Ячейки - да, возможно
Код
=СУММПРОИЗВ(СЧЁТЕСЛИМН(C:C;I7:I8;D:D;J7:K7))
Все время забываю, что там действительно можно через СУММ Или вот так
Код
=СУММ(СЧЁТЕСЛИМН(C:C;I7:I8;D:D;ТРАНСП(J7:J8)))
, но тогда это формула массива (вводится одновременным нажатием Контрл Шифт Ентер) Файл перевложил
Фильтр с массивом - какой у Вас пример был, такой ответ мы и дали. Но теоретически - так, напрямую, не прокатит. Придется извращаться. Но это уже другая тема и решение не обязательно будет через СЧЁТЕСЛИМН_Boroda_