Формула зведеної таблиці в Excel

Формула зведеної таблиці в Excel (Зміст)

Формула зведеної таблиці в Excel
Спеціальне поле для обчислення суми прибутку
Розширена формула в обчисленому полі

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

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

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

У цій статті ми продемонструємо способи використання обчислених полів зведеної таблиці для побудови нових стовпців на основі нашої вимоги.

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

Ви можете завантажити шаблон шаблону формули Excel таблиці формули тут - шаблон формули таблиці Excel

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

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

Проблема полягає в тому, що у вихідних даних у мене немає стовпця про прибуток. Мені потрібно з’ясувати відсоток прибутку та прибутку для кожної країни. Ці два стовпці ми можемо додати до самої зведеної таблиці.

Крок 1: Виберіть клітинку в зведеній таблиці. Перейдіть на вкладку Аналіз на стрічці та виберіть Поля, Елементи та Набори. Під цим пунктом виберіть Розрахункове поле.

Крок 2: У діалоговому вікні нижче введіть назву вашому новому розрахованому полі.

Крок 3: У розділі Формула застосуйте формулу, щоб знайти прибуток. Формула пошуку прибутку - валовий обсяг продажів - COGS.

Зайдіть всередину рядка формул> Виберіть Валовий обсяг продажів у полі нижче та двічі клацніть, щоб воно з’явилося на панелі формул.

Тепер введіть символ мінус (-) та виберіть COGS> Подвійне клацання.

Крок 4: Клацніть ADD і OK для завершення формули.

Крок 5: Тепер у поворотній таблиці є наш стовпець ВСІЙ доходності.

Це обчислене поле є гнучким, воно не обмежується лише загальнодержавним аналізом, але ми можемо використовувати його для всіх видів аналізу. Якщо я хочу ознайомитися з аналізом по країні та продуманим, мені просто потрібно перетягнути стовпець продукту до поля ROW, це покаже розбиття прибутку для кожного товару в кожній країні.

Крок 6: Тепер нам потрібно розрахувати відсоток прибутку. Формула для обчислення відсотка прибутку - загальний прибуток / валовий обсяг продажів.

Перейдіть до "Аналіз" і знову виберіть "Розраховане поле" під полями, елементами та наборами.

Крок 7: Тепер ми повинні побачити щойно вставлене обчислене поле Загальний прибуток у списку полів. Вставте це поле у формулу.

Крок 8: Введіть символ роздільника (/) та вставте поле валових продажів.

Крок 9: Назвіть це розраховане поле як відсоток прибутку.

Крок 10: Клацніть ADD і OK для завершення формули. У новому стовпці маємо відсоток прибутку.

Розширена формула в обчисленому полі

Що я зараз показав - це основні матеріали Розрахункового поля. У цьому прикладі я покажу вам розширені формули в полях обчисленої таблиці. Тепер я хочу обчислити суму заохочення, виходячи з відсотка прибутку.

Якщо прибуток% становить> 15%, стимул повинен становити 6% від загального прибутку.

Якщо прибуток% становить> 10%, стимул повинен становити 5% від загального прибутку.

Якщо прибуток% <10%, стимул повинен становити 3% від загального прибутку.

Крок 1: Перейдіть до Розрахункового поля та відкрийте діалогове вікно нижче. Назвіть назву як заохочувальна сума.

Крок 2: Тепер я буду використовувати умову IF для обчислення суми заохочення. Застосовуйте наведені нижче формули, як показано на зображенні.

= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))

Крок 3: Клацніть на ДОДАТИ ТА ОК, щоб завершити Тепер у нас стовпець Сума заохочувальної суми.

Обмеження обчисленого поля

Ми бачили диво обчислених полів, але воно також має деякі обмеження. Тепер погляньте на зображення нижче, якщо я хочу побачити розрив стимулюючої суми для продукту, ми помилимось SUB TOTAL & GRAND TOTAL OF INCENTIVE AMOUNT.

Тому будьте обережні, показуючи підсумкові підрахунки полів. Він покаже вам неправильні суми.

Отримайте список усіх обчислених формул поля

Якщо ви не знаєте, скільки формул є в обчисленому полі зведеної таблиці, ви можете отримати підсумок всіх цих даних на окремому робочому аркуші.

Перейдіть до аналізу> поля, елементи та набори -> перелічіть формули.

Це дасть вам зведення всіх формул у новому аркуші.

Що слід пам’ятати про формулу зведеної таблиці в Excel

Ми можемо видалити, змінити всі обчислені поля.
Ми не можемо використовувати формули, такі як VLOOKUP, SUMIF та багато інших формул, пов’язаних з діапазоном, у обчислених полях, тобто всі формули, які потребують діапазону, не можуть бути використані.