присвоение имен диапазонам данных

ЛАБОРАТОРНЫЕ РАБОТЫ ПО MS EXCEL 2007

Лабораторная работа №1. 1

Лабораторная работа № 2. 3

Лабораторная работа № 3. 4

Лабораторная работа № 4. 7

Лабораторная работа № 5. 8

Лабораторная работа № 6. 10

Лабораторная работа №1.

Создание простейшей таблицы. Ввод и редактирование данных.

Копирование и перемещение данных

1. Создайте и заполните таблицу, приведенную на рисунке 17. (В ячейке С1 надпись «Дата рождения» необходимо написать в двух строках, для этого введите Дата, нажмите Alt + Enter и введите рождения. Нажмите Enter).

 

 

Рис. 17. Таблица

 

2. Добавьте столбец «Год поступления» между столбцами «Дата рождения» и «Оценка». Значения пустых столбцов задать самостоятельно.

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

4. Отредактируйте текст заголовка таблицы, заменив слово «группы №» на «факультета» (Вход в режим редактирования — двойной щелчок мыши по ячейке или F2 или через строку формул).

5. Удалите содержимое столбца «Год поступления» из таблицы (нажмите на кнопку Очистить на закладке Главная группы Редактирование). Разберитесь, что очищают остальные пункты данной кнопки: Все, Форматы, Примечания.

6. Восстановите содержимое столбца, отменив предыдущую операцию.

7. Проведите сортировку в данной таблице по столбцу с фамилиями в алфавитном порядке.

8. Установите для данной таблицы фильтр. Отобразите только тех студентов, которые родились в марте и июне (или других, имеющихся в вашей таблице, месяцах). Отмените фильтрацию.

9. Отобразите, с помощью автофильтра, только тех студентов, которые имеют оценку выше 4 баллов.

10. Перейдите на чистый лист. Используя автозаполнение, пронумеруйте ячейки столбца А от 0 до 100 с шагом 5, для этого в ячейку A1 введите значение «0», в ячейку A2 — «5» ® Выделить обе ячейки ®Пользуясь маркером заполнения, протянуть выделение до ячейки A21).

11. Начиная с адреса ячейки В1, введите названия всех месяцев года, используя встроенный список для автозаполнения.

12. Создайте список цветов, включив в него 6 элементов (Кнопка Office ® Параметры Excel ® Основные ® Изменить списки… ® Ввести элементы списка ® OK). Заполните значениями этого списка столбец и строку, начиная с ячейки D2.

13. Скопируйте таблицу тремя способами на различные листы:

a) используя мышь – поместите мышь на границу выделенного фрагмента, указатель примет вид крестообразной стрелки. Нажмите клавиши Alt + Ctrl и, не отпуская их, перетащите указатель мыши с помощью левой (или правой) кнопки на ярлычок того рабочего листа, на который следует скопировать фрагмент;

b) используя контекстное меню;

c) используя опцию «Специальная вставка» - скопируйте таблицу ® на закладке Главная в группе Буфер обмена выберите команду Специальная вставка ® в открывшемся окне щелкнуть по кнопке Вставить связь.

14. Сохраните рабочую книгу под именем «Книга1».

Лабораторная работа № 2

Защита данных в MS Excel

1. Откройте рабочую книгу «Книга1».

2. Включите защиту листа «Лист1», установив пароль для доступа. Ввод

разрешить только в столбец «Оценка» (Выделить столбец «Оценка» ® через кон-

текстное меню Формат ячейки…®на закладке Защита® Сбросить флажок «За-

щищаемая ячейка» ® OK, т.е. на столбец «Оценка» не установлена защита.

Далее необходимо установить защиту на весь лист, для этого на вкладке Ре-

цензирование, в группе Изменения выбрать опцию Защитить лист® OK).

3. Защитите файл рабочей книги с помощью пароля (Файл® Сохранить

как…® Сервис® Общие параметры® Задать пароль для открытия файла® OK

® Подтвердить пароль® OK).

4. Закройте рабочую книгу.

5. Откройте повторно файл рабочей книги. Снимите защиту с «Лист1»,

снимите защиту с рабочей книги.

6. Сохраните рабочую книгу под именем «Книга2».

Лабораторная работа № 3

Формулы и функции

Задача 3.1.Создайте таблицу для расчета стоимости проката товара (см. рис. 18).

 

 

 

Рис. 18 Таблица расчета стоимости проката товара

 

 

Задача 3.2.Пользуясь расписанием движения поездов (см. рис. 19), рассчитайте

продолжительность поездки на каждом составе.

 

 

 

Задача 3.3.Заполните таблицу (см. рис. 20), используя функции СЧЕТЕСЛИ и

СЧЕТЗ

Рис. 20. Таблица оценок

 

Задача 3.4. Используя функцию ЕСЛИ заполнить столбец G: если затраты превышают 35000$, то предоставляется скидка 5%, в противном случае – скидки нет. Используя функцию СРЗНАЧЕСЛИ в ячейке С16, посчитать среднюю стоимость посылки. Используя функцию СРЗНАЧЕСЛИМН в ячейке Е16, посчитать среднюю стоимость международных писем (см. рис. 21).

 

Используя функцию СРЗНАЧЕСЛИ в ячейке С16, посчитать среднюю стоимость посылки. Используя функцию СРЗНАЧЕСЛИМН в ячейке Е16, посчитать среднюю стоимость международных писем (см. рис. 21).

 

 

Рис. 21. Таблица расчетов

 

Проверка вводимых данных, функция «Форматировать как таблицу»,

присвоение имен диапазонам данных.

Функция «Формировать как таблицу», которая располагается на вкладке Главная, группа – Стили, позволяет быстро форматировать диапазон ячеек и преобразовать его в таблицу с помощью выбора определенного стиля таблицы. При этом Excel автоматически маркирует заголовки столбцов и создает автофильтр. После этого в группе контекстных инструментов Работа с таблицамиотобразится вкладка Конструктор, в которой находятся инструменты для работы с таблицей.

 

Опция «Проверка данных», которая располагается на вкладке Данные, группы Работа с данными, позволяет защитить ячейку от ввода неверных данных. Например, можно запретить ввод чисел превышающих 500. Можно также разрешитьввод значений только из предварительно определенного раскрывающегося списка.Для этого необходимо вызвать опцию «Проверка данных», в поле Тип данныхвыбрать Список, в появившемся поле Источникуказать диапазон ячеек со списком. Если диапазон со списком находится на другом листе, то ему предварительно необходимо задать имя (выделяется диапазон и в поле Имяуказывается имя). Тогда в поле Источник____________указывается имя диапазона.

 

Задача 3.5. С помощью инструмента «Проверка вводимых значений» и функции ВПР составить удобное средство поиска суммы по статье расхода (см. рис. 22). То есть, в ячейке А2 должен быть список, из которого выбирается статья расходов, после чего в ячейке В2 появляется соответствующая сумма расходов.

 

Рис. 22. Таблица расходов

Задача 3.6. Необходимо заполнить столбец С (Город) (см. рис. 23), используя список названий городов (список придумать самостоятельно). Таблица расположена в книге на «Лист1», список названий городов – на «Лист2». Список названий городов необходимо будет сформировать как таблицу, используя соответствующую функцию.

 

 

Рис. 23. Таблица с вложенным списком

 

Сохраните рабочую книгу под именем «Книга3».

 

 

Задача 3.7. Необходимо заполнить столбец С (Город) (см. рис. 23), используя

список названий городов (список придумать самостоятельно). Таблица расположена в книге на «Лист1», список названий городов – на «Лист2». Список названий городов необходимо будет сформировать как таблицу, используя соответствующую функцию.

 

Сохранить рабочую книгу под именем «Книга3».

 

Лабораторная работа № 4

Построение графиков и диаграмм

Задача 4.1.По имеющимся данным: «за» — 7 чел., «против» — 11 чел., «воздержалось» — 2 чел., постройте круговую объемную диаграмму, отражающую процентное соотношение результатов голосования. Цвета секторов — красный, синий и зеленый соответственно. Для этого в диапазоне ячеек А1:В3 ввести данные по голосованию, выделить этот диапазон и во вкладке Вставка, группы Диаграммывыбрать соответствующую диаграмму. Обратите внимание на то, что, когда выделена диаграмма в Ленте добавляется раздел Работа с диаграммами,в котором три вкладки: Конструктор, Макет и Формат. Посмотрите эти вкладки самостоятельно.

 

Задача 4.2.По данным таблицы (см. рис. 24) постройте различные типы диаграмм, отображающих:

¾ долю каждого энергоносителя в потреблении 1990 года (для этого необходимо одновременно выделить колонку с энергоносителями и колонку за 1990 г., далее вызвать мастера диаграмм);

¾ динамику изменения потребления нефти за период с 1965 по 1990 годы (для этого необходимо выделить строку с нефтью за весь период).

 

 

Рис. 24. Таблица потребления угля, нефти и газа

Лабораторная работа № 5

Составление отчетов

Задача 5.1.В таблице (см. рис. 27) представлены сведения о трех акционерах фирмы (значение в столбце «Общая стоимость» определите по формуле).

 

Рис. 27. Сведения об акционерах фирмы

 

Необходимо получить на листе не используя формулы:

1) Общее количество акций и их общую стоимость для каждого акционера.

