Главная | Обратная связь | Поможем написать вашу работу!

База данных товарно-транспортных накладных

1. На чистом листе новой книги Excel запишите заголовок «Товарно-транспортные накладные за ноябрь».

2. Наберите шапку списка накладных (рис. 1). Надписи для каждой колонки будут названиями полей базы данных. Последняя колонка «Q, т*км» рассчитывает транспортную работу как произведение данных из колонок «Пробег, км» и «Перевезено, т».

Рис. 1. Пример базы данных товарно-транспортных накладных

3. Введите примерно 8 товарно-транспортных накладных как показано на рис. 1. Замечание. Для правильного распознавания границ базы данных недопустимо добавлять внутри базы пустые строки и столбцы.

4. Проведите сортировку базы данных по Клиентам, а внутри одного клиента по номеру накладной. Для этого выполните команду меню Данные à Сортировка и в диалоговом окне задайте параметры сортировки. Но прежде проверьте, чтобы текущей ячейкой была одна из ячеек, принадлежащая базе. Это необходимо, чтобы правильно была распознана база данных, для которой будет проводиться операция сортировки.

5. Проведите сортировку базы данных по Гаражному номеру автомобиля и по номеру накладной.

6. Установите автофильтр для базы. Для этого выполните команду меню Данные à Фильтр à Автофильтр. В шапке таблицы появятся кнопки автофильтра (рис. 2). С их помощью можно выполнить отбор записей, удовлетворяющих определенному признаку. Например, все записи, относящиеся к клиенту «Сенако АО» (рис.2). Для отключения автофильтра выполните команду меню Данные à Фильтр à Отобразить все.

Рис. 2. Пример использования автофильтра

7. Подведите итоги работы за месяц по товарно-транспортным накладным, группируя данные по клиентам, подводя по клиентам промежуточные итоги. Для этого отсортируйте базу по клиентам, а затем выполните команду меню Данные à Итоги. В диалоговом окне установите параметры, показанные на рис. 3. Полученный результат показан на рис. 4.

Рис. 3. Параметры для подведения итогов по Клиентам

Рис. 4. Итоги по Клиентам

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

СВОДНЫЕ ТАБЛИЦЫ

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

Сводная таблица может быть создана на основании данных находящихся:

- в списке или базе данных Microsoft Excel;

- во внешнем источнике данных;

- в нескольких диапазонах консолидации;

в другой сводной таблице.

Каждая сводная таблица состоит из 4 областей: страница, строка, столбец, данные.

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

- Cтрока. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков строк в сводной таблице. Если в эту область помещено одно поле, то количество строк в сводной таблице (без учета итогов) равно числу уникальных значений этого поля.

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

- Данные. Значения полей, помещенных в эту область, используются для заполнения ячеек сводной таблицы итоговыми данными (суммирование, подсчет количества, вычисление среднего значения и т. д.).

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

Задание 14.

На основании следующей таблицы:

Менеджер	Месяц	Продукты	Доход	Расход	Прибыль	Регион
Иванов	январь	мясо	100,00	50,00		Страны СНГ
Иванов	февраль	мясо	100,00	50,00		Россия
Иванов	февраль	мясо	100,00	50,00		Россия
Иванов	апрель	мясо	100,00	50,00		Россия
Иванов	апрель	мясо	100,00	50,00		Россия
Петров	январь	мясо	100,00	50,00		Страны СНГ
Петров	февраль	мясо	100,00	50,00		Страны СНГ
Петров	февраль	мясо	100,00	50,00		Страны СНГ
Петров	апрель	мясо	100,00	50,00		Страны СНГ
Петров	апрель	мясо	100,00	50,00		Страны СНГ
Сидоров	май	рыба	100,00	50,00		Страны СНГ
Сидоров	январь	рыба	100,00	50,00		Россия
Иванов	февраль	рыба	100,00	50,00		Россия
Иванов	март	молоко	200,00	20,00		Россия
Петров	март	молоко	300,00	30,00		Страны СНГ
Сидоров	март	молоко	150,00	100,00		Страны СНГ

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

Выполнение.

Рассчитайте значение поля «Прибыль», записав соответствующую формулу.

Сделайте текущей любую ячейку построенного списка.

Выполните команды Данны е и Сводная таблица.

Установите флажок – В списке или базе данных Microsoft Excel;

Укажите диапазон, содержащий построенный список. Если список был построен правильно, нужный диапазон будет выбран автоматически.

Перетащите кнопки «Продукция» и «Менеджер» в область «Строка». При этом важен порядок перетаскивания – поле «Менеджер» будет вложенным по отношению к полю «Продукция». Затем в область «Столбец» перетащите кнопку «Месяц» и в область страниц – кнопку «Регион». В область данных перетащите кнопку «Прибыль».

Укажите место размещения сводной таблицы.

Построенная сводная таблица будет иметь следующий вид:

Задание 15.

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

Выполнение.

Скопируйте сводную таблицу задания 1 на другой лист или повторите процесс ее построения. Можно также создать копию листа со сводной таблицей.

Отметьте диапазон A4:C15. Для этого достаточно сделать текущей ячейку С4 (выделится столбец сводной таблицы за январь месяц) и нажав клавишу «Shift» щелкнуть по ячейке E4.

