1. Кнопка Office – Параметры Excel – Основные – Изменить Списки.

2. Создать список в окне Элементы списка

• Курсор в поле «Импорт списка из ячеек»

• выделить диапазон D2:D5 – списка наименований товаров

• Импорт

• ОК».

Результат показан на рисунке.

8. Затем выполнить команду «Данные – Сортировка» В поле «Сортировать по» выбрать «Товар».

В поле Порядок – Настраиваемый список

Появится ДО Списки

В поле Списки выбрать нужный список – ОК!

9. Сортировка 6 – по списку постоянных клиентов:

Аналогично Сортировка 5

10.Сортировка 7 - кнопкой (сортировка от минимального к максимальному) на вкладке Данные группы Сортировка.

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

11.Сортировка 8 ‑ Отсортировать продажи по убыванию веса покупок. Для этого выберите любую ячейку в ключевом для сортировки столбце и примените кнопку «Сортировка от максимального к минимальному»;

12. Сортировка 9 – отсортировать записи о продажах по алфавиту.

Выделить ячейку столбца «Товар» и применить кнопку ;

13. Сортировка 10 – Клиенты по алфавиту.

Аналогично

Многоуровневые сортировки

14. Сортировка 11 – товары по алфавиту, а пределах каждого товара цена реализации – по возрастанию

Выделить любую ячейку таблицы. Выполнить команду «Данные – Сортировка»

Добавить уровень (см. рис.)

15. Сортировка 12 – Покупатели по алфавиту, а каждый покупатель хронологическом порядке.

16. Сортировка 13 –Клиенты – по алфавиту, а внутри Выручка – по убыванию.

17. Сортировка 14 – кнопкам и _. Сначала сортировка по столбцу «Товар» затем (см. рис. ).

Вместо

18. Сортировка 15 – Клиенты – по алфавиту, внутри товары по алфавиту, а покупки товаров в по Дате по возрастанию.

Трехуровневая. В поле «Сортировать по» внести «Клиент» от А до Я,

Добавить уровень

в поле «Затем по» – «Товар» от А до Я,

Добавить уровень

в следующем поле «Затем по» – «Дата» от старых к новым. ОК;

Рис.17 Трехуровневая сортировка

19. Сортировка 16 – трехуровневая. Товары по алфавиту, внутри цена по возрастанию, затем по убыванию вес покупок

20. Сортировка 17 – Клиенты по списку постоянных клиентов, для каждого клиента выручка по убыванию.

21. Сортировка 18 – по двум спискам. Товары по списку наименований товаров, внутри покупатели по списку постоянных клиентов. Это две одноуровневые сортировки, каждая с пользовательским порядком сортировки по первому ключу

Сортировка 18 по двум спискам (см. задание)

21. Сортировка 19 – трехуровневая с пользовательским порядком сортировки по второму ключу. Сортировать сначала покупателей по алфавиту, внутри товары по списку, а внутри списка товаров выручка по убыванию;

23. Сортировка 20 – Вернуть первоначальный список. Сортировать по полю «№».

Форма отчетности по лабораторной работе 3.

На листе «5 Сортировка» продемонстрировать все 20 сортировок.

Лабораторная работа №4 ( MS Excel)

Тема. Подведение итогов

Цель: Освоить средства автоматического подведения итогов

Задание на лабораторную работу. Провести анализ работы с постоянными покупателями, проиллюстрировать результаты на диаграммах.

Порядок выполнения лабораторной работы:

1. Создать новый лист вида «6Итоги клиентов»;

2. ”Сделать копию листа продаж”???

Копия сделана с листа 4Проверка на лист 6Итоги клиентов;

