Пов’язані книги

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

1 В англомовній версії Excel використовуються кома і крапка з комою відповідно, а десяткова точка точкою і представлена – Прямуючи ред

істотно економите час на перерахунок та оновлення даних, а також память компютера

У цьому розділі мова піде про деякі прийоми роботи з повязаними допомогою зовнішніх (external) посилань книгами Детальніше про зовнішні посилання див раніше розділи

«Посилання на інші аркуші тієї ж книги» і «Посилання на листи інших книг»

Збереження повязаних книг

Коли ви маєте набір повязаних книг, перш ніж зберігати підсумкову книгу, необхідно зафіксувати зміни в усіх вихідних Нехай ви моделюєте бюджет фірми на 2004 рік в новій і поки Не збереження книзі Книга1 . Потім ви записуєте її у файл вже з імям Бюджет 2004

Тепер, припустимо, що для документування фактичних витрат по цих же статтях бюджету ви створили іншу книгу і дали їй імя Витрати 2004 Вона містить посилання на книгу Бюджет 2004, і тому є залежною Однак при створенні звязків ви посилалися на імя Книга1

Якщо ви зберігаєте Кнігу1 під імям Бюджет 2004, поки відкрита книга Витрати 2004, всі посилання на Кнігу1 в ній автоматично заміняться посиланнями Бюджет 2004 Тобто, наприклад, посилання = [Книга1] Лист1 $ А $ 1 при збереженні перетвориться до виду

= [Бюджет 2004х15] Лист1 $ А $ 1

Коли ви закриєте залежну книгу Витрати 2004 і спробуєте зберегти вихідну бюджетну Кнігу1, на екрані зявиться попередження: «Зберегти Витрати 2004 з посиланнями на документи, що не були збережені »Клацніть на кнопці ОК, підтверджуючи свої наміри Якщо потім перейменувати Кнігу1 в Бюджет 2004, посилання на неї в книзі Витрати 2004 не оновиться, і формули будуть як і раніше посилатися на Кнігу1 Тому, знову відкривши книгу Витрати 2004, ви побачите повідомлення, що вона містить звязку з іншою книгою, і програма запропонує оновити їх Якщо натиснути кнопку Так, Excel спробує знайти файл Книга1 Оскільки такого файлу вже не існує (він перейменований в Бюджет 2004), вам доведеться вказати місцезнаходження реального файлу, щоб програма зуміла оновити звязку в книзі Витрати 2004

Відкриття залежних книг

При збереженні книги, в якій присутні залежні формули, Excel запамятовує самі останні результати обчислень по них Якщо ви відкриєте і змініть вміст комірок у вихідній книзі після закриття залежною книги, ці значення не будуть оновлені Коли ви наступного разу завантажте залежну книгу, вона міститиме старі значення і на екрані зявиться повідомлення: «Дана книга містить автоматично оновлювані звязку з іншою книгою Оновити їх »

Якщо натиснути кнопку Ні (Dont Update), відкриється залежна книга без оновлення посилань на вихідну, а все залежні формули будуть містити останні обчислені та збережені значення

При виборі варіанту Так (Update) програма здійснить пошук вихідної книги – і, знайшовши її, прочитає вихідні значення і оновить формули в залежною книзі Зверніть увагу, що програма не відкриває залежну книгу, а тільки зчитує з неї відповідні дані для оновлення

Якщо ж програма не знайде вихідної книги, зявиться повідомлення, що повідомляє, що книга містить звязку, які не можуть бути оновлені Перед вами знову вибір: або натиснути кнопку Продовжити (Continue) для обовязкового відкриття цієї книги, або клацнути на кнопці Змінити (Edit Links) у діалоговому вікні Зміна звязків (Edit Links), щоб вибрати іншу поточну папку або вказати потрібний файл

Редагування звязків

Вихідні книги можна відкривати командою Звязку (Links) меню Правка Ця команда стає доступною, тільки якщо активна книга містить формули із зовнішніми посиланнями Її дією є відкриття діалогового вікна Зміна звязків (Edit Links), показаного на рис 1230

Рис 1230 Діалогове вікно Зміна звязків відображає зовнішнє посилання на вихідну книгу

Зупинимося докладніше на тому, що ми бачимо вданном вікні:

► Буква А (А) в стовпці Оновлення (Status) позначає автоматично оновлювану звязок, а буква 3 (М) – звязок, яка оновлюється вручну, тобто тільки при натисканні кнопки Оновити (Update Values)

► Щоб відкрити вихідну книгу, виберіть імя та натисніть кнопку Відкрити (Open Source)

► Якщо ви хочете вибрати іншу вихідну книгу, клацніть на кнопці Змінити

(Change Source)

► Клацніть на кнопці Розірвати звязок (Break Link) для перетворення всіх існуючих зовнішніх посилань у формулах в їх поточні значення Будьте уважні, ця команда не має зворотної дії

