Применение автоформата к диапазону

1. Выберите диапазон, который нужно отформатировать.

2. В меню Формат выберите команду Автоформат.

Выберите нужный формат.

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

Условное форматирование – изменение параметров форматирования ячеек при выполнении определенных условий.

Для задания условного форматирования:

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

2. В меню Формат выберите команду Условное форматирование.

3. Выполните одно из следующих действий:

Чтобы в качестве условия форматирования использовать значения выделенных ячеек, выберите параметр значение, задайте операцию сравнения, а затем в необходимое поле введите значение. Ввести можно заданное значение или формулу, но перед формулой необходимо поставить знак равенства (=).

Формула используется в качестве критерия форматирования для оценки данных или условий, отличных от значений выделенных ячеек. Слева из поля выберите параметр формула, а затем в поле справа введите формулу. Формула должна принимать логическое значение ИСТИНА или ЛОЖЬ.

4. Выберите команду Формат.

5. Выберите тип шрифта, его цвет, подчеркивание, рамку и затенение ячеек или узоры.

Выбранные форматы будут применены, только если значение ячейки отвечает поставленному условию или если формула принимает значение ИСТИНА.

6. Для добавления условия выберите команду Добавить, а затем повторите шаги 3-5.

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

1.3. Копирование (перемещение) данных таблицы

Для копирования в прилегающую область ячеек:

1. Выделить ячейки с данными;

2. Протащить выделение за маркер заполнения вправо или вниз.

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

Для копирования (перемещения) в неприлегающую область ячеек:

1. Выделить ячейки с данными.

2. Выполнить в меню Правка команду Копировать(Вырезать).

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

4. Выполнить в меню Правка команду Вставить.

Для перемещения данных ячеек в другую область с помощью мыши (без использования буфера обмена данными):

1. Выделить ячейки с данными.

2. Подвести курсор мыши внутрь области выделения, так чтобы курсор принял вид белой стрелки, направленной влево и вверх по диагонали.

3. Переместить мышь в нужном направлении, удерживая нажатой клавишу CTRL.

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

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

1. Выделить ячейки с данными.

2. Выполнить в меню Правка команду Копировать(Вырезать).

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

4. Выполнить в меню Правка команду Специальная вставка, установить нужный переключатель.

Копирование форматов в другие ячейки

Выделите ячейки, содержащие копируемый формат.

Нажмите кнопку Формат по образцу на панели инструментов Стандартная.

Выделите ячейки, которые должны иметь тот же формат.

1.4. Заполнение рядов

В Excel предусмотрена возможность простого и быстрого создания прогрессий.

Для создания числовых рядов с заданным шагом:

1. Укажите первую ячейку заполняемого диапазона и введите начальное значение.

2. Чтобы задать приращение, укажите вторую ячейку ряда и введите соответствующее ей значение. Величина приращения будет задана разностью значений, находящихся в этих ячейках.

3. Выделите ячейку или ячейки, содержащие начальные значения.

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

Создание пользовательского списка автозаполнения или порядка сортировки

Существует возможность создания пользовательского списка автозаполнения или порядка сортировки либо из существующих на листе элементов, либо с помощью непосредственного ввода списка.

1. Если ряд элементов, который необходимо представить в виде пользовательского списка автозаполнения, был набран заранее, то выделите его на листе.

2. Выберите команду Параметры в меню Сервис, а затем откройте вкладку Списки.

3. Чтобы использовать выделенный список, нажмите кнопку Импорт.

4. Чтобы создать новый список, выберите Новый список из списка Списки, а затем введите данные в поле Элементы списка, начиная с первого элемента. После ввода каждой записи нажимайте клавишу ENTER. Нажмите кнопку Добавить после того, как список будет набран полностью.

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

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

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

1. Выделить нужный лист.

2. В контекстном меню листа выбрать нужную команду.

Лекция 2. Основные приемы работы с таблицами и диаграммами в MS Excel

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

2.1. Типы ссылок

Относительные ссылки

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

Примеры относительных ссылок:

G3 – ссылка на одну ячейку;

A1:C3 – ссылка на диапазон из 9 ячеек;

A1;C3 – ссылка на две ячейки.

Абсолютные ссылки

Абсолютными называются ссылки, которые не изменяются при копировании формулы в другую ячейку. Например, если имеется формула, умножающая содержимое ячейки A5 на ячейку C1 (=A5*C1), то при копировании формулы в другую ячейку изменятся обе ссылки. Для создания абсолютной ссылки на ячейку C1, поставьте знак доллара ($) перед той частью, которая не должна изменяться. Чтобы создать абсолютную ссылку на ячейку C1, поместите знак доллара так, как показано в примере: =A5*$C$1.

Смешанные ссылки

Смешанными называются ссылки, в которых только одна из компонент адреса ячейки является абсолютной. Например, $C1 – смешанная ссылка, где абсолютным является адрес столбца ( т.е. при копировании формулы или функции, содержащей такую ссылку, по горизонтали, адресация на ячейку С1 изменятся не будет. При копировании по вертикали номер строки будет увеличиваться при копировании вниз и уменьшаться при копировании вверх).

2.2. Диаграммы

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

Пример таблицы и построенной по данным таблицы диаграммы.

Создание диаграммы

1. Выделите ячейки, содержащие исходные данные, которые должны быть отражены на диаграмме.

Область данных должна быть прямоугольной. Можно использовать неприлегающее выделение.

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

2. Выберите в меню Вставка команду Диаграмма.

3. Следуйте инструкциям мастера диаграмм.

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

Структура диаграммы

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

Маркеры рядов – графическое изображение числовых рядов на диаграмме.

Выделение диаграммы и отдельных элементов диаграммы выполняется щелчком мыши.

Форматирование элементов диаграммы:

1. Двойным щелчком мыши по элементу открыть окно форматирования элемента.

2. Выбрать необходимые параметры.

Графики функций

Рассмотрим создание графика функции на примере Y = x².

Создайте таблицу, включающую значения аргумента и значения функции. Значения функции вычисляется только для первого аргумента – в ячейку В2 по формуле =А2^2. Затем эта формула копируется в область В3:С10.

Затем мастером нужно построить диаграмму точечного типа.

	A	B
	x	Y
	-4
	-3
	-2
	-1

2.3. Сортировка данных таблицы. Подведение итогов

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

Лучше всего, если сортируемый массив данных будет иметь заголовки столбцов.

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

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

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

1. Укажите ячейку в сортируемом списке.

2. В меню Данные выберите команду Сортировка.

3. Укажите столбцы сортировки в полях Сортировать по и Затем по.

4. Чтобы отсортировать данные по более, чем трем столбцам одновременно, отсортируйте список сначала по трем наименее значимым столбцам. Например, если список содержит сведения о сотрудниках и его требуется отсортировать по полям "Отдел", "Должность", "Фамилия" и "Имя", сортировку нужно будет выполнить дважды. Выберите сначала Имя в поле Сортировать по и отсортируйте список. Затем выберите Отдел в поле Сортировать по, Должность в поле Затем по, Фамилия в поле В последнюю очередь, по и отсортируйте список еще раз.

5. Выберите другие параметры сортировки и нажмите кнопку OK.

6. Повторите шаги 2 — 4 для следующих более значимых столбцов.

Подведение промежуточных итогов

1. Отсортируйте список по столбцу, для которого необходимо подвести промежуточный итог. Например, чтобы просуммировать единицы продукции, проданные каждым продавцом, в списке продавцов, объемов продаж и количества проданного, отсортируйте список по столбцу продавцов.

2. Укажите ячейку в списке.

3. Выберите команду Итоги в меню Данные.

4. Выберите столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.

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

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

Подведение "вложенных" или многоуровневых итогов

