трюк №52

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

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

Мал. 5.7. Дані, які будуть поміщені на діаграму

Якщо ви не пам'ятаєте, як створювати динамічні іменовані діапазони, зверніться до розділу «Трюк № 42. Створення збільшуються і зменшуються діапазонів».

Створіть динамічний іменований діапазон з ім'ям TEMP_DATES для дат в стовпці А, вибравши команду Вставка → Ім'я → Присвоїти (Insert → Name → Define) і ввівши наступну формулу: = OFFSET ($ A $ 1; 1; 0; COUNTA ($ A: $ A ) -1; 1). в російській версії Excel = зміщений ($ A $ 1; 1; 0; СЧЁТЗ ($ А: $ А) -1; 1).

Зверніть увагу, що відразу ж за аргументом СЧЁТЗ (COUNTA) коштує -1. Це гарантує, що заголовок не буде включено в іменований діапазон для певного ряду.

У цьому прикладі в якості аргументу функції COUNTA (СЧЁТЗ) вказано весь стовпець А ($ А: $ А), В ранніх версіях Excel краще обмежувати його набагато меншою групою осередків, щоб не перевантажувати обчислення. Іншими словами, вказуючи діапазон повністю, ви змушуєте Excel переглядати тисячі непотрібних осередків. Деякі функції Excel досить розумні, щоб визначити, які клітинки містять дані, деякі зробити це не можуть. У нових версіях Excel не обов'язково строго обмежувати діапазон, так як обробка великих діапазонів в них покращена.

Потім для температурних даних шпальти У налаштуйте ще один динамічний діапазон з ім'ям TEMP_READINGS, вказавши формулу = OFFSET ($ B $ 2; 0; 0; COUNTA ($ B: $ B) -1; 1). в російській версії Excel = зміщений ($ В $ 2; 0; 0; СЧЁТЗ ($ В: $ В) -1; 1). Тепер можна створити діаграму, якої сможе скористатися кримінальний адвокат. використовуючи створені динамічні іменовані діапазони замість посилань на осередки.

Мал. 5.8. Динамічні іменовані діапазони замість статичних посилань на діапазони

Виділіть дані (діапазон $ А $ 1: $ В $ 11), потім на стандартній панелі інструментів клацніть кнопку Майстер діаграм (Chart Wizard). На першому кроці майстра виберіть тип діаграми (в цьому прикладі ми використовуємо гистограмму) і натисніть кнопку Далі (Next). На другому кроці ви побачите дві вкладки: Діапазон даних (Data Range) і Ряд (Series). Вам потрібна вкладка Ряд (Series). Видаліть формулу, яка знаходиться в полі Значення (Value), і введіть таку формулу: = Sheetl! TEMP_READINGS.

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

Нарешті, видаліть формулу в полі Підписи осі X (Category X Labels) і введіть наступне: = Sheet1! TEMP_DATES. Виконайте інші кроки майстра діаграм, внісши необхідні зміни. Результат повинен виглядати, як на рис. 5.8. Нові записи в стовпці A (Dates) або стовпці В (Temperature) будуть при додаванні автоматично з'являтися в налаштованої діаграмі.

Побудова діаграми для кількох останніх даних

Ще один тип іменованих діапазонів, який можна використовувати з діаграмами, - це діапазони, які вибирають тільки останні 10 значень (можна вказати будь-яке число) з ряду даних. Спробуйте виконати наступне на тих же даних, які використовувалися в першій частині цього трюку. Для даних в стовпці А створіть динамічний іменований діапазон з ім'ям TEMP_DATES_10DAYS, який посилається на такі дані: = OFFSET ($ A $ 1; COUNTA ($ A: $ A) -10, 0, 10; 1), в російській версії Excel = зміщений ($ А $ 1; СЧЁТЗ ($ А: $ А) -10; 0; 10; 1).

Для даних в стовпці В створіть ще один динамічний іменований діапазон з ім'ям TEMP_READINGS_10DAYS і введіть наступну формулу: = OFFSET (Sheet1! $ А $ 1; COUNTA (Sheet15! $ А: $ А) -10; 1; 10; 1). в російській версії Excel = зміщений (Sheet1! $ А $ 1, CЧETЗ (Sheetl5! $ А: $ А) -10; 1; 10; 1).

Якщо ви хочете змінити кількість використовуваних значень, наприклад, на 20, змініть останню частину формули, щоб вона виглядала так: = OFFSET (Sheet1! $ А $ 1; COUNTA (Sheet15! $ А: $ А) -20; 1; 20; 1 ). в російській версії Excel = зміщений (Sheet1! $ A $ l; CЧETЗ (Sheet15! $ A: $ A) -20; 1; 20; 1).

Використання динамічних іменованих діапазонів з діаграмами забезпечить виняткову гнучкість і заощадить величезну кількість часу і зусиль, які ви витратили б на настройку діаграм після додавання ще одного запису до вихідних даних.