а) Использование формул и функций в MS Excel

Решение расчетных задач с использованием MS Excel

Каждую таблицу, форму или диаграмму следует размещать на отдельном листе в книге Excel.

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

Работа с формулами и диаграммами в MS Excel

 

а) Использование формул и функций в MS Excel

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

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

В качестве операторов вычислений используются арифметические, логические операторы и операторы сравнения: умножение (знак *), деление (знак /), сложение (знак +), вычитание (знак-), возведение в степень (знак ^), больше (знак >), меньше (знак <), равно (знак =), не равно (знак <>), больше или равно (знак ≥), меньше или равно (знак ≤) и целый ряд других.

Ссылки или адреса ячеек, диапазонов ячеек в формулах могут быть относительные и абсолютные. Относительная ссылка в формуле определяет адрес ячейки (диапазона ячеек), в которой находятся данные или переменная (или пустая ячейка), с которыми выполняются какие-либо действия. При копировании формул с относительными ссылками адреса ячеек (диапазона ячеек) изменяются на столько строк и столбцов, на сколько изменился адрес ячейки с формулой. Например, если в ячейке А1 имеется формула =В1+С1, то при копировании формулы в ячейку D10 формула примет вид =E10+F10. Но при выполнении операции перемещения формулы адреса относительных ссылок не меняются.

Абсолютная ссылка обозначается знаком $, который, ставится перед именем столбца или номером строки. Различают полную и частичные абсолютные ссылки: полная – $A$1 – при копировании формулы адрес ячейки не меняется, частичная – A$1 – при копировании формулы изменяется только адрес столбца, частичная $A1 – при копировании формулы изменяется только номер строки.

Функции – это программно определенные формулы, которые выполняют действия над определенными аргументами в определенном порядке. Функция всегда начинается с указания её имени, за которым в круглых скобках следую аргументы функции. Если аргументов больше двух они разделяются точкой с запятой. MS Excel содержит несколько сотен различных функций, которые можно вводить в формулы вручную или с помощью «Мастера Функций», используя пункт меню ВставкаðФункция (рис.7).

Для правильного ввода формул и функций следует придерживаться следующих правил:

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

б) Для ввода формул с несколькими функциями, например формулы cos(x)+sin(y), следует выделить ячейку ð выбрать пункт меню ВставкаðФункция ð в диалоговом окне Мастера функций выделить функцию COS (категория Полный алфавитный перечень), нажать Ок ð в диалоговом окне Аргументы функции COS ввести адрес ячейки с переменной х ð установить курсор в правой части Строки формул в конец введенной формулы ð выбрать оператор вычисления ð в левой части Строки формул раскрыть список функций, выбрать Другие функции ð используя Мастер функций выбрать функцию SIN и её аргумент ð нажать Ок. В результате будет введена формула, например =COS(В1)+SIN(С1);

в) Для ввода формул с вложенными функциями, например tg(sin(x²), следует выделить ячейку ð с помощью мастера функций выбрать функцию TAN (тангенс) ð не выбирая аргумент функции, в левой части Строки формул раскрыть список функций, выбрать Другие функции ð с помощью Мастера функций выбрать SIN ð не выбирая аргумента функции, выбрать функцию СТЕПЕНЬ и её аргументы – адрес ячейки с переменной и показатель степени ð нажать Ок. В результате будет введена формула, например =TAN(SIN(СТЕПЕНЬ(В1;2))).

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

 

Б) Построение диаграмм

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

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

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

Для Точечной и Пузырьковой диаграмм, для каждого ряда необходимо заполнить поля Значения Х и Значения У (и поле Размер для Пузырьковой). Для Круговой диаграммы вводится только один Ряд.

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

На четвертом шаге выбирается расположение диаграммы на отдельном листе или на имеющимся рабочем листе.

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

Каждый элемент диаграммы имеет свои способы форматирования в частности: Ряды данных – вид, фигура, подписи данных, порядок рядов, параметры; Оси – вид, шкала, шрифт, число, выравнивание.

Используя пункт меню Диаграмма можно изменить Тип диаграммы, Исходные данные, Параметры диаграммы, Размещение, а для объемных диаграмм Формат трехмерной проекции с помощью команды Объемный вид.

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

Самостоятельная работа – электронные таблицы Excel

Значения переменной Х изменяются от 1 до 3 с шагом 0,1

1. Рассчитайте по формулам приведенных в столбце А величину значений переменных У и У1 (по левой формуле в столбце А определить переменную У, по правой формуле правая переменную У1)

2. Оформление границ и фона таблицы и диаграмм – произвольный.

3. Пример: Рассчитать по формуле У= величину значений переменной У, если значения переменной Х изменяются от 1 до 2 с шагом 0,25

Решение:

4. Рассчитайте произведение переменных У1 если И переменная х>=1,5 И переменная У<=3,3 иначе найдите сумму переменных У1 (функции ЕСЛИ, И, СУММ, ПРОИЗВ).

