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

Вход

Регистрация

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

 

= Мир MS Excel/Несколько аргументов для критерия в формуле COUNTIFS - Мир MS Excel

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

Excel 2010
Всем добрый день!

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

Функция работает хорошо до тех пор, пока в критерий не требуется вводить несколько аргументов.
Приведу пример - нужно посчитать количество строк в таблице, у которых в столбце С находятся значения "яблоко" или "груша", а в столбце D значения "красный" или "зеленый".

Вы скажете, что можно просуммировать значения функций для всех возможных сочетаний аргументов:
Код
=COUNTIFS(C:C;"яблоко";D:D;"зеленый") + COUNTIFS(C:C;"яблоко";D:D;"красный") + COUNTIFS(C:C;"груша";D:D;"зеленый") + COUNTIFS(C:C;"груша";D:D;"красный")


Хорошо если таких сочетаний всего 4, как в примере... но что делать если их несколько десятков?
Я думаю, не для того мы с Вами формулы используем, чтобы ручками такую работу делать...

Покопавшись в интернетах я нашел на зарубежных форумах следующий вариант решения задачи (на рускоязычных форумах такого не видел):
Код
=COUNTIFS(C:C;{"яблоко";"груша"};D:D;{"зеленый";"красный"})


Однако такое написание формулы на моем компе по незивестной мне причине не работает.
Формула считается по первому значению, вбитому в массив с фигурными скобками - см. прикрепленное изображение.

Подскажите, пожалуйста, в чем может быть проблема?

Спасибо.
К сообщению приложен файл: 5348327.jpg (64.5 Kb) · example.xlsx (10.1 Kb)


Сообщение отредактировал UncleT - Пятница, 30.06.2017, 14:39
 
Ответить
СообщениеВсем добрый день!

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

Функция работает хорошо до тех пор, пока в критерий не требуется вводить несколько аргументов.
Приведу пример - нужно посчитать количество строк в таблице, у которых в столбце С находятся значения "яблоко" или "груша", а в столбце D значения "красный" или "зеленый".

Вы скажете, что можно просуммировать значения функций для всех возможных сочетаний аргументов:
Код
=COUNTIFS(C:C;"яблоко";D:D;"зеленый") + COUNTIFS(C:C;"яблоко";D:D;"красный") + COUNTIFS(C:C;"груша";D:D;"зеленый") + COUNTIFS(C:C;"груша";D:D;"красный")


Хорошо если таких сочетаний всего 4, как в примере... но что делать если их несколько десятков?
Я думаю, не для того мы с Вами формулы используем, чтобы ручками такую работу делать...

Покопавшись в интернетах я нашел на зарубежных форумах следующий вариант решения задачи (на рускоязычных форумах такого не видел):
Код
=COUNTIFS(C:C;{"яблоко";"груша"};D:D;{"зеленый";"красный"})


Однако такое написание формулы на моем компе по незивестной мне причине не работает.
Формула считается по первому значению, вбитому в массив с фигурными скобками - см. прикрепленное изображение.

Подскажите, пожалуйста, в чем может быть проблема?

Спасибо.

Автор - UncleT
Дата добавления - 30.06.2017 в 14:22
_Boroda_ Дата: Пятница, 30.06.2017, 14:25 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
на рускоязычных форумах такого не видел

Да что Вы говорите? Только на этом форуме таких решений море
Читайте Правила форума.
Исправляйте свой пост - все формулы засовывайте в теги (в режиме правки поста выделяйте формулу и жмите кнопку fx)
Прикладывайте пример в Excel
И будет Вам решение


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
на рускоязычных форумах такого не видел

Да что Вы говорите? Только на этом форуме таких решений море
Читайте Правила форума.
Исправляйте свой пост - все формулы засовывайте в теги (в режиме правки поста выделяйте формулу и жмите кнопку fx)
Прикладывайте пример в Excel
И будет Вам решение

Автор - _Boroda_
Дата добавления - 30.06.2017 в 14:25
UncleT Дата: Пятница, 30.06.2017, 14:41 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, скорректировал сообщение
 
Ответить
Сообщение_Boroda_, скорректировал сообщение

Автор - UncleT
Дата добавления - 30.06.2017 в 14:41
_Boroda_ Дата: Пятница, 30.06.2017, 14:44 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=СУММПРОИЗВ(СЧЁТЕСЛИМН(C:C;{"яблоко":"груша"};D:D;{"зеленый";"красный"}))


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=СУММПРОИЗВ(СЧЁТЕСЛИМН(C:C;{"яблоко":"груша"};D:D;{"зеленый";"красный"}))

