Контрольная работа: Обработка данных с помощью средств MS Excel

ВВЕДЕНИЕ

Контрольная работа — это самостоятельная работа студента по дисциплине «Информатика».

Цель написания контрольной работы:

· закрепить теоретические и практические знания по предмету;

· научиться применять, полученные знания при решении практических вопросов;

· научиться самостоятельно находить информационную[2] базу на основе знакомства с библиографическим фондом библиотеки и читального зала ВУЗа, с помощью электронной справки и других источников информации.

1. ПОРЯДОК И СРОКИ СДАЧИ КОНТРОЛЬНОЙ РАБОТЫ НА РЕГИСТРАЦИЮ, ПРОВЕРКУ И ДОРАБОТКУ

Срок предоставления контрольной работы определяется преподавателем. Студент приносит печатный вариант контрольной работы в установленный срок на кафедру «Прикладной математики» и сдает ее лаборанту кафедры. Лаборант кафедры фиксирует дату сдачи контрольной работы, дату выдачи на проверку и, позднее, дату проверки контрольной работы преподавателем. Контрольная работа представляется на проверку не менее чем за две недели до зачета или экзамена . Проверка контрольной работы происходит в течение двух недель со дня передачи работы на проверку. В тех случаях, если работа не отвечает требованиям (по содержанию или по оформлению), она возвращается студенту на доработку. Замечания, заверенные своей подписью, преподаватель оставляет на первом или последнем листе работы. Студент имеет право выслушать замечания в устной форме при личной беседе с преподавателем для разъяснения необходимости доработки контрольной работы. Доработка осуществляется в трёхдневный срок со времени проверки контрольной работы.

Не засчитываются работы, оказавшиеся идентичными по своему содержанию, причем обе работы аннулируются, а их авторам выдаются новые темы. Не могут быть засчитаны работы, полностью скопированные с образцов компьютерных обучающих программ, из сети «Интернет» и т. п.

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

Зачёт (незачёт) контрольной работы может проводиться после завершения цикла проверок и доработок, или после проведения собеседования по контрольной работе по усмотрению преподавателя.

При проведении собеседования студент должен иметь при себе электронную версию контрольной работы.

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

Выполненные студентами контрольные работы сдаются в архив филиала МГОУ в г. Воскресенске.

2. СТРУКТУРА КОНТРОЛЬНОЙ РАБОТЫ

Структура контрольной работы должна быть следующей:

· титульный лист (Приложение1);

· оглавление;

· задание;

· практическая часть;

· список использованной литературы.

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

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

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

3. ОФОРМЛЕНИЕ КОНТРОЛЬНОЙ РАБОТЫ

Оптимальный объем курсовой работы не должен превышать 10-15 страниц печатного текста формата А4. В этот объем не входят приложения и список использованной литературы (библиография).

Контрольная работа оформляется при помощи текстового процессора Microsoft Word.

Каждая страница основного текста и приложений должна иметь поле: левое – 20 мм, верхнее, правое и нижнее — 10 мм.

Требования к тексту курсовой работы:

шрифт — Times New Roman;

размер шрифта — 12 пт;

межстрочный интервал — полуторный.

Ссылки на литературу размешаются непосредственно в самом тексте, (например, «Стандартное отклонение» [1, стр. 195], где 1 — номер источника согласно списку использованной литературы, с 195 — номер страницы в данном источнике).

Нумерация страниц проставляется в середине нижней части листа последовательно, начиная с 3-й страницы (задание), то есть после титульного листа и оглавления.

Текст контрольной работы разбивается на главы (разделы), параграфы (подразделы) и пункты, которые должны иметь порядковые номера. После номера раздела ставиться точка. Введение и заключение не нумеруются. Подразделы (параграфы) каждого раздела (главы) нумеруются двумя арабскими цифрами, разделенными точкой. Первая цифра обозначает номер раздела, вторая — номер подраздела. Например, 1.1. — первый подраздел первого раздела.

Название глав работы пишутся с новой страницы прописными буквами, шрифтом — Arial. Заголовки подразделов пишутся с отдельной строки, обычным шрифтом.

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

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

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

Список использованной литературы включает перечень всех первоисточников , использованных в работе по определенной форме и последовательности:

1. Законы Российской Федерации и субъектов РФ. Постановления Правительства, Положения и Инструкции Министерств и ведомств

2. Учебники, монографии — в алфавитном порядке фамилий авторов с указанием места и года издания

3. Журнальные и газетные публикации — в алфавитном порядке фамилий авторов с указанием печатного органа и времени издания.

4. Архивные материалы — с указанием архивов, фондов, описей и номеров дел Последовательность элементов библиографического описания источников следующая:

а) для монографий и учебников:

· если произведение имеет автора, то пишется его фамилия, а затем инициалы;

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

· если авторов более грех, то указывается фамилия первою автора с добавлением слов «и др.»;

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

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

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

· при описании многотомных изданий после названия книги ставится двоеточие;

· при библиографическом описании документа название места издания приводиться полностью в именительном падеже, за исключением названий городов: Москва- М.;

· наименование издательств пишется без кавычек. При наличии двух издательств — приводят наименование обоих, каждому предшествует двоеточие;

· год издания обозначается лишь цифрой без указания буквы «г», в случае отсутствия; года издания пишутся буквы «б. г.»;

· при ссылке на тома, часть, разделы, главы или страницы данные пишутся сокращенно.

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

· в случае ссылки на постановление правительства РФ или закон РФ указываются дата их принятия, номер, полное издание, откуда взят документ

Контрольная работа скрепляется степлером или помещается в папку.

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

4. ТЕМА И ЗАДАНИЯ ПО КОНТРОЛЬНОЙ РАБОТЕ

Тема контрольной работы — Обработка данных с помощью средств Microsoft Excel

Варианты заданий

Номер варианта соответствует последней цифре в номере зачетной книжки студента.

Задания по контрольной работе выбираются из Приложения 2.

5. СОДЕРЖАНИЕ И ПОРЯДОК ВЫПОЛНЕНИЯ КОНТРОЛЬНОЙ РАБОТЫ

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

Расчётные таблицы обязательно должны быть представлены в двух вариантах:

· в режиме отображения результатов вычислений;

· в режиме отображения формул.

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

6. КРАТКИЕ ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

6.1. РАБОТА С ИМЕНАМИ ЯЧЕЕК

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

Работа с именами ячеек начинается с задания им имён.

6.1.1. Создание имён ячеек

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

1. Задание имён на основе заголовков строки (верхней строки) и столбца (левого столбца):

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

• Задать команду Вставка ® Имя, а далее выбрать пункт Создать и выбрать параметр «в строке выше», или «в столбце слева», или «в строке ниже», или «в столбце справа» в зависимости от того, где располагается заголовок.

2. Задание произвольных имен:

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

• Задать команду Вставка ® Имя, указать пункт Присвоить, ввести имя в поле Имя и нажать ОК. Другой способ — щелкнуть в окне имен (левая часть строки формул), ввести новое имя, нажать клавишу <Еntег>. При ссылке в формулах на именованную ячейку она будет адресована абсолютно и при копировании формул не возникнут ошибки.

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

6.1.2. Замена адресов ячеек их именами

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

• выделить диапазон, в котором должны быть заменены адреса;

• задать команду Вставка ® Имя ® Применить;

• выделить все применяемые имена в этом диапазоне и нажать кнопку ОК.

6.1.3. Вставка имен в формулы

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

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

Работа с именами ячеек начинается с задания им имён.

6.1.1. Создание имён ячеек

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

1. Задание имён на основе заголовков строки (верхней строки) и столбца (левого столбца):

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

• Задать команду Вставка ® Имя, а далее выбрать пункт Создать и выбрать параметр «в строке выше», или «в столбце слева», или «в строке ниже», или «в столбце справа» в зависимости от того, где располагается заголовок.

2. Задание произвольных имен:

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

