Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
12
РГАУ – МСХА имени К.А. Тимирязева
КАФЕДРА СТАТИСТИКИ
УКОЛОВА А.В.
МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ПРИМЕНЕНИЮ ПРОГРАММЫ 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 столбцов: наведите курсор на название столбца, левее которого вы хотите вставить столбцы, курсор превратится в жирную черную стрелочку, щелкните левой клавишей мыши и выделите нужное число столбцов; не убирая курсора с выделенной области, щелкните правой кнопкой и выберите щелчком мыши пункт "Добавить ячейки". Если потребуется удалить столбцы (строки, ячейки), выделите их и выберите пункт контекстного меню "Удалить ячейки".
Допустим, для раскрытия темы были выбраны признаки:
Чтобы рассчитать названные относительные показатели, необходимо получить по каждой организации абсолютные показатели стоимости валовой продукции, затрат на производство продукции сельского хозяйства.
Стоимость валовой продукции сельского хозяйства найдем как сумму затрат на производство продукции сельского хозяйства, прибыли от реализации продукции сельского хозяйства и субсидий. Определим эти показатели по каждому хозяйству.
Рассчитаем в первом свободном столбце (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 и провести анализ взаимосвязей между признаками, определиться, по какому из них будет проводиться группировка. По выбранному признаку целесообразно оценить показатели вариации и ошибки выборки.
Расчет показателей вариации и ошибок выборки
Расчет показателей вариации можно провести на том же листе ECXEL, где находится табл. 1. Поскольку оценить требуется различия между организациями, расчет проведем в форме простых величин, а не взвешенных. Именно в такой форме представлены в ECXEL встроенные статистические функции для расчета средней арифметической, дисперсии, стандартного (среднего квадратического) отклонения ("Вставка", "Функция", категория – "Статистические"). Для расчета средней простой используется функция "СРЗНАЧ", ее можно самостоятельно ввести в ячейку как обычную формулу: "=срзнач()", поставить курсор между скобками и выделить необходимый диапазон данных. Для расчета дисперсии по выборке встроена функция "ДИСП", среднего квадратического отклонения - "СТАНДОТКЛОН". Среднее квадратическое отклонение можно также найти как корень из дисперсии ("=корень()", или "=()^0,5") (рис. 10).
Рис. 10. Расчет показателей вариации и ошибок выборки
При расчете ошибок выборки поправкой на конечность совокупности () можно пренебречь, поскольку она близка к единице при выборке из 30 хозяйств по региону. Для оценки уровня ошибок можно найти их отношения к среднему значению, обычно в статистических исследованиях считается допустимым 5-10% уровень предельной ошибки для распространения выборочных данных на генеральную совокупность. Но, поскольку в настоящее время организации сильно дифференцированы, о чем свидетельствует коэффициент вариации (78,2 %), для выявления статистических закономерностей, которые являются целью курсового проектирования, уровень ошибок является приемлемым.
Высокая степень вариации вызывает необходимость проведения группировки.
Группировка и сводка статистических данных
Чтобы выяснить характер изменения выбранного группировочного признака, нужно построить ранжированный ряд и его график. Поскольку в качестве группировочного в данном примере выбрана стоимость валовой продукции в расчете на 100 га с.-х. угодий, то ранжированный ряд по этому признаку уже построен в табл. 1. Для построения графика перенесем ранжированный ряд на новый лист (назовем его, допустим, "огива"), связав его с табл.1 (ссылка – "=" и автоматическое заполнение), а для автоматической подписи рисунка добавим к названию "Рис. 1" (рис. 11).
Рис. 11. Окно мастера диаграмм
Затем нужно выделить диапазон данных (вместе с названием), вызвать функцию "Мастер диаграмм", нажав элемент меню "Вставка", а затем "Диаграмма"; или, щелкнув значок на панели инструментов. Для построения огивы выберите стандартные графики и нажмите кнопку "Готово" – в этом случае график добавится на тот же лист (рис. 12).
Рис. 12. Результат построения графика
Чтобы сделать невидимой рамку диаграммы, нужно выделить область диаграммы, нажать правую клавишу мыши – появится контекстное меню (рис. 13), выбрать пункт "Формат области диаграммы", "Вид" и переставить макрос рамки в положение "невидимая".
Рис. 13. Форматирование диаграммы
Чтобы убрать поперечные линии, выберите пункт "Параметры диаграммы", "Линии сетки" и снимите флажок "основные линии" по оси Y (рис. 13).
Чтобы снять заливку поля графика, нужно выделить его и вывести контекстное меню (рис. 14), выбрать пункт "Формат области построения" и переставить пкреключатели в полях "Рамка" и "Заливка" в положения "невидимая" и "прозрачная" соответственно.
Рис. 14. Форматирование поля графика
В результате получится график, представленный на рис. 15.
Рис. 15. Окончательный вариант форматирования
При достаточно плавном изменении группировочного признака можно использовать промежуточную аналитическую группировку, ее методику мы и рассмотрим.
Построим интервальный ряд распределения, использовав встроенный инструмент анализа "Гистограмма" ("Сервис", "Анализ данных", "Гистограмма") (рис. 16). Откройте окно "Гистограмма" на листе "огива", где представлен ранжированный ряд и его график. Поставьте курсор в окно "Входной интервал" и выделите числовой ряд данных, захватив вниз еще 10-15 пустых ячеек (это сделано для того, чтобы выделить 6 интервалов, если выделите только числовые значения по 30 хозяйствам, то будет выделено 5 интервалов). Обязательно поставьте флажок "Вывод графика", только тогда будет построена гистограмма.
Рис. 16. Инструмент анализа "Гистограмма"
После нажатия кнопки "ОК" ряд распределения и гистограмма будут выведены на новый рабочий лист (если не изменять параметры вывода) в таком виде, как представлено на рис. 17.
Рис. 17. Вывод результатов при использовании инструмента "Гистограмма"
Округлите значения, приведенные в графе "Карман". Отметим, что в первой строке данной графы всегда выдается значение первого элемента в ранжированном ряду – нижняя граница первого интервала, а по остальным строкам – верхние границы интервалов, т.е. нижняя граница первого интервала показана в одной строке (во второй), а верхняя – в другой (в третьей). Поэтому следует удалить всю 2-ю строку, затем указать границы интервалов, при этом нужно увеличить частоту первого интервала на единицу (в данном случае вместо 8 было записано 9) (рис. 18).
Рис. 18. Интервальный ряд распределения и гистограмма
Чтобы убрать зазор между столбцами, выведите контекстное меню формата рядов данных, выберите пункт "Параметры" и поставьте ширину зазора "0". Измените подписи осей и название графика: сначала щелкните на названии – вокруг появиться рамка, поставьте внутрь курсор и отредактируйте текст.
Границы аналитических групп определены, на листе "рабочая база" выделим разными цветами хозяйства разных групп: выделите необходимые строки и, используя значок на панели инструментов "Цвет заливки" – , измените заливку строк (рис. 19). При работе с базами данных можно использовать функцию разделения окна ("Окно", "Разделить") – можно поделить окно на 2 или 4 части, при этом каждая часть будет иметь свою полосу прокрутки. Например, в первой половине окна можно показать названия показателей, а во второй – прокручивать числовую часть базы. Полосы разделения можно передвигать курсором, если придвинуть горизонтальную (вертикальную) полосу к краю видимой части листа, то разделение по горизонтали (вертикали) снимется (полностью снять разделение можно и другим путем: "Окно", "Снять разделение").
Проведем сводку всех признаков по выделенным аналитическим группам на том же листе в свободных ячейках по окончании базы (рис.20).
Рис. 19. Разделение совокупности на аналитические группы
Чтобы видеть названия признаков, можно использовать разделитель окна, а можно скопировать названия, используя контекстное меню правой кнопки мыши, или сочетания клавиш: Ctrl+C – копирование, Shift+Insert – вставка.
Обозначьте в левой части номера групп, и найдите сумму по первому признаку (в нашем примере – это затраты на производство продукции сельского хозяйства) по хозяйствам первой группы в ячейке D37. Для этого в ячейке D37 нужно поставить знак "=", нажать знак суммы ( ) на панели инструментов, а затем выделить диапазон данных, подлежащих суммированию – в нашем случае, для первой группы – D4: D12. Для каждой группы по первому признаку нужно найти суммы таким же образом, на рис. 20 показано, что для шестой группы суммировать нужно данные в диапазоне D32: D33. Сумму по всей совокупности найдем как сумму по шести группам, а для проверки правильности выполненных действий – как сумму в диапазоне D4: D33 (на рис. 20 получены одинаковые суммы, значит, ни одно хозяйство не было пропущено при сводке, поскольку нулевых значений по данному признаку не было). Сводку по остальным признакам проведем автоматически: выделите столбец со сводными данными по первому признаку (D38: D44) и, потянув за черный квадратик вправо, заполните все ячейки до конца базы. Сводка должна быть сделана по всем показателям базы!
Но по относительным признакам сводку проводить нельзя, поскольку для характеристики аналитических, а затем типических групп, должны применяться только взвешенные средние. Необходимые для расчета относительных признаков суммы абсолютных значений уже определены нами по группам.
Рис. 20. Сводка по аналитическим группам
Поэтому для расчета относительных показателей по группам скопируем их формулы по одному из хозяйств (на рис. 21 скопированы расчетные формулы по 30 хозяйству), затем выделим диапазон данных для вставки и вставим формулы. Таким образом, основные относительные показатели для характеристики аналитических групп рассчитаны, остается на отдельном листе сформировать макет таблицы и, используя ссылки ("="), занести значения показателей (рис. 22).
Рис. 21. Расчет относительных показателей по аналитическим группам
Рис. 22. Оформление аналитической группировки
Далее нужно определить, какие из групп имеют приблизительно одинаковые уровень интенсивности, эффективности производства – их можно объединить, но при этом нужно учитывать требования достаточной численности единиц в группах (не менее 5) и хотя бы приблизительного соответствия закону нормального распределения (самая многочисленная – средняя группа). После принятия решения об объединении групп, требуется провести сводку по выделенным типическим группам по всем показателям базы (рис. 23).
Рис. 23. Проведение сводки по типическим группам
Так же, как и по аналитическим группам, нужно рассчитать относительные показатели, а затем оформить их в таблицу (Рис. 24).
Рис. 24. Формирование таблицы типологической группировки
Если анализ группировки подтверждает, что типы выделены верно, то имеющиеся своды по типам для удобства в дальнейшей работе лучше представить в транспонированном виде (рис. 25).
Рис. 25. Транспонирование сводных данных по типическим группам
На данном этапе завершается работа над первой частью курсового проекта, которая в оформленном виде должна быть сдана на кафедру в первом семестре.