Фильтрация данных в excel

Как пользоваться расширенным фильтром в Excel

Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

Заполняем меню расширенного фильтра:

Получаем таблицу с отобранными по заданному критерию строками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Заполняем параметры фильтрации. Нажимаем ОК.

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» — значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

Применим инструмент «Расширенный фильтр»:

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

1. Результат формулы – это критерий отбора.
2. Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
3. Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
4. Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).

В таблице остались только те строки, где значения в столбце «Количество» выше среднего.

Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».

Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.

Фильтр по цвету в Excel

Фильтр по цвету в Excel

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

Имеем таблицу в которой уже включён фильтр (вкладка «Главная», блок кнопок «Редактирование», кнопка «Сортировка и фильтр», пункт «Фильтр»). Также строки залиты разными цветами.

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

Для того, чтобы отфильтровать данные в нашей таблице по цвету:

• щёлкнем треугольник у второго столбца «Выручка»;
• в выпавшем списке выберем «Фильтр по цвету»;
• укажем цвет, данные которого нужно отобразить.

Отобразятся только ячейки таблицы с бледно-зелёной заливкой.

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

Если выбрать сортировку по бледно-зелёному цвету, то картина будет другой.

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

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

Снова открываем список настроек сортировки и фильтрации и выбираем пункт «Пользовательская сортировка».

На снимке выше уже готовый порядок отображения. Если нужно что-либо добавить:

• жмём «Добавить уровень»;
• добавляем условие сортировки (у нас это Магазин, Выручка, Управляющий);
• указываем признак сортировки (Цвет ячейки, Значения, Цвет шрифта, Значок ячейки);
• задаём порядок отображения (какой сначала цвет, затем следующий и т.д.);
• располагаем цвет уровня в нужномместе (сверху или снизу).

Удалить строку настройки сортировки можно кнопкой «Удалить уровень». В итоге получим то, что нужно!

Аналогично можно поступить и с цветом текста (шрифта). Поставим магазину «Восход» цвет текста синий. И настроим отображение именно по цвету текста (шрифта).

Вот и всё. Всем удачи! Если остались вопросы — пишите комментарии или через форму обратно связи!

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

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

Задание условий фильтрации

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

2. Записать условия фильтрации. Условия, записанные в одной строке, выполняются одновременно (как условие « И»), а в разных строках — как условие выбора (« ИЛИ»). В качестве условия может быть совпадение значения, которое заносится в ячейку, или сравнение с заданным в ячейке значением с помощью знаков  < или  >. Если один столбец должен удовлетворять двум условиям, его заголовок нужно повторить еще раз и записать в этом столбце второе условие.

3. На вкладке Данные  найти группу команд Сортировка и фильтр  и выбрать команду Дополнительно . 

1. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте  или скопировать результат в другое место .

1. Указать Исходный диапазон , выделяя исходную таблицу вместе с заголовками столбцов.
2. Указать Диапазон условий , отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
3. Указать при необходимости место с результатами в поле  Поместить результат в диапазон , отметив курсором ячейку диапазона для размещения результатов фильтрации.
4. Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи .

Расширенные возможности пользовательского автофильтра

Допустим нас интересует 10 продаж с наибольшими ценами. Быстрее всего можно реализовать данную задачу с помощью специальной опции автофильтра. Из выпадающего списка на столбце «Цена» выберите опцию: «Числовые фильтры»-«Первые 10». Данная функция автофильтра позволяет находить в этой таблице первые десять товаров с наибольшими ценами или 10 товаров (при необходимости и больше, например, 12) с наибольшей или наименьшей суммой продаж и т.п. Как видно из названия группы опции «Числовые фильтры» ее можно применять только к числовым значениям в столбцах таблицы, а также к датам (ведь дата в Excel – это число).

Допустим мы визуально анализируем отчет по продажам товаров на несколько сотен позиций и наименований, которые повторяются в смешанном порядке. Нас интересуют в первую очередь: какие товары из магазина №5 попали в ТОП-7 самых продаваемых? Если мы отсортируем все товары по наименованию, а потом будем суммировать количество проданных товаров по отдельности, то это займет много времени. Вместо суммирования групп позиций по отдельности можно воспользоваться промежуточными итогами или автофильтром. В несколько кликов мышки мы скроем ненужную информацию и оставим только необходимые данные содержащие соответственные итоговые значения. Для этого:

1. Сначала удалите критерий фильтрования из предыдущего примера: «ДАННЫЕ»-«Сортировка и фильтр»-«Очистить».
2. Из выпадающего списка на столбце «Магазин» отметьте галочкой только опцию: «Магазин 5».
3. Из выпадающего списка на столбце «Сумма» выберите опцию: «Числовые фильтры»-«Первые 10».
4. В появившемся окне «Наложения условия по списку» установите следующие параметры: наибольших; 7; элементов списка. И нажмите ОК.

В результате мы получили список из ТОП-7 самых продаваемых товаров в Магазине №5.

Как сделать сортировку в Excel по возрастанию и по убыванию

​ не наилучшее решение​ качестве критериев для​ это «Место рождения».​ И нажмите на​ В данном примере​ Excel.​щелкните​На вкладке​ Эта страница переведена​ вниз рядом с​

​ в этой таблице​Правой кнопкой мышки щелкните​ по выделенному столбцу.​ вычисления. Так же​ и тогда результаты​ можно просто сделать​ необходимые данные,которые вы​ для данной задачи.​ каждого первого выпадающего​ Второй выпадающий список​ кнопку «Сортировка».​ – это столбец​Перейдите курсором клавиатуры на​Цвет ячейки​Данные​ автоматически, поэтому ее​ фильтром для столбца​ диапазон A4:F12 и​ по ячейке A1​

Сортировка данных в Excel

​ В контекстном меню​ разъединены объединенные ячейки.​ их вычислений будут​ сортировку по этому​ правы не сохраняются​ Особенно если столбцов​

​ списка секции «Столбец»​ оставим без изменений​Обратите внимание на то,​

1. ​ А (№п/п). Часто​ любую ячейку в​
2. ​,​нажмите кнопку​ текст может содержать​ «Население», которая показывает,​ повторно выполнить второй​ на копии таблицы​ выберите опцию «Сортировка»-«от​ Осталось убрать лишний​ ошибочны. Во-вторых, после​

​ столбцу по возрастанию​ и сортировка не​ более тысячи. Поэтому​ в каждом уровне,​ – «Значение», а​ что значения ячеек​ необходимо привести таблицу​ столбце G (можно​Цвет шрифта​

​Фильтр​ неточности и грамматические​ что данные отсортированы​

1. ​ пункт этого раздела,​ на третьем листе​
2. ​ минимального к максимальному»​ текст в заголовках​
3. ​ многократных сортировок можно​ и все встанет​ отменяется…​ лучше воспользоваться специальным​ будут использованы заголовки​ в третьем указываем​ в остальных столбцах​ в изначальный вид​ даже на заголовок​или​.​ ошибки. Для нас​

​ в порядке убывания.​ только указать розовый​ (Лист3) и выберите​

Как в Excel сделать сортировку в столбце

​ и таблица готова​ перетасовать данные таблицы​ на изначальное место.​Как можно решить​

​ встроенным инструментом Excel​ столбцов рабочего листа​ тип сортирования «От​ остались без изменений:​ после выполнения с​

​ столбца G1)

А​Значок ячейки​Щелкните стрелку _з0з_ в​ важно, чтобы эта​ После сортировки в​

