Инструкция (стр. 1 )

1.  Cнова щелкните на кнопке Добавить, если ввод данных будет продолжен, или на кнопке Закрыть формы, если вы ввели все требуемые записи.
Все введенные записи добавляются в конец списка на листе Excel.

Упражнение

Создайте список вкладчиков, показанный на рис. 102.

1.  Запустите Excel.

2.  Сохраните новую книгу в своей папке под именем «Вкладчики. xls».

3.  В ячейке A1 введите название списка и объедините ячейки A1:G1.

4.  В третьей строке введите названия полей (столбцов).

5.  В четвертой строке введите данные в ячейки A4:F4, а в ячейку G4 - формулу, которая вычисляет возраст клиента: =ГОД(СЕГОДНЯ())-F4.

6.  Остальные записи вводите с помощью Формы данных (см. п. 17.2. «Ввод очередной записи списка с помощью Формы»), при этом формула будет копироваться в последующие строки автоматически.

17.3. Просмотр записей списка

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

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

Использовать форму для перехода к какой-либо записи очень длинного списка уже неудобно, в таких случаях надо найти запись, удовлетворяющую заданному критерию (см. п. 17.4. «Поиск записей в списке»).

Замечание

Для перехода к следующей записи списка можно использовать клавишу Enter вместо кнопки Далее,
а вместо кнопки Назад – клавиши Shift+Enter.

17.4. Поиск записей в списке

Прежде чем найти данные, надо описать то, что надо найти, т. е. задать критерий (условие) поиска. Например: найти записи, в которых название лекарства – Малавит (поиск точного соответствия, см. п. 17.4.1); найти всех избирателей, фамилия которых начинается на «Зо» (поиск близкого соответствия по образцу, см. п.17.4.2); найти все товары, проданные в количестве строго больше 20 (поиск по числовому сравнению, см. п.17.4.3); определить всех вкладчиков, которые имеют вклады, превышающиерублей, и тип их вклада – текущий (поиск соответствия по множественному критерию, см. п. 17.4.4). Критерии задаются одинаково при любом способе поиска или фильтрации данных.

Замечание

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

17.4.1. Поиск по критерию точного соответствия

Рассмотрим пример списка вкладчиков, в котором требуется найти клиентов, имеющих тип вклада «Текущий».

Упражнение

1.  Откройте файл «Вкладчики. xls».

2.  Вызовите форму для этого списка (см. п. 17.1. «Вызов формы»).

3.  Щелкните на кнопке Критерии.
При этом очистятся все поля формы, а в верхнем правом углу появится запись: «Критерии». Кроме того, изменятся названия некоторых кнопок формы (рисунок I Таблицы 16).

4.  Перейдите в поле «Тип вклада:» (например, с помощью клавиши Tab).

5.  Введите в этом поле название вклада «Текущий», - естественно, без кавычек (рисунок II Таблицы 17).

6.  Щелкните на кнопке Далее формы.
В полях формы появятся данные строки списка, где указан тип вклада «Текущий».

7.  Продолжаете щелкать на кнопке Далее формы для просмотра данных всех вкладчиков, имеющих такой тип вклада. При этом в верхнем правом углу будет отображаться номер очередной записи.
Если после нажатия на кнопку Далее, номер записи в верхнем правом углу не меняется, значит это последняя запись списка, удовлетворяющая критерию поиска. Щелкая на кнопку Назад, вы можете пролистать найденные записи в обратном порядке до самой первой записи, удовлетворяющей критерию.

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

I

II

17.4.2. Поиск по критерию близкого соответствия по образцу

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

Например, если вы хотите найти всех вкладчиков, фамилия которых начинается на «Си», то в соответствующем поле нужно ввести «Си*» (маску, шаблон) – естественно, без кавычек. Символ * означает, что после «Си» могут следовать любые символы. Символ звездочки можно использовать в любом месте: в начале, в середине или в конце искомого текста. Например по маске « * пальто» будут найдены записи: «мужское пальто», «зимнее пальто» и т. п. Если вы просто указываете «Ма», то по умолчанию считается, что задан шаблон «Ма*».

Если требуется найти имя «Таня» или «Тоня», то вводите «Т? ня», символ вопроса означает один символ в строке. Символы? и * называются символами шаблона, маски.

Если требуется найти в поле сами символы? или *, то в маске перед этими символами надо поставить символ ~ (тильда). Это будет означать, что символы? или * используются не как символы шаблона.

Упражнение

Найти всех вкладчиков по фамилии, начинающейся на «Ива».

1.  Откройте файл «Вкладчики. xls».

2.  Вызовите форму для этого списка (см. п. 17.1. «Вызов формы»).

3.  Щелкните на кнопке Критерии.
При этом очистятся все поля формы, а в верхнем правом углу появится запись – «Критерии». Кроме того, изменятся названия некоторых кнопок формы.

4.  В поле «Ф. И.О.:» введите шаблон «Ива*» - естественно, без кавычек.

5.  Щелкните на кнопке Далее формы.
В полях формы появятся данные строки списка, где указана фамилия, начинающаяся на «Ива».

6.  Продолжаете щелкать на кнопке Далее формы для просмотра данных всех вкладчиков, имеющих соответствующую фамилию. При этом в верхнем правом углу будет отображаться номер очередной записи.
Если после щелчка на кнопке Далее номер записи в верхнем правом углу не меняется, значит, это последняя запись, удовлетворяющая критерию поиска. Щелкая на кнопку Назад, вы можете пролистать найденные записи в обратном порядке до самой первой записи, в которой фамилия вкладчика начинается на «Ива».

17.4.3. Поиск с использованием числового сравнения

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

Операция	Пример	Результатом будут записи, соответствующие поля которых
= (равняется)	=500 =Иванов	Равны 500 Равны и И, и , и Иванов В. В.
= (равняется)	=	Пусты
> (строго больше)	>200	Больше 200
>= (больше или равно)	>=3000 >=П	Больше или равны 3000 Начинаются на букву П и далее по алфавиту
< (строго меньше)	<12.11.98	Включают даты до 12 ноября 1998 года
<= (меньше или равно)	<=21 мар.	Включают даты до 21 марта включительно
<> (не равно)	<>50	Не равны 50

Замечание

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

Упражнение 1

Найти всех вкладчиков старше 65 лет:

1.  Откройте файл «Вкладчики. xls».

2.  Вызовите форму для этого списка (см. п. 17.1. «Вызов формы»).

3.  Щелкните на кнопке Критерии.
При этом очистятся все поля формы, а в верхнем правом углу появится запись: «Критерии». Кроме того, изменятся названия некоторых кнопок формы.

4.  Перейдите в поле «Возраст, лет:» введите условие « >65» (естественно, без кавычек).

5.  Щелкните на кнопке Далее формы.
В полях формы появятся данные строки списка, где указан возраст, превышающий 65.

6.  Продолжаете щелкать на кнопке Далее формы для просмотра данных всех вкладчиков старше 65 лет. При этом в верхнем правом углу будет отображаться номер очередной записи (строки списка).
Если после щелчка на кнопке Далее номер записи в верхнем правом углу не меняется, значит, это последняя запись, удовлетворяющая критерию поиска. Щелкая на кнопке Назад, вы можете пролистать найденные записи в обратном порядке до первой записи, в которой возраст клиента превышает 65 лет.

Упражнение 2

Найти всех вкладчиков, фамилия которых начинается с буквы П и далее в алфавитном порядке:

1.  Щелкните на кнопке Критерии.
При этом очистятся все поля формы, а в верхнем правом углу появится запись: «Критерии». Кроме того, изменятся названия некоторых кнопок формы.

2.  Перейдите в поле «Ф. И.О.:» введите условие « >=П» (естественно, без кавычек).

3.  Щелкните на кнопке Далее формы.
В полях формы появятся данные строки списка, где указана, например, фамилия (буква «С» по алфавиту находится после буквы «П»).

