Лабораторная работа 3
Тема: Изучение электронных таблиц MS Excel.
Цель: получить практические навыки использования базовых и некоторых дополнительных возможностей MS Excel.
Задание 1
Создать файл MS Excel, содержащий следующую таблицу
Продукция |
Отпускная цена, тыс. руб. |
Объем реализации, шт. |
Выручка от реализации, тыс. руб. |
Ставка НДС, % |
Сумма НДС, тыс. руб. |
Выручка от реализации, с учетом НДС, тыс. руб. |
Брошь «Фармазон» |
999 |
3 |
|
18% |
|
|
Коньяк «Три поросенка» |
5 |
50 |
|
|
|
|
Детский конструктор «Нанотехнологии» |
50 |
30 |
|
|
|
|
Маскарадный костюм «КГ/AM» |
300 |
20 |
|
|
|
|
Медвед плюшевый |
70 |
25 |
|
|
|
|
Итого: |
|
|
|
|
|
Не заполненные ячейки таблицы должны содержать расчетные формулы. Формула задается для одной ячейки и в последующие ячейки заносится копированием. При расчете суммы НДС и ссылке на размер ставки НДС необходимо использовать абсолютные ссылки ($). Отобразить зависимости формул (влияющие и зависимые ячейки).
Построить круговую диаграмму по структуре реализации продукции в натуральном выражении и на отдельном листе столбиковую диаграмму по выручке от реализации продукции. Диаграммы должны иметь следующий вид:
После создания таблицы сделать копию созданного листа и переименовать новый лист, присвоив ему название «Резервный вариант».Скрыть лист «Резервный вариант», а затем вновь его отобразить. Скрыть на листе «Резервный вариант» столбец «Ставка НДС, %», а затем вновь его отобразить. Преобразовать таблицу на листе «Резервный вариант» таким образом, чтобы она содержала только результаты расчетов с точностью 2 знака после запятой, но не содержала формул расчета. Защитить от изменения ячейки преобразованной таблицы.
Теоретический материал для выполнения задания:
Формула в Excel должна начинаться со знака равенства (=), за которым следует набор вычисляемых величин и ссылки на ячейки в виде буквы (столбец) и цифры (строка), например: B3 соответствует второму столбцу (B) и третьей строке. Ссылка на непрерывный диапазон ячеек определятся адресами верхней левой и нижней правой ячеек, которые записываются через двоеточие, например: A2:C5. Ссылку на ячейку можно ввести с клавиатуры, но гораздо быстрее при написании формулы просто щелкнуть левой кнопкой мыши по нужной ячейке или выделить диапазон, удерживая левую кнопку мыши. При копировании ячеек в формулах автоматически изменяются ссылки на ячейки, сохраняя «структуру» вычислений. Для запрета автоматического изменения в формуле ссылки на ячейку, её надо указывать со знаком $ перед буквой столбца и/или номером строки (например, A$1, $A1, $A$1 блокирует изменение «по строкам», «по столбцам», «по строкам и по столбцам»). При двойном щелчке по ячейке формула открывается для редактирования, отображая цветом, участвующие в ней ячейки. Зависимости в формуле можно отразить и при помощи команды меню Сервис\Зависимости формул. Результаты расчетов можно скопировать, исключив используемые для расчета формулы и возможность дальнейшего перерасчета при изменении данных: выделить данные и скопировать их, затем воспользоваться меню Правка\Специальная вставка\Значения.
Построение диаграммы: выделить данные вместе с подписями к ним (удерживая клавишу Ctrl можно выделить не смежные ячейки), меню Вставка\Диаграмма следовать указаниям диалогового окна. После построения диаграмма и отдельные её элементы могут редактироваться при помощи двойного щелчка мыши по отдельным её элементам или вызову контекстного меню на этих элементах.
Копирование листа осуществляется при помощи меню Правка\Переместить-Скопировать лист или просто перетянув ярлычок листа кнопкой мыши, удерживая клавишу Ctrl. Переименование листа осуществляется двойным щелчком по его ярлыку или командой меню Формат\Лист\Переименовать. Команды меню Формат\Лист также позволяют скрыть лист или отобразить скрытый лист. Аналогично команды меню Формат\Строка и Формат\Столбец позволяют скрыть/отобразить отдельные столбцы и строки.
Для защиты ячеек от изменения или сокрытия формул в этих ячейках необходимо выделить ячейки, в контекстном меню Формат ячеек\Защита установить флажки в позициях «Защищаемая ячейка» и «Скрыть формулы», после чего воспользоваться командой меню Сервис\Защита\Защитить лист. Можно также защитить книгу в целом (структуру и окна) или установить пароль на открытие/сохранение книги – Сервис/Параметры/Безопасность.
Задание 2
Создать на листе Excel таблицу, аналогичную созданной в задании 1 лабораторной работы 2.
Предприятие |
Ф.И.О. директора |
Основная продукция |
Район подчинения |
Р/с июль |
Р/с август |
ООО "Атари" |
Иванов С.И. |
Компьютеры |
Советский |
700800000 |
529000000 |
ЗАО "Сега" |
Геворкян П.И. |
Цветные металлы |
Фрунзенский |
1523000000 |
2987000000 |
ООО "Бездна" |
Небобер А.И. |
Канцтовары |
Фрунзенский |
0 |
0 |
АО "Без гроша" |
Бывший И.И. |
Ксерокопия |
Фрунзенский |
0 |
0 |
ООО "Сит" |
Ситников К.Г. |
С/х продукция |
Советский |
351000000 |
217000000 |
НПО "Белмед" |
Китайцев Н.А. |
Медпрепараты |
Советский |
400000000 |
900000000 |
ООО "Контур" |
Говорков А.М. |
Радиоприборы |
Фрунзенский |
30000000 |
120000000 |
ООО "Нотрдам" |
Сазонов И.А. |
Медицинская техника |
Фрунзенский |
78000000 |
250000000 |
Выполнить следующие задания на основании указанной таблицы
Задание |
Указания к выполнению |
Сортировка данных Отсортировать на листе Excel таблицу, сначала по столбцу “Район подчинения”, затем по столбцу “Основная продукция”, затем по столбцу “Предприятие”. |
Выделить сортируемые данные, Данные\Сортировка, Диалог: “Сортировать по.. “ столбец А по возрастанию, “Затем по “, “В последнюю очередь по..” |
Автофильтр Наложить на таблицу автофильтр. Отобразить только предприятия, относящиеся к Фрунзенскому району и продукция которых начинается на “Мед”. |
Выделить фильтруемые данные или установить курсор на одну из ячеек данных, Данные\Фильтр\Автофильтр. Для снятия автофильтра выполните команду еще раз. |
Задание |
Указания к выполнению |
|||||||||||||||||
Расширенный фильтр Создать расширенный фильтр со следующими условиями: отобрать только ООО (Общество с Ограниченной Ответственностью), советского района у которых на расчетном счету в среднем за два месяца больше 200 млн. или любые предприятия с продукцией, начинающейся на “Мед*”. Отфильтровать данные с помощью расширенного фильтра на месте, снять фильтр, и заново отфильтровать, поместив результат фильтрации в новое место.
|
Пример создания расширенного фильтра: предположим, что столбцы таблицы имеют заголовки “Товар”, “Продавец”, “Продажи”. Создайте условие отбора в виде таблицы Excel, отделенной от данных по крайней мере одной пустой строкой.
Условия в одной строке - AND, условия в разных строках - OR. Если у столбца условия нет заголовка (см. 4 столбец), то в условии фильтрации можно использовать формулы. В примере выводятся только те строки, у которых в столбце G содержится число, которое умноженное на 2 будет >= среднего значения, содержащегося в ячейках с E5 по E14. Используемая в условии формула должна ссылаться либо на заголовок столбца (например, «Продажи» - G1), либо на соответствующее поле в первой записи (G2). При использовании заголовка столбца в формуле, в ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, она не повлияет на результаты фильтрации. После создания таблицы условий для расширенного фильтра необходимо использовать меню Данные\Фильтр\Расширенный фильтр - указать исходный диапазон и диапазон условий, фильтровать на месте или поместить результаты фильтрации в новое место. Команда Данные\Фильтр\Отобразить все - снимает фильтр. |
|||||||||||||||||
Использование имен Создайте имя для ячейки на пересечении столбца “Р/с июль” и строки “ЗАО Сега”. Используя созданное имя, создайте формулу, рассчитывающую 10% от суммы на р/с ЗАО Сега в Июле. Присвойте имя области, где записаны суммы, находящиеся на р/с предприятий, и создайте формулу, с использованием этого имени, возвращающую среднее значение всех сумм (функция СРЗНАЧ). |
Выделить ячейку или диапазон, Вставка\Имя\ Присвоить - указать имя. Выделить диапазон где в левом столбце - имена, в правом столбце - ячейки на которые создаются имена, Вставка\Имя\ Создать - “Создать имена по тексту в столбце слева” - создано сразу несколько имен. Вставка\Имя\Вставить - вставка имен в формулы и ячейки.
|
|||||||||||||||||
Проверка вводимых значений Установите ограничение на ввод только целых неотрицательных числовых значения в ячейки столбцов Р/с июль и Р/с август. При установке курсора на ячейку выдать сообщение: Заголовок - “Ввод данных по р/с”, Текст сообщения - “Вводите целые, неотрицательные числа”. При ошибочном вводе в ячейку выдать сообщение: Заголовок - “Ошибка”, Текст сообщения - “Данные не соответсвуют ограничениям”. Для столбца “Р/с июль” запретить ввод ошибочных данных”, для столбца “Р/с август” - разрешить ввод ошибочных значений после подтверждения. |
Выделить ячейку или диапазон - Данные\Проверка - Диалог: Вкладка “Параметры” - указать тип данных (целое, дата, текст определенной длины и т.д.), диаапазон изменения (не больше чем, в пределах от ... и до ...), Вкладки “Сообщение для ввода” и “Сообщение об ошибке”.
|
|||||||||||||||||
Условное форматирование Установить следующий формат для ячеек: - если на Р/с в каком либо из месяцев больше 100 млн. и не равно 400 млн., то число отображается синим полужирным курсивом. - если в июле (августе) среднее значение (функция СРЗНАЧ) сумм на р/с предприятий меньше 100 млн. , то название “Р/с июль” (“Р/с август”) отображается красным полужирным курсивом. - название предприятия выделяется красным курсивом, если в июле и августе Р/с = 0 (установите форматирование для одного предприятия, уберите из формулы условного форматирования знаки доллара, и скопируйте только формат этой ячейки во все другие, через специальную вставку) |
Выделить ячейку или диапазон - Формат\Условное форматирование - указать условия (>= ячейка или число, в диапазоне от .. по ... и т.д.) и формат ячейки при выполнении этого условия. Вместо условия можно использовать формулу вида =G1*300<>A5/100. Указанное форматирование присваивается ячейке при истинности формулы. Кнопка “А также” - указать дополнительные условия.
|
|||||||||||||||||
Задание |
Указания к выполнению |
Сценарии По столбцам “Р/с июль”, “Р/с август” определить три сценария - 1) исходные значения, 2)все нули, 3)”Р/с июль” - текущие значения, “Р/с август” - нули”. Загрузить каждый из сценариев, переопределить сценарий 1, добавив в него столбец “Предприятие”. |
Сценарии - сохраненные наборы данных, занесенные в ячейки. Загрузить сценарий - значит считать в ячейки ранее сохраненный набор данных. Сервис\ Сценарии - Диалог: кнопка “Добавить” - указать название сценария и диапазон входящих в него ячеек - “ОК” - ввести новые значения для ячеек сценария или оставить старые. Кнопки “Вывести” - загрузить сценарий, “Удалить” - удалить сценарий, “Изменить” - изменить диапазон ячеек сценария или их значения. |
Использование форм для поиска записей в списке Использовать таблицу созданную в задании 3 лабораторной работы 1. При помощи формы просмотреть и изменить записи для всех предприятий фрунзенского района у которых на р\с в июле < 400 млн.
|
Выделите данные, Данные\Форма – появится диалог: выделенные ячейки представляются в виде записей (как в базе данных), где для названия полей использованы заголовки столбцов, имеются полосы прокрутки, кнопки “Добавить”, “Удалить” (запись), “Вернуть” (отменить изменения внесенные в запись), “Назад”, “Далее” (переход между записями), “Критерии”, “Закрыть”. Чтобы задать условия поиска, нажмите кнопку Критерии. Введите критерии в форме(>=, <> , Ив?н*). Чтобы найти совпадающие с критериями записи, нажмите кнопки Далее или Назад. Чтобы вернуться к правке формы, нажмите кнопку Правка. |
Подбор параметра Создать произвольную сложную систему формул: ввести формулу1 аргументами которой являются ячейки в которых в свою очередь введены формулы2, ссылающиеся на другие ячейки, в которые введены формулы3 и т.д. Последний уровень формул ссылается на ячейки с данными. Изменяя исходные значения в любой из ячеек данных добиться того, чтобы в ячейке формулы1 было получено какое-либо заранее заданное Вами значение. Подгонку данных производить не вручную, а автоматически. |
Сервис\Подбор параметра Диалог: “Установить в ячейке” (ячейка с формулой1), “Значение” (любое заданное Вами значение), “Изменяя ячейки” (ячейка с исходными данными).
|
Использование гиперссылок Создать на листе 1 гиперссылку на ячейку D10 листа2. Определить любое имя. Создать гиперссылку на имя. |
Вставка \ Гиперссылка -> “Связать с файлом / URL” , “Имя объекта в документе” (лист и ячейка или имя).
|
Использование внешних данных Microsoft Access в файлах Microsoft Excel Создать таблицу Access, аналогичную таблице, указанной в начале задания 2 (Предприятия, ФИО и др.) Вставить данные из таблицы Access в документ Excel, при помощи запроса к базе данных, отобрав только поля "Предприятие", "ФИО директора", "Р/с август" и “Район подчинения” только по тем записям, которые относятся к Фрунзенскому району, отсортировав их по возрастанию по полю "Р/с август". Сохранить на диске созданный запрос к базе данных (кнопка “Сохранить запрос” на самом последнем шаге диалога) и воспользоваться сохраненным запросом в другом файле Excel.
|
Данные\Импорт внешних данных\Создать запрос, выбрать тип источника данных (файлы Microsoft Access или Microsoft Excel) и указать имя файла, выбрать поля для запроса и условия при которых отбираются поля (до 3 условий), указать порядок сортировки (сначала по полю 1, затем ... до 3 полей) или отсутствие сортировки, указать “Вернуть данные в Microsoft Excel”, указать куда надо помещать результат запроса (новый или текущий лист, сводная таблица). После создания запроса его можно изменить КМ: “Изменить запрос”, обновить данные КМ: “Обновить данные” или установить флажок “Обновление при открытии файла” в “Свойствах” запроса. Сохраненным на диске запросом можно потом воспользоваться в другом файле Данные\ Внешние данные\ Выполнить запрос. |
Задание 3
3.1. Таблицы подстановки
Пусть взят кредит сроком на один год, проценты по кредиту выплачиваются по окончании года. Используя таблицу подстановки, определить сумму выплат процентов при всех приведенных ниже комбинациях сумм кредита и годовых процентных ставок по нему. Суммы кредита: 100 000, 300 000, 500 000, 700 000. Процентные ставки: 5%, 10%, 15%, 20%, 25%
Теоретический материал для выполнения задания:
Таблица подстановки позволяет проводить многовариантные расчеты, подставляя в формулу различные комбинации исходных параметров. Формула записывается в левом верхнем углу таблицы и должна ссылаться на 1 или 2 ячейки вне таблицы. При переборе различных комбинаций параметров, данные в эти ячейки подставляются из заголовков столбцов и строк таблицы. Результаты расчетов помещаются на пересечении столбцов и строк. Пример таблицы подстановки с двумя переменными
Ячейка_столб |
|
Ячейка_стр |
Формула (ячейка_столб+ячейка_стр) |
Данные_столб1 |
Данные_столб2 |
Данные_столб3 |
Данные_стр1 |
Результат |
||
Данные_стр2 |
подстановки |
||
*Формула (ячейка_столб+ячейка_стр) должны ссылаться на ячейки вне таблицы, см. выше.
Пример таблицы подстановки с одной переменной
Ячейка_стр |
|
Формула 1 (ячейка_стр) |
Формула 2 (ячейка_стр) |
Формула 3 (ячейка_стр) |
Данные_стр1 |
Результат |
||
Данные_стр2 |
подстановки |
||
Набрать таблицу подстановки, выделить ее, Данные\Таблица подстановки -> Диалог: “Подставлять значения по столбцам в <ячейку>”, ““Подставлять значения по строкам в <ячейку>”
3.2. Поиск решения
Решить следующую оптимизационную задачу: Максимизировать прибыль Z от выпуска разных видов продукции X1…X4, в условиях ограниченной пропускной способности оборудования.
Целевая функция: Z = 30*X1 + 40 *X2 + 50 *X3 + 20 *X4 --> max
Ограничения на целевую функцию:
2*X1 + 3,5*X2 + 5*X3 + 1*X4 1800
1*X1 + 2,5*X2 + 0,3*X3 + 0,7*X4 1700
3,7*X1 + 0,4*X2 + 0,9*X3 + 2,3*X4 2100
Ограничения по неотрицательности и целочисленности: X1 .. X4 - целое, больше 0.
Ответ: X1 = 0, X2 = 266, X3 = 1, X4 = 864.
Теоретический материал для выполнения задания:
1) создать на листе Excel модель оптимизации:
Объемы выпуска:
X1 |
X2 |
X3 |
X4 |
Целевая функция:
=30*X1 + 40 *X2 + 50 *X3 + 20 *X4 |
Ограничения на целевую функцию:
=2*X1 + 3,5*X2 + 5*X3 + 1*X4 |
1800 |
=1*X1 + 2,5*X2 + 0,3*X3 + 0,7*X4 |
1700 |
=3,7*X1 + 0,4*X2 + 0,9*X3 + 2,3*X4 |
2100 |
2) Сервис \ Надстройки \ Поиск решения - подключить дополнение.
3) Сервис \ Поиск решения -> Диалог: “Установить целевую ячейку равной” (min, max, 0 или другое значение), “Изменяя ячейки”, “Ограничения” (кнопки “Добавить”, “Изменить”, “Удалить”) -> ячейка1 ячейка 2, ячейки X1:X4 - целое, ячейки X1:X4 > 0.
3.3. Статистический анализ (гистограмма, генерация случайных чисел)
Значение |
Вероятность |
10 |
0,2 |
50 |
0,3 |
100 |
0,4 |
200 |
0,1 |
На основании сгенерированных случайных чисел выполнить статистический анализ в виде построения гистограммы с интервалами значений (карманами): 10, 50, 100, 200. Вывести график гистограммы.
Теоретический материал для выполнения задания:
Генерация случайных чисел
Сервис \ Надстройки \ Пакет анализа - подключить дополнение.
Сервис \ Анализ данных \ Генерация случайных чисел - Диалог: указать число переменных (число столбцов) и число случайных чисел (число строк в выходном диапазоне). Указать вид распределения (дискретное, нормальное, равномерное и т.д.), указать параметры распределения (для дискретного предварительно создать на листе 2 - столбца: 1 - со значениями 2 - с вероятностями появления этих значений и указать их в качестве входного интервала значений и вероятностей; для нормального - среднее и стандартное отклонение; для равномерного - интервал между числом и числом). Указать выходной интервал (на текущем листе, на другом листе или в новой книге).
или
Если необходимо только равномерное распределение можно воспользоваться стандартными функциями Excel. Чтобы в результате выполнения на листе вычислений вернуть равномерно распределенное случайное число, большее или равное 0 и меньшее 1, используйте функцию СЛЧИС(). Чтобы вернуть случайное число, лежащее между произвольными заданными значениями, используйте функцию СЛУЧМЕЖДУ().
Построение гистограммы
Сервис \ Надстройки \ Пакет анализа - подключить дополнение.
На листе Excel создать входной интервал - т.е. ввести в ячейки данные, на основании которых будет построена гистограмма (любая прямоугольная область, в т.ч. сгенерированные ранее случайные числа).
На листе Excel создать интервал карманов - т.е. столбец, в ячейках которого будут заданы интервалы, на основании которых будет построена гистограмма.
Сервис \ Анализ данных \ Гистограмма - указать входной интервал и интервал карманов, указать куда выводить результаты (на тот же лист, на новый лист, или в новую книгу), указать надо ли строить график гистограммы (если необходимо строить график, то результаты надо выводить в новую книгу).
Пример: Пусть входной интервал: 0, 5, 250, 150, 17, 95 и интервал карманов: 10, 100, 200
Карман |
Частота |
10 |
2 |
100 |
2 |
200 |
1 |
Еще |
1 |