Связь между рабочими листами

MS Excel - ПРАКТИЧЕСКАЯ ЧАСТЬ 3. Сортировка и фильтрация данных. Обмен данными между Excel и Word

I. ОСНОВНЫЕ ПРИЕМЫРАБОТЫСО СПИСКАМИ

1. Поиск данных

Для поиска данных выполните следующие действия:

• выделите первую ячейку рабочего листа;

• войдите в меню Правка → Найти;

• в диалоговом окне Найти в поле Что введите объект поиска (число, слово, пробел и т. п.);

• в списке Просматривать выбрать направление поиска;

• в списке Область поиска выбрать тип данных для поиска;

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

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

• для перехода к нужной ячейке следует щелкнуть левой кнопкой мыши по ее записи в списке;

• для более детального поиска во вкладке Найти диалогового окна Найти и заменить можно нажать кнопку Параметры;

• для завершения поиска щелкнуть на кнопке Закрыть.

Замена данных

Чтобы заменить данные в таблице, выполните следующие действия:

• выделите первую ячейку рабочего листа;

• войдите в меню Правка → Заменить;

• во вкладке Заменить диалогового окна Найти и заменить в поле Найти ввести заменяемые данные, а в поле Заменить на — заменяющие данные;

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

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

• для более детального поиска и замены данных во вкладке Заменить диалогового окна Найти и заменить можно нажать кнопку Параметры;

• для завершения поиска щелкнуть на кнопке Закрыть.

Сортировка данных

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

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

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

Работа с Автофильтром

Установка Автофильтра: <выделить любую ячейку таблицы> → Данные → Фильтр → Автофильтр. После этого команда Автофильтр в меню Данные → Фильтр будет отмечена галочкой. Это означает, что фильтр включен и работает. После установки автофильтра в названиях столбцов таблицы появятся значки рас­крывающихся списков.

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

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

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

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

Всего существует 12 вариантов условий.

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

Для текстовых значений можно использовать следующие ва­рианты условий: равно, не равно, содержит, не содержит, начина­ется с, не начинается с, заканчивается на, не заканчивается на.

Одновременно можно применять два условия отбора, объе­диняя их союзом И, если требуется, чтобы данные удовлетворя­ли обоим условиям, или союзом ИЛИ, если требуется, чтобы данные удовлетворяли хотя бы одному из них.

Снятие Автофильтра: Данные → Фильтр → Автофильтр.

Связь между рабочими листами

Данные с другого листа или из другой книги Excel можно пе­ренести на текущий лист с помощью специальной вставки: <вы­делить ячейку> → Копировать → <перейти на текущий лист> → Правка → Специальная вставка → Вставить связь. Редактирова­ние связанных данных осуществляется на исходном листе. В формуле можно использовать данные, взятые с другого листа илииз другой книги Excel, для этого при наборе формулы нуж­но открыть лист (книгу), щелкнуть по ячейке и нажать в строке формул кнопку Ввод.

Вид формулы с данными с другого листа: =АЗ+ВЗ+Лист2!А4

Вид формулы с данными из другой книги: =А4*[Книга2.xls]Лист1!$А$6

6. Печать документа вExcel

Просмотр данных на страницах: Вид → Разметка страницы.

Установка размера бумаги, полей, колонтитулов: Файл → Па­раметры страницы.

Печать выделенных листов: Файл → Печать.

Если выделена диаграмма, то печатается только диаграмма.

II. ЗАДАНИЯ

Задание 1. Создание таблицы и проведение сортировки

1. Создайте новую книгу. Лист1 переименуйте в Автомобили. Создайте таблицу по образцу (рис. 1).

	А	В	С	D	Е
	Модель	Цена	Год	Пробег	Цвет
	Волга 21	$600		85 000	Серый
	ВАЗ 21011	$500		150 000	Оранжевый
	Волга 2410	$1050		35 500	Зеленый
	Волга 2410	$500			Черный
	Chrysler New Yorker	$5500			Шоколад
	Nissan Laurel	$600			Темно-серый
	Волга 2410	$750		140 000	Серый
	Mazda Titan	$7200		190 000	Белый
	ВАЗ 21093	$1550		75 000	Белый
	ВАЗ 21053	$900		93 000	Белый
	ВАЗ 21063	$950		69 000	Голубой
	Ford Econoline 150	$9900		87 000	Черный
	Mercedes 320 / WHO	$12 000		244 660	Серый металлик
	Иж2715	$550		90 000	Кофейный
	Москвич 2141	$1500		40 500	Вишня
	ВАЗ 21093	$1750		64 000	Красный
	ВАЗ 21093	$1900		70 000	Красный
	Mazda 323	$3400		215 000	Красный
	Cadillac STS	$6000			Черный
	ВАЗ 21063	$400		120 000	Бежевый

Рис. 1. Образец таблицы для задания 1

2. Лист2 переименуйте в Модель. Скопируйте таблицу на лист Модель и проведите сортировку по возрастанию (от А до Я) по столбцу Модель: <выделите ячейку А1 (заголовок столбца Модель)> → <щелкните по кнопке Сортировка по возрастанию >

3. ЛистЗ переименуйте в Цена, скопируйте таблицу с листа Автомобили. Проведите по столбцу Цена сортировку по возраста­нию, а потом по убыванию. Обратите внимание, что значение по строкам остается неизменным, например, для модели Вол­га 21 цена, год, пробег и цвет не меняются, независимо от того, на какой позиции в списке оказывается эта модель.

4. Добавьте в книгу новый лист и переместите его после лис­та Цена, назовите новый лист Пробег, скопируйте на него табли­цу с листа Автомобили. На листе Пробег проведите сортировку по пробегу (сортировка по убыванию).

5. Добавьте в книгу новый лист и переместите его после лис­та Пробег, назовите новый лист Цвет, скопируйте на него табли­цу с листа Автомобили. На листе Цвет проведите сортировку по цвету (сортировка по возрастанию).

6. Добавьте в книгу новый лист и переместите его после лис­та Цвет, назовите новый лист По трем, скопируйте на него таб­лицу с листа Автомобили. На листе По трем проведите сортиров­ку по трем столбцам: цена, год, пробег. Для этого: <выделите любую ячейку заполненной таблицы> →Данные → Сортиров­ка → <из списка Сортировать по выберите столбец Цена, по­ставьте переключатель по возрастанию>→ <из списка Затем по выберите столбец Год, поставьте переключатель по возрастанию> → <из списка В последнюю очередь, по выберите столбец Пробег, поставьте переключатель по возрастанию > → ОК.

Таким образом, сначала автомобили будут отсортированы по цене, потом автомобили с одинаковой ценой будут отсортирова­ны по году выпуска, и, наконец, автомобили с одинаковой це­ной и годом выпуска будут отсортированы по пробегу.

Задание 2. Проведение выборки данных с помощью автофильтра

1. Добавьте в книгу новый лист и переместите его после лис­та По трем, назовите новый лист Автофильтр, скопируйте на него таблицу с листа Автомобили. На листе Автофильтр выдели­те любую ячейку таблицы и включите автофильтр: Данные → Фильтр → Автофильтр.

2. Проведите выборку по столбцу Модель — значение равно ВАЗ 21093, для этого: щелкните по значку раскрывающегося списка столбца Модель и выберите значение ВАЗ 21093. В ячей­ке А23 напечатайте заголовок Модель ВАЗ 21093, скопируйте от­фильтрованную таблицу и вставьте после этого заголовка.

Модель ВАЗ 21093
Модель	Цена	Год	Пробег	Цвет
ВАЗ 21093	$1550		75 000	Белый
ВАЗ 21093	$1750		64 000	Красный
ВАЗ 21093	$1900		70 000	Красный

Рис. 2. Копия отфильтрованной таблицы

Снимите фильтрацию с основной таблицы: щелкните по значку раскрывающегося списка столбца Модель и выберите параметр Все. Аналогичным образом копируйте результаты фильт­рации и во всех других заданиях.

3. Проведите выборку по столбцу Год — значение 1993.

