Лабораторная работа №6
СОРТИРОВКА ДАННЫХ, АВОТОФИЛЬТР
Цель работы: Овладение навыками простой и сложной сортировки данных в таблицах, работы с автофильтром.
Задание
1. Назовите лист Товар. На листе Товар наберите таблицу.
номер | фирма | кассир | № счета | дата поставки | цена | дата оплаты | сумма оплаты | остаток |
Гвоздика | Иванов | 05.03.2002 | 02.04.2002 | |||||
Ромашка | Аронов | 06.04.2002 | 16.04.2002 | |||||
Василек | Лемешев | 14.03.2004 | 25.05.2004 | |||||
Ромашка | Иванов | 08.07.2002 | 07.09.2002 | |||||
Ландыш | Лемешев | 09.03.2007 | 10.11.2007 | |||||
Ландыш | Коломейцев | 16.12.2002 | 20.02.2003 | |||||
Василек | Плотников | 07.03.2002 | 17.03.2002 | |||||
Ландыш | Аронов | 12.01.2004 | 22.04.2004 | |||||
Ромашка | Коломейцев | 13.03.2002 | 25.07.2002 | |||||
Ромашка | Иванов | 23.03.2002 | 24.05.2002 | |||||
Василек | Иванов | 15.04.2001 | 01.07.2001 | |||||
Ландыш | Аронов | 11.08.2001 | 02.09.2001 | |||||
Гвоздика | Коломейцев | 12.02.2003 | 12.07.2003 | |||||
Ромашка | Коломейцев | 25.12.2002 | 18.04.2003 | |||||
Гвоздика | Плотников | 30.10.2004 | 14.12.2004 |
2. Задайте формат для столбцов: цена и сумма оплаты – денежный, р; дата поставки – длинный формат даты; дата оплаты – формат даты, подобный 14 мар 01.
3. Данные столбца остаток посчитать как разница значений столбцов цена и сумма оплаты. Задать такой формат данных, чтобы отрицательные значения были выделены красным цветом, знаков после запятой нет.
4. Скопируйте таблицу и вставьте ее копию ниже на листе. В верхней таблице проведите Сортировку данных с помощью кнопки Сортировка по столбцу фирма.
5. В нижней таблице проведите настраиваемую сортировку сначала по столбцу фирма, затем по столбцу кассир, и затем по столбцу № счета. Сравните с результатом, который получился в верхней таблице.
6. Скопируйте лист «Товар» и назовите копию листа «Фильтр». Скопируйте лист Фильтр и назовите копию Фильтр по цвету.
7. На листе Фильтр в верхней таблице поменяем местами столбцы.
Чтобы данные последнего столбца сохранили при этом свои значения скопируйте последний столбец и вставьте на это же место с помощью специальной вставки только значения.
Теперь под каждым столбцом таблицы запишите следующий желаемый порядок расстановки столбцов: 1, 2, 9, 5, 3, 6, 4, 7, 8.
Проведите сортировку данных этой таблицы, сортируя не строки, а столбцы. После сортировки цифры под таблицей удалите. Сравните с таблицей, расположенной ниже.
8. В верхней таблице добавьте новый столбец ч исло дней. В нем посчитайте разницу в днях между датами поставки и оплаты с помощью функции Дней 360 (в строке метод поставить 1).
9. К верхней таблице примените фильтр. С помощью него отобразите данные: по цене значение меньше 500, по дате поставок с 01 июля 2002г, по фирме только гвоздика. Фильтр не отключайте.
10. Скопируйте нижнюю таблицы и вставьте ее копию еще ниже. Примените фильтр теперь к средней таблице: по фирме – больше Д; по номеру счета - первые пять наибольших; по дате оплаты – между 1 мая 2002 и 1 октября 2002.
11. Примените фильтр к нижней таблице: Кассир - только Иванов и Коломейцев; по цене – не равно 444; сумма оплаты – выше среднего.
12. Перейдите на лист Товар. Справа от верхней таблицы создайте такую таблицу:
среднее по сумме оплаты | |
максимум по дате счета | |
минимум по дате оплаты | |
сумма по сумме счета |
13. Дайте имена диапазонам данных в столбцах «Сумма оплаты», «Дата счета», «Дата оплаты» и «Сумма в счете». Для этого выделяете соответствующий столбец и в строке имени пишете имя диапазона.
14. Заполните таблицу, вычислив среднее (average), максимум (max), минимум (min) и сумму (sum) соответствующих данных, указывая в качестве аргументов названия диапазонов данных.
15. На листе Фильтр по цвету примените к столбцу № счета условное форматирование – набор из четырех значков.
16. В столбце дата оплаты залейте желтым цветом с помощью кнопки заливка значения 20 фев 03 и 22 апр 04.
17. Включите фильтр и с помощью фильтра по цвету отобразите в столбце № счета самые малые значения, а в столбце дата оплаты – желтые ячейки.