Тема 4.9. Сортировка и фильтрация данных. Связь с листами.

↑

▷ Содержание

⇐ ПредыдущаяСтр 19 из 22Следующая ⇒

Практическая работа №14

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

1. выбрать всех сотрудников из отдела ИТР со стажем работы на данном предприятии от 15 и более лет;

2. всех плотников из отдела Рабочий;

3. все сотрудников из отдела Администрация с общим стажем работы от 20 и более лет;

4. всех сотрудников, имеющих детей.

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

Задание 2.

· Переименуйте Лист 2 в Базу;

· Введите в ячейку первой строки названия полей базы;

· Заполните базу через команду Форма меню Данные (10 записей);

С помощью меню Данные – Форма выполните следующие операции:

1. Просмотреть все записи;

2. Добавить две новые записи;

3. Удалить одну любую запись;

4. Установите для базы Автофильтр;

5. Выбрать все записи, в которых значение поля Возраст больше 35;

6. Выбрать все записи, в которых значение поля Оклад больше 3000;

7. Скорректировать все записи, в которых значение поля Оклад меньше 4000 (увеличить оклад);

8. Выбрать все записи, в которых значение поля Телефон непустое;

9. Выбрать все записи, в которых поле Фамилия начинается на букву «И»;

10. Выбрать все записи, в которых значение поля Оклад больше 3000, но меньше 7000;

11. Выбрать все записи, в которых значение поля Оклад меньше 5000, а значение поля Возраст больше 20, но меньше 40;

12. Уберите Автофильтр.

Задание 3. Создать и заполнить предложенную таблицу. Лист переименовать в Автофильтр.

1. Выполнить сложную сортировку (Данные – Сортировка). В первую очередь Сортировать по Лесник, затем по Посадка, в последнюю очередь по Вид.

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

· Выбрать посадки из села Красное;

· Выбрать лесников, фамилии которых начинаются с буквы «К»;

· Выбрать виды деревьев, возраст которых больше 15 и меньше 40 лет.

3. Вставить новый лист и переименовать его в Расширенный.

a. Скопировать таблицу Учет посадок лесничества с листа Автофильтр на лист Расширенный.

b. Скопировать шапку таблицы на одну строку ниже основной таблицы.

c. Ниже скопированной шапки выполнить следующие условия расширенного фильтра:

· Выбрать лесников, у которых имеются посадки площадью 30 кв.м. и более, и фамилии которых начинаются на «П» и «Н»;

· Выбрать виды деревьев, возраст которых не превышает 20 лет, и их названия заканчиваются на «а».

d. Выделить основную таблицу и в меню Данные выбрать команду Фильтр, а затем Расширенный фильтр.

4. Вставить новый лист и переименовать его в Статистика.

a. Скопировать таблицу Учет посадок лесничества с листа Автофильтр на лист Статистика.

b. Подсчитать количество посадок «молодняков» - до 20 лет и «зрелых» - от 20 и более лет. (Вставка – Функция – Статистические – СЧЕТЕСЛИ);

c. Определить средний возраст деревьев (СРЗНАЧ), наибольший и наименьший площади посадок (МАКС и МИН), а также общую площадь посадок (СУММ);

d. Ниже основной таблицы оформить результаты в следующем виде:

Практическая работа №15

Тема: «Связь с листами»

Студент должен:

Знать: технологию работы с листами.

Уметь: осуществлять связь между листами.

Задание 1. Создать и заполнить предложенную таблицу. Лист переименовать в Товары. В ячейках, содержащих цены, использовать денежный формат. Для изменения направления содержимого ячеек на 90° выделить необходимые ячейки и в меню Формат выбрать Ячейки - Выравнивание - Ориентация - 90°. Выполнить обрамление таблицы.

1. Вычислить необходимые данные по формулам:

Количество остатка = Количество прихода – Количество расхода

Сумма остатка = Количество остатка * Цена расхода

2. Выполнить сортировку данных по возрастанию (Данные - Сортировка) по столбцу Отдел.

3. С помощью Автофильтра выбрать данные с нулевым остатком и выделить их Цветом заливки. А затем убрать Автофильтр (Данные - Фильтр - Автофильтр).

4. Вставить новый лист (Вставка - Лист) и переименовать его в Сводка.

