MS Excel

ОСНОВНЫЕ ВОЗМОЖНОСТИ MS EXCEL

Запустить MS Excel можно последовательностью команд Start / All Programs / Microsoft Excel (Пуск / Все программы / Microsoft Excel) или двойным щелчком по соответствующему ярлыку на рабочем столе MS Windows.

Основным документом MS Excel является рабочая книга - файл с произвольным именем и расширением .xls. Для создания новой книги из главного меню Файл выбирают команду Создать...

Листы

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

Операции с листами доступны из контекстного меню листа (рисунок 1).

Кроме того, можно:

переименовать лист - дважды щелкнуть по соответствующему ярлычку левой клавишей мыши, ввести имя листа, нажать клавишу Enter;
вставить новый рабочий лист - выполнить из главного меню команду Вставка / Лист;
удалить лист - выбрать из главного меню Правка / Удалить лист.

Некоторые операции с листами доступны из главного меню Формат, подменю Лист.

Рисунок 1 - Кнопки прокрутки, ярлыки и контекстное меню листов

Ячейки

Ячейка - первичный элемент таблицы, содержащий данные. Каждая ячейка таблицы имеет уникальный адрес, который состоит из имени столбца (A, B, …, Z, AA, AB, …, AZ, …, IV) и номера строки (нумеруются арабскими цифрами), например, B5 - ячейка, стоящая на пересечении столбца B и пятой строки.

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

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

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

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

щелкают левой клавишей мыши в строке формул по редактируемым данным;
дважды щелкают левой клавишей мыши непосредственно по редактируемой ячейке;
нажимают клавишу Backspace (<-). При этом все данные удаляются.

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

Данные в ячейках

В активную ячейку MS Excel можно поместить:

текст (текстовые данные) - по умолчанию выравнивается по левому краю ячейки;
число (числовые значения) - выравнивается по правому краю;
формулу - начинается со знака "=" (равно).

Формула - последовательность значений, адресов или имен ячеек, функций, арифметических операций и скобок, которая начинается со знака "=" (равно), например, =2*SIN(A4)+$B$5.

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

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

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

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

#ЗНАЧ! - при использовании недопустимого типа аргумента или операнда, например, при сложении числовых и текстовых данных;
#ИМЯ? - отображается, если MS Excel не может распознать имя, используемое в формуле;
#ДЕЛ/0! - отображается при попытке деления на 0;
#ССЫЛКА! - при использовании в формуле недопустимой ссылки на ячейку и др.

Форматирование ячеек

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

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

Формулы и функции

В MS Excel имеется большое количество встроенных функций, которые можно использовать в формулах. Для этого достаточно указать их имя и, как правило, аргумент или список аргументов, заключенных в круглые скобки. Аргументы функций отделяются друг от друга знаком ";" - точка с запятой. В качестве аргументов могут использоваться числа, адреса или имена ячеек, диапазоны, арифметические выражения и функции.

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

=СУММ(A3:C5;A10:C10)

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

=SIN(Лист2!А3)

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

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

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

ABS(число) - возвращает модуль (абсолютную величину) числа;
COS(число) - возвращает косинус угла;
EXP(число) - экспонента заданного числа;
LN(число) - натуральный логарифм;
LOG(число; основание) - логарифм числа по указанному основанию;
SIN(число) - возвращает синус угла;
TAN(число) - возвращает тангенс угла;
КОРЕНЬ(число) - возвращает значение квадратного корня;
ОКРУГЛ (число; число_разрядов) - округляет число до указанного количества десятичных разрядов;
ПИ() - число пи, округленное до 15 знаков после запятой;
ПРОИЗВЕД(число1; число2; ...) - произведение аргументов;
СЛЧИС() - случайное число, равномерно распределенное на диапазоне [0; 1). Используется без аргументов;
СТЕПЕНЬ(число; степень) - результат возведения в степень;
СУММ(число1; число2; ...).

Статистические функции:

МАКС(число1; число2; ...) - возвращает максимальное значение из списка аргументов;
МИН(число1; число2; ...) - минимальное значение из списка аргументов;
СРЗНАЧ(число1; число2; ...) - среднее арифметическое аргументов;
СЧЁТЗ(значение1; значение2; …) - количество значений в списке аргументов и непустых ячеек.

Графические возможности MS Excel. Построение графика функции

Для построения графиков и диаграмм в MS Excel предназначен Мастер диаграмм, вызываемый последовательностью команд главного меню Вставка / Диаграмма… или нажатием на кнопку панели инструментов Стандартная.

При этом отобразится окно Мастера диаграмм, работа с которым проводится в четыре этапа (шага):

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

4 Место размещения диаграммы - на имеющемся или на отдельном листе - определяется на последнем, четвертом шаге Мастера.

Порядок выполнения лабораторной работы

1 Запустите MS Excel: Start / All Programs / Microsoft Excel (Пуск / Все программы / Microsoft Excel).

2 Сохраните рабочую книгу в личной папке на диске z:\ под именем ФИО. Производите сохранение регулярно в процессе работы (Ctrl + S).