• Задать команду Вставка ® Имя, указать пункт Присвоить, ввести имя в поле Имя и нажать ОК. Другой способ — щелкнуть в окне имен (левая часть строки формул), ввести новое имя, нажать клавишу <Еntег>. При ссылке в формулах на именованную ячейку она будет адресована абсолютно и при копировании формул не возникнут ошибки.

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

6.1.2. Замена адресов ячеек их именами

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

• выделить диапазон, в котором должны быть заменены адреса;

• задать команду Вставка ® Имя ® Применить;

• выделить все применяемые имена в этом диапазоне и нажать кнопку ОК.

6.1.3. Вставка имен в формулы

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

6.2. ИСПОЛЬЗОВАНИЕ ВСТРОЕННЫХ ФУНКЦИЙ ЕХСЕL

Поставляемые вместе с Ехсеl встроенные функции призваны значительно облегчить работу пользователя, поскольку сокращают написание формул. Ранее уже были рассмотрены две функции — автосуммирования и округления. Для вызова функции автосуммирования используется кнопка Автосумма. Для задания округления был использован способ написания на строке формул следующей конструкции: =ОКРУГЛ(АДРЕС; ТОЧНОСТЬ).

В принципе аналогичным же образом любая функция может быть вручную введена в ячейку. Ввод любой функции начинается со знака равенства [ = ], за которым следует ее название, а затем без интервалов должны идти круглые скобки, в которых через символ точки с запятой [;] перечисляются аргументы функции. Даже если аргументы отсутствуют (как у функции =СЕГОДНЯ()), круглые скобки должны присутствовать. Например, в функции автосуммирования =СУММ(ДИАПАЗОН) названием функции является слово СУММ, а аргументом — диапазон суммируемых ячеек. Однако такой ручной способ ввода не является наилучшим, поскольку требует от пользователя довольно длительного и тщатель­ного набора. Поэтому для задания функции используют следующие правила.

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

• нажать клавиши <Shift><FЗ>;

• задать команду Вставка - Функция;

• нажать кнопку Вставка функции [f_х ] на стандартной панели.

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

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

6.2.1. Суммирование ячеек, удовлетворяющих определенному критерию

СУММЕСЛИ(ДИАПАЗОН;УСЛОВИЕ;ДИАПАЗОН_СУММИРОВА-НИЯ) — группа математических функций. Функция предназначена для суммирования только ячеек, удовлетворяющих некому критерию.

• ДИАПАЗОН — это диапазон, в котором определяется критерий;

• УСЛОВИЕ — указывается в форме числа, выражения или текста;

• ДИАПАЗОН_СУММИРОВАНИЯ — это диапазон суммируемых ячеек.

Пример 1 .

В ячейки А1, А2, АЗ, А4, А5 введена информация:

А1=1000, А2=2000, А3=900, А4=800, А5=1500.

Требуется подсчитать сумму чисел, значение которых больше или равно 1000. Результат должен быть получен в ячейке А6.

Пошаговыми действиями Мастера функций в ячейку А6 следует ввести формулу:

=СУММЕСЛИ(А1 :А5;">=1000").

В ячейке А6 получится число 4500.

Измените значение ячейки АЗ на любое большее 1000. Проследите изменение результата вычислений.

6.2.2. Подсчет количества значений в диапазоне

Для подсчета количества числовых значений в диапазоне:

СЧЕТ (ЗНАЧЕНИЕ1;ЗНАЧЕНИЕ2;...) - группа статистических функций.

Пример 2 . В ячейки В1,В2,ВЗ,В4,В5 введена информация:

В1=«Текст», В2=2000, В3= , В4=800, В5=1500.

Требуется подсчитать количество ячеек с числовыми значениями в диапазоне В1:В5.

Результат должен быть получен в ячейке В6.

Пошаговыми действиями Мастера функций в ячейку В6 следует ввести формулу:
=СЧЕТ(В1 :В5). В ячейке В6 получится число 3.

Для подсчета количества всех значений в списке аргументов и непустых ячеек: СЧЕТЗ(ЗНАЧЕНИЕ1;ЗНАЧЕНИЕ2;...) -- группа статистических функций.

Если эта функция будет введена в примере 2 в ячейку В7, то результат в ячейке В7 будет равен 4.

6.2.3. Подсчет количества пустых ячеек в диапазоне

СЧИТАТЬПУСТОТЫ(ДИАПАЗОН) — группа статистических функций.

Пример 3 .

В ячейки С1,С2,СЗ,С4,С5 скопируйте информацию из соответствующих ячеек столбца А, оставив пустой ячейку СЗ:

С1=1000, С2=2000, С3= , С4=800, С5=1500.

Требуется подсчитать количество пустых ячеек в диапазоне С1:С5. Результат должен быть получен в ячейке Сб.

Пошаговыми действиями Мастера функций в ячейку С6 следует ввести формулу: =СЧИТАТЬПУСТОТЫ(С1:С5). В ячейке С6 получится число 1.

6.2.4. Подсчет количества непустых ячеек в диапазоне, удовлетворяющих заданному условию

СЧЕТЕСЛИ(ДИАПАЗОН; УСЛОВИЕ) — группа статистических функций.

• ДИАПАЗОН — это диапазон, в котором определяется критерий;

• УСЛОВИЕ — указывается в виде числа, выражения или текста и определяет какие ячейки надо подсчитывать.

Пример 4 .

Подсчитать количество непустых ячеек в диапазоне С1:С5, значение которых больше или равно 1000. Результат должен быть получен в ячейке С7.

Пошаговыми действиями Мастера функций в ячейку С7 следует ввести формулу: =СЧЕТЕСЛИ(С1 :С5;">=1000"). В ячейке С7 получится число 3.

Измените значение ячейки С1 на любое, меньшее 1000. Проследите изменение результата вычислений.

6.2.5. Расчет среднего значения

СРЗНАЧ(ДИАПАЗОН1; ДИАПАЗОН2;...) - группа статистических функций. В текущую ячейку возвращается среднее значение для чисел указанного диапазона.

Пример 5 .

В диапазоне ячеек А1 :А5 из примера 1 определить среднее значение. Результат должен быть получен в ячейке А7.

Пошаговыми действиями Мастера функций в ячейку А7 следует ввести формулу: =СРЗНАЧ(А1 :А5).

6.2.6. Определение максимального значения

МАКС(ДИАПАЗОН1; ДИАПАЗОН2;...) — группа статистических функций. В текущую ячейку возвращается максимальное число из данного диапазона.

Пример 6 .

В диапазоне ячеек А1:А5 из примера 1 определить максимальное значение. Результат должен быть получен в ячейке А8.

Пошаговыми действиями Мастера функций в ячейку А8 следует ввести формулу: =МАКС(А1 :А5). В ячейке А8 получится число 2000.

6.2.7. Определение минимального значения

МИН(ДИАПАЗОН1; ДИАПАЗОН2;...) - группа статистических функций. В текущую ячейку возвращается минимальное число из данного диапазона.

Пример 7 .

В диапазоне ячеек В1:В5 из примера 2 определить минималь­ное значение. Результат должен быть получен в ячейке В8.

Пошаговыми действиями Мастера функций в ячейку В8 следует ввести формулу: =МИН(В1:В5). В ячейке В8 получится число 800.

6.2.8. Функции прогнозирования[3]

Для прогнозирования используется ряд функций (ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ) и диаграммы.

Функция ПРЕДСКАЗ

ПРЕДСКАЗ (X; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X).

Функция ПРЕДСКАЗ вычисляет или предсказывает будущее значение по существующим значениям.

X — это точка данных, для которой предсказывается значение.

ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y — это зависимый массив или интервал данных.

ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X — это независимый массив или интервал данных.

Функция РОСТ

РОСТ(ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X; НОВЫЕ ЗНАЧЕНИЯ X; КОНСТ).

Функция РОСТ рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения Y для последовательности новых значений X, задаваемых с помощью существующих Х- и Y-значений. Функция рабочего значения РОСТ может применяться также для аппроксимации существующих Х- и Y-значений экспоненциальной кривой.

ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y — это множество значений Y, которые уже известны для соотношения Y = b*m^X.

ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X — это необязательное множество значений х, которые уже известны для соотношения Y = b*m^Х.

