Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parāda, kā lietot Excel OFFSET funkcija programmā Excel, lai izveidotu atsauces nobīdi no sākotnējās šūnas.
OFFSET funkciju pārskats
Funkcija OFFSET Sākas ar noteiktu šūnas atsauci un atgriež šūnas atsauci noteiktu rindu un kolonnu skaitu, kas nobīdīts no sākotnējās atsauces. Atsauces var būt viena šūna vai šūnu diapazons. Nobīde arī ļauj mainīt atsauces lielumu noteiktam rindu/kolonnu skaitam.
(Ievērojiet, kā parādās formulas ievades)
Funkcijas IFERROR sintakse un ieejas:
| 1 | = OFFSET (atsauce, rindas, kolonnas, augstums, platums) |
atsauce - Sākotnējā šūnas atsauce, no kuras vēlaties kompensēt.
rindas - kompensējamo rindu skaits.
cols - kompensējamo kolonnu skaits.
augstums - pēc izvēles: pielāgojiet atsauces rindu skaitu.
platums - pēc izvēles: pielāgojiet atsauces kolonnu skaitu.
Kas ir OFFSET funkcija?
Funkcija OFFSET ir viena no jaudīgākajām izklājlapu funkcijām, jo tā var būt diezgan daudzpusīga. Tas lietotājam dod iespēju noteikt šūnu vai diapazonu dažādās pozīcijās un izmēros.
UZMANĪBU: OFFSET funkcija ir viena no gaistošajām funkcijām. Lielāko daļu laika, strādājot izklājlapā, dators pārrēķinās formulu tikai tad, ja ievades vērtības ir mainījušās. Gaistoša funkcija tomēr pārrēķinās katrs kad veicat izmaiņas jebkurā šūnā. Jāievēro piesardzība, lai nodrošinātu, ka neizraisāt lielu pārrēķināšanas laiku pārmērīgas gaistošo funkciju izmantošanas dēļ vai daudzu šūnu dēļ, kas ir atkarīgas no gaistošas funkcijas rezultāta.
Pamata rindu piemēri
Katru reizi izmantojot funkciju OFFSET, jums jānorāda sākuma punkts vai enkurs. Apskatīsim šo tabulu, lai palīdzētu to saprast:
Mēs izmantosim “Bob” šūnā B3 kā mūsu stiprinājuma punktu. Ja mēs vēlētos iegūt vērtību zemāk (Čārlijs), mēs teiktu, ka vēlamies pārvietot rindu par 1. Mūsu formula izskatīsies šādi
| 1 | = OFFSET (B3, 1) |
Ja mēs gribētu pāriet uz augšu, tas būtu negatīvs. Jūs varat domāt par to, jo rindu skaits samazinās, tāpēc mums ir jāatņem. Tādējādi, lai iegūtu augstāku vērtību (Ādams), mēs rakstītu
| 1 | = Nobīde (B2, -1) |
Pamata kolonnu piemēri
Turpinot iepriekšējā piemēra ideju, mēs savai tabulai pievienosim vēl vienu kolonnu.
Ja mēs gribētu paķert skolotāju Bobam, mēs varētu izmantot formulu
| 1 | = Nobīde (B2, 0, 1) |
Šajā gadījumā mēs teicām, ka vēlamies kompensēt nulles rindas (aka palikt tajā pašā rindā), bet vēlamies kompensēt 1 kolonnu. Kolonnās pozitīvs skaitlis nozīmē nobīdi pa labi, bet negatīvs - nobīdi pa kreisi.
OFFSET un Match
Pieņemsim, ka jums ir vairākas datu slejas un jūs vēlaties dot lietotājam iespēju izvēlēties, no kuras kolonnas iegūt rezultātus. Jūs varētu izmantot funkciju INDEX vai OFFSET. Tā kā MATCH atgriezīs vērtības relatīvo pozīciju, mums jāpārliecinās, ka stiprinājuma punkts atrodas pa kreisi no mūsu pirmās iespējamās vērtības. Apsveriet šādu izkārtojumu:
B2, mēs uzrakstīsim šādu formulu:
| 1 | = NOBĪDE (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH izskatīsies “februārī” diapazonā C1: F1 un atradīs to 2nd šūna. Pēc tam OFFSET pārvietos 1 kolonnu pa labi no B2 un iegūs vēlamo vērtību 9. Ņemiet vērā, ka OFFSET nav problēmu izmantot to pašu šūnu, kurā ir formula kā stiprinājuma punkts.
PIEZĪME. Šo paņēmienu var izmantot kā VLOOKUP vai HLOOKUP aizstājēju, ja vēlaties atgriezt vērtību no meklēšanas diapazona pa kreisi/virs. Tas ir tāpēc, ka OFFSET var veikt negatīvus nobīdes.
OFFSET, lai iegūtu diapazonu
Jūs varat izmantot 4tūkst un 5tūkst argumenti funkcijā OFFSET, lai atgrieztu diapazonu, nevis tikai vienu šūnu. Pieņemsim, ka šajā tabulā vēlaties apkopot 3 kolonnas.
| 1 | = VIDĒJĀ (NOBĪDE (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
F2 sadaļā mēs esam izvēlējušies studenta vārdu, kuram vēlamies iegūt vidējos testa rezultātus. Lai to izdarītu, mēs izmantosim formulu
| 1 | = VIDĒJĀ (NOBĪDE (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH meklēs A slejā mūsu vārdu un atgriezīs relatīvo pozīciju, kas mūsu piemērā ir 3. Redzēsim, kā tas tiks novērtēts. Pirmkārt, OFFSET gatavojas iet uz leju 3 rindas no A1 un 1 kolonna līdz taisnība no A1. Tas mūs ievieto šūnā B3.
| 1 | = VIDĒJĀ (NOBĪDE (A1, 3, 1, 1, 3)) |
Tālāk mēs mainīsim diapazona izmērus. Jaunajā diapazonā augšējā kreisā šūna būs B3. Tas būs 1 rindu augsts un 3 kolonnas augsts, sniedzot mums diapazonu B4: D4.
| 1 | = VIDĒJĀ (NOBĪDE (A1,3, 1, 1, 3)) |
Ņemiet vērā, ka, lai gan nobīdes argumentos likumīgi varat ievietot negatīvas vērtības, lieluma argumentos varat izmantot tikai negatīvās vērtības.
Beigās mūsu vidējā funkcija redz:
| 1 | = VIDĒJĀ (B4: D4) |
Tādējādi mēs iegūstam savu risinājumu 86,67
OFFSET ar dinamisku SUM
Tā kā OFFSET tiek izmantots, lai atrastu atsauci, nevis norādītu tieši uz šūnu, tas ir visnoderīgākais, ja strādājat ar datiem, kuriem ir pievienotas vai dzēstas rindas. Apsveriet šo tabulu ar kopsummu apakšā
| 1 | = SUMMA (B2: B4) |
Ja mēs šeit būtu izmantojuši SUM pamata formulu “= SUM (B2: B4)” un pēc tam ievietotu jaunu rindu, lai pievienotu Bila ierakstu, mums būtu nepareiza atbilde
Tā vietā padomāsim, kā to atrisināt no Total viedokļa. Mēs patiešām vēlamies satvert visu, sākot no šūnas B2 un beidzot ar šūnu nedaudz virs mūsu kopsummas. Veids, kā mēs to varam ierakstīt formulā, ir rindas nobīde -1. Tādējādi mēs to izmantojam kā formulu mūsu kopsummai šūnā B5:
| 1 | = SUMMA (B2: nobīde (B5, -1,0)) |
Šī formula dara to, ko mēs tikko aprakstījām: sāciet no B2 un dodieties uz 1 šūnu virs mūsu kopējās šūnas. Jūs varat redzēt, kā pēc Bila datu pievienošanas mūsu kopsumma tiek pareizi atjaunināta.
OFFSET, lai iegūtu pēdējos N vienumus
Pieņemsim, ka jūs reģistrējat ikmēneša pārdošanas apjomus, bet vēlaties aplūkot pēdējos 3 mēnešus. Tā vietā, lai manuāli atjauninātu formulas, lai pielāgotos, pievienojot jaunus datus, varat izmantot funkciju OFFSET ar COUNT.
Mēs jau esam parādījuši, kā jūs varat izmantot OFFSET, lai iegūtu virkni šūnu. Lai noteiktu, cik šūnu mums jāmaina, mēs izmantosim COUNT, lai atrastu, cik daudz numurus ir B slejā. Apskatīsim mūsu tabulas paraugu.
| 1 | = SUMMA (NOBĪDE ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Ja mēs sāktu no B1 un nobīdītu 4 rindas (skaitļu skaits B slejā), mēs nonāktu mūsu diapazona apakšā, B5. Tomēr, tā kā OFFSET nevar mainīt ar negatīvu vērtību, mums ir jāveic dažas korekcijas, lai mēs nonāktu B3. Vispārējais vienādojums tam būs jādara
| 1 | Skaitīt (…) - N + 1 |
Mēs aprēķinām visu kolonnu, atņemam, cik daudz mēs vēlamies atgriezt (jo mēs mainīsim izmērus, lai tos paņemtu), un pēc tam pievienojam 1 (jo mēs būtībā sākam nobīdi nulles pozīcijā).
Šeit jūs varat redzēt, ka esam iestatījuši diapazonu, lai iegūtu pēdējo N mēnešu summu, vidējo un maks. E1 mēs esam ievadījuši vērtību 3. E2 mūsu formula ir
| 1 | = SUMMA (NOBĪDE ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Izceltā sadaļa ir mūsu vispārējais vienādojums, ko mēs tikko apspriedām. Mums nav nepieciešams kompensēt nevienu kolonnu. Pēc tam mēs mainīsim diapazona lielumu, lai tas būtu 3 šūnas garš (noteikts pēc E1 vērtības) un 1 kolonnas platumā. Pēc tam mūsu SUM ņem šo diapazonu un dod mums rezultātu 1850 USD. Mēs arī esam parādījuši, ka jūs varat aprēķināt šī paša diapazona maksimālo vidējo vērtību, vienkārši pārslēdzot ārējo funkciju no SUM uz jebkuru situāciju.
OFFSET dinamisko validāciju saraksti
Izmantojot pēdējā piemērā parādīto tehniku, mēs varam izveidot arī nosauktos diapazonus, kurus varētu izmantot datu validācijā vai diagrammās. Tas var būt noderīgi, ja vēlaties izveidot izklājlapu, bet gaidāt, ka mūsu saraksti/dati mainīsies. Pieņemsim, ka mūsu veikalā sāk pārdot augļus, un šobrīd mums ir 3 izvēles iespējas.
Lai izveidotu datu validācijas nolaižamo izvēlni, ko varam izmantot citur, definēsim nosaukto diapazonu MyFruit kā
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
COUNT vietā mēs izmantojam COUNTA, jo mēs strādājam ar teksta vērtībām. Tomēr šī iemesla dēļ mūsu COUNTA būs par vienu augstāka, jo tā saskaitīs galvenes šūnu A1 un sniegs vērtību 4. Ja mēs nobīdāmies par 4 rindām, mēs nonāktu šūnā A5, kas ir tukša. Lai to pielāgotu, mēs atņemam 1.
Tagad, kad esam izveidojuši nosaukumu diapazona iestatījumus, mēs varam iestatīt datu validāciju šūnā C4, izmantojot saraksta veidu ar avotu:
| 1 | = MyFruit |
Ņemiet vērā, ka nolaižamajā izvēlnē tiek parādīti tikai mūsu trīs pašreizējie vienumi. Ja pēc tam mēs savam sarakstam pievienojam vairāk vienumu un atgriežamies nolaižamajā izvēlnē, sarakstā tiek parādīti visi jaunie vienumi, mums nav jāmaina neviena formula.
Brīdinājumi par OFFSET izmantošanu
Kā minēts šī raksta sākumā, OFFSET ir nepastāvīga funkcija. Jūs to nepamanīsit, ja to izmantosit tikai dažās šūnās, bet, ja sāksit to iesaistīt simtiem aprēķinu un ātri pamanīsit, ka dators katru reizi, kad veicat izmaiņas, tērē ievērojamu laiku pārrēķināšanai .
Turklāt, tā kā OFFSET tieši nenosauc šūnas, uz kurām tas skatās, citiem lietotājiem ir grūtāk ierasties vēlāk un vajadzības gadījumā mainīt formulas.
Tā vietā būtu ieteicams izmantot tabulas (ieviestas Office 2007), kurās ir atļautas strukturālas atsauces. Tas palīdzēja lietotājiem sniegt vienu atsauci, kuras lielums automātiski tika pielāgots, pievienojot vai dzēšot jaunus datus.
Otra iespēja, ko izmantot OFFSET vietā, ir jaudīgā INDEX funkcija. INDEX ļauj veidot visus dinamiskos diapazonus, kurus mēs redzējām šajā rakstā, neradot jautājumu par nepastāvīgu funkciju.
papildu piezīmes
Izmantojiet funkciju OFFSET, lai atgrieztu šūnas vērtību (vai šūnu diapazonu), izlīdzinot noteiktu rindu un kolonnu skaitu no sākuma atsauces. Meklējot tikai vienu šūnu, OFFSET formulas sasniedz to pašu mērķi kā INDEX formulas, izmantojot nedaudz atšķirīgu tehniku. Funkcijas OFFSET patiesā jauda ir tās spēja atlasīt šūnu diapazonu, ko izmantot citā formulā.
Izmantojot funkciju OFFSET, jūs definējat sākotnējo sākuma šūnu vai šūnu diapazonu. Pēc tam jūs norādāt rindu un kolonnu skaitu, ko nobīdīt no šīs sākotnējās šūnas. Varat arī mainīt diapazona izmērus; pievienot vai atņemt rindas vai kolonnas.
Atgriezieties visu Excel funkciju sarakstā
OFFSET Google izklājlapās
Funkcija OFFSET Google izklājlapās darbojas tieši tāpat kā programmā Excel:
