Формирование списка.

Аналогом простой базы в Excel служит список. Список - группа строк таблицы, содержащая связанные данные, причем каждый столбец списка содержит однотипные данные.

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

Совместитель-многодетный; 21. Справочный столбец.

Непрерывный стаж с;

Перенесите список в Excel, начиная с ячейки А2.

Обработайте перенесенные текстовые данные.

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

в ячейку В2 введите формулу =ДЛСТР(А2) для определения длины текста заголовка, протяните формулу на диапазон В3:В22;

в ячейку С2 введите формулу =ЛЕВСИМВ(А2;В2-1) для удаления последнего символа из заголовка;

в ячейку D2 введите формулу =ПРАВСИМВ(С2;В2-4) для удаления начальных символов из заголовка;

В результате таблица с формулами примет вид: Ш Microsoft Excel - База данных Га Файл Правка Вид Вставка Формат Сервис Данные Окно Справка Введите вопрос Ў _ G х \ ! J J iJ i J 1 d Д 1 ^ i й1« >&• j 1 Ш wa% - ® | : Arial Cyr . 10 - Ж к ч ¦ Е = = т 1 щ % ООО tiS 1 iF iF ЕВ • > - д - g Е2 т Г, =ДЛСТР(А2) А В с D — 1 2 1. Порядковыйномер; j 1 =ЦП:ТР,-О | 1 =ЛЕВСИМВ(А2;В2-1) =ПРАВСИМВ(С2;В2-4) th 3 2. Т аб епьный номер, -ДЛСТР(АЗ) ' -ЛЕВСИМБ(АЗ,БЗ-1) -ПРАВСИМВ(СЗ,ВЗ-4) 4 3. Фамилия; =ДЛСТР(А4) =ЛЕВСИМВ(А4;В4-1) =ПРАВСИМВ(С4;В4-4) 5 4.

создайте в столбце D сложную формулу для обработки текста, для этого:

активизируйте ячейку В4 и в режиме правки в строке формул скопируйте находящуюся в этой ячейке формулу без знака равенства;

нажмите Enter и поместите табличный курсор в ячейку С4;

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

=ПРАБСИМБ(ЛЕВСИМВ(Л2; ДЛСТР(Л2)-1); ДЛСТР(Л2)-4), проверьте правильность созданной формулы, удалив столбцы В и С;

Перенесите заголовки из столбца в строку:

выделите и скопируйте в буфер обмена полученный после обработки текст;

поместите табличный курсор в ячейку А1, которая будет служить началом строки заголовка списка;

из контекстного меню выберите Специальная вставка;

отметьте опции значения и транспонировать, Ок.

Введите данные в базу данных.

Просмотр табличной базы данных

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

открытие нескольких окон

разделение таблицы на области

закрепление областей таблицы

Работа с окнами

Возможно для одного и того же рабочего листа открыть два окна: Ок- но^Новое. В списке появится два имени База1, База2. Команда Окно^Расположить, позволяет изменить расположение окон. Переключение между окнами:

щелчок указателем мыши на этом окне

Ctrl+Tab

Окно^Имя нужного окна

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

Разделение таблицы на области

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

Окно^Разделить.

Каждое из окон имеет независимые области прокрутки.

Закрепление областей

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

Окно^Закрепить области.

Для отмены закрепления областей Окно^Снять закрепление областей.

Отбор данных

Может выполняться с помощью Автофильтра и Расширенного фильтра.

Укажите любую ячейку таблицы.

Данные^Фильтр^Автофильтр.

В столбце, в котором нужно произвести отбор, из списка выберите нужный критерий отбора. Например, если курсор был поставлен в столбец Фамилия: Ш Microsoft Excel - База донных

Рисунок 29

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

Отбор по наименьшему или наибольшему значению

В столбце, содержащем числа, нажмите кнопку со стрелкой и выберите вариант (Первы е 10...).

В поле слева введите количество записей для показа.

В среднем поле выберите вариант наибольших или наименьших.

В поле справа выберите вариант элементов списка или процент от количества элементов.

Например:

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

Наложение условия по списку |Xj п 7 ^ наибольших v элементов списка [ ОК | Отмена ] 2. Отбирается 7% записей, в которых ячейки текущего столбца содержат наименьшие значения в списке. Наложение условия по списку fX] 1 1 1 1 \гшшят 17 ¦*¦ наименьших v ЕоКХЯвЯ 1 ОК [ Отмена | Пользовательский автофильтр Появляется при выборе категории Условие. Оператор ИЛИ позволяет отображать строки, удовлетворяющие одному из двух критериев отбора, а оператор И - строки, удовлетворяющие обоим критериям одновременно. Например, задан отбор записей с должностями Начальник или Менеджер.

Пользовательский автофильтр [х]

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

Вызывается командой Данные^Филътр^Расширенный фильтр. Позволяет задавать условия отбора одновременно для нескольких полей

Вставить перед списком несколько пустых строк.

Сформировать диапазон условий:

в первую строку скопировать заголовки фильтруемых столбцов;

во вторую ввести условия отбора. Например:

а. Отбор женщин, работающих в отделе Контроля. Ш Microsoft Excel - База данных IPJ файл Правка Вид Вставка Формат Сервис 4анные Окно Справка - (Э X 1 ; J Jdij 1А 2 241И 4} т J I Arial Cyr т 9 .

ый номер Фамилия Имя Отчество Отдел Должность Дата приема на работу Дата увольнения Пол - 5 1 ' 0123 ИваНОБ Иван Иванович Реализации Менеджер 01.02.1995 М ] к b 2 г 1234 Петров Петр Петрович Реализации Менеджер 02.03.1996 М ] с 7 3 " 2345 Иваненко Иван Петрович Реализации Начальник 04.05.1997 М J 0 8 4 г 3456 Сидорова Елена Сидоровна Контроля Начальник 03.04.1993 Ж , Ё 9 5 г 4567 Суров Петр Сндорович Снабжения Начальник 05.06.1998 М ] F 10 6 F 5678 Сидоренко Сидор Иванович Снабжения Инженер 06.07.1992 М ] U V 1 н 4 > нК Сотрудники/ |< > I Готово NUM Ь. Отбор мужчин с перечисленными именами'. Ш Microsoft Excel - База данных I Файл Правка Вид Вставка Формат Сервис Данные Окно Справка - t? X1 и и* и 1 а 1 у a 1 * .v / -о- & ^ - ai и \ ш $ ® А : Times New Roman Cyr . 9 . ж К uj =- -= ИЯ 1 Щ ч. мм "зВ Л 1 Щ . . А, . | ! DB - fi, Иван А В С D Е F G Н 1 j _ А 1 Фамилия Имя Отчество Дата рождения Пол 2 Иван м : J 3 Петр м 4 5 №п.п. Табельн

ый номер фамилия Имя Отчество Отдел Должность Дата приема на работу Дата увольнения Пол 6 1 ' 0123 Иванов Иван Иванович Реализации Менеджер 01.02.1995 М Ё 7 2 ' 1234 Петров Петр Петрович Реализации Менеджер 02.03.1996 М г В 3 ' 2345 Иваненко Иван Петрович Реализации Начальник 04.05.1997 М п Ы 4 ' 3456 Сидорова Елена Сндоровна Контроля Начальник 03.04.1993 Ж i 10 5 ' 4567 Суров Петр СхшороЕКЧ Снабжения Н ачата ник 05.06.1998 м 1 11 6 ' 567S Сндоренво Сидор Иванович Снабжения Инженер 06.07.1992 м и 12 7 ' 6789 Карпова Юлия Макаровна Контроля Аудитор 29.09.1999 ж 1 13 8 Г 7890 ТТйИПГн Татьяна Игооевна Реализации . Секоетапь 17.10.1996 ж iv 1 1 м 4 > и \Сотрудники/ | I Готово < -1111 J > NUM с. Отбор мужчин, имеющих имя Иван или отчество Петрович: Microsoft Excel - База данных : Pj файл Правка Вид Вставка Формат Сервис Данные Окно Справка _ б1 X ! J А Л & А1 ^ а 1 Л ^ J, J1 "9 — Ч & Е - а 211Ш © ё j Times New Roman Cyr - 9 - Ж К Ч | Ш Ш Ш | % ООО teS ?1 1 W ^ 1 ЕВ т & т А т В Е8 - f* Пет рович А В С D Е F G н 1 J _ Л 1 Фамилия Имя Отчество Дата рождения Пол 2 Иван м 3 Петрович м 4 5 №п.п. Табельн

ый номер Фамилия Имя Отчество Отдел Должность Дата приема на работу Дата увольнения Пол b 1 0123 Иванов Иван Иванович Реализации Менеджер 01.02.1995 м в / 2 1234 Петров Петр Петрович Реализации Менеджер 02.03.1996 м с и 3 2345 Иваненко Иван Петрович Реализации Начата шаг 04.05.1997 м Л 9 4 ' 3456 Сидорова Елена Сидоровна Контроля Начальник 03.04.1993 ж 1 10 5 г 4567 Суров Петр Сидорович Снабжения Начальник 05.06.1998 м F 11 6 г 5678 Сидоренко Сидор Иванович Снабжения Инженер 06 07 1992 м и 12 7 г 6789 Карпова Юлия Макаровна Контроля Аудитор 29.09.1999 ж 1 13J 8 Г 7890 Легиова Татьяна Игопевна Реализации , Секпетапь 17 10 1996 ж г м < > нIXСотрудники/ |< > | Готово NUM Установить курсор внутри списка данных и выполнить команды Дан- ные^Филътр^Расширенный фильтр.

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

Указать диапазон условий отбора, включая заголовки.

Указать, где выводить фильтрованный список (левую верхнюю ячейку диапазона).

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

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

Откройте одновременно два окна База и диапазон условий на экране, расположите их рядом.

Зафиксируйте курсор на листе База.

Данные^ Фильтр^Расширенный фильтр. Укажите необходимые диапазоны.

При задании критерия отбора в Excel могут использоваться:

текстовые константы:

строки с ячейками, значение которых начинается текстом;

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

знаки подстановки:

? - любой символ в той же позиции, что и знак вопроса;

* - любая последовательность символов в той же позиции, что и звездочка;

~?, ~*, вывод спецсимволов?, *, ~, например Где~? Ищет «Где?».

Диалоговое окно Форма

Позволяет вводить и просматривать данные. Выводится командой Данные^Форма. Выбор кнопки Критерии позволяет выводить данные по нужному параметру.