Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
3.3. Работа со списками. Обмен данными между Excel и Word. Особенности печати в Excel
Цель занятия: 1) научиться работать со списками данных (вводить, осуществлять поиск, замену, сортировку и выбор данных); 2) изучить способы обмена данными между Excel и Word; 3) познакомиться с особенностями печати в программе Excel.
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ. Списки. Сортировка. Выбор данных. Обмен данными между Word и Excel. Особенности печати в Excel
Списки
Существует два основных способа организации данных на листе: таблица и список. При организации данных в виде таблицы формируются строки и столбцы с записями, для которых в ячейку на пересечении строки и столбца помещаются данные. В виде таблиц обычно организуют данные, подлежащие вычислениям.
Другой способ организации данных – список. Список – это набор строк листа, содержащий однородные данные; первая строка в этом списке содержит заголовки столбцов, остальные строки содержат однотипные данные в каждом столбце. Список называют также базой данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы – полями.
В виде списка можно представлять как данные информационного характера (списки товаров, номера телефонов, адреса и т.п.), так и данные, подлежащие вычислениям.
Списочный способ представления данных обеспечивает удобство при сортировках, выборках, подведении итогов и т.п.
Основные операции при работе со списком:
1) Создание списка (автоввод);
2) Поиск и замена данных;
3) Сортировка данных;
4) Отбор (фильтрация) данных.
Автоввод – это средство Excel, позволяющее ускорить ввод повторяющихся элементов списка, автоматически завершая ввод текста. При наборе первых символов текста Автоввод предложит один из вариантов завершения слова или словосочетания уже вводившегося в это поле. Если предложенный вариант не подходит, продолжайте ввод, не удаляя символы, введенные автоматически. Если предложенный вариант подходит, нажмите Enter для перехода к следующей записи.
Поиск и замену данных можно производить на всем листе или в выделенной области листа, например, только в некоторых столбцах или строках, а также сразу во всей книге. Поиск (замену) данных выполняют соответствующими командами вкладки Главная группы Редактирование: Найти и выделить.
Сортировка – расположение данных на листе в определенном порядке. Чаще всего сортируются строки с данными, но при необходимости можно сортировать и столбцы. При сортировке по строкам упорядочивается расположение столбцов в списке. При сортировке по столбцам упорядочивается расположение строк.
Как правило, при сортировке упорядочиваются (меняются местами) целиком строки или столбцы, но можно сортировать и отдельные ячейки.
Сортировку можно производить как по возрастанию, так и по убыванию. При желании можно сортировать данные в соответствии с собственным порядком сортировки.
Поскольку при сортировке Microsoft Excel автоматически определяет связанный диапазон данных, сортируемый диапазон не должен иметь пустых столбцов. Наличие пустых строк допускается, но не рекомендуется.
При сортировке заголовки столбцов обычно не сортируются вместе с данными, но сортируемый диапазон может и не иметь заголовков столбцов.
Прежде чем приступать к сортировке, следует сделать видимыми скрытые строки и столбцы.
Для выбора данных можно использовать операции поиска данных, формы, некоторые функции, а также фильтр. Фильтр – это средство Excel, помогающее делать выбор из списка тех значений, которые отвечают определенному условию. Критерий (условие) фильтра – это логические условия или значения, по которым производится выбор. Результат работы фильтра – это список, записи которого удовлетворяют условию.
В отфильтрованном списке отображаются только строки, отвечающие условиям, заданным для столбца. В отличие от сортировки, фильтр не меняет порядок записей в списке. При фильтрации временно скрываются строки, которые не требуется отображать.
Строки, отобранные при фильтрации, можно редактировать, форматировать, создавать на их основе диаграммы, выводить их на печать, не изменяя порядок строк и не перемещая их.
Обмен данными между Excel и Word
Существует три способа обмена данными между Excel и Word:
Особенности печати документа в Excel
В книге Excel каждый лист может содержать несколько страниц формата А4. При работе с данными на листе не отображаются границы страниц, т.е. вы не можете заранее сказать, умещается ли ваша таблица на странице или нет. Если данные не помещаются на странице, то Excel автоматически переносит их на следующую страницу. Так что при выводе на печать неподготовленной книги вы можете получить несколько страниц, на которых находятся фрагменты таблиц и диаграмм. Поэтому перед печатью нужно перейти в режим разметки страницы, который показывает привычный по работе в приложении Word вид документа. Перед выводом на печать можно перейти и в режим предварительного просмотра, чтобы еще раз убедиться в том, что все таблицы умещаются на страницах.
Еще одной особенностью распечатывания данных из Excel является то, что по умолчанию печатается не вся книга, а только активный лист. Информация с этого листа может располагаться на нескольких страницах формата А4. Но с помощью команды Печать можно вывести на принтер и отдельные листы, и выделенный диапазон и всю книгу.
Также как и в приложении Word в Excel можно вставлять разрывы страниц, и печатать только выделенный диапазон. Но нужно иметь в виду, что если на листе выделена диаграмма, то и печататься будет только эта диаграмма, причем она автоматически будет увеличена до размера страницы формата А4 в альбомной ориентации.
Настройка масштаба печати. По умолчанию в Excel установлен масштаб печати 100 %, это означает, что размеры шрифтов при печати будут такими, какие пользователь выбрал при создании таблицы. Но если таблица большая и не помещается на листе нужного формата, то Excel поможет в этом – автоматически уменьшив или увеличив шрифт на листе. Минимально возможный масштаб печати 10%, максимально возможный – 400%.
В приложении Excel можно подобрать необходимый масштаб печати так, чтобы таблица размещалась на заданном числе страниц.
Можно также указать программе, чтобы небольшая таблица или диаграмма печаталась в центре страницы, при этом пользователю не нужно заботиться об увеличении полей и центрировании объектов вручную.
ПРАКТИЧЕСКАЯ ЧАСТЬ. Сортировка и фильтрация данных. Обмен данными между Excel и Word
I Основные приемы работы со списками
Сортировка данных
Сортировка по одному столбцу: выделить любую ячейку столбца, на вкладке Главная в группе Редактирование нажать кнопку Сортировка и фильтр (или щелкнуть правой кнопкой мыши любую ячейку столбца и выбрать команду Сортировка), в списке выбрать способ сортировки Сортировка от А до Я/ Сортировка от Я до А/Настраиваемая сортировка и т.д.
Сортировка по нескольким столбцам
Для того чтобы произвести сортировку по двум или трем столбцам, следует выделить любую ячейку сортируемого диапазона и выполнить команду Настраиваемая сортировка. В окне Сортировка добавить нужное количество сортируемых столбцов (кнопка Добавить уровень). В раскрывающихся списках Сортировать по, Затем по нужно выбрать последовательность столбцов, по данным которых сортируется список. При необходимости можно указать объекты (списки Сортировка) и направление (списки Порядок) сортировки по каждому из столбцов.
Установка Автофильтра: <выделить любую ячейку таблицы> → на вкладке Главная в группе Редактирование нажать кнопку Сортировка и фильтр → Фильтр (или щелкнуть правой кнопкой мыши любую ячейку столбца и выбрать команду Фильтр), в названиях столбцов таблицы появятся значки раскрывающихся списков.
Выборки данных с использованием фильтра: щелкнуть по значку раскрывающегося списка соответствующего столбца и выбрать способ сортировки, значения или команду Числовые (текстовые) фильтры для задания условий выборки. Исходная таблица исчезает, остаются только строки, выбранные из таблицы с помощью выборки.
Если данные уже отфильтрованы по одному из столбцов, при использовании фильтра для другого столбца будут предложены только те значения, которые видны в отфильтрованном списке.
Выборка по условию: щелкнуть по значку раскрывающегося списка соответствующего столбца и выбрать команду Числовые (текстовые) фильтры, в раскрывшемся списке выбрать подходящее условие (равно…, не равно… и т.д.), появится диалоговое окно Пользовательский автофильтр. В диалоговом окне Пользовательский автофильтр в раскрывающемся списке с названием столбца, по которому производится отбор данных, следует выбрать вариант условия отбора, а в раскрывающемся списке справа – выбрать из списка или ввести с клавиатуры значение условия.
Всего существует 12 вариантов условий.
Для числовых значений можно использовать следующие варианты условий: равно, не равно, больше, меньше, больше или равно, меньше или равно.
Для текстовых значений можно использовать следующие варианты условий: равно, не равно, содержит, не содержит, начинается с, не начинается с, заканчивается на, не заканчивается на.
Одновременно можно применять два условия отбора, объединяя их союзом И, если требуется, чтобы данные удовлетворяли обоим условиям, или союзом ИЛИ, если требуется, чтобы данные удовлетворяли хотя бы одному из них.
Восстановление списка (снятие фильтрации): щелкнуть по значку раскрывающегося списка соответствующего столбца и нажать кнопку Выделить все (или Снять фильтр с…).
Снятие Автофильтра: на вкладке Главная в группе Редактирование нажать кнопку Сортировка и фильтр → Фильтр (или щелкнуть правой кнопкой мыши любую ячейку столбца и выбрать команду Фильтр)
Данные с другого листа или из другой книги Excel можно перенести на текущий лист с помощью специальной вставки: <щелкнуть ячейку правой кнопкой мыши> → Копировать → <перейти на текущий лист> → <на вкладке Главная в группе Буфер обмена нажать стрелку кнопки Вставить> → выбрать команду Специальная вставка → в окне Специальная вставка нажать кнопку Вставить связь. Редактирование связанных данных осуществляется на исходном листе. В формуле можно использовать данные, взятые с другого листа или из другой книги Excel, для этого при наборе формулы нужно открыть лист (книгу), щелкнуть по ячейке и нажать в строке формул кнопку Ввод.
Вид формулы с данными с другого листа: =A3+B3+Лист2!A4
Вид формулы с данными из другой книги: =A4*[Книга2.xls]Лист1!$A$6
Копирование данных из книги Excel в документ Word
Копирование данных из Excel в Word с установкой связи
Проверьте установленную связь, для этого, в книге Excel измените, какое-нибудь значение и посмотрите, как изменились данные в документе Word.
Внедрение таблицы Excel в документ Word
В текстовом процессоре Word выполните команду: вкладка Вставка группа Текст → Вставить объект → <в диалоговом окне Вставка объекта выберите объект Лист Microsoft Office Excel> → ОК.
В результате в окне текстового процессора Word появится фрагмент таблицы Excel в рамке, а также лента приложения Excel.
Создайте любую таблицу и сделайте щелчок вне штриховой рамки, таким образом, вы выйдите из режима
редактирования внедренного объекта и вернетесь к ленте приложения Word.
Печать документа в Excel
Просмотр данных на страницах: На вкладке Вид в группе Режимы просмотра книги нажмите кнопку Разметка страницы.
Предварительный просмотр: Кнопка Office → Печать → Предварительный просмотр.
Поля страницы:
Вкладка Разметка страницы → группа Параметры страницы → Поля. В списке Поля можно выбрать один из стандартных вариантов полей страницы (например, обычное, узкое и др.). Если нужно указать точные размеры, выбрать команду Настраиваемые поля и в группе Поля указать точные размеры полей.
Размер страницы:
Из выпадающего списка Размер выбирается размер печатной страницы (например, А4, А5 или Другие размеры страницы для произвольного размера.).
Эти параметры можно задать в диалоговом окне Параметры страницы, которое можно вызвать нажатием на кнопку в правом нижнем углу раздела Параметры страницы.
Выбрав режим предварительного просмотра (кнопка Office → Печать → Предварительный просмотр → Параметры страницы), можно установить дополнительные параметры печати:
1) Центрирование на странице
По умолчанию таблица размещается в левом верхнем углу страницы. Чтобы выровнять таблицу относительно полей страницы следует выбрать установить соответствующие флажки (горизонтально и вертикально) в разделе Центрировать на странице закладки Поля. Центрирование возможно только в том случае, если таблица размещена в левом верхнем углу листа. Здесь же можно задать произвольные поля страницы.
2) Создание и редактирование колонтитулов
Колонтитулы показаны в окне Excel только в режиме отображения Разметка страницы и в режиме предварительного просмотра. В этом случае на ленте появляется вкладка Работа с колонтитулами – Конструктор.
Колонтитулы выбирают и создают на вкладке Колонтитулы. Колонтитул в книге Excel состоит из трех частей. Соответственно в диалоговом окне имеет три поля для ввода текста: Слева, В центре и Справа. Данные вводятся и оформляются в каждом поле независимо друг от друга.
3) Печать названий строк и столбцов таблицы
При многостраничной печати по умолчанию, при разделении таблицы на несколько страниц, названия строк таблицы печатаются только на левых страницах, а названия столбцов таблицы – только на верхних страницах. Таким образом, на многих страницах становится непонятно, к каким строкам и столбцам относятся те или иные данные. Для печати названий строк таблицы на каждой странице следует на закладке Лист (кнопка Office → Печать → Предварительный просмотр. Вкладка Предварительный просмотр → группа Печать →кнопка Параметры страницы) нажать на кнопку с красной стрелкой напротив поля сквозные строки и щелкнуть по любой ячейке строки, которую следует печатать на каждой странице (обычно строка 1). Для печати названий столбцов таблицы на каждой странице следует нажать на кнопку с красной стрелкой в поле сквозные столбцы и щелкнуть по любой ячейке столбца, который следует печатать на каждой странице (обычно столбец А).
II Задания
Задание 1. Создание таблицы и проведение сортировки
1. Создайте новую книгу. Лист1 переименуйте в Автомобили. Создайте таблицу по образцу. В столбце Цена задайте денежный формат ячеек.
|
|
A |
B |
C |
D |
E |
|
1 |
Модель |
Цена |
Год |
Пробег |
Цвет |
|
2 |
Волга 21 |
$600 |
1963 |
85000 |
Серый |
|
3 |
ВАЗ 21011 |
$500 |
1980 |
150000 |
Оранжевый |
|
4 |
Волга 2410 |
$1 050 |
1980 |
35500 |
Зеленый |
|
5 |
Волга 2410 |
$500 |
1981 |
53600 |
черный |
|
6 |
Chrysler New Yorker |
$5 500 |
1988 |
181142 |
шоколад |
|
7 |
Nissan Laurel |
$600 |
1989 |
665383 |
темно-серый |
|
8 |
Волга 2410 |
$750 |
1989 |
140000 |
серый |
|
9 |
Mazda Titan |
$7 200 |
1989 |
190000 |
белый |
|
10 |
ВАЗ 21093 |
$1 550 |
1990 |
75000 |
белый |
|
11 |
ВАЗ 21053 |
$900 |
1991 |
93000 |
белый |
|
12 |
ВАЗ 21063 |
$950 |
1991 |
69000 |
голубой |
|
13 |
Ford Econoline 150 |
$9 900 |
1992 |
87000 |
черный |
|
14 |
Mercedes 320 / W140 |
$12 000 |
1992 |
244660 |
серый металлик |
|
15 |
Иж 2715 |
$550 |
1993 |
90000 |
кофейный |
|
16 |
Москвич 2141 |
$1 500 |
1993 |
40500 |
вишня |
|
17 |
ВАЗ 21093 |
$1 750 |
1993 |
64000 |
красный |
|
18 |
ВАЗ 21093 |
$1 900 |
1993 |
70000 |
красный |
|
19 |
Mazda 323 |
$3 400 |
1993 |
215000 |
красный |
|
20 |
Cadillac STS |
$6 000 |
1993 |
0 |
черный |
|
21 |
ВАЗ 21063 |
$400 |
1994 |
120000 |
бежевый |
Рис. 3.12. Образец таблицы для задания 1
2. Лист2 переименуйте в Модель. Скопируйте таблицу на лист Модель и проведите сортировку по возрастанию (от А до Я) по столбцу Модель: <выделите ячейку А1 (заголовок столбца Модель)> → <на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр (щелкните правой кнопкой мыши заголовок столбца Модель)> → Сортировка от А до Я.
3. Лист3 переименуйте в Цена, скопируйте таблицу с листа Автомобили. Проведите по столбцу Цена сортировку по возрастанию, а потом по убыванию. Обратите внимание, что значение по строкам остается неизменным, например, для модели Волга 21 цена, год, пробег и цвет не меняются, независимо от того, на какой позиции в списке оказывается эта модель.
4. Добавьте в книгу новый лист и переместите его после листа Цена, назовите новый лист Пробег, скопируйте на него таблицу с листа Автомобили. На листе Пробег проведите сортировку по пробегу (сортировка по убыванию).
5. Добавьте в книгу новый лист и переместите его после листа Пробег, назовите новый лист Цвет, скопируйте на него таблицу с листа Автомобили. На листе Цвет проведите сортировку по цвету (сортировка по возрастанию).
6. Добавьте в книгу новый лист и переместите его после листа Цвет, назовите новый лист По трем, скопируйте на него таблицу с листа Автомобили. На листе По трем проведите сортировку по трем столбцам: цена, год, пробег. Для этого: <выделите любую ячейку заполненной таблицы> → <на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр (щелкните правой кнопкой мыши любую ячейку заполненной таблицы)> → Настраиваемая сортировка → <в окне Сортировка дважды нажмите кнопку Добавить уровень> → <из списка Сортировать по выберите столбец Цвет, поставьте переключатели значения, по возрастанию (от А до Я) > → <из первого списка Затем по выберите столбец Год, поставьте переключатели значения, по возрастанию> → <из второго списка Затем по выберите столбец Пробег, поставьте переключатели значения, по возрастанию> → ОК.
Таким образом, сначала автомобили будут отсортированы по цене, потом автомобили с одинаковой ценой будут отсортированы по году выпуска, и, наконец, автомобили с одинаковой ценой и годом выпуска будут отсортированы по пробегу.
Задание 2. Проведение выборки данных с помощью автофильтра
1. Добавьте в книгу новый лист и переместите его после листа По трем, назовите новый лист Автофильтр, скопируйте на него таблицу с листа Автомобили. На листе Автофильтр выделите любую ячейку таблицы и включите автофильтр: <на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр)> → Фильтр.
2. Проведите выборку по столбцу Модель – значение равно ВАЗ 21093, для этого: щелкните по значку раскрывающегося списка столбца Модель, снимите переключатель Выделить все и выберите значение ВАЗ 21093. В ячейке А23 напечатайте заголовок Модель ВАЗ 21093, скопируйте отфильтрованную таблицу и вставьте с установкой связи после этого заголовка. Установка связи при копировании необходима для того, чтобы все изменения значений в исходной таблице отображались бы и в отфильтрованной таблице. Копирование (вставка) с установкой связи осуществляется следующим образом:
|
Модель ВАЗ 21093 |
||||
|
Модель |
Цена |
Год |
Пробег |
Цвет |
|
ВАЗ 21093 |
$1 550 |
1990 |
75000 |
Белый |
|
ВАЗ 21093 |
$1 750 |
1993 |
64000 |
Красный |
|
ВАЗ 21093 |
$1 900 |
1993 |
70000 |
Красный |
Рис. 14. Копия отфильтрованной таблицы
Снимите фильтрацию с основной таблицы: щелкните по значку раскрывающегося списка столбца Модель и установите переключатель Выделить все → ОК. Измените в исходной таблице несколько значений и убедитесь, что в отфильтрованной таблице значения также изменились.
3. Проведите выборку по столбцу Год – значение 1993. Скопируйте отфильтрованную таблицу и вставьте ее с установкой связи после соответствующего заголовка.
4. Проведите выборку по столбцу Цвет – значение черный. Скопируйте отфильтрованную таблицу и вставьте ее с установкой связи после соответствующего заголовка.
5. Проведите выборку по условию автомобили с годом выпуска не ранее 1993, для этого: <щелкните по значку раскрывающегося списка столбца Год и выберите команду Числовые фильтры> → <в списке выберите больше или равно…, появится диалоговое окно Пользовательский автофильтр> → <в диалоговом окне Пользовательский автофильтр в раскрывающемся списке с названием столбца Год выберите условие отбора больше или равно, а в раскрывающемся списке справа – выберите 1993> → ОК. Скопируйте отфильтрованную таблицу и вставьте ее с установкой связи после соответствующего заголовка.
6. Проведите выборку всех моделей автомобилей, за исключением моделей ВАЗ и Волга, для этого: <щелкните по значку раскрывающегося списка столбца Модель и выберите команду Текстовые фильтры> → <в списке выберите Настраиваемый фильтр, появится диалоговое окно Пользовательский автофильтр> → <в раскрывающемся списке с названием столбца Модель выберите условие не начинается с, а в поле напротив напечатайте значение ВАЗ> → <поставьте переключатель И> → <из нижнего списка также выберите условие не начинается с, а в поле напротив напечатайте значение Волга> → ОК. Скопируйте отфильтрованную таблицу и вставьте ее с установкой связи после соответствующего заголовка.
7. Проведите выборку автомобилей только белого и только черного цвета, для этого: <щелкните по значку раскрывающегося списка столбца Цвет и выберите команду Текстовые фильтры> → <в списке выберите равно…, появится диалоговое окно Пользовательский автофильтр> → <в раскрывающемся списке с названием столбца Цвет выберите условие равно, а из списка напротив выберите значение белый> → <поставьте переключатель ИЛИ> → <из нижнего списка также выберите условие равно, а из списка напротив выберите значение черный> → ОК. Скопируйте отфильтрованную таблицу и вставьте ее с установкой связи после соответствующего заголовка.
Задание 3. Обмен данными между Excel и Word
2.Копирование данных из Excel в Word с установкой связи
Измените в исходной таблице на листе Автофильтр в табличном процессоре Excel несколько значений и убедитесь, что в текстовом процессоре Word в группе Копирование таблиц Excel с установкой связи в исходной и отфильтрованных таблицах также изменились значения, а в группе Копирование таблиц Excel без установки связи таблицы осталась без изменения. Значения в отфильтрованных таблицах в табличном процессоре Excel также должны измениться.
3. Внедрение таблицы Excel в документ Word