Робота з макросами

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

Для запису макросу треба виконати наступні дії:

відкрити потрібну робочу книгу та активізувати робочий лист, де буде записаний макрос;

обрати команду Сервіс Þ Макрос Þ Початизапис. З’явиться діалогове вікно запису макросу;

ввести ім’я та опис макросу, а також сполучення клавіш;

обрати місце зберігання макросу. В даному випадку обирайте Ця книга зі списку Зберегти в;

клацнути на кнопці ОК, щоб почати запис макросу. Діалогове вікно зачиниться, та з’явиться панель інструментів Зупинити запис;

виконати заплановану дію;

клацнути на кнопці Зупинити запис панелі інструментів зупинки запису або обрати команду Сервіс Þ Макрос Зупинити запис

У зовнішньому джерелі даних. Ця опція запускає програму Microsoft Query для вибірки даних із зовнішньої бази даних.

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

У іншій звідній таблиці. У звідній таблиці зберігається копія даних, які аналізуються, для підвищення ефективності та більш економного використання пам’яті. Якщо необхідно створити декілька звідних таблиць на основі одного й того ж набору даних, використовуйте цей перемикач, щоб не допускати дублювання даних та не використовувати зайвих системних ресурсів.

На другому кроці треба обрати діапазон чарунок, на основі яких буде створена звідна таблиця [4]. Для цього необхідно на початковому листі виділити потрібний діапазон та у вікні Майстер звідних таблиць - крок 2 з 4 (рис. 2.3) натиснути кнопку Далі. Якщо перед запуском засобів Майстра звідних таблиць була активізована одна з чарунок списку, то на другому кроці діапазон буде виділений автоматично.

Рисунок 2.3 - Другий крок Майстра звідних таблиць

З’явиться діалогове вікно Майстер звідних таблиць - крок 3 з 4 (рис. 2.4), у якому необхідно прийняти основні рішення по розташуванню полів у звідній таблиці. При цьому треба пересунути назви полів з правої області діалогового вікна у відповідні області звідної таблиці [5]. Після цього натиснути кнопку Далі

Рисунок 2.4 - Вікно формування структури звідної таблиці

На останньому кроці Майстра звідних таблиць необхідно визначити, де буде розташована звідна таблиця (рис. 2.5).

Рисунок 2.5 - Четвертий крок Майстра звідних таблиць

Остаточний вигляд одного з варіантів звідної таблиці зображений на рисунку 2.6.

Рисунок 2.6 - Звідна таблиця

В отриманій звідній таблиці по стовпцям згруповані дані відповідають складам, рядки відповідають матеріалам. Цю таблицю можна уявити у більш компактному вигляді. Для цього необхідно виконати подвійне клацання по виділеній кнопці № складу та у діалоговому вікні Обчислення поля звідної таблиці (рис. 2.7) обрати опцію Орієнтація ð По листам.

Рисунок 2.7 - Вікно обчислення полів звідної таблиці

Після цього з’явиться нова кнопка Номер складу, а всі стовпці звідної таблиці згорнуться у один підсумковий. У цьому стовпці можна продивитися підсумкову інформацію як по окремим складам, обираючи номер складу, так і загальний підсумок по всім складам, обираючи "Все". Аналогічно можна згорнути в один рядок рядки матеріалів з можливістю продивлятися інформацію як по всім, так і по окремим матеріалам [6].

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

2.5.3 Додавання полів, що обчислюються

Можна створювати нові поля звідної таблиці на основі обчислювань та додавати їх до звідної таблиці. Поля, що обчислюються, базуються на існуючих полях та на введеній формулі. Щоб здійснити таке перетворення, виконайте наступні дії:

Клацніть правою кнопкою миші в середині звідної таблиці. Оберіть з контекстного меню команду Формули ð Поле, що обчислюється. З’явиться діалогове вікно Вставка поля, що обчислюється (рисунок 2.8).

Рисунок 2.8 - Вікно вставки поля, що обчислюється

Введіть ім’я нового поля в полі Ім’я.

В полі Формула введіть формулу, яка буде використовуватися для обчислення значень в новому полі. В цьому полі можна використовувати будь-які функції Excel. Для додавання назви поля звідної таблиці в формулу виділіть це поле в списку Поле та клацніть на кнопці Додати поле.

Клацніть на кнопці Додати для створення нового поля.

Наприклад, можна створити нове поле, що обчислюється за допомогою формули =СРЗНАЧ(Кількість) для обчислення середньої кількості товарів в кожному замовленні, а потім використовувати це нове поле в звідній таблиці [7, 8].

2.6 Консолідація даних

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

Для консолідації даних з декількох таблиць необхідно:

відкрити новий лист;

виконати команду Данні ð Консолідація… З’явиться діалогове вікно Консолідація (рис. 2.9);

Рисунок 2.9 - Вікно консолідації

у вікні Функція обрати функцію, що буде виконуватися над чарунками таблиці, яка створюється;

у вікні Посилання вказати перший діапазон консолідації. Для цього, не зачиняючи вікно Консолідація, перейти на лист, який містить першу з таблиць, які об’єднуються, виділити необхідний діапазон і у вікні Консолідація натиснути кнопку Добавить;

аналогічні дії виконати для останніх початкових таблиць;

встановити прапорці “ підписи верхнього рядку ”, “значення лівого стовпця” та “ Створити зв’язки з початковими даними ”.

2.7 Використання діаграм

Для побудови діаграм необхідно мати дані за однією або більше категоріями. Роздивимось побудову діаграми на основі початкової таблиці, представленої на рисунку 2.10

Рисунок 2.10 – Початкова таблиця з даними