Выполните команды «Данные» «Группа и структура» «Группировать». В поле столбца появиться новое поле «Месяц 2» и в сводную таблицу добавится строка, в которой для выделенных трех столбцов присвоится название «Группа 1».

Выполните аналогичные действия для столбцов сводной таблицы за апрель и май месяцы. Для этих столбцов должно появится название «Группа 2».

Удалите поле месяц Для этого вызовите контекстное меню или перетащите его из области сводной таблицы.

Исправьте название «Месяц 2» на «Квартал», «Группа 1» – на «Первый», «Группа 2» – на «Второй».

Полученная таблица должна иметь следующий вид:

Задание 16.

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

Задание 17.

На основании книги «Участники олимпиады» подсчитать количество участников набравших во втором туре 0–4 балла, 5–9 баллов и т. д. по 5 баллов в группе. Постройте диаграмму, показывающую процентное распределение участников по указанным группам.

Выполнение

Постройте сводную таблицу, поместив в область строк поле «Балл», а в область данных поле «Фамилия». Получится сводная таблица из 29 строк, которая показывает количество участников набравших конкретное число баллов.

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

В появившемся окне, установите значение поля «С шагом» равным 5.

Постройте круговую диаграмму по полученной сводной таблице.

Задание 18

На основании построенного списка в задании 14:

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

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

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

4. Построить таблицу, показывающую объем прибыли по регионам.

5. Построить диаграмму изменения суммарной прибыли по регионам по месяцам (январь, февраль, март, апрель, май).

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

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

КОНСОЛИДАЦИЯ РАБОЧИХ ТАБЛИЦ

Под термином консолидация подразумевается ряд стандартных операций с несколькими рабочими таблицами и рабочими книгами. В некоторых случаях консолидация может включать в себя создание связанных формул. Основной фактор, влияющий на консолидацию данных – это способ размещения информации в рабочих таблицах. Если размещение информации во всех таблицах одинаково, то говорят о консолидации по позиции. В том случае, когда размещение информации не идентично, но достаточно похоже, то можно объединить данные по заголовкам строк и/или столбцов. Такая консолидация называется консолидацией по категориям. Если же рабочие таблицы имеют мало общего друг с другом, то необходимо отредактировать листы, чтобы они стали единообразными.

Задание 19.

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

Филиал № 1

Название товара	Январь	Февраль	Март
А–995
В–123
А143
В–123
С–070
Д–060
Е–130
Ф–270
Т–234
М–235

Филиал № 2

Название товара	Январь	Февраль	Март
Т–234
В–123
Р–234
А143
В–123
С–070
Д–060
Е–130
Ф–270
У–111
К–254

Филиал № 3

Название товара	Январь	Февраль	Март
А–995
В–123
А143
Р–234
В–123
С–070
Д–060
Е–130
Ф–270
К–254

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

Выполнение.

Для выполнения данного задания необходимо:

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

2. Создайте новую рабочую книгу (выберите новый рабочий лист), где должны размещаться результаты консолидации. Выполните команду Данные / Консолидация.

3. Задание параметров для диалогового окна «Консолидация»

3.1. В поле «Функция» укажите функцию Сумма, которая показывает тип объединения данных.

3.2. В поле «Ссылка» введите ссылку на диапазон первой рабочей таблицы, которые должны быть консолидированы. Если нужная книга закрыта, щелкните по кнопке «Обзор», чтобы найти нужный файл на диске. Ссылка может задавать диапазон больший, по числу строк, чем нужно консолидировать, но в случае добавления новых строк, параметры консолидации не нужно будет изменять. Когда в поле «Ссылка» будет введена нужная ссылка, щелкните по кнопке «Добавить», чтобы добавить ее к списку диапазонов.

3.3. Ведите ссылку на диапазон второй рабочей таблицы и добавьте ее к списку диапазонов. Выполните указанное действие для остальных диапазонов консолидации

3.4. Так как способы размещения информации в рабочих таблицах различны, установим опции Подписи верхней строки и Значения левого столбца. В результате Excel будет подбирать данные по заголовкам.

3.5. Для того, чтобы консолидация была динамической, установим опцию Создавать связи с исходными данными и нажмем. кнопку «ОК». В результате Excel создаст структуру, содержащую внешние ссылки.

4. Построить требуемую диаграмму.

Задание 20.

Отредактировать исходные данные первого задания так, чтобы диапазоны консолидации стали идентичными. Провести консолидацию этих данных:

- используя формулы, содержащие внешние ссылки. Для задания внешней ссылки используется формат:

=[Имя_рабочей_книги]Имя_листа!Адрес_ячейки

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

=’[Бюджет на 2001 год]Лист1’!A1

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

- с помощью команд Правка / Специальная вставка. Этот метод применим, если все используемые рабочие таблицы открыты. Недостатком этого метода является то, что консолидация получается нединамической (статическая консолидация). Скопируйте данные из первого диапазона исходной рабочей таблицы в буфер обмена. Активизируйте зависимую рабочую книгу и выберите ячейку, в которую нужно поместить консолидированные данные. Выполните команду Правка / Специальная вставка, отметьте переключатель сложить и щелкните по кнопке ОК. Выполните эти действия для всех диапазонов рабочих таблиц, которые должны быть консолидированы.

- с помощью команд Данные / Консолидация.

⇐ Предыдущая 12