Більш складні методи роботи з діаграмами

У цьому розділі ми вивчимо різні теми, які не повязані безпосередньо з тематикою попередніх трьох розділів Ми розглянемо декілька нестандартних типів діаграм, дізнаємося, як за допомогою Excel створювати графіки виконання робіт (діаграми Ганта) і затримаємо увагу на спеціальних способах форматування Почнемо ж з техніки створення діаграм, які автоматично оновлюються при зміні не тільки даних, але і їх джерела

Створення динамічних діаграм за допомогою іменованих діапазонів

Якщо ви виділите ряд на діаграмі і поглянете на рядок формул, то побачите, що ряд створюється за допомогою формули, що використовує функцію РЯД (SERIES) РЯД – це функція особливого виду, діюча тільки в контексті діаграми для визначення рядів даних Її не можна застосовувати в листі, і не можна включати функції аркуша або формули в якості її аргументів

Для всіх типів діаграм, крім бульбашкової, функція РЯД має аргументи, перераховані в табл 271 У бульбашкових діаграмах функція РЯД приймає додатковий аргумент, що визначає діаметр (площа) бульбашок

Таблиця 271 Аргументи функції РЯД

Аргумент Обовязковий Визначає

/ Необовязковий

імя подпісі_

Необовязковий Необовязковий

Імя ряду в легенді

Підписи по осі категорій Якщо підписи опущені, Excel

категорій

використовує як підписів послідовні

цілі числа

значення

Обовязковий

Значення, що відображаються на діаграмі

порядок

Обовязковий

Порядковий номер даного ряду на діаграмі

Кожен з аргументів функції РЯД відповідає полю на вкладці Ряд (Series) діалогового вікна Вихідні дані (Source Data) Малюнок 271 ілюструє ці взаємозвязки Аргументімядорівнює Лист1 $ В $ 1, і він зявляється в полі Імя (Name) діалогового вікна (Оскільки осередок Лист1 $ В $ 1 містить підпис Ціна, в поле Ряд (Series) діалогового вікна цей ряд вказаний як Ціна) Аргумент  подпісі_ка-категорій дорівнює Лист1 $ А $ 2: $ А $ 1624 і зявляється в поле Підписи осі X (Category (X) Axis Labels) Аргументзначеннядорівнює Лист1 $ В $ 2: $ В $ 1б24, його можна знайти в поле Значення (Values) Так як на діаграмі всього один ряд, аргумент порядок дорівнює 1 Цей порядковий номер відображення ряду на діаграмі відповідає позиції ряду Ціна в списку Ряд діалогового вікна

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

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

Рис 271 Кожен аргумент формули РЯД діаграми зявляється у відповідному полі на вкладці Ряд діалогового вікна Вихідні дані

Всі імена діапазонів в Excel є, по суті, іменами формул Дуже часто формули перетворюються у посилання на діапазони Так, якщо ви виділите на Лісте1 осередку А1: А10 і створите імя для виділеного діапазону допомогою команди Присвоїти (Define), Excel визначить імя як = Лист1 $ А $ 1: $ А $ 10

Таким чином, ви можете зробити так,-що імя буде посилатися на різні діапазони осередків залежно від умов на робочому листі

Автоматичне відображення нових даних

У діаграмі, показаної на рис 271, підписи осі категорій знаходяться в діапазоні Лист1 $ А $ 2: $ А $ 27, а ряд даних Ціна – в діапазоні Лист1 $ В $ 2 $: В $ 27 Щоб ця діаграма автоматично відображала нові точки даних, що додаються в стовпці А і В, створіть на Лісте1 такі імена:

Дата = зміщені (Ліст1 $ А $ 1 1 0 СЧЕТЗ ($ А: $ А) -1)

Ціна = зміщені (Ліст1 $ В $ 1 1 0 СЧЕТЗ ($ В: $ В) -1)

ДИВИСЬ ТАКОЖ

Інформація про визначення імен приведена в розділі «Визначення імен та правила іменування» глави 12

Кожна з цих формул вибирає діапазон комірок, що починається з А2 (або В2), з кількістю рядків на одиницю меншим, ніж число заповнених клітинок у стовпці А (або В) У міру заповнення осередків в шпальтах А і В формула автоматично розширює діапазон (Нові дані включаються до Діапазон тільки тоді, коли вони вводяться в комірки безпосередньо нижче наявних даних Для того щоб використання цієї техніки призвело до потрібного результату, частина, що залишилася стовпців А і В має бути порожньою)

Після визначення динамічних імен ви можете застосувати їх у формулі РЯД

діаграми одним із двох способів:

► виділіть ряд діаграми, який ви хочете змінити, і відредагуйте формулу

РЯД в рядку формул

► виділіть діаграму, виберіть команду Вихідні дані (Source Data) в меню Діаграма (Chart), перейдіть на вкладку Ряд (Series) вікна і внесіть зміни у відповідні поля

ПРИМІТКА — –

Не можна застосувати імена за допомогою команди Застосувати (Apply) з підменю Імя (Name)

меню Вставка (Insert) – в даному контексті вона не працює

Робота з діалоговим вікном простіше, але результат один: зміни в одній частині будуть скопійовані в іншу При будь-якому варіанті дій залиште посилання на лист на своєму місці Наприклад, якщо в полі Значення записано = Лист1 $ В $ 2: $ В $ 1б24, замініть в цій формулі тільки абсолютний адресу діапазону імям діапазону Якщо імя унікально в робочій книзі, Excel відобразить його як імя рівня робочої книги, як показано на рис 272

Відображення лише нових точок

Якщо треба показати тільки 30 самих останніх точок на діаграмі зміни ціни,

визначте імена наступним чином:

Дата = зміщені (Ліст1 $ А $ 1 СЧЕТЗ ($ А: $ А) -30) 0 30)

Ціна-зміщені (Ліст1 $ В $ 1 СЧЕТЗ ($ В: $ В) -30 0 30)

Ці формули вказують Excel почати з 30-го рядка від кінця заповненої даними області і створити діапазони, що містять 30 рядків і 1 стовпець

УВАГА

Якщо ви видалите імя, яке було застосовано до ряду діаграми, ряд стане не-

коректним Excel не відновить посилання на діапазон, еквівалентну віддаленого імені

Рис 272 Після заміни абсолютних посилань на діапазони (в полях Значення і Підписи осі X)

діаграма буде автоматично відображати додані дані

Створення статичних діаграм за допомогою масивів

У деяких випадках вам може знадобитися «заморозити» діаграму так, щоб виключити її автоматичне оновлення Наприклад, для створення знімка даних на певний момент часу – або в якості точки відліку, або як сценарій

Один із способів створити таку діаграму – скопіювати куди-небудь вихідні дані і потім приховати або захистити аркуш, на якому вони розташовані Більш безпечний метод

– перетворити всі посилання на діапазони у формулах РЯД діаграми в масиви Для цього виділіть ряд на діаграмі, яку ви хочете зафіксувати У рядку формул повинна зявитися формула РЯД Натисніть клавішу F2, щоб включити режим редагування формули, і потім натисніть клавішу F9 Excel замінить всі посилання на діапазони (у тому числі імена) масивами констант Ці масиви констант залишатимуться незмінними при наступних операціях з робочим листом Єдиний спосіб «розморозити» таку статичну діаграму – знову відредагувати формули РЯД (або зробити еквівалентні зміни в діалоговому вікні Вихідні дані)

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

*

*