Применение Microsoft Excel для обработки данных по засолению

Упражнение 4. Построение солевого профиля с помощью Microsoft Excel.

Цель работы. С помощью Microsoft Excel построить солевой профиль почвы по данным анализа водной вытяжки.

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

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

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

2. В ячейку «А1» введите слово «Слой», а в ячейки «А2»- «А7» описания слоев почвы: 0–30, 30–50, 50–75, 75–100, 100–150, 150–200.

3. В ячейки «В1–G1» введите названия ионов почвенных солей: Ca, Mg, Na, HCO3, Cl, SO4.

4. В ячейки «В2–G7» введите значения содержаний ионов почвенных солей из Вашего задания (первую или вторую часть по указанию преподавателя).

5. Выделите курсором мыши ячейки «В1–G7» и скопируйте их в ячейки «H1–M7».

6. Установите курсор мыши в ячейку «K2» и введите в строке формул выражение: «=E2*-1».

7. С помощью «+» в правом нижнем углу ячейки «K2» распространите курсором мыши формулу сначала на ячейки «L2» и «M2», а затем на ячейки «K3–M7». У Вас получатся отрицательные значения для содержания анионов.

8. Вызовите «Мастер диаграмм» (значок « » в стандартной панели Microsoft Excel).

9. На первом шаге выберите первый тип диаграммы: «Гистограмма» и второй вид: «Гистограмма с накоплением».

10. На втором шаге укажите диапазон данных для диаграммы. Для этого в окне «Исходные данные» нажмите левую кнопку мыши, указав курсором на значок « » в строке «Диапазон», обведите курсором мыши ячейки «H1–M7», а затем подтвердите Ваш выбор, нажав левую кнопку мыши, указав курсором на значок « » в окне «Мастер диаграмм (Шаг 2 из 4): ».

11. Укажите в окне «Исходные данные», что Ваши ряды располагаются в столбцах (переключатель около заголовка «Ряды в»).

12. Выберите в окне «Исходные данные» пункт меню «Ряд» и введите подписи по оси абсцисс. Для этого нажмите левой кнопкой мыши на значок « » в строке «подписи по оси Х», обведите курсором ячейки «А2»- «А7», затем подтвердите Ваш выбор как в пункте 9.

13. Нажмите кнопку «Далее» в окне «Исходные данные» и перейдите к 3 шагу создания диаграммы – окну параметров диаграммы.

14. На закладке «Заголовки» окна «Параметры диаграммы» задайте заголовок для оси Y (ось значений): «Содержание ионов, мг-экв/100 г почвы».

15. На закладке «Легенда» окна «Параметры диаграммы» задайте размещение легенды: внизу посередине.

16. Нажмите кнопку «Далее» и перейдите к 4 шагу создания диаграммы – окну размещения диаграммы.

17. Выберите в окне «Мастер диаграмм (шаг 4 из 4): размещение диаграммы» переключатель «имеющимся» и оставьте в качестве места размещения Лист1. Нажмите кнопку «Готово».

18. Укажите курсором на свободное место внутри диаграммы и нажмите правую кнопку мыши. В открывшемся вертикальном меню выберите пункт «Формат области построения».

19. В окне «Формат области построения» выберите цвет заливки – белый и нажмите кнопку «ОК».

20. Укажите курсором на свободное место внутри области диаграммы (но не в самой диаграмме) и нажмите правую кнопку мыши. В открывшемся вертикальном меню выберите пункт «Формат области диаграммы».

21. На закладке «Вид» окна «Формат области диаграммы» выберите тип рамки – «невидимая» и перейдите на закладку «Шрифт».

22. На закладке «Шрифт» установите начертание шрифта – «обычный», размер – «12» и уберите флажок «Автомасштабирование». Нажмите кнопку «ОК».

23. Укажите курсором на свободное место внутри области легенды и нажмите правую кнопку мыши. В открывшемся вертикальном меню выберите пункт «Формат легенды».

24. На закладке «Вид» окна «Формат легенды» выберите тип рамки – «невидимая» и нажмите кнопку «ОК».

25. Укажите курсором внутрь столбца гистограммы и нажмите правую кнопку мыши. В открывшемся вертикальном меню выберите пункт «Формат рядов данных».

26. На закладке «Параметры» окна «Формат рядов данных» введите ширину зазора - 0.

27. Измените размеры диаграммы, потянув за уголок курсором мыши.

28. Измените цвета заливки рядов данных. Для этого, укажите курсором внутрь области данных любого столбца, нажмите правую кнопку мыши, и в открывшемся меню выберите пункт «Формат рядов данных».

