Файл: Практикум Для студентов вузов Кемерово 2013 4 удк 004 (076) ббк 32. 81я7 И74.pdf

---

---

Изделие 1 продолжится при автозаполнении вниз/вправо текстами Изделие 2, Изделие 3; текст Тип 1.125 продолжится: Тип 1.126 и т. п.); г) если занести в две смежные ячейки два числа, выделить их и затем, наведя указатель на маркер заполнения выделенного диапазона, произвести автозаполнение, продолжая соответст- вующую строку или столбец, создается арифметическая про- грессия с шагом, равным разности первоначально введенных чисел; д) содержимое-формула вносится в другие ячейки соглас- но следующим правилам:
– все элементы формулы, кроме ссылок на влияющие ячейки, не изменяются;
– ссылки изменяются так: при автозаполнении вниз/вверх на одну строку увеличиваются/уменьшаются на единицу номера строк в адресах; при автозаполнении слева направо на один столбец заголовки столбцов в адресах заменяются на заголовок следующего по порядку столбца.
– элементы ссылок, помеченные спереди знаком $, не из- меняются. Если так помечены оба элемента ссылки, например
$A$5
, ссылка называется абсолютной, если один ($A5, A$5) – смешанной. Для создания абсолютных и смешанных ссылок можно использовать клавиатурную клавишу . Курсор щелчком устанавливается внутрь ссылки, после чего последова- тельные нажатия превращают ссылку из относительной в абсолютную, затем в разные варианты смешанной, снова в от- носительную и т.д.;

3. Электронные таблицы Excel
100
– имена ячеек не изменяются, так что ссылка на имя ячей- ки всегда абсолютна.
При автозаполнении, производимом в вертикальном на- правлении для диапазона-строки, каждый столбец заполняется отдельно по описанным правилам. То же относится к автоза- полнению в горизонтальном направлении для диапазона- столбца.
3.7. Диаграммы
Диаграммы строятся с помощью средств, сосредоточен- ных на вкладке «Вставка» в разделе «Диаграммы». Отдельные кнопки этого раздела позволяют выбрать один из типов диа- грамм. Чаще всего используются следующие типы: а) гистограмма, в которой каждый ряд данных (последо- вательность, расположенная в диапазоне-строке или диапазоне- столбце) отображается набором столбиков, высота которых со- ответствует отображаемому значению. Столбики нумеруются по порядку. Их можно также пометить значениями, взятыми из любого другого ряда с такой же структурой. б) график, в котором те же значения отображаются точка- ми, равномерно расположенными по горизонтали и пронумеро- ванными по порядку. Вертикальная координата каждой точки соответствует отображаемому значению. Точки могут быть со- единены линией. в) круговая диаграмма, в которой каждое значение изо- бражается сектором круга, угол раствора которого пропорцио- нален доле отображаемого значения в общей сумме всех значе- ний ряда. Круговые диаграммы наиболее наглядно изображают величины, выраженные в процентах. г) точечная диаграмма. Для ее построения необходимо выделить два ряда одинаковой структуры. Каждая пара значе- ний из этих рядов рассматривается как координаты некоторой точки в обычных декартовых координатах. Далее в разных ва- риантах диаграммы эти точки могут быть соединены линией или не соединены. Точечная диаграмма – это единственный тип диаграммы, позволяющий изобразить реальное положение точек

3. Электронные таблицы Excel
101 на плоскости при построении графиков функций, отображении результатов эксперимента и т.п. д) в лепестковой диаграмме для каждого значения ряда строится луч, выходящий из некоторого центра. Угол между лучами зависит от количества лучей. Значение величины ото- бражается длиной отрезка соответствующего луча. Концы от- резков соединяются ломаной линией. е) поверхность используется при построении графиков функций двух переменных.
Прочие виды диаграмм имеют меньшее распространение.
На рис. 3.5 показаны различные виды диаграмм, постро- енных на основе диапазона A1:B12, который содержит таблицу значений функции sin(x) на отрезке [0,

]. Включение в диапа- зон ячеек с текстовыми заголовками рядов приводит к тому, что эти заголовки становятся подписями рядов в легенде (поясни- тельной надписи) диаграммы.
Видно, что на гистограмме, графике и лепестковой диа- грамме оказалось по две отдельные линии или набора столби- ков. Одна из них отображает значения ряда «х», вторая – ряда
«sin(x)». По горизонтальной оси графика и гистограммы отло- жены номера точек, поэтому вид функции sin(x) эти диаграммы отображают лишь качественно. Круговая и лепестковая диа- граммы в данном случае вообще не дают представления о пове- дении функции. Единственным видом диаграммы, на котором верно изображен график рассматриваемой функции (с правиль- ными значениями по обеим координатным осям), оказалась то- чечная диаграмма.
Поэтому для описания результатов исследований в облас- ти точных и технических наук в основном используются точеч- ные диаграммы. Остальные виды диаграмм чаще используются для отображения и анализа экономической и статистической информации.

