Тема: Обработка данных средствами электронных таблиц.

ПРАКТИЧЕСКОЕ ЗАНЯТИЕ № 6

Обработка данных

1. Запустите программу Excel (Пуск ► Все программы ► Microsoft Office ►

Microsoft Excel 2010).

2. Дважды щелкните на ярлычке текущего рабочего листа и дайте этому рабо-

чему листу имя Данные.

Дайте команду Файл ► Сохранить и сохраните рабочую книгу под именем

book.xlsx (формат Книга Excel).

3. Сделайте текущей ячейку А1 и введите в нее заголовок Результаты измере-

ний.

4. Введите произвольные числа в последовательные ячейки столбца А, начи-

ная с ячейки А2.

5. Введите в ячейку В1 строку Удвоенное значение.

6. Введите в ячейку С1 строку Квадрат значения.

7. Введите в ячейку D1 строку Квадрат следующего числа.

8. Введите в ячейку B2 формулу =2*A2.

9. Введите в ячейку С2 формулу =A2*A2.

10. Введите в ячейку D2 формулу =B2+C2+1.

11. Выделите протягиванием ячейки B2, C2 и D2.

12. Наведите указатель мыши на маркер заполнения в правом нижнем углу

рамки, охватывающей выделенный диапазон. Нажмите левую кнопку мыши и пе-

ретащите этот маркер, чтобы рамка охватила столько строк в столбцах B, C и D,

сколько имеется чисел в столбце A.

13. Убедитесь, что формулы автоматически модифицируются так, чтобы ра-

ботать со значением ячейки в столбце А текущей строки.

14. Измените одно из значений в столбце А и убедитесь, что соответствующие

значения в столбцах B, C и D в этой же строке были автоматически пересчитаны.

15. Введите в ячейку Е1 строку Масштабный множитель.

16. Введите в ячейку Е2 число 5.

17. Введите в ячейку F1 строку Масштабирование.

18. Введите в ячейку F2 формулу =А2*Е2.

19. Используйте метод автозаполнения, чтобы скопировать эту формулу в

ячейки столбца F, соответствующие заполненным ячейкам столбца А.

20. Убедитесь, что результат масштабирования оказался неверным. Это связа-

но с тем, что адрес E2 в формуле задан относительной ссылкой.

21. Щелкните на ячейке F2, затем в строке формул. Установите текстовый

курсор на ссылку Е2 и нажмите клавишу F4. Убедитесь, что формула теперь выгля-

дит как =А2*$Е$2, и нажмите клавишу ENTER.

22. Повторите заполнение столбца F формулой из ячейки F2.

23. Убедитесь, что благодаря использованию абсолютной адресации значения

ячеек столбца F теперь вычисляются правильно. Сохраните рабочую книгу

book.xlsx.

Это первый документ↑

Применение итоговых функций

1. Запустите программу Excel (Пуск ► Все программы ► Microsoft Office ►

Microsoft Excel 2010) и откройте рабочую книгу book.xlsx, созданную ранее.

2. Выберите рабочий лист Данные.

3. Сделайте текущей первую свободную ячейку в столбце A.

4. Щелкните на кнопке Главная ► Редактирование ► Сумма.

5. Убедитесь, что программа автоматически подставила в формулу функцию

СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу

ENTER.

6. Сделайте текущей следующую свободную ячейку в столбце A.

7. Щелкните на кнопке Вставить функцию в строке формул.

8. В раскрывающемся списке Категория выберите пункт Статистические.

9. В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке ОК.

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

заслоняет нужные ячейки. Обратите внимание, что автоматически выбранный диа-

пазон включает все ячейки с числовым содержимым, включая и ту, которая содер-

жит сумму. Выделите правильный диапазон методом протягивания и нажмите кла-

вишу ENTER.

11. Используя порядок действий, описанный в пп. 6-10, вычислите минималь-

ное число в заданном наборе (функция МИН), максимальное число (МАКС), количе-

ство элементов в наборе (СЧЕТ).

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

Это второй документ ↑

Подготовка и форматирование прайс-листа