29. На закладке «Вид» окна «Формат рядов данных» нажмите кнопку «Способы заливки».

30. В открывшемся окне «Способы заливки» выберите закладку «Узор», установите в ней цвета штриховки (черный) и фона (белый), выберите узор и нажмите кнопку «ОК».

31. Нажмите кнопку «ОК» в окне «Формат рядов данных».

32. Повторите операции 28-31 для всех ионов (рядов данных), указывая разные виды узоров. Примерный вид экрана после выполнения всех операций приведен на рисунке 1.5.

Упражнение 5. Расчет параметров засоления почвы с помощью Microsoft Excel.

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

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

Рисунок 1.5 – Экран MS Excel после выполнения упражнения 4

Описание выполнения. Если Вы выполняете это упражнение сразу после выполнения упражнения 1, или у Вас сохранилась книга Microsoft Excel с результатами этого упражнения, пропустите пункты 1–4 и перейдите сразу к пункту 5.

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

2. В ячейку «А1» введите слово «Слой», а в ячейки «А2»- «А7» описания слоев почвы: 0–30, 30–50, 50–75, 75–100, 100–150, 150–200.

3. В ячейки «В1–G1» введите названия ионов почвенных солей: Ca, Mg, Na, HCO3, Cl, SO4.

4. В ячейки «В2–G7» введите значения содержаний ионов почвенных солей из Вашего задания (первую или вторую часть по указанию преподавателя).

5. Скопируйте исходные данные, введенные Вами для выполнения упражнения 1 на другой лист Microsoft Excel. Для этого обведите ячейки «А1–G7» курсором, нажмите значок «Копировать», перейдите на другой лист, установите курсор в ячейку «А1» и нажмите значок «Вставить».

6. В ячейки «А8» и «А9» введите «0–100» и «0–200».

7. Вставьте столбец между столбцами «А» и «В» («Слой» и «Са»). Для этого установите курсор на любую ячейку столбца «В», нажмите правую кнопку и в открывшемся вертикальном меню выберите пункт «Добавить ячейки». В открывшемся окне «Добавление ячеек» выберите переключатель «столбец» и нажмите кнопку «ОК».

8. В ячейку «В1» введите слово «Мощность».

9. В ячейки «В2–В9» введите мощности соответствующих слоев почвы в см (разность между вторым и первым значением в ячейках столбца «А»).

10. В ячейку «С8» введите формулу для вычисления средневзвешенного содержания иона Са в метровом слое (клавиша «F4» – вставка символов «$»): «=(C2*$B$2+C3*$B$3+C4*$B$4+C5*$B$5)/($B$2+$B$3+$B$4+$B$5)».

11. С помощью крестика в нижнем правом углу ячейки «С8» распространите введенную формулу на ячейки «D8–H8».

12. В ячейку «С9» введите формулу для вычисления средневзвешенного содержания иона Са в двухметровом слое (клавиша «F4» – вставка «$»): «=(C6*$B$6+C7*$B$7+C8*$B$8)/($B$6+$B$7+$B$8)».

13. Распространите введенную формулу на ячейки «D9–H9».

14. В ячейки «I1», «J1», «K1», «L1», «M1», «N1» введите значения коэффициентов для перевода содержания солей из миллиграмм-эквивалентов на 100 г почвы в проценты из приложения (для ионов Ca, Mg, Na, HCO₃, Cl, SO₄).

15. В ячейку «I2» введите формулу для вычисления процентного содержания иона Са в верхнем 30-сантиметровом слое почвы: «=C2*I$1» (обратите внимания – знак «$» стоит только перед номером строки).

16. Распространите формулу сначала на ячейки «I3–I9», а затем на ячейки «J3–N9».

17. В ячейку «O1» введите «% солей».

18. В ячейке «O2» подсчитайте сумму солей в верхнем 30-сантиметровом слое почвы. Для этого установите курсор в эту ячейку, нажмите значок «Вставка функции» в меню Excel (значок « »), выберите функцию суммирования («СУММ»), нажмите кнопку «ОК».

19. В открывшемся окне «Аргументы функции» нажмите левой кнопкой мыши на значок « » в строке «Число1», обведите курсором ячейки «I2–N2», а затем подтвердите Ваш выбор, нажав левую кнопку мыши, указав курсором на значок « » в окне «Аргументы функции». Нажмите кнопку «ОК».

20. Распространите введенную формулу на ячейки «О3-О9».

21. Отформатируйте ячейки «О2-О9». Для этого обведите их курсором, выберите пункт «Формат» в главном меню, в открывшемся вертикальном меню выберите пункт «Ячейки».

