Vai jūs zināt lielisko triku, kā izveidot atsauci uz kolonnas virsrakstu rindas pēdējā tukšajā šūnā? Labākais: jums nav vajadzīgas palīglīnijas vai kolonnas. Tas ir tik vienkārši:
Apvienojiet četras funkcijas IFERROR (), INDEX (), MAX () un IF ()
Pārdošanas rīkotājdirektors nosūtīs jums sarakstu ar līgumiem, kas mēnesī tiek noslēgti par produktiem, kuru darbība tiek pārtraukta ①. Jums vajadzētu izmantot formulu N slejā, lai norādītu katra produkta pēdējo pārdošanas mēnesi - bez palīgrindām vai kolonnām. Ja vairs nav noslēgti līgumi, ievadiet tukšu šūnu N slejā.
Šis no pirmā acu uzmetiena banāls piemērs izrādās grūts rieksts, neizmantojot palīglīnijas vai kolonnas. kā Excel praksei-Lasītāji lauž riekstu! Šūnā mums ir šāda masīva formula problēmu risināšanai N2 izveidots:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; SLEJA (B2: M2) -1; -1))); "")}
Soli pa solim apskatiet, kā tas darbojas
Sāciet ar IF () nosacījumu, kas iebūvēts masīva formulā: {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", SLEJA (B2: M2) -1, -1)));"")}
IF () nosacījums masīva formulā izveido fiktīvu palīglīniju un pārbauda šūnas B2 līdz M2vai tie ir tukši vai nē. Ja šūna ir tukša, vērtība -1 tiek atgriezta, izmantojot funkciju COLUMN (), attiecīgā kolonnas numurs mīnus vērtība 1.
Formulā ir nepieciešams atņemt 1, jo tabulas pirmajā slejā ir nevis mēneša nosaukums, bet gan produkta nosaukums. Tālāk jūs uzzināsit, kā izmantot funkciju INDEX (), lai parādītu atbilstošo mēneša nosaukumu, kas - ja jūs neatņēmāt 1 - izmantotās papildu slejas dēļ A. būtu nepareizi ar vienu kolonnu.
Ja visas šūnas diapazonā B2: M2 ir tukšas, vērtība -1 (bez produktu pārdošanas) rada kļūdu, ko izmantojam, lai attēlotu tukšu šūnu. Aktīvo palīglīniju var redzēt 3. līnijas ③ zīm.
Nākamajā solī jūs nolasāt lielāko vērtību ar funkciju MAX (), kurā IF () nosacījums ir ligzdots. Šī ir vērtība 12 3. rindā (13. sleja mīnus 1; sk. Palīgrindu Figure attēlā):
{= IFERROR (INDEKSS ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", SLEJA (B2: M2) -1, -1)));"")}
Jūs nododat šo MAX vērtību funkcijai INDEX (). Pēc tam 1. rindā tiek nolasīts atbilstošais kalendārais mēnesis. Funkcijas INDEX () datu apgabals ir apgabals $ B $ 1: $ M $ 1. Nokārtotā MAX vērtība - 12. piemērā - nozīmē, ka saraksta divpadsmitā vērtība, t.i. Dec decembrī:
{= IFERROR(INDEKSS ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; SLEJA (B2: M2) -1; -1)));"")}
Ja visas tabulas mēneša diapazona šūnas ir tukšas, tad lielākā vērtība ir -1 (sk. IF () nosacījumu sākumā). Ja vērtība -1 tiek nodota funkcijai INDEX (), tas neizbēgami noved pie kļūdas vērtības, jo funkcijas INDEX () saraksta apgabalā ir tikai divpadsmit ieraksti un tāpēc nevar atrast ierakstu -1. Jūs noķerat šo kļūdas vērtību, izmantojot funkciju IFERROR (), un tā vietā atdodat tukšu virkni. Piemērā tas attiecas uz 4. rindas produktu C, jo par šo produktu nevarēja noslēgt vairāk līguma:
{=IFERROR(INDEKSS ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", SLEJA (B2: M2) -1, -1)));"")}
Tā kā šī ir masīva formula, aizpildiet formulas ievadi ar taustiņu kombināciju Ctrl + Shift + Enter.
