Текст книги "Excel 2007. Компьютерная шпаргалка"

М. В. Цуранов
Excel 2007

Глава 1
Начинаем работать с Excel

Что такое электронная таблица

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

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

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

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

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

Вводимая информация может быть обработана с помощью:

• форматирования данных, в том числе с использованием различных тем и стилей, а также условного форматирования;

• различных вычислений с использованием мощного аппарата функций и формул;

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

• автоматического расчета промежуточных и общих итогов;

• группировки данных, чтобы затем при необходимости эти данные можно было свернуть или развернуть;

• создания сводных таблиц, которые упрощают представление сложных данных;

• построения графиков и диаграмм;

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

Запускаем Excel 2007

Для запуска Excel существуют два способа:

• чтобы произвести запуск с помощью меню Пуск, надо в подменю Все программы открыть соответствующую программную группу (как правило, эта группа называется Microsoft Office), в которой выбрать Microsoft Office Excel 2007;

• если на рабочем столе есть ярлык

, можно воспользоваться им.

После запуска редактор Excel автоматически откроет пустую книгу с названием Книга 1, которое будет отображено в строке заголовка, расположенной в верхней части окна.

Как работать с файлами в Excel

Рабочая книга в редакторе Excel представляет собой один файл со стандартным расширением .xlsx или .xls (если книга сохранена в режиме совместимости с предыдущими версиями Excel). Команды, при помощи которых осуществляется управление файлами, находятся в меню кнопки Office.

Создаем рабочую книгу

Для создания книги нужно воспользоваться кнопкой

Создать на панели быстрого доступа или же комбинацией клавиш Ctrl+N. В результате будет создана новая рабочая книга со стандартным названием Книга1. Следующая новая книга в том же сеансе работы в Excel будет открыта под именем Книга2 и т.д.

Открываем рабочую книгу

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

Для открытия файла можно также воспользоваться кнопкой

Открыть, которая находится на панели быстрого доступа. При этом появится то же самое диалоговое окно.

Еще одним способом вызова этого диалогового окна является нажатие сочетания клавиш Ctrl+O.

Как сохранить рабочую книгу

Чтобы сохранить файл, можно воспользоваться командой Office Сохранить, либо кнопкой

Сохранить на панели быстрого доступа, либо комбинацией клавиш Ctrl+S. Если сохранение выполняется первый раз, появится окно Сохранить как, где нужно указать имя файла и папку, в которой будет сохранен текущий документ.

При необходимости сохранения существующего файла в другом месте (папке) или под другим именем следует воспользоваться командой Сохранить как из меню кнопки Office или нажать клавишу F12.

Глава 2
Создание и редактирование таблиц Excel

Как ввести данные в таблицу Excel

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

Ячейка характеризуется выравниванием, цветом фона и рамкой (наличием/ отсутствием границ и цветом/толщиной границ). Высота и/или ширина ячейки может быть как фиксированной, так и заданной вами вручную.

В ячейки можно вводить данные двух типов: формулы и константы (числа, текст или значения дат и времени). Прежде чем вводить данные, следует продумать, как их лучше разместить на листе. Удобно сначала составить шапку таблицы:, заполнив названия ее строк и столбцов. Тогда при внесении данных легко будет ориентироваться в таблице. Если вы забудете включить в таблицу какой-либо столбец или строку, их можно будет вставить позже, не теряя уже введенной информации.

Имя ячейки (адрес ячейки) в Excel формируется по аналогии с именованием клеток на шахматной доске: по имени столбца и строки, на которых расположена ячейка. Так, ячейка С3 находится на пересечении столбца С и 3-й строки.

Для примера создадим небольшую таблицу (рис. 2.1).

Рис. 2.1. Пример таблицы

При создании нового документа ячейка А1 автоматически становится активной. Признаки активной ячейки:

• черная рамка вокруг содержимого ячейки;

• подсветка названий строки и столбца.

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

