Якщо ви працюєте в Excel більше другого дня, то, ймовірно, ви вже зустрічали або використовували посилання на знак долара у формулах і функціях Excel, наприклад $D$2 or 3 F$ тощо. Давайте нарешті з’ясуємо, що саме вони означають, як вони працюють і де вони можуть бути корисними у ваших файлах.
Відносні посилання
Це звичайні посилання у вигляді букви стовпця-номеру рядка ( A1, С5, тобто «лінкор»), що міститься в більшості файлів Excel. Їх особливістю є те, що вони зсуваються при копіюванні формул. Ті. C5, наприклад, перетворюється на С6, С7 тощо під час копіювання вниз або до D5, E5 тощо при копіюванні вправо тощо. У більшості випадків це нормально і не створює проблем:
Змішані посилання
Іноді небажано те, що посилання у формулі при копіюванні «ковзає» щодо вихідної комірки. Тоді для фіксації посилання використовується знак долара ($), який дозволяє фіксувати те, що передує. Так, наприклад, посилання $C5 не змінюватиметься в стовпцях (тобто С ніколи не перетвориться на D, E or F), але може зміщуватися по рядках (тобто може зміщуватися на $ C6, $ C7 тощо). так само C$5 – не буде рухатися по рядках, але може «ходити» по стовпцях. Такі посилання називаються змішаний:
Абсолютні посилання
Добре, якщо додати в посилання відразу обидва долари (5 канадських доларів) – це перетвориться на абсолютний і жодним чином не зміниться під час будь-якого копіювання, тобто долари фіксуються щільно, а рядок і стовпець:
Найпростіший і найшвидший спосіб перетворити відносне посилання на абсолютне або змішане – вибрати його у формулі та кілька разів натиснути клавішу F4. Цей ключ обводить усі чотири можливі варіанти фіксації посилання на клітинку: C5 → 5 канадських доларів → $C5 → C$5 і все заново.
Все просто і зрозуміло. Але є одне «але».
Припустімо, ми хочемо створити абсолютне посилання на клітинку С5. Такий, на який вона ЗАВЖДИ посилалася С5 незалежно від будь-яких подальших дій користувача. Виходить смішна річ – навіть якщо ви зробите посилання абсолютним (тобто 5 канадських доларів), у деяких ситуаціях все ще змінюється. Наприклад: якщо видалити третій і четвертий рядки, він зміниться на 3 канадських доларів. Якщо вставити стовпець ліворуч С, то він зміниться на D. Якщо вирізати клітинку С5 і вставте в F7, то він зміниться на F7 і так далі. Що робити, якщо я хочу справді жорстке посилання, на яке завжди посилатиметься С5 і нічого більше за будь-яких обставин чи дій користувача?
Дійсно абсолютні посилання
Рішення полягає у використанні функції НЕПРАВИЛЬНО (НЕПРЯМИЙ), який генерує посилання на клітинку з текстового рядка.
Якщо ввести формулу в клітинку:
=INDIRECT(“C5”)
=INDIRECT(«C5»)
тоді він завжди вказуватиме на клітинку з адресою C5 незалежно від будь-яких подальших дій користувача, вставки або видалення рядків тощо. Єдина невелика складність полягає в тому, що якщо цільова клітинка порожня, то НЕПРАВИЛЬНО виводить 0, що не завжди зручно. Однак це можна легко обійти, використовуючи дещо складнішу конструкцію з перевіркою через функцію ІСБЛАНК:
=ЯКЩО(НЕПРЯМИЙ(НЕПРЯМИЙ(“C5″)),””,НЕПРЯМИЙ(“C5”))
=ЯКЩО(ПУСТИЙ(НЕПРЯМИЙ(«C5″));»»;НЕПРЯМИЙ(«C5»))
- XNUMXD посилання на групи аркушів під час консолідації даних із кількох таблиць
- Навіщо потрібен стиль посилання R1C1 і як його відключити
- Точне копіювання формул макросом з надбудовою PLEX