Лабораторные работы по excel. Лабораторная работа по информатике на тему: excel Информатика лабораторные работы в ms excel

РЕДАКТОР ЭЛЕКТРОННЫХ ТАБЛИЦ MICROSOFT EXCEL

Цель работы:

Изучить возможности редактора электронных таблиц Excel 2007.

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

Задание:

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

Изучить порядок выполнения работы.

Методические указания

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

Существует несколько разных подходов к использованию программы Ex сel . Они различаются тем, какие применяются средства и какой результат достигается. Основное назначение программы состоит в автоматизации вычислений в числовых таблицах, когда изменение значения в одной ячейке автоматически приводит к изменению данных в других ячейках, связанных с ней. Такой стиль работы характерен для экономистов, бухгалтеров, работников банковской сферы и руководителей, отвечающих за развитие предприятий. Он основан на том, что в ячейках могут стоять не только числа, но и формулы. Если в ячейке находится формула, то в качестве числового значения ячейки на экране отображается результат расчета по этой формуле. Когда изменяются значения в ячейках, входящих в формулу, изменяется и результат расчета по формуле.

Кроме простейших арифметических формул в ячейках можно использовать математические функции и даже микропрограммы, написанные на языке VBA (Visual Basic for Applications – Visual Basic для приложений). Этот уровень использования Excel характерен для научных кругов. Excel является идеальным средством для проведения статистических расчетов и для обработки результатов экспериментов, для подготовки графиков и диаграмм.

ЭЛЕМЕНТЫ ОКНА ЕХСЕL

Чтобы запустить Ехсе l , следует зайти в меню Пуск , в подменю Все программы, открыть программную группу Microsoft Office , а затем выбрать пункт Microsoft Office Excel 2007 .

После запуска редактор Ехсеl автоматически откроет пустую книгу с названием Книга1 , которое будет отображено в Строке заголовка, расположенной в верхней части окна.

В отличие от предыдущих версий Ехсе l 2007 имеет несколько измененный интерфейс (рис. 1). Как и в редакторе Word , здесь имеется:

кнопка Office - служит для вывода списка возможных действий с документом (открытие, сохранение, печать и т.п.), а также для настройки параметров Ехсеl .

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

Строка состояния

Рис.1 Интерфейс Excel

Остальные элементы интерфейса Ехсеl остались прежними. Кратко рассмотрим их назначение.

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

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

Поле имени - это поле, расположенное слева на строке формул, в нем выводится имя активной ячейки (например, А1) или выделенного объекта (например, Диаграмма 1). В этом поле также можно присвоить имя ячейке или диапазону ячеек/

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

Строка состояния расположена в нижней части окна Ехсеl . В левой ее части отображается название выполняемой операции (открытие или сохранение файла, копирование ячеек или запись макроса и т.д.). Также здесь может выводиться подсказка, например, при нажатии на границе выделенного блока ячеек отображается подсказка, как перетащить данный блок; при нажатии на маркере заполнения (выводится подсказка, как заполнить ячейки рядом данных, и т.п.) Правая часть строки состояния содержит ярлыки переключения режимов просмотра документа, кнопку Масштаб, которая открывает одноименное диалоговое окно для выбора масштаба отображения документа, и панель масштабирования, на которой с помощью бегунка можно вручную уменьшать и увеличивать масштаб. Можно также воспользоваться кнопками Уменьшить или Увеличить , при нажатии на которые масштаб уменьшается или увеличивается с шагом 10%.

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

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

Работа с листами

Каждая рабочая книга по умолчанию содержит три листа со стандартными названиями; Лист1 , Лист2 , Лист3. Выбор того или иного листа осуществляется с помощью ярлычков листов в левом нижнем углу рабочей области. По умолчанию для текущего листа ярлычок отображается более светлым фоном, а для всех остальных - темным. Чтобы выбрать лист, следует щелкнуть по его ярлычку.

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

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

Примечание. Количество листов, которое имеет новая книга, по умолчанию выставляется при помощи опции Число листов, расположенной на странице Основные окна Параметры Ехс el , которое вызывается одноименной командой из меню кнопкой Office .

Рис. 2 Элементы управления ярлычками

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

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

СОЗДАНИЕ ТАБЛИЦЫ

Ввод данных

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

Имя ячейки (адрес ячейки) в Ехсеl формируется по аналогии с именованием клеток на шахматной доске: по имени столбца и строки, на которых расположена ячейка. Так, ячейка С3 находится на пересечении столбца С и 3-й строки.

Примечание . Ссылки могут иметь так называемый стиль R 1С1, где R 1 - строка 1, а С1- столбец 1. Переключение между стилями осуществляется при помощи опции Стиль ссылок R 1С1, расположенной на странице Формулы окна Параметры Ехсе1 , которое вызывается одноименной командой из меню кнопки Office .

а

б

в

Рис. 3. Ввод текста:

а – просмотр содержимого ячейки В2;

б – переход на пустую ячейку С2; в – ввод текста в ячейку С2

Ссылаться можно как на отдельные ячейки, так и на диапазоны прямоугольные блоки) ячеек. Когда в диапазон входят смежные ячейки, например А1, А2 и АЗ или А1, В1 и С1, такой диапазон обозначается в формуле при помощи ссылок на первую и последнюю его ячейки, между которыми ставится знакдвоеточия “:” (А1:А3 и А1:С1 соответственно). Если же ячейки диапазона являются несмежными, т. е. они были выделены с помощью клавиши Ctrl , то ссылки на вес ячейки диапазона перечисляются в формуле через точку с запятой “;” (А1;А3;С1).

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

При открытии нового документа автоматически активной устанавливается ячейка А1, она обрамляется черной рамкой. И если сразу же начать вводить текст, он отобразится в этой ячейке. Чтобы ввести текст в другую ячейку, например А2, необходимо ее активизировать, т.е. щелкнуть мышью по этой ячейке либо установить в нее курсор, произведя двойной щелчок (ссылка на активную ячейку отображается в поле имени). Далее следует ввести данные и завершить ввод нажатием клавиши Tab , в результате чего курсор переходит на соседнюю ячейку справа - В2.

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

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

Если информация уже введена в ячейку и требуется лишь добавить или скорректировать ранее введенные данные (например, если в ячейке В2 следует ввести не Наименование, а Наименование товара), необходимо выполнить такие действия:

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

Установить курсор в то место, где нужно добавить текст, т.е. вконец слова Наименование, нажать клавишу Пробел и ввести слово товара.

Для подтверждения внесенных изменений нажать клавишу Enter или Tab либо кнопку Ввод в строке формул.

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

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

ВЫДЕЛЕНИЕ ЭЛЕМЕНТОВ ТАБЛИЦЫ

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

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

Выделение строк и столбцов

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

Теперь, когда нужные столбцы выделены, можно зайти на вкладку ленты Главная и в группе Ячейки выполнить команду Столбец  Автоподбор ширины столбца. В результате Ехсеl автоматически подберет необходимую ширину для каждого выделенного столбца таблицы.