НОВЫЕ ЗНАЧЕНИЯ X — это новые значения X, для которых РОСТ возвращает соответствующие значения Y.

КОНСТ — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1.

Функция ТЕНДЕНЦИЯ

ТЕНДЕНЦИЯ(ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X; НОВОЕ ЗНАЧЕНИЕ X; КОНСТ).

В текущую ячейку возвращается новое значение X, рассчитанное на основании известных значений. Выполняется линейная аппроксимация.

ИЗВЕСТНЫЕ ЗНАЧЕНИЯ Y — это множество значений Y, которые уже известны для соотношения Y = mX + b.

ИЗВЕСТНЫЕ ЗНАЧЕНИЯ X — это необязательное множество значений X, которые уже известны для соотношения y = mX + b.

НОВЫЕ ЗНАЧЕНИЯ X — новые значения X, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения Y.

КОНСТ — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

6.3. ФУНКЦИИ ДАТЫ И ВРЕМЕНИ

Функция текущей даты

СЕГОДНЯ() — возвращает текущую дату компьютера.

Функция текущей даты и времени

ТДАТА() — возвращает текущую дату и время в числовом формате.

Функция определения дня недели

ДЕНЬНЕД(ДАТА_КАК_ЧИСЛО;ТИП) — преобразует дату в числовом формате в номер дня недели. Если ТИП не указан или ра­вен 1, то первым днем недели считается воскресенье, последним (7-м) — суббота. Если тип равен 2, первый день недели — понедельник.

Пример 8

Определить день недели от даты рождения.

Вернуться на Лист1 рабочей книги. В ячейку G8 ввести дату рождения в числовом формате: например, 14.09.1980. Пошаговыми действиями Мастера функций в ячейку G9 ввести формулу: =ДЕНЬНЕД(С8;2).

В ячейке G9 получится число 7, что соответствует «воскресенью».

Функция определения количества дней между двумя датами

ДНЕЙ360(НАЧАЛЬНАЯ_ДАТА;КОНЕЧНАЯ^АТА;МЕТОД) - количество дней определяется как разница между начальной и конечной датами, исходя из 360 дней в году. МЕТОД — задает использование европейского стандарта (необязательный параметр).

Пример 9

Определить количество дней от даты рождения по текущую дату.

Пошаговыми действиями Мастера функций в ячейку СЮ ввести формулу: =ДНЕЙ360(С8;СЕГОДНЯ()).

Функция определения номера месяца

МЕСЯЦ(ДАТА_КАК_ЧИСЛО) - преобразует дату в числовом формате в номер месяца.

6.4. ЛОГИЧЕСКИЕ ФУНКЦИИ

6.4.1. Функция проверки условия

ЕСЛИ(УСЛОВИЕ; ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2) - группа логических функций.

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

Пример 10 .

Скопировать в ячейки А11:А15 информацию из диапазона А1:А5 примера 1. В зависимости от значений ячеек А11:А15 выполнить: если значение больше 1000, делим его на 100, если нет делим на 10. Результат должен быть получен в ячейках В11:В15.

Функция вводится сначала в ячейку В11, затем копируется для всех ячеек до В15. В ячейку В11 вводим:

=ЕСЛИ(А11>1000;А11/100;А11/10). Словами это условие можно выразить так: «Если значение в ячейке А11 больше 1000, то его делим на 100; в противном случае делим его на 10». Результат деления получится в ячейке В11 (там, куда вводилась функция ЕСЛИ).

6.4.2. Использование функции И/ИЛИ

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

1. Когда условия соединены логическим И, результатом проверки нескольких условий считается:

• значение ИСТИНА, если все условия имеют значение ИСТИНА;

• % значение ЛОЖЬ, если хотя бы одно условие имеет значение ЛОЖЬ.

2. Когда условия соединены логическим ИЛИ, результатом проверки условий считается:

• значение ИСТИНА, если хотя бы одно из условий имеет значение ИСТИНА;

• значение ЛОЖЬ, если все условия имеют значение ЛОЖЬ.

6.4.3. Использование функции И

ЕСЛИ (И (УСЛОВИЕ1; УСЛОВИЕ2); ВЫРАЖЕНИЕ1; ВЫРАЖЕ-НИЕ2).

Вычисление выражения 1 выполняется только при истинности всех указанных условий; в противном случае вычисляется выражение 2.

Пример 11 .

Для каждой ячейки из диапазона А11:А15 примера 13 прове­рить условие: если значение ячейки {Аi} больше 900 и одновременно меньше 1500, то умножить его на 100, в противном случае оставить значение ячейки {А;} неизменным. Результат должен быть получен в ячейках С11:С15.

В ячейку СП ввести: =ЕСЛИ(И (А! !>900; А11<1500);А11*10;А11).

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

6.4.4. Использование функции ИЛИ

ЕСЛИ (ИЛИ (УСЛОВИЕ1; УСЛОВИЕ2); ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2).

В случае истинности одного из условий (условия 1 или условия 2) расчет текущей величины выполняется по выражению 1; в противном случае выполняется расчет по выражению 2.

Пример 12 .

Для каждой ячейки из диапазона А11:А15 примера 13 проверить: если значение ячейки {А_i } больше или меньше 1000, то умножить его на 10, в противном случае (если равно 1000) оставить значение ячейки {А_i } неизменным. Результат должен быть получен в ячейках D11:D15.

В ячейку D11 вводим:

=ЕСЛИ(ИЛИ (А11>1000;А11<1000);А11*10;А11).

Сначала вызывается функция ЕСЛИ, затем из списка встроенных функций в левой части строки формул вызывается функция ИЛИ для ввода логического условия. После завершения ввода условий в соответствующие поля окна ИЛИ следует щелкнуть указателем мыши в конце строки формулы и продолжить ввод значе­ний в окне ЕСЛИ. Из ячейки D11 формула копируется в D12:D15.

6.5. ФУНКЦИЯ ПОИСКА ДАННЫХ В НЕКОТОРОМ ДИАПАЗОНЕ

ПРОСМОТР(ИСКОМОЕ ЗНАЧЕНИЕ; ДИАПАЗОН) группа функций ссылки и массивы
(1-й вариант — векторный просмотр; 2-й вариант — массив).

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

Пример 13.

По номеру месяца определить его название. Для этого создается отдельная таблица, где номеру месяца ставится в соответствие его название.

1. Используя автозаполнение, ввести: в ячейки с J1:J2 цифры от 1 до 12; в ячейки К1:К12 названия месяцев с января по декабрь.

2. В ячейку Н9 ввести любое число от 1 до 12.

3. В ячейку I9 вставить функцию ПРОСМОТР, выбрав первый способ задания аргументов (отдельно вектор просмотра и вектор результата): =ПРОСМОТР(Н9;$и$1:$^12;$К$1:$К$12) – знак $ устанавливает абсолютные ссылки на адреса областей расположения номеров и названий месяца. (Для фиксации адреса нажмите <F4> в конце адреса). В ячейке I9 появится название соответствующего месяца.

4. В ячейку Н10 введите новое число от 1 до 12.

5. Скопируйте в ячейку I10 формулу из ячейки I9.

Пример 14.

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

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

=ПРОСМОТР(МЕСЯЦ(СЕГОДНЯ());$Д1:$К$12).

В ячейке I11 получим название месяца текущей даты.

6.6. ОЦЕНКА ЕЖЕМЕСЯЧНЫХ ВЫПЛАТ

Функция ПЛТ

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

Функция ПЛТ может быть использована для анализа всевозможных ссуд.

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

ПЛТ(СТАВКА;КПЕР;ПС;БС;ТИП)

- СТАВКА — процентная ставка за период. Например, если получена ссуда на автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083.

- КПЕР — общее число периодов платежей по аннуитету. Например, если получена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента КПЕР в формулу нужно ввести число 48.

- ПС — приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.

