Excel: обчислення у зведених таблицях

Важливо! За умовчанням для чисел Excel вибирає операцію підсумовування. Для нечислових даних він застосовує функцію підрахунку кількості значень. Це відбудеться навіть у тому випадку, якщо у вихідних даних знайдеться хоча б одна порожня комірка або число в текстовому форматі.

Факти продажів — річ уперта…

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

1) ставимо активну комірку на будь-яке число в області даних зведеного звіту. Наприклад, на суму «

150» (комірка «B4»);

2) клацаємо правою кнопкою миші. З’явиться контекстне меню для управління зведеною таблицею (рис. 5);

3) із цього меню вибираємо «Параметры поля…» і відразу потрапляємо до вікна «Вычисление поля сводной таблицы» (рис. 3);

4) зі списку доступних операцій вибираємо «Количество» і натискуємо на «ОК». Фрагмент нового звіту показано на рис. 6.

Тепер видно, що з контрагентом «

Так само просто ми могли б показати у зведеному звіті середнє, мінімум, максимум тощо. Усього Excel пропонує 11 варіантів. Для цього достатньо лише змінити тип операції та оновити зведену таблицю. Але це ще не все.

Частка, динаміка і наведені відмінності

Абсолютні значення показників не завжди відображають реальну картину. У багатьох випадках краще аналізувати відсоткові зміни, тенденції, прирости показників тощо. Такі розрахунки широко пропонуються у зведених таблицях Excel. Отримати до них доступ можна через кнопку «

Дополнительно» у вікні «Вычисление поля сводной таблицы». Подивимося, як цим скористатися. За основу візьмемо зведений звіт, зображений на рис. 6. Спочатку змінимо його структуру. Ми додамо до звіту поле «Сумма» тричі. Потім для кожного примірника цього поля виберемо різні типи операцій та подивимося, що в нас вийде.

Ставимо активну комірку на область зведеної таблиці. Зараз ми могли б змінити склад полів у звіті через Майстра зведених таблиць. Наприклад, так: клацнути правою кнопкою миші всередині зведеного звіту, вибрати з контекстного меню пункт «

1) ставимо активну комірку всередину зведеного звіту. З’являється спеціальна панель роботи зі зведеною таблицею (рис. 7). На цій панелі можна побачити всі доступні поля;

2) утримуючи натисненою ліву кнопку миші, тричі перетягуємо поле «

3) ставимо покажчик активної комірки на «

4) із контекстного меню вибираємо «

5) у списку «

6) у віконці «

7) натискуємо на «

8) ставимо покажчик активної комірки на «

9) із контекстного меню (рис. 5) вибираємо «

10) клацаємо по кнопці «

11) у списку «

12) у полі «

13) у списку «

14) натискуємо на «

15) стаємо на комірку на «

16) у вікні «

17) у списку «

18) у списку «

19) у списку «Операция:» вибираємо варіант «Сумма». Ось тепер усе зрозуміло. У зведеному звіті для кожного контрагента ми побачимо зміну сумарного обсягу реалізації в поточному місяці щодо попереднього місяця. Причому цю інформацію буде показано у гривнях. Вибравши як поле «Операция:» варіант «Количество», ми могли б отримати порівняльну характеристику щодо кількості продажів;

20) у полі «Имя:» друкуємо текст «Прирост, тыс. грн.»;

21) натискуємо на «ОК». У зведеному звіті з’явилися відомості про зміну обсягів реалізації (у тис. грн.) за контрагентами за кожен місяць. Інакше кажучи, для кожного контрагента ми отримали динаміку продажів по місяцях у вартісному виразі. Порівняння з попереднім періодом відбувається тому, що ми вказали параметр «(назад)». Продовжимо;

22) стаємо на комірку на «C7». Клацаємо правою кнопкою миші;

23) із контекстного меню вибираємо «Параметры поля…»;

24) у вікні «Вычисление поля сводной таблицы» (рис. 3) клацаємо по кнопці «Дополнительно»;

25) у списку «Дополнительные вычисления:» (рис. 8) зазначаємо варіант «Приведенное отличие». У списку «поле:» вибираємо «Месяц», у списку «элемент» — «(назад)»;

26) У полі «Имя:» друкуємо текст «Прирост, %»;

27) у списку «Операция:» вибираємо варіант «Сумма»;

28) натискуємо на «ОК». У зведеному звіті з’являться дані про відсотки зростання (падіння) продажів поточного місяця порівняно з попереднім. Остаточну форму звіту показано на рис. 9.

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

1) у відсотковому форматі не можна виділити кольором від’ємні значення;

2) застосувати умовне форматування до елементів усієї таблиці ми не можемо, оскільки хочемо виділити червоним лише від’ємні значення в рядку «

Це означає, що спосіб форматування потрібно зазначити в самій зведеній таблиці

1) клацаємо правою кнопкою по комірці «C7». Із контекстного меню вибираємо «Параметры поля…»;

2) у вікні «Вычисление поля сводной таблицы» (рис. 3) клацаємо по кнопці «Формат…». Відкриється вікно, як показано на рис. 10.

Спочатку в цьому вікні підсвічуватиметься варіант «

3) зі списку «

4) у полі «

5) натискуємо на «

Тепер від’ємні значення в рядку «