• Документы Excel: книги, содержащие листы
• Базы данных Excel: списки
• Диаграмма — наглядно и эффектно
• Функции и графики — легко и просто
• Лабораторная работа: обработка результатов
• Интерактивная диаграмма — ловкость рук и...
• Решение уравнений: на глазок и с заданной точностью
• "Движенья нет, сказал мудрец брадатый": циклические ссылки
• Выводы

Excel? Знаем, электронные таблицы. А электронные таблицы? Ну... это Excel. Понятно. Так вот, электронные таблицы бывают и иного рода, хотя в наших местах прижились таблицы Microsoft Excel. В свою очередь, Excel — это не только таблицы, пусть и электронные (а что это, кстати, значит?). Excel — это (пусть и не безгранично) универсальное средство работы с числами. Не слишком большие базы данных, вычисления, диаграммы, графики, анализ, собственные средства программирования — стоит того, чтобы познакомиться поближе. Эта глава — удачный случай. Мы не станем пугать читателя всей глубиной возможностей замечательного приложения. Мы постараемся показать — по общему принципу этой книги, что Excel трудно переоценить. И сделаем это на примере Excel 2000. Тому, кто по каким-то причинам остерегается новшеств, эта глава будет не менее полезна. Вы просто лучше сможете ощутить: пора ¹. Сами же приемы работы меняются от версии к версии весьма мягким — спасибо корпорации Microsoft — эволюционным путем. Итак, задачи и методы их решения.

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

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

 

Рис. 2.2. Книга на основе шаблона Счет

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

ВНИМАНИЕ

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

 

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

СОВЕТ

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

СОВЕТ

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

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

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

Сослаться на ячейку можно и из другой книги. Тогда, например, адрес первой ячейки первой (еще не сохраненной) книги будет выглядеть как [Книга1]Лист1!А1, а ссылка на последнюю ячейку некоей книги с именем Последняя книга.xls (лист снова первый) будет выглядеть как '[Последняя книга]Лист1'!$IV$6553б. Символы доллара, появившиеся перед номером столбца и номером строки, означают, что ссылка на ячейку — абсолютная, то есть не зависит от взаимного расположения влияющей и зависимой ячеек. Впрочем (поэкспериментируйте!), смысл понятий абсолютной и относительной ссылок для ячеек, находящихся в разных книгах, несколько... размыт. Одиночные кавычки, апострофы (перед квадратной скобкой с именем книги и перед восклицательным знаком после имени листа) тоже, в общем, не должны озадачивать. Excel автоматически ставит их при появлении “подозрительных” (по его мнению) символов в именах книги или листа. Существенно, однако, появление ссылки типа 'D:\АS\АS12\[Книга1.хls]Лист1'!$А$1. На сей раз в кавычки взят еще и полный путь к книге, а это означает, что книга с влияюшей ячейкой закрыта и чудодейственное свойство Excel (не забывайте о нем!) отслеживать ее перемещения не работает!

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

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

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

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

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

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

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

Остальные форматы на вкладке Числа лучше изучить самостоятельно ¹³. Отметим только несколько важных, на наш взгляд, обстоятельств, предварив их не менее важной общей идеей: все хитрости с форматами нужны 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 года “ошибка вышла, вот о чем молчит наука” ¹⁷.

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

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

Список — это база данных Microsoft Excel. Столбцы списка — поля базы данных, а заголовки ¹⁹ (подписи) столбцов — имена этих полей. Строки же списка — записи этой базы данных. Резонный вопрос: чем список отличается от прочих данных на рабочем листе? Организацией. Толково и тщательно рассмотреть вопрос нам не удастся: мало места (да и вряд ли кому вообще известны все нюансы организации списков), — но не беда, и в решении этой проблемы, как и многих других в Excel, главное — это практика. Основной принцип создания списка, впрочем, достаточно прост: список должен быть отделен от остальных данных на листе пустыми строками и столбцами, в самом же списке их быть не должно. Попробуем создать журнал посещений студентами занятий, в котором отсутствие будет отмечаться нулем, пассивное присутствие — единицей, а прочие оценки — соответствовать результатам работы. Главная трудность в работе — ручной ввод данных. Excel, однако, предоставляет множество средств для упрощения решения этой задачи. Автозамена — средство, известное нам по работе с Word, в Excel — не первоочередное, но полезное, однако о нем уже все сказано (напомним, Сервис > Автозамена).

