Как вставить срезы в сводные таблицы microsoft excel

Как исправить фильтрацию таблиц Excel?

1 Выберите весь столбец, чтобы применить фильтр к

1. Выберите весь столбец электронной таблицы таблицы, щелкнув букву столбца.
2. Щелкните вкладку Данные.
3. Затем нажмите кнопку «Фильтр», чтобы применить фильтр ко всему столбцу.
4. Нажмите кнопку со стрелкой фильтра ячеек, чтобы открыть параметры фильтрации, показанные непосредственно ниже.
5. Вы можете снять флажок «Пустые поля», чтобы не отображать пустые ячейки.

2 Удалите пустые ячейки из столбца таблицы

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

Выделите все строки пустых ячеек, удерживая клавишу Ctrl. Затем щелкните правой кнопкой мыши и выберите параметр «Удалить».

3 разгруппировать листы

Параметр «Фильтр» будет выделен серым цветом, когда ваши листы будут сгруппированы вместе. Таким образом, вы не можете фильтровать таблицы электронных таблиц в сгруппированных листах. Чтобы исправить это, щелкните правой кнопкой мыши сгруппированные листы в нижней части Excel и выберите «Разгруппировать листы».

4 Снять защиту листа

1. Выберите вкладку «Обзор» в Excel.
2. Нажмите кнопку Снять защиту листа.
3. Если открывается окно Снять защиту листа, введите пароль для рабочего листа в текстовое поле.
4. Щелкните кнопку ОК.

5 Разъединить ячейки

1. Нажмите горячую клавишу Ctrl + F.
2. Нажмите кнопку «Форматировать» в окне «Найти и заменить».
3. Щелкните Объединить ячейки на вкладке «Выравнивание», показанной непосредственно ниже.
4. Нажмите кнопку ОК.
5. Нажмите кнопку ” Найти все”

   После этого в окне «Найти и заменить» будут перечислены все ссылки на ячейки с объединенными ячейками.

   .

6. Чтобы разделить объединенные ячейки, выберите объединенную ячейку.
7. На вкладке «Главная» выберите параметр «Объединить и центрировать».
8. Выберите параметр «Разъединить ячейки».

6 Установите новый фильтр

1. Если в вашей таблице есть строки, которые не фильтруются, попробуйте настроить новый фильтр. Для этого выберите вкладку «Данные».
2. Нажмите кнопку «Очистить» в группе «Сортировка и фильтр».
3. Затем выберите курсором весь диапазон столбцов таблицы.
4. Нажмите кнопку «Фильтр» на вкладке «Данные» Excel.

Это некоторые из разрешений, которые могут исправить ваши фильтры таблицы Excel. В большинстве случаев повторное применение фильтров или их очистка для настройки новых фильтров часто решает проблемы с фильтрацией Excel.

Какое из решений решило для вас проблему профессиональной фильтрации Excel? Дайте нам знать, оставив сообщение в разделе комментариев ниже.

СВЯЗАННЫЕ СТАТЬИ ДЛЯ ПРОВЕРКИ: 

• Как исправить ошибку нарушения общего доступа в Excel
• Как исправить ошибку “Файл не загружен полностью” в Microsoft Excel
• Microsoft Excel не может добавлять новые ячейки? Ознакомьтесь с этими советами

Источник записи: windowsreport.com

Текстовый фильтр в Эксель

Рассмотрим, как отфильтровать в Excel ячейки с определенным текстом. Простейший способ – это, по аналогии с прошлым примером, ввести нужный текст (или его часть) в поиске.

Однако, можно и более гибко настраивать отбор. Если в окне фильтра нажать « Текстовые фильтры », то в контекстном меню появится выбор способа сопоставления: равно, не равно, начинается с, заканчивается на, содержит, не содержит.

Например, нужно выбрать людей, которых не зовут Богдан. Выберем вариант «не содержит» и запишем ему критерий « Богдан ». Поставим пробелы перед именем и после. Иначе, например, Богданов Егор Егорович тоже попадет под фильтр, хотя его не зовут Богданом:

Как сделать автофильтр в Excel

