Пошаговая работа в excel: Функции и формулы в Excel с примерами

пошаговая инструкция, специфические особенности и рекомендации

Большинство пользователей компьютерных систем на основе Windows с установленным пакетом Microsoft Office непременно сталкивались с приложением MS Excel. У начинающих юзеров программа вызывает некоторые сложности в освоении, тем не менее работа в «Экселе» с формулами и таблицами не так уж и сложна, как может показаться на первый взгляд, если знать основные принципы, заложенные в приложении.

Что такое Excel?

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

Работа с таблицами «Эксель» подразумевает использование более широких возможностей с объединением и вычислений, и обычного текста, и мультимедиа. Но в своем изначальном виде программа создавалась именно как мощнейший математический редактор. Некоторые, правда, поначалу принимают приложение за какой-то калькулятор с расширенными возможностями. Глубочайшее заблуждение!

Инструкция к Microsoft Office Excel: как вставить функцию в…

Для того чтобы понять, как вставить функцию в Excel, нужно выяснить, что именно нам нужно….

Работа в «Экселе» с таблицами для начинающих: первое знакомство с интерфейсом

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

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

Основное поле занимает сама таблица, которая разбита на ячейки. Каждая имеет нумерацию, благодаря двумерным координатам – номер строки и буквенное обозначение столбца (для примера берем Excel 2016). Такая нумерация нужна для того, чтобы в формуле зависимостей можно было четко определить именно ту ячейку с данными, операция над которой будет производиться.

Что это — документ Эксель и как с ним работать?

Каждая рабочая страница документа Excel сохраняется в отдельном файле. В связи с этим необходимо…

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

Основные типа ввода данных и простейшие операции

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

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

Но ввод несколько отличается от того, который производится в текстовых редакторах. Дело в том, что программа изначально настроена на автоматическое распознавание того, что пользователь прописывает в активной ячейке. Например, если ввести строку 1/2/2016, данные будут распознаны как дата, и в ячейке вместо введенных цифр появится дата в упрощенном виде – 01.02.2016. Изменить формат отображения можно достаточно просто (на этом остановимся чуть позже).

Сводная таблица Excel: как создать и работать? Работа со…

Многие офисные пользователи зачастую сталкиваются с целым рядом проблем при попытке создания и…

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

Но по окончании ввода данных многие начинающие пользователи пытаются перейти на следующую ячейку, используя для этого клавиатурные стрелки (по типу того, как это можно сделать в таблицах Word). И это не срабатывает. Почему? Да только потому, что работа с таблицами «Эксель» отличается от текстового редактора Word достаточно ощутимо. Переход может осуществлен нажатием клавиши Enter или установкой активного прямоугольника на другую ячейку при помощи левого клика мыши. Если после написания чего-то в активной ячейке нажать клавишу Esc, ввод будет отменен.

Действия с листами

Работа с листами на первых порах трудностей вызывать не должна. На панели снизу имеется специальная кнопка добавления листов, после нажатия на которую появится новая таблица с автоматическим переходом на нее и заданием имени («Лист 1», «Лист 2» и т. д.).

Двойным кликом можно активировать переименование любого из них. Также можно использовать меню правого клика для вызова дополнительного меню, в котором имеется несколько базовых команд.

Форматы ячеек

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

В окне слева представлены все доступные форматы, а справа показаны варианты отображения данных. Если посмотреть на пример с датой, показанный выше, в качестве формата выбирается «Дата», а справа устанавливается желаемый вид (например, 1 февраля 2016 г.).

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

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

Работа в «Эксель» с формулами (таблицами): пример

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

Но можно ввести формулу и вручную (работа с таблицами «Эксель» предполагает и такую возможность, когда автоматическое действие не предусмотрено). Для той же суммы следует в строке формул поставить знак равенства и прописать операцию в виде A1+A2 или СУММ(A1;A2), а если требуется указать диапазон ячеек, используется такой вид после знака равенства: (A1:A20), после чего будет посчитана сумма всех чисел, находящихся в ячейках от первой до двадцатой включительно.

Построение графиков и диаграмм

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

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

Перекрестные связи, импорт и экспорт данных

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

Кроме того, файлы Excel можно экспортировать в другие форматы (например, PDF), копировать из них данные и т. д. Но и сама программа может открывать файлы, созданные в других приложениях (текстовые форматы, базы данных, веб-страницы, XML-документы и т. д.).

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

Сводные таблица в Excel пошаговая инструкция

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

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

Как создать сводную таблицу? Давайте рассмотрим с примерами.

Допустим, вы – поставщик бакалейной продукции, овощей и фруктов для крупных компаний. И ваша сводная таблица по всем показателям выглядит так:

Вам нужно узнать о продажах каждого вида продукции в разных городах страны.

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

Давайте узнаем, как сделать это в несколько кликов.

Шаг 1. Важно проверить, чтобы ваша таблица быть плоской — без объединённых ячеек. И у каждого столбца должен быть заголовок.

Шаг 2. Выделяем всю таблицу и идём на вкладку «Вставка», выбираем «Сводная таблица» и нажимаем «Ок» (менять в настройках ничего не нужно). 

Если вы пока не знаете, каким образом хотите сформировать таблицу, вам поможет кнопка «Рекомендуемые сводные таблицы», которая предложит вам разные варианты формирования отчётности. Но раз у нас есть понимание, какие показатели нам нужны, давайте создадим таблицу самостоятельно.

Шаг 3. В правой стороне документа у вас появляется меню «Поля сводной таблицы». Именно там мы продолжаем свою работу. В первой части отметьте галочками те показатели (названия колонок), которые хотите видеть в сводной таблице: Товар, Регион продажи и Продажи. Они отразятся в областях ниже.

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

Фильтр – это область, по которой нужно создать фильтр для исследования данных документа. Так мы можем смотреть продажи по городам, регионам или товарам.

Столбцы – показатели с качественными признаками. Можно сделать колонки по годам или регионам.

Строки – названия показателей, которые будут располагаться по горизонтали. Если в этой области будет более одного показателя, то таблица будет многоуровневой.

Значения – использование различных формул для получения количественного результата. Чаще всего используют формулу суммы для понимания результатов продаж по категориям.

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

Шаг 5.

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

А что, если вам нужно сделать отчёт, в котором будут отражены продажи отдельных товаров разным поставщикам? В таком случае, нам нужно поставить галочку у дополнительного показателя – Поставщика. И расставить показатели внутри полей так, чтобы таблица была нам понятна.

И вот как теперь будет выглядеть наша таблица:

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

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

Как создать рабочий лист Excel: пошаговое руководство

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

Что такое рабочий лист Excel?

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

Итак, это было краткое введение в рабочий лист Excel. В следующем разделе вы узнаете, как создать новый рабочий лист Excel.

Как создать рабочий лист Excel?

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

По умолчанию при запуске Microsoft Excel есть возможность выбора различных рабочих листов, как показано ниже.

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

Вставка нового рабочего листа Excel

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

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

На экране появится новое диалоговое окно, в котором вы можете выбрать вариант включения нового листа.

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

Переименовать рабочий лист Excel

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

В следующем разделе вы узнаете, как удалить рабочий лист.

Удалить рабочий лист Excel

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

Рабочие листы в Excel также можно скрыть. Теперь вы увидите, как это можно сделать.

Скрыть рабочий лист Excel

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

Если вы хотите отобразить скрытый рабочий лист, вы можете щелкнуть правой кнопкой мыши существующий рабочий лист и выбрать параметр «Показать», как показано ниже.

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

В следующей части вы научитесь перемещать или копировать рабочий лист Excel

Перемещение или копирование листа Excel

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

После выбора опции вы увидите новое диалоговое окно, как показано ниже.

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

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

Если вы хотите переместить рабочий лист в новую рабочую книгу, выберите параметр «Новая рабочая книга» в первом меню и не устанавливайте галочку в последнем параметре, который гласит «сделать копию».

В следующей части вы научитесь защищать ячейки на листе Excel.

Защита ячеек в листе Excel

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

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

На этом вы подошли к концу этой статьи «Рабочий лист Excel».

Ждете карьеры бизнес-аналитика? Ознакомьтесь с Сертификационным курсом по бизнес-аналитике с помощью Excel и пройдите сертификацию уже сегодня.

Следующие шаги

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

Вы хотите углубиться в изучение Microsoft Excel Возможно, вы заинтересованы в развитии своей карьеры в области бизнес-аналитики?

Если ответ положительный на один или оба вышеуказанных вопроса, сертификационный курс по бизнес-аналитике, предлагаемый Simplilearn , — это то, что вам обязательно нужно изучить. Эта прикладная учебная программа предназначена для того, чтобы помочь вам понять основные концепции анализа данных и статистики, которые могут помочь вам разработать идеи на основе доступных данных для представления результатов в виде визуализаций с помощью информационных панелей на уровне руководителей. Все это и многое другое вы узнаете в нашей передовой программе. Посмотрите и запишитесь прямо сейчас.

У вас есть какие-либо вопросы к нам по этому учебнику по таблицам Excel? Если да, не стесняйтесь поделиться ими в разделе комментариев ниже. Наши профильные специалисты решат их для вас в кратчайшие сроки.

Часто задаваемые вопросы

1. Что такое рабочий лист Excel?

Вы можете сортировать и анализировать необработанные данные в Excel. Это набор строк и столбцов. Каждый рабочий лист имеет 1048576 строк и 16384 столбца.

2. Каковы основные функции Excel?

Ниже перечислены самые основные и важные функции Excel: 

1. =ЕСЛИ(логический тест, значение если правда, значение если ложь)

2. СУММЕСЛИМН

=СУММЕСЛИМН(диапазон сумм, диапазон критериев 1, критерии 1, …)

3. СЧЁТЕСЛИ

=СЧЁТЕСЛИМН(диапазон критериев 1, критерии 1, …)

4. ТРИМ

=ОТРЕЗАТЬ(текст)

5. СЦЕПИТЬ

= СЦЕПИТЬ (текст1, текст2, текст3, …)

6. ЛЕВЫЙ/ПРАВЫЙ

=ЛЕВО(текст, количество символов)

=ВПРАВО(текст, количество символов)

7. ВПР

= ВПР (искомое значение, массив таблиц, номер индекса столбца, поиск в диапазоне)

8. ЕСЛИ ОШИБКА

=ЕСЛИОШИБКА(ВПР(B2,$G$2:$H$12,2,ЛОЖЬ),»Имя не найдено. Проверьте оба списка»)

9. ЗНАЧЕНИЕ

=ЗНАЧ(текст)

10. УНИКАЛЬНЫЙ

=УНИКАЛЬНЫЙ(массив, по столбцу, ровно один раз)

3. Сколько рабочих листов в Excel?

В одну книгу можно добавить 255 отдельных листов.

4. Как называются файлы Excel?

В Excel есть рабочие листы или электронные таблицы, с которыми мы обычно работаем. Эти рабочие листы хранятся в рабочей книге. В одной книге может быть несколько рабочих листов.

5. Что такое сочетания клавиш для Excel?

Некоторые распространенные сочетания клавиш для Excel: 

1. Закрыть книгу — Ctrl+W
2. Открыть книгу — Ctrl+O
3. Перейти на вкладку «Главная» — Alt+H
4. Сохранить книгу — Ctrl+S
5. Копировать выделение — Ctrl+C
6. Вставить выделение — Ctrl+V 
7. Отменить недавнее действие — Ctrl+Z 
8. Удалить содержимое ячейки — Удалить
9. Выберите цвет заливки — Alt+H, H
10. Вырезать выделение — Ctrl+X 
11. Перейдите на вкладку «Вставка» — Alt+N 
12. Применить полужирное форматирование — Ctrl+B 
13. Выровнять содержимое ячейки по центру — Alt+H, A, C
14. Перейти на вкладку «Разметка страницы» — Alt+P 
15. Перейти на вкладку «Данные» — Alt+A

Учебное пособие по решателю Excel с пошаговыми примерами

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

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

В этом руководстве мы рассмотрим все основные аспекты надстройки Excel Solver и предоставим пошаговое руководство по ее наиболее эффективному использованию.

• Что такое Solver в Excel?
• Как добавить решатель в Excel
• Как использовать Solver в Excel
• Примеры решения Excel
• Как сохранить и загрузить модели Excel Solver
• Алгоритмы решателя Excel

Что такое Excel Solver?

Excel Solver принадлежит к специальному набору команд, часто называемых инструментами анализа «что, если». Он в первую очередь предназначен для моделирования и оптимизации различных бизнес-моделей и инженерных моделей.

Надстройка Excel Solver особенно полезна для решения задач линейного программирования, также называемых задачами линейной оптимизации, и поэтому иногда называется Решатель линейного программирования . Кроме того, он может решать гладкие нелинейные и негладкие задачи. Дополнительные сведения см. в разделе Алгоритмы Excel Solver.

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

Как добавить «Поиск решения» в Excel

Надстройка «Поиск решения» включена во все версии Microsoft Excel, начиная с 2003, но по умолчанию она не включена.

Чтобы добавить Solver в Excel, выполните следующие действия:

1. В Excel 2010 — Excel 365 щелкните Файл > Параметры .
   В Excel 2007 нажмите кнопку Microsoft Office и выберите Параметры Excel .
2. В параметрах Excel , нажмите Надстройки на левой боковой панели, убедитесь, что Надстройки Excel выбраны в поле Управление в нижней части окна, и нажмите Перейти .
3. В диалоговом окне Надстройки установите флажок Надстройка Solver и нажмите OK :

Чтобы получить Solver для Excel 2003 , перейдите в меню Инструменты и щелкните Надстройки . В надстройках доступно , установите флажок Надстройка Solver и нажмите OK .

Примечание. Если Excel отображает сообщение о том, что надстройка Solver в настоящее время не установлена ​​на вашем компьютере, нажмите Да , чтобы установить ее.

Где находится Solver в Excel?

В современных версиях Excel кнопка Solver появляется на вкладке Data , в группе Analysis :

Где находится Solver в Excel 2003?

После загрузки надстройки «Поиск решения» в Excel 2003 ее команда добавляется в Инструменты меню:

Теперь, когда вы знаете, где найти Solver в Excel, откройте новый рабочий лист и приступим!

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

Как использовать Solver в Excel

Перед запуском надстройки Excel Solver сформулируйте модель, которую вы хотите решить, на листе. В этом примере давайте найдем решение следующей простой задачи оптимизации.

Проблема . Предположим, вы владелец салона красоты и планируете предоставлять своим клиентам новую услугу. Для этого вам необходимо купить новое оборудование стоимостью 40 000 долларов США, которое необходимо оплатить в рассрочку в течение 12 месяцев.

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

Для этой задачи я создал следующую модель:

А теперь давайте посмотрим, как Excel Solver может найти решение этой проблемы.

1. Запустите Excel Solver

На вкладке Data в группе Analysis нажмите кнопку Solver .

2. Определите проблему

Откроется окно Solver Parameters , в котором необходимо настроить 3 основных компонента:

• Целевая ячейка
• Переменные ячейки
• Ограничения

Что именно делает Excel Solver с указанными выше параметрами? Он находит оптимальное значение (максимальное, минимальное или заданное) для формулы в Целевая ячейка путем изменения значений в ячейках Переменная и с учетом ограничений в ячейках Ограничения .

Цель

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

В этом примере целевой ячейкой является B7, которая рассчитывает срок платежа по формуле =B3/(B4*B5) , а результат формулы должен быть равен 12:

Переменные ячейки

Переменные ячейки ( Изменяющиеся ячейки или Настраиваемые ячейки в более ранних версиях) — это ячейки, которые содержат переменные данные, которые могут быть изменены для достижения цели. Excel Solver позволяет указать до 200 переменных ячеек.

В этом примере у нас есть несколько ячеек, значения которых можно изменить:

• Прогнозируемое количество клиентов в месяц (B4), которое должно быть меньше или равно 50; и
• Стоимость услуги (B5), которую должен рассчитать Excel Solver.

Наконечник. Если переменные ячейки или диапазоны в вашей модели несмежные , выберите первую ячейку или диапазон, а затем нажмите и удерживайте клавишу Ctrl при выборе других ячеек и/или диапазонов. Или введите диапазоны вручную, разделив их запятыми.

Ограничения

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

Чтобы добавить ограничения, выполните следующие действия:

• Нажмите кнопку Добавить справа от поля « С учетом ограничений ».

• В окне Ограничение введите ограничение.
• Нажмите кнопку Добавить , чтобы добавить ограничение в список.

• Продолжайте вводить другие ограничения.
• После того, как вы ввели окончательное ограничение, нажмите OK для возврата в главное окно Solver Параметры.

Excel Solver позволяет указать следующие отношения между указанной ячейкой и ограничением.

