2 Создание базы данных «Мой край» в Microsoft Excel

Вид материала

Реферат

Содержание Текстовые критерии Копирование отфильтрованных строк в другое место рабочего листа Создание базы данных «Мой край» в Microsoft Excel Второй шаг – выбор программной среды Третий шаг – сбор информации и заполнение БД Шаг четвертый – конструирование запросов и отчетов Численность населения Численность населения Название района Название административной единицы Команда Автофильтр Команда Расширенный фильтр Плотность населения Название района, в котором есть поселки городского типа Численность населения Список сайтов по работе в Microsoft Excel

Подобный материал:

• Применение Microsoft Excel для обработки табличных данных. Выполнение расчетов в таблицах, 14.68kb.
• Назначение программы Microsoft Excel (или просто Excel ) и создание и обработка электронных, 184.32kb.
• Ms access Создание базы данных, 34.31kb.
• Методика оформление учебно-методической документации учителем географии в текстовом, 644.79kb.
• Создание базы данных “Классный, 73.09kb.
• Практическая работа № «Создание базы данных», 21.96kb.
• Курс 2778. Создание запросов на языке Microsoft sql server 2005 Transact-sql. Курс, 16.57kb.
• Пособие для начинающих Воробьев В. В. Microsoft Excel, 439.68kb.
• Курс лекций по дисциплине " основы компьютерных технологий" Часть II. Microsoft Excel, 457.54kb.
• Урок №10. Тема: Работа с базами данных в Excel, 119.05kb.

Содержание

Введение	2
Создание базы данных «Мой край» в Microsoft Excel	3
Первый шаг – разработка модели	3
Второй шаг – выбор программного средства	3
Третий шаг – сбор информации и заполнение БД	3
Полезные ссылки	3
Формирование базы данных	5
Четвертый шаг – конструирование отчетов и запросов	7
Сортировка списков	7
Команда Автофильтр	10
Команда Расширенный фильтр	14
Интервал критериев	14
Текстовые критерии	18
Вычисляемые критерии	19
Применение формы данных для поиска информации в списке	22
Копирование отфильтрованных строк в другое место рабочего листа	22
Подведение итогов	23
Функции баз данных	27
Список Интернет-ресурсов по работе в Microsoft Excel	28

Введение


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

Есть различные программные среды, в которых можно создавать базы данных и использовать их в образовательном процессе. Выбор программной среды зависит от того, для каких целей создается БД. Небольшие базы данных, с несложными запросами можно оперативно создавать в Microsoft Excel, более сложные в Microsoft Access. Сложные БД можно создавать в СУБД MySQL или других мощных оболочках, изменение содержимого такой базы данных можно производить через Web-интерфейс с использованием HTML-формы, не вторгаясь при этом в технические детали каждой специфической СУБД. В данной работе изложены основные способы работы по созданию БД и ссылки на Интернет-ресурсы по данной проблеме, и мы надеемся, что все это поможет вам самостоятельно создать свою базу данных.


Начнем с определения, что же такое база данных.

База данных (БД) – структурированная совокупность взаимосвязанных данных в рамках некоторой предметной области, предназначенная для длительного хранения во внешней памяти ЭВМ и постоянного применения.

Реляционные БД – базы данных с табличной формой организации информации. Реляционная БД состоит из одной или нескольких взаимосвязанных таблиц. (Учебное издание. Информатика. Задачник практикум. Том 2. Издательство «Бином. Лаборатория базовых знаний», 2002, 278с.).


Первым и наиболее важным действием при создании базы данных является разработка ее модели. Второй шаг – выбор программной среды. Третий шаг – сбор информации и заполнение БД. Четвертый шаг – конструирование запросов и отчетов. Следующий шаг – анализ полученных результатов. На конкретных примерах мы попытаемся вместе с вами преодолеть этот нелегкий, но увлекательный путь.


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


Какие же списки можно создавать и использовать в учебном процессе? Например, БД под названием: «Физическая география нашего региона», «Полезные ископаемые», «Население нашего района», «Растительный мир», «Животный мир», «Заболеваемость в нашем районе», «История нашего района», «Профессии моего города», «Наши соседи». Хочется отметить, что количество полей этих БД может быть весьма разнообразным. Возьмем, например, таблицу «Наши соседи». В ней может быть представлен список стран, граничащих с данным регионом, количественный состав населения, столица, города, реки, туристические маршруты и т.д.

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

Учитель (школа) может предложить для учащихся готовые списки литературы по тем или иным предметам, интересных сайтов, конкурсов.

Автору представляется наиболее интересным использование баз данных в рамках краевого телекоммуникационного проекта «Мой край». В рамках этого проекта учащиеся всех административных единиц Приморского края сообщают информацию о своем районе, городе, поселении координатору проекта. Координатор проекта собирает эту информацию и помещает ее в БД.


Создание базы данных «Мой край» в Microsoft Excel


Первый шаг – разработка модели


БД «Мой край» представляет собой обычную таблицу с полями: Название административной единицы, Тип поселения, Название района, Численность населения, Год основания, Расстояние до Владивостока. Таким образом, это реляционная БД, состоящая из одной таблицы. Пока из одной, а может быть, у Вас появятся новые интересные идеи и наша БД значительно преобразится, у нее появятся новые поля и вполне вероятно новые таблицы.

Карта Приморского края ссылка скрыта


Второй шаг – выбор программной среды


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


Третий шаг – сбор информации и заполнение БД


Полезные ссылки


Информацию по нашей проблеме можно найти на сайтах:

ссылка скрыта – Инициативный проект лаборатории компьютерных технологий Дальневосточного геологического института Дальневосточного отделения Российской Академии Наук.

ссылка скрыта, ссылка скрыта – Мир путешествий и приключений. Дизайн, программирование, контент, поддержка, авторские права и в.в. © 2006 Ury Zimin.

ссылка скрыта – Территориальное устройство России.

ссылка скрыта – интерактивные карты Приморья.

ссылка скрыта – Юридический Интернет-портал Справочник ЮНЕТ.

ссылка скрыта Народная энциклопедия городов и районов России «Мой город».

ссылка скрыта – Приморскстат.


Сайты городов Приморского края

ссылка скрыта – официальный сайт администрации города Владивосток.

ссылка скрыта – сайт администрации Артемовского городского округа.

ссылка скрыта – сайт город Артем.

ссылка скрыта – Большой Камень Приморского края - Информационный сайт Большого Камня.

ссылка скрыта – официальный сайт г. Дальнегорска.

ссылка скрыта – официальный сайт администрации города Находки.

ссылка скрыта – официальный сайт администрации Уссурийского городского округа.

ссылка скрыта – сайт администрации муниципального образования город Лесозаводск .

ссылка скрыта – Информационный сайт создан и поддерживается
администрацией Партизанского городского округа.

ссылка скрыта – сайт администрации муниципального образования город Спасск-Дальний.

ссылка скрыта – Неофициальный сайт города Фокино.

ссылка скрыта – Дальнереченск.


Сайты районов Приморского края

ссылка скрыта – официальный сайт администрация Приморского края.

ссылка скрыта – сайт администрации муниципального образования Анучинский район.

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

ссылка скрыта – сайт управления образования Кировский район.

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

ссылка скрыта – сайт администрации муниципального образования Лазовский район.

ссылка скрыта – сайт администрации муниципального образования Михайловский район.

ссылка скрыта – сайт администрации муниципального образования Надеждинский район.

ссылка скрыта – официальный сайт Пограничного муниципального района.

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

ссылка скрыта – сайт администрации муниципального образования Тернейский район.

ссылка скрыта – Ханкайский муниципальный район. Администрация Ханкайского Муниципального района.

ссылка скрыта – сайт администрации Хасанского района.

ссылка скрыта – сайт администрации муниципального образования Хорольский район.

ссылка скрыта – сайт администрации муниципального образования Черниговский район.

ссылка скрыта – сайт администрации муниципального образования Чугуевский район.


Теперь, когда мы знаем, где найти информацию, приступим к созданию нашей таблицы «Мой край».


Формирование базы данных


В списке Excel каждый столбец - это поле, а каждая строка - это запись.

При формировании БД в Excel необходимо руководствоваться следующими правилами:

• Значения, хранящиеся в одном столбце (значения одного поля), должны иметь один и тот же тип, то есть каждый столбец должен содержать однородную информацию. Например, в БД «Мой край» один столбец для названий административных единиц, другой – для численности населения, третий – для названия района, в котором находится данная административная единица, четвертый – расстояние до Владивостока, пятый – год основания и т.д.
  
• Верхняя строка в таблице должны содержать название, описывающие назначение соответствующего столбца.
  
• Необходимо избегать пустых строк и столбцов внутри таблицы, так как список отделяется от остальной части рабочего листа пустыми строками и столбцами.
  
• В идеале на рабочем листе не должно быть ничего, кроме данной таблицы.
  
• Рекомендуется именовать лист названием списка.
  

Н
а рис.1 приведен список из шести столбцов. Как создать такую таблицу?


Список состоит из строк и столбцов. Столбцы — это поля списка (таблицы БД). Первая строка списка обычно содержит имена полей (заголовки столбцов). Создадим первую строку.


Установите необходимую ширину столбцов, воспользуйтесь командой Столбец, Ширина меню Формат.

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

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

Для эстетического отображения названий полей воспользуйтесь вкладкой Выравнивание диалогового окна Формат ячеек и установите выравнивание по вертикали – по центру и выравнивание по горизонтали – по центру.

Чтобы подчеркнуть названия полей списка воспользуйтесь вкладкой Граница диалогового окна Формат ячеек. На рис.2 приведены примеры диалогового окна Формат ячеек.


П

еред вводом данных необходимо установить тип данных для каждого столбца нашей таблицы. Чтобы установить тип данных столбца надо выделить этот столбец и воспользоваться вкладкой Число диалогового окна Формат ячеек рис.3.


Новые данные можно добавлять непосредственного в конец списка. Однако редактирование списка можно выполнять с помощью стандартной экранной формы. Для этого выделите какую-нибудь ячейку в списке (например, A1) и выполните команду Форма из меню Данные. Перед выполнением команды Форма из меню Данные, должна быть выделена только одна ячейка списка. На рис.4 приведен пример такой формы для БД рис1.


В
строке заголовка формы выводится имя рабочего листа, содержащего редактируемый список. На левой части формы располагаются заголовки столбцов списка и поля ввода соответствующих значений. Если столбец содержит формулы, то поле ввода отсутствует. Ширина формы определяется максимальной шириной столбцов на рабочем листе, а высота формы – количеством столбцов в списке. Справа располагаются кнопки управления списком. Для добавления новой строки (записи) в БД щелкните кнопку Добавить. Excel выведет пустую форму, в поля которой можно вводить значения новой строки. Чтобы вернуться на рабочий лист щелкните кнопку Закрыть. При добавлении новых строк список удлиняется, не затрагивая ячейки вне списка. Для коррекции в списке исходных данных найдите с помощью полосы прокрутки в диалоговом окне формы нужную запись (строку) и внесите изменения в соответствующее поле ввода. Чтобы удалить текущую запись из списка щелкните кнопку Удалить. Удаление записи в диалоговом окне формы нельзя отменить, поэтому Excel выводит запрос на подтверждение этой операции.

Вид нашей БД приведен на рис.1.


Шаг четвертый – конструирование запросов и отчетов


Сортировка списков

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

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

Продемонстрируем сортировку на списке, приведенном на рис.1. Необходимо отсортировать список по столбцу Численность населения. Для этого выделите одну ячейку в этом списке и выполните команду Сортировка из меню Данные, откроется п
оказанное на рис.5 диалоговое окно.


В
ыберете поле, по которому нужно сортировать список (в нашем случае – Численность населения). Установите переключатель по убыванию. В разделе Идентифицировать поля по должен быть установлен переключатель подписям (первая строка диапазона) и щелкните ОК. Результат сортировки списка показан на рис.6. Всегда проверяйте результат сортировки. Если она Вас не устраивает, то воспользуйтесь командой Отменить из меню Правка. Если после сортировок нужно восстановить исходный порядок строк в списке, то до сортировки необходимо создать столбец с номерами строк, а затем можно отсортировать список по этому столбцу. В рассмотренном примере исходный список рис.1 был отсортирован по одному столбцу –Численность населения.

О
тсортируем список по полю Название района. Выполните команду Сортировка из меню Данные. Выберите поле по которому нужно сортировать список, в нашем случае Название района. Для сортировки по возрастанию, т.е. от А до Я, установите переключатель по возрастанию.

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

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

Excel дает возможность отсортировать данные не более, чем по трем столбцам. Отсортировать более чем по трем столбцам можно последовательно: сначала по наименее важному столбцу, затем по следующему по важности столбцу и т.д.

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

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


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


Команда Автофильтр


Командой Автофильтр можно воспользоваться, выделив какую-либо ячейку в списке и выполнив команду Автофильтр из подменю Фильтр меню Данные. Справа от каждого заголовка столбца появится кнопка со стрелкой вниз. Щелкнув эту кнопку, Вы раскроете список уникальных значений данного столбца, которые можно использовать для задания критерия фильтра. В нашем примере предположим, что в списке, приведенном на рис.1 нужно выделить только строки, относящиеся к Дальнегорскому району. Для фильтрации этих данных следует установить курсор в любую ячейку таблицы (например A1), выбрать команду Автофильтр, в столбце Название района раскрыть список районов (нажав на кнопку со стрелкой, расположенную справа в данном столбце) и выбрать в нем Дальнегорский. Результат показан на рис.9.





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


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


Критерии команды Автофильтр можно задавать по любому количеству столбцов. Сначала отфильтруйте список по одному столбцу, затем полученный список отфильтруйте по другому столбцу и т.д. В нашем примере мы отфильтровали БД по двум столбцам Название района (выбрали Дальнегорский район) и Тип поселения (выбрали пгт-поселок городского типа) рис.10.


К
аждый раскрывающийся список критериев команды Автофильтр содержит в конце элементы Пустые и Непустые.

Если нужно отфильтровать строки, в которых значения заданного столбца не введены, выберите в качестве критерия фильтра элемент Пустые. Например, сначала отфильтруем строки в списке рис.1, относящиеся только к Надеждинскому району, а затем строки в которых не указана Численность населения, т.е. Пустые, результат приведен на рис.11.

Ч
тобы избавится от строк с пустыми ячейками в фильтруемом столбце, выберите элемент Непустые. Например, отфильтруем сначала строки в списке рис.1, относящиеся к Надеждинскому району, а затем строки в столбце Численность населения, Непустые, результат приведен на рис.12.





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


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

Предположим, мы хотим показать населенные пункты численность населения которых больше 20тыс. человек, но меньше 50тыс. человек. Для списка на рис.1 выполним команду Автофильтр из подменю Фильтр меню Данные. Раскроем список критериев для столбца Численность населения, выберем элемент (Условие…). В диалоговом окне Пользовательский автофильтр при помощи раскрывающегося списка, в первой строке выберем условие Больше или равно, а во второй строке выберем условие Меньше или равно. В первой строке справа введем число 20, а во второй строке – число 30. Переключатель установим в положение И. Результат выполнения данных операций приведен на рис.14.





Для нахождения всех текстовых значений столбца, попадающих в заданный алфавитный интервал, необходимо раскрыть список критериев этого столбца и выбрать элемент (Условие…). В диалоговом окне Пользовательский автофильтр следует задать два критерия, объединенных оператором И. Например, чтобы найти все населенные пункты, названия которых начинаются с буквы Б, нужно установить фильтр по столбцу Название административной единицы, задав в диалоговом окне Пользовательский Автофильтр два условия >Б И <В. Буквы Б и В надо напечатать в соответствующих полях д
иалогового окна рис.15. Результат такой фильтрации для таблицы рис.1 приведен на рис.16.






При создании критериев можно использовать два символа шаблона:

• Звездочка (*) для представления любой последовательности символов.
  
• Вопросительный знак (?) для представления любого отдельного символа.
  

Например, чтобы получить результат представленный на рис.16, для списка на рис.1 выполним команду Автофильтр из подменю Фильтр меню Данные. Раскроем список критериев для столбца Название административной единицы, выберем элемент (Условие…). В диалоговом окне Пользовательский автофильтр задим критерий, как показано на рис.17.






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


Команда Расширенный фильтр


Интервал критериев


Команда Расширенный фильтр позволяет выполнять следующие операции:

• Создавать критерии с условиями по нескольким столбцам, связанным по правилу ИЛИ. Хотя для этого проще несколько раз выполнить команду Автофильтр.
  
• Создавать критерии с тремя и более условиями для заданного столбца, связанными по крайней мере одним союзом ИЛИ. Например, из списка населенных пунктов надо выбрать все районные центры, будь то города, поселки городского типа или села.
  
• Создавать вычисляемые критерии. Например,
  

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

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


Рассмотрим пример критерия с двумя условиями, объединенными по правилу ИЛИ

Пусть из списка «Мой край» приведенного на рис.1, нужно выделить как все поселки городского типа, так и административные единицы, численность населения которых больше или равна 5000 человек. Для создания такого фильтра выполните следующие действия:

• Вставьте несколько строк для интервала критериев выше списка на рабочем листе. Для этого установите курсор в ячейку A1 и пять раз выполните команду Строки из меню Вставка.
  
• Создайте интервал критериев, как показано на рис.18. Заголовки условий (Тип поселения, Численность населения, тыс.чел) скопируйте. На рис.18 в ячейках A1:B3 критерии содержаться в виде обыкновенных меток. Под заголовком Тип поселения введено
  

пгт

а под заголовком Численность населения, тыс.чел.

>=5

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

• Выберите команду Расширенный фильтр из подменю Фильтр меню Данные и введите в соответствии с рис.19 данные в диалоговом окне Расширенный фильтр. Перед тем как выполнять команду Расширенный фильтр, установите курсор в ячейку A6.
  
• У
  бедитесь, что установлен переключатель фильтровать список на месте, и щелкните ОК. Результат фильтрации показан на рис.20.
  

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

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

• Условия на одной строке объединяются по правилу И.
  
• Условия на отдельных строках объединяются по правилу ИЛИ.
  

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


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






Пример критерия с тремя условиями по одному столбцу, объединенных по правилу ИЛИ.


П
редположим теперь, что в списке на рис.1 нужно выделить административные единицы с названиями, начинающимися с букв Ш, Я или Ж. В интервал критерия следует включить заголовок столбца Название административной единицы и ввести буквы Ш, Я, Ж в три ячейки под этим заголовком. Установить курсор в любую ячейку списка, который необходимо отфильтровать, затем открыть диалоговое окно Расширенный фильтр и задать ссылки на список и интервал критериев. Результат выполнения данных операций показан на рис.22

Пример критерия с условиями, объединенными по правилам И и ИЛИ одновременно.


К
ак выделить одновременно поселки городского типа и села, численность населения которых больше 10тыс. человек? Необходимо создать интервал критериев, показанный на рис.23. Обратите внимание, что условие >=10 присутствует в каждой строке интервала, потому что записи каждой группы типа поселения (будь-то поселок городского типа или село), должны одновременно удовлетворять и критерию численности населения, т.е. эти два условия объединяются по правилу И.


Текстовые критерии


Обработка текстовых критериев в Excel выполняется по следующим правилам:

• Если задана одна буква, то равенству (=) будут найдены все начинающиеся на эту букву значения. Например, по критерию =П в столбце Название района будут найдены Партизанский, Пограничный, Пожарский районы и т.п.
  
• По условиям больше (>) или менше (<), будут найдены значения, которые располагаются в алфавитном порядке соответственно после или до заданного значения. Например, по критерию >К в столбце Название района будут выделены районы с названиями, начинающимися с букв от К до Я. Противоположный критерий <К позволяет отобрать районы, начинающиеся с букв от А до И.
  
• По критерию =”=текст” выделяются значения, точно совпадающие с заданным выражением текст. Например, чтобы найти записи относящиеся только к поселкам городского типа, не являющимися районными центрами надо задать формулу =”=пгт”, а если мы просто зададим пгт, то будут выделены все записи относящиеся к поселкам городского типа. Приведем другой пример, по критерию Ново в столбце Название административной единицы мы найдем записи с названиями Новогордеевка, Новошахтинский, Новомихайловка, Новокрещенка и т.д.
  
• Символы шаблона обрабатываются также как в автофильтре.
  

Вычисляемые критерии


Вычисляемыми критериями являются критерии, включающие более сложные операции, чем простое сравнение столбца с константой. Обработка вычисляемых критериев выполняется по следующим правилам:

• Заголовок столбца над вычисляемым критерием не должен совпадать с заголовком какого-либо столбца в анализируемом списке. Он может быть пустым или содержать любой другой текст. Это правило противоположно тому, что требуется при задании обычных критериев.
  
• Ссылки на ячейки вне списка должны быть абсолютными.
  
• Ссылки на ячейки внутри списка должны быть относительными.
  

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

1. Вставьте несколько строк для интервала критериев выше списка на рабочем листе. Для этого установите курсор в ячейку A1 и три раза выполните команду Строки из меню Вставка.
   
2. В ячейку A1 введите заголовок вычисляемого критерия Плотность населения больше медианы. Перед вводом текста установите в A1 Формат ячейки Переносить по словам.
   
3. В ячейку D1 введите текст Медиана плотности населения. Перед вводом текста установите в D1 Формат ячейки Переносить по словам.
   
4. При создания вычисляемого фильтра в ячейку E1 вне списка введите формулу =МЕДИАНА(E6:E29).
   Воспользуйтесь командой Функция из меню Вставка, в диалоговом окне Мастер функций шаг 1 из 2 выберите Категория: Статистические и Выберите функцию: МЕДИАНА, щелкните ОК. В диалоговом окне Аргументы функции задайте интервал E6:E29, ОК.
   
5. В ячейку A2 введите условие вычисляемого критерия с абсолютной ссылкой на эту внешнюю ячейку
   =E6>$E$1
   
6. Перед тем как выполнять команду Расширенный фильтр, установите курсор в ячейку A5.
   
7. Выберите команду Расширенный фильтр из подменю Фильтр меню Данные и введите Диапазон условий $A$1:$A$2.
   

В
строке состояния на рис.25 видно, что по этому фильтру выделено 12 из 24 исходных записей. Как и следовало ожидать, не больше половины всех районов имеют плотность населения выше медианы.


Рассмотренный пример показывает следующее:

• Заголовок интервала не совпадает ни с одним заголовком в списке, иначе фильтр работал бы неправильно. Заголовок может быть пустым, но и в этом случае его надо включать в интервал критерия в диалоговом окне Расширенный фильтр.
  
• В формуле критерия только ячейка E6 сравнивается с ячейкой E1, но при обработке фильтра сравнение выполняется последовательно для всех ячеек столбца E, начиная с ячейки E6 и до конца списка.
  
• Ссылка на ячейку E1 – абсолютная. Если в ячейке A2 задать формулу =E6>E2 с относительной ссылкой, то ячейка E6 сравнивалась бы с ячейкой E1, ячейка E7 – с ячейкой E2 и т.д., что, очевидно, не правильно.
  
• На значение, возвращаемое формулой критерия в ячейке A2, можно не обращать внимания. В данном случае значение ЛОЖЬ соответствует тому, что плотность населения Анученского района меньше медианы.
  

Пример ссылки на ячейки внутри списка


Если в таблице на рис.24 отсутствовал бы столбец Плотность населения, то мы могли бы применить вычисляемый фильтр, для поиска районов с заданной плотностью населения. Например, мы хотим найти районы, где плотность населения меньше чем 5 человек на кв.км. Тогда в формуле критерия
=C6/D6<5
используются относительные ссылки, так как вычисления выполняются с данными из ячеек только текущей строки: C6 делится на D6, C7 делится на D7 и т.д.
Результат фильтрации с вычисляемым критерием и относительными ссылками, приведен на рис.26.





Применение формы данных для поиска информации в списке


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

1. Выделить любую ячейку в списке.
   