1. Запустите программу Excel (Пуск ► Все программы ► Microsoft Office ►

Microsoft Excel 2007) и откройте рабочую книгу book.xlsx.

2. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте

новый (кнопка Вставить лист в правой части панели ярлыков листов). Дважды

щелкните на ярлычке нового листа и переименуйте его как Прейскурант.

3. В ячейку А1 введите текст Прейскурант и нажмите клавишу ENTER.

4. В ячейку А2 введите текст Курс пересчета: и нажмите клавишу ENTER. В

ячейку B2 введите текст 1 у.е.= и нажмите клавишу ENTER. В ячейку С2 введите те-

кущий курс пересчета и нажмите клавишу ENTER.

5. В ячейку А3 введите текст Наименование товара и нажмите клавишу

ENTER. В ячейку B3 введите текст Цена (у.е.) и нажмите клавишу ENTER. В ячейку

С3 введите текст Цена (руб.) и нажмите клавишу ENTER.

6. В последующие ячейки столбца A введите названия товаров (не менее 5 на-

именований), включенных в прейскурант.

7. В соответствующие ячейки столбца B введите цены товаров в условных

единицах.

8. В ячейку С4 введите формулу: =B4*$C$2, которая используется для пересче-

та цены из условных единиц в рубли.

9. Методом автозаполнения скопируйте формулы во все ячейки столбца С, ко-

торым соответствуют заполненные ячейки столбцов A и B.

10. Измените курс пересчета в ячейке С2. Обратите внимание, что все цены в

рублях при этом обновляются автоматически.

11. Выделите методом протягивания диапазон A1:C1 и дайте команду контек-

стного меню Формат ячеек. На вкладке Выравнивание задайте выравнивание по

горизонтали По центру и установите флажок Объединение ячеек.

12. На вкладке Шрифт задайте размер шрифта равный 14 пунктам и в списке

Начертание выберите вариант Полужирный. Щелкните на кнопке ОК.

13. Щелкните правой кнопкой мыши на ячейке B2 и выберите в контекстном

меню команду Формат ячеек. Задайте выравнивание по горизонтали По правому

краю и щелкните на кнопке ОК.

14. Щелкните правой кнопкой мыши на ячейке С2 и выберите в контекстном

меню команду Формат ячеек. Задайте выравнивание по горизонтали По левому

краю и щелкните на кнопке ОК.

15. Выделите методом протягивания диапазон B2:C2. Щелкните на раскры-

вающейся кнопке рядом с кнопкой Главная ► Шрифт ► Границы и задайте для

этих ячеек толстую внешнюю границу.

16. Дважды щелкните на границе между заголовками столбцов A и B, B и C, C

и D. Обратите внимание, как при этом изменяется ширина столбцов A, B и C.

17. Посмотрите, устраивает ли вас полученный формат таблицы. Выполните

команду Файл ► Печать, чтобы увидеть, как документ будет выглядеть при печати.

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

Это третий документ ↑

Построение экспериментального графика

1. Запустите программу Excel (Пуск ► Все программы ► Microsoft Office ►

Microsoft Excel 2010) и откройте рабочую книгу book.xlsx, созданную ранее.

2. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте

новый. Дважды щелкните на ярлычке листа и переименуйте его как Обработка экс-

перимента.

3. В столбец A, начиная с ячейки A1 по ячейку A20, введите произвольный на-

бор значений независимой переменной.

4. В столбец B, начиная с ячейки B1 по ячейку B20, введите произвольный на-

бор значений функции.

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

6. Выполните команду Вставка ► Диаграммы ► Точечная ► Точечная с глад-

кими кривыми и маркерами.

7. Стандартная диаграмма выбранного типа будет вставлена на рабочий лист.

Через контекстное меню диаграммы вызовите окно Выбрать данные. В поле Ряд

выберите Ряд 1 и нажмите на кнопку Изменить. В появившемся окне, в поле Имя,

укажите Результаты измерений. Закройте открытые окна нажатиями на кнопки ОК.

8. Убедитесь, что заданное название ряда данных автоматически использовано

