количество уникальных и автофильтр(ПРОМЕЖУТОЧНЫЕ.ИТОГИ)

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » количество уникальных и автофильтр(ПРОМЕЖУТОЧНЫЕ.ИТОГИ) (Формулы/Formulas)

lFJl

Дата: Среда, 22.02.2017, 16:23 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 6 ±

Замечаний: 0% ±

Excel 2013

Добрый день.
есть таблица, там количество отфильтрованных значений нахожу так:

Код

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;Таблица1[Столбец1])

Как можно еще узнать количество уникальных отфильтрованных значений?

К сообщению приложен файл: 6975764.xlsx (10.0 Kb)

Ответить

TimSha

Дата: Среда, 22.02.2017, 16:52 | Сообщение № 2

Группа: Проверенные

Ранг: Ветеран

Сообщений: 627

Репутация: 94 ±

Замечаний: 0% ±

Excel 2013 Pro +

Цитата lFJl, 22.02.2017 в 16:23, в сообщении № 1 ()

Как можно еще

Вариантов море, вот один из них через под поле - далее фильтр по 1 -

Код

=СЧЁТЕСЛИ($A$2:A2;A2)

"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)

Ответить

jakim

Дата: Среда, 22.02.2017, 16:59 | Сообщение № 3

Группа: Друзья

Ранг: Старожил

Сообщений: 1199

Репутация: 313 ±

Замечаний: 0% ±

Excel 2010

Обычная формула

Код

=SUMPRODUCT(1/COUNTIF(Таблица1[Столбец1];Таблица1[Столбец1]))

Массивная

Код

=SUM(1/COUNTIF(Таблица1[Столбец1];Таблица1[Столбец1]))

Ответить

bmv98rus

Дата: Среда, 22.02.2017, 17:12 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4107

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

jakim,

Цитата lFJl, 22.02.2017 в 16:23, в сообщении № 1 ()

количество уникальных отфильтрованных значений?

Вроде ни та ни другая не пропускает скрытые или я ошибаюсь?

С доп. столбцом.

К сообщению приложен файл: Copy_of_6975764.xlsx (10.7 Kb)

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus - Среда, 22.02.2017, 17:14

Ответить

lFJl

Дата: Среда, 22.02.2017, 18:46 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 6 ±

Замечаний: 0% ±

Excel 2013

bmv98rus, Верно, формулы TimSha, jakim, не помогают. Мне без доп столбца нужно, увы...

Ответить

bmv98rus

Дата: Среда, 22.02.2017, 19:42 | Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4107

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

lFJl,
Или писать соственную функцию на VBA, или ... мысль моя на этом обрываются.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

buchlotnik

Дата: Среда, 22.02.2017, 19:53 | Сообщение № 7

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Цитата

Мне без доп столбца нужно, увы...

ловите - даже немассивка, хоть и монструозная

