Створення формул та аналіз даних – ЧАСТИНА 2

5 Клацніть на ярличку Ліста2 в нижній частині вікна книги2

6 Клацніть на осередку А2 Вид екрану в цей момент показаний на рис 121

7 Натисніть клавішу Enter, щоб зафіксувати посилання

Стиль посилань R1C1

Даний стиль посилань означає, що Excel посилається на коміркиза номерамирядків і стовпців Наприклад, посилання виду R1C1 треба розуміти як рядок (Row) 1, стовпець (Column) 1 Тобто запис R1C1 позначає посилання на ту ж саму комірку, що і відносна посилання А1 Хоча звичайна нотація використовується частіше, в деяких програмах обробки електронних таблиць, таких як Multiplan, стандартом є запис посилань у вигляді R1C1 Excel орієнтується на буквено-цифрову нумерацію стовпців і рядків, наприклад А1 і Z100

Для активізації подібного стилю записи посилань виберіть у меню Сервіс (Tools) команду Параметри (Options), відкрийте вкладку Загальні (General) діалогового вікна та встановіть прапорець Стиль посилань R1C1 (R1C1 Reference Style) Після цього всі посилання в формулах візьмуть зазначений вид Наприклад, комірка М10 стане R10C13, а осередок IV65536 (остання осередок листа) перетвориться на R65536C256

Рис 121 Створення зовнішнього посилання за допомогою миші

У синтаксисі R1C1 враховується швидше відносне розташування осередку, на яку посилається формула, ніж її фактичне (абсолютне) місце розташування на аркуші Так, припустимо, що ви, хотіли б ввести в комірку R10C2 (В10) формулу, яка складає значення в осередках R1C1 (А1) і R1C2 (В1) Для цього виділіть клітинку R10C2, введіть знак рівності, виділіть комірку R1C1, введіть знак «плюс», потім виділіть клітинку R1C2 і натисніть клавішу Enter У рядку формул зявиться вираз

= R [-9] C [-1] + R [-9] C Негативні номери рядків і стовпців у квадратних дужках говорять про те, що осередок, на яку вказує посилання, знаходиться вище і лівіше клітинки з формулою Відповідно позитивні значення, навпаки, показують, що цільова осередок знаходиться нижче і правіше клітинки з формулою Квадратні дужки позначають відносні посилання Таким чином, дана формула може читатися наступним чином: «Скласти клітинку, розташовану на девять рядків вище і один стовпець лівіше вічка з формулою, з осередком, що знаходиться на девять рядків вище в тому ж стовпці»

У відносній посиланням номери повинні бути укладені в квадратні дужки, в іншому випадку Excel буде вважати, що ви використовуєте абсолютні посилання Наприклад, запис = R8C1 + R9C1 увазі абсолютну адресацію осередків у рядках 8 та 9 стовпця 1

Поведінка посилань при копіюванні формул

Одне з корисних властивостей формул – це можливість їх копіювати і вставляти в інші осередки листа Але перш ніж скористатися нею, необхідно чітко уявляти собі, як поводяться посилання в момент вставки, щоб отримати передбачуваний результат

Копіювання відносних посилань

Коли ви копіюєте осередок, що містить формулу з відносною посиланням, ця посилання змінюється автоматично залежно від місця розташування цільової комірки для вставки формули Подивіться на рис 122 – ми ввели в комірку F4 формулу = СРЗНАЧ (В4: Е4), яка усереднює значення у вказаному діапазоні і виводить результат у тому ж осередку F4

Рис 122 Осередок F4 містить відносну посилання на діапазон комірок зліва

Тепер потрібно закінчити обчислення середніх значень і для інших осередків цього стовпця Замість того щоб щоразу вводити в осередок шпальти F нову формулу, простіше просто скопіювати її Для цього виділіть клітинку F4 і виберіть у меню Правка (Edit) команду Копіювати (Сміттю) Потім виділіть діапазон F5: F8 і в меню Правка скористайтеся командою Спеціальна вставка (Paste Special) Встановіть перемикач Вставити (Insert) у положення Формули та формати чисел (Formulas And Number Formats) це дозволить уникнути непотрібного форматування виділених осередків Результат операції представлений на рис 123 Тепер в кожному осередку шпальти F міститься формула, що обчислює середнє значення у відповідному рядку цієї таблиці Наприклад, комірка F7 містить формулу = СРЗНАЧ (В7: Е7)

Рис 123 Ми продублювали відносне посилання з осередку F4 в осередки діапазону F5: F8

Копіювання абсолютних посилань

Якщо ви хочете, щоб при копіюванні формул посилання на клітинку не змінювалася, використовуйте не відносні, а абсолютні посилання Так, на рис 124 зліва в клітинку В2 введено значення ставки погодинної оплати праці, а в комірці С5 міститься формула

