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

Microsoft Office Excel 2007 - табличный процессор. Поддерживает необходимые функции создания и обработки электронных таблиц.

Последняя версия использует формат с расширением «.xlsx», с макросом .xlsm, более ранние версии использовали двоичный формат «.xls».

Пояснения по выполнению рабочего задания.

К заданию 1

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

В качестве примера: выделим диапазон ячеек, в которые будут вводиться данные. Возьмем инструмент "Проверка данных" на панели "Работа с данными" ленты "Данные". Из выпадающего списка выберем значение "Проверка данных..". В появившемся окне "Проверка вводимых значений" на вкладке "Параметры" задайте условия проверки (например, тип данных: целое число; значение: не равно; значение: 0; игнорировать пустые ячейки). На вкладке "Сообщение для ввода" нужно ввести текстовые значения, которые будут показаны, когда ячейка выбрана (см. рисунок 17). На вкладке "Сообщение об ошибке" нужно ввести текстовые значения, которые будут показаны, когда в ячейку введено ошибочное значение (см. рисунок 17). На рисунке18 изображено, как работает проверка диапазона ввода данных.

Рисунок 17 - Образец назначения условия и ограничения на ввод

Рисунок 18 - Проверка данных, результат

Если в окне "Проверка вводимых значений" на вкладке "Параметры" установить в поле "Тип данных" значение "Список", то можно упростить ввод повторяющихся данных. Для этого надо предварительно ввести все допустимые значения из списка. Затем в поле "Источник" указать диапазон ячеек, где расположены допустимые списочные значения.

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

Рисунок 19 - Пример представления в разных форматах

Форматирование является удобным для анализа данных - можно раскрасить рабочий лист так, что каждый цвет будет соответствовать определенным данным. В таком случае хватит даже беглого взгляда на лист документа, чтобы быстро проанализировать данные. Для применения условного форматирования служит кнопка "Условное форматирование" на панели "Стили" вкладки "Главная".

Для сортировки и фильтрации используется лента «Данные». Иногда таблицы могут содержать большое количество данных, причем эти данные зачастую представлены в виде списка. Тогда помогают такие инструменты, как сортировка списков и фильтрация. При этом список должен быть оформлен определенным образом, в противном случае выбранные инструменты работать не будут. Список состоит из записей (строк) и полей (столбцов). Столбцы должны содержать однотипные данные. Список не должен содержать пустых строк \ столбцов. Если в списке присутствуют заголовки, то они должны быть отформатированы другим образом, а не как остальные элементы списка.

Сортировка или упорядочивание списков облегчает поиск информации. После сортировки записи отображаются в порядке, определенном значениями столбцов (по алфавиту, по возрастанию/убыванию цены и пр.). Сделайте небольшой список для тренировки. Выделите его. Нажмите кнопку "Сортировка и фильтр" на панели "Редактирование" ленты "Главная". Выберите "Сортировка от А до Я". Список будет отсортирован по первому столбцу. Если надо отсортировать список по нескольким полям, то для этого предназначен пункт "Настраиваемая сортировка..". Сложная сортировка подразумевает упорядочение данных по нескольким полям. Добавлять поля можно при помощи кнопки "Добавить уровень". В итоге список будет отсортирован согласно установленным параметрам сложной сортировки. Если надо отсортировать поле нестандартным способом, то для этого предназначен пункт меню "Настраиваемый список.." выпадающего списка "Порядок". Перемещать уровни сортировки можно при помощи кнопок "Вверх" и "Вниз".

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

Связанная таблица - это набор данных, которыми можно управлять как единым целым. Для создания связанной таблицы предназначена кнопка "Форматировать как таблицу" на панели "Стили" ленты "Главная". Выберите нужный стиль будущей таблицы и задайте диапазон ячеек, на основе которого будет создана связанная таблица. Рядом с заголовками столбцов появятся кнопки со стрелочками (по аналогии с фильтрацией), а в окне программы появится контекстный инструмент "Работа с таблицами", содержащий ленту "Конструктор". Каждой связанной таблице дается уникальное имя. По умолчанию - "Таблица_номер". Изменить название таблицы можно на панели "Свойства". На панели "Стили таблиц" можно, при необходимости, изменить или настроить стиль связанной таблицы. Связанную таблицу можно обратно превратить в обычный диапазон ячеек. Для этого предназначена кнопка "Преобразовать в диапазон" на панели "Сервис" контекстной ленты "Конструктор". Рядом с заголовками столбцов расположены кнопки сортировки и фильтрации, при помощи которых можно сортировать данные и применять фильтры. В связанной таблице можно подсчитывать итоговые значения в столбцах. Для этого надо установить флажок "Строка итогов" на панели "Параметры стилей таблиц".

