ТОГБПОУ «Аграрно-технологический техникум»

ТОГБПОУ «Аграрно-технологический техникум»

 

 

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В ПРОФЕССИОНАЛЬНОЙ ДЕЯТЕЛЬНОСТИ

 

Практические работы

для специальностей 38.02.04 Коммерция (по отраслям)

35.02.16 Эксплуатация и ремонт сельскохозяйственной техники и оборудования

 

 

ПРАКТИЧЕСКАЯ РАБОТА № 2

«ИСПОЛЬЗОВАНИЕ ОТНОСИТЕЛЬНОЙ И АБСОЛЮТНОЙ АДРЕСАЦИИ В ВЫЧИСЛЕНИЯХ»

          

Цель работы

1.Закрепить навыки применения относительной и абсолютной адре­саций для финансовых расчетов.

2. Закрепить навыки применения сортировки, условного формати­рования и копирования созданных таблиц.

2 Формируемые образовательные результаты

 Образовательные результаты У2, У3, З4, ОК5, ПК1.2

 

Обеспеченность занятия

ü Персональный компьютер с установленными программами Microsoft Office.

ü Принтер.

 

4 Вопросы для самоподготовки

1. Какая адресация называется абсолютной? Как она устанавливается?

2. Перечислите статистические функции, используемые в работе.

3. Объясните назначение команды Условное фор­матирование?

4. Как осуществить Условное фор­матирование?

5. Как создать примечание к ячейке?

6. Как построить диаграмму для столбцов, расположенных в разных областях таблицы?

7. Как осуществить защиту листа, книги?

 

Литература

Мочула В.Г. Excel 2007. Расчеты и анализ: учебник / В.Г. Мочула. - Москва, ФЕНИКС, 2010.

 

Содержание заданий

Задание 1 Расчет зарплаты за октябрь  

Задание 2 Расчет зарплаты за ноябрь  

Задание 3 Построение диаграмм  

Задание 4 Защита листа 

Задание 5 Применение условного форматирования

Последовательность выполнения заданий

Задания выполняются в строго указанной последовательности.

Задания и инструкции по выполнению

Задание 1 Расчет зарплаты за октябрь.   

1. Открыть программу электронных таблиц Microsoft Excel.

2. Переименовать Лист1 в Зарплата за октябрь, для этого дважды щелкните мышью по ярлычку и введите новое имя.

3. Создать таблицу по образцу.

 4.Произвести расчеты во всех столбцах таблицы:

ü при расчете Премии используется формула Премия = Оклад * % Премии, для этого в ячейке D5 ввести формулу = SD$4 * С5 (ячейка D4 используется в виде абсолютной адресации, для установки абсолютной адресации нажать клавишу F4) и скопировать формулу ав­тозаполнением.

ü для расчета Всего начислено используется формула Всего начислено = Оклад + Премия (осуществить щелчки по ячейкам и применить ав­тозаполнение).

ü для расчета Удержания используется формула Удержание = Всего начислено * % Удержания, для этого в ячейке F5 ввести формулу = $F$4 * Е5 ( применить ав­тозаполнение);

ü для расчета столбца К выдаче используется формула К выдаче = Всего начислено – Удержания (применить ав­тозаполнение).

5. Рассчитать итоги по столбцам, используя функцию СУММ

6. Рассчитать максимальный, ми­нимальный и средний доходы по данным колонки К выдаче, используя команды Формулы—Другие—Статистические (МИН, МАХ, СРЗНАЧ).

Задание 2 Расчет зарплаты за ноябрь.   

1. Переименовать Лист2 в Зарплата за ноябрь, для этого дважды щелкните мышью по ярлычку и введите новое имя.

2. Скопировать содержимое листа Зарплата октябрь на новый лист (Главная—Копировать—Вставить)

3. Исправить название месяца в названии таблицы. Изменить значение Премии 27% на 32 %. Убедитесь, что программа произвела пере­счет формул.

4. Между колонками Премия и Всего начислено вставить новую колонку Доплата (Главная—Вставить—Вставить ячейки).

5. Рассчитать значе­ние доплаты по формуле Доплата = Оклад * % Доплаты. Значение доплаты принять равным 5 %.