Учёт продаж по торговой точке /Вишневский А.Н./
№	Клиент	Дата	Товар	Цена	Количество	Выручка
1	Бистров	1 Декабрь	Капуста	18,00р	2,500кг	45,00р
2	Кукин	2 Декабрь	Помидоры	37,50р	3,500кг	131,25р
3	Оптов	3 Декабрь	Картофель	10,00р	10,000кг	100,00р
4	Торбин	6 Декабрь	Капуста	14,00р	8,500кг	119,00р
5	Кукин	7 Декабрь	Капуста	15,00р	2,000кг	30,00р
6	Бистров	9 Декабрь	Огурцы	38,00р	4,500кг	171,00р
7	Кукин	9 Декабрь	Картофель	11,00р	3,500кг	38,50р
8	Оптов	10 Декабрь	Огурцы	34,50р	7,000кг	241,50р
9	Торбин	13 Декабрь	Картофель	11,00р	10,000кг	110,00р
10	Иванов	14 Декабрь	Огурцы	35,00р	1,500кг	52,50р
11	Бистров	14 Декабрь	Картофель	13,00р	3,500кг	45,50р
12	Кукин	16 Декабрь	Капуста	14,50р	2,000кг	29,00р
13	Оптов	17 Декабрь	Капуста	15,00р	9,500кг	142,50р
14	Торбин	20 Декабрь	Помидоры	38,00р	4,800кг	182,40р
15	Кукин	21 Декабрь	Картофель	11,00р	2,500кг	27,50р
16	Бистров	21 Декабрь	Помидоры	41,50р	1,500кг	62,25р
17	Кукин	23 Декабрь	Огурцы	36,50р	4,000кг	146,00р
18	Оптов	24 Декабрь	Капуста	15,00р	10,000кг	150,00р
19	Бистров	24 Декабрь	Картофель	13,00р	3,000кг	39,00р
20	Торбин	24 Декабрь	Огурцы	37,00р	8,000кг	296,00р
21	Оптов	27 Декабрь	Помидоры	39,00р	8,000кг	312,00р
22	Торбин	27 Декабрь	Картофель	11,50р	10,000кг	115,00р
23	Кукин	27 Декабрь	Помидоры	40,00р	2,000кг	80,00р
24	Новых	27 Декабрь	Картофель	13,00р	2,500кг	32,50р
25	Новых	28 Декабрь	Помидоры	41,00р	1,500кг	61,50р
26	Оптов	29 Декабрь	Картофель	12,00р	14,000кг	168,00р
27	Новых	29 Декабрь	Огурцы	40,00р	2,000кг	80,00р
28	Бистров	29 Декабрь	Огурцы	39,00р	15,000кг	585,00р
29	Новых	30 Декабрь	Капуста	18,00р	3,000кг	54,00р
30	Кукин	30 Декабрь	Картофель	12,00р	12,000кг	144,00р

3. Отсортировать записи по клиентам в порядке, задаваемом списком постоянных клиентов:

Вычислить по каждому клиенту общую сумму его покупок в рублях и килограммах.

• Выделить всю таблицу

• Команда «Данные – Промежуточные итоги»

• В ДО «Промежуточные итоги» внести в поле

• При каждом изменении в: – Клиент,

• Операция: – Сумма,

• Добавить итоги по: - Выручка», «Количество»;

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

«Данные – Промежуточные итоги», в появившемся ДО «Промежуточные итоги» внести в полях:

• При каждом изменении в: - Клиент

• Операция – Количество,

• Добавить итоги по – Товар.

• убрать флажок Заменить текущие итоги

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

Иначе:

Добавить по каждому покупателю максимальные и минимальные, средние:

выручку, количество и цену

Максимум и Среднее – аналогично.

Необходимо соблюдать очерёдность подведения итогов для использования итоговых данных (построения диаграмм и др.)

Учёт продаж по торговой точке /Вишневский А.Н./
№	Клиент	Дата	Товар	Цена	Количество	Выручка
4	Торбин	6 Декабрь	Капуста	14,00р	8,500кг	119,00р
9	Торбин	13 Декабрь	Картофель	11,00р	10,000кг	110,00р
14	Торбин	20 Декабрь	Помидоры	38,00р	4,800кг	182,40р
20	Торбин	24 Декабрь	Огурцы	37,00р	8,000кг	296,00р
22	Торбин	27 Декабрь	Картофель	11,50р	10,000кг	115,00р
	Торбин Максимум			38,00р	10,000кг	296,00р
	Торбин Минимум			11,00р	4,800кг	110,00р
	Торбин Среднее			22,30р	8,260кг	164,48р
	Торбин Количество		5
	Торбин Итог				41,300кг	822,40р
2	Кукин	2 Декабрь	Помидоры	37,50р	3,500кг	131,25р
5	Кукин	7 Декабрь	Капуста	15,00р	2,000кг	30,00р
7	Кукин	9 Декабрь	Картофель	11,00р	3,500кг	38,50р
10	Кукин	14 Декабрь	Огурцы	35,00р	1,500кг	52,50р
12	Кукин	16 Декабрь	Капуста	14,50р	2,000кг	29,00р
15	Кукин	21 Декабрь	Картофель	11,00р	2,500кг	27,50р
17	Кукин	23 Декабрь	Огурцы	36,50р	4,000кг	146,00р
23	Кукин	27 Декабрь	Помидоры	40,00р	2,000кг	80,00р
30	Кукин	30 Декабрь	Картофель	12,00р	12,000кг	144,00р
	Кукин Максимум			40,00р	12,000кг	146,00р
	Кукин Минимум			11,00р	1,500кг	27,50р
	Кукин Среднее			23,61р	3,667кг	75,42р
	Кукин Количество		9
	Кукин Итог				33,000кг	678,75р
3	Оптов	3 Декабрь	Картофель	10,00р	10,000кг	100,00р
8	Оптов	10 Декабрь	Огурцы	34,50р	7,000кг	241,50р
13	Оптов	17 Декабрь	Капуста	15,00р	9,500кг	142,50р
18	Оптов	24 Декабрь	Капуста	15,00р	10,000кг	150,00р
21	Оптов	27 Декабрь	Помидоры	39,00р	8,000кг	312,00р
26	Оптов	29 Декабрь	Картофель	12,00р	14,000кг	168,00р
	Оптов Максимум			39,00р	14,000кг	312,00р
	Оптов Минимум			10,00р	7,000кг	100,00р
	Оптов Среднее			20,92р	9,750кг	185,67р
	Оптов Количество		6
	Оптов Итог				58,500кг	1114,00р
1	Бистров	1 Декабрь	Капуста	18,00р	2,500кг	45,00р
6	Бистров	9 Декабрь	Огурцы	38,00р	4,500кг	171,00р
11	Бистров	14 Декабрь	Картофель	13,00р	3,500кг	45,50р
16	Бистров	21 Декабрь	Помидоры	41,50р	1,500кг	62,25р
19	Бистров	24 Декабрь	Картофель	13,00р	3,000кг	39,00р
28	Бистров	29 Декабрь	Огурцы	39,00р	15,000кг	585,00р
	Бистров Максимум			41,50р	15,000кг	585,00р
	Бистров Минимум			13,00р	1,500кг	39,00р
	Бистров Среднее			27,08р	5,000кг	157,96р
	Бистров Количество		6
	Бистров Итог				30,000кг	947,75р
24	Новых	27 Декабрь	Картофель	13,00р	2,500кг	32,50р
25	Новых	28 Декабрь	Помидоры	41,00р	1,500кг	61,50р
27	Новых	29 Декабрь	Огурцы	40,00р	2,000кг	80,00р
29	Новых	30 Декабрь	Капуста	18,00р	3,000кг	54,00р
	Новых Максимум			41,00р	3,000кг	80,00р
	Новых Минимум			13,00р	1,500кг	32,50р
	Новых Среднее			28,00р	2,250кг	57,00р
	Новых Количество		4
	Новых Итог				9,000кг	228,00р
	Общий максимум			41,50р	15,000кг	585,00р
	Общий минимум			10,00р	1,500кг	27,50р
	Общее среднее			24,13р	5,727кг	126,36р
	Общее количество		30
	Общий итог				171,800кг	3790,90р

Результат промежуточных итоговых функций

Сумма по полям «Количество 171,800кг» и «Выручка 3790,90р»

Количество^ 30 по полю «Товар»

Рис.21Фрагмент с несколькими итоговыми функциями

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

8. Отобразить только итоговые суммы по количеству и выручке для каждого клиента:

Рис.23 Итоговые суммы по клиентам

9. Скрыть все столбцы между столбцами «Клиент» и «Выручка», выделить столбцы C,D,E,F щелкнуть правой кнопкой мыши. В появившемся меню выбрать «Скрыть»:

Рис.24 Итоги со скрытыми пустыми столбцами

10. Отсортировать полученные итоги по выручке по убыванию.

Для сортировки выделить только Фамилии клиентов с выручкой

Выделить таблицу до строки «Общий Максимум», «Данные – Сортировать» по выручке по убыванию. В результате первым в списке отобразиться лучший покупатель, сделавший покупки на наибольшую сумму, образец на рис.25;

11. Создать новый лист вида «7 Круговая диаграмма»;

12.Скопировать лист «6 Итоги клиентов» и постройте круговую диаграмму по итоговым суммам покупателей.

Вставка – Круговая – выбрать

На вкладке Макет использовать группы: – (Легенда – Подписи данных – Название диаграммы)

Подписи данных – Дополнительные параметры (Значения, Доли, Категории)

На рисунке ниже показан образец диаграммы и исходные данные для нее:

Макет – Надпись – Фигуры – Рисунок

Макет – Поворот объёмной фигуры

Рис.25 Отсортированные итоги и круговая диаграмма

15. Выделить заголовок, переместить его влево вверх, шрифт Times, 20 пунктов, полужирный курсив, в две строки;

16. Увеличить угол показа объемной диаграммы до 210 градусов. Для этого выделить диаграмму, М* по диаграмме в появившемся КМ выбрать «Формат ряда данных», в ДО Формат ряда данных выбрать «Параметры ряда » и отметить угол поворота 210 градусов;

17. Выкатить сектор лучшего покупателя из круга.

18. Выделить подписи данных, щелкнуть правой кнопкой мыши. В появившемся контекстном меню выбрать формат подписей данных. В ДО выбрать вкладку «Число», выбрать в процентах с одной цифрой после запятой. На вкладке шрифт выбрать полужирный, 16 пунктов, для самого большого сектора – 20 пунктов, для очень маленького (Новых) – на выноске, 14 пунктов;

19. Для экономии места разместите подписи на фоне широких цветных секторов. На темном фоне сектора (Панченко) написать белым цветом. Лучше всего использовать ненавязчивые цвета стандартной палитры: сине-фиолетовый, вишневый, слоновая кость, светло-бирюзовый, темно-фиолетовый;

20. Убрать рамку вокруг легенды. Выделить легенду, клик правой кнопкой мыши, в контекстном меню выбрать формат легенды. В появившемся ДО на вкладке «Вид» отметить невидимую рамку. На вкладке «Шрифт» – шрифт Times, 16 пунктов;

21. В левом нижнем углу добавить надписи с панели рисования выручку по всему магазину;