Переміщення по осередках в Excel стрілками

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

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

Переміщення стрілками в Excel

Заповніть рядки даними, тому що показано на цьому малюнку:

Завдання у цьому уроці – не стандартні, а й не складні, хоча дуже корисні.

Примітка! При записі комбінацій гарячих клавіш знак "+" означає, що клавіші слід натискати одночасно, а слово "потім" означає послідовне натискання.

Перейдіть на комірку А1. Найкращий спосіб це зробити, натиснути на комбінацію гарячих клавіш CTRL+HOME. Натисніть клавішу END потім «стрілка вправо». У попередніх уроках, коли листи були ще порожніми, ця комбінація просто зміщувала курсор на останній стовпець листа. Якщо в даному рядку є запис, то ця комбінація дозволяє миттєво знайти кінець заповненого уривка рядка. Але рядок може бути розбитий на кілька відрізків записів. Тоді ця комбінація переміщає курсор по кінцях відрізків рядка. Для наочного прикладу зверніть увагу на малюнок:

Клавіша END потім "стрілка вліво" виконує вище описане переміщення курсору у зворотному порядку - відповідно. Щоразу, коли ми натискаємо клавішу END, у рядку стану вікна програми висвічується повідомлення: "Режим переходу на кінець".

Всі вище описані дії для переміщення по осередках в Excel стрілками так само виконує натискання на клавіатурі комп'ютера CTRL + "стрілка вправо (стрілка вліво, вгору та вниз)"

Виберіть будь-яку комірку на аркуші та натисніть комбінацію клавіш CTRL+END. В результаті курсор переміститься на перехрестя останнього запису в рядку і останнього запису в стовпці. Зверніть увагу на рисунок.

Примітка. Якщо видалити вміст кількох осередків, щоб змінити адресу перехрестя після натискання CTRL+END. Але після видалення необхідно обов'язково зберегти файл. Інакше CTRL+END повертатиме курсор за старою адресою.

Таблиця дій для переміщення курсором Excel:

Як зробити стрілки в осередках Excel та інші значки оцінок значень

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

Набір піктограм в Excel

Для того, щоб зробити стрілки в осередках Excel будемо використовувати умовне форматування та набір значків. Спочатку підготуємо вихідні дані в стовпці E та введемо формули для обчислення різниці у прибутку для кожного магазину = D2-C2. Як показано нижче на малюнку:

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

Виділіть діапазон осередків E2:E13 та виберіть інструмент: «ГОЛОВНА»-«Стилі»-«Умовне форматування»-«Набори значків». Із галереї «Напрямки» виберіть першу групу «3 кольорові стрілки».

В результаті в комірки вставилися кольорові стрілки, кольори та напрямки яких відповідають числовим значенням для цих осередків:

• червоні стрілки донизу – негативні числа;
• зелені нагору – позитивні;
• жовті убік – середні значення.

Так як нас не цікавлять самі числові значення, а лише напрямок зміни тренду, тому модифікуємо правило:

1. Виділіть діапазон E2:E13 та оберіть інструмент: «ГОЛОВНА»-«Стилі»-«Умовне форматування»-«Управління правилами»
2. У вікні «Диспетчер правил умовного форматування» виберіть правило «Набір значків» і натисніть кнопку «Змінити правило».
3. З'явиться вікно "Зміна правила форматування", в якому вже вибрано опцію "Форматувати всі комірки на підставі їх значень". А у списку «Стиль формату:» вже вибрано значення «Набори значків». Поставте галочку проти опції «Показувати тільки стовпець».
4. Змініть параметри, щоб відобразити жовті стрілки. У двох списках групи «Тип» виберіть опцію число. У першому полі введення групи "Значення" вкажіть число 10000, а у другому (нижче) - 5000. І натисніть кнопку ОК на всіх відкритих вікнах.

Тепер жовта стрілка бокового (флет) тренду вказуватиме на числові значення в межах між від 5000 до 10000 для різниці в річний прибуток для кожного магазину.

Набори піктограм в умовному форматуванні

У галереї значків є й складніші у налаштуваннях, але більш інформативні у відображенні групи значків. Щоб продемонструвати нам необхідно використовувати нове правило, але спочатку видалимо старе. Для цього оберіть інструмент: «ГОЛОВНА»-«Стилі»-«Умовне форматування»-«Видалити правила»-«Видалити правила з усього аркуша».

Тепер виберіть інструмент: «ГОЛОВНА»-«Стилі»-«Умовне форматування»-«Набори значків»-«Напрямки»-«5 кольорових стрілок». І при необхідності приховайте значення осередків у налаштуваннях правила, як було описано вище.

В результаті ми бачимо 3 тенденції для бокового (флет) тренду:

1. Швидше вгору, ніж у бік.
2. Чистий флет-тренд.
3. Швидше вниз, ніж у бік.

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

