13

СОДЕРЖАНИЕ

Введение 3

Создание и Фильтрация Списков 3

Использование инструмента Автофильтр. 4

Фильтрация по определенному значению. 4

Использование фильтра Первые10 5

Выборочная фильтрация. 5

Сортировка данных. 6

Создание собственного списка автозаполнения 7

Сортировка с помощью списка автозаполнения. 7

Сортировка “слева направо” 8

структурирование листа. 8

Автоматическое структурирование листа 8

Структурирование листа вручную 8

Сводные таблицы. 9

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

Получение внешних данных с помощью Microsoft Query 11

Создание диаграммы 11

Заключение. 12

Литература. 13

Введение

Microsoft Excel – самая распространенная в мире программа электронных таблиц. Программа Excel 97 расширена многими новыми полезными возможностями и полностью интегрирована с другими программами Microsoft Office 97.

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

Программу Excel 97 можно использовать для хранения данных. Эта программа обладает многими возможностями, присущими современным программам для работы с базами данных. Так, например, Excel 97 имеет средства для обработки данных и осуществления их фильтрации. Однако по своим возможностям Excel 97 уступает специализированным программам, предназначенным для работы с базами, таким как Microsoft Access, Oracle, SQL. В частности, Excel 97 не может работать с очень большими базами данных. Это связано с тем, что программа Excel 97 загружает в память всю базу данных, независимо от ее объема. Специализированные же программы загружают только те данные, которые необходимы.

Создание и Фильтрация Списков

Р
исунок 1. Обычный список в Excel

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

Согласно терминологии, принятой в базах данных, понятие поле эквивалентно принятому в Excel 97понятию столбец. Метки, описывающие поля, называются именами полей. Понятие запись эквивалентно принятому в Excel 97 понятию строка. На рисунке 1 показан пример базы данных, имеющей четыре поля: Студенты, Тест1, Тест2 и Средний показатель. В ней имется также семь записей, по одной на каждого студента.

При осуществлении фильтрации списка согласно установленному критерию или правилам часть его данных оказываются “спрятанными” (на экране их не видно). Предположим, что у нас имеется список всех клиентов некой фирмы из России и стран СНГ. Мы можем отфильтровать наш список так, что в нем останутся клиенты только из Украины. Или же этот список можно отфильтровать так, что в нем останутся только те клиенты, заказы которых превышают некоторую заранее установленную сумму. Для каждого конкретного списка имеется много возможных путей его фильтрации.

Фильтрация данных в списках Excel осуществляется с помощью двух инструментов: Автофильтр и Расширенный фильтр.

Использование инструмента Автофильтр.

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

Из меню Данные надо выбрать команды Фильтр | Автофильтр. После этого в заголовках столбцов появятся стрелки Автофильтра. Если щелкнуть по одной из таких стрелок, то раскрывается меню, в котором представлены различные параметры Автофильтра. Это меню предлагает несколько возможных вариантов фильтрации данных:

• Все записи

• Первые десять записей

• Фильтрация данных согласно определенному условию.

Фильтрация по определенному значению.

Параметры фильтрации определяются типом данных, с которым ведется работа. Если выбрать из меню Автофильтра некоторое значение, то в списке останутся только те записи, в ячейках которых записано именно это значение. Рассмотрим конкретный пример. Если щелкнуть на стрелке Автофильтра F3, содержащей имя поля Средний показатель, и из появившегося меню выбрать 71%, то в представленном списке останутся видны только студенты Лепин и Якушев, поскольку только у них средние показатели равны 71%.

Если выбрать имя одного из студентов из списка Автофильтра (Рисунок 2), то в списке останется только запись с результатами этого студента, а остальные записи окажутся скрытыми.

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

Использование фильтра Первые10

Е
сли после включения инструмента Автофильтр, выбрать команду Первые 10, то появится диалоговое окно Наложение условия по списку. В первом поле слева можно определить количество выводимых записей.

Рисунок 2 Список со стрелками и открытым меню инструмента Автофильтра

Также можно определить, по какому параметру будет производиться отбор элементов: наибольшие или наименьшие значения. Наибольшее значение в списке - это самое большое числовое значение, записанное в одной из ячеек столбца, наименьшее – это самое маленькое из числовых значений, содержащееся в ячейках выбранного столбца.

В третьем поле диалогового окна Наложение условия по списку можно определить, будет ли выведено указанное количество элементов или же доля элементов списка (процент).

При осуществлении операции фильтрации адреса ячеек не изменяются. Например, если будут отфильтрованы строки 6 и 7, то после строки 5 будет видна строка 8.

Для отключения инструмента Автофильтр следует из меню Данные выбрать команду Фильтр и щелкнуть на команде Автофильтр.

Выборочная фильтрация.