Если сразу же начать вводить текст, он отобразится в этой ячейке. Чтобы ввести текст в другую ячейку, например А2, необходимо ее активировать, т.е. щелкнуть мышью по этой ячейке либо установить в нее курсор, произведя двойной щелчок (ссылка на активную ячейку отображается в поле имени). В примере таблица располагается, начиная с ячейки А2. Следовательно, создав новый документ, нужно перейти на ячейку А2 и ввести №. Завершить ввод содержимого ячейки А2 можно нажатием клавиши Tab, в результате чего курсор переходит на соседнюю ячейку справа – В2, в которую нужно ввести Наименование товара и снова нажать клавишу Tab и т.д.

После того как будет введен текст в ячейку В2, он отобразится и за ее пределами (рис. 2.2а). Но это не означает, что соседние ячейки С2 и D2 заняты. В этом легко убедиться, активировав одну из этих ячеек. Если в активной ячейке содержится информация, она отображается в строке формул. На рис. 2.2б видно, что в строке формул пусто, т.е. ячейка С2 не содержит никакой информации. После того как в нее будет введен текст, информация, выходившая до этого за пределы ячейки В2, скроется (рис. 2.2в).

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

После ввода строки заголовков можно заполнить столбцы данных. При этом завершать ввод данных в ячейку нужно нажатием клавиши Enter, тогда будет активироваться ячейка ниже. Например, можно установить курсор в ячейку А3, ввести значение 1 и нажать клавишу Enter. В результате будет активирована ячейка А4, в которую вводится значение 2 и нажимается Enter, и т.д. Аналогичным образом заполняются остальные столбцы.

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

Примечание. Направление перехода курсора при вводе данных в ячейку указывается при помощи опции Переход к другой ячейке после нажатия клавиши ВВОД и выпадающего списка Направление, расположенных на странице Дополнительно окна Параметры Excel, которое вызывается одноименной командой из меню кнопки Office.

Рис. 2.2. Ввод текста: а – просмотр содержимого ячейки В2; б – переход на пустую ячейку С2; в – ввод текста в ячейку С2

Если информация уже введена в ячейку и требуется лишь добавить или скорректировать ранее введенные данные (например, если в ячейку В2 по ошибке было введено Наименование, а не Наименование товара), необходимо выполнить такие действия (рис. 2.3):

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

2. Установить курсор в то место, где нужно добавить текст, т.е. в конец слова Наименование, нажать клавишу Пробел и ввести слово товара.

3. Для подтверждения внесенных изменений нажать клавишу Enter или Tab либо кнопку

Ввод в строке формул.

Если в процессе редактирования возникла необходимость восстановить исходные данные в ячейке (с выходом из режима редактирования), следует нажать клавишу Esc или щелкнуть по кнопке

Отмена, расположенной в строке формул.

После того как будут внесены все данные, таблица будет выглядеть, как показано на рис. 2.4.

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

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

Однако данный способ неудобен, если таблица имеет много столбцов.

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

Рис. 2.3. Неотформатированная таблица

Рис. 2.4. Таблица с измененными размерами столбцов

Выделяем строки и столбцы

Принцип работы в Excel состоит в том, что, прежде чем применить какое-либо

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

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

Чтобы выделить один столбец, достаточно щелкнуть по его буквенному заголовку. Для выделения нескольких смежных (рядом расположенных) столбцов (в данном случае это столбцы A, B, C, D и E) следует протащить указатель мыши по их заголовкам, удерживая нажатой левую кнопку. Выделение строк таблицы осуществляется аналогичным образом.

Когда нужные столбцы выделены, нужно зайти на вкладку Главная и в группе Ячейки выполнить команду Столбец | Автоподбор ширины столбца. В результате Excel автоматически подберет необходимую ширину для каждого выделенного столбца таблицы, как это показано на рис. 2.1.

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

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

Рис. 2.5. Выделение всех ячеек рабочего листа

Добавляем строки и столбцы

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

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

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

2. Вызвать контекстное меню, щелкнув правой кнопкой мыши по заголовку строки или столбца.

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

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

Рис. 2.6. Диалоговое окно Добавление ячеек

