Построение таблицы с одним параметром

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

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

Ячейка ввода, используемая в формуле, может быть пустой (иметь значение, равное 0). Эта вспомогательная ячейка формально предназначена для перебора значений из входного интервала. Фактическое значение этой ячейки никогда не изменяется, так что можно использовать любую ячейку рабочего листа вне таблицы. Построение таблицы с одним параметром производится с помощью команды Таблица данных вкладки Данные, раздел Работа с данными, кнопка Анализ-Что-Если. Необходимо заполнить одно из двух полей «Подставлять значения строки в», если входной интервал – столбец, или «Подставлять значения столбца в», если входной интервал – строка.

 

Построение таблицы с двумя параметрами

Таблицы подстановки с двумя переменными используют одну формулу с двумя наборами значений. При формировании таблицы подстановки с двумя переменными исходные данные следует располагать и в столбце, и в строке. Ячейку, находящуюся на пересечении строки и столбца с исходными данными, используют для ввода формулы. Формула должна ссылаться на две ячейки ввода. После выделения диапазона, содержащего формулу и подставляемые значения, необходимо выполнить команду Данные – Анализ «что-если» - Таблица данных, заполнив поля «Подставлять значения строки в» и «Подставлять значения столбца в».

 

Решение задачи подбор параметра

Подбор параметра является частью блока задач, который называют инструментами анализа "что-если". Используется, когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата. На вкладке Данные в группе Работа с данными выбрать пиктограмму Анализ «что-если», а затем выбрать в списке пункт Подбор параметра.

В окне команды Подбор параметра необходимо заполнить поля:

- Установить в ячейке: вводят адрес ячейки, содержащей формулу;

- Значение: вводят искомое значение;

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

В ячейку, задаваемую в поле Изменяя значение ячейки по определенному алгоритму подставляются значения до тех пор, пока не будет найдено решение, задаваемое в поле Значение. По умолчанию по команде Подбор параметра вычисления прекращаются либо после 100 операций, либо когда найденное решение отличается от заданного не более чем на 0.001. Если требуется большая точность, то следует нажать кнопку Office, выбрать команду Параметры - Формулы и изменить поле Относительная погрешность.

 

Консолидация данных

Консолидация данных – это объединение значений из нескольких диапазонов данных.

Предусмотрено несколько способов консолидации:

1. С помощью трехмерных ссылок (наиболее предпочтительный способ) - отсутствуют ограничения на расположение данных в исходных областях.

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

3. По категории - используют, если требуется обобщить набор листов, имеющих одинаковые заголовки.

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

5.2.5 Сводная таблица

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

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

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

После этого на листе создается сводная таблица, а на Ленте появляется временный раздел Работа со сводными таблицами с двумя вкладками Параметры и Конструктор для работы со сводной таблицей

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

 

Порядок выполнения работы

5.3.1 Создать на рабочем листе таблицу:

Рис.5.1. Исходная таблица для построения таблиц подстановки

5.3.2Построить таблицу с одним параметром (процентная ставка):

- Ввести в ячейки D2:D10 исходные данные: список процентных ставок: 3%; 3.25%; 3.5%; 3.75%; 4%; 4.25%; 4.5%; 4.75%; 5%.

- В ячейку Е1 ввести формулу =((1+В3)^В2)*В1

- Выделить диапазон D1:E10

- Выполнить команду Данные - Таблица подстановки. Откроется окно
Таблица подстановки. В поле «Подставлять значение по строкам в»
ввести В3. Нажать ОК.

- Для построения таблицы подстановки используется формула (=ТАБЛИЦА(;В3)).

5.3.3 Построить таблицу с двумя параметрами (процентная ставка и срок вклада):

- Ввести в ячейки G2:G10 исходные данные: список процентных ставок: 3%; 3.25%; 3.5%; 3.75%; 4%; 4.25%; 4.5%; 4.75%; 5%.

- Ввести в ячейки H1:M1 исходные данные: срок вклада: 10, 12, 14, 16, 18, 20

- В ячейку G1 ввести формулу =((1+В3)^В2)*В1.

- Выделить диапазон G1:M10.

- Выполнить команду Данные - Работа с данными – Анализ «что-если» - Таблица подстановки. В поле «Подставлять значение по столбцам в» ввести В2. В поле «Подставлять значение по строкам в» ввести В3. Нажать ОК.

- Для построения таблицы подстановки используется формула (=ТАБЛИЦА(ВЗ;В4)).

5.3.4.Построить таблицу числовых данных (таблица подстановки для 1-ой переменной) для функции вида Y = aX + b. Значение X изменяется в диапазоне 3.00 - 6.00 с шагом 0.25. Значения а и b задать самостоятельно.