з відносною посиланням: = В2 * В5 Припустимо, ви хочете скопіювати формулу з комірки С5 в діапазон Сб: С8 На рис 124 праворуч показано, що станеться, якщо ви спробуєте це зробити: Excel видасть повідомлення про помилку Здавалося б, формули в діапазоні Сб: С8 повинні посилатися на клітинку В2, але насправді цього не відбувається, і в комірці С8 можна побачити зовсім неправильний запис, на зразок = В5 * В7

Рис 124 Формула в комірці С5 містить відносне посилання Ми скопіювали цю формулу в комірки С6: С8 і отримали невірний результат

Справа в тому, що посилання на клітинку В2 у вихідній формулі відносна, і при копіюванні формули в інші комірки посилання також змінюється Щоб обчислення виконувалися без помилки, в даному випадку потрібно використовувати не відносну, а абсолютну посилання на комірку В2

Щоб змінити тип посилання, виділіть ще раз осередок С4, клацніть у рядку формул на засланні на клітинку В2 і натисніть клавішу F4 У результаті формула прийме вигляд

= $ В $ 2 * В5

Тепер при копіюванні цієї формули в діапазон С6: С8 змінюватися буде тільки відносне посилання на комірку В5, а абсолютне посилання на клітинку В2 залишиться як є Таким чином, в цікавій вас комірці, наприклад С7 (рис 125), буде отримана коректна формула = $ В $ 2 * В7

Рис 125 Перш ніж копіювати формулу, перетворіть відносне посилання В2 в абсолютну – $ В $ 2

Копіювання змішаних посилань

У формулах допускається використовувати змішані посилання, що дозволяє фіксувати тільки певну частину осередків (як ви памятаєте, у змішаних посиланнях одна частина посилання абсолютна, а інша – Відносна) При копіюванні формули

зі змішаною посиланням Excel жорстко привязує абсолютну частина цього посилання і змінює відносну відповідно з місцем розташування кінцевої комірки

Щоб створити посилання змішаного типу, натисніть клавішу F4 стільки разів, скільки потрібно для вибору необхідного типу змішаної посилання Наприклад, від посилання В2 можна перейти до $ В $ 2, потім – до В $ 2 і нарешті до останнього варіанту – $ В2

При розрахунку відрахувань по кредиту в таблиці, показаної на рис 126, використовуються два типи посилань: змішані і абсолютні Досить ввести тільки одну формулу в комірку Сб, а потім просто поширити її на весь діапазон C6: F10 Ми в цей осередок занесли формулу =-ПЛТ ($ В6, $ С $ 3, С $ 5), яка обчислює щорічні відрахування по кредиту в сумі $ 10 000 на період 15 років при кредитній ставці 6%, і тим самим оцінили можливі виплати при інших варіантах цікавлять нас процентних утримань

Рис 126 У цій таблиці використовуються посилання змішаного типу

Перше посилання у формулі ($ В6) вказує, що при розрахунку завжди слід брати значення з шпальти В, але посилання на рядок є відносною, тому може змінюватися Точно так само, змішана посилання З $ 5 означає, що при розрахунку завжди використовуються значення рядка 5, але посилання на стовпець не фіксована Наприклад, комірка Е8 після копіювання формули в діапазон C6: F10 буде містити формулу = ПЛТ ($ В8, $ С $ 3, Е $ 5) Якби не змішані посилання, довелося б для кожного осередку цього діапазону вводити формули окремо

ВИРІШЕННЯ ПРОБЛЕМ

Якщо ви використовуєте функцію СУММ (SUM) в нижньому рядку таблиці, то при додаванні в таблицю нових рядків (між вихідними значеннями і рядком з формулами) нові дані не будуть враховуватися в розрахунку У даному випадку ви зіткнулися з віковою проблемою, властивою будь-яким електронних таблиць Але в Excel є можливість виправлення цього недоліку Посилання на діапазон в аргументі функції СУММ не змінюється при додаванні рядків Однак, якщо ви почнете вводити значення в нові додані осередку, посилання на діапазон автоматично оновиться Єдина умова – всі значення потрібно вводити за один раз, починаючи з клітинки, розташованої безпосередньо нижче вказаного у формулі діапазону Якщо почати введення з середини групи нових осередків, посилання на діапазон у формулі підсумовування залишиться тією ж Докладніше про функції СУММ див розділ

«Функція СУММ» глави 14

Редагування формул

Формули редагуються так само, як і будь-які текстові значення Для видалення посилання або інших символів з формули виділіть в комірці або в рядку формул потрібні символи і натисніть клавішу Backspace або Delete Щоб замінити одне посилання другий, виділіть заменяемую посилання і клацніть на комірці, на яку хочете послатися в цій формулі відносні посилання заміняться автоматично

Ви можете додавати у формули додаткові посилання Наприклад, щоб вставити посилання на клітинку В1 в формулу = А1 + А3, просто встановіть покажчик, скажімо, між А1 і знаком плюса і введіть + В1 (або введіть знак «плюс», а потім клацніть на комірці В1) У результаті формула прийме наступний вигляд: = А1 + В1 + А3

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*