У меня есть большая таблица, которая уже организована с использованием фильтров и т.д. Я хотел бы добавить сводку под определенными столбцами, которые содержат количество различных значений в этом столбце.
Там нет функции =COUNTDISTINCT(A2:A100) так что я могу сделать вместо этого? (Excel 2003)
Я не могу точно использовать ответы на этот похожий вопрос, потому что я не хочу изменять таблицу или фильтрацию. Мне нужно дополнение на листе, а не модификация.
=SUMPRODUCT((A2:A100 <> "")/COUNTIF(A2:A100,A2:A100 & ""))
будет делать это без необходимости использовать формулу массива.
Я нашел здесь решение, которое кажется невероятным окольным путем. Но эй, это работает ...
= СУММА (ЕСЛИ (СЧЕТЧИК (A2:A100, A2:A100) = 0, "", 1/ СЧЕТЧИК (A2:A100, A2:A100)))
и затем нажмите Ctrl+Shift+Enter. Нажатие только Enter даст неправильный результат.
Найдены два ресурса для вас:
http://www.excelforum.com/excel-worksheet-functions/365877-count-distinct-values.html
а также
http://www.cpearson.com/excel/Duplicates.aspx
Вы должны быть в состоянии найти работоспособное решение оттуда.
Эта статья показывает это для текстовых значений:
=SUM(IF(FREQUENCY(IF(LEN(C3:C25)>0,MATCH(C3:C25,C3:C25,0),""), IF(LEN(C3:C25)>0,MATCH(C3:C25,C3:C25,0),""))>0,1))
и это для числовых значений:
=SUM(IF(FREQUENCY(C3:C25, C3:C25)>0,1))
В этой статье показаны аналогичные формулы, но также показан метод с использованием фильтров.
Подсчитайте количество уникальных значений с помощью фильтра
Вы можете использовать расширенный фильтр, чтобы извлечь уникальные значения из столбца данных и вставить их в новое место. Затем вы можете использовать функцию ROWS для подсчета количества элементов в новом диапазоне.
- Убедитесь, что первая строка в столбце имеет заголовок столбца.
- В меню «Данные» выберите пункт «Фильтр» и нажмите «Расширенный фильтр».
- В диалоговом окне «Расширенный фильтр» нажмите «Копировать в другое место».
- Если подсчитываемый диапазон еще не выбран, удалите любую информацию в поле «Диапазон списка», а затем щелкните столбец (или выберите диапазон), содержащий ваши данные.
- В поле «Копировать в» удалите любую информацию в поле или щелкните в поле, а затем щелкните пустой столбец, в который требуется скопировать уникальные значения.
Установите флажок «Только уникальные записи» и нажмите «ОК».
Уникальные значения из выбранного диапазона копируются в новый столбец.
В пустой ячейке под последней ячейкой диапазона введите функцию ROWS. Используйте диапазон уникальных значений, которые вы только что скопировали в качестве аргумента. Например, если диапазон уникальных значений B1:B45, введите:
= ЧСТРОКИ (B1:B45)
= СУММ (1/ СЧЕТЕСЛИ (А2: А100; A2: A100))
Подтвердите с помощью Ctrl+Shift+Enter
Для каждой ячейки он подсчитывает, сколько раз это происходит, и суммирует обратные значения всех этих значений. Предположим, что некоторые строки или числа окк 5 раз. Его обратное значение равно 0,2, которое суммируется 5 раз, поэтому добавляется 1. В конце он дает количество разных значений.
Примечание: не работает, когда возникают пробелы!
= СУММ (ЕСЛИ (частоты (ПЧ (ИТОГО (3, OFFSET (А2: А100, ROW (A2: A100)-строка (А2), 0,1)), ПОИСКПОЗ ("~" & А2: А100, А2: А100 &» », 0)), ROW (A2: A100)-строка (А2)+1), 1))
Обязательно нажимайте CONTROL+SHIFT+ENTER после вставки этой формулы. Это для диапазона A2:A100, отрегулируйте диапазон соответствующим образом.
Попробуйте эту ссылку. Это показывает, как считать уникальные значения в списке, пропуская пустые ячейки.
http://www.functioninexcel.com/lists-arrays/count-unique-values-in-a-list/
= сумма (если (частота (совпадение (список, список, 0), совпадение (список, список, 0))> 0, 1))
Где "Список" - это ваш диапазон ячеек, например:
Список = $ A $ 2:$ A $ 12 ИЛИ- Список = смещение ($ A $ 1 ,,, match (rept ("z", 255), $ A:$ A)) -OR- Список = смещение ($ A $ 1, ,, match (value (rept ("9", 255)), $ A:$ A))