Проверить орфографию также не составит труда (Сервис > Орфография — или клавиша F7). Рассказывать об остальных средствах, полезных при вводе данных, можно очень долго — и в рамках нашей книги мы можем лишь привлечь к ним ваше внимание. Начнем с Автозаполнения. Довольно логично первый столбец заполнить порядковыми номерами (сколько их, студентов-то?). Введите в первую ячейку самого левого столбца списка число 1. Укажите на черный квадратик в правом нижнем углу этой ячейки — маркер заполнения — указатель превратится в черный плюс. Схватите маркер заполнения и перетащите его (вместе с рамкой) на одну ячейку вниз. Теперь и в ней тоже единица. Небогато: того же можно достичь, схватив рамку (указатель превратится в стрелку) ячейки и перетащив ее, удерживая клавишу Ctrl. (Вы уже догадались, что, не удерживая клавишу Ctrl, вы просто переместите ячейку.) Другие способы копирования, перемещения и прочих приемов работы с ячейками (присвоения числового формата, например) представляет контекстное меню ячейки (рис. 2.5).

Запомнив способы копирования и перемещения ячеек (и диапазонов), введем в ячейку под первой число 2. Выделим обе ячейки и, схватив маркер заполнения, перетащим его вниз, ячеек на шестнадцать. (Число ячеек можно определить по всплывающей подсказке со значением, помещаемым в ячейку.) Диапазон заполнен числами от 1 до 18 (таково число наших студентов): Excel распознает начальное значение и шаг заполнения — и автоматически заполняет диапазон по распознанному принципу.

Для других ситуаций весьма полезны контекстные меню перетаскивания: меню, появляющиеся, соответственно, при перетаскивании правой кнопкой мыши рамки диапазона (рис. 2.6) или маркера заполнения (рис. 2.7). Первое из них полезно, по крайней мере, для копирования только значений или только форматов. Про команды Связать и Создать гиперссылку тоже не стоит забывать. Команды второго меню еще интереснее. Изучите их! Протащите, например, правой кнопкой мыши маркер заполнения через несколько ячеек. Выбрав команду Прогрессия, вы вызовете диалоговое окно Прогрессия (рис. 2.8), в котором есть много возможностей настройки параметров прогрессии, члены которой и заполнят отведенные (протаскиванием маркера заполнения) ячейки. При этом ограничением числа членов (заполненных ячеек) станет либо число выделенных ячеек, либо установленное в диалоговом окне предельное значение. В это же диалоговое окно можно попасть и командой Правка > Заполнить > Прогрессия. Нюансы поведения осваиваются на практике.

 

Рис. 2.6. Контекстное меню перетаскивания рамки

 

Рис. 2.7. Контекстное меню перетаскивания маркера заполнения

 

Рис. 2.8. Определение типа прогрессии

ВНИМАНИЕ

Более сложная задача — заполнение второго столбца фамилиями и именами студентов. Но и здесь есть несколько полезных приемов. Самый простой: если у вас уже есть список в Word, возможны варианты. Если список представлен в виде таблицы, скопируйте его и вставьте в активную ячейку (обычными приемами копирования и вставки). Скопированные столбец или строка таблицы Word будут помещены, соответственно, в столбец или строку Excel. Даже если список Word содержится в таблице, разделителей в виде символа конца абзаца будет достаточно, чтобы в Excel список заполнил столбец, а разделителей в виде символа табуляции — для заполнения строки.

Щелкните на кнопке Импорт, и список будет помещен в поле Списки. В поле же Элементы списка в список, видя его целиком, можно внести изменения. (Таким же образом можно просто создать новый список вручную.) Теперь Excel сможет распознать этот список по двум его элементам и начать автозаполнение, в том числе и не подряд, а, скажем, через один или два элемента.

Самое время заполнить строку дат проведения занятий. Сначала придется создать для нее место — ведь мы ввели первые два столбца прямо со строки А. Щелкните на первой строке. Выберите команду Вставка > Строки — и проблема решена: Excel добавляет строку выше выделенной ячейки. Аналогично, командой Вставка > Столбцы можно вставить столбец слева от выделенной ячейки.

Введите теперь в ячейку С1 дату первого занятия, например 1.9. Excel автоматически распознает дату. Выделите ячейку С1 и выберите команду Правка > Заполнить > Прогрессия. В диалоговом окне Прогрессия (см. рис. 2.8) настройте параметры прогрессии так, чтобы даты в строке нарастали через семь дней, но не дальше 31 декабря. Щелкните на кнопке ОК — строка дат заполнена. Того же результата ясно было достичь, введя две первых даты и протащив маркер заполнения до достижения последнего занятия.

