Использование диапазонов переменных для уникального подсчета - Советы по Excel

Содержание

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

Образец рабочего листа

В столбце D подсчитывается количество строк в каждом из разделов столбца B, а в столбце C подсчитывается количество уникальных разделов на основе первых пяти символов столбца A для этого раздела. Ячейки B2: B11 содержат ARG, и вы можете насчитать восемь уникальных элементов в первых пяти символах A2: A11, потому что каждый из A7: A9 содержит 11158, поэтому два дубликата не учитываются. Точно так же 5 в D12 говорит вам, что для BRD есть пять строк, но в строках 12:16 есть три уникальных элемента из первых пяти символов, поскольку 11145 повторяется, а 11173 повторяется.

Но как вы скажете Excel сделать это? И какую формулу вы могли бы использовать в C2, которую можно было бы скопировать в C12 и C17?

Простая формула подсчета в D2`` =COUNTIF(B:B,B2)подсчитывает, сколько раз B2 (ARG) существует в столбце B.

Вы используете вспомогательный столбец, чтобы изолировать первые пять символов столбца A, как на этом рисунке:

Столбец помощника

Далее вам нужно каким-то образом указать, что для ARG вас интересуют только ячейки F2: F11, чтобы найти количество уникальных элементов. В общем, вы можете найти это значение, используя формулу массива, показанную на этом рисунке:

Уникальные предметы

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

Итак, какова формула в C2, C12 и C17? Удивительный (и крутой) ответ показан на этом рисунке:

Удивительный ответ

Ого! Как это работает?

Взгляните на Answer в определенных именах на этом рисунке:

Определенные имена в диспетчере имен

Это та же формула, что и на предыдущем рисунке, но вместо диапазона F2: F11 используется диапазон с именем Rg. Кроме того, формула была формулой массива, но именованные формулы обрабатываются так, как если бы они были формулами массива! То есть =Answerне вводится с помощью Ctrl + Shift + Enter, а просто вводится как обычно.

Итак, как определяется Rg? Если выбрана ячейка C1 (что является важным шагом для понимания этого трюка), то она определяется, как на этом рисунке:

Rg Определение

Вот это =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).

Loan_Details - это имя листа, но вы можете просмотреть эту формулу без длинного имени листа. Легкий способ сделать это - временно присвоить листу какое-нибудь простое имя, например x, а затем снова посмотреть на определенное имя:

Более короткая формула

Эту формулу легче читать!

Вы можете видеть, что эта формула соответствует $ B1 (обратите внимание на относительную ссылку на текущую строку) для всего столбца B и вычитает 1. Вы вычитаете 1, потому что вы используете СМЕЩЕНИЕ из F1. Теперь, когда вы знаете формулу для C, взгляните на формулу для C2:

Обновленная формула Rg

MATCH($B2,$B:$B,0)Часть формулы 2, поэтому формула (без ссылки на имя листа) является:

=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)

или:

=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)

или:

=OFFSET($F$1,1,0,10,1)

Поскольку COUNTIF($B:$B,$B2)это 10, есть 10 ARG. Это диапазон F2: F11. Фактически, если выбрана ячейка C2 и вы нажмете F5, чтобы перейти к Rg, вы увидите следующее:

Перейти к диалогу

Rg - выбранный диапазон

Если начальная ячейка была C12, нажатие F5 для перехода к Rg дает следующее:

Начальная ячейка как C12

Итак, теперь, когда ответ определяется как =SUM(1/COUNTIF(rg,rg)), все готово!

Давайте подробнее рассмотрим, как работает эта формула, на гораздо более простом примере. Обычно синтаксис COUNTIF =COUNTIF(range,criteria)такой, как =COUNTIF(C1:C10, "b")на этом рисунке:

СЧЁТЕСЛИ Формула

Это даст 2 как количество b в диапазоне. Но при передаче самого диапазона в качестве критерия каждый элемент диапазона используется в качестве критерия. Если выделить эту часть формулы:

Формула выделения

и нажмите F9, вы увидите:

Нажатие F9

Оценивается каждый элемент в диапазоне, и эта серия чисел означает, что есть один a, два b, три c и четыре d. Эти числа делятся на 1, что дает 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, как вы можете видеть здесь:

альт

Итак, у вас есть 2 половинки, 3 трети, 4 четверти и 1 целое, и их сложение дает 4. Если элемент повторяется 7 раз, у вас будет 7 седьмых и так далее. Довольно круто! (Снимаю шляпу перед Дэвидом Хагером за открытие / изобретение этой формулы.)

Но подожди минутку. В существующем виде вам нужно ввести эту формулу только в C2, C12 и C17. Разве не было бы лучше, если бы вы могли ввести его в C2, заполнить и показать только в правильных ячейках? Фактически, вы можете это сделать. Вы можете изменить формулу в C2, чтобы она была =IF(B1B2,Answer,""), и когда вы заполните ее, она выполнит свою работу:

Скопируйте формулу

Но зачем останавливаться на достигнутом? Почему бы не превратить формулу в именованную формулу, как показано здесь:

Именованная формула

Чтобы это работало, ячейка C2 должна быть активной ячейкой (или формула должна быть другой). Теперь вы можете заменить формулы столбца C на =Answer2:

Используйте именованную формулу

Вы можете видеть, что C3 имеет =Answer2, как и все ячейки в столбце C. Почему бы не продолжить это в столбце D? Формула в D2 после применения сравнения к B1 и B2 показана здесь: