Як побудувати графік в excel 3 (факторний аналіз)

У попередніх статтях ми розглянули основи побудови графіків і діаграм в Excel (див рубрику Діаграми і Графіки). Сьогодні ми ускладнити завдання і спробуємо провести факторний аналіз в Excel (спрощена звичайно). Припустимо у нас є точка продажів будь-яких товарів, наприклад мобільних телефонів і продажу від тижня до тижня можуть то рости то падати. Звичайно, загальну динаміку продажів ми побачимо якщо побудуємо графік по кількості проданих одиниць, але цей графік не дасть нам уявлення про те, які моделі або бренди втрачають популярність, а які ні. Для того щоб наочно побачити який з брендів «просів» у продажах нам і допоможе факторний аналіз в Excel (в нашому прикладі побудова гістограми за певними умовами).

Отже, у нас є дані про продажі за 4 тижні:

Дані до графіку

Ми ходимо зрозуміти за рахунок яких телефонів стався основне зростання за підсумками другого тижня. Уявімо дані дещо в іншому вигляді:

перетворення Даних

Якщо відбулося зростання в порівнянні з минулим тижнем по окремому бренду, то позитивну дельту ми запишемо в стовпчик «Рост», а негативну в «Зниження». Наприклад в осередку К4 у нас буде прописана формула = ЕСЛИ ((C3- B3) Gt; 0-C3-B3-0) а в клітинку L4 = ЕСЛИ (K4= 0-B3-C3-0) . (Можна прописати Зростання і Зниження через інші функції - в прикладі те, що перше спало на думку). У стовпці J вказана сума попереднього значення плюс попереднє зростання без поточного зниження =J3+K3-L4.

Тепер розрахуємо внесок кожного з брендів (% зростання) = ЕСЛИ (K4/$ J $ 11= 0--L4/$ J $ 11-K4/$ J $ 11) :

Дані для побудови гістограми

тепер залишилося тільки виділити всю область для побудови діаграми (в підпис даних потрібно включити і столец «% зростання» і «чинники») Можна виділити весь діапазон H1: L10 потім перейядя на вкладку «Вставка» вибрати «Гістограма» (докладніше дивись в статті «Як побудувати графік в Excel»):

отриманий графік

Попрацюємо з підписами даних і квітами (приберемо накопичувальний підсумок залишивши тільки Зростання і Зниження):

Факторний аналіз в Excel

Тепер ми наочно бачимо хто дає основний внесок в зростання продажів.

При бажанні можна зробити графік «динамічним». Наприклад, зробити спливаючий список з тижнів (1ая, 2ая ...), а в формули стовпчика Роста (Зниження і інших стобцов) включити формулу ВВР, яка в залежності від зазначеної тижні буде підтягувати в таблицю для факторного аналізу відповідні дані з основної таблиці і графік буде змінюватися!

завантажити grafik

Дуже сподіваємося, що наша стаття допомогла Вам у вирішенні Вашої проблеми. Будемо вдячні, якщо Ви натиснете +1 і / або Мені подобається внизу даної статті або поділіться з друзями за допомогою кнопок розташованих нижче.

Дякуємо за увагу.