Іскрові лінії Excel - повний посібник із прикладами

Функція Sparklines була представлена ​​в Excel 2010.

У цій статті ви дізнаєтесь все про Excel Sparklines і побачите кілька корисних прикладів цього.

Що таке Sparklines?

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

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

Хоча Sparklines - це крихітні діаграми, вони мають обмежену функціональність (порівняно зі звичайними діаграмами в Excel). Незважаючи на це, Sparklines-це чудово, оскільки ви можете легко створювати ці тенденції (і навіть викиди/високі та низькі моменти) та робити ваші звіти та інформаційну панель більш зручними для читачів.

На відміну від звичайних діаграм, Sparklines не є об’єктами. Вони знаходяться в клітині як фон цієї клітини.

Типи Sparklines в Excel

У Excel є три типи іскрових ліній:

• Лінія
• Колонка
• Виграш-програш

На зображенні нижче я створив приклад усіх цих трьох типів іскрових ліній.

Перший у G2-це іскровий рядок рядкового типу, у G3-тип стовпця, а в G4-тип виграшу.

Ось кілька важливих речей, які потрібно знати про Excel Sparklines:

1. Іскрові лінії є динамічними і залежать від базового набору даних. Коли базовий набір даних змінюється, іскровий рядок автоматично оновлюється. Це робить його корисним інструментом для використання при створенні інформаційних панелей Excel.
2. Розмір іскрових ліній залежить від розміру комірки. Якщо ви зміните висоту або ширину комірки, іскрову лінію буде відрегульовано відповідним чином.
3. Хоча у вас є іскровий рядок у комірці, ви також можете ввести в неї текст.
4. Ви можете налаштувати ці іскрові лінії - наприклад, змінити колір, додати вісь, виділити максимальні/мінімальні точки даних тощо. Ми розглянемо, як це зробити для кожного типу іскрової лінії, далі в цьому підручнику.

Примітка: Іскровий лінія з виграшем така ж, як іскровий рядок стовпця, але не показує величини значення. Його краще використовувати в ситуаціях, коли результат двійковий, такий як Так/Ні, Правда/Неправда, Голова/Хвіст, 1/-1 тощо. Наприклад, якщо ви плануєте, чи йшов дощ протягом останніх 7 днів або ні, ви можете скласти виграшну програшу з 1 за дні, коли йшов дощ, і -1 за дні, коли цього не було. У цьому посібнику все, що стосується іскрових ліній колонок, також можна застосувати до іскрових ліній без виграшу.

Тепер давайте розглянемо кожен із цих типів іскрових ліній та всі налаштування, які ви можете з ним зробити.

Вставка Sparklines в Excel

Скажімо, ви хочете вставити іскрову лінію (як показано нижче).

Нижче описано, як вставити іскрову лінію в Excel:

1. Виберіть клітинку, в якій потрібно розмістити іскрову лінію.
2. Натисніть на вкладку Вставка.
3. У групі Sparklines натисніть на опцію Лінія.
4. У діалоговому вікні «Створити іскрові лінії» виберіть діапазон даних (A2: F2 у цьому прикладі).
5. Натисніть OK.

Це додасть іскрову лінію у клітинку G2.

Щоб вставити іскрову лінію "Колонка" або "Виграш-програш", вам потрібно виконати ті ж кроки вище, і вибрати Стовпці або Виграш-програш замість Лінії (на кроці 3).

Хоча вищевказані кроки вставляють базову іскрову лінію в клітинку, ви можете зробити деякі налаштування, щоб покращити її.

Вибравши клітинку зі Sparkline, ви помітите, що контекстна вкладка - Дизайн інструментів Sparkline - стає доступним. На цій контекстній вкладці ви знайдете всі параметри налаштування для вибраного типу іскрової лінії.

Редагування набору даних існуючих іскрових ліній

Ви можете редагувати дані існуючої іскрової лінії за допомогою опції Редагувати дані. Натиснувши спадне меню Редагувати дані, ви отримаєте такі параметри:

• Редагувати розташування та дані групи: Використовуйте це, коли ви згрупували декілька іскрових ліній і хочете змінити дані для всієї групи (групування розглянуто далі в цьому підручнику).
• Редагування даних одиночної Sparkline: Використовуйте це, щоб змінити дані лише для вибраної іскрової лінії.

Натиснувши будь -який із цих параметрів, відкриється діалогове вікно Редагувати іскрові лінії, де можна змінити діапазон даних.

Обробка прихованих та порожніх клітин

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

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

Ось приклад, коли в іскровій лінії стовпця немає точки даних.

Ви можете вказати, як потрібно обробляти ці порожні клітинки.

Ось кроки:

1. Клацніть клітинку з іскровою лінією.
2. Натисніть вкладку «Дизайн» (контекстна вкладка, яка стає доступною лише тоді, коли ви вибираєте клітинку з іскровою лінією).
3. Натисніть на опцію Редагувати дані (натисніть на текстову частину, а не на її піктограму).
4. У спадному меню виберіть опцію «Приховані та порожні клітинки».
5. У діалоговому вікні, що відкриється, виберіть, чи потрібно відображати
   • Порожні клітини як прогалини
   • Порожні клітинки як нуль
   • З’єднайте точки даних до і після з лінією [ця опція доступна лише для лінійних іскрових ліній].

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

Нижче наведено приклад усіх трьох варіантів іскрової лінії:

1. Клітинка G2 - це те, що відбувається, коли ви вирішуєте показати розрив у іскровій лінії.
2. Комірка G3 - це те, що відбувається, коли ви вирішуєте замість цього показувати нуль.
3. Комірка G2 - це те, що відбувається, коли ви вирішуєте показувати безперервну лінію, з'єднуючи точки даних.

Ви можете зробити те ж саме з іскровими лініями стовпців та виграшу (не для параметра точки з'єднання даних).

Зміна типу Sparkline

Якщо ви хочете швидко змінити тип іскрової лінії - з рядка в стовпець або навпаки, ви можете зробити це, виконавши такі дії:

• Натисніть на іскрову лінію, яку потрібно змінити.
• Перейдіть на вкладку Дизайн інструментів Sparkline.
• У групі Тип виберіть потрібну іскрову лінію.

Виділення точок даних у Sparklines

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

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

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

Ці параметри доступні на вкладці Інструменти Sparkline (у групі показу).

Тут доступні різні варіанти:

1. Висока/Нижня точка: Ви можете використовувати будь -який з них або обидва з них, щоб виділити максимальну та/або мінімальну точку даних.
2. Перший/Останній пункт: Ви можете використовувати будь -який з них або обидва з них, щоб виділити першу та/або останню точку даних.
3. Негативні бали: Якщо у вас є негативні точки даних, ви можете скористатися цією опцією, щоб виділити всі ці дані одночасно.
4. Маркери: Ця опція доступна лише для лінійних іскрових ліній. Він виділить усі точки даних маркером. Ви можете змінити колір маркера за допомогою опції "Колір маркера".

Колір і стиль Sparklines

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

Він дозволяє змінювати колір іскрової лінії (рядків або стовпців), а також маркери.

Ви також можете використовувати готові варіанти стилів. Щоб отримати повний список опцій. клацніть по спадному значку в нижньому правому куті вікна стилю.

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

Додавання осі

Коли ви створюєте іскрову лінію, у її стандартній формі вона показує найнижчу точку даних унизу, а всі інші точки даних відносно неї.

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

У наведеному нижче прикладі відхилення становить лише 5 пунктів (де весь набір даних становить від 95 до 100). Але оскільки вісь починається з найнижчої точки (а це 95), зміна виглядає величезною.

Ця відмінність набагато більш помітна в іскровій лінії колонки:

У іскровій лінії стовпця вище це може виглядати так, ніби значення Jan близьке до 0.

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

Ось як це зробити:

1. Виділіть клітинку з іскровими лініями.
2. Натисніть на вкладку Дизайн інструментів Sparkline.
3. Натисніть на опцію Ось.
4. У спадному меню виберіть Спеціальне значення (у параметрах мінімального значення вертикальної осі).
5. У діалоговому вікні Налаштування вертикальної осі Sparkline введіть значення як 0.
6. Натисніть OK.

Це дасть вам результат, як показано нижче.

Встановивши митне значення на 0, ми змусили іскрову лінію почати з 0. Це дає справжнє уявлення про варіацію.

Примітка: Якщо у вашому наборі даних є негативні числа, краще не встановлювати вісь. Наприклад, якщо встановити вісь на 0, від’ємні числа не відображатимуться у іскровій лінії (оскільки вона починається з 0).

Ви також можете зробити вісь видимою, вибравши опцію Показати вісь. Це корисно лише тоді, коли у вас є числа, що перетинають вісь. Наприклад, якщо у вас вісь встановлена ​​на 0 і у вас є як позитивні, так і негативні числа, то вісь буде видно.

Групові та розгруповані Sparklines

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

Щоб згрупувати Sparklines:

1. Виберіть ті, які потрібно згрупувати.
2. Натисніть на вкладку Дизайн інструментів Sparklines.
3. Натисніть на значок групи.

Тепер, коли ви вибираєте будь -який з згрупованих Sparkline, він автоматично вибирає всі з них у цій групі.

Ви можете розгрупувати ці іскрові лінії за допомогою опції Розгрупувати.

Видалення Sparklines

Ви не можете видалити іскрову лінію, вибравши клітинку та натиснувши клавішу видалення.

Щоб видалити іскрову лінію, виконайте наведені нижче дії.

1. Виберіть клітинку з іскровою лінією, яку потрібно видалити.
2. Перейдіть на вкладку Дизайн інструментів Sparkline.
3. Натисніть опцію Очистити.