Як задати діапазон значень в Excel

При використанні Microsoft Excel дуже часто користувач заздалегідь не знає, скільки інформації буде в результаті в таблиці. Тому ми не в усіх ситуаціях розуміємо, який діапазон має бути охоплений. Адже набір комірок — поняття змінне. Щоб позбутися цієї проблеми, необхідно зробити генерацію діапазону автоматичною, щоб вона базувалася виключно на кількості даних, які ввів користувач.

Автоматична зміна діапазонів клітинок в Excel

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

Як зробити автоматичну зміну діапазону в Excel

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

На перший погляд, рішення очевидно: потрібно просто підсумувати весь стовпець. Якщо в ньому з'являться записи, сума буде оновлена ​​самостійно. Але цей спосіб має багато недоліків:

1. Якщо задачу розв’язати таким чином, то клітинки, що входять у стовпець Б, використовувати не за призначенням буде неможливо.
2. Така таблиця буде споживати багато оперативної пам'яті, що унеможливить використання документу на слабких комп'ютерах.

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

1. Перейдіть у вкладку «Формули», яка знаходиться в головному меню. Там буде розділ «Визначені імена», де є кнопка «Призначити ім'я», на яку нам потрібно натиснути.
2. Далі з'явиться діалогове вікно, в якому потрібно заповнити поля, як показано на скріншоті. Важливо відзначити, що нам потрібно застосувати функцію = ЗМІЩЕННЯ разом із функцією ПЕРЕВІРКАщоб створити діапазон автоматичного оновлення. 
3. Після цього нам потрібно скористатися функцією SUM, який використовує наш динамічний діапазон як аргумент. 

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

Функція OFFSET в Excel

Давайте подивимося на функції, які ми записали в полі «діапазон» раніше. Використання функції ВІДХОДЖЕННЯ ми можемо визначити розмір діапазону, враховуючи, скільки клітинок у стовпці B заповнено. Аргументи функції такі:

1. Початкова клітинка. За допомогою цього аргументу користувач може вказати, яка комірка в діапазоні вважатиметься верхньою лівою. Він буде повідомляти вниз і праворуч.
2. Зміщення діапазону рядками. Використовуючи цей діапазон, ми встановлюємо кількість клітинок, на яку має відбуватися зміщення від верхньої лівої клітинки діапазону. Можна використовувати не тільки позитивні значення, але і нуль, і мінус. У цьому випадку зміщення може не відбутися взагалі, або воно буде здійснюватися в зворотному напрямку.
3. Зміщення діапазону по стовпцях. Цей параметр схожий на попередній, тільки дозволяє задати ступінь горизонтального зсуву діапазону. Тут також можна використовувати як нульові, так і від’ємні значення.
4. Величина діапазону по висоті. Насправді назва цього аргументу дає нам зрозуміти, що він означає. Це кількість клітинок, на яку має збільшитися діапазон.
5. Значення діапазону по ширині. Аргумент подібний до попереднього, тільки стосується стовпців.

Вам не потрібно вказувати останні два аргументи, якщо вам це не потрібно. У цьому випадку значення діапазону буде складати лише одну клітинку. Наприклад, якщо вказати формулу =ЗМІЩЕННЯ(A1;0;0), ця формула посилатиметься на ту саму комірку, що й у першому аргументі. Якщо вертикальне зміщення встановлено на 2 одиниці, то в цьому випадку клітинка посилатиметься на клітинку A3. Тепер опишемо докладніше, що означає функція ПЕРЕВІРКА.

Функція COUNT в Excel

Використання функції ПЕРЕВІРКА визначаємо, скільки комірок у стовпці B ми заповнили загалом. Тобто за допомогою двох функцій ми визначаємо, скільки клітинок в діапазоні заповнено, і на основі отриманої інформації визначаємо розмір діапазону. Отже, підсумкова формула буде наступною: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Давайте розберемося, як правильно розуміти принцип цієї формули. Перший аргумент вказує на те, де починається наш динамічний діапазон. У нашому випадку це комірка B2. Подальші параметри мають нульові координати. Це означає, що нам не потрібні зсуви відносно верхньої лівої комірки. Усе, що ми заповнюємо, — це вертикальний розмір діапазону, у якості якого ми використовували функцію ПЕРЕВІРКА, що визначає кількість комірок, які містять деякі дані. Четвертий параметр, який ми заповнили, це одиниця. Таким чином ми показуємо, що загальна ширина діапазону повинна становити один стовпець.

Таким чином, використовуючи функцію ПЕРЕВІРКА користувач може використовувати пам'ять максимально ефективно, завантажуючи лише ті комірки, які містять певні значення. Відповідно, не буде додаткових помилок в роботі, пов'язаних з поганою роботою комп'ютера, на якому буде працювати табличний процесор.

Відповідно, щоб визначити розмір діапазону в залежності від кількості стовпців, потрібно виконати аналогічну послідовність дій, тільки в цьому випадку в третьому параметрі потрібно вказати одиницю, а в четвертому формулу. ПЕРЕВІРКА.

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

Динамічні діаграми в Excel

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

1. Вибираємо наш діапазон, після чого вставляємо діаграму типу «Гістограма з групуванням». Ви можете знайти цей пункт у розділі «Вставити» розділу «Діаграми – Гістограма».
2. Клацніть лівою кнопкою миші по випадковому стовпчику гістограми, після чого в рядку функції відобразиться функція =SERIES(). На скріншоті ви можете побачити детальну формулу. 
3. Після цього у формулу потрібно внести деякі зміни. Потрібно замінити діапазон після «Аркуш1!» до назви асортименту. Це призведе до наступної функції: =РЯДОК(Аркуш1!$B$1;;Аркуш1!дохід;1)
4. Тепер залишилося додати новий запис до звіту, щоб перевірити, оновлюється діаграма автоматично чи ні.

Давайте подивимося на нашу схему.

Давайте підсумуємо, як ми це зробили. На попередньому кроці ми створили динамічний діапазон, розмір якого залежить від того, скільки елементів він містить. Для цього ми використали комбінацію функцій ПЕРЕВІРКА и ВІДХОДЖЕННЯ. Ми назвали цей діапазон, а потім використали посилання на це ім’я як діапазон нашої гістограми. Який конкретно діапазон вибрати в якості джерела даних на першому етапі не так важливо. Головне, потім замінити його назвою асортименту. Таким чином можна заощадити багато оперативної пам'яті.

Іменовані діапазони та їх використання

Тепер поговоримо докладніше про те, як правильно створювати іменовані діапазони і використовувати їх для виконання завдань, які ставляться перед користувачем Excel.

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

Все починається з назви комірки. Для цього просто вибираємо його, а потім пишемо потрібне нам ім'я в полі його імені. Важливо, щоб він легко запам'ятовувався. Є деякі обмеження, які слід враховувати при присвоєнні імені:

1. Максимальна довжина – 255 символів. Цього цілком достатньо, щоб присвоїти ім'я, яке душа забажає.
2. Ім'я не повинно містити пробілів. Тому, якщо він містить кілька слів, то їх можна розділити за допомогою символу підкреслення.

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

Наступним кроком є ​​створення іменованого діапазону. Процедура в основному така ж. Спочатку потрібно вибрати діапазон, а потім вказати його назву. Після цього це ім'я можна використовувати в усіх інших операціях з даними в Excel. Наприклад, іменовані діапазони часто використовуються для визначення суми значень.

Крім того, можна створити іменований діапазон за допомогою вкладки «Формули» за допомогою інструмента «Установити назву». Після того, як ми його виділимо, з'явиться вікно, де нам потрібно вибрати назву для нашого діапазону, а також вказати область, на яку він буде поширюватися вручну. Ви також можете вказати, де працюватиме цей діапазон: на одному аркуші чи в усій книзі.

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

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

Інший спосіб створити іменований діапазон — отримати його з таблиці. Для цього існує спеціальний інструмент «Створити з виділеного». Як ми розуміємо, для того, щоб ним скористатися, потрібно спочатку вибрати діапазон, який ми будемо редагувати, а потім встановити місце, в якому у нас будуть заголовки. В результаті на основі цих даних Excel автоматично оброблятиме всі дані, і заголовки будуть автоматично присвоєні.

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

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