Применение формы данных для поиска информации в списке

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

1. Выделить любую ячейку в списке.
2. Выбрать команду Форма из меню Данные и в открывшемся диалоговом окне щелкнуть кнопку Критерии
3. Заполнить поля ввода так же, как при создании интервала критериев.
4. Щелкнуть кнопку Следующая, чтобы вывести первую строку списка, удовлетворяющую заданным критериям.
5. Пролистать выделенные записи, используя кнопку Следующая, или кнопку предыдущая.

Копирование отфильтрованных строк в другое место рабочего листа

В диалоговом окне Расширенный фильтр можно задать режим копирования строк в другое место рабочего листа: установить переключатель скопировать результат в другое место и в поле ввода Поместить результат в диапазон: ввести имя или адрес интервала, в который нужно поместить результат фильтрации. Проще задать интервал для копирования – щелкнуть мышью начальную ячейку этого интервала. Убедитесь, что справа от этой ячейки и ниже её имеется достаточно свободного места. Заголовки столбцов и все записи списка, удовлетворяющие критериям расширенного фильтра, будут помещены в заданный интервал. Если интервал для копирования задать полностью, отфильтрованные строки будут копироваться только до заполнения всех ячеек интервала. Интервалу, задаваемому в поле Поместить результат в диапазон:, присваивается имя Извлечь, которое можно использовать для перехода внутри листа. Например, чтобы вернуться к этому интервалу для замены заголовков столбцов, нажмите клавишу F5 и выберите Извлечь из диалогового окна Переход.

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

1. Скопируйте заголовки столбцов Название административной единицы и Численность населения в интервал ячеек L6:M6. Заголовки можно копировать в любом порядке в зависимости от требуемого вида отфильтрованных данных.
2. Выберите команду Расширенный фильтр из подменю Фильтр меню Данные.
3. В открывшемся диалоговом окне Расширенный фильтр задайте ссылки на интервал фильтруемого списка и интервал критериев, установите переключатель скопировать результат в другое место, напечатайте L6:M6 в поле Поместить результат в диапазон: и щелкните ОК.

Флажок Только уникальные записи диалогового окна Расширенный фильтр действует только в режиме скопировать результат в другое место и устраняет дублирование строк. Пусть из списка «Мой край» нужно выделить районы в которых есть поселки городского типа, при этом каждый из районов должен присутствовать в списке один раз. Для формирования такого списка выполните следующие действия:

1. Создайте интервал критериев, который бы позволил найти все записи, относящиеся к поселкам городского типа, для этого скопируйте заголовок Тип поселения в ячейку A1, а в ячейку A2 введите пгт.

2. В другой пустой области рабочего листа, например в ячейку K4 введите заголовок Название района, в котором есть поселки городского типа. Ячейка с заголовком является началом интервала для копирования отфильтрованных строк.

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

4. В диалоговом окне Расширенный фильтр введите ссылки на интервалы списка и критериев. Установите переключатель скопировать результат в другое место, напечатайте K4 в поле Поместить результат в диапазон:, установите флажок
Только уникальные записи и щелкните ОК. Результат показан на рис.27.

Подведение итогов

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

Команда Итоги дает возможность подвести также общие итоги, т.е. применить выбранную функцию, например СУММА или СРЕДН, не только к группе записей в списке, но и ко всему списку. Более того, Вы можете задать, место размещения общих итогов в конце или в начале списка. Последнее удобно при длинном списке, так как не нужно искать общие итоги в его конце.

 

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

 

При рассмотрении команды Итоги будет использоваться список, приведенный на рис.28, в котором представлены названия, численность населения городов, районных центров и поселков городского типа Приморского края. Список отсортирован по значениям столбца Название района.

 

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

1. Выберите из меню Данные команду Итоги.

2.
Заполните диалоговое окно Промежуточные итоги в соответствии с рис.29.

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

В соответствии с заданными в диалоговом окне Промежуточные итоги параметрами Excel выполнит следующие действия:

· Создаст формулу подведения итогов для каждого изменения значений в столбце Название административной единицы.

· Построит формулу с функцией СУММА для столбца Численность населения (чтобы сложить значения в этом столбце по каждой группе)

· Поместит промежуточные итоги под каждой группой данных, а общие итоги – в конце списка.

Используемая при подведении итогов в области данных по умолчанию функция зависит от типа данных:

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

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

 

 

Список на рис.30 структурирован, этим можно воспользоваться, чтобы

· Вывести только общие итоги, щелкнув символ уровня строки 1, результат показан на рис.31 (кнопки 1, 2, 3 находятся в левом углу таблицы).

· Вывести общие и промежуточные итоги, щелкнув символ уровня строки 2, результат показан на рис.32.

· Вывести полный список, щелкнув символ уровня 3, результат показан на рис.30.

 

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

1. Щелкнуть символ уровня строки 2 для ввода только основных и промежуточных итогов.

2. Отсортировать этот «свёрнутый» по убыванию поля Численность населения.

После сортировки связь детальных и соответствующих итоговых строк сохраняется.

 

Формула в ячейке D11 на рис.33 может служить примером того, как Excel подводит итоги. В ней использована функция ПРОМЕЖУТОЧНЫЕ ИТОГИ(), в которой аргумент 9 задает тип вычислений. Не стоит применять собственные формулы подведения итогов, к интервалу, содержащему эту функцию.

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

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

 

Функции баз данных

Функции для работы с базами данных:

• БСЧЁТ - подсчитывает количество ячеек, содержащих числа;
• БСЧЁТА - подсчитывает количество непустых ячеек;
• ДМАКС - ищет максимальное значение;
• ДМИН - ищет минимальное значение;
• БДСУММ - вычисляет сумму числовых значений;
• БДПРОИЗВЕД - перемножает числовые значения;
• ДСРЗНАЧ - считает среднее значение;
• ДСТАНДОТКЛ - оценивает стандартное отклонение;
• ДСТАНДОТКЛП - вычисляет стандартное отклонение по генеральной совокупности;
• БДДИСП - оценивает дисперсию;
• БДДИСПП - вычисляет дисперсию по генеральной совокупности;
• БИЗВЛЕЧЬ - ищет одну запись (если критерию удовлетворяют несколько записей, возвращается ошибка #ЧИСЛО!).

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

=БДСУММ(A6:F75; "Численность населения, тыс.чел."; A1:A2)

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

Обратите внимание на правила обращения к функциям баз данных:

• Первый аргумент задает весь список, а не отдельный столбец.
• Второй аргумент задает столбец, элементы которого необходимо просуммировать. Усреднить и т.п.
• Вторым аргументом может быть заголовок столбца в виде текстовой константы или порядковый номер столбца в списке. Например, в формуле на рис.34 вторым аргументом могло бы быть число 4 потому, что столбец Численность населения – четвертый в списке.
• Третий аргумент задает интервал критериев. В функциях баз данных могут использоваться любые допустимые критерии.