Підкресліть головні значення в діаграмах для інформаційних панелей Excel

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

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

Секрет цієї техніки полягає в незрозумілій функції ВЕЛИКИЙ Excel. Функція LARGE повертає n- е найбільше число з набору даних. Іншими словами, ви вказуєте йому, де шукати, і який ранг ви хочете.

Щоб знайти найбільше число в наборі даних, введіть формулу LARGE (Діапазон_даних, 1). Щоб знайти п’яте за величиною число в наборі даних, використовуйте LARGE(Data_Range, 5). Цей малюнок ілюструє, як працює функція LARGE.

Ідея досить проста. Щоб визначити п’ять головних значень у наборі даних, вам спочатку потрібно визначити п’яте за величиною число (на порятунок функція ВЕЛИКИЙ), а потім перевірити кожне значення в наборі даних, щоб перевірити, чи воно більше, ніж п’яте за величиною число. Ось що ви робите:

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

У першому рядку фідера діаграми введіть формули, показані на попередньому малюнку.

Формула для першого стовпця (F4) перевіряє, чи значення в клітинці C4 менше за число, яке повертає формула ВЕЛИКИЙ (п’яте за величиною значення). Якщо це так, повертається значення в клітинці C4. В іншому випадку використовується NA.

Формула для другого стовпця працює так само, за винятком того, що оператор IF змінюється на протилежне: якщо значення в комірці C4 більше або дорівнює числу, повернутому формулою, то повертається значення; в іншому випадку використовується NA.

Скопіюйте формули вниз, щоб заповнити таблицю.

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

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

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

Клацніть правою кнопкою миші будь-яку з міток даних для серії «не в топ-5» і виберіть «Форматувати мітки даних».

З’явиться діалогове вікно Формат міток даних.

У цьому діалоговому вікні розгорніть розділ «Числа» та виберіть «Власний» у списку «Категорія».

Введіть #,##0;; як користувацький числовий формат, як показано на цьому малюнку.

Натисніть кнопку Додати, а потім натисніть кнопку Закрити.

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

Ви можете застосувати ту саму техніку, щоб виділити п’ять нижніх значень у вашому наборі даних. Єдина відмінність полягає в тому, що замість використання функції ВЕЛИКИЙ, ви використовуєте функцію SMALL. Тоді як функція LARGE повертає найбільше n- е значення з діапазону, функція SMALL повертає найменше n- е значення.

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

Формула для першого стовпця (F22) перевіряє, чи значення в комірці C22 більше за число, яке повертає формула SMALL (п’яте найменше значення). Якщо це так, повертається значення в клітинці C22. В іншому випадку використовується NA.

Формула для другого стовпця працює так само, за винятком того, що оператор IF змінюється на протилежне: якщо значення в комірці C22 більше за число, яке повертає формула SMALL, тоді використовується NA; інакше повертається значення.