Информационные системы и технологии в финансах (стр. 18 из 21)

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

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

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

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

Для удаления всех автофильтров и их кнопок необходимо убрать галочку рядом с командой Автофильтр. Для этого:

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

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

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

Кол-во	Цена-у.е.
>=4
>=850

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

Примеры условий отбора расширенного фильтра

Наименование

Epson*

Canon*

Mustek*

Пример 1. Для следующего диапазона условий будут отобраны строки, содержащие наименование оргтехники, начинающиеся с Epson, Canon, Mustek.

Пример 2.Для следующего диапазона условий будут отобраны строки, содержащие Ксерокс в столбце Категория, Canon * в столбце Наименование и имеющие цену больше 300 $.

Категория	Наименование	Цена-у.е.
Ксерокс	Canon*	>300

Пример 3.Для следующего диапазона условий будут отобраны строки, удовлетворяющие одному из нескольких условий, наложенных на разные столбцы, содержащие либо Факс * в столбце Наименование, либо Туби в столбце Диллер, либо имеющие цену меньше 100.

Наименование	Диллер	Цена-у.е.
Факс*
Туби
<100

Пример 4.Для следующего диапазона условий будут отобраны строки, содержащие Сканер в столбце Категория, Mustek* в столбце Наименование, Туби в столбце Диллер и имеющие цену меньше 120 $. А также строки, содержащие C* в столбце Наименование и имеющие цену меньше 300 $.

Категория	Наименование	Диллер	Цена
Сканер	Mustek *	Туби	<120
C*	<300

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

Пример 5. Например, для следующего диапазона условий будут

Вывести: Цены выше среднего

=Е4>СРЗНАЧ($E$4:$E$24)

отображены строки, в которых цена оргтехники выше среднего.

Используемая в условии формула должна ссылаться либо

на заголовок столбца, либо на соответствующее поле в первой записи. В приведенном примере G5 ссылается на соответствующее поле (столбец G) первой записи (строка 5) списка.

При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона в ячейке будет выведено значение ошибки #ИМЯ? Или #ЗНАЧ. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

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

Задания для самостоятельной работы:

По предлагаемой таблице выполнить следующие задачи фильтрации:

1. Вывести на экран записи таблицы с датой размещения от 1 февраля 1997 г. по 1 марта 1997 г. и скопировать их на лист 2.

2. Вывести на экран заказы, отправленные в Берлин.

3. Вывести на экран заказы Инны Ясеневой, размещенные в 1996 году.

4. С помощью автофильтра отобразить все заказы, доставленные Ространсом в период с 1 апреля 1996 по 1 февраля 1997 г.

5. С помощью автофильтра отобразить все заказы, доставленные Почтой в Польшу.

6. С помощью автофильтра отобразить все заказы сотрудника Дарьи Вороновой, доставленные Ространсом в Германию.

7. С помощью автофильтра отобразить все заказы сотрудника Марии Беловой за период с 1 марта 1996года по 1 июля 1997 года.

8. С помощью автофильтра отобразить все заказы, пришедшие в Буэнос-

Айрес.

9. Отобразить все заказы, стоимость доставки которых превышает 4

миллиона.

10. Вывести на экран записи таблицы с датой размещения от 1 декабря 1996 г. по 1 декабря 1998 г. и скопировать их на лист 2.

11. С помощью автофильтра отобразить все заказы сотрудника Андрея Кротова, доставленные в США или Францию в 1998 году.

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

1. Порядок использования Расширенного фильтра.

2. Формулы в расширенном фильтре.

3. Применение автофильтра к нескольким столбцам

4. Как используется автофильтр для нахождения первых десяти элементов?

5. Как применяется автофильтр для задания более сложных условий отбора?

6. Использование символов шаблона в пользовательском фильтре?

7. Отмена результатов фильтрации?

Практическая работа № 14. Создание макросов

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

План занятия:

1. Создать исходную таблицу и диаграмму на листе Excel.

2. Поместить на лист элементы управления – кнопки. 3. Создать обработчики событий нажатия для кнопок.

Порядок выполнения работы

В работе мы построим график функции y( x) xsin( ax) . Введите в ячейки А1:А500 значения для х с помощью автозаполнения. Значения х будут меняться от 0 с шагом 0,1. Для этого введите в А1 значение 0, в А2 – 0,1. Далее выделите эти ячейки и перетащите маркер автозаполнения к ячейке А500.

2. В ячейку С1 занесите значение параметра "а" – 15.

3. Теперь заполним столбец для у. Введите в ячейку В1 формулу, указанную выше (х берем из столбца А; параметр а – из ячейки С1; функцию синуса – из мастера формул). Установите абсолютную адресацию таким образом, чтобы формула правильно переносилась с помощью маркера автозаполнения. Переместите формулу таким образом, чтобы она заполнила значениями ячейки В1:В500.

4. Создайте на этом же листе график этой функции. Тип диаграммы

– точечная. Вид и цвет точек выберите на свой вкус. Диапазон для шкалы по оси х – (0-60), по оси у – (-60 -+60)..

5. Теперь поместим на лист кнопки для изменения значений параметра. Для этого выведите на экран панель инструментов "Элементы управления". Далее установите на листе 2 кнопки – одну под другой, рядом с диаграммой.

6. Введем названия для кнопок. Щелкните правой кнопкой мыши на ней и выберите пункт "Объект кнопка – Изменить" и введите новое название кнопки : "Увеличить". Другую кнопку назовите "Уменьшить".

7. Напишем макрокоманду, которая будет действовать при нажатии кнопки. Щелкните на кнопке "Увеличить" 2 раза. Откроется окно набора текста макроса. В этом окне введите (обязательно английскими буквами) текст "Cells(1, 3).Value = Cells(1, 3).Value + 0.1". Выполнение этой команды приведет к увеличению значения в ячейке с координатами (1,3) – (это С1) на 0,1. Закройте окно ввода текста макроса и создайте макрос для кнопки "уменьшить", соответствующим образом изменив текст предыдущего макроса.

8. Нажимая созданные кнопки, наблюдайте за изменением внешнего вида графика и значения параметра в ячейке С1. Значение в С1 меняйте от 10 до 20. Покажите результат преподавателю.

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

1. Использование абсолютной адресации ячеек.

2. Типы элементов управления, размещаемые на листах Excel.

3. Настройка элементов управления. 4. Адресация ячеек из макроса excel.

Практическое занятие № 15-16.

Расчет заработной платы

Цель: научиться проводить вариантные расчеты в Еxcel, с использованием функции "ЕСЛИ" в сложных случаях с использованием вложенных формул и элементов управления листа на примере расчета заработной платы; получить представление о создании пользовательских функций с использованием втсроенного языка Visual Basic. План:

1. Изучить порядок начисления зарплаты и создать на листе исходную таблицу.

2. Ввести формулы для расчета зарплаты двумя способами.