Table Of ContentББК 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 2007 – последней из сущест-
вующих версий на момент написания книги. Эта версия Excel отличается от всех
предыдущих совершенно иным интерфейсом, кроме того, имеются и некоторые
внутренние отличия. По мере необходимости в тексте даются пояснения, касаю-
щиеся наиболее заметных отличий от других версий.
В книге приводится большое количество примеров выполнения различных опера-
ций и решения типичных задач. Многие примеры прикладных задач приводятся
в различных вариантах. На конкретных примерах показаны возможные проблемы,
возникающие при поиске оптимального решения. На прилагаемом компакт-диске
находятся файлы примеров по всем рассматриваемым темам.
Все примеры и программы могут успешно использоваться не только в Excel 2007,
но и в более ранних версиях.
Для выделения различных терминов, названий пунктов меню, кнопок, команд
и других интерфейсных элементов будут использоваться различные шрифты:
термин, пункт меню, кнопка, команда (адрес ячейки, функция, арифмети-
ческое выражение Excel).
От издательства 9
От издательства
Ваши замечания, предложения и вопросы отправляйте по адресу электронной почты
[email protected] (издательство «Питер», компьютерная редакция).
Мы будем рады узнать ваше мнение!
Подробную информацию о наших книгах вы найдете на веб-сайте издательства
http://www.piter.com.
