Курсовая работа по теме Создание сайта «Электронные таблицы Excel» (стр. 7 )

Периферия, Клавиатура, Genius, 9;

Периферия, Клавиатура, ВТС 5739, 25;

Периферия, Сканер, Acer 610 PT, 195;

Комплектующие, Винчестер, 3.2Гб IDE Seagate, 128;

Комплектующие, Винчестер, 6.4Гб IDE Quantum FB_EX, 161;

Комплектующие, Винчестер, 9.1Гб UW SCSI_IBM, 499.

Cкопируйте формулу из ячейки G9,заполнив поле Цена (б\н) для всех записей. Теперь рабочий лист готов (см. рис. 20).

рис.20

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

Фильтрация данных с помощью автофильтра:

установить курсор внутри таблицы; ввести команду меню ДАННЫЕ – Фильтр – Автофильтр; щелчком мыши по кнопке со стрелкой [▼] раскрыть список столбца, по которому будет производится выборка; указать требуемые значения или выбрать строку ”условие” и задать критерии выборки в диалоговом окне Пользовательский автофильтр.

рис. 21 использование автофильтра для отбора записей по признаку «М» -(мужчина)

рис. 22 рабочий лист после фильтрации списка по признаку «мужчина»

Фильтрация данных с использованием расширенного фильтра. Расширенный фильтр позволяет формировать множественные критерии выборки и осуществлять более сложную фильтрацию данных таблицы с заданием набора условий отбора по нескольким столбцам. Для фильтрации записей списка расширенный фильтр обеспечивает использование двух типов критериев:

критериев сравнения; вычисляемых критериев.

Фильтрация данного типа выполняется с помощью команды меню ДАННЫЕ – Фильтр – Расширенный фильтр. Особенностью этого режима является, то что до выполнения самой команды фильтрации необходимо сформировать специальную область для задания условий фильтрации данных – диапазон условий отбора. После формирования диапазона условий с критериями выборки записей устанавливают курсор внутри таблицы, вводят команду ДАННЫЕ – Фильтр – Расширенный фильтр и в диалоговом окне Расширенный фильтр указывают диапазон ячеек таблицы и адрес или имя сформированного заранее диапазон условий.

Для восстановления всех строк исходной таблицы и отмены режима фильтрации следует ввести команду меню ДАННЫЕ – Фильтр – Отобразить всё.

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

В Excel можно создавать до 8 уровней структуры. Для каждого рабочего листа можно создать только одну структуру.

Структура таблицы создается автоматически после выполнения таких команд меню ДАННЫЕ, как Итоги, Консолидация, Сводная таблица.

Структурировать таблицу можно и самостоятельно с помощью команд меню ДАННЫЕ – Группа и структура.

Автоматическое структурирование таблицы выполняется с помощью команды меню ДАННЫЕ – Группа и структура – Создание структуры. Перед вводом этой команды необходимо выделить диапазон ячеек – область, для которой требуется создать структуру. После выполнения этой команды будут отображены в развернутом виде все существующие структуры.

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

В Excel имеется несколько способов консолидации данных

консолидация данных по расположению – используется для однотипных данных, упорядоченных одинаковым образом; консолидация данных по категориям – используется для однотипных данных, организованных в различных областях - источниках по–разному; консолидация данных путем создания сводной таблицы. Этот способ сходен с консолидацией по категориям, но обеспечивает большую гибкость и информативность. консолидация данных с помощью формул с использованием ссылок. Этот способ не накладывает ни каких ограничений на расположения данных в исходных областях; консолидация данных с использованием Мастера шаблонов с функцией автоматического сбора данных.

Анализ и обобщение данных с помощью сводных таблиц и сводных диаграмм.

Сводные таблицы Excel – вспомогательные таблицы, с помощью которых можно анализировать большие объемы данных, находящихся в различных источниках, и представлять их в наиболее удобном виде. Excel 2000 содержит встроенную возможность – построения сводной диаграммы, отображающей данные сводной таблицы и позволяющей изменять форму представления данных так же легко, как и в сводных таблицах.

Так же, сводные таблицы и сводные диаграммы можно создавать:

на основе таблиц, полученных в результате консолидации данных; на основе других сводных таблиц; на основе внешних источников данных.

Необходимым условием для создания сводной таблицы или сводной диаграммы является наличие одной или нескольких таблиц, содержащих заголовки столбцов. Заголовки столбцов служат для создания в них полей данных. Создание и модификация сводных таблиц и сводных диаграмм выполняются с помощью Мастера сводных таблиц и диаграмм, окно которого появляется на экране после ввода команды меню ДАННЫЕ – Сводная таблица или щелчка по кнопке Мастер сводных таблиц на панели инструментов Сводные таблицы.

Средства условного анализа (“что, если”) используются для исследования различных вариантов решения задач, поиска и выбора оптимального решения.

К средствам условного анализа относятся:

подбор параметра; поиск решения; сценарии; таблицы подстановки.

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

При выполнении этой операции следует иметь в виду, что:

подбор параметра может выполняться только для ячейки, содержащей формулу; ячейка, которая будет изменяться при подборе, должна, наоборот, содержать значение, а не формулу.

Программа Поиск решения не только вычисляет конечный результат на основе изменения значений нескольких ячеек, но и позволяет при этом создавать дополнительные условия – вводить ограничения на изменения параметров влияющих ячеек. Программа допускает установку до 200 изменяемых ячеек. При выполнении поиска решения так же, как при подборе параметра, целевая ячейка должна содержать формулу и быть прямо или косвенно связанной с влияющими ячейками. Ячейки, которые будут изменяться при поиске решения, должны содержать значения, а не формулы.

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

Excel позволяет создавать таблицы подстановки различных типов:

таблицы подстановки с одной переменной (с одной или несколькими формулами); таблицы подстановки с двумя переменными.

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

☺ Упражнение на сортировку и фильтрацию.

На листе Список товаров выделите какую – нибудь ячейку списка, например С8. Выполните команду Данные - Фильтр – Автофильтр. У ячеек с названиями полей появились кнопки, раскрывающие списки значений. Выберите все записи, в которых поле Категория принимает значение «Периферия». Для этого нажмите кнопку поля Категория и в появившемся списке выберите пункт Периферия. В оставшейся части выберите всю информацию о принтерах. Для этого нажмите кнопку поля Товар и в появившемся списке выберите пункт Принтер. Выполните более сложную фильтрацию. Выберите в списке такие принтеры, цена которых не превышает 200 у. е. Для этого нажмите кнопку поля Цена (нал) и выберите пункт Условие. В появившемся окне диалога Пользовательский автофильтр в списке первого поля выберите пункт меньше или равно, а во втором поле со списком введите 200. Нажмите кнопку ОК. Самостоятельно выберите из списка такие компьютеры, цена которых меньше 200 у. е. Включите фильтрацию, выполнив команду Данные – Фильтр – Автофильтр. На листе список товаров выделите какую-нибудь ячейку списка, например С8. Выполните команду Данные – Сортировка. Появится окно диалога Сортировка диапазона. Отсортируем список по полю Категория. Для этого нужно выбрать пункт Категория в списке Сортировать по окна диалога Сортировка диапазона.

33. Нажмите кнопку ОК. Теперь список отсортирован по выбранному полю в алфавитном порядке.

34. Отсортируем список по двум полям одновременно так, чтобы записи полей  Категория и Товар шли в алфавитном порядке, а цены на соответствующие товары шли по убыванию. Для этого вызовите еще раз окно диалога Сортировка диапазона.

35. В окне диалога в списке Сортировать по выберите пункт Категория, а в списке Затем по – пункт Товар.

36. Во втором списке В последнюю очередь по выберите пункт Цена (нал), а переключатель рядом с этим списком установите в положение По убыванию.