как заголовок диаграммы. Выделите заголовок диаграммы и, убедившись, что он

находится в режиме редактирования, замените его, введя заголовок Эксперимен-

тальные точки.

9. Выполните команду контекстного меню диаграммы Переместить диаграм-

му. В появившемся окне Перемещение диаграммы выберите На отдельном листе,

введите имя для листа Диаграмма и нажмите ОК. Убедитесь, что диаграмма пере-

мещена на отдельный лист.

10. Щелкните на построенной кривой, чтобы выделить ряд данных. Дайте ко-

манду контекстного меню ряда Формат ряда данных.

11. Откройте вкладку Линия, установите параметр Сплошная линия, откройте

палитру Цвет и выберите красный цвет.

12. Откройте вкладку Тип линии, в списке Тип штриха выберите пунктир.

13. На вкладке Параметры маркера установите параметр Встроенный, выбе-

рите в списке Тип треугольный маркер.

14. Откройте вкладку Заливка маркера, установите параметр Сплошная за-

ливка, в палитре Цвет выберите зеленый.

15. Щелкните на кнопке ОК, снимите выделение с ряда данных и посмотрите,

как изменился вид графика.

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

Анализ данных с использованием метода наименьших квадратов

Задача. Для заданного набора пар значений независимой переменной и функ-

ции определить наилучшие линейное приближение в виде прямой с уравнением

y = ax + b и показательное приближение в виде линии с уравнением x y = ba.

Это 4й документ ↑

1. Запустите программу Excel (Пуск ► Все программы ► Microsoft Office ►

Microsoft Excel 2010) и откройте рабочую книгу book.xlsx, созданную ранее.

2. Щелчком на ярлычке выберите рабочий лист Обработка эксперимента.

3. Сделайте ячейку С1 текущей и щелкните на кнопке Вставить функцию в

строке формул.

4. В окне мастера функций выберите категорию Ссылки и массивы и функцию

ИНДЕКС. В новом диалоговом окне выберите первый вариант набора параметров.

5. Установите текстовый курсор в первое поле для ввода параметров в окне

Аргументы функции и выберите в раскрывающемся списке в строке формул пункт

Другие функции.

6. С помощью мастера функций выберите функцию ЛИНЕЙН категории Стати-

стические.

7. В качестве первого параметра функции ЛИНЕЙН выберите диапазон, содер-

жащий значения функции (столбец В).

8. В качестве второго параметра функции ЛИНЕЙН выберите диапазон, содер-

жащий значения независимой переменной (столбец A).

9. Переместите текстовый курсор в строке формул, чтобы он стоял на имени

функции ИНДЕКС. В качестве второго параметра функции ИНДЕКС задайте число 1.

Щелкните на кнопке ОК в окне Аргументы функции.

10. Сделайте текущей ячейку D1. Повторите операции, описанные в пп. 3-9,

чтобы в итоге в этой ячейке появилась формула: =ИНДЕКС(ЛИНЕЙН(B1:B20;A1

:A20);2). Ее можно ввести и вручную (посимвольно). Теперь в ячейках С1 и D1 вы-

числены соответственно коэффициенты a и b уравнения наилучшей прямой.

11. Сделайте текущей ячейку С2. Повторите операции, описанные в пп. 3-9,

или введите вручную следующую формулу:

=ИНДЕКС(ЛГРФПРИБЛ(B1:B20;A1:A20);1)

12. Сделайте текущей ячейку D2. Повторите операции, описанные в пп. 3-9,

или введите вручную следующую формулу:

=ИНДЕКС(ЛГРФПРИБЛ(B1:B20;A1:A20);2)

Теперь ячейки С2 и D2 содержат соответственно коэффициенты a и b урав-

нения наилучшего показательного приближения.

13. Для построения наилучшей прямой другим способом дайте команду Дан-

ные ► Анализ ► Анализ данных.

14. Откроется одноименное диалоговое окно. В списке Инструменты анализа

выберите пункт Регрессия, после чего щелкните на кнопке ОК.

15. В поле Входной интервал Y укажите методом протягивания диапазон, со-

держащий значения функции (столбец В).

