Пошук рішення задач в Excel з прикладами

1. Рішення задач оптимізації в Excel
2. Вирішення фінансових завдань в Excel
3. Рішення економетрики в Excel
4. Рішення логічних задач в Excel
5. Рішення математичних задач в Excel

Користувачі Excel давно і успішно застосовують програму для вирішення різних типів завдань в різних областях.

Excel - це найпопулярніша програма в кожному офісі в усьому світі. Її можливості дозволяють швидко знаходити ефективні рішення в самих різних сферах діяльності. Програма здатна вирішувати різного роду завдання: фінансові, економічні, математичні, логічні, оптимізаційні та багато інших. Для наочності ми кожне з вище описаних рішення задач в Excel і приклади його виконання.

Рішення задач оптимізації в Excel

Оптимізаційні моделі застосовуються в економічній і технічній сфері. Їх мета - підібрати збалансоване рішення, оптимальне в конкретних умовах (кількість продажів для отримання певної виручки, краще меню, число рейсів і т.п.).

В Excel для вирішення завдань оптимізації використовуються наступні команди:

Для вирішення найпростіших завдань застосовується команда «Підбір параметра». Найскладніших - «Диспетчер сценаріїв». Розглянемо приклад розв'язання оптимізаційної задачі за допомогою надбудови «Пошук рішення».

Умова. Фірма виробляє кілька сортів йогурту. Умовно - «1», «2» і «3». Реалізувавши 100 баночок йогурту «1», підприємство отримує 200 рублів. «2» - 250 рублів. «3» - 300 рублів. Збут, налагоджений, але кількість наявного сировини обмежена. Потрібно визначити, який йогурт і в якому обсязі необхідно робити, щоб отримати максимальний дохід від продажів.

Відомі дані (в т.ч. добові норми витрат сировини) занесемо в таблицю:

На підставі цих даних складемо робочу таблицю:

1. Кількість виробів нам поки невідомо. Це змінні.
2. У стовпець «Прибуток» внесено формули: = 200 * B11, = 250 * В12, = 300 * В13.
3. Витрата сировини обмежений (це обмеження). В осередку внесені формули: = 16 * B11 + 13 * B12 + 10 * B13 ( «молоко»); = 3 * B11 + 3 * B12 + 3 * B13 ( «закваска»); = 0 * B11 + 5 * B12 + 3 * B13 ( «амортизатор») і = 0 * B11 + 8 * B12 + 6 * B13 ( «цукор»). Тобто ми норму витрати помножили на кількість.
4. Мета - знайти максимально можливий прибуток. Це осередок С14.

Активізуємо команду «Пошук рішення» і вносимо параметри.

Після натискання кнопки «Виконати» програма видає своє рішення.

Оптимальний варіант - сконцентруватися на випуску йогурту «3» і «1». Йогурт «2» проводити не варто.

Вирішення фінансових завдань в Excel

Найчастіше для цієї мети застосовуються фінансові функції. Розглянемо приклад.

Умова. Розрахувати, яку суму покласти на внесок, щоб через чотири роки утворилося 400 000 рублей. Відсоткова ставка - 20% річних. Відсотки нараховуються щоквартально.

Оформимо вихідні дані у вигляді таблиці:

Так як процентна ставка не змінюється протягом усього періоду, використовуємо функцію ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).

Заповнення аргументів:

1. Ставка - 20% / 4, тому що відсотки нараховуються щоквартально.
2. Кпер - 4 * 4 (загальний термін вкладу * число періодів нарахування на рік).
3. Плт - 0. Нічого не пишемо, тому що депозит поповнюватися не буде.
4. Тип - 0.
5. БС - сума, яку ми хочемо отримати в кінці терміну вкладу.

Вкладникові необхідно вкласти ці гроші, тому результат негативний.

Для перевірки правильності рішення скористаємося формулою: ПС = БС / (1 + ставка) кпер. Підставимо значення: ПС = 400 000 / (1 + 0,05) 16 = 183245.

Рішення економетрики в Excel

Для встановлення кількісних і якісних взаємозв'язків застосовуються математичні та статистичні методи і моделі.

Дано 2 діапазону значень:

Значення Х гратимуть роль факторного ознаки, Y - результативного. Завдання - знайти коефіцієнт кореляції.

Для вирішення цього завдання передбачена функція КОРРЕЛ (масив 1; масив 2).

Рішення логічних задач в Excel

У табличному процесорі є вбудовані логічні функції. Будь-яка з них повинна містити хоча б один оператор порівняння, який визначить ставлення між елементами (=,>, =,

Приклад завдання. Учні здавали залік. Кожен з них отримав позначку. Якщо більше 4 балів - залік зданий. Менш - не зданий.

1. Ставимо курсор в осередок С1. Натискаємо значок функцій. Вибираємо «ЯКЩО».
2. Заповнюємо аргументи. Логічний вираз - B1> = 4. Ця умова, при якому логічне значення - ІСТИНА.
3. Якщо ІСТИНА - «Залік здав». БРЕХНЯ - «Залік не здав».

Рішення математичних задач в Excel

Засобами програми можна вирішувати як прості математичні задачки, так і більш складні (операції з функціями, матрицями, лінійними рівняннями і т.п.).

Умова навчального завдання. Знайти обернену матрицю В для матриці А.

1. Робимо таблицю зі значеннями матриці А.
2. Виділяємо на цьому ж аркуші область для оберненої матриці.
3. Натискаємо кнопку «Вставити функцію». Категорія - «Математичні». Тип - «МОБР».
4. В поле аргументу «Масив» вписуємо діапазон матриці А.
5. Натискаємо одночасно Shift + Ctrl + Enter - це обов'язкова умова для введення масивів.

завантажити приклади

Можливості Excel не безмежні. Але безліч завдань програми «під силу». Тим більше тут не описані можливості які можна розширити за допомогою макросів і призначених для користувача налаштувань.