Выделите новый столбец Всего (например, щелкнув в верхней ячейке и нажав сочетание клавиш Ctrl+Shift+¯ ) и выделите (визуально) и его жирной рамкой. Схватите рамку (указателем в форме стрелки), нажмите сочетание клавиш Ctrl+Shift и перетащите на нужное место, на границу между следующими месяцами. При таком перетаскивании рамка диапазона представляет собой не прямоугольник, а горизонтальный или вертикальный отрезок — стало быть, и вставить копируемый диапазон можно как в виде строки, так и в виде столбца, положение отрезка и всплывающая подсказка показывают диапазон вставки. Главное в таком копировании — оно происходит с “отталкиванием” диапазона, бывшего ранее на месте вставки, что нам как раз и нужно. Вставьте все такие столбцы вместе с итоговым за год, которому и присвойте название Итог.

Для подведения итогов выделите диапазон Всего и щелкните на кнопке Автосумма на панели инструментов Стандартная (или нажмите сочетание клавиш Alt+=). Excel чудесным образом (благодаря грамотной организации списка) соображает, что требуется, и суммирует в каждой ячейке итогового столбца значения в той же строке слева — в пределах данных одного месяца. Формулу, по которой Excel производит вычисления в каждой из ячеек, легко видеть в строке формул, щелкнув на конкретной ячейке. В строке 10, например, итогового столбца можно видеть формулу =СУММ(С10: G10). Проделав то же с остальными столбцами Всего и со столбцом Итог, вы обнаружите, что Excel суммирует то, что нужно, во всех них, в том числе и в итоговом столбце за семестр, что нетривиально: можно было ожидать в нем двойную сумму — сумму собственно данных и сумму итогов. На самом же деле, в нем сумма только итогов (формула!).

Осталось озаглавить наш список. Щелкните на любой ячейке списка и нажмите сочетание клавиш Ctrl+Shift+8: выделен весь список. Перетащите выделенный диапазон на несколько строк ниже. Выделите диапазон ячеек над списком, достаточный для создания надписи, и щелкните на кнопке Объединить и поместить в центре панели инструментов Форматирование. Введите заголовок, например ЖУРНАЛ ПОСЕЩЕНИЙ. Поскольку клавиша Enter в Excel уже занята, для разбиения заголовка на две строки нажмите сочетание клавиш Alt+Enter после ввода первого слова. Все. Небольшие штрихи форматирования по вкусу — список готов (рис. 2.11). А как бы вывести список на печать? Выберите команду Вид > Разметка страницы. Excel извещает вас о режиме разметки страницы, дающем возможность перетаскивать границы страниц мышью (рис. 2.12). Прежде чем приступить к этой операции, выберите все же команду Файл > Параметры страницы и ознакомьтесь с диалоговым окном Параметры страницы, там есть много вариантов. Выберите лучший!

Рис. 2.11. Готовый список

 

Рис. 2.12. Разметка страницы

Надеемся, вы помните об упомянутой в предыдущей главе замечательной возможности Excel создавать диаграммы на основе собственных таблиц данных. Почему бы не представить на диаграмме динамику процесса обучения? Выделите список (но без порядковых номеров — они нам только помешают). Выберите команду Вставка > Диаграмма, а лучше щелкните на кнопке Мастер диаграмм на панели инструментов Стандартная. Первое диалоговое окно Мастера диаграмм позволяет выбрать тип и вид диаграммы. Нам по душе один из видов диаграммы типа Гистограмма: Трехмерная диаграмма. Щелкните на кнопке Просмотр результата — и вам станет понятно, чем такая диаграмма нагляднее диаграмм других видов (рис. 2.13). Внизу, под полем Образец, можно прочесть, что трехмерная диаграмма показывает раскладку значений по рядам и категориям. Что бы это значило? Выражаясь как бы математически (не слишком строго), значения — это значения некоторой функции, зависящей от аргументов, расположенных рядами. Категории — это точки, в которых “считываются” величины членов последовательности (аргументов) для каждого ряда. В трехмерной диаграмме Excel категории располагаются по оси X, ряды — по оси Y, а значения — по оси Z (по умолчанию вертикальной).

