CC BY
98
с
ÂectHièê, август, 2013 г., № 8
УДК 004.9
НЕСКОЛЬКО УТИЛИТ ДЛЯ УПРОЩЕНИЯ РАБОТЫ С ГЕОЛОГО-ГЕОХИМИЧЕСКИМИ ГРАФИКАМИ В MICROSOFT EXCEL
Т. А. Ситников Негосударственное образовательное учреждение «Центр подготовки кадров энергетики», Санкт-Петербург t.sitnikov@mail.ru
Подробно описаны разработанные автором утилиты, позволяющие в программе Microsoft Excel форматировать XY-точечные графики, изменять значки точек одновременно на всех строящихся графиках, менять местами оси X и Y, обозначать на графике уравнения линейной регрессии доверительные интервалы («ошибку уравнения регрессии») и строить треугольные графики.
Ключевые слова: компьютерные программы, Microsoft Excel, построение XY-точечных графиков, утилиты.
SOME UTILITES FOR THE MICROSOFT EXCEL
T. A. Sitnikov
Non-state educational establishment «Power energy personnel training center»
t.sitnikov@mail.ru
Five utilités for Microsoft Excel were created by the author and described in detail. The utilités deal with the XY-scatter diagrams, and are very useful for the researchers (geochemists or lithologists). Keywords: computer programs, XY-scatter diagram, utilités.
Подавляющее большинство геологов и геохимиков, пользующихся компьютерами, имеет дело с операционной системой Microsoft Windows и пакетом программ Microsoft Office, из которых наиболее популярными являются текстовой процессор Microsoft Word и электронные таблицы Microsoft Excel.
Опыт работы (автор заведует кафедрой программного обеспечения петербургского Центра подготовки кадров энергетики (ЦП1КЭ) показывает, что большинство первичных данных анализов в нашей стране заносится и хранится в Microsoft Excel. Часто здесь же проводится и обработка, и визуализация данных, однако стандартные возможности Microsoft Excel ограничены, возникает необходимость экспорта данных в другие программы, которые нужно дополнительно приобретать, нередко за весьма ощутимую цену. Этого можно избежать, создав для себя подручные инструменты — небольшие служебные программы, так называемые утилиты.
В статье описаны некоторые утилиты для Microsoft Excel, разработанные автором по просьбе сыктывкарских и петербургских геохимиков и литологов. Эти утилиты позволяют существенно упростить некоторые задачи визуализации данных или реализовать способы визуализации, не предусмотренные стандартными возможностями Excel.
Утилиты были созданы автором с использованием Visual Basic for Applications (VBA) — встроенного языка программирования для приложений Microsoft Office. Используя VBA, можно дополнять вычислительные и графические возможности Microsoft Excel. С помощью VBA становится возможным решение многих нестандартных задач; описанные ниже утилиты — это всего лишь небольшая часть такого рода задач. Все работающие версии описанных ниже утилит можно найти на сайте www.Lithology.ru.
1. Утилита форматирования
графиков — График ЮК
Эта утилита была разработана для быстрого создания и корректировки графиков в соответствии с литохими-ческим стандартом ЮК [3]. Она включает в себя также ряд возможностей для быстрого изменения внешнего вида стандартного XY-точечного графика Microsoft Excel. Почти все, что можно сделать с ее помощью, делается и стандартными методами, но существенно дольше.
Диалоговое окно утилиты вызывается из меню или клавишей F6 (в последней на сегодня версии). Предварительно должна быть выделена XY-точечная диаграмма или диапазон данных, на основе которых она долж-
на быть построена (в последнем случае диаграмма будет создана). Вид диалогового окна показан на рис. 1.
Большая часть элементов диалогового окна касается форматирования рядов данных. В левой части окна выведен список рядов данных (может быть всего один). Если XY-точечная диаграмма была построена ранее стандартными средствами Microsoft Excel, ряды могут называться Ряд 1, Ряд 2 и т.д. Автоматически убрать слово «ряд» из названий рядов можно соответствующей кнопкой (в правой части окна).
Выделив ряд в списке, можно менять его свойства:
— «Имя ряда» — имя, которое будет отображаться в легенде и в списке рядов;
— «Значок ряда» — один из стандартных значков (ромб, треугольник, круг и т. д.)
— «Размер значка» — изменяется в пунктах как размер шрифта;
— «Цвет значка» и «Заполнить контур» — отвечают соответственно за цвет и заполнение;
— «X» и «Y» — позволяют изменить ссылки на исходные данные в таблице для построения ряда.
Кнопка «Добавить подписи точек» позволяет добавить к точкам подписи (к примеру, номера образцов), хранящиеся в определенном диапазоне ячеек (стандартные средства Microsoft
*
Âec&iuK, август, 2013 г., № 8
Рис. 1. Вид диалогового окна для утилиты «График ЮК» и форматируемый график
Excel сделать это не позволяют). После нажатия кнопки необходимо пометить диапазон с подписями, причем количество ячеек в нем должно совпадать с количеством точек ряда. После добавления подписей можно изменить их расположение относительно точек и размер шрифта подписей.
Поля ввода «Название графика», «Подпись по X», «Подпись по Y» позволяют изменить соответствующие свойства, не выходя из диалогового окна утилиты.
Кнопки «Расположить легенду на графике» и «Легенда вне графика» позволяют быстро изменять расположение легенды. В первом случае для экономии места легенда будет расположена поверх области построения графика, во втором — в своем стандартном положении (справа от области построения).
Команда «Преобразовать в рисунок» необходима для того, чтобы диаграмма престала быть привязанной к исходным данным. После преобразования в рисунок диаграмма теряет свойства, позволяющие ее форматировать как диаграмму Microsoft Excel. Чаще всего такая операция необходима перед вставкой диаграммы в тек-
стовый документ в качестве иллюстрации.
Команда «Форматировать все линии» сработает только в том случае, если на диаграмме есть проведенные «вручную» линии (например, оконтуриваю-щие группу точек) и одна из них в данный момент выделена. Все остальные «произвольные» линии будут отф орма-тированы по образцу выделенной.
Все эти операции, кроме добавления подписей к точкам, могут быть проведены и стандартными средствами. Утилита сводит управление наиболее часто изменяемыми свойствами точечной диаграммы в одно окно.
2. Утилита
XY_Change_Markers
Утилита предназначена для изменения значков точек одновременно на всех диаграммах, расположенных на листе рабочей книги Microsoft Excel. В текущей версии утилиты её диалоговое окно вызывается из пункта меню «Макросы». В приведенном на рис. 2 примере на всех диаграммах текущего листа значки типа «Незаполненный квадрат» будут заменены на значки «Заполненный треугольник». Если графиков много, это может существенно сэкономить время их ф ор-матирования.
3. Утилита XY_Change
Утилита меняет на графике местами оси X и Y, для ее вызова график должен быть предварительно выделен. В текущей версии оси меняются местами при нажатии клавиши F9. Утилита позволяет одним нажатием клавиши сделать то, что стандартными методами достигается продолжительной «ручной» работой. Если на графике больше одного ряда, значения X и Y поменяются для каждого ряда в отдельности. Это приведет к тому, что по абсциссе у каждого ряда будут отложены значения из разных столбцов (обычно в Microsoft Excel по оси абсцисс откладываются значения первого столбца, по оси ординат — значения второго и последующих столбцов). Во избежание путаницы рекомендуется пользоваться утилитой для графиков с одним рядом данных.
4. Утилита
XY_Confidence_Intervals
Применяется для отображения на корреляционном графике доверительной зоны для линии линейной регрессии (линии тренда). Несмотря на большое количество встроенных
Рис. 2. Диалоговое окно утилиты XY Change Markers и изменяемые графики
с
ÂectHiuê, август, 2013 г., № 8
статистических функций, возможность показа доверительной зоны на графиках с линиями тренда в Microsoft Excel не предусмотрена.
Размах доверительных интервалов для линии линейной регрессии определяется формулой:
AYi =ty,k'So
1 х (xi~xcp)
2>i"Xcp)
/=1
Ö у = -4 8,43х+ 17,488
о о
Ö4----
о "-О ^
где tg k — значение распределения Стьюдента для заданной вероятности и числа степеней свобод, S —
ост
стандартная ошибка предсказанного значения (стандартное отклонение остатков), n — число значений. VBA позволяет использовать для вычислений табличные статистические функции Microsoft Excel. В данной утилите использованы функции: FORECAST^ русской версии — ПРЕДСКАЗ), возвращающая предсказанное по линейной регрессии значение Y; TINV (СТЬЮДРАС-ПОБР), возвращающая t-значение распределения Стьюдента для заданных условий; DEVSQ (КВАДРОТКЛ), возвращающая сумму квадратов отклонений точек данных от их среднего; STEYX (CTOmYX), возвращающая стандартную ошибку предсказанных значений y для каждого значения х в регрессии; AVERAGE (СРЗНАЧ), возвращающая среднее для ряда значений. Эти встроенные функции позволили написать небольшую программу, добавляющую на график пунктирные линии, ограничиваю -щие зону доверительного интервала. Можно добавлять три типа доверительных интервалов: с вероятностью попадания значения в интервал ~ 68.3 % («1 сигма»), с вероятностью ~ 95.5 % («2 сигма») или с вероятностью ~ 99.7 % («3 сигма»). При нажатии клавиш Alt + F9 формируется доверительная зона для вероятности 1 сигма, при нажатии Alt + Ctrl + F9 — для вероятности 2 сигма, Shift + Alt + Ctrl + F9 — 3 сигма. Вероятность будет указана в правом нижнем углу графика (рис. 3).
Если на диаграмме несколько рядов данных, все ряды, кроме первого, будут удалены. Линии интервалов строятся только для одного ряда данных. После создания линии границ доверительной зоны перестают быть связаны с исходными данными в таблицах. Изменение исходных данных никак не повлияет на построенную доверительную зону.
0,00 0,10 0,20 0,30
НКМ 2 сигма
Рис 3. График с доверительной зоной линейной регрессии для вероятности 95.5 %
5. Утилита для построения треугольных диаграмм — Triangle
Треугольные (трехкомпонент-ные) диаграммы довольно широко применяются в литологии [1, 2], но в Microsoft Excel не предусмотрена возможность построения таких диаграмм. Треугольная диаграмма представляет собой равносторонний треугольник, каждая сторона которого является равномерной масштабной шкалой от 0 до 100. С учетом того, что сумма всех трех компонентов для каждой точки всегда равна 100 %, путем несложных тригонометрических расчетов можно получить координаты X и Y для каждой точки. Таким образом, можно использовать XY-точечные графики Microsoft Excel как базу для построения треугольных диаграмм, что и было сделано при создании этой утилиты.
Три обязательных для работы утилиты поля в диалоговом окне про-
граммы — это ссылки на диапазоны ячеек со значениями каждого из трех параметров. Количество ячеек во всех диапазонах должно быть одинаковым. Можно указать также диапазон с подписями для каждой точки. Кроме того, можно обозначить точки разными значками. В этом случае утилита предоставляет два варианта: обозначить либо принадлежность анализа к какой-то группе по качественному признаку (типу пород, месторождению и т. п.), либо разные значения какого-либо четвертого параметра (например, крестик — значения до 10, кружок — 10—25 и т. д.). В любом из этих вариантов нужно отметить диапазон, в котором указаны значения четвертого параметра. Если производится группировка по качественному признаку, список возможных значений параметра будет автоматически заполнен (рис. 4), далее нужно выбрать значок для каждой группы. Максимальное число групп, показанных разными значками, — 12; можно объединять группы, показав их одним и тем же значком.
Если значками нужно показать количественный параметр, помимо ячеек со значениями параметра необходимо в отдельном диапазоне ячеек указать границы интервалов для отображения разными значками. Самую нижнюю и самую верхнюю границы указывать не следует. К примеру, если указаны значения 10, 20, 30, будут сформированы интервалы «<10»,
Рис. 4. Диалоговое окно для утилиты построения треугольных диаграмм
Âe&ûiuK, август, 2013 г., № 8
*
«10—20», «20—30», «>30», для каждого из них нужно выбрать значок.
Диалоговое окно вызывается клавишами Ctrl+Shift+F6. Если предварительно был выделен диапазон ячеек, первые три столбца будут рассматриваться как значения трех основных параметров, четвертый — как параметр для обозначения значками, пятый — как подписи точек. При этом можно сразу выделять и названия параметров. При корректировке адресов ячеек в окне утилиты названия в адреса включать не следует.
Поля, обязательные для заполнения, показаны в диалоговом окне жирным шрифтом. После построения диаграмма вставляется в рабочий лист Microsoft Excel как рисунок (рис. 5).
Рис. 5. Пример треугольной диаграммы, построенной с помощью утилиты Triangle
Несмотря на то что все утилиты были проверены при обработке дан-
ных, в процессе использования в них могут быть выявлены техничес -кие и функциональные недочеты. Автор готов принять замечания и пожелания по работе этих и созданию других утилит.
Литература
1. Маслов А. В. Осадочные породы: методы изучения и интерпретации полученных данных. Екатеринбург: Изд-во УГГУ, 2005. 289 с.
2. Шванов В. Н, Фролов В. Т., Сергеева Э. И. и др. Систематика и классификации осадочных пород и их аналогов. СПб.: Недра, 1998. 352 с.
3. Юдович Я. Э, Кетрис М. П. Основы литохимии. СПб.: Наука, 2000. 479 с.
Рецензент д. г.-м. н. Я. Э. Юдович
