Інтерактивні елементи в excel з використанням лічильника і смуги прокрутки

Helen Bradley пояснює, як додавати інтерактивні елементи на лист Excel, використовуючи Смугу прокрутки і Лічильник.

Всякий раз, коли користувач вибирає з дискретного набору варіантів, які дані ввести на лист Excel, Ви можете заощадити час, автоматизувавши процес введення. Це можна зробити різними способами, і один з них - використовувати інтерактивні елементи Spin Button (Лічильник) або Scroll Bar (Смуга прокрутки). Сьогодні я познайомлю Вас з використанням Лічильника і Смуги прокрутки. а також покажу, як додати до графіка в Excel інтерактивний елемент Лічильник.

Відкриваємо вкладку Розробник

Щоб побачити інструменти, перейдіть на вкладку Developer> Insert (Розробник> Вставити) і виберіть елемент Spin Button (Лічильник) або Scroll Bar (Смуга прокрутки) з групи Form Controls (Елементи управління форми). Вкрай важливо вибирати саме з цієї групи, а не з ActiveX Controls (Елементи ActiveX), так як вони працюють абсолютно по-різному.

Перетягніть на робочий лист Excel Лічильник і Смугу прокрутки. Зауважте, що Ви можете змінювати положення цих елементів, а також повертати їх вертикально або горизонтально. Щоб пересунути або змінити розмір елемента керування, клацніть по ньому правою кнопкою миші, а потім змінюйте розмір або перетягніть.

Щоб побачити, як все це працює, клацніть правою кнопкою миші по об'єкту і виберіть пункт Format Control (Формат об'єкта). У діалоговому вікні на вкладці Control (Елемент управління) знаходяться параметри для настройки Лічильника. За своєю суттю, Лічильник поміщає в клітинку якесь значення, а Ви, натискаючи стрілки, збільшуєте або зменшуєте його.

Елемент Лічильник має обмеження: значення має бути цілим числом від 0 до 30000, параметр Incremental Change (Крок зміни) також повинен бути будь-яким цілим числом від 1 до 30000.

