Электронные таблицы MS Excel. Массивы данных и их обработка (стр. 1 )

МОСКОВСКИЙ АВИАЦИОННЫЙ ИНСТИТУТ

(ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ)

Кафедра 303

Авиационные приборы

и измерительно-вычислительные комплексы

I вычислительная практика

Электронные таблицы MS Excel.

Массивы данных и их обработка

Учебно-методические материалы

Под редакцией ,

Москва, 2007 год

Содержание

Цели практического курса MS Excel 3

Задание на I вычислительную практику. 3

Основные сведения об электронных таблицах. 4

Основы работы с MS Excel 4

Построение графика. 6

Аппроксимация дискретных данных

с использованием метода наименьших квадратов (МНК) 7

Применение таблиц подстановки для построения графиков. 10

Математико-статистическая обработка и анализ результатов измерений. 11

Варианты заданий. 13

Требования к отчёту. 22

Контрольные вопросы.. 22

Литература. 23

В Excel существует возможность объединения нескольких ячеек в одну (группировка). При необходимости получения данных из сгруппированных ячеек следует обратиться к первой (левой верхней) ячейке группы. Если же обратиться к любой другой ячейке группы, то это не вызовет ошибки, но возвращаемое значение будет равно нулю.

Чтобы в ячейке таблицы получить некоторое значение, вычисляемое на основе данных их других ячеек, необходимо указать функцию в строке формул, находящейся над рабочей областью электронной таблицы. Перед вводом любой формулы необходимо ставить знак равенства “ = ”.

Адресация ячеек может быть относительной и абсолютной. Абсолютная адресация используется для многократных вычислений с содержимым адресуемой ячейки на некотором множестве. Может задаваться например так: =2*$C$2, т. е. знак “$” выполняет роль фиксатора для указателя на адрес ячейки. При такой записи все вычисления, связанные с автоматическим заполнением ряда данными будут производиться только с ячейкой C2. Относительная адресация позволяет варьировать адрес при заполнении некоторого ряда данных. Комбинирование абсолютного и относительного адресов в вычисляемой формуле позволяет корректно указать табличному процессору порядок заполнения ряда данных.

Рисунок 1. Таблица с данными.

Чтобы заполнить некоторое множество ячеек данными, вычисляемыми по одной и той же формуле, необязательно записывать её в каждую ячейку, а достаточно указать её лишь в одной ячейке, правильно применив оператор фиксации “$”. Потянув за правый нижний уголок ячейки с формулой можно сформировать желаемый ряд данных с использованием относительной адресации.

Подробную информацию об имеющихся функциях и их синтаксисе всегда можно найти в файлах справки табличного процессора MS Excel или при вызове мастера вставки функции (кнопка fx перед строкой формул).

MS Excel имеет мощные средства форматирования данных и документов, обширную библиотеку стилей и шаблонов, развитые средства визуализации данных (построение диаграмм, подключение сторонних графических объектов и программных модулей), возможность сетевой поддержки таблиц (распределённый доступ к таблице по сети, получение данных из других таблиц) и др.

Построение графика

Предположим, что вы провели эксперимент, в результате которого определили статическую характеристику некоторого датчика, т. е. установили некоторую дискретную зависимость выходного сигнала от входного воздействия.

Запустите программу Excel (Пуск > Программы > Microsoft Excel) и откройте свою рабочую книгу, созданную ранее.

Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый (Вставка > Лист) Дважды щелкните на ярлычке листа и переименуйте его как Обработка эксперимента.

В столбец A, начиная с ячейки A1, введите произвольный набор значений независимой переменной. Для определенности пусть это будет следующий набор из 10 чисел:

10 60   210   

В столбец B, начиная с ячейки B1, введите произвольный набор значений функции. Для определенности пусть это будет следующий набор из 10 чисел

30

Методом протягивания выделите все заполненные ячейки столбцов A и В.

Щелкните на значке Мастер диаграмм на стандартной панели инструментов.