В случае, если надо выделить все ячейки листа, достаточно нажать кнопку на пересечении названий строк и столбцов. Выделение несмежных (расположенных отдельно) столбцов или строк осуществляется, как и несмежных ячеек (см. подраздел «Выделение несмежных ячеек»), с использованием клавиши Ctrl .

Выделение смежных ячеек

а

б

Рис. 4. Выделение смежных ячеек:

а – в двух столбцах; б – в одном столбце

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

Это можно осуществить следующим образом: подвести указатель к угловой ячейке выделяемого диапазона, например D 3, нажать левую кнопку мыши и, удерживая ее нажатой, перетащить указатель к диагонально противоположной ячейке блока Е7.

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

Выделение несмежных ячеек

Для того чтобы выделить несмежные ячейки, вначале необходимо выделить первый диапазон (в данном случае А3:А7, здесь двоеточие - оператор диапазона), затем нажать клавишу Ctrl и, удерживая ее, выделить остальные ячейки (т.е. диапазон D 3:Е7). После того как будут выделены все нужные ячейки, к ним можно применить операцию выравнивания.

КОПИРОВАНИЕ И ПЕРЕМЕЩЕНИЕ ЯЧЕЕК

В редакторе Ехсеl копирование и перемещение данных осуществляется стандартным для Windows способом, который состоит из следующих этапов:

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