Excel располагает огромным количеством встроенных функций. Мы не будем перечислять даже категории этих функций: если всерьез ими пользоваться, стоит всерьез к ним и обратиться. Рамки же нашей книги позволяют лишь намекнуть, насколько легко и просто решать в Excel довольно сложные задачи. Построим график астроиды ²⁰, создав сначала таблицу значений с помощью функций. Астроида — это кривая, задаваемая уравнением х^2/3+у^2/3=а^2/3. Положим а=1 и воспользуемся параметрическим представлением x=cos³t, y=sin³t для построения кривой на отрезке t Î [ -p ; p ]. Зададим t на левой границе приближением -3, 2, введя его в ячейку А1. Введя в ячейку А1 значение -3, 1, мы зададим шаг автозаполнения 0, 1.

Присвоим ячейкам А1, В1 и С1 формат Числовой с одним десятичным знаком в первой и двумя в остальных: при последующем протаскивании маркера заполнения Excel скопирует формат. Щелкнем на ячейке В1 и введем =cos(a1)^3, завершив ввод нажатием клавиши Tab. В ячейку С1 введем =sin(a1)^3.

Выделим (например, протаскиванием мыши) ячейки B1 и С1 и протащим маркер заполнения на строку вниз — формулы и форматы скопированы. Выделим (например, щелчком на угловой ячейке, а затем на противоположной ей по диагонали с удержанием клавиши Shift) диапазон А1:С2 и протащим маркер заполнения вниз до появления всплывающей подсказки со значением 3, 2 — формулы и форматы скопированы, и столбцы значений х и у заполнены.

Выделим диапазон В1:С1 и дважды щелкнем на нижней границе рамки выделения, удерживая клавишу Shift — неплохой способ выделения диапазона.

Выберем команду (просто для разнообразия) Вставка > Диаграмма и в уже знакомом диалоговом окне Мастера диаграмм (см. рис. 2.13) выберем тип диаграммы. Казалось бы, здесь все ясно: График. Увы, график в Excel предполагает равномерное (с постоянным шагом) изменение аргумента. В нашем случае это не так, но выход есть: выберем тип Точечная. Вроде, неплохо получилось (рис. 2.18).

Вообще же, путь к функциям лежит через щелчок на кнопке Вставка функции панели инструментов Стандартная, команду Вставка > Функция — и через общий способ обращения к функциям: щелчок на кнопке Изменить формулу (“знак равенства” слева от строки формул). В последнем случае обращение к диалоговому окну Мастера функций (рис. 2.19) произойдет не сразу, а только в том случае, если вам не хватит функций из раскрывающегося списка недавно использовавшихся функций, появляющегося в поле имени после щелчка на кнопке Изменить формулу. Именно списки Категория и Функция этого диалогового окна помогут выбрать нужную функцию среди богатств Excel. Ну, а про операторы ^ (возведения в степень), * (умножения) и другие вполне доступно рассказано в Справочной системе. Не забывайте только о порядке выполнения операций (символы которых и называются в Excel операторами ²¹)!

Сильное впечатление производят возможности Excel при работе с большими массивами чисел, подлежащих “групповым операциям”. Именно с этим студенту приходится сталкиваться при подготовке отчетов по лабораторным работам. Впрочем, та же наука вполне пригодна и при обработке результатов серьезных экспериментов. Мы не будем задаваться целью обучить вас статистике, метрологии и прочей кабалистике ²² — методические указания на сей счет горазды давать пособия по проведению соответствующих работ, а постижение серьезных научных изысканий на эту тему требует известного подвига. Ограничимся поэтому простенькой иллюстрацией к реальной лабораторной работе ²³ по определению момента инерции на основе измерения периода колебаний крутильного маятника.

Здесь I — момент инерции тела, I₀ — момент инерции добавочного груза, Т₁ — период колебаний маятника, Т₂ — период колебаний маятника с добавочным грузом.

Занесем данные эксперимента в таблицу, но прежде, приняв к сведению известный момент инерции добавочного груза I₀=(5,35±0,01) кг*м², поместим его в ячейку выше таблицы данных (если “красота” вас не очень заботит, такое размещение констант, потребных для вычислений, вполне разумно). Заголовок таблицы лучше всего ввести, предварительно отведя под него несколько ячеек и щелкнув на кнопке Объединить и поместить в центре панели инструментов Форматирование. (Про сочетание клавиш Alt+Enter для разбиения текста в ячейках по строкам вы не забыли?) При вводе заголовков в таблицу вам пригодятся верхний и нижний индексы. К сожалению, клавиатурные сокращения Word для этих целей в Excel не работают. Потому для помещения текста в индекс нужно любым известным способом вызвать диалоговое окно Формат ячеек (см. рис. 2.4), перейти на вкладку Шрифт и установить соответствующий флажок. С символами вроде D тоже можно справиться: установите шрифт Symbol и введите D (главу 1 помните?). Ячейкам со значениями и разбросом присвойте формат Числовой с одним знаком после запятой — измерение периода колебаний с точностью лучше десятой секунды представляется нам проблематичным. Формат остальных ячеек выберите по вкусу, не забывая о том, что точность с ходом действий имеет свойство разве что уменьшаться.