Для этого установите курсор в пределах таблицы и вставьте сводную таблицу (вкладка Вставитьгруппа Таблица). Откроется новый лист с дополнительным окном (Список полей сводной таблицы), в нем необходимо пометить поля: «ФИО», «кол-во», «общая стоимость». Должна появиться следующая таблица (см. рис. 28):

 

Рис. 28. Сводная таблица

2) среднее значение количества акций у каждого акционера;

3) общее количество акций каждого выпуска и их общую стоимость;

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

 

Задача 5.2. Проведите консолидацию данных трех рабочих листов «Булочная №1», «Булочная №2», «Кофейня». Разместите результат на листе «Консолидация». В качестве данных для рабочих листов использовать ниже приведенные таблицы соответственно (см. рис. 29, рис. 30, рис. 31).

Рис. 29. Реализация хлебобулочных изделий в булочной № 1

 

Рис. 30. Реализация хлебобулочных изделий в булочной № 2

Рис. 31. Реализация хлебобулочных изделий в кофейне

 

На листе «Консолидация» установите курсор в ячейку А1® вкладка Данные, группа Работа с данными® кнопка Консолидация.В открывшимся окне установите параметры: Функция: Сумма; установить флажки «Использовать в качестве имен подписи верхней строки и значения левого столбца».

Установите курсор мыши в текстовом поле «Ссылка:» ® Перейдите на лист источник данных «Булочная №1» ® Выделите на нем область данных для консолидации (всю таблицу без строки Итого)® нажмите кнопку Добавить® Повторите эту процедуру для всех областей консолидации® OK.

 

Сохраните рабочую книгу под именем «Книга9».

 

Лабораторная работа № 6

Поиск решения, подбор параметра

Задача 6.1.В приведенной таблице (см. рис. 32), используя инструмент «Подбор параметра» (вкладка Данные, группа Работа с данными, кнопка Анализ «что – если») определить:

1) насколько нужно снизить транспортные расходы, чтобы они составили не более 40% от общих расходов компании;

2) насколько нужно снизить стоимость работ, чтобы она составляла не более 20% от общих затрат.

 

Рис. 32. Таблица затрат компании

 

Задачи на оптимизацию, решаемые с помощью надстройки «Поиск решения»

Обычными задачами, решаемыми с помощью надстройки «Поиск решения» являются:

1) ассортимент продукции. Максимизация выпуска товара при ограничениях на сырье (или других ресурсов) для производства изделий;

2) планирование перевозок. Минимизация затрат на транспортировку;

3) оптимизация финансовых показателей (например, максимизация доходов за счет оптимизации средств на разные инвестиционные проекты);

4) и др.

 

Задачи, которые лучше всего решаются данным средством, имеют три свойства:

1) имеется единственная минимизируемая или максимизируемая цель;

2) имеются ограничения, выражаемые, как правило, в виде неравенств;

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

 

Задача 6.2.Из приведенной таблицы (см. рис. 33), используя опцию «Поиск решения», определить количество товара разных наименований, так чтобы количество каждого товара было не менее 100 шт. и при общих издержках (т.е. сумме по Суммарным издержкам) не более 1 000 000 р. Прибыль предприятия была максимальной. Столбцы D, F, G должны быть заполнены с помощью формул. Рис. 33.

 

Рис. 33. Исходная таблица

 

Опция «Поиск решения» находится на вкладке Данныегруппы Анализ, если этот компонент не установлен, то необходимо зайти Параметры Excel(через кнопку Office), выбрать в пункте Надстройкикоманду «Поиск решений». Перед началом решения задачи необходимо определить, какая ячейка будет целевой, какие ячейки будут изменяться, и на какие ячейки будут накладываться условия.

 

В данной задаче ячейка F8 будет целевой, так как нам необходимо максимизировать прибыль предприятия.

 

Ячейки С2:С7 могут изменяться. А условия необходимо наложить, во-первых, на ячейки С2:С7, они должны быть все не менее 100, во-вторых, числа в этих же ячейках должны быть все целые, в-третьих, ячейка D8 должна быть меньше 1 000 000. Таким образом, окно поиска решения должно быть заполнено следующим образом (см. рис. 34):

 

 

 

Рис. 34. Диалоговое окно «Поиск решения»

 

Задача 6.3.В приведенной таблице (см. рис. 35), используя опцию «Поиск реше-

ния», определить оптимальное размещение рекламных объявлений с учетом следующих ограничений:

1) необходимо обеспечить максимальную читательскую аудитории для объ-

явлений;

2) нужно заказать как минимум 8 объявлений в трех журналах и хотя бы 10 в

четвертом;

3) в одном журнале не более 20 объявлений;

4) аудитория не менее 10 млн. человек;

5) бюджет рекламной компании составляет 3 млн.р.

 

Рис. 35. Бюджет рекламной компании