Мервін з Англії надіслав цю проблему.
Чи є в комірці B2 одна формула, яку можна скопіювати поперек і вниз, щоб створити таблицю множення?
Мета Мервіна - ввести єдину формулу в B2, яку можна легко скопіювати у всі 144 клітинки, щоб створити таблицю посилань на множення.
Давайте атакуємо цього як початківця Excel і подивимось, на які підводні камені ми натрапляємо. Початковою реакцією може бути наявність формули в B2 =A2*B1. Ця формула дає правильний результат, але вона не підходить для призначення Мервіна.
Коли ви копіюєте цю формулу з комірки B2 в клітинку C2, комірки, на які посилаються у формулі, також переміщуються по одній комірці. Формулою, яка була =A2*B1зараз, стає =C1*B2. Ця функція розроблена в Microsoft Excel і називається відносним посиланням на формулу. Під час копіювання формули посилання на клітинки у формулі також переміщують відповідну кількість комірок поперек і вниз.
Бувають випадки, коли ви не хочете, щоб Excel проявляв таку поведінку, і поточний випадок є одним із таких. Щоб Excel не міняв посилання під час копіювання комірок, вставте "$" перед тією частиною посилання, яку потрібно заморозити. Приклади:
- $ A1 повідомляє Excel, що ви завжди хочете посилатися на стовпець A.
- B $ 1 говорить Excel, що ви завжди хочете посилатися на рядок 1.
- $ B $ 1 говорить Excel, що ви завжди хочете посилатися на клітинку B1.
Вивчення цього коду різко зменшить час, необхідний для створення робочих аркушів. Замість того, щоб вводити 144 формули, Мервін може використовувати цей скорочений текст, щоб ввести одну єдину формулу та скопіювати її у всі комірки.
Переглядаючи формулу Мервіна =B1*A2, ми усвідомлюємо, що частина виразу "B1" завжди повинна вказувати на число в рядку 1. Це слід переписати як "B $ 1". Розділ "A2" формули завжди повинен вказувати на число у стовпці A. Це слід переписати як "$ A2". Отже, нова формула в комірці B2 є =B$1*$A2.
Після введення формули в B2 я можу скопіювати та вставити її у відповідний діапазон. Excel виконує мої інструкції, і після копіювання я знаходжу такі формули:
- Клітина М2 містить
=M$1*$A2 - Осередок B13 містить
=B$1*$A13 - Клітина М13 містить
=M$1*$A13
Кожен із цих типів формул має свою назву. Формули без будь-яких знаків долара називаються відносними формулами. Формули, де і рядок, і стовпець зафіксовані знаком долара, називаються абсолютними формулами. Формули, де рядок або стовпець зафіксовані знаком долара, називаються змішаними формулами.
Існує скорочений метод введення знаків долара. Під час набору формули та закінчення посилання на клітинку ви можете натиснути клавішу для перемикання між 4 типами посилань. Скажімо, ви почали вводити формулу і ввели =100*G87.
- Натисніть F4, і ваша формула зміниться на
=100*$G$87 - Натисніть F4 ще раз, і ваша формула зміниться на
=100*G$87 - Натисніть F4 ще раз, і ваша формула зміниться на
=100*$G87 - Натисніть F4 ще раз, і ваша формула повернеться до оригіналу
=100*G87
Ви можете зробити паузу під час введення формули для кожного посилання на клітинку, щоб натиснути F4, поки не отримаєте правильний тип посилання. Натискання клавіш для введення формули Мервіна вище =B1*A1.
Одне з моїх улюблених варіантів використання змішаних посилань на формули з’являється, коли у мене є довгий список записів у стовпці А. Коли я хочу швидкий і брудний метод пошуку дублікатів, я іноді ввожу цю формулу в клітинку B4 і потім копіюю її:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Зверніть увагу, що другий член формули VLOOKUP використовує як абсолютну ($ A $ 2), так і змішану ($ A3) посилання для опису діапазону пошуку. По-англійськи, я кажу Excel, щоб завжди шукати з комірки $ A $ 2 до комірки у стовпці A трохи вище поточної комірки. Як тільки Excel зустрічає дублікат значення, результат цієї формули змінюється з # N / A! до значення.
Завдяки творчому використанню посилань на клітинки $ in, ви можете переконатись, що одну єдину формулу можна скопіювати до багатьох комірок з правильними результатами.
