Evolcom.ru

Бытовая техника
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как повторить значение ячейки x раз в Excel?

27 полезных навыков для комфортного пользования Excel ч.2

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

Множественная вставка формулы или значений

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

Когда содержимое введено, нажмите Ctrl+Enter . Затем данные будут вставлены во все выделенные элементы. Вам не придется копировать и вставлять несколько раз. Это значительно ускорит вашу работу.

Быстрая настройка относительных ссылок

Наличие относительных ссылок в Excel — отличная возможность. Следовательно, скопированные формулы не нужно редактировать. В приведенном выше примере, если написать =A1 в ячейке и скопировать ее в следующую строку, новая формула уже будет содержать =A2. Если вы скопируете ее в правый столбец, она будет выглядеть как =B1.

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

  • =$A$1 — заморожены и строки и столбцы
  • =$A1 — заморожены только столбцы
  • =A$1 — заморожены только строки
  • =A1 — ничего не заморожено

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

  1. Установите курсор на ссылку, которую вы хотите заморозить
  2. Нажмите клавишу F4, чтобы установить заморозку
  3. Нажмите клавишу F4, чтобы выбрать необходимую заморозку

Нажать F4 гораздо проще, чем вручную писать знаки доллара.

Перетаскивание формул

Когда вы написали формулу и вам нужно изменить ее расположение, простое копирование, скорее всего, не сработает из-за относительных ссылок, описанных выше. Существует три способа решения этой проблемы:

  1. Установите координаты, как описано в предыдущем параграфе. Теперь вы можете скопировать формулу без потери ссылок
  2. Копировать текст формулы. Поместите курсор в исходную ячейку и скопируйте формулу как текст непосредственно из строки формул. Теперь вставьте его в любую другую ячейку, ссылки не изменятся. Но форматирование ячейки не будет скопировано
  3. Переместите исходную ячейку, перетащив ее. Выберите его и наведите курсор мыши на рамку ячейки так, чтобы курсор превратился в четырехстороннюю стрелку. Перетащите ячейку в новое место. Будет перенесено как содержание, так и форматирование.

Копирование нескольких формул

Если Вам нужно скопировать несколько формул так, чтобы сохранились относительные ссылки – заставьте Excel думать, что Ваши формулы – это текст. Например, можно заменить знак «=» вначале формулы на другой символ:

  1. Выделите весь копируемый диапазон формул
  2. Нажмите Ctrl+F и в открывшемся окне перейдите на вкладку «Заменить»

Просто и быстро, мы используем его.

Копирование формул двойным кликом

Если у вас есть массив данных, к которому вы хотите применить одну и ту же формулу построчно, то сделайте это следующим образом:

  • Запишите нужную формулу в самой верхней строке
  • Сделайте двойной клик по маленькому квадратному маркеру в нижнем правом углу ячейки с формулой

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

Проверка всех формул на листе

В тех случаях, когда на листе существует очевидная ошибка и имеется достаточно формул для ее проверки, выведите список всех формул. Для этого щелкните на ленте Формулы — Зависимости формул — Показать формулы.

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

Как выделить все формулы на листе

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

Затем выберите Формулы и снова выберите ОК. Excel выделит все формулы в ячейках.

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

Как вычислить часть формулы в Экселе

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

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

Затем нажмите F9 . Если расчет выбранного сегмента будет выполнен, то он заменит сегмент результатом.

Нажмите Enter, чтобы сохранить результаты. Если вы хотите вернуть формулу в исходное состояние, нажмите Esc.

Пошаговое вычисление формулы

Пошаговое выполнение — еще один инструмент для проверки вычислений. Выберите ячейку с формулой и щелкните на ленте Формулы — Зависимости формул — Вычислить формулу .

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

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

Вставка значений вместо формул

Один из вопросов, который мне задают чаще всего, — как заменить формулы результатами вычислений. Для всех сразу выделите массив формул, затем скопируйте их с помощью Ctrl+C.

Затем используйте инструмент Paste Special. Нажмите Пуск — Буфер обмена — Вставить — Специальная вставка. В появившемся окне выберите Значения и нажмите OK. Формулы были заменены вашими результатами.

Быстрые арифметические операции с массивами

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

Выберите пустую ячейку и введите в нее значение 1,1, затем скопируйте эту ячейку. Выберите весь столбец, который необходимо изменить. Нажмите поочередно: Лента Главная — Буфер обмена — Вставить — Пользовательская вставка . Выберите Value и установите флажок Multiplication.

Все выбранные элементы массива будут умножены на 1.1, то есть увеличены на 10%. Вы можете складывать, вычитать, умножать и делить.

Применяйте имена вместо адресов ячеек

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

Например, диапазон ячеек A1:A12 можно назвать «sales_2019». Если написать формулу в удобочитаемом виде, что это будет: =SUM(A1:A12) или =SUM(sales_2019)? Очевидно, что вторая.

Выделение нужных ячеек и щелчок на ленте Формулы — Определенные имена — Присвоить имя — это один из способов присвоения имен ячейкам. Более подробно эта тема рассматривается здесь.

Применение имен в уже написанных формулах

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

Нажмите Формулы — Определенные имена — Присвоить имя (стрелка вниз) — Применить имена . Появится простое диалоговое окно. Выберите нужные имена и нажмите OK. Программа заменит ссылки на их названия в существующих формулах.

Продвинутое использование имен

Используйте BOM для создания новых переменных или констант. Вот два примера:

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

Нажмите на ленте Формулы – Определенные имена – Присвоить имя . В поле «Имя» запишите информативное название константы, а в поле «Диапазон» — ее значение. Жмите Ок . Теперь это имя можно использовать, как константу.

Как объединить ячейки в Excel

Функция объединения ячеек в Excel хорошо известна. Однако выполнение этой операции часто занимает много времени. Здесь мы рассмотрим, что означает термин «объединение» и как быстро выполнить эту задачу. Выбор лучших вариантов сэкономит вам много времени.

Формат ячеек

Команда Excel Правая кнопка мыши → Формат ячеек → Выравнивание → Вид → Вид → Объединить ячейки удаляет границы между ячейками в выбранном диапазоне. В результате получается одна большая клетка. На рисунке показано объединение ячеек из одной строки и трех столбцов.

Объединение ячеек Excel

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

Команда объединить и поместить в центре

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

Надпись в центре объединенной ячейки

Это выглядит красиво, но крайне непрактично. Выделение столбца с помощью комбинации Ctrl + пробел расширит его на все столбцы, представленные объединенной ячейкой. Кроме того, при копировании ширина столбца не может быть автоматически отрегулирована в электронной таблице Excel и т.д. В общем, объединение ячеек создает массу неудобств для дальнейшей работы. Поэтому в большинстве случаев лучше не объединять ячейки.

Как найти объединенные ячейки в Excel

Может случиться так, что в файле уже есть слитые ячейки, и они мешают нормальной работе. Например, в отчете 1С или при работе с чужим файлом Excel. Тогда вам нужно быстро найти их и отменить слияние. Как сделать это быстро? Выполните следующие действия.

  1. Вызовите команду поиска Главная (вкладка) → Правка (группа) → Найти и выберите → Найти. Вы также можете нажать комбинацию клавиш Ctrl + F.
  2. Убедитесь, что поле «Найти» пустое.
  3. Нажмите кнопку Настройки.
  4. Перейдите в раздел Формат, в окне Формат выберите вкладку Выравнивание и установите флажок Объединить ячейки.
  5. OK.
  6. В окне Найти и заменить нажмите Найти все.
  7. Появятся адреса всех объединенных ячеек. Вы можете выбрать их по одному или все сразу, нажав Ctrl + A.

Как убрать объединение ячеек в Excel

Таким образом можно отменить слияние сразу на всем листе Excel.

  1. Выберите все ячейки на листе. Можно щелкнуть на треугольнике на пересечении заголовков строк и столбцов или нажать сочетание клавиш Ctrl + A (один или два раза).
  2. Повторно примените команду Главная (вкладка) → Выравнивание (группа) → Объединить и центрировать.

Все объединенные клетки вернутся в нормальное состояние. Если что-то пошло не так, отмените последнее действие.

Горячие клавиши объединения ячеек в Excel

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

Буквы под командами после нажатия Alt

Для каждой команды или выпадающего списка будут указаны буквы. Таким образом, вы можете определить клавиши для вызова нужной команды. Комбинация Alt — I — Sch — B в моей версии Excel необходима для объединения и центрирования ячеек.

Второй метод. Способ объединения ячеек и получения команды с помощью клавиши F4 (повторение последнего действия).

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

Настройка панели быстрого доступа Excel

Это приведет вас к настройкам панели инструментов быстрого доступа. Найдите команду Scale и поместите ее в центр, затем добавьте ее в окно справа.

Перенос кнопки на панель быстрого доступа

На панели инструментов быстрого доступа вы найдете кнопку.

Объединение ячеек на панели быстрого доступа

После нажатия Alt под этой кнопкой появится цифра, зависящая от порядкового номера строки.

Цифра под командой на панели быстрого доступа

Теперь есть еще одна горячая клавиша для объединения ячеек в Excel — Alt + 5.

Выравнивание по центру (без объединения ячеек)

Чтобы поместить надпись в середине строки, используйте команду Выровнять по центру

  1. Поместите надпись в левую ячейку строки, в которой необходимо выравнивание.
  2. Выберите нужное количество ячеек в правой части.
  3. Вызов Щелкните правой кнопкой мыши → Формат ячеек → Выравнивание → Горизонтальное → По центру.

Внешне результат такой же, как и при объединении, только все ячейки остаются на том же месте. Есть несколько тонкостей.

  1. Не всегда понятно, в какой ячейке находится запись.
  2. Возможно только горизонтальное выравнивание, вертикальное выравнивание невозможно.

В большинстве случаев лучше использовать центральное выравнивание, а не объединять клетки.

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

Объединение ячеек с помощью & (амперсанд) и функции Excel СЦЕПИТЬ (CONCATENATE)

Объединение содержимого ячеек – очень распространенная задача. Выбор решения зависит от типа данных и их количества.
Если нужно сцепить несколько ячеек, то подойдет оператор & (амперсанд).

& (амперсанд) для сцепки ячеек Excel

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

Функция СЦЕПИТЬ для объединения ячеек

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

Функция СЦЕПИТЬ для текста

Номер присоединяется полностью, в том виде, в котором он хранится в памяти программы. Добавьте функцию ТЕКСТ после объединения, если вы хотите изменить формат на число или дату.

Правильная связь между текстом и цифрой.

Объединение текста и числа в Excel функцией СЦЕПИТЬ

Связь между текстом и датой.

Объединение текста и даты в Excel функцией СЦЕПИТЬ

Как правило, эти методы хорошо подходят для объединения столбцов в Excel. Однако у & & и CLOTH есть существенный недостаток. Каждая часть текста должна быть указана отдельно. Поэтому объединение большого количества ячеек становится проблемой.

Функция Excel СЦЕП (CONCAT)

Теперь в Excel 2016 вместо функции СЧЁТЕСЛИ используется функция СЧЁТЕСЛИ. В качестве аргумента можно указать целый диапазон значений.

Функция СЦЕП Excel

Все хорошо, но нет места. Если вам нужно присоединиться к сепаратору, CEEP не сильно поможет. Полезной будет и новая функция объединения ячеек.

Функция Excel ОБЪЕДИНИТЬ (TEXTJOIN)

Функция CONNECT также появилась в Excel 2016 и решила все проблемы, связанные с одновременным объединением ячеек: указание всего диапазона, вставка разделителя и даже пропуск пустых ячеек в диапазоне, чтобы не дублировать разделители.

Сепаратор — разделитель, который вставляется между ячейками

Skip_blank — если 0, пустые ячейки включаются, если 1, они игнорируются. Обычно устанавливается в 1, чтобы не дублировать разделитель.

Text1;. Ссылка на диапазон или конкретную ячейку.

Функция ОБЪЕДИНИТЬ Excel

Лучшее решение для объединения ячеек — функция CONNECT в Excel.

Заполнить – Выровнять

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

Столбец с текстом для сцепления

Поступайте следующим образом

  1. Выделите столбец данных.
  2. Разверните выделение справа на столько или столько, сколько нужно для заполнения объединенного текста.
  3. Вызовите Home (вкладка) -> Edit (группа) -> Fill -> Adjust.

Текст помещается в ячейку, отделенную пробелом.

Выравнивание столбца с текстом по горизонтали

  • Если ячейки содержат числа, даты или формулы, этот подход не сработает. Он работает только с текстом.
  • Общая длина текста не должна превышать 255 символов. Ненужный текст будет автоматически перенесен на вторую строку (см. ролик ниже).

Давайте подытожим, как объединить ячейки, строки и столбцы в электронной таблице Excel. С помощью форматирования можно просто удалить границы между ячейками. Это не лучшее решение. Лучше использовать центральное выравнивание. Объединение ячеек в Excel без потери данных осуществляется с помощью специальных функций.

Лабораторная работа №9 (EXCELL) / Лабораторная09

1) Запустите Excel (Пуск > Программы > Microsoft Excel) и откройте рабочую книгу .xls, созданную ранее.

2) Выберите рабочий лист обработки экспериментов, нажав на ярлык.

1) Нажмите кнопку Вставить функцию на панели формул и выберите C1.

4) В окне мастера функций выберите категорию «Ссылки и массивы» и функцию «INDEX», В новом диалоговом окне выберите первый параметр набора параметров.

5) Поместите текстовый курсор в первое поле параметров окна «Аргументы функции» и выберите «Другие функции» из выпадающего списка в строке формул.

