Вивчаємо функцію зміщений, exceltip
Чи не правда, було б чудово, якби Excel міг змінювати діапазон даних, наприклад, для графіків або діаграм, при додаванні нових рядків або стовпців. Насправді, можливість створення динамічних діапазонів існує, і вона реалізована за допомогою функції зміщений.
Функція зміщений повертає посилання на діапазон з певною відправною точкою із заданою висотою і шириною.
Що це означає? Наприклад, у вас є діапазон даних, який використовується для побудови діаграми, але кількість рядків настільки велике, що діаграма ставати нечитабельною для користувачів. Виходом із ситуації стане відображення тільки певної частини даних. Наприклад, користувачам необхідно буде вказати останній квартал або рік і функція зміщена відправить новий діапазон даних на побудову діаграми.
Синтаксис формули зміщений
Формула зміщений виглядає наступним чином:
І звичайно, всі аргументи можуть посилатися на інші осередки. Це означає, що ви можете написати формулу = зміщений (A1; D1; D2; D3; D4), яка поверне діапазон
- Усунутий від осередку A1
- На D1 рядків і D2 стовпців
- Розміром D3 рядки і D4 стовпчика
На малюнку зображено приклад роботи формули зміщений для кращого розуміння.
Навіщо використовувати формулу зміщений?
Чому просто не можна явно вказати назву діапазону, наприклад, A1: C4?
Ось пара причин чому:
Інтерактивне опис формули зміщений
Формула зміщений досить непроста для розуміння, тому була створена інтерактивна таблиця, щоб ви могли краще зрозуміти, як вона працює. Змінюючи 5 аргументів формули, Excel буде виділяти діапазон, який ви вибрали. Після декількох хвилин пустощів, ви зрозумієте формулу краще.
Практикуємося з формулою зміщений - середнє значення останнього тижня
Припустимо, що ми працюємо в службі якості фабрики з виробництва шоколадних батончиків. Одним з показників ефективності (KPI), який ми повинні відслідковувати є браковані батончики. Лист збору даних буде виглядати приблизно так.
Так як же ми будемо вважати середній показник шлюбу за тиждень?
Якщо припустити, що дані знаходяться в діапазоні B2: B23, тоді необхідно записати = СРЗНАЧ (B2: B23).
Але нам необхідно, щоб ця формула змінювалася щодня.
Використовуючи формулу зміщений ми можемо один раз порахувати середнє значення і більше не повертатися до цього питання.
Розберемося, як працює ця формула
- Для того, щоб порахувати середнє значення останнього тижня, ми повинні витягнути останні 7 рядків з усього масиву даних.
- Ось звідки взялася формула СЧЁТЗ (B2: B300) -7, яка вважає кількість значень і потім, від отриманої цифри віднімається 7.
- Формула зміщений переміщається з осередку B2 на стартову осередок останнього тижня.
- На ДІФКУ показано, як працює динамічний діапазон.
Вам також можуть бути цікаві такі статті
Ренат, спасибі велике за опис функції!
Чи не могли б ви також пояснити метод, який був використаний для створення інтерактивної таблиці. Зокрема, мене цікавить частина «= ЧСТРОК (діап_смещ I12)», яка повертає «порожньо», якщо осередок поза діапазону, і «1», якщо всередині. Я перший раз бачу використання назви масиву через пробіл з посиланням на осередок, хотів би зрозуміти, який механізм за цим стоїть. Велике спасибі.
Ренат, це одна з хитрощів Excel. Функція ЧСТРОК дозволяє сказати кількість рядків, які знаходяться на перетині двох масивів (в нашому випадку масиву і осередки). Пропуск вказує на те, що нам необхідно саме перетин
З великим інтересом прочитав вашу статтю про формулу зміщені.
Дуже цікаво і пізнавально! особливо сподобалося
застосування зміщений для обчислення середнього значення останнього тижня.
Просто неперевершено! Давно шукав щось подібне, просте
і витончене. Дякую за вашу працю.
Скажіть, а чи не можна якось переписати вашу формулу таким чином,
щоб вона при обчисленнях враховувала (ігноріровала-?) порожні клітинки?
Я спробував застосувати вашу формулу до діапазону чисел, в якому
є порожні ячейкі.Пріменітельно наприклад в вашій статті - це як якби
відсутнє, наприклад, число 98 в осередку В20. Вийшла якась дурниця, формула
начебто порахувала, але видала невірне значення. Як можна це виправити,
щоб формула працювала з діапазоном чисел, в якому є порожні клітинки?
Мені особисто нічого в голову не приходить, пробував і так і сяк, нічого
не виходить. Може бути, допоможете вирішити цю проблему?
Добрий день, щоб визначити положення останнього елемента в діапазоні, необхідний безперервний масив (без порожніх клітинок). Якщо все таки у діапазоні є прогалини, роль такого масиву може зіграти стовпець з датами. Тоді формула набуде вигляду: = СРЗНАЧ (зміщений (B2; СЧЁТЗ (A2: A300) -7; 0; 7; 1))
Привіт, Ренат! Дякую за допомогу, нова формула прекрасно працює,
все правильно вважає, в тому числі і при наявності порожніх клітинок. Якраз те, що мені
і було потрібно. Ще раз дякую за вашу допомогу.
Користуюся вашої формулою вже 2 дні, прекрасно працює, ще раз дякую
за допомогу.
У процесі роботи з формулою виникла одна проблемка, пов'язана з особливістю
тих даних, на підставі яких я будую таблицю. Я якось упустив її з поля зору, коли
в перший раз звертався до вас за допомогою. Може бути, допоможете вирішити її зараз?
Ви пишете: "... Для того, щоб порахувати середнє значення останнього тижня,
ми повинні витягнути останні 7 рядків з усього масиву даних.
Ось звідки взялася формула СЧЁТЗ (B2: B300) -7, яка вважає кількість
значень і потім, від отриманої цифри віднімається 7. »Тобто формула в обох
варіантах задає зсув на певну кількість рядків, 7 в даному випадку,
і вже для них розраховують СРЗНАЧ. У моєму випадку, при наявності порожніх клітинок в діапазоні,
може виникнути ситуація, коли всі ці осередки виявляться порожніми, тому що
проміжки між даними, як з'ясувалося, можуть бути в одних випадках 1 осередок, а
в інших 5,10, і навіть 24 осередки (!) можуть виявитися порожніми. зіткнувся сьогодні
з таким варіантом, сам не очікував нічого подібного. Причому, все це постійно змінюється.
В таких умовах вести розрахунок в діапазоні, що складається з певної кількості
рядків, дуже незручно. Доводиться все одно перелопачувати весь стовпець вручну.
В цьому випадку набагато краще було б вважати діапазон, що складається
не з певної кількості комірок (7), як це зроблено у вас, а діапазон, що складається з
з певної кількості тільки ЗАПОВНЕНИХ осередків (7). Які, з урахуванням порожніх
осередків між ними, можуть займати 7,10,20 ... будь-яку кількість осередків, якщо вважати їх
поспіль, від останньої заповненої до сьомої, наприклад, лежить всередині діапазону В. Саме
така формула мені необхідна. Може бути, на базі вашої формули, ви змогли б створити
дещо подібне? Я був би вам дуже вдячний.
Стосовно наприклад в вашій статті і з урахуванням рішення для діапазону з порожніми
осередками, яке ви мені вже підказали, алгоритм дій міг би виглядати наступним
чином. СЧЕТЗ ведемо по стовпчику А з датами, оскільки він без пробілів. Потім з цього
стовпчика вибираємо тільки ті дати, для яких в стовпці В є заповнені осередки. вибираємо
певну кількість таких осередків з шпальти В, наприклад 7 останніх, і вже для них розраховуємо
СРЗНАЧ. При це вся ця конструкція, природно, повинна працювати так само, як і ваша формула,
якщо в осередку А і В будуть додані нові значення. Якось так.
Але як саме все це вибрати і співвіднести? За допомогою ВВР, ЯКЩО, роботи з базами
даних, якщо стовпець В позначити як базу даних, або ще якихось функцій і прийомів?
Тут моїх знань не вистачає, дуже сподіваюся на вашу допомогу.
Владислав, якщо вам потрібно знайти наступну після максимальної величину, скористайтеся функцією НАЙБІЛЬШИЙ, яка повертає необхідний найбільший елемент в масиві. Детальніше про цю функцію можна прочитати в статті про функцію НАЙБІЛЬШИЙ / НАЙМЕНШИЙ
Добрий день. Допоможіть з проблемою.
Необхідно, щоб коли менеджер вибирає день місяця - в сусідньому стовпці в випадаючому списку були доступні тільки ті машини, які відзначили як «доступні» в цей день (і ще треба, щоб порожніх рядків не було в випадаючому списку)
Для цього зробив перелік авто і можливість, щоб людина вручну по днях відзначав, які з них будуть доступні (тому що треба щоб можна було на сьогодні і наприклад на 3 дні вперед відзначити доступні авто). Після цього зробив ще одну таблицю в розрізі днів, куди переносяться доступні авто без прогалиною і для кожного дня створив іменований діапазон (за допомогою зміщені, щоб в кінці не було порожніх клітинок випадаючому списку).
Діапазони кожного дня назвав д1, Д2 ... Д31.
І тепер коли намагаюся прив'язати ці діапазони до Даті за допомогою ДВССИЛ, то випадає помилка в джерелі (як я розумію, тому що ДВССИЛ і зміщені не сумісні?)
Що можна ще придумати, як можна відразу в джерело прописати формулу зі зміщені, щоб при виборі дня місяця - відразу вибирався перелік доступних машин цього дня.
Без зміщений все виходить, але тоді порожні клітинки в переліку.
Буду дуже вдячний за допомогу.
ось такі фотки давно пора б.
Саме на теперішній пору покупка будь-якої техніки зобов'язана протікати раптово плюс квалітативну, то іншими словами діяти власне на вигідних даних, по доречною грошах також відмінному хорошому змісті. Презентована магазин вентиляційної техніки надає постачання кліматізерной системою різне будівлі особливо на даних умовах. Фірма Майстер-Клімат в Києві касетний кондиціонер київ створює продаж, транспортування, орієнтацію, лагодження, розбір, очищення також діагностику придбаної кондиционерной обладнанні в недорогим цінами і ще твердження гарантії. У даному веб-румі Майстер Клімат абоненти одержувати здійсненність виділити актуальну виріб визнаних також східних компаній Panasonic, NeoClima, Купер Hunter, Mitsubishi, Олма, Leberg, які можна замовити у столиці за збагненним цінами. Майстер Клімат застосовує реалізацію систем вентиляцій та підігріву, що посприяє гарантувати нормальний средувоздух, влаштоване до того ж длітельнуюдолгосрочную дію до довгі період використання. Телефонуйте до Майстер Клімат, і менеджер підбираємо для клієнта обрані різновиди повністю на вигідних договорі.
I apologise, but, in my opinion, you are not right. I am assured. Write to me in PM, we will communicate.