Лабораторная работа по статистике

Задача 1. Автоматизация учета данных на примере автотранспортного предприятия.

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

1. В начале дня или заблаговременно диспетчер в соответствии с планом распределения клиентуры выписывает путевой лист на конкретный автомобиль, определенному водителю. Или двум водителям на две смены работы автомобиля. Указывается номер путевого листа, табельный номер водителя, номер водительского удостоверения, гаражный номер автомобиля, дата, показание спидометра при выезде, остаток топлива в баке, маршрут движения, плановое количество рейсов, план дохода в рублях, количество выданного топлива в рублях;
2. Водитель утром, получив путевой лист, проходит медицинский осмотр с соответствующей отметкой в путевом листе. Затем водитель на автомобиле подъезжает к контрольно-пропускному пункту (КПП) для ежедневного технического осмотра. Здесь механик КПП заносит в путевой лист время выезда;
3. В течение рабочего дня водителем и клиентами в путевой лист делаются отметки о количестве выполненных рейсов, перевезенных грузов и пассажиров, фактических доходах, линейных отказах и неисправностях, времени устранения отказов, фактически заправленном топливе;
4. При возвращении с линии на КПП механиком делаются отметки о времени прибытия, показании спидометра, количестве топлива в баке. После чего путевой лист сдается обратно диспетчеру.

Решение.

Приведем набор справочников данных, необходимых для решения задачи:

• Персонал -  содержит справочник с данными водителей;
• Парк автомобилей – содержит параметры автомобилей в автомобильном парке;
• Марки автомобилей – содержит справочник марок автомобилей;
• Маршруты движения – справочник описывает маршруты, которые совершают водители;
• Данные для путевых листов -  содержит оперативные данные для путевых листов и заполняется на основе остальных справочников.

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

Справочник персонал:

Название атрибута	Тип данных
ФИО	Текстовый
Табельный №	Числовой
Номер отряда	Числовой
Номер бригады	Числовой
Телефон	Номер телефона
Адрес	Текстовый
Год рождения	Дата

 

Справочник Парк автомобилей

Название атрибута	Тип данных
Гаражный номер	Числовой
Код марки	Числовой
Номер отряда	Числовой
Номер бригады	Числовой

 

Справочник Марки автомобилей

Название атрибута	Тип данных
Код марки	Числовой
Марка	Текстовый
Норма расхода топлива	Числовой
Тариф заработной платы	Числовой

 

Справочник Маршруты движения

Название атрибута	Тип данных
Номер маршрута	Числовой
Вид перевозок	Текстовый
Протяженность	Числовой (вещественный)
Наименование	Текстовый
План дохода на рейс	Числовой
Время движения	Числовой (вещественный)

 

Данные путевого листа

Название атрибута	Тип данных
Дата	Дата
Табельный №	Числовой
ФИО	Текстовый
Код марки	Числовой
Марка	Текстовый
Норма расхода топлива	Числовой
Тариф заработной платы	Числовой
Номер маршрута	Числовой
Вид перевозок	Текстовый
Протяженность	Числовой (вещественный)
Время движения	Числовой (вещественный)
План дохода на рейс	Числовой
Количество рейсов	Числовой

 

Откройте табличный процессор Эксель, создайте новую книгу и сохраните ее под именем «Учет на предприятии».

Переименуйте листы книги в следующей последовательности: «Персонал», «Парк», «Марки», «Маршруты», «ДанныеПЛ».

Откройте лист Персонал и заполните первую строку по образцу

Используя таблицы атрибутов для справочников данных, заполните листы «Парк», «Марки», «Маршруты», «ДанныеПЛ».

Перейдите на лист Персонал. Выделите мышью ячейки с A2 по A16 и нажав левую кнопку мыши выберите пункт «Формат ячеек». На вкладке «Число» выберите формат «Текстовый». Для ячеек B2:B16 задайте формат «Числовой» с нулевым количеством разрядов после запятой. Аналогично поступите с диапазонами C2:c16 и D2:D16. Ячейки E2:E16 должны иметь формат «Номер телефона» который можно найти в дополнительных форматах. Ячейки F2:F16 должны иметь текстовый формат. А G2:16 – формат ячеек «Дата». Очевидно, что значения табельного номера, номера отряда и номера бригады являются целыми неотрицательными числами. Установим на данные ячейки ограничение на ввод только целых чисел. Для этого выделите ячейки с B2 по D16 и перейдя на ленте быстрого доступа на вкладку Данные нажмите кнопку «Проверка данных».

Установите тип данных «Действительное» и нажмите ОК. Для ячеек G2:G16 введите ограничение на ввод только даты.

Согласно типам данных в таблицах справочников определите форматы столбцов на листах «Парк» (10 строк), «Марки» (10 строк), «Маршруты» (20 строк), «ДанныеПЛ» (30 строк).

Заполните лист Персонал, введя данные на 10 водителей. Учитывайте то, что табельный номер для каждого водителя уникален. Пример заполнения:

