Змішана довідка в Excel (Зміст)
- Вступ до змішаної посилання в Excel
- Приклади змішаної посилання в Excel
Вступ до змішаної посилання в Excel
Посилання є однією з важливих особливостей в Excel, яка допомагає, коли ми працюємо з операціями excel, використовуючи формули. Змішана довідка, сама назва говорить, що це поєднання посилань. Комбінація включає відносну посилання та абсолютну посилання.
Для розуміння та роботи зі змішаною посиланням слід ознайомитись із відносною та абсолютною посиланням. Отже, спочатку ми побачимо, що таке відносна довідка і що є абсолютною посиланням, як ми можемо використовувати її у формулах, і все побачимо на відповідних прикладах.
Приклади змішаної посилання в Excel
Ось приклади відносної та абсолютної довідкової роботи зі змішаною довідкою в Excel, наведеними нижче.
Ви можете завантажити цей Змішаний довідковий шаблон Excel тут - Змішаний довідковий шаблон ExcelПриклад №1 - Відносна довідка
За замовчуванням посилання у excel вважатиме це відносним посиланням. Посилання - це нічого, якщо ми вводимо «= A1» у комірку B1, тоді ми маємо на увазі A1 у B1. Давайте подивимось приклад для розуміння відносної посилання.
Розгляньте нижче скріншот заробітку та витрат з січня по травень.
Тепер ми хочемо підрахувати грошові кошти, що залишилися після витрат, сплачених від заробітку. Ми можемо отримати це за допомогою простої формули віднімання витрат від заробітку. Просто введіть формулу поруч із витратами, щоб знайти гроші, що залишилися щомісяця.
Слідкуйте за наведеним нижче скріншотом Я ввів формулу поруч із витратами, які ви можете знайти з панелі формул.
Якщо ми спостерігаємо, що формула в D2 є B2-C2, це означає, що D2 посилається на комбінацію B2 і C2. Коли ми перетягнемо формулу до низу, вона займе аналогічно D2, це означає, що для D2 було посилання з B2 і C2 аналогічно для D3, воно посилається з B3 і C3, для D4 посилається на B4 і C4 тощо.
Ми можемо спостерігати відповідні скріншоти протягом інших місяців. На скріншоті нижче пов'язаний D3, який посилається на B3 та C3.
Скріншот, пов’язаний з D4, який посилається на B4 та C4.
Це означає, що формула завжди буде вважати тим самим посиланням таким же, як і перша формула, оскільки вона стосується цього. Сподіваємось, ви зрозуміли, що є відносним орієнтиром, де ми можемо використовувати.
Приклад №2 - Абсолютна довідка
Розгляньте той самий приклад, щоб зрозуміти і Абсолютне посилання. Розглянемо на наведеному вище скріншоті у нас прибуток і витрати, припустимо, що ми фіксували витрати на оренду, крім того, витрати, наявні в таблиці.
У вищевказаному досвіді ми згадали про оренду лише в одній клітині, яка є F2. Тепер нам потрібно внести зміни до формули у графі D, щоб відняти оренду від "грошових коштів, що залишилися після витрат".
Поспостерігайте на скріншоті, який ми відняли F2 з результату B2-C2. Перетягнемо формулу також до інших комірок.
Я перетягнув формулу, але все-таки результати не змінюються, тому що у комірці D2 ми відняли F2, як було пояснено раніше за замовчуванням Excel, буде відносно посилання, тому для формули в D3 було взято F3 аналогічно F4 у D4. Але у нас немає жодних значень у клітинах F3, F4, F5 та F6, тому він вважає ці клітини нульовими та не впливав на результати.
Як ми можемо вирішити це, не перетягуючи оренду до комірок F3, F4, F5 та F6. Тут Абсолютна довідка допомагає досягти правильних результатів без перетягування орендної плати.
Дуже просто створити абсолютну посилання, нам просто потрібно натиснути формулу і вибрати клітинку F2 і натиснути D2, тоді абсолютна посилання створить.
Якщо $ застосовується як для індексу стовпця F, так і для індексу рядків 2, то абсолютна посилання знаходиться на рівні комірки. Тепер перетягніть формулу в D3, D4, D5 та D6 і подивіться, як змінюються результати.
Спостерігайте формулу в комірці D3, вона дорівнює $ F $ 2, навіть після того, як ми перетягли формулу. Оскільки він заблокований на рівні комірок, він посилатиметься лише на F2. Це використання абсолютної довідки. Це буде використовувати посилання з заблокованого індексу.
Приклад №3 - Змішана довідка
Як було сказано раніше у змішаній посилання, ми будемо використовувати як відносну, так і абсолютну. Подивимось, як ми можемо використовувати обидва, використовуючи один і той же приклад.
Розгляньте дані у форматі нижче, як показано на скріншоті.
З наведеної вище таблиці нам потрібно знайти відсоток заробітку кожного місяця проти "Всього" заробітку за 5 місяців аналогічно, відсотки до витрат та грошових коштів, що залишилися після витрат.
Якщо все ще незрозуміло, нам потрібно з'ясувати 5000 відсотків із загального заробітку за 5 місяців 32100.
Тепер у нижній порожній таблиці нам потрібно знайти відсоток для кожної комірки.
Формула для знаходження відсотка ділиться на доходи січня на загальну суму 32100.
Якщо ви спостерігаєте, я використовував абсолютну довідку, оскільки заробітки за весь місяць повинні ділитися лише на загальну суму. Тепер ми можемо перетягнути формулу до травня місяця.
Тепер перетягніть також формули донизу.
Якщо ми все ще спостерігаємо, формула витрат займає лише загальний прибуток, але насправді це повинно зайняти 15200 витрат. Для цього нам потрібно створити посилання лише на рівні стовпців, що не потрібно на рівні рядків.
Дотримуйтесь, що формула $ символ доступна в N не в рядку індексу 11. Внесіть зміни завжди до першої формули, а потім перетягніть до інших комірок.
Скрізь формула підбирається правильно. Якщо ми спостерігаємо, ми виконували абсолютні посилання в операціях рядків і відносні посилання в операціях стовпців. Сподіваюсь, тепер зрозуміло, як виконувати змішані довідки та в яких сферах це буде корисно.
Що потрібно пам’ятати
- Змішана посилання - це комбінація відносної відліку та абсолютної відліку.
- Відносна посилання - це посилання за замовчуванням у excel, тоді як посилається на будь-які комірки в операціях excel. У цій іншій комірці візьмемо аналогічне посилання, як у першій формулі.
- Абсолютна посилання - це інший тип, де вона буде приймати посилання як фіксовану, де б ви не вставили формулу.
- Щоб створити абсолютну посилання, виберіть посилання на клітинку та натисніть F4, тоді символ $ додасть до індексу рядків та індексу стовпців.
- Змішана посилання - це комбінація обох даних, тому в цьому випадку ми можемо застосовувати абсолютне посилання лише на індекс стовпця або індекс рядка.
- $ A1 - означає посилання, створене для індексу стовпців.
- $ 1 - означає посилання, створене для індексу рядків.
- $ A $ 1 - означає посилання, створене для індексів рядків та стовпців.
Рекомендовані статті
Це посібник зі змішаної посилання в Excel. Тут ми обговорюємо, як відносна та абсолютна довідка працює зі змішаною довідкою в Excel разом із прикладами та шаблоном Excel, який можна завантажити. Ви також можете переглянути наступні статті, щоб дізнатися більше -
- ДЕНЬ Формула в Excel
- Формула розміру зразка
- Формула HLOOKUP в Excel
- VALUE формула в Excel