- БС — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение БС равно 0. Например, если предполагается накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость. Можно сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц.

- ТИП — число 0 (нуль) или 1, обозначающее, должна ли производиться выплата в конце периода (0) или в начале периода (1).

Пример 15.

Предположим, что нужно воспользоваться 9-процентной 15-летней ссудой. Объем ссуды составляет 150 000 000 рублей. С помощью Мастера функций можно определить величины ежемесячных выплат. Предварительно следует привести все другие значения к месячной норме.

Ввести таблицу (рис. 11), начиная с ячейки А15:

Рис. 11. Определение величины ежемесячных выплат

В ячейки В16 и В17 ввести соответствующие формулы.

Процентная ставка — годовая, поэтому для получения месячной ставки (Удельная ставка) соответствующее значение делится на 12 (0,09/12).

Срок действия ссуды — 15 лет, поэтому с учетом 12 платежей в год общее количество месячных выплат (КПЕР) составит 12x15.

Для ячейки В20 пошаговыми действиями Мастера функций выполните настройку функции ПЛТ. После этого в поле Значение диалогового окна Мастера функций вы увидите сумму ежемесячною взноса. А после нажатия на кнопку Готово результат отобразится в ячейке.

Пример 16.

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

В ячейки А22:В26 введите следующие значения, оставив пустой строку перед числовыми значениями (рис. 12):

Рис. 12. Определение величины ежемесячных выплат
с использованием таблицы подстановки

В ячейку В23 скопировать формулу для расчета ежемесячных выплат.

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

1. Выделить диапазон А23:В26, включив в него значения процентных ставок и расчетную формулу (формула должна находиться в ячейке, расположенной правее и выше заданных значений).

2. В меню Данные выбрать команду Таблица подстановки.

3. В поле «Подставлять значения по строкам в:» указать ячейку В15.

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

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

Функция БС

Функция БС предназначена для расчета будущей стоимости периодических постоянных платежей и единой суммы вклада или займа на основе постоянной процентной ставки.

БС(СТАВКА;КПЕР;ПЛТ;ПС;ТИП)

- СТАВКА — это процентная ставка за период.

- КПЕР — это общее число периодов платежей по аннуитету.

- ПЛТ — это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно ПЛТ состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если аргумент опущен, должно быть указано значение аргумента ПС. Например, ежемесячная выплата по четырехгодичному займу в 10 000 руб. под 12 процентов годовых составит 263,33 руб. В качестве значения аргумента выплата нужно ввести в формулу число -263,33.

- ПС — это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента ПЛТ.

- ТИП — это число 0 или 1, обозначающее, когда должна производиться выплата. Если этот аргумент опущен, то он полагается равным 0.

Для аргументов СТАВКА и КПЕР используются согласованные единицы измерения. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то СТАВКА должна быть 12%/12, а КПЕР должно быть 4*12. Если производятся ежегодные платежи по тому же займу, то СТАВКА должна быть 12%, а КПЕР должно быть 4.

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

Например, вы собираетесь вложить 1000 руб. под 6% годовых; (что составит в месяц 6%/12 или 0,5%), Вы собираетесь вкладывать по 100 руб. в начале каждого следующего месяца в течение следующих 12 месяцев. Сколько денег будет на счету в конце 12 месяцев?

БС (0,5%; 12; -100; -1000; 1). Результат 2301,40 руб.

Для выполнения расчета вызывается Мастер функций, в поле Категории выбираются финансовые функции и в поле Функция выбирается функция БС. В появившемся окне заполняются соответствующие поля путем подстановки значений аргументов, а если данная функция вычисляется в расчете, то вместо этого указываются адреса исходных данных из таблицы расчета.

Функция ПС

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

ПС — возвращает текущий объем вклада. Текущий объем — это общая сумма, которую составят будущие платежи. Например, когда вы берете взаймы деньги, заимствованная сумма и есть текущий объем для заимодавца.

ПС(СТАВКА;КПЕР;ПЛТ;БС;ТИП)

- СТАВКА — это процентная ставка за период.

- КПЕР — это общее число периодов платежей по аннуитету.

- ПЛТ — это выплата, производимая в каждый период.

- БС — это будущая стоимость периодических постоянных платежей и единой суммы вклада или займа на основе постоянной процентной ставки.

- ТИП — это число 0 или 1, обозначающее, когда должна производиться выплата. Если этот аргумент опущен, то он полагается равным 0.

Например, определите необходимую сумму текущего вклада в банк, чтобы через пять лет он достиг 5000 руб. при 20% годовых и ежегодном начислении процентов в конце года.

ПС(20%, 5, 5000). Результат 2009,39.

Функция КПЕР

Для определения срока платежа и процентной ставки используются функции КПЕР и ПРПЛТ.

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

КПЕР(СТАВКА;ПЛТ;ПС;БС;ТИП)

СТАВКА — процентная ставка за период.

ПЛТ — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам и не включает налогов и сборов.

ПС — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

БС — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0 (например, будущая стоимость займа равна 0).

ТИП — число 0 или 1, обозначающее, когда должна производиться выплата.

Например, рассчитаем срок погашения ссуды размером 5000 руб., выданной под 20% годовых при погашении ежемесячными платежами по 200 руб.

Синтаксис: КПЕР (20%/12; -200; 5000). Результат 32,6 месяца или 2,7 года.

Функция ПРПЛТ

Функция ПРПЛТ определяет значение процентной ставки за один расчетный период. Для нахождения годовой процентной ставки полученное значение необходимо умножить на число расчетных периодов в году.

ПРПЛТ(СТАВКА;ПЕРИОД;КПЕР;ПС;БС;ТИП)

СТАВКА — процентная ставка за период.

ПЕРИОД — это период, для которого требуется найти платежи по процентам; должен находиться в интервале от 1 до «КПЕР».

КПЕР — общее число периодов выплат годовой ренты.

ПС — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

БС — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0 (например, БС для займа равно 0).

ТИП — число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент «тип» опущен, то он полагается равным 0.

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

Синтаксис: ПРПЛТ (48; -200; 8000). Результат 0,008. или 0,8% в месяц или 9,6% годовых.

6.7. ПОДВЕДЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ В ТАБЛИЦЕ

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

- отсортировать таблицу по столбцу, содержащему группы, по которым надо подвести итоги;

- установить курсор в любую ячейку этого столбца;

- задать команду Данные ® Итоги;

- в поле При каждом изменении в указать столбец с группами, по которым надо подводить итоги;

- в поле Использовать функцию указать СУММА;

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

- нажать кнопку ОК.

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

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

6.8. АНАЛИЗ ДАННЫХ

6.8.1. Подбор параметра

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

Математическая суть задачи состоит в решении уравнения f(х) = а , где функция f(х) описывается заданной формулой, х -искомый параметр, а — требуемый результат формулы.

Для решения этой задачи необходимо выполнить следующие действия:

1. Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.

2. В меню Сервис выбрать команду Подбор параметра.

3. В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).

4. В поле Значение ввести значение, которое нужно получить по заданной формуле.

5. В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).

6. Щелкнуть по кнопке ОК. Пример.

Дано уравнение

Х^2 + ЗХ - 2 = А,

где А — требуемый результат формулы; X — искомый параметр. Определить такое значение параметра X, при котором А будет равно 20.

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

2. Задать команду Сервис > Подбор параметра.

3. В поле Установить в ячейке указать А4 (по умолчанию в это поле вводится адрес текущей ячейки).

4. В поле Значение ввести — 20.

5. В поле Изменяя значение ячейки указать адрес ячейки, в которой должен находиться параметр X.

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

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

6.8.2. Таблицы подстановки данных

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

Анализ выполняется при помощи таблицы подстановки данных.

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

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

Анализ формулы начинается с подготовки таблицы подстановки :

1. Левую верхнюю ячейку блока, отведенного под таблицу, оставить пустой.

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

3. В верхнюю строку блока, начиная со второй ячейки, ввести ссылки на ячейки с анализируемыми формулами.

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

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

5. В меню Данные выбрать команду Таблица подстановки.

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

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