В следующий столбец справа поместим значения второго периода, а правее него — столбец значений разброса. Формулы для подсчета разброса значений второго периода скопируем из такого же столбца для первого периода: поскольку адрес ячейки со значением среднего смешанный (с относительным номером столбца), а адреса остальных ячеек — просто относительные, ошибки не произойдет. Следующие два столбца мы заполним значениями модулей (абсолютных величин) значений разброса — для подсчета их среднего. Здесь нам пригодится функция ABS ²⁴, вставить которую можно, обратившись (для разнообразия) щелчком на кнопке Вставка функции панели инструментов Форматирование к диалоговому окну Мастера функций (см. рис. 2.19) и выбрав эту функцию в категории Математичеcкие. Подсчитав в отдельных ячейках (что не обязательно) квадраты периодов и относительную погрешность их измерения, введем формулы для подсчета значения момента инерции и относительной и абсолютной его погрешностей. Первая из них (в нашем варианте) выглядит как =А9*А13/(В13-А13) — обратите внимание на скобки после оператора деления: порядок выполнения операций! Вторая содержит еще одну функцию: =КОРЕНЬ((С13)^2+(013)^2). Третья — ну, разберетесь. Вот и результат наших трудов: T=(13,81+1,07) кг*м² (см. рис. 2.20).

Еще немного о диаграммах и лабораторных работах. Достаточно часто, завершив работу, случается обнаружить, что среди результатов, явно “просящихся” на определенный график (по тем или иным соображениям красивый), есть несколько точек, явно “идущих не в ногу”. В разумном эксперименте такие точки называются промахом и просто отбрасываются. В лабораторных же делах можно просто что-то “прохлопать”, не туда посмотреть — да в наше время не редкость и разбитое стекло вольтметра, склеенное непрозрачной лентой, или градуировочная кривая, залитая тушью. Преподавателю же, как это тоже бывает, важен отчет, а не оправдания. Ну вы-то, дорогой студент, еще не преподаватель, и ничто человеческое вам не чуждо ²⁵. Пробьемся...

Заполните (“автозаполните”) столбец значениями, начиная с нуля, с шагом 0,5 до предельного значения 10. В соседнем столбце постройте (с помощью формулы) квадраты этих чисел. Выделите таблицу и щелкните на кнопке Мастер диаграмм панели инструментов Стандартная. Постройте на листе с таблицей данных диаграмму типа Точечная (без кривой, только из точек-маркеров). Щелкните на маркере с абсциссой, скажем, 5. Указатель при этом превращается в четырехглавую стрелку, а рядом с ним появляется всплывающая подсказка со сведениями о координатах этой точки. Попробуйте изменить ее ординату. Excel немедленно выведет диалоговое окно Подбор параметра (рис. 2.21) с запросом о ячейке, изменяя значение которой, можно установить в данной ячейке новое значение. Легко представить, что единственной ячейкой, способной изменить результат расчета по формуле, может быть ячейка аргумента, то есть соответствующая ячейка слева. Одна из способностей Excel подбирать параметр и приведет к отысканию нужного значения именно таким путем, о чем будет выведено сообщение в следующем диалоговом окне Результат подбора параметра (рис. 2.22).

Рис. 2.21. Подбор параметра при изменении значения в ячейке с формулой

 

Рис. 2.22. Сообщение о результате подбора параметра

Любопытно, что при этом произошло с графиком (рис. 2.23). Обратите внимание: при выделении на диаграмме элемента данных выделяется и таблица исходных Данных. В поле имени и строке состояния выводятся сведения о выделенном элементе. (В нашем случае в поле имени приведены сведения о точке, а в строке формул — о ряде данных, ее содержащем.) Всплывающая подсказка извещает о том, что точка, соответствующая измененным параметрам, почти слилась с другой уже имеющейся точкой, на графике же образовался “пустой” промежуток. О “дырках” в графике мы еще поговорим, пока же проделаем следующий эксперимент. Выделим столбец ординат, например, щелкнув на нем и нажав сочетание клавиш Ctrl+Пробел (не самый удачный способ, но и он годится). Скопируем столбец и, не снимая выделения, выберем команду Правка > Специальная вставка. Диалоговое окно Специальная вставка (рис. 2.24) обладает замечательными возможностями. Интересующая нас вставка достигается установкой переключателя значения в группе Вставить. Щелкните на кнопке ОК — столбец формул стал столбцом констант.

