Лабораторная работа 4. Связанные списки

1. Создайте новую рабочую книгу. Оформите таблицу с марками и моделями автомобилей. Требуется создать связанные выпадающие списки: марка-модель (список должен быть отсортирован по маркам автомобилей).

2. Для создания первичного выпадающего списка марок поставьте курсор в ячейку G1. На вкладке Данные щелкните по кнопке Проверка данных, выберите вариант проверки Список и укажите в поле Источник =$D$1:$D$3.

3. Для зависимого списка моделей нужно создать именованный диапазон с функцией СМЕЩ, который будет динамически ссылаться только на ячейки моделей определенной марки. Для этого на вкладке Формулы щелкните по кнопке . Создайте новый именованный диапазон с именем Модели и в поле Ссылка введите вручную следующую формулу:

=СМЕЩ($A$1;ПОИСКПОЗ($G$1;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$1);1)

В более понятном варианте синтаксис этой функции таков:

· Ссылка - берем первую ячейку нашего списка, т.е. А1;

· Смещ_по_строкам - считает функция ПОИСКПОЗ, которая выдает порядковый номер ячейки с выбранной маркой (G1) в столбце А;

· Смещ_по_столбцам = 1, т.к. мы хотим сослаться на модели в столбце В;

· Высота - вычисляет функция СЧЕТЕСЛИ, которая считает количество встретившихся в столбце А нужных марок авто (G1);

· Ширина = 1, т.к. нам нужен один столбец с моделями.

4. Добавьте выпадающий список на основе созданной формулы в ячейку G2. Для этого выделите ячейку G2, на вкладке Данные щелкните по кнопке Проверка данных, выберите вариант проверки Список и введите в поле Источник ссылку на динамический именованный диапазон =Модели.

5. Скройте столбцы А:Е (выделите нужные столбцы, на вкладке Главная щелкните по кнопке Формат и выберите Скрыть или отобразить/Скрыть столбцы).

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

7. Сохраните рабочую книгу под именем Связанные списки в папку Выполненные задания. Закройте книгу.

Выбор фото из выпадающего списка

Задание. Необходимо сделать так, чтобы в одной из ячеек листа был выпадающий список с наименованиями, при выборе из которого, товар отображался бы рядом в виде фотографии.

Методические указания

1. Создайте на Листе 1 каталог с наименованиями и фотографиями товаров, состоящий из двух столбцов (Модель и Фото).

2. На вкладке Формулы щелкните на кнопке Диспетчер имен. Создайте именованный диапазон: имя Фотоальбом, а в качестве адреса введите формулу:

=СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A:$A)-1;1).

Эта формула определяет последнюю занятую ячейку в столбце А и выдает на выходе диапазон с А2 до этой найденной ячейки. Такая относительно сложная конструкция нужна, чтобы впоследствии дописывать новые модели к списку и не думать об исправлении диапазона.

3. Перейдите на Лист 2 и создайте там ячейку с выпадающим списком для выбора пользователем модели телефона (пусть это будет A1). Для этого выделите ячейку, на вкладке Данные нажмите Проверка данных. Далее в поле Тип данных следует выбрать Список, а в качестве Источника указываем Фотоальбом (не забудьте перед ним добавить знак равенства). Кроме того, ячейке А1 удобно дать имя, например Выбор.

4. Перенесем первую фотографию из фотоальбома к выпадающему списку. Выделите ячейку с первой фотографией (не сам рисунок, а ячейку!) и разверните выпадающий список под кнопкой Копировать на Главной вкладке:

Копируем, переходим на Лист 2 к выпадающему списку и в любую пустую ячейку недалеко от него вставляем наш мини-скриншот ячейки с фотографией (меню Правка - Вставить или обычное CTRL+V).

5. Теперь необходимо сделать ссылку, которая будет указывать на ячейку с выбранной фотографией. Открываем меню Диспетчер имен на вкладке Формулы и создаем еще один именованный диапазон. Имя ссылки будет Фото, а формула

=СМЕЩ(Лист1!$B$2;ПОИСКПОЗ(Выбор;Фотоальбом;0)-1;0;1;1).

Технически, функция ПОИСКПОЗ находит ячейку с нужной моделью в каталоге по названию, а функция СМЕЩ затем выдает ссылку на соседнюю справа от найденного названия ячейку, т.е. ячейку с фотографией товара.

6. Осталось выделить скопированную фотографию на Листе 2 и вписать в строку формул = Фото и нажать ENTER.

 

ЛАБОРАТОРНАЯ РАБОТА 5. БАЗЫ ДАННЫХ (СОРТИРОВКА, ФИЛЬТРАЦИЯ). СВОДНЫЕ ТАБЛИЦЫ И ДИАГРАММЫ

Задание. Представьте себя в роли руководителя отдела продаж. У Вашей компании есть два склада, с которых вы отгружаете заказчикам овощи и фрукты. Для учёта проданного товара в Excel заполняется таблица. В ней каждая отдельная строка содержит полную информацию об одной отгрузке: кто из менеджеров заключил сделку и с каким заказчиком; какого товара и в каком количестве продано; с какого из складов была отгрузка;когда (месяц и день месяца).