В списке Тип выберите пункт Точечная (для отображения графика, заданного парами значений). В палитре Вид выберите «маркеры, соединенные гладкими кривыми». Щелкните на кнопке Далее.

Так как диапазон ячеек был выделен заранее, мастер диаграмм автоматически определяет расположение рядов данных. Убедитесь, что данные на диаграмме выбраны правильно. На вкладке Ряд в поле Имя укажите Результаты измерений. Щелкните на кнопке Далее.

Выберите вкладку Заголовки. Убедитесь, что заданное название ряда данных автоматически использовано как заголовок диаграммы. Замените его, введя в поле Название диаграммы заголовок Экспериментальные точки. Щелкните на кнопке Далее.

Установите переключатель На отдельном, чтобы диаграмма была выведена на отдельный лист. По желанию задайте произвольное имя добавляемого рабочего листа. Щелкните на кнопке Готово.

Убедитесь, что диаграмма построена и внедрена в новый рабочий лист. Рассмотрите ее и щелкните на построенной кривой (по точке), чтобы выделить ряд данных.

Дайте команду Формат > Выделенный ряд. Откройте вкладку Вид. На панели Линия откройте палитру Цвет и выберите красный цвет. В списке Тип линии выберите пунктир. На панели Маркер выберите в списке Тип маркера треугольный маркер. В палитрах Цвет и фон выберите зеленый цвет.

Щелкните на кнопке ОК, снимите выделение с ряда данных и посмотрите, как изменился вид графика. Дважды щелкните по диаграмме, чтобы вернуться к настройкам. Выберите нужную толщину линии и размер маркера. Оцените результат.

Сохраните рабочую книгу.

Аппроксимация дискретных данных

с использованием метода наименьших квадратов (МНК)

Аппроксимация (лат. approximare – «приближаться») – замена одних математических объектов другими, в том или ином смысле близкими к исходным.

Аппроксимация позволяет исследовать числовые характеристики и качественные свойства объекта, сводя задачу к изучению более простых или более удобных объектов (например, таких, характеристики которых легко вычисляются или свойства которых уже известны). В нашем задании будем рассматривать частный случай аппроксимации – аппроксимацию экспериментальных данных и кривых, построенных на их основе.

Рассмотрим типовую задачу аппроксимации в инженерной практике – подбор математического выражения, описывающего связь между экспериментальными данными. Само математическое выражение называют уравнением регрессии (регрессией), а соответствующую кривую – линией регрессии. Чтобы подобрать наилучшую в некотором смысле регрессию, необходимо установить критерий оптимального её выбора. Одним из широко применяемых на практике критериев оптимальности регрессии является критерий минимума суммы квадратов. В соответствии с этим критерием наилучшее согласование линии регрессии с результатами измерения достигается при выполнении условия:

, (1)

где – уравнение регрессии,

– дискретное множество экспериментальных данных.

Его применение позволяет при определении линии регрессии использовать метод наименьших квадратов, обеспечивающий построение линии регрессии, характеризуемой минимальным средним квадратом её отклонения от результатов эксперимента.

После определения критерия оптимальности регрессии следует перейти к выбору типа уравнения регрессии. Тип уравнения в значительной мере зависит от вида экспериментальных данных.

Далее необходимо найти конкретные значения параметров уравнения регрессии, в общем случае составив систему уравнений вида:

, (2)

где Q – математическая запись выбранного критерия,

pi – параметры уравнения регрессии.

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

Задача. В предыдущем задании на основе значений независимой переменной вы построили функцию. В этом задании для заданного набора пар значений независимой переменной и функции на основе метода наименьших квадратов (МНК) определим наилучшее приближение этой функции в двух формах: прямой с уравнением y=ax+b и линии с уравнением y=bax.

Для нахождения коэффициентов a и b используем функции ИНДЕКС, ЛИНЕЙН и ЛГРФПРИБЛ[1].

Запустите программу Excel (Пуск > Программы > Microsoft Excel) и откройте свою рабочую книгу.

Щелчком на ярлычке выберите рабочий лист Обработка эксперимента. Если нет такого листа, то вам следует вернуться к предыдущему заданию.