Для включения необходимо щелкнуть в любом месте таблицы с данными, перейти на вкладку «Данные» — «Сортировка и фильтр». Нажать кнопку «Фильтр». Или нажмите комбинацию горячих клавиш CTRL+SHIFT+L.

Рядом с заголовками появятся стрелочки. При нажатии открывается выпадающий список с условиями для фильтрации.

Если данные отформатированы как таблица, то автофильтр включается автоматически (стрелочки видны сразу).

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

Сделаем фильтрацию числовых значений:

1. Нажимаем кнопку автофильтра у заголовка столбца с числовыми значениями. Выбираем «Числовые фильтры» — раскрывается список условий.
2. Выберем, например, «больше или равно». Откроется пользовательский автофильтр в Excel.
3. Зададим в качестве критерия для фильтрации значение «3000». Введем эти цифры в поле напротив условия.
4. На листе останутся только те данные, которые соответствуют заданному критерию.

Чтобы снять фильтр, нажимаем на значок в заголовке столбца и выбираем «Снять фильтр…».

Предположим, пользователю необходимо отобразить определенное количество наибольших или наименьших значений цены.

1. Нажимаем кнопку автофильтра у заголовка «Цена». В списке «Числовых фильтров» выбираем оператор «Первые 10».
2. Открывается меню «Наложение условия по списку». Определяемся, какие значения хотим видеть: наибольшие или наименьшие. С помощью счетчика задаем, сколько таких значений должно отобразиться в таблице.
3. Если отбор будет производиться по числам, то назначаем условие «Элементов списка». Если необходимо отфильтровать значения в процентах, то выбираем «% от количества элементов».
4. Чтобы закрыть окно и выполнить условие поиска, жмем ОК.

Наибольшие и наименьшие значения выбираются из исходного диапазона данных, а не по отфильтрованному списку.

В качестве критерия можно задать несколько условий поиска.

Назначим фильтр для столбца «Дата»:

1. Нажимаем кнопку автофильтра. Открываем выпадающий список «Фильтры по дате».
2. Чтобы отобразить данные за второе полугодие 2014 г., назначим критерий «После». В открывшемся окне «Пользовательского автофильтра» для критерия «После» напишем условие «01.06.2014». Выберем функцию «И». Назначим второй критерий – «До». Условие – «31.12.2014». Дату можно вводить вручную, а можно выбрать в «Календаре».
3. После нажатия кнопки ОК пользователю становится доступна информация за второе полугодие 2014 года.

Отфильтруем текстовый диапазон ячеек:

1. Нажимаем кнопку у заголовка «Наименование». При наведении курсора на «Текстовые фильтры» откроется список доступных операторов сравнения, по которым можно сделать отбор.
2. Допустим, нам нужно отобразить информацию по товарам, в названии которых есть цифра «2». Выбираем критерий «Содержит». Вводим условие «2».
3. После нажатия ОК.

При назначении условий для пользовательского автофильтра можно пользоваться элементами подстановки:

• «?» — заменяет один любой знак. Условие для фильтрации данных в столбце «Наименование» — «Содержит «1?»:
• «*» — заменяет несколько знаков.

В конце любого списка фильтров (числовых, текстовых, по дате) есть «Настраиваемый фильтр». Эта кнопка тоже открывает окно пользовательского автофильтра в Excel.

Плюсы и минусы инструмента умные таблицы в Excel

Преимущества инструмента Умные таблицы в Excel

1. Таблица автоматически расширяется при добавлении строк или столбцов. 

Главное требование при этом — новый столбец или строка должны добавляться “впритык” к таблице — без пропусков строк или столбцов.

2. Формулы, введенные в ячейку, после нажатия Enter автоматически копируются на весь столбец.

3. Автоматически добавляются автофильтры ко всем столбцам. И на столбцы, добавленные уже после создания таблицы, фильтры также “навешиваются” автоматически. При этом фильтр можно убрать (см. в разделе Форматирование умной таблицы)

При прокрутке таблицы строка с заголовками и фильтрами закреплена вверху страницы, даже если вы отдельно не закрепляли области. 

4. Умные таблицы в Excel имеет имя, которое присваивается автоматически, но его можно изменить. 

