Використання нарізок у зведеній таблиці Excel - Посібник для початківців

Версія зведеної таблиці Excel 2010 була приголомшена появою нової супер крутої функції - нарізки.

Нарізка зведеної таблиці дозволяє фільтрувати дані, коли ви вибираєте один або кілька варіантів у полі «Нарізка» (як показано нижче).

У наведеному вище прикладі Slicer - це помаранчеве поле праворуч, і ви можете легко відфільтрувати зведену таблицю, просто натиснувши кнопку області в нарізці.

Давайте розпочнемо.

Натисніть тут do Завантажте зразки даних і слідкуйте за ним.

Вставка слайсера у зведену таблицю Excel

Припустимо, у вас є набір даних, як показано нижче:

Це фіктивний набір даних (роздрібні продажі в США) і охоплює 1000 рядків. Використовуючи ці дані, ми створили зведену таблицю, яка показує загальний обсяг продажів у чотирьох регіонах.

Читати далі: Як створити зведену таблицю з нуля.

Коли у вас є зведена таблиця, ви можете вставити зрізи.

Хтось може запитати - навіщо мені потрібні слайсери?

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

Слайсери - це більш наочний спосіб, який дозволяє фільтрувати дані зведеної таблиці на основі вибраного.

Нижче описано, як вставити нарізку для цієї зведеної таблиці.

• Виберіть будь -яку клітинку у зведеній таблиці.
• Перейдіть до пункту Вставка -> Фільтр -> Нарізка.
• У діалоговому вікні Вставити нарізки виберіть розмір, для якого можна фільтрувати дані. У вікні нарізки будуть перераховані всі доступні розміри, і ви зможете вибрати один або кілька розмірів одночасно. Наприклад, якщо я вибираю лише Регіон, він буде вставляти лише поле Регіональний нарізник, а якщо я обираю Регіон та Тип роздрібної торгівлі, обидва, то він вставляє два нарізки.
• Натисніть OK. Це дозволить вставити зріз (и) у робочий аркуш.

Зауважте, що Slicer автоматично визначить усі унікальні елементи вибраного виміру та перелічить їх у полі нарізки.

Після того, як ви вставили нарізку, ви зможете відфільтрувати дані, просто натиснувши на елемент. Наприклад, щоб отримати продажі лише для південного регіону, натисніть на південний. Ви помітите, що вибраний елемент набуває іншого відтінку кольору порівняно з іншими елементами у списку.

Ви також можете вибрати вибір кількох елементів одночасно. Для цього утримуйте клавішу керування та клацніть ті, які потрібно вибрати.

Щоб видалити виділення, натисніть на значок фільтра (з червоним хрестиком) у верхньому правому куті.

Вставка декількох нарізок у зведену таблицю

Ви також можете вставити кілька нарізок, вибравши декілька вимірів у діалоговому вікні Вставити нарізки.

Щоб вставити кілька нарізок:

• Виберіть будь -яку клітинку у зведеній таблиці.
• Перейдіть до пункту Вставка -> Фільтр -> Нарізка.
• У діалоговому вікні Вставити нарізки виберіть усі розміри, для яких потрібно отримати нарізки.
• Натисніть OK.

Це додасть усі вибрані фрагменти до робочого аркуша.

Зауважте, що ці нарізки з'єднані між собою. Наприклад, якщо я вибираю "Середній Захід" у фільтрі "Регіон" та "Багаторядковий" у фільтрі "Тип роздрібної торгівлі", тоді він відображатиме продажі для всіх роздрібних продавців "Багаторядковий" у регіоні Середнього Заходу.

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

Слайсери проти Фільтри звітів

Чим відрізняються нарізки та фільтри звітів?

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

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

Ось деякі ключові відмінності між нарізками та фільтрами звітів:

• Нарізки не займають фіксовану клітинку на аркуші. Ви можете переміщати їх, як і будь -який інший об’єкт або форму. Фільтри звітів прив'язані до комірки.
• Фільтри звітів пов’язані з певною зведеною таблицею. Нарізки, з іншого боку, можуть бути пов'язані з кількома зведеними таблицями (як ми побачимо далі в цьому підручнику).
• Оскільки фільтр звітів займає нерухому клітинку, її простіше автоматизувати за допомогою VBA. З іншого боку, нарізка - це об’єкт і потребує більш складного коду.

Форматування слайсера

Слайсер поставляється з великою гнучкістю щодо форматування.

Ось речі, які можна налаштувати на нарізці.

Зміна кольорів нарізки

Якщо вам не подобаються стандартні кольори нарізки, ви можете легко змінити її.

• Виберіть слайсер.
• Перейдіть до Інструменти нарізки -> Параметри -> Стилі нарізки. Тут ви знайдете ряд різних варіантів. Виберіть той, який вам подобається, і ваш слайсер миттєво отримає це форматування.

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

Отримання кількох стовпців у коробці нарізки

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

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

Зробити це:

• Виберіть слайсер.
• Перейдіть до Інструменти нарізки -> Параметри -> Кнопки.
• Змініть значення стовпців на 2.

Це миттєво розділить елементи в нарізці на два стовпці. Однак ви можете отримати щось настільки жахливе, як показано нижче:

Це виглядає захаращеним, а повні імена не відображаються. Щоб він виглядав краще, ви змінюєте розмір нарізки і навіть кнопки в ній.

Зробити це:

• Виберіть слайсер.
• Перейдіть до Інструменти нарізки -> Параметри.
• Змінити висоту та ширину кнопок та нарізки. (Зверніть увагу, що ви також можете змінити розмір нарізки, просто вибравши її та скориставшись мишею для регулювання країв. Однак, щоб змінити розмір кнопки, потрібно внести зміни лише в Параметри).

Зміна/видалення заголовка нарізки

За замовчуванням слайсер вибирає назву поля з даних. Наприклад, якщо я створю слайсер для регіонів, заголовок автоматично буде "Регіон".

Ви можете змінити заголовок або повністю видалити його.

Ось кроки:

• Клацніть правою кнопкою миші на слайсері та виберіть Налаштування слайсера.
• У діалоговому вікні Налаштування нарізки змініть заголовок заголовка на потрібний.
• Натисніть OK.

Це змінить заголовок у нарізці.

Якщо ви не хочете бачити заголовок, зніміть прапорець Показувати заголовок у діалоговому вікні.

Сортування елементів у нарізці

За замовчуванням елементи у нарізці сортуються у порядку зростання у разі тексту та від старих до новіших у разі чисел/дат.

Ви можете змінити налаштування за замовчуванням і навіть використовувати власні критерії сортування.

Ось як це зробити:

• Клацніть правою кнопкою миші на слайсері та виберіть Налаштування слайсера.
• У діалоговому вікні Налаштування нарізки можна змінити критерії сортування або скористатися власними критеріями сортування.
• Натисніть OK.

Читати далі: Як створити спеціальні списки в Excel (для створення власних критеріїв сортування)

Приховування елементів без даних із коробки нарізки

Може статися так, що деякі елементи у зведеній таблиці не мають даних. У таких випадках ви можете змусити слайсери приховати цей елемент.

Наприклад, на зображенні нижче у мене є два нарізки (один для регіону, а інший для типу роздрібної торгівлі). Коли я вибираю Середній Захід, пункт Спеціальність у другому фільтрі отримує світло -блакитний відтінок, який вказує на відсутність у ньому даних.

У таких випадках ви можете не відображати його взагалі.

Нижче наведено кроки для цього.

• Клацніть правою кнопкою миші на слайсері, в якому потрібно приховати дані, і виберіть Налаштування нарізки.
• У діалоговому вікні Налаштування нарізки з параметрами "Сортування та фільтрація елементів" встановіть прапорець "Приховати елементи без даних".
• Натисніть OK.

Підключення слайсера до кількох зведених таблиць

Слайсер можна підключити до кількох зведених таблиць. Після підключення ви можете використовувати один зріз для одночасного фільтрування всіх підключених зведених таблиць.

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

Читати далі: Що таке кеш зведеної таблиці та як ним користуватися?

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

Щоб підключити цей зріз до обох зведених таблиць:

• Клацніть правою кнопкою миші на слайсері та виберіть Звіти про підключення. (Крім того, ви також можете вибрати слайсер і перейти до Інструменти нарізки -> Параметри -> Нарізка -> З’єднання звітів).
• У діалоговому вікні З'єднання звітів ви побачите всі імена зведеної таблиці, які мають спільний кеш -пам’ять. Виберіть ті, які потрібно підключити до нарізки. У цьому випадку у мене є лише дві зведені таблиці, і я під’єднав обидві з нарізкою.
• Натисніть OK.

Тепер ваш слайсер підключений до обох зведених таблиць. Коли ви робите вибір у нарізці, фільтрація відбуватиметься в обох зведених таблицях (як показано нижче).

Створення динамічних зведених діаграм за допомогою нарізок

Так само, як ви використовуєте слайсер із зведеною таблицею, ви також можете використовувати його зі зведеними діаграмами.

Щось, як показано нижче:

Ось як можна створити цю динамічну діаграму:

• Виберіть дані та перейдіть до пункту Вставка -> Діаграми -> Зведена діаграма.
• У діалоговому вікні Створення зведеної діаграми переконайтеся, що діапазон правильний, і натисніть OK. Це дозволить вставити зведену діаграму в новий аркуш.
• Виберіть поля (або перетягніть поля в розділ області), щоб отримати потрібну зведену діаграму. У цьому прикладі ми маємо діаграму, яка показує продажі за регіонами за чотири квартали. (Прочитайте тут про те, як групувати дати як чверті).
• Коли ви зведете зведену діаграму, перейдіть до пункту Вставка -> Нарізка.
• Виберіть потрібний параметр нарізки за допомогою діаграми. У цьому випадку мені потрібні типи роздрібної торгівлі, тому я перевіряю цей вимір.
• Відформатуйте діаграму та нарізку, і все готово.

Зауважте, що ви можете підключити кілька зрізів до однієї зведеної діаграми, а також можете підключити декілька діаграм до одного зрізу (так само, як ми підключили кілька зведених таблиць до одного зрізу).

Натисніть тут, щоб завантажити зразки даних та спробувати самі.

Вам також можуть сподобатися такі підручники зведеної таблиці:

• Як групувати дати в зведеній таблиці Excel.
• Як групувати числа в зведеній таблиці в Excel.
• Як оновити зведену таблицю в Excel.
• Підготовка вихідних даних для зведеної таблиці.
• Як додати та використовувати обчислюване поле зведеної таблиці Excel.
• Як застосувати умовне форматування до зведеної таблиці в Excel.
• Як замінити порожні клітинки нулями у зведених таблицях Excel.