Центр Компьютерного Обучения " Специалист ", 2012 Microsoft Excel 2010. Расширенные возможности решение
 Скачать 4.3 Mb.
|
|
СуммаUSD, считая, что курс 1$=29 р. Сравнить полученный результат с образцом: Форматирование отчета Построить на новом листе отчет по данным таблицы с листа Бытовая техника для анализа количества брака (шт), который выявили Приемщики по каждому наименованию и каждому производителю. Переименовать лист – АнализБрака. Отформатировать отчет: Применить стиль сводной таблицы Средний в любой цветовой схеме. Изменить макет отчета для предоставления в табличной форме. Расположить все промежуточные итоги в нижней части группы. На листе Поставки оформить отчет сводной таблицы: Изменить макет отчета, чтобы показать в форме структуры. Показать промежуточные итоги, расположив их в верхней части группы. Добавить расчет общих итогов как по строкам и столбцам. Вставить пустую строку после каждого элемента. Настроить повторение всех подписей элементов. Сводные диаграммы На листе АнализБрака переместить поле Производитель в область фильтр отчета. Построить по данным отчета диаграмму – Гистограмма с группировкой. Отобразить только производителя Tefal и Braun. Изменить вид диаграммы на Гистограмма с накоплением. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 75 Очистить фильтры. Обновление данных В исходной таблице на листе Бытовая техника необходимо сделать изменения в исходных данных. Один из приемщиков сменил фамилию: была Толерантная Т.Т. – стала теперь Тихая Т.Т. Обновить данные отчета на листе АнализБрака. Отчет на листе Поставки построен на основе таблицы с листа Бытовая техника, в которую были добавлены записи. Обновить источник данных отчета, чтобы добавленные значения были учтены (в отчете после обновления должно появиться наименование Весы). Группировка данных По данным таблицы на листе Комплекты,построить отчет на этом же листе, разместив результат начиная с ячейки Н1, в котором можно видеть суммы продаж каждого наименования по всем годам. По данным таблицы с листа Бытовая техника, предоставить данные поставок по количеству брака и его стоимости за следующие периоды: месяц, квартал, год. Отчет разметить на новом листе, который затем переименовать в Брак. На листе Заказы по данным таблицы,построить отчет на этом же листе, разместив результат начиная с ячейки Н1, в котором можно проанализировать недельные суммы продаж подарочных наборов, начиная с 1-го понедельника года – 04.01.2010. По данным таблицы на листе Страхование,определить средние затраты сотрудников на страхование в зависимости от пола, начиная с 25-ти лет с шагом в 5 лет. Результат разместить на этом же листе, начиная с ячейки F1. Расчетные значения представить целым числом с обозначением знака $. По данным таблицы на листе Комплекты,построить отчет для анализа продаж Группы компаний: Нирвана, Перспектива, Рапсодия, Фаворит по годам. Отчет разместить на новом листе, который переименовать в АнализПродаж. Сохранить изменения в файле и закрыть его. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листах 13, 14, 15 и 16. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 76 Модуль 4. О СОБЕННОСТИ СОВМЕСТНОЙ РАБОТЫ Защита ячеек, листов и рабочих книг Excel В программе Excel предусмотрено несколько уровней защиты, позволяющих управлять доступом к данным Excel и их изменением. Можно ограничить доступ к файлу, можно применить защиту к элементам книги, ограничив просмотр отдельных листов или изменение данных на листе, можно защитить элементы листа, например, ячейки листа, запретив доступ к ним или предоставить доступ к определенным диапазонам определенным пользователям. Каждая из этих защит может быть установлена как по отдельности, так и в совокупности. Защита ячеек листа Защита полезна в таблицах, содержащих сложные формулы и заранее заданные константы. На рабочем листе каждая ячейка по умолчанию является защищаемой, и пользователи не могут вносить изменения в них. Например, в заблокированных ячейках нельзя вставить, изменить, удалить или отформатировать данные. Однако можно указать элементы, которые пользователи смогут изменять после защиты листа. Защита проводится в два действия: сперва происходит настройка атрибутов ячеек, а затем устанавливается защита ячеек листа. Скрытие, блокировка и защита элементов книг и листов не предназначены для повышения безопасности или защиты каких-либо конфиденциальных сведений, содержащихся в книге. Это лишь помогает убрать данные или формулы, которые могут смутить других пользователей, и предотвратить просмотр и изменение этих данных. 1. Настройка атрибутов ячеек. Выделить ячейки. Щелкнуть правой кнопкой мыши по выделению и выбрать Формат ячеек [Format cells] или нажать клавиши Ctrl + 1 Открыть вкладку Защита [Protection]. Снять атрибут Защищаемая ячейка [Locked] с ячеек, которые после установки защиты листа должны изменяться. Установить атрибут Скрыть формулы [Hidden] для ячеек, где нужно скрыть истинное содержимое ячеек, которое отображается в строке формул. Атрибут Защищаемая ячейка [Locked] должен быть установлен. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 77 2. Установка защиты ячеек листа На вкладке Рецензирование [Review], в группе Изменения [Changes], выбрать Защитить лист [Protect Sheet] Флажок Защитить лист и содержимое защищаемых ячеек [Protect worksheet and contents of locked cells] должен быть установлен. В списке Разрешить всем пользователям этого листа [Allow all users of this worksheet to] отметить разрешения на действия с защищенными ячейками. Установить пароль в поле Пароль для отключения защиты листа [Password to unprotect sheet], иначе защита легко будет снята другим пользователем. Пароль может содержать только буквы a-z, цифры 0-9 и специальные знаки, например !, @, #, $, %, ^, &, *, (, ), +, = ОК. Защита ставится на каждый лист отдельно, при необходимости защитить ячейки другого листа следует повторить алгоритм. Для снятия защиты достаточно на вкладке Рецензирование [Review], в группе Изменения [Changes], выбрать Снять защиту листа [Unprotect Sheet]. Перенастраивать атрибуты ячеек не нужно, т.к. без защиты ячеек листа они не действуют. ПРАКТИКУМ: Открыть файл 04_1 Защита. На листе Защита1 установить защиту ячеек листа с паролем Pro01, чтобы только в ячейки C2:D24 можно было вносить изменения, а в ячейках E2:L25 нельзя было увидеть содержимое в строке формул. Проверить установленную защиту. Снять защиту с листа. Установить защиту ячеек листа Защита1 с паролем 007 таким образом, чтобы можно было выделять только изменяемые ячейки. Выборочная защита диапазонов для разных пользователей. Если с файлом работают несколько пользователем, при этом каждый может менять только свою область листа, то можно сделать выборочную защиту диапазонов с уникальным паролем, действующим на разные диапазоны ячеек. Для установки такой защиты надо: 1. На вкладке Рецензирование [Review], в группе Изменения [Changes], выбрать Разрешить изменение диапазонов [Allow Users to Edit Ranges]; 2. В появившемся окне нажать кнопку Создать [New]. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 78 Ввести Имя [Title] защищаемого диапазона. Выделить защищаемые Ячейки [Refers to cells]. Ввести Пароль диапазона [Range password] для доступа к ячейкам. ОК. Повторить пункт 2 для разных пользователей и разных диапазонов; 3. Включить защиту листа, нажав кнопку Защитить лист [Protect Sheet]. Теперь при попытке доступа к любому из защищенных диапазонов, программа будет требовать ввести пароль именно для этого диапазона, т.е. каждый пользователь будет только со своими ячейками. Для снятия защиты достаточно на вкладке Рецензирование [Review], в группе Изменения [Changes], выбрать Снять защиту листа [Unprotect Sheet]. ПРАКТИКУМ: В открытом файле 04_1 Защита, на листе Защита2 установить защиту ячеек листа с паролем 007, создав два защищаемых диапазона: Диапазон Тарифная ставка для ячеек C2:C24 с паролем zone01 Диапазон Отработано часов для ячеек D2:D24 с паролем zone02 Проверить установленную защиту, путем ввода данных в защищаемые ячейки. Сохранить сделанные изменения. Защита листов книги Можно заблокировать структуру книги, чтобы пользователи не могли листы добавлять/удалять, перемещать/копировать, скрывать/отображать, а так же переименовывать и менять цвет ярлычка. Можно также запретить пользователям изменять размер или положение окон листа. Защита структуры и окна книги распространяется на всю книгу. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 79 1. На вкладке Рецензирование [Review], в группе Изменения [Changes], выбрать Защитить книгу [Protect Workbook]. 2. Выбрать вариант защиты книги. Поставить флажок структуру [Structure], если требуется защита структуры книги от действий с листами, т.е. лист нельзя будет добавить, удалить, скопировать, переместить, скрыть и отобразить. Выбрать флажок Windows, если нужна защита книги от действий с ее окном, т.е. запрет на изменение размера окна книги внутри окна Excel. 3. Ввести Пароль [Password]. 4. ОК. Для отмены защиты на вкладке Рецензирование [Review], в группе Изменения [Changes], отжать кнопку Защитить книгу [Protect Workbook]. ПРАКТИКУМ: В открытом файле 04_1 Защита, скрыть лист Защита2. Установить защиту книгис паролем 454. Проверить действие защиты. Снять защиту книги и отобразить лист Защита2. Сохранить изменения в файле. Защита файла от открытия Книгу Excel можно защитить как от открытия, так и от изменений. Если задан пароль для открытия файла, то рабочая книга может быть открыта только в случае, если пароль известен. Если установить защиту от изменений в рабочей книги, то изменения в книге могут быть сохранены только после указания пароля или путем пересохранения рабочей книги под другим именем. В случае, если есть необходимость защитить файл от нежелательного просмотра, имеет смысл поставить защиту от открытия файла. 1. Выбрать Файл [File], Сохранить как [Save As] или нажать клавишу F12 . 2. В окне сохранения нажать кнопку Сервис [Tools], затем Общие параметры [General Options]. 3. Ввести Пароль для открытия файла [Password to open], OK. 4. Повторить пароль, OK. 5. Нажать кнопку Сохранить [Save] и подтвердить замену существующего файла. Отмена защиты происходит аналогично установки защиты от открытия, только вместо ввода пароля происходит его удаление из соответствующего поля. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 80 ПРАКТИКУМ: В открытом файле 04_1 Защита, установить защиту на открытие файла, указав пароль Excel2010. Закрыть файл. Открыть файл Защита, снять пароль. Сохранить изменения в файле и закрыть. Проверка вводимых значений Проверка данных – это инструмент Excel, с помощью которого пользователь может сам сформировать ограничения на ввод данных в ячейки, а также выводить на экран сообщения, предлагающие выполнить правильные действия и уведомлять об ошибках. С помощью проверки данных можно упростить ввод повторяющихся данных, сформировав из них выпадающий список. Можно создать подсказку для ввода данных в ячейку, которое будет появляться при выделении ячейки. Таким образом, можно подсказать пользователю о необходимости ввода тех или иных данных. Установка ограничений на ввод данных Если таблицу только предстоит заполнять, то чтобы избежать случайных опечаток или облегчить ввод повторяющихся значений, можно задать ограничения на вводимые данные. Если таблица уже заполнена данными, то чтобы обнаружить неверные данные, необходимо так же установить ограничения на данные. 1. Выделить диапазон ячеек. 2. На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation]. 3. В диалоговом окне Проверка вводимых значений [Data Validation] на вкладке Параметры, задать Условие проверки – выбрать тип данных и поставить ограничение на вводимые значения. Любое значение [Any value] – нет ограничений на ввод данных. Целое число [Whole number] – ввод только целых чисел. Действительное [Decimal] – ввод как целых, так и дробных чисел. Дата [Date] – ограничение на ввод даты. Время [Time] – ввод только времени. Длина текста [Text length] – ограничение на количество вводимых символов. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 81 Другой [Custom] - предоставляется возможность более широко контролировать ввод данных, условие проверки задается с помощью формулы. Список [List] – ввод данных из предлагаемого списка (нет ограничений по типу данных). Максимум может содержать 32767 элементов. Источник [Source] списка можно: Ввести с клавиатуры, разделяя через точку с запятой ; (разделитель зависит от региональных настроек – может быть запятая , ). Выделить ячейки с любого листа текущей книги. выбрать именованный диапазон с помощью клавиши F3 Применив типы проверок Целое число или Действительное, внести в ячейку можно будет только число. Однако стоит помнить, что даты и время Excel воспринимает и хранит именно как числовые значения, поэтому ввод даты и времени тоже будет разрешен, если он не противоречит остальным условиям проверки. 4. На вкладке Сообщение для ввода [Input Message] ввести подсказку об ограничениях при вводе данных. Подсказка будет отображаться в активной ячейке при условии, что выбран параметр Отображать подсказку, если ячейка является текущей [When input message when cell is selected]. 5. На вкладке Сообщение об ошибке [Error Alert] в поле Вид [Style], выбрать: Останов [Stop] – запрет ввода неверных данных, появляется сообщение об ошибке. Предупреждение [Warning] – ввод неверных данных допускается, при подтверждении пользователем – Да [Yes]. Сообщение [Information] – ввод неверных данных разрешается при подтверждении пользователем - ОК. Сообщение об ошибке будет выводиться на экран, при условии, что установлен флажок Выводить сообщение об ошибке [Show error alert after invalid data is entered]. При вводе неверных данных в ячейку отобразилось сообщение для ввода (подсказка) и предупреждение: Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 82 Поиск неверных данных В случае, если данные в таблице уже заполнены, и необходимо обнаружить некорректно введенные значения, надо сперва задать ограничения на введенные данные, а затем попросить программу обвести все неверные данные. Для этого на вкладке Данные [Data] в группе Работа с данными [Data Tools], открыть список кнопки Проверка данных [Data Validation] и выбрать Обвести неверные данные [Circle Invalid Data]. На текущем листе все неверные данные будут обведены красным цветом. Программа только указывает неверные данные, задача пользователя – внести изменения. При сохранении изменений в файле, красная обводка автоматически убирается с листа. На печать обводка неверных данных не выводится. Для принудительного удаления обводки неверных данных, надо на вкладке Данные [Data] в группе Работа с данными [Data Tools], открыть список кнопки Проверка данных [Data Validation] и выбрать Удалить обводку неверных данных [Clear Validation Circles]. ПРАКТИКУМ: В открытом файле Проверка данных. На листе Командировка выполнить задание Сохранить изменения в файле и закрыть его. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 83 Модуль 5. М АКРОСЫ Макрос – это записанная последовательность действий, которую программа Excel может выполнить по команде пользователя. Макросы удобно использовать для автоматизации каких- либо однообразных, рутинных операций (однотипные расчеты, типовые операции форматирования, копирования или перемещения данных и т.п.). Макрос представляет собой набор команд – программу на языке VBA (язык программирования |