7. Щелкнуть по кнопке ОК. Таблица будет заполнена значениями.

В случае анализа зависимости формулы от двух переменных таблица подстановки подготавливается по-другому:

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

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

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

4. Выделить таблицу подстановки.

5. В меню Данные выбрать команду Таблица подстановки.

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

7. В поле Подставлять значения по столбцам в ввести ссылку на ячейку с переменной, значения лля которой расположены в мерной строке таблицы подстановки, и Щелкнуть по кнопке ОК. Таблица, будет заполнена значениями.

6.8. 3 . Поиск решения

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

Целевая ячейка — это ячейка, для которой нужно найти максимальное, минимальное или заданное значения.

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

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

Чтобы запустить процедуру поиска решения, надо:

1. В меню Сервис выбрать команду Поиск решения. Откроется диалоговое окно Поиск решения.

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

3. В поле Изменяя ячейки ввести ссылки на изменяемые ячейки. (Если щелкнуть по кнопке Предположить, то Поиск решения самостоятельно определит изменяемые ячейки).

4. Для задания ограничений щелкнуть по кнопке Добавить.

5. В открывшемся диалоговом окне следует:

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

• во втором поле выбрать оператор ограничения (>, <, = и т.д.);

• в поле Ограничение ввести значение ограничения.

6. Для задания следующего ограничения щелкнуть по кнопке Добавить и повторить операции пункта 5.

7. Когда все ограничения будут заданы, щелкнуть по кнопке ОК, чтобы вернуться в диалоговое окно Поиск решения.

8. Изменять и удалять ограничения можно с помощью кнопок Изменить и Удалить.

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

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

Для возврата в диалоговое окно Поиск решения щелкнуть по кнопке ОК.

10. Для инициализации процедуры поиска решения щелкнуть по кнопке Выполнить. Полученные результаты будут выведены на рабочий лист.

После завершения процедуры решения в диалоговом окне Результаты поиска решения можно выполнить один из следующих вариантов:

• сохранить найденное решение или восстановить исходные значения на рабочем листе;

• сохранить параметры поиска решения в виде модели;

• сохранить решение в виде сценария;

• просмотреть любой из встроенных отчетов.

Текущие установочные параметры для поиска решения можно сохранить в виде модели.

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

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

Чтобы впоследствии загрузить модель, надо щелкнуть по кнопке Загрузить модель в диалоговом окне Параметры поиска решения. (Диалоговое окно Параметры поиска решения открывается при щелчке по кнопке Параметры в диалоговом окне команды Сервис - Поиск решения).

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

1. В диалоговом окне Результаты поиска решения выбрать Сохранить сценарий.

2. В поле Название сценария ввести имя сценария. Просмотреть сценарии можно с помощью команды Сервис -Сценарии.

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

Каждый отчет создается на отдельном листе текущей рабочей книги.

Для создания отчета надо в диалоговом окне Результаты поиска решения выбрать нужный тип отчета в поле Тип отчета. Можно выбрать сразу несколько типов (при выделении нескольких строк используется клавиша <Сtгl>).

Типы отчетов:

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

• Устойчивость — отчет содержит сведения о степени зависимости модели от изменений величин, входящих в формулы, применяемые в задаче (формулы модели и формулы ограничений).

• Пределы — выводится целевая ячейка и ее значение, а также список изменяемых ячеек, их значений, нижних и верхних пределов и целевых результатов.

Упражнение

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

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

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

Формулы и константы для расчетов:

Объем сбыта = 35 х Сезонный фактор х Затраты на рекламу + 3000.

Доход от оборота = Объем сбыта х Цена.

Себестоимость реализованной продукции = Объем сбыта х Себестоимость.

Валовая прибыль = Доход от оборота — Себестоимость реализованной продукции.

Накладные расходы =15% дохода от оборота.

Валовые издержки = Затраты на зарплату + Затраты на рекламу + Накладные расходы.

Прибыль = Валовая прибыль — Валовые издержки.

Коэффициент прибыльности = Прибыль / Доход от оборота.

Сезонный фактор: для I квартала — 0,9; для II — 1,1; для III -0,8; для IV квартала — 1,2.

Затраты на зарплату: для I квартала — 8000 р.; для II — 8000 р.; для III — 9000 р.; для IV квартала — 9000 р.

Затраты на рекламу для каждого квартала — по 10000 р.

Цена — 40 р.; себестоимость — 25 р.

2. Отформатировать таблицу: ячейкам, содержащим денежные величины, назначить денежный формат; ячейкам строки Коэффициент прибыльности назначить процентный формат; расчертить таблицу линиями.

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

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

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

6. Изменить ограничение, установив верхний предел расходов на рекламу за год 50000 р. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.

7. Сохранить в качестве сценария первоначальные значения величин затрат на рекламу в каждом квартале.

8. Загрузить каждую модель и сохранить результаты в качестве сценариев. Просмотреть все созданные сценарии.

9. Загрузить каждую модель и создать отчеты по результатам поиска решения.

10. Восстановить первоначальные значения с помощью первого сценария.

6.9. РАБОТА СО СПИСКАМИ (БАЗАМИ ДАННЫХ)

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

Для ведения больших, постоянно пополняющихся списков, для удобства их заполнения, а также для организации поиска данных по какому-либо критерию в Excel используются формы (маски данных), в которых отображаются значения ТОЛЬКО ОДНОЙ записи.

6.9.1. Создание списка (базы данных)

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

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

В списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные.

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

Для создания списка с помощью формы (маски ввода):

1. Сформируйте заглавную строку списка. В каждом столбце этой строки введите название соответствующего поля записи.

2. Щелкните на любой из ячеек заглавной строки и выберите команду Данные ® Форма.

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

<Tab> — для перехода вниз и <Shift><Tab> — для перехода вверх.

4. Нажмите кнопку Добавить для помещения значений данной записи в список и введите следующую запись.

Для завершения процесса ввода данных нажмите кнопку Закрыть.

6.9.2. Поиск записей в списке

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

1. Установите курсор в любую ячейку списка и выберите команду Данные ® Форма.

2. Нажмите кнопку Критерии.

3. Введите критерии поиска в одно или несколько полей записи. Так, для поиска всех фамилий, начинающихся на букву А, достаточно в поле фамилии набрать А. Для поиска записей с величиной оклада, большей 500000, в поле оклада следует ввести «>500000».

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

Для задания нового критерия поиска нажмите кнопку Очистить.

6.9.3. Редактирование записей с помощью формы

Для исправления значений (но не формул) в любом поле записи:

1. Установите курсор в любую ячейку списка и выберите команду Данные ® Форма.

2. Найдите требуемую запись с помощью кнопок Назад и Далее.

3. Отредактируйте запись.

4. Нажмите кнопку Закрыть.

6.9.4. Удаление записей с помощью формы

Для удаления записи:

1. Установите курсор в любую ячейку списка и выберите команду Данные ® Форма.

2. Найдите требуемую запись и нажмите кнопку Удалить.

3. Подтвердите удаление, нажав ОК.

4. Нажмите кнопку Закрыть.

6.9.5. Добавление записей

При использовании диалогового окна команды Форма вновь создаваемые записи заносятся в конец списка.

Для добавления записи внутрь списка:

1. Установите курсор в строку, перед которой будет вставлена новая строка.

2. Выберите команду Вставка ® Строка и введите в нее соответствующие значения.

6.9.6. Фильтрация списка

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

6.9.7. Выбор элементов списка с помощью Автофильтра

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

• Установить курсор в любую ячейку списка, задать команду Данные ® Фильтр, а затем выбрать пункт Автофильтр.

• Нажать на кнопку со стрелкой в том столбце, по которому надо фильтровать данные.

• Выбрать любой элемент из списка.

• При использовании пункта Условие можно задавать до двух критериев фильтрации одного столбца, выбирая из списка операторов сравнения и списка значений данного поля те значения, которые необходимы для задаваемого критерия. В случае неточного совпадения значений можно пользоваться подстановочными символами. Завершив установки, нажмите ОК.

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

