Реците да желите да имате могућност бројања јединствених предмета са листе, али са заокретом. И рецимо да радите са овим радним листом:
Колона Д броји број редова у сваком одељку из колоне Б, а колона Ц број јединствених одељака на основу првих пет знакова колоне А за тај одељак. Ћелије Б2: Б11 садрже АРГ, а у првих пет знакова А2: А11 можете избројати осам јединствених предмета, јер А7: А9 садрже по 11158, тако да се два дупликата не броје. Слично томе, 5 у Д12 говори вам да постоји пет редова за БРД, али унутар редова 12:16 налазе се три јединствена предмета од првих пет знакова, јер се понавља 11145 и понавља 11173.
Али како рећи Екцел-у да то уради? А коју бисте формулу могли користити у Ц2 која би се могла копирати на Ц12 и Ц17?
Једноставна формула за бројање у Д2, =COUNTIF(B:B,B2)броји колико пута Б2 (АРГ) постоји у колони Б.
Помоћу помоћне колоне изолујете првих пет знакова колоне А, као на овој слици:
Даље, морате некако да назначите да вас за АРГ занимају само ћелије Ф2: Ф11 да бисте пронашли број јединствених предмета. Генерално, ову вредност бисте пронашли помоћу формуле низа приказане на овој слици:
Привремено користите ћелију Ц3 само да бисте приказали формулу; можете видети да није присутан у Ц3 на претходним сликама. (Убрзо ћете сазнати како функционише ова формула.)
Па, која је формула у Ц2, Ц12 и Ц17? Изненађујући (и кул) одговор приказан је на овој слици:
Вхоа! Како ово ради?
Погледајте Ансвер у дефинисаним именима на овој слици:
То је иста формула са раније слике, али уместо да користи опсег Ф2: Ф11, користи опсег под називом Рг. Такође, формула је била формула низа, али именоване формуле се третирају као да су формуле низа! Односно, =Answerне уноси се Цтрл + Схифт + Ентер, већ се једноставно уноси као и обично.
Па, како се дефинише Рг? Ако је изабрана ћелија Ц1 (што је важан корак за разумевање овог трика), онда је дефинисана као на овој слици:
То је то =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).
Лоан_Детаилс је име листа, али ову формулу можете погледати без дугог имена листа. Једноставан начин да то урадите је да привремено именујете лист тако једноставно, као што је к, а затим поново погледате дефинисано име:
Ову формулу је лакше прочитати!
Можете видети да се ова формула подудара са $ Б1 (имајте на уму релативну референцу на тренутни ред) у односу на све колоне Б и одузима 1. Одузимате 1 јер користите ОФФСЕТ од Ф1. Сад кад знате за формулу за Ц, погледајте ону за Ц2:
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 АРГ-ова. Ово је опсег Ф2: Ф11. Заправо, ако је одабрана ћелија Ц2 и притиснете Ф5 да бисте прешли на Рг, видећете ово:
Ако је почетна ћелија била Ц12, притиском на Ф5 за прелазак на Рг настаје ово:
Дакле, са одговором дефинисаним као =SUM(1/COUNTIF(rg,rg)), готови сте!
Погледајмо пажљивије како функционише ова формула, користећи много једноставнији пример. Синтакса за ЦОУНТИФ је обично =COUNTIF(range,criteria), као =COUNTIF(C1:C10, "b")на овој слици:
То би дало 2 као број б у опсегу. Али прослеђивање самог опсега као критеријума користи сваку ставку у опсегу као критеријуме. Ако истакнете овај део формуле:
и притисните Ф9, видећете:
Процењује се свака ставка у опсегу, а ова серија бројева значи да постоје један а и два б, три ц и четири д. Ови бројеви су подељени на 1, дајући 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, као што овде можете видети:
Дакле, имате 2 половине, 3 трећине, 4 четвртине и 1 целину, а њихово сабирање даје 4. Ако би се нека ствар поновила 7 пута, тада бисте имали 7 седмица и тако даље. Прилично кул! (Капа доле Давиду Хагеру због откривања / измишљања ове формуле.)
Али сачекај минут. Како стоји, ову формулу морате унети само у Ц2, Ц12 и Ц17. Зар не би било боље када бисте могли да га унесете у Ц2 и попуните и прикажете само у исправним ћелијама? У ствари, ви то можете. Можете да модификујете формулу у Ц2 да буде =IF(B1B2,Answer,"")и када то попуните, то обавља посао:
Али зашто се овде зауставити? Зашто не направити формулу у именовану формулу, као што је овде приказано:
Да би ово функционисало, ћелија Ц2 мора бити активна ћелија (или би формула требало да буде другачија). Сада формуле колоне Ц можете заменити са =Answer2:
Можете видети да Ц3 има =Answer2, као и све ћелије у колони Ц. Зашто то не бисте наставили у колони Д? Формула у Д2, након примене упоређивања на Б1 и Б2, приказана је овде:
Дакле, ако задржите ћелију Д2 одабраном и дефинишете другу формулу, реците Ансвер3:
онда можете да унесете =Answer3у ћелију Д2 и попуните:
Ево горњег дела радног листа са приказаним формулама, праћеним истим снимком екрана са приказаним вредностима:
Када други људи покушају да то схвате, прво би се могли почешати по глави!
Овај гостујући чланак је из програма Екцел МВП Боб Умлас. Из књиге је, Још Екцел изван оквира. Да бисте видели остале теме у књизи, кликните овде.