6) Выберите функцию LINE из категории Статистика с помощью мастера функций.

Если выбран диапазон, содержащий значения функции (столбец B), он становится первым параметром функции ЛИНИЯ.

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

9) Переместите текстовый курсор в строке формул так, чтобы он оказался над именем функции INDEX. Установите число 1 в качестве второго параметра функции INDEX. Нажмите кнопку OK в окне Аргументы функции.

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

10) Сделайте текущую ячейку D1. Следуйте инструкциям, приведенным в параграфах. Таким образом, эта ячейка будет содержать формулу: =INDEX(LINEAR(C1:C20;A1:A20);2). Также вы можете ввести ее вручную (несимметрично). Теперь ячейки C1 и D1 содержат коэффициенты a и b уравнения лучшей линии, соответственно.

11) Сделайте текущую ячейку C2. Повторите операции в пунктах. Используя следующую формулу, введите 3-9:

12) Сделайте ячейку D2 текущей ячейкой. Повторите операции, описанные в разделах 3-9. 3-9, или введите следующую формулу вручную:

Ячейки C2 и D2 теперь содержат, соответственно, коэффициенты a и b уравнения наилучшего приближения.

(Для интерполяции или экстраполяции оптимальной кривой без явного определения параметров можно использовать функции TREND (для линейной зависимости) и ROST (для экспоненциальной зависимости)).

13) Чтобы построить наилучшую линию другим способом, дайте команду Сервис > Анализ данных.

14) Появится одноименное диалоговое окно. Затем выберите Регрессия и нажмите OK.

15) В поле Y Input Range укажите диапазон, содержащий значения функции (столбец B), перетащив его.

Используя метод растяжения, укажите диапазон, содержащий независимую переменную (столбец A) в поле Input Y Range (Диапазон ввода Y).

17) Активируйте переключатель Новый рабочий лист и назовите его Результат вычисления.

По окончании расчета нажмите кнопку OK и откройте рабочий лист Результат расчета. Убедитесь, что значения рассчитанных коэффициентов (см. ячейки B17 и B18) совпадают со значениями, полученными первым методом.

19) Сохраните рабочий журнал.xls.

Упражнение 2: Использование таблиц с символами подстановки

Задача. Постройте графики функций, коэффициенты которых определены в предыдущем упражнении. 1) Запустите Excel (Пуск > Программы > Microsoft Excel) и откройте рабочую книгу book.xls.

2) Нажмите на вкладку для выбора рабочего листа управления экспериментом.

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

4) С помощью перетаскивания выберите значения в столбце A. Используйте команду Правка > Копировать, чтобы скопировать данные в буфер обмена. Сделайте ячейку F2 текущей ячейкой и с помощью команды Правка > Вставить скопируйте определенные значения независимой переменной в столбец F, начиная со второй строки.

5) В ячейку G1 введите формулу =C3*$C$1+$D$1. Здесь C3 — это входная ячейка. а остальные ссылки — это рассчитанные коэффициенты наименьших квадратов уравнения прямой линии.

6) Введите формулу =$D$2*$C$2^C3 в ячейку H1, чтобы вычислить значение экспоненциальной функции. В Excel можно табулировать несколько функций одной и той же переменной за одну операцию.

7) Выделите прямоугольную область со столбцами F, G и H и строками от строки 1 с формулами до последней строки с данными из столбца F.

8) Приведите данные > Таблица подстановки. Выберите поле Заменить значения строкой в поле и щелкните на ячейке ввода C3.

9)Нажмите OK, чтобы заполнить пустые ячейки в столбцах G и H выбранного диапазона значениями из формулы в ячейках первой строки для значений выбранной независимой переменной в столбце F.

10) Перейдите на лист Chart1 (если имя листа Chart по умолчанию было изменено, используйте собственное имя).

11) Нажмите кнопку Мастер диаграмм на стандартной панели инструментов и пропустите первый шаг.

12) Выберите вкладку «Строка» и нажмите кнопку «Добавить». В поле Имя введите: «Лучшая прямая линия». В поле X Value введите диапазон ячеек, содержащих данные в столбце F, а в поле Y Value введите диапазон ячеек в столбце G.

13) Еще раз нажмите «Добавить». Поле «Имя»: укажите «Показать функцию». В поле «Значения X» должен быть указан диапазон ячеек для данных в столбце F, а в поле «Значения Y» — диапазон ячеек для данных в столбце H.

14) Нажмите кнопку Готово, чтобы перестроить диаграмму в соответствии с новыми настройками.

15) Сохраните рабочую книгу.xls.

Упражнение 3. Решение уравнения с помощью инструментов Excel

