НИЗКОТЕМПЕРАТУРНЫХ И ПИЩЕВЫХ ТЕХНОЛОГИЙ

Федеральное агентство по образованию

 

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

САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
НИЗКОТЕМПЕРАТУРНЫХ И ПИЩЕВЫХ ТЕХНОЛОГИЙ

 

Е.Б. Петрунина, Е.Г. Селина

 

ОБРАБОТКА ЧИСЛОВОЙ

ИНФОРМАЦИИ В EXCEL

 

 

Учебное пособие

 

 

Санкт-Петербург 2005

 

УДК 681.3

ББК 32.973.26-018.2

Петрунина Е.Б., Селина Е.Г.

А65Обработка числовой информации в EXCEL: Учеб. пособие. – СПб.: СПбГУНиПТ, 2005. – 110 с.

 

Данное пособие составлено применительно к разделу "Работа в Microsoft office", изучаемому в рамках курса информатики в Санкт-Петербургском государственном университете низкотемпературных и пищевых технологий. В пособии дано описание команд Excel, необходимых для ввода данных, проведения расчетов, форматирования результатов и построения диаграмм. Разобраны способы реализации в Excel основных вычислительных алгоритмов. Приведено более 100 упражнений для приобретения устойчивых навыков работы с командами.

Учебное пособие предназначено для студентов 1 и 2-го курсов всех направлений (бакалавриат) и специальностей очной и заочной форм обучения.

УДК 681.3

ББК 32.973.26-018.2

 

Рецензенты

Кафедра общих математических и естественнонаучных дисциплин НОУ ВПО ИБП (зав. кафедрой канд. физ.-мат. наук, доц. Д.К. Потапов)

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

 

Рекомендовано к изданию редакционно-издательским советом университета

 

ã	Санкт-Петербургский государственный университет низкотемпературных и пищевых технологий, 2005

СПИСОК УСЛОВНЫХ ОБОЗНАЧЕНИЙ

· <Ctrl + Home> – одновременное нажатие клавиш, названия которых указаны между < >; · ® – движение по указанным пунктам меню для заказа нужного действия,… · названия программ, окон, вкладок пишутся с большой буквы, названия параметров, которые надо определить в диалоговых…

ВВЕДЕНИЕ

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

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

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

Электронные версии представленных ниже заданий содержат всю вспомогательную набивку и позволяют сосредоточиться только на командах Excel. Их можно получить у заведующего лабораторией кафедры прикладной математики. Для более полного ознакомления с возможностями табличного процессора Excel и другими примерами их использования можно обратиться, например, к литературным источникам [1– 4].

 

ОСНОВНЫЕ КОМАНДЫ EXCEL

Основные понятия Excel

Главное окно Excel аналогично окнам других приложений Windows. Его вид представлен на рис. 1.1.1.

Файл документа, созданного в Excel, имеет расширение .xls и называется рабочей книгой. Книга состоит из отдельных листов. На них можно располагать данные, формулы, которые их обрабатывают, пояснительные тексты, индивидуальные программы пользователя для сложной обработки данных и т. д.

Листам можно присваивать удобные имена или пользоваться шаблонными именами "Лист1", "Лист2" и т. д. Заказ листа для работы – щелчок левой кнопкой мышки на ярлычке листа, изменение имени – двойной щелчок на нем и затем набор нового имени вместо выделенного старого. Листы можно располагать в удобном для пользователя порядке. Для этого нужно просто отбуксировать мышкой ярлычок листа на новое место.

Рабочее поле листа разбито на ячейки. Каждая ячейка находится на пересечении столбца и строки. Столбцы обозначаются буквами латинского алфавита, строки – числами. Их названия указаны в адресной линейке и адресном столбце, которые выделены серой заливкой. В адресе ячейки первым указывается столбец. Адрес активной ячейки, в которую поступает информация, набираемая на клавиатуре, высвечивается в адресном поле строки формул. Примеры адресов ячеек: Е2, V5, A78, XY12345 и т. д. (но не 2Е, 5V, 78A, 12345XY). Такие адреса называются двумерными.

Если нужная ячейка располагается на другом листе, то перед ее адресом указывается имя этого листа и ставится восклицательный знак. В этом случае адрес называется трехмерным. Примеры таких адресов: Форматы!G12345, Лист4!А10, Март!С1 и т. д.

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

Рис. 1.1.1. Вид окна при работе с EXCEL–документом:

1 – строка заголовка; 2 – панели инструментов; 3 – адресное поле строки формул; 4 – адресные линейки (столбец и строка); 5 – рабочий лист; 6 – ярлычки рабочих листов; 7 – строка меню; 8 – информационное поле строки формул; 9 – полосы прокрутки текста

 

Группа ячеек, которая обрабатывается одной командой, называется блоком. Прямоугольный блок ячеек задается адресами концов любой его диагонали, указанными через ":". Если блок имеет сложную форму, его разбивают на прямоугольные фрагменты и перечисляют их координаты через ";". Примеры блоков: А3:А3 – блок из одной ячейки, B2:D4 – блок из девяти ячеек (B2, C2, D2, B3, …, D4), КварталI!Е2:Е4 – фрагмент столбца (Е2, Е3, Е4) на листе с именем КварталI, А3:А6;D8:D12 – сложный блок из двух фрагментов столбцов.

Если требуется сослаться на столбец (строку) целиком, в адресе указывают только одну компоненту. Такие адреса называют одномерными. Примеры: А:С – блок состоит из всех ячеек столбцов А, В и С, 12:34 – все ячейки строк с 12-й по 34-ю.

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

Выделение блока ячеек

Приемы выделения прямоугольных блоков: · Клавишами – <Shift + клавиши перемещения курсора>. · Мышкой – курсор в форме объемного крестика протянуть по нужным ячейкам.

Ввод текстов

Текст вводится обычным способом, как на пишущей машинке или в документ Word. По умолчанию он прижимается к левому краю ячейки. Если в тексте часто встречаются повторяющиеся слова или выражения, то ввод можно ускорить с помощью команды Сервис ® Автозамена…, которая действует так же, как в Word.

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