Сделайте ячейку C1 текущей и щелкните на кнопке Вставка функции в строке формул (в строке формул значок fx).

В появившемся окне мастера функций перейдите в раздел Категория и выберите категорию Ссылки и массивы, и затем функцию ИНДЕКС. Нажмите ОК. В новом диалоговом окне выберите первый вариант набора параметров и нажмите ОК. Появится новое окно Аргументы функции.

Установите текстовый курсор в первое поле (массив) для ввода параметров в окне Аргументы функции и затем перейдите к строке формул (в ее левую часть, где надпись ИНДЕКС, справа от которой значок раскрывающегося списка) и выберите в раскрывающемся списке пункт Другие функции.

В появившемся окне мастера функций выберите категорию Статистические, а затем функцию ЛИНЕЙН. В результате немного изменится внешний вид окна Аргументы функции, которое теперь предназначено для функции ЛИНЕЙН.

Установите курсор в окне первого параметра (известные значения y) функции ЛИНЕЙН и методом протягивания выберите диапазон, содержа­щий значения функции (столбец В). Этот диапазон должен появиться в окне первого параметра. Установите курсор в окно второго параметра (известные значения x).

Аналогичным образом в качестве второго параметра функции ЛИНЕЙН выберите диапазон, содержащий значения независимой переменной (столбец А). Нажмите на кнопку ОК. Согласитесь (нажмите ОК) с появившимся сообщением о слишком малом числе введенных аргументов.

Текстовый курсор в строке формул перейдет на функцию ИНДЕКС и установится перед правой скобкой. Введите знак точки с запятой (;) и затем в качестве второго параметра функции ИНДЕКС задайте число 1. Щелкните на кнопке Enter. Вы увидите, что в ячейке C1 появится число, равное коэффициенту a уравнения прямой.

Пояснение: функция ЛИНЕЙН возвращает коэффициенты уравнения прямой в виде массива из двух элементов. С помощью функции ИНДЕКС выбирается нужный элемент из этих двух элементов.

Сделайте текущей ячейку D1. Повторите операции, описанные выше, чтобы в итоге в этой ячейке появилась формула: =ИНДЕКС(ЛИНЕЙН(В1:В10,А1:А10);2). Ее можно ввести и вручную (посимвольно). Теперь в ячейках C1 и D1 вычислены, соответственно, коэффициенты а и b уравнения наилучшей прямой.

Сделайте текущей ячейку C2. Повторите операции, описанные выше, или введите вручную следующую формулу: =ИНДЕКС(ЛГРФПРИБЛ (В1:В10;А1:А10);1)

Сделайте текущей ячейку D2. Повторите операции, описанные выше, или введите вручную следующую формулу: =ИНДЕКС(ЛГРФПРИБЛ (В1:В10;А1:А10);2)

Теперь ячейки C2 и D2 содержат, соответственно, коэффициенты а и b уравнения наилучшего показательного приближения.

Наша задача состоит в построении на одном графике сразу трёх зависимостей: линии исходной функции, ее приближения с помощью прямой и с помощью показательной функции. Для этого надо иметь численные значения этих функций во всех точках. Ниже рассмотрен один из способов построения нескольких графиков на одной диаграмме.

Откройте лист Обработка эксперимента и создайте его копию. Далее в этом разделе будем иметь дело только с новым листом Обработка эксперимента 1.

В ячейках E1:E10 рассчитайте значения прямой линии, используя значения коэффициентов a и b из ячеек С1 и D1 соответственно. Подсказка: расчет для ячейки E1 следует вести по формуле =$C$1*A1+$D$1, а затем потянуть за уголок этой ячейки, чтобы заполнить ряд данных.

Теперь аналогичным образом рассчитайте значения показательной функции в столбце F. Расчет следует вести, начиная с ячейки F1, по формуле =$D$2*$C$2^A1, где ^ - это знак степени.

