2 Основы Работы с Excel

2.1 Документы Excel: книги, содержащие листы

Книга – именно так называется документ Excel. Оно и понятно: первоначальная идея этого приложения – автоматизация бухгалтерского учета. Естественно, за­пустив приложение, вы увидите открытый в нем по умолчанию документ Книга 1 (рисунок 2.1), созданный на основе шаблона по умолчанию. Для создания книги на основе другого шаблона – правильно, обратимся к диалоговому окну Создание документа (команда Файл > Создать). Выбрав, например, шаблон Рассрочка (на вкладке Решения), мы и создадим, соответственно, книгу Рассрочка1 (рисунок 2.2).

Р исунок 2.1 - Документ Excel по умолчанию

Р исунок 2.2 - Книга на основе шаблона Рассрочка

Как видно, документы Excel не обязательно похожи друг на друга как две капли воды. Разберемся в том, что мы видим. Первое заметное отличие от документа Word - листы. Книга Excel состоит из листов двух основных типов: рабочих лис­тов и листов диаграмм. В книге, открытой по умолчанию, рабочих листов – три, и каждому из них соответствует ярлычок внизу листа с его именем, в нашем случае (по умолчанию), соответственно, Лист 1, Лист 2 и Лист 3.

СОВЕТ

В версиях до Excel 97 листов в новой книге было 16. Если и в ваших книгах листов должно быть заведомо больше (или меньше), выберите команду Сервис > Параметры, в диалоговом окне Параметры перейдите на вкладку Общие и счетчиком Листов в новой книге установите нужное число (от 1 до 255) листов.

ПРИМЕЧАНИЕ

Забегая вперед, заметим, что на этой же вкладке флажком Стиль ссылок R1C1 в группе Пара­метры можно установить стиль ссылок (адресов) ячеек рабочего листа, подобный нумерации элементов матриц в алгебре: первым идентифицировать ячейку будет номер строки (R – row), вторым – номер столбца (С – column). Пользоваться этим стилем в нашей книге мы не будем, однако помнить о нем полезно². Смысл букв R и С, опять-таки, стоит помнить, глядя на всплывающие подсказки и сведения в поле именно размере диапазона.

ВНИМАНИЕ

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

СОВЕТ

Слева от ярлычков расположены четыре кнопки прокрутки ярлычков, две для перебора по одному и две для перехода к крайним.

СОВЕТ

В отличие от Word, в Excel есть две вешки разбивки (документа на области): и на вертикальной, и на горизонтальной полосах прокрутки. Кроме них на левом краю горизонтальной полосы прокрутки есть еще вешка разбивки ярлычков, отделяющая ярлычки от полосы прокрутки. Перемещая вешку разбивки ярлычков, можно перераспределять место для отображения ярлычков и полосы прокрутки.

СОВЕТ

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

Рисунок 2.3 - Настройка вида окна

Щелчок на ярлычке делает лист активным (лист становится фокусом ввода). Ярлычок становится продолжением листа и светлеет. Двойной щелчок выделяет имя листа и позволяет его изменить (при выходе за пределы допустимых имен – непустое, не длиннее 31 символа и т. д. – вы получите предупреждение Excel). Схватив ярлычок мышью, лист можно переместить, а при удержании клавиши Ctrl скопировать – и не только в пределах исходной книги.

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

ПРИМЕЧАНИЕ

Окно свернутого документа представляет собой кнопку (см. рисунок 2.2, где свернута Книга 1), но кое в чем ведет себя так же, как и окна других типов. Перетащив на эту кнопку, например, ярлычок листа, мы поместим лист в свернутую книгу. Забегая вперед: если (щелчком) акти­визировать свернутую книгу, не восстанавливая ее и не разворачивая, в поле имени появит­ся адрес активной ячейки этой книги.

ПРИМЕЧАНИЕ

Как вы заметили, в отличие от Word 2000, в Excel 2000 возможность восстановления и сво­рачивания независимых окон документов внутри окна приложения (в области документа) сохранилась.

ПРИМЕЧАНИЕ

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

СОВЕТ

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

ВНИМАНИЕ

Команда контекстного меню Выделить все листы не выделяет, а группирует листы книги. При этом фокусом ввода (активными) становятся все листы книги и действия с ячейками одного листа дублируются на других листах. Все ярлычки сгруппированных листов становятся светлы­ми, но продолжением листа является только один («верхнего» листа, того, что виден в окне документа). К имени книги в заголовке добавляется слово (Группа). Разгруппировать листы можно соответствующей командой, появляющейся в контекстном меню после их группирования.

СОВЕТ

Проще группировать листы, щелкая на ярлычках при нажатой клавише Ctrl (в произвольном порядке) или при нажатой клавише Shift (сплошную «колоду»). Разгруппировать листы можно щелчком на «не верхнем» листе, а если их два – щелчком на «не верхнем» листе при нажатой клавише Ctrl или щелчком на «верхнем» листе при нажатой клавише Shift.

Как мы видели (см. рисунок 2.1 и 2.2), имена созданных документов не содержат рас­ширений – верный знак к тому, чтобы сохранить их, присвоив им конкретные тип и имена. Процедура, достаточно знакомая по работе с документами Word. Прин­ципиально отличие процесса сохранения в Excel: автосохранение в Excel обеспечи­вается специальной надстройкой, по умолчанию не подключенной. Выберите, что вам больше по душе, время от времени самостоятельно щелкать на кнопке Сохранить панели инструментов Стандартная или прибегнуть к назойливому «будильнику». В последнем случае выберите команду Сервис > Надстройки и в диалоговом окне Надстройки установите флажок против пункта Автосохранение в списке надстроек.

ВНИМАНИЕ

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

ПРИМЕЧАНИЕ

В списке Тип файла диалогового окна Сохранение документа можно видеть, что расширение .xls соответствует книге Excel, .xlt – шаблону, .xla – надстройке и т. д. Сообщим, что *.xlb – это файл с параметрами окна Excel, a *.xlw – файл с параметрами рабочей области.

ПРИМЕЧАНИЕ

Рабочая область – это та конфигурация окон в сеансе Excel, которую хорошо бы сохранить по его завершении. Для этого выберите команду Файл > Сохранить рабочую область и сохра­ните файл – по умолчанию resume.xlw – в нужном месте. Запустив его, вы сразу вернетесь к предыдущему сеансу Excel.

Как управлять документами (книгами) и «субдокументами» (листами), в первом приближении ясно. Что же, однако, представляют собой листы? Начнем с рабо­чих листов (о листах диаграмм позже). Рабочий лист – это совокупность ячеек, образованных пересечением 65536 строк и 256 столбцов. Как вы уже догадались (см. рисунок 2.2 и 2.3), сетка может быть и скрыта, – но 2¹⁶ × 2⁸ ячеек по-прежнему существуют. Понятно, что должен существовать какой-то способ (или несколько) идентификации (адресации) ячеек. Основной из идентификаторов – это адрес, или ссылка. Взгляните на лист: принцип ясен. Кнопки вверху листа (с одной или двумя латинскими буквами) нумеруют столбцы, а кнопки слева (с цифрами) нумеруют строки. Комбинация номера (буквенного) столбца и номера строки и образует адрес (ссылку) ячейки. Активной является ячейка, выделенная (щелч­ком на ней указателем в форме объемного креста) жирной рамкой. Ее адрес пока­зан в поле имени (поле слева от строки формул – тем самым, надеемся, мы сделали понятным, что такое поле имени и строка формул). На рисунок 2.1 и 2.2 активна ячей­ка А1 – это самая левая и самая верхняя ячейка листа. Нажмите сочетание кла­виш Ctrl+↓, а затем Ctrl+>. Теперь активной стала самая последняя ячейка IV65536.

СОВЕТ

Можно нажать обе стрелки сразу – мы просто хотели показать способ попасть в конец столб­ца, строки или листа. Способ этот, однако, предложен нами лукаво: на самом деле, это сочета­ние клавиш перемещает активную ячейку в последнюю в данном направлении ячейку с дан­ными (или форматированием не по умолчанию). Известное сочетание клавиш Ctrl+End, перемещающее в Word "курсор в конец документа, в Excel перемещает активную ячейку в конец области данных, области, ограниченной столбцами и строками с ячейками, в которых содер­жатся данные или происходили какие-либо (не любые) изменения в течение сеанса Excel.

ПРИМЕЧАНИЕ

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

Сослаться на ячейку можно и из другой книги. Тогда, например, адрес первой ячейки первой (еще не сохраненной) книги будет выглядеть как [Книга1]Лист1!А1, а ссылка на последнюю ячейку некоей книги с именем Последняя книra.xls (лист снова первый) будет выглядеть как '[Последняя книга]Лист1'!$IV$65536. Символы доллара, появившиеся перед номером столбца и номером строки, означают, что ссылка на ячейку – абсолютная, то есть не зависит от взаимного расположения влияющей и зависимой ячеек. Впрочем (поэкспериментируйте!), смысл поня­тий абсолютной и относительной ссылок для ячеек, находящихся в разных кни­гах, несколько... размыт. Одиночные кавычки, апострофы (перед квадратной скобкой с именем книги и перед восклицательным знаком после имени листа) тоже, в общем, не должны озадачивать. Excel автоматически ставит их при появлении «подозрительных» (по его мнению) символов в именах книги или листа. Существенно, однако, появление ссылки типа '0:\А5\А512\[Книга1.х15]Лист1'!$А$1. На сей раз в кавычки взят еще и полный путь к книге, а это означает, что книга с влияющей ячейкой закрыта и чудодейственное свойство Excel (не забывайте о нем!) отслеживать ее перемещения не работает!

Совокупность ячеек называется диапазоном, который может быть простым (в этом случае он всегда прямоуголен), а может быть составным. Простой диапазон можно построить (выделить или указать), щелкнув мышью на первой ячейке, а затем, при нажатой клавише Shift, – на последней (и, разумеется, протаскиванием мыши). Адреса первой и последней ячеек, разделенные двоеточием, образуют адрес про­стого диапазона. Составной диапазон образуется щелчками на нужных ячейках при удерживаемой клавише Ctrl Адреса отдельных ячеек и адреса простых поддиа­пазонов, входящих в составной, разделенные точкой с запятой, образуют адрес составного диапазона.

ВНИМАНИЕ

Любой диапазон, построенный с помощью клавиши Ctrl, Excel считает составным. В отличие от простого, выделенного жирной рамкой активной ячейки, составной диапазон рамкой не выделяется (кроме выделенной тонкой рамкой активной ячейки). При указании рамки диапа­зонов выглядят одинаково («бегущий шлагбаум»).

ВНИМАНИЕ

Далеко не любая операция, применимая к простому диапазону, применима и к составному.

ПРИМЕЧАНИЕ

При выделении в Excel 2000 происходит не инверсия цвета (как во всех предыдущих верси­ях), а наложение полупрозрачной «маски».

Итак, поскольку выделять мы умеем, раскрасить лист (кнопка Цвет заливки пане­ли инструментов Форматирование) труда уже не составит – так что теперь и о фор­матировании вообще мы будем вспоминать лишь между прочим. Как еще можно воздействовать на выделенные ячейки? Ввести туда данные – числа (или текст) или формулы. В чем, надо полагать, читатель уже и разглядел цель Excel. Данные можно ввести в активную ячейку, набрав их прямо с клавиатуры и завершив ввод нажатием клавиши Enter.

СОВЕТ

Выберите команду Сервис > Параметры, в диалоговом окне Параметры перейдите на вкладку Правка и убедитесь, что флажок Переход к другой ячейке после ввода, в направлении уста­новлен, а в списке выбран пункт Вниз. Установите флажок Правка прямо в ячейке.

СОВЕТ

Для перехода по завершении ввода в другом направлении есть другие клавиши.

ПРИМЕЧАНИЕ

Простой диапазон часто называют смежным, а составное! – несмежным.

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

СОВЕТ

Нажатие клавиши F2 – эквивалент двойного щелчка на ячейке.

Почему мы начали разговор с выделения, в то время как ввод происходит только в активную ячейку (которая, кстати, всегда выделена)? Потому что сочетание клавиш Ctrl+Enter позволяет ввести сразу во все выделенные ячейки данные, на­бранные на клавиатуре, а сочетание клавиш Ctrl+Shift+Enter – формулу массива. Многие другие операции также применимы ко всему выделенному диапазону, поэтому, различая два понятия, не будем забывать ни об одном из них.