Для восстановления всех записей списка необходимо задать команду Данные ® Фильтр ® Показать все или же в раскрывающемся списке автофильтра выбрать пункт Все. Для отмены фильт­рации необходимо повторно выбрать команду Данные ® Фильтр ® Автофильтр.

6.9.8. Фильтрация списка с использованием сложных критериев

В данном случае должна быть выполнена следующая процедура:

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

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

• Если на экран надо вывести записи, удовлетворяющие одному из критериев (логическое ИЛИ), то ввод условий производится в разные строки одного столбца.

2. Установить курсор в любую ячейку списка и задать команду Данные ® Фильтр, а затем выбрать пункт Расширенный фильтр.

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

• В поле Диапазон критериев указать диапазон тех ячеек, где размещается область критериев.

• Если требуется поместить результат в целевую область, то в поле Поместить результат в диапазон следует указать диапазон, содержащий заголовок целевой области.

• Нажать кнопку ОК.

Для восстановления списка следует выбрать команду Данные ® Фильтр ® Показать все.

6.9.9. Сводные таблицы

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

Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц.

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

1. Установите курсор в любую ячейку списка и выберите команду Данные - Сводная таблица.

2. В открывшемся диалоговом окне Мастер сводных таблиц отметьте опцию в списке или базе данных МS Ехсеl.

3. Далее определите диапазон, с которым будет работать Мастер сводных таблиц. Щелкните на кнопке Далее.

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

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

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

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

7. РЕКОМЕНДУЕМАЯ ЛИТЕРАТУРА

1. Практикум по экономической информатике. Учеб. Пособие: ч. 1/ Под ред. Е. Л. Шуремова и др.

2. Информатика. Базовый курс. Под ред. Симоновича. — СПб.: Питер, 2002

3. Гусева О. Л., Миронова Н. Н.Практикум по Excel. — М.: Финансы и статистика, 1997

4. Нельсон, Стивен Л. Анализ данных в Microsoft Excel для «чайников». — М.: Издательский дом «Вильямс», 2003

Приложение 1

ОБРАЗЕЦ ОФОРМЛЕНИЯ ТИТУЛЬНОГО ЛИСТА

МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ ОТКРЫТЫЙ УНИВЕРСИТЕТ

Филиал в г. Воскресенске

Кафедра Прикладной математики

Контрольная работа

по дисциплине «Информатика»

на тему:

_____________________________________________

Выполнил(а) студент (ка)

__________________форме обучения

специальности ___________________

___________курса, _________группы,

шифр: __________________________

______________ _________________

Руководитель работы

______________________ _______________ ________________

200_ – 200_ уч. год

Приложение 2

Варианты заданий к контрольной работе
по теме «Обработка данных с помощью средств MS Excel»

Вариант 1

Используя соответствующие финансовые функции, решите следующие задачи.

1. Определите, какая сумма окажется на счете, если вклад размером 900 руб. положен под 9 % годовых на 19 лет, а проценты начисляются ежеквартально.

2. Какая сумма должна быть выплачена, если 6 лет назад была выдана ссуда 1500 руб. под 15 % годовых с ежемесячным начислением процентов.

3. Взносы на сберегательный счет составляют 200 руб. в начале каждого года. Определите, сколько будет на счете через 7 лет при ставке 10 %.

4. Есть два варианта вложения средств в сумме 300 тыс. руб. в течении 4 лет: в начале каждого года под 26% и в конце каждого года под 38 % годовых. Определите, сколько денег окажется на счете для каждого варианта через 4 года.

5. Ссуда в 5000 руб. погашается ежемесячными платежами по 141,7 руб. Через сколько лет произойдет погашение ссуды, если годовая ставка процента 16 %.

6. Какую сумму необходимо положить на депозит под 16,5 %, чтобы через 3 года получить 44 тыс. руб. при полугодовом начислении процентов.

7. По сертификату, погашаемому через 3 года выплатой в 250 тыс. руб., начисляются проценты раз в полгода. Определите цену продажи, если номинальная ставка 38 %.

8. Определите необходимую сумму текущего вклада, чтобы через 12 лет он достиг 5000 руб., если процентная ставка по нему составляет 12 %.

9. Рассматриваются два варианта покупки дома: заплатить сразу 100000 руб. или платить в рассрочку в течении 15 лет по 940 руб. ежемесячно. Какой вариант выгодней, если ставка процента 8 % годовых.

10. Какую сумму нужно ежемесячно вносить на счет, чтобы через 3 года получить 10 млн. руб., если годовая ставка 18,6 %.

11. Определите ежемесячные выплаты по займу в 10 млн. руб., взятому на 7 месяцев под 9 % годовых.

12. Определите величину ежегодной амортизации оборудования начальной стоимостью 8000 тыс.руб., если срок эксплуатации его 10 лет, а остаточная стоимость 500 тыс. руб. Выполнить расчеты, используя функцию АПЛ.

Вариант 2

1. Создать таблицу, содержащую сведения о сотрудниках предприятия. Названия колонок: «Фамилия», «Должность», «Отдел», «Дата поступления на работу», «Стаж работы», «Зарплата», «Надбавка», «Премия», «Всего начислено», «Пенсионный фонд», «Налогооблагаемая база», «Налог», «Выплатить». Колонки «Фамилия», «Должность», «Отдел», «Дата поступления на работу», «Зарплата» заполнить произвольными данными, при этом в колонке «Зарплата» использовать значения от 500 до 3000 р., в колонке «Должность» использовать 5-6 названий (например, техник, инженер, экономист, водитель и т.д.), в колонке «Отдел» использовать 3-4 названия (например, бухгалтерия, отдел кадров, транспортный отдел, конструкторский отдел). Значения в остальных колонках рассчитать по формулам:

Стаж работы = (Текущая дата — Дата поступления на работу)/365.

Результат округлить до целого.

0, если стаж работы меньше 5 лет;

Надбавка = 5 % от зарплаты, если стаж работы от 5 до 10 лет;

10% от зарплаты, если стаж работы больше 10 лет.

Премия = 20 % (Зарплата + Надбавка).

Всего начислено = Зарплата + Надбавка + Премия.

Пенсионный фонд = 1 % от «Всего начислено».

Налогооблагаемая база = Всего начислено — Пенсионный фонд.

12 % от Налогооблагаемой базы, если Налогооблагаемая база меньше 1000 руб.

20 % от Налогооблагаемой базы, если Налогооблагаемая база больше 1000 руб.

Выплатить = Всего начислено — Пенсионный фонд — Налог.

В таблице должно быть не менее 10 строк.

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

3. Присвоить рабочему листу имя Сведения о сотрудниках. Это же название можно использовать в качестве заголовка таблицы.

4. Используя форму данных, добавить в список еще 10 записей.

5. Используя форму данных, выполнить поиск записей по следующим критериям:

· заданная должность;

· заданный отдел;

· стаж работы больше заданного;

· заданная должность и зарплата меньше заданной;

· заданный отдел и стаж работы больше заданного.

6. Выполнить сортировку данных по:

· отделам;

· фамилиям;

· отделам и фамилиям;

· отделам и зарплатам;

· отделам, должностям и фамилиям;

· отделам, должностям, зарплатам, фамилиям.

7. Используя Автофильтр, отобрать данные о сотрудниках:

· фамилии которых начинаются на заданную букву;

· зарплата которых больше заданной;

· стаж работы которых находится в заданном диапазоне;

· с заданной должностью и зарплатой в заданном диапазоне.

8. Используя расширенный фильтр, отобрать данные о сотрудниках:

· с зарплатой от 1000 до 1500 руб.;

· со стажем работы меньше 7 лет или с зарплатой меньше 1000 руб.

· со стажем работы от 5 до 10 лет и с зарплатой от 700 руб. до 1500 руб.;

· зарплата которых выше средней (использовать функцию СРЗНАЧ);

· зарплата которых выше средней, а стаж работы от 5 до 15 лет;

