Практическое занятие по Теме 1.1. Практикум учитывается в итоговой оценке по дисциплине. В рамках Практикума Вам необходимо выполнить задания в программе ms excel
 Скачать 0.72 Mb.
|
|
Практикум в MS Excel по Теме 1.1 Внимание! Данный практикум учитывается в итоговой оценке по дисциплине. В рамках Практикума Вам необходимо выполнить задания в программе MS Excel. Если у Вас возникают трудности при выполнении заданий, Вы можете воспользоваться решением после каждого задания. Начало работы в MS Excel в целях складского учета 1. Необходимо создать файл с любым названием. 2. Заполнить справочники (поставщики, покупатели). Они должны иметь примерный вид (рисунок 1.3) Рисунок 1.3 – Пример оформленного справочника 3. Закрепить заголовки (на вкладке «Вид» сделать клик по кнопке «Закрепить области») 4. Создать вспомогательный справочник пунктов отпуска товаров (если организация имеет несколько торговых точек – складов) – рисунок 1.4 Рисунок 1.4 – Пример оформленного вспомогательного справочника 5. Создать таблицу для номенклатуры (рисунок 1.5). Заголовки таблицы: «Наименование товара», «Сорт», «Единица измерения», «Характеристика», «Комментарий». 5.1 Выделяем диапазон этой таблицы. В поле «Имя», расположенном прямо над ячейкой с названием «А», вводят слово «Таблица 1». 5.2 Так же поступают с соответствующим диапазоном на листе «Поставщики». При этом указывают «Таблица 2». 5.3 Фиксации приходных и расходных операций производится на двух отдельных листах. Они помогут вести складской учет в Excel. Рисунок 1.5 – Пример оформленной таблицы для номенклатуры товара Программа «Склад» (Excel) рассчитывает стоимость автоматически, с помощью математических формул. Создаем оборотную ведомость 1. Все справочники требуется отформатировать в виде таблицы и для столбца «Наименование» создать выпадающий список. 1.1 выделяем столбец (кроме шапки); 1.2 находим вкладку «Данные»; 1.3 нажимаем на иконку «Проверка данных»; 1.4 в поле «Тип данных» ищем «Список»; 1.5 в поле «Источник» указываем функцию «=ДВССЫЛ(«номенклатура!$A$4:$A$8»)». 1.6 Выставляем галочки напротив «Игнорировать пустые ячейки» и «Список допустимых значений». 1.7 Если все сделано правильно, то при заполнении 1-го столбца можно выбирать название товара из списка. 1.8 При этом в столбце «Ед. изм.» появится соответствующее значение. 1.9 Точно так же создаются автозаполнение для столбцов «Код» и «Поставщик», а также выпадающий список. 1.10 Для заполнения графы «Стоимость» используют формулу умножения. Она должна иметь вид «= цена * количество». 1.11 Нужно также сформировать выпадающий список под названием «Точки учета», который будет указывать, куда был отправлен поступивший товар. Это делается точно так же, как в предыдущих случаях. Шаги отображения отчета по итогам работы: 1. Для этого начинаем работать с соответствующей таблицей и в начало временного периода выставляем нули, так как складской учет вести еще только собираемся. 2. Если же его осуществляли и ранее, то в этой графе должны будут отображаться остатки. 3. При этом единицы измерения и наименования товаров должны браться из номенклатуры. 4. Чтобы облегчить складской учет, программа бесплатная должна заполнять столбцы «Отгрузки» и «Поступление» посредством функции СУММЕСЛИ. 5. Остатки товаров на складе считаем, используя математические операторы. Задание 1 1.1 Создать «Оборотную ведомость по остаткам» в программе MS Excel, на основании данных таблиц 1–4; 1.2 Применить проверку данных (проверку вводимых значений); 1.3 Использовать математическую функцию СУММЕСЛИ при формировании итоговых значений в оборотной ведомости. Таблица 1 – Данные для заполнения первых трех листов книги MS Excel Поставщики Покупатели Точки учета ООО «Альфа» ООО «АВК» Склад 1 ООО «Аметист» ООО «Булочная» Склад 2 ООО «Вологда» ООО «Восход» Склад 3 ООО «Глеб» ООО «Главная газета» Склад 4 ООО «ДомостройПлюс» ООО «ДомУЮТ» Магазин 1 ООО «Елкин дом» ООО «Зонт» Магазин 2 ООО «Зима» ООО «Камень» Магазин 3 ООО «Ивушка» ООО «Лавина» Магазин 4 ООО «Копылов» ООО «Молоко» Выдача покупателю ООО «Лоскут» ИП Коробов У. Ю. Брак ООО «Мир» ИП Клоков А. М. ООО «Титов» ИП Устинов А. Р. ИП Александров И. Ю. ИП Шитов К. Е. ИП Зиновьева А. Д. ИП Шмелева К. О. ИП Соколова А. М. ИП Щетинина Р. Л. ИП Штинова К. Д. ИП Яковлева П. И. Таблица 1.2 – Данные для заполнения четвертого листа книги MS Excel Наименование Цена Ед.изм. Характеристика Количество Эмаль ПФ-115 (Радуга) 135 шт. Белая глянцевая 10 Эмаль универсальная 80 шт. Голубая 15 Эмаль для пола ПФ-266 116 шт. Красно-коричневая 10 Эмаль алкидная ПФ-115 (Усадьба) 545 шт. Белая 20 Эмаль ПФ-115 Лакра 182 шт. Зеленая 15 Эмаль ремонтная 400 шт. Слоновая кость 12 Эмаль полиуретановая Полифлекс 1101 442 шт. Карта цветов 10 Эмаль ХП-1405 фасадная 338 шт. Разные цвета 4 Эмаль краска ПФ-115 ГОСТ 85 шт. Разные цвета 8 Эмаль «Омега» огнезащитная 99 шт. Светло-молочная 26 Эмаль акриловая ТМ Оптимист 740 шт. Супербелая полуглянцевая 8 Таблица 1.3 – Данные для заполнения пятого листа книги MS Excel Наименование Дата № накла дной Поставщик Код Точка учета Ед. изм. Кол- во Цена Стоим ость Эмаль ПФ-115 (Радуга) 04.04.2018 25 ООО «ДомостройПл юс» 11-005 Склад 1 шт. 10 135 1350 Эмаль ПФ-115 (Радуга) 05.04.2018 26 ООО «Ивушка» 11-010 Склад 2 шт. 2 545 1090 Эмаль ремонтная 10.04.2018 98 ООО «Лоскут» 11-006 Склад 3 шт. 5 400 2000 Эмаль ПФ-115 Лакра 11.04.2018 78 ООО «Лоскут» 11-008 Склад 4 шт. 8 182 1456 Эмаль ПФ-115 Лакра 18.04.2018 123 ИП Зиновьева А.Д. 11-012 Магазин 1 шт. 12 442 5304 Эмаль ХП-1405 фасадная 28.04.2018 56 ИП Соколова А.М. 11-011 Магазин 3 шт. 12 338 4056 Таблица 4 – Данные для заполнения шестого листа книги MS Excel Наименование Дата № накла дной Покупатель Код Точка учета Ед.из м. Кол- во Цена Стоим ость Эмаль ПФ-115 (Радуга) 01.04.2018 1312 ООО «АВК» 18-001 Склад 1 шт. 2 285 570 Эмаль универсальная 05.04.2018 1313 ООО «АВК» 18-002 Склад 2 шт. 1 147 147 Эмаль универсальная 06.04.2018 1314 ООО «Восход» 18-003 Склад 3 шт. 3 147 441 Эмаль алкидная ПФ- 115 (Усадьба) 07.04.2018 1315 ИП Коробов У. Ю. 18-004 Склад 3 шт. 5 752 3760 Эмаль алкидная ПФ- 115 (Усадьба) 25.04.2018 1316 ИП Устинов А. Р. 18-005 Склад 4 шт. 2 752 1504 Эмаль ремонтная 26.04.2018 1317 ООО «Камень» 18-006 Магазин 1 шт. 3 550 1650 Эмаль ремонтная 27.04.2018 1318 ООО «Камень» 18-009 Магазин 1 шт. 4 550 2200 Эмаль ремонтная 28.04.2018 1319 ИП Шитов К. Е. 18-012 Магазин 2 шт. 2 550 1100 Эмаль ремонтная 29.04.2018 1320 ИП Шитов К. Е. 18-014 Магазин 3 шт. 2 550 1100 Эмаль «Омега» огнезащитная 30.04.2018 1321 ИП Шмелева К. О. 18-013 Магазин 3 шт. 5 200 1000 Эмаль «Омега» огнезащитная 30.04.2018 1322 ИП Шмелева К. О. 18-015 Магазин 4 шт. 3 200 600 РЕШЕНИЕ: 1. Создать Лист MS Excel. 2. Переименовать Лист 1 на «Поставщики». 3. Подписать ячейку А1 «Поставщики». Внести наименование поставщиков в ячейки А2–А 17 (на основании данных таблицы 1). 4. Переименовать Лист 2 на «Покупатели». 5. На листе «Покупатели» подписать ячейку А1 «Покупатели». Внести наименование покупателей в ячейки А2–А 17 (на основании данных таблицы 1). Что должно получиться (рисунок 1). Рисунок 1 – Вид выполненых 2-5 пунктов заданий* *Цветовая заливка ячеек используется по усмотрению 6. Переименовать Лист 3 на «Точки учета». 7. На листе «Точки учета» подписать ячейку А1 «Точки учета». Внести наименование точек учета в ячейки А2 – А 11 (на основании данных таблицы 1). Что должно получиться (рисунок 2). Рисунок 2 – Вид выполненного 6 и 7 пункта задания 8. Переименовать Лист 4 на «Номенклатура товара». 9. На листе «Номенклатура товара» подписать ячейку А 1 «Номенклатура товара». По второй строке столбцов А – Е внести данные: Наименование, Цена, Ед. изм., Характеристика, Количество. Внести наименование товара в ячейки А 3–А 13 (на основании данных таблицы 2). Что должно получиться (рисунок 3). Рисунок 3 – Вид выполненного 8 и 9 пункта задания 10. Переименовать Лист 5 на «Поступление товара». 11. Оформить табличную часть по образцу из данных таблицы 3. 12. Провести автоматизацию заполнения таблицы «Поступление товара». Необходимо сделать так, чтобы можно было выбирать из готового списка наименование товара, поставщика и точку учета. Остальные показатели вносятся вручную. Стоимость рассчитывается с помощью математического действия. 12.1 Создать выпадающий список для столбца «Наименование» – для этого выделить столбец (без шапки) – выбрать вкладку «Данные» – Инструмент «проверка данных» (рисунок 4). Рисунок 4 – Что должно получиться при выполнении действия 12.2 В поле «Тип данных» выбираем «Список». Сразу появляется дополнительное поле «Источник». Чтобы значения для выпадающего списка брались с другого листа, используйте функцию: =ДВССЫЛ("номенклатура!$A$3:$A$13") и нажмите ОК – рисунок 5. Рисунок 5 – Что должно получиться при выполнении действия 12.3 При заполнении первого столбца таблицы выбрать наименование товара из списка, рисунок 6. Рисунок 6 – Вариант правильно выполненного действия 12.3 12.4 Аналогичным образом (действию в пункте 12.3) проводится проверка данных в столбцах «Поставщик» и «Точка учета», рисунок 7 и рисунок 8. Рисунок 7 – Вариант правильно выполненного действия 12.4 Рисунок 8 – Вариант правильно выполненного действия 12.4 13. Заполнить таблицу «Поступление товара» на основании данных таблицы 3. Что должно получиться (рисунок 9). Рисунок 9 – Вариант правильно выполненного действия 13 14. Рассчитать стоимость товара с помощью формулы (=Н4 * I4), то есть необходимо количество умножить на цену (рисунок 10). Рисунок 10 – Вариант правильно выполненного действия 14 15. Переименовать Лист 6 на «Расход товара». 16. Оформить табличную часть по образцу из данных таблицы 4. 17. Провести автоматизацию таблицы (см. пункт 12). 18. Заполнить таблицу «Расход товара» на основании таблицы 4. 19. Рассчитать стоимость товара. Что должно получиться (рисунок 11). Рисунок 11 – Вариант правильно выполненных 16-19 действий 20. Переименовать Лист 7 на «Оборотная ведомость». 21. Подписать ячейку А1 «Оборотная ведомость». 22. Заполнить шапку таблицы по образцу (рисунок 12). Рисунок 12 – Образец выполненного 22 действия 23. Провести автоматизацию таблицы столбцов «Наименование», «Ед.изм.», «Остаток на начало». 24. Заполнить таблицу по образцу (рисунок 13). Рисунок 13 – Образец для выполнения 24 задания 25. Столбцы «Поступление» и «Отгрузка» рассчитать с помощью функции СУММЕСЛИ. Образец правильно выполненных действий (рисунок 14 и 15). При заполнении столбца «Поступление» – необходимо переходить на лист «Поступление товара», при заполнении столбца «Отгрузка» – на лист «Расход товаров». Рисунок 14 – Образец выполненного 25 действия в части «Поступление» Рисунок 15 – Образец выполненного 25 действия в части «Отгрузка» 26. Рассчитать итоги по столбцам «Остаток на начало», «Поступление», «Отгрузка», с помощью математической функции Автосумма (меню Формулы – Автосумма), рисунок 16. Рисунок 16 – Образец выполненного 26 действия 27. Добавить столбец «Остаток на конец». Рассчитать остатки на конец по строкам (остаток на начало + поступление – выбытие) и итоговую сумму (с помощью функции Автосумма), рисунок 17. Рисунок 17 – Образец выполненного 27 действия Задание выполнено. Задание 2 2.1 Создать «Оборотную ведомость по сумме» в программе MS Excel, на основании данных таблиц 1-4 задания 1; 2.2 Применить проверку данных (проверку вводимых значений); 2.3 Использовать математическую функцию СУММЕСЛИ при формировании итоговых значений в оборотной ведомости. Алгоритм решения аналогичен заданию 1. РЕШЕНИЕ: Действие первое – оформить таблицу на основании задания 1 таблиц 1-4. Применить проверку данных, рисунок 1. Рисунок 1 – Образец оформления таблицы Действие второе – вручную ввести остатки на начало в сумме и рассчитать итоговую сумму (рисунок 2). Рисунок 2 – Образец выполненного 2 действия Действие третье – рассчитать сумму в столбцах поступление и расход (с помощью функции СУММЕСЛИ), рассчитать итоговую сумму, рисунок 3 и 4. Рисунок 3 – Образец выполненного 3 действия Рисунок 4 – Образец выполненного 3 действия Действие четвертое – рассчитать остатки на конец по строкам наименований и итоговую сумму по столбцу «Остаток на конец» (остаток на начало + поступление – расход), рисунок 5. Рисунок 5 – Образец выполненного 5 действия Задание выполнено. |