Заміна посилань іменами

Заміна посилань іменами

Вітаю всіх працівників «Б & К»! У мене таке запитання. Створюючи таблиці в програмі Excel, я часто користуюся іменованими діапазонами. Це — дуже зручний інструмент, але є одна проблема. Якщо імена коміркам присвоїти завчасно, то при написанні формули Excel підставить ці імена автоматично. Інша справа, коли формула вже готова. У цьому випадку після створення нового імені комірки формули залишаються без змін. І для того щоб скористатися всіма перевагами іменованих діапазонів, усю таблицю доводиться коригувати вручну. Підкажіть, що можна зробити в такому разі? Наперед завдячую.

С. І. Ковальов, бухгалтер, м. Харків

Відповідає

Рішення проблеми, звісно, існує. Причому автоматичну підстановку імен можна виконати в будь-якій версії програми Excel. Я покажу, як це зробити в Excel 2003 та Excel 2010.

Автоматична підстановка імен в Excel 2003

Для прикладу скористаємося таблицею, зображеною на рис. 1. У ній чотири колонки: найменування товарів та три ціни — роздрібна, дрібнооптова й оптова. Роздрібну ціну (колонка «

Дрібнооптову та оптову ціну підраховано за формулою з урахуванням знижки. Розмір знижки для дрібнооптової ціни знаходиться в комірці «D1». Знижка для оптових покупців знаходиться в комірці «D2». Таким чином, дрібнооптову ціну для товару «Printer Canon pixma ip1900» (комірка «C5») підрахуємо за формулою: «=B5-B5*$D$1». Оптова ціна цього товару (комірка «D5») має такий вигляд: «=B5-B5*$D$2». Ці формули скопійовано на всю висоту таблиці. Наше завдання — спочатку присвоїти коміркам «D1» і «D2» імена, а потім поширити ці імена на всі формули таблиці. Виконуємо такі дії:

1) виділяємо блок «C1:D2»;

2) викликаємо «Вставка → Имя → Создать…». Відкриється вікно, як на рис. 2;

3) вибираємо варіант «в столбце слева»;

4) натискаємо на «ОК».

Імена створено. Тепер комірка «D2» має назву «Скидка_МО», а комірка «D3» — «Скидка_Опт.». Ці назви Excel присвоїв автоматично, узявши їх з комірок «С1» і «С2». Дивимося, що сталося з формулами. Клацаємо по комірці «C5». У рядку формул, як і раніше, бачимо вираз «=B5-B5*$D$1». Отже, імена коміркам ми присвоїли, однак на формули ці імена не вплинули. Виконуємо такі дії:

1) залишаючись на поточному листі, викликаємо меню «Вставка → Имя → Применить…». Відкриється вікно «Применение имен», як на рис. 3. У цьому вікні вже показано два імена для діапазонів комірок;

2) утримуючи натисненою клавішу «Shift», виділяємо обидві назви діапазонів. У деяких випадках цього робити не потрібно, оскільки Excel сам виділить усі доступні діапазони;

3) у вікні «Применение имен» натискаємо на «ОК»;

4) стаємо на комірку «C5». У ній бачимо формулу «=B5-B5*Скидка_МО»;

5) стаємо на комірку «D5». Тепер у ній записано вираз «=B5-B5*Скидка_Опт.». Excel відкоригував формули, замінивши адреси зі знижками на імена відповідних комірок.

Автоматична підстановка імен в Excel 2010

Подивимося, який вигляд має заміна адрес їх іменами в програмі Excel 2010. Скористаємося таблицею з попереднього прикладу (рис. 1). Вважатимемо, що імена в ній ще не створено. Тому спочатку виконуємо такі дії:

1) виділяємо блок «

2) клацаємо по меню «

3) у групі «

4) у ньому вибираємо варіант «

5) натискаємо на «

Як і в попередньому випадку, формули поки що залишилися без змін. Щоб замінити у них посилання на іменовані діапазони, виконуємо такі дії:

1) на стрічці «

2) у цьому меню вибираємо варіант «Применить имена…». Excel проаналізує формули та замінить у них посилання іменами діапазонів.