4.  С помощью кнопки Далее формы продолжаете просматривать другие записи, где фамилии вкладчиков начинаются с буквы «П» и далее по алфавиту.
Если после щелчка на кнопке Далее номер записи в верхнем правом углу не меняется, значит, это последняя запись, удовлетворяющая критерию поиска. Щелкая на кнопке Назад, вы можете пролистать найденные записи в обратном порядке до первой записи, в которой фамилия клиента начинается с буквы «П» и далее в алфавитном порядке.

17.4.4. Поиск по множественному критерию

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

Замечание

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

Упражнение 1

Найти всех вкладчиков-женщин пенсионного возраста. Условие поиска: вкладчик - женского пола и не моложе 55 лет:

1.  Откройте файл «Вкладчики. xls».

2.  Вызовите форму для этого списка (см. п. 17.1. «Вызов формы»).

3.  Щелкните на кнопке Критерии.
При этом очистятся все поля формы, а в верхнем правом углу появится запись: «Критерии». Кроме того, изменятся названия некоторых кнопок формы.

4.  Перейдите в поле «Пол:» и введите условие «=Ж» или просто «Ж» (естественно, без кавычек).

5.  Перейдите в поле «Возраст, лет:» введите условие «>=55», тоже без кавычек.

6.  Щелкните на кнопке Далее формы.
В полях формы появятся данные строки списка, где вкладчиком является женщина старше 55 лет.

7.  Продолжаете щелкать на кнопке Далее формы для просмотра данных всех вкладчиков женского пола пенсионного возраста.
При этом в верхнем правом углу будет отображаться номер очередной записи.
Если после щелчка на кнопке Далее номер записи в верхнем правом углу не меняется, значит, это последняя запись, удовлетворяющая критерию поиска. Щелкая на кнопке Назад, вы можете пролистать найденные записи в обратном порядке до первой записи, в которой вкладчиком является женщина старше 55 лет.

Упражнение 2

Найти всех клиентов Южного отделения с размером вклада не болеерублей. Условие поиска: клиент имеет счет в Южном отделении банка, и на его счету находится не более 50 000 рублей:

1.  Щелкните на кнопке Критерии.
При этом очистятся все поля формы, а в верхнем правом углу появится запись: «Критерии». Кроме того, изменятся названия некоторых кнопок формы.

2.  Перейдите в поле «Размер вклада в руб.:» и введите условие «<=50000» (естественно, без кавычек).

3.  Перейдите в поле «Отделение банка:», введите условие «Южное» или «= Южное», тоже без кавычек.

4.  Щелкните на кнопке Далее формы.
В полях формы появятся данные строки списка, где указан вкладчик, имеющий вклад не более 50 000 рублей в Южном отделении банка.

5.  Продолжаете щелкать на кнопке Далее формы для просмотра данных всех вкладчиков, Южного отделения банка, размер вклада которых не превышает 50 000 рублей.
При этом в верхнем правом углу будет отображаться номер очередной записи. Если после щелчка на кнопке Далее, номер записи в верхнем правом углу не меняется, значит, это последняя запись, удовлетворяющая критерию поиска.
Щелкая на кнопку Назад, вы можете пролистать найденные записи в обратном порядке до первой записи, в которой клиент имеет вклад в Южном отделении банка на сумму не выше 50 000рублей.

17.5. Удаление записи из списка

1.  Вызовите форму для списка.

2.  Найдите требуемую запись по критерию (см. п. 17.4. «Поиск записей в списке»).

3.  Щелкните на кнопке Удалить в окне формы.

4.  Подтвердите удаление, щелкнув на кнопке ОК в появившемся диалоговом окошке.

Замечание

Удаленные записи не могут быть восстановлены!

17.6. Редактирование записей с помощью формы данных

1.  Вызовите форму для списка.

2.  Найдите требуемую запись по критерию (см. п. 17.4. «Поиск записей в списке»).

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

4.  Щелкните на кнопке Далее формы.
Форма покажет следующую запись списка, удовлетворяющую заданному критерию.

5.  Если необходимо редактировать записи, удовлетворяющие другому критерию, то начните со второго шага.

