Припустимо, у нас є побудована зведена таблиця з результатами аналізу продажів по місяцях для різних міст (при необхідності прочитайте цю статтю, щоб зрозуміти, як їх створити в загальному або освіжити пам'ять):
Ми хочемо трохи змінити його зовнішній вигляд, щоб він чіткіше відображав потрібні вам дані, а не просто викидав на екран купу цифр. Що для цього можна зробити?
Інші функції розрахунку замість банальної суми
Якщо клацнути правою кнопкою миші на обчислюваному полі в області даних і вибрати команду з контекстного меню Параметри поля (Налаштування поля) або у версії Excel 2007 – Параметри поля значення (Параметри поля значення), то відкриється дуже корисне вікно, за допомогою якого можна встановити купу цікавих налаштувань:
Зокрема, ви можете легко змінити функцію обчислення поля на середнє, мінімум, максимум тощо. Наприклад, якщо ми змінимо суму на кількість у нашій зведеній таблиці, то побачимо не загальний дохід, а кількість транзакцій для кожного товару:
За замовчуванням Excel завжди автоматично вибирає підсумовування для числових даних. (сума), а для нечислових (навіть якщо з тисячі клітинок з числами є хоча б одна порожня або з текстом, або з числом у текстовому форматі) – функція для підрахунку кількості значень (Рахувати).
Якщо ви хочете бачити в одній зведеній таблиці відразу і середнє, і суму, і кількість, тобто кілька розрахункових функцій для одного поля, то сміливо кидайте мишею в область даних потрібного вам поля кілька разів. підряд, щоб отримати щось подібне:
…а потім установіть різні функції для кожного з полів, клацаючи їх по черзі мишею та вибираючи команду Параметри поля (Налаштування поля)щоб отримати те, що ви хочете:
Якщо в тому ж вікні Параметри поля натисніть кнопку Додатково (Параметри) або перейдіть на вкладку Додаткові розрахунки (в Excel 2007-2010), після чого стане доступним розкривний список Додаткові розрахунки (Показати дані як):
У цьому списку, наприклад, можна вибрати параметри Відсоток від суми рядка (% від рядка), Відсоток від загальної кількості за стовпцем (% стовпця) or Частка від заг (% від загальної кількості)для автоматичного розрахунку відсотків для кожного продукту чи міста. Ось так, наприклад, буде виглядати наша зведена таблиця з увімкненою функцією Відсоток від загальної кількості за стовпцем:
Динаміка продажів
Якщо в спадному списку Додаткові розрахунки (Показати дані як) вибрати варіант відмінність (різниця), а в нижніх вікнах Поле (Базове поле) и Елемент (Базовий предмет) вибрати місяць и назад (в рідному англійському варіанті замість цього дивного слова було більш зрозуміле Попередній, ті. Попередній):
…тоді отримуємо зведену таблицю, яка показує відмінності продажів кожного наступного місяця від попереднього, тобто – динаміку продажів:
А якщо замінити відмінність (різниця) on Дана різниця (% різниці) і додати умовне форматування для виділення негативних значень червоним кольором отримуємо те ж саме, але не в рублях, а у відсотках:
PS
У Microsoft Excel 2010 усі перераховані вище параметри обчислень можна виконати ще простіше – клацнувши правою кнопкою миші на будь-якому полі та вибравши команди з контекстного меню Усього для (Узагальнити значення за):
… І Додаткові розрахунки (Показати дані як):
Також у версії Excel 2010 до цього набору було додано кілька нових функцій:
- % від загального за батьківським рядком (стовпцем) – дозволяє обчислити частку відносно проміжного підсумку для рядка або стовпця:
У попередніх версіях ви могли обчислити лише частку відносно загальної суми.
- % від суми наростаючим підсумком – працює подібно до функції кумулятивної суми, але відображає результат у вигляді частки, тобто у відсотках. Зручно порахувати, наприклад, відсоток виконання плану або бюджету:
- Сортування від найменшого до найбільшого і навпаки – трохи дивна назва функції ранжирування (RANK), яка обчислює порядковий номер (позицію) елемента в загальному списку значень. Наприклад, з його допомогою зручно ранжувати менеджерів за сумарним доходом, визначаючи, хто на якому місці в загальному заліку:
- Що таке зведені таблиці і як їх будувати
- Групування чисел і дат із потрібним кроком у зведених таблицях
- Створення звіту зведеної таблиці на основі кількох діапазонів вихідних даних