Какие типы фильтров существуют в табличном процессоре excel

Табличный процессор EXCEL. Списки и средства их обработки. Фильтры, виды фильтров и их применение

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

Список состоит из трех структурных элементов:

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

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

Рабочий лист должен содержать только один список, т. к. некоторые операции, например, фильтрование, могут работать только с одним списком.
Если на рабочем листе кроме списка необходимо хранить и другие данные, список необходимо отделить пустой строкой и пустым столбцом. Причем лучше не размещать другие данные слева и справа от списка, иначе они могут быть скрыты во время фильтрации списка.
Заглавную строку лучше дополнительно отформатировать, чтобы выделить среди строк списка (использовать форматы, отличные от тех, которые применены к данным списка).
Метки столбцов могут содержать до 255 символов.
Не следует отделять заглавную строку от записей пустыми строками или строкой, содержащей линию из символа «дефис».
Список должен быть составлен так, чтобы столбец содержал во всех строках однотипные значения.
При вводе значения поля нельзя вставлять ведущие пробелы, это может привести к проблемам при поиске и сортировке.
В списках можно использовать формулы. Списки могут обрабатываться, как обычные таблицы.

То есть для редактирования данных могут применяться все рассмотренные ранее возможности. Однако значительно упростить работу с записями списка позволяет команда Форма меню Данные. Использование формы данных позволяет:

добавить записи в список;
организовать поиск записей в списке;
редактировать данные записи;
удалять записи из списка.

Диалоговое окно команды Форма содержит шаблон

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

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

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

Перемещение между полями в шаблоне формы осуществляется нажатием клавиши Tab или установкой мышью места ввода в нужное поле ввода.

Добавляемая с помощью формы данных запись всегда вставляется в конец списка. Если необходимо добавить запись в середину списка, необходимо вставить пустую строку в нужное место командой Строки меню Вставка (вставка всегда осуществляется перед выделенной строкой), а затем отредактировать пустую строку командой Форма меню -Данные.

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

Существуют три способа для поиска записей в списке:

с помощью командных кнопок Далее/Назад;
с помощью полосы прокрутки;
с помощью командной кнопки Критерии.

При задании критериев можно использовать операторы сравнения.

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

* — для указания произвольного количества символов;
? — для указания одного символа.

Сортировка списков — это переупорядочивание одного или более столбцов. Сортировка выполняется с помощью одноименной команды меню Данные.

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

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

Выделенное подмножество списка можно редактировать, форматировать, печатать, использовать для построения диаграмм.

Excel содержит два варианта фильтрации: автофильтр и усиленный фильтр. Автофильтр осуществляет быструю фильтрацию списка в соответствии с содержимым ячеек или в соответствии с простым критерием поиска. Активизация автофильтра осуществляется командой Фильтр — Автофильтр меню Данные (указатель должен быть установлен внутри области списка). Заглавная строка списка в режиме автофильтра содержит в каждом столбце кнопку со стрелкой. Щелчок раскрывает списки, элементы которого участвуют в формировании критерия. Каждое поле (столбец) может использоваться в качестве критерия. Список содержит следующие элементы.

Все — будут выбраны все записи.
Первые 10

Процентное — задается доля выводимых элементов, имеющих наибольшие или наименьшие значения из всего списка.

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

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

Установленный фильтр можно удалить.

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

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

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

Фильтрация списка с помощью усиленного фильтра выполняется командой Фильтр — Расширенный фильтр меню Данные.

При формировании критерия отбора расширенного фильтра возможны следующие варианты.

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

2. Критерий отбора содержит несколько условий, накладываемых на несколько столбцов (полей) одновременно. Здесь возможны следующие варианты:

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

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

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

28. Табличный процессор EXCEL. Создание сводных таблиц, формирование общих и промежуточных итогов. Консолидация данных.

Excel содержит средства формирования сводной информации для проведения анализа данных. Сводная информация может быть получена:

объединением данных промежуточных итогов;
методом консолидации;
формированием сводных таблиц. Простейший способ получения итогов — с помощью команды Итоги меню Данные.

Для выполнения этой команды необходимо:

представить данные в виде списка;
командой Сортировка меню Данные упорядочить записи списка в соответствии со значениями того поля, по которому будут подводиться промежуточные итоги;
установить указатель на ячейку списка;
выполнить команду Итоги меню Данные;
в диалоговом окне Промежуточные итоги задать нужные параметры.

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

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