Ввод чисел

При вводе арифметических чисел целая часть отделяется от дробной запятой, а не точкой, как это принято в большинстве языков программирования. На… Арифметические числа можно также представлять в экспоненциальном виде.… Если после числа без пробелов ввести символ %, то в память заносится значение в 100 раз меньшее, чем было введено. Это…

Примечание

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

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

1. Ввести два первых числа ряда.

2. Выделить эти ячейки.

3. Протянуть их (см. подразд. 1.2) в том же направлении, в каком вводились первые числа, до тех пор, пока во всплывающем окне рядом с курсором не появится нужное конечное значение.

Для ввода чисел, меняющихся по геометрической прогрессии, следует ввести два первых числа, выделить весь блок, который следует заполнить, и воспользоваться командой Правка ® Заполнить ® Прогрессия…

Задания

· Введите в ячейки А1:А11 ряд чисел 1; 1,3; 1,6; …; 4.

· Заполните ячейки В1:В21 теми же числами, но введенными через строчку (два первых числа вводим через строчку, выделяем В1:В4, протягиваем до В21).

· Введите в ячейки С1:С21 ряд геометрической прогрессии, который начинается с чисел 1; 1,3.

· Закажите для введенных в столбец А чисел денежный формат.

Ввод стандартных списков

Набор стандартных списков можно дополнять. Для этого следует: 1. Выполнить команду Сервис ® Параметры… (вкладка Списки). 2. В параметре "Списки" сделать одинарный щелчок левой кнопкой мышки по строке "Новый список". …

Ввод формул

Ввод любой формулы обязательно начинают со знака "=" и заканчивают нажатием клавиши <Enter>. После этого текст формулы высвечивается… Если в результате работы формулы рассчитывается несколько значений, то перед… Обозначение арифметических действий и очередность их выполнения таковы: Действие Возведение в…

Присваивание имен ячейкам и блокам

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

Использование подписей данных

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

Работа с Мастером функций

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

Правка информации

Для ввода одинаковых исправлений в несколько ячеек удобно пользоваться командой Правка ® Заменить… Эта команда аналогична такой же команде Word.… Для очистки ячеек выделяют нужный блок и нажимают <Del> или выполняют… Отменить неверные изменения до выхода из режима правки можно клавишей <Esc>, после выхода – горячими клавишами…

Копирование и перемещение информации

Источник – блок, в котором первоначально размещена информация. Адресат – блок, в котором она размещена после выполнения команды. Техника выполнения этих команд та же, что и в программе Word, а именно:

1. Выделяют блок-источник.

2. Выполняют команду Вырезать или Копировать, которую можно заказать в меню Правка, на стандартной панели инструментов или в контекстном меню, которое вызывается щелчком правой кнопки.

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

4. Выполняют команду Вставить, которую можно заказать в меню Правка, на стандартной панели инструментов или в контекстном меню.

Примечания

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

· При копировании в ячейки, смежные с источником, можно после выделения блока-источника воспользоваться протяжкой (см. подразд. 1.2, 1.6).

Особенности выполнения операций перемещения и копирования:

· Если перемещаемому блоку было присвоено имя, то оно сохраняется за ним на новом месте.

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

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

Иногда формулы содержат ссылки на данные, которые в копиях должны быть теми же, что и в источнике, но замена их стандартных адресов на имена не выполнена. Тогда в стандартный адрес следует ввести символ "$". Если этот символ помечает и столбец, и строку в адресе, то адрес называют абсолютным, если же что-то одно, – то смешанным. Адреса, которые следует изменять при копировании по стандартному правилу, называют относительными. Они символ "$" не содержат.

Примеры

$D$4 – данное всегда берется из ячейки D4 при копировании формулы в любом направлении. Этот адрес абсолютный.

D$4 – закреплена только строка. Если, к примеру, копия формулы с этой ссылкой расположена на два столбца правее источника, то ссылка на данное поменяется на F$4; если же копия находится двумя строками ниже, то данное по-прежнему берется из D4. Это пример смешанной ссылки.

Закрепление всего адреса или его частей при наборе формулы удобно делать повторными нажатиями клавиши <F4>.

Примечания

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

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

Задание

Установите в команде Сервис ® Параметры… (вкладка Вычисления) флажок на параметре "Допускать названия диапазонов".

Введите в ячейки А1:F1 тексты "h", "x", "y", "z", "k", "v". Ячейке А2 присвойте имя h и введите в нее число 10. В В2:В10 введите какие-нибудь числа, в С2 – формулу =В2*h, в D2 – формулу =x*h. Протяните С2:D2 вниз до строки 10. Выделите D2:D10 и протяните этот блок на столбцы E, F. Обратите внимание на то, как выглядят формулы в этих ячейках и какие результаты получены по ним.

Теперь в ячейке C2 закрепите адрес В2 ($В$2) и протяните ее вниз, а затем вбок. Посмотрите, как изменится вид формулы и результат. Закрепите часть адреса В2 и повторите процедуру.

Специальная вставка

Для того чтобы выполнить специальную вставку, на четвертом шаге операции копирования вместо обычной команды Правка ® Вставить заказывают команду… Задание Заполните блок А1:А10 числами 1, 2, …, 10, ячейку В1 – формулой =А1*2 и протяните ее на блок В2:В10. В блоке В1:В10…

Простейшее форматирование ячеек

К основным командам форматирования относятся: · Изменение ширины столбцов (строк). Курсор мышки располагается в адресной… · Выравнивание информации в пределах ячейки. В панели Форматирование этим управляют кнопки с макетами строк,…

Стандартное форматирование чисел

, (запятая) – отделяет целую часть от дробной; . (точка) – разделяет указатели дней, месяцев и года в дате; : (двоеточие) – разделяет указатели часов, минут и секунд при вводе времени;

Нестандартное форматирование чисел

