Упражнение 1. Копирование листов в другой файл (рабочую книгу).

Луганск

ТАБЛИЧНЫЙ ПРОЦЕССОР MS EXCEL.
УПРАВЛЕНИЕ ДАННЫМИ

Введение

Система Excel является не только электронной таблицей, но и своего рода системой управления базами данных (СУБД).

Чем отличается база данных от электронной таблицы?
База данных – это совокупность данных, обладающая некоторой структурированностью и взаимосвязанностью. Именно благодаря этим свойствам базы данных позволяют хранить информацию о подобных объектах реального мира и моделировать связи между ними. В то же время, электронная таблица может не иметь никакой структуры, а связи между данными электронной таблицы могут отсутствовать. Однако, если данные на рабочем листе будут упорядочены определенным образом, можно говорить о базе данных. Принцип упорядочения в базу данных, принятый в Excel, – это упорядочение в виде списка. Работа со списками требует, прежде всего, навыков так организовывать свои рабочие книги, чтобы данные в них обладали структурированностью и взаимосвязанностью; затем умения сортировать и обрабатывать списки; составлять отчеты и строить сводные таблицы, а также автоматизировать рутинные операции с помощью встроенного языка программирования Microsoft Visual Basic[1] для приложений. Именно приобретению этих навыков посвящены задания вычислительной практики.

Изучив теоретические материалы и выполнив задания, студент-практикант должен:

Знать: возможности современных информационных технологий для решения различных задач учета и анализа в экономике и бизнесе.

Уметь: использовать технологии и средства обрабоки и анализа данных в среде MS Excel, разрабатывать или совершенствовать программное и информационное обеспечение автоматизированного решения задач учета, планирования и анализа экономических процессов.

Основное внимание во время вычислительной практики уделяется приобретению практических навыков работы в среде Microsoft Excel.

Задание 1. Организация рабочих книг

Вы научитесь:

· Копировать и перемещать листы рабочей книги;

· Добавлять примечания в ячейки, текстовые поля и комментарии к вашей рабочей книге;

· Осуществлять быстрый поиск файлов по заметкам и ключевым словам

Начало работы.

1. Создать в папке Мои документыпапку, которой присвоить имя, используя вашу фамилию. Создать в вашей папке первый документ MS Excel и присвоить ему имя Оргтехника, затем открыть его в MS Excel и присвоить листу Лист1 имя Факсы;

2. Ввести данные, изображенные на рис. 1.1.

Рис. 1.1. Факсы

Порядок ввода следующий: введите сначала данные столбцов Модель, Название, Стоимость и Количество, а значения в столбцах Цена и Сумма получите с помощью формул:

Цена = Стоимость*1,3 и Сумма = Количество*Цена.

3. Примените к ячейкам столбцов С, D, и F денежный формат.

4. Создать в папке второй документ Excel и присвоить ему имя Задание1; затем открыть его в Excel и присвоить листу Лист1 имя Ксероксы ;

5. Ввести данные, изображенные на рис. 1.2, в том же порядке, что и данные в книге Оргтехника на листе Факсы. Примените к ячейкам столбцов С, D, и F денежный формат.

Рис. 1.2. Ксероксы

Управление рабочими книгами

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

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

Х 00 Базовая модель

Рис. 1.7 Изменение текстового поля

Заполните таблицу
(заполненная таблица помещается в отчет
по вычислительной практике)

Задание 2. Сортировка и обработка списков

Вы научитесь:

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

· быстро вводить данные с помощью функций Автоввод и Выбор из списка;

· сортировать данные по определенным критериям.

При работе с MS Excel вам наверняка придется сортировать и обрабатывать списки данных. На приведенной ниже иллюстрации (рис.2.1) данные хранятся в виде списка, заголовки столбцов которого определяют поля, а строки содержат записи. В каждом поле содержится информация определенного типа, например фамилии, имена и т.д., а запись состоит из описания элемента списка. В списке иллюстрации каждая запись содержит сведения о названии товара, код модели, стоимость, ежемесячный и квартальный сбыт, а также полный приход от продажи этого вида оргтехники. Совокупность данных в виде таблицы полей и записей называется спискомили базой данных. В MS Excel понятия список и база данных взаимозаменяемы.