2. Выбрать команду Форма из меню Данные и в открывшемся диалоговом окне щелкнуть кнопку Критерии
   
3. Заполнить поля ввода так же, как при создании интервала критериев.
   
4. Щелкнуть кнопку Следующая, чтобы вывести первую строку списка, удовлетворяющую заданным критериям.
   
5. Пролистать выделенные записи, используя кнопку Следующая, или кнопку предыдущая.
   

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


В диалоговом окне Расширенный фильтр можно задать режим копирования строк в другое место рабочего листа: установить переключатель скопировать результат в другое место и в поле ввода Поместить результат в диапазон: ввести имя или адрес интервала, в который нужно поместить результат фильтрации. Проще задать интервал для копирования – щелкнуть мышью начальную ячейку этого интервала. Убедитесь, что справа от этой ячейки и ниже её имеется достаточно свободного места. Заголовки столбцов и все записи списка, удовлетворяющие критериям расширенного фильтра, будут помещены в заданный интервал. Если интервал для копирования задать полностью, отфильтрованные строки будут копироваться только до заполнения всех ячеек интервала. Интервалу, задаваемому в поле Поместить результат в диапазон:, присваивается имя Извлечь, которое можно использовать для перехода внутри листа. Например, чтобы вернуться к этому интервалу для замены заголовков столбцов, нажмите клавишу F5 и выберите Извлечь из диалогового окна Переход.

Чтобы поместить в новый интервал только некоторые столбцы списка, скопируйте туда их заголовки, в поле Поместить результат в диапазон: задайте ссылку на интервал, занимаемый этими заголовками. Например, для копирования только столбцов Название административной единицы и Численность населения отфильтрованного по поселкам городского типа списка «Мой край» рис.1 в интервал, начинающийся с ячейки L6, выполните следующие действия:

1. Скопируйте заголовки столбцов Название административной единицы и Численность населения в интервал ячеек L6:M6. Заголовки можно копировать в любом порядке в зависимости от требуемого вида отфильтрованных данных.
   
2. Выберите команду Расширенный фильтр из подменю Фильтр меню Данные.
   
3. В открывшемся диалоговом окне Расширенный фильтр задайте ссылки на интервал фильтруемого списка и интервал критериев, установите переключатель скопировать результат в другое место, напечатайте L6:M6 в поле Поместить результат в диапазон: и щелкните ОК.
   

Флажок Только уникальные записи диалогового окна Расширенный фильтр действует только в режиме скопировать результат в другое место и устраняет дублирование строк. Пусть из списка «Мой край» нужно выделить районы в которых есть поселки городского типа, при этом каждый из районов должен присутствовать в списке один раз. Для формирования такого списка выполните следующие действия:

1. Создайте интервал критериев, который бы позволил найти все записи, относящиеся к поселкам городского типа, для этого скопируйте заголовок Тип поселения в ячейку A1, а в ячейку A2 введите пгт.
   
2. В другой пустой области рабочего листа, например в ячейку K4 введите заголовок Название района, в котором есть поселки городского типа. Ячейка с заголовком является началом интервала для копирования отфильтрованных строк.
   
3. Выберите команду Расширенный фильтр из подменю Фильтр меню Данные.
   
4. В диалоговом окне Расширенный фильтр введите ссылки на интервалы списка и критериев. Установите переключатель скопировать результат в другое место, напечатайте K4 в поле Поместить результат в диапазон:, установите флажок Т
   олько уникальные записи и щелкните ОК. Результат показан на рис.27.
   

Подведение итогов


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

Команда Итоги дает возможность подвести также общие итоги, т.е. применить выбранную функцию, например СУММА или СРЕДН, не только к группе записей в списке, но и ко всему списку. Более того, Вы можете задать, место размещения общих итогов в конце или в начале списка. Последнее удобно при длинном списке, так как не нужно искать общие итоги в его конце.


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


При рассмотрении команды Итоги будет использоваться список, приведенный на рис.28, в котором представлены названия, численность населения городов, районных центров и поселков городского типа Приморского края. Список отсортирован по значениям столбца Название района.


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

1. Выберите из меню Данные команду Итоги.
   
2. З
   аполните диалоговое окно Промежуточные итоги в соответствии с рис.29.
   
3. Убедитесь, что флажки Заменить текущие итоги и Итоги под данными установлены, и щелкните ОК. Результат показан на рис.30.
   

В соответствии с заданными в диалоговом окне Промежуточные итоги параметрами Excel выполнит следующие действия:

• Создаст формулу подведения итогов для каждого изменения значений в столбце Название административной единицы.
  
• Построит формулу с функцией СУММА для столбца Численность населения (чтобы сложить значения в этом столбце по каждой группе)
  
• Поместит промежуточные итоги под каждой группой данных, а общие итоги – в конце списка.
  

Используемая при подведении итогов в области данных по умолчанию функция зависит от типа данных:

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

Список на рис.30 структурирован, этим можно воспользоваться, чтобы

• Вывести только общие итоги, щелкнув символ уровня строки 1, результат показан на рис.31 (кнопки 1, 2, 3 находятся в левом углу таблицы).
  
• Вывести общие и промежуточные итоги, щелкнув символ уровня строки 2, результат показан на рис.32.
  
• Вывести полный список, щелкнув символ уровня 3, результат показан на рис.30.
  

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

1. Щелкнуть символ уровня строки 2 для ввода только основных и промежуточных итогов.
   
2. Отсортировать этот «свёрнутый» по убыванию поля Численность населения.
   

После сортировки связь детальных и соответствующих итоговых строк сохраняется.


Формула в ячейке D11 на рис.33 может служить примером того, как Excel подводит итоги. В ней использована функция ПРОМЕЖУТОЧНЫЕ ИТОГИ(), в которой аргумент 9 задает тип вычислений. Не стоит применять собственные формулы подведения итогов, к интервалу, содержащему эту функцию.





Флажок Конец страницы между группами в диалоговом окне Промежуточные итоги дает возможность распечатать каждую группу списка с итогами на отдельной странице.

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


Функции баз данных


Функции для работы с базами данных:

• БСЧЁТ - подсчитывает количество ячеек, содержащих числа;
  
• БСЧЁТА - подсчитывает количество непустых ячеек;
  
• ДМАКС - ищет максимальное значение;
  
• ДМИН - ищет минимальное значение;
  
• БДСУММ - вычисляет сумму числовых значений;
  
• БДПРОИЗВЕД - перемножает числовые значения;
  
• ДСРЗНАЧ - считает среднее значение;
  
• ДСТАНДОТКЛ - оценивает стандартное отклонение;
  
• ДСТАНДОТКЛП - вычисляет стандартное отклонение по генеральной совокупности;
  
• БДДИСП - оценивает дисперсию;
  
• БДДИСПП - вычисляет дисперсию по генеральной совокупности;
  
• БИЗВЛЕЧЬ - ищет одну запись (если критерию удовлетворяют несколько записей, возвращается ошибка #ЧИСЛО!).
  

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

=БДСУММ(A6:F75; "Численность населения, тыс.чел."; A1:A2)

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

Обратите внимание на правила обращения к функциям баз данных:

• Первый аргумент задает весь список, а не отдельный столбец.
  
• Второй аргумент задает столбец, элементы которого необходимо просуммировать. Усреднить и т.п.
  
• Вторым аргументом может быть заголовок столбца в виде текстовой константы или порядковый номер столбца в списке. Например, в формуле на рис.34 вторым аргументом могло бы быть число 4 потому, что столбец Численность населения – четвертый в списке.
  
• Третий аргумент задает интервал критериев. В функциях баз данных могут использоваться любые допустимые критерии.
  

Список сайтов по работе в Microsoft Excel


ссылка скрыта – полезная информация по работе в Microsoft Excel.

Коллекция приёмов, on-line-учебник, галерея файлов, ссылки на интернет-ресурсы.

ссылка скрыта – Проэксель – самоучитель (учебник) содержит: программы курсов, упражнения, видеоролики.

ссылка скрыта – Учебник. Знакомство с Microsoft Excel 97. Для знакомства с возможностями Excel предлагаются семь занятий.