Примеры использования возможностей приложения Excel

Пример 9.1. Построить график зависимости теплоемкости компонента Ci8H36 от температуры. Значения теплоемкостей и соответствующие температуры приведены в табл. 9.1.

Таблица 9.1

Температура,

К

300

400

500

600

700

800

900

1000

Теплоемкость, кал/(моль • К)

97,71

132,32

146,42

166,00

182,65

196,80

209,01

219,50

Последовательность выполнения операций:

  • 1. Запустите программу Excel.
  • 2. Поставьте курсор в ячейку А и, пользуясь клавиатурой, приступите к набору табличных данных (вещественная часть отделяется запятой). В верхнюю строку помещается шапка таблицы, в левый столбец - значения аргумента.

Примечание: переход в ячейку А2 и последующие ячейки осуществляется с помощью клавиши Enter; переход в начало следующего столбца (В, С и т. д.) - с помощью клавиши Tab. По области таблицы перемещаться можно также и с помощью мыши, нажатием левой кнопки, при этом выбранная ячейка становится активной.

  • 3. Выделите данные, по которым строится график, следующим образом: переместите курсор с помощью мыши в ячейку Л1; нажав на левую клавишу мыши обведите область, содержащую данные для построения графика, при этом область будет выделена контрастным цветом (кроме ячейки А1).
  • 4. На верхней панели инструментов выберите раздел ВСТАВКА - Рекомендуемые диаграммы и откройте его.
  • 5. Перейдите на вкладку Все диаграммы и выберите тип диаграммы: Точечная - Точечная с гладкими кривыми и маркерами, т. к. исходные данные содержат координаты точек:
  • 6. Мышью нажмите клавишу ОК, на поле листа рабочей книги будет помещена диаграмма, ее можно переместить, если она закрывает ячейки с данными.
  • 7. Оформите диаграмму: добавьте название осей, легенду, измените название диаграммы (см. раздел 9.5).
  • 8. Сохраните рабочую книгу в файл под именем, например, Cp.xls.

Пример 9.2. Выполнить аппроксимацию зависимости теплоемкости С|хН36 от температуры (см. табл. 9.1).

Последовательность выполнения операций:

  • 1. Запустите программу Excel и откройте рабочую книгу Cp.xls, созданную ранее.
  • 2. На графике (экспериментальные данные табл. 9.1) выделите ряд данных щелчком мыши на экспериментальной точке.
  • 3. На верхней панели откройте раздел КОНСТРУКТОР и подразделы - Добавить элемент диаграммы - Линия тренда - Дополнительные параметры линии тренда. На экране появится окно формата линии тренда (рис. 9.17).
Добавление линии тренда

Рис. 9.17. Добавление линии тренда

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

- показывать уравнение на диаграмме;

- поместить на диаграмму величину достоверности аппроксимации (R2).

Нажмите клавишу О К.

Примечание: если необходимо построить несколько типов линий тренда, то на верхней панели нажмите клавишу со значком Н (отменить) и укажите следующий тип линии.

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

4. По величине достоверности аппроксимации (R2) определите, какой тип линии тренда дает наибольшую точность при обработке экспериментальных данных.

В результате обработки экспериментальных данных получены следующие зависимости:

где /?" - степень достоверности аппроксимации.

Анализ полученных зависимостей показывает, что наибольшая достоверность (значение R2 близко к 1) наблюдается при использовании логарифмического уравнения и полинома 2-го порядка. Следовательно, эти уравнения можно эффективно использовать для описания экспериментальных данных.

Пример 9.3. Получить коэффициенты аппроксимирующих уравнений с использованием метода наименьших квадратов.

Для заданного набора пар значений независимой переменной (температура) и функции (теплоемкость) (табл. 9.1) определить линейное приближение в виде прямой у=ах + b и показательное приближение в видеу=6 • а

Последовательность выполнения операций:

  • 1. Запустите программу Excel и откройте рабочую книгу Cp.xls, созданную ранее.
  • 2. Сделайте ячейку С1 текущей и выберите элементы меню ФОРМУЛЫ - Вставить функцию в строке формул. В раскрывшемся окне выберите категорию Ссылки и массивы и функцию ИНДЕКС (рис. 9.18). В новом диалоговом окне выберите первый вариант набора параметров (массив: номер строки; номер столбца).
  • 4. Установите текстовый курсор в первое поле для ввода параметров в палитре формул и снова выберите пункт Другие функции в раскрывающемся списке в строке формул.
  • 5. С помощью Мастера функций выберите функцию ЛИНЕЙН категории Статистические (рис. 9.19).
  • 6. В качестве первого параметра функции ЛИНЕЙН выберите диапазон, содержащий значения функции (массив В2 : В9, столбец 6).
IS. Выбор функции ИНДЕКС

