Практическая работа Excel

Задание 1
Указания по выполнению работы
Предварительно следует изучить теоретический материал (по предложенным на карте сайта ссылкам на инф. ресурсы по MS Excel, по учебнику, по методичкам, по конспекту), затем выполнять последовательно пункты задания.
Замечание. По просьбам студентов к отдельным пунктам добавлены небольшие методические указания по выполнению заданий. При этом имена собственные (названия меню, команд и вкладок), а также текстовые и числовые значения, устанавливаемые в качестве параметров, заключаются в кавычки и (или) выделяются полужирным начертанием.
1. Установка параметров приложения Excel
Цель: закрепить навыки по установке параметров работы приложения (меню «Сервис»)
Методические замечания
До установки адреса рабочей папки необходимо создать ее на своем рабочем диске. Те флажки, которые уже установлены (или сняты) и не упоминаются в настоящем задании изменять не следует.
Задания:
1. Запустить приложение Excel.
2. На вкладке Параметры–Общие установить: Шрифт=«Arial Cyr», Размер=«10», указать адрес рабочей папки в текстовом поле «рабочий каталог», ФИО пользователя, проконтролировать отсутствие флажка «Стиль ссылок R1C1».
3. На вкладке «Вид» установить: отображать – строку формул, строку состояния; примечания – только индикатор; объекты – отображать; параметры окна – сетка, заголовки строк и столбцов, нулевые значения, полосы прокрутки (горизонтальная и вертикальная), ярлычки листов; цвет линий сетки – синий.
4. На вкладке «Правка» установить: параметры – «правка прямо в ячейке», «перетаскивание ячеек», «предупреждать перед перезаписью ячеек», «переход к другой ячейке после ввода» в направлении «вниз», «автозавершение значений ячеек», «автоматический ввод процентов».
2. Настройка панелей инструментов
Цель: закрепить навыки по настройке и созданию панелей инструментов («Сервис – Настройка»)
Методические замечания
Вызов команды "настройка" можно выполнить также из контекстного меню, щелкнув в области стандартных панелей инструментов приложения. В процессе выполнения задания стараться запоминать расположение вкладок, названия категорий и кнопок.
Задания:
1. На панель «Стандартная» добавить кнопки (команды) из области "Команды": «Влияющие ячейки», «Убрать все стрелки» (категория «Сервис»).
2. Вывести на панель «Форматирование» команды (вкладка "Команды"): Вставить формат, Вставить значения, Прогрессия (категория «Правка»).
3. Создать (вкладка "Панели иснтрументов") собственную панель инструментов (под своим ФИО, напр. «Ivan»), на которую вывести команды (вкладка "Команды"): «Лист» и «Вставить» (категория "Вставка"), «Закрепить области» (категория "Окна и справка"), «Имя» (категория «Встроенные меню»), а также выполнить перенос сюда всех ранее добавленных кнопок с панелей «Стандартная» и «Форматирование». В дальнейшем при работе с именами, ссылками, форматированием использовать эту подготовленную панель с целью повышения удобства и ускорения работы.
3. Ввод данных и сохранение книги Excel
Цель: закрепить навыки по созданию таблиц и организации расчетов
Задания:
1. Создать рабочую книгу «fin_department».
2. Ввести данные согласно заданию (см. табл.1). При этом первоначально (с помощью буфера Clipboard) перенести Табл. 1 на лист рабочей книги, затем переставить столбцы «Должность» и «ФИО», после чего с помощью команды «создать имя» с собственной панели инструментов присвоить имена ячейкам в столбце «Зарплата». Сохранить книгу в свой рабочий каталог (предварительно создав его и назвав lesson1). В дальнейшем выполнять регулярное сохранение файла командой «Сохранить».
Методические замечания
Имена используются в Excel для обозначения (идентификации) ячеек или их диапазонов с целью последующего использования при работе содержательных и понятных названий вместо абстрактных и обезличенных обозначений типа «G25». Создание имени для целевой (содержащей данные обозначаемой ячейки) в данной работе выполняется двумя способами: операции "присвоить..." и "создать..." команды "имя" (из меню "вставка" или собственной панели инструментов). В первом случае в соседней ячейке (слева или сверху от выделенной целевой) находится имя-текстовая строка, которое автоматически будет найдено программой Excel и после подтверждения пользователем присвоено целевой ячейке. Во втором случае необходимо выделить диапазон ячеек, в котором, аналогично предыдущему случаю, левый столбец (или верхняя строка) должны содержать строки-имена. В настоящем пункте задания целевыми ячейками являются те, в которых указана зарплата, а имена содержатся в столбце с ФИО сотрудников. При построении формул следует использовать уже созданные к этому моменту имена.

Табл. 1. Данные для Листа 1
Сведения о среднемесячной заработной плате сотрудников отдела

ФИО
Должность
Зарплата, руб.
Премия, %
Премия, руб.
Итого

Иванова И.И.
начальник отдела
12000
75
 
 

Павлов П.П.
гл. специалист
10000
50
 
 

Петрова П.П.
специалист по кредиту
8000
25
 
 

Яковлев Я.Я.
программист (совмест.)
6000
0
 
 


3. Отформатировать данные по образцу табл.1. При этом в первую строку листа внести название таблицы и разместить посредине с использованием команды «Объединить и поместить в центре».
4. Средствами Excel рассчитать размер премии для каждого сотрудника (графа «Премия, руб.»), а также сумму выплаты: а) по каждой строке; б) по столбцу «Итого». При этом воспользоваться средствами «автозаполнение ячеек» и «автосуммирование». Оформить таблицу с помощью команды «Автоформат». Оформить ярлычок листа и сетку листа красным цветом.
5. Ввести данные согласно заданию на другой лист (см. табл.2). Для этого создать новый лист с помощью команды «Добавить лист» созданной ранее панели инструментов (см. пункт 2.3)

Табл. 2. Данные для Листа 2
Аренда помещения ( в мес.) (на примере офиса в гостинице «Москва»)

Наименование расходов
Сумма, $
Сумма, руб.

Офис (комната 20 м2, прихожая со встроенной мебелью, санузел)
300
 

Номер телефона
50
 

Охрана (сигнализация)
60
 

Кондиционер
30
 

Ежедневная влажная уборка помещения
60
 

Всего:
 
 


6. Средствами Excel рассчитать сумму аренды помещения (исходя из курс доллара 30 руб. за USD).
При этом в формулах использовать абсолютную адресацию к ячейке во второй строке таблицы, куда предварительно поместить значение параметра, обозначающего текущий курс доллара. Отформатировать таблицу средствами команды «Автоформат». Произвести оформление листа желтым цветом.
7. Ввести данные согласно заданию (см. табл.3) на лист №3.

Табл. 3. Данные для Листа 3
Смета на приобретение оборудования
 

Наименование статьи расхода
Модель
Стоимость за ед, у.е.
Кол-во, шт
Всего, у.е.
Всего, руб.

Компьютеры
 
 
 
 
 

Ноутбук
 
1750
3
 
 

Мышь оптическая
 
50
3
 
 

Комплектующие и принадлежности
 
 
 
 
 

USB Flash Drive (128 Mb)
 
60
3
 
 

CD-RW
 
1
100
 
 

Программное обеспечение
 
 
 
 
 

Microsoft Project
 
530
1
 
 

КонсультантПлюс (верс. Проф)
 
300
1
 
 

Периферийные устройства
 
 
 
 
 

Принтер лазерный цветной А4
 
2700
1
 
 

Сканер
 
300
1
 
 

Оргтехника
 
 
 
 
 

Копировальный аппарат А4
 
570
1
 
 

Дубликатор
 
4500
1
 
 

Средства связи
 
 
 
 
 

Факсимильный аппарат
 
180
1
 
 

Телефонный аппарат
 
150
1
 
 

ИТОГО
 
 
 
 
 

Скидка
 
 
 
 
 

(см примечание внизу страницы)

8. Средствами Excel организовать возможность перерасчета стоимости в рублях, если за условную единицу принят а) $, б) . Для этого использовать для вставки требуемого символа в нужную ячейку команду «Вставка–символ». Выбор типа условной единицы выполнить с помощью функции «Если», выбирая в качестве ее аргумента символ требуемой денежной единицы.
Методические замечания
Для того, чтобы организовать автоматический пересчет таблицы в зависимости от принятой условной единицы (т.е. $ или ), следует в отдельную ячейку-параметр c именем "ue" поместить заданный символ (напр., $). Далее при расчете значения в столбце "Всего, руб." в формуле с помощью функции ЕСЛИ осуществлять проверку вида «=ЕСЛИ(ue="$";USD;EURO)», где USD и EURO – имена ячеек, содержащих значения курсов соответствующих валют. Таким образом, заменяя в дальнейшем в единственной ячейке-параметре ue символ $ на , можно получить автоматический пересчет требуемой суммы в рублях с учетом курсов, указанных в ячейках USD и EURO. Названия моделей оборудования и их ориентировочную стоимость можно узнать из прайс-листов, журналов типа "Компьютер-маркет" или "Компьютер-прайс", бесплатно распространяемых в компьютерных фирмах и магазинах типа "Кей", "Компьютерный мир" и т.д. или на сайте издательства (напр. [ Cкачайте файл, чтобы посмотреть ссылку ]).
9. Средствами Excel рассчитать в рублях сумму скидки, предоставляемой фирме при условии, что сумма покупки превышает 1000 у.е. (также с использованием логической функции «ЕСЛИ»). Размер скидки составляет 5% от суммы покупки. Поместить расчет скидки в строке, следующей за итоговой. Произвести оформление листа синим цветом.
10. Ввести данные согласно заданию (см. табл.4), самостоятельно поставив в столбец "Стоимость за ед., руб." значения, соответствующие текущим ценам на соответствующие товары (например, найдя эти цены в каталоге канцтоваров в Интернете).

Табл. 4. Данные для Листа 4
Смета на приобретение расходных материалов (на год)
 

Наименование статьи расхода
Ед. изм.
Стоимость за ед., руб.
Кол-во, шт.
Всего, руб.

Бумага
 
 
 
 

конверты
коробка
 
 
 

наклейки
коробка
 
 
 

белая, плотность 80 г/м2
упаковка
 
 
 

цветная, плотность 200 г/м2
упаковка
 
 
 

факсовая
рулон
 
 
 

Канцелярские товары
 
 
 
 

папка-накопитель
шт.
 
 
 

папка-скоросшиватель
шт.
 
 
 

папка-уголок
упаковка
 
 
 

и т.д.
 
 
 
 

ИТОГО
 
 
 
 

Скидка
 
 
 
 


11. Средствами Excel рассчитать и сумму скидки постоянному покупателю – 3% (в руб.) и разместить в последней строке таблицы.
12. В ячейки столбца «Ед. изм.» ввести примечания о количестве товара в упаковке (например, конверты – коробка – 1000 штук, или бумага для факсимильного аппарата – рулон – 50 м). При этом для ввода данных в столбец «Ед. изм.» использовать возможность ввода данных из заранее подготовленного списка значений (команда «Данные–проверка»). Сам список расположить на этом же листе в отдельной табличке в строках 100-104 в виде следующей таблицы (предварительно выполнить ее сортировку по алфавиту командой «Данные–сортировка»):

рулон

коробка

шт.

упаковка


Для перехода от одной области таблицы к другой использовать клавиатурную комбинацию «Ctrl+стрелка вниз/вверх»
Методические замечания
Для того, чтобы обеспечить безошибочный ввод данных, в Excel часто применяется команда «проверка» из меню «Данные». При этом для некоторой ячейки или диапазона в зависимости от типа ожидаемых при вводе данных могут быть заданы минимальное и максимальное значение (или диапазон дат, начальное и конечное время и т.д.) либо конкретный перечень допустимых значений (список). Этот список помещается на этот же лист, часто в область, которая скрыта от глаз пользователя (команды "Формат - строка/столбец – скрыть") либо находится вне обычно наблюдаемой на экране левой верхней части листа. В данной работе используется этот второй способ и список-подтаблица помещается на значительном удалении от основной таблицы в строках 100-104. Для того, чтобы было удобно при подготовке листа перемещаться курсором от списка к основной таблице и обратно, указанный список следует поместить в тот же столбец листа Excel, что и данные столбца "Ед. изм." таблицы 4.
13. В созданной рабочей книге переименовать Лист1 – Зарплата, Лист2 – Аренда, Лист3 – Оборудование, Лист4 – Материалы, Лист5 – Всего расходов.

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