Построить таблицу числовых данных (таблица подстановки для 2-х переменных) для функции вида Z = aX + bY + c. Значение X изменяется в диапазоне 4.00 - 8.00 с шагом 0.3, значение Y изменяется в диапазоне 5-25 с шагом 5. Значения а,Ь, с задать самостоятельно

5.3.5 Использование команды «Подбор параметра». В ячейке В4 (рис.5.1) сумма выплат вычисляется по формуле =((1+В3)^В2)*В1. Для уменьшения суммы выплат до величины 120 000р за счет изменения размера вклада необходимо выделить ячейку В4. Затем на вкладке Данные в разделе Работа с данными нажать кнопку Анализ «что-если» и выбрать строку «Подбор параметра». В поле "значение" указать 120000р. В поле "изменяя значение ячейки" указать адрес В1. Нажать ОК. Результат вычислений отобразится в следующем диалоговом окне "результат подбора параметра". После нажатия ОК вычисленные значения будут вставлены в таблицу. Аналогично увеличить сумму выплат до величины 150 000р за счет изменения процентной ставки.

5.3.6 Найти решения уравнения согласно варианту в таблице 5.1. Для поиска корней строим график функции (левая часть уравнения). Аргументы необходимо задать таким образом, чтобы график функции пересекал ось абцисс. Решением уравнения являются значения аргумента, которые обращают функцию в 0. Эти значения выбираются по графику, а затем уточняются с помощью средства «Подбор параметра».

 

Таблица 5.1 – Варианты заданий для решения уравнения

№ вар	Уравнение

 

5.3.7 Консолидация данных. На новом листе создать три таблицы (рис.5.2) и заполнить их на основании таблицы «Доходы фирм по продаже компьютеров»

 

Рис.5.2.Таблицы для консолидации

Определить суммарные и средние продажи за три месяца. Для этого

- указать верхнюю левую ячейку конечной области консолидируемых данных, например А15;

- в окне Консолидация выбрать соответствующие функции;

- в поле Ссылка ввести исходную область (таблицу продаж в январе) и нажать кнопку Добавить, повторить действия для всех консолидируемых исходных областей;

- установить флажок Значения левого столбца.

5.3.8 Создание сводной таблицы. Скопировать на новый лист таблицу «Работники магазинов»

Построить сводные таблицы:

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

- для определения суммарной заработной платы в каждом магазине по всем должностям и для каждой должности в отдельности. Для этого выбрать значения поля Фамилия в качестве заголовков строк, поле № магазина в качестве заголовков столбцов, Должность – фильтр, Заработная плата – в качестве значений.

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

 

Рис.5.3. Пример сводной таблицы

 

Содержание отчета

Цель работы, постановка задачи, описание использования инструментов аналица «что-если»: таблиц подстановки, подбора параметров, консолидации данных и построения сводных таблиц.

 

5.5 Контрольные вопросы

1. В каком виде представляются исходные данные для консолидации?

2. Цель использования инструмента «подбор параметра».

3. Способы прогнозирования значений путем построения таблиц на основе значений 1-ой и 2-х переменных.

4. Как создать сводную таблицу в MS EXCEL?

ЛАБОРАТОРНАЯ РАБОТА №6

СОЗДАНИЕ МАКРОСОВ

 

6.1 Цель работы – получение практических навыков создания макропрограмм с помощью макрорекордера

Теоретические сведения

Макрос – это набор инструкций, имеющих имя, которые выполняются по команде запуска. Цель макросов – автоматизировать повторяющиеся действия.

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

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

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

Личная книга макросов (Personal Macro Workbook) – это книга, которая автоматически загружается при запуске Excel. Макросы из неё можно вызывать из любой рабочей книги.

Для создания макроса с помощью макрорекордера необходимо:

-На вкладке Разработчик в группе Код нажать кнопку Запись макроса. Откроется окно «Запись макроса» (рис.6.1).

-В поле Имя макроса ввести имя макроса. По умолчанию используются имена Макрос1, Макрос2 и т.д. Имя можно изменить. Первым символом имени макроса должна быть буква. Последующие символы могут быть буквами, цифрами или знаками подчеркивания.

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

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

-Ввести описание макроса в поле Описание.

-Для начала записи макроса нажать кнопку ОК.

-Выполнить действия, которые нужно записать.

-На вкладке Разработчик в группе Код нажать кнопку Остановить запись.

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

Рис.6.1. Окно «запись макроса»

 

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

Кнопка Относительная ссылка ( вкладка Вид, область Макросы) позволяет по ходу записи переключаться с относительных ссылок на абсолютные и обратно.