Автор - _Boroda_
Дата добавления - 30.06.2017 в 14:44
Udik Дата: Пятница, 30.06.2017, 14:49 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
а чего разделители в массивах разные - двоеточие и точка с запятой?


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщениеа чего разделители в массивах разные - двоеточие и точка с запятой?

Автор - Udik
Дата добавления - 30.06.2017 в 14:49
_Boroda_ Дата: Пятница, 30.06.2017, 14:52 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Вот именно в этом и дело - создаем матрицу


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВот именно в этом и дело - создаем матрицу

Автор - _Boroda_
Дата добавления - 30.06.2017 в 14:52
Udik Дата: Пятница, 30.06.2017, 15:01 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
а чо, так можно было? :D


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщениеа чо, так можно было? :D

Автор - Udik
Дата добавления - 30.06.2017 в 15:01
UncleT Дата: Пятница, 30.06.2017, 15:09 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
Предложенный Вами вариант, к сожалению, у меня не заработал.
Мой excel не хочет принимать формулу из-за запятых и двоеточия.
 
Ответить
Сообщение_Boroda_,
Предложенный Вами вариант, к сожалению, у меня не заработал.
Мой excel не хочет принимать формулу из-за запятых и двоеточия.

Автор - UncleT
Дата добавления - 30.06.2017 в 15:09
vikttur Дата: Пятница, 30.06.2017, 15:14 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Слева от формулы кнопка Eng/Rus для перевода формулы. Разделители тоже меняются.
Вместо СУММПРОИЗВ достаточно применить СУММ


Сообщение отредактировал vikttur - Пятница, 30.06.2017, 15:16
 
Ответить
СообщениеСлева от формулы кнопка Eng/Rus для перевода формулы. Разделители тоже меняются.
Вместо СУММПРОИЗВ достаточно применить СУММ

Автор - vikttur
Дата добавления - 30.06.2017 в 15:14
UncleT Дата: Пятница, 30.06.2017, 15:28 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
vikttur,
_Boroda_,

Про перевод формул я догадался изначально. Мой Excel русские формулы не оспринимает, а в английских просит исключить запятые и двоеточие.
Если я это делаю, когда ввожу формулу из примера __Boroda__, то значение меняется на 2. Как я понимаю, теперь либо яблоки считаются и зеленые и красные, либо зеленые грушы и яблоки.
К сообщению приложен файл: 5120498.jpg (59.2 Kb)
 
Ответить
Сообщениеvikttur,
_Boroda_,

Про перевод формул я догадался изначально. Мой Excel русские формулы не оспринимает, а в английских просит исключить запятые и двоеточие.
Если я это делаю, когда ввожу формулу из примера __Boroda__, то значение меняется на 2. Как я понимаю, теперь либо яблоки считаются и зеленые и красные, либо зеленые грушы и яблоки.

Автор - UncleT
Дата добавления - 30.06.2017 в 15:28
vikttur Дата: Пятница, 30.06.2017, 15:32 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Для того, чтобы проверялись все условия для всех условий, диапазоны должны быть разновекторные. Попробуйте заменить ; на запятую (не в Вашей - в исходной формуле)


Сообщение отредактировал vikttur - Пятница, 30.06.2017, 15:49
 
Ответить
СообщениеДля того, чтобы проверялись все условия для всех условий, диапазоны должны быть разновекторные. Попробуйте заменить ; на запятую (не в Вашей - в исходной формуле)

Автор - vikttur
Дата добавления - 30.06.2017 в 15:32
UncleT Дата: Пятница, 30.06.2017, 15:36 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
vikttur,
При попытке ввести в ячейку формулу, содержащую двоеточие или запятую, Excel выдаёт ошибку, указывая на недопустимый аргумент.
Кстати в варианте с приложенного выше скрина считаются яблоки зеленые и груши красные %)
 
Ответить
Сообщениеvikttur,
При попытке ввести в ячейку формулу, содержащую двоеточие или запятую, Excel выдаёт ошибку, указывая на недопустимый аргумент.
Кстати в варианте с приложенного выше скрина считаются яблоки зеленые и груши красные %)

