В тази статия ще научим как да използваме функцията OFFSET в Excel.
Какво е OFFSET функция?
Функцията Offset е функция за извличане на информация. Ако имате огромна таблица, съдържаща определени данни, и искате да извлечете данни от нея, най -добрият начин да направите това е да вмъкнете формула за изместване за таблицата в този работен лист или друг лист в същата работна книга. По този начин, след като сте създали таблица за извличане на офсета, просто трябва да въведете данните например „месец“ и ще получите „продажбите“ или „приходите“ за този месец от тази таблица.
Преминаването през таблицата е друга опция, но бихте ли обмислили опцията, ако имате листове и листове с данни, съдържащи таблица с данни от 1000 колони? Тук офсетът се комбинира с други функции.
Концепцията за обобщена таблица идва от офсета. Обобщена таблица е обща таблица и от тази специфична комбинация от данни и графики се получава, когато и когато е необходимо. Също така динамичните таблици могат да бъдат създадени по същия начин. За тези таблици ще трябва да създадете формулата за компенсиране на excel
Синтаксис на OFFSET
| = OFFSET (препратка, редове, колони, [височина], [ширина]) |
Редове - молите Excel да премести броя редове, за да получи информацията. В този случай той трябва да се движи из цялата маса и така просто да остави празно място, обозначено със запетая (,). В противен случай, за да преместите колона напред, дайте стойност като 1 и да преместите колона преди, дайте стойност -1.
Колони - това е за придвижване на функцията до броя колони. Системата на стойностите е същата като тази за ред.
Височина - височината на масата, в случая A11.
Ширина - ширина на масата, в този случай D11.
След като настроите функцията за отместване, провеждането на пробен период е важно, за да премахнете всички грешки.
Нека вземем таблица, която съдържа Име, имейл адрес, дата на раждане и възраст. Таблицата започва от клетка A1, съдържаща заглавието „Име“. Данните се изпълняват за A11. И редовете се движат до D1. Цялата таблица работи с A1: D11. Искате система за извличане, която ви дава името, когато въведете имейл адреса, или имейл адреса, когато въведете името, заедно с други подробности. Вие създавате формула, като задоволявате 5 -те аргумента.
Референтната точка е клетката, от която трябва да започне търсенето. В този случай трябва да е А2. Това е общият стандарт.
Пример:
Например давате препратка към B4 и отместване на ред 0, и изместване на колона 1, тогава стойността от C4 ще бъде върната. Объркващо? Нека имаме още няколко примера. Последният пример за функцията OFFSET казва как да се сумира динамично.
Функция OFFSET за получаване на стойност от дясно и от ляво на клетка
Имаме тази маса.
Сега, ако искам да получа стойност от 2 клетки направо до B2 (което означава D2). Ще напиша тази ОФСЕТ формула:
| = OFFSET (B2,0,2) |
Функцията OFFSET ще премести 2 клетки вдясно от клетка B4 и ще върне стойността й. Вижте изображението по -долу
Ако искам да получа стойност от 1 клетка вляво до B2 (което означава A2). Ще напиша тази ОФСЕТ формула:
| = OFFSET (B2,0, -1) |
Знакът минус (-) показва отместване за преместване в обратна посока.
Функция OFFSET за получаване на стойност отдолу и отгоре на клетка
Така че отново в горната таблица, ако искам да получа стойност от 2 клетъчни белове към B3 (което означава B5). Ще напиша тази ОФСЕТ формула:
| = OFFSET (B2,2,0) |
Ако искам да получа стойност от 1 клетка вляво до B2 (което означава A2). Ще напиша тази ОФСЕТ формула:
| = OFFSET (B2, -2,0) |
- Професионален съвет: Третирайте OFFSET като указател на графика, където Reference е начало, а редът и колоната са оси x и y.
Функция OFFSET за динамично сумиране
Нека видим този пример.
В горната таблица Общ ред в края на таблицата. С течение на времето ще добавяте редове към тази таблица. След това ще трябва да промените общия диапазон във формулата. Тук можем да използваме функцията OFFSET за динамично сумиране. В момента в клетка С11 имаме = SUM (C2: C10). Заменете това с формулата по -долу.
| = SUM (C2: OFFSET (C11, -1,0)) |
Тази формула просто взема първия ред с данни и след това сумира диапазона над общия ред.
Офсет за получаване на масив
Функцията OFFSET има два незадължителни аргумента, [височина] и [ширина]. Не ги приемайте за даденост. Когато функцията OFFSET е предоставена [височина] и [ширина] аргументи, тя връща двуизмерен масив. Ако само един от тях е предаден като аргумент, той се връща към размерния масив от стойности.
Ако въведете тази формула във всяка клетка:
| = SUM (OFFSET (C1,1,0,9,3)) |
Той ще сумира стойности в диапазон, започващ от C2 до 9 реда по -долу и 3 колони вдясно.
ОФСЕТ (C1,1,0,9,3): Това ще се преведе на {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8,3,9; 8,9,2; 3,5 , 4; 6,6,5}.
И така, в заключение, офсетът е много полезна функция на Excel, която може да ви помогне да разрешите много удушени нишки. Кажете ми как използвате функцията OFFSET във формулата си и къде тя ви е помогнала най -много.
Функцията за търсене е същата като офсета, но използва функции като Match, Counta и IF за работа с динамична таблица.
Използването на офсет в Excel изисква умения и добри познания за функциите на Excel и за това как работят заедно. Издърпването му без никаква грешка е най -голямото препятствие.
Ето всички забележки от наблюденията относно използването на формулата.
Бележки:
- Тази формула работи както с текст, така и с числа.
- Има пет аргумента на изместването, които трябва да бъдат удовлетворени, за да получите информация без грешки. Ако напишете неправилна формула, възниква грешка Ref.
Надявам се, че сте разбрали какво представлява функцията Excel Offset и как да я използвате в Excel. Разгледайте още статии за търсене в Excel и сравняване на стойности с помощта на формули тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Функция VLOOKUP за изчисляване на оценка в Excel : За изчисляване на оценки IF и IFS не са единствените функции, които можете да използвате. VLOOKUP е по -ефективен и динамичен за такива условни изчисления. За да изчислим оценки с помощта на VLOOKUP можем да използваме тази формула.
17 неща за Excel VLOOKUP : VLOOKUP се използва най -често за извличане на съответстващи стойности, но VLOOKUP може да направи много повече от това. Ето 17 неща за VLOOKUP, които трябва да знаете, за да използвате ефективно.
Потърсете първия текст от списък в Excel : Функцията VLOOKUP работи добре с заместващи знаци. Можем да използваме това, за да извлечем първата текстова стойност от даден списък в Excel. Ето общата формула.
LOOKUP дата с последна стойност в списъка : За да извлечем датата, която съдържа последната стойност, използваме функцията LOOKUP. Тази функция проверява за клетката, която съдържа последната стойност във вектор и след това използва тази препратка, за да върне датата.
Как да извлечете най -новата цена в Excel : Обичайно е да се актуализират цените във всеки бизнес и използването на най -новите цени за всяка покупка или продажба е задължително. За да извлечем най -новата цена от списък в Excel, използваме функцията LOOKUP. Функцията LOOKUP достига най -новата цена.
Популярни статии:
50 Преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