Выделите диапазон ячеек[2] A1:B10 и E1:F10 и нажмите на кнопку Мастер диаграмм. В разделе Тип выберите Точечная, а в разделе Вид – гладкая кривая с маркерами. Нажимайте кнопку Далее, делайте интересующий вас выбор параметров диаграммы и, в частности, обеспечьте выдачу графиков на отдельном листе (именуйте лист по своему усмотрению).

Отредактируйте каждую кривую по своему усмотрению так, чтобы графики хорошо смотрелись. Итак, на одной диаграмме у вас должны были быть построены 3 графика – исходная экспериментальная кривая, наилучшая аппроксимация этой кривой прямой линией и наилучшая аппроксимация показательной функцией.

Применение таблиц подстановки для построения графиков

Задача. Рассмотрим другой способ построения нескольких графиков на одной диаграмме. Нужно построить графики функций, коэффициенты которых определены в предыдущем упражнении.

Запустите программу Excel (Пуск > Программы > Microsoft Excel) и откройте свою книгу, если это еще не сделано.

Выберите щелчком на ярлычке рабочий лист Обработка эксперимента. Так как программа Excel не позволяет непосредственно строить графики функций, заданных формулами, необходимо сначала табулировать формулу, то есть создать таблицу значений функций для заданных значений переменной. Сделайте текущей ячейку С3 и занесите в нее значение 0. Эта ячейка будет использоваться как ячейка ввода, на которую будут ссылаться формулы.

Методом протягивания выделите значения в столбце А. Дайте команду Правка > Копировать, чтобы перенести эти данные в буфер обмена. Сделайте текущей ячейку F2 и дайте команду Правка > Вставить, чтобы скопировать заданные значения независимой переменной в столбец F, начиная со второй строки.

В ячейку G1 введите формулу =C3*$C$1+$D$1. Здесь С3 — ячейка ввода, а в качестве других ссылок используются вычисленные методом наименьших квадратов коэффициенты уравнения прямой.

В ячейку Н1 введите формулу =$D$2*$C$2^C3 для вычисления значения показательной функции. В программе Excel можно табулировать несколько функций одной переменной в рамках единой операции.

Выделите прямоугольный диапазон, включающий столбцы F, G и Н и строки от строки 1, содержащей формулы, до последней строки с данными в столбце F.

Дайте команду Данные > Таблица подстановки. Выберите поле Подставлять значения по строкам в и щелкните на ячейке ввода С3.

Щелкните на кнопке ОК, чтобы заполнить пустые ячейки в столбцах G и Н выделенного диапазона значениями формул в ячейках первой строки для значений независимой переменной, выбранных из столбца F.

Переключитесь на рабочий лист Диаграмма1 (если используемое по умолчанию название листа с диаграммой было изменено, используйте свое название).

Щелкните на кнопке Мастер диаграмм на стандартной панели инструментов и пропустите первый этап щелчком на кнопке Далее.

Выберите вкладку Ряд и щелкните на кнопке Добавить. В поле Имя укажите: Наилучшая прямая. Здесь же переведите курсор в поле Значения X, затем перейдите на основной лист с данными (это можно сделать, нажав на кнопку, находящуюся справа от поля, для выбора диапазона значений) и выделите диапазон ячеек с данными в столбце F. Проследите, чтобы ссылка на него появилась в поле Значения X. Аналогичным образом в поле Значения Y укажите диапазон ячеек в столбце G.

Еще раз щелкните на кнопке Добавить. В поле Имя укажите: Показательная функция. По аналогии с предыдущим пунктом в поле Значения X укажите диапазон ячеек с данными в столбце F, а в поле Значения Y укажите диапазон ячеек в столбце Н.

Щелкните на кнопке Готово, чтобы перестроить диаграмму. Сохраните рабочую книгу.

Математико-статистическая обработка и анализ результатов измерений

Задача. В течение некоторого времени был сделан ряд замеров температуры (общее количество замеров определить по исходным данным) одновременно тремя датчиками, работа которых основана на различных физических принципах. Известно, что в течение этого времени среднее значение темпе­ратуры составляло m = t °C. Среднеквадратическое отклонение (с. к.о.) показаний эта­лонного датчика составляет σ.

