Використання змінних діапазонів для унікальних підрахунків - поради 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, щоб знайти кількість унікальних елементів. Загалом, ви могли б знайти це значення, використовуючи формулу масиву, показану на цьому малюнку:

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

Ви тимчасово використовуєте клітинку С3 лише для відображення формули; ви можете бачити, що він не присутній у C3 на попередніх малюнках. (Незабаром ви дізнаєтесь, як працює ця формула.)

То яка формула в С2, С12 та С17? Дивовижна (і крута) відповідь показана на цьому малюнку:

Дивовижна відповідь

Вау! Як це працює?

Погляньте на відповідь у визначених іменах на цьому малюнку:

Визначені імена в диспетчері імен

Це та сама формула з попереднього малюнка, але замість того, щоб використовувати діапазон 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, оскільки використовуєте OFFSET з 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. Насправді, якщо вибрано комірку С2 і ви натискаєте клавішу F5, щоб перейти до Rg, ви бачите таке:

Перейти до діалогового вікна

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

Якщо початковою коміркою був C12, натискання клавіші F5 для переходу до Rg видає таке:

Початкова комірка як C12

Тож тепер, коли відповідь визначено як =SUM(1/COUNTIF(rg,rg)), ви все готово!

Давайте більш детально розглянемо, як працює ця формула, на набагато простішому прикладі. Зазвичай синтаксис для COUNTIF =COUNTIF(range,criteria)такий, як =COUNTIF(C1:C10, "b")на малюнку:

Формула COUNTIF

Це дало б 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,""), і коли ви заповнюєте це, це робить роботу:

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

Але навіщо тут зупинятися? Чому б не перетворити формулу в названу формулу, як показано тут:

Названа формула

Щоб це працювало, комірка С2 повинна бути активною коміркою (або формула повинна бути іншою). Тепер ви можете замінити формули стовпця C такими =Answer2:

Використовуйте іменовану формулу

Ви можете бачити, що C3 має =Answer2, як і всі клітинки в стовпці C. Чому б не продовжити це в стовпці D? Формула в D2, після застосування порівняння до B1 та B2, показана тут: