Одновременный просмотр различных частей листа

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

Для разделения листа на области необходимо навести указатель мыши на маркер, расположенный в верхней части вертикальной полосы прокрутки или в правой части горизонтальной полосы прокрутки (рис.27). Когда указатель примет вид двунаправленной стрелки, перетащить его вниз или влево.

Можно также выделить ячейку, относительно которой следует разделить лист, и нажать кнопку Разделить в группе Окно вкладки Вид.

При необходимости линии разделения листа можно перемещать перетаскиванием.

Для прекращения режима разделения листа следует нажать кнопку Разделить в группе Окно вкладки Вид.

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

Обычно требуется закрепить верхнюю строку листа (названия столбцов таблицы) или первый столбец (названия строк таблицы). Для этого надо щелкнуть по кнопке Закрепить области и в появившемся меню (рис. 28.) выбрать соответствующую команду.

Рис. 28. Меню закрепления областей листа

 

Для закрепления произвольной области следует выделить ячейку ниже закрепляемой строки (строк) и правее закрепляемого столбца (столбцов) и в меню выбрать команду Закрепить области.

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

Следует отметить, что одновременно разделить лист и закрепить области листа невозможно.

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

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

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

Поиск и замена данных. Поиск данных можно производить на всем листе или в выделенной области листа, например, только в некоторых столбцах или строках, а также сразу во всей книге. Для этого необходимо в группе Редактирование вкладки Главная щелкнуть по кнопке Найти и выделить и выбрать команду Найти – откроется окно Найти и заменить (рис. 29).

Рис. 29. Окно Найти и заменить

 

При поиске можно использовать подстановочные знаки:

* (звездочка) заменяет любое количество любых символов;

? (знак вопроса) заменяет один любой символ.

В окне Найти и заменить можно указать дополнительные параметры поиска (кнопка Параметры):

· На листе или В книге. Во втором случае будут просматриваться данные всех листов книги (за исключением скрытых);

· По формулам (формулы) или по значениям ячеек (значения). Например, ячейка может показывать значение " 100 ", но содержать формулу =А8*25. При поиске числа " 100 " по формулам эта ячейка найдена не будет. При поиске числа " 100 " по значениям эта ячейка будет найдена.

Поиск также можно выполнять по примечаниям, добавленным к ячейкам.

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

· Ячейка целиком для обеспечения поиска точного совпадения с набором символов, заданным в поле Найти, а не всех слов, в которые искомое слово входит как составная часть. Например, при поиске слова "Град" как имени собственного, установка указанных флажков позволит избежать нахождения таких слов как: град, ограда, награда, и др.;

· Кнопка Найти далее находит ближайшую ячейку, содержащую искомое значение. Найденная ячейка будет выделена, а ее содержимое отображено в строке формул;

· Кнопка Найти все отображает списка всех ячеек листа или книги, содержащих искомое значение.

Искать можно не только конкретные данные, но и элементы оформления ячеек.

Замену данных, так же как и поиск, можно производить на всем листе или в выделенной области листа, например, только в некоторых столбцах или строках, а также сразу во всей книге.

Замена производиться в том же окне Найти и заменить (рис. 30) на закладке Заменить.

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

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

· скопировать диапазон/ячейку;

· во вкладке Главная в группе Буфер обмена щелкнуть по стрелке кнопки Вставить и в появившемся меню выбрать один из способов вставки.

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

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

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

Прежде чем приступать к сортировке, рекомендуется сделать видимыми скрытые строки и столбцы.

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

Простейшая сортировка производится по данным одного столбца (Главная → Редактирование → Сортировка и фильтр).

Сортировка по нескольким столбцам. Сортировку можно осуществлять по нескольким столбцам. Например, если взять столбцы "Отдел" и "Сотрудник", сначала можно отсортировать строки по столбцу "Отдел" (для группировки всех сотрудников одного отдела), а затем по имени (для расположения имен сотрудников в каждом отделе в алфавитном порядке). Можно одновременно осуществлять сортировку по 64 столбцам.

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

Сортировка по формату. Необходимо выделить одну любую ячейку в диапазоне сортируемых данных. Далее выполнить команду Главная → Редактирование → Сортировка и фильтр → Настраиваемая сортировка….

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

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

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

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

· выделить одну любую ячейку в диапазоне, для которого устанавливаются фильтры;

· выбратькоманду Главная → Редактирование → Сортировка и фильтр → Фильтр;

· или Данные → Сортировка и фильтр → Фильтр

В заголовках строк в выделенном диапазоне появятся значки раскрывающегося списка, при нажатии на который появляется окно настройки фильтра (рис. 30).

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

Рис. 30. Окно настройки фильтра

Защита от несанкционированного просмотра и изменения. В Excel предусмотрено несколько уровней защиты, позволяющих управлять доступом к данным Excel и их изменением:

· можно ограничить доступ к файлу, например несанкционированное открытие файла и/или сохранение в нем изменений;

· можно применить защиту к элементам книги, ограничив просмотр отдельных листов и/или изменение данных на листе;

· можно защитить элементы листа, например ячейки с формулами, запретив доступ к ним или предоставить доступ к определенным диапазонам.

Все уровни защиты являются не взаимоисключающими, а взаимодополняющими.

Защита файла. Пароль для защиты файла от несанкционированного открытия/изменения устанавливают при сохранении файла. Для этого необходимо нажать Кнопку Office, а затем выбрать команду Сохранить как. В окне Сохранение документа нажать кнопку Сервис и выбрать команду Общие параметры. В окне Параметры сохранения (рис. 31) ввести пароли в поля Пароль для открытия и/или Пароль для открытия нажмите кнопку ОК.

Рис. 31. Установка пароля на файл

 

Защита ячеек. По умолчанию все ячейки листа являются защищаемыми (Главная → Ячейки → Формат → Формат ячеек → Защита должен быть установлен флажок Защищаемая ячейка). В окне Защита листа (Рецензирование → Изменения → Защитить лист) можно настроить защиту как данных на листе, так и структуры листа.

Защита книги. Можно защищать от изменений структуру книги и окно книги (Рецензирование → Изменения → Защитить книгу). Под защитой структуры книги от изменений понимается запрещение действий с листами книги. Под защитой окна книги понимается запрет перемещения, изменения размеров, скрытие, показ и закрытие окон.

Задание по работе

 

Документы сохранять с указанными ниже именами в своей папке на диске D.

 

Задание 1

1. Из папки Задания Excel 2007 на диске D, скопируйте в свою папку файл Заготовка и переименуйте его в Прайс. В получившемся документе на Листе1 должен содержаться список товаров.

2. В книге Прайс дополнительно создайте листы: Ноутбуки, Компьютеры, Комплектующие, Мониторы, Мультимедиа, Периферия, Разное. С Листа1 переместить соответствующую информацию на листы:

· На лист Ноутбуки – вся информация о ноутбуках;

· На лист Компьютеры – информация о компьютерах (готовые решения);

· На лист Комплектующие – процессоры, системы охлаждения, память, оптические накопители, TV-тюнеры, звуковые карты, корпуса, FDD, жесткие диски, блоки питания, устройства ввода;

· На лист Мониторы – информация о мониторах;

· На лист Мультимедиа – колонки, наушники, Web-камеры, МР3-плееры;

· На лист Периферия – принтеры, МФУ, сканеры, ИБП;

· На лист Разное – всё остальное с Листа1.

3. Отформатируйте все листы:

· На каждом листе должен быть заголовок таблицы (Код, Тип, Наименование товара, Цена), Шрифт Times New Roman 8пт, полужирный;

· Таблица не должна выходить за ширину экрана (добиться этого с помощью изменения ширины столбцов и настройки переноса текста в ячейках);

· Цвет ярлычков листов поочередно: оранжевый – чёрный.

· На всех листах закрепите верхнюю строку с заголовками.

4. Лист1 удалите и сохраните документ.

 

Задание 2

