Основные элементы электронной таблицы Excel
Microsoft Excel (в дальнейшем просто - Excel) - это программа выполнения расчетов и управления так называемыми электронными таблицами.
Электронная таблица- основное средство, используемое для обработки и анализа цифровой информации средствами вычислительной техники. Хотя электронные таблицы в основном связаны с числовыми или финансовыми операциями, они также могут использоваться для различных задач анализа данных, предоставляя пользователю большие возможности по автоматизации обработки данных.
Excel позволяет выполнять сложные расчеты, в которых могут использоваться данные, расположенные в разных областях электронной таблицы и связанные между собой определенной зависимостью. Для выполнения таких расчетов в Excel существует возможность вводить различные формулы в ячейки таблицы. Excel выполняет вычисления и отображает результат в ячейке с формулой. Доступный диапазон формул - от простого сложения и вычитания до финансовых и статистических вычислений.
Важной особенностью использования электронной таблицы является автоматический пересчет результатов при изменении значений ячеек. Например, можно использовать Excel для выполнения финансовых расчетов, учета и контроля кадрового состава организации и т. д. Excel также может строить и обновлять графики, основанные на введенных числах.
Файл, с которым работает Excel, называется книгой. Книга, как правило, состоит из нескольких рабочих листов, которые могут содержать таблицы, тексты, диаграммы, рисунки.
Книга является хорошим организационным средством. Например, можно в одной книге собрать все документы (рабочие листы), относящиеся к определенному проекту (задаче), или все документы, которые ведутся одним исполнителем. Основа рабочего листа (рис. 79) представляет собой сетку из строк и столбцов. Ячейка образуется пересечением строки и столбца. Выделенная мышью ячейка (ячейки) называется активной (на рис. 77 активная ячейка выделена рамкой).
Рис. 79. Элементы таблицы
Строка в рабочем листе идентифицируется именем (цифрой), которое появляется на левой стороне рабочего листа.
Столбец в рабочем листе также идентифицируется именем (буквами латинского алфавита), которое появляется вверху рабочего листа.
Рабочий лист книги Excel может содержать до 65 536 строк и 256 столбцов.
Ячейка - основной элемент таблицы - имеет свой уникальный адрес, состоящий из номера столбца и строки, например Е4.
Каждая ячейка содержит один элемент информации, будь то цифровое значение, текст или формула.
Окно Excel
Команды запуска приложений Office описаны в предыдущей главе. При входе в программу открывается окно Excel и на экран выводится пустая рабочая книга с именем Книга 1.
При открытии ранее созданного файла в окне Excel появляется книга с введенными данными.
Рабочая книга в Excel - это файл, в котором хранятся и анализируются данные. Файл рабочей книги состоит из нескольких рабочих листов, которые могут содержать таблицы, тексты, диаграммы или рисунки. Каждый рабочий лист идентифицируется именем, которое появляется на ярлычке листа.
Общие элементы окна приложений Office описаны в предыдущих главах. Рассмотрим подробнее специфические элементы окна Excel (рис. 80).
Рис. 80. Окно Excel
Строка состояния содержит информацию об активном документе, выбранной команде меню, индикаторах режимов клавиатуры. В ней пользователь получает сообщения о том, как выполнить начатую команду до конца и просмотреть промежуточные результаты некоторых вычислений.
Строка формул показывает формулу (если она присутствует в ячейке) или данные, содержащиеся в активной ячейке. В строке формул можно вводить и редактировать текст, числа и формулы.
В активную ячейку можно вводить и редактировать данные непосредственно в ячейке или в строке формул.
Ссылка на ячейку - адрес точки пересечения ее столбца и строки.
Например, ссылка на самую левую верхнюю ячейку на рабочем листе Excel - А1.
Ссылка на активную ячейку будет появляться в поле имени.
Ярлыки используются для идентификации листа в рабочей книге, По умолчанию листы нумеруются Лист 1, Лист 2 и т. д.
Кнопки прокрутки слева внизу окна используются для просмотра ярлыков листов и для перемещения между листами в рабочей книге, содержащей большое количество листов.
Создание новой рабочей книги
Так же как и в Word, каждая рабочая книга, созданная Excel, основывается на некоторой модели, называемой шаблоном. По умолчанию Excel основывает новую рабочую книгу на шаблоне с именем Книга. Шаблон сохраняет информацию о форматировании ячеек и рабочих листов, а также используемых панелях инструментов.
Щелкнув кнопку Создать (New) на стандартной панели инструментов, можно создать новую, пустую рабочую книгу на базе шаблона, принятого по умолчанию.
Создание книги на основе выбранного шаблона
1. Выберите команду Создать (New) из меню Файл,
2. Выберите нужную закладку для определения категории шаблона создаваемой рабочей книги.
3. Затем выберите шаблон или мастер, на которых будет основываться новая рабочая книга.
Шаблоны могут быть созданы предварительно и могут содержать элементы форм для отчетов, включающих накладные, долговые обязательства, финансовые отчеты и др., принятые в конкретной организации (рис. 81).
Рис. 81. Шаблон Платежные формы (лист Накладная)
Перемещение внутри рабочей книги
Окно рабочей книги показывает только часть листа открытой электронной таблицы. Чтобы просмотреть различные части листа, используются вертикальный или горизонтальный бегунки для прокрутки листа по вертикали и по горизонтали соответственно. Положение бегунка Показывает относительное место размещения окна экрана внутри рабочего листа. Использование бегунков изменяет область просмотра, но не перемещает активную ячейку.
В больших таблицах данные могут находиться и за пределами видимого окна экрана. Для перемещения активной ячейки с одновременной прокруткой окна используются клавиши и клавиатурные сочетания.
Таблица 16. Клавиши и клавиатурные сочетания перемещения активной ячейки
| Клавиши | Перемещение |
| → или Tab | Вправо на одну ячейку |
| ↓ или Enter | Вниз на одну ячейку |
| ↑ или ← | Вверх на одну ячейку или влево на одну ячейку |
| Pg Up или Pg Dn | Вверх или вниз на одно экранное "окно" |
| End + → или Ctrl + → | Вправо до конца строки в области, содержащей данные |
| End + ← или Ctrl + ← | Влево до конца строки в области, содержащей данные |
| End + ↑ или Ctrl + ↑ | К началу столбца в области, содержащей данные |
| End + ↓ или Ctrl + ↓. | К концу столбца в области, содержащей данные |
| Home | Самая левая ячейка в строке |
| Ctrl + Home | Ячейка А1 |
| Ctrl + End | Нижняя, правая ячейка, используемая на рабочем листе |
Чтобы перейти к любой ячейке в рабочей книге, можно использовать ссылку на ячейку (рис. 82).
Рис. 82. Диалоговое окно Переход
1. Выберите команду Перейти из меню Правка (Edit, Go To) или нажмите на клавиатуре клавишу F5.
2. Введите ссылку на ячейку (например, ячейка С2) и нажмите ОК.
Эту операцию удобно использовать для перехода к ячейке с известным адресом в больших таблицах, содержащих несколько десятков строк или столбцов.
Для перемещения между листами в рабочей книге используются ярлыки листов и кнопки прокрутки ярлыков. Чтобы перейти на другой лист, нужно щелкнуть по ярлыку этого листа.
Ввод и редактирование данных
Типы данных
Excel позволяет вводить в ячейки три типа данных: числа, текст, формулы. Текст может использоваться для заголовков таблиц, объяснения или пометок на рабочем листе. Если Excel не распознает тип данных как числовой или как формулу, то данные воспринимаются как текст.
Числа используются для представления цифровой информации и могут быть введены в различных форматах: общем, денежном, финансовом, процентном и т. д. Дата и время могут также рассматриваться как числа.
Формулы, введенные в ячейку, производят вычисления, управляют работой базы данных, проверяют свойства и значения ячеек и используются для задания связи между ячейками и массивами с помощью адресных ссылок.
Любая формула начинается со знака (=). Если в ячейку введена формула, то по умолчанию ячейка будет показывать результат расчета.
Ввод данных в ячейку
Данные набираются непосредственно в активной ячейке, при этом они отображаются в строке формул. Также данные могут набираться и в строке формул.
Ввод данных
1. Щелкните по ячейке.
2. Введите данные, например Отчет за 2001 год.
3. Нажмите клавишу Enter.
4. Для отмены ввода нажмите Esc.
При вводе в строке формул щелкните кнопку Enter- √ для подтверждения, а для отмены ввода нажмите кнопку Отмена - ×.
Нажатие клавиш перемещения курсора или щелчок по другой ячейке Сбудет всегда приводить к сохранению набранных данных в активной ячейке перед перемещением к следующей.
Текст, ширина которого слишком велика, чтобы вместиться в текущую ячейку, будет зрительно накладываться на соседние ячейки, хотя фактически он будет содержаться в одной ячейке. Excel ограничивает текст или формулы в ячейке длиной в 255 символов.
Числа, которые слишком велики, чтобы быть показанными внутри текущей ячейки, будут отображаться как последовательность символов # # # #. Чтобы показать числовое значение в ячейке, надо увеличить ширину столбца (см. раздел "Форматирование ячеек").
Удалить содержимое ячейки можно с помощью клавиши Delete.
Выделение ячеек
Множество операций, таких, как вставка строк или столбцов, удаление, копирование или перемещение ячеек, требует выделения одной или нескольких ячеек перед началом операции.
Область выделения может быть как отдельной ячейкой, так и занимать целую рабочую книгу. Активная ячейка всегда является частью выделенной области. Область выделения должна быть прямоугольной и может быть определена как:
одна или несколько ячеек; один или несколько столбцов; одна или несколько строк; один или несколько рабочих листов.Ссылка на прямоугольную область ячеек состоит из адресов начальной и конечной ячеек, разделенных двоеточием (:).
Табл. 17 иллюстрирует некоторые из возможных комбинаций.
Таблица 17. Обозначения областей таблицы
| Элементы | Обозначения | Область определения | |
|
Ячейка Группа ячеек |
В2 или В2:В2 В2:В23 В2:05 |
1 ячейка 22 ячейки 12 ячеек |
|
|
Строка Строки |
1 1:2 |
1 строка 2 строки |
|
|
Столбец Столбцы |
А:А А:С |
1 столбец 3 столбца |
|
| Рабочий лист |
Лист1 Лист1:ЛистЗ |
1 рабочий лист 3 рабочих листа |
|
| Множество | А2:С2,С4:D6,В8 | 10 ячеек | |
Ячейки могут быть выделены с помощью мыши или клавиатуры или их комбинации (табл. 18). Выделенные ячейки будут отличаться цветом.
Таблица 18. Выделение областей таблицы
| Область выделения | Способ выделения |
| Отдельная ячейка | Щелкните мышью в ячейке |
| Группа ячеек | Щелкните, мышью в ячейке; не отпуская мышь, перетащите от первой ячейки до последней или щелкните первую ячейку и при нажатой клавише Shift щелкните по последней ячейке |
| Столбец | Щелкните по заголовку столбца |
| Соседние столбцы | Щелкните по первому заголовку столбца; не отпуская мышь, перетащите от первого заголовка столбца к последнему или щелкните по первому заголовку столбца и при нажатой клавише Shift щелкните по последнему заголовку столбца |
| Строка | Щелкните по заголовку строки |
| Соседние строки | Щелкните по заголовку строки; не отпуская мышь, перетащите от первого заголовка строки к последнему или щелкните по первому заголовку строки и при нажатой клавише Shift щелкните по последнему заголовку строки |
| Все ячейки в текущем рабочем листе | Щелкните по кнопке на пересечении заголовков строк и столбцов |
| Несмежные столбцы | Выберите первый столбец, нажмите Ctrl и выберите следующие столбцы |
| Несмежные строки . | Выберите первую строку, нажмите Ctrl и выберите следующие строки |
| Несмежные ячейки | Выберите первую группу ячеек, нажмите Ctrl и выберите следующую группу ячеек |
Для отмены выделения щелкните по любой ячейке.
Для выделения области с помощью мыши в том случае, если область выделения превышает размер окна, как и в других приложениях, используется автопрокрутка. Это означает, что если указатель мыши выходит за границы окна, то лист будет автоматически прокручиваться в этом направлении.
Редактирование содержимого ячейки
Редактирование содержимого ячейки может осуществляться либо в ячейке, либо в строке формул. Режимы Ввод и Правка показываются в строке состояния.
Для редактирования следует дважды щелкнуть в ячейке, которую нужно отредактировать, или щелкнуть в строке формул.
Указатель мыши может быть использован для перемещения в место редактирования. Кроме того, в режиме редактирования могут использоваться следующие клавиши:
| Клавиша | Действие |
| F2 | Активизация режима редактирования |
| → | Перемещение вправо на один символ |
| ← | Перемещение влево на один символ |
| End | Перемещение в конец символьной строки |
| Home | Перемещение в начало символьной строки |
| Ctrl + → | Перемещение на одно слово вправо |
| Ctrl + ← | Перемещение на одно слово влево |
| Delete | Удаление символа справа от места вставки |
| Backspace | Удаление символа слева от места вставки |
| Enter | Ввод отредактированной информации в ячейку |
| Esc | Отмена любых изменений содержимого ячейки |
Так же как и в Word, нажатие на клавишу Insert осуществляет переключение между режимами вставки и замещения.
Отмена и повторение действий
Excel позволяет отменить изменения, сделанные в рабочей книге. Хотя эта функция применима к большинству команд, для нее существуют исключения (например, нельзя отменить удаление и переименование листа).
Команда Отменить (Undo) в меню Правка (Edit) контекстно зависима. Когда пользователь набирает или редактирует данные в строке формул, в меню Правка будет предложена команда, соответствующая последней выполняемой операции.
На стандартной панели для отмены последней команды следует нажать
кнопку 
После выбора команды Отменить в меню Правка команда изменится на команду Вернуть (Redo).
Вставка строк и столбцов
Дополнительные строки или столбцы могут быть вставлены по мере необходимости в любом месте таблицы. Команда Вставить (Insert) в меню Правка (Edit) может использоваться для вставки нового столбца слева от текущего столбца или новой строки над текущей строкой.
Несколько столбцов и строк может быть добавлено при выделении области, которая включает больше чем один столбец или строку.
1. Выделите столько столбцов или строк, сколько необходимо вставить.
2. Выберите Вставка, Строки (Insert, Rows) или Вставка, Столбцы (Insert, Columns) или нажмите комбинацию клавиш Ctrl и + на цифровой клавиатуре.
Для удаления строк или столбцов:
1. Выберите строки или столбцы для удаления.
2. Выберите Правка, Удалить (Edit, Delete) или нажмите комбинацию клавиш Ctrl и - на цифровой клавиатуре.
При вставке и удалении столбцов или строк смещаются адреса оставшихся данных в таблице, поэтому при вставке или удалении нужно быть особенно внимательными.
Перемещение и копирование данных
Перемещение и копирование данных является одной из основных операций, используемых при работе с табличными данными, при этом копируется на новое место не только содержимое ячеек, но и их форматирование.
Перемещение и копирование содержимого ячеек можно осуществить двумя способами:
командами меню Правка (Edit); перетаскиванием с помощью мыши.Как только пользователь выделит ячейку и выберет команду Вырезать или Копировать (Cut или Сору) в меню Правка (Edit), Excel произведет копирование содержимого ячейки в буфер обмена.
При перемещении данные исходных ячеек будут вставлены на новое место.
Копирование данных используется для дублирования информации. Как только содержимое одной ячейки скопировано, оно может быть вставлено в отдельную ячейку или в область ячеек неоднократно. Кроме того, выбранная область окружается подвижной пунктирной границей, которая будет оставаться до тех пор, пока операция не будет завершена или отменена.
Граница выглядит как пульсирующая точечная рамка, окружающая выделенный объект. Вставка содержимого ячеек возможна только тогда, когда существует эта граница.
Использование команды Вставить (Paste) в меню Правка (Edit) после выбора команды Вырезать (Cut) будет отключать границу.
Использование команды Вставить (Paste) после команды Копировать (Сору) не будет отключать границу, т.е. пользователь может продолжать указывать другие места назначения для вставки данных и применять команду Вставить снова.
Нажатие клавиши Enter будет вставлять выделенную область на новое место, указанное мышью, и отключать границу.
Нажатие клавиши Esc будет отменять операцию копирования в буфер и отключать границу.
Когда вставляются данные из более чем одной ячейки, требуется указать только левый верхний угол области ячеек на рабочем листе, в которую осуществляется вставка.
Перемещение и копирование с помощью меню
1. Выделите область ячеек для копирования.
2. Для перемещения щелкните кнопку Вырезать -
Вырезать из меню Правка (Edit, Cut). Excel показывает
границу, окружающую область вырезания.
3. Для копирования щелкните по кнопке Копировать -
Правка, Копировать (Edit, Copy). Excel показывает границу, окружающую
область копирования.
4. Выберите место (верхнюю ячейку области) вставки.
5. Щелкните по кнопке
Вставить -
нажмите Enter или выберите Правка, Вставить (Edit, Paste).
Возможность Перетаскивания (Drag and Drop) позволяет перемещать или копировать содержимое выделенных ячеек с помощью мыши. Эта возможность особенно полезна при .перемещении и копировании на небольшие расстояния (в пределах видимой области рабочего листа).
Перемещение и копирование перетаскиванием
1. Выберите область ячеек для перемещения.
2. Переместите указатель мыши на обрамление выделения.
3. Перетащите выделение с помощью указателя на новое место. Область ячеек будет перемещена на новое место.
4. Если при перетаскивании удерживать нажатой клавишу Ctrl, область ячеек будет скопирована на новое место.
Специальное копирование данных
Специальное копирование данных между файлами включает в себя команду Специальная вставка (Paste Special) в меню Правка (Edit). В отличие от обычного копирования данных с помощью команды Вставить (Paste) команда Специальная вставка (Paste Special) может быть использована для вычислений и преобразования информации, а также для связывания данных рабочих книг (эти возможности будут рассмотрены в следующей главе).
Команда Специальная вставка (Piste Special) часто используется и для копирования атрибутов форматирования ячейки.
1. Выделите ячейку или ячейки для копирования.
2. Выберите Правка, Копировать (Edit, Copy).
3. Выделите ячейку или ячейки, в которые будут помещены исходные данные.
4. Выберите Правка, Специальная вставка (Edit, Paste Special). Диалоговое окно Специальная вставка содержит несколько параметров для вставки данных (рис. 83).
Рис. 83. Специальная вставка
5. Установите необходимые параметры, например форматы (при колировании форматов изменяется только форматирование, а не значение ячеек).
6. Выберите ОК.
Первая группа параметров диалогового окна Специальная вставка (Paste Special) позволяет выбрать содержимое или атрибуты форматирования, которые необходимо вставлять. При выборе параметра Все (АИ) вставляются содержимое и атрибуты каждой копируемой ячейки на новое место. Другие варианты позволяют вставлять разные комбинации содержимого и/или атрибутов.
Вторая группа параметров применяется только при вставке формул или значений и описывает выполняемые операции над вставляемой информацией в ячейки, которые уже содержат данные (табл. 19).
Таблица 19. Параметры команды Специальная вставка
| Параметр | Результат вставки |
| Сложить | Вставляемая информация будет складываться с существующими значениями |
| Вычесть | Вставляемая информация будет вычитаться из существующих значений |
| Умножить | Существующие значения будут умножены на вставляемую информацию |
| Разделить | Существующие значения будут поделены на вставляемую информацию |
| Пропускать пустые ячейки | Можно выполнить действия только для ячеек, содержащих информацию, т. е. при специальном копировании пустые ячейки не разрушат существующие данные |
| Транспонировать | Ориентация вставляемой области будет переключена со строк на столбцы и наоборот |
Выбор Нет (None) означает, что копируемая информация просто замещает содержимое ячеек. Выбирая другие варианты операций, получим, что текущее содержимое будет объединено со вставляемой информацией и результатом такого объединения будет новое содержимое ячеек.
Упражнение
Выполнение вычислений с помощью команды "Специальная вставка"
Введите данные, как показано в табл. 20.
Таблица 20. Исходные данные
| А | В | С | D | Е | F | G | Н | |
| 1 | ||||||||
| 2 | 5 | 2 | 1 | 2 | ||||
| 3 | 12 | 3 | 10 | 3 | ||||
| 4 | 8 | 2 | 15 | 4 |
1. Выделите область для копирования А2:А4.
2. Выберите Правка, Копировать (Edit, Copy).
3. Щелкните ячейку В2 (верхний левый угол области, в которую будут помещены данные).
4. Выберите Правка, Специальная вставка (Edit, Paste Special).
5. Установите параметр Умножить.
6. Нажмите ОК. Обратите внимание, что на экране осталась граница области выделения.
7. Щелкните ячейку С2, которая будет началом области вставки.
8. Выберите Правка, Специальная вставка (Edit, Paste Special) и установите параметр Транспонировать.
9. Скопируйте самостоятельно форматы столбца G в столбец Н и получите табл. 21.
Таблица 21. Результат команды Специальная вставка
| А | B | C | D | Е | F | G | Н | |
| 1 | ||||||||
| 2 | 5 | 10 | 5 | 12 | 8 | 1 | 2 | |
| 3 | 12 | 36 | 10 | 3 | ||||
| 4 | 8 | 16 | 15 | 4 |
Автоматизация работы в Excel
Автозаполнение
Функция Автозаполнение позволяет заполнять данными область ячеек по определенным правилам. Excel осуществляет поиск правила заполнения введенных данных для того, чтобы определить значения пустых ячеек. Если вводится одно начальное значение образца заполнения, то выделяется одна ячейка, если список с интервалом изменения данных, то необходимо выделить две ячейки, заполненные соответствующими данными.
Автозаполнение
1. Введите данные в начальную ячейку или смежные ячейки (если задается правило заполнения).
2. Выберите ячейку или ячейки, в которых установлено правило заполнения.
3. Установите курсор мыши на Маркере заполнения (рис. 84), маленьком квадратике в правом нижнем углу активной ячейки.
4. Перетащите мышью Маркер заполнения (по горизонтали или по вертикали) до последней ячейки, которую хотите заполнить по образцу.
5. Отпустите кнопку мыши.
Рис. 84. Маркер заполнения активной ячейки
Есть один полезный прием автозаполнения - это перетаскивание маркера заполнения при нажатой правой клавише мыши. При этом появляется контекстное меню, которое поможет выбрать способ автозаполнения (рис. 85).
Рис. 85. Контекстное меню автозаполнения
Упражнение
Для каждой группы данных в зависимости от правила заполнения требуется ввести данные в одну или две смежные ячейки. Используйте автозаполнение для ввода следующих данных (табл. 21).
Таблица 21. Примеры автозаполнения
| Начальное значение | Ряды, полученные с помощью автозаполнения | |||
| среда | четверг | пятница | суббота | воскресенье |
| Январь | Февраль | Март | Апрель | Май |
| Квартал 1 | Квартал 2 | Квартал 3 | Квартал 4 | Квартал 1 |
| 1-я группа | 2-я группа | 3-я группа | 4-я группа | 5-я группа |
| 07.окт | 08.окт | 09.окт | 10.окт | 11.окт |
| 11.01.00 | 12.01.00 | 13.01.00 | 14.01.00 | 15.01.00 |
Для удобства работы в Excel существуют стандартные списки, содержащие названия дней недели и месяцев, доступные при выборе команды Параметры в меню Сервис (закладка Списки), а также списки, создаваемые пользователем (рис. 86).
Рис. 86. Создание пользовательского списка
Стандартные списки в отличие от пользовательских нельзя удалить или отредактировать. Для создания или изменения пользовательского списка автозаполнения выполните следующие действия:
1. Выберите команду Параметры в меню Сервис (Tools, Options), а затем закладку Списки (List).
2. Укажите Новый список (New) в поле Списки (List).
3. Введите элементы списка или сделайте изменения в поле Элементы списка и нажмите кнопку Добавить (Add), затем кнопку ОК.
Для удаления списка выделите его и нажмите кнопку Удалить (Delete).
Проверка орфографии
Как и Word, Excel при проверке орфографии сравнивает слова на рабочем листе со словами, находящимися в словаре.
Проверка орфографии доступна при выборе команды Орфография из меню Сервис (Tools, Spelling).
Большинство используемых слов имеется в словаре. Словарь не включает некоторые собственные имена, наименования мест и технические термины. Excel будет отмечать эти слова как ошибочные, но пользователь может оставить слово таким, каким оно было набрано.
Расположение окон
Иногда бывает необходимо открывать несколько рабочих книг одновременно, например когда объединяются данные из разных файлов.
1. Выберите Файл, Открыть (File, Open).
2. Выделите необходимые для открытия рабочие книги. Для того чтобы выделить при помощи мыши последовательную группу книг, используйте клавишу Shift, чтобы выделить несмежные книги - Ctrl.
3. Выберите Открыть (Open).
Открытые рабочие книги могут быть размещены на экране одним из четырех способов: рядом, сверху вниз, слева направо, каскадом.
Чтобы показать на экране несколько рабочих книг или листов:
1. Выберите Окно, Расположить (Window, Arrange).
2. Выберите один из вариантов расположения (рис. 87) и нажмите ОК.
Рис. 87. Диалоговое окно вариантов расположения окон
Пока окно не закрыто пользователем, книга остается открытой. Все открытые книги перечислены в меню Окно (Window) в порядке их открытия. Однако только одна рабочая книга может быть активной в текущий момент времени; ее окно располагается поверх всех других окон, если не выбрано специальное расположение. Активизацию нужного окна можно осуществить с помощью мыши или выбора нужного документа в меню Окно (Window).
Для одновременного просмотра разных частей книги, занимающей более одного экрана, можно создать несколько окон для одной книги.
1. Выберите Окно, Новое (Window, New). В меню Окно (Window) появится второе окно для открытой книги.
2. Выберите Окно, Расположить (Window, Arrange).
3. Выберите Только окна текущей книги (рис. 87) и задайте вариант расположения.
4. Щелкните ОК.
Вычисления в Excel
Создание простых формул
Формула - это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.
Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы.
В Excel формулы начинаются со знака =. Скобки ( ) могут использоваться для определения порядка математических операции.
Excel поддерживает следующие арифметические операции:
сложение (+); умножение (*); нахождение процента (%); вычитание (-); деление (/); экспонента (^).Операторы сравнения:
= равно; < меньше; > больше; <= меньше или равно; >= больше или равно; <> не равно.Операторы связи:
: диапазон; ; объединение; & оператор соединения текстов.Таблица 22. Примеры формул
| Формула | Составляющие элементы |
|
=27+36 =А1+А2-АЗ =45%*АЗ , |
Цифры Ссылки на ячейки Цифры и ссылки на ячейки |
|
=СУММ(А1:А5), =МАКС(АЗ:А5) |
Предварительно определенные функции |
| =(А1+А2)/АЗ | Ссылки на ячейки при заданном порядке действий |
Упражнение
Вставка формулы -25-А1+АЗ
Предварительно введите любые числа в ячейки А1 и A3.
1. Выберите необходимую ячейку, например В1.
2. Начните ввод формулы со знака=.
3. Введите число 25, затем оператор (знак -).
4. Введите ссылку на первый операнд, например щелчком мыши на нужную ячейку А1.
5. Введите следующий оператор(знак +).
6. Щелкните мышью в той ячейке, которая является вторым операндом в формуле.
7. Завершите ввод формулы нажатием клавиши Enter. В ячейке В1 получите результат.
Автосуммирование
Кнопка Автосумма (AutoSum) - ∑ может использоваться для автоматического создания формулы, которая суммирует область соседних ячеек, находящихся непосредственно слева в данной строке и непосредственно выше в данном столбце.
1. Выберите ячейку, в которую надо поместить результат суммирования.
2. Щелкните кнопку Автосумма - ∑ или нажмите комбинацию клавиш Alt+=. Excel примет решение, какую область включить в диапазон суммирования, и выделит ее пунктирной движущейся рамкой, называемой границей.
3. Нажмите Enter для принятия области, которую выбрала программа Excel, или выберите с помощью мыши новую область и затем нажмите Enter.
Функция "Автосумма" автоматически трансформируется в случае добавления и
удаления ячеек внутри области.
Упражнение
Создание таблицы и расчет по формулам
1. Введите числовые данные в ячейки, как показано в табл. 23.
Таблица 23. Исходная таблица данных
| А | В | С | D | Б | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | ||
| 3 | Высшее | 25 | 20 | 9 | ||
| 4 | Среднее спец. | 28 | 23 | 21 | ||
| 5 | ПТУ | 27 | 58 | 20 | ||
| в | Другое | 8 | 10 | 9 | ||
| 7 | Всего | |||||
| 8 | Без высшего | |||||
2. Выберите ячейку В7, в которой будет вычислена сумма по вертикали.
3. Щелкните кнопку Автосумма - ∑ или нажмите Alt+=.
4. Повторите действия пунктов 2 и 3 для ячеек С7 и D7.
Вычислите количество сотрудников без высшего образования (по формуле В7-ВЗ).
5. Выберите ячейку В8 и наберите знак (=). 6. Щелкните мышью в ячейке В7, которая является первым операндом в формуле.
7. Введите с клавиатуры знак (-) и щелкните мышью в ячейке ВЗ, которая является вторым операндом в формуле (будет введена формула).
8. Нажмите Enter (в ячейке В8 будет вычислен результат).
9. Повторите пункты 5-8 для вычислений по соответствующим формулам в ячейках С8 и 08.
10. Сохраните файл с именем Образование_сотрудников.х1s.
Таблица 24. Результат расчета
| А | B | С | D | Е | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | ||
| 3 | Высшее | 25 | 20 | 9 | ||
| 4 | Среднее спец. | 28 | 23 | 21 | ||
| 5 | ПТУ | 27 | 58 | 20 | ||
| 6 | Другое | 8 | 10 | 9 | ||
| 7 | Всего | 88 | 111 | 59 | ||
| 8 | Без высшего | 63 | 91 | 50 | ||
Тиражирование формул при помощи маркера заполнения
Область ячеек (ячейка) может быть размножена при помощи использования маркера заполнения. Как было показано в предыдущем разделе, маркер заполнения представляет собой контрольную точку в правом нижнем углу выделенной ячейки.
Часто бывает необходимо размножать не только данные, но и формулы, содержащие адресные ссылки. Процесс тиражирования формул при помощи маркера заполнения позволяет колировать формулу при одновременном изменении адресных ссылок в формуле.
1. Выберите ячейку, содержащую формулу для тиражирования.
2. Перетащите маркер заполнения в нужном направлении. Формула будет размножена во всех ячейках.
Обычно этот процесс используется при копировании формул внутри строк или столбцов, содержащих однотипные данные. При тиражировании формул с помощью маркера заполнения меняются так называемые относительные адреса ячеек в формуле (подробно относительные и абсолютные ссылки будут описаны далее).
Упражнение
Тиражирование формул
1.Откройте файл Образование_сотрудников.х1s.
2. Введите в ячейку ЕЗ формулу для автосуммирования ячеек =СУММ(ВЗ:03).
3. Скопируйте, перетащив маркер заполнения, формулу в ячейки Е4:Е8.
4. Просмотрите как меняются относительные адреса ячеек в полученных формулах (табл. 25) и сохраните файл.
Таблица 25. Изменение адресов ячеек при тиражировании формул
| А | В | С | D | Е | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | ||
| 3 | Высшее | 25 | 20 | 9 | =СУММ{ВЗ:03) | |
| 4 | Среднее спец. | 28 | 23 | 21 | =СУММ(В4:04) | |
| 5 | ПТУ | 27 | 58 | 20 | =СУММ(В5:05) | |
| 6 | Другое | 8 | 10 | 9 | =СУММ(В6:06) | |
| 7 | Всего | 88 | 111 | 58 | =СУММ(В7:07) | |
| 8 | Без высшего | 63 | 91 | 49 | =СУММ(В8:08) | |
Относительные и абсолютные ссылки
Формулы, реализующие вычисления в таблицах, для адресации ячеек используют так называемые ссылки. Ссылка на ячейку может быть относительной или абсолютной.
Использование относительных ссылок аналогично указанию направления движения по улице - "идти три квартала на север, затем два квартала на запад". Следование этим инструкциям из различных начальных мест будет приводить в разные места назначения.
Обычно ссылки на ячейки описываются и используются как относительные (формат записи А1). Когда формула, содержащая эти ссылки, копируется, происходит изменение формулы для поддержания относительности ссылок.
Например, формула, которая суммирует числа в столбце или строке, затем часто копируется для других номеров строк или столбцов. В таких формулах используются относительные ссылки (см. предыдущий пример в табл. 25).
Абсолютная ссылка на ячейку .иди область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.
Абсолютная ссылка может быть создана только при наборе формулы, перед адресом строки и столбца вводится знак доллара - $.
Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот.
Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 - так называемые смешанные ссылки).
Упражнение
Создание абсолютной ссылки
Создайте таблицу, аналогичную представленной ниже.
Таблица 26. Расчет зарплаты
| A | B | C | |
| 1 | Часовая ставка | 100р. | |
| 2 | Ф. И. 0 . | Часов | Зарплата |
| 3 | Иванов | 40 | |
| 4 | Петров | 30 | |
| 5 | Сидоров | 25 |
2. В ячейку СЗ введите формулу для расчета зарплаты Иванова =В1*ВЗ.
При тиражировании формулы данного примера с относительными ссылками в ячейке С4 появляется сообщение об ошибке (#ЗНАЧ!), так как изменится относительный адрес ячейки В1, и в ячейку С4 скопируется формула =В2*В4;
3. Задайте абсолютную ссылку на ячейку В1, поставив курсор в строке формул на В1 и нажав клавишу F4, Формула в ячейке СЗ будет иметь вид =$В$1*ВЗ.
4. Скопируйте формулу в ячейки С4 и С5.
5. Сохраните файл (табл. 27) под именем Зарплата.xls.
Таблица 27. Итоги расчета зарплаты
| A | B | C | |
| 1 | Часовая ставка | 100р. | |
| 2 | ФИО | Часов | Зарплата |
| 3 | Иванов | 40 | 4000р. |
| 4 | Петров | 30 | 3000р. |
| 5 | Сидоров | 25 | 2500р. |
Имена в формулах
Имена в формулах легче запомнить, чем адреса ячеек, поэтому вместо абсолютных ссылок можно использовать именованные области (одна или несколько ячеек). Необходимо соблюдать следующие правила при создании имен:
имена могут содержать не более 255 символов; имена должны начинаться с буквы и могут содержать любой символ, кроме пробела; имена не должны быть похожи на ссылки, такие, как ВЗ, С4; имена не должны использовать функции Excel, такие, как СУММ, ЕСЛИ и т. п.В меню Вставка, Имя существуют две различные команды создания именованных областей: Создать и Присвоить.
Команда Создать позволяет задать (ввести) требуемое имя (только одно), команда Присвоить использует метки, размещенные на рабочем листе, в качестве имен областей (разрешается создавать сразу несколько имен).
Создание имени
1. Выделите ячейку В1 (табл. 26).
2. Выберите в меню Вставка, Имя (Insert, Name) команду Присвоить (Define).
3. Введите имя Часовая ставка и нажмите ОК.
4. Выделите ячейку В1 и убедитесь, что в поле имени указано Часовая ставка.
Создание нескольких имен
1. Выделите ячейки ВЗ:С5 (табл. 27).
2. Выберите в меню Вставка, Имя (Insert, Name) команду Создать (Create), появится диалоговое окно Создать имена (рис. 88).
3. Убедитесь, что переключатель в столбце слева помечен и нажмите ОК.
4. Выделите ячейки ВЗ:СЗ и убедитесь, что в поле имени указано Иванов.
Рис. 88. Диалоговое окно Создать имена
Можно в формулу вставить имя вместо абсолютной ссылки.
1. В строке формул установите курсор в то место, где будет добавлено имя.
2. Выберите в меню Вставка, Имя (Insert, Name) команду Вставить (Paste), появится диалоговое окно Вставить имена.
3. Выберите нужное имя из списка и нажмите ОК.
Ошибки в формулах
Бели при вводе формул или данных допущена ошибка, то в результирующей ячейке появляется сообщение об ошибке. Первым символом всех значений ошибок является символ #. Значения ошибок зависят от вида допущенной ошибки.
Excel может распознать далеко не все ошибки, но те, которые обнаружены, надо уметь исправить.
Ошибка # # # # появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.
Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на нуль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.
Ошибка #Н/Д! является сокращением термина "неопределенные данные". Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.
Ошибка #ИМЯ? появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.
Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.
Ошибка #ЧИСЛО! появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.
Ошибка #ССЫЛКА! появляется, когда в формуле используется недопустимая ссылка
на ячейку. Например, если ячейки были удалены или в эти ячейки было помещено
содержимое других ячеек.
Ошибка
#ЗНАЧ! появляется, когда в формуле используется недопустимый тип аргумента
или операнда. Например, вместо числового или логического значения для оператора
или функции введен текст.
Кроме перечисленных ошибок, при вводе формул может появиться циклическая ссылка.
Циклическая ссылка возникает тогда, когда формула прямо или косвенно включает ссылки на свою собственную ячейку. Циклическая ссылка может вызывать искажения в вычислениях на рабочем листе и поэтому рассматривается как ошибка в большинстве приложений. При вводе циклической ссылки появляется предупредительное сообщение (рис. 89).
Рис. 89. Циклическая ссылка
Индикатор циклической ссылки в строке состояния показывает ««ссылку на последнюю зависимую формулу.
Когда формула, содержащая циклическую ссылку, находится на рабочем листе, появится сообщение, указанное на рис. 89.
Для исправления ошибки удалите ячейку, которая вызвала циклическую ссылку, отредактируйте или введите заново формулу.
Функции в Excel
Более сложные вычисления в таблицах Excel осуществляются с помощью специальных функций (рис. 90). Список категорий функций доступен при выборе команды Функция в меню Вставка (Insert, Function).
Финансовые функции осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др.
Функции Дата и время позволяют работать со значениями даты и времени в формулах. Например, можно использовать в формуле текущую дату, воспользовавшись функцией СЕГОДНЯ.
Рис. 90. Мастер функций
Математические функции выполняют простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, абсолютной величины числа, округление чисел и др.
Статистические функции позволяют выполнять статистический анализ данных. Например, можно определить среднее значение и дисперсию по выборке и многое другое.
Функции Ссылки и массивы позволяют осуществить поиск данных в списках или таблицах, найти ссылку на ячейку в массиве. Например, для поиска значения в строке таблицы используется функция ГПР.
Функции работы с базами данных можно использовать для выполнения расчетов и для отбора записей по условию.
Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ.
Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.
Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ.
Для вычислений в таблице с помощью встроенных функций рекомендуется
использовать мастер функций. Диалоговое окно мастера функций доступно при выборе
команды Функция в меню Вставка или нажатии кнопки
Упражнение
Вычисление величины среднего значения для каждой строки в файле Образование.хls.
1. Выделите ячейку F3 и нажмите на кнопку мастера функций.
2. В первом окне диалога мастера функций из категории Статистические выберите функцию СРЗНАЧ, нажмите на кнопку Далее.
3. Во втором диалоговом окне мастера функций должны быть заданы аргументы. Курсор ввода находится в поле ввода первого аргумента. В это поле в качестве аргумента число! введите адрес диапазона B3:D3 (рис. 91).
4. Нажмите ОК.
5. Скопируйте полученную формулу в ячейки F4:F6 и сохраните файл (табл. 28).
Рис. 91. Ввод аргумента в мастере функций
Таблица 28. Таблица результатов расчета с помощью мастера функций
| А | В | С | D | Е | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | Среднее | |
| 3 | Высшее | 25 | 20 | 9 | 54 | 18 |
| 4 | Среднее спец. | 28 | 23 | 21 | 72 | 24 |
| 8 | ПТУ | 27 | 58 | 20 | 105 | 35 |
| в | Другое | 8 | 10 | 9 | 27 | 9 |
| 7 | Всего | 88 | 111 | 59 | 258 | |
| 8 | Без высшего | 63 | 91 | 50 | 208 | |
Для ввода диапазона ячеек в окно мастера функций можно мышью обвести на рабочем листе таблицы этот диапазон (в примере B3:D3). Если окно мастера функций закрывает нужные ячейки, можно передвинуть окно диалога. После выделения диапазона ячеек (B3:D3) вокруг него появится бегущая пунктирная рамка, а в поле аргумента автоматически появится адрес выделенного диапазона ячеек.
Ввод аргументов можно выполнить и с клавиатуры.
Форматирование таблицы
Столбцы и строки
Стандартная ширина столбцов не всегда подходит для столбцов на рабочем листе.
Например, ширину столбцов необходимо изменять при следующих условиях:
ширина недостаточна, чтобы показать текст без усечения; ширина недостаточна, чтобы показать цифровую информацию (в этом случае ячейку будут заполнять символы # # # #); ширина слишком большая для данной таблицы, и ее необходимо сократить.Если ширина столбца слишком маленькая, данные не теряются; они просто не показываются полностью.
Ширина столбца и групп столбцов может быть изменена при использовании команд Формат, Столбец (Format, Column).
Форматирование столбца
1. Выделите столбцы для форматирования.
2. Выберите в меню команду Формат, Столбец (Format, Column) и затем требуемую операцию (табл. 29).
Таблица 29. Форматирование столбца
| Операция | Описание |
| Ширина | Определяет новую ширину для выбранных столбцов |
| Автоподбор ширины | Назначает ширину столбца в соответствии с самой широкой среди выбранных ячеек |
| Скрыть | Делает столбцы невидимыми |
| Отобразить | Показывает скрытые ранее столбцы |
| Стандартная ширина | Определяет стандартную ширину столбцов в текущем рабочем листе. Новые установки применяются ко всем столбцам, чья ширина не была установлена заранее |
Изменение ширины столбцов с помощью мыши
1 .Выделите столбцы для изменения ширину.
2. Переместите указатель мыши на правую границу одного из выбранных столбцов в область заголовков столбцов. Указатель мыши изменится на двунаправленную стрелку.
3. Перетащите правую границу столбца на новое место. Ширина колонки будет показана в поле всплывающей подсказки в ходе этого процесса.
Для автоподбора ширины столбца щелкните дважды по правой границе столбца.
Высота выбранных строк может быть изменена для того, чтобы обеспечить
увеличенное расстояние между линиями.
Высота строк, автоматически присваиваемая Excel, базируется на размере наибольшего шрифта, находящегося в строке. Автоматически рассчитанная высота строки приблизительно на 25 % больше, чем размер шрифта в пунктах (1/72 дюйма).
Форматирование строк
1. Выделите необходимые строки.
2. Выберите Формат, Строка (Format, Row) и требуемые операции (табл. 30).
Таблица 30. Форматирование строки
| Операция | Описание |
| Высота | Определяет новую высоту для выбранных строк |
| Автоподбор высоты | Приводит высоту строк в соответствие с наибольшим шрифтом. Это установка по умолчанию |
| Скрыть | Скрывает строки |
| Отобразить | Показывает строки, которые были до этого скрыты |
Высоту строки также можно изменить с помощью мыши. Перетащите нижнюю границу строки на новое место. Высота строки будет показана в поле всплывающей подсказки в ходе этого процесса.
Для автоподбора высоты строки щелкните дважды по нижней границе строки.
Форматирование ячеек
Содержимое ячеек таблицы может быть отформатировано для улучшения внешнего вида таблицы на рабочем листе. Все опции форматирования ячеек могут быть найдены в окне диалога команды Ячейки в меню Формат (Format, Cells). Кроме того, некоторые кнопки доступны в панели инструментов Форматирование для быстрого применения наиболее общих текстовых и цифровых форматов.
Параметры форматирования диалогового окна Формат ячеек (рис. 90) разделены закладками и представлены в табл. 31.
Таблица 31. Закладки диалогового окна Формат ячеек
| Закладка | Описание |
| Число | Числовые форматы, которые могут быть применены к данным в ячейке |
| Выравнивание | Выравнивание и направление начертания символов в ячейке |
| Шрифт | Установки для шрифта, размера и начертания символов в ячейке |
| Граница | Рамки ячеек: их вид и цвет |
| Вид | Цвета и узоры теневой маски для ячеек |
| Защита | Ячейки можно заблокировать или скрыть |
Форматирование чисел
Данные в таблицах Excel могут быть представлены в различных числовых форматах, в зависимости от того, какие вычисления требуется произвести (рис.92).
В поле Числовые форматы представлен список форматов. Можно задать нужное число знаков после запятой и выбрать способ представления отрицательных чисел в соответствующих полях.
Существует разница между тем, что ячейка "реально" содержит, и способом показа ее содержимого. Строка формул показывает неформатированное содержимое, в то время как ячейка показывает форматированную версию. Поле Образец показывает выбранное форматирование.
При работе с новой таблицей по Умолчанию установлен цифровой формат Общий (General), который показывает числа без определенного форматирования (т.е. 145123; 0,37; 31 и т. д.).
Ввод некоторых символов автоматически установит нужное форматирование. Например; ввод 40 р. или 5 % в ячейку автоматически установит денежный или процентный формат введенных данных соответственно.
Форматирование может также выполняться при введении значений на рабочий лист.
Рис. 92. Закладка Число
1. Выделите область ячеек для ввода.
2. Нажмите кнопку 
Форматирование.
3. Введите данные в ячейки. Все последующие значения, введенные в ячейки выделенной области, будут представлены в денежном формате.
Выравнивание данных в ячейке
Для установки новых значений выравнивания или изменения значений принятых по умолчанию могут использоваться опции закладки Выравнивание (Alignment) в окне диалога Формат, Ячейки (Format, Cells) (рис. 93).
Изменения будут применены для всех выбранных ячеек, областей рабочего листа. По умолчанию выравнивание текста, введенного в ячейку, осуществляется по левому краю, а цифры выравниваются вправо.
Опции выравниваний по горизонтали и по вертикали определяют положение текста в ячейке. Для изменения направления текста требуется повернуть стрелку со словом Надпись в поле Ориентация.
Если текст в ячейке состоит из нескольких слов, для удобства чтения задайте опито Переносить по словам.
Рис. 93. Закладка Выравнивание
Центрирование текста по столбцам
Содержимое ячейки может быть выровнено по столбцам в выбранной области. Эта возможность используется для центрирования заголовков на рабочем листе. В этом случае текст должен находиться в самой левой ячейке выделенной области.
1. Введите текст заголовка листа.
2. Выделите несколько соседних ячеек (по размеру таблицы).
3. Нажмите кнопку Объединить и поместить в центре -
Установка шрифтов и их атрибутов
Опции закладки Шрифт (Font) окна диалога Формат, Ячейки (Format/ Cells) используются для установки шрифтов и их стилей для выделенных ячеек.
Средства форматирования с использованием шрифтов в Excel аналогичны средствам
Word, описанным в предыдущей главе.
Границы и вид ячейки или области
Рамки могут применяться для ячейки или выделенной области. Опции рамок могут быть установлены при использовании закладки Граница (Border) (рис. 94).
Рис. 94. Закладка Граница
Некоторые из доступных стилей рамок представлены в поле Тип линии.
1. Выделите ячейки для форматирования.
2. Откройте панель диалога Формат, Ячейки (Format, Cells) и выберите закладку Граница (Border).
3. Выберите стороны для ячеек, в которых будет установлена рамка.
4. Выберите Тип и цвет линии.
5. Нажмите ОК.
Внешний вид ячеек может быть улучшен при заполнении их цветом и/или узором. Цвета и узоры (включая цвет узора) могут быть установлены при использовании закладки Вид (Patterns) окна диалога Формат, Ячейки (Format, Cells).
Выбранная заливка и узор показываются в поле Образец.
Защита ячеек
Защита полезна в таблицах, содержащих сложные формулы и заранее заданные константы. В Excel используется двухуровневая система защиты. В рабочем листе каждая ячейка по умолчанию заблокирована, но, если защита листа выключена, данные можно вводить во все ячейки.
Закладка Защита (Protection) содержит опции Защищаемая ячейка (Locked) и Скрыть формулы (Hidden).
Чтобы запретить изменение ячеек листа для сохранения формул или данных, следует разблокировать ячейки для ввода и установить защиту листа.
1. Выделите и разблокируйте все ячейки, которые потребуется изменять после защиты листа, сняв опцию Защищаемая ячейка (Locked).
2. Скройте все формулы, которые не должны быть видимы, установив опцию Скрыть формулы (Hidden).
3. В меню Сервис выберите команду Защита (Tools, Options), а затем команду Защитить лист (Protect Sheet).
Чтобы никто другой не смог снять защиту с листа, введите пароль, но учтите, что при утере пароля получить доступ к защищенным элементам листа будет невозможно.
Работа с листами
Ярлыки листов
Ярлыки листов располагаются внизу слева в области рабочей книги. Имя каждого рабочего листа появляется на соответствующем ярлыке. Однако любому ярлыку может быть дано другое имя, длиной не более 31 символа.
Это имя может быть использовано при адресации листа в формулах.
Адресация листа
Для использования в формуле данных, расположенных на определенных листах, необходимо после названия листа поставить восклицательный знак, затем адрес ячейки. Диапазон листов задается через двоеточие.
Примеры адресации листов:
Лист1!А1
Sheet12!G23
Квартал1!В4:Е7
Лист1!:ЛистЗ!А2
Во вновь создаваемую книгу по умолчанию Excel вставляет три
листа.
Операции, выполняемые с рабочими листами, доступны при вызове контекстного меню (рис. 95). Щелкнув правой кнопкой мыши по ярлыку рабочего листа, можно выбрать нужную операцию из меню.
Рис. 95. Контекстное меню
Имя рабочего листа можно ввести не используя контекстное меню, для этого:
1. Щелкните дважды по ярлыку рабочего листа.
2. Введите имя листа в область ярлыка и нажмите Enter.
Для перемещения между листами щелкните ярлык необходимого листа. Имя текущего листа выделится жирным шрифтом.
Кнопки прокрутки ярлыков листов могут быть использованы для перехода к другим листам. Для перемещения по списку листов щелкните кнопки прокрутки ярлыков.
Для изменения количества выводимых ярлыков перетащите разделитель поля ярлыков вправо (рис. 96).
Рис. 96. Ярлыки листов
Операции удаления, перемещения и переименования листа отменить нельзя.
Добавление нового листа
Существует несколько способов добавления новых листов в рабочую книгу.
Команда Вставка, Лист (Insert, Worksheet) используется для добавления нового листа в текущую рабочую книгу. Новый лист будет вставлен перед текущим листом.
Любые формулы, использующие ссылки на листы, будут автоматически обновляться при добавлении нового листа.
Процедура, показанная ниже, также может быть использована для добавления нового листа.
1. Щелкните правой кнопкой мыши по ярлыку текущего рабочего листа для открытия контекстного меню.
2. Выберите Добавить (Insert). Появится диалоговое окно Вставка.
3. Выберите Лист (Worksheet) из диалогового окна и нажмите ОК.
Удаление листов
Для удаления листов из рабочей книги можно воспользоваться командой Правка, Удалить лист (Edit, Delete Sheet) или выбрать соответствующую команду из контекстного меню.
Копирование и перемещение листов
В процессе обработки электронных таблиц часто бывает необходимо перемещать и копировать листы как в пределах рабочей книги, так и в другую книгу.
1. Выделите лист для перемещения или копирования.
2. Выберите Правка (Edit), Переместить или скопировать лист (Move or Copy Sheet) или вызовите контекстное меню и выберите Переместить или скопировать (Move or Сору) (рис. 97).
Появится диалоговое окно Переместить или скопировать (Move or Copy).
3. В поле В книгу (То Book) выберите книгу, в которую листы будут скопированы или перемещены. Книга должна быть открыта, чтобы ее можно было выбрать,
4. Выберите лист, перед которым лист(ы) будет вставлен в поле Перед листом (Before Sheet).
5. Включите опцию Создавать копию (Create a Copy) для копирования. В противном случае данные будут перемещены.
6. Нажмите ОК после завершения.
Рис. 97. Диалоговое окно Переместить или скопировать
Как и ячейки таблицы, листы также могут быть перемещены или скопированы с помощью перетаскивания (Drag and Drop). Этот метод может быть использован для перемещения или копирования листов в пределах одной рабочей книги или для открытых книг из одной в другую.
Для перемещения листа перетащите ярлык выделенного листа на новую позицию. Указатель (треугольник Ñ) отмечает положение, куда будут помещены листы, когда кнопка мыши будет отпущена.
Для копирования листов перетащите выделенные листы на новую позицию при нажатой клавише Ctrl. Появление символа плюс (+) на указателе при копировании показывает, что выделенные листы будут скопированы.
Объемные таблицы в Excel
Каждая ячейка в Excel имеет две координаты - номер строки и номер столбца. Поскольку рабочая книга состоит из нескольких рабочих листов (по умолчанию из трех), то ячейке можно присвоить третью координату - номер листа, которая представляет собой третье измерение, что позволяет говорить об объемных таблицах.
Использование рабочих книг с несколькими листами обеспечивает следующие преимущества:
гибкость в оформлении рабочих листов; возможность консолидации (объединения) данных.В рабочей книге с несколькими листами с помощью группировки листов можно изменять формат одного листа таким образом, что одновременно изменяются и другие листы. Книга с несколькими листами может быть использована для размещения каждого сегмента данных на разных листах, позволяя выполнять операции вычислений независимо или объединять и связывать данные.
Например, если однотипная информация по кварталам размещается последовательно на четырех рабочих листах, то пятый лист может содержать итоговые данные за год. Для суммирования значений по кварталам создается формула связывания данных с использованием ссылок с именами листов.
Трехмерные ссылки
Excel использует ярлыки листов для определения адреса листа. Имя каждого листа последовательно увеличивается на единицу, начиная с Лист! (Sheetl). Трехмерные формулы включают области, которые охватывают более одного листа, т. е. определяют диапазон листов, содержащих данные.
Лист!: Лист2!В11
Квартал!: Квартал4!А1:А10
Объединение листов
Для выполнения общего форматирования ячеек таблицы, ввода заголовков и однотипных данных используется группировка или объединение листов. Для объединения смежных листов:
1. Выделите ярлык первого листа.
2. Нажмите Shift и щелкните ярлык последнего листа.
Ярлыки объединенных листов станут выделенными. Для объединения несмежных листов:
1. Выделите ярлык первого листа.
2. Нажмите Ctrl и щелкайте ярлыки тех листов, которые должны быть включены.
Ярлыки объединенных листов станут выделенными.
Для отмены объединения щелкните правой кнопкой ярлык любого листа из объединенных и в контекстном меню выберите Разгруппировать листы или щелкните ярлык любого листа, не принадлежащего к группе.
Упражнение
Создание общего заголовка для группы листов
1. Создайте в новой рабочей книге четыре листа с именами Отдел1, Отдел2, Отдел3 и Общий список.
2. Выберите ярлык листа Отдел1.
3. Нажмите Shift и щелкните ярлык листа ОтделЗ (будут выделены листы Отдел1, Отдел2, Отдел3, которые должны быть включены в группу).
4. Выделите ячейку А1 и введите текст: Список сотрудников по отделам на 01.07.2000 г.
5. Примените форматирование шрифта Размер 12, Полужирный.
6. Выделите несколько соседних ячеек (А1:Е1 по размеру таблицы).
7. Нажмите кнопку Объединить и поместить в центре -
Форматирование.
8. Для отмены объединения листов щелкните ярлык листа Общий список, не принадлежащего к группе.
9. Просмотрите заголовки на трех листах (Отдел1, Отдел2, Отдел3) и убедитесь, что заголовка на листе Общий список нет.
10. Введите элементы списка по отделам.
11. Скопируйте введенные данные на лист Общий список и сохраните файл с именем Список_сотрудников.х1s.
Как читать большие листы
Установки для просмотра
Существует несколько опций для предварительных установок просмотра таблиц в Excel, которые доступны при выборе соответствующих закладок в окне диалога команды Параметры из меню Сервис (Tools, Options).
Для установки правил отображения элементов окна Excel выберите закладку Вид (View) и установите требуемые параметры (рис. 98).
Для проверки правильности вычислений в таблице следует включить опцию Формулы в поле Параметры окна, при этом в ячейках, содержащих формулы, можно их увидеть.
Для таблиц, содержащих рисунки и текстовые блоки, можно отключить сетку.
Для остальных параметров можно рекомендовать те опции, которые установлены на представленном экране.
Рис. 98. Закладка Вид команды Параметры
Фиксация панелей
Строки и столбцы, содержащие заголовки, могут быть зафиксированы на месте, подобно именам строк и столбцов. Заголовки остаются на месте, в то время как пользователь может просматривать содержимое рабочего листа. Фиксация заголовков особенно эффективна для улучшения читаемости больших таблиц, строки или столбцы которых находятся за пределами видимого экрана.
Позиция активной ячейки будет определять, какие строки и/или столбцы станут заголовками в результате прокрутки окна:
выделение строки фиксирует верхнюю строку в качестве заголовка; выделение столбца фиксирует левый столбец в качестве заголовка; выделение ячейки фиксирует верхнюю строку и левый столбец в качестве заголовка.1. Выделите подходящую ячейку, строку или столбец.
2. Выберите Окно, Закрепить области (Window, Freeze Panes).
Переместите бегунок и просмотрите таблицу при зафиксированных строках заголовка.
Если заголовок уже зафиксирован, команда Закрепить области изменится на команду Снять закрепление областей (Unfreeze Panes). Используйте эту команду для того, чтобы отменить фиксацию областей.
Разделение окон
Окно текущего рабочего листа может быть разделено на две или четыре части. Каждая панель показывает один и тот же рабочий лист, но любая из панелей может использоваться независимо для просмотра различных частей рабочего листа. Эта возможность полезна при создании формул для выбора ячеек, находящихся далеко друг от друга.
Позиция активной ячейки, строки или столбца определяет тип разделения (при выделении строки - вдоль листа, столбца - поперек, ячейки - на четыре части).
1. Выделите подходящую ячейку, строку или столбец.
2. Выберите Окно, Разделить (Window, Split).
Рабочий лист разделится, и появятся полосы разделения поперек или вдоль рабочего листа. Например, верхнее левое окно может использоваться для просмотра данных, в то время как нижнее правое для просмотра результатов вычислений.
Как только окно будет разделено, команда Разделить изменится на команду Снять разделение (Remove Split). Для удаления разделения окна нужно выбрать эту команду.
Предварительный просмотр и печать таблицы
Просмотр макета
После окончания оформления книги ее можно распечатать. Однако перед печатью макет книги следует просмотреть и при необходимости установить требуемые параметры печати.
Выберите Файл, Предварительный просмотр (File, Preview) или
щелкните кнопку 
Макет рабочей книги будет показан в окне предварительного просмотра. Документ можно просмотреть, используя линейки прокрутки и панель инструментов предварительного просмотра (рис. 99). Для управления масштабом просмотра нажмите кнопку Масштаб.
Рис. 99. Кнопки управления предварительным просмотром
Упражнение
Предварительный просмотр документа
1. Откройте книгу Список сотрудников.
2. Войдите в режим предварительного просмотра документа перед печатью с помощью команды Предварительный просмотр в меню Файл.
В строке состояния в нижней части экрана выводятся номер текущей страницы и количество печатных страниц выбранного листа.
3. В режиме просмотра нажмите на кнопку Поля. На странице появятся пунктирные линии, обозначающие границы полей и колонтитулов, а также маркеры ширины столбцов.
4. Для изменения размера поля с помощью мыши перетащите маркер левого поля (размер поля можно увидеть в строке состояния в нижней части экрана).
5. Просмотрите следующую или предыдущую страницу с помощью кнопок Далее и Назад.
6. Нажмите на кнопку Страница для перехода в диалоговое окно Параметры страницы.
7. Нажмите кнопку Закрыть на панели, чтобы перейти в обычный режим просмотра.
Верхний и нижний колонтитулы
Верхний колонтитул (Header) - это текст, который повторяется наверху каждой печатаемой страницы (типа заголовка и текущей даты). Нижний колонтитул (Footer) - это текст, повторяющийся внизу каждой печатаемой страницы (типа номера страницы). Колонтитулы видны в окне предварительного просмотра.
Окно диалога для пользовательской настройки Верхнего колонтитула или Нижнего колонтитула станет доступным, если выбрать Создать верхний колонтитул или Создать нижний колонтитул в закладке Колонтитулы.
Закладка Колонтитулы окна диалога Параметры страницы (Page Setup) имеет область для ввода верхнего и нижнего колонтитулов (рис. 100).
Рис. 100. Диалоговое окно Верхний колонтитул
Окна диалога содержат кнопки (табл. 32), которые могут использоваться для автоматической вставки некоторых данных в поля верхнего и нижнего колонтитулов.
Таблица 32. Кнопки диалогового окна Верхний колонтитул
| Кнопка | Отображаемый код | Результат |
 |
&[Date] | Текущая дата |
 |
&[Time] | Текущее время |
 |
&[Page] | Номер страницы |
 |
&[Pages] | Количество печатаемых страниц |
 |
&[File] | Имя файла |
 |
&[Tab] | Имя рабочего листа |
Любой текст и кодовые сокращения могут быть помещены в левый, правый или
центральный раздел. Используя кнопку 
Упражнение
Создание колонтитулов
1. Откройте файл Список_сотрудников.х1s на листе Общий список.
2. Выполните команду Параметры страницы из меню Файл, перейдите на закладку Колонтитулы.
3. Очистите верхний колонтитул. Для этого откройте раскрывающийся список в поле Верхний колонтитул и выберите из списка элемент Нет.
4. Нажмите кнопку Создать верхний колонтитул, чтобы ввести пользовательский верхний колонтитул.
5. В окне диалога Верхний колонтитул выберите левую секцию и введите текст: Фирма "Москва".
6. Щелкните в правую секцию и нажмите на кнопку Дата, которая поместит в колонтитул текущую дату.
7. Чтобы отформатировать текст в колонтитуле, выделите его, нажмите в окне
диалога кнопку Шрифт -
8. Выберите подходящее форматирование и нажмите кнопку ОК.
9. Нажмите кнопку ОК в окне диалога Верхний колонтитул и перейдите в диалоговое окно Параметры страницы, где будет показан созданный верхний колонтитул.
10. Для создания нижнего колонтитула нажмите кнопку Создать нижний колонтитул.
11. Перейдите в правую секцию и нажмите на кнопку номера страницы, перед и после кода поставьте дефис (-).
12. Чтобы отформатировать текст в колонтитуле, выделите его, нажмите в окне диалога кнопку Шрифт, выберите подходящее форматирование и нажмите кнопку ОК.
13. В диалоговом окне Параметры страницы будут показаны созданные колонтитулы.
14. Нажмите на кнопку Просмотр, чтобы войти .в режим просмотра перед печатью. Просмотрите результат и сохраните файл.
Параметры печати
Перед выводом на печать для созданной таблицы можно задать дополнительные установки, которые позволят получить эффектно оформленную распечатку. Эти установки задаются в диалоговом окне команды Параметры страницы из меню Файл (File, Page Setup) (рис. 101).
В табл. 33 приведены наиболее часто используемые опции настройки страницы.
Рис. 101. Закладка Страница команды Параметры страницы.
Таблица 33. Опции настройки страницы
| Опции | Закладка | Назначение |
| Ориентация | Страница |
Ориентация представлена двумя
вариантами: вертикальным расположением страницы; горизонтальным расположением страницы |
| Масштаб | Страница | Подбор масштаба таблицы при выводе на печать. Опция Разместить не более чем на (Fit to) используется для сжатия таблицы так, чтобы она уместилась на установленном количестве страниц |
| Размер бумаги | Страница | Выбор различных размеров бумаги |
| Границы | Поля | Определение верхних, нижних, левых и правых границ. Установки для области верхнего или нижнего колонтитула от края страницы |
| Центрировать | Поля | Область печати может быть центрирована горизонтально или вертикально на странице |
| Создать верхний/нижний колонтитул | Колонтитулы | Определение верхнего и нижнего колонтитула в книге |
| Сквозные строки, Сквозные столбцы | Лист | Выбор строк и столбцов, которые будут использоваться как фиксированные заголовки на каждом печатающемся листе. Функция у таких заголовков такая же, как и у заголовков, созданных с помощью команды Закрепить области |
| Сетка | Лист | Включение и отключение печати линий сетки, по умолчанию - значение опции Включено |
| Черно-белая | Лист | Подавление цвета при печати |
| Заголовки строк и столбцов | Лист | Включение и отключение вывода на печать заголовков строк и столбцов, обозначенных буквами и цифрами |
Печать рабочих листов
Кнопка Печать (Print) -
Файл
команду Печать (File, Print). Появится панель диалога Печать
(рис. 102).
Рис. 102. Диалоговое окно Печать
Окно диалога Печать может использоваться для определения нескольких копий или выбора страниц, листов или областей рабочей книги, которые будут отпечатаны.
Установка области печати
Excel позволяет распечатать не весь лист, а лишь некоторую область, так называемую область печати. Как только область печати для рабочего листа установлена, Excel по умолчанию будет печатать только ее для всех рабочих листов.
Но можно задать для каждого листа свою собственную область печати.
1. Выделите область рабочего листа, которая будет определена как область печати.
2. Выберите команду Область печати в меню Файл (File, Print Area).
3. Выберите Задать. Ограничительная линия появится вокруг выделенной области.
Для очистки предварительно определенной области печати выберите команду Область печати в меню Файл (File, Print Area), затем Убрать.
Параметры печати многостраничной таблицы устанавливаются в диалоговом окне при выборе закладки Лист (List) команды Параметры страницы из меню Файл (рис. 103).
Рис. 103. Закладка Лист команды Параметры страницы
При многостраничной печати в полях Сквозные строки и Сквозные столбцы задаются адреса строк/столбцов, содержащих заголовки таблицы.
Последовательность вывода страниц для многостраничных таблиц задается установкой переключателей и показана стрелкой внизу экрана.
Подготовки данных к печати
1. Откройте файл с именем Список_сотрудников.х1s и перейдите на лист Общий_список.
2. В меню Файл выберите команду Параметры страницы и перейдите на закладку Поля.
3. Установите параметры полей:
верхнее - 2.5 ем; нижнее - 2.5 см; левое - 3 см; правое - 2 см.
Размещение таблицы на заданном числе страниц
Требуется уменьшить масштаб распечатываемых данных чтобы разместить их на одной печатной странице.
1. Перейдите на рабочий лист Общий_список.
2. Убедитесь, что данная таблица занимает в ширину более одной страницы (рис. 104)(если таблица небольшая, добавьте дополнительные столбцы).
Рис. 104. Пример списка сотрудников
3. В окне просмотра нажмите на кнопку Страница для перехода в диалоговое окно Параметры страницы.
4. Выберите закладку Страница.
5. Выберите ориентацию страницы Альбомная.
6. Установите переключатель Разместить не более чем на и введите цифру 1 в поле стр. в ширину.
7. Нажмите на кнопку Просмотр, убедитесь, что данные при печати умещаются на одной странице (рис. 105).
Рис. 105. Лист Общий список в режиме предварительного просмотра
Разделение документа на страницы
Для больших таблиц Excel автоматически производит разбивку на страницы, однако иногда необходимо задать разделение на страницы принудительно.
1. Выделите строку, с которой необходимо начать новую страницу, например шестую (список должен содержать более шести строк).
2. В меню Вставка выберите команду Разрыв страницы.
Excel вставит конец страницы (широкую пунктирную линию) над выделенной строкой.
Задание заголовков для печати
Требуется, чтобы названия столбцов в первой строке рабочего листа печатались на каждой странице в качестве заголовков.
1. В диалоговом окне Параметры страницы выберите закладку Лист.
2. Выберите поле Сквозные строки (в поле должен мигать текстовый курсор).
3. На рабочем листе выделите с помощью мыши первую строку. При необходимости переместите окно диалога Параметры страницы так, чтобы оно не мешало выделять нужные строки и столбцы.
4. Нажмите на кнопку Просмотр, чтобы войти в режим просмотра перед печатью. Убедитесь, что заголовки появились на каждой странице.
5. Чтобы закрыть окно просмотра без вывода на печать, нажмите кнопку Закрыть.
