✅ Динамічний діапазон в Excel - Як створити динамічний діапазон в Excel?

Діапазон в Excel

Динамічний діапазон Excel (Зміст)

Діапазон в Excel
Динамічний діапазон в Excel
Як створити динамічний діапазон в Excel?

Діапазон в Excel

У цьому ми будемо обговорювати динамічний діапазон у відмінній формі. Перш ніж потрапити в динамічний діапазон, давайте розберемося, що таке діапазон у excel.

Діапазон : Діапазон - це не що інше, як поєднання двох або більше комірок.

Це може бути група вертикальних комірок або горизонтальних комірок або комбінація обох. Діапазон відіграє хорошу роль при застосуванні формул та виконанні деяких операцій в excel.

Нижче наведено приклади діапазону:

Використання дальності

Припустимо, вам потрібно вказати номер індексу для діапазону баз даних 100 рядків. Оскільки у нас є 100 рядків, нам потрібно дати 100 індексних чисел вертикально зверху вниз. Якщо ми введемо ці 100 цифр вручну, це займе приблизно 5-10 хвилин. Тут діапазон допомагає виконати це завдання протягом 5 секунд часу. Подивимось, як це буде працювати.

Нам потрібно досягти індексних чисел, як показано на скріншоті нижче, тут я наводжу приклад лише на 10 рядків.

Дайте перші два числа під номером індексу та виберіть діапазон двох чисел, як показано нижче, та натисніть на кут (Позначений червоним кольором).

Перетягуйте до потрібного діапазону. Це дасть послідовну серію згідно з першими двома цифрами.

Якщо ми дамо першу комірку як 2, а другу клітинку як 4, то вона дасть усі парні числа. Виберіть діапазон двох чисел, як показано нижче, і натисніть на кут (Позначений червоним кольором).

Перетягуйте до потрібного діапазону. Це дасть послідовну серію згідно з першими двома цифрами.

Те саме стосуватиметься місяців, днів та дат тощо… Надайте перші два значення потрібного шаблону, виділіть діапазон і натисніть на кут.

Перетягуйте до потрібної довжини. Це дасть послідовну серію згідно з першими двома цифрами.

Сподіваюся, ви зрозуміли, що таке діапазон, зараз ми обговоримо динамічний діапазон. Ви можете дати ім’я діапазону комірок, просто вибравши діапазон.

Введіть ім'я у вікні імен (виділено на скріншоті).

Виберіть ім'я, яке ми додали, і натисніть клавішу enter.

Так що при виборі цього імені діапазон вибирається автоматично.

Динамічний діапазон в Excel

Динамічний діапазон - це не що інше, як діапазон, який динамічно підбирається, коли до існуючого діапазону додаються додаткові дані.

Приклад

Дивіться нижче графік продажів компанії з січня до квітня

У травні місяці, коли ми оновлюємо продажі за травень нижче даних квітневого місяця, графік продажів оновлюватиметься автоматично (Динамічно). Це відбувається тому, що діапазон, який ми використовували, є динамічним діапазоном, отже діаграма приймає динамічний діапазон і відповідно вносить зміни в діаграму.

Якщо діапазон не є динамічним діапазоном, діаграма не отримає оновлення автоматично, коли ми оновимо дані до існуючого діапазону даних.

Динамічний діапазон ми можемо досягти двома способами.

Використання функції таблиці Excel
Використання зсуву

Як створити динамічний діапазон в Excel?

Динамічний діапазон в Excel дуже простий і легко виправити. Давайте розберемося, як створити динамічний діапазон за допомогою деяких прикладів.

Ви можете завантажити цей шаблон Excel з динамічним діапазоном тут - Шаблон Excel з динамічним діапазоном

Динамічний діапазон в Excel Приклад №1 - Особливість таблиці Excel

Динамічний діапазон ми можемо досягти, скориставшись цією функцією, але це буде застосовано, якщо ми будемо використовувати версію excel 2017 та версії, випущені після 2017 року.

Подивимося, як створити зараз. Візьміть діапазон баз даних, як показано нижче.

Виберіть весь діапазон даних і натисніть кнопку вставки у верхній панелі меню, позначеною червоним кольором.

Пізніше натисніть на таблицю під меню вставки.

Наведене нижче спливаюче вікно з’явиться, перевірте поле "Моя таблиця має заголовок", оскільки наш вибраний діапазон таблиць даних має заголовок та натисніть "Ок".

Тоді формат даних зміниться на формат таблиці, ви також можете спостерігати за кольором.