Для создания диаграммы (см. рисунок 20) необходимо воспользоваться инструментами панели "Диаграммы" ленты "Вставка". Если не устраивает ни один из вариантов диаграмм, то можно воспользоваться кнопкой вызова окна панели "Диаграммы", указать диапазон данных для построения. Если данные берутся из всей таблицы, то нужно указать любую ячейку таблицы. Если нужны определенные данные из таблицы, то надо выделить этот диапазон. Во время выделения можно пользоваться кнопками Shift (для последовательного выделения), Ctrl (для несмежного выделения элементов таблицы). Для взаимной замены данных на осях воспользуйтесь кнопкой "Строка/Столбец". После вставки диаграммы в окне появляется контекстный инструмент "Работа с диаграммами", содержащий три ленты "Конструктор", "Макет", "Формат".

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

Рисунок 20 - Пример построения диаграммы по исходным данным

Ссылки делятся на 3 типа:

1. Относительная ссылка: значение в ячейке зависит от данных относительно ее местоположения. Пример: = из ячейки сверху = А1 (см. рисунок 21а).

2. Абсолютная ссылка: берется конкретное значение указанной ячейки. Пример: = значение из ячейки А1 = $A$1 (см. рисунок 21б).

3. Смешение относительной и абсолютной ссылок называют смешанной ссылкой. Пример: = по столбцу А ячейка выше = $A1; по первой строчке самый левый столбец = A$1 (см. рисунок 21в).

Введем в ячейку А1 любое число. В ячейку А2 введем формулу =А1. Нажмем клавишу F4. После нажатия тип ссылки будет меняться (см. рисунок 21г).

а б

в г

Рисунок 21 - Примеры разных ссылок

Можно ссылаться на другие листы той же книги или на листы других книг так же, как и на ячейки текущего листа. Ссылка будет называться внешней. Пример ссылки на ячейку из другой книги =[Книга2]Лист2!А5 или =[Пример_1.xlsx]Лист1!$A$2. Пример использования внешнего ресурса ='C:\Documents and Settings\Admin\Рабочий стол\Пример_2.xlsx'!$A$3.

Ячейкам и диапазонам ячеек можно присваивать имена и затем использовать их в формулах и ссылаться в любом месте любой книги. Пусть у нас в ячейке А3 записана формула А1+А2. Если присвоить ячейке А1 имя "Базис", а ячейке А2 - "Надстройка", то запись =Базис+Надстройка возвратит подсчитанное значение.

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

К заданию 2

Наиболее распространенные функции Excel являются краткой записью часто используемых формул. Например, функция =СУММ(А1:А4) аналогична записи =А1+А2+А3+А4. Каждая функция состоит из имени и аргумента. В предыдущем случае СУММ - это имя функции, а диапазон ячеек А1:А4 - аргумент. Аргумент заключается в круглые скобки. Так как функция суммы используется часто, то на панель инструментов "Стандартная" вынесена кнопка "Автосумма". Она снабжена выпадающим списком, из которого можно выбрать другую формулу. Для выбора функций служит кнопка "Вставка функции" в строке формул (см. рисунок 22). Если точно неизвестна функция, которую надо применить, то в окне диалога "Поиск функции" можно произвести поиск. Если формула очень громоздкая, то можно включить в текст формулы пробелы или разрывы строк. Это никак не влияет на результаты вычислений. Если функция имеет переменное число аргументов, это окно диалога при вводе дополнительных аргументов расширяется.

Рисунок 22 - Меню выбора функций

К заданию 3

К заданию 3.1

Для примера построим график функции y=cos(πx) на отрезке [0;1) с шагом 0,1.

1. Необходимо сначала построить таблицу значений при различных значениях аргумента. Подпишем столбцы: в ячейке А1 введем «х», а в В1 введем «y=cos2(πx)». В ячейки А2:А12 введите значения х: 0,0.1,0.2, …1 (для этого в ячейку А2 вводим «0», а в ячейку А3 - «0.1». Затем выделите вместе эти ячейки. После этого установите указатель мыши на маркере заполнения выделенного диапазона в правом нижнем углу выделения, должен появиться черный плюс (см. рисунок 23а) и протащите его вниз до тех пор, пока не получится числовой ряд нужной длины, то есть до А12. Далее в ячейку В2 вводим формулу =cos (пи()*А2) (см. рисунок 23б). Ввод формулы в ячейку можно производить с клавиатуры или с помощью диалогового окна Мастер функций. Итак, выделите ячейку В2 и нажмите на кнопку Мастер функций. Функция cos относится к категории Математические, выберем эту функцию и нажмем кнопку Далее. На экране появится второе диалоговое окно Мастера функций, в котором вводится аргумент функции, то есть пи()*А2. Для того чтобы завершить процесс заполнения значений в таблице, выделите ячейку В2, установите указатель мыши на маркере заполнения и протащим его вниз до ячейки В12 (см. рисунок 23в).

2. Итак, таблица значений функций создана. Для построения графика функции выделите диапазон ячеек А2:В12, содержащий таблицу значений, и вызовем на Ленте Вставка Мастер диаграмм. Выберите тип диаграммы, например, Точечная, и выберите окончательный вид. Рядом с таблицей значений отобразится построенный график (см. рисунок 23г).

3. Для изменения данных, цветов, шрифтов, оформления, подписей, осей, названий диаграммы необходимо внутри диаграммы щелкнуть левой кнопкой мыши и станут доступными пункты «Изменить тип диаграммы», «Выбрать данные», «Переместить диаграмму».

абвг

Рисунок 23 - Этапы построения по таблице значений графика функции

Для примера построим графики функций y₁=x² и y₂=x³ на интервале [- 3;3] с шагом 0,5 на 1 графике.

1. Необходимо заполнить таблицу значений (см. рисунок 24а).

2. Затем выделить таблицу и указать тип диаграммы «Точечная» - для построения это самый удачный тип диаграммы.

3. В Макете указать название диаграммы «Графики», дать название осей: X и Y. Совместить построение 2 графиков (см. рисунок 24б).

аб

Рисунок 24 - Построение графиков функций y1=x² и y2= x³

К заданию 3.2

Для примера найдем решение системы в диапазоне[0,3] с шагом 0,2.

1. Необходимо заполнить таблицу значений (рисунок 25а).

2. Выбрать тип диаграммы и по полученным значениям таблицы (столбец 2 и столбец 3) постройте графики (рисунок 25б).

3. Получена диаграмма значений кривых синуса и косинуса. Как видно по рисунку 25 система имеет решение (есть точка пересечения) и оно единственное. Решением системы в заданном диапазоне являются координаты точки пересечения кривых. Для их нахождения надо навести указатель мыши на точку пересечения и щелкнуть левой кнопкой мыши и не отводить указатель в течение 5 секунд. Появится надпись с указанием искомых координат. Таким образом, приближенное решение системы x=0,8; y=0,697

аб

Рисунок 25 - Построение графиков функций y=sin x и y=cos х

К заданию 3.3

Для примера построим график функции y=на отрезке [-3; 3].

1. В столбец A вводятся значения х, а в столбец В значения функции, начиная с ячейки В2, в которую вводится формула =ЕСЛИ(A2<0;(-5*A2);A2^2), а потом она растягивается на весь диапазон.

2. Необходимо выбрать тип диаграммы и построить график (см. рисунок 26)

Рисунок 26 - Построение графика функции с одним условием

К заданию 3.4

Для примера построим поверхность Z=X²-Y², изменяя Х и Y на отрезке [-1;1] с шагом 0,2.

1. В диапазоне ячеек B1:L1 вводится последовательность значений: -1; -0,8; … 1 переменной х, а в диапазоне ячеек А2:А12 – такую же последовательность значений переменной Y. В ячейку В2 вводится формула =$A2^2-B$1^2. Выделяя эту ячейку, устанавливается указатель мыши на ее маркере и протаскивается так, чтобы заполнить диапазон В2:L12. Знак $, стоящий перед буквой в имени ячейки, дает абсолютную ссылку на столбец с данным именем, а знак $, стоящий перед цифрой, абсолютную ссылку на строку с этим именем. Поэтому при протаскивании (копировании) формулы из ячейки В2 в ячейки диапазона В2:L12 в них будет найдено значение Z при соответствующих значениях X и Y.

2. Далее выделяется диапазон ячеек A1:L12, содержащий таблицу значений функции и ее аргументов, и вызывается мастер диаграмм. На втором шаге мастера диаграмм выбирается тип диаграммы – «Поверхность», на третьем шаге выбирается вид поверхности (см. рисунок 27).

Рисунок 27 - Построение поверхности