Почніть з кількох років даних.
Скажімо, у вас є два роки детальних записів. Кожен запис має щоденну дату. Коли ви створюєте зведену таблицю з цього звіту, у вас буде сотні рядків щоденних дат у зведеній таблиці. Це не дуже короткий зміст.
Виберіть одну з цих комірок дати у зведеній таблиці. На вкладці Аналіз на стрічці виберіть Групове поле.
Оскільки ви знаходитесь у полі дати, ви отримуєте цю версію діалогового вікна Групування. У ньому зніміть виділення з Months і виберіть Years.
Щоденні дати зводиться до років. Перемістіть поле Дата з РЯДКІВ у СТОЛБЦІ.
Результат майже ідеальний. Але замість загальної суми у стовпці D ви, мабуть, хочете відсоткову дисперсію.
Щоб позбутися стовпця Загальний підсумок, на вкладці Дизайн виберіть Загальні підсумки, Увімкнено лише для стовпців. (Я згоден, це одне з найбільш незграбних формулювань у Excel.)
Щоб побудувати стовпець дисперсії, вам потрібно написати формулу поза зведеною таблицею, яка вказує всередині зведеної таблиці. Не торкайтесь миші або клавіш зі стрілками під час створення формули, інакше з’явиться неприємна функція GETPIVOTDATA. Натомість просто введіть =C5/B5-1і натисніть Enter.
Дякуємо Олександру Воробьєву, що запропонував цю пораду.
Переглянути відео
- Почніть з кількох років даних
- Вставка, зведена таблиця
- Перетягніть поле дати в область рядків
- Excel 2016: натисніть Ctrl + Z, щоб розгрупувати дати
- Перетягніть дохід у область цінностей
- Виберіть будь-яку дату в області рядків
- Використовуйте поле групи
- Виберіть місяці та роки
- Як додати проміжні підсумки до поля років після групування зведеної таблиці
- Використовуйте табличну форму у зведеній таблиці, щоб надати кожному полю рядка власний стовпець
- Повторіть усі мітки рядків, щоб заповнити порожні місця в зведеній таблиці
- Перетягніть роки в область колони
- Клацніть правою кнопкою миші заголовок загальної суми та видаліть
- Як уникнути функції GetPivotData, коли формула вказує на зведену таблицю
- Щоб побудувати дисперсію, введіть формулу без миші та стрілок
- Дякуємо Олександру Воробьєву, що запропонував цю пораду
Стенограма відео
Дізнайтеся Excel із подкасту MrExcel, серія 1998. Зведена таблиця Рік за роком.
Я підкастую всю цю книгу, натисніть клавішу „i” у верхньому правому куті для списку відтворення MrExcel Excel.
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Сьогодні перша тема в книзі про зведені таблиці та про те, як створити звіт за рік. Отже, ми починаємо з набору даних, який триває два роки, бачите, ми переходимо з 2018 по 2019 рік. Я збираюся створити зведену таблицю, Вставити, Зведену таблицю, Добре, це нормально, давайте перейдемо до новий аркуш.
На новому аркуші ми візьмемо поле Дата і перетягнемо його в область Рядки. А потім, бачите, в Excel 2016 він автоматично групує речі. Зараз я знаю, що більшість людей все ще перебувають у програмі Excel 2013, можливо Excel 2010, тому я збираюся натиснути Ctrl + Z, щоб скасувати. Це досвід, який ви отримаєте в 2010 і 2013 роках, ми збираємося взяти поле Дохід і перетягнути його в область Значення. Отже, ми починаємо з потворного звіту, там буде сотні і сотні рядків, тому що кожен день ми продавали. Але я збираюся перейти до самого першого поля дати, самого першого поля дати, перейти до інструментів зведеної таблиці, проаналізувати, у 2013 або 2016 та 2010 рр. Воно називалось Параметри та вибрати Групове поле. Переконайтеся, що ви вибрали дату, перш ніж вибрати Групове поле, і згорніть події до місяців та років. Або квартали та роки,або місяці, квартали та роки, натисніть кнопку ОК. І що ми отримуємо, це нове віртуальне поле під назвою Роки тут, в області Рядки.
Тепер кілька прикрощів. Прикро, що вони не дають нам підсумків за рік, це досить легко виправити, хоча в моєму випадку мені цього не потрібно виправляти. Ми натискаємо «Налаштування поля» та «Автоматично», оскільки я не планую залишати Роки тут. Звичайно, кожного разу, коли я створюю зведену таблицю, я переходжу до проектування та кажу "Показати у табличній формі", щоб отримати все у своїй колонці. А потім повторіть усі мітки елементів, щоб заповнити ці пропуски вздовж лівої сторони. Знову ж, це не було б необхідним тут, тому що я не планую залишати Роки там, де вони є, я збираюся брати Роки та перетягувати їх до Стовпців, і це звіт, на який я стріляв.
Гаразд, отже, у нас є місяці, що спускаються ліворуч, з 2018 по 2019 рік. У стовпці D, що я насправді хочу, я не хочу загального підсумку, я натиснув правою кнопкою миші та сказав Видалити загальний Усього, і тепер ми поза зведеною таблицею. Отже,% Change, дуже спокусливо побудувати цю формулу за допомогою миші або клавіш зі стрілками, але ви не можете, оскільки ви отримаєте функцію… get Pivot Data, яку нелегко скопіювати. Отже, = C5 / B5-1, ми відформатуємо це у відсотках і двічі клацнемо, щоб скопіювати, правильно. Прекрасний звіт, що показує, де ми були минулого року, де ми були цього року, а потім відсоток змінюється з року в рік.
Що ж, ця порада та ще 40 - все в цій книзі. Вперед, клацніть на "i" у верхньому правому куті біля книги. Добре, так що ви починаєте з набору даних з кількох років, виконайте Вставлення, Зведена таблиця, перетягніть поле дати в область рядка. А в Excel 2016 натисніть Ctrl + Z, щоб скасувати їх автоматичне оновлення групування. Чому б нам просто не залишити це тут? Ну, насправді це залежить. Якщо у вас є дані, скажімо, з 2 січня по 30 грудня, вони не збираються правильно групувати ці дані. Він повинен витратити цілий рік, щоб ви отримали поле року. Можливо, у вас це виходить, я трохи виродка контролю, я, скоріше, просто матиму контроль, тому і скасовую. Існує: перетягніть дохід у область значень, виберіть будь-яку комірку, будь-яку дату в області рядка, а потім Поле групи, виберіть Місяці та роки,перетягніть роки по області стовпця, клацніть правою кнопкою миші загальний підсумок, щоб видалити, а потім побудуйте відхилення, введіть формулу, не використовуйте клавіші миші та стрілки.
Я не уявляю, як це вимовляти, але завдяки цьому читачеві (Александр Воробьев), що запропонував цю пораду, і дякую вам, що завітали. Ми побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast1998.xlsx
