(СРСП) Лаб. № 4. Филиалы

(СРСП)  Лаб. № 4. Филиалы

1.       Создайте рабочую книгу и сохраните ее в своей папке под именем  Филиалы(Ваша фамилия). Начнем выполнение примера с создания таблицы и ввода данных о каждом филиале.

2.       Подготовительный этап. Скопируйте в буфер обмена с листа Товары книги Заказы данные о товарах, их номерах и ценах, т.е. скопируйте диапазон ячеек А1-С12 листа Товары.

3.       Перейдите к первому листу книги Филиалы и в ячейку А3 вставьте скопированный фрагмент таблицы. В 3 строе в ячейки D3, E3, F3 введите соответственно записи Количество заказов, Проданное количество и Объем продаж. Задайте центрирование текста в ячейках и разрешите перенос текста по словам.

4.       В ячейку F4 поместите формулу: =С4*Е4 и скопируйте ее в ячейки F5-F14.

5.       Введите в ячейку В15 слово Всего:, а в ячейку F15 вставьте формулу суммы или нажмите кнопку  панели инструментов Стандартная. Excel  сам определит диапазон ячеек, содержимое которых следует суммировать.

6.       Таких листов должно быть столько, сколько у вас было городов в листе Клиенты. Мы должны скопировать этот лист 4 раза.

7.       Для этого установите курсор мыши на его ярлычке и  нажмите правую кнопку манипулятора. В контекстном меню выберите команду Переместить/скопировать, в появившемся диалоговом окне укажите лист, перед которым должна быть вставлена копия, активизируйте опцию Создать копию и нажмите ОК. Намного проще копировать с помощью мыши: установите указатель мыши на ярлычке листа и  переместите его в позицию вставки копии, удерживая при этом нажатой клавишу [Ctrl].

8.       Имена рабочих листов соответствуют названиям городов с листа Клиенты, например, Алматы, Астана, Шымкент, Актау, Караганда или другие названия. Введите название филиала, соответствующего названию листа и в ячейку А1 данного листа.

9.       Дополните лист Заказы еще одним столбцом. В ячейку М1 введите слово Город. В ячейку М2 введите формулу  =ЕСЛИ(ЕПУСТО($H2);“  ”;ПРОСМОТР($H2;Код; Город)), протяните эту формулу до строки 31 этого столбца.

10.    Выбрать в меню Данные ÞФильтр/Атофильтр. Выберите в столбце Город  первый филиал. Данные столбца Количество листа Заказы будут внесены вами в столбец Проданное количество листа книги Филиалы,  в строки соответствующие номерам товаров. Если проданы товары с одним номером в разные месяцы, то берется их суммарное количество. И так заполняются листы всех городов. 

11.    Консолидация данных. Скопируйте с первого листа книги Филиалы диапазон А3-В14, перейдите в 6 рабочий лист и вставьте в ячейку А3.

12.    Приступаем к консолидации. Установите указатель ячейки в С3  и выберите в меню Данные ÞКонсолидация.

13.    В списке Функции следует выбрать элемент Сумма. Укажите в поле ввода Ссылка диапазон ячеек, данные которых должны быть подвергнуть процессу консолидации. Удобно отмечать диапазон ячеек с помощью мыши.

14.    Установите курсор ввода в поле Ссылка, выполните щелчок на ярлычке первого города, например –Алматы, выделить диапазон ячеек D3-F14 и нажать кнопку Добавить  окна Консолидация. В результате указанный диапазон будет переставлен в поле Список диапазонов.

15.    Затем переходите на лист второго города. Диапазон указывается автоматически, нажимаете на кнопку Добавить и так 5 раз.

16.    Если верхняя строка и (или) левый столбец содержат заголовки, которые необходимо скопировать в итоговую таблицу, следует активизировать соответствующие опции в группе Использовать метки. Поскольку в нашем примере верхняя строка содержит заголовки столбцов, нужно активизировать опцию В верхней строке.

17.    Если между исходными данными и данными консолидированной таблицы должны быть установлены динамическая связь, включите опцию Создавать связи с исходными данными.

18.    Кнопку Обзор следует использовать для выбора файла , который содержит консолидируемые данные.

19.    Нажмите кнопку ОК.

20.    В ячейку А1 введите название новой таблицы Итоговые данные.

21.    Введите в ячейку В70 значение Всего:, а в Е70 -    и нажмите на клавишу [Enter]

22.    Теперь приступаем к определению доли от общей прибыли суммы, вырученной от продажи каждого товара.  Введите в F9 формулу = Е9/$E$70 и скопируйте ее в остальные ячейки столбца  F (до ячейки  F70) .

23.    Отформатируйте содержимое столбца F в процентном стиле. Полученные результаты позволяют сделать выводы о популярности того или иного товара.

24.    При консолидации данных программа записывает в итоговой таблице каждый элемент и автоматически создает структуру документа, что позволяет добиться представления на экране только необходимой информации и скрыть ненужные детали. Слева от таблицы отображаются символы структуры. Цифрами обозначаются уровни структуры (в нашем примере – 1 и 2). Кнопка со знаком плюс позволяет расшифровать данные высшего уровня. Нажмите, например, кнопку для ячейки А9, чтобы получить информацию об отдельных заказах.

25.    Скопируйте формулу из  F9 в ячейки F4- F8.

Цифры в превращаются в Диаграммы

1. Подготовительная работа. Поскольку для каждой диаграммы нужна собственная таблица, создадим новую сводную таблицу на основе данных листа Заказы одноименной книги Заказы.
2. Откройте ранее созданную книгу Заказы. Создайте новую книгу и присвойте ее первому листу имя Таблица. Этот лист будет содержать числовой материал для диаграммы.
3. Поместите указатель в ячейку В3 и выберите меню Данные ÞСводная таблица.
4. Выберите первый способ расположения данных – В списке или базе данныхMicrosoft Excel – нажмите кнопку Далее.
5. На втором шаге поместив курсор ввода в поле Диапазон следует с помощью меню Окно перейти в рабочую книгу Заказы и в рабочем листе Заказы и выделить диапазон A1-L31. После нажимаем на кнопку Далее.
6. Следует определить структуру сводной таблицы. Поместите в область строк кнопку Наименование товара, а в область столбцов – кнопку Месяц. Сумма будет вычисляться по полю Сумма заказа, т.е. переместите эту кнопку в область данных. Нажмите кнопку Готово.
7. Выделите диапазон B4-F14. Если вы выделяете диапазон ячеек с помощью мыши, начните выделение с любой крайней ячейки диапазона за исключением ячейки  F4, которая содержит кнопку сводной таблицы.
8. Щелкните на кнопке Мастер диаграмм в панели инструментов Стандартная.
9. На первом шаге укажите тип диаграммы, нажмите  на кнопку Далее.
10. На втором шаге подтвердите диапазон =Таблица!$B$4:$F$15.
11. На третьем шаге указываете параметры диаграммы (Заголовки, Оси, Легенды и т.д.). Название диаграммы введите Объем продаж по месяцам, Категории (Х )- Наименование товара и Значение(Y) –Объем продаж(USD). Внесенные изменения сразу отразятся на изображении  в поле Образец, нажмите  на кнопку Далее.
12. Нажмите  на кнопку Готово.