Информатика лабораторная работа 2 1 курс. Лабораторные работы по информатике

Лабораторная работа №1

Цель работы: научиться работать с электронными таблицами и научиться строить различные диаграммы.

Краткие теоретические сведения

Excelявляется программой для расчетов с помощью электронных таблиц.

Интерфейс окна приложения Excelаналогичен интерфейсу окна приложенияWord(строка заголовка, строка меню, панели инструментов, строка состояния). Но добавляется строка формул, которой нет вWord.

Различают два вида отображения документа Excel– «обычный» и «разметка страницы», которые можно установить в меню Вид.

Параметры страницы настраиваются в меню Файл/Параметры страницы. Здесь же можно установить Верхний и Нижний колонтитулы на странице. В верхнем колонтитуле можно указать, например, номер группы, в нижнем – ФИО студента. Во вкладке «Лист» можно настроить последовательность вывода страниц.

Рабочая книга . ДокументExcelпредставляет собойрабочую книгу , состоящую из совокупностирабочих листов , сохраняемых на диске ведином файле . По умолчанию в книге 3 листа. Это количество можно изменить (до 255) в меню Сервис/Параметры/вкладка Общие. Также в книгу в любое время можно добавить или удалить листы (через контекстное меню с пом. правой кнопки). Листы в книге можно склеить (кн.Shift+щелчки на тех листах, которые надо склеить). Информация, записанная на склеенных листах, одинакова. Например, если надо создать один и тот же шаблон таблицы на нескольких листах, их надо склеить, один раз создать таблицу, затем листы «разгруппировать» через контекстное меню. На всех листах, которые были склеены, будет одна и та же таблица.

Кроме рабочих листов, в рабочей книге можно хранить диаграммы, построенные на основе данных одной или нескольких таблиц, и макросы. Макрос – это программа на языке VisualBasic, обрабатывающая данные таблицы.

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

Рабочий лист. Состоит изэлектронных ячеек , имеющих адрес: А1, В10 и т.д. Адрес текущей ячейки отображается в поле имени (крайнее левое поле строки формул). Рабочие листы содержат 256 столбцов и 65536 строк. Заголовки столбцов –A…Z,AA…AZ,BA…BZ. Заголовки строк: 1 до 65536.

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

Существует два представления данных ячейки:внутримашинное и экранное . Внутримашинное используется для вычислений, это внутренние значения ячеек, а не отображаемые на экране. Экранное представление определяется форматом ячейки.

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

Числа. Числа хранятся в машине с наибольшей точностью. Экранное представление числа определяется форматом: Формат/ Ячейки/ Число/ Числовые форматы. Можно вводить целые числа, десятичные дроби или числа в степенной (экспоненциальной) форме. Если ячейка заполняется знаками(диез), это значит, что вводимое число превышает ширину столбца.

Текст . Это любой введенный набор символов, которыеExcelне воспринимает как число, дату и время, логическое значение или ошибочное значение. В ячейку можно ввести до 255 символов текста. Чтобы ввести в формулу числа как текст, надо заключить его в кавычки. =”45,00”.

Форматирование текста: Формат/Ячейки/вкладки Выравнивание, Шрифт, Граница, Вид.

Дата и время .Дата представлена в машине в виде числа, определяемого количеством дней от системной даты (1900) до представленной в

ячейке. Это можно увидеть, если в ячейке с датой выбрать формат «Общий». Дата 22.01.2005 эквивалентна числу 38374 дней от 01.01.1900 , а дата 07.01.2005 – числу38359 дней от 01.01.1900. Поэтому над датами можно производить операции сложения и вычитания (в ячейках с датой «15.01.1900» и с числом «15» находится формула =A1-B1, вычисляющая количество дней между датами «22.01.2005-07.01.2005». Разница равна 15). Время представлено в машине в виде дроби. Это также можно увидеть, если в ячейке со временем выбрать формат «Общий». Время 16:14 эквивалентно дроби 0,6763889.

Экранное представление даты и времени определяется также в меню Формат/Ячейки/Число/Числовые форматы. Чтобы быстро ввести в ячейку текущее время, надо нажать Ctrl+<:>, а для текущей даты –Ctrl+<;>.

Логические значения принимают значения «истина» и «ложь». Данные значения являются результатом выполнения логических операций и операций сравнения.

Ошибочные значения являются результатом ошибочных вычислений. Ошибочные значения начинаются со знака диез:н/д! (недопустимое значение),ссылка! (неверная ссылка),знач!(неверный тип аргумента в функции),имя!(не понимает имя),число!(не может правильно истолковать формулу в ячейке) и т.д.

Диапазон ячеек – группа последовательных ячеек. В ссылках на диапазон используются следующие адресные операции:

: (двоеточие) –позволяет ссылаться на все ячейки между границами диапазона вклю-

чительно (А1:В15);

, (запятая) – оператор объединения диапазонов ячеек или отдельных ячеек

ب (пробел) – оператор пересечения, который ссылается на общие ячейки диапазонов,

Β5:B15ٮ A7:D7. В этом примере ячейка В7 является общей для двух диапазонов.

Ввод, редактирование и форматирование данных.

Различают непосредственный ввод данных и использование средств автоматизации при вводе.

Непосредственный – непосредственный ввод данных в текущую ячейку. Для завершения ввода в текущей ячейке и для перехода к последующей ячейке надо нажать одну из следующих клавиш

При вводе одних и тех же данных в диапазон надо: Выделить диапазон – Ввести данные в активную ячейку диапазона – нажатьCtrl+Enter.

Автоматизация ввода.

Редактирование.

Операции редактирования можно разделить на следующие две группы:

Редактирование введенных в ячейкуданных . Содержимое ячеек можно редактировать как непосредственно в ячейке (двойной щелчок на ячейке), так и в строке формул (щелчок в правой стороне строки формул), при этом в строке состояния появляется слово «Правка». В этом режиме становятся доступными все средства редактирования.

Редактирование на уровне ячеек, диапазонов, строк, столбцов. В основном это команды редактирования меню «Правка» и «Вставка».

Форматирование.

Все команды форматирования данных, строк, столбцов, листов и др. сосредоточены в меню «Формат».

Диаграммы в Excel .

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

Объекты диаграммы .Ось (Х – ось категорий, У – ось значений).Точка данных – один элемент данных, например, з/плата за январь.Ряд данных – совокупность точек данных (хорошо виден на графике – все точки ряда данных соединены одной линией).Легенда – значки, узоры, цвета, используемые для различения рядов данных.Маркер данных – представляет точку данных на диаграмме в виде прямоугольника, сектора, точки и т.д., вид маркера зависит от вида диаграммы; все маркеры одного ряда данных имеют одинаковую форму и цвет.Текст – все надписи (название диаграммы, значения и категории на осях) и подписи (тест, связанный с точками данных); для подписей можно воспользоваться пиктограммой «надпись»на панели рисования, либо создатьплавающий текст: щелкнуть по одному из рядов данных - ввести тест (он появится в строке формул) - нажать «Enter».

Правила , используемыеExcelпо умолчанию при построении диаграмм.

1. Excelпредполагает, что ряд данных для построения диаграммы идет вдоль длинной стороны выделенного диапазона ячеек.

2. Если выделен квадратный диапазон ячеек или он занимает по ширине больше ячеек, чем по высоте, то названия категорий будут расположены в верхней строке диапазона. Если больше ячеек по высоте, чем по ширине, то названия категорий идут вниз по левому столбцу. А если в ячейках, которые Excelбудет использовать как названия категорий, содержатся числа (не текст и не даты), тоExcelпредполагает, что в этих ячейках содержится ряд данных, а названия категорий нумерует как 1, 2, 3, 4 и т.д.

3. Excelпредполагает, что названия вдоль короткой стороны выделения должны использоваться как метки легенды для каждого ряда данных. Если ряд данных один, тоExcelиспользует это название в качестве заголовка диаграммы. А если в ячейках, которыеExcelнамерен использовать как метки легенды, содержатся числа (не текст и не даты), тоExcelпредполагает, что в этих ячейках содержатся первые точки рядов данных, а каждому ряду данных присваивает имя: «Ряд1», «Ряд2» и т.д.

Макросы . Служит для автоматизации повторяющихся операций вExcel. Макрос состоит из последовательности внутренних командExcel(макрокоманда). ВExcelмакрос создается с помощью команды «Сервис/Макрос/Начать запись». Эта команда позволяет создать макрос с помощью макрорекордера (способа записи программы). Параллельно с действиями пользователя макрорекордер протоколирует действия пользователя, автоматически переводя их на собственный макроязык. Таким способом можно создавать сравнительно несложные программы, которые выполняются без участия пользователя.

Пример: создать с помощью макрорекордера макрос, который строит диаграмму динамики заработной платы Иванова А.П. по месяцам. Для этого надо:

Задания к лабораторной работе №1 .

Создать электронную таблицу по заданию преподавателя.

Построить на базе этой таблицы две диаграммы:

1. гистограмму с одной осью Y;

   диаграмму с основной и со вспомогательной осями Y, два ряда данных при этом представить в виде графиков.

Построить смешанную диаграмму, в которой один ряд данных представлен в виде гистограммы, а второй ряд данных – в виде графика. Ряды данных задать в редакторе Word, сохранить файл с расширением.txt, затем импортировать этот файл из программыExcel. Данные выдает преподаватель.

Создать макрос (по заданию преподавателя).

Лабораторные работы Excel

Лабораторная работа № 1

Создание списка клиентов

Форматирование таблицы . Для ячеек I2-I14 задайте процентный стиль (для этого выделите данный диапазон и нажмите на кнопку Процентный формат на панели инструментов Форматирование ).

Сортировка данных. Необходимовыбрать в меню Данные  Сортировка. В диалоговом окне выбрать первый критерий сортировки Код и второй критерий Город и ОК. Фильтрация данных. Выбрать в меню Данные  Фильтр/Атофильтр. После щелчка на имени этой команды в первой строке рядом с заголовком каждого столбца появиться кнопка со стрелкой. С ее помощью можно открыть список, содержащий все значения полей в столбце. Выберите название одного из городов в Город. Кроме значений полей, каждый список содержит еще три элемента: (Все), (Первые 10…) и (Условие…). Элемент (Все) предназначен для восстановления отображения на экране всех записей после применения фильтра. Элемент (Первые 10…) обеспечивает автоматическое представление на экране десяти первых записей списка. Если вы занимаетесь составлением всевозможных рейтингов, главная задача которых состоит в определении лучшей десятки, воспользуйтесь этой функцией. Последний элемент - используется для формирования более сложного критерия отбора, в котором можно применить условные операторы И и ИЛИ . Установите курсор в любую заполненную ячейку и выполните следующие действия: в меню Формат  Автоформат  Список 2 .

Создание списка товаров

Лабораторная работа № 2

Лист Заказы

Переименуйте рабочий лист ЛистЗ на имя Заказы .

Введите в первую строку следующие данные, которые будут в дальнейшем именами полей:
А1  Месяц заказа , В1  Дата заказа , С 1  Номер заказа , D 1  Номер товара , Е1  Наименование товара , F 1  Количество , G 1  Цена за ед ., H 1  Код фирмы заказчика ., I 1  Название фирмы заказчика , J 1  Сумма заказа , К1  Скидка(%) , L 1  Оплачено всего .

Для первой строки выполните выравнивание данных по центру Формат  Ячейки  Выравнивание  переносить по словам .

Выделите по очереди столбцы B, C, D, E, F, G, H, I, J, K, L и введите в поле имени имена Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2 и Оплата .

Выделите столбец В и выполните команду меню Формат  Ячейки . Во вкладке Число выберите
Числовой формат Дата , а в поле Тип выберите формат вида ЧЧ.ММ.ГГ. В завершении диалога
щелкните кнопку ОК.

Выделите столбцы G , J , L и выполните команду меню Формат  Ячейки . Во вкладке Число
выберите Числовой формат Денежный , укажите Число десятичных знаков равное 0, а в поле
Обозначение выберите $ Английский (США). В завершении диалога щелкните кнопку ОК .

Выделите столбец К и выполните команду меню Формат  Ячейки . Во вкладке Число выберите
Числовой формат Процентный , укажите Число десятичных знаков равное 0. В завершении
диалога щелкните кнопку ОК .

В ячейке А2 нужно набрать следующую формулу:

=ЕСЛИ(ЕПУСТО($В2);« »;ВЫБОР(МЕСЯЦ($В2);«Январь»;«Февраль»; «Март»; «Апрель»; «Май»;«Июнь»;«Июль»;«Август»;«Сентябрь»;«Октябрь»;«Ноябрь»;«Декабрь»)) (3.1)

И залить ячейку в желтый цвет.

Формула (3.1) работает следующим образом, вначале проверяется условие на пустоту ячейки А2. Если ячейка пусто, то ставится пробел, в противном случае с помощью функции ВЫБОР выбираем нужный месяц из списка, номер которого определяется функцией МЕСЯЦ.

Для того, чтобы набрать формулу (3.1) выполните следующие действия:

сделайте активной ячейку А2 и вызовите функцию ЕСЛИ ;

в окне функции ЕСЛИ в поле Логическое_выражеиие напечатайте вручную $ B2= «», в

поле значепие_если_истина наберите « », в поле значение_еслн_ложь вызовите функцию ВЫБОР;

в окне функции ВЫБОР в поле значение1 напечатайте «Январь», в поле значение2 напечатайте

в поле номер_индекса и вызовите функцию МЕСЯЦ ;

в окне функции МЕСЯЦ в поле Дата_как_число наберите адрес $ B 2 ;

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

В ячейку Е2 набираем следующую формулу:

Правило набора формулы:
Щелкните в ячейку Е2. Установите курсор на значок Стандартной панели. Откроется окно Мастер функции …, выберите функцию ЕСЛИ. Выполните действия, которые видите на рисунке

Т.е. в позиции Лог_выражение щелкните на ячейку D2 и три раза нажмите на клавишу F4 - получите $D2, наберите =« », клавишей Tab или мышью перейдите в позицию Значение_если_истина и наберите. « », перейдите в позицию Значение_если_ложь – щелкните на кнопку рядом с названием функции и выберите команду Другие функции.. → Категории → Ссылки и массивы, в окне Функции → ПРОСМОТР → ОК→ ОК.

Откроется окно функции ПРОСМОТР . В позиции Искомое_значение щелкните на ячейку D2 и три раза нажмите на клавишу F4 - получите $D2, клавишей Tab или мышью перейдите в позицию Просматриваемый_вектор и щелкните на ярлык листа «Товары », выделите диапазон ячеек А2:А12 , нажмите на клавишу F4, перейдите в позицию Вектор_результатов – еще раз щелкните на ярлык листа «Товары », выделите диапазон ячеек В2:В12 , нажмите на клавишу F4, и ОК. Если выполнили все верно – появится в ячейке # HD .

С

10. В ячейку G 2 набираем следующую формулу:

=ЕСЛИ($ D 2=« »;« »;ПРОСМОТР($ D 2;Номер товара; Цена)) (3.3)

Сделайте заливку ячейки желтым цветом.

11. В ячейку I 2 набираем следующую формулу:
=ЕСЛИ($Н2=« »;« »;ПРОСМОТР($ H 2;Код; Фирма)) (3.4)
Сделайте заливку ячейки желтым цветом.

12. В ячейку J 2 набираем следующую формулу:
=ЕСЛИ(F 2=« »;« »; F 2* G 2) (3.5)
Сделайте заливку ячейки желтым цветом..

13. В ячейку K 2 набираем следующую формулу:
=ЕСЛИ($Н2=« »;« »;ПРОСМОТР($ H 2;Код; Скидка)) (3.6)
Сделайте заливку ячейки желтым цветом.

14. В ячейку L 2 набираем следующую формулу:
=ЕСЛИ(J 2=« »;« »; J 2- J 2* K 2) (3.7)
Сделайте заливку ячейки желтым цветом.

15. Ячейки В2 , D2 и Н2 – в которых нет формул, залить голубым цветом. Выделите диапазон А2 – L 2 и маркером заполнения (черный крестик в правом нижнем углу блока ) протянуть заливку и формулы до 31 строки включительно..

16. Сделайте активной ячейку В2 и протяните вниз маркером заполнения до ячейки ВЗ1 включительно.

17. В ячейку С2 напечатайте число 2008-01, которое будет начальным номером заказа и протяните вниз маркером заполнения до ячейки C З1 включительно.

18. Теперь необходимо заполнить с клавиатуры столбцы В2:В31 , D 2: D 31 и Н2:Н31 . С В2 по В11 набираем январские даты (например, 2.01.08, 12.01.08). С В12 по В21 набираем февральские даты (например, 12.02.08, 21.02.08) и с В22 по В31 набираем мартовские даты (например, 5.03.08, 6.03.08). В D 2: D 31 набираем номера товаров т.е. 101, 102, 103, 104, 201, 202, 203, 204, 301, 302 и 303. Номера могут повторяться и идти в любом порядке, аналогично в Н2:Н31 вводим Коды ваших фирм, которые у вас набраны на листе Клиенты. В столбец F вводим двузначные числа.

19.

(СРСП) Лабораторная работа № 3

Бланк Заказа

В ячейку Н5 введите запись Код , а в ячейку I 5 поместите формулу
=ЕСЛИ($ E $3=“ ”; “ ”;ПРОСМОТР($ E $3;Заказ; Код2)) В ячейку С7 введите запись Наименование товара. Ячейка E 7 должна содержать формулу
=ЕСЛИ($ E $3=“ ”; “ ”;ПРОСМОТР($ E $3;Заказ; Товар2)),
а ячейкам E 7, F 7, G 7 назначьте подчеркивание и центрирование. В ячейку Н7 введите символ № , а в ячейку I 7 – формулу:
=ЕСЛИ($ E $3=“ ”; “ ”;ПРОСМОТР($ E $3;Заказ; Номер2)) В ячейку С9 введите запись Заказываемое количество. В ячейку Е9 –формулу
=ЕСЛИ($ E $3=“ ”; “ ”;ПРОСМОТР($ E $3;Заказ; Количество)) В ячейку F 9 –запись ед. по цене и выровнять ее относительно центра столбцов F и G . Ячейка Н9 должна содержать формулу
=ЕСЛИ($ E $3=“ ”; “ ”;ПРОСМОТР($ E $3;Заказ; Цена2)),
этой ячейке следует назначить подчеркивание и денежный стиль. В ячейку I 9 –запись за ед. Введите в С11 текст Общая стоимость заказа , а в Е11 поместите формулу
=ЕСЛИ($ E $3=“ ”; “ ”;ПРОСМОТР($ E $3;Заказ; Сумма)),
В ячейку F 11 –запись Скидка(%) . Выделите F 11, G 11, Н11 и выполните щелчок по кнопке Объединить и поместить в центре . В ячейку I 11 поместите формулу
=ЕСЛИ($ E $3=“ ”; “ ”;ПРОСМОТР($ E $3;Заказ; Скидка2)),
и установите параметры форматирования: подчеркивание и процентный стиль. В ячейку С13 –текст К оплате. А в ячейке D 13 разместите следующую формулу
=ЕСЛИ($ E $3=“ ”; “ ”;ПРОСМОТР($ E $3;Заказ; Оплата)),
и установите параметры форматирования: подчеркивание и денежный стиль. В ячейку Е13 введите запись Оформил(а): , выделитеЕ13 , F 13 и задайте центрирование текста. Затем выделите G 13, Н13, I 13 и задайте в них центрирование и подчеркивание. В завершение установите ширину столбцов B и J равной 1,57, выделите B 2- J 14 и задайте обрамление всего диапазона. Теперь в Е3 укажите Номер заказа , и перед печатью бланка свою фамилию .

Вы с успехом выполнили работу, сдайте ее преподавателю!.

Сводная таблица

Создан список заказов для практического применения и его данные подлежать анализу. Поможет нам выполнить анализ Мастер сводных таблиц.

Сводные таблицы создаются на основе списка или базы данных.

8. Вы с успехом выполнили работу, сдайте ее преподавателю!.

(СРСП) Лаб. № 4. Филиалы

