MS Excel
Подготовка, редактирование и печать таблиц
1. Элементарные действия при создании таблицы.
Используя приемы автозаполнения и автосуммирования, создайте следующую таблицу:
Табл. 1
| Доходы /расходы за второе полугодие 2009г. | ||||||
| Июль | Август | Сентябрь | Октябрь | Ноябрь | Декабрь | |
| Доходы | ||||||
| Оклад | ||||||
| Доплата к окладу | ||||||
| Премия | ||||||
| Дополнительные доходы | ||||||
| Доходы всего | ||||||
| Расходы | ||||||
| Оплата жилья | ||||||
| Возврат кредита | ||||||
| Общие расходы | ||||||
| Отпуск | ||||||
| Расходы всего | ||||||
| Сальдо |
Указания по выполнению. Шапку таблицы и строки поля реквизитов: «Оклад», «Доплата к окладу», «Оплата жилья», «Возврат кредита» создавать с использованием автозаполнения; строки «Доходы всего» и «Расходы всего» - с использованием автосуммирования; строка «Сальдо» - с использованием вычисления по формулам.
2. Редактирование таблицы.
а) введите денежный стиль во всем числовом поле таблицы;
б) уменьшите разрядность данных (установить данные в таблице в руб. без указания коп.);
в) произведите автоподбор ширины столбцов таблицы;
г) произведите автоформатирование таблицы в целом (исключая заголовок).
- Создание и редактирование диаграмм.
а) постройте диаграмму по данным строк «Доходы всего» и «Сальдо» (функции), названия месяцев (аргументы).
4. Сохранение рабочей книги Excel с защитой от несанкционированного доступа.
а) сохраните полученный табличный документ в файле tabl_1.xls с паролем защиты от несанкционированного доступа.
Создание баз данных и работа с ними
Создайте базу данных по следующему образцу или загрузите из файла example_1.xls:
Табл. 2
| Модель | f, MHz | RAM,Mb | HDD, Gb | Video, Mb | CD | Цена, $ |
| Pentium III | 48x | |||||
| Celeron | 52x | |||||
| Celeron | DVD | |||||
| Celeron | 52x | |||||
| Celeron | 52x | |||||
| Celeron | 52x | |||||
| Pentium IV | On board | 52x | ||||
| Pentium IV | DVD | |||||
| Pentium IV | CD/RW | |||||
| Pentium IV | On board | 52x | ||||
| Pentium IV | On board | 52x | ||||
| Pentium IV | 52x | |||||
| Pentium IV | 52x |
1. Сортировка.
Произведите сортировку данных в столбцах "f, MHz", "Цена, $".
2. Поиск в базе данных.
Произведите автоматический поиск данных в базе данных по следующему критерию: Модель - Celeron или Pentium IV, f, MHz >=2000MHz, RAM >=256Mb, HDD>=40Gb, Цена, $<=450$.
Анализ данных
Введите или загрузите из файла Склад2.xls следующую таблицу:
Табл. 3
1.Формирование отчета по фирмам-потребителям.
Произведите формирование отчета по фирмам.
2.Формирование отчета по датам.
Сформируйте отчет за январь.
Сводные таблицы
Создайте по данным предыдущей таблицы (файл Склад2.xls) сводную таблицу, в которой будут представлены итоги как по датам, так и по фирмам.
Табл. 4
| Дата | ||||||||||||
| Наим.орг. | Данные | 01. янв | 02. янв | 03. янв | 04. янв | 05. янв | 06. янв | 07. янв | 08. янв | 09. янв | 10. янв | Общий итог |
| АЛЬЯНС | Сумма по полю Всего | |||||||||||
| Сумма по полю Оплата | ||||||||||||
| Сумма по полю Остаток | ||||||||||||
| АННА -МАРИЯ | Сумма по полю Всего | 1092.5 | 1092.5 | |||||||||
| Сумма по полю Оплата | ||||||||||||
| Сумма по полю Остаток | 1092.5 | 1092.5 | ||||||||||
| ВОЛАНД | Сумма по полю Всего | |||||||||||
| Сумма по полю Оплата | ||||||||||||
| Сумма по полю Остаток | ||||||||||||
| Итог Сумма по полю Всего | 1217,5 | 1263,5 | ||||||||||
| Итог Сумма по полю Оплата | ||||||||||||
| Итог Сумма по полю Остаток | 1217,5 | 1263,5 |
Решение задач
Подбор параметра
а) создайте следующую таблицу:
Табл. 5
| A | B | |
| Расходы/доходы от выпуска продукции | ||
| Количество экземпляров | 20 000 | |
| Доход | =B17*B4 | |
| Себестоимость | =B18*B4 | |
| Валовая прибыль | =B5-B6 | |
| % накладных расходов | ||
| Затраты на зарплату | =250*B4 | |
| Затраты на рекламу | =50*B4 | |
| Накладные расходы | =B5*B8/100 | |
| Валовые издержки | =B9+B10+B11 | |
| Прибыль от продукции | =B7-B12 | |
| Цена продукции | 6 000 | |
| Себестоимость продукции | 2 000 |
б) произведите подбор параметра в ячейке В14, выясните условия получения прибыли 50000000 за счет всех возможных изменений факторов по отдельности: количества экземпляров, накладных расходов, цены и себестоимости.
Поиск оптимальных решений.
а) создайте следующую таблицу:
Табл. 6
| A | B | C | D | E | F | |
| Квартал | I | II | III | IV | Всего | |
| Сезонный фактор | 0,9 | 1,1 | 0,8 | 1,2 | ||
| Объем сбыта | ||||||
| Доход от оборота | ||||||
| Себестоимость реализованной продукции | ||||||
| Валовая прибыль | ||||||
| Затраты на зарплату | ||||||
| Затраты на рекламу | ||||||
| Накладные расходы | ||||||
| Валовые издержки | ||||||
| Прибыль от продукции | ||||||
| Коэффициент прибыльности | 10% | 12% | 8% | 13% | 11% | |
| Цена продукции | ||||||
| Себестоимость продукции | ||||||
| Параметры заполнения | ||||||
| B4 = 35*b2*(B10+3000)^0.5 | ||||||
| B5 = B4*$B$17 | ||||||
| B6 = B4*$B$18 | ||||||
| B7 = B5-B6 | ||||||
| B11 = 0.15*B5 | ||||||
| B12 = СУММ(B9:B11) | ||||||
| B14 = B7-B12 | ||||||
| B15 = B14/B5 |
б) найдите условия оптимального распределения затрат на рекламу по кварталам, при котором годовая прибыль (второй вариант - коэффициент прибыльности) максимальна.
Использование функций
Используя функции GROWTH и FORECAST выполните линейную и экспоненциальную экстраполяцию в приведенной ниже таблице; с помощью экстраполяции произведите прогнозирование экспорта товаров в будущем (например, в 1995г.) по данным за 1990-1994г.г.
Табл. 7
| Структура экспорта товаров из России в страны дальнего зарубежья (Российский статистический ежегодник, 1995г.) (в млрд. долларов США) | |||||
| Машины, оборудование и транспортные средства | 12.5 | 5.2 | 3.8 | 2.9 | 2.5 |
| Минеральные продукты (в т. ч. нефть и газ) | 32.3 | 26.3 | 20.7 | 21.9 | |
| Металлы, драгоценные камни и изделия из них | 9.2 | 7.3 | 10.3 | 13.1 | |
| Продукция химической промышленности | 3.3 | 3.4 | 2.6 | 2.6 | 3.9 |
| Древесина и целлюлозно-бумажные изделия | 3.1 | 2.4 | 1.6 | 1.9 | 2.1 |
| Текстиль и текстильные изделия | 0.7 | 0.5 | 0.3 | 0.2 | 0.4 |
| Кожевенное сырье, пушнина и изделия из них | 0.1 | 0.1 | 0.1 | 0.1 | 0.2 |
| Продовольственные товары и сельхоз сырье | 1.5 | 1.3 | 1.6 | 1.6 | 2.1 |
| Прочие | 8.4 | 4.4 | 3.4 | 3.9 | |
| ВСЕГО | 71.1 | 50.9 | 42.4 | 44.3 | 50.1 |
Работа с макросами
Создание макроса
а) Откройте файл wares.xls;
б) выполните копирование листа рабочей книги, содержащего базу данных, на Лист 2 этой же книги и одновременно создайте макрос для этой операции.
Запуск макроса
Выполните копирование листа рабочей книги, содержащего базу данных, на Лист 2 с помощью макроса.
Создание средств управления для работы с макросами
Создайте средства управления (кнопку из меню «Forms», графический объект, объект WordArt, кнопку в панели инструментов «Standard») для макроса, созданного по п.2.