6. Изменить формулу для расчета значений колонки Всего на­числено.

Всего начислено = Оклад + Премия + Доплата.

7. Провести условное фор­матирование значений колонки К выдаче. Установить формат вывода:

ü значений между 7000 и 10000 — зеленым цветом шриф­та; меньше 7000 — красным;

ü боль­ше или равно 10000 — синим цве­том шрифта (Главная--Условное форматирование—Правила выделения ячеек).

8. Осуществить сортировку по столбцу Фа­милия в алфавитном порядке, по возрастанию, для этого следует выделить фрагмент с 5 по 18 строки таблицы (без итогов) и применить команду Данные—Сортировка-- сортировать по — Столбец В.

9. Создать к ячейке D3 комментарии Премия пропорцио­нальна окладу, для этого применить команду Рецензирование--Создать примечание, при этом в правом верх­нем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен рисунке.

Задание 3 Построение диаграмм.   

1. Построить круговую диаграмму на листе Зарплата за ноябрь по столбцам:

ü Фамилия и К выдаче;

ü Фамилия и Всего Начислено;

ü Фамилия и Премия.

Задание 4 Защита листа.   

1. Защитить лист Зарплата ноябрь от изменений, для этого следует использовать команду Рецензирование -- Защитить лист. Задать пароль на лист 123.

Задание 5 Применение условного форматирования

1. Сделать примечания к двум-трем ячейкам листа Зарплата за октябрь.

2. Выполнить условное форматирование оклада и пре­мии за октябрь месяц:

ü до 2000 р. — желтым цветом заливки;

ü от 2000 до 10000 р. — зеленым цветом шрифта;

ü свыше 10000 р. — малиновым цветом заливки, белым цветом шрифта.

3.Защитить лист Зарплата за октябрь от изменений. Проверьте защиту. Убедитесь в неизменяемости данных. Сними­те защиту со всех листов электронной книги.

4. Построить круговую диаграмму начисленной сум­мы к выдаче всех сотрудников за октябрь месяц. Сохранить файл под именем ЗАРПЛАТА в своей папке.

ПРАКТИЧЕСКАЯ РАБОТА № 3

«ИСПОЛЬЗОВАНИЕ СОРТИРОВКИ, ФИЛЬТРОВ, СВОДНЫХ ТАБЛИЦ И КОНСОЛИДАЦИИ ДЛЯ АНАЛИЗА ИНФОРМАЦИИ В СПИСКАХ»

           (2 часа)

Цель работы

1.Закрепить навыки использования сортировки в списках.

2. Получить навыки применения фильтров в списке.

2 Формируемые образовательные результаты

Образовательные результаты У2, У3, З4, ОК5, ПК1.2

 

Обеспеченность занятия

ü Персональный компьютер с установленными программами Microsoft Office.

ü Принтер.

 

4 Вопросы для самоподготовки

1. Что называется списком в табличном процессоре Excel?

2. Для чего применяется сортировка списков?

3. В каких ситуациях применяется сортировка списков по нескольким признакам?

4. Список состоит из двух полей: фамилии студента и оценке по информатике. Какие из этих полей следует использовать как первичный и вторичный признаки сортировки? Обоснуйте ответ.

5. Что такое фильтр? Какие виды фильтров имеются в Excel?

6. Объясните принцип работы фильтра.

7. Объясните принцип работы расширенного фильтра.

8. Чем расширенный фильтр отличается от фильтра?

9. Какая таблица называется сводной и для чего она используется? Как создать сводную таблицу?

10. Для чего используется консолидация данных?

11. перечислите методы консолидация данных?

 

Литература

Мочула В.Г. Excel 2007. Расчеты и анализ: учебник / В.Г. Мочула. - Москва, ФЕНИКС, 2010.

Содержание заданий

Задание 1 Сортировка списка

Задание 2 Применение фильтра

Задание 3 Применение расширенного фильтра

Задание 4 Создание сводной таблицы

Задание 5 Консолидация данных

 

Задания и инструкции по выполнению

Задание 1 Сортировка списка.

1. Создать файл в MS Excel. Присвойте имя файлу ПР №4.

2. Переименовать Лист 1 в Сортировка. Создать таблицу вида:

3. Рассчитать столбцы Количество остатка и Сумма остатка.

4. Установите курсор внутри таблицы данных и выполнть команду меню Данные - Сортировка.

Выбрать первый ключ сортировки: в раскрывающемся списке " сортировать по " выбрать " Отдел " порядок " от А до Я ". Если же хотите, чтобы внутри отдела товары расположились по алфавиту, то выбрать второй ключ сортировки Добавить уровень в раскрывающемся списке " Затем " выбрать " Наименование товара " и установить переключатель в положение " от А до Я ".

Задание 2 Применение фильтра.

1.Скопировать исходную таблицу с Листа 1 на Лист 2, переименовать Лист 2 в Фильтр

2. Установить курсор на заголовок таблицы и выполнить команду меню Данные - Фильтр.

3.Используя фильтр установить- все записи с ненулевым количеством остатка.

4.Вставьть дату в ячейки A1:С1, которая будет автоматически меняться в соответствии с системным временем компьютера, для этого выбрать команду Формулы -- Дата и время - Сегодня.

5. Скопировать исходную таблицу с Листа 1 на Лист 2 ниже первой, например в строку 20.

6. Используя фильтр установить- все записи с Количеством остатка > 0.

7.Скрыть все столбцы, кроме Наименование товара и Количество остатка, для этого выделить столбец (скрываемый) и используя контекстное меню применить команду Скрыть.

Задание 3 Применение расширенного фильтра.

1. Перейти на Лист 3. Переименовать Лист 3 в Расширенный фильтр, скопировать исходную таблицу с Листа 1 на Лист 3.

2. Создать интервал критериев. Для этого скопировать подписи столбцов (диапазон A2:I2) в диапазон ячеек A 20: I 20.

3. Отобрать информацию о книгах, которые закуплены или в 1 -ом квар­тале или имеют тематику Экономика. Для этого:

в ячейку А21 занести значение 1 кв, в ячейку В22 занести значе­ние Экономика (т.е. при расположении условий отбора на разных строчках формируется их связь по правилу ИЛИ)

сделать текущей любую ячейку в области исходной таблицы и выполнить команду Данные — Дополнительно, в диалоговом окне Расширенный фильтр поле Исходный диа­пазон будет уже заполнено, в поле Диапазон условий либо набрать вручную (ввод осуществ­ляется с использованием абсолютных адресов ячеек), либо выделить с помощью мыши диапазон A 20: I 20 (диапазон ячеек, в кото­ром заданы условия отбора). В результате будут отобраны записи, относящиеся к первому кварталу или у которых тематика Экономика.

4. Отменить действие фильтра.

5. Отобрать информацию о книгах, которые закуплены в 1 -м квартале и имеют тематику Экономика. Для этого переместить значение Экономика из ячейки В22 в ячейку В21 (т.е. при расположении условий отбора на одной строчке формируется их связь по правилу И). Сделать текущей любую ячейку в области исходной таблицы и выполнить команду Данные — Дополнительно, выбрать поле Диапазон условий, очистить его и ввести в него диапазон $ A $20:$ I $22 (диапазон ячеек, в котором заданы усло­вия отбора).

6. Отобрать информацию о книгах, цена расхода которых больше или равна 130, но меньше 180 рублей.

7. Отобрать информацию о книгах, тематика которых начинается с бу­квы К.

8. Отобрать информацию о книгах, которые закуплены в 1, 2 и 3-м квар­талах.

9. Отобрать информацию о книгах, которые закуплены в 1 -м квартале по тематике Экономика, а во 2-м квартале по тематике Компьютеры.

Задание 4 Создание сводной таблицы.

1. Скопировать исходную таблицу с Листа 1 на Лист 4, переименовать Лист 4 в Сводная таблица.

2.Необходимо получить на листе не используя формулы Сумму количества прихода, Сумму количества расхода и сумму остатка. Для этого установить курсор в пределах таблицы и вставить сводную таб­лицу вкладка Вставить –Сводная таблица. Откроется новый лист с допол­нительным окном (Список полей сводной таблицы), в нем необходимо пометить поля: Наименование товара (в строки), Кол-во прихода, Кол-во расхода и Сумма остатка (в столбцы). Должна появиться следующая таблица вида:

Задание 5 Консолидация данных.