Начисление денежных средств по вкладам
№ счета
ФИО
Вид вклада
Сумма вклада (в руб.)
Годовой процент
Сумма по процентам за месяц
Сумма к выплате

0012
Сидоров В.И.
Срочный
3500
 
 
 

0100
Андреева И.Т.
Депозит
1000
 
 
 

0200
Ковалева О.А.
Срочный
5001
 
 
 

0300
Лобанов А.О.
Пенсионный
129
 
 
 

1145
Петров В.Х.
Пенсионный
550
 
 
 

1190
Морозов П.С.
Пенсионный
250
 
 
 

2390
Пулит А.В.
Срочный
2300
 
 
 

3490
Шанина Е.П.
Срочный
7800
 
 
 

5566
Сидоров В.И.
Пенсионный
10000
 
 
 

6688
Андреева И.Т.
Пенсионный
20000
 
 
 

7979
Петров В.Х.
Депозит
133
 
 
 

 
 
Итого
 
 
 
 


2. Рассчитать значения столбцов с учетом заданного процента, при этом годовой процент (от суммы вклада) равен:
12% для пенсионного вклада;
24% для срочного вклада;
30% для депозита.
Для расчета годового процента депозита за год использовать функцию ЕСЛИ.
3. Сделать графу "Сумма по процентам за месяц" невидимой.
4. Закрепить для просмотра на экране шапку таблицы и первый столбец таблицы.
5. Определить итоговую сумму к выплате на конец года на основании суммы вклада и годового процента.
6. Выделить цветом шапку таблицы и итоговую строку (заливкой).
7. Определить среднюю сумму вклада.
8. Диапазону "Сумма вклада" присвоить имя.
Методические рекомендации
Этот пункт следует выполнить в 2 этапа: сначала выделить диапазон, т.е. все ячейки в нужном столбце, над которыми в дальнейшем будет производиться операция, напр. вычисление среднего значения; затем выполнить команду "Вставка-имя-присвоить" и в появившемся диалоговом окне ввести в верхнее текcтовое поле нужное имя, скажем, Вклад.
9. Определить максимальную "Сумму вклада".
10. Присвоить текущему листу рабочей книги имя "2008 год".
11. Скопировать таблицу на чистый лист; графу "Вид вклада" заполнить с использованием технологии проверки ввода данных; графу "Годовой процент" заполнить с использованием функций "Просмотр" или "ВПР" (для этого ввести дополнительную справочную таблицу процентов выплаты по депозиту). Предварительно ознакомиться с работой функций по Справке приложения Excel.
Методические рекомендации
Выполнение этого пункта предусматривает предварительное самостоятельное изучение по Справке или учебнику работы функций Просмотр и ВПР. Кратко говоря, они применяются для выборки нужных значений из дополнительной (справочной) таблицы. При этом задается входной параметр-ключ, например номер банковского счета, а результат выполнения функции – значение, которое Excel подставляет из некоторого столбца этой справочной таблицы. Например, пусть такая таблица в диапазоне F1:G4 содержит данные в столбцах: Счет и Фамилия:
F G
1 Счет Фамилия
2 1000 Петрова П.П.
3 2000 Иванова И.И.
4 3000 Сидоров С.С.
Тогда функция ВПР(2000, F1:G4, 2) вернет значение (Иванова И.И.) из ячейки G3 (результат во 2-м столбце таблицы, в первом стоит число-ключ "2000"). Естественно, что в качестве аргументов функции могут стоять любые допустимые значения: ссылки, имена, диапазоны и др., а число столбцов справочной таблицы может быть достаточно большим.
Отметим, что имеется похожая на ВПР функция ГПР (горизонтальный просмотр). В отличие от рассмотренных функция "Просмотр" работает только с двумя столбцами/строками (векторная форма), причем ключи должен быть отсортированы в возрастающем порядке.
12. Сравнить обе технологии заполнения таблиц.
Пример оформления результата показан ниже [ Cкачайте файл, чтобы посмотреть картинку ]

Задание 3
Указания по выполнению работы
Создать таблицу по приведенному образцу, после чего выполнить перечисленные ниже пункты.

Таб. номер
Фамилия
Имя
Отчество
Отдел
Должность
Дата приема на работу
Дата увольн.
Пол
Кол. ижд-в
Оклад

1.
1
Иванов
Иван
Иванович
Плановый
начальник
10.01.1996
 
м
2
1500

2.
454
Иваненко
Иван
Петрович
Маркетинга
экономист
10.04.1998
 
м
1
3000

3.
1234
Петров
Петр
Петрович
Маркетинга
секретарь
21.07.1998
 
м
2
2250

4.
12312
Петренко
Петр
Иванович
Бухгалтерия
?
10.10.1998
 
м
1
5250

5.
12345
Сидоров
Сидор
Сидорович
?
?
10.01.1999
10.10.01
м
0
3750

6.
23456
Седов
Кузьма
Фомич
?
?
12.04.1999
 
м
5
1500

7.
34567
Фомин
Фома
Фомич
?
?
26.07.1995
 
м
1
1650

8.
45454
Фоменко
Сидор
Кузьмич
?
?
10.11.1999
 
м
1
4500

9.
45564
Кукина
Юлия
Петровна
?
?
10.01.2000
21.12.01
ж
1
2250

10.
45678
Макова
Алина
Игоревна
?
?
10.04.2000
 
ж
1
3750

11.
56565
Сушкина
Алла
Вадимовна
?
?
10.07.2000
12.12.00
ж
1
2055

12.
56786
Кротова
Инна
Павловна
?
?
21.10.1997
 
ж
1
2250

13.
56789
Бойцов
Семен
Семенович
?
?
10.01.2001
 
м
1
4500

14.
67890
Гайдай
Иван
Михайлович
?
?
30.04.2001
 
м
1
3000

15.
78787
Краснов
Павел
Павлович
?
?
10.07.2001
 
м
5
6000

1. Заполнить графы №, Отдел, Должность, используя оптимальные технологии ввода (в т.ч. заполнить ячейки, отмеченные знаком "?", используя вспомогательные таблицы-перечни, изобретая и вводя, при необходимости, новые значения в поля этой таблицы).
2. Скрыть столбцы "Дата увольнения" и "Отчество".
3. Закрепить шапку таблицы.
4. Увеличить всем сотрудникам оклад в 1,37 раза. Коэффициент повышения оклада хранить в отдельной ячейке.
5. Для каждого сотрудника определить стаж работы на предприятии.
6. Начислить всем сотрудникам премию, пропорциональную стажу работы: 1000 руб. x (стаж работы).
7. Всем сотрудникам, проработавшим более 5 лет начислить дополнительную премию, в размере 500 руб. Величину премии хранить в отдельной ячейке.
8. Найти величину максимального оклада на предприятии. Средствами условного форматирования выделить ячейки с окладами сотрудников, которые попадают в разряды наиболее и наименее оплачиваемых работников (20% от min/max соответственно).
9. Дать листу имя "2000 год".
10. Скопировать таблицу на другой лист и изменить условные данные (добавить новых сотрудников, "изобретая" их ФИО).
11. На третьем листе найти объем дополнительных выплат для каждого сотрудника.
12. Сохранить рабочую книгу, предварительно обозначив непоименованные рабочие листы и обеспечив "эталонную" структуру таблицы с размещением параметров в ее верхнем правом углу.
Практическая работа № 2 Деловая графика и ее экспорт в MS PowerPoint
Цель: изучить методы построения и редактирования диаграмм, их импорт в другие приложения MS Office. Попутно повторить применение функции ВПР и команды «Таблица подстановки».
Задание 1. Расчет зарплаты сотрудников
1. Основная расчетная таблица

Таб. номер
ФИО
Тарифная ставка (руб.)
Отработано часов
Без учета налога
Начислено

0124


125



0101


134



0105


350



0106


250



0103


267



0110


786



0118


155



0136


467



 
Итого
 





Вспомогательная таблица

 
справочник
 

Таб. номер
ФИО
Тарифная ставка (руб.)

0101
Андреева И.Т.
50,00р.

0103
Евдокимов В.Х.
250,00.

0105
Ковалева О.А.
70,00р.

0106
Лобанов А.О.
100,00р.

0110
Морозова Н.С.
120,00р.

0118
Пулит А.В.
245,00р.

0124
Сидоров В.И.
120,00р.

0136
Шанина Е.П.
120,00р.



2. Порядок выполнения
На основании исходных данных, приведенных в таблице, выполнить следующее.

С помощью функции ВПР (или ГПР – по выбору преподавателя) и вспомогательной таблицы «Справочник» заполнить данными столбцы ФИО и тарифная ставка.
Рассчитать заработную плату сотрудников с учетом подоходного налога (13%) и заполнить два последних столбца и итоговую графу.
Построить диаграмму, отражающую начисленную сумму каждому из сотрудников.
Изменить цвет ряда данных.
Изменить цвет области построения диаграммы.
Подписать столбец, соответствующий максимальной сумме.
Добавить на диаграмму ряд данных «Отработано часов».
Настроить его на вспомогательную ось.
С помощью диаграммы увеличить величину отработанных часов Андреевой И.Т. до 100 часов.
С помощью диаграммы увеличить сумму, начисленную Сидорову В.И. до 30 000р.
Повторно сохранить файл с расчетами в своем рабочем каталоге.
Задание 2. Расчет суммы по вкладу
1.Вид основной расчетной таблицы


1 вар.
2 вар.
разность

срок
без капитал
с капитал.%


1




2









Вспомогательная таблица процентов по вкладам банка

Таблица % по вкладам

депозит
% по вкл

1000
6%

3000
7%

8000
8%

15000
9%

50000
11%

150000
13%

500000
15%



2. Порядок выполнения

Используя в качестве справочной таблицу процентов по вкладам банка:
Рассчитать доход по вкладу на 1 год без капитализации процентов (по первому варианту вклада)
Вычислить доход при условии капиталицации процентов (по второму варианту вклада)
Выполнить расчеты по пп. 1-2 на сроки от 2 до 10 лет
Рассчитать доход, получаемый в случае вложения по 2 варианту.
Построить графики для 3-х столбцов (1 вар., 2 вар. и разность)
Построить линию тренда для столбца "разность", поместив на график и величину достоверности аппроксимации R^2
Осуществить прогноз для значений разности при увеличении срока до 15 лет.
Повторить расчет дохода по 2-му варианту с использованием Таблицы подстановки.
Подготовить несколько графиков (не менее 3-4) для импортирования в приложение MS Powerpoint c целью демонстрации необходимости вложений с капитализацией.
Создать презентацию и вставить в нее подготовленные графики.
Практическая работа № 3. Анализ данных в таблицах
Цель практической работы: освоить технологии анализа и расчетов на основе списков (баз данных) EXCEL: структурирование и группировка данных; формирование Итогов; консолидация таблиц; сортировка данных; фильтры; решение задач бизнес-анализа средствами Сводных таблиц.
Содержание работы
Задание 1: Сортировка и выборка данных из таблиц
Задание 2: Выборка из таблиц с помощью функций работы со списками
Задание 3: Фильтрация и обработка данных, построение сводных таблиц.
Требования к оформлению рабочих книг
При работе со списками (базами данных Excel) исходные данные размещаются на первом листе, а результаты их обработки по каждому пункту задания - на последующих. По каждому заданию этой работы следует подготовить отдельную книгу и присвоить ей имя файла по типу Фамилия_3_n, где Фамилия - фамилия (а также имя, отчество при необходимости) студента, n - номер задания.
Описание заданий
Задание 1.
Дана следующая таблица:
Таблица1
Месяц
Продукция
Продавец
Район
Объем (шт.)
Выручка в руб.

янв
Напитки
Марченко
Северный
11111
2577752

янв
Напитки
Марченко
Восточный
3214
745648

янв
Напитки
Марченко
Южный
3200
742400

фев
Напитки
Марченко
Северный
567
131544

март
Напитки
Марченко
Северный
567
131544

фев
Напитки
Николаев
Западный
45677
10597064

янв
Напитки
Николаев
Западный
45670
10595440

янв
Мясо
Ивин
Южный
543
125976

янв
Мясо
Ивин
Восточный
5678
1317296

фев
Мясо
Ивин
Южный
4444
1031008

фев
Мясо
Ивин
Северный
6666
1546512

март
Мясо
Ивин
Восточный
7777
1804264

март
Мясо
Ивин
Южный
2323
538936

март
Молоко
Козлов
Южный
353
81896

март
Молоко
Козлов
Северный
36789
8535048

янв
Молоко
Козлов
Южный
353
81896

фев
Молоко
Козлов
Южный
354
82128

март
Молоко
Марченко
Северный
4567
1059544

фев
Бакалея
Козлов
Южный
5432
1260224

март
Бакалея
Козлов
Восточный
2345
544040

март
Бакалея
Козлов
Южный
567
131544

На основании исходных данных, приведенных в таблице, выполнить следующее.
Отсортировать Табл. 1 сначала в алфавитном порядке фамилий продавцов, а затем в порядке возрастания полученной выручки.
Отсортировать Табл. 1 по районам в следующем порядке: «Северный», «Восточный», «Западный», «Южный».
Выбрать из таблицы данные по продукции «Молоко», проданной в Северном районе.
Выбрать из таблицы записи, содержащие данные по молоку с выручкой больше1 000 000 р.и по напиткам, с выручкой, большей 200 000р.
Выбрать записи, со значениями выручки, больше средней.
Задание 2.
Дана следующая таблица:
 
 
Информация о потребителях рынка одежды
 

 
 
 
 
 
 
 
 

Пол
Возраст
Социальная группа
Покупатель-ные возможности
Образо-вание
Средний срок носки выходной одежды
Средний срок носки повседневной одежды
Приобре-тение одежды в "Сэконд хэнд"

м
18-25
студент
300р.
н/высшее
1
1
нет

м
26-35
предприниматель
3 000р.
высшее
1
менее года
нет

ж
18-25
студент
500р.
среднее
1
1
да

ж
26-35
безработный
100р.
среднее
3
2
да

м
18-25
предприниматель
2 000р.
среднее
1
1
нет

м
26-35
предприниматель
1 000р.
высшее
1
1
нет

ж
36-45
безработный
0р.
высшее
3
3
да

м
36-45
рабочий
500р.
среднее
3
3
да

м
18-25
рабочий
300р.
высшее
3
3
да

ж
26-35
служащий
200р.
высшее
2
2
да

ж
26-35
служащий
500р.
высшее
1
2
нет

м
36-45
гос. служащий
100р.
высшее
2
2
да

ж
26-35
предприниматель
500р.
н/высшее
1
3
нет

м
46-56
безработный
100р.
н/высшее
3
 более 3 лет
да

ж
18-25
студент
1 000р.
среднее
1
2
нет

м
26-35
безработный
200р.
среднее
2
 более 3 лет
да

ж
36-45
предприниматель
4 000р.
высшее
3
3
нет

ж
56-
пенсионер
200р.
среднее
1
 более 3 лет
да

ж
46-56
пенсионер
300р.
высшее
3
 более 3 лет
да

ж
56-
пенсионер
500р.
высшее
2
 более 3 лет
нет

ж
26-35
предприниматель
1 000р.
высшее
1
2
нет

ж
26-35
студент
200р.
среднее
1
1
да

ж
26-35
служащий
400р.
высшее
2
1
да

м
46-56
рабочий
400р.
среднее
3
 более 3 лет
да

м
36-45
предприниматель
2 000р.
н/высшее
менее года
менее года
да

На основании исходных данных выполнить следующее.
Отсортировать таблицу по графе "Образование", организовав пользовательский список: "н/высшее", "высшее", "среднее", а затем по соц. группе и возрасту.
Отсортировать таблицу по графе "Соц. группа".
Подсчитать кол-во опрошенных в каждой соц. группе (двумя способами).
Выбрать записи, относящиеся к предпринимателям, которые могут тратить от 1000 до 3000 рублей.
Выбрать записи, относящиеся к данным о пенсионерах, готовых тратить больше 300 рублей, и студентах, готовых тратить больше 500 рублей.
Выбрать записи, относящиеся к мужчинам от 18 до 35 лет и к женщинам, от 26 до 35 лет.
Выбрать записи с информацией об опрошенных, имеющих покупательные возможности больше среднего значения.
Задание 3.
Дана следующая таблица:
Данные отдела кадров
 №пп
Таб. номер
Фамилия
 Имя
Отчество
Дата рождения
Отдел
Должность
Дата приема на работу
Дата увольне-ния
Пол
Кол-во иждивенцев
Оклад

1
00001
Иванов
Иван
Иванович
28.10.1952
Плановый
экономист
10.01.1996
 
м
2
1500

2
00454
Иваненко
Иван
Петрович
21.01.1935
Бухгалтерия
бухгалтер
10.04.1998
 
м
1
3000

3
01234
Петров
Петр
Петрович
26.08.1970
Плановый
секретарь
21.07.1998
 
м
2
2250

4
12312
Петренко
Петр
Иванович
14.11.1970
Маркетинга
менеджер
10.10.1998
 
м
1
5250

5
12345
Сидоров
Сидор
Сидорович
02.02.1971
Снабжения
менеджер
10.01.1999
10.10.2001
м
0
3750

6
23456
Седов
Кузьма
Фомич
23.04.1971
Плановый
экономист
12.04.1999
 
м
5
1500

7
34567
Фомин
Фома
Фомич
12.07.1985
Плановый
экономист
26.07.1995
 
м
1
1650

8
45454
Фоменко
Сидор
Кузьмич
30.09.1971
Бухгалтерия
бухгалтер
10.11.1999
 
м
1
4500

9
45564
Кукина
Юлия
Петровна
19.12.1971
Бухгалтерия
секретарь
10.01.2000
21.12.2001
ж
1
2250

10
45678
Макова
Алина
Игоревна
08.03.1972
Снабжения
менеджер
10.04.2000
 
ж
1
3750

11
56565
Сушкина
Алла
Вадимовна
17.12.1956
Плановый
экономист
10.07.2000
12.12.2000
ж
1
2055

12
56786
Кротова
Инна
Павловна
21.01.1980
Снабжения
секретарь
21.10.1997
 
ж
1
2250