Чтобы "вложить" или вставить итог для групп, находящихся внутри уже существующих групп, необходимо отсортировать список.

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

2. Вставьте автоматические итоги для первого столбца, содержащего группы суммируемых данных. Этот столбец при сортировке списка должен быть указан в поле Сортировать по. (В примере, приведенном на шаге 1, первым столбцом должен быть столбец регионов.)

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

4. Укажите ячейку в этом списке.

5. Выберите команду Итоги в меню Данные.

6. Выберите следующий столбец, по которому необходимо подвести итоги, из списка При каждом изменении в.

7. Снимите флажок Заменить текущие итоги, а затем нажмите кнопку OK.

8. Повторите шаги с 4 по 7 для каждого столбца, по которому необходимо подвести итоги.

Удаление промежуточных итогов

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

2. Выделите ячейку в списке, содержащую промежуточный итог.

3. Выберите команду Итоги в меню Данные.

4. Нажмите кнопку Убрать все.

2.4. Списки

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

Рекомендации по созданию списка на листе книги

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

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

2. Необходимо спроектировать список таким образом, чтобы все строки содержали подобные объекты в одном столбце.

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

4. Важные данные следует размещать слева или справа от списка; данные могут быть скрыты при фильтрации списка.

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

При форматировании данных в списке рекомендуется:

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

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

3. В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

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

5. При добавлении новых строк в конец списка Excel использует согласованные форматы и формулы. Чтобы это произошло, три из пяти предыдущих ячеек должны иметь тот же формат или содержать ту же формулу.

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

Фильтры могут быть использованы только для одного списка на листе.

Просмотр группы строк списка с помощью фильтров

1. Укажите ячейку в фильтруемом списке.

2. Выберите пункт Фильтр в меню Данные, а затем команду Автофильтр.

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

4. Выберите значение в списке.

5. Повторите шаги 3 и 4, чтобы ввести дополнительные ограничения для значений в других столбцах

6. Чтобы отфильтровать список по двум значениям в одном столбце или применить отличные от равно операторы сравнения, нажмите кнопку со стрелкой, а затем выберите пункт Условие.

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

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

Отображение подмножества строк с использованием условий отбора

Чтобы отобрать строки из списка с использованием одного или двух условий отбора для одного столбца:

1. Выберите команду Фильтр в меню Данные, затем команду Автофильтр. Нажмите кнопку со стрелкой в столбце, в котором находятся сравниваемые данные, а затем из раскрывающего списка выберите пункт Условие.

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

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

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

Удаление фильтра из списка

Чтобы удалить фильтр для одного столбца списка, нажмите на кнопку со стрелкой, а затем выберите из развернувшегося списка Все.

Чтобы удалить фильтры для всех столбцов списка, выберите пункт Фильтр в меню Данные, а затем — команду Показать все.

Чтобы удалить автофильтр из списка, выберите пункт Фильтр в меню Данные, а затем — команду Автофильтр.

2.6. Поиск информации с помощью расширенного фильтра

По отношению к автофильтру расширенный фильтр имеет преимущества:

· По одному столбцу базовой таблицы можно установить три и более условий.

· В условиях можно использовать вычисляемые выражения.

· Условия хранятся в виде отдельной таблицы.

· Результаты фильтрации также могут находиться в отельной таблице.

1.Отдельно от исходной таблицы создать таблицу условий. Заголовками столбцов этой таблицы будут являться заголовки столбцов исходной таблицы, по значению которых должны устанавливаться условия. Записи этой таблицы – сами условия. В случае, если по одному столбцу необходимо проверить и более условий, объединенных союзом и, каждое из таких условий должно указываться в отдельном столбце таблицы условий под своим заголовком:

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

Заголовками столбцов таблицы условий должны быть абсолютно идентичны заголовкам соответствующих столбцов исходной таблицы.

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

Знак = в таблице условий не ставится. Пробел допускается, если есть текст.