3 Добавьте в рабочую книгу четыре новых листа (Вставка / Лист или команда Добавить… из контекстного меню ярлычка листа).

4 Переименуйте листы рабочей книги следующим образом: Задание 1, Формулы 1, Задание 2, Формулы 2, Задание 3, Задание 4, Формулы 4. Для переименования щелкните дважды по тексту ярлычка листа левой клавишей мыши, введите новое название листа и нажмите клавишу Enter.

5 Перейдите на лист Задание 1 и выполните

Задание 1. Вычислить значения функции при b1 = 1,3, если m изменяется на промежутке [5; 11] с шагом Δm = 0,6.

Решение.

1) Введем в ячейку А1 текст "b1=".

В ячейку B1 введем текст "m="; в ячейку C1 введем "fun1=".

Примечание - В активную ячейку MS Excel можно ввести либо текст, либо число, либо формулу. Если ввести в одну ячейку b1=1,3, то эта запись будет восприниматься как текст, с которым невозможно выполнять математические операции.

2) Числовое значение для b1 введем в ячейку A2: 1,3.

В MS Excel в качестве десятичного разделителя используется запятая.

3) В ячейку B2 введем начальное значение переменной m, равное 5.

4) В ячейку B3 введем значение 5,6, которое подсчитывается как начальное значение плюс шаг, т. е. m = 5 + Δm = 5 + 0,6 = 5,6.

5) Выполним автозаполнение ячеек значениями переменной m.

Для этого выделим две ячейки B2 и B3, содержащие первое и второе значения этой переменной. В этом случае запоминается шаг изменения аргумента - разность между вторым и первым значениями.

Наведем указатель мыши на правый нижний угол рамки, выделяющей ячейки B2 и B3 так, чтобы он принял вид тонкого черного крестика. Нажав в этом положении левую клавишу мыши, протянем маркер заполнения вниз, не отпуская, пока на всплывающей подсказке не отобразится последнее значение переменной m, равное 11.

6) В ячейку C2 введем формулу для вычисления значений функции fun1(m). Набор формулы начнем со ввода знака "=".

Для вставки функции вычисления квадратного корня вызовем мастер вставки функции нажатием на кнопку на панели инструментов или командой Вставка / Функция. Выберем категорию функций Математические. Выберем функцию КОРЕНЬ и нажмем кнопку OK. Появится окно:

Квадратный корень извлекается из переменной m. Но в качестве аргумента функции в Excel используется не имя переменной, а адрес ячейки, в которой находится ее значение, например, B2. Причем для ввода этого адреса достаточно щелкнуть левой клавишей мыши по соответствующей ячейке. При этом в строке формул отобразится =КОРЕНЬ(B2), а ячейка B2 выделится мерцающей рамкой.

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

Для набора знака деления перейдем в строку формул и напечатаем "/" (можно использовать клавишу дополнительной клавиатуры).

В строке формул откроем скобку (Shift + 9) для набора выражения из знаменателя дроби. Так как окно Мастера функций открыто, для возведения тангенса в квадрат обратимся к списку в начале строки формул, откуда выберем пункт Другие функции…:

В категории функций Математические найдем функцию СТЕПЕНЬ и нажмем кнопку OK:

В строке Степень напечатаем число 2 и перейдем в строку Число, чтобы ввести тангенс переменной m. Для этого снова обратимся к списку функций слева от строки формул (Другие функции…) и в категории Математические выберем TAN. В строке Число укажем B2, щелкнув для этого по ячейке B2, содержащей число 5:

В строке формул отобразится: =КОРЕНЬ(B2)/(СТЕПЕНЬ(TAN(B2);2).

Перейдем в строку формул, наберем знак "+" и адрес ячейки A2, где находится значение константы b1. Так как это значение единственное, то его следует зафиксировать, создав абсолютную ссылку. Для отображения знаков "$" установим курсор на A2 и нажмем клавишу F4.

Закроем скобку (Shift + 0) и нажмем клавишу Enter. Окончательно формула выглядит так:

=КОРЕНЬ(B2)/(СТЕПЕНЬ(TAN(B2);2)+$A$2)

7) Выполним автозаполнение столбца С значениями функции fun1 для соответствующих значений переменной m из столбца B. Для этого активизируем ячейку C2 и наведем указатель мыши на правый нижний угол ее рамки до изменения его на тонкий черный крестик. Протянем маркером заполнения вниз до последнего значения переменной m.

Решение рассмотренной задачи в численном виде и в режиме отображения формул приведено ниже.

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

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

Решение.

1) Для построения графика выделим диапазон ячеек С1:С12 со значениями функции fun1(m).

2) Вызовем Мастер диаграмм, выбрав из главного меню команду Вставка / Диаграмма или щелкнув по кнопке на панели инструментов Стандартная.

