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

Практическое занятие №1

 

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

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

 

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

 

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

 

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

 

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

 

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

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

В списке 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.

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

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

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

 

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

 

При рассмотрении команды Итоги будет использоваться список, приведенный на рис.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 потому, что столбец Численность населения – четвертый в списке.
• Третий аргумент задает интервал критериев. В функциях баз данных могут использоваться любые допустимые критерии.

 

Практическое занятие №1