3. Электронные таблицы Excel
102
Рис. 3.5. Виды диаграмм
Для построения диаграммы прежде всего надо выделить в таблице данные, на основании которых будет построена диа- грамма. Затем следует мышью выбрать в разделе «Вставка» –
«Диаграммы» тип диаграммы, после чего в раскрывшемся спи- ске – разновидность диаграммы выбранного типа. После щелчка по кнопке с изображением выбранной разновидности на листе сразу появляется диаграмма, параметры оформления которой назначены Excel по умолчанию.
Область диаграммы (окно, занятое диаграммой) содержит следующие элементы:

область построения, в которой строится диаграмма;

легенду, в которой находятся пояснения к диаграмме; легенда нужна, если в одной области на одной координатной сетке построено несколько диаграмм для разных рядов данных;

название – текстовый заголовок диаграммы.
В области построения находятся:

координатные оси с разметкой;

названия координатных осей;

линии координатной сетки, связанные с соответствую- щей осью;

графические отображения рядов данных – точками, ли- ниями, столбиками и т.п.

3. Электронные таблицы Excel
103
Добавление элементов на диаграмму или удаление эле- ментов производится командами контекстной вкладки «Работа с диаграммами» – «Макет».
Любой элемент построенной диаграммы (координатную ось, линию графика, область построения и др.) можно выделить щелчком по нему. Затем правым щелчком можно вызвать кон- текстное меню элемента (рис. 3.6).
Рис. 3.6. Контекстное меню ряда данных
Пункт меню «Изменить тип диаграммы» позволяет для выбранного ряда заменить, например, гистограмму на график и совместить на одной координатной сетке разные виды диа- грамм.
Выбор команды «Выбрать данные» вызывает на экран ок- но диалога (рис. 3.7). В этом окне можно: а) уточнить диапазон, по которому построена диаграмма; б) поменять способ выбора рядов данных – в строках или столбцах; в) добавить на диаграмму новый ряд или удалить один из имеющихся.

3. Электронные таблицы Excel
104
Рис. 3.7. Диалоговое окно выбора данных
Выбор пункта «Формат…» вызывает на экран окно диало- га, с помощью которого можно выбрать параметры форматиро- вания соответствующего элемента диаграммы – цвет, тип и толщину линий (осей координат, координатной сетки, графиков, рамок…), параметры шрифта (для легенды или разметки осей координат), формат представления чисел, диапазон значений и цену деления разметки координатных осей, цвет заливки от- дельных областей и т.п.
3.8. Общие требования к выполнению
лабораторных работ
Все лабораторные работы в электронных таблицах Excel выполняются в одной книге, в которую при необходимости до- бавляются дополнительные листы. Книга сохраняется в личной папке студента, которая должна находиться в папке «Мои доку- менты».
Каждая работа (или ее отдельная часть) выполняется на отдельном листе. Имена листов должны отражать содержание работ.
Каждый лист, содержащий какие-то расчетные формулы, должен дублироваться в книге дважды. На одном листе в ячей- ках должны быть показаны значения, на другом – содержания ячеек (формулы, см. § 3.2). Если таблицу требуется отформати- ровать, это делается для таблицы с показом значений; таблицы с формулами не форматируются.

3. Электронные таблицы Excel
105
Для общего оформления ячеек должен быть создан стиль
«Студенческий» с параметрами:

число: формат «Числовой», 3 десятичные цифры (знака после запятой);

выравнивание: по центру (по горизонтали и вертикали),
«переносить по словам», направление текста – горизонтальное;

шрифт: Times New Roman, черный, кегль 11, начерта- ние обычное;

границы: внешние – тонкими черными сплошными ли- ниями;

заливка: серая, желтая или голубая, светлая.
Заголовки таблиц, занимающие визуально несколько столбцов, должны создаваться путем выделения диапазонов и выравнивания по центру выделения (без объединения ячеек).