Создайте рабочую книгу и сохраните ее в своей папке под именем Филиалы(Ваша фамилия). Начнем выполнение примера с создания таблицы и ввода данных о каждом филиале.

Подготовительный этап. Скопируйте в буфер обмена с листа Товары книги Заказы данные о товарах, их номерах и ценах, т.е. скопируйте диапазон ячеек А1-С12 листа Товары.

Перейдите к первому листу книги Филиалы и в ячейку А3 вставьте скопированный фрагмент таблицы. В 3 строе в ячейки D 3, E 3, F 3 введите соответственно записи Количество заказов, Проданное количество и Объем продаж . Задайте центрирование текста в ячейках и разрешите перенос текста по словам.

В ячейку F 4 поместите формулу: =С4*Е4 и скопируйте ее в ячейки F 5- F 14 .

Введите в ячейку В15 слово Всего: , а в ячейку F 15 вставьте формулу суммы или нажмите кнопку панели инструментов Стандартная. Excel сам определит диапазон ячеек, содержимое которых следует суммировать.

Таких листов должно быть столько, сколько у вас было городов в листе Клиенты . Мы должны скопировать этот лист 4 раза.

Для этого установите курсор мыши на его ярлычке и нажмите правую кнопку манипулятора. В контекстном меню выберите команду Переместить/скопировать , в появившемся диалоговом окне укажите лист, перед которым должна быть вставлена копия, активизируйте опцию Создать копию и нажмите ОК . Намного проще копировать с помощью мыши: установите указатель мыши на ярлычке листа и переместите его в позицию вставки копии, удерживая при этом нажатой клавишу [ Ctrl ] .

Имена рабочих листов соответствуют названиям городов с листа Клиенты , например, Алматы, Астана, Шымкент, Актау, Караганда или другие названия. Введите название филиала, соответствующего названию листа и в ячейку А1 данного листа.

Дополните лист Заказы еще одним столбцом. В ячейку М1 введите слово Город. В ячейку М2 введите формулу =ЕСЛИ(ЕПУСТО($ H 2);“ ”;ПРОСМОТР($ H 2;Код; Город)) , протяните эту формулу до строки 31 этого столбца.

Выбрать в меню Данные  Фильтр/Атофильтр. Выберите в столбце Город первый филиал. Данные столбца Количество листа Заказы будут внесены вами в столбец Проданное количество листа книги Филиалы, в строки соответствующие номерам товаров. Если проданы товары с одним номером в разные месяцы, то берется их суммарное количество. И так заполняются листы всех городов.

Консолидация данных. Скопируйте с первого листа книги Филиалы диапазон А3-В14 , перейдите в 6 рабочий лист и вставьте в ячейку А3 .

Приступаем к консолидации. Установите указатель ячейки в С3 и выберите в меню Данные  Консолидация.

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

Установите курсор ввода в поле Ссылка , выполните щелчок на ярлычке первого города, например –Алматы , выделить диапазон ячеек D 3- F 14 и нажать кнопку Добавить окна Консолидация . В результате указанный диапазон будет переставлен в поле Список диапазонов.

Затем переходите на лист второго города. Диапазон указывается автоматически, нажимаете на кнопку Добавить и так 5 раз.

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

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

Кнопку Обзор следует использовать для выбора файла, который содержит консолидируемые данные.

Нажмите кнопку ОК.

В ячейку А1 введите название новой таблицы Итоговые данные.

Введите в ячейку В70 значение Всего: , а в Е70 - и нажмите на клавишу [ Enter ]

Теперь приступаем к определению доли от общей прибыли суммы, вырученной от продажи каждого товара. Введите в F 9 формулу = Е9/$ E $70 и скопируйте ее в остальные ячейки столбца F (до ячейки F 70) .

Отформатируйте содержимое столбца F в процентном стиле. Полученные результаты позволяют сделать выводы о популярности того или иного товара.

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

Скопируйте формулу из F 9 в ячейки F 4- F 8.

Цифры в превращаются в Диаграммы

Подготовительная работа. Поскольку для каждой диаграммы нужна собственная таблица, создадим новую сводную таблицу на основе данных листа Заказы одноименной книги Заказы. Откройте ранее созданную книгу Заказы. Создайте новую книгу и присвойте ее первому листу имя Таблица . Этот лист будет содержать числовой материал для диаграммы. Поместите указатель в ячейку В3 и выберите меню Данные  Сводная таблица. Выберите первый способ расположения данных – В списке или базе данных Microsoft Excel – нажмите кнопку Далее. На втором шаге поместив курсор ввода в поле Диапазон следует с помощью меню Окно перейти в рабочую книгу Заказы и в рабочем листе Заказы и выделить диапазон A 1- L 31 . После нажимаем на кнопку Далее . Следует определить структуру сводной таблицы. Поместите в область строк кнопку Наименование товара , а в область столбцов – кнопку Месяц . Сумма будет вычисляться по полю Сумма заказа, т.е. переместите эту кнопку в область данных . Нажмите кнопку Готово . Выделите диапазон B 4- F 14 . Если вы выделяете диапазон ячеек с помощью мыши, начните выделение с любой крайней ячейки диапазона за исключением ячейки F 4 , которая содержит кнопку сводной таблицы. Щелкните на кнопке Мастер диаграмм в панели инструментов Стандартная. На первом шаге укажите тип диаграммы, нажмите на кнопку Далее. На втором шаге подтвердите диапазон =Таблица!$ B $4:$ F $15. На третьем шаге указываете параметры диаграммы (Заголовки, Оси, Легенды и т.д.). Название диаграммы введите Объем продаж по месяцам, Категории (Х)- Наименование товара иЗначение( Y ) –Объем продаж(USD ) . Внесенные изменения сразу отразятся на изображении в поле Образец, нажмите на кнопку Далее. Нажмите на кнопку Готово.

ЛАБОРАТОРНЫЕ РАБОТЫ ПО ИНФОРМАТИКЕ

ДЛЯ ЭКОНОМИКО-МАТЕМАТИЧЕСКОГО ФАКУЛЬТЕТА

(поток Мбд,МКбд – 1 курс)

Лабораторная работа 1. Ввод и редактирование в текстовом процессоре Writer (OpenOffice.org) 3

Настройка процессора............................................................................................................. 3

Набор текста.............................................................................................................................. 3

Форматирование....................................................................................................................... 5

Операции поиска и замены..................................................................................................... 6

Списки....................................................................................................................................... 6

Применение стилей.................................................................................................................. 7

Упражнения для самостоятельной работы............................................................................ 8

Контрольные вопросы по теме:.............................................................................................. 8

Лабораторная работа 2. Встраивание объектов в документ.......................................................... 9

Работа с таблицами.................................................................................................................. 9

Создание рисунков и схем...................................................................................................... 9

Методы встраивания объектов, подготовленных в других приложениях...................... 10

Настройка изображения......................................................................................................... 11

Редактирование формул........................................................................................................ 11

Автонумерация объектов...................................................................................................... 12

Упражнения для самостоятельной работы.......................................................................... 12

Контрольные вопросы по теме:............................................................................................ 12

Лабораторная работа 3. Подготовка документа к печати...................................................... 13

Проверка правописания....................................................................................................... 13

Установка колонтитулов...................................................................................................... 13

Создание сносок и концевых сносок................................................................................... 13

Нумерация страниц............................................................................................................... 13

Печать...................................................................................................................................... 14

Контрольные вопросы по теме:............................................................................................ 14

Лабораторная работа 4. Вычисления в Calc.................................................................................. 15

Использование формул.......................................................................................................... 17

Вставка функций.................................................................................................................... 18

Оформление таблицы............................................................................................................ 19

Контрольные вопросы по теме:............................................................................................ 19

Лабораторная работа 5. Деловая графика...................................................................................... 20

Построение диаграммы......................................................................................................... 20

Редактирование диаграммы.................................................................................................. 20

Упражнения для самостоятельной работы.......................................................................... 21

Контрольные вопросы по теме:............................................................................................ 21

Лабораторная работа 6. Некоторые функции OpenOffice.org Calc............................................ 22

Дата и время............................................................................................................................ 22

Логические функции............................................................................................................. 23

Математические функции..................................................................................................... 24

Контрольные вопросы по теме:............................................................................................ 24

Лабораторная работа 7. Статистическая обработка данных....................................................... 25

Числовые характеристики выборки..................................................................................... 25

Лабораторная работа 8. Функции, использующие критерии...................................................... 26

Математические функции. Функция SUMIF (СУММЕСЛИ)........................................... 28

Лабораторная работа 9. Обработка списков в CALC............................................................... 34

Создание списка..................................................................................................................... 34

Сортировка списка................................................................................................................. 35

Поиск с помощью стандартного фильтра............................................................................ 35

Автофильтрация данных....................................................................................................... 36

Расширенная фильтрация...................................................................................................... 36

Лабораторная работа 10. Основы работы в СУБД BASE............................................................ 37

Создание базы данных........................................................................................................... 37

Изменение базы данных........................................................................................................ 39

Обработка запросов................................................................................................................ 40

Контрольные вопросы по теме:............................................................................................ 40

Лабораторная работа 1. Ввод и редактирование в текстовом процессоре Writer (OpenOffice.org)

Настройка процессора

Загрузите текстовый процессор Writer (OpenOffice.org) используя Пуск / Программы / Writer (OpenOffice.org).

Загрузите справочную систему . Ознакомьтесь со справочной системой: ее структура такая же, как у справки к операционной системе Windows.

Рассмотрите элементы окна Writer. С помощью меню Вид установите режим отображения – Разметка страницы, убедитесь в наличии линеек (при необходимости подключите их, используя меню Вид ). Опробуйте другие режимы отображения как с помощью меню Вид , так и с помощью кнопок в левой нижней части окна: назначение каждой кнопки можно определить по всплывающей подсказке. Проверьте наличие двух панелей – Стандартной и Форматирования. Если установлены другие панели, отключите их (Вид / Панели инструментов). Установите масштаб изображения 100% (Вид / Масштаб / 100% / ОК или поле Масштаб с раскрывающимся списком в стандартной панели инструментов).

Для установки параметров работы текстового процессора выберите Сервис / Параметры. / OpenOffice.Writer На вкладке Вид проверьте наличие флажков у команд Показывать графические объекты и Всплывающие подсказки , и у всех команд Окна (строка состояния, вертикальная линейка, полоски прокрутки); на вкладке Общие установите единицы измерения – сантиметры, на вкладке Правописание отключите команды Автоматически проверять орфографию и Автоматически проверять грамматику – эти проверки замедляют работу, их можно будет провести, когда документ подготовлен окончательно. На вкладке Сохранение выберите Автосохранение каждые 10 минут . Просмотрите установки на других вкладках. Для реализации сделанных установок щелкните по кнопке ОК.

Параметры страницы настраиваются с помощи меню Формат / Страниц а, где могут быть установлены размер листа и его ориентация, поля и другие параметры. Используя соответствующие вкладки диалогового окна Параметры страницы , установите размер бумаги А4 , ориентация – книжная, поля – по 20 мм со всех сторон, затем нажмите ОК.

Набор текста

В панели форматирования проверьте установки: стиль – Обычный, шрифт (гарнитура) – Times New Roman, размер шрифта – 10. В рабочей области введите приведенный ниже текст, используя соответствующие кнопки выравнивания по центру (для обращения), по ширине (для основной части текста), по правому краю (фамилия). Печать точки при русской раскладке – нажатием крайней правой клавиши в нижнем ряду (знак?), запятой – эта же клавиша при нажатой Shift . Для пропуска строки используйте клавишу ввода Enter .

Для удаления ошибочно введенного текста используйте клавиши удаления Backspace или Delete . Как правило, текстовый процессор работает в режиме вставки , когда вновь вводимый в текст символ раздвигает уже введенные символы. В режиме замены, вновь вводимые символы замещают ранее введенные. Для включения или отключения режима замены используется двойной щелчок по индикатору ЗАМ в строке состояния.

Writer позволяет автоматизировать некоторые операции при наборе текста. Многократно повторяющиеся слова можно сохранить в виде автотекста , и они будут вводиться затем по первым символам или специальному обозначению. Добавьте, например, в автотекст слово "Информатика". Для этого выполните следующие действия: напечатайте в тексте слово Информатика и скопируйте его в буфер обмена, затем выберите меню Правка \ Автотекст, введите из буфера обмена в панель автотекста – скопированное слово Информатика, убедитесь что помечена категория стандартная , щелкните на кнопку автотекст , выберите в всплывающем меню – создать текст, затем щелкните по кнопке добавить . Теперь начните ввод этого слова в набираемом тексте – компьютер предложит автоматически вставить соответствующее слово; для вставки достаточно нажать клавишу Enter .

Подобным образом Writer исправляет и некоторые типичные ошибки: производит автозамену . Например, неправильно введенное слово бюлетень будет автоматически заменено на правильное – бюллетень. Настроить автозамену можно используя меню Сервис / Автозамена / Заменить. Настройте замену символов ИИИ на текст «Иванов Иван Иванович». В меню автозамены просмотрите список таких исправлений, внести в него новые слова.

Введите текст (не обращая внимание, на границы абзацев, они будут изменены далее):

Уважаемый Иван Петрович!

Извещаю Вас, что 10 марта в 10.00 в конференц-зале открытого акционерного общества "Интеграл" состоится очередное собрание кредиторов с повесткой дня:

Утверждение изменений к плану внешнего управления ОАО "Интеграл".

При себе необходимо иметь:

паспорт или удостоверение личности

доверенность

акт сверки счетов Вашего предприятия с ОАО "Интеграл"

Внешний управляющий ОАО "Интеграл"

А.А.Александров

При редактировании документа иногда надо ясно видеть, где нажаты клавиша Enter , сколько раз нажата клавиша пробела и т.п. – то есть увидеть специальные непечатаемые символы. Для этого используется кнопка Непечатаемые символы в стандартной панели инструментов (¶). Нажмите эту кнопку и просмотрите обозначения и расстановку таких символов. Отключите кнопку Непечатаемые символы .

Форматирование

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

перенесен или скопирован через буфер обмена как в любое место активного документа, так и в другой документ. Используется любой из ранее рассмотренных методов: кнопки Вырезать , Копировать , Вставить ; соответствующие команды контекстного меню или меню Правка ; сочетания клавиш клавиатуры: Ctrl+X – вырезать, Ctrl+C – скопировать в буфер, Ctrl+V – вставить из буфера. При переносе фрагмента на небольшое расстояние более удобен другой способ: поместите указатель мыши в выделенную область (вид указателя – стрелка) и перетащите фрагмент левой кнопкой мыши в нужное место (указатель изменит вид – теперь это стрелка с прямоугольником внизу). Подобным образом фрагмент можно скопировать: те же действия производятся при нажатой клавише Ctrl.

Выделенный фрагмент может быть удален нажатием клавиши удаления Delete . Скопируйте путем перетаскивания фрагмент второго предложения « в 10.00» в любое другое место этой же строки. Удалите скопированный фрагмент.

Выделите весь подготовленный текст. Измените размер шрифта на 12.

Для установки левой и правой границ текста используйте маркеры на линейке. Для установки абзацного отступа (отступа первой строки в абзаце) можно нажать клавишу табуляции Tab . Величину отступов можно отрегулировать соответствующим маркером на горизонтальной линейке.

Установите межстрочный интервал, используя меню Формат / Абзац вкладка – Отступы и интервалы, на значение полуторный . Просмотрите, какие еще виды междустрочных интервалов Вам доступны. Задать междустрочный интервал можно также соответствующими кнопками на панели инструментов форматирование . Найдите эти кнопки, выделите абзац текста и опробуйте разные значения междустрочного интервала.

Просмотрите разные способы выравнивания текста в абзаце: по левому краю, по правому краю, по центру, по ширине, используя соответствующие кнопки на панели форматирования или меню настроек абзаца.

Для точной настройки параметров абзаца можно использовать меню Формат / Абзац вкладка – Отступы и интервалы. Найдите на вкладке настройку выравнивания текста, его стиль (основной текст); отступа установленные для указанного абзаца:

ü Слева . Это отступ всего абзаца влево от границы текста;

ü Справа . Это отступ всего абзаца вправо от границы текста;

ü Перед . Это отступ всего абзаца вниз от предыдущего абзаца;

ü После . Это отступ всего абзаца вверх от последующего абзаца.

Выделите абзац, начинающийся со слов «Извещаю Вас» и установите отступ слева – 1см, отступ справа – 1см, интервал перед абзацем – 1см, интервал после абзаца – 1см. Посмотрите, как изменился текст.

Выделите в виде фрагмента первую строку и оформите другим шрифтом и размером шрифта, используя панели инструментов и контекстное меню. Задайте для данной строки разреженный шрифт, для этого используйте меню Формат / Символ . Просмотрите все возможные настройки, отраженные на вкладках этого меню и найдите настройку междусимвольного интервала. Установите разреженный на 3 пункта (пт.) шрифт. Посмотрите, как изменилось написание строки. Попробуйте установить уплотненный тип шрифта.

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

Операции поиска и замены

Предположим, что в названии организации сделана ошибка: ОАО называется не "Интеграл", а, например, "Интервал". Как внести изменения, не перепечатывая каждый раз ошибочно набранное слово? Воспользуйтесь меню Правка / Заменить. В диалоговом окне наберите в поле Найти слово Интеграл, а в поле Заменить – слово Интервал (для активизации нужного поля щелкните по нему мышью). Нажмите кнопку Найти далее, затем – Заменить. Если Вы уверены, что надо заменить все, нажмите соответствующую кнопку.

Списки

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

Перечень документов, которые необходимо иметь при себе, оформите в виде маркированного списка : выделите соответствующие три строчки, и щелкните по кнопке Маркеры . Если Вас не устраивает вид маркеров, их можно изменить с помощью меню Формат / Маркеры и нумерация (или аналогичного контекстного меню) и выбрать один из имеющихся вариантов. С помощью вкладки Изображения можно выбрать и другие маркеры. Отступы списка можно отрегулировать с помощью кнопок Уменьшить / Увеличить отступ .

Используя это же меню, можно организовать и многоуровневый список . Для этого в меню Формат / Список настраивается вид уровней создаваемого списка, вид их нумерации, отступы и другие параметры. Далее создаете обычный нумерованный список, где вся нумерация получится одного уровня. Далее с помощью клавиши на клавиатуре TAB или кнопок Уменьшить / Увеличить отступ переходим к нужному уровню в каждом пункте.

Для внесения изменений в списки можно использовать панель Маркеры и нумерация.

Предположим, что в последовательности автоматически нумерованных абзацев Вам нужен один абзац без нумерации, как в примере:

1) первый абзац

2) второй абзац

третий абзац без нумерации

3) четвертый абзац

Это можно сделать следующими способами:

Способ 1: нумеруем все абзацы без исключения; затем помещаем курсор перед третьим абзацем и нажимаем клавишу , для стирания одного символа справа. Абзац теряет свой номер.

Способ 2: нумеруем все абзацы без исключения; затем помещаем курсор перед третьим абзацем и щелкаем по значку Нумерация в панели инструментов. Абзац теряет свой номер и отступ текста.

Способ 3: пронумеруем абзацы 1 и 2 с помощью значка Нумерация в панели инструментов. Далее переходим к абзацу 4выбираем Формат – Маркеры и нумерация – вкладка Настройки и в счетчике начального значения задаем число3.

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

Сохраните созданный документ.

Применение стилей

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

Чтобы задать стиль абзаца, установите на него курсор и щелкните мышкой на кнопке стили и форматирование в панели инструментов или выбрать в меню Формат / Стили. Далее выбирается из списка нужный стиль.

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

Для создания своего стиля абзаца выберите в тексте сформированный по Вашим требованиям текст. Откройте меню стили и форматирование и щелкните по кнопке Создать стиль из выделенного .

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

Просмотрите еще раз настройки меню АБЗАЦ и СИМВОЛ, вызвав их с помощью контекстного меню или из меню окна ФОРМАТ. Опробуйте все возможные в них настройки и научитесь оформлять с помощью них текст.

