Сьогодні вранці Філ написав запитання про створення діаграми в Excel.
Чи є спосіб взяти дві колонки, що містять дати початку та зупинки для окремих подій, і створити діаграму типу Ганта, не виходячи з Excel?
Цю тему було висвітлено в підказці Створити діаграму шкали часу. У тій підказці від літа 2001 року згадувалося, що ви також можете створити діаграму типу Ганта на робочому аркуші, використовуючи умовне форматування. Цей тип діаграми вирішить питання Філа.
Я думаю, що дані Філа виглядають приблизно так, як таблиця зліва. Існує подія, потім дати початку у стовпці B та дати закінчення у стовпці C. Я використовую роки для свого прикладу, але ви можете легко використовувати звичайні дати Excel.
Наступний крок можна легко включити в макрос, але справжній фокус цієї техніки полягає у встановленні умовного форматування. Я просканував свої дані і помітив, що дати коливаються з 1901 по 1919 рік. Починаючи з стовпця D, я вводив перший рік 1901 року. У E1 я ввів 1902 рік. Потім ви можете вибрати D1: E1, клацнути на ручці заповнення в нижній правий кут виділення за допомогою миші та перетягніть до стовпця W, щоб заповнити всі роки з 1901 по 1920 рік.
Для того, щоб роки зайняли менше місця, виберіть D1: W1, а потім за допомогою Формат - Клітинки - Вирівнювання виберіть параметр вертикального тексту. Потім виберіть Формат - Стовпець - Автоширина, і ви зможете побачити всі 23 стовпці на екрані.
Виберіть верхню ліву клітинку області діаграми Ганта або D2 у цьому прикладі. У меню виберіть Формат - Умовне форматування. Спочатку діалогове вікно має спадне меню з лівого боку, яке за замовчуванням має значення «Cell Value Is». Змініть це випадаюче меню на "Формула є", і права частина діалогового вікна зміниться на велике текстове поле для введення формули.
Мета полягає в тому, щоб ввести формулу, яка перевіряє, чи не потрапляє рік у рядку 1 над цією клітинкою в діапазон років у стовпцях B & C цього рядка. Важливо використовувати правильну комбінацію відносної та абсолютної адрес, щоб формула, яку ми вводимо в D2, була скопійована у всі комірки в діапазоні.
Існуватиме дві умови для перевірки, і обидві повинні бути істинними. Це означає, що ми почнемо з =AND()функції.
Перша умова перевірить, чи рік у рядку 1 більший чи рівний році в стовпці B. Оскільки я завжди хочу, щоб ця формула посилалася на рядок 1, перша частина формули D $ 1> = $ B2 . Зверніть увагу, що знак долара перед знаком 1 у D $ 1 гарантує, що наша формула завжди вказує на рядок 1, а знак долара перед значком B у $ B2 гарантує, що він завжди порівнюється зі стовпчиком B.
Друга умова перевірить, чи рік у рядку 1 менше або дорівнює даті у стовпці C. Нам все одно потрібно використовувати ту саму відносну та абсолютну адресацію, тож це буде D $ 1 <= $ C2
Нам потрібно поєднати обидві ці умови, використовуючи функцію AND (). Це було б=AND(D$1>=$B2,D$1<=$C2)
Введіть цю формулу у полі формул діалогового вікна «Звичайне форматування». Переконайтеся, що починаєте зі знака рівності, інакше умовне форматування не буде працювати.
Далі виберіть яскравий колір, який використовуватиметься, коли умова відповідає дійсності. Натисніть кнопку Формат … На вкладці Візерунки виберіть колір. Натисніть кнопку OK, щоб закрити діалогове вікно Форматування комірок, і у вас залишиться діалогове вікно умовного форматування, яке виглядає як це
Натисніть OK, щоб закрити поле Умовне форматування. Якщо ваша горішня ліва клітина в D2 через рік випадково впаде, ця клітина пожовкне.
Незалежно від того, стала комірка жовтою чи ні, натисніть D2 і скористайтеся комбінацією клавіш Ctrl + C або Змінити - Копіювати, щоб скопіювати цю клітинку.
Виділіть D2: W6 і в меню виберіть Редагувати - Вставити Спеціальні - Формати - ОК. Умовний формат буде скопійовано до всього діапазону діаграми Ганта, і ви отримаєте діаграму, схожу на цю.
Умовне форматування - чудовий інструмент, який дозволяє легко створювати діаграми типу Ганта прямо на робочому аркуші. Майте на увазі, що для будь-якої клітини ви обмежуєтесь лише трьома умовами. Ви можете експериментувати з різними комбінаціями умов. Для того, щоб створити межі навколо кожного стовпчика на діаграмі Ганта, я використав три умови, як показано нижче, і використовував різні межі для кожної умови.