Якщо ви хочете змінити формат (колір), виберіть таблицю та натисніть кнопку дизайну у верхній панелі меню. Ви можете обрати необхідні формати із "стилів таблиці".

Тепер діапазон даних знаходиться у форматі таблиці, отже щоразу, коли ви додаєте нові рядки даних, функція таблиці робить діапазон даних динамічно оновлюваним у таблицю, отже, діаграма також динамічно змінюється. Нижче наведено скріншот для довідки.

Клацніть на вставку та перейдіть до діаграм та натисніть на Лінійний графік, як показано нижче.

Лінійна діаграма додається, як показано нижче.

Якщо ви додасте ще одну інформацію в таблицю, тобто червень та продажі як 100, графік автоматично оновлюється.

Це один із способів створення динамічного діапазону даних.

Приклад №2 - Динамічний діапазон у програмі Excel - Використання зсуву

Формула зміщення допомагає починати з опорної точки і може компенсувати необхідну кількість рядків і вправо, або вліво з необхідною кількістю стовпців до нашого діапазону таблиць.

Подивимося, як досягти динамічного діапазону за допомогою формули зміщення. Нижче представлений зразок даних таблиці учнів для створення динамічного діапазону.

Щоб пояснити вам формат зміщення, я даю формулу на звичайному аркуші замість того, щоб застосувати в "Визначити ім'я".

Формат внесення зміщення

Довідка: Посилається на опорну клітинку діапазону таблиці, яка є початковою точкою.
Рядки: Посилається на необхідну кількість рядків для зміщення нижче початкової точки відліку.
Cols: Посилається на необхідну кількість стовпців для зміщення вправо або вліво до початкової точки.
Висота: посилається на висоту рядків.
Ширина: Посилається на Ширину стовпців.

Почніть з опорної комірки, тут учень - це контрольна комірка, з якої потрібно запустити діапазон даних.

Тепер дайте необхідну кількість рядків, щоб спуститися, щоб розглянути діапазон. Тут '1' задано, тому що це один рядок вниз від еталонної комірки.

Тепер дайте стовпці як 0, оскільки ми не збираємося розглядати стовпці тут.

Тепер дайте висоту рядків, оскільки ми не впевнені, скільки рядків ми будемо додавати у майбутньому, отже, надайте функцію "countta" рядків (виберіть весь стовпець "A"). Але ми не обробляємо заголовок у діапазоні, тому зменшуємо на 1.

Тепер у Width дайте 2, оскільки тут у нас є два стовпці, і ми не хочемо додавати сюди додаткових стовпців. Якщо ми не знаємо, скільки стовпців ми будемо додавати в майбутньому, тоді застосуємо функцію 'countta' до всього рядка як те, як ми застосували висоту. Закрийте формулу.

Тепер нам потрібно застосувати формулу зміщення у визначенні імені для створення динамічного діапазону. Клацніть на меню «формула» вгорі (виділено).

Клацніть на опцію "Визначити ім'я", позначену на скріншоті нижче.

З'явиться спливаюче вікно, дайте назву діапазону таблиць як «студентський», не маючи місця. "Обсяг" залиште його як "Робочу книжку", а потім перейдіть до "Посилається на", де нам потрібно дати формулу "OFFSET". Скопіюйте формулу, яку ми підготували апріорі та вставте в Посилання

Тепер ми можемо перевірити діапазон, натиснувши на диспетчер імен та вибрати ім’я, яке ви вказали у визначенні імені

Ось це "Studenttable", а потім натисніть на формулу, щоб діапазон таблиць виділявся автоматично, як ви можете спостерігати на знімку екрана.

Якщо ми додамо іншу позицію до існуючого діапазону, діапазон вибирається автоматично. Ви можете перевірити, додавши позицію та перевірити діапазон, нижче наведено приклад знімка екрана.

Сподіваємось, ви зрозуміли, як працювати з діапазоном Dynamic.

Переваги динамічного діапазону в Excel

Миттєве оновлення діаграм, поворотів тощо.
Не потрібно ручного оновлення формул.

Недоліки динамічного діапазону в Excel

Працюючи з централізованою базою даних з кількома користувачами, переконайтесь, що кожен оновлює правильні дані, оскільки діапазон вибирається автоматично.

Що слід пам’ятати про динамічний діапазон в Excel

Динамічний діапазон використовується при необхідності оновлення даних динамічно.
Функція таблиці Excel та формула OFFSET допомагають досягти динамічного діапазону.

Динамічний діапазон в Excel - Як створити динамічний діапазон в Excel?