Рис. 9. IS. Выбор функции ИНДЕКС

  • 7. В качестве второго параметра функции ЛИНЕЙН выберите диапазон, содержащий значения независимой переменной (массив А2:А9, столбец А).
  • 8. Переместите текстовый курсор в строке формул так, чтобы он стоял на имени функции ИНДЕКС. В качестве второго параметра функции ИНДЕКС (номер строки) задайте число «1». Щелкните на кнопке ОК на палитре формул (рис. 9.20). Функция ЛИНЕЙН возвращает коэффициенты уравнения прямой в виде массива из двух элементов. С помощью функции ИНДЕКС выбирается нужный элемент.
Выбор функции ЛИ НЕЙ Н

Рис. 9.19. Выбор функции ЛИ НЕЙ Н

Выбор аргументов функции ИНДЕКС

Рис. 9.20. Выбор аргументов функции ИНДЕКС

9. Сделайте текущей ячейку D1. Повторите операции, описанные в пп. 3-9, чтобы в итоге в этой ячейке появилась формула:

= ИНДЕКС(ЛИНЕЙН(В2:В9; Л2:Л9);2).

Ее можно ввести и вручную (посимвольно). Теперь в ячейках С1 и D1 вычислены соответственно коэффициенты « = 0,167115 и Ь = 60,17619 уравнения прямой, наилучшим образом описывающей эксперимент (рис. 9.21).

Значения коэффициентов линейной аппроксимации

Рис. 9.21. Значения коэффициентов линейной аппроксимации

10. Активизируйте ячейку С2. Повторите операции, описанные в пп. 3-9, только вместо ЛИНЕЙН, следует выбрать функцию ЛГРФПРИБЛ или ввести вручную следующую формулу:

= ИНДЕКС(ЛГРФПРИБЛ(В2:В9:А2:А9);').

11. Активизируйте ячейку D2. Повторите операции, описанные в пп. 3-9, выбрав функцию ЛГРФПРИБЛ или введите вручную следующую формулу:

= ИНДЕКС(ЛГРФПРИБЛ(В2 : 69; А2 : А9) 2).

Теперь ячейки С2 и 02 содержат, соответственно, коэффициенты а и b уравнения наилучшего приближения показательной функции (рис. 9.22):

Примечание: для интерполяции или экстраполяции оптимальной кривой без явного определения ее параметров можно использовать функции ТЕНДЕНЦИЯ (для линейной зависимости) и РОСТ (для показательной зависимости), ссылаясь в окне Известные значения У на диапазон (Я2:?10), в окне Известные значения X на диапазон (А2:А10) и подставляя в окно Новые значения X численные значения аргумента.

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

Аппроксимация линейной и показательной функцией

Рис. 9.22. Аппроксимация линейной и показательной функцией

Пример 9.4. Решение уравнений средствами Excel

Найти решение нелинейного уравнения х3 - Зх2 + х=-1.

Последовательность выполнения операций:

  • 1. Запустите программу Excel и откройте рабочую книгу Cp.xls, созданную ранее.
  • 2. Перейдите на новый рабочий лист, дважды щелкните на его ярлычке и присвойте ему имя Уравнение.
  • 3. Поместите в ячейку 41 значение «О».
  • 4. Поместите в ячейку 61 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку 41. Соответствующая формула будет иметь вид: = СТЕПЕНЬ(А1 ;3)-3*СТЕПЕНЬ(А1 ;2)+А1.
  • 5. Выполните команду ДАННЫЕ ? Анализ «что если» ? Подбор параметра.
  • 6. В поле Установить в ячейке укажите 61, в поле Значение задайте «-1», в поле Изменяя значение ячейки укажите А1 (рис. 9.23).
  • 7. Щелкните на кнопке ОК и посмотрите на результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Щелкните на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции (рис. 9.24).
  • 8. Повторите расчет, задавая в ячейке 41 другие начальные значения, например «0,5» или «2». Проверьте, совпали ли результаты вычислений. Чем можно объяснить различия?
  • 9. Сохраните рабочую книгу Cp.xls.

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

Выполнение команды Подбор параметра

Рис. 9.23. Выполнение команды Подбор параметра

Выполнение команды Подбор параметра

Рис. 9.24. Выполнение команды Подбор параметра

Пример 9.5. Рассчитать значения констант скорости химической реакции при различных температурах, используя уравнение Аррениуса:

где к0 - предэкспоненциальный множитель п =4,802-107); Е- энергия активации, (Е= 9,305-104 ); R - газовая постоянная,

(R = 8,314 ); Т- температура, К (550, 560, 570, 580, 590, 600 К).

