Если Нужно количество значений без повторений (т.е. уникальных), то так можно, хотя может и проще можно, я не думал пока!

DeggasadЕсли Нужно количество значений без повторений (т.е. уникальных), то так можно, хотя может и проще можно, я не думал пока!


Вместо
$B$1:$B$10 , свой диапазон!

Да, это только для чисел
=СУММ(--(ЧАСТОТА($B$1:$B$10;$B$1:$B$10)>0))

Для текста

{=СУММ(ЕСЛИ(ЕОШ(1/СЧЁТЕСЛИ($A$1:$A$13;$A$1:$A$13));0;1/СЧЁТЕСЛИ($A$1:$A$13;$A$1:$A$13)))}
либо

{=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A1:A13)>0;ПОИСКПОЗ(A1:A13;A1:A13;0);"");ЕСЛИ(ДЛСТР(A1:A13)>0;ПОИСКПОЗ(A1:A13;A1:A13;0);""))>0;1))}

Содрал по ссылке не моё!

Ещё раз переосмыслил:

Это неверно (не работает когда есть и нулевые и пустые ячейки в диапазоне)
{=СУММ(ЕСЛИ(ЕОШ(1/СЧЁТЕСЛИ($A$1:$A$13;$A$1:$A$13));0;1/СЧЁТЕСЛИ($A$1:$A$13;$A$1:$A$13)))}

Это лучший вариант:
{=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A1:A13)>0;ПОИСКПОЗ(A1:A13;A1:A13;0);"");ЕСЛИ(ДЛСТР(A1:A13)>0;ПОИСКПОЗ(A1:A13;A1:A13;0);""))>0;1))}

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

=ОКРУГЛ(СУММПРОИЗВ(1/(СЧЁТЕСЛИ($A$1:$A$13;$A$1:$A$13)+0,000001)*($A$1:$A$13<>""));0)

KL (XL)

=СУММПРОИЗВ(($A$1:$A$13<>"")/СЧЁТЕСЛИ($A$1:$A$13;""&$A$1:$A$13))

{=СУММ(--(ЕСЛИ($A$1:$A$13<>"";ПОИСКПОЗ($A$1:$A$13;$A$1:$A$13;0))=(СТРОКА($A$1:$A$13)-СТРОКА($A$1)+1)))}

=СУММПРОИЗВ(--(ПОИСКПОЗ(""&$A$1:$A$13;""&$A$1:$A$13;0)=(СТРОКА($A$1:$A$13)-СТРОКА($A$1)+1)))

KL
[MVP - Microsoft Excel]

Привет, KL (XL) .
Открой секрет пожалуйста! Как правктически работает приставка типа ""& в формулах СЧЁТЕСЛИ() и ПОИСКПОЗ() я понял - избавляет от ошибок при пустых ячейках в диапазоне(это моё видение, если что поправте), но охота уяснить для себя почему так. Пояснение вообщем нужно что даёт эта приставка, не поможешь? А то сам никак?

KL (XL)
Такие варианты:

не считает пусто как значение, сравнительно быстрая, короткая, не требует ввода через CTRL+SHIFT+ENTER
=СУММПРОИЗВ(($A$1:$A$13<>"")/СЧЁТЕСЛИ($A$1:$A$13;""&$A$1:$A$13))

не считает пусто как значение, очень быстрая, длинная, требует ввода через CTRL+SHIFT+ENTER
{=СУММ(--(ЕСЛИ($A$1:$A$13<>"";ПОИСКПОЗ($A$1:$A$13;$A$1:$A$13;0))=(СТРОКА($A$1:$A$13)-СТРОКА($A$1)+1)))}

считает пусто как значение, сравнительно быстрая, сравнительно короткая, не требует ввода через CTRL+SHIFT+ENTER
=СУММПРОИЗВ(--(ПОИСКПОЗ(""&$A$1:$A$13;""&$A$1:$A$13;0)=(СТРОКА($A$1:$A$13)-СТРОКА($A$1)+1)))

KL
[MVP - Microsoft Excel]

Кстате, чтобы посчитать пусто как значения, если я правильно понимаю, в первой формуле нужно сделать следующие изменения:
=СУММПРОИЗВ(1/СЧЁТЕСЛИ($A$1:$A$13;""&$A$1:$A$13))
или не так?
Если так то необходимость в остальных формулах впринципе отпадает!

