Скажем, вы хотите иметь возможность подсчитывать уникальные элементы из списка, но с изюминкой. Допустим, вы работаете с этим листом:
В столбце 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 (что является важным шагом для понимания этого трюка), то она определяется, как на этом рисунке:
Вот это =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:
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, вы увидите следующее:
Если начальная ячейка была C12, нажатие F5 для перехода к Rg дает следующее:
Итак, теперь, когда ответ определяется как =SUM(1/COUNTIF(rg,rg)), все готово!
Давайте подробнее рассмотрим, как работает эта формула, на гораздо более простом примере. Обычно синтаксис COUNTIF =COUNTIF(range,criteria)такой, как =COUNTIF(C1:C10, "b")на этом рисунке:
Это даст 2 как количество b в диапазоне. Но при передаче самого диапазона в качестве критерия каждый элемент диапазона используется в качестве критерия. Если выделить эту часть формулы:
и нажмите 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 показана здесь:
Итак, если вы сохраните выделенную ячейку D2 и определите другую формулу, скажем, Ответ3:
затем вы можете ввести =Answer3ячейку D2 и заполнить:
Вот верхняя часть листа с формулами и тем же снимком экрана со значениями:
Когда другие люди пытаются это понять, они сначала могут почесать затылок!
Это гостевая статья от Боба Умласа, MVP по Excel. Это из книги «Больше Excel вне коробки». Чтобы увидеть другие темы в книге, щелкните здесь.
