Содержание
1. Условие задачи...................................................................................... 3
2. Создание исходной таблицы................................................................. 4
3. Формирование «Ведомости
поступления готовой продукции на склад» 4
3.1. Перенос исходных данных............................................................. 4
3.2. Проведение вычислений в
таблице................................................ 5
3.3. Вычисление итоговых данных....................................................... 6
3.4. Итоговый вид «Ведомости
поступления готовой продукции
от различных производителей»..................................................... 6
4. Построение диаграмм........................................................................... 8
4.1. Столбиковая диаграмма
плановых и фактических поступлений
продукции от «Нонолет»................................................................ 8
4.2. Круговая диаграмма суммарной
стоимости фактически
поставленной продукции............................................................... 9
Литература............................................................................................... 11
1. Условие задачи
Имеется таблица 1 данных о поступлении на склад продукции от
различных фирм – производителей.
Таблица
1
|
Фирма – производитель
|
Продукция
|
Цена за единицу, руб.
|
Подлежит поставке по
договору, шт.
|
Фактически поставлено, шт
|
|
|
|
|
|
|
1. Сформировать таблицу 2 «Ведомость поступление готовой
продукции на склад».
Таблица
2
|
Фирма – производитель
|
Продукция
|
Цена за единицу, руб
|
Подлежит поставке по
договору
|
Фактически поставлено
|
Отклонение
|
|
кол-во, шт.
|
сумма, руб.
|
кол-во, шт.
|
сумма, руб.
|
кол-во, шт.
|
сумма, руб.
|
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
Выходной документ должен содержать 15 – 20 записей (3 – 5
фирм – производителей, каждая из которых поставляет по 3 – 5 наименований
продукции).
Расчет в графах 5, 7, 8, 9 в каждой строке таблицы 2
осуществляется в соответствии со
следующей схемой (в квадратных скобках указаны порядковые номера граф):
[5] = [3] *
[4]
[7] = [3] *
[6]
[8] = [4] –
[6]
[9] = [5] –
[7].
2. Таблица 2 должна содержать итоговые данные по каждой
фирме – производителю и общие итого по предприятию в графах 4, 5, 6, 7, 8, 9.
3. Построить столбиковую диаграмму плановых и фактических
поступлений продукции от одного производителя (любого).
4. Построить круговую диаграмму суммарной фактически
поставленной продукции от всех фирм – производителей.
2. Создание исходной таблицы
На пустом листе книги Microsoft Excel создадим структуру в соответствии
с приведенной выше Таблицей 1 и заполним ее следующими исходными данными:
3. Формирование «Ведомости поступления готовой
продукции на склад»
3.1. Перенос исходных данных
Ведомость поступления готовой продукции на склад будем
формировать на отдельном листе книги Microsoft Excel.
В Ведомость поступления готовой продукции на
склад перенесем исходные данные из исходной таблицы.
Скопируем диапазон ячеек A3:E24 из
исходной таблицы в создаваемую.
Добавим столбцы Подлежит поставке по договору, сумма, руб.,
Фактически
поставлено, сумма, руб. и Отклонение, сумма, руб. после
столбцов Подлежит поставке по договору, кол-во, шт., Фактически
поставлено, кол-во, шт. и Отклонение, кол-во, руб.
соответственно.
3.2. Проведение вычислений в таблице
Вычисления в Ведомости поступления готовой продукции от
различных производителей будем проводить на основании формул,
приведенных в задании.
Разберем создание формул для первой строки таблицы.
В ячейку E6
введем формулу =C6*D6[1]. Так как в ячейке C6 записана Цена
за единицу, руб. для первой строки таблицы, а в ячейке D6 записано Количество
поставленной по договору продукции.
В ячейку G6
введем формулу =C6*F6. Так как в ячейке C6 записана Цена за единицу, руб. для первой
строки таблицы, а в ячейке F6
записано Количество фактически поставленной
продукции.
В ячейку H6
введем формулу =D6-F6. Так как в D6
записана Кол-во, которое подлежит поставке по договору, а в F6 – Фактически поставленное
количество.
В ячейку I6
введем формулу =E6-G6. Так как в E6
записана Сумма продукции, которая подлежит поставке по договору, а в F6 – Сумма продукции, которая
фактически поставлена.
Чтобы создать аналогичные расчетные формулы во всех
остальных строках таблицы, будем использовать Автозаполнение[2].
Например, для столбца [5] подведем указатель мыши к правому
нижнему углу ячейки E6,
когда указатель примет вид черного крестика, растянем выделение на диапазон E7 – E26. При этом ссылки на ячейки,
участвующие в формулах, будут
автоматически изменяться для каждой строки.
Аналогично проведем Автозаполнение
для столбцов [8] и [9].
3.3. Вычисление итоговых данных
После строк для каждой фирмы – производителя добавим пустые
строки для расчета суммарных данных.
Например, для первой фирмы добавим пустую строку после
строки 9.
Перейдем в ячейку D10 и нажмем кнопку Автосумма[3]
на панели инструментов. После этого выделим диапазон ячеек D6:D9, в котором хранятся требуемые для суммирования данные.
В результате, в ячейке D10 будет сформирована
формула =СУММ(D6:D9).
Аналогично вычислим суммарные данные по остальным столбцам
для каждой фирмы.
Для расчета итоговых данных по всем фирмам, например, для [4]
столбца в ячейку D33 введем
формулу =D10+D15+D20+D25.
3.4. Итоговый вид «Ведомости поступления готовой
продукции от различных производителей»
После создания всех расчетных формул, описанных выше, окно Microsoft Excel примет
вид:
Для просмотра таблицы в Режиме
просмотра формул выполним пункт главного меню Microsoft Excel Сервис
> Зависимости формул >
Режим проверки формул[4].
В этом режиме таблица имеет вид:
4. Построение диаграмм
4.1. Столбиковая диаграмма плановых и фактических
поступлений продукции от «Нонолет»
Для построения диаграммы запустим Мастер диаграмм[5],
нажав одноименную кнопку на панели инструментов.
На первом шаге запустившегося мастера выберем тип диаграммы Гистограмма.
На следующем шаге мастера создадим 2 ряда – для отображения планового объема поставки и Фактического объема поставки.
Для значений
зададим ячейки D21:D24 и F21:F24, в которых записаны значения плановых и фактических объемов
поставок каждого товара фирмой «Нонолет».
Для Подписей оси Х зададим
ячейки B21:B24, в которых записаны
наименования товаров.
На третьем шаге мастера зададим название диаграммы Плановые
и фактические поставки от «Нонолет», оси X
- Товар, а оси Y – Кол-во, шт.
Столбиковую диаграмму сохраним ни отдельном листе, присвоив
ему соответствующее имя.
Получим диаграмму:
4.2. Круговая диаграмма суммарной стоимости
фактически поставленной продукции
Скопируем требуемые для построения данные в нижние пустые ячейки рабочего листа.
Эту круговую диаграмму также построим при помощи мастера, на
первом шаге которого выберем тип диаграммы Круговая.
Зададим ячейки, в которые были скопированы требуемые данные.
На вкладке Ряд для значений зададим ячейки G36:G40, а для подписей – A36:A40.
Установим флажки Значения и Доли для отображения
соответствующих значений на диаграмме.
Получим круговую
диаграмму:
Литература
1.
Козырев А.А. Самоучитель работы на персональном
компьютере. Учебное пособие. Изд. 2-е, переработанное и дополненное. СПб.:
Изд-во Михайлова В.А., 2000 – 304 с.
2.
д-р Конрад Карлберг Бизнес-анализ с помощью EXCEL.
Издано: 2001, М., Издательский дом "Вильямс", 480 стр.
3.
Левин А. Самоучитель работы на компьютере. Москва,
издательство «Нолидж», 1999 – 624 с.
4.
Фигурнов В.Э. IBM PC для пользователя. Краткий курс. – М. ИНФРА-М, 1999. – 480 с.:
ил.
5.
Эд Ботт, Вуди Леонард Использование Microsoft Office
2000. Специальное издание. Издано:2000,
Вильямс, 1024 стр.
[1] Левин
А. Самоучитель работы на компьютере. Москва, издательство «Нолидж», 1999 – стр. 222
[2]
Козырев А.А. Самоучитель работы на персональном компьютере. Учебное пособие.
Изд. 2-е, переработанное и дополненное. СПб.: Изд-во Михайлова В.А., 2000 –
стр. 47
[3] Эд
Ботт, Вуди Леонард Использование Microsoft Office 2000. Специальное издание. Издано:2000, Вильямс, стр. 563
[4]
Фигурнов В.Э. IBM PC
для пользователя. Краткий курс. – М. ИНФРА-М, 1999. – стр. 224
[5] д-р
Конрад Карлберг Бизнес-анализ с помощью EXCEL. Издано: 2001, М., Издательский
дом "Вильямс", стр. 120