Галерея «Набори значків» в Excel пропонує користувачеві на вибір кілька груп:

• стрілки в осередках для вказівки напрямку кожного значення по відношенню до інших осередків;
• оцінки в осередку Excel;
• фігури в осередках;
• індикатори.

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

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади

Стрілки в осередках

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

Спосіб 1. Користувальницький формат зі спецсимволами

Припустимо, що у нас є ось така таблиця з вихідними даними, які нам треба візуалізувати: Для розрахунку відсотків динаміки в осередку D4 використовується проста формула, яка обчислює різницю між цінами цього та минулого року та процентний формат осередків. Щоб додати до осередків ошатні стрілочки, робимо таке:
Виділіть будь-яку порожню комірку та введіть у неї символи "трикутник вгору" і "трикутник вниз", використовуючи команду Вставити - Символ (Insert - Symbol) : Краще використовувати стандартні шрифти (Arial, Tahoma, Verdana), які є на будь-якому комп'ютері. Закрийте вікно вставки, виділіть обидва введені символи (у рядку формул, а не комірку з ними!) і скопіюйте в Буфер (Ctrl+C). Виділіть комірки з відсотками (D4:D10) та відкрийте вікно Формат осередку (можна використовувати Ctrl+1). На вкладці Число (Number) виберіть у списку формат Усі формати (Custom) та вставте скопійовані символи в рядок Тип, а потім допишіть до них вручну з клавіатури нулі та відсотки, щоб вийшло наступне: Принцип простий: якщо в комірці буде позитивне число, то до неї застосовуватиметься перший формат користувача, якщо негативне - другий. Між форматами стоїть обов'язковий роздільник – крапка з комою. І в жодному разі не вставляйте жодних прогалин "для краси". Після натискання на ОК наша таблиця буде виглядати майже як задумано: Додати червоний та зелений колір до осередків можна двома способами. Перший – повернутися у вікно Формат осередків і дописати потрібні кольори в квадратних дужках прямо в нашому форматі користувача: Особисто мені не дуже подобається виходить в цьому випадку отруйний зелений, тому я віддаю перевагу другому варіанту - додати колір за допомогою умовного форматування. Для цього виділіть комірки з відсотками та виберіть на вкладці Головна - Умовне форматування - Правила виділення осередків - Більше (Home - Conditional formatting - Highlight Cell Rules - Greater Then) , введіть як порогове значення 0 і задайте бажаний колір: Потім повторіть ці дії для негативних значень, вибравши Менше (Less Then) , щоб відформатувати їх зеленим. Якщо трохи поекспериментувати з символами, можна знайти багато аналогічних варіантів реалізації подібного трюка з іншими символами:

Спосіб 2. Умовне форматування

Виділіть комірки з відсотками та відкрийте на вкладці Головна - Умовне форматування - Набори значків - Інші правила (Home - Conditional formatting - Icon Sets - More) . У вікні виберіть потрібні значки зі списків, що випадають, і задайте обмеження для підстановки кожного з них як на малюнку: Після натискання на ОК отримаємо результат: Плюси цього у його відносній простоті і пристойному наборі різних вбудованих значків, які можна використовувати: Мінус ж у цьому, що у цьому наборі немає, наприклад, червоної стрілки вгору і зеленої вниз, тобто. для зростання прибутку ці значки використовувати буде логічно, а ось для зростання цін – вже не дуже. Але, у будь-якому випадку, цей спосіб теж заслуговує, щоб ви його знали ;)

Посилання по темі

Миколо, все просто і наочно. Дякую! Метод з форматами осередків сподобався більше т.к. його 1 раз прописав, а потім просто застосовуй на потрібні осередки і нічого не треба налаштовувати.
P.S. Пробіл "для краси" між стрілкою і значенням можна поставити, але тільки 1 (більше не ставиться:)).

А чи є спосіб останній варіант з персональним умовним форматуванням зберегти окремою кнопкою на швидкій панелі?

Тільки якщо записати макрос та повісити на кнопку.
Або просто копіювати формат "пензликом" із вже зроблених осередків на нові.

Привіт!
По-перше, дякую за перший спосіб - дуже пізнавально

По-друге, часто скористався варіантом №2 і стикався з проблемою, що до наборів значків неможливо задати умову формулою, що містить відносні посилання. Чи можна цей якось обійти чи виправити?

Доброго дня перший спосіб дуже цікавий, а колір можна вибрати будь-який з 56, наприклад [Колір3]▲0%;[Колір10]▼0%
Тут є палітра.

Selection.NumberFormat = " " & ChrW(8593) & " 0%;" & ChrW(8595) & "-0%;0%"

Selection.NumberFormat = " " & ChrW(9650) & " 0%;" & ChrW(9660) & "-0%;0%"