Каждый день к этой таблице будет дописываться несколько десятков строк и к концу, например, года или хотя бы квартала размеры таблицы станут огромными. Допустим необходимо создать отчет по этим данным. Например: сколько и каких товаров продали в каждом месяце, какова сезонность продаж, кто из менеджеров сколько заказов заключил и на какую сумму? Ответы на все вышеперечисленные и многие аналогичные вопросы можно получить с помощью инструмента Ms Excel - сводные таблицы.

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

Методические указания

1. Необходимо подготовить в программе Excel таблицу, содержащую следующие поля:

Наименование

Дата

Склад

Цена за тонну

Продано, тонн

Менеджер

Заказчик

Заполните таблицу данными (не менее 30 строк).

Для облегчения заполнения некоторых полей таблицы (Наименование, Склад, Менеджер, Заказчик) используйте выпадающие списки. Для этого предварительно на отдельном листе рабочей книги составьте «справочную» таблицу с перечнем значений полей. Для заполнения цены используйте функцию ВПР.

2. На вкладке Данные щелкните по кнопке Фильтр. Попробуйте разные варианты фильтрации базы данных (по наименованию, по заказчику, по менеджеру, по складу, по дате, по объему продаж). Отмените все фильтры.

3. На вкладке Данные нажмите кнопку Сортировка и примените к базе данных не менее трех уровней сортировки.

4. Скопируйте наименования столбцов базы данных в ячейки I1:O1. В ячейке M2 введите условие отбора, например >=500. Поставьте курсор в любую ячейку базы данных и на вкладке Данные в группе команд Сортировка и фильтр щелкните по кнопке . В открывшемся окне диалога Расширенный фильтр установите переключатель Скопировать результат в другое место. Убедитесь в том, что в поле Исходный диапазон указана вся база данных. В поле Диапазон условий введите I1:O2. В поле Поместить результат в диапазон введите диапазон достаточного размера, чтобы поместились результаты выборки. Нажмите ОК. Посмотрите результат.

5. Очистите результаты фильтрации. Введите другие условия отбора, например, по наименованию товара, фамилии менеджера или названию фирмы-заказчика. Примените новые условия фильтра.

6. Выделите любую ячейку в таблице с исходными данными и нажмите кнопку Сводная таблица на вкладке Вставка. Отобразится диалоговое окно, в котором нужно выбрать источник данных и место вывода сводной таблицы, нажать ОК и перейти к редактированию макета. Определите, на какую сумму было продано каждого из наименований: на панели Список полей сводной таблицы перетащите поле Наименование в область Названия строк. Перетащите поле Продано, тонн в область Значения.

7. Измените оформление сводной таблицы, выбрав другой стиль на ленте Конструктор.

8. Измените сводный отчет таким образом, чтобы можно было определить какому заказчику какое наименование было продано и на какую сумму. Для этого перетащите в область Названия столбцов поле Заказчик.

9. Определите, с какими заказчиками, какие менеджеры работали, и на какую сумму. Удалите из области Названия строк поле Наименование, вместо него поместите поле Менеджер.

10. Перейдите на лист с базой данных. Добавьте внизу таблицы строку с еще одной продажей. Перейдите обратно на лист сводной таблицы (Лист4). Обновите данные сводной таблицы (щелкните по сводной таблице, на вкладке Параметры нажмите на кнопку Изменить источник данных, заново выделите исходную таблицу и нажмите ОК).

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

12. Определите, какие продукты сколько раз продавались. Для этого отобразите в сводной таблице только данные по наименованиям в области строк. Щелкните по полю Сумма по полю Продано в списке Значения, области Списка полей сводной таблицы. Выберите команду Параметры полей значений... В окне Параметры полей значений выберите вид операции - Количество.

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

Отмените расчет по количеству наименований.

13. Добавьте в макет в область Наименования строк поле Склад, разместив его над полем Наименование. Сверните группы с номерами складов (на вкладке Параметры, в области Активное поле нажмите кнопку Свернуть все поле).

Удалите поле Склад из области Наименования строк.

14. Рассчитайте общую сумму проданных наименований товаров.

На вкладке Параметры нажмите кнопку Формулы и выберите команду Вычисляемое поле. В окне Вставка вычисляемого поля задайте имя нового поля: «Стоимость». В поле Формула введите: = 'Цена за тонну'* Продано, тонн.

Нажмите ОК.

Задайте для нового, вычисляемого поля Стоимость денежный, рублевый формат.

Для этого щелкните по полю Стоимость в области Значения списка полей сводной таблицы. Выберите команду Параметры полей значений.

В окне Параметры полей значений нажмите на кнопку Числовой формат.

Задайте в окне Форматы денежный, рублевый формат. Нажмите ОК.

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

16. Перенесите в область Названия столбцов поле Менеджер, в область Фильтр отчета поле Заказчик. Постройте сводную диаграмму. Для этого выделите любую ячейку сводной таблицы. На вкладке Параметры нажмите кнопку Сводная диаграмма. Выберите тип диаграммы – гистограмма. Нажмите ОК.

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

Отобразите данные по всем менеджерам.

Для сводной диаграммы выполните отбор всех наименований, которых было продано на сумму, превышающую 500 тыс.руб.

Отключите фильтр.

18. Сохраните изменения в рабочей книге, дайте ей имя Склад.

Закройте книгу.