Практическое занятие 05
Тема: Создание электронной таблицы. Форматирование элементов таблицы.
1 Цель работы:
· Освоение приемов работы в табличном процессоре Excel.
· Получение навыков практической работы по созданию и редактированию электронных таблиц.
· Ознакомление с использование формул и функций, выполнение вычислений.
· Ознакомление с методами сортировки и фильтрации данных.
Контрольные вопросы:
1. Режим, в котором находится табличный процессор в каждый конкретный момент, отражается в Строке состояния (внизу окна, слева).
1) режим готовности - в Строке состояния появляется индикатор Готово;
2) режим ввода данных- в Строке состояния появляется индикатор Ввод;
3) режим редактирования - индикатор Правка;
4) командный режим - в Строке состояния появляются подсказки;
5) запись макроса - подключение микропрограммы на Visual Basic.
2. Автоматически в режиме заполнения могут быть введены: числовые последовательности (1 2 3 4… 3 5 7 9…), дни недели (Понедельник вторник … Ср чт…), месяцы (Январь февраль…), даты, временные данные, текстовые последовательности (Таблица 1 Таблица 2…) и пользовательские списки. Числа и даты образуют при заполнении ряды в арифметической прогрессии с шагом, который вычисляется в виде разности двух соседних значений.
Технология автоматического заполнения следующая.
· набрать первое значение ряда в ячейке;
· набрать следующее значение ряда в соседней ячейке (в случаях, когда нужно указать шаг прогрессии).
· Замаркировать две ячейки со значениями ряда.
· В правом нижнем углу выделенной области встать на маркер заполнения – курсор примет вид черного "крестика".
· Нажать левую кнопку мыши и буксировать в направлении заполнения ряда, а затем отпустить мышь.
Заполнение с помощью команды Правка ► Заполнить ► Прогрессия используется для заполнения ячеек рядами более разнообразного вида, например, чисел в арифметической или геометрической прогрессиях.
При применении технологии автоматического заполнения к ячейке, содержащей формулу, произойдет ее копирование с соответствующим изменением.
3. Ячейки электронной таблицы могут содержать данные произвольного типа (по мере необходимости пользователь может загружать в них самую любую информацию - данные числового, логического типа, даты массивов OLE-объекты, формулы, финансовые, статистические и другие функции и т. д.) Действие с ячейками выполняется по однотипным данным, которые задаются в формате ячеек. Ячейки рабочего листа электронной таблицы могут содержать:
· исходные или первичные данные - константы;
· производные данные, которые рассчитываются с помощью формул или функций.
Ячейки, содержащие константы, называются влияющими, а ячейки с формулами - зависимыми.
В обычном режиме работы в ячейках с производными данными отображаются значения, а формулы можно увидеть лишь при активации (выделении) ячейки в строке ввода и редактирования.
Данные в ячейках таблицы могут относиться к одному из следующих типов: текст, числа, даты, формулы и функции. Тип данных подразумевает:
- допустимое множество значений, которые можно принимать константа или переменная;
- допустимое множество операций над этими данными.
1) Текст - последовательность букв, иногда цифр или некоторых специальных символов.
2) Числа могут включать цифры и различные символы: знак процента, знак мантиссы, круглые скобки, денежные обозначения, разделители и др.
Разделители в разных странах отличаются: десятичный - в США точка, а в России и многих других странах запятая; для обозначения тысяч - в России иногда ставят пробел, в США - запятая, во многих странах -точка.
3) Дата и время. Вводятся как числа и выравниваются по правому краю. Дата и время могут быть использованы в качестве аргументов для формул. Вид и тип написания даты определяются в формате ячейки.
4) Формула. Формулой в электронной таблице называют арифметические и логические выражения. Формула всегда начинается со знака равенства (=) и вводится по латинскому регистру. Формулы могут содержать константы - числа или текст (в двойных кавычках), ссылки на ячейки, знаки арифметических, логических и других операций, встроенные функции, скобки, закладки и др. Сохраняется принятый в математике порядок выполнения операций.
5) Функция. Представляет собой программу с уникальным именем, для которой пользователь должен задать конкретное значение аргументов.
Все функции имеют одинаковый формат записи и включают имя функции и находящийся в круглых скобках перечень аргументов.
4. Функции управления базами данных и списками, функции даты и времени, DDE/Внешние
функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.
5. В Excel для фильтрации данных используются команды Автофильтр и Расширенный фильтр. В случае простых критериев для выборки нужной информации достаточно команды Автофильтр в ячейках выделенных заголовков должны появиться новые элементы. C их помощью можно устанавливать критерии фильтрации данных.
При использовании сложных критериев следует применять команду Расширенный фильтр.
Обе команды вызываются в результате выбора команды Данные Фильтр.
6. Команда Данные Сортировка устанавливает порядок строк в таблице в соответствии с содержимым конкретных столбцов.
Сортировка по возрастанию предполагает следующий порядок:
· Числа
· Текст, включая текст с числами (почтовые индексы, номера автомашин)
· Логические значения
· Значения ошибок
· Пустые ячейки
Сортировка по убыванию происходит в обратном порядке. Исключением являются пустые ячейки, которые всегда располагаются в конце списка.
При выборе Данные Сортировка открывается окно диалога “Сортировка диапазона”, которое позволяет вам указать поля для сортировки и определить критерий сортировки. С помощью раскрывающегося списка Сортировать по вы можете выбрать столбец для сортировки. Порядок сортировки устанавливается переключателями по возрастанию или по убыванию.
При сортировке по возрастанию текстовые данные упорядочиваются в алфавитном порядке от А к Я. Числовые данные упорядочиваются по возрастанию значений от минимального к максимальному. Даты упорядочиваются от наиболее ранней даты к наиболее поздней. При выборе переключателя по убыванию порядок сортировки изменяется на противоположный.
Два дополнительных раздела Затем и В последнюю очередь, по позволяют определить порядок вторичной сортировки для записей, в которых имеются совпадающие значения.
Переключатель Идентифицировать поля по указывает, имеется ли в сортируемом списке строка заголовков, которую нужно исключить из сортировки. При наличии в списке меток столбцов выберите переключатель Подписям, в противном случае — Обозначениям столбцов листа.
Окно диалога содержит кнопку Параметры, в результате нажатия которой открывается окно диалога “Параметры сортировки”. С помощью этого окна вы можете:
· Определить пользовательский порядок сортировки для столбца, указанного в раскрывающемся списке Сортировать по
· Сделать сортировку чувствительной к использованию прописных и строчных букв
· Изменить направление сортировки (вместо сортировки сверху вниз установить сортировку слева направо).
Практическое занятие 06
Тема: Выполнение вычислений в табличном процессоре. Создание и редактирование диаграмм.
1 Цель работы: Изучение информационной технологии представления данных в виде диаграмм в MS Excel; получение практических навыков выполнения вычислений с использованием формул.
Контрольные вопросы:
1. Данные числового, логического типа, даты массивов OLE-объекты, формулы, финансовые, статистические и другие функции и т. д. Ячейки рабочего листа электронной таблицы могут содержать:
· исходные или первичные данные - константы;
· производные данные, которые рассчитываются с помощью формул или функций.
2. Относительными называются ссылки, которые при копировании в составе формулы в другую ячейку автоматически изменяются.
При копировании формулы с относительной ссылкой (столбец)(строка) на n строк ниже и на m столбцов правее ссылка изменяется на (столец+m)(строка+n).
В большинстве случаев это очень удобно, но иногда этого не требуется.
Абсолютными называются ссылки, которые при копировании в составе формулы в другую ячейку не изменяются.
Абсолютные ссылки используются в формулах тогда, когда нежелательно автоматическое изменение ссылки при копировании.
3. Создание диаграммы с помощью" Мастера диаграмм"
шаг 1. Нужно выбрать подходящий вариант строящейся диаграммы. Для перехода к каждому последующему шагу Мастера диаграмм нужно использовать кнопку Далее.
шаг 2. Источник данных диаграммы. На вкладке Диапазон данных можно просмотреть, как будет выглядеть диаграмма, если ряды исходных данных находятся в столбцах, выбрать наиболее удачный вариант.
шаг 3. Можно определить характер оформления диаграммы - выполнить её форматирование.
шаг 4. Нажать клавишу Готово.
Для редактирования диаграммы:
· Дважды нажмите мышью в любом месте диаграммы.
· Команда Формат обозначения легенды устанавливает цвет линий, ихстиль и толщину.
· Для форматирования объекта диаграммы нажмите на нем правую кнопку
мыши и выберите нужную для форматирования команду из появившегося списка.
· Для замены одного ряда данных в диаграмме другим примените команду Формат ряда.
· Изменяя линии графика, можно изменить данные на рабочем листе.
· Команда Тип диаграмм позволяет изменять тип уже существующей диаграммы.
· Команда Автоформат меняет не только тип диаграммы, но и
· устанавливает стандартные параметры диаграммы.
· Команда Объемный вид изменяет пространственную ориентацию диаграммы.
При создании диаграммы смешанного типа возникают две группы рядов. Одна
группа — гистограмма, а другая может быть графиком, с областями
или XY-точечной. После создания диаграммы вы можете использовать для
каждого ряда данных любой тип плоской диаграммы.
Вы можете изменить формат диаграммы, которую Excel строит по умолчанию.
Команда Автоформат создает пользовательский автоформат для построения
диаграмм и графиков. Для изменения формата построения стандартных диаграмм в
окне диалога “Параметры” выполните команду Сервис | Параметры и
выберите вкладку “Диаграмма”.
Практическое занятие 07 08
Тема: Анализ и обобщение данных в электронных таблицах Exсel
Цель работы
· Освоение операции автоматического подведения итогов.
· Работа со структурой электронной таблицы.
· Выполнение консолидации данных.
· Создание прайс-листа на основе данных таблицы Excel.
Контрольные вопросы:
1. С помощью команды Данные | Итоги можно вставить строки итогов в
список, осуществив суммирование данные нужным способом. При вставке строк
итогов Excel автоматически помещает в конец списка данных строку общих итогов.
После выполнения команды Данные | Итоги вы можете выполнить следующие операции:
· выбрать одну или несколько групп для автоматического подведения
итогов по этим группам
· выбрать функцию для подведения итогов
· выбрать данные, по которым нужно подвести итоги
· Кроме подведения итогов по одному столбцу, автоматическое подведение
итогов позволяет:
· выводить одну строку итогов по нескольким столбцам
· выводить многоуровневые, вложенные строки итогов по нескольким столбцам
· выводить многоуровневые строки итогов с различными способами
вычисления для каждой строки
· скрывать или показывать детальные данные в этом списке.
Консолидации данных
В табличном редакторе Microsoft Excel предусмотрено несколько способов
консолидации:
· С помощью трехмерных
ссылок, что является наиболее предпочтительным способом. При использовании
трехмерных ссылок отсутствуют ограничения по расположению данных в исходных
областях.
· По расположению, если
данные исходных областей находятся в одном и том же месте и размещены в одном и
том же порядке. Используйте этот способ для консолидации данных нескольких
листов, созданных на основе одного шаблона.
· Если данные, вводимые
с помощью нескольких листов-форм, необходимо выводить на отдельные листы,
используйте мастер шаблонов с функцией автоматического сбора данных.
· По категориям, если
данные исходных областей не упорядочены, но имеют одни и те же заголовки.
Используйте этот способ для
консолидации данных листов, имеющих разную структуру, но одинаковые заголовки.
· С помощью сводной
таблицы. Этот способ сходен с консолидацией по категориям, но обеспечивает
большую гибкость при реорганизации категорий.
Консолидация данных с использованием трехмерных ссылок
Для консолидации данных с использованием трехмерных ссылок
необходимо проделать следующие шаги:
1.
На листе консолидации скопируйте или
задайте надписи для данных консолидации.
2. Укажите ячейку, в
которую следует поместить данные консолидации.
3. Введите формулу. Она должна
включать ссылки на исходные ячейки каждого листа, содержащего данные, для
которых будет выполняться консолидация.
4. Повторите шаги 2 и 3
для каждой ячейки, в которой требуется вывести результаты обработки данных.
Использование
трехмерных ссылок для объединения данных
Если исходные
листы имеют различные шаблоны и подписи или если требуется создать собственный
шаблон, или применить собственные формулы объединения данных, при консолидации
следует использовать трехмерные ссылки. Трехмерные ссылки обновляются
автоматически при изменении исходных данных.
Добавление
данных для объединения
Если все
исходные листы имеют одинаковый шаблон, в трехмерных формулах можно
использовать диапазон имен листов. Чтобы ввести в объединение новый лист,
скопируйте его в диапазон, на который ссылается формула.
2. При подведении итогов автоматически создается структура электронной таблицы. Символы структуры отображаются слева от номеров строк.