Лабораторная работа по "Программированию"

Задание №4

Сортировка, фильтрация, итоги.

Открыть файл Excel с именем «ИТ-?0?_Иванов И.И.».

Отредактировать таблицу «Товар», содержащую следующие поля:

[pic 1]

Условие:

Записей в таблице не менее 45

Для столбца «Скидка» зависящее от столбца «Полугодие» (шт, кг, л. и т.д.) самостоятельно прописать функцию ЕСЛИ через абсолютную ссылку

[pic 2][pic 3]

Столбец «Налог» прописать функцию ЕСЛИ через абсолютную ссылку.

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

Для того чтобы выполнить фильтрацию нужно:

1. Установить активную ячейку в таблицу.
2. Выполнить команду Данные – Фильтр – Автофильтр.

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

1. Скопируйте свою таблицу на новый лист с именем Фильтр 1.
2. Вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы - это будет диапазон с условиями.
3. Между диапазоном условий и исходной таблицей обязательно должна быть хотя бы одна пустая строка.
4. В верхнюю таблицу нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать «дрова» поставляемые в регион «Север» с ценой не ниже 50.

[pic 4]

1. Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно. В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий. Обратите внимание, что диапазон условий нельзя выделять "с запасом", т.е. нельзя выделять лишние пустые строки, т.к. пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка - как просьба вывести все данные без разбора.

Задание:

1. На листе Фильтр 2 отобрать все записи по одной из групп (газ, бензин), отображающие данные налога 7% при выручке, например не более 1000 рублей.
2. На листе Фильтр 3 отобрать все записи по наименованию одного из товаров превышающее количество, например 500 шт при скидке не ниже 450 руб.
3. Скопируйте свою основную таблицу на лист Итог 1. Выполните сортировку по региону, а внутри региона – по товару.

Для того чтобы произвести многоуровневую сортировку нужно:

1. Установить активную ячейку в столбец, данные которого подлежат сортировке.
2. Выполнить команду Данные – Сортировка на панели инструментов Стандартная.
3. В открывшемся диалоговом окне Сортировка диапазона выбрать последовательность столбцов сортировки и другие необходимые параметры. Для появления второй строки в окне Сортировка нажмите кнопку Добавить уровень. Нажать Ок.

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

Для этого:

• Выделите диапазон. Данные - Промежуточный итог
• в окне промежуточные итоги поставить флажки

[pic 5]

1. Изучите полученную структуру и формулы подведения промежуточных итогов
2. Скопируйте свою основную таблицу на лист Итог 2 и лист Итог 3. Самостоятельно подвести промежуточные итоги, данные выбрать произвольно, в одном из итогов использовать операцию Минимум или Максимум.
3. Выделите любую ячейку своей основной таблицы и выполните команду Вставка - Сводная таблица

• На первом шаге нужно указать источник данных для создания сводной таблицы.
• Так, как одна из ячеек списка была выделена, по умолчанию Excel принимает в качестве диапазона весь список. Укажите, что поместить отчет нужно на Новый лист (переименовать в Свод 1). Нажмите ОК. Появляется Макет для определения структуры сводной таблицы. В диалоговом окне расположен шаблон, содержащий четыре области: Строка - для использования данных поля, расположенного в этой области, в качестве заголовков строк; столбец – Для использования данных поля, расположенных в этой области, в качестве заголовков столбцов; данные – для применения итоговой функции к значениям поля, расположенного в этой области, в ячейках сводной таблицы; страница – для обеспечения возможности вывода данных сводной таблицы, относящихся только к полю, расположенному в этой области. В этих областях нужно разместить названия полей исходного списка. Эти поля размещены справа от шаблона.
• Перетащите мышью поле «Регион» в область строк, а поле «Скидка» - в область данных.

1. Самостоятельно сделать сводные таблицы на листе Свод 2 и листе Свод 3. Используйте итоговую функцию Среднее или Минимум.