13
56789
Бойцов
Семен
Семенович
26.08.1970
Бухгалтерия
начальник
10.01.2001
 
м
1
4500

14
67890
Гайдай
Иван
Михайлович
14.11.1970
Бухгалтерия
бухгалтер
30.04.2001
 
м
1
3000

15
78787
Краснов
Павел
Павлович
02.02.1971
Плановый
начальник
10.07.2001
 
м
5
6000

16
78901
Рябов
Олег
Евгеньевич
23.04.1971
Снабжения
начальник
13.10.2001
 
м
1
4500

17
89012
Белова
Софья
Петровна
12.07.1971
Плановый
экономист
10.01.2002
 
ж
2
2550

18
90123
Чернова
Зоя
Богдановна
30.09.1971
Маркетинга
начальник
10.04.2000
 
ж
2
7500

19
98989
Родионов
Андрей
Вадимович
19.12.1971
Маркетинга
секретарь
10.07.2002
30.01.2001
м
0
2250

20
99999
Хрустов
Юрий
Юрьевич
08.03.1972
Маркетинга
менеджер
10.10.2002
 
м
0
3750















На основании исходных данных выполнить следующее.
Отсортировать таблицу по названиям отделов, внутри отдела по табельному номеру.
Отсортировать таблицу по названиям отделов, расположив их в следующей последовательности: "Плановый", "Бухгалтерия", Маркетинг", "Снабжения". Внутри отдела отсортировать по должности.
Сосчитать количество сотрудников в каждом отделе.
Определить величину среднего оклада сотрудников каждого отдела.
Составить список всех уволенных сотрудников, занимавших должность секретаря.
Найти всех сотрудников 1971 года рождения.
Выбрать сотрудников, имеющих оклад меньше среднего.
Составить список всех сотрудников, занимающих должность секретаря старше 20 лет, и экономистов старше 30 лет.
Определить количество иждивенцев в каждом отделе.
Построить сводную таблицу, аналогичную приведенной на листе "Образец".
Подсчитать общее кол-во сотрудников в каждом отделе.
Рассчитать средний оклад сотрудников каждого отдела.
Изменить данные исходной таблицы (например, изменив название любого отдела) и обновить сводную таблицу.
Скрыть данные по отделу "Маркетинга".
Построить новую сводную таблицу с данными о кол-ве мужчин и женщин в каждом отделе.
Рассчитать средний оклад мужчин и женщин на предприятии.
Сформировать приказ о начислении премий.
Сформировать приказ о начислении премий сотрудникам отдела Снабжения.
[ Cкачайте файл, чтобы посмотреть картинку ] Практическая работа № 4. Фильтрация и расчеты в базе данных Excel
Цель практической работы: освоить технологии фильтрации и расчетов в списках (базах данных) EXCEL с помощью специальных функций по работе с БД.
Содержание работы
Задание 1. Чтение запароленного файла, дублирование базы данных на новые листы.
Задание 2. Поиск в базе данных и ее редактирование с помощью формы.
Задание 3. Вычисления с помощью команды "Специальная вставка".
Задание 4. Вычисления в БД с использованием специальных функций БД.
Задание 5. Сохранение результатов.
Задание 1. Чтение запароленного файла, дублирование базы данных на новые листы
1. Откройте файл исходных данных bd4.xls в режиме "только для чтения", введя пароль из 6 цифр ("010505") и скопируйте его в новую книгу под именем newbase.xls на Лист 1. Запишите эту книгу в свой рабочий каталог W:\Excel.
2. Выполните копирование базы данных (БД) на два дополнительных листа книги newbase.xls.(Лист 2 и Лист 3) с помощью команды "Правка >Заполнить >По листам" с предварительным выделением :
подлежащих копированию листов;
копируемой области (при этом используются горячие клавиши Ctrl+Shift+<стрелка управления курсором>)
При появлении диалогового окна "Заполнить по листам" в группе "Заполнить" отметить первый вариант "полностью".
Задание 2. Поиск в базе данных и ее редактирование с помощью формы (работа на Листе 2)
1. С помощью команды "Данные > Форма" найти в базе данных все записи, стоимость рекламы для которых составляет менее $20. Для этого перейти в режим формы, войти в режим "Критерии" и задать в поле "Стоимость" условие "<20", после чего с помощью кнопок "назад" и "далее" найти все записи, удовлетворяющие условию. Записать номера найденных записей в отдельный текстовый файл, вызвав текстовый редактор "Блокнот".
2.После окончания работы в режиме "Форма" сохранить номера записей из текстового файла на свободном месте листа под таблицей (списком базы данных). Проверить результаты выполнения предыдущего пункта с помощью Автофильтра по условию.
3. Ввести с помощью формы следующие данные в базу данных, относящиеся к рекламному модулю "Чтобы победило качество" :
26.12.2008
ТВ
НТВ
30
сек.
Чтобы победило качество
3500
03.12.2008

03.11.2008
радио
Европа Плюс
10
сек.
Чтобы победило качество
38
25.09.2008

30.10.2008
пресса
Деньги
1/2
полоса
Чтобы победило качество
1860
20.10.2008

