|
|
Методичка ИРМ 1__ 2014. Методические указания к информационно компьютерному практикуму Москва Издательство мгту им. Н. Э. Баумана 2008 удк 681. 3 378 ббк 32. 81
3.Сортировка и подведение итогов в списке.
3.1.Сортировка в нескольких столбцах:
Копировать исходную таблицу, импортированную из текстового файла, ниже по листу.
Форматировать строку заголовков, выделив их полужирным шрифтом.
Установить курсор на заголовке "Фамилия", а затем щелкнуть по стрелке, а затем щелкнуть по кнопке "Сортировка по возрастанию" (А-Я стрелка вниз).
Щелкнуть по заголовку "Город рождения",, а затем щелкнуть по кнопке "Сортировка по убывания" (А-Я стрелка вверх).
3.2.Формирование списка для сортировкиСортировка в произвольном порядке:.
Выделить поля под заголовком в столбце "Фамилия" и копировать их за последний столбец таблицы так, чтобы между новым столбцом и таблицей остался один пустой столбец.
Расставить фамилии в новом столбце в нужном порядке. Выделить поле новых значений.
Вызвать панель Параметры Excel через опцию Другие команды настройки Панели быстрого доступа. меню "Сервис" щелкнуть по опции "Параметры, где выделить лист "Списки".
В опции Основные кне "Списки щелкнуть по кнопке строке "Изменить спискиНОВЫЙ СПИСОК" и щелкнуть по кнопке "Импорт", а затем по кнопке "ОК".
Очистить новый столбец.
Проверить работу списка, для чего занести в одну из свободных ячеек какой-нибудь элемент из нового списка. Очистить новый столбец, вВыделить одну из ячеекячейку и «протянуть» ее за нижний угол левой кнопкой мыши. списка.
3.3. Сортировка в произвольном порядке.
Выделить произвольную ячейку таблицы. Щелкнуть по кнопке Сортировка в группе Сортировка и фильтр вкладки Данные.полнить произвольную сортировку, в меню "Данные" выбрать опцию "сортировка".
На новой вкладке "Сортировка диапазона" выбрать в раскрывающемся списке "Сортировать по" выбрать заголовок соответствующего столбца фамилия, во втором поле - значение, а в поле Порядок выбрать настраиваемый список и занести в это поле сформированный в предыдущем пункте список.затем щелкнуть по кнопке "Параметры" для выбора на следующей вкладке порядка сортировки (в окне "Сортировка по первому уровню"). Дважды нажать на кнопку "ОК".
Щелкнуть по кнопке Добавить уровень.
Для выполнения сортировки внутри групп первого уровня следует занести соответствующие значения в поля добавленного уровня.
Для выполнения сортировки щелкнуть по кнопке "ОК".
Проведя подобные манипуляции можно обеспечить сортировку по всем заявленным уровням.перевести курсор на соответствующие заголовки и выполнить сортировки.
3.3.Определение числовых объемов по каждой отсортированной группе (используя данные столбца "Оклад"):
Копировать исходную таблицу, импортированную из текстового файла, ниже по листу.
Активизировать любую ячейку таблицы (списка). В меню "Данные" активизировать команду "Итоги".
В группе Структура на листе Данные активизировать кнопку диалоге "Промежуточные итоги"." в списке "При каждом изменении в" выделить строку "Организация".
На появившейся вкладке Промежуточные итоги установить, в каких полях будут производиться изменения итогов, тип итоговой операции, перечень полей итогов и другие опции.Сохранить остальные опции и
щЩелкнуть по кнопке "ОК". Слева от таблицы появятся числа в рамках, которые соответствуют каждому из уровней итогов, в итоговых строках показаны значения по каждомуй выбранному полю организации.
Щелкнуть пона цифре 2 в рамке, чтобы отобразить только итоги (останутся только итоги по каждомуй полю организации (в данном случае) и общий итог).
Перейти вниз по линейке итогов и щелкать на знаке "+" рядом со строкой итогов, чтобы отобразить все данные, и на знаке "-", чтобы скрыть детали.
4.Фильтрация списка.
4.1. Копировать исходную таблицу, импортированную из текстового файла, ниже по листу. Добавить дополнительные столбцы в список, в столбцах указать выплаты персональных надбавок (10%) за апрель, май, июнь:
Ввести "Апрель" в ячейку заголовка за столбцом "Оклад". Протащить мышь на три ячейки вправо для формирования заголовков трех дополнительных столбцов таблицы.
Выделить поле значений столбца "Оклад" и перетащить мышь в режиме Автозаполнение на 3 ячейки вправо. Все ячейки заполнятся новыми значениями.
Разделить все значения ячеек на 10: Ввести число 10 в следующую правую от таблицы ячейку на первую строку. Поместить значение этой вспомогательной ячейки в буфер обмена.
Выделить столбец Апрель и щелкнуть правой клавишей мыши по этому полю, (или в меню "Правка" выбрать опцию "Специальная вставка".
На вкладке "Специальная вставка" установить флажки на строках "значения" и "разделить" и щелкнуть по кнопке "ОК". В выделенных ячейках значения уменьшатся в 10 раз.
Повторить предыдущую операцию для значений в столбцах "май" и "июнь".
Очистить поле вспомогательной ячейки.
4.2.Фильтрация заданных значений:
Выделить одну из ячеек таблицы
Щелкнуть по кнопке Фильтр в группе Сортировка и фильтр вкладки Данные. Кнопка активизируется и в строке заголовка появятся ячейки (стрелки) фильтра.
Щелкнуть по на одной из ячеейке фильтра в списке "Город". В меню "Данные" выбрать опцию "Текстовые фФильтры" .и щелкнуть по команде "Автофильтр".
В меню выбрать условие фильтрации (равно или по первой букве). В списке "Город" выберите географически удаленный.
Перейти на первую ячейку в списке "Июнь" и введите максимальную (из списка) надбавку (можно узнать ее раскрыв список). Используя режим автозаполнения, увеличить надбавки всем членам Совета из удаленного города.
Открыть список, нажав на кнопку Фильтр (погасив кнопку) группы Сортировка…,опцией "Все" и убедиться в правильности выполненных преобразований.
Действуя аналогично выделить (отфильтровать) всех лиц, у которых оклад больше или равен некоторой сумме. Оценить результат.
5.Макропрограммирование в табличных документных системах.
5.1.Разработать макрос оформления таблицы расходов: Оборудование, Зарплата, Аренда. Налоги, добавить строку Итого. С помощью таблицы фиксировать расходы на текущий день. Выполнить следующую последовательность операций:
Перейти на новый лист рабочей книги. Записать соответствующее имя ярлыка.
Активизировать опцию "Запись мМакросаы" группы Макросы на вкладке Вид команды "Сервис" и перейти в режим "Начать запись";.
На появившейся панели выбрать опцию Запись макроса.
На вкладке "Запись макроса" в поле "Имя макроса" ввести соответствующий текст без пробелов;
В поле "Описание" записать небольшое пояснение или имя файла или папки, в которой будет храниться макрос;
Нажать на кнопку Параметры и на поле нижней части вкладки уУстановить для макроса комбинацию горячих клавиш, имя опции в меню Сервис (опция Макрос), указать на используемый язык программирования (в большинстве случаев целесообразно ориентироваться на язык Visual Basic), определить выбор места хранения макроса: Личная, ЭтаТекущая книга, НоваяОтдельная книга (следует учесть, что используемый язык программирования для записи макроса в большинстве случаев - Visual Basic),,.
Активизировать режим записи макроса, щелкнув по кнопке ОК;
Нанести на поле рабочего листа основные элементы создаваемой таблицы, например, заглавие: Расходы, названия строк: Оборудование, Зарплата, Налоги, Аренда, Итого.
С помощью Мастера функций установить под заглавием таблицы ячейку с указанием текущей даты и времени. (в окнах первого шага мастера установить строки: Дата и время, Сегодня);
На ячейке таблицы строки Итого активизировать клавишу суммирования и протащить мышь по строке таблицы для подведения итога по столбцу;
Раздвинуть поле вычисляемого столбца. Отформатировать текст заголовков строк и заглавия таблицы.
Остановить запись макроса, для чего щЩелкнуть по кнопке Макросы и на выпавшей панели "Остановить запись".
После выполнения показанной последовательности операций можно постоянно создаватьвносить данные в текущую та таблицу расходов, не формируя ее структуру. Достаточно в меню кнопки оманды "МакросыСервис" выбрать строку "Макросы", в списке выбрать необходимый и щелкнуть по кнопке "Выполнить.
Перейти на свободный лист книги и проверить работу макроса. Обратить внимание на размещение таблицы на поле нового листа.
5.2.Редактирование макроса:
Для редактирования макроса необходимо воспользоваться вкладкой макроса, появляющейся на экране после установки имени макроса. Можно заново переопределить значение каждой ячейки таблицы, воспользовавшись кнопкой "Изменить" или прейти на уровень записи текста программы на языке Visual Basic через кнопку "Войти". В этомпоследнем случае на экране появляется текст программы и панель инструментов Visual Basic .
Используя текст макроса, увеличить итоговую сумму на 20%;
На новом листе рабочей книги вызвать разработанный макрос и подготовить новый отчет о расходах.
5.3.Относительное размещение результатов макроса.
Активизировать опцию "Макросы" группы Макросы на вкладке Вид.
На появившейся панели выбрать опцию Относительные ссылки. Повторно щелкнуть опцию "Макросы" группы Макросы, кнопка Относительные ссылки должна быть подсвечена (стала активной).
Запустить режим записи макроса. Назвать его Дата_время.
Кликнуть мышью в свободной ячейке листа. Выбрать из Мастера функций и разместить в этой ячейке функцию ТДАТА. Завершить выполнение макроса.
Закрыть режим записи макроса.
Проверить режим относительного размещения результатов выполнения макроса. Значение функции ТДАТА должно размещаться в той области листа, где зафиксирован маркер.
5.34.Закрепление макроса за кнопкой:
Открыть вкладку Вставка. Активизировать команду "Вид" системного меню, перейти на опцию "Панели инструментов" и в списке выбрать панель "Настройка",
Щелкнуть по кнопке листа "ФигурыКоманды", в группе Иллюстрации. в окне категорий выбрать "Макросы". На поле выбрать "Настраиваемая кнопка";
Выбрать фигуру для кнопки и вставить ее в соответствующую точку листа. Скорректировать при необходимости размеры кнопкиЗахватить выбранную кнопку и перенести ее на панель инструментов "Элементы управления". Закрыть панель "Настройка",
Щелкнуть по фигуреперенесенной на панель кнопке правой кнопкой мыши , выбрать в листе "Назначить макрос" и из списка макросов выделить Дата_время необходимый, нажать клавишу ОК, закрыть диалоговое окно;
Проверить работу макроса путем нажатия на "его" кнопку, при этом с учетом результатов действий предыдущего пункта значения будут размещаться в произвольном месте.;
При необходимости на панель инструментов выносятся несколько кнопок, ассоциированных с разными макросами. К сожалению, вид стандартной кнопки одинаков для всех макросов и их выбор придется осуществлять по местоположению кнопки на панели инструментов;.
Чтобы убрать отдельную кнопку достаточно «вырезать» ее рисунок. ее следует перетащить за пределы панели при открытом диалоговом окне "Настройка панелей инструментов".
6. Задание для самостоятельной работы
Подготовить таблицу для записи компьютеров, находящихся на балансе офиса, используя следующие параметры: компьютер, процессор, RAM, Винчестер, рубли, Количество.
Записать в таблицу сведения об имеющихся в офисе компьютерах, используя Форму ввода данных.
Подготовить таблицу записи расходов за текущий месяц, используя следующие строки: Оборудование, Зарплата, Аренда, налоги, Итого. Ввести дополнительный столбец: Расходы за текущий год (по каждой строке). Написать макрос обслуживания столбца Текущий год: После ввода информации за текущий месяц макрос должен (после вызова макроса) записать текущие расходы за год.
Сформировать макросы увеличения и уменьшения расходов на зарплату на 100 руб. при каждом вызове макроса. Снабдить макросы горячими клавишами и кнопками вызова.
Записать подготовленные решения на дискету.
Удалить на панели инструментов установленные ранее кнопки вызова макросов.
7. Сохранить рабочую книгу., Удостовериться в правильности сохранения файлов.
Вопросы для самоконтроля
Сохраняется ли структура табличных данных при сортировке
Возможна ли сортировка по нескольким параметрам
Что нужно, чтобы путем фильтрации выделить уникальный элемент таблицы
Возможно ли создать макрос в макросе
Возможна ли реализация рекурсии при создании макросов макрорекордером
Допускается ли в теле макроса создание новой книги
Как поведет себя макрос, если в нем сформировано обращение к эзлементам закрытой книги
Возможно ли исполнение макроса, отсутствующего в текущей книге
Работа №ТЕМА 8: Использование функций в табличных системах
Цель работы : – приобретение и закрепление практических навыков работы с таблично организованными данными при разработке штатных документов в организациях.
Главной задачей работы является практическое освоение правил использование элементов библиотеки функций среды Ms Excel. При выполнении задания должны быть освоены приемыи формирование практики защиты крытия табличных данных или их фрагментов, а также работы с набором стандартных функций, имеющихся в среде Ms Excel, на примере типового штатного расписания факультета ВУЗа.
Отчетом по работе служит протокол действий по реализации заданий в виде книги именной Ms Excel, на листах которой представлены соответствующие примеры.
Установочные положения.
В любой организации, компании или предприятии вне зависимости от их отношения к собственности (бюджетное предприятие, ОАО, ООО и т.п.) существует документ, без которого организация не может существовать. Это т.н. штатное расписание.
Этот документ представляет собой таблично организованный перечень должностей всех исполнителей, которые необходимы для нормального функционирования организации. Наименования всех возможных должностей, как правило, имеют устоявшиеся названия и перечислены в Тарифно-квалификационном справочнике, подготавливаемым Минтрудом страны. В этом же справочнике указаны квалификационные требования к должности: образовательный ценз, необходимый опыт работы и т.п.. Для одного наименования должности возможно несколько градаций квалификации в зависимости от этих параметров. Например, для инженеров существует до 5-и должностных градаций.
Каждой должностной градации устанавливается свой тарифный разряд, определяющий ее «качество», т.е. тарифный разряд определяет уровень оплаты труда исполнителя. Очевидно, что для каждой должностной категории в штатном расписании отводится строка таблицы с указанием всех необходимых атрибутов для этой категории: количество единиц, должностной оклад, возможные доплаты и т.д. Таким образом штатное расписание становится основой для расчета затрат, связанных с оплатой труда и базой для формирования различных справок и сводок.
Поскольку ввиду инфляции или по другим причинам значение ставок тарифных разрядов групповым образом (единовременно) меняется, то это требует корректировки соответствующих значений всех от них зависящих параметров. Из этого следует, что хранить числовые значения параметров в самой таблице штатного расписания (ШР) нецелесообразно, ибо это будет требовать постоянного переписывания таблицы. Правильнее в таблице ШР указывать ссылки на место хранения этих данных, т.е. организовать косвенную адресацию данных по наименованию базового поля таблицы, каковым является полное название должности. Подстановка значений при такой адресации реализуется соответствующей функцией из числа функций библиотеки Ms Excel.
В данной работе
Задание для самостоятельной работы
Постановка задачи
Ннеобходимо сформировать бюджетное штатное расписание (ШР) бюджетной организации - факультета ВУЗа, которое строится из типовых штатных должностей, однозначно связанных с разрядами Единой Тарифной Сетки (ЕТС). В соответствии с этой сеткой каждой должности соответствует свой т.н. тарифный разряд, а ему, соответственно, значение оклада по должности). ЕТС (значение тарифных ставок) периодически изменяется, а структура ШР при этом фактически сохраняется.
Штатное расписание состоит из двух структур: ШР преподавательского персонала (ППП) и ШР других видов персонала (УВП – учебно-вспомогательный, АУП – административно-управленческий, ПОП – прочий обслуживающий). Для каждого вида персонала помимо должностных окладов предусмотрены различные надбавки и доплаты, фиксируемые в штатном расписании. На основании данных ШР ежемесячно составляется заявка на бюджетные ассигнования, необходимые для выплаты заработной платы (ЗП).
Поскольку штатное расписание очень важный документ, доступ к нему на предмет корректировки значений и внесения изменений разрешен лишь конкретным лицам. Для этого лист ШР или его части защищаются паролем, а открытой остается только та часть таблицы, которая касается определенных исполнителей.
При выполнении пунктов задания обратить особое внимание на использование функций из имеющегося стандартного списка, а также возможности по связыванию данных различных листов одной книги и данных различных книг.
|
|
|
Скачать 446.63 Kb.