• Меньше или равно , равно и больше или равно . Вы устанавливаете эти отношения, выбирая ячейку в поле Ссылка на ячейку , выбирая один из следующих знаков: <= , =, или >= , а затем введите число, ссылку на ячейку/имя ячейки или формулу в поле Constraint (см. скриншот выше).
• Целое число . Если указанная ячейка должна быть целым числом, выберите int , и слово integer появится в поле Constraint .
• Различные значения . Если каждая ячейка в указанном диапазоне должна содержать другое значение, выберите dif и слово AllDifferent появится в поле Constraint .
• Двоичный . Если вы хотите ограничить ссылочную ячейку значением 0 или 1, выберите bin , и слово binary появится в поле Constraint .

Примечание. Отношения int , bin и dif можно использовать только для ограничений переменных ячеек.

Чтобы отредактировать или удалить существующее ограничение, выполните следующие действия:

• В диалоговом окне Параметры решателя щелкните ограничение.
• Чтобы изменить выбранное ограничение, нажмите Изменить и внесите необходимые изменения.
• Чтобы удалить ограничение, нажмите кнопку Удалить .

В этом примере ограничения:

• B3=40000 — стоимость нового оборудования 40 000 долларов.
• B4<=50 - прогнозируемое количество пациентов в месяц в возрасте до 50 лет.

3. Решите проблему

После настройки всех параметров нажмите кнопку Решить внизу окна Параметры решателя (см. скриншот выше) и дайте надстройке Excel Solver найти оптимальное решение вашей проблемы.

В зависимости от сложности модели, памяти компьютера и скорости процессора это может занять несколько секунд, несколько минут или даже несколько часов.

Когда Solver завершит обработку, он отобразит Solver Results диалоговое окно, в котором вы выбираете Keep the Solver Solution и нажимаете OK :

Окно Solver Result закроется, и решение сразу появится на рабочем листе.

В этом примере в ячейке B5 отображается 66,67 доллара США, что является минимальной стоимостью за услугу, которая позволит вам оплатить новое оборудование через 12 месяцев при условии, что количество клиентов в месяц составляет не менее 50:

Чаевые:

• Если Excel Solver слишком долго обрабатывал определенную проблему, вы можете прервать процесс, нажав клавишу Esc. Excel пересчитает рабочий лист с последними значениями, найденными для ячеек Variable .
• Чтобы получить дополнительные сведения о решенной проблеме, щелкните тип отчета в поле Отчеты , а затем щелкните OK . Отчет будет создан на новом листе:

.

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

Примеры Excel Solver

Ниже вы найдете еще два примера использования надстройки Excel Solver. Сначала мы найдем решение известной головоломки, а затем решим реальную задачу линейного программирования.

Решатель Excel, пример 1 (магический квадрат)

Я думаю, все знакомы с головоломками «магический квадрат», где вам нужно поместить набор чисел в квадрат так, чтобы все строки, столбцы и диагонали в сумме давали определенное число.

Например, знаете ли вы решение для квадрата 3×3, содержащего числа от 1 до 9?где каждая строка, столбец и диагональ в сумме дают 15?

Вероятно, решить эту головоломку методом проб и ошибок не составит большого труда, но держу пари, что Решатель найдет решение быстрее. Наша часть работы состоит в том, чтобы правильно определить проблему.

Для начала введите числа от 1 до 9 в таблицу, состоящую из 3-х строк и 3-х столбцов. Решателю Excel на самом деле не нужны эти числа, но они помогут нам визуализировать проблему. Что действительно нужно надстройке Excel Solver, так это формулы SUM, которые суммируют каждую строку, столбец и 2 диагонали:

Со всеми формулами запустите Solver и настройте следующие параметры:

• Set Objective . В этом примере нам не нужно устанавливать какую-либо цель, поэтому оставьте это поле пустым.
• Переменные ячейки . Мы хотим заполнить числами ячейки от B2 до D4, поэтому выберите диапазон B2:D4.
• Ограничения . Должны быть соблюдены следующие условия:
  • $B$2:$D$4 = AllDifferent — все ячейки переменных должны содержать разные значения.
  • $B$2:$D$4 = целое число — все ячейки переменных должны быть целыми числами.
  • $B$5:$D$5 = 15 — сумма значений в каждом столбце должна равняться 15.
  • $E$2:$E$4 = 15 — сумма значений в каждой строке должна равняться 15.
  • $B$7:$B$8 = 15 — сумма обеих диагоналей должна быть равна 15.

