информатика. Игнатьева Елена Александровна, Измайлова Елена Ивановна. Информатика. Электронный ресурс методические указания
 Скачать 4.32 Mb.
|
|
Пример 1 . Условие отбора накладывается на содержимое двух и более столбцов списка. Пусть необходимо из списка вы- делить записи, которые одновременно содержат в поле "Долж- ность" значение "Инженер-программист", в поле "ФИО сотруд- ника" – "Сидоров М.А.", а в поле "Дата получения премии" – все даты позже 15.02.07. В этом случае диапазон критериев будет выглядеть так, как изображено на рис. 2. Необходимо обратить внимание на следующее: 1. Диапазон критериев начинается с 55 строки (отделен от списка рис.1 одной пустой строкой). 2. Все условия отбора размещены в одной строке. Тем са- мым задается их взаимодействие по схеме "И" (требуется одно- временное их выполнение). 109 Рис. 2. Диапазон критериев при взаимодействии по схеме "И" Для соединения условий отбора по схеме "ИЛИ" необходи- мо каждое из них разместить в отдельной строке (рис. 3). Рис. 3. Диапазон критериев при взаимодействии по схеме "ИЛИ" При задании оформленного таким образом диапазона кри- териев из списка будут выбраны записи, содержащие либо зна- чение "Сидоров Л.А." в столбце "ФИО сотрудника", либо "Ин- женер-программист" в столбце "Должность", либо имеющие зна- чение ">15.02.97" в столбце "Дата получения премии". Так зада- ются разные условия отбора на несколько полей списка одно- временно (рис .3). Для задания сложного условия отбора необходимо вводить его составные части в отдельные строки диапазона критериев. Пример 2. Три и более условий отбора накладываются на содержимое одного столбца списка. Диапазон критериев в этом случае оформляется в виде столбца, в котором в смежных ячейках записаны условия отбора (рис. 4) А С F 55 ФИО сотрудника Должность Дата получения премии 56 Сидоров Л.А. Инженер- программист >15.02.07 А С F 59 ФИО сотрудника Должность Дата получения премии 60 Сидоров Л.А. 61 Инженер- программист 62 >15.02.97 110 Рис. 4. Диапазон критериев при трех и более условиях отбора Например, для рассматриваемого списка следующий диапа- зон критериев задает отбор записей, содержащих в столбце "ФИО сотрудника" значения либо " Петрова И.В.", либо " Иванов С.А. ", либо " Сидоров Л. А. ". Пример 3. В условии отбора используется возвращаемое формулой значение. В этом случае диапазон критериев и указываемая в нем формула должны удовлетворять следующим требованиям: • формула может иметь в своем составе несколько функций и зависеть от нескольких полей списка, но должна обязательно иметь в качестве результата логическую величину ИСТИНА (True) или ЛОЖЬ (False); • ссылки в формуле могут указывать как на ячейки списка, так и на другие ячейки рабочего листа. При этом ссылки на ячейки списка должны быть относительными, а на ячейки вне списка – абсолютными; • вычисляемый критерий должен иметь имя поля, не совпа- дающее ни с одним из имен полей списка; • формула должна ссылаться хотя бы на одно поле списка; • при задании ссылки на все значения столбца необходимо указывать относительную ссылку на первую ячейку этого столб- ца. Оформленный таким образом критерий называется вычис- ляемым. В результате фильтрации из списка выбираются записи, для которых проверяемое условие истинно. Например, следующий вычисляемый критерий, заданный для списка на рис. 1, позволяет найти фамилии сотрудников, ко- торые имеют сумму оклада и премии более 8000 рублей (рис. 5). А 67 ФИО сотрудника 68 Петрова И.В. 69 Иванов С.А. 70 Сидоров Л. А. 111 Рис. 5. Вычисляемый критерий Обратите внимание, что формула помещена под именем по- ля ("Всего руб."), не совпадающим ни с одним из имен полей списка на рис. 1. В качестве имени поля можно использовать лю- бой уникальный текст. Приведем примеры задания формул в критериях для списка на рис. 1, выполняющих сравнение содержимого ячеек в преде- лах записи: =E2=G2 =E2 Более сложные критерии используют ссылки на ячейки не- скольких записей списка или на ячейки вне списка, например: =Е2=$В$72 – сравнение значений ячеек в поле "Оклад (руб.)" с значением внешней по отношению к списку ячейки $В$72. Логические функции И, ИЛИ, НЕ расширяют возможности вычисляемых критериев. Например, формула =И(С2="Инженер";Р2>1.02.08) в крите- рии задает отбор записей, содержащих в поле "Должность" зна- чение "Инженер" и в поле "Дата получения премии" – значение дат позже 1.02.08. После создания диапазона критериев можно непосредст- венно выполнить саму операцию фильтрации с использованием расширенного фильтра. Для этого необходимо выполнить сле- дующие действия: 1. Активизировать одну из ячеек списка и выполнить ко- манду "Расширенный фильтр" меню "Данные" \ "Фильтр". 2. В поле "Диапазон критериев" появившегося диалогового окна "Расширенный фильтр" указать ссылку на данный диапазон критериев и нажать на "ОК". Замечания: А 72 Всего руб. 73 =E2+G2>8000 112 • При задании критериев строчные и прописные буквы не различаются. • При задании критериев можно использовать символы "*" и "?" в соответствии с правилами оформления шаблонов: "*" – лю- бая последовательность символов, "?" – один символ в заданной позиции. • Отфильтрованные записи можно поместить в любое место текущего рабочего листа. Для этого в диалоговом окне "Расши- ренный фильтр" нужно установить переключатель в положение "Скопировать результат в другое место" и в поле "Поместить ре- зультат в диапазон:" указать место размещения отфильтрован- ных записей списка. Вычисление промежуточных и общих итогов Промежуточными итогами называют результаты вычисле- ния по определенной функции изменяющихся значений одного или нескольких столбцов записей, предварительно разбитых на несколько групп. Общий итог вычисляется с применением функ- ций, заданных для вычисления промежуточных итогов. Он рас- полагается в виде отдельной строки в конце списка. Необходи- мость в выполнении таких операций возникает довольно часто, поскольку они позволяют свести воедино и проанализировать однородные и неоднородные данные списка. Прежде чем задать вычисление промежуточных итогов, все записи списка необходимо разбить на несколько групп. С этой целью используется сортировка. Ее производят по полю, при из- менении значений которого предполагается вычисление проме- жуточных и общих итогов. Например, отсортировав в алфавит- ном порядке список на рис. 1 по полю "Занимаемая должность", все записи списка тем самым разбиваем на несколько групп по категориям должностей. Теперь для любого столбца группы за- писей можно задать автоматическое вычисление промежуточных итогов с указанием определенного типа функции. Например, задать функцию СРЗНАЧ для столбцов "Оклад" и "Премия" рассматриваемого списка. В этом случае для каждой группы записей списка применительно к указанным столбцам 113 будут автоматически вычислены промежуточные итоги, а в кон- це появится строка с общими итогами для всего списка. Чтобы задать операцию вычисления промежуточных итогов для отсортированного по определенному полю списка, необхо- димо выполнить следующие действия: 1. Выполнить команду "Итоги" меню "Данные". 2. В поле "При каждом изменении в" появившегося диало- гового окна выбрать имя столбца, содержащего группы записей (т. е. имя столбца, по которому произведена сортировка). 3. В раскрывающемся списке "Операция" выбрать функ- цию для вычисления промежуточных итогов. 4. В поле "Добавить итоги по" выбрать столбцы, значения которых будут обрабатываться с применением указанной функ- ции. 5. Нажать "ОК". После этого под каждой группой записей появится строка с промежуточными итогами для выбранных столбцов, а в конце списка – строка с общим итогом для всех записей списка. Авто- матически появятся символы структуры списка, позволяющие скрыть или отобразить его детальные данные. Строки с вычисленными промежуточными итогами можно разместить не под, а над каждой группой записей. В этом случае необходимо сбросить флажок "Итоги под данными" в диалого- вом окне "Промежуточные итоги". Если группы записей списка с вычисленными для них про- межуточными итогами предполагается вывести на печать, то они могут быть размещены на отдельных страницах. Для этого необ- ходимо установить флажок "Конец страницы между группами". К списку с вычисленными промежуточными итогами мож- но повторно применить ту же операцию, но с выбором других столбцов и функций или по отношению к подгруппам сущест- вующих групп записей. В последнем случае создаются вложен- ные промежуточные итоги. Excel при этом автоматически созда- ет соответствующие уровни структуры. Порядок действий при создании вложенных промежуточ- ных итогов аналогичен рассмотренному. Дополнительно необхо- димо сбросить флажок "Заменить текущие итоги" в диалоговом окне "Промежуточные итоги". 114 Чтобы удалить промежуточные итоги, достаточно выпол- нить команду "Отменить итоги" в меню "Правка" или нажать кнопку "Убрать все" в диалоговом окне "Промежуточные итоги". Кроме того, итоги удаляются и при пересортировке списка. 1. ПОРЯДОК ВЫПОЛНЕНИЯ 1. Получить задание у преподавателя. 2. Выполнить задание. 3. Показать преподавателю и ответить на контрольные во- просы. 2. ЗАДАНИЕ Вариант 1 Задание № 1 Создать и заполнить таблицу исходных данных для своего варианта (табл. П1). Задание № 2 Вычислить: 1) средний процент наценки на весь товар; 2) доход от реализации всего товара; 3) максимальную, минимальную и среднюю цены реализа- ции и закупа; 4) общее количество единиц товара. Задание № 3 Отсортировать данные в таблице: 1) по фирме; 2) по складу и фирме; 3) по цене реализации; 4) по названию, фирме и складу. Задание № 4 При помощи "Фильтра" выбрать следующие данные: 1) весь товар на складе A; 2) товар фирмы Samsung; 3) цена реализации <7600 руб. и склад С; 4) наименование = Телевизор или Магнитола; 5) наименование = Телевизор и цена реализации < 8600 руб.; 115 6) склад = B и Наименование = Телевизор. Задание № 5 Подвести промежуточные и общие итоги: 1. вычислить доход от реализации товара фирмы Panasonic; 2. вычислить общее количество единиц товара на складе A; 3. вычислить общую сумму закупа всех товаров фирмы Philips, хранящихся на складе B. Исходные данные для варианта № 1 Название Фирма Кол -во Склад Цена за- купа, руб. % на- ценки Наценка Цена реали- зации Сумма закупа Сумма реали- зации Телевизор Panasonic 300 A 10000 10 Телевизор Philips 100 A 8000 11 Телевизор Philips 50 B 7600 12 Телевизор Philips 30 B 7200 13 Телевизор Samsung 100 C 4800 14 Телевизор Sony 150 C 17600 15 Телевизор Sony 250 A 12800 16 Магнитола Panasonic 95 A 6000 17 Магнитола Philips 300 B 4800 18 Магнитола Samsung 500 B 3600 19 Магнитола Sony 100 C 7000 20 Видеомаг- нитофон Panasonic 700 C 4800 21 Видеомаг- нитофон. Philips 200 A 4400 22 Видеомаг- нитофон. Samsung 100 A 4000 23 Видеомаг- нитофон. Sony 120 B 4600 24 1 1 6 117 4. КОНТРОЛЬНЫЕ ВОПРОСЫ 1. Что такое список в Excel? 2. Как создать новый список в Excel ? 3. Как будут располагаться записи в списке, если задан "возрастающий" порядок сортировки? 4. Какие действия нужно выполнить, чтобы обработать таб- лицу с помощью "автофильтра"? 5. Как задается диапазон критериев в "расширенном фильт- ре"? 6. Что нужно сделать, чтобы промежуточные итоги можно было разместить на отдельных страницах? Лабораторная работа № 6 Работа с макросами в табличном процессоре MS EXCEL. 3. ЦЕЛЬ РАБОТЫ Целью работы является приобретение практических навы- ков по созданию макросов в виде таблиц в MS EXCEL. 4. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ Некоторые действия вMicrosoft Excel могут носить повто- ряющийся характер. Например, нам нужно выполнить одинако- выми заголовками целый ряд рабочих документов или одним и тем же способом отформатировал все заголовки. Вместо того чтобы каждый раз вводить одни и те же данные или выполнять команды форматирования, вы можете создать макрос и затем, за- пуская его, выполнять такие операции автоматически. За каждой кнопкой панели инструментов закреплена небольшая программа – макрос. Макрос представляет собой последовательность макроко- манд и макрофункций, написанных на языке Visual Basic. Создание макросов с помощью макрорекордера Существует два различных способа создания макроса. 1. Необходимо выполнить задачу, которую вы хотите авто- 118 матизировать, при этом компьютер записывает все ваши действия для последующего их воспроизведения. 2. Написать макрос "с нуля", используя язык программиро- вания Visual Basic for Applications (VBA). Макрорекордер – это встроенный в Microsoft Excel инстру- мент, который может отслеживать выполнения вами задачи и по- сле этого автоматически повторять те же шаги. Процесс записи макроса можно свести к следующим шагам. 1. Сообщите Microsoft Excel, что хотите начать запись мак- роса. 2. Дайте имя макросу. 3. Выберите параметры для макроса, такие как описание и где он должен быть сохранен. 4. Запустите макрорекордер. 5. Выполняйте задачу (задачи), которую хотите автоматизи- ровать. 6. Остановите макрорекордер. Запись макроса При записи макроса все шаги, необходимые для выполнения действий, записываются программой записи макроса. Перемеще- ние по ленте не включается в записанные шаги. 1. Если вкладка Разработчик недоступна, выполните следующие действия для ее отображения: щелкните значок Кнопка Microsoft Office , а за- тем щелкните Параметры Excel. в категории Личная настройка в группе Основные параметры работы с Excel установите флажок По- казывать вкладку "Разработчик" на ленте, а затем нажмите кнопку ОК. 2. Для установки уровня безопасности, временно разре- шающего выполнение всех макросов, выполните следующие действия: на вкладке Разработчик в группе Код нажмите кноп- ку Безопасность макросов. 119 в группе Параметры макросов выберите переклю- чатель Включить все макросы (не рекомендуется, возможен запуск опасной программы), а затем дважды нажмите кнопку ОК. ПРИМЕЧАНИЕ. Для предотвращения запуска потен- циально опасных программ по завершении работы с макроса- ми рекомендуется вернуть параметры, отключающие все мак- росы. 3. На вкладке Разработчик в группе Код нажмите кнопку Запись макроса 4. В поле Имя макроса введите имя макроса. 5. В списке Сохранить в выберите книгу, в которой не- обходимо сохранить макрос. 6. Для присоединения описания макроса введите нужный текст в поле Описание. 7. Для начала записи макроса нажмите кнопку ОК. 8. Выполните действия, которые нужно записать. 9. На вкладке Разработчик в группе Код нажмите кнопку Остановить запись СОВЕТ. Можно также нажать кнопку Остановить запись слева от строки состояния Чтобы использовать макрос в любом месте рабочего сто- ла, перед началом записи макроса необходимо выделить началь- ную ячейку. Если выделить ячейку после начала записи макроса, то процедура выделения будет воспринята как часть макроса. Использование записанных макросов . Чтобы выполнить ранее созданный макрос: 1. Если вкладка Разработчик недоступна, выполните сле- дующие действия для ее отображения: щелкните значок Кнопка Microsoft Office , а затем щелкните Параметры Excel. 120 в категории Основные в группе Основные параметры работы с Excel установите флажок Показывать вкладку "Раз- работчик" на ленте, а затем нажмите кнопку ОК 2. Для установки уровня безопасности, временно разре- шающего выполнение всех макросов, выполните следующие действия. на вкладке Разработчик в группе Код нажмите кноп- ку Безопасность макросов. в категории Параметры макросов в группе Па- раметры макросов нажмите кнопку Включить все макро- сы (не рекомендуется, возможен запуск опасной про- граммы), а затем нажмите кнопку ОК ПРИМЕЧАНИЕ. Для предотвращения запуска потен- циально опасных программ по завершении работы с макроса- ми рекомендуется вернуть параметры, отключающие все мак- росы. 3. Откройте книгу, содержащую нужный макрос. 4. На вкладке Разработчик в группе Код нажмите кнопку Макросы. 5. В поле Имя макроса введите имя макроса, который нужно выполнить. 6. Выполните одно из следующих действий: для запуска макроса в книге Excel нажмите кнопку Выполнить СОВЕТ. для запуска макроса можно также нажать клавиши CTRL+F8. Для прекращения выполнения макроса нажмите клавишу ESC. для запуска макроса из модуля Microsoft Visual Basic нажмите кнопку Изменить, а затем в меню Run выберите команду |