Удаляем строки и столбцы

Для удаления строки или столбца из таблицы нужно:

1. Выделить строку (столбец) щелчком мыши по ее (его) заголовку.

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

2. Вызвать контекстное меню щелчком правой кнопки мыши.

3. В появившемся списке команд выбрать команду Удалить.

Также для удаления строк или столбцов можно воспользоваться командой Главная | Ячейки |Удалить.

Выделяем смежные ячейки

При работе с таблицами очень часто приходится выделять блоки смежных ячеек, чтобы применить к ним некоторую настройку. Например, чтобы в нашей таблице выровнять значения столбцов Цена входящая и Цена отпускная по центру, необходимо сначала выделить блок с цифрами, ячейки которого являются смежными (рис. 2.7а), – блок D3:E7, где двоеточие – это оператор диапазона. Для этого следует:

1. Подвести указатель к угловой ячейке выделяемого диапазона, в данном случае – D3.

2. Нажать левую кнопку мыши и, удерживая ее нажатой, перетащить указатель к последней ячейке блока E7.

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

По центру на панели Выравнивание вкладки Главная.

Аналогичным образом можно выровнять значения столбца №, предварительно их выделив (рис. 2.7б).

Как копировать и перемещать ячейки

В редакторе Excel копирование и перемещение данных осуществляется стандартным для Windows способом, который состоит из следующих этапов:

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

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

Копировать (

Вырезать), расположенной на панели Буфер обмена вкладки Главная).

3. Установить курсор в то место документа, куда будет вставлена переносимая информация.

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

Вставить, расположенной в группе команд Буфер обмена.

Рис. 2.7. Выделение смежных ячеек: а – в двух столбцах; б – в одном столбце

Что такое формат данных

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

Данные в Excel отображаются в таких категориях форматов: Числовой, Экспоненциальный, Финансовый, Денежный, Дата, Время, Текстовый, Процентный, Дробный и Дополнительный. Если необходимо вводить числа без привязки к каким-либо форматам, то можно использовать формат Общий.

Как настроить формат ячейки

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

Рис. 2.8. Группа команд Число

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

Таблица 2.1

Назначение кнопок в группе команд Число

Глава 3
Форматирование, сортировка и фильтрация данных

Форматирование данных с помощью стилей

Excel 2007 позволяет достаточно быстро задать оформление таблицы при помощи встроенных стилей. Рассмотрим их применение на примере таблицы Учет товара (рис. 3.1). Для работы с таблицей будут использованы инструменты, расположенные на панели Стили вкладки Главная.

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

Рис. 3.1. Пример таблицы

В открывшемся списке ячеек (рис. 3.2) нужно выбрать стиль Название из группы Названия и заголовки, выполнив по нему щелчок мьпнью. При этом текст в текущей ячейке примет указанное оформление (рис. 3.3).

Рис. 3.2. Список стилей ячеек

Для оформления таблицы следует установить курсор на какую-либо из ее ячеек и нажать кнопку Форматировать как таблицу, которая расположена на панели Стили вкладки Главная. После открытия списка стилей таблицы (рис. 3.4) надо выбрать щелчком мыши какой-либо стиль оформления.

Рис. 3.3. Форматирование заголовка таблицы с использованием стиля Название

Рис. 3.4. Список стилей таблицы

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

Рис. 3.5. Окно Форматирование таблицы

После нажатия на кнопку OK таблица примет вид, заданный выбранным стилем (рис. 3.6).

Рис. 3.6. Оформление таблицы

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

Как сортировать данные

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

Чтобы отсортировать таблицу по возрастанию (от А до Я или от 0 до 9) либо по убыванию (от Я до А или от 9 до 0) данных какого-либо столбца, можно воспользоваться списком команд кнопки Сортировка и фильтр (рис. 3.7а), расположенной на панели Редактирование вкладки Главная, или группой кнопок панели Сортировка и фильтрация вкладки Данные (рис. 3.7б).

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

Сортировка от А до Я  или