Автор - UncleT
Дата добавления - 30.06.2017 в 15:36
_Boroda_ Дата: Пятница, 30.06.2017, 15:38 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Посмотрите прямо в файле, что мы мучаемся, Ваш Excel сам преобразует
К сообщению приложен файл: example-14-1.xlsx (10.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПосмотрите прямо в файле, что мы мучаемся, Ваш Excel сам преобразует

Автор - _Boroda_
Дата добавления - 30.06.2017 в 15:38
vikttur Дата: Пятница, 30.06.2017, 15:51 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Кстати в варианте с приложенного выше скрина считаются яблоки зеленые и груши красные

Потому что у Вас массивы одинаковго направления

Для того, чтобы проверялись все условия для всех условий, диапазоны должны быть разновекторные.
 
Ответить
Сообщение
Кстати в варианте с приложенного выше скрина считаются яблоки зеленые и груши красные

Потому что у Вас массивы одинаковго направления

Для того, чтобы проверялись все условия для всех условий, диапазоны должны быть разновекторные.

Автор - vikttur
Дата добавления - 30.06.2017 в 15:51
UncleT Дата: Пятница, 30.06.2017, 16:24 | Сообщение № 15
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, vikttur,
Оказалось что мой Excel видит сиволы следующим образом:

=SUMPRODUCT(COUNTIFS(C:C;{"яблоко";"груша"};D:D;{"зеленый"\"красный"})) Не пишу в тегах, чтобы созранить синтаксис.

Огромное спасибо за помощь!

Если Вас не затруднит, могли бы Вы подсказать, как установить в качестве критерия не конкретное значение, а ссылку на ячейку. Чтобы значения "зеленый" и "красный" брались из ячеек J2 и J3?

И ещё есть теоретический вопрос - возможно ли добавить ещё один фильтр с массивом?

Спасибо.
К сообщению приложен файл: example123.xlsx (10.4 Kb)
 
Ответить
Сообщение_Boroda_, vikttur,
Оказалось что мой Excel видит сиволы следующим образом:

=SUMPRODUCT(COUNTIFS(C:C;{"яблоко";"груша"};D:D;{"зеленый"\"красный"})) Не пишу в тегах, чтобы созранить синтаксис.

Огромное спасибо за помощь!

Если Вас не затруднит, могли бы Вы подсказать, как установить в качестве критерия не конкретное значение, а ссылку на ячейку. Чтобы значения "зеленый" и "красный" брались из ячеек J2 и J3?

И ещё есть теоретический вопрос - возможно ли добавить ещё один фильтр с массивом?

Спасибо.

Автор - UncleT
Дата добавления - 30.06.2017 в 16:24
_Boroda_ Дата: Пятница, 30.06.2017, 16:29 | Сообщение № 16
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Ячейки - да, возможно
Код
=СУММПРОИЗВ(СЧЁТЕСЛИМН(C:C;I7:I8;D:D;J7:K7))

Все время забываю, что там действительно можно через СУММ
Или вот так
Код
=СУММ(СЧЁТЕСЛИМН(C:C;I7:I8;D:D;ТРАНСП(J7:J8)))
, но тогда это формула массива (вводится одновременным нажатием Контрл Шифт Ентер)
Файл перевложил

Фильтр с массивом - какой у Вас пример был, такой ответ мы и дали. Но теоретически - так, напрямую, не прокатит. Придется извращаться. Но это уже другая тема и решение не обязательно будет через СЧЁТЕСЛИМН
К сообщению приложен файл: example123_2.xlsx (10.6 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЯчейки - да, возможно
Код
=СУММПРОИЗВ(СЧЁТЕСЛИМН(C:C;I7:I8;D:D;J7:K7))

Все время забываю, что там действительно можно через СУММ
Или вот так
Код
=СУММ(СЧЁТЕСЛИМН(C:C;I7:I8;D:D;ТРАНСП(J7:J8)))
, но тогда это формула массива (вводится одновременным нажатием Контрл Шифт Ентер)
Файл перевложил

Фильтр с массивом - какой у Вас пример был, такой ответ мы и дали. Но теоретически - так, напрямую, не прокатит. Придется извращаться. Но это уже другая тема и решение не обязательно будет через СЧЁТЕСЛИМН

Автор - _Boroda_
Дата добавления - 30.06.2017 в 16:29
Pelena Дата: Пятница, 30.06.2017, 17:21 | Сообщение № 17
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
{"зеленый"\"красный"}

Похоже на Mac


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
{"зеленый"\"красный"}

Похоже на Mac

Автор - Pelena
Дата добавления - 30.06.2017 в 17:21
UncleT Дата: Суббота, 01.07.2017, 18:19 | Сообщение № 18
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Огромное всем спасибо и хороших выходных!
 
Ответить
СообщениеОгромное всем спасибо и хороших выходных!

Автор - UncleT
Дата добавления - 01.07.2017 в 18:19
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Несколько аргументов для критерия в формуле COUNTIFS (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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