Наконец, нажмите кнопку Решить , и решение уже готово!

Excel Solver, пример 2 (задача линейного программирования)

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

Проблема : Вы хотите минимизировать стоимость доставки товаров с 2 разных складов 4 различным клиентам. Каждый склад имеет ограниченное предложение, и у каждого клиента есть определенный спрос.

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

Исходные данные

Вот как выглядит наша задача оптимизации транспортировки:

Формулировка модели

Чтобы определить нашу задачу линейного программирования для Excel Solver, давайте ответим на 3 основных вопроса:

1. Какие решения должны быть сделанный? Мы хотим рассчитать оптимальное количество товара для доставки каждому покупателю с каждого склада. Это переменных ячеек (B7:E8).
2. Каковы ограничения? Запасы, доступные на каждом складе (I7:I8), не могут быть превышены, и должно быть доставлено количество, заказанное каждым клиентом (B10:E10). это Ограничено ячеек.
3. Какова цель? Минимальная общая стоимость доставки. А это наша ячейка Objective (C12).

Следующее, что вам нужно сделать, это рассчитать общее количество товаров, отгруженных с каждого склада (G7:G8), и общее количество товаров, полученных каждым клиентом (B9:E9). Вы можете сделать это с помощью простых формул суммы, показанных на снимке экрана ниже. Кроме того, вставьте формулу СУММПРОИЗВ в C12 для расчета общей стоимости доставки:

Чтобы облегчить понимание нашей модели оптимизации транспортировки, создайте следующие именованные диапазоны:

Последнее, что вам осталось сделать, это настроить параметры Excel Solver:

• Цель: Shipping_cost установить на Min
• Переменные ячейки: Products_shipped
• Ограничения: Всего_получено = Заказано и Всего_отгружено <= Доступно

Обратите внимание, что в этом примере мы выбрали метод решения Simplex LP , потому что мы имеем дело с задачей линейного программирования. Если вы не уверены, какая у вас проблема, вы можете оставить метод решения GRG Nonlinear по умолчанию. Для получения дополнительной информации см. Алгоритмы Excel Solver.

Решение

Нажмите кнопку Решить в нижней части окна Параметры решателя , и вы получите ответ. В этом примере надстройка Excel Solver рассчитала оптимальное количество товаров для доставки каждому покупателю с каждого склада с минимальной общей стоимостью доставки:

Как сохранить и загрузить сценарии Excel Solver

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

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

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

Сохранение модели

Чтобы сохранить сценарий решателя Excel, выполните следующие действия:

1. Откройте рабочий лист с рассчитанной моделью и запустите решатель Excel.
2. В параметрах решателя , нажмите кнопку Загрузить/Сохранить .
3. Excel Solver сообщит вам, сколько ячеек необходимо для сохранения вашего сценария. Выберите столько пустых ячеек и нажмите Сохранить :
4. Excel сохранит вашу текущую модель, которая может выглядеть примерно так:

В то же время появится окно Параметры решателя , в котором вы можете изменить свои ограничения и попробовать различные варианты «что, если».

Загрузка сохраненной модели

Когда вы решите восстановить сохраненный сценарий, выполните следующие действия:

1. В окне Параметры решателя нажмите кнопку Загрузить/Сохранить .
2. На рабочем листе выберите диапазон ячеек, содержащих сохраненную модель, и нажмите Загрузить :
3. В диалоговом окне Загрузить модель нажмите кнопку Заменить :
4. Откроется главное окно Excel Solver с параметрами ранее сохраненной модели. Все, что вам нужно сделать, это нажать на Кнопка «Решить » для пересчета.

Алгоритмы решателя Excel

При определении задачи для решателя Excel можно выбрать один из следующих методов в раскрывающемся списке Выберите метод решения :

• GRG Нелинейный. Обобщенный редуцированный градиентный нелинейный алгоритм используется для задач, которые являются гладкими нелинейными, т.е. в которых хотя бы одно из ограничений является гладкой нелинейной функцией переменных решения. Более подробную информацию можно найти здесь.
• LP Симплекс . Метод Simplex LP Solving основан на алгоритме Simplex, созданном американским ученым-математиком Джорджем Данцигом.