Программные средства решения экономических задач

 

 

 

Введение

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

Excel предлагает широкий  набор функциональных средств  по обработке табличных данных:

-      создание и редактирование электронных таблиц с применением богатого набора встроенных функций;

-      оформление и печать электронных таблиц;

-      построение диаграмм и графиков разной степени наглядности и детализации;

-      работа с электронными таблицами как с базами данных: фильтрация, сортировка, создание итоговых и сводных таблиц, консолидация данных из разных таблиц и т.д.;

-      решение экономических задач путем подбора параметров;

-      решение оптимизационных задач;

-      численное решение разнообразных математических задач;

-      статистическая обработка данных;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Любая формула в Excel начинается со знака «=» и может включать константы,  знаки  операторов,   адреса  ячеек  и  функции.   Например:

=2+А2/SIN(B2).   Операторы   делятся   на   арифметические,   текстовые,

операторы отношений.

Арифметические операторы (в порядке приоритета):

 

- - унарный минус (умножение на -1);

% - процент

^ - возведение в степень;

*,/ - умножение, деление;

+, - - сложение, вычитание.

Приоритет изменяется с помощью круглых скобок ( )

Если в ячейку введено число со знаком %, то фактическое его значение будет в 100 раз меньше. Например: в ячейку введено 5%; Excel запомнит число 0,05.

Текстовый оператор: &. Используется для сцепление двух и более текстовых строк в одну. Например: = ―Петр‖& ― Кузнецов‖; в результате в

ячейке будет фраза «Петр Кузнецов».

Операторы отношения:

= - равно;

<> - не равно;

< -меньше;

> - больше;

<= - меньше или равно;

>= - больше или равно.

Данная группа операторов может использоваться как с числовыми, так и с текстовыми данными. Результатом использования операторов отношения в формулах будут слово ИСТИНА, если результат сравнения истинный и ЛОЖЬ, если результат сравнения ложный. 

                                           Адресация ячеек

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

Относительная адресация. Относительный адрес ячейки – это такой адрес, который основывается только на текущее местоположение ячейки, т.е.

при копировании он будет изменяться.

Абсолютная адресация. Абсолютный адрес ячейки – это адрес, который не изменяется при копировании. При этом при имени столбца и

номере строки стоит знак $ (доллар).

Смешанная адресация содержит как абсолютный, так и относительный адрес, т.е. знак доллара может стоять либо перед именем столбца, либо перед номером строки.

Для более удобного изменения адреса ячейки (в режиме ввода и редактирования формул) используется функциональная клавиша F4. Последовательное нажатие этой клавиши влечет поочередный ввод знака $ перед элементами адреса. Так для адреса В5 получим цепочку преобразований: В5 à $B$5 àB$5 à $B5 àB5. Т.е. для получения желаемого вида ссылки следует последовательно нажимать клавишу F4.

Ввод формул

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

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

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

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

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

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

Список всех функций можно найти в окне Мастера функций,

 

^{воспользовавшись командой   из вкладки «Формулы» или щелкнуть} левой кнопкой мыши в строке формул по значку

 

 

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

В общем виде любая функция может быть записана в виде:

=<имя_функции>(аргументы)

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

Существуют следующие правила ввода функций:

1. Если формула начинается с функции, то вызывается Мастер функций. Если функция вводится вручную, то вводится знак «=», далее Имя функции.
2. Аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов.
3. Между именем функции и знаком « ( » пробел не ставится.
4. Вводить имя функции и ее аргументов рекомендуется строчными буквами. Если ввод осуществлен правильно, Excel сам преобразует строчные буквы в прописные.

Для проверки правильности работы формулы во вкладке «Формулы» в

^{группе команд «Зависимости формул» щелкнуть по кнопке   «Вычислить} формулу» (предварительно ячейка с формулой должна быть выделена). В открывшемся диалоговом окне, щелкая по кнопке  вычислить,  можно отследить весь процесс вычисления формулы по шагам.

Пример 6: Пусть ячейка С10 содержит формулу для расчета текущего бюджета. Если результат вычислений по формуле в ячейке A10 меньше или равен 100, то в ячейке С10 отображается сообщение "В пределах бюджета", в противном случае отображается сообщение "Превышение бюджета".

Решение: Решить эту задачу можно с помощью функции ЕСЛИ( ).

=ЕСЛИ(A10<=100;"В пределах бюджета";"Превышение бюджета")

Пример 7: Предположим, что в таблице Excel ведется учет баллов студентов по балльно-рейтинговой системе. В итоговом столбце необходимо выводить итоговые результаты, в зависимости от набранной студентом за семестр суммы баллов. Отлично – 85 – 100 баллов, Хорошо – 65 - 84, Удовлетворительно – 51 – 64, Плохо – 50 и менее.

 

 

Решение: Эту задачу также как и предыдущую можно решить с помощью функции ЕСЛИ( ). При этом понадобиться использовать вложенные функции ЕСЛИ ( ). Итак, в качестве проверяемого условия будет выступать Сумма баллов. Тогда итоговая формула будет выглядеть следующим образом:

=ЕСЛИ(Сумма_баллов<=50;‖Плохо‖;ЕСЛИ(Сумма_баллов<65;

‖Удовлетворительно‖;   ЕСЛИ(Сумма_баллов<85;‖Хорошо‖;‖Отлично‖)

Пример 8. Дан список названий месяцев. Определить, какой по счету в данном списке месяц «мар»?

 

 

 

 

 

 

 

Для определения порядкового номера «мар» воспользуемся функцией ПОИСКПОЗ( ).

ПОИСКПОЗ( ) возвращает относительную позицию в массиве элемента,

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

В нашем случае формула будет имеет вид: =ПОИСКПОЗ("мар";A2:E2;0), где "мар" – искомое_значение (что ищем);

A2:E2 – Просматриваемый_массив (диапазон ячеек для поиска (обязательно либо строка, либо столбец));

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

Пример 9. В исходной таблице, найти число, расположенное под цифрой 5 в третьей строке таблицы.

Для решения этой задачи воспользуемся функцией ГПР( ).

Функция ГПР( ) предназначена для поиска исходного значения (в нашем примере цифра 5) в самой верхней строке таблицы (диапазон B7:F9) и изъятие значения из ячейки, находящейся в строке с заданным номером того же столбца, что и исходное значение (в нашем примере строка 3).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

0 - интервальный просмотр, указывающий что поиск в первой строке должен производится точно

Ответом в данном примере будет число 45

 

Пример  10. В исходном диапазоне найти символ, стоящий в одной строке с цифрой 3.

Для решения данного примера воспользуемся функцией ВПР( ).

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

 

 

 

 

 

 

 

 

В результате получим значение «а»

Функции ГПР( ) и ВПР( ) являются аналогами многократного использования в формуле функции ЕСЛИ( )

Пример 11. Найти значение, находящееся на пересечении 2-ой строки и 4-ого столбца.

 

 

 

 

 

Для решения данной задачи воспользуемся функцией ИНДЕКС().

Функция ИНДЕКС( ) осуществляет поиск ячейки в заданном диапазоне на пересечении строки и столбца с заданными номерами.

В результате применения в нашем примере функции ИНДЕКС( ) получим число 40.

Практическая  часть

1. Клиентская база «DobroPogalovat»

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

Рис.1: Клиентская база «DobroPogalovat»

1. В эту базу мы заносим следующие данные заказа: Тип продукции (например: поп -печать по оттиску на полимере; пнп-первичная печать на полимере; в4+0- визитки полноцветные односторонние; в4+4 – визитки полноцветные двусторонние; штнп- штамп первичный на полимере, факсим. – факсимиле и тд.)
2. Оснастка  ( для печати штампа и факсимиле (автоматическая или обычная)  или вид бумаги для визиток, листовок ( обычная или название дизайнерской)
3. Название заказанного вида продукции ( Например: прислали оттиск печати под названием «Ромашка», сохраняем в обшей папке  под  названием «Ромашка», и копируем название вместе с расширением файла «Ромашка.jpg», это название вставляем  в графу название ( 5 столбец)
4. В столбце 6  мы пишем кол-во заказанной продукции.
5. В столбце 7 мы записываем контакты заказчика ( контактный телефон, адрес доставки, электронную почту).
6. В столбце 8 пишутся небольшие примечания, например если печать заказана по срочному тарифу, мы пишем «СР», или помечаем до какого времени важно изготовить или доставить данный заказ.
7. В столбце 9 мы пишем цену заказа.
8. В столбце 10 пишется информация по доставке, если клиент сам забирает заказ мы пишем «сами», если требуется доставка мы пишем «о».  

Когда заказ забирают курьеры, мы на место   «о» ставим имя  курьера, а когда курьеры привозят деньги или клиент забирает заказ  и отдает за него деньги, на место  «имя курьера» или «сами», мы ставим число, которым пришли деньги, например «30,05».

9. В столбце 11 пишем на какой стадии находится в данный момент продукция. (варить- печать нарисована и изготавливается,  на подтв. – макет выслан клиенту на подтверждение, и т.д.)

 

Для  того что бы сделать  распечатку курьерам, я пользуюсь  фильтром на столбцах            10  и 11, выделяя  «о» (отвезти заказ)  и«+» (что обозначает готовность заказа). ( рис. 2) Рис.  2:   Таблица для распечатки курьерам

Так же я использую фильтр для  написания отчета за день. Ставлю фильтр на столбец  10, выделяя число  «30,05». Потом выделяю столбец 9 и  смотрю сколько денег я получила за день за заказы. ( рис. 3)

 

Рис. 3: подсчет денег за день

 

 

 

 

 

 

 

 

 

 

 

2. Калькулятор