Создайте новый документ (кнопка Создать) с параметрами страницы – формат А4, ориентация – книжная, поля слева и справа по 3 см, сверху и снизу – по 2 см; наберите в нем текст раздела Списки. Заголовок расположите по центру. Сделайте две копии текста в этом же документе. В первой копии измените тип шрифта (гарнитуру) и его размер. Установите разреженный интервал между символами. Увеличьте отступы слева и справа. Во второй копии измените одинарный межстрочный интервал на двойной. Установите интервал перед вторым абзацем 1 см., после него – 2 см.

Создайте файл из четырех абзацев текста, для каждого абзаца установите свой шрифт, начертание, междустрочный интервал и выравнивание текста. Выделите отдельные слова в тексте, установив у них начертание – полужирный, курсив, подчеркивание. Добавьте в текст три списка: маркированный, нумерованный и многоуровневый. Сохраните документ под именем Списки в папке Мои документы.

Для просмотра документа Извещение щелкните по его имени в меню Окно: эта команда меню позволяет активизировать любой из загруженных в Writer документов и расположить их все одновременно на экране с помощью пункта Упорядочить все. При необходимости можно изменить расположение окон, изменяя размеры окон перетаскиванием их границ и перемещая в нужное положение за строку заголовка. Расположите окна документов Извещение и Список разными способами.

Контрольные вопросы по теме:

1. Установка параметров страницы;

2. Настройка автотекста и автозамены;

3. Настройка шрифта текста;

4. Настройка параметров абзаца;

5. Стиль документа, создание собственного стиля.

Создание рисунков и схем

Рисунок может быть создан с использованием графического редактора и встроен в документ. Несложные рисунки и схемы часто удобнее подготовить, используя встроенные средства текстового процессора. Подключите панель инструментов для рисования Вид / Панели инструментов / Рисование (или кнопка Рисование в стандартной панели инструментов).

Изобразите структурную схему экономико-математического факультета. Выберите Автофигуры / Основные фигуры / Скругленный прямоугольник, протащите указатель мыши от левого верхнего до правого нижнего угла изображаемого прямоугольника, отпустите кнопку мыши (при этом нарисованная фигура остается выделенной: по ее углам и серединам сторон – квадратики-маркеры), выберите Действие / Выровнять/распределить / Выровнять по центру: скругленный прямоугольник окажется изображенным строго по центру документа. Для редактирования линии контура прямоугольника выделите его и щелкните по кнопке Тип линии: выберите нужный тип. Не снимая выделения, щелкните по кнопке Надпись, а затем внутри фигуры наберите текст: Экономико-математический факультет. Выделите текст надписи и отформатируйте ее: сцентрируйте, выберите подходящий тип и размер шрифта.

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

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

При ошибочно выполненном действии используйте кнопку Отменить; выделенный объект может быть удален нажатием клавиши Delete .

Настройка изображения

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

Различные методы настройки изображения возможны с использованием панели инструментов Изображения или контекстного меню. Установите эту панель и опробуйте возможности ее инструментов: изменение контрастности и яркости. Наиболее важным инструментом настройки изображения является кнопка, обеспечивающая обтекание встроенного в документ рисунка текстом: сверху и снизу рисунка, вокруг рамки, по контуру и т.д. Введите любой текст (например, скопируйте текст из файла Извещение), наложите на него встроенный ранее клипарт и опробуйте различные варианты обтекания. При необходимости используйте контекстное меню / Обтекание.

Редактирование формул