1. Переименовать Лист 5 в 1 квартал, Лист 6 во 2 квартал, Лист 7 в 3 квартал, Лист 8 в 4 квартал.

2. Скопировать исходную таблицу с Листа 1 на лист 1 квартал и удалить часть таблицы, оставив данные только 1 кв.

3. Скопировать исходную таблицу с Листа 1 на лист 2 квартал и удалить часть таблицы, оставив данные только 2 кв.

4. Скопировать исходную таблицу с Листа 1 на лист 3 квартал и удалить часть таблицы, оставив данные только 3 кв.

5. Скопировать исходную таблицу с Листа 1 на лист 4 квартал и удалить часть таблицы, оставив данные только 4 кв.

6. Перейти на Лист 9, переименовать Лист 9 в Консолидация. Выбрать команду Данные—Консолидация, в окне нажать , последовательно выбрать таблицы с каждого листа, нажимая кнопку Добавить,  установить ссылки на таблицы, установит все флаги и нажать кнопку ОК

Результат выполнения Консолидации.

ПРАКТИЧЕСКАЯ РАБОТА

« Связи между файлами и консолидация данных в MS Excel »

          

Цель работы

1. Получить навыки создания итоговых таблиц.

2. Получить навыки создания транспонированных и связанных таблиц.

      

2 Формируемые образовательные результаты

  Образовательные результаты У2, У3, З4, ОК5, ПК1.2

 

Обеспеченность занятия

ü Персональный компьютер с установленными программами Microsoft Office.

ü Принтер.

 

4 Вопросы для самоподготовки

1. Для каких целей используются итоговые таблицы?

2. Как создаются итоговые таблицы?

3. Какая таблица называется транспонированной?

4. Как создать транспонированную таблицу?

5. Как осуществить группировку в таблице?

6. Перечислите способы связывания рабочих листов.

7. Для чего предназначено связывание рабочих листов?

8.Напишите синтаксис для формулы с внешней ссылкой.

9. Каким образом можно просмотреть список связанных файлов?

10. Перечислите виды внедренных объектов.

 

Литература

Мочула В.Г. Excel 2007. Расчеты и анализ: учебник / В.Г. Мочула. - Москва, ФЕНИКС, 2010.

Содержание заданий

Задание 1 Создание итоговой таблицы

Задание 2 Использование таблиц промежуточных итогов

Задание 3 Транспонирование таблицы

Задание 4 Группировка

Задание 5 Создание таблиц с исходными данными

Задание 6 Прямое связывание листов

Задания и инструкции по выполнению

Задание 1 Создание итоговой таблицы.

1. Создать файл в MS Excel. Присвоить имя файлу ПЗ №5

2. Переименовать Лист 1 в Итоговая таблица и создайте таблицу по образцу:

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

4. Необходимо получить на листе не используя формулы общее количество акций и их общую стоимость для каждого акционера. Для этого:

4.1. Скопировать исходную таблицу ниже.

4.2. Осуществить сортировку по столбцу ФИО.

4.3. Установить курсор в пределах таблицы и вставить итоговую таб­лицу Данные –Промежуточные итоги.

4.4. В форме установить: При каждом изменении в -- ФИО; Операция— Сумма; установить флаги полям- Кол-во и Общая стоимость. Результат выполнения задания:

Примечание. Перед применением итогов исходная таблица копируется.

Задание 2 Использ ование таблиц промежуточных итогов.

1. Используя таблицы промежуточных итогов получить:

1.1. Промежуточные итоги по Выпуску акций сумму количества акций.

         1.2. Промежуточные итоги по ФИО - среднее количество акций.

        1.3. Промежуточные итоги по Видам акций - среднюю стоимость акции.

Задание 3 Транспонирование таблицы.

1. Переименовать Лист 2 в Транспонированная таблица.

2. Скопировать исходную таблицу с Листа 1, используя команды Вставить — Специальная вставка —установить флаг Транспонировать. Строки таблицы станут столбцами.

Задание 4 Группировка.

1. Переименовать Лист 3 в Группировка.

2. Скопировать Транспонированную таблицу.

3. Осуществить группировку, используя команды Данные—Группировать--Столбцы.

Задание 5 Создание таблиц с исходными данными.

