Тема: Фильтрация списков.
Цель: Изучить средства фильтрации списков Excel автофильтрами.
Задание на лабораторную работу. Последовательно выполните на одном листе фильтрацию списка, отвечая на поставленные вопросы. Ответы приведены.
Порядок выполнения лабораторной работы
Вставить новый лист: «13АФильтр»
Сделать копию листа 4Проверка на лист 13
Выделить любую из ячеек с данными
Запустить команду Автофильтр – командой (Данные – Фильтр)
АФ1. Отобразить записи по продажам капусты. Для этого необходимо раскрыть список фильтров поля Товар и выбрать «Капуста».
Вернуть нормальный вид командой: «Данные – Фильтр
А
Ф2.
Отобразить покупки одного клиента -
Оптова. Для этого открыть список клиентов
и выбрать Оптова.
АФ2. Отобразить тринадцатую продажу. Выбрать номер 13 в столбце №
АФ4. Отобразить продажи за 29 декабря. Выбрать 29 декабря из списка «Дата»
АФ5. Продажи весом 10 кг.
.
АФ6. Отобразить 10 наиболее крупных по стоимости разовых покупок.
Цена – Числовые форматы – Первые 10
АФ7. Выбрать первые 10 продаж за отчетный период. Аналогично предыдущему, только фильтр проводить по дате, а вместо «наибольших» выбрать «наименьших» во 2-м поле.
АФ8. Отобразить 10% наибольших по весу покупок. Аналогично предыдущему. Вместо «элементов списка» выбрать «% от количества элементов».
АФ9. Три продажи по самой высокой отпускной цене. Фильтр по цене. В 1-м поле выбрать 3.
АФ10. 20% продаж самого дешевого товара. В 1 поле выбрать «20», в 3-м – «% от количества элементов»
АФ11. Половину наибольших по выручке продаж. Фильтр по выручке. «50 наибольших % от количества элементов»
АФ12. Отобразите первую половину продаж за отчетный период. Аналогично предыдущему.
АФ13. Какой покупатель сделал самую большую по стоимости покупку? В списке автофильтра по выручке выбрать наибольшее значение.
АФ14. У какого товара была минимальная отпускная цена? Аналогично предыдущему.
АФ15. Сведения о последней продаже (без прокрутки). Что купил последний покупатель? Выбрать «первые 10», «1 наибольших элементов списка»
Наложенные автофильтры по двум и более полям. Отменить все фильтры можно из верхнего меню Данные – Фильтр – Отобразить все.
АФ16. Отобразить продажи одного конкретного товара одному конкретному покупателю капусты, Оптову. Вначале выбрать фильтра товара «Капуста», затем – фильтр клиента «Оптов»
АФ17. Продажи картофеля Кукину по 11 руб. Аналогично предыдущему, только вначале проводить фильтр по цене («11»), а затем по всем остальным пунктам – товару и клиенту.
АФ18. 10-килограммовые продажи картофеля Оптову. Аналогично предыдущему.
АФ19. Продажи огурцов в первой половине продаж за отчетный период. Вначале провести фильтрование по «50 наименьших % от количества элементов», а затем – по товару «огурцы»
АФ20. Продажи капусты из 20 самых больших по весу продаж. Аналогично предыдущему, только фильтрование проводить вначале по «20 наибольших элементов списка» по столбцу Количество, а затем по капусте.
П
ользовательский
автофильтр (далее ПАФ) дает возможность
задать условие фильтрации с операциями
равно, меньше, меньше или равно, больше,
больше или равно, не равно, с операциями
над текстовыми значениями «начинается
с», «не начинается с», «заканчивается
на», «не заканчивается на», «содержит»,
«не содержит», возможность использовать
текстовые маски с шаблонами подстановки
? и *, задавать до двух условий на одно
поле, соединять их логическими операциями
И или ИЛИ. Например, решать задачи на
выбор двух значений (ИЛИ), исключение
двух значений (И), выбор из диапазона
(И), вне диапазона (ИЛИ). Для этого в списке
фильтров выбранного поля
необходимо выбрать пункт Условие и в
появившемся диалоге Пользовательский
автофильтр задать операцию фильтрации
и значение ограничения
ПАФ1. Крупные продажи на сумму более 250 руб. В списке столбца выручка выбрать «Условие». В первом левое поле выбрать из списка «больше», в первое правое ввести «250». Ок.
ПАФ2. Продажи за первую неделю, с 1 по 5 декабря, см. календарь. Аналогично предыдущему. Только необходимо использовать первую и вторую строку, связанные логическим оператором «И»
ПАФ3. Крупные продажи капусты на суммы 100 руб. и более.
ПАФ4. Продажи картофеля в последнюю, предпраздничную неделю месяца.
ПАФ5. Отобразите продажи весом не более 5 кг, но на сумму не менее 100 руб.
ПАФ6. Продажи клиентам на К товаров на букву К.
ПАФ7. 27 декабря покупатель с простой русской фамилией на «-ов» забыл сдачу. Вычислите его.
ПАФ8. В магазине обнаружен мешок с капустой, на полустершейся надписи ясно различается только третья буква – «т». Чей мешок?
ПАФ9. В понедельник утром сделан заказ и не выкуплен в течение недели. На подписи в заказе разборчивы только две буквы «р» и «в». Кто заказчик?
Бистров
ПАФ10. Продавец Картавый не выговаривает «р», поэтому ему поручено отпускать все товары, в названии которых нет р, всем клиентам без р. Отобразите сделки Картавого.
ПАФ11. Составьте список продаж огурцов и помидоров, больше 5 кг.
ПАФ12. Все продажи картофеля кроме как двум постоянным клиентам Торбину и Кукину.
ПАФ13. Продажи картофеля весом не более 3 кг.
ПАФ14. Какие из трех самых больших по выручке продаж приходятся на последнюю неделю отчетного периода.
ПАФ15. Продажи на сумму более 200 рублей, за исключением последней предпраздничной недели месяца.
ПАФ16. Продажи капусты на сумму от 100 до 200 рублей включительно.
ПАФ17. Продажи за вторую декаду месяца.
ПАФ18. Продажи капусты за первую и третью декады месяца.
ПАФ19. Продажи за последние два понедельника отчетного периода.
ПАФ20. Продажи капусты за первую декаду месяца, исключая понедельники.
Форма отчетности по лабораторной работе в Excel 7. На листе 13АФильтр проверяется любой из автофильтров 1- 20 и один из пользовательских автофильтров 1-20.
Лабораторная работа в Excel 8.