Посмотреть и изменить имя умной таблицы в Excel можно на вкладке Конструктор таблиц (при этом курсор должен быть установлен на любой ячейке в таблице, иначе вкладка Конструктор таблиц не появится на ленте)

5. К умной таблице можно добавить срезы, такие же, как для сводной таблицы. Срезы в умной таблице упрощают ее фильтрацию и делают более отчет более профессиональным.

6. Структурированные формулы, которые легко читать.

В формулах внутри таблицы используются не привычные адреса ячеек и диапазонов, а специальные адреса, отражающие называние столбца или области внутри таблицы. Это облегчает “чтение” формулы (если, конечно, столбцам в таблице были даны релевантные названия).

7. Автоматическое добавление итогов к таблице с возможностью выбора вида итога (сумма, среднее и т.д.).

8. Удобно создавать сводные таблицы на основании умных таблиц. При добавлении новых столбцов или строк в исходную таблицу, они автоматически добавляются в сводную таблицу (после ее обновления) без необходимости настраивать диапазон.

9. Умные таблицы в Excel удобно загружать в Power Query для последующей обработки. 

10. Ну и конечно, красивый “полосатый” внешний вид без “танцев с бубнами” с условным форматированием. 

Еще одно преимущество “внешности” — легкая настройка форматирования умной таблицы в Excel. На вкладке Конструктор таблиц его можно быстро настроить (см. раздел Форматирование умной таблицы)

Но, конечно, “умные” таблицы в Excel имеют и ряд особенностей, которые могут затруднить их использование. 

Минусы инструмента Умные таблицы в Excel

1. При достаточно большом размере таблицы и большом количестве формул файл с умной таблицей может тормозить. Это связано с тем, что форматирование умной таблицы сделано по типу условного форматирования, которое “нагружает” файл.
2. Если добавить строку итогов, то функция автоматического расширение «умной таблицы» по строкам при добавлении данных перестает работать. Это логично, т.к. не могут же новые строки с данными быть после строки итогов. Расширить таблицу в этом случае можно только растягиванием за уголок.
3. Заголовки можно располагать только в первой строке умной таблицы. Это лишает возможность делать сложные «шапки» таблиц, а также использовать одинаковые названия столбцов — если столбцы будут называться одинаково, Excel автоматически добавить к ним цифру 2, 3 и т.д. 
4.  Формулы могут становятся очень длинными, поскольку в формулу прописывается наименование столбца. Особенно если заголовки длинные сами по себе. Этот момент ограничивает возможность давать сложные названия столбцам.
5. Нет возможности закрепить ячейки или столбцы абсолютными/относительными ссылками, и при “протягивании” формулы ссылки съезжают.
6. Нельзя установить режим совместного доступа в файл, который содержит хотя бы одну “умную” таблицу.

Создание сводной таблицы вручную

Иногда ни одна из сводных таблиц, предлагаемых инструментами быстрого анализа или кнопкой Рекомендуемые таблицы, не подходит. В подобных случаях можно либо выбрать готовый образец сводной таблицы, макет которого наиболее близок к требуемой вам сводной таблице, либо создать нужную таблицу “с нуля”. Создать сводную таблицу вручную не так уж и трудно, как может показаться на первый взгляд.

Чтобы создать сводную таблицу вручную на основе листа с анализируемыми данными, установите курсор ячейки в одной из ячеек списка данных и щелкните на кнопке Сводная таблица, находящейся на вкладке ленты Вставка.

На экране появится диалоговое окно создания сводной таблицы, и будут выделены все данные в списке, в котором находится курсор ячейки (вокруг списка появится мерцающая пунктирная рамка). Можете откорректировать этот диапазон в текстовом поле Таблица или диапазон, если рамка не включает какие-либо данные, которые следует консолидировать. По умолчанию Excel создает сводную таблицу на новом рабочем листе, добавляемом в книгу. Если хотите, чтобы сводная таблица была создана на том же листе, где находятся исходные данные, щелкните на кнопке На существующий лист, а затем в поле Диапазон укажите ячейки, в которых будет находиться сводная таблица. (Проверьте, не будет ли перекрывать создаваемая сводная таблица данные, уже существующие на рабочем листе.)

Если источник данных сводной таблицы представляет собой внешнюю базу данных, созданную в другой программе, такой как Access, установите переключатель Использовать внешний источник данных. Потом щелкните на кнопке Выбрать подключение, а затем в открывшемся диалоговом окне выберите требуемое подключение. Кроме того, Excel поддерживает анализ данных для нескольких связанных таблиц листа (так называемая “модель данных”). Если данные новой сводной таблицы будут анализироваться наряду с данными существующей сводной таблицы, то установите флажок Добавить эти данные в модель данных.

После того как будет определен источник данных и указано место расположения сводной таблицы, щелкните на кнопке ОК, и программа добавит пустую сетку для новой таблицы, а также откроет в правой части области рабочего листа панель Список полей сводной таблицы. Эта панель разделена на две части. Вверху находится список полей источника данных, которые можно добавить в сводную таблицу, а внизу — область, разделенная на четыре зоны: ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ и ЗНАЧЕНИЯ.

Чтобы завершить создание сводной таблицы, осталось назначить поля, перечисленные в списке, различным частям таблицы. Эта задача осуществляется путем перетаскивания имени поля из списка в соответствующую зону нижней части панели задач Поля сводной таблицы.

• ФИЛЬТРЫ. Здесь содержатся поля, позволяющие фильтровать данные таблицы. Так, например, если перетащить сюда поле года, то можно будет отображать в таблице сводные данные для каждого конкретного года, представленного в списке данных.
• СТОЛБЦЫ. Здесь содержатся поля, определяющие данные, которые отображаются в столбцах сводной таблицы.
• СТРОКИ. Здесь находятся поля, определяющие данные, которые отображаются в строках сводной таблицы.
• ЗНАЧЕНИЯ. Здесь содержатся поля, определяющие, какие данные будут отображаться в ячейках таблицы, т.е. значения, консолидируемые в последнем столбце (по умолчанию суммируемые).

Чтобы лучше понять, как эти зоны связаны со сводной таблицей, посмотрите на готовую таблицу, показанную скриншоте ниже.

В этой сводной таблице в качестве фильтра из списка данных было выбрано поле Дата. Поле Категория я назначил меткам строк. В качестве значений были выбраны поля Расход и Доход.

В результате выполненного выбора в сводной таблице теперь отображается сумма расходов и доходов по категориям с возможностью выбора конкретной даты.

После создания новой сводной таблицы (или выбора ячеек существующей таблицы рабочего листа) программа отображает контекстную вкладку Анализ из группы контекстных вкладок Работа со сводными таблицами — последняя автоматически добавляется на ленту. Среди множества групп этой вкладки в правой части находится группа Показать, включающая следующие полезные команды.

• Список полей. Служит для сокрытия и отображения списка полей на панели задач в правой части области рабочего листа.
• +/- Кнопки. Используется для сокрытия и отображения кнопок сворачивания (-) и разворачивания (+) конкретных строк и столбцов, позволяющих временно удалять и отображать в сводной таблице конкретные значения.
• Заголовки полей. Служит для сокрытия и отображения полей, назначаемых меткам строк и столбцов сводной таблицы.

Форматирование значений сводной таблицы

Чтобы отформатировать итоговые значения сводной таблицы с помощью любого из числовых форматов Excel, выполните следующие действия.

1. Щелкните на имени поля таблицы (1), содержащего слова “Сумма по полю”, за которыми следует имя поля. Перейдите на вкладку Анализ (2) набора контекстных вкладок Работа со сводными таблицами, и щелкните на кнопке Параметры поля (3).

Откроется диалоговое окно Параметры поля значений (4).

2. В диалоговом окне щелкните на кнопке Числовой формат (1). Откроется вкладка Число диалогового окна Формат ячеек (2).
3. В списке Категории щелкните на типе числового формата, который хотите применить к значениям сводной таблицы.
4. (Дополнительно.) Измените остальные параметры выбранного формата (число десятичных знаков, разделитель разрядов и способ представления отрицательных чисел).
5. Закройте открытые диалоговые окна, щелкнув в каждом из них на кнопке ОК.

Решение № 2. Добавление вычисляемого столбца в набор данных

Если вы не можете использовать макросы, вы можете решить эту проблему с помощью вычисляемого столбца. Это решение НЕ так гибко, как макрос. Оно НЕ очень хорошо работает, если вы хотите иметь срезы, где пользователь может выбирать другие даты отчета.

Мы можем добавить столбец
к исходным данным, чтобы проверить, совпадает ли дата отчета в каждой строке с
самой последней датой. Мы назовем этот столбец «Current Wk».

Если это так, то формула
вернет ИСТИНА, если нет — ЛОЖЬ.

Это очень простая формула.
Мы могли бы использовать функцию If, но в этом нет необходимости. Знак
равенства оценивает совпадение и возвращает значение ИСТИНА или ЛОЖЬ.
Ознакомьтесь с моей статьей If в
формулах Excel для объяснения этого.

Теперь мы можем добавить
этот новый столбец в область «Фильтры» сводной таблицы и отфильтровать его для
ИСТИНА. Это означает, что каждый раз, когда исходные данные обновляются новыми
данными, формулы пересчитывают новые строки текущей недели. Обновление сводной
таблицы автоматически применит фильтр для строк текущей недели и отобразит их в
сводной таблице.

Недостатком здесь является
то, что пользователь не может использовать слайсер для фильтрации других дат.
Технически они могут, но нам придется повторно применить фильтр Current Wk при
следующем обновлении данных. Мы могли бы использовать макрос для автоматизации
этого, но тогда мы действительно вернулись к решению № 1.

Хотя он не такой
динамичный, как решение № 1, рассчитанный столбец может быть всем необходимым
для отображения данных за последний период в статическом отчете.

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

Вы также можете вставить несколько срезов, выбрав более одного измерения в диалоговом окне «Вставить срезы».

Чтобы вставить несколько срезов:

• Выберите любую ячейку в сводной таблице.
• Перейдите в Вставка -> Фильтр -> Слайсер.
• В диалоговом окне «Вставить срезы» выберите все размеры, для которых вы хотите получить срезы.
• Щелкните ОК.

Это вставит все выбранные слайсеры в рабочий лист.

Обратите внимание, что эти слайсеры связаны друг с другом. Например, если я выберу «Средний Запад» в фильтре «Регион» и «Многострочный» в фильтре «Тип розничного продавца», тогда будут показаны продажи для всех многострочных розничных продавцов только в регионе Среднего Запада

Кроме того, если я выберу Mid West, обратите внимание, что опция Specialty во втором фильтре приобретает более светлый оттенок синего (как показано ниже). Это означает, что нет данных по розничным торговцам специализированной продукции в регионе Среднего Запада

Настраиваемый тестовый фильтр

Расскажу, как поставить фильтр в Excel на два условия в одной ячейке. Для этого кликнем Текстовые фильтры – Настраиваемый фильтр .

Пусть нам понадобилось отобрать людей с именем Богдан или Никита. Запишем логику, как на картинке

А вот результат:

Как определить, какой выбрать оператор сравнения, «И» или «ИЛИ»? Логика такая:

• И – когда нужно, чтобы выполнились оба условия одновременно
• ИЛИ – когда достаточно выполнения хотя бы одного условия из двух

Больше про логические операторы вы можете прочесть в этой статье.

Кроме того, в условии можно использовать операторы:

• ? – это один любой символ
• * – любое количество любых символов

Например, чтобы выбрать ФИО, в котором присутствует строка «ктор», запишем условие так: *ктор*.

Фильтрация полей отчета

Пожалуй, наиболее важной кнопкой сводной таблицы является кнопка фильтрации полей отчета. Выбрав конкретные пункты из раскрывающегося списка, прикрепленного к кнопке фильтрации, вы увидите в таблице только сводные данные для этого подмножества

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

Щелкните на кнопке фильтрации по Дате, в раскрывающемся списке выберите «1 января», а затем щелкните на кнопке ОК. В результате отобразятся расходы и доходы только за 1 января.

Аналогичным образом можно выбирать несколько дат. Для этого установите галочку напротив пункта Выделить несколько элементов.

Если позже вы решите отображать расходы и доходы за весь период, снова установите флажок в поле (Все) раскрывающегося списка фильтрации и щелкните на кнопке ОК.