Excel в примерах и задачах – ()

300 примеров по Excel — Microsoft Excel для начинающих

Те, кто работает в продажах, в маркетинге или в любом другом направлении, которое использует

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

Уроки MS Excel

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

Уроки MS Excel

Если в Excel необходимо отобразить только записи, удовлетворяющие определённому критерию, то используйте фильтр. Для

Уроки MS Excel

В Excel можно сортировать данные по одному или нескольким столбцам. Сортировка может быть выполнена

Уроки MS Excel

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

office-guru.ru

MS EXCEL в расчетных задачах

2

Косовцева Татьяна Реональдовна, Петров Вадим Юрьевич. MS EXCEL в расчетных задачах. Учебное пособие. – СПб: СПГУ ИТМО, 2010. – 82 с.

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

Для студентов специальностей – 080801 «Прикладная информатика в экономике», 080100 Экономика»

Рекомендовано к печати на заседании ученого совета Гуманитарного факультета, протокол № 8 от23 февраля 2010 г.

В 2009 году Университет стал победителем многоэтапного конкурса, в результате которого определены 12 ведущих университетов России, которым присвоена категория «Национальный исследовательский университет». Министерством образования и науки Российской Федерации была утверждена Программа развития государственного образовательного учреждения высшего профессионального образования «СанктПетербургский государственный университет информационных технологий, механики и оптики» на 2009–2018годы.

©Санкт-Петербургскийгосударственный университет информационных технологий, механики и оптики, 2010©Т.Р. Косовцева, В.Ю.Петров, 2010

 

3

 

ОГЛАВЛЕНИЕ

ОБЩИЕ СВЕДЕНИЯ

…………………………………………………………………………………. 5

1.ОСНОВНЫЕ ОПЕРАЦИИ НА РАБОЧЕМ ЛИСТЕ MS EXCEL.

СОЗДАНИЕ И ФОРМАТИРОВАНИЕ ПРОСТОЙ ТАБЛИЦЫ

……………………. 6

1.1. СТРУКТУРА ОКНА ПРИЛОЖЕНИЯ MS EXCEL …………………………..

6

1.2. ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ………………………………………

8

1.3. ФОРМАТЫ ЧИСЛОВЫХ ДАННЫХ …………………………………………….

11

1.4. ПРАКТИЧЕСКАЯ РАБОТА………………………………………………………….

12

1.4.1. ПРИМЕРЫ СОЗДАНИЯ ТАБЛИЦЫ …………………………………….

12

1.4.2. ВАРИАНТЫ ЗАДАНИЙ………………………………………………………

15

2.ВЫЧИСЛЕНИЯ ПО ФОРМУЛАМ С ИСПОЛЬЗОВАНИЕМ

ВСТРОЕННЫХ МАТЕМАТИЧЕСКИХ ФУНКЦИЙ MS EXCEL………………

19

2.1. ОСНОВНЫЕ ПОНЯТИЯ……………………………………………………………….

19

2.2. ВСТРОЕННЫЕ ФУНКЦИИ MS EXCEL………………………………………..

19

2.3. ОПЕРАЦИИ

………………………………………………………………………………….

20

2.4. ПРАКТИЧЕСКАЯ ………………………………………………………….РАБОТА

22

2.4.1. ПРИМЕРЫ…………………………………………………………………………..

22

2.4.2. ВАРИАНТЫ ……………………………………………………….ЗАДАНИЙ

26

3. ЛОГИЧЕСКИЕ ФУНКЦИИ ………………………………………………..MS EXCEL

28

3.1. ОСНОВНЫЕ ……………………………………………………………….ПОНЯТИЯ

28

3.2. ЛОГИЧЕСКИЕ ……………………………ФУНКЦИИ ЕСЛИ, И, ИЛИ, НЕ

29

3.2. ПРАКТИЧЕСКАЯ ………………………………………………………….РАБОТА

30

3.2.1. ПРИМЕРЫ……………………………………………………………………………

30

3.2.2. ВАРИАНТЫ ……………………………………………………….ЗАДАНИЙ

33

4. ПОСТРОЕНИЕ ДИАГРАММ ………….И ТАБУЛИРОВАНИЕ ФУНКЦИЙ

35

4.1. ОСНОВНЫЕ ……………………………………………………………….ПОНЯТИЯ

35

4.2. ПРИМЕРЫ ПОСТРОЕНИЯ ……………………………………..ДИАГРАММ

36

4.4. ВАРИАНТЫ ……………………………………………………………….ЗАДАНИЙ

