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

Из предложенного документа Таблица комплектующих копируем данную таблицу в Новую книгу MS Excel.

Из задания копируем список предложенных деталей в созданный документ MS Excel.

Фильтруем таблицу комплектующих с помощью Расширенного фильтра (Данные \ Фильтр \ Расширенный фильтр):

В окне Расширенный фильтр выбираем:

Исходный диапазон: вся таблица вместе с заголовками.

Диапазон условий: скопированный список деталей из задания В1: С11 (Приложение № 3).

Фильтруем список на месте.

Получаем нужную таблицу для дальнейшей работы А1: F37.

Добавляем в нашу таблицу следующие столбцы:

Цена продаж:

В соответствии с данным в варианте задания условием получаем формулу:

ЕСЛИ (C2<10; C2+C2*0,35; ЕСЛИ (И (C2>=10; C2<=20); C2+C2*0,3; ЕСЛИ (C2>20; C2+C2*0,25))) в которой используем функции ЕСЛИ, И, УМНОЖЕНИЕ.

Доход:

Получаем путём умножения Цены продаж и Количества деталей. Получаем: G2* K2.

Чистая прибыль:

Получаем как разность Дохода и произведения Цены изготовления на Количество деталей. Получаем: H2- C2* K2.

Срок годности:

Показывает дату, до которой проработает данная деталь. При создании формулы учитываем Срок хранения и Дату изготовления деталей. Для этого в ячейке J2 вводим формулу: (F2*30) + D2.

То есть количество месяцев умножаем на количество дней в месяце, в среднем. Получаем срок хранения в днях и прибавляем эти дни к Дате изготовления. При этом в ячейке J2 устанавливаем формат ячейки - Дата. (Формат \ Ячейки \ Числовой формат - Дата).

Количество деталей:

Автоматически переводим из варианта задания. Для этого в ячейке K2 вводим формулу:

ИНДЕКС (Лист1! $B$2: $C$12; ПОИСКПОЗ (Лист2! B2; Лист1! $B$2: $B$12; 0);

2)

В данной формуле:

Лист1! $B$2: $C$12 - абсолютная ссылка на массив данных нам по условию деталей.

ПОИСКПОЗ (Лист2! B2; Лист1! $B$2: $B$12; 0) - выдаёт № строки в искомом массиве (смотри выше), в котором находится искомое значение.

Лист2! B2 - абсолютная ссылка на искомое значение - Обозначение детали.

Лист1! $B$2: $B$12 - абсолютная ссылка на просматриваемый массив.

0 - тип сопоставления (то есть значение равно искомому).

2 - № столбца в просматриваемом массиве В2: В12.

Примечание.

Все приведённые выше формулы для второй строки аналогичным способом применяем для следующих строк до 37-ой.

 

Выполнение индивидуального задания

 

Вычисление чистой прибыли каждого завода

Для выполнения этого задания воспользуемся “услугами" Сводной таблицы (Данные \ Сводная таблица).

В появившемся окне Мастер сводных таблиц выбираем пункт: Создать таблицу на основе данных, находящихся в списке или базе данных MS Excel.

Нажимаем Далее.

Затем указываем диапазон, содержащий исходные данные, то есть основную таблицу: Лист2! $ A$1: $ K$37.

Нажимаем Далее.

На 3-ем шаге в область Строка “перетаскиваем” графу Изготовитель (название заводов-изготовителей). В область Данные - графу Чистая прибыль.

Нажимаем Далее.

На 4-ем шаге выбираем место размещения таблицы: Существующий лист, в нашем случае, ячейка А44.

Нажимаем Готово.

Нахождение заводов, имеющих чистую прибыль выше средней.

Для начала для этого находим среднюю прибыль заводов, используя функцию СРЗНАЧ. В ячейке D44 вводим: СРЗНАЧ (B46: B54).

Средняя прибыль получилась - 53,64

Далее, в отдельную таблицу (Е43: Е48) вводим необходимые условия.

Наконец, обращаемся к “услугам” Расширенного фильтра. (Данные \ Фильтр \ Расширенный фильтр):

В окне Расширенный фильтр выбираем:

Исходный диапазон: Лист2! $ A$45: $ B$54, то есть полученную ранее сводную таблицу.

Диапазон условий: Лист2! $E$44: $E$45, то есть Всего>53,64

Копируем результат в другое место: Лист2! $ A$58: $ B$58

Нажимаем OK.

Нахождение заводов, имеющих чистую прибыль ниже средней.

Вновь обращаемся к Расширенному фильтру:

Исходный диапазон: Лист2! $ A$45: $ B$54

Диапазон условий: Лист2! $ E$47: $ E$48

Копируем результат в другое место: Лист2! $ A$65: $ B$65

Нажимаем OK.

Построение графика, показывающего заводы с чистой прибылью ниже средней.

Для построения диаграммы применим Мастер диаграмм (Вставка \ Диаграмма).

На 1-ом шаге выбираем:

Стандартную диаграмму - Круговая - Объёмный вариант разрезной круговой диаграммы.

Нажимаем Далее.

На 2-ом шаге выбираем:

Диапазон данных: Лист2! $ A$65: $ B$70, то есть построенная ранее таблица - Заводы с прибылью ниже средней.

Нажимаем Далее.

На 3-ем шаге:

Вводим Название диаграммы - Заводы с прибылью ниже средней.

Добавляем Легенду справа, подписывая в ней названия заводов

В разделе Подписи данных выбираем Значение.

Нажимаем Далее.

На 4-ом шаге выбираем: Поместить диаграмму на имеющемся листе - Лист2.

Нажимаем Готово.

Далее размещаем готовую диаграмму на Листе2.

Диаграмма готова. Щелчком правой кнопкой мыши по полю диаграммы можно изменять стиль оформления диаграммы, её значения, параметры, тип и т.д.

Заключение

 

Наша курсовая работа позволила укрепить знания в применении таких программ, как MS EXCEL и MS WORD, что немаловажно не только для любого образованного человека, но и для нас - студентов, обучающихся по специальности 180100 - “Кораблестроение." Именно программы Microsoft Word и Excel являются самыми распространённым в работе с документами и таблицами. Для этих программ предусмотрен широкий выбор средств автоматизации, упрощающих выполнение типичных задач, что чрезвычайно удобно и в чём мы могли убедиться, выполняя курсовую работу. Именно поэтому данное семейство Office корпорации Microsoft используется на многих предприятиях и в частных фирмах.

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

В ходе работы мы продемонстрировали как с достаточной лёгкостью можно обработать большое количество информации, провести необходимые расчёты и составить подробный отчёт по проделанной работе, представленный как в виде таблиц, так и в виде диаграмм, что является очень удобным и наглядным средством для работы и способом представления обработанных данных.

Список литературы

 

1. Додж М., Стинсон К. “Эффективная работа с Microsoft Excel 2000” // Санкт-Петербург: издательство “Питер”. - 2000.

2. Лавренов С.М. “Excel. Сборник примеров и задач" // Москва: “Финансы и статистика". - 1999.

3. Мэнсфилд Р. “EXCEL 97 для занятых" // Москва. - 1997.

4. Острейковский В.А. “Информатика” // Москва: “Высшая школа". - 1999.

5. Рычков В. “Excel 2000” // Санкт-Петербург: “Питер"; самоучитель. - 2000.

6. Симонович С.В. “Информатика. Базовый курс” // Санкт-Петербург “Питер”; учебник для ВУЗов. - 2001.

7. “MS EXCEL 97. Наглядно и конкретно" // Москва; справочник. - 1997.

Приложение

 

Минимум по полю Цена (Изготовл), руб
Обозначение	Итог	Количество	Цена деталей	Срок годности
Д 226Б	12,4	2	24,8	12.11 2013
Д 814А	12,7	2	24,8	29.05.2012
Д223	12,9	4	49,6	16.04.2013
КД208А	12,9	2	24,8	16.04.2013
КМ - 6 470Ф	21,8	16	198,4	09.07.2006
КТ-2 0,33 мкФ	24,9	4	49,6	29.11.2007
КТ209	9,6	2	24,8	02.05.2011
МЛТ - 0,5 150 Ом	4,8	2	24,8	20.03.2012
МЛТ - 0,5 20 кОм	5	12	148,8	03.12.2011
МЛТ - 0,5 5,6 кОм	4,9	2	24,8	04.11.2014
Общая цена устройства			595,2	09.07.2006