Sakiet, ka vēlaties uzskaitīt unikālus vienumus no saraksta, taču ar pagriezienu. Un sakiet, ka strādājat ar šo darblapu:
D slejā tiek skaitīts rindu skaits katrā no B slejas sadaļām, un C slejā tiek uzskaitīts unikālo sadaļu skaits, pamatojoties uz šīs sadaļas A slejas pirmajām piecām rakstzīmēm. Šūnās B2: B11 ir ARG, un A2: A11 pirmajās piecās rakstzīmēs varat saskaitīt astoņus unikālus vienumus, jo A7: A9 katrā ir 11158, tāpēc abi dublikāti netiek skaitīti. Līdzīgi 5 D12 norāda, ka BRD ir piecas rindas, bet 12:16 rindās ir trīs unikāli vienumi no pirmajām piecām rakstzīmēm, jo 11145 tiek atkārtots un 11173 tiek atkārtots.
Bet kā jūs liekat Excel to darīt? Kādu formulu jūs varētu izmantot C2, ko varētu nokopēt uz C12 un C17?
Vienkāršā skaitīšanas formula D2, =COUNTIF(B:B,B2)saskaita reižu skaitu B2 (ARG) B kolonnā.
Palīdzības kolonnu izmantojat, lai izolētu pirmās piecas kolonnas A rakstzīmes, kā parādīts šajā attēlā:
Tālāk jums kaut kā jānorāda, ka ARG jūs interesē tikai šūnas F2: F11, lai atrastu unikālo vienumu skaitu. Parasti šo vērtību atradīsit, izmantojot masīva formulu, kas parādīta šajā attēlā:
Šūnu C3 īslaicīgi izmantojat tikai, lai parādītu formulu; jūs varat redzēt, ka iepriekšējos attēlos tā nav C3. (Jūs drīz uzzināsiet, kā šī formula darbojas.)
Kāda tad ir formula C2, C12 un C17? Pārsteidzošā (un lieliskā) atbilde ir parādīta šajā attēlā:
Akā! Kā tas darbojas?
Apskatiet Atbildi definētajos nosaukumos šajā attēlā:
Tā ir tā pati formula no iepriekšējā attēla, taču tā vietā, lai izmantotu diapazonu F2: F11, tā izmanto diapazonu ar nosaukumu Rg. Arī formula bija masīva formula, bet ar nosauktajām formulām rīkojas tā, it kā tās būtu masīvu formulas! Tas ir, =Answernetiek ievadīts ar Ctrl + Shift + Enter, bet tiek vienkārši ievadīts kā parasti.
Tātad, kā tiek definēts Rg? Ja ir atlasīta šūna C1 (kas ir svarīgs solis šī trika izpratnei), tad tā tiek definēta kā šajā attēlā:
Tas ir =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 ir lapas nosaukums, taču jūs varat apskatīt šo formulu bez garās lapas nosaukuma. Vienkāršs veids, kā to izdarīt, ir īslaicīgi nosaukt lapu kaut kā vienkārši, piemēram, x, un pēc tam vēlreiz apskatīt definēto nosaukumu:
Šo formulu ir vieglāk lasīt!
Jūs varat redzēt, ka šī formula atbilst $ B1 (ņemiet vērā relatīvo atsauci uz pašreizējo rindu) pret visu B sleju un atņemot 1. Jūs atņemat 1, jo izmantojat OFFSET no F1. Tagad, kad zināt par C formulu, apskatiet C2 formulu:
MATCH($B2,$B:$B,0)Daļa no formulas ir 2, tā formula (bez atsauces uz lokšņu nosaukumu) ir:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
vai:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
vai:
=OFFSET($F$1,1,0,10,1)
Tā kā COUNTIF($B:$B,$B2)ir 10, ir 10 ARG. Tas ir diapazons F2: F11. Faktiski, ja ir atlasīta šūna C2 un nospiežat F5, lai pārietu uz Rg, jūs redzat šo:
Ja sākuma šūna bija C12, nospiežot F5, lai pārietu uz Rg, rodas šādi:
Tātad tagad, kad Atbilde ir definēta kā =SUM(1/COUNTIF(rg,rg)), viss ir pabeigts!
Apskatīsim rūpīgāk, kā šī formula darbojas, izmantojot daudz vienkāršāku piemēru. Parasti COUNTIF sintakse ir =COUNTIF(range,criteria)tāda, kā =COUNTIF(C1:C10, "b")šajā attēlā:
Tas dotu 2 kā b skaitu diapazonā. Bet, pārsniedzot diapazonu kā kritēriju, katrs kritērijs tiek izmantots diapazonā. Ja iezīmējat šo formulas daļu:
un nospiediet F9, redzat:
Katrs diapazona elements tiek novērtēts, un šī skaitļu sērija nozīmē, ka ir viens a un ir divi b, trīs c un četri d. Šie skaitļi ir sadalīti 1, dodot 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, kā redzat šeit:
Tātad jums ir 2 pusītes, 3 trešdaļas, 4 ceturtdaļas un 1 veselums, saskaitot tos kopā, iegūstat 4. Ja prece tiktu atkārtota 7 reizes, tad jums būtu 7 septītās daļas un tā tālāk. Diezgan vēss! (Cepure nost Deividam Hāgeram par šīs formulas atklāšanu / izgudrošanu.)
Bet turieties uz minūti. Pašreizējā formā šī formula jāievada tikai C2, C12 un C17. Vai nebūtu labāk, ja jūs to varētu ievadīt C2 un aizpildīt un parādīt tikai pareizajās šūnās? Patiesībā jūs to varat izdarīt. Varat modificēt formulu C2, lai tā būtu =IF(B1B2,Answer,""), un, aizpildot to, tas veic darbu:
Bet kāpēc apstāties šeit? Kāpēc neveidot formulu nosauktajā formātā, kā parādīts šeit:
Lai tas darbotos, šūnai C2 jābūt aktīvai šūnai (vai formulai būtu jāatšķiras). Tagad jūs varat aizstāt C slejas formulas ar =Answer2:
Var redzēt, ka C3 ir =Answer2tāpat kā visām šūnām C slejā. Kāpēc neturpināt to D slejā? Formula D2, pēc salīdzinājuma piemērošanas arī B1 un B2, ir parādīta šeit:
Tātad, ja paturat atlasītu šūnu D2 un definējat citu formulu, sakiet Answer3:
tad jūs varat ievadīt =Answer3šūnā D2 un aizpildīt:
Šeit ir darblapas augšdaļa, kurā redzamas formulas, kam seko tas pats ekrānuzņēmums ar vērtībām:
Kad citi cilvēki mēģina to noskaidrot, viņi sākumā var saskrāpēt galvu!
Šis viesu raksts ir no Excel MVP Bob Umlas. Tas ir no grāmatas Vairāk Excel ārpus kastes. Lai skatītu citas grāmatas tēmas, noklikšķiniet šeit.
