РГАУ – МСХА имени К.А. Тимирязева
КАФЕДРА СТАТИСТИКИ
УКОЛОВА А.В.
МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ПРИМЕНЕНИЮ ПРОГРАММЫEXCEL ПРИ КУРСОВОМ ПРОЕКТИРОВАНИИ ПО СТАТИСТИКЕ
МОСКВА – 2005
Исходная информация для выполнения курсового проекта представлена в формате электронной таблицы EXCEL. Желательно не вносить изменений в исходную базу, а создать "рабочую" базу, скопировав исходную информацию на новый лист книги: выделите исходный лист путем нажатия кнопки выделения всего листа – серый прямоугольник в левом верхнем углу листа на пересечении заголовков строк и столбцов, нажмите правую кнопку мыши для вывода контекстного меню и выберите пункт "Копировать", щелкнув левой кнопкой мыши; перейдите на новый лист книги, щелкнув ярлычок листа в нижней части активного листа, затем щелкните кнопку выделения всего листа или поставьте курсор в первую ячейку листа, выведите контекстное меню (правая кнопка мыши) и выберите пункт "Вставить".
Теперь обратите внимание на то, как представлена информация: в левой части базы приведены названия и коды организаций по ОКПО (Общероссийский классификатор предприятий и организаций), в правой части – система показателей: в первой строке – наименования показателей, во второй и третьей – единицы их измерения и коды в субрегистре "Сельскохозяйственные организации, который ведется в рамках ЕГРПО – единого государственного регистра предприятий и организаций.
В самом начале базы, после нескольких общеэкономических показателей, представлен комплекс показателей, характеризующий продажи сельскохозяйственной продукции, а также продукцию переработки собственного сельхоз-сырья. Результаты от реализации продукции в переработанном виде показаны по кодам: 11440-11460 – мяса и мясопродуктов, в том числе по видам животных – крупного рогатого скота (11700, 11710, 11720), свиней (11730,11740,11750), овец и коз (11760,11770,11780); 11610-11630 – молочных продуктов, – по этим продуктам часть стоимости, добавленной переработкой, относится уже не к сельскохозяйственной деятельности, а к обрабатывающим производствам по классификации ОКВЭД (Общероссийский классификатор видов экономической деятельности). Но, поскольку данных о стоимости переработки нет, нельзя выделить чистую отрасль "сельское хозяйство" при определении выручки от продаж, стоимости валовой продукции сельского хозяйства.
|
|
Далее в базе представлена характеристика объемов субсидий, получаемых организациями из бюджетов разных уровней; под кодами 12011-12242 - характеристика затрат по элементам (оплата труда с отчислениями на социальные нужды, материальные затраты, амортизация и прочие затраты) и основным статьям, произведенным в отчетном году по видам деятельности – растениеводство и животноводство. Затраты, произведенные в данном году на производство продукции, могут быть получены как сумма затрат в растениеводстве (12230) и животноводстве (12240) – эти затраты следует применять при расчете показателя стоимости валовой продукции сельского хозяйства, относительных показателей интенсивности.
Затем представлена характеристика процессов производства продукции растениеводства и животноводства показателями размера посевных площадей, (численности поголовья животных), объемы производства продукции и затраты на производство продукции по видам сельскохозяйственных культур и животных. Если поделить соответствующие затраты на объем производства продукции, то получится производственная себестоимость единицы продукции. Суммарные затраты по растениеводству (13450) и животноводству (14830) характеризуют затраты, отнесенные на себестоимость готовой продукции. Общие затраты на производство продукции сельского хозяйства (сумма 12230 и 12240) больше затрат, отнесенных на готовую продукцию, полученную в данном году, (сумма 13450 и 14830) на сальдо незавершенного производства, сумму затрат, не давших продукции, и меньше на стоимость рассады, зеленой массы, использованной на силос и сенаж.
|
|
Следует отметить, что в базе отсутствует показатель стоимости основных средств производства, поэтому нельзя рассчитать такие относительные показатели как фондооснащенность, фондоотдача, фондоемкость. В качестве косвенной оценки можно использовать затраты на амортизацию в расчете на единицу площади, стоимости валовой продукции.
Для получения первичного представления об изучаемой совокупности рекомендуется рассчитать по каждой организации 4-5 относительных признаков, изучить их взаимосвязи и оценить вариацию, ошибки выборки.
Расчет относительных признаков
Поскольку на данном этапе не ясно, какой признак будет использован в качестве группировочного, по которому будет отсортирована вся совокупность организаций, рассчитаем 4-5 наиболее существенных признаков в самой базе. Для этого в начало листа добавим 9-10 столбцов: наведите курсор на название столбца, левее которого вы хотите вставить столбцы, курсор превратится в жирную черную стрелочку, щелкните левой клавишей мыши и выделите нужное число столбцов; не убирая курсора с выделенной области, щелкните правой кнопкой и выберите щелчком мыши пункт "Добавить ячейки". Если потребуется удалить столбцы (строки, ячейки), выделите их и выберите пункт контекстного меню "Удалить ячейки".
|
|
Допустим, для раскрытия темы были выбраны признаки:
· стоимость валовой продукции сельского хозяйства в расчете на 100 га с.-х. угодий, тыс. руб.;
· выручка от реализации продукции сельского хозяйства в расчете на 100 га с.-х. угодий, тыс. руб.;
· затраты на производство продукции сельского хозяйства в расчете на 100 га с.-х. угодий, тыс. руб.;
· рентабельность реализации продукции сельского хозяйства, %;
· среднегодовая численность работников, занятых в с.-х. производстве в расчете на 100 га с.-х. угодий, чел.
Чтобы рассчитать названные относительные показатели, необходимо получить по каждой организации абсолютные показатели стоимости валовой продукции, затрат на производство продукции сельского хозяйства.
Стоимость валовой продукции сельского хозяйства найдем как сумму затрат на производство продукции сельского хозяйства, прибыли от реализации продукции сельского хозяйства и субсидий. Определим эти показатели по каждому хозяйству.
Рассчитаем в первом свободном столбце (D) (рис. 1) затраты на производство продукции сельского хозяйства. В ячейке D1 напишите название показателя, затем поставьте курсор в ячейку D4, два раза щелкните в ней левой кнопкой мыши – курсор превратиться в мигающую вертикальную полоску, теперь можно записать формулу для расчета показателя: поставьте знак "=", этот же знак появится и в командной строке, где будет отражаться вводимая формула, (рис. 1) и выделите курсором ячейку, содержащую по соответствующему хозяйству затраты на производство продукции растениеводства (код показателя 12230) – по периметру ячейки замигают пунктирные линии (рис. 2); затем нажмите знак "+" и выделите ячейку содержащую затраты на производство продукции животноводства (код 12240) по данному хозяйству и нажмите клавишу "ENTER" – получите по первому в базе хозяйству затраты на производство продукции сельского хозяйства (рис. 3).
Рис. 1 Рис. 2
Чтобы автоматически произвести расчет по оставшимся хозяйствам, соедините курсор с черным квадратиком в нижнем правом углу ячейки с рассчитанной величиной – получится тонкий черный крестик, нажмите левую клавишу мыши и потяните за крестик вниз (рис. 4) до ячейки последнего хозяйства в базе включительно, затем отпустите клавишу – затраты на производство продукции сельского хозяйства автоматически рассчитаются по всем организациям.
Рис. 3 Рис. 4
|
В следующем столбце (Е) рассчитаем по каждому хозяйству прибыль от реализации продукции сельского хозяйства как разность между выручкой (11680) и полной себестоимостью (11670); в столбце F – сумму всех субсидий. Теперь рассчитаем в столбце G стоимость валовой продукции как сумму первых трех показателей (рис. 5, формула в командной строке).
Рис. 5. Расчет стоимости валовой продукции 
Данный способ позволяет быстро и достаточно точно определить объемы валовой продукции, но не лишен недостатков: прибыль может быть получена от продажи продукции, произведенной в данном году и ранее, а затраты же учитываются по продукции текущего и частично следующего года, поэтому по убыточным предприятиям, продававшим продукцию прошлых лет и текущего года с убытком, его абсолютная величина может оказаться больше текущих затрат, и стоимость валовой продукции окажется отрицательной. Организации с отрицательной стоимостью валовой продукции следует удалить из рабочей базы как нетипичные, отметив в курсовом проекте причины исключения.
В следующих столбцах рассчитаем намеченные относительные признаки (рис. 6).
Рис. 6. Расчет относительных признаков
Числовые значения одного и того же признака по всем хозяйствам должны иметь одну и ту же разрядность, для увеличения (уменьшения) которой следует выделить необходимый диапазон числовых данных и нажать соответствующую кнопку на панели инструментов: 
– "Увеличить разрядность", 
– "Уменьшить разрядность".
Для удобства изучения взаимосвязи между признаками нужно проранжировать (произвести сортировку по возрастанию) хозяйства по одному из них, наиболее важному для раскрытия темы, в нашем случае – это стоимость валовой продукции. Чтобы иметь возможность вернуть базу в исходное состояние, увеличить число признаков для идентификации хозяйств, пронумеруйте хозяйства в первом столбце (рис. 7). Для автоматического заполнения рядом натуральных чисел от 1 до 30 нужно набрать в первых двух ячейках цифры 1, 2 соответственно; затем выделить эти ячейки и потянув за крестик в правом углу произвести заполнение следующих 28 ячеек. Полученные номера будем считать номерами хозяйств в исходной базе.
Для проведения сортировки следует выделить строки листа с числовыми значениями (с 4 по 33), затем выбрать пункт "Сортировка" меню "Данные" – появится окно "Сортировка диапазона" (рис. 7), выберите идентификацию по обозначениям столбцов, а затем в предлагаемом списке пункта "Сортировать по" выбрать столбец с нужным признаком (в нашем случае это столбец В), затем нажать кнопку "ОК" – все хозяйства базы будут отсортированы по возрастанию стоимости валовой продукции.
Теперь оформим рассчитанные показатели в отдельной таблице. Каждую таблицу для удобства форматирования лучше всего рассчитывать и оформлять на отдельном листе, после окончательного редактирования ее можно скопировать и вставить в текстовый редактор "WORD".
Рис. 7. Сортировка по одному признаку
Оформим таблицу 1 на новом листе (чтобы добавить, удалить, переименовать лист нужно на ярлычке листа щелкнуть правой кнопкой мыши для появления контекстного меню и выбрать нужную операцию). Во-первых, нужно разработать макет таблицы, сделать это просто, поскольку система признаков уже определена. Все признаки, за исключением рентабельности, рассчитаны на 100 га сельхозугодий, поэтому их следует объединить общим подзаголовком, в начало таблицы добавьте графы с номером по ранжиру и номером хозяйства в исходной базе (рис. 8).
Рис. 8. Формирование таблицы 1
Числовые значения признаков уже рассчитаны, поэтому их нужно перенести на данный лист, но при этом лучше не использовать функцию копирования, а обеспечить связку, для этого следует поставить знак "=" в нужной ячейке (по стоимости валовой продукции в расчете на 100 га с.-х. угодий по первому хозяйству в таблице – ячейка С4), перейти на лист с рабочей базой, выделить искомое значение (данное значение было определено в ячейке Н4, см. формулу в командной строке на рис. 7) и нажать "ENTER". Затем автоматически можно заполнить всю таблицы, скопировав формулу влево и вправо. Если перенести информацию таким способом, то изменения в рабочей базе будут отражаться и в табл. 1. Таким же образом перенесите номера хозяйств из исходной базы.
Теперь можно провести окончательное форматирование таблицы. Если основной текст курсового проекта должен быть набран шрифтом Times New Roman размером 14 pt, то при оформлении числовой части таблиц можно использовать 12, а текстовой – 10 pt. Названия граф должны быть выравнены по центру как по вертикали, так и по горизонтали; числовые значения – по вертикали – по нижнему краю, по горизонтали – по правому. В таблице не должно быть лишних поперечных линий. Чтобы отформатировать шапку таблицы, выделите ее и выведите контекстное меню нажатием правой кнопки, выберите пункт "Формат ячейки". Обязательно выберите вариант отображения "переносить по словам". Отдельно отформатируйте числовую часть таблицы (рис. 9).
Отрегулируйте ширину столбцов, чтобы таблица поместилась на лист формата А4 с верхним полем 2,5 см, нижним – 2, левым – 3, правым – 1 см. Для установки размеров полей можно воспользоваться пунктом "Параметры страницы" меню "Файл", в результате лист разобьется на страницы пунктирной линией и будет видно, не выходят ли отдельные графы таблицы за пределы полей.
Рис. 9. Фрагмент форматирования числовой части таблицы
Теперь таблицу можно перенести в Word и провести анализ взаимосвязей между признаками, определиться, по какому из них будет проводиться группировка. По выбранному признаку целесообразно оценить показатели вариации и ошибки выборки.