Консолидация — это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В Excel предусмотрено несколько способов консолидации данных:

консолидация данных с помощью трехмерных ссылок;
консолидация данных по расположению;
консолидация данных по категориям.

Первый способ наиболее простой. Он позволяет объединить данные консолидируемых областей формулами. Технология выполнения консолидации с помощью трехмерных ссылок такова:

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

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

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

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

Сводная таблица — это таблица, предназначенная для более наглядного представления и анализа данных из существующих списков и таблиц.

Сводная таблица может быть создана:

на основе данных любой таблицы или области таблицы рабочего листа;
на основе данных нескольких таблиц, полученных в результате консолидации данных;
на основе данных сводной таблицы;
на основе данных, находящихся во внешнем источнике данных Microsoft Access, FoxPro, dBase и т. д.

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

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

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

выполнить команду Обновить данные меню Данные;
выполнить команду Обновить данные контекстного меню.

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

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

Новое средство Excel — автоотображение — позволяет отображать в поле только несколько наибольших или наименьших элементов, основываясь на значениях в области данных таблицы.

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

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

29. Табличный процессор EXCEL. Средства анализа данных: подбор параметров, сценарии, поиск решений.

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

решить нужную задачу с каким-либо начальным значением параметра;
выбрать команду Подбор параметра меню Сервис;
в окне диалога Подбор параметра в поле Установить в ячейке задать абсолютную ссылку на ячейку, содержащую расчетную формулу, а в поле Значение — то значение, которое следует получить в качестве результата формулы;
в поле Изменяя значение ячейки ввести ссылку на ячейку с параметром;
нажать кнопку ОК или клавишу Enter, на экране появится окно диалога Результат подбора параметра;
для сохранения найденного значения нажать кнопку ОК. Для восстановления значения, которое было в ячейке с параметром до использования команды Подбор параметра нажать кнопку Отмена.

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

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

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

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

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

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

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

Последний шаг определения поиска решений — задание ограничений. Он не является обязательным. Чтобы задать ограничения, следует в окне Поиск решения нажать кнопку Добавить и заполнить окно диалога Добавление ограничений. Ограничение состоит из трех компонентов: ссылки на ячейку, оператора сравнения и значения ограничения. В левой части от оператора сравнения кроме ссылки на ячейку может также задаваться ссылка на диапазон. В правой части может задаваться диапазон (той же размерности, что и в левой части), ссылка на ячейку или константное значение.

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

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

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

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

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

Отчет по результатам содержит целевую ячейку, список изменяемых ячеек и ограничений. В этом отчете для каждого ограничения выводятся «статус» и «разница». Разница — это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, заданным в правой части формулы ограничения. Статус может принимать три состояния: «Связанное» (ограничение, для которого значение разницы равно 0), «Не связанное» (ограничение, которое было выполнено с ненулевым значением разницы) и «Не выполненное».

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

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

studopedia.org — Студопедия.Орг — 2014-2022 год. Студопедия не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования (0.007 с) .

Лекция: Табличный процессор EXCEL. Списки и средства их обработки. Фильтры, виды фильтров и их применение.

Список состоит из трех структурных элементов:

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

Рабочий лист должен содержать только один список, т. к. некоторые операции, например, фильтрование, могут работать только с одним списком.
Если на рабочем листе кроме списка необходимо хранить и другие данные, список необходимо отделить пустой строкой и пустым столбцом. Причем лучше не размещать другие данные слева и справа от списка, иначе они могут быть скрыты во время фильтрации списка.
Заглавную строку лучше дополнительно отформатировать, чтобы выделить среди строк списка (использовать форматы, отличные от тех, которые применены к данным списка).
Метки столбцов могут содержать до 255 символов.
Не следует отделять заглавную строку от записей пустыми строками или строкой, содержащей линию из символа «дефис».
Список должен быть составлен так, чтобы столбец содержал во всех строках однотипные значения.
При вводе значения поля нельзя вставлять ведущие пробелы, это может привести к проблемам при поиске и сортировке.
В списках можно использовать формулы. Списки могут обрабатываться, как обычные таблицы.

добавить записи в список;
организовать поиск записей в списке;
редактировать данные записи;
удалять записи из списка.

Диалоговое окно команды Форма содержит шаблон

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

Существуют три способа для поиска записей в списке:

с помощью командных кнопок Далее/Назад;
с помощью полосы прокрутки;
с помощью командной кнопки Критерии.

При задании критериев можно использовать операторы сравнения.

* — для указания произвольного количества символов;
? — для указания одного символа.

Все — будут выбраны все записи.
Первые 10

Процентное — задается доля выводимых элементов, имеющих наибольшие или наименьшие значения из всего списка.

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

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

Установленный фильтр можно удалить.

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

При формировании критерия отбора расширенного фильтра возможны следующие варианты.

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

объединением данных промежуточных итогов;
методом консолидации;
формированием сводных таблиц. Простейший способ получения итогов — с помощью команды Итоги меню Данные.

Для выполнения этой команды необходимо:

представить данные в виде списка;
командой Сортировка меню Данные упорядочить записи списка в соответствии со значениями того поля, по которому будут подводиться промежуточные итоги;
установить указатель на ячейку списка;
выполнить команду Итоги меню Данные;
в диалоговом окне Промежуточные итоги задать нужные параметры.

консолидация данных с помощью трехмерных ссылок;
консолидация данных по расположению;
консолидация данных по категориям.

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

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

Сводная таблица может быть создана:

на основе данных любой таблицы или области таблицы рабочего листа;
на основе данных нескольких таблиц, полученных в результате консолидации данных;
на основе данных сводной таблицы;
на основе данных, находящихся во внешнем источнике данных Microsoft Access, FoxPro, dBase и т. д.

выполнить команду Обновить данные меню Данные;
выполнить команду Обновить данные контекстного меню.

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

29. Табличный процессор EXCEL. Средства анализа данных: подбор параметров, сценарии, поиск решений.

решить нужную задачу с каким-либо начальным значением параметра;
выбрать команду Подбор параметра меню Сервис;
в окне диалога Подбор параметра в поле Установить в ячейке задать абсолютную ссылку на ячейку, содержащую расчетную формулу, а в поле Значение — то значение, которое следует получить в качестве результата формулы;
в поле Изменяя значение ячейки ввести ссылку на ячейку с параметром;
нажать кнопку ОК или клавишу Enter, на экране появится окно диалога Результат подбора параметра;
для сохранения найденного значения нажать кнопку ОК. Для восстановления значения, которое было в ячейке с параметром до использования команды Подбор параметра нажать кнопку Отмена.

Использование фильтров

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

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

Фильтрацию данных можно выполнять с помощью Автофильтра и Расширенного фильтра. Автофильтр применяется для простых условий отбора; Расширенный фильтр — для более сложных условий отбора.

Работа с автофильтром. Первоначально следует выделить любую ячейку внутри списка. Затем в меню Данные — Сортировка и фильтр выбрать команду Фильтр. MS Ехсе1 выведет рядом с каждым заголовком столбцов кнопки Автофильтров в виде стрелок. После щелчка по кнопке Автофильтра откроется список команд с помощью, которых можно задать условия отбора данных.

Команда Первые 10 позволяет найти заданное число или процент наибольших или наименьших элементов в списка.

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

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

Отменить автофильтр можно двумя способами: выбрать команду Все в раскрывающемся списке Автофильтра, либо с помощью команды Данные — Фильтр — Отобразить все.

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

Работа с Расширенным фильтром. Расширенный фильтр предоставляет следующие возможности:

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

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

Порядок создания Расширенного фильтра:

· создать диапазон критериев;
· в меню Данные выбрать команду ;
· в диалоговом окне задать необходимые параметры (исходный диапазон, диапазон условий и другие).

Блок критериев (диапазон условий) — это специально отведенная область листа рабочей книги, отвечающая следующему требованию:

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

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

· условия на одной строке в разных столбцах считаются соединенными логическим оператором И;
· условия на разных строках считаются соединенными логическим оператором ИЛИ;
· пустая ячейка в диапазоне критериев означает любое значение для соответствующего столбца;
· в Расширенный фильтр нельзя включать пустые строки.

Типы критериев. Элементы, которые указываются в диапазоне критериев, можно разделить на текстовые или числовые критерии. При фильтрации используется сравнение с числом или строкой с помощью операторов сравнения: =, < >, >, <, >=, <=. Например, <500, > К — слова, начинающиеся на Л до Я, <> С — все слова, кроме слов, начинающихся на С. В критериях допускается использование символов * и ?. В текстовых сравнениях не различаются строчные и прописные буквы. Например, Сильный, сильный.

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

