Статистические, финансовые, логические функции в Microsoft Excel

Инструкционная карта

Лабораторная работа №____

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

Цель работы - Привить у студентов навыки создания и обработки баз данных в Microsoft Excel.

Краткие теоретические сведения

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

Использоване списка в качестве базы данных

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

Столбцы списков становятся полями базы данных.

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

Каждая строка списка преобразуется в запись данных.

Требования к базе данных (списку) на листе Excel.

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

На листе не следует помещать более одного списка. Некоторые функции обработки списков, например, фильтры, не позволяют обрабатывать несколько списков одновременно.

Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец.

В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

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

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

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

Не следует помещать пустую строку между заголовками и первой строкой данных.

Желательно также придерживаться следующих советов:

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

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

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

Для упорядочения ячеек по значениям (без учета формата) предусмотрен определенный порядок сортировки.

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

Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем (от 1 до 9, от А до Я) или убывающем (от 9 до 1, от Я до А) порядке.

По умолчанию списки сортируются в алфавитном порядке. Для сортировки месяцев и дней недели в соответствии с их логическим, а не алфавитным порядком следует использовать пользовательский порядок сортировки. Такой порядок сортировки также используется, если требуется отсортировать список в другом, особом порядке. Например, если в одном из столбцов списка содержатся значения «Низкий», «Средний» или «Высокий», можно создать такой порядок сортировки, что строки, содержащие «Низкий», будут первыми, «Средний» - следующими и «Высокий» - последними. (При помощи Сервис, Параметры, Списки можно создать собственный порядок сортировки).

Для сортировки списков в одном столбце следует использовать кнопки «По возрастанию» и «По убыванию».

Для сортировки списка по двум или более столбцам необходимо:

1 Указать любую ячейку в сортируемом списке.

2 Выбрать Данные, Сортировка.

3 Указать столбцы сортировки в полях Сортировать по и Затем по.

Чтобы отсортировать данные по более, чем трем столбцам одновременно, отсортируйте список сначала по трем наименее значимым столбцам. Например, если список содержит сведения о сотрудниках и его требуется отсортировать по полям «Отдел», «Должность», «Фамилия», «Имя» и «Отчество», выберите сначала «Имя» в поле Сортировать по, «Отчество» в поле Затем по и отсортируйте список. Затем выберите «Отдел» в поле Сортировать по, «Должность» в поле Затем по, «Фамилия» в поле В последнюю очередь, по и отсортируйте список.

4 Выбрать другие параметры сортировки и нажать кнопку OK.

5 Повторить шаги 2 - 4 для следующих более значимых столбцов.

Автофильтр. Для применения автофильтра необходимо:

Щелкнуть любую ячейку внутри списка.

Выбрать Данные, Фильтр, Автофильтр. В ячейках с именами полей списка должны появиться кнопки со стрелками вниз.

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

Выбрать значение в списке.

Повторить шаги 3 и 4, чтобы ввести дополнительные ограничения для значений в других столбцах.

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

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

В правом верхнем поле ввода ввести соответствующее значение.

Если необходимо отобрать строки, удовлетворяющие одновременно двум условиям отбора, то надо щелкнуть флажок «И», а затем сформировать второе условие в нижней части окна.

Если необходимо отобрать строки, удовлетворяющие одному из двух условий отбора, то надо щелкнуть флажок «ИЛИ», а затем сформировать второе условие в нижней части окна.

Расширенный фильтр.

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

1 Скопируйте из списка заголовки фильтруемых столбцов.

2 Вставьте скопированные заголовки столбцов в первой пустой строке диапазона условий отбора.

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

4 Укажите ячейку в списке.

5 Выберите пункт Фильтр в меню Данные, а затем - команду Расширенный фильтр.

6 Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель Обработка в положение Фильтровать список на месте.

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

7 Введите в поле Диапазон критериев ссылку на диапазон условий отбора, включающий заголовки столбцов.

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

Совет. Если на листе существует диапазон с именем Критерии, то в поле Диапазон условий автоматически появится ссылка на этот диапазон

Примеры условий отбора расширенного фильтра

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

Подведение промежуточных итогов в списке (базе данных).

1 Отсортировать список по столбцу, для которого необходимо подвести промежуточный итог. Например, чтобы просуммировать единицы продукции, проданные каждым лицом в списке продавцов, объемы продаж и количество проданного, необходимо отсортировать список по столбцу продавцов.

2 Указать ячейку в этом списке.

3 Выбрать команду Итоги в меню Данные.

4 Выбрать столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.