Последовательность выполнения операций:

  • 1. Запустите программу Excel и откройте рабочую книгу Cp.xls, созданную ранее.
  • 2. Создайте новый рабочий лист.
  • 3. Поместите в ячейку 41 обозначение «ка», а в ячейку 42 - соответствующее значение, в ячейки 81 и С1 - обозначения «?» и «/?», а в ячейки 82 и С2 - соответствующие значения, в ячейку D1 - обозначение «Г», а в ячейки 02, 03, 04, 05, 06, 07 - соответствующие значения температуры, в ячейку ?1 поместите обозначение «к » (рис. 9.25).
  • 4. Сделайте ячейку ?2 текущей и внесите знак «=»; далее мышью выполните ссылку на ячейку 42 и добавьте знак «*», с помощью Мастера функций вызовите стандартную функцию ЕХР, в окне «Число» палитры формул наберите выражение аргумента (-E/RT), с помощью ссылок на соответствующие ячейки. В строке формул появится выражение: =A2*EXP(-B2/(C2*D2)) (рис. 9.25). После щелчка на кнопке ОК в ячейке ?2 появится вычисленное при температуре 550 К значение константы скорости химической реакции - 0,070077.

Рис. 9.25

Для того чтобы выполнить расчеты по данной формуле при других значениях температуры, сделаем ссылки на ячейки, содержащие значения к0, Е и R абсолютными, т. е. введем в адреса ячеек 42, 62 и С2 в строке формул символ «5»: = $A$2*EXP(-$B$2/($C$2*D2)) и выполним расчет по отредактированной формуле. Результат останется прежним.

5. Вновь сделаем активной ячейку ?2. Поймаем мышью крестик в правом нижнем углу ячейки и протянем его вниз по ячейку ?7 включительно. В столбце ? появятся результаты (рис. 9.26).

Таким образом, вы научились с помощью программы Excel выполнять расчеты по введенной с помощью Мастера формул зависимости для диапазона данных.

Рис. 9.26

Пример 9.6. Состав газовой смеси представлен в объемных процентах (см. таблицу). Выполнить пересчет состава в массовых долях. Определить среднюю молекулярную массу смеси.

Компонент

n2

С02

СП,

с2н6

с3н8

1 ‘ О

X

о

/7-С4Н10

1-С5Н12

/7-С5Н12

Содержание, об. %

1,15

3,98

79,21

9,86

4,68

0,59

0,46

0,04

0,02

При разработке алгоритма расчета данной задачи следует помнить, что один моль идеального газа, независимо от его состава, занимает 22,4 л, поэтому объемные проценты соответствуют мольным. Для пересчета, кроме данных по составу, необходимы значения молекулярных масс компонентов. Средняя молекулярная масса смеси рассчитывается по формуле

где С, ()б - процент объемный /'-го компонента; ММ( - молекулярная масса /'-го компонента.

Пересчет состава смеси в массовые проценты выполняется по формуле

Последовательность выполнения операций:

  • 1. Запустите программу Excel (Пуск ? Программы ? Microsoft Excel) и откройте рабочую книгу Cp.xls, созданную ранее.
  • 2. Перейдите на новый рабочий лист, дважды щелкните на его ярлычке и присвойте ему имя Пересчет.
  • 3. На рабочем листе Пересчет в первой строке введите обозначения столбцов: в ячейке At - «Компоненты», в ячейке 61 - «% об», в ячейке С1 - «ММ» (молекулярная масса), в ячейке D1 - «масса» (масса компонента в 1 моле смеси), и в ячейке ?1 - «% масс».
  • 4. В диапазон 42:410 поместите обозначения компонентов, в диапазон 62:610 - соответствующие значения концентраций, в диапазон С2:С10 — соответствующие молекулярные массы.
  • 5. Для проверки правильности введения значений концентраций выделим диапазон 62:610 и щелкнем кнопкой «?» на панели инструментов. В ячейке 611 появится значение, которое должно соответствовать 100. В соседней ячейке 411 введем комментарий «Сумма».
  • 6. Сделаем активной ячейку D2. С помощью ссылок на ячейки 62 и С2 введем запись: =62*С2/100. В ячейке D2 появится рассчитанное значение. Поймаем мышью крестик в правом нижнем углу ячейки D2 и протянем его вниз по ячейку D10 включительно. В ячейках выделенного диапазона появятся соответствующие каждому компоненту рассчитанные значения массы в одном моле смеси. Щелкнем кнопкой «?» на панели инструментов, и в ячейке 011 появится сумма масс компонентов одного моля смеси, т. е. средняя молекулярная масса смеси. В соседнюю ячейку С11 введем комментарий «СрММ (см. рис. 9.27).
  • 7. Активизируем ячейку ?2. С помощью ссылок на ячейки 62, С2 и абсолютной ссылки на ячейку 011 введем запись = 62*С2/$0$11. В ячейке ?2 появится рассчитанное значение концентрации компонента N2 в массовых процентах. Поймаем мышью крестик в правом нижнем углу ячейки ?2 и протянем его вниз по ячейку ?10 включительно. В ячейках выделенного диапазона появятся соответствующие каждому компоненту рассчитанные значения концентраций в массовых процентах. Щелкнем кнопкой «X» на панели инструментов, и в ячейке ?11 появится сумма, которая должна соответствовать 100.

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