5. На листе Сводка создать таблицу, включающую столбцы № п/п, Отдел, Наименование товара и Сумма остатка. Причем данные перенести с листа Товары с помощью Связи с листами. Для этого на листе Сводка в ячейке А1 ввести знак =, затем перейти на лист Товары, выделить ячейку A3 и нажать клавишу [Enter]. Данные для смежных ячеек заполнить Маркером заполнения.

6. Вычислить итоговую Сумму остатка. Выполнить обрамление таблицы.

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

1. Ввести значение курса доллара на текущий день.

2. Вычислить необходимые данные по формуле: Цена в руб. = Цена в $ * Курс доллара [F4].

3. Выполнить сортировку по возрастанию по группам товара и наименованию (ДАННЫЕ - СОРТИРОВКА).

4. Вставьте новый лист (ВСТАВКА – ЛИСТ) и переименуйте его в «Продажа товаров».

5. На листе «Продажа товаров» создайте таблицу, используя связь с листами.

6. Заполнить столбцы Организация, Количество товаров. Вычислить необходимые данные по формулам:

Стоимость товаров в руб. = Количество товаров * Цена в руб.

Скидка по дисконтной карте 5% = Стоимость товаров в руб. * 5%

Всего к оплате = Стоимость товаров в руб. – Скидка по дисконтной карте 5%

7. Подвести итоги по столбцам Количество и Всего к оплате.

8. На отдельном листе построить диаграмму «Продажа товаров» по столбцам Организация, Всего к оплате. Выделить столбцы с помощью [Ctrl] и Вставка – Диаграмма.

Задание 3.

1. Сформировать таблицу расчета заработной платы на 10 человек. ФИО, Оклад и количество детей заполнить произвольными данными.Вычислить необходимые данные по формулам:

Премия = Оклад * 25%

Итого начислено = Оклад + Премия

Профсоюзный = Итого начислено * 1%

Пенсионный = Итого начислено * 1%

Подоходный = Итого начислено * 13%

Итого удержано = Профсоюзный + Пенсионный + Подоходный

Сумма к выдаче = Итого начислено – Итого удержано

2. Текущий лист переименовать в Зарплата. Следующий лист – в Ведомость. При помощи связи с листами перенести данные по столбцам №, Фамилия, Сумма к выдаче.

3. Третий лист назвать Детские. Создать в нем 3 столбца: №, Фамилия, Сумма пособия.

4. Вычислить Сумму пособия по формуле = 75% * Миним. зарплата * Количество детей

5. Установить фильтр по наличию детей.

6. Выше таблицы поместить заголовок Ведомость начисления пособия.

7. Построить круговую диаграмму «Ведомость» на отдельном листе.

Тема 4.10. Подбор параметра

Студент должен:

иметь представление:

- об использовании Надстроек в MS Excel;

знать:

- о процедуре Подбор параметра;

уметь:

- решать уравнения и задачи с использованием Подбора параметра.

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

Практическая работа №16

Тема: «Подбор параметра»

Задание 1: Решить уравнение 5х² – 2х + 6 = 8, используя Подбор параметра. Для этого:

1. в ячейку А1 ввести заголовок таблицы Подбор параметра.

2. в ячейку А2 ввести переменную х, а в ячейку А3 значение переменной – 0 (т. к. х – неизвестное, то начальное значение можно взять нулевое).

3. В ячейку В2 ввести y, а в ячейку В3 формулу =5*А3^2-2*А3+6.

4. Установить курсорную рамку в ячейку В3 и выполнить команду Сервис - Подбор параметра.

5. В окне Подбор параметра: Установить в ячейке – В3; значение – 8; Изменяя значение ячейки $А$3. Таким образом, процедура Подбор параметра будет автоматически изменять значение в ячейке А3, то есть Х, до тех пор, пока значение целевой функции в ячейке В3, то есть Y, не станет равным 8.

6. Образец таблицы:

Задание 2: Решить уравнения, используя процедуру Подбор параметра:

a) (х-2)² – 0,5^х = 1

b) x³ + 2x² + 6x + 4 = 0

c)

d) х³ - 6х² + 9х + 0,2=0

e) sin x + 2cos² x = 0

f) 2х² - 4х – 3 = 0

g) 25х² – 17х – 14 = 0

h) 99х² + х – 100 = 0

Познавательные статьи:



Как правильно слушать собеседника

Типичные ошибки при выполнении бросков в баскетболе

Принятие христианства на Руси и его значение

Средства массовой информации США