16. В поле Входной интервал X укажите методом протягивания диапазон, со-

держащий значения независимой переменной (столбец A).

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

Результат расчета.

18. Щелкните на кнопке ОК и по окончании расчета откройте рабочий лист

Результат расчета. Убедитесь, что вычисленные коэффициенты (см. ячейки B17 и

B18) совпали с полученными первым методом.

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

Это 5й документ ↑

Табулирование формул, построение графиков функций

Задача. Построить графики функций, коэффициенты которых определены в

предыдущем упражнении.

1. Запустите программу Excel (Пуск ► Все программы ► Microsoft Office ►

Microsoft Excel 2010) и откройте рабочую книгу book.xlsx.

2. Выберите щелчком на ярлычке рабочий лист Обработка эксперимента.

3. Так как программа Excel не позволяет непосредственно строить графики

функций, заданных формулами, необходимо сначала табулировать формулу, то

есть создать таблицу значений функций для заданных значений переменной.

4. В ячейку G1 введите формулу =A1*$C$1+$D$1. Здесь абсолютные ссылки на

ячейки C1 и D1 ведут к вычисленным методом наименьших квадратов коэффици-

ентам уравнения прямой.

5. В ячейку H1 введите формулу =$D$2*$C$2^A1 для вычисления значения по-

казательной функции.

6. Выделите ячейки G1 и H1 и путем протягивания заполните их формулами.

Убедитесь, что при автозаполнении ячеек изменяется лишь ссылка на ячейку

столбца A, который содержит независимую переменную функции.

7. Переключитесь на рабочий лист Диаграмма.

8. Из контекстного меню диаграммы выберите пункт Выбрать данные.

9. В группе Элементы легенды ряды и щелкните на кнопке Добавить. В поле

Имя укажите: Наилучшая прямая. В поле Значения Х укажите диапазон ячеек с

данными в столбце A, а в поле Значения Y укажите диапазон ячеек в столбце G.

10. Еще раз щелкните на кнопке Добавить. В поле Имя укажите: Показатель-

ная функция. В поле Значения Х укажите диапазон ячеек с данными в столбце A, а в

поле Значения Y укажите диапазон ячеек в столбце H.

11. Щелкните на кнопке OK, убедитесь в том, что диаграмма перестроена в со-

ответствии с новыми настройками.

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

Это 6й документ↑

Решение уравнений средствами программы Excel

Задача. Найти решение уравнения x 3 -3 x 2 + x = -1.

1. Запустите программу Excel (Пуск ► Все программы ► Microsoft Office ►

Microsoft Excel 2010) и откройте рабочую книгу book.xlsx, созданную ранее.

2. Создайте новый рабочий лист, дважды щелкните на его ярлычке и присвой-

те ему имя Уравнение.

3. Занесите в ячейку А1 значение 0.

4. Занесите в ячейку B1 левую часть уравнения, используя в качестве незави-

симой переменной ссылку на ячейку А1. Соответствующая формула может, напри-

мер, иметь вид =A1^3-3*A1^2+A1.

5. Дайте команду Данные ► Анализ ► Поиск решения.

6. В поле Оптимизировать целевую функцию укажите B1, установите пере-

ключатель До значения и задайте значение -1, в поле Изменяя ячейки переменных

укажите А1.

7. Щелкните на кнопке Найти решение и посмотрите на результат поиска, ко-

торый отобразится на рабочем листе. Установите переключатель Сохранить най-

денное решение и щелкните на кнопке ОК, чтобы сохранить полученные значения

ячеек, участвовавших в операции.

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

Решение задач оптимизации

Задача. Завод производит электронные приборы трех видов (прибор A, прибор

B и прибор С), используя при сборке микросхемы трех типов (тип 1, тип 2 и тип 3).

Расход микросхем задается следующей таблицей:

Прибор A Прибор B Прибор C

Тип1 2 5 1

Тип 2 2 0 4

Тип3 2 1 1

Стоимость изготовленных приборов одинакова.

Ежедневно на склад завода поступает 400 микросхем типа 1 и по 500 микро-

