24Использование собственных имен ячеек в ms excel

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

Для создания именованной ячейки нужно выделить нужную ячейку (рис. 1) или диапазон (рис. 2), затем щелкнуть в текстовое поле, ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ячейка может ссылаться на несвязный диапазон ячеек (выделенный с «Ctrl»).

25Использование имен в формулах в ms excel

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

имена могут содержать не более 255 символов;

имена должны начинаться с буквы и могут содержать любой символ, кроме пробела;

имена не должны быть похожи на ссылки, такие, как ВЗ, С4;

имена не должны использовать функции Excel, такие, как СУММ, ЕСЛИ и т. п. В меню Вставка, Имя существуют две различные команды создания именованных областей: Создать и Присвоить. Создание имени :

1. Выделите ячейку В1 (табл. 26).

2. Выберите в меню Вставка, Имя (Insert, Name) команду Присвоить (Define) .

3. Введите имя Часовая ставка и нажмите ОК .

Тип примера Пример без имени Пример с именем

Ссылка =СУММ(C20:C30) =СУММ(ПродажиЗаПервыйКвартал)

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

Определенное имя Имя, представляющее ячейку, диапазон ячеек, формулу или значение константы. Можно создавать свои собственные определенные имена, а иногда Microsoft Office Excel создает для пользователя определенное имя, например, при установке области печати.

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

26Преимущества использования собственных имен вместо ссылок в ms excel

Применение именованных ячеек и диапазонов имеет ряд преимуществ.

Имена позволяют понять смысл формулы и использовать ее. Это особенно важно

для тех, кто не участвовал в создании рабочего листа. Очевидно, что такую формулу,

как, например, =Доходы-Налоги, понять значительно легче, чем =D20-D40.

Описательное имя диапазона (например, Общий_доход) запоминается легче, чем

адреса ячеек (например, A21). Кроме того, вероятность опечатки при написании

имени меньше, чем при вводе адреса ячейки или диапазона.

Вы можете легко перемещаться по рабочему листу, используя поле Имя, распо-

ложенное в левой части строки формул (щелкните на кнопке со стрелкой, и поя-

вится список всех назначенных имен). Перейти от одного элемента листа к друго-

му можно, выбрав команду Найти и выделить􀃖Перейти (Find&Select􀃖Go To)

или нажав клавишу <F5>. В открывшемся диалоговом окне следует ввести имя

диапазона или ячейки.

Когда вы выделяете именованную ячейку или диапазон, имя этого элемента появ-

ляется в поле Имя.

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

зонов. Эти имена вы сможете вставить в формулу, выбрав команду Использовать

в формуле (Use in Formula) во вкладке Формулы и выбрав в открывшемся спи-

ске нужное имя.

Использование имен вместо адресов ячеек и диапазонов облегчит создание макросов и дальнейшую работу с ними.

27)Подбор единственного решения средствами MS Excel

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

решить нужную задачу с каким-либо начальным значением параметра;

выбрать команду Подбор параметра меню Сервис;

в окне диалога Подбор параметра в поле Установить в ячейке задать абсолютную ссылку на ячейку, содержащую расчетную формулу, а в поле Значение — то значение, которое следует получить в качестве результата формулы;

в поле Изменяя значение ячейки ввести ссылку на ячейку с параметром;

нажать кнопку ОК или клавишу Enter, на экране появится окно диалога Результат подбора параметра;

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

28)Многопараметрическая оптимизация средствами MS Excel

1. На вкладке Данные в группе Анализ щелкните Решатель.

Если команда Поиск решения или группа Анализ отсутствует, необходимо загрузить надстройку (Надстройка. Вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.) "Поиск решения".

Загрузка надстройки "Поиск решения"

1. На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки.

2. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти.

3. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

2. В поле Оптимизировать целевую функцию введите ссылку на ячейку (Ссылка на ячейку. Координаты, определяющие расположение ячейки на листе. Например, B3 представляет ссылку на ячейку, находящуюся на пересечении столбца B и строки 3.) или имя (Имя. Слово или строка знаков, представляющие ячейку, диапазон ячеек, формулу или константу. Понятные имена, такие как «Продукты», используют для ссылок на диапазоны, названия которых трудно запомнить, например, Продажи!C20:C30.) целевой ячейки. Целевая ячейка должна содержать формулу.

3. Выполните одно из указанных ниже действий.

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

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

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

1. В поле Изменяя ячейки переменных введите имена диапазонов ячеек переменных решения или ссылки на них. Несмежные ссылки разделяйте запятыми. Ячейки переменных должны быть прямо или косвенно связаны с целевой ячейкой. Можно задать до 200 ячеек переменных.

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

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

2. В поле Ссылка на ячейку введите ссылку на ячейку или имя диапазона ячеек, на значения которых налагаются ограничения.

3. Выберите в раскрывающемся списке отношение ( <=, =, >=, int, bin или dif), которое нужно использовать между ссылкой и ограничением.

Если выбрать вариант int, в поле Ограничение появится значение целое число. Если выбрать вариант bin, в поле Ограничение появится значение двоичное число. Если выбрать вариант dif, в поле Ограничение появится значение все разные.

4. Если в поле Ограничение было выбрано отношение <=, = или >=, введите число, ссылку на ячейку (или имя ячейки) или формулу.

5. Выполните одно из указанных ниже действий.

   • Чтобы принять данное ограничение и добавить другое, нажмите кнопку Добавить.

   • Чтобы принять ограничение и вернуться в диалоговое окно Параметры поиска решения, нажмите кнопку ОК.

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

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

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

2. Нажмите кнопку Изменить и внесите изменения либо нажмите кнопку Удалить.

1. Нажмите кнопку Выполнить и выполните одно из указанных ниже действий.

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

• Чтобы восстановить исходные значения перед нажатием кнопки Решить, выберите вариант Восстановить исходные значения.

Примечания    

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

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

• Чтобы сохранить значения ячейки переменной решения в качестве сценария, который можно будет отобразить позже, нажмите кнопку Сохранить сценарий в диалоговом окне Результаты поиска решения, а затем введите имя этого сценария в поле Название сценария