Процес створення діаграми включає чотири етапи роботи засобів Майстра діаграм. Для запуску Майстра оберіть команду Вставка ðДіаграма. З’явиться вікно Майстер діаграм - крок 1 з 4 (рис. 2.11).

Рисунок 2.11 - Перший крок Майстра діаграм: тип діаграми

Майстер діаграм надає можливість виконати настройку майбутньої діаграми. На першому кроці обирається Стандартна або Нестандартна діаграма, тип діаграми (Лінійчата, Кругова, Гістограма або інша), а також вид діаграми (Звичайна, З накопичуванням або Нормована).

На другому кроці треба обрати діапазон чарунок, на основі яких буде створена діаграма. Вказуються посилання на комірки з підписами. Також надається зменшений загальний вигляд наступної діаграми, що дозволяє вже на другому кроці зменшити помилки щодо побудови діаграми [9]. Для цього необхідно на початковому листі виділити потрібний діапазон та у вікні Майстер діаграм - крок 2 з 4: джерело даних діаграми (рис. 2.12) натиснути кнопку Далі. Якщо перед запуском засобів Майстра діаграм була активізована одна з чарунок списку, то на другому кроці діапазон буде виділений автоматично.

Рисунок 2.12 - Другий крок Майстра діаграм

З’явиться діалогове вікно Майстер діаграм - крок 3 з 4: параметри діаграми (рис. 2.13), у якому необхідно заповнити дані щодо назви діаграми, надписів категорій і даних, параметрів ліній сітки та інші [10,11]. Після цього натиснути кнопку Далі

Рисунок 2.13 - Третій крок Майстра діаграм: параметри діаграми

На четвертому кроці треба обрати місце розміщення діаграми: на виділеному листі, або на існуючому, та у вікні Майстер діаграм - крок 4 з 4: розміщення діаграми (рис. 2.14) натиснути кнопку Готово.

Рисунок 2.14 - Четвертий крок Майстра діаграм: розміщення діаграми

Створена діаграма складається з наступних елементів: надпис, легенда, підписи, сітка, маркери, лінії та інше (рис. 2.15).

Рисунок 2.15 – Елементи створеної діаграми та панель її надпису

Усі елементи діаграми мають або панелі (рис. 2.15) або контекстне меню (рис. 2.16). За допомогою контекстних меню можна змінити встановлені раніше параметри діаграми [12].

Рисунок 2.16 – Контекстне меню для формату рядків даних

Контрольні питання

Як виділити область чарунок за допомогою миші?

Як виділити область чарунок за допомогою клавіатури?

Як додатково виділити другу область за допомогою миші чи клавіатури?

Склад та призначення панелі інструментів Excel?

Як налагодити панель інструментів?

Виклик та призначення контекстного меню.

Яким чином можна вказати адресу чарунки?

Як скопіювати зміст чарунки?

Як вилучити чарунку (групу чарунок)?

Як вилучити зміст чарунок?

Як вилучити з чарунок постійну інформацію, зберігши формули?

Як можна перейти від одного стилю позначення чарунок до іншого?

Що таке зовнішнє посилання?

У чому різниця між абсолютним і відносним посиланнями?

Якою буде адреса С3 в стилі R1C1?

Якою буде адреса $А$4 в стилі R1C1?

Якою буде адреса R3C8 в стилі А1?

Як буде виглядати адреса R[-3]C[1] в стилі А1 відносно чарунки R4C1?

Як можна локалізувати помилки у формулах?

Як можна викликати Майстра функцій, як з ним працювати?

Яким чином можна задати функцію автоскладання чарунок стовпця або рядка?

Як працює логічна функція ЯКЩО?

Як здійснити посилання на чарунки іншої таблиці або книги?

Як здійснити сортування або фільтрацію даних?

Що собою представляє макрос?

Для чого використовуються макроси?

Які бувають макроси?

Послідовність запису макросу.

Як задати кнопку для виклику макросу?

Що уявляє собою звідна таблиця?

Для чого використовуються звідні таблиці?

Послідовність дій по створенню звідних таблиць.

Які види джерел даних використовуються при створенні звідних таблиць?

Яким чином виконують згортку інформації у звідних таблицях?

Як оновлюються дані у звідній таблиці після внесення змін у початкову таблицю?

Що таке поля, що обчислюються, як вони створюються?

Як консолідувати дані, які зберігаються в різних таблицях?

Література

Берк В. Анализ данных с помощью Excel. – К.: Диалектика, 2005. – 560 с.

Григоришин Г.А. Excel для самостiйного вивчення. Навчальний посiбник. – К.:КНТ, 2007. – 200 с.

Далглеиш А. Сводные таблицы в Excel. Технологии PivotTables. – СПб: Питер, 2008. – 286 с.

Джелен Дж. Сводные таблицы в Excel 2007. – К.: Диалектика, 2008. – 336 с.

Курбатова В.П. Excel 2003. Стислий курс. – К.: Диалектика, 2004. – 288 с.

Куртер Дж., Маркви А. Microsoft Office 2000: учебный курс. Пер с англ. –С-Пб.: Питер, 2002. –640 с.

Мак-Федрис М. Формулы и функции в Excel 2003. – К.: Диалектика, 2006. – 576 с.

Мак-Федрис М. Формулы и функции в Excel 2007. – К.: Диалектика, 2008. – 640 с.

Уокенбах Т. Excel 2003. Библия пользователя. К.: Диалектика, 2007. – 766 с.

Уокенбах Т. Excel 2007. Библия пользователя. К.: Диалектика, 2008. – 816 с.

Уокенбах Т. Excel 2007: Профессиональное программирование на VBA. – К.: Диалектика, 2008. – 928 с.

Хэлворсон М., Янг М.Эффективная работа: Office XР. Пер с англ. – С-Пб.: Питер, 2003. –1072 с.