VLOOKUP, HLOOKUP un INDEX-MATCH ir slaveni un izplatīti veidi, kā Excel tabulā meklēt vērtības. Bet tie nav vienīgie veidi, kā meklēt vērtības programmā Excel. Šajā rakstā mēs iemācīsimies izmantot funkciju OFFSET kopā ar funkciju MATCH programmā Excel. Jā, jūs to izlasījāt pareizi, mēs izmantosim Excel bēdīgi slaveno OFFSET funkciju, lai Excel tabulā uzmeklētu noteiktu vērtību.
Vispārīgā formula,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Uzmanīgi izlasiet šo:
StartCell:Šī ir uzmeklēšanas tabulas sākuma šūna. Pieņemsim, ka, ja vēlaties meklēt diapazonā A2: A10, tad StartCell būs A1.
RowLookupValue: Šī ir uzmeklēšanas vērtība, kuru vēlaties atrast rindās zemStartCell.
RowLookupRange:Šis ir diapazons, kurā vēlaties meklēt RowLookupValue. Tas ir diapazons zemāk StartCell (A2: A10).
ColLookupValue: Šī ir uzmeklēšanas vērtība, kuru vēlaties atrast kolonnās (galvenēs).
ColLookupRange:Šis ir diapazons, kurā vēlaties meklēt ColLookupValue. Tas ir diapazons StartCell labajā pusē (piemēram, B1: D1).
Tātad, pietiek ar teoriju. Sāksim ar piemēru.
Piemērs: Pārdošanas meklēšana, izmantojot funkciju OFFSET un MATCH No Excel tabulas
Šeit mums ir pārdošanas tabulas paraugs, ko dažādos mēnešos veikuši dažādi darbinieki.
Tagad mūsu priekšnieks lūdz mums sniegt pārdošanu, ko jūnija mēnesī veica Id 1004. Ir daudz veidu, kā to izdarīt, bet, tā kā mēs mācāmies par OFFSET-MATCH formulu, mēs tos izmantosim, lai meklētu vēlamo vērtību.
Mums jau ir iepriekš minētā vispārīgā formula. Mums vienkārši jāidentificē šie mainīgie šajā tabulā.
StartCell šeit ir B1. RowLookupValue ir M1. RowLookupRange ir B2: B1o (diapazons zem sākuma šūnas).
ColLookupValue atrodas M2 šūnā. ColLookupRange ir C1: I1 (galvenes diapazons StartCell labajā pusē).
Mēs esam identificējuši visus mainīgos. Ieviesīsim tos Excel formulā.
Ierakstiet šo formulu šūnā M3 un nospiediet ievadīšanas pogu.
| = OFFSET (B1, MATCH (M1, B2: B10,0), Match (M2, C1: I1,0)) |
Nospiežot ievadīšanas pogu, jūs nekavējoties saņemsit rezultātu. Pārdošanas ID 1004 jūnija mēnesī ir 177.
Kā tas darbojas?
Funkcijas OFFSET uzdevums ir attālināties no dotās sākuma šūnas uz norādīto rindu un kolonnas un pēc tam atgriezt vērtību no šīs šūnas. Piemēram, ja es rakstu OFFSET (B1,1,1), funkcija OFFSET pāriet uz šūnu C2 (1 šūna uz leju, 1 šūna pa labi) un atgriež tās vērtību.
Lūk, mums ir formulaOFFSET (B1, MATCH (M1, B2: B10,0), Match (M2, C1: I1,0)).
Pirmā MATCH funkcija atgriež indeksu M1 (1004) diapazonā B2: B10, kas ir 4. Tagad formula ir,OFFSET (B1,4, MATCH (M2, C1: I1,0)).
Nākamā MATCH funkcija atgriež M2 ('Jun') indeksu diapazonā C1: I1, kas i 7. Tagad formula irOFFSET (B1,4,7).
Tagad funkcija OFFSET vienkārši pārvieto 4 šūnas uz leju līdz šūnai B1, kas to pārvieto uz B5. Pēc tam funkcija OFFSET pāriet uz 7 pa kreisi uz B5, kas to pārnes uz I5. Tas ir viss. Funkcija OFFSET atgriež vērtību no šūnas I5, kas 177.
Šīs meklēšanas metodes priekšrocības?
Tas ir ātri. Šīs metodes vienīgā priekšrocība ir tā, ka tā ir ātrāka nekā citas metodes. To pašu var izdarīt, izmantojot funkciju INDEX-MATCH vai funkciju VLOOKUP. Bet ir labi zināt dažas alternatīvas. Kādu dienu tas var noderēt.
Tātad, jā, puiši, šādi jūs varat izmantot funkciju OFFSET un MATCH, lai meklētu vērtības Excel tabulās. Es ceru, ka tas bija pietiekami izskaidrojošs. Ja jums ir šaubas par šo rakstu vai kādu citu ar Excel/VBA saistītu tēmu, jautājiet man komentāru sadaļā zemāk.
Izmantojiet INDEX un MATCH, lai meklētu vērtību:Formulu INDEX-MATCH izmanto, lai dinamiski un precīzi meklētu vērtību attiecīgajā tabulā. Šī ir alternatīva funkcijai VLOOKUP, un tā novērš funkcijas VLOOKUP trūkumus.
Summa pēc OFFSET grupām rindās un kolonnās: Funkciju OFFSET var izmantot, lai dinamiski summētu šūnu grupu. Šīs grupas var atrasties jebkurā lapas vietā.
Kā atgūt katru N vērtību diapazonā programmā Excel: Izmantojot Excel funkciju OFFSET, mēs varam iegūt vērtības no mainīgām rindām vai kolonnām. Šī formula izmanto funkciju ROW, lai mainītu rindas, un funkciju COLUMN, lai mainītos kolonnās.
Kā izmantot funkciju OFFSET programmā Excel: Funkcija OFFSET ir spēcīga Excel funkcija, kuru daudzi lietotāji nepietiekami novērtē. Bet eksperti zina OFFSET funkcijas spēku un to, kā mēs varam izmantot šo funkciju, lai veiktu dažus maģiskus uzdevumus Excel formulā. Šeit ir funkcijas OFFSET pamati.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā izmantot Excel VLOOKUP funkciju| Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot Excel Funkcija COUNTIF| Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Funkcija COUNTIF ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.