Расчет балансов и прогнозирование изменений с помощью MS Excel

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

Это так называемые табличные процессоры, один из которых (Microsoft Excel) входит в широко распространенный на персональных компьютерах пакет офисных программ Microsoft Office for Windows. Именно с помощью этого приложения и будут рассчитываться балансы гумуса и доступных элементов питания растений, а также изменение их содержания в следующих упражнениях.

Упражнение 1. Расчет балансов гумуса и элементов питания растений.

Цель работы. С помощью Microsoft Excel рассчитать балансы гумуса и доступных элементов питания растений.

Исходные данные. Исходные данные для заданий приводятся в приложении.

Описание выполнения.

1. Запустите Microsoft Excel.

2. Указав курсором мыши на границу между столбцами «А» и «В» в строке с названиями столбцов, нажмите левую кнопку мыши и раздвиньте столбец «А» в 2-3 раза.

3. В ячейку «А2» введите слово «Культура», а в ячейки «А3»- «А12» названия культур севооборота из вашего варианта.

4. В ячейку «В2» введите слово «Урожайность», а в ячейки «В3»- «В12» урожайности культур севооборота из вашего варианта.

5. В ячейку «D1» введите слово «Вынос», в ячейки «C2» – «азота»; «D2» – «фосфора»; «E2» – «калия».

6. В ячейку «F1» введите слово «Потери», в ячейку «F2» – «гумуса».

7. В ячейки «С3»–«С12» введите формулы для расчета выноса азота. Для этого, указав курсором на ячейку «С3» введите в строке формул «=В3*(хх-yy)», где хх – значение выноса азота для данной культуры; yy – биологическая фиксация азота клубеньковыми бактериями бобовых культур, кг/т, (принимается равной 10 кг/т сена бобовых трав, 0,5 кг/т зеленого корма злакобобовых травосмесей, 26 кг/т зерна сои). Повторите операции для ячеек «С4»–«С12».

8. Введите в ячейки «D3»–«D12» формулы для расчета выноса фосфора «=В3*хх», где хх – значение выноса фосфора для данной культуры, а в ячейки «Е3»–«Е12» аналогичные формулы для расчета выноса калия.

9. В ячейках «F3»–«F12» рассчитайте потери гумуса. Для этого, согласно приведенной ранее формуле, разделите вынос азота без учета биологическая фиксация азота клубеньковыми бактериями на 50. Формула в ячейке «F3» будет иметь вид: «=В3*хх/50», где хх – значение выноса азота для данной культуры.

10. В ячейку «H1» введите слово «Остатки», в ячейки «G2» – «пожнивные»; «H2» – «корневые»; «I2» – «сумма».

11. В ячейках «G3»–«G12» рассчитайте поступление пожнивных остатков. Для этого введите в них формулы регрессионных зависимостей массы пожнивных остатков от урожайности культуры, заменяя «х» на ссылку на соответствующую ячейку из столбца урожайностей (ячейки «B3»–«B12»).

12. Аналогично рассчитайте в ячейках «H3»–«H12» поступление корневых остатков.

13. Просуммируйте в ячейках «I3»–«I12» пожнивные и корневые остатки (=G3+H3).

14. В ячейку «J2» введите «Кг», а ячейки «J3»–«J12» значения коэффициентов гумификации растительных остатков.

15. В ячейку «K1» введите слово «Поступление», в ячейку «K2» – «гумуса».

16. В ячейках «К3»–«К12» рассчитайте поступление гумуса, умножая коэффициент гумификации на сумму растительных остатков (столбцы G и К).

17. В ячейку «L2» введите «Бг», а в ячейках «L3»–«L12» балансы гумуса (=K3-F3).

18. В ячейке «С13» подсчитайте суммарный вынос азота за всю ротацию. Для этого, укажите курсором на эту ячейку, нажмите кнопку «Вставка функции» ( ), и выберите из списка функций «СУММ». В открывшемся окне «Аргументы функции» укажите значок ввода диапазона ячеек для суммирования ( ) и обведите курсором ячейки «С3»–«С12». Нажмите для подтверждения «Enter», а затем «ОК».

19. Распространив полученную формулу на ячейки «D13» и «E13» Вы получите суммарный вынос фосфора и калия.

20. Для расчета баланса гумуса без участия удобрений, повторите операции из пункта 18 для ячейки «L13» и диапазона «L2-L12».

21. Введите в ячейку «А16» «Удобрение», в ячейку «В16» «Доза», в ячейку «D15» «Содержание»; в ячейки «С16», «D16», «Е16», «F16» - «Азота», «Фосфора», «Калия», «воды».

22. В ячейки «А17-А22» введите названия внесенных удобрений (сначала органических, затем минеральных).

23. В ячейки «В17-В22» введите дозы внесенных удобрений, для органических в тоннах на гектар, минеральных – килограммах на гектар.

24. В ячейки «С17-С22» введите содержание азота во внесенных удобрениях, «D17-D22» - фосфора, «Е17-Е22» - калия, «F17-F22» - воды.