Для ввода простых формул бывает достаточно возможностей меню Стиль / Нижний или Верхний индекс (степень). В новом файле введите формулу y i ¹a+bx 3 (Для ввода знака неравенства и греческих букв используйте вставку символов: проверьте установку шрифта Symbol, щелкните мышью по соответствующему символу, затем по кнопке Вставить; после ввода всех необходимых символов щелкните Закрыть. Для ввода индекса и степени нажмите клавиши нужных букв на клавиатуре, выделите букву и установите Нижний индекс или Верхний индекс. Переменные x и y выделите курсивом.

Для ввода более сложных формул используется специальный формульный редактор (меню Вставка / Объект / Формула). Введите зависимость для функции распределения нормального закона

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

Автонумерация объектов

К объектам, которые требуют нумерации, относятся: рисунки, формулы и таблицы. Рассмотрим сквозную нумерацию рисунков в тексте. В контекстном меню вставленного рисунка выбираем пункт Название. Стиль нумерации и разделитель устанавливаем в соответствии с требованиями по оформлению документа. После этого рисунок автоматически получает очередной номер. Для ссылки в тексте на рисунок пишем ссылку типа «как показано на рисунке », выбираем команду главного меню Вставить – Перекрестная ссылка. В диалоговом окне выбираем тип поля Рисунки и нужный нам рисунок. После нажатия на кнопку Вставить, номер рисунка записывается в позицию курсора. При добавлении или удалении рисунков номера меняются автоматически.

Удобнее всего нумеровать рисунки без обтекания текстом.

Пронумеруйте три рисунка и вставьте на них ссылки в тексте.

Упражнения для самостоятельной работы

В свой файл вставьте таблицу с результатами сдачи Вами последней сессии: дисциплина, дата сдачи, оценка. Проведите сортировку (упорядочение данных) таблицы по алфавиту дисциплин, по датам сдачи, по полученным оценкам (Таблица / Сортировка…). Добавьте в таблицу новую строку Итого и под графой с оценками подсчитай свой суммарный балл используя функцию суммы (панель инструментов Таблица \ Сумма). К ячейке суммы вставить соответствующее примечание (Вставка \ Примечание) С подробностями выполнения этих операций разберитесь самостоятельно, используя справку.

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

Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования «Казанский государственный технологический университет» Лабораторные работы по информатике MS EXCEL Методические указания Казань 2006 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» УДК 658.26:66.094 Составители: доц. Е.С. Воробьев, доц. Е.В. Николаева, доц. Ф.И. Воробьева Лабораторные работы по информатике. MS Excel: Метод. указания / Казан. гос. технол. ун-т; Сост.: Е.С. Воробьев, Е.В. Николаева, Ф.И. Воробьева. – Казань, 2006. – 58 с. Изложены основные приемы работы в пакете MS Excel, описаны порядок и правила создания и редактирования электронных таблиц и диаграмм, выполнения основных вычислений, операций сортировки и фильтрации данных, анализа и обобщения данных, а также использование логических выражений, итоговых и дистрибутивных функций и матричных операций. Отдельная лабораторная работа посвящена поиску решения одно- и двупараметрической задачи. Могут быть использованы при изучении дисциплин «Информатика», «Применение ЭВМ в технологии» и «Применение ЭВМ в расчетах», могут служить пособием для внеаудиторной работы студентов, а также использоваться специалистами любой предметной области для самостоятельного освоения компьютерных технологий. Предназначены для студентов очной и заочной форм обучения специальностей 240802.65 «Основные процессы химических производств и химической кибернетики» и 240801 «Машины и аппараты химических производств», обучающихся по направлению 2480800 «Энерго- и ресурсосберегающие процессы в химической технологии, нефтехимии и биотехнологии». Ил. 68, табл. 1, библиогр. 5 назв. Подготовлены на кафедре общей химической технологии. Печатается по решению редакционно-издательского совета Казанского государственного технологического университета. Рецензенты: Б.К. Курбатов, доцент КГТУ им. Н.А. Туполева Э.А. Мухутдинов, доцент КГЭУ Казанский государственный технологический университет, 2006 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа №1 Упражнение 1 Основные понятия, связанные с работой электронных таблиц Excel 1. Запустите программу Microsoft Excel: щелкните по кнопке Пуск; в появившемся меню выберите пункт Программы; в выплывающем меню выберите Microsoft Excel . 2. Внимательно рассмотрите окно программы Microsoft Excel (Рис. 1). Многие пункты горизонтального меню и кнопки панелей инструментов совпадают с пунктами меню и кнопками окна редактора Word. Однако совсем другой вид имеет рабочая область, которая представляет собой размеченную таблицу, состоящую из ячеек одинакового размера. Одна из ячеек явно выделена (обрамлена черной рамкой – табличным курсором). Как выделить другую ячейку? Для этого достаточно щелкнуть по ней мышью, причем указатель мыши в это Рис. 1 время должен иметь вид светлого креста. Попробуйте выделить различные ячейки таблицы. Для перемещения по таблице воспользуйтесь полосами прокрутки. 3. Для того чтобы ввести текст в одну из ячеек таблицы, необходимо ее выделить и сразу же (не дожидаясь, как в процессоре Word текстового курсора) “писать”. Выделите одну из ячеек таблицы и “напишите” в ней название сегодняшнего дня недели. 4. Основным отличием работы электронных таблиц от текстового процессора является то, что после ввода данных в ячейку, их необходимо зафиксировать, т.е. дать понять программе, что вы закончили вводить информацию -3- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» в эту конкретную ячейку. Зафиксировать данные можно одним из следующих способов: нажать клавишу {Enter}; щелкнуть мышью по другой ячейке; воспользоваться кнопками управления курсором на клавиатуре (стрелки). Зафиксируйте введенные вами данные. 5. Выделите ячейку таблицы, содержащую день недели, и воспользуйтесь кнопками выравнивания абзацев. Каким образом происходит выравнивание? Сделайте вывод. После всех экспериментов обязательно верните исходное выравнивание – влево, в дальнейшем это будет важно. 6. Вы уже заметили, что таблица состоит из столбцов и строк, причем у каждого из столбцов есть свой заголовок (А, В, С...), и все строки пронумерованы (1, 2, 3...) (Рис. 1). Для того чтобы выделить столбец целиком, достаточно щелкнуть мышью по его заголовку, чтобы выделить строку целиком, нужно щелкнуть мышью по ее заголовку. Выделите целиком тот столбец таблицы, в котором расположено введенное вами название дня недели. Каков заголовок этого столбца? Выделите целиком ту строку таблицы, в которой расположено название дня недели. Какой заголовок имеет эта строка? Воспользуйтесь полосами прокрутки для того, чтобы определить, сколько строк имеет таблица и каково имя последнего столбца. 7. Выделите ту ячейку таблицы, которая находится в столбце С и строке 4. Обратите внимание на то, что в «Поле имени» (Рис. 1), расположенном выше заголовка столбца А, появился адрес выделенной ячейки С4. Выделите другую ячейку, и вы увидите, что в Поле имени адрес изменился. Какой адрес имеет ячейка, содержащая день недели? 8. Давайте представим, что в ячейку, содержащую день недели, нужно дописать еще и часть суток. Выделите ячейку, содержащую день недели, введите с клавиатуры название текущей части суток, например "утро", и зафиксируйте данные, нажав клавишу {Enter}. Что произошло? Часть суток не "дописалась" в ячейку, а новые данные заменили исходные и вместо дня недели вы получили часть суток. То есть, если выделить ячейку таблицы, содержащую некоторые данные и ввести новые данные с клавиатуры, в ячейке таблицы окажется последняя информация. Как же дополнить содержимое ячейки таблицы (отредактировать), не набирая заново все данные? Выделив ячейку, содержащую часть суток, вы увидите, что ее содержимое дублируется в «Строке формул», расположенной выше заголовков столбцов (Рис. 1). Именно в «Строку формул» можно щелчком мыши установить традиционный текстовый курсор, внести все требуемые изменения и затем зафиксировать окончательный вариант данных. Выделите ячейку таблицы, содержащую часть суток, установите текстовый курсор перед текстом в «Строке формул» и наберите заново день недели. Зафиксируйте данные. У вас должна получиться следующая картина (Рис. 2). -4- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» 9. Видно, что запись вышла за пределы своей ячейки и заняла часть вторник, утро соседней. Это происходит только тогда, когда соседняя ячейка пуста. Давайте ее Рис. 2 заполним и проверим, что изменится. Выделите ячейку таблицы, расположенную правее ячейки, содержащей ваши данные (ячейку, на которую они "заехали "), и введите в нее любой текст. Теперь видна только та часть ваших данных, которая помещается в ячейке (Рис. вторник, пятница 3). Как просмотреть всю запись? И Рис. 3 опять к вам на помощь придет «Строка формул». Именно в ней можно увидеть все содержимое выделенной ячейки. Итак, «Строка формул» позволяет: внести изменения в содержимое выделенной ячейки; просмотреть содержимое ячейки, если запись не видна целиком. Выделите ячейку, содержащую день недели и часть суток, и просмотрите в «Строке формул» полное содержимое ячейки. 10.Как увеличить ширину столбца для того, чтобы в ячейке одновременно были видны и день недели, и часть суток? Для этого подведите указатель мыши к правой границе заголовка столбца, "поймайте" момент, когда указатель мыши примет вид черной двойной стрелки и, удерживая нажатой левую клавишу мыши, переместите границу столбца вправо. Столбец расширился. Аналогично можно изменять высоту строки. В этом случае курсор при подводе к нижнему краю заголовка строки принимает вид. Измените ширину столбца, содержащего день недели и часть суток таким образом, чтобы весь введенный текст был виден в ячейке таблицы. 11.Часто бывает нужно выделить не одну ячейку и не целый столбец, а блок ячеек (несколько ячеек, расположенных рядом). 12.Для этого нужно установить указатель мыши в крайней ячейке выделения и, при нажатой левой клавише, переместить мышь к противоположному краю выделения (весь выделенный блок "охвачен" рамкой, все ячейки, кроме той, с которой начали выделение, окрашены в черный цвет). Обратите внимание, что в процессе выделения в «Поле имени» регистрируется количество строк и столбцов, попадающих в выделение. В тот же момент, когда вы отпустили левую клавишу, в «Поле имени» высвечивается адрес ячейки, с которой начали выделение. Выделите блок ячеек, начав с ячейки А1 и закончив ячейкой, содержащей "пятница". Для выделения всей таблицы используйте "пустую" угловую кнопку, расположенную над заголовком первой строки. -5- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Выделите таблицу целиком. Снимите выделение, щелкнув мышью по любой ячейке. 13. Каким образом удалить содержимое ячейки? Для этого достаточно выделить ячейку (или блок ячеек) и нажать клавишу {Delete} или воспользоваться командой горизонтального меню «Правка» ⇒ «Очистить». Удалите все свои записи. Упражнение 2 Применение основных приемов работы с электронными таблицами: ввод данных в ячейку. Форматирование шрифта. Изменение ширины столбца. Автозаполнение, ввод формулы, обрамление таблицы, выравнивание текста по центру выделения, набор нижних и верхних индексов Составим таблицу, вычисляющую n-й член и сумму арифметической прогрессии. Для начала напомним формулу n-го члена арифметической прогрессии: an = a1 + d (n − 1) и формулу суммы n первых членов арифметической прогрессии: n S n = (a1 + an) ⋅ , 2 где a1 – первый член прогрессии, а d – разность арифметической прогрессии. На рис. 4 представлена таблица для вычисления n-го члена и суммы арифметической прогрессии, первый член которой равен -2, а разность равна 0,725. Рис. 4 Перед выполнением упражнения придумайте свою арифметическую прогрессию, т. е. задайте собственные первый член прогрессии и разность. Выполнение упражнения можно разложить по следующим этапам: выделите ячейку А1 и введите в нее заголовок таблицы «Вычисление n-го члена и суммы арифметической прогрессии». Заголовок будет размещен в одну строчку и займет несколько ячеек правее А1; в ячейку A2 введите «d», в ячейку В3 – «n», в С3 – «an», в D3 – «Sn». Для набора нижних индексов сначала наберите весь текст, который должен быть в ячейке (например, аn), затем войдите в «Строку формул», выделите текст, который должен быть нижним индексом (например, n), откройте команду «Формат» ⇒ «Ячейки...» (в открывающемся диалоговом окне всего одна вкладка «Шрифт») и активизируйте переключатель «нижний индекс» в группе «Видоизменение»; Вычисление n-го члена и суммы арифметической прогрессии d n an Sn 0,725 1 -2 -2 0,725 2 -1,275 -3,275 0,725 3 -0,55 -3,825 0,725 4 0,175 -3,65 0,725 5 0.9 -2.75 0,725 6 1,625 -1,125 0,725 7 2,35 1,225 0,725 8 3,075 4,3 0,725 9 3,8 8,1 0,725 10 4,525 12,625 -6- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» выделите заполненные четыре ячейки. При помощи соответствующих кнопок панели инструментов увеличьте размер шрифта на 1 пт, выровняйте по центру и примените полужирный стиль начертания символов. Шапка таблицы оформлена. Теперь можете приступить к заполнению таблицы. 1. В ячейку А3 введите величину разности арифметической прогрессии (в нашем примере это 0,725). 2. Далее нужно заполнить ряд нижних ячеек таким же числом. Набирать в каждой ячейке одно и то же число неинтересно и нерационально. В редакторе Word мы пользовались приемом копировать–вставить. Excel позволяет еще больше упростить процедуру заполнения ячеек одинаковыми данными. Выделите ячейку А3, в которой размещена разность арифметической прогрессии. Выделенная ячейка окаймлена рамкой, в правом нижнем углу которой есть маленький черный квадрат – маркер заполнения. Если подвести указатель мыши к маркеру заполнения, и в тот момент, когда указатель мыши принимает форму черного крестика, протянуть маркер заполнения на несколько ячеек вниз (при этом справа от курсора появляется подсказка, какое значение вводится в текущую ячейку), то весь ряд выделенных ячеек заполнится данными, расположенными в первой ячейке. Заполните, таким образом, еще девять ячеек ниже ячейки А3 значением разности арифметической прогрессии. 3. В следующем столбце размещена последовательность чисел от 1 до 10. И опять нам поможет заполнить ряд маркер заполнения. Введите в ячейку В3 число 1, в ячейку В4 число 2, выделите обе эти ячейки и, ухватившись за маркер заполнения, протяните его вниз. Отличие от заполнения одинаковыми данными заключается в том, что, выделив две ячейки, вы указали принцип, по которому следует заполнить оставшиеся ячейки. Маркер заполнения можно "протаскивать" не только вниз, но и вверх, влево или вправо, в этих же направлениях распространится и заполнение. Элементом заполнения может быть не только формула или число, но и текст. Можно ввести в ячейку "январь" и, заполнив ряд дальше вправо, получить "февраль", "март", а "протянув" маркер заполнения от ячейки "январь" влево, соответственно получить "декабрь", "ноябрь" и т.д. Попробуйте выполнить это вне создаваемой таблицы. Самое главное, прежде чем распространять выделение, выделить именно ту ячейку (или те ячейки), по которой форматируется заполнение. 4. В третьем столбце размещаются n-е члены прогрессии. Введите в ячейку С3 значение первого члена арифметической прогрессии. В ячейку С4 нужно поместить формулу для вычисления n-го члена прогрессии, которая заключается в том, что каждая ячейка столбца отличается от предыдущей прибавлением разности арифметической прогрессии. Все формулы начинаются со знака равенства. Для ввода в ячейку формулы необходимо: активизировать ячейку; -7- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» ввести с клавиатуры знак равно «=» или в строке формул щелкнуть по кнопке «Изменить формулу» ; ввести (без пробелов) необходимые значения или ссылки, а также необходимые операторы; зафиксировать ввод. Адрес ячейки вводится в формулы в латинском регистре. Если ввод был выполнен в русском регистре, то появляется сообщение об ошибке «#ИМЯ?». Выделите ячейку С4 и наберите в ней формулу =С3+А4 (не забудьте перейти на латиницу, а вместо ссылки на ячейку А4 можно ввести конкретное значение разности вашей арифметической прогрессии). Можно и не набирать с клавиатуры адрес той ячейки, на которую делается ссылка. Набрав знак равенства, щелкните мышью по ячейке С4 и в строке формул появится ее адрес, затем продолжите набор формулы. В этом случае вам не нужно переключаться на латиницу. Полностью введя формулу, зафиксируйте ее нажатием {Enter}, в ячейке окажется результат вычисления, а в «Строке формул» сама формула. Вот проявилась и еще одна функция «Строки формул»: если в ячейке вы увидите результат вычислений по формуле, то саму формулу можно просмотреть в «Строке формул», выделив соответствующую ячейку. Если вы неправильно набрали формулу, исправить ее можно в «Строке формул», предварительно выделив ячейку. Выделите ячейку С4 и, аналогично заполнению ячеек разностью прогрессии, заполните формулой, «протащив» маркер заполнения вниз, ряд ячеек, ниже С4. Выделите ячейку С8 и посмотрите в «Строке формул», как выглядит формула, она приняла вид =С7+А8. Заметно, что ссылки в формуле изменились относительно смещения самой формулы. 5. Аналогично введите в ячейку D3 формулу =(-2+С3)*B3/2 для подсчета суммы n первых членов арифметической прогрессии, где вместо -2 должен быть первый член вашей придуманной арифметической прогрессии. Выделите ячейку D3 и заполните формулами нижние ячейки, протащив вниз маркер заполнения. 6. Теперь данными заполнены все ячейки, остается их только оформить. Все столбцы одинаковой ширины, хотя и содержат информацию разного объема. Можно вручную (используя мышь) изменить ширину отдельных столбцов, а можно автоматически подогнать ширину. Для этого выделите все ячейки таблицы, содержащие данные (не столбцы целиком, а только блок заполненных ячеек без заголовка "Вычисление n-го члена и суммы арифметической прогрессии") и выполните команду «Формат» ⇒ «Столбец» ⇒ «Автоподбор ширины». 7. Теперь отформатируем заголовок таблицы "Вычисление n-го члена и суммы арифметической прогрессии". Выделите ячейку А1 и примените полужирное начертание символов к содержимому ячейки. Заголовок довольно неэстетично "вылезает" вправо за пределы нашей маленькой таблички. -8- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Выделите четыре ячейки от А1 до D1 и выполните команду «Формат» ⇒ «Ячейки...», выберите закладку «Выравнивание» и установите переключатели (Рис. 6): группы «Выравнивание» ⇒ «по горизонтали:» в положение «по центру выделения»; группы «Отображение» ⇒ «Переносить по словам». Это позволит расположить заголовок в несколько строк и по центру выделенного блока ячеек. Таблицу почти привели к 8. виду образца. Если в этот момент выполнить просмотр «Файл» ⇒ «Предварительный просмотр», то окажется, что остается выполнить обрамление таблицы. Для этого выделите таблицу (без заголовка) и выполните команду «Формат» ⇒ «Ячейки...». В открывающемся диалоговом окне выберите вкладку «Граница», определите тип линии и активизируйте переключатели «Сверху», «Снизу», «Слева», «Справа» (Рис. 5). Данная процедура распространяется на каждую из ячеек выделенной области. Затем выделите блок ячеек, относящихся к заголовку: от А1 до D2 и, проделав те же операции, установите переключатель «Внешние». В этом случае получается рамка вокруг всех выделенных ячеек, а не каждой. Выполните просмотр. Рис. 5 Рис. 6 -9- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа №2 Упражнение 1 Закрепление основных навыков работы с электронными таблицами, знакомство с понятиями: сортировка данных, типы выравнивания текста в ячейке, формат числа Грузоотправитель и его адрес Грузополучатель и его адрес К Реестру № Дата получения «___»___________200__г. СЧЕТ № 123 от 15.11.2000 Поставщик Торговый дом «Рога и Копыта» Адрес 243100, Клинцы, ул. Пушкина, 23 Р/счет № 45638078 в МММ-банке, МФО 985435 Дополнения: № Наименование Ед.измерения 1 2 3 4 5 6 ИТОГО Руководитель предприятия Кол-во Цена Сумма Сидоркин А.Ю. Главный бухгалтер Иванова А.Н. Упражнение заключается в создании и заполнении бланка товарного счета. Выполнение упражнения лучше всего разбить на три этапа: 1-и этап – создание таблицы бланка счета; 2-й этап – заполнение таблицы; 3-й этап – оформление бланка. 1-й этап Заключается в создании таблицы. Основная задача уместить таблицу по ширине листа. Для этого: предварительно установите поля, размер и ориентацию бумаги («Файл» ⇒ «Параметры страницы…»); - 10 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» выполнив команду «Сервис» ⇒ на «Параметры...», вкладке «Вид» в группе переключателей Параметры окна активизируйте переключатель «Авторазбиение на страницы» (Рис. 7) В результате вы получите правую границу полосы набора в виде вертикальной пунктирной линии (если ее не видно, переместитесь при помощи горизонтальной Рис. 7 полосы прокрутки вправо) и нижнюю границу полосы набора в виде горизонтальной пунктирной линии (для того, чтобы ее увидеть переместитесь при помощи вертикальной полосы прокрутки вниз). Авторазбиение на страницы позволяет уже в процессе набора данных и форматирования таблицы следить за тем, какие столбцы помещаются на странице, а какие нет. № 1 2 3 4 5 6 Наименование Ед.измерения Кол-во Цена Сумма ИТОГО Рис. 8 Создайте таблицу по предлагаемому образцу с таким же числом строк и столбцов (Рис. 8). Выровняйте и сформатируйте шрифт в ячейках-заголовках, подберите ширину столбцов, изменяя ее при помощи мыши. Введите нумерацию в первом столбце таблицы, воспользовавшись помощью маркера заполнения. «Разлинуйте» таблицу, используя линии различной толщины. Обратите внимание на то, что в последней строке пять соседних ячеек не имеют внутреннего обрамления. Проще всего добиться этого следующим путем: выделить всю таблицу и установить рамку – «Внешние» жирной линией; - 11 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» затем выделить все строки, кроме последней, и установить рамку тонкой линией «Справа», «Слева», «Сверху», «Снизу»; после этого выделить отдельно самую правую ячейку нижней строки и установить для нее рамку «Слева» тонкой линией; останется выделить первую строку таблицы и установить для нее рамку «Снизу» жирной линией. Хотя можно действовать и наоборот. Сначала «разлиновать» всю таблицу, а затем снять лишние линии обрамления. На этом этапе желательно выполнить команду «Файл» ⇒ «Предварительный просмотр», чтобы убедиться, что таблица целиком вмещается на листе по ширине и все линии обрамления на нужном месте. 2-й этап Заключается в заполнении таблицы, сортировке данных и использовании различных форматов числа. Заполните столбцы «Наименование», «Кол-во» и «Цена» по своему усмотрению. Установите денежный формат числа в тех ячейках, в которых будут размещены суммы и установите требуемое число десятичных знаков, если они вообще нужны. В нашем случае это ячейки столбцов «Цена» и Рис. 9 «Сумма». Их нужно выделить и выполнить команду «Формат» ⇒ «Ячейки...», выбрать вкладку «Число» и выбрать категорию «Денежный» (Рис. 9). Это даст вам разделение на тысячи, чтобы удобнее было ориентироваться в крупных суммах. Введите формулу для подсчета суммы, которая заключается в умножении цены на количество, и заполните формулой ряд ячеек вниз. Введите формулу в ячейку для итоговой суммы. Для этого выделите блок тех ячеек, которые нужно сложить, и одну пустую ячейку под этим блоком, в которую нужно поместить результат. После этого нажмите кнопку панели инструментов. Попробуйте изменить данные в отдельных ячейках и проследите, как изменится результат вычислений. Отсортируйте записи по алфавиту. Для этого выделите все строки таблицы, кроме первой (заголовка) и последней («Итого»), можно не выделять и - 12 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» нумерацию. Выполните команду «Данные» ⇒ «Сортировка...» (Рис. 10), выберите столбец, по которому нужно отсортировать данные (в нашем случае это столбец В, так как именно он содержит перечень товаров, подлежащих сортировке), и установите переключатель в положение «По возрастанию». 3-й этап оформления счета вставьте Для дополнительные строки перед таблицей. Для этого выделите несколько первых строк таблицы и выполните команду «Вставка» ⇒ «Строки». Рис. 10 Вставится столько же строк, сколько вы выделили. Наберите необходимый текст до и после таблицы. Следите за выравниванием. Обратите внимание, что текст «Дата получения «__»_______200_г.» и фамилии руководителей предприятия внесены в тот же столбец, в котором находится столбик таблицы «Сумма» (самый правый столбец нашей таблички), только применено выравнивание вправо. Текст «СЧЕТ №…» внесен в ячейку самого левого столбца, и применено выравнивание по центру выделения (предварительно выделены ячейки одной строки по всей ширине таблицы счета). Применена рамка для этих ячеек сверху и снизу. Вся остальная текстовая информация до и после таблицы внесена в самый левый столбец, выравнивание влево. Выполните просмотр. Упражнение 2 Введение понятия «абсолютная ссылка», установка точного значения ширины столбца при помощи команд горизонтального меню. Вставка функции при помощи мастера функций Новое понятие «абсолютная ссылка» можно рассмотреть на конкретном примере. Подготовим традиционную таблицу квадратов двузначных чисел (Рис. 11), так хорошо знакомую каждому из курса алгебры. В ячейку A3 введите число 1, в ячейку А4 - число 2, выделите обе ячейки и протащите маркер выделения вниз, чтобы заполнить столбец числами от 1 до 9. Аналогично заполните ячейки В2 - К2 числами от 0 до 9. Когда вы заполнили строчку числами от 0 до 9, то все необходимые вам для работы ячейки одновременно не видны на экране. Давайте сузим их, но так, чтобы все столбцы имели одинаковую ширину (чего нельзя добиться, изменяя ширину столбцов мышкой). Для этого выделите столбцы от А до К и выполните команду «Формат» ⇒ - 13 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» «Столбец» ⇒ «Ширина...», в поле ввода «Ширина столбца» введите значение, например 5. Разумеется, каждому понятно, что в ячейку В3 нужно поместить формулу, которая возводит в квадрат число, составленное из десятков, указанных в столбце А, и единиц, соответствующих значению, размещенному в строке 2. Таким образом, само число, которое должно возводиться в квадрат в ячейке В3 можно задать формулой А3*10+В2 (число десятков, умноженное на десять плюс число единиц). Остается возвести это число в квадрат. 1 2 3 4 5 6 7 8 9 0 100 400 900 1600 2500 3600 4900 6400 8100 1 121 441 961 1681 2601 3721 5041 6561 8281 ТАБЛИЦА КВАДРАТОВ 2 3 4 5 144 169 196 225 484 529 576 625 1024 1089 1156 1225 1764 1849 1936 2025 2704 2809 2916 3025 3844 3969 4096 4225 5184 5329 5476 5625 6724 6889 7056 7225 8464 8649 8836 9025 6 256 676 1296 2116 3136 4356 5776 7396 9216 7 289 729 1369 2209 3249 4489 5929 7569 9409 8 324 784 1444 2304 3364 4624 6084 7744 9604 9 361 841 1521 2401 3481 4761 6241 7921 9801 Рис. 11 Попробуем воспользоваться «Мастером функций». Для этого выделите ячейку, в которой должен разместиться результат вычислений (ВЗ), и выполните команду «Вставка» ⇒ «Функция...» (Рис. 12). В диалоговом окне «Мастер функций (шаг 1 из 2)» (Рис. 12) имеется два подокна: «Категория» и «Функция». При выборе определенной функции в нижней части диалогового окна появляется краткое ее описание. Среди предложенных Рис. 12 категорий функций выберите «Математические», среди «Функций» - «Степень», нажмите кнопку Ok. В следующем диалоговом окне (Рис. 13) введите в поле «Число» (основание степени) – А3*10+В2 и в поле «Показатель степени» − 2. Так же, как и при наборе формулы непосредственно в ячейке электронной таблицы, нет необходимости вводить с клавиатуры адрес каждой ячейки, на которую ссылается формула. В - 14 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» диалоговом окне второго шага «Мастера функций» достаточно указать мышью на соответствующую ячейку электронной таблицы, и ее адрес появится в поле ввода «Число» диалогового окна. Вам останется ввести только арифметические Рис. 13 знаки (*, +) и число 10. В тех случаях, когда для ввода аргументов нужно выделять ячейки, закрытые окном, справа от каждого поля для ввода аргументов имеются кнопки, которые позволяют сворачивать и разворачивать диалоговое окно. Кроме того, окно «Мастера функций» можно переместить в сторону, «схватив» мышью за заголовок. В этом же диалоговом окне (Рис. 13) можно увидеть значение самого числа (10) и результат вычисления степени (100). Остается только нажать кнопку Ok. В ячейке В3 появился результат вычислений. Хотелось бы распространить эту формулу и на остальные ячейки таблицы. Выделите ячейку В3 и заполните, протянув маркер заполнения вправо, соседние ячейки. Что произошло (Рис. 14)? Рис. 14 Почему результат не оправдал наших ожиданий? В ячейке С3 не видно числа, т. к. оно не помещается целиком в ячейкуРасширьте мышью столбец С. Число появилось на экране, но оно явно не соответствует квадрату числа 11 (Рис. 15). Рис. 15 Почему? Дело в том, что когда мы распространили формулу вправо, Excel автоматически изменил адреса ячеек с учетом нашего смещения, на которые ссылается формула, и в ячейке С3 возводится в квадрат не число 11, а число, вычисленное по формуле В3*10+С2. Во всех предыдущих упражнениях нас вполне устраивали относительные ссылки на ячейки таблицы (при перемещении формулы по такому же закону смещаются и ссылки), однако здесь возникла необходимость зафиксировать определенные ссылки, т.е. указать, что число десятков можно брать только из столбца А, а число единиц только из строки 2 (для того, чтобы формулу можно было - 15 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» распространить вниз). Для этого в Excel существует возможность задавать абсолютные и смешанные ссылки. Абсолютная ссылка – это ссылка, которая не изменяется при копировании формул. Для этого перед именем столбца и номером строки добавляется знак доллара $ (либо вводится с клавиатуры, либо после ввода адреса ячейки нажимается функциональная клавиша F4). Смешанными называются ссылки, которые только частично являются абсолютными, т.е. фиксируется либо столбец, либо строка. В этом случае знак доллара $ устанавливается либо перед буквой, в случае, когда фиксируется столбец, либо перед цифрой, в случае, когда фиксируется строка. Знак доллара $ либо вводится с клавиатуры, либо после ввода адреса ячейки нажимается функциональная клавиша F4 до тех пор, пока знак $ не окажется в необходимом месте. При копировании формулы, содержащей смешанную ссылку, изменяется только относительная часть ссылки. Верните ширину столбца С в исходное положение и выполните следующие действия: Выделите ячейку В3 и, установив текстовый курсор в «Строку формул», исправьте имеющуюся формулу =СТЕПЕНЬ(А3*10+В2;2) на правильную =СТЕПЕНЬ($А3*10+В$2,2). Теперь, воспользовавшись услугами маркера заполнения, можно заполнить этой формулой все свободные ячейки таблицы (сначала протянуть маркер заполнения вправо, затем, не снимая выделения с полученного блока ячеек, вниз). Для ввода ссылок на ячейки с данными столбца А и строки 2 мы использовали смешанные ссылки. Абсолютную ссылку в нашем примере можно было бы использовать, если бы мы ввели в формулу не число 10, на которое умножаются числа в столбце А, а адрес ячейки, например A15 (где ввели бы это число 10). В этом случае формула в ячейке B3 записалась бы в виде: =СТЕПЕНЬ($A3*$A$15+B$2), а затем также скопировали ее в остальные ячейки. Попробуйте это выполнить. Осталось оформить таблицу: ввести и ячейку А1 заголовок, отформатировать его и отцентрировать по выделению, выполнить обрамление таблицы и заполнение фоном отдельных ячеек. Упражнение 3 Введение понятия «имя ячейки» Представьте, что вы имеете собственную фирму по продаже какой-либо продукции и вам ежедневно приходится распечатывать прайс-лист с ценами на товары в зависимости от курса доллара. Подготовьте таблицу, состоящую из столбцов: «Наименование товара»; «Эквивалент $ US»; «Цена в р.». - 16 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Заполните все столбцы, креме «Цена в р.». Столбец «Наименование товара» заполните текстовыми данными (перечень товаров по вашему усмотрению), а столбец «Эквивалент $ US» числами (цены в $). Понятно, что в столбце «Цена в р.» должна разместиться формула: «Эквивалент $ US»*«Курс доллара». Почему неудобно в этой формуле умножать на конкретное значение курса? Да потому, что при каждом изменении курса, вам придется менять свою формулу в каждой ячейке. Проще отвести под значение курса доллара отдельную ячейку, на которую и ссылаться в формуле. Ясно, что ссылка должна быть абсолютной, т.е. значение курса доллара можно брать только из этой конкретной ячейки с зафиксированным адресом. Как задавать абсолютные ссылки, мы рассмотрели выше, однако существует еще один удобный способ: ссылаться не на адрес ячейки, а на имя, которое можно присвоить ячейке. При присвоении имен ячейке или диапазону ячеек, можно обращаться к этой ячейке или диапазону в любой момент и из любой точки таблицы, даже если они меняют свои местоположения или находятся на других листах. Выделите ячейку, в которую будет вводиться курс доллара (выше таблицы), введите в нее значение курса доллара на сегодняшний день и выполните команду «Вставка» ⇒ «Имя» ⇒ «Присвоить...». В открывающемся диалоговом окне (Рис. 16) можно ввести любое имя и выбрать в поле «Формула» диапазон, для которого вводится это имя. Имя может иметь в длину до 255 символов и содержать буквы, цифры, подчерки (_), символы: обратная косая черта (\), точки и вопросительные знаки. Однако первый символ должен быть буквой, подчерком (_) или символом обратная косая черта (\). Не допускаются имена, которые воспринимаются как числа или ссылки на ячейки. В появившемся диалоговом окне вам остается только ввести имя ячейки (ее точный адрес уже приведен в поле ввода «Формула») и нажать кнопку Оk. Обратите внимание на то, что в «Поле имени», вместо адреса ячейки, теперь размещено ее имя. В ячейку, расположенную левее ячейки «Курс_доллара», можно ввести текст «Курс доллара». Рис. 16 Теперь остается ввести формулу для подсчета цены в рублях. Для этого выделите самую верхнюю пустую ячейку столбца «Цена в рублях» и введите формулу следующим образом: введите знак «=», затем щелкните мышью - 17 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» по ячейке, расположенной левее (в которой размещена цена в дол.), после этого введите знак «*» и «Курс_доллара». Формула должна выглядеть приблизительно так: =В7*Курс_доллара. Заполните формулу вниз, воспользовавшись услугами маркера заполнения. Выделите соответствующие ячейки и примените к ним денежный формат числа. Оформите заголовок таблицы: выровняйте по центру, примените полужирный стиль начертания шрифта, расширьте строку и примените вертикальное выравнивание по центру, воспользовавшись командой «Формат» ⇒ «Ячейки...», выберите вкладку «Выравнивание» и в группе «Вертикальное:» выберите «По центру». В этом же диалоговом окне активизируйте переключатель «Переносить по словам» на случай, если какой-то заголовок не поместится в одну строчку. Измените ширину столбцов. Выделите таблицу и задайте для нее обрамление. - 18 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа №3 Упражнение 1 Изменение ориентации текста в ячейке, ознакомление с возможностями баз данных Excel. Сортировка данных по нескольким ключам Представьте себя владельцем маленького магазина. Необходимо вести строгий учет прихода и расхода товаров, ежедневно иметь перед глазами реальный остаток, иметь возможность распечатать наименование товаров по отделам и т.д. Даже в таком непростом деле Excel может заметно облегчить работу. Разобьем данное упражнение на несколько заданий в логической последовательности: создание таблицы; заполнение таблицы данными традиционным способом и с применением формы; подбор данных по определенному признаку. 1. Создание таблицы Введите заголовки таблицы в соответствии с предложенным образцом. Учтите, что заголовок располагается в двух строках таблицы: в верхней строке «Приход», «Расход», «Остаток», а строкой ниже остальные пункты заголовка (Рис. 17). Сумма остатка Остаток Кол-во остатка Кол-во расхода Расход Цена расхода Наименование товара Кол-во прихода Отдел Цена прихода № Единица измерения Приход 1 2 3 4 5 6 Рис. 17 Ввод текста заголовка лучше начать со второй строки. Вы уже обратили внимание на то, что графа «Приход» распространяется на две ячейки. Слово «Приход» набрано в том же столбце, что и «Цена прихода», затем выделены две соседние ячейки, и текст отцентрирован по выделению (данная операция неоднократно рассматривалась в предыдущих упражнениях). Аналогично сформатированы ячейки «Расход» и «Остаток». Выделите вторую строку заголовка и выровняйте по центру. Также видно, что для того, чтобы вся таблица могла разместиться по ширине на листе, в некоторых ячейках текст «повернут на 90°». Выделите те ячейки, в - 19 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» которых нужно «развернуть» текст и выберите команду «Формат» ⇒ «Ячейки...», на вкладке «Выравнивание» (Рис. 18) выберите «Ориентацию текста» 90o и обязательно активизируйте переключатель «Переносить по словам» (вертикальное выравнивание оставьте «По нижнему краю»). Для оставшихся (неразвернутых) ячеек примените вертикальное выравнивание «По центру». Задайте обрамление таблицы («Формат» ⇒ «Ячейки…», вкладка «Граница»). Установите в ячейках, Рис. 18 содержащих цены, денежный формат числа («Формат» ⇒ «Ячейки…», вкладка «Число»). Введите нумерацию строк таблицы (столбец №), при помощи маркера заполнения. Вставьте формулы для количества остатка («Кол-во прихода» минус «Кол-во расхода») и суммы остатка («Кол-во остатка» умножить на «Цену расхода»). Распространите эти формулы вниз по таблице. В процессе выполнения задания во многих случаях удобнее пользоваться контекстным меню, вызываемым нажатием правой клавиши мыши. Так, для форматирования ячеек их достаточно выделить, щелкнуть правой клавишей мыши в тот момент, когда указатель мыши находится внутри выделения и выбрать команду «Формат» ⇒ «Ячеек...». При этом вы перейдете к тому же диалоговому окну «Формат ячеек» (Рис. 18). Да и редактировать содержимое ячейки (исправлять, изменять данные) совсем не обязательно в «Строке формул». Если дважды щелкнуть мышью по ячейке или нажать клавишу F2, в ней появится текстовый курсор и можно произвести все необходимые исправления. 2. Заполнение таблицы Переименуйте «Лист1» в «Наличие». Для этого нажмите правую кнопку мыши на ярлыке «Лист1» и выберите команду переименовать. Введите новое название и нажмите клавишу {Enter}. Определитесь, каким видом товара вы собираетесь торговать, и какие отделы будут в вашем магазине. Вносите данные в таблицу не по отделам, а вперемешку (в порядке поступления товаров). Заполните все ячейки, кроме тех, которые содержат формулы («Остаток»). Обязательно оставьте последнюю строку таблицы пустой (но эта строка должна содержать все формулы и нумерацию). Вводите данные таким - 20 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» образом, чтобы встречались разные товары из одного отдела (но не подряд) и обязательно присутствовали товары с нулевым остатком (все продано) (Рис. 19). Согласитесь, что традиционный способ заполнения таблицы не особенно удобен. Воспользуемся возможностями баз данных Excel. 1 2 3 4 5 6 Отдел Кондитерский Молочный Мясной Мясной Вино-водочный Наименование товара Зефир в шоколаде Сыр Колбаса Московская Балык Водка «Абсолют» Расход Остаток Цена прихода Кол-во прихода Цена расхода Кол-во расхода Кол-во остатка Сумма остатка № Единица измерения Приход упак. 20 р. 15 кг. 65 р. 10 кг. 110 р. 20 кг. 120 р. 10 бут. 2 л. 400 р. 100 25р. 85 р. 120р. 140 р. 450 р. 15 8 15 5 99 0 2 5 5 1 0 0 р. 170 р. 600 р. 700 р. 450 р. 0 р. Рис. 19 Выберите команду «Данные» ⇒ «Форма...» Вы получите форму данных (Рис. 20), содержащую статический текст (имена полей базы данных) и окон редактирования, в которых можно вводить и редактировать текст. Вычисляемые поля (в которых размещены формулы) выводятся на экран без окон редактирования («Колво остатка» и «Сумма остатка»). Теперь вы имеете свою таблицу как бы в форме отдельных карточекзаписей (каждая из которых представляет строку таблицы). Рис. 20 Перемещаться между записями можно либо при помощи кнопок «Предыдущая», «Следующая», либо клавишами управления курсором (вверх, вниз), либо перемещая бегунок на полосе прокрутки формы данных. Дойдя до последней записи (мы специально оставили ее пустой, но распространили на нее формулы и нумерацию), заполните ее новыми данными. Перемещаться между окнами редактирования, в которые вносятся данные, удобно клавишей {Tab}. Когда заполните всю запись, нажмите клавишу {Enter}, и вы автоматически перейдете к новой чистой карточке-записи. Как только вы заполните новую запись, вся внесенная вами информация автоматически воспроизведется в исходной таблице. Заполните несколько новых записей и нажмите кнопку «Закрыть». - 21 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Как видно, заполнять таблицу в режиме формы довольно удобно. 3. Заполнение таблицы с помощью готового списка данных Поскольку количество отделов у нас ограничено и их названия постоянны, то при заполнении таблицы лучше всего пользоваться заранее подготовленным списком этих отделов. Сотрем названия отделов из столбца «Отдел» и введем короткий список, включающий названия всех отделов по одному разу, за пределами таблицы, например в столбце L. Затем выделяем ячейки столбца «Отдел» в таблице и выбираем команду «Данные» ⇒ «Проверка». При этом появится Рис. 21 диалоговое окно «Проверка вводимых значений» (Рис. 21), где мы должны указать условия проверки. В нашем примере мы должны выбирать из списка (что и вводим в поле «Тип данных»). Чтобы выбрать «Источник» данных воспользуйтесь кнопкой свертывания окна. Нажмите ее, выделите список наших отделов в столбце L и вернитесь в окно с помощью кнопки разворачивания окна. Выполнив эти действия, нажмите кнопку Ok. Теперь при переходе в ячейки столбца «Отдел», где установлено условие проверки, справа от этих ячеек будет появляться квадрат со стрелкой, щелкнув по которому, мы можем выбрать необходимое нам название отдела (Рис. 22). Рис. 22 Чтобы скрыть таблицу отделов, можно сделать шрифт в ячейках столбца L белым, либо скрыть весь столбец. Чтобы скрыть столбец L, выделите его, выберите команду «Формат» ⇒ «Столбец» ⇒ «Скрыть». Чтобы вернуть столбец L на экран, - 22 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» необходимо выделить столбцы вокруг скрытого столбца (столбцы K и М) и выполнить команду «Формат» ⇒ «Столбец» ⇒ «Отобразить». Заметим, что команду «Скрыть» можно применять и к строкам. Для этого выделяется строка, выбирается команда «Формат» ⇒ «Строка» ⇒ «Скрыть». Чтобы вернуть строку на экран, необходимо выделить строки вокруг скрытой строки и выполнить команду «Формат» ⇒ «Строка» ⇒ «Отобразить». Рис. 23 Создавать список можно и на другом листе. Однако, в этом случае нельзя в качестве «Источника» информации указать адреса, включающие имя листа, т.е. необходимо вводить в качестве адресации имя диапазона ячеек. На прошлом занятии мы познакомились с тем, как задавать имя одной ячейки. Для задания имени диапазоны ячеек необходимо перед выполнением команды «Вставка» ⇒ «Имя» ⇒ «Присвоить» выделить диапазон ячеек, а не одну ячейку. Перенесем наш список отделов из столбца L Листа1 на Лист2 в столбец А. Выделяем ячейки, в которых помещен наш список и выполняем команду «Вставка» ⇒ «Имя» ⇒ «Присвоить». В открывающемся диалоговом окне (Рис. 23) можно ввести любое имя, например «Отдел», и выбрать в поле «Формула» диапазон, для которого вводится это имя (по умолчанию здесь помещается адрес выделенного нами диапазона). После этого нажимаем кнопку Ok. Теперь в диалоговом окне команды «Проверка…» в качестве источника (Рис. 21) достаточно ввести знак «=», затем нажать клавишу F3 для открытия списка имеющихся имен ячеек, выбрать в открывшемся списке «Отдел» и нажать кнопки Ok для закрытия диалоговых окон. 4. Сортировка данных Итак, вы заполняли таблицу в порядке поступления товаров, а хотелось бы иметь список товаров по отделам, для этого применим сортировку строк. Выделите таблицу со второй строкой заголовка, но без первого столбца «№», и выберите команду «Данные» ⇒ «Сортировка...» (Рис. 24). Рис. 24 - 23 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Выберите первый ключ сортировки: в раскрывающемся списке «Сортировать по» выберите «Отдел» и установите переключатель в положение «По возрастанию» (все отделы в таблице расположатся по алфавиту). Если же вы хотите, чтобы внутри отдела все товары размещались по алфавиту, то выберите второй ключ сортировки: в раскрывающемся списке «Затем по» выберите «Наименование товара», установите переключатель в положение «По возрастанию». Теперь вы имеете полный список товаров по отделам. 5. Фильтрация данных Продолжим знакомство с возможностями баз данных Excel. Вспомним, что нам ежедневно нужно распечатывать список товаров, оставшихся в магазине (имеющих ненулевой остаток) или показать остатки в какомлибо отделе, но для этого сначала нужно получить такой список, т.е. отфильтровать данные. Выделите таблицу со второй строкой заголовка (как перед созданием формы данных). Выберите команду меню «Данные» ⇒ «Фильтр...» ⇒ «Автофильтр». Снимите выделение с таблицы. У каждой ячейки заголовка таблицы появилась кнопка со стрелкой (она не выводится на печать), позволяющая задать критерий фильтра (Рис. 25). Рис. 25 Пусть мы хотим оставить все записи для «Кондитерского отдела». Раскройте список ячеек «Отдел» и выберите «Кондитерский». В этом случае Excel изменит таблицу и покажет ее в виде, где будут присутствовать только данные, относящиеся к выбранному отделу (Рис. 26), а стрелка в колонке, где был применен автофильтр, станет синей. Рис. 26 - 24 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Точно также можно посмотреть данные и по другим отделам или выбрать критерий фильтрации в другом столбце. В отфильтрованном виде таблицу можно распечатать. Отфильтрованные строки можно выделить цветом шрифта, фоном, рамками, иным способом отформатировать. Убрав фильтрацию, мы получим очень наглядную разметку таблицы. В отфильтрованном участке таблицы можно вычислять суммы, произведения, производить другие операции так, как будто нет других строк таблицы. Например, мы хотим посчитать сумму остатка по кондитерскому отделу. Для этого мы выделяем данные столбца «Сумма остатка», захватив свободную последнюю ячейку, и нажимаем кнопку «Автосуммирования» . В свободной ячейке появится функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; F2:F8) (Рис. 26). В ней первым аргументом является номер математической или статистической операции (1 вычисление среднего значения; 2 и 3 – подсчет количества чисел и непустых ячеек; 4 и 5 – вычисление максимума и минимума; 6 - произведение; 7 и 8 – стандартное отклонение; 9 – сумма; 10 и 11 – дисперсия), а вторым – интервал вычислений. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ находится в категории математические и отличается тем, что она вычисляет значения только по видимым ячейкам, а невидимые не учитывает. При изменении фильтрации меняются и промежуточные итоги (Рис. 25), тогда как обычная функция суммирования или произведения останется неизменной. Изменим немного условие задачи, пусть мы хотим посмотреть данные по ненулевым остаткам кондитерского отдела. Для этого выберем в списке столбца «Сумма остатка» пункт «Условие». При этом появится диалоговое окно «Пользовательский автофильтр» (Рис. 28). В верхнем поле выбираем «больше» «0.00р.». Полученный результат представлен на рис. 28. Рис. 27 А теперь, допустим, мы хотим посмотреть данные по ненулевым остаткам кондитерского и мясного отделов. Для этого в столбце «Сумма остатка» мы оставляем тот же самый фильтр, а в столбце «Отдел» выбираем «Условие» (Рис. 28). В верхнем поле выбираем «равно» Рис. 28 - 25 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» «Кондитерский», внизу «равно» «Мясной», а в качестве логической функции задаем ИЛИ. Полученный результат представлен на рис. 29. Рис. 29 Чтобы снова увидеть таблицу целиком, необходимо щелкнуть по стрелке в столбце, где применялась фильтрация (они показаны синими стрелками), выбрать в списке «Все» или еще раз зайти в команду «Фильтр» ⇒ «Автофильтр» меню «Данные» для отмены режима фильтрации. - 26 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа № 4 Упражнение 1 Создание и редактирование графиков в документе Excel Очень часто в жизни приходится измерять зависимость одной переменной от другой и показывать эти зависимости в виде графиков. Excel обеспечивает возможность такого наглядного отображения числовых данных электронных. Допустим, был проведен какой-нибудь эксперимент, например, измерили зависимость какогото параметра от температуры. Начальная температура была равна 10оC. Шаг изменения – 10оС. Введите эти данные на лист Excel (Рис. 30). Чтобы расположить заголовок «Исходные данные» над таблицей исходных данных так, как Рис. 30 показано на рисунке, выделите две ячейки, выполните команду «Формат» ⇒ «Формат ячейки…» и на вкладке «Выравнивание» поставьте переключатель в полях «переносить по словам», «объединить ячейки» в группе отображение и выравнивание «по центру» и по горизонтали, и по вертикали (см. Лабораторная работа №1). Теперь подготовим таблицу экспериментальных данных (Рис. 31). Введите данные первой колонки – номера по порядку вводятся с помощью маркера заполнения (черной точки в углу табличного курсора). Заголовок столбца «Температура» вводится по формуле: =A2 (т.е. в формуле ссылка на адрес ячейки, Рис. 31 где помещено название параметра в таблице «Исходные данные» (Рис. 30)). Начальная температура у нас равна исходной температуре, т.е. =B2. Далее она отличается от предыдущей на шаг. Следовательно, во второй строке температура равна начальной температуре + шаг, т.е. =E3+$B$3. Чтобы далее использовать маркер заполнения для копирования формулы, а шаг должен браться постоянно из одной и той же ячейки, мы делаем ссылку на нее абсолютной. Введенная таким образом информация позволяет нам автоматизировать подстройку таблицы экспериментальных данных к изменению начальных условий. Попробуйте вместо «температуры» в исходных данных ввести «давление», а исходное значение поставить, например, 20. Значения Yэкпер должны браться из эксперимента, поэтому просто введите их с клавиатуры. Для форматирования заголовка «Эксперимент» лучше всего воспользоваться возможностью копирования формата. Для этого: - 27 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Рис. 32 Рис. 33 выберите ячейку «Исходные данные»; щелкните на кнопке «Формат по стандартной панели образцу» инструментов. При этом Excel копирует формат выбранной ячейки, а указатель мыши превращается в кисточку, слева от которой расположен знак «плюс»; протащите указатель мыши по диапазону ячеек, в которые нужно скопировать выбранный формат. Для того чтобы построить график зависимости Yэкпер от температуры, выделите эти два столбца, включая их заголовки, и выполните команду «Вставка» ⇒ «Диаграмма» или нажмите кнопку вызова Мастера диаграмм на панели инструментов. Первое диалоговое окно «Мастер диаграмм (шаг 1 из 4) – тип диаграммы» (Рис. 32) имеет две вкладки – «Стандартные» и «Нестандартные». На этом этапе выбирается вариант строящейся диаграммы из имеющихся образцов. Чтобы построить график зависимости одной величины от другой, нужно выбрать тип диаграммы «Точечная», а затем любой из пяти ее видов. Пусть мы нанесем только точки и не будем прорисовывать линии. Щелкните по кнопке «Просмотр результата» (Рис. 32). Excel сразу покажет, как будут отображаться наши данные на готовой диаграмме. Для перехода к каждому последующему шагу Мастера диаграмм используется кнопка «Далее». Во втором окне «Мастер диаграмм (шаг 2 из 4): источник данных диаграммы» на вкладке «Диапазон данных» отображаются адрес выделенного диапазона исходных данных и образец строящейся диаграммы (Рис. 33). Выберите «в столбцах» и нажмите кнопку «Далее». - 28 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» На третьем этапе работы Мастера диаграмм (на шаге 3) в окне «Параметры диаграммы» (Рис. 34) определите характер оформления диаграммы – выполните ее форматирование. Третье окно Мастера диаграмм имеет следующие вкладки: заголовок – позволяет ввести текст заголовка диаграммы и подписи осей; оси – позволяет определить Рис. 34 отображение и маркировку осей координат; линии сетки – позволяет определить тип линий и характер отображения сетки; легенда – позволяет отобразить или скрыть легенды и определить ее место на диаграмме. Легенда – Рис. 35 это небольшое подокно на диаграмме, в котором отображаются названия рядов данных и образцы их раскраски на диаграмме (в виде ключа Легенды); подписи данных – позволяет управлять отображением надписей, соответствующих отдельным элементам данных на диаграмме; таблица данных – позволяет добавить или скрыть на диаграмме таблицу данных, использованную для построения диаграммы. Последнее (четвертое) окно мастера диаграмм (Рис. 35) служит для определения ее размещения в рабочей книге. Рекомендуется выбрать ее размещение на отдельном листе, т.к. в этом случае диаграмму легче вставлять в другие документы, она не загораживает исходные данные, она более читабельна и т.д. Как только вы определили все необходимые параметры, нажмите кнопку «Готово». Excel построил нам диаграмму (Рис. 36), используя некий набор параметров, таких как цвет области построения (области, в которой отображена сама диаграмма, без заголовков, легенды и других элементов), шрифт, шкалы, размер точек и т.д., по умолчанию. Чтобы изменить тот или иной параметр форматирования диаграммы, необходимо щелкнуть по нему правой кнопкой мыши и в открывшемся контекстном меню выбрать соответствующую команду. Сделайте фон области построения диаграммы белым. Для этого щелкните по области построения диаграммы правой кнопкой мыши и выберите команду «Формат области построения». В открывшемся диалоговом окне (Рис. 37) поставьте переключатель в группе заливка «обычная». Нажмите кнопку Ok. - 29 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Y=f(x) 12 10 8 Y 6 4 2 0 0 20 40 60 80 100 120 X Yэкспер Рис. 36 Рис. 37 Рис. 38 Увеличьте размер точек. Для этого щелкните по точкам правой кнопкой мыши и выберите команду «Формат ряда данных». В открывшемся диалоговом окне (Рис. 38) в группе «размер» установите размер точек например 8 пт. Здесь же можно выбрать другие параметры данных, например, изменить маркер, т.е. вид точек, провести линию, выбрав ее цвет, толщину и тип, сгладить линию и др. Наш аргумент функции изменяется от 10 до 100, а шкала по оси X имеет минимальное значение 0, а максимальное – 120. Кроме того, шрифт подписи данных мелковат. Как изменить это? Щелкните по оси X правой кнопкой мыши и выберите команду «Формат оси». В открывшемся диалоговом окне (Рис. 39) на вкладке «Шкала» установите минимальное значение – 10, максимальное – 100 и цену основных делений – 10, т.к. наши данные изменяются с шагом 10. В этом же окне на вкладке «Шрифт» можно увеличить размер шрифта и изменить его начертание, например, увеличить до 8 пт и сделать курсивом. На складке «Выравнивание» можно установить вертикальное написание подписей. Аналогичным образом можно - 30 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» отформатировать ось Y. Сделайте это. Вы уже поняли принцип форматирования диаграммы? Тогда увеличьте размер шрифта заголовка, настройте легенду, названий осей. Пример отформатированной диаграммы представлен на рисунке (Рис. 40). Чтобы предсказывать значения отклика – параметра Y на выходе эксперимента от фактора – независимых переменных X на входе в систему (в нашем случае это температура), необходимо знать функциональную зависимость Y = f (X). В Excel имеется возможность автоматического подбора такой функции. Рис. 39 Yэкспер Y=f(x) 12 10 8 Y 6 4 2 100 90 80 70 60 50 40 30 20 10 0 X Рис. 40 Щелкните правой кнопкой мыши по точкам и выберите в контекстном меню «Добавить линию тренда». В открывшемся диалоговом окне (Рис. 41) на закладке «Тип» выберите тип линии тренда. Обычно для описания системы используется полиномиальная линия тренда второго порядка: Y = a0 + a1 * X + a2 * X 2 , (1) где ai – коэффициенты уравнения. При необходимости можно изменить степень до 6. Тогда уравнение примет вид: - 31 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Y = a0 + a1 * X + a2 * X 2 + a3 * X 3 + + a4 * X 4 + a5 * X 5 + a6 * X 6 . (2) Рис. 41 Рис. 42 На вкладке «Параметры» установите переключатели в пункты «показать уравнение на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации» (Рис. 42). Это позволит вам увидеть уравнение и точность аппроксимации наших данных. Выведенные на экран уравнение и точность можно переместить в любое место диаграммы (также как и другие надписи, например, заголовка диаграммы, названия осей, легенду), «схватив» левой кнопкой мыши за рамку. Примерный окончательный вид нашей зависимости представлен на рис. 43. Сохраните результаты упражнения 1, они понадобятся нам позднее (см. ниже Лабораторная работа № 6). y = -0.0054x2 + 0.6014x - 5.9667 R2 = 0.9817 Y=f(x) Yэкспер Полиномиальный (Yэкспер) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X Рис. 43 - 32 - 70 80 90 100 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Упражнение 2 Создание и редактирование поверхностей в документе Excel В предыдущем упражнении мы рассматривали возможности визуализации однопараметрической зависимости (функция зависит только от одной переменной). В реальности, такие простые зависимости встречаются достаточно редко. Чаще приходится сталкиваться с многопараметрическими функциями. Как их визуализировать, рассмотрим на примере двухпараметрической задачи. Пусть у нас имеется уравнение: Z = sin X 2 − Y 2 + 5 ⋅ X ⋅ Y , (3) где X и Y меняются от -5 до 5 с шагом 1. Необходимо построить график поверхности полученных значений Z. Для этого сначала необходимо построить матрицу данных (Рис. 44). () Рис. 44 В ячейку B1 введите первое значение Y = -5. Затем выполните команду «Правка» ⇒ «Заполнить» ⇒ «Прогрессия…». В открывшемся диалоговом окне (Рис. 45) установите: «Расположение» − по строкам, «Шаг:» равный 1 и «Предельное значение:» равное 5. После этого нажмите кнопку Ok. Точно таким же образом Рис. 45 заполняются значения X в столбце А, за тем лишь исключением, что «Расположение» должно быть по столбцам. Выполните это. После того, как значения аргументов введены в таблицу, заполните ячейку B2 формулой для расчета Z (3). Функция Sin находится в категории математические «Мастера функций». - 33 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Не забывайте, что формула должна содержать смешанные ссылки, так как значения X должно постоянно выбираться из столбца А, а значения Y − из строки 1. Для заполнения всей таблицы воспользуйтесь маркером заполнения. Данные для построения поверхности готовы, осталось их только построить на диаграмме. Как и в предыдущем упражнении воспользуемся «Мастером диаграмм» (Рис. 32 – 35). Сначала выделите матрицу значений функции (выделять Рис. 46 значения X и Y не надо!), откройте мастер диаграмм любым известным вам способом и выберите тип диаграммы «Поверхность». Далее построение поверхности от построения графика ничем не отличается. Окончательный вид диаграммы будет примерно таким, как показано на рис. 46. Повернуть или настроить диаграмму можно в диалоговом окне «Формат трехмерной поверхности» (Рис. 47), которое Рис. 47 открывается щелчком правой кнопкой мыши по стенкам поверхности и выбором пункта контекстного меню «Объемный вид…». - 34 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа №5 Упражнение 1 Логические выражения в Excel Введем в ячейку А1 формулу =7>5. Она вернет значение ИСТИНА. Скопируем содержимое А1 в А2 и исправим в А2 формулу: =3>5. Эта формула вернет значение ЛОЖЬ. Правые части обеих формул представляют собой высказывания, т.е. утверждения, относительно которых можно заключить, верны они или нет. Рассмотрим другой пример. Введем в ячейку А4 число 2, а в ячейку В4 формулу =А4>3. Формула возвращает значение ЛОЖЬ. Введем в А4 число 6. Формула возвращает значение ИСТИНА. В В4 записан предикат, т.е. высказывание с переменными (в данном случае переменная одна). В зависимости от значения переменных предикат может принимать значения ИСТИНА и ЛОЖЬ. В этом примере формула как бы дает ответ на вопрос: «Число (или результат вычислений по формуле), хранящееся в ячейке А4, превышает 3?» В зависимости от значения А4 ответ будет ДА (ИСТИНА) или НЕТ (ЛОЖЬ). В формуле =А4>3 ее составные части (А4 и 3) можно считать арифметическими выражениями, только очень простыми. Более сложный пример: =(А4^2-1)>(2*А4+1). В этом выражении скобки можно опустить, потому что арифметические операции имеют более высокий приоритет, чем операции сравнения, но скобки придают формуле наглядность. Операции сравнения сведем в табл. 1. Таблица 1 > больше >= больше или равно < <= меньше или равно меньше = <> равно не равно Обратите внимание, что символ отношения «больше или равно» изображается двумя знаками: > и =. Причина в том, что на клавиатуре нет знака ≥. Высказывание и предикат имеют общее название − логическое выражение. Имеются логические операции, которые позволяют строить сложные логические выражения. Эти операции реализованы в Excel как функции (НЕ, И, ИЛИ). У логических функций аргументы могут принимать только два значения: ИСТИНА и ЛОЖЬ. Функция НЕ может иметь только один аргумент, а функции И и ИЛИ могут иметь два и более аргументов. Пример 1 В ячейке А1 (с именем z) запишите любое число. Выясните, принадлежит ли оно отрезку . Решение. Присвоим ячейке А1 имя z («Вставка» ⇒ «Имя» ⇒ «Присвоить»). Введем в А1 число 3. Для того чтобы z принадлежал отрезку , нужно, чтобы одновременно были истинны два предиката: z ≥ 2 и z ≤ 5 . В ячейке В1 разместим - 35 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» формулу =И(z>=2;z<=5). Для ввода в формулу имени ячейки нажмите F3 для открытия списка имен. В В1 получим значение ИСТИНА. Следует предостеречь от неверного решения: формулы =2<=z<=5. Введите эту формулу в С1 и убедитесь, что она возвращает ЛОЖЬ! Коварство этой, на первый взгляд, такой естественной формулы в том, что Excel ничего не сообщает о ее некорректности. Пример 2 В ячейке А1 (с именем z) записано число. Выяснить, принадлежит ли оно одному из лучей на числовой оси: (-∞,2) или (5,∞). Решение. Для того чтобы z принадлежал хотя бы одному из лучей, нужно, чтобы был истинным хотя бы один из предикатов: z < 2 или z > 5. В ячейке D1 разместите формулу =ИЛИ(z<2;z>5). А1 содержит число 3, поэтому формула возвращает ЛОЖЬ. Задачу можно было решить иначе с учетом того обстоятельства, что на рабочем листе есть формула проверки принадлежности числа z отрезку . Упомянутые два луча составляют на числовой оси дополнение к этому отрезку. Введем в ячейку Е1 формулу =НЕ(В1). Убедитесь, вводя в ячейку А1 различные числа, что формулы в ячейках D1 и Е1 дают идентичные результаты. На практике «в чистом виде» логические выражения, как правило, не используются. Логическое выражение служит первым аргументом функции ЕСЛИ: ЕСЛИ(лог_выражение, значение_если_истина, значение_если_ложь) Во втором аргументе записывается выражение, которое будет вычислено, если лог_выражение возвращает значение ИСТИНА, а в третьем аргументе − выражение, вычисляемое, если лог_выражение возвращает ЛОЖЬ. Пример 3 1. Введем в ячейку A2 формулу, которая возвращает z+1, если z >1, и z в противном случае: = ЕСЛИ(z>1;z+1;z). (В Мастере функций ЕСЛИ находится в категории «Логические», также как функции И, ИЛИ, НЕ.); 2. Если z > 60, то в ячейке В2 выводить сообщение «Превышено пороговое значение», в противном случае выводить z: =ЕСЛИ(z>60;"Превышено пороговое значение";z) Обратите внимание, что текст в формулах вводится в кавычках. 3. Если z ∈ , то возвращать z, если z < 10, то возвращать 10, если z > 25, то возвращать 25. Выражение для этого условия будет выглядеть примерно следующим образом (запишем формулу в С2): =ЕСЛИ(z<10;10;ЕСЛИ(z<=25;z;25)) Теперь попробуйте менять значение z в ячейке А1, следя за тем как меняются значения в ячейках с формулами. - 36 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Упражнение 2 Итоговые функции в Excel Некоторые функции Excel возвращают одно значение, в то время как аргументом имеют блок или несколько блоков. Такие функции будем называть итоговыми. Наиболее часто используемой из таких функций является СУММ − для ее ввода на панели «Стандартная» даже размещена специальная кнопка. Эта функция как бы подводит итог колонке чисел − отсюда и название для всей группы. Эти функции входят в категории «Статистические» и «Математические». Функция СУММ допускает до 30 аргументов. Поэтому с ее помощью можно находить сумму чисел из нескольких блоков. Допустима, например, такая формула =СУММ(В2:В9;12;-4.96;А4:С18). Если в блоке в какой-либо ячейке находится текстовое значение, то оно считается равным нулю. Кроме суммы к итоговым функциям относятся, например: МАКС и МИН − вычисление максимального и минимального значений, СРЗНАЧ − среднее арифметическое значение и т.д. Прочие итоговые функции вы можете найти в «Справке». К итоговым можно отнести функции И и ИЛИ. Пример 4 данные Имеются метеостанции – количество осадков (в мм) (Рис. 48). Необходимо получить суммарное, максимальное, минимальное и среднемесячное количество осадков. Решение. Введите формулы: в В17 =СУММ(В3:В14); в В18: =МАКС(В3:В14); в В19: =МИН(В3:В14); в В20: =СРЗНАЧ(В3:В14). Далее эти формулы скопированы в С17:D20. В блоке Е17:Е20 подведены итоги за три года. В Е17 формула =СУММ(В17:D17), в Е18: т.д. На =MAKC(B18:D18) и Рис. 48 Рис. 49 - 37 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» результаты наложен формат: одна цифра после десятичного разделителя (кнопка «Уменьшить разрядность»). Существует две полезные функции, примыкающие к итоговым: СЧЕТЕСЛИ и СУММЕСЛИ. Их названия показывают, что они считают и суммируют не все данные, а только удовлетворяющие некоторому критерию. Функция СЧЕТЕСЛИ (интервал; критерий) подсчитывает в интервале (т.е. блоке) количество значений, удовлетворяющих критерию. Пример 5 Вычислим количество засушливых месяцев, т.е. месяцев, когда выпадало менее 10 мм осадков (Рис. 49). В ячейке В22 формула =СЧЕТЕСЛИ(В3:В14;"<10"). Критерий взят в двойные кавычки, как текстовая строка. Формула скопирована в C22:D22. В Е22 подсчитана сумма. Функция СУММЕСЛИ(интервал;критерий;сумм_интервал) устроена сложнее. Значения, удовлетворяющие критерию, выбираются из блока, заданного первым аргументом, суммируются соответствующие значения из сумм_интервал, заданного третьим аргументом. Если третий аргумент опущен, то суммируются ячейки в аргументе интервал. Предположим, нужно вычислить суммарные осадки, которые выпали в незасушливые месяцы. Дополним таблицу (Рис. 49). В ячейку В23 введена формула =СУММЕСЛИ(B3:B14;">=10"), далее скопированная в C23:D23. С помощью этой функции можно решить более трудную задачу: каково суммарное количество осадков было в 1993 г. в те месяцы, которые в 1994 г. были засушливыми. Решение дается формулой =СУММЕСЛИ(D3:D14;"<10"; С4:С15), которая возвращает значение 128,6. Поместите ее в ячейку В24. К итоговым можно отнести еще две функции: НАИБОЛЬШИЙ(блок;k) и НАИМЕНЬШИЙ(блок;k). Первая из этих функций возвращает k-e наибольшее значение из множества данных, а вторая − наименьшее. Пример 6 В ячейки A1:D1 введем набор чисел. В блок А2:А5 введем формулы, которые показаны в соседнем столбце Рис. 50 (Рис. 50). Для понимания работы функции важно отметить, что третье наибольшее значение в блоке не 2, как можно было бы подумать, а 6, т.е. совпадает со вторым наибольшим значением. И еще: если в блоке n элементов, то функция НАИБОЛЬШИЙ(блок,n) возвращает минимальное значение, что мы и видим в примере. Обратите внимание, что в А2:А5 получен исходный массив чисел, отсортированный по убыванию. Если в исходном блоке изменить какое-либо число, - 38 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» то блок А2:А5 будет автоматически перестроен. В этом отличие от операции сортировки, которая проводится самим пользователем при необходимости. Обязательно сохраните результаты примеров 4-6, так как мы будем их использовать позднее. Упражнение 3 Табличные формулы в Excel Табличные формулы − очень мощное средство Excel, позволяющее в формулах обращаться с блоками, как с обычными ячейками, давать компактные решения сложных задач. В некоторых задачах вообще невозможно обойтись без табличных формул. Пример 7 Пусть нам известна сумма прихода и сумма расхода. Необходимо вычислить доход как разность этих величин. Введите исходные данные (Рис. 51). Рис. 51 В ячейку D2 можно набрать формулу: =В2-С2, а затем скопировать ее в D3:D5. В этих ячейках появятся формулы =В3-С3 и т.д. Однако здесь фактически из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5. Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора. Создание имен. Для наглядности дадим векторам имена. Выделите диапазон со вторым и третьим столбцами таблицы (В1:С5) и дайте команду меню «Вставка» ⇒ «Имя» ⇒ «Создать». Диапазон В2:В5 получит имя «Приход», а диапазон С2:С5 имя «Расход». Ввод табличной формулы с использованием имен диапазонов. Прежде мы вводили формулу в отдельную ячейку. А сейчас введем ее в диапазон. Подробно опишем шаги. Выделим блок D2:D5. В этом блоке активна ячейка D2. Наберем знак равенства =. Нажмем функциональную клавишу F3. Появится диалоговое окно «Вставка имени». Выберем имя «Приход» и щелкнем Оk. Формула примет вид: =Приход. Наберем знак минус -. Вновь нажмем клавишу F3. В диалоговом окне «Вставка имени» выберем имя «Расход» и щелкнем Ok. Формула примет вид: =Приход-Расход. Нажмем сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=Приход-Расход}. - 39 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Прокомментируем шаги. На третьем и пятом шаге мы выбирали имя из списка имен. Можно было ввести имя непосредственно с клавиатуры, но предложенный метод проще и нет риска ошибиться в наборе имени. На шестом шаге мы нажимаем не Enter, как ранее при вводе формулы, a Shift+Ctrl+Enter (при нажатии клавиши Enter должны быть нажаты обе клавиши Shift и Ctrl). Это очень важно. Если бы мы нажали Enter, то формула была бы введена только в активную ячейку блока D2 (Проверьте!). Фигурные скобки, окружающие формулу, говорят о том, что это табличная формула. Эти скобки нельзя набирать вручную (формула будет воспринята как текст). Ввод табличной формулы. Разумеется, табличную формулу можно вводить и без использования имен. Скопируйте блок А1:С5 в А8:С12. Повторите все шаги. Выделите блок D9:D12. В этом блоке активной ячейкой является D12. Наберите знак равенства =. Выделите блок В9:В12, наберите знак минус -, выделите блок С9:С12, нажмите сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=В9:В12-С9:С12}. Мы получили две идентичные таблицы. Выделение блока с табличной формулой. Выделите одну из ячеек блока и нажмите клавишу F5 (эквивалент пункта меню «Правка» ⇒ «Перейти»). В диалоговом окне щелкните по кнопке «Выделить», установите переключатель «Текущий массив». Изменение табличной формулы. Попытайтесь очистить одну из ячеек, занятую табличной формулой. Например, выделите ячейку D8 и нажмите клавишу Del. В этом случае должно появится сообщение «Нельзя изменять часть массива». Удалить блок можно только целиком. Отредактировать формулу можно так: выделить блок с формулой, нажать функциональную клавишу F2, внести изменения в формулу, нажать сочетание клавиш Shift+Ctrl+Enter. (Попробуйте, например, ввести формулу {=Приход-Расход-1}, потом отмените это.) Коррекция табличной формулы при увеличении блока. Добавьте в обе таблицы на рабочем листе строку с данными: Год - 1996, приход - 240, расход – 200. Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, мы бы поступили просто: скопировали бы формулу из ячейки D5 в D6. Проделаем это для первой таблицы. Вместо ожидаемого 40 получим результат 50, т.е. число из первой ячейки блока с табличной формулой. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим {=В13:В16-С13:С16} – образовался второй блок, что вовсе не входило в наши планы. Удалим формулы в ячейках D6 и D13. Правильное решение для первой и второй таблиц разное. Для первой таблицы изменим именованные блоки (выделим В1:С6 и «Вставка» ⇒ «Имя» ⇒ «Создать», для каждого имени Excel задаст вопрос: «Заменить» существующее определение имени?» Отвечаем «Да»). Выделяем D2:D6, нажимаем клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажимаем клавиши Shift+ Ctrl+Enter. Для второй таблицы выделяем D8:D13, нажимаем клавишу F2 и редактируем формулу. Выделим в формуле подстроку В8:В12 и выделим блок В8:В13, также поступим с блоком С8:С12 либо просто заменим в адресах блоков цифру 2 на цифру 3. Нажимаем сочетание клавиш Shift+Ctrl+Enter. - 40 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Коррекция табличной формулы при уменьшении блока. Теперь мы хотим удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменяем поименованные блоки (в ячейке D6 результат отображается как #Н/Д - недоступно). Выделяем блок с табличной формулой, нажимаем клавишу F2 и добавляем в самое начало формулы апостроф (он расположен на клавише с буквой "Э"). Формула превращается в текст. Вводим этот текст во все ячейки (клавиши Ctrl+Enter). Табличная формула прекратила существование. Очищаем последнюю строку таблицы. Выделяем блок D2:D5, нажимаем клавишу F2, удаляем апостроф, нажимаем клавиши Shift+Ctrl+Enter. Аналогично поступаем со второй таблицей. Как видим, процедура непростая и неприятная. Для решения задачи проще было воспользоваться простыми формулами. Но применение табличных формул, как мы убедимся, дает такие дополнительные возможности, что с неудобствами, связанными с изменениями этих формул, придется смириться. Упражнение 4 Дистрибутивные функции в Excel В Excel можно к блоку применить функцию (большое множество функций Excel), с тем, чтобы она вернула новый блок, содержащий значения функции для элементов исходного блока. Пример 8 Пусть в блоке А1:А4 записаны числа 1, 4, 9, 16. Поместим в B1:В4 табличную формулу {=КОРЕНЬ(А1:А4)}. Будет выведен столбец значений: 1, 2, 3, 4. Можно считать, что функция КОРЕНЬ была применена к вектору из четырех компонент и вернула новый вектор. (Разумеется, тот же результат можно было получить, записав в В1 формулу =КОРЕНЬ(А1) и скопировав ее в блок В2:В4.) Теперь рассмотрим функцию, которую нельзя применять к блоку. Поместим в D1:D2 логические значения ИСТИНА и ЛОЖЬ. В блок Е1:Е2 запишем табличную формулу {=И(D1:D2;”ИСТИНА”)}. Эта формула вернет значение ЛОЖЬ во всех ячейках блока Е1:Е2. Получается, что функцию КОРЕНЬ можно применять к массиву, а функцию И – нет. Функции, которые можно применять к списку, называются дистрибутивными. Продолжим пример с вычислением квадратного корня от элементов блока. Мы хотим вычислить сумму корней ∑ ai . Поместим в ячейку В5 формулу =СУММ(В1:В4). Результат, разумеется, 10. А теперь вычислим эту же сумму, не используя промежуточный блок В1:В4. Поместим в ячейку А5 табличную формулу {=СУММ(КОРЕНЬ(А1:А4))}. Обратите внимание, хотя формула возвращает значение в одной ячейке, она должна вводиться как табличная, т.е. ее ввод заканчивается нажатием комбинации клавиш Shift+Ctrl+Enter. Для сравнения введите в А6 эту формулу как обычную, – она вернет сообщение об ошибке #ЗНАЧ!. - 41 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Итак, к массиву, возвращаемому дистрибутивной функцией, можно применить итоговую функцию, которая возвращает всего одно значение. Но вводить такую формулу нужно как табличную. Пример 9 Рис. 52 Функцию ИЛИ можно использовать в табличных формулах, но как итоговую, т.е. если ИЛИ имеет всего один аргумент и этот аргумент – блок. Пусть в блоке А1:В2 находятся названия планет (Рис. 52). В ячейку D2 введем табличную формулу {=ИЛИ(СОВПАД(А1:В2;D1))}. Функция СОВПАД возвращает логическое значение ИСТИНА или ЛОЖЬ в зависимости от совпадения или несовпадения своих аргументов – текстовых строк. Если бы в А4:В5 была введена формула массива {=СОВПАД(А1:В2;D1)}, то она вернула бы четыре значения (Рис. 52). Функция ИЛИ(А4:В5) возвращает значение ИСТИНА. Эти две формулы мы объединяем в одну табличную формулу, которую и ввели в D2. Пример 10 Вернемся к задаче обработки данных метеостанции (пример 4 и пример 5). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ. Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцы с нормальным количеством осадков. Будем использовать вспомогательный блок F3:H14 тех же размеров, что и блок с исходными данными. В ячейку F3 вводим формулу =ЕСЛИ(И(В3>20;В3<80);1;0) и копируем ее в остальные ячейки блока F3:H14. В блоке выводятся нули и единицы. Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 – выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 – в противном случае. Остается подсчитать сумму таких месяцев. Для этого введем в ячейку F25 формулу =СУММ(F3:F14) и скопируем ее в блок G25:H25. А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу {=СУММ(ЕСЛИ(В4:В15>20;ЕСЛИ(В4:В15<80;1;0);0))} и скопируем ее в C25:D25. (Таким образом, машина, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае – проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном - 42 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» случае – 0). Формула {=СУММ(ЕСЛИ(И(В4>20;В4<80);1;0))} не приведет к успеху, так как функция И не является дистрибутивной). Этот пример очень важен. Он Рис. 53 дает рецепт, как подсчитать в множестве количество элементов, удовлетворяющих определенному критерию. Нужно составить на основе вложенных функций ЕСЛИ индикаторную функцию подмножества и применить к ней итоговую функцию СУММ, введя формулу как табличную. Теперь вычислим суммарное количество осадков, выпавших в эти месяцы Введите в А26 текст «Осадки в нормальные месяцы», в В26 – табличную формулу {=СУММ(ЕСЛИ(В3:В14>20; ЕСЛИ (В3: В14<80;В3:В14;0);0))} и скопируйте ее в C26:D26. В E25 и Е26 введите формулы для суммирования значений в строках (выделите блок В25:Е26 и щелкните кнопку «Автосумма»). Вы получите блок, показанный на рис. 53. Пример 11 В блоке А1:А10 записана числовая последовательность. Проверьте, является ли она возрастающей. Решение. Перейдите на новый лист. Запишите любую последовательность чисел в блок А1:А10. Окончательное решение можно записать одной формулой (поместите его в ячейку A12). {=ЕСЛИ(СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0))=СЧЕТ(А1:А10)1;"возрастающая";"не является возрастающей")} Разберем теперь эту формулу: А2:А10-А1:А9 (т.е., из А10 вычитается А9, из А9 вычитается А8 и т.д.) – образует блок, состоящий из первых разностей элементов исходного блока; ЕСЛИ(А2:А10-А1:А9>0;1;0) – составляет блок из индикаторов положительных первых разностей; СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0)) – считает количество ненулевых элементов в блоке индикаторов; СЧЕТ(А1:А10)-1 – рассчитывает размер блока индикаторов, равный уменьшенному на 1 размер исходного блока; если количество ненулевых элементов в блоке индикаторов равно размеру блока индикаторов, то последовательность возрастающая, иначе – нет. Попробуйте поэтапно строить соответствующие блоки и итоговые функции от них, чтобы добиться ясного понимания, как составлена итоговая формула. - 43 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Упражнение 5 Матричные операции в Excel Простейшие операции, которые можно проделывать с матрицами: сложение (вычитание), умножение на число, перемножение, транспонирование, вычисление обратной матрицы. Пример 12 Сложение матриц и умножение матрицы на число. Сложить матрицы M и N, где − 1 0 4  2 − 3 7 M = и N =   2 − 3 5 .    − 1 5 6 Решение. Введем матрицы М и N в блоки А1:С2 и Е1:G2. В блок А4:С5 введем табличную формулу {=А1:С2+E1:G2}. Обратите внимание, что выделен блок, имеющий те же размеры, что и исходные матрицы. Что произойдет, если перед вводом формулы выделить блок A4:D6? В «лишних» ячейках появится #Н/Д, т.е. «НеДоступно». А если выделить А4:В5? Будет выведена только часть матрицы, без каких-либо сообщений. Проверьте. Использование имен делает процедуру ввода табличной формулы намного проще. Дайте диапазонам А1:С2 и E1:G2 имена М и N соответственно (выполните команду для каждого блока «Вставка» ⇒ «Имя» ⇒ «Присвоить»). В блок E4:G5 введите табличную формулу {=М+N}. Результат, естественно, должен получиться тот же. Теперь вычислим линейную комбинацию матриц 2М-N. В блок А7:С8 введем табличную формулу {=2*М-N}. У Вас должны получиться результаты:  5 − 6 10 1 − 3 11 M +N = и 2 M − N = − 4 13 7  .    1 2 11 Рассмотренные примеры подводят нас к мысли, что обычная операция умножения применительно к блокам не вполне эквивалентна перемножению матриц. И действительно, для матричных операций в Excel предусмотрены функции, входящие в категорию «Математические»: МОПРЕД – вычисление определителя матрицы; МОБР – вычисление обратной матрицы; МУМНОЖ – перемножение матриц; ТРАНСП – транспонирование. Первая из этих функций возвращает число, поэтому вводится как обычная формула. Остальные функции возвращают блок ячеек, поэтому они должны вводиться как табличные формулы. Первая буква «М» в названии трех функций − сокращение от слова «Матрица». Пример 13 Вычислите определитель и обратную матрицу для матрицы - 44 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» − 73 78 24 A =  92 66 25 .   − 80 37 10  Проверьте правильность вычисления обратной матрицы умножением ее на исходную. Повторите эти действия для той же матрицы, но с элементом а33=10,01. Решение. Разместим исходную матрицу в блоке А1:С3. В ячейке В5 поместим формулу для вычисления определителя =МОПРЕД(А1:С3). В блок А7:С9 введем формулу для вычисления обратной матрицы. Для этого выделим блок А7:С9 (он имеет три строки и три столбца, как и исходная матрица). Введем формулу {=МОБР(А1:С3)}. Даже если вы используете Мастер функций, нужно завершать ввод нажатием комбинации клавиш Shift+Ctrl+Enter (вместо щелчка по кнопке «Оk»). Если Вы забыли предварительно выделить блок А7:С9, а ввели формулу в ячейку А7 как обычную формулу ввод Excel (закончив нажатием Enter), то не нужно вводить ее заново: выделите А7:С9, нажмите клавишу F2 (редактирование), но не изменяйте формулу, просто нажмите клавиши Рис. 54 Shift+Ctrl+Enter. Скопируйте блок А1:С9 в блок E1:G9. Чуть-чуть измените один элемент исходной матрицы: в ячейку G3 вместо 10 введите 10,01. Изменения в определителе и в обратной матрице разительны! Этот специально подобранный пример иллюстрирует численную неустойчивость вычисления определителя и обратной матрицы: малое возмущение на входе дает большое возмущение на выходе. Для дальнейших вычислений присвоим матрицам на рабочем листе имена: А1:С3 – А, А7:С9 – Ainv, E1:G3 – АР, E7:G9 – APinv. Чтобы в уже введенных формулах появились эти имена, выделите соответствующие формулы, выберите в меню пункт «Вставка» ⇒ «Имя» ⇒ «Применить», выделите в диалоговом окне нужные имена и щелкните «Оk». Теперь проверим правильность вычисления обратной матрицы. В блок А12:С14 введем формулу {=MУMHOЖ(A,Ainv)}, a в блок E12:G14 – формулу {=МУМНОЖ(АР,АPinv)}. У вас должен получиться результат, как на рис. 54. Как и следовало ожидать, получились матрицы, близкие к единичным. - 45 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Заметим, что набор матричных операций в Excel беден. Если вам нужно серьезно работать с матрицами, лучше прибегнуть к помощи таких математических пакетов как MatLAB (Matrix LABoratory), Mathematica, Derive. - 46 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа № 6 Упражнение 1 Поиск решения В лабораторной работе № 4 мы рассматривали пример автоматического нахождения функциональной зависимости Y = f(X). Напомним, что нахождение подобной зависимости необходимо для предсказания значений отклика – параметра Y на выходе эксперимента от фактора – независимых переменных X на входе в систему (см. Лабораторная работа № 4). В некоторых Рис. 55 случаях представленных в Excel функций бывает недостаточно. Поэтому важно уметь подобрать такую функцию самостоятельно, используя какой-нибудь из математических методов оптимизации, например метод наименьших квадратов. Суть его состоит в том, чтобы минимизировать сумму квадрата разности экспериментальных (Yэкпер) и расчетных (Yрасчет) данных: n ∑ (Yэкпер,i − Y расчет,i) 2 , i =1 (4) где n в нашей задаче было равно 10. Откройте задачу лабораторной работы № 4 и продолжите заполнение таблицы. Экспериментальные Y уже введены. Теперь заполним таблицу расчетными Y. Для этого нам понадобится дополнительная таблица коэффициентов, значения которых мы для начала приравняем к 1 (Рис. 55). Теперь введите формулу полинома второй степени (1) для Yрасчет (Рис. 55). Далее задача заключается в том, чтобы Рис. 56 подобрать коэффициенты уравнения так, чтобы разница между Yрасчет и Yэкспер была минимальной. Для этого нужно ввести формулу - 47 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» расчета квадрата разности (3) и формулу расчета критерия Пирсона для оценки точности нашего расчета (Рис. 56). И та, и другая формулы являются встроенными в Excel и служат примером функций, для которых можно обойтись без ввода табличных формул (см. выше Лабораторная работа № 4). Откройте Мастер функций любым известным вам способом. В категории «Математические» выберите формулу СУММКВРАЗН и нажмите Ok. Во втором окне Мастера функций в Рис. 57 качестве массива_x введите массив Yэкспер, в качестве массива_y – массив Yрасчет и нажмите Ok. Формула для расчета критерия Пирсона находится в категории «Статистические» (функция ПИРСОН). Во втором окне Мастера функций в качестве массива_x также введите массив Yэкспер, в качестве массива_y – массив Yрасчет и нажмите Ok. Чтобы найти значения коэффициентов, в Excel имеется надстройка «Поиск решения», которая позволяет решать задачи отыскания наибольших и наименьших значений, а также решать различные уравнения. Выделите ячейку, где введена формула расчета квадрата разности и выполните команду «Сервис» ⇒ «Поиск решения». Если в меню «Сервис» такая команда отсутствует, то нужно сначала выполнить команду «Сервис» ⇒ «Надстройки» и в открывшемся диалоговом окне поставить переключатель в графе «Поиск решения» (Рис. 57), и только затем выполнить команду «Сервис» ⇒ «Поиск решения». В диалоговом окне «Поиск решения» (Рис. 58) введите параметры: адрес целевой ячейки с подбираемым значением (адрес ячейки с формулой суммы квадрата разности), если вы заранее выделили ее, то адрес помещается автоматически; в поле «Равной:» установите переключатель на «минимальному значению»; - 48 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» в поле «Изменяя ячейки» введите диапазон ячеек изменяемых коэффициентов. Кнопка «Параметры» служит для изменения и настройки параметров поиска. В Рис. 59 их число входят: способ решения задачи, время проведения вычислений и точность результатов. Однако в большинстве случаев достаточно использовать настройки по умолчанию. Поиск решения осуществляется после щелчка по кнопке «Выполнить». Если поиск решения успешно завершен, то результаты вычислений заносятся в исходную таблицу, а на экране появляется диалоговое окно «Результаты поиска решения» (Рис. 59), с помощью которого можно сохранить найденные решения в исходной таблице, восстановить исходные значения, сохранить результаты поиска решения в виде сценария, сформировать отчет по результатам выполнения операции поиска решения. Сравните полученные значения коэффициентов с коэффициентами в уравнении линии тренда. Добавьте расчетные значения Y на график. Для этого перейдите в окно диаграммы, щелкните правой кнопкой мыши в любом ее месте и выберите в контекстном меню команду «Исходные данные». В открывшемся одноименном диалоговом окне (Рис. 60) перейдите на закладку «Ряд» и нажмите кнопку «Добавить». В поле «Имя» щелкните по кнопке свертывания окна, Рис. 60 перейдите на лист с вашими данными, выделите ячейку заголовка столбца Yрасчет и вернитесь в окно с помощью кнопки разворачивания окна. Аналогичным образом Рис. 58 - 49 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» введите «Значения X» (диапазон ячеек со значениями X или температуры) и «Значения Y» (диапазон ячеек со значениями расчетного Y). По окончании ввода нажмите кнопку Ok. Обратите внимание, что точки Yрасчет легли на линию тренда, построенную нами ранее (Рис. 61). В завершение обязательно сохраните свой файл, мы будем его использовать на следующем уроке (см. ниже Лабораторная работа №7). y = -0.0054x2 + 0.6014x - 5.9667 2 R = 0.9817 Y=f(x) Yэкспер Yрасчет Полиномиальный (Yэкспер) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X Рис. 61 - 50 - 70 80 90 100 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа №7 Упражнение 1 Поиск решения двухпараметрической задачи в Excel В предыдущих уроках мы рассматривали возможности обработки и визуализации однопараметрической зависимости (функция зависит только от одной переменной). В реальности такие простые зависимости встречаются достаточно редко. Чаще приходится сталкиваться с многопараметрическими функциями. Как обрабатывать такие зависимости и каким образом их визуализировать, рассмотрим на примере двухпараметрической задачи. Пусть был проведен эксперимент, например, измерили зависимость какого-то параметра от температуры и давления. Средняя температура была равна 100оC. Шаг изменения – 50оС. Среднее давление – 2 атм. Шаг изменения – 1 атм. Такая система будет описываться зависимостью: Y = f (X1, X 2) , (5) являющейся поверхностью, которую часто показывают в виде, подобном контурной карте (Рис. 62). Рис. 62 - 51 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Чтобы найти эту зависимость для нашего случая, воспользуемся заготовкой уроков 4 и 6. Для этого откройте ваш сохраненный файл и перейдите на лист с данными. Щелкните по ярлыку Листа и выберите команду «Переместить/скопировать» (Рис. 63). В открывающемся диалоговом окне (Рис. 64) можно выбрать: куда мы хотим переместить (скопировать) наш лист (в текущую книгу или новую). Выберите название текущей книги; перед каким листом мы хотим поместить текущий лист или его копию. Выберите «(переместить в конец)». Не забудьте поставить галочку «Создать копию», в противном случае лист просто переместиться в конец книги. После чего нажмите Ok. По умолчанию Excel создает копию с именем текущего листа, добавляя в конце в скобках номер копии. Для удобства переименуем его. Для этого щелкните мышкой по ярлыку листа и выберите команду «Переименовать» (Рис. 63); введите новое имя, например, «Эксперимент_2» и нажмите клавишу «Enter». Сначала перестроим таблицу исходных данных, как показано на рис. 65. Выделите две ячейки в верхней части старой таблицы (те, в которых было помещено название параметра «Температура» и ее значение) и выполните команду «Вставка» ⇒ «Ячейки…». При этом откроется диалоговое окно «Добавление ячеек», в котором будет предложено их местоположение (Рис. 66). Поставьте переключатель в положение «ячейки со сдвигом вниз» и нажмите кнопку Ok. - 52 - Рис. 63 Рис. 64 Рис. 65 Рис. 66 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Выделите пустой столбец С (щелкните по заголовку этого столбца) и выполните команду «Вставка» ⇒ «Столбцы». Внесите необходимые изменения в таблицу (Рис. 65). Приведите подобным образом таблицу эксперимента к виду, показанному на рис. 67. Напомним, что заголовки столбцов «Температура» и «Давление» должны вводиться по формулам, чтобы сделать заготовку более универсальной. Рис. 67 Заполним теперь данные таблицы «Эксперимент». Координаты точек 1 – 9 можно вычислить в соответствии с рис. 62 по нижеследующим формулам: № 1 2 3 4 5 6 7 8 9 Темпер. Xср,1-Шаг Xср,1 Xср,1+Шаг Xср,1-Шаг Xср,1 Xср,1+Шаг Xср,1-Шаг Xср,1 Xср,1+Шаг Давл. Xср,2-Шаг Xср,2-Шаг Xср,2-Шаг Xср,2 Xср,2 Xср,2 Xср,2-Шаг Xср,2-Шаг Xср,2-Шаг При вводе формул не забывайте делать постоянными ссылки, чтобы использовать возможность копирования. Значения Yэкспер мы должны взять из эксперимента. Пусть они равны: № точки Yэкспер 1 1 2 7 3 5 4 17 5 25 6 15 Yрасчет должны вычисляться по формуле: Yрасчет = A0 + A1 X 1 + A2 X 2 + A11 X 12 + A12 X 1 X 2 + A22 X 22 . - 53 - 7 3 8 10 (6) 9 4 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Прежде чем вводить формулу (6), необходимо модифицировать таблицу коэффициентов, как показано на рис. 68, введя начальные значения коэффициентов 1. Для подбора функции воспользуемся методом минимизации суммы квадратов разности экспериментальных (Yэкспер) и расчетных (Yрасчет) данных, который мы рассматривали в прошлом уроке. Рис. 68 Формулы расчета квадрата разности и формула расчета критерия Пирсона у нас на листе уже есть. Теперь достаточно поправить в них ссылки и выполнить. Поиск решения выполняется также, как и в случае однопараметрической функции, но поскольку у нас зависимость более сложная, необходимо в диалоговом окне «Поиск решения» открыть подокно «Параметры» (Рис. 69) и установить следующие опции: допустимое отклонение – 1%; «Автоматическое масштабирование»; оценки – «Квадратичные»; разности – «Центральные». Рис. 69 После этого нажать кнопку Ok и в окне «Поиск решения» − «Выполнить». Если удовлетворительная точность не достигнута с первой попытки, операцию поиска решения можно повторить. Наконец, нам осталось только построить поверхность. Для этого сначала на новом листе построим матрицу данных (Рис. 70). Перейдите на новый лист и введите заголовок таблицы. - 54 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Рис. 70 Значения X и Y вычисляются по формулам. Чтобы ввести первое значение для давления введите «=», затем перейдите на лист «Эксперимент_2» и щелкните мышкой ячейке с минимальным значением давления (в нашем случае это 1) и нажмите клавишу «Enter». Те же самые действия необходимо выполнить для ввода минимального значения температуры. Последующие значения температуры и давления вычисляются по формуле: Yi = Yi −1 + Ymax − Ymin , l (7) где Xi, Yi – текущие значения температуры и давления соответственно, Xmin, Ymin – минимальные значения температуры и давления соответственно, Xmах, Ymах – максимальное значение температуры и давления соответственно, l – шаг сетки (пусть он будет равен 10). Введите формулы расчета второго значения давления и температуры. Они будут выглядеть примерно следующим образом: =B4+(Экперимент_2!$G$11-Экперимент_2!$G$3)/10. Для ввода третьего и т.д. значений температуры и давления воспользуйтесь маркером заполнения. Осталось ввести только значения функции (5). Значения коэффициентов возьмите с листа «Эксперимент_2». Не забывайте, что ссылки на коэффициенты должны быть абсолютными, а на значения температуры и давления – смешанными. Формула должна выглядеть примерно следующим образом: =Экперимент_2!$B$7+Экперимент_2!$B$8*$A5+Экперимент_2!$B$9*B$4 +Экперимент_2!$B$10*$A5^2+Экперимент_2!$B$11*$A5*B$4+ Экперимент_2!$B$12*B$4^2 Данные для построения поверхности готовы, осталось их только построить на диаграмме. - 55 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Воспользуйтесь «Мастером диаграмм», выбрав тип диаграммы «Поверхность» (см. Лабораторная работа № 4). Окончательный вид диаграммы будет примерно таким, как показано на Рис. 71. Рис. 71 - 56 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» 1. 2. 3. 4. 5. Библиографический список Фултон, Д. Освой самостоятельно Microsoft Excel 2000. 10 минут на урок. / Д. Фултон. – М.: Издательский дом «Вильямс», 2001. – 224 с. Левин, А.Ш. Excel – это очень просто! / А.Ш. Левин. – СПб.: Питер, 2004. – 74 с. Безручко, В.Т. Практикум по курсу «Информатика». Работа с Windows 2000, Word, Excel: Учеб. пособие. / В.Т. Безручко. – М.: Финансы и статистика, 2003. – 544 с. Лавренов, С.М. Excel: Сборник примеров и задач. / С.М. Лавренов – М.: Финансы и статистика, 2004. – 336 с. Воробьев, Е.С. Основы информатики. Приемы работы в среде MS Office. Учеб. пособие / Е.С. Воробьев, Е.В. Николаева, Воробьева Ф.И., Казан. гос. технол. ун-т. Казань, 2005. – 84 с. - 57 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Содержание Лабораторная работа № 1 ............................................................................................... 3 Упражнение 1. Основные понятия, связанные с работой электронных таблиц Excel.............................................. 3 Упражнение 2. Применение основных приемов работы с электронными таблицами: ввод данных в ячейку. Форматирование шрифта. Изменение ширины столбца. Автозаполнение, ввод формулы, обрамление таблицы, выравнивание текста по центру выделения, набор нижних и верхних индексов............................. 6 Лабораторная работа № 2 ............................................................................................. 10 Упражнение 1. Закрепление основных навыков работы с электронными таблицами, знакомство с понятиями: сортировка данных, типы выравнивания текста в ячейке, формат числа.................... 10 Упражнение 2. Введение понятия «абсолютная ссылка», установка точного значения ширины столбца при помощи команд горизонтального меню. Вставка функции при помощи мастера функций............................................................................. 13 Упражнение 3. Введение понятия «имя ячейки»................................................... 16 Лабораторная работа № 3 ............................................................................................. 19 Упражнение 1. Изменение ориентации текста в ячейке, ознакомление с возможностями баз данных Excel. Сортировка данных по нескольким ключам................................................................... 19 Лабораторная работа № 4 ............................................................................................. 27 Упражнение 1. Создание и редактирование графиков в документе Excel.......... 27 Упражнение 2. Создание и редактирование поверхностей в документе Excel .. 33 Лабораторная работа № 5 ............................................................................................. 35 Упражнение 1. Логические выражения в Excel ..................................................... 35 Упражнение 2. Итоговые функции в Excel............................................................. 37 Упражнение 3. Табличные формулы в Excel ......................................................... 39 Упражнение 4. Дистрибутивные функции в Excel ................................................ 41 Упражнение 5. Матричные операции в Excel ........................................................ 43 Лабораторная работа № 6 ............................................................................................. 47 Упражнение 1. Поиск решения................................................................................ 47 Лабораторная работа № 7 ............................................................................................. 51 Упражнение 1. Поиск решения двухпараметрической задачи............................. 51 Библиографический список.......................................................................................... 57 - 58 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Редактор: Т.М. Петрова Лицензия № 020404 от 6.03.97 г. Подписано в печать Бумага писчая. уч.-изд. л. 2005. Печать Формат 60х84 1/16 усл. печ. л. Тираж 100 экз. Заказ “C” 60 Издательство Казанского государственного технологического университета Офсетная лаборатория Казанского государственного технологического университета 420015, Казань, К. Маркса, 68

САМАРСКАЯ ГОСУДАРСТВЕННАЯ АКАДЕМИЯ ПУТЕЙ СООБЩЕНИЯ

Кафедра информатики

ИНФОРМАТИКА

Табличный процессор MS Excel

Методические указания к выполнению лабораторных работ

для студентов специальности ОПУ всех форм обучения

Составители: Макарова И.С.

Ермоленко Т.И.

Самара 2006

Информатика. Табличный процессор MS Excel. [Текст] : методические указания к выполнению лабораторных работ для студентов специальности ОПУ всех форм обучения. - Часть 2 / cоставители: И.С. Макарова, Т.И. Ермоленко. – Самара: СамГАПС, 2006. – 44 с.

Утверждено на заседании кафедры информатики от 06.04.2006 г., протокол № 8.

Печатается по решению редакционно-издательского совета академии.

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

Использование данных методических указаний предполагает знание студентами основ работы с операционной оболочкой Windows.

Редактор: Е.А. Краснова

Компьютерная верстка: Р.Р. Абрамян

Подписано в печать 15.06.06. Формат 60х90 1/16.

Бумага писчая. Печать оперативная. Усл. п.л. 2,75.

Тираж 200 экз. Заказ № 118.

© Самарская государственная академия путей сообщения, 2006

Введение

Microsoft Excel – достаточно мощный и простой в использовании электронный табличный процессор, предназначенный для решения широкого круга планово-экономических, учетно-статистических, научно-технических, математических и других задач. В основе MS Excel лежит работа с электронными таблицами.

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

Excel поддерживает форматы файлов, помечаемые расширением вида xl*, а собственные документы Еxcel располагаются в файлах, имеющих расширение xls.

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

Лабораторная работа №1. Основы работы с программой MS Excel

Цель работы : познакомиться с основными элементами табличного процессора, приемами ввода информации в таблицы, приемами форматирования

Рис. 1. Окно программы MS Excel

Окно программы Excel содержит все стандартные элементы, присущие окну приложений Windows:

· значок программы;

· строку заголовка;

· строку меню;

· панели инструментов;

· строку состояния;

· полосы прокрутки.

Строка меню Excel отличается от строки меню Word командой Данные (вместо Таблица ). На панели инструментов имеются специальные кнопки для числовых данных – денежный и процентный форматы; разделитель тысяч; увеличение и уменьшение разрядности числа; кнопка для объединения и центрирования текста в группе ячеек.

Ниже панели инструментов располагается Строка формул , которая служит для ввода и редактирования данных в ячейках. В левой части строки формул находится раскрывающийся список – Поле имен , в котором отображается адрес текущей ячейки. В этой же строке во время ввода формул появляются три кнопки для управления процессом ввода.

На пересечении столбца с номерами строк и строки с обозначением столбцов находится кнопка Выделить все , которая служит для выделения всего рабочего листа.

Ниже рабочего поля располагается строка с ярлыками рабочих листов .

Рассмотрим основные понятия MS Excel .

Документ в программе Excel принято называть рабочей книгой, она состоит из совокупности рабочих листов . По умолчанию в каждой книге содержится 3 рабочих листа, но их количество можно изменять от 1 до 255. Рабочий лист имеет табличную структуру и состоит из 65 536 строк и 256 столбцов. Строки нумеруются, а столбцы обозначаются буквами латинского алфавита A,B,C, …, Z,AA, AB,AC,…,BA, BB,…,IV.

Активным листом (текущим листом) рабочей книги называется лист, с которым в данный момент работает пользователь. Ярлычок активного листа всегда имеет более светлый цвет фона, на котором полужирным шрифтом отображается его имя. Щелкая по ярлыкам можно переходить от одного листа к другому в пределах рабочей книги. Для перемещения по листам рабочей книги можно также использовать комбинации клавиш: Ctrl+Page Down и Ctrl+Page Up или группу из четырех кнопок, расположенных в левом нижнем углу рабочего окна программы Excel.

На пересечении строки и столбца располагается ячейка – наименьшая структурная единица рабочего листа. Каждая ячейка имеет адрес , который формируется из имени столбца и номера строки, на пересечении которых она располагается. Так, адрес ячейки С7 означает, что эта ячейка располагается на пересечении столбца С и строки 7 текущего рабочего листа. В тех случаях, когда необходимо сослаться на ячейки, расположенные на других рабочих листах, перед адресом указывается имя рабочего листа, на котором они расположены (например, Лист4!G9).

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

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

Блок ячеек (диапазон) – представляет собой прямоугольную область смежных ячеек. Блок ячеек может состоять из одной ячейки, строки (или ее части), столбца (или его части), а также последовательности строк или столбцов (или их частей). Адрес блока представляет собой комбинацию из адресов левой верхней и правой нижней ячеек блока, разделенных двоеточием. Например, блок с адресом "А3:В5" содержит в себе следующие шесть ячеек: А3, А4, А5, В3, В4, В5.

Excel содержит более 400 встроенных функций. Для облегчения работы с встроенными функциями используется Мастер функций .

ЗАДАНИЕ 1. Знакомство с интерфейсом программы Excel

1. Запустите табличный процессор Excel. Автоматически откроется документ с названием Книга1.

1. Определите количество листов в Книге1. Вставьте через контекстное меню Добавить… - Лист два дополнительных листа. Обратите внимание на названия новых листов и место их размещения.

2. Перетащите ярлычки листов по панели ярлычков так, чтобы установилась порядковая нумерация листов.

3. Сохраните рабочую книгу в своей папке в виде файла с именем tabl.xls .

ЗАДАНИЕ 2. Выделение ячеек, строк, столбцов, блоков и листов

2. Опробуйте различные способы выделения фрагментов электронной таблицы (см. табл.1).

Таблица 1

3. Отмените выделение группы листов, щелкнув мышью по ярлычку любого неактивного листа.

4. Сделайте активным Лист 2 , щелкнув по его ярлычку.

5. Выделите с помощью мыши ячейку С6. Вернитесь в ячейку А1 с помощью клавиш перемещения курсора.

6. Сделайте текущим (активным) Лист 5. Удалите Лист 5 с помощью контекстного меню.

7. Вставьте новый лист с помощью команды меню Вставка . Внимание! Название нового листа – Лист 6.

8. С помощью мыши переместите ярлычокЛиста 6 после ярлычка Листа 4 .

9. Вернитесь к Листу 1. С помощью контекстного меню присвойте ему имя Таблица.

10. Перейдите к Листу 2. Выделите строку 3. Отмените выделение, щелкнув по любой невыделенной ячейке левой клавишей мыши.

11. Выделите столбец D.

12. Выделите вместе столбцы B, C, D. Отмените выделение.

13. Выделите диапазон ячеек (блок) C4:F9 с помощью мыши. Отмените выделение.

14. Выделите блок A2:E11 при нажатой клавише Shift.

15. Выделите одновременно несмежные блоки A5:B5, D3:D15, H12, F5:G10.

16. Выделите весь рабочий Лист 2. Отмените выделение.

ЗАДАНИЕ 3. Ввод данных в ячейки. Форматирование ячеек

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

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

· Для отказа от ввода данных нужно нажать клавишу Esc .

1. В ячейку А1 Листа 2 введите текст Год основания школы №147.

2. В ячейку B1 введите год основания школы 1965.

Важно!

Текстовые данные выравниваются по левому краю ячейки, а числа – по правому краю.

3. Обратите внимание на то, что текст в ячейке А1 "не уместился" и обрезан справа. На самом деле весь текст по-прежнему находится в ячейке А1 , в этом можно убедиться выделив ячейку и посмотрев на строку формул над рабочим листом.

4. Измените ширину столбца А таким образом, чтобы весь текст был виденв ячейке. Для этого перетащите мышью правый разделитель в заголовке столбца (между буквами А и В в заголовках столбцов) или дважды щелкните по разделителю столбца. Для изменения ширины столбца используют также команды меню Формат / Столбец / Ширина (Автоподбор ширины илиСтандартная ширина ).

5. В ячейку А2 введите текст Текущий год.

6. В ячейку В2 введите значение текущего года.

7. В ячейку А3 введите текст Возраст школы.

8. Выделите ячейку В3, введите с клавиатуры формулу для вычисления возраста школы =В2- B1. В ячейке появится числовое значение, отображающее возраст школы в годах.

Важно!

4Ввод формул всегда начинается со знака равенства = .

4Адреса ячеек нужно вводить без пробелов латинскими буквами.

4Адреса ячеек можно вводить в формулы без использования клавиатуры, просто щелкая по ним мышью.

9. Измените ширину первого столбца таким образом, чтобы в ячейке умещалось примерно 10 символов по ширине. Это можно сделать "на глаз" мышью или щелкнув правой кнопкой мыши по заголовку столбца (букве А ) и выполнив команду Ширина столбца… (При этом текст в ячейках первого столбца вновь будет урезан.)

10. Выделите блок ячеек А1:А3 и выполните команду Формат / Ячейки…

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

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

12. Вновь выделите блок ячеек А1:А3 и выполните команду Формат / Ячейки…

13. Перейдите на закладку Шрифт . Установите начертание Полужирный курсив . Самостоятельно поменяйте цвет шрифта.

14. Перейдите на закладку Вид и выберите цвет заливки ячеек.

15. Выделите блок ячеек А1:В3 и выполните команду Формат / Ячейки…

16. Перейдите на закладку Граница . Ознакомьтесь с возможными типами линий. Выберите тип и цвет линии. Затем нажмите Внешние и/или Внутренние для установки границ ячеек (общий вид можно видеть в окне образца). Нажмите ОК .

17. В ячейкуD1 введите текст Год моего рождения.

18. В ячейку Е1 введите год своего рождения.

19. В ячейку D2 введите текст Текущий год.

20. В ячейку Е2 введите значениетекущего года.

21. В ячейку D3 введите Мой возраст.

22. В ячейку Е3 введитеформулу для расчета своего возраста.

23. Определите свой возраст в 2025 году. Для этого замените год в ячейкеЕ2 на 2025. Обратите внимание, что при вводе новых данных пересчет в таблице произошел автоматически .

24. Самостоятельно отформатируйте ячейки и оформите по аналогии с предыдущей таблицей.

25. Переименуйте Лист 2 в Проба.

26. Сохраните работу.

ЗАДАНИЕ 4. Операции перемещения, копирования и удаления содержимого ячеек

1. Выделите ячейку А1. Скопируйте ячейку А1 при помощи правой кнопки мыши или кнопки на панели инструментов Стандартная . Вставьте содержимое ячейки А1 в ячейку А5 при помощи правой кнопки или кнопочной панели. Обратите внимание на то, что скопировалось не только содержимое, но и элементы форматирования ячейки.

2. Скопируйте еще раз ячейку А1 в ячейку А7.

3. Переместите мышью содержимое ячейки А7 в ячейку А9. Для этого выделите ячейку А7, подведите курсор мыши к рамке и перетащите её с нажатой левой кнопкой мыши.

4. Верните содержимое ячейки А9 в ячейку А7.

5. Скопируйтес помощью мыши содержимое ячейки А7 в ячейку А9. Для этого при перемещении необходимо удерживать нажатой клавишу Ctrl .

6. С помощью команд меню Правка / Вырезать , а затем Правка / Вставить переместите содержимое ячейки А5 в ячейку А11.

7. Выделите ячейку А11 и нажмите клавишу Delete . Обратите внимание на то, что содержимое ячейки удалилось, но элементы форматирования сохранились. Для их удаления нужно выполнить команду Правка / Очистить / Форматы.

8. В ячейке А7 измените ориентацию текста так, чтобы текст располагался под углом 45° (команда меню Формат / Ячейки , закладка Выравнивание ).

9. В ячейке А9 расположите текст по вертикали.

10. Сохраните работу.

ЗАДАНИЕ 5. Автозаполнение ячеек

1. Сделайте активным Лист 3 . Переименуйте его в Автозаполнение.

2. В ячейку Е9 введите слово: Среда. Выделите ячейку. Укажите мышью на маркер автозаполнения - квадратик в правом нижнем углу рамки. Нажмите левую клавишу мыши и, удерживая ее нажатой, переместите мышь на несколько строк вниз.

3. Снова выделите ячейку Е9 и протащите ее за маркер на несколько столбцов вправо.

4. Повторите операцию перетаскивания ячейки Е9 с помощью маркера еще два раза - вверх и влево.

5. Проанализируйте результаты и очистите лист. Для этого нажмите пустую кнопку в левом верхнем углу рабочего листа и нажмите клавишу Delete.

6. В ячейку А1 введите число 1. Протащите его за маркер вниз до 10-ой строки. Проанализируйте результат.

7. В ячейку В1 введите число 1.

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

9. Выделите блок ячеек В1:В2 , протащите его за маркер на 10 строк вниз. Проанализируйте результат.

10. В ячейку С3 введите число 1.

11. Протащите его за маркер правой кнопкой мыши на 10 строк вниз. Отпустите левую кнопку мыши, при этом появится контекстное меню. Выберите в меню команду Прогрессия…

12. В открывшемся диалоговом окне Прогрессия установите тип - Арифметическая , шаг - 2 . Нажмите ОК

13. В ячейку D1 введите текст: Январь. Выделите ячейку и протащите за маркер на 12 строк вниз.

14. В ячейку Е1 введите текст ВАЗ 2101. Протащите его за маркер на 12 строк вниз. Проанализируйте полученные результаты.

15. В ячейку F1 Копировать ячейки . Проанализируйте полученные результаты.

16. В ячейку G1 введите текст ВАЗ 2101. Протащите его за маркер правой кнопкой мыши на 12 строк вниз. В открывшемся контекстном меню выберите команду Заполнить . Проанализируйте полученные результаты.

17. Сохраните полученные результаты.

ЗАДАНИЕ 6. Создание списка автозаполнения

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

1. Сделайте активным лист Автозаполнение.

2. Выполните команду меню Сервис / Параметры .

3. Перейдите на закладку Списки.

4. Щелкните по строке Новый список в поле Списки . При этом в поле Элементы списка появится курсор текста.

5. Наберите с клавиатуры фамилии 10 студентов из вашей группы (после набора каждой фамилии нажимайте клавишу Enter ). После окончания набора нажмите кнопку Добавить . Набранный список окажется в поле Списки . Нажмите ОК .

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

7. Для редактирования списка снова выполните команду меню Сервис / Параметры и перейдите на закладку Списки.

8. В поле Списки выделите созданный вами список (он появится и в поле Элементы списка в правой части окна). Удалите первую фамилию и введите вместо неё фамилию Бармалеев .

9. Нажмите кнопку Добавить , а затем ОК .

10. Список в столбце Н не изменился. Подумайте, почему. Что нужно сделать, чтобы список обновился? Запишите ответ на этот вопрос в ячейке А15 .

11. Покажите результат преподавателю.

12. Удалите созданный вами список из перечня списков.

13. Сохраните работу.

ЗАДАНИЕ 7. Составление расписания

1. Сделайте активным Лист 4. Переименуйте его в Расписание.

2. В ячейку A1 введите текст Расписание занятий группы № (укажите номер своей группы) на текущую неделю.

3. В ячейки A3-A6 введите часы занятий (8:30 – 10:00, 10:15 - 11:45 и т.д.)

4. В ячейки B2 - F2 введите названия дней недели (используйте маркер автозаполнения).

5. Заполните таблицу названиями предметов, используя приемы копирования.

6. Выделите ячейки первой строки A1 – F1 и выполните объединение при помощи команды меню Формат / Ячейки (закладка Выравнивание ) или при помощи кнопки Объединить и поместить в центре.

7. Оформите заголовок таблицы при помощи команды Формат / Ячейки.

8. Оформите основное поле расписания, используя границы и заливки.

9. Сохраните работу.

10. Покажите работу преподавателю.