Указания по выполнению работы
Перечень заданий
Цель работы: освоение рациональных приемов работы по созданию и редактированию таблиц в Excel
1. Задание 1: ввод данных, их редактирование и форматирование, имена ячеек, абсолютные и относительные ссылки, функция "Если", команда "Проверка" и ее использование при вводе данных.
2. Задание 2: расчет таблицы "начисление денежных средств по вкладу", имена диапазонов, расчет процентов, функции "Если", "Просмотр", "ВПР", закрепление столбцов и строк, работа со Справкой.
3. Задание 3: расчет дохода по акциям.
4. Задание 4: расчет заработной платы сотрудникам отдела.
5. Задание 5: расчет таблицы для определения величины наращенной суммы.
Задание 1.
Указания по выполнению работы
Ввод данных и сохранение книги Excel
Цель: закрепить навыки по созданию таблиц и организации расчетов
Задания
1. Создать рабочую книгу «fin_department».
2. Ввести данные согласно заданию (см. табл.1). При этом первоначально (с помощью буфера Clipboard) перенестиТабл. 1 на лист рабочей книги, затем переставить столбцы «Должность» и «ФИО», после чего с помощью команды «создать имя» с собственной панели инструментов присвоить имена ячейкам в столбце «Зарплата». Сохранить книгу в свой рабочий каталог (предварительно создав его и назвав lesson1). В дальнейшем выполнять регулярное сохранение файла командой «Сохранить».
Методические замечания.
Имена используются в Excel для обозначения (идентификации) ячеек или их диапазонов с целью последующего использования при работе содержательных и понятных названий вместо абстрактных и обезличенных обозначений типа «G25». Создание имени для целевой (содержащей данные обозначаемой ячейки) в данной работе выполняется двумя способами: операции "присвоить..." и "создать..." команды "имя" (из меню "вставка" или собственной панели инструментов).
В первом случае в соседней ячейке (слева или сверху от выделенной целевой) находится имя-текстовая строка, которое автоматически будет найдено программой Excel и после подтверждения пользователем присвоено целевой ячейке.
Во втором случае необходимо выделить диапазон ячеек, в котором, аналогично предыдущему случаю, левый столбец (или верхняя строка) должны содержать строки-имена.
В настоящем пункте задания целевыми ячейками являются те, в которых указана зарплата, а имена содержатся в столбце с ФИО сотрудников. При построении формул следует использовать уже созданные к этому моменту имена.
Табл. 1. Данные для Листа 1
| Сведения о среднемесячной заработной плате сотрудников отдела | |||||
| ФИО | Должность | Зарплата, руб. | Премия, % | Премия, руб. | Итого |
| Иванова И.И. | начальник отдела | ||||
| Павлов П.П. | гл. специалист | ||||
| Петрова П.П. | специалист по кредиту | ||||
| Яковлев Я.Я. | программист (совмест.) |
3. Отформатировать данные по образцу табл.1. При этом в первую строку листа внести название таблицы и разместить посредине с использованием команды «Объединить и поместить в центре».
4. Средствами Excel рассчитать размер премии для каждого сотрудника (графа «Премия, руб.»), а также сумму выплаты: а) по каждой строке; б) по столбцу «Итого». При этом воспользоваться средствами «автозаполнение ячеек» и «автосуммирование». Оформить таблицу с помощью команды «Автоформат». Оформить ярлычок листа и сетку листа красным цветом.
5. Ввести данные согласно заданию на другой лист (см. табл.2). Для этого создать новый лист с помощью команды «Добавить лист» созданной ранее панели инструментов (см. пункт 2.3)
Табл. 2. Данные для Листа 2
| Аренда помещения (в мес.) (на примере офиса в гостинице «Москва») | ||
| Наименование расходов | Сумма, $ | Сумма, руб. |
| Офис (комната 20 м2, прихожая со встроенной мебелью, санузел) | ||
| Номер телефона | ||
| Охрана (сигнализация) | ||
| Кондиционер | ||
| Ежедневная влажная уборка помещения | ||
| Всего: |
6. Средствами Excel рассчитать сумму аренды помещения (исходя из курса доллара 30 руб. за USD).
При этом в формулах использовать абсолютную адресацию к ячейке во второй строке таблицы, куда предварительно поместить значение параметра, обозначающего текущий курс доллара. Отформатировать таблицу средствами команды «Автоформат». Произвести оформление листа желтым цветом.
7. Ввести данные согласно заданию (см. табл.3) на лист №3.
Табл. 3. Данные для Листа 3
| Смета на приобретение оборудования | |||||
| Наименование статьи расхода | Модель | Стоимость за ед., у.е. | Кол- во, шт. | Всего, у.е. | Всего, руб. |
| Компьютеры | |||||
| Ноутбук | |||||
| Мышь оптическая | |||||
| Комплектующие и принадлежности | |||||
| USB Flash Drive (128 Mb) | |||||
| CD-RW | |||||
| Программное обеспечение | |||||
| Microsoft Project | |||||
| КонсультантПлюс (верс. Проф) | |||||
| Периферийный устройства | |||||
| Принтер лазерный цветной А4 | |||||
| Сканер | |||||
| Оргтехника | |||||
| Копировальный аппарат А4 | |||||
| Дубликатор | |||||
| Средства связи | |||||
| Факсимильный аппарат | |||||
| Телефонный аппарат | |||||
| ИТОГО | |||||
| Скидка |
8. Средствами Excel организовать возможность перерасчета стоимости в рублях, если за условную единицу принят: а) $, б) €. Для этого использовать для вставки требуемого символа в нужную ячейку команду «Вставка–символ». Выбор типа условной единицы выполнить с помощью функции «Если», выбирая в качестве ее аргумента символ требуемой денежной единицы.
Методические замечания.
Для того чтобы организовать автоматический пересчет таблицы в зависимости от принятой условной единицы (т.е. $ или €), следует в отдельную ячейку-параметр c именем "ue" поместить заданный символ (напр., $). Далее при расчете значения в столбце "Всего, руб." в формуле с помощью функции ЕСЛИ осуществлять проверку вида «=ЕСЛИ(ue="$";USD;EURO)»,где USD и EURO – имена ячеек, содержащих значения курсов соответствующих валют. Таким образом, заменяя в дальнейшем в единственной ячейке-параметре ue символ $ на €, можно получить автоматический пересчет требуемой суммы в рублях с учетом курсов, указанных в ячейках USD и EURO.
Названия моделей оборудования и их ориентировочную стоимость можно узнать из прайс-листов, журналов типа "Компьютер-маркет" или "Компьютер-прайс", бесплатно распространяемых в компьютерных фирмах и магазинах типа "Кей", "Компьютерный мир" и т.д. или на сайте издательства (напр. http://study.econ.pu.ru/cse/pans/fk/www.atlant.ru/comar).
9. Средствами Excel рассчитать в рублях сумму скидки, предоставляемой фирме при условии, что сумма покупки превышает 1000 у.е. (также с использованием логической функции «ЕСЛИ»). Поместить расчет скидки в строке, следующей за итоговой. Произвести оформление листа синим цветом.
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 – Всего расходов.
Примечания: 1) руководитель может попросить Вас обосновать выбор того или иного устройства (технические характеристики можно вписывать, например, в примечания к ячейкам);
2) при приобретении стационарных компьютеров комплектующие должны быть расписаны в приложении к смете.
Задание 2.
Указания по выполнению работы
1. Создать таблицу по приведенному образцу.