Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

41

Microsoft Excel 2013. Уровень 2. Расширенные возможности

скрывать их из вида (например, отображать значки только для высоких показателей и не отображать их для средних и низких значений).

Редактирование условного форматирования

Можно просматривать, удалять, редактировать все правила условного форматирования выделенного диапазона, листа, книги.

1.На вкладке Главная [Home], в группе Стили [Styles], раскрыть кнопку Условное форматирование [Conditional Formatting] и выбрать команду Управление правилами

[Manage Rules].

2.В поле Показать правила форматирования для [Show formatting rules for] выбрать область применения правила: текущий фрагмент [Current Selection], этот лист [This Worksheet] или любой лист этой книги.

3.В списке правил выделить правило:

Изменить правило [Edit Rule] – изменение условия и формата

Удалить правило [Remove Rule] – удаление выделенного правила

 – изменение приоритета выполнения правил (приоритет убывает сверху вниз)

Остановить, если истина [Stop If True] – включить, если после исполнения этого правила другие правила выполнять не надо

Правило Гистограммы можно форматировать: применять сплошную или градиентную заливку и границы, задавать направление столбца "справа налево" или "слева направо", а так же настроить цвет заливки для столбцов с отрицательными значениями и цвет оси, если выбрать после нажатия кнопки Изменить правило [Edit Rule], выбрать Отрицательные значения и ось [Negative values and axis].

42

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Создание условия с использованием формулы

Формула используется, если оформляются ячейки одного столбца (например, текст), а условие задается по ячейкам другого столбца (например, числа, дата и т.д.).

1.Выделить ячейки для оформления (без заголовков и начиная с левой верхней ячейки).

2.На вкладке Главная [Home], в группе Стили [Styles], раскрыть кнопку Условное форматирование [Conditional Formatting] и выбрать команду Создать правило [New Rule].

3.В появившемся окне выбрать: Использовать формулу для определения форматируемых ячеек [Use a formula to determine which cells…].

4.Ввести формулу, задать оформление – кнопка Формат [Format], нажать OK.

ПРИМЕР: Выделить строки в таблице, где разница между количеством на складе и минимальным запасом превышает 10 единиц.

ПРАКТИКУМ:

Открыть файл 01_9 Условное форматирование.

На листах ЗАДАНИЕ1, ЗАДАНИЕ2, ЗАДАНИЕ3, ЗАДАНИЕ4, ЗАДАНИЕ5 выполнить соответствующие задания.

Сохранить сделанные изменения и закрыть файл.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 9-1, 9-2 и 9-3.

43

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Модуль 2. РАБОТА С БОЛЬШИМИ ТАБЛИЧНЫМИ

МАССИВАМИ

Создание и ведение таблиц

Таблицей в Excel называется набор данных на листе, в котором каждому столбцу сопоставлено уникальное имя, а каждая строка представляет совокупность данных. Таблица не может содержать пустых строк и столбцов, между строкой заголовков и данными также не должно быть пустых строк.

При работе с таблицами используются несколько специальных терминов: в каждой таблице содержатся строки, называемые записями, и столбцы, называемые полями.

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

Создание таблицы

1.Выделить любую ячейку таблицы данных.

2.На вкладке Вставка [Insert], в группе Таблицы [Tables]

выбрать Таблица [Table] или на вкладке Главная [Home], в группе Стили [Styles] воспользоваться командой

Форматировать как таблицу [Format As Tables].

3.Указать расположение данных таблицы.

4.ОК.

По умолчанию каждой таблице присваивается уникальное имя вида Таблица1, Таблица2 и т.д. Имя можно изменить.

1.Выделить ячейку таблицы.

2.На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле Имя таблицы и нажать Enter . Требования к именам таблиц

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

44

Microsoft Excel 2013. Уровень 2. Расширенные возможности

К таблице можно применять различные стили оформления, для этого необходимо выделить ячейку таблицы и на вкладке Конструктор [Design], выбрать нужное оформление в поле Стили таблиц [Table Styles] или на вкладке Главная [Home], в группе

Стили [Styles] с помощью команды Форматировать как таблицу [Format As Tables].

Вычисления в таблицах

Строка итогов позволяет обрабатывать данные каждого поля различными функциями.

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

1. На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].

2. В строке Итог [Total] выбрать для вычисления по полю нужную функцию.

Для ввода новых записей в конец таблицы, следует отключить строку итогов – убрать флажок Строка итогов [Total Row]. Введенные данные таким образом, будут автоматически расширять диапазон таблицы. Для создания нового поля в конце таблицы (столбец данных) отключать строку не нужно.

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

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

1.Выбрать Файл [File], Параметры [Options].

2.В разделе Формулы [Formulas]в группе Работа с формулами [Working with formulas], выбрать

Использовать имена таблиц в формулах [Use table name in formulas].

3.OK.

45

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Преобразование таблицы в обычный диапазон

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

Для преобразования в диапазон, необходимо выполнить последовательность действий:

1. На вкладке Конструктор [Design] в группе Сервис [Tools], выбрать Преобразовать в диапазон

[Convert to Range].

2.Выбрать Да [Yes] в ответ на вопрос о преобразовании таблицы в обычный диапазон.

ПРАКТИКУМ:

Открыть файл 02_1 Таблицы.

На листе ЗАДАНИЕ1, ЗАДАНИЕ2, ЗАДАНИЕ3 выполнить соответствующие задания.

Сохранить внесенные изменения и закрыть файл.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 10.

Удаление дубликатов

По тем или иным причинам, в больших таблицах нередко появляются записи-дубликаты. Это может быть связано с недостаточно добросовестным вводом данных, случайным копированием и т.д. Анализировать данные с такими дубликатами бессмысленно, поэтому их необходимо удалять.

1.Выделить любую ячейку таблицы с повторениями.

2.На вкладке Данные [Data] в группе Работа с данными [Data Tools] нажать кнопку

Удалить дубликаты [Remove Duplicates].

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

4.ОК.