Тема 2. Операции с записями таблицы

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

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

Задание 20. Нумерация строк

Чтобы была возможность увидеть список в первоначальном виде, вставьте столбец с нумерацией строк. Для этого: установите курсор в первом столбце; в меню «Вставка» выберите команду «Столбец», в A3 введите цифру 1; в А4 введите цифру 2; выделите блок из двух ячеек A3 — А4; подведите указатель мыши к маркеру выделения, ухватившись за черное тонкое перекрестие, потяните вниз до конца списка (пронумеруется весь список):

• измените ширину колонки А;

• выполните обрамление.

Таблица 3. Учет выполненных заказов

Месяц	Дата	Код товара	Наименование товара	Цена	Кол-во	Код клиента	Клиент	Сумма	Скидка	Уплачено
январь	03.01.98	1002			35	13
январь	03.01.98	2001			5	11
январь	12.01.98	2004			10	13
январь	17.01.98	3001			15	14
январь	20.01.98	4001			2	15
январь	25.01.98	4002			3	13
январь	25.01.98	3003			40	11
январь	28.01.98	3002			50	13
февраль	02.02.98	3002			50	15
февраль	04.02.98	3003			35	14
февраль	06.02.98	3002			100	12
февраль	11.02.98	4002			3	13
февраль	14.02.98	1002			100	14
февраль	16.02.98	2001			5	15
февраль	20.02.98	2002			15	15
февраль	22.02.98	2001			4	14
февраль	26.02.98	3001			20	13
март	01.03.98	3003			30	12
март	03.03.98	3002			20	11
март	05.03.98	4002			2	11
март	06.03.98	3001			10	14
март	06.03.98	2002			5	15
март	11.03.98	4002			2	15
март	11.03.98	1002			50	13
март	12.03.98	2001			3	14
март	12.03.98	4001			2	11
.март	14.03.98	4002			2	15

Таблица 4. Учет выполненных заказов

Месяц	Дата	Код товара	Наименование товара	Цена	Кол-во	Код клиента	Клиент	Сумма	Скидка	Уплачено
февраль	20.02.98	2002	Машина стиральная	400	15	15	Магазин № 48	6000	0	6000
февраль	06.02.98	3002	Туфли женские	40	100	12	Ветковское ПО	4000	0,03	4000
январь	25.01.98	4002	Мебельный гарнитур для спальни	1000	3	13	Сталбуновское ПО	3000	0,03	2910
февраль	11.02.98	4002	Мебельный гарнитур для спальни	1000	3	13	Сталбуноаское ПО	3000	0,03	2910
февраль	16.02.98	2001	Холодильник	500	5	15	Магазин № 48	2500	0	2450
январь	03.01.98	2001	Холодильник	500	5	11	Магазин № 8	2500	0,05	2500
март	06.03.98	2002	Машина стиральная	400	5	15	Магазин № 48	2000	0	1900
март	11.03.98	4002	Мебельный гарнитур для спальни	1000	2	15	Магазин № 48	2000	0	2000
февраль	02.02.98	3002	Туфли женские	40	50	15	Магазин № 48	2000	0	2000
февраль	22.02.98	2001	Холодильник	500	4	14	Магазин № 29	2000	0	2000
январь	25.01.98	3003	Туфли мужские	50	40	11	Магазин № 8	2000	0,05	2000
март	05.03.98	4002	Мебельный гарнитур для спальни	1000	2	11	Магазин № 8	2000	0,05	1900
март	14.03.98	4002	Мебельный гарнитур для спальни	1000	2	15	Магазин № 48	2000	0	1900
январь	28.01.98	3002	Туфли женские	40	50	13	Сталбуновское ПО	2000	0,03	2000
февраль	26.02.98	3001	Куртка	90	20	13	Сталбуновское ПО	1800	0,03	1746
февраль	06.02.98	3003	Туфли мужские	50	35	14	Магазин № 29	1750	0	1697,5
март	05.03.98	2001	Холодильник	500	3	14	Магазин № 29	1500	0	1500
январь	17.01.98	3001	Куртка мужская	90	15	14	Магазин № 29	1350	0	1350
март	01.03.98	3003	Туфли мужские	50	20	12	Ветковское ПО	1000	0,03	1000
февраль	14.02.98	1002	Кафельная плитка для стен 1м^2	10	100	14	Магазин № 29	1000	0	970
март	06.03.98	3001	Куртка	90	10	14	Магазин № 29	900	0	900
март	03.03.98	3002	Туфли женские	40	20	11	Магазин № 8	800	0,05	800
март	11.03.98	1002	Кафельная плитка для стен 1 м^2	10	50	13	Стапбуновское ПО	500	0,03	475
март	12.03.98	4001	Мебельный гарнитур для кухни	250	2	11	Магазин № 8	500	0,05	485
январь	20.01.98	4001	Мебельный гарнитур для кухни	250	2	15	Магазин № 48	500	0	475
январь	03.01.98	1002	Кафельная плитка для стен 1 м^2	10	35	13	Сталбуновское ПО	350	0,03	350
январь	12.01.98	2004	Миксер	30	10	13	Сталбуновское ПО	300	0,03	291