1. Выделяется группа ячеек, подлежащая форматированию. 2. Выполняется команда Формат ® Ячейки... (вкладка Число). 3. В списке "Числовые форматы" выбирается строка "Все форматы".

Условное форматирование

В диалоговом окне этой команды в параметре "Условие1" следует скомпоновать в текстовых полях условие на значение, попадающее в… Если возможны другие варианты значений, которые хотелось бы оформить особым… Команда допускает до трех разных вариантов оформления одной и той же ячейки. Значения, которые не подходят ни под одно…

Нестандартное условное форматирование

1. Если в формате указано от одной до трех секций, то они используются только для чисел; текстовая информация представляется стандартным способом.… · если в формате указана только одна секция, то она используется как обычный… · если в формате указаны две секции, то первая используется для положительных и нулевых чисел, вторая – для…

ПРОСТЕЙШИЕ ВЫЧИСЛИТЕЛЬНЫЕ АЛГОРИТМЫ

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

Расчет таблицы значений функции от двух аргументов

Рассмотрим пример. Составим таблицу функции от двух переменных z = y2xln(x + y) для диапазонов 2 < x < 6 и 1 < y < 5. Будем менять х с шагом 0,4; у – с шагом 0,8. Заполним Рабочий лист в… Таблица 2.2.1 Ячейки Информация Значения А1 Заголовок расчета Функция…

Примечание

Если перед вводом формулы в С4 присвоить диапазонам С3:Н3 и В4:В14 имена соответственно "у" и "х", то формулы в С4 можно ввести в более понятном виде: =у^2*x-LN(x+y) (см. подразд. 1.8, 1.11).

Задание

Отформатируйте полученную таблицу (см. подразд. 1.13):

· объедините ячейки С2:Н2;

· объедините ячейки А4:А14, измените в них направление текста на вертикальное;

· выделите цветом шрифта или заливкой заголовки таблицы (С2:Н3 и А4:В14);

· обведите толстой рамкой всю таблицу и отделите такой же рамкой заголовки с аргументами от значений функции;

· разделите тонкими линиями столбцы таблицы;

· разделите пунктирными линиями строки таблицы.

3. ЗАДАЧИ И УПРАЖНЕНИЯ НА ОСНОВНЫЕ
КОМАНДЫ И ПРОСТЕЙШИЕ АЛГОРИТМЫ

Простейшие манипуляции