22. В открывшемся окне «Формат ячеек» выберите закладку «Число», а в ней пункт «Числовые форматы». Установите число десятичных знаков – 2, нажмите кнопку «ОК».

23. Скопируйте содержимое ячеек «А1-А9» в ячейки «А11-А19».

24. В ячейки «В11», «C11», «D11», «E11» введите описания формул: «Cl/SO4», «HCO3/Cl+SO4», «HCO3/Cl», «HCO3/SO4».

25. В ячейку «В12» введите формулу для первого соотношения: «=$G2/$H2», отформатируйте ее как в пункте 22 и распространите на ячейки «В13–В19».

26. В ячейку «С12» введите формулу для второго соотношения: «=$F2/($G2+$H2)», отформатируйте ее как в пункте 22 и распространите на ячейки «С13–С19».

27. В ячейку «D12» введите формулу для третьего соотношения: «=$F2/$G2», отформатируйте ее как в пункте 22 и распространите на ячейки «D13–D19».

28. В ячейку «E12» введите формулу для четвертого соотношения: «=$F2/$G2», отформатируйте ее как в пункте 22 и распространите на ячейки «E13–E19».

29. В ячейку «F11» введите слово «натрий», в ячейку «F12» формулу для определения преобладания Na: «=ЕСЛИ($E2>($C2+$D2);"преобладает";" ")», а затем распространите ее на ячейки «F13–F19».

30. Введите в ячейку «G11» слово «Тип», а в ячейки «G12–G19» названия типов засоления, определенные по полученным Вами соотношениям, с помощью приложения 1 и правил раздела 4.

31. Введите в ячейку «H11» слово «Степень», а в ячейки «H12–H19» степени засоления, определенные с помощью приложения 3 по полученным Вами типами засоления и процентному содержанию солей из ячеек «О2-О9».

32. Вид экрана после выполнения упражнения 5 представлен на рисунке 1.6.

Рисунок 1.6 – Вид экрана после выполнения упражнения 5

ЛИТЕРАТУРА К ЧАСТИ I

1. Базилевич Н. И., Панкова Е. И. Учет засоленных почв // Методические рекомендации по мелиорации солонцов и учету засоленных почв. М. «Колос». 1970.

2. Информационные технологии рационального природопользования на орошаемых землях Поволжья / Н.А. Пронько [и др.] // Саратов: ФГОУ ВПО Саратовский ГАУ. – 2009. – 212 с.

3. Костин Б.И., Гребенюков П.Г. Предупреждение засоления орошаемых земель Заволжья – Саратов: Приволж. кн. изд–во, 1988. – 102 с.

4. Минеев В.Г., Дебрецини Б., Мазур Т. Биологическое земледелие и минеральные удобрения. М.: Колос, 1993. 415 с.

5. Михайлов Л.Н. Азбука плодородия. - Самара: кн.изд-во. 1992. – 104 с.

6. ОСТ 46-52-76 «Методы агрохимических анализов почв. Определение химического состава водных вытяжек и состава грунтовых вод для засоленных почв» (введен в действие приказом Министерства сельского хозяйства СССР от 20 августа 1976 г. №173)

7. Официальный сайт Министерства сельского хозяйства РФ, режим доступа: www.mcx.ru.

8. Оценка засоления земель саратовского Заволжья с помощью ГИС-технологий / Н.А. Пронько [и др.] // Проблемы научного обеспечения сельскохозяйственного производства и образования: сб. науч. тр. – Саратов: Научная книга, 2008. – С. 197­–199.

9. Пронько Н.А., Корсак В.В. Метод расчета доз органических и минеральных удобрений для культур орошаемых севооборотов по прогнозному ротационному балансу элементов питания // Агрохимия, 2001, № 7, С. 66-71.

10. Пронько Н.А., Фалькович А.С., Романова Л.Г Изменение плодородия орошаемых каштановых почв Поволжья в процессе длительного использования и научные основы его регулирования / ФГОУ ВПО «Саратовский ГАУ». Саратов, 2005. 220 с.

11. Туев Н.А. Микробиологические процессы гумусообразования. – М.: Агропромиздат, 1989. – 239 с.

12. Шенявский А.Л. Оценка плодородия почвы методом гумусового баланса: Обзорн. информ. / ВНИИТЭИ СХ. – М., 1973. – 27 с.

13. Шувалов А.Н., Фомин Г.И., Колчина Н.А. Современные системы орошаемого земледелия Поволжья и пути их реформирования. - Саратов, 1994. – 58 с.