​ сверху. Таким образом​ опцию «Специальная вставка»-«значения».​ данные вне указанного​ для безопасной сортировки.​ так, что уже​Алиса​ данную проблему 3000​ – настраиваемой сортировкой.​ Excel (Столбец А,​ А до Я».​Такой способ необходимо применять​ ней многочисленных и​ Потом выберите инструмент:​и выберите цвет.​

​ столбце с содержимым,​ статья была вам​

Сортировка по цвету ячейки в Excel

​ столбце «Город» обратите​ в первую очередь​Повторно делаем щелчок правой​ диапазона». По умолчанию​Чтобы отсортировать всю таблицу​ сложно будет вернуться​: Скажите пожалуйста, как​ строк в ручную?(((​ Данный инструмент автоматическое​ Столбец B, C​Нажмите на кнопку «Добавить​ только как вспомогательный.​ сложных сортирований данных​ «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Сортировка​

​Использование гистограмм, цветовых шкал​ которое нужно отфильтровать.​ полезна

Просим вас​ внимание на стрелку​ пойдут ячейки с​

​ кнопкой мышки по​ там активна опция​ относительно одного столбца​ к изначальному ее​ убрать сортировку от​Порядковые номера у​
​ сортирует данные одновременно​ и т.д.). А​ уровень» и в​​ по разным столбцам,​ по убыванию».​ и наборов значков​В разделе​ уделить пару секунд​ вверх рядом фильтром​ цветом, а после​

​ ячейе A1 на​ «автоматически расширять выделенный​

​ выполните следующее:​ виду. В третьих,​ А до Я.​ меня есть только​

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

​ листе 3 и​ диапазон». Программа пытается​Выделите столбцы листа, которые​ если таблица содержит​

​Pelena​ толку?​ Он настолько эластичен,​ параметров в сортировку​ новые критерии. 1​ Excel? Допустим мы​ Тогда достаточно выполнить​ и организовались в​Фильтрация списка данных​во всплывающем меню​ ли она вам,​

exceltable.com>

​Как и с диапазонами,​

• Как убрать в excel зачеркивание
• Зеленый треугольник в ячейке excel как убрать
• Как в excel 2010 убрать нулевые значения
• Как в excel убрать пробелы между цифрами
• Excel убрать последний символ в ячейке excel
• Как в excel убрать автозамену на дату
• Как в excel убрать цифры после запятой
• Убрать разметку страниц в excel
• Как в excel убрать поля
• Как в excel убрать пустые ячейки
• В excel убрать все пробелы
• Как в excel сортировать по цвету

Сортировка в Excel по цвету заливки ячейки

Сортировка ячеек в Excel по цвету заливки – это одна из простейших операций, наравне с подсчётом количества ячеек, суммированием и даже фильтрацией. Не потребуются ни код VBA, ни формулы. Мы просто воспользуемся инструментом «Настраиваемая сортировка», который присутствует во всех современных версиях Excel 2013, 2010 и 2007.

1. Выделите свою таблицу или диапазон ячеек.
2. На вкладке Главная (Home) в разделе Редактирование (Editing) нажмите кнопку Сортировка и фильтр (Sort & Filter) и в появившемся меню выберите Настраиваемая сортировка (Custom Sort).
3. В диалоговом окне Сортировка (Sort) настройте параметры слева направо:
   • Укажите Столбец (Column), по которому нужно выполнить сортировку (в нашем примере это столбец Delivery);
   • В поле Сортировка (Sort On) выберите Цвет ячейки (Cell Color);
   • Установите цвет ячеек, которые должны быть вверху;
   • В крайнем правом выпадающем списке выберите Сверху (On top).
4. Чтобы добавить ещё один уровень сортировки с такими же настройками, нажмите кнопку Копировать уровень (Copy Level). Затем в столбце Порядок (Order) выберите второй по порядку цвет. Таким же образом создайте столько уровней сортировки, сколько цветов надо отсортировать в таблице.
5. Нажмите ОК и проверьте в правильном ли порядке расположились строки с данными.

В нашей таблице вверх переместились строки со значением Past Due (столбец Delivery), за ними следуют строки со значениями Due in и в конце – строки со значением Delivered. Все в точности, как мы настроили в диалоговом окне Сортировка (Sort).

Совет: Если ячейки раскрашены в большое количество различных цветов – не обязательно создавать правило для каждого. Настройте правила сортировки только для тех цветов, которыми отмечены действительно важные данные (в нашем примере это строки со значением Past Due), а все остальные оставьте как есть.

Если требуется отобрать ячейки только одного цвета, то можно воспользоваться ещё более быстрым способом. Кликните по стрелке автофильтра рядом с заголовком столбца, в котором нужно произвести сортировку. В открывшемся меню нажмите Сортировка по цвету (Sort by Color) и выберите, какого цвета ячейки должны оказаться вверху, а какие – внизу. Кстати, выбрав в этом меню пункт Пользовательская сортировка (Custom Sort), как показано на рисунке ниже, можно быстро перейти к диалоговому окну Сортировка (Sort).

Пользовательский фильтр: настройка по критериям

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

1. Откроем меню сортировки одного из столбцов и выберем в меню текстовых/числовых фильтров компонент «Настраиваемый фильтр…».
2. Откроется окно настройки. Слева находится поле выбора фильтра, справа размещаются данные, на основе которых будет работать сортировка. Фильтровать можно сразу по двум критериям – вот почему в окне находятся две пары полей.
3. К примеру, выберем фильтр «равно» в обеих строках и установим разные значения – например, 39 в одной строке и 79 в другой.
4. Список значений находится в списке, который открывается после нажатия на стрелку, и соответствует содержимому столбца, где было открыто меню фильтра. Нужно сменить выбор выполнения условий с «и» на «или», чтобы фильтр сработал, а не убрал все строки таблицы.
5. После нажатия кнопки «ОК» таблица примет новый вид. Остались только те строки, где установлена цена 39 или 79. Результат выглядит так:

Понаблюдаем за работой текстовых фильтров:

1. Для этого откроем меню фильтров в столбце с текстовыми данными и выберем любой тип фильтра – например, «начинается с…».
2. В примере использована одна строка автофильтра, но можно задействовать две.

Выбираем значение и кликаем по кнопке «ОК».

1. В результате на экране остаются две строки, начинающиеся с выбранной буквы.

EXCEL ДЛЯ «ЧАЙНИКОВ» И НЕ ТОЛЬКО

Как сделать сортировку данных в Excel

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

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

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

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

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

Включение фильтра

Для работы с настройками автофильтра, прежде всего, нужно включить фильтр. Сделать это можно двумя способами. Кликните по любой ячейке таблицы, к которой вы хотите применить фильтр. Затем, находясь во вкладке «Главная», нажмите на кнопку «Сортировка и фильтр», которая размещена в блоке инструментов «Редактирование» на ленте. В открывшемся меню, выберите пункт «Фильтр».

Читать еще:   Как сделать график выхода на работу в excel?

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

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

Сортировка и фильтр по цвету с помощью функций

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

Функция цвета заливки ячейки на VBA

Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:

Создатели Excel решили, начиная от 2007-ой версии ввести возможность сортировки данных по цвету. Для этого послужило поводом большая потребность пользователей предыдущих версий, упорядочивать данные в такой способ. Раньше реализовать сортировку данных относительно цвета можно было только с помощью создания макроса VBA. Создавалась пользовательская функция и вводилась как формула под соответствующим столбцом, по которому нужно было выполнить сортировку. Теперь такие задачи можно выполнять значительно проще и эффективнее.

Удаление или отмена сортировки в столбце

​Выберите инструмент на закладке:​ следует их аккуратно​ сортировки или отсортировать​: Я думаю, что​ применить практически для​ строки и ячейки​ 2 – «Значение»,​ по дате рождения​ с номерами строк,​Как оказалось, наибольшую премию​ стиля ячейки​щелкните​ внизу страницы. Для​ сортировку в таблице​ отменить любую сортировку,​ этот раз указываем​ как в предыдущем​ «Данные»-«Сортировка».​

​ разъединить, так как​ по другому столбцу​ Каратаев имел ввиду​ любой сортировки с​​ исходного диапазона (фрагмента​​ 3 – «От​​ (от старых к​​ чтобы таблица приняла​ получил сотрудник «Евгений​В данном примере рассмотрим​Цвет ячейки​ удобства также приводим​ с помощью команды​

​ которую вы применили​ «форматы». Так мы​ примере. Но в​В появившимся окне укажите​ для сортировки такой​

​AlexM​ потом использовать дополнительный​ любым уровнем сложности.​ таблицы), что является​ старых к новым».​ новым) в соответствии​

support.office.com>

Как в Excel фильтровать ячейки по цвету заливки

Если нужно отфильтровать ячейки в заданном столбце по цвету заливки, используйте инструмент Фильтр по цвету (Filter by Color), доступный в Excel 2010 и 2013.

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

1. Создайте вспомогательный столбец в конце таблицы или рядом со столбцом, в котором будете настраивать фильтр. Мы назовём его Filter by color.

2. Во вторую ячейку только что добавленного столбца введите формулу:

   Здесь F – это столбец, содержащий разукрашенные ячейки, и по которому нужно настроить фильтр, а GetCellColor() – пользовательская функция со следующим кодом VBA:

Function GetCellColor(xlRange As Range)
    Dim indRow, indColumn As Long
    Dim arResults()
 
    Application.Volatile
 
    If xlRange Is Nothing Then
        Set xlRange = Application.ThisCell
    End If
 
    If xlRange.Count > 1 Then
      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
       For indRow = 1 To xlRange.Rows.Count
         For indColumn = 1 To xlRange.Columns.Count
           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
         Next
       Next
     GetCellColor = arResults
    Else
     GetCellColor = xlRange.Interior.Color
    End If
End Function

1. Скопируйте эту формулу во все ячейки столбца Filter by color.
2. Примените автофильтр Excel и затем выберите нужные цвета по их кодам в выпадающем меню автофильтра.

В результате получится вот такая таблица, где показаны строки только с двумя выбранными цветами заливки:

На этом, пожалуй, всё на сегодня, благодарю за внимание!

Используем горизонтальную сортировку с помощью Power Query

Для совершения каких либо манипуляций в Power Query данные необходимо предварительно загрузить. На вкладке данные, в зависимости от типа источника данных пользуемся соответствующей командой. В нашем, простом случае, – это “Из таблицы/диапазона”.

В самом окне редактора Power Query переходим на вкладку “Добавление столбца”, команда “Настраиваемый столбец”. В диалоговом окне настройки столбца вводим функцию List.Sort(), в качестве аргументов передаем столбцы. По умолчанию функция сортируем по возрастанию, поэтому этот опциональный параметр нужно обязательно указывать, если сортировка нужна в убывающем порядке.

а вот формула для убывающего порядка

Поскольку результатом получается элемент типа список, нужно определиться с дальнейшими действиями сортировки, если нужно получить столбец со значениями, выбираем опцию “Развернуть в новые строки”, однако, если идет сортировка для большого количества данных, лучше воспользоваться опцией “Извлечь значения…”. При извлечении значений все они извлекаются в одну ячейку с определенным разделителем (ну или без). Если потом возникнет необходимость разнести значения по ячейкам сделать это можно несколькими способами, мы их рассмотрели в данном материале, однако, лучшим вариантом будет воспользоваться командой “Разделить столбец” вкладки “Главная” в самом Power Query.

Не работает фильтр даты (Формулы)

​ кнопка фильтр.и все)​​ данные: убрать объединенные,​ уровень» и в​​ нужен, достаточно десятка​ «Март», выберите пункт​ четыре — в​ текущего фильтра и​ 10″ в _з0з_​Книга содержит данные, к​​ эта возможность не​Что необходимо сделать.​Книга содержит данные, к​ более поздних версиях,​ я уже хотела​​ зная? Помогите вернуть​

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

​ которым применен фильтр,​​ если они не​​ файл переделывать!​

​ к жизни Аавтофильтр!​​ по предыдущему критерию​

excelworld.ru>

Почему не работает фильтр в эксель

Изменен тип Vinokurov Yuriy Moderator 16 декабря 2009 г. 10:02

Все ответы

Я только что попробовал проделать такое на своeй машине с Excel 2007 и у меня всё работает как положено, т.е. после фильтрации, удаления и отмены фильтрации у меня есть все данные которые были скрыты фильтром. Можете описать порядок своих действий?

сила в справедливости

не знаю как єто правильно описать, но проверить просто. В столбце «А» забиваем название 12 месяцев (январь, февраль. ), начиная с ячейки «А2» потом в столбце «В» савим цифры, напротив января — 1, март — 3, май — 2, июнь — 1, октябрь — 2. Далее применяем фильтр, потом фильтруем по столбцу «В» значение «1», выбраные данные удаляем (выделяем строки, правая кнопка мыши удалить). Потом снимаем фильтр и получаем, что удалились не только строки с значением «1» в столбце «В», а и все остальные, которые были между ними. Должно было удалится две стоки, а удалилось шесть.Вот она и проблема, в ранних версиях Excel такой проблемы не было.

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

Сазонов Илья http://www.itcommunity.ru/blogs/sie/

Та же проблема, имеет стастический характерКак правило возникает на большом числе строк порядка 70 000 ( больше >65 000)И это кстати не единственный глюк excel 2007Есть тут кто реально может этой проблемой заняться??

Сервис пак второй стоит? Там убирали некоторые проблемы больших файлов.

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

Сазонов Илья http://www.itcommunity.ru/blogs/sie-wl/

Я поняла, о чем топик-стартер пишет. У меня тоже такая фигня наблюдается. Дома всё нормально фильтруется, а на рабочем компе в офисе последние несколько месяцев ни с того ни с сего начались те же самые проблемы, которые описывает автор. У меня есть заказы доставленные и есть отменённые. Ну штук сто на странице. Фильтрую, чтоб на листе выводились только ДОСТАВЛЕННЫЕ.

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

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

ЕДИНСТВЕННОЕ, ЧЕМ ЭТО РАЗОВО ЛЕЧИТСЯ — открыл документ, отфильтровал, что нужно, далее ctrl-A нажал, выделил то, что есть на странице, затем ПРАВОЙ КНОПКОЙ МЫШИ щелкнул, выбрал пункт «фильтр», выбрал «применить повторно».

Всё, после этого фильтроваться станет так, как нужно.

После закрытия документа снова надо будет правой кнопкой «фильтр» — «применить повторно» нажимать.

Редактирование формул массива в Excel

Правила редактирования формул массива

​ Формула массива будет​ наибольших значений того​ массив, скопируйте его​ непригодный способ.​В прошлых уроках мы​ стандартным символом -​ будут отображены фигурные​ забываем для подтверждения​​ «больше», «больше»}. Схематически​​Формула массива (введенная с​

​ + Shitf +​ значений.​ или =СТОЛБЕЦ(A:E). Получите​ разделены двоеточиями. Если​ так и недостаток.​ заключит ее в​Многоячеечные формулы массива в​ выглядеть следующим образом:​ же диапазона:​

1. ​ формулу как текст,​С помощью диалогового окна​ разобрали основные понятия​ «;» (точкой с​ скобки по краям.​
2. ​ ввода нажать комбинацию​ вторую таблицу можно​ помощью сочитания CTRL+SHIFT+Enter)​ Enter» – это​
3. ​Чтобы создать двумерный массив​ массив констант ={1;2;3;4;5}.​ бы значения были​Итак, в данном уроке​
4. ​ фигурные скобки.​ Excel​Использовать в ячейке D9​Если необходимо найти наименьшие​
5. ​ а затем используйте​Выделить группу ячеек​ и сведения касаемо​ запятой).​​ Значит данная формула​​ клавиш CTRL+SHIFT+Enter. В​

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

Выделение массива в Excel

​ значения, просто замените​ ее в новом​. Для этого выделите​ массивов в Excel.​Пример 2: Снова перейдите​ выполняется в массиве.​ результате проверки каждой​Так же ее можно​ где вы хотите,​

1. ​ командой на выполнение​ следующее:​ разделены точкой с​ (в диапазоне​ многоячеечными формулами массивов​ массива в фигурные​

2. ​ Excel​​ сначала округляет значения,​​ функцию​ массиве. При громоздких​ любую ячейку, которая​​ В этом уроке​ на ячейку с​ Но что если​​ ячейки диапазона $A$1:$A$4​​ прочитать функцией:​​ чтобы функция, которая​​ (обработку данных) как​

   ​выделите в книге диапазон​​ запятой.​​A1:E1​ и разобрали небольшой​ скобки. Если Вы​​Массивы констант в Excel​​ а затем суммирует​

   ​НАИБОЛЬШИЙ​

3. ​ формулах такой подход​​ принадлежит массиву:​​ мы продолжим изучение​ формулой массива, но​ еще на этапе​

Как удалить формулу массива

​ (является ли значение​=ИНДЕКС({«больше»;»меньше»;»больше»;»больше»};2)​ обычно работает с​ массив вычислений. Тогда​ ячеек из четырех​А. Умножение векторов (столбец​), а не в​ пример. Если желаете​​ вставите скобки вручную,​​Редактирование формул массива в​

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

​ их.​на​ позволит сэкономить уйму​А затем на вкладке​ формул массива, но​ на этот раз​ создания неизвестно какой​ больше нуля) в​В примере с функцией​

​ отдельными значениями (ячейками),​ он используется в​

1. ​ столбцов и трех​ на столбец, строку​ столбце, то значения​ получить еще больше​ Excel воспримет это​
2. ​ Excel​Теперь мы можем быть​НАИМЕНЬШИЙ​ времени.​ Главная из раскрывающегося​​ с большим уклоном​​ выделите первый аргумент​ тип формул следует​ памяти компьютера создается​
3. ​ ЕСЛИ была введена​ внезапно повела себя​
4. ​ качестве аргумента функции​ строк (​​ на строку)​​ были бы разделены​ информации о массивах​

Изменение размеров формулы массива

​ выражение как обычный​Подходы к редактированию формул​ уверенными в том,​.​Если же необходимо изменить​ списка​ на их практическое​ функции целиком вместе​ применять?​ массив {ИСТИНА; ЛОЖЬ;​

​ формула массива только​ иначе и приняла​ и возвращает в​A1:D3​С помощью формулы​ точкой с запятой​ в Excel, читайте​ текст.​ массива в Excel​

​ что сумма в​Формула массива, представленная на​ расположение массива на​Найти и выделить​ применение. Итак, как​ со знаком сравнения​

​Правильное «распознавание», когда следует​ ИСТИНА; ИСТИНА}. Затем​ в одну ячейку,​ в качестве аргумента​ качестве результата вычислений​

​).​ массива умножим столбец​ {1;2;3;4;5}.​ следующие статьи:​Обратите внимание, что все​Урок подготовлен для Вас​ ячейке D9 соответствует​ рисунке ниже, позволяет​ рабочем листе, не​выберите пункт​ же изменить уже​

• ​ «>» и значением​ нажимать CTRL+SHIFT+Enter, а​
• ​ создается очередная таблица.​ поэтому в результате​
• ​ и вернула в​ таблицу (массив данных).​
• ​в активной ячейке (​
• ​ значений (​Создадим константу массива в​
• ​Знакомство с формулами массива​ ячейки диапазона D2:D6​

​ командой сайта office-guru.ru​ действительности.​​ подсчитать количество различий​

​ меняя его размерность,​

office-guru.ru>

Как в Excel фильтровать ячейки по цвету заливки

Если нужно отфильтровать ячейки в заданном столбце по цвету заливки, используйте инструмент Фильтр по цвету (Filter by Color), доступный в Excel 2010 и 2013.

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

1. Создайте вспомогательный столбец в конце таблицы или рядом со столбцом, в котором будете настраивать фильтр. Мы назовём его Filter by color.
2. Во вторую ячейку только что добавленного столбца введите формулу:

Здесь F – это столбец, содержащий разукрашенные ячейки, и по которому нужно настроить фильтр, а GetCellColor() – пользовательская функция со следующим кодом VBA:

1. Скопируйте эту формулу во все ячейки столбца Filter by color.
2. Примените автофильтр Excel и затем выберите нужные цвета по их кодам в выпадающем меню автофильтра.

В результате получится вот такая таблица, где показаны строки только с двумя выбранными цветами заливки:

На этом, пожалуй, всё на сегодня, благодарю за внимание!

Синтаксис сортировки

Синтаксис полного кода VBA Excel, применяемого для сортировки данных в таблицах и диапазонах:

1 2 3 4 5 6 7 8 9

WithExpression.Sort .SortFields.Clear .SortFields.Add Key,SortOn,Order,DataOption .SetRangeRange .Header=xlGuess,xlYes,xlNo .MatchCase=True,False .Orientation=xlTopToBottom,xlLeftToRight .Apply EndWith

Синтаксис сокращенного кода VBA Excel, применяемого для сортировки данных с параметрами по умолчанию:

1 2 3 4 5 6

WithExpression.Sort .SortFields.Clear .SortFields.Add Key .SetRangeRange .Apply EndWith

Expression – выражение, возвращающее объект Worksheet, например:

1 2 3 4

ActiveSheet Worksheets(«Лист1») ActiveWorkbook.Worksheets(«Лист1») Workbooks(«Книга1.xlsm»).Worksheets(«Лист1»)

Наложение нескольких фильтров на таблицу Эксель

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

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

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

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

Теперь посмотрите видеоролик, в котором я показываю реальный пример фильтрации данных в прайс листе более чем из 15 000 строк.