Microsoft Office Excel. Списки, их обработка. Анализ данных

Microsoft Office Excel. Форматирование таблиц.

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

• выполнить команду Формат – Ячейки (Microsoft OfficeExcel 2007: Главная – вкладка «Шрифт» или «Число»);
• щёлкнуть правой клавишей мыши по ячейке или диапазону и в контекстном меню выбрать команду «Формат ячеек».

Разделителем разрядов в Microsoft Office Excel служит «,» (запятая). Если необходимо, чтобы информация в ячейке была в несколько строк, можно воспользоваться командой Формат – Ячейки, открыв вкладку «Выравнивание» и установив флажок «Переносить по словам». Ввод в ячейку пары чисел через точку воспринимается датой, через двоеточие – временем. К операциям оформления таблиц относятся изменения:

• шрифта (вид, размер, начертание, цвет);
• границ (выбор типа и цвета линии, вида границ);
• цвета заливки, выбор узора.

Кроме этого можно применить к таблице или выделенному диапазону автоформат, воспользовавшись командой Формат - Автоформат (Microsoft Office Excel 2007: Главная – Стили).

1. Работа с примечаниями. При выполнении расчётов несколькими пользователями часто прибегают к использованию примечаний для ячеек или диапазонов. Задание примечания ячейке выполняется командой Вставка – Примечание или в контекстном меню выбором команды «Вставить примечание».

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

2. Виды ссылок на ячейки.

Существует два способа ссылок на ячейки:

• относительная ссылка: при копировании формулы адреса в ссылках автоматически изменяются в соответствии с относительным расположением исходной ячейки и создаваемой копии;
• абсолютная ссылка: если при копировании формулы нужно оставить неизменным какой-либо адрес, то используется абсолютная ссылка на него. Элементы адреса ячейки в этом случае предваряются знаком «$», например: $A1 – абсолютная ссылка по столбцу; A$1 – абсолютная ссылка по строке; $А$1 – абсолютная ссылка. Для организации абсолютной ссылки необходимо в строке формул выделить неизменяемый элемент адреса и нажать клавишу F4. При перемещении ячейки с формулой адреса в ней не меняются.

3. Имя ячейки.

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

ячейки не должно начинаться с точки, также недопустимо наличие пробела внутри имени. Лучше применить нижнее подчеркивание, например, «Текущий_курс». Для того чтобы увидеть перечень имён ячеек можно воспользоваться командой Вставка – Имя – Присвоить или, выделив нужные ячейки, выполнить команду контекстного меню «Присвоить имя».

4. Работа с колонтитулами.

Колонтитулы – служебные надписи, которыми снабжается лист. Колонтитулы бывают верхними и нижними и могут использоваться совместно. Вставка колонтитулов в рабочую книгу осуществляется командой Вид – Колонтитулы (Microsoft Office Excel 2007: Вставка – Колонтитулы).

5. Параметры страницы. Печать листа. Чтобы установить необходимые параметры для последующей

печати документа можно выполнить команду Файл – Предварительный просмотр или Файл – Параметры страницы (Microsoft Office Excel 2007: Разметка страницы – Параметры страницы).

Описание вкладок окна «Параметры страницы»:

• Страница: изменение ориентации листа, масштабы вывода информации на печать, размеры бумаги;
• Поля: установка размеров всех полей документа;
• Колонтитулы: задание верхних и нижних колонтитулов, предварительный просмотр листа;
• Лист: вывод на печать определённого диапазона листа (строки, столбца), настройка видов печати документа.

После настройки параметров страницы на листе появляется штриховая разметка, обозначающая границы печати. Экранное представление таблицы значительно отличается от того, что получается при выводе данных на печать. Это связано с тем, что единый рабочий лист приходится разбивать на фрагменты, размеры которых зависят от формата печатной страницы. Перед печатью рабочего листа следует перейти в режим предварительного просмотра (кнопка «Предварительный просмотр» располагается на стандартной панели инструментов), с помощью которого его можно увидеть на экране точно в таком виде, в каком он будет напечатан. Кроме того, режим предварительного просмотра позволяет изменить свойства печатной страницы и параметры печати. Управление в режиме предварительного просмотра осуществляется при помощи кнопок, расположенных вдоль верхнего края окна. Кнопка «Страница» открывает диалоговое

окно «Параметры страницы», которое служит для задания параметров печатной страницы, ориентации листа, масштаба (изменение масштаба позволяет управлять числом печатных страниц, необходимых для документа), размеров полей документа. Здесь же можно задать верхние и нижние колонтитулы для страницы. На вкладке «Лист» включается или отключается печать сетки и номеров строк и столбцов, а также выбирается последовательность разбиения рабочего листа на страницы, когда рабочий лист превосходит размер печатного листа, как по длине, так и по ширине. Изменить величину полей страницы, а также ширину ячеек при печати можно также непосредственно в режиме предварительного просмотра при помощи кнопки «Поля». При нажатии этой кнопке на странице появляются маркеры, указывающие границы полей страницы и ячеек. Изменить положение этих границ можно методом буксировки. Завершить работу в режиме предварительного просмотра можно тремя способами, в зависимости от того, что планируется

делать дальше. Щелчок на кнопке «Закрыть» позволяет вернутьсяк редактированию документа. Щелчок на кнопке «Разметка страницы» служит для возврата к редактированию документа, но в режиме разметки страницы. В этом режиме документ отображается таким образом, чтобы наиболее удобно показать не содержимое ячеек таблицы, а область печати и границы страниц документа. Переключение между режимом разметки и обычным режимом можно также осуществлять через меню «Вид»: команды Вид – Обычный и Вид – Разметка страницы). Третий способ – начать печать документа. Щелчок на кнопке «Печать» открывает одноимённое диалоговое окно, используемое для распечатки документа (его можно открыть и без предварительного просмотра с помощью команды Файл – Печать). Это окно содержит стандартные

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

Область печати – часть рабочего листа, которая должна быть выведена на печать. По умолчанию область печати совпадает с заполненной частью рабочего листа и представляет собой прямоугольник, прилегающий к верхнему левому углу рабочего листа и захватывающий все заполненные ячейки. Если часть данных не должна выводиться на бумагу, область печати можно задать вручную. Для этого надо выделить ячейки, которые должны быть включены в область печати, и выполнить команду Файл – Область печати – Задать. Если текущей является одна ячейка, то программа предполагает, что область печати просто не выделена, и выдает предупреждающее сообщение. Если область печати задана, то программа отображает в режиме__ предварительного просмотра и распечатывает только её. Границы области печати выделяются на рабочем листе крупным пунктиром (сплошной линией в режиме разметки). Для изменения области печати можно задать новую область или командой Файл – Область печати – Убрать вернуться к параметрам, используемым по умолчанию.

6. Работа с листом.

Перемещение ярлыка листа с нажатой клавишей CTRL – создание копии этого листа. Сделать копию листа можно и с помощью контекстного меню: подвести указатель мыши к ярлыку нужного листа, нажать по нему правой клавишей мыши. В открывшемся меню выбрать команду «Переместить или скопировать» и либо создать копию листа в имеющейся рабочей книге, либо создать новую рабочую книгу с этим листом. Также с помощью контекстного меню можно переименовать лист. Для выделения нескольких листов необходимо щёлкнуть по ярлыку первого листа и с нажатой клавишей CTRL щёлкнуть по остальным листам.

28)Microsoft Office Excel. Организация расчётов с помощью формул, функций и диаграмм.

1. Формулы и функции.

Как было отмечено ранее, формулы и функции относятся к стандартным типам данных табличного процессора, работа с ними осуществляется через Мастер функций, а сами функции располагаются в тематических категориях для простоты их поиска.

Мастер функций можно запустить двумя способами:

• выполнить команду Вставка – Функция (Microsoft Office Excel 2007: Формулы – Вставить функцию);
• Нажать на кнопку fх на панели инструментов или воспользоваться сочетанием клавиш [Shift+F3].
• Алгоритм работы с мастером функций: выбрать нужную категорию функций; в категории выбрать необходимую функцию; для выбранной функции задать её аргументы (адреса ячеек, имена ячеек, имена диапазонов).

Если имя функции известно, то его можно ввести в поле «Поиск функции» и попытаться найти, минуя категории. Формулы с функциями копируются обычным образом. Функции рекуррентны, то есть внутри одной функции в качестве аргумента можно использовать другую. Для организации вложенной функции пользуются ниспадающим списком слева от строки формул, а для перехода от внутренней функции к внешней, в строке формул необходимо щёлкнуть левой клавишей мыши по имени внешней функции.

Если ячейка в формуле располагается на другом листе, то её адрес записывается в виде трёхмерного: Имя

Листа!СтолбецСтрока, где:

• Имя листа! – первый уровень;
• Столбец – второй уровень;
• Строка – третий уровень.

При копировании формулы с такой адресацией меняются два первых уровня. Если ячейка в формуле располагается в другойрабочей книге, её адрес записывается в виде четырёхмерного: [Имя книги]Имя Листа!СтолбецСтрока. Адрес такого вида часто называют внешней ссылкой. В результате организации четырёхмерного адреса создаётся связь: при открытии книги со связью следует запрос об обновлении связей.

2. Диаграммы.

Среди задач, решаемых с помощью Microsoft Excel 2003, особое место занимает графическое представление данных и результатов их обработки. Используются для этих целейдиаграммы, которые отображают данные нагляднее, облегчают восприятие и помогают при анализе и сравнении данных.

Диаграмма – средство графического изображения информации, предназначенное для сравнения нескольких

величин или нескольких значений одной величины, слежения за изменением их значений.

В зависимости от места расположения, особенностей построения и редактирования, различают два типа диаграмм:

• внедрённые диаграммы – помещаются на том же рабочем листе, где и данные, по которым они построены;
• диаграммы в формате полного экрана – располагаются на новом рабочем листе.

Оба типа связаны с данными рабочего листа и автоматически обновляются при их изменении.

Построение диаграммы осуществляется в Microsoft Excel 2003 с помощью Мастера диаграмм, запускаемый командой Вставка – Диаграммы.

Этапы построение диаграммы с помощью Мастера:

а) Выбор типа диаграммы.

• круговая диаграмма: служит для сравнения нескольких величин в одной точке, особенно полезна, если величины в сумме составляют нечто целое (100%);
• столбчатая диаграмма: используется для сравнения нескольких величин в нескольких точках;
• линейная диаграмма: применяется для отслеживания за изменением нескольких величин при переходе от одной точки к другой;
• ярусная диаграмма: позволяет наглядно сравнить суммы нескольких величин в нескольких точках, и при этом показать вклад каждой величины в общую сумму;
• областная диаграмма (диаграмма площадей): позволяет одновременно проследить изменение каждой из нескольких величин и изменение их суммы в нескольких точках.

б) Выбор данных.

Выбор диапазона ячеек, на основе данных которых будет построена диаграмма. Если данные не образуют единой группы, то информацию для прорисовки отдельных рядов задают на специальной вкладке «Ряд».

в) Оформление диаграммы.

Состоит в задании следующих параметров:

название диаграммы, подписи осей;

• отображение и маркировка осей координат;
• отображение сетки линий, параллельных осям координат;
• описание построенных графиков;
• отображение надписей, соответствующих отдельным элементам данных диаграммы.

г) Размещение диаграммы.

• на листе вместе с данными;
• на отдельно листе.

3. Тренды.

Тренд – функция заданного вида, с помощью которой можно аппроксимировать построенный график. Служит для выявлений тенденций развития процесса, представленного в диаграмме, и обеспечивает прогноз на заданный период. Необходимые условия построения тренда: период времени, за который изучается прогнозируемый процесс. Этот процесс должен быть достаточным для выявления закономерностей; процесс, представленный диаграммой должен обладать определённой динамикой.

Виды диаграмм, для которых можно построить тренд:

• линейная;
• точечная;
• гистограмма;
• областная.

Алгоритм построения линии тренда:

• построить диаграмму для одного ряда данных;
• выделить диаграмму щелчком левой клавишей мыши по ней, на диаграмме появятся маркеры;
• пользуясь контекстным меню выполнить команду «Добавить линию тренда»;
• в открывшемся окне «Формат линии тренда» выбрать его тип и установить параметры (количество периодов прогноза, отображение уравнения на диаграмме, пересечение кривой с осью Y).

1. Сортировка данных в списке.

Списком называют электронную таблицу, в которой строки (записи) имеют фиксированную структуру, а имена столбцов (полей) занимают одну строку.

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

• сначала сортировка осуществляется в столбце первого уровня;
• далее сортируются одинаковые записи первого столбца по столбцу второго уровня;
• затем сортируются одинаковые записи второго столбца по столбцу третьего уровня.

Кнопкой «Параметры» задаются дополнительные установки сортировки – с учётом регистра или без него; по столбцам или по строкам; выбирается порядок сортировки из предлагаемого списка.

2. Фильтрация данных в списке.

При фильтрации осуществляется выбор данных по заданному

критерию (условию) с помощью команды Данные – Фильтр или Данные – Расширенный фильтр (Microsoft Office Excel 2007: Данные – Сортировка и фильтр, кнопка «Дополнительно» или Главная – Редактирование). Если установлено несколько фильтров, то общая фильтрация идёт по схеме «И».

3. Автофильтр.

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

варианты:

• все – выбираются все записи без ограничений;
• первые 10 – выбор определённого количества наибольших или наименьших элементов списка, которые необходимо отобразить;
• условие – отбор записей по условию, формируемому пользователем;
• значения –отображение только тех записей, которые в данном столбце содержат указанное значение.

4. Расширенный фильтр.

Обеспечивает использование двух типов критериев для фильтрации:

• критерий сравнения;
• вычисляемый критерий.

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

требований:

• состав столбцов области критерия соответствует составу столбцов искомого списка;
• имена столбцов области критериев должны точно совпадать с именами столбцов искомого списка;
• ниже имён столбцов располагаются критерии сравнения (точное значение; точное значение, сформированное при использовании операторов отношения; шаблон значений). Флажок «Только уникальные записи» означает отображение в результате фильтрации записей, не имеющих дублирования в искомом списке

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

5. Структурирование таблиц с подведением итогов.

При работе с большими таблицами применяется их структурирование – группирование строк и столбцов, то есть создаётся древовидная структура таблицы. Для создания структуры необходимо выполнить команду Данные – Группа и структура (Microsoft Office Excel 2007: Данные – Структура).

Способы структурирования: ручной и автоматический. При ручном способе необходимо предварительно выделить область (смежные строки или столбцы). Если внутри структурной части выделить группу и выполнить команду Данные – Группа и структуры – Группировать, то мы получим вложенный структурный элемент. В итоге создаётся структура таблицы, содержащая следующие элементы:

• линии уровней структуры, показывающие соответствующие группы иерархического уровня кнопка «+» для раскрытия групп структурированной таблицы;

• кнопка «-» для скрытия групп структурированнойтаблицы;
• кнопки с номерами уровней – для открытия или скрытиясоответствующего уровня.

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

С помощью команды Данные – Итоги (Microsoft Office Excel 2007: Данные – Структура – Промежуточные итоги) можно создать структуру таблицы и одновременно вставить строки промежуточных и общих итогов для выбранных столбцов в соответствии с заданной операцией (например, сумма, количество значений, среднее, максимум, минимум, произведение, количество чисел).

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

соответствующей операции; поле «Операция»: выбор операции, используемой для подведения итогов;

поле «Добавить итоги по»: перечень столбцов, по которым подводятся итоги. Можно подвести итог для одного и того же списка многократно. Если установлен флажок «Заменить текущие итоги», то ранее подведённые итоги будут заменены новыми, иначе они добавятся.

Итоги выводятся либо под данными, либо над ними с помощью флажка «Итоги под данными».

Принудительно группы могут размещаться на отдельных печатных страницах (флажок «Конец страницы между группами»). Для создания автоструктуры таблицы необходимо установить указатель мыши на одну из ячеек списка и выполнить команду Данные – Группа и структура – Создание структуры (Microsoft

Office Excel 2007: Данные – Структура – Группировать – Создание структуры).

6. Создание структуры таблицы вручную.

Для создания структуры ручным способом необходимо воспользоваться командой Данные – Группа и структуры – Группировать (Microsoft Office Excel 2007: Данные – Структура –

Группировать). Прежде, чем создавать структуру, следует вставить пустые строки или столбцы, а затем выполнять соответствующую команду. Удаление структуры осуществляется командой Данные – Группа и структуры – Удалить структуру (Microsoft Office Excel 2007: Данные – Структура – Разгруппировать).

7. Консолидация данных.

Другим способом создания итоговой информации является консолидация данных, которая выполняется в соответствии с выбранной функцией обработки. Консолидация – агрегирование или объединение данных, представленных в исходных областях-источниках. Результат консолидации создаётся путём применения функций обработки к исходным данным. Области-источники могут находиться на разных листах или разных рабочих книгах, в консолидации может участвовать до 255 областей-источников. Варианты консолидации данных:

· с помощью формул, в которых используются ссылки;

· по расположению данных для одинаково организованныхисточников;

· по категориям для источников различной структуры;

· консолидация внешних данных.

Для консолидации данных необходимо установить курсор в область назначения и выполнить команду Данные – Консолидация (Microsoft Office Excel 2007: Данные – Работа с данными – Консолидация). Условия консолидации задаются в окне «Консолидация»:

• выбирается функция консолидации данных;
• для каждой области-источника строится ссылка;
• при консолидации по расположению данных все источники имеют одинаковое расположение данных источников, данные имеют одинаковую структуру, фиксированное расположение ячеек и могут быть консолидированы с определённой функцией обработки (например, среднее значение, максимум, минимум) по их расположению;
• при консолидации по категориям области-источники содержат однотипные данные, но организованные в них неодинаково при консолидации устанавливаются флажки «Подписи верхней строки» или «Значения левого столбца» (Microsoft Office Excel 2003 автоматически переносит эти имена в область назначения);
• при консолидации внешних данных следует выбрать файл, содержащий области-источники для присоединения к списку, а затем добавить ссылку на ячейку или указать имя блока ячеек.
• Флажок «Создавать связи с исходными данными»: ссылки можно модифицировать либо изменять их конфигурацию, если до этого не был выбран данный флажок.

Дата добавления: 2015-04-24