Задание 21. Выполнение сортировки

Для выполнения сортировки:

• выделите в списке блок A3 — L29 (заголовки столбцов не включать в диапазон выделения);

• в меню «Данные» выберите команду «Сортировка». Появится окно диалога «Сортировка диапазона». В нижней части этого окна в поле «Идентифицировать поля по…» есть два переключателя;

• если вы установите переключатель по обозначениям столбцов листа, то в раскрывающемся списке поля «Сортировать по…» будет предложена сортировка по столбцам рабочего листа с именами А, В, С;

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

• установите переключатель в нижней части окна диалога «Обозначения столбцов листа»;

• отсортируйте список по столбцу К (Скидка) по убыванию, т.е. определить, кто из клиентов пользуется наибольшей скидкой. Выделение блока не отменяйте;

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

Задание 22. Сортировка по нескольким столбцам

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

• отсортируйте список по столбцам «Наименование товара» и по полю «Клиент»;

• просмотрите, у кого из клиентов и какой товар пользуется большим спросом.

Задание 23. Использование Итогов при сортировке

Проделайте следующее:

• установите указатель в области списка;

• в меню «Данные» выберите команду «Итоги»;

• в окне запроса «Считать» верхнюю строку выделения строкой названий ответьте «Да»;

• в окне диалога «Промежуточные итоги» при каждом изменении выберите столбец Е;

• в поле «Операция» — выберите «Сумма»;

• в поле «Добавить итоги» — выберите по столбцу L.

Если нужно отменить «Промежуточные итоги», в меню «Данные» выберите команду «Итого». В окне диалога нажмите кнопку «Убрать все».

Чтобы оставить на экране только итоги, нажмите знаки минусов на кнопках, которые появились в левой части окна Excel.

Задание 24. Использование фильтров для анализа списков

Отфильтровать список — значит скрыть все строки за исключением тех, которые удовлетворяют заданным условиям отбора. В меню «Данные» команда «Фильтр» имеет подменю «Автофильтр» — для простых условий отбора и «Расширенный фильтр» — для более сложных критериев.

Для выполнения фильтрации:

• выделите любую ячейку в списке;

• в меню «Данные» выберите команду «Фильтр», подменю «Автофильтр».

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

Для примера рассмотрим следующую ситуацию.

Допустим, нас интересует торговля с магазином № 48.

Для этого в столбце «Клиент» щелкните по кнопке и в раскрывающемся списке выберите магазин № 48. В результате на экране останутся данные только для магазина № 48.

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

Для анализа отфильтрованных строк можно использовать команду «Итоги». Подсчитайте итоги по магазину № 48, для этого выполните следующее:

• выделите диапазон ячеек столбца «Получено»;

• нажмите на кнопку «Суммы» на панели инструментов, внизу колонки появится итоговая сумма;

• нажмите на стрелку синего цвета на кнопке возле столбца «Клиент». В раскрывшемся списке кроме перечня клиентов вы увидите элементы, по которым производится фильтрация: «Все», «Условие», «Первые 10».

«Все» — данный элемент в раскрывающемся списке выбирается для восстановления всех записей. Выберите «Все» для восстановления записей.

«Первые 10». Если вы занимаетесь составлением всевозможных рейтингов, ваш список довольно велик, и главная задача состоит в определении лучшей десятки; воспользуйтесь данным элементом. Также «Автофильтр» дает возможность найти заданное число (первые 10...) наибольших или наименьших элементов в списке. Найдите 10 самых удачных продаж товаров. Для этого установите автофильтр, раскройте открывающийся список столбца L, выберите в списке « первые 10... ».

Откроется окно диалога с тремя раскрывающимися списками. В среднем выберите «Наибольших».

В левом списке задайте любое число от 1 до 500 (например, 10).

В правом списке выберите «Элементы списка» — появятся 10 наиболее удачных продаж.

Восстановите записи, выбрав элемент «Все».

«Условие» используется для более сложного критерия отбора. В нашем примере задайте условие по колонке «Цена» >50<1000. Отобразятся найденные записи.

Чтобы удалить автофильтр для конкретного столбца, откройте соответствующий список и выберите «Все».

Чтобы удалить все примененные фильтры, в меню «Данные» выберите команду «Фильтр» и удалите флажок.