Можно также определить и свои собственные критерии фильтрации данных. Для того чтобы осуществить выборочную фильтрацию, необходимо сначала включить инструмент Автофильтр, выбрав команды Данные | Фильтр | Автофильтр. Затем щелкнуть на стрелке Автофильтра и из появившегося меню выбрать команду Условие. Появится диалоговое окно Пользовательский Автофильтр. В раскрывающихся списках в левых полях диалогового окна Пользовательский автофильтр имеется ряд параметров:

• Равно

• Не равно

• Больше

• Больше или равно

• Меньше

• Меньше или равно

• Начинается с

• Не начинается с

• Заканчивается на

• Не заканчивается

• Содержит

• Не содержит

П
рименение двух параметров, объединенных функцией И/ИЛИ позволяет сузить область поиска (Рисунок 3).

Рисунок 3. Выборочная фильтрация с использованием двух параметров.

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

Сортировка данных.

Для организации данных можно воспользоваться имеющимися в Excel инструментами сортировки. В зависимости от типа данных, Excel может задать ряд вопросов с тем, чтобы определить, по какому принципу необходимо сортировать эти данные. В отличии от фильтрации данных, операция сортировки не приводит к скрытию данных. Можно видеть те же данные, но в другом порядке.

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

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

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

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

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

Создание собственного списка автозаполнения

В Excel есть возможность для создания своих списков автозаполнения и сохранения их для дальнейшего использования. Например, можно создать список государств и затем сохранить его как список для автозаполнения. Теперь, после того как введены два первых государства, инструмент Автозаполнение заполнит весь список. Можно также создать список месяцев в году или список дней недели.

Для того, чтобы воспользоваться инструментом Автозаполнение необходимо сделать следующее:

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

2. Выделить весь список.

3. Выбрать из меню Сервис команду Параметры. В появившемся диалоговом окне Параметры перейти на вкладку Списки.

4. Щелкнуть на кнопке Импорт.

5. Щелкнуть на кнопке Ok

Предварительное выделение списка не обязательно. Вместо этого можно на вкладке Списки просто ввести адреса ячеек, в которых располагается список, в поле Импорт списка из ячеек.

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

Сортировка с помощью списка автозаполнения.

Если данные соответствуют списку автозаполнения, то для их сортировки можно использовать этот список. Для выполнения сортировки данных с помощью списка автозаполнения необходимо выполнить следующие действия:

1. Выделить ячейки, подлежащие сортировке.

2. Выбрать из меню Данные команду Сортировка. В диалоговом окне Сортировка диапазона щелкнуть на кнопке Параметры. Появится диалоговое окно Параметры сортировки

3. В поле Сортировка по первому ключу щелкнуть на направленной вниз стрелке и из раскрывшегося меню выбрать список, который будет использован для сортировки.

Сортировка “слева направо”

Чаще всего производится сортировка строк (при этом изменяется порядок следования записей в столбцах). Однако сортировать можно и данные в строке (при этом будет производится перестановка столбцов).

Для того чтобы с помощью диалогового окна Сортировка выполнить сортировку слева направо, необходимо сделать следующее:

1. Выделить диапазон значений. Не включать в этот диапазон ячейки, в которые введены названия строк, иначе они тоже будут подлежать сортировке.

2. Выбрать из меню Данные команду Сортировка. В диалоговом окне Сортировка диапазона щелкнуть на кнопке Параметры. Появится диалоговое окно Параметры сортировки.

3. В поле Сортировать, оно расположено в нижней части диалогового окна Параметры сортировки, выбрать параметр Столбцы диапазона.

4. Для закрытия диалогового окна Параметры сортировки щелкнуть на кнопке Ok

5. В диалоговом окне Сортировка диапазона в поле Сортировать по выбрать номер строки, подлежащей сортировке.

Для того, чтобы при сортировке учитывались строчные или прописные символы в диалоговом окне Параметры сортировки поставте метку в поле Учитывать регистр.

структурирование листа.

Структурирование используется для выделения важных данных. Так, например, при создании документов в текстовом редакторе Word все заголовки оформляются специальным образом для того, чтобы облегчить восприятие информации. Программа Excel может выполнить структурирование листа автоматически и “вручную”.

Автоматическое структурирование листа

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

1. Выделить диапазон ячеек, для которых необходимо создать структуру. Для структурирования листа целиком указать любую ячейку.

2. Выбрать команду Структура в меню Данные, а затем – Автоструктура.

Структурирование листа вручную

1. Выделить строки или столбцы, содержащие детальные данные. Детальные строки или столбцы обычно подчинены строке или столбцу, содержащему соответствующие итоговые данные. Например, если строка 6 содержит суммы для строк с 3 по 5, то выделите строки 3 - 5.

2. Выбрать команду Структура в меню Данные, а затем – Сгруппировать.Рядом с группой на экране должны появиться символы структуры.

3. Продолжать выделение детальных строк или столбцов и выполнять команду Сгруппировать до тех пор, пока в структуре не будут созданы все необходимые уровни.

Структура может иметь до 8 уровней детальных данных, где каждый "внутренний" уровень предоставляет детальные данные для предыдущего "внешнего".