4. Выйдя из режима "Форма", проверить, что в базу данных действительно были внесены соответствующие записи.
5. Отсортировать базу данных по первому столбцу (по дате выхода рекламы). Проконтролировать наличие введенных в базу данных записей.
6. Задать ярлыку Листа 2 зеленый цвет и перейти к работе с листом 3.
Задание 3. Вычисления с помощью команды "Специальная вставка" (работа на Листе 3)
1. Дополнить базу данных еще одним столбцом "Стоимость в руб."
2. Выполнить расчет значений в созданном столбце с помощью команды "Правка>Специальная вставка". Для этого:
ввести в некоторую ячейку в самом верху листа (напр. в J1) значение текущего курса доллара;
выполнить дублирование содержания столбца "Стоимость в $" в столбец "Стоимость в руб."
выделить ячейку курса доллара и выполнить операцию "копировать"; при этом ячейка останется выделенной (обведенной пунктирной линией);
выделить содержимое столбца "Стоимость в руб.", после чего применить команду "Правка>Специальная вставка", причем в появившемся окне "Специальная вставка" в разделе "Операция" отметить переключатель (кнопку) "Умножить";
после нажатия клавиши "ОК" проверить результат операции.
СЛЕДУЕТ ЗАПОМНИТЬ, что при работе с командой "Специальная вставка" в качестве первого операнда (т.е. объекта, над которым производится работа), всегда берется значение из буфера обмена (одно значение или диапазон значений), а в качестве второго - только что выделенный перед применением команды "Специальная вставка" диапазон (значение).
3. Удалить теперь уже не нужное для работы содержимое ячейки с курсом доллара (ячейку J1).
4. Задать ярлыку Листа 3 зеленый цвет и перейти к работе с листом 4.
Задание 4. Вычисления в БД с использованием функций БСЧЕТ, БДСУММ, ДМАКС, ДМИН
1. Выполнить дублирование базы данных с Листа 1 на Лист 4 (предварительно создав его) с помощью ранее изученной команды "Заполнить > По листам".
2. Создать область критериев над списком базы данных наподобие того, как это обычно делается для команды "расширенный фильтр", выделив для этой области 3-4 строки в верхней части листа.
3. Присвоить списку (базе данных) имя "bd" с помощью команды "Вставка > Имя >Присвоить".
4. С помощью функции БСЧЁТ(база_данных;поле;критерий) вычислить и записать в некоторую рабочую ячейку под областью базы данных (под списком) количество записей, относящихся к рекламоносителям разного типа: ТВ, радио и прессе. При этом в области критериев задавать тип рекламоносителя (напр., "ТВ" ), а в качестве базы_данных и поля задавать, соответственно, имя "bd" и наименование (или порядковый номер) поля (напр., "носитель").
5. С помощью контекстного меню (команды "копировать" и "специальная вставка"), задавая по очереди в области критериев соответствующий тип рекламоносителя и перенося вычисляемые данные из рабочей ячейки, создать и разместить под областью списка несколько ячеек с результатами, содержащими данные по количеству записей каждого типа по образцу следующей таблицы:
всего ТВ
463

всего радио
269

всего полиграфия
48

всего пресса
6

