Excel OFFSET Formula (Съдържание)

  • Какво е OFFSET формула в Excel?
  • Как да използвате OFFSET формулата в Excel?

Какво е OFFSET формула в Excel?

OFFSET Formula в Excel дава клетка или диапазон от динамични правоъгълни клетки като изход, който е подмножество от общия даден диапазон с определен брой редове, колони, височина и ширина.

Синтаксис на OFFSET формула в Excel

Обяснение на OFFSET функцията в Excel:

  • справка: Отправната точка за OFFSET или можем да кажем набор от клетки, които могат да се считат за база за OFFSET.
  • редове : Броят редове за OFFSET под или над базовото референтно ниво.
  • cols: Броят на колоните за OFFSET вдясно или вляво от базовия еталон.
  • височина: Брой редове във върната справка.
  • ширина: Брой колони във върната справка.

Височината и ширината са опционални параметри за тази функция и могат да се използват за определяне на размера на създадената референция.

Как да използвате OFFSET формулата в Excel?

Сега ще научим как да пишем формула за офсет, за да получим стойността на търсенето. Нека разберем формулата за компенсиране с някои примери в Excel.

Можете да изтеглите този шаблон на OFFSET Formula Excel тук - OFFSET шаблон на формула Excel

Excel OFFSET Формула - Пример №1

Да предположим, че имаме данни, както са дадени по-долу.

  • В клетка E2 въведете следната формула:

= OFFSET (A1, 11, 1)

  • След като приключите с формулата, натиснете Enter и вижте изхода.

Това, което прави тази формула, е, че тя се позовава от клетка А1 (Година) и се премества надолу с 11 реда след А1 (т.е. ред № 12) и след това получава стойността, която е координата на 11 -ия ред след А1 и първата колона ( колона A = 0 колона B = 1. В този случай, както знаем, можем да преминем и надясно, и на ляво). Което е 141, 27 милиона долара.

Excel OFFSET Формула - Пример №2

Сега ще видим още един пример за референтна клетка за конкретна година. Поставете следната формула в клетка E3:

= OFFSET (A1, 11, 0)

  • След като приключите с формулата, натиснете Enter и вижте изхода.

Ако сте виждали формулата, тя е същата като предишната, единствената промяна е в референцията на колоните. Референтната колона 0 (нула) означава, че стойността ще бъде уловена от колона А за 11 -ти ред след А1 (което е 2010 г.).

Excel OFFSET Формула - Пример №3

В този пример ще изчислим сумата от последните N редове, използвайки Excel OFFSET функцията. Общата формула за сумиране на последните N числа е:

= SUM (OFFSET (A1, COUNT (A: A), 0, -N))

За да обобщим последните N наблюдения от вашите данни, можете да използвате функцията SUM заедно с OFFSET и COUNT функция заедно. В този пример ще обобщим последните 5 точки от данни. Моля, имайте предвид, че когато казваме N, това е обобщение на всяка цяло число. Така че можете да изчислите за последните 3, последните 4, последните 5 и т.н., в зависимост от вашия набор от данни, както и от вашето изискване.

Нека го направим за нашия набор от данни:

  • Въведете следната формула в клетка E2 на файла excel.

= SUM (OFFSET (B1, COUNT (B: B), 0, -5))

  • Щракнете върху Enter, за да видите резултата.

Както вече видяхме, функцията Excel OFFSET извежда правоъгълни динамични диапазони, когато е дадена начална референция.

  • В този случай началната справка се предоставя от гледна точка на B1 (като първи аргумент към OFFSET функцията).
  • Използвахме функцията COUNT, защото имаме нужда от сумата от наблюдения от последната стойност. Функция COUNT помага OFFSET да определи колко наблюдения (редове) има в колона B. Той отчита всички числови стойности в колона B (19 е броят).
  • С броене 19, формулата OFFSET става:

= OFFSET (B1, 19, 0, -5)

  • Тази формула започва прихващане в B1 с 19 реда и същата колона (0). Тогава параметър височина -5 помага на тази функция да разшири обхвата в посока назад с 5 реда (вижте отрицателния знак, свързан с 5).
  • Динамичният диапазон от Последните пет наблюдения е даден като вход за сумиране. Кои добиви при сумиране на тези от колона Б.

Excel OFFSET Формула - Пример №4

В този пример ще изчислим средните продажби за последните 3 години. Функцията OFFSET може да се използва като аргумент за изчисляване на същото.

  • Поставете следната формула в клетка E2:

= MEDIAN (OFFSET (B1, COUNT (B: B), 0, -3))

  • Натиснете Enter, за да видите изхода.

Тази формула работи по линиите, подобни на предишната.

  • OFFSET Създава динамичен диапазон от последните три наблюдения от колона B с помощта на функция COUNT (= OFFSET (A1, 19, 0, -3)).
  • Това може да се даде като изход към функцията MEDIAN. Което след това сортира наблюденията във възходящ или низходящ ред и след това избира средната най-голяма стойност, както виждате в продукцията ($ 212.07).

Excel OFFSET Formula - Пример №5

Тук ще изчислим средната година, използвайки функция MEDIAN и OFFSET.

  • Използвайте следната формула за същата в клетка E3:

= MEDIAN (OFFSET (А1, COUNT (А: А), 0, -3))

  • Щракнете върху Enter, за да видите резултата.

Същото нещо се е случило и тук. Формулата пое динамичен диапазон, създаден от OFFSET функция с помощта на COUNT. И го използва като аргумент на функция MEDIAN, която след това сортира годишните стойности във възходящ или низходящ ред и след това дава продукция като 2017.

Използването на OFFSET формулата е в създаването на динамичните диапазони. Винаги е по-добре да работите в динамични диапазони вместо статични. Тъй като може да имате данни, които се добавят от време на време. Например, служител майстор. Вашите служители Основни данни винаги ще се увеличават от време на време, тъй като трябва да се присъединят някои нови служители. Всеки път, когато използвате една и съща формула, няма смисъл. Вместо това създайте диапазон, който е динамичен, използвайки OFFSET функцията и вижте магията. Със сигурност ще ви спести време.

Неща, които трябва да запомните

  • Функцията OFFSET връща само препратка към определен диапазон от клетки в Excel. Той не мести клетките от първоначалните си позиции.
  • По подразбиране тази функция работи в горната част (по редове) и дясната страна (по отношение на колоните). Въпреки това, той може да бъде снабден с отрицателни аргументи, за да обърне функцията по подразбиране. Подобно на -отрицателния ред аргумент помага компенсиране на данните от надолу нагоре и отрицателен аргумент col помага компенсиране на данните вляво.
  • OFFSET формулата може да забави функционалността на Excel, тъй като е формула, която се преизчислява всеки път, когато има промяна в работния лист.
  • #REF грешка ще бъде изхвърлена, ако обхватът ви е извън обхвата на работната ви книга.
  • Всяка друга функция, която очаква препратка, е в състояние да използва OFFSET функция в нея.

Препоръчителни статии

Това е ръководство за OFFSET формула в Excel. Тук обсъждаме как да използваме OFFSET функцията в Excel, заедно с практически примери и изтеглящ се шаблон на excel. Можете да разгледате и другите ни предложени статии -

  1. Как да увиете текст в Excel?
  2. Просто ръководство за сортиране в Excel
  3. LOOKUP функция в Excel
  4. Как да използвате функцията FIND в Excel?

Категория:

Excel Съвети