схем типов 2 и 3. Каково оптимальное соотношение дневного производства прибо-

ров различного типа, если производственные мощности завода позволяют исполь-

зовать запас поступивших микросхем полностью?

1. Запустите программу Excel (Пуск ► Все программы ► Microsoft Office ►

Microsoft Excel 2010) и откройте рабочую книгу book.xlsx, созданную ранее.

2. Создайте новый рабочий лист, дважды щелкните на его ярлычке и присвой-

те ему имя Организация производства.

3. В ячейки A2, A3 и A4 занесите дневной запас комплектующих - числа 400,

500 и 500 соответственно.

4. В ячейки C1, D1 и E1 занесите нули - в дальнейшем значения этих ячеек бу-

дут подобраны автоматически.

Стоимость изготовленных приборов одинакова.

Ежедневно на склад завода поступает 400 микросхем типа 1 и по 500 микро-

схем типов 2 и 3. Каково оптимальное соотношение дневного производства прибо-

ров различного типа, если производственные мощности завода позволяют исполь-

зовать запас поступивших микросхем полностью?

1. Запустите программу Excel (Пуск ► Все программы ► Microsoft Office ►

Microsoft Excel 2010) и откройте рабочую книгу book.xlsx, созданную ранее.

2. Создайте новый рабочий лист, дважды щелкните на его ярлычке и присвой-

те ему имя Организация производства.

3. В ячейки A2, A3 и A4 занесите дневной запас комплектующих - числа 400,

500 и 500 соответственно.

4. В ячейки C1, D1 и E1 занесите нули - в дальнейшем значения этих ячеек бу-

дут подобраны автоматически.

5. В ячейках диапазона С2:E4 разместите таблицу расхода комплектующих.

6. В ячейках B2:B4 нужно указать формулы для расчета расхода комплектую-

щих по типам. В ячейке B2 формула будет иметь вид =$C$1*C2+$D$1*D2+$E$1*E2,

а остальные формулы можно получить методом автозаполнения.

7. В ячейку F1 занесите формулу, вычисляющую общее число произведенных

приборов: для этого выделите диапазон С1:Е1 и щелкните на кнопке Автосумма на

стандартной панели инструментов.

8. Дайте команду Данные ► Анализ ► Поиск решения - откроется диалоговое

окно Параметры поиска решения.

9. В поле Оптимизировать целевую функцию укажите ячейку, содержащую

оптимизируемое значение (F1). Установите переключатель Максимум (требуется

максимальный объем производства).

10. В поле Изменяя ячейки переменных задайте диапазон подбираемых пара-

метров - C1:E1.

11. Чтобы определить набор ограничений, щелкните на кнопке Добавить. В

диалоговом окне Добавление ограничения в поле Ссылка на ячейку укажите диа-

пазон B2:B4. В качестве условия задайте <=. В поле Ограничение задайте диапазон

A2:A4. Это условие указывает, что дневной расход комплектующих не должен пре-

восходить запасов. Щелкните на кнопке ОК.

12. Снова щелкните на кнопке Добавить. В поле Ссылка на ячейку укажите

диапазон С1:Е1. В качестве условия задайте >=. В поле Ограничение задайте число

0. Это условие указывает, что число производимых приборов неотрицательно.

Щелкните на кнопке ОК.

13. Снова щелкните на кнопке Добавить. В поле Ссылка на ячейку укажите

диапазон С1:Е1. В качестве условия выберите пункт цел. Это условие не позволяет

производить доли приборов. Щелкните на кнопке ОК.

14. Щелкните на кнопке Найти решение. По завершении оптимизации откро-

ется диалоговое окно Результаты поиска решения.

15. Установите переключатель Сохранить найденное решение, после чего

щелкните на кнопке ОК.

16. Проанализируйте полученное решение. Кажется ли оно очевидным? Про-

верьте его оптимальность, экспериментируя со значениями ячеек C1:E1. Чтобы вос-

становить оптимальные значения, можно в любой момент повторить операцию по-

иска решения.

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

Седьмой документ ↑

Не нашли, что искали? Воспользуйтесь поиском: