ТЕМА: РАБОТА СО СПИСКАМИ И БАЗАМИ ДАННЫХ В MS EXCEL
Список – это упорядоченный набор данных. Обычно список состоит из строки заголовков (текст описания) и строк данных, которые могут быть числовыми или текстовыми. Список можно считать табличной базой данных, чем он, в сущности, и является.
Столбцы списка обычно называют полями, а строки – записями. Теоретически, размер списка ограничен размерами одного рабочего листа. Однако список такого размера занимает очень много памяти и программа плохо с ним справляется.
В Excel есть несколько средств, предназначенных для работы со списками. Они могут быть использованы для самых разнообразных целей. Для одних пользователей список – это способ простого хранения набора данных (например, список клиентов), другие используют его для хранения данных, которые в конечном итоге должны быть включены в отчет. Ниже приведены операции, которые обычно выполняются над списками.
Ввод данных в список.
Фильтрация списков, которая выполняется в целях выборочного отображения строк (по определенному критерию).
Сортировка списков.
Вставка формул для подведения промежуточных итогов.
Создание формул для вычисления результатов в списке, отфильтрованном по определенным критериям.
Создание итоговой сводной таблицы на основе данных списка.
В данной работе нами будут рассмотрены некоторые из перечисленных операций.
1. Ввод данных в список. Использование формы данных
Ввод данных начинается с определения заголовков столбцов. Они должны располагаться в первой строке диапазона вводимых данных. Заголовки нужны для того, чтобы описываемая команда работала правильно.
При создании списков нужно помнить о некоторых рекомендациях:
в каждом столбце должна содержаться однотипная информация, например, не следует смешивать в одном столбце даты и обычный текст;
не используйте пустых строк в списке, т.к. при проведении операций над списком Excel определяет его границы автоматически, при этом пустая строка обозначает конец списка;
помещайте список на отдельный лист, если на этот лист необходимо поместить еще другую информацию, помещайте ее ниже или выше списка, не используйте для этого ячейки справа или слева от списка;
используйте команду Закрепить областина вкладкеВидв группеОкно, чтобы заголовки были всегда видны при прокручивании листа списка;
старайтесь предварительно отформатировать весь столбец, чтобы данные всегда имели один и тот же формат.
Задание:
Создайте новый файл под именем ЛабрабExcel2.
Скопируйте таблицу 1 на Лист1 так, чтобы Фамилия находилась в ячейке A1.
Таблица 1
Фамилия
Имя
Отчество
телефон
взнос
Иванов
Петр
Семенович
33-3333
5 000,00р.
Петров
Иван
Иванович
44-8855
4 500,00р.
Сидоров
Сергей
Иванович
78-5692
7 500,00р.
Орлов
Антон
Семенович
33-4578
8 000,00р.
Соколов
Михаил
Сергеевич
47-2386
10 000,00р.
Петухов
Алексей
Иванович
17-8866
2 500,00р.
Воробьев
Максим
Сергеевич
44-7785
3 000,00р.
Синицын
Иван
Семенович
52-6851
4 200,00р.
Установите ширину столбцов так, чтобы данные в них отражались полностью.
2. Проверка вводимых значений
Excel позволяет указать тип данных, которые могут помещаться в определенной ячейке или диапазоне. Например, вам нужно разработать таблицу, которую будут использовать другие люди. Допустим, в этой таблице есть ячейка для ввода данных, на которую имеется ссылка в некоторой формуле. Предположим также, что в этой конкретной ячейке должно находиться число от 1 до 12, так как только при этом условии формула будет давать допустимые результаты. В этой ситуации вы можете воспользоваться функцией проверки вводимых данных, чтобы, в случае, если пользователь введет значение, не принадлежащее интервалу от 1 до 12, программа выдавала соответствующе сообщение.
Задание:
В таблице на Листе1 в ячейке F1 введите название столбца Возраст.
Выделите диапазон F2:F9.
Выберите пункт Проверка данных…после нажатия кнопкиПроверка данныхна вкладке Данные к группе Работа с данными. Появится на экране диалоговое окно Проверка вводимых значений с тремя вкладками.
Выберите вкладку Параметры и укажите тип данных, которые должны находиться в данной ячейке. Например, выберите в поле Тип данных – целое число, в поле Значение - между. В поле Минимум введите значение 18, в поле Максимум – 100 (рис. 1).
Рис. 1
Щелкните на вкладке Сообщение для вводаи в поле Сообщение введите текст, который должен появляться на экране при выделении указанной ячейки (это необязательно). Это сообщение появится в маленьком всплывающем окне. Например, введите сообщение Укажите возраст вкладчика (рис. 2).
Рис. 2
Выберите вкладку Сообщение об ошибкеи введите текст сообщения. Например, в поле Видвыберите значение Останов(программа не даст вводить значения дальше до тех пор, пока не будет введено допустимое значение). В поле Заголовоки в поле Сообщениевведите соответствующий текст (рис. 3). Нажмите кнопку ОК.
Рис. 3
Введите значения в столбец Возраст, при этом намеренно укажите неправильные значения, чтобы убедиться, что проверка данных работает. При введении неправильного значения, должно появляться сообщение об ошибке.
Добавьте к таблице поле Вид счета.
В ячейки А12:А14 введите следующие данные (рис. 4).
Рис. 4
Выделите диапазон G2:G9. Выберите пункт Проверка данных…после нажатия кнопкиПроверка данныхна вкладке Данные к группе Работа с данными.
Заполните поля, как указано на рис. 5. В качестве источника укажите диапазон А13:А14.
Рис. 5
Укажите сообщения для ввода и сообщения об ошибке.
Введите виды счетов, выбирая счет из списка (рис. 6).
Рис. 6
Задание для самостоятельной работы:
Добавьте к таблице поле Дата. Назначьте проверку вводимым данным: укажите диапазон с 1 января по 31 декабря текущего года.
Введите новое поле под названием Срок вклада. Сроки вкладов должны быть не менее 2 лет.
Дополните таблицу полем Город. Задайте список возможных значений: Москва, Новороссийск, Екатеринбург, Омск.
3. Фильтрация списка
Фильтрация списка – это процесс сокрытия всех строк, кроме тех, которые удовлетворяют определенным критериям. Например, если у вас есть список клиентов, его можно отфильтровать так, чтобы видеть фамилии только тех из них, которые живут в определенном городе. В данной работе будет рассмотрена автоматическая фильтрация.
Фильтрация по одному критерию
Задание:
Активизируйте Лист2 и назовите его Фильтр.
Скопируйте данные, приведенные в таблице 1.
Таблица 1
Фамилия
Имя
Отчество
Оклад
Годовой фонд зарплаты
Отдел
Дата приема на работу
Андреева
Анна
Семеновна
7 166,67р.
86 000,04р.
Бухгалтерия
04.11.2002
Ноткин
Иван
Семенович
9 000,00р.
108 000,00р.
Бухгалтерия
09.07.1998
Горбатов
Иван
Семенович
9 916,67р.
119 000,04р.
Склад
11.05.2001
Крылова
Ольга
Сергеевна
8 083,33р.
96 999,96р.
Склад
06.09.1999
Андреева
Анна
Олеговна
6 250,00р.
75 000,00р.
ОК
02.01.1999
Ерохин
Иван
Федорович
8 541,67р.
102 500,04р.
Бухгалтерия
07.08.2000
Петрова
Мария
Андреевна
10 375,00р.
124 500,00р.
Склад
11.04.1994
Крылова
Ирина
Максимовна
9 458,33р.
96 999,96р.
Цех №1
09.06.2006
Васин
Игорь
Петрович
7 625,00р.
91 500,00р.
Цех №2
05.10.2001
Самойлов
Семен
Петрович
6 708,33р.
80 499,86р.
Бухгалтерия
03.12.2000
Бершев
Никита
Иванович
8 450,12р.
101 401,44р.
Цех №1
03.01.2000
Быстрова
Татьяна
Олеговна
7 120,54р.
85 446,48р.
ОК
05.12.1999
Доценко
Иван
Сергеевич
9 800,78р.
117 609,36р.
Цех №2
15.07.2003
Фандеев
Петр
Иванович
8 657,89р.
103 894,68р.
Цех №2
09.06.2001
Конов
Алексей
Алексеевич
6 852,64р.
82 231,68р.
Цех №1
05.11.2001
Установите табличный курсор на одну из ячеек списка.
Нажмите кнопкуФильтрна вкладке Данные к группе Сортировка и фильтр.Добавится в строку заголовков полей кнопки раскрывающихся списков (кнопки автофильтра): (рис. 10).
Рис. 7
Щелкните на кнопке автофильтра в ячейке Отдел. Список раскроется и покажет все значения, содержащиеся в этом столбце (рис. 8).
Рис. 8
Выберите наименование какого-нибудь отдела, и Excel спрячет все строки, кроме тех, которые включают отмеченное значение (рис. 9). Другими словами, критерием отбора служит выбранное вами значение.
Рис. 9
Обратите внимание, что после фильтрации в строке состояния появилась сообщение о том, сколько строк отобрано: . Кроме того, изменился цвет кнопки автофильтра , чтобы напомнить вам, что список отфильтрован по значениям, содержащимся в этом столбце.
Снова щелкните на кнопке автофильтра и выберите опцию (Выделить все). Программа отобразит весь список полностью.