6.  Закройте окно формы после внесения всех изменений.

Глава 18. Использование Автофильтра для поиска записей

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

Автофильтр можно включить только для одного списка на листе Excel. Если на одном листе имеется два списка и более, то сначала надо отключить уже имеющийся Автофильтр, а затем установить его для другого списка.

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

Включить Автофильтр можно двумя способами: через меню Данные и с помощью правой кнопки мыши. Фильтрацию данных списка можно проводить при любом способе выбора Автофильтра, и только при втором способе можно быстро подвести итоги по столбцам списка (см. главу 19 «Использование Автофильтра для подведения итогов по столбцам списка).

В этой главе рассмотрим первый способ включения Автофильтра:

1.  Установите курсор в ячейку внутри списка или выделите весь список.

2.  В меню Данные наведите курсор на команду Фильтр 4.

3.  В дополнительном списке выполните команду Автофильтр.
В результате поля в строке заголовка списка превратятся в раскрывающиеся списки (рис. 104).

 

Чтобы отфильтровать записи списка:

1.  Включите Автофильтр для этого списка.

2.  Раскройте список того поля, которое войдет в критерий поиска.

3.  Выберите из списка требуемый критерий из предложенных вариантов:

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

4.  Задайте параметры в диалоговых окнах «Наложение условия по поиску» или «Пользовательский автофильтр», если вы выбрали соответствующие команды (см. упражнения ниже).

5.  Вернитесь ко второму шагу, если необходимо включить в критерий другое поле.

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

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

Упражнение 1

Найти 5 записей с наименьшими вкладами:

1.  Откройте файл со списком вкладчиков «Вкладчики. xls».

2.  Включите Автофильтр для этого списка.

3.  Откройте список для поля «Размер вклада, в руб.».

4.  Выберите в списке строку «(Первые 10)».

5.  В диалоге «Наложение условия по списку» укажите количество записей (5) с помощью счетчика и выберите из первого списка строку с текстом «наименьших» (рисунок I Таблицы 18).

6.  Щелкните на кнопке ОК диалога.
Результат показан на рисунке II Таблицы 17.

7.  Восстановите все записи списка, выбрав команду (Все) в списке поля «Размер вклада, в руб.».

Упражнение 2

Показать 20% вкладчиков от общего числа с наибольшими вкладами:

1.  Откройте список для поля «Размер вклада, в руб.».

2.  Выберите в списке строку «(Первые 10)».

3.  В диалоге «Наложение условия по списку» укажите количество 20 (процентов) с помощью счетчика, выберите из первого списка строку с текстом «наибольших», а из второго списка - «% от количества элементов» (рисунок I Таблицы 19).

4.  Щелкните на кнопке ОК диалога.
Результат показан на рисунке II Таблицы 19.

5.  Восстановите все записи списка, выбрав команду (Все) в списке поля «Размер вклада, в руб.».

Таблица 18 Результат фильтрации по критерию «(Первые 10)»

I

II

Таблица 19 Результат фильтрации по критерию «(Первые 10)» в процентах от общего числа записей

I

II

Упражнение 3

Найти всех вкладчиков по Западному отделению банка. Условие фильтрации - точное совпадение поля «Отделение банка» с названием «Западное»:

1.  Откройте список для поля «Отделение банка».

2.  Выберите в выпавшем списке строку с названием «Западное».
Результатом фильтрации будут всего две строки списка: с номерами 4 и 5.

3.  Восстановите все записи списка, выбрав команду (Все) в списке поля «Отделение банка».

Упражнение 4

Найти всех вкладчиков банка, которые имеют депозиты по Центральному отделению. Условие фильтрации включает два поля: «Тип вклада» и «Отделение банка». Фильтрация по точному совпадению значений двух полей.

1.  Откройте список для поля «Отделение банка».

2.  Выберите в выпавшем списке строку с названием «Центральное».
Результатом фильтрации будут пять строк списка: с номерами с 15 по 19.

3.  Откройте список для поля «Тип вклада».

