Обработка данных средствами MS Office Excel

Обработка данных средствами MS Office Excel Т.А. Голованова, доцент кафедры САУ Microsoft Excel 2010 – табличный процессор, предназначенный для решения планово-экономических, учётно-статистических, научнотехнических и других задач, в которых числовая, текстовая или графическая информация с определённой повторяющейся структурой представлена в табличном виде 2 Возможности и средства Microsoft Excel создание таблиц произвольной структуры и возможность их изменения; средства автоматизации заполнения ячеек таблицы данными; набор функций для выполнения вычислений; 3 средства автоматизации построения диаграмм различного типа; механизмы создания и обработки списков: сортировка, фильтрация, поиск; средства автоматизации создания отчётов 4 Интерфейс Excel Лента Лента – главный элемент пользовательского интерфейса, содержит команды, которые собраны в логические группы, упорядоченные на вкладках 5 Вкладки Постоянные: Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид 6 Контекстные, отображаются при работе с некоторыми объектами: Работа с колонтитулами, Работа с рисунками, Работа с таблицами, Работа с диаграммами и т.д. 7 Элементы управления кнопки, раскрывающиеся кнопки, списки, раскрывающиеся списки, счетчики, кнопки с меню, флажки, значки (кнопки) группы 8 Строка формул Отображает содержимое активной ячейки. Позволяет редактировать данные и вычисления в ячейке 9 Организация данных на листе 1. Список – набор строк листа, содержащий однородные данные. Первая строка содержит заголовки столбцов, остальные строки – однотипные данные в каждом столбце 10 Достоинства: можно представлять как информационные данные, так и данные для вычислений; удобен для сортировок, выборок, подведения итогов и т.д. Недостатки: не всегда наглядно представлены данные; может быть затруднено построение диаграмм 11 2. Таблица – способ организации данных, при котором формируются строки и столбцы с записями, а в ячейки на пересечении строк и столбцов помещаются данные 12 Достоинства: может иметь сложную многоуровневую структуру; компактное размещение данных; удобна для создания диаграмм Недостатки: может быть затруднена обработка данных: сортировка, выборка и т.д. 13 Список Таблица 14 Размещение данных на листе на листе размещают одну таблицу; таблицу помещают в верхнем левом углу листа; первый столбец таблицы размещается в столбце А; первая строка таблицы размещается в строке 1; недопустимы пустые строки и столбцы внутри таблицы 15 Вычисления с использованием формул Формула – математическое выражение, по которому выполняются вычисления формула всегда начинается со знака =; формула может содержать ссылки на ячейки или имена ячеек, операторы, константы 16 =СУММ(В2:В8)*30 Функция Диапазон ячеек Константа Оператор умножения 17 Ввод формулы 1) выделить ячейку, в которую требуется ввести формулу; 2) ввести = (знак равенства); 3) выделить мышью ячейку, являющуюся аргументом формулы; 18 4) ввести знак оператора; 5) выделить мышью ячейку, являющуюся вторым аргументом формулы; 6) при необходимости продолжить ввод знаков операторов и выделение ячеек; 7) подтвердить ввод формулы в ячейку: нажать клавишу Enter или Tab или кнопку Ввод (галочка) в строке формул. 19 Операторы Арифметические Оператор Значение + (знак плюс) Сложение - (знак минус) Вычитание/Отрицание * (звездочка) Умножение / (косая черта) Деление % (знак процента) Процент ^ (крышка) Возведение в степень 20 Сравнения Оператор Значение = (знак равенства) Равно > (знак больше) Больше < (знак меньше) Меньше >= (знак больше и равно) Больше или равно <= (знак меньше и равно) Меньше или равно <> (знак меньше и больше) Не равно 21 Ссылок Оператор Значение : (двоеточие) Ставится между ссылками на первую и последнюю ячейки диапазона ; (точка с Оператор объединения запятой) (пробел) Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов 22 Создание формул с использованием библиотеки функций Функция – стандартная формула, обеспечивающая выполнение определённых действий над данными, которые являются её аргументами 23 Обратите внимание внимание: функции располагаются в группе Библиотека функций вкладки Формулы и разделены на категории 24 1) выделить ячейку, в которую требуется ввести формулу; 2) выбрать категорию функций в группе Библиотека функций и выбрать нужную функцию; 3) в окне Аргументы функции в соответствующем поле (полях) ввести аргументы функции; 4) в окне Аргументы функции нажать кнопку ОК. 25 26 Создание формул с использованием Мастера функций 1) нажать кнопку Вставить функцию в строке формул; 2) в окне Мастер функций: шаг 1 из 2 выбрать категорию функции, затем в списке Выберите функцию выбрать функцию; 27 3) нажать ОК или дважды щелкнуть мышью по названию выбранной функции; 4) В окне Аргументы функции ввести аргументы, нажать ОК. 28 29 Кнопка «Сумма» Располагается в группе Редактирование вкладки Главная и в группе Библиотека функций вкладки Формулы (Автосумма) 30 Относительные и абсолютные ссылки на ячейки по умолчанию все ссылки – относительные; при копировании ячейки с формулой относительная ссылка автоматически изменяется; если при копировании ссылка не должна изменяться, вместо относительной ссылки следует создать абсолютную 31 $ - служебный символ, указывающий на абсолютную ссылку $B$4 – ссылка абсолютная по столбцу и по строке; B$4 – ссылка, относительная по столбцу и абсолютная по строке; $B4 – ссылка, абсолютная по столбцу и относительная по строке 32 Имена ячеек и диапазонов Имя – слово или строка знаков, представляющих ячейку, диапазон ячеек, формулу или константу, при использовании в формуле является абсолютной ссылкой 33 Присваивание имени: выделить ячейку или диапазон → → вкладка Формулы → группа Определённые имена → кнопка Присвоить имя → ввести имя → при необходимости задать область действия и ввести примечание 34 выделить ячейку или диапазон → в поле Имя в строке формул ввести имя → Enter 35 Применение имён в формулах: ввести с клавиатуры; вкладка Формулы → группа Определённые имена → кнопка Использовать в формуле → выбрать имя в списке 36 выбрать в раскрывающемся списке в поле Имя строки формул 37 Удаление имён: вкладка Формулы → группа Определённые имена → Диспетчер имён → выбрать имя → кнопка Удалить 38 Условное форматирование Условное форматирование – способ визуального выделения из общего массива данных, отвечающих некоторым условиям. Может применяться к данным и к результатам вычисления 39 Выделение значений: выделить ячейку или диапазон → вкладка Главная → группа Стили → кнопка Условное форматирование → выбрать условие и способ выделения Выделение крайних значений: выделить ячейку или диапазон → вкладка Главная → группа Стили → кнопка Условное форматирование → Правила отбора первых и последних значений 40 41 42 Форматирование с использованием гистограмм, трёхцветной шкалы, набора значков Позволяет рассмотреть значения в ячейке относительно других чем длиннее гистограмма, тем выше значение, трёхцветная шкала использует градацию цветов, значки соответствуют диапазону значений 43 44 Консолидация данных Консолидация – это процесс объединения данных из разных источников в виде итоговых значений, размещаемых в диапазоне назначения 45 Объектом консолидации или источником консолидируемых данных могут быть отдельные ячейки и диапазоны ячеек, размещенные на одном или нескольких листах, в одной или разных книгах. 46 Порядок консолидации данных: 1) Указать ячейку или диапазон ячеек, где будут располагаться консолидированные данные; 2) Вкладка Данные → группа Работа с данными → кнопка Консолидация → заполнить поля в диалоговом окне Консолидация 47 48 3) Указать диапазоны данных, подлежащих консолидации; 4) Указать способ консолидации согласно расположению в диапазоне, согласно заголовкам строк и столбцов; 49 3) Выбрать тип консолидации; 4) При необходимости указать добавление структуры – флажок Создавать связи с исходными данными 50 Подведение промежуточных итогов Промежуточные итоги необходимы для создания отчётов и обобщения большого объёма однотипных данных. При этом Excel создаёт необходимые формулы, вставляет строки итогов, структурирует данные 51 Итоги могут быть: простыми промежуточными, сложными промежуточными, связанными с вычислением частичных сумм 52 При подведении итогов можно: указать способ группировки данных, вывести промежуточные и общие итоги для одной группы в списке, вывести промежуточные и общие итоги для нескольких групп в списке, выполнить вычисления 53 Порядок подведения промежуточных итогов 1) Подготовить список данных, определить, какие итоги (суммы, количества и т.д.) и по каким данным нужны; 2) Выполнить сортировку данных по столбцу или столбцам; 3) Выделить ячейку в диапазоне или весь диапазон; 54 4) Вкладка Данные → группа Структура → кнопка Промежуточные итоги → диалоговое окно Промежуточные итоги → задать нужные параметры 55 56 57 Работа со сводными таблицами Сводная таблица – это средство группировки, обобщения и анализа данных, находящихся как в списках (таблицах) Excel, так и в таблицах, созданных в других приложениях. 58 Сводные таблицы представляют собой структуру с возможностью трёхмерного размещения данных и применяются для обобщения большого количества однотипных данных, реорганизации данных, отбора и группировки данных, построения диаграмм 59 Формирование сводной таблицы: 1) Вставить сводную таблицу на существующий или новый лист вкладка Вставка → группа Таблицы → кнопка Сводная таблица → Сводная таблица → выбрать диапазон на листе или указать источник данных, указать место для отчёта сводной таблицы 60 61 62 2) Сформировать структуру сводной таблицы определить поля, находящиеся в строках и столбцах таблицы, задать поля, по которым подводятся итоги, выбрать операцию для итоговых значений, при необходимости изменить параметры полей значений 63 64 65