Geum.ru

Учебно-методический комплекс Для специальности: 080503 «Антикризисное управление» Москва, 2008

Вид материалаУчебно-методический комплекс

Содержание


7. Тематика контрольных работ и методические указания по их выполнению
=слчис () (=rand () )
=целое(99.44) (into )
=cmei1usa$ 1,3,4) (offset ())
Функции excel, зависящие от того, как и когда к ним
Практическое применение функции случмежду
Подобный материал:
1   ...   10   11   12   13   14   15   16   17   18

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



Цель контрольных работ

Цель контрольных работ — продемонстрировать, насколько широкие возможности открываются перед управленцем, когда он начинает комбинировать функции. Совместное использование СЛУЧМЕЖДУ и ВПР предоставляет возможность случайного выбора элементов, хотя они и не укладывались в стандартные рамки генератора случайных чисел.

По выполнении контрольных работ Вы должны узнать, что функции Excel дают гораздо более широкие возможности, чем элементарные арифметические вычисления. Также вы имеете возможность познакомиться с примерами практического применения различных функций, включая функции ИСТИНА, ложь, СТРОКА, СТОЛБЕЦ, СЕГОДНЯ, ТДАТА, СЛЧИСЛО, СЛУЧМЕЖДУ, ОКРУГЛ, ОКРВВЕРХ, ЦЕЛОЕ И ВПР.

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

Предварительные замечания

Условные обозначения. Ниже перечислены условные обозначения, встречающиеся в книге.

Табличные функции и ячейки. Встроенные функции рабочего листа Excel (например, СУММ ИЛИ СЛЧИС), а также стандартные функции из надстроек (в частности, СЛУЧМЕЖДУ) выделены ПРОПИСНЫМИ БУКВАМИ. Пользовательские имена, присвоенные диапазону ячеек, набираются без пробелов, первая буква каждого слова выделена прописной (например, НекоеЗначениеЯчейки).

Комбинации клавиш и команд

Нажатие отдельной клавиши обозначается ее названием на клавиатуре: , и т.д. В комбинациях клавиш присутствует знак "плюс" — (комбинация для перезагрузки DOS).

Последовательности команд меню обозначаются следующим образом ФайлСохранить (Filc=>Save). В приведенном примере нужно открыть меню Файл и выбрать в нем команду «Сохранить» для сохранения документа.


Программирование без программирования

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

Каждая таблица представляет собой отдельную полноценную программу. При этом программирование иногда сводится к внесению в столбец нескольких чисел. Но задачу можно ставить и по-другому — анализ ежемесячных объемов продаж и финансовых показателей магазинов за пять лет в сотнях торговых центров по всей стране с разделением по кодам NAICS/SIC, демографическим районам, площадям арендуемых торговых помещений и т.д. Важно также, что как в простом, так и в сложном случае задача решается с помощью одного и того же программного инструмента (Excel). Вряд ли вы найдете другие программы, способные успешно справиться с заданиями столь разного типа. Хотелось бы, чтобы, работая с таблицами, вы это помнили.

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


Контрольная работа №1. Основные функции Excel


Функции Excel весьма разнообразны; условно их можно поделить на три группы. Ниже приведены названия этих групп и перечислены некоторые входящие в них функции.

• Функции, не требующие входных данных:

=СЛЧИС () (=RAND () )

=СЕГОДНЯ() (=TODAY())

=ИСТИНА() (=TRUE())

