Работа с Microsoft Excel

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

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

Диапазон  условий: скопированный список деталей из задания В1: С11

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

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

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

Цена  продаж:

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

ЕСЛИ (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.

Примечание.

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

5.2 ВЫПОЛНЕНИЕ  ИНДИВИДУАЛЬНОГО ЗАДАНИЯ 

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

По данным таблицы на Лист 3 создаём Сводную таблицу с Расширенным фильтром отсортировывая все детали со сроком годности с текущей даты менее 1 года, с обозначением детали и ценой продаж 

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

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

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

Диапазон  условий: минимальная цена продажи В1: С11

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

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

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

Цена  продаж: 

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

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

=INDEX(Задание!$B$2:$C$11;ПОИСКПОЗ(Лист4!D5;Задание!$B$2:$B$11;0);2)

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

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

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

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

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

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

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

Примечание.

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

На  основании данных из столбцов D («Обозначение») и G («Дата изготовления») строим линейную диаграмму.  

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

6. ЗАКЛЮЧЕНИЕ 

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

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

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

7. СПИСОК ЛИТЕРАТУРЫ 

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.