Заполните на листе «Парк» 10 строк. Учтите, что номера отрядов и бригад не должны отличаться от номеров отрядов и бригад введенных на листе Персонал. Пример заполнения

Заполните на листе Марки 10 строк. Код марки является уникальным идентификатором, однако сами марки могут повторяться. Пример заполнения

Заполните лист «Маршруты» введя 20 строк. Номер маршрута должен быть уникален. Вид перевозок определяйте как междугородние и городские.

Перейдем к заполнению листа «ДанныеПЛ». Значения некоторых атрибутов будут заполняться автоматически. Рассмотрим встроенную функцию Эксель ПРОСМОТР().

Наиболее часто функция ПРОСМОТР() используется в векторном формате и имеет следующий прототип

ПРОСМОТР(искомое значение; просматриваемый вектор; [вектор результатов]), где Искомое значение - значение, которое функция ПРОСМОТР ищет в первом векторе. Просматриваемый вектор - диапазон, состоящий из одной строки или одного столбца. Вектор результатов -диапазон, состоящий из одной строки или столбца. Вектор результатов должен иметь тот же размер, что и просматриваемый вектор.

Заполним значения атрибута ФИО на листе «ДанныеПЛ» по табельному номеру водителя. Кроме того необходимо предвидеть случай, когда значение табельного номера не заполнено.

Сама функция поиска ФИО по табельному номеру примет вид:

=ПРОСМОТР(B2;Персонал!$B$2:$B$11;Персонал!$A$2:$A$11). Чтобы обеспечить проверку на пустое значение табельного номера воспользуемся функцией ЕСЛИ(). Окончательно в ячейку C2 листа «ДанныеПЛ» необходимо ввести формулу:

=ЕСЛИ(B2="";"";ПРОСМОТР(B2;Персонал!$B$2:$B$11;Персонал!$A$2:$A$11)).

После ввода формулы, растяните значение ячейки C2 на 30 строк.

Для столбцов Марка, Норма расхода топлива, Тариф заработной платы введите формулы, определяющие марку автомобиля, норму расхода и тариф по Коду марки. Растяните формулы на 30 строк.

Для столбцов Вид перевозок, Протяженность, Время движения, План дохода на рейс введите формулы, определяющие данные параметры по Номеру маршрута. Растяните формулы на 30 строк.

В путевом листе ячейки столбца Дата и Табельный номер вволятся пользователем, ячейки столбца ФИО вычисляются автоматически. Для того чтобы защитить ячейки ФИО от изменения зайдите на вкладку «Рецензирование» панели управления. Определим изменяемые ячейки. Нажмите кнопку «Разрешить изменения диапазонов». В открывшемся диалоговом окне создайте новый диапазон и введите в него ячейки с A2 по B31. Пароль вводить не надо.

По аналогии определите остальные изменяемые диапазоны. Нажмите ОК, а затем запретите изменения на листе нажав кнопку «Защитить лист» на вкладке «Рецензирование».

 

 

Задача 2. Учет материальных ценностей средствами  Эксель.

Базовый подход учета материальных ценностей в Эксель строится на ведении четырех листов.

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

На втором листе вводятся данные расходных документов.

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

 Четвертый лист предназначен  для формирования проводок по  отраженным в таблицах операциям.

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

Решение. Создайте новую книгу Эксель и переименуйте ее в «УМЦ». Присвойте четырем листам книги имена «Приход», «Расход», «Обороты», «Проводки».

На каждом из листов создайте атрибуты будущих таблиц:

Для листа «Приход»

Название атрибута	Тип данных
Код	Текстовый
Наименование	Текстовый
Количество	Числовой
Стоимость единицы	Числовой
Сумма по документу	Числовой
Включает НДС?	Логический
Сумма без НДС	Числовой
НДС	Числовой

 

Для листа «Расход»

Название атрибута	Тип данных
Код	Текстовый
Наименование	Текстовый
Количество	Числовой

 

Для листа «Обороты»

Название атрибута	Тип данных
Код	Текстовый
Наименование	Текстовый

Для листа «Проводки»

Название атрибута	Тип данных
Дебет	Текстовый
Кредит	Текстовый
Сумма	Числовой
Комментарий	Текстовый

 

Отформатируйте ячейки всех листов согласно указанным типам данных.

Начиная с четвертой строки (вторая и третья строка понадобятся далее) введите не менее 15 материальных ценностей с указанием их кодов и наименований. Материальные ценности должны подразделяться на не менее три категории. Пример заполнения:

Перейдите на лист «Приход». В столбец А вносятся коды учитываемых объектов. Он форматируется как «текстовый». Коды должны вводиться на основании фактурных частей приходных документов и в соответствии с кодированием, определенным в справочнике. Мы строим таблицу таким образом, чтобы в столбце В автоматически появлялись наименования объектов учета из справочника с листа «Обороты».