При работе со списками (базами данных) часто приходится искать определенную информацию, необходимую в данный момент, а также сортировать данные по определенному признаку. Например, если требуется составить отчет о количестве сотрудников административной службы, то из общего списка сотрудников следует выбрать только административных работников. MS Excel имеет два мощных инструмента для поиска и вывода на экран специфических данных из общего списка — это фильтрация и сортировка. С их помощью легко выбирать нужные данные на рабочем листе. В этом уроке вы научитесь пользоваться Автофильтром и сортировать данные по различным признакам, называемым ключами.

Начало работы

Откройте в папке Мои документы свою папку и откройте файл с именем Задание1, а затем сохраните его под именем Задание2.

Фильтрация списков

При работе с базами данных важно иметь возможность быстро находить нужную информацию. Допустим, у вас имеется список телефонных номеров или таблица ассортимента товаров, а вам требуется выбрать номера одной АТС или все коды моделей факсов. Конечно же, вам не захочется просматривать весь список и выписывать необходимую информацию. С помощью имен в полях вашего списка вы можете автоматически отфильтровать и вывести на экран только нужные вам данные. Включить режим автоматической фильтрации (Автофильтр) тможно, выделив некоторую ячейку списка, а затем щелкнуть на кнопке Фильтрв группеСортировка и фильтрвкладки Данные. Когда выделена одна из ячеек списка на рабочем листе, MS Excel выполняет фильтрацию всего списка в целом. Далее с помощью имен полей следует определить условия, по которым будет выполняться фильтрация списка. Выбрав необходимые данные, вы можете отключить Автофильтр, щелкнуть на той же кнопке Фильтрв группеСортировка и фильтрвкладки Данные.

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

Сортировка данных

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

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

Задание 3. Составление отчетов

Вы научитесь:

· получать промежуточные результаты данных;

· структурировать данные итогового отчета;

· консолидировать несколько рабочих листов с однотипными данными в итоговый отчет.

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

Начало работы

Откройте в папке Мои документы свою папку и выберите файл с именем Задание2.xls, а затем сохраните его под новым именем Задание3.xls. Для удобства работы с таблицей измените формат столбцов Стоимость, Цена и Сумма с денежного на числовой без десятичных знаков на всех листах.

Промежуточные итоги

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

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

Упражнение 1. Укажите значение суммарной стоимости для каждого типа ксерокса

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

1. Щелкните на ярлычке листа Ксероксыи выделите ячейку A6.

2. В группе Структура вкладки Данные щелкните на кнопке Промежуточные итоги. Откроется диалоговое окно Промежуточные итоги(рис 3.1).

3. Из раскрывающегося списка При каждом изменении в выберите строку Название. Так вы подведете итог по каждому из названий ксероксов.

4. Убедитесь, что в поле Операция находится слово Сумма.

Рис. 3.1 Диалоговое окно Промежуточные итоги

5. Убедитесь, что в списке Добавить итоги по флажок установлен только напротив строки Сумма. Для этого, если потребуется, прокрутите весь список.

6. Проследите, чтобы были установлены флажки напротив строк Заменить текущие итоги и Итоги под данными, и щелкните на кнопке ОК. Таким образом, вы получите итоговые значения суммарной стоимости каждого вида ксерокса (рис. 3.2).

Если вы, установив параметры в диалоговом окне Промежуточные итоги, вдруг передумали использовать эту установку, щелкните на кнопке Убрать все. Ваши данные вернутся к прежнему состоянию.

Рис.3.7 Консолидированные данные

Контрольные вопросы

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

2. Как удалить промежуточные итоги?

3. Что такое консолидация данных?

4. Можно ли расположить итоговый отчет на одном листе с исходными данными?

5.Как добавить вложенные итоги?

Заполните таблицу
(заполненная таблица помещается в отчет
по вычислительной практике)

Краткое изложение задания 3

Чтобы	Необходимо
Добавить промежуточные итоги
Убрать промежуточные итоги
Добавить вложенные итоги
Скрыть детали структуры
Показать детали структуры
Структурировать рабочий лист, содержащий формулы
Отменить структурирование
Провести консолидацию данных нескольких рабочих листов