1.Переименовать рабочие листы: Лист 4 – Закупка, Лист 5 – Реализация, Лист 6 – Остаток на складе.

2.На рабочем листе " Закупка " создать таблицу по образцу:

3.Рассчитать данные столбца Всего за полугодие и Сумма.

4.На рабочем листе " Реализация " создать таблицу копированием исходной и изменить данные по образцу:

5.На рабочем листе "Остаток на складе" создать таблицу по образцу:

Задание 6 Прямое связывание листов.

1. В таблице Остаток на складе, используя прямое связывание листов, ввести формулы для расчета столбцов Количество и Сумма, для этого:

1.1.Установить курсор в ячейку В3, ввести знак равно «=»

1.2. Перейти на лист Закупка произвести щелчок мышкой по ячейке Н3, ввести знак минус «-», перейти на лист Реализация произвести щелчок мышкой по ячейке Н3.

1.3. Нажать клавишу Enter.

1.4. «Растащить» формулу в ячейки В4:В7.

1.5. Аналогично рассчитать столбец Сумма.

2.Изменить любые значения в таблице Количество закупленной продукции, Количество реализованной продукции  пронаблюдать изменения в таблице Остаток на складе.

3. Переименовать рабочие листы: Лист 7   в Лист Специальная вставка.

4. Осуществить связывание листов по средствам Специальной вставки.

ПРАКТИЧЕСКАЯ РАБОТА

« Экономические расчеты в MS Excel. Анализ экономических показателей деятельности предприятия »

          

Цель работы

1. Изучение технологии подбора параметра при обратных расчетах, технологии поиска решения для задач оптимизации.

2 Формируемые образовательные результаты

   Образовательные результаты У2, У3, З4, ОК5, ПК1.2

Обеспеченность занятия

ü Персональный компьютер с установленными программами Microsoft Office.

ü Принтер.

4 Вопросы для самоподготовки

1. В каких случаях целесообразно использовать инструмент Подбор параметра?

2. Запишите алгоритм заполнения окна Подбор параметра.

3. В каких случаяхиспользуется инструмент Поиск решения?

4. Запишите алгоритм заполнения окна Поиск решения.

5. Чем отличаются методы Подбор параметра и Поиск решения?

6. Какие виды отчетов формирует Поиск решения?

7. Как осуществить дополнительную настройку Поиска решения?

 

Литература

Мочула В.Г. Excel 2007. Расчеты и анализ: учебник / В.Г. Мочула. - Москва, ФЕНИКС, 2010.

Содержание заданий

Задание 1 Изучение постановки задачи для расчета суммы кредит для закупки деталей.

Задание 2 Расчет суммы кредит для закупки деталей.

Задание 3 Изучение постановки задачи для расчета штатного расписания.

Задание 4 Расчет штатного расписания.

Задания и инструкции по выполнению

Задание 1 Изучение постановки задачи для расчета суммы кредит для закупки деталей.

Предприятию необходимо получить кредит для закупки товара. Коммерсант должен оценить условия получения кредита, провести всесторонний анализ возможных вариантов, а затем заключать договора на приобретение деталей. Известно, что на покупку 15 единиц товара по цене 3500 руб. за штуку был получен кредит на 24 месяца под 12% годовых. Перед коммерсантом стоит задача провести анализ возможностей предприятия и ответить на несколько вопросов.

ü Какой платеж следует осуществлять ежемесячно при покупке 15 единиц товара?

ü Что будет, если потребуется приобретать не 15, а 19 единиц товара при тех же условиях?

ü Как изменится платеж по кредиту, если изменится стоимость товара при том же количестве?

ü Какие возможности есть у коммерсанта, если стоимость единицы товара будет колебаться в некоторых пределах?

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

ü Снизить размер кредита за счет уменьшения количества закупаемого товара

ü Продлить срок выплаты кредитной задолженности.

ü Добиться снижения кредитной ставки.

Для решение проблемы использовать финансовую функцию ПЛТ.

Примечание: Функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

Синтаксис ПЛТ(ставка;кпер;пс;бс;тип)

Ø Ставка — процентная ставка по ссуде.

Ø Кпер — общее число выплат по ссуде.

Ø Пс — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.

