Работа со списком как с базой данных

Работа со списком как с базой данных

В Excel можно работать с однотабличными базами данных, представленными в виде списка. В БД хранится информация об однотипных объектах.

Например:

о библиотечные карточки о книгах;

о читательские билеты;

о школьный журнал;

о листы по контрольной неделе.

фамилия	имя	алгебра	геометрия	информатика
Березин	Антон	5	4	3
Курлинёва	Анастасия	4	5	4

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

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

Операции со списком

1. Сортировка (расположение записей в определённом порядке). Например, отсортировать по номеру группы (убыванию, возрастанию), а затем по фамилии (в прямом или обратном порядке алфавита).

2. Фильтрация – отбор записей, отвечающих определённым условиям.

3. Фильтр Автофильтр – возле каждого столбца появляется стрелка (треугольник вниз), т. е. каждое поле превращается в раскрывающийся список. Из него можно выбрать, например, конкретное значение или поставить условие на значение.

a)  Для числовых данных условие может быть: равно, не равно, больше или равно, меньше и т. д.

b)  Для текстовых значений: начинается с, не начинается с, заканчивается на, не заканчивается на, содержит, не содержит.

c)  Условий может быть два, причём они могут быть соединены логическим И или логическим ИЛИ.

d)  Когда на поле наложено условие, цвет стрелки-списка меняется.

4. Расширенный фильтр даёт возможность включить более двух условий для одного поля, а также условие ИЛИ для разных полей.

5. Пример: найдем мальчиков или высоких (>168) девочек. На рисунке – результат фильтрации.

6. Схема работы с расширенным фильтром:

a)  Вставить строки в верхнюю часть таблицы (5-6 строк).

b)  Строку с названием полей скопировать в первую строку. Это будет диапазон условий.

c)  Записать условия: условия в строке соединяются логическим И, строки между собой – логическим ИЛИ.

7. Для восстановления списка – Фильтр Отобразить всё

8. Итоги (по группам)

a)  Необходимо определить группы. Какие могут быть группы? Например: мальчики и девочки, «отличники» и прочие, городские и сельские и т. д.

b)  Итоги – это среднее, сумма, количество значений в группах и т. д.

c)  По каким полям имеет смысл подводить итоги? По тем, значения которых дискретны и число их относительно невелико. Например, по росту нет смысла группировать, если записей 50, а различных значений роста 30.

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

e)  В диалоговом окне надо определить:

e. i.  при изменении значений какого поля считать итоги;

e. ii.  Операцию (сумма, максимум, минимум, произведение и т. д.);

e. iii.  по какому полю итог.

Пример: группы по цвету волос, средняя оценка по математике. Будут подведены итоги по группам и по списку в целом.

Можно свернуть информацию и увидеть только итоги (без данных).

9. Проверка – даёт возможность следить за вводом данных, чтобы не допустить случайных ошибок:

a)  Для текста можно использовать шаблон или ограничить длину слова.

b)  Для числовых значений установить диапазон.

c)  Задать сообщение об ошибке.

d)  Установить сообщение-подсказку об ограничениях ввода.

10. Сводные таблицы (мы ограничимся данными в списке Excel, не рассматривая внешние).

11. Сводные таблицы предназначены для обобщения данных. Например, если у нас есть два поля, значения которых дискретны и принадлежат определённому диапазону (группа и место жительства). Фактически мы вновь создаём группы, но уже по значению двух (как минимум) свойств. Значения расположены в заголовках столбцов и в заголовках строк в таблице, а на перекрёстке значений – итог.

12. По команде Сводная таблица подключается мастер, который по шагам ведёт нас к решению поставленной задачи.

1-й. На какой основе создаём список.

2-й. Указать диапазон исходных данных.

3-й. Создать МАКЕТ, т. е. «перетащить»:

a)  поле на область Столбец;

b)  поле на область Строка;

c)  поле на область Данные;

d)  двойной щелчок открывает окно, где можно установить функцию (формат и т. д.);

e)  расположить список на отдельном листе или на этом в определённом диапазоне.

Работа со сводными таблицами очень богата. Мы привели лишь простейший из возможных вариантов. Во-первых, полей может быть больше 3-х, во-вторых, с уже готовой сводной таблицей можно работать с помощью панели Сводные таблицы.