Страницы: 1
RSS
Подсчёт уникальных значений по нескольким критериям
 
Добрый день!
Подскажите пожалуйста как при помощи формул произвести расчёт значений по нескольким условиям. Во вложенном файле в таблице "список деталей" необходимо произвести подсчёт данных из таблицы "БР", при этом считать данные нужно по нескольким критериям одновременно. Пробовал использовать "СЧЁТЕСЛИ", но получается итог только по одному критерию выбора, при использовании "СЧЁТЕСЛИМН", вообще ответ не выдаёт "#ЗНАЧ!".
 
Здравствуйте. Может так надо для J20?
Код
=СЧЁТЕСЛИМН($B$2:$B$14;B20;$A$2:$A$14;"<>")

Ошибки в формуле СЧЁТЕСЛИМН(A2:B14;B19;A2:A14;">0")

Изменено: gling - 24.02.2017 13:42:22
 
Спасибо, так работает!
подскажите ещё, в чём ошибка. При добавлении в формулу ещё одного критерии (=СЧЁТЕСЛИМН($B$2:$B$14;B19;$A$2:$A$14;"<>";$E$2:$E$14;"1990") опять же всё работает, но стоит добавить ещё одно условие:  СЧЁТЕСЛИМН($B$2:$B$14;B19;$A$2:$A$14;"<>";$E$2:$E$14;"1990";$E$2:$E$14;"1989") то сразу выдаёт неверный результат.
 
Isalgiz, в формуле все условия по логике И, если хотя бы одно не выполняется, то не зачёт, иначе - зачёт. Если Вам диапазон лет, то писать надо так:
=СЧЁТЕСЛИМН($B$2:$B$14;B19;$A$2:$A$14;"<>";$E$2:$E$14;"<=1990";$E$2:$E$14;">=1989")

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Всем спасибо! Всё работает как надо.
 
Добрый день!
Камрады, на вас одна надежда, помогите. Подобная же проблема. необходимо подсчитать количество по нескольким критериям. При этом значение одного из критериев в ячейках столбца повторяется и если использовать СЧЕТЕСЛМН то данные ячейки подсчитываются, а нужно что бы по данному критерию  считались только уникальные записи в данном столбце с учетом других необходимых обычных критериев.  
Изменено: Kalyam - 24.01.2020 09:25:06 (Перезалил ниже файл.)
 
Kalyam, формула массива:
=СУММ(($C$2:$C$55=1)*($D$2:$D$55=G2)*($E$2:$E$55="гр"))

обычный СЧЁТЕСЛИМН:
=СЧЁТЕСЛИМН(D:D;G2;C:C;1;E:E;"гр")
Изменено: Polkilo - 23.01.2020 16:04:31
 
Polkilo, такой вариант мной рассматривал я, но не у всех первых есть отметка по столбу Е, бывает только у 2 и 3 записи по счету.  
 
Kalyam, считаться должны только записи, в которых выполняется условие по городу и гр с учётом уникальности по столбцу B?
 
Polkilo, Да.  
 
Kalyam, несколько городов в одном акте могут быть? Если да, то как учитывать?
 
Polkilo, У разных городов одинаковые номера актов быть не могут. Если номер акта одинаковые, то все записи принадлежат одному городу.
 
Чуть изменил пример, добавил пояснений.
 
Объединил ответы в один, т.к. они ключевые в задаче:
Считаться должны только записи, в которых выполняется условие по региону и отметке "гр" с учётом уникальности номера акта по столбцу B.
У разных регионов одинаковые номера актов быть не могут. Если номер акта одинаковые, то все записи принадлежат одному региону.
Изменено: Kalyam - 24.01.2020 09:28:27
 
Цитата
Kalyam написал:
У разных регионов одинаковые номера актов быть не могут.
№ п/п 3, 4 акт 0140.17.002043 регион Ростов
№ п/п 5     акт 0140.17.002043 регион Казань
Лень двигатель прогресса, доказано!!!
 
Цитата
Сергей написал:
№ п/п 3, 4 акт 0140.17.002043 регион Ростов№ п/п 5     акт 0140.17.002043 регион Казань
там ростов. Ошибка в примере, моя невнимательность при его создании
 
если доп столбец допустим то все просто
Лень двигатель прогресса, доказано!!!
 
Цитата
Сергей написал:
если доп столбец допустим то все просто
подскажите формулу для дополнительного столбца. Отметки "гр" проставляются не в момент заполнения таблицы, они вносятся позже. Так же позже могут появиться новые строки, имеющие тот же номер акта и + вероятно отметки "гр".  
 
смотрите
Лень двигатель прогресса, доказано!!!
 
Сергей, Если отметка гр стоит у первого номера акта, то у всех с тем же номером акта в дополнительном столбце проставляется 1
 
не обратил внимания тогда так
Код
=ЕСЛИ(И(СЧЁТЕСЛИМН($B$2:B2;B2;$D$2:D2;D2;$E$2:E2;"гр")=1;E2="гр");1;)
Лень двигатель прогресса, доказано!!!
 
Сергей, Спасибо огромное.
Тоже попробовал через дополнительный столбец и используя умные таблицы, но намудрил (правда в доп столбце присваивается 1 к первому акту из одинаковых, даже если у ней нет отметки гр):
=ЕСЛИ(СЧЁТЕСЛИ($B$2:[@[№ акта]];[@[№ акта]])=1;ЕСЛИ(СЧЁТЕСЛИМН([№ акта];[@[№ акта]];[Отметка];"гр")>=1;1;0);0)  
 
Может кто предложит способ без дополнительного столбца
 
Kalyam, не самый оптимальный вариант, но оптимизировать лениво:
Код
'Диапазон условия, условие, диапазон флага, флаг, столбец по которому считаем уникальные
Function ololo(Range1 As Range, City As String, range2 As Range, Flag As String, Range3 As Range)
Dim MyCell As Object, myDictionary As Object
Dim MyItem As Variant
Dim Counter As Long

If Range1.Count <> range2.Count Or Range1.Count <> Range3.Count Then ololo = "Ошибка"

Set myDictionary = CreateObject("Scripting.Dictionary")

On Error Resume Next
For Each MyCell In Range3
  myDictionary.Add CStr(MyCell), CStr(MyCell)
Next
On Error GoTo 0
Err.Clear

For Each MyItem In myDictionary.Items
    If WorksheetFunction.CountIfs(Range1, City, range2, Flag, Range3, MyItem) > 0 Then
        Counter = Counter + 1
    End If
Next

ololo = Counter

End Function
Пример использования:
=ololo($D$2:$D$40;G2;$E$2:$E$40;$E$4;$B$2:$B$40)
 
Интересует без макросов. Вся сложность в том, что в таблице восемь столбцов с различными отметками (по типу гр). И использовать дополнительный столбец не очень удобно, ведь их тоже надо будет делать 8  
 
Добрый вечер. У меня вопрос по Казани.
В Вашем примере акт 0140.17.002043 есть у Ростова и у Казани. К какому региону он принадлежит? В котором встречается впервые?
 
Akropochev,было выше, это ошибка примера. Регион у этих трех записей любой, главное одинаковый
 
Что-то тяжело в пятницу думается. Лучше ничего не придумал

=СЧЁТ(1/(МУМНОЖ(Ч(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ($B$2:$B$40&$D$2:$D$40&$E$2:$E$40;$B$2:$B$40&$D$2:$D$40&$E$2:$E$40;);СТРОКА($B$2:$B$40)-1);ВЫБОР({1;2};$D$2:$D$40;$E$2:$E$40))=ВЫБОР({1;2};J4;"гр"));{1:1})=2))
Изменено: Akropochev - 24.01.2020 20:26:20
 
Код
=СЧЁТ(1/ЧАСТОТА(ЕСЛИ(МУМНОЖ(($E$2:$E$40="гр")*($D$2:$D$40=J2);1);ИНДЕКС(СЧЁТЕСЛИ($B$2:$B$40;">="&$B$2:$B$40);));СЧЁТЕСЛИ($B$2:$B$40;">="&$B$2:$B$40)))
Изменено: Akropochev - 24.01.2020 20:54:28
 
Akropochev, Спасибо огромное!!!
Страницы: 1
Наверх