Ø Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нолю), т. е. для займа, например, значение бс равно 0.

Ø Тип — число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.

Задание 2 Расчет суммы кредит для закупки деталей.

1. Построить таблицы по образцу:

2. Записать исходные данные в таблицу Потребности деталей.

3. Рассчитать значение ячейки Сумма в ячейке B 6.

4. Задать условия выплаты кредита в таблице Платежи по формулам:

5. Занести данные (аргументы) функции ПЛТ

 

6. Провести анализ закупаемого товара, если известно, что ежемесячная выплата составляет 1700 руб. Для этого вызвать надстройку Подбор параметра. Данные-Анализ «что-если»-Подбор параметра:

ü ввести в диалоговое окно условия, которые удовлетворяют заемщика;

ü проанализировать результат, который отображается на диалоговом окне;

ü записать в отчет полученный результат.

7. Провести анализ суммы кредита, если известно, что необходимо закупить 19 единиц товара и срок выплаты составляет 13 месяцев. Для этого:

ü скопировать исходные расчетные таблицы на Лист 2;

ü в ячейку В5 занести значение 19;

ü ввести в диалоговое окно условия;

ü проанализировать результат, который отображается на диалоговом окне;

ü записать в отчет полученный результат.

Задание 3 Изучение постановки задачи для расчета штатного расписания.

Используя режим подбора параметра, определить штатное расписание фирмы.

Известно, что в штате фирмы состоят: 6 курьеров, 8 менеджеров, 10 экспедиторов, 3 заведующих отделами, 1 гл. бухгалтер, 1 программист, 1 системный аналитик, 1 генеральный директор.

Общий месячный фонд заработной платы составляет 100000 рублей.

Определить, какими должны быть оклады сотрудников.

Каждый оклад является линейной функцией от оклада курьера, а именно:

зарплата = Ai * x + Bi, где х – оклад курьера;

Ai и Bi – коэффициенты, показывающие:

Ai – во сколько раз превышается значение х;

Bi – на сколько превышается значение х.   

Задание 4. Расчет штатного расписания.

1. Открыть редактор электронных таблиц MS Excel.

2. Создать таблицу штатного расписания ООО «Сибиряк» по приведенному

ниже образцу.

 

3. В ячейку Е3 занести произвольное число (это переменная х).

4. Рассчитать зарплату сотрудников. В ячейку D6 внесите формулу

 = B6*$Е$3+С6 (адрес ячейки Е3 задан в виде абсолютной ссылки). Скопировать

формулу в диапазон D7:D13.

5. Рассчитать заработную плату всех сотрудников, работающих в данной должности (столбец F).

6. В ячейке F14 вычислить суммарный фонд заработной платы.

ПРАКТИЧЕСКАЯ РАБОТА

« Использование финансовых функций в экономических расчетах »

          

Цель работы

1. Приобрести навыки работы использования электрон­ных таблиц как хранилищ информации на основе обработки списков и организации выборки данных в Microsoft Excel.

        

2 Формируемые образовательные результаты

    Образовательные результаты У2, У3, З4, ОК5, ПК1.2

Обеспеченность занятия

ü Персональный компьютер с установленными программами Microsoft Office.

ü Принтер.

4 Вопросы для самоподготовки

1. Какие операции обработки списков можно выполнить с помощью электронной таблицы?

2. Для чего используются сводные таблицы?

3. Опишите технологию построения и редактирования сводной таблицы.

4. Какие функции используются для выборки информации из списка?

5. Как сводная таблица помогает в анализе данных?

Литература

Мочула В.Г. Excel 2007. Расчеты и анализ: учебник / В.Г. Мочула. - Москва, ФЕНИКС, 2010.

Содержание заданий

Задание 1 Создание списка Клиенты

Задание 2 Создание списка Товары

Задание 3 Создание бланка заказа

Задания и инструкции по выполнению

Задание 1 Создание списка Клиенты.

1. Открыть программу электронных таблиц Microsoft Excel.

2. Переименовать Лист 1 в База данных, для этого дважды щелкните мышью по ярлычку и введите новое имя.