Код

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(Таблица1[Столбец1];Таблица1[Столбец1]);ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(Таблица1[[#Заголовки];[Столбец1]];СТРОКА(Таблица1[Столбец1])-1;)))

а то прям так сразу - VBA

К сообщению приложен файл: 5709231.xlsx (10.5 Kb)

Сообщение отредактировал buchlotnik - Среда, 22.02.2017, 19:58

Ответить

lFJl

Дата: Среда, 22.02.2017, 20:37 | Сообщение № 8

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 6 ±

Замечаний: 0% ±

Excel 2013

bmv98rus, макросом можно конечно, но это крайний случай

buchlotnik, к сожалению - не корректно работает, я уже делал так

К сообщению приложен файл: 9025501.xlsx (11.4 Kb)

Ответить

bmv98rus

Дата: Среда, 22.02.2017, 20:42 | Сообщение № 9

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4107

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

Щас проверю вариант тезки, ну а пока крайний вариант

[vba]

Код

Function CountUnicalVisible(ByVal RangeArea As Range) As Long
    Dim objDict As Variant
    Set objDict = CreateObject("Scripting.Dictionary")
    For Each TheCell In RangeArea
        If Not TheCell.Parent.Rows(TheCell.Row).Hidden And _
            Not TheCell.Parent.Columns(TheCell.Column).Hidden And _
             Not objDict.exists(TheCell.Value) Then _
             objDict.Add TheCell.Value, ""
    Next
    CountUnicalVisible = objDict.Count
    objDict = Empty
End Function

[/vba]

К сообщению приложен файл: Copy_of_6975764.xlsm (17.5 Kb)

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

Pelena

Дата: Среда, 22.02.2017, 20:48 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 19179

Репутация: 4419 ±

Замечаний: ±

Excel 365 & Mac Excel

lFJl, надо было сразу написать, что Вы по цвету фильтруете. Если фильтровать по значению, то формула Михаила работает

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

buchlotnik

Дата: Среда, 22.02.2017, 20:48 | Сообщение № 11

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация: 929 ±

Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG

Цитата

к сожалению - не корректно работает

а где в первом посте хоть слово о том, что вы фильтруете по ЦВЕТУ? Тогда уж точно - только VBA

Цитата

я уже делал так

об этом тоже ни слова не увидел

Сообщение отредактировал buchlotnik - Среда, 22.02.2017, 21:07

Ответить

lFJl

Дата: Среда, 22.02.2017, 21:10 | Сообщение № 12

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 236

Репутация: 6 ±

Замечаний: 0% ±

Excel 2013

Pelena, buchlotnik, Ого, пардон, предположил, что будет разница от числовых значений и текстовых, перемешал. Не думал, что по цвету разница есть!

На самом деле у меня таблица больше, и фильтр не обязательно в этом столбце будет.
bmv98rus, Этого достаточно для моей цели, что - то я про функции забыл совсем

Но все же реализация формулой интересна

Ответить

Pelena

Дата: Четверг, 23.02.2017, 09:08 | Сообщение № 13

Группа: Админы

Ранг: Местный житель

Сообщений: 19179

Репутация: 4419 ±

Замечаний: ±

Excel 365 & Mac Excel

Цитата lFJl, 22.02.2017 в 21:10, в сообщении № 12 ()

реализация формулой интересна

Да пожалуйста

Код

=СУММПРОИЗВ(--(ПОИСКПОЗ(база[Группа];база[Группа];0)=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ(база[[#Заголовки];[Группа]];СТРОКА(база[Группа])-1;))*(СТРОКА(база[Группа])-1)))

Коверкается формула, вставлю без тегов
=СУММПРОИЗВ(--(ПОИСКПОЗ(база[Группа];база[Группа];0)=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ(база[[#Заголовки];[Группа]];СТРОКА(база[Группа])-1;))*(СТРОКА(база[Группа])-1)))
Есть ещё более универсальный вариант, правда формула массива
=СУММПРОИЗВ(--(ПОИСКПОЗ(Т(СМЕЩ(база[[#Заголовки];[Группа]];ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ(база[[#Заголовки];[Группа]];СТРОКА(база[Группа])-1;))*(СТРОКА(база[Группа])-1);));Т(СМЕЩ(база[[#Заголовки];[Группа]];ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ(база[[#Заголовки];[Группа]];СТРОКА(база[Группа])-1;))*(СТРОКА(база[Группа])-1);));0)=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ(база[[#Заголовки];[Группа]];СТРОКА(база[Группа])-1;))*(СТРОКА(база[Группа])-1)))

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Lama77

Дата: Пятница, 14.04.2017, 15:53 | Сообщение № 14

Группа: Пользователи

Ранг: Прохожий

Сообщений: 6

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

buchlotnik, добрый день, не могли бы прописать формулу в моем файле, что то никак не получается применить.
Мне нужно считать количество магазинов в каждом регионе. То есть фильтром выбираю сначала регион "Север 1", плюс отфильтровываю еще несколько столбцов по другим параметрам. В итоге мне нужно посчитать сколько магазинов попало в данный регион с учетом всех фильтров. Изначально название каждого магазина повторяется в столбце по 7 семь раз. Но при отфильтровке какой то магазин может повториться 5, какой то 7, какой то 3 раза и тп. Мне нужно посчитать без учета повторов, только уникальные значения.

Заранее спасибо! Файл прилагаю