Таблиці й діаграми з Excel, які оновлюються в курсовій самі

Аналітичний розділ курсової чи дипломної роботи містить 10–20 таблиць, кілька діаграм і десятки чисел у тексті, і всі вони походять з одного масиву даних. Коли керівник просить замінити 2022 рік на 2025-й або у звітності знаходиться помилка, ручне оновлення означає передрукувати кожну таблицю, перебудувати діаграми і знайти кожне число в тексті. Після такої роботи майже неминуче з'являються розбіжності: у тексті «прибуток зріс на 18 %», а в таблиці 17,6 %.

Зв'язування об'єктів у Microsoft Office вирішує цю задачу. Word показує дані безпосередньо з файлу Excel і оновлює їх після змін у джерелі. Опишемо, як організувати файл Excel, зв'язати таблиці, діаграми й окремі числа, оновити роботу під новий рік і підготувати остаточний файл до здачі.

1. Як організувати файл Excel

Зв'язки працюють надійно лише тоді, коли файл Excel побудовано за чіткою схемою: дані, розрахунки й діаграми розміщено на окремих аркушах, а Word отримує вже готові таблиці (рис. 1).

• Аркуш «Дані» містить вихідні показники звітності за всі доступні роки: у рядках коди рядків форм 1 і 2, у стовпцях роки. Нові дані додають сюди новим стовпцем.
• Аркуш «Розрахунки» містить таблиці саме в тому вигляді, у якому вони будуть у роботі, і клітинку «Звітний рік». Роки в заголовках таблиць обчислюються від неї, а значення підтягуються з аркуша «Дані» формулою (рис. 2).
• Діаграми будують на основі таблиць аркуша «Розрахунки», а не вихідних даних. Тоді разом із роком змінюються і підписи категорій.

Формула в клітинці C4 знаходить на аркуші «Дані» рядок з кодом 2000 і стовпець з роком 2023:

=ІНДЕКС(Дані!$B$2:$H$60;ПОШУКПОЗ($A4;Дані!$A$2:$A$60;0);ПОШУКПОЗ(C$3;Дані!$B$1:$H$1;0))

В англомовному Excel це функції INDEX і MATCH, а роздільником аргументів є кома. Заголовки років задано формулами =$B$1-2, =$B$1-1, =$B$1. Після заміни 2025 на 2026 у клітинці B1 уся таблиця перебудується на 2024–2026 роки, якщо дані за 2026 рік уже внесено на аркуш «Дані».

Три правила оформлення в Excel

1. Оформлюйте таблицю в Excel за вимогами методички: шрифт, межі, вирівнювання, формат чисел з пробілом між розрядами і комою. Під час оновлення Word бере форматування з джерела, тож налаштовувати таблицю в Word немає сенсу.
2. Округлюйте значення функцією ОКРУГЛ (ROUND), а не лише форматом клітинки. Інакше складові структури, показані з одним знаком після коми, у сумі дадуть 99,9 % або 100,1 %, а підсумок покаже 100,0 %. Рецензенти помічають цю розбіжність.
3. Присвойте кожній таблиці ім'я. Виділіть діапазон і введіть ім'я в поле імені ліворуч від рядка формул або скористайтеся «Формули → Диспетчер імен» (Formulas → Name Manager). Наприклад: Табл_2_3.

2. Зв'язування таблиці

1. В Excel виділіть діапазон таблиці (без назви: її задає підпис у Word) і натисніть Ctrl+C.
2. У Word поставте курсор під назвою таблиці й відкрийте «Основне → Вставити → Спеціальне вставлення» (Home → Paste → Paste Special) або натисніть Ctrl+Alt+V.
3. Виберіть перемикач «Зв'язати» (Paste link), у списку виберіть «Текст у форматі RTF» (Formatted Text (RTF)) і натисніть OK (рис. 3).

Формат RTF вставляє діапазон як звичайну таблицю Word: її ширина узгоджується з полями сторінки, текст доступний для пошуку і перевірки на нормоконтролі. Варіант «Аркуш Microsoft Excel (об'єкт)» вставляє вбудований об'єкт, який поводиться як рисунок: шрифт у ньому масштабується разом з об'єктом і може не відповідати вимогам методички.

Чому таблиці потрібне ім'я

Натисніть Alt+F9, щоб побачити код поля, яке створив Word:

{ LINK Excel.Sheet.12 "C:\\Курсова\\Аналіз.xlsx" "Розрахунки!R3C1:R6C6" \a \f 4 \r }

Ключ \a означає автоматичне оновлення, \r — вставлення у форматі RTF, \f 4 — збереження форматування джерела Excel. Діапазон задано адресою R3C1:R6C6. Якщо в Excel вище таблиці вставити рядок, Word і далі братиме дані за старою адресою, і в роботі з'явиться зсунута таблиця. Замініть адресу на ім'я діапазону: ім'я в полі LINK допускається [1] і переміщується разом з таблицею.

{ LINK Excel.Sheet.12 "C:\\Курсова\\Аналіз.xlsx" "Табл_2_3" \a \f 4 \r }

Ще раз натисніть Alt+F9, щоб повернутися до звичайного вигляду, виділіть таблицю і натисніть F9, щоб оновити поле.

3. Зв'язування діаграми

Діаграму будують в Excel за вимогами методички: без назви всередині області діаграми (її замінює підпис рисунка у Word), з назвами осей і одиницями виміру, у відтінках сірого або з контрастними кольорами, що розрізняються під час друку.

1. Виділіть діаграму в Excel і натисніть Ctrl+C.
2. У Word натисніть «Основне → Вставити» і в параметрах вставлення виберіть «Зберегти вихідне форматування й зв'язати дані» (Keep Source Formatting & Link Data).
3. Якщо після змін у Excel діаграма не оновилася, виділіть її у Word і виберіть «Конструктор діаграм → Оновити дані» (Chart Design → Refresh Data).

Варіант «вбудувати книгу» копіює всі дані в документ: діаграма більше не залежить від файлу Excel і не оновлюється.

4. Числа в тексті

Найбільше розбіжностей виникає в числах, наведених у тексті аналізу. Їх також можна зв'язати:

1. Розрахуйте потрібне значення в окремій клітинці, наприклад =ОКРУГЛ((E6/D6-1)*100;1) для приросту чистого прибутку, і присвойте їй ім'я Приріст_прибутку.
2. Скопіюйте клітинку, у Word поставте курсор у потрібне місце речення і відкрийте «Спеціальне вставлення».
3. Виберіть «Зв'язати» і «Неформатований текст» (Unformatted Text). Число прийме шрифт абзацу, у який його вставлено.

Так само зв'язують клітинку «Звітний рік», щоб роки в тексті («у 2025 р.») і в назвах таблиць («за 2023–2025 рр.») змінювалися разом з даними.

Щоб бачити, які числа є зв'язаними, увімкніть затінення полів: «Файл → Параметри → Додатково», розділ «Показувати вміст документа», параметр «Затінення полів: Завжди» (Field shading: Always). Затінення видно лише на екрані, під час друку воно не відображається.

5. Оновлення роботи під нові дані

Прохання керівника «замінити дані на 2025 рік» виконується за кілька хвилин:

1. На аркуші «Дані» додайте стовпець 2025 року зі звітності підприємства.
2. На аркуші «Розрахунки» змініть значення клітинки «Звітний рік» і збережіть файл.
3. Відкрийте роботу у Word. На запит про оновлення зв'язків з іншими файлами відповідайте «Так». Якщо файл уже відкрито, натисніть Ctrl+A, потім F9: оновляться всі поля, зокрема зв'язки.
4. Перечитайте текст аналізу. Числа оновлюються автоматично, а слова — ні: якщо показник раніше зростав, а тепер знизився, формулювання «зріс» доведеться виправити вручну.

Усіма зв'язками документа керують через вікно «Зв'язки»: «Файл → Відомості → Змінити зв'язки з файлами» (File → Info → Edit Links to Files) у блоці пов'язаних документів праворуч (рис. 4).

У цьому вікні можна оновити вибрані зв'язки (1), вказати новий шлях до файлу Excel після переміщення або перейменування (2), заборонити Word змінювати форматування під час оновлення (3) і розірвати зв'язок (4). Щоб застосувати дію до всіх зв'язків одразу, виділіть увесь список, клацнувши перший рядок, а потім з натиснутою клавішею Shift останній.

Word зберігає повний шлях до файлу Excel. Тримайте обидва файли в одній папці й не перейменовуйте їх. Після перенесення папки на інший комп'ютер шлях доведеться оновити кнопкою «Змінити джерело».

6. Підготовка остаточного файлу

Файл зі зв'язками не варто надсилати керівнику чи завантажувати в систему перевірки. На іншому комп'ютері Word попередить про зв'язки з недоступними файлами, а спроба оновлення закінчиться повідомленням про помилку.

1. Збережіть копію роботи під іншою назвою, наприклад «Курсова_фінал.docx». Робочу версію зі зв'язками залиште для подальших правок.
2. У копії відкрийте вікно «Зв'язки», виділіть усі зв'язки і натисніть «Розірвати зв'язок». Таблиці й числа стануть звичайним текстом, діаграми — статичними.

7. Типові проблеми

Підсумок

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

Після оновлення таблиці зручно один раз перевірити макросом, описаним у статті про форматування таблиць за методичкою.