3. Создать заголовки к столбцам таблицы по образцу

 4. Выделить столбец J и отформатировать столбец, выбрав процентный формат.

 5. Залить светло-серым цветом строку заголовка.

 6. Для ввода данных необходимо воспользоваться специальным диалоговым окном – Формой данных . Для этого на панели Быстрого доступа следует выбрать команду Форма. В диалоговом окне формы данных рядом с названиями полей создаваемого списка находятся поля ввода, в которые нужно вводить данные.

7. Ввести в соответствующие поля данные о клиентах, завершая ввод каждой записи нажатием кнопки Добавить. Переход между отдельными полями ввода осуществляется посредством щелчка мыши или нажатием клавиши Tab. После ввода последней записи щелкните на кнопке Закрыть.

 8. Отсортируйте данные в алфавитном порядке по столбцу Названиям фирмы.

 9. Выделить столбец С и в строке формул присвоить имя Код, столбцу В-Фирма, столбцу J – Скидка.

Задание 2 Создание списка Товары.

1. Перейти на Лист 2. Переименовать Лист 2 в Товары.

2. Создать таблицу по образцу.

4. Выделите столбец A и в строке формул присвоить имя Номер, столбцу B – Товар, столбцу C - Цена.

Задание 3 Создание списка Заказы.

Список заказов должен помочь в управлении данными обо всех выполненных с начала года заказах. При этом нужно максимально ав­томатизировать процедуру ввода.

1. Перейти на Лист 3. Переименовать Лист 3 в Заказы.

2. Создать структуру базы данных. Для этого в ячейках A1-L1 указать следующие названия полей: Месяц, Дата, Номер заказа, Номер товара, Наименование товара, Количество, Цена за ед., Код заказчика, Название фирмы, Сумма заказа, Скидка, Уплачено.

3. Выделить по очереди столбцы и ввести в поле имени имена В- Дата, С- Заказ, D- Номер2,

Е- Товар2, F- Количество, G- Цена2, Н- Код2, I- Фирма2, J- Сумма, К- Скидка2, L- Оплата:

4. Определить и присвоить форматы для полей: Дата— Дата; Цена, Сумма заказа, Скидка, Уплачено— Денежный.

5. Данные в таблицу НЕ ВВОДИТЬ!

6. Создать расчетные формулы:

6.1.  В ячейку Е2  ввести формулу =ЕСЛИ($D2="";"";ПРОСМОТР($D2;Номер;Товар))

ПРИМЕЧАНИЕ: Данная формула требует небольших пояснений. Функция ЕСЛИ проверяет содержимое ячейки D2. Если в ячейке D2 данные отсутствуют, то в ячейка Е2 также останется незаполненной. Если же в ячейку D2 уже введен номер товара, то будет выполняться поиск номера товара в диапазоне Номер и в ячейку D2 возвратится соответствующее значение диапазона Товар. Для этого используется функция ПРОСМОТР.

6.2. В ячейку G 2 ввести формулу = ЕСЛИ($D2="";"";ПРОСМОТР($D2;Номер;Цена))

Скопировать формулу до строки 15

ПРИМЕЧАНИЕ: Целесообразно скопировать формулу из ячейки E2 в ячейку G2 и затем только изменить имя диапазона.

6.3. В ячейку I 2 ввести формулу = ЕСЛИ($ H 2="";"";ПРОСМОТР($ H 2;Код;Фирма))

Скопировать формулу до строки 15

6.4. В ячейку J 2 введите формулу =ЕСЛИ(F2="";"";F2*G2)

Скопировать формулу до строки 15

6.5. В ячейку K 2 ввести формулу самостоятельно. Скопировать формулу до строки 15

6.6. В ячейку L 2 введите формулу = ЕСЛИ(J2="";"";J2-J2*K2)

Скопировать формулу до строки 15.

7.Заполнить таблицу данными, используя Форму.

Задание 4 Создание бланка заказа.

1. Переименуйте Лист 4 в Бланк.

2.Создать бланк заказа по образцу.

 3. Ввести формулы в бланк заказа

3.1. В ячейку F 2 ввести формулу =ЕСЛИ($D$2="";"";ПРОСМОТР($D$2;Заказ;Дата))

Помните, что формат ячейки F 2 должен быть Дата

3.2.В ячейку D 4 ввести формулу = ЕСЛИ($D$2="";"";ПРОСМОТР($D$2;Заказ;Фирма2))

