Авторское пособие 1-5 задачи. Zinas tip жирные значения те, которые должны получиться если перед ними стоит формула с равенством, то это формула из этой ячейки, которая выдаёт это значение чтобы получилась ячейка с долларами, нажать f4
 Скачать 0.87 Mb.
|
  
Zina’s tip ЖИРНЫЕ ЗНАЧЕНИЯ – ТЕ, КОТОРЫЕ ДОЛЖНЫ ПОЛУЧИТЬСЯ ЕСЛИ ПЕРЕД НИМИ СТОИТ ФОРМУЛА С РАВЕНСТВОМ, ТО ЭТО ФОРМУЛА ИЗ ЭТОЙ ЯЧЕЙКИ, КОТОРАЯ ВЫДАЁТ ЭТО ЗНАЧЕНИЕ ЧТОБЫ ПОЛУЧИЛАСЬ ЯЧЕЙКА С ДОЛЛАРАМИ, НАЖАТЬ F4
Переименовать лист в “I”
кредит=ПС (когда депозит, отрицательное число) ставка(годовая)/12=СТАВКА (месячная), так как период=месяц срок кредита(в годах)*12=КПЕР (в месяцах) месячный платёж=ПЛТ
в ПЛТ должна быть изначально формула, как указано в таблице. она выдаст -35 292,50, не пугайтесь данные-анализ что если->подбор параметра установить в ячейке $B$9 значение=-70585, изменяя значение ячейки B8 в итоге выдаст КПЕР нецелый, как в таблице, мы его округляем в бОльшую сторону (это логично, мы не можем выплачивать кредит 24,70… месяцев)
формируем таблицу: по вертикали пишем произвольные ставки в формате 0,xx; по горизонтали пишем КПЕР в формате двузначного целого числа (формат значений, по которым строится таблица (см. условие) должен совпадать с форматом значений в таблице номер один из этого задания. например, если мы строим таблицу по периодам, как здесь, они задаются двузначным числом – количеством месяцев (60), следовательно и таблица строится с подобными значениями. это нужно потому, что значения из таблицы подставляются в формулу) выделить мышкой таблицу данные->анализ что если->таблица данных подставлять значения по столбцам в $B$1, по строкам в $B$5$ (это опять же делается потому, что B1 и B5 участвуют в начальной формуле, то есть мы должны значениям из таблицы дать «образец» поведения и участия в формуле. то есть если у нас в шапках столбцов КПЕР, то подставлять по столбцам тоже нужно в КПЕР) II. Решение задачи линейного программирования (Поиск решения) Переименовать лист в “II” Витамины А, В и С, которых требуется в день 6, 8 и 2 г соответственно, содержатся в двух видах продуктов. Цена первого продукта равна 50 руб./кг, цена второго продукта — 20 руб./кг. В 1 кг первого продукта содержится 2 г витамина А, 4 г витамина В и 2 г витамина С; в 1 кг второго продукта содержится соответственно 2 и 3 г витаминов A и B. Витамин C во втором продукте не содержится. Записать математическую модель (ограничения и целевую функцию) и найти оптимальное (минимальное) по стоимости количество продуктов 1 и 2, которые нужно потреблять ежедневно для обеспечения необходимого количества витаминов.
Суть задачи сводится к минимизации общей стоимости продуктов. При этом человек всё равно должен получить необходимое количество витаминов, как минимум самое необходимое (см. условие и таблицу). Мы получаем витамины из обоих продуктов, поэтому количество получаемого витамина из каждого продукта равно витамину из каждого продукта, умноженному на количество продукта. Нужно взять сумму витаминов из каждого продукта. Общая стоимость равна сумме стоимостей каждого продукта. Стоимость каждого продукта равна произведению стоимости за штуку, умноженного на количество данные->поиск решения оптимизировать целевую функцию $B$11 до минимума в соответствием с ограничениями: $B$9>=$B$8, $C$9>=$C$8, $D$9>=$D$8 (получаем витаминов не меньше, чем необходимо) выберите метод решения: поиск решения линейных задач симплекс-методом->найти решение->ок III. Частотная таблица и гистограмма (использование функций МИН, МАКС, СЧЁТ, ОКРУГЛВВЕРХ, ЧАСТОТА и построение двухосевой диаграммы) 1. Из файла «исходные данные» скопировать лист «banks» в созданный файл с Вашим именем (удобно использовать контекстное меню для работы с листами рабочей книги). 2. Поименовать столбцы таблицы с данными листа «banks» с помощью команды Создать из выделенного. Все остальные вычисления выполнять, используя эти имена. 3. Разбить значения поля «Изменение позиции в рейтинге» на интервалы группировки, применяя для расчета количества интервалов m формулу…, где n – размер числового ряда, h – ширина интервала, xmax -максимальное значение цены, xmin – минимальное значение цены. Все вычисления должны быть выполнены с помощью функций расчета максимума, минимума и подсчета количества числовых ячеек в диапазоне, округления. 4. Рассчитать количество значений (абсолютную частоту) поля «Изменение позиции в рейтинге», попадающих в каждый интервал, с помощью функции ЧАСТОТА. Рассчитать относительную и кумулятивную частоты. 5. Отформатировать частотную таблицу, применив следующие элементы форматирования: a. центровка и перенос по словам названий столбцов, b. шрифт жирный и курсив, c. процентный формат с одним знаком после запятой для относительной и кумулятивной частот. d. границы ячеек таблицы. 4. Построить диаграмму относительно двух осей (см. рис. 67): a. показать абсолютную частоту в виде гистограммы, кумулятивную частоту – в виде графика. b. добавить подписи значений данных для каждой точки кумуляты, изменив фон подписей. Легенда разместить внизу с перекрытием. c. Диаграмму разместить на отдельном диаграммном листе, поименовав лист своей фамилией. Чтобы скопировать лист из исходного файла, необходимо одновременно открыть оба документа. Открыть лист “banks”, нажать на его название внизу листа правой кнопкой мыши, выбрать опцию «переместить или скопировать», выбрать название вашего документа, промотать все названия страниц, после чего нажать «переместить в конец» и не забыть нажать галочку о создании копии. Встать в ячейку таблицы и выделить её полностью с помощью команд CTRL+A или CTRL+SHIFT+8. Формулы->создать из выделенного->только галочка в строке выше Поименовать новый лист “III” При создании интервалов ставите равно, пишете функцию, заходите на вкладку формулы->использовать в формуле-выбираете имя столбца, например изменение позиции в рейтинге. R, m и h вычисляются по формулам из задания. R-это range(вспомните статистику, это max-min)
Границы интервалов: первая=МИН+ширина интервала h, вторая и последующие=1+h Проверить себя можно тем, что последний интвервал-максимум Чтобы рассчитать частоту, нужно сначалa!!!!!! Выделить F2-F11, ввести формулу частоты, как в таблице, и вместо ENTER нажать CTRL+SHIFT+ENTER. Должны появиться кривые скобочки. Форматирование таблицы очевидное, кроме пункта процентный формат с одним знаком после запятой для относительной и кумулятивной частот. Выделяете ячейки относительной и кумулятивной частоты и на вкладке главная в секции формата пощёлкать стрелочки с нулями туда-сюда, пока не останется 2 знака после запятой.
Выделяете необходимые для диаграммы столбцы (зажимая CTRL, протаскиваете мышкой вдоль двух столбцов)->вставка, диаграмма. На форматирование здесь тратить время не буду, и потому что у меня другая версия EXCEL, и потому что сейчас 7.30, и я не успею дописать пособие, а есть более важные вещи. Лист с диаграмой назвать фамилией IV. Группировка с использованием функций ЕСЛИ и СЧЁТЕСЛИ. 1. Разбить данные поля «Изменение позиции в рейтинге» с помощью функции ЕСЛИ на 4 группы в соответствии с описанием группы, представленным ниже. С помощью функции СЧЁТЕСЛИ рассчитать количество банков, у которых позиция в рейтинге изменилась: • Группа 1 – изменение в рейтинге более 10 позиций • Группа 2 – изменение в рейтинге в интервале от 5 позиций до 10 позиций • Группа 3 – изменения в рейтинге в интервале от 1 до 4 позиций • Группа 4 – изменения в рейтинге не было.
Записать в A1 формулу: =ЕСЛИ(ABS(banks!B5)>10;"группа 1";ЕСЛИ(ABS(banks!B5)>=5;"группа 2";ЕСЛИ(ABS(banks!B5)>=1;"группа 3";"группа 4"))) и протянуть до конца на количество ячеек в столбце «изменение позиции рейтинга» Начиная с C1, сделать следующую таблицу:
Сделать по ней диаграмму. Опять же, диаграмму разбирать тут не буду. V. Задания на списки. 1. Скопировать из файла "данные для примерного варианта" в свой файл лист "сделки" 2. Поименовать таблицу листа «сделки», назвав ее сегодняшней датой 3. При помощи функции ВПР к этой таблице справа около столбца “CompanyName” добавить колонку с датой создания фирмы (“date of establishment”). 4. Отсортировать таблицу по 4-м ключам: OrderDate по возрастанию (от старых к новым), CompanyName по алфавиту, Quantity по убыванию, Subtotal по убыванию. 5. При помощи функции базы данных определить количество сделок в 1996 году, в которых скидка была, нулевой или 25%. 6. С помощью условного форматирования выделить ячейки столбца OrderDate, удовлетворяющие этому условию желтым цветом. 7. Рассчитать среднюю цену сделки (the transaction price) среди сделок французских и немецких фирм, заключенных с августа 1996г. по март 1997г. 8. Создать новый лист, назвав его Вашим отчеством и используя вычисляемый критерий и расширенный фильтр, извлечь из исходной таблицы на новый лист строки с данными по сделкам, цена которых превышает рассчитанную в предыдущем пункте среднюю цену. 9. На листе "banks" построить нижеприведенную таблицу (рис. 69). Для этого a. При помощи обычного фильтра отобрать банки, изменение в рейтинге которых превышает 100 единиц. Скопировать названия отобранных банков и расположить их в строке (можно использовать транспонирование). b. Названия трех показателей расположить в строках в том порядке, в котором они расположены в формируемой таблице (рис. 69). c. Используя функцию ПОИСКПОЗ, найти номера строк внутри таблицы, в которых расположена информация по отобранным банкам. d. При помощи функции ГПР отобрать в таблицу заданные показатели, используя найденные номера строк с данными по отобранным банкам. Перенести лист сделки, как в исходном задании Удалить из таблицы пустые столбцы, встать в клетку таблицы, нажать CTRL+A, в левой верхней клетке рядом со строкой формулы написать сегодняшнуюю дату в формате October_30 (главное, чтобы начиналось с буквы), и ENTER Выделить стобцы HI (последние в таблице) и поименовать их в том же окошке, допустим, table Вставить пустой столбец правой кнопкой мыши нажав на Company Name В первой строчке этого столбца (F1) записать формулу =ВПР(E2;table;2;0) и протянуть до конца Затем встать в любую клетку->данные->сортировка, значок в виде таблички. Сортировка должна выглядеть как на фото  САМОЕ ГЛАВНОЕ: критерии базы данных То, что в условии дано в виде «или»(как здесь скидка), записывается в виде двух строчек. То есть если дата в 1996, то мы ее будем повторять для всех, так как дата – единственный критерий, а вот скидки 2, значит в каждой строчке мы меняем это Условие. Главное, чтобы были все возможные комбинации критериев. Даты можно было бы записать как >=01.01.1996 <=31.12.1996
Теперь пишем где-нибудь рядом =БСЧЁТ(october_24;;A1:C3) =262 Выписываем отдельно даты, потому что формула со знаками неравенства работать скорее всего не будет.
Фомула условного форматирования =ИЛИ(И(сделки!A2>V!$A$5;сделки!A2 Условнное форматирование может выдать и истину, и ложь По сути, мы скрепляем между собой условие внутри строки при помощи И, а между строками – при помощи или. Этой формулой задаем условия совпадения значений в таких же!!! столбцах начальной таблицы с этими условиями. То есть каждую ячейку той таблицы мы сраниваем с критериями здесь. Критерии закрепляем долларами (боюсь, что пока допишу пособие, доллар вырастет еще на пару центов;) То есть конкретно: Order date из таблицы больше 31 декабря 1995, меньше 1 января 1997, а Discount из таблицы равен 0, или во второй строчке такие же даты, а равенство со скидкой другое). Ссылаемся не на критерий по датам, а на скопированные отдельно даты без знаков неравенства (см. выше) Скопировать формулу со знаком равно Дальше переходим в таблицу, выделяем столбец, который нужно раскрасить, открываем условное форматирование во вкладке главное, добавляем правило. Туда вставляем формулу и выбираем желтый цвет форматирования кнопкой формат. Enter, все должно раскраситься, кол-во ячеек=тому количеству, которое вы искали через БСЧЁТ Теперь пишем еще одну такую табличку
С помощью =ДСРЗНАЧ(october_24;сделки!D1;A7:C9) = 1591 находим среднюю цену сделки !!! среди значений, удовлетворяющих критериям. Запишите где-нибудь рядом отдельное значение >1591 Создать лист, назвать его отчеством, скопируйте туда все заголовки начальной таблицы Данные->дополнительный фильтр->скопировать результат в другое место Исходный диапазон – весь лист сделок Диапазон условий клеточка >1591, который вы записали до этого Скопировать в диапазон – выделяете все заголовки сверху  Последнюю часть не успеваю Вот просто формулы для номеров строк и ГПР =ПОИСКПОЗ(B875:D875;C4:C614;0) =ГПР($A876;$A$4:$H$614;B$874;ЛОЖЬ) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||