Решение:

 

 

№ вар.	А (значения переменных У и У1найти по приведенным формулам)	В
			1. Рассчитайте переменную У2 если переменная х<=1,5, то переменную У1 умножить на 10, если х<=2, то переменную У1 умножить на 100, если х>2, то переменную У1 умножить на 1000 (функция ЕСЛИ). 2. Построить для всех переменных диаграммы: гистограмма объемная, график с маркерами.
			1. Рассчитайте переменную У2 если И переменная х<=1,5 И переменная У<=3,8, то переменную У1 умножить на 10, иначе переменную У1 умножить на 100 (функции ЕСЛИ, И) 2. Построить для всех переменных диаграммы: гистограмма объемная нормированная, с областями объемная с накоплением
			1. Рассчитайте переменную У2 если ИЛИ переменная х<=1,5 ИЛИ переменная У<=3, то переменную У1 умножить на 10, иначе переменную У1 умножить на 100. (функции ЕСЛИ, ИЛИ) 2. Построить для всех переменных диаграммы: гистограмма объемная с накоплением, график
			1. Рассчитайте сумму переменных Х и У если переменная х<=1,5 и количество переменных Х и У если переменная У<=4,0. и произведение переменных (функции СЧЕТЕСЛИ, СУММЕСЛИ, ПРОИЗВЕД). 2. Построить для всех переменных диаграммы: гистограмма обычная, точечная со значениями
			1. Рассчитайте сумму и количество переменных Х и У если переменная х<=1,5 и количество переменных Х и У если переменная У<=0, и произведение всех переменных (функции СЧЕТЕСЛИ, СУММЕСЛИ, ПРОИЗВЕД). 2. Построить для всех переменных диаграммы: гистограмма объемная в виде цилиндров, точечная без маркеров сглаженная.
			1. Рассчитайте среднее значение переменных У1 если переменная х<=1,5, иначе найдите минимум переменной У1 (функции ЕСЛИ, СРЗНАЧ, МИН) 2. Построить для всех переменных диаграммы: гистограмма объемная в виде конусов, лепестковая.
			1. Округлите значение переменных У1 если переменная х<=1,5, иначе найдите сумму переменных Х и У1 (функции ЕСЛИ, ОКРУГ, СУММ) 2. Построить для всех переменных диаграммы: гистограмма объемная, круговая объемная.
			1. Рассчитайте среднее значение переменных У1 если переменная х<=1,5, иначе найдите максимум переменной У1 (функции ЕСЛИ, СРЗНАЧ, МАХ) 2. Построить для всех переменных диаграммы: с областями объемная, круговая объемная разрезанная.
			1. Рассчитайте переменную У2 если переменная х<=1,5, то переменную У1 умножить на 10, если х<=2, то переменную У1 умножить на 100, если х>2, то переменную У1 умножить на 1000 (функция ЕСЛИ). 2. Построить для всех переменных диаграммы: гистограмма объемная, график с маркерами.
			1. Рассчитайте переменную У2 если И переменная х<=1,5 И переменная У<=3,8, то переменную У1 умножить на 10, иначе переменную У1 умножить на 100. (функции ЕСЛИ, И) 2. Построить для всех переменных диаграммы: гистограмма объемная нормированная, с областями объемная с накоплением
			1. Рассчитайте переменную У2 если ИЛИ переменная х<=1,5 ИЛИ переменная У<=3, то переменную У1 умножить на 10, иначе переменную У1 умножить на 100. (функции ЕСЛИ, ИЛИ) 2. Построить для всех переменных диаграммы: гистограмма объемная с накоплением, график
			1. Рассчитайте сумму переменных Х и У если переменная х<=1,5 и количество переменных Х и У если переменная У<=4,0., и произведение переменных (функции СЧЕТЕСЛИ, СУММЕСЛИ, ПРОИЗВЕД) 2. Построить для всех переменных диаграммы: гистограмма обычная, точечная со значениями
			1. Рассчитайте сумму и количество переменных Х и У если переменная х<=1,5 и количество переменных Х и У если переменная У<=0. (функции СЧЕТЕСЛИ, СУММЕСЛИ). 2. Построить для всех переменных диаграммы: гистограмма объемная в виде цилиндров, точечная без маркеров сглаженная.
			1. Рассчитайте среднее значение переменных У1 если ИЛИ переменная х<=1,5 ИЛИ переменная У<=8, иначе найдите минимум переменной У1 (функции ЕСЛИ, И, СРЗНАЧ, МИН) 2. Построить для всех переменных диаграммы: гистограмма объемная в виде конусов, лепестковая.
			1. Округлите значение переменных У1 если И переменная х<=1,5 И переменная У<=0,5, иначе найдите сумму переменных Х и У1 (функции ЕСЛИ, И, ОКРУГ, СУММ) 2.Построить для всех переменных диаграммы: гистограмма объемная, круговая объемная.