KL (XL) DeggasadКстати, чтобы посчитать пусто как значения, если я правильно понимаю, в первой формуле нужно сделать следующие изменения:
=СУММПРОИЗВ(1/СЧЁТЕСЛИ($A$1:$A$13;""&$A$1:$A$13))
или не так?
Если так то необходимость в остальных формулах впринципе отпадает!

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

KL
[MVP - Microsoft Excel]
Да, ещё чуть поэксперементировал. Действительно к ошибкам по разному относятся!

KL (XL)Привет Deggasad ,

DeggasadПривет, KL (XL) .
Открой секрет пожалуйста! Как правктически работает приставка типа ""& в формулах СЧЁТЕСЛИ() и ПОИСКПОЗ() я понял - избавляет от ошибок при пустых ячейках в диапазоне(это моё видение, если что поправте), но охота уяснить для себя почему так. Пояснение вообщем нужно что даёт эта приставка, не поможешь? А то сам никак?

В целом ты уловил суть. Ну а в деталях:

1) СЧЁТЕСЛИ() игнорирует пустые ячейки как критерий (и соответственно возвращает 0), но считает текстовые строки нулевой длинны (""). Причем текстовую строку нулевой длинны он воспринимает именно как критерий пустой ячейки. Кстати такое поведение свойственно для большинства функций Excel. Данная функция работает с критериями как в текстовом так и в числовом и логическом форматах (те, что вводятся в числовом или логическом формате, она похоже форсированно конвертирует в текст). Интересно, что хотя эта конвертация и не распространяется на значения ошибки [впрочем это тоже нормально для всех функций], но при этом возможно например такое: =СЧЁТЕСЛИ(A1:A10;"#Н/Д").

2) Применительно к формулам типа 1/СЧЁТЕСЛИ($A$1:$A$13;A$1): если в критерии есть пустая ячейка, то СЧЁТЕСЛИ вернет 0 и последующая операция деления вернет ошибку деления на 0.

3) ПОИСКПОЗ() ведет себя несколько иначе. При попытке подстановки пустой ячейки как критерия, она сразу возвращает #Н/Д. Текстовую строку нулевой длинны эта функция находит, но только если таковые есть в диапазоне поиска. Т.е. текстовая строка нулевой длинны для ПОИСКПОЗ() не соответствует пустым ячейкам как для СЧЁТЕСЛИ(). Отсюда необходимость &"" как в первом так и во втором параметрах ПОИСКПОЗ().

KL
[MVP - Microsoft Excel]

Спасибо за объяснение, стало намного понятнее!

Что ещё интересное заметил. Если в критерии функции СЧЁТЕСЛИ есть пустая ячейка, а в диапазоне есть нулевые значения, то СЧЁТЕСЛИ вернет не 0 (как в случае без нулевых значений), а количество нулей в диапазоне (именно нулей, не считая пустых значений).
На мой взгляд это не совсем верно. Т.к. и пусто и ноль в критерии считают только нули в диапазоне. Ситуация выравнивается, только методом, который ты подсказал. Получается что только после добавления &"" , функция работает более менее верно (если пользователя устраивает подсчёт пустых значений), а изначально она глюченая.

KL (XL) Ну уж это дело хозяйское ;-)

Я по началу не понял что значит по автофильтру посчитать. Это взять автофильтр развернуть и до 300 глазками считать! Так что действительно дело хозяйское!

SНеужели все так сложно?
Нет не сложно если тебе без заморочек:
1)Выделяешь столбец с данными и данные - сводная таблица делаешь, а потом считаешь сколько значений получилось - они там уникальные.
2)Выделяешь столбец с данными и данные - фильтр - расширенный фильтр. Там выбираешь куда поместить и указываешь только уникальные значения. И опять же считаешь что получилось.
3)Ещё есть куча простых и понятных вариантов с использованием дополнительного столбца, но их я не указывал, т.к. это не интересно и тем более ты не просил попроще.


SКстати, в том, для чего мне это понадобилось, встречаются 13-ти значные коды, в которых содержатся как цифры, так и буквы. Значения повторяются и поэтому необходимо было посчитать кол-во встречающихся уникальных значений.

SСделал это дедовским способом, путем использованием автофильтра и ручного подсчета в этом автофильтре предлагаемых варианто. Было немного, штук под 300 :).

Деды то наши, автофильтров в глаза не видели, скорее наоборот формылу или даже код какой-нить! Так что способ никак не дедовский, а наоборот попсовый!