Определить выборочное среднее значение данных, полученных с термометров № 1 и № 2, и их исправленную выборочную дисперсию. С помощью мастера диаграмм MS Excel построить графики плотностей распре­деления показаний всех датчиков, если известно, что эти плотности подчинены нормальному закону распределения.

По полученным оценкам сделать выводы о точности измерения тем­пературы датчиками № 1 и № 2 относительно эталонного.

Справочная информация

1)  Выборочное среднее:

ni – частота события xi;

n – объём выборки (общее количество замеров), ;

k – количество различных значений измерений.

2)  Исправленная выборочная дисперсия (дает несмещенную оценку в случае п < 30):

.

3)  «Исправленное» с. к. о.:

.

4)  Плотность нормального распределения случайной величины Х:

.

Здесь удобнее воспользоваться встроенной функцией НОРМРАСП. При построении графиков для термометров № 1 и № 2 считать а = , σ = S.

5)  Необходимые функции из мастера функций MS Excel:

·  НОРМРАСП с параметром «ЛОЖЬ»

·  СУММ

·  СТЕПЕНЬ

·  КОРЕНЬ

Описание и параметры функций посмотреть в файлах справки MS Excel.

Варианты заданий

Таблицы данных для первой части задания.

Вариант №1
X	10	60	110	160	210	260	310	360	410	460
Y	30	39	50	65	85	211	250	290	350	460

Вариант №2
X	20	40	120	140	200	250	320	370	440	480
Y	30	39	50	65	85	211	250	290	350	460

Вариант №3
X	10	60	110	160	210	260	310	360	410	460
Y	20	49	58	60	95	220	280	335	410	490

Вариант №4
X	18	25	36	49	57	95	150	198	280	350
Y	20	28	32	39	42	70	130	151	242	320

Вариант №5
X	28	54	72	104	126	154	198	244	280	310
Y	20	60	80	100	125	165	212	270	330	350

Вариант №6
X	25	55	60	75	85	102	120	140	155	162
Y	20	40	55	72	86	101	115	145	150	165

Вариант №7
X	20	25	36	49	66	100	150	198	270	320
Y	18	23	32	39	52	70	130	140	208	234

Вариант №8
X	7	19	25	41	65	87	98	130	164	181
Y	330	250	200	165	135	120	100	70	52	42

Вариант №9
X	18	25	36	49	57	95	150	198	280	350
Y	200	190	180	165	140	115	90	60	25	8

Вариант №10
X	10	22	29	45	57	81	115	130	150	185
Y	20	45	59	85	100	148	200	255	305	382

Вариант №11
X	3	5	15	25	39	45	48	58	67	80
Y	1	8	12	24	42	40	39	50	69	88

Вариант №12
X	30	48	65	81	98	118	146	165	192	217
Y	20	40	60	77	92	118	159	180	225	258

Вариант №13
X	50	55	59	62	68	75	82	94	101	115
Y	8	16	32	55	72	88	100	125	158	175

Вариант №14
X	30	31	32	49	65	95	150	155	160	165
Y	2	4	9	28	57	89	148	162	175	192

Вариант №15
X	15	25	36	57	72	95	120	198	240	310
Y	20	25	38	60	70	95	125	215	260	350

Вариант №16
X	10	30	55	62	88	109	125	151	185	213
Y	10	29	40	70	100	125	175	213	270	360

Вариант №17
X	2	8	16	35	50	78	89	100	125	138
Y	2	7	13	33	45	70	79	90	95	98

Вариант №18
X	15	28	37	49	54	95	150	188	280	310
Y	20	28	32	39	42	70	130	151	242	320

Вариант №19
X	5	25	36	47	56	95	140	192	270	300
Y	21	28	33	39	42	72	130	156	242	320

Вариант №20
X	18	25	36	49	57	95	150	198	280	350
Y	20	28	32	39	42	70	130	151	242	320

Таблицы данных для второй части задания.

Из за большого объема этот материал размещен на нескольких страницах: 1 2