3.3. В ячейку H 4 ввести формулу =ЕСЛИ($D$2=""; ""; ПРОСМОТР($D$2;Заказ;Код2))

3.4. Для остальных полей Бланка заказа формулы ввести самостоятельно.

ПРАКТИЧЕСКАЯ РАБОТА

« Средства анализа данных в таблицах. Диаграммы. Графики » Совместное использование нескольких рабочих листов

Цель работы

1. Освоить процесс создания оборотно-сальдовой ведомости с помощью MS Excel.

    2 Формируемые образовательные результаты

   Образовательные результаты У2, У3, З4, З9, ОК5, ПК2.1

 

Литература

Мочула В.Г. Excel 2007. Расчеты и анализ: учебник / В.Г. Мочула. - Москва, ФЕНИКС, 2010.

Содержание заданий

Задание 1 Создание оборотно-сальдовой ведомости

Задание 2 Проверка введенных формул, отображение зависимых и влияющих ячеек

Задание 3 Создание учетного регистра

Задание 4 Проверка работы оборотно-сальдовой ведомости (соответственно варианту)

Задания и инструкции по выполнению

З адание 1 Создание Оборотно-сальдовой ведомости.

1. Открыть программу электронных таблиц Microsoft Excel.

2. Переименовать Лист1 в Оборотно-сальдовая ведомость, для этого дважды щелкните мышью по ярлычку и введите новое имя.

3. Создать таблицу по образцу.

4. Рассчитать суммарный оборот. Для этого необходимо рассчитать суммарный оборот по всем операциям в столбце С по формуле: =СУММ(СЗ:С9).

3. Автоматизировать занесение суммы из столбца С (Сумма) в те ячейки, которые определяются номерами счетов в столбцах D (Дебит), для этого следует использовать функцию ЕСЛИ. Установить курсор в ячейку F 3 и ввести формулу = ЕСЛИ(D 3= F $1;$С3; "")

4. Автоматизировать занесение суммы из столбца С (Сумма) в те ячейки, которые определяются номерами счетов в столбцах Е (Кредит). Установить курсор в ячейку G 3 и ввести формулу

= ЕСЛИ(E 3= F $1;$С3; "").

5. Ввести формулы для остальных ячеек таблицы. Для того чтобы быстро распространить логическую формулу на остальные столбцы таблицы, в частности для дебета счета 55 (ячейка H 1) следует скопировать формулу из ячейки F3 в ячейку H3, формула будет модифицирована следующим образом: =ЕСЛИ($ D 3=Н$1;$СЗ;"").

6. Аналогичным образом создать формулы для остальных счетов. Результаты приведены в таблице.

7. После заполнения журнала операций необходимо провести бухгалтерские операции, т.е. рассчитать суммы в столбцах Дебет и Кредит каждого счета. Для этого в результирующую строку каждого столбца поместить суммирующую формулу.

Задание 2 Проверка введенных формул, отображение зависимых и влияющих ячеек.

1. Для выполнения контроля ввода формул необходимо проверить правильность их ввода, для этого следует использовать команду Формула —лента Зависимости формул—Проверка наличия ошибок.

 

2. Отобразить зависимые и влияющие ячейки Формула —лента Зависимости формул—Зависимые ячейки / Влияющие ячейки.

3. Для проверки правильности введенных сумм и формул в ячейку T 10 ввести формулу

= СУММ(F 10: S 10) должен получиться результат равный значению ячейки С10, а именно 39800  

4. Найти сумму по каждой строку таблицы, результат отображать в столбце U. Рассчитать сумму столбца U. Результат должен равняться значению в ячейке T 10 и в ячейке С10.

Задание 3 Создание учетного регистра.

1. Переименовать Лист2 в Учетный регистр, для этого дважды щелкните мышью по ярлычку и введите новое имя.

2. Создать таблицу по образцу. Для быстроты создания таблицы рекомендуется скопировать интервал А9:Е9 с листа Оборотно-сальдовая ведомость на лист Учетный регистр

3. Просуммировать операции с одинаковыми счетами, для этого в ячейку D 13 столбца Дебет ввести формулу =СУММЕСЛИ