Встановіть Current Value (Поточне значення) рівним 50. Minimum Value (Мінімальне значення) рівним 0. Maximum Value (Максимальне значення) рівне 300 і Incremental Change (Крок зміни) рівним 10. Клацніть по полю Cell Link (Зв'язок з осередком), потім виділіть осередок A1 і закрийте діалогове вікно.

Робимо Лічильник кориснішим

Ви можете використовувати Лічильник таким чином, щоб користувач міг вводити значення на аркуші простим натисканням кнопки, замість введення вручну з клавіатури. Ймовірно, у Вас виникає питання: як бути, якщо значення, які повинен ввести користувач не цілі числа в діапазоні від 0 до 30000?

Рішення є - використовуйте проміжну осередок для обчислення потрібного Вам значення. Наприклад, якщо Ви хочете, щоб користувач вводив значення між 0% і 5% з кроком 0,1%. потрібно масштабувати значення, яке дає лічильник, щоб отримати результат від 0 до 0,05 з кроком 0,001.

Є безліч варіантів, як це можна реалізувати математично і, якщо Ваше рішення працює, то не має значення, як Ви це зробили. Ось одне з можливих рішень: клікніть по лічильнику правою кнопкою миші, виберіть Format Control (Формат об'єкта) і встановіть Minimum Value (Мінімальне значення) = 0, Maximum Value (Максимальне значення) = 500 і Incremental Change (Крок зміни) = 10. Встановіть зв'язок з осередком A1. Далі в осередку A2 запишіть формулу = A1 / 10000 і застосуєте до неї процентний числовий формат з одним десятковим знаком.

Тепер, натискаючи на кнопки лічильника, Ви отримаєте в осередку A2 саме той результат, який необхідний - значення відсотка між 0% і 5% з кроком 0,1%. Значення в осередку A1 створено лічильником, але нас більше цікавить значення в осередку A2.

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

Як працює Смуга прокрутки

Смуга прокрутки працює таким же чином, як і Лічильник. Крім цього, для Смуги прокрутки Ви можете налаштувати параметр Page Change (Крок зміни за сторінками), який визначає на скільки змінюється значення, коли Ви клікаєте по смузі прокрутки в стороні від її повзунка. Параметр Incremental Change (Крок зміни) використовується при натисканні стрілок по краях Смуги прокрутки. Звичайно ж потрібно налаштувати зв'язок смуги прокрутки з осередком, в яку повинен бути поміщений результат. Якщо потрібно масштабувати значення, Вам потрібна додаткова осередок з формулою, яка буде змінювати значення, отримане від Смуги прокрутки. щоб отримати правильний кінцевий результат.

Уявіть організацію, яка надає кредити від $ 20 000 до $ 5 000 000 з кроком зміни суми $ 10 000. Ви можете використовувати Смугу прокрутки для введення суми позики. У цьому прикладі я встановив зв'язок з осередком E2. а в C2 ввів формулу = E2 * 10000 - цей осередок показує бажану суму позики.

Смуга прокрутки матиме параметри: Minimum Value (Мінімальне значення) = 2, Maximum Value (Максимальне значення) = 500, Incremental Change (Крок зміни) = 1 і Page Change (Крок зміни за сторінками) = 10. Incremental Change (Крок зміни) має дорівнювати 1, щоб дати можливість користувачу точно налаштувати значення кратне $ 10 000. Якщо Ви хочете створити зручне рішення, то дуже важливо, щоб користувач мав можливість легко отримати потрібний йому результат. Якщо Ви встановите Крок зміни рівним, наприклад, 5. користувач зможе змінювати суму позики кратно $ 50 000, а це занадто велике число.

Параметр Page Change (Крок зміни за сторінками) дозволяє користувачеві змінювати суму позики з кроком $ 100 000, так він зможе швидше наблизитися до суми, яка його цікавить. Повзунок смуги прокрутки не налаштовується ні якими параметрами, так що користувач здатний миттєво перейти від $ 20 000 до $ 5 000 000 просто перетягнувши повзунок від одного кінця смуги прокрутки до іншого.

Інтерактивний графік з лічильником

В осередку C2 знаходиться така формула:

Ця формула скопійована в інші комірки стовпчика C. До осередкам C2: C19 застосовано умовне форматування, яке приховує будь-які повідомлення про помилки, тому що формули в цих осередках покажуть безліч повідомлень про помилку # N / A (# Н / Д). Ми могли б уникнути появи помилки в стовпці C. написавши формулу ось так:

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

Щоб приховати помилки, виділіть осередки в стовпці C і натисніть Conditional Formatting> New Rule (Умовне форматування> Створити правило), виберіть тип правила Format Only Cells That Contain (Форматувати тільки осередки, які містять), в першому випадаючому списку виберіть Errors (Помилки) і далі в налаштуваннях формату встановіть білий колір шрифту, щоб він зливався з фоном осередків - це найефективніший спосіб приховати помилки!

У клітинці G1 знаходиться ось така формула = 40000 + G3. а осередок G3 ми зробимо пов'язаної зі Лічильником. Встановимо ось такі параметри: Current Value (Поточне значення) = 695, Minimum Value (Мінімальне значення) = 695, Maximum Value (Максимальне значення) = 814, Incremental Change (Крок зміни) = 7 і Cell Link (Зв'язок з осередком) = G3 . Протестуйте Лічильник. натискаючи на його стрілки, Ви повинні бачити в стовпці C значення, відповідне датою в клітинці G1.

додаємо графік

Графік створюємо з діапазону даних A1: C19. вибираємо тип Column chart (Гістограма). Розмір графіка зробіть таким, щоб він закривав собою стовпець C. але, щоб було видно перший рядок листа Excel.

Щоб налаштувати вигляд графіка, необхідно натиснути правою кнопкою миші по одиночному колонки, що показує значення для Series 2 (Ряд 2), вибрати Change Series Chart Type (Змінити тип діаграми для ряду), а потім Line Chart With Markers (Графік з маркерами). Далі натискаємо правою кнопкою миші по маркеру, вибираємо Format Data Series (Формат ряду даних) і налаштовуємо симпатичний вигляд маркера. Ще раз клацаємо правою кнопкою миші по маркеру і вибираємо Add Data Labels (Додати підписи даних), потім за Легендою, щоб виділити її і видалити.

Тепер, коли Ви натискаєте кнопки Лічильника. маркер стрибає по графіку, виділяючи показник продажів на ту дату, яка з'являється в клітинці G1. а поруч з маркером відображається ярличок з сумою продажів.

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