Проверка соответствия статистического разброса показаний детекторов частиц спутника «Университетский-Татьяна» распределению Пуассона.
Введение
Показание любых приборов, в том числе приборов, установленных на орбите Земли для измерения потоков частиц и нейтрального излучения, меняются во времени. Эти изменения могут быть вызваны как реальными изменениями потоков, так и статистическими факторами. Действительно, прибор, регистрирующий в среднем 100 частиц в секунду, с некоторой вероятностью зарегистрирует не 100, а 95 или 105 частиц за тот же интервал времени. При анализе временных рядов показаний прибора (в данном случае это последовательность количества частиц, зарегистрированных прибором за время кадра) нужно уметь различать, какие из изменений показаний статистически значимы.
Количество событий, зарегистрированных счетчиком частиц за время кадра, представляет собой случайную величину, поведение которой описывается распределением Пуассона. В общем случае, распределение Пуассона описывает поведение случайной величины, принимающей целые значения, если значение этой величины является суммой значений большого числа независимых случайных величин, принимающих значения “0” или “1”, причем вероятность “1” мала. В эксперименте со счетчиком частиц мы имеем именно такой случай, поскольку число частиц, зарегистрированных за 1 секунду, является суммой ряда числа частиц, зарегистрированных за каждую миллисекунду или за более короткие интервалы времени.
Распределение Пуассона описывается формулой, по которой можно вычислить вероятность появления значения n описываемой случайной величины:
Pm(n) = exp(-m)×(mn/n!)
Здесь положительный параметр m представляет собой математическое ожидание (для примера со счетчиком это ожидаемое число отсчетов за рассматриваемый интервал времени).
Разброс ряда значений вокруг ожидаемого значения (или вокруг среднего значения), измеренных при многократном повторении эксперимента, характеризуется величиной стандартного отклонения s. Можно показать, что для распределения Пуассона стандартное отклонение s = Öm.
В настоящей работе предлагается проверить, описывается ли статистический разброс временного ряда показаний прибора, измеряющего число различных частиц на орбите Земли, распределению Пуассона. Анализ в данной работе будет проводиться на качественном уровне, т. е. без использования сложных статистических критериев, позволяющих выразить степень соответствия распределения экспериментальных данных ожидаемому распределению количественно (например, критерия хи-квадрат). Для качественного анализа
достаточно будет проверить, имеет ли зависимость стандартного отклонения разброса точек на временном ряду от среднего значения вид s = Öm. Построив на одной диаграмме экспериментальный и теоретический график зависимости s(m), можно сделать соответствующий вывод.
Следует отметить, что влияние некоторых факторов физического и технического характера может приводить к отличию распределения показаний прибора от распределения Пуассона. К таким факторам можно отнести наличие пучков частиц, связанных друг с другом (стандартное отклонение увеличивается) или перегрузку прибора (количество зарегистрированных частиц в кадре определяется в первую очередь мертвым временем, в результате чего стандартное отклонение уменьшается).
Для выполнения работы пригодны данные любого космического эксперимента. Мы будем использовать данные спутника «Университетский-Татьяна», осуществлявшего мониторинг частиц различной природы на орбите Земли с января 2005 г. Показания приборов этого спутника за период с января 2005г. по декабрь 2006 г. находятся в сети интернет в свободном доступе на сайте космического научно-образовательного проекта Московского государственного университета им. по адресу: http://cosmos. *****. В таблице указаны типы частиц, соответствующих отдельным каналам информации. Кроме сорта частиц и энергетических интервалов каналы различаются типом детектора, проводящего измерения, в частности его геометрическим фактором. Соответствующая информация, включающая описание измерительной аппаратуры, также есть на сайте, указанном выше.
Номер канала | Тип регистрируемых частиц |
3 | Электроны Ee>70keV и протоны Ep>0.8MeV |
4 | Протоны Ep=2-14MeV |
6 | Электроны Ee=0.3-0.6MeV |
7 | Протоны Ep=7-15MeV |
8 | Электроны Ee=0.7-0.9MeV и протоны Ep>7MeV |
9 | Протоны Ep=15-40MeV |
12 | Протоны Ep=40-100MeV |
Для выполнения работы предлагается использовать электронные таблицы Microsoft Excel. Этот пакет позволяет обрабатывать большие массивы данных (столбцы до 65000 значений), строить диаграммы и графики. Большое количество формул, в том числе формулы для статистического анализа, заложены в Excel в виде готовых функций. Вероятно, основы работы с электронными таблицами Microsoft Excel, входящих в состав пакет Microsoft Office, известны каждому пользователю персонального компьютера. Для систематизации и закрепления соответствующих навыков принципы обработки данных с помощью электронных таблиц Microsoft Excel рассмотрены в приложении к описанию этой лабораторной работы.
Порядок выполнения работы:
1) Получение данных из интернета (в текстовый файл).
А) Войти на сайт cosmos. *****, выбрать пункт меню «данные»
Б) Выбрать пункт меню «Измерения потоков заряженных частиц (КДМ)»
В) Выбрать интервал времени наблюдений (в течение которого данные доступны).
Г) Указать, какие параметры нужны для анализа. Можно выбрать «All the channels (output for a plotting program)» или конкретные параметры, для которых предполагается проводить анализ.
Д) Нажать кнопку «Show data». Данные появятся на экране.
Е) Через буфер обмена перенести данные в текстовый файл:
- выделить данные
- Скопировать в буфер обмена (меню «правка»à «копировать»
- Открыть редактор «Блокнот»
- Вставить данные (меню «правка» à «вставить»)
- Сохранить файл.
2) Перенос данных из текстового файла в электронные таблицы Excel.
А) Войти в Excel, выбрать пункт меню «файл» à «открыть»
Б) В качестве типа файлов указать «все файлы». Найти нужный файл.
В) Далее указать в качестве формата «фиксированной ширины»
Г) В следующем окошке скорректируйте разбиение данных по колонкам. Следует добавить маркеры, разделяющие по разным колонкам составные части времени измерения (год-месяц-число-час-минута-секунда). Это необходимо для последующего вычисления времени в секундах (т. е., для создания равномерной шкалы времени)
Д) Пройдя дальнейшие пункты «далее», убедиться, что данные нормально отображаются в электронных таблицах, после чего сохранить файл под новым именем в формате Excel.
Важное примечание: в файле данных в качестве разделителя десятичных знаков используется точка. Если у Вас в компьютере используется запятая, то нужно либо изменить настройку Windows, либо заменить в файле точки на запятые.
3) Создать столбец, показывающий время в секундах. Вычисления можно проводить по формуле t = ss + mm*60 + hh*3600 + dd*24*3600 (если данные – в пределах одного месяца. Если данные содержат переход на новый месяц – то добавится еще одно слагаемое. Определите его самостоятельно).
Для удобства работы можно отсчитывать время в секундах от времени первого из кадров. Для этого достаточно добавить в формулу для каждого из кадров, начиная со второго, вычитание времени первого кадра.
4) Построить точечную диаграмму, отображающую изменение показаний в разных каналах в зависимости от времени. Выбрать канал для дальнейшего анализа. Рекомендуется провести анализ для каналов, отображающих потоки частиц определенного типа, так как именно для них целесообразно провести проверку на наличие возможных отклонений наблюдаемого распределения от распределения Пуассона, обусловленных различными неоднородностями:
- Channel 12 -> Ep=40-100MeV (G=25)
- Channel 6 -> Ee=0.3-0.6MeV (G=9.7E-02)
Другие каналы также можно использовать для анализа.
5) Для выбранного канала нужно перевести данные, представляющие собой скорости счета (число частиц в секунду) в число частиц, зарегистрированных в данном кадре. Для этого нужно умножить столбец скоростей счета на длительность кадра. В данном случае длительность кадра равна 10.5 с. Убедитесь, что в результате умножения получаются целые значения.
6) При просмотре данных видно, что в некоторых кадрах записаны значения, не соответствующие реальным скоростям счета. Эти значения соответствуют сбоям при записи и передаче данных на Землю. В процессе предварительной подготовки данных перед помещением их в открытый доступ сбойные кадры были заполнены одинаковыми значениями, равными –1*10100. Такие заведомо невозможные значения нужно исключить из анализа, чтобы они не искажали общей картины. Предлагается заменить их каким-либо текстом (например, словом «сбой»). Тогда результатом вычисления средних значений и стандартных отклонений во всех случаях, когда сбойная ячейка входит в диапазон обрабатываемых ячеек, будет нечисловое значение (#ЗНАЧ).
Создайте столбец, в котором с помощью функции ЕСЛИ( ) будут записаны значения скорости счета, если они больше нуля (или равны), и слово «сбой», если меньше.
7) Вычислить для каждого момента времени среднее значение числа частиц, регистрируемых в кадре. Среднее следует вычислять по ±5 кадрам. Это соответствует интервалу времени около двух минут. Использование большего интервала привело бы к влиянию на вычисленное среднее значение регулярных изменений потока, связанных с орбитальным движением.
Для вычисления среднего значения можно использовать функцию СРЗНАЧ( ).
8) Вычислите отклонений показаний прибора от среднего значения в каждый момент времени. Для этого создайте соответствующий столбец разностей.
9) Для каждого момента времени определите величину стандартного отклонения для вычисленных перед этим разностей между зарегистрированным числом частиц и средним значением. Вычисляйте стандартное отклонение на том же интервале ±5 кадров, который использовался для вычисления среднего значения. В электронных таблицах Excel можно использовать функцию СТАНДОТКЛОН( ).
10) Вычислить столбец корней квадратных из среднего числа частиц, зарегистрированных в соответствующий момент времени.
11) Построить точечную диаграмму, отображающую зависимость стандартного отклонения показаний от величины среднего числа частиц. На той же диаграмме отобразить ряд корней квадратных из среднего числа частиц. Убедиться в том, что стандартное отклонение скоростей счета прибора в первом приближении зависит от средней скорости как корень квадратный.
Более внимательное рассмотрение полученной диаграммы показывает, что корень из среднего значения является нижним пределом для ряда вычисленных стандартных отклонений. При этом отклонения в большую сторону встречаются достаточно часто. Для понимания полученного результата следует провести более точный анализ.
Большая величина стандартного отклонения может быть связана не только с теми участками данных, на которых за счет статистических факторов появились отдельные точки со значительным отклонением от среднего. Другая возможность – наличие систематической ошибки при усреднении. Для проверки этого предположения найдем участки данных, на которых возникает значительное отклонение показаний прибора от среднего значения.
Вычислим столбец отклонений, усредненных по ±5 кадрам, и построим точечную диаграмму зависимости этих величин от времени. На диаграмме видно, что для большинства кадров, как и следует ожидать, среднее отклонение колеблется около нуля. Однако, иногда встречаются участки, большинство точек которых демонстрирует значительное отклонение в одну и ту же сторону (либо много точек подряд отклоняются в +, либо в минус). Сопоставив диаграмму с временным ходом показаний прибора, видим, что погрешности привязаны к резкими возрастаниям регистрируемого потока. Очевидно, скользящее среднее, использованное нами для оценки ожидаемого потока, в этих случаях дает ошибки. Внимательно рассмотрев ход средних значений на фоне исходных данных, можно убедиться, что это действительно так.
Более корректно было бы использовать для оценки ожидаемого потока более сложную зависимость, чем скользящее среднее (например, вписывать прямую методом наименьших квадратов). Однако, этот подход, требующих значительных вычислений, также не дает идеального соответствия.
В данной работе мы просто исключим из рассмотрения те участки данных, на которых усреднение не дает удовлетворительной оценки ожидаемого потока. В качестве критерия пригодности участка будем проверять приблизительное равенство количества точек, отклонившихся от среднего в положительную и в отрицательную сторону.
12) Создайте столбец, в котором будет записано число отклонений показаний прибора от среднего значения в положительную сторону. Для этого воспользуйтесь функцией СЧЕТЕСЛИ( ). Аргументами этой функции служат диапазон ячеек, в котором ведется подсчет, и условие, которое при этом проверяется. В качестве диапазона укажите набор ячеек, по которым проводилось усреднение. В качестве условия запишите “ >0 “
13) Создайте столбец стандартных отклонений, в котором для тех кадров, когда число положительных отклонений от среднего N£3, указывается некоторое значение, не попадающее на график (например, -1). Воспользуйтесь функцией ЕСЛИ( ), имеющей аргументы: a)условие, б)значение, которое функция принимает при выполнении условия в)Значение, которое будет при невыполнении условия. Укажите условие “>3”, «значение, если выполнено» возьмите из ячейки стандартных отклонений, в качестве альтернативного значения укажите «-1»
14) Аналогичным образом создайте столбец для исключения кадров при условии N³8. Для этого также воспользуйтесь функцией ЕСЛИ( ). В качестве значения при выполнении условия укажите значение, вычисленное в предыдущем пункте. Используйте сложное условие, позволяющее также исключить нечисловые значения, появившиеся из-за сбоев в исходной информации. Объединение двух условий в одно осуществляется функцией И( ), в аргументах которой следует перечислить соответствующие условия. В нашем случае одно из них – “<8”, другое проверяется функцией ЕЧИСЛО( ).
15) Добавьте на диаграмму, показывающую зависимость отклонений от величины среднего значения, дополнительный ряд точек, вычисленный в предыдущем пункте. Убедитесь, что эти точки лучше описываются теоретической зависимостью. Сделайте вывод о том, описывается ли разброс показаний прибора распределением Пуассона.
16) Определите, на сколько стандартных отклонений должны показания прибора в одном кадре превышать среднее значение, чтобы можно было утверждать наличие короткого (в пределах 1 кадра) всплеска или резкого уменьшения потока. Потребуйте, чтобы вероятность случайного отклонения хотя бы в одной точке на всём анализируемом ряду не превышала 1%. Из этого следует, что вероятность случайного отклонения в конкретной точке не должна превышать величину P=0.01/N, где N – общее количество точек. Зная общее число точек, вычислите величину Р и определите порог значимости по таблице.
Примечание: В таблице приводятся вероятности для нормального распределения. Распределение
Пуассона приближается к нормальному с ростом среднего значения (близко при m >10)
Приложение: Использование электронных таблиц Microsoft Excel
для анализа данных и моделирования.
Использование Excel для автоматических вычислений.
После загрузки Excel пользователь видит экран, разбитый на ячейки (в виде таблицы). Каждая ячейка имеет адрес, задаваемый буквой и числом аналогично известной игре «Морской бой». Например, на приведенном ниже рисунке в ячейке В9 записано число 11. В каждый момент времени одна из ячеек является текущей. Она выделяется рамкой с небольшим черным квадратиком в нижнем правом углу (на рис. это ячейка С4). При наборе на клавиатуре числа или текста изменения вносятся именно в текущую ячейку.
В каждой ячейке может быть записано число, текст или формула. Если Excel может интерпретировать содержимое как число, он считает содержимое числом. При вводе чисел нужно обратить внимание на то, что в качестве разделителя в десятичных дробях следует использовать либо точку, либо запятую в зависимости от установок Windows (панель управления à язык и стандарты à числа). Щелкая по соответствующим иконкам, можно менять разрядность представления чисел в ячейке.
Если содержимое начинается со знака равенства, то компьютер его интерпретирует как формулу. Формулу, записанную в ячейке, можно увидеть в строке формул, если сделать ячейку текущей. В самой ячейке при этом будет отображаться результат вычислений. Формула может содержать числа, адреса других ячеек и функции. Для ввода функции можно воспользоваться иконкой [fx], позволяющей выбрать соответствующую функцию из списка, после чего ввести аргументы функции вручную или с помощью соответствующего мастера. Для удобства и надежности ввода адресов ячеек можно воспользоваться мышкой: при редактировании формулы (сделали ячейку текущей и выбрали нужное место в строке формул) щелчок левой кнопкой мышки по произвольной ячейке приводит к появлению ее адреса в текущем месте строки формул. Наиболее часто используется функция СУММ( ), вычисляющая сумму ячеек в диапазоне, указанном в качестве аргумента. Для быстрого ввода этой функции предусмотрена иконка [S] . Результат всегда записывается в текущую ячейку. В качестве аргументов функции СУММ() Excel по умолчанию предлагает диапазон содержащих числа ячеек, расположенных сверху (или слева) от текущей. Обратите внимание, что диапазон ячеек записывается через двоеточие (например, С4:С13). Еще одна часто используемая функция – функция СРЗНАЧ( ) – вычисляет среднее арифметическое значение аргументов, указанных в скобках. В качестве аргументов могут быть перечислены через запятую числа, адреса отдельных ячеек и диапазоны ячеек.
Если компьютер не может интерпретировать содержимое ни как число, ни как формулу, он считает, что в ячейке записан текст. Если текст превышает размеры ячейки, то он будет виден полностью, только в том случае, если соседние ячейки пустые. Рекомендуется объединять ячейки таким образом, чтобы текст помещался полностью (выделяя левой кнопкой мышки набор ячеек и щелкая по белой иконке со стрелочками). Для наглядности оформления электронной таблицы можно использовать различные форматы шрифта (аналогично работе с MS Word). Также можно использовать выравнивание по центру, по левому или по правому краю.
Кроме содержимого у каждой ячейки существует определенный формат, который в процессе работы также может меняться. К свойствам ячейки, совокупность которых и составляет ее формат, относятся заливка, наличие и вид границ (на рис. … у ячеек никаких границ нет и при печати таблицы никакой разлиновки не будет), а также форма представления содержимого. Например, если в ячейке записано число, то оно может интерпретироваться как обычное число, или как дата, или как доля в процентах, и т. п. Если Вы случайно изменили формат ячейки неправильным образом, то вернуться к исходному виду («общий формат») можно, выбрав пункт меню правка à очистить à форматы .
При каждом переходе в процессе редактирования к другой ячейке (с помощью мышки или клавиш со стрелками, а также при нажатии [Enter]) компьютер автоматически обновляет содержимое всех ячеек. В частности, числа, отображаемые в ячейках, содержащих формулы, автоматически вычисляются в соответствие с обновленными данными, используемыми в этих формулах. Диаграммы и графики, построенные на основе записанных в ячейки данных (см. ниже), также автоматически обновляются. На этом основан наиболее обычный порядок использования Excel: пользователь «программирует» автоматически вычисляемую таблицу, после чего, каждый раз заполняя ее новыми данными, получает необходимые результаты.
Курсор мышки при работе с Excel может быть трех различных видов и, соответственно, пользователь при этом может выполнять различные действия.
1) Чаще всего курсор имеет вид широкого светлого креста. Нажав на левую кнопку мышки и двигая ее, в этом случае можно выделить группу ячеек. Для добавления к группе следующего набора ячеек выделяйте их при нажатой клавише [Ctrl].
2) Если подвести курсор к границе текущей ячейки или выделенной области, курсор принимает вид стрелки. Нажав левую кнопку мышки, можно передвинуть содержимое ячеек на другое место. Обратите внимание, что во всех местах электронной таблицы, где были использованы адреса перемещаемых ячеек, формулы будут автоматически отредактированы таким образом, что никакие значения не изменятся.
3) Если курсор подвести к нижнему правому углу текущей ячейки или выделенной области, то он будет иметь вид маленького черного плюса. В этом режиме происходит копирование ячеек. В зависимости от вида содержимого процесс копирования будет происходить по-разному. Если содержимое представляет собой число или текст, при этом копируется одна ячейка, то происходит обыкновенное копирование. Если взять две последовательные ячейки, заполненные числами, то копирование приводит к заполнению последующих ячеек арифметической прогрессией. Если содержимое представляет собой формулу, то копируется не результат вычислений, а сама формула. При этом адреса ячеек, входящих в нее изменяются следующим образом. При копировании вверх-вниз каждый раз цифровая часть адреса ячеек, входящих в формулу, меняется на соседнее значение. При копировании вправо-влево то же самое происходит с буквенной частью адреса. В тех случаях, когда адрес некоторой ячейки, входящей в формулу, при копировании меняться не должен (например, в ячейке записан некоторый коэффициент) используется абсолютный адрес: перед цифровой, или буквенной, или и той и другой частью адреса ставится символ $ . В этом случае соответствующая компонента адреса останется неизменной.
При работе с данными большого объема удобно использовать несколько электронных «листов» с данными, формулами и диаграммами, объединенных в одну «книгу». «Книга» Excel хранится в одном файле. Переход от листа к листу осуществляется путем выбора соответствующей вкладки, расположенной внизу экрана.
Построение диаграмм и графиков.
Для построения диаграммы или графика в Excel предназначена иконка с изображением нескольких цветных столбиков. Нажатие на нее запускает «мастер диаграмм», предлагающий определить тип диаграммы, отображаемые данные, надписи и т. п. Многие из параметров диаграммы (график – это один из видов диаграммы) компьютер устанавливает по умолчанию. Впоследствии эти параметры можно отредактировать по своему усмотрению.
Перед запуском мастера диаграмм рекомендуется выделить диапазон ячеек, содержащих отображаемые данные. Это поможет избежать ручного ввода этих диапазонов. Следует, однако, обратить внимание, что компьютер по-разному интерпретирует столбцы (строки) выделенных данных для диаграмм различного типа. Нужно всегда внимательно проверять правильность отображения данных.
В процессе построения диаграммы пользователь последовательно проходит 4 шага:
1) Пользователь выбирает тип диаграммы. Наиболее часто используются:
- 
Столбчатая диаграмма (гистограмма) используется для сопоставления данных, относящихся к различным случаям или вариантам (времени, фирме и т. п.)
- Круговая диаграмма наглядно показывает долю составных частей в целом
- График отображает последовательное изменение данных от случая к случаю. В отличие от математики, по оси абсцисс отображается порядковый номер случая.
- Точечная диаграмма соответствует обычному для математики понятию графика. Можно выбрать варианты, в которых точки соединяются или не соединяются линиями. Линии могут быть сглаженными или ломаными.
2) Пользователь определяет исходные данные. Сначала указывает (обычно просто подтверждает), что данные расположены в столбцах, либо в строках. Затем определяет (либо просто подтверждает) сами диапазоны отображаемых данных. Они указываются в полях «Значения Х», «Значения Y» для каждого ряда. Щелчок левой кнопкой мыши на цветном квадрате справа от соответствующего поля запускает мастер, дающий возможность определить диапазон данных с помощью мыши. Следует задать имя для каждого ряда (вместо «ряд 1», … , используемых по умолчанию). Именно под этим именем ряды перечисляются в легенде – подписи рядов, обычно расположенной справа от диаграммы. Для некоторых типов диаграмм, отличных от графика или точечной диаграммы, на той же вкладке «ряд» задаются подписи категорий.
3) Пользователь задает текст заголовков и некоторые другие параметры оформления диаграммы. Можно задать «подписи данных», указывающих значения в каждой точке.
4) Пользователь определяет, будет ли диаграмма расположена на том же листе, где отображаемые данные, или для нее будет создан отдельный лист. Первый вариант используется в случае сравнительно небольшого объема данных, в этом случае удобно просматривать, а также распечатывать, данные вместе с диаграммами. Второй случай используется при анализе сложных диаграмм и графиков.
Для последующего редактирования диаграммы пользователь выделяет один из ее элементов – область построения, ось, ряд данных, легенда и т. п. щелчком правой кнопки мыши. Затем выбирается пункт контекстного меню «формат», после чего соответствующему элементу задаются новые свойства. Важные частные случаи - изменение шкалы осей, цвета и вида заливки столбцов, типа соединяющих линий, отображения вертикальной или горизонтальной сетки. При необходимости можно вернуться к каждому из исходных четырех шагов, щелкнув правой кнопкой мышки на свободном поле диаграммы и выбрав соответствующий пункт контекстного меню.
Объединение данных из нескольких файлов. Экспорт данных, записанных в других форматах.
Для переноса данных из одной электронной таблицы в другую можно использовать буфер обмена. Последовательность действий такова:
- Открыть таблицу, содержащую нужные данные.
- Выделить необходимые данные с помощью мышки. Для выделения всего столбца щелкните мышкой по его заглавной букве.
- Скопировать данные в буфер обмена. Это можно сделать, выбрав пункты меню правка à копировать, либо выбрав аналогичный пункт копировать в контекстном меню (щелкнув правой кнопкой по выделенной области) , либо используя иконку с изображением двух листов бумаги. Для копирования в буфер обмена обычно предусмотрена комбинация клавиш [Ctrl]+C.
- Перейти в ту таблицу, куда нужно вставить данные. Сделать текущей ту ячейку, начиная с которой должны размещаться вставляемые данные.
- Выбрать пункты меню правка à вставить, либо выбрать аналогичный пункт вставить в контекстном меню, либо использовать иконку с изображением папки и листа бумаги. Для вставки из буфера обмена обычно предусмотрена комбинация клавиш [Ctrl]+V.
Важно уметь вставить в электронную таблицу для последующей обработки средствами Excel данные, хранящиеся в файлах других форматов. Обычно представляют интерес файлы текстовых форматов, создаваемые в результате использования различных программных средств. Эти файлы как правило имеют символьную кодировку (возможны варианты кодировки Windows или DOS), т. е. устроены аналогично файлам, создаваемым редактором «Блокнот». Естественно, их расширение не обязательно совпадает с. txt.
При открытии подобного файла (файл à открыть) следует выбрать тип файла «все файлы», после чего в процессе открытия указать, как записаны данные. Сначала (первый шаг) Excel предложит указать кодировку файла и выбрать один из вариантов формата данных: либо «с разделителями», т. е. когда одно значение от другого отделяется некоторым стандартным символом, либо «фиксированной ширины», когда под каждое значение в столбце отводится определенное число позиций. И в том, и в другом случае переход на новую строку в исходном файле будет интерпретироваться как переход к новой строке в электронной таблице. Переход к новому значению будет означать переход к соседней ячейке таблицы.
Затем (второй шаг) в зависимости от выбранного формата нужно либо указать символы, используемые в качестве разделителя, либо, смещая соответствующие разделительные линии, определить границы столбцов в исходном файле. И в том, и в другом случае обратите внимание на предварительный просмотр результата. Последний, третий, шаг позволяет определить формат данных для каждого из столбцов и, возможно, пропустить некоторые из них.
Нажатие на иконку [готово] завершает операцию, в результате которой данные из файла отображаются в виде электронной таблицы. Эту таблицу можно сохранить в формате Excel. Если в сохранении нет необходимости, можно перенести через буфер обмена нужные данные в уже существующую электронную таблицу, после чего закрыть файл, не внося изменений.
Кроме описанного выше способа можно воспользоваться пунктом меню данные à внешние данные à импорт текстового файла. Далее процедура импорта во многом сходна с вышеописанной. В конце Excel предлагает либо поместить данные на отдельный лист, либа на уже имеющийся.
Функции Excel, наиболее часто используемые в процессе обработки информации.
Функции, предоставляемые электронными таблицами Excel, могут иметь один и более аргументов, помещаемых в скобки и отделяемых при записи точкой с запятой. В качестве аргументов, в зависимости от их назначения, могут использоваться явно заданные числа, адреса отдельных ячеек или диапазоны ячеек. Если указывается диапазон, т. е. записываются через двоеточие адреса двух ячеек, то в качестве аргумента рассматриваются все ячейки между указанными. Во многих случаях в качестве параметра допускается перечисление набора чисел, ячеек и диапазонов через запятую. Используя ту или иную функцию, следует обратить внимание, в каком виде должны быть (могут быть) указаны ее аргументы.
Все функции в Excel разделены на ряд больших групп, к которым относятся:
1) Математические функции, такие, как:
- КОРЕНЬ( ) вычисляет квадратный корень.
- SIN( ), COS( ), TAN( ) – тригонометрические функции.
- ASIN( ), ACOS( ), ATAN( ) – обратные тригонометрические функции.
- ABS( ) вычисляет модуль
- EXP( ) вычисляет экспоненту, т. е. величину ex.
- СТЕПЕНЬ( ) возводит число в степень.
- LN( ), LOG( ) и LOG10( ) – натуральный, произвольный и десятичный логарифмы.
- СУММ( ) и ПРОИЗВЕД( ) вычисляют сумму и произведение содержимого ячеек, указанных в качестве аргумента.
- ОКРУГЛ( ) округляет число до заданного числа знаков. ОТБР( ) отбрасывает дробную часть (всю или частично).
- СЛЧИС( ) генерирует случайное число в диапазоне от 0 до 1. Функция параметров не имеет. Случайные числа распределены равномерно. Значения функции обновляются при любых внесениях изменений в электронную таблицу.
2) Статистические функции, позволяющие легко вычислить многие величины, использующиеся при статистической обработке данных:
- СРЗНАЧ( ) вычисляет среднее значение.
- СТАНДОТКЛОН( ) вычисляет стандартное отклонение
- МИН( ) и МАКС( ) определяют, соответственно, минимальное и максимальное значение.
- НАКЛОН( ) и ОТРЕЗОК( ) вычисляют методом наименьших квадратов параметры k и b прямой y = k*x + b. Эти функции имеют по два аргумента. В качестве первого указываются ячейки (диапазон), содержащие значения Y, а в качестве второго – X
- КОРРЕЛ( ) вычисляет коэффициент корреляции двух рядов данных.
- НОРМРАСП( ) вычисляет значение в точке Х (первый аргумент) нормальной функции распределения, заданного средним значением и стандартным отклонением (второй и третий аргументы). Для получения обычного распределения в качестве четвертого аргумента указывается логическое значение ЛОЖЬ (можно ввести явно словом). Указав значение ИСТИНА, можно получить интегральное нормальное распределение, дающее вероятность получить значение меньше X. Разность между его значениями в точках X1 и X2 дает вероятность получить значение на интервале [X1,X2].
- НОРМОБР( ) вычисляет обратное нормальное распределение (интегральное), то есть определяет значение X, при котором достигается заданная в качестве первого аргумента вероятность получить значение меньше X. Параметры распределения - среднее и стандартное отклонение - задаются вторым и третьим аргументами.
- СЧЕТЕСЛИ( ) подсчитывает непустые ячейки из заданного диапазона (первый аргумент), удовлетворяющие заданному условию (второй аргумент).
- СУММЕСЛИ( ) суммирует значения из ячеек, диапазон которых указан в третьем аргументе в том случае, если соответствующая ячейка из диапазона, указанного в первом аргументе, удовлетворяет условию, записанному во втором аргументе.
3) Логические функции:
- И( ), ИЛИ( ) возвращают значения ИСТИНА или ЛОЖЬ согласно заявленной логической операции над своими аргументами
- НЕ( ) меняет логическое значение на противоположное
- ЕСЛИ( ) принимает в зависимости от истинности или ложности логического выражения одно из указанных значений.
Кроме вышеперечисленных в Excel существует множество других функций. Они позволяют производить более сложные математические расчеты (например, матричные преобразования), проводить статистическую обработку данных при помощи методов, выходящих за рамки этого пособия (с использованием распределений, отличных от нормального, специальных критериев проверки гипотез и т. п.). Также есть группы функций, осуществляющие операции над текстовыми строками, преобразование форматов времени и даты и т. п. При необходимости все эти функции можно использовать, разобравшись самостоятельно.