3) Выберем тип диаграммы на вкладке Стандартные в левой части окна - Точечная. Выберем вид диаграммы справа, например, диаграмма со значениями, соединенными сглаживающими линиями - первая во втором ряду. Нажмем кнопку Далее> для перехода ко второму шагу Мастера диаграмм.

4) На вкладке Диапазон данных автоматически указано: "=Лист1!$C$1:$C$12" и отмечено: "Ряды в столбцах", т. к. диапазон данных для построения графика был предварительно выделен.

5) Для указания диапазона значений переменной m откроем вкладку Ряд, поместим текстовый курсор в пустую строку Значения X:, и нажмем кнопку справа от этой строки:

Окно мастера диаграмм свернется в строку, предоставляя возможность выделить диапазон ячеек B1:B12.

Восстановить окно Мастера диаграмм можно, нажав на кнопку . В результате строке Значения X: отразится:

=Лист1!$B$1:$B$12

Нажмем кнопку Далее> для перехода к шагу 3 Мастера диаграмм.

6) В пустой строке Название диаграммы напечатаем "Значения функции fun1"; в строке Ось X (категорий) - "аргумент m"; в строке Ось Y(значений) - "fun1".

7) Перейдем на вкладку Оси, на которой должны быть установлены следующие отметки:

8) На вкладке Линии сетки установим значки в строках основные линии по оси X и по оси Y.

9) Перейдем на вкладку Легенда, где уберем значок в строке Добавить легенду.

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

11) Нажмем кнопку Далее> для перехода к последнему шагу Мастера диаграмм. Для размещения диаграммы на текущем листе нажмем кнопку Готово.

Диаграмма будет выглядеть следующим образом:

Примечания.

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

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

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

7 Перейдем на лист Задание 2 и решим задачу обработки одномерных числовых массивов в пакете Excel.

Задание 2. Ввести в массив MS, состоящих из десяти произвольных чисел и значение р9 = 5,7. Для каждого элемента MS_i вычислить . Подсчитать сумму и произведение значений RES. Найти минимальное значение массива RES. Подсчитать количество значений RES, больших числа 5.

Решение.

1) Введем поясняющий текст.

В ячейке А1 напечатаем: р9=. В ячейку В1 введем: MS=, в ячейку С1 введем RES=.

2) В ячейку А2 введем числовое значение р9, а именно, 5,7.

3) Заполним ячейки В2 - В11 произвольными значениями массива MS.

4) В ячейке С2 наберем формулу, используя Мастер формул (кнопка на панели инструментов Стандартная):

=СТЕПЕНЬ(В2+$A$2;1/3)+ABS(B2)

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

5) Выполним копирование формулы на диапазон ячеек С2:С11, наведя указатель мыши на правый нижний угол рамки ячейки С2, пока он не примет вид тонкого черного креста и протянем, не отпуская, левой клавишей мыши до ячейки С11.

6) Введем в ячейку В13 поясняющий текст: СУММА(RES)=.

7) Для вычисления суммы элементов массива RES перейдем в ячейку С13 и нажмем кнопку (Автосумма) на панели инструментов Стандартная. В ячейке С13 и в строке формул появится формула =СУММ (С2:С12) Откорректируем ее, изменив ссылку С12 на С11. Для этого выделим диапазон ячеек С2:С11, используя указатель мыши, либо просто установим текстовой курсор на ссылке С12 в формуле, а затем щелкнем указателем мыши по ячейке С11. Зафиксируем формулу вычисления суммы нажатием клавиши Enter на клавиатуре.

8) В ячейку В14 введем вспомогательный текст MIN(RES)=. Перейдем на ячейку С14, в которой напечатаем знак "=".

Запустим Мастер функций с панели Стандартная. Выберем категорию Статистические. В перечне функций найдем МИН и нажмем кнопку OK. Укажем диапазон поиска минимального элемента С2:С11. В ячейке С14 отобразится минимальный элемент массива RES.

9) Вычисление произведения производится аналогичным образом с помощью функции ПРОИЗВЕД(С2:С11) из категории Математические.

10) Для вычисления количества значений массива RES, больших числа 5, используем функцию СЧЁТЕСЛИ(диапазон; критерий) из категории Статистические.

Решение задачи в режиме отображения формул приведено ниже.

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

Понятие и сфера применения электронных таблиц.
Как настроить внешний вид окна и панели инструментов MS Excel ?
Какие элементы интерфейса MS Excel отличают ее от других программ?
Рабочая книга MS Excel . Настройка основных свойств.
Операции с листами рабочей книги.
Ячейка. Редактирование данных в ячейке электронной таблицы MS Excel .
Данные в ячейках.
Ввод формулы в ячейку. Копирование формул.
Ссылка на ячейку. Относительные, абсолютные, частично-абсолютные ссылки.
Наиболее часто встречающиеся ошибки в формулах.
Форматирование ячеек. Форматирование электронной таблицы.
Использование мастера функций.
Основные категории встроенных функций в MS Excel .
Основные математические функции.
Основные статистические и логические функции.
Графические возможности MS Excel . Мастер диаграмм.
Форматирование графиков и диаграмм.