· зарплата которых выше средней, а стаж работы — меньше среднего.

· обо всех техниках конструкторского отдела, у которых либо стаж работы больше 5 лет, либо зарплата больше 900 руб.;

9. Скопировать рабочий лист под именем «Итоги». Открыть таблицу на листе «Итоги».

10.Используя инструмент подведения итогов, разбить список на группы по отделам и подвести промежуточные и общие итоги по полям «Фамилия» (операция Количество значений), «Зарплата», «Надбавка», «Премия», «Пенсионный фонд», «Налог», «Выплатить» (операция СУММА).

11.Открыть таблицу на листе Сведения о сотрудниках.

12.Создать сводную таблицу суммарных выплат по отделам, внутри отделов — по фамилиям. Для этого в макете поместить поля «Отдел» и «Фамилия» в область «Строка», а поле «Выплатить» — в область «Данные».

13.Скопировать лист со сводной таблицей. Внести изменения в копию, чтобы данные по каждому отделу выводились на отдельной странице (для этого поле «Отдел» переместить в область «Страница»).

14.Создать еще одну копию первой сводной таблицы. Изменить копию так, чтобы подчитывалось количество сотрудников в отделах (для этого удалить из области «Данные» поле «Выплатить» и поместить туда поле «Фамилия»).

Вариант 3

Составить таблицу для ведения учета основных средств предприятия (рис. 1).

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

В ячейку А1 (дата расчета) может быть введено любое число в формате значений типа «дата» Ехсеl.

Значения в колонках А, В, С, в вводятся произвольно вручную со следующими ограничениями:

1) значения в колонке В больше нуля;

2) значения в колонке С больше нуля и не больше 100;

3) в колонку в вводятся значения в формате даты Ехсе1.

Число строк между заголовком таблицы и строкой «Итого» может быть произвольным.

Значения в колонке G рассчитываются по следующим правилам.

Если дата ввода в эксплуатацию (D) не заполнена или она больше, чем значение в ячейке А1, то срок эксплуатации (G) равен 0. В противном случае, срок эксплуатации равен числу месяцев от даты ввода в эксплуатацию (D) до даты расчета (А1). При составлении расчетной формулы следует руководствоваться следующим правилом расчета срока эксплуатации в месяцах:

СрокЭксплуатации=12*(ГодРасчета-ГодВводаВЭксплуатацию)+МесяцРасчета-МесяцВводаВЭксплуатацию

При записи формулы рекомендуется использовать функции Ехсеl ГОД() и МЕСЯЦ().

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

Значения в колонке Н рассчитываются в соответствии с правилом:

РасчетныйИзнос=Стоимость*НормаАмортизации*СрокЭксплуатации/1200

Колонки G и Н чисто технологические и используются для упрощения записи формул расчета колонок Е и F.

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

ИзносЗаМесяц=Стоимость*НормаАмортизации/1200

Если расчетный износ (колонка Н) больше стоимости, то накопленный износ (колонка F) равен стоимости, в противном случае накопленный износ равен расчетному износу.

По износу за месяц и накопленному износу должны быть подведены итоги по всей таблице.

Вариант 4

Составить таблицу (рис. 1). При построении таблицы руководствоваться следующими правилами.

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

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

В строке 4 задаются формулы для расчета итогов по соответствующим колонкам.

Значения таблицы в колонках А («Продукция»), В («Материалы»), С («Зарплата»), начиная со строки 5, задаются вручную. В колонку А («Продукция») в произвольной текстовой форме вводятся данные о видах выпускаемой продукции. В колонке В («Материалы») вводятся данные о материалах, затраченных на производство данного вида продукции, а в колонку С («Зарплата») — сведения о зарплате, выплаченной за ее производство.

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

В колонке Е, в зависимости от номера правила распределения, производится вычисление доли накладных расходов, относимых на конкретный вид продукции.

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

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

Очевидно, что сумма долей накладных расходов, относимых на все виды продукции в соответствии с правилами их расчета должна быть равна 100%.

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

Вариант 5

1. Создать новую книгу и ввести таблицу (рис. 1):

• шапку таблицы ввести в две строки, задав для ячеек, содержащих названия граф, расположение по центру столбца;

• переопределить ширину столбцов («Номер п/п») и («Показатели»);

• названия граф («Номер п/п») и («Итого за год») ввести в две ячейки;

• название графы («Показатели») расположить в две ячейки сверху вниз, выполнив действия;

• значение ячейки («Год») отцентрировать по кварталам. Расчертить таблицу, как указано на рис. 1.

3. Автоматически пронумеровать все показатели в столбце А, задать для чисел столбца А выравнивание по центру.

4. Выполнить необходимые расчеты:

• данные по строке «Валовая прибыль» рассчитать как разность между торговыми доходами и торговыми расходами;

• данные по строке «Общие затраты» получить как сумму трех предыдущих строк:

• данные по строке «Производственная прибыль» получить как разность между валовой прибылью и общими затратами:

• данные по строке «Удельная валовая прибыль» получить как результат деления производственной прибыли на торговые доходы;

• данные в колонке «Итого за год» получить суммированием квартальных данных.

5. Задать для строки «Удельная валовая прибыль» Процентный формат, а для всех остальных строк — Формат с разделителями.

6. На строке 1 (при необходимости вставить строку) ввести заголовок, задав для него более крупный жирный шрифт и расположение по центру всей таблицы.

7. Зафиксировать титулы таблицы (головку (шапку) и боковик). Перемещением по таблице отобразить на экране данные только трех столбцов: «Номер п/п», «Показатели». «Итого за год». Снять закрепление областей.

8. На листе с таблицей по данным строки «Торговые расходы фирмы» за первые три квартала построить разрезанную круговую диаграмму (при выделении данных для построения диаграммы использовать и названия кварталов).

9. Добавить на диаграмму данные по 4-му кварталу.

10. По данным строки «Валовая прибыль» построить гистограмму. Оформить заголовки и легенды[3] диаграмм, расположить диаграммы рядом под таблицей.

Рис. 1. Структура доходов и расходов фирмы

11. Изменить числовое значение за 4-й квартал по торговым расходам фирмы. Проследить зависимость графических данных в диаграммах от числовых в таблице.

12. На отдельном листе построить нормированную гистограмму со столбцами в виде цилиндров по данным всей таблицы (исключая графу «Итого за год»).

Вариант 6

Создать новую рабочую книгу, ввести и оформить таблицу как на рис. 1.

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

Рис. 1. Прогнозирование прибыли предприятия

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

2. Оформить диаграмму: ввести нужные заголовки и форматы, разместить на отдельном рабочем листе.

3. Добавить линию тренда[4] (щелкнуть правой кнопкой мыши по линии графика, для вывода контекстного меню).

4. В диалоговом окне Линия тренда, вкладка Тип выбрать линейную аппроксимацию[5] .

5. Во вкладке Параметры установить флажок в поле показывать уравнение на диаграмме.

6. В данном примере уравнение линии тренда имеет вид У = 67,833Х - 133937, где X - год.

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

8. В колонку в ввести формулу для расчета абсолютной разности между значениями статистическими и полученными в результате аппроксимации. Для этого воспользоваться математической функцией =АВS (В-С).

9. В ячейку D4 ввести статистическую функцию для расчета максимальной погрешности =МАКС(D3:D13). В данном случае эта величина = 52,002.

10. На основе исходных данных построить еще одну диаграмму, выбрав логарифмическую функцию. В данном случае функция будет иметь вид у = 135325Ln(х) — 1Е+06.

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

12. Сравнить погрешность различных типов аппроксимации. Чем меньше погрешность, тем точнее прогноз.

13. Выбрать более точный тип аппроксимации, рассчитать прогноз на 2000 и 2001 гг.

14. Рассчитать прогноз прибыли на 2000 и 2001 гг. на основе функций прогнозирования ПРЕДСКАЗ, РОСТ и ТЕНДЕНЦИЯ.

15. Сделать сравнительный анализ используемых методов прогнозирования. Какой из них дает более точный прогноз?