25. Введите в ячейку «H15» «Поступление», а в ячейки «G16», «H16», «I16» скопируйте содержимое ячеек «С16», «D16», «Е16».

26. Рассчитайте поступление питательных веществ с органическими удобрениями. Для этого в ячейку «G17» введите формулу «=$B17*C17*10». Знак «$» означает, что при распространении формулы столбец «В» в ней не изменится, а коэффициент 10 получился при делении 1000 (килограммов в тонне) на 100 (процентов).

27. Распространите формулу на строки с органическими удобрениями и столбцы «D» и «Е».

28. Рассчитайте поступление питательных веществ с минеральными удобрениями. Для этого в ячейку на пересечении первой строки с минеральными удобрениями и столбца «G» введите формулу «=$B19*C19/100».

29. Распространите формулу на строки с минеральными удобрениями и столбцы «D» и «Е».

30. Просуммируйте поступление азота, фосфора и калия в ячейках «G23», «H23», «I23» (аналогично п. 18).

31. Введите в ячейку «J16» «органики», в ячейку «К16» «гумуса».

32. Введите в ячейку «J17» формулу для расчета поступления в почву свежего органического вещества: «=B17*(1-F17/100)». Распространите ее на все строки с органическими удобрениями.

33. Введите в ячейку «К17» формулу для расчета поступления в почву гумуса: «=J17*0,35» (0,35 – коэффициент гумификации растительных остатков из приложения). Распространите формулу на все строки с органическими удобрениями.

34. Просуммируйте в ячейке «К23» поступление в почву гумуса аналогично пунктам 18 и 30.

35. Введите в ячейки «А24-А28» слова «Баланс», «гумуса», «азота», «фосфора», «калия».

36. В ячейке «А25» подсчитайте баланс гумуса («=L13+K23»); в ячейках «А26-А28» балансы азота, фосфора и калия с помощью формул «=G23-C13», «=H23-D13» и «=I23-E13» соответственно.

37. Сохраните книгу (файл) Microsoft Excel с именем, которое вам укажет преподаватель. Выключите Microsoft Excel.

Упражнение 2. Расчет и оценка прогнозных изменения содержания гумуса и элементов питания растений.

Цель работы. С помощью Microsoft Excel определить прогнозные изменения содержания гумуса и доступных элементов питания растений.

Исходные данные. Исходными данными для заданий являются результаты выполнения Упражнения 1.

Описание выполнения.

1. Запустите Microsoft Excel.

2. Откройте файл (книгу Microsoft Excel), созданный при выполнении упражнения 1.

3. Скопируйте результаты расчета баланса на другой лист книги.

4. Для этого обведите ячейки «А24-В28»; скопируйте их содержимое в буфер обмена (например, нажав «Ctrl+C»); перейдите на нужный лист (список листов внизу таблицы); выберите в главном меню «Правка» – «Специальная вставка», и, в открывшемся окне специальной вставки отметьте указатель значения.

5. Введите в ячейку «С1» «Исходные запасы», в ячейку «D1» «Конечные запасы».

6. Введите в ячейку «С2» формулу для расчета исходных запасов гумуса «=30*хх*yy», где хх – плотность сложения почвы, г/см³; yy – исходное содержание гумуса , %.

7. В ячейку «D2» введите формулу для расчета конечных (прогнозируемых) запасов гумуса «=В2+С2».

8. Введите в ячейку «Е1» «Прогноз содержания», а в ячейку «Е2» формулу для расчета содержания гумуса в %: «=D2/30/хх», где хх – плотность сложения почвы, г/см³.

9. Введите в ячейки «F1» и «G1» «Абсолютное изменение» и «Относительное изменение»

10. В ячейку «F2» введите формулу для расчета абсолютного изменения содержания гумуса «=C2-D2».

11. В ячейку «G2» введите формулу для расчета относительного изменения содержания гумуса «=F2/C2*100».

12. Введите в ячейки «С4» и «С5» формулы для расчета исходных запасов доступного фосфора и обменного калия в верхнем 30-сантиметровом слое «30*хх*yy1» и «30*хх*yy2», где хх – плотность сложения почвы, г/см³; yy1 и yy2 – исходное содержание доступного фосфора и обменного калия, мг на 100 г почвы.

13. Введите в ячейки «D4» и «D5» формулы для расчета прогнозируемых запасов доступного фосфора и обменного калия «=С4+В4» и «=С5+В5».

14. В ячейки «Е4» и «Е5» введите формулы для расчета прогнозируемого содержания фосфора и калия «=D4/30/хх» и «=D5/30/хх», где хх – плотность сложения почвы, г/см³.

15. В ячейках «G4» и «G5» рассчитайте относительное изменение содержания доступного фосфора и калия (формулы «(yy1-Е4)/yy1*100» и «(yy2-Е5)/yy2*100», где исходное содержание доступного фосфора и обменного калия, мг на 100 г почвы).

16. Оцените полученные результаты. Определите, насколько увеличится или уменьшится содержание элементов питания и органического вещества в случае реализации изучаемых севооборотов.