4. Проведите выборку по столбцу Цвет — значение черный.

5. Проведите выборку по условию автомобили с годом выпуска не ранее 1993,для этого: <щелкните по значку раскры­вающегося списка столбца Год и выберите параметр Условие> → <в диалоговом окне Пользовательский автофильтр в раскрываю­щемся списке с названием столбца Год выберите условие отбора больше или равно, а в раскрывающемся списке справа — выбери­те 1993> → ОК. Скопируйте отфильтрованную таблицу ниже ос­новной, а в основной таблицу снимите фильтрацию.

6. Проведите выборку всех моделей автомобилей, за исклю­чением моделей ВАЗ и Волга, для этого: <щелкните по значкураскрывающегося списка столбца Модель и выберите параметр Условие> → <в раскрывающемся списке с названием столбца Модель выберите условие не начинается на, а в поле на­против напечатайте значение ВАЗ> → <поставьте переключатель И > → <из нижнего списка также выберите условие не начинается на, а вполе напротив напечатайте значение Волга> → ОК.

7. Проведите выборку автомобилей только белого и только черного цвета, для этого: <щелкните по значку раскрывающегося списка столбца Цвет и выберите параметр Условие> → <в раскрывающемся списке с названием столбца Цвет выберите условие равно, а из списка напротив выберите значение белый> → <поставьте переключатель ИЛИ> → <из нижнего списка также выберите условие равно, а из списка напротив выберите значение черный> → ОК.

Задание 3. Копирование данных из окна Excel в окно Word

1. Откройте Word и наберите заголовок Простое копирование таблицы Excel в документ Word.

2. Откройте любую книгу Excel с заполненной таблицей данных.

3. Выделите только таблицу и скопируйте ее в Буфер обмена (Правка → Копировать).

4. Активизируйте документ Word и установите текстовый курсор в пустую строку после заголовка.

5. Вставьте таблицу из Буфера обмена (Правка → Вставить).

Таблица Excel преобразовалась в таблицу Word и редактировать ее в документе Word можно только средствами Word. При изменении таблицы в Excel ее придется заново копировать в документ Word.

Задание 4. Копирование данных из Excel в Word с установкой связи

1. В документе Word с новой строки наберите второй заголовок Копирование таблицы Excel в документ Word с установкой связи.

2. Откройте любую книгу Excel с заполненной таблицей данных.

3. Выделите только таблицу и скопируйте ее в Буфер обмена (Правка → Копировать).

4. Активизируйте документ Word и установите текстовый курсор в пустую строку после второго заголовка.

5. Выполните команду Правка → Специальная вставка → <выберите режим Лист MS Excel объект> → <включите кнопку выбора Связать> → ОК.

6. Проверьте установленную связь, для этого вExcel измени­те какое-нибудь значение и посмотрите, как изменились данные в документе Word.

7. Сохраните документ Word под именем Обменное, закройте Excel.

8. Отредактируйте вторую таблицу в документе Обмен, для этого: <сделайте двойной щелчок на таблице (в Excel откроется файл, содержащий таблицу)> → <измените значение в какой-либо ячейке> → <сохраните изменения в таблице и закройте Excel >.

9. Активизируйте документ Обменное и посмотрите как изменилось значение во второй таблице.

Задание 5. Внедрение таблицы Excel в документ Word

1. В документе Обменное с новой строки наберите третий заголовок Внедрение в документ Word таблицы Excel.

2. Выполните команду Вставка → Объект → <на вкладке Создание выберите режим Лист MS Excel> → OK.

В результате в окне Word появится фрагмент таблицы Excel в штриховой рамке, меню и панели Excel.

3. Создайте любую таблицу и сделайте щелчок вне штрихо­вой рамки (вы вышли из Excel в Word).

4. Отредактируйте третью таблицу в документе Обмен, для этого: <сделайте двойной щелчок на таблице (появится штриховая рамка, меню и панели Excel)> → <измените таблицу> → <сделайте щелчок вне штриховой рамки>.

5. Сохраните измененный документ Word под тем же именем Обменное.