=СТРОКА(> (-ROWO)

• Функции одного аргумента:

=СЖПРОБЕЛЫ(" Удаляет лишние пробелы в начале и конце строки ") (TRIMO)

=ЦЕЛОЕ(99.44) (INTO )

=ДАТА(" 1/28/2004") (DAYO)

=СТРОКА(НалСтавка_Указанная_Мной)

• Функции, принимающие в качестве входных данных более одного значения (или аргумента):

=СУММПРОИЗВ(А1:А10,т:О10) (=SUMPRODUCT () )

=ПСТР("возвращает четыре знака, начиная с 6го,б,4) (MID О )

=CMEI1USA$ 1,3,4) (OFFSET ())

Некоторые функции Excel содержатся сразу в нескольких категориях.


Некоторые важные функции и их применение

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

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


Контрольная работа №2. Функции без входных данных

Само название полностью отображает особенности этих функций. Будучи от природы независимыми, параметрах для корректной работы они не нуждаются в специальных. Самые независимые функции в этой категории — ИСТИНА () (TRUE0), ложь () ( F A L S E O H I M O (Р1()).

Указанные функции упрямо возвращают одно и то же значение, независимо от того, где и когда произведены вычисления. Какое значение возвращает функция пи (), я думаю, вы уже догадались. Функция ИСТИНА () возвращает значение ИСТИНА; функция ЛОЖЬ () возвращает ложь. Но зачем нужны в Excel функции ИСТИНА () и ложь () ? Оказывается, в ряде случаев они бывают весьма полезны, но не так, как может показаться на первый взгляд. Они не используются в "готовых формулах", а выступают в качестве заменителей в процессе создания сложных формул, помогая проверить их и оценить. Обратите внимание на приведенную ниже формулу:


=ЕСЛИ(ОбычноНебольшаяВеличина>100000,ЧрезвычайнаяСитуация,

ОбычнаяСитуация)


ОбычноНебольшаяВеличина — заменитель числа, которое, как вы понимаете, как правило, представляет собой небольшое значение. Насколько часто оно будет превышать значение 100 000? Нечасто. Следовательно, на практике вероятность получитьв результате работы функции значение ЧрезвычайнаяСитуация очень невелика. При создании таблицы может оказаться, что многие формулы должны вести себя иначе при возврате значения ЧрезвычайнаяСитуация. В таком случае целесообразно принудительно смоделировать значение ЧрезвычайнаяСитуация. Для этого замените значение ОбычноНебольшаяВеличина>10000 величиной TRUE или функцией, которая возвращает это значение, и ваша формула будет выглядеть так:


=ЕСЛИ(ИСТИНА(),ЧрезвычайнаяСитуация,ОбычнаяСитуация)


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

В конце выполнения контрольных работ вы убедитесь, что иногда ИСТИНА/ЛОЖЬ могут выступать в качестве специальных параметров для определенных функций Excel.


Контрольная работа №3. Пространственные функции EXCEL

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


=СТРОКА() (=ROW()) и =СТОЛБЕЦ () (=COLUMN()).


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

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


=(СТРОКА()-СТРОКА(НачальнаяТочка))*

НекоторыеФинансовыеВопросыАктуальныеДляТаблицы


Обратите внимание на тот факт, что при вычислении входящей величины для функции СТРОКА или СТОЛБЕЦ она использует позицию ячейки, на которую ссылается имя ячейки, а не свою собственную. При отсутствии вводных данных функция СТРОКА использует свою собственную позицию.

Безусловно, можно написать эквивалент приведенной выше формулы, не задействуя строки (например, расположить сбоку от таблицы столбец с числами). А что, если из-за недостатка места нельзя предусмотреть отдельный столбец для чисел? При вычислении со строками задача заключается в том, чтобы создать "поточную" вычислительную систему.

Термин СТРОКА () -СТРОКА (НачальнаяТочка) производит то же действие непосредственно внутри формулы, не полагаясь на выделенный фрагмент с нумерацией строк. Данный способ имеет свои преимущества: при вставке и удалении задействованных строк и столбцов расчеты останутся верными. Этот же метод используется в утилите для просмотра данных (Data Viewer) "Анализ данных". Как правило, техника встроенного расчета комбинируется с функцией Excel СМЕЩ (OFFSET).


Контрольная работа №4 Временные функции EXCEL

Выше речь шла о функциях без входных данных, которые зависят от того, откуда к ним обращаются. А как насчет того, когда к ним обращаются? В формулах таблиц могут присутствовать и временные аспекты. Следует обратить внимание на две функции, предназначенные именно для этой цели: СЕГОДНЯ (TODAY) и ТДАТА (NOW). Чтобы понять разницу между ними, представьте себе СЕГОДНЯ как ЦЕЛОЕ (ТДАТА О ). Функция ТДАТА возвращает текущую дату и время в числовом формате. Десятичную часть этого числа составляют часы, минуты, секунды и доли секунд. При применении функции ЦЕЛОЕ К ТДАТА десятичная составляющая будет отброшена; останется только целое число (т.е. точная календарная дата, которая вычисляется с помощью функции СЕГОДНЯ).

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

ФУНКЦИИ EXCEL, ЗАВИСЯЩИЕ ОТ ТОГО, КАК И КОГДА К НИМ

ОБРАЩАЮТСЯ

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

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

Если в работе с таблицами Excel вам понадобятся более мощные математические инструменты, воспользуйтесь утилитой Mathematica Link for Excel от Wolfram Research.

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

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

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


=НижнийПредел+(ВерхнийПредел-НижнийПредел)*СЛЧИС О


Применение данной формулы для получения проверочных значений продемонстрировано в файле таблицы ch0l-06random.xls (рис. 1.19).

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

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

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




Рис. 7.1. Получение случайных чисел в диапазоне от верхнего до нижнего предела.


Контрольная работа №5 Функции EXCEL, связанные с точностью и неопределенностью

Для округления случайных чисел до двух знаков после запятой воспользуйтесь функцией ОКРУГЛ (ROUND), как на втором листе рабочей книги указанного выше файла (рис. 7.2). В данном случае формула выглядит так:


=ОКРУГЛ(НижнийПредел+(ВерхнийПредел-НижнийПредел)*СЛЧИС 0 , 2 )

( = R O U N D ( L o w e r L i m i t + ( U p p e r L i m i t - L o w e r L i m i t ) * R A N D ( ) , 2 )


В то же время на рис. 1.20 проверочная величина 6 (Test Value 6) имеет вид 42.7, а не 42 .70. Никакой ошибки в данном случае нет. 42.7 и 42.70 — одно и то же значение. Некоторых пользователей, в частности тех, кто работает над публикуемыми отчетами, такой вариант может не устроить. Как правило, их интересует безукоризненное выравнивание и итоговая сумма в 100%. В подобных случаях параметры форматирования следует задать в начале работы, чтобы потом не возвращаться к данному вопросу. Отформатируем созданный список, используя два десятичных знака после запятой и забудем об этой проблеме. Если речь идет о группе из случайных чисел, никаких сложностей не предвидится. А что, если это процентные значения, которые должны в сумме составлять точно 100%?



Рис. 7.2. Округление до двух десятичных знаков

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

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

Несколько слов об округлении. Синтаксическая структура функции ОКРУГЛ имеет такой вид:

ОКРУГЛ(число;число_разрядов)


Как вы уже убедились, чтобы округлить число до двух десятичных знаков после запятой, вместо значения число разрядов необходимо подставить 2. Чтобы отменить десятичные знаки, т.е. получить целые числа, число разрядов заменяется значением 0. А если необходимо округлить числа до сотен или тысяч? Возможно, вы уже заметили, что округление работает с экспонентой 10, но в обращенном порядке. Чтобы округлить до тысячи, замените число разрядов на -3, как на рис. 7.3. Обратите внимание на то, как округляемое значение доходит до 1540, а затем опять снижается до 1500.

Еще одна функция, ОКРВВЕРХ (CEILING), в определенном смысле близка к функции ОКРУГЛ. Только в данном случае указывается не количество десятичных знаков, а число, до которого выполняется округление. Указанная функция производит округление всегда в большую сторону. В частности, ОКРВВЕРХ(67,15) возвращает 75. Вполне возможно, вас заинтересует и табличная функция ОКРВНИЗ (FLOOR) (информация о ней приведена в справочной системе Excel).




Рис. 7.3. Пример вычислений с округлением

Существует еще одна функция, о которой вы должны знать — функция ЦЕЛОЕ. Как и полагается целым числам, она отсекает десятичные знаки. ЦЕЛОЕ (2,9) возвращает 2. С отрицательными числами ситуация выглядит иначе. Они округляются до большего по модулю отрицательного числа. Примеры с работы этой функции представлены на листе 3 файла choi-07round.xls. Обращение знака в вычислениях (например, замена положительных значений отрицательными и наоборот) не всегда дает желаемый результат, что иллюстрирует следующий пример: ЦЕЛОЕ (2,9) возвращает 2, а ЦЕЛОЕ (-2,9) возвращает -3. Не упускайте из виду этот важный момент.


Контрольная работа №6 Встраиваемые функции EXCEL


И все же может оказаться, что генератор случайных чисел, послуживший поводом для этой дискуссии, по тем или иным причинам вам не походит. Вполне возможно, вы захотите сразу выделить целые числа, удалив десятичные знаки, округлить их и применить функцию слчис. Эти задачи может выполнить функция надстройки Excel — СЛУЧМЕЖДУ (RANDBETWEEN). Она принимает два входных значения — нижний и верхний пределы. Оба значения должны быть целыми числами. Функция СЛУЧМЕЖДУ возвращает целое число, случайным образом выбранное из указанного диапазона.

В данном случае термин надстройка (Add-In) противопоставляется стандартной функции рабочей таблицы, поскольку для работы с ней необходимо загрузить дополнительный модуль Пакет анализа (Analysis ToolPak) (рис. 7.4). Для загрузки дополнительных модулей выполните команду Сервис=>Надстройки (Tools1*Add-Ins). На рис. 7.5 показан пример использования функции СЛУЧМЕЖДУ.



Рис. 7.4. Установите флажки нужных дополнительных модулей


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

При работе над таблицей совместно с другими пользователями включите в нее СЛУЧМЕЖДУ или другие функции из надстроек, чтобы предупредить других пользователей о необходимости активизировать дополнительные модули. Даже если в своей копии Excel вы без проблем работаете с функцией СЛУЧМЕЖДУ, нет никакой гарантии, что вы не столкнетесь со сложностями позднее. Предположим, вы решили передать своему коллеге таблицу, в которую включена СЛУЧМЕЖДУ ИЛИ любая другая функция из надстраиваемых модулей. Вполне возможно, что на компьютере другого пользователя этот модуль не активизирован. Планируя совместную работу над таблицей, внимательно продумайте все моменты, связанные с применением надстроек.




Рис. 7.5. Применение функции СЛУЧМЕЖДУ (RANDBETWEEN)


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

ПРАКТИЧЕСКОЕ ПРИМЕНЕНИЕ ФУНКЦИИ СЛУЧМЕЖДУ

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

Предположим, вы проверяете сделки, проведенные по кредитной карте в декабре 2003. За этот период зарегистрировано 800 сделок. Из общего списка необходимо проверить 20 случайно выбранных записей. Из рис. 7.6. видно, что файл включает два основных раздела. Справа находится таблица со списком из 800 сделок, в который включена дата и сумма в долларах США.





Рис. 7.6. Таблица для сверки значений с балансовой стоимостью