Скопировать (переместить) выделенный блок в буфер обмена (например, посредством кнопки Копировать (Вырезать ), Буфер обмена на вкладке Главная.

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

Вставить находящуюся в буфере информацию в место расположения курсора нажатием кнопки Вставить, расположенной в группе команд Буфер обмена.

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

Маркер заполнения

а

б

Рис. 5. Копирование содержимого ячеек:

а – выделение исходного текста; б – результат копирования

Кроме того, копирование и перемещение можно осуществлять путем перетаскивания с помощью мыши. Для этого следует выполнить такие действия:

Выделить ячейку или блок ячеек.

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

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

Отпустить кнопку мыши, а затем клавишу Ctrl .

При этом все имеющиеся в области вставки данные будут заменены новыми.

Использование специальной вставки

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

В приведенной на рис. 6 таблице требуется добавить значения столбца 3-я бригада к значениям столбца Заготовка яблок, т. Для этого необходимо сделать следующее:

Скопировать значения диапазона D 2:D 5 в буфер обмена.

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

В открывшемся одноименном диалоговом окне (рис. 7) в поле Операция выбрать пункт сложить.

Нажать кнопку ОК.

Рис. 6 Добавление данных

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

Как видно на рис. 7, окно Специальная вставка позволяет копировать различные сложные элементы:

группа Вставить определяет объект копирования;

группа Операция при необходимости назначает математическую операцию, которую можно применить к копируемым данным;

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

опция транспонировать служит для отображения указанной в области копирования строки в столбец и соответственно столбца копируемой области - в строку (область вставки не должна перекрывать область копирования).

Например, чтобы транспонировать строку, в которой перечислены месяцы отчетного периода (диапазон В1:Е1), в столбец (диапазон А2:А5), следует выполнить такие действия:

Рис. 7. Диалоговое окно Специальная вставка

Рис. 8 Результат сложения

Скопировать значения исходного диапазона В1:Е1 в буфер обмена.

Установить курсор в верхнюю левую ячейку области вставки А2 и активизировать в контекстном меню команду Специальная вставка.

В открывшемся одноименном диалоговом окне включить опцию транспонировать и нажать ОК.

В результате скопированная строка отобразится в столбце, как показано на рис. 9

Рис. 9. Результат транспонирования

Ввод данных в несколько ячеек одновременно

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

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

Ввести необходимую информацию (рис. 10а).

Нажать сочетание клавиш Ctrl + Enter .

В результате все выделенные ячейки будут содержать одинаковое значение (рис. 10б).

Рис. 10 Ввод данных в несколько ячеек одновременно:

а – ввод необходимой информации;

б – одновременное копирование во все выделенные ячейки

Заполнение ячеек копированием

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

Ввести значение в некоторую ячейку.

Нажать и удерживать клавишу Ctrl .

Захватить маркер заполнения левой кнопкой мыши и протащить по заполняемым ячейкам.

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

ДОБАВЛЕНИЕ СТРОК И СТОЛБЦОВ

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

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

Вызвать контекстное меню нажатием правой кнопки мыши.

В появившемся списке команд выбрать команду Вставить, после чего появится новая строка (столбец).

Примечание. Если требуется вставить сразу несколько строк (столбцов), следует выделить те строки (столбцы), перед которыми нужно вставить новые. При этом количество вставляемых строк (столбцов) соответствует количеству выделенных.

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

Можно воспользоваться другим методом выполнения вставки ячейки, строки или столбца - на вкладке Главная в группе Ячейка открыть пункт Вставить и выбрать в нем одну из команд: Вставить ячейки, Вставить строки на лист или Вставить столбцы на лист соответственно.

РАБОТА С ФОРМУЛАМИ И ФУНКЦИЯМИ

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

Формулой в Ехсеl называется последовательность символов, которая начинается со знака равенства (=) и содержит вычисляемые элементы (операнды) и операторы.

Операндами могут быть:

постоянные значения;

имена;

функции.

Существуют четыре вида операторов:

арифметические;

операторы сравнения;

текстовый оператор «&», который используется для обозначения операции объединения нескольких последовательностей символов в одну;

адресные операторы.

Операторы всех перечисленных разновидностей приведены ниже (табл. 1-3).

Таблица 1 Арифметические операторы

Арифметические операторы

Операторы сравнения

Оператор

Значение

Оператор

Значение

Сложение

Равно

Вычитание

Больше

Умножение

Меньше

Деление

Больше или равно

Процент

Меньше или равно

Возведение в степень

Не равно

Таблица 2 Операторы сравнения

Оператор

Значение

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

Оператор объединения, который ссылается на объединения ячеек диапазонов

(пробел)

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

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

Таблица 3. Приоритет операторов

Оператор

Описание

Оператор

Описание

Получение диапазона ячеек

Возведение в степень

(пробел)

Пересечение диапазонов

* и /

Умножение и деление

Объединение диапазонов

И -

Сложение и вычитание

Смена знака выражения

Объединение текстовых строк

Вычисление процента

= < > <= <= <>

Сравнение данных

Рис. 11 Элементы формулы

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

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

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

Ввести знак равенства (=) и адреса ячеек с арифметическими операторами (рис. 11).

Нажать клавишу Enter .

ИСПОЛЬЗОВАНИЕ ССЫЛОК

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

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

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

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

Например, если в ячейке А3 была записана формула =А1*А2, то при копировании содержимого АЗ в ячейки ВЗ и СЗ новые формулы с обновленными ссылками примут следующий вид: = В1*В2, =С1*С2 (рис. 12а).

Кроме относительных ссылок, в редакторе Excel часто используются абсолютные ссылки , где кроме названия столбца и номера строки используется специальный символ «$», который фиксирует часть ссылки (столбец, строку) и оставляет ее неизменной при копировании формулы с такой ссылкой в другую ячейку. Обычно абсолютные ссылки указывают на ячейки, в которых содержатся константы, используемые при вычислениях.

Рис. 12. Использование ссылок

а – относительных; б – абсолютных

Например, если необходимо зафиксировать в формуле =А1*В1 значение ячейки А1 (рис. 12 б), которое не должно изменяться в случае копирования данной формулы, то абсолютная ссылка на эту ячейку будет иметь следующий вид: $А$1. Таким образом, при копировании формулы из ячейки В2 в ячейку С2 формула примет вид =$А$1*С1.

Выделить ячейку с формулой.

Нажатием клавиши F 4 выбрать требуемый тип ссылки.

Последовательность изменения типов ссылок для ячейки А1 при использовании клавиши F 4 такая:

А$ 1 - изменяемый столбец и неизменяемая строка;

$А1 - неизменяемый столбец и изменяемая строка;

путем непосредственного ввода ссылок с клавиатуры (вводятся латинскими буквами), что часто используется при редактировании формул;

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

Второй способ включает в себя следующие действия:

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

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

Нажать клавишу Enter .

ПОНЯТИЕ ФУНКЦИИ В ЕХСЕL

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

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

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

Математические, финансовые и другие функции

Для удобства работы пользователя при построении формул функции в Ехсеl разбиты по категориям: функции управления базами данных и списками, функции даты и времени, финансовые, статистические, текстовые, математические, логические (рис. 13).

Рис. 13. Панель Библиотека функций на вкладке Формулы

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

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

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

В Ехсеl широко представлены математические функции, в частности помимо действий с числами можно выполнять операции округления.

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

Правила синтаксиса при записи функций

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

После этого вводится имя функции и сразу за ним - список аргументов в круглых скобках. Аргументы отделяются друг от друга точкой с запятой «;». Скобки позволяют Ехсеl определить, где начинается и где заканчивается список аргументов (рис. 14).

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

Рис. 14. Запись функций

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

Например, в формуле, отображенной на рис. 15, осуществляется суммирование ячеек В2, ВЗ, В4, В5 и Е6.

Рис.15. Суммирование ячеек

Рассмотрим работу функции ОКРУГЛ(арг1;арг2), которая округляет число до заданного количества знаков после запятой и имеет два аргумента:

арг1 - адрес ячейки с числом (или само число), которое нужно округлить;

арг2 - количество цифр после запятой у числа после округления.

Чтобы округлить число 2,71828, находящееся в ячейке А1, с точностью до одного, двух или трех знаков после запятой и записать результаты вычислений соответственно в ячейки В1, С1 и D 1, необходимо действовать следующим образом:

Ввести число 2,71828 в ячейку А1.

Ввести в ячейки В1, С1 и D 1 такие формулы (рис. 16): =ОКРУГЛ(А1;1)

ОКРУГЛ(А1;2)

ОКРУГЛ(А1;3)

Рис. 16. Результат использования функции округления

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

СУММ(ОКРУГЛ(А1;2);ОКРУГЛ(А2;2))

Здесь функция ОКРУГЛ является вложенной. Ехсеl позволяет использовать в формулах не более семи уровней вложенности функций.

В Ехсеl существуют функции, которые не имеют аргументов. Примерами таких функций являются ПИ (возвращает значение числа  , округленное до 15 знаков) или СЕГОДНЯ (возвращает текущую дату). При использовании подобных функций следует в строке формул сразу после названия функции ставить круглые скобки. Другими словами, чтобы получить в ячейках значение числа  или текущую дату, нужно ввести формулы такого вида:

ПИ()

СЕГОДНЯ()

ЗАДАНИЯ К ЛАБОРАТОРНОЙ РАБОТЕ

Вариант 1

1. На первом листе открытой книги набрать следующую таблицу

Фамилия И.

Алгебра

Геометрия

Общ. пок-ль

Кол-во оценок

Баллы

Средний балл

Кол-во оценок

Баллы

Средний балл

Иванов М.

Петров Д.

Сидоров В.

3,571428571

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

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

4. В столбце Средний балл осуществить округление числа с точностью до двух знаков после запятой.

6. Переименовать лист1 в лист Математика.

7. На втором листе создать аналогичную таблицу с блоком гуманитарных дисциплин (н-р, Литература, История ).

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

Вариант 2

1. На первом листе открытой книги набрать таблицу, содержащую данные о ДТП с участием детей за январь месяц 2008/2009 гг.

Наименование ОВД

Всего

Погибло

Ранено

ДТП

2008

2009

2008

2009

2008

2009

ГУВД по г.Тамбову

3

3

Жердевский РОВД

Кирсановский ГРОВД

1

2

Котовский ГОВД

Мичуринское ГУВД

Мичуринский РОВД

1

2

Моршанский ГОВД

1

1

Моршанский РОВД

Рассказовский ГРОВД

1

1

Бондарский РОВД

2. На втором листе создать аналогичную таблицу за февраль месяц 2008/2009 гг.

Наименование ОВД

Всего

Погибло

Ранено

ДТП

2008

2009

2008

2009

2008

2009

ГУВД по г.Тамбову

4

4

Жердевский РОВД

Кирсановский ГРОВД

Котовский ГОВД

Мичуринское ГУВД

2

2

Мичуринский РОВД

1

1

Моршанский ГОВД

1

1

Моршанский РОВД

Рассказовский ГРОВД

Бондарский РОВД

Переименовать первый лист книги Excel лист1 в 01 , а лист2 –в 02 .

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

Обозначить лист3 как лист с названием +2 .

Вариант 3

1. На первом листе открытой книги набрать таблицу с заголовком Платежное извещение

Адрес: ул. Пролетарская, 11, кв. 067

Лицевой счет

2234567654

Период

янв.08

Всего к оплате

Добр. страх жилья:

23,35

Всего со страховкой:

Вид платежа (ед. изм.)

Тариф

Объем

Начислено по тарифу

Сод. и рем. Жил. (м2)

4,33

46,7

Отопление (м2)

23,68

46,7

Газ (плиты) (чел)

Водоснабжение (чел.)

84,27

Водоотведение (чел.)

58,16

Гор. Водоснабж. (чел.)

150,73

Вывоз ТБО (чел.)

20,13

Домофон

Всего к оплате:

Приборы

На день

Предыдущее

Кол-во (кВт.)

учета

выписки

Свет/1/

3200

3050

В основную таблицу в столбец Вид платежа добавить строку Электроэнергия (кВт) , со значением тарифа равным 2,05р.

Записать посчитанное количество киловатт в строку Электроэнергия (кВт) , осуществив связь между этими ячейками;

Значения столбца Начислено по тарифу получить с помощью формулы (необходимо перемножить значения в столбцах Тариф и Объем ).

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

Вариант 4

1. На первом листе открытой книги Excel набрать таблицу по продажам телевизоров

Модель

Цена

Продано, шт.

Доход, руб.

Panasonic TX-R32LM70

25 848,00р.

2

51 696,00р.

Panasonic TX-R32LX70

33 084,00р.

3

99 252,00р.

Panasonic TX-R32LX700

44 604,00р.

1

44 604,00р.

Итого:

195 552,00р.

3. Переименовать лист1 в Panasonic .

В отдельной от таблице ячейке оформить значение курса евро:

курс евро

36,20р.

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

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

Модель

Цена

Продано, шт.

Доход

Philips 20PF4121

10 980,00р.

4

43 920,00р.

Philips 20PF5120

16 812,00р.

5

84 060,00р.

Philips 20PF5123

11 376,00р.

1

11 376,00р.

Итого:

139 356,00р.

Вариант 5

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

Оформить следующую таблицу, введя фамилии и величины оклада в рублях.

Ввести количество детей в столбец число детей .

Фамилия

Сумма к

число

Сумма за год

Оклад

Налог

выдаче

выплат

1

Морыженков

15000

2

Соседов

14900

3

Семёнов

13780

4

Короленко

16200

5

Стенбок

17560

6

Мускатин

12870

7

Гераськин

18430

8

Кочеев

15555

В отдельной от таблице ячейке оформить значение Начисление на детей.

Начисление на детей

153р

6. На лист2 оформить следующую таблицу, осуществив связь с таблицей, расположенной на первом листе через столбец Фамилия .

Фамилия,

имя отчество

Сумма

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

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

Как называется документ в Excel и из чего он состоит?

Как добавить новый лист в книгу? Как переименовать лист?

Что такое ячейка?

Из чего состоит адрес ячейки?

Что такое активная ячейка?

Что такое абсолютная и относительная адресация?

С какого символа начинают ввод формулы в ячейку?

Что такое Мастер функций, как он работает?

Как скопировать или переместить ячейку?

Как отредактировать содержимое ячейки?

Что такое автозаполнение и как оно выполняется?

Как удалить (вставить) строку (столбец)?

Что такое автосуммирование?

Список используемой литературы

Глушаков, С. В. Microsoft Office 2007. Лучший самоучитель / С.В. Глушаков, А.С. Сурядный. – изд. 3-е доп. и переработ. – М.: АСТ: АСТ МОСКВА: Владимир: ВКТ, 2008. -446. c . (Учебный курс).

Глушаков, С. В. Microsoft Excel 2007. Лучший самоучитель / С.В. Глушаков, А.С. Сурядный. – изд. 2-е доп. и переработ. – М.: АСТ: АСТ МОСКВА:, 2008. -416 c . - (Учебный курс).

С. Симонович, В. Мураховский. Популярный самоучитель работы на компьютере – М.: “Техбук”, 2006. – 576 с.

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

Информатика, кибернетика и программирование

Заполните диапазон А1:F10 данными по образцу приведенному на рис. Рис.а Рис. После преобразования в таблицу диапазон представлен на рис.

Лабораторные работы в MS Excel 2007

(часть 2 основная самостоятельная)

Задание № 1. Таблицы MS Excel 2007. 2

Задание № 2. Условное форматирование. 3

Задание № 3. Организация таблиц. 5

Задание № 4. Функции. 7

Задание № 5. Диаграммы. 11

Задание № 1. Таблицы MS Excel 2007.

Цель : Знакомство с возможностями таблиц - списков MS Excel

Темы: Создание «таблиц», работа с «таблицами», сортировка и фильтрация с использованием раскрывающихся списков в заголовках столбцов .

1 . Заполните диапазон А1: F 10 данными по образцу, приведенному на рис.2.2.а, или воспользуйтесь результатами предыдущего занятия и сохраните созданный файл.

1.1. Озаглавьте столбцы.

1.2. Заполните диапазон A 2: D 10.

1.3. Формулы в диапазон E 2: F 10 вводить не надо.

1.4. Одну из строк диапазона сделайте дублирующей любую другую строку диапазона.

Рис.2.2.а	Рис.2.2.б

2 . Преобразуйте диапазон в таблицу.

2.1. Установите курсор внутрь диапазона.

2.2. Выполните команду Вставка – Таблицы – Таблица и в диалоговом окне Создание таблицы проверьте расположение данных таблицы и нажмите ОК.

После преобразования в таблицу диапазон представлен на рис.2.2.б.

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

3.1. Убедитесь в возможности прокрутки строк таблицы при сохранении на экране заголовков столбцов таблицы.

3.2. Воспользуйтесь командой Сервис – Удалить дубликаты и проследите за результатом.

3.3 Воспользуйтесь командой Параметры стилей таблиц и предложенными командами-флажками для применения особого форматирования для отдельных элементов таблицы.

3.4. Воспользуйтесь командой Стили таблиц – Экспресс-стили и примените один из них.

3.5. Удалите из таблицы одну из строк.

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

4 . Познакомьтесь с особенностями ввода формул в таблицу.

4.1. Добавьте в таблицу еще один столбец справа от столбца Стоимость и озаглавьте его Стоимость 1 .

4.2. В произвольную ячейку столбца Стоимость введите вручную формулу, обеспечивающую умножение количества продукции на ее цену, например, в ячейку Е6 может быть введена формула = C 6* D 6. Обратите внимание на то, что формула распространилась на все остальные ячейки столбца таблицы.

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

Убедитесь в том, что в результате во всех ячейках столбца Стоимость 1 будет записана одинаковая формула =[Количество]*[Цена].

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

4.4. Дайте имя ячейке А15, в которой находится коэффициент, влияющий на комиссионный сбор, например, komiss . Для этого выберите команду Формулы – Определенные имена – Присвоить имя, предварительно активизируйте ячейку А15 . Заполните формулами столбец Комисс. сбор, используя Автозаполнение формул.

Познакомьтесь с управлением именами с помощью Диспетчера имен . Активизируйте его командой Формулы – Определенные имена – Диспетчер имен.

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

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

6.1. Отсортируйте таблицу по наименованию продукции (в алфавитном порядке).

6.2. Отсортируйте таблицу в порядке убывания цены на продукцию.

6.3. С помощью фильтрации найдите данные таблицы для бетона и дверей.

6.4. Рассмотрите возможности Текстовых , Числовых фильтров и Фильтров по дате (добавьте в конец таблицы столбец с датами поступления товаров на склад).

Задание № 2. Условное форматирование.

Цель : Знакомство с возможностями условного форматирования таблиц.

Темы: Создание и использование правил условного форматирования.

1. Создайте таблицу, приведенную на рис.4.5.

1.1. Примените к диапазону В3:В14 условное форматирование с помощью набора значков «три сигнала светофора без обрамления», а к диапазону С3:С14 - «пять четвертей».

1.1.1. Активизируйте команду Главная – Стили – Условное форматирование – Наборы значков .

1.1.2. Выберите команду Управление правилами и перейдите в диалоговое окно Диспетчер правил условного форматирования . Ознакомьтесь с возможностями данного окна.

1.2. Создайте правило условного форматирования на основе формулы . Отформатируйте только те значения диапазона В3:В14, которые больше 40%, выделив их красной заливкой. Для этого активизируйте команду Главная – Стили – Условное форматирование – Создать правило . В диалоговом окне Создание правила форматирования выберите Использовать формулу и введите формулу =В3>$А$16. Перейдя в диалоговое окно Формат ячеек , установите нужный формат. Повторите указанные действия для диапазона С3:С14 и порога, записанного в ячейке А17.

Рис.4.5

2. Создайте таблицу, приведенную на рис.4.6.

2.1. С помощью условного форматирования определите повторяющиеся значения в диапазоне с фамилиями.

2.2. Для диапазона В2:В14 выделите значения, превышающие два заказа и значения, равные одному заказу.

2.3. Для диапазона С2:С14 выделите суммы заказов, выше среднего значения и ниже среднего , а также выделите четыре наибольших сумм заказов.

2.4. Вставьте новый столбец справа от столбца С и скопируйте в него столбец сумм заказов, выровняйте значения по правому краю и увеличьте ширину столбца. Примените условное форматирование Гистограммы .

2.5. К диапазону Курьер примените условное форматирование Текст содержит и выделите значение Гермес.

Рис.4.6

3. Предъявите результаты преподавателю.

Задание № 3. Организация таблиц.

Цель : Знакомство с организацией вычислений в таблицах.

Темы: Работа с группами листов. Использование «формулы массива». «Автовычисление», «Автоформатирование». Влияющие и зависимые ячейки.

1 . Пользуясь методом группового заполнения листов, создайте на трех листах нового документа таблицу, приведенную на рис.5.1, введя данные в диапазон В4: F 8. Дайте листам имена "Таб1", "Таб2", "Таб3".

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

2.1. В диапазоне G 4: G 8 запишите формулы для вычисления суммарной нагрузки по группам , пользуясь формулой массива .

2.2. В диапазоне В10: F 10 запишите формулы для вычисления суммарной нагрузки по видам нагрузки, пользуясь буфером обмена (ввести формулу, вычисляющую суммарную нагрузку по лекциям в ячейку B 10, затем воспользоваться командами Главная – Буфер обмена – Копировать и Главная – Буфер обмена – Вставить , предварительно выделив диапазон вставки).

Рис.5.1

2.3. Запишите формулу для суммирования нагрузки по строкам в ячейку G 9.

2.4. Запишите формулу для суммирования нагрузки по столбцам в ячейку G 10.

2.5. Запишите формулу для вычисления процентного содержания нагрузки для группы ЕС61-63 в общей сумме часов (ячейка H 4).

2.6. Скопируйте данную формулу в диапазон H 5: H 8, пользуясь автозаполнением .

2.8. Запишите формулу для вычисления процентного содержания лекционной нагрузки в общей сумме часов (ячейка В11).

2.9. Заполните аналогичными формулами диапазон C 11: F 11, пользуясь командой Главная – Редактирование – Заполнить вправо .

3 . Пользуясь автовычислением , определите среднее, минимальное и максимальное значения нагрузки для групп ЕС61-63 и СУ61 и зафиксируйте результаты.

4 . Активизируйте режим ручного пересчета формул (Office – Параметры Excel ).

4.1. Несколько раз измените значения в таблице и выполните ручной пересчет.

5 . Отформатируйте таблицу на листе "Таб2" по образцу, представленному на рис.5.2, обратив внимание на центровку строки заголовка и формат процентного представления чисел в ячейках (H 4: H 8 и В11: F 11).

5.1. Заголовки столбцов оформите с использованием непосредственного форматирования.

5.2. Для форматирования ячеек А10:А11 используйте копирование формата, созданного в п.5.1.

5.3. Отформатируйте таблицу на листе "Таб3", пользуясь функцией автоформатирования .

Рис.5.2

6 . Пользуясь командой Формулы – Зависимости формул , выявите влияющие и зависимые ячейки для ячейки G 9 .

7 . Пользуясь "объемной" формулой =СУММ(Таб1:Таб3! G 9), вычислите сумму значений в клетках G 9 трех листов и зафиксируйте полученный результат в клетке G 15 листа "Таб1".

8 . Пользуясь командой Главная – Буфер обмена – Вставить – Специальная вставка , уменьшите значения в диапазоне B 10: F 10 в четыре раза.

9 . Реализуйте подсчет суммы значений с последовательным накоплением сумм в столбце Накопленные суммы таблицы, приведенной на рис.5.3. Сумма с накоплением для ячейки С2 – это продажи за январь, для С3 – продажи за январь и февраль, для С4 – продажи за январь, февраль и март и т.д. Для осуществления этого алгоритма примените необходимую адресацию в формуле =сумм(В2:В2) , помещенной в ячейку С2 указанного столбца и скопируйте ее в остальные ячейки С3:С14.

Рис.5.3

Задание № 4. Функции.

Цель : Знакомство с использованием функций табличного процессора MS Excel.

Темы: Математические, статистические и логические функции. Функции даты и времени. Функции ссылки и массива. Текстовые функции. Функции для финансовых расчетов.

1 . Научитесь пользоваться математическими и статистическими функциями.

1.1.Создайте таблицу, приведенную на рис.6.1.

Рис.6.1

1.2. Введите в столбец B функции, указанные в столбце А (столбец А заполнять не надо) и сравните полученные результаты с данными, приведенными в столбце В на рис.6.1.

1.3. Проанализируйте результаты и сохраните созданную таблицу в книге.

2 . Научитесь пользоваться логическими функциями.

2.1. Активизируйте второй лист созданной книги.

2.2. Введите таблицу, приведенную на рис.6.2.

2.3. В клетку С2 введите формулу, по которой будет вычислена скидк а и скопируйте ее в диапазон С3:С6:

1. если стоимость товара <2000 единиц, то скидка составляет 5% от стоимости товара,
2. в противном случае - 10%.

2.4. В клетку D2 введите формулу, определяющую налог и скопируйте ее в диапазон D3:D6:

1. если разность между стоимостью и скидкой >5000, то налог составит 5% от этой разности,
2. в противном случае - 2%.

Рис.6.2

2.5. Повторите п.2.3 для следующих условий:

1. если стоимость товара <2000, то скидка составляет 5% от стоимости товара,
2. если стоимость товара >5000, то скидка составляет 15% от стоимости товара,
3. в противном случае - 10%.

2.6. В клетку А10 может быть занесена одна из текстовых констант: "желтый", "зеленый", "красный". В клетку А11 введите формулу, которая в зависимости от содержимого клетки А10, будет возвращать значения: "ждите","идите" или "стойте", соответственно.

2.7. Занесите в клетки Е8:E10 три имени: (Лена, Зина, Вера), а в клетки F8:F10 занесите даты их рождений. В клетку E4 введите одно из упомянутых имен.

Пользуясь конструкцией "вложенного" оператора ЕСЛИ, выполните следующие действия:

Проанализировав имя в клетке Е4, запишите в клетку С12 функцию ЕСЛИ, обеспечивающую:

1. вывод даты рождения, взятой из соответствующей клетки,
2. если же введено неподходящее имя, вывод сообщения: "нет такого имени".

3 . Научитесь пользоваться функциями даты и времени , ссылки и массива .

3.1. Активизируйте третий лист книги Имя_6_1.

3.2. Введите в клетку С2 функцию, отображающую сегодняшнюю дату.

3.3. Введите в клетку С3 функцию ДАТА, отображающую произвольно выбранную дату.

3.4. В клетку С5 запишите функцию ВЫБОР, позволяющую вывести название дня недели для даты, введенной в клетку С2 (понедельник, вторник, среда...).

3.5. В клетку С6 запишите аналогичную функцию для даты, введенной в клетку С3.

3.6. Вычислите возраст человека, поместив дату его рождения в клетку С10. Для этого используйте формулу:

РАЗНДАТ(С10;СЕГОДНЯ();"y")

3.7. Представьте текущее время , используя функции ТДАТА() и СЕГОДНЯ().

3.8. Поместите в соседние ячейки текущую дату и время и дату и время, отстоящую от текущей на трое суток. Найдите количество часов и минут между этими датами, пользуясь форматом [ч]:мм:сс и Общим форматом, а также форматом 13:30 . Зафиксируйте результаты и объясните различие.

3.9. Определите номер текущей недели и выведите сообщение:

"Сейчас идет № недели неделя".

3.10. На четвертом листе книги создайте таблицу, приведенную на рис.6.3.

3.10.1. Дайте имена диапазонам клеток, определяющим полученную стипендию за каждый семестр.

3.10.2. В клетку В8 запишите функцию, дающую ответ на вопрос: "Какую стипендию в n -м семестре получил m -й студент?" Значения n -го семестра и фамилия m -го студента должны быть введены в клетки А8 и А9. Для решения поставленной задачи используйте функции ПРОСМОТР и ВЫБОР.

Рис.6.3

4 . Научитесь пользоваться статистическими функциями
РАНГ и ПРЕДСКАЗАНИЕ.

4.1. На пятом листе книги создайте таблицу, приведенную на рис.6.4.

4.2. Используя функцию РАНГ, определите ранги цехов в зависимости от объема продаж по каждому году и поместите результаты в соответствующие клетки таблицы. В ячейки J3:J7 запишите формулы для вычисления средних значений рангов цехов.

4.3. Пользуясь информацией об объемах продаж, спрогнозируйте объемы продаж для каждого цеха в 1999 году, пользуясь функцией ПРЕДСКАЗАНИЕ.

Рис.6.4

5. Научитесь использовать текстовые функции.

5.1. Используйте формулу

="Сегодня "&ТЕКСТ(СЕГОДНЯ();"ДДДД ДД ММММ ГГГГ \г\.")

Проанализируйте полученный результат и измените аргумент функции ТЕКСТ, применяющий формат.

5.2. Для данных таблицы, приведенной на рис.6.5, используйте функцию ТЕКСТ для получения информации, идентичной записи в ячейке В6. В ячейке В5 текст «Доход равен» и число из ячейки В3 объедините с помощью конкатенации: «Доход равен » & В3. (Обратите внимание, что число при этом не форматируется ).

Рис.6.5

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

6 . 1. Вычислите объем ежемесячных выплат по ссуде, взятой на на срок 4 года, размер ссуды 70 000 руб., процентная ставка составляет 6% годовых. Для вычислений используйте функцию ПЛТ.

6 . 2. Вычислите общее количество выплат по ссуде размером 70 000 руб. Ссуда взята под 6% годовых. Объем ежемесячных выплат по ссуде 1 643,95 руб. Для вычислений используйте функцию КПЕР.

6.3. Вычислите объем ссуды, которую можно получить на 4 года под 6% годовых, если объем выплат не превышает 1 643,95 руб. Для вычислений используйте функцию ПС.

6.4. Вычислите основную часть выплат по ссуде за определенный период (первый, десятый, двадцатый и сорок восьмой месяцы). Ссуда 70 000 руб., взята на 4 года под 6% годовых. Для вычислений используйте функцию ОСПЛТ.

6.5. Вычислите часть выплат по ссуде, которая идет на выплату процентов за определенный период (первый, десятый, двадцатый и сорок восьмой месяцы). Ссуда 70 000 руб., взята на 4 года под 6% годовых. Для вычислений используйте функцию ПРПЛТ. Просуммируйте результаты вычислений функций ОСПЛТ и ПРПЛТ за соответствующие периоды и сделайте выводы.

7 . Предъявите результаты работы преподавателю.

Задание № 5. Диаграммы.

Цель : Знакомство с графическим представлением табличных данных в MS Excel.

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

1 . Введите таблицу, представленную на рис.7.1, на первый и второй листы книги.

Рис.7.1

2 . Научитесь создавать диаграммы на листе Диаграмма и на рабочем листе.

2.1 Выделите рабочий диапазон таблицы А4: G 6, и нажмите клавишу F 11 для быстрого построения гистограммы на отдельном листе.

2.2. Познакомьтесь с командами вкладки Работа с диаграммами – Конструктор - Тип и поменяйте гистограмму на нормированную гистограмму и проанализируйте полученный результат, верните прежний тип гистограммы.

2.3. Используя команду Работа с диаграммами – Конструктор – Данные – Строка/столбец , измените ориентацию рядов диаграммы, затем верните диаграмму к прежнему виду.

2.4. Познакомьтесь с экспресс - макетами диаграммы и примените один из них, для возврата используйте команду экспресс – макет 11.

2.5. Снабдите диаграмму элементами диаграммы, перечень которых можно найти на вкладке Работа с диаграммами – Макет . На диаграмме должны быть подписи данных, легенда, название диаграммы, а также названия осей и таблица значений .

2.6. Выберите маркер диаграммы из ряда Факт с наибольшим значением, увеличьте размер шрифта подписи данных этого маркера и измените его заливку. Используйте команду Формат выделенного фрагмента на вкладке Работа с диаграммами - Макет или Работа с диаграммами - Формат .

2.7. Постройте на рабочем поле первого листа аналогичную гистограмму. Обратите внимание на команду Работа с диаграммами – Конструктор – Расположение , которая позволит расположить диаграмму на отдельном листе или непосредственно в текущем.

2.8. Добавьте новую строку в исходную таблицу, в которой будет рассчитано среднее значение между плановыми и фактическими показателями, и отредактируйте гистограмму, указав новый диапазон данных (Работа с диаграммами – Конструктор – Данные – Выбрать данные) . Замените тип диаграммы для ряда среднего значения на график и используйте для него вспомогательную ось. Снабдите гистограмму всеми элементами диаграммы (п.2.5) и оформите ее по своему усмотрению. Сохраните книгу.

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

3.1.Постройте диаграмму с областями (Area ).

3.2.Постройте линейчатую диаграмму (Bar).

3.3.Постройте диаграмму типа график (Line).

3.4.Постройте круговую диаграмму для фактических показателей (Pie).

3.5.Постройте кольцевую диаграмму (Doughnut ).

3.6.Постройте лепестковую диаграмму - "Радар" (Radar).

3.7.Постройте точечную диаграмму (XY).

3.8.Постройте объемную круговую диаграмму плановых показателей (3-D_Pie).

3.9.Постройте объемную гистограмму (3-D_Column).

3.10.Постройте объемную диаграмму с областями (3-D_Area).

4 . Научитесь редактировать диаграммы 2 .

4.1. В диаграмме "График" замените тип диаграммы для данных, обозначающих "План", на круговую и назовите лист "Line_Pie".

4.2. Отредактируйте круговую диаграмму, созданную на листе "Pie", так, как показано на рис.7.2.

4.3. Отредактируйте линейные графики так, как показано на рис.7.3.

Рис.7.2 Рис.7.3

4.4. Научитесь редактировать объемные диаграммы.

4.4.1. Установите "поворот" диаграммы вокруг оси Z для просмотра:

фронтально расположенных рядов (угол 0 о );

под углом в 30 о ;

под углом в 180 о ;

4.4.2. Измените перспективу, сужая и расширяя поле зрения.

4.4.3. Измените порядок рядов, представленных в диаграмме.

5 . Предъявите результаты преподавателю.

2 Оформление надписи "показатели производства" на рис.7.2 производится факультативно.

А также другие работы, которые могут Вас заинтересовать
85288.		Лицарський турнір	763.5 KB
	6 грудня у календарі позначено як День Збройних сил України. І вже стало традицією вітати у цей день усіх чоловіків, хлопчиків. Напевне, цим жінки хочуть зайвий раз підкреслити у чоловіків риси, як мужність, сміливість. Щиросердя, шляхетність.
85289.		Турнір Веселих інформатиків	220 KB
	Мета: розвиток стійкого інтересу до інформатики; формування творчої особистості; формування комунікаційної компетенції; виховання поваги до суперника, стійкості, волі до перемоги, спритності; повторення й закріплення основного матеріалу в нестандартній формі...
85290.		Різноманітність тварин у природі	62.5 KB
	Формувати елементарні поняття риби земноводні плазуни; уявлення про істотні ознаки різних груп тварин. Виховувати пізнавальний інтерес до вивчення тварин прагнення до самоствердження у поєднанні з толерантним ставленням до інших потребу у збереженні природи.
85291.		У царстві рослин. Дерева, кущі, трави. Зовнішня будова рослин	69.5 KB
	Ознайомити з функціональним призначенням органів рослин, показати пристосування рослин для поширення плодів і насіння; розвивати спостережливість, увагу; виховувати бережливе ставлення до природи, любов до рідного краю, почуття прекрасного в природі.
85292.		У царстві рослин. Я і Україна	135 KB
	Мета: формування ключових компетентностей: вміння вчитися – самоорганізовуватися до навчальної діяльності у взаємодії; загальнокультурної – дотримуватися норм мовленнєвої культури, зв’язно висловлюватися в контексті змісту; соціальної – проектувати стратегії своєї поведінки з урахуванням потреб...
85294.		Свято в королівстві Ввічливості (лицарський турнір)	82 KB
	Запрошуємо Вас на наше свято. Відбудеться воно в незвичайній країні..., країні – добрих і ввічливих людей. Є в тій країні Королівство гарних манер або королівство Ввічливості. Правлять королівством їхні величності Король та Королева. А зрештою – побачите самі!
85295.		Руководство по защите от пыли при добыче и переработке полезных ископаемых	12.46 MB
	Руководство было написано группой специалистов по технике безопасности, охране труда, профессиональным заболеваниям, и инженерами (перечислены ниже) для того, чтобы собрать и представить проверенные технологии и методы снижения воздействия пыли на людей, используемые на всех стадиях добычи и переработки минеральных полезных ископаемых.
85296.		Фольклорная арт-терапия	39.8 KB
	Несомненную привлекательность арттерапии в глазах современного человека пользующегося в основном вербальным каналом коммуникации составляет то что она использует язык визуальной и пластической экспрессии. Это делает ее незаменимым инструментом для исследования и гармонизации тех сторон внутреннего мира человека для выражения которых слова малопригодны. С развитием арттерапии связываются надежды на создание такой гуманной синтетической методологии которая в равной мере учитывала бы достижения научной мысли и опыт искусства интеллект...

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

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

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

Введите список 15 фирм. Фирмы распределите по 5 городам. Набрав первую запись нажмите на кнопку Добавить.

Форматирование таблицы . Для ячеек 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 набираем следующую формулу:

=ЕСЛИ($ D2=« »; “ ”;ПРОСМОТР($D2;Номер товара; Наименование товара) (3.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 ) . Внесенные изменения сразу отразятся на изображении в поле Образец, нажмите на кнопку Далее. Нажмите на кнопку Готово.

путей сообщения»

Т.Г. ШАХУНЯНЦ

Методические указания

К лабораторным работам

По дисциплине

«Информатика»

Москва – 2014

Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования

«Московский государственный университет

путей сообщения»

Кафедра «Вычислительные системы и сети»

Т.Г. ШАХУНЯНЦ

Обработка данных средствами Microsoft Excel 2013

Университета в качестве методических указаний

Для студентов I курса

специальности “Эксплуатация железных дорог “

Москва – 2014

УДК 681.3

Шахунянц Т.Г. Обработка данных средствами Microsoft Excel 2013: Методические указания. – М.: МГУПС

(МИИТ), 2014. – 36 с.

Данные методические указания предназначены для выполнения лабораторных работ по изучению и освоению некоторых возможностей обработки данных в среде Microsoft Excel 2013. Для выполнения заданий к каждой из лабораторных работ приводятся соответствующие примеры.

© МГУПС (МИИТ), 2014

Введение………………………………………………………...…4

1.Лабораторная работа №1………….…………………...............5

2.Лабораторная работа №2 ……………………....…………....…9

3.Лабораторная работа №3 ……………………..…………….…12

4.Лабораторная работа №4 ……………..…………………….…15

5.Лабораторная работа №5 ……………….……………….….…19

6.Лабораторная работа №6………………………………………21

7.Лабораторная работа №7……...…………………….………...25

8.Лабораторная работа №8……………...……..……………..…29

9.Литература…………….…………………………………........35

Введение

Microsoft Excel относится к программам, позволяющим обрабатывать данные, представленные в форме таблиц («программам «электронные таблицы»). Электронные таблицы широко применяются в экономических и научно-технических задачах для проведения однотипных расчётов над большими наборами данных, построения диаграмм и графиков по имеющимся данным, решения уравнений, поиска значений параметров в задачах оптимизации и т.п.

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

В описываемых лабораторных работах используется версия Microsoft Excel 2013, имеющая ряд усовершенствований, по сравнению с предыдущими.

Целью лабораторных работ является изучение и освоение некоторых возможностей обработки данных в среде Microsoft Excel 2013.

В данных методических указаниях использованы материалы монографии

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

Основы работы в среде Microsoft Excel 2013.

1.1. Цель работы

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

1.2. Задания к выполнению лабораторной работы

Создать согласованные с преподавателем электронные таблицы с использованием абсолютных и относительных ссылок и копирования формул методом автозаполнения.

1.3. Подготовка к работе

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

1.4. Пример действий по выполнению заданий к лабораторной работе

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

2. Создайте новую книгу (Файл-> Создать).

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

4. Cохраните книгу под именем examples (Файл-> Сохранить как->Компьютер->Обзор->(Выбрать тип файла: Книга Excel).

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

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

6. Введите 9 произвольных чисел в последовательные ячейки столбца А, начиная с ячейки А2, заканчивая ячейкой А10.

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

8. Введите в ячейку С1 строку “Куб числа”.

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

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

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

12. Введите в ячейку D2 формулу =A3*A3.

13. Выделите протягиванием ячейки В2, С2 и D2.

14. Наведите указатель мыши на маркер заполнения в правом нижнем углу рамки, охватывающий выделенный диапазон. Нажмите левую кнопку мыши и перетащите этот маркер, чтобы рамка охватила столько строк в столбцах B,C,D, сколько имеется чисел в столбце A.

15. Измените одно из значений в столбце А и убедитесь, что соответствующие значения в столбцах В, С и D в этой же строке были автоматически пересчитаны.

16. Введите в ячейку Е1 строку “Масштаб”.

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

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

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

20. Используйте метод автозаполнения, чтобы скопировать эту формулу в ячейки столбца F, соответствующие заполненным ячейкам столбца А.

21. Убедитесь, что результат масштабирования оказался неверным. Это связано с тем, что адрес Е2 в формуле задан относительной ссылкой.

22. Щелкните на ячейке F2, затем в строке формул. Установите текстовый курсор на ссылку Е2 и нажмите клавишу F4. Убедитесь, что формула теперь выглядит как =А2*$Е$2, и нажмите клавишу ENTER.

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

24. Убедитесь, что благодаря использованию абсолютной адресации значения ячеек столбца F теперь вычисляются правильно. Сохраните книгу examples.

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

1.В чем отличия ввода данных от записи формул?

2.Как осуществляется копирование формул методом автозаполнения?

3. Чем отличаются относительные и абсолютные ссылки по форме и по результатам их обработки?

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

Использование стандартных и итоговых функций в среде Microsoft Excel.

2.1. Цель работы

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

2.2. Задания к выполнению лабораторной работы

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

2.3. Подготовка к работе

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

2.4. Пример действий по использованию стандартных и итоговых функций.

1.Запустите программу Excel (Пуск -> Все программы -> Microsoft Office->Microsoft Excel2013) и откройте рабочую книгу examples, созданную ранее (Файл -> Открыть)

2. Выберите рабочий лист “Данные”(щёлкнув по вкладке на нижней панели) .

3. Сделайте активной ячейку А11.

4. Щелкните на кнопку “Автосумма” на вкладке «Формулы» или на стандартной панели значок «∑»

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

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

7. Щелкните на кнопке “Вставить функцию” (Значок f X) на вкладке «Формулы».

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

10. Методом протягивания выберете ячейки от А2 до А10

11. Используя порядок действий, описанный в пп. 6-10, вычислите минимальное число в заданном наборе (функция МИН), максимальное число (МАКС), количество элементов в наборе

12. Сохраните книгу examples.

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

1. Способы использования стандартных функций.

2. Способы использования итоговых функций.

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

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

Создание, форматирование и подготовка к печати документов.

3.1. Цель работы

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

3.2. Задания к выполнению лабораторной работы

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

3.3. Подготовка к работе

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

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

1. Запустите программу Excel (Пуск> Все программы > Microsoft Office>Microsoft Excel 2013) и откройте книгу examples.

2. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый (Горячая клавиша SHIFT + F11). Дважды щелкните на ярлычке нового листа и пере­именуйте его как Прейскурант.

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

4. В ячейку А2 введите текст “Курс пересчета” : и нажмите клавишу ENTER. В ячейку В2 введите текст “1 у.е.=” нажмите клавишу ENTER. В ячейку С2 введите “Текущий курс пересчета” и нажмите клавишу ENTER.

5. В ячейку A3 введите текст «Наименование товара» и нажмите клавишу ENTER. В ячейку ВЗ введите текст «цена (у.е.)» и нажмите клавишу ENTER. В ячейку СЗ введите текст «Цена (руб.)» и нажмите клавишу ENTER.

6. В последующие ячейки столбца А введите названия товаров, включенных в прейскурант.

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

8. В ячейку С4 введите формулу: =В4*$С$2, которая используется для пересчета цены из условных единиц в рубли.

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

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

11. Выделите методом протягивания диапазон А1:С1 и дайте команду в контекстном меню Формат ячеек. На вкладке “Выравнивание” задайте выравнивание: “По левому краю” и щелкните “Объединить по строкам”.

12. На вкладке Шрифт задайте размер шрифта в 14 и в списке “Начертание” выберите вариант “Полужирный”.

13. Щелкните правой кнопкой мыши на ячейке В2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали: “По правому краю” и щелкните на кнопке ОК.

14. Щелкните правой кнопкой мыши на ячейке С2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали: “По левому краю” и щелкните на кнопке ОК.

15. Выделите методом протягивания диапазон В2:С2. и выберите в контекстном меню команду Формат ячеек. На вкладке “Границы” задайте широкую внешнюю рамку.

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

17. Посмотрите, устраивает ли Вас полученный формат таблицы. Щёлкните на кнопке «Предварительный просмотр», нажав Файл-> Печать, чтоб увидеть, как будет выглядеть при печати.

18. Щёлкните по кнопке «Печать» (Файл -> Печать -> Печать) и напечатайте документ.

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

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

1. Как осуществляется выравнивание текста в ячейках?

2. Способы изменения ширины столбцов и строк.

3. Как объединить ячейки таблицы?

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

4. Лабораторная работа №4

Похожая информация.

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

 

Возможно, будет полезно почитать:

• Копирование лендингов: Научитесь самостоятельно копировать лендинги ;
• Простой способ скачать apk файл из Google Play на компьютер ;
• Samsung Galaxy S7 Edge - обзор и технические характеристики Фронтальная камера s7 edge ;
• Кейт Мэтсудейра: Масштабируемая Веб-архитектура и распределенные системы ;
• Как поднять тиц Как повысить тиц ;
• Chrome: Загрузка только одной вкладки при запуске Остановить загрузку всех вкладок хром ;
• Обновляем BIOS на MSI Раздел Power - Питание ПК ;
• Как увеличить громкость на ноутбуке с Windows ;