Макрос можно поставить в соответствие графическому объекту либо элементу управления. Для этого щелкнуть на листе правой кнопкой мыши объект, графический объект или элемент управления, которому нужно назначить существующий макрос, а затем в контекстном меню выбрать команду Назначить макрос. В поле Имя макроса выбрать макрос, который нужно назначить.Чтобы вставить элемент управления (например, кнопку) на вкладке Разработчик в разделе Элементы управления нажатькнопку Вставить.

Для выполнения макроса на вкладке Вид, область Макросы (либо вкладка Разработчик, область Код) нажать кнопку Макросы. В окне Макрос в списке найти нужный макрос, нажать кнопку Выполнить либо выполнить щелчок по элементу управления (графическому объекту). Удаление макроса производится нажатием кнопки Удалить в окне Макрос. Если макрос, который требуется удалить, хранится в личной книге макросов (Personal.xlsb), для ее отображения необходимо на вкладке Вид в группе Окно нажать кнопку Отобразить окно. В разделе Показать скрытое окнокниги выбрать пункт "PERSONAL". В окне Макрос в списке Находится в выбрать рабочую книгу с макросом, который требуется удалить.

 

Порядок выполнения работы

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

6.3.2 На ленте найти вкладку Разработчик. Если вкладка Разработчик не доступна, нажать кнопку Оffice - Параметры Excel; в группе Основные, в рубрике Основные параметры работы с Excel установите флажок Показывать вкладку «Разработчик» на ленте, а затем нажмите ОК. На вкладке Разработчик в группе Код нажать кнопку Безопасность макросов, выбрать рубрику Параметры макросов и в группе Параметры макросов установить переключатель в позицию Включить все макросы (не рекомендуется, возможен запуск опасной программы), а затем дважды нажмите кнопкуОК.

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

6.3.3 Создать макрос с именем График и комбинацией клавиш Сtrl-G, который строит круговую диаграмму распределения доходов фирм за полгода. Cкопировать таблицу на новый лист. Выполнить макрос.

6.3.4 Создать макрос с именем Налог, который добавляет в таблицу третий столбец с именем Налоги, значения которого рассчитываются по формуле: Всего за полгода*20%. Вставить на лист геометрическую фигуру с надписью Налоги и назначить ей макрос Налог. Создать макрос с именем Очистка, очищающий последний столбец таблицы. Вставить на лист элемент управления – кнопку с именем Очистка. Для этого на вкладке Разработчик в области Элементы управления нажать кнопку Вставить и выбрать нужный элемент. Назначить ей соответствующий макрос. Выполнить последовательно макросы Налог – Очистка - Налог щелчком мыши по геометрической фигуре и кнопке.

6.3.5 Создать макрос с относительными ссылками для выбора из таблицы дохода определенной фирмы.. Для выборки из таблицы информации удобно использовать функции категории «Ссылки и массивы»:

=ПРОСМОТР(искомое_значение; просматриваемый_вектор; вектор результата)

ВПР(искомое_значение; просматриваемая таблица; номер_столбца; интервальн_просмотр)

Искомое_значение - это значение, которое должно быть найдено в просматриваемом векторе или первом столбце просматриваемой таблицы, отсортированными по возрастанию. Просматриваемый_вектор (таблица) и вектор результата - диапазоны, которые задаются абсолютными адресами ($A$1:$A$10). Результат возвращается из вектора результата или столбца таблицы с заданным номером.

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

 

Содержание отчета

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

 

6.5 Контрольные вопросы

1. Как создать макрос с помощью макрорекордера?

2. Для чего используется кнопка Относительные ссылки на панели Разработчик?

3. Как сохранить книгу Excel, содержащую макросы?

4. Как выполнять макрос с помощью кнопки?

 

СПИСОК ЛИТЕРАТУРЫ

1. Безручко Т.В. Практикум по курсу " Информатика": работа в Windows, Word, Excel / В. Т. Безручко. - М.: Финансы и статистика, 2002. - 270 с.: ил.

2. Бобцов А.А. Эффективная работа с пакетом программ Мicrosoft Office. Учебно-методическое пособие./ А.А.Бобцов, Е.В.Рукуйжа. – СпбГУ ИТМО, 2008. – 129 с.

3. Информатика: базовый курс: учеб. пособие для студ. втузов / Ред. С. В. Симонович. - СПб. и др.: Питер, 2008. - 640 c.: ил.

4. Каймин В.А.Информатика: учеб./ В. А. Каймин. - 3-е изд. - М.:ИНФРА - М, 2003. - 272 с.

5. Корнелл;П. Анализ данных в Excel. Просто как дважды два /П. Корнелл; пер. с англ. – М.:Эксмо, 2007. – 224 с.: ил.

6. Рудикова Л.В. Мicrosoft Excel для студента. СПб.:БХВ-Петербург, 2005. – 368 с.: ил.