43

5.РЕШЕНИЕ ЗАДАЧ С ИСПОЛЬЗОВАНИЕМ ФУНКЦИЙ ВПР,

СУММЕСЛИ, СЧЕТЕСЛИ………………………………………………………………………..

44

5.1. ФУНКЦИЯ ВПР……………………………………………………………………………

44

5.2. ФУНКЦИЯ …………………………………………………………..

СУММЕСЛИ

47

5.3. ФУНКЦИЯ …………………………………………………………….

СЧЁТЕСЛИ

47

5.4. ПРИМЕР. СОЗДАНИЕ ВЕДОМОСТИ ДЛЯ РАСЧЕТА ЗАРАБОТКА

ПРИ ПОЧАСОВОЙ …………………………………………………………………..ОПЛАТЕ

48

5.4.1. ПОСТАНОВКА ……………………………………………………..ЗАДАЧИ

48

5.4.2. РЕШЕНИЕ……………………………………………………………………………

50

5.4.3. РЕЗУЛЬТАТ…………………………………………………………………………

52

5.5. ВАРИАНТЫ ……………………………………………………………….ЗАДАНИЙ

53

6. ОБРАБОТКА СПИСКОВ………………………………………………………………………

63

6.1. ОСНОВНЫЕ ……………………………………………………………….ПОНЯТИЯ

63

6.2.ФОРМИРОВАНИЕ ………………………………………………………..СПИСКА

63

6.3. СОРТИРОВКА ……………………………………………………………СПИСКОВ

64

6.4. АНАЛИЗ СПИСКА …………………………..С ПОМОЩЬЮ ФИЛЬТРОВ

65

6.4.1. КОМАНДА ………………………………………………….АВТОФИЛЬТР

65

4

 

6.4.2. ПОИСК ТЕКСТОВЫХ ЗНАЧЕНИЙ……………………………………..

67

6.3.3. УДАЛЕНИЕ АВТОФИЛЬТРОВ ……………………………………………

68

64.4. КОМАНДА РАСШИРЕННЫЙ ФИЛЬТР………………………………..

68

6.4.5. ИНТЕРВАЛ КРИТЕРИЕВ ……………………………………………………

69

6.4.6. ТЕКСТОВЫЕ КРИТЕРИИ…………………………………………………….

71

6.4.7. ВЫЧИСЛЯЕМЫЕ КРИТЕРИИ……………………………………………..

71

6.3.8. ССЫЛКИ НА ЯЧЕЙКИ ВНЕ СПИСКА…………………………………

72

6.4.9. ССЫЛКИ НА ЯЧЕЙКИ ВНУТРИ СПИСКА………………………….

73

6.4.10. ПОДВЕДЕНИЕ ИТОГОВ ……………………………………………………

74

6.5. ВАРИАНТЫ ЗАДАНИЙ……………………………………………………………….

75

СПИСОК ЛИТЕРАТУРЫ………………………………………………………………………….

79

5

ОБЩИЕ СВЕДЕНИЯ

Табличный процессор Microsoft Excel – ведущая программа, обработки электронных таблиц. Первая версия MS Excel появилась в 1985 году и обеспечивала только простые арифметические операции в строку или в столбец. В 1993 году вышла пятая версия MS Excel, ставшая составной частью интегрированного пакета Microsoft Office и обладает структурой управления, аналогичной другим программам MS Office.

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

(Visual Basic for Application (VBA). С помощью VBA можно автоматизировать всю работу, начиная со сбора информации, её обработки до создания итоговой документации, как для офисного пользователя, так и для размещения на Web-узле.

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

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

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

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

Вданном пособие используется версия MS Excel 2007.

6

1. ОСНОВНЫЕ ОПЕРАЦИИ НА РАБОЧЕМ ЛИСТЕ MS EXCEL. СОЗДАНИЕ И ФОРМАТИРОВАНИЕ ПРОСТОЙ ТАБЛИЦЫ

1.1. СТРУКТУРА ОКНА ПРИЛОЖЕНИЯ MS EXCEL

Файлы, создаваемые с помощью MS Excel, называются рабочими книгами Еxcel и имеют по умолчанию расширениеxls. Имя файла может быть любым, разрешенным в операционной системе Windows.

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

Рабочее поле листа – это электронная таблица, состоящая изстолбцов истрок (рис.1.1). Названия столбцов – буква или две буквы латинского алфавита. Названия строк – цифры. Размер рабочего листа определяется системой по умолчанию. MS Excel 2007 поддерживает листы размером до одного миллиона строк и16-титысяч столбцов. Так сетка Office Excel 2007 состоит из 1048576 строк и 16384 столбцов.

Рис.1.1. Интерфейс стандартной рабочей книги с листами

7

Пересечение конкретного столбца и строки образует ячейку. Местоположение ячейки задается адресом, образованным из имени столбца и номера строки, на пересечении которых находится эта ячейка. Адрес ячейки в электронной таблице обычно называютссылкой. Например, на рисунке 1.1. указана ячейка с адресом (ссылкой) B5.

Ссылка типа A1 называется относительной. Кроме нее возможнаабсолютная ссылка на ячейку — $A$1, а такжесмешанные ссылки $A1 и A$1. Прямоугольная часть таблицы, состоящая из смежных ячеек, называетсяобластью, илиинтервалом ячеек. Интервал однозначно определяется своей первой и последней ячейками и записывается с указанием этих ячеек, разделенных двоеточием, например, B2:D5 или C10:C15. Для областей применяются также термины: блок, диапазон, массив.

При использовании ячеек одного рабочего листа на другом листе, ячейки указываются с именем листа. Например, ячейку Лист1!С5 можно использовать на других листах. Можно использовать и ячейки другой рабочей книги.

ПРИМЕР: ЗАПИСЬ ‘[ОТЧЕТ.XLS]ЛИСТ2’!C10 ОЗНАЧАЕТ ЯЧЕЙКУ C10 НА РАБОЧЕМ ЛИСТЕ С ИМЕНЕМ ЛИСТ2 РАБОЧЕЙ КНИГИ ОТЧЕТ.XLS.

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

Маркер

заполнения

.

Рис.1.2. Окно рабочей книги. Стрелка указывает на Маркер заполнения

Для просмотра электронной таблицы используются линейки прокрутки.

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

Ярлычки листов рабочей книги служат для перехода от одного рабочего листа к другому.

8

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

На вкладке Формулы указывается стиль ссылок (адресов) по умолчанию:A1 (адрес клетки- имя столбца, номер строки) илиR1C1 (адрес клетки — номер строки и номер столбца).

Рис. 1.3. Диалоговое окно Параметры Excel

1.2. ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ

Одна из ячеек рабочего листа является текущей, иливыделенной, на рис.1.1 она обведена жирной рамкой. Адрес текущей ячейки при этом указывается в поле имени (области ссылок) — области в левой части строки формул. Для того чтобы выделить другую ячейку, нужно щелкнуть по ней после наведения указателя мыши в виде “белого креста”. Ввод данных с клавиатуры осуществляется в текущую ячейку. Содержимое текущей ячейки отображается в строке формул. В ячейки рабочего листа можно вводить два вида данных: постоянные значения и формулы

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

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

9

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

Формула — это некоторая последовательность постоянных значений, ссылок на ячейки, имен, функций или знаков операций, которая задает правило для вычисления нового значения по данным. Формулы всегда начинаются со знака равенства (=). После ввода формулы MS Excel автоматически выводит в ячейку результат вычислений. Он может меняться, если меняются значения в ячейках, входящих в формулу.

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

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

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

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

Экспоненциальный.

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

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

Чтобы ввести текст, нужно выделить ячейку и набрать текст. Ячейка может вмещать до 255 символов. Если требуется ввести произвольный набор символов как текст (в том числе числа и даты) следует заранее установить для данной ячейки формат Текстовый или набрать перед числом знак апострофа (‘). Текст по умолчанию выравнивается по левому краю.

Если выделить ячейку и заново ввести данные, то ранее введенные данные стираются.

10

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

В MS Excel 2007 можно выделить ячейки определенного типа, например, с числовыми данными, формулами или те, к которым применено условное форматирование. Для этого нужно нажать кнопку Найти и выделить в группеФорматирование на вкладкеГлавная

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

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

MS Excel допускает выделение несмежных областей. Это достигается выделением каждой области при нажатой клавише Ctrl. Перед выполнением данных операций нужно выделить требуемую ячейку или область.

Укажем два наиболее важных способа очистки содержимого ячеек:

клавиша Delete удаляет содержимое ячейки;

кнопка |Очистить(группа Редактирование, вкладка Главная)

предлагает выбрать вариант очистки из пунктов Все,Форматы,

Содержимое, Примечание.

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

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

Текущий рабочий лист можно переименовать, переместить, копировать, удалить. Соответствующие команды выполняются по командной кнопке Формат (группаЯчейки, вкладкаГлавная) или через контекстное меню рабочего листа. Удаленные рабочие листы не восстанавливаются с помощью команды отмены.

11

1.3. ФОРМАТЫ ЧИСЛОВЫХ ДАННЫХ

Если числовые данные имеют специальные единицы измерения – денежные, проценты, даты, время, то нужно использовать соответствующие специальные форматы.

Формат содержимого выделенной ячейки можно установить с помощью командных кнопок на вкладке Главная.

Группа кнопок Шрифт позволяет выбрать требуемый формат из списка форматов и установить его параметры.

Группа кнопок Числовой (рис.1.4) позволяет установить требуемое количество значащих цифр в десятичной записи числа.

Рис. 1.4. Задание числового формата

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

Формат Экспоненциальный позволяет записывать числа с десятичным порядком. Например: число 0,000873 может быть записано как0,873E-3или8,73E-04.Число 1000000 можно задать как 1E+06 или 1E6 (знак “плюс”опущен). При этом буква E – латинская.

Существуют форматы Денежный, Финансовыйи Процентный.

Формат Дата предлагает большое число вариантов ввода дат, например, 01.04.10 или 1 Апрель, 2010 или 31 дек 10. Еще один вариант формата даты содержит дробную черту, например, 1/6 MS Excel интерпретирует как 1 июня. Чаще с дробной чертой используется форма 01/06/10.

Формат Время чаще всего использует двоеточие как разделитель (3:20:25 или 12:05). Если нужно выводить время дня в12-тичасовом

studfiles.net

Мир MS Excel — Сборники.

 
Книга: Excel, сборник 1
Автор: Коллектив
Страниц: 2212
Формат: DJVU
Размер: 41 Мб
Качество: Нормальное
Язык: Русский


     Сборник из 6 книг в формате djvu: «Excel. Единый справочник» Шитов В. Н.; «Excel. Трюки» Холи Р., Холи Д.; «Использование макросов в Excel» Роман С.; «Научные вычисления в Microsoft Excel» Васильев А.Н.; «Планирование на малых и средних предприятиях средствами Excel» Плакунов М. К.; «Решение математических задач средствами Excel» Гельман В. Я.

 
Книга: Excel, сборник 2
Автор: Коллектив
Страниц: 2092
Формат: DJVU
Размер: 42,4 Мб
Качество: Нормальное
Язык: Русский


    Сборник из 6 книг в формате djvu: «1700 заданий по Microsoft Excel» Златопольский Д. М.; «Excel для бухгалтера в примерах» Коцюбинский А.О., Грошев С.В.; «Excel: Сборник примеров и задач» Лавренов С. М.; «Использование MS Excel и VBA в экономике и финансах» Гарнаев А. Ю.; «Лучшие методики применения Excel в бизнесе» Абдулазар Л.; «Разработка управленческого решения средствами пакета Excel» Степанов А. Г.

 
Книга: Excel. Сборник примеров и задач
Автор: Лавренов С. М.
Издательство: Финансы и Статистика
Страниц: 336
Формат: PDF
Размер: 21 Mb
Качество: Отличное
Язык: Русский
Год издания: 2003 


     Представленные в книге примеры, упражнения и задачи предназначены для углубленного изучения возможностей процессора электронных таблиц Excel (в основном версии Excel 97, Excel 2000, но большинство примеров могут выполняться в среде Excel 5.0/7.0). Задачи разнообразны по тематике и уровню трудности. Особое внимание уделено методам адресации, построению табличных формул (формул массива), работе с электронными таблицами как с базами данных.
     Для широкого круга читателей: от начинающих до опытных пользователей. Большая часть материала доступна учащимся старших классов и студентам младших курсов экономических и технических вузов. Книгу можно использовать для самообразования и для проведения занятий с преподавателем.


www.excelworld.ru

Решение производственной задачи в Excel

20.08.2015 Григорий Цапко Бизнес-планирование, Бюджетирование, Калькуляторы, шаблоны, форматы, Малая автоматизация

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

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

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

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

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

Таким образом, мы имеем три вида ограничений:

1) По максимально возможному объему выпуска;

2) По минимально возможному объему выпуска;

3) По наличию ресурсов (материальных, трудовых, финансовых и т.д.).

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

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

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

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

Для поиска оптимального решения в программе Excel необходимо чтобы была установлена надстройка «Поиск решения«.

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

В открывшемся диалоговом окне выбрать надстройку Поиск решения (если надстройка еще не активна) и нажать кнопку Перейти (1)

В открывшемся окне установить соответствующую галочку (2) и нажать кнопку Ок.

Далее для удобства присваиваем диапазонам, в которых находятся наши данные имена.

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

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

Диапазон с данными оптимального выпуска назовем «Выпуск». Этот параметр мы и будем оптимизировать.

Соответственно прибыль у нас рассчитается по формуле:

=СУММПРОИЗВ(Маржа;Выпуск)

Диапазону с данными о наличии того или иного ресурса присвоим имя «Наличие».

Ограничение по максимальному выпуску назовем «Максимум» а по минимальному соответственно «Минимум».

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

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

Например для первого вида ресурса формула будет выглядеть так:

=СУММПРОИЗВ(Выпуск;$C14:$V14),

где диапазон $C14:$V14 содержит данные о норме расхода данного вида ресурса по всем видам продукции.

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

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

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

Выпуск <= Максимум

Выпуск => Минимум

Выпуск = целое (целое число для штучных единиц продукции)

Потребность <= Наличие

Оптимизируем Прибыль до максимального значения.

Теперь, после того как мы сформировали логику поиска решения остается самое простое, внести параметры поиска решения в соответствующую надстройку.

На вкладке Данные вызываем Надстройку Поиск решения и вносим соответствующие параметры и ограничения.

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

Все. Теперь остается Нажать копку Найти решение и в нашем диапазоне «Выпуск» появится оптимальный план выпуска при котором значение прибыли примет максимально возможное для данных условий значение.

Для удобства в шаблоне присутствует макрос, который при нажатии на кнопку «Найти решение» осуществляет запуск надстройки поиска решений.

Чтобы он корректно работал, необходимо в редакторе VBAустановить ссылку на SOLVER.

Как это сделать вы найдете на листе «Справка» в шаблоне.

Для того чтобы увидеть файл в полном размере нажмите «квадратики» в правом нижнем углу.

excel-training.ru

Решение финансовых задач в ms excel

Федеральное агентство по образованию

Государственное образовательное учреждение высшего профессионального образования

Тульский государственный университет

Кафедра автоматизированных информационных и управляющих систем

КУРСОВАЯ РАБОТА

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

на тему:

Выполнила: ________________________________ Чебыкина Е.С.

гр. 720782

Руководитель: _____________________________ Яблочкин Л.Б.

Тула 2009

Содержание

Введение……………………………………………………………………………4

Глава 1. Назначение и основные возможности ms Excel………………………5

Глава 2. Решение задач……………………………………………………………9

Задача №1……………………………………………………………………9

Задача №2………………………………………………………………….13

Задача №3………………………………………………………………….17

Заключение……………………………………………………………………….22

Список используемой литературы………………………………………………23

Федеральное агентство по образованию

Тульский государственный университет

Кафедра «Автоматизированные информационные и управляющие системы»

Задание на курсовую работу

Студент __________________________________ Группа ________________

Фамилия, инициалы

  1. Тема ____________________________________________________________

__________________________________________________________________

  1. Срок предоставления работы к защите » ___ » _________ 200__ г.

  1. Исходные данные для проектирования

Входные документы ________________________________________________

________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

Выходные документы _______________________________________________

______________________________________________________________________________________________________________________________________________________________________________________________________

Решаемая задача ___________________________________________________

______________________________________________________________________________________________________________________________________________________________________________________________________

Содержание пояснительной записки

1. Анализ предметной области.

2. Постановка задачи.

3. Разработка информационного обеспечения задачи.

4. Описание технологии и алгоритмов решения задачи и их машинная реализация.

5. Разработка диалогового приложения пользователя.

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

5. Задание выдал __________ «___»______ 200__ г ______________________

Подпись Фамилия и инициалы

6. Задание получил_________ «___»______ 200__ г ______________________

Подпись Фамилия и инициалы

Введение

Табличный процессор MS Excel (электронные таблицы) – одно из наиболее часто используемых приложений интегрированного пакета MS Office, мощнейший инструмент в умелых руках, значительно упрощающий рутинную повседневную работу. Основное назначение MS Excel – решение практически любых задач расчетного характера, входные данные которых можно представить в виде таблиц. Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без программирования расчётов. В сочетании же с языком программирования Visual Basic for Application (VBA), табличный процессор MS Excel приобретает универсальный характер и позволяет решить вообще любую задачу, независимо от ее характера.

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

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

studfiles.net

Добавить комментарий

Ваш адрес email не будет опубликован.