В Расширенном фильтре можно использовать любое количество вычисляемых критериев, а также сочетать вычисляемые и не вычисляемые критерии.

Правила создания блока критериев с простыми (не вычисляемыми) условиями:

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

Правила создания блока критериев с вычисляемыми условиями:

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

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

При каждом выполнении команды Расширенный фильтр Ехсе1 просматривает полный список, а не текущее множество ранее отфильтрованных строк. Поэтому необязательно использовать команду Показать все перед изменением фильтра.

Какие виды фильтров существуют в excel

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

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

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

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

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

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

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

Срезы

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

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

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

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

Конструктор

В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.

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

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

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

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

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

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

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

В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].

Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.
Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
Указать при необходимости место с результатами в поле Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.
Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].

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

Многие пользователи ПК хорошо знакомы с пакетом продуктов для работы с различного рода документами под названием Microsoft Office. Среди программ этой компании есть MS Excel. Данная утилита предназначена для работы с электронными таблицами.

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

Что это за функция? Описание

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

К примеру, если у нас есть электронная таблица со сведениями обо всех учениках школы (рост, вес, класс, пол и т. п.), то мы с легкостью сможем выделить среди них, скажем, всех мальчиков с ростом 160 из 8-го класса. Сделать это можно, используя функцию «Расширенный фильтр» в Excel. О ней мы и будем детально рассказывать далее.

Что значит автофильтр?

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

Как делать правильно?

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

Инструкция по расширенной фильтрации электронной таблицы:

Необходимо создать место выше основной таблицы. Там и будут располагаться результаты фильтрации. Должно быть достаточное количества места для готовой таблицы. Также требуется еще одна строка. Она будет разделять отфильтрованную таблицу от основной.
В самую первую строку освобожденного места скопировать всю шапку (названия колонок) основной таблицы.
Ввести необходимые данные для фильтрации в нужный столбец. Отметим, что запись должна выглядеть следующим образом: = «= фильтруемое значение».
Теперь необходимо пройти в раздел «Данные». В области фильтрации (значок в виде воронки) выбрать «Дополнительно» (находится в конце правого списка от соответствующего знака).
Далее во всплывшем окошке нужно ввести параметры расширенного фильтра в Excel. «Диапазон условий» и «Исходный диапазон» заполняются автоматически, если была выделена ячейка начала рабочей таблицы. Иначе их придется вводить самостоятельно.
Нажать на Ок. Произойдет выход из настроек параметров расширенной фильтрации.

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

Работа с расширенным фильтром в «Экселе»

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

Для этого необходимо:

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

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

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

Расширенная фильтрация. Основные правила использования при работе «Экселе»

Критериями отбора называются результаты исходной формулы.
Результатом могут быть только два значения: «ИСТИНА» или «ЛОЖЬ».
При помощи абсолютных ссылок указывается исходный диапазон фильтруемой таблицы.
В результатах формулы будут показаны только те строки, которые получают по итогу значение «ИСТИНА». Значения строк, которые получили по итогу формулы «ЛОЖЬ», не будут высвечиваться.

При помощи этих правил можно создавать любые формулы с допустимыми операндами и значениями.

Пример в «Экселе 2010»

Рассмотрим пример расширенного фильтра в Excel 2010 и использования в нем формул. К примеру, разграничим значения какого-нибудь столбца с числовыми данными по результату среднего значения (больше или меньше).

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

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

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

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

Внимание! При работе записи, которые не прошли критерий фильтрации, не удаляются. Они просто не показываются (не отображаются). Расширенная фильтрация предназначена для того, чтобы можно было скопировать результат и вставить его куда-нибудь в другое место. После этого необходимо нажать на «Очистить» в разделе «Фильтрация и сортировка». Тогда ваша исходная таблица вернется на экран.

Автофильтр. Пример использования

Небольшое заключение

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

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

Инструкция по созданию и использованию фильтов данных в Excel

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

Видео по фильтрации и сортировке данных в Excel

Виды фильтров в Excel

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

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