5 Выбрать функцию, необходимую для подведения итогов, из списка Операция.

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

Проверка данных при вводе

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

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

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

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

ЗАДАНИЕ

Создайте в своей рабочей книге новый лист под названием П5. Заполните в нем базу данных с помощью Формы:

1) Заполним базу данных под названием «Планеты Солнечной ситемы». Введите в 1-ю, 2-ю и 3-ю строки листа следующие данные:

Планеты Солнечной ситемы

Планета

Период

Расстояние

Диаметр

Масса

Спутники

Солнце

0

0

13929

2000000

0

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

Планеты Солнечной ситемы

Планета

Период

Расстояние

Диаметр

Масса

Спутники

Солнце

0

0

13929

2000000

0

Меркурий

0,241

58

4,9

0,32

0

Венера

0,615

108

12,1

4,86

0

Земля

1

150

12,8

6

1

Марс

1,881

288

6,8

0,61

2

Юпитер

11,86

778

142,6

1906,98

16

Сатурн

29,46

1426

120,2

570,9

17

Уран

84,01

2869

49

87,24

14

Нептун

164,8

4496

50,2

103,38

2

Плутон

247,7

5900

2,8

0,1

1

Ход выполнения работы:

1) Внесите в ячейки B2:E2 следующие примечания (Вставка-Примечание): «Период обращения по орбите в земных годах»; «Среднее расстояние от Солнца, в млн.км»; «Экваториальный диаметр, тыс.км»; «Масса в 10^24кг».

Скопируйте таблицу на Лист2 - Лист4

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

a) Первый ряд базы данных содержит неповторяющиеся имена полей.

b) Остальные ряды базы данных содержат записи, которые не являются пустыми рядами

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

2) Лист 1. Щелкните любую ячейку внутри базы данных и выберите команду: Данные, Фильтр, Автофильтр. На именах полей должны появиться кнопки с изображением стрелок. Понажимайте эти кнопки и исследуйте их работу.

3) Нажмите кнопку на поле «Планета». Выберите пункт «Условие». В появившемся окне установите параметры так, чтобы отобрать планеты, названия которых начинаются с букв «С» или «Ю».

4) После этого нажмите кнопку на поле «Масса» и выбрав пункт «Условие», оставьте в списке только те планеты, масса которых <600.

5) Лист 2. Условия фильтрации: найдите все планеты, диаметр которых меньше 50 тыс.км и масса которых меньше 4*10^24 кг.

6) Лист 3. Найдите все планеты, находящиеся от Солнца на расстоянии не менее 100 млн. км., имеющих массу в диапазоне от 3*10^24 кг до 500*10^24 кг, а также не более 2-х спутников.

7) Лист 4. Теперь перейдем к созданию Расширенного фильтра. Для этого вначале скопируйте 1-й ряд базы данных (2-я строка) в строки 15 и 20 Вашего рабочего листа. В ячейке B16 (поле Период) укажите условие >10. В ячейке F16 (поле Спутники) укажите условие >=2. Щелкните любую ячейку внутри базы данных. Затем выполните команду Данные, Фильтр, Расширенный фильтр. В появившемся окне установите флажок "Скопировать результат в другое место". Поле "Исходный диапазон" должно иметь значение $A$2:$F$12. В поле «Диапазон условий» установите $A$15:$F$16. В поле «Поместить результат в диапазон» укажите $A$20:$F$25. Нажмите кнопку Ok. В итоге Вы должны отобрать 4 планеты: Юпитер, Сатурн, Уран, Нептун.

8) Скопируйте заголовки столбцов в строки Н15:М15 и Н20:М20. В ячейке Н16 (поле Период) укажите условие >10, в ячейке М17 (поле Спутники) укажите условие >=2. Затем выполните команду Данные, Фильтр, Расширенный фильтр с правильным указанием параметров «Диапазон условий» и «Поместить результат в диапазон».

Сравните два результата фильтрации. В чем отличия.

9) С использованием расширенного фильтра найдите планеты:

а) «Диапазон условий»:

Планета

Период

Расстояние

Диаметр

Масса

Спутники

М*

С*

Земля

б) имеющие период обращения более 2-х земных лет и экваториальный диаметр менее 50 тыс.км., или имеющих спутников более 15.

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

10) Покажите работу преподавателю.

11. Новый лист назовите Полный ассортимент, он должен выглядеть так:

11. С помощью команды Данные -Фильтр - Автофильтр на листе Полный ассортимент отфильтруйте базу данных: оставьте только персональные ксероксы. Восстановите весь список.