6. Вычислить и разместить в ячейках под списком суммарную стоимость рекламы по каждому виду рекламоносителя с помощью функции БДСУММ (база_данных;поле;критерий) аналогично предыдущему пункту 5.
7. Аналогично предыдущему пункту вычислить и разместить в ячейках под списком максимальную и минимальную стоимости рекламы для каждого из видов рекламоносителяи (используя функции ДМАКС(база_данных; поле; критерий) и ДМИНС(база_данных; поле; критерий).
8. Задать ярлыку Листа 4 зеленый цвет.
Задание 5. Окончательное сохранение результатов
1.Ввести на Лист 1 книги справа в первую строку свои данные (ФИО, группу, дату)
2. Сохранить книгу newbase.xls в каталоге, выделенном преподавателем для размещения результатов работы, под именем ФАМИЛИЯСТУДЕНТА_РАБ4.XLS.
Практическая работа № 5. Подбор параметра, таблица подстановки, сценарии
Цель практической работы: освоить методику расчетов с использованием команд "Подбор параметра", "Таблица подстановки" и "Сценарии".
Содержание работы
Задание 1. Подбор параметра
На основе приведенной таблицы с использованием инструмента «Подбор параметра» определить:
При какой цене сумма со скидкой, полученная за Товар 1 будет равна 25000р.?
При каком количестве проданного товара 2 сумма без скидки будет равна 42000р.?
№ п/п
Код товара
Наименование товара
Цена товара (без учета налога с продаж)
Цена товара (с учетом налога с продаж)
Продано единиц
Сумма (в руб.)
Сумма со скидкой (в руб.)

1
2001
Товар 1
120,00р
 
200
 
 

2
2002
Товар 2
243,70р.
 
150
 
 

Принять, что при сумме более 25000 руб. предоставляется скидка, равная 5%, в противном случае 3%, а налог с продаж составляет 18%.
Задание 2. Таблица подстановки
Используя инструмент «Таблица подстановки», рассчитать варианты платежей по простому займу в 10000р, 11000р., 12000р., 13000р., выданному на 10, 11, 12, 13, 14 месяцев под 8%.
Примечание. Расчет платежа выполняется по формуле: сумма займа * (100%+процент)/срок.
Задание 3. Сценарии
Составить сценарии поведения следующей модели, считая изменяемыми:
затраты на рекламу;
среднее количество покупателей.
Принять, что в году 52 недели. При этом рассмотреть 3 случая:
наихудший (минимальное кол-во клиентов определяется командой "Подбор параметра" для точки безубыточности, затраты на рекламу - 10000 руб/год);
наилучший (100000 клиентов в неделю, затраты на рекламу 500000 руб/год.);
средний (при условии обслуживания среднего количества клиентов и среднего значения затрат на рекламу). Предварительно следует определить эти средние значения.
Перед составлением сценария следует выполнить расчеты в таблице, отталкиваясь от заданных цифр, т.е., например, данные за год рассчитать, исходя из данных за неделю (умножением на 52) и наоборот, данные за неделю получить на основании данных за год. Только после получения готовой таблицы можно приступать к работе со сценариями.
 
 
Всего за неделю
Всего за год

Доход от одного покупателя
 
34,78
 

Расходы на одного покупателя
 
30,12
 

Прибыль от одного покупателя
 
вычислить!
 

Среднее кол-во покупателей
 
изменять!
 

Общая прибыль
 
вычислить!
вычислить!

Накладные расходы
 
 
 

 
Отчисления на зарплату
 расчитать!
3 494 046

 
Оборудование
 расчитать!
1 635 511

 
Амортизация
 расчитать!
453 305

 
Реклама
 расчитать!
изменять!

 
Снабжение
 расчитать!
496 944

 
Прочее
 расчитать!
1 295 828

Всего
 
 
вычислить!

Текущая прибыль
 
 
вычислить!


Практическая работа № 6. Создание функций пользователя, автоматизация задач
Цель практической работы: изучение возможностей MS Excel, используемых при автоматизации решения задач с помощью средств офисного программирования (языка VBA).
Содержание работы
Задание 1. Подготовка листа и ввод исходных данных.
Задание 2. Автоматизация вычислений с помощью макрорекордера.
Задание 3. Создание функций пользователя.
Описание заданий
Каждое задание выполняется на отдельном листе!
Задание 1. Подготовка исходных данных
Создать новую пустую книгу и сохранить ее в своем рабочем каталоге под именем ФАМИЛИЯСТУДЕНТА_РАБ6.XLS.
Создать таблицу указанного вида на Листе 1 (см. пример ниже).
Рассчитать столбец "цена в руб." в зависимости от признака "тип валюты" на основе столбца "цена в у.е." с помощью функции "Если" и поместить в столбец "E".
Рассчитать столбец "итого в руб." как произведение количества и рассчитанной цены в руб. и поместить в столбец "F".
Рассчитать итоговые значения для столбцов E:F и записать в строку 19.

Пример таблицы
 
A
B
C
D
E
F

1
 
Расчет стоимости компьютера
 
тип валюты
$
 

2
 
 
 
курс $=
28
 

3
 
 
 
курс =
37
 

4
№ п/п
Наименование
количество
цена в у.е.
цена в руб.
итого в руб.

5
1
Процессор
2
150
 
 

6
2
HDD
3
62
 
 

7
3
FDD
1
15
 
 

8
4
RAM
2
34
 
 

9
5
Модем
1
70
 
 

10
6
Монитор
1
480
 
 

11
7
Системная плата
1
48
 
 

12
8
Мышь
1
28
 
 

13
9
клавиатура
1
12
 
 

14
10
Принтер
1
210
 
 

15
11
CR-RW
1
45
 
 

16
12
Адаптер LAN
1
20
 
 

17
13
Корпус
1
48
 
 

18
14
UPS
1
80
 
 

19
 
ИТОГО для всех
 
 
 
 


Задание 2.
1. Выделить несмежные диапазоны A1:D18, E1:E4 и F4 на Листе 1 и скопировать их на Лист 2.
2. Выделить ячейку E5 и приступить к записи макроса, вычисляющего значение цены в рублях, для чего:
Вызвать команду Сервис > макрос > начать запись;
Установить в диалоговом окне параметры:
Имя макроса:"цена"
Сочетание клавиш: "А"
Сохранить в: "эта книга".
Нажать клавишу "ОК" и перейти в режим записи относительных адресов, нажав клавишу "относительная ссылка" на панели "остановить запись".
Ввести в ячейку E5 формулу: "=ЕСЛИ($E$1="$";$E$2;$E$3)*D5"
По окончании записи, нажав символ "квадрат", остановить макрорекордер.
3. Проверить работу макроса, для чего, выделив следующую ячейку столбца (E6), выполнить команду Сервис > Макрос > Макросы и в появившемся диалоговом окне Макрос выбрать ранее введенное имя макроса ("цена") и нажать кнопку "Выполнить". Проконтролировать правильность расчетов, сравнив их результат с вычислениями на листе 1.
4. Повторно выполнив ту же команду для вызова диалогового окна Макрос, проверить комбинацию "горячих" клавиш вызова макроса цена нажатием кнопки Параметры и с помощью горячих клавиш вычислить оставшиеся значения в столбце E.
5. Потренироваться в создании клавиатурных макросов, служащих для экономии времени при выполнении рутинных повторяющихся операций. Для этого записать и сохранить макросы для выполнения следующих действий: заливка ячеек нужным цветом, форматирование, увеличение/уменьшение разрядности, объединение ячеек, вычисление итоговой суммы как произведения двух рядом стоящих чисел.
Задание 3. Создание функций пользователя
1. Выполнить пункт 1 задания 2 для формирования таблицы с исходными данными на Листе 3.
2. Создать функцию пользователя для вычисления по приведенной ранее формуле в зависимости от признака валюты и курсов доллара и евро. Для этого выполнить следующее.
Вызвать систему программирования VBA по команде Сервис > Макрос > Редактор Visual Basic.
Если в проекте нет модуля, в который предстоит записать функцию пользователя, то создать его, выполнив команду меню VBA Вставка > Модуль.
Выполнить команду меню VBA Вставка > Процедура.
В открывшемся диалоговом окне "Вставка процедуры" установить переключатели "Функция", а в поле "Имя" задать имя функции "Цена"
Щелчком по "ОК" завершить создание прототипа функции и проконтролировать его появление в соответствующем модуле среды VBA.
Ввести список параметров, их типы, указать тип возвращаемого значения и записать тело функции по образцу следующего фрагмента (знак "подчеркивание" нужен для переноса текста строки в VBA):
Function ЦЕНА(ЦЕНА_В_УЕ As Double, Признак_валюты As String, _
Курс_Дол As Double, Курс_Евро As Double) As Double)
If Признак_валюты = "$" Then
ЦЕНА = ЦЕНА_В_УЕ * Курс_Дол
Else
ЦЕНА = ЦЕНА_В_УЕ * Курс_Евро
End If
End Function
3. В режиме просмотра объектов в среде VBA (клавиша F2), установив в поле Project /Library имя VBProject, выбрать в соответствующем модуле (столбец "Classes") имя введенной функции и с помощью команды "Свойства" контекстного меню задать в окне "Параметры компонента" краткое описание назначения и аргументов функции.
4. Проверить работу функции для вычисления значений в столбце E. Для этого:
установив курсор в нужной ячейке, после ввода признака формулы "=" выбрать созданную функцию "Цена" в списке функций Мастера функций, предварительно установив в списке категорию функции в значение "определенные пользователем";
после нажатия ОК в появившемся окне "Аргументы функции" выбрать обычным для Excel образом нужные аргументы, не забывая в необходимых случаях указывать признак абсолютной адресации.
5. После выполнения вычисления в одной ячейке с помощью автозаполнения вычислить значения во всем столбце E.
6. Вычислить значения в столбце F и итоговые значения в строке 19. Сравнить результат с тем, который был получен ранее.
7. Потренироваться в создании функций пользователя на примерах вычисления тригонометрических и статистических функций и сравнить их с результатами, получаемыми с помощью встроенных функций Excel.
Окончательное сохранение результатов
Вписать в первой строке Листа 1 реквизиты студента (ФИО, группу, дату) и выполнить окончательное сохранение книги в каталоге, выделенном преподавателем для размещения результатов работы.


Приложенные файлы

  • doc 24924225
    Размер файла: 393 kB Загрузок: 2

Добавить комментарий