1. С помощью фильтра отберите информацию только о мониторах Samsung 22”. Получившийся результат скопируйте в новую книгу.

2. К таблице примените стиль оформления Светлый 2 и затем преобразуйте таблицу в диапазон.

3. Постройте гистограмму цена-модель монитора. Оформите гистограмму (название, подписи данных, легенда, названия осей и т.д.).

4. Сохраните книгу под именем Мониторы.

 

Задание 3

1. С помощью фильтра отберите информацию только о памяти DDR3. Получившийся результат скопируйте в новую книгу.

2. В таблице с помощью условного форматирования отберите 10 товаров с минимальными ценами.

3. Сделайте сортировку по цвету ячейки.

4. Сохраните книгу под именем Память.

 

Задание 4

1. В новой книге создайте таблицу Сравнение ноутбуков (таблица 22).

 

Таблица 22- Сравнение ноутбуков

 

2. Используя функции Максимум, Минимум, Среднее, Число и ссылки на книгу Прайс заполните таблицу. К таблице примените стиль Светлый 14.

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

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

5. Оформите диаграммы и сохраните документ под именем Ноутбуки.

 

Задание 5

1. С помощью фильтра отберите информацию только о USB Flash. Получившийся результат скопируйте в новую книгу.

2. Закрепите верхнюю строку с заголовками.

3. К столбцу Цены примените условное форматирование Двухцветная цветовая шкала.

4. Отсортируйте столбец Цены по убыванию.

5. Сохраните книгу под именем USB Flash.

 

Задание 6

1. С помощью фильтра отберите информацию о принтерах. Получившийся результат скопируйте в новую книгу. К таблице примените стиль форматирования Светлый 9. Закрепите верхнюю строку с заголовками.

2. В таблицу добавьте столбцы: Цена, руб.; Сумма НДС, руб.; Цена с НДС, руб.; Цена со скидкой, руб.; Примечания.

3. Отформатируйте столбцы: денежный формат в рублях/долларах, количество знаков после запятой.

4. На Листе2 введите в ячейку А1 – Курс USD, в ячейку В1 – курс.

5. Заполните таблицу: НДС – 20%, скидка только на товары со стоимостью свыше 3 000 000 руб. В столбце Примечание для товаров со скидкой должен появляться текст «Скидка!» (с помощью функции ЕСЛИ). Текст красного цвета, полужирный.

6. Сохраните книгу под именем Принтеры_1.

 

Задание 7

1. Откройте документ Принтеры_1. Скопируйте таблицу.

2. Откройте новую книгу и с помощью специальной вставки вставьте значения заранее скопированной таблицы (Главная → Буфер обмена → Вставить → Вставить значения).

3. Сохраните книгу под именем Принтеры_2.

 

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

1. Порядок закрепления областей листа.

2. Применение специальной вставки.

3. Изменение ширины столбцов и высоты строк.

4. Форматирование ячеек (заливка, шрифт, перенос текста, выравнивание, границы, направление текста).

5. Применение числовых и текстовых фильтров.

6. Настраиваемая сортировка (по значению, цвету ячейки, цвету шрифта).

7. Защита ячеек и файлов от просмотра и изменения.

ОГЛАВЛЕНИЕ

 

ВВЕДЕНИЕ 3

ЛАБОРАТОРНАЯ РАБОТА № 1. Интерфейс Excel 2007.

Структура документа. Общие приемы работы с Excel. 4

ЛАБОРАТОРНАЯ РАБОТА № 2. Форматирование данных

в Excel 2007. 15

ЛАБОРАТОРНАЯ РАБОТА № 3.Вычисления в MS Excel. 23

ЛАБОРАТОРНАЯ РАБОТА № 4. Ссылки в MS Excel. 33

ЛАБОРАТОРНАЯ РАБОТА № 5. Создание таблиц.

Условное форматирование. 39

ЛАБОРАТОРНАЯ РАБОТА № 6. Построение диаграмм

в MS Excel. 46

ЛАБОРАТОРНАЯ РАБОТА № 7. Отображение документа.

Работа с данными. Защита данных. 52