48489 (Работа с электронными таблицами), страница 2

Настройка авто фильтра для более сложных критериев. Для каждого столбца можно создать критерий, состоящий из одного или двух условий, соединённых логическими операторами И, ИЛИ.

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

Расширенный фильтр позволяет:

сразу копировать отфильтрованные записи в другое место рабочего листа;

сохранять критерий отбора для дальнейшего использования;

показывать в отфильтрованных записях не все столбцы, а только указанные;

объединять оператором ИЛИ условия для разных столбцов;

для одного столбца объединять операторами И, ИЛИ более двух условий;

создавать вычисляемые критерии;

выводить только уникальные значения.

Включить режим фильтрации можно, выделив любую ячейку списка и выполнив команду данные → фильтр →автофильтр, после чего Excel самостоятельно определит размер и положение списка на листе. При включенном режиме фильтрации у ячеек с названиями полей появляются кнопки, раскрывающие список уникальных (неповторяющихся) значений в ячейках этого столбца. Если выбрать какое-либо из этих значений, то в списке будут показаны только те записи, в соответствующем поле которых находится выбранное значение.

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

Сводная таблица связана с исходными данными, и она может обновляться (пересчитываться) через заданные интервалы времени.

_{2.2 Функции, использованные в данной курсовой работе}

ЕСЛИ

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется при проверке условий для значений и формул.

Синтаксис

ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)

И

Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Синтаксис

И (логическое_значение1; логическое_значение2;. .)

Логическое_значение1, логическое_значение2,... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

СУММ

Суммирует все числа в интервале ячеек.

Синтаксис

СУММ (число1; число2;. .)

Число1, число2,... - это от 1 до 30 аргументов, для которых требуется определить итог или сумму.

Учитываются числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.

ПРОИЗВЕД

Перемножает числа, заданные в качестве аргументов и возвращает их произведение.

Синтаксис

ПРОИЗВЕД (число1; число2;. .)

Число1, число2,... - это от 1 до 30 перемножаемых чисел.

СРЗНАЧ

Возвращает среднее (арифметическое) своих аргументов.

Синтаксис

СРЗНАЧ (число1; число2;. .)

Число1, число2,... - это от 1 до 30 аргументов, для которых вычисляется среднее.

ИНДЕКС

Возвращает значение или ссылку на значение из таблицы или интервала. Функция ИНДЕКС имеет две синтаксические формы: ссылка и массив. Ссылочная форма всегда возвращает ссылку; форма массива всегда возвращает значение или массив значений.

Синтаксис

ИНДЕКС (массив; номер_строки; номер_столбца) возвращает значение указанной ячейки или массив значений в аргументе массив.

ИНДЕКС (ссылка; номер_строки; номер_столбца; номер_области) возвращает ссылку на указанную ячейку или ячейки в аргументе ссылка.

ПОИСКПОЗ

Возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. Функция ПОИСКПОЗ используется вместо функций типа ПРОСМОТР, если нужна позиция элемента в диапазоне, а не сам элемент.

Синтаксис

ПОИСКПОЗ (Искомое_значение, просматриваемый_массив, тип_сопоставления)

Искомое_значение - это значение, используемое при поиске значения в таблице.

Искомое_значение - это значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Например, при поиске номера в телефонной книге имя абонента указывается в качестве искомого значения, а требуемым значением будет номер телефона.

Искомое значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую число, текст или логическое значение.

Просматриваемый_массив - это непрерывный интервал ячеек, возможно, содержащих искомые значения. Просматриваемый_массив может быть массивом или ссылкой на массив.

Тип_сопоставления - это число - 1, 0 или 1. Тип_сопоставления указывает, как Microsoft Excel сопоставляет искомое_значение со значениями в аргументе просматриваемый_массив.

Если тип_сопоставления равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое равно или меньше, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию:..., - 2, - 1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА.

Если тип_сопоставления равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть в любом порядке.

Если тип_сопоставления равен - 1, то функция ПОИСКПОЗ находит наименьшее значение, которое равно и больше чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A,..., 2, 1, 0, - 1, - 2,..., и так далее.

Если тип_сопоставления опущен, то предполагается, что он равен 1.

_{2.3 Проектная часть}

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

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

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

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

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

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

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

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

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

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

Цена продаж:

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

ЕСЛИ (C2=10; C220; 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-ой.

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

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

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

В появившемся окне Мастер сводных таблиц выбираем пункт: Создать таблицу на основе данных, находящихся в списке или базе данных 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.

Приложение