Задание 4. Анализ данных с помощью сводных таблиц

Вы научитесь:

· подводить итоги с помощью сводной таблицы;

· изменять структуру данных без перестройки рабочего листа;

· придавать документам профессиональный вид с помощью форматирования;

· строить диаграммы сводных таблиц.

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

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

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

Начало работы

Откройте в папке Мои документы свою папку и выберите файл с именем Задание3.xls, а затем сохраните его под новым именем Задание4.xls.

Создание сводной таблицы

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

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

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

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

Изменение сводной таблицы

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

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

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

Добавление данных

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

Удаление общих итогов

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

Задание 5. Автоматизация выполнения повторяющихся задач

Вы научитесь:

· создавать макросы для автоматизации повторяющихся операций;

· вносить комментарии, поясняющие действия макросов;

· создавать кнопки для быстрого запуска макросов.

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

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

Начало работы

Откройте в папке Мои документы свою папку и выберите файл с именем Задание2.xls, а затем сохраните его под новым именем Задание5.xls.

Запись макроса

Процесс записи макроса состоит из трех шагов:

1. Активизация процесса записи.

2. Выполнение действий, которые составляют суть макроса.

3. Остановка процесса записи.

Процесс записи макроса рассмотрим на примере.

На листе Полный ассортиментнаходится таблицаИнформация о товарах(рис. 2.1). Предположим, что фирма «Запад» в связи с колебанием курса доллара каждый день обновляет стоимость товаров, их количество и соответственно изменяет сумму. Поэтому в процессе работы, перед очередным обновлением ассортимента поле Дата и столбцы Стоимость, Количество и Сумма нужно очистить. Запишем макрос, обеспечивающий выполнение этой работы.

Начало записи макроса

1. Чтобы начать запись макроса, нужно на вкладке Вид ленты инструментов в группе щелкнуть на раскрывающейся кнопке Макросы. В открывшемся меню выбрать команду Запись макроса. На экране появится одноименное диалоговое окно (рис. 5.1).

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

3. В поле Описаниенужно ввести краткое описание макроса.

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

Макрос может быть сохранен в личной книге макросов или в текущей книге. По умолчанию записываемый макрос будет сохранен в личной книге макросов. Личная книга макросов (файл personal.xls) — это специальная книга, которая автоматически загружается при запуске Excel, что обеспечивает возможность запуска макросов, находящихся в этой книге, во время работы с другими книгами. В личную книгу макросов обычно помещают универсальные макросы. По умолчанию личная книга макросов скрыта от пользователя.

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

5. После ввода имени макроса, его описания и выбора книги, в которую будет помещен макрос, нужно щелкнуть на кнопке ОК.

В результате будет активизирован процесс записи макроса и с этого момента Excel запоминает все действия пользователя.

Рис. 5.1 Окно Запись макроса

Процесс записи макроса

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

Ниже перечислены действия, которые нужно выполнить в процессе записи макроса Обновление_ассортимента (см. рис. 5.1):

1. Выделить ячейку B3, нажать клавишу <Del>.

2. Выделить диапазон D7:D27, нажать клавишу <Del>.

3. Выделить диапазон F7:F27, нажать клавишу <Del>.

4. Выделить диапазон G7:G27, нажать клавишу <Del>.

5. Щелкнуть в ячейке B3.

Запуск макроса

Для того чтобы запустить макрос, нужно:

1. Из списка кнопки Макросы выбрать команду Макросы.

2. В появившемся диалоговом окне Макросвыбрать имя макроса, который нужно запустить.

3. Щелкнуть на кнопке Выполнить.

Контрольные вопросы

1. Ограничения в записи имени макроса.

2. В какую книгу макрос записывается по умолчанию? Как сделать ее доступной?

3. Как остановить процесс записи макроса?

4. Как изменить текст записанного макроса?

5. Создание командной кнопки.

6. Как изменить значок на командной кнопке?

7. Какой язык программирования используют для записи макросов?

8. Как запустить макрос на выполнение?

Заполните таблицу
(заполненная таблица помещается в отчет
по вычислительной практике)

Краткое изложение задание 5

Чтобы	Необходимо
Из каких шагов состоит процесс записи макроса
Начать запись макроса
Остановить процесс записи макроса
Запустить макрос

Задания 1–5 необходимо выполнить и сдать руководителю практики до 31.01.15!!!

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ

Задание 7. Использование расширенного фильтра для управления данными

Внимание! Номер варианта задания студент узнает у руководителя практики. Задания 7 необходимо выполнить и сдать руководителю практики до 06.02.15!!!

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

Расширенный фильтр предназначен для фильтрации списков тогда, когда возможностей автофильтра недостаточно. Хотя расширенный фильтр и несколько сложнее автофильтра, он имеет ряд преимуществ. Основные из них:

1) можно сохранять критерий отбора данных для дальнейшего использования;

2) для одного столбца можно задать более двух критериев отбора;

3) между столбцами можно задать несколько критериев сравнения;

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

5) в критерии можно включать формулы, т.е. позволяет создавать вычисляемые критерии;

6) копию полученной в результате фильтрации выборки можно помещать в другое место рабочего листа.

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

1. Проверить, чтобы столбцы списка имели заголовки.

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

Рис. 1. Список и таблица критериев.

При создании таблицы-критерия необходимо помнить о следующих соглашениях:

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

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

3) для истинности критерия, состоящего из условий, располагающихся в разных строках, требуется выполнение хотя бы одного из них, т.е. считается, что они соединены логической операцией ИЛИ;

4) в таблице-критерии не должно быть пустых строк.

Чтобы применить расширенный фильтр, нужно выполнить следующие действия.

1. Выделить диапазон фильтрации и вызвать команду Данные → Сортировка и фильтр → Дополнительно.

2. В открывшемся диалоговом окне Расширенный фильтр (рис. 2) задать параметры расширенного фильтра:

а) в поле Исходный диапазон нужно ввести диапазон фильтруемой таблицы (программа обычно сама правильно устанавливает исходный диапазон);

б) в поле Диапазон условий необходимо задать диапазон таблицы-критерия (в данном случае $А$18:$G$19).

Рис. 2. Диалоговое окно Расширенный фильтр.

в) результат фильтрации по умолчанию будет размещен на месте исходной таблицы. Если вы хотите, чтобы результат был помещен в другом месте листа или Рабочей книги, щелкните по переключателю Скопировать результат в другое место. Затем в поле Поместить результат в диапазон введите диапазон вставки отфильтрованной таблицы.

г) если вы желаете, чтобы в результирующей таблице не появлялись повторяющиеся записи, щелкните по флажку Только уникальные записи.

3) Закройте диалоговое окно Расширенный фильтр щелчком по кнопке ОК.

Если вы правильно указали диапазон и критерии фильтрации, то на листе появится результирующая таблица (рис. 3).

Рис. 3. Результирующая таблица.

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

Создание вычисляемых критериев при работе с Расширенным фильтром

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

Вычисляемый критерий представляет собой формулу, в которой обязательно имеется ссылка (для реализации каких-либо вычислений) на соответствующую ячейку первой записи списка. Так как эта формула является логическим выражением, то в ячейке, ее содержащей, отображается результат вычисления (ИСТИНА или ЛОЖЬ) для первой записи списка. А в результате процесса фильтрации в списке будут скрыты те записи, для которых при вычислении формулы получается значение ЛОЖЬ.

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

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

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

3) ссылки на ячейки вне списка должны быть абсолютными.

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

Использование текстовых функций при формировании вычисляемых критериев

Текстовые функции дают возможность выполнять самые разнообразные преобразования текстовых данных. К наиболее часто используемым из них относятся следующие:

ЗНАЧЕН(текст) – преобразует числовые данные, введенные на рабочем листе в текстовом формате (т.е. заключенные в кавычки), в числовые значения.

ТЕКСТ(значение; формат) – преобразует число в текст по заданному формату.

ДЛСТР(текст) – возвращает длину строки – количество символов в параметре текст, включая пробелы между словами.

ПРАВСИМВ(текст; колич_симв) – извлекает заданное количество колич_симв последних (самых правых) символов из текстовой строки текст.

ЛЕВСИМВ(текст; колич_симв) – извлекает заданное количество колич_симв первых (самых левых) символов из текстовой строки текст.

ПСТР(текст; нач_позиция; колич_симв) – извлекает из исходной строки текст, начиная с указанной позиции нач_позиция, подстроку заданной длины колич_симв.

СЖПРОБЕЛЫ(текст) – удаляет из текста все начальные и хвостовые пробелы, из внутренних же удаляются все пробелы, за исключением одиночных.

НАЙТИ(иск_текст; просм_текст; нач_позиция)

и ПОИСК(иск_текст; просм_текст; нач_позиция) – осуществляют поиск внутри строки некоторого образца, т.е. заданной подстроки, и возвращают порядковый номер символа строки, с которого начинается найденный образец. При подсчете всегда учитываются все символы, включая пробелы и знаки препинания. Обе функции выполняют сходные действия, различие заключается в том, что функция НАЙТИ() учитывает регистр, а функция ПОИСК() допускает символы шаблона.

ЗАМЕНИТЬ(старый_текст; нач_позиция; колич_симв; новый_текст) – заменяет заданный фрагмент текста старый_текст другой строкой новый_текст.

СЦЕПИТЬ(текст1; текст2; …) – соединяет несколько строк в одну (конкатенация).

Пусть, например, необходимо из списка студентов (рис. 4), расположенного на Лист1, выбрать студентов, обучающихся на специальности «Финансы» экономического факультета (специальность указана в названии группы – три первые символа). Критерий для реализации такого запроса разместим на отдельном листе рабочей книги (рис. 5). В ячейку В3 таблицы критериев поместим формулу =ЛЕВСИМВ(Лист1!F5;3)="Фин". Результаты фильтрации показаны на рис. 6.

Рис. 4

Рис. 5.

Рис. 6

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

Рассмотрим основные функции, предназначенные для работы с датами и временем:

ДАТА(год; месяц; день) – возвращает дату, заданную параметрами, в числовом формате.

СЕГОДНЯ() – возвращает числовое значение текущей даты. В нашем списке эта функция используется при вычислении возраста студентов (табл. 1).

ДЕНЬНЕД(дата; тип) – вычисляет порядковый номер дня недели (от 1 до 7), соответствующего заданной дате. Необязательный второй аргумент тип дает возможность выбрать желаемый порядок нумерации дней недели. Если этот аргумент равен 1 или отсутствует, то первым днем недели считается воскресенье, а последним - суббота. Если тип равен 2, то первым днем недели считается понедельник, а последним - воскресенье.

ГОД(дата) – возвращает значение года (от 1900 до 9999) для данной даты.

МЕСЯЦ(дата) – возвращает номер месяца (от 1 до 12) для данной даты.

ДЕНЬ(дата) – возвращает номер дня в месяце (от 1 до 31) для данной даты.

ДАТАЗНАЧ(дата_как_текст) – преобразует в числовой формат дату, заданную в текстовом формате.

ДНЕЙ360(нач_дата; кон_дата; метод) – вычисляет количество дней между двумя датами на основе 360-дневного года (12 месяцев по 30 дней).

Пусть, например, необходимо выдать список студентов, родившихся летом. Интервал критериев для реализации этого запроса приведен на рис. 7, а полученная выборка – на рис. 8.

Рис. 7

Рис. 8

Функции для анализа списков

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

СЧЁТЕСЛИ(интервал; критерий) – возвращает количество ячеек в интервале, которые удовлетворяют критерию.

Например, подсчитать число студенток в списке можно по формуле =СЧЁТЕСЛИ(F2:F26; “Ж”).

СУММЕСЛИ(интервал; критерий; интервал_суммирования) - возвращает сумму значений в ячейках из интервала суммирования, отфильтрованных в соответствии с критерием, применяемым к интервалу.

Каждая из оставшихся функций аналогична «обычной» статистической функции. Различие сводится к тому, что функции баз данных обрабатывают только те ячейки интервала, которые удовлетворяют заданным критериям. При этом их синтаксис одинаков: БДФУНКЦИЯ(база_данных; поле; критерий). Необходимо обратить внимание на правила обращения к функ<