Теория Excel_0. Решение задач оптимизации исследование влияния разных факторов на данные

• Название: Решение задач оптимизации исследование влияния разных факторов на данные
• Дата: 06.09.2022
• Размер: 474.43 Kb.
• Имя файла: Теория Excel_0.docx
• Тип: Решение; #663640
• страница: 6 из 6

3.6.4 Обработка списков

Под списком в MS Excel понимают специальным образом организованные таблицы, имеющие одинаковую структуру (рис. 3.10). Списки можно рассматривать как внутренние базы данных, записями в

которых являются строки, а полями – столбцы. В MS Excel разработаны специальные операции обработки списков.




Рис. 3.10. Список товаров
Широкие возможности для поиска нужной информации предоставляет автофильтр. Для его создания необходимо выделить диапазон А1:Е6 и выбрать в меню Данные → Фильтр. Признаком создания фильтра являются ^{кнопки в заголовках таблицы, разворачивающие список . При нажатии на} эту кнопку появляется окно, позволяющее выбрать либо первые 10 строк, либо все, либо по определенному условию. Например, для приведенного списка на рис. 3.10 требуется выбрать из имеющихся на складе телевизоры с диагональю экрана 14. Для этого кнопкой в заголовке Размер экрана задать условие числового фильтра «равно 14». Появится список, приведенный на рис. 3.11. Далее задать в окне Запас условие: «больше 0». Появится список, приведенный на рис. 3.12.




Рис. 3.11. Телевизоры с диагональю экрана 14




Рис. 3.12. Телевизоры с диагональю экрана 14, имеющиеся на складе

7. Построение диаграмм

Диаграммы MS Excel часто используются как иллюстрации объема производства, продаж и покупательского спроса в сфере бизнеса, для

отображения результатов научных расчетов, сопоставления данных (например, план и результат) и т.д.

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

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

1. Пример построения кольцевой диаграммы

Построим диаграмму на основе результатов консолидации (см. рис. 3.5). Для построения необходимо выполнить следующую последовательность действий:

1. 
   Выделить всю таблицу, включая заголовки граф (кроме строки
   

Итоги).

2. 
   В разделе Вставка на панели Диаграмма выбрать необходимый тип диаграммы (в данном случае Другиедиаграммы→Кольцевая).
   
3. 
   Ввести название диаграммы «Реализация нефтепродуктов», настроить внешний вид в Конструкторе диаграмм (появляется при нажатии левой клавиши мыши на диаграмме).
   

Результат построения диаграммы приведен на рис. 3.13. Каждое из трех колец диаграммы представляет собой один из столбцов исходной таблицы («К-во», «Цена», «Сумма»).



Рис. 3.13. Кольцевая диаграмма по реализации нефтепродуктов

2. Пример построения графика функции

Одномерная функция 𝑦 = 𝑓⁽𝑥⁾ может быть задана в табличном или в аналитическом виде. Исходные данные для графика в Excel распола-гаются в строках или столбцах. Если функция задана аналитически, то для построения графика, необходимо задать программу вычисления ряда значений функций для заданного значения аргумента.

Для примера построим график функции 𝑦 = 𝑥³ − 12𝑥² + 3. Пусть значения аргумента содержатся в столбце А, а значения функции – в столбце В.

1. 
   Размесить в строке 1 обозначение осей графика [Xи Y(x)].
   
2. 
   Записать в ячейку А2 начальное значение аргумента: –5, а в ячейку В2 – формулу: =А2^3-12*A2^2+3.
   
3. 
   В результате на листе будут записаны координаты первой точки графика, как показано на рис. 3.14.
   

Рис. 3.14. Подготовка данных для графика

4. 
   Пусть требуется построить график в диапазоне значений аргумента [– 5; 10] с шагом 1. Это соответствует диапазону ячеек, которые должны хранить значения аргумента: А2:А17. В ячейку А3 записать значение –4, выделить ячейки А2:А3 и с помощью маркера заполнения растянуть диапазон на требуемое число ячеек.
   
5. 
   Выделить ячейку В2. Установите курсор мыши на маркере заполнения и растяните рамку выделения так, чтобы она содержала в себе нужный диапазон ячеек: В2:В17. Формула из ячейки В2 распространится на весь выбранный диапазон с коррекцией ссылок на ячейку.
   
6. 
   Выделить диапазон В1:В17 и выбрать на панели Диаграммытип
   

График.

7. 
   Открыв конструктор диаграмм, нажать на кнопку Выбратьданные , далее в появившемся окне Выбористочникаданныхвыбрать диапазон данных (А1:А17) для горизонтальной оси (рис. 3.15).
   

Рис. 3.15. Выбор источника данных для горизонтальной оси

8. 
   Скорректировать название диаграммы и подписи осей.
   

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



Рис. 3.16. График функции

3. Пример построения поверхности

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

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

𝑦 = 𝑒𝑥𝑝⁽−⁽𝑥1 − 1⁾^2 − ⁽𝑥1 − 1⁾ ∙ ⁽𝑥2 − 1⁾ + ⁽𝑥2 − 1⁾^2⁾.

Для подготовки данных необходимо выполнить следующие действия:

1. 
   Ввести последовательность значений Х1 в столбец А, начиная с ячейки А2. Ячейка А1 должна остаться пустой (рис. 3.17).
   
2. 
   Ввести последовательность значений Х2 в строку 1, начиная с ячейки В1. Ячейка А1 должна остаться пустой (рис. 3.17).
   
3. 
   Ввести в ячейку В2 формулу
   

𝑦 = 𝐸𝑥𝑝⁽−⁽𝐴2 − 1⁾^2 − ⁽𝐴2 − 1⁾ ∙ ⁽𝐵1 − 1⁾ + ⁽𝐵1 − 1⁾^2⁾.

Для заполнения формулами остальных ячеек можно воспользоваться маркером заполнения, передвигая его либо по строке, либо по столбцу. При заполнении строки в формуле должен будет меняться только номер столбца, а номер строки остается неизменным. Поэтому необходимо поставить в формуле символ абсолютной ссылки «$» перед буквенным обозначением строки ($А2) и перед числовым обозначением столбца (B$1).

В результате формула в ячейке В2 должна принять вид

𝑦 = 𝐸𝑥𝑝⁽−⁽$𝐴2 − 1⁾^2 − ⁽$𝐴2 − 1⁾ ∙ ⁽𝐵$1 − 1⁾ + ⁽𝐵$1 − 1⁾^2⁾.

4. 
   Выделить ячейку В2и заполнить формулами строку 2. Выделить ячейку В2 и заполнить формулами столбец В.
   
5. 
   Аналогично заполнить остальные столбцы/строки таблицы.
   

Таблица данных для построения поверхности приведена на рис. 3.17.




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

6. 
   Выделить блок данных для построения диаграммы.
   
7. 
   Выбрать тип диаграммы – поверхность, уточнить ее вид.
   
8. 
   Добавить легенду, название диаграммы и осей.
   

Построенная при помощи мастера поверхность приведена на рис. 3.18.




Рис. 3.18. Пример поверхности