4.  Выберите в выпавшем списке строку с названием «Депозит».
В результате на экране останутся только три записи: с номерами 15, 16 и 17. Две записи, в которых были вклады «Текущий», будут скрыты.

5.  Восстановите все записи списка, выбрав строку «(Все)» в списке полей «Отделение банка» и «Тип вклада» (порядок не имеет значения).

Упражнение 5

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

1.  Откройте список для поля «Размер вклада, в руб.».

2.  Выберите в списке строку «(Условие …)».

3.  В диалоге «Пользовательский список» выберите из списков операции сравнения и значения поля, установив переключатель в позицию «И» (рисунок I Таблицы 20).

4.  Щелкните на кнопке ОК диалога (результат показан на рисунке II Таблицы 20).

5.  Восстановите все записи списка, выбрав строку «(Все)» в списке поля, по которому проводилась фильтрация.

Таблица 20 Фильтрация проводится по одному полю, на которое накладывается два условия с использованием логической функции И

I

II

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

Глава 19. Использование Автофильтра для подведения итогов по столбцам списка

В предыдущей главе вы включали Автофильтр через меню Данные. В Excel 2003 существует еще один способ включения этого режима с помощью правой кнопки мыши:

1.  Щелкните правой кнопкой мыши внутри списка или на выделенном списке.

2.  В контекстном меню выполните команду Создать список…. .

3.  Щелкните на кнопке ОК диалога «Создание списка».
В результате поля в строке заголовка списка превратятся в раскрывающиеся списки, как на рис. 105. В отличие от первого способа включения Автофильтра, появляется дополнительная строка вставки:

a)  со знаком * в первой ячейке,

b)  маркером в нижнем правом уголке последней ячейки, с помощью которого
можно изменять область списка для получения итогов (см. п. 19.1. «Как получить итоги по столбцам списка»),

c)  весь список вместе со строкой вставки обводится синей рамкой.

В строке вставки можно вводить новые данные, при этом синяя рамка расширяется, охватывая новые данные.
В окне Excel может появиться панель инструментов «Список», в меню Данные автоматически появится флажок слева от команды Автофильтр.

 

19.1. Как получить итоги по столбцам списка

1.  Включите Автофильтр с помощью правой кнопки мыши.

2.  Щелкните правой кнопкой мыши на любой ячейке внутри списка.

3.  В контекстном меню наведите курсор мыши на команду Список 4.

4.  Выполните в появившемся списке команду Строка итогов.
Внизу последнего столбца появится итог, в данном примере это - сумма значений ячеек этого столбца, как на рисунке Таблицы 21. Если бы столбец содержал текстовые данные, то в качестве итога было бы получено количество непустых элементов в этом столбце.

5.  Щелкните на ячейке с итоговым значением.
Справа от нее появится значок раскрывающегося списка, в котором можно выбрать другие способы подведения итогов, например: Среднее (рисунок I Таблицы 22).
Теперь в итоговой ячейке находится число, показывающее средний возраст вкладчиков (рисунок II Таблицы 22).

Таблица 21 По умолчанию итог в виде суммы появился внизу последнего столбца, т. к. маркер находился в последней ячейке строки итогов

6.  Щелкните в ячейке под столбцом «Размер вклада в руб.», выберите из списка строку «Сумма» (рисунок I Таблицы 23). В итоговой ячейке находится сумма всех вкладов (рисунок II Таблицы 23).

Таблица 22 С помощью списка можно изменить итоговое значение – например, выбрав строку Среднее

I

II

Таблица 23 Получение итогов четвертого столбца списка в виде суммарного вклада всех клиентов

I

II

В Таблице 24 в итоговой ячейке второго столбца списка было выбрано значение «Количество» (поскольку в этом столбце находятся текстовые данные, то применять к ним статистические функции не имеет особого смысла). Число 21 – это количество вкладчиков (записей в списке). В итоговой ячейке третьего столбца было выбрано «Количество чисел», так как в этом столбце чисел нет, то получили – ноль. В шестом столбце в качестве итогового значения было выбрано минимальное значение, это год рождения самого старого клиента банка.