Очевидное отличие формул от чисел – знак равенства, предваряющий все осталь­ные символы. Серьезнее разговор о формулах мы поведем чуть позже, а пока– главная идея с числами (к каковым формально относится и текст): не так все про­сто! То, что мы видим в ячейке, может отличаться от того, что мы увидим в строке формул, ну, а в понимании Excel это будет и вовсе третье.

СОВЕТ

Если вы хотите поиграть с Excel в компьютерные игры, не обращайте внимания на этот совет. Если же вы собрались поработать, выберите команду Пуск > Настройка > Панель управления, в окне Панель управления дважды щелкните на значке Язык и стандарты и в диалоговом окне Свойства: Язык и стандарты внимательно ознакомьтесь со всеми вкладками. Начните с вкладки Региональные стандарты – именно она задает «общий настрой» остальных. Прими­те для начала настройки по умолчанию – изменение любой из них повлечет не всегда пред­сказуемые последствия.

ВНИМАНИЕ

Доброжелательная критика прессой нашего незнания проблемы Y2K, выраженного открытым текстом в прошлых сочинениях об Excel, заставила призадуматься. И точно (чтоб сразу сообра­зить!): надо ж было приобрести новый календарь! Столь же хитроумное решение предлага­ется на вкладке Дата: при желании обозначать год двумя цифрами вам придется решить, к какому столетию они относятся. По умолчанию предлагается промежуток с 1930 по 2029 год. Вам, дорогой студент, вероятно, больше подойдет что-нибудь вроде 1991-2090, стало быть, воспользуйтесь счетчиком для «верхней» даты, «нижняя» будет изменяться автоматически.

Выберите команду Формат > Ячейки – а лучше нажмите сочетание клавиш Ctrl+1. К диалоговому окну Формат ячеек и его шести вкладкам обращаться приходится, пожалуй, еще и чаще, чем к знаменитому диалоговому окну Параметры (с его восе­мью вкладками). И прежде всего нас интересует вкладка Число (рисунок 2.4).

Рисунок 4.4 - Формат представления чисел

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

СОВЕТ

Нажмите сочетание клавиш Ctrl+1, перейдите на вкладку Выравнивание и убедитесь, что в группе Выравнивание в списке по горизонтали выбран пункт по значению.

Проверьте, что теперь выделено в списке Числовые форматы. Это, по-прежнему, формат Общий, и он позволяет обрабатывать, в том числе, и текст. Если же вам понадобится представить как текст данные, которые Excel таковым не считает, добавьте перед ними апостроф (одиночную кавычку). В ячейке апостроф виден не будет, его роль чисто служебная. Не очень длинную последовательность цифр Excel, понятное дело, истолкует как число и сохранит ее вид почти неизменным – во всяком случае, в строке формул. В ячейке, впрочем, это число Excel тоже разве что выровняет вправо и отбросит незначащие нули. Стоит, однако, ввести дюжи­ну значащих цифр, и Excel вполне может представить число в ячейке в экспонен­циальной (научной) форме – не меняя его формат, вид в строке формул и толко­вание во внутреннем представлении. Хуже, если вы попробуете ввести больше 15 значащих цифр: если не навязать такому числу текстовое представление, все цифры сверх пятнадцати будут отброшены – или заменены нулями, если в спис­ке Числовые форматы выбрать предварительно пункт Числовой и установить счет­чик Число десятичных знаков, скажем, на значение 30 (см. рисунок 2.4).

ВНИМАНИЕ

Таково общее ограничение Excel: число значащих цифр не может быть больше пятнадцати!

ВНИМАНИЕ

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