Рис. 2.23. График при подборе параметра

 

Рис. 2.24. Специальная вставка

Теперь изменение ординат не вызывает никаких вопросов Excel. “Разбросаем” (перетаскивая маркеры мышью) их значения так, чтобы уйти от точной квадратичной зависимости. Щелкнем правой кнопкой мыши на любом из маркеров данных и в контекстном меню выберем команду Добавить линию тренда. На вкладке Тип диалогового окна Линия тренда (рис. 2.25) можно выбрать тип кривой, аппроксимируюшей по методу наименьших квадратов зависимость между разбросанными по плоскости точками. Перебирать типы нам нет нужды — обратимся сразу к квадратичной зависимости, оставив работу с остальными для самостоятельных упражнений. Перейдя на вкладку Параметры (рис. 2.26), можно уточнить целый ряд важных параметров, установив, например, прогнозное построение кривой перед или назад по абсциссе (нам это не потребовалось) или задав пересечение с осью ординат в нужной точке. Непосредственно на диаграмме можно вывести уравнение аппроксимирующей кривой, тренда, что подчас и бывает основной проблемой, и величину достоверности аппроксимации R². Обратите внимание на рис. 2.27, уравнение кривой явно отклонилось от простой параболы у=х², а коэффициент достоверности аппроксимации R² стал меньше изначальной единицы — оставаясь достаточно высоким для того, чтобы полагаться на построенную кривую как правильно отражающую суть зависимости. Заметим, что при R²<0,9 стоит призадуматься, нет ли промахов в измерениях и таков ли процесс, как вы предположили.

Рис. 2.25. Выбор типа линии тренда

 

Рис. 2.26. Настройка параметров линии тренда

 

Рис. 2.27. Построение тренда

Теперь, в принципе, ясно, как заполнять “дыры” в графике: внести в таблицу данных точки с абсциссами, соответствующими пропуску в результатах, а затем подобрать ординаты так, чтобы тренд не слишком исказился. Разумеется, для жульничества все это слишком сложно: достаточно набросать на листке диаграммной бумаги имеющиеся точки — “воображенье дорисует остальное”. А вот в серьезной работе проанализировать, “что, если”, всегда важно, и Excel располагает для этого весьма удобными средствами, диапазон которых гораздо шире только что рассмотренного.

Поговорим о возможностях Excel в решении уравнений. Найдем, например, на промежутке [3; 4, 5] корень уравнения tgx=lnx. Табулируем уже известным способом значения обеих функций (рис. 2.28) и, выделив всю таблицу, построим точечную диаграмму — но уже для двух рядов данных. На промежутке [ 4; 4,1 ] тангенс, наконец, обгоняет медленно растущий логарифм, и если такая точность вас устраивает, проблема решена ²⁶.

Есть, однако ж, быстрый способ получить и более точное значение. Поместим, скажем, в ячейку А1 другого листа “стартовую” точку (х=4) для поиска корня, введем функцию f(x)=tgx-lnx в ячейку В1, выделим ее и выберем команду Сервис > Подбор параметра. В полях знакомого диалогового окна Подбор параметра (см. рис. 2.21) попробуем установить в ячейке В1 значение 0, изменяя значение в ячейке $А$1. Результат (порядка 10^-5) подбора значения функции (рис. 2.29) заметно лучше первоначальной прикидки (порядка 10^-2 при выбранной нами абсциссе 4). Как относиться к выводимому в строке состояния значению корня 4, 09545300394052 — проблемы не Excel (мы предупреждали).

И все же несколько слов о точности в Excel. Выберите команду Сервис > Параметры, в диалоговом окне Параметры (рис. 2.30) перейдите на вкладку Вычисления и в группе итерации взгляните на поля Предельное число итераций (значение по умолчанию 100) и Относительная погрешность (значение по умолчанию 0,001). Excel будет продолжать последовательные приближения до тех пор, пока не будет достигнуто одно из заданных значений.