Задача. Найти решение уравнения x 3 − 3 x 2 + x = 1 .

1) Запустите Excel (Пуск > Программы > Microsoft Excel) и откройте созданную ранее рабочую книгу .xls.

2) Создайте новый рабочий лист (Вставка > Рабочий лист), дважды щелкните по ярлыку и назовите его Уравнения.

3) Введите значение 0 в ячейку A1.

Левая половина уравнения записывается в ячейке B1, а независимая переменная — в ячейке A1. В качестве примера можно привести формулу A1*Z-3*A1*2+A1. 5) Выполните команду Сервис > Выбрать параметр.

B) В поле «Вставить в ячейку» введите B1, в поле «Значение» введите -1, в поле «Изменить значение ячейки» введите А1.

7) Нажмите OK и увидите результат согласования, отображенный в диалоговом окне Parameter Matching Result. Нажмите OK, чтобы сохранить значения для ячеек, участвующих в операции.

8) Введите в ячейку A1 другое начальное значение, например, 0,5 или 2. Совпали ли результаты вычислений? Как можно объяснить различия?

9) Сохранить рабочую книгу book.xls.

Задача 4: Решение оптимизационной проблемы

Задача. Завод производит три типа электронных устройств (устройство A, устройство B, устройство C), используя для их сборки три типа микросхем (тип 1, тип 2 и тип 3). Потребление микросхем приведено в следующей таблице:

Стоимость изготовленного оборудования одинакова.

Ежедневно на завод поступает 400 чипов типа 1 и 500 чипов типа 2 и 3. Каково оптимальное соотношение ежедневного производства различных типов устройств, если производственные мощности завода позволяют использовать весь запас полученных микросхем?

Откройте рабочую книгу, созданную ранее (Пуск > Программы > Microsoft Excel), в Excel.

2) Создайте новый лист (Вставка > Лист), дважды щелкните по его ярлыку и назовите его Организация производства.

3) В ячейки A2, AZ и A4 введите ежедневные поставки аксессуаров — 400, 500 и 500, соответственно.

4) Введите нули в ячейки C1, D1 и E1 — позже значения в этих ячейках будут подобраны автоматически.

5) В ячейках интервала C2:E4 разместите таблицу расхода ингредиентов.

Формулы для расчета расхода компонентов в ячейках с B2 по B4 приведены в ячейках. В ячейке B2 формула будет =$C$1*C2+$D$1*B2+$E$1*E2, а остальные формулы можно получить путем автозаполнения (обратите внимание на использование абсолютных и относительных ссылок).

7) Введите формулу, которая подсчитывает общее количество произведенных инструментов в ячейку F1: для этого выделите диапазон C1:E1 и нажмите кнопку «Автосумма» на стандартной панели инструментов.

8) Выберите Сервис > Решатель — появится диалоговое окно Настройки решателя.

В поле Set Target Cell (F1) необходимо выбрать ячейку, содержащую оптимизируемое значение. Установите переключатель Equal To Max (Равно

Максимальное значение) (требуется максимальная выработка).

10) В поле Edit Cells (Редактирование ячеек) установите диапазон параметров, который будет выбран, на C1:E1.

Чтобы определить набор ограничений, нажмите кнопку Добавить. В диалоговом окне Добавить ограничение в поле Ссылка на ячейку укажите диапазон B2:B4. В качестве условия задайте <=. В поле Ограничения укажите диапазон от A2 до A4. Согласно этому условию, количество компонентов не должно превышать их запасы в любой момент времени. Нажмите кнопку OK.

12) Снова нажмите кнопку Добавить. Поле ссылки на ячейку должно содержать диапазон C1:E1. В качестве условия укажите >=. В поле Constraint (Ограничение) установите число 0. При выполнении этого условия количество произведенных единиц не будет иметь отрицательного значения. Нажмите кнопку OK.

13) Снова нажмите кнопку Добавить. Ссылка на ячейку указывает на диапазон C1:E1. Выберите int(целое число) в качестве условия. Это условие не допускает дробных единиц. Нажмите OK.

14) Нажмите кнопку Resolve (Разрешить). По завершении оптимизации открывается диалоговое окно Solve Results.

15) Выберите радиокнопку Keer Solver и нажмите кнопку OK.

16) Проанализируйте полученное решение — выглядит ли оно очевидным? Проверьте, является ли он оптимальным, экспериментируя со значениями C1:E1. Чтобы восстановить оптимальные значения, вы можете повторить операцию решения в любое время.

голоса
Рейтинг статьи
Читайте так же:
Как посчитать количество дней кроме воскресенья / выходных в Excel?
Ссылка на основную публикацию
Adblock
detector