16. Результаты оформить в виде отчета в документе MS Word, скопировав в него со связью все необходимые данные и диаграммы.

Вариант 7

Три фирмы-поставщика (ФИРМА 1,ФИРМА 2, ФИРМА 3) предлагают фармпредприятию компоненты (компонента1, компонента2, компонента3, компонента4 компонента5), необходимые для изготовления трех препаратов (препарат1, препарат2, препарат3). Компоненты отличаются ценой и сроком хранения. Необходимо:

1. Выполнить закупку компонентов по максимальному сроку хранения.

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

Исходные данные:

1. Процентные весовые отношения компонент для изготовления препаратов и вес каждого препарата в условных весовых единицах (таблица 1). Имя этой таблицы — КОМПОНЕНТЫ.

2. Наименования компонентов, цена условной весовой единицы компоненты, срок хранения, предлагаемые фирмой - поставщиком (таблицы 2,3,4). Имена этих таблиц — ФИРМА 1,ФИРМА 2, ФИРМА 3.

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

1. Наименование фирмы – поставщика и цены для каждой компоненты при максимальном сроке хранения. Имя этой таблицы – ЗАКУПКИ.

2. Стоимость расходных материалов (т. е. стоимость требуемых компонент) для изготовления требуемого количества препаратов (в штуках) по каждому наименованию и общую стоимость требуемых компонент по всем наименованиям с учетом сделанных ранее закупок компонентов. Создается электронная таблица заданного формата (таблицы 5). Имя этой таблицы — РАСЧЕТ.

Таблица 1

Таблица 2

Таблица 3

Таблица 4

Таблица 5

Таблица 6

Вариант 8

1. Создайте таблицу как на рис. 1.

Рис. 1. Список расходов

2. Используя форму данных, добавьте в список данные об АО Престиж: «30.06.97. Материалы. $800, АО Престиж».

3. Используя форму данных, просмотрите информацию о Казакове и измените сумму зарплаты за 05.09.99 на $2800.

4. Используя форму данных, просмотрите все данные списка о расходах на материалы, превышающих $12000.

5. Используя Автофильтр, отобразите все данные списка по АО ИНВЕСТ.

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

7. По каждому расходу подведите итог по полю Сумма, воспользовавшись командой Данные > Итоги, предварительно отменив Автофильтр и отсортировав данные списка по полю Расход.

8. Присвоите имя (например, имя Список) диапазону ячеек, содержащему все данные списка (включая заглавную строку).

9. Вставьте перед диапазоном со списком 11 пустых строк.

10. В ячейки А1:01 скопируйте шапку таблицы.

11. Используя Расширенный фильтр, отобразите все данные списка по зарплате, используя для области критериев ячейки А1:В2. Изменив область критериев, отобразите все данные списка по накладным расходам.

12. Внесите изменения в область критериев, добавив в ее шапку еще одну ячейку с названием Сумма, так чтобы отобразились накладные расходы только от $500 до $1000.

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

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

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

16. Для заданного критерия отбора вычислите общую сумму:

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

• в ячейку В5 введите текст «Сумма по заданному критерию».

17. Используя функцию БСЧЕТ, подсчитайте в ячейке С6 количество выданных зарплат. Имя поля, указываемого в окне Мастера функций, оставьте прежним. В ячейку С5 введите текст «Количество».

18. Подсчитайте сумму расходов и количество записей по материалам, изменив область критериев. (Область значений при этом изменится автоматически.) 19. Сформируйте таблицу для расчета суммы и количества расходов по каждому виду расхода, скопировав в ячейки А7, А8 и А9 значения поля Расходы: Материалы, Зарплата и Накладные расходы. В ячейках В6 и С6 рассчитайте общую сумму и количество расходов, очистив критерий поиска.

20. Для автоматической подстановки значений из ячеек А7, А8 и А9 в ячейку В2 области критериев и построчного получения результатов в сформированной таблице, выделите диапазон А6:С9 и выполните команду Данные ® Таблица подстановки.

21. Сформируйте еще одну таблицу для расчета количества и суммы расходов, связанных с АО ИНВЕСТ, ТОО Надежда и ЗАО БИН, а также найдите максимальные и минимальные из них, воспользовавшись возможностью автоматической подстановки значений в область критериев для получения соответствующих результатов.

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

23. Сформируйте еще одну таблицу для расчета количества каждого из расходов по АО Престиж, ЗАО БИН и Васильевой М.Ф., для этого значения получателей запишите в разных строках под ячейкой с формулой, а значения расходов — в разных столбцах, правее ячейки с формулой. Выделите сформированную таблицу и выполните команду Данные - Таблица подстановки, указав, в какую ячейку области критериев значения будут подставляться построчно, а в какую — из столбцов.

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

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

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

• Отобразите данные по зарплате.

• Используя Мастер сводных таблиц, добавьте в область данных количество значений по полю Получатель.

• Отобразите данные по накладным расходам.

• Используя панель инструментов Сводные таблицы (кнопка Отобразить страницы), отобразите информацию по каждому расходу.

27. На этом же рабочем листе, начиная с новой ячейки, создайте еще одну сводную таблицу, позволяющую отобразить в один столбец сумму расходов по каждой дате.

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

Вариант 9

1. Создайте таблицу, используя базу данных деклараций, приведённую в табл. 1.

2. Найти, на какую сумму было закуплено мужского, женского и детского трикотажа (код 61*) по отдельности и всего трикотажа ежеквартально. Для этого необходимо провести отбор записей согласно выбранному критерию и необходимые вычисления, используя методы анализа данных в списках.

3. По сводной таблице постройте диаграмму.

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

Таблица 1

Вариант 10

1. Создать таблицу (рис. 1), начиная с третьей строки:

· шапку таблицы ввести в две строки, задав для ячеек, содержащих названия граф, расположение по центру столбца;

· текст «Объем страховых сделок» расположить по центру четырех столбцов;

· названия граф «№ п/п», «Комиссионное вознаграждение» расположить в 2 ячейки по вертикали, объединив эти ячейки;

· для граф «№ п/п», «за III квартал», «Комиссионное вознаграждение» — задать перенос по словам;

· названия граф «июль», «август», «сентябрь» ввести, используя автозаполнение.

2. Ввести фамилии, после этого переопределить ширину столбца. В, сделав его равным максимальной длине фамилии.

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

4. Ввести числовые данные в столбцы D, Е и F.

5. Автоматически пронумеровать все фамилии в столбце А.

6. Сделать сортировку всей таблицы по фамилиям (без столбца А).

Рис. 1. Отчет о деятельности страховых агентов

7. Вычислить для каждого страхового агента объем сделок за III квартал как сумму сделок за июль, август и сентябрь.

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

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

10. По каждой фамилии рассчитать данные в графе «Комиссионное вознаграждение» как произведение «Объема страховых сделок за III квартал» на значение процента, вычисленного функцией ПРОСМОТР по Тарификационной таблице. Для функции ПРОСМОТР выбрать синтаксическую форму ПРОСМОТР(искомое_значение;массив) и указать в формуле имя массива — Тариф. Для уточнения действий функции ПРОСМОТР воспользоваться Справочной системой Ехсеl

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

12. Справа от графы «Комиссионное вознаграждение» рассчитать ранг каждого страхового агента по показателем III квартала.

13. Создать имя для диапазона ячеек с данными за III квартал и применить его к ячейкам диапазона с данными о ранге.

14. Рассчитать процентную норму ранга за III квартал.

15. Разграфить таблицу, написать заголовок по центру таблицы более крупным шрифтом.

Рис. 2. Тарификационная таблица

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

[3] Более подробно о функциях прогнозирования см. в справочной системе MS Excel.

[3] Легенда — подпись, определяющая узор или цвета рядов или категорий данных на диаграмме.

[4] Линии тренда позволяют графически отображать тенденции данных и прогнозировать их дальнейшие изменения. Более подробно см. в справочной системе MS Excel.

[5] Аппроксимацией называется процесс подбора опытным путём формулы f( x) .