3. Электронные таблицы Excel
106
Лабораторная работа № 1
Табулирование функций
Цель работы: получение первичных навыков создания и оформления таблиц, автоматизации вычислений, построения и форматирования диаграмм.
Задания для самостоятельной работы
1. Изучить материал § 3.1–3.7.
2. Создать для оформления ячеек стиль «Студенческий».
3. Составить таблицу значений простой алгебраической функции одной переменной согласно индивидуальному вариан- ту (табл. 3.2). Построить и отформатировать диаграмму типа
«Точечная», отображающую график функции. Параметры фор- матирования диаграммы выбрать из табл. 3.3 согласно индиви- дуальному варианту.
4. Выполнить такие же действия для кусочной алгебраи- ческой функции одной переменной, согласно индивидуальному варианту (табл. 3.4). Таблицу и диаграмму отформатировать так же, как при табулировании простой функции. Для повторения параметров форматирования диаграммы использовать специ- альную вставку.
5. Построить таблицу значений и диаграмму типа «По- верхность» для функции двух переменных согласно индивиду- альному варианту задания (табл. 3.5).
Порядок выполнения работы
Табулирование и построение графика простой функции
Ход работы рассматривается на примере табулирования функции у =e
–x/2
sin(х) на отрезке [a, b], где a= –3,5, b=3,5. Шаг аргумента h=0,5.
1. В ячейку А1 ввести текст Исходные, в ячейку А2 –
Данные. Выделить диапазон А1:В2, войти в окно форматиро- вания (рис. 3.2) и установить выравнивание по горизонтали –
«По центру выделения».

3. Электронные таблицы Excel
107 2. Заполнить ячейки с исходными данными:
А3: "а="
B3:
–3,5
А4: "b="
B4:
3,5
A5: "h="
B5:
0,5
3. В ячейку С1 ввести текст Значения функции. Выде- лить диапазон С1:D1 и установить выравнивание по горизонта- ли – «По центру выделения».
4. Запустить графический редактор формул «Microsoft
Equation» («Вставка» – «Текст» – [Объект] – «Microsoft
Equation»). Создать формулу табулируемой функции. Мышью передвинуть ее в строку 2 (столбцы C, D) и отрегулировать раз- мер изображения либо высоту строки 2, чтобы формула поме- щалась в строке.
5. В ячейку C3 ввести текст x, в ячейку D3 – y.
6. В ячейку C4 ввести формулу =B3.
7. В ячейку C5 ввести формулу =C4+$B$5.
8. Выделить ячейку С5. Навести мышь на нижний правый угол выделенной ячейки, нажать левую кнопку и вести мышь вниз до ячейки С18. За счет копирования формулы создается равномерная последовательность чисел – арифметическая про- грессия с шагом h.
9. В ячейку D4 ввести формулу =exp(–C4/2)*sin(C4) (об- ратите внимание, как записан множитель
e
–x/2
!).
10. Выделить ячейку D4. Навести мышь на нижний пра- вый угол ячейки, нажать левую кнопку и, ведя мышь вниз, про- извести автозаполнение ячеек столбца D, вплоть до ячейкиD18, формулами вычисления функции.
11. Выделить диапазон ячеек, содержащих заголовок таб- лицы (C1:D2). Вызвать диалоговое окно «Формат ячеек»
(рис. 3.2), и средствами вкладки «Граница» создать для диапа- зона внешние границы сплошными линиями шириной 0,75 пт и заливку того же цвета, что и у стиля «Студенческий».
12. Выделить диапазон значений аргумента хи функции у, включая ячейки с заголовками столбцов (C3:D18). Назначить выделенным ячейкам стиль «Студенческий».
13. Так как все значения аргумента х в ячейках C4:С18 имеют только одну значащую цифру после запятой, изменить формат чисел в этих ячейках на «Числовой» с одной десятичной цифрой.

3. Электронные таблицы Excel
108 14. Выделить диапазон C3:D18. Выполнить команду
«Вставка» – «Диаграммы» – [Точечная] – выбрать вариант
«Точечная с гладкими кривыми и маркерами».
Результат описанных действий показан на рис. 3.8.
Рис. 3.8. Табулирование простой функции
15. Диаграмма, отформатированная по умолчанию, выгля- дит не слишком опрятно, поэтому ее требуется дополнительно отформатировать. Например, можно сделать так:

область диаграммы (сначала правым щелчком по об- ласти вызвать контекстное меню и выбрать: «Формат области диаграммы» для вызова окна диалога): залить светло-голубым
(«Заливка» – «Сплошная заливка» – открыть список цветов и выбрать светло-голубой), обвести по границе черной («Цвет границы» – «Сплошная линия» – выбрать на палитре черный цвет) толстой («Стили границ» – «Ширина» – установить шири- ну 2 пт) линией;

область построения: заливка светло-желтым, граница тонкой (0,75 пт) черной линией;

вертикальная ось: на вкладке «Число» установить фор- мат чисел в подписях: «Числовой» с одним десятичным знаком; на вкладке «Параметры оси» – фиксированную цену основных делений 1, промежуточных 0,5, вывод основных и промежуточ- ных меток – «наружу»; «горизонтальная ось пересекает значе- ние оси»: 0,0;

3. Электронные таблицы Excel
109

горизонтальная ось: установить параметры аналогично вертикальной оси;

