Работа 1. Управление данными

Постановка задачи

Создать фрагмент системы подготовки и управления данными торговой фирмы, включающий в себя:

─ оформление списков клиентов фирмы,

─ оформление списков товаров, предлагаемых фирмой,

─ оформление списков заказов на поставку на основе электронного бланка-заказа,

─ консолидацию данных о продажах по нескольким филиалам,

─ оценку наиболее популярного товара по всем филиалам,

─ определение наиболее успешно работающего филиала,

─ определение товара имеющего наибольший оборот и долю в общих продажах и т.п.

Инструментарий:

Работа с таблицей как с базой данных (формы, фильтры, имена полей), функции ЕСЛИ, ПРОСМОТР, Сводные таблицы, Консолидация.

Решение задачи

1. Создадим список клиентов фирмы в соответствии с приведенной таблицей на рабочем листе Клиенты.

 

 

 

2. Заголовки таблицы должны быть отцентрированы и слова в них должны переноситься по словам.

 

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

Для присвоения имени Фирма столбцу A нужно выделить его щелчком правой клавиши мыши на заголовке и присвоить название столбцу. Столбцу B – Код и столбцу I – Скидка.

 

 

 

3. Создадим список товаров в соответствии с приведенным образцом таблицы. Этот список будет содержать данные о предлагаемых фирмой товарах. Каждому товару присвоим определенный номер, что позволит в дальнейшем автоматизировать некоторые операции. Рабочий лист назовем Товары. Столбцам A, B, C присвоим соответственно имена – Номер, Товар, Цена.

 

4. Создадим список заказов, который позволит помочь в обработке данных обо всех выполненных заказах с начала текущего года.

 

 

Список создадим на рабочем листе Заказы.

Для столбцов B, C, D, E, F, G, H, I, J, K, L зададим имена соответственно – Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2, Оплата.

5. В ячейке E2 должно быть представлено наименование товара, оно вводится автоматически с помощью следующей формулы: =ЕСЛИ($D2="";"";ПРОСМОТР($D2;Номер;Товар)). В остальные ячейки столбца эта формула копируется.

Функция ЕСЛИ проверяет содержимое ячейки D2. Если в ней данные отсутствуют, то E2 тоже останется незаполненной. Если в D2 введен номер товара, то будет выполняться поиск номера товара в диапазоне Номер и в ячейку D2 возвратится соответствующее значение диапазона Товар.

Аналогичные формулы будут для ячеек цены, названия фирмы, скидки, а для суммы заказа и суммы оплаты будут расчетные формулы. Все эти формулы сведены в таблицу:

 

В ячейке	Имя	Ввести формулу
G2	Цена	=ЕСЛИ(D2="";"";ПРОСМОТР($D2;Номер;Цена))
I2	Название фирмы	=ЕСЛИ($H2="";"";ПРОСМОТР($H2;Код;Фирма))
J2	Сумма заказа	=ЕСЛИ($H2="";"";F2*G2)
K2	Скидка	=ЕСЛИ($H2="";"";ПРОСМОТР($H2;Код;Скидка))
L2	Сумма оплаты	=ЕСЛИ($J2="";"";J2-J2*K2)

 

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

 

6. Создадим бланк-заказ. Образец его приведен на рисунке:

 

Чтобы при заполнении заказа информация вставлялась автоматически, занесем формулы в соответствующие ячейки:

 

В ячейке	Ввести формулу
E5	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;фирма2))
I5	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;код2))
E7	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;товар2))
I7	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;номер2))
E9	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;количество))
H9	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;цена2))
E11	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;сумма))
I11	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;скидка2))
D13	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;оплата))

 

7. Выполним анализ данных с помощью сводной таблицы. Сводные таблицы создаются из отдельного списка или базы данных. Новому рабочему листу присвоим имя Таблица и выполним команду Данные – Сводная таблица. На первом шаге работы мастера сводных таблиц в качестве источника данных выберем В списке базы данных MS EXCEL.

8. Далее укажем диапазон, содержащий исходные данные (Заказы!$A$1:$L$11). На третьем шаге мастера определяем структуру сводной таблицы, она подтягивается из настройки панели быстрого доступа как на рисунке.

 

 

 

 

Элементы списка нужно перетаскивать мышью на подходящее поле. По полученной таблице можно сделать вывод о том, какой товар имеет наибольший оборот.

 

 

9. Введем таблицы с данными о работе филиалов фирмы. Предположим, что филиалов три. Присвоим листам названия в соответствии с названиями городов Киев, Минск и Рига. Скопируем на все листы таблицу Товары (используя группировку листов[1]), добавим столбцы Количество заказов, Проданное количество и Объем продаж. Заполним их данными. Объем продаж – расчетная графа.

Таким образом, на трех листах будет введена одинаковая информация. Некоторые данные по количеству проданных компьютеров на листах Рига и Минск можно изменить. Получится примерно такого вида три таблицы на разных листах:

10. Выполним консолидацию данных по филиалам. Для этого скопируем с рабочего листа Киев ячейки A3:B14 на новый лист с названием Итог. Поместив курсор в ячейку С3, выполним команду Данные – Консолидация. Выберем функцию Сумма. С помощью поля Ссылка и кнопки Добавить подберем диапазоны ячеек D3:F14 с листов всех филиалов. Установим опцию Создавать связи с исходными данными.

11. Добавим в полученную таблицу строку Всего и столбец Доля для определения значений долей в общем объеме продаж. Результат консолидации будет такой:

 

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

12. Построим сводную таблицу на основе диапазонов консолидации для того, чтобы определить какой товар как продавался и какой филиал работал наиболее успешно. Дав следующему листу имя Таблица1, поместим курсор в левую верхнюю ячейку и подтянем Мастер сводных таблиц и диаграмм.

 

 

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

 

 

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

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

 

 

 

Главная особенность сводной таблицы – ее интерактивность. Используя это свойство можно, например, переместить мышью поле Строка в область страницы, а поле Страница1 – в область строк. В результате получится новый вид таблицы, из которой можно сделать вывод о наиболее успешно работающем филиале:

 

 

[1] Группировка рабочих листов – щелчок на ярлыке первого листа (например, Киев), нажать клавишу SHIFT и щелкнуть на ярлыке последнего листа.