Сортировка от Я до А.

Если требуется сортировка ячеек таблицы по двум или трем столбцам, используется команда Настраиваемая сортировка из списка команд кнопки Сортировка и фильтр (рис. 3.7а) или кнопка Сортировка панели Сортировка и фильтрация (рис. 3.7б).

Например, чтобы отсортировать данные таблицы, приведенной на рис. 3.8а, по возрастанию содержимого столбца Фамилия, а в случае совпадения значений – по возрастанию соответствующих ячеек столбцов Имя и Отчество, следует:

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

2. Нажать кнопку Сортировка на панели Сортировка и фильтрация вкладки Данные.

3. В открывшемся диалоговом окне Сортировка указать, по какому столбцу (или столбцам) и в каком направлении будет выполняться сортировка (рис. 3.8в). В данном случае нужно:

Рис. 3.7. Команды меню Найти и вы/делить: а – из списка команд кнопки Сортировка и фильтр; б – из группы команд Сортировка и фильтрация

4. В поле Сортировать по из раскрывающегося списка выбрать имя столбца, по которому будет выполняться сортировка; в поле Сортировка выбрать пункт Значения (если сортировка осуществляется на основе данных в ячейках); в поле Порядок указать направление сортировки – от А до Я или от Я до А;

5. Воспользоваться кнопкой Добавить уровень, чтобы задать сортировку более чем по одному столбцу. При этом появится еще одна строка (Затем по) для указания настроек сортировки для второго поля. При щелчке по этой строке появится еще одна и т.д.

6. Закрыть диалоговое окно нажатием кнопки OK.,

Результат сортировки приведен на рис. 3.8б.

Рис. 3.8. Сортировка таблицы по трем столбцам: а – начальный список; б – список после сортировки; в – диалоговое окно Сортировка

Фильтрация данных

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

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

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

Используем автофильтр

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

• по значениям списка;

• по формату;

• по условиям.

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

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

Для установки автофильтра необходимо поместить курсор в таблицу и нажать кнопку Фильтр на панели Сортировка и фильтр вкладки Данные. В результате выполнения команды в строку с заголовками столбцов помещаются кнопки раскрьшаюпщхся списков (кнопки со стрелкой) (рис. 3.9).

Рис. 3.9. Режим фильтрации

Существует несколько способов фильтрации данных, которые выбираются из меню фильтрации и подменю Числовые фильтры (рис. 3.10):

• отображение только тех строк в таблице, которые соответствуют выбранному значению в определенном столбце. Например, требуется отобразить только те строки в таблице, для которых значение в столбце Яблоки, кг составляет 300. Для этого нужно нажать на стрелку в заголовке этого столбца и отметить в появившемся списке значение 300 (рис. 3.11а). В данном примере результатом фильтрации будет только одна строка (рис. 3.11 б);

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

• отображение только тех строк таблицы, которые удовлетворяют ограничениям, установленным в одном из режимов подменю Числовые фильтры. Например, чтобы отобразить строки, для которых значение в столбце Яблоки, кг равно или превышает 300 и в то же время меньше 350, необходимо выбрать для этого столбца в подменю Числовые фильтры команду больше или равно – откроется диалоговое окно Пользовательский автофильтр (рис. 3.13).

Рис. 3.10. Меню фильтрации и подменю Числовые фильтры

Рис. 3.11. Отображение строк, соответствующих выбранному значению: а – условие фильтрации; б – результат фильтрации

Рис. 3.12. Диалоговое окно команды Первые 10

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

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

Рис. 3.13. Диалоговое окно Пользовательский автофильтр

Рис. 3.14. Результат применения пользовательского автофильтра

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

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

В Microsoft Excel 2007 в начале раскрывающегося списка автофильтра добавлены команды Сортировка от минимального к максимальному, Сортировка от максимального к минимальному, Сортировка по цвету и Фильтр по цвету (см. рис. 3.10). При выборе одной из этих команд список сортируется в указанном порядке по тому столбцу, в котором была выбрана команда или указан цвет.