перации над рабочими листами

Microsoft Excel

Тема 1. Запуск Microsoft Excel. Экран Excel

Запустите MS Excel и вы увидите окно (рис.1.1). Рассмотрим отдельные элементы окна и основные понятия Excel. Заметьте, что структура окна очень похожа на структуру знакомого уже вам окна текстового редактора Word. Поэтому знакомится с элементами окна и возможностями Excel будем по мере необходимости, выполняя предлагаемые ниже задания.

Рис. 1. Структура окна Excel

Основные элементы MS Excel

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

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

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

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

1.1. Строка заголовка. Строка меню. Панели инструментов

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

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

Строка меню.

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

трока формул

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

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

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

абочая область

Рабочий лист (или просто лист) образует рабочее пространство пользователя. Лист имеет 256 столбцов и более 16 тысяч строк. Каждый столбец имеет заголовок: A, B,…,Z, AA, AB,..,AZ, BB,..,IV, а строки пронумерованы.

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

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

Ячейкаявляется основным структурным элементом таблицы. Каждая ячейка имеет адрес, например, D25 (пересечение столбца D и строка 25).

Ячейка становится текущей (активной) ячейкой, если по ней щелкнуть мышкой. Текущую ячейку можно отличить по рамке вокруг нее. В такую ячейку можно вводить данные с клавиатуры и редактировать ее содержание.

Блок (прямоугольная область ячеек) задается адресами левого верхнего и правого нижнего его углов, например, D15:F20.

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

перации над рабочими листами

Книга (документ MS Excel) – самая крупная единица данных в Excel. Книга является синонимом файла с расширением .xls. По умолчанию книги получают имена Книга 1, Книга 2 и т.д. Книга состоит из рабочих листов. Каждая вновь созданная книга состоит из 3-х листов.

Непосредственно под рабочим листом расположены вкладки листов с их именами. По умолчанию – это Лист1, Лист2, … Щелкнув по вкладке листа кнопкой мыши, можно вызвать нужный лист из рабочей книги.

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

Имя листа можно изменить. Для этого дважды щелкните на вкладке листа и непосредственно здесь же введите новое имя.

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

Попробуйте проделать все это.

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

Каждая ячейка в Excel может содержать данные одного из трех типов:

текст, число или формулу.

чейка текстового типа

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

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

Задание 2.1. Подготовьте «логотип» по образцу рис.2, скопируйте его на Лист 2, переименуйте эти листы. Переместите логотип в верхний правый угол рабочего листа.

Рис. 2.Ячейки текстового типа

Рекомендации к выполнению задания 2.1

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

1. Ввод и форматирование текстовой информации. Используйте параметры шрифта, цвет символов, тонирование ячеек, установку границы аналогично тому, как вы делали это в текстовом редакторе Word.

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

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

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

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

2. Для простановки даты в ячейке D5 (рис. 2.1) используйте Мастер функции.

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

1. Копирование. Используйте буксировку и буфер обмена аналогично Word.

2. Для очистки содержимого ячейки или блока ячеек используйте команду Удалить (кнопка Delete) или команду:

Вкладка Главная/Редактирование/Очистить (пиктограмма ).

Самостоятельно опробуйте эти команды и определите разницу между ними.

чейка числового типа

Ячейка числового типа содержит числа в диапазоне –1,67*10³⁰⁸-+1,67*10³⁰⁸ (количество значащих цифр 15).

Задание 2.2. Введите несколько различных чисел в разные ячейки.

2.2.1. Форматы данных. Формат позволяет отображать числовые данные в том или ином виде.

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

 

Рассмотрим наиболее часто используемые форматы.

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

Числовой формат. Число отображается с заданным количеством десятичных знаков после запятой.

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

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

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

Приведем несколько примеров пользовательских форматов.

Для отображения числа в виде 10,23кв.м. формат может иметь вид: #0,00”кв.м.”

А для (-34,546х)-формат имеет вид:

+ #0,000” х”; - #0,000” х”

с тем чтобы отображался и знак полюс или минус, и символ «х».

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

Ø выделить ячейку с нужным форматом;

Ø щелкнуть на пиктограмме копировать формат ;

Ø курсор приобретет форму кисточки;

Ø указать («покрасить») область применения копируемого формата с помощью мышки.

Задание 2.3. Установите рублевый и долларовый форматы всеми предложенными способами. Установите определенное количество знаков после запятой. Создайте любой пользовательский формат. Используйте операцию копирования форматов.

2.2.2. Автоматическое заполнениепозволяет заполнить ряд смежных ячеек числами, датами, перечисляемыми названиями так, как показано на рис.3.

Рис.3. Ввод данных с помощью инструмента Автозаполнение.

Задание 2.4. Постройте примеры автозаполнения аналогично тому, как это показано на рис.3.

Для заполнения ячеек перечисляемыми названиями, такими как, 1 квартал, 2 квартал,….. или май, июнь, ….и др., выполните следующую последовательность действий:

Ø введите в ячейку первое значение, например, май,

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

Ø держа левую клавишу мыши, протащите её в нужном направлении.

Аналогично можно получить список дат, дней недели и т.д.

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

Ø введите в две смежные ячейки числа, например -2 и 0,

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

Ø протащите мышь в нужном направлении.

Если все проделано верно, то вы получите последовательность чисел: -2, 0, 2, 4, 6, 8, …, т.е. арифметическую прогрессию.

2.3. Ячейки типа «формула»

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

Далее набирается арифметическое выражение с учетом приоритета выполняемых операций:

Ø вычисление внутри скобок;

Ø вычисление функций;

Ø умножение, деление;

Ø сложение, вычитание.

Задание 2.5. Выполните следующие операции:

Ø введите формулу в ячейку A5: А5 =3,55+2,3; (здесь и в дальнейшем эта запись означает, что в ячейку А5 вводится формула =3,55+2,3);

Ø введите в ячейки А1 и С5 какие либо числа;

Ø введите формулу А7=А1+С5; адрес ячейки можно включать в формулу одним щелчком мыши по этой ячейке;

Ø измените содержимое ячеек А1 и С5 и проанализируйте результат в ячейке А7;

Ø поочередно активизируйте ячейки А1, А5, А7 и обратите внимание на содержимое этих ячеек в строке формул.

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

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

Задание 2.6.а). Подготовьте таблицу по аналогии с рис.4 из 2-х столбцов (Х и У), числовые значения можно ввести с помощью автозаполнения.

Рис.4. Расчетная схема задания 2.6.

Задание 2.6.б). В столбце С вычислите сумму соответствующих элементов столбцов A и В.

Порядок выполнения.

Ø введите формулу С4=А4+В4,

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

Проанализируйте результаты копирования формулы; посмотрите, как выглядит формула, например в ячейках С5 или С7.

Задание 2.6.в). Элементы полученного столбца умножьте на постоянное число (х+у)*a.

Порядок выполнения.

Ø Введите в ячейку В1 текст «а=», а в ячейку В2 = 2 (где будет храниться значение а),

Ø введите формулу D4=C4*$В$2,

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

Проанализируйте результаты копирования; посмотрите как выглядит формула, например, в ячейках D5 или D7.

Задание 2.6.г). Найдите максимальный и минимальный элементы последнего столбца (D) и сумму элементов этого столбца.

Используйте для этого автосуммирование и Мастер функций.

Мастер функций. При записи формулы обращение к встроенной (стандартной) функции можно написать вручную или с помощью Мастера функций.

Мастера - это встроенные инструменты (программы) Microsoft Excel, призванные помогать при составлении формул, построении диаграмм или сводных таблиц.

Мастер функций можно вызвать с помощью пиктограммы

Окно Мастера функций приведено на рис.5. Мастер функций имеет два окна (два шага). На первом шаге выбирается категория (например, математические, статистические, дата и время и т.д.) и необходимая функция из этой категории. Например: категория статистические, функции МАКС или МИН.

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

Рис.5. Окно Мастера функций.

Полное описание всех функций имеется в справочной системе Excel.

Стандартная функция СУММ имеет формат:

СУММ(<число1>;<число2>;…)

Функция возвращает сумму чисел, входящих в список параметров.

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

Функцию суммирования можно ввести и с помощью пиктограммы Автосуммирования S.

Функция СУММ() удобна, если слагаемых много и они находятся в смежных ячейках.

Для нахождения минимального и максимального элементов воспользуйтель пиктограммой Автосуммирования - S: Главная/Редактирование

Задание 2.6.д). Вставьте строку в таблицу и оцените, как изменится общая сумма.

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

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

Тема 3. Диаграммы

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

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

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

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

 

 

При построении диаграммы используют следующие понятия: ряд данных, категории, легенда.

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

Категории - (аргументы функции на оси Х) служат для упорядочения значений в рядах данных.

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

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

Задание 3.1. Протабулируйте на отрезке[-5,4] функцию

и постройте ее график, используя Мастер диаграмм.

Рекомендации к выполнению задания.

Построение графика функции в Excel напоминает построение графика функции по точкам. Сначала постройте таблицу значений функций для равноудаленных точек отрезка [-5,4] (рис.6). Эта операция называется табулированием. Для этого проделайте следующие операции:

Ø Для заполнения столбца А:

Введите в ячейки

А12 =D6 (первоначально значение х = значению начала отрезка - a);

А13 =А12+$D$9 (вычисляется следующее значение х; в ячейке D9 хранится значение шага табулирования - h);

Выделите ячейку А13 и скопируйте ее вниз до получения в столбце А значения конца отрезка – b);

Ø для вычисления значений функции в равноудаленных точках отрезка используйте логическую функцию ЕСЛИ.

Формат функции: ЕСЛИ (<условие>; <результат, если <условие>=True>; <результат, если<условие>=False>)

Для заполнения столбца В введите формулу B12 = ЕСЛИ(A12>1; 3*Ln(A12); 2*(A12-1)^2), а далее скопируйте ее вниз до конца таблицы.

Рис.6. Построение графика функции.

Этапы построения Точечной диаграммы:

Для построения Точечной диаграммы необходимо ввести данные на листе рабочей книги MS Excel.

Выделите данные (ячейки A12:В22), которые нужно показать на Точечной диаграмме.

С помощью Вкладка Вставка/Диаграмма выбрать нужную позицию (а именно Точечную).

Если щелкнуть по диаграмме, то в ленте меню откроется Работа с диаграммами.

Тип диаграммы; макеты диаграмм, стили диаграмм, расположение диаграммможно изменить с помощью вкладки Конструктор.

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

С помощью вкладки Формат можно изменять: область диаграмм, стили фигур, стили WordArt и др.

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

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

Задания выполняются строго по вариантам, которые приведены в приложении.

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

Ø тему лабораторной работы, фамилию, имя; код группы;

Ø расчетные схемы;

Ø диаграммы;

Ø основные формулы,

Ø контрольный пример.