линии сетки (вертикальные линии выносятся на диа- грамму одной из команд контекстного меню горизонтальной оси): тонкие пунктирные («Тип линии» – «Тип штриха»), ко- ричневого цвета («Цвет линии»);

легенда: удалить (командой контекстного меню или вкладки «Работа с диаграммами» – «Макет»);

линия диаграммы: сплошная толщиной 2 пт («Тип ли- нии») темно-синяя («Цвет линии»), маркеры круглые размером
8 пт («Параметры маркера») с синей заливкой («Заливка маркера») и красной границей («Цвет линии маркера»);

название диаграммы удалить. Средствами вкладки
«Макет» добавить названия осей – горизонтальной «под осью», вертикальной – «горизонтальное». Мышью передвинуть рамки названий к концам осей, заменить тексты названий на «х» и «у»
(шрифт Times New Roman, кегль 14).
В результате диаграмма примет вид, показанный на рис. 3.9.
16. Выделить диапазон, содержащий таблицу, и скопиро- вать его в буфер обмена. Перейти на Лист 2 книги, активизиро- вать ячейку А1 и вставить из буфера скопированную таблицу.
Щелкнуть клавишу «Формулы» – «Зависимости формул» [Пока- зать формулы]. Результат показан на рис. 3.10. Рассмотреть тек- сты формул и оценить, как изменяются при автозаполнении аб- солютные и относительные ссылки.
Рис. 3.9. Диаграмма после форматирования

3. Электронные таблицы Excel
110 17. Присвоить листу с таблицей в режиме чисел и диа- граммой имя «Табулирование 1-а», листу с показом формул –
«Табулирование 1-б».
Рис. 3.10. Таблица в режиме показа формул
Табулирование и построение графика кусочной функции
Кусочной называется функция, которая на разных участ- ках числовой оси задается разными выражениями, например






2
sin
0 0
1 1 ln
1
x
x
y
x
x
x
x




 

 


(3.1)
Для вычисления такой функции необходимо провести в ячейке таблицы разветвляющийся вычислительный процесс, вычисляя разные выражения в зависимости от значения x, нахо- дящегося во влияющей ячейке. В EXCEL для этой цели исполь- зуется логическая функция ЕСЛИ(…) (см. § 3.5).
В рассматриваемом примере числовая ось значений аргу- мента х разбита на три области:
А)
(-

; 0)
В)
[0 ; 1]
С)
(1 ;

).
Поставив условие x < 0,мы выделяем область «А»: х мо- жет либо принадлежать этой области (и тогда значение функции вычисляется с помощью первого из приведенных выражений),

3. Электронные таблицы Excel
111 либо не принадлежать ей. Этому соответствует формула (пред- полагается, что, как и при табулировании простой функции, первое из значений аргумента х находится в ячейке C4)
=ЕСЛИ( C4<0; sin(C4); …)
(3.2)
Если х не принадлежит области «А» (в формуле (3.2) этот вариант обозначен многоточием), значение х обязано принадлежать одной из двух оставшихся областей: «В» или «С».
Область «В» выделяется условием х

1, причем проверять, что
x

0уже не нужно, т.к. за пределами области «А» это условие выполняется автоматически. Если условие х

1 выполняется, значение функции вычисляется с помощью второго из приведенных выражений. Если и это условие не выполняется, значение х может принадлежать только области «С», поэтому проверять дополнительно принадлежность значения хк этой области не требуется, а функция вычисляется с помощью третьего выражения. На месте многоточия в (3.2) должна стоять вторая функция ЕСЛИ(…), обеспечивающая выбор между областями «В» и «С»:

1   2   3   4   5   6   7   8   9   10   ...   19

---

=ЕСЛИ
(
С4<0; sin(С4); ЕСЛИ
(
С4

1;
С4^2; 1+LN(С4)
)
)
Для наглядности здесь вложенная функция ЕСЛИ() под- черкнута, а парные скобки выделены различными размерами.
Для создания формулы с использованием функции
ЕСЛИ(…) удобно воспользоваться Мастером функций. Окно ввода аргументов функции показано на рис. 3.11.
На рисунке изображен ввод аргументов вложенной функ- ции ЕСЛИ(…). Об этом можно судить, в частности, по тому, что в строке формул эта функция выделена более жирным шрифтом.
В остальном процесс табулирования кусочной функции ничем не отличается от табулирования простой функции и вы- полняется в той же последовательности, за исключением шага
15 (форматирование диаграммы). Поскольку параметры форма- тирования диаграммы кусочной функции по заданию должны совпадать с параметрами форматирования диаграммы простой функции, можно быстро перенести весь комплекс параметров форматирования с одной диаграммы на другую. Для этого ис-

---

---