Невозможно переместить ячейки в таблице или диапазоне с примененным фильтром excel

Обновлено: 04.07.2024

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

В ней отфильтрованы данные по трем столбцам: Контрагент, Отсрочка платежа дни, Штрафы за просрочку оплаты. При этом из более чем 80-ти контрагентов отобрано фильтром только 25 необходимых, для отсрочки платежа установлено условие не показывать строки с просрочкой 25 дней и менее, а для штрафов - не показывать строки с суммами меньше или равные 1000р.
Ситуация1: точно такие же условия надо применить к такой же таблице, но за другие периоды. Вручную придется в другой таблице заново выбирать всех контрагентов и проставлять условия на другие столбцы.
Ситуация2: в таблицу необходимо добавить еще один столбец - Условия предоставления кредитного лимита. И поверх прежних условий фильтрации применить фильтр еще и по новому столбцу. Вручную придется сначала полностью убрать фильтр с листа, добавить столбец, установить фильтр заново и прописать все условия. Никак по-другому не получится(если только речь не об умной таблице - Вставка -Таблица).
С помощью команды Переместить фильтр обе ситуации становятся решаемыми очень просто. Все, что необходимо - выделить диапазон с нужным фильтром, перейти на вкладку MulTEx -группа Ячейки/Диапазоны -Диапазоны -Переместить фильтр:

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

И распространить эти условия на диапазон: указывается диапазон ячеек вместе с заголовком, к которому необходимо применить скопированные условия фильтрации.
Если столбцов в новом диапазоне меньше, чем в скопированном фильтре - то в новую таблицу будут перенесены условия первых n столбцов скопированного фильтра, где n - количество столбцов в новой таблице. Если столбцов больше - фильтр будет установлен на все указанные столбцы.
Если условия скопированного фильтра необходимо распространить на умную таблицу(Вставка (Insert) -Таблица (Table) ), то в поле И распространить эти условия на диапазон допускается указать одну любую ячейку внутри этой таблицы. Границы такой таблицы будут определены автоматически.

Сразу оговорюсь, что материал статьи предназначается для начинающих пользователей Excel. Опытные пользователи уже зажигательно станцевали на этих граблях не раз, поэтому моя задача уберечь от этого молодых и неискушённых «танцоров».

Вы не даёте заголовки столбцам таблиц

Многие инструменты Excel, например: сортировка, фильтрация, умные таблицы, сводные таблицы, — подразумевают, что ваши данные содержат заголовки столбцов. В противном случае вы либо вообще не сможете ими воспользоваться, либо они отработают не совсем корректно. Всегда заботьтесь, чтобы ваши таблицы содержали заголовки столбцов.

Пустые столбцы и строки внутри ваших таблиц

Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.

На одном листе располагается несколько таблиц

Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.

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

Данные одного типа искусственно располагаются в разных столбцах

Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

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

Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.

Если вы построите сводную таблицу, то обнаружите, что нет возможности легко получить данные по году или кварталу, так как показатели разнесены по разным полям. У вас нет одного поля по объёму продаж, которым можно удобно манипулировать, а есть 12 отдельных полей. Придётся создавать руками отдельные вычисляемые поля для кварталов и года, хотя, будь это всё в одном столбце, сводная таблица сделала бы это за вас.

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Рекомендуемый формат таблицы выглядит так:

Разнесение информации по разным листам книги «для удобства»

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

Информация в комментариях

Часто пользователи добавляют важную информацию, которая может им понадобиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментариях, вы можете только посмотреть (если найдёте). Вытащить это в ячейку затруднительно. Рекомендую лучше выделить отдельный столбец для комментариев.

Бардак с форматированием

Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:

Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.

Объединение ячеек

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

Объединение текста и чисел в одной ячейке

Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

Числа в виде текста в ячейке

Избегайте хранить числовые данные в ячейке в формате текста. Со временем часть ячеек в таком столбце у вас будут иметь текстовый формат, а часть в обычном. Из-за этого будут проблемы с формулами.

Если ваша таблица будет презентоваться через LCD проектор

Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.

Страничный режим листа в Excel

Это тот самый режим, при котором Excel показывает, как лист будет разбит на страницы при печати. Границы страниц выделяются голубым цветом. Не рекомендую постоянно работать в этом режиме, что многие делают, так как в процессе вывода данных на экран участвует драйвер принтера, а это в зависимости от многих причин (например, принтер сетевой и в данный момент недоступен) чревато подвисаниями процесса визуализации и пересчёта формул. Работайте в обычном режиме.

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

Но что, если необходимо отфильтровать данные по нескольким критериям одновременно? Или использовать при фильтрации формулу? Или отобрать значения одновременно в двух и более столбцах? На помощь в такой ситуации может прийти Расширенный фильтр (Advanced Filter) .

Процесс работы с этим фильтром существенно отличается от того, к которому все привыкли, и состоит из нескольких этапов:

Подготовка диапазона для условий фильтрации
Формирование условий фильтрации
Работа с мастером "Расширенного фильтра"

Чтобы Вы сразу понимали, о чем идет речь, на гифке ниже представлен небольшой пример работы фильтра:

Разберем каждый из этапов его применения поподробнее.

1. Подготовка диапазона для условий фильтрации

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

Также важно помнить, что при указании в 3 шаге (работа в Мастере "Расширенного фильтра") диапазона условий нужно выделять шапку и строки, где есть какие-то критерии. Пустые строки выделять не нужно, иначе фильтра воспримет их как сигнал "Отобразить все строки".

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

Правильная организация данных для работы с расширенным фильтром Правильная организация данных для работы с расширенным фильтром

2. Формирование условий фильтрации

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

Они могут быть 3 видов:

- текстовые критерии

Если в качестве текстового критерия ввести в поле какое-то слово, например, "Москва", то будут отобраны ВСЕ строки, в которых в заданном столбце запись начинается со слова "Москва"

Если слово или часть слова нужно искать не с начала строки, а во всей, то необходимо пользоваться подстановочными знаками. Если Вы про них не знаете, читайте вот здесь и здесь . В примере ниже найдены все строки, в которых в столбце "Город" есть слово "Петербург"

Если нужно найти точное вхождение слова или фразы, то критерий придется задать несколько необычной формулой. Например, чтобы найти строки, в которых записано "Петербург" и не отображать строки "Санкт-Петербург", нужно ввести формулу: ="=Петербург" (именно так, с двумя знаками "=") .

- числовые критерии и даты

В качестве критерия можно вводить число (и тогда будут отобраны строки, в которых значения столбца равны этому числу)

Также можно вводить выражения с использованием логических операторов (>, <, >=, <=, <>). Например, найти строки с суммой больше 500 000 можно введя критерий >500000

Особо внимательным нужно быть при вводе критериев в виде даты. Даты обязательно необходимо вводить через косую черту. Например, чтобы отобрать все сделки после 4 января 2017, нужно ввести критерий по полю "Дата" - >04/01/2017 (в некоторых версиях Excel требуется осуществлять ввод в формате ММ/ДД/ГГГГ, то есть сначала указывать месяц. Имейте это в виду при работе).

Самое лучшее, что умеет расширенный фильтр - это использовать в качестве критерия формулы. Чтобы все работало, задаваемая Вами формула должна возвращать значение ИСТИНА (и тогда строка выведется) или ЛОЖЬ (строка будет скрыта). Крайне важно - шапка столбца с формулой должна отличаться от любой записи в шапке таблицы (можете вообще оставить ее пустой). При написании формул, не забывайте правильно расставлять абсолютные и относительные ссылки.

Например, если нужно показать топ 5 строк по полю сумма, то необходимо будет ввести следующую формулу:

=F10>НАИБОЛЬШИЙ($F$10:$F$37;6),

где F10 - ячейка первой строки в столбце "Сумма" (она не закреплена, так как формула будет перебирать строки по очереди), $F$10:$F$37 - ссылка на диапазон, который занимает столбец "Сумма" (ссылка закреплена, так как столбец не изменяется).

В результате формула пройдет по всем строкам (от 10-ой до 37-ой) и скроет все, кроме тех, где значение больше шестого по величине (то есть оставит ТОП 5).

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

Итак, основные концепции, которые Вам нужно усвоить для успешного применения Расширенного фильтра:

- заголовок столбца, в котором пишем критерий отбора, должен быть точно таким же, как у того столбца, к которому применяем этот критерий. То есть, если отбираем строки, в которых в столбце "Сумма" значение больше 500, то и условие >500 пишем под шапку "Сумма";

- условия, записанные в одной строке, воспринимаются фильтром как связанные оператором И. Например, на картинке ниже записано условие И год 2017, И город Москва, И менеджер Петров .

- условия, записанные в разных строках, воспринимаются фильтром как связанные оператором ИЛИ. Условия могут применяться как к одному столбцу, так и к разным. Например, на картинке ниже записано условие ИЛИ город Москва, ИЛИ менеджер Иванов . Таким образом, каждая строка - это единый набор условий.

- если нужно задать условие И, но при этом использовать один и тот же столбец (например, И сумма больше 500 000, И сумма меньше 600 000 ), то заголовок такого столбца нужно продублировать дважды. Пример:

Теперь Вы знаете, какие критерии можно задавать, и как их правильно комбинировать. Этого достаточно, чтобы создавать сложные запросы, которые не под силу обычному автофильтру. Например, если нужно показать все сделки в Москве за 2017 год с суммой больше 500 000, а также одновременно отобразить все сделки Иванова за 2016 год, которые входят в ТОП5, то критерии будут выглядеть вот так:

3. Работа с мастером "Расширенного фильтра"

Самое сложное позади - Вы научились формировать критерии отбора. Остался один шаг. Выделяем диапазон с исходными данными (или любую его ячейку - Excel сам определит и выделит нужный массив) и находим на ленте команду "Дополнительно" во вкладке "Данные" в группе "Сортировка и Фильтр". Перед Вами появится окно "Расширенный фильтр"

1) задать исходный диапазон (он будет выбран автоматически при запуске фильтра),

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

3) выбрать способ обработки (фильтровать данные в вашей исходной таблице или скопировать отфильтрованные строки в другое место),

4) указать, стоит ли показать все записи, или вывести только уникальные.

После настройки нажимаем "ОК" и получаем желаемый результат.

Чтобы очистить фильтр, переместите курсор в шапку таблицы с отфильтрованными данными и воспользуйтесь командой "Очистить" на ленте, или примените Автофильтр (Ctrl+Shift+L).

Недостатки Расширенного фильтра

Главным недостатком является то, что данный инструмент не является интерактивным и динамическим. Нельзя просто вписать в диапазон условий новый критерий и увидеть результат. Придется каждый раз вызывать Мастер "Расширенного фильтра" и повторять некоторые операции. Конечно, можно обойти и этот недостаток, но это уже тема для другой статьи.

Бонус. Полезный трюк с Расширенным фильтром

Если вам нужно быстро извлечь из диапазона уникальные записи, то проделайте следующее:

Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
Можно воспользоваться строкой быстрого поиска
Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:

При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.

Отмена фильтрации

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

Срезы

Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

Форматирование срезов

Выделить срез.
На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Расширенный фильтр

Расширенный фильтр предоставляет дополнительные возможности. Он позволяет объединить несколько условий, расположить результат в другой части листа или на другом листе и др.

Читайте также: