Kā izmantot funkciju Excel OFFSET -

Kopsavilkums

Excel OFFSET funkcija atgriež atsauci uz diapazonu, kas izveidots ar piecām ieejām: (1) sākuma punktu, (2) rindas nobīdi, (3) kolonnas nobīdi, (4) augstumu rindās, (5) platumu in kolonnas. OFFSET ir ērts formulās, kurām nepieciešams dinamiskais diapazons.

Mērķis

Izveidojiet atsauces nobīdi no norādītā sākuma punkta

Atgriešanās vērtība

Šūnas atsauce.

Sintakse

= OFFSET (atsauce, rindas, slejas, (augstums), (platums))

Argumenti

• atskaite - sākuma punkts, kas tiek piegādāts kā šūnas atsauce vai diapazons.
• rindas - rindu skaits, kas jāatskaita zem sākuma atsauces.
• kolonnas - kolonnu skaits, kas jāpārsniedz pa labi no sākuma atsauces.
• augstums - (pēc izvēles) atgrieztā atsauces augstums rindās.
• width - (pēc izvēles) Platums atgrieztās atsauces slejās.

Versija

Excel 2003

Lietošanas piezīmes

Excel OFFSET funkcija atgriež dinamisko diapazonu, kas izveidots ar piecām ieejām: (1) sākuma punktu, (2) rindas nobīdi, (3) kolonnu nobīdi, (4) augstumu rindās, (5) platumu kolonnās.

Sākuma punkts ( atsauces arguments) var būt viena šūna vai šūnu diapazons. Šīs rindas un COLS argumenti ir šūnu skaits, lai "kompensētu" no sākuma punkta. Par augstums un platums argumenti ir obligāta un noteikt lielumu diapazonā, kas ir izveidots. Ja augstums un platums ir izlaisti, tie pēc noklusējuma ir atsauces augstums un platums .

Piemēram, atsaucei C5, kas sākas ar A1, atsauce ir A1, rindas ir 4 un kolonnas ir 2:

=OFFSET(A1,4,2) // returns reference to C5

Uz atsauci C1: C5 no A1, atsauce ir A1, rindas ir 0, kolonnas ir 2, augstums ir 5 un platums ir 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Piezīme: platumu var izlaist, jo tas pēc noklusējuma būs 1.

Parasti ir redzams, ka OFFSET ir iesaiņots citā funkcijā, kas sagaida diapazonu. Piemēram, uz SUM C1: C5, sākot no A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

OFFSET galvenais mērķis ir ļaut formulām dinamiski pielāgoties pieejamajiem datiem vai lietotāja ievadam. Funkciju OFFSET var izmantot, lai izveidotu dinamisku nosaukto diapazonu diagrammām vai rakurstabulām, lai nodrošinātu, ka avota dati vienmēr ir atjaunināti.

Piezīme: Excel dokumentācijā norādīts, ka augstums un platums nevar būt negatīvi, bet šķiet, ka negatīvās vērtības ir labi darbojušās kopš 1990. gadu sākuma. Google izklājlapu funkcija OFFSET nepieļaus negatīvu vērtību argumentiem ar augstumu vai platumu.

Piemēri

Tālāk sniegtie piemēri parāda, kā OFFSET var konfigurēt, lai atgrieztu dažāda veida diapazonus. Šie ekrāni tika uzņemti, izmantojot programmu Excel 365, tāpēc OFFSET atgriež dinamisko masīvu, ja rezultāts ir vairāk nekā viena šūna. Vecākās Excel versijās varat izmantot taustiņu F9, lai pārbaudītu rezultātus, kas atgriezti no OFFSET.

1. piemērs

Zemāk redzamajā ekrānā mēs izmantojam OFFSET, lai atgrieztu trešo vērtību (marts) otrajā kolonnā (West). H4 formula ir:

=OFFSET(B3,3,2) // returns D6

2. piemērs

Zemāk redzamajā ekrānā mēs izmantojam OFFSET, lai atgrieztu pēdējo vērtību (jūnijs) trešajā kolonnā (ziemeļi). H4 formula ir:

=OFFSET(B3,6,3) // returns E9

3. piemērs

Zemāk mēs izmantojam OFFSET, lai atgrieztu visas vērtības trešajā kolonnā (ziemeļi). H4 formula ir:

=OFFSET(B3,1,3,6) // returns E4:E9

4. piemērs

Zemāk mēs izmantojam OFFSET, lai atgrieztu visas maija vērtības (piektā rinda). H4 formula ir:

=OFFSET(B3,5,1,1,4) // returns C8:F8

5. piemērs

Tālāk mēs izmantojam OFFSET, lai atgrieztu aprīļa, maija un jūnija vērtību Rietumu reģionā. H4 formula ir:

=OFFSET(B3,4,2,3,1) // returns D7:D9

6. piemērs

Tālāk mēs izmantojam OFFSET, lai atgrieztu aprīļa, maija un jūnija vērtību rietumos un ziemeļos. H4 formula ir:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Piezīmes

• OFFSET atgriež tikai atsauci, neviena šūna netiek pārvietota.
• Gan rindas, gan slejas var piegādāt kā negatīvus skaitļus, lai mainītu to parasto nobīdes virzienu - negatīvās kolonnas tiek nobīdītas pa kreisi un negatīvās rindas nobīdītas iepriekš.
• OFFSET ir "nepastāvīga funkcija" - tā pārrēķinās ar katru darblapas izmaiņu. Nepastāvīgo funkciju dēļ lielākas un sarežģītākas darbgrāmatas darbojas lēni.
• OFFSET parādīs #REF! kļūdas vērtība, ja nobīde atrodas ārpus darblapas malas.
• Ja augstums vai platums nav norādīts, tiek izmantots atskaites augstums un platums .
• OFFSET var izmantot ar jebkuru citu funkciju, kas sagaida atsauces saņemšanu.
• Excel dokumentācijā teikts, ka augstums un platums nevar būt negatīvi, bet negatīvās vērtības darbojas.

Saistītie videoklipi

Kā izveidot dinamisku nosaukto diapazonu ar OFFSET Šajā videoklipā aplūkosim, kā izveidot dinamisko nosaukto diapazonu, izmantojot funkciju OFFSET - visizplatītāko veidu, kā izveidot dinamisko nosaukumu diapazonu ar formulu.