► Якщо натиснути кнопку Оновити, Excel витягне всі змінені дані з файлів вихідних книг, не відкриваючи їх

► Ви можете встановити звязок з обєктами і документами, створеними в інших додатках, наприклад в Microsoft Word При цьому в стовпці Тип (Турі) відображається назва програми і тип обєкта

ПРИМІТКА

Будьте уважні і обережні при переміщенні повязаних документів У вас не виникне жодних проблем, якщо ви працюєте з операційною системою Windows 2000 (або з більш пізньою версією) або Windows NT Наприклад, якщо ви створили звязок з яким-небудь документом Word, а потім перейменували або змінили місце розташування файлу документа, Excel все одно знайде його, завдяки можливостям файлової системи NTFS, аіменно системним журналам (Проте при використанні гіперпосилань переміщення або модифікація документа-джерела руйнує звязок) Якщо ж ви перемістили документ на диск з файловою системою FAT32 (MS-DOS і ранні версії Windows), програма не зуміє його знайти

Копіювання залежних формул

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

Припустимо, ми створили формулу = [форма2] Лист1 F1 в комірці А1 листа Лист1 книги Форма1, а потім за допомогою команд Вирізати і Вставити продублювали цю формулу в комірку В1 Оскільки посилання на клітинку F1 відносна, в комірці В1 формула перетвориться до такої: = [форма2] Лист1 С1 Природно, якби посилання в комірці А1 була абсолютною, формула зберегла би свій початковий вигляд = [форма2] Лист1 $ F $ 1

Копіювання і вставка в інші книги

Коли залежна формула копіюється з однієї книги в іншу, що містить посилання на яку-небудь третю книгу, посилання змінюються залежно від місцезнаходження формули Наприклад, нехай в комірці А1 книги Форма1 міститься формула

= [Форма2] Лист1 А1 При копіюванні і вставці цієї формули в комірку В5 книги

ФормаЗ ми побачимо вираз = [форма2] Лист1 В5

Точно так само при копіюванні формули з абсолютною посиланням в інші книги ця формула не змінюється Так, якщо скопіювати формулу = [форма2] Лист1 $ А $ 1 з осередку А1 книги Форма1 в комірку В5 книги ФормаЗ, її вид залишиться колишнім

Навіть якщо залежна формула вставляється в книгу, на яку сама посилається, вона все одно залишиться залежною Відповідно, якщо скопіювати, формулу

= [Форма2] Лист1 $ А $ 1 з осередку А1 книги Форма1 в клітинку A3 книги форма2, вона не зміниться, за винятком того, що посилання на книгу стане зайвою, оскільки знаходиться в тій же книзі В результаті формула скоротиться до = Лист1 $ А $ 1

Вирізання та вставка в інші книги

Excel немає налаштовує відносні посилання у формулах при вирізанні та вставці формул в інші книги так, як це відбувається у випадку копіювання залежних формул Тобто при вирізання формули = [форма2] Лист1 А1 з осередку А1 листа Лист1 книги Форма1 і вставці її в комірку В5 книги ФормаЗ формула збереже свій колишній вигляд

Вирізання та вставка впливають осередків

Коли ви вирізати і вставляти комірки, Excel за замовчуванням налаштовує всі посилання на ці осередки в формулах книги Зовсім формули, проте, не підкоряються цьому правилу

Нехай ви створили формулу = [форма2] Лист1 А10 в комірці А1 книги Форма1 Якщо закрити останню, а потім вирізати значення з комірки А10 книги форма2 і вставити його в сусідню клітинку В10, формула в комірці А1 книги Форма1 залишиться без змін І справа не в тому, що вона не була оновлена, раз вихідна книга була закрита

– програма, безумовно, стежить за змінами у вихідних даних Тому при наступному відкритті книги Форма1 ви побачите інформаційне вікно з текстом:

«Дана книга містить автоматично оновлювані звязку з іншою книгою Оновити їх »

Умовні вирази

Умовні вирази (Conditional test) служать для порівняння чисел, функцій, формул, текстових або логічних значень З їх допомогою можна робити вибірку значень, що потрапляють в певний діапазон, наприклад вище або нижче заданих порогових величин При створенні умов порівняння допустимо використовувати прості математичні та логічні оператори, а також різноманітні вбудовані функції, про які детально йдеться в розділі «Логічні функції» глави 14

Всі наведені нижче приклади включають в себе умовні вирази:

= А1> А2

=5-3&lt5*2

-СРЗНАЧ (В1: Вб)-СУМ (6,7,8)

= С2 = Жіночий

= РАХУНОК (А1: А10) = РАХУНОК (В1: В10)

= ДЛСТР (А1) = 10

Будь-яке умовне вираз повинен містити принаймні один оператор порівняння (див табл 124), який і визначає відношення між операндами вираження Наприклад, у виразі А1> А2 оператор «Більше» (>) зіставляє значення в осередках А1 і А2

Результатом умовного виразу є логічна величина ІСТИНА (TRUE або 1)

або БРЕХНЯ (FALSE або 0) Наприклад, формула = А1 = 10 повертає значення ІСТИНА,

якщо в комірці А1 дійсно знаходиться значення 10, і БРЕХНЯ в разі будь-якого іншого значення

Таблиця 124 Оператори порівняння

Оператор Визначення

= Перевірка на рівність

> Більше

< Менше

> = Більше або дорівнює

<= Менше або дорівнює

<> Не дорівнює

До складу Excel включені два дуже корисних інструменту, використання яких значно спрощує створення складних формул Майстер підсумовування (Conditional Sum Wizard) і Майстер підстановок (Lookup Wizard) належать до групи надбудов (Add-ins) – додаткових програмних модулів, що інтегруються в основний додаток Щоб переконатися, завантажені чи ні ці надбудови, відкрийте меню Сервіс Якщо ви не знайшли в ньому потрібного модуля, виберіть у цьому меню команду Надбудови (Add-ins) і встановіть відповідні прапорці в діалоговому вікні, після чого натисніть кнопку ОК Якщо модулі, що підключаються взагалі відсутні у списку команд, встановіть їх, запустивши програму установки Office Відповідну інформацію см в додатку А

Створення формул за допомогою Майстра підсумовування

Майстер підсумовування створює формули, спираючись на функції СУММ (SUM) і ЕСЛИ (IF) З його допомогою можна не тільки швидко і просто отримувати готові формули даного типу, а й навчитися самостійно без допомоги майстра конструювати власні формули з умовами порівняння

ДИВИСЬ ТАКОЖ

Докладніше про функції ЕСЛИ (IF) см в розділі «Логічні функції» глави 14

Щоб створити «умовну» формулу, виконайте такі дії:

1 Активізуйте таблицю або лист, з якими ви збираєтеся надалі працювати, і виберіть у підменю Майстер меню Сервіс команду Часткова сума (Conditional Sum) На екрані зявиться діалогове вікно Майстра підсумовування, представлене на рис 1231

Якщо клацнути в будь-якому місці таблиці до запуску майстра, автоматично буде виділена вся таблиця Якщо виділений діапазон вас влаштовує, натисніть кнопку Далі (Next) В іншому випадку за допомогою миші самостійно виділіть його Не забувайте включати у виділення позначення діапазонів рядків і стовпців Вид діалогового вікна зміниться, як показано на рис 1232, зліва

2 Виберіть в списку Підсумувати (Column To Sum) імя стовпця для розміщень підсумкових значень Саме для цього на кроці 1 і потрібно було виділяти всю таблицю, включаючи позначення діапазонів Якщо ви не знайшли останніх у списку, поверніться на крок назад і повторіть процедуру виділення діапазону

Рис 1231 Майстер підсумовування дозволяє створювати складні формули

Рис 1232 На кожному кроці ви виділяєте осередки, які будуть включені в підсумкову формулу

3 На цьому ж кроці 2 задайте стовпець, що містить потрібні позначення діапазонів, в списку Стовпець (Column), оператор порівняння – у списку Оператор (Is) і значення, з яким будуть порівнюватися дані цього стовпця, – у списку

Значення (This Value) Склад списку Значення змінюється в залежності від того, який стовпець обраний для аналізу У цьому списку відображаються тільки унікальні значення, а дублікати ігноруються

4 Клацніть на кнопці Додати умова (Add Condition) Критерій відбору значень буде поміщений в список в нижній частині діалогового вікна Допускається створювати до семи критеріїв Щоб поміняти умова, виділіть його в списку і натисніть кнопку Видалити умову (Remove Condition) Закінчивши цю процедуру, натисніть кнопку Далі

5 На кроці 3 роботи майстра виберіть один з варіантів: або Копіювати формулу в окрему клітинку (Copy Just The Formula To A Single Cell), або Копіювати формулу і значення умови (Copy The Formula And Conditional Values)

6 Клацніть на кнопці Далі, а потім виділіть комірку, в яку буде занесена підсумкова формула

7 Якщо на кроці 3 був обраний другий варіант – Копіювати формулу і значення умови, процедура розшириться на додатковий крок Вам необхідно вказати адресу комірки, де буде зберігатися умова Виділіть цю клітинку та натисніть кнопку Далі Все, що залишиться зробити після цього, – вибрати комірку, в яку буде поміщена підсумкова формула

8 Клацніть на кнопці Готово (Finish) Excel вставить результуючу формулу (і умова) в задані комірки робочого листа

РАДА

