автордың кітабын онлайн тегін оқу Excel 2007 для менеджеров и экономистов: логистические, производственные и оптимизационные расчеты
ББК 32.973.23-018
УДК 004.42
Т78
Трусов А. Ф.
Т78 Excel 2007 для менеджеров и экономистов: логистические, производственные
и оптимизационные расчеты (+CD). — СПб.: Питер, 2009. — 256 с.: ил.
ISBN 978-5-388-00527-4
Экономические и производственные расчеты, выбор оптимальных вариантов, принятие решений — с подобными проблемами часто сталкиваются работники различного уровня — от рядовых менеджеров до руководителей крупных компаний.
Эта книга является практическим руководством по использованию и разработке в среде Excel 2007 расчетных решений, предназначенных для автоматизации логистических, производственных, экономических расчетов, а также решения оптимизационных задач. Освоив материал книги, вы научитесь быстро решать сложные задачи транспортной логистики, эффективно управлять закупками и запасами, автоматизировать процесс составления документов, бланков и технических спецификаций, управлять инвестициями и ценными бумагами и многому другому. И все это — с помощью знакомого большинству офисных работников Excel!
На компакт-диске вы найдете файлы примеров, рассмотренных в книге, а также готовые шаблоны и надстройки Excel, которые можно использовать в своей работе. Кроме того, на диске размещен ряд полезных программ, расширяющих возможности Excel.
ББК 32.973.23-018
УДК 004.42
Все права защищены. Никакая часть данной книги не может быть воспроизведена в какой бы то ни было форме без письменного разрешения владельцев авторских прав.
дежные. Тем не менее, имея в виду возможные человеческие или технические ошибки, издательство не может Информация, содержащаяся в данной книге, получена из источников, рассматриваемых издательством как нагарантировать абсолютную точность и полноту приводимых сведений и не несет ответственности за возможные ошибки, связанные с использованием книги.
ISBN 978-5-388-00527-4 © ООО «Питер Пресс», 2009
Оглавление
Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Структура книги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 От издательства . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Контактные адреса . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Часть 1. Работа с Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Глава 1. Основы Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13
Интерфейс пользователя . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13 Книга, рабочий лист . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18 Ввод и изменение данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20 Выделение ячеек, строк, столбцов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21 Ссылки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21 Столбцы, строки, ячейки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24 Копирование, перемещение и вставка . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28 Очистка и удаление ячеек . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29 Форматирование данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29
Глава 2. Типовые операции в Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34
Операторы и операции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34
Автоматизация ввода данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35
Формулы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37
Функции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39
Использование функций . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42
Суммирование . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42
Поиск . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45
Логические функции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47
Просмотр формул . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47
Оформление таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48
Шаблоны . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51
Печать . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53
4 Оглавление
Защита элементов листа и книги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57 Сводные таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59 Построение диаграмм . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63 Внешние данные . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65 Надстройки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .67 Примеры использования надстроек . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69 Поиск решения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71
Диалоговое окно Поиск решения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .74
Диалоговое окно Параметры поиска решения . . . . . . . . . . . . . . . . . . . . . . . . .75
Диалоговое окно Результаты поиска решения . . . . . . . . . . . . . . . . . . . . . . . . .79
Диалоговое окно Текущее состояние поиска решения . . . . . . . . . . . . . . . . . .80
Отчеты надстройки Поиск решения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81 Программирование . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .86 Безопасность . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .88
Часть 2. Логистические расчеты . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .91
Глава 3. Снабженческая логистика . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .93
Закупки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .93
Метод миссий . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .93
Оптимизация закупок средствами Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95 Управление запасами . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97
ABC-анализ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97
XYZ-анализ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99
Совмещение АВС и XYZ-результатов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .100
Пример ABC и XYZ-анализа . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101
Программы ABC и XYZ-анализа . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .105 Оптимизация закупок и запасов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112 Рейтинг поставщиков . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117
Глава 4. Транспортная логистика . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .120
Решение транспортной задачи . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .121
Сбалансированная транспортная задача . . . . . . . . . . . . . . . . . . . . . . . . . . . . .121
Транспортная задача с дефицитом . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .124
Транспортная задача с избытком . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .127
Транспортная задача с несколькими перевозчиками . . . . . . . . . . . . . . . . . . .129
Доли перевозчиков в суммарной стоимости перевозок . . . . . . . . . . . . . . . .131
Доли перевозчиков в общем количестве перевозок . . . . . . . . . . . . . . . . . . . .138
Задача о рюкзаке . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142
Решение задачи в классической постановке . . . . . . . . . . . . . . . . . . . . . . . . . .142
Модифицированная задача . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .143
Оглавление 5
Глава 5. Складская логистика . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .145
Управление потоками на складах . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .145
Расчет величины суммарного материального потока на складе . . . . . . . . .147
Расчет стоимости переработки грузов на складе . . . . . . . . . . . . . . . . . . . . . .150 Расчет точки безубыточности деятельности склада . . . . . . . . . . . . . . . . . . . .151 Размещение товаров на складе . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .152 Выбор складов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .152
Часть 3. Производственно-технологические расчеты . . . . . .155
Глава 6. Производственные расчеты . . . . . . . . . . . . . . . . . . . . . . . . . . . . .157
Строительные расчеты . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .157
Расчет фундаментов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .158
Расчет звукоизоляции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .160
Расчеты каменных конструкций . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .161
Расчет подвала . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .163
Расчет эквивалентной нагрузки на плиты . . . . . . . . . . . . . . . . . . . . . . . . . . . .166
Расчет арок и стропил . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .167
Геометрические расчеты . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .170
Задача раскроя . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .176
Раскрой прутьев . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .176
Раскрой листа . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .178
Составление смеси . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184
Классическая постановка задачи . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184
Решение задачи при дополнительных ограничениях . . . . . . . . . . . . . . . . . .186 Планирование производства изделий . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188
Производство изделий различных видов . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188
Производство изделий различных видовс учетом расхода сырья . . . . . . .190
Глава 7. Теплотехнические расчеты . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .192
Программа «Теплотехник» . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .192
Калькулятор «Теплотехнический расчет» . . . . . . . . . . . . . . . . . . . . . . . . . . . . .199
Глава 8. Бланки, документы,технические спецификации . . . . . .203
Типовые бланки и отдельныерасчеты . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .203
Автоматизация составления документов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .204
Унифицированные формы для расчетов с покупателями . . . . . . . . . . . . . .204
Счет-фактура и счет . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .204
Microsoft Office Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .208
Система складского учета Doors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .209
Технические спецификации . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215
6 Оглавление
Часть 4. Оптимизационные расчеты . . . . . . . . . . . . . . . . . . . . . . . . . . .223
Глава 9. Финансовые расчеты . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .225
Управление капиталом . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .225
Управление ценными бумагами . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .228
Модель Шарпа . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .228
Максимум скорости оборота ценных бумаг . . . . . . . . . . . . . . . . . . . . . . . . . .229
Минимум риска ценных бумаг . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .230
Управление инвестициями . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .231
Глава 10. Задача о назначениях . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .238
Сбалансированная задача о назначениях . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .238 Несбалансированная задача о назначениях с избытком предложений . .242 Несбалансированная задача о назначениях с избытком спроса . . . . . . . . .244
Приложение. Функции Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .247
Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .255
Введение
Экономические и производственные расчеты, выбор оптимальных вариантов, принятие решений — с подобными проблемами часто сталкиваются работники различного уровня: от рядовых менеджеров до руководителей крупных компаний. К сожалению, в отечественной практике существует очень мало примеров широкого применения компьютерной техники, программ и алгоритмов автоматизации области деятельности, например, сложности формализации процессов и по-этого процесса. Существуют объективные причины слабой автоматизации этой становки задач в достаточно универсальной формулировке. В такой ситуации создание программ для широкой аудитории пользователей становится затруднительным, и чаще всего программы создаются по специальным заказам в расчете может позволить себе не каждая организация, так как стоимость подобной раз-на применение в конкретной организации. Однако подобное решение проблемы работки может быть весьма значительной и для нее требуется очень квалифицированная постановка задачи и тщательный выбор алгоритмов решения.
Для индивидуального предпринимателя или небольшой фирмы хорошей альтернативой специализированным программам может оказаться использование о том, что в электронных таблицах Excel есть все необходимые средства и воз-стандартных офисных программных средств. Далеко не все задумываются
можности для выполнения экономических, производственных, оптимизационных расчетов. Некоторые возможности, например построение диаграмм и сводных таблиц, являются хорошим дополнением вычислительных средств. В графическом виде информация воспринимается значительно легче, поэтому применение этих средств может существенно облегчить анализ результатов работы организации. Некоторым пользователям могут пригодиться разнообразные встроенные функции для финансовых вычислений, инженерных и статистических расчетов.
С помощью Excel легко может быть создано средство решения конкретной задачи для конкретной организации. Именно индивидуальный подход к решению достаточно сложных задач и наличие надежных алгоритмов позволяют получить решение быстро и эффективно.
Применение Excel имеет и другое важное преимущество: пользователь может при необходимости самостоятельно создавать или применять стандартные формы выходных документов. Кроме того, в Excel имеются и мощные инструменты для программирования. Конечно, далеко не каждый пользователь решится взяться как существуют разработки профессиональных программистов. Многие из них за разработку программ в Excel, но в этом и нет большой необходимости, так вить набор собственных средств для выполнения необходимых расчетов. Такой являются бесплатными и регулярно обновляются. С помощью Excel можно состаинструментарий может использоваться как в виде временного средства, так и на длительный срок.
8 Введение
Структура книга
Книга предназначена для различных категорий пользователей, в том числе и для тех, кто мало знаком с электронными таблицами Excel. Для этих пользователей
предназначена гл. 1. В ней приводятся основные правила и описание работы в Excel.
Пользователи, уже знакомые с Excel, могут уделить больше внимания гл. 2. Некоторые из описанных в ней действий могут быть им знакомы, но, вероятно, найдутся и такие операции, которые ими еще не неприменялись. Им будет полезно, например, познакомиться с построением диаграмм и сводных таблиц. Особое внимание можно уделить изучению работы с инструментами для проведения анализа и поиска оптимального решения. Эти средства являются основными описаны наиболее подробно.при решении приводимых в книге примеров задач оптимизации, поэтому они В гл. 3–5 рассматривается применение Excel к решению логистических задач. В них
приводятся решения на основе специализированных расчетных методик, а также примеры применения универсальных средств оптимизации.
Гл. 6–8 посвящены описанию применения Excel для решения различных производственных задач и выполнения расчетов. Приводятся примеры программ строитель-ных и теплотехнических расчетов, заполнения различных бланков и документов, подготовки технических спецификаций.
В гл. 9 и 10 описывается применения Excel для решения некоторых других оптимизационных задач (финансовые задачи и задача о назначениях).
вующих версий на момент написания книги. Эта версия Excel отличается от всех Применение Excel показано на примере версии Excel 2007 – последней из существнутренние отличия. По мере необходимости в тексте даются пояснения, касаю-предыдущих совершенно иным интерфейсом, кроме того, имеются и некоторые щиеся наиболее заметных отличий от других версий.
В книге приводится большое количество примеров выполнения различных операв различных вариантах. На конкретных примерах показаны возможные проблемы, ций и решения типичных задач. Многие примеры прикладных задач приводятся возникающие при поиске оптимального решения. На прилагаемом компакт-диске находятся файлы примеров по всем рассматриваемым темам.
Все примеры и программы могут успешно использоваться не только в Excel 2007, но и в более ранних версиях.
и других интерфейсных элементов будут использоваться различные шрифты: Для выделения различных терминов, названий пунктов меню, кнопок, команд термин, пункт меню, кнопка, команда (адрес ячейки, функция, арифметическое выражение Excel) .
От издательства 9
От издательства
Ваши замечания, предложения и вопросы отправляйте по адресу электронной почты
comp@piter.com (издательство «Питер», компьютерная редакция).
Мы будем рады узнать ваше мнение!
http://www.piter.com.Подробную информацию о наших книгах вы найдете на веб-сайте издательства
Часть 1
Работа с Excel
Программа Microsoft Excel является мощным и универсальным средством выполнения различных вычислений. Она относится к катесовершенствования в Excel накопился не только богатый набор вы-гории так называемых электронных таблиц. За годы непрерывного отображения и анализа результатов расчетов. Умение эффективно числительных средств, но и многочисленные средства наглядного применять подобные средства позволит пользователям быстро решать
возникающие задачи.
Глава 1
Основы Excel
Программа Microsoft Excel 2007 входит в комплект офисных программ Microsoft Office 2007. От предыдущих версий ее отличают повышенная вычислительная мощность и функциональность, а также новый интерфейс.
Интерфейс пользователя
Все пользователи персональных компьютеров привыкли, что сложные программы, имеющие множество возможностей для настройки работы и управления, имеют специальное меню, называемое главным, в котором содержатся все команды управ-ления и настройки параметров. В новой версии Microsoft Office 2007 применен новый подход. Главное меню здесь отсутствует. Его заменила так называемая лента. работы, облегчить поиск нужных средств решения различных задач, раскрыть Цель разработки нового интерфейса заключалась в том, чтобы повысить скорость новые функциональные возможности приложений и повысить эффективность их
16 Глава 1. Основы Excel
Рис. 1.6. Вкладка Данные
Рис. 1.7. Вкладка Рецензирование
Последняя из стандартных вкладок — Вид (рис. 1.8) содержит средства настройки
режима просмотра и масштабирования.
Рис. 1.8. Вкладка Вид
При необходимости может быть включен (Office ? Параметры Excel ? Основные) показ вкладки Разработчик (рис. 1.9). На ней находятся инструменты, которые чаще применяются программистами, чем обычными пользователям. Однако некоторые будут рассматриваться в данной книге.операции с обращением к этой вкладке достаточно распространены, поэтому они
Рис. 1.9. Вкладка Разработчик
Большинство кнопок выполняют только одно действие и имеют обычное изображение, но за некоторыми закреплено несколько вариантов действий. Такие кнопки имеют меню (дополнительный раскрывающийся список задач); их изображение дополнено в нижней части маленьким треугольником, расположенным рядом
Интерфейс пользователя 17