Остальные форматы на вкладке Числа лучше изучить самостоятельно. Отметим только несколько важных, на наш взгляд, обстоятельств, предварив их не менее важной общей идеей: все хитрости с форматами нужны Excel вовсе не для ослож­нения нашей жизни. Их общая идея: упростить ввод, облегчив Excel распознавание вводимых данных. Ради этого и затеяны все условности. Введите, например, 1 р – выравнивание влево и значение в строке формул, совпадающее со значением в ячейке, дают понять: введен текст. Стоит, однако, поставить после буквы «р» точку, и Excel воспринимает вводимые данные как денежную сумму, да еще и присваивает ячейке денежный формат с определенным (самим приложением) числом десятичных знаков. В строке формул никакого символа денежной едини­цы при этом нет: Excel будет работать с числом рублей так же, как с любым дру­гим числом, напоминая нам о его смысле только в ячейке. Задав формат ячейки как Денежный (с числом десятичных знаков, например, два), мы вместо рубля уви­дим в ячейке 1, 00р. Вводить при этом запятую, нули, букву «р» и точку вовсе ни к чему. Заключив единицу в скобки, мы получим то же самое, но со знаком «минус» – Excel воспринимает числа в круглых скобках как отрицательные. Введите 1.1.

Вполне возможно, вы увидите в строке формул 01.01. 2000, а в ячейке 01. янв – значит, Excel присваивает ячейке числовой формат (все форматы) типа ДД.МММ. Присвойте теперь этой ячейке формат Числовой с разделителем групп разрядов и двумя десятичными знаками, и в ячейке вы увидите 36 526,00, а в строке фор­мул – то же самое, но без пробела-разделителя. Excel обрабатывает даты, ставя им в соответствие число дней... нет, не с начала века, а с начала 1900 года, а время – в соответствии с долей времени, истекшего с начала суток. Поэтому, например, значению 36 526, 50 в формате Время типа 14.3.99 13:30 будет соответствовать 01.01.00 12:00 в ячейке и 01.01.2000 12:00:00 в строке формул. Еще немного экс­периментов: числу 0, 5 в том же формате (или в формате Дата того же типа) соответ­ствует 00.01.00 12:00 (00. 01.1900 12:00:00 в строке формул), числу 1 - 01. 01. 00 (00.01.1900 в строке формул). И наоборот, дате 01.01.1901 соответствует число 367. Выводы: нулевого января не бывает, стало быть, Excel ведет счет времени с 31 де­кабря 1899 года. Триста шестьдесят седьмым днем с начала 1900 года первое янва­ря 1901 года могло оказаться, только если в 1900 году было 366 дней – но летопи­си свидетельствуют: год этот високосным не был. Попробуйте по очереди ввести в ячейки с присвоенным форматом Дата даты 29 февраля 1900, 1999 и 2000 года... Сравните результаты. Excel на мякине не проведешь – только вот насчет 1900 года «ошибка вышла, вот о чем молчит наука».

Главное ясно: с этим представлением данных – глаз да глаз. Форма и содержание, сущность и явление... Но это еще не все. Выберите команду Сервис > Параметры, в диалоговом окне Параметры перейдите на вкладку Правка и установите (это не совет, а возможность) флажок Фиксированный десятичный формат при вводе, десятич­ных разрядов и установите нужное число разрядов соответствующим счетчиком. Теперь при вводе любого числового значения определенное счетчиком количе­ство разрядов (последних введенных) будет автоматически отделяться запятой.

СОВЕТ

При вводе в режиме фиксированного десятичного формата в строке состояния «загорается» индикатор FIX. Обращайте внимание на строку состояния: помимо индикаторов, там все вре­мя присутствует комментарий состояния Excel. Щелкните правой кнопкой мыши на строке состояния. Появившееся контекстное меню позволит выбрать функцию, аргументами кото­рой послужат значения в выделенных ячейках. Значение этой функции отображается в стро­ке состояния – это работа средства Автовычисление.

А вот еще сюжет. Складываете вы, допустим, некие числа, ясно видимые в ячей­ках вместе со своей суммой, и вдруг замечаете, что Excel врет! Неправда ваша – посмотрите (мы же предупреждали!) на строку формул и сравните значения, выводимые в ней, со значениями, видимыми в ячейках. Как быть? Нет проблем. Если уж вас так задело несоответствие точности Excel и точности, видимой в ячейках, выберите команду Сервис > Параметры, в диалоговом окне Параметры перейдите на вкладку Вычисления и в группе Параметры книги установите флажок точность как на экране. Кое-что уже ясно. Пора переходить к делу. К какому? Ну, например, к работе со списками.