Сортировка по возрастанию или по убыванию.
«Все» — Excel покажет (восстановит) все строки.
«Первые 10» — Excel отобразит первые 10 записей. При выборе этого пункта откроется новое окно, в котором можно указать число записей, выбрать, какие из них показывать (наибольшие или наименьшие), а также установить ограничение на количество отображаемых записей.
«Условие» — здесь пользователь может самостоятельно создать 2 критерия отбора данных, объединяя их простыми операторами И, ИЛИ.
Любой из элементов. Здесь можно выбрать любое значение, которое находится в колонке. Например, если столбец состоит из наименований товаров, то в перечне элементов будут указаны абсолютно все товары. Пользователь может указать любой из них.
«Пустые» и «Непустые» — Excel отобразит пустые (или непустые) ячейки. Данная опция появляется только в том случае, если в столбце есть незаполненные поля.

Пример создания автофильтра данных Excel

Чтобы удалить фильтр из одной колонки, нужно кликнуть в перечне элементов на пункт «Все». Если нужно отменить его для всей таблицы, необходимо выбрать в панели меню пункты «Данные — Фильтр — Отобразить все». Удаление автофильтра осуществляется так же, как и его запуск.

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

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

Второй вариант фильтрации предоставляет пользователю больше возможностей для отбора необходимых данных.Чтобы запустить расширенную опцию, необходимо создать копию заголовков таблицы (т.е. просто скопировать шапку). Это будет диапазон условий. Затем надо заполнить этот диапазон критериями отбора. Но тут важно придерживаться правил: если нужно, чтобы значения отбирались по двум параметрам (к примеру, фамилия студента и оценка), то условия записываются в одну строку; если же критерии будут отбираться в режиме «ИЛИ» (марка машины или объем двигателя), тогда они записываются в разные строки.

Допустим, есть таблица с 2 колонками — наименование товара и количество. Всего товаров 3 — бананы, апельсины, мандарины, а количество — 10, 20 и 15 штук соответственно. После того как будет скопирована шапка, можно создать условие, например, показать товары, количество которых меньше или равно 15. То есть под скопированной шапкой в колонке «Кол-во» надо написать
Отблагодари меня, поделись ссылкой с друзьями в социальных сетях:

2 способа фильтрации данных. Автофильтр и расширенный фильтр в Excel.

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

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

1) Работает только с неразрывным диапазоном. Два разных списка на одном листе отфильтровать уже не получится.

2) Самая верхняя строчка таблица автоматически назначается заголовком и в фильтрации не участвует.

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

Практическое применение в работе: например, вы работаете по этому списку, чтобы найти ошибку или проверить данные. После применения автофильтра можно поочередно проходить всю таблицу, последовательно отмечая те данные, которые уже просмотрены. Кнопками «Очистить» и «Применить повторно» определяется вид таблицы после применения условий. Потом, после окончания работы с таблицей можно вернуть шрифты обратно в первоначальный вид, не меняя самих данных. Кстати, некоторых смущает факт пропадания всех записей в таблице после применения каких-либо условий. Ну что ж, посмотрите внимательней, вы задали условия, при которых нет записей, удовлетворяющих этим условиям. Факт того, что таблица отфильтрована – выделение синим цветом номеров строк таблицы.

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

1) Задает столько условий, сколько необходимо.

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

3) Позволяет копировать результат фильтра в отдельное место, не трогая основной массив.

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

1) ‘L*’ – ячейки начинающиеся с L

2) ‘>5’ — данные больше 5

Если вы удаляете из отфильтрованной таблицы строки, то они удалятся, не прихватывая с собой соседей. Т.е. если таблица отфильтрована и показывает строчки с 26-29 и 31-25, выделение всех строк и их удаление не повлечет удаление строчки 30. Это удобно, лично я часто пользуюсь этим при написании макросов. Какое преимущество это дает – часто нам достаются таблицы, которые надо привести в рабочий вид, т.е. удалить, например пустые строки. Что мы делаем: применяем фильтр к таблице, показывая только те строки, которые нам не нужны, затем удаляем всю таблицу, включая заголовок. Удаляются ненужные строки и заголовок, при этом таблица не имеет пробелов и составляет единый диапазон. А строку заголовков можно добавить простой операций копирования из загодя заготовленной области. Почему это важно при написании макросов? Неизвестно, с какой строки начинаются нежелательные данные и непонятно, с какой строки их начать удалять, удаление всей таблицы помогает быстро решить эту проблему.

Фильтры, виды фильтров и их применение;

Средства защиты данных

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

27.Табличный процессор Эксель. Списки и средства их обработки. Фильтры, виды фильтров и их применение.

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

Список состоит из трех структурных элементов:

1) Заглавная строка

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

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

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

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

Сортировка списков – это переупорядочивание одного или более столбцов.

По умолчанию, списки сортируются в алфавитном порядке.

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

Excel содержит два варианта фильтрации: автофильтр и усиленный фильтр.

1.Автофильтр осуществляет быструю фильтрацию списка в соответствии с содержимым ячеек или в соответствии с простым критерием поиска.

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

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

Фильтрация списка с помощью усиленного фильтра выполняется командой Фильтр — Расширенный фильтр меню ДАННЫЕ

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

Создание сводных таблиц:

Сводная таблица – это таблица, предназначенная для более наглядного представления и анализа данных из существующих списков и таблиц.

Сводная таблица может быть создана:

— на основе данных любой таблицы или области таблицы рабочего листа;

— на основе данных нескольких таблиц, полученных в результате консолидации данных;

— на основе данных сводной таблицы;

— на основе данных, находящихся во внешнем источнике данных Microsoft Access, FoxPro, dBase и т.д.

Для создания сводной таблицы необходимо выделить источник данных и запустить программу Мастер сводных таблиц. Перед построением сводной таблицы из источника данных обязательно должны быть убраны промежуточные итоги и наложенные фильтры. Запустить программу Мастер сводных таблиц можно путем выбора команды Сводная таблица меню ДАННЫЕ или нажатием кнопки Мастер сводных таблиц инструментальной панели Сводные таблицы.

Чтобы Excel автоматически обновлял сводную таблицу при каждом ее открытии, следует выполнить команду Сводная таблица — Параметры на панели инструментов Сводные таблицы. В появившемся диалоговом окне установить флажок Обновить при открытии.

Формирование общих и промежуточных итогов:

Простейший способ получения итогов – с помощью команды Итоги меню ДАННЫЕ. Для выполнения этой команды необходимо:

— представить данные в виде списка;

— командой Сортировка меню ДАННЫЕ упорядочить записи списка в соответствии со значениями того поля, по которому будут подводиться промежуточные итоги;

— установить указатель на ячейку списка;

— выполнить команду Итоги меню ДАННЫЕ;

— в диалоговом окне Промежуточные итоги задать нужные параметры.

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

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

Консолидация– это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В Excel предусмотрено несколько способов консолидации данных:

— консолидация данных с помощью трехмерных ссылок;

— консолидация данных по расположению;

консолидация данных по категориям.

Первый способ наиболее простой. Он позволяет объединить данные консолидируемых областей формулами.

Консолидация данных по расположению используется, если консолидируемые данные находятся в одном и том же месте разных листов и размещены в одном и том же порядке.

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

29.Табличный процессор Эксель. Средства анализа данных: подбор параметров, сценарии, поиск решений.

1.Команда Подбор параметраменю СЕРВИС позволяет определить неизвестное значение (параметр), которое будет давать желаемый результат.

По умолчанию команда Подбор параметра прекращает вычисления, когда выполняется 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения

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

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

После заполнения диалогового окна Поиск решения следует нажать кнопку Выполнить. При нахождении оптимального решения на экран выводится диалоговое окно Результаты поиска решения.

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

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

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

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

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

Какие типы фильтров существуют в табличном процессоре excel

Табличный процессор EXCEL. Списки и средства их обработки. Фильтры, виды фильтров и их применение

Лекция: Табличный процессор EXCEL. Списки и средства их обработки. Фильтры, виды фильтров и их применение.

Использование фильтров

Какие виды фильтров существуют в excel

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

Срезы

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

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

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

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

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

Что это за функция? Описание

Что значит автофильтр?

Как делать правильно?

Работа с расширенным фильтром в &#171;Экселе&#187;

Расширенная фильтрация. Основные правила использования при работе &#171;Экселе&#187;

Пример в &#171;Экселе 2010&#187;

Автофильтр. Пример использования

Небольшое заключение

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

Инструкция по созданию и использованию фильтов данных в Excel

Видео по фильтрации и сортировке данных в Excel

Виды фильтров в Excel

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

2 способа фильтрации данных. Автофильтр и расширенный фильтр в Excel.

Фильтры, виды фильтров и их применение;

Добавить комментарий Отменить ответ

Работа с расширенным фильтром в «Экселе»

Расширенная фильтрация. Основные правила использования при работе «Экселе»

Пример в «Экселе 2010»