Посилання, яке посилається на одну клітинку або діапазон на кількох аркушах, називається об'ємним посиланням. Об'ємне посилання – це зручний і зручний спосіб посилання на кілька аркушів, які мають однаковий шаблон і містять дані одного типу, наприклад, коли ви об'єднуєте бюджетні дані з різних відділів організації.
Докладні відомості про об'ємне посилання
За допомогою тривимірного посилання можна скласти бюджетні асигнування між трьома відділами, відділами збуту, кадрами та маркетингом, кожен із яких на іншому аркуші, використовуючи таке об'ємне посилання:
=SUM(Sales:Marketing!B3)
Можна навіть додати інший аркуш, а потім перемістити його до діапазону, на який посилається формула. Наприклад, щоб додати посилання на клітинку B3 на аркуші "Приміщення ", перемістіть аркуш "Приміщення " між аркушами " Продажі " та " Відділ кадрів", перетягнувши її вкладку, як показано на рисунку:
Оскільки формула містить об'ємне посилання на діапазон імен аркушів, sales:Marketing! B3, всі аркуші в діапазоні є частиною нового обчислення.
Дізнайтеся, як змінюються тривимірні посилання під час переміщення, копіювання, вставлення або видалення аркушів
У наведених нижче прикладах пояснюється, що відбувається, коли ви вставляєте, копіюєте, видаляєте або переміщуєте аркуші, включені в об'ємне посилання. У кожному з цих прикладів використовується формула =SUM(Аркуш2:Аркуш6! A2:A5), щоб додати клітинки від A2 до A5 на аркушах від 2 до 6:
- Вставити або скопіювати– Якщо вставити або скопіювати аркуші між Аркушем2 та Аркушем6 (кінцеві точки в цьому прикладі), в обчисленнях Excel буде включено всі значення клітинок від A2 до A5.
- Видалити – Якщо видалити аркуші між Аркушем2 та Аркушем6, їх значення буде видалено з обчислення.
- Перемістити — Якщо перемістити аркуші з аркуша між Аркушем2 та Аркушем6 до розташування за межами діапазону аркушів, на який посилається посилання, excel видалить їхні значення з обчислення.
- Переміщення кінцевої точки – Якщо перемістити Аркуш2 або Аркуш6 до іншого розташування в тій самій книзі, програма Excel змінить обчислення, доки не буде скасовано порядок кінцевих точок у книзі. Якщо змінити кінцеві точки на протилежні, об'ємне посилання змінить аркуш кінцевої точки. Наприклад, може бути посилання на Аркуш2:Аркуш6. Якщо перемістити Аркуш2 так, щоб він був після Аркуша6 у книзі, формулу буде налаштовано так, щоб вона вказував на Аркуш3:Аркуш6. Якщо перемістити Аркуш6 перед аркушем 2, формула наблизиться до пункту Аркуш2:Аркуш5.
- Видалення кінцевої точки – Якщо видалити Аркуш2 або Аркуш6, значення на цьому аркуші буде видалено з обчислення.
Створення об'ємного посилання
Указівки
- Клацніть клітинку, у яку потрібно ввести функцію.
- Введіть = (знак рівності), а потім ім'я функції (див. таблицю нижче), а потім відкривну дужку.
- Виберіть вкладку першого аркуша, на який потрібно створити посилання.
- Утримуючи натиснутою клавішу Shift, виберіть вкладку останнього аркуша, на який потрібно створити посилання.
- Виділіть клітинку або діапазон клітинок, на які потрібно створити посилання.
- Заповніть формулу та натисніть клавішу Enter.
У об'ємному посиланні можна використовувати такі функції:
| Функція | Опис |
|---|---|
| Функція AVERAGE | Обчислює середнє (середнє арифметичне) чисел. |
| Функція AVERAGEA | обчислює середнє (середнє арифметичне) чисел; містить текст і логічні значення. |
| Функція COUNT | Підраховує кількість клітинок, які містять числа. |
| Функція COUNTA | Підраховує кількість непустих клітинок. |
| Функція HARMEAN | Повертає середнє гармонічне сукупності додатних чисел: взаємність середнього арифметичного обчислення взаємних значень. |
| Функція KURT | Ця функція обчислює ексцес набору даних. |
| Функція LARGE | Ця функція повертає k-е найбільше значення у сукупності даних. Наприклад, п'яте найбільше число. |
| Функція MAX | Знаходить найбільше значення в наборі значень. |
| Функція MAXA | Знаходить найбільше значення в наборі значень; містить текст і логічні значення. |
| Функція MEDIAN | Повертає медіану або число всередині набору заданих чисел. |
| Функція MIN | Знаходить найменше значення в наборі значень. |
| Функція MINA | Знаходить найменше значення в наборі значень; містить текст і логічні значення. |
| Функція PERCENTILE | Повертає k-й процентиль значень у діапазоні, де k знаходиться в діапазоні 0..1. Надано для сумісності з попередніми версіями Excel. |
| Функція PERCENTILE.EXC | Повертає k-тий процентиль для значень діапазону, де k перебуває в діапазоні 0..1, за винятком 0 та 1. |
| Функція PERCENTILE.INC | Повертає k-тий процентиль для значень діапазону, де k перебуває в діапазоні 0..1, включно з 0 і 1. |
| Функція PERCENTRANK | Повертає ранг значення в наборі даних як відсоток (0,1) набору даних. Надано для сумісності з попередніми версіями Excel. |
| Функція PERCENTRANK.EXC | Повертає ранг (відсоткову норму) значення (0..1, за винятком 0 та 1) у сукупності даних. |
| Функція PERCENTRANK.INC | Повертає ранг (відсоткову норму) значення як відсоток (0..1 включно з 0 і 1) сукупності даних. |
| Функція QUARTILE | Повертає квартиль набору даних на основі значень процентиля від 0..1. Надано для сумісності з попередніми версіями Excel. |
| Функція QUARTILE.EXC | Повертає квартиль набору даних на основі значень процентиля від 0..1, за виключаючою. |
| Функція QUARTILE.INC | Повертає квартиль набору даних на основі значень процентиля від 0..1, включно з 0 і 1 |
| Функція PRODUCT | Перемножує числа. |
| Функція RANK | Повертає ранг числа у списку чисел: його розмір відносно інших значень у списку. Надано для сумісності з попередніми версіями Excel. |
| Функція RANK.EQ | Повертає ранг числа у списку чисел: його розмір відносно інших значень у списку. |
| Функція RANK.AVG | Повертає ранг числа у списку чисел: його розмір відносно інших значень у списку. |
| Функція SKEW | Повертає асиметрію розподілу. |
| Функція SMALL | Повертає k-е найменше значення в наборі даних. |
| Функція STDEV.S | Обчислює стандартне відхилення на основі вибірки. |
| STDEV. Функція P | Обчислює стандартне відхилення генеральної сукупності. |
| Функція STDEVA | Обчислює стандартне відхилення на основі вибірки; містить текст і логічні значення. |
| Функція STDEVPA | Обчислює стандартне відхилення генеральної сукупності; містить текст і логічні значення. |
| Функція TRIMMEAN | Повертає середнє значення внутрішньої частки набору даних. |
| Функція SUM | Додає числа. |
| Функція VAR.S | Оцінює дисперсію на основі вибірки. |
| VAR. Функція P | Обчислює дисперсію для всієї генеральної сукупності. |
| Функція VARA | Обчислює дисперсію на основі вибірки; містить текст і логічні значення. |
| Функція VARPA | Обчислює дисперсію для генеральної сукупності; містить текст і логічні значення |
Створення імені для об'ємного посилання
Указівки
- На вкладці Формули натисніть кнопку Визначити ім'я (у групі Визначені імена ).
- У спливаючому вікні Нове ім'я введіть ім'я для посилання. Ім'я може містити до 255 символів.
- У списку Посилання виберіть знак рівності (=) і посилання, а потім натисніть клавішу Backspace.
- Виберіть вкладку першого аркуша, на який потрібно створити посилання.
- Утримуючи натиснутою клавішу Shift, перейдіть на вкладку аркуша посилання.
- Виділіть клітинку або діапазон клітинок, на які потрібно створити посилання.
Докладніше
Докладні відомості про переміщення аркуша в книзі див. в статті Переміщення або копіювання аркуша. Докладні відомості про створення та використання визначених імен див. в статті Визначення та використання імен у формулах.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті tech Excel або отримати підтримку в спільнотах.