Центр Компьютерного Обучения " Специалист ", 2012 Microsoft Excel 2010. Расширенные возможности решение
 Скачать 4.3 Mb.
|
|
ячеек (Use a formula to determine which cells…). 4. Ввести формулу, задать оформление – кнопка Формат (Format), нажать OK. ПРИМЕР: выделить Поставщиков, выручка которых выше среднего значения. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 42 ПРАКТИКУМ: Открыть файл 01_9 Условное форматирование. На листах Температура, Продажи, Отделения, Регионы, Поставщики выполнить соответствующие задания: Сохранить сделанные изменения и закрыть файл. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листах 9-1, 9-2 и 9-3. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 43 Модуль 2. Р АБОТА С БОЛЬШИМИ ТАБЛИЦАМИ ( СПИСКАМИ ) Работа со списками Списком называется набор данных на листе Excel, в котором каждому столбцу сопоставлено уникальное имя, а каждая строка представляет совокупность данных. Список данных не может содержать пустых строк и столбцов, между строкой заголовков и данными также не должно быть пустых строк, а ячейки рядом со списками не должны содержать лишних данных. При работе со списками используются несколько специальных терминов: в каждой таблице содержатся строки, называемые записями, и столбцы, называемые полями. Преобразование диапазона ячеек в список позволяет быстро переключаться между различными стилями оформления. При прокручивании таблицы вниз, названия столбцов листа автоматически преобразуются в названия полей таблицы. Можно отображать строку итогов внизу таблицы с возможностью вычисления по каждому столбцу нужными функциями, а при фильтрации расчет будет происходить только для видимых строк. Создание списка 1. Выделить любую ячейку таблицы данных. 2. На вкладке Вставка [Insert], выбрать Таблица [Table]. 3. Указать расположение данных таблицы. 4. ОК. По умолчанию каждому списку присваивается уникальное имя вида Таблица1, Таблица2 и т.д. Имя списка можно изменить. 1. Выделить ячейку списка. 2. На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя списка в поле Имя таблицы и нажать Enter К списку можно применять различные стили оформления, для этого необходимо выделить ячейку списка и на вкладке Конструктор [Design], выбрать нужное оформление в поле Стили таблиц [Table Styles]. Вычисления в списках Строка итогов позволяет обрабатывать данные каждого поля различными функциями. Для вычисления итогов, которые появляются внизу таблицы, необходимо выполнить следующие действия. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 44 1. На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Toral Row]. 2. В строке Итог [Total] выбрать для вычисления по полю нужную функцию. Для ввода новых записей в конец списка, следует отключить строку итогов – убрать флажок Строка итогов [Total Row]. Введенные данные таким образом, будут автоматически расширять диапазон списка. Для создания нового поля в конце таблицы (столбец данных) отключать строку не нужно. Для вычисления новых данных, достаточно написать формулу в одной ячейке. При этом обращение к ячейке происходит не к ее адресу, а к имени поля. По завершении формулы, она автоматически будет скопирована вниз до окончания списка. Преобразование списка в обычный диапазон При работа с таблицами (списками), действует ряд ограничений: нельзя добавлять ячейки со сдвигом, нельзя объединять ячейки и т.д. Список можно быстро преобразовать в обычный диапазон. При этом фильтры автоматически будут удалены, а оформление таблицы останется. Для преобразования в диапазон, необходимо выполнить последовательность действий: 1. На вкладке Конструктор [Design] в группе Сервис [Tools], выбрать Преобразовать в диапазон [Convert to Range]. 2. Выбрать Да [Yes] в ответ на вопрос о преобразовании таблицы в обычный диапазон. ПРАКТИКУМ: Открыть файл 02_1 Списки. На листе Списки1 преобразовать исходный диапазон в список. Применить к списку стиль средний 12 Включить Строку итогов. Вычислить количество значений по полю Отдел, сумму Ежемесячных выплат и среднее значение Дополнительной выплаты. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 45 Отобразить данные по сотрудникам из Отдела информации. Посмотреть на результат вычислений в строке Итоги. Отключить Строку итогов. Добавить в конец таблицы сотрудника Великолепная В.В. из Отдела продаж с коэффициентом надбавки 1,5 и ежемесячной выплатой 1100 у.е. Включить строку итогов и отобразить данные только из Отдела продаж. На листе Списки2 преобразовать исходный диапазон в список. В ячейку J1 ввести заголовок – Сумма выплаты, р. Вычислить значения введенного поля, считая 1 у.е. = 40 руб. Сохранить внесенные изменения и закрыть файл. Удаление дубликатов По тем или иным причинам, в больших таблицах нередко появляются записи-дубликаты. Как правило, это связано с недостаточно добросовестным вводом данных. Анализировать данные с такими дубликатами бессмысленно, поэтому их необходимо вычислять и удалять. 1. Выделить любую ячейку таблицы с повторениями. 2. На вкладке Данные [Data] в группе Работа с данными [Data Tools] нажать кнопку Удалить дубликаты [Remove Duplicates]. 3. Выбрать названия столбцов, при совпадении значений которых, строки будут считаться дублирующими. 4. ОК. ПРАКТИКУМ: Открыть файл 02_2 Дубликаты. На листе Задание1, Задание2, Задание3 выполнить соответствующие задания. Сохранить изменения в файле и закрыть его. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 46 Сортировка Сортировка данных является встроенной частью анализа данных. Может потребоваться расположить в алфавитном порядке фамилии в списке, составить перечень объемов запасов продуктов от максимального до минимального, а также задать порядок строк в зависимости от цвета или значка. Сортировка данных помогает быстро придавать данным удобную форму и лучше понимать их, организовывать и находить необходимую информацию, и в итоге принимать более эффективные решения. Можно выполнять сортировку данных по тексту (от А до Я или от Я до А), по числам (от наименьших к наибольшим или от наибольших к наименьшим), а также по датам и времени (от старых к новым или от новых к старым). Сортировку можно выполнять как по данным одного столбца, так и по нескольким. Можно также выполнять сортировку по настраиваемым текстовым спискам или по формату, включая цвет ячеек, цвет шрифта, а также по значкам. Большинство сортировок применяются к столбцам, но возможно также применить сортировку к строкам. Сортировка по одному столбцу 1. Выделить любую ячейку столбца (не выделять столбец!); 2. На панели инструментов нажмите кнопку - сортировка от А до Я (по возрастанию) или кнопку - сортировка от я до А (по убыванию). Существует и другой удобный способ сортировки данных– щелкнуть правой кнопкой мыши по ячейке столбца и в контекстном меню выбрать Сортировка [Sort], далее – нужный вариант. Многоуровневая сортировка 1. Выделить любую ячейку таблицы. Если исходный диапазон содержит пустые строки и/или столбцы, то необходимо выделить всю таблицу вместе с заголовками. 2. На вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать Сортировка [Sort].; 3. Указать последовательность сортировки столбцов. Чтобы задать следующий уровень сортировки, следует нажать кнопку Добавить уровень [Add Level] Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 47 Для сортировки по дням недели, названиям месяцев выбрать в списке поля Порядок, Настраиваемый список Сортировка по цвету 1. Выделить любую ячейку таблицы. 2. На вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать Сортировка [Sort]. 3. Выбрать в поле Столбец поле, по которому будет производиться сортировка. 4. В поле Сортировка выбрать по какому критерию будет сортировка: цвет ячейки (заливка), цвет шрифта или значок ячейки. 5. В зависимости от критерия сортировки в поле Порядок выбрать цвет или значок ячейки, а так же схему упорядочения Сверху или Снизу. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 48 6. Нажать кнопку Копировать уровень, чтобы быстро задать следующий уровень сортировки, изменив в поле Порядок цвет или значок. Повторить нужное количество раз. 7. ОК. ПРАКТИКУМ: Открыть файл 02_3 Сортировка. На листе Сортировка1, Сортировка2, Сортировка3, Сортировка4 выполнить соответствующие задания. Сохранить сделанные изменения в файле и закрыть его. Фильтрация данных Фильтрация – отбор тех строк базы данных, значения которых удовлетворяют выбранным условиям в столбцах (в полях базы данных). В программе предусмотрено два вида фильтров: Автофильтр – позволяет отбирать записи из базы данных с простыми условиями. Расширенный фильтр – возможность решения задач фильтрации данных со сложными критериями отбора. Автофильтр Установка Автофильтра: 1. Указать любую ячейку таблицы; 2. На вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать Фильтр [Filter]. Отбор записей: 1. Раскрыть список фильтрации в нужном столбце строки заголовков таблицы; 2. Выбрать условие фильтрации: Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 49 Установить флажки внизу списка для отбора нужных записей; Фильтр по цвету – выбор по форматированию ячейки: по цвету ячейки (если задана пользовательская заливка ячеек), по цвету шрифта (если установлен цвет шрифта) и по значку ячейки (если установлено условное форматирование); Числовые фильтры, Текстовые фильтры, Фильтры по дате – в зависимости от содержимого ячейки; Первые 10… [Top 10…] – несколько наибольших [Top] или наименьших [Bottom] элементов списка [Items] или % от элементов списка [Percent]; Настраиваемый фильтр… [Custom…] – настраиваемые условия отбора, максимум 2 условия: И [And] – одновременное выполнение 2-х условий, ИЛИ [Or] – выполнение хотя бы одного условия (Выделить все) [All] или Удалить фильтр с – снятие условия фильтрации по выбранному столбцу. Чтобы удалить все условия отбора, надо на вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать Очистить [Clear] ПРАКТИКУМ: Открыть файл 02_4 Фильтры. На листе Задание1, Задание2 решить соответствующие задания. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 50 Расширенный фильтр Расширенный фильтр позволяет: 1. Построить более сложные условия отбора. 2. Разместить отфильтрованные данные в другом диапазоне. 3. Из списка повторяющихся значений выбрать только уникальные. Последовательность действий: 1. Построить таблицу условий отбора данных на любом листе текущей книги (можно и в другой книге). Название столбца должно совпадать с одним из заголовков таблицы (лучше копировать из исходной таблицы), условия отбора в одной строке работают как И, а в разных строках – как ИЛИ. Условия фильтрации могут быть на совпадение (искомое значение просто вводится в ячейку) или на сравнение (в ячейку видится оператор сравнения и значение). Если по одному столбцу надо поставить условие между, то следует добавить этот заголовок еще раз и задать второе ограничение. 2. Находясь на листе с таблицей условий, выбрать на вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter] команду Дополнительно [More]. 3. Выделить Исходный диапазон (фильтруемая таблица) [List range] и Диапазон условий (условия фильтрации) [Criteria range] вместе с названиями столбцов (заголовками). 4. Выбрать вариант обработки [Action]: фильтровать список на месте [Filter the list, in-place] скопировать результат в другое место [Copy to another Location]. Указать ячейку для размещения результата отбора в поле Поместить результат в диапазон [Copy to]. 5. Установить флажок Только уникальные записи [Unigue records only], если необходимо получить результат отбора без повторений. 6. ОК. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 51 ПРАКТИКУМ: Открыть файл 02_4 Фильры. На листе Задание3, Задание4 выполнить соответствующие задания. Сохранить сделанные изменения в файле и закрыть его. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листах 10-1 и 10-2. Промежуточные итоги Один из способов обработки и анализа базы данных состоит в подведении различных итогов. Итоги – это быстрый способ вставки функций в определенные столбцы таблицы с группировкой данных в столбце, по которому подводятся итоги. 1. Отсортировать таблицу по столбцу, по значениям которого нужно подвести итог. 2. Выделить любую ячейку таблицы. 3. На вкладке Данные [Data], в группе Структура [Outline], выбрать Промежуточный итог [Subtotal]. 4. В поле При каждом изменении в [At each change in]выбрать столбец, который сортировали. 5. В поле Операция [Use function] выбрать нужную функцию. 6. В окне Добавить итоги по: [Add subtotal to] поставить флажки для тех полей, к которым будет применена операция. 7. ОК. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 52 Команда Промежуточный итог [Subtotal] вставляет в таблицу новые строки, содержащие специальную функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(Номер_функции; Ссылка) [SUBTOTAL(Function_num; Ref)]. Номер_функции [Function_num] – это число от 1 до 11, которое указывает, какую функцию использовать при вычислении итогов внутри списка. Ссылка [Ref]- это интервал или ссылка, для которой подводятся итоги. НОМЕР ФУНКЦИИ ФУНКЦИЯ FUNCTION НОМЕР ФУНКЦИИ ФУНКЦИЯ FUNCTION 1 СРЗНАЧ AVERAGE 7 СТАНДОТКЛОН STDEV 2 СЧЁТ COUNT 8 СТАНДОТКЛОНП STDEVP 3 СЧЁТЗ COUNTA 9 СУММ SUM 4 МАКС MAX 10 ДИСП VAR 5 МИН MIN 11 ДИСПР VARP 6 ПРОИЗВЕД PRODUCT В разделе структуры имеются элементы управления трех типов: Кнопки Скрыть детали [Hide Detail] – когда строки в группе отображаются, рядом с группой появляется кнопка . Кнопки Отобразить детали [Show Detail] – когда группа строк скрыта, кнопка рядом с группой становится кнопкой Отобразить детали . Нажав кнопку Отобразить детали , можно просмотреть строки таблицы данной группы. Кнопки уровня. Каждая из пронумерованных кнопок уровня (максимум 8) представляет уровень организации в таблице; нажав кнопку уровня, можно скрыть все уровни деталей, относящихся к нажатой кнопке. 1 – Общий итог 2 – Промежуточные итоги для каждой группы 3 – Отдельные строки таблицы Если к одним итогам (например, сумма) добавляются другие (среднее), то при добавлении новых итогов снять флажок Заменить текущие итоги [Replace current subtotals]. Для удаления итогов с листа выбрать в окне Промежуточные итоги [Subtotals]команду Убрать все [Remove All]. Многоуровневые итоги Если необходимо подвести итоги по нескольким полям (например, по Наименованию товара, а затем внутри товаров по Поставщикам), то надо: 1. Сделать многоуровневую сортировку. 2. В окне Промежуточные итоги [Subtotal] подвести итоги по первому полю сортировки (например, Наименование товара). 3. Подвести итоги по второму полю сортировки (например, Поставщик), при этом обязательно снять флажок Заменить промежуточные итоги [Replace current subtotals]. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 53 ПРАКТИКУМ: Открыть файл 02_5 Промежуточные итоги. На листе Задание1, Задание2, Задание3, Задание4, Задания5 решить соответствующие задачи. Сохранить сделанные изменения в файле и закрыть его. САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листах 11-1, 11-2 и 11-3. Консолидация данных Консолидация – объединение значений из нескольких диапазонов в один новый диапазон с выполнением операции. Объединяемые диапазоны могут находиться как на разных листах, так и в разных книгах. 1. Выделить пустую ячейку листа, начиная с которой будет размещен консолидируемый диапазон. 2. На вкладке |