12. С помощью команды Данные -Фильтр - Автофильтр на листе Полный ассортимент отфильтруйте базу данных: оставьте только профессиональные и профессиональные плюс факсы.

13. Восстановите весь список и отключите автофильтр.

14. С помощью команды Данные -Сортировка отсортируйте базу данных на листе Полный ассортимент по виду и названию оргтехники.

    1. В меню Данные выберите команду Сортировка.

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

    3. В группе затем по щелкните на кнопке «вниз» и выделите ключ Название.

    4. ОК.

Теперь в списке группа Факс расположена перед группой Ксерокс, а внутри групп названия отсортированы в алфавитном порядке по возрастанию.

11. Составьте итоговый отчет на листе Ксероксы:

• На листе ксероксы выделите ячейку С10

• В меню Данные выберите команду Итоги. Откроется диалоговое окно Промежуточные итоги.

• Щелкните на стрелке текстового поля При каждом изменении в и в появившемся окне списка выделите строку Название. Так вы подведете итог по каждому из названий ксероксов.

• Убедитесь, что в поле Операция находится слово Сумма.

• Убедитесь, что в поле Добавить итоги по флажок установлен напротив строки Сумма. Для этого, если потребуется, прокрутите весь список.

• Проследите, чтобы были установлены флажки напротив строк Заменить текущие итоги и Итоги под данными, и щелкните кнопку ОК.

Таким образом, вы получите итоговые значения суммарной стоимости каждого вида ксероксов. Рабочий лист должен выглядеть так:

11. Удалите промежуточные итоги:

• Убедитесь, что одна из ячеек подытоженных данных выделена.

• В меню Данные выберите команду Итоги. Откроется диалоговое окно Промежуточные итоги.

• Щелкните в нем на кнопке Убрать все.

Таблица данных вернется к прежнему виду.

11. Введите вложенные промежуточные итоги (Усредните стоимость и цену по видам товаров):

• Сначала выполним сортировку, для этого перейдите на лист Полный ассортимент и выделите ячейку С10.

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

• Убедитесь, что в группе Сортировать по находится ключ Товар, и включите опцию По возрастанию.

• В группе Затем по установите ключ Название и включите опцию По возрастанию.

• Выделите ячейку С10 и из меню Данные выберите команду Итоги. Откроется диалоговое окно Промежуточные итоги.

• В окне списка При каждом изменении в выделите Товар.

• В окне списка Операция выделите функцию Среднее.

• Проследите, чтобы в окне списка Добавить итоги по были установлены флажки напротив строк Стоимость и Цена.

• Удалите флажок в поле Заменить текущие итоги и щелкните ОК.

• В меню Данные выберите команду Итоги. Откроется диалоговое окно Промежуточные итоги.

• Щелкните на стрелке поля При каждом изменении и в появившемся списке выделите Название.

• В окне Операция выделите функцию Среднее.

• Проследите, чтобы в окне Добавить итоги по флажки были установлены только напротив строк Стоимость и цена. ОК.

  17. Проведите структурирование рабочих листов:

Скройте, а затем покажите детали структуры

• Щелкните на всех кнопках структуры второго уровня. Рабочий лист отображает только средние значения стоимости и цены по двум группам товаров.

• Перейдите к строке 44. в этой строке дублируется информация из предыдущей строки. Выделите заголовок строки и удалите все её содержимое.

• Снова раскройте все структуры второго уровня. Для этого щелкните на кнопках структуры второго уровня (они приобрели вид +).

• Щелкните на всех кнопках третьего уровня. Теперь на рабочем листе видны средние по всем названиям товаров и общее среднее.

Покажите, а затем скройте детали списка персональных ксероксов

• Поместите указатель мыши на кнопку показа деталей, соответствующую названию ксерокса «Персональный» (кнопка со знаком + напротив строки Персональный Среднее), и щелкните левой кнопкой мыши.

На экране появится список персональных ксероксов, а детали остальных названия копировальной техники останутся скрытыми.

• Снова щелкните на этой же кнопке (на ней теперь изображен - и она работает как кнопка скрытия).

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

18. Сохраните книгу «ПР_№5_ФИО».

Контрольные вопросы:

1. Какая таблица может быть использована в качестве базы данных?

1. Как задать допустимые значения для элементов базы данных?

1. Как отсортировать данные по нескольким полям?

1. Как вывести первые несколько элементов базы данных?

1. Как настроить расширенный фильтр?

1. Как показать промежуточные итоги для базы данных?