Решим (для сравнения) то же уравнение методом дихотомии ²⁷. Согласно известной теореме Больцано — Коши, функция, непрерывная на отрезке и принимающая на его концах значения, различные по знаку, обращается внутри него в ноль. Коль скоро наша функция f(x)=tgx-lnx как раз удовлетворяет таким условиям на отрезке [4; 4,1 ], резонно обратиться к методу дихотомии. Будем делить отрезок пополам и выбирать ту половинку, значения функции на концах которой противоположны по знаку. Для этого мы всякий раз сначала будем проверять, удовлетворяет ли условию неположительности произведения этих значений “нижняя” половинка отрезка. Когда длина испытуемого отрезка станет меньше заданной точности, мы примем за корень его середину.

Введем в диапазоне А1:D1 — четыре ячейки первой строки нашей таблицы-программы (программирование на рабочем листе!) — заголовки 01, 00, 02 и КОРЕНЬ. 01 и 02 — границы исходного отрезка, и в ячейки А2 и С2 поместим соответствующие значения. 00 — середина отрезка, зададим ее формулой =СРЗНАЧ(А2; С2) — и сразу протащим (за маркер заполнения) на строку ниже. В ячейку D2 поместим текстовое значение ' корень не найден (вам понятно, зачем апостроф?). Теперь, так сказать, собственно тело программы. В ячейку A3 запишем =ЕСЛИ ((TAN($A2)-LN($A2))*(TAN($B2)-LN($B2))<=0;A2;B2). Эта формула определяет нижнюю границу отрезка в зависимости от произведения значений нашей функции на проверяемом отрезке. При истинности логического выражения (до точки с запятой) функция ЕСЛИ возвратит нижнюю границу проверенного отрезка (первое значение после точки с запятой), при ложности — верхнюю (второе значение после точки с запятой), то есть нижнюю границу “верхнего” полуотрезка. Неудивительно, что в ячейке С3 должна быть формула =ЕСЛИ((TAN($A2)-LN($A2))*(TAN($B2)-LN($B2))<=0; B2; C2). Наконец, в ячейке D3 запишем формулу =ЕСЛИ((С2-А2)<=0,00001; R3; D$2). Самостоятельно разберитесь в ее назначении, отметим только, что точность отыскания корня 10^-5 мы задали именно здесь.

Выделим диапазон A3:D3 и протащим рамку за маркер заполнения вниз. Результат выполнения программы будет выводиться в столбце D (рис. 2.31). При появлении корня процесс можно прекратить. Заведомо зная об искомой точности, мы, для придания картине стройности, ограничили все числовые форматы пятью знаками после запятой — что, разумеется, не обязательно. Интереснее обратить ваше внимание на другое: “подсвеченный” корень — вовсе не результат выделения ячейки, а следствие примененного ко всему столбцу D условного форматирования.

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

Рассказывают ²⁸, некто Ахиллес пытался догнать черепаху. Находясь от нее в 1000 шагов и пробегая 10 шагов в секунду (а та проползает всего 1 шаг в секунду), герой за 100 с пробежал разделяющие их 1000 шагов. Да вот беда: черепаха отползла еще на 100 шагов! Еще через 10 с уязвимый лишь в пятку покроет и эти 100 шагов — но и черепаха не промах: дала 10 шагов вперед! Неутомимый полубог через секунду тут как тут — но шаг, сделанный черепахой! Некий мудрый граф, любитель детей и женщин, неплохо, должно быть, заработал на тех пяти страницах в сценарии своей знаменитой мыльной оперы, где на основе этих рассуждений сделал убедительный вывод: рассмотренный итеративный процесс, содержа бесконечное число членов, вовек не сойдется ²⁹.

Пойдем и мы тем же путем. Посоветовавшись со школьниками, составим уравнение 10х-х=1000, но решать не станем ³⁰, а перепишем в виде х=100+х/10.

Приравняв неизвестное к функции от него же, следующим этапом пренебрежем десятой долей х — и вот первое приближение: х=100. Не правда ли, вот и начало знакомой по книге Льва Николаевича бесконечной процедуры... Обратимся, однако, к Excel. Введем в ячейку А1 формулу =А2, в ячейку же А2 введем =100+А1/10. Не тут-то было — последует предупреждение о циклической ссылке — ячейки ссылаются друг на друга (рис. 2.33).

Не беда (на то и Excel): выберите команду Сервис к Параметры, в диалоговом окне Параметры перейдите на вкладку Вычисления (см. рис. 2.30) и установите флажок итерации. Результат не замедлит сказаться (рис. 2.34). Немного упражнений с переключателями и кнопками группы Вычисления и полями группы итерации на той же вкладке —.и время достижения Ахиллесом черепахи (далась она ему!) можно отыскать с любой достижимой в Excel точностью (про пятнадцать значащих цифр помните?). “Сильнее бы не мог он возразить. Хвалили все ответ замысловатый”.

