VLOOKUP, HLOOKUP și INDEX-MATCH sunt moduri celebre și comune de a căuta valori în tabelul Excel. Dar acestea nu sunt singurele modalități de a căuta valori în Excel. În acest articol, vom învăța cum să utilizați funcția OFFSET împreună cu funcția MATCH în Excel. Da, ați citit-o bine, vom folosi infama funcție OFFSET din Excel pentru a căuta o anumită valoare într-un tabel Excel.
Formula generică,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Citiți cu atenție:
StartCell:Aceasta este celula de pornire a tabelului de căutare. Să presupunem că dacă doriți să căutați în intervalul A2: A10, atunci StartCell va fi A1.
RowLookupValue: Aceasta este valoarea de căutare pe care doriți să o găsiți în rândurile de subStartCell.
RowLookupRange:Acesta este intervalul în care doriți să căutați RowLookupValue. Este gama de mai jos StartCell (A2: A10).
ColLookupValue: Aceasta este valoarea de căutare pe care doriți să o găsiți în coloane (anteturi).
ColLookupRange:Acesta este intervalul în care doriți să căutați ColLookupValue. Este gama din partea dreaptă a StartCell (cum ar fi B1: D1).
Deci, suficient din teorie. Să începem cu un exemplu.
Exemplu: Căutare vânzări utilizând funcția OFFSET și MATCH Din tabelul Excel
Aici avem un eșantion de tabel de vânzări realizat de diferiți angajați în diferite luni.
Acum șeful nostru ne cere să oferim vânzările făcute de Id 1004 în luna iunie. Există multe modalități de a face acest lucru, dar din moment ce aflăm despre formula OFFSET-MATCH, le vom folosi pentru a căuta valoarea dorită.
Avem deja formula generică de mai sus. Trebuie doar să identificăm aceste variabile în acest tabel.
StartCell este B1 aici. RowLookupValue este M1. RowLookupRange este B2: B1o (interval sub celula de pornire).
ColLookupValue se află în celula M2. ColLookupRange este C1: I1 (Range Header în dreapta StartCell).
Am identificat toate variabilele. Să le punem într-o formulă Excel.
Scrieți această formulă în celula M3 și apăsați butonul Enter.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Când apăsați butonul Enter, obțineți rezultatul cu promptitudine. Vânzarea făcută ID 1004 în luna iunie este de 177.
Cum functioneazã?
Funcția OFFSET este de a vă deplasa de la celula inițială dată la rândul și coloanele date și apoi a returna valoarea din acea celulă. De exemplu, dacă scriu OFFSET (B1,1,1), funcția OFFSET va merge la celula C2 (1 celulă în jos, 1 celulă la dreapta) și îi returnează valoarea.
Aici, avem formulăOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Prima funcție MATCH returnează indicele lui M1 (1004) în intervalul B2: B10, care este 4. Acum formula este,OFFSET (B1,4, MATCH (M2, C1: I1,0)).
Următoarea funcție MATCH returnează indicele lui M2 („Jun”) în intervalul C1: I1, care i 7. Acum formula esteOFFSET (B1,4,7).
Acum funcția OFFSET mută pur și simplu 4 celule în jos către celula B1 care o duce la B5. Apoi funcția OFFSET se deplasează la 7 la stânga la B5, care o duce la I5. Aia este. Funcția OFFSET returnează valoarea din celula I5 care 177.
Avantajele acestei tehnici de căutare?
Acest lucru este rapid. Singurul avantaj al acestei metode este că este mai rapid decât celelalte metode. Același lucru se poate face folosind funcția INDEX-MATCH sau funcția VLOOKUP. Dar este bine să cunoașteți câteva alternative. Poate fi util într-o zi.
Deci da, băieți, așa puteți folosi funcția OFFSET și MATCH pentru a căuta valori în tabelele Excel. Sper că a fost suficient de explicativ. Dacă aveți îndoieli cu privire la acest articol sau la orice alt subiect legat de Excel / VBA, întrebați-mă în secțiunea de comentarii de mai jos.
Utilizați INDEX și MATCH pentru a căuta valoarea:Formula INDEX-MATCH este utilizată pentru a căuta dinamic și precis o valoare într-un tabel dat. Aceasta este o alternativă la funcția VLOOKUP și depășește neajunsurile funcției VLOOKUP.
Suma de grupuri OFFSET în rânduri și coloane: Funcția OFFSET poate fi utilizată pentru a însuma dinamic grupul de celule. Aceste grupuri pot fi oriunde în foaie.
Cum se recuperează fiecare valoare n-o dintr-un interval în Excel: Folosind funcția OFFSET din Excel putem prelua valori din rânduri sau coloane alternative. Această formulă oferă ajutor funcției ROW pentru a alterna prin rânduri și funcția COLUMN pentru a alterna în coloane.
Cum se utilizează funcția OFFSET în Excel: Funcția OFFSET este o funcție Excel puternică, subestimată de mulți utilizatori. Dar experții știu puterea funcției OFFSET și cum putem folosi această funcție pentru a realiza unele sarcini magice în formula Excel. Iată elementele de bază ale funcției OFFSET.
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se utilizează funcția Excel VLOOKUP| Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se folosește Excel Funcția COUNTIF| Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția COUNTIF este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.