3.1.2. Введите на Лист1 информацию, указанную на рис. 3.1.1. Установите в ячейке В2 следующий формат: размер шрифта – 26 пт., цвет шрифта – красный,… Рис. 3.1.1 3.1.3. Присвойте ячейкам А5 и F3 (см. рис. 3.1.1) имена соответственно Делимое и Делитель (команда Вставка ® Имя… ®…

Нестандартные имена ячеек и подписи диапазонов

Рис. 3.2.1 Подсказка 1. Поставьте курсор на ячейку А3.

Разлиновка сложных таблиц

Эти задачи выполняются на разных листах в одной книге. Составляемые в них таблицы взаимосвязаны. В каждой таблице часть данных берется из предыдущих… 3.3.1. Создайте в Excel бланк для расчета объемов производства по образцу… Таблица 3.3.1 Продукт Объем производства Количество смен Доля в общем объеме, % …

Таблица 3.3.2

Продукт	Объем производства, кг/смена	Время работы единицы оборудования, час/смена	Выбранное оборудование	Количество единиц оборудования
Марка	Производительность единицы оборудования
Кг/час	Кг/смена
Итого:

3.3.3. Создайте в Excel бланк для расчета стоимости оборудования по образцу табл. 3.3.3. Введите с помощью ссылочных формул нужные данные из табл. 3.3.1 и табл. 3.3.2 в графы 1, 2, 3. Для графы 4 придумайте новые данные и введите их с клавиатуры. В графу 5 введите расчетные формулы и подведите итог.

Таблица 3.3.3

Продукция	Выбранное оборудование	Балансовая стоимость
Марка	Количество оборудования	единицы оборудования, р.	всего оборудования, тыс. р.
Итого:

3.3.4.Создайте в Excel бланк для расчета площадей, занимаемых технологическим оборудованием по образцу табл. 3.3.4. Введите с помощью ссылочных формул нужные данные из табл. 3.3.3 в графы 1 и 2. Для графы 3 придумайте геометрические размеры единицы каждого вида оборудования и введите расчет площади по формуле. Пример: =2,5*3,8 . В графу 4 введите расчетные формулы для определения необходимой площади под все оборудование и подведите итог. Создайте круговую диаграмму, иллюстрирующую соотношение площадей, необходимых под оборудование для производства каждого продукта.

Таблица 3.3.4

Марка оборудования	Количество оборудования	Занимаемая площадь, м2
единицы оборудования	всего оборудования
Итого:

3.3.5. Создайте в Excel бланк для расчета материальных затрат по сырью по образцу табл. 3.3.5. Введите с помощью ссылок нужные данные из табл. 3.3.1 в графы 2 и 3. В графы 1, 4 и 6 придумайте и введите с клавиатуры название сырья, расход его на 1 тонну продукции и его стоимость. В графы 5, 7 и 8 введите расчетные формулы для определения соответствующих величин и подведите итоги.

Таблица 3.3.5

Вид сырья	Продукт	Годовой объем производства	Расход сырья, т	Стоимость сырья, р.
на тонну продукции	на весь объем производства	одной тонны	на 1 тонну продукции	на весь объем продукции
Итого:

3.3.6. В табл. 3.3.6 приведен бланк для расчета заработной платы.

Таблица 3.3.6

ФИО	Оклад	Отрабо­тано дней	Пре­мия	Начис­лено	Отчисления	Выдать на руки
Пенсион­ный фонд (1%)	Подоход­ный налог (12%)	Всего удер­жано
Кряк	7000р		2000р
…	…	…	…	…	…	…	…	…

Создайте аналогичный бланк для отдела из пяти человек. Значения в графах "ФИО", "Оклад", "Отработано дней", "Премия" придумайте сами, остальные графы в первой строке заполните формулами и скопируйте их на нижние строки. В ячейках с денежными данными форматированием добавьте символы валюты. Количество рабочих дней в месяце введите в отдельную ячейку, например, равным числу 21.

3.3.7. Коэффициент трудового участия (КТУ) используют на таком производстве, где невозможно объективно оценить индивидуальное количество и качество труда. В этом случае каждый работник по окончании отчетного периода выставляет КТУ каждому из коллег (число от 0 до 1), и потом выводятся средние показатели:

· "Всего КТУ" = сумма всех оценок работника.

· "КТУ работника" = "Всего КТУ" / "Сумма КТУ бригады".

· "КТУ работника" = "КТУ работника" / максимальный "КТУ работника", который находится по функции МАКС().

Оформите табл. 3.3.7 в Excel и введите эти формулы в соответствующие графы.

Таблица 3.3.7

Работники	Оценки работникам	Всего КТУ	КТУ работника	КТУ работника
Петр	0,9	0,7	0,7	0,9
Иван	0,6	0,55	0,6	0,4
Олег	0,8	0,8	0,55	0,8
Вера	0,9	0,9	0,45
Сумма КТУ бригады

ПОСТРОЕНИЕ ДИАГРАММ

Мастер диаграмм заказывается командой Вставка ® Диаграмма… или кнопкой Мастер диаграмм в стандартной панели инструментов (разноцветные вертикальные… 4.1. Первый шаг Мастера диаграмм: тип диаграммы Выбор типа диаграммы зависит от характера данных, которые будут отображаться. Пояснения к каждому типу диаграммы…

Исправление диаграммы

Одинарный или двойной щелчок левой кнопкой мышки по любому элементу диаграммы приводит к тому, что элемент, на который указывал курсор, выделяется… После выделения нужного элемента в меню Формат первой строкой появляется… Перейти в режим правки можно также, сделав щелчок правой кнопкой по любому элементу диаграммы. При этом открывается…

Построение линии тренда

Команду вставки тренда в диаграмму можно вызвать из меню Диаграмма или из контекстного меню для нужного ряда данных. Окно Линия тренда, которое… На вкладке Тип приведены графики стандартных линий тренда (если аргументы… · линейная – уравнение y = mx + b;

Таблица 5.1

t, оС

–3

–17

–6

–3

–17

Сбыт, кг

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

Таблица 5.2

Неделя

Продано товара, шт.

?

?

5.3. Введите и отформатируйте ведомость оплаты счетов за IV квартал (табл. 5.3). Графу "Долг" и строку "Общий итог" заполните с помощью формул.

Таблица 5.3

Фирма	Сумма в счете	Сумма оплаты	Долг
"Василек"	$600	$550	$50
"Гвоздика"	$400	$300	$100
"Ландыш"	$900	$900	$0
"Ромашка"	$800	$800	$0
Общий итог	$2 700	$2 550	$150

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

5.4. Изобразите на круговых диаграммах показатели Сумма в счете и Сумма оплаты из табл. 5.3. Отформатируйте эти диаграммы разными способами и сравните разные варианты подписей около секторов.

5.5. Изобразите на гистограммах с накоплением данные о сумме счетов за I квартал (табл. 5.4). В первом варианте ряды данных относятся к разным месяцам, во втором – к фирмам. Представьте эти диаграммы в абсолютном и процентном масштабе.

Таблица 5.4

Фирма	Месяц	Итого
январь	февраль	март
"Василек"	$200	$300	$100	$600
"Ландыш"	$500	$400	$200	$1 100
"Ромашка"	$300	$600	$400	$1 300
Итого:	$1 000	$1 300	$700

5.6. Составьте таблицу значений функций x(t) = sin(kt)cos(t) и
y(t) = sin(kt)sin(t) в соответствии с рис. 5.1.

	A	B	C	D	E	F
	Параметр функций	Расчет шага по аргументу
	k =		tнач	tкон	число шагов	шаг
			–3,14	3,14
	t	x(t)	y(t)

Рис. 5.1

Формулу для расчета длины шага составьте самостоятельно. Постройте две точечные диаграммы по полученной таблице. В первой диаграмме изобразите функции x(t) и y(t) (по горизонтальной оси значения t, по вертикальной – два ряда данных: x(t) и y(t)). Во второй – изобразите неявную зависимость y(x) (по горизонтальной оси значения х, по вертикальной – значения y). Посмотрите, как меняется вид диаграмм при изменении значения параметра k (k = 1, 2, 3, ...).

5.7. В табл. 5.5 представлена ежедневная выручка пяти филиалов магазина в течение недели. Оформите эти данные в виде таблицы Excel. Введите формулы для итоговой выручки. Для чисел используйте денежный формат. Дни недели вводите протяжкой. Изобразите эти данные в виде двух гистограмм с накоплением. На первой в качестве рядов данных рассматривайте выручку того или иного филиала по дням недели (по горизонтальной оси откладываются дни недели). На второй – выручку всех филиалов в определенный день недели (по горизонтальной оси – номера филиалов).

Таблица 5.5

День недели	Филиал	Всего за день
№ 1	№ 2	№ 3	№ 4	№ 5
Понедельник	30 250	25 940	35 970	19 430	15 760
Вторник	28 400	25 820	35 590	17 830	18 590
Среда	35 240	24 760	36 120	15 120	23 790
Четверг	32 680	26 550	34 540	19 560	22 320
Пятница	34 630	27 230	33 170	20 890	20 210
Суббота	26 570	26 120	32 850	22 360	17 530
Воскресенье	24 780	25 310	30 390	23 150	16 910
Всего
Общая выручка за неделю:

5.8. В табл. 5.6 представлены данные о численности населения в некоторой стране за ряд лет. Представьте эти данные на диаграмме, подберите тренд и сделайте прогноз численности населения на следующие 5 лет.

Таблица 5.6

Год	Население, тыс. чел.	Год	Население, тыс. чел.	Год	Население, тыс. чел.	Год	Население, тыс. чел.

5.9. В табл. 5.7 представлена ведомость, которая заполняется по мере поступления заказов от покупателей на определенный товар. В нее также внесены графы, обобщающие характеристики покупок. Оформите эту ведомость в виде таблицы Excel, рассчитанной на 20–40 покупателей, придумайте и введите объемы заказов в графу 2. Введите в отдельную ячейку цену на товар и заполните формулами графы 3–7. Графы 6 и 7 проиллюстрируйте диаграммами.

Таблица 5.7

№ п/п	Заказано покупателем	Стоимость покупки	Итого за день	В среднем на покупку
Куплено	Выручка	Куплено	Выручка
…	…	…	…	…	…	…

Подсказка

· Графа 3. Составьте формулу самостоятельно.

· Графа 4. В первой строке "Итого за день куплено" совпадает с заказом первого покупателя. В остальных – к итогу по предыдущим строкам добавляется объем заказа очередного покупателя.

· Графа 5 – аналогично графе 4.

· Графа 6 – "Итого за день куплено" / "№ п/п".

· Графа 7 – аналогично графе 6.

Пояснение к задачам 5.10–5.16

В этих задачах константы, необходимые для выделения нужных данных, удобно предварительно ввести или рассчитать в отдельных ячейках и затем использовать их для сравнения в команде Формат ® Условное форматирование…

5.10. Составьте ведомость, в которой отражены оценки 10 студентов по разным дисциплинам (4–6 дисциплин), средний балл по каждой из них, средний балл каждого студента. С помощью условного форматирования выделите в ведомости двойки и пропуски экзаменов. Постройте в цветном и черно-белом варианте гистограммы, представляющие средние баллы по разным дисциплинам (по оси Х – дисциплина, по оси Y – средний балл по ней).

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

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

5.13. Составьте таблицу, в которой приведены сведения о среднем количестве покупателей в магазине в разное время дня (9:00 – 9:30; 9:30 – 10:00 и т. д.). Найдите среднее количество посетителей за день. Отметьте в таблице с помощью условного форматирования время, когда количество посетителей превышает норму обслуживания одним кассиром. Представьте эти данные в виде диаграммы типа График.

5.14. Предприятие реализует свой товар в нескольких районах области. Представьте данные об объемах и выручке по районам в виде ведомости (в разных районах цены могут быть разными). Найдите общий объем реализации. Отметьте районы, в которых реализация превысила накладные расходы. Сравните результаты реализации на круговой диаграмме.

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

5.16.Предприятие проводит рекламные мероприятия. Отразите сведения о сбыте товаров в каждый день рекламной кампании в виде таблицы и ломаной линии. Отметьте день с максимальным сбытом. Определите общий объем сбыта за время кампании. Подберите на графике функцию тренда.

5.17. Постройте таблицу и диаграмму функции при значениях a = 0,2; b = 0,3. Аргументы х перебирайте в диапазоне от 1 до 2 с шагом 0,1.

5.18. Постройте таблицу и график функции . Аргументы х перебирайте в диапазоне от 0 до 2 с шагом 0,2. вычисляется либо как x ^ 0,5, либо как функция КОРЕНЬ(х).

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

Таблица 5.8

x	sin(x)	sin(x) + 0,5	sin(2x + 1)	cos(4x)	Сумма функций
0,3
…
Среднее значение

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

Таблица 5.9

x		ln(x)	5lg(x)	0,25x2	Сумма функций
0,5
0,75
…
Среднее значение

5.21.В табл. 5.10 представленызначениятемпературы в июле.

Таблица 5.10

Время дня	Число месяца
10:00	19,00	18,72	18,73	18,97	19,37	19,37	19,31
11:00	20,00	19,86	19,71	19,34	19,16	18,92	18,99
12:00	22,00	21,55	21,12	20,67	20,37	20,75	21,02
13:00	23,00	23,22	23,06	23,48	23,12	22,85	23,31
14:00	25,00	25,47	25,09	24,91	24,78	25,09	25,02
15:00	27,00	27,40	27,89	27,62	27,84	27,42	27,61
16:00	25,00	25,11	25,41	25,25	25,46	25,12	24,97
17:00	24,00	23,63	23,56	23,33	23,68	23,43	23,76

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

Таблица 5.11

Показатель	Число месяца
Средняя температура
Стандартное отклонение
Средняя температура минус стандартное отклонение
Средняя температура плюс стандартное отклонение

 

РАСЧЕТНЫЕ АЛГОРИТМЫ В EXCEL

· ЕСЛИ() – позволяет предусмотреть разные варианты заполнения ячейки; · СУММ(), ПРОИЗВЕД() – соответственно суммирование и перемножение значений из… · СУММПРОИЗВ() – суммирование произведений соответствующих элементов двух или нескольких массивов;

Общие сведения о функции ЕСЛИ()

Функция ЕСЛИ() позволяет предусмотреть разные способы заполнения одной и той же ячейки. То, каким из них следует воспользоваться в данный момент, Excel определяет самостоятельно по тому, выполняется или нет при введенных данных указанное в функции условие. Стандартный формат функции имеет следующий вид:

ЕСЛИ(Логическое_выражение;
Значение_если_истина;Значение_если_ложь)

Здесь:

· Логическое_выражение – это условие, которое при одних значениях введенных данных выполняется, при других – нет;

· Значение_если_истина – алгоритм, по которому определяется значение функции, когда условие оказывается правильным;

· Значение_если_ложь – алгоритм, по которому определяется значение функции, когда условие оказывается неправильным.

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

Рассмотрим действие этой функции на конкретных примерах.

Выбор из двух вариантов по одному условию

Пример

Поставщик ввел оптовую скидку на цену для больших партий товара. Надо составить шаблон для расчета стоимости любой партии товара.

Составим таблицу из констант, необходимых для расчета. В ячейки А1:А4 введем названия констант: "ОбъемПартии", "ОптБарьер", "РознЦена", "ОптЦена". Присвоим ячейкам В1:В4 такие же имена (удобно пользоваться командой Вставка ® Имя ® Создать…). В ячейку С1 введем текст "СтоимПартииТовара".

Сделаем активной ячейку С2 и вызовем через Мастер функций функцию ЕСЛИ(). В окне аргументов в текстовые поля введем следующие значения:

· В поле "Логическое_выражение:" вводится условие, по которому Excel будет выбирать нужный вариант действий. Его можно составить так:

ОбъемПартии<=ОптБарьер

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

ОбъемПартии* РознЦена

· В поле "Значение_если_ложь:" указывается, как рассчитывать функцию, если условие не выполняется. Для нашего примера следует ввести

ОбъемПартии*ОптЦена

Расчетный шаблон готов. Чтобы проверить его, введите удобные для устных расчетов числа в ячейки В1:В4 и проверьте, правильно ли функция ЕСЛИ() выбрала формулу для заполнения ячейки С2. Введите в В1 другой объем партии, при котором требуется использовать другую цену при расчете стоимости покупки. Если в обоих случаях получены верные результаты, можно красиво отформатировать ячейки А1:С4 (см. подразд. 1.13–1.16) и пользоваться этим шаблоном, меняя только значения констант в В1:В4.

Пример

В таблице значений функции y = 2cos(x + 2)e^0,5x надо отметить символом "*" строку с минимальным значением.

Введем в ячейки А1 и В1 подписи "X" и "Y", в блок А2:А11 – значения аргументов, в блок В2:В11 – формулу расчета функции. Столбец С зарезервируем для заказанной в условии метки. В ячейку D1 введем текст "минимум", в ячейке D2 с помощью функции МИН() найдем это значение в блоке В2:В11.

Выделим ячейку С2 и вызовем через Мастер функций функцию ЕСЛИ(). Условие, по которому Excel выбирает нужный вариант действий, составим так: В2=$D$2. В строку второго аргумента вводим символ "*" (без кавычек), в третий – пробел и нажмем после этого <ОК>. С помощью протяжки скопируем полученную формулу на блок С2:С11.

Задание

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

Рассмотрим функцию ЕСЛИ() в той строке, в которой появилась "*". Значение функции y в этой строке минимально. Левая и правая части условия оказались одинаковыми, т. е. первый аргумент – правильный. Поэтому для заполнения своей ячейки функция ЕСЛИ выбрала то, что указано во втором аргументе. Для значений функции у в других строках условие, введенное в функцию ЕСЛИ(), оказывается неверным, поэтому она заполняет свои ячейки по варианту третьего аргумента. В нашем случае это пробел, который невидим на экране, поэтому ячейки кажутся пустыми.

Измените аргументы, введенные в А2:А11. "*" переместилась в другую строку, хотя формулы в С2:С11 не были изменены (после изменения данных каждая функция ЕСЛИ() автоматически проверила свой первый аргумент заново и приняла новое управляющее решение, каким правилом пользоваться для заполнения своей ячейки).

Задание

В ячейку А1 введите формулу:

=ЕСЛИ(С3=37;"СЕНО";"СОЛОМА")

Определите влияющую ячейку (команда Сервис ® Зависимости ® Влияющие ячейки) и введите в нее такое число, при котором СОЛОМА превратится в СЕНО.

6.3. Проверка двух условий при выборе одного
из двух вариантов

Пример

Объем заказа k, который принимает фирма, должен лежать в диапазоне от a1 до a2 штук изделий. Цена одного изделия составляет d рублей. Составьте шаблон для расчета стоимости заказа. Требуется, чтобы на экране появлялась стоимость заказа, если его объем лежит в нужном диапазоне, и предупреждение, если объем не попал в допустимые границы.

Введем в ячейку А1 общее название шаблона: Расчет стоимости заказа, в А3:А6 – названия переменных, а в В3:В6 – их значения. Названия переменных, использованные при формулировке примера, неудобны для Excel-расчета: а1, а2 похожи на стандартные адреса ячеек, поэтому можно воспользоваться длинными текстовыми именами, например, в А3 – ОбъемЗаказа, в А4 – НижнГран, в А5 – ВерхГран, в А6 – Цена. В А2 введем текст СтоимостьЗаказа, в В2 – функцию ЕСЛИ() со следующими аргументами:

Логическое_выражение: И(B3>=B4;B3<=B5)

Значение_если_истина: B3*B6

Значение_если_ложь: ТАКОЙ ЗАКАЗ НЕ ПРИНИМАЕМ

Если ячейкам В3:В6 предварительно присвоить имена, указанные в А3:А6 (см. подразд. 1.7), то смысл аргументов станет более понятным.

Задание

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

2. Отформатируйте расчетный шаблон (см. подразд. 1.13–1.16):

· измените ширину столбца А так, чтобы все длинные тексты были видны на экране;

· выровняйте по левому краю значения переменных в столбце В;

· выделите заливкой или цветом шрифта ячейки А2:В2, А3:В3;

· отцентрируйте заголовок по ячейкам А1:В2.

3. Измените условие в первом аргументе:

ИЛИ(В3<=B4;B3>=B5)

В какой последовательности надо теперь перечислять способы заполнения ячейки В2?

Вывод. Если выбор одного из двух вариантов заполнения ячейки зависит от нескольких условий, то все они перечисляются через ";", заключаются в общие скобки и перед ними указывается нужный тип объединения:

· должны выполняться все одновременно – И;

· должно выполняться хотя бы одно из них – ИЛИ.

Примечание

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

Выбор одного из многих вариантов

Пример

В таблице значений функции y = 2cos(x + 2)e^0,5x надо отметить словами "минимум" и "максимум" строки с самым маленьким и самым большим значениями функции. Остальные строки не помечать ничем.

Введем в ячейки А1 и В1 подписи "X" и "Y", в блок А2:А11 – значения аргументов, в блок В2:В11 – формулу расчета функции. Столбец С зарезервируем для заказанных в условии меток. В ячейки D1 и Е1 введем тексты "минимум" и "максимум" соответственно, в ячейках D2 и Е2 с помощью функций МИН() и МАКС() вычислим эти значения по блоку В2:В11.

Выделим ячейку С2 и вызовем через Мастер функций функцию ЕСЛИ(). Эта функция должна обеспечить выбор одной из трех меток для своей ячейки: либо одно из двух указанных слов, либо пустая ячейка. Стандартная же функция позволяет предусмотреть только два варианта. Чтобы расширить возможности функции, в третий аргумент вводят дополнительную функцию ЕСЛИ() (правила вставки дополнительной функции в аргумент основной изложены в подразд. 1.9):

=ЕСЛИ(В2=$D$2;"минимум";ЕСЛИ(В2=$Е$2;"максимум";""))

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

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

Пример

Составить шаблон для расчета подоходного налога по прогрессивной схеме.

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

Налог=

 

Здесь d – доход; n1, n2 – границы налоговых ставок; р1, р2, р3 – процентные ставки налогов (очередной процент действует только на ту часть дохода, которая попала в его диапазон).

Введем в ячейку А1 общий заголовок шаблона: "Расчет налога". В А2:А7 введем соответственно названия "Доход", "Граница1", "Граница2", "Проц1", "Проц2", "Проц3". В В2:В7 введем значения этих величин, В С2 – текст: "Налог", в D2 – функцию ЕСЛИ(), первый и второй аргументы которой такие:

Логическое_выражение: B2<=B3

Значение_если_истина: B5*B2

В третьем аргументе надо объяснить, как заполнять ячейку, если доход больше первой границы. Так как для этого случая существует еще две возможности: d n2 и d > n2, в этот аргумент вставляют дополнительную функцию ЕСЛИ(), которая должна выбрать нужный вариант. Правила вставки дополнительной функции в аргумент основной изложены в подразд. 1.6, 1.9. Окончательно третий аргумент основной функции ("Значение_если_ложь") выглядит так:

ЕСЛИ(B2<=B4;B5*B3+B6*(B2-B3);B5*B3+B6*(B4-B3)+B7*(B2-B4))

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

Решение уравнения

Команда Сервис ® Подбор параметра… вызывает на экран окно Подбор параметра, в котором следует указать: · адрес ячейки, в которой находится конечное значение функции; · то число, к которому ее надо приравнять;

Решение систем уравнений

Для нелинейных систем можно использовать команду Сервис ® Поиск решения…, преобразовав задачу в оптимизационную (см. подразд. 6.7). Систему линейных уравнений можно решить, запрограммировав вручную метод… АХ = В; Х = А-1В.

Решение задач оптимизации

· поиск безусловных экстремумов функции одного или нескольких аргументов; · поиск экстремумов функции одного или нескольких аргументов при наличии… · поиск аргументов, при которых функция примет нулевое значение;

Подбор функции по опытным данным

Если требуется подобрать тренд нестандартного типа, можно построить несложный расчетный шаблон для самостоятельного расчета тренда по любой формуле… Рис. 6.8.1 В столбцах А и В располагается таблица функции, для которой ищется тренд. В ячейки Е3 и F3 введены ориентировочные…

Примечание

Формулы будут более понятны, если присвоить подходящие имена ячейкам Е3, F3, F5 и F7 и при вводе формул использовать не стандартные адреса ячеек, а подписи диапазонов и эти имена (см. подразд. 1.7, 1.8).

После того, как расчетный шаблон составлен и заполнен, уточнение коэффициентов подбираемой функции завершает команда Сервис ® Поиск решения… В нашем примере ей надо задать такие параметры:

· "Установить целевую ячейку" – F5;

· "Равной" – минимальному значению;

· "Изменяя ячейки" – E3:F3.

После этого следует нажать кнопку <Выполнить>.

Примечания

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

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

· Для того чтобы проиллюстрировать качество тренда, полезно построить диаграмму с двумя рядами данных: один ряд – опытные данные f(x), другой – расчетные y(x). Если функция зависит от нескольких переменных, то на втором шаге Мастера диаграмм, когда задаются исходные данные, следует оставить чистым поле аргументов. В этом случае будет построена плоская диаграмма, у которой роль аргументов будут играть номера точек в таблице.

· Если тренд зависит от подбираемых коэффициентов не линейно или их начальные приближения далеки от оптимальных, то время, необходимое для решения задачи, может превысить тот допустимый лимит, который предусмотрен в команде Сервис ® Поиск решения… Никакого сигнала о такой ситуации команда не выдает. Поэтому полезно после того, как команда отработает и даст какой-то результат, запустить ее повторно, ничего не меняя в полученных данных. Если критерий S не изменится, это означает, что найденные коэффициенты – наилучшие, в противном случае следует повторно запускать команду до тех пор, пока S не перестанет изменяться.

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

ЗАДАЧИ И УПРАЖНЕНИЯ НА ВЫЧИСЛИТЕЛЬНЫЕ АЛГОРИТМЫ

Задачи на использование функции ЕСЛИ()

В этих задачах предполагается два варианта заполнения одной и той же ячейки (см. подразд. 6.2). 7.1.1. В табл. 7.1.1 представлена ведомость покупок, в которой надо заполнить… Таблица 7.1.1 Товар Цена, $ Количество Сумма, р. Налог1, р. Налог2, р. …

Задачи на решение уравнений

Подсказка 1. Расположите и отформатируйте информацию в соответствии с рис. 7.2.1. … Рис. 7.2.1

Примечание

Формулы во вторую строку можно ввести протяжкой из первой строки, но при этом надо предварительно закрепить клавишей F4 адреса, которые не должны меняться при копировании.

Пояснение к задачам 7.2.3–7.2.7

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

7.2.3. х² + у² = 1; у = –х².

7.2.4. 3х² + 5у² = 24; ху = –0,4.

7.2.5. ху = –32; х = –6у².

7.2.6. ху = 31; у = –6х².

7.2.7.2х² + 5у² = 17; ху =0,3.

Поиск безусловных экстремумов и корней функций

Пояснение к задачам 7.3.13–7.3.5

В этих задачах требуется найти графически и по командам Сервис ® Подбор параметра…, Сервис ® Поиск решения… минимумы и корни предложенных функции (см. подразд. 2.1 и 6.5).

7.3.1. х⁴ – х³ – х² – 2х + 1.

7.3.2. х⁴ –2х³ – 3х² – 3х – 32.

7.3.3. х⁴ – 3х³ + 2х² – 20.

7.3.4. х⁴ – х³ – 3х² – 5х – 16.

7.3.5. х⁴ – 2х³ + х² – 3х – 22.

Задачи планирования

7.4.2.Фирма имеет возможность рекламировать свою продукцию через теле- и радиосеть. Каждая минута радиорекламы обходится в $5, телерекламы – в $100.… 7.4.3.Фирма производит два вида продукции – А и В. Объем сбыта продукции А… 7.4.4.Фирма выпускает ковбойские шляпы фасонов А и В. Трудоемкость изготовления шляпы фасона А вдвое выше, чем для…

Таблица 7.5.1

Неделя

Сбыт

Подсказка

1. Изобразите опытные данные на точечной диаграмме (см. подразд. 4.1–4.5). "На глаз" видно, что можно использовать следующие математические модели:

· очень грубо – линейную y = a + bx (линейный тренд);

· с учетом замедления роста сбыта в конце периода – квадратичную y = a + bx + cх² (полиномиальный тренд второй степени);

· если предполагается, что сбыт выходит на стационарный уровень, то y = a + b / (x – c) или y = a + barctg(cx) (нестандартные тренды).

2. Добавьте к диаграмме стандартные тренды и их уравнения (см. подразд. 4.6). Изобразите каждый тренд и его уравнение новым цветом.

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

4. Выберите "на глаз" наиболее удачные линии тренда и сосчитайте средние квадраты отклонений исходных данных от расчетных по ним.

5. Сделайте вывод, какой тренд лучше всего использовать для прогноза сбыта на следующие недели.

7.5.2.Подберите наилучшие коэффициенты для описания данных, представленных в табл. 7.5.2, функцией y = a + bx + cх².

Таблица 7.5.2

х	–3	–2,5	–2,0	–1,5	–1,0	–0,5		0,5	1,0	1,5
у	0,36	–1,83	–3,32	–4,18	–4,43	–4,06	–2,90	–1,22	1,20	4,21

7.5.3.Подберите наилучшие коэффициенты для описания данных, представленных в табл. 7.5.3, функцией y = a + bx + cх² + dx³.

Таблица 7.5.3

х		4,7	4,4	4,1	3,8	3,5	3,2	2,9	2,6	2,3
у

Пояснение к задачам 7.5.4–7.5.13

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

 

7.5.4. z = a + bxexp(cy)		7.5.5. z = a / (bx + cy)
у х	2,0	2,2	2,4	у х	1,0	1,8	2,4
1,0	7,67	7,90	8,16	1,0	3,11	2,08	1,51
1,5	8,76	9,11	9,48	1,5	2,24	1,65	1,27
2,0	9,85	10,31	10,81	2,0	1,75	1,37	1,10
2,5	10,94	11,51	12,14	2,5	1,44	1,17	0,96
7.5.6. z = a / (bx + exp(cy))		7.5.7. z = ax + bexp(cy)
у х	0,5	0,7	0,9	у х	0,1	0,4	0,8
0,1	26,13	21,37	17,41	0,2	0,57	0,59	0,63
0,2	23,89	19,84	16,39	0,5	0,81	0,83	0,87
0,3	21,99	18,52	15,47	1,1	1,29	1,31	1,35
0,4	20,38	17,36	14,66	1,7	1,77	1,79	1,83
7.5.8. z = (a + x) / (b + cy2)		7.5.9. z = a (xb + exp(cy))
у х	0,5	1,5	2,5	у х	2,0	5,0	10,0
1,0	0,74	0,37	0,18	1,0	44,43	9,31	6,21
4,0	1,75	0,87	0,43	4,0	6,21	5,48	4,24
9,0	3,43	1,71	0,85	7,0	4,24	3,88	3,22
16,0	5,78	2,88	1,44	10,0	3,21	3,01	2,59
7.5.10. z = a + bxyc		7.5.11. z = (a + bx)yc
у х	0,4	0,9	1,5	у х	2,0	3,0	5,0
0,4	-0,07	0,03	0,22	2,0	28,50	75,42	256,99
0,9	-0,02	0,20	0,62	4,0	45,39	120,11	409,29
1,1	-0,01	0,27	0,78	8,0	79,17	209,50	713,87
1,8	0,05	0,50	1,33	16,0	146,73	388,27	1323,04
7.5.12. z = ayc + bx		7.5.13. z = (a + bxc)y
у х	0,4	0,9	1,5	у х	2,0	3,0	5,0
0,4	-0,50	6,50	19,40	2,0	6,2	8,4	14,1
0,9	-3,20	3,20	17,10	4,0	0,3	-0,5	-0,9
1,1	-4,30	3,10	17,90	8,0	-8,1	-13,4	-20,2
1,8	-8,20	-0,75	12,90	16,0	-18,8	-28,9	-52,6

СПИСОК ЛИТЕРАТУРЫ

1. Гарнаев. А. Excel, VBA, Internet в экономике и финансах. – СПб.: БХВ-Петербург, 2002. – 816 с.

2. Дадлей К., Кокс Дж., Урбан Microsoft Оffice 97: Краткий курс. – СПб: ЗАО Изд-во "Питер", 1999. – 384 с.

3. Долженков В.А., Колесников Ю.В. Самоучитель Micro­-
soft® Excel 2002. – СПб.: БХВ-Петербург, 2002. – 432 с.

4. Попов А. Excel: Практическое руководство. – М.: DECCOM, 2000. – 302 с.