Рис. 9.27

Пример 9.7. Построить таблицу конечных разностей зависимости теплоемкости компонента С|8Н36 от температуры в диапазоне 300-1000 К для интерполирования по формулам Ньютона. Значения теплоемкостей при соответствующих температурах приведены в табл. 9.1. Результаты оформить в виде таблицы.

  • 1. Запустите программу Excel и откройте рабочую книгу Cp.xls, созданную ранее.
  • 2. Создайте новый рабочий лист, дважды щелкните на его ярлычке и присвойте ему имя Конечные разности.
  • 3. В ячейку А1 впишите: «Таблица конечных разностей для интерполирования по формулам Ньютона», в ячейку А2 - «№», в ячейку 82 - «Табличные значения», в ячейку 02 - «Конечные разности».
  • 4. Щелчком на ярлычке выберите рабочий лист Обработка эксперимента, Выделите диапазон Д1:89, скопируйте его в буфер.
  • 5. Перейдите на рабочий лист Конечные разности, щелчком мыши сделайте активной ячейку S3 и вставьте из буфера скопированные на листе Обработка эксперимента табличные данные температур и соответствующих значений теплоемкости. Данные расположатся в диапазоне 63:С11.
  • 6. Теперь рассчитаем столбец первых конечных разностей

. Для этого активизируйте ячейку D4 и, пользуясь

ссылками на адреса ячеек, введите формулу: «= С5-С4». В ячейке D4 появится соответствующее значение конечной разности. Чтобы рассчитать все конечные разности первого порядка, сделайте активной вновь ячейку D4, поймайте крестик в правом нижнем углу и протяните его вниз по ячейку D10 включительно. В выделенном диапазоне появятся рассчитанные значения первых конечных разностей.

8. Для расчета столбца вторых конечных разностей

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

  • 9. Перемещаясь на столбец вправо, повторяйте процедуру расчета конечных разностей, пока столбец не будет содержать один элемент. В результате получите треугольную таблицу значений конечных разностей (см. рис. 9.28).
  • 10. Оформим эту таблицу. Начнем с корректировки ширины столбцов. Мышью в строке обозначений столбцов переместим соответствующие границы по максимальной длине записи (правую границу столбца А - влево, а правую границу столбца С - вправо). Отформатируем название таблицы. Для этого выделите диапазон Л1:Л и нажмем кнопку «Объединить и поместить в центре» на развернутой панели меню ГЛАВНАЯ.
  • 11. Выполните аналогичные операции с диапазонами А2:АЗ, D2.J2.
  • 12. В ячейку Л4 введите «1», а в ячейку Л5 - «2». Выделите диапазон Л4: Л 5, поймайте крестик в правом нижнем углу выделенного фрагмента и протяните его вниз по ячейку А11 включительно. В результате автозаполнения строки таблицы последовательно пронумеруются.
  • 13. В ячейки D3:J3 введите обозначения АСр'+АСр1 и выровняйте по центру.
  • 14. Диапазон численных значений Л4:Л 1 также выровняйте по центру.
  • 15. Чтобы дробные числа были представлены одинаковым числом знаков после запятой, выделите диапазон C2J11, в меню ГЛАВНАЯ ? Число ? Числовой ? разрядность должна соответствовать числу десятичных знаков - 2.
  • 16. Для обрамления таблицы воспользуйтесь кнопкой «Границы» меню ГЛАВНАЯ, выбрав «Все границы».

Рабочий лист с выполненным заданием представлен на рис. 9.28.

Рис. 9.28

Таким образом, вы закрепили навыки копирования данных, автозаполнения ячеек формулами, научились оформлять таблицы в приложении Microsoft Excel.

Вопросы для самоконтроля

  • 1. Войдите в рабочую книгу Microsoft Excel.
  • 2. Сделайте активной ячейку СЗ.
  • 3. Как обозначается диапазон ячеек?
  • 4. Как ввести текст или число?
  • 5. Редактирование содержимого ячейки?
  • 6. Как изменить ширину столбца и высоту строки?
  • 7. Как выполняются вычисления по формулам?
  • 8. Как задаются ссылки на ячейку?
  • 9. Копирование содержимого ячеек?
  • 10. Как использовать стандартные функции?
  • 11. Последовательность работы с Мастером диаграмм.
 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ   ОРИГИНАЛ     След >