ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 14.06.2021
Просмотров: 843
Скачиваний: 28
41
Рис.5.3.1
.
№
Фамилия,
имя,
отчество
Информатика
Русский
язык
Иностранный
язык
Итого
Рис. 5.3.2.
Задание 5.4.
Работаем с файлом
Список1.
Скрыть не важные по Вашему усмотрению
столбцы (например столбец
Пол
и все столбцы адреса).
Используя расширенный фильтр, провести фильтрацию результатов сдачи
вступительных экзаменов:
Отобразить строки таблицы с абитуриентами, которые получили все пятерки.
Отобразить строки таблицы с абитуриентами, которые не имеют троек и
сортировать отфильтрованную таблицу:
o
по фамилии,
o
затем по сумме набранных баллов.
Отобразить строки таблицы с абитуриентами, которые не имеют двоек и получили
хотя бы одну пятёрку.
Отобразить строки таблицы с абитуриентами, которые не имеют двоек.
6. Документы слияния
Создать документ слияния с поздравлением мужчин (женщин), список которых
находится в файле Excel (Список1) c Новым Годом по данному образцу. Документ слияния
создается в редакторе MS Word
42
ДОКУМЕНТЫ СЛИЯНИЯ
это документы, которые создаются путем объединения постоянной
(неизменяемой) части документа с источником данных. В качестве источника данных могут быть
таблицы MS Word, MS Excel,.MC Access. Документ создается в текстовом редакторе Word за
шесть этапов.
1 этап.
Для создания документа слияния необходимо напечатать неизменяемую часть
документа в редакторе MS Word. (общую для всех документов). Войти на вкладку Рассылки
–
группа
Начать слияние –список Начать слияние
–
Пошаговый мастер слияния
. Справа
выбрать тип Документа
Письма
, и уйти по гиперссылке
Далее Открытие документа
.
Примечание.
Пошаговый мастер слияния полностью соответствует порядку создания
документов слияния с предыдущих версиях редактора Word.
2
этап
. Выбрать Тип документа
Текущий документ (
а если документ создан заранее,
то
Существующий документ)
. Создать неизменяемую часть документа. Уйти по
гиперссылке
Далее Выбор получателей
.
3 этап
. В поле Выбор получателей выбрать
Существующий список
и найти
существующий список через
Обзор.
Указать место расположения списка в предложенном
документе (например, лист книги MS Excel). Найти файл «Список_1», «лист 1» и при
необходимости осуществить фильтрацию, например по полю «Пол»
.
Уйти по гиперссылке
Далее Создание письма
.
4 этап.
Последовательно устанавливая курсор в места вставок, и выбирая в поле
Создание письма - Другие элементы
вставить в текст необходимые поля. Уйти по
гиперссылке
Далее Просмотр писем
.
5 этап.
Щелкая кнопками
>>
или
<<
можно просмотреть письма. Уйти по
гиперссылке
Далее Завершение слияния
.
6
этап.
Письма можно печатать или просмотреть, выбрав ссылку
Изменить список
получателей
(с дальнейшим указанием каких).
Правка осуществляется в основном
документе.
Фамилия «Фамилия» «Имя» «Отчество»
Область «Республика, область, край»
Район «Район»
Населенный пункт «Город, населенный пункт»
Улица «Улица»
Дом «Дом»
Квартира «Квартира»
Уважаемый «Имя» «Отчество»!
Поздравляю Вас с Наступающим Новым 2011 Годом!
Желаю здоровья и всего самого наилучшего!
Президент компании «К светлому будущему» Ваш депутат
А.П. Петров
Примечание
. Все, что указано в кавычках являются полями слияния, кроме «К светлому
будущему».
43
7. Импорт списков в программу Excel из программы Блокнот
В программу Excel можно осуществлять копирование списков из различных программ.
Из некоторых программ, например, MS Access, импорт осуществляется через меню. Рассмотрим
наиболее сложный случай импорта списков в программу Excel из программы Блокнот (текстовых
файлов со списками). Проведите копирование файла «Образец выписки1 2007_12_20.txt» из
папки Преподавателя в свою папку.
Работаем в программе Excel в меню
Файл-Открыть
. В диалоговом окне
Открытие
документа
в списке
Тип файла
выбираем
Все файлы
, находим нужный файл (Образец
выписки1 2007_12_20.txt) и нажимаем кнопку
Открыть
. Т.к. файл не является файлом
программы Excel, открывается диалоговое окно
Мастер (текстов) (импорт)- шаг 1 из трех
(или
другой подобный мастер). На первом шаге необходимо указать формат данных (с разделителями
или фиксированной ширины). В примере с разделителями. Указать с какой строки начать импорт
(в примере с первой). Нажать
Кн. Далее
. В диалоговом окне
шага 2
установить символ
разделителя. У нас Знак табуляции, а ограничитель строк нет.
8. Сводные таблицы
Сводная таблица является аналитическим инструментом, позволяющим суммировать
информацию из полей списка Excel и других структур. Можно создавать сводные таблицы для
данных, измеряемых не только в мегабайтах, но и в гигабайтах, не нарушая при этом
ограничений по объему памяти вашей системы. При создании сводной таблицы вы можете
задать нужные поля, способ организации таблицы и тип выполняемых вычислений. После
построения таблицы можно изменять её расположение для просмотра данных под другим углом
зрения. Именно возможность изменения ориентации таблицы, например транспонирование
заголовков столбцов в заголовки строк, и наоборот, дала сводной таблице её название (в
оригинале Pivot Table – вращающаяся таблица) и сделала её мощным аналитическим
инструментом.
Наряду со сводными таблицами можно создавать сводные диаграммы. Можно
перетаскивать поля в сводной диаграмме с одной оси на другую точно так, как при работе с
таблицей.
8.1. Создание сводной таблицы
Работаем с таблицей Список1. Выделить ячейку в таблице. На вкладке Вставка слева
выбираем Сводная таблица. При этом запускается мастер сводных таблиц и диаграмм.
Появляется диалоговое окно Создание сводной таблицы.
На первом шаге выбирается источник данных для анализа и место расположения отчета
сводной таблицы.
Если исходный список
находится в книге Excel,
соответствующий файл ищется
через Обзор. После нахождения
нужного файла введите имя или
ссылку на диапазон, где
находится список.
Самый
безопасный
выбор
поместить таблицу на новом
листе.
Далее
идет
задание
начальной структуры таблицы.
После нажатия кнопки ОК в окне
диалога мастера Excel отобразит
в рабочем листе пустой макет
44
таблицы с четырьмя областями:
Перетащите сюда поля страниц, Перетащите сюда поля
столбцов и Перетащите сюда поля строки
и областью для данных
Перетащите сюда элементы
данных
. Кроме того, на экран будет выведена панель инструментов Сводные таблицы с кнопками
для каждого поля из источника данных. Вы можете задать начальную структуру сводной
таблицы, перетаскивая кнопки полей с панели инструментов в области макета. В каждую область
макета можно поместить любое количество полей. Чтобы удалить поле, перетащите его кнопку
за пределы макета.
Задание 8.1.
Создадим Сводную таблицу для анализа количества мужчин и женщин,
проживающих в городах и районах области и внесенных в список. Сводная таблица
создается перетаскиванием кнопок полей исходной таблицы с панели инструментов в три
области макета:
Перетащите сюда поля столбцов
. Перенесем сюда кнопки
Район
.
Перетащите сюда поля строк
. Перенесем сюда одну кнопку
Пол
То, что анализируется переносится в поле
Перетащите сюда элементы данных.
Перенесем сюда, например, кнопку
Фамилия.
Для анализа осуществляется выбор из списков в областях столбцов и строк.
Задание 8.2.
Создадим Сводную таблицу для анализа оценок по предметам на
вступительных экзаменах.
Перетащите сюда поля столбцов
. Перенесем сюда кнопки
одного из предметов
(например, Информатика)
.
Перетащите сюда поля строк
. Перенесем сюда одну кнопку
Район
То, что анализируется переносится в поле
Перетащите сюда элементы данных.
Перенесем сюда, например, кнопку
Фамилия.
8.2. Создание Сводной диаграммы
Вы можете создать сводную диаграмму, выбор в самом начале через вкладку
Вставка -
Сводная таблица – Сводная диаграмма
. Сводную диаграмму можно также создать после
построения сводной таблицы. Для этого выделите любую ячейку в сводной таблице и
используйте вкладку
Вставка-Гистограмма
. В первом случае Excel создает диаграмму
одновременно со сводной таблицей, а во втором – сводную диаграмму на основе текущей
сводной таблицы.
В любом случае таблица и диаграмма являются объектами. Изменения в одном сразу же
отображаются в другом.
Постройте Сводную диаграмму для сводной таблицы Примера 2.
Вы можете перестроить сводную диаграмму точно так же, как и таблицу, перетаскивая
кнопки полей с одной оси на другую. Чтобы добавить поле, перетащите его кнопку с панели
инструментов Сводные таблицы. Для удаления поля достаточно перетащить его за пределы
диаграммы. ( Поскольку таблица и диаграмма связаны, можно перестроить диаграмму,
реорганизуя сводную таблицу).
8.3. Обновление Сводной таблицы
Хотя сводная таблица связана с исходными данными, но она не обновляется
автоматически при обновлении исходных данных. Чтобы обновить сводную таблицу, выделите
в ней любую ячейку и затем на вкладке Данные выберите команду Обновить все. При этом
должен быть снят флажок
Отложить обновление макета
на панели инструментов Сводные
таблицы.
Лабораторная работа № 3
Тема. Консолидация. Обработка данных в пределах одной и нескольких книг.
Надстройки программы MS Excel (Подбор параметра, Поиск решения, Пакет анализа).
1. Анализ данных с применением технологии консолидации
Под консолидацией понимается объединение данных, хранящихся в одном или в нескольких
списках, выполнение над объединенными данными необходимых вычислений и формирование
результирующей таблицы.
45
Консолидацию можно выполнять по расположению или по категории. Консолидация по
расположению предполагает сбор информации из ячеек исходных списков, расположенных одинаково.
При консолидации по категории табличный процессор использует в качестве признака для объединения
заголовки столбцов или строк списков. Такой способ консолидации предоставляет большую свободу
организации данных.
Консолидация выполняется с помощью инструмента
Консолидация,
который включается на
вкладке
Данные-Консолидация.
Для выполнения консолидации нужно выполнить операции:
—установить курсор в ячейку рабочего листа, в которой будет размещаться левая верхняя
ячейка результирующей таблицы;
—включить инструмент
Консолидация;
в диалоговом окне
Консолидация
в списке
Функция
выбрать функцию для выполнения
необходимых вычислений;
—
в поле
Ссылка
указать адрес диапазона или всего списка, данные которого
предполагается консолидировать, если есть еще списки для консолидации щелкнуть на кнопке
Добавить,
ввести следующий диапазон и т.д.;
—
если консолидация выполняется по категории, в группе
Использовать в качестве имен
установить флажок
Подписи верхней строки,
при
консолидации по расположению — флажок
Значения левого столбца;
—для установления связи с исходными списками установить флажок
Создавать связи с
исходными данными (обычно не работает, но предполагается, что изменения в таблицах
приводят к изменениям в консолидированной таблице);
—щелкнуть на кнопке ОК.
Пример
Использовать файл «Консолидация».
На листах «1-й день», «2-й день», «3-й день» дан перечень операций, проведенных продавцами
магазина. В конце третьего дня руководитель (владелец магазина) используя технологию
Консолидации должен подвести общий итог работы продавцов.
Примечание
. В отличие от консолидации по расположению, которая может применяться
для данных одной таблицы, консолидация по категориям применяется только в тех случаях,
когда нужно консолидировать данные нескольких таблиц.
2. Присвоение имен ячейкам и диапазонам. Ссылки на листы и книги
2.1.
Присвоение имен ячейкам и диапазонам
Выделить ячейку или диапазон ячеек на рабочем листе и набрать имя в поле
Имя
строки формул. При этом имя это одно слово возможно с применением знака
подчеркивания. Имя может использоваться в формулах наравне с адресами ячеек и
диапазонов в пределах всей книги. Например, имя
Блок_1.
2.2.
Ссылки на листы и книги
Ссылка на лист
Лист1!В4
. Однако лист можно переименовывать как угодно пользователю
и ссылаться на новое имя листа. Имя типа
Лист1!В4
действует в пределах книги. (Но имя типа
Лист1!Блок_1
действует только в пределах одного листа под аналогичным именем.)
Объемное имя
Лист1:Лист3!В5
охватывает ячейки
В5
на листа с 1 по 3. Такое имя может
использоваться в формулах в пределах книги.
После того как на рабочем листе определено одно или несколько имен, для включения имен
в формулы можно использовать команды
Имя
и
Вставить
меню
Вставка
.