Знакомый доцент, увидя нас с книжками про Excel, весело смеясь, сообщил: “Меня Excel за пятнадцать минут научили”. Ну, вы-то, дорогой студент, еще не доцент, да и книжку, возможно, подольше читали. Одним словом, ничему мы еще не научились. Даже всевозможных приемов эффективной работы мы, можно сказать, просто не коснулись. Что делать, в наши задачи входило лишь рассказать вам о существовании замечательного и вполне доступного средства работы с числами. Мы вовсе не против более мощных средств — и об этом вторая часть нашей книги, но зачем пенять на хилую машину, если слабы знания?

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

Анализ данных в собственном, так сказать, смысле: таблицы подстановки, поиск решения, сценарии — стоит потратить время на то, чтобы хотя бы познакомиться с этими возможностями. Сфера их применения неохватна. Сводные таблицы и (новое средство Excel 2000) сводные диаграммы: представьте себе, просто перетаскивая нечто мышью, вы можете довольно быстро перестроить представление ваших показателей не по годам, а по позициям, или по филиалам, или по любым данным, которые только можно извлечь из имеющихся в вашей базе — это ли не визуализация данных! Кстати, о визуализации: мы ведь ничего не сказали о представлении данных на картах...

Вперед, во всемирное информационное пространство (глава 3). Уж там-то вы точно поймете — проблема не в отсутствии данных, проблема в их обработке. Не забудьте вернуться в Excel!

¹  Говорят, к апрелю 2000 Office 2000 установили уже 16% пользователей Office (во всем мире). Если Учесть, сколь тяжело дается покупка программных продуктов за пределами нашей Родины, цифра впечатляет.)

³  Если у вас Windows 98, вы поместили ярлык Excel на Панель быстрого запуска?

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

⁵  Желающие могут самостоятельно выяснить, зачем им нужны листы диалога Excel 5.0 и листы макросов Excel 4.0.

⁶  В VBA, да и просто при вставке, скажем, диапазона Excel в Word.

¹¹  Совсем как у “The Beatles”: “Her name was McGill, but she called herself Lill, and everyone knew her as Nancy”

¹²  А ведь, действительно, стоящая проблема. Сообразили же Питер Жагер с Клифом Куртцманом выручить за свой сайт www.year2000.com $10 млн! Главное — вовремя смыться.

¹⁴  Ради этого мы и призываем вас выбрать четко определенный стандарт, соответствующий (по мнению Excel) стране вашего проживания. Главная проблема — это, конечно, принятая у программистов десятичная точка. Excel приписывает российской традиции запятую. Сочувствуем космополитам — но, в конце концов, если уж быть последовательными, что-то не удается припомнить в наших широтах традицию разделять группы разрядов (триады тысяч) запятой, как это принято в дальних странах...

¹⁷  Ну, да мало ли с датами недоразумений. Когда родился Иисус Христос? Не впадая в ересь, ответим, Бог весть. Известно, между тем, что будущий основатель Советского государства родился 10 апреля 1870 года от Рождества Христова. Отчего же все прогрессивное человечество празднует годовщины со дня рождения вождя 22 апреля? Этак получается, что и родился он в городе Ульяновске... Excel совершенно не в курсе “старого” и “нового” стилей, о Юлианском календаре и исчезнувших в России 13 днях ничего не знает — и для исторических экскурсов требует особой настройки. Со временем, кстати, все в порядке: вместе с системой Excel, согласно правительственным указам, в надлежащий момент переходит на надлежащее время. Да и старый и новый стили здесь как-то не прижились, а жаль: поди разбери, о каком времени толкуют доктора, астрологи и агрономы...

²⁴  Как вы уже могли заметить, в локализованной версии Excel имена функций за небольшим исключением русские. Ни к чему, кроме недоразумений, это не приводит. Что делать, вот и еще (см. главу 1) кроты в корпорации Microsoft — франкофоны! А то, быть может, потомки любителей “рассказов о русском первенстве” или, напротив, опасающихся прослыть английскими шпионами “безродных космополитов”, недообезвреженных тов. Сталиным. Спасибо, хоть синус с косинусом не тронули.

²⁸  Зенон Элейский. Цит. по: Виленкин Н. Я. Метод последовательных приближений. М., 1968. С. 13.

²⁹  См. также: Фейнман Р., Лейтон Р., Сэндс М. Фейнмановские лекции по физике. М: Мир, 1976. Т. 1. С. 146.