У більшості випадків на першому кроці в якості діапазону для підсумовування виділяється вся таблиця Однак це не завжди правильно Наприклад, на аркуші, представленому на рис 1231, Excel автоматично обводить рамкою діапазон А1: С20, що абсолютно неприпустимо, оскільки в нього входить обєднана осередок у верхній частині таблиці Необхідно виділити потрібний діапазон вручну

Ви можете додати у формулу додаткові умови відбору при наявності ж готового списку унікальних значень (в даному випадку таблиці прізвищ співробітників фірми) ви вправі розширити дію формули на весь цей список, як показано на рис 1233

РАДА

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

Створення формул за допомогою Майстра підстановок

Майстер підстановок при побудові формул використовує функції ІНДЕКС (INDEX) і П0ІСКПОЗ (MATCH) З його допомогою можна легко, швидко і наочно конструювати складні формули, а також набиратися досвіду для подальшого

самостійного їх створення Детальніше про функції ІНДЕКС та П0ІСКПОЗ розповідається в розділі «Функції для списків і масивів» глави 14 Для того щоб створити формулу, вам необхідно зробити наступне: 1 У підменю Майстер меню Сервіс (Tools) виберіть команду Пошук (Lookup), щоб вивести на екран діалогове вікно Майстра підстановок, представлене на рис 1234 зліва

Рис 1233 Ми додали список унікальних прізвищ співробітників (умов) в стовпець Е, а потім скопіювали формулу підсумовування в осередки F5: F9

Рис 1234 На перших двох кроках роботи Майстра підстановок задайте діапазон пошуку, а також рядок і стовпець, що містять шукане значення

ПРИМІТКА

Підсумкова формула, показана в рядку формул на рис 1233, укладена у фігурні дужки, що вказують на те, що вона є формулою масиву (див раніше розділ

«Масиви»)

2 Виділіть таблицю або аркуш, на якому знаходяться дані для обробки Якщо клацнути в будь-якому місці таблиці до запуску майстра, програма автоматично виділить весь поточний діапазон Якщо ви згодні, клацніть на кнопці Далі в іншому випадку виділіть потрібний діапазон за допомогою миші Не забувайте про позначення діапазонів рядків і стовпців На екрані зявиться таке вікно (рис 1234, справа)

3 Вкажіть в списку Виберіть стовпець, що містить шукане значення (Select The Column Label) імя потрібного стовпця, а також імя рядка в списку Виберіть рядок, що містить шукане значення (Select The Row Label) (Саме для цього на кроці 1 і потрібно було виділяти всю таблицю, включаючи позначення діапазонів рядків і стовпців) Якщо в списку немає таких, поверніться на крок назад і повторіть процедуру виділення діапазону

4 Вирішіть, чи хочете ви, щоб і параметри пошуку, і результат були виведені на робочий лист Якщо так, – то на кроці 3 виберіть пункт Копіювання формули і параметрів вибору (Copy The Formula And Lookup Parameters), якщо ні, – виберіть положення Копіювання формули в окрему клітинку (Copy Just The Formula To A Single Sell) (рис 1235) Перший варіант більш кращий, як буде показано пізніше Клацніть на кнопці Далі

Рис 1235 Якщо скопіювати формулу в окрему клітинку, параметри пошуку будуть зафіксовані при копіюванні і формули, і параметрів пошуку можна створити таблицю підстановки

5 Виберіть клітинку для розміщення формули Якщо на четвертому кроці ви вказали спосіб копіювання формули разом з параметрами (умовами), то для завершення роботи майстра буде потрібно виконати ще два кроки: вибрати місце розташування двох осередків для копіювання першого і другого параметрів пошуку Після визначення координат кожного осередку натискайте кнопку Далі (Next)

6 Клацніть на кнопці Готово (Finish)

На рис 1236 показано, як за допомогою Майстра підстановок створювати таблицю пошуку

Рис 1236 Щоб змінити значення в комірці R5, виберіть інші позначення діапазонів

Як вже говорилося, в разі копіювання формули разом з параметрами ці параметри зявляються на робочому листі, що ми і бачимо в прикладі для осередків Р5 і Q5 Підсумкова формула, збережена в комірці R5, в Як перші аргументів функції ПОИСКПОЗ бере відносні посилання якраз на осередки Р5 і Q5 Це дозволяє використовувати формулу двояко: по-перше, замість призначених параметрів пошуку в дані комірки можна помістити будь-які інші заголовки таблиці (наприклад, Вер, або Виріб 12, або і той і інший), і формула пошуку знайде наступне перетин а по-друге, оскільки посилання на комірки Р5 і Q5 у формулі є відносними, то саму формулу можна скопіювати в інші комірки і ввести додаткові параметри в осередку з тим же самим відносним місцем розташування

Джерело: Ефективна робота: 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>

*

*